Skip to content

Commit 6e9ef0d

Browse files
author
kishan
committed
2.2beta4 to GA db changes
1 parent e901ccf commit 6e9ef0d

2 files changed

Lines changed: 47 additions & 6 deletions

File tree

‎setup/db/22beta4to22GA.sql‎

Lines changed: 46 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -63,9 +63,50 @@ ALTER TABLE `cloud`.`user_ip_address` ADD UNIQUE (source_network_id, public_ip_a
6363

6464

6565
--step 4 (independent of above)
66+
67+
ALTER TABLE `cloud`.`user_statistics` CHANGE `host_id` `device_id` bigint unsigned NOT NULL default 0;
68+
ALTER TABLE `cloud`.`user_statistics` ADD COLUMN `device_type` varchar(32) NOT NULL default 'DomainRouter';
69+
UPDATE `cloud`.`user_statistics` us,`cloud`.`host` h SET us.device_type = h.type where us.device_id = h.id AND us.device_id > 0;
70+
ALTER TABLE `cloud`.`user_statistics` ADD UNIQUE (`account_id`, `data_center_id`, `public_ip_address`, `device_id`, `device_type`);
71+
72+
ALTER TABLE `cloud`.`snapshots` modify `id` bigint unsigned UNIQUE NOT NULL AUTO_INCREMENT COMMENT 'Primary Key';
73+
6674
----------------------usage changes (for cloud_usage database)--------------------------------------------------------------------------------------------------------------
67-
ALTER TABLE `cloud_usage`.`user_statistics` DROP COLUMN host_id;
68-
ALTER TABLE `cloud_usage`.`user_statistics` DROP COLUMN host_type;
69-
ALTER TABLE `cloud_usage`.`user_statistics` ADD COLUMN `device_id` bigint unsigned NOT NULL;
70-
ALTER TABLE `cloud_usage`.`user_statistics` ADD COLUMN `device_type` varchar(32) NOT NULL;
71-
ALTER TABLE `cloud_usage`.`user_statistics` ADD UNIQUE (`account_id`, `data_center_id`, `device_id`, `device_type`);
75+
76+
ALTER TABLE `cloud_usage`.`user_statistics` ADD COLUMN `device_id` bigint unsigned NOT NULL default 0;
77+
ALTER TABLE `cloud_usage`.`user_statistics` ADD COLUMN `device_type` varchar(32) NOT NULL default 'DomainRouter';
78+
ALTER TABLE `cloud_usage`.`user_statistics` ADD COLUMN `public_ip_address` varchar(15);
79+
UPDATE `cloud_usage`.`user_statistics` cus, `cloud`.`user_statistics` us SET cus.device_id=us.device_id, cus.device_type=us.device_type, cus.public_ip_address=us.public_ip_address WHERE cus.id = us.id;
80+
ALTER TABLE `cloud_usage`.`user_statistics` ADD UNIQUE (`account_id`, `data_center_id`, `public_ip_address`, `device_id`, `device_type`);
81+
82+
INSERT INTO user_statistics ( account_id, data_center_id, device_id, device_type ) SELECT VM.account_id, VM.data_center_id, DR.id,'DomainRouter' FROM vm_instance VM, domain_router DR WHERE VM.id = DR.id;
83+
84+
ALTER TABLE `cloud_usage`.`usage_network` ADD COLUMN `host_id` bigint unsigned NOT NULL default 0;
85+
ALTER TABLE `cloud_usage`.`usage_network` ADD COLUMN `host_type` varchar(32);
86+
ALTER TABLE `cloud_usage`.`usage_network` drop PRIMARY KEY;
87+
ALTER TABLE `cloud_usage`.`usage_network` add PRIMARY KEY (`account_id`, `zone_id`, `host_id`, `event_time_millis`);
88+
89+
ALTER TABLE `cloud_usage`.`usage_ip_address` ADD COLUMN `id` bigint unsigned NOT NULL;
90+
ALTER TABLE `cloud_usage`.`usage_ip_address` ADD COLUMN `is_source_nat` smallint(1) NOT NULL;
91+
92+
ALTER TABLE `cloud_usage`.`cloud_usage` ADD COLUMN `type` varchar(32);
93+
94+
CREATE TABLE `cloud_usage`.`usage_port_forwarding` (
95+
`id` bigint unsigned NOT NULL,
96+
`zone_id` bigint unsigned NOT NULL,
97+
`account_id` bigint unsigned NOT NULL,
98+
`domain_id` bigint unsigned NOT NULL,
99+
`created` DATETIME NOT NULL,
100+
`deleted` DATETIME NULL
101+
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102+
103+
CREATE TABLE `cloud_usage`.`usage_network_offering` (
104+
`zone_id` bigint unsigned NOT NULL,
105+
`account_id` bigint unsigned NOT NULL,
106+
`domain_id` bigint unsigned NOT NULL,
107+
`vm_instance_id` bigint unsigned NOT NULL,
108+
`network_offering_id` bigint unsigned NOT NULL,
109+
`is_default` smallint(1) NOT NULL,
110+
`created` DATETIME NOT NULL,
111+
`deleted` DATETIME NULL
112+
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

‎setup/db/create-schema.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -678,7 +678,7 @@ CREATE TABLE `cloud`.`user_statistics` (
678678
`current_bytes_received` bigint unsigned NOT NULL default '0',
679679
`current_bytes_sent` bigint unsigned NOT NULL default '0',
680680
PRIMARY KEY (`id`),
681-
UNIQUE KEY (`account_id`, `data_center_id`, `device_id`, `device_type`)
681+
UNIQUE KEY (`account_id`, `data_center_id`, `public_ip_address`, `device_id`, `device_type`)
682682
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
683683

684684
CREATE TABLE `cloud`.`vm_template` (

0 commit comments

Comments
 (0)