Transcript Database - MICE Collaboration Meeting 26

Database
Collaboration Meeting 26
University of California Riverside
Mission Inn
David Forrest
University of Glasgow
[email protected]
1
Summary





What does the database do?
Defining the database system
Schedule
Bit of a ‘demo’
Making client code
2
Analysis

Analysing real data requires
knowledge of configuration
Beamline
Settings?
Calibrations?
Cablings?
Also included:
Geometry?
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 3be
Control Room

We currently enter a lot of information
by hand (spreadsheet, EPICs magnet
Can I just load
control etc)
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)
4
Other Remote Users
Web interface will be provided for
users who want to view configuration
data without having to use G4MICE,
Find me all
What were
the run etc
EPICs

conditions?
the runs with
these
constraints….
Show me the
config
for run 01436
Easy and quick to develop for specific use cases as they arise
5
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
6
G4MICE, EPICs, etc….
Interfacing with the database
Web Services Host
Database Host
DB
Interface
Web
Service
PostgreSQL
Java (JDBC),
Apache Tomcat
XML, SAX
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.
7
Web Interface
Interfacing with the database
Database Host
Web Services 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
8
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
9
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
10
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
11
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
12
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 Littlefield
to develop updatable model of
experiment (Malcolm/Yagmur’s
talk) which will feed into this
activity nicely
13
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
14
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
Proper migration to Rutherford Lab
necessary and represents the bulk of
outstanding work
(except cabling)

15
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
16
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)
17
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:SOAPENV="http://schemas.xmlsoap.org/soap/envelope/
" xmlns:xsi="http://www.w3.org/1999/XMLSchemainstance"
xmlns:xsd="http://www.w3.org/1999/XMLSchema">
<SOAP-ENV:Body>
<testDB xmlns:ns1="urn:miceapi6" SOAPENV: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 18I
give you. Note that we call a function called ‘testDB’ here.
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
19
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
20
For example
(Pseudocode)

startElement()
{
<run>
if(name==“run”)
<runNumber>111</runNumber>
{
run=new Run();
…
}
</run>
…
}
characters()
{
if(name==“runNumber”){run.runnumber=content}
…
}
endDocument()
{
database.Write(run);
…
}
21
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
22
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.
23
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.
24
An elephant never
forgets
Backup




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)
25
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.
26
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
27
And that’s a wrap
28
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
29
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.
30
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
[email protected]
31
Appendix 2: Client Side
Resources

SOAP messages
(these do not represent authorisation
tokens for connecting to db, contact
me or Malcolm for that)
32
Hello World



<?xml version='1.0' encoding='UTF-8'?>
<SOAP-ENV:Envelope xmlns:SOAPENV="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" SOAPENV:encodingStyle="http://schemas.xmlsoap.org/soap/encodi
ng/">
<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
33
Alarm handler







<ns1:setAlarmHandler xmlns:ns1="urn:miceapi6" SOAPENV:encodingStyle="http://schemas.xmlsoap.org/soap/encodi
ng/">
<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>
34
Tagged Set Values



<ns1:getTaggedSetValues
xmlns:ns1="urn:miceapi6" SOAPENV:encodingStyle="http://schemas.x
mlsoap.org/soap/encoding/">
<ns1:input
xsi:type="xsd:string">Test0</ns1:input
>
</ns1:getTaggedSetValues>
35
Set Values









<ns1:setSetValues xmlns:ns1="urn:miceapi6" SOAPENV:encodingStyle="http://schemas.xmlsoap.org/soap/encodi
ng/">
<ns1:setPoints xsi:type="xsd:string">
<![CDATA[
<run_setvalues>
<run>…</run><daq>…</daq><isis>…</isis><target>…</targ
et><magnets>…</magnets>
</run_setvalues>
]]>
</ns1:setPoints>
</ns1:setSetValues>
36
Set Values (read)

<ns1:getSetValuesString
xmlns:ns1="urn:miceapi6" SOAPENV:encodingStyle="http://schemas.x
mlsoap.org/soap/encoding/">
<ns1:runno
xsi:type="xsd:int">112</ns1:runno>
</ns1:getSetValuesString>
37
Calibration



<ns1:getCalibration
xmlns:ns1="urn:miceapi6" SOAPENV:encodingStyle="http://schemas.x
mlsoap.org/soap/encoding/">
<ns1:calibID
xsi:type="xsd:int">112</calibID>
</ns1:getCalibration>
38
Calibration









<?xml version='1.0' encoding='UTF-8'?>
<SOAP-ENV:Envelope xmlns:SOAPENV="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" SOAPENV: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
39
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)
40
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/introwhatcando.php
41
Polarity of some of these
relationships should be
revisited. Cabling to be
reopened at this CM in
discussion with JSG.
Cabling is complex.
42