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