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