CB = COOL Browser Support Tables in COMA (Conditions DB Management Metadata)

1. CB = COOL Browser Support Tables in COMA(Conditions DB Management Metadata) • All COMA tables start with prefix ‘COMA_’ • CB is my shortname for ‘Conditions Browser’ part of the COMA schema • Loading restrictions • Instances: COMP200, MONP200, OFLP200 • Not: TBDP200, CMCP200, PIXRUNCO, STRM200, TGC1, TMCP200, ALLP200, CORALDB2, MDT • Accounts: ATLAS_(COOLONL,COOLOFL)_* • Not: ATLAS_COOL_% (pre-July-2007) • Tags: locked tags • Not unlocked or partially locked tags (?) • CB_Nodes and CB_Folders table content might seem redundant … • Optimizes storage • Helps interfaces to reduce joins • Current Row counts: See blue boxes • COOL Accounts: OnOff_Schema/Instance • Example: COOLONL_SCT/COMP200

2. 2012 March: COOL_CB Conditions DB Management Metadata CB_Instances P – CBI_NAME CBI_DESC CB_GTag_Infos P – CBGTI_INDEX FU – TAG_NAME U – STATUS_SINCE STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK Deployment_Location CB_Owner_Instances P – CBOI_INDEX U – OWNER_NAME FU – CBI_NAME F – CBO_NAME FI – CBS_NAME U – COOL_SCHEMA COMA_INS_DATE (t) CB_GTAGS P – CBGT_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_INSTIME CBGT_RANK CBGT_INTEGRITY COMA_INS_DATE (t) COMA_UPD_DATE (t) CB_GT_TO_OIS P – CBG2O_INDEX FU – CBGT_INDEX FU – CBOI_INDEX TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME ? CB_Schemas P – CBS_NAME CBS_SYSTEM CBS_DESC CB_OnOffs P – CBO_NAME CBO_DESC 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) • GRAY tables: • Not filled • Dashed tables: • Not created CB_PColumns P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC COMA_INS_DATE (t) COMA_UPD_DATE (t) CB_GT_TO_FTS P – CBG2F_INDEX FU – CBGT_INDEX FU – CBFT_INDEX CBAMI_GTAGS P – CBAGT_INDEX U – TAG_NAME U – PROJECT_PREFIX IS_ACTIVE DATASET_COUNT DATE_FIRST_DATASET DATE_LAST_DATASET COMA_INS_DATE (t) COMA_UPD_DATE (t) 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) CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME FOLDER_PAYLOAD_EXTREF COMA_INS_DATE (t) COMA_UPD_DATE (t) CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ?

3. COMA_CB Program help/options • ./load_COMA_CBrowser.py --help This program loads instance, schema, folder, channel, payload information into the Conditions Metadata tables in an Oracle database. The information is extracted from the ATLAS Conditions DB from the COOL tables for schemas not excluded by the program. On insufficient input, the program will terminate with a message on what is missing. Usage: load_COMA_CBrowser.py {<options>} Mandatory Arguments are: --oracledb = <Oracle COMA Schema connection string (writer)> --oraclecool = <Oracle Trigger Schema connection string (reader)> --nrun = <number of cool schemas to load)> --runSelectMode=<string directing CB loading> ALL --> load everything below Owners --> load Schema, Instance, Owner related CB tables Nodes --> load CB Nodes table (including all Folders) FTags --> load CB Folder Tags GTags --> load CB Global Tags Optional Arguments are: --coolschema = comma separated schema_instance names (i.e. COOLOFL_LAR/OFLP200,COOLOFL_RPC/COMP200) if absent, all will be inserted/updated Many general arguments inherited from the parent class are ignored. Useful ones for COMA_CB are the --quiet and --rollback arguments.

4. Node (Folder, FolderSet) Loading For each COOLSchema/Instance:  Get all Nodes from COOL  Get all Nodes from COMA Delete from COMA: FTag(s), Table Reference, Payload Columns, Node for all COMA Nodes not in COOL. If ANY FTags are deleted with GTags, then set the ForceGTagUpdate flag. YES Any COMA Nodes not in COOL ? Loop over all COOL Nodes Insert into COMA: Node, Table Reference, Payload Column NO Is this Node in COMA ? YES Is this a FolderSet or a Folder ? Abort program ! FolderSet metadata never changes NO YES FolderSet: Has LastMod_Date changed? Folder: Has the # of channels changed ? YES Update: channel count NO Folder: Has LastMod_Date or Node_ID changed? NO YES Update COMA: all differences in Nodes columns, Table References, Payload Columns for this Folder

5. Folder Tag Loading For each COOLSchema/Instance: For each MultiVersion Folder  Get all FTags from COOL  Get all FTags from COMA Delete from COMA: FTags and any GTag-to-FTag associations of all COMA FTags not in COOL Any COMA FTag NOT in COOL ? YES Insert into COMA: New FTag Loop over all COOL FTags Is this FTag in COMA ? NO YES Is FTag TagLockStatus == 0 (unlocked) ? or Any change in TagLockStatus ? or Any change in TagDescription ? NO YES Update COMA: update all changed FTag metadata for this FTag

6. Global Tag Loading • For each COOLSchema/Instance: • Get all GTags from COOL (exclude early GTags as agreed) •  Get all GTags from COMA Delete from COMA: GTags and any GTag-to-FTag associations and any GTag-to-Schema associations of all COMA GTags not in COOL Any COMA GTag NOT in COOL ? YES Loop over all COOL GTags Insert into COMA: New GTag Is this GTag in COMA ? NO YES Update COMA: Update GTag Insert time to the earlier time Does this Schema have an earlier GTag insert date than currently in COMA ? YES YES NO Is GTag TagLockStatus == 0 (unlocked) ? or Any change in TagLockStatus ? or Any change in TagDescription ? NO YES Loop over all COOL Multi Version Folders Delete COMA: Delete any GTag-to-FTag associations which no longer exist. Insert COMA: Insert any new GTag-to-FTag associations

7. Backup … Obsolete schemas and other stuff …

8. September 2011: COMA_CB + Andrea Formica’s Tag tables CB_SCHEMAS P – CBS_NAME CBS_SYSTEM CBS_DESC 19652 17 12 CB_INSTANCES P – CBI_NAME CBI_DESC 2 CB_ONOFFS P – CBO_NAME CBO_DESC CB_PAYLOADS P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC 1590 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 2216 1590 CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME 101865 GLOBAL_TAG2SYSTEM_TAG SYSTEM_TAG CLUSTER_NAME DB_NAME FOLDER_NAME SCHEMA_NAME GLOBAL_TAG_ID GLOBAL_TAG_INFO GLOBAL_TAG_INFO_ID STATUS_SINCE STATUS_UNTIL DEPLOYMENT_LOCATION STATUS_SUMMARY USED_FOR_TASK GLOBAL_TAG_ID GLOBAL_TAG GLOBAL_TAG_ID GLOBAL_TAG_DESCRIPTION GLOBAL_TAG_NAME GLOBAL_TAG_SYSTIME NEXTGLOBALTAG_GLOBAL_TAG_ID 493

9. October 2011: 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 ! CBAMI_GTAGS P – TAG_NAME IS_ACTIVE DATASET_COUNT DATE_FIRST_DATASET DATE_LAST_DATASET 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 CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ? CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME

10. November 2011: COOL_CB Conditions DB Management Metadata CB_Instances P – CBI_NAME CBI_DESC CB_Owner_Instances P – CBOI_INDEX U – OWNER_NAME FU – CBI_NAME F – CBO_NAME FI – CBS_NAME U – COOL_SCHEMA CB_GTAGS P – CBGT_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_INSTIME CBGT_RANK CB_GTag_Infos P – CBGTI_INDEX FU – TAG_NAME U – STATUS_SINCE STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK Deployment_Location CB_GT_TO_OIS P – CBG2O_INDEX FU – CBGT_INDEX FU – CBOI_INDEX TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CB_Schemas P – CBS_NAME CBS_SYSTEM CBS_DESC CB_OnOffs P – CBO_NAME CBO_DESC 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 NODE_DESCRIPTION FOLDER_VERSIONING I – CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT • GRAY tables: • Not filled • Dashed tables: • Not created CB_PColumns P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC CB_GT_TO_FTS P – CBG2F_INDEX FU – CBGT_INDEX FU – CBFT_INDEX CBAMI_GTAGS P – TAG_NAME IS_ACTIVE DATASET_COUNT DATE_FIRST_DATASET DATE_LAST_DATASET 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 CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV2TAGTABLENAME FOLDER_CHANNELTABLENAME FOLDER_PAYLOAD_EXTREF CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ?