Page History

...

Code Block

language	sql
title	Oracle DDL for a GEODESC project (aka GEODPnnn)
collapse	true

/* Establish the schema account first.
   GEODESC project schemas own DB objects, therefore must have the privileges to manage them
*/
-- GEODESC project schemas (e.g.Examples: GEODP397, GEODP398, GEODPTST360, GEODP_TST123, GEODP999, etc.)
-- Replace "GEODPnnn" below with the desired GEODESC project code.define xschema=GEODP395

/* Sequences */
CREATE SEQUENCE "GEODPnnn"."&&xschema..P_CAPTURECOLUMNS_FILES_CFKEYCOLKEY_SEQ" MINVALUE 0 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
CREATE SEQUENCE "GEODPnnn"."&&xschema..P_COLUMNSTEMPLATES_COLKEYTPKEY_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"&&xschema..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"."&&xschema..P_FINALWORKINGSET_FILES_FFKEYWSKEY_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_FILESTables, constraints, triggers, indexes */
--------------------------------------------------------
--  DDL for Table P_COLUMNS
--------------------------------------------------------
CREATE TABLE &&xschema..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 &&xschema..P_COLUMNS.HEADER IS 'unique within project';
COMMENT ON COLUMN &&xschema..P_COLUMNS.JSONCAPLOCKS IS 'locks used in capture sheet as defined in TM';

--------------------------------------------------------
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_COLUMNS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX &&xschema..P_COLUMNS_PK ON &&xschema..P_COLUMNS ("COLKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDL for Trigger P_COLUMNS_COLKEY_TRG
------------------------------------------------------------------
--  DDL for Index P_CAPTURE_FILES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_CAPTURE_FILES_PK" ON "GEODPnnn"."P_CAPTURE_FILES" ("CFKEY") 
TABLESPACE "GEODESCIDX" ;
  

CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_COLUMNS_COLKEY_TRG 
before insert on &&xschema..P_COLUMNS
for each row 
  WHEN (new.colkey < 0 or new.colkey is null)
begin
  select &&xschema..P_COLUMNS_COLKEY_SEQ.nextval into :new.colkey from dual;
end;
/
ALTER TRIGGER &&xschema..P_COLUMNS_COLKEY_TRG ENABLE;

--------------------------------------------------------
--  DDLConstraints for TriggerTable P_CAPTURE_FILES_CFKEY_TRGCOLUMNS
--------------------------------------------------------
CREATEALTER 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"TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONENTRY" CHECK (JSONENTRY IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS MODIFY ("COLKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_PK" PRIMARY KEY ("COLKEY") USING INDEX &&xschema..P_COLUMNS_PK  ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONHEADER" CHECK (JSONHEADER IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONDISPLAY" CHECK (JSONDISPLAY IS JSON) ENABLE;
ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_CAPTURECOLUMNS_FILES_PKJSONKEYWORD" PRIMARY KEYCHECK ("CFKEY") USING INDEX "GEODPnnn"."P_CAPTURE_FILES_PK" JSONKEYWORD IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("FILENAME" NOT NULL ENABLE)&&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONCAPLOCKS" CHECK (JSONCAPLOCKS IS JSON) ENABLE;
ALTER TABLE "GEODPnnn".&&xschema..P_COLUMNS ADD CONSTRAINT "P_CAPTURECOLUMNS_FILESJSONTMLOCKS" MODIFYCHECK ("LIMSHOLE"JSONTMLOCKS NOTIS NULLJSON) ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES"&&xschema..P_COLUMNS MODIFY ("CFKEYTYPE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES"&&xschema..P_COLUMNS MODIFY ("EXPEDITIONHEADER" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES"&&xschema..P_COLUMNS MODIFY ("SITETPKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_CAPTURE_FILES" MODIFY ("HOLE" NOT NULL ENABLE);


----


--------------------------------------------------------
--  DDL for Table P_COLUMNSDATA
--------------------------------------------------------
CREATE TABLE "GEODPnnn"."&&xschema..P_COLUMNS"DATA 
( "COLKEYTPKEY" NUMBER(38,0), 
  "TPKEYLIMSHOLE" NUMBER(38,0), 
  "TYPECORE" VARCHAR2(20 BYTE), 
  "HEADERCHANGED_ON" VARCHAR2(200 BYTETIMESTAMP (6), 
  "JSONHEADERCHANGED_BY" VARCHAR2(4000100 BYTE), 
  "JSONDISPLAYJSONDATA" 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';

--CLOB
) TABLESPACE "GEODESC" 
  LOB ("JSONDATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;

--------------------------------------------------------
--  DDL for Index P_DATA_PK_IDX1
--------------------------------------------------------
--CREATE  DDL for Index INDEX &&xschema..P_COLUMNS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."P_COLUMNS_PK" ON "GEODPnnn"."P_COLUMNS" ("COLKEYDATA_PK_IDX1 ON &&xschema..P_DATA ("LIMSHOLE", "CORE") 
TABLESPACE "GEODESCIDXGEODESC" ;

--------------------------------------------------------
--  DDL for TriggerIndex P_COLUMNSDATA_COLKEY_TRGPK1
--------------------------------------------------------
CREATE ORUNIQUE 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" ENABLEINDEX &&xschema..P_DATA_PK1 ON &&xschema..P_DATA ("TPKEY", "LIMSHOLE", "CORE") 
TABLESPACE "GEODESC" ;

--------------------------------------------------------
--  Constraints for Table P_COLUMNSDATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."&&xschema..P_COLUMNS"DATA ADDMODIFY CONSTRAINT "P_COLUMNS_JSONENTRY("TPKEY" CHECKNOT (JSONENTRY IS JSON) ENABLENULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_COLUMNS"DATA MODIFY ("COLKEYLIMSHOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_COLUMNS"DATA ADDMODIFY CONSTRAINT "P_COLUMNS_PK("CORE" PRIMARYNOT KEY ("COLKEY") USING INDEX "GEODPnnn"."P_COLUMNS_PK"  ENABLENULL ENABLE);
ALTER TABLE &&xschema..P_DATA MODIFY ("JSONDATA" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_COLUMNS"DATA ADD CONSTRAINT "PJSONDATA_COLUMNSJSON_JSONHEADERCONSTRAINT" CHECK (JSONHEADERJSONDATA 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_LIST_VALUES
--------------------------------------------------------
CREATE TABLE &&xschema..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" ;

--------------------------------------------------------
--  DDLConstraints for Table P_LIST_DATAVALUES
--------------------------------------------------------
CREATEALTER 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 "GEODESC" )
;

--&&xschema..P_LIST_VALUES MODIFY ("SORTORDER" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_LIST_VALUES MODIFY ("COLKEY" NOT NULL ENABLE);


--------------------------------------------------------
--  DDL for IndexTable P_DATA_PK_IDX1TAXA
--------------------------------------------------------
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" ;

TABLE &&xschema..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" ;

--------------------------------------------------------
--  ConstraintsDDL for TableIndex P_TAXA_DATAPK
--------------------------------------------------------
ALTERCREATE 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;


--------UNIQUE INDEX &&xschema..P_TAXA_PK ON &&xschema..P_TAXA ("TAXKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDLConstraints for Table P_DISPLAY_COLUMNSTAXA
--------------------------------------------------------
CREATEALTER TABLE "GEODPnnn"."P_DISPLAY_COLUMNS" 
( &&xschema..P_TAXA MODIFY ("COLKEY" NUMBER(38,0), 
  "DPKEY" NUMBER(38,0), 
  "JSONDISPLAY" VARCHAR2(4000 BYTE), 
  "JSONHEADER" VARCHAR2(4000 BYTE), 
  "SORTORDER" NUMBER(4,0)
) TABLESPACE "GEODESC" ; NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("TAXKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("FOSSIL_GROUP" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA MODIFY ("MODIFIED" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TAXA ADD CONSTRAINT "P_TAXA_PK" PRIMARY KEY ("TAXKEY") USING INDEX &&xschema..P_TAXA_PK  ENABLE;


--------------------------------------------------------
--  ConstraintsDDL for Table P_DISPLAY_COLUMNSTEMPLATES
--------------------------------------------------------
ALTERCREATE 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&&xschema..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)
) 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, 
  "CHANGED_ON" TIMESTAMP (6) DEFAULT SYSTIMESTAMP, 
  "IGNORE_FLAG" VARCHAR2(5 BYTE), 
  "OWNERINVALID_SESSIONFLAG" VARCHAR2(1005 BYTE), 
  "UPLOADSAMPLE_GROUP" VARCHAR2(5 BYTE) DEFAULT 'FL', 
  "LAST_UPLOAD_ON" TIMESTAMP (6)HEADER_HEIGHT" NUMBER(38,0) DEFAULT 120, 
  "ROW_HEIGHT" NUMBER(38,0) DEFAULT 22, 
  "LAST_UPLOAD_BYPROJECT" VARCHAR2(10050 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)
) TABLESPACE "GEODESC" ;

COMMENT ON COLUMN &&xschema..P_TEMPLATES.EXTENSION IS 'used for names';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.ALLOWED_EXP IS 'JSON array of expeditions which can use the template';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.OFFSET_FLAG IS 'T=true (allow offset change) F=false';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.SECTION_FLAG IS 'T=true (change bottom for length changes); F=false';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.SAMPLE_TYPES IS 'JSON array of allowed sample types';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.PERMISSION IS 'T=locked to template manager; A=all';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.CHANGED_BY IS 'user who made the last change';
COMMENT ON COLUMN &&xschema..P_TEMPLATES.CHANGED_ON IS 'time of the last change';
COMMENT ON COLUMN "GEODPnnn"."&&xschema..P_WORKINGSET"."UPLOAD"TEMPLATES.IGNORE_FLAG IS 'T or F I=ignore; used by review';
COMMENT ON COLUMN "GEODPnnn"."&&xschema..P_WORKINGSET"."TPKEY"TEMPLATES.INVALID_FLAG IS 'key to the template tableI=invalid; used by review';
COMMENT ON COLUMN "GEODPnnn"."&&xschema..P_WORKINGSET"."TIMEOUT"TEMPLATES.SAMPLE_GROUP IS 'Time in minutes before upload warningL=large; S=small';

--------------------------------------------------------
--  DDL for Index P_WORKINGSETTEMPLATES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "GEODPnnn"."&&xschema..P_WORKINGSETTEMPLATES_PK" ON "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES ("WSKEYTPKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  DDL for Trigger P_WORKINGSETTEMPLATES_WSKEYTPKEY_TRG
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "GEODPnnn"."&&xschema..P_WORKINGSETTEMPLATES_WSKEYTPKEY_TRG" 
before insert on "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES
for each row
    WHEN (new.wskeytpkey < 0 or new.wskeytpkey is null)
begin
  select &&xschema..P_WORKINGSETTEMPLATES_WSKEYTPKEY_SEQ.nextval into :new.wskeytpkey from dual;
end;
/
ALTER TRIGGER "GEODPnnn"."&&xschema..P_WORKINGSETTEMPLATES_WSKEYTPKEY_TRG" ENABLE;

--------------------------------------------------------
--  Constraints for Table P_WORKINGSETTEMPLATES
-----------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_WORKINGSET"
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("TYPECODE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("OBJECTIVE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("SCALE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("OFFSET_FLAG" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("WSKEYSECTION_FLAG" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES ADD CONSTRAINT "P_WORKINGSETTEMPLATES_PK" PRIMARY KEY ("WSKEYTPKEY") USING INDEX "GEODPnnn"."&&xschema..P_WORKINGSETTEMPLATES_PK"  ENABLE;
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES MODIFY ("HOLE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("TPKEY" NOT NULL ENABLE)ADD CONSTRAINT "P_TEMPLATES_EXP_JSON" CHECK (ALLOWED_EXP IS JSON) ENABLE;
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES MODIFY ("EXPEDITIONPERMISSION" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET"TEMPLATES MODIFY ("SITESAMPLE_GROUP" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn".&&xschema..P_TEMPLATES ADD CONSTRAINT "P_WORKINGSETTEMPLATES_SAMPLE_TYPES" MODIFY ("FIRST_CORE" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."P_WORKINGSET" MODIFY ("LAST_CORE" NOT NULL ENABLE);


CHECK (SAMPLE_TYPES IS JSON) ENABLE;



--------------------------------------------------------
--  DDL for Table P_WORKINGSET
-------------------------------------------------------------------
--  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
CREATE TABLE &&xschema..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 &&xschema..P_WORKINGSET.WSKEY IS 'primary key generated when working set is created';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.UPLOAD IS 'T or F ';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.TPKEY IS 'key to the template table';
COMMENT ON COLUMN &&xschema..P_WORKINGSET.TIMEOUT IS 'Time in minutes before upload warning';

--------------------------------------------------------
--  ConstraintsDDL for TableIndex P_WORKINGSET_DATAPK
--------------------------------------------------------
ALTERCREATE UNIQUE TABLE "GEODPnnn"."INDEX &&xschema..P_WORKINGSET_DATA"PK 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. */ON &&xschema..P_WORKINGSET ("WSKEY") 
TABLESPACE "GEODESCIDX" ;

--------------------------------------------------------
--  Ref ConstraintsDDL for TableTrigger P_CAPTUREWORKINGSET_WSKEY_FILESTRG
--------------------------------------------------------
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_COLUMNSCREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_WORKINGSET_WSKEY_TRG 
before insert on &&xschema..P_WORKINGSET
for each row
    WHEN (new.wskey < 0 or new.wskey is null)
begin
  select &&xschema..P_WORKINGSET_WSKEY_SEQ.nextval into :new.wskey from dual;
end;
/
ALTER TRIGGER &&xschema..P_WORKINGSET_WSKEY_TRG ENABLE;

--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_COLUMNS" ADD CONSTRAINT "P_COLUMNS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;


--  Constraints for Table P_WORKINGSET
----------------------------------------------------------
--  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;
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET ADD CONSTRAINT "P_WORKINGSET_PK" PRIMARY KEY ("WSKEY") USING INDEX &&xschema..P_WORKINGSET_PK  ENABLE;
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("HOLE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("TPKEY" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("EXPEDITION" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("SITE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("FIRST_CORE" NOT NULL ENABLE);
ALTER TABLE &&xschema..P_WORKINGSET MODIFY ("LAST_CORE" NOT NULL ENABLE);


--------------------------------------------------------
--  Ref ConstraintsDDL for Table P_DISPLAYWORKINGSET_COLUMNSDATA
--------------------------------------------------------
ALTERCREATE 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&&xschema..P_WORKINGSET_DATA
( "WSKEY" NUMBER, 
  "ROWKEY" NUMBER, 
  "ROWDATA" CLOB
) TABLESPACE "GEODESC" 
  LOB ("ROWDATA") STORE AS SECUREFILE ( TABLESPACE "GEODESC" )
;

COMMENT ON COLUMN &&xschema..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.';

--------------------------------------------------------
--  Ref ConstraintsDDL for TableTrigger P_DISPLAYS
_WORKINGSET_DATA_ROWKEY_TRG
---------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_DISPLAYS" ADD CONSTRAINT "P_DISPLAYS_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE -
CREATE OR REPLACE EDITIONABLE TRIGGER &&xschema..P_WORKINGSET_DATA_ROWKEY_TRG
before insert on &&xschema..P_WORKINGSET_DATA
for each row
  WHEN (new.rowkey < 0 or new.rowkey is null)
begin
  select &&xschema..P_WORKINGSET_DATA_ROWKEY_SEQ.nextval into :new.rowkey from dual;
end;
/
ALTER TRIGGER &&xschema..P_WORKINGSET_DATA_ROWKEY_TRG" ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_FINALWORKINGSET_FILES
DATA
--------------------------------------------------------------
ALTER TABLE &&xschema..P_WORKINGSET_DATA MODIFY ("WSKEY" NOT NULL ENABLE);
ALTER TABLE "GEODPnnn"."&&xschema..P_FINALWORKINGSET_FILES"DATA ADDMODIFY CONSTRAINT "P_FINAL_FILES_TPKEY("ROWKEY" FOREIGNNOT KEY ("TPKEY") REFERENCES "GEODPnnn"."P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;
NULL ENABLE);
ALTER TABLE &&xschema..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_LIST_VALUESCOLUMNS
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_LIST_VALUES"&&xschema..P_COLUMNS ADD CONSTRAINT "P_LISTCOLUMNS_VALUES_COLKEYTPKEY" FOREIGN KEY ("COLKEYTPKEY") REFERENCES "GEODPnnn"."&&xschema..P_COLUMNS"TEMPLATES ("COLKEYTPKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_PRODUCTSDATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."&&xschema..P_PRODUCTS"DATA ADD CONSTRAINT "P_PRODUCTSDATA_TPKEY" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."&&xschema..P_TEMPLATES" ("TPKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_LIST_TAXAVALUES
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."P_TAXA"&&xschema..P_LIST_VALUES ADD CONSTRAINT "P_LIST_TAXAVALUES_FK1COLKEY" FOREIGN KEY ("COLKEY") REFERENCES "GEODPnnn"."&&xschema..P_COLUMNS" ("COLKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_TAXAFILESTAXA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."&&xschema..P_TAXAFILES"TAXA ADD CONSTRAINT "P_TAXAFILESTAXA_TPKEYFK1" FOREIGN KEY ("TPKEYCOLKEY") REFERENCES "GEODPnnn"."&&xschema..P_TEMPLATES"COLUMNS ("TPKEYCOLKEY") ON DELETE CASCADE ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_WORKINGSET
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET" ADD CONSTRAINT "P_WORKINGSET_FK1" FOREIGN KEY ("TPKEY") REFERENCES "GEODPnnn"."&&xschema..P_TEMPLATES" ("TPKEY") ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table P_WORKINGSET_DATA
--------------------------------------------------------
ALTER TABLE "GEODPnnn"."&&xschema..P_WORKINGSET_DATA" ADD CONSTRAINT "P_WORKINGSET_DATA_FK1" FOREIGN KEY ("WSKEY") REFERENCES "GEODPnnn"."&&xschema..P_WORKINGSET" ("WSKEY") ON DELETE CASCADE ENABLE;

...

Code Block

language	sql
title	Anonymous procedure to establish GEODESC project schema table grants
collapse	true

-- Anonymous procedure to establish above grants.
-- Project Code must be revised for each new GEODESC project created.
-- This procedure must be run by an account sufficiently privileged
-- to manage GRANTs.
set serveroutput on;
declare
  -- GEODESC project code (to be prefixed by "GEODP")
  -- Change this before you run it.
  project_code varchar2(30) := 'nnn';
  
  type WorkList is table of varchar2(50);
  tableList WorkList := WorkList(
    -- List of tables within GEODESC project schema
      'p_capture_files'
    , 'p_columns'
    , 'p_data'
    , 'p_display_columns'
    , 'p_displays'
    , 'p_final_files'
    , 'p_list_values'
    , 'p_old_products'
    , 'p_products'
    , 'p_taxa'
    , 'p_taxafiles'
    , 'p_templates'
    , 'p_workingset'
    , 'p_workingset_data'
    );
    
    fullyQualifiedName varchar2(100);

begin
  -- For the new GEODESC schema, generate the appropriate grants for each table.
  for t in tableList.FIRST .. tableList.LAST
  loop
    fullyQualifiedName := 'GEODP' || upper(project_code) || '.' || upper(tableList(t));
    dbms_output.put('Grants on '||fullyQualifiedName);
    execute immediate 'grant select, insert, update, delete on ' || fullyQualifiedName || ' to writer';
    execute immediate 'grant select on ' || fullyQualifiedName || ' to guest';
    execute immediate 'grant select on ' || fullyQualifiedName || ' to public';
    dbms_output.put_line('...applied to WRITER, GUEST, PUBLIC.');
  end loop;
end;
/

...

Page tree

Versions Compared

Old Version 6

New Version 7

Key