COMA Tables COnditions Metadat A for TAG DB
COMA Tables (COnditions Metadat. A for TAG DB) Elizabeth Gallas July 2010 Elizabeth Gallas - July 2010
Oracle Database: TAG DB and associated metadata tables n TAG DB Event-wise metadata tables n Stores Event-wise attributes: electron (Et, eta, phi …) n muon (Et, eta, phi …) … and references to RAW, ESD, AOD files ORACLE Database COMA tables n Official data processing chain: RAW ESD AOD TAG files RUNS n n ‘COMA’ (Conditions Metadat. A) tables n Conditions of data taking n n n Beam conditions Trigger and DAQ conditions Magnetic field … Various sources: Conditions DB, Log files, xml files, email… n n DATA Catalogue Tables Data Catalogue tables n Stores information on file and dataset processing and location n n Project name AMI tag (what processing occurred)… Sources: AMI (ATLAS Metadata Catalogue, Tier 0 … n Elizabeth Gallas - July 2010 TAG DB: Event-wise metadata Run LB Event …
Notes on the Run Metadata Schema n Warning: The schema tables, relations, content shown in the diagram n Reflect how I think the schema will evolve. n n 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 n Table names in the implementation have the common ‘COMA_’ prefix n n n Note: most but not all data is loaded from the Conditions Database or the Trigger Database Many tables adopt the RUN_INDEX column from RUNS n n Act as placeholders Cvs: offline/Database/Run. Lumi/Conditions. To. Tags/ddl/ The latest version is not guaranteed to be checked into cvs 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) n n Arrow direction indicate parent to child relationship (foreign key constraint) Example: Runs must use a TK_INDEX from TRIGKEYS (defining it’s trigger configuration) and must have DATA_SOURCE, RUN_TYPE and DETECTOR_MASK from those parent tables respectively. 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 n n Except for DETECTOR_MASK which follows the Conditions DB payload column name Stone gray tables and/or dashed relationships are empty and in development n n In this case, the child table includes additional columns to form its Primary (or Unique) Key(s) Relationships are indicated with arrows connecting the tables n n COMA COnditions database Metadat. A Tables with a single integer primary key have key column name *_INDEX n n “*” on table and column names Parts of the schema in flux https: //twiki. cern. ch/twiki/bin/view/Atlas/Cool. Online. Data … Elizabeth Gallas - July 2010
n Abbreviations which might be on the diagram n P == Primary Key (all are mandatory) n F == Foreign Key (can be optional) n (R) == Redundant (but useful) n M == Mandatory n O == Optional n n U == Comprise Unique Keys for this table n n Unique keys are constraints forcing uniqueness like PKs. ? == questionable value n n Note: columns are by default optional kept only for backward compatibility (MC) == populated only for MC n NULL values vs default values – n prefer NULL when unknown n Relationships: One to one One to many Many to many Elizabeth Gallas - July 2010 Stone gray tables: Not filled yet.
Conditions Metadat. A (COMA) Schema (1) RUN_TYPES DATA_SOURCES P - DATA_SOURCE_DESC P - RUN_TYPE_DESC DETECTOR_MASKS P - DETECTOR_MASK_DECODED RUN_EVENTS RUN_STREAM_EVENTS PF - RUN_INDEX P – PARTITION_NAME L 1_EVENTS L 2_EVENTS L 3_EVENTS RECORDED_EVENTS PF - RUN_INDEX P – STREAM_NAME STREAM_EVENTS STREAM_INDEX STREAM_PRESCALE RUN_STREAM_LBN_EVENTS PF - RUN_INDEX P – LBN P – STREAM_INDEX STREAM_EVENTS RUN_LOAD STATUS P – RUN_INDEX P - CONDITION_TYPE P – COLUMN_AFFECTED SEVERITY COLUMN_NEW COLUMN_OLD RUN_MAGNETS* RUN_INDEX LBN_BEGIN LBN_END SOL_STATE TOR_STATE RUN_FILLS* FILL_INDEX START_TIME END_TIME TBD FILL_INDEX RUN_INDEX LBN_BEGIN LBN_END SHORTNAMES Elizabeth Gallas - July 2010 P – PERIOD_NAME P – RUN_INDEX P – SHORTNAME U –ENTITIY_NAME ENTITY_TYPE P – PERIOD_NAME P – FILENAME_TAG PERIOD_RUN_STRING PERIOD_DESC RUN_LBN_TIMES P - RUN_INDEX P - LBN START_TIME END_TIME DURATION RUN_LBN_LUMS* P - RUN_INDEX P - LBN P – LUM_COOL_INDEX PF – LUM_CHANNEL INST_LUM COOL_CONNECTIONS DETSTATUS P - TRANS_TIME PU - TRANS_TYPE U - TRANS_NUMBER TRIGGERS, PRESCALES, TRIGGER CHAINS AGGREGATE PRESCALES STREAMS GROUPS PERIOD_RUNS P - RUN_INDEX F - DATA_SOURCE F - RUN_TYPE F - DETECTOR_MASK F - COOL_INDEX F - PRESCALE_INDEX F - TRIGKEY_INDEX * RUN_NUMBER FILENAME_TAG RECORDING_ENABLED CLEAN_STOP START_TIME END_TIME DURATION START_LBN END_LBN DAQ_CONFIGURATION F - FILL_INDEX MC_TRIG_KEY (MC%) U – AMITAG_NAME_MC (MC) TIME_TRANSITIONS See next slides for PERIODS RUNS PF - RUN_INDEX PF - COOL_INDEX PF - DETECTOR_INDEX P – LBN_BEGIN LBN_END CODE DEADFRAC THRUST DESCRIPTION P - COOL_INDEX COOL_IMPL COOL_SERVER COOL_SCHEMA COOL_INSTANCE U - COOL_TAG LOCK_DATE TAG_DESC DETSTATUS_DETECTORS P - DETECTOR_INDEX DETECTOR_DESC DETECTOR_LONG SYSTEM SUBSYSTEM TABLE_VALUE_EXPLAINS DETSTATUS_CODES P – TABLE_SN P – COL_NAME P – COL_VALUE COL_DESC P - CODE_DESC CODE_COLOR CODE_HEX LUM_DETS* P – LUM_CHANNEL_NAME LUM_CHANNEL_DESC LUM_SUBSYSTEM DETSTATUS_VFLAGS PF – VF_COOL_INDEX PF – VF_CHANNEL DESCRIPTION EXPRESSION VF_DETECTOR_STRING DETSTATUS_GROUPS DETSTATUS_VDETS P - DETECTOR_INDEX CDG_MIN CDG_MAX P – VF_CHANNEL_NAME VF_CHANNEL_DESC
Runs, Triggers, Prescales and (Run, Trig)_Aggregate Prescales Runs P - RUN_INDEX DATA_SOURCE RUN_TYPE START_TIME … F – PRESCALE_INDEX F - TRIGKEY_INDEX Master_Keys P – TRIGKEY_INDEX MK_DESCRIPTION SMT_NAME SMT_VERSION LVL 1_DESCRIPTION HLT_DESCRIPTION Prescale_Keys P – PRESCALE_INDEX Run_Agg_PS PF – PRESCALE_INDEX PF – TRIGGER_INDEX F – TRIGKEY_INDEX (R) MIN_PS MAX_PS PS_FLAG PT_FLAG Trig_Agg_PS RR_FLAG PF – LVL 1_PRESCALE_INDEX PF – HLT_PRESCALE_INDEX PF – TRIGGER_INDEX F - TRIGKEY_INDEX (R) AGG_PS PT_FLAG Elizabeth Gallas - July 2010 PF - PRESCALE_INDEX P – LBN_BEGIN P – LBN_END F - HLT_PRESCALE_INDEX F - LVL 1_PRESCALE_INDEX F – TRIGKEY_INDEX (R) HLT_PS_Keys 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 Prescale_Sets P - HLT_PRESCALE_INDEX LVL 1_PS_Keys P – LVL 1_PRESCALE_INDEX HLT_PRESCALES LVL 1_PRESCALES PF - LVL 1_PRESCALE_INDEX P(F) – CHAIN_COUNTER PRESCALE PF - HLT_PRESCALE_INDEX P(F) – TRIGGER_LEVEL P(F) – CHAIN_COUNTER PRESCALE PASSTHROUGH
Runs, Trigger Chain Tables, Run Aggregate Prescales Prescale_Sets Prescale_Keys P – PRESCALE_INDEX P – LBN_BEGIN LBN_END TRIGKEY_INDEX (R) LVL 1_PRESCALE_INDEX HLT_PRESCALE_INDEX P – PRESCALE_INDEX Master_Keys P – TRIGKEY_INDEX … PF – PRESCALE_INDEX PF – TRIGGER_INDEX F – TRIGKEY_INDEX (R) MIN_PS MAX_PS PS_FLAG PT_FLAG RR_FLAG MKT_Physics P - TRIGGER_INDEX F TRIGKEY_INDEX 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 Elizabeth Gallas - July 2010 P - RUN_INDEX DATA_SOURCE FILENAME_TAG RUN_NUMBER … Run_Agg_PS MK_Triggers Runs L 3_BIT L 2_BIT L 1_BIT L 3_NAME L 2_NAME L 1_NAME STREAM_INFO F – PRESCALE_INDEX F - TRIGKEY_INDEX MKT_Others P - TRIGGER_INDEX F TRIGKEY_INDEX L 3_BIT L 2_BIT L 1_BIT L 3_NAME L 2_NAME L 1_NAME STREAM_INFO ULCN MKT_PHYSICS: MKT_OTHERS “Physics (L 1 -L 2 -EF) chains” “Commissioning chains” replaces obsolete V_MK_Triggers (get ps from Run_Agg_PS) (Aggregate PS for these rows also in Run_Agg_PS)
Runs P - RUN_NUMBER DATA_SOURCE RUN_TYPE START_TIME … F – PRESCALE_INDEX F - TRIGKEY_INDEX Runs and Triggers and Streams Run_Stream_Events PF – RUN_INDEX P – STREAM_NAME STREAM_EVENTS F – STREAM_INDEX STREAM_PRESCALE Stone gray tables: Not filled yet. Stream_Keys Master_Keys P -- STREAM_INDEX P – TRIGKEY_INDEX MK_DESCRIPTION SMT_NAME SMT_VERSION LVL 1_DESCRIPTION HLT_DESCRIPTION STREAM_NAME STREAM_TYPE OBEYLB STREAM_DESC 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 Elizabeth Gallas - July 2010 TRIGGER_ELEMENTS GROUPS
Views V_RUN_DQTAGS RUN_INDEX COOL_TAG Elizabeth Gallas - July 2010
Old ideas Next slides contain old/obsolete n Tables n Views n Ideas n Pictures Elizabeth Gallas - July 2010
Obsolete Tables RUN_LBN_LUMS* P - RUN_INDEX P - LBN P - COOL_INDEX INST_LUM LIVE_FRACTION ? RUN_LBNS (OLD) • RUN_INDEX • LBN • START_TIME • END_TIME • DURATION • INST_LUM • LIVE_FRACTION • COOL_INDEX* TRIGKEYS 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 RUN_LBN_TRIGGERS • RUN_INDEX • LBN • L 3_BIT • L 3_ACCEPTS • L 2_ACCEPTS • L 1_ACCEPTS Elizabeth Gallas - July 2010 TRIGKEY_INDEX COOL_INDEX HLT_KEYNAME LVLV 1_KEYNAME TRIGKEY_DESC TRIGGERS TRIGKEY_INDEX TRIGGER_LEVEL CHAIN_COUNTER CHAIN_NAME CHAIN_VERSION STREAM_INFO PRESCALE_INITIAL PASS_THROUGH LOWER_CHAIN_NAME TRIGGER_ELEMENTS V_MK_TRIGGERS TRIGKEY_INDEX TRIGGER_INDEX STREAM L 3_BIT L 2_BIT L 1_BIT L 3_NAME L 2_NAME L 1_NAME L 3_VERSION L 2_VERSION L 1_VERSION L 3_IPRESCALE L 2_IPRESCALE L 1_IPRESCALE L 3_PASS_THROUGH L 2_PASS_THROUGH L 1_PASS_THROUGH L 3_RERUN_PRESCALE L 2_RERUN_PRESCALE
COMA Runs and Triggers – Nov 2009 RUNS RUN_NUMBER RUN_TYPE START_TIME END_TIME … TRIGKEY_INDEX TRIGKEYS TRIGKEY_INDEX MASTER_KEY HLT_PRESCALE_KEY LVL 1_PRESCALE_KEY TRIGGERS MASTER_KEY TRIGGER_LEVEL CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … Elizabeth Gallas - July 2010 • 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 Fundamental Secondary opts Other options LVL 1_PRESCALES HLT_PRESCALES STREAM_TAGS • STREAM_INDEX • STREAM_NAME • STREAM_TYPE • OBEY_LB GROUPS TRIGGER_ELEMENTS
COMA Triggers, Streams, Elements, Groups – Nov 2009 Fundamental Secondary opts Other options TRIGGERS P - TRIGGER_INDEX FU - MASTER_KEY_INDEX U - TRIGGER_LEVEL U - CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … 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). STREAM_TAGS • STREAM_INDEX • STREAM_NAME • STREAM_TYPE • OBEY_LB TRIGGER_ELEMENTS GROUPS Elizabeth Gallas - July 2010
COMA Runs and Triggers – Nov 2009 – 2 Options: RUNS P - RUN_NUMBER DATA_SOURCE RUN_TYPE … F - TRIGKEY_INDEX TRIGKEYS P - TRIGKEY_INDEX P - RUN_NUMBER P - START_LB P - END_LB TRIGGERS P - TRIGGER_INDEX FU - MASTER_KEY U - TRIGGER_LEVEL U - CHAIN_NAME OPTION 1 2 CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … RUNS P - RUN_NUMBER DATA_SOURCE RUN_TYPE … F - MASTER_KEY F - PRESCALE_INDEX PRESCALES P - PRESCALE_INDEX P - RUN_NUMBER P - START_LB P - END_LB MASTER_KEY HLT_PRESCALE_KEY LVL 1_PRESCALE_KEY TRIGGERS P - TRIGGER_INDEX U - MASTER_KEY U - TRIGGER_LEVEL U - CHAIN_NAME CHAIN_VERSION CHAIN_COUNTER LOWER_CHAIN_NAME … HLT_PRESCALE_KEY LVL 1_PRESCALES HLT_PRESCALES PF - LVL 1_PRESCALE_KEY PF – TRIGGER_INDEX PF - HLT_PRESCALE_KEY PF – TRIGGER_INDEX LVL 1_PRESCALES HLT_PRESCALES LVL 1_PRESCALE PASSTHROUGH ENABLE ? PF - LVL 1_PRESCALE_KEY PF – TRIGGER_INDEX PF - HLT_PRESCALE_KEY PF – TRIGGER_INDEX LVL 1_PRESCALE PASSTHROUGH ENABLE ? Questions: 1. Master Key Issue 1. Use negative MK for simulation ? 1. Idea rejected (more than 2 sources) ? 2. Add column indicating ‘Config. Source’ ? 1. Adds PK to 2 tables with MASTER_KEY (ugh!) 3. Alt to 2. Add pseudo MK table with source ? 2. Prescale type (int, float, string ? ) 3. Pass. Through possible values/meanings ? 4. Is Enable indicated by a prescale special value? Elizabeth Gallas - July 2010
- Slides: 14