http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to224DomainVlans.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to224DomainVlans.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to224DomainVlans.java new file mode 100644 index 0000000..e46b3f5 --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to224DomainVlans.java @@ -0,0 +1,140 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.HashMap; + +import org.apache.log4j.Logger; + +import com.cloud.utils.exception.CloudRuntimeException; + +public class Upgrade218to224DomainVlans implements DbUpgrade { + final static Logger s_logger = Logger.getLogger(Upgrade218to224DomainVlans.class); + + @Override + public File[] getPrepareScripts() { + return null; + } + + @Override + public void performDataMigration(Connection conn) { + HashMap<Long, Long> networkDomainMap = new HashMap<Long, Long>(); + // populate domain_network_ref table + try { + PreparedStatement pstmt = conn.prepareStatement("SELECT id FROM networks WHERE shared=1 AND traffic_type='Guest' AND guest_type='Direct'"); + ResultSet rs = pstmt.executeQuery(); + s_logger.debug("query is " + pstmt); + while (rs.next()) { + Long networkId = rs.getLong(1); + Long vlanId = null; + Long domainId = null; + + pstmt = conn.prepareStatement("SELECT id FROM vlan WHERE network_id=? LIMIT 0,1"); + pstmt.setLong(1, networkId); + s_logger.debug("query is " + pstmt); + rs = pstmt.executeQuery(); + + while (rs.next()) { + vlanId = rs.getLong(1); + } + + if (vlanId != null) { + pstmt = conn.prepareStatement("SELECT domain_id FROM account_vlan_map WHERE domain_id IS NOT NULL AND vlan_db_id=? LIMIT 0,1"); + pstmt.setLong(1, vlanId); + s_logger.debug("query is " + pstmt); + rs = pstmt.executeQuery(); + + while (rs.next()) { + domainId = rs.getLong(1); + } + + if (domainId != null) { + if (!networkDomainMap.containsKey(networkId)) { + networkDomainMap.put(networkId, domainId); + } + } + } + } + + // populate domain level networks + for (Long networkId : networkDomainMap.keySet()) { + pstmt = conn.prepareStatement("INSERT INTO domain_network_ref (network_id, domain_id) VALUES (?, ?)"); + pstmt.setLong(1, networkId); + pstmt.setLong(2, networkDomainMap.get(networkId)); + pstmt.executeUpdate(); + } + + rs.close(); + pstmt.close(); + + performDbCleanup(conn); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to convert 2.1.x domain level vlans to 2.2.x domain level networks", e); + } + } + + @Override + public File[] getCleanupScripts() { + return null; + } + + @Override + public String[] getUpgradableVersionRange() { + return new String[] { "2.1.8", "2.1.8" }; + } + + @Override + public String getUpgradedVersion() { + return "2.2.4"; + } + + @Override + public boolean supportsRollingUpgrade() { + return false; + } + + private void performDbCleanup(Connection conn) { + try { + PreparedStatement pstmt = conn.prepareStatement("SELECT domain_id FROM account_vlan_map"); + try { + pstmt.executeQuery(); + } catch (SQLException e) { + s_logger.debug("Assuming that domain_id field doesn't exist in account_vlan_map table, no need to upgrade"); + return; + } + + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`account_vlan_map` DROP FOREIGN KEY `fk_account_vlan_map__domain_id`"); + pstmt.executeUpdate(); + + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`account_vlan_map` DROP COLUMN `domain_id`"); + pstmt.executeUpdate(); + + pstmt = conn.prepareStatement("DELETE FROM `cloud`.`account_vlan_map` WHERE account_id IS NULL"); + pstmt.executeUpdate(); + + pstmt.close(); + + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to delete domain_id field from account_vlan_map table due to:", e); + } + } +}
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22Premium.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22Premium.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22Premium.java new file mode 100644 index 0000000..61eee95 --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22Premium.java @@ -0,0 +1,100 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; + +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; + +public class Upgrade218to22Premium extends Upgrade218to22 { + @Override + public File[] getPrepareScripts() { + File[] scripts = super.getPrepareScripts(); + File[] newScripts = new File[2]; + newScripts[0] = scripts[0]; + + String file = Script.findScript("","db/schema-21to22-premium.sql"); + if (file == null) { + throw new CloudRuntimeException("Unable to find the upgrade script, schema-21to22-premium.sql"); + } + + newScripts[1] = new File(file); + + return newScripts; + } + + @Override + public void performDataMigration(Connection conn) { + super.performDataMigration(conn); + updateUserStats(conn); + updateUsageIpAddress(conn); + } + + @Override + public File[] getCleanupScripts() { + File[] scripts = super.getCleanupScripts(); + File[] newScripts = new File[1]; + // Change the array to 2 when you add in the scripts for premium. + newScripts[0] = scripts[0]; + return newScripts; + } + + private void updateUserStats(Connection conn) { + try { + + // update device_id information + PreparedStatement pstmt = conn.prepareStatement("update cloud_usage.user_statistics uus set device_id = " + + "(select device_id from cloud.user_statistics us where uus.id = us.id)"); + pstmt.executeUpdate(); + pstmt.close(); + + s_logger.debug("Upgraded cloud_usage user_statistics with deviceId"); + + // update host_id information in usage_network + PreparedStatement pstmt1 = conn.prepareStatement("update cloud_usage.usage_network un set host_id = " + + "(select device_id from cloud_usage.user_statistics us where us.account_id = un.account_id and us.data_center_id = un.zone_id)"); + pstmt1.executeUpdate(); + pstmt1.close(); + + s_logger.debug("Upgraded cloud_usage usage_network with hostId"); + + + } catch (Exception e) { + throw new CloudRuntimeException("Failed to upgrade user stats: ", e); + } + } + + private void updateUsageIpAddress(Connection conn) { + try { + + // update id information + PreparedStatement pstmt = conn.prepareStatement("update cloud_usage.usage_ip_address uip set id = " + + "(select id from cloud.user_ip_address ip where uip.public_ip_address = ip.public_ip_address and ip.data_center_id = uip.zone_id)"); + pstmt.executeUpdate(); + pstmt.close(); + + s_logger.debug("Upgraded cloud_usage usage_ip_address with Id"); + + } catch (Exception e) { + throw new CloudRuntimeException("Failed to upgrade usage_ip_address: ", e); + } + } + +} http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade2210to2211.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade2210to2211.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2210to2211.java new file mode 100644 index 0000000..186caf2 --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2210to2211.java @@ -0,0 +1,64 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; + +import org.apache.log4j.Logger; + +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; + +public class Upgrade2210to2211 implements DbUpgrade { + final static Logger s_logger = Logger.getLogger(Upgrade2210to2211.class); + + @Override + public String[] getUpgradableVersionRange() { + return new String[] { "2.2.10", "2.2.10"}; + } + + @Override + public String getUpgradedVersion() { + return "2.2.11"; + } + + @Override + public boolean supportsRollingUpgrade() { + return true; + } + + @Override + public File[] getPrepareScripts() { + String script = Script.findScript("", "db/schema-2210to2211.sql"); + if (script == null) { + throw new CloudRuntimeException("Unable to find db/schema-2210to2211.sql"); + } + + return new File[] { new File(script) }; + } + + @Override + public void performDataMigration(Connection conn) { + } + + @Override + public File[] getCleanupScripts() { + return null; + } + +} http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212.java new file mode 100644 index 0000000..03ff1f1 --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212.java @@ -0,0 +1,139 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; + +import org.apache.log4j.Logger; + +import com.cloud.configuration.Resource; +import com.cloud.configuration.Resource.ResourceType; +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; + +public class Upgrade2211to2212 implements DbUpgrade { + final static Logger s_logger = Logger.getLogger(Upgrade2211to2212.class); + + @Override + public String[] getUpgradableVersionRange() { + return new String[] { "2.2.11", "2.2.11"}; + } + + @Override + public String getUpgradedVersion() { + return "2.2.12"; + } + + @Override + public boolean supportsRollingUpgrade() { + return true; + } + + @Override + public File[] getPrepareScripts() { + String script = Script.findScript("", "db/schema-2211to2212.sql"); + if (script == null) { + throw new CloudRuntimeException("Unable to find db/schema-2211to2212.sql"); + } + + return new File[] { new File(script) }; + } + + @Override + public void performDataMigration(Connection conn) { + createResourceCount(conn); + } + + @Override + public File[] getCleanupScripts() { + return null; + } + + + private void createResourceCount(Connection conn) { + s_logger.debug("Creating missing resource_count records as a part of 2.2.11-2.2.12 upgrade"); + try { + + //Get all non removed accounts + List<Long> accounts = new ArrayList<Long>(); + PreparedStatement pstmt = conn.prepareStatement("SELECT id FROM account"); + ResultSet rs = pstmt.executeQuery(); + while (rs.next()) { + accounts.add(rs.getLong(1)); + } + rs.close(); + + + //get all non removed domains + List<Long> domains = new ArrayList<Long>(); + pstmt = conn.prepareStatement("SELECT id FROM domain"); + rs = pstmt.executeQuery(); + while (rs.next()) { + domains.add(rs.getLong(1)); + } + rs.close(); + + //2.2.12 resource types + String[] resourceTypes = {"user_vm", "public_ip", "volume", "snapshot", "template"}; + + for (Long accountId : accounts) { + for (String resourceType : resourceTypes) { + pstmt = conn.prepareStatement("SELECT * FROM resource_count WHERE type=? and account_id=?"); + pstmt.setString(1, resourceType); + pstmt.setLong(2, accountId); + rs = pstmt.executeQuery(); + if (!rs.next()) { + s_logger.debug("Inserting resource_count record of type " + resourceType + " for account id=" + accountId); + pstmt = conn.prepareStatement("INSERT INTO resource_count (account_id, domain_id, type, count) VALUES (?, null, ?, 0)"); + pstmt.setLong(1, accountId); + pstmt.setString(2, resourceType); + pstmt.executeUpdate(); + } + rs.close(); + } + pstmt.close(); + } + + for (Long domainId : domains) { + for (String resourceType : resourceTypes) { + pstmt = conn.prepareStatement("SELECT * FROM resource_count WHERE type=? and domain_id=?"); + pstmt.setString(1, resourceType); + pstmt.setLong(2, domainId); + rs = pstmt.executeQuery(); + if (!rs.next()) { + s_logger.debug("Inserting resource_count record of type " + resourceType + " for domain id=" + domainId); + pstmt = conn.prepareStatement("INSERT INTO resource_count (account_id, domain_id, type, count) VALUES (null, ?, ?, 0)"); + pstmt.setLong(1, domainId); + pstmt.setString(2, resourceType); + pstmt.executeUpdate(); + } + rs.close(); + } + pstmt.close(); + } + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to create default security groups for existing accounts due to", e); + } + } + +} http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212Premium.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212Premium.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212Premium.java new file mode 100644 index 0000000..dd51d9d --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2211to2212Premium.java @@ -0,0 +1,55 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; + +import org.apache.log4j.Logger; + +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; + +public class Upgrade2211to2212Premium extends Upgrade2211to2212 { + final static Logger s_logger = Logger.getLogger(Upgrade2211to2212Premium.class); + + @Override + public File[] getPrepareScripts() { + File[] scripts = super.getPrepareScripts(); + File[] newScripts = new File[2]; + newScripts[0] = scripts[0]; + + String file = Script.findScript("","db/schema-2211to2212-premium.sql"); + if (file == null) { + throw new CloudRuntimeException("Unable to find the upgrade script, schema-2211to2212-premium.sql"); + } + + newScripts[1] = new File(file); + + return newScripts; + } + + @Override + public void performDataMigration(Connection conn) { + super.performDataMigration(conn); + } + + @Override + public File[] getCleanupScripts() { + return null; + } +} http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade2212to2213.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade2212to2213.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2212to2213.java new file mode 100644 index 0000000..744bada --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2212to2213.java @@ -0,0 +1,104 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; + +import org.apache.log4j.Logger; + +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; + +public class Upgrade2212to2213 implements DbUpgrade { + final static Logger s_logger = Logger.getLogger(Upgrade2212to2213.class); + + @Override + public String[] getUpgradableVersionRange() { + return new String[] { "2.2.12", "2.2.13"}; + } + + @Override + public String getUpgradedVersion() { + return "2.2.13"; + } + + @Override + public boolean supportsRollingUpgrade() { + return true; + } + + @Override + public File[] getPrepareScripts() { + String script = Script.findScript("", "db/schema-2212to2213.sql"); + if (script == null) { + throw new CloudRuntimeException("Unable to find db/schema-2212to2213.sql"); + } + + return new File[] { new File(script) }; + } + + @Override + public void performDataMigration(Connection conn) { + fixForeignKeys(conn); + } + + @Override + public File[] getCleanupScripts() { + return null; + } + + + private void fixForeignKeys(Connection conn) { + HashMap<String, List<String>> foreignKeys = new HashMap<String, List<String>>(); + List<String> keys = new ArrayList<String>(); + keys.add("fk_networks__data_center_id"); + foreignKeys.put("networks", keys); + + // drop all foreign keys + s_logger.debug("Dropping old key fk_networks__data_center_id..."); + for (String tableName : foreignKeys.keySet()) { + DbUpgradeUtils.dropKeysIfExist(conn, tableName, foreignKeys.get(tableName), true); + } + + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`networks` ADD CONSTRAINT `fk_networks__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to reinsert data center key for the network", e); + } + + + // drop primary keys + DbUpgradeUtils.dropPrimaryKeyIfExists(conn, "cloud_usage.usage_load_balancer_policy"); + DbUpgradeUtils.dropPrimaryKeyIfExists(conn, "cloud_usage.usage_port_forwarding"); + + //Drop usage_network_offering unique key + try { + PreparedStatement pstmt = conn.prepareStatement("drop index network_offering_id on cloud_usage.usage_network_offering"); + pstmt.executeUpdate(); + s_logger.debug("Dropped usage_network_offering unique key"); + } catch (Exception e) { + // Ignore error if the usage_network_offering table or the unique key doesn't exist + } + } +} http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade2213to2214.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade2213to2214.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2213to2214.java new file mode 100644 index 0000000..d3528e3 --- /dev/null +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade2213to2214.java @@ -0,0 +1,308 @@ +// 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. +package com.cloud.upgrade.dao; + +import java.io.File; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; + +import org.apache.log4j.Logger; + +import com.cloud.utils.exception.CloudRuntimeException; +import com.cloud.utils.script.Script; +import com.cloud.vm.ConsoleProxyVO; + +public class Upgrade2213to2214 implements DbUpgrade { + final static Logger s_logger = Logger.getLogger(Upgrade2213to2214.class); + + + @Override + public String[] getUpgradableVersionRange() { + return new String[] { "2.2.13", "2.2.14"}; + } + + @Override + public String getUpgradedVersion() { + return "2.2.14"; + } + + @Override + public boolean supportsRollingUpgrade() { + return true; + } + + + @Override + public File[] getPrepareScripts() { + String script = Script.findScript("", "db/schema-2213to2214.sql"); + if (script == null) { + throw new CloudRuntimeException("Unable to find db/schema-2213to2214.sql"); + } + + return new File[] { new File(script) }; + } + + private void upgradeCerts(Connection conn) { + PreparedStatement pstmt; + try { + pstmt = conn.prepareStatement("select md5(`cloud`.`keystore`.key) from `cloud`.`keystore` where name = 'CPVMCertificate'"); + ResultSet rs = pstmt.executeQuery(); + while (rs.next()) { + String privateKeyMd5 = rs.getString(1); + if (privateKeyMd5.equalsIgnoreCase("432ea1370f57ccd774f4f36052c5fd73")) { + s_logger.debug("Need to upgrade cloudstack provided certificate"); + pstmt = conn.prepareStatement("update `cloud`.`keystore` set `cloud`.`keystore`.key = ?, certificate = ? where name = 'CPVMCertificate'"); + pstmt.setString(1, ConsoleProxyVO.keyContent); + pstmt.setString(2, ConsoleProxyVO.certContent); + pstmt.executeUpdate(); + + pstmt = conn.prepareStatement("insert into `cloud`.`keystore` (name, certificate, seq, domain_suffix) VALUES (?,?,?,?)"); + pstmt.setString(1, "root"); + pstmt.setString(2, ConsoleProxyVO.rootCa); + pstmt.setInt(3, 0); + pstmt.setString(4, "realhostip.com"); + pstmt.executeUpdate(); + } + } + rs.close(); + pstmt.close(); + } catch (SQLException e) { + s_logger.debug("Failed to upgrade keystore: " + e.toString()); + } + + } + + @Override + public void performDataMigration(Connection conn) { + fixIndexes(conn); + upgradeCerts(conn); + } + + @Override + public File[] getCleanupScripts() { + return null; + } + + + private void fixIndexes(Connection conn) { + //Drop i_usage_event__created key (if exists) and re-add it again + List<String> keys = new ArrayList<String>(); + keys.add("i_usage_event__created"); + DbUpgradeUtils.dropKeysIfExist(conn, "usage_event", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`usage_event` ADD INDEX `i_usage_event__created`(`created`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to execute usage_event table update", e); + } + + //In cloud_usage DB, drop i_usage_event__created key (if exists) and re-add it again + keys = new ArrayList<String>(); + keys.add("i_usage_event__created"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud_usage.usage_event", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud_usage`.`usage_event` ADD INDEX `i_usage_event__created`(`created`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to execute cloud_usage usage_event table update", e); + } + + //Drop netapp_volume primary key and add it again + DbUpgradeUtils.dropPrimaryKeyIfExists(conn, "cloud.netapp_volume"); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`netapp_volume` add PRIMARY KEY (`id`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to update primary key for netapp_volume", e); + } + + //Drop i_snapshots__removed key (if exists) and re-add it again + keys = new ArrayList<String>(); + keys.add("i_snapshots__removed"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.snapshots", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`snapshots` ADD INDEX `i_snapshots__removed`(`removed`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert index for removed column in snapshots", e); + } + //Drop i_op_vm_ruleset_log__instance_id, u_op_vm_ruleset_log__instance_id key (if exists) and re-add u_op_vm_ruleset_log__instance_id again + keys = new ArrayList<String>(); + keys.add("i_op_vm_ruleset_log__instance_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.op_vm_ruleset_log", keys, false); + + keys = new ArrayList<String>(); + keys.add("u_op_vm_ruleset_log__instance_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.op_vm_ruleset_log", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`op_vm_ruleset_log` ADD CONSTRAINT `u_op_vm_ruleset_log__instance_id` UNIQUE (`instance_id`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to execute changes for op_vm_ruleset_log", e); + } + + + //Drop i_async__removed, i_async_job__removed (if exists) and add i_async_job__removed + keys = new ArrayList<String>(); + keys.add("i_async__removed"); + keys.add("i_async_job__removed"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.async_job", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`async_job` ADD INDEX `i_async_job__removed`(`removed`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert index for removed column in async_job", e); + } + + keys = new ArrayList<String>(); + keys.add("fk_ssh_keypair__account_id"); + keys.add("fk_ssh_keypair__domain_id"); + keys.add("fk_ssh_keypairs__account_id"); + keys.add("fk_ssh_keypairs__domain_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "ssh_keypairs", keys, true); + + keys = new ArrayList<String>(); + keys.add("fk_ssh_keypair__account_id"); + keys.add("fk_ssh_keypair__domain_id"); + keys.add("fk_ssh_keypairs__account_id"); + keys.add("fk_ssh_keypairs__domain_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "ssh_keypairs", keys, false); + + try { + PreparedStatement pstmt; pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`ssh_keypairs` ADD CONSTRAINT `fk_ssh_keypairs__account_id` FOREIGN KEY `fk_ssh_keypairs__account_id` (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to execute ssh_keypairs table update for adding account_id foreign key", e); + } + + try { + PreparedStatement pstmt; pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`ssh_keypairs` ADD CONSTRAINT `fk_ssh_keypairs__domain_id` FOREIGN KEY `fk_ssh_keypairs__domain_id` (`domain_id`) REFERENCES `domain` (`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to execute ssh_keypairs table update for adding domain_id foreign key", e); + } + + //Drop i_async__removed, i_async_job__removed (if exists) and add i_async_job__removed + keys = new ArrayList<String>(); + keys.add("i_async__removed"); + keys.add("i_async_job__removed"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.async_job", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`async_job` ADD INDEX `i_async_job__removed`(`removed`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert index for removed column in async_job", e); + } + + //Drop storage pool details keys (if exists) and insert one with correct name + keys = new ArrayList<String>(); + keys.add("fk_storage_pool__pool_id"); + keys.add("fk_storage_pool_details__pool_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.storage_pool_details", keys, true); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.storage_pool_details", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`storage_pool_details` ADD CONSTRAINT `fk_storage_pool_details__pool_id` FOREIGN KEY `fk_storage_pool_details__pool_id`(`pool_id`) REFERENCES `storage_pool`(`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert foreign key in storage_pool_details ", e); + } + + //Drop securityGroup keys (if exists) and insert one with correct name + keys = new ArrayList<String>(); + keys.add("fk_security_group___account_id"); + keys.add("fk_security_group__account_id"); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.security_group", keys, true); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.security_group", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`security_group` ADD CONSTRAINT `fk_security_group__account_id` FOREIGN KEY `fk_security_group__account_id` (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert foreign key in security_group table ", e); + } + + //Drop vmInstance keys (if exists) and insert one with correct name + keys = new ArrayList<String>(); + keys.add("i_vm_instance__host_id"); + keys.add("fk_vm_instance__host_id"); + + keys.add("fk_vm_instance__last_host_id"); + keys.add("i_vm_instance__last_host_id"); + + keys.add("fk_vm_instance__service_offering_id"); + keys.add("i_vm_instance__service_offering_id"); + + keys.add("fk_vm_instance__account_id"); + keys.add("i_vm_instance__account_id"); + + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.vm_instance", keys, true); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.vm_instance", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`vm_instance` ADD CONSTRAINT `fk_vm_instance__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`)"); + pstmt.executeUpdate(); + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`vm_instance` ADD CONSTRAINT `fk_vm_instance__last_host_id` FOREIGN KEY (`last_host_id`) REFERENCES `host` (`id`)"); + pstmt.executeUpdate(); + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`vm_instance` ADD CONSTRAINT `fk_vm_instance__account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)"); + pstmt.executeUpdate(); + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`vm_instance` ADD CONSTRAINT `fk_vm_instance__service_offering_id` FOREIGN KEY (`service_offering_id`) REFERENCES `service_offering` (`id`)"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert foreign key in vm_instance table ", e); + } + + //Drop user_ip_address keys (if exists) and insert one with correct name + keys = new ArrayList<String>(); + keys.add("fk_user_ip_address__account_id"); + keys.add("i_user_ip_address__account_id"); + + keys.add("fk_user_ip_address__vlan_db_id"); + keys.add("i_user_ip_address__vlan_db_id"); + + keys.add("fk_user_ip_address__data_center_id"); + keys.add("i_user_ip_address__data_center_id"); + + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.user_ip_address", keys, true); + DbUpgradeUtils.dropKeysIfExist(conn, "cloud.user_ip_address", keys, false); + try { + PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`user_ip_address` ADD CONSTRAINT `fk_user_ip_address__account_id` FOREIGN KEY (`account_id`) REFERENCES `account`(`id`)"); + pstmt.executeUpdate(); + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`user_ip_address` ADD CONSTRAINT `fk_user_ip_address__vlan_db_id` FOREIGN KEY (`vlan_db_id`) REFERENCES `vlan`(`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`user_ip_address` ADD CONSTRAINT `fk_user_ip_address__data_center_id` FOREIGN KEY (`data_center_id`) REFERENCES `data_center`(`id`) ON DELETE CASCADE"); + pstmt.executeUpdate(); + pstmt.close(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to insert foreign key in vm_instance table ", e); + } + + } +}
