From 52039edadd2f35c2de12878ea939742773fac87d Mon Sep 17 00:00:00 2001 From: "Benjamin, Max (mb388a)" Date: Thu, 11 Apr 2019 13:33:09 -0400 Subject: vnr vrf fk issue Updated flyway migration script to rename object as per review comments. Used Tosca node type for determining VRF and VNF nodes updated code based on review comments for schema.sql Altered ConfigurationCustomizationResource for VRF-VNF to have bidirectional relationship ToscaResourceInstaller ConfigurationResourceCustomization was made bidrectional Change-Id: Ib45151ff7a40edca38d512ae76f8889b1cc4d0c4 Issue-ID: SO-1777 Signed-off-by: Benjamin, Max (mb388a) --- .../migration/V5.7.1__WorkFlowDesignerTables.sql | 175 +++++++++++++++++++++ .../V5.7__Use_ID_Configuration_Customization.sql | 75 +++++++++ .../db/migration/V5.7__WorkFlowDesignerTables.sql | 175 --------------------- 3 files changed, 250 insertions(+), 175 deletions(-) create mode 100644 adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7.1__WorkFlowDesignerTables.sql create mode 100644 adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__Use_ID_Configuration_Customization.sql delete mode 100644 adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__WorkFlowDesignerTables.sql (limited to 'adapters/mso-catalog-db-adapter/src/main/resources/db') diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7.1__WorkFlowDesignerTables.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7.1__WorkFlowDesignerTables.sql new file mode 100644 index 0000000000..e44a6b97ba --- /dev/null +++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7.1__WorkFlowDesignerTables.sql @@ -0,0 +1,175 @@ +USE catalogdb; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`workflow` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `ARTIFACT_UUID` VARCHAR(200) NOT NULL, + `ARTIFACT_NAME` VARCHAR(200) NOT NULL, + `NAME` VARCHAR(200) NOT NULL, + `OPERATION_NAME` VARCHAR(200) NULL, + `VERSION` DOUBLE NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL, + `BODY` LONGTEXT NULL, + `RESOURCE_TARGET` VARCHAR(200) NOT NULL, + `SOURCE` VARCHAR(200) NOT NULL, + `TIMEOUT_MINUTES` INT(11) NULL DEFAULT NULL, + `ARTIFACT_CHECKSUM` VARCHAR(200) NULL DEFAULT 'MANUAL RECORD', + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_workflow` (`ARTIFACT_UUID` ASC, `NAME` ASC, `VERSION` ASC, `SOURCE` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`vnf_resource_to_workflow` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, + `WORKFLOW_ID` INT(11) NOT NULL, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_vnf_resource_to_workflow` (`VNF_RESOURCE_MODEL_UUID` ASC, `WORKFLOW_ID` ASC), + INDEX `fk_vnf_resource_to_workflow__workflow1_idx` (`WORKFLOW_ID` ASC), + INDEX `fk_vnf_resource_to_workflow__vnf_res_mod_uuid_idx` (`VNF_RESOURCE_MODEL_UUID` ASC), + CONSTRAINT `fk_vnf_resource_to_workflow__vnf_resource1` + FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) + REFERENCES `catalogdb`.`vnf_resource` (`MODEL_UUID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_vnf_resource_to_workflow__workflow1` + FOREIGN KEY (`WORKFLOW_ID`) + REFERENCES `catalogdb`.`workflow` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `NAME` VARCHAR(200) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NOT NULL, + `VERSION` DOUBLE NOT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_activity_spec` (`NAME` ASC, `VERSION` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`user_parameters` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `NAME` VARCHAR(200) NOT NULL, + `PAYLOAD_LOCATION` VARCHAR(500) NULL, + `LABEL` VARCHAR(200) NOT NULL, + `TYPE` VARCHAR(200) NOT NULL, + `DESCRIPTION` VARCHAR(1200) NULL, + `IS_REQUIRED` TINYINT(1) NOT NULL, + `MAX_LENGTH` INT(11) NULL, + `ALLOWABLE_CHARS` VARCHAR(200) NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_user_parameters` (`NAME` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`workflow_activity_spec_sequence` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `WORKFLOW_ID` INT(11) NOT NULL, + `ACTIVITY_SPEC_ID` INT(11) NOT NULL, + `SEQ_NO` INT(11) NOT NULL, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_workflow_activity_spec_sequence` (`WORKFLOW_ID` ASC, `ACTIVITY_SPEC_ID` ASC, `SEQ_NO` ASC), + INDEX `fk_workflow_activity_spec_sequence__activity_spec_idx` (`ACTIVITY_SPEC_ID` ASC), + INDEX `fk_workflow_activity_spec_sequence__workflow_actifact_uuid_idx` (`WORKFLOW_ID` ASC), + CONSTRAINT `fk_workflow_activity_spec_sequence__activity_spec1` + FOREIGN KEY (`ACTIVITY_SPEC_ID`) + REFERENCES `catalogdb`.`activity_spec` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_workflow_activity_spec_sequence__workflow1` + FOREIGN KEY (`WORKFLOW_ID`) + REFERENCES `catalogdb`.`workflow` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_parameters` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `NAME` VARCHAR(200) NOT NULL, + `TYPE` VARCHAR(200) NOT NULL, + `DIRECTION` VARCHAR(200) NULL, + `DESCRIPTION` VARCHAR(1200) NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_activity_spec_parameters` (`NAME` ASC, `DIRECTION` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_categories` ( + `ID` INT(11) NOT NULL, + `NAME` VARCHAR(200) NOT NULL, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_activity_spec_categories` (`NAME` ASC)) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_activity_spec_categories` ( + `ID` INT(11) NOT NULL, + `ACTIVITY_SPEC_ID` INT(11) NOT NULL, + `ACTIVITY_SPEC_CATEGORIES_ID` INT(11) NOT NULL, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_activity_spec_to_activity_spec_categories` (`ACTIVITY_SPEC_ID` ASC, `ACTIVITY_SPEC_CATEGORIES_ID` ASC), + INDEX `fk_activity_spec_to_activity_spec_categories__activity_spec_idx` (`ACTIVITY_SPEC_CATEGORIES_ID` ASC), + INDEX `fk_activity_spec_to_activity_spec_categories__activity_spec_idx1` (`ACTIVITY_SPEC_ID` ASC), + CONSTRAINT `fk_activity_spec_to_activity_spec_categories__activity_spec1` + FOREIGN KEY (`ACTIVITY_SPEC_ID`) + REFERENCES `catalogdb`.`activity_spec` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_activity_spec_to_activity_spec_categories__activity_spec_c1` + FOREIGN KEY (`ACTIVITY_SPEC_CATEGORIES_ID`) + REFERENCES `catalogdb`.`activity_spec_categories` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_activity_spec_parameters` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `ACTIVITY_SPEC_ID` INT(11) NOT NULL, + `ACTIVITY_SPEC_PARAMETERS_ID` INT(11) NOT NULL, + PRIMARY KEY (`ID`), + INDEX `fk_activity_spec_to_activity_spec_params__act_sp_param_id_idx` (`ACTIVITY_SPEC_PARAMETERS_ID` ASC), + UNIQUE INDEX `UK_activity_spec_to_activity_spec_parameters` (`ACTIVITY_SPEC_ID` ASC, `ACTIVITY_SPEC_PARAMETERS_ID` ASC), + INDEX `fk_activity_spec_to_activity_spec_parameters__act_spec_id_idx` (`ACTIVITY_SPEC_ID` ASC), + CONSTRAINT `fk_activity_spec_to_activity_spec_parameters__activity_spec_1` + FOREIGN KEY (`ACTIVITY_SPEC_ID`) + REFERENCES `catalogdb`.`activity_spec` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_activity_spec_to_activity_spec_parameters__activ_spec_param1` + FOREIGN KEY (`ACTIVITY_SPEC_PARAMETERS_ID`) + REFERENCES `catalogdb`.`activity_spec_parameters` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + +CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_user_parameters` ( + `ID` INT(11) NOT NULL, + `ACTIVITY_SPEC_ID` INT(11) NOT NULL, + `USER_PARAMETERS_ID` INT(11) NOT NULL, + PRIMARY KEY (`ID`), + UNIQUE INDEX `UK_activity_spec_to_user_parameters` (`ACTIVITY_SPEC_ID` ASC, `USER_PARAMETERS_ID` ASC), + INDEX `fk_activity_spec_to_user_parameters__user_parameters1_idx` (`USER_PARAMETERS_ID` ASC), + INDEX `fk_activity_spec_to_user_parameters__activity_spec1_idx` (`ACTIVITY_SPEC_ID` ASC), + CONSTRAINT `fk_activity_spec_to_user_parameters__activity_spec1` + FOREIGN KEY (`ACTIVITY_SPEC_ID`) + REFERENCES `catalogdb`.`activity_spec` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE, + CONSTRAINT `fk_activity_spec_to_user_parameters__user_parameters1` + FOREIGN KEY (`USER_PARAMETERS_ID`) + REFERENCES `catalogdb`.`user_parameters` (`ID`) + ON DELETE CASCADE + ON UPDATE CASCADE) +ENGINE = InnoDB +DEFAULT CHARACTER SET = latin1; + + diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__Use_ID_Configuration_Customization.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__Use_ID_Configuration_Customization.sql new file mode 100644 index 0000000000..b39331234d --- /dev/null +++ b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__Use_ID_Configuration_Customization.sql @@ -0,0 +1,75 @@ +USE catalogdb; + +/* Drop existing foreign key */ +ALTER TABLE `catalogdb`.`configuration_customization` +DROP FOREIGN KEY IF EXISTS `fk_configuration_customization__configuration_customization1`; + +ALTER TABLE `catalogdb`.`configuration_customization` +DROP FOREIGN KEY IF EXISTS `fk_configuration_resource_customization__configuration_resour1`; +/* Drop existing index */ +ALTER TABLE `catalogdb`.`configuration_customization` +DROP INDEX IF EXISTS `fk_configuration_customization__configuration_customization_idx` ; + +/* Create a new table */ +CREATE TABLE `tmp_configuration_customization` ( + `ID` INT(11) NOT NULL AUTO_INCREMENT, + `MODEL_CUSTOMIZATION_UUID` VARCHAR(200) NOT NULL, + `MODEL_INSTANCE_NAME` VARCHAR(200) NOT NULL, + `CONFIGURATION_TYPE` VARCHAR(200) DEFAULT NULL, + `CONFIGURATION_ROLE` VARCHAR(200) DEFAULT NULL, + `CONFIGURATION_FUNCTION` VARCHAR(200) DEFAULT NULL, + `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `CONFIGURATION_MODEL_UUID` VARCHAR(200) NOT NULL, + `SERVICE_PROXY_CUSTOMIZATION_MODEL_CUSTOMIZATION_UUID` VARCHAR(200) DEFAULT NULL, + `CONFIGURATION_CUSTOMIZATION_MODEL_CUSTOMIZATION_ID` int(11) DEFAULT NULL, + `SERVICE_MODEL_UUID` VARCHAR(200) NOT NULL, + PRIMARY KEY (`ID`) , + KEY `fk_configuration_customization__configuration_idx` (`CONFIGURATION_MODEL_UUID`), + KEY `fk_configuration_customization__service_idx` (`SERVICE_MODEL_UUID`), + UNIQUE KEY `uk_configuration_customization` (`MODEL_CUSTOMIZATION_UUID` ASC, `SERVICE_MODEL_UUID` ASC), + CONSTRAINT `fk_configuration_customization__configuration1` FOREIGN KEY (`CONFIGURATION_MODEL_UUID`) + REFERENCES `configuration` (`MODEL_UUID`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_configuration_customization__service1` FOREIGN KEY (`SERVICE_MODEL_UUID`) + REFERENCES `service` (`MODEL_UUID`) + ON DELETE CASCADE ON UPDATE CASCADE + +) ENGINE=INNODB DEFAULT CHARSET=LATIN1; + +/* Migrate the existing data */ +INSERT INTO tmp_configuration_customization +(`model_customization_uuid` , + `model_instance_name`, + `configuration_type` , + `configuration_role` , + `configuration_function` , + `creation_timestamp` , + `configuration_model_uuid` , + `service_proxy_customization_model_customization_uuid` , + `service_model_uuid`) +SELECT `config`.`model_customization_uuid`, + `config`.`model_instance_name`, + `config`.`configuration_type`, + `config`.`configuration_role`, + `config`.`configuration_function`, + `config`.`creation_timestamp`, + `config`.`configuration_model_uuid`, + `config`.`service_proxy_customization_model_customization_uuid`, + `svc`.`model_uuid` service_model_uuid FROM + configuration_customization config, + service svc, + configuration_customization_to_service config_svc +WHERE + config_svc.service_model_uuid = svc.model_uuid + AND config_svc.resource_model_customization_uuid = config.model_customization_uuid; + +/* Drop the old tables */ + +DROP TABLE `catalogdb`.`configuration_customization`; + +DROP TABLE `catalogdb`.`configuration_customization_to_service`; + +/* Rename the table */ +RENAME TABLE tmp_configuration_customization TO configuration_customization; + + \ No newline at end of file diff --git a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__WorkFlowDesignerTables.sql b/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__WorkFlowDesignerTables.sql deleted file mode 100644 index e44a6b97ba..0000000000 --- a/adapters/mso-catalog-db-adapter/src/main/resources/db/migration/V5.7__WorkFlowDesignerTables.sql +++ /dev/null @@ -1,175 +0,0 @@ -USE catalogdb; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`workflow` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `ARTIFACT_UUID` VARCHAR(200) NOT NULL, - `ARTIFACT_NAME` VARCHAR(200) NOT NULL, - `NAME` VARCHAR(200) NOT NULL, - `OPERATION_NAME` VARCHAR(200) NULL, - `VERSION` DOUBLE NOT NULL, - `DESCRIPTION` VARCHAR(1200) NULL, - `BODY` LONGTEXT NULL, - `RESOURCE_TARGET` VARCHAR(200) NOT NULL, - `SOURCE` VARCHAR(200) NOT NULL, - `TIMEOUT_MINUTES` INT(11) NULL DEFAULT NULL, - `ARTIFACT_CHECKSUM` VARCHAR(200) NULL DEFAULT 'MANUAL RECORD', - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_workflow` (`ARTIFACT_UUID` ASC, `NAME` ASC, `VERSION` ASC, `SOURCE` ASC)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`vnf_resource_to_workflow` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `VNF_RESOURCE_MODEL_UUID` VARCHAR(200) NOT NULL, - `WORKFLOW_ID` INT(11) NOT NULL, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_vnf_resource_to_workflow` (`VNF_RESOURCE_MODEL_UUID` ASC, `WORKFLOW_ID` ASC), - INDEX `fk_vnf_resource_to_workflow__workflow1_idx` (`WORKFLOW_ID` ASC), - INDEX `fk_vnf_resource_to_workflow__vnf_res_mod_uuid_idx` (`VNF_RESOURCE_MODEL_UUID` ASC), - CONSTRAINT `fk_vnf_resource_to_workflow__vnf_resource1` - FOREIGN KEY (`VNF_RESOURCE_MODEL_UUID`) - REFERENCES `catalogdb`.`vnf_resource` (`MODEL_UUID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_vnf_resource_to_workflow__workflow1` - FOREIGN KEY (`WORKFLOW_ID`) - REFERENCES `catalogdb`.`workflow` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `NAME` VARCHAR(200) NOT NULL, - `DESCRIPTION` VARCHAR(1200) NOT NULL, - `VERSION` DOUBLE NOT NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_activity_spec` (`NAME` ASC, `VERSION` ASC)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`user_parameters` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `NAME` VARCHAR(200) NOT NULL, - `PAYLOAD_LOCATION` VARCHAR(500) NULL, - `LABEL` VARCHAR(200) NOT NULL, - `TYPE` VARCHAR(200) NOT NULL, - `DESCRIPTION` VARCHAR(1200) NULL, - `IS_REQUIRED` TINYINT(1) NOT NULL, - `MAX_LENGTH` INT(11) NULL, - `ALLOWABLE_CHARS` VARCHAR(200) NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_user_parameters` (`NAME` ASC)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`workflow_activity_spec_sequence` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `WORKFLOW_ID` INT(11) NOT NULL, - `ACTIVITY_SPEC_ID` INT(11) NOT NULL, - `SEQ_NO` INT(11) NOT NULL, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_workflow_activity_spec_sequence` (`WORKFLOW_ID` ASC, `ACTIVITY_SPEC_ID` ASC, `SEQ_NO` ASC), - INDEX `fk_workflow_activity_spec_sequence__activity_spec_idx` (`ACTIVITY_SPEC_ID` ASC), - INDEX `fk_workflow_activity_spec_sequence__workflow_actifact_uuid_idx` (`WORKFLOW_ID` ASC), - CONSTRAINT `fk_workflow_activity_spec_sequence__activity_spec1` - FOREIGN KEY (`ACTIVITY_SPEC_ID`) - REFERENCES `catalogdb`.`activity_spec` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_workflow_activity_spec_sequence__workflow1` - FOREIGN KEY (`WORKFLOW_ID`) - REFERENCES `catalogdb`.`workflow` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_parameters` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `NAME` VARCHAR(200) NOT NULL, - `TYPE` VARCHAR(200) NOT NULL, - `DIRECTION` VARCHAR(200) NULL, - `DESCRIPTION` VARCHAR(1200) NULL, - `CREATION_TIMESTAMP` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_activity_spec_parameters` (`NAME` ASC, `DIRECTION` ASC)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_categories` ( - `ID` INT(11) NOT NULL, - `NAME` VARCHAR(200) NOT NULL, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_activity_spec_categories` (`NAME` ASC)) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_activity_spec_categories` ( - `ID` INT(11) NOT NULL, - `ACTIVITY_SPEC_ID` INT(11) NOT NULL, - `ACTIVITY_SPEC_CATEGORIES_ID` INT(11) NOT NULL, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_activity_spec_to_activity_spec_categories` (`ACTIVITY_SPEC_ID` ASC, `ACTIVITY_SPEC_CATEGORIES_ID` ASC), - INDEX `fk_activity_spec_to_activity_spec_categories__activity_spec_idx` (`ACTIVITY_SPEC_CATEGORIES_ID` ASC), - INDEX `fk_activity_spec_to_activity_spec_categories__activity_spec_idx1` (`ACTIVITY_SPEC_ID` ASC), - CONSTRAINT `fk_activity_spec_to_activity_spec_categories__activity_spec1` - FOREIGN KEY (`ACTIVITY_SPEC_ID`) - REFERENCES `catalogdb`.`activity_spec` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_activity_spec_to_activity_spec_categories__activity_spec_c1` - FOREIGN KEY (`ACTIVITY_SPEC_CATEGORIES_ID`) - REFERENCES `catalogdb`.`activity_spec_categories` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_activity_spec_parameters` ( - `ID` INT(11) NOT NULL AUTO_INCREMENT, - `ACTIVITY_SPEC_ID` INT(11) NOT NULL, - `ACTIVITY_SPEC_PARAMETERS_ID` INT(11) NOT NULL, - PRIMARY KEY (`ID`), - INDEX `fk_activity_spec_to_activity_spec_params__act_sp_param_id_idx` (`ACTIVITY_SPEC_PARAMETERS_ID` ASC), - UNIQUE INDEX `UK_activity_spec_to_activity_spec_parameters` (`ACTIVITY_SPEC_ID` ASC, `ACTIVITY_SPEC_PARAMETERS_ID` ASC), - INDEX `fk_activity_spec_to_activity_spec_parameters__act_spec_id_idx` (`ACTIVITY_SPEC_ID` ASC), - CONSTRAINT `fk_activity_spec_to_activity_spec_parameters__activity_spec_1` - FOREIGN KEY (`ACTIVITY_SPEC_ID`) - REFERENCES `catalogdb`.`activity_spec` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_activity_spec_to_activity_spec_parameters__activ_spec_param1` - FOREIGN KEY (`ACTIVITY_SPEC_PARAMETERS_ID`) - REFERENCES `catalogdb`.`activity_spec_parameters` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - -CREATE TABLE IF NOT EXISTS `catalogdb`.`activity_spec_to_user_parameters` ( - `ID` INT(11) NOT NULL, - `ACTIVITY_SPEC_ID` INT(11) NOT NULL, - `USER_PARAMETERS_ID` INT(11) NOT NULL, - PRIMARY KEY (`ID`), - UNIQUE INDEX `UK_activity_spec_to_user_parameters` (`ACTIVITY_SPEC_ID` ASC, `USER_PARAMETERS_ID` ASC), - INDEX `fk_activity_spec_to_user_parameters__user_parameters1_idx` (`USER_PARAMETERS_ID` ASC), - INDEX `fk_activity_spec_to_user_parameters__activity_spec1_idx` (`ACTIVITY_SPEC_ID` ASC), - CONSTRAINT `fk_activity_spec_to_user_parameters__activity_spec1` - FOREIGN KEY (`ACTIVITY_SPEC_ID`) - REFERENCES `catalogdb`.`activity_spec` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE, - CONSTRAINT `fk_activity_spec_to_user_parameters__user_parameters1` - FOREIGN KEY (`USER_PARAMETERS_ID`) - REFERENCES `catalogdb`.`user_parameters` (`ID`) - ON DELETE CASCADE - ON UPDATE CASCADE) -ENGINE = InnoDB -DEFAULT CHARACTER SET = latin1; - - -- cgit 1.2.3-korg