-- 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.

SET foreign_key_checks = 0;

use cloud;

DROP VIEW IF EXISTS `cloud`.`port_forwarding_rules_view`;

DROP TABLE IF EXISTS `cloud`.`configuration`;

DROP TABLE IF EXISTS `cloud`.`ip_forwarding`;

DROP TABLE IF EXISTS `cloud`.`management_agent`;

DROP TABLE IF EXISTS `cloud`.`host`;

DROP TABLE IF EXISTS `cloud`.`mshost`;

DROP TABLE IF EXISTS `cloud`.`service_offering`;

DROP TABLE IF EXISTS `cloud`.`user`;

DROP TABLE IF EXISTS `cloud`.`user_ip_address`;

DROP TABLE IF EXISTS `cloud`.`user_statistics`;

DROP TABLE IF EXISTS `cloud`.`vm_template`;

DROP TABLE IF EXISTS `cloud`.`vm_instance`;

DROP TABLE IF EXISTS `cloud`.`domain_router`;

DROP TABLE IF EXISTS `cloud`.`event`;

DROP TABLE IF EXISTS `cloud`.`host_details`;

DROP TABLE IF EXISTS `cloud`.`host_pod_ref`;

DROP TABLE IF EXISTS `cloud`.`host_zone_ref`;

DROP TABLE IF EXISTS `cloud`.`data_ceneter`;

DROP TABLE IF EXISTS `cloud`.`volumes`;

DROP TABLE IF EXISTS `cloud`.`storage`;

DROP TABLE IF EXISTS `cloud`.`data_center`;

DROP TABLE IF EXISTS `cloud`.`pricing`;

DROP TABLE IF EXISTS `cloud`.`sequence`;

DROP TABLE IF EXISTS `cloud`.`user_vm`;

DROP TABLE IF EXISTS `cloud`.`template_host_ref`;

DROP TABLE IF EXISTS `cloud`.`volume_host_ref`;

DROP TABLE IF EXISTS `cloud`.`upload`;

DROP TABLE IF EXISTS `cloud`.`template_zone_ref`;

DROP TABLE IF EXISTS `cloud`.`dc_vnet_alloc`;

DROP TABLE IF EXISTS `cloud`.`dc_ip_address_alloc`;

DROP TABLE IF EXISTS `cloud`.`vlan`;

DROP TABLE IF EXISTS `cloud`.`host_vlan_map`;

DROP TABLE IF EXISTS `cloud`.`pod_vlan_map`;

DROP TABLE IF EXISTS `cloud`.`vm_host`;

DROP TABLE IF EXISTS `cloud`.`op_ha_work`;

DROP TABLE IF EXISTS `cloud`.`op_dc_vnet_alloc`;

DROP TABLE IF EXISTS `cloud`.`op_dc_ip_address_alloc`;

DROP TABLE IF EXISTS `cloud`.`op_vm_host`;

DROP TABLE IF EXISTS `cloud`.`op_host_queue`;

DROP TABLE IF EXISTS `cloud`.`console_proxy`;

DROP TABLE IF EXISTS `cloud`.`secondary_storage_vm`;

DROP TABLE IF EXISTS `cloud`.`domain`;

DROP TABLE IF EXISTS `cloud`.`account`;

DROP TABLE IF EXISTS `cloud`.`limit`;

DROP TABLE IF EXISTS `cloud`.`op_host_capacity`;

DROP TABLE IF EXISTS `cloud`.`alert`;

DROP TABLE IF EXISTS `cloud`.`op_lock`;

DROP TABLE IF EXISTS `cloud`.`op_host_upgrade`;

DROP TABLE IF EXISTS `cloud`.`snapshots`;

DROP TABLE IF EXISTS `cloud`.`scheduled_volume_backups`;

DROP TABLE IF EXISTS `cloud`.`disk_offering`;

DROP TABLE IF EXISTS `cloud`.`security_group`;

DROP TABLE IF EXISTS `cloud`.`network_rule_config`;

DROP TABLE IF EXISTS `cloud`.`host_details`;

DROP TABLE IF EXISTS `cloud`.`launch_permission`;

DROP TABLE IF EXISTS `cloud`.`resource_limit`;

DROP TABLE IF EXISTS `cloud`.`async_job`;

DROP TABLE IF EXISTS `cloud`.`sync_queue`;

DROP TABLE IF EXISTS `cloud`.`sync_queue_item`;

DROP TABLE IF EXISTS `cloud`.`security_group_vm_map`;

DROP TABLE IF EXISTS `cloud`.`load_balancer_vm_map`;

DROP TABLE IF EXISTS `cloud`.`load_balancer_stickiness_policies`;

DROP TABLE IF EXISTS `cloud`.`load_balancer_inline_ip_map`;

DROP TABLE IF EXISTS `cloud`.`storage_pool`;

DROP TABLE IF EXISTS `cloud`.`storage_pool_host_ref`;

DROP TABLE IF EXISTS `cloud`.`template_spool_ref`;

DROP TABLE IF EXISTS `cloud`.`guest_os`;

DROP TABLE IF EXISTS `cloud`.`snapshot_policy`;

DROP TABLE IF EXISTS `cloud`.`snapshot_policy_ref`;

DROP TABLE IF EXISTS `cloud`.`snapshot_schedule`;

DROP TABLE IF EXISTS `cloud`.`op_pod_vlan_alloc`;

DROP TABLE IF EXISTS `cloud`.`storage_pool_details`;

DROP TABLE IF EXISTS `cloud`.`cluster`;

DROP TABLE IF EXISTS `cloud`.`nics`;

DROP TABLE IF EXISTS `cloud`.`networks`;

DROP TABLE IF EXISTS `cloud`.`op_networks`;

DROP TABLE IF EXISTS `cloud`.`network_offerings`;

DROP TABLE IF EXISTS `cloud`.`account_network_ref`;

DROP TABLE IF EXISTS `cloud`.`domain_network_ref`;

DROP TABLE IF EXISTS `cloud`.`instance_group`;

DROP TABLE IF EXISTS `cloud`.`instance_group_vm_map`;

DROP TABLE IF EXISTS `cloud`.`op_it_work`;

DROP TABLE IF EXISTS `cloud`.`load_balancing_ip_map`;

DROP TABLE IF EXISTS `cloud`.`load_balancing_rules`;

DROP TABLE IF EXISTS `cloud`.`port_forwarding_rules`;

DROP TABLE IF EXISTS `cloud`.`firewall_rules`;

DROP TABLE IF EXISTS `cloud`.`firewall_rules_cidrs`;

DROP TABLE IF EXISTS `cloud`.`ssh_keypairs`;

DROP TABLE IF EXISTS `cloud`.`usage_event`;

DROP TABLE IF EXISTS `cloud`.`host_tags`;

DROP TABLE IF EXISTS `cloud`.`version`;

DROP TABLE IF EXISTS `cloud`.`account_vlan_map`;

DROP TABLE IF EXISTS `cloud`.`cluster_details`;

DROP TABLE IF EXISTS `cloud`.`guest_os_category`;

DROP TABLE IF EXISTS `cloud`.`guest_os_hypervisor`;

DROP TABLE IF EXISTS `cloud`.`op_dc_link_local_ip_address_alloc`;

DROP TABLE IF EXISTS `cloud`.`op_host`;

DROP TABLE IF EXISTS `cloud`.`op_nwgrp_work`;

DROP TABLE IF EXISTS `cloud`.`op_vm_ruleset_log`;

DROP TABLE IF EXISTS `cloud`.`ovs_tunnel_network`;

DROP TABLE IF EXISTS `cloud`.`ovs_tunnel_interface`;

DROP TABLE IF EXISTS `cloud`.`remote_access_vpn`;

DROP TABLE IF EXISTS `cloud`.`resource_count`;

DROP TABLE IF EXISTS `cloud`.`security_ingress_rule`;

DROP TABLE IF EXISTS `cloud`.`security_group_rule`;

DROP TABLE IF EXISTS `cloud`.`stack_maid`;

DROP TABLE IF EXISTS `cloud`.`storage_pool_work`;

DROP TABLE IF EXISTS `cloud`.`user_vm_details`;

DROP TABLE IF EXISTS `cloud`.`vpn_users`;

DROP TABLE IF EXISTS `cloud`.`data_center_details`;

DROP TABLE IF EXISTS `cloud`.`network_tags`;

DROP TABLE IF EXISTS `cloud`.`op_host_transfer`;

DROP TABLE IF EXISTS `cloud`.`projects`;

DROP TABLE IF EXISTS `cloud`.`physical_network`;

DROP TABLE IF EXISTS `cloud`.`physical_network_tags`;

DROP TABLE IF EXISTS `cloud`.`physical_network_isolation_methods`;

DROP TABLE IF EXISTS `cloud`.`physical_network_traffic_types`;

DROP TABLE IF EXISTS `cloud`.`physical_network_service_providers`;

DROP TABLE IF EXISTS `cloud`.`virtual_router_elements`;

DROP TABLE IF EXISTS `cloud`.`dc_storage_network_ip_range`;

DROP TABLE IF EXISTS `cloud`.`op_dc_storage_network_ip_address`;

DROP TABLE IF EXISTS `cloud`.`cluster_vsm_map`;

DROP TABLE IF EXISTS `cloud`.`virtual_supervisor_module`;

DROP TABLE IF EXISTS `cloud`.`port_profile`;

DROP TABLE IF EXISTS `cloud`.`region`;

DROP TABLE IF EXISTS `cloud`.`s2s_customer_gateway`;

DROP TABLE IF EXISTS `cloud`.`s2s_vpn_gateway`;

DROP TABLE IF EXISTS `cloud`.`s2s_vpn_connection`;

DROP TABLE IF EXISTS `cloud`,`external_nicira_nvp_devices`;

DROP TABLE IF EXISTS `cloud`,`nicira_nvp_nic_map`;

DROP TABLE IF EXISTS `cloud`,`s3`;

DROP TABLE IF EXISTS `cloud`,`template_s3_ref`;

DROP TABLE IF EXISTS `cloud`,`nicira_nvp_router_map`;

DROP TABLE IF EXISTS `cloud`,`external_bigswitch_vns_devices`;

DROP TABLE IF EXISTS `cloud`.`autoscale_vmgroup_policy_map`;

DROP TABLE IF EXISTS `cloud`.`autoscale_policy_condition_map`;

DROP TABLE IF EXISTS `cloud`.`autoscale_vmgroups`;

DROP TABLE IF EXISTS `cloud`.`autoscale_vmprofiles`;

DROP TABLE IF EXISTS `cloud`.`autoscale_policies`;

DROP TABLE IF EXISTS `cloud`.`counter`;

DROP TABLE IF EXISTS `cloud`.`conditions`;

DROP TABLE IF EXISTS `cloud`.`inline_load_balancer_nic_map`;

DROP TABLE IF EXISTS `cloud`.`cmd_exec_log`;

DROP TABLE IF EXISTS `cloud`.`keystore`;

DROP TABLE IF EXISTS `cloud`.`swift`;

DROP TABLE IF EXISTS `cloud`.`project_account`;

DROP TABLE IF EXISTS `cloud`.`project_invitations`;

DROP TABLE IF EXISTS `cloud`.`elastic_lb_vm_map`;

DROP TABLE IF EXISTS `cloud`.`ntwk_offering_service_map`;

DROP TABLE IF EXISTS `cloud`.`ntwk_service_map`;

DROP TABLE IF EXISTS `cloud`.`external_load_balancer_devices`;

DROP TABLE IF EXISTS `cloud`.`external_firewall_devices`;

DROP TABLE IF EXISTS `cloud`.`network_external_lb_device_map`;

DROP TABLE IF EXISTS `cloud`.`network_external_firewall_device_map`;

DROP TABLE IF EXISTS `cloud`.`virtual_router_providers`;

DROP TABLE IF EXISTS `cloud`.`op_user_stats_log`;

DROP TABLE IF EXISTS `cloud`.`netscaler_pod_ref`;

DROP TABLE IF EXISTS `cloud`.`mshost_peer`;

DROP TABLE IF EXISTS `cloud`.`vm_template_details`;

DROP TABLE IF EXISTS `cloud`.`hypervisor_capabilities`;

DROP TABLE IF EXISTS `cloud`.`template_swift_ref`;

DROP TABLE IF EXISTS `cloud`.`account_details`;

DROP TABLE IF EXISTS `cloud`.`vpc`;

DROP TABLE IF EXISTS `cloud`.`vpc_offerings`;

DROP TABLE IF EXISTS `cloud`.`vpc_offering_service_map`;

DROP TABLE IF EXISTS `cloud`.`vpc_gateways`;

DROP TABLE IF EXISTS `cloud`.`router_network_ref`;

DROP TABLE IF EXISTS `cloud`.`private_ip_address`;

DROP TABLE IF EXISTS `cloud`.`static_routes`;

DROP TABLE IF EXISTS `cloud`.`resource_tags`;

DROP TABLE IF EXISTS `cloud`.`primary_data_store_provider`;

DROP TABLE IF EXISTS `cloud`.`image_data_store_provider`;

DROP TABLE IF EXISTS `cloud`.`image_data_store`;

DROP TABLE IF EXISTS `cloud`.`vm_compute_tags`;

DROP TABLE IF EXISTS `cloud`.`vm_root_disk_tags`;

DROP TABLE IF EXISTS `cloud`.`vm_network_map`;

DROP TABLE IF EXISTS `cloud`.`netapp_volume`;

DROP TABLE IF EXISTS `cloud`.`netapp_pool`;

DROP TABLE IF EXISTS `cloud`.`netapp_lun`;

CREATE TABLE `cloud`.`version` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',

`version` char(40) NOT NULL UNIQUE COMMENT 'version',

`updated` datetime NOT NULL COMMENT 'Date this version table was updated',

`step` char(32) NOT NULL COMMENT 'Step in the upgrade to this version',

PRIMARY KEY (`id`),

INDEX `i_version__version`(`version`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.1.0', now(), 'Complete');

CREATE TABLE `cloud`.`op_it_work` (

`id` char(40) COMMENT 'reservation id',

`mgmt_server_id` bigint unsigned COMMENT 'management server id',

`created_at` bigint unsigned NOT NULL COMMENT 'when was this work detail created',

`thread` varchar(255) NOT NULL COMMENT 'thread name',

`type` char(32) NOT NULL COMMENT 'type of work',

`vm_type` char(32) NOT NULL COMMENT 'type of vm',

`step` char(32) NOT NULL COMMENT 'state',

`updated_at` bigint unsigned NOT NULL COMMENT 'time it was taken over',

`instance_id` bigint unsigned NOT NULL COMMENT 'vm instance',

`resource_type` char(32) COMMENT 'type of resource being worked on',

`resource_id` bigint unsigned COMMENT 'resource id being worked on',

PRIMARY KEY (`id`),

CONSTRAINT `fk_op_it_work__mgmt_server_id` FOREIGN KEY (`mgmt_server_id`) REFERENCES `mshost`(`msid`),

CONSTRAINT `fk_op_it_work__instance_id` FOREIGN KEY (`instance_id`) REFERENCES `vm_instance`(`id`) ON DELETE CASCADE,

INDEX `i_op_it_work__step`(`step`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_networks`(

`id` bigint unsigned NOT NULL UNIQUE KEY,

`mac_address_seq` bigint unsigned NOT NULL DEFAULT 1 COMMENT 'mac address',

`nics_count` int unsigned NOT NULL DEFAULT 0 COMMENT '# of nics',

`gc` tinyint unsigned NOT NULL DEFAULT 1 COMMENT 'gc this network or not',

`check_for_gc` tinyint unsigned NOT NULL DEFAULT 1 COMMENT 'check this network for gc or not',

PRIMARY KEY(`id`),

CONSTRAINT `fk_op_networks__id` FOREIGN KEY (`id`) REFERENCES `networks`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`networks` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` varchar(255) COMMENT 'name for this network',

`uuid` varchar(40),

`display_text` varchar(255) COMMENT 'display text for this network',

`traffic_type` varchar(32) NOT NULL COMMENT 'type of traffic going through this network',

`broadcast_domain_type` varchar(32) NOT NULL COMMENT 'type of broadcast domain used',

`broadcast_uri` varchar(255) COMMENT 'broadcast domain specifier',

`gateway` varchar(15) COMMENT 'gateway for this network configuration',

`cidr` varchar(18) COMMENT 'network cidr',

`ip6_gateway` varchar(50) COMMENT 'IPv6 gateway for this network',

`ip6_cidr` varchar(50) COMMENT 'IPv6 cidr for this network',

`mode` varchar(32) COMMENT 'How to retrieve ip address in this network',

`network_offering_id` bigint unsigned NOT NULL COMMENT 'network offering id that this configuration is created from',

`physical_network_id` bigint unsigned COMMENT 'physical network id that this configuration is based on',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center id that this configuration is used in',

`guru_name` varchar(255) NOT NULL COMMENT 'who is responsible for this type of network configuration',

`state` varchar(32) NOT NULL COMMENT 'what state is this configuration in',

`related` bigint unsigned NOT NULL COMMENT 'related to what other network configuration',

`domain_id` bigint unsigned NOT NULL COMMENT 'foreign key to domain id',

`account_id` bigint unsigned NOT NULL COMMENT 'owner of this network',

`dns1` varchar(255) COMMENT 'comma separated DNS list',

`dns2` varchar(255) COMMENT 'comma separated DNS list',

`guru_data` varchar(1024) COMMENT 'data stored by the network guru that setup this network',

`set_fields` bigint unsigned NOT NULL DEFAULT 0 COMMENT 'which fields are set already',

`acl_type` varchar(15) COMMENT 'ACL access type. Null for system networks, can be Account/Domain for Guest networks',

`network_domain` varchar(255) COMMENT 'domain',

`reservation_id` char(40) COMMENT 'reservation id',

`guest_type` char(32) COMMENT 'type of guest network that can be shared or isolated',

`restart_required` int(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 if restart is required for the network',

`created` datetime NOT NULL COMMENT 'date created',

`removed` datetime COMMENT 'date removed if not null',

`specify_ip_ranges` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network provides an ability to define ip ranges',

`vpc_id` bigint unsigned COMMENT 'vpc this network belongs to',

PRIMARY KEY (`id`),

CONSTRAINT `fk_networks__network_offering_id` FOREIGN KEY (`network_offering_id`) REFERENCES `network_offerings`(`id`),

CONSTRAINT `fk_networks__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_networks__related` FOREIGN KEY(`related`) REFERENCES `networks`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_networks__account_id` FOREIGN KEY(`account_id`) REFERENCES `account`(`id`),

CONSTRAINT `fk_networks__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain`(`id`),

CONSTRAINT `fk_networks__vpc_id` FOREIGN KEY(`vpc_id`) REFERENCES `vpc`(`id`),

CONSTRAINT `uc_networks__uuid` UNIQUE (`uuid`),

INDEX `i_networks__removed`(`removed`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`account_network_ref` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`account_id` bigint unsigned NOT NULL COMMENT 'account id',

`network_id` bigint unsigned NOT NULL COMMENT 'network id',

`is_owner` smallint(1) NOT NULL COMMENT 'is the owner of the network',

PRIMARY KEY (`id`),

CONSTRAINT `fk_account_network_ref__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_account_network_ref__networks_id` FOREIGN KEY (`network_id`) REFERENCES `networks`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`nics` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',

`uuid` varchar(40),

`instance_id` bigint unsigned COMMENT 'vm instance id',

`mac_address` varchar(17) COMMENT 'mac address',

`ip4_address` char(40) COMMENT 'ip4 address',

`netmask` varchar(15) COMMENT 'netmask for ip4 address',

`gateway` varchar(15) COMMENT 'gateway',

`ip_type` varchar(32) COMMENT 'type of ip',

`broadcast_uri` varchar(255) COMMENT 'broadcast uri',

`network_id` bigint unsigned NOT NULL COMMENT 'network configuration id',

`mode` varchar(32) COMMENT 'mode of getting ip address',

`state` varchar(32) NOT NULL COMMENT 'state of the creation',

`strategy` varchar(32) NOT NULL COMMENT 'reservation strategy',

`reserver_name` varchar(255) COMMENT 'Name of the component that reserved the ip address',

`reservation_id` varchar(64) COMMENT 'id for the reservation',

`device_id` int(10) COMMENT 'device id for the network when plugged into the virtual machine',

`update_time` timestamp NOT NULL COMMENT 'time the state was changed',

`isolation_uri` varchar(255) COMMENT 'id for isolation',

`ip6_address` char(40) COMMENT 'ip6 address',

`ip6_gateway` varchar(50) COMMENT 'gateway for ip6 address',

`ip6_cidr` varchar(50) COMMENT 'cidr for ip6 address',

`default_nic` tinyint NOT NULL COMMENT "None",

`vm_type` varchar(32) COMMENT 'type of vm: System or User vm',

`created` datetime NOT NULL COMMENT 'date created',

`removed` datetime COMMENT 'date removed if not null',

PRIMARY KEY (`id`),

CONSTRAINT `fk_nics__instance_id` FOREIGN KEY `fk_nics__instance_id`(`instance_id`) REFERENCES `vm_instance`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_nics__networks_id` FOREIGN KEY `fk_nics__networks_id`(`network_id`) REFERENCES `networks`(`id`),

CONSTRAINT `uc_nics__uuid` UNIQUE (`uuid`),

INDEX `i_nics__removed`(`removed`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`network_offerings` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',

`name` varchar(64) COMMENT 'name of the network offering',

`uuid` varchar(40),

`unique_name` varchar(64) UNIQUE COMMENT 'unique name of the network offering',

`display_text` varchar(255) NOT NULL COMMENT 'text to display to users',

`nw_rate` smallint unsigned COMMENT 'network rate throttle mbits/s',

`mc_rate` smallint unsigned COMMENT 'mcast rate throttle mbits/s',

`traffic_type` varchar(32) NOT NULL COMMENT 'traffic type carried on this network',

`tags` varchar(4096) COMMENT 'tags supported by this offering',

`system_only` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is this network offering for system use only',

`specify_vlan` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Should the user specify vlan',

`service_offering_id` bigint unsigned COMMENT 'service offering id that virtual router is tied to',

`conserve_mode` int(1) unsigned NOT NULL DEFAULT 1 COMMENT 'Is this network offering is IP conserve mode enabled',

`created` datetime NOT NULL COMMENT 'time the entry was created',

`removed` datetime DEFAULT NULL COMMENT 'time the entry was removed',

`default` int(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 if network offering is default',

`availability` varchar(255) NOT NULL COMMENT 'availability of the network',

`dedicated_lb_service` int(1) unsigned NOT NULL DEFAULT 1 COMMENT 'true if the network offering provides a dedicated load balancer for each network',

`shared_source_nat_service` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides the shared source nat service',

`sort_key` int(32) NOT NULL default 0 COMMENT 'sort key used for customising sort method',

`redundant_router_service` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides the redundant router service',

`state` char(32) COMMENT 'state of the network offering that has Disabled value by default',

`guest_type` char(32) COMMENT 'type of guest network that can be shared or isolated',

`elastic_ip_service` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides elastic ip service',

`elastic_lb_service` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides elastic lb service',

`specify_ip_ranges` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides an ability to define ip ranges',

`inline` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is this network offering LB provider is in inline mode',

`is_persistent` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'true if the network offering provides an ability to create persistent networks',

PRIMARY KEY (`id`),

INDEX `i_network_offerings__system_only`(`system_only`),

INDEX `i_network_offerings__removed`(`removed`),

CONSTRAINT `uc_network_offerings__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`cluster` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',

`name` varchar(255) COMMENT 'name for the cluster',

`uuid` varchar(40) COMMENT 'uuid is different with following guid, while the later one is generated by hypervisor resource',

`guid` varchar(255) UNIQUE DEFAULT NULL COMMENT 'guid for the cluster',

`pod_id` bigint unsigned NOT NULL COMMENT 'pod id',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center id',

`hypervisor_type` varchar(32),

`cluster_type` varchar(64) DEFAULT 'CloudManaged',

`allocation_state` varchar(32) NOT NULL DEFAULT 'Enabled' COMMENT 'Is this cluster enabled for allocation for new resources',

`managed_state` varchar(32) NOT NULL DEFAULT 'Managed' COMMENT 'Is this cluster managed by cloudstack',

`removed` datetime COMMENT 'date removed if not null',

PRIMARY KEY (`id`),

CONSTRAINT `fk_cluster__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `cloud`.`data_center`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_cluster__pod_id` FOREIGN KEY (`pod_id`) REFERENCES `cloud`.`host_pod_ref`(`id`),

UNIQUE `i_cluster__pod_id__name`(`pod_id`, `name`),

INDEX `i_cluster__allocation_state`(`allocation_state`),

INDEX `i_cluster__removed`(`removed`),

CONSTRAINT `uc_cluster__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`cluster_details` (

`id` bigint unsigned NOT NULL auto_increment,

`cluster_id` bigint unsigned NOT NULL COMMENT 'cluster id',

`name` varchar(255) NOT NULL,

`value` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `fk_cluster_details__cluster_id` FOREIGN KEY (`cluster_id`) REFERENCES `cluster`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_host_upgrade` (

`host_id` bigint unsigned NOT NULL UNIQUE COMMENT 'host id',

`version` varchar(20) NOT NULL COMMENT 'version',

`state` varchar(20) NOT NULL COMMENT 'state',

PRIMARY KEY (`host_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_lock` (

`key` varchar(128) NOT NULL UNIQUE COMMENT 'primary key of the table',

`mac` varchar(17) NOT NULL COMMENT 'management server id of the server that holds this lock',

`ip` char(40) NOT NULL COMMENT 'name of the thread that holds this lock',

`thread` varchar(255) NOT NULL COMMENT 'Thread id that acquired this lock',

`acquired_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time acquired',

`waiters` int NOT NULL DEFAULT 0 COMMENT 'How many have the thread acquired this lock (reentrant)',

PRIMARY KEY (`key`),

INDEX `i_op_lock__mac_ip_thread`(`mac`, `ip`, `thread`)

) ENGINE=Memory DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`configuration` (

`category` varchar(255) NOT NULL DEFAULT 'Advanced',

`instance` varchar(255) NOT NULL,

`component` varchar(255) NOT NULL DEFAULT 'management-server',

`name` varchar(255) NOT NULL,

`value` varchar(4095),

`description` varchar(1024),

PRIMARY KEY (`name`),

INDEX `i_configuration__instance`(`instance`),

INDEX `i_configuration__name`(`name`),

INDEX `i_configuration__category`(`category`),

INDEX `i_configuration__component`(`component`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_ha_work` (

`id` bigint unsigned UNIQUE NOT NULL AUTO_INCREMENT COMMENT 'id',

`instance_id` bigint unsigned NOT NULL COMMENT 'vm instance that needs to be ha.',

`type` varchar(32) NOT NULL COMMENT 'type of work',

`vm_type` varchar(32) NOT NULL COMMENT 'VM type',

`state` varchar(32) NOT NULL COMMENT 'state of the vm instance when this happened.',

`mgmt_server_id` bigint unsigned COMMENT 'management server that has taken up the work of doing ha',

`host_id` bigint unsigned COMMENT 'host that the vm is suppose to be on',

`created` datetime NOT NULL COMMENT 'time the entry was requested',

`tried` int(10) unsigned COMMENT '# of times tried',

`taken` datetime COMMENT 'time it was taken by the management server',

`step` varchar(32) NOT NULL COMMENT 'Step in the work',

`time_to_try` bigint COMMENT 'time to try do this work',

`updated` bigint unsigned NOT NULL COMMENT 'time the VM state was updated when it was stored into work queue',

PRIMARY KEY (`id`),

CONSTRAINT `fk_op_ha_work__instance_id` FOREIGN KEY `fk_op_ha_work__instance_id` (`instance_id`) REFERENCES `vm_instance` (`id`) ON DELETE CASCADE,

INDEX `i_op_ha_work__instance_id`(`instance_id`),

CONSTRAINT `fk_op_ha_work__host_id` FOREIGN KEY `fk_op_ha_work__host_id` (`host_id`) REFERENCES `host` (`id`),

INDEX `i_op_ha_work__host_id`(`host_id`),

INDEX `i_op_ha_work__step`(`step`),

INDEX `i_op_ha_work__type`(`type`),

CONSTRAINT `fk_op_ha_work__mgmt_server_id` FOREIGN KEY `fk_op_ha_work__mgmt_server_id`(`mgmt_server_id`) REFERENCES `mshost`(`msid`),

INDEX `i_op_ha_work__mgmt_server_id`(`mgmt_server_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`sequence` (

`name` varchar(64) UNIQUE NOT NULL COMMENT 'name of the sequence',

`value` bigint unsigned NOT NULL COMMENT 'sequence value',

PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('vm_instance_seq', 1);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('vm_template_seq', 200);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('public_mac_address_seq', 1);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('private_mac_address_seq', 1);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('storage_pool_seq', 200);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('volume_seq', 1);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('networks_seq', 200);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('checkpoint_seq', 1);

INSERT INTO `cloud`.`sequence` (name, value) VALUES ('physical_networks_seq', 200);

CREATE TABLE `cloud`.`volumes` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',

`account_id` bigint unsigned NOT NULL COMMENT 'owner. foreign key to account table',

`domain_id` bigint unsigned NOT NULL COMMENT 'the domain that the owner belongs to',

`pool_id` bigint unsigned COMMENT 'pool it belongs to. foreign key to storage_pool table',

`last_pool_id` bigint unsigned COMMENT 'last pool it belongs to.',

`instance_id` bigint unsigned NULL COMMENT 'vm instance it belongs to. foreign key to vm_instance table',

`device_id` bigint unsigned NULL COMMENT 'which device inside vm instance it is ',

`name` varchar(255) COMMENT 'A user specified name for the volume',

`uuid` varchar(40),

`size` bigint unsigned NOT NULL COMMENT 'total size',

`folder` varchar(255) COMMENT 'The folder where the volume is saved',

`path` varchar(255) COMMENT 'Path',

`pod_id` bigint unsigned COMMENT 'pod this volume belongs to',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center this volume belongs to',

`iscsi_name` varchar(255) COMMENT 'iscsi target name',

`host_ip` char(40) COMMENT 'host ip address for convenience',

`volume_type` varchar(64) NOT NULL COMMENT 'root, swap or data',

`pool_type` varchar(64) COMMENT 'type of the pool',

`disk_offering_id` bigint unsigned NOT NULL COMMENT 'can be null for system VMs',

`template_id` bigint unsigned COMMENT 'fk to vm_template.id',

`first_snapshot_backup_uuid` varchar (255) COMMENT 'The first snapshot that was ever taken for this volume',

`recreatable` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is this volume recreatable?',

`created` datetime COMMENT 'Date Created',

`attached` datetime COMMENT 'Date Attached',

`updated` datetime COMMENT 'Date updated for attach/detach',

`removed` datetime COMMENT 'Date removed. not null if removed',

`state` varchar(32) COMMENT 'State machine',

`chain_info` text COMMENT 'save possible disk chain info in primary storage',

`update_count` bigint unsigned NOT NULL DEFAULT 0 COMMENT 'date state was updated',

PRIMARY KEY (`id`),

INDEX `i_volumes__removed`(`removed`),

INDEX `i_volumes__pod_id`(`pod_id`),

INDEX `i_volumes__data_center_id`(`data_center_id`),

CONSTRAINT `fk_volumes__account_id` FOREIGN KEY `fk_volumes__account_id` (`account_id`) REFERENCES `account` (`id`),

INDEX `i_volumes__account_id`(`account_id`),

CONSTRAINT `fk_volumes__pool_id` FOREIGN KEY `fk_volumes__pool_id` (`pool_id`) REFERENCES `storage_pool` (`id`),

INDEX `i_volumes__pool_id`(`pool_id`),

INDEX `i_volumes__last_pool_id`(`last_pool_id`),

CONSTRAINT `fk_volumes__instance_id` FOREIGN KEY `fk_volumes__instance_id` (`instance_id`) REFERENCES `vm_instance` (`id`) ON DELETE CASCADE,

INDEX `i_volumes__instance_id`(`instance_id`),

INDEX `i_volumes__state`(`state`),

INDEX `i_volumes__update_count`(`update_count`),

CONSTRAINT `uc_volumes__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`snapshots` (

`id` bigint unsigned UNIQUE NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',

`data_center_id` bigint unsigned NOT NULL,

`account_id` bigint unsigned NOT NULL COMMENT 'owner. foreign key to account table',

`domain_id` bigint unsigned NOT NULL COMMENT 'the domain that the owner belongs to',

`volume_id` bigint unsigned NOT NULL COMMENT 'volume it belongs to. foreign key to volume table',

`disk_offering_id` bigint unsigned NOT NULL COMMENT 'from original volume',

`status` varchar(32) COMMENT 'snapshot creation status',

`path` varchar(255) COMMENT 'Path',

`name` varchar(255) NOT NULL COMMENT 'snapshot name',

`uuid` varchar(40),

`snapshot_type` int(4) NOT NULL COMMENT 'type of snapshot, e.g. manual, recurring',

`type_description` varchar(25) COMMENT 'description of the type of snapshot, e.g. manual, recurring',

`size` bigint unsigned NOT NULL COMMENT 'original disk size of snapshot',

`created` datetime COMMENT 'Date Created',

`removed` datetime COMMENT 'Date removed. not null if removed',

`backup_snap_id` varchar(255) COMMENT 'Back up uuid of the snapshot',

`swift_id` bigint unsigned COMMENT 'which swift',

`s3_id` bigint unsigned COMMENT 'S3 to which this snapshot will be stored',

`sechost_id` bigint unsigned COMMENT 'secondary storage host id',

`prev_snap_id` bigint unsigned COMMENT 'Id of the most recent snapshot',

`hypervisor_type` varchar(32) NOT NULL COMMENT 'hypervisor that the snapshot was taken under',

`version` varchar(32) COMMENT 'snapshot version',

PRIMARY KEY (`id`),

CONSTRAINT `uc_snapshots__uuid` UNIQUE (`uuid`),

CONSTRAINT `fk_snapshots__s3_id` FOREIGN KEY `fk_snapshots__s3_id` (`s3_id`) REFERENCES `s3` (`id`),

INDEX `i_snapshots__removed`(`removed`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`vlan` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,

`uuid` varchar(40),

`vlan_id` varchar(255),

`vlan_gateway` varchar(255),

`vlan_netmask` varchar(255),

`ip6_gateway` varchar(255),

`ip6_cidr` varchar(255),

`description` varchar(255),

`ip6_range` varchar(255),

`vlan_type` varchar(255),

`data_center_id` bigint unsigned NOT NULL,

`network_id` bigint unsigned NOT NULL COMMENT 'id of corresponding network offering',

`physical_network_id` bigint unsigned NOT NULL COMMENT 'physical network id that this configuration is based on',

PRIMARY KEY (`id`),

#CONSTRAINT `fk_vlan__network_id` FOREIGN KEY (`network_id`) REFERENCES `networks`(`id`),

CONSTRAINT `fk_vlan__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`),

CONSTRAINT `uc_vlan__uuid` UNIQUE (`uuid`),

CONSTRAINT `fk_vlan__physical_network_id` FOREIGN KEY (`physical_network_id`) REFERENCES `physical_network`(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`pod_vlan_map` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,

`pod_id` bigint unsigned NOT NULL COMMENT 'pod id. foreign key to pod table',

`vlan_db_id` bigint unsigned NOT NULL COMMENT 'database id of vlan. foreign key to vlan table',

PRIMARY KEY (`id`),

CONSTRAINT `fk_pod_vlan_map__pod_id` FOREIGN KEY (`pod_id`) REFERENCES `host_pod_ref` (`id`) ON DELETE CASCADE,

INDEX `i_pod_vlan_map__pod_id`(`pod_id`),

CONSTRAINT `fk_pod_vlan_map__vlan_id` FOREIGN KEY (`vlan_db_id`) REFERENCES `vlan` (`id`) ON DELETE CASCADE,

INDEX `i_pod_vlan_map__vlan_id`(`vlan_db_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`account_vlan_map` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,

`account_id` bigint unsigned NOT NULL COMMENT 'account id. foreign key to account table',

`vlan_db_id` bigint unsigned NOT NULL COMMENT 'database id of vlan. foreign key to vlan table',

PRIMARY KEY (`id`),

CONSTRAINT `fk_account_vlan_map__account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,

INDEX `i_account_vlan_map__account_id`(`account_id`),

CONSTRAINT `fk_account_vlan_map__vlan_id` FOREIGN KEY (`vlan_db_id`) REFERENCES `vlan` (`id`) ON DELETE CASCADE,

INDEX `i_account_vlan_map__vlan_id`(`vlan_db_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`data_center` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,

`name` varchar(255),

`uuid` varchar(40),

`description` varchar(255),

`dns1` varchar(255) NOT NULL,

`dns2` varchar(255),

`internal_dns1` varchar(255) NOT NULL,

`internal_dns2` varchar(255),

`gateway` varchar(15),

`netmask` varchar(15),

`router_mac_address` varchar(17) NOT NULL DEFAULT '02:00:00:00:00:01' COMMENT 'mac address for the router within the domain',

`mac_address` bigint unsigned NOT NULL DEFAULT '1' COMMENT 'Next available mac address for the ethernet card interacting with public internet',

`guest_network_cidr` varchar(18),

`domain` varchar(100) COMMENT 'Network domain name of the Vms of the zone',

`domain_id` bigint unsigned COMMENT 'domain id for the parent domain to this zone (null signifies public zone)',

`networktype` varchar(255) NOT NULL DEFAULT 'Basic' COMMENT 'Network type of the zone',

`dns_provider` char(64) DEFAULT 'VirtualRouter',

`gateway_provider` char(64) DEFAULT 'VirtualRouter',

`firewall_provider` char(64) DEFAULT 'VirtualRouter',

`dhcp_provider` char(64) DEFAULT 'VirtualRouter',

`lb_provider` char(64) DEFAULT 'VirtualRouter',

`vpn_provider` char(64) DEFAULT 'VirtualRouter',

`userdata_provider` char(64) DEFAULT 'VirtualRouter',

`allocation_state` varchar(32) NOT NULL DEFAULT 'Enabled' COMMENT 'Is this data center enabled for allocation for new resources',

`zone_token` varchar(255),

`is_security_group_enabled` tinyint NOT NULL DEFAULT 0 COMMENT '1: enabled, 0: not',

`is_local_storage_enabled` tinyint NOT NULL DEFAULT 0 COMMENT 'Is local storage offering enabled for this data center; 1: enabled, 0: not',

`removed` datetime COMMENT 'date removed if not null',

PRIMARY KEY (`id`),

CONSTRAINT `fk_data_center__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain`(`id`),

INDEX `i_data_center__domain_id`(`domain_id`),

INDEX `i_data_center__allocation_state`(`allocation_state`),

INDEX `i_data_center__zone_token`(`zone_token`),

INDEX `i_data_center__removed`(`removed`),

CONSTRAINT `uc_data_center__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_dc_ip_address_alloc` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',

`ip_address` char(40) NOT NULL COMMENT 'ip address',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center it belongs to',

`pod_id` bigint unsigned NOT NULL COMMENT 'pod it belongs to',

`nic_id` bigint unsigned NULL COMMENT 'nic id',

`reservation_id` char(40) NULL COMMENT 'reservation id',

`taken` datetime COMMENT 'Date taken',

`mac_address` bigint unsigned NOT NULL COMMENT 'mac address for management ips',

PRIMARY KEY (`id`),

CONSTRAINT `fk_op_dc_ip_address_alloc__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE,

INDEX `i_op_dc_ip_address_alloc__pod_id__data_center_id__taken` (`pod_id`, `data_center_id`, `taken`, `nic_id`),

UNIQUE `i_op_dc_ip_address_alloc__ip_address__data_center_id`(`ip_address`, `data_center_id`),

CONSTRAINT `fk_op_dc_ip_address_alloc__pod_id` FOREIGN KEY (`pod_id`) REFERENCES `host_pod_ref` (`id`) ON DELETE CASCADE,

INDEX `i_op_dc_ip_address_alloc__pod_id`(`pod_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_dc_link_local_ip_address_alloc` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',

`ip_address` char(40) NOT NULL COMMENT 'ip address',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center it belongs to',

`pod_id` bigint unsigned NOT NULL COMMENT 'pod it belongs to',

`nic_id` bigint unsigned NULL COMMENT 'instance id',

`reservation_id` char(40) NULL COMMENT 'reservation id used to reserve this network',

`taken` datetime COMMENT 'Date taken',

PRIMARY KEY (`id`),

INDEX `i_op_dc_link_local_ip_address_alloc__pod_id`(`pod_id`),

INDEX `i_op_dc_link_local_ip_address_alloc__data_center_id`(`data_center_id`),

INDEX `i_op_dc_link_local_ip_address_alloc__nic_id_reservation_id`(`nic_id`,`reservation_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`dc_storage_network_ip_range` (

`id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,

`uuid` varchar(40),

`start_ip` char(40) NOT NULL COMMENT 'start ip address',

`end_ip` char(40) NOT NULL COMMENT 'end ip address',

`gateway` varchar(15) NOT NULL COMMENT 'gateway ip address',

`vlan` int unsigned DEFAULT NULL COMMENT 'vlan the storage network on',

`netmask` varchar(15) NOT NULL COMMENT 'netmask for storage network',

`data_center_id` bigint unsigned NOT NULL,

`pod_id` bigint unsigned NOT NULL COMMENT 'pod it belongs to',

`network_id` bigint unsigned NOT NULL COMMENT 'id of corresponding network offering',

PRIMARY KEY (`id`),

CONSTRAINT `fk_storage_ip_range__network_id` FOREIGN KEY (`network_id`) REFERENCES `networks`(`id`),

CONSTRAINT `fk_storage_ip_range__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`),

CONSTRAINT `fk_storage_ip_range__pod_id` FOREIGN KEY (`pod_id`) REFERENCES `host_pod_ref`(`id`),

CONSTRAINT `uc_storage_ip_range__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_dc_storage_network_ip_address` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',

`range_id` bigint unsigned NOT NULL COMMENT 'id of ip range in dc_storage_network_ip_range',

`ip_address` char(40) NOT NULL COMMENT 'ip address',

`mac_address` bigint unsigned NOT NULL COMMENT 'mac address for storage ips',

`taken` datetime COMMENT 'Date taken',

PRIMARY KEY (`id`),

CONSTRAINT `fk_storage_ip_address__range_id` FOREIGN KEY (`range_id`) REFERENCES `dc_storage_network_ip_range`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`host_pod_ref` (

`id` bigint unsigned NOT NULL UNIQUE auto_increment,

`name` varchar(255),

`uuid` varchar(40),

`data_center_id` bigint unsigned NOT NULL,

`gateway` varchar(255) NOT NULL COMMENT 'gateway for the pod',

`cidr_address` varchar(15) NOT NULL COMMENT 'CIDR address for the pod',

`cidr_size` bigint unsigned NOT NULL COMMENT 'CIDR size for the pod',

`description` varchar(255) COMMENT 'store private ip range in startIP-endIP format',

`allocation_state` varchar(32) NOT NULL DEFAULT 'Enabled' COMMENT 'Is this Pod enabled for allocation for new resources',

`external_dhcp` tinyint NOT NULL DEFAULT 0 COMMENT 'Is this Pod using external DHCP',

`removed` datetime COMMENT 'date removed if not null',

PRIMARY KEY (`id`),

UNIQUE KEY (`name`, `data_center_id`),

INDEX `i_host_pod_ref__data_center_id`(`data_center_id`),

INDEX `i_host_pod_ref__allocation_state`(`allocation_state`),

INDEX `i_host_pod_ref__removed`(`removed`),

CONSTRAINT `uc_host_pod_ref__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_dc_vnet_alloc` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary id',

`vnet` varchar(18) NOT NULL COMMENT 'vnet',

`physical_network_id` bigint unsigned NOT NULL COMMENT 'physical network the vnet belongs to',

`data_center_id` bigint unsigned NOT NULL COMMENT 'data center the vnet belongs to',

`reservation_id` char(40) NULL COMMENT 'reservation id',

`account_id` bigint unsigned NULL COMMENT 'account the vnet belongs to right now',

`taken` datetime COMMENT 'Date taken',

PRIMARY KEY (`id`),

UNIQUE `i_op_dc_vnet_alloc__vnet__data_center_id__account_id`(`vnet`, `data_center_id`, `account_id`),

INDEX `i_op_dc_vnet_alloc__dc_taken`(`data_center_id`, `taken`),

UNIQUE `i_op_dc_vnet_alloc__vnet__data_center_id`(`vnet`, `physical_network_id`, `data_center_id`),

CONSTRAINT `fk_op_dc_vnet_alloc__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_op_dc_vnet_alloc__physical_network_id` FOREIGN KEY (`physical_network_id`) REFERENCES `physical_network`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`firewall_rules` (

`id` bigint unsigned NOT NULL auto_increment COMMENT 'id',

`uuid` varchar(40),

`ip_address_id` bigint unsigned COMMENT 'id of the corresponding ip address',

`start_port` int(10) COMMENT 'starting port of a port range',

`end_port` int(10) COMMENT 'end port of a port range',

`state` char(32) NOT NULL COMMENT 'current state of this rule',

`protocol` char(16) NOT NULL default 'TCP' COMMENT 'protocol to open these ports for',

`purpose` char(32) NOT NULL COMMENT 'why are these ports opened?',

`account_id` bigint unsigned NOT NULL COMMENT 'owner id',

`domain_id` bigint unsigned NOT NULL COMMENT 'domain id',

`network_id` bigint unsigned NOT NULL COMMENT 'network id',

`xid` char(40) NOT NULL COMMENT 'external id',

`created` datetime COMMENT 'Date created',

`icmp_code` int(10) COMMENT 'The ICMP code (if protocol=ICMP). A value of -1 means all codes for the given ICMP type.',

`icmp_type` int(10) COMMENT 'The ICMP type (if protocol=ICMP). A value of -1 means all types.',

`related` bigint unsigned COMMENT 'related to what other firewall rule',

`type` varchar(10) NOT NULL DEFAULT 'USER',

`vpc_id` bigint unsigned COMMENT 'vpc the firewall rule is associated with',

`traffic_type` char(32) COMMENT 'the traffic type of the rule, can be Ingress or Egress',

PRIMARY KEY (`id`),

CONSTRAINT `fk_firewall_rules__ip_address_id` FOREIGN KEY(`ip_address_id`) REFERENCES `user_ip_address`(`id`),

CONSTRAINT `fk_firewall_rules__network_id` FOREIGN KEY(`network_id`) REFERENCES `networks`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_firewall_rules__account_id` FOREIGN KEY(`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_firewall_rules__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_firewall_rules__related` FOREIGN KEY(`related`) REFERENCES `firewall_rules`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_firewall_rules__vpc_id` FOREIGN KEY (`vpc_id`) REFERENCES `vpc`(`id`) ON DELETE CASCADE,

INDEX `i_firewall_rules__purpose`(`purpose`),

CONSTRAINT `uc_firewall_rules__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`firewall_rules_cidrs` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`firewall_rule_id` bigint(20) unsigned NOT NULL COMMENT 'firewall rule id',

`source_cidr` varchar(18) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_firewall_cidrs_firewall_rules` (`firewall_rule_id`),

UNIQUE INDEX `unique_rule_cidrs` (`firewall_rule_id`, `source_cidr`),

CONSTRAINT `fk_firewall_cidrs_firewall_rules` FOREIGN KEY (`firewall_rule_id`) REFERENCES `firewall_rules` (`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`load_balancing_rules` (

`id` bigint unsigned NOT NULL,

`name` varchar(255) NOT NULL,

`description` varchar(4096) NULL COMMENT 'description',

`default_port_start` int(10) NOT NULL COMMENT 'default private port range start',

`default_port_end` int(10) NOT NULL COMMENT 'default destination port range end',

`algorithm` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `fk_load_balancing_rules__id` FOREIGN KEY(`id`) REFERENCES `firewall_rules`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`load_balancer_vm_map` (

`id` bigint unsigned NOT NULL auto_increment,

`load_balancer_id` bigint unsigned NOT NULL,

`instance_id` bigint unsigned NOT NULL,

`revoke` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 is when rule is set for Revoke',

PRIMARY KEY (`id`),

UNIQUE KEY (`load_balancer_id`, `instance_id`),

CONSTRAINT `fk_load_balancer_vm_map__load_balancer_id` FOREIGN KEY(`load_balancer_id`) REFERENCES `load_balancing_rules`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_load_balancer_vm_map__instance_id` FOREIGN KEY(`instance_id`) REFERENCES `vm_instance`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`load_balancer_stickiness_policies` (

`id` bigint unsigned NOT NULL auto_increment,

`uuid` varchar(40),

`load_balancer_id` bigint unsigned NOT NULL,

`name` varchar(255) NOT NULL,

`description` varchar(4096) NULL COMMENT 'description',

`method_name` varchar(255) NOT NULL,

`params` varchar(4096) NOT NULL,

`revoke` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 is when rule is set for Revoke',

PRIMARY KEY (`id`),

CONSTRAINT `fk_load_balancer_stickiness_policies__load_balancer_id` FOREIGN KEY(`load_balancer_id`) REFERENCES `load_balancing_rules`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`inline_load_balancer_nic_map` (

`id` bigint unsigned NOT NULL auto_increment,

`public_ip_address` char(40) NOT NULL,

`nic_id` bigint unsigned NULL COMMENT 'nic id',

PRIMARY KEY (`id`),

UNIQUE KEY (`nic_id`),

CONSTRAINT `fk_inline_load_balancer_nic_map__nic_id` FOREIGN KEY(`nic_id`) REFERENCES `nics`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`port_forwarding_rules` (

`id` bigint unsigned NOT NULL COMMENT 'id',

`instance_id` bigint unsigned NOT NULL COMMENT 'vm instance id',

`dest_ip_address` char(40) NOT NULL COMMENT 'id_address',

`dest_port_start` int(10) NOT NULL COMMENT 'starting port of the port range to map to',

`dest_port_end` int(10) NOT NULL COMMENT 'end port of the the port range to map to',

PRIMARY KEY (`id`),

CONSTRAINT `fk_port_forwarding_rules__id` FOREIGN KEY(`id`) REFERENCES `firewall_rules`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_port_forwarding_rules__instance_id` FOREIGN KEY `fk_port_forwarding_rules__instance_id` (`instance_id`) REFERENCES `vm_instance` (`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`host` (

`id` bigint unsigned NOT NULL auto_increment,

`name` varchar(255) NOT NULL,

`uuid` varchar(40) COMMENT 'this uuid is different with guid below, the later one is used by hypervisor resource',

`status` varchar(32) NOT NULL,

`type` varchar(32) NOT NULL,

`private_ip_address` char(40) NOT NULL,

`private_netmask` varchar(15),

`private_mac_address` varchar(17),

`storage_ip_address` char(40),

`storage_netmask` varchar(15),

`storage_mac_address` varchar(17),

`storage_ip_address_2` char(40),

`storage_mac_address_2` varchar(17),

`storage_netmask_2` varchar(15),

`cluster_id` bigint unsigned COMMENT 'foreign key to cluster',

`public_ip_address` char(40),

`public_netmask` varchar(15),

`public_mac_address` varchar(17),

`proxy_port` int(10) unsigned,

`data_center_id` bigint unsigned NOT NULL,

`pod_id` bigint unsigned,

`cpus` int(10) unsigned,

`speed` int(10) unsigned,

`url` varchar(255) COMMENT 'iqn for the servers',

`fs_type` varchar(32),

`hypervisor_type` varchar(32) COMMENT 'hypervisor type, can be NONE for storage',

`hypervisor_version` varchar(32) COMMENT 'hypervisor version',

`ram` bigint unsigned,

`resource` varchar(255) DEFAULT NULL COMMENT 'If it is a local resource, this is the class name',

`version` varchar(40) NOT NULL,

`parent` varchar(255) COMMENT 'parent path for the storage server',

`total_size` bigint unsigned COMMENT 'TotalSize',

`capabilities` varchar(255) COMMENT 'host capabilities in comma separated list',

`guid` varchar(255) UNIQUE,

`available` int(1) unsigned NOT NULL DEFAULT 1 COMMENT 'Is this host ready for more resources?',

`setup` int(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Is this host already setup?',

`dom0_memory` bigint unsigned NOT NULL COMMENT 'memory used by dom0 for computing and routing servers',

`last_ping` int(10) unsigned NOT NULL COMMENT 'time in seconds from the start of machine of the last ping',

`mgmt_server_id` bigint unsigned COMMENT 'ManagementServer this host is connected to.',

`disconnected` datetime COMMENT 'Time this was disconnected',

`created` datetime COMMENT 'date the host first signed on',

`removed` datetime COMMENT 'date removed if not null',

`update_count` bigint unsigned NOT NULL DEFAULT 0 COMMENT 'atomic increase count making status update operation atomical',

`resource_state` varchar(32) NOT NULL DEFAULT 'Enabled' COMMENT 'Is this host enabled for allocation for new resources',

PRIMARY KEY (`id`),

INDEX `i_host__removed`(`removed`),

INDEX `i_host__last_ping`(`last_ping`),

INDEX `i_host__status`(`status`),

INDEX `i_host__data_center_id`(`data_center_id`),

CONSTRAINT `fk_host__pod_id` FOREIGN KEY (`pod_id`) REFERENCES `host_pod_ref` (`id`) ON DELETE CASCADE,

INDEX `i_host__pod_id`(`pod_id`),

CONSTRAINT `fk_host__cluster_id` FOREIGN KEY (`cluster_id`) REFERENCES `cloud`.`cluster`(`id`),

CONSTRAINT `uc_host__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`op_host` (

`id` bigint unsigned NOT NULL UNIQUE COMMENT 'host id',

`sequence` bigint unsigned DEFAULT 1 NOT NULL COMMENT 'sequence for the host communication',

PRIMARY KEY (`id`),

CONSTRAINT `fk_op_host__id` FOREIGN KEY (`id`) REFERENCES `host`(`id`) ON DELETE CASCADE

) ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`account_details` (

`id` bigint unsigned NOT NULL auto_increment,

`account_id` bigint unsigned NOT NULL COMMENT 'account id',

`name` varchar(255) NOT NULL,

`value` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `fk_account_details__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`) ON DELETE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`host_details` (

`id` bigint unsigned NOT NULL auto_increment,

`host_id` bigint unsigned NOT NULL COMMENT 'host id',

`name` varchar(255) NOT NULL,

`value` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES `host`(`id`) ON DELETE CASCADE,

CONSTRAINT UNIQUE KEY `uk_host_id_name` (`host_id`, `name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`mshost` (

`id` bigint unsigned NOT NULL auto_increment,

`msid` bigint unsigned NOT NULL UNIQUE COMMENT 'management server id derived from MAC address',

`runid` bigint NOT NULL DEFAULT 0 COMMENT 'run id, combined with msid to form a cluster session',

`name` varchar(255),

`state` varchar(10) NOT NULL DEFAULT 'Down',

`version` varchar(255),

`service_ip` char(40) NOT NULL,

`service_port` integer NOT NULL,

`last_update` DATETIME NULL COMMENT 'Last record update time',

`removed` datetime COMMENT 'date removed if not null',

`alert_count` integer NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

INDEX `i_mshost__removed`(`removed`),

INDEX `i_mshost__last_update`(`last_update`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`mshost_peer` (

`id` bigint unsigned NOT NULL auto_increment,

`owner_mshost` bigint unsigned NOT NULL,

`peer_mshost` bigint unsigned NOT NULL,

`peer_runid` bigint NOT NULL,

`peer_state` varchar(10) NOT NULL DEFAULT 'Down',

`last_update` DATETIME NULL COMMENT 'Last record update time',

PRIMARY KEY (`id`),

CONSTRAINT `fk_mshost_peer__owner_mshost` FOREIGN KEY (`owner_mshost`) REFERENCES `mshost`(`id`) ON DELETE CASCADE,

CONSTRAINT `fk_mshost_peer__peer_mshost` FOREIGN KEY (`peer_mshost`) REFERENCES `mshost`(`id`),

UNIQUE `i_mshost_peer__owner_peer_runid`(`owner_mshost`, `peer_mshost`, `peer_runid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`host_tags` (

`id` bigint unsigned NOT NULL auto_increment,

`host_id` bigint unsigned NOT NULL COMMENT 'host id',

`tag` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

CONSTRAINT `fk_host_tags__host_id` FOREIGN KEY (`host_id`) REFERENCES `host`(`id`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`user` (

`id` bigint unsigned NOT NULL auto_increment,

`uuid` varchar(40),

`username` varchar(255) NOT NULL,

`password` varchar(255) NOT NULL,

`account_id` bigint unsigned NOT NULL,

`firstname` varchar(255) default NULL,

`lastname` varchar(255) default NULL,

`email` varchar(255) default NULL,

`state` varchar(10) NOT NULL default 'enabled',

`api_key` varchar(255) default NULL,

`secret_key` varchar(255) default NULL,

`created` datetime NOT NULL COMMENT 'date created',

`removed` datetime COMMENT 'date removed',

`timezone` varchar(30) default NULL,

`registration_token` varchar(255) default NULL,

`is_registered` tinyint NOT NULL DEFAULT 0 COMMENT '1: yes, 0: no',

`region_id` int unsigned NOT NULL,

`incorrect_login_attempts` integer unsigned NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

INDEX `i_user__removed`(`removed`),

INDEX `i_user__secret_key_removed`(`secret_key`, `removed`),

UNIQUE `i_user__api_key`(`api_key`),

CONSTRAINT `fk_user__account_id` FOREIGN KEY `fk_user__account_id` (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,

INDEX `i_user__account_id`(`account_id`),

CONSTRAINT `uc_user__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`event` (

`id` bigint unsigned NOT NULL auto_increment,

`uuid` varchar(40),

`type` varchar(32) NOT NULL,

`state` varchar(32) NOT NULL DEFAULT 'Completed',

`description` varchar(1024) NOT NULL,

`user_id` bigint unsigned NOT NULL,

`account_id` bigint unsigned NOT NULL,

`domain_id` bigint unsigned NOT NULL,

`created` datetime NOT NULL,

`level` varchar(16) NOT NULL,

`start_id` bigint unsigned NOT NULL DEFAULT 0,

`parameters` varchar(1024) NULL,

PRIMARY KEY (`id`),

CONSTRAINT `uc_event__uuid` UNIQUE (`uuid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cloud`.`user_ip_address` (

`id` bigint unsigned NOT NULL UNIQUE auto_increment,

`uuid` varchar(40),

`account_id` bigint unsigned NULL,

`domain_id` bigint unsigned NULL,

`public_ip_address` char(40) NOT NULL,

`data_center_id` bigint unsigned NOT NULL COMMENT 'zone that it belongs to',

`source_nat` int(1) unsigned NOT NULL default '0',

`allocated` datetime NULL COMMENT 'Date this ip was allocated to someone',

`vlan_db_id` bigint unsigned NOT NULL,

`one_to_one_nat` int(1) unsigned NOT NULL default '0',

`vm_id` bigint unsigned COMMENT 'vm id the one_to_one nat ip is assigned to',

`state` char(32) NOT NULL default 'Free' COMMENT 'state of the ip address',

`mac_address` bigint unsigned NOT NULL COMMENT 'mac address of this ip',

`source_network_id` bigint unsigned NOT NULL COMMENT 'network id ip belongs to',

`network_id` bigint unsigned COMMENT 'network this public ip address is associated with',

`physical_network_id` bigint unsigned NOT NULL COMMENT 'physical network id that this configuration is based on',

`is_system` int(1) unsigned NOT NULL default '0',

`vpc_id` bigint unsigned COMMENT 'vpc the ip address is associated with',

PRIMARY KEY (`id`),