cloudstack-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From alena1...@apache.org
Subject [27/90] [abbrv] [partial] Moved most of the VOs and DAOs from server package into engine-schema as well
Date Wed, 08 May 2013 20:47:53 GMT
http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade302to40.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade302to40.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade302to40.java
new file mode 100644
index 0000000..753f64e
--- /dev/null
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade302to40.java
@@ -0,0 +1,1033 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package com.cloud.upgrade.dao;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.UUID;
+
+import org.apache.log4j.Logger;
+
+import com.cloud.utils.crypt.DBEncryptionUtil;
+import com.cloud.utils.exception.CloudRuntimeException;
+import com.cloud.utils.script.Script;
+import com.cloud.dc.DataCenter.NetworkType;
+
+public class Upgrade302to40 extends Upgrade30xBase implements DbUpgrade {
+    final static Logger s_logger = Logger.getLogger(Upgrade302to40.class);
+
+    @Override
+    public String[] getUpgradableVersionRange() {
+        return new String[] { "3.0.2", "4.0.0" };
+    }
+
+    @Override
+    public String getUpgradedVersion() {
+        return "4.0.0";
+    }
+
+    @Override
+    public boolean supportsRollingUpgrade() {
+        return false;
+    }
+
+    @Override
+    public File[] getPrepareScripts() {
+        String script = Script.findScript("", "db/schema-302to40.sql");
+        if (script == null) {
+            throw new CloudRuntimeException("Unable to find db/schema-302to40.sql");
+        }
+
+        return new File[] { new File(script) };
+    }
+
+    @Override
+    public void performDataMigration(Connection conn) {
+        correctVRProviders(conn);
+        correctMultiplePhysicaNetworkSetups(conn);
+        addHostDetailsUniqueKey(conn);
+        addVpcProvider(conn);
+        updateRouterNetworkRef(conn);
+        fixForeignKeys(conn);
+        setupExternalNetworkDevices(conn);
+        fixZoneUsingExternalDevices(conn);
+    }
+
+    @Override
+    public File[] getCleanupScripts() {
+        String script = Script.findScript("", "db/schema-302to40-cleanup.sql");
+        if (script == null) {
+            throw new CloudRuntimeException("Unable to find db/schema-302to40-cleanup.sql");
+        }
+
+        return new File[] { new File(script) };
+    }
+    
+    private void correctVRProviders(Connection conn) {
+        PreparedStatement pstmtVR = null;
+        ResultSet rsVR = null;
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        
+        try{
+            pstmtVR = conn.prepareStatement("SELECT id, nsp_id FROM `cloud`.`virtual_router_providers` where type = 'VirtualRouter' AND removed IS NULL");
+            rsVR = pstmtVR.executeQuery();
+            while (rsVR.next()) {
+                long vrId = rsVR.getLong(1);
+                long nspId = rsVR.getLong(2);
+                
+                //check that this nspId points to a VR provider.
+                pstmt = conn.prepareStatement("SELECT  physical_network_id, provider_name FROM `cloud`.`physical_network_service_providers` where id = ?");
+                pstmt.setLong(1, nspId);
+                rs = pstmt.executeQuery();
+                if(rs.next()){
+                    long physicalNetworkId = rs.getLong(1);
+                    String providerName = rs.getString(2);
+                    if(!providerName.equalsIgnoreCase("VirtualRouter")){
+                        //mismatch, correct the nsp_id in VR
+                        PreparedStatement pstmt1 = null;
+                        ResultSet rs1 = null;
+                        pstmt1 = conn.prepareStatement("SELECT  id FROM `cloud`.`physical_network_service_providers` where physical_network_id = ? AND provider_name = ? AND removed IS NULL");
+                        pstmt1.setLong(1, physicalNetworkId);
+                        pstmt1.setString(2, "VirtualRouter");
+                        rs1 = pstmt1.executeQuery();
+                        if(rs1.next()){
+                            long correctNSPId = rs1.getLong(1);
+                            
+                            //update VR entry
+                            PreparedStatement pstmtUpdate = null;
+                            String updateNSPId = "UPDATE `cloud`.`virtual_router_providers` SET nsp_id = ? WHERE id = ?";
+                            pstmtUpdate = conn.prepareStatement(updateNSPId);
+                            pstmtUpdate.setLong(1, correctNSPId);
+                            pstmtUpdate.setLong(2, vrId);
+                            pstmtUpdate.executeUpdate();
+                            pstmtUpdate.close();
+                        }
+                        rs1.close();
+                        pstmt1.close();
+                    }
+                }
+                rs.close();
+                pstmt.close();
+            }
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while correcting Virtual Router Entries", e);
+        } finally {
+            if (rsVR != null) {
+                try {
+                    rsVR.close();
+                }catch (SQLException e) {
+                }
+            }
+            
+            if (pstmtVR != null) {
+                try {
+                    pstmtVR.close();
+                } catch (SQLException e) {
+                }
+            }
+            
+            if (rs != null) {
+                try {
+                    rs.close();
+                }catch (SQLException e) {
+                }
+            }
+            
+            if (pstmt != null) {
+                try {
+                    pstmt.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+        
+    }
+
+    private void correctMultiplePhysicaNetworkSetups(Connection conn) {
+        PreparedStatement pstmtZone = null;
+        ResultSet rsZone = null;
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        
+        try{
+    
+            //check if multiple physical networks with 'Guest' Traffic types are present
+            //Yes: 
+            //1) check if there are guest networks without tags, if yes then add a new physical network with default tag for them
+            //2) Check if there are physical network tags present
+                //No: Add unique tag to each physical network
+            //3) Get all guest networks unique network offering id's  
+          
+            //Clone each for each physical network and add the tag.
+            //add ntwk service map entries
+            //update all guest networks of 1 physical network having this offering id to this new offering id
+            
+            pstmtZone = conn.prepareStatement("SELECT id, domain_id, networktype, name, uuid FROM `cloud`.`data_center`");
+            rsZone = pstmtZone.executeQuery();
+            while (rsZone.next()) {
+                long zoneId = rsZone.getLong(1);
+                Long domainId = rsZone.getLong(2);
+                String networkType = rsZone.getString(3);
+                String zoneName = rsZone.getString(4);
+                String uuid = rsZone.getString(5);
+                
+                PreparedStatement pstmtUpdate = null;
+                if(uuid == null){
+                    uuid = UUID.randomUUID().toString();
+                    String updateUuid = "UPDATE `cloud`.`data_center` SET uuid = ? WHERE id = ?";
+                    pstmtUpdate = conn.prepareStatement(updateUuid);
+                    pstmtUpdate.setString(1, uuid);
+                    pstmtUpdate.setLong(2, zoneId);
+                    pstmtUpdate.executeUpdate();
+                    pstmtUpdate.close();
+                }
+                
+                //check if any networks were untagged and remaining to be mapped to a physical network
+                
+                pstmt = conn.prepareStatement("SELECT count(n.id) FROM networks n WHERE n.physical_network_id IS NULL AND n.traffic_type = 'Guest' and n.data_center_id = ? and n.removed is null");
+                pstmt.setLong(1, zoneId);
+                rs = pstmt.executeQuery();
+                if(rs.next()){
+                    Long count = rs.getLong(1);
+                    if(count > 0){
+                        // find the default tag to use from global config or use 'cloud-private'
+                        String xenGuestLabel = getNetworkLabelFromConfig(conn, "xen.guest.network.device");
+                        //Decrypt this value.
+                        xenGuestLabel = DBEncryptionUtil.decrypt(xenGuestLabel);
+                        
+                        //make sure that no physical network with this traffic label already exists. if yes, error out.
+                        if(xenGuestLabel != null){
+                            PreparedStatement pstmt5 = conn.prepareStatement("SELECT count(*) FROM `cloud`.`physical_network_traffic_types` pntt JOIN `cloud`.`physical_network` pn ON pntt.physical_network_id = pn.id WHERE pntt.traffic_type ='Guest' AND pn.data_center_id = ? AND pntt.xen_network_label = ?");
+                            pstmt5.setLong(1, zoneId);
+                            pstmt5.setString(2, xenGuestLabel);
+                            ResultSet rsSameLabel = pstmt5.executeQuery();
+                            
+                            if(rsSameLabel.next()){
+                                Long sameLabelcount = rsSameLabel.getLong(1);
+                                if(sameLabelcount >  0){
+                                    s_logger.error("There are untagged networks for which we need to add a physical network with Xen traffic label = 'xen.guest.network.device' config value, which is: "+xenGuestLabel);
+                                    s_logger.error("However already there are "+sameLabelcount+" physical networks setup with same traffic label, cannot upgrade");
+                                    throw new CloudRuntimeException("Cannot upgrade this setup since a physical network with same traffic label: "+xenGuestLabel+" already exists, Please check logs and contact Support.");
+                                }
+                            }
+                        }
+                        
+                        //Create a physical network with guest traffic type and this tag
+                        long physicalNetworkId = addPhysicalNetworkToZone(conn, zoneId, zoneName, networkType, null, domainId);
+                        addTrafficType(conn, physicalNetworkId, "Guest", xenGuestLabel, null, null);
+                        addDefaultVRProvider(conn, physicalNetworkId, zoneId);
+                        addDefaultSGProvider(conn, physicalNetworkId, zoneId, networkType, true);
+                        
+                        PreparedStatement pstmt3 = conn.prepareStatement("SELECT n.id FROM networks n WHERE n.physical_network_id IS NULL AND n.traffic_type = 'Guest' and n.data_center_id = ? and n.removed is null");
+                        pstmt3.setLong(1, zoneId);
+                        ResultSet rsNet = pstmt3.executeQuery();
+                        s_logger.debug("Adding PhysicalNetwork to VLAN");
+                        s_logger.debug("Adding PhysicalNetwork to user_ip_address");
+                        s_logger.debug("Adding PhysicalNetwork to networks");
+                        while(rsNet.next()){
+                            Long networkId = rsNet.getLong(1);
+                            addPhysicalNtwk_To_Ntwk_IP_Vlan(conn, physicalNetworkId,networkId);
+                        }
+                        rsNet.close();
+                        pstmt3.close();
+                    }
+                }
+                rs.close();
+                pstmt.close();
+
+                
+                boolean multiplePhysicalNetworks = false;
+                
+                pstmt = conn.prepareStatement("SELECT count(*) FROM `cloud`.`physical_network_traffic_types` pntt JOIN `cloud`.`physical_network` pn ON pntt.physical_network_id = pn.id WHERE pntt.traffic_type ='Guest' and pn.data_center_id = ?");
+                pstmt.setLong(1, zoneId);
+                rs = pstmt.executeQuery();
+                if(rs.next()){
+                    Long count = rs.getLong(1);
+                    if(count >  1){
+                        s_logger.debug("There are "+count+" physical networks setup");
+                        multiplePhysicalNetworks = true;
+                    }
+                }
+                rs.close();
+                pstmt.close();
+    
+                if(multiplePhysicalNetworks){
+                    //check if guest vnet is wrongly configured by earlier upgrade. If yes error out
+                    //check if any vnet is allocated and guest networks are using vnet But the physical network id does not match on the vnet and guest network.
+                    PreparedStatement pstmt4 = conn.prepareStatement("SELECT v.id, v.vnet, v.reservation_id, v.physical_network_id as vpid, n.id, n.physical_network_id as npid FROM `cloud`.`op_dc_vnet_alloc` v JOIN `cloud`.`networks` n ON CONCAT('vlan://' , v.vnet) = n.broadcast_uri WHERE v.taken IS NOT NULL AND v.data_center_id = ? AND n.removed IS NULL AND v.physical_network_id !=  n.physical_network_id");
+                    pstmt4.setLong(1, zoneId);
+                    ResultSet rsVNet = pstmt4.executeQuery();
+                    if(rsVNet.next()){
+                        String vnet = rsVNet.getString(2);
+                        String networkId = rsVNet.getString(5);
+                        String vpid = rsVNet.getString(4);
+                        String npid = rsVNet.getString(6);
+                        s_logger.error("Guest Vnet assignment is set wrongly . Cannot upgrade until that is corrected. Example- Vnet: "+ vnet +" has physical network id: " + vpid +" ,but the guest network: " +networkId+" that uses it has physical network id: " +npid );
+                        
+                        String message = "Cannot upgrade. Your setup has multiple Physical Networks and is using guest Vnet that is assigned wrongly. To upgrade, first correct the setup by doing the following: \n" +
+                        "1. Please rollback to your 2.2.14 setup\n" +
+                        "2. Please stop all VMs using isolated(virtual) networks through CloudStack\n" +
+                        "3. Run following query to find if any networks still have nics allocated:\n\t"+
+                        "a) check if any virtual guest networks still have allocated nics by running:\n\t" +
+                        "SELECT DISTINCT op.id from `cloud`.`op_networks` op JOIN `cloud`.`networks` n on op.id=n.id WHERE nics_count != 0 AND guest_type = 'Virtual';\n\t"+ 
+                        "b) If this returns any networkd ids, then ensure that all VMs are stopped, no new VM is being started, and then shutdown management server\n\t"+
+                        "c) Clean up the nics count for the 'virtual' network id's returned in step (a) by running this:\n\t"+
+                        "UPDATE `cloud`.`op_networks` SET nics_count = 0 WHERE  id = <enter id of virtual network>\n\t"+
+                        "d) Restart management server and wait for all networks to shutdown. [Networks shutdown will be determined by network.gc.interval and network.gc.wait seconds] \n"+
+                        "4. Please ensure all networks are shutdown and all guest Vnet's are free.\n" +
+                        "5. Run upgrade. This will allocate all your guest vnet range to first physical network.  \n" +
+                        "6. Reconfigure the vnet ranges for each physical network as desired by using updatePhysicalNetwork API \n" +
+                        "7. Start all your VMs";
+                        
+                        s_logger.error(message);
+                        throw new CloudRuntimeException("Cannot upgrade this setup since Guest Vnet assignment to the multiple physical networks is incorrect. Please check the logs for details on how to proceed");
+                        
+                    }
+                    rsVNet.close();
+                    pstmt4.close();
+                    
+                    //Clean up any vnets that have no live networks/nics
+                    pstmt4 = conn.prepareStatement("SELECT v.id, v.vnet, v.reservation_id FROM `cloud`.`op_dc_vnet_alloc` v LEFT JOIN networks n ON CONCAT('vlan://' , v.vnet) = n.broadcast_uri WHERE v.taken IS NOT NULL AND v.data_center_id = ? AND n.broadcast_uri IS NULL AND n.removed IS NULL");
+                    pstmt4.setLong(1, zoneId);
+                    rsVNet = pstmt4.executeQuery();
+                    while(rsVNet.next()){
+                        Long vnet_id = rsVNet.getLong(1);
+                        String vnetValue = rsVNet.getString(2);
+                        String reservationId = rsVNet.getString(3);
+                        //does this vnet have any nic associated?
+                        PreparedStatement pstmt5 = conn.prepareStatement("SELECT id, instance_id FROM `cloud`.`nics` where broadcast_uri = ? and removed IS NULL");
+                        String uri = "vlan://"+vnetValue;
+                        pstmt5.setString(1, uri);
+                        ResultSet rsNic = pstmt5.executeQuery();
+                        Long nic_id = rsNic.getLong(1);
+                        Long instance_id = rsNic.getLong(2);
+                        if(rsNic.next()){
+                            throw new CloudRuntimeException("Cannot upgrade. Please cleanup the guest vnet: "+ vnetValue +" , it is being used by nic_id: "+ nic_id +" , instance_id: " + instance_id );
+                        }
+                        
+                        //free this vnet
+                        String freeVnet = "UPDATE `cloud`.`op_dc_vnet_alloc` SET account_id = NULL, taken = NULL, reservation_id = NULL WHERE id = ?";
+                        pstmtUpdate = conn.prepareStatement(freeVnet);
+                        pstmtUpdate.setLong(1, vnet_id);
+                        pstmtUpdate.executeUpdate();
+                        pstmtUpdate.close();
+                    }
+                    rsVNet.close();
+                    pstmt4.close();
+
+                    
+                    //add tags to the physical networks if not present and clone offerings
+                    
+                    pstmt = conn.prepareStatement("SELECT pn.id as pid , ptag.tag as tag FROM `cloud`.`physical_network` pn LEFT JOIN `cloud`.`physical_network_tags` ptag ON pn.id = ptag.physical_network_id where pn.data_center_id = ?");
+                    pstmt.setLong(1, zoneId);
+                    rs = pstmt.executeQuery();
+                    while(rs.next()){
+                        long physicalNetworkId = rs.getLong("pid");
+                        String tag = rs.getString("tag");
+                        if(tag == null){
+                            //need to add unique tag
+                            String newTag = "pNtwk-tag-" + physicalNetworkId;
+                            
+                            String updateVnet = "INSERT INTO `cloud`.`physical_network_tags`(tag, physical_network_id) VALUES( ?, ? )";
+                            pstmtUpdate = conn.prepareStatement(updateVnet);
+                            pstmtUpdate.setString(1, newTag);
+                            pstmtUpdate.setLong(2, physicalNetworkId);
+                            pstmtUpdate.executeUpdate();
+                            pstmtUpdate.close();
+                            
+                            //clone offerings and tag them with this new tag, if there are any guest networks for this physical network
+                            
+                            PreparedStatement pstmt2 = null;
+                            ResultSet rs2 = null;
+    
+                            pstmt2 = conn.prepareStatement("SELECT distinct network_offering_id FROM `cloud`.`networks` where traffic_type= 'Guest' and physical_network_id = ? and removed is null");
+                            pstmt2.setLong(1, physicalNetworkId);
+                            rs2 = pstmt2.executeQuery();
+                            
+                            while(rs2.next()){
+                                //clone each offering, add new tag, clone offering-svc-map, update guest networks with new offering id
+                                long networkOfferingId = rs2.getLong(1);
+                                cloneOfferingAndAddTag(conn, networkOfferingId, physicalNetworkId, newTag);
+                            }
+                            rs2.close();
+                            pstmt2.close();
+                        }
+                    }
+                    rs.close();
+                    pstmt.close();
+               }
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while correcting PhysicalNetwork setup", e);
+        } finally {
+            if (rsZone != null) {
+                try {
+                    rsZone.close();
+                }catch (SQLException e) {
+                }
+            }
+            
+            if (pstmtZone != null) {
+                try {
+                    pstmtZone.close();
+                } catch (SQLException e) {
+                }
+            }
+            
+            if (rs != null) {
+                try {
+                    rs.close();
+                }catch (SQLException e) {
+                }
+            }
+            
+            if (pstmt != null) {
+                try {
+                    pstmt.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+        
+    private void cloneOfferingAndAddTag(Connection conn, long networkOfferingId, long physicalNetworkId, String newTag) {
+
+        
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;        
+        try{
+            pstmt = conn.prepareStatement("select count(*) from `cloud`.`network_offerings`");
+            rs = pstmt.executeQuery();
+            long ntwkOffCount = 0;
+            while (rs.next()) {
+                ntwkOffCount = rs.getLong(1);
+            }
+            rs.close();
+            pstmt.close();
+            
+            pstmt = conn.prepareStatement("DROP TEMPORARY TABLE IF EXISTS `cloud`.`network_offerings2`");
+            pstmt.executeUpdate();
+            
+            pstmt = conn.prepareStatement("CREATE TEMPORARY TABLE `cloud`.`network_offerings2` ENGINE=MEMORY SELECT * FROM `cloud`.`network_offerings` WHERE id=1");
+            pstmt.executeUpdate();
+            pstmt.close();
+            
+            // clone the record to
+            pstmt = conn.prepareStatement("INSERT INTO `cloud`.`network_offerings2` SELECT * FROM `cloud`.`network_offerings` WHERE id=?");
+            pstmt.setLong(1, networkOfferingId);
+            pstmt.executeUpdate();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("SELECT unique_name FROM `cloud`.`network_offerings` WHERE id=?");
+            pstmt.setLong(1, networkOfferingId);
+            rs = pstmt.executeQuery();
+            String uniqueName = null;
+            while (rs.next()) {
+                uniqueName = rs.getString(1) + "-" + physicalNetworkId;
+            }
+            rs.close();
+            pstmt.close();
+            
+
+            pstmt = conn.prepareStatement("UPDATE `cloud`.`network_offerings2` SET id=?, unique_name=?, name=?, tags=?, uuid=?  WHERE id=?");
+            ntwkOffCount = ntwkOffCount + 1;
+            long newNetworkOfferingId = ntwkOffCount;
+            pstmt.setLong(1, newNetworkOfferingId);
+            pstmt.setString(2, uniqueName);
+            pstmt.setString(3, uniqueName);
+            pstmt.setString(4, newTag);
+            String uuid = UUID.randomUUID().toString();
+            pstmt.setString(5, uuid); 
+            pstmt.setLong(6, networkOfferingId);
+            pstmt.executeUpdate();
+            pstmt.close();
+            
+            pstmt = conn.prepareStatement("INSERT INTO `cloud`.`network_offerings` SELECT * from `cloud`.`network_offerings2` WHERE id=" + newNetworkOfferingId);
+            pstmt.executeUpdate();
+            pstmt.close();
+            
+            //clone service map
+            pstmt = conn.prepareStatement("select service, provider from `cloud`.`ntwk_offering_service_map` where network_offering_id=?");
+            pstmt.setLong(1, networkOfferingId);
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                String service = rs.getString(1);
+                String provider = rs.getString(2);
+                pstmt = conn.prepareStatement("INSERT INTO `cloud`.`ntwk_offering_service_map` (`network_offering_id`, `service`, `provider`, `created`) values (?,?,?, now())");
+                pstmt.setLong(1, newNetworkOfferingId);
+                pstmt.setString(2, service);
+                pstmt.setString(3, provider);
+                pstmt.executeUpdate();
+            }
+            rs.close();
+            pstmt.close();
+
+            pstmt = conn.prepareStatement("UPDATE `cloud`.`networks` SET network_offering_id=? where physical_network_id=? and traffic_type ='Guest' and network_offering_id="+networkOfferingId);
+            pstmt.setLong(1, newNetworkOfferingId);
+            pstmt.setLong(2, physicalNetworkId);
+            pstmt.executeUpdate();
+            pstmt.close();
+            
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while cloning NetworkOffering", e);
+        } finally {
+            try {
+                pstmt = conn.prepareStatement("DROP TEMPORARY TABLE `cloud`.`network_offerings2`");
+                pstmt.executeUpdate();
+            
+                if (rs != null) {
+                    rs.close();
+                }
+                
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            }catch (SQLException e) {
+            }
+        }
+    }
+
+    private void addHostDetailsUniqueKey(Connection conn) {
+        s_logger.debug("Checking if host_details unique key exists, if not we will add it");
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        try {
+            pstmt = conn.prepareStatement("SHOW INDEX FROM `cloud`.`host_details` WHERE KEY_NAME = 'uk_host_id_name'");
+            rs = pstmt.executeQuery();
+            if (rs.next()) {
+                s_logger.debug("Unique key already exists on host_details - not adding new one");
+            }else{
+                //add the key
+                PreparedStatement pstmtUpdate = conn.prepareStatement("ALTER IGNORE TABLE `cloud`.`host_details` ADD CONSTRAINT UNIQUE KEY `uk_host_id_name` (`host_id`, `name`)");
+                pstmtUpdate.executeUpdate();
+                s_logger.debug("Unique key did not exist on host_details -  added new one");
+                pstmtUpdate.close();
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Failed to check/update the host_details unique key ", e);
+        } finally {
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+    }
+
+    private void addVpcProvider(Connection conn){
+        //Encrypt config params and change category to Hidden
+        s_logger.debug("Adding vpc provider to all physical networks in the system");
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        try {
+            pstmt = conn.prepareStatement("SELECT id FROM `cloud`.`physical_network` WHERE removed is NULL");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                Long pNtwkId = rs.getLong(1);
+                
+                //insert provider
+                pstmt = conn.prepareStatement("INSERT INTO `cloud`.`physical_network_service_providers` " +
+                        "(`physical_network_id`, `provider_name`, `state`, `vpn_service_provided`, `dhcp_service_provided`, " +
+                        "`dns_service_provided`, `gateway_service_provided`, `firewall_service_provided`, `source_nat_service_provided`," +
+                        " `load_balance_service_provided`, `static_nat_service_provided`, `port_forwarding_service_provided`," +
+                        " `user_data_service_provided`, `security_group_service_provided`) " +
+                        "VALUES (?, 'VpcVirtualRouter', 'Enabled', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0)");
+                
+                pstmt.setLong(1, pNtwkId);
+                pstmt.executeUpdate();
+                
+                //get provider id
+                pstmt = conn.prepareStatement("SELECT id FROM `cloud`.`physical_network_service_providers` " +
+                        "WHERE physical_network_id=? and provider_name='VpcVirtualRouter'");
+                pstmt.setLong(1, pNtwkId);
+                ResultSet rs1 = pstmt.executeQuery();
+                rs1.next();
+                long providerId = rs1.getLong(1);
+                
+                //insert VR element
+                pstmt = conn.prepareStatement("INSERT INTO `cloud`.`virtual_router_providers` (`nsp_id`, `type`, `enabled`) " +
+                        "VALUES (?, 'VPCVirtualRouter', 1)");
+                pstmt.setLong(1, providerId);
+                pstmt.executeUpdate();
+                
+                s_logger.debug("Added VPC Virtual router provider for physical network id=" + pNtwkId);
+                
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable add VPC physical network service provider ", e);
+        } finally {
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+        s_logger.debug("Done adding VPC physical network service providers to all physical networks");        
+    }
+    
+    private void updateRouterNetworkRef(Connection conn){
+        //Encrypt config params and change category to Hidden
+        s_logger.debug("Updating router network ref");
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        try {
+            pstmt = conn.prepareStatement("SELECT d.id, d.network_id FROM `cloud`.`domain_router` d, `cloud`.`vm_instance` v " +
+                    "WHERE d.id=v.id AND v.removed is NULL");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                Long routerId = rs.getLong(1);
+                Long networkId = rs.getLong(2);
+                
+                //get the network type
+                pstmt = conn.prepareStatement("SELECT guest_type from `cloud`.`networks` where id=?");
+                pstmt.setLong(1, networkId);
+                ResultSet rs1 = pstmt.executeQuery();
+                rs1.next();
+                String networkType = rs1.getString(1);
+                
+                //insert the reference
+                pstmt = conn.prepareStatement("INSERT INTO `cloud`.`router_network_ref` (router_id, network_id, guest_type) " +
+                        "VALUES (?, ?, ?)");
+                
+                pstmt.setLong(1, routerId);
+                pstmt.setLong(2, networkId);
+                pstmt.setString(3, networkType);
+                pstmt.executeUpdate();
+
+                s_logger.debug("Added reference for router id=" + routerId + " and network id=" + networkId);
+                
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Failed to update the router/network reference ", e);
+        } finally {
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+        s_logger.debug("Done updating router/network references");        
+    }
+
+    
+    private void fixForeignKeys(Connection conn) {
+        //Drop the keys (if exist)
+        List<String> keys = new ArrayList<String>();
+        keys.add("fk_ssh_keypair__account_id");
+        keys.add("fk_ssh_keypair__domain_id");
+        keys.add("fk_ssh_keypairs__account_id");
+        keys.add("fk_ssh_keypairs__domain_id");
+        DbUpgradeUtils.dropKeysIfExist(conn, "ssh_keypairs", keys, true);
+        
+        keys = new ArrayList<String>();
+        keys.add("fk_ssh_keypair__account_id");
+        keys.add("fk_ssh_keypair__domain_id");
+        keys.add("fk_ssh_keypairs__account_id");
+        keys.add("fk_ssh_keypairs__domain_id");
+        DbUpgradeUtils.dropKeysIfExist(conn, "ssh_keypairs", keys, false);
+        
+        //insert the keys anew
+        try {
+            PreparedStatement pstmt; pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`ssh_keypairs` ADD " +
+                    "CONSTRAINT `fk_ssh_keypair__account_id` FOREIGN KEY `fk_ssh_keypair__account_id` (`account_id`)" +
+                    " REFERENCES `account` (`id`) ON DELETE CASCADE");
+            pstmt.executeUpdate();
+            pstmt.close();
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to execute ssh_keypairs table update for adding account_id foreign key", e);
+        }
+            
+        try {
+            PreparedStatement pstmt; pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`ssh_keypairs` ADD CONSTRAINT" +
+                    " `fk_ssh_keypair__domain_id` FOREIGN KEY `fk_ssh_keypair__domain_id` (`domain_id`) " +
+                    "REFERENCES `domain` (`id`) ON DELETE CASCADE");
+            pstmt.executeUpdate();
+            pstmt.close();
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to execute ssh_keypairs table update for adding domain_id foreign key", e);
+        }
+    }
+
+    // upgrades deployment with F5 and SRX devices, to 3.0's Network offerings & service providers paradigm
+    private void setupExternalNetworkDevices(Connection conn) {
+        PreparedStatement zoneSearchStmt = null, pNetworkStmt = null, f5DevicesStmt = null, srxDevicesStmt = null;
+        ResultSet zoneResults = null, pNetworksResults = null, f5DevicesResult = null, srxDevicesResult = null;
+
+        try {
+            zoneSearchStmt = conn.prepareStatement("SELECT id, networktype FROM `cloud`.`data_center`");
+            zoneResults = zoneSearchStmt.executeQuery();
+            while (zoneResults.next()) {
+                long zoneId = zoneResults.getLong(1);
+                String networkType = zoneResults.getString(2);
+
+                if (!NetworkType.Advanced.toString().equalsIgnoreCase(networkType)) {
+                    continue;
+                }
+
+                pNetworkStmt = conn.prepareStatement("SELECT id FROM `cloud`.`physical_network` where data_center_id=?");
+                pNetworkStmt.setLong(1, zoneId);
+                pNetworksResults = pNetworkStmt.executeQuery();
+                while (pNetworksResults.next()) {
+                    long physicalNetworkId = pNetworksResults.getLong(1);
+                    PreparedStatement fetchF5NspStmt = conn.prepareStatement("SELECT id from `cloud`.`physical_network_service_providers` where physical_network_id=" + physicalNetworkId
+                            + " and provider_name = 'F5BigIp'");
+                    ResultSet rsF5NSP = fetchF5NspStmt.executeQuery();
+                    boolean hasF5Nsp = rsF5NSP.next();
+                    fetchF5NspStmt.close();
+
+                    if (!hasF5Nsp) {
+                        f5DevicesStmt = conn.prepareStatement("SELECT id FROM host WHERE data_center_id=? AND type = 'ExternalLoadBalancer' AND removed IS NULL");
+                        f5DevicesStmt.setLong(1, zoneId);
+                        f5DevicesResult = f5DevicesStmt.executeQuery();
+
+                        while (f5DevicesResult.next()) {
+                            long f5HostId = f5DevicesResult.getLong(1);;
+                            // add F5BigIP provider and provider instance to physical network
+                            addF5ServiceProvider(conn, physicalNetworkId, zoneId);
+                            addF5LoadBalancer(conn, f5HostId, physicalNetworkId);
+                        }
+                    }
+
+                    PreparedStatement fetchSRXNspStmt = conn.prepareStatement("SELECT id from `cloud`.`physical_network_service_providers` where physical_network_id=" + physicalNetworkId
+                            + " and provider_name = 'JuniperSRX'");
+                    ResultSet rsSRXNSP = fetchSRXNspStmt.executeQuery();
+                    boolean hasSrxNsp = rsSRXNSP.next();
+                    fetchSRXNspStmt.close();
+
+                    if (!hasSrxNsp) {
+                        srxDevicesStmt = conn.prepareStatement("SELECT id FROM host WHERE data_center_id=? AND type = 'ExternalFirewall' AND removed IS NULL");
+                        srxDevicesStmt.setLong(1, zoneId);
+                        srxDevicesResult = srxDevicesStmt.executeQuery();
+
+                        while (srxDevicesResult.next()) {
+                            long srxHostId = srxDevicesResult.getLong(1);
+                            // add SRX provider and provider instance to physical network
+                            addSrxServiceProvider(conn, physicalNetworkId, zoneId);
+                            addSrxFirewall(conn, srxHostId, physicalNetworkId);
+                        }
+                    }
+                }
+            }
+
+            if (zoneResults != null) {
+                try {
+                    zoneResults.close();
+                } catch (SQLException e) {
+                }
+            }
+            if (zoneSearchStmt != null) {
+                try {
+                    zoneSearchStmt.close();
+                } catch (SQLException e) {
+                }
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworks", e);
+        } finally {
+
+        }
+    }
+
+    private void addF5LoadBalancer(Connection conn, long hostId, long physicalNetworkId){
+        PreparedStatement pstmtUpdate = null;
+        try{
+            s_logger.debug("Adding F5 Big IP load balancer with host id " + hostId + " in to physical network" + physicalNetworkId);
+            String insertF5 = "INSERT INTO `cloud`.`external_load_balancer_devices` (physical_network_id, host_id, provider_name, " +
+                    "device_name, capacity, is_dedicated, device_state, allocation_state, is_inline, is_managed, uuid) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+            pstmtUpdate = conn.prepareStatement(insertF5);
+            pstmtUpdate.setLong(1, physicalNetworkId);
+            pstmtUpdate.setLong(2, hostId);
+            pstmtUpdate.setString(3, "F5BigIp");
+            pstmtUpdate.setString(4, "F5BigIpLoadBalancer");
+            pstmtUpdate.setLong(5, 0);
+            pstmtUpdate.setBoolean(6, false);
+            pstmtUpdate.setString(7, "Enabled");
+            pstmtUpdate.setString(8, "Shared");
+            pstmtUpdate.setBoolean(9, false);
+            pstmtUpdate.setBoolean(10, false);
+            pstmtUpdate.setString(11, UUID.randomUUID().toString());
+            pstmtUpdate.executeUpdate();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding F5 load balancer device" ,  e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    private void addSrxFirewall(Connection conn, long hostId, long physicalNetworkId){
+        PreparedStatement pstmtUpdate = null;
+        try{
+            s_logger.debug("Adding SRX firewall device with host id " + hostId + " in to physical network" + physicalNetworkId);
+            String insertSrx = "INSERT INTO `cloud`.`external_firewall_devices` (physical_network_id, host_id, provider_name, " +
+                    "device_name, capacity, is_dedicated, device_state, allocation_state, uuid) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+            pstmtUpdate = conn.prepareStatement(insertSrx);
+            pstmtUpdate.setLong(1, physicalNetworkId);
+            pstmtUpdate.setLong(2, hostId);
+            pstmtUpdate.setString(3, "JuniperSRX");
+            pstmtUpdate.setString(4, "JuniperSRXFirewall");
+            pstmtUpdate.setLong(5, 0);
+            pstmtUpdate.setBoolean(6, false);
+            pstmtUpdate.setString(7, "Enabled");
+            pstmtUpdate.setString(8, "Shared");
+            pstmtUpdate.setString(9, UUID.randomUUID().toString());
+            pstmtUpdate.executeUpdate();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding SRX firewall device ",  e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    private void addF5ServiceProvider(Connection conn, long physicalNetworkId, long zoneId){
+        PreparedStatement pstmtUpdate = null;
+        try{
+            // add physical network service provider - F5BigIp
+            s_logger.debug("Adding PhysicalNetworkServiceProvider F5BigIp" + " in to physical network" + physicalNetworkId);
+            String insertPNSP = "INSERT INTO `cloud`.`physical_network_service_providers` (`uuid`, `physical_network_id` , `provider_name`, `state` ," +
+                    "`destination_physical_network_id`, `vpn_service_provided`, `dhcp_service_provided`, `dns_service_provided`, `gateway_service_provided`," +
+                    "`firewall_service_provided`, `source_nat_service_provided`, `load_balance_service_provided`, `static_nat_service_provided`," +
+                    "`port_forwarding_service_provided`, `user_data_service_provided`, `security_group_service_provided`) VALUES (?,?,?,?,0,0,0,0,0,0,0,1,0,0,0,0)";
+
+            pstmtUpdate = conn.prepareStatement(insertPNSP);
+            pstmtUpdate.setString(1, UUID.randomUUID().toString());
+            pstmtUpdate.setLong(2, physicalNetworkId);
+            pstmtUpdate.setString(3, "F5BigIp");
+            pstmtUpdate.setString(4, "Enabled");
+            pstmtUpdate.executeUpdate();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworkServiceProvider F5BigIp", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    private void addSrxServiceProvider(Connection conn, long physicalNetworkId, long zoneId){
+        PreparedStatement pstmtUpdate = null;
+        try{
+            // add physical network service provider - JuniperSRX
+            s_logger.debug("Adding PhysicalNetworkServiceProvider JuniperSRX");
+            String insertPNSP = "INSERT INTO `cloud`.`physical_network_service_providers` (`uuid`, `physical_network_id` , `provider_name`, `state` ," +
+                    "`destination_physical_network_id`, `vpn_service_provided`, `dhcp_service_provided`, `dns_service_provided`, `gateway_service_provided`," +
+                    "`firewall_service_provided`, `source_nat_service_provided`, `load_balance_service_provided`, `static_nat_service_provided`," +
+                    "`port_forwarding_service_provided`, `user_data_service_provided`, `security_group_service_provided`) VALUES (?,?,?,?,0,0,0,0,1,1,1,0,1,1,0,0)";
+
+            pstmtUpdate = conn.prepareStatement(insertPNSP);
+            pstmtUpdate.setString(1, UUID.randomUUID().toString());
+            pstmtUpdate.setLong(2, physicalNetworkId);
+            pstmtUpdate.setString(3, "JuniperSRX");
+            pstmtUpdate.setString(4, "Enabled");
+            pstmtUpdate.executeUpdate();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworkServiceProvider JuniperSRX" ,  e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    // 1) ensure that networks using external load balancer/firewall in 2.2.14 or prior releases deployments
+    //    has entry in network_external_lb_device_map and network_external_firewall_device_map
+    //
+    // 2) Some keys of host details for F5 and SRX devices were stored in Camel Case in 2.x releases. From 3.0
+    //    they are made in lowercase. On upgrade change the host details name to lower case
+    private void fixZoneUsingExternalDevices(Connection conn) {
+        //Get zones to upgrade
+        List<Long> zoneIds = new ArrayList<Long>();
+        PreparedStatement pstmt = null;
+        PreparedStatement pstmtUpdate = null;
+        ResultSet rs = null;
+        long networkOfferingId, networkId;
+        long f5DeviceId, f5HostId;
+        long srxDevivceId,  srxHostId;
+
+        try {
+            pstmt = conn.prepareStatement("select id from `cloud`.`data_center` where lb_provider='F5BigIp' or firewall_provider='JuniperSRX' or gateway_provider='JuniperSRX'");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                zoneIds.add(rs.getLong(1));
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to create network to LB & firewalla device mapping for networks  that use them", e);
+        }
+
+        if (zoneIds.size() == 0) {
+                return; // no zones using F5 and SRX devices so return
+        }
+
+        // find the default network offering created for external devices during upgrade from 2.2.14
+        try {
+            pstmt = conn.prepareStatement("select id from `cloud`.`network_offerings` where unique_name='Isolated with external providers' ");
+            rs = pstmt.executeQuery();
+            if (rs.first()) {
+                    networkOfferingId = rs.getLong(1);
+            } else {
+                    throw new CloudRuntimeException("Cannot upgrade as there is no 'Isolated with external providers' network offering crearted .");
+            }
+        } catch  (SQLException e) {
+                throw new CloudRuntimeException("Unable to create network to LB & firewalla device mapping for networks  that use them", e);
+        }
+
+        for (Long zoneId : zoneIds) {
+            try {
+        // find the F5 device id  in the zone
+                pstmt = conn.prepareStatement("SELECT id FROM host WHERE data_center_id=? AND type = 'ExternalLoadBalancer' AND removed IS NULL");
+                pstmt.setLong(1, zoneId);
+                rs = pstmt.executeQuery();
+                if (rs.first()) {
+                   f5HostId  = rs.getLong(1);
+                } else {
+                    throw new CloudRuntimeException("Cannot upgrade as there is no F5 load balancer device found in data center " + zoneId);
+                }
+                pstmt = conn.prepareStatement("SELECT id FROM external_load_balancer_devices WHERE  host_id=?");
+                pstmt.setLong(1, f5HostId);
+                rs = pstmt.executeQuery();
+                if (rs.first()) {
+                        f5DeviceId = rs.getLong(1);
+                } else {
+                    throw new CloudRuntimeException("Cannot upgrade as there is no F5 load balancer device with host ID " + f5HostId + " found in external_load_balancer_device");
+                }
+
+            // find the SRX device id  in the zone
+                pstmt = conn.prepareStatement("SELECT id FROM host WHERE data_center_id=? AND type = 'ExternalFirewall' AND removed IS NULL");
+                pstmt.setLong(1, zoneId);
+                rs = pstmt.executeQuery();
+                if (rs.first()) {
+                        srxHostId = rs.getLong(1);
+                } else {
+                    throw new CloudRuntimeException("Cannot upgrade as there is no SRX firewall device found in data center " + zoneId);
+                }
+                pstmt = conn.prepareStatement("SELECT id FROM external_firewall_devices WHERE  host_id=?");
+                pstmt.setLong(1, srxHostId);
+                rs = pstmt.executeQuery();
+                if (rs.first()) {
+                        srxDevivceId = rs.getLong(1);
+                } else {
+                    throw new CloudRuntimeException("Cannot upgrade as there is no SRX firewall device found with host ID " + srxHostId + " found in external_firewall_devices");
+                }
+
+            // check if network any uses F5 or SRX devices  in the zone
+                pstmt = conn.prepareStatement("select id from `cloud`.`networks` where guest_type='Virtual' and data_center_id=? and network_offering_id=? and removed IS NULL");
+                pstmt.setLong(1, zoneId);
+                pstmt.setLong(2, networkOfferingId);
+                rs = pstmt.executeQuery();
+                while (rs.next()) {
+            // get the network Id
+                      networkId = rs.getLong(1);
+
+                     // add mapping for the network in network_external_lb_device_map
+                    String insertLbMapping = "INSERT INTO `cloud`.`network_external_lb_device_map` (uuid, network_id, external_load_balancer_device_id, created) VALUES ( ?, ?, ?, now())";
+                    pstmtUpdate = conn.prepareStatement(insertLbMapping);
+                    pstmtUpdate.setString(1, UUID.randomUUID().toString());
+                    pstmtUpdate.setLong(2, networkId);
+                    pstmtUpdate.setLong(3, f5DeviceId);
+                    pstmtUpdate.executeUpdate();
+                    s_logger.debug("Successfully added entry in network_external_lb_device_map for network " +  networkId + " and F5 device ID " +  f5DeviceId);
+
+                     // add mapping for the network in network_external_firewall_device_map
+                    String insertFwMapping = "INSERT INTO `cloud`.`network_external_firewall_device_map` (uuid, network_id, external_firewall_device_id, created) VALUES ( ?, ?, ?, now())";
+                    pstmtUpdate = conn.prepareStatement(insertFwMapping);
+                    pstmtUpdate.setString(1, UUID.randomUUID().toString());
+                    pstmtUpdate.setLong(2, networkId);
+                    pstmtUpdate.setLong(3, srxDevivceId);
+                    pstmtUpdate.executeUpdate();
+                    s_logger.debug("Successfully added entry in network_external_firewall_device_map for network " +  networkId + " and SRX device ID " +  srxDevivceId);
+                }
+
+                // update host details for F5 and SRX devices
+                s_logger.debug("Updating the host details for F5 and SRX devices");
+                pstmt = conn.prepareStatement("SELECT host_id, name FROM `cloud`.`host_details` WHERE  host_id=? OR host_id=?");
+                pstmt.setLong(1, f5HostId);
+                pstmt.setLong(2, srxHostId);
+                rs = pstmt.executeQuery();
+                while (rs.next()) {
+                    long hostId = rs.getLong(1);
+                    String camlCaseName = rs.getString(2);
+                    if (!(camlCaseName.equalsIgnoreCase("numRetries") ||
+                            camlCaseName.equalsIgnoreCase("publicZone") ||
+                            camlCaseName.equalsIgnoreCase("privateZone") ||
+                            camlCaseName.equalsIgnoreCase("publicInterface") ||
+                            camlCaseName.equalsIgnoreCase("privateInterface") ||
+                            camlCaseName.equalsIgnoreCase("usageInterface") )) {
+                        continue;
+                    }
+                    String lowerCaseName = camlCaseName.toLowerCase();
+                    pstmt = conn.prepareStatement("update `cloud`.`host_details` set name=? where host_id=? AND name=?");
+                    pstmt.setString(1, lowerCaseName);
+                    pstmt.setLong(2, hostId);
+                    pstmt.setString(3, camlCaseName);
+                    pstmt.executeUpdate();
+                }
+                s_logger.debug("Successfully updated host details for F5 and SRX devices");
+            } catch (SQLException e) {
+                throw new CloudRuntimeException("Unable create a mapping for the networks in network_external_lb_device_map and network_external_firewall_device_map", e);
+            }  finally {
+                try {
+                    if (rs != null) {
+                        rs.close();
+                    }
+                    if (pstmt != null) {
+                        pstmt.close();
+                    }
+                } catch (SQLException e) {
+                }
+            }
+            s_logger.info("Successfully upgraded networks using F5 and SRX devices to have a entry in the network_external_lb_device_map and network_external_firewall_device_map");
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade30to301.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade30to301.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade30to301.java
new file mode 100644
index 0000000..bc6edbe
--- /dev/null
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade30to301.java
@@ -0,0 +1,124 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+package com.cloud.upgrade.dao;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.log4j.Logger;
+
+import com.cloud.configuration.Resource.ResourceType;
+import com.cloud.utils.exception.CloudRuntimeException;
+import com.cloud.utils.script.Script;
+
+public class Upgrade30to301 implements DbUpgrade {
+    final static Logger s_logger = Logger.getLogger(Upgrade30to301.class);
+
+    @Override
+    public String[] getUpgradableVersionRange() {
+        return new String[] { "3.0.0", "3.0.1" };
+    }
+
+    @Override
+    public String getUpgradedVersion() {
+        return "3.0.1";
+    }
+
+    @Override
+    public boolean supportsRollingUpgrade() {
+        return true;
+    }
+
+    @Override
+    public File[] getPrepareScripts() {
+        String script = Script.findScript("", "db/schema-30to301.sql");
+        if (script == null) {
+            throw new CloudRuntimeException("Unable to find db/schema-30to301.sql");
+        }
+
+        return new File[] { new File(script) };
+    }
+
+    @Override
+    public void performDataMigration(Connection conn) {
+        // update network account resource count
+        udpateAccountNetworkResourceCount(conn);
+        // update network domain resource count
+        udpateDomainNetworkResourceCount(conn);
+    }
+
+    @Override
+    public File[] getCleanupScripts() {
+        return null;
+    }
+    
+    
+    protected void udpateAccountNetworkResourceCount(Connection conn) {
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        ResultSet rs1 = null;
+        long accountId = 0;
+        try {
+            pstmt = conn.prepareStatement("select id from `cloud`.`account` where removed is null");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                accountId = rs.getLong(1);
+                
+                //get networks count for the account
+                pstmt = conn.prepareStatement("select count(*) from `cloud`.`networks` n, `cloud`.`account_network_ref` a, `cloud`.`network_offerings` no" +
+                        " WHERE n.acl_type='Account' and n.id=a.network_id and a.account_id=? and a.is_owner=1 and no.specify_vlan=false and no.traffic_type='Guest'");
+                pstmt.setLong(1, accountId);
+                rs1 = pstmt.executeQuery();
+                long count = 0;
+                while (rs1.next()) {
+                    count = rs1.getLong(1);
+                }
+                
+                pstmt = conn.prepareStatement("insert into `cloud`.`resource_count` (account_id, domain_id, type, count) VALUES (?, null, 'network', ?)");
+                pstmt.setLong(1, accountId);
+                pstmt.setLong(2, count);
+                pstmt.executeUpdate();
+                s_logger.debug("Updated network resource count for account id=" + accountId + " to be " + count);
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to update network resource count for account id=" + accountId, e);
+        } finally {
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+                
+                if (rs1 != null) {
+                    rs1.close();
+                }
+                
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+    }
+    
+    protected void udpateDomainNetworkResourceCount(Connection conn) {
+        Upgrade218to22.upgradeDomainResourceCounts(conn, ResourceType.network);
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade30xBase.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade30xBase.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade30xBase.java
new file mode 100644
index 0000000..723089e
--- /dev/null
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade30xBase.java
@@ -0,0 +1,311 @@
+// 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.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.UUID;
+
+import org.apache.log4j.Logger;
+
+import com.cloud.utils.exception.CloudRuntimeException;
+
+public abstract class Upgrade30xBase implements DbUpgrade{
+
+    final static Logger s_logger = Logger.getLogger(Upgrade30xBase.class);
+
+    protected String getNetworkLabelFromConfig(Connection conn, String name){
+        String sql = "SELECT value FROM `cloud`.`configuration` where name = '"+name+"'";
+        String networkLabel = null;
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        try{
+            pstmt = conn.prepareStatement(sql);
+            rs = pstmt.executeQuery();
+            if (rs.next()) {
+                networkLabel = rs.getString(1);
+            }
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to fetch network label from configuration", e);
+        }finally{
+            if (rs != null) {
+                try {
+                    rs.close();
+                } catch (SQLException e) {
+                }
+            }
+            if (pstmt != null) {
+                try {
+                    pstmt.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+        return networkLabel;
+    }
+
+
+    protected long addPhysicalNetworkToZone(Connection conn, long zoneId, String zoneName, String networkType, String vnet, Long domainId){
+
+        String getNextNetworkSequenceSql = "SELECT value from `cloud`.`sequence` where name='physical_networks_seq'";
+        String advanceNetworkSequenceSql = "UPDATE `cloud`.`sequence` set value=value+1 where name='physical_networks_seq'";
+        PreparedStatement pstmtUpdate = null, pstmt2 = null;
+        // add p.network
+        try{
+            pstmt2 = conn.prepareStatement(getNextNetworkSequenceSql);
+
+            ResultSet rsSeq = pstmt2.executeQuery();
+            rsSeq.next();
+
+            long physicalNetworkId = rsSeq.getLong(1);
+            rsSeq.close();
+            pstmt2.close();
+            pstmt2 = conn.prepareStatement(advanceNetworkSequenceSql);
+            pstmt2.executeUpdate();
+            pstmt2.close();
+
+            String uuid = UUID.randomUUID().toString();
+            String broadcastDomainRange = "POD";
+            if ("Advanced".equals(networkType)) {
+                broadcastDomainRange = "ZONE";
+            }
+
+            s_logger.debug("Adding PhysicalNetwork " + physicalNetworkId + " for Zone id " + zoneId);
+            String sql = "INSERT INTO `cloud`.`physical_network` (id, uuid, data_center_id, vnet, broadcast_domain_range, state, name) VALUES (?,?,?,?,?,?,?)";
+
+            pstmtUpdate = conn.prepareStatement(sql);
+            pstmtUpdate.setLong(1, physicalNetworkId);
+            pstmtUpdate.setString(2, uuid);
+            pstmtUpdate.setLong(3, zoneId);
+            pstmtUpdate.setString(4, vnet);
+            pstmtUpdate.setString(5, broadcastDomainRange);
+            pstmtUpdate.setString(6, "Enabled");
+            zoneName = zoneName + "-pNtwk" +physicalNetworkId;
+            pstmtUpdate.setString(7, zoneName);
+            s_logger.warn("Statement is " + pstmtUpdate.toString());
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+
+            if (domainId != null && domainId.longValue() != 0) {
+                s_logger.debug("Updating domain_id for physical network id=" + physicalNetworkId);
+                sql = "UPDATE `cloud`.`physical_network` set domain_id=? where id=?";
+                pstmtUpdate = conn.prepareStatement(sql);
+                pstmtUpdate.setLong(1, domainId);
+                pstmtUpdate.setLong(2, physicalNetworkId);
+                pstmtUpdate.executeUpdate();
+                pstmtUpdate.close();
+            }
+
+            return physicalNetworkId;
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworks", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+            if (pstmt2 != null) {
+                try {
+                    pstmt2.close();
+                } catch (SQLException e) {
+                }
+            }
+
+        }
+    }
+
+    protected void addTrafficType(Connection conn, long physicalNetworkId, String trafficType, String xenPublicLabel, String kvmPublicLabel, String vmwarePublicLabel){
+        // add traffic types
+        PreparedStatement pstmtUpdate = null;
+        try{
+            s_logger.debug("Adding PhysicalNetwork traffic types");
+            String insertTraficType = "INSERT INTO `cloud`.`physical_network_traffic_types` (physical_network_id, traffic_type, xen_network_label, kvm_network_label, vmware_network_label, uuid) VALUES ( ?, ?, ?, ?, ?, ?)";
+            pstmtUpdate = conn.prepareStatement(insertTraficType);
+            pstmtUpdate.setLong(1, physicalNetworkId);
+            pstmtUpdate.setString(2, trafficType);
+            pstmtUpdate.setString(3, xenPublicLabel);
+            pstmtUpdate.setString(4, kvmPublicLabel);
+            pstmtUpdate.setString(5, vmwarePublicLabel);
+            pstmtUpdate.setString(6, UUID.randomUUID().toString());
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworks", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+
+    protected void addDefaultSGProvider(Connection conn, long physicalNetworkId, long zoneId, String networkType, boolean is304){
+        PreparedStatement pstmtUpdate = null, pstmt2 = null;
+        try{
+            //add security group service provider (if security group service is enabled for at least one guest network)
+            boolean isSGServiceEnabled = false;
+            String selectSG = "";
+
+            if(is304){
+                 selectSG = "SELECT nm.* FROM `cloud`.`ntwk_service_map` nm JOIN `cloud`.`networks` n ON nm.network_id = n.id where n.data_center_id = ? and nm.service='SecurityGroup'";
+            }else{
+                 selectSG = "SELECT * from `cloud`.`networks` where is_security_group_enabled=1 and data_center_id=?";
+            }
+
+            pstmt2 = conn.prepareStatement(selectSG);
+            pstmt2.setLong(1, zoneId);
+            ResultSet sgDcSet = pstmt2.executeQuery();
+            if (sgDcSet.next()) {
+                isSGServiceEnabled = true;
+            }
+            sgDcSet.close();
+            pstmt2.close();
+
+            if(isSGServiceEnabled){
+                s_logger.debug("Adding PhysicalNetworkServiceProvider SecurityGroupProvider to the physical network id=" + physicalNetworkId);
+                String insertPNSP = "INSERT INTO `cloud`.`physical_network_service_providers` (`uuid`, `physical_network_id` , `provider_name`, `state` ," +
+                        "`destination_physical_network_id`, `vpn_service_provided`, `dhcp_service_provided`, `dns_service_provided`, `gateway_service_provided`," +
+                        "`firewall_service_provided`, `source_nat_service_provided`, `load_balance_service_provided`, `static_nat_service_provided`," +
+                        "`port_forwarding_service_provided`, `user_data_service_provided`, `security_group_service_provided`) VALUES (?,?,?,?,0,0,0,0,0,0,0,0,0,0,0,1)";
+                pstmtUpdate = conn.prepareStatement(insertPNSP);
+                pstmtUpdate.setString(1, UUID.randomUUID().toString());
+                pstmtUpdate.setLong(2, physicalNetworkId);
+                pstmtUpdate.setString(3, "SecurityGroupProvider");
+                pstmtUpdate.setString(4, "Enabled");
+
+                pstmtUpdate.executeUpdate();
+                pstmtUpdate.close();
+            }
+
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding default Security Group Provider", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+            if (pstmt2 != null) {
+                try {
+                    pstmt2.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    protected void addDefaultVRProvider(Connection conn, long physicalNetworkId, long zoneId){
+        PreparedStatement pstmtUpdate = null, pstmt2 = null;
+        try{
+            // add physical network service provider - VirtualRouter
+            s_logger.debug("Adding PhysicalNetworkServiceProvider VirtualRouter");
+            String insertPNSP = "INSERT INTO `cloud`.`physical_network_service_providers` (`uuid`, `physical_network_id` , `provider_name`, `state` ," +
+                    "`destination_physical_network_id`, `vpn_service_provided`, `dhcp_service_provided`, `dns_service_provided`, `gateway_service_provided`," +
+                    "`firewall_service_provided`, `source_nat_service_provided`, `load_balance_service_provided`, `static_nat_service_provided`," +
+                    "`port_forwarding_service_provided`, `user_data_service_provided`, `security_group_service_provided`) VALUES (?,?,?,?,0,1,1,1,1,1,1,1,1,1,1,0)";
+
+            String routerUUID = UUID.randomUUID().toString();
+            pstmtUpdate = conn.prepareStatement(insertPNSP);
+            pstmtUpdate.setString(1, routerUUID );
+            pstmtUpdate.setLong(2, physicalNetworkId);
+            pstmtUpdate.setString(3, "VirtualRouter");
+            pstmtUpdate.setString(4, "Enabled");
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+
+            // add virtual_router_element
+            String fetchNSPid = "SELECT id from `cloud`.`physical_network_service_providers` where physical_network_id=" + physicalNetworkId + " AND provider_name = 'VirtualRouter' AND uuid = ?";
+            pstmt2 = conn.prepareStatement(fetchNSPid);
+            pstmt2.setString(1, routerUUID);
+            ResultSet rsNSPid = pstmt2.executeQuery();
+            rsNSPid.next();
+            long nspId = rsNSPid.getLong(1);
+            pstmt2.close();
+
+            String insertRouter = "INSERT INTO `cloud`.`virtual_router_providers` (`nsp_id`, `uuid` , `type` , `enabled`) " +
+                    "VALUES (?,?,?,?)";
+            pstmtUpdate = conn.prepareStatement(insertRouter);
+            pstmtUpdate.setLong(1, nspId);
+            pstmtUpdate.setString(2, UUID.randomUUID().toString());
+            pstmtUpdate.setString(3, "VirtualRouter");
+            pstmtUpdate.setInt(4, 1);
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworks", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+            if (pstmt2 != null) {
+                try {
+                    pstmt2.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+    }
+
+    protected void addPhysicalNtwk_To_Ntwk_IP_Vlan(Connection conn, long physicalNetworkId, long networkId){
+        PreparedStatement pstmtUpdate = null;
+        try{
+            // add physicalNetworkId to vlan for this zone
+            String updateVLAN = "UPDATE `cloud`.`vlan` SET physical_network_id = " + physicalNetworkId + " WHERE network_id = " + networkId;
+            pstmtUpdate = conn.prepareStatement(updateVLAN);
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+
+            // add physicalNetworkId to user_ip_address for this zone
+            String updateUsrIp = "UPDATE `cloud`.`user_ip_address` SET physical_network_id = " + physicalNetworkId + " WHERE source_network_id = " + networkId;
+            pstmtUpdate = conn.prepareStatement(updateUsrIp);
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+
+            // add physicalNetworkId to guest networks for this zone
+            String updateNet = "UPDATE `cloud`.`networks` SET physical_network_id = " + physicalNetworkId + " WHERE id = " + networkId + " AND traffic_type = 'Guest'";
+            pstmtUpdate = conn.prepareStatement(updateNet);
+            pstmtUpdate.executeUpdate();
+            pstmtUpdate.close();
+        }catch (SQLException e) {
+            throw new CloudRuntimeException("Exception while adding PhysicalNetworks", e);
+        } finally {
+            if (pstmtUpdate != null) {
+                try {
+                    pstmtUpdate.close();
+                } catch (SQLException e) {
+                }
+            }
+        }
+
+    }
+
+
+
+
+}

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/572e71e5/engine/schema/src/com/cloud/upgrade/dao/Upgrade40to41.java
----------------------------------------------------------------------
diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade40to41.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade40to41.java
new file mode 100644
index 0000000..9e386b9
--- /dev/null
+++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade40to41.java
@@ -0,0 +1,164 @@
+// 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 com.cloud.utils.db.Transaction;
+import com.cloud.utils.exception.CloudRuntimeException;
+import com.cloud.utils.script.Script;
+import org.apache.log4j.Logger;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.UUID;
+
+public class Upgrade40to41 implements DbUpgrade {
+    final static Logger s_logger = Logger.getLogger(Upgrade40to41.class);
+
+    @Override
+    public String[] getUpgradableVersionRange() {
+        return new String[] { "4.0.0", "4.1.0" };
+    }
+
+    @Override
+    public String getUpgradedVersion() {
+        return "4.1.0";
+    }
+
+    @Override
+    public boolean supportsRollingUpgrade() {
+        return false;
+    }
+
+    @Override
+    public File[] getPrepareScripts() {
+        String script = Script.findScript("", "db/schema-40to410.sql");
+        if (script == null) {
+            throw new CloudRuntimeException("Unable to find db/schema-40to410.sql");
+        }
+
+        return new File[] { new File(script) };
+    }
+
+    @Override
+    public void performDataMigration(Connection conn) {
+        updateRegionEntries(conn);
+        upgradeEgressFirewallRules(conn);
+    }
+
+    @Override
+    public File[] getCleanupScripts() {
+        String script = Script.findScript("", "db/schema-40to410-cleanup.sql");
+        if (script == null) {
+            throw new CloudRuntimeException("Unable to find db/schema-40to410-cleanup.sql");
+        }
+
+        return new File[] { new File(script) };
+    }
+
+    private void updateRegionEntries(Connection conn) {
+        int region_id = Transaction.s_region_id;
+        PreparedStatement pstmt = null;
+        try {
+            //Update regionId in region table
+            s_logger.debug("Updating region table with Id: "+region_id);
+            pstmt = conn.prepareStatement("update `cloud`.`region` set id = ?");
+            pstmt.setInt(1, region_id);
+            pstmt.executeUpdate();
+
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Error while updating region entries", e);
+        } finally {
+            try {
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+    }
+
+    private void upgradeEgressFirewallRules(Connection conn) {
+        PreparedStatement pstmt = null;
+        ResultSet rs = null;
+        ResultSet rsId = null;
+        ResultSet rsNw = null;
+        try {
+            // update the existing ingress rules traffic type
+            pstmt = conn.prepareStatement("update `cloud`.`firewall_rules`  set traffic_type='Ingress' where purpose='Firewall' and ip_address_id is not null and traffic_type is null");
+            s_logger.debug("Updating firewall Ingress rule traffic type: " + pstmt);
+            pstmt.executeUpdate();
+
+            pstmt = conn.prepareStatement("select network_id FROM `cloud`.`ntwk_service_map` where service='Firewall' and provider='VirtualRouter' ");
+            rs = pstmt.executeQuery();
+            while (rs.next()) {
+                    long netId = rs.getLong(1);
+                    //When upgraded from 2.2.14 to 3.0.6 guest_type is updated to Isolated in the 2214to30 clean up sql. clean up executes
+                    //after this. So checking for Isolated OR Virtual
+                    pstmt = conn.prepareStatement("select account_id, domain_id FROM `cloud`.`networks` where (guest_type='Isolated' OR guest_type='Virtual') and traffic_type='Guest' and vpc_id is NULL and (state='implemented' OR state='Shutdown') and id=? ");
+                    pstmt.setLong(1, netId);
+                    s_logger.debug("Getting account_id, domain_id from networks table: " + pstmt);
+                    rsNw = pstmt.executeQuery();
+
+                    if(rsNw.next()) {
+                    long accountId = rsNw.getLong(1);
+                    long domainId = rsNw.getLong(2);
+
+                    //Add new rule for the existing networks
+                    s_logger.debug("Adding default egress firewall rule for network " + netId);
+                    pstmt = conn.prepareStatement("INSERT INTO firewall_rules (uuid, state, protocol, purpose, account_id, domain_id, network_id, xid, created,  traffic_type) VALUES (?, 'Active', 'all', 'Firewall', ?, ?, ?, ?, now(), 'Egress')");
+                    pstmt.setString(1, UUID.randomUUID().toString());
+                    pstmt.setLong(2, accountId);
+                    pstmt.setLong(3, domainId);
+                    pstmt.setLong(4, netId);
+                    pstmt.setString(5, UUID.randomUUID().toString());
+                    s_logger.debug("Inserting default egress firewall rule " + pstmt);
+                    pstmt.executeUpdate();
+
+                    pstmt = conn.prepareStatement("select id from firewall_rules where protocol='all' and network_id=?");
+                    pstmt.setLong(1, netId);
+                    rsId = pstmt.executeQuery();
+
+                    long firewallRuleId;
+                    if(rsId.next()) {
+                        firewallRuleId = rsId.getLong(1);
+                        pstmt = conn.prepareStatement("insert into firewall_rules_cidrs (firewall_rule_id,source_cidr) values (?, '0.0.0.0/0')");
+                        pstmt.setLong(1, firewallRuleId);
+                        s_logger.debug("Inserting rule for cidr 0.0.0.0/0 for the new Firewall rule id=" + firewallRuleId + " with statement " + pstmt);
+                        pstmt.executeUpdate();
+                    }
+                }
+            }
+        } catch (SQLException e) {
+            throw new CloudRuntimeException("Unable to set egress firewall rules ", e);
+        } finally {
+            try {
+                if (rs != null) {
+                    rs.close();
+                }
+                if (pstmt != null) {
+                    pstmt.close();
+                }
+            } catch (SQLException e) {
+            }
+        }
+    }
+
+}


Mime
View raw message