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