public class ErrorMessage {
	public static final String CONNECTION_FAIL = "Connection failed: %s.";
	public static final String WRONG_RESPONSE  = "Response of %s is wrong.";
	public static final String INVALID_JSON      = "Invalid json syntax.";
	public static final String INVALID_PARAMETER = "%s is invalid parameter.";

	public static final String IS_NULL  = "%s is null.";
	public static final String IS_BLANK = "%s is blank.";
	public static final String IS_NOT_NULL     = "%s is not null.";
	public static final String IS_NOT_BLANK    = "%s is not blank.";
	public static final String IS_NOT_INCLUDED = "%s is not included %s.";
	public static final String IS_OVERLAPPED   = "%s is overlapped.";
	public static final String THERE_IS_NULL              = "There is null in %s.";
	public static final String THERE_IS_BLANK             = "There is blank in %s";
	public static final String THERE_IS_INVALID_PARAMETER = "There is invalid parameter %s.";
	public static final String THERE_ARE_OVERLAPPED       = "There are overlapped %s.";

	public static final String ALREADY_EXIST = "%s is already exists";
	public static final String FIND_NULL = "Find %s thats %s is null";
	public static final String NOT_FOUND = "%s is not found.";
	public static final String IS_FULL = "%s is full.";
	public static final String UNEXPECTED_ERROR = "Unexpected error.";
	public static final String RETURNED_NULL = "%s returned null.";
	public static final String NOW_USED	= "%s is now assigned.";

	public static final String IS_PATCHED = "%s is patched";
	public static final String IS_NO_ROUTE = "There is no route between %s and %s.";
	public static final String IS_NOT_NUMBER = "%s is not number.";

	public static final String PARSE_ERROR = "Parse error: %s";

	public static final String COULD_NOT_DELETE = "Couldn't delete %s";
	public static final String INVALID_NUMBER_OF = "Invalid number of %s.";
	public static final String PATCH_INSERT_FAILD = "patchWiring insert failed. %s[%s]-%s[%s]@%s(%s,%s)";
	public static final String ROUTE_INSERT_FAILD = "route insert failed. sequence_num=%s, logical_link_id=%s, node_id=%s, node_name=%s, in_port_id=%s, in_port_name=%s, in_port_number=%s, out_port_id=%s, out_port_name=%s, out_port_number=%s";
} public class OfpmDefinition {
	/* Define version */
	public static final String CONFIG_KEY_VERSION = "version";

	/* Define static http code */
	public static final int STATUS_SUCCESS        = 200;
	public static final int STATUS_CREATED        = 201;
	public static final int STATUS_USED           = 220;
	public static final int STATUS_BAD_REQUEST    = 400;
	public static final int STATUS_UNAUTHORIZED   = 401;
	public static final int STATUS_FORBIDDEN      = 403;
	public static final int STATUS_NOTFOUND       = 404;
	public static final int STATUS_CONFLICT       = 409;
	public static final int STATUS_INTERNAL_ERROR = 500;
	public static final int STATUS_NOW_USED       = 550;

	public static final String HTTP_METHOD_GET    = "GET";
	public static final String HTTP_METHOD_PUT    = "PUT";
	public static final String HTTP_METHOD_POST   = "POST";
	public static final String HTTP_METHOD_DELETE = "DELETE";

	/* Define property file */
	public static final String DEFAULT_PROPERTIY_FILE = "ofpm.properties";

	/* Define openflow controller config */
	public static final String OFC_ADD_FLOWENTRY_PATH = "/stats/flowentry/add";
	public static final String OFC_DELETE_FLOWENTRY_PATH = "/stats/flowentry/delete";
	public static final String OFC_PATH = "/ofc/ryu/ctrl";

	/* Define Database url */
	public static final String CONFIG_KEY_DB_URL = "db.url";
	public static final String CONFIG_KEY_DB_USER = "db.user";
	public static final String CONFIG_KEY_DB_DRIVER = "db.driver";
	public static final String CONFIG_KEY_DB_PASSWORD = "db.password";
	public static final String CONFIG_KEY_DB_MAX_ACTIVE_CONN = "db.conn.active.max";
	public static final String CONFIG_KEY_DB_WAIT = "db.conn.wait";
	

	/* Define DMDB node traffic type */
	public static final String DEVICE_TRAFFIC_TYPE_CLIENT  = "001";
	public static final String DEVICE_TRAFFIC_TYPE_SERVER  = "002";
	public static final String DEVICE_TRAFFIC_TYPE_MEASURE = "003";
	public static final String DEVICE_TRAFFIC_TYPE_PLANE   = "004";
	public static final String DEVICE_TRAFFIC_TYPE_SWITCH  = "005";

	/* Define validation parameters */
	public static final int COLLECT_NUMBER_OF_DEVICE_NAMES_IN_LINK = 2;
	public static final String[] ENABLE_OFP_FLAGS    = {"true", "false"};
	public static final String[] ENABLE_DEVICE_TYPES = {"Server", "Switch", "Leaf", "Spine", "Aggregate_Switch", "Sites_Switch"};

	public static final String[] LEGACY_DEVICE_TYPES = {"Server", "Switch","Sites_Switch"};
	public static final String[] OPEN_FLOW_DEVICE_TYPES = {"Leaf", "Spine","Aggregate_Switch"};

	public static final String CSV_SPLIT_REGEX = ",";
	public static final String REGEX_NUMBER = "[0-9]+";
	public static final String REGEX_IPADDRESS = "[0-9,\\.,:]+";
	public static final String REGEX_DATAPATH_ID = "0x[0-9a-fA-F]{1,16}";

	public static final int DEVICE_NAME_MAX_LENGTH = 30;
	public static final int DEVICE_LOCATION_MAX_LENGTH = 30;
	public static final int DEVICE_TENANT_MAX_LENGTH = 50;
	public static final int DEVICE_DATAPATHID_LENGTH = 18;
	
	public static final String DEVICE_IPADDRESS_VALUE = "[0-255]";
	public static final String DEVICE_PORT_VALUE = "[1024-65535]";
	
	public static final int PORT_NAME_MAX_LENGTH = 30;
	public static final int MIN_PORT_VALUE = 1024;
	public static final int MAX_PORT_VALUE = 65535;
	public static final int MAX_IPADDRESS_VALUE = 255;
	
	public static final int BAND_VALUE_1G = 1024;
	public static final int BAND_VALUE_10G = 10240;


	/* Define max macaddress value */
	public static final long MIN_MACADDRESS_VALUE = -140737488355329L;
	public static final long MAX_MACADDRESS_VALUE = 140737488355328L;

	/* Define max link ratio */
	public static final long LINK_MAXIMUM_USED_RATIO = 10000L;

	/* Define network instance type */
	public static final String NETWORK_INSTANCE_TYPE = "VLAN";
	public static final long MIN_ETHER_PACKET_SIZE_BIT = 512L;
	
	public static final long CONVERT_MBPS_BPS = 1048576L;	// 1024 * 1024
	public static final long MIN_ETHER_FRAME_SIZE_BIT = 512L;
	public static final long VLAN_FEALD_SIZE_BIT = 32L;	// VLAN field 4byte = 32bit
} public class OrientDBDefinition {
	/* Routing */
	public static final int DIJKSTRA_WEIGHT_NO_ROUTE = 100;
	public static final int DIJKSTRA_WEIGHT_AVAILABLE_ROUTE = 1;
	public static final long USED_BLOCKING_VALUE = 1000000000000L;
	public static final long BIG_USED_BUS_VALUE = 10240000L;

	/* Node Type */
	public static final String NODE_TYPE_SERVER = "Server";
	public static final String NODE_TYPE_SWITCH = "Switch";
	public static final String NODE_TYPE_EX_SWITCH = "ExSwitch";
	public static final String NODE_TYPE_LEAF = "Leaf";
	public static final String NODE_TYPE_SPINE = "Spine";

	public static final String NODE_TYPE_AGGREGATE_SW = "Aggregate_Switch";
	public static final String NODE_TYPE_SITES_SW = "Sites_Switch";

	public static final String[] SYSTEM_RESOURCE_TYPES = {NODE_TYPE_LEAF, NODE_TYPE_SPINE,NODE_TYPE_AGGREGATE_SW};
	public static final String[] RENT_RESOURCE_TYPES = {NODE_TYPE_SERVER, NODE_TYPE_SWITCH};

	public static final String[] BIG_USED_BUS_TYPES = {NODE_TYPE_SPINE, NODE_TYPE_AGGREGATE_SW, NODE_TYPE_SITES_SW};

	/* DB Response */
	public static final int DB_RESPONSE_STATUS_OK = 200;
	public static final int DB_RESPONSE_STATUS_EXIST = 210;
	public static final int DB_RESPONSE_STATUS_INVALID_ERR = 400;
	public static final int DB_RESPONSE_STATUS_NOT_FOUND = 404;
	public static final int DB_RESPONSE_STATUS_USED = 220;
	public static final int DB_RESPONSE_STATUS_FORBIDDEN = 403;
	public static final int DB_RESPONSE_STATUS_FAIL = 500;
	public static final int DB_RESPONSE_STATUS_NOW_USED = 550;

	/* SQL Node Key */
	public static final String SQL_NODE_KEY_NAME = "name";
	public static final String SQL_NODE_KEY_TYPE = "type";
	public static final String SQL_NODE_KEY_FLAG = "ofpFlag";

	/* select */
//	public static final String SQL_GET_NODE_INFO_LIST = "select @rid.asString() as rid, name, location, type, tenant, sw_instance_type, sw_instance_id from node limit 10000";
//	public static final String SQL_GET_NODE_INFO_LIST = "select @rid.asString() as rid, name, location, type, tenant, sw_instance_id.dpid as datapathId, sw_instance_id.ofc_id.ip as ip, sw_instance_id.ofc_id.port as port from node limit 10000";
	public static final String SQL_GET_NODE_INFO_LIST = "select @rid.asString() as rid, name, location, type, tenant from node limit 10000";
//	public static final String SQL_GET_NODE_INFO_LIST = "select * from node limit 10000";
	public static final String SQL_GET_NODE_INFO_FROM_DEVICE_NAME = "select @rid.asString(), name, type from node where name=?";
	public static final String SQL_GET_NODE_INFO_FROM_DEVICE_NAME_FM_SYS = "select system_resource_id.node_id.@rid.asString() as rid, system_resource_id.node_id.name as name, system_resource_id.node_id.location as location, system_resource_id.type as type, system_resource_id.tenant as tenant, dpid as datapathId, ofc_id.ip as ip, ofc_id.port as port from ofs where system_resource_id.node_id.name=?";
	public static final String SQL_GET_NODE_INFO_FROM_DEVICE_NAME_FM_RENT = "select system_resource_id.node_id.@rid.asString() as rid, node_id.name as name, node_id.location as location, type, tenant from rentResource where node_id.name=?";
	public static final String SQL_GET_NODE_INFO_FROM_DEVICE_RID  = "select @rid.asString(), name, type, datapathId, ofcIp from node where @rid=?";
	public static final String SQL_GET_NODE_RID_FROM_DEVICENAME   = "select @rid.asString() as rid from node where name=?";
	public static final String SQL_GET_DEVICENAME_FROM_DATAPATHID = "select name from node where dpid = ?";
	public static final String SQL_GET_DEVICE_INFO_FROM_DEVICERID = "select name, type, datapathId, ofcIp from node where @RID = ?";

	public static final String SQL_GET_STSTEM_RESOURCE_RID_FROM_NODE_RID   = "select @rid.asString() from systemResource where node_id=?";
	public static final String SQL_GET_RENT_RESOURCE_RID_FROM_NODE_RID   = "select @rid.asString() from rentResource where node_id=?";

	public static final String SQL_GET_OFC_RID_FROM_IP_AND_PORT   = "select @rid.asString() from ofc where ip=? and port=?";

	public static final String SQL_GET_PORT_INFO_FROM_PORTRID    = "select @rid.asString(), name, number, band, node_name from port where @RID = ?";
	public static final String SQL_GET_PORT_INFO_FROM_DEVICENAME = "select @rid.asString(), name, number, band, node_name from port where node_name=?";
	public static final String SQL_GET_PORT_INFO_FROM_PORT_NAME  = "select @rid.asString(), name, number, band, node_name, network from port where name = ? and node_name = ?";
	public static final String SQL_GET_PORT_RID_FROM_DEVICENAME_PORTNAME  = "select @rid.asString() from port where name = ? and node_name = ?";
	public static final String SQL_GET_PORTRID_FROM_DEVICENAME_PORTNUMBER = "select @rid.asString() from port where node_name = ? and number = ?";
	public static final String SQL_GET_PORT_BAND_FROM_DEVICENAME_PORTNAME  = "select band from port where name = ? and node_name = ?";
	public static final String SQL_GET_OFS_INFO_LIST = "select @rid.asString() as rid, ofc_id.@rid.asString() as ofc_id, node_id.@rid.asString() as node_id, dpid as datapathId, ofc_id.ip as ip, ofc_id.port as port from ofs";
	public static final String SQL_GET_OFS_RID_FROM_NODE_ID = "select @rid.asString() as rid, ofc_id.@rid.asString() as ofc_id, dpid, ofc_id.ip as ip, ofc_id.port as port from ofs where node_id=?";
	public static final String SQL_GET_OFC_INFO_LIST = "select ip, port from ofc";
	public static final String SQL_GET_OFC_INFO_FROM_IP_PORT = "select @rid.asString(), ip, port from ofc where ip = ? and port = ?";

	public static final String SQL_GET_CABLE_FROM_IN_PORTRID =
			"select in.node_name as inDeviceName, in.name as inPortName, in.number as inPortNumber, "
			+ "out.node_name as outDeviceName, out.name as outPortName, out.number as outPortNumber, @RID.asString(), band, used "
			+ "from link where in.@RID = ? and in.@class='port' and out.@class='port'";
	public static final String SQL_GET_CABLE_FROM_OUT_PORTRID =
			"select in.node_name as inDeviceName, in.name as inPortName, in.number as inPortNumber, "
			+ "out.node_name as outDeviceName, out.name as outPortName, out.number as outPortNumber, @RID.asString(), band, used "
			+ "from link where out.@RID = ? and in.@class='port' and out.@class='port'";
	public static final String SQL_GET_CABLE_LINKS    =
			"select in.node_name as inDeviceName, in.name as inPortName, in.number as inPortNumber, "
			+ "out.node_name as outDeviceName, out.name as outPortName, out.number as outPortNumber, @RID, band, used "
			+ "from link where in.node_name = ? and out.@class = 'port'";
	public static final String SQL_GET_CABLE_LINKS_ALL =
			"select in.node_name as inDeviceName, in.name as inPortName, in.number as inPortNumber, "
			+ "out.node_name as outDeviceName, out.name as outPortName, out.number as outPortNumber, @RID, band, used from cable limit 10000";

	public static final String SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME          =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring where inDeviceName=?";
	public static final String SQL_GET_LOGICAL_LINKS_FROM_DEVICENAME          =
			"select in_node_name as inDeviceName, in_port_name as inPortName, out_node_name as outDeviceName, out_port_name as outPortName from logicalLink where in_node_name=? or out_node_name=?";

	public static final String SQL_GET_LOGICAL_LINKS_FROM_DEVICENAME_2          =
			"select in_node_name as inDeviceName, in_port_name as inPortName, out_node_name as outDeviceName, out_port_name as outPortName from logicalLink where out_node_name=?";

	public static final String SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME_PORTNAME =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring " +
			"where inDeviceName=? and inPortName=? order by sequence asc";
	public static final String SQL_GET_PATCH_WIRINGS_FROM_NODERID =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring where parent=?";
	public static final String SQL_GET_PATCH_WIRINGS_FROM_PORTRID =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring where out=? or in=?";
	public static final String SQL_GET_PATCH_INFO_FROM_PATCHWIRING_PORTRID =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring where in = ?";
	public static final String SQL_GET_PATCH_INFO_FROM_PATCHWIRING_DEVICENAME_PORTNAME =
			"select out, in, parent, sequence from patchWiring " +
			"where inDeviceName = ? and inPortName = ? and outDeviceName = ? and outPortName = ? order by sequence desc";
	public static final String SQL_GET_PATCH_WIRINGS_FROM_PARENTRID =
			"select out, in, parent, sequence, inDeviceName, inPortName, outDeviceName, outPortName from patchWiring " +
			"where parent=? order by sequence asc limit=100000";

	public static final String SQL_GET_LOGICAL_LINK_FROM_NODE_NAME_PORT_NAME  =
			"select @rid.asString(), in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type from logicalLink " +
			"where (in_node_name=? and in_port_name=?) or (out_node_name=? and out_port_name=?)";

	public static final String SQL_GET_LOGICAL_LINK_FROM_RID  =
			"select @rid.asString(), in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type from logicalLink " +
			"where @RID = ?";

	public static final String SQL_GET_ROUTE_FROM_LOGICAL_LINK_ID  =
			"select @rid.asString(), sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number from route " +
			"where logical_link_id=?";

	public static final String SQL_GET_ROUTE_FROM_NODERID  =
			"select @rid.asString(), sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number from route " +
			"where node_id=?";

	public static final String SQL_GET_MAX_NW_INSTANCE_ID = "select max(nw_instance_id) as maxNwInstanceId from logicalLink";
	public static final String SQL_GET_NW_INSTANCE_ID = "select nw_instance_id as maxNwInstanceId from logicalLink order by nw_instance_id asc";
	public static final String SQL_GET_MAX_INTERNALMAC = "select max(internalMac) as maxInternalMac from internalMacMap";
	public static final String SQL_GET_INTERNALMAC_FROM_SRCMAC_DSTMAC_INPORT_DEVICENAME =
			"select internalMac from internalMacMap where node_name = ? and inPort = ? and srcMac = ? and dstMac = ?";
	public static final String SQL_GET_INTERNALMAC_LIST_FROM_DEVICENAME_INPORT =
			"select internalMac from internalMacMap where node_name = ? and inPort = ?";
	public static final String SQL_GET_INTERNALMAC_INFO_LIST_FROM_DEVICENAME_PORTNUMBER =
			"select srcMac, dstMac, internalMac from internalMacMap where node_name = ? and inPort = ? limit = 100000";

	public static final String SQL_GET_DIJKSTRA_PATH_FLATTEN  =
			"select @rid.asString() as rid, name, number, node_name, @class " +
			"from (select flatten(dijkstra) from (select dijkstra(?,?,'used')))";
	public static final String SQL_GET_NEIGHBOR_PORT_INFO_FROM_PORT_RID =
			"select out.@rid.asString() as rid, out.name as name, out.node_name as node_name, out.number as number from cable where in.@RID = ?";
	public static final String SQL_GET_NETWORK = "select * from network";
	public static final String SQL_GET_ASSIGNED_NETWORK_ID = "select from network where outer_tag=? and name=?";
	public static final String SQL_GET_OUTER_TAG_FLOWS = "select from Outer_tag where ag_dpid=?";
	public static final String SQL_GET_NETWORK_ID_OF_FREE = "select from network where spine1=\"\" AND spine2=\"\" AND name=? limit 10000";
	public static final String SQL_GET_NETWORK_ID_FROM_SPID = "select from network where spine1=? AND spine2=? AND name=? limit 10000";

	/* insert */
	public static final String SQL_INSERT_NODE      = "create vertex node set name = '%s', type = '%s'";
	public static final String SQL_INSERT_NODE_INFO = "create vertex node set name = ?, location = ?,type = ?, tenant = ?";
	public static final String SQL_INSERT_PORT      = "create vertex port set name = '%s', number = %s, band = %s, node_name = '%s'";
	public static final String SQL_INSERT_PORT_INFO = "create vertex port set name = ?, number = ?, band = ?, node_name = ?, network = ?";
	public static final String SQL_INSERT_BUS  = "create edge bus from ? to ? set used = ?";
	public static final String SQL_INSERT_CABLE = "create edge cable from ? to ? set used = 0";
	public static final String SQL_INSERT_PATCH_WIRING_2 = "insert into patchWiring(in, out, parent, inDeviceName, inPortName, outDeviceName, outPortName, sequence) values (?, ?, ?, ?, ?, ?, ?, ?)";
	public static final String SQL_INSERT_INTERNALMAC = "insert into internalMacMap(node_name, inPort, srcMac, dstMac, internalMac) values (?, ?, ?, ?, ?)";
	public static final String SQL_INSERT_SYSTEM_RESOURCE_INFO = "insert into systemResource(node_id, type, tenant) values (?, '?', '?')";
	public static final String SQL_INSERT_RENT_RESOURCE_INFO = "insert into rentResource(node_id, type, tenant) values (?, '?', '?')";
	public static final String SQL_INSERT_OFS_INFO = "insert into ofs(dpid, node_id, ofc_id) values ('?', ?, ?)";
	public static final String SQL_INSERT_OFC_INFO = "insert into ofc(ip, port) values (?, ?)";
	public static final String SQL_INSERT_LOGICAL_LINK = "insert into logicalLink(in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	public static final String SQL_INSERT_ROUTE_INFO = "insert into route(sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	public static final String SQL_INSERT_NETWORK_ID = "insert into network(outer_tag,name,spine1,spine2) values (?,?,'','')";
	public static final String SQL_INSERT_OUTER_TAG_OF_FLOW = "insert into Outer_tag(ag_dpid,ag_inport,ag_outport,localvlan,outer_tag,type,network) values (?,?,?,?,?,?,?)";


	/* delete */
	public static final String SQL_DELETE_NODE_FROM_NODERID = "delete vertex node where @RID = ?";
	public static final String SQL_DELETE_SYSTEM_RESOURCE_FROM_RID = "delete from systemResource where @RID = ?";
	public static final String SQL_DELETE_OFS_FROM_RID = "delete from ofs where @RID = ?";
	public static final String SQL_DELETE_RENT_RESOURCE_FROM_RID = "delete from rentResource where @RID = ?";
	public static final String SQL_DELETE_PORT_FROM_PORTRID = "delete vertex port where @RID = ?";
	public static final String SQL_DELETE_PORT_FROM_DEVICENAME = "delete vertex port where node_name = ?";
	public static final String SQL_DELETE_BUS_FROM_ONE_PORTRID = "delete edge bus where out = ? or in = ?";
	public static final String SQL_DELETE_CABLE_FROM_ONE_PORTRID = "delete edge cable where out = ? and in = ?";
	public static final String SQL_DELETE_PATCH_WIRING_FROM_DEVICE_NAME_PORT_NAME = "delete from patchWiring where (inDeviceName=? and inPortName=?) or (outDeviceName=? and outPortName=?)";
	public static final String SQL_DELETE_INTERNALMAC_FROM_DEVICE_NAME_PORT_NAME = "delete from internalMacMap where node_name=? and inPort=?";
	public static final String SQL_DELETE_OFC_FROM_IP_AND_PORT = "delete from ofc where ip = ? and port = ?";
	public static final String SQL_DELETE_LOGICAL_LINK_FROM_DEVICE_NAME_PORT_NAME = "delete from logicalLink where (in_node_name=? and in_port_name=?) or (out_node_name=? and out_port_name=?)";
	public static final String SQL_DELETE_ROUTE_FROM_LOGICAL_LINK_ID = "delete from route where logical_link_id=?";
	public static final String SQL_DELETE_NETWORK_ID = "delete from network where outer_tag=? and name=?)";
	public static final String SQL_DELETE_OUTER_TAG_OF_FLOW = "delete from Outer_tag where localvlan=?";

	/* update */
	public static final String SQL_UPDATE_NODE_INFO_FROM_RID = "update node set name = '?', location = '?', tenant = '?' where @RID = ?";
	public static final String SQL_UPDATE_OFS_INFO_FROM_RID = "update ofs set dpid = '?' , ofc_id = ? where @RID = ?";
	public static final String SQL_UPDATE_NODE_SW_INSTANCE_INFO_FROM_RID = "update node set sw_instance_type = '?', sw_instance_id = ? where @RID = ?";
	public static final String SQL_UPDATE_PORT_INFO_FROM_RID = "update port set name = ?, number = ?, band = ?, network = ? where @RID = ?";
	public static final String SQL_UPDATE_PORT_DEVICENAME = "update port set node_name = ? where node_name = ?";
	public static final String SQL_UPDATE_CALBE_LINK_USED_VALUE_FROM_PORT_RID = "update link set used = ? where out.@class='port' and in.@class='port' and (in.@RID = ? or out.@RID = ?)";
	public static final String SQL_UPDATE_PATCH_WIRING_INDEVICENAME  = "update patchWiring set  inDeviceName = ? where  inDeviceName = ?";
	public static final String SQL_UPDATE_PATCH_WIRING_OUTDEVICENAME = "update patchWiring set outDeviceName = ? where outDeviceName = ?";
	public static final String SQL_UPDATE_PATCH_WIRING_INPORTNAME  = "update patchWiring set  inPortName = ? where  inPortName = ? and  inDeviceName = ?";
	public static final String SQL_UPDATE_PATCH_WIRING_OUTPORTNAME = "update patchWiring set outPortName = ? where outPortName = ? and outDeviceName = ?";
	public static final String SQL_UPDATE_OFC_INFO = "update ofc set ip = ?, port = ? where @RID = ?";
	public static final String SQL_UPDATE_PAYOUT_OF_NETWORK_ID = "update network set spine1=?,spine2=? where outer_tag=? and name=?";
	public static final String SQL_UPDATE_RETURN_OF_NETWORK_ID = "update network set spine1=\"\",spine2=\"\" where (spine1=? or spine2=?) and network=?";

	/* OFP Flag */
	public static final String OFP_FLAG_TRUE  = "true";
	public static final String OFP_FLAG_FALSE = "false";

	/* network instance id min value */
	public static final Long MIN_NETWORK_INSTANCE_ID = 2L;
	public static final Long MAX_NETWORK_INSTANCE_ID = 4094L;
} public class ValidateException extends Exception {
	public ValidateException(String mes) {
		super(mes);
	}
}
 public class BaseResponse {
	private int status;
	private String message;

	public String getMessage() {
		return this.message;
	}
	public void setMessage(final String message) {
		this.message = message;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}

	@Override
	public boolean equals(Object obj) {
		return EqualsBuilder.reflectionEquals(this, obj);
	}
	@Override
	public int hashCode() {
		return HashCodeBuilder.reflectionHashCode(this);
	}
	@Override
	public String toString() {
		return this.toJson();
	}

	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<BaseResponse>() {}.getType();
		return gson.toJson(this, type);
	}
	public static BaseResponse fromJson(String json) {
		Gson gson = new Gson();
		Type type = new TypeToken<BaseResponse>() {}.getType();
		return gson.fromJson(json, type);
	}

} public class DeviceInfo extends Node implements Cloneable {
	private String datapathId;
	private String ofcIp;

	public String getDatapathId() {
		return datapathId;
	}

	public void setDatapathId(String datapathId) {
		this.datapathId = datapathId;
	}
	
	public String getOfcIp() {
		return ofcIp;
	}

	public void setOfcIp(String ofcIp) {
		this.ofcIp = ofcIp;
	}

	@Override
	public String toString() {
		Gson gson = new Gson();
		Type type = new TypeToken<DeviceInfo>() {}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public boolean equals(Object obj) {
		if (obj == this) return true;
		if (!super.equals(obj)) return false;
		if (obj.getClass() != this.getClass()) return false;
		DeviceInfo other = (DeviceInfo)obj;
		if (!StringUtils.equals(other.datapathId, this.datapathId)) return false;
		if (!StringUtils.equals(other.ofcIp,      this.ofcIp     )) return false;
		return true;
	}
	@Override
	public int hashCode() {
		int hash = super.hashCode();
		if (this.datapathId != null) {
			hash += this.datapathId.hashCode();
		}
		if (this.ofcIp != null) {
			hash += this.ofcIp.hashCode();
		}
		return hash;
	}
	@Override
	public DeviceInfo clone() {
		DeviceInfo newObj = (DeviceInfo)super.clone();
		if (this.datapathId != null) {
			newObj.datapathId = new String(this.datapathId);
		}
		if (this.ofcIp != null) {
			newObj.ofcIp      = new String(this.ofcIp);
		}
		return newObj;
	}
} public class PortData extends PortInfo implements Cloneable {
	private String deviceName;

	@Override
	public PortData clone() {
		PortData newObj = (PortData)super.clone();
		if (this.deviceName != null) {
			newObj.deviceName = new String(this.deviceName);
		}
		return newObj;
	}
	@Override
	public boolean equals(Object obj) {
		if (obj == this) return true;
		if (obj == null) return false;
		if (obj.getClass() != this.getClass()) return false;
		PortData other = (PortData)obj;
		if (!StringUtils.equals(other.deviceName, this.deviceName)) return false;
		return super.equals(obj);
	}
	@Override
	public int hashCode() {
		int hash = super.hashCode();
		if (this.deviceName != null) {
			hash += this.deviceName.hashCode();
		}
		return hash;
	}
	@Override
	public String toString() {
		Gson gson = new Gson();
		return gson.toJson(this);
	}

	/* Setters and Getters */
	public String getDeviceName() {
		return deviceName;
	}
	public void setDeviceName(String deviceName) {
		this.deviceName = deviceName;
	}
} public class LogicalLink implements Cloneable {
	private List<PortData> link;

	/* Setters and Getters */
	public List<PortData> getLink() {
		return link;
	}
	public void setLink(List<PortData> link) {
		this.link = link;
	}


	@Override
	public boolean equals(Object obj) {
		if(this == obj) return true;
		if(obj == null) return false;
		if(this.getClass() != obj.getClass()) return false;
		LogicalLink other = (LogicalLink)obj;
		if (other.link == this.link) return true;
		if (other.link == null) return false;
		if (this.link  == null) return false;
		if (!other.link.containsAll(this.link)) return false;
		if (!this.link.containsAll(other.link)) return false;
		return true;
	}
	@Override
	public int hashCode() {
		int hash = 0;
		if (this.link != null) {
			for (PortData port : this.link) {
				hash += port.hashCode();
			}
		}
		return hash;
	}
	@Override
	public LogicalLink clone() {
		LogicalLink newObj = new LogicalLink();
		if (this.link != null) {
			newObj.link = new ArrayList<PortData>();
			for (PortData port : this.link) {
				newObj.link.add(port.clone());
			}
		}
		return newObj;
	}
	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<LogicalLink>(){}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public String toString() {
		return this.toJson();
	}
} public class LogicalTopology implements Cloneable {
	private List<OfpConDeviceInfo> nodes;
	private List<LogicalLink> links;
	private List<getnetwork> OuterTag_List;

	/* Setters and Getters */
	public List<OfpConDeviceInfo> getNodes() {
		return nodes;
	}
	public List<LogicalLink> getLinks() {
		return links;
	}
	public void setNodes(List<OfpConDeviceInfo> nodes) {
		this.nodes = nodes;
	}
	public void setLinks(List<LogicalLink> links) {
		this.links = links;
	}
	public void setNetwork(List<getnetwork> OuterTag_List){
		this.OuterTag_List = OuterTag_List;
	}

	public LogicalTopology sub(LogicalTopology other) {
		LogicalTopology newObj = this.clone();
		newObj.nodes.removeAll(other.nodes);
		newObj.links.removeAll(other.links);
		return newObj;
	}


	@Override
	public LogicalTopology clone() {
		LogicalTopology newObj = null;
		try {
			newObj = (LogicalTopology)super.clone();
		} catch (CloneNotSupportedException e) {
			return null;
		}
		newObj.nodes = new ArrayList<OfpConDeviceInfo>();
		newObj.links = new ArrayList<LogicalLink>();

		for (OfpConDeviceInfo node : this.nodes) {
			newObj.nodes.add(node.clone());
		}
		for (LogicalLink link : links) {
			newObj.links.add(link.clone());
		}
		return newObj;
	}
	@Override
	public String toString() {
		Gson gson = new Gson();
		Type type = new TypeToken<LogicalTopology>() {}.getType();
		return gson.toJson(this, type);
	}



	public static class OfpConDeviceInfo extends Node implements Cloneable {
		private List<OfpConPortInfo> ports;

		/* Setters and Getters */
		public List<OfpConPortInfo> getPorts() {
			return ports;
		}
		public void setPorts(List<OfpConPortInfo> ports) {
			this.ports = ports;
		}

		@Override
		public OfpConDeviceInfo clone() {
			OfpConDeviceInfo newObj = (OfpConDeviceInfo)super.clone();
			return newObj;
		}
		@Override
		public boolean equals(Object obj) {
			if (obj == this) return true;
			if (obj == null) return false;
			if (obj.getClass() != this.getClass()) return false;
			OfpConDeviceInfo other = (OfpConDeviceInfo)obj;
			if (other.ports == this.ports) return true;
			if (other.ports == null) return false;
			if (this.ports  == null) return false;
			if (!other.ports.containsAll(this.ports)) return false;
			if (!this.ports.containsAll(other.ports)) return false;
			return true;
		}
		@Override
		public int hashCode() {
			int hash = super.hashCode();
			if (this.ports != null) {
				for (OfpConPortInfo port : ports) {
					hash += port.hashCode();
				}
			}
			return hash;
		}
		@Override
		public String toString() {
			Gson gson = new Gson();
			Type type = new TypeToken<OfpConDeviceInfo>() {}.getType();
			return gson.toJson(this, type);
		}
	}



	public static class getnetwork extends NetworkId implements Cloneable {
		@Override
		public NetworkId clone() {
			getnetwork newObj = (getnetwork)super.clone();
			return newObj;
		}
		@Override
		public String toString() {
			Gson gson = new Gson();
			Type type = new TypeToken<getnetwork>() {}.getType();
			return gson.toJson(this, type);
		}
	}
	
	public static class OfpConPortInfo extends PortInfo implements Cloneable {
		private PortData ofpPortLink;

		/* Setters and Getters */
		public PortData getOfpPortLink() {
			return ofpPortLink;
		}
		public void setOfpPortLink(PortData ofpPortLink) {
			this.ofpPortLink = ofpPortLink;
		}

		@Override
		public OfpConPortInfo clone() {
			OfpConPortInfo newObj = (OfpConPortInfo)super.clone();
			newObj.ofpPortLink = this.ofpPortLink.clone();
			return newObj;
		}
		@Override
		public boolean equals(Object obj) {
			if (obj == this) return true;
			if (obj == null) return false;
			if (obj.getClass() != this.getClass()) return false;
			OfpConPortInfo other = (OfpConPortInfo)obj;
			if (!ObjectUtils.equals(other.ofpPortLink, this.ofpPortLink)) return false;
			return super.equals(obj);
		}
		@Override
		public int hashCode() {
			int hash = super.hashCode();
			if (this.ofpPortLink != null) {
				hash += this.ofpPortLink.hashCode();
			}
			return hash;
		}
		@Override
		public String toString() {
			Gson gson = new Gson();
			return gson.toJson(this);
		}
	}
} public class LogicalTopologyGetJsonOut extends BaseResponse {
	private LogicalTopology result = new LogicalTopology();

	public LogicalTopology getResult() {
		return result;
	}
	public void setResult(LogicalTopology result) {
		this.result = result;
	}

	public static LogicalTopologyGetJsonOut fromJson(String json) {
		Gson gson = new Gson();
		Type type = new TypeToken<LogicalTopologyGetJsonOut>(){}.getType();
		return gson.fromJson(json, type);
	}
	@Override
	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<LogicalTopologyGetJsonOut>(){}.getType();
		return gson.toJson(this, type);
	}

	@Override
	public boolean equals(Object obj) {
		if(this == obj) return true;
		if(obj == null) return false;
		if(this.getClass() != obj.getClass()) return false;
		LogicalTopologyGetJsonOut other = (LogicalTopologyGetJsonOut)obj;
		if(this.getStatus() != other.getStatus()) return false;
		if(! this.getMessage().equals(other.getMessage())) return false;
		return this.result.equals(other.result);
	}
	@Override
	public int hashCode() {
		int hash = super.hashCode();
		if(this.result != null) hash += this.result.hashCode();
		return hash;
	}
	@Override
	public String toString() {
		return this.toJson();
	}
} public static class OfpConDeviceInfo extends Node implements Cloneable {
	private List<OfpConPortInfo> ports;

	/* Setters and Getters */
	public List<OfpConPortInfo> getPorts() {
		return ports;
	}
	public void setPorts(List<OfpConPortInfo> ports) {
		this.ports = ports;
	}

	@Override
	public OfpConDeviceInfo clone() {
		OfpConDeviceInfo newObj = (OfpConDeviceInfo)super.clone();
		return newObj;
	}
	@Override
	public boolean equals(Object obj) {
		if (obj == this) return true;
		if (obj == null) return false;
		if (obj.getClass() != this.getClass()) return false;
		OfpConDeviceInfo other = (OfpConDeviceInfo)obj;
		if (other.ports == this.ports) return true;
		if (other.ports == null) return false;
		if (this.ports  == null) return false;
		if (!other.ports.containsAll(this.ports)) return false;
		if (!this.ports.containsAll(other.ports)) return false;
		return true;
	}
	@Override
	public int hashCode() {
		int hash = super.hashCode();
		if (this.ports != null) {
			for (OfpConPortInfo port : ports) {
				hash += port.hashCode();
			}
		}
		return hash;
	}
	@Override
	public String toString() {
		Gson gson = new Gson();
		Type type = new TypeToken<OfpConDeviceInfo>() {}.getType();
		return gson.toJson(this, type);
	}
} public static class OfpConPortInfo extends PortInfo implements Cloneable {
	private PortData ofpPortLink;

	/* Setters and Getters */
	public PortData getOfpPortLink() {
		return ofpPortLink;
	}
	public void setOfpPortLink(PortData ofpPortLink) {
		this.ofpPortLink = ofpPortLink;
	}

	@Override
	public OfpConPortInfo clone() {
		OfpConPortInfo newObj = (OfpConPortInfo)super.clone();
		newObj.ofpPortLink = this.ofpPortLink.clone();
		return newObj;
	}
	@Override
	public boolean equals(Object obj) {
		if (obj == this) return true;
		if (obj == null) return false;
		if (obj.getClass() != this.getClass()) return false;
		OfpConPortInfo other = (OfpConPortInfo)obj;
		if (!ObjectUtils.equals(other.ofpPortLink, this.ofpPortLink)) return false;
		return super.equals(obj);
	}
	@Override
	public int hashCode() {
		int hash = super.hashCode();
		if (this.ofpPortLink != null) {
			hash += this.ofpPortLink.hashCode();
		}
		return hash;
	}
	@Override
	public String toString() {
		Gson gson = new Gson();
		return gson.toJson(this);
	}
} public static class getnetwork extends NetworkId implements Cloneable {
	@Override
	public NetworkId clone() {
		getnetwork newObj = (getnetwork)super.clone();
		return newObj;
	}
	@Override
	public String toString() {
		Gson gson = new Gson();
		Type type = new TypeToken<getnetwork>() {}.getType();
		return gson.toJson(this, type);
	}
} public class ConnectPhysicalLinksJsonIn {
	private List<PhysicalLink> links;

	public static ConnectPhysicalLinksJsonIn fromJson(String json) {
		Gson gson = new Gson();
		Type type = new TypeToken<ConnectPhysicalLinksJsonIn>() {}.getType();
		return gson.fromJson(json, type);
	}
	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<ConnectPhysicalLinksJsonIn>() {}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public String toString() {
		return this.toJson();
	}


	/* Setters and Getters */
	public List<PhysicalLink> getLinks() {
		return links;
	}
	public void setLinks(List<PhysicalLink> links) {
		this.links = links;
	}
} public class DisconnectPhysicalLinksJsonIn {
	private List<PhysicalLink> links;

	public static DisconnectPhysicalLinksJsonIn fromJson(String json) {
		Gson gson = new Gson();
		Type type = new TypeToken<DisconnectPhysicalLinksJsonIn>() {}.getType();
		return gson.fromJson(json, type);
	}
	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<DisconnectPhysicalLinksJsonIn>() {}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public String toString() {
		return this.toJson();
	}


	/* Setters and Getters */
	public List<PhysicalLink> getLinks() {
		return links;
	}
	public void setLinks(List<PhysicalLink> links) {
		this.links = links;
	}
} public class PhysicalLink {
	private String band;
	private List<PortData> link;

	@Override
	public String toString() {
		Gson gson = new Gson();
		Type type = new TypeToken<PhysicalLink>(){}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public boolean equals(Object obj) {
		if (obj == this) return true;
		if (obj == null) return false;
		if (obj.getClass() != this.getClass()) return false;
		PhysicalLink other = (PhysicalLink)obj;
		if (!other.band.equals(this.band)) return false;

		if (other.link == this.link) return true;
		if (other.link == null) return false;
		if (this.link  == null) return false;
		if (other.link.size() != this.link.size()) return false;
		if (!other.link.containsAll(this.link)) return false;
		if (!this.link.containsAll(other.link)) return false;
		return true;
	}
	@Override
	public int hashCode() {
		return link.hashCode();
	}

	/* Setters and Getters */
	public List<PortData> getLink() {
		return link;
	}
	public void setLink(List<PortData> link) {
		this.link = link;
	}

	public String getBand() {
		return band;
	}
	public void setBand(String band) {
		this.band = band;
	}
} public class AddNetworkId {
	int start,end;
	String type;

	public static AddNetworkId fromJson(String json) {
		Gson gson = new Gson();
		Type type = new TypeToken<AddNetworkId>() {}.getType();
		return gson.fromJson(json, type);
	}
	public String toJson() {
		Gson gson = new Gson();
		Type type = new TypeToken<AddNetworkId>() {}.getType();
		return gson.toJson(this, type);
	}
	@Override
	public String toString() {
		return this.toJson();
	}


	/* Setters and Getters */
	public int GetStart()
	{
		return start;
	}
	public int GetEnd()
	{
		return end;
	}
	public String GetType()
	{
		return type;
	}
} public interface Config {
	/**
     * Get value to corresponding to the key.
     *
     * @param key
     * @return
     * 	string value.
     */
    String getString(String key);

    /**
     * Get value to corresponding to the key.
     * Specified value is returned if not contains key to config file.
     *
     * @param key
     * @param defaultValue
     * @return
     * 	String value.
     */
    String getString(String key, String defaultValue);

    /**
     * Get value to corresponding to the key.
     *
     * @param key
     * @return
     * 	int value.
     */
    int getInt(String key);

    /**
     * Get value to corresponding to the key.
     * Specified value is returned if not contains key to config file.
     *
     * @param key
     * @param defaultValue
     * @return
     * 	int value.
     */
    int getInt(String key, int defaultValue);

    /**
     * Return with string in contents of config file.
     *
     * @return
     * 	Contents of config file.
     */
    String getContents();

    /**
     * Return with Configuration object in contents of config file.
     *
     * @return
     * 	Contents of Configuration object.
     */
    Configuration getConfiguration();

    List<Object> getList(String key);
} public class ConfigImpl implements Config {

    private static final Logger logger = Logger.getLogger(ConfigImpl.class);

    private Configuration config = null;

    /**
     * Load config file.
     *
     * @throws RuntimeException
     * Failed to load config file.
     */
    public ConfigImpl() {
        this(DEFAULT_PROPERTIY_FILE);
    }

    /**
     * Specify config file and then create instance.
     *
     * @param config
     * 	File path.
     */
    public ConfigImpl(String config) {
        try {
            this.config = new PropertiesConfiguration(config);
        } catch (ConfigurationException e) {
            String message = "failed to read config file.";
            logger.error(message);
            throw new RuntimeException(message, e);
        }
    }

    /**
     * Specify config object and then create instance.
     *
     * @param config
     * Config object.
     */
    public ConfigImpl(Configuration config) {
        this.config = config;
    }

    @Override
    public String getString(String key) {
        String value = getConfiguration().getString(key);
        return value;
    }

    @Override
    public String getString(String key, String defaultValue) {
        try {
            String value = getConfiguration().getString(key, defaultValue);
            return value;
        } catch (ConversionException e) {
            return defaultValue;
        }
    }

    @Override
    public int getInt(String key) {
        return getConfiguration().getInt(key);
    }

    @Override
    public int getInt(String key, int defaultValue) {
        try {
            int value = getConfiguration().getInt(key, defaultValue);
            return value;
        } catch (ConversionException e) {
            return defaultValue;
        }
    }

    @Override
    public String getContents() {
        PropertiesConfiguration prop = new PropertiesConfiguration();
        prop.append(this.config);
        StringWriter sw = new StringWriter();
        try {
            prop.save(sw);
        } catch (ConfigurationException e) {
            logger.error(e.getMessage());
            throw new RuntimeException(e);
        }
        return sw.toString();
    }

    /**
     * Configuration object is returned, it to provide reference to config file.
     *
     * @return
     * Configuration object to provide reference to config file.
     */
    @Override
    public Configuration getConfiguration() {
        return this.config;
    }

    @Override
    public List<Object> getList(String key) {
           List<Object> values = getConfiguration().getList(key);
           return values;
    }
} public class OFPMUtils {
	/**
	 * Check port contains into nodes.
	 * @param nodes
	 * @param deviceName port device name.
	 * @param portName port name. If this param is null, check only device name.
	 * @return
	 */
	public static boolean nodesContainsPort(Collection<OfpConDeviceInfo> nodes, String deviceName, String portName) {
		for (OfpConDeviceInfo device : nodes) {
			if (device.getDeviceName().equals(deviceName)) {
				if (StringUtils.isBlank(portName)) {
					return true;
				}
				for (OfpConPortInfo ofpConPort : device.getPorts()) {
					if (ofpConPort.getPortName().equals(portName)) {
						return true;
					}
				}
			}
		}
		return false;
	}

	/**
	 * transfer macAddress→long
	 * @param mac String ex.AA:BB:CC:DD:EE:FF
	 * @return long transfered
	 * @throws NullPointerException mac is null
	 * @throws NumberFormatException if the String does not contain a parsable long
	 */
	public static long macAddressToLong(String mac) throws NullPointerException, NumberFormatException {
		//String macTmp = StringUtils.join(mac.split(":"));
		String hexMac = mac.replace(":", "");
		long longMac = Long.decode("0x" + hexMac);
		if (longMac < MIN_MACADDRESS_VALUE || MAX_MACADDRESS_VALUE < longMac) {
			String errMsg = String.format(PARSE_ERROR, mac);
			throw new NumberFormatException(errMsg);
		}
		return longMac;
	}

	/**
	 * transfer macAddress→long
	 * @param longMac long
	 * @return macAddress transfered
	 * @throws IllegalFormatException If a format string contains an illegal syntax
	 * @throws NullPointerException longMac is null
	 * @throws NumberFormatException if the String does not contain a parsable long
	 */
	public static String longToMacAddress(long longMac) throws IllegalFormatException, NullPointerException, NumberFormatException {
		if (longMac < MIN_MACADDRESS_VALUE || MAX_MACADDRESS_VALUE < longMac) {
			String errMsg = String.format(PARSE_ERROR, longMac);
			throw new NumberFormatException(errMsg);
		}
		String hex = "000000000000" + Long.toHexString(longMac);
		StringBuilder hexBuilder = new StringBuilder(hex.substring(hex.length()-12));

		for (int i=2; i < 16; i=i+3) {
			hexBuilder.insert(i, ":");
		}
		return hexBuilder.toString();
	}

	/**
	 * Charactor is represented band-width converted to value of Mbps. For example, 1.2Gbps is  converted to 1229.
	 * Available range is from Mbps to Ybps.
	 * @param bandWidth [Number][kMGTPEZY]bps
	 * @return
	 */
	public static long bandWidthToBaseMbps(String bandWidth) {
		String reg = "([0-9]+)([MGTPEZY])bps";
		Pattern pat = Pattern.compile(reg);
		Matcher mat = pat.matcher(bandWidth);
		if (!mat.find()) {
			throw new NumberFormatException(String.format(PARSE_ERROR, bandWidth));
		}
		String numb = mat.group(1);
		String base = mat.group(2);

		long value = Long.parseLong(numb);
		if (base.equals("G")) {
			value *= 1024L;
		} else if (base.equals("T")) {
			value *= 1048576L;
		} else if (base.equals("P")) {
			value *= 1073741824L;
		} else if (base.equals("E")) {
			value *= 1099511627776L;
		} else if (base.equals("Z")) {
			value *= 1125899906842624L;
		} else if (base.equals("Y")) {
			value *= 1152921504606846976L;
		}
		return value;
	}

	/**
	 * Logging stack-trace when error happend.
	 * @param logger
	 * @param t
	 */
	public static void logErrorStackTrace(Logger logger, Throwable t) {
		StringWriter sw = new StringWriter();
		t.printStackTrace(new PrintWriter(sw));
		logger.error(t);
		logger.error(sw.toString());
	}

	public static boolean isNodeTypeOfpSwitch(String nodeType) {
		return (StringUtils.equals(nodeType, NODE_TYPE_LEAF) || StringUtils.equals(nodeType, NODE_TYPE_SPINE));
	}

	/**
	 * Compare PortData. If one-sided portName is blank, compare only deviceName.
	 * @param port1
	 * @param port2
	 * @return
	 */
	public static boolean PortDataNonStrictEquals(PortData port1, PortData port2) {
		boolean ret = false;
		if (StringUtils.isBlank(port1.getPortName()) || StringUtils.isBlank(port2.getPortName())) {
			ret = StringUtils.equals(port1.getDeviceName(), port2.getDeviceName());
		} else {
			ret = port1.equals(port2);
		}
		return ret;
	}
} public abstract class BaseValidate {
	/**
	 * Determine if Object is null.
	 *
	 * @param value
	 * @return
	 * true : Not null. <br>
	 * false: null.
	 */
	protected static boolean checkNull(Object value) {
		return value == null;
	}

	/**
	 * Determine if length of string is less than or equal length.
	 *
	 * @param value
	 * @param length
	 * @return
	 * true : Not over. <br>
	 * false: Over.
	 */
	protected static boolean checkOverLength(String value, int length) {
		if (value.length() > length) {
			return true;
		}
		return false;
	}

	/**
	 * Determine if string contains multi-byte charactor.
	 *
	 * @param value
	 * @return
	 * true : Not contains multi-byte charactor. <br>
	 * false: Contains multi-byte charactor.
	 */
	protected static boolean checkHalfNum(String value) {
		if (value == null || !value.matches("^[0-9]+$")) {
			return false;
		}
		return true;
	}

	public static void checkArrayStringBlank(List<String> params) throws ValidateException {
		for (String param : params) {
			if (StringUtils.isBlank(param)) {
				throw new ValidateException(String.format(THERE_IS_BLANK, "parameter"));
			}
		}
	}
	public static void checkArrayOverlapped(List<String> params) throws ValidateException {
		int size = params.size();
		for (int dni = 0; dni < size; dni++) {
			for (int ci = dni + 1; ci < size; ci++) {
				if (params.get(dni).equals(params.get(ci))) {
					throw new ValidateException(String.format(THERE_ARE_OVERLAPPED, params.get(dni)));
				}
			}
		}
	}

	public static void checkStringBlank(String param) throws ValidateException {
		if (StringUtils.isBlank(param)) {
			throw new ValidateException(String.format(IS_BLANK, "parameter"));
		}
	}} public class ConnectPhysicalLinksJsonInValidate extends BaseValidate {
	private static Logger logger = Logger.getLogger(ConnectPhysicalLinksJsonInValidate.class);

	public void checkValidation(ConnectPhysicalLinksJsonIn connectPhysicalLink) throws ValidateException {
		final String fname = "checkValidateion";
		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s(connectPhysicalLink=%s) - start", fname, connectPhysicalLink));
		}

		if (BaseValidate.checkNull(connectPhysicalLink)) {
			throw new ValidateException(String.format(IS_NULL, "Input parameter"));
		}

		List<PhysicalLink> links = connectPhysicalLink.getLinks();
		if (BaseValidate.checkNull(links)) {
			throw new ValidateException(String.format(IS_NULL, "links"));
		}

		for (int i = 0; i < links.size(); i++) {
			String msgLinks = "links[" + i + "]";
			PhysicalLink physicalLink = links.get(i);
			if (BaseValidate.checkNull(physicalLink)) {
				throw new ValidateException(String.format(IS_NULL, msgLinks));
			}
			
			String band = physicalLink.getBand();
			if (BaseValidate.checkNull(band)) {
				throw new ValidateException(String.format(IS_NULL, "band"));
			}

			if(!Pattern.matches(REGEX_NUMBER, band)){
				throw new ValidateException(String.format(INVALID_PARAMETER, "band"));				
			}
			
			List<PortData> ports = physicalLink.getLink();
			if (BaseValidate.checkNull(ports)) {
				throw new ValidateException(String.format(IS_NULL, msgLinks + ".link"));
			}
			if (ports.size() != COLLECT_NUMBER_OF_DEVICE_NAMES_IN_LINK) {
				throw new ValidateException(String.format(INVALID_PARAMETER, "Length of " + msgLinks + ".link"));
			}
			for (int pi = 0; pi < ports.size(); pi++) {
				String msgLink = msgLinks + ".link[" + pi + "]";
				PortData port = ports.get(pi);
				if (BaseValidate.checkNull(port)) {
					throw new ValidateException(String.format(IS_NULL,  msgLink));
				}
				if (StringUtils.isBlank(port.getDeviceName())) {
					throw new ValidateException(String.format(IS_BLANK, msgLink + ".deviceName:" + port.getDeviceName()));
				}
				if (StringUtils.isBlank(port.getPortName())) {
					throw new ValidateException(String.format(IS_BLANK, msgLink + ".portName:" + port.getPortName()));
				}
			}
			if (ports.get(0).equals(ports.get(1))) {
				PortData port = ports.get(0);
				throw new ValidateException(String.format(THERE_ARE_OVERLAPPED, port.getDeviceName() + '.' + port.getPortName() + " in " + msgLinks));
			};
		}

		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s() - end", fname));
		}
	}
} public class DisconnectPhysicalLinksJsonInValidate extends BaseValidate {
	private static Logger logger = Logger.getLogger(DisconnectPhysicalLinksJsonInValidate.class);

	public void checkValidation(DisconnectPhysicalLinksJsonIn disconnectPhysicalLink) throws ValidateException {
		final String fname = "checkValidateion";
		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s(disconnectPhysicalLink=%s) - start", fname, disconnectPhysicalLink));
		}

		if (BaseValidate.checkNull(disconnectPhysicalLink)) {
			throw new ValidateException(String.format(IS_NULL, "Input parameter"));
		}

		List<PhysicalLink> links = disconnectPhysicalLink.getLinks();
		if (BaseValidate.checkNull(links)) {
			throw new ValidateException(String.format(IS_NULL, "links"));
		}
		for (int i = 0; i < links.size(); i++) {
			String msgLinks = "links[" + i + "]";
			PhysicalLink physicalLink = links.get(i);
			if (BaseValidate.checkNull(physicalLink)) {
				throw new ValidateException(String.format(IS_NULL, msgLinks));
			}
			List<PortData> ports = physicalLink.getLink();

			String band = physicalLink.getBand();
			if (BaseValidate.checkNull(band)) {
				throw new ValidateException(String.format(IS_NULL, "band"));
			}

			if(!Pattern.matches(REGEX_NUMBER, band)){
				throw new ValidateException(String.format(INVALID_PARAMETER, "band"));				
			}
						
			if (BaseValidate.checkNull(ports)) {
				throw new ValidateException(String.format(IS_NULL, msgLinks + ".link"));
			}
			if (ports.size() != COLLECT_NUMBER_OF_DEVICE_NAMES_IN_LINK) {
				throw new ValidateException(String.format(INVALID_PARAMETER, "Length of " + msgLinks + ".link"));
			}
			for (int pi = 0; pi < ports.size(); pi++) {
				String msgLink = msgLinks + ".link[" + pi + "]";
				PortData port = ports.get(pi);
				if (BaseValidate.checkNull(port)) {
					throw new ValidateException(String.format(IS_NULL,  msgLink));
				}
				if (StringUtils.isBlank(port.getDeviceName())) {
					throw new ValidateException(String.format(IS_BLANK, msgLink + ".deviceName:" + port.getDeviceName()));
				}
				if (StringUtils.isBlank(port.getPortName())) {
					throw new ValidateException(String.format(IS_BLANK, msgLink + ".portName" + port.getPortName()));
				}
			}
		}

		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s() - end", fname));
		}
	}
} public class AddNetworkIdJsonInValidate extends BaseValidate {
	private static Logger logger = Logger.getLogger(AddNetworkIdJsonInValidate.class);

	public void checkValidation(AddNetworkId inParam) throws ValidateException {
		final String fname = "checkValidateion";
		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s(connectPhysicalLink=%s) - start", fname, inParam));
		}

		if (BaseValidate.checkNull(inParam)) {
			throw new ValidateException(String.format(IS_NULL, "Input parameter"));
		}
						
		if(inParam.GetStart()<0 || 4096 < inParam.GetStart())
		{
			throw new ValidateException(String.format(INVALID_PARAMETER, "NetworkID Start"));				

		}
		if(inParam.GetEnd()<0 || 4096 < inParam.GetEnd() || inParam.GetStart() > inParam.GetEnd())
		{
			throw new ValidateException(String.format(INVALID_PARAMETER, "NetworkID End"));				

		}

		if(!inParam.GetType().toString().equals("OIX") && !inParam.GetType().toString().equals("ToT"))
		{
			throw new ValidateException(String.format(INVALID_PARAMETER, "NetworkType"));							
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s() - end", fname));
		}
	}
} public interface ConnectionUtilsJdbc {
	 /**
     * get connection
     * @param autoCommit
     * @return connection
     * @throws SQLException
     */
    public abstract Connection getConnection(boolean autoCommit) throws SQLException;

    /**
     * close connection
     * @param conn
     */
    public abstract void close(Connection conn);

    /**
     * exec query
     * @param conn
     * @param sql
     * @param handler
     * @return result SQL
     * @throws SQLException
     */
    public abstract <T> T query(Connection conn, String sql,
            ResultSetHandler<T> handler, Object... params) throws SQLException;

    public abstract <T> T query(Connection conn, String sql,
            ResultSetHandler<T> handler) throws SQLException;
    /**
     * commit
     * @param conn
     * @throws SQLException
     */
    public abstract void commit(Connection conn) throws SQLException;

    /**
     * rollback
     * @param conn
     */
    public abstract void rollback(Connection conn);

    /**
     * insert
     * @param conn
     * @param sql
     * @param params
     * @return line of insert
     * @throws SQLException
     */
    public abstract int update(Connection conn, String sql, Object[] params)
            throws SQLException;
    /**
     * insert
     * @param conn
     * @param sql
     * @return line of insert
     * @throws SQLException
     */
    public abstract int update(Connection conn, String sql)
            throws SQLException;

    /**
     * exec query
     * @param conn
     * @param sql
     * @throws SQLException
     */
    public abstract void query(Connection conn, String sql) throws SQLException;

} public class ConnectionUtilsJdbcImpl implements ConnectionUtilsJdbc {

	private static final Logger logger = Logger.getLogger(ConnectionUtilsJdbcImpl.class);

    /**
     * setting file
     */
    private Config config;

    /**
     * default constructor
     */
    public ConnectionUtilsJdbcImpl() {
        this.config = new ConfigImpl();
    }

    /**
     * constructor with parameter
     * @param config
     */
    public ConnectionUtilsJdbcImpl(Config config) {
        this.config = config;
    }

    @Override
    public Connection getConnection(boolean autoCommit) throws SQLException {
        Connection conn = ConnectionManagerJdbc.getInstance(config).getConnection();
        conn.setAutoCommit(autoCommit);
        return conn;
    }

    @Override
    public void close(Connection conn) {
        try {
        	ConnectionManagerJdbc.getInstance(config).close(conn);
        } catch (SQLException se) {
            String message = "failed to close connection.";
            logger.warn(message);
        }
    }

    @Override
    public void commit(Connection conn) throws SQLException {
    	ConnectionManagerJdbc.getInstance(config).commit(conn);
    }

    @Override
    public void rollback(Connection conn) {
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("rollback(conn=%s) - start ", conn));
        }
        try {
        	ConnectionManagerJdbc.getInstance(config).rollback(conn);
        } catch (SQLException se) {
            String message = "failed to rollback connection.";
            logger.warn(message);
        }
        if (logger.isDebugEnabled()) {
            logger.debug("rollback() - end ");
        }
    }

    @Override
    public int update(Connection conn, String sql, Object[] params)
            throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("update(conn=%s, sql=%s, params=%s) - start ", conn, sql, params));
        }
        QueryRunner qRunner = new QueryRunner();
        int rows = qRunner.update(conn, sql, params);
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("update(rows=%s) - end ", rows));
        }
        return rows;
    }

    @Override
    public int update(Connection conn, String sql) throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("update(conn=%s, sql=%s) - start ", conn, sql));
        }
        QueryRunner qRunner = new QueryRunner();
        int rows = qRunner.update(conn, sql);
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("update(rows=%s) - end ", rows));
        }
        return rows;
    }

    @Override
    public void query(Connection conn, String sql) throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.debug(String.format("query(conn=%s, sql=%s) - start ", conn, sql));
        }
        QueryRunner qRunner = new QueryRunner();
        ResultSetHandler<Object> rsh = null;
        qRunner.query(conn, sql, rsh);
        if (logger.isDebugEnabled()) {
            logger.debug("query() - end ");
        }
    }

    @Override
    public <T> T query(Connection conn, String sql,
            ResultSetHandler<T> handler, Object... params) throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.trace(String.format("query(conn=%s, sql=%s, handler=%s, params=%s) - start ", conn, sql, handler, params.toString()));
        }
        QueryRunner qRunner = new QueryRunner();
        T records = qRunner.query(conn, sql, handler, params);
        if (logger.isDebugEnabled()) {
            logger.trace(String.format("query(records=%s) - end ", records));
        }
        return records;
    }

    @Override
    public <T> T query(Connection conn, String sql,
            ResultSetHandler<T> handler) throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.trace(String.format("query(conn=%s, sql=%s, handler=%s) - start ", conn, sql, handler));
        }
        QueryRunner qRunner = new QueryRunner();
        T records = qRunner.query(conn, sql, handler);
        if (logger.isDebugEnabled()) {
            logger.trace(String.format("query(records=%s) - end ", records));
        }
        return records;
    }
} public interface Dao {

	/**
	 * set connectionUtilsJdbc
	 * @param utils
	 */
	void setConnectionUtilsJdbc(ConnectionUtilsJdbc utils);

	void close() throws SQLException;

	/**
	 * get device name from datapathid
	 * @param datapathId
	 * @return device name
	 * @throws SQLException failed sql
	 */
	String getDeviceNameFromDatapathId(Connection conn, String datapathId) throws SQLException;

	/**
	 * get port RID from deviceName and portNumber
	 * @param deviceName
	 * @param portNumber
	 * @return port Rid
	 * @throws SQLException
	 */
	String getPortRidFromDeviceNamePortNumber(Connection conn, String deviceName, Integer portNumber) throws SQLException;

	/**
	 * @param portRid
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Map<String, Object>>> getDevicePortInfoSetFlowFromPortRid(Connection conn, String portRid) throws SQLException;

	/**
	 * get internalmac (if exist then to return it, not if generate internalmac)
	 * @param deviceName
	 * @param inPort
	 * @param srcMac
	 * @param dstMac
	 * @return internalmac String
	 * @throws SQLException
	 */
	//static synchronized String getInternalMacFromDeviceNameInPortSrcMacDstMac(ConnectionUtilsJdbc utilsJdbc, Connection conn, String deviceName, String inPort, String srcMac, String dstMac) throws SQLException;

	/**
	 * get nw_instance_id
	 * @param conn
	 * @throws SQLException
	 */
	Long getNwInstanceId(Connection conn) throws SQLException;

	/**
	 * get internal-mac-address list. if not exist, return empty list.
	 * @param conn
	 * @param deviceName
	 * @param inPort
	 * @return
	 * @throws SQLException
	 */
	List<String> getInternalMacListFromDeviceNameInPort(Connection conn, String deviceName, String inPort) throws SQLException;

	/**
	 * Get internal-mac record list. if not exist, return empty list.
	 * @param conn
	 * @param deviceName
	 * @param PortNumber
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getInternalMacInfoListFromDeviceNameInPort(Connection conn, String deviceName, Integer PortNumber) throws SQLException;

	/**
	 * Delete internal-mac-address.
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	int deleteInternalMac(Connection conn, String deviceName, int inPort) throws SQLException;

	/*********************************************************************************
	 * ----------------------------------------------------------------------------- *
	 *                               JDBC
	 * ----------------------------------------------------------------------------- *
	 *********************************************************************************/

	/**
	 * Get patchWiring-list that is connected to other devices.
	 * @param devName
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getPatchWiringsFromDeviceName(Connection conn, String deviceName) throws SQLException;

	/**
	 * Get LogicalLink-list that is connected to other devices.
	 * @param devName
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getLogicalLinksFromDeviceName(Connection conn, String deviceName) throws SQLException;

	/**
	 * Check if contains pair of deviceName and portName into patchWiring .
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	boolean isContainsPatchWiringFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Check if contains pair of deviceName and portName into logicalLink .
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	boolean isContainsLogicalLinkFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException;


	/**
	 * Check if contains device name into patchWirings inDeviceName or outDeviceName
	 * @param conn
	 * @param deviceName
	 * @return
	 * @throws SQLException
	 */
	boolean isDeviceNameContainedIntoPatchWiring(Connection conn, String deviceName) throws SQLException;

	/**
	 * Check if contains device rid into patchWirings parent
	 * @param conn
	 * @param nodeRid
	 * @return
	 * @throws SQLException
	 */
	boolean isNodeRidContainedIntoPatchWiring(Connection conn, String nodeRid) throws SQLException;

	/**
	 * Check if containts port rid into patchWirings in or out.
	 * @param conn
	 * @param portRid
	 * @return
	 * @throws SQLException
	 */
	boolean isPortRidContainedIntoPatchWiring(Connection conn, String portRid) throws SQLException;

	/**
	 * Insert patch-wiring infromation into db.
	 * @param ofpRid RID of of-patch switch.
	 * @param in RID of of-patchs in port.
	 * @param out RID of of-patchs out port.
	 * @param inDeviceName
	 * @param inPortName
	 * @param outDeviceName
	 * @param outPortName
	 * @param sequence
	 * @param Connection conn
	 * @throws SQLException
	 */
	int insertPatchWiring(Connection conn, String ofpRid, String in, String out, String inDeviceName, String inPortName, String outDeviceName, String outPortName, int sequence) throws SQLException;

	/**
	 * Insert logical link infromation into db.
	 * @param Connection conn
	 * @param in_node_id
	 * @param in_node_name
	 * @param in_port_id
	 * @param in_port_name
	 * @param out_node_rid
	 * @param out_node_name
	 * @param out_port_id
	 * @param out_port_name
	 * @param nw_instance_id
	 * @param nw_instance_type
	 * @throws SQLException
	 */
	int insertLogicalLink(Connection conn, String in_node_id, String in_node_name, String in_port_id, String in_port_name, String out_node_id, String out_node_name, String out_port_id, String out_port_name, Long nw_instance_id, String nw_instance_type) throws SQLException;

	/**
	 * Get logical link infromation.
	 * @param Connection conn
	 * @param node_name
	 * @param port_name
	 * @throws SQLException
	 */
	Map<String, Object> getLogicalLinkFromNodeNamePortName(Connection conn, String node_name, String port_name) throws SQLException;
	/**
	 * Get logical link infromation.
	 * @param Connection conn
	 * @param logicalLinkRid
	 * @throws SQLException
	 */
	Map<String, Object> getLogicalLinkFromRid(Connection conn, String logicalLinkRid) throws SQLException;

	/**
	 * Delete LogicalLink from devices port.
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	int deleteLogicalLinkFromNodeNamePortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Get route list.
	 * @param Connection conn
	 * @param logical_link_id
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getRouteFromLogicalLinkId(Connection conn,  String logical_link_id) throws SQLException;

	/**
	 * Get route list.
	 * @param Connection conn
	 * @param logical_link_id
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getRouteFromNodeRid(Connection conn,  String nodeRid) throws SQLException;

	/**
	 * Delete Route-list from logical link rid.
	 * @param conn
	 * @param logical_link_id
	 * @return
	 * @throws SQLException
	 */
	int deleteRouteFromLogicalLinkRid(Connection conn, String logical_link_id) throws SQLException;

	/**
	 * Insert route data into db.
	 * @param Connection conn
	 * @param Integer sequence_num
	 * @param String logical_link_id
	 * @param String node_id
	 * @param String node_name
	 * @param String in_port_id
	 * @param String in_port_name
	 * @param String in_port_number
	 * @param String out_port_id
	 * @param String out_port_name
	 * @param String out_port_number
	 * @throws SQLException
	 */
	int insertRoute(Connection conn, Integer sequence_num, String logical_link_id, String node_id, String node_name, String in_port_id, String in_port_name, Integer in_port_number, String out_port_id, String out_port_name, Integer out_port_number) throws SQLException;

	/**
	 * Delete patchWiring-list from devices port.
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	int deletePatchWiring(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Get patchWiring-list from devices port.
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getPatchWiringsFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Ge patchWiring-list from parent rid.
	 * @param conn
	 * @param parentRid
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getPatchWiringsFromParentRid(Connection conn, String parentRid) throws SQLException;


	/*============= __     __
	 *  CableLink   #_=====_#
	 *=============           */
	/**
	 * Get link-list that  is connected to other devices port.
	 * The link is correspond to LAN-cable or SPF-cable.
	 * @param devName
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getCableLinksFromDeviceName(Connection conn, String deviceName) throws SQLException;

	/**
	 * Get link-list that is connected to other devices port from in-ports rid.
	 * The link is correspond to LAN-cable or SPF-cable.
	 * @param conn
	 * @param inPortRid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getCableLinkFromInPortRid(Connection conn, String inPortRid) throws SQLException;

	/**
	 * Get link-list that is connected to other devices port from out-ports rid.
	 * The link is correspond to LAN-cable or SPF-cable.
	 * @param conn
	 * @param outPortRid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getCableLinkFromOutPortRid(Connection conn, String outPortRid) throws SQLException;

	/**
	 * Create cable link by device name and port name.
	 * Used value set to 0.
	 * @param conn
	 * @param deviceName0
	 * @param portName0
	 * @param deviceName1
	 * @param portName1
	 * @return
	 * @throws SQLException
	 */
	int createCableLink(Connection conn, String deviceName0, String portName0, String deviceName1, String portName1) throws SQLException;

	/**
	 * Delete cable link by device name and port name.
	 * If cable link is used to patchWiring, not delete.
	 * @param conn
	 * @param deviceName0
	 * @param portName0
	 * @param deviceName1
	 * @param portName1
	 * @return
	 * @throws SQLException
	 */
	int deleteCableLink(Connection conn, String deviceName0, String portName0, String deviceName1, String portName1) throws SQLException;

	/**
	 * Modify used-value of cable-link that include ports-rid.
	 * Calbe-link represent LAN-Cable, SFP-Cable.
	 * @param conn
	 * @param portRid
	 * @param newUsed
	 * @throws SQLException
	 */
	void updateCableLinkUsedFromPortRid(Connection conn, String portRid, long newUsed) throws SQLException;



	/**
	 * Get port-to-port path that computed by dijkstra.
	 * @param conn
	 * @param ridA
	 * @param ridZ
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getShortestPath(Connection conn, String ridA, String ridZ) throws SQLException;


	/*================ |1_2 3_4 5_6 7_8   RS-485 |
	 *  NodeInfo I/F   |[_] [_] [_] [_]  _______ |
	 *================ |[_]_[_]_[_]_[_]__\_::::_/| */
	/**
	 * Get DeviceInfo from device name.
	 * @param conn
	 * @param deviceName
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getNodeInfoFromDeviceName(Connection conn, String deviceName) throws SQLException;

	/**
	 * Get DeviceInfo from devices rid.
	 * @param conn
	 * @param ofpRid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getNodeInfoFromDeviceRid(Connection conn, String ofpRid) throws SQLException;

	/**
	 * Get DeviceInfo list in db.
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getNodeInfoList(Connection conn) throws SQLException;

	/**
	 * Get node rid from device name.
	 * @param conn
	 * @param deviceName
	 * @return
	 * @throws SQLException
	 */
	String getNodeRidFromDeviceName(Connection conn, String deviceName) throws SQLException;


	/**
	 * Create DeviceInfo.
	 * @param conn
	 * @param deviceName
	 * @param deviceType
	 * @param datapathId
	 * @param ofcIp
	 * @return
	 */
	int createNodeInfo(Connection conn, String deviceName, String deviceType, String location, String tenant, String datapathId, String ofcIp) throws SQLException;

	/**
	 * Update DeviceInfo.
	 * @param conn
	 * @param keyDeviceName current device name.
	 * @param deviceName new device name
	 * @param datapathId new datapath id
	 * @param ofcIp new openflow controller ip
	 * @return
	 * @throws SQLException
	 */
	int updateNodeInfo(Connection conn, String keyDeviceName, String deviceName, String location, String tenant, String datapathId, String ofcIp, String type) throws SQLException;

	/**
	 * Delete DeviceInfo
	 * @param conn
	 * @param deviceName deleted device name
	 * @return
	 * @throws SQLException
	 */
	int deleteNodeInfo(Connection conn, String deviceName) throws SQLException;


	/*================ | _|    |_ |
	 *  PortInfo I/F   ||        ||
	 *================ ||__====__|| */
	/**
	 * Create PortInfo.
	 * @param conn
	 * @param portName
	 * @param portNumber
	 * @param band
	 * @param deviceName
	 * @param network
	 * @return
	 */
	int createPortInfo(Connection conn, String portName, Integer portNumber, Integer band, String deviceName, String network) throws SQLException;

	/**
	 * Update PortInfo
	 * @param conn
	 * @param keyPortName
	 * @param keyDeviceName
	 * @param portName
	 * @param portNumber
	 * @param band
	 * @return
	 * @throws SQLException
	 */
	int updatePortInfo(Connection conn, String keyPortName, String keyDeviceName, String portName, Integer portNumber, Integer band,String network) throws SQLException;

	/**
	 * Delete PortInfo
	 * @param conn
	 * @param portName
	 * @param deviceName
	 * @return
	 * @throws SQLException
	 */
	int deletePortInfo(Connection conn, String portName, String deviceName) throws SQLException;

	/**
	 * Get port info from device name and port name.
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getPortInfoFromPortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Get port info from rid.
	 * @param conn
	 * @param rid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getPortInfoFromPortRid(Connection conn, String rid) throws SQLException;

	/**
	 * Get port info from device name.
	 * @param conn
	 * @param deviceName
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getPortInfoListFromDeviceName(Connection conn, String deviceName) throws SQLException;

	/**
	 * Get neighbor port from port rid.
	 * @param conn
	 * @param portRid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getNeighborPortFromPortRid(Connection conn, String portRid) throws SQLException;

	/**
	 * Get node info from rid.
	 * @param conn
	 * @param rid
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getDeviceInfoFromDeviceRid(Connection conn, String rid) throws SQLException;


	/**
	 * Get port rid from device name and port name
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	String getPortRidFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Get port band from device name and port name
	 * @param conn
	 * @param deviceName
	 * @param portName
	 * @return
	 * @throws SQLException
	 */
	String getPortBandFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException;

	/**
	 * Get ofc rid from ofcIp
	 * @param conn
	 * @param ofcIp
	 * @return
	 * @throws SQLException
	 */
	String getOfcRid(Connection conn, String ofcIp) throws SQLException;

	/**
	 * Get OfcInfo list in db.
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	List<Map<String, Object>> getOfcInfoList(Connection conn) throws SQLException;

	/**
	 * Get OfcInfo .
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	Map<String, Object> getOfcInfo(Connection conn, String ofcIpPort) throws SQLException;

    /**
     * Get OfcInfo in db.
     * @param conn, ip, port
     * @return
     * @throws SQLException
     */
    Map<String, Object> getOfcRidInfo(Connection conn, String ofcIpPort) throws SQLException;

	/**
     * Update OfcInfo.
     * @param conn
     * @param ofcIp new openflow controller ip
     * @param ofcPort new openflow controller port
     * @return
     * @throws SQLException
     */
    int updateOfcInfo(Connection conn, String ofcIpPort, String ip, Integer port) throws SQLException;

	/**
	 * Create OfcInfo.
	 * @param conn
	 * @param ip
	 * @param port
	 * @return
	 * @throws SQLException
	 */
	int createOfcInfo(Connection conn, String ip, Integer port) throws SQLException;

	/**
	 * Delete ofc
	 * @param conn
	 * @param ofcIpPort
	 * @return
	 * @throws SQLException
	 */
	int deleteOfcInfo(Connection conn, String ofcIpPort) throws SQLException;

	public List<Map<String, Object>> getCableList(Connection conn) throws SQLException;
	public List<Map<String, Object>> getNetworkidFromSpineid(Connection conn, String in_spine_id,String out_spine_id,String network_type) throws SQLException;
	public String payoutNetworkid(Connection conn, String in_spine_id,String out_spine_id,String network_type) throws SQLException;
	public int returnNetworkid(Connection conn, String spine_id,String network_type) throws SQLException;
	public int insertOutertag(Connection conn, String localvlan, String outer_tag, String agid, String ag_inport,String ag_outport,String type,String network_type) throws SQLException;
	public List<Map<String,Object>> getOutertagflows(Connection conn, String agid) throws SQLException;
	public int DeleteOutertagflows(Connection conn,Long localvlan) throws SQLException;
	public int AddNetworkId(Connection conn,int networkid, String type) throws SQLException;
	public int delNetworkId(Connection conn,int networkid, String type) throws SQLException;
    public List<Map<String, Object>> getNetworkId(Connection conn) throws SQLException;

} public class DaoImpl implements Dao {

	private static final Logger logger = Logger.getLogger(DaoImpl.class);

	protected ConnectionUtils utils = null;
	protected ODatabaseDocumentTx database = null;
	protected List<ODocument> documents = null;

	// jdbc
	protected ConnectionUtilsJdbc utilsJdbc = null;

	public DaoImpl(ConnectionUtils utils) throws SQLException {
		if (logger.isTraceEnabled()){
			logger.trace(String.format("DaoImpl(utils=%s) - start", utils));
		}
		this.utils = utils;
		init();
		if (logger.isTraceEnabled()){
			logger.trace("DaoImpl() - end");
		}
	}

	// jdbc
	public DaoImpl(ConnectionUtilsJdbc utils) {
		if (logger.isTraceEnabled()){
			logger.trace(String.format("DaoImpl(utils=%s) - start", utils));
		}
		this.utilsJdbc = utils;
		if (logger.isTraceEnabled()){
			logger.trace("DaoImpl() - end");
		}
	}

	// default constructor
	public DaoImpl() {
		if (logger.isTraceEnabled()){
			logger.trace(String.format("DaoImpl() - start"));
		}
		if (logger.isTraceEnabled()){
			logger.trace("DaoImpl() - end");
		}
	}

	// connectionUtil setter
	public void setConnectionUtilsJdbc(ConnectionUtilsJdbc utils) {
		this.utilsJdbc = utils;
	}

	synchronized private void init() throws SQLException {
		if (logger.isTraceEnabled()){
			logger.trace("init() - start");
		}
		database = utils.getDatabase();
		if (logger.isTraceEnabled()){
			logger.trace("init() - end");
		}
	}

	synchronized public void close() throws SQLException {
		if (logger.isTraceEnabled()){
			logger.trace("close() - start");
		}
		if (database != null && !database.isClosed()) {
			utils.close(database);
		}
		if (logger.isTraceEnabled()){
			logger.trace("close() - end");
		}
	}

	/* (non-Javadoc)
	 * @see org.okinawaopenlabs.orientdb.client.Dao#getDeviceNameFromDatapathId(java.lang.String)
	 */
	@Override
	public String getDeviceNameFromDatapathId(Connection conn, String datapathId) throws SQLException {
		final String fname = "getDeviceNameFromDatapathId";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, datapathId=%s) - start", fname, conn, datapathId));
		}
		String ret = null;
		try {
//			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_DEVICENAME_FROM_DATAPATHID,
//                    new MapListHandler(), datapathId);
//			if (records.size() <= 0) {
//                throw new Exception(String.format(NOT_FOUND, datapathId));
//			}
//			ret = records.get(0).get("name").toString();
//			return ret;
			List<Map<String, Object>> infoMapList = this.getNodeInfoList(conn);

			for (Map<String, Object> infoMap : infoMapList) {
				if(infoMap.containsKey("datapathId")){
					if (datapathId.contentEquals((String) infoMap.get("datapathId"))) {
						return (String) infoMap.get("name");
					}
				}
			}

			// not found.
			return null;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public String getPortRidFromDeviceNamePortNumber(Connection conn, String deviceName, Integer portNumber) throws SQLException {
		final String fname = "getPortRidFromDeviceNamePortNumber";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(deviceName=%s, portNumber=%s) - start", fname, deviceName, portNumber));
		}
		String ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PORTRID_FROM_DEVICENAME_PORTNUMBER,
                    new MapListHandler(), deviceName, portNumber);
			if (records.size() <= 0) {
				throw new Exception(String.format(NOT_FOUND, deviceName + "," + portNumber));
			}
			ret = records.get(0).get("rid").toString();
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public List<Map<String, Map<String, Object>>> getDevicePortInfoSetFlowFromPortRid(Connection conn, String portRid) throws SQLException {
		final String fname = "getDevicePortInfoSetFlowFromPortRid";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(portRid=%s) - start", fname, portRid));
		}
		List<Map<String, Map<String, Object>>> ret = new ArrayList<Map<String, Map<String, Object>>>();
		try {
			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_PATCH_INFO_FROM_PATCHWIRING_PORTRID,
                    new MapListHandler(), portRid);
			if (records.size() <= 0) {
				throw new Exception(String.format(NOT_FOUND, portRid + ":wiring info"));
			}
			String inDeviceName = records.get(0).get("inDeviceName").toString();
			String inPortName = records.get(0).get("inPortName").toString();
			String outDeviceName = records.get(0).get("outDeviceName").toString();
			String outPortName = records.get(0).get("outPortName").toString();
			records = utilsJdbc.query(conn, SQL_GET_PATCH_INFO_FROM_PATCHWIRING_DEVICENAME_PORTNAME,
                    new MapListHandler("out", "in", "parent"), inDeviceName, inPortName, outDeviceName, outPortName);
			if (records.size() != 3 && records.size() != 1) {
				throw new Exception(UNEXPECTED_ERROR);
			}

			Iterator<Map<String, Object>> it = records.iterator();
			while (it.hasNext()) {
				Map<String, Object> record = it.next();
				Map<String, Object> inPortInfo = getPortInfoFromPortRid(conn, record.get("in").toString());
				Map<String, Object> outPortInfo = getPortInfoFromPortRid(conn, record.get("out").toString());
				Map<String, Object> parentInfo = getDeviceInfoFromDeviceRid(conn, record.get("parent").toString());

				Map<String, Map<String, Object>> tmp = new HashMap<String, Map<String, Object>>();
				tmp.put("in", inPortInfo);
				tmp.put("out", outPortInfo);
				tmp.put("parent", parentInfo);
				ret.add(tmp);
			}
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getCableLinksFromDeviceName(Connection conn, String deviceName) throws SQLException {
		final String fname = "getCableLinks";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		List<Map<String, Object>> maps = null;
		MapListHandler rhs = new MapListHandler(
				"inDeviceName",  "inPortName",  "inPortNumber",
				"outDeviceName", "outPortName", "outPortNumber",
				"@rid.asString()", "band", "used");
		try {
			maps = utilsJdbc.query(
					conn,
					SQL_GET_CABLE_LINKS,
					rhs,
					deviceName);
			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getPatchWiringsFromDeviceName(Connection conn, String deviceName) throws SQLException {
		final String fname = "getPatchWirings";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		List<Map<String, Object>> maps = null;
		MapListHandler rhs = new MapListHandler(
				"inDeviceName",  "inPortName",  "inPortNumber",
				"outDeviceName", "outPortName", "outPortNumber",
				"@rid.asString()", "band", "used");
		try {
			maps = utilsJdbc.query(
					conn,
					SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME,
					rhs,
					deviceName);
			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getLogicalLinksFromDeviceName(Connection conn, String deviceName)
			throws SQLException {
		final String fname = "getLogicalLinksFromDeviceName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		List<Map<String, Object>> maps = null;
		MapListHandler rhs = new MapListHandler(
				"inDeviceName",  "inPortName",
				"outDeviceName", "outPortName");
		try {
			maps = utilsJdbc.query(
					conn,
					SQL_GET_LOGICAL_LINKS_FROM_DEVICENAME,
					rhs,
					deviceName,deviceName);

			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getPatchWiringsFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "getPatchWiringsFromDeviceNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		List<Map<String, Object>> maps = null;
		try {
			MapListHandler rsh = new MapListHandler("in", "out", "parent", "inDeviceName", "inPortName", "outDeviceName", "outPortName", "sequence");
			maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME_PORTNAME, rsh, deviceName, portName);
			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getPatchWiringsFromParentRid(Connection conn, String parentRid) throws SQLException {
		final String fname = "getPatchWiringsFromParentRid";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, parentRid=%s) - start", fname, conn, parentRid));
		}
		List<Map<String, Object>> ret = null;
		try {
			ret = utilsJdbc.query(
					conn,
					SQL_GET_PATCH_WIRINGS_FROM_PARENTRID,
					new MapListHandler("in", "out", "parent", "inDeviceName", "inPortName", "outDeviceName", "outPortName", "sequence"),
					parentRid);
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public boolean isContainsPatchWiringFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "isContainsPatchWiringFromDeviceNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		boolean ret = true;
		try {
			MapListHandler rsh = new MapListHandler("in", "out", "parent", "inDeviceName", "inPortName", "outDeviceName", "outPortName");
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME_PORTNAME, rsh, deviceName, portName);
			if (maps == null || maps.isEmpty()) {
				ret = false;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public boolean isDeviceNameContainedIntoPatchWiring(Connection conn, String deviceName) throws SQLException {
		final String fname = "isDeviceNameContainedIntoPatchWiring";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		boolean ret = true;
		try {
			MapListHandler rsh = new MapListHandler("parent");
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_DEVICENAME, rsh, deviceName);
			if (maps == null || maps.isEmpty()) {
				ret = false;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public boolean isNodeRidContainedIntoPatchWiring(Connection conn, String nodeRid) throws SQLException {
		final String fname = "isNodeRidContainedIntoPatchWiring";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
		}
		boolean ret = true;
		try {
			MapListHandler rsh = new MapListHandler("parent");
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_NODERID, rsh, nodeRid);
			if (maps == null || maps.isEmpty()) {
				ret = false;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public boolean isPortRidContainedIntoPatchWiring(Connection conn, String portRid) throws SQLException {
		final String fname = "isPortRidContainedIntoPatchWiring";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, portRid=%s) - start", fname, conn, portRid));
		}
		boolean ret = true;
		try {
			MapListHandler rsh = new MapListHandler("parent");
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PATCH_WIRINGS_FROM_PORTRID, rsh, portRid, portRid);
			if (maps == null || maps.isEmpty()) {
				ret = false;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int deletePatchWiring(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "deletePatchWiring";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		int ret = 0;
		try {
			Object[] params = {deviceName, portName, deviceName, portName};
			ret = utilsJdbc.update(conn, SQL_DELETE_PATCH_WIRING_FROM_DEVICE_NAME_PORT_NAME, params);
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public Map<String, Object> getCableLinkFromInPortRid(Connection conn, String inPortRid) throws SQLException {
		final String fname = "getCableLinkFromInPortRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, inPortRid=%s) - start", fname, conn, inPortRid));
		}
		Map<String, Object> ret = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_CABLE_FROM_IN_PORTRID, new MapListHandler(), inPortRid);
			if (!maps.isEmpty()) {
				ret = maps.get(0);
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public Map<String, Object> getCableLinkFromOutPortRid(Connection conn, String outPortRid) throws SQLException {
		final String fname = "getCableLinkFromOutPortRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, outPortRid=%s) - start", fname, conn, outPortRid));
		}
		Map<String, Object> ret = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_CABLE_FROM_OUT_PORTRID, new MapListHandler(), outPortRid);
			if (!maps.isEmpty()) {
				ret = maps.get(0);
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public int createCableLink(Connection conn, String deviceName0, String portName0, String deviceName1, String portName1) throws SQLException {
		final String fname = "createCableLink";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName0=%s, portName0=%s, deviceName1=%s, portName1=%s) - start",
					fname, conn, deviceName0, portName0, deviceName1, portName1));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			String port0Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName0, portName0);
			if (StringUtils.isBlank(port0Rid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}
			String port1Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName1, portName1);
			if (StringUtils.isBlank(port1Rid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			String sql = SQL_INSERT_CABLE;
			sql = sql.replaceFirst("\\?", port0Rid);
			sql = sql.replaceFirst("\\?", port1Rid);
			int result = utilsJdbc.update(conn, sql);
			if (result != 1) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			sql = SQL_INSERT_CABLE;
			sql = sql.replaceFirst("\\?", port1Rid);
			sql = sql.replaceFirst("\\?", port0Rid);
			result = utilsJdbc.update(conn, sql);
			if (result != 1) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int deleteCableLink(Connection conn, String deviceName0, String portName0, String deviceName1, String portName1) throws SQLException {
		final String fname = "deleteCableLink";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName0=%s, portName0=%s, deviceName1=%s, portName1=%s) - start",
					fname, conn, deviceName0, portName0, deviceName1, portName1));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			boolean contain = false;
// TODO
//			contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName0, portName0);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_USED;
//				return ret;
//			}

// TODO
//			contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName1, portName1);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_USED;
//				return ret;
//			}

			String port0Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName0, portName0);
			if (StringUtils.isBlank(port0Rid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}
			String port1Rid = this.getPortRidFromDeviceNamePortName(conn, deviceName1, portName1);
			if (StringUtils.isBlank(port1Rid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

// TODO
//			contain = this.isPortRidContainedIntoPatchWiring(conn, port0Rid);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_USED;
//				return ret;
//			}

// TODO
//			contain = this.isPortRidContainedIntoPatchWiring(conn, port1Rid);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_USED;
//				return ret;
//			}

			String sql = SQL_DELETE_CABLE_FROM_ONE_PORTRID;
			sql = sql.replaceFirst("\\?", port0Rid);
			sql = sql.replaceFirst("\\?", port1Rid);
			int result = utilsJdbc.update(conn, sql);
			if (result == 0) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}
			if (result != 1) {
				ret = DB_RESPONSE_STATUS_FAIL;
				return ret;
			}

			sql = SQL_DELETE_CABLE_FROM_ONE_PORTRID;
			sql = sql.replaceFirst("\\?", port1Rid);
			sql = sql.replaceFirst("\\?", port0Rid);
			result = utilsJdbc.update(conn, sql);
			if (result == 0) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}
			if (result != 1) {
				ret = DB_RESPONSE_STATUS_FAIL;
				return ret;
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public void updateCableLinkUsedFromPortRid(Connection conn, String portRid, long newUsed) throws SQLException {
		final String fname = "updateCableLinkUsedFromPortRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, portRid=%s, newUsed=%s) - start", fname, conn, portRid, newUsed));
		}
		try {
			Object[] params = {newUsed, portRid, portRid};
			int result = utilsJdbc.update(conn, SQL_UPDATE_CALBE_LINK_USED_VALUE_FROM_PORT_RID, params);
			if (result != 4 && result != 2) {
				throw new SQLException(String.format(INVALID_NUMBER_OF, "link(represent cable)"));
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		}
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s() - end", fname));
		}
	}

	@Override
	public List<Map<String, Object>> getShortestPath(Connection conn, String ridA, String ridZ) throws SQLException {
		final String fname = "getShortestPath";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, ridA=%s, ridZ=%s) - start", fname, conn, ridA, ridZ));
		}
		List<Map<String, Object>> ret = null;
		try {
			String sql = SQL_GET_DIJKSTRA_PATH_FLATTEN;
			sql = sql.replaceFirst("\\?", ridA);
			sql = sql.replaceFirst("\\?", ridZ);
			MapListHandler rsh = new MapListHandler("rid", "name", "number", "node_name", "class");
			ret = utilsJdbc.query(conn, sql, rsh);

			for (Map<String, Object> current : ret) {
				String deviceName = "";
				if (StringUtils.equals((String)current.get("class"), "node")) {
					deviceName = (String)current.get("name");
				} else {
					deviceName = (String)current.get("node_name");
				}
				Map<String, Object> nodeMap = this.getNodeInfoFromDeviceName(conn, deviceName);
				current.put("type", (String)nodeMap.get("type"));
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public int insertPatchWiring(Connection conn, String ofpRid, String in, String out, String inDeviceName, String inPortName, String outDeviceName, String outPortName, int sequence) throws SQLException {
		final String fname = "insertPatchWiring";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, ofpRid=%s, in=%s, out=%s, inDeviceName=%s, inPortName=%s, outDeviceName=%s, outPortName=%s, sequence=%s) - start",
					fname, conn, ofpRid, in, out, inDeviceName, inPortName, outDeviceName, outPortName, sequence));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Object[] forwardParams = {in, out, ofpRid, inDeviceName, inPortName, outDeviceName, outPortName, sequence};
			int result = utilsJdbc.update(conn, SQL_INSERT_PATCH_WIRING_2, forwardParams);
			if (result != 1) {
				throw new SQLException(String.format(PATCH_INSERT_FAILD, inDeviceName, inPortName, outDeviceName, outPortName, ofpRid, in, out));
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public int insertLogicalLink(Connection conn, String in_node_id, String in_node_name, String in_port_id,
		String in_port_name, String out_node_id, String out_node_name, String out_port_id, String out_port_name,
		Long nw_instance_id, String nw_instance_type) throws SQLException {
		final String fname = "insertLogicalLink";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, in_node_id=%s, in_node_name=%s, in_port_id=%s, in_port_name=%s, out_node_id=%s, out_node_name=%s, out_port_id=%s, out_port_name=%s, nw_instance_id=%s, nw_instance_type=%s) - start",
					fname, conn, in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Object[] forwardParams = {in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type};
			int result = utilsJdbc.update(conn, SQL_INSERT_LOGICAL_LINK, forwardParams);
			if (result != 1) {
				throw new SQLException(String.format(PATCH_INSERT_FAILD, in_node_id, in_node_name, in_port_id, in_port_name, out_node_id, out_node_name, out_port_id, out_port_name, nw_instance_id, nw_instance_type));
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public Long getNwInstanceId(Connection conn) throws SQLException {
		final String fname = "getNwInstanceId";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		List<String> ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_MAX_NW_INSTANCE_ID, new MapListHandler());
			Long maxNwInstanceId = MIN_NETWORK_INSTANCE_ID;
			if (records.size() > 0) {
				maxNwInstanceId = (Long)Long.parseLong((records.get(0).get("maxNwInstanceId").toString())) + 1;
				if (maxNwInstanceId > MAX_NETWORK_INSTANCE_ID) {
					records = utilsJdbc.query(conn, SQL_GET_NW_INSTANCE_ID, new MapListHandler());
					Long count = MIN_NETWORK_INSTANCE_ID;
					for(Map<String, Object> record : records) {
						Long curId = (Long)record.get("maxNwInstanceId");
						if (curId != count) {
							// found.
							return count;
						}
						count++;
					}

					// not found.
					return -1L;
				}
			}

			return maxNwInstanceId;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public Map<String, Object> getNodeInfoFromDeviceName(Connection conn, String deviceName) throws SQLException {
		final String fname = "getNodeInfoFromDeviceName";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		Map<String, Object> map = null;
		try {
			List<Map<String, Object>> infoMapList = this.getNodeInfoList(conn);

			for (Map<String, Object> infoMap : infoMapList) {
				if (deviceName.contentEquals((String) infoMap.get("name"))) {
					return infoMap;
				}
			}

			// not found.
			return null;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, map));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getNodeInfoList(Connection conn) throws SQLException {
		final String fname = "getNodeInfoList";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		List<Map<String, Object>> maps = null;
		try {
			maps = utilsJdbc.query(conn, SQL_GET_NODE_INFO_LIST, new MapListHandler());

			List<Map<String, Object>> ofsMaps = utilsJdbc.query(conn, SQL_GET_OFS_INFO_LIST, new MapListHandler());
			for (Map<String, Object> infoMap : maps) {
				for (Map<String, Object> ofsMap : ofsMaps) {
					String rid = (String)infoMap.get("rid");
					if(rid.equals((String) ofsMap.get("node_id"))){
						infoMap.put("sw_instance_id", ofsMap.get("rid"));
						infoMap.put("datapathId", (String) ofsMap.get("datapathId"));
						infoMap.put("ip", (String) ofsMap.get("ip"));
						infoMap.put("port", (Integer) ofsMap.get("port"));
						Integer port = (Integer) ofsMap.get("port");
						infoMap.put("ofcIp", (String) ofsMap.get("ip") + ":" + port.toString());
					}
				}
			}


			return maps;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public Map<String, Object> getNodeInfoFromDeviceRid(Connection conn, String nodeRid) throws SQLException {
		final String fname = "getDeviceInfoFromDeviceRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
		}
		Map<String, Object> map = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NODE_INFO_FROM_DEVICE_RID, new MapListHandler(), nodeRid);
			if (!maps.isEmpty()) {
				map = maps.get(0);
			}
			return map;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, map));
			}
		}
	}

	@Override
	public String getNodeRidFromDeviceName(Connection conn, String deviceName) throws SQLException {
		final String fname = "getNodeRidFromDeviceName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		String ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					SQL_GET_NODE_RID_FROM_DEVICENAME,
					new MapListHandler("rid"),
					deviceName);
			if (records != null && !records.isEmpty() && records.get(0) != null) {
				ret = (String) records.get(0).get("rid");
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int createNodeInfo(Connection conn, String deviceName, String deviceType, String location, String tenant, String datapathId, String ofcIp) throws SQLException {
		final String fname = "createNodeInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s, deviceType=%s, location=%s, tenant=%s, datapathId=%s, ofcIp=%s) - start", fname, conn, deviceName, deviceType, location, tenant, datapathId, ofcIp));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Map<String, Object> current = this.getNodeInfoFromDeviceName(conn, deviceName);
			if (current != null) {
				return DB_RESPONSE_STATUS_EXIST;
			}

			Object[] params = {deviceName, location, deviceType, tenant};
			int nRecords = utilsJdbc.update(conn, SQL_INSERT_NODE_INFO, params);
			if (nRecords == 0) {
				return DB_RESPONSE_STATUS_EXIST;
			}

			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
				String nodeRid = this.getNodeRidFromDeviceName(conn, deviceName);
				if (StringUtils.isBlank(nodeRid)) {
					return DB_RESPONSE_STATUS_NOT_FOUND;
				}

				ret = this.createOfsInfo(conn, nodeRid, datapathId, ofcIp);
			}


			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	public String getResourceRidFromNodeRid(Connection conn, String nodeRid, String deviceType) throws SQLException {
		final String fname = "getResourceRidFromNodeRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, nodeRid=%s, deviceType=%s) - start", fname, conn, nodeRid, deviceType));
		}
		String ret = null;

		try {
			String sql = null;
			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
				sql = SQL_GET_STSTEM_RESOURCE_RID_FROM_NODE_RID;
			} else {
				sql = SQL_GET_RENT_RESOURCE_RID_FROM_NODE_RID;
			}

			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					sql,
					new MapListHandler("rid"),
					nodeRid);
			if (records != null && !records.isEmpty() && records.get(0) != null) {
				ret = (String) records.get(0).get("rid");
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	public int createResourceInfo(Connection conn, String deviceName, String deviceType, String datapathId, String tenant, String ofcIp) throws SQLException {
		final String fname = "createResourceInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s, deviceType=%s, tenant=%s, datapathId=%s, ofcIp=%s) - start", fname, conn, deviceName, deviceType, tenant, datapathId, ofcIp));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			String nodeRid = this.getNodeRidFromDeviceName(conn, deviceName);
			if (StringUtils.isBlank(nodeRid)) {
				return DB_RESPONSE_STATUS_NOT_FOUND;
			}

			String sql = null;
			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
				sql = SQL_INSERT_SYSTEM_RESOURCE_INFO;
			} else {
				sql = SQL_INSERT_RENT_RESOURCE_INFO;
			}
//			Object[] params = {nodeRid, deviceType, tenant};
//			int nRecords = utilsJdbc.update(conn, sql, params);
			sql = sql.replaceFirst("\\?", nodeRid);
			sql = sql.replaceFirst("\\?", deviceType);
			sql = sql.replaceFirst("\\?", tenant);
			int nRecords = utilsJdbc.update(conn, sql);
			if (nRecords == 0) {
				return DB_RESPONSE_STATUS_EXIST;
			}

			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, deviceType)) {
				String resourceRid = this.getResourceRidFromNodeRid(conn, nodeRid, deviceType);
				ret = this.createOfsInfo(conn, resourceRid, datapathId, ofcIp);
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	public int createOfsInfo(Connection conn, String nodeRid, String datapathId, String ofcIp) throws SQLException {
		final String fname = "createOfsInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, nodeRid=%s, datapathId=%s, ofcIp=%s) - start", fname, conn, nodeRid, datapathId, ofcIp));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			if (!StringUtils.isBlank(datapathId) && !StringUtils.isBlank(ofcIp)) {
				String ofcRid = this.getOfcRid(conn, ofcIp);
				if (StringUtils.isBlank(ofcRid)) {
					return DB_RESPONSE_STATUS_NOT_FOUND;
				}
//				Object[] params = {datapathId, nodeRid, ofcRid};
//				int nRecords = utilsJdbc.update(conn, SQL_INSERT_OFS_INFO, params);
				String sql = SQL_INSERT_OFS_INFO;
				sql = sql.replaceFirst("\\?", datapathId);
				sql = sql.replaceFirst("\\?", nodeRid);
				sql = sql.replaceFirst("\\?", ofcRid);
				int nRecords = utilsJdbc.update(conn, sql);

				if (nRecords == 0) {
					return DB_RESPONSE_STATUS_EXIST;
				}

				String ofsRid = this.getOfsRid(conn, nodeRid);
				if (StringUtils.isBlank(ofsRid)) {
					return DB_RESPONSE_STATUS_NOT_FOUND;
				}

				sql = SQL_UPDATE_NODE_SW_INSTANCE_INFO_FROM_RID;
				sql = sql.replaceFirst("\\?", "OpenFlowSwitch");
				sql = sql.replaceFirst("\\?", ofsRid);
				sql = sql.replaceFirst("\\?", nodeRid);
				int result = utilsJdbc.update(conn, sql);
				if (result == 0) {
					ret = DB_RESPONSE_STATUS_EXIST;
					return ret;
				}
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	public String getOfsRid(Connection conn, String nodeRid) throws SQLException {
        final String fname = "getOfsRid";
        if (logger.isTraceEnabled()) {
                logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
        }

        String ret = null;
        try {
                List<Map<String, Object>> records = utilsJdbc.query(
                                conn,
                                SQL_GET_OFS_RID_FROM_NODE_ID,
                                new MapListHandler("rid"),
                                nodeRid);
                if (records != null && !records.isEmpty() && records.get(0) != null) {
                        ret = (String) records.get(0).get("rid");
                }
                return ret;
        } catch (Exception e) {
                throw new SQLException(e.getMessage());
        } finally {
                if (logger.isTraceEnabled()) {
                        logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                }
        }
}
    @Override
    public String getOfcRid(Connection conn, String ofcIp) throws SQLException {
            final String fname = "getOfcRid";
            if (logger.isTraceEnabled()) {
                    logger.trace(String.format("%s(conn=%s, ofcIp=%s) - start", fname, conn, ofcIp));
            }

            String ret = null;
            try {
                    String[] ofc = ofcIp.split(":", 0);
                    String ip = ofc[0];
                    if(!this.isNum(ofc[1])) {
                            return null;
                    }
                    int port = Integer.parseInt(ofc[1]);

                    List<Map<String, Object>> records = utilsJdbc.query(
                                    conn,
                                    SQL_GET_OFC_RID_FROM_IP_AND_PORT,
                                    new MapListHandler("rid"),
                                    ip, port);
                    if (records != null && !records.isEmpty() && records.get(0) != null) {
                            ret = (String) records.get(0).get("rid");
                    }
                    return ret;
            } catch (Exception e) {
                    throw new SQLException(e.getMessage());
            } finally {
                    if (logger.isTraceEnabled()) {
                            logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                    }
            }
    }

    public boolean isNum(String number) {
        try {
            Integer.parseInt(number);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }


	@Override
	public int updateNodeInfo(Connection conn, String keyDeviceName, String deviceName, String location,String tenant, String datapathId, String ofcIp, String type) throws SQLException {
		final String fname = "updateNodeInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, keyDeviceName=%s, newDeviceName=%s, datapathId=%s, ofcIp=%s) - start", fname, conn, keyDeviceName, deviceName, datapathId, ofcIp));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Map<String, Object> current = this.getNodeInfoFromDeviceName(conn, keyDeviceName);
			if (current == null) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			String nodeRid = (String)current.get("rid");
			if (StringUtils.isBlank(deviceName)) {
				deviceName = (String)current.get("name");
			}

			if (StringUtils.isBlank(type)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			if(!type.equals((String)current.get("type"))){
				ret = DB_RESPONSE_STATUS_INVALID_ERR;
				return ret;
			}

			if (StringUtils.isBlank(location)) {
				location = (String)current.get("location");
			}
			if (StringUtils.isBlank(tenant)) {
				tenant = (String)current.get("tenant");
			}

			if(!ArrayUtils.contains(LEGACY_DEVICE_TYPES, type))
			{
				if (StringUtils.isBlank(datapathId)) {
					datapathId = (String)current.get("datapathId");
				}
			}

			String sql = SQL_UPDATE_NODE_INFO_FROM_RID;
			sql = sql.replaceFirst("\\?", deviceName);
			sql = sql.replaceFirst("\\?", location);
			sql = sql.replaceFirst("\\?", tenant);
			sql = sql.replaceFirst("\\?", nodeRid);
			int result = utilsJdbc.update(conn, sql);
			if (result == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, (String)current.get("type"))) {
				if (StringUtils.isBlank(ofcIp)) {
					ofcIp = (String)current.get("ofcIp");
				}
				String ofcRid = this.getOfcRid(conn, ofcIp);
				if (StringUtils.isBlank(ofcRid)) {
					ret = DB_RESPONSE_STATUS_NOT_FOUND;
					return ret;
				}


				sql = SQL_UPDATE_OFS_INFO_FROM_RID;
				sql = sql.replaceFirst("\\?", datapathId);
				sql = sql.replaceFirst("\\?", ofcRid);
				sql = sql.replaceFirst("\\?", (String)current.get("sw_instance_id"));
				result = utilsJdbc.update(conn, sql);
				if (result == 0) {
					ret = DB_RESPONSE_STATUS_EXIST;
					return ret;
				}
			}

//TODO
//			Object[] updDevNamePara = {deviceName, keyDeviceName};
//			utilsJdbc.update(conn, SQL_UPDATE_PORT_DEVICENAME, updDevNamePara);
//			utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_INDEVICENAME,  updDevNamePara);
//			utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_OUTDEVICENAME, updDevNamePara);

			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int deleteNodeInfo(Connection conn, String deviceName) throws SQLException {
		final String fname = "deleteNodeInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, conn, deviceName));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Map<String, Object> deviceMap = this.getNodeInfoFromDeviceName(conn, deviceName);
			if (deviceMap == null) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			String nodeRid = (String)deviceMap.get("rid");

// TODO
//			boolean contain = this.isDeviceNameContainedIntoPatchWiring(conn, deviceName);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_FORBIDDEN;
//				return ret;
//			}

// TODO
//			contain = this.isNodeRidContainedIntoPatchWiring(conn, nodeRid);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_FORBIDDEN;
//				return ret;
//			}

			Object[] params = {deviceName};
			utilsJdbc.update(
					conn,
					SQL_DELETE_PORT_FROM_DEVICENAME,
					params);

			if (ArrayUtils.contains(SYSTEM_RESOURCE_TYPES, (String)deviceMap.get("type"))) {
				String sql = SQL_DELETE_OFS_FROM_RID;
				sql = sql.replaceFirst("\\?", (String)deviceMap.get("sw_instance_id"));
				int nRecord = utilsJdbc.update(conn, sql);
				if (nRecord != 1) {
					ret = DB_RESPONSE_STATUS_FAIL;
					return ret;
				}
			}

			String sql = SQL_DELETE_NODE_FROM_NODERID;
			sql = sql.replaceFirst("\\?", nodeRid);
			int nRecord = utilsJdbc.update(conn, sql);
			if (nRecord != 1) {
				ret = DB_RESPONSE_STATUS_FAIL;
				return ret;
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int createPortInfo(Connection conn, String portName, Integer portNumber, Integer band, String deviceName, String network) throws SQLException {
		final String fname = "createPortInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, portName=%s, portNumber=%s, band=%s, deviceName=%s) - start", fname, conn, portName, portNumber, band, deviceName));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			/* PHASE 1: Insert port info */
			Map<String, Object> devMap = this.getNodeInfoFromDeviceName(conn, deviceName);

			if (devMap == null) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			if (StringUtils.equals((String)devMap.get("type"), NODE_TYPE_SERVER)) {
				portNumber = null;
			}

			Map<String, Object> current = this.getPortInfoFromPortName(conn, deviceName, portName);
			if (current != null) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}
			if (network == null) {
				network = (String)current.get("");
			}
			Object[] params = {portName, portNumber, band, deviceName, network};
			int nRecords = utilsJdbc.update(conn, SQL_INSERT_PORT_INFO, params);
			if (nRecords == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			/* PHASE 2: Insert Bus-link */
			String devRid  = (String) devMap.get("rid");
			String devType = (String) devMap.get("type");
			String portRid = this.getPortRidFromDeviceNamePortName(conn, deviceName, portName);
			if (StringUtils.isBlank(portRid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			/* Here, decide Used default value of bus of Node. */
			Long used = USED_BLOCKING_VALUE;
			if (ArrayUtils.contains(BIG_USED_BUS_TYPES, devType)) {
				used = BIG_USED_BUS_VALUE;
			} else if (StringUtils.equals(devType, NODE_TYPE_LEAF)) {
				used = 0L;
			}

			String sql = SQL_INSERT_BUS;
			sql = sql.replaceFirst("\\?", portRid);
			sql = sql.replaceFirst("\\?", devRid);
			sql = sql.replaceFirst("\\?", used.toString());
			nRecords = utilsJdbc.update(conn, sql);
			if (nRecords == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			sql = SQL_INSERT_BUS;
			sql = sql.replaceFirst("\\?", devRid);
			sql = sql.replaceFirst("\\?", portRid);
			sql = sql.replaceFirst("\\?", used.toString());
			nRecords = utilsJdbc.update(conn, sql);
			if (nRecords == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int updatePortInfo(Connection conn, String keyPortName, String keyDeviceName, String portName, Integer portNumber, Integer band, String network) throws SQLException {
		final String fname = "updatePortInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, keyPortName=%s, keyDeviceName=%s, portName=%s, portNumber=%s, band=%s) - start", fname, conn, keyPortName, keyDeviceName, portName, portNumber, band));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Map<String, Object> current = this.getPortInfoFromPortName(conn, keyDeviceName, keyPortName);
			if (current == null) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				if (logger.isTraceEnabled()){
					logger.trace(String.format("%s(ret=%s) - end", fname, ret));
				}
				return ret;
			}

			String portRid = (String)current.get("rid");
			if (StringUtils.isBlank(portName)) {
				portName = (String)current.get("name");
			}
			if (portNumber == null) {
				portNumber = (Integer)current.get("portNumber");
			}
			if (band == null) {
				band = (Integer)current.get("band");
			}
			if (network == null) {
				network = (String)current.get("network");
			}

			Object[] params = {portName, portNumber, band, network, portRid};
			int result = utilsJdbc.update(conn, SQL_UPDATE_PORT_INFO_FROM_RID, params);
			if (result == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				if (logger.isTraceEnabled()) {
					logger.trace(String.format("%s(ret=%s) - end", fname, ret));
				}
				return ret;
			}

// TODO
//			Object[] updPortNamePara = {portName, keyPortName, keyDeviceName};
//			utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_INPORTNAME, updPortNamePara);
//			utilsJdbc.update(conn, SQL_UPDATE_PATCH_WIRING_OUTPORTNAME, updPortNamePara);
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public int deletePortInfo(Connection conn, String portName, String deviceName) throws SQLException {
		final String fname = "deletePortInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, portName=%s, deviceName=%s) - start", fname, conn, portName, deviceName));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			String portRid = this.getPortRidFromDeviceNamePortName(conn, deviceName, portName);
			if (StringUtils.isBlank(portRid)) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

// TODO
//			boolean contain = this.isContainsPatchWiringFromDeviceNamePortName(conn, deviceName, portName);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_FORBIDDEN;
//				return ret;
//			}

// TODO
//			contain = this.isPortRidContainedIntoPatchWiring(conn, portRid);
//			if (contain) {
//				ret = DB_RESPONSE_STATUS_FORBIDDEN;
//				return ret;
//			}

			String sql = SQL_DELETE_PORT_FROM_PORTRID;
			sql = sql.replaceFirst("\\?", portRid);
			int nRecord = utilsJdbc.update(conn, sql);
			if (nRecord != 1) {
				ret = DB_RESPONSE_STATUS_FAIL;
				return ret;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public Map<String, Object> getPortInfoFromPortName(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "getPortInfoFromPortName";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		Map<String, Object> map = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_PORT_INFO_FROM_PORT_NAME, new MapListHandler(), portName, deviceName);
			if (!maps.isEmpty()) {
				map = maps.get(0);
			}
			return map;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, map));
			}
		}
	}

	@Override
	public Map<String, Object> getPortInfoFromPortRid(Connection conn, String rid) throws SQLException {
		final String fname = "getPortInfoFromPortRid";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(rid=%s) - start", fname, rid));
		}
		Map<String, Object> ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					SQL_GET_PORT_INFO_FROM_PORTRID,
                    new MapListHandler(),
                    rid);
			if (records != null && !records.isEmpty()) {
				ret = records.get(0);
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("getPortInfo(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public String getPortRidFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "getPortRidFromDeviceNamePortName";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, deviceName, portName));
		}
		String ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					SQL_GET_PORT_RID_FROM_DEVICENAME_PORTNAME,
					new MapListHandler("rid"),
					portName, deviceName);
			if (records != null && !records.isEmpty() && records.get(0) != null) {
				ret = (String) records.get(0).get("rid");
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getPortInfoListFromDeviceName(Connection conn, String deviceName) throws SQLException {
		final String fname = "getPortInfoListFromDeviceName";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s) - start", fname, deviceName));
		}
		List<Map<String, Object>> ret = null;
		try {
			ret = utilsJdbc.query(
					conn,
					SQL_GET_PORT_INFO_FROM_DEVICENAME,
					new MapListHandler(),
					deviceName);
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public Map<String, Object> getNeighborPortFromPortRid(Connection conn, String portRid) throws SQLException {
		final String fname = "getNeighborPortFromPortRid";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, portRid=%s) - start", fname, portRid));
		}
		Map<String, Object> ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					SQL_GET_NEIGHBOR_PORT_INFO_FROM_PORT_RID,
					new MapListHandler(),
					portRid);
			if (records != null && !records.isEmpty()) {
				ret = records.get(0);
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public Map<String, Object> getDeviceInfoFromDeviceRid(Connection conn, String rid) throws SQLException {
		final String fname = "getDeviceInfoFromDeviceRid";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(rid=%s) - start", fname, rid));
		}
		Map<String, Object> record = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_DEVICE_INFO_FROM_DEVICERID,
                    new MapListHandler(), rid);
			if (records.size() <= 0) {
				String msg = String.format(NOT_FOUND, "node(" + rid + ")");
                throw new SQLException(msg);
			}
			record = records.get(0);
			return record;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, record));
			}
		}
	}

	static synchronized public String getInternalMacFromDeviceNameInPortSrcMacDstMac(ConnectionUtilsJdbc utilsJdbc, Connection conn, String deviceName, String inPort, String srcMac, String dstMac) throws SQLException {
		final String fname = "getInternalMacFromDeviceNameInPortSrcMacDstMac";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, inPort=%s, srcMac=%s, dstMac=%s) - start", fname, conn, deviceName, inPort, srcMac, dstMac));
		}
		String ret = null;
		try {
			DbAccessFlag.lock();

			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_INTERNALMAC_FROM_SRCMAC_DSTMAC_INPORT_DEVICENAME,
                    new MapListHandler(), deviceName, inPort, srcMac, dstMac);
			if (records.size() > 0) {
                ret = OFPMUtils.longToMacAddress(Long.parseLong((records.get(0).get("internalMac").toString())));
			} else {
				records = utilsJdbc.query(conn, SQL_GET_MAX_INTERNALMAC, new MapListHandler());
				Long newInternalMac = 1L;
				if (records.size() > 0) {
					newInternalMac = Long.parseLong((records.get(0).get("maxInternalMac").toString())) + 1L;
				}
				Object[] params = {deviceName, inPort, srcMac, dstMac, newInternalMac};
				int rows = utilsJdbc.update(conn, SQL_INSERT_INTERNALMAC, params);
				if (rows == 0) {
					// TODO exist error
				}
				ret = OFPMUtils.longToMacAddress(newInternalMac);
			}
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
			DbAccessFlag.unlock();
		}
	}

	@Override
	public List<String> getInternalMacListFromDeviceNameInPort(Connection conn, String deviceName, String inPort) throws SQLException {
		final String fname = "getInternalMacListFromDeviceNameInPort";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, inPort=%s) - start", fname, conn, deviceName, inPort));
		}
		List<String> ret = null;
		try {
			ret = new ArrayList<String>();
			List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_INTERNALMAC_LIST_FROM_DEVICENAME_INPORT, new MapListHandler("internalMac"), deviceName, inPort);
			for (int i = 0; i < records.size(); i++) {
				Map<String, Object> record = records.get(i);
				Long internalMac = Long.parseLong(record.get("internalMac").toString());
				ret.add(OFPMUtils.longToMacAddress(internalMac));
			}
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public int deleteInternalMac(Connection conn, String deviceName, int inPort) throws SQLException {
		final String fname = "deleteInternalMac";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%d) - start", fname, conn, deviceName, inPort));
		}
		int ret = 0;
		try {
			Object[] params = {deviceName, inPort};
			ret = utilsJdbc.update(conn, SQL_DELETE_INTERNALMAC_FROM_DEVICE_NAME_PORT_NAME, params);
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public List<Map<String, Object>> getInternalMacInfoListFromDeviceNameInPort(Connection conn, String deviceName, Integer portNumber) throws SQLException {
		final String fname = "getInternalMacInfoListFromDeviceNameInPort";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, portNumber=%s) - start", fname, conn, deviceName, portNumber));
		}
		List<Map<String, Object>> ret = null;
		try {
			ret = utilsJdbc.query(
					conn,
					SQL_GET_INTERNALMAC_INFO_LIST_FROM_DEVICENAME_PORTNUMBER,
					new MapListHandler("srcMac", "dstMac", "internalMac"),
					deviceName,
					portNumber);
			if (ret == null) {
				return new ArrayList<Map<String, Object>>();
			}
			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", ret));
			}
		}
	}

	@Override
	public String getPortBandFromDeviceNamePortName(Connection conn, String deviceName, String portName) throws SQLException {
		final String fname = "getPortBandFromDeviceNamePortName";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, deviceName=%s, portName=%s) - start", fname, deviceName, portName));
		}
		String ret = null;
		try {
			List<Map<String, Object>> records = utilsJdbc.query(
					conn,
					SQL_GET_PORT_BAND_FROM_DEVICENAME_PORTNAME,
					new MapListHandler("band"),
					portName, deviceName);
			if (records != null && !records.isEmpty() && records.get(0) != null) {
				ret = (String) Integer.toString((Integer)records.get(0).get("band"));
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getOfcInfoList(Connection conn) throws SQLException {
		final String fname = "getOfcInfoList";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		List<Map<String, Object>> maps = null;
//		List<Map<String, Object>> rent_resource_maps = null;
		try {
			maps = utilsJdbc.query(conn, SQL_GET_OFC_INFO_LIST, new MapListHandler());
			return maps;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public Map<String, Object> getOfcInfo(Connection conn, String ofcIpPort) throws SQLException {
		final String fname = "getOfcInfo";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
		}

		String[] ofc = ofcIpPort.split(":", 0);
		String ip = ofc[0];
		int port = Integer.parseInt(ofc[1]);

		List<Map<String, Object>> infoMapList = getOfcInfoList(conn);

		for (Map<String, Object> infoMap : infoMapList) {

			if (ip.equals((String) infoMap.get("ip")) && (port == (Integer) infoMap.get("port"))) {
				return infoMap;
			}
		}
		try {
			// not found
			return null;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - end", fname, conn, ofcIpPort));
			}
		}
	}

    @Override
    public Map<String, Object> getOfcRidInfo(Connection conn, String ofcIpPort) throws SQLException {
            final String fname = "getOfcRidInfo";
/*              if (logger.isTraceEnabled()){
                    logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
            }
*/
            Map<String, Object> ret = null;
//          List<Map<String, Object>> rent_resource_maps = null;
            if (logger.isTraceEnabled()){
                    logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
            }

            String[] ofc = ofcIpPort.split(":", 0);
            String ip = ofc[0];
            int port = Integer.parseInt(ofc[1]);
            try {
                    List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_OFC_INFO_FROM_IP_PORT, new MapListHandler(), ip, port);
                    if (records != null && !records.isEmpty()) {
                            ret = records.get(0);
                    }
                    return ret;
            } catch (Exception e){
                    throw new SQLException(e.getMessage());
            } finally {
                    if (logger.isTraceEnabled()){
                            logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                    }
            }
    }

	@Override
	public int createOfcInfo(Connection conn, String ip, Integer port) throws SQLException {

		final String fname = "createOfcInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, ofcIp=%s, ofcPort=%d) - start", fname, conn, ip, port));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			String ofcIpPort = ip + ":" + port.toString();
			Map<String, Object> current = this.getOfcRidInfo(conn, ofcIpPort);
			if (current != null) {
				return DB_RESPONSE_STATUS_EXIST;
			}

			Object[] params = {ip, port};
			int nRecords = utilsJdbc.update(conn, SQL_INSERT_OFC_INFO, params);
			if (nRecords == 0) {
				return DB_RESPONSE_STATUS_EXIST;
			}

			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}

	}

	@Override
	public int updateOfcInfo(Connection conn, String ofcIpPort, String ip, Integer port) throws SQLException {
		final String fname = "updateOfcInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Map<String, Object> current = this.getOfcRidInfo(conn, ofcIpPort);
			if (current == null) {
				ret = DB_RESPONSE_STATUS_NOT_FOUND;
				return ret;
			}

			String OfcRid = (String)current.get("rid");
			if (StringUtils.isBlank(ip)) {
				ip = (String)current.get("ip");
			}
			if (port == null) {
				port = (Integer)current.get("port");
			}

			Object[] params = {ip, port, OfcRid};
			int result = utilsJdbc.update(conn, SQL_UPDATE_OFC_INFO, params);

			if (result == 0) {
				ret = DB_RESPONSE_STATUS_EXIST;
				return ret;
			}

			return ret;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int deleteOfcInfo(Connection conn, String ofcIpPort) throws SQLException {
		final String fname = "deleteOfcInfo";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ofcIpPort));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			String[] ofc = ofcIpPort.split(":", 0);
			String ip = ofc[0];
			int port = Integer.parseInt(ofc[1]);

			Object[] params = {ip, port};
			int nRecord = utilsJdbc.update(conn, SQL_DELETE_OFC_FROM_IP_AND_PORT, params);
			if (nRecord != 1) {
				ret = DB_RESPONSE_STATUS_FAIL;
				return ret;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}

	}

	@Override
	public int insertRoute(Connection conn, Integer sequence_num, String logical_link_id, String node_id, String node_name, String in_port_id, String in_port_name, Integer in_port_number, String out_port_id, String out_port_name, Integer out_port_number) throws SQLException {
		final String fname = "insertRoute";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, sequence_num=%s, logical_link_id=%s, node_id=%s, node_name=%s, in_port_id=%s, in_port_name=%s, in_port_number=%s, out_port_id=%s, out_port_name=%s, out_port_number=%s) - start",
					fname, conn, sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number));
		}
		int ret = DB_RESPONSE_STATUS_OK;
		try {
			Object[] params = {sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number};
			int result = utilsJdbc.update(conn, SQL_INSERT_ROUTE_INFO, params);
			if (result != 1) {
				throw new SQLException(String.format(ROUTE_INSERT_FAILD,
						sequence_num, logical_link_id, node_id, node_name, in_port_id, in_port_name, in_port_number, out_port_id, out_port_name, out_port_number));
			}
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public Map<String, Object> getLogicalLinkFromNodeNamePortName(Connection conn, String node_name, String port_name) throws SQLException {
		final String fname = "getLogicalLinkFromNodeNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, node_name=%s, port_name=%s) - start", fname, conn, node_name, port_name));
		}
		Map<String, Object> map = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_LOGICAL_LINK_FROM_NODE_NAME_PORT_NAME, new MapListHandler(), node_name, port_name, node_name, port_name);
			if (!maps.isEmpty()) {
				map = maps.get(0);
			}
			return map;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, map));
			}
		}
	}

	@Override
	public Map<String, Object> getLogicalLinkFromRid(Connection conn, String logicalLinkRid) throws SQLException {
		final String fname = "getLogicalLinkFromRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, logicalLinkRid=%s) - start", fname, conn, logicalLinkRid));
		}
		Map<String, Object> map = null;
		try {
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_LOGICAL_LINK_FROM_RID, new MapListHandler(), logicalLinkRid);
			if (!maps.isEmpty()) {
				map = maps.get(0);
			}
			return map;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, map));
			}
		}
	}

	@Override
	public boolean isContainsLogicalLinkFromDeviceNamePortName(Connection conn, String deviceName, String portName)
			throws SQLException {
		final String fname = "isContainsLogicalLinkFromDeviceNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		boolean ret = true;
		try {
			Map<String, Object> map = getLogicalLinkFromNodeNamePortName(conn, deviceName, portName);
			if (map == null || map.isEmpty()) {
				ret = false;
			}
			return ret;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getRouteFromLogicalLinkId(Connection conn, String logical_link_id) throws SQLException {
		final String fname = "getRouteFromLogicalLinkId";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, logical_link_id=%s) - start", fname, conn, logical_link_id));
		}
		List<Map<String, Object>> maps = null;
		try {
			MapListHandler rhs = new MapListHandler(
					"@rid.asString()", "sequence_num", "logical_link_id",
					"node_id", "node_name", "in_port_id", "in_port_name", "out_port_id", "out_port_name");
			maps = utilsJdbc.query(conn, SQL_GET_ROUTE_FROM_LOGICAL_LINK_ID, rhs, logical_link_id);
			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getRouteFromNodeRid(Connection conn,  String nodeRid) throws SQLException {
		final String fname = "getRouteFromNodeRid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, nodeRid=%s) - start", fname, conn, nodeRid));
		}
		List<Map<String, Object>> maps = null;
		try {
			MapListHandler rhs = new MapListHandler(
					"@rid.asString()", "sequence_num", "logical_link_id",
					"node_id", "node_name", "in_port_id", "in_port_name", "in_port_number",
					"out_port_id", "out_port_name", "out_port_number");
			maps = utilsJdbc.query(conn, SQL_GET_ROUTE_FROM_NODERID, rhs, nodeRid);
			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public int deleteLogicalLinkFromNodeNamePortName(Connection conn, String deviceName, String portName)
			throws SQLException {
		final String fname = "deleteLogicalLinkFromNodeNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, devicename=%s, portName=%s) - start", fname, conn, deviceName, portName));
		}
		int ret = 0;
		try {
			Object[] params = {deviceName, portName, deviceName, portName};
			ret = utilsJdbc.update(conn, SQL_DELETE_LOGICAL_LINK_FROM_DEVICE_NAME_PORT_NAME, params);
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String
						.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public int deleteRouteFromLogicalLinkRid(Connection conn, String logical_link_id) throws SQLException {
		final String fname = "deleteLogicalLinkFromNodeNamePortName";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, logical_link_id=%s) - start", fname, conn, logical_link_id));
		}
		int ret = 0;
		try {
			Object[] params = {logical_link_id};
			ret = utilsJdbc.update(conn, SQL_DELETE_ROUTE_FROM_LOGICAL_LINK_ID, params);
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
		return ret;
	}

	@Override
	public List<Map<String, Object>> getCableList(Connection conn) throws SQLException {
		final String fname = "getCableList";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		List<Map<String, Object>> maps = null;
		MapListHandler rhs = new MapListHandler(
				"inDeviceName",  "inPortName",  "inPortNumber",
				"outDeviceName", "outPortName", "outPortNumber",
				"@rid.asString()", "band", "used");
		try {
			maps = utilsJdbc.query(
					conn,
					SQL_GET_CABLE_LINKS_ALL,
					rhs);
			return maps;
		} catch (Exception e){
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()){
				logger.trace(String.format("%s(ret=%s) - end", fname, maps));
			}
		}
	}

	@Override
	public List<Map<String, Object>> getNetworkidFromSpineid(Connection conn, String in_spine_id,String out_spine_id,String network_type) throws SQLException {
		final String fname = "getNetworkidFromSpineid";
		if (logger.isTraceEnabled()) {
			logger.trace(String.format("%s(conn=%s, in_spine_id=%s, out_spine_id=%s) - start", fname, conn, in_spine_id,out_spine_id));
		}

		try {
			MapListHandler rsh = new MapListHandler();
			Object[] params={in_spine_id,out_spine_id,network_type};
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NETWORK_ID_FROM_SPID, rsh,params);

			return maps;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public String payoutNetworkid(Connection conn, String in_spine_id,String out_spine_id,String network_type) throws SQLException
	{
		final String fname = "getCableList";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}

		String nwid = "Outer_Tag is sold out!";
		try {
			MapListHandler rsh = new MapListHandler();
			Object[] params = {network_type};
			List<Map<String, Object>> maps = utilsJdbc.query(conn, SQL_GET_NETWORK_ID_OF_FREE, rsh, params);
			if(!maps.isEmpty())
			{
				nwid = maps.get(0).get("outer_tag").toString();
				Object[] insert_params = {in_spine_id,out_spine_id,nwid,network_type};
				int status = utilsJdbc.update(conn, SQL_UPDATE_PAYOUT_OF_NETWORK_ID, insert_params);
			}
			return nwid;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int returnNetworkid(Connection conn, String spine_id,String network_type) throws SQLException
	{
		final String fname = "getCableList";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}

		try {
			Object[] params = {spine_id,spine_id};
			int status = utilsJdbc.update(conn, SQL_UPDATE_RETURN_OF_NETWORK_ID,params);
			return status;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int insertOutertag(Connection conn, String localvlan, String outer_tag, String agid, String ag_inport,String ag_outport,String type,String network_type) throws SQLException {
		final String fname = "insertOutertag";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}

		try {
			Object[] params = {agid,ag_inport,ag_outport,localvlan,outer_tag,type,network_type};
			int status = utilsJdbc.update(conn, SQL_INSERT_OUTER_TAG_OF_FLOW,params);
			return status;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public List<Map<String,Object>> getOutertagflows(Connection conn, String agid) throws SQLException {
		final String fname = "getOutertagflows";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		List<Map<String, Object>> flows = null;
		try {
			Object[] params = {agid};
			flows = utilsJdbc.query(conn, SQL_GET_OUTER_TAG_FLOWS, new MapListHandler(),params);
			return flows;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int DeleteOutertagflows(Connection conn,Long localvlan) throws SQLException {
		final String fname = "DeleteOutertagflows";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		try {
			Object[] params = {localvlan};
			int status = utilsJdbc.update(conn, SQL_DELETE_OUTER_TAG_OF_FLOW,params);
			return status;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int AddNetworkId(Connection conn,int networkid, String type) throws SQLException {
		final String fname = "AddNetworkId";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		try {
			int status;
			Object[] params = {networkid, type};
			List<Map<String,Object>> query = utilsJdbc.query(conn, SQL_GET_ASSIGNED_NETWORK_ID,new MapListHandler(),params);
			if(query.isEmpty()==true)
			{
				status = utilsJdbc.update(conn, SQL_INSERT_NETWORK_ID,params);
			}
			else
			{
				status = 1;
			}
			return status;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

	@Override
	public int delNetworkId(Connection conn,int networkid, String type) throws SQLException {
		final String fname = "delNetworkId";
		if (logger.isTraceEnabled()){
			logger.trace(String.format("%s(conn=%s) - start", fname, conn));
		}
		try {
			int status;
			Object[] params = {networkid, type};
			List<Map<String, Object>> result = utilsJdbc.query(conn, SQL_GET_ASSIGNED_NETWORK_ID,new MapListHandler(),params);
			if(result.get(0).containsKey("spine1")==true)
			{
				if(!result.get(0).get("spine1").equals("") && !result.get(0).get("spine2").equals(""))
				{
					status = STATUS_NOW_USED;
				}
				else
				{
					status = utilsJdbc.update(conn, SQL_DELETE_NETWORK_ID,params);
				}
			}
			else
			{
				status = utilsJdbc.update(conn, SQL_DELETE_NETWORK_ID,params);
			}
			return status;
		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (logger.isTraceEnabled()) {
				//logger.trace(String.format("%s(ret=%s) - end", fname, ret));
			}
		}
	}

    @Override
    public List<Map<String, Object>> getNetworkId(Connection conn) throws SQLException {
            final String fname = "getNetworkId";
/*              if (logger.isTraceEnabled()){
                    logger.trace(String.format("%s(conn=%s, ip=%s, port=%s) - start", fname, conn, ip, port));
            }
*/
            Map<String, Object> ret = null;
            if (logger.isTraceEnabled()){
                    logger.trace(String.format("%s(conn=%s, ofcIpPort=%s) - start", fname, conn, ""));
            }

            try {
                    List<Map<String, Object>> records = utilsJdbc.query(conn, SQL_GET_NETWORK, new MapListHandler());
                    if (records != null && !records.isEmpty()) {
                            ret = records.get(0);
                    }
                    return records;
            } catch (Exception e){
                    throw new SQLException(e.getMessage());
            } finally {
                    if (logger.isTraceEnabled()){
                            logger.trace(String.format("%s(ret=%s) - end", fname, ret));
                    }
            }
    }

}
import static org.okinawaopenlabs.constants.ErrorMessage.*;
import static org.okinawaopenlabs.constants.OfpmDefinition.*;
import static org.okinawaopenlabs.constants.OrientDBDefinition.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.google.gson.JsonSyntaxException;
import org.apache.log4j.Logger;
import org.okinawaopenlabs.ofpm.exception.ValidateException;
import org.okinawaopenlabs.ofpm.json.common.BaseResponse;
import org.okinawaopenlabs.ofpm.json.device.DeviceInfo;
import org.okinawaopenlabs.ofpm.json.device.PortData;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalLink;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalTopology;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalTopologyGetJsonOut;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalTopology.OfpConDeviceInfo;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalTopology.OfpConPortInfo;
import org.okinawaopenlabs.ofpm.json.topology.logical.LogicalTopology.getnetwork;
import org.okinawaopenlabs.ofpm.json.topology.physical.ConnectPhysicalLinksJsonIn;
import org.okinawaopenlabs.ofpm.json.topology.physical.DisconnectPhysicalLinksJsonIn;
import org.okinawaopenlabs.ofpm.json.topology.physical.PhysicalLink;
import org.okinawaopenlabs.ofpm.json.topology.physical.AddNetworkId;
import org.okinawaopenlabs.ofpm.utils.Config;
import org.okinawaopenlabs.ofpm.utils.ConfigImpl;
import org.okinawaopenlabs.ofpm.utils.OFPMUtils;
import org.okinawaopenlabs.ofpm.validate.common.BaseValidate;
import org.okinawaopenlabs.ofpm.validate.topology.physical.ConnectPhysicalLinksJsonInValidate;
import org.okinawaopenlabs.ofpm.validate.topology.physical.DisconnectPhysicalLinksJsonInValidate;
import org.okinawaopenlabs.ofpm.validate.topology.physical.AddNetworkIdJsonInValidate;
import org.okinawaopenlabs.orientdb.client.ConnectionUtilsJdbc;
import org.okinawaopenlabs.orientdb.client.ConnectionUtilsJdbcImpl;
import org.okinawaopenlabs.orientdb.client.Dao;
import org.okinawaopenlabs.orientdb.client.DaoImpl;
/*
 *   Copyright 2015 Okinawa Open Laboratory, General Incorporated Association
 *
 *   Licensed 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 org.okinawaopenlabs.ofpm.business;






public class PhysicalBusinessImpl implements PhysicalBusiness {
	private static final Logger logger = Logger.getLogger(PhysicalBusinessImpl.class);

	Config conf = new ConfigImpl();

	@Override
	public String getPhysicalTopology() {
		final String fname = "getLogicalTopology";
		if (logger.isDebugEnabled()) {
			logger.debug(String.format("%s() - start", fname));
		}
		LogicalTopologyGetJsonOut res = new LogicalTopologyGetJsonOut();

		ConnectionUtilsJdbc utilsJdbc = null;
		Connection conn = null;
		try {
			utilsJdbc = new ConnectionUtilsJdbcImpl();
			conn = utilsJdbc.getConnection(true);
			Dao dao = new DaoImpl(utilsJdbc);

			List<Map<String, Object>> infoMapList = dao.getNodeInfoList(conn);

			/* Make nodes and links */
			List<OfpConDeviceInfo> nodeList = new ArrayList<OfpConDeviceInfo>();
			for (Map<String, Object> infoMap : infoMapList) {
				OfpConDeviceInfo node = new OfpConDeviceInfo();
				node.setDeviceName((String) infoMap.get("name"));
				node.setDeviceType((String) infoMap.get("type"));
				node.setLocation((String) infoMap.get("location"));
				node.setTenant((String) infoMap.get("tenant"));

				List<OfpConPortInfo> portList = new ArrayList<OfpConPortInfo>();
				List<Map<String, Object>> linkDocList = dao.getCableLinksFromDeviceName(conn, (String) infoMap.get("name"));
				if (linkDocList == null) {
					return null;
				}
				for (Map<String, Object> linkDoc : linkDocList) {
					String outDevName = (String)linkDoc.get("outDeviceName");

					PortData ofpPort = new PortData();
					String  outPortName = (String)linkDoc.get("outPortName");
					Integer outPortNmbr = (Integer)linkDoc.get("outPortNumber");
					ofpPort.setDeviceName(outDevName);
					ofpPort.setPortName(outPortName);
					ofpPort.setPortNumber(outPortNmbr);

					String  inPortName = (String)linkDoc.get("inPortName");
					Integer inPortNmbr = (Integer)linkDoc.get("inPortNumber");
					OfpConPortInfo port = new OfpConPortInfo();
					port.setPortName(inPortName);
					port.setPortNumber(inPortNmbr);
					port.setOfpPortLink(ofpPort);

					portList.add(port);
				}
				node.setPorts(portList);
				
				nodeList.add(node);
			}
			

			List<Map<String, Object>> cableMapList = dao.getCableList(conn);
			Set<LogicalLink> linkSet = new HashSet<LogicalLink>();
			List<LogicalLink>      linkList = new ArrayList<LogicalLink>();
			for (Map<String, Object> cableMap : cableMapList) {

				String inDevName  = (String)cableMap.get("inDeviceName");
				String inPortName = (String)cableMap.get("inPortName");
				PortData inPort = new PortData();
				inPort.setDeviceName(inDevName);
				inPort.setPortName(inPortName);

				String outDevName  = (String)cableMap.get("outDeviceName");
				String outPortName = (String)cableMap.get("outPortName");
				PortData outPort = new PortData();
				outPort.setDeviceName(outDevName);
				outPort.setPortName(outPortName);

				List<PortData> ports = new ArrayList<PortData>();
				ports.add(inPort);
				ports.add(outPort);

				LogicalLink link = new LogicalLink();
				link.setLink(ports);

				linkSet.add(link);
			}
			linkList.addAll(linkSet);

			LogicalTopology topology = new LogicalTopology();