Minos DBI Nick West

1. Minos DBINick West Contents The Placeholder Strategy The Services It Provides Short Term Issues Long Term Solutions T2K Collaboration Meeting

2. The Placeholder Strategy • Why now? • Because • We have an urgent need to record calibration data, distribute it and make it available to the offline code • A native solution isn’t due until at least the Summer • Won’t it be very wasteful? • Have to rewrite all the code and data when the native solution becomes available? • No – it will be a good fit • In January at UK Meeting at Liverpool Iain Bertram proposed DBI API based on MINOS • See pages 10 onwards of his talk • Allows placeholder strategy • Thin wrapper package: oaOfflineDatabase • Implementation package: MinosDBI • MINOS DatabaseInterface package + 8 support packages • Backend is ROOT's SQL library • Low cost migration to native implementation • Code should be ~ free • Data should be simple 1:1 mapping DB tables and rows T2K Collaboration Meeting

3. The Services It Provides: Read Access: Concept • Concept: DB table • Individual row has • Set of calibration constants for a single "element" (e.g. channel) • A "validity" - a date range over which the data is valid • Complete DB table contains data • For all elements • Over extended period (multiple, normally overlapping, validity ranges) • Database query • For a given “context” i.e. date/time • Get "best" rows for all elements • "best" because must resolve any overlaps for each individual element T2K Collaboration Meeting

4. The Services It Provides: Read Access: Code • Example TFB calibration table: DEMO_CALIB_TABLE4 columns: CHANNEL_ID int, I_PARM int, F_PARM float, S_PARM text • Must develop corresponding table row class class TDemo_Calib_Table : public TTableRow { public: TTfbChannelId GetId() const; Int_t GetIparm() const { return fIparm; } Float_t GetFparm() const { return fFparm; } string GetSparm() const { return fSparm; } void Print(); ... }; • The Query: For event context return handle to a vector of TDemo_Calib_Table rowsTND280Event event; ND::TResultSetHandle<ND::TDemo_Calib_Table> rsh(event.GetContext()); • Sequential Access GetRow(int index) e.g. Iterate over all rowsfor (Int_t irow = 0; irow < rs.GetNumRows(); ++irow) rs.GetRow(irow)->Print(); • Random Access GetRowByIndex(int index) e.g. For given TFB return corresponding rowND::TTfbChannelId id;const ND::TDemo_Calib_Table* row = rs.GetRowByIndex(id);if ( row ) row->Print();else ND280Log("bother!"); T2K Collaboration Meeting

5. The Services It Provides: Memory Management • TRseultSetHandle is a lightweight handle • Can copy, pass by value etc. • DBI retains heavyweight result set • At least until all handles destroyed • Actually longer • Consider typical Use CaseLoop over events Request calibration constants for current event Calibrate current event Process current event Delete current event • After event deleted no handles remain • But there is a very good chance that next event will request the same constants T2K Collaboration Meeting

6. The Services It Provides:Aggregation: Natural Structuring of Data • A result set represents entire detector • But detector granular (sub-detectors) • No reason why all rows have same validity: some may be more volatile than others • Solution: Form “aggregates” • All rows sharing a validity are aggregated together • First column of row is an integer SEQNO • Members of aggregate have same value of SEQNO • The SEQNO is primary key to auxillary table that has validity • Plus other useful information e.g. insertion time, creation time • Saves space, record validity data just once, not with every row of data • Saves time, do date query just once (much slower than SEQNO query) • For each table map aggregates onto sub-detectors • Or onto whatever natural structure the data has • O.K., to have one aggregate if no internal structure • O.K. to have multiple aggregates per sub-detector if that is what is required • The interface hides this structuring • For the supplied context it locates all aggregates • Presents as single “seamless” table T2K Collaboration Meeting

7. The Services It Provides:Writer Support • Have not exposed MINOS writer support • But the hard bit is getting all the data and assigning a validity • Beyond the remit of this talk! • The tricky bit is allocate a unique (within table) SEQNO • Have provided service for that • Can allocate 2 types of SEQNO • Local – unique within this database. Use for testing and then recycle • Global – unique within all databases. Allows multiple Master databases • Quite easy to write data using the unix ‘mysql’ command. T2K Collaboration Meeting

8. Short Term Issues:Table Definition and Population • Table Definition: MINOS DBI places some minimal constraints • Defines first two columns • Remainder can be arbitrary combination of ints, floats and strings (no objects) • The calibration group need to finalise table definitions • We need to agree how we aggregate sub-detectors for each table • Associated TTableRow Class • For each table have to write a table row class (subclasses ND::TTableRow) • Very simple • Holds the data for one row or state derived from it • Provides access to this data or services derived from it • It is an important interface if want to keep table structure hidden • Table Population • Determination of constants for each validity interval beyond remit of this talk • Once data has been assembled • Use interface to allocate SEQNO • Write • Directly from C++ using ROOT's SQL interface • Indirectly to ASCII file and then simple script to write using mysql command T2K Collaboration Meeting

9. Short Term Issues:Data Distribution • What is our distribution model? • I don't know – do we have one? • But not one database for entire collaboration • Too slow and too fragile • Could have single Master feeding a series of Slaves • Snag: • At least in short term could have data written at multiple sites • KEK, Tokai, Liverpool, CERN, Vancouver, ... ? • Could even have single table e.g. pedestals written at multiple sites • How do we • Merge together ? • MySQL replication only works for single Master • Make available elsewhere ? • MINOS have a DB management package (not in T2K repository) • Supports up to 9 Masters and arbitrary number of Slaves • Incorporates a validation system that verifies distribution • As a short term ~ few months operation I could distribute amongst Masters • Not make code available, just run operation from Oxford • No support for Slaves (use your local Master) • For each Master I would require a writer account accessible for Oxford • Can only be short term - The solution doesn't scale • As tables grow validation from a remote site becomes too slow • I am not looking for a maintenance job - I go part time in October! • In longer term • Need to have a model ! • It could be a single Master at KEK and then use MySQL replication T2K Collaboration Meeting

10. Long Term Solutions:Native Implementation • Must start with an in-depth examination of Use Cases and Requirements covering • What types of data? • Possiblities: beam spill, slow controls, calibration, book-keeping • Each has a different volatility • Do we support unsigned integer data? • MINOS DBI doesn’t (but did until we tried to support Oracle backend) • What defines "Context" besides date and time? • Possibilities: run, type (real/MC), sub-detector • How do we resolve overlaps to get "best" rows? • Do we support local roll-back to reproduce past results? • How do we support global roll-back for past recalibration passes? • How do we support trial table updates ? • Why not continue with the current MINOS DBI? • After all if it ain’t broke why fix it? • No! • It is the solution to MINOS requirements NOT T2K • Must start with analysis NOT implementation! • What if it is shown to be a good fit to T2K requirements? • Should we keep it then? • No! • Code is far too dirty: drags in 8 other MINOS packages • Some could be removed e.g. leak checker • Some could be replaced e.g. logger • But others are essential e.g. Validity, Registry • Has (at least) one serious flaw • Creation Date used for two conflicting purposes • There could be others • Other experiments (SNO+, Daya Bay and Double Chooz) are either using, or actively considering using, the MINOS code • I can ask them what else they found. • In any case I have not discussed any of this with Iain Bertram • I don't know haw far his plans have developed. T2K Collaboration Meeting