GlueX Calibration Database Table Design and Interfaces – Current Status

1. GlueX Calibration Database Table Design and Interfaces – Current Status N. Kolev

2. Outline • Interfaces – types and purpose. • Table design/structure. • Examples with calibDBmanager. • Other issues – database choice (MySQL), organization and deployment, backups, incoming data flows. • (For details on tables and several examples see GlueX-doc-672).

3. Interfaces - Status • Interface integrated in the framework – in C++, needs more development, questions about exact implementation. • Web interface – trial (not very suitable for production purposes), but performs basic tasks like reading, writing of values and creating and modifying tables. • calibDBmanager – communication with the database to perform basic functions like reading, writing, creating tables, etc.

4. Framework Integrated Interface • On request, a calibrationConstants object is created in the framework, which provides the requested constants, e.g.: string bb("BCAL:ADCpedestals"); DCalibration *myCa = new DCalibration(9999,""); // this will be done by the framework const float *pt2 = NULL; myCa->GetCalibration(bb,pt2); delete myCa; // this will be done by the framework • The interface can search the local file system (should it?) or if the requested data don’t exist, get from the database. In either case it gets an XML file, which has the description as well (number of constants, their meaning, etc.). • The interface provides STL vectors with the requested calibration constants. • STATUS – code exists that can read from the XML file; needs several modifications; not tried with the framework yet.

5. Web Interface • http://tangra.phys.uregina.ca/cgi-bin/hallDcalibration.cgi • Purpose: to communicate with calibDBmanager in order to do: general reading, creating new tables, modifying existing tables, writing values of constants, table description browsing. The option “Edit calibration collection” can provide online editing etc., but this still needs a lot more work. • A more sophisticated and easy to use (perhaps perl is most suitable) interface is needed for production purposes.

6. calibDBmanager • Purpose – performs all the physical communication with the database. • Written in C++ (should not be a problem). • Exposes methods for reading, writing, creating new tables, etc. • Performs basic validation, error checking, referential integrity (more needs to be done here). • Status – most features tested as stand-alone, but needs more testing and debugging as an integrated object – e.g. a new table is created empty and this causes errors because the table is listed as existing and in a read attempt it is supposed to provide data (empty tables are in this sense misleading).

7. public: calibDBmanager(const int task, const string credentials); string getCalibrationCollection(const int runNumber, const string what, const string globalContext = ""); string getSets(string setName); string getAllSets(); string readValues(const int runNumber, const string what, const string globalContext = ""); string getSetDescription(const string setName); string createCalibrationSet(const string subsystem, const string setName, const string context, const string chadd, const string kind, const string attributes[], const string numberOfElements, const string runMin, const string runMax, const string officer, const string note); string writeCalibrationValues(const string tableName, const int runMin, const int runMax, const string officer, const string note, const int nOfElements, const float theValues[]); string calibDBmanager::writeAllValuesFromXML( const string theXMLfileName); string calibDBmanager::writeAllValues(const int runMin, const int runMax, const string officer, const string note, const string allValues); string restoreSetAsDefault(const string someSet, const string newOfficer, const string newNote); string getSubsystems(); private: string readFromTable(string queryString, MYSQL_RES &queryResult); calibDBmanager(); calibDBmanager – Methods

8. Table Design/Structure • SetDescriptions. • Attributes. • Value tables, e.g. BCAL_TDCoffsets_default_1 • A couple of auxiliary redundant tables exist, which will be removed in a production version.

9. Value Tables • +--------------+------------------+-------+------+---------------------+---------------------+ • | Field | Type | Null | Key | Default | Extra | • +--------------+------------------+-------+------+---------------------+---------------------+ • | id | int(11) | | PRI | NULL | auto_increment | • | runMin | int(11) | | | 0 | | • | runMax | int(11) | | | 0 | | • | datestamp | datetime | | | 0000-00-00 00:00:00 | | • | officer | varchar(32) | | | | | • | note | varchar(128) | | | | | • | value1 | float | YES | | 0 | | • | value2 | float | YES | | 0 | | • | value3 | float | YES | | 0 | | • | value4 | float | YES | | 0 | | • +--------------+------------------+--------+------+--------------------+---------------------+

10. SetDescriptions Table | id | subsystem | setName | context | order | kind | nOfElements | runMin | runMax | datestamp | officer | note | +----+-----------+------------------+-----------+--------------+---------+-------------+--------+--------+---------------------+---------+-------------+ | 2 | BCAL | fiberItems | default | 1 | simple | 8 | 1 | 99999 | 2006-07-17 18:26:08 | NK | Empty. | | 3 | BCAL | gammaCorrections | default | 1 | simple | 4 | 1 | 99999 | 2006-07-17 18:26:37 | NK | Empty. | | 4 | BCAL | gammaCorrections | 3pointFit | 0 | simple | 6 | 1 | 100 | 2006-07-17 18:27:09 | NK | Empty. | | 5 | BCAL | gammaCorrections | default | 2 | simple | 6 | 1 | 99999 | 2006-07-17 18:27:47 | NK | Empty. | | 30 | BCAL | Ceff | default | 1 | simple | 2 | 1 | 99999 | 2006-11-09 11:56:07 | NK | empty | | 26 | FCAL | ADCpedestals | default | 1 | channel | 212 | 1 | 99999 | 2006-11-06 13:03:51 | NK | Empty. | | 22 | BEAM | shifts | default | 1 | simple | 3 | 1 | 99999 | 2006-07-30 19:34:20 | NK | Empty. | +----+-----------+------------------+-----------+--------------+---------+-------------+--------+--------+---------------------+---------+-------------+

11. Attributes Table +----+------------------------------------+----------------------+---------------------------------------------+ | id | tableName | attributeNumber | attribute | +----+------------------------------------+----------------------+---------------------------------------------+ | 1 | BCAL_fiberItems_default_1 | 1 | fiber diamerer (mm) | | 2 | BCAL_fiberItems_default_1 | 2 | first cladding thickness (mm) | | 3 | BCAL_fiberItems_default_1 | 3 | second cladding thickness (mm) | | 4 | BCAL_fiberItems_default_1 | 4 | refraction index core | | 5 | BCAL_fiberItems_default_1 | 5 | refraction index first cladding | | 6 | BCAL_fiberItems_default_1 | 6 | refraction index second cladding | | 7 | BCAL_fiberItems_default_1 | 7 | cEff, cm/ns | | 8 | BCAL_fiberItems_default_1 | 8 | attenuation length, cm | …………… | 28 | BEAM_shifts_default_1 | 2 | y, cm | | 29 | BEAM_shifts_default_1 | 3 | z, cm | +----+------------------------------------+----------------------+---------------------------------------------+

12. Examples with calibDBmanager Global context example: “BCAL:gammaCorrections:3pointFit FCAL:TDCoffsets:316channels” Reading examples: string theOutput = calibDBmanager.readValues(330,”all”); string theOutput = calibDBmanager.readValues(330,”BCAL”,”BCAL:gammaCorresctions:3pointFit”); Writing example: string theResult = calibDBmanager.writeCalibrationValues("FCAL_ADCpedestals_default_1",1,99999,"NK","Put a note here.",212,someValues); Create set example: string theResult = theManager.createCalibrationSet("BCAL","Ceff","default", “add","simple",theAttributes,"3","1","100","NK","new fibers");

13. XML File Example <?xml version = "1.0"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns = "http://www.w3.org/1999/xhtml"><head><title>GlueX Calibration Database Web Interface</title></head><body><?xml version="1.0" encoding="UTF-8"> <system><name>GlueX</name> <subsystem>BCAL <subsystem_set><name>Ceff</name> <kind>simple</kind> <number_of_items>2</number_of_items> <value>16.8</value><attribute>cladding</attribute> <value>14.2</value><attribute>core</attribute> </subsystem_set></subsystem> <subsystem>BCAL <subsystem_set><name>fiberItems</name> <kind>simple</kind> <number_of_items>8</number_of_items> <value>0.01</value><attribute>fiber diamerer(mm)</attribute> <value>0.001</value><attribute>first cladding thickness (mm)</attribute> <value>0.0005</value> …….

14. Other Issues • MySQL – newer versions have important features – transactions are promised for all types of tables and seem to be crucial, as things can go wrong when many database access operations have to be performed as one. • Deployment, organization, backups – a first trial version can be integrated in OpenShop (e.g. on hydra). Security issues. • Incoming data – calibDBmanager is almost ready to process calibration constants coming as an XML file. Other types (C++ streams, STL vectors, etc.) should not be a problem.