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

Last change on this file since 951 was 906, checked in by Kris Deugau, 7 years ago

/trunk

Bulk addition of "add 'the directory the script is in' to @INC" for Perls
that have dropped '.' from @INC

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