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