source: trunk/cgi-bin/db-update.pl@ 788

Last change on this file since 788 was 772, checked in by Kris Deugau, 9 years ago

/trunk

Tweak 2.7 -> 3.0 update script to fill in the custid on routed and master blocks

  • Property svn:executable set to *
  • Property svn:keywords set to Date Rev Author Id
File size: 5.8 KB
Line 
1#!/usr/bin/perl
2# Quick sort of hack to populate new columns in DB
3
4use strict;
5use warnings;
6use Data::Dumper;
7
8use MyIPDB;
9
10# run the tabledef update first. This is "safe"; IPDB < 3.0 can still happily munch away at it.
11print qx { PGPASSWORD=ipdbpwd psql -h localhost -U ipdb ipdb <ipdb-2.7-3.0.sql };
12
13my $dbh;
14my $errstr;
15($dbh,$errstr) = connectDB_My;
16my $verbose = 0;
17
18die "db conn failed: $errstr\n" if !$dbh;
19
20initIPDBGlobals($dbh);
21
22local $dbh->{AutoCommit} = 0;
23local $dbh->{RaiseError} = 1;
24
25eval {
26
27 my $get_id = $dbh->prepare("SELECT currval('allocations_id_seq')");
28
29 # master blocks move to the allocations table
30 my $getm = $dbh->prepare("SELECT cidr,ctime,mtime,rwhois FROM masterblocks");
31 my $insm = $dbh->prepare("INSERT INTO allocations (cidr,type,createstamp,modifystamp,swip,custid) VALUES (?,'mm',?,?,?,?)");
32
33 # routed blocks move to the allocations table
34 my $getr = $dbh->prepare("SELECT cidr,city FROM routed WHERE cidr <<= ?");
35 my $insr = $dbh->prepare("INSERT INTO allocations (cidr,type,city,parent_id,custid) VALUES (?,'rm',?,?,?)");
36 my $rfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='y'");
37
38 # update freeblocks with new parent relation info
39 my $mfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='n'");
40 my $setm_f = $dbh->prepare("UPDATE freeblocks SET master_id = ? WHERE cidr <<= ?");
41 my $cfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='c' WHERE cidr <<= ?");
42
43 # update allocations with new parent relation info
44 my $getc = $dbh->prepare("SELECT cidr,type,id FROM allocations WHERE cidr <<= ? AND type LIKE '_c'");
45 my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ?");
46 my $updalloc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND NOT (type='rm' OR type='mm')");
47 my $updc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND type LIKE '_r'");
48
49 # update poolips with new parent relation info
50 my $getp = $dbh->prepare("SELECT cidr,id,master_id FROM allocations WHERE type LIKE '_d' OR type LIKE '_p'");
51 my $updpool = $dbh->prepare("UPDATE poolips SET parent_id = ?, master_id = ? WHERE ip << ?");
52
53 # "spare" freeblocks that are technically part of a container, but whose formal container parent
54 # isn't actually present. Arguably these could autoconvert the parent to a container, but IIRC
55 # in some cases live data has a mix of types in a container. *sigh*.
56 my $sparef = $dbh->prepare("SELECT cidr,id FROM freeblocks WHERE parent_id = 0");
57 my $fparent = $dbh->prepare("UPDATE freeblocks SET parent_id = ".
58 "(SELECT id FROM allocations WHERE cidr >>= ? ORDER BY masklen(cidr) DESC LIMIT 1)".
59 " WHERE id = ?");
60
61 # Need to disable the update trigger on the allocations and poolips tables,
62 # so we don't mangle the real mtimes on the data.
63 $dbh->do("DROP TRIGGER up_modtime ON allocations");
64 $dbh->do("DROP TRIGGER up_modtime ON poolips");
65
66 $getm->execute;
67 while (my ($master,$mctime,$mmtime,$mswip) = $getm->fetchrow_array()) {
68 print "$master\t";
69 # copy master to allocations table
70 $insm->execute($master, $mctime, $mmtime, $mswip, $def_custids{'mm'});
71 $get_id->execute;
72 my ($mid) = $get_id->fetchrow_array();
73 print "$mid\n";
74 # parent relation for free blocks directly under the master
75 $mfree->execute($mid, $master);
76
77 $getr->execute($master);
78 while (my ($routed,$rcity) = $getr->fetchrow_array()) {
79 print " $routed\t";
80 # copy routed to allocations table
81 $insr->execute($routed, $rcity, $mid, $def_custids{'rm'});
82 $get_id->execute;
83 my ($rid) = $get_id->fetchrow_array();
84 print "$rid\n";
85 # parent relation for free blocks directly under the routed block
86 $rfree->execute($rid, $routed);
87 # parent relation for allocations in the routed block
88 $updalloc->execute($rid, $routed);
89 $getc->execute($routed);
90 while (my ($container, $ctype, $cid) = $getc->fetchrow_array()) {
91 print " $container";
92 # container blocks are now functionally equivalent to routed blocks;
93 # update the parent relations on the contained blocks to treat the
94 # container as the parent, not the routed block
95 $updc->execute($cid, $container);
96 my $c = $cfree->execute($cid, $container);
97 }
98 }
99 # Just In Case. Bulk-set the master ID on all allocations, then freeblocks,
100 # within the master. This could theoretically be merged into the updates
101 # above, but edge cases kept happening.
102 $setm_a->execute($mid, $master);
103 $setm_f->execute($mid, $master);
104 }
105
106 # Update poolips with new parent and master relation info
107 $getp->execute();
108 while (my ($pool, $pid, $pmaster) = $getp->fetchrow_array()) {
109 $updpool->execute($pid, $pmaster, $pool);
110 }
111
112 # clean up "spare" freeblocks from formally-incorrect use of container types
113 $sparef->execute();
114 while (my ($free, $fid) = $sparef->fetchrow_array()) {
115 $fparent->execute($free, $fid)
116 }
117
118 if ($ARGV[0] && $ARGV[0] == 'fetchdns') {
119 # weesa gotsa BIIIIG job, gotta fetch all the current rDNS. note this relies
120 # on having had the DNS data imported and munged into the new pseudotypes.
121 # ... never mind. this will be opportunistic, since DNS data isn't viewed
122 # unless adding (not present, we should hope!) or updating (where it should
123 # be retrieved from the authoritative API in real time)
124 }
125
126 # Recreate modtime trigger on allocations, now that we're done monkeying with it.
127 $dbh->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations FOR EACH ROW EXECUTE PROCEDURE up_modtime()");
128 $dbh->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips FOR EACH ROW EXECUTE PROCEDURE up_modtime()");
129
130 $dbh->commit;
131}; # all wrapped up in an eval{} so we can roll it all back when we want to
132if ($@) {
133 print "ebbeh? $@\n";
134}
Note: See TracBrowser for help on using the repository browser.