COMA Tables (COnditions MetadatA)

1. COMA Tables(COnditions MetadatA) Elizabeth Gallas June 2014

2. Oracle Database: TAG Services SchemasTAG DB, TAG Catalog, COMA DB RUNS ATLR DB COMA • 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 … TAG Catalogue ATLARC DB COMA (copy) TAG Catalogue (copy) 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) • FI == Indexed Foreign Key • (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) • (t) == populated by Database Trigger • NULL values vs default values – • prefer NULL when unknown • Relationships: Stone gray tables: Not filled yet.

5. COMA Schema Load_Logs Run_Stream_Overlaps Explains SOURCES RUN_TYPES Run_Stream_LBN_Events MASKS Events ShortNames PGRL_OBS PH2L_OBS Run_ATLAS_Readys Stream_Events Transitions P2R_OBS Run_Magnets PD_OBS Run_Load_Status Run_LBN_Times Run_Stable_Beams Period_Phi_To_Plos Run_Lums RUNS Run_LBN_Lums Period_Defs Run_Fills Fills Period_P1_To_Runs Instances Schemas OnOffs COOL_Connections Run_Agg_PS GT_TO_OIS Owner_Instances Master_Keys Prescale_Keys GTags Nodes MK_Triggers Virtual_Flags Trig_Agg_PS PColumns GT_TO_FTS PS_Sets Vflag_Dets MKT_Physics,Others References Det_Status AMI_GTags Mkt_To_Sks Det_Codes LVL1_PSK HLT_PSK FTags LVL1_PS Stream_Keys Detectors GTag_Infos Groups HLT_PS

6. Conditions and Configurations Metadata for ATLAS (COMA) See next slides for: Data Periods Triggers, Prescales, and Aggregate Prescales Trigger Chain Tables Runs, Streams, Triggers Fills, Beams, Magnets, Ready CB Tables: Conditions DB Management Metadata for support of Conditions DB Browsing DQ and Virtual Flags DETECTOR_MASKS P - DETECTOR_MASK MASK_DECODED Run_Events PF - RUN_INDEX P – PARTITION_NAME L1_EVENTS L2_EVENTS L3_EVENTS RECORDED_EVENTS FILENAME_TAGS P – FILENAME_TAG FNT_DESC Run_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION DATA_SOURCES P - DATA_SOURCE DATA_SOURCE_DESC 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 DAQ_CONFIGURATION RECORDING_ENABLED CLEAN_STOP START_TIME END_TIME DURATION START_LBN END_LBN CONFIG_SOURCE MC_TRIG_KEY (MC09) U – AMITAG_NAME_MC (MC*) RUN_TYPES P - RUN_TYPE RUN_TYPE_DESC Cool_Connections P - COOL_INDEX COOL_IMPL COOL_SERVER COOL_SCHEMA COOL_INSTANCE U - COOL_TAG COOL_FOLDER COOL_DATE COOL_DESC Run_Load_Status P – RUN_INDEX P - CONDITION_TYPE P – COLUMN_AFFECTED SEVERITY COLUMN_NEW COLUMN_OLD Load_Logs P – LL_INDEX LL_PROGRAM COMA_INS_DATE COUNT_INSERT COUNT_UPDATE COUNT_DELETE COMA_UPD_DATE TABLE_VALUE_EXPLAINS P – TABLE_SN P – COL_NAME P – COL_VALUE COL_DESC Time_Transitions P - TRANS_TIME PU - TRANS_TYPE U - TRANS_NUMBER SHORTNAMES P – SHORTNAME U –ENTITIY_NAME ENTITY_TYPE

7. Data Periods (Tables and Views) RUNS P -- RUN_INDEX V_P1_RUNS PDOM_NAME P1_INDEX P1_PROJECT P1_PERIOD RUN_INDEX V_P2_RUNS PDOM_NAME P2_INDEX P2_PROJECT P2_PERIOD P1_INDEX P1_PROJECT P1_PERIOD RUN_INDEX V_P3_RUNS PDOM_NAME P3_INDEX P3_PROJECT P3_PERIOD P2_INDEX P2_PROJECT P2_PERIOD P1_INDEX P1_PROJECT P1_PERIOD RUN_INDEX PERIOD_P1_TO_RUNS P – P2R_INDEX (t) F – P_INDEX F – RUN_INDEX CREATE_USER CREATE_DATE (t) PERIOD_DEFS P – P_INDEX (t) F – PDOM_NAME U – P_LEVEL ( 1, 2, 3 ) U – P_PROJECT_PERIOD P_PROJECT P_PERIOD P_DESC P_STATUS CREATE_USER CREATE_DATE (t) MODIFY_USER MODIFY_DATE (t) PERIOD_PHI_TO_PLOS P – PH2L_INDEX (t) F – PHI_INDEX F – PLO_INDEX CREATE_USER CREATE_DATE (t) V_PHI_PLOS PDOM_NAME PHI_LEVEL PHI_INDEX PHI_PROJECT PHI_PERIOD PLO_INDEX PLO_PROJECT PLO_PERIOD PERIOD_GRLS P – PGRL_INDEX (t) UF – P_INDEX U – PGRL_NAME CREATE_USER CREATE_DATE (t) V_PERIOD_RUNS PDOM_NAME P_LEVEL P_INDEX P_PROJECT P_PERIOD RUN_INDEX PERIOD_DOMAINS P – PDOM_NAME PDOM_DESC

8. Data Periods (tables for storing updated/deleted rows) On DELETE triggers (red)  DELETE_USER must be updated by AMI Entry program P2R_OBS P2R_INDEX (t) P_INDEX (t) RUN_INDEX (t) CREATE_USER (t) CREATE_DATE (t) DELETE_USER DELETE_DATE (t) On UPDATE trigger (green) No need to populate columns DELETE_% PERIOD_P1_TO_RUNS P2R_INDEX (t) P_INDEX RUN_INDEX CREATE_USER CREATE_DATE (t) RUNS P -- RUN_INDEX PD_OBS PDO_INDEX (t) P_INDEX (t) PDOM_NAME (t) P_LEVEL (t) P_PROJECT_PERIOD (t) P_PROJECT (t) P_PERIOD (t) P_DESC (t) P_STATUS (t) CREATE_USER (t) CREATE_DATE (t) MODIFY_USER (t) MODIFY_DATE (t) DELETE_USER DELETE_DATE (t) PH2L_OBS PH2L_INDEX (t) PHI_INDEX (t) PLO_INDEX (t) CREATE_USER (t) CREATE_DATE (t) DELETE_USER DELETE_DATE (t) PERIOD_DEFS P_INDEX (t) PDOM_NAME P_LEVEL P_PROJECT_PERIOD P_PROJECT P_PERIOD P_DESC P_STATUS CREATE_USER CREATE_DATE (t) MODIFY_USER MODIFY_DATE (t) PERIOD_PHI_TO_PLOS PH2L_INDEX (t) PHI_INDEX PLO_INDEX CREATE_USER CREATE_DATE (t) PGRL_OBS PGRL_INDEX (t) P_INDEX (t) PGRL_NAME (t) CREATE_USER (t) CREATE_DATE (t) DELETE_USER DELETE_DATE (t) PERIOD_GRLS PGRL_INDEX (t) P_INDEX PGRL_NAME CREATE_USER CREATE_DATE (t) PERIOD_DOMAINS PDOM_NAME PDOM_DESC

9. Good Run List Load_Logs P – LL_INDEX LL_PROGRAM COMA_INS_DATE … GRL_XMLS P – GRLX_INDEX GRLF_INDEX GRLF_FILE GRLF_PATH GRLF_DATE GRLF_CLOB GRL_FILES P – GRLF_INDEX U – GRLF_FILE U – GRLF_PATH GRLF_DATE GRLF_SIZE GRLF_CHECKSUM GRLF_SOURCE GRLF_NAME GRLF_VERSION GRLF_STYLESHEET GRLF_PROJECT GRLF_PERIOD INTEG_LUMI GRLF_INTEG_LUMI GRLF_ERROR PERIOD_DEFS P – P_INDEX P_PROJECT_PERIOD P_PROJECT P_PERIOD … GRL_Criteria_Others P – GRLCO_INDEX F – GRLF_INDEX GRLCO_NAME GRLCO_VALUE RUNS P - RUN_INDEX … FILENAME_TAG … GRL_DQS P – GRLDQS_INDEX FU – GRLF_INDEX U – GRLDQ_STRING Run_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION GRL_RUNS P – GRLR_INDEX FU – GRLF_INDEX FU – RUN_INDEX INTEG_LUMI (R) GRLR_INTEG_LUMI RUN_LUMS P – RL_INDEX UF – RUN_INDEX UF – LUM_COOL_INDEX INTEG_LUMI … GRL_Streams P – GRLS_INDEX FU – GRLF_INDEX U – STREAM RAW_EVENTS SFO_TOT_EVENTS SFO_SEL_EVENTS XML_TOT_EVENTS XML_SEL_EVENTS RUN_LBN_LUMS P – RLL_INDEX UF – RUN_INDEX UF – LBN UF – LUM_COOL_INDEX LBAVINSTLUMI LVAVEVTSPERBX VALID RLL_INTLUMI GRL_Run_Streams P – GRLRS_INDEX FU – GRLS_INDEX FU – RUN_INDEX SFO_TOT_EVENTS (R) SFO_SEL_EVENTS GRL_RUN_LBS P – GRLL_INDEX F – GRLR_INDEX F – LBN_BEGIN F – LBN_END GRLL_INTEG_LUMI

10. 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 HPR_MODIFIED_TIME 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 L1PS_MODIFIED_TIME 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

11. 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_OTHERS “Commissioning chains” (Aggregate PS for these rows also in Run_Agg_PS) MKT_PHYSICS: “Physics (L1-L2-EF) chains” replaces obsolete V_MK_Triggers (get total prescale from Run_Agg_PS)

12. Runs, Triggers, Streams, and Events Run_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION Runs P - RUN_NUMBER DATA_SOURCE RUN_TYPE START_TIME … F – PRESCALE_INDEX F - TRIGKEY_INDEX Run_Stream_LBN_Events P – RSLE_INDEX UF – RSE_INDEX U – LBN STREAM_EVENTS Run_Stream_Overlaps P – RSO_INDEX UF – RSE_INDEX U – OVERLAP_STREAM OVERLAP_EVENTS Run_Stream_Events P – RSE_INDEX UF – RUN_INDEX U – STREAM STREAM_EVENTS SUM_FILESIZE COUNT_FILESIZE F –STREAM_INDEX STREAM_PRESCALE Master_Keys P – TRIGKEY_INDEX MK_DESCRIPTION SMT_NAME SMT_VERSION LVL1_DESCRIPTION HLT_DESCRIPTION Run_PSA_Streams P – RPAS_INDEX RUN_INDEX STREAM_INDEX LBN_BEGIN LBN_END 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 Stream_Keys P -- STREAM_INDEX STREAM_NAME STREAM_TYPE OBEYLB STREAM_DESC MKT_TO_SKS PF – TRIGGER_INDEX PF – STREAM_INDEX STREAM_PRESCALE

13. Run and Beam-related information 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 F - DETECTOR_MASK F - COOL_INDEX FI - PRESCALE_INDEX FI - TRIGKEY_INDEX U - RUN_NUMBER U - FILENAME_TAG DAQ_CONFIGURATION RECORDING_ENABLED CLEAN_STOP START_TIME END_TIME DURATION START_LBN END_LBN CONFIG_SOURCE MC_TRIG_KEY (MC09) U – AMITAG_NAME_MC (MC*) FILLS P – FILL_INDEX START_TIME END_TIME RUN_FILLS P – RF_INDEX (t) FU – RUN_INDEX FU – FILL_INDEX RUN_LUMS P – RL_INDEX UF – RUN_INDEX UF – LUM_COOL_INDEX INTEG_LUMI MIN_INST_LUMI MAX_INST_LUMI MIN_AVG_MU MAX_AVG_MU READY_FRACTION Run_Stable_Beams P – RSB_INDEX FU – RUN_INDEX FU – LBN_BEGIN LBN_END RSB_STATE FI – FILL_INDEX BEAM_ENERGY_GEV NUM_BUNCH_COLL MIN_BUNCH_DT MACHINE_MODE RUN_LBN_LUMS P – RLL_INDEX UF – RUN_INDEX UF – LBN UF – LUM_COOL_INDEX LBAVINSTLUMI LVAVEVTSPERBX VALID RLL_INTLUMI RUN_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION Run_Magnets P – RM_INDEX FU – RUN_INDEX FU – LBN_BEGIN LBN_END SOL_STATE TOR_STATE Run_Atlas_Readys P – RAR_INDEX F – RUN_INDEX F – LBN_BEGIN F – LBN_END RAR_STATE LUM_DETS ? P – LUM_CHANNEL LUM_CHANNEL_NAME LUM_CHANNEL_DESC LUM_SUBSYSTEM

14. Lum Views * Period_Defs P_INDEX (t) PDOM_NAME P_LEVEL P_PROJECT_PERIOD P_PROJECT P_PERIOD P_DESC P_STATUS … Period_P1_To_Runs P – P2R_INDEX (t) F – P_INDEX F – RUN_INDEX … V_Stable_Lums RUN_INDEX MIN_FILL_INDEX MAX_FILL_INDEX BEAM_ENERGY_GEV (MIN) NUM_BUNCH_COLL (MIN) MIN_BUNCH_DT (MIN) MACHINE_MODE (MAX) MAX_RAR_STATE LUM_COOL_INDEX INTEG_LUMI MIN_INST_LUMI MAX_INST_LUMI MIN_AVG_MU MAX_AVG_MU READY_FRACTION V_Period_Lums * P_PROJECT P_PERIOD P_LEVEL P_DESC P_STATUS MIN_RUN_INDEX MAX_RUN_INDEX NUM_RUNS MIN_START_TIME MAX_END_TIME MIN_FILL_INDEX MAX_FILL_INDEX LUM_COOL_INDEX INTEG_LUMI MIN_INST_LUMI MAX_INST_LUMI MIN_AVG_MU MAX_AVG_MU READY_LUMI READY_FRACTION BEAM_ENERGY_GEV NUM_BUNCH_COLL MIN_BUNCH_DT (MIN) MACHINE_MODE (MAX) Run_Lums P – RL_INDEX UF – RUN_INDEX UF – LUM_COOL_INDEX INTEG_LUMI MIN_INST_LUMI MAX_INST_LUMI MIN_AVG_MU MAX_AVG_MU READY_FRACTION RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE F - DETECTOR_MASK F - COOL_INDEX FI - PRESCALE_INDEX FI - TRIGKEY_INDEX U - RUN_NUMBER U - FILENAME_TAG START_TIME END_TIME DURATION START_LBN END_LBN … Run_Stable_Beams P – RSB_INDEX FU – RUN_INDEX FU – LBN_BEGIN LBN_END RSB_STATE FI – FILL_INDEX BEAM_ENERGY_GEV NUM_BUNCH_COLL MIN_BUNCH_DT MACHINE_MODE V_Run_Fills RUN_INDEX MIN_FILL_INDEX MAX_FILL_INDEX MAX_RSB_STATE MAX_RAR_STATE Run_Atlas_Readys P – RAR_INDEX F – RUN_INDEX F – LBN_BEGIN F – LBN_END RAR_STATE Run_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION

15. Event Count, PSCorr Lumi by Run/Trig CRT_Runs P – CRTR_INDEX U1 – COLLECTION_NAME U1,U2 – RUN_INDEX U2 – STREAM U2 – AMI_TAG FRX_TAG FILENAME_TAG SCHEMA_NAME STREAM_TYPE STREAM_NAME NB_EVENTS TAGDB_EVENTS TAGFILE_EVENTS AODFILE_EVENTS ESDFILE_EVENTS RAWFILE_EVENTS CRTR_FLAG CRTR_CHECKSUM CRTR_ACTIVE CRTR_SECS CRTR_ERROR CRTR_INFO Run_Trig_Lums P – RTL_INDEX FU – RUN_INDEX FU – TRIGGER_INDEX PSC_INTLUMI_SB PSC_INTLUMI_SBR MIN_AGG_PS_SBR MAX_AGG_PS_SBR Run_Agg_PS PF – PRESCALE_INDEX PF – TRIGGER_INDEX F – TRIGKEY_INDEX (R) MIN_PS MAX_PS PS_FLAG … Trig_Agg_PS PF – LVL1_PRESCALE_INDEX PF – HLT_PRESCALE_INDEX PF – TRIGGER_INDEX F - TRIGKEY_INDEX (R) AGG_PS PT_FLAG RUNS P - RUN_INDEX … F – PRESCALE_INDEX F - TRIGKEY_INDEX Run_Stream_Events P – RSE_INDEX UF – RUN_INDEX U – STREAM STREAM_EVENTS … MKT_ALLs P - TRIGGER_INDEX F TRIGKEY_INDEX MKTA_TYPE TOP_NAME L3_BIT L3_NAME … Run_Atlas_Readys P – RAR_INDEX F – RUN_INDEX F – LBN_BEGIN F – LBN_END RAR_STATE Run_LBN_Times PF – RUN_INDEX P – LBN START_TIME END_TIME DURATION Run_Stable_Beams P – RSB_INDEX FU – RUN_INDEX FU – LBN_BEGIN LBN_END RSB_STATE … CRT_Counts P – CRTC_INDEX FU – CRTR_INDEX FU – TRIGGER_INDEX RTC_COUNT RTC_FLAG Run_LBN_Lums P – RLL_INDEX UF – RUN_INDEX UF – LBN UF – LUM_COOL_INDEX LBAVINSTLUMI LVAVEVTSPERBX VALID RLL_INTLUMI COOL_TCounts P – CTC_INDEX FU – RUN_INDEX FU – TRIGGER_INDEX EVENT_COUNT Prescale_Sets PF - PRESCALE_INDEX P – LBN_BEGIN P – LBN_END F - HLT_PRESCALE_INDEX F - LVL1_PRESCALE_INDEX F – TRIGKEY_INDEX (R)

16. Count entries in COOL by Run, FolderTag, Channel for selected COOL Folderssuch as Luminosity and Data Quality CB_NODES P – CBF_INDEX FU – CBOI_INDEX U – NODE_FULLPATH I – NODE_NAME NODE_ID NODE_PARENTID NODE_ISLEAF NODE_INSTIME LASTMOD_DATE CBF_LASTMOD_DATE COMA_INS_DATE (t) NODE_DESCRIPTION FOLDER_VERSIONING I – CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT COMA_UPD_DATE (t) RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE F - DETECTOR_MASK F - COOL_INDEX FI - PRESCALE_INDEX FI - TRIGKEY_INDEX U - RUN_NUMBER U - FILENAME_TAG START_TIME END_TIME DURATION START_LBN END_LBN … Project_FTag_Ranks PF – FILENAME_TAG PF – CBFT_INDEX FTAG_RANK RUN_COOL_Counts PF – RUN_INDEX PF – CBFT_INDEX P – CHANNEL ROW_COUNT CB_FTAGS P – CBFT_INDEX FU – CBF_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBFT_INSTIME CBFT_NODE_ID CBFT_ROWCOUNT CBFT_LAST_OBJTIME CBFT_SINCE_TIME CBFT_UNTIL_TIME COMA_INS_DATE (t) COMA_UPD_DATE (t)

17. Conditions DB Management Metadata Table Overview Instances Schemas OnOffs For details (columns) see: http://www-pnp.physics.ox.ac.uk/~gallas/TAGs/1203_COMA_CB.ppt Owner_Instances (Schema and Instances) GT_TO_OIS GTags (Global tags) Nodes (Folders and Foldersets) PColumns GT_TO_FTS AMI_GTags References FTags (Folder Tags) GTag_Infos

18. Data Quality and Virtual Flags (DQ Stoplights: deprecated!) RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE … U - RUN_NUMBER … START_TIME END_TIME … START_LBN END_LBN … COOL_CONNECTIONS P - COOL_INDEX COOL_IMPL COOL_SERVER COOL_SCHEMA COOL_INSTANCE U - COOL_TAG COOL_FOLDER COOL_DATE COOL_DESC 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_GROUPS P - DETECTOR_INDEX CDG_MIN CDG_MAX V_RUN_DQTAGS RUN_INDEX COOL_INDEX COOL_TAG DETSTATUS_DETECTORS P - DETECTOR_INDEX DETECTOR_DESC DETECTOR_LONG SYSTEM SUBSYSTEM DETSTATUS_CODES P - CODE CODE_DESC CODE_COLOR CODE_HEX

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

20. Obsolete ? Event Counts by Collection/Run/Trigger CRT_Runs P – CRTR_INDEX UF – CRTC_INDEX UF – RUN_INDEX NB_EVENTS CRTR_FLAG CRTR_CHECKSUM CRTR_ACTIVE CRTR_SECS CRT_Collections P – CRTC_INDEX U1 – COLLECTION_NAME U2 – FILENAME_TAG U2 – CRTS_INDEX U2 – AMI_TAG SCHEMA_NAME FRX_TAG CRT_TCounts P – CRTT_INDEX FU – CRTR_INDEX FU – TRIGGER_INDEX RTC_COUNT RTC_FLAG CRTS_INDEX CRT_Streams P – CRTS_INDEX U1 – STREAM STREAM_TYPE STREAM_NAME Run_LBN_Trig_Lums P – RLTL_INDEX FU – RUN_INDEX FU – LBN FU – TRIGGER_INDEX RLTL_INTLUMI_PSC AGG_PS (R) Too many rows (10B)

21. Obsolete Tables 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 TRIGKEYS TRIGKEY_INDEX COOL_INDEX HLT_KEYNAME LVLV1_KEYNAME TRIGKEY_DESC RUN_LBNS (OLD) • RUN_INDEX • LBN • START_TIME • END_TIME • DURATION • INST_LUM • LIVE_FRACTION • COOL_INDEX* RUN_LBN_LUMS* P - RUN_INDEX P - LBN P - COOL_INDEX INST_LUM LIVE_FRACTION ? RUN_STREAMS PF - RUN_INDEX P - STREAM_NAME STREAM_EVENTS STREAM_NAME_DS TRIGGERS TRIGKEY_INDEX TRIGGER_LEVEL CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION STREAM_INFO PRESCALE_INITIAL PASS_THROUGH LOWER_CHAIN_NAME TRIGGER_ELEMENTS RUN_LBN_STREAMS PF - RUN_INDEX P - LBN P - STREAM_NAME STREAM_EVENTS RUN_LBN_TRIGGERS • RUN_INDEX • LBN • L3_BIT • L3_ACCEPTS • L2_ACCEPTS • L1_ACCEPTS GRL_TO_DQS P – GRL2DQ_INDEX FU – GRLF_INDEX FU – GRLDQ_INDEX GRL2DQ_GOOD GRL_Criteria_DQS P – GRLDQ_INDEX U – GRLDQ_CRITERIA U – GRLDQ_SYSTEM

22. 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

23. 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

24. 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?

25. 2010 implementation Draft 1: Data Periods and Period Groups RUNS RUN_INDEX RUNS P -- RUN_INDEX PERIODS P –PROJECT_PERIOD PERIOD_NAME FILENAME_TAG PP_FILE_TIME PP_START_TIME PP_END_TIME PP_RUN_STRING PP_DESC PERIOD_RANK PERIOD_NAMES P – PN_INDEX F – PD_NAME PN_PROJECT_PERIOD PN_PROJECT PN_PERIOD PN_DESC PN_STATUS PN_CREATE_USER PN_CREATE_DATE PN_MODIFY_USER PN_MODIFY_DATE PERIOD_RUNS P – PERIOD_NAME P – RUN_INDEX PERIOD_PN_TO_RUNS P – PN_INDEX P – RUN_INDEX PERIOD_OBS P –CPO_INDEX PROJECT_PERIOD PERIOD_NAME FILENAME_TAG PP_FILE_TIME PERIOD_PG_TO_PNS P – PG_INDEX P – PN_INDEX PERIOD_DOMAINS P – PD_NAME PD_DESC PERIOD_GROUPS P – PG_INDEX F – PD_NAME PG_PROJECT_PERIOD PG_PROJECT PG_PERIOD PG_DESC PG_STATUS PG_CREATE_USER PG_CREATE_DATE PG_MODIFY_USER PG_MODIFY_DATE PERIOD_PG_TO_PGS P – PG1_INDEX P – PG2_INDEX PERIOD_DOMAIN_ROLES PDR_INDEX PD_NAME PDR_USER PDR_ROLE PERIOD_GRLS P – PG_INDEX P – GRL_NAME PGRL_STATUS

26. Draft 1: Data Periods and Period Groups (tables and views) RUNS P -- RUN_INDEX V_PG_TO_RUNS PD_NAME PG_INDEX PG_PROJECT PG_PERIOD PN_INDEX PN_PROJECT PN_PERIOD RUN_INDEX V_PN_TO_RUNS PD_NAME PN_INDEX PN_PROJECT PN_PERIOD RUN_INDEX PERIOD_PN_TO_RUNS P – PN_INDEX P – RUN_INDEX PERIOD_NAMES P – PN_INDEX F – PD_NAME PN_PROJECT_PERIOD PN_PROJECT PN_PERIOD PN_DESC PN_STATUS PN_CREATE_USER PN_CREATE_DATE PN_MODIFY_USER PN_MODIFY_DATE V_PG_TO_PNS PD_NAME PG_INDEX PN_INDEX PG_PROJECT PG_PERIOD PN_PROJECT PN_PERIOD V_PPG_TO_RUNS PD_NAME PG1_INDEX PG1_PROJECT PG1_PERIOD PG2_INDEX PG2_PROJECT PG2_PERIOD PN_INDEX PN_PROJECT PN_PERIOD RUN_INDEX PERIOD_PG_TO_PNS P – PG_INDEX P – PN_INDEX PERIOD_GROUPS P – PG_INDEX F – PD_NAME PG_PROJECT_PERIOD PG_PROJECT PG_PERIOD PG_DESC PG_STATUS PG_CREATE_USER PG_CREATE_DATE PG_MODIFY_USER PG_MODIFY_DATE PERIOD_PG_TO_PGS P – PG1_INDEX P – PG2_INDEX PERIOD_DOMAINS P – PD_NAME PD_DESC V_PERIOD_TO_RUNS PD_NAME P_LEVEL P_INDEX P_PROJECT P_PERIOD RUN_INDEX PERIOD_DOMAIN_ROLES PDR_INDEX PD_NAME PDR_USER PDR_ROLE PERIOD_GRLS P – PG_INDEX P – GRL_NAME PGRL_STATUS

27. COOL_CB Conditions DB Management Metadata CB_SCHEMAS P – CBS_NAME CBS_SYSTEM CBS_DESC CB_ONOFFS P – CBO_NAME CBO_DESC CB_FTAGS P – CBFT_INDEX FU – CBF_INDEX U – TAG_NAME CB_GT_TO_FTS P – CBG2F_INDEX FU – CBGT_INDEX FU – CBFT_INDEX CB_INSTANCES P – CBI_NAME CBI_DESC CB_ALL_FTAGS P – CBFT_INDEX FU – CBF_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBFT_INSTIME CBFT_ROWCOUNT CBFT_LAST_OBJTIME CBFT_SINCE_TIME CBFT_UNTIL_TIME CB_NODES P – CBF_INDEX FU – CBO_NAME FU – CBS_NAME FU – CBI_NAME U – NODE_FULLPATH NODE_NAME NODE_ID NODE_PARENTID NODE_ISLEAF NODE_INSTIME LASTMOD_DATE CBF_SCHEMA_NAME CBF_MODIFY_DATE CBF_DESC CBF_TWIKI CB_FOLDERS PF – CBF_INDEX FU – CBO_NAME FU – CBS_NAME FU – CBI_NAME U – NODE_FULLPATH NODE_NAME NODE_DESCRIPTION FOLDER_VERSIONING CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT CB_GTAGS P – CBGT_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_INSTIME CBGT_RANK GRAY tables Not filled yet ! CB_GTAG_AMIS P – TAG_NAME IS_ACTIVE DATASET_COUNT Processing_Date_Range ? CB_GTAG_INFOS P – CBGTI_INDEX FU – TAG_NAME U – STATUS_SINCE STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK DEPLOYMENT_LOCATION CB_PAYLOADS P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC CB_GTAG_AMI_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ? CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME

28. COOL Browser Support Tables CB_ONOFFS P – CBO_NAME CBO_DESC CB_INSTANCES P – CBI_NAME CBI_DESC CB_SCHEMAS P – CBS_NAME CBS_SYSTEM CBS_DESC Too many channels CB_Folders.CHAN_COUNT contains the channel count CB_CHANNELS P – CBC_INDEX FU – CBF_INDEX U – CBC_CHANNEL CBC_NAME CB_NODES P – CBF_INDEX FU – CBO_NAME FU – CBS_NAME FU – CBI_NAME U – NODE_FULLPATH NODE_NAME NODE_ID NODE_PARENTID NODE_ISLEAF NODE_INSTIME LASTMOD_DATE CBF_MODIFY_DATE CBF_DESC CBF_TWIKI CB_FOLDERS PF – CBF_INDEX FU – CBO_NAME FU – CBS_NAME FU – CBI_NAME U – NODE_FULLPATH NODE_NAME NODE_DESCRIPTION FOLDER_VERSIONING CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT CB_PAYLOADS P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME