-- Working tables in shrine_query_history create table "AUDIT_ENTRY" ( "AUDIT_ENTRY_ID" NUMBER NOT NULL, "PROJECT" VARCHAR2(254) NOT NULL, "USERNAME" VARCHAR2(254) NOT NULL, "DOMAIN_NAME" VARCHAR2(254) NOT NULL, "TIME" TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "QUERY_TEXT" CLOB, "QUERY_TOPIC" VARCHAR2(245), CONSTRAINT "AUDIT_ENTRY_ID_PK" PRIMARY KEY("AUDIT_ENTRY_ID") USING INDEX ); / create index "IDX_AUDIT_ENTRY_DOM_USR_TOPIC" ON AUDIT_ENTRY ("DOMAIN_NAME", "USERNAME", "QUERY_TOPIC"); create sequence AUDIT_ENTRY_ID start with 1 increment by 1; create or replace trigger AUDIT_ENTRY_ID_INSERT before insert on "AUDIT_ENTRY" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.AUDIT_ENTRY_ID is null then -- NO ID PASSED, GET ONE FROM THE SEQUENCE select AUDIT_ENTRY_ID.nextval into :new.AUDIT_ENTRY_ID from DUAL; else -- ID WAS SET VIA INSERT, SO UPDATE THE SEQUENCE select GREATEST(NVL(max(AUDIT_ENTRY_ID),0), :new.AUDIT_ENTRY_ID) into MAX_ID from "AUDIT_ENTRY"; select AUDIT_ENTRY_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select AUDIT_ENTRY_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / CREATE TABLE "SHRINE_QUERY"( "ID" NUMBER NOT NULL, "LOCAL_ID" VARCHAR2(256) NOT NULL, "NETWORK_ID" NUMBER NOT NULL, "USERNAME" VARCHAR2(256) NOT NULL, "DOMAIN" VARCHAR2(256) NOT NULL, "QUERY_NAME" VARCHAR2(256) NOT NULL, "QUERY_EXPRESSION" CLOB, "DATE_CREATED" TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "HAS_BEEN_RUN" NUMBER(1) DEFAULT 0, "FLAGGED" NUMBER(1) DEFAULT 0, "FLAG_MESSAGE" CLOB NULL, "QUERY_XML" CLOB, CONSTRAINT "SHRINE_QUERY_ID_PK" PRIMARY KEY("ID") USING INDEX ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence SHRINE_QUERY_ID start with 1 increment by 1; create or replace trigger SHRINE_QUERY_ID_INSERT before insert on "SHRINE_QUERY" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select SHRINE_QUERY_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "SHRINE_QUERY"; select SHRINE_QUERY_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select SHRINE_QUERY_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "QUERY_RESULT" ( "ID" NUMBER NOT NULL, "LOCAL_ID" VARCHAR2(256) NOT NULL, "QUERY_ID" NUMBER NOT NULL, "TYPE" VARCHAR2(30) CHECK (TYPE IN ('PATIENTSET','PATIENT_COUNT_XML','PATIENT_AGE_COUNT_XML','PATIENT_RACE_COUNT_XML','PATIENT_VITALSTATUS_COUNT_XML','PATIENT_GENDER_COUNT_XML','ERROR')) NOT NULL, "STATUS" VARCHAR2(30) CHECK (STATUS IN ('FINISHED', 'ERROR', 'PROCESSING', 'QUEUED')) NOT NULL, "TIME_ELAPSED" NUMBER NULL, "LAST_UPDATED" TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "QUERY_RESULT_ID_PK" PRIMARY KEY("ID"), CONSTRAINT "FK_QUERY_RESULT_QUERY_ID" FOREIGN KEY ("QUERY_ID") REFERENCES SHRINE_QUERY ("ID") ON DELETE CASCADE ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence QUERY_RESULT_ID start with 1 increment by 1; create or replace trigger QUERY_RESULT_ID_INSERT before insert on "QUERY_RESULT" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select QUERY_RESULT_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "QUERY_RESULT"; select QUERY_RESULT_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select QUERY_RESULT_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "ERROR_RESULT" ( "ID" NUMBER NOT NULL, "RESULT_ID" NUMBER NOT NULL, "MESSAGE" VARCHAR2(256) NOT NULL, "CODEC" VARCHAR2(256) DEFAULT 'Pre-1.20 Error', "STAMP" VARCHAR2(256) DEFAULT 'Unknown time and machine', "SUMMARY" CLOB NOT NULL, "PROBLEM_DESCRIPTION" CLOB NOT NULL, "DETAILS" CLOB NOT NULL, CONSTRAINT "ERROR_RESULT_ID_PK" PRIMARY KEY("ID"), CONSTRAINT "FK_ERROR_RESULT_QR_ID" FOREIGN KEY ("RESULT_ID") REFERENCES QUERY_RESULT ("ID") ON DELETE CASCADE ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence ERROR_RESULT_ID start with 1 increment by 1; create or replace trigger ERROR_RESULT_ID_INSERT before insert on "ERROR_RESULT" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select ERROR_RESULT_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "ERROR_RESULT"; select ERROR_RESULT_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select ERROR_RESULT_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "COUNT_RESULT" ( "ID" NUMBER NOT NULL, "RESULT_ID" NUMBER NOT NULL, "ORIGINAL_COUNT" NUMBER NOT NULL, "OBFUSCATED_COUNT" NUMBER NOT NULL, "DATE_CREATED" TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "COUNT_RESULT_ID_PK" PRIMARY KEY("ID"), CONSTRAINT "FK_COUNT_RESULT_QR_ID" FOREIGN KEY ("RESULT_ID") REFERENCES QUERY_RESULT ("ID") ON DELETE CASCADE ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence COUNT_RESULT_ID start with 1 increment by 1; create or replace trigger COUNT_RESULT_ID_INSERT before insert on "COUNT_RESULT" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select COUNT_RESULT_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "COUNT_RESULT"; select COUNT_RESULT_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select COUNT_RESULT_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "BREAKDOWN_RESULT" ( "ID" NUMBER NOT NULL, "RESULT_ID" NUMBER NOT NULL, "DATA_KEY" VARCHAR2(256) NOT NULL, "ORIGINAL_VALUE" NUMBER NOT NULL, "OBFUSCATED_VALUE" NUMBER NOT NULL, CONSTRAINT "BREAKDOWN_RESULT_ID_PK" PRIMARY KEY("ID"), CONSTRAINT "FK_BREAKDOWN_RESULT_QR_ID" FOREIGN KEY ("RESULT_ID") REFERENCES QUERY_RESULT ("ID") ON DELETE CASCADE ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence BREAKDOWN_RESULT_ID start with 1 increment by 1; create or replace trigger BREAKDOWN_RESULT_ID_INSERT before insert on "BREAKDOWN_RESULT" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select BREAKDOWN_RESULT_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "BREAKDOWN_RESULT"; select BREAKDOWN_RESULT_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select BREAKDOWN_RESULT_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "PRIVILEGED_USER" ( "ID" NUMBER NOT NULL, "USERNAME" VARCHAR2(256) NOT NULL, "DOMAIN" VARCHAR2(256) NOT NULL, "THRESHOLD" NUMBER NULL, --Used to be not null. See SHRINE-1262 "OVERRIDE_DATE" TIMESTAMP NULL, CONSTRAINT "PRIVILIGED_USER_PK" PRIMARY KEY("ID"), CONSTRAINT "IX_PU_USERNAME_DOMAIN" UNIQUE ("USERNAME", "DOMAIN") ); / -- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/ create sequence PRIVILEGED_USER_ID start with 1 increment by 1; create or replace trigger PRIVILEGED_USER_ID_INSERT before insert on "PRIVILEGED_USER" for each row declare MAX_ID number; CUR_SEQ number; begin if :new.id is null then -- No ID passed, get one from the sequence select PRIVILEGED_USER_ID.nextval into :new.id from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max(id),0), :new.id) into MAX_ID from "PRIVILEGED_USER"; select PRIVILEGED_USER_ID.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select PRIVILEGED_USER_ID.nextval into CUR_SEQ from DUAL; end loop; end if; end; / -- end autoincrement CREATE TABLE "problems" ( "id" INTEGER NOT NULL PRIMARY KEY, "codec" VARCHAR2(254) NOT NULL, "stampText" VARCHAR2(500) NOT NULL, "summary" CLOB NOT NULL, "description" CLOB NOT NULL, "detailsXml" CLOB NOT NULL, "epoch" NUMBER(19) NOT NULL ); / create index "IDX_EPOCH" on "problems" ("epoch"); create sequence PROBLEMS_IDAUTOINC; create or replace trigger PROBLEMS_TRIGGERID_ID before insert on "problems" for each row declare MAX_ID number; CUR_SEQ number; begin if :new."id" is null then -- No ID passed, get one from the sequence select PROBLEMS_IDAUTOINC.nextval into :new."id" from DUAL; else -- ID was set via insert, so update the sequence select GREATEST(NVL(max("id"),0), :new."id") into MAX_ID from "problems"; select PROBLEMS_IDAUTOINC.nextval into CUR_SEQ from DUAL; while CUR_SEQ < MAX_ID loop select PROBLEMS_IDAUTOINC.nextval into CUR_SEQ from DUAL; end loop; end if; end; /