/* 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;