CLOUDSTACK-4716: upgrade resource count (cpu,memory,primary_storage,secondary_storage) in the upgrade to 4.2
Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/38bbfdc8 Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/38bbfdc8 Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/38bbfdc8 Branch: refs/heads/4.2 Commit: 38bbfdc89a50bbb9464700d202d1cfa7b7955953 Parents: 0363b4a Author: Wei Zhou <w.z...@leaseweb.com> Authored: Mon Sep 23 14:13:31 2013 +0200 Committer: Wei Zhou <w.z...@leaseweb.com> Committed: Mon Sep 23 14:14:01 2013 +0200 ---------------------------------------------------------------------- .../com/cloud/upgrade/dao/Upgrade410to420.java | 149 +++++++++++++++++++ 1 file changed, 149 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cloudstack/blob/38bbfdc8/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java index 646b406..cd4a959 100755 --- a/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java @@ -115,6 +115,7 @@ public class Upgrade410to420 implements DbUpgrade { migrateVolumeOnSecondaryStorage(conn); createFullCloneFlag(conn); upgradeVpcServiceMap(conn); + upgradeResourceCount(conn); } private void createFullCloneFlag(Connection conn) { @@ -3012,4 +3013,152 @@ public class Upgrade410to420 implements DbUpgrade { } } } + + private void upgradeResourceCount(Connection conn) { + s_logger.debug("upgradeResourceCount start"); + PreparedStatement pstmt1 = null; + PreparedStatement pstmt2 = null; + PreparedStatement pstmt3 = null; + PreparedStatement pstmt4 = null; + PreparedStatement pstmt5 = null; + ResultSet rs = null; + ResultSet rsAccount = null; + ResultSet rsCount = null; + try { + pstmt1 = conn.prepareStatement("select id, domain_id FROM `cloud`.`account` where removed is NULL "); + rsAccount = pstmt1.executeQuery(); + while (rsAccount.next()) { + long account_id = rsAccount.getLong(1); + long domain_id = rsAccount.getLong(2); + // 1. update cpu,memory for all accounts + pstmt2 = conn.prepareStatement( "SELECT SUM(service_offering.cpu), SUM(service_offering.ram_size)" + + " FROM `cloud`.`vm_instance`, `cloud`.`service_offering`" + + " WHERE vm_instance.service_offering_id = service_offering.id AND vm_instance.account_id = ?" + " AND vm_instance.removed is NULL" + + " AND vm_instance.vm_type='User' AND state not in ('Destroyed', 'Error', 'Expunging')"); + pstmt2.setLong(1, account_id); + rsCount = pstmt2.executeQuery(); + if (rsCount.next()) { + upgradeResourceCountforAccount(conn, account_id, domain_id, "cpu", rsCount.getLong(1)); + upgradeResourceCountforAccount(conn, account_id, domain_id, "memory", rsCount.getLong(2)); + } else { + upgradeResourceCountforAccount(conn, account_id, domain_id, "cpu", 0L); + upgradeResourceCountforAccount(conn, account_id, domain_id, "memory", 0L); + } + // 2. update primary_storage for all accounts + pstmt3 = conn.prepareStatement("SELECT sum(size) FROM `cloud`.`volumes` WHERE account_id= ?" + + " AND (path is not NULL OR state in ('Allocated')) AND removed is NULL" + + " AND instance_id IN (SELECT id FROM `cloud`.`vm_instance` WHERE vm_type='User')"); + pstmt3.setLong(1, account_id); + rsCount = pstmt3.executeQuery(); + if (rsCount.next()) { + upgradeResourceCountforAccount(conn, account_id, domain_id, "primary_storage", rsCount.getLong(1)); + } else { + upgradeResourceCountforAccount(conn, account_id, domain_id, "primary_storage", 0L); + } + // 3. update secondary_storage for all accounts + long totalVolumesSize = 0; + long totalSnapshotsSize = 0; + long totalTemplatesSize = 0; + pstmt4 = conn.prepareStatement("SELECT sum(size) FROM `cloud`.`volumes` WHERE account_id= ?" + + " AND path is NULL AND state not in ('Allocated') AND removed is NULL"); + pstmt4.setLong(1, account_id); + rsCount = pstmt4.executeQuery(); + if (rsCount.next()) { + totalVolumesSize = rsCount.getLong(1); + } + pstmt4 = conn.prepareStatement("SELECT sum(size) FROM `cloud`.`snapshots` WHERE account_id= ? AND removed is NULL"); + pstmt4.setLong(1, account_id); + rsCount = pstmt4.executeQuery(); + if (rsCount.next()) { + totalSnapshotsSize = rsCount.getLong(1); + } + pstmt4 = conn.prepareStatement("SELECT sum(template_store_ref.size) FROM `cloud`.`template_store_ref`,`cloud`.`vm_template` WHERE account_id = ?" + + " AND template_store_ref.template_id = vm_template.id AND download_state = 'DOWNLOADED' AND destroyed = false AND removed is NULL"); + pstmt4.setLong(1, account_id); + rsCount = pstmt4.executeQuery(); + if (rsCount.next()) { + totalTemplatesSize = rsCount.getLong(1); + } + upgradeResourceCountforAccount(conn, account_id, domain_id, "secondary_storage", totalVolumesSize + totalSnapshotsSize + totalTemplatesSize); + } + // 4. upgrade cpu,memory,primary_storage,secondary_storage for domains + String resource_types[] = {"cpu","memory", "primary_storage", "secondary_storage"}; + pstmt5 = conn.prepareStatement("select id FROM `cloud`.`domain`"); + rsAccount = pstmt5.executeQuery(); + while (rsAccount.next()) { + long domain_id = rsAccount.getLong(1); + for(int count=0; count < resource_types.length; count++) { + String resource_type = resource_types[count]; + upgradeResourceCountforDomain(conn, domain_id, resource_type, 0L); // reset value to 0 before statistics + } + } + for(int count= 0; count < resource_types.length; count++) { + String resource_type = resource_types[count]; + pstmt5 = conn.prepareStatement("select account.domain_id,sum(resource_count.count) from `cloud`.`account` left join `cloud`.`resource_count` on account.id=resource_count.account_id " + + "where resource_count.type=? group by account.domain_id;"); + pstmt5.setString(1, resource_type); + rsCount = pstmt5.executeQuery(); + while (rsCount.next()) { + long domain_id = rsCount.getLong(1); + long resource_count = rsCount.getLong(2); + upgradeResourceCountforDomain(conn, domain_id, resource_type, resource_count); + } + } + s_logger.debug("upgradeResourceCount finish"); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to upgrade resource count (cpu,memory,primary_storage,secondary_storage) ", e); + } finally { + try { + if (rs != null) { + rs.close(); + } + if (rsAccount != null) { + rsAccount.close(); + } + if (rsCount != null) { + rsCount.close(); + } + if (pstmt1 != null) { + pstmt1.close(); + } + if (pstmt2 != null) { + pstmt2.close(); + } + if (pstmt3 != null) { + pstmt3.close(); + } + if (pstmt4 != null) { + pstmt4.close(); + } + if (pstmt5 != null) { + pstmt5.close(); + } + } catch (SQLException e) { + } + } + } + + private static void upgradeResourceCountforAccount(Connection conn, Long account_id, Long domain_id, String type, Long resource_count) throws SQLException { + //update or insert into resource_count table. + PreparedStatement pstmt = null; + pstmt = conn.prepareStatement("INSERT INTO `cloud`.`resource_count` (account_id, type, count) VALUES (?,?,?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), count=?"); + pstmt.setLong(1, account_id); + pstmt.setString(2, type); + pstmt.setLong(3, resource_count); + pstmt.setLong(4, resource_count); + pstmt.executeUpdate(); + pstmt.close(); + } + + private static void upgradeResourceCountforDomain(Connection conn, Long domain_id, String type, Long resource_count) throws SQLException { + //update or insert into resource_count table. + PreparedStatement pstmt = null; + pstmt = conn.prepareStatement("INSERT INTO `cloud`.`resource_count` (domain_id, type, count) VALUES (?,?,?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), count=?"); + pstmt.setLong(1, domain_id); + pstmt.setString(2, type); + pstmt.setLong(3, resource_count); + pstmt.setLong(4, resource_count); + pstmt.executeUpdate(); + pstmt.close(); + } }
