diff --git a/apps/steward-app/src/main/sql/oracle.ddl b/apps/steward-app/src/main/sql/oracle.ddl index 3d7883e05..03698eb99 100644 --- a/apps/steward-app/src/main/sql/oracle.ddl +++ b/apps/steward-app/src/main/sql/oracle.ddl @@ -1,54 +1,56 @@ 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"); + +create table "userAudit" ("researcher" VARCHAR(254) NOT NULL,"queryCount" INTEGER NOT NULL,"changeDate" NUMBER NOT NULL); \ No newline at end of file diff --git a/apps/steward-app/src/main/sql/sqlserver.ddl b/apps/steward-app/src/main/sql/sqlserver.ddl index b5023590c..ff88e6b62 100644 --- a/apps/steward-app/src/main/sql/sqlserver.ddl +++ b/apps/steward-app/src/main/sql/sqlserver.ddl @@ -1,20 +1,22 @@ create database stewardDB; use stewardDB; create table "users" ("userName" VARCHAR(254) NOT NULL PRIMARY KEY,"fullName" VARCHAR(254) NOT NULL,"isSteward" TINYINT NOT NULL); create table "topics" ("id" INTEGER NOT NULL,"name" VARCHAR(254) NOT NULL,"description" VARCHAR(MAX) NOT NULL,"createdBy" VARCHAR(254) NOT NULL,"createDate" BIGINT NOT NULL,"state" VARCHAR(254) NOT NULL,"changedBy" VARCHAR(254) NOT NULL,"changeDate" BIGINT 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"); create table "queries" ("stewardId" BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,"id" BIGINT NOT NULL,"name" VARCHAR(254) NOT NULL,"researcher" VARCHAR(254) NOT NULL,"topic" INTEGER,"queryContents" VARCHAR(MAX) NOT NULL,"stewardResponse" VARCHAR(254) NOT NULL,"date" BIGINT NOT NULL); 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" BIGINT NOT NULL); create unique index "researcherTopicIdIndex" on "userTopic" ("researcher","topicId"); + +create table "userAudit" ("researcher" VARCHAR(254) NOT NULL,"queryCount" INTEGER NOT NULL,"changeDate" BIGINT NOT NULL); \ No newline at end of file