tags/0.4.3.1-pre2/utils/country-table3.pl

   1 #!/usr/bin/perl
   2
   3 #       This file is part of SurrealServices.
   4 #
   5 #       SurrealServices is free software; you can redistribute it and/or modify
   6 #       it under the terms of the GNU General Public License as published by
   7 #       the Free Software Foundation; either version 2 of the License, or
   8 #       (at your option) any later version.
   9 #
  10 #       SurrealServices is distributed in the hope that it will be useful,
  11 #       but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 #       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 #       GNU General Public License for more details.
  14 #
  15 #       You should have received a copy of the GNU General Public License
  16 #       along with SurrealServices; if not, write to the Free Software
  17 #       Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  18
  19 #  SurrealChat.net does not provide the Country/Allocation data,
  20 #  is in no way associated with maxmind.com,
  21 #  nor are we providing a license to download/use it.
  22 #  Be sure to direct availability/accuracy/licensing questions to maxmind.com
  23
  24 use strict;
  25 use DBI;
  26
  27 BEGIN {
  28         use Cwd qw( abs_path getcwd );
  29         use File::Basename;
  30         my %constants = (
  31                 CWD => getcwd(),
  32                 PREFIX => abs_path(dirname(abs_path($0)).'/..'),
  33         );
  34         require constant; import constant(\%constants);
  35 }
  36 chdir PREFIX;
  37 use lib PREFIX;
  38
  39 use Date::Parse;
  40
  41 use SrSv::Conf::sql;
  42 use SrSv::Conf2Consts qw( sql );
  43
  44 use constant {
  45         countrydb_url => 'rsync://countries-ns.mdc.dk/zone/zz.countries.nerd.dk.rbldnsd',
  46         srcname => 'zz.countries.nerd.dk.rbldnsd',
  47 };
  48
  49 main();
  50 exit 0;
  51
  52 sub main() {
  53
  54         print "Synching country-data file...\n";
  55         downloadData();
  56         print "Connecting to database...\n";
  57         my $dbh = dbConnect();
  58         print "Creating new table...\n";
  59         newTable($dbh);
  60         print "Inserting data...     ";
  61         loadData($dbh);
  62         print "Removing old table...\n";
  63         cleanup($dbh);
  64         $dbh->disconnect();
  65         print "Country table update complete.\n";
  66 }
  67
  68 sub downloadData() {
  69         my $srcPath = PREFIX.'/data/'.srcname;
  70         system('rsync -azvv --progress '.countrydb_url.' '.$srcPath);
  71         unless(-e $srcPath) {
  72                 print STDERR "FATAL: Download failed.\n";
  73                 exit -1;
  74         }
  75 }
  76
  77 sub dbConnect() {
  78
  79         my $dbh;
  80         eval {
  81                 $dbh = DBI->connect("DBI:mysql:"..sql_conf_mysql_db, sql_conf_mysql_user, sql_conf_mysql_pass,
  82                         {  AutoCommit => 1, RaiseError => 1, PrintError => 1 })
  83         };
  84
  85         if($@) {
  86                 print STDERR "FATAL: Can't connect to database:\n$@\n";
  87                 print STDERR "You must edit config/sql.conf and create a corresponding\nMySQL user and database!\n\n";
  88                 exit -1;
  89         }
  90         return $dbh;
  91 }
  92
  93
  94 sub newTable($) {
  95         my ($dbh) = @_;
  96
  97         $dbh->do("DROP TABLE IF EXISTS newcountry");
  98         $dbh->do(
  99         "CREATE TABLE `newcountry` (
 100           `low` int unsigned NOT NULL default 0,
 101           `high` int unsigned NOT NULL default 0,
 102           `country` char(2) NOT NULL default '-',
 103           PRIMARY KEY (`low`, `high`)
 104         ) TYPE=MyISAM"
 105         );
 106 }
 107
 108 sub loadData($) {
 109         my ($dbh) = @_;
 110         my $add_entry = $dbh->prepare("INSERT IGNORE INTO newcountry SET low=?, high=?, country=?");
 111
 112         $| = 1;
 113         my $unpackPath = PREFIX.'/data/'.srcname;
 114         my ($lines) = qx{wc -l $unpackPath};
 115         my $div = int($lines/100);
 116         my ($i, @entries);
 117
 118         open ((my $COUNTRYTABLE), '<', $unpackPath);
 119         $dbh->do("ALTER TABLE `newcountry` DISABLE KEYS");
 120         $dbh->do("LOCK TABLES newcountry WRITE");
 121         while(my $x = <$COUNTRYTABLE>) {
 122                 if($i == 0 or !($i % $div)) {
 123                         printf("\b\b\b\b%3d%", ($i/$lines)*100);
 124                 }
 125
 126                 chomp $x;
 127                 #85.10.224.152/29 :127.0.0.20:ad
 128                 if ($x =~ /^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})\/(\d{1,2}) \:(\S+)\:([a-z]{1,2})$/) {
 129                         my $low = $1 << 24 | $2 << 16 | $3 << 8 | $4;
 130                         my $high = $low + ((2 << (31 - $5)));
 131                         my $country = $7;
 132                         next if lc $country eq 'eu';
 133                         push @entries, '('.$dbh->quote($low).','.$dbh->quote($high).','.$dbh->quote($country).')';
 134                         if(scalar(@entries) >= 100) { #1000 only gives another 10% boost for 10x as much memory
 135                             $dbh->do("INSERT IGNORE INTO newcountry (low, high, country) VALUES ".join(',', @entries));
 136                             @entries = ();
 137                         }
 138                 }
 139
 140                 $i++;
 141         }
 142         $dbh->do("INSERT IGNORE INTO newcountry (low, high, country) VALUES ".join(',', @entries)) if scalar(@entries);
 143
 144         $dbh->do("UNLOCK TABLES");
 145         $dbh->do("ALTER TABLE `newcountry` ENABLE KEYS");
 146         close $COUNTRYTABLE;
 147         print "\b\b\b\bdone.\n";
 148 }
 149
 150 sub cleanup($) {
 151         my ($dbh) = @_;
 152
 153         $dbh->do("DROP TABLE IF EXISTS `oldcountry`");
 154         print "Renaming new table...\n";
 155         $dbh->{RaiseError} = 0;
 156         $dbh->do("OPTIMIZE TABLE `newcountry`");
 157         $dbh->do("ANALYZE TABLE `newcountry`");
 158         # Doing the renames cannot be done atomically
 159         # as sometimes `country` doesn't exist yet.
 160         $dbh->do("RENAME TABLE `country` TO `oldcountry`");
 161         $dbh->do("RENAME TABLE `newcountry` TO `country`");
 162         $dbh->do("DROP TABLE `oldcountry`");
 163 }