Conditions - CMSROMA/MTDDB GitHub Wiki

Conditions are data collected during activities done on a part. Conditions can be collected either during construction or while running. At the moment, construction conditions are supported.

In order to define conditions generated during an activity you must assign to each condition:

  1. its name
  2. its type

Each activity can provide more than one condition. For each activity generating one or more conditions, a table must be created in the DB. Such a table has at least three columns. The first two columns must be

  1. RECORD_ID
  2. CONDITION_DATA_SET_ID both NOT NULL and of type NUMBER(38). The following columns have a name corresponding to the generated condition and its corresponding type. Column RECORD_ID is the table primary key (PK), while CONDITION_DATA_SET_ID is a foreign key (FK), linking the conditions to the corresponding Dataset id.

Condition tables are stored in tablespace CMS_MTD_PROCERNIT_DATA.

We create an index for the PK and one for the FK. Finally we grant reading access to everyone.

gen_create_condition_sql.pl

gen_create_condition_sql.pl takes care of generating the SQL commands needed to create a condition table. The SQL statements are printed on screen. The user can then redirect the output to a file and execute it as an SQL procedure. The script uses a template in btl_conditions_template.sql.

Adding conditions to parts

In order for a part to be associated to a condition the following steps must be followed.

  1. A row must be inserted in CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS with, at least, the following columns:

    • NAME: the condition given name. Such a name must be agreed with people providing the data and must reflect its nature.
    • EXTENSION_TABLE_NAME: the name of the table aimed at storing actual data (the one defined above).
    • COMMENT_DESCRIPTION: an optional long description of the condition

  2. Associate the newly created condition to the parts for which it makes sense. Add a row in CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS with the following fields:

    • KIND_OF_PART_ID: the id of the kind of part for which the condition is intended
    • KIND_OF_CONDITION_ID: the id of the newly created condition to associate with the latter

  3. When performing an activity that generates conditions, do the following.

    • Insert a row in CMS_MTD_CORE_COND.COND_RUNS specifying
      • RUN_NAME or the pair RUN_TYPE/RUN_NUMBER
      • COMMENT_DESCRIPTION
      • LOCATION

  4. As soon as the activity finishes, update CMS_MTD_CORE_COND.COND_RUNS to reflect the end of a run.

  5. Insert a new row in CMS_MTD_CORE_COND.COND_DATA_SETS that specify:

    • PART_ID: the id of the part subject to the activity
    • COND_RUN_ID: the id of the run during which data were collected (the id - COND_RUN_ID - of the row inserted at point 2 above in table COND_RUNS
    • KIND_OF_CONDITION_ID: the id of the generated condition data (the primary key of the KINDS_OF_CONDITIONS table referring to the actual condition data)

  6. Insert a new row in the extension table defined in the KINDS_OF_CONDITIONS table corresponding to the above KIND_OF_CONDITION_ID in which you insert actual data. In this table

    • RECORD_ID is a primary key and is set automatically
    • CONDITION_DATA_SET_ID corresponds to the column with the same name of table CMS_MTD_CORE_COND.COND_DATA_SETS

Getting the conditions

Condition can be accessed using the account CMS_MTD_PRTYP_TMING_RDR. Few useful queries follows.

Get data for a given barcode:

SELECT P.PART_ID, K.DISPLAY_NAME FROM CMS_MTD_CORE_CONSTRUCT.PARTS P JOIN 
                                      CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS K 
                                   ON K.KIND_OF_PART_ID = P.KIND_OF_PART_ID 
                                WHERE BARCODE LIKE '%175';

Get conditions collected for a part:

SELECT C2P.DISPLAY_NAME, KC.NAME, KC.EXTENSION_TABLE_NAME
       FROM CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS C2P
       JOIN CMS_MTD_CORE_CONSTRUCT.PARTS P ON P.KIND_OF_PART_ID	= C2P.KIND_OF_PART_ID
       JOIN CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS K ON K.KIND_OF_PART_ID = P.KIND_OF_PART_ID
       JOIN CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS KC ON	KC.KIND_OF_CONDITION_ID	= C2P.KIND_OF_CONDITION_ID
      WHERE P.BARCODE LIKE '%175';

Get a specific condition (e.g. LY_XTALK) for all parts:

SELECT KOC.NAME, DS.PART_ID, P.BARCODE, KOP.DISPLAY_NAME, R.RUN_TYPE, R.RUN_NAME, R.RUN_BEGIN_TIMESTAMP,
        R.RUN_END_TIMESTAMP, R.LOCATION, R.COMMENT_DESCRIPTION, R.INITIATED_BY_USER,
        X.* FROM CMS_MTD_TMING_COND.LY_XTALK X
        JOIN CMS_MTD_CORE_COND.COND_DATA_SETS DS ON DS.CONDITION_DATA_SET_ID = X.CONDITION_DATA_SET_ID
        JOIN CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS KOC ON KOC.KIND_OF_CONDITION_ID = DS.KIND_OF_CONDITION_ID
        JOIN CMS_MTD_CORE_COND.COND_RUNS R ON R.COND_RUN_ID = DS.COND_RUN_ID
        JOIN CMS_MTD_CORE_CONSTRUCT.PARTS P ON P.PART_ID = DS.PART_ID
        JOIN CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS KOP ON KOP.KIND_OF_PART_ID = P.KIND_OF_PART_ID;

Storing conditions

By default, storing the conditions is a task for the db-loader. During the preparation phase a custom db-loader has been developed running on oms-mtd: a virtual machine created on OpentStack.

The temporary db-loader wakes up each night and checks every second if there is a new file in /home/dev/dbloader/xml. If so, it calls an event handler to process it, interpret its content and generate the SQL queries needed to insert data into the DB. Queries are automatically executed and operations are logged in /home/dev/dblaoder/logs. One processed, files are moved from /home/dev/dbloader/xml to /home/dev/dbloader/history.

The db-loader is a script called mdtdbloader.py running as a crontab job on oms-mtd. The script exits if finds a file called STOP in the xml directory.