perl/spot2sql.pl

   1 #!/usr/bin/perl
   2 #
   3 # $Id$
   4 #
   5 # Copyright (c) 2005 Dirk Koopman G1TLH
   6 #
   7 # Load all the spots you have into the spider.sdb SQLite
   8 # SQL database.
   9 #
  10 BEGIN {
  11
  12         sub mkver {};
  13
  14         # root of directory tree for this system
  15         $main::root = "/spider";
  16         $main::root = $ENV{'DXSPIDER_ROOT'} if $ENV{'DXSPIDER_ROOT'};
  17
  18         unshift @INC, "$root/perl";     # this IS the right way round!
  19         unshift @INC, "$root/local";
  20 }
  21
  22 use strict;
  23
  24 use DXUtil;
  25 use Spot;
  26 use DBI;
  27
  28 our $root;
  29
  30 Spot::init();
  31
  32 my $dbh;
  33 my $sort = lc shift || 'sqlite';
  34
  35 if ($sort eq 'sqlite') {
  36         unlink "$root/data/spider.db";
  37         $dbh = DBI->connect("dbi:SQLite:dbname=$root/data/spider.db","","")
  38                 or die "cannot open $root/data/spider.db";
  39         $dbh->do("PRAGMA default_synchronous = OFF");
  40 } elsif ($sort eq 'mysql') {
  41         $dbh = DBI->connect("dbi:mysql:dbname=spider","spider","spider")
  42                 or die $DBI::errstr;
  43 } elsif ($sort eq 'pg') {
  44         $dbh = DBI->connect("dbi:Pg:dbname=spider","postgres","")
  45                 or die $DBI::errstr;
  46 } else {
  47         die "invalid database type: $sort";
  48 }
  49
  50 $dbh->{PrintError} = 0;
  51 $dbh->{PrintWarn} = 0;
  52
  53 opendir DIR, "$root/data/spots" or die "No spot directory $!\n";
  54 my @years = grep {/^\d/} readdir DIR;
  55 closedir DIR;
  56
  57 my $start = time;
  58
  59 eval { $dbh->do("drop table spots");};
  60
  61 $dbh->do("CREATE TABLE spots (freq real,
  62 spotted varchar(255),
  63 t int,
  64 comment varchar(255),
  65 spotter varchar(255),
  66 spotted_dxcc int,
  67 spotter_dxcc int,
  68 origin varchar(255),
  69 spotted_itu int,
  70 spotted_cq int,
  71 spotter_itu int,
  72 spotter_cq int,
  73 spotted_state varchar(2),
  74 spotter_state varchar(2)
  75 )");
  76
  77 my $sth = $dbh->prepare("insert into spots values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)") or die "prepare\n";
  78
  79 foreach my $year (@years) {
  80         opendir DIR, "$root/data/spots/$year" or next;
  81         my @days = grep {/^\d+\.dat/} readdir DIR;
  82         closedir DIR;
  83         my $j = Julian::Day->new(time);
  84         for (@days) {
  85                 my ($day) = /^(\d+)/;
  86                 my $count;
  87                 $j->[0] = $year;
  88                 $j->[1] = $day-0;
  89                 printf "\rdoing $year %03d", $day;
  90                 my $fh = $Spot::fp->open($j); # get the next file
  91                 if ($fh) {
  92                         $dbh->begin_work;
  93                         while (<$fh>) {
  94                                 if ($sort eq 'pg' && $count && $count % 100 == 0) {
  95                                         $dbh->commit;
  96                                         $dbh->begin_work;
  97                                 }
  98                                 my @s = split /\^/;
  99                                 if ($sort eq 'pg') {
 100                                         push @s, '' while @s < 14;
 101                                         $s[5]+=0;
 102                                         $s[6]+=0;
 103                                         $s[8]+=0;
 104                                         $s[9]+=0;
 105                                         $s[10]+=0;
 106                                         $s[11]+=0;
 107                                 } else {
 108                                         push @s, undef while @s < 14;
 109                                 }
 110                                 eval { $sth->execute(@s) };
 111                                 if ($@) {
 112                                         print DBI::neat_list(@s);
 113                                         $dbh->rollback;
 114                                         $dbh->begin_work;
 115                                 }
 116                                 $count++;
 117                         }
 118                         $dbh->commit;
 119                 }
 120                 print " $count\n";
 121         }
 122 }
 123 print "\n";
 124 $sth->finish;
 125
 126 my $secs = time - $start;
 127 print "Load took $secs\n";
 128 $secs = time;
 129
 130 $dbh->do("CREATE INDEX spotted_idx on spots(spotted)");
 131
 132 my $secs = time - $start;
 133 print "Spotted index took $secs\n";
 134 $secs = time;
 135
 136 $dbh->do("CREATE INDEX t_idx on spots(t)");
 137
 138 my $secs = time - $start;
 139 print "T index took $secs\n";
 140 $secs = time;
 141
 142 $dbh->disconnect;
 143
 144
 145