/*
 | Job Scheduler Tables for Event Management with PostgreSQL 
 | SOS GmbH, 2017-12-11, SP
*/


/* Table for events */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; 
BEGIN SELECT COUNT(*) INTO c FROM INFORMATION_SCHEMA.SEQUENCES clustered
	WHERE SEQUENCE_CATALOG=CURRENT_DATABASE() AND SEQUENCE_SCHEMA=CURRENT_SCHEMA() AND SEQUENCE_NAME='reporting_custom_events_ID_seq'; 
IF c = 1 THEN EXECUTE 'ALTER SEQUENCE "reporting_custom_events_ID_seq" RENAME TO REPORTING_RCE_ID_SEQ;';
END IF;   
RETURN; 
END; 
$$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; 
BEGIN SELECT COUNT(*) INTO c FROM INFORMATION_SCHEMA.SEQUENCES clustered
    WHERE SEQUENCE_CATALOG=CURRENT_DATABASE() AND SEQUENCE_SCHEMA=CURRENT_SCHEMA() AND UPPER(SEQUENCE_NAME)='REPORTING_RCE_ID_SEQ'; 
IF c = 0 THEN EXECUTE 'CREATE SEQUENCE REPORTING_RCE_ID_SEQ;';
END IF; 
RETURN; 
END; 
$$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA() AND UPPER(TABLE_NAME)='REPORTING_CUSTOM_EVENTS'; IF c = 0 THEN EXECUTE '
CREATE TABLE REPORTING_CUSTOM_EVENTS (
  "ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_RCE_ID_SEQ''),    /* unique event entry identifier */
  "SCHEDULER_ID"          VARCHAR(100)    NOT NULL,                                              /* Job Scheduler instance that handles events */
  "REMOTE_SCHEDULER_HOST" VARCHAR(100)            ,                                              /* host of remote Job Scheduler that throws events */
  "REMOTE_SCHEDULER_PORT" INTEGER                 ,                                              /* port of remote Job Scheduler that throws events */
  "REMOTE_URL"            VARCHAR(255)            ,
  "JOB_CHAIN"             VARCHAR(250)            ,                                              /* job chain that throws event */
  "ORDER_ID"              VARCHAR(250)            ,                                              /* order identification */
  "JOB_NAME"              VARCHAR(100)            ,                                              /* job that throws event */
  "EVENT_CLASS"           VARCHAR(100)    NOT NULL,                                              /* event class name */
  "EVENT_ID"              VARCHAR(100)    NOT NULL DEFAULT '''',                                 /* event identification */
  "EXIT_CODE"             INTEGER         NOT NULL DEFAULT 0,                                    /* exit code */
  "PARAMETERS"            TEXT                    ,                                              /* event parameters in XML format <params><param name="x" value="y"/>...</params> */
  "CREATED"               TIMESTAMP       NOT NULL,                                              /* timestamp for creation date */
  "EXPIRES"               TIMESTAMP               ,                                              /* timestamp for expiration date */
  PRIMARY KEY ( "ID" )
)
'; EXECUTE '
CREATE INDEX REPORTING_CUSTOM_EVENTS_INDEX   ON REPORTING_CUSTOM_EVENTS ( "EVENT_CLASS", "EVENT_ID", "EXIT_CODE" )
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;