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