Below is an overview and sample of how to issue SQL queries against an
Oracle database using perl and its DBI & DBD::Oracle modules. I have
just included snippets of exim.conf and a complete perl start_up.pl
rather than a complete solution.
Along with Chris & Paul's software from ex-parrot.com I will be
releasing a complete POP/SMTP virtual mail system based on exim, Debian,
Oracle 8i, Perl and tpop3d which also features an embedded perl
interpreter (
http://www.ex-parrot.com/~chris/tpop3d/ ). This'll happen
shortly after next week's Open Source Conference.
1. Ensure exim is built with perl!
In Local/Makefile,
@@ -252,3 +252,3 @@
-# EXIM_PERL=perl.o
+EXIM_PERL=perl.o
2. Queries issued in exim.conf
2.0 Turn on perl,
perl_at_start = true
perl_startup = do '/usr/exim_test/start_up.pl'
2.1 a exim macro is assigned with some SQL. This must happen in the
first section of exim.conf and use caps for the macro name.
SQL_LOCALUSER = \
select mbox_name || '@' || domain_name \
from popbox \
where local_part = ? and domain_name = ?
SQL_LOCALDELIVERY = select d.path || '/' || p.mbox_name \
from domain d, popbox p \
where p.local_part = ? and p.domain_name = ? \
and p.domain_name = d.domain_name
Note a nice feature of the Perl way is the use of bound variables. This
allows the database to prepare an execution plan for the SQL query so
that it can be executed multiple times without constructing the
execution plan from scratch each time. This is a potential performance
flaw with the existing native interfaces that exim features (I'll talk
about this in another email).
2.2 In the directors section,
virtual_localuser:
driver = smartuser
new_address = ${perl{sql_query}{SQL_LOCALUSER}{$local_part}{$domain}}
transport = virtual_localdelivery
The perl function sql_query($@) (below, in 3.0) takes some arbitrary SQL
and any number of variables to bind (these must be in the right order
wrt to original SQL). If perl returns undef the director will be failed
and control passes to the next director.
2.3 In the transport section,
Another example essentially the same as above, using the hassle-free
Maildir format,
virtual_localdelivery:
driver = appendfile
# for maildir:
check_string = ""
prefix = ""
suffix = ""
maildir_format = true
directory = ${perl{sql_query}{SQL_LOCALDELIVERY}{$local_part}{$domain}}
delivery_date_add
envelope_to_add
return_path_add
user = ${perl{sql_query}{SQL_UNIX_USER}{$domain}}
mode = 0660
Note how here there is a per-domain unix user that can be specified.
Also note how it only takes one parameter. This is another flexible
feature of using perl :-)
3. The perl start_up.pl
#!/usr/bin/perl -w
# The above line is for testing on the command line
BEGIN { warn scalar(localtime), ": Perl started...\n" }
use strict;
use DBI;
my @DSN = qw(dbi:Oracle:vmail postmaster postmaster);
my $dbh = DBI->connect(@DSN)
or die "Unable to connect to DSN '@DSN': ", DBI->errstr, "\n";
### END of start-up code
sub sql_query {
my ($query, @params) = @_;
my $destination;
unless (defined $sth{$query}) {
$sth{$query} = $dbh->prepare($query)
or die "Unable to prepare SQL '$query': ",
DBI->errstr, "\n";
}
warn "$query (@params) => \n";
$sth{$query}->execute(@params)
or die "Couldn't execute $query (@params): ", DBI->errstr, "\n";
($destination) = $sth{$query}->fetchrow_array;
warn "...'$destination'\n";
$destination || undef;
}
__END__
The warn()s are for testing and can safely be removed. Note that
sql_query must return undef rather than "" if it doesn't complete a
lookup.
Other notes: The example above embeds SQL in the exim.conf. The first
time I did it the SQL was in a hash in the start_up.pl file. Perl
then pre-prepared the SQL by iterating through the hash. The SQL could
of course also live in an Oracle PL/SQL package.
Enjoy,
Paul
--
Look at the order in which you do things
