HPS Conditions System Update

1. HPS Conditions System Update • First Look at the Database Implementation Jeremy McCormick, Oct-10-2013

2. Overview HPS Conditions System Update • Existing conditions system inadequate for future needs. • No standardized support for time dependencies or validity by run. • Relatively unstructured set of flat files. (In different formats!) • Lack of conformance to standard conditions system interfaces. • Too much internal coupling of IO with reconstruction classes. • etc. • Requirements • Store data in a relational database. • Tagging and retrieval of conditions data by run. • Standard way to build full set of conditions objects using API calls. • Much discussed at last collaboration meeting. • Many data sources, different types of data, various producers and consumers, a lot of different contributors required to make it all work, etc. • This is a complicated problem!

3. New Conditions System HPS Conditions System Update • Maintains backward compatibility with the existing API. • However, porting dependent code is still necessary, the amount of work depending on the details of the prior implementation. • Major additions to org.lcsim.hps.conditions • generic database facilities • database conditions reader • packages for SVT and ECAL with data classes • Database support • MySQL test server at SLAC => world accessible with internet connection

4. What was done? HPS Conditions System Update • Created DatabaseConditionsReader based on Dima’s example. • Wrotea ConnectionManager class with utilities for interacting with the database, like connecting, querying, etc. • Added database tables to model the validity records and the raw data itself. • Wrote a set of converters, each of which creates a Java object for a specific type of conditions data. • Imported (most) text file conditions data from hps-java using MySQL’s built-in text loading commands. • Added some sample validity records for the imported data. • Wrote a few test cases which can be used as examples of how to use the system.

5. Concepts HPS Conditions System Update • Conditions key: unique tag that identifies a type of condition for the experiment e.g. “svt_calibrations” • Validity record: record in the database that establishes a time validity range (by run) for a given conditions type; modeled by the ConditionsRecord class • Converter: class registered with the reader for converting from database records to Java objects • Channel: lowest level of readout granularity in the detector e.g. strips in the SVT and crystals in the ECAL • ChannelMap: map between database IDs and channel setup information, e.g. crate, channel, slot in the ECAL • Conditions Set: conditions data with a single type returned by the conditions manager, such as “svt_calibrations”

6. ConditionsRecord (Date fields are all actually Date + time.) HPS Conditions System Update public class ConditionsRecord { int id; // database id int runStart; // starting valid run int runEnd; // ending valid run Date updated; // last updated Date created; // when created Date validFrom; // starting valid date Date validTo; // endingvalid date String createdBy; // user who added the conditions set String notes; // miscellaneous notes String name; // name of condition (conditions key) String formatVersion; // version of data format String tableName; // name of data table String fieldName; // name of data table’s field int fieldValue; // value of data table’s field }

7. Database Schema conditions calibrations table_name “SELECT value FROM” + table_name + “ WHERE “ + field_name “ = “ + field_value field_name field_value (Just an example. All database fields not shown.) HPS Conditions System Update

8. What conditions are in the database now? ECAL SVT • svt_bad_channels • svt_calibrations • svt_channels • svt_gains • svt_pulse_parameters These are all string literals that can be used as keys to retrieve a particular type of condition set and convert it to a Java object. (shown later) HPS Conditions System Update • ecal_bad_channels • ecal_calibrations • ecal_channels • ecal_gains

9. SVT Conditions HPS Conditions System Update

10. ECAL Conditions HPS Conditions System Update

11. Example: Converter Code HPS Conditions System Update // Construct the query. String query = "SELECT svt_channel_id, gain, offset FROM " + database + "." + tableName + " WHERE " + fieldName + " = " + fieldValue + " ORDER BY svt_channel_id ASC"; // Execute the query and get the results. ResultSet resultSet = connectionManager.query(connection, query); // Create the object with this channel's gain parameters. while(resultSet.next()) { int channelId = resultSet.getInt(1); double gain = resultSet.getDouble(2); double offset = resultSet.getDouble(3); collection.put(channelId, new SvtGain(gain, offset)); }

12. Example: Getting Cached Conditions HPS Conditions System Update // Use the conditions manager to create objects from the database. SvtCalibrationCollection calibrations // local variable = manager.getCachedConditions( // conditions API call SvtCalibrationCollection.class, // type of Java class to build SVT_CALIBRATIONS) // conditions key .getCachedData(); // fetch data which may be cached // Loop over returned conditions data. for (Entry<Integer,SvtCalibration> entry : calibrations.entrySet()) { SvtChannel channel = conditions.getChannelMap().get(entry.getKey()); // etc. }

13. Miscellaneous Notes and Gripes HPS Conditions System Update • Not entirely satisfied with the way the lcsim conditions system works. • not configurable in terms of feature set (enabling or disabling features) • too much automatic behavior that sometimes causes problems, e.g. automatically loading conditions even if they are not needed immediately • ...though it seems to be functioning okay for now. • Will work with Dima to refactor and restructure to our liking. (already started) • Table schema for validity records was modified from the structure proposed by Dima/SCS (which was based on EXO/Fermi). • I only included fields which I knew would have a use for us. • What should actually be stored here is up for discussion. • Backward compatibility is a problem and likely cannot be achieved easily! • Different conditions set keys • e.g. “daqmap/svt_qa.badchannels” vs. “svt_bad_channels” • Existing conditions data classes tightly coupled to text IO. • Overuse of static methods and data structures, making extension difficult or impossible without significant code modifications.

14. MySQL Stuff (real password not shown) HPS Conditions System Update # connecting to the database mysql -D rd_hps_cond -h mysql-node03.slac.stanford.edu -P 3306 -u rd_hps_cond_ro –p******** # inserting a validity record mysql> insert into conditions_dev(run_start, run_end, created, name, table_name, field_name, field_value) values (777, 1365, NOW(), 'svt_gains', 'svt_gains', 'set_id', 1); # loading a text file into a temporary data table mysql> load data local infile '/scratch/hps/svt_default.gain' into table svt_gains_scratch fields terminated by ' '; # example join of conditions data with its associated channel information mysql> select svt_channel_id, fpga, hybrid, channel, gain, offset from svt_gains as a, svt_channels b where a.svt_channel_id = b.id;

15. What’s next? HPS Conditions System Update • Add implementations for remaining conditions types and subsystems. • https://confluence.slac.stanford.edu/display/hpsg/Conditions+Discussion • What data will be available in the JLab “archiver” database? (Sergei) • Need more input here from the experts! • Decide how to handle conditions w/ overlapping time validity. • For example, QA dead channels in the SVT should be merged with the dead channels by run. • Work with subsystem experts to replace/update old lcsim code. • Eventually retire the old code once the database implementation is completed. • For SVT, plan to work with Omar and restructure existing code. • For ECal, may initially try to wrap the old, and heavily used, EcalConditions class. • Port part of the run log from the text file on confluence to the database. • https://confluence.slac.stanford.edu/download/attachments/121349638/HPS_RUN_LOG.txt • Probably start with trigger rate and beam current, each as separate conditions. • Replay the test run and check that conditions are loaded correctly. • Move database to JLab and start using as primary. • Get the conditions web application up and running.