perl/spot2sql.pl

   1 #
   2 # $Id$
   3 #
   4 # Copyright (c) 2005 Dirk Koopman G1TLH
   5 #
   6 # Load all the spots you have into the spider.sdb SQLite
   7 # SQL database.
   8 #
   9 BEGIN {
  10
  11         sub mkver {};
  12
  13         # root of directory tree for this system
  14         $root = "/spider";
  15         $root = $ENV{'DXSPIDER_ROOT'} if $ENV{'DXSPIDER_ROOT'};
  16
  17         unshift @INC, "$root/perl";     # this IS the right way round!
  18         unshift @INC, "$root/local";
  19 }
  20
  21 use DXUtil;
  22 use Spot;
  23 use DBI;
  24 use DBD::SQLite;
  25
  26 Spot::init();
  27
  28 my $dbh = DBI->connect("dbi:SQLite:dbname=$root/data/spider.sdb","","")
  29         or die "cannot open $root/data/spider.sdb";
  30
  31 opendir DIR, "$root/data/spots" or die "No spot directory $!\n";
  32 my @years = grep {/^\d/} readdir DIR;
  33 closedir DIR;
  34
  35 $dbh->do("delete from spots");
  36
  37 my $sth = $dbh->prepare("insert into spots values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)") or die "prepare\n";
  38
  39 foreach my $year (@years) {
  40         opendir DIR, "$root/data/spots/$year" or next;
  41         my @days = grep {/^\d+\.dat/} readdir DIR;
  42         closedir DIR;
  43         my $j = Julian::Day->new(time);
  44         for (@days) {
  45                 my ($day) = /^(\d+)/;
  46                 my $count;
  47                 $j->[0] = $year;
  48                 $j->[1] = $day-0;
  49                 printf "\rdoing $year %03d", $day;
  50                 my $fh = $Spot::fp->open($j); # get the next file
  51                 if ($fh) {
  52                         $dbh->begin_work;
  53                         while (<$fh>) {
  54                                 my @s = split /\^/;
  55                                 push @s, undef while @s < 14;
  56                                 $sth->execute(@s);
  57                                 $count++;
  58                         }
  59                         $dbh->commit;
  60                 }
  61                 print " $count\n";
  62         }
  63 }
  64 print "\n";
  65 $sth->finish;
  66 $dbh->disconnect;
  67
  68