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