EcalCondDB/sql/dcu_summary.sql

0001 /*
0002  * dcu_summary.sql
0003  *
0004  * A program to output a summary of the DCU data that has been
0005  * written to the online DB.  For every IOV and tag the number of
0006  * channels written to a data table is listed.
0007  */
0008
0009
0010 set linesize 1000;
0011 set pagesize 50000;
0012 set feedback off;
0013
0014 alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
0015
0016 DROP FUNCTION dcu_summary;
0017 DROP TYPE dcu_summary_t;
0018 DROP TYPE dcu_summary_o;
0019
0020
0021
0022 CREATE OR replace TYPE dcu_summary_o AS OBJECT (
0023   location varchar2(30),
0024   gen_tag varchar2(30),
0025   since date,
0026   till date,
0027   iov_id number(10),
0028   db_timestamp timestamp,
0029   capsule_temp_cnt number(10),
0030   capsule_temp_raw_cnt number(10),
0031   idark_cnt number(10),
0032   idark_ped_cnt number(10),
0033   vfe_temp_cnt number(10),
0034   lvr_temps_cnt number(10),
0035   lvrb_temps_cnt number(10),
0036   lvr_voltages_cnt number(10),
0037
0038   CONSTRUCTOR FUNCTION dcu_summary_o(x NUMBER)
0039     RETURN SELF AS RESULT
0040 );
0041 /
0042 show errors;
0043
0044
0045
0046 CREATE OR REPLACE TYPE BODY dcu_summary_o AS
0047   CONSTRUCTOR FUNCTION dcu_summary_o(x NUMBER)
0048     RETURN SELF AS RESULT
0049   AS
0050   BEGIN
0051   /* A null constructor */
0052     RETURN;
0053   END;
0054 END;
0055 /
0056 show errors;
0057
0058
0059
0060 CREATE OR REPLACE TYPE dcu_summary_t AS TABLE OF dcu_summary_o;
0061 /
0062 show errors;
0063
0064
0065
0066 CREATE OR replace FUNCTION dcu_summary RETURN dcu_summary_t PIPELINED IS
0067   sql_stmt varchar2(4000);
0068   summary dcu_summary_o := dcu_summary_o(NULL);
0069   TYPE table_data_t IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(32);
0070   table_data table_data_t;
0071   t VARCHAR2(32);  /* table name */
0072 BEGIN
0073
0074 /* Initialize table_data */
0075 table_data('DCU_CAPSULE_TEMP_DAT') := 0;
0076 table_data('DCU_CAPSULE_TEMP_RAW_DAT') := 0;
0077 table_data('DCU_IDARK_DAT') := 0;
0078 table_data('DCU_IDARK_PED_DAT') := 0;
0079 table_data('DCU_VFE_TEMP_DAT') := 0;
0080 table_data('DCU_LVR_TEMPS_DAT') := 0;
0081 table_data('DCU_LVRB_TEMPS_DAT') := 0;
0082 table_data('DCU_LVR_VOLTAGES_DAT') := 0;
0083
0084 /* Loop through the DCU runs */
0085 FOR result IN (select loc.location, tag.gen_tag, iov.since, iov.till, iov.iov_id, iov.db_timestamp
0086                  from location_def loc
0087                  join dcu_tag tag on tag.location_id = loc.def_id
0088                  join dcu_iov iov on iov.tag_id=tag.tag_id
0089                  order by loc.location asc, tag.gen_tag asc, iov.since asc)
0090 LOOP
0091   summary.location := result.location;
0092   summary.gen_tag := result.gen_tag;
0093   summary.since := result.since;
0094   summary.till := result.till;
0095   summary.iov_id := result.iov_id;
0096   summary.db_timestamp := result.db_timestamp;
0097
0098   /* Loop through all the data tables saving the number of channels written */
0099   t := table_data.FIRST;
0100   WHILE t IS NOT NULL
0101   LOOP
0102     sql_stmt := 'select count(*) from ' || t || ' where iov_id = :iov_id';
0103     EXECUTE IMMEDIATE sql_stmt INTO table_data(t) USING summary.iov_id;
0104     t := table_data.NEXT(t);
0105   END LOOP;
0106
0107   /* assign table_data to summary object elements */
0108   summary.capsule_temp_cnt     := table_data('DCU_CAPSULE_TEMP_DAT');
0109   summary.capsule_temp_raw_cnt := table_data('DCU_CAPSULE_TEMP_RAW_DAT');
0110   summary.idark_cnt            := table_data('DCU_IDARK_DAT');
0111   summary.idark_ped_cnt        := table_data('DCU_IDARK_PED_DAT');
0112   summary.vfe_temp_cnt         := table_data('DCU_VFE_TEMP_DAT');
0113   summary.lvrb_temps_cnt       := table_data('DCU_LVRB_TEMPS_DAT');
0114   summary.lvr_temps_cnt        := table_data('DCU_LVR_TEMPS_DAT');
0115   summary.lvr_voltages_cnt     := table_data('DCU_LVR_VOLTAGES_DAT');
0116
0117   PIPE ROW(summary);
0118 END LOOP;
0119 RETURN;
0120 END;
0121 /
0122 show errors;
0123
0124 /* Executes the summary program */
0125 SELECT * FROM TABLE(dcu_summary());