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