Re: [Exim] [recipe] Creating CDB files from a database

On Tue, Jul 15, 2003 at 03:58:02AM +0100, Paul Makepeace wrote:
> --
> I dislike querying a RDBMS from exim so have instead created CDB files
> as a configuration source. This is more resilient and faster at the cost
> of a little more work to maintain. Attached is an example script that
> will do the SQL->CDB conversion. Of course, tweak for your own schema.
>
> I'd be grateful for feedback, improvements, etc. I'll put it online
> somewhere eventually if there's interest.


#!/usr/bin/perl -w

# paulm, 2002-2003
# make_mail_config.pl creates CDB files from the contents of
# the mail configuration stored in the RDBMS.

use strict;
use CDB_File;
use File::Spec::Functions qw(catfile);

use DBI;

my @DSN = qw(dbi:mysql:DBNAME DBUSER DBPASS);
my $mail_etc_dir = q[/etc/mail];

my %sql = (
    forwards => q{
        select concat(a.local_part, '@', d.domain), a.destination
          from accounts a, domains d
         where a.domain_id=d.id
             and a.acc_type='forwarder'
          order by d.domain, a.local_part
    },

    forward_domains => q{
        select domain, ''
          from domains d, accounts a
        where a.acc_type='forwarder'
          and d.id=a.domain_id
        group by domain
    },

    local_domains => q{
        select domain, '' from domains
         where type='local'
    },

    relay_domains => q{
        select domain, '' from domains
         where type='relay'
    },

    routed_domains => q{
        select domain, route from domains
         where type='routed'
    },

    aliased_domains => q{
        select d1.domain, d2.domain route
          from domains d1, domains d2
         where d1.type='alias'
           and d1.alias_domain_id=d2.id
    },

    smtp_auth => qq{
        select username, password
          from smtpauths
         where status='enabled'
    }
);

my $dbh = DBI->connect(@DSN, { RaiseError => 1 })
    or die "Unable to connect to DSN '@DSN': ", DBI->errstr, "\n";
# Check all the SQL before making the files.
my %sth = map {$_ => $dbh->prepare($sql{$_})} keys %sql;

$|=1;
foreach my $file_type (keys %sql) {
    print "Creating $file_type... ";
    update_mail_config($file_type);
    print "done!\n";
}
$dbh->disconnect();

# End

sub update_mail_config {
    my $file_type = shift;
    my $file = catfile($mail_etc_dir, "$file_type.cdb");
    $sth{$file_type}->execute();
    mkcdb($file, $sth{$file_type}, 2);
}

# bound_vars is the number of bound variables in the statement
sub mkcdb {
    my ($file, $sth, $bound_vars) = @_;
    my @tmp;
    $sth->bind_columns(\(@tmp[1..$bound_vars]));

    my $cdb = new CDB_File ($file, "$file.$$")
        or die "Couldn't start creation of CDB '$file': $!\n";
    $cdb->insert(@tmp[1..$bound_vars]) while $sth->fetch;
    $cdb->finish;
}

__END__

=head1 NAME

make_mail_config.pl

=head1 SYNOPSIS

mail_mail_config.pl

=head1 DESCRIPTION

make_mail_config.pl performs a series of SQL queries and generates
single CDB (Constant DataBase) files.

=head1 EXAMPLES

Some exim configuration examples. Please consult exim's documentation
for a fuller explanation.

domainlist local_domains = @ : @[] : cdb;/etc/mail/local_domains.cdb
domainlist relay_domains = cdb;/etc/mail/relay_domains.cdb
domainlist forward_domains = cdb;/etc/mail/forward_domains.cdb

forwards:
driver = redirect
domains = +forward_domains
data = ${lookup{$local_part@$domain} cdb*@ {/etc/mail/forwards.cdb}{$value}fail}

=head1 SEE ALSO

L<CDB_File>, L<http://www.exim.org/docs.html>


=head1 AUTHOR

Copyright (C) 2003 Paul Makepeace L<http://paulm.com>

This program is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.

=cut



--
Paul Makepeace ....................................... http://paulm.com/

"What is the biggest thing? The way of the fish."
-- http://paulm.com/toys/surrealism/