[ljcom] r7165: updated verison of script with support o...

U   trunk/bin/upgrading/ipmap_text2binary.pl

Modified: trunk/bin/upgrading/ipmap_text2binary.pl
===================================================================
--- trunk/bin/upgrading/ipmap_text2binary.pl	2009-03-26 11:57:49 UTC (rev 7164)
+++ trunk/bin/upgrading/ipmap_text2binary.pl	2009-03-26 13:47:19 UTC (rev 7165)
@@ -2,19 +2,119 @@
 
 use strict;
 use warnings;
-
+use File::Temp qw/mktemp/;
 use IpMap;
 
 my $ipmap = IpMap->new();
-my $in_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_cdn.txt";
-my $out_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_cdn.bin";
 
-if ($IpMap::VERSION ge "1.1.0") {
-    die "You must use import_geoip_data.pl, usage example is in import_geoip_data.sh"; 
+if ($IpMap::VERSION le "1.1.0") {
+    my $in_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_cdn.txt";
+    my $out_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_cdn.bin";
+    print "Reading IP database from '$in_filename'\n";
+    $ipmap->LoadFromText($in_filename);
+    print "Saving database to '$out_filename'\n";
+    $ipmap->SaveToBinary($out_filename);
+    print "Done.\n"
+} else {
+    print "Creating temporary tables (*_update) for GeoIp data\n";
+    create_tmp_tables();
+    print "Loading data into temp tables\n";
+
+    my ($login, $password) = ("lj", "ljpass"); ## TODO: get them from LJ::DBINFO
+    my $cmd = "mysql '-u$login' '-p$password' livejournal < $ENV{LJHOME}/bin/upgrading/geo.sql";
+    print $cmd, "\n";
+    system($cmd)==0 or die "Can't run mysql: $?";
+    
+    my $in_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_lj.txt.gz";
+    my $tmp_filename = mktemp();
+    my $out_filename = "$ENV{LJHOME}/bin/upgrading/ipmap_lj.bin";
+
+    print "Decompressing $in_filename into $tmp_filename";
+    $cmd = "gzip -dc $in_filename > $tmp_filename";
+    print $cmd, "\n";
+    system($cmd)==0 or die "Can't run gzip: $?";
+        
+    print "Reading IP database from '$tmp_filename'\n";
+    $ipmap->LoadFromText($tmp_filename);
+    print "Saving database to '$out_filename'\n";
+    $ipmap->SaveToBinary($out_filename);
+    unlink($tmp_filename);
+    
+    print "Renaming temp tables to permanent";
+    rename_tables();
+    print "Done.\n"
 }
 
-print "Reading IP database from '$in_filename'\n";
-$ipmap->LoadFromText($in_filename);
-print "Saving database to '$out_filename'\n";
-$ipmap->SaveToBinary($out_filename);
-print "Done.\n"
+
+sub create_tmp_tables {
+    my $dbh = LJ::get_db_writer() or die "Unable to contact writer role";
+    $dbh->{RaiseError} = 1;
+
+    # ATTENTION! UTF-8 have several bytes for one character
+    # This very old DB we use latin1 for UTF, so enlarge CHAR(x) columns twice
+
+    $dbh->do("DROP TABLE IF EXISTS geo_countries_update");
+    $dbh->do("CREATE TABLE geo_countries_update (
+              country_id INT(3) NOT NULL default '0',
+              short_name CHAR(2) default NULL,
+              name VARCHAR(45) default NULL,
+              PRIMARY KEY (country_id)
+             ) ENGINE = InnoDB");
+
+    $dbh->do("DROP TABLE IF EXISTS geo_regions_update");
+    $dbh->do("CREATE TABLE geo_regions_update (
+              region_id INT(10) unsigned NOT NULL,
+              name TINYTEXT,
+              country_id INT(3) NOT NULL,
+              region VARCHAR(90) NOT NULL,
+              translit_region VARCHAR(90) default NULL,
+              rus_region VARCHAR(90) default NULL,
+              PRIMARY KEY (region_id)
+             ) ENGINE = InnoDB");
+
+    $dbh->do("DROP TABLE IF EXISTS geo_cities_update");
+    $dbh->do("CREATE TABLE geo_cities_update (
+              city_id INT(10) unsigned NOT NULL,
+              name VARCHAR(90) default NULL,
+              region_id INT(10) unsigned default NULL,
+              country_id INT(3) NOT NULL,
+              latitude DECIMAL(7,4) NOT NULL,
+              longitude DECIMAL(7,4) NOT NULL,
+              rus_name VARCHAR(90) default NULL,
+              PRIMARY KEY (city_id)
+             ) ENGINE = InnoDB");
+} 
+
+
+sub rename_tables {    
+    my $dbh = LJ::get_db_writer() or die "Unable to contact writer role";
+    $dbh->{RaiseError} = 1;
+
+    $dbh->do("DROP TABLE IF EXISTS geo_countries_backup");
+    $dbh->do("DROP TABLE IF EXISTS geo_regions_backup");
+    $dbh->do("DROP TABLE IF EXISTS geo_cities_backup");
+
+    # first time introdution table we have no old tables
+    # but rename statement written as we have it - so create dummy
+
+    $dbh->do("CREATE TABLE IF NOT EXISTS geo_countries (
+              dummy CHAR(1)
+             ) ENGINE = InnoDB");
+
+    $dbh->do("CREATE TABLE IF NOT EXISTS geo_regions (
+              dummy CHAR(1)
+             ) ENGINE = InnoDB");
+
+    $dbh->do("CREATE TABLE IF NOT EXISTS geo_cities (
+              dummy CHAR(1)
+             ) ENGINE = InnoDB");
+
+    $dbh->do("RENAME TABLE " . 
+                "geo_countries          TO geo_countries_backup,    " .
+                "geo_countries_update   TO geo_countries,           " .
+                "geo_regions            TO geo_regions_backup,      " .
+                "geo_regions_update     TO geo_regions,             " .
+                "geo_cities             TO geo_cities_backup,       " .
+                "geo_cities_update      TO geo_cities"
+    );
+}