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