From be05d6ea96d1b9bd023900eab7ec5d119d99853a Mon Sep 17 00:00:00 2001
From: minima <minima>
Date: Sun, 8 May 2005 15:21:47 +0000
Subject: [PATCH] add spot2sql.pl for experiment sql support

---
 perl/DXCommandmode.pm |  2 +-
 perl/spot2sql.pl      | 68 +++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 69 insertions(+), 1 deletion(-)
 create mode 100755 perl/spot2sql.pl

diff --git a/perl/DXCommandmode.pm b/perl/DXCommandmode.pm
index 5f89ac23..080cf666 100644
--- a/perl/DXCommandmode.pm
+++ b/perl/DXCommandmode.pm
@@ -440,7 +440,7 @@ sub run_cmd
 		# strip out // and .. on command only
 		$cmd =~ s|//|/|g;
 		$cmd =~ s|^/||g;		# no leading / either
-		$cmd =~ s|[^-\w/]||g;	# and no funny characters
+		$cmd =~ s|[^-?\w/]||g;	# and no funny characters
 					
 		my ($path, $fcmd);
 			
diff --git a/perl/spot2sql.pl b/perl/spot2sql.pl
new file mode 100755
index 00000000..7ffb758b
--- /dev/null
+++ b/perl/spot2sql.pl
@@ -0,0 +1,68 @@
+#
+# $Id$
+#
+# Copyright (c) 2005 Dirk Koopman G1TLH
+#
+# Load all the spots you have into the spider.sdb SQLite
+# SQL database.
+# 
+BEGIN {
+
+	sub mkver {};
+	
+	# root of directory tree for this system
+	$root = "/spider";
+	$root = $ENV{'DXSPIDER_ROOT'} if $ENV{'DXSPIDER_ROOT'};
+	
+	unshift @INC, "$root/perl";     # this IS the right way round!
+	unshift @INC, "$root/local";
+}
+
+use DXUtil;
+use Spot;
+use DBI;
+use DBD::SQLite;
+
+Spot::init();
+
+my $dbh = DBI->connect("dbi:SQLite:dbname=$root/data/spider.sdb","","")
+	or die "cannot open $root/data/spider.sdb";
+
+opendir DIR, "$root/data/spots" or die "No spot directory $!\n";
+my @years = grep {/^\d/} readdir DIR;
+closedir DIR;
+
+$dbh->do("delete from spots");
+
+my $sth = $dbh->prepare("insert into spots values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)") or die "prepare\n";
+
+foreach my $year (@years) {
+	opendir DIR, "$root/data/spots/$year" or next;
+	my @days = grep {/^\d+\.dat/} readdir DIR;
+	closedir DIR;
+	my $j = Julian::Day->new(time);
+	for (@days) {
+		my ($day) = /^(\d+)/;
+		my $count;
+		$j->[0] = $year;
+		$j->[1] = $day-0;
+		printf "\rdoing $year %03d", $day;
+		my $fh = $Spot::fp->open($j); # get the next file
+		if ($fh) {
+			$dbh->begin_work;
+			while (<$fh>) {
+				my @s = split /\^/;
+				push @s, undef while @s < 14;
+				$sth->execute(@s);
+				$count++;
+			}
+			$dbh->commit;
+		}
+		print " $count\n";
+	}
+}
+print "\n";
+$sth->finish;
+$dbh->disconnect;
+
+
-- 
2.34.1