Homec4science
Diffusion SHRINE MedCo Fork (feature/sprint2) Browse

shrine-medco/apps/steward-app/src/main/sql/oracle.ddl
377684fbd0c0feature/sprint2

Last Change
Raw File

oracle.ddl
View Options

create table "users" ("userName" VARCHAR(254) NOT NULL PRIMARY KEY,"fullName" VARCHAR(254) NOT NULL,"isSteward" NUMBER NOT NULL);
create table "topics" ("id" INTEGER NOT NULL,"name" VARCHAR(254) NOT NULL,"description" CLOB NOT NULL,"createdBy" VARCHAR(254) NOT NULL,"createDate" NUMBER NOT NULL,"state" VARCHAR(254) NOT NULL,"changedBy" VARCHAR(254) NOT NULL,"changeDate" NUMBER NOT NULL);
create index "changeDateIndex" on "topics" ("changeDate");
create index "changedByIndex" on "topics" ("changedBy");
create index "createDateIndex" on "topics" ("createDate");
create index "createdByIndex" on "topics" ("createdBy");
create index "idIndex" on "topics" ("id");
create index "stateIndex" on "topics" ("state");
create index "topicNameIndex" on "topics" ("name");
-- handcrafted autoincrement from http://earlruby.org/2009/01/creating-auto-increment-columns-in-oracle/
create table "queries" (
"stewardId" NUMBER NOT NULL,
"id" NUMBER NOT NULL,
"name" VARCHAR(254) NOT NULL,
"researcher" VARCHAR(254) NOT NULL,
"topic" INTEGER,
"queryContents" CLOB NOT NULL,
"stewardResponse" VARCHAR(254) NOT NULL,
"date" NUMBER NOT NULL,
CONSTRAINT stewardIdPk PRIMARY KEY(stewardId) using index
);
create sequence stewardIdSeq start with 1 increment by 1;
create or replace trigger stewardIdInsert
before insert on "queries"
for each row
declare
max_id number;
cur_seq number;
begin
if :new."stewardId" is null then
-- No ID passed, get one from the sequence
select stewardIdSeq.nextval into :new."stewardId" from dual;
else
-- ID was set via insert, so update the sequence
select greatest(nvl(max("stewardId"),0), :new."stewardId") into max_id from "queries";
select stewardIdSeq.nextval into cur_seq from dual;
while cur_seq < max_id
loop
select stewardIdSeq.nextval into cur_seq from dual;
end loop;
end if;
end;
-- end autoincrement
create index "dateIndex" on "queries" ("date");
create index "externalIdIndex" on "queries" ("id");
create index "queryNameIndex" on "queries" ("name");
create index "researcherIdIndex" on "queries" ("stewardId");
create index "stewardResponseIndex" on "queries" ("stewardResponse");
create index "topicIdIndex" on "queries" ("topic");
create table "userTopic" ("researcher" VARCHAR(254) NOT NULL,"topicId" INTEGER NOT NULL,"state" VARCHAR(254) NOT NULL,"changedBy" VARCHAR(254) NOT NULL,"changeDate" NUMBER NOT NULL);
create unique index "researcherTopicIdIndex" on "userTopic" ("researcher","topicId");
c4science · Help