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