Database

1. Database Collaboration Meeting 26University of California RiversideMission Inn David Forrest University of Glasgowd.forrest@physics.gla.ac.uk

2. Summary • What does the database do? • Defining the database system • Schedule • Bit of a ‘demo’ • Making client code

3. Analysis • Analysing real data requires knowledge of configuration Calibrations? Beamline Settings? Cablings? Geometry? Also included: Alarm handler settings The aim is that much of this will be automated, and that even though MICE will become more complicated (eg more detectors with calibrations), retrieving config parameters won’t need to be

4. Control Room • We currently enter a lot of information by hand (spreadsheet, EPICs magnet control etc) Can I just load beamline parameters with one click? I want to tag these settings for future use. What were my magnet settings yesterday? (The database will not replace the e-Log, but it will replace the run settings spreadsheet)

5. Other Remote Users • Web interface will be provided for users who want to view configuration data without having to use G4MICE, EPICs etc Find me all the runs with these constraints…. What were the run conditions? Show me the configfor run 01436 Easy and quick to develop for specific use cases as they arise

6. Demonstration – web GUI • [Cue demonstration] • (This is just a prototype) • This is one use case asking for run numbers associated with given meta data • You could then type this run number into a G4MICE data card or some other client code

7. G4MICE, EPICs, etc…. Interfacing with the database Web Services Host Database Host WebService Interface DB PostgreSQL Java (JDBC), XML, SAX Apache Tomcat This is the obligatory non user-friendly technology slide. You do not need to know ANY of these technologies to use the database. (So the message is don’t panic) The purpose of this diagram is simply to illustrate the choices made for each technology, covered in more detail in previous collaboration meetings. A series of functions are defined in the interface which a client in G4MICE or EPICs may call via the web service.

8. Web Interface Interfacing with the database Web Services Host Database Host Web Interface DB Postgres PHP This is just a web interface, there is no reason the user should need to know any of the technologies here (see demo, just need to point and click) Developers need understand PHP and SQL There are no current use cases which include the web interface having write access to the db (Security covered later)You could run this on other web platforms eg iPhone etc if you really wanted to

9. Review of Functionality • So the interface is some suite of functions…. • And the database stores information in six configuration domains: geometry, calibration, cabling, values set by shifters, eg magnets etc… set values tagged for later use and alarm handler limits • The information is stored with respect to a maximum of two dimensions of time- Valid time is the period over which some fact holds - Transaction time is the time it was added to the database

10. Calibrations • We use transaction time as a measure of ‘current-ness’ of a calibration for a given period. We do not lose access to old calibrations. • A calibration is an arbitrary number of potentially inhomogeneous vectors of arbitrary type. Flexible schema for current and future detectors. • These periods do not partially overlap but stack as above. It is possible that there may be no calibration for a given period. • Indexed also by author

11. Set Values • The database will record in an automated way the magnet settings, ‘ISIS settings’, target information and DAQ information, which is a superset of what is currently entered manually into the run configuration spreadsheet on the MICO page • You can retrieve these settings at the start of the run, choosing insofar as possible to run with the same settings as a previous run, for example • So here we are talking about set values pertaining to a real run which took place at some point in time • EPICS client developed by James Leaver for this

12. Tagged Set Values • We can also talk about set values which are not necessarily associated with a run which has occurred at some previous point in time • For example, it may be of interest to tag some set values for future use, eg ‘300 MeV pion beam MA’ • This also calls for an EPICs client which James Leaver is developing

13. Geometry • This is the true geometry of the experiment at a given time • It is robust enough that you can discover later that an element was misaligned. By default it returns the most recently updated geometry of a given run, say. You can also get the older information too. • See work by Mark Littlefieldto develop updatable model ofexperiment (Malcolm/Yagmur’stalk) which will feed into thisactivity nicely

14. And more… • Alarm handler settings are recorded in the database, this is fully implemented, plan to QA it with Pierrick at this CM • Cabling settings are the most complicated part. Schema not yet agreed, but structure to cope with whatever it will be is in place – anticipating discussion with JSG at this CM

15. Status • Progress was suspended in January due to failure of heplnw17 • Local copy of DB system under development in Glasgow, progress resumed late feb and in March • The main server functionality requested has now been implemented (except cabling) • Proper migration to Rutherford Lab necessary and represents the bulk of outstanding work

16. Schedule(?) • The timing of the installation of equipment at RAL is the limiting factor • Will be at RAL in April – I understand there is some chance that progress can be made on hardware front around this time but I don’t have a definite date • Once installation takes place I anticipate clients can be done rapidly, provided people volunteer or are volunteered to do them

17. How does this work ? ? • The database server at RAL and the client computer on your desk speak to each other primarily in a language called XML • Eg: • <run> <runNumber>1000</runNumber> <start>2010-03-10 11:42:42.0</start> <end>2010-03-11 11:42:42.0</end> <notes>Purely for demonstration!</notes> <status>1</status> <momD1>100.0</momD1> <momD2>100.0</momD2> <species>davion</species> <pulseStart>100</pulseStart> <pulseEnd>101</pulseEnd> <step>1.1</step></run> • Two questions : How do we send this? How do we parse this? (next slides)

18. Sending/Recieving • Use something called a SOAP Envelope to contain our XML and send it over HTTP(S). Mostly anything can send and receive HTTP. • <?xml version='1.0' encoding='UTF-8'?><SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema"><SOAP-ENV:Body><testDB xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><run><runNumber>1000</runNumber>…</run></testDB> • Most of this you would just paste in your code from what I give you. Note that we call a function called ‘testDB’ here.

19. What do I do with that? • In the example, had you asked to read run data rather than write it, you would receive a similar response in XML. • You can parse it however you like • I would recommend using SAX

20. SAX • See backup slides for references • Implemented for many different languages (incl C++, Java) • A pattern for parsing xml documents • You define functions for starting/ending xml documents, elements, reading characters in them etc

21. For example (Pseudocode) • startElement(){ if(name==“run”) { run=new Run(); } …}characters(){ if(name==“runNumber”){run.runnumber=content} …}endDocument(){ database.Write(run); …} <run><runNumber>111</runNumber>…</run>

22. Client Code Summary • This is a great, and last, chance to sit down with me and talk about making client code • We will (future tense) have a sandbox replica of the database which you can write to and read from until your code is ready

23. Client Code Example • Malcolm Ellis has written an example client which communicates with the database and will be added to G4MICE shortly • Making your own client simply means modifying this one by adding your own parser! • You only have to send different xml relevant to the function you want to call, which I will give (so that parts easy!), and write something to parse the xml you get back from the database interface • (he did this in one night  - so the suggestion is that you can too!) • James Leaver has also implemented a client in EPICs for the set value related functionality. EPICs will also control alarm handler and tagged set value information.

24. Security • Client code will connect through HTTPS, username and password will be encrypted • Move later to a key based system • The majority of client code will be read-only, as will web interface. Extra authorisation tokens required for write access.

25. Backup An elephant never forgets • Three artefacts to back up – dump of db (data+schema), log file, interface code • Very easy to back up database, simply require log file and contents (schema+data) – can be dumped to two files in a shell script and sent offsite and onsite in a simple manner. • Similarly easy to do the reverse and reinstate a db from backup • Backups of the database can in principle be performed extremely frequently (eg rate of seconds, or less)

26. Backup - 2 • Part of a wider discussion on IT requirements which we won’t get into here • Current draft document suggests daily backups. Usually run data will be written at night and calibs, hardware etc written in day – favour frequent backups during day and night (eg every 3hrs) • The point is it is very simple to script this, so we can back it up as many times as we like - quickly and easily without interrupting the server. • DB interfaces (which are just code) can of course be backed up on a frequency of one day.

27. Things to do • Migration of system from Glasgow to new architecture at RAL (cannot give date) • Effort should be made to establish how apache tomcat server can be made more stable and robust

28. And that’s a wrap

29. Backup Slides • Don’t worry. I should have stopped talking by this point. • Contents:FAQ; SOAP messages client should send (just paste into tcp client); current schema of cabling

30. Appendix 1: FAQ • Q: How future proof is the database? • A: We can add new tables if we absolutely have to. Changes to the interface allow new functionality. XML files are designed to be extendable and backwards compatible. The DB does not impose restrictions on the software which can interact with it, so we can connect it with whatever software we like. • Q: What about backups? • A: The DB will not be used without a backup script being in place, which will copy all config data to storage designated by Henry. This is easy to do. We can have an arbitrary level of redundancy. • Q: Can we mirror the interface? • A: Certainly. If a request is made for this and human, hardware and technical resources supplied (we are not presently operating a surplus), both the actual database and/or the database interface can be mirrored. The interface is written in Java so is portable. As for the database itself, the control room copy would be the master, and mirrors could be synchronised to it with high periodicity. This is supported in PostgreSQL.

31. Appendix 1: FAQ-2 • Q: Can you help me make a client application? • A: Yes, talk to me here (best option) or e-mail me on d.forrest@physics.gla.ac.uk

32. Appendix 2: Client Side Resources • SOAP messages(these do not represent authorisation tokens for connecting to db, contact me or Malcolm for that)

33. Hello World • <?xml version='1.0' encoding='UTF-8'?> • <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema"><SOAP-ENV:Body><ns1:sayHello xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><ns1:say xsi:type="xsd:string">Can you hear me?</ns1:say></ns1:sayHello></SOAP-ENV:Body></SOAP-ENV:Envelope> • In future slides I will just include from the function down, not soap envelope or soap body

34. Alarm handler • <ns1:setAlarmHandler xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> • <ns1:alInput xsi:type="xsd:string"><![CDATA[<sth> • <Alarm pvname="hungry" lolo="3." lo="3" hi="3." hihi="3."></Alarm> • <Alarm pvname="tired" lolo="3." lo="3" hi="3." hihi="3."></Alarm> • <Alarm pvname="bored" lolo="3." lo="3" hi="3." hihi="3."></Alarm></sth>]]> • </ns1:alInput> • </ns1:setAlarmHandler>

35. Tagged Set Values • <ns1:getTaggedSetValues xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> • <ns1:input xsi:type="xsd:string">Test0</ns1:input> • </ns1:getTaggedSetValues>

36. Set Values • <ns1:setSetValues xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> • <ns1:setPoints xsi:type="xsd:string"> • <![CDATA[ • <run_setvalues> • <run>…</run><daq>…</daq><isis>…</isis><target>…</target><magnets>…</magnets> • </run_setvalues> • ]]> • </ns1:setPoints> • </ns1:setSetValues>

37. Set Values (read) • <ns1:getSetValuesString xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><ns1:runno xsi:type="xsd:int">112</ns1:runno></ns1:getSetValuesString>

38. Calibration • <ns1:getCalibration xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> • <ns1:calibID xsi:type="xsd:int">112</calibID> • </ns1:getCalibration>

39. Calibration • <?xml version='1.0' encoding='UTF-8'?> • <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema"> • <SOAP-ENV:Body> • <ns1:getCalibration xmlns:ns1="urn:miceapi6" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> • <ns1:vt xsi:type="xsd:string"><![CDATA[2010-03-14 11:42:42.0]]></ns1:vt> • <ns1:detector xsi:type="xsd:string"><![CDATA[KL]]></ns1:detector> • </ns1:getCalibration> • </SOAP-ENV:Body> • </SOAP-ENV:Envelope> Ask me for the XML for writing a calibration

40. Documentation • There are many more such messages defined in the DB, some of which I’ve excluded here • I would like to produce a document (in preparation) which simply outlines the functions you can call, and teaches you how to do that confidently in XML… • …Describing in more detail how to write clients (but talk to me, don’t wait for the note, as its quicker to sit down and show you it working)

41. More Information on Technology Used • PostgreSQL Database Management System: www.postgresql.org • Apache Tomcat: http://tomcat.apache.org/ • SOAP: http://www.soapuser.com/ • XML: http://www.w3schools.com/xml/default.asp • Java: http://math.hws.edu/javanotes/ • SAX Parser: http://www.saxproject.org/quickstart.html • PHP: http://us3.php.net/manual/en/intro-whatcando.php

42. Polarity of some of these relationships should be revisited. Cabling to be reopened at this CM in discussion with JSG. Cabling is complex.