server/src/com/cloud/upgrade/dao/Upgrade30xBase.java - cloudstack - Git at Google

 // 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) {
                 }
             }
         }

     }


 }
	// 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) {
	}
	}
	}

	}




	}