Differences between revisions 1 and 2
Revision 1 as of 2005-06-05 13:54:32
Size: 8967
Editor: PhilBurnside
Comment:
Revision 2 as of 2009-09-20 22:02:47
Size: 8967
Editor: localhost
Comment: converted to 1.6 markup
No differences found!

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); 

MySQL41Schema.sql (last edited 2009-09-20 22:02:47 by localhost)