function getVSIDsByGroup($group_id)
{
$ret = array();
$vsinfo = spotEntity('ipvs', $group_id);
amplifyCell($vsinfo);
if (count($vsinfo['vips'])) {
$ips = reduceSubarraysToColumn($vsinfo['vips'], 'vip');
$qm = questionMarks(count($ips));
$result = usePreparedSelectBlade("SELECT id FROM IPv4VS WHERE vip IN ({$qm}) ORDER BY vip", $ips);
$ret = array_merge($ret, $result->fetchAll(PDO::FETCH_COLUMN, 0));
unset($result);
}
$bin_marks = array();
foreach ($vsinfo['ports'] as $port) {
if ($port['proto'] == 'MARK') {
$bin_marks[] = pack('N', $port['vport']);
}
}
if (count($bin_marks)) {
$qm = questionMarks(count($bin_marks));
$result = usePreparedSelectBlade("SELECT id FROM IPv4VS WHERE proto = 'MARK' AND vip IN ({$qm}) ORDER BY vip", $bin_marks);
$ret = array_merge($ret, $result->fetchAll(PDO::FETCH_COLUMN, 0));
}
return $ret;
}
function findSparePorts($port_info, $filter)
{
$qparams = array();
$query = "\nSELECT\n\tp.id,\n\tp.name,\n\tp.reservation_comment,\n\tp.iif_id,\n\tp.type as oif_id,\n\tpii.iif_name,\n\tpoi.oif_name,\n\tp.object_id,\n\to.name as object_name\nFROM Port p\nINNER JOIN Object o ON o.id = p.object_id\nINNER JOIN PortInnerInterface pii ON p.iif_id = pii.id\nINNER JOIN PortOuterInterface poi ON poi.id = p.type\n";
// porttype filter (non-strict match)
$query .= "\nINNER JOIN (\n\tSELECT Port.id FROM Port\n\tINNER JOIN\n\t(\n\t\tSELECT DISTINCT\tpic2.iif_id\n\t\tFROM PortInterfaceCompat pic2\n\t\tINNER JOIN PortCompat pc ON pc.type2 = pic2.oif_id\n";
if ($port_info['iif_id'] != 1) {
$query .= " INNER JOIN PortInterfaceCompat pic ON pic.oif_id = pc.type1 WHERE pic.iif_id = ? AND ";
$qparams[] = $port_info['iif_id'];
} else {
$query .= " WHERE pc.type1 = ? AND ";
$qparams[] = $port_info['oif_id'];
}
$query .= "\n\t\t\tpic2.iif_id <> 1\n\t) AS sub1 USING (iif_id)\n\tUNION\n\tSELECT Port.id\n\tFROM Port\n\tINNER JOIN PortCompat ON type1 = type\n\tWHERE\n\t\tiif_id = 1 and type2 = ?\n) AS sub2 ON sub2.id = p.id\n";
$qparams[] = $port_info['oif_id'];
// self and linked ports filter
$query .= " WHERE p.id <> ? " . "AND p.id NOT IN (SELECT porta FROM Link) " . "AND p.id NOT IN (SELECT portb FROM Link) ";
$qparams[] = $port_info['id'];
// rack filter
if (!empty($filter['racks'])) {
// objects directly mounted in the racks
$query .= sprintf('AND p.object_id IN (SELECT DISTINCT object_id FROM RackSpace WHERE rack_id IN (%s) ', questionMarks(count($filter['racks'])));
// children of objects directly mounted in the racks
$query .= sprintf("UNION SELECT child_entity_id FROM EntityLink WHERE parent_entity_type='object' AND child_entity_type = 'object' AND parent_entity_id IN (SELECT DISTINCT object_id FROM RackSpace WHERE rack_id IN (%s)) ", questionMarks(count($filter['racks'])));
// zero-U objects mounted to the racks
$query .= sprintf("UNION SELECT child_entity_id FROM EntityLink WHERE parent_entity_type='rack' AND child_entity_type='object' AND parent_entity_id IN (%s)) ", questionMarks(count($filter['racks'])));
$qparams = array_merge($qparams, $filter['racks']);
$qparams = array_merge($qparams, $filter['racks']);
$qparams = array_merge($qparams, $filter['racks']);
}
// objectname filter
if (!empty($filter['objects'])) {
$query .= 'AND o.name like ? ';
$qparams[] = '%' . $filter['objects'] . '%';
}
// asset_no filter
if (!empty($filter['asset_no'])) {
$query .= 'AND o.asset_no like ? ';
$qparams[] = '%' . $filter['asset_no'] . '%';
}
// portname filter
if (!empty($filter['ports'])) {
$query .= 'AND p.name LIKE ? ';
$qparams[] = '%' . $filter['ports'] . '%';
}
// ordering
$query .= ' ORDER BY o.name';
$ret = array();
$result = usePreparedSelectBlade($query, $qparams);
$rows_by_pn = array();
$prev_object_id = NULL;
// fetch port rows from the DB
while (TRUE) {
$row = $result->fetch(PDO::FETCH_ASSOC);
if (isset($prev_object_id) and (!$row or $row['object_id'] != $prev_object_id)) {
// handle sorted object's portlist
foreach (sortPortList($rows_by_pn) as $ports_subarray) {
foreach ($ports_subarray as $port_row) {
$port_description = $port_row['object_name'] . ' -- ' . $port_row['name'];
if (count($ports_subarray) > 1) {
$if_type = $port_row['iif_id'] == 1 ? $port_row['oif_name'] : $port_row['iif_name'];
$port_description .= " ({$if_type})";
}
if (!empty($port_row['reservation_comment'])) {
$port_description .= ' -- ' . $port_row['reservation_comment'];
}
$ret[$port_row['id']] = $port_description;
}
}
$rows_by_pn = array();
}
$prev_object_id = $row['object_id'];
if ($row) {
$rows_by_pn[$row['name']][] = $row;
} else {
break;
}
}
return $ret;
}
function usePreparedInsertBlade($tablename, $columns)
{
global $dbxlink;
$query = "INSERT INTO {$tablename} (" . implode(', ', array_keys($columns));
$query .= ') VALUES (' . questionMarks(count($columns)) . ')';
// Now the query should be as follows:
// INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
try {
$prepared = $dbxlink->prepare($query);
$prepared->execute(array_values($columns));
return $prepared->rowCount();
} catch (PDOException $e) {
throw convertPDOException($e);
}
}
function linkmgmt_findSparePorts($port_info, $filter, $linktype, $multilink = false, $objectsonly = false, $byname = false, $portcompat = true, $src_object_id = NULL)
{
/*
$linktable ports that will be returned if not linked in this table
$linkinfotable display link for info only show backend links if you want front link a port
front: select ports no front connection and port compat, filter, ...
back:
*/
if ($linktype == 'back') {
$linktable = 'LinkBackend';
$linkinfotable = 'Link';
} else {
$linktable = 'Link';
$linkinfotable = 'LinkBackend';
}
$qparams = array();
$whereparams = array();
// all ports with no link
/* port:object -> linked port:object */
$query = 'SELECT';
$join = "";
$where = " WHERE";
$group = "";
$order = " ORDER BY";
if ($objectsonly) {
$query .= " remotePort.object_id, CONCAT(IFNULL(remoteObject.name, CONCAT('[',remoteObjectDictionary.dict_value,']')), ' (', count(remotePort.id), ')') as name";
$group .= " GROUP by remoteObject.id";
} else {
if ($byname) {
if ($linktype == 'back') {
$arrow = '=?=>';
} else {
$arrow = '-?->';
}
$query .= ' CONCAT(localPort.id, "_", remotePort.id),
CONCAT(IFNULL(localObject.name, CONCAT("[",localObjectDictionary.dict_value,"]")), " : ", localPort.Name, " ' . $arrow . '", remotePort.name, " : ", IFNULL(remoteObject.name,CONCAT("[",remoteObjectDictionary.dict_value,"]")))';
} else {
if ($linktype == 'front') {
$arrow = '==';
} else {
$arrow = '--';
}
$query .= " remotePort.id, CONCAT(IFNULL(remoteObject.name, CONCAT('[',remoteObjectDictionary.dict_value,']')), ' : ', remotePort.name,\n\t\t\t\tIFNULL(CONCAT(' {$arrow} ', IFNULL(IFNULL(infolnk_a.cable,infolnk_b.cable),''), ' {$arrow}> ', InfoPort.name, ' : ', IFNULL(InfoObject.name,CONCAT('[',InfoObjectDictionary.dict_value,']'))),'') ) as Text";
}
}
$query .= " FROM Port as remotePort";
$join .= " LEFT JOIN Object as remoteObject on remotePort.object_id = remoteObject.id";
$order .= " remoteObject.name";
/* object type name */
$join .= " LEFT JOIN Dictionary as remoteObjectDictionary on (remoteObjectDictionary.chapter_id = 1 AND remoteObject.objtype_id = remoteObjectDictionary.dict_key)";
if ($byname) {
/* by name */
$join .= " JOIN Port as localPort on remotePort.name = localPort.name";
$where .= " remotePort.object_id <> ? AND localPort.object_id = ?";
$whereparams[] = $src_object_id;
$whereparams[] = $src_object_id;
/* own port not linked */
$join .= " LEFT JOIN {$linktable} as localLink_a on localPort.id = localLink_a.porta";
$where .= " AND localLink_a.porta is NULL";
$join .= " LEFT JOIN {$linktable} as localLink_b on localPort.id = localLink_b.portb";
$where .= " AND localLink_b.portb is NULL";
$join .= " LEFT JOIN Object as localObject on localObject.id = localPort.object_id";
/* object type name */
$join .= " LEFT JOIN Dictionary as localObjectDictionary on (localObject.objtype_id = localObjectDictionary.dict_key AND localObjectDictionary.chapter_id = 1)";
} else {
/* exclude current port */
$where .= " remotePort.id <> ?";
$whereparams[] = $port_info['id'];
$order .= " ,remotePort.name";
/* add info to remoteport */
$join .= " LEFT JOIN {$linkinfotable} as infolnk_a on remotePort.id = infolnk_a.porta";
$join .= " LEFT JOIN {$linkinfotable} as infolnk_b on remotePort.id = infolnk_b.portb";
$join .= " LEFT JOIN Port as InfoPort on InfoPort.id = IFNULL(infolnk_a.portb, infolnk_b.porta)";
$join .= " LEFT JOIN Object as InfoObject on InfoObject.id = InfoPort.object_id";
/* object type name */
$join .= " LEFT JOIN Dictionary as InfoObjectDictionary on (InfoObject.objtype_id = InfoObjectDictionary.dict_key AND InfoObjectDictionary.chapter_id = 1)";
}
/* only ports which are not linked already */
$join .= " LEFT JOIN {$linktable} as lnk_a on remotePort.id = lnk_a.porta";
$where .= " AND lnk_a.porta is NULL";
$join .= " LEFT JOIN {$linktable} as lnk_b on remotePort.id = lnk_b.portb";
$where .= " AND lnk_b.portb is NULL";
if ($portcompat) {
/* port compat */
$join .= ' INNER JOIN PortInnerInterface pii ON remotePort.iif_id = pii.id
INNER JOIN PortOuterInterface poi ON remotePort.type = poi.id';
// porttype filter (non-strict match)
$join .= ' INNER JOIN (
SELECT Port.id FROM Port
INNER JOIN
(
SELECT DISTINCT pic2.iif_id
FROM PortInterfaceCompat pic2
INNER JOIN PortCompat pc ON pc.type2 = pic2.oif_id';
if ($port_info['iif_id'] != 1) {
$join .= " INNER JOIN PortInterfaceCompat pic ON pic.oif_id = pc.type1 WHERE pic.iif_id = ?";
$qparams[] = $port_info['iif_id'];
} else {
$join .= " WHERE pc.type1 = ?";
$qparams[] = $port_info['oif_id'];
}
$join .= " AND pic2.iif_id <> 1\n\t\t\t ) AS sub1 USING (iif_id)\n\t\t\tUNION\n\t\t\tSELECT Port.id\n\t\t\tFROM Port\n\t\t\tINNER JOIN PortCompat ON type1 = type\n\t\t\tWHERE iif_id = 1 and type2 = ?\n\t\t\t) AS sub2 ON sub2.id = remotePort.id";
$qparams[] = $port_info['oif_id'];
}
$qparams = array_merge($qparams, $whereparams);
// rack filter
if (!empty($filter['racks'])) {
$where .= ' AND remotePort.object_id IN (SELECT DISTINCT object_id FROM RackSpace WHERE rack_id IN (' . questionMarks(count($filter['racks'])) . ')) ';
$qparams = array_merge($qparams, $filter['racks']);
}
// object_id filter
if (!empty($filter['object_id'])) {
$where .= ' AND remoteObject.id = ?';
$qparams[] = $filter['object_id'];
} else {
// objectname filter
if (!empty($filter['objects'])) {
$where .= ' AND remoteObject.name like ? ';
$qparams[] = '%' . $filter['objects'] . '%';
}
}
// portname filter
if (!empty($filter['ports'])) {
$where .= ' AND remotePort.name LIKE ? ';
$qparams[] = '%' . $filter['ports'] . '%';
}
$query .= $join . $where . $group . $order;
$result = usePreparedSelectBlade($query, $qparams);
$row = $result->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_COLUMN);
$result->closeCursor();
/* [id] => displaystring */
return $row;
}