Skip to content

Commit fe1113f

Browse files
xueyuanchipchilders
xueyuan
authored and
chipchilders
committed
Added fixes for CLOUDSTACK-1839. Missing lots of indexes due to a rollback to 4.0 by Rohit
1 parent a35f7c7 commit fe1113f

1 file changed

Lines changed: 69 additions & 13 deletions

File tree

‎setup/db/create-schema.sql‎

Lines changed: 69 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -206,7 +206,6 @@ CREATE TABLE `cloud`.`version` (
206206
INDEX `i_version__version`(`version`)
207207
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
208208

209-
210209
INSERT INTO `version` (`version`, `updated`, `step`) VALUES('4.0.0', now(), 'Complete');
211210

212211
CREATE TABLE `cloud`.`op_it_work` (
@@ -528,6 +527,12 @@ CREATE TABLE `cloud`.`snapshots` (
528527
`version` varchar(32) COMMENT 'snapshot version',
529528
PRIMARY KEY (`id`),
530529
CONSTRAINT `uc_snapshots__uuid` UNIQUE (`uuid`),
530+
CONSTRAINT `fk_snapshots__account_id` FOREIGN KEY(`account_id`) REFERENCES `account` (`id`),
531+
INDEX `i_snapshots__account_id`(`account_id`),
532+
INDEX `i_snapshots__volume_id`(`volume_id`),
533+
INDEX `i_snapshots__name`(`name`),
534+
INDEX `i_snapshots__snapshot_type`(`snapshot_type`),
535+
INDEX `i_snapshots__prev_snap_id`(`prev_snap_id`),
531536
INDEX `i_snapshots__removed`(`removed`)
532537
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
533538

@@ -965,6 +970,11 @@ CREATE TABLE `cloud`.`event` (
965970
`start_id` bigint unsigned NOT NULL DEFAULT 0,
966971
`parameters` varchar(1024) NULL,
967972
PRIMARY KEY (`id`),
973+
INDEX `i_event__created`(`created`),
974+
INDEX `i_event__user_id`(`user_id`),
975+
INDEX `i_event__account_id` (`account_id`),
976+
INDEX `i_event__level_id`(`level`),
977+
INDEX `i_event__type_id`(`type`),
968978
CONSTRAINT `uc_event__uuid` UNIQUE (`uuid`)
969979
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970980

@@ -1018,6 +1028,9 @@ CREATE TABLE `cloud`.`user_statistics` (
10181028
`agg_bytes_received` bigint unsigned NOT NULL default '0',
10191029
`agg_bytes_sent` bigint unsigned NOT NULL default '0',
10201030
PRIMARY KEY (`id`),
1031+
CONSTRAINT `fk_user_statistics__account_id` FOREIGN KEY `fk_user_statistics__account_id` (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
1032+
INDEX `i_user_statistics__account_id`(`account_id`),
1033+
INDEX `i_user_statistics__account_id_data_center_id`(`account_id`, `data_center_id`),
10211034
UNIQUE KEY (`account_id`, `data_center_id`, `public_ip_address`, `device_id`, `device_type`)
10221035
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10231036

@@ -1051,6 +1064,7 @@ CREATE TABLE `cloud`.`vm_template` (
10511064
`sort_key` int(32) NOT NULL default 0 COMMENT 'sort key used for customising sort method',
10521065
PRIMARY KEY (`id`),
10531066
INDEX `i_vm_template__removed`(`removed`),
1067+
INDEX `i_vm_template__public`(`public`),
10541068
CONSTRAINT `uc_vm_template__uuid` UNIQUE (`uuid`)
10551069
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10561070

@@ -1168,6 +1182,9 @@ CREATE TABLE `cloud`.`upload` (
11681182
`error_str` varchar(255),
11691183
`url` varchar(255),
11701184
`install_path` varchar(255),
1185+
CONSTRAINT `fk_upload__host_id` FOREIGN KEY(`host_id`) REFERENCES `host` (`id`) ON DELETE CASCADE,
1186+
INDEX `i_upload__host_id`(`host_id`),
1187+
INDEX `i_upload__type_id`(`type_id`),
11711188
PRIMARY KEY (`id`)
11721189
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
11731190

@@ -1311,6 +1328,10 @@ CREATE TABLE `cloud`.`account` (
13111328
PRIMARY KEY (`id`),
13121329
INDEX i_account__removed(`removed`),
13131330
CONSTRAINT `fk_account__default_zone_id` FOREIGN KEY `fk_account__default_zone_id`(`default_zone_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE,
1331+
INDEX `i_account__cleanup_needed`(`cleanup_needed`),
1332+
INDEX `i_account__account_name__domain_id__removed`(`account_name`, `domain_id`, `removed`),
1333+
CONSTRAINT `fk_account__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain` (`id`),
1334+
INDEX `i_account__domain_id`(`domain_id`),
13141335
CONSTRAINT `uc_account__uuid` UNIQUE (`uuid`)
13151336
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13161337

@@ -1320,6 +1341,10 @@ CREATE TABLE `cloud`.`resource_limit` (
13201341
`account_id` bigint unsigned,
13211342
`type` varchar(255),
13221343
`max` bigint NOT NULL default '-1',
1344+
CONSTRAINT `fk_resource_limit__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain` (`id`) ON DELETE CASCADE,
1345+
INDEX `i_resource_limit__domain_id`(`domain_id`),
1346+
CONSTRAINT `fk_resource_limit__account_id` FOREIGN KEY(`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
1347+
INDEX `i_resource_limit__account_id`(`account_id`),
13231348
PRIMARY KEY (`id`)
13241349
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13251350

@@ -1433,7 +1458,10 @@ CREATE TABLE `cloud`.`stack_maid` (
14331458
`cleanup_delegate` varchar(128),
14341459
`cleanup_context` text,
14351460
`created` datetime,
1436-
PRIMARY KEY (`id`)
1461+
PRIMARY KEY (`id`),
1462+
INDEX `i_stack_maid_msid_thread_id`(`msid`, `thread_id`),
1463+
INDEX `i_stack_maid_seq`(`msid`, `seq`),
1464+
INDEX `i_stack_maid_created`(`created`)
14371465
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
14381466

14391467
CREATE TABLE `cloud`.`sync_queue_item` (
@@ -1607,7 +1635,8 @@ CREATE TABLE `cloud`.`guest_os` (
16071635
`uuid` varchar(40),
16081636
`display_name` varchar(255) NOT NULL,
16091637
PRIMARY KEY (`id`),
1610-
CONSTRAINT `uc_guest_os__uuid` UNIQUE (`uuid`)
1638+
CONSTRAINT `uc_guest_os__uuid` UNIQUE (`uuid`),
1639+
CONSTRAINT `fk_guest_os__category_id` FOREIGN KEY(`category_id`) REFERENCES `guest_os_category` (`id`) ON DELETE CASCADE
16111640
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
16121641

16131642
CREATE TABLE `cloud`.`guest_os_hypervisor` (
@@ -1657,7 +1686,8 @@ CREATE TABLE `cloud`.`launch_permission` (
16571686
`id` bigint unsigned NOT NULL auto_increment,
16581687
`template_id` bigint unsigned NOT NULL,
16591688
`account_id` bigint unsigned NOT NULL,
1660-
PRIMARY KEY (`id`)
1689+
PRIMARY KEY (`id`),
1690+
INDEX `i_launch_permission_template_id`(`template_id`)
16611691
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
16621692

16631693
CREATE TABLE `cloud`.`snapshot_policy` (
@@ -1670,6 +1700,7 @@ CREATE TABLE `cloud`.`snapshot_policy` (
16701700
`max_snaps` int(8) NOT NULL default 0 COMMENT 'maximum number of snapshots to maintain',
16711701
`active` tinyint(1) unsigned NOT NULL COMMENT 'Is the policy active',
16721702
PRIMARY KEY (`id`),
1703+
INDEX `i_snapshot_policy__volume_id`(`volume_id`),
16731704
CONSTRAINT `uc_snapshot_policy__uuid` UNIQUE (`uuid`)
16741705
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16751706

@@ -1683,7 +1714,16 @@ CREATE TABLE `cloud`.`snapshot_schedule` (
16831714
`snapshot_id` bigint unsigned COMMENT 'If this schedule is being executed, then the corresponding snapshot has this id. Before that it is null',
16841715
UNIQUE (volume_id, policy_id),
16851716
CONSTRAINT `uc_snapshot_schedule__uuid` UNIQUE (`uuid`),
1686-
PRIMARY KEY (`id`)
1717+
PRIMARY KEY (`id`),
1718+
CONSTRAINT `fk__snapshot_schedule_volume_id` FOREIGN KEY(`volume_id`) REFERENCES `volumes` (`id`) ON DELETE CASCADE,
1719+
INDEX `i_snapshot_schedule__volume_id`(`volume_id`),
1720+
CONSTRAINT `fk__snapshot_schedule_policy_id` FOREIGN KEY(`policy_id`) REFERENCES `snapshot_policy` (`id`) ON DELETE CASCADE,
1721+
INDEX `i_snapshot_schedule__policy_id`(`policy_id`),
1722+
CONSTRAINT `fk__snapshot_schedule_async_job_id` FOREIGN KEY(`async_job_id`) REFERENCES `async_job` (`id`) ON DELETE CASCADE,
1723+
INDEX `i_snapshot_schedule__async_job_id`(`async_job_id`),
1724+
CONSTRAINT `fk__snapshot_schedule_snapshot_id` FOREIGN KEY(`snapshot_id`) REFERENCES `snapshots` (`id`) ON DELETE CASCADE,
1725+
INDEX `i_snapshot_schedule__snapshot_id`(`snapshot_id`),
1726+
INDEX `i_snapshot_schedule__scheduled_timestamp`(`scheduled_timestamp`)
16871727
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16881728

16891729
CREATE TABLE `cloud`.`op_pod_vlan_alloc` (
@@ -1705,7 +1745,10 @@ CREATE TABLE `cloud`.`security_group` (
17051745
`account_id` bigint unsigned NOT NULL,
17061746
UNIQUE (`name`, `account_id`),
17071747
PRIMARY KEY (`id`),
1708-
CONSTRAINT `uc_security_group__uuid` UNIQUE (`uuid`)
1748+
CONSTRAINT `uc_security_group__uuid` UNIQUE (`uuid`),
1749+
CONSTRAINT `fk_security_group__account_id` FOREIGN KEY `fk_security_group__account_id` (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
1750+
CONSTRAINT `fk_security_group__domain_id` FOREIGN KEY `fk_security_group__domain_id` (`domain_id`) REFERENCES `domain` (`id`),
1751+
INDEX `i_security_group_name`(`name`)
17091752
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17101753

17111754
CREATE TABLE `cloud`.`security_group_rule` (
@@ -1720,14 +1763,20 @@ CREATE TABLE `cloud`.`security_group_rule` (
17201763
`allowed_ip_cidr` varchar(44),
17211764
`create_status` varchar(32) COMMENT 'rule creation status',
17221765
PRIMARY KEY (`id`),
1723-
CONSTRAINT `uc_security_group_rule__uuid` UNIQUE (`uuid`)
1766+
CONSTRAINT `uc_security_group_rule__uuid` UNIQUE (`uuid`),
1767+
CONSTRAINT `fk_security_group_rule___security_group_id` FOREIGN KEY(`security_group_id`) REFERENCES `security_group` (`id`) ON DELETE CASCADE,
1768+
CONSTRAINT `fk_security_group_rule___allowed_network_id` FOREIGN KEY(`allowed_network_id`) REFERENCES `security_group` (`id`) ON DELETE CASCADE,
1769+
INDEX `i_security_group_rule_network_id`(`security_group_id`),
1770+
INDEX `i_security_group_rule_allowed_network`(`allowed_network_id`)
17241771
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17251772

17261773
CREATE TABLE `cloud`.`security_group_vm_map` (
17271774
`id` bigint unsigned NOT NULL auto_increment,
17281775
`security_group_id` bigint unsigned NOT NULL,
17291776
`instance_id` bigint unsigned NOT NULL,
1730-
PRIMARY KEY (`id`)
1777+
PRIMARY KEY (`id`),
1778+
CONSTRAINT `fk_security_group_vm_map___security_group_id` FOREIGN KEY(`security_group_id`) REFERENCES `security_group` (`id`) ON DELETE CASCADE,
1779+
CONSTRAINT `fk_security_group_vm_map___instance_id` FOREIGN KEY(`instance_id`) REFERENCES `user_vm` (`id`) ON DELETE CASCADE
17311780
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17321781

17331782
CREATE TABLE `cloud`.`op_nwgrp_work` (
@@ -1764,14 +1813,17 @@ CREATE TABLE `cloud`.`instance_group` (
17641813
`created` datetime COMMENT 'date the group was created',
17651814
PRIMARY KEY (`id`),
17661815
INDEX `i_instance_group__removed`(`removed`),
1767-
CONSTRAINT `uc_instance_group__uuid` UNIQUE (`uuid`)
1816+
CONSTRAINT `uc_instance_group__uuid` UNIQUE (`uuid`),
1817+
CONSTRAINT `fk_instance_group__account_id` FOREIGN KEY(`account_id`) REFERENCES `account` (`id`)
17681818
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
17691819

17701820
CREATE TABLE `cloud`.`instance_group_vm_map` (
17711821
`id` bigint unsigned NOT NULL auto_increment,
17721822
`group_id` bigint unsigned NOT NULL,
17731823
`instance_id` bigint unsigned NOT NULL,
1774-
PRIMARY KEY (`id`)
1824+
PRIMARY KEY (`id`),
1825+
CONSTRAINT `fk_instance_group_vm_map___group_id` FOREIGN KEY(`group_id`) REFERENCES `instance_group` (`id`) ON DELETE CASCADE,
1826+
CONSTRAINT `fk_instance_group_vm_map___instance_id` FOREIGN KEY(`instance_id`) REFERENCES `user_vm` (`id`) ON DELETE CASCADE
17751827
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17761828

17771829
CREATE TABLE `cloud`.`ssh_keypairs` (
@@ -1781,7 +1833,9 @@ CREATE TABLE `cloud`.`ssh_keypairs` (
17811833
`keypair_name` varchar(256) NOT NULL COMMENT 'name of the key pair',
17821834
`fingerprint` varchar(128) NOT NULL COMMENT 'fingerprint for the ssh public key',
17831835
`public_key` varchar(5120) NOT NULL COMMENT 'public key of the ssh key pair',
1784-
PRIMARY KEY (`id`)
1836+
PRIMARY KEY (`id`),
1837+
CONSTRAINT `fk_ssh_keypairs__account_id` FOREIGN KEY(`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
1838+
CONSTRAINT `fk_ssh_keypairs__domain_id` FOREIGN KEY(`domain_id`) REFERENCES `domain` (`id`) ON DELETE CASCADE
17851839
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17861840

17871841
CREATE TABLE `cloud`.`usage_event` (
@@ -1797,7 +1851,8 @@ CREATE TABLE `cloud`.`usage_event` (
17971851
`size` bigint unsigned,
17981852
`resource_type` varchar(32),
17991853
`processed` tinyint NOT NULL default '0',
1800-
PRIMARY KEY (`id`)
1854+
PRIMARY KEY (`id`),
1855+
INDEX `i_usage_event__created`(`created`)
18011856
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
18021857

18031858
CREATE TABLE `cloud`.`ovs_tunnel_interface` (
@@ -2416,7 +2471,8 @@ CREATE TABLE `cloud`.`nicira_nvp_nic_map` (
24162471
`logicalswitch` varchar(255) NOT NULL COMMENT 'nicira uuid of logical switch this port is provisioned on',
24172472
`logicalswitchport` varchar(255) UNIQUE COMMENT 'nicira uuid of this logical switch port',
24182473
`nic` varchar(255) UNIQUE COMMENT 'cloudstack uuid of the nic connected to this logical switch port',
2419-
PRIMARY KEY (`id`)
2474+
PRIMARY KEY (`id`),
2475+
CONSTRAINT `fk_nicira_nvp_nic_map__nic` FOREIGN KEY(`nic`) REFERENCES `nics`(`uuid`) ON DELETE CASCADE
24202476
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24212477

24222478
SET foreign_key_checks = 1;

0 commit comments

Comments
 (0)