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