Oracle environment for a stardard parameter container in HYDRA

by Ilse Koenig, GSI Feb 26, 2002

Introduction

A standard parameter container in HYDRA needs a version management, often holds a large amount of data and has a tree-like structure. It may have different contexts (to distinguish between different valid versions for the same run). The data elements will probably not change anymore in future code releases.

For such containers I propose to use a partially standardized layout for the Oracle tables. This allows to write parts of the analysis interface code with cut-and-paste or to reuse code (even without using dynamic sql for performance reasons).
It is possible to use a generic WebDb GUI for validation and queries. This GUI uses dynamic sql to access and manipulate the data. But to manipulate the data without the need to write individual procedures and triggers, it needs standardized column names and types.

The Oracle tables, sequences and triggers

For each parameter container exist three tables in Oracle

the version table xxx_vers

the data table xxx_data

the time range table xxx

The first and third tables are standardized. They contain the same columns with the same names and types. Only the names of the table and of the constraints are different.

CREATE TABLE calpar_vers ( vers_id NUMBER(8) NOT NULL, date_create DATE DEFAULT SYSDATE, orig_context_id NUMBER(8) NOT NULL CONSTRAINT calpar_context_fk1 REFERENCES hydra.container_context (context_id), run_id NUMBER(10) NOT NULL, author VARCHAR2(80), description VARCHAR2(4000), CONSTRAINT calpar_vers_pk PRIMARY KEY (vers_id) USING INDEX PCTFREE 2 ); COMMENT ON TABLE calpar_vers IS 'List of versions for the TOF calibration parameters'; COMMENT ON COLUMN calpar_vers.vers_id IS 'Identifier for the version (provided by the sequence tof_par_vers_sequ)'; COMMENT ON COLUMN calpar_vers.date_create IS 'Creation date of this version (default sysdate)'; COMMENT ON COLUMN calpar_vers.orig_context_id IS 'Identifier for the context defined in the analysis before write'; COMMENT ON COLUMN calpar_vers.run_id IS 'Run Id used for initialization in the analysis before write (defines the maximum setup)'; COMMENT ON COLUMN calpar_vers.author IS 'Author of the parameter set'; COMMENT ON COLUMN calpar_vers.description IS 'Comment';

The data table is almost completly individual.
The columns for the parameters have the same name as the parameters, but capital letters are replaced by underscore + lowercase.

CREATE TABLE calpar_data ( rod_id NUMBER(4) NOT NULL CONSTRAINT rod_FK5 REFERENCES rod (rod_id), vers_id NUMBER(8) NOT NULL CONSTRAINT calpar_vers_FK1 REFERENCES calpar_vers (vers_id) ON DELETE CASCADE, left_k NUMBER(7,5) NOT NULL, right_k NUMBER(7,5) NOT NULL, v_group NUMBER(6,3) NOT NULL, pos_k NUMBER(7,3) NOT NULL, tim_k NUMBER(7,3) NOT NULL, pedestal_l NUMBER(7,3) NOT NULL, pedestal_r NUMBER(7,3) NOT NULL, att_len NUMBER(7,3) NOT NULL, gain_asym NUMBER(7,3) NOT NULL, edep_k NUMBER(9,5), CONSTRAINT calpar_data_PK PRIMARY KEY (rod_id,vers_id) USING INDEX PCTFREE 2 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0)) PCTUSED 90 PCTFREE 10 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0); CREATE INDEX calpar_data_I ON calpar_data (vers_id,rod_id) PCTFREE 0 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0); COMMENT ON TABLE calpar_data IS 'TOF calibration parameters'; COMMENT ON COLUMN calpar_data.rod_id IS 'Identifier for the TOF rod, references ROD.ROD_id'; COMMENT ON COLUMN calpar_data.vers_id IS 'Identifier for the version of the parameter set'; COMMENT ON COLUMN calpar_data.left_k IS 'Left time slope (ns/ch)'; COMMENT ON COLUMN calpar_data.right_k IS 'Right time slope (ns/ch)'; COMMENT ON COLUMN calpar_data.v_group IS 'Group velocity(cm/ns)'; COMMENT ON COLUMN calpar_data.pos_k IS 'Position offset (cm)'; COMMENT ON COLUMN calpar_data.tim_k IS 'Timing offset (ns)'; COMMENT ON COLUMN calpar_data.pedestal_l IS 'ADC left pedestal (ch)'; COMMENT ON COLUMN calpar_data.pedestal_r IS 'ADC right pedestal (ch)'; COMMENT ON COLUMN calpar_data.att_len IS 'Attenuation length (mm)'; COMMENT ON COLUMN calpar_data.gain_asym IS 'Gain asymmetry'; COMMENT ON COLUMN calpar_data.edep_k IS 'Energy deposited conversion factor';

The time range table has also a trigger on insert, which gets the next sequence number and sets the id. The sequence may be shared by several parameter containers.

CREATE TABLE calpar ( id NUMBER(8) NOT NULL, det_part_id NUMBER(1) NOT NULL CONSTRAINT tof_sector_fk4 REFERENCES hades_oper.sector (sector_id), vers_id NUMBER(8) NOT NULL CONSTRAINT calpar_vers_fk2 REFERENCES calpar_vers (vers_id), context_id NUMBER(8) NOT NULL CONSTRAINT calpar_context_fk2 REFERENCES hydra.container_context (context_id), valid_since DATE NOT NULL, valid_until DATE NOT NULL, date_create DATE DEFAULT SYSDATE, invalid_since DATE NOT NULL, comment_id NUMBER(8) CONSTRAINT tof_comment_fk9 REFERENCES tof_comment (tof_comment_id), CONSTRAINT calpar_pk PRIMARY KEY (id) USING INDEX PCTFREE 2 STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 50), CONSTRAINT calpar_unq UNIQUE (vers_id,det_part_id,context_id,valid_until,invalid_since) USING INDEX PCTFREE 2 STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 50)) PCTUSED 95 PCTFREE 5 STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 50); COMMENT ON TABLE calpar IS 'Valid calibration parameter versions (best parameter sets) for a TOF sector'; COMMENT ON COLUMN calpar.id IS 'Primary key (identifier for the entry)'; COMMENT ON COLUMN calpar.det_part_id IS 'Sector number (1..6), references HADES_OPER.SECTOR.SECTOR_ID'; COMMENT ON COLUMN calpar.vers_id IS 'Version of the parameters, references calpar_vers.vers_id'; COMMENT ON COLUMN calpar.context_id IS 'Id of parameter context, references hydra.container_context.context_id'; COMMENT ON COLUMN calpar.valid_since IS 'Date (Run start) when this parameter set gets valid'; COMMENT ON COLUMN calpar.valid_until IS 'Date (Run stop) when this parameter set gets invalid'; COMMENT ON COLUMN calpar.date_create IS 'Date when the entry was made'; COMMENT ON COLUMN calpar.invalid_since IS 'Date when this parameter set was (will be) replaced by a better one'; COMMENT ON COLUMN calpar.comment_id IS 'Identifier for the comment, references tof_comment.tof_comment_id'; CREATE SEQUENCE tof_par_sequ INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 NOCYCLE CACHE 6; CREATE TRIGGER trg_calpar_b_ins BEFORE INSERT ON calpar FOR EACH ROW DECLARE BEGIN SELECT tof_par_sequ.NEXTVAL INTO :new.id FROM DUAL; :new.date_create := SYSDATE; :new.invalid_since := hdate.high_date; END; / show errors

The following sequence is used to provide new versions for all version tables.

CREATE SEQUENCE tof_par_vers_sequ INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 NOCYCLE CACHE 5;

Package for the analysis

The following package is used by the analysis for filling.
Only the owner (here TOF_ANATEST) can get a new version.

CREATE  OR REPLACE PACKAGE tof_par_query AS

  FUNCTION next_version RETURN NUMBER;

  FUNCTION get_rod_id (
    secNum NUMBER,
    segNum NUMBER,
    rodNum NUMBER )
  RETURN NUMBER;

  PRAGMA RESTRICT_REFERENCES (next_version, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (get_rod_id, WNDS, WNPS, RNPS);

END tof_par_query;
/


CREATE OR REPLACE PACKAGE BODY tof_par_query AS

  FUNCTION next_version RETURN NUMBER IS
      vers NUMBER := -1;
    BEGIN
      IF USER = 'TOF_ANATEST' THEN 
        SELECT tof_par_vers_sequ.NEXTVAL INTO vers FROM DUAL;
      END IF;
      RETURN vers;
    END next_version;

  FUNCTION get_rod_id (
      secNum NUMBER,
      segNum NUMBER,
      rodNum NUMBER )
    RETURN NUMBER IS
      id rod.rod_id%TYPE;
    BEGIN
      id := -1;
      SELECT rod_id INTO id
        FROM module m, rod r
        WHERE sector_id = secNum
          AND segment = segNum
          AND m.module_id = r.module_id
          AND rod_number = rodNum;
      RETURN id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20101,'s='||secNum||' m='||segNum||' r='||rodNum||
                                     '   rod_id not found');
  END get_rod_id;

END tof_par_query;
/
show errors

Views for the analysis

CREATE OR REPLACE VIEW tof_calpar_vers_at_date
  ( sector, context_id, version, valid_since, valid_until )
AS
  SELECT det_part_id, context_id, vers_id, valid_since, valid_until
    FROM tof_anatest.calpar
    WHERE hades_oper.run_query.get_date BETWEEN valid_since AND valid_until
      AND hades_oper.run_query.get_history_date BETWEEN date_create AND invalid_since;

COMMENT ON TABLE tof_calpar_vers_at_date IS 'Calibration parameter versions for a TOF sector (used in HYDRA)';
COMMENT ON COLUMN tof_calpar_vers_at_date.sector IS 'Sector number (1..6)';
COMMENT ON COLUMN tof_calpar_vers_at_date.context_id IS 'Identifier for the parameter context';
COMMENT ON COLUMN tof_calpar_vers_at_date.version IS 'Version of the calibration parameters';
COMMENT ON COLUMN tof_calpar_vers_at_date.valid_since IS 'Date (Run start), when this version gets valid';
COMMENT ON COLUMN tof_calpar_vers_at_date.valid_until IS 'Last date (Run stop), when this version is still valid';

CREATE VIEW tof_calpar_data
  ( version, sector, module, rod,
    left_k, right_k, v_group, pos_k, tim_k,
    pedestal_l, pedestal_r, att_len, gain_asym, edep_k )
AS
  SELECT vers_id, sector_id, segment, rod_number,
         left_k, right_k, v_group, pos_k, tim_k,
         pedestal_l, pedestal_r, att_len, gain_asym, edep_k
    FROM module m, rod r, calpar_data d
    WHERE m.module_id = r.module_id and r.rod_id = d.rod_id;

COMMENT ON TABLE tof_calpar_data IS 'Tof calibration parameters (used in HYDRA)';
COMMENT ON COLUMN tof_calpar_data.version IS 'Version of the calibration parameters';
COMMENT ON COLUMN tof_calpar_data.sector IS 'Sector number (1..6)';
COMMENT ON COLUMN tof_calpar_data.module IS 'Module number (1..22)';
COMMENT ON COLUMN tof_calpar_data.rod IS 'Rod number (1..8)';
COMMENT ON COLUMN tof_calpar_data.left_k IS 'Left time slope (ns/ch)';
COMMENT ON COLUMN tof_calpar_data.right_k IS 'Right time slope (ns/ch)';
COMMENT ON COLUMN tof_calpar_data.v_group IS 'Group velocity(cm/ns)';
COMMENT ON COLUMN tof_calpar_data.pos_k IS 'Position offset (cm)';
COMMENT ON COLUMN tof_calpar_data.tim_k IS 'Timing offset (ns)';
COMMENT ON COLUMN tof_calpar_data.pedestal_l IS 'ADC left pedestal (ch)';
COMMENT ON COLUMN tof_calpar_data.pedestal_r IS 'ADC right pedestal (ch)';
COMMENT ON COLUMN tof_calpar_data.att_len IS 'Attenuation length (mm)';
COMMENT ON COLUMN tof_calpar_data.gain_asym IS 'Gain asymmetry';
COMMENT ON COLUMN tof_calpar_data.edep_k IS 'Energy deposited conversion factor';

Views for the WebDB GUI

The blue marked column names are mandatory. The GUI cannot work with other names.

CREATE VIEW hwpg_tof_calpar_parts
  ( vers_id, det_part_id )
as
  select distinct vers_id, sector_id
    from module m, rod r, calpar_data d
    where m.module_id = r.module_id and r.rod_id = d.rod_id;

COMMENT ON TABLE hwpg_tof_calpar_parts IS 'Tof sectors in calibration parameter versions (used in WebDB GUI)';
COMMENT ON COLUMN hwpg_tof_calpar_parts.vers_id IS 'Version of the calibration parameters';
COMMENT ON COLUMN hwpg_tof_calpar_parts.det_part_id IS 'Sector number';

CREATE VIEW hwpg_tof_calpar_data
  ( vers_id, det_part_id, sector, module, rod,
    left_k, right_k, v_group, pos_k, tim_k,
    pedestal_l, pedestal_r, att_len, gain_asym, edep_k )
AS
  select vers_id, sector_id, sector_id, segment, rod_number,
         left_k, right_k, v_group, pos_k, tim_k,
         pedestal_l, pedestal_r, att_len, gain_asym, edep_k
    from module m, rod r, calpar_data d
    where m.module_id = r.module_id and r.rod_id = d.rod_id;

COMMENT ON TABLE hwpg_tof_calpar_data IS 'Tof calibration parameters (used in WebDb GUI)';
COMMENT ON COLUMN hwpg_tof_calpar_data.vers_id IS 'Version of the calibration parameters';
COMMENT ON COLUMN hwpg_tof_calpar_data.det_part_id IS 'Sector number';
COMMENT ON COLUMN hwpg_tof_calpar_data.sector IS 'Sector number (1..6)';
COMMENT ON COLUMN hwpg_tof_calpar_data.module IS 'Module number (1..22)';
COMMENT ON COLUMN hwpg_tof_calpar_data.rod IS 'Rod number (1..8)';
COMMENT ON COLUMN hwpg_tof_calpar_data.left_k IS 'Left time slope (ns/ch)';
COMMENT ON COLUMN hwpg_tof_calpar_data.right_k IS 'Right time slope (ns/ch)';
COMMENT ON COLUMN hwpg_tof_calpar_data.v_group IS 'Group velocity(cm/ns)';
COMMENT ON COLUMN hwpg_tof_calpar_data.pos_k IS 'Position offset (cm)';
COMMENT ON COLUMN hwpg_tof_calpar_data.tim_k IS 'Timing offset (ns)';
COMMENT ON COLUMN hwpg_tof_calpar_data.pedestal_l IS 'ADC left pedestal (ch)';
COMMENT ON COLUMN hwpg_tof_calpar_data.pedestal_r IS 'ADC right pedestal (ch)';
COMMENT ON COLUMN hwpg_tof_calpar_data.att_len IS 'Attenuation length (mm)';
COMMENT ON COLUMN hwpg_tof_calpar_data.gain_asym IS 'Gain asymmetry';
COMMENT ON COLUMN hwpg_tof_calpar_data.edep_k IS 'Energy deposited conversion factor';

Grants

GRANT SELECT ON calpar_vers TO ana_query;
GRANT SELECT ON calpar_vers TO hades_ana;

GRANT SELECT ON calpar_data TO ana_query;
GRANT SELECT ON calpar_data TO hades_ana;

GRANT SELECT ON calpar TO ana_query;
GRANT SELECT ON calpar TO hades_ana;

GRANT SELECT ON tof_calpar_vers_at_date TO ana_query;
GRANT SELECT ON tof_calpar_vers_at_date TO hades_ana;

GRANT SELECT ON tof_calpar_data TO ana_query;
GRANT SELECT ON tof_calpar_data TO hades_ana;

GRANT EXECUTE ON tof_par_query TO ana_query;
GRANT EXECUTE ON tof_par_query TO hades_ana;

The following grant have to be made to the owner of the WebDB GUI:

Production: HANAL

Test: HYDRA

GRANT SELECT ON tof_comment TO hydra;
GRANT INSERT ON tof_comment TO hydra;
GRANT UPDATE ON tof_comment TO hydra;

GRANT SELECT ON calpar_vers TO hydra;
GRANT UPDATE ON calpar_vers TO hydra;
GRANT DELETE ON calpar_vers TO hydra;

GRANT SELECT ON calpar TO hydra;
GRANT UPDATE ON calpar TO hydra;
GRANT INSERT ON calpar TO hydra;

GRANT SELECT ON hwpg_tof_calpar_parts TO hydra;

GRANT SELECT ON hwpg_tof_calpar_data TO hydra;

the version table	xxx_vers
the data table	xxx_data
the time range table	xxx