cloudstack-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From prachida...@apache.org
Subject [31/91] [abbrv] [partial] Moved most of the VOs and DAOs from server package into engine-schema as well
Date Thu, 09 May 2013 00:20:42 GMT
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
new file mode 100644
index 0000000..2ef842a
--- /dev/null
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade218to22.java
@@ -0,0 +1,2382 @@
+// 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.io.IOException;
+import java.io.StringReader;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+import java.util.TimeZone;
+import java.util.UUID;
+
+import org.apache.log4j.Logger;
+
+import com.cloud.configuration.Resource.ResourceType;
+import com.cloud.event.EventTypes;
+import com.cloud.event.EventVO;
+import com.cloud.event.UsageEventVO;
+import com.cloud.utils.DateUtil;
+import com.cloud.utils.NumbersUtil;
+import com.cloud.utils.exception.CloudRuntimeException;
+import com.cloud.utils.net.NetUtils;
+import com.cloud.utils.script.Script;
+
+public class Upgrade218to22 implements DbUpgrade {
+    final static Logger s_logger = Logger.getLogger(Upgrade218to22.class);
+    boolean _basicZone;
+
+    @Override
+    public File[] getPrepareScripts() {
+        String file = Script.findScript("", "db/schema-21to22.sql");
+        if (file == null) {
+            throw new CloudRuntimeException("Unable to find the upgrade script, schema-21to22.sql");
+        }
+
+        return new File[] { new File(file) };
+    }
+
+    protected void upgradeStoragePools(Connection conn) {
+        PreparedStatement pstmt;
+        try {
+            pstmt = conn.prepareStatement("UPDATE storage_pool SET status='Up'");
+            pstmt.executeUpdate();
+            pstmt.close();
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't upgrade storage pool ", e);
+        }
+    }
+
+    protected long insertNetworkOffering(Connection conn, String name, String displayText, String trafficType, boolean systemOnly, boolean defaultNetworkOffering, String availability,
+            boolean dns_service, boolean gateway_service, boolean firewall_service, boolean lb_service, boolean userdata_service, boolean vpn_service, boolean dhcp_service) {
+        String insertSql = "INSERT INTO network_offerings (name, display_text, nw_rate, mc_rate, concurrent_connections, traffic_type, tags, system_only, specify_vlan, service_offering_id, created, removed, `default`, availability, dns_service, gateway_service, firewall_service, lb_service, userdata_service, vpn_service, dhcp_service) "
+                + "VALUES (?,    ?,            NULL,    NULL,    NULL,                   ?,            NULL, ?,           0,            NULL,                now(),   NULL,    ?,       ?,            ?,           ?,               ?,                ?,          ?,                ?,           ?)";
+        try {
+            PreparedStatement pstmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
+            int i = 1;
+            pstmt.setString(i++, name);
+            pstmt.setString(i++, displayText);
+            pstmt.setString(i++, trafficType);
+            pstmt.setBoolean(i++, systemOnly);
+            pstmt.setBoolean(i++, defaultNetworkOffering);
+            pstmt.setString(i++, availability);
+            pstmt.setBoolean(i++, dns_service);
+            pstmt.setBoolean(i++, gateway_service);
+            pstmt.setBoolean(i++, firewall_service);
+            pstmt.setBoolean(i++, lb_service);
+            pstmt.setBoolean(i++, userdata_service);
+            pstmt.setBoolean(i++, vpn_service);
+            pstmt.setBoolean(i++, dhcp_service);
+            pstmt.executeUpdate();
+            ResultSet rs = pstmt.getGeneratedKeys();
+            rs.next();
+            long id = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+            return id;
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to insert network offering ", e);
+        }
+    }
+
+    protected void upgradeInstanceGroups(Connection conn) {
+        try {
+
+            // Create instance groups - duplicated names are allowed across accounts
+            PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT v.group, v.account_id from vm_instance v where v.group is not null");
+            ResultSet rs = pstmt.executeQuery();
+            ArrayList<Object[]> groups = new ArrayList<Object[]>();
+            while (rs.next()) {
+                Object[] group = new Object[10];
+                group[0] = rs.getString(1); // group name
+                group[1] = rs.getLong(2); // accountId
+                groups.add(group);
+            }
+            rs.close();
+            pstmt.close();
+
+            for (Object[] group : groups) {
+                String groupName = (String) group[0];
+                Long accountId = (Long) group[1];
+                createInstanceGroups(conn, groupName, accountId);
+            }
+
+            // update instance_group_vm_map
+            pstmt = conn.prepareStatement("SELECT g.id, v.id from vm_instance v, instance_group g where g.name=v.group and g.account_id=v.account_id and v.group is not null");
+            rs = pstmt.executeQuery();
+            ArrayList<Object[]> groupVmMaps = new ArrayList<Object[]>();
+            while (rs.next()) {
+                Object[] groupMaps = new Object[10];
+                groupMaps[0] = rs.getLong(1); // vmId
+                groupMaps[1] = rs.getLong(2); // groupId
+                groupVmMaps.add(groupMaps);
+            }
+            rs.close();
+            pstmt.close();
+
+            for (Object[] groupMap : groupVmMaps) {
+                Long groupId = (Long) groupMap[0];
+                Long instanceId = (Long) groupMap[1];
+                createInstanceGroupVmMaps(conn, groupId, instanceId);
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't update instance groups ", e);
+        }
+
+    }
+
+    protected void createInstanceGroups(Connection conn, String groupName, long accountId) throws SQLException {
+        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group (account_id, name, created) values (?, ?, now()) ");
+        pstmt.setLong(1, accountId);
+        pstmt.setString(2, groupName);
+        pstmt.executeUpdate();
+        pstmt.close();
+    }
+
+    protected void createInstanceGroupVmMaps(Connection conn, long groupId, long instanceId) throws SQLException {
+        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO instance_group_vm_map (group_id, instance_id) values (?, ?) ");
+        pstmt.setLong(1, groupId);
+        pstmt.setLong(2, instanceId);
+        pstmt.executeUpdate();
+        pstmt.close();
+    }
+
+    protected long insertNic(Connection conn, long networkId, long instanceId, boolean running, String macAddress, String ipAddress, String netmask, String strategy, String gateway, String vnet,
+            String guru, boolean defNic, int deviceId, String mode, String reservationId) throws SQLException {
+        PreparedStatement pstmt = conn
+                .prepareStatement(
+                        "INSERT INTO nics (instance_id, network_id, mac_address, ip4_address, netmask, strategy, ip_type, broadcast_uri, mode, reserver_name, reservation_id, device_id, update_time, isolation_uri, ip6_address, default_nic, created, removed, state, gateway) "
+                                + "VALUES (?,           ?,          ?,           ?,           ?,       ?,        'Ip4',   ?,             ?,    ?,             ?,              ?,         now(),       ?,          NULL,         ?,          now(),   NULL,    ?,     ?)",
+                        Statement.RETURN_GENERATED_KEYS);
+        int i = 1;
+        String isolationUri = null;
+
+        String broadcast = null;
+        if (vnet != null) {
+            broadcast = "vlan://" + vnet;
+            if (vnet.equalsIgnoreCase("untagged")) {
+                isolationUri = "ec2://" + vnet;
+            } else {
+                isolationUri = broadcast;
+            }
+        }
+        pstmt.setLong(i++, instanceId);
+        pstmt.setLong(i++, networkId);
+        pstmt.setString(i++, macAddress);
+        pstmt.setString(i++, ipAddress);
+        pstmt.setString(i++, netmask);
+        pstmt.setString(i++, strategy);
+        pstmt.setString(i++, broadcast);
+        pstmt.setString(i++, mode);
+        pstmt.setString(i++, guru);
+        pstmt.setString(i++, reservationId);
+        pstmt.setInt(i++, deviceId);
+        pstmt.setString(i++, isolationUri);
+        pstmt.setBoolean(i++, defNic);
+        pstmt.setString(i++, running ? "Reserved" : "Allocated");
+        pstmt.setString(i++, gateway);
+        pstmt.executeUpdate();
+        ResultSet rs = pstmt.getGeneratedKeys();
+        long nicId = 0;
+        if (!rs.next()) {
+            throw new CloudRuntimeException("Unable to get id for nic");
+        }
+        nicId = rs.getLong(1);
+        rs.close();
+        pstmt.close();
+        return nicId;
+    }
+
+    protected void upgradeDomR(Connection conn, long dcId, long domrId, Long publicNetworkId, long guestNetworkId, long controlNetworkId, String zoneType, String vnet) throws SQLException {
+        s_logger.debug("Upgrading domR" + domrId);
+        PreparedStatement pstmt = conn
+                .prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, domain_router.public_mac_address, domain_router.public_ip_address, domain_router.public_netmask, domain_router.guest_mac_address, domain_router.guest_ip_address, domain_router.guest_netmask, domain_router.vnet, domain_router.gateway FROM vm_instance INNER JOIN domain_router ON vm_instance.id=domain_router.id WHERE vm_instance.removed is NULL AND vm_instance.id=?");
+        pstmt.setLong(1, domrId);
+        ResultSet rs = pstmt.executeQuery();
+
+        if (!rs.next()) {
+            throw new CloudRuntimeException("Unable to find router " + domrId);
+        }
+
+        // long id = rs.getLong(1);
+        String state = rs.getString(2);
+        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+        String privateMac = rs.getString(3);
+        String privateIp = rs.getString(4);
+        String privateNetmask = rs.getString(5);
+        String publicMac = rs.getString(6);
+        String publicIp = rs.getString(7);
+        String publicNetmask = rs.getString(8);
+        String guestMac = rs.getString(9);
+        String guestIp = rs.getString(10);
+        String guestNetmask = rs.getString(11);
+        String gateway = rs.getString(13);
+        rs.close();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
+        pstmt.setString(1, publicIp);
+        rs = pstmt.executeQuery();
+
+        String publicVlan = null;
+        while (rs.next()) {
+            publicVlan = rs.getString(1);
+        }
+
+        // Control nic is the same for all types of networks
+        long controlNicId = insertNic(conn, controlNetworkId, domrId, running, privateMac, privateIp, privateNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 1, "Static",
+                privateIp != null ? (domrId + privateIp) : null);
+        if (privateIp != null) {
+            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
+            pstmt.setLong(1, controlNicId);
+            pstmt.setString(2, privateIp);
+            pstmt.setLong(3, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+
+        if (zoneType.equalsIgnoreCase("Basic")) {
+            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectPodBasedNetworkGuru", true, 0, "Dhcp", null);
+        } else if (publicIp != null) {
+            // update virtual domR
+            insertNic(conn, publicNetworkId, domrId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static", null);
+            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Start", null, vnet, "ExternalGuestNetworkGuru", false, 0, "Dhcp", null);
+        } else {
+            // update direct domR - dhcp case
+            insertNic(conn, guestNetworkId, domrId, running, guestMac, guestIp, guestNetmask, "Create", gateway, vnet, "DirectNetworkGuru", true, 0, "Dhcp", null);
+        }
+
+    }
+
+    protected void upgradeSsvm(Connection conn, long dataCenterId, long publicNetworkId, long managementNetworkId, long controlNetworkId, String zoneType) throws SQLException {
+        s_logger.debug("Upgrading ssvm in " + dataCenterId);
+        PreparedStatement pstmt = conn
+                .prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, secondary_storage_vm.public_mac_address, secondary_storage_vm.public_ip_address, secondary_storage_vm.public_netmask, secondary_storage_vm.guest_mac_address, secondary_storage_vm.guest_ip_address, secondary_storage_vm.guest_netmask, secondary_storage_vm.gateway, vm_instance.type FROM vm_instance INNER JOIN secondary_storage_vm ON vm_instance.id=secondary_storage_vm.id WHERE vm_instance.removed is NULL AND vm_instance.data_center_id=? AND vm_instance.type='SecondaryStorageVm'");
+        pstmt.setLong(1, dataCenterId);
+        ResultSet rs = pstmt.executeQuery();
+
+        if (!rs.next()) {
+            s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
+            return;
+        }
+
+        long ssvmId = rs.getLong(1);
+        String state = rs.getString(2);
+        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+        String privateMac = rs.getString(3);
+        String privateIp = rs.getString(4);
+        String privateNetmask = rs.getString(5);
+        String publicMac = rs.getString(6);
+        String publicIp = rs.getString(7);
+        String publicNetmask = rs.getString(8);
+        String guestMac = rs.getString(9);
+        String guestIp = rs.getString(10);
+        String guestNetmask = rs.getString(11);
+        String gateway = rs.getString(12);
+        String type = rs.getString(13);
+        rs.close();
+        pstmt.close();
+
+        pstmt = conn
+                .prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.removed is NULL AND vm_instance.data_center_id=? AND vm_instance.type='SecondaryStorageVm'");
+        pstmt.setLong(1, dataCenterId);
+        rs = pstmt.executeQuery();
+
+        if (!rs.next()) {
+            s_logger.debug("Unable to find ssvm in data center " + dataCenterId);
+            return;
+        }
+
+        String podGateway = rs.getString(1);
+        rs.close();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
+        pstmt.setString(1, publicIp);
+        rs = pstmt.executeQuery();
+
+        String publicVlan = null;
+        while (rs.next()) {
+            publicVlan = rs.getString(1);
+        }
+
+        rs.close();
+        pstmt.close();
+
+        if (zoneType.equalsIgnoreCase("Basic")) {
+            insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2, "Dhcp", null);
+
+        } else {
+            insertNic(conn, publicNetworkId, ssvmId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static", null);
+        }
+
+        long controlNicId = insertNic(conn, controlNetworkId, ssvmId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
+                guestIp != null ? (ssvmId + guestIp) : null);
+        if (guestIp != null) {
+            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
+            pstmt.setLong(1, controlNicId);
+            pstmt.setString(2, guestIp);
+            pstmt.setLong(3, dataCenterId);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+
+        long mgmtNicId = insertNic(conn, managementNetworkId, ssvmId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1, "Static", null);
+        if (privateIp != null) {
+            pstmt = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
+            pstmt.setLong(1, mgmtNicId);
+            pstmt.setString(2, privateIp);
+            pstmt.setLong(3, dataCenterId);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+    }
+
+    protected void upgradeConsoleProxy(Connection conn, long dcId, long cpId, long publicNetworkId, long managementNetworkId, long controlNetworkId, String zoneType) throws SQLException {
+        s_logger.debug("Upgrading cp" + cpId);
+        PreparedStatement pstmt = conn
+                .prepareStatement("SELECT vm_instance.id, vm_instance.state, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, console_proxy.public_mac_address, console_proxy.public_ip_address, console_proxy.public_netmask, console_proxy.guest_mac_address, console_proxy.guest_ip_address, console_proxy.guest_netmask, console_proxy.gateway, vm_instance.type FROM vm_instance INNER JOIN console_proxy ON vm_instance.id=console_proxy.id WHERE vm_instance.removed is NULL AND vm_instance.id=?");
+        pstmt.setLong(1, cpId);
+        ResultSet rs = pstmt.executeQuery();
+
+        if (!rs.next()) {
+            throw new CloudRuntimeException("Unable to find cp " + cpId);
+        }
+
+        long id = rs.getLong(1);
+        String state = rs.getString(2);
+        boolean running = state.equals("Running") | state.equals("Starting") | state.equals("Stopping");
+        String privateMac = rs.getString(3);
+        String privateIp = rs.getString(4);
+        String privateNetmask = rs.getString(5);
+        String publicMac = rs.getString(6);
+        String publicIp = rs.getString(7);
+        String publicNetmask = rs.getString(8);
+        String guestMac = rs.getString(9);
+        String guestIp = rs.getString(10);
+        String guestNetmask = rs.getString(11);
+        String gateway = rs.getString(12);
+        String type = rs.getString(13);
+        rs.close();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("SELECT host_pod_ref.gateway from host_pod_ref INNER JOIN vm_instance ON vm_instance.pod_id=host_pod_ref.id WHERE vm_instance.id=?");
+        pstmt.setLong(1, cpId);
+        rs = pstmt.executeQuery();
+
+        if (!rs.next()) {
+            throw new CloudRuntimeException("Unable to find cp " + cpId);
+        }
+
+        String podGateway = rs.getString(1);
+        rs.close();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("SELECT v.vlan_id from vlan v, user_ip_address u where v.id=u.vlan_db_id and u.public_ip_address=?");
+        pstmt.setString(1, publicIp);
+        rs = pstmt.executeQuery();
+
+        String publicVlan = null;
+        while (rs.next()) {
+            publicVlan = rs.getString(1);
+        }
+
+        rs.close();
+        pstmt.close();
+
+        if (zoneType.equalsIgnoreCase("Basic")) {
+            insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "DirectPodBasedNetworkGuru", true, 2, "Dhcp", null);
+        } else {
+            insertNic(conn, publicNetworkId, cpId, running, publicMac, publicIp, publicNetmask, "Create", gateway, publicVlan, "PublicNetworkGuru", true, 2, "Static", null);
+        }
+
+        long controlNicId = insertNic(conn, controlNetworkId, cpId, running, guestMac, guestIp, guestNetmask, "Start", "169.254.0.1", null, "ControlNetworkGuru", false, 0, "Static",
+                guestIp != null ? (cpId + guestIp) : null);
+        if (guestIp != null) {
+            pstmt = conn.prepareStatement("UPDATE op_dc_link_local_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
+            pstmt.setLong(1, controlNicId);
+            pstmt.setString(2, guestIp);
+            pstmt.setLong(3, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+        long mgmtNicId = insertNic(conn, managementNetworkId, cpId, running, privateMac, privateIp, privateNetmask, "Start", podGateway, null, "PodBasedNetworkGuru", false, 1, "Static",
+                privateIp != null ? (cpId + privateIp) : null);
+        if (privateIp != null) {
+            pstmt = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET instance_id=? WHERE ip_address=? AND data_center_id=?");
+            pstmt.setLong(1, mgmtNicId);
+            pstmt.setString(2, privateIp);
+            pstmt.setLong(3, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+    }
+
+    protected void upgradeUserVms(Connection conn, long domainRouterId, long networkId, String gateway, String vnet, String guruName, String strategy) throws SQLException {
+        PreparedStatement pstmt = conn
+                .prepareStatement("SELECT vm_instance.id, vm_instance.private_mac_address, vm_instance.private_ip_address, vm_instance.private_netmask, vm_instance.state, vm_instance.type FROM vm_instance INNER JOIN user_vm ON vm_instance.id=user_vm.id WHERE user_vm.domain_router_id=? and vm_instance.removed IS NULL");
+        pstmt.setLong(1, domainRouterId);
+        ResultSet rs = pstmt.executeQuery();
+        List<Object[]> vms = new ArrayList<Object[]>();
+        while (rs.next()) {
+            Object[] vm = new Object[10];
+            vm[0] = rs.getLong(1); // vm id
+            vm[1] = rs.getString(2); // mac address
+            vm[2] = rs.getString(3); // ip address
+            vm[3] = rs.getString(4); // netmask
+            vm[4] = rs.getString(5); // vm state
+            vms.add(vm);
+        }
+        rs.close();
+        pstmt.close();
+
+        s_logger.debug("Upgrading " + vms.size() + " vms for router " + domainRouterId);
+
+        int count = 0;
+        for (Object[] vm : vms) {
+            String state = (String) vm[4];
+
+            boolean running = false;
+            if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
+                running = true;
+                count++;
+            }
+
+            insertNic(conn, networkId, (Long) vm[0], running, (String) vm[1], (String) vm[2], (String) vm[3], strategy, gateway, vnet, guruName, true, 0, "Dhcp", null);
+        }
+
+        pstmt = conn.prepareStatement("SELECT state FROM vm_instance WHERE id=?");
+        pstmt.setLong(1, domainRouterId);
+        rs = pstmt.executeQuery();
+        rs.next();
+        String state = rs.getString(1);
+        if (state.equals("Running") || state.equals("Starting") || state.equals("Stopping")) {
+            count++;
+        }
+        rs.close();
+        pstmt.close();
+
+        Long originalNicsCount = 0L;
+        pstmt = conn.prepareStatement("SELECT nics_count from op_networks where id=?");
+        pstmt.setLong(1, networkId);
+        ResultSet originalCountRs = pstmt.executeQuery();
+
+        if (originalCountRs.next()) {
+            originalNicsCount = originalCountRs.getLong(1);
+        }
+
+        Long resultCount = originalNicsCount + count;
+        originalCountRs.close();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("UPDATE op_networks SET nics_count=?, check_for_gc=? WHERE id=?");
+        pstmt.setLong(1, resultCount);
+        if (count == 0) {
+            pstmt.setBoolean(2, false);
+        } else {
+            pstmt.setBoolean(2, true);
+        }
+        pstmt.setLong(3, networkId);
+        pstmt.executeUpdate();
+        pstmt.close();
+    }
+
+    protected long insertNetwork(Connection conn, String name, String displayText, String trafficType, String broadcastDomainType, String broadcastUri, String gateway, String cidr, String mode,
+            long networkOfferingId, long dataCenterId, String guruName, String state, long domainId, long accountId, String dns1, String dns2, String guestType, boolean shared, String networkDomain,
+            boolean isDefault, String reservationId) {
+        String getNextNetworkSequenceSql = "SELECT value from sequence where name='networks_seq'";
+        String advanceNetworkSequenceSql = "UPDATE sequence set value=value+1 where name='networks_seq'";
+        String insertNetworkSql = "INSERT INTO networks(id, name, display_text, traffic_type, broadcast_domain_type, gateway, cidr, mode, network_offering_id, data_center_id, guru_name, state, domain_id, account_id, dns1, dns2, guest_type, shared, is_default, created, network_domain, related, reservation_id, broadcast_uri) "
+                + "VALUES(?,  ?,    ?,            ?,            ?,                     ?,       ?,    ?,    ?,                   ?,              ?,         ?,     ?,         ?,          ?,    ?,    ?,          ?,      ?,          now(),   ?,              ?,       ?,              ?)";
+        try {
+            PreparedStatement pstmt = conn.prepareStatement(getNextNetworkSequenceSql);
+            ResultSet rs = pstmt.executeQuery();
+            rs.next();
+            long seq = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement(advanceNetworkSequenceSql);
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement(insertNetworkSql);
+            int i = 1;
+            pstmt.setLong(i++, seq);
+            pstmt.setString(i++, name);
+            pstmt.setString(i++, displayText);
+            pstmt.setString(i++, trafficType);
+            pstmt.setString(i++, broadcastDomainType);
+            pstmt.setString(i++, gateway);
+            pstmt.setString(i++, cidr);
+            pstmt.setString(i++, mode);
+            pstmt.setLong(i++, networkOfferingId);
+            pstmt.setLong(i++, dataCenterId);
+            pstmt.setString(i++, guruName);
+            pstmt.setString(i++, state);
+            pstmt.setLong(i++, domainId);
+            pstmt.setLong(i++, accountId);
+            pstmt.setString(i++, dns1);
+            pstmt.setString(i++, dns2);
+            pstmt.setString(i++, guestType);
+            pstmt.setBoolean(i++, shared);
+            pstmt.setBoolean(i++, isDefault);
+            pstmt.setString(i++, networkDomain);
+            pstmt.setLong(i++, seq);
+            pstmt.setString(i++, reservationId);
+            pstmt.setString(i++, broadcastUri);
+            pstmt.executeUpdate();
+
+            pstmt = conn.prepareStatement("INSERT INTO op_networks(id, mac_address_seq, nics_count, gc, check_for_gc) VALUES(?, ?, ?, ?, ?)");
+            pstmt.setLong(1, seq);
+            pstmt.setLong(2, 0);
+            pstmt.setLong(3, 0);
+            if (trafficType.equals("Guest")) {
+                pstmt.setBoolean(4, true);
+            } else {
+                pstmt.setBoolean(4, false);
+            }
+            pstmt.setBoolean(5, false);
+            pstmt.executeUpdate();
+
+            pstmt = conn.prepareStatement("INSERT INTO account_network_ref (account_id, network_id, is_owner) VALUES (?,    ?,  1)");
+            pstmt.setLong(1, accountId);
+            pstmt.setLong(2, seq);
+            pstmt.executeUpdate();
+
+            return seq;
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to create network", e);
+        }
+    }
+
+    protected void upgradeManagementIpAddress(Connection conn, long dcId) throws SQLException {
+        PreparedStatement pstmt = conn.prepareStatement("SELECT op_dc_ip_address_alloc.id FROM op_dc_ip_address_alloc WHERE data_center_id=?");
+        pstmt.setLong(1, dcId);
+        ResultSet rs = pstmt.executeQuery();
+        ArrayList<Object[]> allocatedIps = new ArrayList<Object[]>();
+        while (rs.next()) {
+            Object[] ip = new Object[10];
+            ip[0] = rs.getLong(1); // id
+            allocatedIps.add(ip);
+        }
+        rs.close();
+        pstmt.close();
+
+        for (Object[] allocatedIp : allocatedIps) {
+            pstmt = conn.prepareStatement("SELECT mac_address FROM data_center WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                throw new CloudRuntimeException("Unable to get mac address for data center " + dcId);
+            }
+            long mac = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE data_center SET mac_address=mac_address+1 WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE op_dc_ip_address_alloc SET mac_address=? WHERE id=?");
+            pstmt.setLong(1, mac);
+            pstmt.setLong(2, (Long) allocatedIp[0]);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+
+    }
+
+    protected void upgradeDirectUserIpAddress(Connection conn, long dcId, long networkId, String vlanType) throws SQLException {
+        s_logger.debug("Upgrading user ip address for data center " + dcId + " network " + networkId + " vlan type " + vlanType);
+        PreparedStatement pstmt = conn
+                .prepareStatement("UPDATE user_ip_address INNER JOIN vlan ON user_ip_address.vlan_db_id=vlan.id SET user_ip_address.source_network_id=vlan.network_id WHERE user_ip_address.data_center_id=? AND vlan.vlan_type=?");
+        pstmt.setLong(1, dcId);
+        pstmt.setString(2, vlanType);
+        pstmt.executeUpdate();
+        pstmt.close();
+
+        pstmt = conn
+                .prepareStatement("SELECT user_ip_address.id, user_ip_address.public_ip_address, user_ip_address.account_id, user_ip_address.allocated FROM user_ip_address INNER JOIN vlan ON vlan.id=user_ip_address.vlan_db_id WHERE user_ip_address.data_center_id = ? AND vlan.vlan_type=?");
+        pstmt.setLong(1, dcId);
+        pstmt.setString(2, vlanType);
+        ResultSet rs = pstmt.executeQuery();
+        ArrayList<Object[]> allocatedIps = new ArrayList<Object[]>();
+        while (rs.next()) {
+            Object[] ip = new Object[10];
+            ip[0] = rs.getLong(1); // id
+            ip[1] = rs.getString(2); // ip address
+            ip[2] = rs.getLong(3); // account id
+            ip[3] = rs.getDate(4); // allocated
+            allocatedIps.add(ip);
+        }
+        rs.close();
+        pstmt.close();
+
+        s_logger.debug("Marking " + allocatedIps.size() + " ip addresses to belong to network " + networkId);
+        s_logger.debug("Updating mac addresses for data center id=" + dcId + ". Found " + allocatedIps.size() + " ip addresses to update");
+
+        for (Object[] allocatedIp : allocatedIps) {
+            pstmt = conn.prepareStatement("SELECT mac_address FROM data_center WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                throw new CloudRuntimeException("Unable to get mac address for data center " + dcId);
+            }
+            long mac = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE data_center SET mac_address=mac_address+1 WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE user_ip_address SET mac_address=? WHERE id=?");
+            pstmt.setLong(1, mac);
+            pstmt.setLong(2, (Long) allocatedIp[0]);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+    }
+
+    protected void upgradePublicUserIpAddress(Connection conn, long dcId, long networkId, String vlanType) throws SQLException {
+        s_logger.debug("Upgrading user ip address for data center " + dcId + " network " + networkId + " vlan type " + vlanType);
+        PreparedStatement pstmt = conn
+                .prepareStatement("UPDATE user_ip_address INNER JOIN vlan ON user_ip_address.vlan_db_id=vlan.id SET source_network_id=? WHERE user_ip_address.data_center_id=? AND vlan.vlan_type=?");
+        pstmt.setLong(1, networkId);
+        pstmt.setLong(2, dcId);
+        pstmt.setString(3, vlanType);
+        pstmt.executeUpdate();
+        pstmt.close();
+
+        pstmt = conn.prepareStatement("UPDATE vlan SET network_id = ? WHERE data_center_id=? AND vlan_type=?");
+        pstmt.setLong(1, networkId);
+        pstmt.setLong(2, dcId);
+        pstmt.setString(3, vlanType);
+        pstmt.executeUpdate();
+        pstmt.close();
+
+        pstmt = conn
+                .prepareStatement("SELECT user_ip_address.id, user_ip_address.public_ip_address, user_ip_address.account_id, user_ip_address.allocated FROM user_ip_address INNER JOIN vlan ON vlan.id=user_ip_address.vlan_db_id WHERE user_ip_address.data_center_id = ? AND vlan.vlan_type='VirtualNetwork'");
+        pstmt.setLong(1, dcId);
+        ResultSet rs = pstmt.executeQuery();
+        ArrayList<Object[]> allocatedIps = new ArrayList<Object[]>();
+        while (rs.next()) {
+            Object[] ip = new Object[10];
+            ip[0] = rs.getLong(1); // id
+            ip[1] = rs.getString(2); // ip address
+            ip[2] = rs.getLong(3); // account id
+            ip[3] = rs.getDate(4); // allocated
+            allocatedIps.add(ip);
+        }
+        rs.close();
+        pstmt.close();
+
+        for (Object[] allocatedIp : allocatedIps) {
+            pstmt = conn.prepareStatement("SELECT mac_address FROM data_center WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                throw new CloudRuntimeException("Unable to get mac address for data center " + dcId);
+            }
+            long mac = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE data_center SET mac_address=mac_address+1 WHERE id = ?");
+            pstmt.setLong(1, dcId);
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            Long associatedNetworkId = null;
+            if (allocatedIp[3] != null && allocatedIp[2] != null) {
+                pstmt = conn.prepareStatement("SELECT id FROM networks WHERE data_center_id=? AND account_id=?");
+                pstmt.setLong(1, dcId);
+                pstmt.setLong(2, (Long) allocatedIp[2]);
+                rs = pstmt.executeQuery();
+                if (!rs.next()) {
+                    throw new CloudRuntimeException("Unable to find a network for account " + allocatedIp[2] + " in dc " + dcId);
+                }
+                associatedNetworkId = rs.getLong(1);
+                rs.close();
+                pstmt.close();
+            }
+            pstmt = conn.prepareStatement("UPDATE user_ip_address SET mac_address=?, network_id=? WHERE id=?");
+            pstmt.setLong(1, mac);
+            if (associatedNetworkId != null) {
+                pstmt.setLong(2, associatedNetworkId);
+            } else {
+                pstmt.setObject(2, null);
+            }
+            pstmt.setLong(3, (Long) allocatedIp[0]);
+            pstmt.executeUpdate();
+            pstmt.close();
+        }
+
+    }
+
+    protected void upgradeDataCenter(Connection conn) {
+        PreparedStatement pstmt;
+        try {
+            pstmt = conn.prepareStatement("SELECT value FROM configuration WHERE name='direct.attach.untagged.vlan.enabled'");
+            ResultSet rs = pstmt.executeQuery();
+            _basicZone = !rs.next() || Boolean.parseBoolean(rs.getString(1));
+            rs.close();
+            pstmt.close();
+            pstmt = conn
+                    .prepareStatement("UPDATE data_center SET networktype=?, dns_provider=?, gateway_provider=?, firewall_provider=?, dhcp_provider=?, lb_provider=?, vpn_provider=?, userdata_provider=?");
+            if (_basicZone) {
+                pstmt.setString(1, "Basic");
+                pstmt.setString(2, "DhcpServer");
+                pstmt.setString(3, null);
+                pstmt.setString(4, null);
+                pstmt.setString(5, "DhcpServer");
+                pstmt.setString(6, null);
+                pstmt.setString(7, null);
+                pstmt.setString(8, "DhcpServer");
+            } else {
+                pstmt.setString(1, "Advanced");
+                pstmt.setString(2, "VirtualRouter");
+                pstmt.setString(3, "VirtualRouter");
+                pstmt.setString(4, "VirtualRouter");
+                pstmt.setString(5, "VirtualRouter");
+                pstmt.setString(6, "VirtualRouter");
+                pstmt.setString(7, "VirtualRouter");
+                pstmt.setString(8, "VirtualRouter");
+            }
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            // For basic zone vnet field should be NULL
+
+            if (_basicZone) {
+                pstmt = conn.prepareStatement("UPDATE data_center SET vnet=?, guest_network_cidr=?");
+                pstmt.setString(1, null);
+                pstmt.setString(2, null);
+                pstmt.executeUpdate();
+                pstmt.close();
+            }
+
+            pstmt = conn.prepareStatement("SELECT id, guest_network_cidr, domain FROM data_center");
+            rs = pstmt.executeQuery();
+            ArrayList<Object[]> dcs = new ArrayList<Object[]>();
+            while (rs.next()) {
+                Object[] dc = new Object[10];
+                dc[0] = rs.getLong(1); // data center id
+                dc[1] = rs.getString(2); // guest network cidr
+                dc[2] = rs.getString(3); // network domain
+                dcs.add(dc);
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("SELECT id FROM network_offerings WHERE name='System-Management-Network'");
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                s_logger.error("Unable to find the management network offering.");
+                throw new CloudRuntimeException("Unable to find the management network offering.");
+            }
+            long managementNetworkOfferingId = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("SELECT id FROM network_offerings WHERE name='System-Public-Network'");
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                s_logger.error("Unable to find the public network offering.");
+                throw new CloudRuntimeException("Unable to find the public network offering.");
+            }
+            long publicNetworkOfferingId = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("SELECT id FROM network_offerings WHERE name='System-Control-Network'");
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                s_logger.error("Unable to find the control network offering.");
+                throw new CloudRuntimeException("Unable to find the control network offering.");
+            }
+            long controlNetworkOfferingId = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("SELECT id FROM network_offerings WHERE name='System-Storage-Network'");
+            rs = pstmt.executeQuery();
+            if (!rs.next()) {
+                s_logger.error("Unable to find the storage network offering.");
+                throw new CloudRuntimeException("Unable to find the storage network offering.");
+            }
+            long storageNetworkOfferingId = rs.getLong(1);
+            rs.close();
+            pstmt.close();
+
+            if (_basicZone) {
+                for (Object[] dc : dcs) {
+                    Long dcId = (Long) dc[0];
+                    long mgmtNetworkId = insertNetwork(conn, "ManagementNetwork" + dcId, "Management Network created for Zone " + dcId, "Management", "Native", null, null, null, "Static",
+                            managementNetworkOfferingId, dcId, "PodBasedNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    long storageNetworkId = insertNetwork(conn, "StorageNetwork" + dcId, "Storage Network created for Zone " + dcId, "Storage", "Native", null, null, null, "Static",
+                            storageNetworkOfferingId, dcId, "PodBasedNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    long controlNetworkId = insertNetwork(conn, "ControlNetwork" + dcId, "Control Network created for Zone " + dcId, "Control", "LinkLocal", null, NetUtils.getLinkLocalGateway(),
+                            NetUtils.getLinkLocalCIDR(), "Static", controlNetworkOfferingId, dcId, "ControlNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    upgradeManagementIpAddress(conn, dcId);
+                    long basicDefaultDirectNetworkId = insertNetwork(conn, "BasicZoneDirectNetwork" + dcId, "Basic Zone Direct Network created for Zone " + dcId, "Guest", "Native", null, null, null,
+                            "Dhcp", 5, dcId, "DirectPodBasedNetworkGuru", "Setup", 1, 1, null, null, "Direct", true, null, true, null);
+
+                    pstmt = conn.prepareStatement("SELECT id FROM vlan WHERE vlan_type='DirectAttached' AND data_center_id=?");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    while (rs.next()) {
+                        long vlanId = rs.getLong(1);
+
+                        pstmt = conn.prepareStatement("UPDATE vlan SET network_id=? WHERE id=?");
+                        pstmt.setLong(1, basicDefaultDirectNetworkId);
+                        pstmt.setLong(2, vlanId);
+                        pstmt.executeUpdate();
+                        pstmt.close();
+                    }
+
+                    upgradeDirectUserIpAddress(conn, dcId, basicDefaultDirectNetworkId, "DirectAttached");
+
+                    // update Dhcp servers information in domain_router and vm_instance tables; all domRs belong to the same
+                    // network
+                    pstmt = conn
+                            .prepareStatement("SELECT vm_instance.id, vm_instance.domain_id, vm_instance.account_id, domain_router.gateway, domain_router.guest_ip_address, domain_router.domain, domain_router.dns1, domain_router.dns2, domain_router.vnet FROM vm_instance INNER JOIN domain_router ON vm_instance.id=domain_router.id WHERE vm_instance.removed IS NULL AND vm_instance.type='DomainRouter' AND vm_instance.data_center_id=?");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    ArrayList<Object[]> routers = new ArrayList<Object[]>();
+                    while (rs.next()) {
+                        Object[] router = new Object[40];
+                        router[0] = rs.getLong(1); // router id
+                        router[1] = rs.getString(4); // router gateway which is gonna be gateway for user vms
+                        routers.add(router);
+                    }
+                    rs.close();
+                    pstmt.close();
+
+                    for (Object[] router : routers) {
+                        s_logger.debug("Updating domR with network id in basic zone id=" + dcId);
+                        pstmt = conn.prepareStatement("UPDATE domain_router SET network_id = ? wHERE id = ? ");
+                        pstmt.setLong(1, basicDefaultDirectNetworkId);
+                        pstmt.setLong(2, (Long) router[0]);
+                        pstmt.executeUpdate();
+                        pstmt.close();
+
+                        upgradeUserVms(conn, (Long) router[0], basicDefaultDirectNetworkId, (String) router[1], "untagged", "DirectPodBasedNetworkGuru", "Create");
+                        upgradeDomR(conn, dcId, (Long) router[0], null, basicDefaultDirectNetworkId, controlNetworkId, "Basic", "untagged");
+                    }
+
+                    upgradeSsvm(conn, dcId, basicDefaultDirectNetworkId, mgmtNetworkId, controlNetworkId, "Basic");
+
+                    pstmt = conn.prepareStatement("SELECT vm_instance.id FROM vm_instance WHERE removed IS NULL AND type='ConsoleProxy' AND data_center_id=?");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    while (rs.next()) {
+                        upgradeConsoleProxy(conn, dcId, rs.getLong(1), basicDefaultDirectNetworkId, mgmtNetworkId, controlNetworkId, "Basic");
+                    }
+
+                }
+            } else {
+                for (Object[] dc : dcs) {
+                    Long dcId = (Long) dc[0];
+                    long mgmtNetworkId = insertNetwork(conn, "ManagementNetwork" + dcId, "Management Network created for Zone " + dcId, "Management", "Native", null, null, null, "Static",
+                            managementNetworkOfferingId, dcId, "PodBasedNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    insertNetwork(conn, "StorageNetwork" + dcId, "Storage Network created for Zone " + dcId, "Storage", "Native", null, null, null, "Static", storageNetworkOfferingId, dcId,
+                            "PodBasedNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    long controlNetworkId = insertNetwork(conn, "ControlNetwork" + dcId, "Control Network created for Zone " + dcId, "Control", "Native", null, null, null, "Static",
+                            controlNetworkOfferingId, dcId, "ControlNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+                    upgradeManagementIpAddress(conn, dcId);
+                    long publicNetworkId = insertNetwork(conn, "PublicNetwork" + dcId, "Public Network Created for Zone " + dcId, "Public", "Vlan", null, null, null, "Static",
+                            publicNetworkOfferingId, dcId, "PublicNetworkGuru", "Setup", 1, 1, null, null, null, true, null, false, null);
+
+                    pstmt = conn
+                            .prepareStatement("SELECT vm_instance.id, vm_instance.domain_id, vm_instance.account_id, domain_router.guest_ip_address, domain_router.domain, domain_router.dns1, domain_router.dns2, domain_router.vnet FROM vm_instance INNER JOIN domain_router ON vm_instance.id=domain_router.id WHERE vm_instance.removed IS NULL AND vm_instance.type='DomainRouter' AND vm_instance.data_center_id=? and domain_router.role='DHCP_FIREWALL_LB_PASSWD_USERDATA'");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    ArrayList<Object[]> routers = new ArrayList<Object[]>();
+                    while (rs.next()) {
+                        Object[] router = new Object[40];
+                        router[0] = rs.getLong(1); // router id
+                        router[1] = rs.getLong(2); // domain id
+                        router[2] = rs.getLong(3); // account id
+                        router[3] = rs.getString(4); // guest ip which becomes the gateway in network
+                        router[4] = rs.getString(5); // domain name
+                        router[5] = rs.getString(6); // dns1
+                        router[6] = rs.getString(7); // dns2
+                        router[7] = rs.getString(8); // vnet
+                        routers.add(router);
+                    }
+                    rs.close();
+                    pstmt.close();
+
+                    for (Object[] router : routers) {
+                        String vnet = (String) router[7];
+                        String reservationId = null;
+                        String state = "Allocated";
+                        if (vnet != null) {
+                            reservationId = dcId + "-" + vnet;
+                            state = "Implemented";
+                        }
+
+                        String vlan = null;
+                        if (vnet != null) {
+                            vlan = "vlan://" + vnet;
+                        }
+
+                        long virtualNetworkId = insertNetwork(conn, "VirtualNetwork" + router[0], "Virtual Network for " + router[0], "Guest", "Vlan", vlan, (String) router[3], (String) dc[1],
+                                "Dhcp", 6, dcId, "ExternalGuestNetworkGuru", state, (Long) router[1], (Long) router[2], (String) router[5], (String) router[6], "Virtual", false, (String) router[4],
+                                true, reservationId);
+                        pstmt = conn.prepareStatement("UPDATE domain_router SET network_id = ? wHERE id = ? ");
+                        pstmt.setLong(1, virtualNetworkId);
+                        pstmt.setLong(2, (Long) router[0]);
+                        pstmt.executeUpdate();
+                        pstmt.close();
+                        s_logger.debug("Network inserted for " + router[0] + " id = " + virtualNetworkId);
+
+                        upgradeUserVms(conn, (Long) router[0], virtualNetworkId, (String) router[3], vnet, "ExternalGuestNetworkGuru", "Start");
+                        upgradeDomR(conn, dcId, (Long) router[0], publicNetworkId, virtualNetworkId, controlNetworkId, "Advanced", vnet);
+                    }
+
+                    upgradePublicUserIpAddress(conn, dcId, publicNetworkId, "VirtualNetwork");
+
+                    // Create direct networks
+                    pstmt = conn.prepareStatement("SELECT id, vlan_id, vlan_gateway, vlan_netmask FROM vlan WHERE vlan_type='DirectAttached' AND data_center_id=?");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    HashMap<String, Long> vlanNetworkMap = new HashMap<String, Long>();
+                    while (rs.next()) {
+                        long vlanId = rs.getLong(1);
+                        String tag = rs.getString(2);
+                        String gateway = rs.getString(3);
+                        String netmask = rs.getString(4);
+                        String cidr = NetUtils.getCidrFromGatewayAndNetmask(gateway, netmask);
+
+                        // Get the owner of the network
+                        Long accountId = 1L;
+                        Long domainId = 1L;
+                        boolean isShared = true;
+                        pstmt = conn.prepareStatement("SELECT account_id FROM account_vlan_map WHERE account_id IS NOT NULL AND vlan_db_id=?");
+                        pstmt.setLong(1, vlanId);
+                        ResultSet accountRs = pstmt.executeQuery();
+                        while (accountRs.next()) {
+                            isShared = false;
+                            accountId = accountRs.getLong(1);
+                            pstmt = conn.prepareStatement("SELECT domain_id FROM account WHERE id=?");
+                            pstmt.setLong(1, accountId);
+                            ResultSet domainRs = pstmt.executeQuery();
+                            while (domainRs.next()) {
+                                domainId = domainRs.getLong(1);
+                            }
+                        }
+
+                        if (vlanNetworkMap.get(tag) == null) {
+                            long directNetworkId = insertNetwork(conn, "DirectNetwork" + vlanId, "Direct network created for " + vlanId, "Guest", "Vlan", "vlan://" + tag, gateway, cidr, "Dhcp", 7,
+                                    dcId, "DirectNetworkGuru", "Setup", domainId, accountId, null, null, "Direct", isShared, (String) dc[2], true, null);
+                            vlanNetworkMap.put(tag, directNetworkId);
+                        }
+
+                        pstmt = conn.prepareStatement("UPDATE vlan SET network_id=? WHERE id=?");
+                        pstmt.setLong(1, vlanNetworkMap.get(tag));
+                        pstmt.setLong(2, vlanId);
+                        pstmt.executeUpdate();
+
+                        pstmt.close();
+
+                        upgradeDirectUserIpAddress(conn, dcId, vlanNetworkMap.get(tag), "DirectAttached");
+                        s_logger.debug("Created Direct networks and upgraded Direct ip addresses");
+                    }
+
+                    // Create DHCP domRs - Direct networks
+                    pstmt = conn
+                            .prepareStatement("SELECT vm_instance.id, domain_router.guest_ip_address FROM vm_instance INNER JOIN domain_router ON vm_instance.id=domain_router.id WHERE vm_instance.removed IS NULL AND vm_instance.type='DomainRouter' AND vm_instance.data_center_id=? and domain_router.role='DHCP_USERDATA'");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    ArrayList<Object[]> dhcpServers = new ArrayList<Object[]>();
+                    while (rs.next()) {
+                        Object[] dhcpServer = new Object[40];
+                        dhcpServer[0] = rs.getLong(1); // router id
+                        dhcpServer[1] = rs.getString(2); // guest IP address - direct ip address of the domR
+                        dhcpServers.add(dhcpServer);
+                    }
+                    rs.close();
+                    pstmt.close();
+
+                    for (Object[] dhcpServer : dhcpServers) {
+                        Long routerId = (Long) dhcpServer[0];
+                        String directIp = (String) dhcpServer[1];
+
+                        pstmt = conn.prepareStatement("SELECT u.source_network_id, v.vlan_id from user_ip_address u, vlan v where u.public_ip_address=? and v.id=u.vlan_db_id");
+                        pstmt.setString(1, directIp);
+                        rs = pstmt.executeQuery();
+                        if (!rs.next()) {
+                            throw new CloudRuntimeException("Unable to find Direct ip address " + directIp + " in user_ip_address table");
+                        }
+
+                        Long directNetworkId = rs.getLong(1);
+                        String vnet = rs.getString(2);
+                        rs.close();
+
+                        pstmt = conn.prepareStatement("SELECT gateway from networks where id=?");
+                        pstmt.setLong(1, directNetworkId);
+                        rs = pstmt.executeQuery();
+                        if (!rs.next()) {
+                            throw new CloudRuntimeException("Unable to find gateway for network id=" + directNetworkId);
+                        }
+
+                        String gateway = rs.getString(1);
+                        rs.close();
+
+                        pstmt = conn.prepareStatement("UPDATE domain_router SET network_id = ? wHERE id = ? ");
+                        pstmt.setLong(1, directNetworkId);
+                        pstmt.setLong(2, routerId);
+                        pstmt.executeUpdate();
+                        pstmt.close();
+                        s_logger.debug("NetworkId updated for router id=" + routerId + "with network id = " + directNetworkId);
+
+                        upgradeUserVms(conn, routerId, directNetworkId, gateway, vnet, "DirectNetworkGuru", "Create");
+                        s_logger.debug("Upgraded Direct vms in Advance zone id=" + dcId);
+                        upgradeDomR(conn, dcId, routerId, null, directNetworkId, controlNetworkId, "Advanced", vnet);
+                        s_logger.debug("Upgraded Direct domRs in Advance zone id=" + dcId);
+                    }
+
+                    // Upgrade SSVM
+                    upgradeSsvm(conn, dcId, publicNetworkId, mgmtNetworkId, controlNetworkId, "Advanced");
+
+                    // Upgrade ConsoleProxy
+                    pstmt = conn.prepareStatement("SELECT vm_instance.id FROM vm_instance WHERE removed IS NULL AND type='ConsoleProxy' AND data_center_id=?");
+                    pstmt.setLong(1, dcId);
+                    rs = pstmt.executeQuery();
+                    while (rs.next()) {
+                        upgradeConsoleProxy(conn, dcId, rs.getLong(1), publicNetworkId, mgmtNetworkId, controlNetworkId, "Advanced");
+                    }
+                    pstmt.close();
+                }
+            }
+
+        } catch (SQLException e) {
+            s_logger.error("Can't update data center ", e);
+            throw new CloudRuntimeException("Can't update data center ", e);
+        }
+    }
+
+    private void updateUserStats(Connection conn) {
+        try {
+
+            // update device_type information
+            PreparedStatement pstmt = conn.prepareStatement("UPDATE user_statistics SET device_type='DomainRouter'");
+            pstmt.executeUpdate();
+            pstmt.close();
+            s_logger.debug("Upgraded userStatistcis with device_type=DomainRouter");
+
+            // update device_id infrormation
+            pstmt = conn.prepareStatement("SELECT id, account_id, data_center_id FROM user_statistics");
+            ResultSet rs = pstmt.executeQuery();
+
+            while (rs.next()) {
+                Long id = rs.getLong(1); // user stats id
+                Long accountId = rs.getLong(2); // account id
+                Long dataCenterId = rs.getLong(3); // zone id
+
+                pstmt = conn.prepareStatement("SELECT networktype from data_center where id=?");
+                pstmt.setLong(1, dataCenterId);
+
+                ResultSet dcSet = pstmt.executeQuery();
+
+                if (!dcSet.next()) {
+                    s_logger.error("Unable to get data_center information as a part of user_statistics update");
+                    throw new CloudRuntimeException("Unable to get data_center information as a part of user_statistics update");
+                }
+
+                String dataCenterType = dcSet.getString(1);
+
+                if (dataCenterType.equalsIgnoreCase("basic")) {
+                    accountId = 1L;
+                }
+
+                pstmt = conn.prepareStatement("SELECT id from vm_instance where account_id=? AND data_center_id=? AND type='DomainRouter'");
+                pstmt.setLong(1, accountId);
+                pstmt.setLong(2, dataCenterId);
+                ResultSet rs1 = pstmt.executeQuery();
+
+                Long deviceId = 0L;
+                if (!rs1.next()) {
+                    // check if there are any non-removed user vms existing for this account
+                    // if all vms are expunged, and there is no domR, just skip this record
+                    pstmt = conn.prepareStatement("SELECT * from vm_instance where account_id=? AND data_center_id=? AND removed IS NULL");
+                    pstmt.setLong(1, accountId);
+                    pstmt.setLong(2, dataCenterId);
+                    ResultSet nonRemovedVms = pstmt.executeQuery();
+
+                    if (nonRemovedVms.next()) {
+                        s_logger.warn("Failed to find domR for for account id=" + accountId + " in zone id=" + dataCenterId + "; will try to locate domR based on user_vm info");
+                        //try to get domR information from the user_vm belonging to the account
+                        pstmt = conn.prepareStatement("SELECT u.domain_router_id from user_vm u, vm_instance v where u.account_id=? AND v.data_center_id=? AND v.removed IS NULL AND u.domain_router_id is NOT NULL");
+                        pstmt.setLong(1, accountId);
+                        pstmt.setLong(2, dataCenterId);
+                        ResultSet userVmSet = pstmt.executeQuery();
+                        if (!userVmSet.next()) {
+                            s_logger.warn("Skipping user_statistics upgrade for account id=" + accountId + " in datacenter id=" + dataCenterId);
+                            continue;
+                        }
+                        deviceId = userVmSet.getLong(1);
+                    } else {
+                        s_logger.debug("Account id=" + accountId + " doesn't own any user vms and domRs, so skipping user_statistics update");
+                        continue;
+                    }
+                } else {
+                    deviceId = rs1.getLong(1);
+                }
+
+                pstmt = conn.prepareStatement("UPDATE user_statistics SET device_id=? where id=?");
+                pstmt.setLong(1, deviceId);
+                pstmt.setLong(2, id);
+                pstmt.executeUpdate();
+
+                pstmt = conn.prepareStatement("");
+
+            }
+            s_logger.debug("Upgraded userStatistcis with deviceId(s)");
+
+        } catch (Exception e) {
+            throw new CloudRuntimeException("Failed to migrate usage events: ", e);
+        }
+    }
+
+    public void upgradePortForwardingRules(Connection conn) {
+        try {
+            PreparedStatement pstmt = conn.prepareStatement("SELECT id, public_ip_address, public_port, private_ip_address, private_port, protocol FROM ip_forwarding WHERE forwarding=1");
+            ResultSet rs = pstmt.executeQuery();
+            ArrayList<Object[]> rules = new ArrayList<Object[]>();
+            while (rs.next()) {
+                Object[] rule = new Object[10];
+                rule[0] = rs.getLong(1); // rule id
+                rule[1] = rs.getString(2); // rule public IP
+                rule[2] = rs.getString(3); // rule public port
+                rule[3] = rs.getString(4); // rule private Ip
+                rule[4] = rs.getString(5); // rule private port
+                rule[5] = rs.getString(6); // rule protocol
+                rules.add(rule);
+            }
+            rs.close();
+            pstmt.close();
+
+            if (!rules.isEmpty()) {
+                s_logger.debug("Found " + rules.size() + " port forwarding rules to upgrade");
+                for (Object[] rule : rules) {
+                    long id = (Long) rule[0];
+                    String sourcePort = (String) rule[2];
+                    String protocol = (String) rule[5];
+                    String publicIp = (String) rule[1];
+
+                    pstmt = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");
+                    pstmt.setString(1, publicIp);
+                    rs = pstmt.executeQuery();
+
+                    if (!rs.next()) {
+                        s_logger.error("Unable to find public IP address " + publicIp);
+                        throw new CloudRuntimeException("Unable to find public IP address " + publicIp);
+                    }
+
+                    int ipAddressId = rs.getInt(1);
+                    long accountId = rs.getLong(2);
+                    long domainId = rs.getLong(3);
+                    long networkId = rs.getLong(4);
+                    String privateIp = (String) rule[3];
+
+                    rs.close();
+                    pstmt.close();
+
+                    // update port_forwarding_rules table
+                    s_logger.trace("Updating port_forwarding_rules table...");
+                    pstmt = conn.prepareStatement("SELECT instance_id FROM nics where network_id=? AND ip4_address=?");
+                    pstmt.setLong(1, networkId);
+                    pstmt.setString(2, privateIp);
+                    rs = pstmt.executeQuery();
+
+                    if (!rs.next()) {
+                        // the vm might be expunged already...so just give the warning
+                        s_logger.warn("Unable to find vmId for private ip address " + privateIp + " for account id=" + accountId + "; assume that the vm is expunged");
+                        // throw new CloudRuntimeException("Unable to find vmId for private ip address " + privateIp +
+                        // " for account id=" + accountId);
+                    } else {
+                        long instanceId = rs.getLong(1);
+                        s_logger.debug("Instance id is " + instanceId);
+                        // update firewall_rules table
+                        s_logger.trace("Updating firewall_rules table as a part of PF rules upgrade...");
+                        pstmt = conn
+                                .prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'PortForwarding',       ?,      ?,      ?,      ?,       0,     now())");
+                        pstmt.setLong(1, id);
+                        pstmt.setInt(2, ipAddressId);
+                        pstmt.setInt(3, Integer.valueOf(sourcePort.trim()));
+                        pstmt.setInt(4, Integer.valueOf(sourcePort.trim()));
+                        pstmt.setString(5, protocol);
+                        pstmt.setLong(6, accountId);
+                        pstmt.setLong(7, domainId);
+                        pstmt.setLong(8, networkId);
+                        pstmt.setString(9, UUID.randomUUID().toString());
+                        pstmt.executeUpdate();
+                        pstmt.close();
+                        s_logger.trace("firewall_rules table is updated as a part of PF rules upgrade");
+
+                        rs.close();
+                        pstmt.close();
+
+                        String privatePort = (String) rule[4];
+                        pstmt = conn.prepareStatement("INSERT INTO port_forwarding_rules VALUES (?,    ?,      ?,      ?,       ?)");
+                        pstmt.setLong(1, id);
+                        pstmt.setLong(2, instanceId);
+                        pstmt.setString(3, privateIp);
+                        pstmt.setInt(4, Integer.valueOf(privatePort.trim()));
+                        pstmt.setInt(5, Integer.valueOf(privatePort.trim()));
+                        pstmt.executeUpdate();
+                        pstmt.close();
+                        s_logger.trace("port_forwarding_rules table is updated");
+
+                    }
+
+                }
+            }
+            s_logger.debug("Port forwarding rules are updated");
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't update port forwarding rules ", e);
+        }
+    }
+
+    public void upgradeLoadBalancingRules(Connection conn) {
+        try {
+            PreparedStatement pstmt = conn.prepareStatement("SELECT name, ip_address, public_port, private_port, algorithm, id FROM load_balancer");
+            ResultSet rs = pstmt.executeQuery();
+            ArrayList<Object[]> lbs = new ArrayList<Object[]>();
+            while (rs.next()) {
+                Object[] lb = new Object[10];
+                lb[0] = rs.getString(1); // lb name
+                lb[1] = rs.getString(2); // lb public IP
+                lb[2] = rs.getString(3); // lb public port
+                lb[3] = rs.getString(4); // lb private port
+                lb[4] = rs.getString(5); // lb algorithm
+                lb[5] = rs.getLong(6); // lb Id
+                lbs.add(lb);
+            }
+            rs.close();
+            pstmt.close();
+
+            if (!lbs.isEmpty()) {
+                s_logger.debug("Found " + lbs.size() + " lb rules to upgrade");
+                pstmt = conn.prepareStatement("SELECT id FROM firewall_rules order by id");
+                rs = pstmt.executeQuery();
+                long newLbId = 0;
+                while (rs.next()) {
+                    newLbId = rs.getLong(1);
+                }
+                rs.close();
+                pstmt.close();
+
+                for (Object[] lb : lbs) {
+                    String name = (String) lb[0];
+                    String publicIp = (String) lb[1];
+                    String sourcePort = (String) lb[2];
+                    String destPort = (String) lb[3];
+                    String algorithm = (String) lb[4];
+                    Long originalLbId = (Long) lb[5];
+                    newLbId = newLbId + 1;
+
+                    pstmt = conn.prepareStatement("SELECT id, account_id, domain_id, network_id FROM user_ip_address WHERE public_ip_address=?");
+                    pstmt.setString(1, publicIp);
+                    rs = pstmt.executeQuery();
+
+                    if (!rs.next()) {
+                        s_logger.warn("Unable to find public IP address " + publicIp + "; skipping lb rule id=" + originalLbId
+                                + " from update. Cleaning it up from load_balancer_vm_map and load_balancer table");
+                        pstmt = conn.prepareStatement("DELETE from load_balancer_vm_map where load_balancer_id=?");
+                        pstmt.setLong(1, originalLbId);
+                        pstmt.executeUpdate();
+
+                        pstmt = conn.prepareStatement("DELETE from load_balancer where id=?");
+                        pstmt.setLong(1, originalLbId);
+                        pstmt.executeUpdate();
+
+                        continue;
+                    }
+
+                    int ipAddressId = rs.getInt(1);
+                    long accountId = rs.getLong(2);
+                    long domainId = rs.getLong(3);
+                    long networkId = rs.getLong(4);
+
+                    rs.close();
+                    pstmt.close();
+
+                    // update firewall_rules table
+                    s_logger.trace("Updating firewall_rules table as a part of LB rules upgrade...");
+                    pstmt = conn
+                            .prepareStatement("INSERT INTO firewall_rules (id, ip_address_id, start_port, end_port, state, protocol, purpose, account_id, domain_id, network_id, xid, is_static_nat, created) VALUES (?,    ?,      ?,      ?,      'Active',        ?,     'LoadBalancing',       ?,      ?,      ?,      ?,       0,       now())");
+                    pstmt.setLong(1, newLbId);
+                    pstmt.setInt(2, ipAddressId);
+                    pstmt.setInt(3, Integer.valueOf(sourcePort));
+                    pstmt.setInt(4, Integer.valueOf(sourcePort));
+                    pstmt.setString(5, "tcp");
+                    pstmt.setLong(6, accountId);
+                    pstmt.setLong(7, domainId);
+                    pstmt.setLong(8, networkId);
+                    pstmt.setString(9, UUID.randomUUID().toString());
+                    pstmt.executeUpdate();
+                    pstmt.close();
+                    s_logger.trace("firewall_rules table is updated as a part of LB rules upgrade");
+
+                    // update load_balancing_rules
+                    s_logger.trace("Updating load_balancing_rules table as a part of LB rules upgrade...");
+                    pstmt = conn.prepareStatement("INSERT INTO load_balancing_rules VALUES (?,      ?,      NULL,      ?,       ?,      ?)");
+                    pstmt.setLong(1, newLbId);
+                    pstmt.setString(2, name);
+                    pstmt.setInt(3, Integer.valueOf(destPort));
+                    pstmt.setInt(4, Integer.valueOf(destPort));
+                    pstmt.setString(5, algorithm);
+                    pstmt.executeUpdate();
+                    pstmt.close();
+                    s_logger.trace("load_balancing_rules table is updated as a part of LB rules upgrade");
+
+                    // update load_balancer_vm_map table
+                    s_logger.trace("Updating load_balancer_vm_map table as a part of LB rules upgrade...");
+                    pstmt = conn.prepareStatement("SELECT instance_id FROM load_balancer_vm_map WHERE load_balancer_id=?");
+                    pstmt.setLong(1, originalLbId);
+                    rs = pstmt.executeQuery();
+                    ArrayList<Object[]> lbMaps = new ArrayList<Object[]>();
+                    while (rs.next()) {
+                        Object[] lbMap = new Object[10];
+                        lbMap[0] = rs.getLong(1); // instanceId
+                        lbMaps.add(lbMap);
+                    }
+                    rs.close();
+                    pstmt.close();
+
+                    pstmt = conn.prepareStatement("UPDATE load_balancer_vm_map SET load_balancer_id=? WHERE load_balancer_id=?");
+                    pstmt.setLong(1, newLbId);
+                    pstmt.setLong(2, originalLbId);
+                    pstmt.executeUpdate();
+                    pstmt.close();
+
+                    s_logger.trace("load_balancer_vm_map table is updated as a part of LB rules upgrade");
+                }
+            }
+            s_logger.debug("LB rules are upgraded");
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't update LB rules ", e);
+        }
+    }
+
+    private void upgradeHostMemoryCapacityInfo(Connection conn) {
+        try {
+            // count user_vm memory info (M Bytes)
+            PreparedStatement pstmt = conn
+                    .prepareStatement("select h.id, sum(s.ram_size) from host h, vm_instance v, service_offering s where h.type='Routing' and v.state='Running' and v.`type`='User' and v.host_id=h.id  and v.service_offering_id = s.id group by h.id");
+
+            ResultSet rs = pstmt.executeQuery();
+            Map<Long, Long> hostUsedMemoryInfo = new HashMap<Long, Long>();
+            while (rs.next()) {
+                hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2));
+            }
+            rs.close();
+            pstmt.close();
+
+            int proxyRamSize = NumbersUtil.parseInt(getConfigValue(conn, "consoleproxy.ram.size"), 1024); // ConsoleProxyManager.DEFAULT_PROXY_VM_RAMSIZE);
+            int domrRamSize = NumbersUtil.parseInt(getConfigValue(conn, "router.ram.size"), 128); // VpcVirtualNetworkApplianceManager.DEFAULT_ROUTER_VM_RAMSIZE);
+            int ssvmRamSize = NumbersUtil.parseInt(getConfigValue(conn, "secstorage.vm.ram.size"), 256); // SecondaryStorageVmManager.DEFAULT_SS_VM_RAMSIZE);
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='ConsoleProxy' and v.host_id=h.id group by h.id");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
+                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * proxyRamSize + usedMem);
+                } else {
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * proxyRamSize);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='DomainRouter' and v.host_id=h.id group by h.id");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
+                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * domrRamSize + usedMem);
+                } else {
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * domrRamSize);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='SecondaryStorageVm' and v.host_id=h.id group by h.id");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedMemoryInfo.get(rs.getLong(1)) != null) {
+                    Long usedMem = hostUsedMemoryInfo.get(rs.getLong(1));
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * ssvmRamSize + usedMem);
+                } else {
+                    hostUsedMemoryInfo.put(rs.getLong(1), rs.getLong(2) * ssvmRamSize);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            for (Map.Entry<Long, Long> entry : hostUsedMemoryInfo.entrySet()) {
+                pstmt = conn.prepareStatement("update op_host_capacity set used_capacity=? where host_id=? and capacity_type=0");
+                pstmt.setLong(1, entry.getValue() * 1024 * 1024);
+                pstmt.setLong(2, entry.getKey());
+
+                pstmt.executeUpdate();
+            }
+
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't upgrade host capacity info ", e);
+        }
+    }
+
+    // per domain resource counts is introduced from 2.2 so we need to evaluate the domain limits
+    // from the resource counts of account, and account-domain relation for all resource types
+    private void upgradeDomainResourceCounts(Connection conn) {
+        upgradeDomainResourceCounts(conn, ResourceType.volume);
+        upgradeDomainResourceCounts(conn, ResourceType.user_vm);
+        upgradeDomainResourceCounts(conn, ResourceType.snapshot);
+        upgradeDomainResourceCounts(conn, ResourceType.template);
+        upgradeDomainResourceCounts(conn, ResourceType.public_ip);
+    }
+
+    public static void upgradeDomainResourceCounts(Connection conn, ResourceType resourceType) {
+        try {
+
+            PreparedStatement account_count_pstmt = conn.prepareStatement("SELECT account_id, count from resource_count where type='" + resourceType + "'");
+            ResultSet rs_account_count = account_count_pstmt.executeQuery();
+
+            while (rs_account_count.next()) {
+                Long accountId = rs_account_count.getLong(1);
+                Long accountCount = rs_account_count.getLong(2);
+
+                PreparedStatement account_pstmt = conn.prepareStatement("SELECT domain_id from account where id=?");
+                account_pstmt.setLong(1, accountId);
+                ResultSet rs_domain = account_pstmt.executeQuery();
+
+                if (!rs_domain.next()) {
+                    throw new CloudRuntimeException("Unable to get the domain for the account Id: " + accountId);
+                }
+                Long domainId = rs_domain.getLong(1);
+
+                rs_domain.close();
+                account_pstmt.close();
+
+                // resource count on a domain is aggregate of resource count of all the accounts that belong to the domain and
+                // its sub-domains.
+                // so propagate the count across the domain hierarchy all the way up to the root domain.
+                while (domainId != 0) {
+
+                    PreparedStatement domain_count_pstmt = conn.prepareStatement("SELECT count from resource_count where type='" + resourceType + "' and domain_id=?");
+                    domain_count_pstmt.setLong(1, domainId);
+                    ResultSet rs_domain_count = domain_count_pstmt.executeQuery();
+
+                    // if a row has been created for the domain in the resource_count table, add the count to the existing
+                    // domain count
+                    if (rs_domain_count.next()) {
+                        Long domainCount = rs_domain_count.getLong(1);
+                        domainCount = domainCount + accountCount;
+                        PreparedStatement update_domain_count_pstmt = conn.prepareStatement("UPDATE resource_count set count=? where domain_id=? and type ='" + resourceType + "'");
+                        update_domain_count_pstmt.setLong(1, domainCount);
+                        update_domain_count_pstmt.setLong(2, domainId);
+                        update_domain_count_pstmt.executeUpdate();
+                        update_domain_count_pstmt.close();
+                    } else {
+                        PreparedStatement update_domain_count_pstmt = conn.prepareStatement("INSERT INTO resource_count (type, count, domain_id) VALUES (?,?,?)");
+                        update_domain_count_pstmt.setString(1, resourceType.getName());
+                        update_domain_count_pstmt.setLong(2, accountCount);
+                        update_domain_count_pstmt.setLong(3, domainId);
+                        update_domain_count_pstmt.executeUpdate();
+                        update_domain_count_pstmt.close();
+                    }
+
+                    rs_domain_count.close();
+                    domain_count_pstmt.close();
+
+                    PreparedStatement parentDomain_pstmt = conn.prepareStatement("SELECT parent from domain where id=?");
+                    parentDomain_pstmt.setLong(1, domainId);
+                    ResultSet rs_domain_parent = parentDomain_pstmt.executeQuery();
+
+                    if (rs_domain_parent.next()) {
+                        domainId = rs_domain_parent.getLong(1);
+                    } else {
+                        throw new CloudRuntimeException("Unable to get the parent domain for the domain Id: " + domainId);
+                    }
+
+                    rs_domain_parent.close();
+                    parentDomain_pstmt.close();
+                }
+            }
+
+            rs_account_count.close();
+            account_count_pstmt.close();
+
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't upgrade domain resource counts ", e);
+        }
+    }
+
+    private void upgradeHostCpuCapacityInfo(Connection conn) {
+        try {
+            // count user_vm memory info (M Bytes)
+            PreparedStatement pstmt = conn
+                    .prepareStatement("select h.id, sum(s.speed*s.cpu) from host h, vm_instance v, service_offering s where h.type='Routing' and v.state='Running' and v.`type`='User' and v.host_id=h.id  and v.service_offering_id = s.id group by h.id");
+
+            ResultSet rs = pstmt.executeQuery();
+            Map<Long, Long> hostUsedCpuInfo = new HashMap<Long, Long>();
+            while (rs.next()) {
+                hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2));
+            }
+            rs.close();
+            pstmt.close();
+
+            int proxyCpuMhz = NumbersUtil.parseInt(getConfigValue(conn, "consoleproxy.cpu.mhz"), 500); // ConsoleProxyManager.DEFAULT_PROXY_VM_CPUMHZ);
+            int domrCpuMhz = NumbersUtil.parseInt(getConfigValue(conn, "router.cpu.mhz"), 500); // VpcVirtualNetworkApplianceManager.DEFAULT_ROUTER_CPU_MHZ);
+            int ssvmCpuMhz = NumbersUtil.parseInt(getConfigValue(conn, "secstorage.vm.cpu.mhz"), 500); // SecondaryStorageVmManager.DEFAULT_SS_VM_CPUMHZ);
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='ConsoleProxy' and v.host_id=h.id group by h.id");
+
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedCpuInfo.get(rs.getLong(1)) != null) {
+                    Long usedCpuMhz = hostUsedCpuInfo.get(rs.getLong(1));
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * proxyCpuMhz + usedCpuMhz);
+                } else {
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * proxyCpuMhz);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='DomainRouter' and v.host_id=h.id group by h.id");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedCpuInfo.get(rs.getLong(1)) != null) {
+                    Long usedCpuMhz = hostUsedCpuInfo.get(rs.getLong(1));
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * domrCpuMhz + usedCpuMhz);
+                } else {
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * domrCpuMhz);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn
+                    .prepareStatement("select h.id, count(v.id) from host h, vm_instance v where h.type='Routing' and v.state='Running' and v.`type`='SecondaryStorageVm' and v.host_id=h.id group by h.id");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                if (hostUsedCpuInfo.get(rs.getLong(1)) != null) {
+                    Long usedCpuMhz = hostUsedCpuInfo.get(rs.getLong(1));
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * ssvmCpuMhz + usedCpuMhz);
+                } else {
+                    hostUsedCpuInfo.put(rs.getLong(1), rs.getLong(2) * ssvmCpuMhz);
+                }
+            }
+            rs.close();
+            pstmt.close();
+
+            for (Map.Entry<Long, Long> entry : hostUsedCpuInfo.entrySet()) {
+                pstmt = conn.prepareStatement("update op_host_capacity set used_capacity=? where host_id=? and capacity_type=1");
+                pstmt.setLong(1, entry.getValue());
+                pstmt.setLong(2, entry.getKey());
+
+                pstmt.executeUpdate();
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't upgrade host capacity info ", e);
+        }
+    }
+
+    private String getConfigValue(Connection conn, String name) {
+        try {
+            // count user_vm memory info (M Bytes)
+            PreparedStatement pstmt = conn.prepareStatement("select value from configuration where name=?");
+            pstmt.setString(1, name);
+            ResultSet rs = pstmt.executeQuery();
+
+            String val = null;
+            if (rs.next()) {
+                val = rs.getString(1);
+            }
+            rs.close();
+            pstmt.close();
+
+            return val;
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Can't upgrade host capacity info ", e);
+        }
+    }
+
+    private void migrateEvents(Connection conn) {
+        try {
+            PreparedStatement pstmt1 = conn.prepareStatement("SHOW DATABASES LIKE 'cloud_usage'");
+            ResultSet rs1 = pstmt1.executeQuery();
+            if (!rs1.next()) {
+                s_logger.debug("cloud_usage db doesn't exist. Skipping events migration");
+                return;
+            }
+
+            // get last processed event Id
+            Long lastProcessedEvent = getMostRecentEvent(conn);
+            // Events not yet processed
+            String sql = "SELECT type, description, user_id, account_id, created, level, parameters FROM cloud.event vmevt WHERE vmevt.id > ? and vmevt.state = 'Completed' ";
+            if (lastProcessedEvent == null) {
+                s_logger.trace("no events are processed earlier, copying all events");
+                sql = "SELECT type, description, user_id, account_id, created, level, parameters FROM cloud.event vmevt WHERE vmevt.state = 'Completed' ";
+            }
+
+            PreparedStatement pstmt = null;
+
+            pstmt = conn.prepareStatement(sql);
+            int i = 1;
+            if (lastProcessedEven

<TRUNCATED>

Mime
View raw message