ozeigermann 2003/11/26 02:07:09
Modified: proposals/stores/org/apache/slide/store/impl/rdbms
MySqlSchema.sql
Log:
Added improvements thanks to Alexander Wendt:
- tinyint for Booleans,
- dropped indexes (because primary and unique are indexes)
- Single or first unique became primary
Submitted by: Alexander Wendt
Revision Changes Path
1.2 +152 -147
jakarta-slide/proposals/stores/org/apache/slide/store/impl/rdbms/MySqlSchema.sql
Index: MySqlSchema.sql
===================================================================
RCS file:
/home/cvs/jakarta-slide/proposals/stores/org/apache/slide/store/impl/rdbms/MySqlSchema.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- MySqlSchema.sql 25 Nov 2003 09:19:17 -0000 1.1
+++ MySqlSchema.sql 26 Nov 2003 10:07:09 -0000 1.2
@@ -1,3 +1,5 @@
+# --------------------------------------------------------
+
drop table if exists OBJECT;
drop table if exists CHILDREN;
drop table if exists LINKS;
@@ -13,192 +15,195 @@
drop table if exists LABEL;
drop table if exists URI;
+# --------------------------------------------------------
CREATE TABLE URI (
- URI_ID INTEGER NOT NULL AUTO_INCREMENT,
- URI_STRING VARCHAR(255) NOT NULL,
- PRIMARY KEY(URI_ID)
-) TYPE=INNODB;
-
-
-CREATE INDEX URI_IX1 ON URI (URI_ID);
-CREATE INDEX URI_IX2 ON URI (URI_STRING);
+ URI_ID int(11) NOT NULL auto_increment,
+ URI_STRING varchar(255) NOT NULL,
+ PRIMARY KEY (URI_ID),
+ KEY URI_IX2 (URI_STRING)
+) TYPE=InnoDB;
+# --------------------------------------------------------
CREATE TABLE OBJECT (
- URI_ID INTEGER NOT NULL,
- CLASS_NAME VARCHAR(255) NOT NULL,
- PRIMARY KEY (URI_ID)
-
-) TYPE=INNODB;
+ URI_ID int(11) NOT NULL,
+ CLASS_NAME varchar(255) NOT NULL,
+ PRIMARY KEY (URI_ID)
+) TYPE=InnoDB;
-CREATE INDEX OBJECT_IX1 ON OBJECT (URI_ID);
-ALTER TABLE OBJECT ADD CONSTRAINT OBJECT_FK1 FOREIGN KEY OBJECT_FK1(URI_ID)
REFERENCES URI (URI_ID);
+ALTER TABLE OBJECT ADD CONSTRAINT OBJECT_FK1 FOREIGN KEY OBJECT_FK1(URI_ID)
+REFERENCES URI (URI_ID);
+# --------------------------------------------------------
CREATE TABLE CHILDREN (
- URI_ID INTEGER NOT NULL,
- CHILD_URI_ID INTEGER NOT NULL,
- PRIMARY KEY (URI_ID, CHILD_URI_ID)
-) TYPE=INNODB;
-
-
-CREATE INDEX CHILDREN_IX1 ON CHILDREN (URI_ID);
-ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_FK1 FOREIGN KEY CHILDREN_FK1(URI_ID)
REFERENCES URI (URI_ID);
-
-CREATE INDEX CHILD_IX2 ON CHILDREN (CHILD_URI_ID);
-ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_FK2 FOREIGN KEY
CHILDREN_FK2(CHILD_URI_ID) REFERENCES URI (URI_ID);
-
-
-CREATE TABLE LINKS (
- URI_ID INTEGER NOT NULL,
- LINK_TO_ID INTEGER NOT NULL,
- PRIMARY KEY (URI_ID, LINK_TO_ID)
-) TYPE=INNODB;
-
-CREATE INDEX LINK_IX1 ON LINKS (URI_ID);
-ALTER TABLE LINKS ADD CONSTRAINT LINK_FK1 FOREIGN KEY LINK_FK1(URI_ID) REFERENCES
URI (URI_ID);
-
-CREATE INDEX LINK_IX2 ON LINKS (LINK_TO_ID);
-ALTER TABLE LINKS ADD CONSTRAINT LINK_FK2 FOREIGN KEY LINK_FK2(LINK_TO_ID)
REFERENCES URI (URI_ID);
+ URI_ID int(11) NOT NULL,
+ CHILD_URI_ID int(11) NOT NULL,
+ PRIMARY KEY (URI_ID,CHILD_URI_ID),
+ key CHILD_IX2 (CHILD_URI_ID)
+) TYPE=InnoDB;
+
+ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_FK1 FOREIGN KEY
+CHILDREN_FK1(URI_ID) REFERENCES URI (URI_ID);
+ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_FK2 FOREIGN KEY
+CHILDREN_FK2(CHILD_URI_ID) REFERENCES URI (URI_ID);
+
+# --------------------------------------------------------
+
+CREATE TABLE LINKS (
+ URI_ID int(11) NOT NULL,
+ LINK_TO_ID int(11) NOT NULL,
+ PRIMARY KEY (URI_ID,LINK_TO_ID),
+ key LINK_IX2 (LINK_TO_ID)
+) TYPE=InnoDB;
+
+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 INTEGER NOT NULL,
- OBJECT_ID INTEGER NOT NULL,
- SUBJECT_ID INTEGER NOT NULL,
- TYPE_ID INTEGER NOT NULL,
- EXPIRATION_DATE DATE NOT NULL,
- IS_INHERITABLE INTEGER NOT NULL,
- IS_EXCLUSIVE INTEGER NOT NULL,
- UNIQUE (LOCK_ID)
-)TYPE=INNODB;
-
-CREATE INDEX LOCK_IX1 ON LOCKS (LOCK_ID);
-ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK1 FOREIGN KEY LOCK_FK1(LOCK_ID) REFERENCES
URI (URI_ID);
-
-CREATE INDEX LOCK_IX2 ON LOCKS (OBJECT_ID);
-ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK2 FOREIGN KEY LOCK_FK2(OBJECT_ID)
REFERENCES URI (URI_ID);
-
-CREATE INDEX LOCK_IX3 ON LOCKS (SUBJECT_ID);
-ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK3 FOREIGN KEY LOCK_FK3(SUBJECT_ID)
REFERENCES URI (URI_ID);
-
-CREATE INDEX LOCK_IX4 ON LOCKS (TYPE_ID);
-ALTER TABLE LOCKS ADD CONSTRAINT LOCK_FK4 FOREIGN KEY LOCK_FK4(TYPE_ID) REFERENCES
URI (URI_ID);
+ LOCK_ID int(11) NOT NULL,
+ OBJECT_ID int(11) NOT NULL,
+ SUBJECT_ID int(11) NOT NULL,
+ TYPE_ID int(11) NOT NULL,
+ EXPIRATION_DATE date NOT NULL,
+ IS_INHERITABLE tinyint(1) NOT NULL,
+ IS_EXCLUSIVE tinyint(1) NOT NULL,
+ PRIMARY KEY (LOCK_ID),
+ KEY LOCK_IX2 (OBJECT_ID),
+ KEY LOCK_IX3 (SUBJECT_ID),
+ KEY LOCK_IX4 (TYPE_ID)
+) TYPE=InnoDB;
+
+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 INTEGER NOT NULL AUTO_INCREMENT,
- BRANCH_STRING VARCHAR(255) NOT NULL,
- PRIMARY KEY (BRANCH_ID)
-)TYPE=INNODB;
+ BRANCH_ID int(11) NOT NULL auto_increment,
+ BRANCH_STRING varchar(255) NOT NULL,
+ PRIMARY KEY (BRANCH_ID)
+) TYPE=InnoDB;
+# --------------------------------------------------------
CREATE TABLE LABEL (
- LABEL_ID INTEGER NOT NULL AUTO_INCREMENT,
- LABEL_STRING VARCHAR(255) NOT NULL,
- PRIMARY KEY (LABEL_ID)
-)TYPE=INNODB;
+ LABEL_ID int(11) NOT NULL auto_increment,
+ LABEL_STRING varchar(255) NOT NULL,
+ PRIMARY KEY (LABEL_ID)
+) TYPE=InnoDB;
+# --------------------------------------------------------
CREATE TABLE VERSION (
- URI_ID INTEGER NOT NULL,
- IS_VERSIONED INTEGER NOT NULL,
- PRIMARY KEY(URI_ID)
+ URI_ID int(11) NOT NULL,
+ IS_VERSIONED tinyint(1) NOT NULL,
+ PRIMARY KEY (URI_ID)
+) TYPE=InnoDB;
-)TYPE=INNODB;
-
-
-CREATE INDEX VERSION_IX1 ON VERSION (URI_ID);
-ALTER TABLE VERSION ADD CONSTRAINT REVISON_FK1 FOREIGN KEY VERSION_FK1(URI_ID)
REFERENCES URI (URI_ID);
+ALTER TABLE VERSION ADD CONSTRAINT REVISON_FK1 FOREIGN KEY
+VERSION_FK1(URI_ID) REFERENCES URI (URI_ID);
+# --------------------------------------------------------
CREATE TABLE VERSION_HISTORY (
- VERSION_ID INTEGER NOT NULL AUTO_INCREMENT,
- URI_ID INTEGER NOT NULL,
- BRANCH_ID INTEGER NOT NULL,
- REVISION_NO VARCHAR(20) NOT NULL,
- PRIMARY KEY (VERSION_ID),
- UNIQUE(URI_ID, BRANCH_ID, REVISION_NO)
-
-)TYPE=INNODB;
-
-CREATE INDEX VERSION_HISTORY_IX1 ON VERSION_HISTORY (URI_ID);
-ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK1 FOREIGN KEY
VERSION_HISTORY_FK1(URI_ID) REFERENCES URI (URI_ID);
-
-CREATE INDEX VERSION_HISTORY_IX2 ON VERSION_HISTORY(BRANCH_ID);
-ALTER TABLE VERSION_HISTORY ADD CONSTRAINT VERSION_HISTORY_FK2 FOREIGN KEY
VERSION_HISTORY_FK2(BRANCH_ID) REFERENCES BRANCH (BRANCH_ID);
+ VERSION_ID int(11) NOT NULL auto_increment,
+ URI_ID int(11) NOT NULL,
+ BRANCH_ID int(11) NOT NULL,
+ REVISION_NO varchar(20) NOT NULL,
+ PRIMARY KEY (VERSION_ID),
+ UNIQUE KEY URI_ID (BRANCH_ID,URI_ID,REVISION_NO),
+ KEY URI_ID_IX3 (URI_ID)
+) TYPE=InnoDB;
+
+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 INTEGER NOT NULL,
- PREDECESSOR_ID INTEGER NOT NULL,
- UNIQUE (VERSION_ID, PREDECESSOR_ID)
-)TYPE=INNODB;
-
-CREATE INDEX VERSION_PREDS_IX1 ON VERSION_PREDS (VERSION_ID);
-ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK1 FOREIGN KEY
VERSION_PREDS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
-
-CREATE INDEX VERSION_PREDS_IX2 ON VERSION_PREDS (PREDECESSOR_ID);
-ALTER TABLE VERSION_PREDS ADD CONSTRAINT REVISON_PREDS_FK2 FOREIGN KEY
VERSION_PREDS_FK2(PREDECESSOR_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
+ VERSION_ID int(11) NOT NULL,
+ PREDECESSOR_ID int(11) NOT NULL,
+ PRIMARY KEY (VERSION_ID,PREDECESSOR_ID),
+ KEY PREDECESSOR_ID_IX2 (PREDECESSOR_ID)
+) TYPE=InnoDB;
+
+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 INTEGER NOT NULL,
- LABEL_ID INTEGER NOT NULL,
- UNIQUE (VERSION_ID, LABEL_ID)
-)TYPE=INNODB;
+ VERSION_ID int(11) NOT NULL,
+ LABEL_ID int(11) NOT NULL,
+ PRIMARY KEY (VERSION_ID,LABEL_ID),
+ KEY LABEL_ID_IX2 (LABEL_ID)
+) TYPE=InnoDB;
+
+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 INDEX VERSION_LABELS_IX1 ON VERSION_LABELS (VERSION_ID);
-ALTER TABLE VERSION_LABELS ADD CONSTRAINT REVISON_LABELS_FK1 FOREIGN KEY
VERSION_LABELS_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
-
-CREATE INDEX VERSION_LABELS_IX2 ON VERSION_LABELS (LABEL_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 INTEGER NOT NULL,
- CONTENT BLOB NOT NULL,
- UNIQUE(VERSION_ID)
-)TYPE=INNODB;
+ VERSION_ID int(11) NOT NULL,
+ CONTENT blob NOT NULL,
+ PRIMARY KEY (VERSION_ID)
+) TYPE=InnoDB;
-CREATE INDEX VERSION_CONTENT_IX1 ON VERSION_CONTENT (VERSION_ID);
-ALTER TABLE VERSION_CONTENT ADD CONSTRAINT REVISON_CONTENT_FK1 FOREIGN KEY
VERSION_CONTENT_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
+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 INTEGER NOT NULL,
- PROPERTY_NAMESPACE VARCHAR(50) NOT NULL,
- PROPERTY_NAME VARCHAR(50) NOT NULL,
- PROPERTY_VALUE VARCHAR(255) NOT NULL,
- PROPERTY_TYPE VARCHAR(50) NOT NULL,
- IS_PROTECTED INTEGER NOT NULL,
- UNIQUE (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
-)TYPE=INNODB;
+ VERSION_ID int(11) NOT NULL,
+ PROPERTY_NAMESPACE varchar(50) NOT NULL,
+ PROPERTY_NAME varchar(50) NOT NULL,
+ PROPERTY_VALUE varchar(255) NOT NULL,
+ PROPERTY_TYPE varchar(50) NOT NULL,
+ IS_PROTECTED tinyint(1) NOT NULL,
+ PRIMARY KEY (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME)
+) TYPE=InnoDB;
-CREATE INDEX PROPERTIES_IX1 ON PROPERTIES (VERSION_ID);
-ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
+ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
+PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
+# --------------------------------------------------------
CREATE TABLE PERMISSIONS (
- OBJECT_ID INTEGER NOT NULL,
- SUBJECT_ID INTEGER NOT NULL,
- ACTION_ID INTEGER NOT NULL,
- VERSION_NO VARCHAR(20),
- IS_INHERITABLE INTEGER NOT NULL,
- IS_NEGATIVE INTEGER NOT NULL,
- SUCCESSION INTEGER NOT NULL,
- UNIQUE (OBJECT_ID, SUBJECT_ID, ACTION_ID),
- UNIQUE (OBJECT_ID, SUCCESSION)
-)TYPE=INNODB;
-
-CREATE INDEX PERMISSIONS_IX1 ON PERMISSIONS (OBJECT_ID);
-ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK1 FOREIGN KEY
PERMISSIONS_FK1(OBJECT_ID) REFERENCES URI (URI_ID);
-
-CREATE INDEX PERMISSIONS_IX2 ON PERMISSIONS (SUBJECT_ID);
-ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK2 FOREIGN KEY
PERMISSIONS_FK2(SUBJECT_ID) REFERENCES URI (URI_ID);
-
-CREATE INDEX PERMISSIONS_IX3 ON PERMISSIONS (ACTION_ID);
-ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_FK3 FOREIGN KEY
PERMISSIONS_FK3(ACTION_ID) REFERENCES URI (URI_ID);
-
-
+ OBJECT_ID int(11) NOT NULL,
+ SUBJECT_ID int(11) NOT NULL,
+ ACTION_ID int(11) NOT NULL,
+ VERSION_NO varchar(20),
+ IS_INHERITABLE tinyint(1) NOT NULL,
+ IS_NEGATIVE tinyint(1) NOT NULL,
+ SUCCESSION int(11) 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;
+
+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);
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
