|
I wasn’t able to use the new schema for MySql-4.1. It looks like it doesn’t like this
syntax: URI_STRING
varchar(255) NOT NULL CHARACTER
SET utf8 COLLATE utf8_general_ci, The file I attached is the schema I used to create the
tables. I only had to move the NOT
NULL part to the end of the line like this: URI_STRING
varchar(255) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL, Is there anyone else that has gotten this schema to work so
I know it isn’t just me? Also,
Several people have posted about the MySQL
Adapter not allowing uploads/downloads of files greater than 64k in size. I was hoping the upgrade to MySQL-4.1.1
would fix this but it did not. I
have not seen any response to this, so I am including my changes to the
VERSION_CONTENT table to fix this. The modified table uses LONGBLOB for the
content field. Here is the full
create statement from the file: CREATE TABLE VERSION_CONTENT ( VERSION_ID bigint NOT NULL, CONTENT longblob NOT NULL, PRIMARY KEY (VERSION_ID) ) TYPE=InnoDB CHARACTER SET utf8; Does anyone have any thoughts on this? Regards, Ryan Rhodes |
# -------------------------------------------------------- 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 varchar(255) 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]
