http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f1706e2c/setup/db/db/schema-304to305.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-304to305.sql b/setup/db/db/schema-304to305.sql new file mode 100755 index 0000000..b1b49fd --- /dev/null +++ b/setup/db/db/schema-304to305.sql @@ -0,0 +1,384 @@ +# Copyright 2012 Citrix Systems, Inc. Licensed under the +# Apache License, Version 2.0 (the "License"); you may not use this +# file except in compliance with the License. Citrix Systems, Inc. +# reserves all rights not expressly granted by 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. +# + +#Schema upgrade from 3.0.4 to 3.0.5; + +CREATE TABLE `cloud`.`resource_tags` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40), + `key` varchar(255), + `value` varchar(255), + `resource_id` bigint unsigned NOT NULL, + `resource_uuid` varchar(40), + `resource_type` varchar(255), + `customer` varchar(255), + `domain_id` bigint unsigned NOT NULL COMMENT 'foreign key to domain id', + `account_id` bigint unsigned NOT NULL COMMENT 'owner of this network', + PRIMARY KEY (`id`), + CONSTRAINT `fk_tags__account_id` FOREIGN KEY(`account_id`) REFERENCES `account`(`id`), + CONSTRAINT `fk_tags__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain`(`id`), + UNIQUE `i_tags__resource_id__resource_type__key`(`resource_id`, `resource_type`, `key`), + CONSTRAINT `uc_resource_tags__uuid` UNIQUE (`uuid`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`vpc_offerings` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40) NOT NULL, + `unique_name` varchar(64) UNIQUE COMMENT 'unique name of the vpc offering', + `name` varchar(255) COMMENT 'vpc name', + `display_text` varchar(255) COMMENT 'display text', + `state` char(32) COMMENT 'state of the vpc offering that has Disabled value by default', + `default` int(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 if vpc offering is default', + `removed` datetime COMMENT 'date removed if not null', + `created` datetime NOT NULL COMMENT 'date created', + `service_offering_id` bigint unsigned COMMENT 'service offering id that virtual router is tied to', + PRIMARY KEY (`id`), + INDEX `i_vpc__removed`(`removed`), + CONSTRAINT `fk_vpc_offerings__service_offering_id` FOREIGN KEY `fk_vpc_offerings__service_offering_id` (`service_offering_id`) REFERENCES `service_offering`(`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`vpc_offering_service_map` ( + `id` bigint unsigned NOT NULL auto_increment, + `vpc_offering_id` bigint unsigned NOT NULL COMMENT 'vpc_offering_id', + `service` varchar(255) NOT NULL COMMENT 'service', + `provider` varchar(255) COMMENT 'service provider', + `created` datetime COMMENT 'date created', + PRIMARY KEY (`id`), + CONSTRAINT `fk_vpc_offering_service_map__vpc_offering_id` FOREIGN KEY(`vpc_offering_id`) REFERENCES `vpc_offerings`(`id`) ON DELETE CASCADE, + UNIQUE (`vpc_offering_id`, `service`, `provider`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`vpc` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40) NOT NULL, + `name` varchar(255) COMMENT 'vpc name', + `display_text` varchar(255) COMMENT 'vpc display text', + `cidr` varchar(18) COMMENT 'vpc cidr', + `vpc_offering_id` bigint unsigned NOT NULL COMMENT 'vpc offering id that this vpc is created from', + `zone_id` bigint unsigned NOT NULL COMMENT 'the id of the zone this Vpc belongs to', + `state` varchar(32) NOT NULL COMMENT 'state of the VP (can be Enabled and Disabled)', + `domain_id` bigint unsigned NOT NULL COMMENT 'domain the vpc belongs to', + `account_id` bigint unsigned NOT NULL COMMENT 'owner of this vpc', + `network_domain` varchar(255) COMMENT 'network domain', + `removed` datetime COMMENT 'date removed if not null', + `created` datetime NOT NULL COMMENT 'date created', + `restart_required` int(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 if restart is required for the VPC', + PRIMARY KEY (`id`), + INDEX `i_vpc__removed`(`removed`), + CONSTRAINT `fk_vpc__zone_id` FOREIGN KEY `fk_vpc__zone_id` (`zone_id`) REFERENCES `data_center` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_vpc__vpc_offering_id` FOREIGN KEY (`vpc_offering_id`) REFERENCES `vpc_offerings`(`id`), + CONSTRAINT `fk_vpc__account_id` FOREIGN KEY `fk_vpc__account_id` (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_vpc__domain_id` FOREIGN KEY `fk_vpc__domain_id` (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +CREATE TABLE `cloud`.`router_network_ref` ( + `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', + `router_id` bigint unsigned NOT NULL COMMENT 'router id', + `network_id` bigint unsigned NOT NULL COMMENT 'network id', + `guest_type` char(32) COMMENT 'type of guest network that can be shared or isolated', + PRIMARY KEY (`id`), + CONSTRAINT `fk_router_network_ref__networks_id` FOREIGN KEY (`network_id`) REFERENCES `networks`(`id`) ON DELETE CASCADE, + UNIQUE `i_router_network_ref__router_id__network_id`(`router_id`, `network_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +CREATE TABLE `cloud`.`vpc_gateways` ( + `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id', + `uuid` varchar(40), + `ip4_address` char(40) COMMENT 'ip4 address of the gateway', + `netmask` varchar(15) COMMENT 'netmask of the gateway', + `gateway` varchar(15) COMMENT 'gateway', + `vlan_tag` varchar(255), + `type` varchar(32) COMMENT 'type of gateway; can be Public/Private/Vpn', + `network_id` bigint unsigned NOT NULL COMMENT 'network id vpc gateway belongs to', + `vpc_id` bigint unsigned NOT NULL COMMENT 'id of the vpc the gateway belongs to', + `zone_id` bigint unsigned NOT NULL COMMENT 'id of the zone the gateway belongs to', + `created` datetime COMMENT 'date created', + `account_id` bigint unsigned NOT NULL COMMENT 'owner id', + `domain_id` bigint unsigned NOT NULL COMMENT 'domain id', + `state` varchar(32) NOT NULL COMMENT 'what state the vpc gateway in', + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_vpc_gateways__network_id` FOREIGN KEY `fk_vpc_gateways__network_id`(`network_id`) REFERENCES `networks`(`id`), + CONSTRAINT `fk_vpc_gateways__vpc_id` FOREIGN KEY `fk_vpc_gateways__vpc_id`(`vpc_id`) REFERENCES `vpc`(`id`), + CONSTRAINT `fk_vpc_gateways__zone_id` FOREIGN KEY `fk_vpc_gateways__zone_id`(`zone_id`) REFERENCES `data_center`(`id`), + CONSTRAINT `fk_vpc_gateways__account_id` FOREIGN KEY(`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_vpc_gateways__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_vpc_gateways__uuid` UNIQUE (`uuid`), + INDEX `i_vpc_gateways__removed`(`removed`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`private_ip_address` ( + `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', + `ip_address` char(40) NOT NULL COMMENT 'ip address', + `network_id` bigint unsigned NOT NULL COMMENT 'id of the network ip belongs to', + `reservation_id` char(40) COMMENT 'reservation id', + `mac_address` varchar(17) COMMENT 'mac address', + `vpc_id` bigint unsigned COMMENT 'vpc this ip belongs to', + `taken` datetime COMMENT 'Date taken', + PRIMARY KEY (`id`), + CONSTRAINT `fk_private_ip_address__vpc_id` FOREIGN KEY `fk_private_ip_address__vpc_id`(`vpc_id`) REFERENCES `vpc`(`id`), + CONSTRAINT `fk_private_ip_address__network_id` FOREIGN KEY (`network_id`) REFERENCES `networks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +CREATE TABLE `cloud`.`static_routes` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40), + `vpc_gateway_id` bigint unsigned COMMENT 'id of the corresponding ip address', + `cidr` varchar(18) COMMENT 'cidr for the static route', + `state` char(32) NOT NULL COMMENT 'current state of this rule', + `vpc_id` bigint unsigned COMMENT 'vpc the firewall rule is associated with', + `account_id` bigint unsigned NOT NULL COMMENT 'owner id', + `domain_id` bigint unsigned NOT NULL COMMENT 'domain id', + `created` datetime COMMENT 'Date created', + PRIMARY KEY (`id`), + CONSTRAINT `fk_static_routes__vpc_gateway_id` FOREIGN KEY(`vpc_gateway_id`) REFERENCES `vpc_gateways`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_static_routes__vpc_id` FOREIGN KEY (`vpc_id`) REFERENCES `vpc`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_static_routes__account_id` FOREIGN KEY(`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_static_routes__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_static_routes__uuid` UNIQUE (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +ALTER TABLE `cloud`.`networks` ADD COLUMN `vpc_id` bigint unsigned COMMENT 'vpc this network belongs to'; +ALTER TABLE `cloud`.`networks`ADD CONSTRAINT `fk_networks__vpc_id` FOREIGN KEY(`vpc_id`) REFERENCES `vpc`(`id`); + +ALTER TABLE `cloud`.`firewall_rules` ADD COLUMN `vpc_id` bigint unsigned COMMENT 'vpc the firewall rule is associated with'; +ALTER TABLE `cloud`.`firewall_rules` ADD COLUMN `traffic_type` char(32) COMMENT 'the type of the rule, can be Ingress or Egress'; +ALTER TABLE `cloud`.`firewall_rules` MODIFY `ip_address_id` bigint unsigned COMMENT 'id of the corresponding ip address'; +ALTER TABLE `cloud`.`firewall_rules` ADD CONSTRAINT `fk_firewall_rules__vpc_id` FOREIGN KEY (`vpc_id`) REFERENCES `vpc`(`id`) ON DELETE CASCADE; + + +ALTER TABLE `cloud`.`user_ip_address` ADD COLUMN `vpc_id` bigint unsigned COMMENT 'vpc the ip address is associated with'; +ALTER TABLE `cloud`.`user_ip_address` ADD CONSTRAINT `fk_user_ip_address__vpc_id` FOREIGN KEY (`vpc_id`) REFERENCES `vpc`(`id`) ON DELETE CASCADE; + +ALTER TABLE `cloud`.`domain_router` ADD COLUMN `vpc_id` bigint unsigned COMMENT 'correlated virtual router vpc ID'; +ALTER TABLE `cloud`.`domain_router` ADD CONSTRAINT `fk_domain_router__vpc_id` FOREIGN KEY `fk_domain_router__vpc_id`(`vpc_id`) REFERENCES `vpc`(`id`); + + +ALTER TABLE `cloud`.`physical_network_service_providers` ADD COLUMN `networkacl_service_provided` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is Network ACL service provided'; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'vpc.cleanup.interval', '3600', 'The interval (in seconds) between cleanup for Inactive VPCs'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'vpc.max.networks', '3', 'Maximum number of networks per vpc'); + + +CREATE TABLE `cloud`.`counter` ( + `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id', + `uuid` varchar(40), + `source` varchar(255) NOT NULL COMMENT 'source e.g. netscaler, snmp', + `name` varchar(255) NOT NULL COMMENT 'Counter name', + `value` varchar(255) NOT NULL COMMENT 'Value in case of source=snmp', + `removed` datetime COMMENT 'date removed if not null', + `created` datetime NOT NULL COMMENT 'date created', + PRIMARY KEY (`id`), + CONSTRAINT `uc_counter__uuid` UNIQUE (`uuid`), + INDEX `i_counter__removed`(`removed`), + INDEX `i_counter__source`(`source`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`conditions` ( + `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id', + `uuid` varchar(40), + `counter_id` bigint unsigned NOT NULL COMMENT 'Counter Id', + `threshold` bigint unsigned NOT NULL COMMENT 'threshold value for the given counter', + `relational_operator` char(2) COMMENT 'relational operator to be used upon the counter and condition', + `domain_id` bigint unsigned NOT NULL COMMENT 'domain the Condition belongs to', + `account_id` bigint unsigned NOT NULL COMMENT 'owner of this Condition', + `removed` datetime COMMENT 'date removed if not null', + `created` datetime NOT NULL COMMENT 'date created', + PRIMARY KEY (`id`), + CONSTRAINT `fk_conditions__counter_id` FOREIGN KEY `fk_condition__counter_id`(`counter_id`) REFERENCES `counter`(`id`), + CONSTRAINT `fk_conditions__account_id` FOREIGN KEY `fk_condition__account_id` (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_conditions__domain_id` FOREIGN KEY `fk_condition__domain_id` (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_conditions__uuid` UNIQUE (`uuid`), + INDEX `i_conditions__removed`(`removed`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`autoscale_vmprofiles` ( + `id` bigint unsigned NOT NULL auto_increment, + `uuid` varchar(40), + `zone_id` bigint unsigned NOT NULL, + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `autoscale_user_id` bigint unsigned NOT NULL, + `service_offering_id` bigint unsigned NOT NULL, + `template_id` bigint unsigned NOT NULL, + `other_deploy_params` varchar(1024) COMMENT 'other deployment parameters that is in addition to zoneid,serviceofferingid,domainid', + `destroy_vm_grace_period` int unsigned COMMENT 'the time allowed for existing connections to get closed before a vm is destroyed', + `counter_params` varchar(1024) COMMENT 'the parameters for the counter to be used to get metric information from VMs', + `created` datetime NOT NULL COMMENT 'date created', + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_autoscale_vmprofiles__domain_id` FOREIGN KEY `fk_autoscale_vmprofiles__domain_id` (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmprofiles__account_id` FOREIGN KEY `fk_autoscale_vmprofiles__account_id` (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmprofiles__autoscale_user_id` FOREIGN KEY `fk_autoscale_vmprofiles__autoscale_user_id` (`autoscale_user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_autoscale_vmprofiles__uuid` UNIQUE (`uuid`), + INDEX `i_autoscale_vmprofiles__removed`(`removed`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`autoscale_policies` ( + `id` bigint unsigned NOT NULL auto_increment, + `uuid` varchar(40), + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `duration` int unsigned NOT NULL, + `quiet_time` int unsigned NOT NULL, + `action` varchar(15), + `created` datetime NOT NULL COMMENT 'date created', + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_autoscale_policies__domain_id` FOREIGN KEY `fk_autoscale_policies__domain_id` (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_policies__account_id` FOREIGN KEY `fk_autoscale_policies__account_id` (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_autoscale_policies__uuid` UNIQUE (`uuid`), + INDEX `i_autoscale_policies__removed`(`removed`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`autoscale_vmgroups` ( + `id` bigint unsigned NOT NULL auto_increment, + `uuid` varchar(40), + `zone_id` bigint unsigned NOT NULL, + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `load_balancer_id` bigint unsigned NOT NULL, + `min_members` int unsigned DEFAULT 1, + `max_members` int unsigned NOT NULL, + `member_port` int unsigned NOT NULL, + `interval` int unsigned NOT NULL, + `profile_id` bigint unsigned NOT NULL, + `state` varchar(255) NOT NULL COMMENT 'enabled or disabled, a vmgroup is disabled to stop autoscaling activity', + `created` datetime NOT NULL COMMENT 'date created', + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_autoscale_vmgroup__autoscale_vmprofile_id` FOREIGN KEY(`profile_id`) REFERENCES `autoscale_vmprofiles`(`id`), + CONSTRAINT `fk_autoscale_vmgroup__load_balancer_id` FOREIGN KEY(`load_balancer_id`) REFERENCES `load_balancing_rules`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmgroups__domain_id` FOREIGN KEY `fk_autoscale_vmgroups__domain_id` (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmgroups__account_id` FOREIGN KEY `fk_autoscale_vmgroups__account_id` (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmgroups__zone_id` FOREIGN KEY `fk_autoscale_vmgroups__zone_id`(`zone_id`) REFERENCES `data_center`(`id`), + CONSTRAINT `uc_autoscale_vmgroups__uuid` UNIQUE (`uuid`), + INDEX `i_autoscale_vmgroups__removed`(`removed`), + INDEX `i_autoscale_vmgroups__load_balancer_id`(`load_balancer_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`autoscale_policy_condition_map` ( + `id` bigint unsigned NOT NULL auto_increment, + `policy_id` bigint unsigned NOT NULL, + `condition_id` bigint unsigned NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT `fk_autoscale_policy_condition_map__policy_id` FOREIGN KEY `fk_autoscale_policy_condition_map__policy_id` (`policy_id`) REFERENCES `autoscale_policies` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_policy_condition_map__condition_id` FOREIGN KEY `fk_autoscale_policy_condition_map__condition_id` (`condition_id`) REFERENCES `conditions` (`id`), + INDEX `i_autoscale_policy_condition_map__policy_id`(`policy_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`autoscale_vmgroup_policy_map` ( + `id` bigint unsigned NOT NULL auto_increment, + `vmgroup_id` bigint unsigned NOT NULL, + `policy_id` bigint unsigned NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT `fk_autoscale_vmgroup_policy_map__vmgroup_id` FOREIGN KEY `fk_autoscale_vmgroup_policy_map__vmgroup_id` (`vmgroup_id`) REFERENCES `autoscale_vmgroups` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_autoscale_vmgroup_policy_map__policy_id` FOREIGN KEY `fk_autoscale_vmgroup_policy_map__policy_id` (`policy_id`) REFERENCES `autoscale_policies` (`id`), + INDEX `i_autoscale_vmgroup_policy_map__vmgroup_id`(`vmgroup_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `cloud`.`counter` (id, source, name, value,created) VALUES (1,'snmp','Linux User CPU - percentage', '1.3.6.1.4.1.2021.11.9.0', now()); +INSERT INTO `cloud`.`counter` (id, source, name, value,created) VALUES (2,'snmp','Linux System CPU - percentage', '1.3.6.1.4.1.2021.11.10.0', now()); +INSERT INTO `cloud`.`counter` (id, source, name, value,created) VALUES (3,'snmp','Linux CPU Idle - percentage', '1.3.6.1.4.1.2021.11.11.0', now()); +INSERT INTO `cloud`.`counter` (id, source, name, value,created) VALUES (100,'netscaler','Response Time - microseconds', 'RESPTIME', now()); + +CREATE TABLE `cloud`.`s2s_vpn_gateway` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40), + `addr_id` bigint unsigned NOT NULL, + `vpc_id` bigint unsigned NOT NULL, + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_s2s_vpn_gateway__addr_id` FOREIGN KEY (`addr_id`) REFERENCES `user_ip_address` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_gateway__vpc_id` FOREIGN KEY (`vpc_id`) REFERENCES `vpc` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_gateway__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_gateway__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_s2s_vpn_gateway__uuid` UNIQUE (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`s2s_customer_gateway` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40), + `name` varchar(255) NOT NULL, + `gateway_ip` char(40) NOT NULL, + `guest_cidr_list` varchar(200) NOT NULL, + `ipsec_psk` varchar(256), + `ike_policy` varchar(30) NOT NULL, + `esp_policy` varchar(30) NOT NULL, + `ike_lifetime` int NOT NULL DEFAULT 86400, + `esp_lifetime` int NOT NULL DEFAULT 3600, + `dpd` int(1) NOT NULL DEFAULT 0, + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_s2s_customer_gateway__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_customer_gateway__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_s2s_customer_gateway__uuid` UNIQUE (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`s2s_vpn_connection` ( + `id` bigint unsigned NOT NULL auto_increment COMMENT 'id', + `uuid` varchar(40), + `vpn_gateway_id` bigint unsigned NULL, + `customer_gateway_id` bigint unsigned NULL, + `state` varchar(32) NOT NULL, + `domain_id` bigint unsigned NOT NULL, + `account_id` bigint unsigned NOT NULL, + `created` datetime NOT NULL COMMENT 'date created', + `removed` datetime COMMENT 'date removed if not null', + PRIMARY KEY (`id`), + CONSTRAINT `fk_s2s_vpn_connection__vpn_gateway_id` FOREIGN KEY (`vpn_gateway_id`) REFERENCES `s2s_vpn_gateway` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_connection__customer_gateway_id` FOREIGN KEY (`customer_gateway_id`) REFERENCES `s2s_customer_gateway` (`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_connection__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE, + CONSTRAINT `fk_s2s_vpn_connection__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE, + CONSTRAINT `uc_s2s_vpn_connection__uuid` UNIQUE (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +ALTER TABLE `cloud`.`data_center` ADD COLUMN `is_local_storage_enabled` tinyint NOT NULL DEFAULT 0 COMMENT 'Is local storage offering enabled for this data center; 1: enabled, 0: not'; +UPDATE `cloud`.`data_center` SET `is_local_storage_enabled` = IF ((SELECT `value` FROM `cloud`.`configuration` WHERE `name`='use.local.storage')='true', 1, 0) WHERE `removed` IS NULL; +DELETE FROM `cloud`.`configuration` where name='use.local.storage'; + +ALTER TABLE `cloud`.`hypervisor_capabilities` ADD COLUMN `max_data_volumes_limit` int unsigned DEFAULT 6 COMMENT 'Max. data volumes per VM supported by hypervisor'; + +UPDATE `cloud`.`hypervisor_capabilities` SET `max_data_volumes_limit`=13 WHERE `hypervisor_type`='XenServer' AND (`hypervisor_version`='6.0' OR `hypervisor_version`='6.0.2'); + +UPDATE `cloud`.`configuration` SET description='In second, timeout for creating volume from snapshot' WHERE name='create.volume.from.snapshot.wait'; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Account Defaults', 'DEFAULT', 'management-server', 'max.account.vpcs', '20', 'The default maximum number of vpcs that can be created for an account'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Project Defaults', 'DEFAULT', 'management-server', 'max.project.vpcs', '20', 'The default maximum number of vpcs that can be created for a project'); + +UPDATE `cloud`.`configuration` SET category='Network' WHERE name='guest.domain.suffix'; +UPDATE `cloud`.`configuration` SET component='management-server' WHERE name='agent.lb.enabled'; +UPDATE `cloud`.`configuration` SET component='StorageManager' WHERE name='backup.snapshot.wait'; +UPDATE `cloud`.`configuration` SET component='StorageManager' WHERE name='copy.volume.wait'; +UPDATE `cloud`.`configuration` SET component='StorageManager' WHERE name='create.volume.from.snapshot.wait'; +UPDATE `cloud`.`configuration` SET component='TemplateManager' WHERE name='primary.storage.download.wait'; +UPDATE `cloud`.`configuration` SET component='StorageManager' WHERE name='storage.cleanup.enabled'; +UPDATE `cloud`.`configuration` SET component='StorageManager' WHERE name='storage.cleanup.interval'; +UPDATE `cloud`.`configuration` SET description='Comma separated list of cidrs internal to the datacenter that can host template download servers, please note 0.0.0.0 is not a valid site ' WHERE name='secstorage.allowed.internal.sites'; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Network', 'DEFAULT', 'management-server', 'site2site.vpn.vpngateway.connection.limit', '4', 'The maximum number of VPN connection per VPN gateway'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Network', 'DEFAULT', 'management-server', 'site2site.vpn.customergateway.subnets.limit', '10', 'The maximum number of subnets per customer gateway'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Usage', 'DEFAULT', 'management-server', 'traffic.sentinel.include.zones', 'EXTERNAL', 'Traffic going into specified list of zones is metered. For metering all traffic leave this parameter empty'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Usage', 'DEFAULT', 'management-server', 'traffic.sentinel.exclude.zones', '', 'Traffic going into specified list of zones is not metered'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'ha.workers', '5', 'Number of ha worker threads'); + +DROP TABLE IF EXISTS `cloud`.`ovs_tunnel_account`; +UPDATE `cloud`.`snapshots` set swift_id=null where swift_id=0; +DELETE FROM `cloud`.`host_details` where name in ('storage.network.device1', 'storage.network.device2');
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f1706e2c/setup/db/db/schema-305to306-cleanup.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-305to306-cleanup.sql b/setup/db/db/schema-305to306-cleanup.sql new file mode 100644 index 0000000..ff70c7c --- /dev/null +++ b/setup/db/db/schema-305to306-cleanup.sql @@ -0,0 +1,21 @@ +# Copyright 2012 Citrix Systems, Inc. Licensed under the +# Apache License, Version 2.0 (the "License"); you may not use this +# file except in compliance with the License. Citrix Systems, Inc. +# reserves all rights not expressly granted by 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. +# + + +#Schema cleanup from 3.0.5 to 3.0.6; + + +DELETE FROM `cloud`.`configuration` where `cloud`.`configuration`.`name`="vm.hostname.flag"; +DELETE FROM `cloud`.`storage_pool_host_ref` WHERE `cloud`.`storage_pool_host_ref`.`pool_id` IN (SELECT `cloud`.`storage_pool`.`id` FROM `cloud`.`storage_pool` WHERE `cloud`.`storage_pool`.`removed` IS NOT NULL); + +ALTER TABLE `cloud`.`sync_queue` DROP COLUMN queue_proc_msid; +ALTER TABLE `cloud`.`sync_queue` DROP COLUMN queue_proc_time; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f1706e2c/setup/db/db/schema-305to306.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-305to306.sql b/setup/db/db/schema-305to306.sql new file mode 100755 index 0000000..9609281 --- /dev/null +++ b/setup/db/db/schema-305to306.sql @@ -0,0 +1,91 @@ +# Copyright 2012 Citrix Systems, Inc. Licensed under the +# Apache License, Version 2.0 (the "License"); you may not use this +# file except in compliance with the License. Citrix Systems, Inc. +# reserves all rights not expressly granted by 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. +# + +#Schema upgrade from 3.0.5 to 3.0.6; + +ALTER TABLE `cloud`.`network_offerings` ADD COLUMN `eip_associate_public_ip` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if public IP is associated with user VM creation by default when EIP service is enabled.' AFTER `elastic_ip_service`; +INSERT INTO `cloud`.`guest_os_hypervisor` (hypervisor_type, guest_os_name, guest_os_id) VALUES ('VmWare', 'Red Hat Enterprise Linux 6.0(32-bit)', 136); +INSERT INTO `cloud`.`guest_os_hypervisor` (hypervisor_type, guest_os_name, guest_os_id) VALUES ('VmWare', 'Red Hat Enterprise Linux 6.0(64-bit)', 137); + +UPDATE `cloud`.`user` SET PASSWORD=RAND() WHERE id=1; + +ALTER TABLE `cloud`.`sync_queue` ADD COLUMN `queue_size` smallint DEFAULT 0 COMMENT 'number of items being processed by the queue'; +ALTER TABLE `cloud`.`sync_queue` ADD COLUMN `queue_size_limit` smallint DEFAULT 1 COMMENT 'max number of items the queue can process concurrently'; +ALTER TABLE `cloud`.`sync_queue_item` ADD COLUMN `queue_proc_time` datetime COMMENT 'when processing started for the item'; +ALTER TABLE `cloud`.`sync_queue_item` ADD KEY `i_sync_queue__queue_proc_time`(`queue_proc_time`); + +ALTER TABLE `cloud`.`usage_event` ADD COLUMN `virtual_size` bigint unsigned; +ALTER TABLE `cloud_usage`.`usage_event` ADD COLUMN `virtual_size` bigint unsigned; +ALTER TABLE `cloud_usage`.`usage_storage` ADD COLUMN `virtual_size` bigint unsigned; +ALTER TABLE `cloud_usage`.`cloud_usage` ADD COLUMN `virtual_size` bigint unsigned; +ALTER TABLE `cloud`.`volumes` ADD COLUMN `iso_id` bigint unsigned COMMENT 'Records the iso id from which the vm is created' AFTER `template_id` ; + +ALTER TABLE `cloud`.`external_load_balancer_devices` DROP COLUMN `is_inline`; +ALTER TABLE `cloud`.`network_offerings` ADD COLUMN `inline` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is this network offering LB provider is in inline mode'; + +ALTER TABLE `cloud`.`inline_load_balancer_nic_map` DROP FOREIGN KEY fk_inline_load_balancer_nic_map__load_balancer_id; +ALTER TABLE `cloud`.`inline_load_balancer_nic_map` DROP COLUMN load_balancer_id; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'concurrent.snapshots.threshold.perhost', null, 'Limits number of snapshots that can be handled by the host concurrently; default is NULL - unlimited'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'xen.update.url', 'http://updates.xensource.com/XenServer/updates.xml', 'URL to get the latest XenServer updates'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'update.check.interval', '10080', 'Interval to check XenServer updates(in minutes)'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'host.updates.enable', 'false', 'Enable/Disable Host updates checker'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Network','DEFAULT','NetworkManager','network.dhcp.nondefaultnetwork.setgateway.guestos','Windows','The guest OS\'s name start with this fields would result in DHCP server response gateway information even when the network it\'s on is not default network. Names are separated by comma.'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'NetworkManager', 'router.check.poolsize' , '10', 'Numbers of threads using to check redundant router status.'); + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'timeout.baremetal.securitygroup.agent.echo' , '3600', 'Timeout to echo baremetal security group agent, in seconds, the provisioning process will be treated as a failure'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'interval.baremetal.securitygroup.agent.echo' , '10', 'Interval to echo baremetal security group agent, in seconds'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'enable.baremetal.securitygroup.agent.echo' , 'false', 'After starting provision process, periodcially echo security agent installed in the template. Treat provisioning as success only if echo successfully'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'recreate.systemvm.enabled' , 'false', 'If true, will recreate system vm root disk whenever starting system vm'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'vm.instancename.flag' , 'false', 'If true, will append guest VMs display Name (if set) to its internal name and set hostname and display name to the conjoined value'); +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'management-server', 'vmware.additional.vnc.portrange.size' , '1000', 'Start port number of additional VNC port range'); + +INSERT IGNORE INTO `cloud`.`hypervisor_capabilities`(hypervisor_type, hypervisor_version, max_guests_limit, security_group_enabled, max_data_volumes_limit) VALUES ('XenServer', '6.1.0', 50, 1, 13); +INSERT IGNORE INTO `cloud`.`hypervisor_capabilities`(hypervisor_type, hypervisor_version, max_guests_limit, security_group_enabled) VALUES ('VMware', '5.1', 128, 0); + +INSERT INTO `cloud`.`guest_os` (id, category_id, name, uuid, display_name) VALUES (206, 6, NULL, '8ceb2da9-62cd-53d4-ac8a-d0563d9bec2d', 'Windows 8(64-bit)'); + + +CREATE TABLE `cloud`.`host_updates` ( + `id` bigint unsigned NOT NULL auto_increment, + `uuid` varchar(40), + `label` varchar(40), + `description` varchar(999), + `after_apply_guidance` varchar(40), + `url` varchar(999), + `timestamp` varchar(80), + PRIMARY KEY (`id`), + CONSTRAINT `uc_host_updates__uuid` UNIQUE (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cloud`.`host_updates_ref` ( + `id` bigint unsigned NOT NULL auto_increment, + `host_id` bigint unsigned NOT NULL, + `patch_id` bigint unsigned NOT NULL, + `update_applied` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`id`), + CONSTRAINT `uc_host_updates__host_patch_id` UNIQUE (`host_id`, `patch_id`), + CONSTRAINT `fk_host_updates__host_id` FOREIGN KEY (`host_id`) REFERENCES `host`(`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Advanced', 'DEFAULT', 'AgentManager', 'xen.nics.max', '7', 'Maximum allowed nics for Vms created on Xen'); + +UPDATE `cloud`.`networks` set name='Shared SG enabled network', display_text='Shared SG enabled network' WHERE name IS null AND traffic_type='Guest' AND data_center_id IN (select id from data_center where networktype='Advanced' and is_security_group_enabled=1) AND acl_type='Domain'; + +# patch UUID colomn with ID for volumes and snapshot_policy tables +UPDATE `cloud`.`volumes` set uuid=id WHERE uuid is NULL; +UPDATE `cloud`.`snapshot_policy` set uuid=id WHERE uuid is NULL; http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f1706e2c/setup/db/db/schema-306to307.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-306to307.sql b/setup/db/db/schema-306to307.sql new file mode 100644 index 0000000..b0fa137 --- /dev/null +++ b/setup/db/db/schema-306to307.sql @@ -0,0 +1,17 @@ +# Copyright 2012 Citrix Systems, Inc. Licensed under the +# Apache License, Version 2.0 (the "License"); you may not use this +# file except in compliance with the License. Citrix Systems, Inc. +# reserves all rights not expressly granted by 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. +# + +#Schema upgrade from 3.0.6 to 3.0.7; + +INSERT IGNORE INTO `cloud`.`configuration` VALUES ('Network', 'DEFAULT', 'management-server', 'network.loadbalancer.haproxy.max.conn', '4096', 'Load Balancer(haproxy) maximum number of concurrent connections(global max)'); + +ALTER TABLE `cloud`.`network_offerings` ADD COLUMN `concurrent_connections` int(10) unsigned COMMENT 'concurrent connections supported on this network'; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f1706e2c/setup/db/db/schema-307to410-cleanup.sql ---------------------------------------------------------------------- diff --git a/setup/db/db/schema-307to410-cleanup.sql b/setup/db/db/schema-307to410-cleanup.sql new file mode 100644 index 0000000..984a683 --- /dev/null +++ b/setup/db/db/schema-307to410-cleanup.sql @@ -0,0 +1,30 @@ +-- 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. + +--; +-- Schema cleanup from 3.0.7 to 4.1.0 +--; + +# Drop the field introduced as a part of 410-420 upgrade and re-enable it back +ALTER TABLE `cloud`.`network_offerings` DROP COLUMN `eip_associate_public_ip`; +ALTER TABLE `cloud`.`network_offerings` CHANGE COLUMN `eip_associate_public_ip_1` `eip_associate_public_ip` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if public IP is associated with user VM creation by default when EIP service is enabled.' AFTER `elastic_ip_service`; + + + + + +
