From: Ian Jackson Date: Thu, 30 Aug 2012 00:01:57 +0000 (+0100) Subject: wip X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~ian/git?p=nj-awaymsg.git;a=commitdiff_plain;h=42a6560ca0aa3325296f3ea72096232cdc056cb5 wip --- diff --git a/AwayMsg.pm b/AwayMsg.pm index 9fdf742..e20d6b6 100644 --- a/AwayMsg.pm +++ b/AwayMsg.pm @@ -11,7 +11,7 @@ BEGIN { our ($VERSION, @ISA, @EXPORT, @EXPORT_OK, %EXPORT_TAGS); $VERSION = 1.00; @ISA = qw(Exporter); - @EXPORT = qw(&db_connect $dbh); + @EXPORT = qw(&db_connect &db_commit $dbh); %EXPORT_TAGS = ( ); @EXPORT_OK = qw(); } @@ -19,36 +19,37 @@ BEGIN { our ($dbh); sub db_connect () { - my $h = DBI->connect("dbi:SQLite:away.db", + my $dbf; + foreach my $d (@INC) { + $dbf = "$d/away.db"; + if (stat $dbf) { + chdir($d) or die $!; + last; + } + } + $dbh = DBI->connect("dbi:SQLite:away.db", { AutoCommit=>0, RaiseError=>1, ShowErrorStatement=>1 }) or die "$DBI::errstr ?"; +} - $dbh->do("BEGIN"); - - $dbh->do("CREATE TEMPORARY TABLE config (". - " emailaddr TEXT PRIMARY KEY NOT NULL,". - " username TEXT NOT NULL,". - " forwardfile TEXT NOT NULL". - ")"); - - open C, "config" or die $!; - while () { - s/^\s+//; - next if m/^\#/; - chomp or die; - s/\s+$//; - my @s = split; - die "$_ ?" unless @s==3; - $dbh->do("INSERT INTO config". - " (emailaddr, username, forwardfile)". - " VALUES (?,?,?)", - {}, @s); +sub nooutput ($) { + my ($stmt) = @_; + my $sth= $dbh->prepare($stmt); + $sth->execute(); + my $row; + if ($row= $sth->fetchrow_hashref()) { + die("REFERENTIAL INTEGRITY ERROR\n". + "\n$stmt\n". Dumper($row),"\n"); } - $dbh->do("COMMIT"); +} + - return $h; +sub db_commit () { + nooutput("SELECT * FROM addresses LEFT JOIN config USING (emailaddr)"); + nooutput("SELECT * FROM addresses LEFT JOIN texts USING (textid)"); + $dbh->do("COMMIT"); } 1; diff --git a/mason/main b/mason/main index 8a2533f..ec7c653 100755 --- a/mason/main +++ b/mason/main @@ -1,5 +1,6 @@ <%init> use AwayMsg; +db_connect(); Email "out of office" setup @@ -10,11 +11,13 @@ use AwayMsg; <%perl> my %row; -my $sth = $dbh->prepare("SELECT emailaddr, textid, expires" - " FROM schema - " RIGHT JOIN addresses USING (emailaddr)". - " JOIN texts USING (textid)", - " ORDER BY emailaddr"); +my $sth = $dbh->prepare(< @@ -24,12 +27,23 @@ my $sth = $dbh->prepare("SELECT emailaddr, textid, expires" -% while ($row= $sth->fetchrow_hashref()) { +% while (my $row= $sth->fetchrow_hashref()) { + % if (defined $row->{'textid'}) { % my $expiry = expires2timet($row->{'expires'}); % if ($expiry > time) { +% } else { + +% } - + + +% } + +% } +
Which message? Expiry date
<% $row->{'emailaddr'} |h %>activeexpired<% $row->{'textid'} |h %> <% $row->{'expires'} |h %> +% } else { +inactive
diff --git a/schema b/schema index eb2ffb8..aaeb0d9 100644 --- a/schema +++ b/schema @@ -4,7 +4,7 @@ CREATE TABLE texts ( name TEXT NOT NULL, subject TEXT NOT NULL, text TEXT NOT NULL, - expires TEXT + expires TEXT NOT NULL ); CREATE TABLE addresses ( @@ -12,3 +12,8 @@ CREATE TABLE addresses ( textid INTEGER ); +CREATE TABLE config ( + emailaddr TEXT PRIMARY KEY NOT NULL, + username TEXT NOT NULL, + forwardfile TEXT NOT NULL + ); diff --git a/update-config b/update-config new file mode 100755 index 0000000..a7f87f3 --- /dev/null +++ b/update-config @@ -0,0 +1,28 @@ +#!/usr/bin/perl -w + +use strict; + +use AwayMsg; + +db_connect(); + +$dbh->do("BEGIN"); + +$dbh->do("DELETE FROM config"); + +my $sth= $dbh->prepare("INSERT INTO config". + " (emailaddr, username, forwardfile)". + " VALUES (?,?,?)"); + +open C, "config" or die $!; +while () { + s/^\s+//; + next if m/^\#/; + chomp or die; + s/\s+$//; + my @s = split; + die "$_ ?" unless @s==3; + $sth->execute(@s); +} + +db_commit();