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