COMA Tables (COnditions MetadatA for TAG DB)

1. COMA Tables(COnditions MetadatA for TAG DB) Elizabeth Gallas November 2010

2. Oracle Database: TAG DB and associated metadata tables RUNS ORACLE Database • TAG DB Event-wise metadata tables • Stores Event-wise attributes: • electron (Et, eta, phi …) • muon (Et, eta, phi …) … and references to RAW, ESD, AOD files • Official data processing chain: RAW  ESD AOD TAG files • ‘COMA’ (Conditions MetadatA) tables • Conditions of data taking • Beam conditions • Trigger and DAQ conditions • Magnetic field … • Various sources: Conditions DB, Log files, xml files, email… • Data Catalogue tables • Stores information on file and dataset processing and location • Project name • AMI tag (what processing occurred)… • Sources: AMI (ATLAS Metadata Catalogue, Tier0 … COMA tables DATA Catalogue Tables TAG DB: Event-wise metadata

3. Notes on the Run Metadata Schema • Warning: The schema tables, relations, content shown in the diagram • Reflect how I think the schema will evolve. • “*” on table and column names  Parts of the schema in flux • May not be exactly as implemented on the database instances • See database instance for the current implementation • Boxes in the diagram show the Oracle tables • Table names in the implementation have the common ‘COMA_’ prefix • COMA  COnditions database MetadatA • Note: most but not all data is loaded from the Conditions Database or the Trigger Database • Tables with a single integer primary key have key column name *_INDEX • Many tables adopt the RUN_INDEX column from RUNS • In this case, the child table includes additional columns to form its Primary (or Unique) Key(s) • Except for DETECTOR_MASK which follows the Conditions DB payload column name • Relationships are indicated with arrows connecting the tables • Arrow direction indicate parent to child relationship (foreign key constraint) • Example: Runs must use a TRIGKEY_INDEX from MASTER_KEYS (defining it’s trigger configuration) and must have DATA_SOURCE, RUN_TYPE and DETECTOR_MASK from those parent tables respectively. • Stone gray tables and/or dashed relationships are empty and some stage of development • Act as placeholders • Tables shown might have columns or new indexes added. • The elongated diamond shaped tables are many-to-many relationships. • Refer to the database instance or the ddl for column types • SVN: offline/Database/RunLumi/ConditionsToTags/ddl/ • The latest version is not guaranteed to be checked into SVN • Column Names follow COOL payload or Trigger DB column names found in the associated folders or tables (but with underscores and avoiding Oracle reserved words) • https://twiki.cern.ch/twiki/bin/view/Atlas/CoolOnlineData • …

4. One to many Many to many One to one • Abbreviations which might be on the diagram • P == Primary Key (all are mandatory) • F == Foreign Key (can be optional) • (R) == Redundant (but useful) • M == Mandatory • O == Optional • Note: columns are by default optional • U == Comprise Unique Keys for this table • Unique keys are constraints forcing uniqueness like PKs. • ? == questionable value • kept only for backward compatibility • (MC) == populated only for Simulation • MC* == populated only for MC09+ (not FDR, MC08) • NULL values vs default values – • prefer NULL when unknown • Relationships: Stone gray tables: Not filled yet.

5. COMA Schema Overview SOURCES RUN_TYPES STREAM_LBN_EVENTS PERIOD_OBS EXPLAINS LOAD STATUS MASKS STREAM_EVENTS SHORTNAMES PERIODS RUN_MAGNETS* EVENTS RUN_FILLS* RUN_PERIODS FILLS* LBN_TIMES RUNS TRANSITIONS RUN_LUMS LBN_LUMS Master_Keys LUM_DETS Run_Agg_PS COOL_CONNECTIONS MK_Triggers VIRTUAL_FLAGS Trig_Agg_PS Prescale_Keys MKT_Physics VFLAG_DETS PS_Sets MKT_Others DETSTATUS CODES TRIG_STR LVL1_PSK HLT_PSK GROUPS DETECTORS LVL1_PS HLT_PS STREAM

6. Conditions MetadatA (COMA) Schema (1) RUN_STREAM_LBN_EVENTS PF - RUN_INDEX P – LBN P – STREAM_INDEX STREAM_EVENTS PERIOD_OBS P –CPO_INDEX PROJECT_PERIOD PERIOD_NAME FILENAME_TAG PP_FILE_TIME PERIODS P –PROJECT_PERIOD PERIOD_NAME FILENAME_TAG PP_FILE_TIME PP_START_TIME PP_END_TIME PP_RUN_STRING PP_DESC PERIOD_RANK RUN_TYPES P - RUN_TYPE RUN_TYPE_DESC DATA_SOURCES P - DATA_SOURCE DATA_SOURCE_DESC DETECTOR_MASKS P - DETECTOR_MASK MASK_DECODED RUN_STREAM_EVENTS PF - RUN_INDEX P –AMI_ STREAM_NAME STREAM_EVENTS STREAM_INDEX STREAM_PRESCALE RUN_LOAD STATUS P – RUN_INDEX P - CONDITION_TYPE P – COLUMN_AFFECTED SEVERITY COLUMN_NEW COLUMN_OLD RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE F - DETECTOR_MASK F - COOL_INDEX F - PRESCALE_INDEX F - TRIGKEY_INDEX U - RUN_NUMBER U - FILENAME_TAG RECORDING_ENABLED CLEAN_STOP START_TIME END_TIME DURATION START_LBN END_LBN DAQ_CONFIGURATION ? - FILL_INDEX MC_TRIG_KEY (MC09) U – AMITAG_NAME_MC (MC*) PERIOD_RUNS P – PERIOD_NAME P – RUN_INDEX RUN_EVENTS PF - RUN_INDEX P – PARTITION_NAME L1_EVENTS L2_EVENTS L3_EVENTS RECORDED_EVENTS RUN_MAGNETS* RUN_INDEX LBN_BEGIN LBN_END SOL_STATE TOR_STATE RUN_LBN_TIMES PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION RUN_FILLS* FILL_INDEX RUN_INDEX LBN_BEGIN LBN_END FILLS* FILL_INDEX START_TIME END_TIME TBD COOL_CONNECTIONS P - COOL_INDEX COOL_IMPL COOL_SERVER COOL_SCHEMA COOL_INSTANCE U - COOL_TAG COOL_FOLDER COOL_DATE COOL_DESC TIME_TRANSITIONS P - TRANS_TIME PU - TRANS_TYPE U - TRANS_NUMBER RUN_LBN_LUMS PF – RUN_INDEX PF – LBN PF – LUM_COOL_INDEX PF – LUM_CHANNEL INST_LUM See next slides for TRIGGERS, PRESCALES, TRIGGER CHAINS AGGREGATE PRESCALES STREAMS GROUPS Data Quality Virtual Flags SHORTNAMES P – SHORTNAME U –ENTITIY_NAME ENTITY_TYPE RUN_LUMS PF – RUN_INDEX PF – LUM_COOL_INDEX PF – LUM_CHANNEL DEL_LUM READY_LUM MAX_INST_LUM MIN_INST_LUM LUM_DETS P – LUM_CHANNEL LUM_CHANNEL_NAME LUM_CHANNEL_DESC LUM_SUBSYSTEM TABLE_VALUE_EXPLAINS P – TABLE_SN P – COL_NAME P – COL_VALUE COL_DESC

7. Data Quality and Virtual Flags COOL_CONNECTIONS P - COOL_INDEX COOL_IMPL COOL_SERVER COOL_SCHEMA COOL_INSTANCE U - COOL_TAG COOL_FOLDER COOL_DATE COOL_DESC RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE … U - RUN_NUMBER … START_TIME END_TIME … START_LBN END_LBN … DETSTATUS_VFLAGS PF – VF_COOL_INDEX PF – VF_CHANNEL P – VF_RUN_BEGIN VF_RUN_END VF_DESCRIPTION VF_EXPRESSION VF_DETECTOR_STRING VF_RUN_STRING DETSTATUS_VDETS P – VF_CHANNEL VF_CHANNEL_NAME VF_CHANNEL_DESC Cool_H_Tags P – CHT_COOL_INDEX P – CHT_SUB_INDEX DETSTATUS PF - RUN_INDEX PF - COOL_INDEX PF - DETECTOR_INDEX P – LBN_BEGIN LBN_END CODE DEADFRAC THRUST DESCRIPTION DETSTATUS_DETECTORS P - DETECTOR_INDEX DETECTOR_DESC DETECTOR_LONG SYSTEM SUBSYSTEM DETSTATUS_CODES P - CODE CODE_DESC CODE_COLOR CODE_HEX DETSTATUS_GROUPS P - DETECTOR_INDEX CDG_MIN CDG_MAX

8. Runs, Triggers, Prescales and (Run,Trig)_Aggregate Prescales Runs P - RUN_INDEX DATA_SOURCE RUN_TYPE START_TIME … F – PRESCALE_INDEX F - TRIGKEY_INDEX Prescale_Keys P – PRESCALE_INDEX Prescale_Sets PF - PRESCALE_INDEX P – LBN_BEGIN P – LBN_END F - HLT_PRESCALE_INDEX F - LVL1_PRESCALE_INDEX F – TRIGKEY_INDEX (R) Run_Agg_PS PF – PRESCALE_INDEX PF – TRIGGER_INDEX F – TRIGKEY_INDEX (R) MIN_PS MAX_PS PS_FLAG PT_FLAG RR_FLAG Master_Keys P – TRIGKEY_INDEX MK_DESCRIPTION SMT_NAME SMT_VERSION LVL1_DESCRIPTION HLT_DESCRIPTION Trig_Agg_PS PF – LVL1_PRESCALE_INDEX PF – HLT_PRESCALE_INDEX PF – TRIGGER_INDEX F - TRIGKEY_INDEX (R) AGG_PS PT_FLAG HLT_PS_Keys P - HLT_PRESCALE_INDEX MK_Triggers P - TRIGGER_INDEX UF TRIGKEY_INDEX U - TRIGGER_LEVEL U - CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION LOWER_CHAIN_NAME ? - PRESCALE_SIM ? - PASSTHROUGH_SIM RERUN_PRESCALE STREAM_INFO TRIGGER_ELEMENTS LVL1_PS_Keys P – LVL1_PRESCALE_INDEX HLT_PRESCALES PF - HLT_PRESCALE_INDEX P(F) – TRIGGER_LEVEL P(F) – CHAIN_COUNTER PRESCALE PASSTHROUGH LVL1_PRESCALES PF - LVL1_PRESCALE_INDEX P(F) – CHAIN_COUNTER PRESCALE

9. Runs, Trigger Chain Tables, Run Aggregate Prescales Prescale_Sets P – PRESCALE_INDEX P – LBN_BEGIN LBN_END TRIGKEY_INDEX (R) LVL1_PRESCALE_INDEX HLT_PRESCALE_INDEX Runs P - RUN_INDEX DATA_SOURCE FILENAME_TAG RUN_NUMBER … F – PRESCALE_INDEX F - TRIGKEY_INDEX Prescale_Keys P – PRESCALE_INDEX Master_Keys P – TRIGKEY_INDEX … Run_Agg_PS PF – PRESCALE_INDEX PF – TRIGGER_INDEX F – TRIGKEY_INDEX (R) MIN_PS MAX_PS PS_FLAG PT_FLAG RR_FLAG MKT_Others P - TRIGGER_INDEX F TRIGKEY_INDEX L3_BIT L2_BIT L1_BIT L3_NAME L2_NAME L1_NAME STREAM_INFO ULCN MKT_Physics P - TRIGGER_INDEX F TRIGKEY_INDEX L3_BIT L2_BIT L1_BIT L3_NAME L2_NAME L1_NAME STREAM_INFO MK_Triggers P - TRIGGER_INDEX UF TRIGKEY_INDEX U - TRIGGER_LEVEL U - CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION LOWER_CHAIN_NAME RERUN_PRESCALE STREAM_INFO TRIGGER_ELEMENTS MKT_PHYSICS: “Physics (L1-L2-EF) chains” replaces obsolete V_MK_Triggers (get ps from Run_Agg_PS) MKT_OTHERS “Commissioning chains” (Aggregate PS for these rows also in Run_Agg_PS)

10. Runs and Triggers and Streams Runs P - RUN_NUMBER DATA_SOURCE RUN_TYPE START_TIME … F – PRESCALE_INDEX F - TRIGKEY_INDEX Stone gray tables: Not filled yet. Run_Stream_Events PF – RUN_INDEX P – STREAM_NAME STREAM_EVENTS F –STREAM_INDEX STREAM_PRESCALE Stream_Keys P -- STREAM_INDEX STREAM_NAME STREAM_TYPE OBEYLB STREAM_DESC Master_Keys P – TRIGKEY_INDEX MK_DESCRIPTION SMT_NAME SMT_VERSION LVL1_DESCRIPTION HLT_DESCRIPTION MKT_TO_SKS PF – TRIGGER_INDEX PF – STREAM_INDEX MK_Triggers P - TRIGGER_INDEX UF TRIGKEY_INDEX U - TRIGGER_LEVEL U - CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION LOWER_CHAIN_NAME RERUN_PRESCALE STREAM_INFO TRIGGER_ELEMENTS == no plan to fill TRIGGER_ELEMENTS GROUPS

11. Views V_RUN_DQTAGS RUN_INDEX COOL_INDEX COOL_TAG

12. Old ideas Next slides contain old/obsolete • Tables • Views • Ideas • Pictures

13. Obsolete Tables RUN_LBNS (OLD) • RUN_INDEX • LBN • START_TIME • END_TIME • DURATION • INST_LUM • LIVE_FRACTION • COOL_INDEX* V_MK_TRIGGERS TRIGKEY_INDEX TRIGGER_INDEX STREAM L3_BIT L2_BIT L1_BIT L3_NAME L2_NAME L1_NAME L3_VERSION L2_VERSION L1_VERSION L3_IPRESCALE L2_IPRESCALE L1_IPRESCALE L3_PASS_THROUGH L2_PASS_THROUGH L1_PASS_THROUGH L3_RERUN_PRESCALE L2_RERUN_PRESCALE RUN_LBN_LUMS* P - RUN_INDEX P - LBN P - COOL_INDEX INST_LUM LIVE_FRACTION ? TRIGKEYS TRIGKEY_INDEX COOL_INDEX HLT_KEYNAME LVLV1_KEYNAME TRIGKEY_DESC RUN_STREAMS PF - RUN_INDEX P - STREAM_NAME STREAM_EVENTS STREAM_NAME_DS RUN_LBN_STREAMS PF - RUN_INDEX P - LBN P - STREAM_NAME STREAM_EVENTS TRIGGERS TRIGKEY_INDEX TRIGGER_LEVEL CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION STREAM_INFO PRESCALE_INITIAL PASS_THROUGH LOWER_CHAIN_NAME TRIGGER_ELEMENTS RUN_LBN_TRIGGERS • RUN_INDEX • LBN • L3_BIT • L3_ACCEPTS • L2_ACCEPTS • L1_ACCEPTS

14. COMA Runs and Triggers – Nov 2009 Fundamental Secondary opts Other options RUNS RUN_NUMBER RUN_TYPE START_TIME END_TIME … TRIGKEY_INDEX • This is schematic only from TAG meeting Nov 17, 2009 showing trigger related metadata that would/might be useful to include • does not show the LB-wise changes in prescales LVL1_PRESCALES TRIGKEYS TRIGKEY_INDEX MASTER_KEY HLT_PRESCALE_KEY LVL1_PRESCALE_KEY HLT_PRESCALES STREAM_TAGS • STREAM_INDEX • STREAM_NAME • STREAM_TYPE • OBEY_LB TRIGGERS MASTER_KEY TRIGGER_LEVEL CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … GROUPS TRIGGER_ELEMENTS

15. COMA Triggers, Streams, Elements, Groups – Nov 2009 Fundamental Secondary opts Other options Notes: • Streams are definitely a piece of metadata (the association between streams and triggers) which would be useful selection criteria and help ELSSI to map selections to the CATALOG Metadata. • Elements and Groups look like they may also be of interests (but I don’t have a concrete sense for this data and its usage). TRIGGERS P - TRIGGER_INDEX FU - MASTER_KEY_INDEX U - TRIGGER_LEVEL U - CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … STREAM_TAGS • STREAM_INDEX • STREAM_NAME • STREAM_TYPE • OBEY_LB TRIGGER_ELEMENTS GROUPS

16. COMA Runs and Triggers – Nov 2009 – 2 Options: RUNS P - RUN_NUMBER DATA_SOURCE RUN_TYPE … F - TRIGKEY_INDEX TRIGGERS P - TRIGGER_INDEX FU - MASTER_KEY U - TRIGGER_LEVEL U - CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … RUNS P - RUN_NUMBER DATA_SOURCE RUN_TYPE … F - MASTER_KEY F - PRESCALE_INDEX OPTION 12 TRIGGERS P - TRIGGER_INDEX U - MASTER_KEY U - TRIGGER_LEVEL U - CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … TRIGKEYS P - TRIGKEY_INDEX P - RUN_NUMBER P - START_LB P - END_LB MASTER_KEY HLT_PRESCALE_KEY LVL1_PRESCALE_KEY PRESCALES P - PRESCALE_INDEX P - RUN_NUMBER P - START_LB P - END_LB HLT_PRESCALE_KEY LVL1_PRESCALE_KEY LVL1_PRESCALES PF - LVL1_PRESCALE_KEY PF – TRIGGER_INDEX LVL1_PRESCALE HLT_PRESCALES PF - HLT_PRESCALE_KEY PF – TRIGGER_INDEX PRESCALE PASSTHROUGH ENABLE ? LVL1_PRESCALES PF - LVL1_PRESCALE_KEY PF – TRIGGER_INDEX LVL1_PRESCALE HLT_PRESCALES PF - HLT_PRESCALE_KEY PF – TRIGGER_INDEX PRESCALE PASSTHROUGH ENABLE ? Questions: • Master Key Issue • Use negative MK for simulation ? • Idea rejected (more than 2 sources) ? • Add column indicating ‘ConfigSource’ ? • Adds PK to 2 tables with MASTER_KEY (ugh!) • Alt to 2. Add pseudo MK to MK table with source ? • Prescale type (int, float, string ?) • PassThrough possible values/meanings ? • Is Enable indicated by a prescale special value?