| [vhffs-dev] [2148] updated mx2 mirroring script using the new mail database schema |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
Revision: 2148
Author: gradator
Date: 2012-04-15 20:16:59 +0200 (Sun, 15 Apr 2012)
Log Message:
-----------
updated mx2 mirroring script using the new mail database schema
Modified Paths:
--------------
trunk/vhffs-backend/src/mirror/mx2-mirror.pl
trunk/vhffs-backend/src/mirror/mx2-mirror.sql
Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl 2012-04-13 21:52:07 UTC (rev 2147)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl 2012-04-15 18:16:59 UTC (rev 2148)
@@ -31,43 +31,39 @@
# Mirroring script for exim on mx2 (no listengine).
# Set master & slave DB params and put it in a cron.
-# Slave database must have at least vhffs_mx2domain and
-# vhffs_mx2addresses tables.
+# Slave database must have at least vhffs_mx2 and
+# vhffs_mx2_localpart tables (from mx2-mirror.sql).
use DBI;
use strict;
use utf8;
# Master DB params
-my $MASTER_DB_HOST = 'localhost';
-my $MASTER_DB_PORT = 5432;
-my $MASTER_DB_NAME = 'vhffs_auth';
+my $MASTER_DB_DATASOURCE = 'database=vhffs;host=localhost;port=5432';
my $MASTER_DB_USER = 'vhffs';
my $MASTER_DB_PASS = 'vhffs';
# Slave DB params
-my $SLAVE_DB_HOST = 'localhost';
-my $SLAVE_DB_PORT = 5432;
-my $SLAVE_DB_NAME = 'exim';
+my $SLAVE_DB_DATASOURCE = 'database=vhffs;host=localhost;port=5432';
my $SLAVE_DB_USER = 'vhffs';
my $SLAVE_DB_PASS = 'vhffs';
# We've to connect to the master DB, fetch
# mxdomain, boxes, forward & ml
# tables and reinject them in slave DB
-# We just fetch fields usefull for address verification
+# We just fetch necessary fields for address verification
-my $master_dbh = DBI->connect("DBI:Pg:dbname=$MASTER_DB_NAME;host=$MASTER_DB_HOST;port=$MASTER_DB_PORT", $MASTER_DB_USER, $MASTER_DB_PASS)
- or die("Unable to open master connection\n");
+my $master_dbh = DBI->connect('DBI:Pg:'.$MASTER_DB_DATASOURCE, $MASTER_DB_USER, $MASTER_DB_PASS)
+ or die('Unable to open master connection'."\n");
-my $slave_dbh = DBI->connect("DBI:Pg:dbname=$SLAVE_DB_NAME;host=$SLAVE_DB_HOST;port=$SLAVE_DB_PORT", $SLAVE_DB_USER, $SLAVE_DB_PASS)
- or die("Unable to open slave connection\n");
+my $slave_dbh = DBI->connect('DBI:Pg:'.$SLAVE_DB_DATASOURCE, $SLAVE_DB_USER, $SLAVE_DB_PASS)
+ or die('Unable to open slave connection'."\n");
# Create temporary tables
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mxdomain(LIKE vhffs_mx2domain)')
- or die("Unable to create temporary MX domain table\n");
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_addresses(LIKE vhffs_mx2addresses)')
- or die("Unable to create temporary boxes table\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx2(LIKE vhffs_mx2)')
+ or die('Unable to create temporary MX domain table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx2_localpart(LIKE vhffs_mx2_localpart)')
+ or die('Unable to create temporary boxes table'."\n");
$master_dbh->{AutoCommit} = 0;
$slave_dbh->{AutoCommit} = 0;
@@ -75,122 +71,130 @@
# We need to set transaction isolation level to serializable to avoid
# foreign key issues
$master_dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')
- or die("Unable to set transaction level on master DB\n");
+ or die('Unable to set transaction level on master DB'."\n");
-# Replicate vhffs_mxdomain table
-my $msth = $master_dbh->prepare(q{SELECT d.domain, d.catchall
- FROM vhffs_mxdomain d
- INNER JOIN vhffs_object o ON o.object_id = d.object_id
- WHERE o.state = 6})
- or die("Unable to prepare SELECT query for vhffs_mxdomain\n");
-my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mxdomain(domain, catchall)
- VALUES(?, ?)})
- or die("Unable to prepare INSERT query for tmp_mxdomain\n");
+# Replicate vhffs_mx table
+my $msth = $master_dbh->prepare(q{SELECT d.mx_id, d.domain
+ FROM vhffs_mx d
+ INNER JOIN vhffs_object o ON o.object_id = d.object_id
+ WHERE o.state = 6})
+ or die('Unable to prepare SELECT query for vhffs_mxdomain'."\n");
+my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx2(mx_id, domain)
+ VALUES(?, ?)})
+ or die('Unable to prepare INSERT query for tmp_mxdomain'."\n");
$msth->execute()
- or die("Unable to execute SELECT query for vhffs_mxdomain\n");
+ or die('Unable to execute SELECT query for vhffs_mxdomain'."\n");
-while(my $row = $msth->fetchrow_hashref()) {
- $ssth->execute($row->{domain}, $row->{catchall})
- or die('Unable to insert mxdomain #'.$row->{id}."\n");
+while(my @array = $msth->fetchrow_array) {
+ $ssth->execute(@array)
+ or die('Unable to insert mxdomain #'.$array[0]."\n");
}
$ssth->finish();
$msth->finish();
-# Replicate all addresses (boxes, forward & ml)
+# Replicate all localparts
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx2_localpart(mx_id, localpart)
+ VALUES(?, ?)})
+ or die('Unable to prepare INSERT query for tmp_mx2_localpart'."\n");
+my $distinct = {};
-# First, we fetch boxes & fowards since it's the
-# same scheme (a box/forward depends from a domain which
-# is active or not.
-$msth = $master_dbh->prepare(q{SELECT bf.domain, bf.local_part
- FROM (SELECT domain AS domain, local_part AS local_part
- FROM vhffs_boxes b WHERE state = 6
- UNION
- SELECT domain AS domain, local_part AS local_part
- FROM vhffs_forward f) AS bf
- INNER JOIN vhffs_mxdomain d ON d.domain = bf.domain
- INNER JOIN vhffs_object o ON o.object_id = d.object_id
- WHERE o.state = 6})
- or die("Unable to prepare SELECT query for vhffs_boxes/forward\n");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_addresses(domain, local_part)
- VALUES(?, ?)})
- or die("Unable to prepare INSERT query for tmp_addresses\n");
+# Replicate boxes localparts
+$msth = $master_dbh->prepare(q{SELECT d.mx_id, lp.localpart
+ FROM vhffs_mx_localpart lp
+ INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+ INNER JOIN vhffs_object o ON o.object_id = d.object_id
+ INNER JOIN vhffs_mx_box b ON b.localpart_id = lp.localpart_id
+ WHERE o.state = 6 AND b.state = 6})
+ or die('Unable to prepare SELECT query for vhffs_mx_box'."\n");
+$msth->execute()
+ or die('Unable to execute SELECT query for vhffs_mx_box'."\n");
+while(my $r = $msth->fetchrow_hashref) {
+ unless( exists $distinct->{$r->{mx_id}}->{$r->{localpart}} ) {
+ $ssth->execute( $r->{mx_id}, $r->{localpart} )
+ or die('Unable to insert localpart '.$r->{localpart}.'@'.$r->{mx_id}."\n");
+ $distinct->{$r->{mx_id}}->{$r->{localpart}} = undef;
+ }
+}
+$msth->finish();
+# Replicate redirects localparts
+$msth = $master_dbh->prepare(q{SELECT d.mx_id, lp.localpart
+ FROM vhffs_mx_localpart lp
+ INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+ INNER JOIN vhffs_object o ON o.object_id = d.object_id
+ INNER JOIN vhffs_mx_redirect r ON r.localpart_id = lp.localpart_id
+ WHERE o.state = 6})
+ or die('Unable to prepare SELECT query for vhffs_mx_redirect'."\n");
$msth->execute()
- or die("Unable to execute SELECT query for vhffs_boxes/forward\n");
-
-while(my $row = $msth->fetchrow_hashref()) {
- $ssth->execute($row->{domain}, $row->{local_part})
- or die('Unable to insert box/forward '.$row->{local_part}.'@'.
- $row->{domain}."\n");
+ or die('Unable to execute SELECT query for vhffs_mx_redirect'."\n");
+while(my $r = $msth->fetchrow_hashref) {
+ unless( exists $distinct->{$r->{mx_id}}->{$r->{localpart}} ) {
+ $ssth->execute( $r->{mx_id}, $r->{localpart} )
+ or die('Unable to insert localpart '.$r->{localpart}.'@'.$r->{mx_id}."\n");
+ $distinct->{$r->{mx_id}}->{$r->{localpart}} = undef;
+ }
}
-$ssth->finish();
$msth->finish();
-# OK, now we've to fetch mailing lists. The scheme isn't the same since a
-# mailing list is an object. Its activation doesn't really depends from its
-# domain activation. Moreover we've to add -request address.
-
-$msth = $master_dbh->prepare(q{SELECT ml.domain, ml.local_part
- FROM vhffs_ml ml
- INNER JOIN vhffs_object o ON o.object_id = ml.object_id
- WHERE o.state = 6})
- or die("Unable to prepare SELECT query for vhffs_ml\n");
-
+# Replicate mailing lists localparts
+$msth = $master_dbh->prepare(q{SELECT d.mx_id, lp.localpart
+ FROM vhffs_mx_localpart lp
+ INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+ INNER JOIN vhffs_object o ON o.object_id = d.object_id
+ INNER JOIN vhffs_mx_ml ml ON ml.localpart_id = lp.localpart_id
+ INNER JOIN vhffs_object mlo ON mlo.object_id = ml.object_id
+ WHERE o.state = 6 AND mlo.state = 6})
+ or die('Unable to prepare SELECT query for vhffs_mx_ml'."\n");
$msth->execute()
- or die("Unable to execute SELECT query for vhffs_ml\n");
-
-while(my $row = $msth->fetchrow_hashref()) {
- $ssth->execute($row->{domain}, $row->{local_part})
- or die('Unable to insert ml '.$row->{local_part}.'@'.
- $row->{domain}."\n");
- $ssth->execute($row->{domain}, $row->{local_part}.'-request')
- or die('Unable to insert ml request
- address'.$row->{local_part}.'-request@'.$row->{domain}."\n");
+ or die('Unable to execute SELECT query for vhffs_mx_ml'."\n");
+while(my $r = $msth->fetchrow_hashref) {
+ unless( exists $distinct->{$r->{mx_id}}->{$r->{localpart}} ) {
+ $ssth->execute( $r->{mx_id}, $r->{localpart} )
+ or die('Unable to insert localpart '.$r->{localpart}.'@'.$r->{mx_id}."\n");
+ $distinct->{$r->{mx_id}}->{$r->{localpart}} = undef;
+ }
+ # -request localpart for mailing lists
+ $ssth->execute($r->{mx_id}, $r->{localpart}.'-request')
+ or die('Unable to insert ml request address'.$r->{localpart}.'-request@'.$r->{mx_id}."\n");
}
-$ssth->finish();
$msth->finish();
+$distinct = {};
+$ssth->finish();
+
# We're done fetching data
$master_dbh->disconnect();
my $count;
-($count = $slave_dbh->do(q{DELETE FROM vhffs_mx2addresses WHERE
- NOT EXISTS(SELECT * FROM tmp_addresses t
- WHERE t.domain = vhffs_mx2addresses.domain
- AND t.local_part = vhffs_mx2addresses.local_part)}))
- or die("Unable to delete no more existing addresses\n");
-print "$count addresses deleted\n";
+# Delete old localparts/domains
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx2_localpart WHERE
+ NOT EXISTS(SELECT * FROM tmp_mx2_localpart t
+ WHERE t.mx_id = vhffs_mx2_localpart.mx_id
+ AND t.localpart = vhffs_mx2_localpart.localpart)}))
+ or die('Unable to delete no more existing localparts'."\n");
+print int($count).' localparts deleted'."\n";
-($count = $slave_dbh->do(q{DELETE from vhffs_mx2domain
- WHERE domain NOT IN(SELECT domain FROM tmp_mxdomain)}))
- or die("Unable to delete o more existing domains\n");
-print "$count domains deleted\n";
+($count = $slave_dbh->do(q{DELETE from vhffs_mx2
+ WHERE mx_id NOT IN(SELECT mx_id FROM tmp_mx2)}))
+ or die('Unable to delete o more existing domains'."\n");
+print int($count).' domains deleted'."\n";
-# The only update case is domain catchall since others data are
-# unique
+# Insert new localparts/domains
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx2(mx_id, domain)
+ SELECT mx_id, domain FROM tmp_mx2 WHERE mx_id NOT
+ IN(SELECT mx_id FROM vhffs_mx2)}))
+ or die('Unable to insert new mail domains'."\n");
+print int($count).' domains added'."\n";
-($count = $slave_dbh->do(q{UPDATE vhffs_mx2domain SET catchall = tmp.catchall
- FROM tmp_mxdomain tmp WHERE tmp.domain = vhffs_mx2domain.domain}))
- or die("Unable to update catchall for existing domains\n");
-print "$count domains updated\n";
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx2_localpart(mx_id, localpart)
+ SELECT mx_id, localpart FROM tmp_mx2_localpart tmp WHERE NOT EXISTS(
+ SELECT * FROM vhffs_mx2_localpart a WHERE a.mx_id = tmp.mx_id AND
+ a.localpart = tmp.localpart)}))
+ or die('Unable to insert new localparts'."\n");
+print int($count).' localparts added'."\n";
-# Insert new addresses/domains
-
-($count = $slave_dbh->do(q{INSERT INTO vhffs_mx2domain(domain, catchall)
- SELECT domain, catchall FROM tmp_mxdomain WHERE domain NOT
- IN(SELECT domain FROM vhffs_mx2domain)}))
- or die("Unable to insert new mail domains\n");
-print "$count domains added\n";
-
-($count = $slave_dbh->do(q{INSERT INTO vhffs_mx2addresses(domain, local_part)
- SELECT domain, local_part FROM tmp_addresses tmp WHERE NOT EXISTS(
- SELECT * FROM vhffs_mx2addresses a WHERE a.domain = tmp.domain AND
- a.local_part = tmp.local_part)}))
- or die("Unable to insert new addresses\n");
-print "$count addresses added\n";
-
$slave_dbh->commit();
$slave_dbh->disconnect();
Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.sql 2012-04-13 21:52:07 UTC (rev 2147)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.sql 2012-04-15 18:16:59 UTC (rev 2148)
@@ -1,13 +1,13 @@
-CREATE TABLE vhffs_mx2domain(
- domain VARCHAR,
- catchall VARCHAR
+CREATE TABLE vhffs_mx2 (
+ mx_id int4 NOT NULL,
+ domain varchar NOT NULL,
+ CONSTRAINT vhffs_mx2_unique_mx_id PRIMARY KEY (mx_id),
+ CONSTRAINT vhffs_mx2_unique_domain UNIQUE (domain)
);
-CREATE TABLE vhffs_mx2addresses(
- local_part VARCHAR,
- domain VARCHAR
+CREATE TABLE vhffs_mx2_localpart (
+ mx_id int4 NOT NULL,
+ localpart varchar NOT NULL,
+ CONSTRAINT vhffs_mx2_localpart_unique_mx_id_localpart PRIMARY KEY (mx_id, localpart),
+ CONSTRAINT fk_vhffs_mx2_localpart_vhffs_mx2 FOREIGN KEY (mx_id) REFERENCES vhffs_mx2(mx_id) ON DELETE CASCADE
);
-
-CREATE UNIQUE INDEX vhffs_mx2domain_unique_domain ON vhffs_mx2domain(domain);
-
-CREATE UNIQUE INDEX vhffs_mx2addresses_unique_couple ON vhffs_mx2addresses(local_part, domain);