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