Author: chathura Date: Fri Feb 8 00:38:11 2008 New Revision: 13446 Log:
Added the new mysql script. Removed the old mysql script. Added: branches/registry/1_0/modules/core/src/main/resources/database-scripts/mysql-complete.sql Removed: branches/registry/1_0/modules/core/src/main/resources/database-scripts/mysql-registry.sql Added: branches/registry/1_0/modules/core/src/main/resources/database-scripts/mysql-complete.sql ============================================================================== --- (empty file) +++ branches/registry/1_0/modules/core/src/main/resources/database-scripts/mysql-complete.sql Fri Feb 8 00:38:11 2008 @@ -0,0 +1,77 @@ +CREATE TABLE ARTIFACTS (AID INTEGER AUTO_INCREMENT, + PATH VARCHAR (500) NOT NULL, + MEDIA_TYPE VARCHAR (500), + DIRECTORY INTEGER NOT NULL, + STATE INTEGER, + AUTHOR VARCHAR (500), + CREATED_TIME TIMESTAMP, + DESCRIPTION VARCHAR (10000), + PRIMARY KEY (AID), + UNIQUE(PATH)); + +CREATE TABLE PROPERTIES (P_ID INTEGER AUTO_INCREMENT, + AID INTEGER NOT NULL, + PKEY VARCHAR (100) NOT NULL, + PVALUE VARCHAR (500) NOT NULL, + PRIMARY KEY (P_ID), + UNIQUE (AID, PKEY), + FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE CHILDREN (CID INTEGER AUTO_INCREMENT, + PARENT_ID INTEGER NOT NULL, + CHILD_ID INTEGER NOT NULL, + PRIMARY KEY (CID), + UNIQUE (PARENT_ID, CHILD_ID), + FOREIGN KEY (PARENT_ID) REFERENCES ARTIFACTS (AID), + FOREIGN KEY (CHILD_ID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE TAGS (TN_ID INTEGER AUTO_INCREMENT, + TAG_NAME VARCHAR (500) NOT NULL, + AID INTEGER NOT NULL, + USER_ID VARCHAR (20) NOT NULL, + TAGGED_TIME TIMESTAMP NOT NULL, + PRIMARY KEY (TN_ID), + UNIQUE (TAG_NAME, AID, USER_ID), + FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE COMMENTS (CM_ID INTEGER AUTO_INCREMENT, + AID INTEGER NOT NULL, + USER_ID VARCHAR (20) NOT NULL, + COMMENT_TEXT VARCHAR (500) NOT NULL, + COMMENTED_TIME TIMESTAMP NOT NULL, + PRIMARY KEY (CM_ID), + FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE LOGS (LOG_ID INTEGER AUTO_INCREMENT, + RID INTEGER, + USER_ID VARCHAR (20) NOT NULL, + LOGGED_TIME TIMESTAMP NOT NULL, + ACTION INTEGER NOT NULL, + ACTION_DATA VARCHAR (500), + PRIMARY KEY (LOG_ID)); + +CREATE TABLE RATINGS (R_ID INTEGER AUTO_INCREMENT, + AID INTEGER NOT NULL, + USER_ID VARCHAR (20) NOT NULL, + RATING INTEGER NOT NULL, + RATED_TIME TIMESTAMP NOT NULL, + PRIMARY KEY (R_ID), + FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE VERSIONS ( + AID INTEGER NOT NULL, + VN INTEGER NOT NULL, + CONTENT LONGBLOB, + AUTHOR VARCHAR (500), + UPDATED_TIME TIMESTAMP, + UNIQUE (AID,VN), + FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID)); + +CREATE TABLE UM_USERS (ID VARCHAR(255), USER_NAME VARCHAR(255) NOT NULL UNIQUE, PASSWORD VARCHAR(255) NOT NULL, PRIMARY KEY (ID)); +CREATE TABLE UM_USER_ATTRIBUTES (ID VARCHAR(255), ATTR_NAME VARCHAR(255) NOT NULL, ATTR_VALUE VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID)); +CREATE TABLE UM_ROLES (ID VARCHAR(255), ROLE_NAME VARCHAR(255) NOT NULL UNIQUE, PRIMARY KEY (ID)); +CREATE TABLE UM_ROLE_ATTRIBUTES (ID VARCHAR(255), ATTR_NAME VARCHAR(255) NOT NULL, ATTR_VALUE VARCHAR(255), ROLE_ID VARCHAR(255), FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, PRIMARY KEY (ID)); +CREATE TABLE UM_PERMISSIONS (ID VARCHAR(255), RESOURCE_ID VARCHAR(255) NOT NULL, ACTION VARCHAR(255) NOT NULL, PRIMARY KEY (ID)); +CREATE TABLE UM_ROLE_PERMISSIONS (ID VARCHAR(255), PERMISSION_ID VARCHAR(255), ROLE_ID VARCHAR(255), IS_ALLOWED SMALLINT NOT NULL, FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, PRIMARY KEY (ID)); +CREATE TABLE UM_USER_PERMISSIONS (ID VARCHAR(255), IS_ALLOWED SMALLINT NOT NULL, PERMISSION_ID VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (PERMISSION_ID) REFERENCES UM_PERMISSIONS(ID) ON DELETE CASCADE, FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID)); +CREATE TABLE UM_USER_ROLES (ID VARCHAR(255), ROLE_ID VARCHAR(255), USER_ID VARCHAR(255), FOREIGN KEY (ROLE_ID) REFERENCES UM_ROLES(ID) ON DELETE CASCADE, FOREIGN KEY (USER_ID) REFERENCES UM_USERS(ID) ON DELETE CASCADE, PRIMARY KEY (ID)); _______________________________________________ Registry-dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/registry-dev
