
ALTER SESSION SET SQL_TRACE = TRUE;

DROP TRIGGER "URI-URI_ID-TRG";
DROP TRIGGER "BRANCH-BRANCH_ID-TRG";
DROP TRIGGER "LABEL-LABEL_ID-TRG";
DROP TRIGGER "VERSION_HISTORY-VERSION_ID-TRG";

DROP SEQUENCE "URI-URI_ID-SEQ";
DROP SEQUENCE "BRANCH-BRANCH_ID-SEQ";
DROP SEQUENCE "LABEL-LABEL_ID-SEQ";
DROP SEQUENCE "VERSION_HISTORY-VERSION_ID-SEQ";

DROP TABLE "URI" CASCADE CONSTRAINTS;
DROP TABLE "OBJECT" CASCADE CONSTRAINTS;
DROP TABLE "BINDING" CASCADE CONSTRAINTS;
DROP TABLE "PARENT_BINDING" CASCADE CONSTRAINTS;
DROP TABLE "LINKS" CASCADE CONSTRAINTS;
DROP TABLE "LOCKS" CASCADE CONSTRAINTS;
DROP TABLE "BRANCH" CASCADE CONSTRAINTS;
DROP TABLE "LABEL" CASCADE CONSTRAINTS;
DROP TABLE "VERSION" CASCADE CONSTRAINTS;
DROP TABLE "VERSION_HISTORY" CASCADE CONSTRAINTS;
DROP TABLE "VERSION_PREDS" CASCADE CONSTRAINTS;
DROP TABLE "VERSION_LABELS" CASCADE CONSTRAINTS;
DROP TABLE "VERSION_CONTENT" CASCADE CONSTRAINTS;
DROP TABLE "PROPERTIES" CASCADE CONSTRAINTS;
DROP TABLE "PERMISSIONS" CASCADE CONSTRAINTS;


// path max length: 2500

CREATE TABLE "URI" (
	"URI_ID" NUMBER(10) NOT NULL,
    	"URI_STRING" VARCHAR2(2500) NOT NULL,
	PRIMARY KEY("URI_ID"),
    	UNIQUE("URI_STRING")
) CACHE NOLOGGING;

CREATE TABLE "OBJECT" (
	"URI_ID" NUMBER(10),
    	"CLASS_NAME" VARCHAR2(255) NOT NULL,
	PRIMARY KEY("URI_ID"),
    	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

// node name max length: 512

CREATE TABLE "BINDING" (
	"URI_ID" NUMBER(10) NOT NULL,
	"NAME" VARCHAR2(512) NOT NULL,
	"CHILD_UURI_ID" NUMBER(10) NOT NULL,
	PRIMARY KEY("URI_ID", "NAME", "CHILD_UURI_ID"),
	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("CHILD_UURI_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

CREATE TABLE "PARENT_BINDING" (
	"URI_ID" NUMBER(10) NOT NULL,
	"NAME" VARCHAR2(512) NOT NULL,
    	"PARENT_UURI_ID" NUMBER(10) NOT NULL,
	PRIMARY KEY("URI_ID", "NAME", "PARENT_UURI_ID"),
	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("PARENT_UURI_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

CREATE TABLE "LINKS" (
	"URI_ID" NUMBER(10) NOT NULL,
	"LINK_TO_ID" NUMBER(10) NOT NULL,
	PRIMARY KEY("URI_ID", "LINK_TO_ID"),
	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("LINK_TO_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

CREATE TABLE "LOCKS" (
	"LOCK_ID" NUMBER(10) NOT NULL, 
    	"OBJECT_ID" NUMBER(10) NOT NULL, 
	"SUBJECT_ID" NUMBER(10) NOT NULL, 
	"TYPE_ID" NUMBER(10) NOT NULL, 
	"EXPIRATION_DATE" NUMBER(14) NOT NULL,
	"IS_INHERITABLE" NUMBER(1) NOT NULL, 
    	"IS_EXCLUSIVE" NUMBER(1) NOT NULL, 
	"OWNER" VARCHAR2(512), 
	PRIMARY KEY("LOCK_ID"), 
	FOREIGN KEY("LOCK_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("OBJECT_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("SUBJECT_ID") REFERENCES "URI"("URI_ID"),
	FOREIGN KEY("TYPE_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

CREATE TABLE "BRANCH" (
	"BRANCH_ID" NUMBER(10) NOT NULL, 
    	"BRANCH_STRING" VARCHAR2(512) NOT NULL, 
	PRIMARY KEY("BRANCH_ID"), 
	UNIQUE("BRANCH_STRING")
) CACHE NOLOGGING;

CREATE TABLE "LABEL" (
	"LABEL_ID" NUMBER(10) NOT NULL, 
    	"LABEL_STRING" VARCHAR2(512) NOT NULL, 
	PRIMARY KEY("LABEL_ID")
) CACHE NOLOGGING;

CREATE TABLE "VERSION" (
	"URI_ID" NUMBER(10) NOT NULL, 
    	"IS_VERSIONED" NUMBER(1) NOT NULL, 
	PRIMARY KEY("URI_ID"), 
    	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID")
) CACHE NOLOGGING;

CREATE TABLE "VERSION_HISTORY" (
	"VERSION_ID" NUMBER(10) NOT NULL, 
	"URI_ID" NUMBER(10) NOT NULL, 
	"BRANCH_ID" NUMBER(10) NOT NULL, 
	"REVISION_NO" VARCHAR2(20) NOT NULL, 
    	PRIMARY KEY("VERSION_ID"), 
	UNIQUE("URI_ID", "BRANCH_ID", "REVISION_NO"), 
	FOREIGN KEY("URI_ID") REFERENCES "URI"("URI_ID"), 
	FOREIGN KEY("BRANCH_ID") REFERENCES "BRANCH"("BRANCH_ID")
) CACHE NOLOGGING;

CREATE TABLE "VERSION_PREDS" (
	"VERSION_ID" NUMBER(10) NOT NULL, 
	"PREDECESSOR_ID" NUMBER(10) NOT NULL, 
	FOREIGN KEY("VERSION_ID") REFERENCES "VERSION_HISTORY"("VERSION_ID"), 
	FOREIGN KEY("PREDECESSOR_ID") REFERENCES "VERSION_HISTORY"("VERSION_ID"), 
    	UNIQUE("VERSION_ID", "PREDECESSOR_ID")
) CACHE NOLOGGING;

CREATE TABLE "VERSION_LABELS" (
	"VERSION_ID" NUMBER(10) NOT NULL, 
	"LABEL_ID" NUMBER(10) NOT NULL, 
	UNIQUE("VERSION_ID", "LABEL_ID"), 
	FOREIGN KEY("VERSION_ID") REFERENCES "VERSION_HISTORY"("VERSION_ID"), 
	FOREIGN KEY("LABEL_ID") REFERENCES "LABEL"("LABEL_ID")
) CACHE NOLOGGING;

CREATE TABLE "VERSION_CONTENT" (
	"VERSION_ID" NUMBER(10) NOT NULL, 
	"CONTENT" BLOB, 
	PRIMARY KEY("VERSION_ID"), 
	FOREIGN KEY("VERSION_ID") REFERENCES "VERSION_HISTORY"("VERSION_ID")
) CACHE NOLOGGING
LOB ("CONTENT") STORE AS (NOCACHE NOLOGGING STORAGE(MAXEXTENTS UNLIMITED));

CREATE TABLE "PROPERTIES" (
	"VERSION_ID" NUMBER(10) NOT NULL, 
	"PROPERTY_NAMESPACE" VARCHAR2(50) NOT NULL, 
	"PROPERTY_NAME" VARCHAR2(50) NOT NULL, 
	"PROPERTY_VALUE" VARCHAR2(255), 
	"PROPERTY_TYPE" VARCHAR2(50),
	"IS_PROTECTED" NUMBER(1) NOT NULL, 
	UNIQUE("VERSION_ID", "PROPERTY_NAMESPACE", "PROPERTY_NAME"), 
	FOREIGN KEY("VERSION_ID") REFERENCES "VERSION_HISTORY"("VERSION_ID")
) CACHE NOLOGGING;
	
CREATE TABLE "PERMISSIONS" (
	"OBJECT_ID" NUMBER(10) NOT NULL, 
	"SUBJECT_ID" NUMBER(10) NOT NULL, 
	"ACTION_ID" NUMBER(10) NOT NULL, 
	"VERSION_NO" VARCHAR2(20), 
	"IS_INHERITABLE" NUMBER(1) NOT NULL, 
	"IS_NEGATIVE" NUMBER(1) NOT NULL, 
	"SUCCESSION" NUMBER(10) NOT NULL, 
	FOREIGN KEY("OBJECT_ID") REFERENCES "URI"("URI_ID"), 
	FOREIGN KEY("SUBJECT_ID") REFERENCES "URI"("URI_ID"), 
	FOREIGN KEY("ACTION_ID") REFERENCES "URI"("URI_ID"), 
	UNIQUE("OBJECT_ID", "SUBJECT_ID", "ACTION_ID"), 
	UNIQUE("OBJECT_ID", "SUCCESSION")
) CACHE NOLOGGING;

CREATE SEQUENCE "URI-URI_ID-SEQ" START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE "BRANCH-BRANCH_ID-SEQ" START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE "LABEL-LABEL_ID-SEQ" START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE "VERSION_HISTORY-VERSION_ID-SEQ" START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE TRIGGER "URI-URI_ID-TRG" BEFORE INSERT ON "URI" FOR EACH ROW 
BEGIN 
SELECT "URI-URI_ID-SEQ".nextval INTO :new.URI_ID from dual; 
END;
/

CREATE TRIGGER "BRANCH-BRANCH_ID-TRG" BEFORE INSERT ON "BRANCH" FOR EACH ROW 
BEGIN 
SELECT "BRANCH-BRANCH_ID-SEQ".nextval INTO :new.BRANCH_ID from dual; 
END;
/

CREATE TRIGGER "LABEL-LABEL_ID-TRG" BEFORE INSERT ON "LABEL" FOR EACH ROW 
BEGIN 
SELECT "LABEL-LABEL_ID-SEQ".nextval INTO :new.LABEL_ID from dual; 
END;
/

CREATE TRIGGER "VERSION_HISTORY-VERSION_ID-TRG" BEFORE INSERT ON "VERSION_HISTORY" FOR EACH ROW 
BEGIN 
SELECT "VERSION_HISTORY-VERSION_ID-SEQ".nextval INTO :new.VERSION_ID from dual; 
END;
/
