/*
 | Job Scheduler Reporting Interface inventory tables for PostgreSQL
 | SOS GmbH, 2015-01-30, robert.ehrlich@sos-berlin.com
*/

/* Table for INVENTORY_FILES */
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)='INVENTORY_FILES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_FILES(
	"ID"                    BIGINT          NOT NULL,
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"FILE_TYPE"             VARCHAR(50)     NOT NULL,
    "FILE_NAME"             VARCHAR(255)    NOT NULL,
    "FILE_BASENAME"         VARCHAR(255)    NOT NULL,
	"FILE_DIRECTORY"        VARCHAR(255)    NOT NULL,
    "FILE_CREATED"          TIMESTAMP,
	"FILE_MODIFIED"         TIMESTAMP,
 	"FILE_LOCAL_CREATED"    TIMESTAMP,
	"FILE_LOCAL_MODIFIED"   TIMESTAMP,
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IF_INST_ID           ON INVENTORY_FILES("INSTANCE_ID")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_IF_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IF_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


/* Table for INVENTORY_INSTANCES */
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)='INVENTORY_INSTANCES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_INSTANCES(
	"ID"                    BIGINT          NOT NULL,
	"SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
    "HOSTNAME"              VARCHAR(100)    NOT NULL,
	"PORT"                  BIGINT          NOT NULL,
	"LIVE_DIRECTORY"        VARCHAR(255)    NOT NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_II_SCHEDULER_ID      ON INVENTORY_INSTANCES("SCHEDULER_ID")
'; EXECUTE '
CREATE INDEX REPORTING_II_HOST              ON INVENTORY_INSTANCES("HOSTNAME","PORT")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_II_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_II_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


/* Table for INVENTORY_JOB_CHAIN_NODES */
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)='INVENTORY_JOB_CHAIN_NODES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOB_CHAIN_NODES(
	"ID"                    BIGINT          NOT NULL,
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"JOB_CHAIN_ID"          BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "ORDERING"              BIGINT          NOT NULL,    
    "STATE"                 VARCHAR(100),
    "NEXT_STATE"            VARCHAR(100),
    "ERROR_STATE"           VARCHAR(100),
    "JOB"                   VARCHAR(255),
    "JOB_NAME"              VARCHAR(255)    NOT NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IJCN_INST_ID         ON INVENTORY_JOB_CHAIN_NODES("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJCN_JC_ID           ON INVENTORY_JOB_CHAIN_NODES("JOB_CHAIN_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJCN_JOB_NAME        ON INVENTORY_JOB_CHAIN_NODES("JOB_NAME")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_IJCN_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJCN_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


/* Table for INVENTORY_ORDERS */
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)='INVENTORY_ORDERS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_ORDERS(
	"ID"                    BIGINT          NOT NULL,
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"FILE_ID"               BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
    "TITLE"                 VARCHAR(255),    
    "ORDER_ID"              VARCHAR(255)    NOT NULL,
    "JOB_CHAIN_NAME"        VARCHAR(255)    NOT NULL,
    "IS_RUNTIME_DEFINED"    NUMERIC(1)      NOT NULL,	
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IO_INST_ID        ON INVENTORY_ORDERS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IO_FILE_ID        ON INVENTORY_ORDERS("FILE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IO_JC_NAME        ON INVENTORY_ORDERS("JOB_CHAIN_NAME")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_IO_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IO_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;
 
/* Table for INVENTORY_JOB_CHAINS */
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)='INVENTORY_JOB_CHAINS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOB_CHAINS(
	"ID"                    BIGINT          NOT NULL,
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"FILE_ID"               BIGINT          NOT NULL,
    "START_CAUSE"           VARCHAR(50)     NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
	"TITLE"                 VARCHAR(255),
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IJC_INST_ID          ON INVENTORY_JOB_CHAINS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJC_FILE_ID          ON INVENTORY_JOB_CHAINS("FILE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJC_NAME             ON INVENTORY_JOB_CHAINS("NAME")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_IJC_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJC_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Table for INVENTORY_JOBS */
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)='INVENTORY_JOBS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOBS(
	"ID"                    BIGINT          NOT NULL,
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"FILE_ID"               BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
	"TITLE"                 VARCHAR(255),
    "IS_ORDER_JOB"          NUMERIC(1)      NOT NULL,
	"IS_RUNTIME_DEFINED"    NUMERIC(1)      NOT NULL,
	"CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IJ_INST_ID           ON INVENTORY_JOBS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJ_FILE_ID           ON INVENTORY_JOBS("FILE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJ_NAME              ON INVENTORY_JOBS("NAME")
';
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 pg_class,pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_IJ_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJ_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MAXVALUE 999999999 
MINVALUE 1 CYCLE;
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

DROP FUNCTION tmp();
COMMIT;