bin/migrate-distcontext-sqlite-postgres.pl

   1 #!/usr/bin/perl
   2
   3 # use 5.010;
   4 use strict;
   5 use warnings;
   6
   7 =head1 NAME
   8
   9
  10
  11 =head1 SYNOPSIS
  12
  13
  14
  15 =head1 OPTIONS
  16
  17 =over 8
  18
  19 =cut
  20
  21 my @opt = <<'=back' =~ /B<--(\S+)>/g;
  22
  23 =item B<--help|h!>
  24
  25 This help
  26
  27 =item B<--max=i>
  28
  29 Defaults to 0, which stands for unlimited. Otherwise the number of
  30 records to transfer.
  31
  32 =back
  33
  34 =head1 DESCRIPTION
  35
  36 Just the quickaccess db from sqlite to postgres. Designed to be USED
  37 ONLY ONCE. It drops the table before starting work, so if you run it
  38 twice, it's OK, but should only be done if the first round was broke
  39 or so.
  40
  41 Performance? For 49611 records it took 482 seconds.
  42
  43 Amazing: the website continued to work while we were running.
  44
  45 =cut
  46
  47
  48 use FindBin;
  49 use lib "$FindBin::Bin/../lib";
  50 BEGIN {
  51     push @INC, qw(       );
  52 }
  53
  54 use Dumpvalue;
  55 use File::Basename qw(dirname);
  56 use File::Path qw(mkpath);
  57 use File::Spec;
  58 use File::Temp;
  59 use Getopt::Long;
  60 use Pod::Usage;
  61 use Hash::Util qw(lock_keys);
  62
  63 our %Opt;
  64 lock_keys %Opt, map { /([^=|!]+)/ } @opt;
  65 GetOptions(\%Opt,
  66            @opt,
  67           ) or pod2usage(1);
  68 if ($Opt{help}) {
  69     pod2usage(0);
  70 }
  71
  72 our $SIGNAL = 0;
  73 $SIG{INT} = $SIG{TERM} = sub { my $sig = shift; warn "Caught $sig\n"; $SIGNAL=1 };
  74
  75 use DBI;
  76 use Time::HiRes qw(sleep time);
  77 use FindBin;
  78 use lib "$FindBin::Bin/../CPAN-Blame/lib";
  79 use CPAN::Blame::Config::Cnntp;
  80 my($workdir,$cpan_home,$ext_src);
  81 BEGIN {
  82     $workdir = File::Spec->catdir
  83         ($CPAN::Blame::Config::Cnntp::Config->{solver_vardir},
  84          "workdir");
  85     $cpan_home = $CPAN::Blame::Config::Cnntp::Config->{cpan_home};
  86     $ext_src = $CPAN::Blame::Config::Cnntp::Config->{ext_src};
  87 }
  88
  89 my $dsn = "dbi:SQLite:dbname=$workdir/quickaccess.db"; # no option any more
  90
  91 sub my_do_query {
  92     my($dbi,$sql,@args) = @_;
  93     my $success = $dbi->do($sql,undef,@args);
  94     unless ($success) {
  95         my $err = $dbi->errstr;
  96         warn sprintf
  97             (
  98              "Warning: error occurred while executing sql[%s]with args[%s]: %s",
  99              $sql,
 100              join(":",map { defined $_ ? "'$_'" : "<undef>"} @args),
 101              $err,
 102             );
 103     }
 104     return $success;
 105 }
 106
 107 my $sldbi = DBI->connect ($dsn); # return a dbi handle
 108 my $slsth0 = $sldbi->prepare("SELECT count(*) from distcontext");
 109 $slsth0->execute();
 110 my($slcnt) = $slsth0->fetchrow_array();
 111 warn "Found $slcnt records in the sqlite table";
 112 my $slsql = "SELECT distv,yaml,greenish,dist from distcontext order by distv";
 113 if ($Opt{max}) {
 114     $slsql .= " limit $Opt{max}";
 115     $slcnt = $Opt{max};
 116 }
 117 my $slsth = $sldbi->prepare($slsql);
 118 $slsth->execute();
 119
 120 my $pgdbi = DBI->connect ("dbi:Pg:dbname=analysis");
 121 my $pgsql = "DROP TABLE distcontext";
 122 my_do_query($pgdbi, $pgsql);
 123 $pgsql = "CREATE TABLE distcontext (
 124 distv text primary key,
 125 yaml text,
 126 greenish integer,
 127 dist text)";
 128 my_do_query($pgdbi, $pgsql);
 129 my $pgsth2 = $pgdbi->prepare("INSERT INTO distcontext
 130  (distv,yaml,greenish,dist) values
 131  (?,     ?,     ?,       ?)");
 132
 133 my $i = 0;
 134 my $lastreport = my $starttime = time;
 135 $|=1;
 136 print "\n";
 137 ROW: while (my(@row) = $slsth->fetchrow_array) {
 138     $row[2] ||= 0;
 139     unless ($pgsth2->execute(@row)) {
 140         warn sprintf "ALERT: error inserting row/id[%s]: %s\n", $row[0], $sldbi->errstr;
 141         last ROW;
 142     }
 143     ++$i;
 144     my $eta = int((time - $^T)*($slcnt - $i)/$i);
 145     printf "\r%d/%d  remaining seconds: %d    ", $i, $slcnt, $eta;
 146     last ROW if $SIGNAL;
 147 }
 148 print "\n";
 149 my $tooktime = time - $starttime;
 150 warn "records transferred[$i] tooktime[$tooktime]\n";
 151
 152 # Local Variables:
 153 # mode: cperl
 154 # cperl-indent-level: 4
 155 # End: