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();
}
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;
<%init>
use AwayMsg;
+db_connect();
</%init>
<html lang="en"><head>
<title>Email "out of office" setup</title>
<%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>
<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>
name TEXT NOT NULL,
subject TEXT NOT NULL,
text TEXT NOT NULL,
- expires TEXT
+ expires TEXT NOT NULL
);
CREATE TABLE addresses (
textid INTEGER
);
+CREATE TABLE config (
+ emailaddr TEXT PRIMARY KEY NOT NULL,
+ username TEXT NOT NULL,
+ forwardfile TEXT NOT NULL
+ );
--- /dev/null
+#!/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();