...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
/* Establish the schema account first.
GEODESC project schemas own DB objects, therefore must have the privileges to manage them
*/
-- GEODESC project schemas (e.g. GEODP397, GEODP398, GEODPTST360, GEODP_TST123, GEODP999, etc.)
-- Replace "GEODPnnn" below with the desired GEODESC project code.
/* Sequences */
CREATE SEQUENCE "GEODPnnn"."P_CAPTURE_FILES_CFKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_COLUMNS_COLKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_DISPLAYS_DPKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_FINAL_FILES_FFKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_TAXAFILES_TFKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_TEMPLATES_TPKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_WORKINGSET_DATA_ROWKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."P_WORKINGSET_WSKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
/* Tables, constraints, triggers, indexes */
--------------------------------------------------------
-- DDL for Table P_CAPTURE_FILES
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_CAPTURE_FILES"
( "CFKEY" NUMBER(*,0),
"TPKEY" NUMBER(*,0),
"LIMSHOLE" NUMBER(*,0),
"EXPEDITION" VARCHAR2(20 BYTE),
"SITE" VARCHAR2(20 BYTE),
"HOLE" VARCHAR2(20 BYTE),
"FIRST_CORE" NUMBER(3,0),
"LAST_CORE" NUMBER(3,0),
"FILENAME" VARCHAR2(2000 BYTE),
"CREATED_BY" VARCHAR2(100 BYTE),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
) TABLESPACE "GEODESC" ;
COMMENT ON COLUMN "GEODPnnn"."P_CAPTURE_FILES"."TPKEY" IS 'key from template';
COMMENT ON COLUMN "GEODPnnn"."P_CAPTURE_FILES"."LIMSHOLE" IS 'key from LIMS';
--------------------------------------------------------
-- DDL for Index P_CAPTURE_FILES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_CAPTURE_FILES_PK" ON "GEODPnnn"."P_CAPTURE_FILES" ("CFKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_CAPTURE_FILES_CFKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_CAPTURE_FILES_CFKEY_TRG"
before insert on "GEODPnnn"."P_CAPTURE_FILES"
for each row
WHEN (new.cfkey < 0 or new.cfkey is null)
begin
select P_CAPTURE_FILES_CFKEY_SEQ.nextval into :new.cfkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_CAPTURE_FILES_CFKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_CAPTURE_FILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" ADD CONSTRAINT "P_CAPTURE_FILES_PK" PRIMARY KEY ("CFKEY") USING INDEX "GEODPnnn"."P_CAPTURE_FILES_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("FILENAME" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("LIMSHOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("CFKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("EXPEDITION" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("SITE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("HOLE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_COLUMNS
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_COLUMNS"
( "COLKEY" NUMBER(38,0),
"TPKEY" NUMBER(38,0),
"TYPE" VARCHAR2(20 BYTE),
"HEADER" VARCHAR2(200 BYTE),
"JSONHEADER" VARCHAR2(4000 BYTE),
"JSONDISPLAY" VARCHAR2(4000 BYTE),
"JSONTMLOCKS" VARCHAR2(1000 BYTE),
"JSONCAPLOCKS" VARCHAR2(4000 BYTE),
"SORTORDER" NUMBER(4,0),
"IGNORE_FLAG" VARCHAR2(5 BYTE),
"INVALID_FLAG" VARCHAR2(5 BYTE),
"JSONKEYWORD" VARCHAR2(4000 BYTE),
"COMMENTS" VARCHAR2(1000 BYTE),
"JSONENTRY" CLOB
) TABLESPACE "GEODESC"
LOB ("JSONENTRY") STORE AS SECUREFILE (TABLESPACE "GEODESC")
;
COMMENT ON COLUMN "GEODPnnn"."P_COLUMNS"."HEADER" IS 'unique within project';
COMMENT ON COLUMN "GEODPnnn"."P_COLUMNS"."JSONCAPLOCKS" IS 'locks used in capture sheet as defined in TM';
--------------------------------------------------------
-- DDL for Index P_COLUMNS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_COLUMNS_PK" ON "GEODPnnn"."P_COLUMNS" ("COLKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_COLUMNS_COLKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_COLUMNS_COLKEY_TRG"
before insert on "GEODPnnn"."P_COLUMNS"
for each row
WHEN (new.colkey < 0 or new.colkey is null)
begin
select P_COLUMNS_COLKEY_SEQ.nextval into :new.colkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_COLUMNS_COLKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_COLUMNS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONENTRY" CHECK (JSONENTRY IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" MODIFY ("COLKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_PK" PRIMARY KEY ("COLKEY") USING INDEX "GEODPnnn"."P_COLUMNS_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONHEADER" CHECK (JSONHEADER IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONDISPLAY" CHECK (JSONDISPLAY IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONKEYWORD" CHECK (JSONKEYWORD IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONCAPLOCKS" CHECK (JSONCAPLOCKS IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_JSONTMLOCKS" CHECK (JSONTMLOCKS IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_COLUMNS" MODIFY ("TYPE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_COLUMNS" MODIFY ("HEADER" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_COLUMNS" MODIFY ("TPKEY" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_DATA
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_DATA"
( "TPKEY" NUMBER(38,0),
"LIMSHOLE" NUMBER(38,0),
"CORE" VARCHAR2(20 BYTE),
"CHANGED_ON" TIMESTAMP (6),
"CHANGED_BY" VARCHAR2(100 BYTE),
"JSONDATA" CLOB
) TABLESPACE "GEODESC"
LOB ("JSONDATA") STORE AS SECUREFILE ( TABLESPACE "USERSGEODESC" )
;
--------------------------------------------------------
-- DDL for Index P_DATA_PK_IDX1
--------------------------------------------------------
CREATE INDEX "GEODPnnn"."P_DATA_PK_IDX1" ON "GEODPnnn"."P_DATA" ("LIMSHOLE", "CORE")
TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- DDL for Index P_DATA_PK1
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_DATA_PK1" ON "GEODPnnn"."P_DATA" ("TPKEY", "LIMSHOLE", "CORE")
TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- Constraints for Table P_DATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DATA" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_DATA" MODIFY ("LIMSHOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_DATA" MODIFY ("CORE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_DATA" MODIFY ("JSONDATA" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_DATA" ADD CONSTRAINT "JSONDATA_JSON_CONSTRAINT" CHECK (JSONDATA IS JSON) ENABLE;
--------------------------------------------------------
-- DDL for Table P_DISPLAY_COLUMNS
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_DISPLAY_COLUMNS"
( "COLKEY" NUMBER(38,0),
"DPKEY" NUMBER(38,0),
"JSONDISPLAY" VARCHAR2(4000 BYTE),
"JSONHEADER" VARCHAR2(4000 BYTE),
"SORTORDER" NUMBER(4,0)
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- Constraints for Table P_DISPLAY_COLUMNS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" MODIFY ("DPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" ADD CONSTRAINT "P_DISPLAY_COLUMNS_JSONDISPLAY" CHECK (JSONDISPLAY IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" ADD CONSTRAINT "P_DISPLAY_COLUMNS_JSONHEADER" CHECK (JSONHEADER IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" MODIFY ("COLKEY" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_DISPLAYS
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_DISPLAYS"
( "DPKEY" NUMBER(38,0),
"TPKEY" NUMBER(38,0),
"NAME" VARCHAR2(30 BYTE),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"CREATED_BY" VARCHAR2(100 BYTE)
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- DDL for Index P_DISPLAYS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_DISPLAYS_PK" ON "GEODPnnn"."P_DISPLAYS" ("DPKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_DISPLAYS_DPKEY_SEQ
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_DISPLAYS_DPKEY_SEQ"
before insert on "GEODPnnn"."P_DISPLAYS"
for each row
WHEN (new.dpkey < 0 or new.dpkey is null)
begin
select P_DISPLAYS_DPKEY_SEQ.nextval into :new.dpkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_DISPLAYS_DPKEY_SEQ" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_DISPLAYS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DISPLAYS" ADD CONSTRAINT "P_DISPLAYS_PK" PRIMARY KEY ("DPKEY") USING INDEX "GEODPnnn"."P_DISPLAYS_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_DISPLAYS" MODIFY ("DPKEY" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_FINAL_FILES
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_FINAL_FILES"
( "TPKEY" NUMBER,
"FILENAME" VARCHAR2(200 BYTE),
"EXPEDITON" VARCHAR2(20 BYTE),
"SITE" VARCHAR2(20 BYTE),
"HOLE" VARCHAR2(5 BYTE),
"TYPE" VARCHAR2(20 BYTE),
"SCALE" VARCHAR2(20 BYTE),
"ASMAN_KEY" NUMBER,
"METAFLAG" VARCHAR2(5 BYTE) DEFAULT 'D',
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"CREATED_BY" VARCHAR2(100 BYTE),
"FFKEY" NUMBER,
"DATA" CLOB
) TABLESPACE "GEODESC"
LOB ("DATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;
COMMENT ON COLUMN "GEODPnnn"."P_FINAL_FILES"."METAFLAG" IS 'D=DATA; M=METADATA';
--------------------------------------------------------
-- DDL for Index P_FINAL_FILES_FILENAME
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_FINAL_FILES_FILENAME" ON "GEODPnnn"."P_FINAL_FILES" ("FFKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Index P_FINAL_FILES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_FINAL_FILES_PK" ON "GEODPnnn"."P_FINAL_FILES" ("FILENAME")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_FINAL_FILES_FFKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_FINAL_FILES_FFKEY_TRG"
before insert on "GEODPnnn"."P_FINAL_FILES"
for each row
WHEN (new.ffkey < 0 or new.ffkey is null)
begin
select P_FINAL_FILES_FFKEY_SEQ.nextval into :new.ffkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_FINAL_FILES_FFKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_FINAL_FILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("METAFLAG" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" ADD CONSTRAINT "P_FINAL_FILES_UK1" UNIQUE ("FILENAME") USING INDEX "GEODPnnn"."P_FINAL_FILES_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("ASMAN_KEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("CREATED_ON" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("CREATED_BY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("FFKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("DATA" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" ADD CONSTRAINT "P_FINAL_FILES_FILENAME" PRIMARY KEY ("FFKEY") USING INDEX "GEODPnnn"."P_FINAL_FILES_FILENAME" ENABLE;
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("FILENAME" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("EXPEDITON" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("SITE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("HOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("TYPE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" MODIFY ("SCALE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_LIST_VALUES
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_LIST_VALUES"
( "COLKEY" NUMBER(38,0),
"FIRSTORDER" VARCHAR2(100 BYTE),
"SECONDORDER" VARCHAR2(100 BYTE),
"THIRDORDER" VARCHAR2(100 BYTE),
"AVALUE" VARCHAR2(100 BYTE),
"COMMENTS" VARCHAR2(4000 BYTE),
"RANK" NUMBER(3,0),
"SORTORDER" NUMBER(4,0),
"VLKEY" NUMBER(38,0)
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- Constraints for Table P_LIST_VALUES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_LIST_VALUES" MODIFY ("SORTORDER" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_LIST_VALUES" MODIFY ("COLKEY" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_OLD_PRODUCTS
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_OLD_PRODUCTS"
( "ASMAN_KEY" NUMBER(38,0),
"FILENAME" VARCHAR2(2000 BYTE),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- DDL for Index P_OLD_PRODUCTS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_OLD_PRODUCTS_PK" ON "GEODPnnn"."P_OLD_PRODUCTS" ("ASMAN_KEY")
TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- Constraints for Table P_OLD_PRODUCTS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_OLD_PRODUCTS" MODIFY ("ASMAN_KEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_OLD_PRODUCTS" ADD CONSTRAINT "P_OLD_PRODUCTS_PK" PRIMARY KEY ("ASMAN_KEY") USING INDEX "GEODPnnn"."P_OLD_PRODUCTS_PK" ENABLE;
--------------------------------------------------------
-- DDL for Table P_PRODUCTS
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_PRODUCTS"
( "FILENAME" VARCHAR2(2000 BYTE),
"TPKEY" NUMBER(38,0),
"EXPEDITION" VARCHAR2(20 BYTE),
"SITE" VARCHAR2(20 BYTE),
"HOLE" VARCHAR2(20 BYTE),
"TYPE" VARCHAR2(50 BYTE),
"SCALE" VARCHAR2(50 BYTE),
"ASMAN_KEY" NUMBER(38,0),
"META_FLAG" VARCHAR2(5 BYTE),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"CREATED_BY" VARCHAR2(100 BYTE)
) TABLESPACE "GEODESC" ;
COMMENT ON COLUMN "GEODPnnn"."P_PRODUCTS"."TYPE" IS 'copied from template to improve search';
COMMENT ON COLUMN "GEODPnnn"."P_PRODUCTS"."SCALE" IS 'copied from template to improve search';
--------------------------------------------------------
-- DDL for Index P_PRODUCTS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_PRODUCTS_PK" ON "GEODPnnn"."P_PRODUCTS" ("FILENAME")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- Constraints for Table P_PRODUCTS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_PRODUCTS" MODIFY ("FILENAME" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_PRODUCTS" ADD CONSTRAINT "P_PRODUCTS_PK" PRIMARY KEY ("FILENAME") USING INDEX "GEODPnnn"."P_PRODUCTS_PK" ENABLE;
--------------------------------------------------------
-- DDL for Table P_TAXA
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_TAXA"
( "COLKEY" NUMBER(38,0),
"TAXKEY" NUMBER(38,0),
"FOSSIL_GROUP" VARCHAR2(100 BYTE),
"GENUS" VARCHAR2(200 BYTE),
"SUBSPECIES_MOD" VARCHAR2(20 BYTE),
"SPECIES" VARCHAR2(200 BYTE),
"SPECIES_MOD" VARCHAR2(20 BYTE),
"FOSSIL_SIZE" VARCHAR2(20 BYTE),
"SUFFIX" VARCHAR2(20 BYTE),
"ADDED_TEXT" VARCHAR2(200 BYTE),
"REFERENCE" VARCHAR2(1000 BYTE),
"SENSU_REFERENCE" VARCHAR2(1000 BYTE),
"AGE" VARCHAR2(200 BYTE),
"MARKER_SPECIES" VARCHAR2(200 BYTE),
"GEO_REGION" VARCHAR2(200 BYTE),
"KEYWORDS" VARCHAR2(2000 BYTE),
"SUBSPECIES" VARCHAR2(200 BYTE),
"FAMILY_GROUP" VARCHAR2(100 BYTE),
"MODIFIED" VARCHAR2(20 BYTE)
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- DDL for Index P_TAXA_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_TAXA_PK" ON "GEODPnnn"."P_TAXA" ("TAXKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- Constraints for Table P_TAXA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TAXA" MODIFY ("COLKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXA" MODIFY ("TAXKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXA" MODIFY ("FOSSIL_GROUP" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXA" MODIFY ("MODIFIED" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXA" ADD CONSTRAINT "P_TAXA_PK" PRIMARY KEY ("TAXKEY") USING INDEX "GEODPnnn"."P_TAXA_PK" ENABLE;
--------------------------------------------------------
-- DDL for Table P_TAXAFILES
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_TAXAFILES"
( "TFKEY" NUMBER(38,0),
"TPKEY" NUMBER(38,0),
"CHANGED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"CHANGED_BY" VARCHAR2(100 BYTE),
"FILENAME" VARCHAR2(2000 BYTE),
"NUM_RECORDS" NUMBER(10,0),
"ASMAN_KEY" NUMBER(38,0),
"FILEHASH" VARCHAR2(4000 BYTE)
) TABLESPACE "GEODESC" ;
--------------------------------------------------------
-- DDL for Index P_TAXAFILES_TFKEY
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_TAXAFILES_TFKEY" ON "GEODPnnn"."P_TAXAFILES" ("TFKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_TAXAFILES_TFKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_TAXAFILES_TFKEY_TRG"
before insert on "GEODPnnn"."P_TAXAFILES"
for each row
WHEN (new.tfkey < 0 or new.tfkey is null)
begin
select P_TAXAFILES_TFKEY_SEQ.nextval into :new.tfkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_TAXAFILES_TFKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_TAXAFILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("TFKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXAFILES" ADD CONSTRAINT "P_TAXAFILES_TFKEY" PRIMARY KEY ("TFKEY") USING INDEX "GEODPnnn"."P_TAXAFILES_TFKEY" ENABLE;
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("FILEHASH" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("FILENAME" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("NUM_RECORDS" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TAXAFILES" MODIFY ("ASMAN_KEY" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_TEMPLATES
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_TEMPLATES"
( "TPKEY" NUMBER(38,0),
"TYPECODE" VARCHAR2(50 BYTE),
"OBJECTIVE" VARCHAR2(50 BYTE),
"SCALE" VARCHAR2(50 BYTE),
"EXTENSION" VARCHAR2(200 BYTE),
"ALLOWED_EXP" VARCHAR2(4000 BYTE),
"OFFSET_FLAG" VARCHAR2(5 BYTE) DEFAULT 'T',
"SECTION_FLAG" VARCHAR2(5 BYTE) DEFAULT 'T',
"SAMPLE_TYPES" VARCHAR2(4000 BYTE),
"PERMISSION" VARCHAR2(5 BYTE) DEFAULT 'T',
"CHANGED_BY" VARCHAR2(100 BYTE),
"CHANGED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"IGNORE_FLAG" VARCHAR2(5 BYTE),
"INVALID_FLAG" VARCHAR2(5 BYTE),
"SAMPLE_GROUP" VARCHAR2(5 BYTE) DEFAULT 'L',
"HEADER_HEIGHT" NUMBER(38,0) DEFAULT 120,
"ROW_HEIGHT" NUMBER(38,0) DEFAULT 22,
"PROJECT" VARCHAR2(50 BYTE)
) TABLESPACE "GEODESC" ;
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."EXTENSION" IS 'used for names';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."ALLOWED_EXP" IS 'JSON array of expeditions which can use the template';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."OFFSET_FLAG" IS 'T=true (allow offset change) F=false';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."SECTION_FLAG" IS 'T=true (change bottom for length changes); F=false';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."SAMPLE_TYPES" IS 'JSON array of allowed sample types';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."PERMISSION" IS 'T=locked to template manager; A=all';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."CHANGED_BY" IS 'user who made the last change';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."CHANGED_ON" IS 'time of the last change';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."IGNORE_FLAG" IS 'I=ignore; used by review';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."INVALID_FLAG" IS 'I=invalid; used by review';
COMMENT ON COLUMN "GEODPnnn"."P_TEMPLATES"."SAMPLE_GROUP" IS 'L=large; S=small';
--------------------------------------------------------
-- DDL for Index P_TEMPLATES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_TEMPLATES_PK" ON "GEODPnnn"."P_TEMPLATES" ("TPKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_TEMPLATES_TPKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_TEMPLATES_TPKEY_TRG"
before insert on "GEODPnnn"."P_TEMPLATES"
for each row
WHEN (new.tpkey < 0 or new.tpkey is null)
begin
select P_TEMPLATES_TPKEY_SEQ.nextval into :new.tpkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_TEMPLATES_TPKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_TEMPLATES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("TYPECODE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("OBJECTIVE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("SCALE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("OFFSET_FLAG" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("SECTION_FLAG" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" ADD CONSTRAINT "P_TEMPLATES_PK" PRIMARY KEY ("TPKEY") USING INDEX "GEODPnnn"."P_TEMPLATES_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_TEMPLATES" ADD CONSTRAINT "P_TEMPLATES_EXP_JSON" CHECK (ALLOWED_EXP IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("PERMISSION" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" MODIFY ("SAMPLE_GROUP" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_TEMPLATES" ADD CONSTRAINT "P_TEMPLATES_SAMPLE_TYPES" CHECK (SAMPLE_TYPES IS JSON) ENABLE;
--------------------------------------------------------
-- DDL for Table P_WORKINGSET
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_WORKINGSET"
( "WSKEY" NUMBER,
"OWNER_NAME" VARCHAR2(100 BYTE),
"OWNER_SESSION" VARCHAR2(100 BYTE),
"UPLOAD" VARCHAR2(5 BYTE) DEFAULT 'F',
"LAST_UPLOAD_ON" TIMESTAMP (6),
"LAST_UPLOAD_BY" VARCHAR2(100 BYTE),
"CREATED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
"CREATED_BY" VARCHAR2(100 BYTE),
"CHANGED_ON" TIMESTAMP (6),
"CHANGED_BY" VARCHAR2(100 BYTE),
"TPKEY" NUMBER,
"HOLE" VARCHAR2(20 BYTE),
"EXPEDITION" VARCHAR2(20 BYTE),
"SITE" VARCHAR2(20 BYTE),
"LIMSHOLE" NUMBER,
"FIRST_CORE" NUMBER(3,0) DEFAULT 0,
"LAST_CORE" NUMBER(3,0) DEFAULT 999,
"TIMEOUT" NUMBER DEFAULT 90
) TABLESPACE "GEODESC" ;
COMMENT ON COLUMN "GEODPnnn"."P_WORKINGSET"."WSKEY" IS 'primary key generated when working set is created';
COMMENT ON COLUMN "GEODPnnn"."P_WORKINGSET"."UPLOAD" IS 'T or F ';
COMMENT ON COLUMN "GEODPnnn"."P_WORKINGSET"."TPKEY" IS 'key to the template table';
COMMENT ON COLUMN "GEODPnnn"."P_WORKINGSET"."TIMEOUT" IS 'Time in minutes before upload warning';
--------------------------------------------------------
-- DDL for Index P_WORKINGSET_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_WORKINGSET_PK" ON "GEODPnnn"."P_WORKINGSET" ("WSKEY")
TABLESPACE "GEODESCIDX" ;
--------------------------------------------------------
-- DDL for Trigger P_WORKINGSET_WSKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_WORKINGSET_WSKEY_TRG"
before insert on "GEODPnnn"."P_WORKINGSET"
for each row
WHEN (new.wskey < 0 or new.wskey is null)
begin
select P_WORKINGSET_WSKEY_SEQ.nextval into :new.wskey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_WORKINGSET_WSKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_WORKINGSET
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" ADD CONSTRAINT "P_WORKINGSET_PK" PRIMARY KEY ("WSKEY") USING INDEX "GEODPnnn"."P_WORKINGSET_PK" ENABLE;
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("HOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("EXPEDITION" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("SITE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("FIRST_CORE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("LAST_CORE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table P_WORKINGSET_DATA
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."P_WORKINGSET_DATA"
( "WSKEY" NUMBER,
"ROWKEY" NUMBER,
"ROWDATA" CLOB
) TABLESPACE "GEODESC"
LOB ("ROWDATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;
COMMENT ON COLUMN "GEODPnnn"."P_WORKINGSET_DATA"."ROWKEY" IS 'keys from project data start at 20000. Keys from capture should always be less than that and are provided by the capture tool.';
--------------------------------------------------------
-- DDL for Trigger P_WORKINGSET_DATA_ROWKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."P_WORKINGSET_DATA_ROWKEY_TRG"
before insert on "GEODPnnn"."P_WORKINGSET_DATA"
for each row
WHEN (new.rowkey < 0 or new.rowkey is null)
begin
select P_WORKINGSET_DATA_ROWKEY_SEQ.nextval into :new.rowkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."P_WORKINGSET_DATA_ROWKEY_TRG" ENABLE;
--------------------------------------------------------
-- Constraints for Table P_WORKINGSET_DATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_WORKINGSET_DATA" MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET_DATA" MODIFY ("ROWKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET_DATA" MODIFY ("ROWDATA" NOT NULL ENABLE);
/* Gather reference constraints together at the end due to inter-table dependencies. */
--------------------------------------------------------
-- Ref Constraints for Table P_CAPTURE_FILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" ADD CONSTRAINT "P_CAPTURE_FILES_FK1" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_COLUMNS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_DATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DATA" ADD CONSTRAINT "P_DATA_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_DISPLAY_COLUMNS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" ADD CONSTRAINT "P_DISPLAY_COLUMNS_COLKEY" FOREIGN KEY ("COLKEY") REFERENCES "GEODPnnn"."P_COLUMNS" ("COLKEY") ON DELETE CASCADE ENABLE;
ALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" ADD CONSTRAINT "P_DISPLAY_COLUMNS_DPKEY" FOREIGN KEY ("DPKEY") REFERENCES "GEODPnnn"."P_DISPLAYS" ("DPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_DISPLAYS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DISPLAYS" ADD CONSTRAINT "P_DISPLAYS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_FINAL_FILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_FINAL_FILES" ADD CONSTRAINT "P_FINAL_FILES_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_LIST_VALUES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_LIST_VALUES" ADD CONSTRAINT "P_LIST_VALUES_COLKEY" FOREIGN KEY ("COLKEY") REFERENCES "GEODPnnn"."P_COLUMNS" ("COLKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_PRODUCTS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_PRODUCTS" ADD CONSTRAINT "P_PRODUCTS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_TAXA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TAXA" ADD CONSTRAINT "P_TAXA_FK1" FOREIGN KEY ("COLKEY") REFERENCES "GEODPnnn"."P_COLUMNS" ("COLKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_TAXAFILES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TAXAFILES" ADD CONSTRAINT "P_TAXAFILES_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_WORKINGSET
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_WORKINGSET" ADD CONSTRAINT "P_WORKINGSET_FK1" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ENABLE;
--------------------------------------------------------
-- Ref Constraints for Table P_WORKINGSET_DATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_WORKINGSET_DATA" ADD CONSTRAINT "P_WORKINGSET_DATA_FK1" FOREIGN KEY ("WSKEY") REFERENCES "GEODPnnn"."P_WORKINGSET" ("WSKEY") ON DELETE CASCADE ENABLE; |
...