2 # (optional) ARRL Dx Database handling
6 # Copyright (c) 2005 Dirk Koopman G1TLH
13 use vars qw($VERSION $BRANCH $dbh $dbname %tabledefs $error);
15 main::mkver($VERSION = q$Revision$) if main->can('mkver');
24 $dbname = "$main::root/data/arrldx.db";
26 paragraph => 'CREATE TABLE paragraph(p text, t int, bullid text)',
27 paragraph_t_idx => 'CREATE INDEX paragraph_t_idx ON paragraph(t DESC)',
28 refer => 'CREATE TABLE refer(r text, rowid int, t int, pos int)',
29 refer_id_idx => 'CREATE INDEX refer_id_idx ON refer(rowid)',
30 refer_t_idx => 'CREATE INDEX refer_t_idx ON refer(t DESC)',
36 my $class = ref $pkg || $pkg;
42 $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "");
45 Log('err', $DBI::errstr);
46 $error = $DBI::errstr;
50 # check that all the tables are present and correct
51 my $sth = $dbh->prepare("select name,type from sqlite_master") or $error = $DBI::errstr, return;
52 $sth->execute or $error = $DBI::errstr, return;
54 while (my @row = $sth->fetchrow_array) {
55 $f{$row[0]} = $row[1];
57 foreach my $t (sort keys %tabledefs) {
58 $dbh->do($tabledefs{$t}) unless exists $f{$t};
66 if (ref $args{file}) {
67 $self->{f} = $args{file};
69 $self->{f} = IO::File->new($args{file}) or $error = $!, return;
73 return bless $self, $class;
80 return unless $self->{f};
90 $state = 'ZC' if /^ZCZC/;
91 } elsif ($state eq 'ZC') {
92 if (/\b(ARLD\d+)\b/) {
96 } elsif ($state eq 'id') {
97 if (/^Newington\s+CT\s+(\w+)\s+(\d+),\s+(\d+)/i) {
99 $self->{date} = str2time("$1 $2 $3") if $state eq 'date';
101 } elsif ($state eq 'date') {
102 if (/^$self->{id}/) {
103 last unless /DX\s+[Nn]ews\s*$/;
106 } elsif ($state eq 'week') {
107 $state = 'weekro' if /^This\s+week/;
108 } elsif ($state eq 'weekro') {
113 } elsif ($state eq 'para') {
116 $self->{para} =~ s/^\s+//;
117 $self->{para} =~ s/\s+$//;
118 $self->{para} =~ s/\s+/ /g;
123 } elsif (/^THIS\s+WEEKEND/) {
129 $self->{para} .= $_ . ' ';
141 my $sth = $dbh->prepare("insert into paragraph values(?,?,?)");
142 $sth->execute($self->{para}, $self->{date}, $self->{id});
143 my $lastrow = $dbh->func('last_insert_rowid');