source: trunk/cgi-bin/import.pl@ 6

Last change on this file since 6 was 6, checked in by Kris Deugau, 21 years ago

Add intermediate development files for posterity
  • Property svn:executable set to *
File size: 14.1 KB
Line 
1#!/usr/bin/perl
2# Import data from old IPDB to new DB
3# Started 06/29/04 kdeugau@vianet
4
5#use warnings;
6#use CGI::Carp qw/fatalsToBrowser/;
7use DBI;
8#use DBD::Pg;
9#use CommonWeb qw/:ALL/;
10#use POSIX qw/ceil/;
11use NetAddr::IP;
12
13#use strict;
14
15$null = new NetAddr::IP "255.255.255.255/32";
16
17# Hardcode the masters because it's too messy and slow to
18# snag them programmatically *EVERY TIME*
19@masters = ((new NetAddr::IP "66.186.64.0/19"),
20 (new NetAddr::IP "204.138.172.0/24"),
21 (new NetAddr::IP "204.187.88.0/23"),
22 (new NetAddr::IP "205.207.184.0/23"),
23 (new NetAddr::IP "206.130.64.0/24"),
24 (new NetAddr::IP "209.91.128.0/18"),
25 (new NetAddr::IP "10.0.0.0/8"),
26 (new NetAddr::IP "192.168.0.0/16"),
27 );
28
29# First step: Slurp up data to find "master" blocks
30 my $DSN = "DBI:mysql:ipdb";
31 my $user = 'root';
32 my $pw = '';
33 $dbh = DBI->connect($DSN, $user, $pw) || return undef if(!$dbh);
34
35$newdbh = DBI->connect("DBI:Pg:dbname=ipdb", "ipdb", "");
36
37# Snag the static IPs (/32 and probably /31 (WTF?)
38$sth = $dbh->prepare("select * from blocks where netmask='/32' or netmask='/31'");
39$sth->execute;
40while (@data = $sth->fetchrow_array) {
41 $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
42 @staticblocks = $null->compact($cidr,@staticblocks);
43 $sth2 = $newdbh->prepare("insert into poolips values ('$cidr','$cidr','$data[4]','$data[3]','c','n','$data[7]\n$data[8]')");
44 $sth2->execute;
45 if ($sth2->err) { print "err on $cidr: ".$sth2->err."\tstring: ".$sth2->errstr."\n"; }
46}
47
48#foreach $static (@staticblocks) {
49# print "static: $static\n";
50#}
51
52$sth = $newdbh->prepare("insert into masterblocks values (?)");
53$sth2 = $newdbh->prepare("insert into freeblocks values (?,?,'Sudbury','n')");
54foreach $master (@masters) {
55 print "$master\n";
56 $sth->execute("$master");
57 $sth2->execute("$master",$master->masklen);
58}
59
60$i=0;
61$sth = $dbh->prepare("select * from blocks where netmask !='/32' and netmask !='/31'");
62$sth->execute;
63CIDR: while (@data = $sth->fetchrow_array) {
64# $blockdata[$i] = join '|',@data;
65# $cidrlist[$i] = new NetAddr::IP "$data[0].$data[2]$data[1]";
66 # data contains ipclass,netmask,subnet,city,custid,name,swip,description,notes
67 $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
68
69# Munge single-quotes in name, desc and notes
70 $data[5] =~ s/'/''/g;
71 $data[7] =~ s/'/''/g;
72 $data[8] =~ s/'/''/g;
73
74# Here's a better idea. Instead of just upcasing the city, mangle it "properly".
75# This also lets us correct mistakes in the source data... sort of.
76# $data[3] =~ tr/a-z/A-Z/;
77 if ($data[3] =~ /BARRIE/i) {
78 $data[3] = "Barrie";
79 } elsif ($data[3] =~ /BEAMSVILLE/i) {
80 $data[3] = "Beamsville";
81 } elsif ($data[3] =~ /BLIND RIVER/i) {
82 $data[3] = "Blind River";
83 } elsif ($data[3] =~ /BRACEBRIDGE/i) {
84 $data[3] = "Bracebridge";
85 } elsif ($data[3] =~ /BURK'?S FALLS/i) {
86 $data[3] = "Burk''s Falls";
87 } elsif ($data[3] =~ /BURLINGTON/i) {
88 $data[3] = "Burlington";
89 } elsif ($data[3] =~ /CAMBRIDGE/i) {
90 $data[3] = "Cambridge";
91 } elsif ($data[3] =~ /CHAPLEAU/i) {
92 $data[3] = "Chapleau";
93 } elsif ($data[3] =~ /ELLIOT LAKE/i) {
94 $data[3] = "Elliot Lake";
95 } elsif ($data[3] =~ /ESPANOLA/i) {
96 $data[3] = "Espanola";
97 } elsif ($data[3] =~ /GRAVENHURST/i) {
98 $data[3] = "Gravenhurst";
99 } elsif ($data[3] =~ /HAMILTON/i) {
100 $data[3] = "Hamilton";
101 } elsif ($data[3] =~ /HUNTSVILLE/i) {
102 $data[3] = "Huntsville";
103 } elsif ($data[3] =~ /INGERSOLL/i) {
104 $data[3] = "Ingersol";
105 } elsif ($data[3] =~ /KAPUSKASING/i) {
106 $data[3] = "Kapuskasing";
107 } elsif ($data[3] =~ /KIRKLAND LAKE/i) {
108 $data[3] = "Kirkland Lake";
109 } elsif ($data[3] =~ /LINDSAY/i) {
110 $data[3] = "Lindsay";
111 } elsif ($data[3] =~ /LITTLE CURRENT/i) {
112 $data[3] = "Little Current";
113 } elsif ($data[3] =~ /LIVELY/i) {
114 $data[3] = "Lively";
115 } elsif ($data[3] =~ /LONDON/i) {
116 $data[3] = "London";
117 } elsif ($data[3] =~ /MANITOULIN/i) {
118 $data[3] = "Manitoulin";
119 } elsif ($data[3] =~ /MARATHON/i) {
120 $data[3] = "Marathon";
121 } elsif ($data[3] =~ /MARKHAM/i) {
122 $data[3] = "Markham";
123 } elsif ($data[3] =~ /MISSISAUGA/i) {
124 $data[3] = "Missisauga";
125 } elsif ($data[3] =~ /MOONBEAM/i) {
126 $data[3] = "Moonbeam";
127 } elsif ($data[3] =~ /NEPEAN (OTTOWA)/i) {
128 $data[3] = "Nepean (Ottawa)";
129 } elsif ($data[3] =~ /NIPIGON/i) {
130 $data[3] = "Nipigon";
131 } elsif ($data[3] =~ /NORTH BAY/i) {
132 $data[3] = "North Bay";
133 } elsif ($data[3] =~ /^OTTAWA$/i) {
134 $data[3] = "Ottawa";
135 } elsif ($data[3] =~ /PARRY SOUND/i) {
136 $data[3] = "Parry Sound";
137 } elsif ($data[3] =~ /PEMBROKE/i) {
138 $data[3] = "Pembroke";
139 } elsif ($data[3] =~ /PORT CARLING/i) {
140 $data[3] = "Port Carling";
141 } elsif ($data[3] =~ /SAULT STe?.? MARIE/i) {
142 $data[3] = "Sault Ste. Marie";
143 } elsif ($data[3] =~ /STURGEON FALLS/i) {
144 $data[3] = "Sturgeon Falls";
145 } elsif ($data[3] =~ /SUCKER CREEK/i) {
146 $data[3] = "Sucker Creek";
147 } elsif ($data[3] =~ /SUDBURY/i) {
148 $data[3] = "Sudbury";
149 } elsif ($data[3] =~ /SUNDRIDGE/i) {
150 $data[3] = "Sundridge";
151 } elsif ($data[3] =~ /THESSALON/i) {
152 $data[3] = "Thessalon";
153 } elsif ($data[3] =~ /THUNDER BAY/i) {
154 $data[3] = "Thunder Bay";
155 } elsif ($data[3] =~ /TIMMINS/i) {
156 $data[3] = "Timmins";
157 } elsif ($data[3] =~ /TORONTO/i) {
158 $data[3] = "Toronto";
159 } elsif ($data[3] =~ /VALLEY/i) {
160 $data[3] = "Valley";
161 } elsif ($data[3] =~ /WALDEN/i) {
162 $data[3] = "Walden";
163 } elsif ($data[3] =~ /WARREN/i) {
164 $data[3] = "Warren";
165 } elsif ($data[3] =~ /WAWA/i) {
166 $data[3] = "Wawa";
167 } elsif ($data[3] =~ /WINDSOR/i) {
168 $data[3] = "Windsor";
169 }
170 $cities{$data[3]}=1;
171
172# figger out where the allocation comes from
173 $sth2 = $newdbh->prepare("select * from freeblocks");
174 $sth2->execute;
175 while (@data2 = $sth2->fetchrow_array) {
176 $free = new NetAddr::IP "$data2[0]";
177 if ($free->contains($cidr)) {
178 if ($free->masklen == $cidr->masklen) {
179 # delete from freeblocks
180 # add to allocations
181 $sth2 = $newdbh->prepare("delete from freeblocks where cidr='$cidr'");
182 $sth2->execute;
183if ($sth2->err) { print "error on delete $cidr from freeblocks: ".$sth2->errstr."\n"; }
184 # All data to be inserted as customer network. Cleanup will have to
185 # be manual due to the messiness of existing data. >:(
186 $sth2 = $newdbh->prepare("insert into allocations values ('$cidr','$data[4]',".
187 "'cn','$data[3]','$data[5]','$data[7]\n$data[8]',".$cidr->masklen.")");
188 $sth2->execute;
189#if ($sth2->err) { print "error on insert $cidr allocation: ".$sth2->errstr."\n"; }
190 } else { # end free == cidr
191 $searchme = $free;
192 $sth2 = $newdbh->prepare("delete from freeblocks where cidr='$searchme'");
193 $sth2->execute;
194 undef @freelist;
195 while ($searchme->masklen < $cidr->masklen) {
196 @temp = $searchme->split($searchme->masklen+1);
197 if ($temp[0]->contains($cidr)) {
198 $newfree = $temp[1];
199 $searchme = $temp[0];
200 } else {
201 $newfree = $temp[0];
202 $searchme = $temp[1];
203 }
204 push @freelist, ($newfree);
205 } #while
206 $sth2 = $newdbh->prepare("insert into allocations values ('$cidr','$data[4]',".
207 "'cn','$data[3]','$data[5]','$data[7]\n$data[8]',".$cidr->masklen.")");
208 $sth2->execute;
209if ($sth2->err) { print "error on insert $cidr allocation: ".$sth2->errstr."\n"; }
210 $sth2 = $newdbh->prepare("insert into freeblocks values (?,?,'Sudbury','n')");
211 foreach $ip (@freelist) {
212 $sth2->execute("$ip",$ip->masklen);
213#print "insert into freeblocks values ('$ip',".$ip->masklen.",'Sudbury','n')\n";
214if ($sth2->err) { print "error on insert $ip freeblock: ".$sth2->errstr."\n"; }
215 }
216
217 } # free != cidr
218 next CIDR;
219 } # free contains cidr
220 }
221
222 $i++;
223}
224
225$sth = $dbh->prepare("select * from blocks where netmask ='/24'");
226$sth->execute;
227while (@data = $sth->fetchrow_array) {
228# $blockdata[$i] = join '|',@data;
229# $cidrlist[$i] = new NetAddr::IP "$data[0].$data[2]$data[1]";
230 # data contains ipclass,netmask,subnet,city,custid,name,swip,description,notes
231 $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
232
233# Here's a better idea. Instead of just upcasing the city, mangle it "properly".
234# This also lets us correct mistakes in the source data... sort of.
235# $data[3] =~ tr/a-z/A-Z/;
236 if ($data[3] =~ /BARRIE/i) {
237 $data[3] = "Barrie";
238 } elsif ($data[3] =~ /BEAMSVILLE/i) {
239 $data[3] = "Beamsville";
240 } elsif ($data[3] =~ /BLIND RIVER/i) {
241 $data[3] = "Blind River";
242 } elsif ($data[3] =~ /BRACEBRIDGE/i) {
243 $data[3] = "Bracebridge";
244 } elsif ($data[3] =~ /BURK'?S FALLS/i) {
245 $data[3] = "Burk's Falls";
246 } elsif ($data[3] =~ /BURLINGTON/i) {
247 $data[3] = "Burlington";
248 } elsif ($data[3] =~ /CAMBRIDGE/i) {
249 $data[3] = "Cambridge";
250 } elsif ($data[3] =~ /CHAPLEAU/i) {
251 $data[3] = "Chapleau";
252 } elsif ($data[3] =~ /ELLIOT LAKE/i) {
253 $data[3] = "Elliot Lake";
254 } elsif ($data[3] =~ /ESPANOLA/i) {
255 $data[3] = "Espanola";
256 } elsif ($data[3] =~ /GRAVENHURST/i) {
257 $data[3] = "Gravenhurst";
258 } elsif ($data[3] =~ /HAMILTON/i) {
259 $data[3] = "Hamilton";
260 } elsif ($data[3] =~ /HUNTSVILLE/i) {
261 $data[3] = "Huntsville";
262 } elsif ($data[3] =~ /INGERSOLL/i) {
263 $data[3] = "Ingersol";
264 } elsif ($data[3] =~ /KAPUSKASING/i) {
265 $data[3] = "Kapuskasing";
266 } elsif ($data[3] =~ /KIRKLAND LAKE/i) {
267 $data[3] = "Kirkland Lake";
268 } elsif ($data[3] =~ /LINDSAY/i) {
269 $data[3] = "Lindsay";
270 } elsif ($data[3] =~ /LITTLE CURRENT/i) {
271 $data[3] = "Little Current";
272 } elsif ($data[3] =~ /LIVELY/i) {
273 $data[3] = "Lively";
274 } elsif ($data[3] =~ /LONDON/i) {
275 $data[3] = "London";
276 } elsif ($data[3] =~ /MANITOULIN/i) {
277 $data[3] = "Manitoulin";
278 } elsif ($data[3] =~ /MARATHON/i) {
279 $data[3] = "Marathon";
280 } elsif ($data[3] =~ /MARKHAM/i) {
281 $data[3] = "Markham";
282 } elsif ($data[3] =~ /MISSISAUGA/i) {
283 $data[3] = "Missisauga";
284 } elsif ($data[3] =~ /MOONBEAM/i) {
285 $data[3] = "Moonbeam";
286 } elsif ($data[3] =~ /NEPEAN (OTTOWA)/i) {
287 $data[3] = "Nepean (Ottawa)";
288 } elsif ($data[3] =~ /NIPIGON/i) {
289 $data[3] = "Nipigon";
290 } elsif ($data[3] =~ /NORTH BAY/i) {
291 $data[3] = "North Bay";
292 } elsif ($data[3] =~ /^OTTAWA$/i) {
293 $data[3] = "Ottawa";
294 } elsif ($data[3] =~ /PARRY SOUND/i) {
295 $data[3] = "Parry Sound";
296 } elsif ($data[3] =~ /PEMBROKE/i) {
297 $data[3] = "Pembroke";
298 } elsif ($data[3] =~ /PORT CARLING/i) {
299 $data[3] = "Port Carling";
300 } elsif ($data[3] =~ /SAULT STe?.? MARIE/i) {
301 $data[3] = "Sault Ste. Marie";
302 } elsif ($data[3] =~ /STURGEON FALLS/i) {
303 $data[3] = "Sturgeon Falls";
304 } elsif ($data[3] =~ /SUCKER CREEK/i) {
305 $data[3] = "Sucker Creek";
306 } elsif ($data[3] =~ /SUDBURY/i) {
307 $data[3] = "Sudbury";
308 } elsif ($data[3] =~ /SUNDRIDGE/i) {
309 $data[3] = "Sundridge";
310 } elsif ($data[3] =~ /THESSALON/i) {
311 $data[3] = "Thessalon";
312 } elsif ($data[3] =~ /THUNDER BAY/i) {
313 $data[3] = "Thunder Bay";
314 } elsif ($data[3] =~ /TIMMINS/i) {
315 $data[3] = "Timmins";
316 } elsif ($data[3] =~ /TORONTO/i) {
317 $data[3] = "Toronto";
318 } elsif ($data[3] =~ /VALLEY/i) {
319 $data[3] = "Valley";
320 } elsif ($data[3] =~ /WALDEN/i) {
321 $data[3] = "Walden";
322 } elsif ($data[3] =~ /WARREN/i) {
323 $data[3] = "Warren";
324 } elsif ($data[3] =~ /WAWA/i) {
325 $data[3] = "Wawa";
326 } elsif ($data[3] =~ /WINDSOR/i) {
327 $data[3] = "Windsor";
328 }
329
330# insert entry in routed table for /24 allocations. We'll clean it up later.
331 if ($cidr->masklen == 24) {
332 $sth2 = $newdbh->prepare("insert into routed values ".
333 "('$cidr',".$cidr->masklen.",'$data[3]')");
334#print "insert into routed values ('$cidr',".$cidr->masklen.",'$data[3]')\n";
335 $sth2->execute;
336 if ($sth2->err) { print "error on insert $cidr route: ".$sth2->errstr."\n"; }
337 }
338}
339
340
341
342$i=0;
343#foreach $data (@blockdata) {
344# @bits = split /\|/,$data;
345# # ipclass,mask,subnet,city,custid,customer name,swip,desc,notes
346## print $data[0];
347# if ($bits[0] =~ /\d{1,3}\.\d{1,3}\.\d{1,3}/) {
348## $bits[0] .= ".0/24";
349# $cidrlist[$i++] = new NetAddr::IP "$bits[0].$bits[2]$bits[1]";
350## print $cidrlist[$i-1]."\n";
351# } else {
352# print "odd: $data[0].$data[2]$data[1]\n";
353# }
354## $bits[3] = s/\s+//g;
355# $bits[3] = tr/a-z/A-Z/;
356# $cities{$bits[3]}=1;
357#}
358
359@citylist = keys %cities;
360
361print "done first slurp\n";
362
363# snag master blocks from list of IPs.
364#$i=0;
365#$sth = $dbh->prepare("select * from ips");
366#$sth->execute;
367#while (@data = $sth->fetchrow_array) {
368# my $cidr = new NetAddr::IP "$data[0]/32";
369# if ($data[1] eq 1) {
370# @usedlist = $cidr->compact(@usedlist);
371# } else {
372# @freelist = $cidr->compact(@freelist);
373# }
374# $i++;
375# if (($i%500)==0) { print "."; }
376#}
377#@masters2 = $null->compact(@freelist,@usedlist);
378#foreach $temp (@masters2) {
379# print "altmaster: $temp\n";
380#}
381
382#open FREELIST,">freelist";
383#foreach $freeip (@freelist) {
384# print FREELIST "$freeip\n";
385#}
386#open USEDLIST,">usedlist";
387#foreach $usedip (@usedlist) {
388# print USEDLIST "$usedip\n";
389#}
390
391exit 0;
392
393# Hokay. Playing with the raw data doesn't seem to be working. :/
394
395$sth = $dbh->prepare("select * from blocks where city=? and netmask != '/32'");
396foreach $city (@citylist) {
397 print "$city\n";
398 $sth->execute($city);
399 my $i=0;
400 my @routelist;
401 while (@data = $sth->fetchrow_array) {
402 $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
403# print " $cidr\n";
404 $errcode = eval { @routelist = $null->compact($cidr,@routelist); };
405 if ($@) { print "compact $cidr broke: ($errcode) $@\n"; }
406#print "$cidr:\n";
407# print "\t$data[0]\n";
408#print "$data[0] $cidr\n";
409 }
410# foreach $route (@routelist) {
411# print " $route\n";
412# }
413 #eval { unset $routelist; };
414}
415
416#foreach $city (@citylist) {
417# foreach $cidrcitylist (@cidrlist{$city}) {
418# print "$city: $cidrcitylist[1]\n";
419# }
420#}
421
422exit 0;
423
424foreach $block (@blockdata) {
425 @bits = split /\|/, $block;
426#print $bits[3]."\t";
427 foreach $city (@citylist) {
428# print "$city:\n";
429 if ($city eq $bits[3]) {
430 $cidr = new NetAddr::IP "$bits[0].$bits[2]$bits[1]";
431 @roughroutes{$city} = $null->compact($cidr, @roughroutes{$city});
432#print $block."\n";
433#print "$#roughroutes $roughroutes[0] $cidr\n";
434#print "$city\t$bits[3]\n";
435# print $city."\n";
436 }
437 }
438# print "$city: $#(roughroutes{$city})\n";
439# unset $roughroutes;
440}
441
442foreach $city (@citylist) {
443 foreach $route (@roughroutes{$city}) {
444 print "$city route: $route\n";
445 }
446}
Note: See TracBrowser for help on using the repository browser.