/* | Job Scheduler Reporting Interface custom tables for PostgreSQL | SOS GmbH, 2015-01-30, robert.ehrlich@sos-berlin.com | Custom Mapping */ /* Table for REPORT_CUSTOM_APPS */ 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)='REPORT_CUSTOM_APPS'; IF c = 0 THEN EXECUTE ' CREATE TABLE REPORT_CUSTOM_APPS( "ID" BIGINT NOT NULL, "NAME" VARCHAR(255) NOT NULL, "TITLE" VARCHAR(255), "CREATED" TIMESTAMP NOT NULL, "MODIFIED" TIMESTAMP NOT NULL, PRIMARY KEY ("ID") ) '; EXECUTE ' CREATE INDEX REPORTING_RCA_NAME ON REPORT_CUSTOM_APPS("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_RCA_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE ' CREATE SEQUENCE REPORTING_RCA_ID_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE; '; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT tmp(); COMMIT; /* Table for REPORT_CUSTOM_MANDATORS */ 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)='REPORT_CUSTOM_MANDATORS'; IF c = 0 THEN EXECUTE ' CREATE TABLE REPORT_CUSTOM_MANDATORS( "ID" BIGINT NOT NULL, "NAME" VARCHAR(255) NOT NULL, "TITLE" VARCHAR(255), "CREATED" TIMESTAMP NOT NULL, "MODIFIED" TIMESTAMP NOT NULL, PRIMARY KEY ("ID") ) '; EXECUTE ' CREATE INDEX REPORTING_RCM_NAME ON REPORT_CUSTOM_MANDATORS("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_RCM_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE ' CREATE SEQUENCE REPORTING_RCM_ID_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE; '; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT tmp(); COMMIT; /* Table for REPORT_CUSTOM_JOB_APPS */ 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)='REPORT_CUSTOM_JOB_APPS'; IF c = 0 THEN EXECUTE ' CREATE TABLE REPORT_CUSTOM_JOB_APPS( "ID" BIGINT NOT NULL, "JOB_NAME" VARCHAR(255) NOT NULL, "APP_NAME" VARCHAR(255) NOT NULL, "CREATED" TIMESTAMP NOT NULL, "MODIFIED" TIMESTAMP NOT NULL, PRIMARY KEY ("ID") ) '; EXECUTE ' CREATE INDEX REPORTING_RCJA_JN ON REPORT_CUSTOM_JOB_APPS("JOB_NAME") '; EXECUTE ' CREATE INDEX REPORTING_RCJA_AN ON REPORT_CUSTOM_JOB_APPS("APP_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_RCJA_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE ' CREATE SEQUENCE REPORTING_RCJA_ID_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE; '; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT tmp(); COMMIT; /* Table for REPORT_CUSTOM_JOB_MANDATORS */ 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)='REPORT_CUSTOM_JOB_MANDATORS'; IF c = 0 THEN EXECUTE ' CREATE TABLE REPORT_CUSTOM_JOB_MANDATORS( "ID" BIGINT NOT NULL, "JOB_NAME" VARCHAR(255) NOT NULL, "MANDATOR_NAME" VARCHAR(255) NOT NULL, "CREATED" TIMESTAMP NOT NULL, "MODIFIED" TIMESTAMP NOT NULL, PRIMARY KEY ("ID") ) '; EXECUTE ' CREATE INDEX REPORTING_RCJM_JN ON REPORT_CUSTOM_JOB_MANDATORS("JOB_NAME") '; EXECUTE ' CREATE INDEX REPORTING_RCJM_MN ON REPORT_CUSTOM_JOB_MANDATORS("MANDATOR_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_RCJM_ID_SEQ' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_user; IF c = 0 THEN EXECUTE ' CREATE SEQUENCE REPORTING_RCJM_ID_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE; '; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT tmp(); COMMIT; /* clean-up temporary objects */ DROP FUNCTION tmp();