/* PostgreSQL 
 * SOS GmbH, 2016-09-26, SP
 */

/* INVENTORY_FILES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IF_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IF_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_FILES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_FILES(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IF_ID_SEQ''),
	"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,
    CONSTRAINT INVENTORY_IF_UNIQUE UNIQUE ("INSTANCE_ID", "FILE_NAME"),
    PRIMARY KEY ("ID")
)
'; 
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


/* INVENTORY_INSTANCES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_II_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_II_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_INSTANCES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_INSTANCES(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_II_ID_SEQ''),
    "OS_ID"                 BIGINT          NOT NULL,
	"SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
    "HOSTNAME"              VARCHAR(100)    NOT NULL,
	"PORT"                  INT             NOT NULL,
	"LIVE_DIRECTORY"        VARCHAR(255)    NOT NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    "VERSION"               VARCHAR(30)     DEFAULT '''' NOT NULL,
    "COMMAND_URL"           VARCHAR(100)    DEFAULT '''' NOT NULL,
    "URL"                   VARCHAR(100)    DEFAULT '''' NOT NULL,
    "TIMEZONE"              VARCHAR(30)     DEFAULT '''' NOT NULL,
    "CLUSTER_TYPE"          VARCHAR(30)     DEFAULT '''' NOT NULL,
    "PRECEDENCE"            INT             NULL,
    "DBMS_NAME"             VARCHAR(30)     DEFAULT '''' NOT NULL,
    "DBMS_VERSION"          VARCHAR(100)    NULL,
    "STARTED_AT"            TIMESTAMP       NULL,
    "SUPERVISOR_ID"         BIGINT          NULL,
    "AUTH"                  VARCHAR(255)    NULL,
    CONSTRAINT INVENTORY_II_UNIQUE UNIQUE ("SCHEDULER_ID", "HOSTNAME", "PORT"),
    PRIMARY KEY ("ID")
)
'; 
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;



/* INVENTORY_JOB_CHAIN_NODES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IJCN_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJCN_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_JOB_CHAIN_NODES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOB_CHAIN_NODES(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IJCN_ID_SEQ''),
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"JOB_CHAIN_ID"          BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "ORDERING"              INT             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,
    "JOB_ID"                BIGINT          DEFAULT 0 NOT NULL,
    "NESTED_JOB_CHAIN"      VARCHAR(255)    NULL,
    "NESTED_JOB_CHAIN_NAME" VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "NESTED_JOB_CHAIN_ID"   BIGINT          DEFAULT 0 NOT NULL,
    "NODE_TYPE"             INT             DEFAULT 0 NOT NULL,
    "ON_ERROR"              VARCHAR(100)    NULL,
    "DELAY"                 INT             NULL,
    "DIRECTORY"             VARCHAR(255)    NULL,
    "REGEX"                 VARCHAR(255)    NULL,
    "FILE_SINK_OP"          NUMERIC(1)      NULL,
    "MOVE_PATH"             VARCHAR(255)    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;


/* INVENTORY_ORDERS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IO_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IO_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_ORDERS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_ORDERS(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IO_ID_SEQ''),
	"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,
    "JOB_CHAIN_ID"          BIGINT          DEFAULT 0 NOT NULL,
    "INITIAL_STATE"         VARCHAR(100)    NULL,
    "END_STATE"             VARCHAR(100)    NULL,
    "PRIORITY"              INT             NULL,
    "SCHEDULE"              VARCHAR(255)    NULL,
    "SCHEDULE_NAME"         VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "SCHEDULE_ID"           BIGINT          DEFAULT 0 NOT NULL,
    CONSTRAINT INVENTORY_IO_UNIQUE UNIQUE ("FILE_ID"),
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IO_INST_ID        ON INVENTORY_ORDERS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IO_JC_NAME        ON INVENTORY_ORDERS("JOB_CHAIN_NAME")
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

 
/* INVENTORY_JOB_CHAINS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IJC_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJC_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_JOB_CHAINS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOB_CHAINS(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IJC_ID_SEQ''),
	"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,
    "MAX_ORDERS"            INT             NULL,
    "DISTRIBUTED"           NUMERIC(1)      DEFAULT 0 NOT NULL,
    "PROCESS_CLASS"         VARCHAR(255)    NULL,
    "PROCESS_CLASS_NAME"    VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "PROCESS_CLASS_ID"      BIGINT          DEFAULT 0 NOT NULL,
    "FW_PROCESS_CLASS"      VARCHAR(255)    NULL,
    "FW_PROCESS_CLASS_NAME" VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "FW_PROCESS_CLASS_ID"   BIGINT          DEFAULT 0 NOT NULL,
    CONSTRAINT INVENTORY_IJC_UNIQUE UNIQUE ("FILE_ID"),
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IJC_INST_ID          ON INVENTORY_JOB_CHAINS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJC_NAME             ON INVENTORY_JOB_CHAINS("NAME")
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_JOBS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IJ_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IJ_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_JOBS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_JOBS(
	"ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IJ_ID_SEQ''),
	"INSTANCE_ID"           BIGINT          NOT NULL,
	"FILE_ID"               BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
	"TITLE"                 VARCHAR(255)    NULL,
    "IS_ORDER_JOB"          NUMERIC(1)      NOT NULL,
	"IS_RUNTIME_DEFINED"    NUMERIC(1)      NOT NULL,
	"CREATED"               TIMESTAMP       NOT NULL,
	"MODIFIED"              TIMESTAMP       NOT NULL,
    "USED_IN_JOB_CHAINS"    INT             NULL,
    "PROCESS_CLASS"         VARCHAR(255)    NULL,
    "PROCESS_CLASS_NAME"    VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "PROCESS_CLASS_ID"      BIGINT          DEFAULT 0 NOT NULL,
    "SCHEDULE"              VARCHAR(255)    NULL,
    "SCHEDULE_NAME"         VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "SCHEDULE_ID"           BIGINT          DEFAULT 0 NOT NULL,
    "MAX_TASKS"             INT             DEFAULT 0 NOT NULL,
    "HAS_DESCRIPTION"       NUMERIC(1)      NULL,
    CONSTRAINT INVENTORY_IJ_UNIQUE UNIQUE ("FILE_ID"),
    PRIMARY KEY ("ID")
)
'; EXECUTE '
CREATE INDEX REPORTING_IJ_INST_ID           ON INVENTORY_JOBS("INSTANCE_ID")
'; EXECUTE '
CREATE INDEX REPORTING_IJ_NAME              ON INVENTORY_JOBS("NAME")
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_OPERATING_SYSTEMS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IOS_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IOS_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_OPERATING_SYSTEMS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_OPERATING_SYSTEMS(
    "ID"             BIGINT           NOT NULL DEFAULT NEXTVAL(''REPORTING_IOS_ID_SEQ''),
    "HOSTNAME"       VARCHAR(100)     NOT NULL,
    "NAME"           VARCHAR(50)      NULL,
    "ARCHITECTURE"   VARCHAR(255)     NULL,
    "DISTRIBUTION"   VARCHAR(255)     NULL,
    "CREATED"        TIMESTAMP        NOT NULL,
    "MODIFIED"       TIMESTAMP        NOT NULL,
    CONSTRAINT INVENTORY_IOS_UNIQUE UNIQUE ("HOSTNAME"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_PROCESS_CLASSES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IPC_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IPC_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_PROCESS_CLASSES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_PROCESS_CLASSES(
    "ID"                    BIGINT        NOT NULL DEFAULT NEXTVAL(''REPORTING_IPC_ID_SEQ''),
    "INSTANCE_ID"           BIGINT        NOT NULL,
    "FILE_ID"               BIGINT        NOT NULL,
    "NAME"                  VARCHAR(255)  NOT NULL,
    "BASENAME"              VARCHAR(255)  NOT NULL,
    "MAX_PROCESSES"         INT           NULL,
    "HAS_AGENTS"            NUMERIC(1)    NOT NULL,
    "CREATED"               TIMESTAMP     NOT NULL,
    "MODIFIED"              TIMESTAMP     NOT NULL,    
    CONSTRAINT INVENTORY_IPC_UNIQUE UNIQUE ("INSTANCE_ID", "FILE_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_AGENT_CLUSTERS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IAC_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IAC_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_AGENT_CLUSTERS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_AGENT_CLUSTERS(
    "ID"                    BIGINT         NOT NULL DEFAULT NEXTVAL(''REPORTING_IAC_ID_SEQ''),
    "INSTANCE_ID"           BIGINT         NOT NULL,
    "PROCESS_CLASS_ID"      BIGINT         NOT NULL,
    "SCHEDULING_TYPE"       VARCHAR(15)    NOT NULL,
    "NUMBER_OF_AGENTS"      INT            NOT NULL,
    "CREATED"               TIMESTAMP      NOT NULL,
    "MODIFIED"              TIMESTAMP      NOT NULL,    
    CONSTRAINT INVENTORY_IAC_UNIQUE UNIQUE ("PROCESS_CLASS_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_AGENT_CLUSTERMEMBERS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IACM_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IACM_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_AGENT_CLUSTERMEMBERS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_AGENT_CLUSTERMEMBERS(
    "ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IACM_ID_SEQ''),
    "INSTANCE_ID"           BIGINT          NOT NULL,
    "AGENT_CLUSTER_ID"      BIGINT          NOT NULL,
    "AGENT_INSTANCE_ID"     BIGINT          NOT NULL,
    "URL"                   VARCHAR(255)    NOT NULL,
    "ORDERING"              INT             NOT NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
    "MODIFIED"              TIMESTAMP       NOT NULL,    
    CONSTRAINT INVENTORY_IACM_UNIQUE UNIQUE ("INSTANCE_ID", "AGENT_CLUSTER_ID", "AGENT_INSTANCE_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;


/* INVENTORY_AGENT_INSTANCES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IAI_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IAI_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_AGENT_INSTANCES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_AGENT_INSTANCES(
    "ID"                   BIGINT           NOT NULL DEFAULT NEXTVAL(''REPORTING_IAI_ID_SEQ''),
    "INSTANCE_ID"          BIGINT           NOT NULL,
    "OS_ID"                BIGINT           NOT NULL,
    "HOSTNAME"             VARCHAR(100)     NULL,    
    "VERSION"              VARCHAR(30)      NULL,
    "URL"                  VARCHAR(100)     NOT NULL, 
    "STATE"                INT              NOT NULL,
    "STARTED_AT"           TIMESTAMP        NULL,
    "CREATED"              TIMESTAMP        NOT NULL,
    "MODIFIED"             TIMESTAMP        NOT NULL,    
    CONSTRAINT INVENTORY_IAI_UNIQUE UNIQUE ("INSTANCE_ID", "URL"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_SCHEDULES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IS_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IS_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_SCHEDULES'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_SCHEDULES(
    "ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IS_ID_SEQ''),
    "INSTANCE_ID"           BIGINT          NOT NULL,
    "FILE_ID"               BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
    "TITLE"                 VARCHAR(255)    NULL,
    "SUBSTITUTE"            VARCHAR(255)    NULL,
    "SUBSTITUTE_NAME"       VARCHAR(255)    DEFAULT ''.'' NOT NULL,
    "SUBSTITUTE_ID"         BIGINT          DEFAULT 0 NOT NULL,
    "SUBSTITUTE_VALID_FROM" TIMESTAMP       NULL,
    "SUBSTITUTE_VALID_TO"   TIMESTAMP       NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
    "MODIFIED"              TIMESTAMP       NOT NULL,    
    CONSTRAINT INVENTORY_IS_UNIQUE UNIQUE ("FILE_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_LOCKS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IL_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IL_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_LOCKS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_LOCKS(
    "ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IL_ID_SEQ''),
    "INSTANCE_ID"           BIGINT          NOT NULL,
    "FILE_ID"               BIGINT          NOT NULL,
    "NAME"                  VARCHAR(255)    NOT NULL,
    "BASENAME"              VARCHAR(255)    NOT NULL,
    "MAX_NON_EXCLUSIVE"     INT             NULL,
    "CREATED"               TIMESTAMP       NOT NULL,
    "MODIFIED"              TIMESTAMP       NOT NULL,    
    CONSTRAINT INVENTORY_IL_UNIQUE UNIQUE ("FILE_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* INVENTORY_APPLIED_LOCKS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ DECLARE c integer; BEGIN SELECT COUNT(*) INTO c FROM pg_class, pg_namespace, pg_user WHERE UPPER(pg_class.relname) = 'REPORTING_IAL_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relowner = pg_user.usesysid and pg_user.usename = current_user; IF c = 0 THEN EXECUTE '
CREATE SEQUENCE REPORTING_IAL_ID_SEQ
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 CYCLE;
';
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)='INVENTORY_APPLIED_LOCKS'; IF c = 0 THEN EXECUTE '
CREATE TABLE INVENTORY_APPLIED_LOCKS(
    "ID"                    BIGINT          NOT NULL DEFAULT NEXTVAL(''REPORTING_IAL_ID_SEQ''),
    "JOB_ID"                BIGINT          NOT NULL,
    "LOCK_ID"               BIGINT          NOT NULL,    
    "CREATED"               TIMESTAMP       NOT NULL,
    "MODIFIED"              TIMESTAMP       NOT NULL,    
    CONSTRAINT INVENTORY_IAL_UNIQUE UNIQUE ("JOB_ID", "LOCK_ID"),
    PRIMARY KEY ("ID")
)
';
END IF; RETURN; END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

DROP FUNCTION tmp();
COMMIT;