From 1d12614280fe82454053e2bf381023dcb0c6612c Mon Sep 17 00:00:00 2001 From: Carlos Konstanski Date: Wed, 22 Nov 2017 00:00:02 +0000 Subject: update create_databases_schama.sql Wed Nov 22 00:00:02 UTC 2017 --- .../database/files/create_openbook_schemas.sql | 46 +++++++++++++++++----- 1 file changed, 37 insertions(+), 9 deletions(-) (limited to 'salt/openbook/database/files') diff --git a/salt/openbook/database/files/create_openbook_schemas.sql b/salt/openbook/database/files/create_openbook_schemas.sql index 4f860b2..4a7295f 100644 --- a/salt/openbook/database/files/create_openbook_schemas.sql +++ b/salt/openbook/database/files/create_openbook_schemas.sql @@ -594,7 +594,8 @@ CREATE TABLE `t_metric_aggregate_customer_daily` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -647,7 +648,8 @@ CREATE TABLE `t_metric_aggregate_customer_hourly` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -722,7 +724,8 @@ CREATE TABLE `t_metric_aggregate_resource_daily` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -778,7 +781,8 @@ CREATE TABLE `t_metric_aggregate_resource_hourly` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -854,7 +858,8 @@ CREATE TABLE `t_metric_aggregate_tenant_daily` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -908,7 +913,8 @@ CREATE TABLE `t_metric_aggregate_tenant_hourly` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -982,7 +988,8 @@ CREATE TABLE `t_metric_values` ( PARTITION `p737047` VALUES LESS THAN (737047) ENGINE = InnoDB, PARTITION `p737048` VALUES LESS THAN (737048) ENGINE = InnoDB, PARTITION `p737049` VALUES LESS THAN (737049) ENGINE = InnoDB, - PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB); + PARTITION `p737050` VALUES LESS THAN (737050) ENGINE = InnoDB, + PARTITION `p737051` VALUES LESS THAN (737051) ENGINE = InnoDB); /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -1388,6 +1395,7 @@ CREATE TABLE `t_tenant` ( `customer_id` int(10) unsigned DEFAULT NULL, `service_manager_id` int(10) unsigned NOT NULL, `is_active` tinyint(4) NOT NULL DEFAULT 0, + `tenant_type_id` int(10) unsigned DEFAULT 1, KEY `ix_tenant_1` (`tenant_id`), KEY `ix_tenant_2` (`customer_id`), KEY `ix_tenant_3` (`service_manager_id`) @@ -1395,6 +1403,25 @@ CREATE TABLE `t_tenant` ( /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t_tenant_type` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(32) NOT NULL DEFAULT '', + `description` varchar(256) DEFAULT NULL, + KEY `ix_tenant_1` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t_tenant_type_properties` ( + `tenant_type_id` int(10) unsigned NOT NULL, + `property_group` varchar(128) NOT NULL, + `property_name` varchar(128) NOT NULL, + `property_value` text DEFAULT NULL, + PRIMARY KEY (`tenant_type_id`,`property_group`,`property_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; CREATE TABLE `t_unbilled_charges` ( `invoice_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `payer_id` int(10) unsigned NOT NULL, @@ -1878,7 +1905,8 @@ SET character_set_client = utf8; `is_active` tinyint NOT NULL, `customer_name` tinyint NOT NULL, `management_region_id` tinyint NOT NULL, - `service_manager_type` tinyint NOT NULL + `service_manager_type` tinyint NOT NULL, + `tenant_type` tinyint NOT NULL ) ENGINE=MyISAM */; SET character_set_client = @saved_cs_client; SET @saved_cs_client = @@character_set_client; @@ -2803,7 +2831,7 @@ USE `openbook`; /*!50001 SET collation_connection = utf8_general_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`openbook`@`%` SQL SECURITY DEFINER */ -/*!50001 VIEW `v_tenant` AS select `t`.`tenant_id` AS `tenant_id`,`t`.`tenant_name` AS `tenant_name`,`t`.`tenant_foreign_id` AS `tenant_foreign_id`,`t`.`create_date` AS `create_date`,`t`.`deactivate_date` AS `deactivate_date`,`t`.`customer_id` AS `customer_id`,`t`.`service_manager_id` AS `service_manager_id`,`t`.`is_active` AS `is_active`,`c`.`customer_name` AS `customer_name`,`c`.`management_region_id` AS `management_region_id`,`tsmt`.`service_manager_type` AS `service_manager_type` from (((`t_tenant` `t` left join `t_customer` `c` on(`t`.`customer_id` = `c`.`customer_id`)) left join `t_service_manager` `tsm` on(`t`.`service_manager_id` = `tsm`.`service_manager_id`)) left join `t_service_manager_type` `tsmt` on(`tsm`.`service_manager_type_id` = `tsmt`.`service_manager_type_id`)) */; +/*!50001 VIEW `v_tenant` AS select `t`.`tenant_id` AS `tenant_id`,`t`.`tenant_name` AS `tenant_name`,`t`.`tenant_foreign_id` AS `tenant_foreign_id`,`t`.`create_date` AS `create_date`,`t`.`deactivate_date` AS `deactivate_date`,`t`.`customer_id` AS `customer_id`,`t`.`service_manager_id` AS `service_manager_id`,`t`.`is_active` AS `is_active`,`c`.`customer_name` AS `customer_name`,`c`.`management_region_id` AS `management_region_id`,`tsmt`.`service_manager_type` AS `service_manager_type`,`tt`.`name` AS `tenant_type` from ((((`t_tenant` `t` left join `t_customer` `c` on(`t`.`customer_id` = `c`.`customer_id`)) left join `t_service_manager` `tsm` on(`t`.`service_manager_id` = `tsm`.`service_manager_id`)) left join `t_service_manager_type` `tsmt` on(`tsm`.`service_manager_type_id` = `tsmt`.`service_manager_type_id`)) left join `t_tenant_type` `tt` on(`t`.`tenant_type_id` = `tt`.`id`)) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- cgit v1.3