Changeset 325 for branches


Ignore:
Timestamp:
04/06/06 15:53:25 (19 years ago)
Author:
Kris Deugau
Message:

/branches/stable

Merge rWHOIS update from /trunk r324.

Location:
branches/stable/cgi-bin
Files:
2 edited
3 copied

Legend:

Unmodified
Added
Removed

  • branches/stable/cgi-bin/extras/db2rwhois.pl

    r308 r325  
    2828
    2929my @masterblocks;
     30my %netnameprefix;
    3031
    3132# Fill in data about our master blocks as allocated from ARIN
    … …  
    3334# Note that this ASS-U-MEs that we do not add master IP blocks-
    3435# there should probably be a separate system for doing that.
    35 my $sth = $dbh->prepare("select cidr,ctime from masterblocks;");
     36my $sth = $dbh->prepare("select cidr,ctime,mtime from masterblocks;");
    3637$sth->execute;
    3738my $i=0;
    … …  
    4546  $masterblocks[$i] = new NetAddr::IP $data[0];
    4647  my ($ctime,undef) = split /\s/, $data[1];
    47 
    48 print "$masterblocks[$i] $data[1]\n";
     48  my ($mtime,undef) = split /\s/, $data[2];
     49print "$masterblocks[$i] $ctime $mtime\n";
    4950
    5051  my $date;
    … …  
    6465        "IP-Network: $masterblocks[$i]\n".
    6566        "IP-Network-Block: ".$masterblocks[$i]->range."\n".
    66         "Organization: Friendly ISP\n".
    67         "Tech-Contact: noc\@example.com\n".
    68         "Admin-Contact: ISP-ARIN-HANDLE\n".
    69         "Abuse-Contact: abuse\@example.com\n".
     67        "Org-Name: Friendly ISP\n".
     68        "Street-Address: 123 4th Street\n".
     69        "City: Anytown\n".
     70        "StateProv: Ontario\n".
     71        "Postal-Code: H0H 0H0\n".
     72        "Country-Code: CA\n".
     73        "Tech-Contact: ISP-ARIN-HANDLE\n".
    7074        "Created: $ctime\n".
    71         "Updated: $date\n".
     75        "Updated: $mtime\n".
    7276        "Updated-By: noc\@example.com\n";
    7377
    … …  
    7680}
    7781
     82# prefetch alloctype data
     83$sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes where listorder <500");
     84$sth->execute;
     85while (my @data = $sth->fetchrow_array) {
     86  $netnameprefix{$data[0]} = $data[2];
     87}
     88
    7889# Now read out the data in the "main" delegation list, and check it
    7990# with the master blocks.  We need to do this to decide which rWHOIS
    … …  
    8394# No use or point in broadcasting our use of them.
    8495# Also remove the details of our "reserved CORE/WAN" blocks;  they're not critical.
    85 $sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp ".
     96$sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ".
    8697        "from allocations where ".
    8798        "not (cidr <<= '192.168.0.0/16') and ".
    … …  
    93104$sth->execute;
    94105
     106my $custsth = $dbh->prepare("select name,street,city,province,country,pocode,phone,tech_handle,special from customers where custid=?");
     107
    95108$i=0;
    96 while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime) = $sth->fetchrow_array) {
     109while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $sth->fetchrow_array) {
    97110
    98111# We get master block info from @masterblocks.
    … …  
    134147      ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/);
    135148
    136       print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
    137         "Auth-Area: $master\n".
    138         "Network-Name: ISP-".$net->network."\n".
    139         "IP-Network: $net\n".
    140         "IP-Network-Block: ".$net->range."\n".
    141         "Organization: $desc\n".
    142 #       "Tech-Contact: $data[9]\n".
    143         "Tech-Contact: abuse\@example.com\n".
    144         "Admin-Contact: ISP-ARIN-HANDLE\n".
    145         "Created: $ctime\n".
    146         "Updated: $mtime\n".
    147         "Updated-By: noc\@example.com\n";
    148     }
    149   }
     149# Notes:
     150# Network-name should contain some component of "description"
     151# Cust address/contact data should be included;  NB, no phone for ARIN!
     152#  network:ID: NET-WIDGET
     153#  network:Network-Name: WIDGET                 [IPDB description, sort of]
     154#  network:IP-Network: 10.1.1.0/24
     155#  network:Org-Name: Widget Corp                [Cust name;  from billing?]
     156#  network:Street-Address: 211 Oak Drive        [May need more than one line, OR...]
     157#  network:City: Pineville                      [...this line...]
     158#  network:StateProv: WI                        [...and this line...]
     159#  network:Postal-Code: 48888                   [...and this line]
     160#  network:Country-Code: US
     161#  network:Tech-Contact: BZ142-MYRWHOIS         [ARIN handle?]
     162#  network:Updated: 19991221                    [timestamp from db]
     163#  network:Updated-By: jo@myrwhois.net          [noc@example, since that's our POC for IP netspace issues]
     164#  network:Class-Name:network                   [Provided by rWHOIS protocol]
     165
     166      my $netname = $netnameprefix{$type};
     167
     168      if ($swip eq 'n') {
     169        print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
     170                "Auth-Area: $master\n".
     171                "Network-Name: $netname-".$net->network."\n".
     172                "IP-Network: $net\n".
     173                "IP-Network-Block: ".$net->range."\n".
     174                "Org-Name: Friendly ISP\n".
     175                "Street-Address: 123 4th Street\n".
     176                "City: Anytown\n".
     177                "StateProv: Ontario\n".
     178                "Postal-Code: H0H 0H0\n".
     179                "Country-Code: CA\n".
     180                "Tech-Contact: ISP-ARIN-HANDLE\n".
     181                "Created: $ctime\n".
     182                "Updated: $mtime\n".
     183                "Updated-By: noc\@example.com\n";
     184      } else {
     185        $custsth->execute($custid);
     186        my ($name, $street, $city, $prov, $country, $pocode, $phone, $tech, $special) = $custsth->fetchrow_array;
     187        $custsth->finish;
     188        if ($special && $special =~ /NetName/ && $special =~ /$cidr/) {
     189          ($netname) = ($special =~ /NetName$cidr: ([A-Z0-9_-]+)/);
     190        } else {
     191          $netname .= "-".$net->network;
     192        }
     193        print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
     194                "Auth-Area: $master\n".
     195                "Network-Name: $netname\n".
     196                "IP-Network: $net\n".
     197                "IP-Network-Block: ".$net->range."\n".
     198                "Org-Name: $name\n".
     199                "Street-Address: $street\n".
     200                "City: $city\n".
     201                "StateProv: $prov\n".
     202                "Postal-Code: $pocode\n".
     203                "Country-Code: $country\n".
     204                "Tech-Contact: $tech\n".
     205                "Created: $ctime\n".
     206                "Updated: $mtime\n".
     207                "Updated-By: noc\@example.com\n";
     208      } # swip
     209
     210    } # net in master
     211  } # foreach master
    150212
    151213

  • branches/stable/cgi-bin/ipdb.psql

    r323 r325  
    1010        "city" character varying(30),
    1111        "province" character(2),
     12        "country" character(2),
    1213        "pocode" character varying(7),
    1314        "phone" character varying(15),
    14         "abuse" character varying(50),
     15        "tech_handle" character varying(50),
     16        "abuse_handle" character varying(50),
     17        "admin_handle" character varying(50),
    1518        "def_rdns" character varying(40),
    16         "description" text,
     19        "special" text,
    1720        Constraint "customers_pkey" Primary Key ("custid")
    1821);
    1922
    2023REVOKE ALL on "customers" from PUBLIC;
    21 GRANT ALL on "customers" to "kdeugau";
    2224GRANT ALL on "customers" to "ipdb";
    2325
    2426CREATE TABLE "masterblocks" (
    25         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY
     27        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
     28        "ctime" timestamp DEFAULT now(),
     29        "mtime" timestamp DEFAULT now()
    2630);
    2731
    2832REVOKE ALL on "masterblocks" from PUBLIC;
    29 GRANT ALL on "masterblocks" to "kdeugau";
    3033GRANT ALL on "masterblocks" to "ipdb";
    3134
    … …  
    3336        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
    3437        "maskbits" integer DEFAULT 128,
    35         "city" character varying(30) DEFAULT ''
     38        "city" character varying(30) DEFAULT '',
     39        "ctime" timestamp DEFAULT now()
    3640);
    3741
    3842REVOKE ALL on "routed" from PUBLIC;
    39 GRANT ALL on "routed" to "kdeugau";
    4043GRANT ALL on "routed" to "ipdb";
     44GRANT SELECT on "routed" to "ipdb";
    4145
    4246CREATE TABLE "temp" (
    … …  
    4549
    4650REVOKE ALL on "temp" from PUBLIC;
    47 GRANT ALL on "temp" to "kdeugau";
    4851GRANT ALL on "temp" to "ipdb";
    4952
    … …  
    5659
    5760REVOKE ALL on "freeblocks" from PUBLIC;
    58 GRANT ALL on "freeblocks" to "kdeugau";
    5961GRANT ALL on "freeblocks" to "ipdb";
    6062
    … …  
    7072        "circuitid" character varying(128) DEFAULT '' NOT NULL,
    7173        "privdata" text DEFAULT '' NOT NULL,
    72         "newcustid" integer,
     74        "newcustid" character varying(16) DEFAULT '',
     75        "createstamp" timestamp DEFAULT now(),
     76        "modifystamp" timestamp DEFAULT now(),
    7377        CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
    7478);
    7579
    7680REVOKE ALL on "poolips" from PUBLIC;
    77 GRANT ALL on "poolips" to "kdeugau";
    7881GRANT ALL on "poolips" to "ipdb";
    7982
    … …  
    8790        "maskbits" integer DEFAULT 128,
    8891        "circuitid" character varying(128) DEFAULT '',
     92        "createstamp" timestamp DEFAULT now(),
     93        "modifystamp" timestamp DEFAULT now(),
    8994        "privdata" text DEFAULT '' NOT NULL,
    90         "newcustid" integer
     95        "newcustid" character varying(16) DEFAULT '',
     96        swip character(1) DEFAULT 'n'
    9197);
    9298
    9399REVOKE ALL on "allocations" from PUBLIC;
    94 GRANT ALL on "allocations" to "kdeugau";
    95100GRANT ALL on "allocations" to "ipdb";
    96101
    97 CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.oldcustid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.oldcustid FROM poolips;
     102CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes FROM poolips;
    98103
    99104REVOKE ALL on "searchme" from PUBLIC;
    100 GRANT ALL on "searchme" to "kdeugau";
    101105GRANT ALL on "searchme" to "ipdb";
    102106
    … …  
    106110        "dispname" character varying(40) DEFAULT '',
    107111        "listorder" integer DEFAULT 0,
    108         "def_custid" character varying(16) DEFAULT ''
     112        "def_custid" character varying(16) DEFAULT '',
     113        "arin_netname" character varying(20) DEFAILT 'ISP'
    109114);
    110115
     116--
     117-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
     118--
     119
     120COPY "alloctypes" FROM stdin;
     121cn      Customer netblock       Customer netblock       0               ISPCUST
     122si      Static IP - Server pool Server pool IP  20      6750400 ISP
     123ci      Static IP - Cable       Static cable IP 21              ISP
     124di      Static IP - DSL Static DSL IP   22              ISP
     125mi      Static IP - Dialup      Static dialup IP        23              ISP
     126wi      Static IP - Wireless    Static wireless IP      24              ISP
     127sd      Static Pool - Servers   Server pool     40      6750400 ISP
     128cd      Static Pool - Cable     Cable pool      41      CBL-BUS ISP-STATIC-CABLE
     129dp      Static Pool - DSL       DSL pool        42      DSL-BUS ISP-STATIC-DSL
     130mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS        ISP-STATIC-DIAL
     131wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS  ISP-STATIC-WIFI
     132en      End-use netblock        End-use netblock        100     6750400 ISP
     133me      Dialup netblock Dialup netblock 101     DIAL-RES        ISP-DIAL
     134de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES ISP-DSL
     135ce      Dynamic cable block     Dynamic cable block     103     CBL-RES ISP-CABLE
     136we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES  ISP-WIFI
     137ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP        ISP
     138li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400 ISP
     139ld      Static Pool - LAN/POP   LAN pool        191     6750400 ISP
     140wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400 ISP
     141pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400 ISP-STATIC-DSL
     142ac      Reserve for ATM ATM blocks      202     6750400 ISP
     143wr      CORE/WAN block  CORE/WAN block  220     6750400 ISP
     144pr      Dynamic-route DSL netblock      Dynamic-route DSL       221             ISP
     145ar      ATM block       ATM block       222             ISP
     146rm      Routing Routed netblock 500     6750400 ISP
     147in      Internal netblock       Internal netblock       990     6750400 ISP
     148mm      Master block    Master block    999     6750400 ISP
     149\.
     150
    111151REVOKE ALL on "alloctypes" from PUBLIC;
    112 GRANT ALL on "alloctypes" to "kdeugau";
    113152GRANT ALL on "alloctypes" to "ipdb";
    114153
    115154CREATE TABLE "cities" (
    116         "city" character varying(30) DEFAULT '' NOT NULL PRIMARY KEY,
     155        "id" serial NOT NULL PRIMARY KEY,
     156        "city" character varying(30) DEFAULT '' NOT NULL,
    117157        "routing" character(1) DEFAULT 'n' NOT NULL
    118158);
    119159
    120160REVOKE ALL on "cities" from PUBLIC;
    121 GRANT ALL on "cities" to "kdeugau";
    122161GRANT ALL on "cities" to "ipdb";
    123162
    124163--
    125 -- Selected TOC Entries:
     164-- Trigger and matching function to update modifystamp on allocations, poolips
    126165--
    127 \connect - ipdb
     166CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
     167    BEGIN
     168        NEW.modifystamp := ''now'';
     169        RETURN NEW;
     170    END;
     171' LANGUAGE 'plpgsql';
    128172
    129 --
    130 -- TOC Entry ID 2 (OID 92809)
    131 --
    132 -- Name: alloctypes Type: TABLE Owner: ipdb
    133 --
     173CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
     174    FOR EACH ROW EXECUTE PROCEDURE up_modtime();
    134175
    135 CREATE TABLE "alloctypes" (
    136         "type" character(2) DEFAULT '' NOT NULL,
    137         "listname" character varying(40) DEFAULT '',
    138         "dispname" character varying(40) DEFAULT '',
    139         "listorder" integer DEFAULT 0,
    140         "def_custid" character varying(16) DEFAULT '',
    141         Constraint "alloctypes_pkey" Primary Key ("type")
    142 );
    143 
    144 --
    145 -- TOC Entry ID 3 (OID 92809)
    146 --
    147 -- Name: alloctypes Type: ACL Owner:
    148 --
    149 
    150 REVOKE ALL on "alloctypes" from PUBLIC;
    151 GRANT ALL on "alloctypes" to "kdeugau";
    152 GRANT ALL on "alloctypes" to "ipdb";
    153 
    154 --
    155 -- Data for TOC Entry ID 4 (OID 92809)
    156 --
    157 -- Name: alloctypes Type: TABLE DATA Owner: ipdb
    158 --
    159 
    160 
    161 COPY "alloctypes" FROM stdin;
    162 cd      Static Pool - Cable     Cable pool      41      CBL-BUS
    163 dp      Static Pool - DSL       DSL pool        42      DSL-BUS
    164 mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS
    165 wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS
    166 mm      Master block    Master block    999     6750400
    167 in      Internal netblock       Internal netblock       990     6750400
    168 sd      Static Pool - Servers   Server pool     40      6750400
    169 cn      Customer netblock       Customer netblock       0       
    170 ci      Static IP - Cable       Static cable IP 21     
    171 di      Static IP - DSL Static DSL IP   22     
    172 mi      Static IP - Dialup      Static dialup IP        23     
    173 wi      Static IP - Wireless    Static wireless IP      24     
    174 si      Static IP - Server pool Server pool IP  20      6750400
    175 wc      Reserve for WAN blocks  WAN IP blocks   200     6750400
    176 wr      Internal WAN block      Internal WAN block      201     6750400
    177 pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 202     6750400
    178 en      End-use netblock        End-use netblock        100     6750400
    179 me      Dialup netblock Dialup netblock 101     DIAL-RES
    180 de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES
    181 ce      Dynamic cable block     Dynamic cable block     103     CBL-RES
    182 we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES
    183 rm      Routing Routed netblock 500     6750400
    184 pr      Dynamic-route DSL netblock      Dynamic-route DSL       203     
    185 li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400
    186 ld      Static Pool - LAN/POP   LAN pool        191     6750400
    187 \.
    188 
     176CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
     177    FOR EACH ROW EXECUTE PROCEDURE up_modtime();
    189178
    190179--
    … …  
    197186        "acl" varchar(16) DEFAULT 'b'
    198187);
     188
     189CREATE TABLE "dns" (
     190        "ip" inet NOT NULL PRIMARY KEY,
     191        "hostname" character varying(128),
     192        "auto" character(1) DEFAULT 'y'
     193);
Note: See TracChangeset for help on using the changeset viewer.