
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 CREATE TABLE VPX_TOPN_PAST_DAY(
   COUNTER_ID NUMERIC(38,0) NOT NULL,
   SAMPLE_TIME TIMESTAMP NOT NULL,
   INTEGRAL_VALUE NUMERIC(38,0) NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_PAST_DAY ADD CONSTRAINT PK_VPX_TOPN_PAST_DAY PRIMARY KEY (COUNTER_ID,SAMPLE_TIME);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_PAST_DAY IS 'Corresponds to VPX_HIST_STAT1 and stores integral values at the lowest level of granularity (daily level) ' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_DAY.COUNTER_ID IS 'Counter Identifier - Defines statistic for a given device of an object' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_DAY.SAMPLE_TIME IS 'Date and time at lowest level of granularity (daily) - represents data with 5 minutes frequency of actuality' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_DAY.INTEGRAL_VALUE IS 'Integral Value' ;
 
 
 
 
 
 CREATE TABLE VPX_TOPN_PAST_WEEK(
   COUNTER_ID NUMERIC(38,0) NOT NULL,
   SAMPLE_TIME TIMESTAMP NOT NULL,
   INTEGRAL_VALUE NUMERIC(38,0) NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_PAST_WEEK ADD CONSTRAINT PK_VPX_TOPN_PAST_WEEK PRIMARY KEY (COUNTER_ID,SAMPLE_TIME);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_PAST_WEEK IS 'Corresponds to VPX_HIST_STAT2 and stores integral values at the weekly level of granularity' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_WEEK.COUNTER_ID IS 'Counter Identifier - Defines statistic for a given device of an object' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_WEEK.SAMPLE_TIME IS 'Date and time at weekly level of granularity - represents data with 30 minutes frequency of actuality' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_WEEK.INTEGRAL_VALUE IS 'Integral Value' ;
 
 
 
 
 
 CREATE TABLE VPX_TOPN_PAST_MONTH(
   COUNTER_ID NUMERIC(38,0) NOT NULL,
   SAMPLE_TIME TIMESTAMP NOT NULL,
   INTEGRAL_VALUE NUMERIC(38,0) NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_PAST_MONTH ADD CONSTRAINT PK_VPX_TOPN_PAST_MONTH PRIMARY KEY (COUNTER_ID,SAMPLE_TIME);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_PAST_MONTH IS 'Corresponds to VPX_HIST_STAT3 and stores integral values at the monthly level of granularity' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_MONTH.COUNTER_ID IS 'Counter Identifier - Defines statistic for a given device of an object' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_MONTH.SAMPLE_TIME IS 'Date and time at monthly level of granularity - represents data with 2 hours frequency of actuality' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_MONTH.INTEGRAL_VALUE IS 'Integral Value' ;
 
 
 
 
 
 CREATE TABLE VPX_TOPN_PAST_YEAR(
   COUNTER_ID NUMERIC(38,0) NOT NULL,
   SAMPLE_TIME TIMESTAMP NOT NULL,
   INTEGRAL_VALUE NUMERIC(38,0) NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_PAST_YEAR ADD CONSTRAINT PK_VPX_TOPN_PAST_YEAR PRIMARY KEY (COUNTER_ID,SAMPLE_TIME);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_PAST_YEAR IS 'Corresponds to VPX_HIST_STAT4 and stores integral values at the yearly level of granularity' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_YEAR.COUNTER_ID IS 'Counter Identifier - Defines statistic for a given device of an object' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_YEAR.SAMPLE_TIME IS 'Date and time at yearly level of granularity - represents data with 24 hours frequency of actuality' ;
 COMMENT ON COLUMN VPX_TOPN_PAST_YEAR.INTEGRAL_VALUE IS 'Integral Value' ;
 
 
 
 
 
 CREATE TABLE VPX_TOPN_STATISTICS(
   STAT_ID NUMERIC(38,0) NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_STATISTICS ADD CONSTRAINT PK_VPX_TOPN_STATISTICS PRIMARY KEY (STAT_ID);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_STATISTICS IS 'Contain all the statistics that will support Top N feature' ;
 COMMENT ON COLUMN VPX_TOPN_STATISTICS.STAT_ID IS 'Statistic Identifier' ;
 
 
 
 
 
 CREATE TABLE VPX_TOPN_STATE(
   TIME_LEVEL NUMERIC(2,0) NOT NULL
         CONSTRAINT CK_VPX_TOPN_STATE CHECK (TIME_LEVEL IN (1, 2, 3, 4)),
   SAMPLE_TIME TIMESTAMP NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_TOPN_STATE ADD CONSTRAINT PK_VPX_TOPN_STATE PRIMARY KEY (TIME_LEVEL);
 
 
 
 
 
 COMMENT ON TABLE VPX_TOPN_STATE IS 'Describes current state and time boundary of calculated integral values and is intended to support incremental calculation process and efficient recalculations and recovery' ;
 COMMENT ON COLUMN VPX_TOPN_STATE.TIME_LEVEL IS 'Level of Granularity:1 - Daily 2 - Weekly 3 - Monthly 4 - Yearly' ;
 COMMENT ON COLUMN VPX_TOPN_STATE.SAMPLE_TIME IS 'Date and time corresponding with VC performance statistics one for the latest calculation' ;
 
 
 
 
 
 CREATE TABLE VPX_JOB_LOG(
   JOB_ID NUMERIC(5,0) NOT NULL,
   STATUS VARCHAR(10) DEFAULT 'WAITING' NOT NULL,
   LAST_RUN Timestamp(3) DEFAULT TO_TIMESTAMP('01.01.1900 00:00:00', 'DD.MM.YYYY HH24:MI:SS') NOT NULL
 );
 
 
 
 
 
 ALTER TABLE VPX_JOB_LOG ADD CONSTRAINT PK_VPX_JOB_LOG PRIMARY KEY (JOB_ID);
 
 
 
 
 
 COMMENT ON TABLE VPX_JOB_LOG IS 'Automated Jobs Log' ;
 COMMENT ON COLUMN VPX_JOB_LOG.JOB_ID IS 'Job Identifier' ;
 COMMENT ON COLUMN VPX_JOB_LOG.STATUS IS 'Working state/Status' ;
 COMMENT ON COLUMN VPX_JOB_LOG.LAST_RUN IS 'Date and time of the last job run' ;
 
 
 
 
 
 INSERT INTO VPX_JOB_LOG VALUES (1);
 INSERT INTO VPX_JOB_LOG VALUES (2);
 INSERT INTO VPX_JOB_LOG VALUES (3);
 INSERT INTO VPX_JOB_LOG VALUES (4);
 
