Re: Modified Proposal for a more efficient Slide Database Design

[Jean-Philippe Courson <co...@noos.fr>]

Hi Ashok,

I've successfully tested your new database scheme with PostgreSQL.

I just had one problem : you forgot to enclose a string with '. Here is 
a patch :

--- J2EEDescriptorsStore2.java.origin    Thu Aug  1 09:56:34 2002
+++ J2EEDescriptorsStore2.java    Thu Aug  1 09:55:59 2002
@@ -1159,7 +1159,7 @@
            
                 theSQL = new StringBuffer("insert into VERSION_HISTORY 
(URI_ID,BRANCH_ID,REVISION_NO) values(");
                 theSQL.append(l_uri).append(", ").append(l_brid);
-                theSQL.append(", 
").append(revisionDescriptor.getRevisionNumber().toString()).append(")");
+                theSQL.append(", 
'").append(revisionDescriptor.getRevisionNumber().toString()).append("')");
                 statement.execute(theSQL.toString());
             }
             long Version_ID = 
this.getVersionID(l_uri,revisionDescriptor.getBranchName(),revisionDescriptor.getRevisionNumber().toString());

I think that this new schema is a good starting point, but tables joins 
are the worst performance killer.
So, when performances are the problem, my opinion is that they should be 
avoid even if it leads to data duplication.

What is your opinion on this ?

You will find next database creation script for PostgreSQL if it could 
help someone.
Indexes on primary keys are automaticly created by PostgreSQL.

Regards

Jp

DROP TABLE URI;
DROP SEQUENCE NEXT_URI_ID;
DROP TABLE OBJECT;
DROP TABLE CHILDREN;
DROP TABLE LINKS;
DROP TABLE LOCKS;
DROP TABLE BRANCH;
DROP SEQUENCE NEXT_BRANCH_ID;
DROP TABLE LABEL;
DROP SEQUENCE NEXT_LABEL_ID;
DROP TABLE VERSION;
DROP TABLE VERSION_HISTORY;
DROP SEQUENCE NEXT_VERSION_HISTORY_ID;
DROP TABLE VERSION_PREDS;
DROP TABLE VERSION_LABELS;
DROP TABLE VERSION_CONTENT;
DROP TABLE PROPERTIES;
DROP TABLE PERMISSIONS;

CREATE SEQUENCE NEXT_URI_ID;
CREATE TABLE URI (
    URI_ID          BIGINT               DEFAULT NEXTVAL('NEXT_URI_ID') 
PRIMARY KEY,
    URI_STRING      VARCHAR(800)         NOT NULL
);

CREATE TABLE OBJECT (
    URI_ID          BIGINT               PRIMARY KEY,
    CLASS_NAME      VARCHAR(255)         NOT NULL,
    FOREIGN KEY (URI_ID) REFERENCES URI
);

CREATE TABLE CHILDREN (
    URI_ID          BIGINT               NOT NULL REFERENCES URI,
    CHILD_URI_ID    BIGINT               NOT NULL REFERENCES URI (URI_ID),
    PRIMARY KEY (URI_ID, CHILD_URI_ID)
);

CREATE TABLE LINKS (
    URI_ID          BIGINT               NOT NULL REFERENCES URI,
    LINK_TO_ID      BIGINT               NOT NULL REFERENCES URI (URI_ID),
    PRIMARY KEY (URI_ID, LINK_TO_ID)
);

CREATE TABLE LOCKS (
    LOCK_ID         BIGINT               PRIMARY KEY,
    OBJECT_ID       BIGINT               NOT NULL REFERENCES URI (URI_ID),
    SUBJECT_ID      BIGINT               NOT NULL REFERENCES URI (URI_ID),
    TYPE_ID         BIGINT               NOT NULL REFERENCES URI (URI_ID),
    EXPIRATION_DATE TIMESTAMP            NOT NULL,
    IS_INHERITABLE  SMALLINT             NOT NULL,
    IS_EXCLUSIVE    SMALLINT             NOT NULL,
    FOREIGN KEY (LOCK_ID) REFERENCES URI (URI_ID)
);

CREATE SEQUENCE NEXT_BRANCH_ID;
CREATE TABLE BRANCH (
    BRANCH_ID       BIGINT               DEFAULT 
NEXTVAL('NEXT_BRANCH_ID') PRIMARY KEY,
    BRANCH_STRING   VARCHAR(255)         NOT NULL
);

CREATE SEQUENCE NEXT_LABEL_ID;
CREATE TABLE LABEL (
    LABEL_ID        BIGINT               DEFAULT 
NEXTVAL('NEXT_LABEL_ID') PRIMARY KEY,
    LABEL_STRING    VARCHAR(255)         NOT NULL
);

CREATE TABLE VERSION (
    URI_ID          BIGINT               PRIMARY KEY,
    IS_VERSIONED    SMALLINT             NOT NULL,
    FOREIGN KEY (URI_ID) REFERENCES URI
);

CREATE SEQUENCE NEXT_VERSION_HISTORY_ID;
CREATE TABLE VERSION_HISTORY (
    VERSION_ID      BIGINT               DEFAULT 
NEXTVAL('NEXT_VERSION_HISTORY_ID') PRIMARY KEY,
    URI_ID          BIGINT               NOT NULL REFERENCES VERSION,
    BRANCH_ID       BIGINT               NOT NULL REFERENCES BRANCH,
    REVISION_NO     VARCHAR(20)                NOT NULL
);
CREATE INDEX VERSION_HISTORY_INDEX ON VERSION_HISTORY (URI_ID, 
BRANCH_ID, REVISION_NO);

CREATE TABLE VERSION_PREDS (
    VERSION_ID      BIGINT        NOT NULL REFERENCES  VERSION_HISTORY,
    PREDECESSOR_ID  BIGINT        NOT NULL REFERENCES  VERSION_HISTORY 
(VERSION_ID),
    PRIMARY KEY (VERSION_ID, PREDECESSOR_ID)
);

CREATE TABLE VERSION_LABELS (
    VERSION_ID        BIGINT        NOT NULL REFERENCES  VERSION_HISTORY,
    LABEL_ID        BIGINT        NOT NULL REFERENCES  LABEL,
    PRIMARY KEY (VERSION_ID, LABEL_ID)
);

CREATE TABLE VERSION_CONTENT (
    VERSION_ID        BIGINT        PRIMARY KEY,
    CONTENT         OID            NOT NULL,
    FOREIGN KEY (VERSION_ID) REFERENCES VERSION_HISTORY
);

CREATE TABLE PROPERTIES (
    VERSION_ID         BIGINT        NOT NULL REFERENCES  
VERSION_HISTORY,   
    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       SMALLINT         NOT NULL,
    PRIMARY KEY    (VERSION_ID, PROPERTY_NAMESPACE, PROPERTY_NAME)
);

CREATE TABLE PERMISSIONS (
    OBJECT_ID       BIGINT              NOT NULL REFERENCES URI (URI_ID),
    SUBJECT_ID      BIGINT              NOT NULL REFERENCES URI (URI_ID),
    ACTION_ID       BIGINT              NOT NULL REFERENCES URI (URI_ID),
    VERSION_NO      VARCHAR(20),
    IS_INHERITABLE  SMALLINT            NOT NULL,
    IS_NEGATIVE     SMALLINT            NOT NULL,
    PRIMARY KEY    (OBJECT_ID, SUBJECT_ID, ACTION_ID)
);



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>