http://git-wip-us.apache.org/repos/asf/stratos/blob/ee5e9639/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/derby.sql ---------------------------------------------------------------------- diff --git a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/derby.sql b/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/derby.sql deleted file mode 100644 index f3141bc..0000000 --- a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/derby.sql +++ /dev/null @@ -1,383 +0,0 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one -* or more contributor license agreements. See the NOTICE file -* distributed with this work for additional information -* regarding copyright ownership. The ASF licenses this file -* to you under the Apache License, Version 2.0 (the -* "License"); you may not use this file except in compliance -* with the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, -* software distributed under the License is distributed on an -* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -* KIND, either express or implied. See the License for the -* specific language governing permissions and limitations -* under the License. -*/ - -CREATE TABLE REG_CLUSTER_LOCK ( - REG_LOCK_NAME VARCHAR (20), - REG_LOCK_STATUS VARCHAR (20), - REG_LOCKED_TIME TIMESTAMP, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOCK_NAME) -); - -CREATE TABLE REG_LOG ( - REG_LOG_ID INTEGER GENERATED ALWAYS AS IDENTITY, - REG_PATH VARCHAR (2000), - REG_USER_ID VARCHAR (31) NOT NULL, - REG_LOGGED_TIME TIMESTAMP NOT NULL, - REG_ACTION INTEGER NOT NULL, - REG_ACTION_DATA VARCHAR (500), - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_PATH( - REG_PATH_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_PATH_VALUE VARCHAR(2000) NOT NULL, - REG_PATH_PARENT_ID INT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID) -); -CREATE INDEX REG_PATH_IND_BY_PATH_VALUE ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID); -CREATE INDEX REG_PATH_IND_BY_PARENT_ID ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID); - -CREATE TABLE REG_CONTENT ( - REG_CONTENT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_CONTENT_DATA BLOB, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_CONTENT_HISTORY ( - REG_CONTENT_ID INTEGER NOT NULL, - REG_CONTENT_DATA BLOB, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_RESOURCE ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_IND_BY_NAME ON REG_RESOURCE(REG_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE REG_RESOURCE_HISTORY ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER DEFAULT 0, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE REG_COMMENT ( - REG_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_COMMENT_TEXT VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_COMMENTED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_RESOURCE_COMMENT ( - REG_COMMENT_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - - -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE REG_RATING ( - REG_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_RATING INTEGER NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_RATED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_RESOURCE_RATING ( - REG_RATING_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE REG_TAG ( - REG_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_TAG_NAME VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_TAGGED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_RESOURCE_TAG ( - REG_TAG_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE REG_PROPERTY ( - REG_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_NAME VARCHAR(100) NOT NULL, - REG_VALUE VARCHAR(1000), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_RESOURCE_PROPERTY ( - REG_PROPERTY_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID); - --- CREATE TABLE REG_ASSOCIATION ( --- SRC_PATH_ID INTEGER, --- SRC_RESOURCE_NAME VARCHAR(256), --- SRC_VERSION INTEGER, --- TGT_PATH_ID INTEGER, --- TGT_RESOURCE_NAME VARCHAR(256), --- TGT_VERSION INTEGER --- ); --- --- ALTER TABLE REG_ASSOCIATION ADD CONSTRAINT REG_ASSOCIATION_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID); --- ALTER TABLE REG_ASSOCIATION ADD CONSTRAINT REG_ASSOCIATION_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID); --- CREATE INDEX REG_ASSOCIATION_IND_BY_SRC_VERSION ON REG_ASSOCIATION(SRC_VERSION); --- CREATE INDEX REG_ASSOCIATION_IND_BY_TGT_VERSION ON REG_ASSOCIATION(TGT_VERSION); --- CREATE INDEX REG_ASSOCIATION_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATION(SRC_RESOURCE_NAME); --- CREATE INDEX REG_ASSOCIATION_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATION(TGT_RESOURCE_NAME); - - - -CREATE TABLE REG_ASSOCIATION ( - REG_ASSOCIATION_ID INTEGER GENERATED ALWAYS AS IDENTITY, - REG_SOURCEPATH VARCHAR (2000) NOT NULL, - REG_TARGETPATH VARCHAR (2000) NOT NULL, - REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID) -); - -CREATE TABLE REG_SNAPSHOT ( - REG_SNAPSHOT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - REG_PATH_ID INTEGER NOT NULL, - REG_RESOURCE_NAME VARCHAR(256), - REG_RESOURCE_VIDS BLOB NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID) -); - -CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - - --- ################################ --- USER MANAGER TABLES --- ################################ - -CREATE TABLE UM_TENANT ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_DOMAIN_NAME VARCHAR(255) NOT NULL, - UM_EMAIL VARCHAR(255), - UM_ACTIVE BOOLEAN DEFAULT FALSE, - PRIMARY KEY (UM_ID), - UNIQUE(UM_DOMAIN_NAME)); - -CREATE TABLE UM_USER ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_USER_PASSWORD VARCHAR(255) NOT NULL, - UM_SALT_VALUE VARCHAR(31), - UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE, - UM_CHANGED_TIME TIMESTAMP NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_USER_NAME, UM_TENANT_ID) -); - -CREATE TABLE UM_ROLE ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_ROLE_NAME, UM_TENANT_ID) -); - - -CREATE TABLE UM_PERMISSION ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_RESOURCE_ID VARCHAR(255) NOT NULL, - UM_ACTION VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION - ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID); - -CREATE TABLE UM_ROLE_PERMISSION ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - --UNIQUE (PERMISSION_ID, UM_ROLE_NAME), - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_USER_PERMISSION ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - --UNIQUE (PERMISSION_ID, UM_USER_NAME), - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE , - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_USER_ROLE ( - UM_ID INTEGER GENERATED ALWAYS AS IDENTITY, - UM_ROLE_ID INTEGER NOT NULL, - UM_USER_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID), - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_USER_ATTRIBUTE ( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_ATTR_NAME VARCHAR(255) NOT NULL, - UM_ATTR_VALUE VARCHAR(1024), - UM_PROFILE_ID VARCHAR(255), - UM_USER_ID INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_DIALECT( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_DIALECT_URI VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_URI, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_CLAIM( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_DIALECT_ID INTEGER NOT NULL, - UM_CLAIM_URI VARCHAR(255) NOT NULL, - UM_DISPLAY_TAG VARCHAR(255), - UM_DESCRIPTION VARCHAR(255), - UM_MAPPED_ATTRIBUTE VARCHAR(255), - UM_REG_EX VARCHAR(255), - UM_SUPPORTED SMALLINT, - UM_REQUIRED SMALLINT, - UM_DISPLAY_ORDER INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID), - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_PROFILE_CONFIG( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_DIALECT_ID INTEGER, - UM_PROFILE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_CLAIM_BEHAVIOR( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_PROFILE_ID INTEGER, - UM_CLAIM_ID INTEGER, - UM_BEHAVIOUR SMALLINT, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID, UM_TENANT_ID), - FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_HYBRID_ROLE( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_ROLE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE UM_HYBRID_USER_ROLE( - UM_ID INTEGER GENERATED BY DEFAULT AS IDENTITY, - UM_USER_NAME VARCHAR(255), - UM_ROLE_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); -
http://git-wip-us.apache.org/repos/asf/stratos/blob/ee5e9639/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/h2.sql ---------------------------------------------------------------------- diff --git a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/h2.sql b/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/h2.sql deleted file mode 100644 index d7a7a53..0000000 --- a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/h2.sql +++ /dev/null @@ -1,356 +0,0 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one -* or more contributor license agreements. See the NOTICE file -* distributed with this work for additional information -* regarding copyright ownership. The ASF licenses this file -* to you under the Apache License, Version 2.0 (the -* "License"); you may not use this file except in compliance -* with the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, -* software distributed under the License is distributed on an -* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -* KIND, either express or implied. See the License for the -* specific language governing permissions and limitations -* under the License. -*/ - -CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK ( - REG_LOCK_NAME VARCHAR (20), - REG_LOCK_STATUS VARCHAR (20), - REG_LOCKED_TIME TIMESTAMP, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOCK_NAME) -); - -CREATE TABLE IF NOT EXISTS REG_LOG ( - REG_LOG_ID INTEGER AUTO_INCREMENT, - REG_PATH VARCHAR (2000), - REG_USER_ID VARCHAR (31) NOT NULL, - REG_LOGGED_TIME TIMESTAMP NOT NULL, - REG_ACTION INTEGER NOT NULL, - REG_ACTION_DATA VARCHAR (500), - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_PATH( - REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_PATH_VALUE VARCHAR(2000) NOT NULL, - REG_PATH_PARENT_ID INT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID) -); -CREATE INDEX IF NOT EXISTS REG_PATH_IND_BY_NAME ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_PATH_IND_BY_PARENT_ID ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID); - - -CREATE TABLE IF NOT EXISTS REG_CONTENT ( - REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_CONTENT_DATA LONGBLOB, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY ( - REG_CONTENT_ID INTEGER NOT NULL, - REG_CONTENT_DATA LONGBLOB, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL AUTO_INCREMENT, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -ALTER TABLE REG_RESOURCE ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_IND_BY_NAME ON REG_RESOURCE(REG_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_HISTORY_IND_BY_NAME ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_COMMENT ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_COMMENT_TEXT VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_COMMENTED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT ( - REG_COMMENT_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_COMMENT_IND_BY_VERSION ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_RATING ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_RATING INTEGER NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_RATED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING ( - REG_RATING_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_RATING_IND_BY_VERSION ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID); - - -CREATE TABLE IF NOT EXISTS REG_TAG ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_TAG_NAME VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_TAGGED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG ( - REG_TAG_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_TAG_IND_BY_VERSION ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_PROPERTY ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_NAME VARCHAR(100) NOT NULL, - REG_VALUE VARCHAR(1000), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY ( - REG_PROPERTY_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -); - -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT IF NOT EXISTS REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_RESOURCE_PROPERTY_IND_BY_VERSION ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_ASSOCIATION ( - REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT, - REG_SOURCEPATH VARCHAR (2000) NOT NULL, - REG_TARGETPATH VARCHAR (2000) NOT NULL, - REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS REG_SNAPSHOT ( - REG_SNAPSHOT_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_PATH_ID INTEGER NOT NULL, - REG_RESOURCE_NAME VARCHAR (256), - REG_RESOURCE_VIDS LONGBLOB NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID) -); - -ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT IF NOT EXISTS REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -CREATE INDEX IF NOT EXISTS REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - --- ################################ --- USER MANAGER TABLES --- ################################ - -CREATE TABLE IF NOT EXISTS UM_TENANT ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DOMAIN_NAME VARCHAR(255) NOT NULL, - UM_EMAIL VARCHAR(255), - UM_ACTIVE BOOLEAN DEFAULT FALSE, - UM_CREATED_DATE TIMESTAMP NOT NULL, - PRIMARY KEY (UM_ID), - UNIQUE(UM_DOMAIN_NAME)); - -CREATE TABLE IF NOT EXISTS UM_USER ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_USER_PASSWORD VARCHAR(255) NOT NULL, - UM_SALT_VALUE VARCHAR(31), - UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE, - UM_CHANGED_TIME TIMESTAMP NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_USER_NAME, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_USER_ATTRIBUTE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ATTR_NAME VARCHAR(255) NOT NULL, - UM_ATTR_VALUE VARCHAR(1024), - UM_PROFILE_ID VARCHAR(255), - UM_USER_ID INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_ROLE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_RESOURCE_ID VARCHAR(255) NOT NULL, - UM_ACTION VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID)); - -CREATE INDEX IF NOT EXISTS INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID); - -CREATE TABLE IF NOT EXISTS UM_ROLE_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID), - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_USER_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UNIQUE (UM_PERMISSION_ID, UM_USER_NAME, UM_TENANT_ID), - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_USER_ROLE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_ID INTEGER NOT NULL, - UM_USER_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID), - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID)); - -CREATE TABLE IF NOT EXISTS UM_DIALECT( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_URI VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_URI, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - - -CREATE TABLE IF NOT EXISTS UM_CLAIM( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_ID INTEGER NOT NULL, - UM_CLAIM_URI VARCHAR(255) NOT NULL, - UM_DISPLAY_TAG VARCHAR(255), - UM_DESCRIPTION VARCHAR(255), - UM_MAPPED_ATTRIBUTE VARCHAR(255), - UM_REG_EX VARCHAR(255), - UM_SUPPORTED SMALLINT, - UM_REQUIRED SMALLINT, - UM_DISPLAY_ORDER INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID), - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS UM_PROFILE_CONFIG( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_ID INTEGER, - UM_PROFILE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PROFILE_ID INTEGER, - UM_CLAIM_ID INTEGER, - UM_BEHAVIOUR SMALLINT, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID, UM_TENANT_ID), - FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS UM_HYBRID_ROLE( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -CREATE TABLE IF NOT EXISTS UM_HYBRID_USER_ROLE( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_USER_NAME VARCHAR(255), - UM_ROLE_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); http://git-wip-us.apache.org/repos/asf/stratos/blob/ee5e9639/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mssql.sql ---------------------------------------------------------------------- diff --git a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mssql.sql b/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mssql.sql deleted file mode 100755 index 8aa0b84..0000000 --- a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mssql.sql +++ /dev/null @@ -1,513 +0,0 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one -* or more contributor license agreements. See the NOTICE file -* distributed with this work for additional information -* regarding copyright ownership. The ASF licenses this file -* to you under the Apache License, Version 2.0 (the -* "License"); you may not use this file except in compliance -* with the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, -* software distributed under the License is distributed on an -* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -* KIND, either express or implied. See the License for the -* specific language governing permissions and limitations -* under the License. -*/ - ---create table REG_CLUSTER_LOCK -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CLUSTER_LOCK]') AND TYPE IN (N'U')) -CREATE TABLE REG_CLUSTER_LOCK ( - REG_LOCK_NAME VARCHAR (20), - REG_LOCK_STATUS VARCHAR (20), - REG_LOCKED_TIME DATETIME, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOCK_NAME) -); - ---create table REG_LOG - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_LOG]') AND TYPE IN (N'U')) - -CREATE TABLE REG_LOG ( - REG_LOG_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_PATH VARCHAR (2000), - REG_USER_ID VARCHAR (31) NOT NULL, - REG_LOGGED_TIME DATETIME NOT NULL, - REG_ACTION INTEGER NOT NULL, - REG_ACTION_DATA VARCHAR (500), - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID) -); - ---create table regpath - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PATH]') AND TYPE IN (N'U')) -CREATE TABLE REG_PATH( - REG_PATH_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_PATH_VALUE VARCHAR(895) NOT NULL, - REG_PATH_PARENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID) -); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_PATH_IND_BY_PATH_VALUE') -DROP INDEX REG_PATH.REG_PATH_IND_BY_PATH_VALUE -CREATE INDEX REG_PATH_IND_BY_PATH_VALUE ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID); - - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_PATH_IND_BY_PARENT_ID') -DROP INDEX REG_PATH.REG_PATH_IND_BY_PARENT_ID -CREATE INDEX REG_PATH_IND_BY_PARENT_ID ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID); ---create table regcontent - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT]') AND TYPE IN (N'U')) -CREATE TABLE REG_CONTENT ( - REG_CONTENT_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_CONTENT_DATA VARBINARY(MAX), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - ---create table REG_CONTENT_HISTORY -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT_HISTORY]') AND TYPE IN (N'U')) -CREATE TABLE REG_CONTENT_HISTORY ( - REG_CONTENT_ID INTEGER NOT NULL, - REG_CONTENT_DATA VARBINARY(MAX), - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -); - - ---create table REG_RESOURCE -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER IDENTITY(1,1) NOT NULL, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME DATETIME NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME DATETIME NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0 - CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_FK_BY_PATH_ID]') AND PARENT_OBJECT_ID = OBJECT_ID(N'DBO.[REG_RESOURCE]')) -ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - ---This foriegn key constrainst is maintained from the code level ---IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_FK_BY_CONTENT_ID]') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE]')) ---ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_NAME') -DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_NAME -CREATE INDEX REG_RESOURCE_IND_BY_NAME ON REG_RESOURCE(REG_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_PATH_ID_NAME') -DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_PATH_ID_NAME -CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - - ---create table REG_RESOURCE_HISTORY -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_HISTORY]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE_HISTORY ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME DATETIME NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME DATETIME NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HIST_FK_BY_PATHID]') AND parent_object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HISTORY]')) -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HIST_FK_BY_CONTENT_ID]') AND parent_object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HISTORY]')) -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_HISTORY_IND_BY_NAME') -DROP INDEX REG_RESOURCE_HISTORY.REG_RESOURCE_HISTORY_IND_BY_NAME -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME') -DROP INDEX REG_RESOURCE_HISTORY.REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME ON REG_RESOURCE_HISTORY(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - ---create table REG_COMMENT - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_COMMENT]') AND TYPE IN (N'U')) -CREATE TABLE REG_COMMENT ( - REG_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_COMMENT_TEXT VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_COMMENTED_TIME DATETIME NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - ---create table REG_RESOURCE_COMMENT -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_COMMENT]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE_COMMENT ( - REG_COMMENT_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_COMMENT PRIMARY KEY(REG_COMMENT_ID, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_COMMENT_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_COMMENT')) -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_COMMENT')) -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME') -DROP INDEX REG_RESOURCE_COMMENT.REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_COMMENT_IND_BY_VERSION') -DROP INDEX REG_RESOURCE_COMMENT.REG_RESOURCE_COMMENT_IND_BY_VERSION -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID); - ---create table REG_RATING -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RATING]') AND TYPE IN (N'U')) -CREATE TABLE REG_RATING ( - REG_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_RATING INTEGER NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_RATED_TIME DATETIME NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - ---create table REG_RESOURCE_RATING - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_RATING]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE_RATING ( - REG_RATING_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_RATING PRIMARY KEY(REG_RATING_ID, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING')) -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING_FK_BY_RATING_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING')) -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME') -DROP INDEX REG_RESOURCE_RATING.REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME -CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_RATING_IND_BY_VERSION') -DROP INDEX REG_RESOURCE_RATING.REG_RESOURCE_RATING_IND_BY_VERSION -CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID); - ---create table REG_TAG - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_TAG]') AND TYPE IN (N'U')) -CREATE TABLE REG_TAG ( - REG_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_TAG_NAME VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_TAGGED_TIME DATETIME NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - - - ---create table REG_RESOURCE_TAG - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_TAG]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE_TAG ( - REG_TAG_ID INTEGER NOT NULL, - REG_VERSION INTEGER DEFAULT 0, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_TAG PRIMARY KEY(REG_TAG_ID, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG')) -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG_FK_BY_TAG_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG')) -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME') -DROP INDEX REG_RESOURCE_TAG.REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME -CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_TAG_IND_BY_VERSION') -DROP INDEX REG_RESOURCE_TAG.REG_RESOURCE_TAG_IND_BY_VERSION -CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID); - ---CREATE TABLE REG_PROPERTY - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PROPERTY]') AND TYPE IN (N'U')) -CREATE TABLE REG_PROPERTY ( - REG_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_NAME VARCHAR(100) NOT NULL, - REG_VALUE VARCHAR(1000), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID) -); - ---CREATE TABLE REG_RESOURCE_PROPERTY - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_PROPERTY]') AND TYPE IN (N'U')) -CREATE TABLE REG_RESOURCE_PROPERTY ( - REG_PROPERTY_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_PROPERTY PRIMARY KEY(REG_PROPERTY_ID, REG_TENANT_ID) -); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY')) -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY_FK_BY_TAG_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY')) -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME') -DROP INDEX REG_RESOURCE_PROPERTY.REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_PROPERTY_IND_BY_VERSION') -DROP INDEX REG_RESOURCE_PROPERTY.REG_RESOURCE_PROPERTY_IND_BY_VERSION -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID); - ---CREATE TABLE REG_ASSOCIATION - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_ASSOCIATION]') AND TYPE IN (N'U')) -CREATE TABLE REG_ASSOCIATION ( - REG_ASSOCIATION_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_SOURCEPATH VARCHAR (2000) NOT NULL, - REG_TARGETPATH VARCHAR (2000) NOT NULL, - REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID) -); - ---CREATE TABLE REG_SNAPSHOT -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_SNAPSHOT]') AND TYPE IN (N'U')) -CREATE TABLE REG_SNAPSHOT ( - REG_SNAPSHOT_ID INTEGER IDENTITY(1,1) NOT NULL, - REG_PATH_ID INTEGER NOT NULL, - REG_RESOURCE_NAME VARCHAR (256), - REG_RESOURCE_VIDS VARBINARY(MAX) NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID) -); -IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_SNAPSHOT_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_SNAPSHOT')) -ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - - -IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME') -DROP INDEX REG_SNAPSHOT.REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME -CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - --- ################################ --- USER MANAGER TABLES --- ################################ - ---CREATE TABLE UM_TENANT_ - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_TENANT]') AND TYPE IN (N'U')) -CREATE TABLE UM_TENANT ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_DOMAIN_NAME VARCHAR(255) NOT NULL, - UM_EMAIL VARCHAR(255), - UM_ACTIVE BIT DEFAULT 0, - PRIMARY KEY (UM_ID), - UNIQUE(UM_DOMAIN_NAME)); - ---CREATE TABLE UM_USER - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER]') AND TYPE IN (N'U')) -CREATE TABLE UM_USER ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_USER_PASSWORD VARCHAR(255) NOT NULL, - UM_SALT_VALUE VARCHAR(31), - UM_REQUIRE_CHANGE BIT DEFAULT 0, - UM_CHANGED_TIME DATETIME NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_USER_NAME, UM_TENANT_ID) -); - ---CREATE TABLE UM_USER_ATTRIBUTE - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_ATTRIBUTE]') AND TYPE IN (N'U')) -CREATE TABLE UM_USER_ATTRIBUTE ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_ATTR_NAME VARCHAR(255) NOT NULL, - UM_ATTR_VALUE VARCHAR(1024), - UM_PROFILE_ID VARCHAR(255), - UM_USER_ID INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID)); - ---CREATE TABLE UM_ROLE - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_ROLE]') AND TYPE IN (N'U')) -CREATE TABLE UM_ROLE ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_ROLE_NAME, UM_TENANT_ID) -); - - ---CREATE TABLE UM_PERMISSION - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_PERMISSION]') AND TYPE IN (N'U')) -CREATE TABLE UM_PERMISSION ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_RESOURCE_ID VARCHAR(255) NOT NULL, - UM_ACTION VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - -IF EXISTS (SELECT name FROM sysindexes WHERE name = 'INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION') -DROP INDEX UM_PERMISSION.INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION -CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID); - ---CREATE TABLE UM_ROLE_PERMISSION - -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_ROLE_PERMISSION]') AND TYPE IN (N'U')) -CREATE TABLE UM_ROLE_PERMISSION ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID), - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - ---CREATE TABLE UM_USER_PERMISSION -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_PERMISSION]') AND TYPE IN (N'U')) -CREATE TABLE UM_USER_PERMISSION ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_PERMISSION_ID, UM_USER_NAME, UM_TENANT_ID), - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_USER_ROLE -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_ROLE]') AND TYPE IN (N'U')) -CREATE TABLE UM_USER_ROLE ( - UM_ID INTEGER IDENTITY(1,1) NOT NULL, - UM_ROLE_ID INTEGER NOT NULL, - UM_USER_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID), - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_DIALECT -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_DIALECT]') AND TYPE IN (N'U')) -CREATE TABLE UM_DIALECT( - UM_ID INTEGER IDENTITY(1, 1), - UM_DIALECT_URI VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_URI, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_CLAIM -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_CLAIM]') AND TYPE IN (N'U')) -CREATE TABLE UM_CLAIM( - UM_ID INTEGER IDENTITY(1, 1), - UM_DIALECT_ID INTEGER, - UM_CLAIM_URI VARCHAR(255), - UM_DISPLAY_TAG VARCHAR(255), - UM_DESCRIPTION VARCHAR(255), - UM_MAPPED_ATTRIBUTE VARCHAR(255), - UM_REG_EX VARCHAR(255), - UM_SUPPORTED SMALLINT, - UM_REQUIRED SMALLINT, - UM_DISPLAY_ORDER INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_PROFILE_CONFIG -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_PROFILE_CONFIG]') AND TYPE IN (N'U')) -CREATE TABLE UM_PROFILE_CONFIG( - UM_ID INTEGER IDENTITY(1, 1), - UM_DIALECT_ID INTEGER, - UM_PROFILE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_CLAIM_BEHAVIOR -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_CLAIM_BEHAVIOR]') AND TYPE IN (N'U')) -CREATE TABLE UM_CLAIM_BEHAVIOR( - UM_ID INTEGER IDENTITY(1, 1), - UM_PROFILE_ID INTEGER, - UM_CLAIM_ID INTEGER, - UM_BEHAVIOUR SMALLINT, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID, UM_TENANT_ID), - FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_HYBRID_ROLE -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_ROLE]') AND TYPE IN (N'U')) -CREATE TABLE UM_HYBRID_ROLE( - UM_ID INTEGER IDENTITY(1, 1), - UM_ROLE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - --- create table UM_HYBRID_USER_ROLE -IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_USER_ROLE]') AND TYPE IN (N'U')) -CREATE TABLE UM_HYBRID_USER_ROLE( - UM_ID INTEGER IDENTITY(1, 1), - UM_USER_NAME VARCHAR(255), - UM_ROLE_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -); - http://git-wip-us.apache.org/repos/asf/stratos/blob/ee5e9639/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mysql.sql ---------------------------------------------------------------------- diff --git a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mysql.sql b/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mysql.sql deleted file mode 100644 index a72241b..0000000 --- a/components/org.apache.stratos.throttling.manager/src/test/resources/carbon-home/dbscripts/mysql.sql +++ /dev/null @@ -1,389 +0,0 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one -* or more contributor license agreements. See the NOTICE file -* distributed with this work for additional information -* regarding copyright ownership. The ASF licenses this file -* to you under the Apache License, Version 2.0 (the -* "License"); you may not use this file except in compliance -* with the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, -* software distributed under the License is distributed on an -* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY -* KIND, either express or implied. See the License for the -* specific language governing permissions and limitations -* under the License. -*/ - -CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK ( - REG_LOCK_NAME VARCHAR (20), - REG_LOCK_STATUS VARCHAR (20), - REG_LOCKED_TIME TIMESTAMP, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOCK_NAME) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_LOG ( - REG_LOG_ID INTEGER AUTO_INCREMENT, - REG_PATH VARCHAR (750), - REG_USER_ID VARCHAR (31) NOT NULL, - REG_LOGGED_TIME TIMESTAMP NOT NULL, - REG_ACTION INTEGER NOT NULL, - REG_ACTION_DATA VARCHAR (500), - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID) -)ENGINE INNODB; - --- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750. --- See CARBON-5917. - -CREATE TABLE IF NOT EXISTS REG_PATH( - REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_PATH_VALUE VARCHAR(750) NOT NULL, - REG_PATH_PARENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID); -CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_CONTENT ( - REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_CONTENT_DATA LONGBLOB, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY ( - REG_CONTENT_ID INTEGER NOT NULL, - REG_CONTENT_DATA LONGBLOB, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_RESOURCE ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL AUTO_INCREMENT, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY ( - REG_PATH_ID INTEGER NOT NULL, - REG_NAME VARCHAR(256), - REG_VERSION INTEGER NOT NULL, - REG_MEDIA_TYPE VARCHAR(500), - REG_CREATOR VARCHAR(31) NOT NULL, - REG_CREATED_TIME TIMESTAMP NOT NULL, - REG_LAST_UPDATOR VARCHAR(31), - REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL, - REG_DESCRIPTION VARCHAR(1000), - REG_CONTENT_ID INTEGER, - REG_DELETED SMALLINT, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID) -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_HISTORY ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_COMMENT ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_COMMENT_TEXT VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_COMMENTED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT ( - REG_COMMENT_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_COMMENT ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_RATING ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_RATING INTEGER NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_RATED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING ( - REG_RATING_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_RATING ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID); - - -CREATE TABLE IF NOT EXISTS REG_TAG ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_TAG_NAME VARCHAR(500) NOT NULL, - REG_USER_ID VARCHAR(31) NOT NULL, - REG_TAGGED_TIME TIMESTAMP NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG ( - REG_TAG_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_TAG ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID); - -CREATE TABLE IF NOT EXISTS REG_PROPERTY ( - REG_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_NAME VARCHAR(100) NOT NULL, - REG_VALUE VARCHAR(1000), - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY ( - REG_PROPERTY_ID INTEGER NOT NULL, - REG_VERSION INTEGER, - REG_PATH_ID INTEGER, - REG_RESOURCE_NAME VARCHAR(256), - REG_TENANT_ID INTEGER DEFAULT 0 -)ENGINE INNODB; - -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); -ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); -CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID); - --- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS ( --- SRC_PATH_ID INTEGER, --- SRC_RESOURCE_NAME VARCHAR(256), --- SRC_VERSION INTEGER, --- TGT_PATH_ID INTEGER, --- TGT_RESOURCE_NAME VARCHAR(256), --- TGT_VERSION INTEGER --- )ENGINE INNODB; --- --- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID); --- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID); --- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION); --- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION); --- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME); --- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME); - - - -CREATE TABLE IF NOT EXISTS REG_ASSOCIATION ( - REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT, - REG_SOURCEPATH VARCHAR (750) NOT NULL, - REG_TARGETPATH VARCHAR (750) NOT NULL, - REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE IF NOT EXISTS REG_SNAPSHOT ( - REG_SNAPSHOT_ID INTEGER NOT NULL AUTO_INCREMENT, - REG_PATH_ID INTEGER NOT NULL, - REG_RESOURCE_NAME VARCHAR(255), - REG_RESOURCE_VIDS LONGBLOB NOT NULL, - REG_TENANT_ID INTEGER DEFAULT 0, - CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID) -)ENGINE INNODB; - -CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); - -ALTER TABLE REG_SNAPSHOT ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID); - - --- ################################ --- USER MANAGER TABLES --- ################################ - -CREATE TABLE UM_TENANT ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DOMAIN_NAME VARCHAR(255) NOT NULL, - UM_EMAIL VARCHAR(255), - UM_ACTIVE BOOLEAN DEFAULT FALSE, - PRIMARY KEY (UM_ID), - UNIQUE(UM_DOMAIN_NAME)); - -CREATE TABLE UM_USER ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_USER_PASSWORD VARCHAR(255) NOT NULL, - UM_SALT_VALUE VARCHAR(31), - UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE, - UM_CHANGED_TIME TIMESTAMP NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_USER_NAME, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_ROLE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID), - UNIQUE(UM_ROLE_NAME, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_RESOURCE_ID VARCHAR(255) NOT NULL, - UM_ACTION VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION - ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID); - -CREATE TABLE UM_ROLE_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_ROLE_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - --- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID) -CREATE TABLE UM_USER_PERMISSION ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PERMISSION_ID INTEGER NOT NULL, - UM_USER_NAME VARCHAR(255) NOT NULL, - UM_IS_ALLOWED SMALLINT NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - --- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID) -CREATE TABLE UM_USER_ROLE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_ID INTEGER NOT NULL, - UM_USER_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID), - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - - -CREATE TABLE UM_USER_ATTRIBUTE ( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ATTR_NAME VARCHAR(255) NOT NULL, - UM_ATTR_VALUE VARCHAR(1024), - UM_PROFILE_ID VARCHAR(255), - UM_USER_ID INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - - - -CREATE TABLE UM_DIALECT( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_URI VARCHAR(255) NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_URI, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_CLAIM( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_ID INTEGER NOT NULL, - UM_CLAIM_URI VARCHAR(255) NOT NULL, - UM_DISPLAY_TAG VARCHAR(255), - UM_DESCRIPTION VARCHAR(255), - UM_MAPPED_ATTRIBUTE VARCHAR(255), - UM_REG_EX VARCHAR(255), - UM_SUPPORTED SMALLINT, - UM_REQUIRED SMALLINT, - UM_DISPLAY_ORDER INTEGER, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID), - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_PROFILE_CONFIG( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_DIALECT_ID INTEGER NOT NULL, - UM_PROFILE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_CLAIM_BEHAVIOR( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_PROFILE_ID INTEGER, - UM_CLAIM_ID INTEGER, - UM_BEHAVIOUR SMALLINT, - UM_TENANT_ID INTEGER DEFAULT 0, - FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID, UM_TENANT_ID), - FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_HYBRID_ROLE( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_ROLE_NAME VARCHAR(255), - UM_TENANT_ID INTEGER DEFAULT 0, - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; - -CREATE TABLE UM_HYBRID_USER_ROLE( - UM_ID INTEGER NOT NULL AUTO_INCREMENT, - UM_USER_NAME VARCHAR(255), - UM_ROLE_ID INTEGER NOT NULL, - UM_TENANT_ID INTEGER DEFAULT 0, - UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID), - FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID), - PRIMARY KEY (UM_ID, UM_TENANT_ID) -)ENGINE INNODB; -
