2 # (optional) ARRL Dx Database handling
6 # Copyright (c) 2005 Dirk Koopman G1TLH
13 use vars qw($VERSION $BRANCH $dbh $dbname %tabledefs $error %stop $limit);
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)',
93 $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "");
96 Log('err', $DBI::errstr);
97 $error = $DBI::errstr;
106 return unless do_connect();
107 my $sth = $dbh->prepare("select name,type from sqlite_master where type = 'table'") or $error = $DBI::errstr, return;
108 $sth->execute or $error = $DBI::errstr, return;
109 while (my @row = $sth->fetchrow_array) {
110 $dbh->do("drop table $row[0]");
119 # check that all the tables are present and correct
120 my $sth = $dbh->prepare("select name,type from sqlite_master") or $error = $DBI::errstr, return;
121 $sth->execute or $error = $DBI::errstr, return;
123 while (my @row = $sth->fetchrow_array) {
124 $f{$row[0]} = $row[1];
126 foreach my $t (sort keys %tabledefs) {
127 $dbh->do($tabledefs{$t}) unless exists $f{$t};
135 my $class = ref $pkg || $pkg;
141 return unless do_connect();
148 if (ref $args{file}) {
149 $self->{f} = $args{file};
151 $self->{f} = IO::File->new($args{file}) or $error = $!, return;
155 return bless $self, $class;
162 return unless $self->{f};
172 $state = 'ZC' if /^ZCZC/;
173 } elsif ($state eq 'ZC') {
174 if (/\b(ARLD\d+)\b/) {
178 } elsif ($state eq 'id') {
179 if (/^Newington\s+CT\s+(\w+)\s+(\d+),\s+(\d+)/i) {
182 $self->{date} = str2time("$1 $2 $3") if $state eq 'date';
184 } elsif ($state eq 'date') {
185 if (/^$self->{id}/) {
186 last unless /DX\s+[Nn]ews\s*$/;
189 } elsif ($state eq 'week') {
190 $state = 'weekro' if /^This\s+week/;
191 } elsif ($state eq 'weekro') {
196 } elsif ($state eq 'para') {
199 $self->{para} =~ s/^\s+//;
200 $self->{para} =~ s/\s+$//;
201 $self->{para} =~ s/\s+/ /g;
206 } elsif (/^THIS\s+WEEKEND/) {
212 $self->{para} .= $_ . ' ';
224 my $sth = $dbh->prepare("insert into paragraph values(?,?,?)");
225 $sth->execute($self->{para}, $self->{date}, "$self->{year}-$self->{id}");
226 my $lastrow = $dbh->func('last_insert_rowid');
230 # my @w = split /[.,;:\s"'\$\%!£^&\*\(\)\[\]\{\}\#\<\>+=]+/, $self->{para};
231 my @w = split m|[\b\s]+|, $self->{para};
232 # print join(' ', @w), "\n";
233 $sth = $dbh->prepare("insert into refer values(?,?,?,?)");
238 # starts with a capital letter that isn't Q
239 if (/^[A-PR-Z]/ || m|\d+[A-Z][-/A-Z0-9]*$|) {
247 # add it into the word list
248 $sth->execute($w, $lastrow, $self->{date}, $i);
264 my @w = map { s|[^-/\w]||g; uc $_ } split /\s+/, $args{'q'};
266 my $s = qq{select distinct p, t, bullid from (select distinct rowid from refer where };
269 $s .= qq{r like '$w\%'};
275 $s .= qq{ order by t desc limit $l), paragraph where paragraph.ROWID=rowid};
276 my $sth = $dbh->prepare($s) or $error = $DBI::errstr, return @out;
278 while (my @row = $sth->fetchrow_array) {