pg_setup.sql

CREATE TABLE DIM_UI_SBM (
    LETO_UI_SBM_ID       SERIAL,
    SUB_PRJ_ID           NUMERIC(20) NOT NULL,
    LETO_UI_SBM_TYP      VARCHAR(25) NOT NULL,
    UI_TST_SBM_SUB_TYP   VARCHAR(25),
    EXC_STU              VARCHAR(30),
	HPQC_USR_NM          VARCHAR(50),
    MTA_EFC_DTE          TIMESTAMP,
    MTA_UPT_DTE          TIMESTAMP,
    CRT_USR_ID           VARCHAR(15),
    UPT_USR_ID           VARCHAR(15)
);

CREATE INDEX DIM_UI_SBM_X1 ON
    DIM_UI_SBM ( SUB_PRJ_ID ASC );

CREATE INDEX DIM_UI_SBM_X2 ON
    DIM_UI_SBM ( CRT_USR_ID ASC );
	
ALTER TABLE DIM_UI_SBM ADD CONSTRAINT DIM_UI_SBM_PK PRIMARY KEY ( LETO_UI_SBM_ID );

CREATE TABLE DIM_HPQC_TST_SET (
    LETO_UI_SBM_ID   INT NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_SET_NM       VARCHAR(100),
    PRJ_FOL_PAH      VARCHAR(500),
    CYL_NM           VARCHAR(15),
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_HPQC_TST_SET_X1 ON
    DIM_HPQC_TST_SET ( LETO_UI_SBM_ID ASC );

ALTER TABLE DIM_HPQC_TST_SET ADD CONSTRAINT DIM_HPQC_TST_SET_PK PRIMARY KEY ( LETO_UI_SBM_ID,
TST_SET_ID );

CREATE TABLE DIM_TST_INST (
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   INT NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_INST_NM      VARCHAR(100),
    EXC_STU          VARCHAR(30),
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_TST_INST_X1 ON
    DIM_TST_INST ( LETO_UI_SBM_ID ASC,
    TST_SET_ID ASC );

ALTER TABLE DIM_TST_INST
    ADD CONSTRAINT DIM_TST_INST_PK PRIMARY KEY ( TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID );
	
ALTER TABLE DIM_HPQC_TST_SET
    ADD CONSTRAINT DIM_HPQC_TST_SET_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );
		
ALTER TABLE DIM_TST_INST
    ADD CONSTRAINT DIM_TST_INST_F1 FOREIGN KEY ( LETO_UI_SBM_ID,
    TST_SET_ID )
        REFERENCES DIM_HPQC_TST_SET ( LETO_UI_SBM_ID,
        TST_SET_ID );
		
CREATE TABLE UI_EXC_TRG (
    UI_EXC_TRG_ID         SERIAL NOT NULL,
    LETO_UI_SBM_ID        INT NOT NULL,
    SUB_PRJ_ID            NUMERIC(20) NOT NULL,
    UI_EXC_TRG_TYP        VARCHAR(20),
    TST_EXC_TRG_SUB_TYP   VARCHAR(15),
    MTA_EFC_DTE           TIMESTAMP,
    CRT_USR_ID            VARCHAR(15)
);

CREATE INDEX UI_EXC_TRG_X1 ON
    UI_EXC_TRG ( LETO_UI_SBM_ID ASC );
	
ALTER TABLE UI_EXC_TRG ADD CONSTRAINT UI_EXC_TRG_PK PRIMARY KEY ( UI_EXC_TRG_ID );

ALTER TABLE UI_EXC_TRG
    ADD CONSTRAINT UI_EXC_TRG_F1 FOREIGN KEY ( LETO_UI_SBM_ID )
        REFERENCES DIM_UI_SBM ( LETO_UI_SBM_ID );

CREATE TABLE FACT_TST_EXC (
    TST_EXC_ID        SERIAL NOT NULL,
    JIRA_CDA_MOV_ID   INT NOT NULL,
    UI_EXC_TRG_ID     INT NOT NULL,
    LETO_UI_SBM_ID    INT NOT NULL,
    EXC_STT_TME       TIMESTAMP,
    EXC_END_TME       TIMESTAMP,
    TOT_EXC_TME       NUMERIC(10,2),
    TST_EXC_RSL       VARCHAR(50),
    MTA_EFC_DTE       TIMESTAMP,
    CRT_USR_ID        VARCHAR(15)
);

CREATE INDEX FACT_TST_EXC_X2 ON
    FACT_TST_EXC ( UI_EXC_TRG_ID ASC );

ALTER TABLE FACT_TST_EXC ADD CONSTRAINT FACT_TST_EXC_PK PRIMARY KEY ( TST_EXC_ID );		
		
ALTER TABLE FACT_TST_EXC
    ADD CONSTRAINT FACT_TST_EXC_F2 FOREIGN KEY ( UI_EXC_TRG_ID )              
        REFERENCES UI_EXC_TRG ( UI_EXC_TRG_ID );
		
CREATE TABLE FACT_TST_INST_LOG (
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   INT NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    TST_EXC_ID       INT NOT NULL,
    TST_RSL_ID       SERIAL NOT NULL,
    TST_RSL_FIL_NM   VARCHAR(30),
    TST_STP_RSL      VARCHAR(30),
    MTA_EFC_DTE      TIMESTAMP,
    TOT_EXC_TME      NUMERIC(10,2)
);

CREATE INDEX FACT_TST_INST_LOG_X1 ON
    FACT_TST_INST_LOG (
        TST_INST_ID
    ASC,
        LETO_UI_SBM_ID
    ASC,
        TST_SET_ID
    ASC );

CREATE INDEX FACT_TST_INST_LOG_X2 ON
    FACT_TST_INST_LOG ( TST_EXC_ID ASC );
	
ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_PK PRIMARY KEY ( TST_RSL_ID,
    TST_INST_ID,
    LETO_UI_SBM_ID,
    TST_SET_ID );	
	
ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_F1 FOREIGN KEY ( TST_INST_ID,     
    LETO_UI_SBM_ID,
    TST_SET_ID )
        REFERENCES DIM_TST_INST ( TST_INST_ID,
        LETO_UI_SBM_ID,
        TST_SET_ID );
		
ALTER TABLE FACT_TST_INST_LOG
    ADD CONSTRAINT FACT_TST_INST_LOG_F2 FOREIGN KEY ( TST_EXC_ID )          
        REFERENCES FACT_TST_EXC ( TST_EXC_ID );
		
CREATE TABLE DIM_TST_INST_ATT (
    HPQC_ATT_ID      NUMERIC(20) NOT NULL,
    TST_INST_ID      NUMERIC(20) NOT NULL,
    LETO_UI_SBM_ID   INT NOT NULL,
    TST_SET_ID       NUMERIC(20) NOT NULL,
    ATT_NM           VARCHAR(30) NOT NULL,
    ATT_FIL_TYP      VARCHAR(15),
    ATT_FIL          BYTEA,
    MTA_EFC_DTE      TIMESTAMP,
    MTA_UPD_DTE      TIMESTAMP,
    CRT_USR_ID       VARCHAR(15),
    UPT_USR_ID       VARCHAR(15)
);

CREATE INDEX DIM_TST_INST_ATT_X1 ON
    DIM_TST_INST_ATT (
        TST_INST_ID
    ASC,
        LETO_UI_SBM_ID
    ASC,
        TST_SET_ID
    ASC );

ALTER TABLE DIM_TST_INST_ATT ADD CONSTRAINT DIM_TST_INST_ATT_PK PRIMARY KEY ( HPQC_ATT_ID );


CREATE OR REPLACE FUNCTION notify_event() RETURNS TRIGGER AS $$

    DECLARE 
        data json;
        notification json;
    
    BEGIN
    
        -- Convert the old or new row to JSON, based on the kind of action.
        -- Action = DELETE?             -> OLD row
        -- Action = INSERT or UPDATE?   -> NEW row
        IF (TG_OP = 'DELETE') THEN
            data = row_to_json(OLD);
        ELSE
            data = row_to_json(NEW);
        END IF;
        
        -- Contruct the notification as a JSON string.
        notification = json_build_object(
                          'table',TG_TABLE_NAME,
                          'action', TG_OP,
                          'data', data);
        
                        
        -- Execute pg_notify(channel, notification)
        PERFORM pg_notify('events',notification::text);
        
        -- Result is ignored since this is an AFTER trigger
        RETURN NULL; 
    END;
    
$$ LANGUAGE plpgsql;


CREATE TRIGGER UI_Submission_notify_event
AFTER INSERT OR UPDATE OR DELETE ON DIM_UI_SBM
    FOR EACH ROW EXECUTE PROCEDURE notify_event();