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