...
- When the account is created, a password is established for the account.
Please record these credentials in the local development password safe. - The credential is not used by any applications--the use of GRANTS ensures the WRITER schema has all the proxy access it needs: select, insert, update, delete.
Make it as long, complicated, and obtuse as you like (not really--Oracle limits credentials to 30 bytes). We'll only cut and past it the few times we need it.
...
Code Block |
---|
create user geodp### identified by "stored-secret-30-bytes" profile long_password; alter user geodp### default tablespace geodesc temporary tablespace temp quota unlimited on geodesc quota unlimited on geodescidx ; grant create session to geodp###; grant create sequence to geodp###; grant create table to geodp###; grant create trigger to geodp###; |
...
- Granted Roles:
- Connect
- System Privileges:
- Advisor
- Alter session
- Create Credential
- Create Database Link
- Create Job
- Create Procedure
- Create Public Synonym
- Create Sequence
- Create Session
- Create Synonym
- Create Table
- Create Trigger
- Create Type
- Create View
- Create Any Procedure
- Debug Connect Session
- Drop Public Synonym
- Select Any Dictionary
- Select Any Table
- Quotas
- LABWARE
- LABWAREIDX
- GEODESC
- GEODESCIDXUSERS | 31457280 | K
2. Run DDL to create project tables, sequences, and triggers
...
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.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_FILESCOLKEY_CFKEY_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 */ -----------------Tables, 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'; -------------------------------------------------------- -- DDL for TableIndex P_CAPTURECOLUMNS_FILESPK -------------------------------------------------------- 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'; UNIQUE INDEX &&xschema..P_COLUMNS_PK ON &&xschema..P_COLUMNS ("COLKEY") TABLESPACE "GEODESCIDX" ; -------------------------------------------------------- -- DDL for Trigger P_COLUMNS_COLKEY_TRG -------------------------------------------------------- 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 IndexTable P_CAPTURE_FILES_PKCOLUMNS -------------------------------------------------------- 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;ALTER 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_COLUMNS_JSONKEYWORD" CHECK (JSONKEYWORD IS JSON) ENABLE; ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONCAPLOCKS" CHECK (JSONCAPLOCKS IS JSON) ENABLE; ALTER TABLE &&xschema..P_COLUMNS ADD CONSTRAINT "P_COLUMNS_JSONTMLOCKS" CHECK (JSONTMLOCKS IS JSON) ENABLE; ALTER TABLE &&xschema..P_COLUMNS MODIFY ("TYPE" NOT NULL ENABLE); ALTER TABLE &&xschema..P_COLUMNS MODIFY ("HEADER" NOT NULL ENABLE); ALTER TABLE &&xschema..P_COLUMNS MODIFY ("TPKEY" NOT NULL ENABLE); -------------------------------------------------------- -- ConstraintsDDL for Table P_CAPTURE_FILESDATA -------------------------------------------------------- ALTERCREATE 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); &&xschema..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" ) ; -------------------------------------------------------- -- DDL for TableIndex P_DATA_COLUMNSPK_IDX1 -------------------------------------------------------- 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';INDEX &&xschema..P_DATA_PK_IDX1 ON &&xschema..P_DATA ("LIMSHOLE", "CORE") TABLESPACE "GEODESC" ; -------------------------------------------------------- -- DDL for Index P_DATA_PK1 -------------------------------------------------------- CREATE UNIQUE INDEX &&xschema..P_DATA_PK1 ON &&xschema..P_DATA ("TPKEY", "LIMSHOLE", "CORE") TABLESPACE "GEODESC" ; -------------------------------------------------------- -- Constraints for Table P_DATA -------------------------------------------------------- ALTER TABLE &&xschema..P_DATA MODIFY ("TPKEY" NOT NULL ENABLE); ALTER TABLE &&xschema..P_DATA MODIFY ("LIMSHOLE" NOT NULL ENABLE); ALTER TABLE &&xschema..P_DATA MODIFY ("CORE" NOT NULL ENABLE); ALTER TABLE &&xschema..P_DATA MODIFY ("JSONDATA" NOT NULL ENABLE); ALTER TABLE &&xschema..P_DATA ADD CONSTRAINT "JSONDATA_JSON_CONSTRAINT" CHECK (JSONDATA IS JSON) ENABLE; -------------------------------------------------------- -- DDL for IndexTable P_COLUMNSLIST_PKVALUES -------------------------------------------------------- CREATE UNIQUE INDEX "GEODPnnn"."P_COLUMNS_PK" ON "GEODPnnn"."P_COLUMNS" ("COLKEY") TABLESPACE "GEODESCIDX" ; -----------------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 TriggerTable P_COLUMNSLIST_COLKEY_TRGVALUES -------------------------------------------------------- CREATEALTER 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;TABLE &&xschema..P_LIST_VALUES MODIFY ("SORTORDER" NOT NULL ENABLE); ALTER TABLE &&xschema..P_LIST_VALUES MODIFY ("COLKEY" NOT NULL ENABLE); -------------------------------------------------------- -- ConstraintsDDL for Table P_COLUMNSTAXA -------------------------------------------------------- ALTERCREATE TABLE "GEODPnnn"."&&xschema..P_COLUMNS"TAXA ADD ( CONSTRAINT "P_COLUMNS_JSONENTRY"COLKEY" 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); 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" ; -------------------------------------------------------- -- DDLConstraints for Table P_DATATAXA -------------------------------------------------------- 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_TAXA MODIFY ("COLKEY" 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); -------------------------------------------------------- -- DDL for IndexTable P_DATA_PK_IDX1TEMPLATES -------------------------------------------------------- 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" ; -TABLE &&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), "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 &&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 &&xschema..P_TEMPLATES.IGNORE_FLAG IS 'I=ignore; used by review'; COMMENT ON COLUMN &&xschema..P_TEMPLATES.INVALID_FLAG IS 'I=invalid; used by review'; COMMENT ON COLUMN &&xschema..P_TEMPLATES.SAMPLE_GROUP IS 'L=large; S=small'; -------------------------------------------------------- -- ConstraintsDDL for TableIndex P_DISPLAYTEMPLATES_COLUMNSPK -------------------------------------------------------- ALTERCREATE UNIQUE TABLE "GEODPnnn"."INDEX &&xschema..P_DISPLAY_COLUMNS" MODIFYTEMPLATES_PK ON &&xschema..P_TEMPLATES ("DPKEYTPKEY") 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); -- TABLESPACE "GEODESCIDX" ; -------------------------------------------------------- -- DDL for Trigger P_TEMPLATES_TPKEY_TRG ---------------------------------------------------------- --CREATE OR DDLREPLACE forEDITIONABLE TableTRIGGER &&xschema..P_DISPLAYS -----_TEMPLATES_TPKEY_TRG before insert on &&xschema..P_TEMPLATES for each row WHEN (new.tpkey < 0 or new.tpkey is null) begin select &&xschema..P_TEMPLATES_TPKEY_SEQ.nextval into :new.tpkey from dual; end; / ALTER TRIGGER &&xschema..P_TEMPLATES_TPKEY_TRG ENABLE; ---------------------------------------------------- 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" ; ---- -- Constraints for Table P_TEMPLATES -------------------------------------------------------- -- 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; 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 ("SECTION_FLAG" NOT NULL ENABLE); ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_PK" PRIMARY KEY ("TPKEY") USING INDEX &&xschema..P_TEMPLATES_PK ENABLE; ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_EXP_JSON" CHECK (ALLOWED_EXP IS JSON) ENABLE; ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("PERMISSION" NOT NULL ENABLE); ALTER TABLE &&xschema..P_TEMPLATES MODIFY ("SAMPLE_GROUP" NOT NULL ENABLE); ALTER TABLE &&xschema..P_TEMPLATES ADD CONSTRAINT "P_TEMPLATES_SAMPLE_TYPES" CHECK (SAMPLE_TYPES IS JSON) ENABLE; -------------------------------------------------------- -- ConstraintsDDL for Table P_DISPLAYSWORKINGSET -------------------------------------------------------- ALTERCREATE 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&&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), "EXPEDITONCHANGED_ON" VARCHAR2(20 BYTETIMESTAMP (6), "SITECHANGED_BY" VARCHAR2(20100 BYTE), "HOLETPKEY" VARCHAR2(5 BYTE)NUMBER, "TYPEHOLE" VARCHAR2(20 BYTE), "SCALEEXPEDITION" 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), "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"."ON &&xschema..P_WORKINGSET_DATA" MODIFY ("ROWKEYWSKEY") 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. */ TABLESPACE "GEODESCIDX" ; -------------------------------------------------------- -- Ref ConstraintsDDL for TableTrigger P_WORKINGSET_CAPTUREWSKEY_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"."&&xschema..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 ENABLEWORKINGSET_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_FILESDATA -------------------------------------------------------------- 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 | ||||||
---|---|---|---|---|---|---|
| ||||||
-- 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' of varchar2(50); tableList WorkList := WorkList( , 'p_final_files' , 'p_list_values' -- List of tables within GEODESC project schema , 'p_old_productscolumns' , 'p_productsdata' , 'p_taxalist_values' , 'p_taxafilestaxa' , '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'; insert into dbms_output.put_line('...transfer.log (entry) values ('BOX for GEODESC project creation. Grants on '||fullyQualifiedName||' applied to WRITER, GUEST, PUBLIC.'); commit; end loop; end; / |
4. Register the new project in table GEODCAT.PROJECTS
...
Code Block |
---|
-- Example insert into GEODCAT.PROJECTS (name, used_by_expedition, schema_name) values ('398', '["398","999"]', 'geodp398'; -- Example insert into GEODCAT.PROJECTS (name, used_by_expedition, schema_name) values ('TST999', '["999"]', 'GEODPTST999'; -- Example update GEODCAT.PROJECTS set name='399', used_by_expedition=replace(used_by_expedition,'397','399'), schema_name=replace(schema_name,'397','399') where name='xyz'; |
Please review GEODCAT.PROJECTS. Keep it to the minimum required number of entries.
...