make the configuration defaults data shareable
[racktables] / wwwroot / inc / interface-reports.php
CommitLineData
069689de
DO
1<?php
2
004c2fd2
DO
3# This file is a part of RackTables, a datacenter and server room management
4# framework. See accompanying file "COPYING" for the full copyright and
5# licensing information.
6
069689de
DO
7function renderSystemReports ()
8{
9 $tmp = array
10 (
11 array
12 (
13 'title' => 'Dictionary/objects',
14 'type' => 'counters',
15 'func' => 'getDictStats'
16 ),
17 array
18 (
19 'title' => 'Rackspace',
20 'type' => 'counters',
21 'func' => 'getRackspaceStats'
22 ),
23 array
24 (
25 'title' => 'Files',
26 'type' => 'counters',
27 'func' => 'getFileStats'
28 ),
29 array
30 (
31 'title' => 'Tags top list',
32 'type' => 'custom',
33 'func' => 'renderTagStats'
34 ),
069689de
DO
35 array
36 (
48291896 37 'title' => 'RackCode stats',
069689de
DO
38 'type' => 'counters',
39 'func' => 'getRackCodeStats',
069689de
DO
40 ),
41 array
42 (
48291896 43 'title' => 'RackCode warnings',
069689de
DO
44 'type' => 'messages',
45 'func' => 'getRackCodeWarnings',
069689de 46 ),
069689de
DO
47 array
48 (
48291896 49 'title' => 'IPv4',
069689de
DO
50 'type' => 'counters',
51 'func' => 'getIPv4Stats'
52 ),
069689de
DO
53 array
54 (
48291896 55 'title' => 'IPv6',
069689de
DO
56 'type' => 'counters',
57 'func' => 'getIPv6Stats'
58 ),
59 );
069689de
DO
60 foreach (getPortIIFOptions() as $iif_id => $iif_name)
61 if (count (getPortIIFStats ($iif_id)))
62 $tmp[] = array
63 (
48291896 64 'title' => "{$iif_name} ports",
069689de
DO
65 'type' => 'meters',
66 'func' => 'getPortIIFStats',
67 'args' => $iif_id,
68 );
69 renderReports ($tmp);
70}
71
72function render8021QReport ()
73{
74 if (!count ($domains = getVLANDomainOptions()))
75 {
76 echo '<center><h3>(no VLAN configuration exists)</h3></center>';
77 return;
78 }
79 $vlanstats = array();
80 for ($i = VLAN_MIN_ID; $i <= VLAN_MAX_ID; $i++)
81 $vlanstats[$i] = array();
82 $header = '<tr><th>&nbsp;</th>';
83 foreach ($domains as $domain_id => $domain_name)
84 {
85 foreach (getDomainVLANList ($domain_id) as $vlan_id => $vlan_info)
86 $vlanstats[$vlan_id][$domain_id] = $vlan_info;
87 $header .= '<th>' . mkA ($domain_name, 'vlandomain', $domain_id) . '</th>';
88 }
89 $header .= '</tr>';
90 $output = $available = array();
91 for ($i = VLAN_MIN_ID; $i <= VLAN_MAX_ID; $i++)
92 if (!count ($vlanstats[$i]))
93 $available[] = $i;
94 else
95 $output[$i] = FALSE;
96 foreach (listToRanges ($available) as $span)
97 {
98 if ($span['to'] - $span['from'] < 4)
99 for ($i = $span['from']; $i <= $span['to']; $i++)
100 $output[$i] = FALSE;
101 else
102 {
103 $output[$span['from']] = TRUE;
104 $output[$span['to']] = FALSE;
105 }
106 }
107 ksort ($output, SORT_NUMERIC);
108 $header_delay = 0;
109 startPortlet ('VLAN existence per domain');
110 echo '<table border=1 cellspacing=0 cellpadding=5 align=center class=rackspace>';
111 foreach ($output as $vlan_id => $tbc)
112 {
113 if (--$header_delay <= 0)
114 {
115 echo $header;
116 $header_delay = 25;
117 }
118 echo '<tr class="state_' . (count ($vlanstats[$vlan_id]) ? 'T' : 'F');
119 echo '"><th class=tdright>' . $vlan_id . '</th>';
120 foreach (array_keys ($domains) as $domain_id)
121 {
122 echo '<td class=tdcenter>';
0a750439 123 if (! array_key_exists ($domain_id, $vlanstats[$vlan_id]))
069689de 124 echo '&nbsp;';
0a750439
DO
125 else
126 {
127 $attrs = $vlanstats[$vlan_id][$domain_id]['vlan_descr'] == '' ? NULL :
128 array ('title' => $vlanstats[$vlan_id][$domain_id]['vlan_descr']);
129 echo mkA ('&exist;', 'vlan', "${domain_id}-${vlan_id}", NULL, $attrs);
130 }
069689de
DO
131 echo '</td>';
132 }
133 echo '</tr>';
134 if ($tbc)
135 echo '<tr class="state_A"><th>...</th><td colspan=' . count ($domains) . '>&nbsp;</td></tr>';
136 }
137 echo '</table>';
138 finishPortlet();
139}
140
141function renderReports ($what)
142{
143 if (!count ($what))
144 return;
145 echo "<table align=center>\n";
146 foreach ($what as $item)
147 {
069689de
DO
148 echo "<tr><th colspan=2><h3>${item['title']}</h3></th></tr>\n";
149 switch ($item['type'])
150 {
151 case 'counters':
152 if (array_key_exists ('args', $item))
153 $data = $item['func'] ($item['args']);
154 else
155 $data = $item['func'] ();
156 foreach ($data as $header => $data)
157 echo "<tr><td class=tdright>${header}:</td><td class=tdleft>${data}</td></tr>\n";
158 break;
159 case 'messages':
160 if (array_key_exists ('args', $item))
161 $data = $item['func'] ($item['args']);
162 else
163 $data = $item['func'] ();
164 foreach ($data as $msg)
165 echo "<tr class='msg_${msg['class']}'><td class=tdright>${msg['header']}:</td><td class=tdleft>${msg['text']}</td></tr>\n";
166 break;
167 case 'meters':
168 if (array_key_exists ('args', $item))
169 $data = $item['func'] ($item['args']);
170 else
171 $data = $item['func'] ();
172 foreach ($data as $meter)
173 {
48291896 174 echo "<tr><td class=tdright>${meter['title']}:</td><td class=tdleft>";
069689de 175 renderProgressBar ($meter['max'] ? $meter['current'] / $meter['max'] : 0);
48291896 176 echo ' <small>' . ($meter['max'] ? $meter['current'] . '/' . $meter['max'] : '0') . '</small></td></tr>';
069689de
DO
177 }
178 break;
179 case 'custom':
180 echo "<tr><td colspan=2>";
181 $item['func'] ();
182 echo "</td></tr>\n";
183 break;
184 default:
185 throw new InvalidArgException ('type', $item['type']);
186 }
187 echo "<tr><td colspan=2><hr></td></tr>\n";
188 }
189 echo "</table>\n";
190}
191
192function renderTagStats ()
193{
48291896 194 echo '<table class="zebra widetable"><tr><th>tag</th><th>total</th><th>objects</th><th>IPv4 nets</th><th>IPv6 nets</th>';
069689de
DO
195 echo '<th>racks</th><th>IPv4 VS</th><th>IPv4 RS pools</th><th>users</th><th>files</th></tr>';
196 $pagebyrealm = array
197 (
198 'file' => 'files&tab=default',
199 'ipv4net' => 'ipv4space&tab=default',
200 'ipv6net' => 'ipv6space&tab=default',
201 'ipv4vs' => 'ipv4slb&tab=default',
202 'ipv4rspool' => 'ipv4slb&tab=rspools',
203 'object' => 'depot&tab=default',
204 'rack' => 'rackspace&tab=default',
205 'user' => 'userlist&tab=default'
206 );
207 foreach (getTagChart (getConfigVar ('TAGS_TOPLIST_SIZE')) as $taginfo)
208 {
209 echo "<tr><td>${taginfo['tag']}</td><td>" . $taginfo['refcnt']['total'] . "</td>";
210 foreach (array ('object', 'ipv4net', 'ipv6net', 'rack', 'ipv4vs', 'ipv4rspool', 'user', 'file') as $realm)
211 {
212 echo '<td>';
213 if (!isset ($taginfo['refcnt'][$realm]))
214 echo '&nbsp;';
215 else
216 {
217 echo "<a href='index.php?page=" . $pagebyrealm[$realm] . "&cft[]=${taginfo['id']}'>";
218 echo $taginfo['refcnt'][$realm] . '</a>';
219 }
220 echo '</td>';
221 }
222 echo '</tr>';
223 }
224 echo '</table>';
225}
226
330f20d8
DO
227function renderExpirations ()
228{
229 global $nextorder, $expirations;
230 $attrmap = getAttrMap();
231 foreach ($expirations as $attr_id => $sections)
232 {
233 startPortlet ($attrmap[$attr_id]['name']);
234 foreach ($sections as $section)
235 {
236 $count = 1;
237 $order = 'odd';
238 $result = scanAttrRelativeDays ($attr_id, $section['from'], $section['to']);
239
240 echo '<table align=center width=60% border=0 cellpadding=5 cellspacing=0 align=center class=cooltable>';
241 echo "<caption>${section['title']}</caption>\n";
242
243 if (! count ($result))
244 {
245 echo "<tr><td colspan=4>(none)</td></tr></table><br>\n";
246 continue;
247 }
248 echo '<tr valign=top><th align=center>Count</th><th align=center>Name</th>';
249 echo "<th align=center>Asset Tag</th><th align=center>OEM S/N 1</th><th align=center>Date Warranty <br> Expires</th></tr>\n";
250 foreach ($result as $row)
251 {
252 $date_value = datetimestrFromTimestamp ($row['uint_value']);
253
254 $object = spotEntity ('object', $row['object_id']);
255 $attributes = getAttrValues ($object['id']);
256 $oem_sn_1 = array_key_exists (1, $attributes) ? $attributes[1]['a_value'] : '&nbsp;';
257 echo '<tr class=' . $section['class'] . $order . ' valign=top>';
ef08756a
DO
258 echo "<td class=tdright>${count}</td>";
259 echo '<td class=tdleft>' . mkCellA ($object) . '</td>';
260 echo "<td class=tdleft>${object['asset_no']}</td>";
261 echo "<td class=tdleft>${oem_sn_1}</td>";
330f20d8
DO
262 echo "<td>${date_value}</td>";
263 echo "</tr>\n";
264 $order = $nextorder[$order];
265 $count++;
266 }
267 echo "</table><br>\n";
268 }
269 finishPortlet ();
270 }
271}
272
273// The validity of some data cannot be guaranteed using foreign keys.
274// Display any invalid rows that have crept in.
275// Possible enhancements:
276// - check for IP addresses whose subnet does not exist in IPvXNetwork (X = 4 or 6)
277// - IPvXAddress, IPvXAllocation, IPvXLog, IPvXRS, IPvXVS
278// - provide links/buttons to delete invalid rows
279// - verify that the current DDL is correct for each DB element
280// - columns, indexes, character sets
281function renderDataIntegrityReport ()
282{
330f20d8
DO
283 $violations = FALSE;
284
285 // check 1: EntityLink rows referencing not-existent relatives
286 // check 1.1: children
287 $realms = array
288 (
289 'location' => 'Location',
290 'object' => 'RackObject',
291 'rack' => 'Rack',
292 'row' => 'Row'
293 );
294 $orphans = array ();
295 foreach ($realms as $realm => $table)
296 {
297 $result = usePreparedSelectBlade
298 (
0c40b71e
DO
299 'SELECT EL.parent_entity_type, EL.parent_entity_id, ' .
300 'EL.child_entity_type, EL.child_entity_id FROM EntityLink EL ' .
330f20d8
DO
301 "LEFT JOIN ${table} ON EL.child_entity_id = ${table}.id " .
302 "WHERE EL.child_entity_type = ? AND ${table}.id IS NULL",
303 array ($realm)
304 );
305 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
306 unset ($result);
307 $orphans = array_merge ($orphans, $rows);
308 }
309 if (count ($orphans))
310 {
311 $violations = TRUE;
312 startPortlet ('EntityLink: Missing Children (' . count ($orphans) . ')');
3b15479f 313 echo "<table cellpadding=5 cellspacing=0 align=center class='cooltable zebra'>\n";
ee660c28 314 echo "<tr><th>Parent</th><th>Child Type</th><th class=tdright>Child ID</th></tr>\n";
330f20d8
DO
315 foreach ($orphans as $orphan)
316 {
317 $realm_name = formatRealmName ($orphan['parent_entity_type']);
318 try
319 {
320 $parent = spotEntity ($orphan['parent_entity_type'], $orphan['parent_entity_id']);
321 $parent_name = $parent['name'];
322 }
323 catch (EntityNotFoundException $e)
324 {
325 $parent_name = 'missing from DB';
326 }
3b15479f 327 echo '<tr>';
330f20d8
DO
328 echo "<td>${realm_name}: ${parent_name}</td>";
329 echo "<td>${orphan['child_entity_type']}</td>";
ee660c28 330 echo "<td class=tdright>${orphan['child_entity_id']}</td>";
330f20d8 331 echo "</tr>\n";
330f20d8
DO
332 }
333 echo "</table>\n";
334 finishPortLet ();
335 }
336
337 // check 1.2: parents
338 $orphans = array ();
339 foreach ($realms as $realm => $table)
340 {
341 $result = usePreparedSelectBlade
342 (
0c40b71e
DO
343 'SELECT EL.parent_entity_type, EL.parent_entity_id, ' .
344 'EL.child_entity_type, EL.child_entity_id FROM EntityLink EL ' .
330f20d8
DO
345 "LEFT JOIN ${table} ON EL.parent_entity_id = ${table}.id " .
346 "WHERE EL.parent_entity_type = ? AND ${table}.id IS NULL",
347 array ($realm)
348 );
349 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
350 unset ($result);
351 $orphans = array_merge ($orphans, $rows);
352 }
353 if (count ($orphans))
354 {
355 $violations = TRUE;
356 startPortlet ('EntityLink: Missing Parents (' . count ($orphans) . ')');
3b15479f 357 echo "<table cellpadding=5 cellspacing=0 align=center class='cooltable zebra'>\n";
ee660c28 358 echo "<tr><th>Child</th><th>Parent Type</th><th class=tdright>Parent ID</th></tr>\n";
330f20d8
DO
359 foreach ($orphans as $orphan)
360 {
361 $realm_name = formatRealmName ($orphan['child_entity_type']);
362 try
363 {
364 $child = spotEntity ($orphan['child_entity_type'], $orphan['child_entity_id']);
365 $child_name = $child['name'];
366 }
367 catch (EntityNotFoundException $e)
368 {
369 $child_name = 'missing from DB';
370 }
3b15479f 371 echo '<tr>';
330f20d8
DO
372 echo "<td>${realm_name}: ${child_name}</td>";
373 echo "<td>${orphan['parent_entity_type']}</td>";
ee660c28 374 echo "<td class=tdright>${orphan['parent_entity_id']}</td>";
330f20d8 375 echo "</tr>\n";
330f20d8
DO
376 }
377 echo "</table>\n";
378 finishPortLet ();
379 }
380
381 // check 3: multiple tables referencing non-existent dictionary entries
382 // check 3.1: AttributeMap
383 $orphans = array ();
384 $result = usePreparedSelectBlade
385 (
dff67d81 386 'SELECT AM.objtype_id, A.name AS attr_name, C.name AS chapter_name ' .
330f20d8
DO
387 'FROM AttributeMap AM ' .
388 'LEFT JOIN Attribute A ON AM.attr_id = A.id ' .
389 'LEFT JOIN Chapter C ON AM.chapter_id = C.id ' .
390 'LEFT JOIN Dictionary D ON AM.objtype_id = D.dict_key ' .
391 'WHERE D.dict_key IS NULL'
392 );
393 $orphans = $result->fetchAll (PDO::FETCH_ASSOC);
394 unset ($result);
395 if (count ($orphans))
396 {
397 $violations = TRUE;
398 startPortlet ('AttributeMap: Invalid Mappings (' . count ($orphans) . ')');
66eece6d
DO
399 $columns = array
400 (
401 array ('th_text' => 'Attribute', 'row_key' => 'attr_name'),
402 array ('th_text' => 'Chapter', 'row_key' => 'chapter_name'),
403 array ('th_text' => 'Object TypeID', 'row_key' => 'objtype_id', 'td_class' => 'tdright'),
404 );
405 renderTableViewer ($columns, $orphans);
330f20d8
DO
406 finishPortLet ();
407 }
408
409 // check 3.2: Object
410 $orphans = array ();
411 $result = usePreparedSelectBlade
412 (
dff67d81 413 'SELECT O.id, O.name, O.objtype_id FROM Object O ' .
330f20d8
DO
414 'LEFT JOIN Dictionary D ON O.objtype_id = D.dict_key ' .
415 'WHERE D.dict_key IS NULL'
416 );
417 $orphans = $result->fetchAll (PDO::FETCH_ASSOC);
418 unset ($result);
419 if (count ($orphans))
420 {
421 $violations = TRUE;
422 startPortlet ('Object: Invalid Types (' . count ($orphans) . ')');
66eece6d
DO
423 $columns = array
424 (
425 array ('th_text' => 'ID', 'row_key' => 'id', 'td_class' => 'tdright'),
426 array ('th_text' => 'Name', 'row_key' => 'name'),
427 array ('th_text' => 'Type ID', 'row_key' => 'objtype_id', 'td_class' => 'tdright'),
428 );
429 renderTableViewer ($columns, $orphans);
330f20d8
DO
430 finishPortLet ();
431 }
432
433 // check 3.3: ObjectHistory
434 $orphans = array ();
435 $result = usePreparedSelectBlade
436 (
dff67d81 437 'SELECT OH.id, OH.name, OH.objtype_id FROM ObjectHistory OH ' .
330f20d8
DO
438 'LEFT JOIN Dictionary D ON OH.objtype_id = D.dict_key ' .
439 'WHERE D.dict_key IS NULL'
440 );
441 $orphans = $result->fetchAll (PDO::FETCH_ASSOC);
442 unset ($result);
443 if (count ($orphans))
444 {
445 $violations = TRUE;
446 startPortlet ('ObjectHistory: Invalid Types (' . count ($orphans) . ')');
66eece6d
DO
447 $columns = array
448 (
449 array ('th_text' => 'ID', 'row_key' => 'id', 'td_class' => 'tdright'),
450 array ('th_text' => 'Name', 'row_key' => 'name'),
451 array ('th_text' => 'Type ID', 'row_key' => 'objtype_id', 'td_class' => 'tdright'),
452 );
453 renderTableViewer ($columns, $orphans);
330f20d8
DO
454 finishPortLet ();
455 }
456
457 // check 3.4: ObjectParentCompat
458 $orphans = array ();
459 $result = usePreparedSelectBlade
460 (
0c40b71e 461 'SELECT OPC.parent_objtype_id, OPC.child_objtype_id, PD.dict_value AS parent_name, CD.dict_value AS child_name '.
330f20d8
DO
462 'FROM ObjectParentCompat OPC ' .
463 'LEFT JOIN Dictionary PD ON OPC.parent_objtype_id = PD.dict_key ' .
464 'LEFT JOIN Dictionary CD ON OPC.child_objtype_id = CD.dict_key ' .
465 'WHERE PD.dict_key IS NULL OR CD.dict_key IS NULL'
466 );
467 $orphans = $result->fetchAll (PDO::FETCH_ASSOC);
468 unset ($result);
469 if (count ($orphans))
470 {
471 $violations = TRUE;
472 startPortlet ('Object Container Compatibility rules: Invalid Parent or Child Type (' . count ($orphans) . ')');
66eece6d
DO
473 $columns = array
474 (
475 array ('th_text' => 'Parent', 'row_key' => 'parent_name'),
476 array ('th_text' => 'Parent Type ID', 'row_key' => 'parent_objtype_id', 'td_class' => 'tdright'),
477 array ('th_text' => 'Child', 'row_key' => 'child_name'),
478 array ('th_text' => 'Child Type ID', 'row_key' => 'child_objtype_id', 'td_class' => 'tdright'),
479 );
480 renderTableViewer ($columns, $orphans);
330f20d8
DO
481 finishPortLet ();
482 }
483
484 // check 4: relationships that violate ObjectParentCompat Rules
485 $invalids = array ();
486 $result = usePreparedSelectBlade
487 (
488 'SELECT CO.id AS child_id, CO.objtype_id AS child_type_id, CD.dict_value AS child_type, CO.name AS child_name, ' .
489 'PO.id AS parent_id, PO.objtype_id AS parent_type_id, PD.dict_value AS parent_type, PO.name AS parent_name ' .
490 'FROM Object CO ' .
491 'LEFT JOIN EntityLink EL ON CO.id = EL.child_entity_id ' .
492 'LEFT JOIN Object PO ON EL.parent_entity_id = PO.id ' .
10318b2d 493 'LEFT JOIN ObjectParentCompat OPC ON PO.objtype_id = OPC.parent_objtype_id AND CO.objtype_id = OPC.child_objtype_id ' .
330f20d8
DO
494 'LEFT JOIN Dictionary PD ON PO.objtype_id = PD.dict_key ' .
495 'LEFT JOIN Dictionary CD ON CO.objtype_id = CD.dict_key ' .
496 "WHERE EL.parent_entity_type = 'object' AND EL.child_entity_type = 'object' " .
497 'AND OPC.parent_objtype_id IS NULL'
498 );
499 $invalids = $result->fetchAll (PDO::FETCH_ASSOC);
500 unset ($result);
501 if (count ($invalids))
502 {
503 $violations = TRUE;
504 startPortlet ('Objects: Violate Object Container Compatibility rules (' . count ($invalids) . ')');
66eece6d
DO
505 $columns = array
506 (
507 array ('th_text' => 'Contained Obj Name', 'row_key' => 'child_name'),
508 array ('th_text' => 'Contained Obj Type', 'row_key' => 'child_type'),
509 array ('th_text' => 'Container Obj Name', 'row_key' => 'parent_name'),
510 array ('th_text' => 'Container Obj Type', 'row_key' => 'parent_type'),
511 );
512 renderTableViewer ($columns, $invalids);
330f20d8
DO
513 finishPortLet ();
514 }
515
516 // check 5: Links that violate PortCompat Rules
517 $invalids = array ();
518 $result = usePreparedSelectBlade
519 (
520 'SELECT OA.id AS obja_id, OA.name AS obja_name, L.porta AS porta_id, PA.name AS porta_name, POIA.oif_name AS porta_type, ' .
521 'OB.id AS objb_id, OB.name AS objb_name, L.portb AS portb_id, PB.name AS portb_name, POIB.oif_name AS portb_type ' .
522 'FROM Link L ' .
523 'LEFT JOIN Port PA ON L.porta = PA.id ' .
524 'LEFT JOIN Object OA ON PA.object_id = OA.id ' .
525 'LEFT JOIN PortOuterInterface POIA ON PA.type = POIA.id ' .
526 'LEFT JOIN Port PB ON L.portb = PB.id ' .
527 'LEFT JOIN Object OB ON PB.object_id = OB.id ' .
528 'LEFT JOIN PortOuterInterface POIB ON PB.type = POIB.id ' .
529 'LEFT JOIN PortCompat PC on PA.type = PC.type1 AND PB.type = PC.type2 ' .
530 'WHERE PC.type1 IS NULL OR PC.type2 IS NULL'
531 );
532 $invalids = $result->fetchAll (PDO::FETCH_ASSOC);
533 unset ($result);
534 if (count ($invalids))
535 {
536 $violations = TRUE;
537 startPortlet ('Port Links: Violate Port Compatibility Rules (' . count ($invalids) . ')');
66eece6d
DO
538 $columns = array
539 (
540 array ('th_text' => 'Object A', 'row_key' => 'obja_name'),
541 array ('th_text' => 'Port A Name', 'row_key' => 'porta_name'),
542 array ('th_text' => 'Port A Type', 'row_key' => 'porta_type'),
543 array ('th_text' => 'Object B', 'row_key' => 'objb_name'),
544 array ('th_text' => 'Port B Name', 'row_key' => 'portb_name'),
545 array ('th_text' => 'Port B Type', 'row_key' => 'portb_type'),
546 );
547 renderTableViewer ($columns, $invalids);
330f20d8
DO
548 finishPortLet ();
549 }
550
551 // check 6: TagStorage rows referencing non-existent parents
552 $realms = array
553 (
554 'file' => array ('table' => 'File', 'column' => 'id'),
555 'ipv4net' => array ('table' => 'IPv4Network', 'column' => 'id'),
556 'ipv4rspool' => array ('table' => 'IPv4RSPool', 'column' => 'id'),
557 'ipv4vs' => array ('table' => 'IPv4VS', 'column' => 'id'),
558 'ipv6net' => array ('table' => 'IPv6Network', 'column' => 'id'),
559 'ipvs' => array ('table' => 'VS', 'column' => 'id'),
560 'location' => array ('table' => 'Location', 'column' => 'id'),
561 'object' => array ('table' => 'RackObject', 'column' => 'id'),
562 'rack' => array ('table' => 'Rack', 'column' => 'id'),
563 'user' => array ('table' => 'UserAccount', 'column' => 'user_id'),
564 'vst' => array ('table' => 'VLANSwitchTemplate', 'column' => 'id'),
565 );
566 $orphans = array ();
567 foreach ($realms as $realm => $details)
568 {
569 $result = usePreparedSelectBlade
570 (
dff67d81 571 'SELECT TS.entity_realm, TS.entity_id, TT.tag FROM TagStorage TS ' .
330f20d8
DO
572 'LEFT JOIN TagTree TT ON TS.tag_id = TT.id ' .
573 "LEFT JOIN ${details['table']} ON TS.entity_id = ${details['table']}.${details['column']} " .
574 "WHERE TS.entity_realm = ? AND ${details['table']}.${details['column']} IS NULL",
575 array ($realm)
576 );
577 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
578 unset ($result);
579 $orphans = array_merge ($orphans, $rows);
580 }
581 if (count ($orphans))
582 {
583 $violations = TRUE;
584 startPortlet ('TagStorage: Missing Parents (' . count ($orphans) . ')');
66eece6d
DO
585 foreach (array_keys ($orphans) as $key)
586 $orphans[$key]['realm_name'] = formatRealmName ($orphans[$key]['entity_realm']);
587 $columns = array
588 (
589 array ('th_text' => 'Tag', 'row_key' => 'tag'),
590 array ('th_text' => 'Parent Type', 'row_key' => 'realm_name'),
591 array ('th_text' => 'Parent ID', 'row_key' => 'entity_id', 'td_class' => 'tdright'),
592 );
593 renderTableViewer ($columns, $orphans);
330f20d8
DO
594 finishPortLet ();
595 }
596
597 // check 7: FileLink rows referencing non-existent parents
598 // re-use the realms list from the TagStorage check, with a few mods
599 unset ($realms['file'], $realms['vst']);
600 $realms['row'] = array ('table' => 'Row', 'column' => 'id');
601 $orphans = array ();
602 foreach ($realms as $realm => $details)
603 {
604 $result = usePreparedSelectBlade
605 (
66eece6d 606 'SELECT FL.entity_type, FL.entity_id, F.id FROM FileLink FL ' .
330f20d8
DO
607 'LEFT JOIN File F ON FL.file_id = F.id ' .
608 "LEFT JOIN ${details['table']} ON FL.entity_id = ${details['table']}.${details['column']} " .
609 "WHERE FL.entity_type = ? AND ${details['table']}.${details['column']} IS NULL",
610 array ($realm)
611 );
612 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
613 unset ($result);
614 $orphans = array_merge ($orphans, $rows);
615 }
616 if (count ($orphans))
617 {
618 $violations = TRUE;
619 startPortlet ('FileLink: Missing Parents (' . count ($orphans) . ')');
66eece6d 620 foreach (array_keys ($orphans) as $key)
330f20d8 621 {
66eece6d
DO
622 $orphans[$key]['name'] = mkCellA (spotEntity ('file', $orphans[$key]['id']));
623 $orphans[$key]['realm_name'] = formatRealmName ($orphans[$key]['entity_type']);
330f20d8 624 }
66eece6d
DO
625 $columns = array
626 (
627 array ('th_text' => 'File', 'row_key' => 'name', 'td_escape' => FALSE),
628 array ('th_text' => 'Parent Type', 'row_key' => 'realm_name'),
629 array ('th_text' => 'Parent ID', 'row_key' => 'entity_id', 'td_class' => 'tdright'),
630 );
631 renderTableViewer ($columns, $orphans);
330f20d8
DO
632 finishPortLet ();
633 }
634
f24df831
DO
635 // check 8: triggers
636 $known_triggers= array
330f20d8 637 (
f24df831
DO
638 array ('trigger_name' => 'Link-before-insert', 'table_name' => 'Link'),
639 array ('trigger_name' => 'Link-before-update', 'table_name' => 'Link'),
640 array ('trigger_name' => 'EntityLink-before-insert', 'table_name' => 'EntityLink'),
641 array ('trigger_name' => 'EntityLink-before-update', 'table_name' => 'EntityLink'),
330f20d8 642 );
f24df831
DO
643 $known_triggers = reindexById ($known_triggers, 'trigger_name');
644
330f20d8
DO
645 $result = usePreparedSelectBlade
646 (
f24df831 647 'SELECT TRIGGER_NAME AS trigger_name, EVENT_OBJECT_TABLE AS table_name ' .
330f20d8
DO
648 'FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = SCHEMA()'
649 );
f24df831 650 $existing_triggers = reindexById ($result->fetchAll (PDO::FETCH_ASSOC), 'trigger_name');
330f20d8 651 unset ($result);
f24df831
DO
652
653 $missing_triggers = array_diff_key ($known_triggers, $existing_triggers);
654 $unknown_triggers = array_diff_key ($existing_triggers, $known_triggers);
655 $columns = array
656 (
657 array ('th_text' => 'Table', 'row_key' => 'table_name'),
658 array ('th_text' => 'Trigger', 'row_key' => 'trigger_name'),
659 );
660
330f20d8
DO
661 if (count ($missing_triggers))
662 {
663 $violations = TRUE;
664 startPortlet ('Missing Triggers (' . count ($missing_triggers) . ')');
f24df831
DO
665 renderTableViewer ($columns, $missing_triggers);
666 finishPortLet ();
667 }
668
669 if (count ($unknown_triggers))
670 {
671 $violations = TRUE;
672 startPortlet ('Unknown Triggers (' . count ($unknown_triggers) . ')');
673 renderTableViewer ($columns, $unknown_triggers);
330f20d8
DO
674 finishPortLet ();
675 }
676
f24df831
DO
677 // check 9: foreign keys
678 $known_fkeys = array
330f20d8 679 (
f24df831
DO
680 array ('fkey_name' => 'Atom-FK-molecule_id', 'table_name' => 'Atom'),
681 array ('fkey_name' => 'Atom-FK-rack_id', 'table_name' => 'Atom'),
682 array ('fkey_name' => 'AttributeMap-FK-chapter_id', 'table_name' => 'AttributeMap'),
683 array ('fkey_name' => 'AttributeMap-FK-attr_id', 'table_name' => 'AttributeMap'),
684 array ('fkey_name' => 'AttributeValue-FK-map', 'table_name' => 'AttributeValue'),
685 array ('fkey_name' => 'AttributeValue-FK-object', 'table_name' => 'AttributeValue'),
686 array ('fkey_name' => 'CachedPAV-FK-object-port', 'table_name' => 'CachedPAV'),
687 array ('fkey_name' => 'CachedPAV-FK-vlan_id', 'table_name' => 'CachedPAV'),
688 array ('fkey_name' => 'CachedPNV-FK-compound', 'table_name' => 'CachedPNV'),
689 array ('fkey_name' => 'CachedPVM-FK-object_id', 'table_name' => 'CachedPVM'),
690 array ('fkey_name' => 'Dictionary-FK-chapter_id', 'table_name' => 'Dictionary'),
691 array ('fkey_name' => 'FileLink-File_fkey', 'table_name' => 'FileLink'),
692 array ('fkey_name' => 'IPv4Allocation-FK-object_id', 'table_name' => 'IPv4Allocation'),
693 array ('fkey_name' => 'IPv4LB-FK-vs_id', 'table_name' => 'IPv4LB'),
694 array ('fkey_name' => 'IPv4LB-FK-object_id', 'table_name' => 'IPv4LB'),
695 array ('fkey_name' => 'IPv4LB-FK-rspool_id', 'table_name' => 'IPv4LB'),
696 array ('fkey_name' => 'IPv4NAT-FK-object_id', 'table_name' => 'IPv4NAT'),
697 array ('fkey_name' => 'IPv4RS-FK', 'table_name' => 'IPv4RS'),
698 array ('fkey_name' => 'IPv6Allocation-FK-object_id', 'table_name' => 'IPv6Allocation'),
699 array ('fkey_name' => 'Link-FK-a', 'table_name' => 'Link'),
700 array ('fkey_name' => 'Link-FK-b', 'table_name' => 'Link'),
701 array ('fkey_name' => 'MountOperation-FK-object_id', 'table_name' => 'MountOperation'),
702 array ('fkey_name' => 'MountOperation-FK-old_molecule_id', 'table_name' => 'MountOperation'),
703 array ('fkey_name' => 'MountOperation-FK-new_molecule_id', 'table_name' => 'MountOperation'),
704 array ('fkey_name' => 'ObjectHistory-FK-object_id', 'table_name' => 'ObjectHistory'),
705 array ('fkey_name' => 'ObjectLog-FK-object_id', 'table_name' => 'ObjectLog'),
706 array ('fkey_name' => 'PatchCableConnectorCompat-FK-connector_id', 'table_name' => 'PatchCableConnectorCompat'),
707 array ('fkey_name' => 'PatchCableConnectorCompat-FK-pctype_id', 'table_name' => 'PatchCableConnectorCompat'),
708 array ('fkey_name' => 'PatchCableHeap-FK-compat1', 'table_name' => 'PatchCableHeap'),
709 array ('fkey_name' => 'PatchCableHeap-FK-compat2', 'table_name' => 'PatchCableHeap'),
710 array ('fkey_name' => 'PatchCableHeapLog-FK-heap_id', 'table_name' => 'PatchCableHeapLog'),
711 array ('fkey_name' => 'PatchCableOIFCompat-FK-oif_id', 'table_name' => 'PatchCableOIFCompat'),
712 array ('fkey_name' => 'PatchCableOIFCompat-FK-pctype_id', 'table_name' => 'PatchCableOIFCompat'),
713 array ('fkey_name' => 'Port-FK-iif-oif', 'table_name' => 'Port'),
714 array ('fkey_name' => 'Port-FK-object_id', 'table_name' => 'Port'),
715 array ('fkey_name' => 'PortAllowedVLAN-FK-object-port', 'table_name' => 'PortAllowedVLAN'),
716 array ('fkey_name' => 'PortAllowedVLAN-FK-vlan_id', 'table_name' => 'PortAllowedVLAN'),
717 array ('fkey_name' => 'PortCompat-FK-oif_id1', 'table_name' => 'PortCompat'),
718 array ('fkey_name' => 'PortCompat-FK-oif_id2', 'table_name' => 'PortCompat'),
719 array ('fkey_name' => 'PortInterfaceCompat-FK-iif_id', 'table_name' => 'PortInterfaceCompat'),
720 array ('fkey_name' => 'PortInterfaceCompat-FK-oif_id', 'table_name' => 'PortInterfaceCompat'),
721 array ('fkey_name' => 'PortLog_ibfk_1', 'table_name' => 'PortLog'),
722 array ('fkey_name' => 'PortNativeVLAN-FK-compound', 'table_name' => 'PortNativeVLAN'),
723 array ('fkey_name' => 'PortVLANMode-FK-object-port', 'table_name' => 'PortVLANMode'),
724 array ('fkey_name' => 'RackSpace-FK-rack_id', 'table_name' => 'RackSpace'),
725 array ('fkey_name' => 'RackSpace-FK-object_id', 'table_name' => 'RackSpace'),
726 array ('fkey_name' => 'RackThumbnail-FK-rack_id', 'table_name' => 'RackThumbnail'),
727 array ('fkey_name' => 'TagStorage-FK-TagTree', 'table_name' => 'TagStorage'),
728 array ('fkey_name' => 'TagTree-K-parent_id', 'table_name' => 'TagTree'),
729 array ('fkey_name' => 'UserConfig-FK-varname', 'table_name' => 'UserConfig'),
730 array ('fkey_name' => 'VLANDescription-FK-domain_id', 'table_name' => 'VLANDescription'),
731 array ('fkey_name' => 'VLANDescription-FK-vlan_id', 'table_name' => 'VLANDescription'),
732 array ('fkey_name' => 'VLANDomain-FK-group_id', 'table_name' => 'VLANDomain'),
733 array ('fkey_name' => 'VLANIPv4-FK-compound', 'table_name' => 'VLANIPv4'),
734 array ('fkey_name' => 'VLANIPv4-FK-ipv4net_id', 'table_name' => 'VLANIPv4'),
735 array ('fkey_name' => 'VLANIPv6-FK-compound', 'table_name' => 'VLANIPv6'),
736 array ('fkey_name' => 'VLANIPv6-FK-ipv6net_id', 'table_name' => 'VLANIPv6'),
737 array ('fkey_name' => 'VLANSTRule-FK-vst_id', 'table_name' => 'VLANSTRule'),
738 array ('fkey_name' => 'VLANSwitch-FK-domain_id', 'table_name' => 'VLANSwitch'),
739 array ('fkey_name' => 'VLANSwitch-FK-object_id', 'table_name' => 'VLANSwitch'),
740 array ('fkey_name' => 'VLANSwitch-FK-template_id', 'table_name' => 'VLANSwitch'),
741 array ('fkey_name' => 'VSEnabledIPs-FK-object_id', 'table_name' => 'VSEnabledIPs'),
742 array ('fkey_name' => 'VSEnabledIPs-FK-rspool_id', 'table_name' => 'VSEnabledIPs'),
743 array ('fkey_name' => 'VSEnabledIPs-FK-vs_id-vip', 'table_name' => 'VSEnabledIPs'),
744 array ('fkey_name' => 'VSEnabledPorts-FK-object_id', 'table_name' => 'VSEnabledPorts'),
745 array ('fkey_name' => 'VSEnabledPorts-FK-rspool_id', 'table_name' => 'VSEnabledPorts'),
746 array ('fkey_name' => 'VSEnabledPorts-FK-vs_id-proto-vport', 'table_name' => 'VSEnabledPorts'),
747 array ('fkey_name' => 'VSIPs-vs_id', 'table_name' => 'VSIPs'),
748 array ('fkey_name' => 'VS-vs_id', 'table_name' => 'VSPorts'),
330f20d8 749 );
011df355
AD
750
751 $plugins = getPlugins ('enabled');
752 foreach (array_keys ($plugins) as $plugin)
753 {
754 global ${"plugin_${plugin}_fkeys"};
755 if (isset (${"plugin_${plugin}_fkeys"}))
f24df831 756 $known_fkeys = array_merge ($known_fkeys, ${"plugin_${plugin}_fkeys"});
011df355 757 }
f24df831
DO
758 $known_fkeys = reindexById ($known_fkeys, 'fkey_name');
759 ksort ($known_fkeys);
011df355 760
330f20d8
DO
761 $result = usePreparedSelectBlade
762 (
f24df831 763 'SELECT CONSTRAINT_NAME as fkey_name, TABLE_NAME AS table_name ' .
330f20d8
DO
764 'FROM information_schema.TABLE_CONSTRAINTS ' .
765 "WHERE CONSTRAINT_SCHEMA = SCHEMA() AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
766 );
f24df831 767 $existing_fkeys = reindexById ($result->fetchAll (PDO::FETCH_ASSOC), 'fkey_name');
330f20d8 768 unset ($result);
f24df831
DO
769 $missing_fkeys = array_diff_key ($known_fkeys, $existing_fkeys);
770 $unknown_fkeys = array_diff_key ($existing_fkeys, $known_fkeys);
771 $columns = array
772 (
773 array ('th_text' => 'Table', 'row_key' => 'table_name'),
774 array ('th_text' => 'Key', 'row_key' => 'fkey_name'),
775 );
776
330f20d8
DO
777 if (count ($missing_fkeys))
778 {
779 $violations = TRUE;
780 startPortlet ('Missing Foreign Keys (' . count ($missing_fkeys) . ')');
f24df831
DO
781 renderTableViewer ($columns, $missing_fkeys);
782 finishPortLet ();
783 }
784
785 if (count ($unknown_fkeys))
786 {
787 $violations = TRUE;
788 startPortlet ('Unknown Foreign Keys (' . count ($unknown_fkeys) . ')');
789 renderTableViewer ($columns, $unknown_fkeys);
330f20d8
DO
790 finishPortLet ();
791 }
792
793 // check 10: circular references
794 // - all affected members of the tree are displayed
795 // - it would be beneficial to only display the offending records
796 // check 10.1: locations
797 $invalids = array ();
798 $locations = listCells ('location');
799 foreach ($locations as $location)
800 {
801 try
802 {
803 $children = getLocationChildrenList ($location['id']);
804 }
805 catch (RackTablesError $e)
806 {
807 $invalids[] = $location;
808 }
809 }
810 if (count ($invalids))
811 {
812 $violations = TRUE;
813 startPortlet ('Locations: Tree Contains Circular References (' . count ($invalids) . ')');
3b15479f 814 echo "<table cellpadding=5 cellspacing=0 align=center class='cooltable zebra'>\n";
ee660c28 815 echo "<tr><th class=tdright>Child ID</th><th>Child Location</th><th class=tdright>Parent ID</th><th>Parent Location</th></tr>\n";
330f20d8
DO
816 foreach ($invalids as $invalid)
817 {
3b15479f 818 echo '<tr>';
ee660c28 819 echo "<td class=tdright>${invalid['id']}</td>";
330f20d8 820 echo "<td>${invalid['name']}</td>";
ee660c28 821 echo "<td class=tdright>${invalid['parent_id']}</td>";
330f20d8
DO
822 echo "<td>${invalid['parent_name']}</td>";
823 echo "</tr>\n";
330f20d8
DO
824 }
825 echo "</table>\n";
826 finishPortLet ();
827 }
828
829 // check 10.2: objects
830 $invalids = array ();
831 $objects = listCells ('object');
832 foreach ($objects as $object)
833 {
834 try
835 {
836 $children = getObjectContentsList ($object['id']);
837 }
838 catch (RackTablesError $e)
839 {
840 $invalids[] = $object;
841 }
842 }
843 if (count ($invalids))
844 {
845 $violations = TRUE;
846 startPortlet ('Objects: Tree Contains Circular References (' . count ($invalids) . ')');
66eece6d
DO
847 foreach (array_keys ($invalids) as $key)
848 $invalids[$key]['object'] = mkCellA ($invalids[$key]);
849 $columns = array
850 (
851 array ('th_text' => 'Contained ID', 'row_key' => 'id', 'td_class' => 'tdright'),
852 array ('th_text' => 'Contained Object', 'row_key' => 'object', 'td_escape' => FALSE),
853 array ('th_text' => 'Container ID', 'row_key' => 'container_id', 'td_class' => 'tdright'),
854 array ('th_text' => 'Container Object', 'row_key' => 'container_name'),
855 );
856 renderTableViewer ($columns, $invalids);
330f20d8
DO
857 finishPortLet ();
858 }
859
860 // check 10.3: tags
a04b65ce 861 global $taglist;
b46bd2c6 862 $invalids = getInvalidNodes ($taglist);
330f20d8
DO
863 if (count ($invalids))
864 {
865 $violations = TRUE;
866 startPortlet ('Tags: Tree Contains Circular References (' . count ($invalids) . ')');
3b15479f 867 echo "<table cellpadding=5 cellspacing=0 align=center class='cooltable zebra'>\n";
ee660c28 868 echo "<tr><th class=tdright>Child ID</th><th>Child Tag</th><th class=tdright>Parent ID</th><th>Parent Tag</th></tr>\n";
330f20d8
DO
869 foreach ($invalids as $invalid)
870 {
3b15479f 871 echo '<tr>';
ee660c28 872 echo "<td class=tdright>${invalid['id']}</td>";
330f20d8 873 echo "<td>${invalid['tag']}</td>";
ee660c28 874 echo "<td class=tdright>${invalid['parent_id']}</td>";
a04b65ce 875 printf('<td>%s</td>', $taglist[$invalid['parent_id']]['tag']);
330f20d8 876 echo "</tr>\n";
330f20d8
DO
877 }
878 echo "</table>\n";
879 finishPortLet ();
880 }
881
11b1059e 882 // L2 addresses
53f8e018
DO
883 $columns = array
884 (
885 array ('th_text' => 'L2 address', 'row_key' => 'l2address', 'td_class' => 'l2address'),
886 array ('th_text' => 'Object', 'row_key' => 'object', 'td_escape' => FALSE),
887 array ('th_text' => 'Port', 'row_key' => 'name'),
888 );
889
11b1059e
DO
890 // The section below is only required so long as Port.l2address is a char column,
891 // switching to a binary type should eliminate the need for this check.
892 $result = usePreparedSelectBlade
893 (
894 'SELECT l2address, object_id, name FROM Port ' .
895 'WHERE l2address IS NOT NULL AND l2address NOT REGEXP("^[0-9A-F]+$")'
896 );
897 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
898 unset ($result);
899 if (count ($rows))
900 {
901 $violations = TRUE;
902 foreach (array_keys ($rows) as $key)
903 $rows[$key]['object'] = mkCellA (spotEntity ('object', $rows[$key]['object_id']));
53f8e018 904 startPortlet ('L2 address invalid characters');
11b1059e
DO
905 renderTableViewer ($columns, $rows);
906 finishPortlet();
907 }
53f8e018 908
11b1059e
DO
909 // The section below will be relevant as long as the L2 address constraint remains
910 // implemented at PHP level.
911 $result = usePreparedSelectBlade
912 (
913 'SELECT l2address, object_id, name, ' .
914 '(SELECT COUNT(*) FROM Port AS P2 WHERE P2.l2address = P1.l2address AND P2.object_id != P1.object_id) AS ocnt ' .
915 'FROM Port AS P1 WHERE P1.l2address IS NOT NULL HAVING ocnt > 0 ORDER BY l2address, object_id'
916 );
917 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
918 unset ($result);
919 if (count ($rows))
920 {
921 $violations = TRUE;
922 foreach (array_keys ($rows) as $key)
66eece6d 923 {
11b1059e 924 $rows[$key]['object'] = mkCellA (spotEntity ('object', $rows[$key]['object_id']));
66eece6d
DO
925 $rows[$key]['l2address'] = l2addressFromDatabase ($rows[$key]['l2address']);
926 }
11b1059e
DO
927 startPortlet ('L2 address unique constraint errors');
928 renderTableViewer ($columns, $rows);
929 finishPortlet();
930 }
931
53f8e018
DO
932 $result = usePreparedSelectBlade
933 (
934 'SELECT l2address, object_id, name ' .
935 'FROM Port WHERE LENGTH(l2address) NOT IN(12, 16, 40)'
936 );
937 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
938 unset ($result);
939 if (count ($rows))
940 {
941 $violations = TRUE;
942 // Do not try to call l2addressFromDatabase() as it will normally throw an exception.
943 foreach (array_keys ($rows) as $key)
944 $rows[$key]['object'] = mkCellA (spotEntity ('object', $rows[$key]['object_id']));
945 startPortlet ('L2 address invalid length');
946 renderTableViewer ($columns, $rows);
947 finishPortlet();
948 }
949
330f20d8 950 if (! $violations)
7941b2e1 951 echo '<h2 class=centered>No integrity violations found</h2>';
330f20d8 952}
a975b2dc
DO
953
954function renderServerConfigurationReport ()
955{
956 echo '<br>';
a36639be
DO
957 try
958 {
959 $test_innodb = isInnoDBSupported();
960 }
961 catch (PDOException $e)
962 {
963 showError ('InnoDB test failed (is binary logging enabled?).');
964 $test_innodb = FALSE;
965 }
966 platform_is_ok ($test_innodb);
a975b2dc 967}