Re: MySqlRDBMSlAdapter

[Christophe Lombart]

Sorry for the confusion,  index is not required for PK within MySql
So, this script is cleaner thanks to Alexander.
Christophe

Wendt, Alexander wrote:

OK .. no Attachement - then text (Sorry for that):

# --------------------------------------------------------

drop table if exists OBJECT;
drop table if exists CHILDREN;
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       int(11)       NOT NULL auto_increment,
 URI_STRING   varchar(255)  NOT NULL default '',
 PRIMARY KEY  (URI_ID),
 KEY URI_IX2  (URI_STRING)
) TYPE=InnoDB;
# --------------------------------------------------------

CREATE TABLE OBJECT (
 URI_ID      int(11)       NOT NULL default '0',
 CLASS_NAME  varchar(255)  NOT NULL default '',
 PRIMARY KEY (URI_ID)
) TYPE=InnoDB;
ALTER TABLE OBJECT ADD CONSTRAINT OBJECT_FK1 FOREIGN KEY OBJECT_FK1(URI_ID)
REFERENCES URI (URI_ID);
# --------------------------------------------------------

CREATE TABLE CHILDREN (
 URI_ID         int(11)  NOT NULL default '0',
 CHILD_URI_ID   int(11)  NOT NULL default '0',
 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 default '0',
 LINK_TO_ID    int(11)  NOT NULL default '0',
 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          int(11)     NOT NULL default '0',
 OBJECT_ID        int(11)     NOT NULL default '0',
 SUBJECT_ID       int(11)     NOT NULL default '0',
 TYPE_ID          int(11)     NOT NULL default '0',
 EXPIRATION_DATE  date        NOT NULL default '0000-00-00',
 IS_INHERITABLE   tinyint(1)  NOT NULL default '0',
 IS_EXCLUSIVE     tinyint(1)  NOT NULL default '0',
 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      int(11)       NOT NULL auto_increment,
 BRANCH_STRING  varchar(255)  NOT NULL default '',
 PRIMARY KEY    (BRANCH_ID)
) TYPE=InnoDB;
# --------------------------------------------------------

CREATE TABLE LABEL (
 LABEL_ID      int(11)       NOT NULL auto_increment,
 LABEL_STRING  varchar(255)  NOT NULL default '',
 PRIMARY KEY   (LABEL_ID)
) TYPE=InnoDB;
# --------------------------------------------------------

CREATE TABLE VERSION (
 URI_ID        int(11)     NOT NULL default '0',
 IS_VERSIONED  tinyint(1)  NOT NULL default '0',
 PRIMARY KEY   (URI_ID)
) TYPE=InnoDB;
ALTER TABLE VERSION ADD CONSTRAINT REVISON_FK1 FOREIGN KEY
VERSION_FK1(URI_ID) REFERENCES URI (URI_ID);
# --------------------------------------------------------

CREATE TABLE VERSION_HISTORY (
 VERSION_ID   int(11)      NOT NULL auto_increment,
 URI_ID       int(11)      NOT NULL default '0',
 BRANCH_ID    int(11)      NOT NULL default '0',
 REVISION_NO  varchar(20)  NOT NULL default '',
 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      int(11)  NOT NULL default '0',
 PREDECESSOR_ID  int(11)  NOT NULL default '0',
 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   int(11)  NOT NULL default '0',
 LABEL_ID     int(11)  NOT NULL default '0',
 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 TABLE VERSION_CONTENT (
 VERSION_ID   int(11)  NOT NULL default '0',
 CONTENT      blob     NOT NULL,
 PRIMARY KEY  (VERSION_ID)
) TYPE=InnoDB;
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          int(11)       NOT NULL default '0',
 PROPERTY_NAMESPACE  varchar(50)   NOT NULL default '',
 PROPERTY_NAME       varchar(50)   NOT NULL default '',
 PROPERTY_VALUE      varchar(255)  NOT NULL default '',
 PROPERTY_TYPE       varchar(50)   NOT NULL default '',
 IS_PROTECTED        tinyint(1)    NOT NULL default '0',
 PRIMARY KEY         (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME)
) TYPE=InnoDB;
ALTER TABLE PROPERTIES ADD CONSTRAINT PROPERTIES_FK1 FOREIGN KEY
PROPERTIES_FK1(VERSION_ID) REFERENCES VERSION_HISTORY (VERSION_ID);
# --------------------------------------------------------

CREATE TABLE PERMISSIONS (
 OBJECT_ID       int(11)      NOT NULL default '0',
 SUBJECT_ID      int(11)      NOT NULL default '0',
 ACTION_ID       int(11)      NOT NULL default '0',
 VERSION_NO      varchar(20)  default NULL,
 IS_INHERITABLE  tinyint(1)   NOT NULL default '0',
 IS_NEGATIVE     tinyint(1)   NOT NULL default '0',
 SUCCESSION      int(11)      NOT NULL default '0',
 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); 

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]