simplify a few functions
[racktables] / wwwroot / inc / database.php
1 <?php
2
3 # This file is a part of RackTables, a datacenter and server room management
4 # framework. See accompanying file "COPYING" for the full copyright and
5 # licensing information.
6
7 /*
8 *
9 * This file is a library of database access functions for RackTables.
10 *
11 */
12
13 $SQLSchema = array
14 (
15 'object' => array
16 (
17 'table' => 'RackObject',
18 'columns' => array
19 (
20 'id' => 'id',
21 'name' => 'name',
22 'label' => 'label',
23 'asset_no' => 'asset_no',
24 'objtype_id' => 'objtype_id',
25 'rack_id' => '(SELECT MIN(rack_id) FROM RackSpace WHERE object_id = RackObject.id)',
26 'rack_id_2' => "(SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack')",
27 'container_id' => "(SELECT MIN(parent_entity_id) FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object')",
28 'container_name' => '(SELECT name FROM RackObject WHERE id = container_id)',
29 'container_objtype_id' => '(SELECT objtype_id FROM RackObject WHERE id = container_id)',
30 'has_problems' => 'has_problems',
31 'comment' => 'comment',
32 'nports' => '(SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id)',
33 '8021q_domain_id' => '(SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1)',
34 '8021q_template_id' => '(SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1)',
35 ),
36 'keycolumn' => 'id',
37 'ordcolumns' => array ('RackObject.name'),
38 ),
39 'user' => array
40 (
41 'table' => 'UserAccount',
42 'columns' => array
43 (
44 'user_id' => 'user_id',
45 'user_name' => 'user_name',
46 'user_password_hash' => 'user_password_hash',
47 'user_realname' => 'user_realname',
48 ),
49 'keycolumn' => 'user_id',
50 'ordcolumns' => array ('UserAccount.user_name'),
51 ),
52 'ipv4net' => array
53 (
54 'table' => 'IPv4Network',
55 'columns' => array
56 (
57 'id' => 'id',
58 'ip_bin' => 'ip',
59 'mask' => 'mask',
60 'name' => 'name',
61 'comment' => 'comment',
62 '8021q' => '(SELECT GROUP_CONCAT(CONCAT(domain_id, "-", vlan_id) ORDER BY domain_id, vlan_id) FROM VLANIPv4 WHERE ipv4net_id = id)',
63 ),
64 'keycolumn' => 'id',
65 'ordcolumns' => array ('IPv4Network.ip', 'IPv4Network.mask'),
66 ),
67 'ipv6net' => array
68 (
69 'table' => 'IPv6Network',
70 'columns' => array
71 (
72 'id' => 'id',
73 'ip_bin' => 'ip',
74 'mask' => 'mask',
75 'name' => 'name',
76 'comment' => 'comment',
77 '8021q' => '(SELECT GROUP_CONCAT(CONCAT(domain_id, "-", vlan_id) ORDER BY domain_id, vlan_id) FROM VLANIPv6 WHERE ipv6net_id = id)',
78 ),
79 'keycolumn' => 'id',
80 'ordcolumns' => array ('IPv6Network.ip', 'IPv6Network.mask'),
81 ),
82 'file' => array
83 (
84 'table' => 'File',
85 'columns' => array
86 (
87 'id' => 'id',
88 'name' => 'name',
89 'type' => 'type',
90 'size' => 'size',
91 'ctime' => 'ctime',
92 'mtime' => 'mtime',
93 'atime' => 'atime',
94 'comment' => 'comment',
95 ),
96 'keycolumn' => 'id',
97 'ordcolumns' => array ('File.name'),
98 ),
99 'ipv4vs' => array
100 (
101 'table' => 'IPv4VS',
102 'columns' => array
103 (
104 'id' => 'id',
105 'vip_bin' => 'vip',
106 'vport' => 'vport',
107 'proto' => 'proto',
108 'name' => 'name',
109 'vsconfig' => 'vsconfig',
110 'rsconfig' => 'rsconfig',
111 'refcnt' => '(select count(vs_id) from IPv4LB where vs_id = id)',
112 //'vip' =>
113 //'dname' =>
114 ),
115 'keycolumn' => 'id',
116 'ordcolumns' => array ('IPv4VS.vip', 'IPv4VS.proto', 'IPv4VS.vport'),
117 ),
118 'ipvs' => array
119 (
120 'table' => 'VS',
121 'columns' => array
122 (
123 'id' => 'id',
124 'name' => 'name',
125 'vsconfig' => 'vsconfig',
126 'rsconfig' => 'rsconfig',
127 ),
128 'keycolumn' => 'id',
129 ),
130 'ipv4rspool' => array
131 (
132 'table' => 'IPv4RSPool',
133 'columns' => array
134 (
135 'id' => 'id',
136 'name' => 'name',
137 'refcnt' => '(select count(rspool_id) from IPv4LB where rspool_id = id)',
138 'rscount' => '(select count(rspool_id) from IPv4RS where rspool_id = IPv4RSPool.id)',
139 'vsconfig' => 'vsconfig',
140 'rsconfig' => 'rsconfig',
141 ),
142 'keycolumn' => 'id',
143 'ordcolumns' => array ('IPv4RSPool.name', 'IPv4RSPool.id'),
144 ),
145 'rack' => array
146 (
147 'table' => 'Rack',
148 'columns' => array
149 (
150 'id' => 'id',
151 'name' => 'name',
152 'height' => 'height',
153 'asset_no' => 'asset_no',
154 'has_problems' => 'has_problems',
155 'comment' => 'comment',
156 'row_id' => 'row_id',
157 'row_name' => 'row_name',
158 'location_id' => 'location_id',
159 'location_name' => 'location_name',
160 ),
161 'keycolumn' => 'id',
162 'ordcolumns' => array ('location_name', 'row_name', 'sort_order', 'Rack.name'),
163 'pidcolumn' => 'row_id',
164 ),
165 'row' => array
166 (
167 'table' => 'Row',
168 'columns' => array
169 (
170 'id' => 'id',
171 'name' => 'name',
172 'location_id' => 'location_id',
173 'location_name' => 'location_name',
174 'rackc' => '(select count(Rack.id) from Rack where row_id = Row.id)',
175 ),
176 'keycolumn' => 'id',
177 'ordcolumns' => array ('location_name', 'name'),
178 ),
179 'location' => array
180 (
181 'table' => 'Location',
182 'columns' => array
183 (
184 'id' => 'id',
185 'name' => 'name',
186 'has_problems' => 'has_problems',
187 'comment' => 'comment',
188 'parent_id' => 'parent_id',
189 'parent_name' => 'parent_name',
190 'refcnt' => "(SELECT COUNT(child_entity_id) FROM EntityLink EL WHERE EL.parent_entity_type = 'location' AND EL.parent_entity_id = Location.id)",
191 ),
192 'keycolumn' => 'id',
193 'ordcolumns' => array ('name'),
194 ),
195 'vst' => array
196 (
197 'table' => 'VLANSwitchTemplate',
198 'columns' => array
199 (
200 'id' => 'id',
201 'description' => 'description',
202 'mutex_rev' => 'mutex_rev',
203 'saved_by' => 'saved_by',
204 'switchc' => '(SELECT COUNT(object_id) FROM VLANSwitch WHERE template_id = id)',
205 'rulec' => '(SELECT COUNT(rule_no) FROM VLANSTRule WHERE vst_id = id)',
206 ),
207 'keycolumn' => 'id',
208 'ordcolumns' => array ('description'),
209 ),
210 );
211
212 $searchfunc = array
213 (
214 'object' => array
215 (
216 'by_port' => 'getPortSearchResults',
217 'by_attr' => 'getObjectAttrsSearchResults',
218 'by_iface' => 'getObjectIfacesSearchResults',
219 'by_nat' => 'getObjectNATSearchResults',
220 'by_cableid' => 'searchCableIDs',
221 ),
222 );
223
224 $tablemap_8021q = array
225 (
226 'desired' => array
227 (
228 'pvm' => 'PortVLANMode',
229 'pav' => 'PortAllowedVLAN',
230 'pnv' => 'PortNativeVLAN',
231 ),
232 'cached' => array
233 (
234 'pvm' => 'CachedPVM',
235 'pav' => 'CachedPAV',
236 'pnv' => 'CachedPNV',
237 ),
238 );
239
240 // VST roles
241 $port_role_options = array
242 (
243 'none' => 'none',
244 'access' => 'user: access only',
245 'trunk' => 'user: trunk only',
246 'anymode' => 'user: any mode',
247 'uplink' => 'system: uplink trunk',
248 'downlink' => 'system: downlink trunk',
249 );
250
251 // flags to pass to scanIPSpace, scanIPv4Space, scanIPv6Space
252 define ('IPSCAN_DO_ADDR', 1 << 0);
253 define ('IPSCAN_DO_ALLOCS', 1 << 1);
254 define ('IPSCAN_DO_VS', 1 << 2);
255 define ('IPSCAN_DO_RS', 1 << 3);
256 define ('IPSCAN_DO_NAT', 1 << 4);
257 define ('IPSCAN_DO_LOG', 1 << 5);
258 define ('IPSCAN_RTR_ONLY', 1 << 6);
259
260 define ('IPSCAN_ANY', -1 ^ IPSCAN_RTR_ONLY);
261 define ('IPSCAN_DO_SLB', IPSCAN_DO_VS | IPSCAN_DO_RS);
262
263 $object_attribute_cache = array();
264
265 // Return list of locations directly under a specified location
266 function getLocations ($location_id)
267 {
268 $result = usePreparedSelectBlade
269 (
270 'SELECT id, name FROM Location WHERE parent_id = ? ORDER BY name',
271 array ($location_id)
272 );
273 return reduceSubarraysToColumn (reindexById ($result->fetchAll (PDO::FETCH_ASSOC)), 'name');
274 }
275
276 // Return detailed information about one rack row.
277 function getRowInfo ($row_id)
278 {
279 $query =
280 "SELECT Row.id AS id, Row.name AS name, COUNT(Rack.id) AS count, " .
281 "IF(ISNULL(SUM(Rack.height)),0,SUM(Rack.height)) AS sum, " .
282 "Location.id AS location_id, Location.name AS location " .
283 "FROM Row LEFT JOIN Rack ON Rack.row_id = Row.id " .
284 "LEFT OUTER JOIN Location ON Row.location_id = Location.id " .
285 "WHERE Row.id = ? " .
286 "GROUP BY Row.id, Location.id";
287 $result = usePreparedSelectBlade ($query, array ($row_id));
288 if ($row = $result->fetch (PDO::FETCH_ASSOC))
289 return $row;
290 throw new EntityNotFoundException ('rackrow', $row_id);
291 }
292
293 // TODO: deprecated function. delete it
294 function getAllRows ()
295 {
296 return listCells ('row');
297 }
298
299 // Return list of rows directly under a specified location
300 function getRows ($location_id)
301 {
302 $result = usePreparedSelectBlade
303 (
304 'SELECT R.id, R.name FROM Row R ' .
305 'INNER JOIN EntityLink EL ON ' .
306 "EL.parent_entity_type = 'location' " .
307 "AND EL.child_entity_type = 'row' " .
308 "AND EL.child_entity_id = R.id " .
309 'WHERE EL.parent_entity_id = ? ' .
310 'ORDER BY R.name',
311 array ($location_id)
312 );
313 return reduceSubarraysToColumn (reindexById ($result->fetchAll (PDO::FETCH_ASSOC)), 'name');
314 }
315
316 function getRacks ($row_id)
317 {
318 $result = usePreparedSelectBlade
319 (
320 'SELECT id, name, asset_no, height, sort_order, comment, row_name FROM Rack WHERE row_id = ? ORDER BY sort_order',
321 array ($row_id)
322 );
323 return reindexById ($result->fetchAll (PDO::FETCH_ASSOC));
324 }
325
326 # Return rack and row details for those objects on the list that have
327 # at least one rackspace atom allocated to them.
328 function getMountInfo ($object_ids)
329 {
330 if (! count ($object_ids))
331 return array();
332 # In theory number of involved racks can be equal or even greater, than the
333 # number of objects, but in practice it will often be tens times less. Hence
334 # the scope of the 1st pass is to tell IDs of all involved racks without
335 # fetching lots of duplicate data.
336 $result = usePreparedSelectBlade
337 (
338 'SELECT object_id, rack_id ' .
339 'FROM RackSpace ' .
340 'WHERE object_id IN(' . questionMarks (count ($object_ids)) . ') ' .
341 'GROUP BY object_id, rack_id ' .
342 'UNION ' .
343 'SELECT child_entity_id AS object_id, parent_entity_id AS rack_id ' .
344 'FROM EntityLink ' .
345 'WHERE child_entity_id IN(' . questionMarks (count ($object_ids)) . ') ' .
346 "AND parent_entity_type = 'rack' AND child_entity_type = 'object' " .
347 'ORDER BY rack_id ASC',
348 array_merge($object_ids, $object_ids)
349 );
350 $rackidlist = $objectlist = array();
351 foreach ($result as $row)
352 {
353 $objectlist[$row['object_id']][] = $row['rack_id'];
354 $rackidlist[] = $row['rack_id'];
355 }
356 unset ($result);
357 # short-cut to exit in case no object had rackspace allocated
358 if (! count ($rackidlist))
359 {
360 $ret = array();
361 foreach ($object_ids as $object_id)
362 $ret[$object_id] = array();
363 return $ret;
364 }
365 # Pass 2. Fetch shorter, but better extra data about the rows and racks,
366 # set displayed names for both.
367 $result = usePreparedSelectBlade
368 (
369 'SELECT Rack_.id as rack_id, Rack_.name AS rack_name, Rack_.label as rack_label, ' .
370 'parent_entity_id AS row_id, Row_.name AS row_name ' .
371 'FROM Object Rack_ ' .
372 "LEFT JOIN EntityLink ON (Rack_.id = child_entity_id AND parent_entity_type = 'row' AND child_entity_type = 'rack') " .
373 'LEFT JOIN Object Row_ ON parent_entity_id = Row_.id ' .
374 'WHERE Rack_.id IN(' . questionMarks (count ($rackidlist)) . ') ',
375 $rackidlist
376 );
377 $rackinfo = array();
378 foreach ($result as $row)
379 {
380 $rackinfo[$row['rack_id']] = array
381 (
382 'rack_id' => $row['rack_id'],
383 'row_id' => $row['row_id'],
384 );
385 if ('' != $row['rack_name'])
386 $rackinfo[$row['rack_id']]['rack_name'] = $row['rack_name'];
387 elseif ('' != $row['rack_label'])
388 $rackinfo[$row['rack_id']]['rack_name'] = $row['rack_label'];
389 else
390 $rackinfo[$row['rack_id']]['rack_name'] = 'rack#' . $row['rack_id'];
391 if ('' != $row['row_name'])
392 $rackinfo[$row['rack_id']]['row_name'] = $row['row_name'];
393 else
394 $rackinfo[$row['rack_id']]['row_name'] = 'row#' . $row['row_id'];
395 }
396 unset ($result);
397 # Pass 3. Combine retrieved data into returned array.
398 $ret = array();
399 foreach ($objectlist as $object_id => $racklist)
400 foreach ($racklist as $rack_id)
401 $ret[$object_id][] = $rackinfo[$rack_id];
402 return $ret;
403 }
404
405 # Return container details for a list of objects
406 function getContainerInfo ($object_ids)
407 {
408 if (! count ($object_ids))
409 return array ();
410 $result = usePreparedSelectBlade
411 (
412 'SELECT EL.child_entity_id, EL.parent_entity_id, RO.name, RO.objtype_id ' .
413 'FROM EntityLink EL ' .
414 'LEFT JOIN RackObject RO ON EL.parent_entity_id = RO.id ' .
415 'WHERE EL.child_entity_id IN (' . questionMarks (count ($object_ids)) . ') ' .
416 "AND EL.parent_entity_type = 'object' " .
417 "AND EL.child_entity_type = 'object' " .
418 'ORDER BY RO.name',
419 $object_ids
420 );
421 $ret = array ();
422 foreach ($result as $row)
423 $ret[$row['child_entity_id']][] = array
424 (
425 'container_id' => $row['parent_entity_id'],
426 'container_dname' => formatObjectDisplayedName ($row['name'], $row['objtype_id'])
427 );
428 unset ($result);
429 return $ret;
430 }
431
432 // For a given realm return a list of entity records, each with
433 // enough information for judgeCell() to execute.
434 function listCells ($realm, $parent_id = 0)
435 {
436 if (!$parent_id)
437 {
438 global $entityCache;
439 if (isset ($entityCache['complete'][$realm]))
440 return $entityCache['complete'][$realm];
441 }
442 global $SQLSchema;
443 if (!isset ($SQLSchema[$realm]))
444 throw new InvalidArgException ('realm', $realm);
445 $SQLinfo = $SQLSchema[$realm];
446 $qparams = array ();
447 $query = 'SELECT ';
448 foreach ($SQLinfo['columns'] as $alias => $expression)
449 // Automatically prepend table name to each single column, but leave all others intact.
450 $query .= ($alias == $expression ? "${SQLinfo['table']}.${alias}" : "${expression} as ${alias}") . ', ';
451 $query = trim($query, ', ');
452 $query .= " FROM ${SQLinfo['table']}";
453 if (isset ($SQLinfo['pidcolumn']) && $parent_id)
454 {
455 $query .= " WHERE ${SQLinfo['table']}.${SQLinfo['pidcolumn']} = ?";
456 $qparams[] = $parent_id;
457 }
458 if (isset ($SQLinfo['ordcolumns']))
459 {
460 $query .= " ORDER BY ";
461 foreach ($SQLinfo['ordcolumns'] as $oc)
462 $query .= "${oc}, ";
463 $query = trim($query, ', ');
464 }
465 $result = usePreparedSelectBlade ($query, $qparams);
466 $ret = array();
467 // Index returned result by the value of key column.
468 while ($row = $result->fetch (PDO::FETCH_ASSOC))
469 {
470 $entity_id = $row[$SQLinfo['keycolumn']];
471 $ret[$entity_id] = array ('realm' => $realm);
472 $ret[$entity_id]['etags'] = array();
473 foreach (array_keys ($SQLinfo['columns']) as $alias)
474 $ret[$entity_id][$alias] = $row[$alias];
475 // use the temporary rack_id_2 key and remove this key from the result array
476 if ($realm == 'object')
477 {
478 if (! isset ($ret[$entity_id]['rack_id']))
479 $ret[$entity_id]['rack_id'] = $ret[$entity_id]['rack_id_2'];
480 unset ($ret[$entity_id]['rack_id_2']);
481 }
482 }
483 unset($result);
484
485 // select tags and link them to previosly fetched entities
486 $query = 'SELECT entity_id, tag_id, user AS tag_user, UNIX_TIMESTAMP(date) AS tag_time FROM TagStorage WHERE entity_realm = ?';
487 $result = usePreparedSelectBlade ($query, array($realm));
488 global $taglist;
489 while ($row = $result->fetch (PDO::FETCH_ASSOC))
490 {
491 $tag_id = $row['tag_id'];
492 if (array_key_exists($row['entity_id'], $ret))
493 $ret[$row['entity_id']]['etags'][$tag_id] = array
494 (
495 'id' => $tag_id,
496 'tag' => $taglist[$tag_id]['tag'],
497 'parent_id' => $taglist[$tag_id]['parent_id'],
498 'user' => $row['tag_user'],
499 'time' => $row['tag_time'],
500 );
501 }
502 unset($result);
503 // Add necessary finish to the list before returning it. Maintain caches.
504 if (!$parent_id)
505 unset ($entityCache['partial'][$realm]);
506 if ($realm == 'object') // cache dict attributes of all objects to speed up autotags calculation
507 cacheDictAttrValues();
508 foreach ($ret as $entity_id => &$entity)
509 {
510 sortEntityTags ($entity); // changes ['etags'] and ['itags']
511 switch ($realm)
512 {
513 case 'object':
514 setDisplayedName ($entity); // set $entity['dname']
515 break;
516 case 'ipv4net':
517 $entity = array_merge ($entity, constructIPRange (ip4_int2bin ($entity['ip_bin']), $entity['mask']));
518 processIPNetVlans ($entity);
519 $entity['spare_ranges'] = array();
520 $entity['kidc'] = 0;
521 break;
522 case 'ipv6net':
523 $entity = array_merge ($entity, constructIPRange ($entity['ip_bin'], $entity['mask']));
524 processIPNetVlans ($entity);
525 $entity['spare_ranges'] = array();
526 $entity['kidc'] = 0;
527 break;
528 case 'ipv4vs':
529 $entity['vip'] = ip_format ($entity['vip_bin']);
530 setDisplayedName ($entity); // set $entity['dname']
531 $entity['vsconfig'] = dos2unix ($entity['vsconfig']);
532 $entity['rsconfig'] = dos2unix ($entity['rsconfig']);
533 break;
534 case 'ipv4rspool':
535 $entity['vsconfig'] = dos2unix ($entity['vsconfig']);
536 $entity['rsconfig'] = dos2unix ($entity['rsconfig']);
537 break;
538 case 'ipvs':
539 $entity['vsconfig'] = dos2unix ($entity['vsconfig']);
540 $entity['rsconfig'] = dos2unix ($entity['rsconfig']);
541 break;
542 default:
543 break;
544 }
545 }
546 if ($realm == 'ipv4net' || $realm == 'ipv6net')
547 fillIPNetsCorrelation ($ret);
548
549 foreach (array_keys ($ret) as $entity_id)
550 {
551 $entity = &$ret[$entity_id];
552 $entity['atags'] = callHook ('generateEntityAutoTags', $entity);
553 if (!$parent_id)
554 $entityCache['complete'][$realm][$entity_id] = $entity;
555 else
556 $entityCache['partial'][$realm][$entity_id] = $entity;
557 }
558
559 return $ret;
560 }
561
562 // Very much like listCells(), but return only one record requested
563 // throws an exception if entity not exists
564 function spotEntity ($realm, $id, $ignore_cache = FALSE)
565 {
566 if (! $ignore_cache)
567 {
568 global $entityCache;
569 if (isset ($entityCache['complete'][$realm]))
570 {
571 if (isset ($entityCache['complete'][$realm][$id]))
572 return $entityCache['complete'][$realm][$id];
573 }
574 elseif (isset ($entityCache['partial'][$realm][$id]))
575 return $entityCache['partial'][$realm][$id];
576 }
577 global $SQLSchema;
578 if (!isset ($SQLSchema[$realm]))
579 throw new InvalidArgException ('realm', $realm);
580 $SQLinfo = $SQLSchema[$realm];
581 $query = 'SELECT tag_id, TagStorage.user as tag_user, UNIX_TIMESTAMP(TagStorage.date) AS tag_time';
582 foreach ($SQLinfo['columns'] as $alias => $expression)
583 // Automatically prepend table name to each single column, but leave all others intact.
584 $query .= ', ' . ($alias == $expression ? "${SQLinfo['table']}.${alias}" : "${expression} as ${alias}");
585 $query .= " FROM ${SQLinfo['table']} LEFT JOIN TagStorage on entity_realm = ? and entity_id = ${SQLinfo['table']}.${SQLinfo['keycolumn']}";
586 $query .= " WHERE ${SQLinfo['table']}.${SQLinfo['keycolumn']} = ?";
587 $result = usePreparedSelectBlade ($query, array ($realm, $id));
588 $ret = array();
589 global $taglist;
590 while ($row = $result->fetch (PDO::FETCH_ASSOC))
591 if (!isset ($ret['realm']))
592 {
593 $ret = array ('realm' => $realm);
594 foreach (array_keys ($SQLinfo['columns']) as $alias)
595 $ret[$alias] = $row[$alias];
596 // use the temporary rack_id_2 key and remove this key from the result array
597 if ($realm == 'object')
598 {
599 if (! isset ($ret['rack_id']))
600 $ret['rack_id'] = $ret['rack_id_2'];
601 unset ($ret['rack_id_2']);
602 }
603 $ret['etags'] = array();
604 if ($row['tag_id'] != NULL && isset ($taglist[$row['tag_id']]))
605 $ret['etags'][$row['tag_id']] = array
606 (
607 'id' => $row['tag_id'],
608 'tag' => $taglist[$row['tag_id']]['tag'],
609 'parent_id' => $taglist[$row['tag_id']]['parent_id'],
610 'user' => $row['tag_user'],
611 'time' => $row['tag_time'],
612 );
613 }
614 elseif (isset ($taglist[$row['tag_id']]))
615 $ret['etags'][$row['tag_id']] = array
616 (
617 'id' => $row['tag_id'],
618 'tag' => $taglist[$row['tag_id']]['tag'],
619 'parent_id' => $taglist[$row['tag_id']]['parent_id'],
620 'user' => $row['tag_user'],
621 'time' => $row['tag_time'],
622 );
623 unset ($result);
624 if (!isset ($ret['realm'])) // no rows were returned
625 throw new EntityNotFoundException ($realm, $id);
626 sortEntityTags ($ret); // changes ['etags'] and ['itags']
627 switch ($realm)
628 {
629 case 'object':
630 setDisplayedName ($ret); // set $ret['dname']
631 break;
632 case 'ipv4net':
633 processIPNetVlans ($ret);
634 $ret = array_merge ($ret, constructIPRange (ip4_int2bin ($ret['ip_bin']), $ret['mask']));
635 if (! fillNetKids ($ret))
636 {
637 $ret['spare_ranges'] = array();
638 $ret['kidc'] = 0;
639 }
640 break;
641 case 'ipv6net':
642 processIPNetVlans ($ret);
643 $ret = array_merge ($ret, constructIPRange ($ret['ip_bin'], $ret['mask']));
644 if (! fillNetKids ($ret))
645 {
646 $ret['spare_ranges'] = array();
647 $ret['kidc'] = 0;
648 }
649 break;
650 case 'ipv4vs':
651 $ret['vip'] = ip_format ($ret['vip_bin']);
652 setDisplayedName ($ret); // set $ret['dname']
653 $ret['vsconfig'] = dos2unix ($ret['vsconfig']);
654 $ret['rsconfig'] = dos2unix ($ret['rsconfig']);
655 break;
656 case 'ipv4rspool':
657 $ret['vsconfig'] = dos2unix ($ret['vsconfig']);
658 $ret['rsconfig'] = dos2unix ($ret['rsconfig']);
659 break;
660 case 'ipvs':
661 $ret['vsconfig'] = dos2unix ($ret['vsconfig']);
662 $ret['rsconfig'] = dos2unix ($ret['rsconfig']);
663 break;
664 default:
665 break;
666 }
667
668 $ret['atags'] = generateEntityAutoTags ($ret);
669 if (! $ignore_cache)
670 $entityCache['partial'][$realm][$id] = $ret;
671 return $ret;
672 }
673
674 function fillNetKids (&$net_cell)
675 {
676 if ($net_cell['realm'] == 'ipv6net')
677 {
678 $table = 'IPv6Network';
679 $ip_first = $net_cell['ip_bin'];
680 $ip_last = ip_last ($net_cell);
681 }
682 else
683 {
684 $table = 'IPv4Network';
685 $ip_first = ip4_bin2db ($net_cell['ip_bin']);
686 $ip_last = ip4_bin2db (ip_last ($net_cell));
687 }
688
689 $result = usePreparedSelectBlade ("
690 SELECT id, ip as ip_bin, mask FROM $table
691 WHERE ip BETWEEN ? AND ? AND mask >= ?
692 ORDER BY ip, mask
693 ", array ($ip_first, $ip_last, $net_cell['mask']));
694 $nets = array();
695 while ($net_row = $result->fetch (PDO::FETCH_ASSOC))
696 {
697 $ip_bin = $net_row['ip_bin'];
698 if ($net_cell['realm'] == 'ipv4net')
699 $ip_bin = ip4_int2bin ($ip_bin);
700 $nets[] = constructIPRange ($ip_bin, $net_row['mask']) +
701 array(
702 'id' => $net_row['id'],
703 'spare_ranges' => array(),
704 'kidc' => 0,
705 );
706 }
707 unset ($result);
708
709 fillIPNetsCorrelation ($nets, 1);
710 if (is_array ($nets[0]) && $nets[0]['id'] == $net_cell['id'])
711 {
712 $net_cell['spare_ranges'] = $nets[0]['spare_ranges'];
713 $net_cell['kidc'] = $nets[0]['kidc'];
714 return TRUE;
715 }
716 return FALSE;
717 }
718
719 // This function can be used with array_walk().
720 function amplifyCell (&$record, $dummy = NULL)
721 {
722 switch ($record['realm'])
723 {
724 case 'object':
725 $record['ports'] = getObjectPortsAndLinks ($record['id']);
726 $record['ipv4'] = getObjectIPv4Allocations ($record['id']);
727 $record['ipv6'] = getObjectIPv6Allocations ($record['id']);
728 $record['nat4'] = getNATv4ForObject ($record['id']);
729 $record['files'] = getFilesOfEntity ($record['realm'], $record['id']);
730 break;
731 case 'file':
732 $record['links'] = getFileLinks ($record['id']);
733 break;
734 case 'location':
735 $record['locations'] = getLocations ($record['id']);
736 $record['rows'] = getRows ($record['id']);
737 break;
738 case 'row':
739 $record['racks'] = getRacks ($record['id']);
740 break;
741 case 'rack':
742 // start with default rackspace
743 for ($i = $record['height']; $i > 0; $i--)
744 for ($locidx = 0; $locidx < 3; $locidx++)
745 $record[$i][$locidx]['state'] = 'F';
746 // load difference
747 $query =
748 "select unit_no, atom, state, object_id, has_problems " .
749 "from RackSpace LEFT JOIN Object ON Object.id = object_id where rack_id = ? and " .
750 "unit_no between 1 and ? order by unit_no";
751 $result = usePreparedSelectBlade ($query, array ($record['id'], $record['height']));
752 global $loclist;
753 $mounted_objects = array();
754 // fetch Zero-U mounted objects
755 foreach (getChildren ($record, 'object') as $child)
756 $mounted_objects[$child['id']] = TRUE;
757
758 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
759 unset ($result);
760 foreach ($rows as $row)
761 {
762 $record[$row['unit_no']][$loclist[$row['atom']]]['state'] = $row['state'];
763 $record[$row['unit_no']][$loclist[$row['atom']]]['object_id'] = $row['object_id'];
764 $record[$row['unit_no']][$loclist[$row['atom']]]['hl'] = $row['has_problems'] == 'yes' ? 'w' : '';
765 if ($row['state'] == 'T' && $row['object_id'] != NULL)
766 $mounted_objects[$row['object_id']] = TRUE;
767 }
768
769 $record['isDeletable'] = (count ($rows) || count ($mounted_objects)) ? FALSE : TRUE;
770 $record['mountedObjects'] = array_keys ($mounted_objects);
771 break;
772 case 'vst':
773 $record['rules'] = array();
774 $record['switches'] = array();
775 $result = usePreparedSelectBlade
776 (
777 'SELECT rule_no, port_pcre, port_role, wrt_vlans, description ' .
778 'FROM VLANSTRule WHERE vst_id = ? ORDER BY rule_no',
779 array ($record['id'])
780 );
781 while ($row = $result->fetch (PDO::FETCH_ASSOC))
782 $record['rules'][$row['rule_no']] = $row;
783 unset ($result);
784 $result = usePreparedSelectBlade ('SELECT object_id, domain_id FROM VLANSwitch WHERE template_id = ?', array ($record['id']));
785 while ($row = $result->fetch (PDO::FETCH_ASSOC))
786 $record['switches'][$row['object_id']] = $row;
787 break;
788 case 'ipvs':
789 $record['ports'] = array();
790 $record['vips'] = array();
791 $result = usePreparedSelectBlade ("SELECT proto, vport, vsconfig, rsconfig FROM VSPorts WHERE vs_id = ?", array ($record['id']));
792 while ($row = $result->fetch (PDO::FETCH_ASSOC))
793 {
794 $row['vsconfig'] = dos2unix ($row['vsconfig']);
795 $row['rsconfig'] = dos2unix ($row['rsconfig']);
796 $record['ports'][] = $row;
797 }
798 unset ($result);
799 $result = usePreparedSelectBlade ("SELECT vip, vsconfig, rsconfig FROM VSIPs WHERE vs_id = ?", array ($record['id']));
800 while ($row = $result->fetch (PDO::FETCH_ASSOC))
801 {
802 $row['vsconfig'] = dos2unix ($row['vsconfig']);
803 $row['rsconfig'] = dos2unix ($row['rsconfig']);
804 $record['vips'][] = $row;
805 }
806 unset ($result);
807 break;
808 default:
809 }
810 }
811
812 // is called by spotEntity and listCells.
813 // replaces ['8021q'] text value in cell by an array with 'domain_id' and 'vlan_id' subkeys
814 // also sets ['vlanc'] cell key to the binded vlans count.
815 function processIPNetVlans (&$cell)
816 {
817 if (empty ($cell['8021q']))
818 $cell['8021q'] = array();
819 else
820 {
821 $ck_list = explode (',', $cell['8021q']);
822 $cell['8021q'] = array();
823 foreach ($ck_list as $vlan_ck)
824 {
825 list ($domain_id, $vlan_id) = decodeVLANCK ($vlan_ck);
826 $cell['8021q'][] = array ('domain_id' => $domain_id, 'vlan_id' => $vlan_id);
827 }
828 }
829 $cell['vlanc'] = count ($cell['8021q']);
830 }
831
832 function fetchPortList ($sql_where_clause, $query_params = array())
833 {
834 $query = <<<END
835 SELECT
836 Port.id,
837 Port.name,
838 Port.object_id,
839 Object.name AS object_name,
840 Port.l2address,
841 Port.label,
842 Port.reservation_comment,
843 Port.iif_id,
844 Port.type AS oif_id,
845 (SELECT PortInnerInterface.iif_name FROM PortInnerInterface WHERE PortInnerInterface.id = Port.iif_id) AS iif_name,
846 (SELECT PortOuterInterface.oif_name FROM PortOuterInterface WHERE PortOuterInterface.id = Port.type) AS oif_name,
847 IF(la.porta, la.cable, lb.cable) AS cableid,
848 IF(la.porta, pa.id, pb.id) AS remote_id,
849 IF(la.porta, pa.name, pb.name) AS remote_name,
850 IF(la.porta, pa.object_id, pb.object_id) AS remote_object_id,
851 IF(la.porta, oa.name, ob.name) AS remote_object_name,
852 (SELECT COUNT(*) FROM PortLog WHERE PortLog.port_id = Port.id) AS log_count,
853 PortLog.user,
854 UNIX_TIMESTAMP(PortLog.date) as time
855 FROM
856 Port
857 INNER JOIN Object ON Port.object_id = Object.id
858 LEFT JOIN Link AS la ON la.porta = Port.id
859 LEFT JOIN Port AS pa ON pa.id = la.portb
860 LEFT JOIN Object AS oa ON pa.object_id = oa.id
861 LEFT JOIN Link AS lb on lb.portb = Port.id
862 LEFT JOIN Port AS pb ON pb.id = lb.porta
863 LEFT JOIN Object AS ob ON pb.object_id = ob.id
864 LEFT JOIN PortLog ON PortLog.id = (SELECT id FROM PortLog WHERE PortLog.port_id = Port.id ORDER BY date DESC LIMIT 1)
865 WHERE
866 $sql_where_clause
867 END;
868
869 $result = usePreparedSelectBlade ($query, $query_params);
870 $ret = array();
871 while ($row = $result->fetch (PDO::FETCH_ASSOC))
872 {
873 $row['l2address'] = l2addressFromDatabase ($row['l2address']);
874 $row['linked'] = isset ($row['remote_id']) ? 1 : 0;
875
876 // last changed log
877 $row['last_log'] = array();
878 if ($row['log_count'])
879 {
880 $row['last_log']['user'] = $row['user'];
881 $row['last_log']['time'] = $row['time'];
882 }
883 unset ($row['user']);
884 unset ($row['time']);
885
886 $ret[] = $row;
887 }
888 return $ret;
889 }
890
891 function getObjectPortsAndLinks ($object_id, $sorted = TRUE)
892 {
893 $ret = fetchPortList ("Port.object_id = ?", array ($object_id));
894 if ($sorted)
895 $ret = sortPortList ($ret, TRUE);
896 return $ret;
897 }
898
899 // This function provides data for syncObjectPorts() and requires only two tables locked.
900 function getObjectPortsAndLinksTerse ($object_id)
901 {
902 $result = usePreparedSelectBlade
903 (
904 'SELECT id, name, iif_id, type AS oif_id, label, l2address, reservation_comment, ' .
905 '(SELECT COUNT(*) FROM Link WHERE porta = Port.id OR portb = Port.id) AS link_count ' .
906 'FROM Port WHERE object_id = ?',
907 array ($object_id)
908 );
909 return $result->fetchAll (PDO::FETCH_ASSOC);
910 }
911
912 // Fetch the object type via SQL.
913 // spotEntity cannot be used because it references RackObject, which doesn't suit Racks, Rows, or Locations.
914 function getObjectType ($object_id)
915 {
916 $result = usePreparedSelectBlade ('SELECT objtype_id from Object WHERE id = ?', array ($object_id));
917 return $result->fetchColumn ();
918 }
919
920 // If the given name is used by any object other than the current object,
921 // raise an exception. Validation is bypassed for certain object types
922 // where duplicates are acceptable.
923 // NOTE: This could be enforced more strictly at the database level using triggers.
924 function checkObjectNameUniqueness ($name, $type_id, $object_id = 0)
925 {
926 // Some object types do not need unique names
927 // 1560 - Rack
928 // 1561 - Row
929 $dupes_allowed = array (1560, 1561);
930 if (in_array ($type_id, $dupes_allowed))
931 return;
932
933 $result = usePreparedSelectBlade
934 (
935 'SELECT COUNT(*) FROM Object WHERE name = ? AND id != ?',
936 array ($name, $object_id)
937 );
938 $row = $result->fetch (PDO::FETCH_NUM);
939 if ($row[0] != 0)
940 throw new InvalidRequestArgException ('name', $name, 'An object with that name already exists');
941 }
942
943 function commitAddObject ($new_name, $new_label, $new_type_id, $new_asset_no, $taglist = array())
944 {
945 checkObjectNameUniqueness ($new_name, $new_type_id);
946 usePreparedInsertBlade
947 (
948 'Object',
949 array
950 (
951 'name' => nullIfEmptyStr ($new_name),
952 'label' => nullIfEmptyStr ($new_label),
953 'objtype_id' => $new_type_id,
954 'asset_no' => nullIfEmptyStr ($new_asset_no),
955 )
956 );
957 $object_id = lastInsertID();
958 switch ($new_type_id)
959 {
960 case 1560:
961 $realm = 'rack';
962 break;
963 case 1561:
964 $realm = 'row';
965 break;
966 case 1562:
967 $realm = 'location';
968 break;
969 default:
970 $realm = 'object';
971 }
972 lastCreated ($realm, $object_id);
973
974 // Do AutoPorts magic
975 if ($realm == 'object')
976 executeAutoPorts ($object_id);
977 // Now tags...
978 produceTagsForNewRecord ($realm, $taglist, $object_id);
979 recordObjectHistory ($object_id);
980 return $object_id;
981 }
982
983 function commitRenameObject ($object_id, $new_name)
984 {
985 $type_id = getObjectType ($object_id);
986 checkObjectNameUniqueness ($new_name, $type_id, $object_id);
987 usePreparedUpdateBlade
988 (
989 'Object',
990 array
991 (
992 'name' => nullIfEmptyStr ($new_name),
993 ),
994 array
995 (
996 'id' => $object_id
997 )
998 );
999 recordObjectHistory ($object_id);
1000 }
1001
1002 function commitUpdateObject ($object_id, $new_name, $new_label, $new_has_problems, $new_asset_no, $new_comment)
1003 {
1004 $type_id = getObjectType ($object_id);
1005 checkObjectNameUniqueness ($new_name, $type_id, $object_id);
1006 usePreparedUpdateBlade
1007 (
1008 'Object',
1009 array
1010 (
1011 'name' => nullIfEmptyStr ($new_name),
1012 'label' => nullIfEmptyStr ($new_label),
1013 'has_problems' => $new_has_problems == '' ? 'no' : $new_has_problems,
1014 'asset_no' => nullIfEmptyStr ($new_asset_no),
1015 'comment' => nullIfEmptyStr ($new_comment),
1016 ),
1017 array
1018 (
1019 'id' => $object_id
1020 )
1021 );
1022 recordObjectHistory ($object_id);
1023 }
1024
1025 function compare_name ($a, $b)
1026 {
1027 return strnatcmp($a['name'], $b['name']);
1028 }
1029
1030 // find either parents or children of a record
1031 function getEntityRelatives ($type, $entity_type, $entity_id)
1032 {
1033 $ret = array();
1034
1035 if ($type == 'parents')
1036 {
1037 // searching for parents
1038 $sql =
1039 'SELECT id, parent_entity_type AS entity_type, parent_entity_id AS entity_id FROM EntityLink ' .
1040 'WHERE child_entity_type = ? AND child_entity_id = ?';
1041 }
1042 else
1043 {
1044 // searching for children
1045 $sql =
1046 'SELECT id, child_entity_type AS entity_type, child_entity_id AS entity_id FROM EntityLink ' .
1047 'WHERE parent_entity_type = ? AND parent_entity_id = ?';
1048 }
1049
1050 $result = usePreparedSelectBlade ($sql, array ($entity_type, $entity_id));
1051 while ($row = $result->fetch (PDO::FETCH_ASSOC))
1052 $ret[$row['id']] = array(
1053 'entity_type' => $row['entity_type'],
1054 'entity_id' => $row['entity_id'],
1055 );
1056
1057 return $ret;
1058 }
1059
1060 function getParents ($entity, $result_realm = NULL)
1061 {
1062 return getRelatives ($entity, 'parents', $result_realm);
1063 }
1064
1065 function getChildren ($entity, $result_realm = NULL)
1066 {
1067 return getRelatives ($entity, 'children', $result_realm);
1068 }
1069
1070 function getRelatives ($entity, $type, $result_realm = NULL)
1071 {
1072 $ret = array();
1073 foreach (getEntityRelatives ($type, $entity['realm'], $entity['id']) as $link_id => $struct)
1074 if (! isset ($result_realm) || $result_realm == $struct['entity_type'])
1075 $ret[$link_id] = spotEntity ($struct['entity_type'], $struct['entity_id']);
1076 return $ret;
1077 }
1078
1079 // This function is recursive and returns only object IDs.
1080 function getObjectContentsList ($object_id, $children = array ())
1081 {
1082 $self = __FUNCTION__;
1083 $result = usePreparedSelectBlade
1084 (
1085 'SELECT child_entity_id FROM EntityLink ' .
1086 'WHERE parent_entity_type = "object" AND child_entity_type = "object" AND parent_entity_id = ?',
1087 array ($object_id)
1088 );
1089 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1090 unset ($result);
1091 foreach ($rows as $row)
1092 {
1093 if (in_array ($row['child_entity_id'], $children))
1094 throw new RackTablesError ("Circular reference for object ${object_id}", RackTablesError::INTERNAL);
1095 $children[] = $row['child_entity_id'];
1096 $children = array_unique (array_merge ($children, $self ($row['child_entity_id'], $children)));
1097 }
1098 return $children;
1099 }
1100
1101 // This function is recursive and returns only location IDs.
1102 function getLocationChildrenList ($location_id, $children = array ())
1103 {
1104 $self = __FUNCTION__;
1105 $result = usePreparedSelectBlade ('SELECT id FROM Location WHERE parent_id = ?', array ($location_id));
1106 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1107 unset ($result);
1108 foreach ($rows as $row)
1109 {
1110 if (in_array ($row['id'], $children))
1111 throw new RackTablesError ("Circular reference for location ${location_id}", RackTablesError::INTERNAL);
1112 $children[] = $row['id'];
1113 $children = array_unique (array_merge ($children, $self ($row['id'], $children)));
1114 }
1115 return $children;
1116 }
1117
1118 // DEPRECATED: use getTagDescendents() instead
1119 // This function is recursive and returns only tag IDs.
1120 function getTagChildrenList ($tag_id, $children = array ())
1121 {
1122 $self = __FUNCTION__;
1123 $result = usePreparedSelectBlade ('SELECT id FROM TagTree WHERE parent_id = ?', array ($tag_id));
1124 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1125 unset ($result);
1126 foreach ($rows as $row)
1127 {
1128 if (in_array ($row['id'], $children))
1129 throw new RackTablesError ("Circular reference for tag ${tag_id}", RackTablesError::INTERNAL);
1130 $children[] = $row['id'];
1131 $children = array_unique (array_merge ($children, $self ($row['id'], $children)));
1132 }
1133 return $children;
1134 }
1135
1136 function commitLinkEntities ($parent_entity_type, $parent_entity_id, $child_entity_type, $child_entity_id)
1137 {
1138 // a location's parent may not be one of its children
1139 if
1140 (
1141 $parent_entity_type == 'location' &&
1142 $child_entity_type == 'location' &&
1143 in_array ($parent_entity_id, getLocationChildrenList ($child_entity_id))
1144 )
1145 throw new RackTablesError ("Circular reference for location ${parent_entity_id}", RackTablesError::INTERNAL);
1146
1147 // an object's container may not be one of its contained objects
1148 if
1149 (
1150 $parent_entity_type == 'object' &&
1151 $child_entity_type == 'object' &&
1152 in_array ($parent_entity_id, getObjectContentsList ($child_entity_id))
1153 )
1154 throw new RackTablesError ("Circular reference for object ${parent_entity_id}", RackTablesError::INTERNAL);
1155
1156 usePreparedInsertBlade
1157 (
1158 'EntityLink',
1159 array
1160 (
1161 'parent_entity_type' => $parent_entity_type,
1162 'parent_entity_id' => $parent_entity_id,
1163 'child_entity_type' => $child_entity_type,
1164 'child_entity_id' => $child_entity_id,
1165 )
1166 );
1167 }
1168
1169 function commitUpdateEntityLink
1170 (
1171 $old_parent_entity_type, $old_parent_entity_id, $old_child_entity_type, $old_child_entity_id,
1172 $new_parent_entity_type, $new_parent_entity_id, $new_child_entity_type, $new_child_entity_id
1173 )
1174 {
1175 // a location's parent may not be one of its children
1176 if
1177 (
1178 $new_parent_entity_type == 'location' &&
1179 $new_child_entity_type == 'location' &&
1180 in_array ($new_parent_entity_id, getLocationChildrenList ($new_child_entity_id))
1181 )
1182 throw new RackTablesError ("Circular reference for location ${new_parent_entity_id}", RackTablesError::INTERNAL);
1183
1184 // an object's container may not be one of its contained objects
1185 if
1186 (
1187 $new_parent_entity_type == 'object' &&
1188 $new_child_entity_type == 'object' &&
1189 in_array ($new_parent_entity_id, getObjectContentsList ($new_child_entity_id))
1190 )
1191 throw new RackTablesError ("Circular reference for object ${new_parent_entity_id}", RackTablesError::INTERNAL);
1192
1193 usePreparedUpdateBlade
1194 (
1195 'EntityLink',
1196 array
1197 (
1198 'parent_entity_type' => $new_parent_entity_type,
1199 'parent_entity_id' => $new_parent_entity_id,
1200 'child_entity_type' => $new_child_entity_type,
1201 'child_entity_id' => $new_child_entity_id
1202 ),
1203 array
1204 (
1205 'parent_entity_type' => $old_parent_entity_type,
1206 'parent_entity_id' => $old_parent_entity_id,
1207 'child_entity_type' => $old_child_entity_type,
1208 'child_entity_id' => $old_child_entity_id
1209 )
1210 );
1211 }
1212
1213 function commitUnlinkEntities ($parent_entity_type, $parent_entity_id, $child_entity_type, $child_entity_id)
1214 {
1215 usePreparedDeleteBlade
1216 (
1217 'EntityLink',
1218 array
1219 (
1220 'parent_entity_type' => $parent_entity_type,
1221 'parent_entity_id' => $parent_entity_id,
1222 'child_entity_type' => $child_entity_type,
1223 'child_entity_id' => $child_entity_id
1224 )
1225 );
1226 }
1227
1228 function commitUnlinkEntitiesByLinkID ($link_id)
1229 {
1230 usePreparedDeleteBlade ('EntityLink', array ('id' => $link_id));
1231 }
1232
1233 // return VM clusters and corresponding stats
1234 // - number of hypervisors
1235 // - number of resource pools
1236 // - number of VMs whose parent is the cluster itself
1237 // - number of VMs whose parent is one of the resource pools in the cluster
1238 function getVMClusterSummary ()
1239 {
1240 $query = <<<END
1241 SELECT
1242 O.id,
1243 O.name,
1244 (SELECT COUNT(*) FROM EntityLink EL
1245 LEFT JOIN Object O_H ON EL.child_entity_id = O_H.id
1246 LEFT JOIN AttributeValue AV ON O_H.id = AV.object_id
1247 WHERE EL.parent_entity_type = 'object'
1248 AND EL.child_entity_type = 'object'
1249 AND EL.parent_entity_id = O.id
1250 AND O_H.objtype_id = 4
1251 AND AV.attr_id = 26
1252 AND AV.uint_value = 1501) AS hypervisors,
1253 (SELECT COUNT(*) FROM EntityLink EL
1254 LEFT JOIN Object O_RP ON EL.child_entity_id = O_RP.id
1255 WHERE EL.parent_entity_type = 'object'
1256 AND EL.child_entity_type = 'object'
1257 AND EL.parent_entity_id = O.id
1258 AND O_RP.objtype_id = 1506) AS resource_pools,
1259 (SELECT COUNT(*) FROM EntityLink EL
1260 LEFT JOIN Object O_C_VM ON EL.child_entity_id = O_C_VM.id
1261 WHERE EL.parent_entity_type = 'object'
1262 AND EL.child_entity_type = 'object'
1263 AND EL.parent_entity_id = O.id
1264 AND O_C_VM.objtype_id = 1504) AS cluster_vms,
1265 (SELECT COUNT(*) FROM EntityLink EL
1266 LEFT JOIN Object O_RP_VM ON EL.child_entity_id = O_RP_VM.id
1267 WHERE EL.parent_entity_type = 'object'
1268 AND EL.child_entity_type = 'object'
1269 AND EL.parent_entity_id IN
1270 (SELECT child_entity_id FROM EntityLink EL
1271 LEFT JOIN Object O_RP ON EL.child_entity_id = O_RP.id
1272 WHERE EL.parent_entity_type = 'object'
1273 AND EL.child_entity_type = 'object'
1274 AND EL.parent_entity_id = O.id
1275 AND O_RP.objtype_id = 1506)
1276 AND O_RP_VM.objtype_id = 1504) AS resource_pool_vms
1277 FROM Object O
1278 WHERE O.objtype_id = 1505
1279 ORDER BY O.name
1280 END;
1281 $result = usePreparedSelectBlade ($query);
1282 return $result->fetchAll (PDO::FETCH_ASSOC);
1283 }
1284
1285 function getVMResourcePoolSummary ()
1286 {
1287 $result = usePreparedSelectBlade
1288 (
1289 "SELECT O.id, O.name, " .
1290 "(SELECT O_C.id " .
1291 "FROM EntityLink EL " .
1292 "LEFT JOIN Object O_C ON EL.parent_entity_id = O_C.id " .
1293 "WHERE EL.child_entity_id = O.id " .
1294 "AND EL.parent_entity_type = 'object' " .
1295 "AND EL.child_entity_type = 'object' " .
1296 "AND O_C.objtype_id = 1505 LIMIT 1) AS cluster_id, " .
1297 "(SELECT O_C.name " .
1298 "FROM EntityLink EL " .
1299 "LEFT JOIN Object O_C ON EL.parent_entity_id = O_C.id " .
1300 "WHERE EL.child_entity_id = O.id " .
1301 "AND EL.parent_entity_type = 'object' " .
1302 "AND EL.child_entity_type = 'object' " .
1303 "AND O_C.objtype_id = 1505 LIMIT 1) AS cluster_name, " .
1304 "(SELECT COUNT(*) FROM EntityLink EL " .
1305 "LEFT JOIN Object O_VM ON EL.child_entity_id = O_VM.id " .
1306 "WHERE EL.parent_entity_type = 'object' " .
1307 "AND EL.child_entity_type = 'object' " .
1308 "AND EL.parent_entity_id = O.id " .
1309 "AND O_VM.objtype_id = 1504) AS VMs " .
1310 "FROM Object O " .
1311 "WHERE O.objtype_id = 1506 " .
1312 "ORDER BY O.name"
1313 );
1314 return $result->fetchAll (PDO::FETCH_ASSOC);
1315 }
1316
1317 function getVMHypervisorSummary ()
1318 {
1319 $result = usePreparedSelectBlade
1320 (
1321 "SELECT O.id, O.name, " .
1322 "(SELECT O_C.id " .
1323 "FROM EntityLink EL " .
1324 "LEFT JOIN Object O_C ON EL.parent_entity_id = O_C.id " .
1325 "WHERE EL.child_entity_id = O.id " .
1326 "AND EL.parent_entity_type = 'object' " .
1327 "AND EL.child_entity_type = 'object' " .
1328 "AND O_C.objtype_id = 1505 LIMIT 1) AS cluster_id, " .
1329 "(SELECT O_C.name " .
1330 "FROM EntityLink EL " .
1331 "LEFT JOIN Object O_C ON EL.parent_entity_id = O_C.id " .
1332 "WHERE EL.child_entity_id = O.id " .
1333 "AND EL.parent_entity_type = 'object' " .
1334 "AND EL.child_entity_type = 'object' " .
1335 "AND O_C.objtype_id = 1505 LIMIT 1) AS cluster_name, " .
1336 "(SELECT COUNT(*) FROM EntityLink EL " .
1337 "LEFT JOIN Object O_VM ON EL.child_entity_id = O_VM.id " .
1338 "WHERE EL.parent_entity_type = 'object' " .
1339 "AND EL.child_entity_type = 'object' " .
1340 "AND EL.parent_entity_id = O.id " .
1341 "AND O_VM.objtype_id = 1504) AS VMs " .
1342 "FROM Object O " .
1343 "LEFT JOIN AttributeValue AV ON O.id = AV.object_id " .
1344 "WHERE O.objtype_id = 4 " .
1345 "AND AV.attr_id = 26 " .
1346 "AND AV.uint_value = 1501 " .
1347 "ORDER BY O.name"
1348 );
1349 return $result->fetchAll (PDO::FETCH_ASSOC);
1350 }
1351
1352 function getVMSwitchSummary ()
1353 {
1354 $result = usePreparedSelectBlade
1355 (
1356 "SELECT O.id, O.name " .
1357 "FROM Object O " .
1358 "WHERE O.objtype_id = 1507 " .
1359 "ORDER BY O.name"
1360 );
1361 return $result->fetchAll (PDO::FETCH_ASSOC);
1362 }
1363
1364 // Remove file links related to the entity, but leave the entity and file(s) intact.
1365 function releaseFiles ($entity_realm, $entity_id)
1366 {
1367 usePreparedDeleteBlade ('FileLink', array ('entity_type' => $entity_realm, 'entity_id' => $entity_id));
1368 }
1369
1370 // There are times when you want to delete all traces of an object
1371 function commitDeleteObject ($object_id)
1372 {
1373 // Reset most of stuff
1374 commitResetObject ($object_id);
1375 // Object itself
1376 usePreparedDeleteBlade ('Object', array ('id' => $object_id));
1377 // Dangling links
1378 usePreparedExecuteBlade
1379 (
1380 'DELETE FROM EntityLink WHERE ' .
1381 "(parent_entity_type IN ('rack', 'row', 'location') AND parent_entity_id = ?) OR " .
1382 "(child_entity_type IN ('rack', 'row', 'location') AND child_entity_id = ?)",
1383 array ($object_id, $object_id)
1384 );
1385 }
1386
1387 function commitResetObject ($object_id)
1388 {
1389 releaseFiles ('object', $object_id);
1390 destroyTagsForEntity ('object', $object_id);
1391 usePreparedDeleteBlade ('IPv4LB', array ('object_id' => $object_id));
1392 usePreparedDeleteBlade ('IPv4Allocation', array ('object_id' => $object_id));
1393 usePreparedDeleteBlade ('IPv6Allocation', array ('object_id' => $object_id));
1394 usePreparedDeleteBlade ('IPv4NAT', array ('object_id' => $object_id));
1395 // Parent-child relationships
1396 usePreparedExecuteBlade
1397 (
1398 'DELETE FROM EntityLink WHERE ' .
1399 "(parent_entity_type = 'object' AND parent_entity_id = ?) OR (child_entity_type = 'object' AND child_entity_id = ?)",
1400 array ($object_id, $object_id)
1401 );
1402 // Rack space
1403 usePreparedExecuteBlade ('DELETE FROM Atom WHERE molecule_id IN (SELECT new_molecule_id FROM MountOperation WHERE object_id = ?)', array ($object_id));
1404 usePreparedExecuteBlade ('DELETE FROM Molecule WHERE id IN (SELECT new_molecule_id FROM MountOperation WHERE object_id = ?)', array ($object_id));
1405 usePreparedDeleteBlade ('MountOperation', array ('object_id' => $object_id));
1406 usePreparedDeleteBlade ('RackSpace', array ('object_id' => $object_id));
1407 // 802.1Q
1408 usePreparedDeleteBlade ('PortVLANMode', array ('object_id' => $object_id));
1409 usePreparedDeleteBlade ('PortNativeVLAN', array ('object_id' => $object_id));
1410 usePreparedDeleteBlade ('PortAllowedVLAN', array ('object_id' => $object_id));
1411 usePreparedDeleteBlade ('CachedPVM', array ('object_id' => $object_id));
1412 usePreparedDeleteBlade ('VLANSwitch', array ('object_id' => $object_id));
1413 // SLB
1414 usePreparedDeleteBlade ('IPv4LB', array ('object_id' => $object_id));
1415 usePreparedDeleteBlade ('VSEnabledIPs', array ('object_id' => $object_id));
1416 usePreparedDeleteBlade ('VSEnabledPorts', array ('object_id' => $object_id));
1417 // Ports & links
1418 usePreparedDeleteBlade ('Port', array ('object_id' => $object_id));
1419 // CN
1420 usePreparedUpdateBlade ('Object', array ('name' => NULL, 'label' => ''), array ('id' => $object_id));
1421 // FQDN
1422 commitUpdateAttrValue ($object_id, 3, "");
1423 // log history
1424 recordObjectHistory ($object_id);
1425 # Cacti graphs
1426 usePreparedDeleteBlade ('CactiGraph', array ('object_id' => $object_id));
1427 # Munin graphs
1428 usePreparedDeleteBlade ('MuninGraph', array ('object_id' => $object_id));
1429 # Do an additional reset if needed
1430 callHook ('resetObject_hook', $object_id);
1431 }
1432
1433 function commitUpdateRack ($rack_id, $new_row_id, $new_name, $new_height, $new_has_problems, $new_asset_no, $new_comment)
1434 {
1435 // Can't shrink a rack if rows being deleted contain mounted objects
1436 $check_result = usePreparedSelectBlade ('SELECT COUNT(*) AS count FROM RackSpace WHERE rack_id = ? AND unit_no > ?', array ($rack_id, $new_height));
1437 $check_row = $check_result->fetch (PDO::FETCH_ASSOC);
1438 unset ($check_result);
1439 if ($check_row['count'] > 0)
1440 throw new InvalidArgException ('new_height', $new_height, 'Cannot shrink rack, objects are still mounted there');
1441
1442 // Determine if the row changed
1443 $old_rack = spotEntity ('rack', $rack_id);
1444 $old_row_id = $old_rack['row_id'];
1445 if ($old_row_id != $new_row_id)
1446 {
1447 // Move it to the specified row
1448 usePreparedUpdateBlade
1449 (
1450 'EntityLink',
1451 array ('parent_entity_id' => $new_row_id),
1452 array ('child_entity_type' => 'rack', 'child_entity_id' => $rack_id)
1453 );
1454
1455 // Set the sort_order attribute so it's placed at the end of the new row
1456 $rowInfo = getRowInfo ($new_row_id);
1457 usePreparedUpdateBlade
1458 (
1459 'AttributeValue',
1460 array ('uint_value' => $rowInfo['count']),
1461 array ('object_id' => $rack_id, 'attr_id' => 29)
1462 );
1463
1464 // Reset the sort order of the old row
1465 resetRackSortOrder ($old_row_id);
1466 }
1467
1468 // Update the height
1469 commitUpdateAttrValue ($rack_id, 27, $new_height);
1470
1471 // Update the rack
1472 commitUpdateObject ($rack_id, $new_name, NULL, $new_has_problems, $new_asset_no, $new_comment);
1473 }
1474
1475 // Unmount all objects from the rack
1476 function commitCleanRack ($rack_id)
1477 {
1478 $rack = spotEntity ('rack', $rack_id);
1479 foreach (getChildren ($rack, 'object') as $child)
1480 commitUnlinkEntities ('rack', $rack_id, 'object', $child['id']);
1481 usePreparedDeleteBlade ('RackSpace', array ('rack_id' => $rack_id));
1482 usePreparedDeleteBlade ('RackThumbnail', array ('rack_id' => $rack_id));
1483 }
1484
1485 // Drop the rack
1486 function commitDeleteRack ($rack_id)
1487 {
1488 $rack = spotEntity ('rack', $rack_id);
1489 releaseFiles ('rack', $rack_id);
1490 destroyTagsForEntity ('rack', $rack_id);
1491 usePreparedDeleteBlade ('RackSpace', array ('rack_id' => $rack_id));
1492 commitDeleteObject ($rack_id);
1493 resetRackSortOrder ($rack['row_id']);
1494 }
1495
1496 // Drop the row with all racks inside
1497 function commitDeleteRow ($row_id)
1498 {
1499 $racks = getRacks ($row_id);
1500 foreach ($racks as $rack)
1501 commitDeleteRack ($rack['id']);
1502 commitDeleteObject ($row_id);
1503 }
1504
1505 // Returns mounted devices count in all racks inside the specified row
1506 function getRowMountsCount ($row_id)
1507 {
1508 $query =<<<END
1509 SELECT COUNT(*) FROM (
1510 SELECT object_id FROM RackSpace rs LEFT JOIN EntityLink el ON (rs.rack_id = el.child_entity_id)
1511 WHERE
1512 rs.object_id IS NOT NULL AND
1513 el.parent_entity_id = ? AND el.parent_entity_type = "row" AND el.child_entity_type = "rack"
1514 UNION
1515 SELECT el1.child_entity_id object_id FROM EntityLink el1 LEFT JOIN EntityLink el2 ON (el1.parent_entity_id = el2.child_entity_id)
1516 WHERE
1517 el1.parent_entity_type = "rack" AND el1.child_entity_type = "object" AND
1518 el2.parent_entity_id = ? AND el2.parent_entity_type = "row" AND el2.child_entity_type = "rack"
1519 ) x
1520 END;
1521 $result = usePreparedSelectBlade ($query, array ($row_id, $row_id));
1522 return $result->fetchColumn();
1523 }
1524
1525 // Returns mounted devices count in specified rack
1526 function getRackMountsCount ($rack_id)
1527 {
1528 $query =<<<END
1529 SELECT COUNT(*) FROM (
1530 SELECT object_id FROM RackSpace WHERE object_id IS NOT NULL AND rack_id = ?
1531 UNION
1532 SELECT child_entity_id object_id FROM EntityLink WHERE
1533 parent_entity_id = ? AND parent_entity_type = "rack" AND child_entity_type = "object"
1534 ) x
1535 END;
1536 $result = usePreparedSelectBlade ($query, array ($rack_id, $rack_id));
1537 return $result->fetchColumn();
1538 }
1539
1540 // Used when sort order is manually changed, and when a rack is moved or deleted
1541 // Input is expected to be a pre-sorted array of rack IDs
1542 function updateRackSortOrder ($racks)
1543 {
1544 for ($i = 0; $i<count($racks); $i++)
1545 {
1546 usePreparedUpdateBlade
1547 (
1548 'AttributeValue',
1549 array ('uint_value' => $i+1),
1550 array ('object_id' => $racks[$i], 'attr_id' => 29)
1551 );
1552 }
1553 }
1554
1555 function resetRackSortOrder ($row_id)
1556 {
1557 // Re-order the row's racks
1558 $racks = getRacks($row_id);
1559 $rack_ids = array ();
1560 foreach ($racks as $rack_id => $rackDetails)
1561 $rack_ids[] = $rack_id;
1562 updateRackSortOrder ($rack_ids);
1563 }
1564
1565 // This function builds a list of rack-unit-atom records assigned to
1566 // the requested object.
1567 function getMoleculeForObject ($object_id)
1568 {
1569 $result = usePreparedSelectBlade
1570 (
1571 'SELECT rack_id, unit_no, atom FROM RackSpace ' .
1572 'WHERE state = "T" AND object_id = ? ORDER BY rack_id, unit_no, atom',
1573 array ($object_id)
1574 );
1575 return $result->fetchAll (PDO::FETCH_ASSOC);
1576 }
1577
1578 // This function builds a list of rack-unit-atom records for requested molecule.
1579 function getMolecule ($mid)
1580 {
1581 $result = usePreparedSelectBlade ('SELECT rack_id, unit_no, atom FROM Atom WHERE molecule_id = ?', array ($mid));
1582 return $result->fetchAll (PDO::FETCH_ASSOC);
1583 }
1584
1585 // returns exactly what is's named after
1586 function lastInsertID ()
1587 {
1588 $result = usePreparedSelectBlade ('select last_insert_id()');
1589 return $result->fetchColumn();
1590 }
1591
1592 // This function creates a new record in Molecule and number of linked
1593 // R-U-A records in Atom.
1594 function createMolecule ($molData)
1595 {
1596 usePreparedExecuteBlade ('INSERT INTO Molecule VALUES()');
1597 $molecule_id = lastInsertID();
1598 foreach ($molData as $rua)
1599 usePreparedInsertBlade
1600 (
1601 'Atom',
1602 array
1603 (
1604 'molecule_id' => $molecule_id,
1605 'rack_id' => $rua['rack_id'],
1606 'unit_no' => $rua['unit_no'],
1607 'atom' => $rua['atom'],
1608 )
1609 );
1610 return $molecule_id;
1611 }
1612
1613 // History logger. This function assumes certain table naming convention and
1614 // column design:
1615 // - History table must have the same row set (w/o keys) plus one row named
1616 // 'ctime' of type 'timestamp'.
1617 function recordObjectHistory ($object_id)
1618 {
1619 global $remote_username;
1620 usePreparedExecuteBlade
1621 (
1622 'INSERT INTO ObjectHistory ' .
1623 'SELECT id, name, label, objtype_id, asset_no, has_problems, comment, ' .
1624 'CURRENT_TIMESTAMP(), ? FROM Object WHERE id=?',
1625 array ($remote_username, $object_id)
1626 );
1627 }
1628
1629 function getRackspaceHistory ()
1630 {
1631 $result = usePreparedSelectBlade
1632 (
1633 "SELECT id as mo_id, object_id as ro_id, ctime, comment, user_name FROM " .
1634 "MountOperation ORDER BY ctime DESC"
1635 );
1636 return $result->fetchAll (PDO::FETCH_ASSOC);
1637 }
1638
1639 // This function is used in renderRackspaceHistory()
1640 function getOperationMolecules ($op_id)
1641 {
1642 $result = usePreparedSelectBlade ('SELECT old_molecule_id, new_molecule_id FROM MountOperation WHERE id = ?', array ($op_id));
1643 // We expect one row.
1644 $row = $result->fetch (PDO::FETCH_ASSOC);
1645 return array ($row['old_molecule_id'], $row['new_molecule_id']);
1646 }
1647
1648 function getResidentRacksData ($object_id = 0, $fetch_rackdata = TRUE)
1649 {
1650 $result = usePreparedSelectBlade
1651 (
1652 // Include racks that the object is directly mounted in
1653 "SELECT rack_id FROM RackSpace WHERE object_id = ? " .
1654 "UNION " .
1655 // Include racks that it's parent is mounted in
1656 "SELECT RS.rack_id FROM RackSpace RS INNER JOIN EntityLink EL ON RS.object_id = EL.parent_entity_id AND EL.parent_entity_type = 'object' WHERE EL.child_entity_id = ? AND EL.child_entity_type = 'object' " .
1657 "UNION " .
1658 // and racks that it is 'Zero-U' mounted in
1659 "SELECT parent_entity_id AS rack_id FROM EntityLink WHERE parent_entity_type = 'rack' AND child_entity_type = 'object' AND child_entity_id = ? " .
1660 'ORDER BY rack_id', array ($object_id, $object_id, $object_id)
1661 );
1662 $rows = $result->fetchAll (PDO::FETCH_NUM);
1663 unset ($result);
1664
1665 $ret = array();
1666 foreach ($rows as $row)
1667 if (! isset ($ret[$row[0]]))
1668 {
1669 if (!$fetch_rackdata)
1670 $rackData = $row[0];
1671 else
1672 {
1673 $rackData = spotEntity ('rack', $row[0]);
1674 amplifyCell ($rackData);
1675 }
1676 $ret[$row[0]] = $rackData;
1677 }
1678 return $ret;
1679 }
1680
1681 function commitAddPort ($object_id, $port_name, $port_type_id, $port_label, $port_l2address)
1682 {
1683 global $dbxlink;
1684 $db_l2address = l2addressForDatabase ($port_l2address);
1685 list ($iif_id, $oif_id) = parsePortIIFOIF ($port_type_id);
1686 // The conditional table locking is less relevant now due to syncObjectPorts().
1687 if ($db_l2address != '')
1688 $dbxlink->exec ('LOCK TABLES Port WRITE');
1689 try
1690 {
1691 assertUniqueL2Addresses (array ($db_l2address), $object_id);
1692 $ret = commitAddPortReal ($object_id, $port_name, $iif_id, $oif_id, $port_label, $db_l2address);
1693 }
1694 catch (Exception $e)
1695 {
1696 if ($db_l2address != '')
1697 $dbxlink->exec ('UNLOCK TABLES');
1698 throw $e;
1699 }
1700 if ($db_l2address != '')
1701 $dbxlink->exec ('UNLOCK TABLES');
1702 return $ret;
1703 }
1704
1705 // Having the call to assertUniqueL2Addresses() in this function would break things because
1706 // if the constraint check fails for any port the whole "transaction" needs to be rolled
1707 // back. Thus the calling function must call assertUniqueL2Addresses() for all involved ports
1708 // first and only then start making any calls to this function.
1709 function commitAddPortReal ($object_id, $port_name, $iif_id, $oif_id, $port_label, $db_l2address)
1710 {
1711 usePreparedInsertBlade
1712 (
1713 'Port',
1714 array
1715 (
1716 'name' => $port_name,
1717 'object_id' => $object_id,
1718 'label' => nullIfEmptyStr ($port_label),
1719 'iif_id' => $iif_id,
1720 'type' => $oif_id,
1721 'l2address' => nullIfEmptyStr ($db_l2address),
1722 )
1723 );
1724 lastCreated ('port', lastInsertID());
1725 return lastInsertID();
1726 }
1727
1728 function getPortReservationComment ($port_id, $extrasql = '')
1729 {
1730 $result = usePreparedSelectBlade ("SELECT reservation_comment FROM Port WHERE id = ? $extrasql", array ($port_id));
1731 return $result->fetchColumn();
1732 }
1733
1734 function commitUpdatePort ($object_id, $port_id, $port_name, $port_type_id, $port_label, $port_l2address, $port_reservation_comment)
1735 {
1736 global $dbxlink;
1737 $db_l2address = l2addressForDatabase ($port_l2address);
1738 list ($iif_id, $oif_id) = parsePortIIFOIF ($port_type_id);
1739 if ($db_l2address != '')
1740 $dbxlink->exec ('LOCK TABLES Port WRITE, PortLog WRITE');
1741 try
1742 {
1743 assertUniqueL2Addresses (array ($db_l2address), $object_id);
1744 commitUpdatePortReal ($object_id, $port_id, $port_name, $iif_id, $oif_id, $port_label, $db_l2address, $port_reservation_comment);
1745 }
1746 catch (Exception $e)
1747 {
1748 if ($db_l2address != '')
1749 $dbxlink->exec ('UNLOCK TABLES');
1750 throw $e;
1751 }
1752 if ($db_l2address != '')
1753 $dbxlink->exec ('UNLOCK TABLES');
1754 }
1755
1756 // The comment about commitAddPortReal() also applies here.
1757 function commitUpdatePortReal ($object_id, $port_id, $port_name, $iif_id, $oif_id, $port_label, $db_l2address, $port_reservation_comment)
1758 {
1759 $old_reservation_comment = getPortReservationComment ($port_id);
1760 $port_reservation_comment = nullIfEmptyStr ($port_reservation_comment);
1761 usePreparedUpdateBlade
1762 (
1763 'Port',
1764 array
1765 (
1766 'name' => $port_name,
1767 'iif_id' => $iif_id,
1768 'type' => $oif_id,
1769 'label' => nullIfEmptyStr ($port_label),
1770 'reservation_comment' => $port_reservation_comment,
1771 'l2address' => nullIfEmptyStr ($db_l2address),
1772 ),
1773 array
1774 (
1775 'id' => $port_id,
1776 'object_id' => $object_id
1777 )
1778 );
1779 if ($old_reservation_comment !== $port_reservation_comment)
1780 addPortLogEntry ($port_id, sprintf ("Reservation changed from '%s' to '%s'", $old_reservation_comment, $port_reservation_comment));
1781 }
1782
1783 function commitUpdatePortComment ($port_id, $port_reservation_comment)
1784 {
1785 global $dbxlink;
1786 $dbxlink->beginTransaction();
1787 $prev_comment = getPortReservationComment ($port_id, 'FOR UPDATE');
1788 $reservation_comment = nullIfEmptyStr ($port_reservation_comment);
1789 usePreparedUpdateBlade
1790 (
1791 'Port',
1792 array
1793 (
1794 'reservation_comment' => $reservation_comment,
1795 ),
1796 array
1797 (
1798 'id' => $port_id,
1799 )
1800 );
1801 if ($prev_comment !== $reservation_comment)
1802 addPortLogEntry ($port_id, sprintf ("Reservation changed from '%s' to '%s'", $prev_comment, $reservation_comment));
1803 $dbxlink->commit();
1804 }
1805
1806 function commitUpdatePortOIF ($port_id, $port_type_id)
1807 {
1808 usePreparedUpdateBlade
1809 (
1810 'Port',
1811 array ('type' => $port_type_id),
1812 array ('id' => $port_id)
1813 );
1814 }
1815
1816 function getAllIPv4Allocations ()
1817 {
1818 $result = usePreparedSelectBlade
1819 (
1820 "select object_id as object_id, ".
1821 "Object.name as object_name, ".
1822 "IPv4Allocation.name as name, ".
1823 "IPv4Allocation.type as type, ".
1824 "INET_NTOA(ip) as ip ".
1825 "from IPv4Allocation join Object on id=object_id "
1826 );
1827 return $result->fetchAll (PDO::FETCH_ASSOC);
1828 }
1829
1830 function linkPorts ($porta, $portb, $cable = NULL)
1831 {
1832 if ($porta == $portb)
1833 throw new InvalidArgException ('porta/portb', $porta, "Ports can't be the same");
1834
1835 $result = usePreparedSelectBlade
1836 (
1837 'SELECT COUNT(*) FROM Link WHERE porta IN (?,?) OR portb IN (?,?)',
1838 array ($porta, $portb, $porta, $portb)
1839 );
1840 if ($result->fetchColumn () != 0)
1841 throw new RTDatabaseError ("Port ${porta} or ${portb} is already linked");
1842 unset ($result);
1843
1844 $ret = usePreparedInsertBlade
1845 (
1846 'Link',
1847 array
1848 (
1849 'porta' => $porta,
1850 'portb' => $portb,
1851 'cable' => nullIfEmptyStr ($cable),
1852 )
1853 );
1854 usePreparedUpdateBlade ('Port', array ('reservation_comment' => NULL), array ('id' => array ($porta, $portb)));
1855
1856 // log new links
1857 $result = usePreparedSelectBlade
1858 (
1859 "SELECT Port.id, Port.name as port_name, Object.name as obj_name FROM Port " .
1860 "INNER JOIN Object ON Port.object_id = Object.id WHERE Port.id IN (?, ?)",
1861 array ($porta, $portb)
1862 );
1863 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1864 unset ($result);
1865 foreach ($rows as $row)
1866 {
1867 $pair_id = ($row['id'] == $porta ? $portb : $porta);
1868 addPortLogEntry ($pair_id, sprintf ("linked to %s %s", $row['obj_name'], $row['port_name']));
1869 }
1870 return $ret;
1871 }
1872
1873 function commitUpdatePortLink ($port_id, $cable = NULL)
1874 {
1875 return usePreparedUpdateBlade
1876 (
1877 'Link',
1878 array ('cable' => nullIfEmptyStr ($cable)),
1879 array ('porta' => $port_id, 'portb' => $port_id),
1880 'OR'
1881 );
1882 }
1883
1884 function commitUnlinkPort ($port_id)
1885 {
1886 // fetch and log existing link
1887 $result = usePreparedSelectBlade
1888 (
1889 "SELECT pa.id AS id_a, pa.name AS port_name_a, oa.name AS obj_name_a, " .
1890 "pb.id AS id_b, pb.name AS port_name_b, ob.name AS obj_name_b " .
1891 "FROM " .
1892 "Link INNER JOIN Port pa ON pa.id = Link.porta " .
1893 "INNER JOIN Port pb ON pb.id = Link.portb " .
1894 "INNER JOIN RackObject oa ON pa.object_id = oa.id " .
1895 "INNER JOIN RackObject ob ON pb.object_id = ob.id " .
1896 "WHERE " .
1897 "Link.porta = ? OR Link.portb = ?",
1898 array ($port_id, $port_id)
1899 );
1900 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1901 unset ($result);
1902 foreach ($rows as $row)
1903 {
1904 addPortLogEntry ($row['id_a'], sprintf ("unlinked from %s %s", $row['obj_name_b'], $row['port_name_b']));
1905 addPortLogEntry ($row['id_b'], sprintf ("unlinked from %s %s", $row['obj_name_a'], $row['port_name_a']));
1906 }
1907
1908 // remove existing link
1909 return usePreparedDeleteBlade ('Link', array ('porta' => $port_id, 'portb' => $port_id), 'OR');
1910 }
1911
1912 function addPortLogEntry ($port_id, $message)
1913 {
1914 global $disable_logging;
1915 if (isset ($disable_logging) && $disable_logging)
1916 return;
1917 global $remote_username;
1918 usePreparedExecuteBlade
1919 (
1920 "INSERT INTO PortLog (port_id, user, date, message) VALUES (?, ?, NOW(), ?)",
1921 array ($port_id, $remote_username, $message)
1922 );
1923 }
1924
1925 function addIPLogEntry ($ip_bin, $message)
1926 {
1927 switch (strlen ($ip_bin))
1928 {
1929 case 4: return addIPv4LogEntry ($ip_bin, $message);
1930 case 16: return addIPv6LogEntry ($ip_bin, $message);
1931 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
1932 }
1933 }
1934
1935 function addIPv4LogEntry ($ip_bin, $message)
1936 {
1937 global $disable_logging;
1938 if (isset ($disable_logging) && $disable_logging)
1939 return;
1940 global $remote_username;
1941 usePreparedExecuteBlade
1942 (
1943 "INSERT INTO IPv4Log (ip, date, user, message) VALUES (?, NOW(), ?, ?)",
1944 array (ip4_bin2db ($ip_bin), $remote_username, $message)
1945 );
1946 }
1947
1948 function addIPv6LogEntry ($ip_bin, $message)
1949 {
1950 global $disable_logging;
1951 if (isset ($disable_logging) && $disable_logging)
1952 return;
1953 global $remote_username;
1954 usePreparedExecuteBlade
1955 (
1956 "INSERT INTO IPv6Log (ip, date, user, message) VALUES (?, NOW(), ?, ?)",
1957 array ($ip_bin, $remote_username, $message)
1958 );
1959 }
1960
1961 function fetchIPLogEntry ($ip_bin)
1962 {
1963 switch (strlen ($ip_bin))
1964 {
1965 case 4: return fetchIPv4LogEntry ($ip_bin);
1966 case 16: return fetchIPv6LogEntry ($ip_bin);
1967 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
1968 }
1969 }
1970
1971 function fetchIPv4LogEntry ($ip_bin)
1972 {
1973 $result = usePreparedSelectBlade
1974 (
1975 "SELECT date, user, message FROM IPv4Log WHERE ip = ? ORDER BY date ASC",
1976 array (ip4_bin2db ($ip_bin))
1977 );
1978 return $result->fetchAll (PDO::FETCH_ASSOC);
1979 }
1980
1981 function fetchIPv6LogEntry ($ip_bin)
1982 {
1983 $result = usePreparedSelectBlade
1984 (
1985 "SELECT date, user, message FROM IPv6Log WHERE ip = ? ORDER BY date ASC",
1986 array ($ip_bin)
1987 );
1988 return $result->fetchAll (PDO::FETCH_ASSOC);
1989 }
1990
1991 // wrapper around getObjectIPv4AllocationList and getObjectIPv6AllocationList
1992 function getObjectIPAllocationList ($object_id)
1993 {
1994 return
1995 getObjectIPv4AllocationList ($object_id) +
1996 getObjectIPv6AllocationList ($object_id);
1997 }
1998
1999 // Returns all IPv4 addresses allocated to object, but does not attach detailed info about address
2000 // Used instead of getObjectIPv4Allocations if you need perfomance but 'addrinfo' value
2001 function getObjectIPv4AllocationList ($object_id)
2002 {
2003 $ret = array();
2004 $result = usePreparedSelectBlade
2005 (
2006 'SELECT name AS osif, type, ip FROM IPv4Allocation ' .
2007 'WHERE object_id = ?',
2008 array ($object_id)
2009 );
2010 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2011 $ret[ip4_int2bin ($row['ip'])] = array ('osif' => $row['osif'], 'type' => $row['type']);
2012 return $ret;
2013 }
2014
2015 // Returns all IPv6 addresses allocated to object, but does not attach detailed info about address
2016 // Used instead of getObjectIPv6Allocations if you need perfomance but 'addrinfo' value
2017 function getObjectIPv6AllocationList ($object_id)
2018 {
2019 $ret = array();
2020 $result = usePreparedSelectBlade
2021 (
2022 'SELECT name AS osif, type, ip AS ip FROM IPv6Allocation ' .
2023 'WHERE object_id = ?',
2024 array ($object_id)
2025 );
2026 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2027 $ret[$row['ip']] = array ('osif' => $row['osif'], 'type' => $row['type']);
2028 return $ret;
2029 }
2030
2031 // Return all IP addresses allocated to the object sorted by allocation name.
2032 // Attach detailed info about address to each alocation records.
2033 // Index result by binary ip
2034 function getObjectIPAllocations ($object_id)
2035 {
2036 return amplifyAllocationList (getObjectIPAllocationList ($object_id));
2037 }
2038 function getObjectIPv4Allocations ($object_id)
2039 {
2040 return amplifyAllocationList (getObjectIPv4AllocationList ($object_id));
2041 }
2042 function getObjectIPv6Allocations ($object_id)
2043 {
2044 return amplifyAllocationList (getObjectIPv6AllocationList ($object_id));
2045 }
2046
2047 function amplifyAllocationList ($alloc_list)
2048 {
2049 $ret = array();
2050 $sorted = array();
2051 foreach ($alloc_list as $ip_bin => $alloc)
2052 $sorted[$alloc['osif']][$ip_bin] = $alloc;
2053 foreach (sortPortList ($sorted) as $osif => $subarray)
2054 foreach ($subarray as $ip_bin => $alloc)
2055 {
2056 $alloc['addrinfo'] = getIPAddress ($ip_bin);
2057 $ret[$ip_bin] = $alloc;
2058 }
2059 return $ret;
2060 }
2061
2062 function scanIPNet ($net_info, $filter_flags = IPSCAN_ANY)
2063 {
2064 $pairlist = array(
2065 array('first' => $net_info['ip_bin'], 'last' => ip_last ($net_info))
2066 );
2067 return scanIPSpace ($pairlist, $filter_flags);
2068 }
2069
2070 function scanIPSpace ($pairlist, $filter_flags = IPSCAN_ANY)
2071 {
2072 $v4_pairs = array();
2073 $v6_pairs = array();
2074 foreach ($pairlist as $pair)
2075 {
2076 if (strlen ($pair['first']) == 4)
2077 $v4_pairs[] = $pair;
2078 elseif (strlen ($pair['first']) == 16)
2079 $v6_pairs[] = $pair;
2080 }
2081 return
2082 scanIPv4Space ($v4_pairs, $filter_flags) +
2083 scanIPv6Space ($v6_pairs, $filter_flags);
2084 }
2085
2086 // Check the range requested for meaningful IPv4 records, build them
2087 // into a list and return. Return an empty list if nothing matched.
2088 // Both arguments are expected in 4-byte binary string form. The resulting list
2089 // is keyed by 4-byte binary IPs, items aren't sorted.
2090 // LATER: accept a list of pairs and build WHERE sub-expression accordingly
2091 function scanIPv4Space ($pairlist, $filter_flags = IPSCAN_ANY)
2092 {
2093 $ret = array();
2094 if (!count ($pairlist)) // this is normal for a network completely divided into smaller parts
2095 return $ret;
2096 $pairlist = reduceIPPairList ($pairlist);
2097 // FIXME: this is a copy-and-paste prototype
2098 $or = '';
2099 $whereexpr1 = '(';
2100 $whereexpr2 = '(';
2101 $whereexpr3a = '(';
2102 $whereexpr3b = '(';
2103 $whereexpr4 = '(';
2104 $whereexpr5a = '(';
2105 $whereexpr5b = '(';
2106 $whereexpr6 = '(';
2107 $qparams = array();
2108 $qparams_bin = array();
2109 foreach ($pairlist as $tmp)
2110 {
2111 $whereexpr1 .= $or . "ip between ? and ?";
2112 $whereexpr2 .= $or . "ip between ? and ?";
2113 $whereexpr3a .= $or . "vip between ? and ?";
2114 $whereexpr3b .= $or . "vip between ? and ?";
2115 $whereexpr4 .= $or . "rsip between ? and ?";
2116 $whereexpr5a .= $or . "remoteip between ? and ?";
2117 $whereexpr5b .= $or . "localip between ? and ?";
2118 $whereexpr6 .= $or . "l.ip between ? and ?";
2119 $or = ' or ';
2120 $qparams[] = ip4_bin2db ($tmp['first']);
2121 $qparams[] = ip4_bin2db ($tmp['last']);
2122 $qparams_bin[] = $tmp['first'];
2123 $qparams_bin[] = $tmp['last'];
2124 }
2125 $whereexpr1 .= ')';
2126 $whereexpr2 .= ')';
2127 $whereexpr3a .= ')';
2128 $whereexpr3b .= ')';
2129 $whereexpr4 .= ')';
2130 $whereexpr5a .= ')';
2131 $whereexpr5b .= ')';
2132 $whereexpr6 .= ')';
2133
2134 // 1. collect labels and reservations
2135 if ($filter_flags & IPSCAN_DO_ADDR)
2136 {
2137 $query = "select ip, name, comment, reserved from IPv4Address ".
2138 "where ${whereexpr1} and (reserved = 'yes' or name != '' or comment != '')";
2139 $result = usePreparedSelectBlade ($query, $qparams);
2140 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2141 {
2142 $ip_bin = ip4_int2bin ($row['ip']);
2143 if (!isset ($ret[$ip_bin]))
2144 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2145 $ret[$ip_bin]['name'] = $row['name'];
2146 $ret[$ip_bin]['comment'] = $row['comment'];
2147 $ret[$ip_bin]['reserved'] = $row['reserved'];
2148 }
2149 unset ($result);
2150 }
2151
2152 // 2. check for allocations
2153 if ($filter_flags & IPSCAN_DO_ALLOCS)
2154 {
2155 if ($filter_flags & IPSCAN_RTR_ONLY)
2156 $whereexpr2 .= " AND ia.type = 'router'";
2157 $query =
2158 "select ia.ip, ia.object_id, ia.name, ia.type, Object.name as object_name " .
2159 "from IPv4Allocation AS ia INNER JOIN Object ON ia.object_id = Object.id where ${whereexpr2} order by ia.type";
2160 $result = usePreparedSelectBlade ($query, $qparams);
2161 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2162 {
2163 $ip_bin = ip4_int2bin ($row['ip']);
2164 if (!isset ($ret[$ip_bin]))
2165 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2166 $ret[$ip_bin]['allocs'][] = array
2167 (
2168 'type' => $row['type'],
2169 'name' => $row['name'],
2170 'object_id' => $row['object_id'],
2171 'object_name' => $row['object_name'],
2172 );
2173 }
2174 unset ($result);
2175 }
2176
2177 // 3a. look for virtual services
2178 if ($filter_flags & IPSCAN_DO_VS)
2179 {
2180 $query = "select id, vip from IPv4VS where ${whereexpr3a}";
2181 $result = usePreparedSelectBlade ($query, $qparams_bin);
2182 $allRows = $result->fetchAll (PDO::FETCH_ASSOC);
2183 unset ($result);
2184 foreach ($allRows as $row)
2185 {
2186 $ip_bin = $row['vip'];
2187 if (!isset ($ret[$ip_bin]))
2188 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2189 $ret[$ip_bin]['vslist'][] = $row['id'];
2190 }
2191
2192 // 3b. look for virtual service groups
2193 $query = "select vs_id, vip from VSIPs where ${whereexpr3b}";
2194 $result = usePreparedSelectBlade ($query, $qparams_bin);
2195 $allRows = $result->fetchAll (PDO::FETCH_ASSOC);
2196 unset ($result);
2197 foreach ($allRows as $row)
2198 {
2199 $ip_bin = $row['vip'];
2200 if (!isset ($ret[$ip_bin]))
2201 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2202 $ret[$ip_bin]['vsglist'][] = $row['vs_id'];
2203 }
2204 }
2205
2206 // 4. don't forget about real servers along with pools
2207 if ($filter_flags & IPSCAN_DO_RS)
2208 {
2209 $query = "select rsip, rspool_id from IPv4RS where ${whereexpr4}";
2210 $result = usePreparedSelectBlade ($query, $qparams_bin);
2211 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2212 {
2213 $ip_bin = $row['rsip'];
2214 if (!isset ($ret[$ip_bin]))
2215 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2216 $ret[$ip_bin]['rsplist'][] = $row['rspool_id'];
2217 }
2218 unset ($result);
2219 }
2220
2221 // 5. add NAT rules, remote ip
2222 if ($filter_flags & IPSCAN_DO_NAT)
2223 {
2224 $query =
2225 "select " .
2226 "proto, " .
2227 "localip, " .
2228 "localport, " .
2229 "remoteip, " .
2230 "remoteport, " .
2231 "description " .
2232 "from IPv4NAT " .
2233 "where ${whereexpr5a} " .
2234 "order by localip, localport, remoteip, remoteport, proto";
2235 $result = usePreparedSelectBlade ($query, $qparams);
2236 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2237 {
2238 $ip_bin_local = ip4_int2bin ($row['localip']);
2239 $ip_bin_remote = ip4_int2bin ($row['remoteip']);
2240 $row['localip_bin'] = $ip_bin_local;
2241 $row['remoteip_bin'] = $ip_bin_remote;
2242 $row['localip'] = ip_format ($ip_bin_local);
2243 $row['remoteip'] = ip_format ($ip_bin_remote);
2244 if (!isset ($ret[$ip_bin_remote]))
2245 $ret[$ip_bin_remote] = constructIPAddress ($ip_bin_remote);
2246 $ret[$ip_bin_remote]['inpf'][] = $row;
2247 }
2248 unset ($result);
2249 // 5. add NAT rules, local ip
2250 $query =
2251 "select " .
2252 "proto, " .
2253 "localip, " .
2254 "localport, " .
2255 "remoteip, " .
2256 "remoteport, " .
2257 "description " .
2258 "from IPv4NAT " .
2259 "where ${whereexpr5b} " .
2260 "order by localip, localport, remoteip, remoteport, proto";
2261 $result = usePreparedSelectBlade ($query, $qparams);
2262 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2263 {
2264 $ip_bin_local = ip4_int2bin ($row['localip']);
2265 $ip_bin_remote = ip4_int2bin ($row['remoteip']);
2266 $row['localip_bin'] = $ip_bin_local;
2267 $row['remoteip_bin'] = $ip_bin_remote;
2268 $row['localip'] = ip_format ($ip_bin_local);
2269 $row['remoteip'] = ip_format ($ip_bin_remote);
2270 if (!isset ($ret[$ip_bin_local]))
2271 $ret[$ip_bin_local] = constructIPAddress ($ip_bin_local);
2272 $ret[$ip_bin_local]['outpf'][] = $row;
2273 }
2274 unset ($result);
2275 }
2276
2277 // 6. collect last log message
2278 if ($filter_flags & IPSCAN_DO_LOG)
2279 {
2280 $query = "select l.ip, l.user, UNIX_TIMESTAMP(l.date) AS time from IPv4Log l INNER JOIN " .
2281 " (SELECT MAX(id) as id FROM IPv4Log GROUP BY ip) v USING (id) WHERE ${whereexpr6}";
2282 $result = usePreparedSelectBlade ($query, $qparams);
2283 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2284 {
2285 $ip_bin = ip4_int2bin ($row['ip']);
2286 if (isset ($ret[$ip_bin]))
2287 $ret[$ip_bin]['last_log'] = array
2288 (
2289 'user' => $row['user'],
2290 'time' => $row['time'],
2291 );
2292 }
2293 unset ($result);
2294 }
2295
2296 $override = callHook ('scanIPSpace_hook', $ret, $pairlist, $filter_flags);
2297 if (isset ($override))
2298 $ret = $override;
2299
2300 return $ret;
2301 }
2302
2303 // Check the range requested for meaningful IPv6 records, build them
2304 // into a list and return. Return an empty list if nothing matched.
2305 // Both arguments are expected as 16-byte binary IPs. The resulting list
2306 // is keyed by 16-byte bynary IPs, items aren't sorted.
2307 function scanIPv6Space ($pairlist, $filter_flags = IPSCAN_ANY)
2308 {
2309 $ret = array();
2310 if (!count ($pairlist)) // this is normal for a network completely divided into smaller parts
2311 return $ret;
2312 $pairlist = reduceIPPairList ($pairlist);
2313
2314 $or = '';
2315 $whereexpr1 = '(';
2316 $whereexpr2 = '(';
2317 $whereexpr3a = '(';
2318 $whereexpr3b = '(';
2319 $whereexpr4 = '(';
2320 $whereexpr6 = '(';
2321 $qparams = array();
2322 foreach ($pairlist as $tmp)
2323 {
2324 $whereexpr1 .= $or . "ip between ? and ?";
2325 $whereexpr2 .= $or . "ip between ? and ?";
2326 $whereexpr3a .= $or . "vip between ? and ?";
2327 $whereexpr3b .= $or . "vip between ? and ?";
2328 $whereexpr4 .= $or . "rsip between ? and ?";
2329 $whereexpr6 .= $or . "l.ip between ? and ?";
2330 $or = ' or ';
2331 $qparams[] = $tmp['first'];
2332 $qparams[] = $tmp['last'];
2333 }
2334 $whereexpr1 .= ')';
2335 $whereexpr2 .= ')';
2336 $whereexpr3a .= ')';
2337 $whereexpr3b .= ')';
2338 $whereexpr4 .= ')';
2339 $whereexpr6 .= ')';
2340
2341 // 1. collect labels and reservations
2342 if ($filter_flags & IPSCAN_DO_ADDR)
2343 {
2344 $query = "select ip, name, comment, reserved from IPv6Address ".
2345 "where ${whereexpr1} and (reserved = 'yes' or name != '' or comment != '')";
2346 $result = usePreparedSelectBlade ($query, $qparams);
2347 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2348 {
2349 $ip_bin = $row['ip'];
2350 if (!isset ($ret[$ip_bin]))
2351 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2352 $ret[$ip_bin]['name'] = $row['name'];
2353 $ret[$ip_bin]['comment'] = $row['comment'];
2354 $ret[$ip_bin]['reserved'] = $row['reserved'];
2355 }
2356 unset ($result);
2357 }
2358
2359 // 2. check for allocations
2360 if ($filter_flags & IPSCAN_DO_ALLOCS)
2361 {
2362 if ($filter_flags & IPSCAN_RTR_ONLY)
2363 $whereexpr2 .= " AND ia.type = 'router'";
2364 $query =
2365 "select ia.ip, ia.object_id, ia.name, ia.type, Object.name as object_name " .
2366 "from IPv6Allocation AS ia INNER JOIN Object ON ia.object_id = Object.id where ${whereexpr2} order by ia.type";
2367 $result = usePreparedSelectBlade ($query, $qparams);
2368 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2369 {
2370 $ip_bin = $row['ip'];
2371 if (!isset ($ret[$ip_bin]))
2372 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2373 $ret[$ip_bin]['allocs'][] = array
2374 (
2375 'type' => $row['type'],
2376 'name' => $row['name'],
2377 'object_id' => $row['object_id'],
2378 'object_name' => $row['object_name'],
2379 );
2380 }
2381 unset ($result);
2382 }
2383
2384 // 3a. look for virtual services
2385 if ($filter_flags & IPSCAN_DO_VS)
2386 {
2387 $query = "select id, vip from IPv4VS where ${whereexpr3a}";
2388 $result = usePreparedSelectBlade ($query, $qparams);
2389 $allRows = $result->fetchAll (PDO::FETCH_ASSOC);
2390 unset ($result);
2391 foreach ($allRows as $row)
2392 {
2393 $ip_bin = $row['vip'];
2394 if (!isset ($ret[$ip_bin]))
2395 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2396 $ret[$ip_bin]['vslist'][] = $row['id'];
2397 }
2398
2399 // 3b. look for virtual service groups
2400 $query = "select vs_id, vip from VSIPs where ${whereexpr3b}";
2401 $result = usePreparedSelectBlade ($query, $qparams);
2402 $allRows = $result->fetchAll (PDO::FETCH_ASSOC);
2403 unset ($result);
2404 foreach ($allRows as $row)
2405 {
2406 $ip_bin = $row['vip'];
2407 if (!isset ($ret[$ip_bin]))
2408 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2409 $ret[$ip_bin]['vsglist'][] = $row['vs_id'];
2410 }
2411 }
2412
2413 // 4. don't forget about real servers along with pools
2414 if ($filter_flags & IPSCAN_DO_RS)
2415 {
2416 $query = "select rsip, rspool_id from IPv4RS where ${whereexpr4}";
2417 $result = usePreparedSelectBlade ($query, $qparams);
2418 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2419 {
2420 $ip_bin = $row['rsip'];
2421 if (!isset ($ret[$ip_bin]))
2422 $ret[$ip_bin] = constructIPAddress ($ip_bin);
2423 $ret[$ip_bin]['rsplist'][] = $row['rspool_id'];
2424 }
2425 unset ($result);
2426 }
2427
2428 // 6. collect last log message
2429 if ($filter_flags & IPSCAN_DO_LOG)
2430 {
2431 $query = "select l.ip, l.user, UNIX_TIMESTAMP(l.date) AS time from IPv6Log l INNER JOIN " .
2432 " (SELECT MAX(id) as id FROM IPv6Log GROUP BY ip) v USING (id) WHERE ${whereexpr6}";
2433 $result = usePreparedSelectBlade ($query, $qparams);
2434 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2435 {
2436 $ip_bin = $row['ip'];
2437 if (isset ($ret[$ip_bin]))
2438 $ret[$ip_bin]['last_log'] = array
2439 (
2440 'user' => $row['user'],
2441 'time' => $row['time'],
2442 );
2443 }
2444 unset ($result);
2445 }
2446
2447 $override = callHook ('scanIPSpace_hook', $ret, $pairlist, $filter_flags);
2448 if (isset ($override))
2449 $ret = $override;
2450
2451 return $ret;
2452 }
2453
2454 function bindIPToObject ($ip_bin, $object_id = 0, $name = '', $type = '')
2455 {
2456 switch (strlen ($ip_bin))
2457 {
2458 case 4:
2459 $db_ip = ip4_bin2db ($ip_bin);
2460 $table = 'IPv4Allocation';
2461 $table2 = 'IPv4Address';
2462 break;
2463 case 16:
2464 $db_ip = $ip_bin;
2465 $table = 'IPv6Allocation';
2466 $table2 = 'IPv6Address';
2467 break;
2468 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2469 }
2470
2471 // release IP reservation and/or comment if configured
2472 $release = getConfigVar ('IPV4_AUTO_RELEASE');
2473 if ($release >= 2)
2474 usePreparedExecuteBlade ("DELETE FROM $table2 WHERE ip = ?", array ($db_ip));
2475 elseif ($release >= 1)
2476 usePreparedExecuteBlade ("UPDATE $table2 SET reserved = 'no' WHERE ip = ?", array ($db_ip));
2477
2478 usePreparedInsertBlade
2479 (
2480 $table,
2481 array ('ip' => $db_ip, 'object_id' => $object_id, 'name' => $name, 'type' => $type)
2482 );
2483 // store history line
2484 $cell = spotEntity ('object', $object_id);
2485 setDisplayedName ($cell);
2486 addIPLogEntry ($ip_bin, "Binded with ${cell['dname']}, ifname=$name");
2487 }
2488
2489 function bindIPv4ToObject ($ip_bin, $object_id = 0, $name = '', $type = '')
2490 {
2491 if (strlen ($ip_bin) != 4)
2492 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2493 return bindIPToObject ($ip_bin, $object_id, $name, $type);
2494 }
2495
2496 function bindIPv6ToObject ($ip_bin, $object_id = 0, $name = '', $type = '')
2497 {
2498 if (strlen ($ip_bin) != 16)
2499 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2500 return bindIPToObject ($ip_bin, $object_id, $name, $type);
2501 }
2502
2503 // Universal v4/v6 wrapper around getIPv4AddressNetworkId and getIPv6AddressNetworkId.
2504 // Return the id of the smallest IP network containing the given IP address
2505 // or NULL, if nothing was found. When finding the covering network for
2506 // another network, it is important to filter out matched records with longer
2507 // masks (they aren't going to be the right pick).
2508 function getIPAddressNetworkId ($ip_bin, $masklen = NULL)
2509 {
2510 switch (strlen ($ip_bin))
2511 {
2512 case 4: return getIPv4AddressNetworkId ($ip_bin, isset ($masklen) ? $masklen : 32);
2513 case 16: return getIPv6AddressNetworkId ($ip_bin, isset ($masklen) ? $masklen : 128);
2514 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2515 }
2516 }
2517
2518 // Returns ipv4net or ipv6net entity, or NULL if no spanning network found.
2519 // Throws an exception if $ip_bin is not valid binary address;
2520 function spotNetworkByIP ($ip_bin, $masklen = NULL)
2521 {
2522 $net_id = getIPAddressNetworkId ($ip_bin, $masklen);
2523 if (! $net_id)
2524 return NULL;
2525 switch (strlen ($ip_bin))
2526 {
2527 case 4: return spotEntity ('ipv4net', $net_id);
2528 case 16: return spotEntity ('ipv6net', $net_id);
2529 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2530 }
2531 }
2532
2533 // Return the id of the smallest IPv4 network containing the given IPv4 address
2534 // or NULL, if nothing was found. When finding the covering network for
2535 // another network, it is important to filter out matched records with longer
2536 // masks (they aren't going to be the right pick).
2537 function getIPv4AddressNetworkId ($ip_bin, $masklen = 32)
2538 {
2539 $row = callHook ('fetchIPv4AddressNetworkRow', $ip_bin, $masklen);
2540 return $row === NULL ? NULL : $row['id'];
2541 }
2542
2543 function fetchIPAddressNetworkRow ($ip_bin, $masklen = NULL)
2544 {
2545 switch (strlen ($ip_bin))
2546 {
2547 case 4:
2548 return callHook ('fetchIPv4AddressNetworkRow', $ip_bin, isset ($masklen) ? $masklen : 32);
2549 case 16:
2550 return callHook ('fetchIPv6AddressNetworkRow', $ip_bin, isset ($masklen) ? $masklen : 128);
2551 default:
2552 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary address");
2553 }
2554 }
2555
2556 function fetchIPv4AddressNetworkRow ($ip_bin, $masklen = 32)
2557 {
2558 $ip_db = ip4_bin2db ($ip_bin);
2559 $result = usePreparedSelectBlade
2560 (
2561 'SELECT id, ip, mask, name, comment FROM IPv4Network ' .
2562 'WHERE ? & (4294967295 >> (32 - mask)) << (32 - mask) = ip AND ip <= ? AND mask < ? ' .
2563 'ORDER BY mask DESC LIMIT 1',
2564 array ($ip_db, $ip_db, $masklen)
2565 );
2566 return nullIfFalse ($result->fetch (PDO::FETCH_ASSOC));
2567 }
2568
2569 // Return the id of the smallest IPv6 network containing the given IPv6 address
2570 // ($ip is an instance of IPv4Address class) or NULL, if nothing was found.
2571 function getIPv6AddressNetworkId ($ip_bin, $masklen = 128)
2572 {
2573 $row = callHook ('fetchIPv6AddressNetworkRow', $ip_bin, $masklen);
2574 return $row === NULL ? NULL : $row['id'];
2575 }
2576
2577 function fetchIPv6AddressNetworkRow ($ip_bin, $masklen = 128)
2578 {
2579 $result = usePreparedSelectBlade
2580 (
2581 'SELECT id, ip, mask, last_ip, name, comment FROM IPv6Network ' .
2582 'WHERE ip <= ? AND last_ip >= ? AND mask < ? ' .
2583 'ORDER BY mask DESC LIMIT 1',
2584 array ($ip_bin, $ip_bin, $masklen)
2585 );
2586 return nullIfFalse ($result->fetch (PDO::FETCH_ASSOC));
2587 }
2588
2589 // This function is actually used not only to update, but also to create records,
2590 // that's why ON DUPLICATE KEY UPDATE was replaced by DELETE-INSERT pair
2591 // (MySQL 4.0 workaround).
2592 function updateAddress ($ip_bin, $name = '', $reserved = 'no', $comment)
2593 {
2594 switch (strlen ($ip_bin))
2595 {
2596 case 4:
2597 $table = 'IPv4Address';
2598 $db_ip = ip4_bin2db ($ip_bin);
2599 break;
2600 case 16:
2601 $table = 'IPv6Address';
2602 $db_ip = $ip_bin;
2603 break;
2604 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2605 }
2606
2607 // compute update log message
2608 $result = usePreparedSelectBlade ("SELECT name, comment, reserved FROM $table WHERE ip = ?", array ($db_ip));
2609 $old_name = '';
2610 $old_comment = '';
2611 if ($row = $result->fetch (PDO::FETCH_ASSOC))
2612 {
2613 $old_name = $row['name'];
2614 $old_comment = $row['comment'];
2615 }
2616
2617 // If the 'comment' argument was specified when this function was called, use it.
2618 // If not, retain the old value.
2619 $comment = (func_num_args () == 4 ) ? $comment : $old_comment;
2620 $new_row = array ('name' => $name, 'comment' => $comment, 'reserved' => $reserved);
2621 $new_row_empty = $name == '' && $comment == '' && $reserved == 'no';
2622
2623 unset ($result);
2624 $messages = array ();
2625 if ($name != $old_name)
2626 {
2627 if ($name == '')
2628 $messages[] = "name '$old_name' removed";
2629 elseif ($old_name == '')
2630 $messages[] = "name set to '$name'";
2631 else
2632 $messages[] = "name changed from '$old_name' to '$name'";
2633 }
2634 if ($comment != $old_comment)
2635 {
2636 if ($comment == '')
2637 $messages[] = "comment '$old_comment' removed";
2638 elseif ($old_name == '')
2639 $messages[] = "comment set to '$comment'";
2640 else
2641 $messages[] = "comment changed from '$old_comment' to '$comment'";
2642 }
2643
2644 if ($row && ! $new_row_empty && $row == $new_row)
2645 return;
2646 if ($row)
2647 usePreparedDeleteBlade ($table, array ('ip' => $db_ip));
2648 // INSERT may appear not necessary.
2649 if (! $new_row_empty)
2650 usePreparedInsertBlade
2651 (
2652 $table,
2653 array ('name' => $name, 'comment' => $comment, 'reserved' => $reserved, 'ip' => $db_ip)
2654 );
2655 // store history line
2656 if ($messages)
2657 addIPLogEntry ($ip_bin, ucfirst (implode (', ', $messages)));
2658 }
2659
2660 function updateV4Address ($ip_bin, $name = '', $reserved = 'no', $comment = '')
2661 {
2662 if (strlen ($ip_bin) != 4)
2663 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2664 return updateAddress ($ip_bin, $name, $reserved, $comment);
2665 }
2666
2667 function updateV6Address ($ip_bin, $name = '', $reserved = 'no', $comment = '')
2668 {
2669 if (strlen ($ip_bin) != 16)
2670 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2671 return updateAddress ($ip_bin, $name, $reserved, $comment);
2672 }
2673
2674 function updateIPBond ($ip_bin, $object_id=0, $name='', $type='')
2675 {
2676 switch (strlen ($ip_bin))
2677 {
2678 case 4: return updateIPv4Bond ($ip_bin, $object_id, $name, $type);
2679 case 16: return updateIPv6Bond ($ip_bin, $object_id, $name, $type);
2680 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2681 }
2682 }
2683
2684 function updateIPv4Bond ($ip_bin, $object_id=0, $name='', $type='')
2685 {
2686 usePreparedUpdateBlade
2687 (
2688 'IPv4Allocation',
2689 array
2690 (
2691 'name' => $name,
2692 'type' => $type,
2693 ),
2694 array
2695 (
2696 'ip' => ip4_bin2db ($ip_bin),
2697 'object_id' => $object_id,
2698 )
2699 );
2700 }
2701
2702 function updateIPv6Bond ($ip_bin, $object_id=0, $name='', $type='')
2703 {
2704 usePreparedUpdateBlade
2705 (
2706 'IPv6Allocation',
2707 array
2708 (
2709 'name' => $name,
2710 'type' => $type,
2711 ),
2712 array
2713 (
2714 'ip' => $ip_bin,
2715 'object_id' => $object_id,
2716 )
2717 );
2718 }
2719
2720
2721 function unbindIPFromObject ($ip_bin, $object_id)
2722 {
2723 switch (strlen ($ip_bin))
2724 {
2725 case 4:
2726 $table = 'IPv4Allocation';
2727 $db_ip = ip4_bin2db ($ip_bin);
2728 break;
2729 case 16:
2730 $table = 'IPv6Allocation';
2731 $db_ip = $ip_bin;
2732 break;
2733 default: throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2734 }
2735
2736 $n_deleted = usePreparedDeleteBlade
2737 (
2738 $table,
2739 array ('ip' => $db_ip, 'object_id' => $object_id)
2740 );
2741 if ($n_deleted)
2742 {
2743 // store history line
2744 $cell = spotEntity ('object', $object_id);
2745 setDisplayedName ($cell);
2746 addIPLogEntry ($ip_bin, "Removed from ${cell['dname']}");
2747 }
2748 }
2749
2750 function unbindIPv4FromObject ($ip_bin, $object_id)
2751 {
2752 if (strlen ($ip_bin) != 4)
2753 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2754 return unbindIPFromObject ($ip_bin, $object_id);
2755 }
2756
2757 function unbindIPv6FromObject ($ip_bin, $object_id)
2758 {
2759 if (strlen ($ip_bin) != 16)
2760 throw new InvalidArgException ('ip_bin', $ip_bin, "Invalid binary IP");
2761 return unbindIPFromObject ($ip_bin, $object_id);
2762 }
2763
2764 function getIPv4PrefixSearchResult ($terms)
2765 {
2766 $byname = getSearchResultByField
2767 (
2768 'IPv4Network',
2769 array ('id'),
2770 'name',
2771 $terms,
2772 'ip'
2773 );
2774 $ret = array();
2775 foreach ($byname as $row)
2776 $ret[$row['id']] = spotEntity ('ipv4net', $row['id']);
2777 return $ret;
2778 }
2779
2780 function getIPv6PrefixSearchResult ($terms)
2781 {
2782 $byname = getSearchResultByField
2783 (
2784 'IPv6Network',
2785 array ('id'),
2786 'name',
2787 $terms,
2788 'ip'
2789 );
2790 $ret = array();
2791 foreach ($byname as $row)
2792 $ret[$row['id']] = spotEntity ('ipv6net', $row['id']);
2793 return $ret;
2794 }
2795
2796 function getIPv4AddressSearchResult ($terms)
2797 {
2798 $query = "select ip, name, comment from IPv4Address where ";
2799 $or = '';
2800 $qparams = array();
2801 foreach (explode (' ', $terms) as $term)
2802 {
2803 $query .= $or . "name like ? or comment like ?";
2804 $or = ' or ';
2805 $qparams[] = "%${term}%";
2806 $qparams[] = "%${term}%";
2807 }
2808 $result = usePreparedSelectBlade ($query, $qparams);
2809 $ret = array();
2810 while ($row = $result->fetch (PDO::FETCH_ASSOC))
2811 {
2812 $ip_bin = ip4_int2bin ($row['ip']);
2813 $row['ip'] = $ip_bin;
2814 $ret[$ip_bin] = $row;
2815 }
2816 return $ret;
2817 }
2818
2819 function getIPv6AddressSearchResult ($terms)
2820 {
2821 $query = "select ip, name, comment from IPv6Address where ";
2822 $or = '';
2823 $qparams = array();
2824 foreach (explode (' ', $terms) as $term)
2825 {
2826 $query .= $or . "name like ? or comment like ?";
2827 $or = ' or ';
2828 $qparams[] = "%${term}%";
2829 $qparams[] = "%${term}%";
2830 }
2831 $result = usePreparedSelectBlade ($query, $qparams);
2832 return reindexById ($result->fetchAll (PDO::FETCH_ASSOC), 'ip');
2833 }
2834
2835 function getIPv4RSPoolSearchResult ($terms)
2836 {
2837 $byname = getSearchResultByField
2838 (
2839 'IPv4RSPool',
2840 array ('id'),
2841 'name',
2842 $terms,
2843 'name'
2844 );
2845 $ret = array();
2846 foreach ($byname as $row)
2847 $ret[$row['id']] = spotEntity ('ipv4rspool', $row['id']);
2848 return $ret;
2849 }
2850
2851 function getIPv4VServiceSearchResult ($terms)
2852 {
2853 $byname = getSearchResultByField
2854 (
2855 'IPv4VS',
2856 array ('id'),
2857 'name',
2858 $terms,
2859 'vip'
2860 );
2861 $ret = array();
2862 foreach ($byname as $row)
2863 $ret[$row['id']] = spotEntity ('ipv4vs', $row['id']);
2864 return $ret;
2865 }
2866
2867 function getVServiceSearchResult ($terms)
2868 {
2869 $byname = getSearchResultByField
2870 (
2871 'VS',
2872 array ('id'),
2873 'name',
2874 $terms,
2875 'name'
2876 );
2877 $ret = array();
2878 foreach ($byname as $row)
2879 $ret[$row['id']] = spotEntity ('ipvs', $row['id']);
2880 return $ret;
2881 }
2882
2883 function getAccountSearchResult ($terms)
2884 {
2885 $byUsername = getSearchResultByField
2886 (
2887 'UserAccount',
2888 array ('user_id', 'user_name', 'user_realname'),
2889 'user_name',
2890 $terms,
2891 'user_name'
2892 );
2893 $byRealname = getSearchResultByField
2894 (
2895 'UserAccount',
2896 array ('user_id', 'user_name', 'user_realname'),
2897 'user_realname',
2898 $terms,
2899 'user_name'
2900 );
2901 // Merge it together, if duplicates persist, byUsername wins
2902 $ret = array();
2903 foreach (array ($byRealname, $byUsername) as $array)
2904 foreach ($array as $user)
2905 {
2906 $user['realm'] = 'user';
2907 $user['id'] = $user['user_id'];
2908 $ret[$user['user_id']] = $user;
2909 }
2910 return $ret;
2911 }
2912
2913 function getFileSearchResult ($terms)
2914 {
2915 $byName = getSearchResultByField
2916 (
2917 'File',
2918 array ('id'),
2919 'name',
2920 $terms,
2921 'name'
2922 );
2923 $byComment = getSearchResultByField
2924 (
2925 'File',
2926 array ('id'),
2927 'comment',
2928 $terms,
2929 'name'
2930 );
2931 // Filter out dupes.
2932 foreach ($byName as $res1)
2933 foreach (array_keys ($byComment) as $key2)
2934 if ($res1['id'] == $byComment[$key2]['id'])
2935 {
2936 unset ($byComment[$key2]);
2937 continue 2;
2938 }
2939 $ret = array();
2940 foreach (array_merge ($byName, $byComment) as $row)
2941 $ret[$row['id']] = spotEntity ('file', $row['id']);
2942 return $ret;
2943 }
2944
2945 function getRackSearchResult ($terms)
2946 {
2947 $byName = getSearchResultByField
2948 (
2949 'Rack',
2950 array ('id'),
2951 'name',
2952 $terms,
2953 'name'
2954 );
2955 $byComment = getSearchResultByField
2956 (
2957 'Rack',
2958 array ('id'),
2959 'comment',
2960 $terms,
2961 'name'
2962 );
2963 $byAssetNo = getSearchResultByField
2964 (
2965 'Rack',
2966 array ('id'),
2967 'asset_no',
2968 $terms,
2969 'name'
2970 );
2971 $bySticker = getStickerSearchResults ('Rack', $terms);
2972 // Filter out dupes.
2973 foreach ($byName as $res1)
2974 {
2975 foreach (array_keys ($byComment) as $key2)
2976 if ($res1['id'] == $byComment[$key2]['id'])
2977 unset ($byComment[$key2]);
2978 foreach (array_keys ($byAssetNo) as $key3)
2979 if ($res1['id'] == $byAssetNo[$key3]['id'])
2980 unset ($byAssetNo[$key3]);
2981 foreach (array_keys ($bySticker) as $key4)
2982 if ($res1['id'] == $bySticker[$key4]['id'])
2983 unset ($bySticker[$key4]);
2984 }
2985 $ret = array();
2986 foreach (array_merge ($byName, $byComment, $byAssetNo, $bySticker) as $row)
2987 $ret[$row['id']] = spotEntity ('rack', $row['id']);
2988 return $ret;
2989 }
2990
2991 function getRowSearchResult ($terms)
2992 {
2993 $byName = getSearchResultByField
2994 (
2995 'Row',
2996 array ('id'),
2997 'name',
2998 $terms,
2999 'name'
3000 );
3001
3002 $ret = array();
3003 foreach ($byName as $row)
3004 $ret[$row['id']] = spotEntity ('row', $row['id']);
3005 return $ret;
3006 }
3007
3008 function getLocationSearchResult ($terms)
3009 {
3010 $byName = getSearchResultByField
3011 (
3012 'Location',
3013 array ('id'),
3014 'name',
3015 $terms,
3016 'name'
3017 );
3018 $byComment = getSearchResultByField
3019 (
3020 'Location',
3021 array ('id'),
3022 'comment',
3023 $terms,
3024 'name'
3025 );
3026 $bySticker = getStickerSearchResults ('Location', $terms);
3027 // Filter out dupes.
3028 foreach ($byName as $res1)
3029 {
3030 foreach (array_keys ($byComment) as $key2)
3031 if ($res1['id'] == $byComment[$key2]['id'])
3032 unset ($byComment[$key2]);
3033 foreach (array_keys ($bySticker) as $key3)
3034 if ($res1['id'] == $bySticker[$key3]['id'])
3035 unset ($bySticker[$key3]);
3036 }
3037 $ret = array();
3038 foreach (array_merge ($byName, $byComment, $bySticker) as $location)
3039 $ret[$location['id']] = spotEntity ('location', $location['id']);
3040 return $ret;
3041 }
3042
3043 function getVLANSearchResult ($terms)
3044 {
3045 $ret = array();
3046 $byDescr = getSearchResultByField
3047 (
3048 'VLANDescription',
3049 array ('domain_id', 'vlan_id'),
3050 'vlan_descr',
3051 $terms
3052 );
3053 foreach ($byDescr as $row)
3054 {
3055 $vlan_ck = $row['domain_id'] . '-' . $row['vlan_id'];
3056 $row['id'] = $vlan_ck;
3057 $ret[$vlan_ck] = $row;
3058 }
3059 return $ret;
3060 }
3061
3062 function getSearchResultByField ($tablename, $retcolumns, $scancolumn, $terms, $ordercolumn = '', $exactness = 0)
3063 {
3064 $query = 'SELECT ' . implode (', ', $retcolumns) . " FROM ${tablename} WHERE ";
3065 $qparams = array();
3066 $pfx = '';
3067 $pterms = $exactness == 3 ? explode (' ', $terms) : parseSearchTerms ($terms);
3068 foreach ($pterms as $term)
3069 {
3070 switch ($exactness)
3071 {
3072 case 3:
3073 $query .= $pfx . "${scancolumn} REGEXP ?";
3074 $qparams[] = $term;
3075 break;
3076 case 2: // does this work as expected?
3077 $query .= $pfx . "BINARY ${scancolumn} = ?";
3078 $qparams[] = $term;
3079 break;
3080 case 1:
3081 $query .= $pfx . "${scancolumn} = ?";
3082 $qparams[] = $term;
3083 break;
3084 default:
3085 $query .= $pfx . "${scancolumn} LIKE ?";
3086 $qparams[] = "%${term}%";
3087 break;
3088 }
3089 $pfx = ' OR ';
3090 }
3091 if ($ordercolumn != '')
3092 $query .= " ORDER BY ${ordercolumn}";
3093 $result = usePreparedSelectBlade ($query, $qparams);
3094 return $result->fetchAll (PDO::FETCH_ASSOC);
3095 }
3096
3097 function getObjectSearchResults ($what)
3098 {
3099 $ret = array();
3100 global $searchfunc;
3101 foreach ($searchfunc['object'] as $method => $func)
3102 foreach ($func ($what) as $objRecord)
3103 {
3104 $ret[$objRecord['id']]['id'] = $objRecord['id'];
3105 $ret[$objRecord['id']][$method] = $objRecord[$method];
3106 }
3107 foreach (getStickerSearchResults ('RackObject', $what) as $objRecord)
3108 {
3109 $ret[$objRecord['id']]['id'] = $objRecord['id'];
3110 $ret[$objRecord['id']]['by_sticker'] = $objRecord['by_sticker'];
3111 }
3112 return $ret;
3113 }
3114
3115 function getObjectAttrsSearchResults ($what)
3116 {
3117 $ret = array();
3118 foreach (array ('name', 'label', 'asset_no', 'comment') as $column)
3119 {
3120 $tmp = getSearchResultByField
3121 (
3122 'RackObject',
3123 array ('id'),
3124 $column,
3125 $what,
3126 $column
3127 );
3128 foreach ($tmp as $row)
3129 {
3130 $ret[$row['id']]['id'] = $row['id'];
3131 $ret[$row['id']]['by_attr'][] = $column;
3132 }
3133 }
3134 return $ret;
3135 }
3136
3137 // Search stickers and return a list of pairs "object_id-attribute_id"
3138 // that matched. A partilar object_id could be returned more than once, if it has
3139 // multiple matching stickers. Search is only performed on "string" or "dict" attributes.
3140 function getStickerSearchResults ($tablename, $what)
3141 {
3142 $attr_types = array();
3143 $result = usePreparedSelectBlade
3144 (
3145 'SELECT AV.object_id, AV.attr_id FROM AttributeValue AV ' .
3146 "INNER JOIN ${tablename} O ON AV.object_id = O.id " .
3147 'INNER JOIN Attribute A ON AV.attr_id = A.id ' .
3148 'LEFT JOIN AttributeMap AM ON A.type = "dict" AND AV.object_tid = AM.objtype_id AND AV.attr_id = AM.attr_id ' .
3149 'LEFT JOIN Dictionary D ON AM.chapter_id = D.chapter_id AND AV.uint_value = D.dict_key ' .
3150 'WHERE string_value LIKE ? ' .
3151 'OR (A.type = "dict" AND dict_value LIKE ?) ORDER BY object_id',
3152 array ("%${what}%", "%${what}%")
3153 );
3154 $ret = array ();
3155 while ($row = $result->fetch (PDO::FETCH_ASSOC))
3156 {
3157 if (! array_key_exists ($row['attr_id'], $attr_types))
3158 $attr_types[$row['attr_id']] = getAttrType ($row['attr_id']);
3159 if (in_array ($attr_types[$row['attr_id']], array ('string', 'dict')))
3160 {
3161 $ret[$row['object_id']]['id'] = $row['object_id'];
3162 $ret[$row['object_id']]['by_sticker'][] = $row['attr_id'];
3163 }
3164 }
3165 return $ret;
3166 }
3167
3168 // search in port "reservation comment", "label" and "L2 address" columns
3169 function getPortSearchResults ($what)
3170 {
3171 $ret = array();
3172 $ports = getSearchResultByField
3173 (
3174 'Port',
3175 array ('object_id', 'id', 'reservation_comment'),
3176 'reservation_comment',
3177 $what,
3178 'object_id',
3179 0
3180 );
3181 foreach ($ports as $port)
3182 {
3183 $ret[$port['object_id']]['id'] = $port['object_id'];
3184 $ret[$port['object_id']]['by_port'][$port['id']] = $port['reservation_comment'];
3185 }
3186 $ports = getSearchResultByField
3187 (
3188 'Port',
3189 array ('object_id', 'id', 'label'),
3190 'label',
3191 $what,
3192 'object_id',
3193 0
3194 );
3195 foreach ($ports as $port)
3196 {
3197 $ret[$port['object_id']]['id'] = $port['object_id'];
3198 $ret[$port['object_id']]['by_port'][$port['id']] = $port['label'];
3199 }
3200 try
3201 {
3202 $db_l2address = l2addressForDatabase ($what);
3203 }
3204 catch (InvalidArgException $e)
3205 {
3206 return $ret;
3207 }
3208 $ports = getSearchResultByField
3209 (
3210 'Port',
3211 array ('object_id', 'id', 'l2address'),
3212 'l2address',
3213 $db_l2address,
3214 'object_id',
3215 2
3216 );
3217 foreach ($ports as $port)
3218 {
3219 $ret[$port['object_id']]['id'] = $port['object_id'];
3220 $ret[$port['object_id']]['by_port'][$port['id']] = $port['l2address'];
3221 }
3222 return $ret;
3223 }
3224
3225 // search in IPv4 allocations
3226 function getObjectIfacesSearchResults ($what)
3227 {
3228 $ret = array();
3229 $ifaces4 = getSearchResultByField
3230 (
3231 'IPv4Allocation',
3232 array ('object_id', 'name'),
3233 'name',
3234 $what,
3235 'object_id'
3236 );
3237 $ifaces6 = getSearchResultByField
3238 (
3239 'IPv6Allocation',
3240 array ('object_id', 'name'),
3241 'name',
3242 $what,
3243 'object_id'
3244 );
3245 foreach (array_merge ($ifaces4, $ifaces6) as $row)
3246 {
3247 $ret[$row['object_id']]['id'] = $row['object_id'];
3248 $ret[$row['object_id']]['by_iface'][] = $row['name'];
3249 }
3250 return $ret;
3251 }
3252
3253 function getObjectNATSearchResults ($what)
3254 {
3255 $ret = array();
3256 $ifaces = getSearchResultByField
3257 (
3258 'IPv4NAT',
3259 array ('object_id', 'description'),
3260 'description',
3261 $what,
3262 'object_id'
3263 );
3264 foreach ($ifaces as $row)
3265 {
3266 $ret[$row['object_id']]['id'] = $row['object_id'];
3267 $ret[$row['object_id']]['by_nat'][] = $row['description'];
3268 }
3269 return $ret;
3270 }
3271
3272 function searchCableIDs ($what)
3273 {
3274 $ret = array();
3275 $result = usePreparedSelectBlade
3276 (
3277 'SELECT object_id, cable ' .
3278 'FROM Link INNER JOIN Port ON porta = Port.id OR portb = Port.id ' .
3279 'WHERE cable LIKE ? ORDER BY object_id',
3280 array ("%${what}%")
3281 );
3282 while ($row = $result->fetch (PDO::FETCH_ASSOC))
3283 {
3284 $ret[$row['object_id']]['id'] = $row['object_id'];
3285 $ret[$row['object_id']]['by_cableid'][] = $row['cable'];
3286 }
3287 return $ret;
3288 }
3289
3290 // This function returns either port ID or NULL for specified arguments.
3291 function getPortIDs ($object_id, $port_name)
3292 {
3293 $result = usePreparedSelectBlade ('SELECT id FROM Port WHERE object_id = ? AND name = ?', array ($object_id, $port_name));
3294 return reduceSubarraysToColumn ($result->fetchAll (PDO::FETCH_ASSOC), 'id');
3295 }
3296
3297 // Search in "FQDN" attribute only, and return object ID, when there is exactly
3298 // one result found (and NULL in any other case).
3299 function searchByMgmtHostname ($string)
3300 {
3301 $result = usePreparedSelectBlade ('SELECT object_id FROM AttributeValue WHERE attr_id = 3 AND string_value = ? LIMIT 2', array ($string));
3302 $rows = $result->fetchAll (PDO::FETCH_NUM);
3303 if (count ($rows) == 1)
3304 return $rows[0][0];
3305 unset ($result);
3306
3307 // second attempt: search for FQDN part, separated by dot.
3308 $result = usePreparedSelectBlade ('SELECT object_id FROM AttributeValue WHERE attr_id = 3 AND string_value LIKE ? LIMIT 2', array ("$string.%"));
3309 $rows = $result->fetchAll (PDO::FETCH_NUM);
3310 return count ($rows) == 1 ? $rows[0][0] : NULL;
3311 }
3312
3313 // returns an array of object ids
3314 function searchByAttrValue ($attr_id, $value)
3315 {
3316 $type = getAttrType ($attr_id);
3317 if (! isset ($type))
3318 throw new InvalidArgException ('attr_id', $attr_id, "No such attribute");
3319
3320 switch ($type)
3321 {
3322 case 'string':
3323 $field = 'string_value';
3324 break;
3325 case 'float':
3326 $field = 'float_value';
3327 break;
3328 case 'uint':
3329 case 'dict':
3330 case 'date':
3331 $field = 'uint_value';
3332 break;
3333 default:
3334 throw new InvalidArgException ('type', $type);
3335 }
3336
3337 $result = usePreparedSelectBlade ("
3338 SELECT object_id FROM AttributeValue
3339 WHERE
3340 attr_id = ?
3341 AND $field = ?
3342 ", array ($attr_id, $value)
3343 );
3344 return $result->fetchAll (PDO::FETCH_COLUMN, 0);
3345 }
3346
3347
3348 // returns user_id
3349 // throws an exception if error occured
3350 function commitCreateUserAccount ($username, $realname, $password)
3351 {
3352 usePreparedInsertBlade
3353 (
3354 'UserAccount',
3355 array
3356 (
3357 'user_name' => $username,
3358 'user_realname' => $realname == '' ? NULL : $realname,
3359 'user_password_hash' => $password,
3360 )
3361 );
3362 $user_id = lastInsertID();
3363 lastCreated ('user', $user_id);
3364 return $user_id;
3365 }
3366
3367 function commitUpdateUserAccount ($id, $new_username, $new_realname, $new_password)
3368 {
3369 usePreparedUpdateBlade
3370 (
3371 'UserAccount',
3372 array
3373 (
3374 'user_name' => $new_username,
3375 'user_realname' => $new_realname == '' ? NULL : $new_realname,
3376 'user_password_hash' => $new_password,
3377 ),
3378 array ('user_id' => $id)
3379 );
3380 }
3381
3382 // This function returns an array of all port type pairs from PortCompat table.
3383 function getPortOIFCompat ($ignore_cache = FALSE)
3384 {
3385 static $cache = NULL;
3386 if (! $ignore_cache && isset ($cache))
3387 return $cache;
3388
3389 $query =
3390 "SELECT type1, type2, POI1.oif_name AS type1name, POI2.oif_name AS type2name FROM " .
3391 "PortCompat AS pc INNER JOIN PortOuterInterface AS POI1 ON pc.type1 = POI1.id " .
3392 "INNER JOIN PortOuterInterface AS POI2 ON pc.type2 = POI2.id " .
3393 'ORDER BY type1name, type2name';
3394 $result = usePreparedSelectBlade ($query);
3395 $cache = $result->fetchAll (PDO::FETCH_ASSOC);
3396 return $cache