Dear Wiki user, You have subscribed to a wiki page or wiki category on "Jakarta-slide Wiki" for change notification.
The following page has been changed by PhilBurnside: http://wiki.apache.org/jakarta-slide/MySQL41Schema%2esql New page: This is a sample schema for use with MySQL4.1 Sourced from the mail archive, and slightly modified. {{{ create database slide; use slide; # -------------------------------------------------------- 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (BRANCH_ID) ) TYPE=InnoDB CHARACTER SET utf8; # -------------------------------------------------------- CREATE TABLE LABEL ( LABEL_ID bigint NOT NULL auto_increment, LABEL_STRING varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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 longblob 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PROPERTY_NAME varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PROPERTY_VALUE text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PROPERTY_TYPE varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 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]
