chiark / gitweb /
~ian / nj-awaymsg.git / commitdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (parent: e4d5c2a)
wip
authorIan Jackson <ijackson@chiark.greenend.org.uk>
Thu, 30 Aug 2012 00:01:57 +0000 (01:01 +0100)
committerIan Jackson <ijackson@chiark.greenend.org.uk>
Thu, 30 Aug 2012 00:01:57 +0000 (01:01 +0100)
AwayMsg.pm patch | blob | history
mason/main patch | blob | history
schema patch | blob | history
update-config [new file with mode: 0755] patch | blob

diff --git a/AwayMsg.pm b/AwayMsg.pm
index 9fdf742ed366720ed91c562abfdf0d14d10143d9..e20d6b6acc39f5b825a8fce78657bc45c79c4494 100644 (file)
--- 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 (<C>) {
-       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 8a2533f3dec18ece1ce9ec8efc8139ad5f29bf60..ec7c6536abc0158f524c84bcae49484c4778b5fd 100755 (executable)
--- a/mason/main
+++ b/mason/main
@@ -1,5 +1,6 @@
 <%init>
 use AwayMsg;
+db_connect();
 </%init>
 <html lang="en"><head>
 <title>Email "out of office" setup</title>
@@ -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(<<END);
+    SELECT emailaddr, textid, expires
+     FROM        config
+       LEFT JOIN addresses USING (emailaddr)
+       LEFT JOIN texts     USING (textid)
+     ORDER BY emailaddr
+END
 </%perl>
 
 <table>
@@ -24,12 +27,23 @@ my $sth = $dbh->prepare("SELECT emailaddr, textid, expires"
 <td>Which message?</td>
 <td>Expiry date</td>
 </tr><tr>
-% while ($row= $sth->fetchrow_hashref()) {
+% while (my $row= $sth->fetchrow_hashref()) {
+<tr>
 <td><% $row->{'emailaddr'} |h %></td>
 %     if (defined $row->{'textid'}) {
 %         my $expiry = expires2timet($row->{'expires'});
 %        if ($expiry > time) {
 <td>active</td>
+%         } else {
+<td>expired</td>
+%         }
 <td><% $row->{'textid'} |h %></td>
 <td><% $row->{'expires'} |h %></td>
-<td>
+%     } else {
+<td>inactive</td>
+<td></td>
+<td></td>
+%     }
+</tr>
+% }
+</table>
diff --git a/schema b/schema
index eb2ffb8d35fb69c97e522e53c8a90ec89efc3bbc..aaeb0d92ba72d3052db2288334dfe3b3c4bd2ae0 100644 (file)
--- 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 (executable)
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 (<C>) {
+    s/^\s+//;
+    next if m/^\#/;
+    chomp or die;
+    s/\s+$//;
+    my @s = split;
+    die "$_ ?" unless @s==3;
+    $sth->execute(@s);
+}
+
+db_commit();
Unnamed repository; edit this file 'description' to name the repository.