source: branches/sql-cleanup/cgi-bin/consistency-check.pl@ 193

Last change on this file since 193 was 142, checked in by Kris Deugau, 20 years ago

/trunk

Wrap connectDB() function in a "module" so we don't get headaches
tracking password changes (if and when). This change *may* get
merged back to IPDB.pm, but it may not so as to allow the HTTP
username to be the user authenticated at the database level.

  • Property svn:executable set to *
  • Property svn:keywords set to Date Rev Author
File size: 4.3 KB
Line 
1#!/usr/bin/perl
2# ipdb/cgi-bin/consistency-check.pl
3# Does full check to see if the data in the db is consistent and complete.
4###
5# SVN revision info
6# $Date: 2005-01-28 19:04:55 +0000 (Fri, 28 Jan 2005) $
7# SVN revision $Rev: 142 $
8# Last update by $Author: kdeugau $
9###
10# Copyright (C) 2004 - Kris Deugau
11
12use DBI;
13use MyIPDB;
14use NetAddr::IP;
15
16($dbh,$errstr) = connectDB_My;
17
18# Schlep up the masters
19$sth = $dbh->prepare("select * from masterblocks order by cidr");
20$sth->execute;
21for ($i=0; @data = $sth->fetchrow_array; $i++) {
22 $masterblocks[$i] = new NetAddr::IP $data[0];
23}
24
25print "First check: All blocks must be within one of the master blocks\n";
26
27# First check - make sure ALL routes and allocated blocks are part
28# of one of the master blocks.
29print "Checking routed blocks: ";
30$sth = $dbh->prepare("select cidr from routed");
31# union select cidr from allocations union select cidr from freeblocks");
32$sth->execute;
33ROUTED: while (@data = $sth->fetchrow_array) {
34 $cidr = new NetAddr::IP $data[0];
35 foreach $master (@masterblocks) {
36 if ($master->contains($cidr)) { next ROUTED; }
37 }
38 print "$cidr not mastered\n";
39}
40print " done.\n";
41
42# Next test: All allocations must be part of a master.
43print "Checking allocations: ";
44$sth = $dbh->prepare("select cidr from allocations");
45$sth->execute;
46ALLOCATED: while (@data = $sth->fetchrow_array) {
47 $cidr = new NetAddr::IP $data[0];
48 foreach $master (@masterblocks) {
49 if ($master->contains($cidr)) { next ALLOCATED; }
50 }
51 print "$cidr not mastered\n";
52}
53print " done.\n";
54
55# Next: free blocks
56print "Checking freeblocks: ";
57$sth = $dbh->prepare("select cidr from freeblocks");
58$sth->execute;
59FREEBLOCK: while (@data = $sth->fetchrow_array) {
60 $cidr = new NetAddr::IP $data[0];
61 foreach $master (@masterblocks) {
62 if ($master->contains($cidr)) { next FREEBLOCK; }
63 }
64 print "$cidr not mastered\n";
65}
66print " done.\n";
67
68print "Done checking master containment.\n\nChecking pool containment...\n";
69
70$sth = $dbh->prepare("select pool,ip from poolips order by ip");
71$sth->execute;
72while (@data = $sth->fetchrow_array) {
73 $pool = new NetAddr::IP $data[0];
74 $ip = new NetAddr::IP $data[1];
75 print "IP $ip listed with incorrect pool $pool\n"
76 if !$pool->contains($ip);
77}
78$sth = $dbh->prepare("select distinct pool from poolips order by pool");
79$sth->execute;
80while (@data = $sth->fetchrow_array) {
81 $sth2 = $dbh->prepare("select cidr from allocations where cidr='$data[0]'");
82 $sth2->execute;
83 print "Pool $data[0] does not exist in allocations table\n"
84 if (($sth2->fetchrow_array)[0] eq '');
85}
86
87print "Done checking pool containment.\n\nChecking block-alignment consistency:\n";
88
89# Block alignment consistency: All allocated+free blocks within a master
90# must NOT overlap, and they must show no gaps.
91# eg, if we have blocks:
92# master is 192.168.2.0/24
93# allocated 192.168.2.0/29, 192.168.2.8/29, 192.168.2.64/26
94# free 192.168.2.16/28, 192.168.2.32/27, 192.168.2.128/25
95# then we're OK, but if any one of the allocated or free blocks is missing,
96# something b0rked.
97
98# (select cidr from allocations where cidr <<= '$master') union
99# (select cidr from freeblocks where cidr <<= '$master')
100# order by cidr
101
102foreach $master (@masterblocks) {
103 print "Master $master:\n";
104 $prev = $master;
105 $sth = $dbh->prepare("(select network(cidr) as net, broadcast(cidr) as bcast ".
106 " from allocations where cidr <<= '$master') union ".
107 "(select network(cidr) as net, broadcast(cidr) as bcast ".
108 "from freeblocks where cidr <<= '$master') order by net");
109 $sth->execute;
110
111 while (@data = $sth->fetchrow_array) {
112 $cur = new NetAddr::IP $data[0];
113
114 if ($master->numeric == $prev->numeric) {
115 # check if cur starts with master
116 if ($cur->numeric > $prev->numeric) {
117 print " Gap from start of master $master to first block $cur\n";
118 } elsif ($cur->numeric < $prev->numeric) {
119 print " BIG problem! Current block $cur begins before master $master!\n";
120 }
121 } else {
122 if ($cur->numeric < ($prev->numeric + 1)) {
123 print " Block ".$prev->network." overlaps block $cur\n";
124 } elsif ($cur->numeric > ($prev->numeric + 1)) {
125 print " Gap between end of block ".$prev->network." and block $cur\n";
126 }
127 }
128
129 $prev = $cur;
130 $prev--;
131
132 } # while (@data...)
133
134 $cur--;
135 $master--;
136 if ($cur->numeric ne $master->numeric) {
137 print " Gap from $cur to end of master at $master\n";
138 }
139 print "done $master.\n";
140}
Note: See TracBrowser for help on using the repository browser.