CB COOL Browser Support Tables in COMA Conditions

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: COMP 200, MONP 200, OFLP 200 § § Accounts: ATLAS_(COOLONL, COOLOFL)_* § § Not: ATLAS_COOL_% (pre-July-2007) Tags: locked tags § § Not: TBDP 200, CMCP 200, PIXRUNCO, STRM 200, TGC 1, TMCP 200, ALLP 200, CORALDB 2, MDT 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: On. Off_Schema/Instance § Example: COOLONL_SCT/COMP 200 March 2012

2012 March: COOL_CB Conditions DB Management Metadata CB_Instances P – CBI_NAME CBI_DESC CB_Schemas P – CBS_NAME CBS_SYSTEM CBS_DESC 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_On. Offs CB_GT_TO_OIS P – CBG 2 O_INDEX FU – CBGT_INDEX FU – CBOI_INDEX TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME P – CBO_NAME CBO_DESC P – CBGT_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_RANK CBGT_INTEGRITY COMA_INS_DATE (t) COMA_UPD_DATE (t) CB_NODES CB_PColumns P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC COMA_INS_DATE (t) COMA_UPD_DATE (t) CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV 2 TAGTABLENAME FOLDER_CHANNELTABLENAME FOLDER_PAYLOAD_EXTREF COMA_INS_DATE (t) COMA_UPD_DATE (t) March 2012 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) CB_GTag_Infos CB_GTAGS CB_GT_TO_FTS P – CBG 2 F_INDEX FU – CBGT_INDEX FU – CBFT_INDEX CB_FTAGS P – CBFT_INDEX FU – CBF_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBFT_NODE_ID CBFT_ROWCOUNT CBFT_LAST_OBJTIME CBFT_SINCE_TIME CBFT_UNTIL_TIME COMA_INS_DATE (t) COMA_UPD_DATE (t) ? P – CBGTI_INDEX FU – TAG_NAME U – STATUS_SINCE STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK Deployment_Location s: GRAY table • Not filled les: Dashed tab d • Not create 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) CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ?

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)> --run. Select. Mode=<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/OFLP 200, COOLOFL_RPC/COMP 200) 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. March 2012

For each COOLSchema/Instance: Get all Nodes from COOL Get all Nodes from COMA Node (Folder, Folder. Set) Loading 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 Force. GTag. Update flag. YES Any COMA Nodes not in COOL ? Loop over all COOL Nodes Is this Node in COMA ? Insert into COMA: Node, Table Reference, Payload Column NO YES Is this a Folder. Set or a Folder ? NO YES Folder. Set: Has Last. Mod_Date changed? Folder: Has the # of channels changed ? YES NO Abort program ! Folder. Set metadata never changes Update: channel count Folder: Has Last. Mod_Date or Node_ID changed? NO YES Update COMA: all differences in Nodes columns, Table References, Payload Columns for this Folder March 2012 4

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

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

Backup … Obsolete schemas and other stuff … March 2012

September 2011: COMA_CB + Andrea Formica’s Tag tables CB_ONOFFS P – CBO_NAME CBO_DESC CB_SCHEMAS 2 P – CBS_NAME CBS_SYSTEM CBS_DESC 17 CB_INSTANCES P – CBI_NAME CBI_DESC 12 19652 CB_PAYLOADS P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN 2216 CB_NODES CB_FOLDERS P – CBF_INDEX PF – 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 NODE_NAME NODE_DESCRIPTION FOLDER_VERSIONING CBF_MODIFY_DATE CBF_DESC CBF_TWIKI CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT CBP_TYPE CBP_DESC 1590 CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV 2 TAGTABLENAME FOLDER_CHANNELTABLENAME 101865 GLOBAL_TAG 2 SYSTEM_TAG GLOBAL_TAG_INFO_ID STATUS_SINCE STATUS_UNTIL DEPLOYMENT_LOCATION STATUS_SUMMARY USED_FOR_TASK GLOBAL_TAG_ID March 2012 1590 493 GLOBAL_TAG_ID GLOBAL_TAG_DESCRIPTION GLOBAL_TAG_NAME GLOBAL_TAG_SYSTIME NEXTGLOBALTAG_GLOBAL_TAG_ID SYSTEM_TAG CLUSTER_NAME DB_NAME FOLDER_NAME SCHEMA_NAME GLOBAL_TAG_ID

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_INSTANCES P – CBI_NAME CBI_DESC CB_FTAGS CB_GT_TO_FTS P – CBFT_INDEX P – CBG 2 F_INDEX FU – CBF_INDEX U – TAG_NAME FU – CBGT_INDEX FU – CBFT_INDEX CB_ALL_FTAGS CB_NODES CB_FOLDERS P – CBFT_INDEX CB_GTAGS PF – CBF_INDEX FU – CBF_INDEX U – TAG_NAME FU – CBO_NAME FU – CBS_NAME FU – CBI_NAME U – NODE_FULLPATH TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME NODE_NAME NODE_ID NODE_PARENTID NODE_ISLEAF NODE_INSTIME LASTMOD_DATE NODE_NAME NODE_DESCRIPTION FOLDER_VERSIONING P – CBF_INDEX CBF_SCHEMA_NAME CBF_MODIFY_DATE CBF_DESC CBF_TWIKI CB_PAYLOADS P – CBP_INDEX CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT CB_REFERENCES CBP_TYPE CBP_DESC March 2012 FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV 2 TAGTABLENAME FOLDER_CHANNELTABLENAME P – CBGT_INDEX U – TAG_NAME TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_RANK CBAMI_GTAGS P – TAG_NAME IS_ACTIVE DATASET_COUNT DATE_FIRST_DATASET DATE_LAST_DATASET PF – CBF_INDEX FU – CBF_INDEX U – CBP_COLUMN CBFT_INSTIME CBFT_ROWCOUNT CBFT_LAST_OBJTIME CBFT_SINCE_TIME CBFT_UNTIL_TIME es ! l b ta yet Y d A R fille G t No CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ? CB_GTAG_INFOS P – CBGTI_INDEX FU – TAG_NAME U – STATUS_SINCE STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK DEPLOYMENT_LOCATION

CB_Instances P – CBI_NAME CBI_DESC CB_Schemas P – CBS_NAME CBS_SYSTEM CBS_DESC CB_On. Offs November 2011: COOL_CB Conditions DB Management Metadata CB_Owner_Instances P – CBOI_INDEX U – OWNER_NAME FU – CBI_NAME F – CBO_NAME FI – CBS_NAME U – COOL_SCHEMA P – CBO_NAME CBO_DESC CB_GT_TO_OIS P – CBG 2 O_INDEX FU – CBGT_INDEX FU – CBOI_INDEX TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CB_GTAGS CB_GTag_Infos P – CBGT_INDEX P – CBGTI_INDEX U – TAG_NAME FU – TAG_NAME U – STATUS_SINCE TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBGT_RANK CB_NODES P – CBF_INDEX CB_PColumns P – CBP_INDEX FU – CBF_INDEX U – CBP_COLUMN CBP_TYPE CBP_DESC CB_REFERENCES PF – CBF_INDEX FOLDER_IOVTABLENAME FOLDER_TAGTABLENAME FOLDER_IOV 2 TAGTABLENAME FOLDER_CHANNELTABLENAME FOLDER_PAYLOAD_EXTREF March 2012 FU – CBOI_INDEX U – NODE_FULLPATH CB_GT_TO_FTS I – NODE_NAME NODE_ID NODE_PARENTID NODE_ISLEAF NODE_INSTIME LASTMOD_DATE FU – CBGT_INDEX FU – CBFT_INDEX P – CBG 2 F_INDEX STATUS_UNTIL STATUS_SUMMARY NEXTGTAG_ID USED_FOR_TASK Deployment_Location s: GRAY table • Not filled les: Dashed tab d • Not create CBAMI_GTAGS CB_FTAGS CBF_LASTMOD_DATE P – CBFT_INDEX NODE_DESCRIPTION FOLDER_VERSIONING FU – CBF_INDEX U – TAG_NAME I – CBF_NODE_PATH CBF_IOV_BASE CBF_ATT_TYPE CBF_CHAN_COUNT CBF_PAYLOAD_COUNT TAG_LOCK_STATUS TAG_DESCRIPTION SYS_INSTIME CBFT_NODE_ID CBFT_ROWCOUNT CBFT_LAST_OBJTIME CBFT_SINCE_TIME CBFT_UNTIL_TIME P – TAG_NAME IS_ACTIVE DATASET_COUNT DATE_FIRST_DATASET DATE_LAST_DATASET CBAMI_GTAG_USES TAG_NAME Project_Name Run_Start Run_End AMITag_Array ?