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