ozeigermann 2004/04/21 02:29:40
Added: src/conf/schema MySql-4.1-Schema.sql
Log:
Added (untested) 4.1 version supporting UTF-8 contributed by Jacob Lund.
Revision Changes Path
1.1 jakarta-slide/src/conf/schema/MySql-4.1-Schema.sql
Index: MySql-4.1-Schema.sql
===================================================================
# --------------------------------------------------------
drop table if exists OBJECT;
drop table if exists CHILDREN;
drop table if exists BINDING;
drop table if exists PARENT_BINDING;
drop table if exists LINKS;
drop table if exists LOCKS;
drop table if exists VERSION_CONTENT;
drop table if exists PROPERTIES;
drop table if exists PERMISSIONS;
drop table if exists VERSION_PREDS;
drop table if exists VERSION_LABELS;
drop table if exists VERSION_HISTORY;
drop table if exists VERSION;
drop table if exists BRANCH;
drop table if exists LABEL;
drop table if exists URI;
# --------------------------------------------------------
CREATE TABLE URI (
URI_ID bigint NOT NULL auto_increment,
URI_STRING varchar(255) NOT NULL CHARACTER SET uft8 COLLATE utf8_general_ci,
PRIMARY KEY (URI_ID),
KEY URI_IX2 (URI_STRING)
) TYPE=InnoDB CHARACTER SET utf8;
# --------------------------------------------------------
CREATE TABLE OBJECT (
URI_ID bigint NOT NULL,
CLASS_NAME varchar(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (URI_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE OBJECT ADD CONSTRAINT OBJECT_FK1 FOREIGN KEY OBJECT_FK1(URI_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE BINDING (
URI_ID bigint NOT NULL,
NAME varchar(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
CHILD_UURI_ID bigint NOT NULL,
PRIMARY KEY (URI_ID, NAME, CHILD_UURI_ID),
KEY BINDING_IX (CHILD_UURI_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE BINDING ADD CONSTRAINT BINDING_FK1 FOREIGN KEY
BINDING_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE BINDING ADD CONSTRAINT BINDING_FK2 FOREIGN KEY
BINDING_FK2(CHILD_UURI_ID) REFERENCES URI (URI_ID);
CREATE TABLE PARENT_BINDING (
URI_ID bigint NOT NULL,
NAME varchar(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
PARENT_UURI_ID bigint NOT NULL,
PRIMARY KEY (URI_ID, NAME, PARENT_UURI_ID),
KEY PARENT_BINDING_IX (PARENT_UURI_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE PARENT_BINDING ADD CONSTRAINT PARENT_BINDING_FK1 FOREIGN KEY
PARENT_BINDING_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE PARENT_BINDING ADD CONSTRAINT PARENT_BINDING_FK2 FOREIGN KEY
PARENT_BINDING_FK2(PARENT_UURI_ID) REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE LINKS (
URI_ID bigint NOT NULL,
LINK_TO_ID bigint NOT NULL,
PRIMARY KEY (URI_ID,LINK_TO_ID),
key LINK_IX2 (LINK_TO_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE LINKS ADD CONSTRAINT LINK_FK1 FOREIGN KEY LINK_FK1(URI_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LINKS ADD CONSTRAINT LINK_FK2 FOREIGN KEY LINK_FK2(LINK_TO_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE LOCKS (
LOCK_ID bigint NOT NULL,
OBJECT_ID bigint NOT NULL,
SUBJECT_ID bigint NOT NULL,
TYPE_ID bigint NOT NULL,
EXPIRATION_DATE bigint NOT NULL,
IS_INHERITABLE tinyint(1) NOT NULL,
IS_EXCLUSIVE tinyint(1) NOT NULL,
OWNER varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (LOCK_ID),
KEY LOCK_IX2 (OBJECT_ID),
KEY LOCK_IX3 (SUBJECT_ID),
KEY LOCK_IX4 (TYPE_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK1 FOREIGN KEY LOCK_FK1(LOCK_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK2 FOREIGN KEY LOCK_FK2(OBJECT_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK3 FOREIGN KEY LOCK_FK3(SUBJECT_ID)
REFERENCES URI (URI_ID);
ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK4 FOREIGN KEY LOCK_FK4(TYPE_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE BRANCH (
BRANCH_ID bigint NOT NULL auto_increment,
BRANCH_STRING varchar(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (BRANCH_ID)
) TYPE=InnoDB CHARACTER SET utf8;
# --------------------------------------------------------
CREATE TABLE LABEL (
LABEL_ID bigint NOT NULL auto_increment,
LABEL_STRING varchar(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (LABEL_ID)
) TYPE=InnoDB CHARACTER SET utf8;
# --------------------------------------------------------
CREATE TABLE VERSION (
URI_ID bigint NOT NULL,
IS_VERSIONED tinyint(1) NOT NULL,
PRIMARY KEY (URI_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE VERSION ADD CONSTRAINT REVISON_FK1 FOREIGN KEY
VERSION_FK1(URI_ID) REFERENCES URI (URI_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_HISTORY (
VERSION_ID bigint NOT NULL auto_increment,
URI_ID bigint NOT NULL,
BRANCH_ID bigint NOT NULL,
REVISION_NO varchar(20) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (VERSION_ID),
UNIQUE KEY URI_ID (BRANCH_ID,URI_ID,REVISION_NO),
KEY URI_ID_IX3 (URI_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK1 FOREIGN KEY
VERSION_HISTORY_FK1(URI_ID) REFERENCES URI (URI_ID);
ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK2 FOREIGN KEY
VERSION_HISTORY_FK2(BRANCH_ID) REFERENCES BRANCH (BRANCH_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_PREDS (
VERSION_ID bigint NOT NULL,
PREDECESSOR_ID bigint NOT NULL,
PRIMARY KEY (VERSION_ID,PREDECESSOR_ID),
KEY PREDECESSOR_ID_IX2 (PREDECESSOR_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK1 FOREIGN KEY
VERSION_PREDS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK2 FOREIGN KEY
VERSION_PREDS_FK2(PREDECESSOR_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_LABELS (
VERSION_ID bigint NOT NULL,
LABEL_ID bigint NOT NULL,
PRIMARY KEY (VERSION_ID,LABEL_ID),
KEY LABEL_ID_IX2 (LABEL_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE VERSION_LABELS ADD CONSTRAINT REVISON_LABELS_FK1 FOREIGN KEY
VERSION_LABELS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
ALTER TABLE VERSION_LABELS ADD CONSTRAINT REVISON_LABELS_FK2 FOREIGN KEY
VERSION_LABELS_FK2(LABEL_ID) REFERENCES LABEL (LABEL_ID);
# --------------------------------------------------------
CREATE TABLE VERSION_CONTENT (
VERSION_ID bigint NOT NULL,
CONTENT blob NOT NULL,
PRIMARY KEY (VERSION_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE VERSION_CONTENT ADD CONSTRAINT REVISON_CONTENT_FK1 FOREIGN KEY
VERSION_CONTENT_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE PROPERTIES (
VERSION_ID bigint NOT NULL,
PROPERTY_NAMESPACE varchar(50) NOT NULL CHARACTER SET utf8 COLLATE
utf8_general_ci,
PROPERTY_NAME varchar(50) NOT NULL CHARACTER SET utf8 COLLATE
utf8_general_ci,
PROPERTY_VALUE varchar(255) NOT NULL CHARACTER SET utf8 COLLATE
utf8_general_ci,
PROPERTY_TYPE varchar(50) NOT NULL CHARACTER SET utf8 COLLATE
utf8_general_ci,
IS_PROTECTED tinyint(1) NOT NULL,
PRIMARY KEY (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------
CREATE TABLE PERMISSIONS (
OBJECT_ID bigint NOT NULL,
SUBJECT_ID bigint NOT NULL,
ACTION_ID bigint NOT NULL,
VERSION_NO varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,
IS_INHERITABLE tinyint(1) NOT NULL,
IS_NEGATIVE tinyint(1) NOT NULL,
SUCCESSION int NOT NULL,
PRIMARY KEY (SUBJECT_ID,OBJECT_ID,ACTION_ID),
UNIQUE KEY OBJECT_ID_2 (OBJECT_ID,SUCCESSION),
KEY ACTION_ID_IX3 (ACTION_ID)
) TYPE=InnoDB CHARACTER SET utf8;
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK1 FOREIGN KEY
PERMISSIONS_FK1(OBJECT_ID) REFERENCES URI (URI_ID);
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK2 FOREIGN KEY
PERMISSIONS_FK2(SUBJECT_ID) REFERENCES URI (URI_ID);
ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK3 FOREIGN KEY
PERMISSIONS_FK3(ACTION_ID) REFERENCES URI (URI_ID);
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
