[vhffs-dev] [2149] updated mx1 mirroring script using the new mail database schema, improved mx2 mirroring script

[ Thread Index | Date Index | More vhffs.org/vhffs-dev Archives ]


Revision: 2149
Author:   gradator
Date:     2012-04-16 00:32:46 +0200 (Mon, 16 Apr 2012)
Log Message:
-----------
updated mx1 mirroring script using the new mail database schema, improved mx2 mirroring script

Modified Paths:
--------------
    trunk/vhffs-backend/src/mirror/mx1-mirror.pl
    trunk/vhffs-backend/src/mirror/mx1-mirror.sql
    trunk/vhffs-backend/src/mirror/mx2-mirror.pl

Modified: trunk/vhffs-backend/src/mirror/mx1-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.pl	2012-04-15 18:16:59 UTC (rev 2148)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.pl	2012-04-15 22:32:46 UTC (rev 2149)
@@ -31,212 +31,255 @@
 
 # Mirroring script for exim on mx1.
 # Set master & slave DB params and put it in a cron.
-# Slave database must have at least vhffs_mxdomain,
-# vhffs_boxes, vhffs_forward, vhffs_ml, vhffs_mx_ml_subscribers
-# and vhffs_object tables
 
+# Slave database must have
+#  - vhffs_object
+#  - vhffs_mx
+#  - vhffs_mx_localpart
+#  - vhffs_mx_redirect
+#  - vhffs_mx_box
+#  - vhffs_mx_catchall
+#  - vhffs_mx_ml
+#  - vhffs_mx_ml_subscribers
+# tables from mx1-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';
+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 = 'mailmirror';
+my $SLAVE_DB_DATASOURCE = 'database=mailmirror;host=localhost;port=5432';
 my $SLAVE_DB_USER = 'mailmirror';
-my $SLAVE_DB_PASS = 'vhffs';
+my $SLAVE_DB_PASS = 'mirror';
 
 # We've to connect to the master DB, fetch
-# object, mxdomain, boxes, forward, ml & ml_subscribers
+# object, mx, boxes, redirects, ml & ml_subscribers
 # tables and reinject them in slave DB
-# We just fetch fields usefull 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_mxdomain)')
-    or die("Unable to create temporary MX domain table\n");
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_boxes(LIKE vhffs_boxes)')
-    or die("Unable to create temporary boxes table\n");
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_forward(LIKE vhffs_forward)')
-    or die("Unable to create temporary forward table\n");
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_ml(LIKE vhffs_ml)')
-    or die("Unable to create temporary ml table\n");
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_ml_subscribers(LIKE vhffs_mx_ml_subscribers)')
-    or die("Unable to create temporary ml_subscribers table\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx(LIKE vhffs_mx)')
+	or die('Unable to create temporary MX domain table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_catchall(LIKE vhffs_mx_catchall)')
+	or die('Unable to create temporary catchall table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_localpart(LIKE vhffs_mx_localpart)')
+	or die('Unable to create temporary localparts table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_box(LIKE vhffs_mx_box)')
+	or die('Unable to create temporary boxes table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_redirect(LIKE vhffs_mx_redirect)')
+	or die('Unable to create temporary redirect table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_ml(LIKE vhffs_mx_ml)')
+	or die('Unable to create temporary ml table'."\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mx_ml_subscribers(LIKE vhffs_mx_ml_subscribers)')
+	or die('Unable to create temporary ml_subscribers table'."\n");
 $slave_dbh->do('CREATE TEMPORARY TABLE tmp_object(LIKE vhffs_object)')
-    or die("Unable to create temporary object table\n");
+	or die('Unable to create temporary object table'."\n");
 
-
-
 $master_dbh->{AutoCommit} = 0;
 $slave_dbh->{AutoCommit} = 0;
 
 # 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_object table
 
 # Type 60 is mail objects and 61 is ml objects.
-my $msth = $master_dbh->prepare(q{SELECT o.object_id, o.owner_uid,
-    o.owner_gid, o.date_creation, o.state, o.description, o.refuse_reason, o.type
-    FROM vhffs_object o
-    WHERE o.state = 6 AND (o.type = 60 OR o.type = 61) })
-    or die("Unable to prepare SELECT query for vhffs_object\n");
+my $msth = $master_dbh->prepare(q{SELECT o.object_id, o.owner_uid, o.owner_gid, o.date_creation, o.type
+	FROM vhffs_object o
+	WHERE o.state = 6 AND (o.type = 60 OR o.type = 61) })
+	or die('Unable to prepare SELECT query for vhffs_object'."\n");
 my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_object(object_id,
-    owner_uid, owner_gid, date_creation, state, description, refuse_reason, type) VALUES(?, ?, ?, ?, ?, ?, ?, ?)})
-    or die("Unable to prepare INSERT query for tmp_object\n");
+	owner_uid, owner_gid, date_creation, type) VALUES(?, ?, ?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_object'."\n");
 
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_object\n");
+	or die('Unable to execute SELECT query for vhffs_object'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{object_id}, $row->{owner_uid}, $row->{owner_gid},
-    $row->{date_creation}, $row->{state}, $row->{description}, $row->{refuse_reason},
-    $row->{type} )
-    or die('Unable to insert object #'.$row->{object_id}."\n");
+	$ssth->execute($row->{object_id}, $row->{owner_uid}, $row->{owner_gid},
+	$row->{date_creation}, $row->{type})
+	or die('Unable to insert object #'.$row->{object_id}."\n");
 }
 
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_mxdomain table
-my $msth = $master_dbh->prepare(q{SELECT d.mxdomain_id, d.domain,
-    d.boxes_path, d.catchall, d.object_id 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(mxdomain_id,
-    domain, boxes_path, catchall, object_id) 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, d.object_id 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_mx'."\n");
+my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx(mx_id, domain, object_id) VALUES(?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx'."\n");
 
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_mxdomain\n");
+	or die('Unable to execute SELECT query for vhffs_mx'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{mxdomain_id}, $row->{domain},
-    $row->{boxes_path}, $row->{catchall}, $row->{object_id})
-    or die('Unable to insert mxdomain #'.$row->{id}."\n");
+	$ssth->execute($row->{mx_id}, $row->{domain}, $row->{object_id})
+	or die('Unable to insert mail domain #'.$row->{mx_id}."\n");
 }
 
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_boxes table
+# Replicate vhffs_mx_localpart table
+$msth = $master_dbh->prepare(q{SELECT lp.localpart_id, lp.mx_id, lp.localpart, lp.password, lp.nospam, lp.novirus
+	FROM vhffs_mx_localpart lp
+	INNER JOIN vhffs_mx d ON lp.mx_id = d.mx_id
+	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_mx_localpart'."\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_localpart(localpart_id, mx_id,
+	localpart, password, nospam, novirus)
+	VALUES(?, ?, ?, ?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx_localpart'."\n");
 
-$msth = $master_dbh->prepare(q{SELECT b.domain, b.local_part, b.domain_hash,
-    b.mbox_name, b.password, b.nospam, b.novirus, b.allowpop, b.allowimap,
-    b.state FROM vhffs_boxes b
-    INNER JOIN vhffs_mxdomain d ON d.domain=b.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\n");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_boxes(domain, local_part,
-    domain_hash, mbox_name, password, nospam, novirus, allowpop, allowimap,
-    state)
-    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})
-    or die("Unable to prepare INSERT query for tmp_boxes\n");
-
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_boxes\n");
+	or die('Unable to execute SELECT query for vhffs_mx_localpart'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{domain}, $row->{local_part}, $row->{domain_hash},
-    $row->{mbox_name}, $row->{password}, $row->{nospam}, $row->{novirus},
-    $row->{allowpop}, $row->{allowimap}, $row->{state} )
-            or die('Unable to insert box '.$row->{local_part}.'@'.
-                $row->{domain}."\n");
+	$ssth->execute($row->{localpart_id}, $row->{mx_id}, $row->{localpart},
+	$row->{password}, $row->{nospam}, $row->{novirus})
+	or die('Unable to insert localpart #'.$row->{localpart_id}."\n");
 }
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_forward table
+# Replicate vhffs_mx_redirect table
+$msth = $master_dbh->prepare(q{SELECT r.redirect_id, r.localpart_id, r.redirect
+	FROM vhffs_mx_redirect r
+	INNER JOIN vhffs_mx_localpart lp ON lp.localpart_id = r.localpart_id
+	INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+	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_mx_redirect'."\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_redirect(redirect_id,
+	localpart_id, redirect) VALUES(?, ?, ?)})
+	or die('Unable to prepare INSERT query for vhffs_mx_redirect'."\n");
 
-$msth = $master_dbh->prepare(q{SELECT f.domain, f.local_part, f.remote_name,
-    f.password
-    FROM vhffs_forward f
-    INNER JOIN vhffs_mxdomain d ON d.domain = f.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_forward\n");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_forward(domain,
-        local_part, remote_name, password) VALUES(?, ?, ?, ?)})
-    or die("Unable to prepare INSERT query for vhffs_forward\n");
-
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_forward\n");
+	or die('Unable to execute SELECT query for vhffs_mx_redirect'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{domain}, $row->{local_part},
-        $row->{remote_name}, $row->{password})
-            or die('Unable to insert forward '.$row->{local_part}.
-                '@'.$row->{domain}."\n");
+	$ssth->execute($row->{redirect_id}, $row->{localpart_id},
+	$row->{redirect})
+	or die('Unable to insert redirect #'.$row->{redirect_id}."\n");
 }
 
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_ml table
-# Exim & listengine don't really need all the fields but the VHFFS API does.
+# Replicate vhffs_mx_box table
+$msth = $master_dbh->prepare(q{SELECT b.box_id, b.localpart_id, b.allowpop, b.allowimap
+	FROM vhffs_mx_box b
+	INNER JOIN vhffs_mx_localpart lp ON lp.localpart_id = b.localpart_id
+	INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+	INNER JOIN vhffs_object o ON o.object_id = d.object_id
+	WHERE o.state = 6 AND b.state = 6})
+	or die('Unable to prepare SELECT query for vhffs_mx_box'."\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_box(box_id, localpart_id, allowpop,
+	allowimap) VALUES(?, ?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx_box'."\n");
 
-$msth = $master_dbh->prepare(q{SELECT ml.ml_id, ml.local_part, ml.domain,
-    ml.prefix, ml.object_id, ml.sub_ctrl, ml.post_ctrl, ml.reply_to, ml.open_archive,
-    ml.signature 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");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_ml(ml_id, local_part, domain,
-    prefix, object_id, sub_ctrl, post_ctrl, reply_to, open_archive,
-    signature) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})
-    or die("Unable to prepare INSERT query for tmp_ml\n");
+$msth->execute()
+	or die('Unable to execute SELECT query for vhffs_mx_box'."\n");
 
+while(my $row = $msth->fetchrow_hashref()) {
+	$ssth->execute($row->{box_id}, $row->{localpart_id}, $row->{allowpop},
+	$row->{allowimap})
+	or die('Unable to insert box #'.$row->{box_id}."\n");
+}
+$ssth->finish();
+$msth->finish();
+
+# Replicate vhffs_mx_catchall table
+$msth = $master_dbh->prepare(q{SELECT c.catchall_id, c.mx_id, c.box_id
+	FROM vhffs_mx_catchall c
+	INNER JOIN vhffs_mx d ON d.mx_id = c.mx_id
+	INNER JOIN vhffs_object o ON o.object_id = d.object_id
+	INNER JOIN vhffs_mx_box b ON b.box_id = c.box_id
+	INNER JOIN vhffs_mx_localpart lpb ON lpb.localpart_id = b.localpart_id
+	INNER JOIN vhffs_mx mxb ON mxb.mx_id = lpb.mx_id
+	INNER JOIN vhffs_object ob ON ob.object_id = mxb.object_id
+	WHERE o.state = 6 AND b.state = 6 AND ob.state = 6})
+	or die('Unable to prepare SELECT query for vhffs_mx_box'."\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_catchall(catchall_id, mx_id, box_id)
+	VALUES(?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx_catchall'."\n");
+
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_ml\n");
+	or die('Unable to execute SELECT query for vhffs_mx_catchall'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{ml_id}, $row->{local_part}, $row->{domain},
-    $row->{prefix}, $row->{object_id}, $row->{sub_ctrl}, $row->{post_ctrl},
-    $row->{reply_to}, $row->{open_archive}, $row->{signature})
-            or die('Unable to insert ml '.$row->{local_part}.'@'.
-                $row->{domain}."\n");
+	$ssth->execute($row->{catchall_id}, $row->{mx_id}, $row->{box_id})
+	or die('Unable to insert catchall #'.$row->{catchall_id}."\n");
 }
+$ssth->finish();
+$msth->finish();
 
+# Replicate vhffs_mx_ml table
+$msth = $master_dbh->prepare(q{SELECT ml.ml_id, ml.localpart_id, ml.prefix,
+	ml.object_id, ml.sub_ctrl, ml.post_ctrl, ml.reply_to, ml.open_archive,
+	ml.signature FROM vhffs_mx_ml ml
+	INNER JOIN vhffs_object o ON o.object_id = ml.object_id
+	INNER JOIN vhffs_mx_localpart lp ON lp.localpart_id = ml.localpart_id
+	INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+	INNER JOIN vhffs_object mxo ON mxo.object_id = d.object_id
+	WHERE o.state = 6 AND mxo.state = 6})
+	or die('Unable to prepare SELECT query for vhffs_mx_ml'."\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_ml(ml_id, localpart_id,
+	prefix, object_id, sub_ctrl, post_ctrl, reply_to, open_archive,
+	signature) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx_ml'."\n");
+
+$msth->execute()
+	or die('Unable to execute SELECT query for vhffs_mx_ml'."\n");
+
+while(my $row = $msth->fetchrow_hashref()) {
+	$ssth->execute($row->{ml_id}, $row->{localpart_id},
+	$row->{prefix}, $row->{object_id}, $row->{sub_ctrl}, $row->{post_ctrl},
+	$row->{reply_to}, $row->{open_archive}, $row->{signature})
+	or die('Unable to insert ml #'.$row->{mx_id}."\n");
+}
+
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_ml_subsribers table
-
+# Replicate vhffs_mx_ml_subscribers table
 $msth = $master_dbh->prepare(q{SELECT ms.sub_id, ms.member, ms.perm, ms.hash,
-    ms.ml_id, ms.language
-    FROM vhffs_mx_ml_subscribers ms
-    INNER JOIN vhffs_ml ml ON ms.ml_id = ml.ml_id
-    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_mx_ml_subscribers\n");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_ml_subscribers(sub_id, member,
-    perm, hash, ml_id, language) VALUES(?, ?, ?, ?, ?, ?)})
-    or die("Unable to prepare INSERT query for tmp_ml_subscribers\n");
+	ms.ml_id, ms.language
+	FROM vhffs_mx_ml_subscribers ms
+	INNER JOIN vhffs_mx_ml ml ON ms.ml_id = ml.ml_id
+	INNER JOIN vhffs_object o ON o.object_id = ml.object_id
+	INNER JOIN vhffs_mx_localpart lp ON lp.localpart_id = ml.localpart_id
+	INNER JOIN vhffs_mx d ON d.mx_id = lp.mx_id
+	INNER JOIN vhffs_object mxo ON mxo.object_id = d.object_id
+	WHERE o.state = 6 AND mxo.state = 6})
+	or die("Unable to prepare SELECT query for vhffs_mx_ml_subscribers\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx_ml_subscribers(sub_id, member,
+	perm, hash, ml_id, language) VALUES(?, ?, ?, ?, ?, ?)})
+	or die('Unable to prepare INSERT query for tmp_mx_ml_subscribers'."\n");
 
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_mx_ml_subscribers\n");
+	or die('Unable to execute SELECT query for vhffs_mx_ml_subscribers'."\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{sub_id}, $row->{member}, $row->{perm}, $row->{hash},
-    $row->{ml_id}, $row->{language})
-            or die('Unable to insert ml_subscriber #'.$row->{sub_id});
+	$ssth->execute($row->{sub_id}, $row->{member}, $row->{perm}, $row->{hash},
+	$row->{ml_id}, $row->{language})
+	or die('Unable to insert ml_subscriber #'.$row->{sub_id});
 }
 
 $ssth->finish();
@@ -248,128 +291,146 @@
 my $count;
 
 ($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_ml_subscribers WHERE
-    sub_id NOT IN (SELECT sub_id FROM tmp_ml_subscribers)}))
-    or die("Unable to delete no more existing ml users\n");
-print "$count subscribers deleted\n";
+	sub_id NOT IN (SELECT sub_id FROM tmp_mx_ml_subscribers)}))
+	or die('Unable to delete no more existing ml users'."\n");
+print int($count).' subscribers deleted'."\n";
 
-($count = $slave_dbh->do(q{DELETE FROM vhffs_ml
-    WHERE ml_id NOT IN (SELECT ml_id FROM tmp_ml)}))
-    or die("Unable to delete no more existing ml\n");
-print "$count mailing lists deleted\n";
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_ml
+	WHERE ml_id NOT IN (SELECT ml_id FROM tmp_mx_ml)}))
+	or die('Unable to delete no more existing ml'."\n");
+print int($count).' mailing lists deleted'."\n";
 
-($count = $slave_dbh->do(q{DELETE FROM vhffs_forward
-    WHERE (local_part, domain)
-    NOT IN (SELECT local_part, domain FROM tmp_forward)}))
-    or die("Unable to delete no more existing forwards\n");
-print "$count forwards deleted\n";
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_redirect
+	WHERE redirect_id
+	NOT IN (SELECT redirect_id FROM tmp_mx_redirect)}))
+	or die('Unable to delete no more existing redirects'."\n");
+print int($count).' redirects deleted'."\n";
 
-($count = $slave_dbh->do(q{DELETE FROM vhffs_boxes
-    WHERE (local_part, domain)
-    NOT IN (SELECT local_part, domain FROM tmp_boxes)}))
-    or die("Unable to delete no more existing boxes\n");
-print "$count boxes deleted\n";
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_catchall
+	WHERE catchall_id
+	NOT IN (SELECT catchall_id FROM tmp_mx_catchall)}))
+	or die('Unable to delete no more existing catchall'."\n");
+print int($count).' catchalls deleted'."\n";
 
-($count = $slave_dbh->do(q{DELETE FROM vhffs_mxdomain
-    WHERE (domain) NOT IN (SELECT domain FROM tmp_mxdomain)}))
-    or die("Unable to DELETE no more existing domains\n");
-print "$count domains deleted\n";
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_box
+	WHERE box_id
+	NOT IN (SELECT box_id FROM tmp_mx_box)}))
+	or die('Unable to delete no more existing boxes'."\n");
+print int($count).' boxes deleted'."\n";
 
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_box
+	WHERE box_id
+	NOT IN (SELECT box_id FROM tmp_mx_box)}))
+	or die('Unable to delete no more existing boxes'."\n");
+print int($count).' boxes deleted'."\n";
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx_localpart
+	WHERE localpart_id
+	NOT IN (SELECT localpart_id FROM tmp_mx_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_object
-    WHERE object_id NOT IN(SELECT object_id FROM vhffs_object)}))
-    or die("Unable to delete no more existing objects\n");
-print "$count objects deleted\n";
+	WHERE object_id NOT IN(SELECT object_id FROM vhffs_object)}))
+	or die('Unable to delete no more existing objects'."\n");
+print int($count).' objects deleted'."\n";
 
-# Boxes & forwards don't need to be updated since all data are
-# unique (ie. for us, a box/forward is only deleted or inserted).
+# Update boxes/redirects/ml/domains
 
-# The only potential change in object is the description.
-# Type and state are always set to 61 and 6 for us, owner_uid can't be
-# changed.
-($count = $slave_dbh->do(q{UPDATE vhffs_object SET description = tmp.description,
-    refuse_reason = tmp.refuse_reason, owner_uid = tmp.owner_uid, owner_gid = tmp.owner_gid,
-    state = tmp.state FROM tmp_object tmp WHERE tmp.object_id = vhffs_object.object_id}))
-    or die("Unable to update object table\n");
+# The only potential change in object is owner_uid, owner_gid
+# Type are always set to 60 or 61 for us
+($count = $slave_dbh->do(q{UPDATE vhffs_object SET owner_uid = tmp.owner_uid,
+	owner_gid = tmp.owner_gid, date_creation = tmp.date_creation
+	FROM tmp_object tmp WHERE tmp.object_id = vhffs_object.object_id}))
+	or die('Unable to update object table'."\n");
 
-# For now, others fields aren't updatable
-$slave_dbh->do(q{UPDATE vhffs_mxdomain SET catchall = tmp.catchall
-    FROM tmp_mxdomain tmp WHERE tmp.domain = vhffs_mxdomain.domain})
-    or die("Unable to update catchall for existing domains\n");
+# nothing to update for vhffs_mx
 
-# Password should be deleted from master DB...
-$slave_dbh->do(q{UPDATE vhffs_boxes SET password = tmp.password,
-    nospam = tmp.nospam, novirus = tmp.novirus, allowpop = tmp.allowpop,
-    allowimap = tmp.allowimap, state = tmp.state
-    FROM tmp_boxes tmp WHERE tmp.local_part = vhffs_boxes.local_part AND
-    tmp.domain = vhffs_boxes.domain})
-    or die("Unable to update boxes data\n");
+# nothing to update for vhffs_mx_catchall
 
-$slave_dbh->do(q{UPDATE vhffs_forward SET remote_name = tmp.remote_name,
-    password = tmp.password FROM tmp_forward tmp
-    WHERE tmp.local_part = vhffs_forward.local_part AND
-    tmp.domain = vhffs_forward.domain})
-    or die("Unable to update forwards data\n");
+$slave_dbh->do(q{UPDATE vhffs_mx_localpart SET password = tmp.password,
+	nospam = tmp.nospam, novirus = tmp.novirus
+	FROM tmp_mx_localpart tmp WHERE tmp.localpart_id = vhffs_mx_localpart.localpart_id})
+	or die('Unable to update boxes data'."\n");
 
-$slave_dbh->do(q{UPDATE vhffs_mx_ml_subscribers SET perm = tmp.perm,
-    hash = tmp.hash, language = tmp.language FROM tmp_ml_subscribers tmp
-    WHERE tmp.sub_id = vhffs_mx_ml_subscribers.sub_id})
-    or die("Unable to update subscribers data\n");
+$slave_dbh->do(q{UPDATE vhffs_mx_box SET allowpop = tmp.allowpop,
+	allowimap = tmp.allowimap
+	FROM tmp_mx_box tmp WHERE tmp.box_id = vhffs_mx_box.box_id})
+	or die('Unable to update boxes data'."\n");
 
-$slave_dbh->do(q{UPDATE vhffs_ml SET prefix = tmp.prefix,
-    sub_ctrl = tmp.sub_ctrl, post_ctrl = tmp.post_ctrl,
-    reply_to = tmp.reply_to, open_archive = tmp.open_archive,
-    signature = tmp.signature FROM tmp_ml tmp
-    WHERE tmp.ml_id = vhffs_ml.ml_id})
-    or die("Unable to update mailing lists data\n");
+$slave_dbh->do(q{UPDATE vhffs_mx_redirect SET redirect = tmp.redirect
+	FROM tmp_mx_redirect tmp
+	WHERE tmp.redirect_id = vhffs_mx_redirect.redirect_id})
+	or die('Unable to update redirecs data'."\n");
 
+$slave_dbh->do(q{UPDATE vhffs_mx_ml SET prefix = tmp.prefix,
+	sub_ctrl = tmp.sub_ctrl, post_ctrl = tmp.post_ctrl,
+	reply_to = tmp.reply_to, open_archive = tmp.open_archive,
+	signature = tmp.signature FROM tmp_mx_ml tmp
+	WHERE tmp.ml_id = vhffs_mx_ml.ml_id})
+	or die('Unable to update mailing lists data'."\n");
 
-# Insert new boxes/forward/ml/domains
+$slave_dbh->do(q{UPDATE vhffs_mx_ml_subscribers SET perm = tmp.perm,
+	hash = tmp.hash, language = tmp.language FROM tmp_mx_ml_subscribers tmp
+	WHERE tmp.sub_id = vhffs_mx_ml_subscribers.sub_id})
+	or die('Unable to update subscribers data'."\n");
 
+# Insert new boxes/redirects/ml/domains
+
 ($count = $slave_dbh->do(q{INSERT INTO vhffs_object(object_id, owner_uid, owner_gid,
-    date_creation, state, description, refuse_reason, type) SELECT object_id,
-    owner_uid, owner_gid, date_creation, state, description, refuse_reason, type
-    FROM tmp_object tmp WHERE tmp.object_id NOT IN(SELECT object_id FROM vhffs_object)}))
-    or die("Unable to insert new objects\n");
-print "$count objects inserted\n";
+	date_creation, type) SELECT object_id, owner_uid, owner_gid, date_creation, type
+	FROM tmp_object tmp WHERE tmp.object_id NOT IN(SELECT object_id FROM vhffs_object)}))
+	or die('Unable to insert new objects'."\n");
+print int($count).' objects inserted'."\n";
 
-($count = $slave_dbh->do(q{INSERT INTO vhffs_mxdomain(mxdomain_id, domain,
-    boxes_path, catchall, object_id)
-    SELECT mxdomain_id, domain, boxes_path, catchall,
-    object_id FROM tmp_mxdomain
-    WHERE mxdomain_id NOT IN(SELECT mxdomain_id FROM vhffs_mxdomain)}))
-    or die("Unable to insert new mail domains\n");
-print "$count domains inserted\n";
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx(mx_id, domain, object_id)
+	SELECT mx_id, domain, object_id FROM tmp_mx
+	WHERE mx_id NOT IN(SELECT mx_id FROM vhffs_mx)}))
+	or die('Unable to insert new mail domains'."\n");
+print int($count).' domains inserted'."\n";
 
-($count = $slave_dbh->do(q{INSERT INTO vhffs_boxes(domain, local_part, domain_hash,
-    mbox_name, password, nospam, novirus, allowpop, allowimap, state)
-    SELECT domain, local_part, domain_hash, mbox_name, password, nospam, novirus,
-    allowpop, allowimap, state FROM tmp_boxes tmp
-    WHERE (tmp.local_part, tmp.domain)
-    NOT IN(SELECT local_part, domain FROM vhffs_boxes)}))
-    or die("Unable to insert new boxes\n");
-print "$count boxes inserted\n";
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_localpart(localpart_id, mx_id,
+	localpart, password, nospam, novirus)
+	SELECT localpart_id, mx_id, localpart, password, nospam, novirus
+	FROM tmp_mx_localpart WHERE localpart_id
+	NOT IN(SELECT localpart_id FROM vhffs_mx_localpart)}))
+	or die('Unable to insert new localparts'."\n");
+print int($count).' localparts inserted'."\n";
 
-($count = $slave_dbh->do(q{INSERT INTO vhffs_forward(domain, local_part, remote_name,
-    password)
-    SELECT domain, local_part, remote_name, password FROM tmp_forward tmp
-    WHERE (tmp.local_part, tmp.domain) NOT IN (SELECT local_part, domain
-    FROM vhffs_forward)}))
-    or die("Unable to insert new forwards\n");
-print "$count forwards inserted\n";
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_redirect(redirect_id, localpart_id,
+	redirect)
+	SELECT redirect_id, localpart_id, redirect FROM tmp_mx_redirect tmp
+	WHERE tmp.redirect_id NOT IN (SELECT redirect_id FROM vhffs_mx_redirect)}))
+	or die('Unable to insert new redirects'."\n");
+print int($count).' redirects inserted'."\n";
 
-($count = $slave_dbh->do(q{INSERT INTO vhffs_ml(ml_id, domain, local_part, prefix,
-    object_id, sub_ctrl, post_ctrl, reply_to, open_archive, signature)
-    SELECT ml_id, domain, local_part, prefix, object_id, sub_ctrl, post_ctrl,
-    reply_to, open_archive, signature
-    FROM tmp_ml tmp WHERE tmp.ml_id NOT IN (SELECT ml_id FROM vhffs_ml)}))
-    or die("Unable to insert new ml\n");
-print "$count mailing lists inserted\n";
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_box(box_id, localpart_id, allowpop,
+	allowimap)
+	SELECT box_id, localpart_id, allowpop, allowimap FROM tmp_mx_box tmp
+	WHERE tmp.box_id NOT IN(SELECT box_id FROM vhffs_mx_box)}))
+	or die('Unable to insert new boxes'."\n");
+print int($count).' boxes inserted'."\n";
 
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_catchall(catchall_id, mx_id, box_id)
+	SELECT catchall_id, mx_id, box_id FROM tmp_mx_catchall tmp
+	WHERE tmp.catchall_id NOT IN(SELECT catchall_id FROM vhffs_mx_catchall)}))
+	or die('Unable to insert new catchalls'."\n");
+print int($count).' catchalls inserted'."\n";
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_ml(ml_id, localpart_id, prefix,
+	object_id, sub_ctrl, post_ctrl, reply_to, open_archive, signature)
+	SELECT ml_id, localpart_id, prefix, object_id, sub_ctrl, post_ctrl,
+	reply_to, open_archive, signature
+	FROM tmp_mx_ml tmp WHERE tmp.ml_id NOT IN (SELECT ml_id FROM vhffs_mx_ml)}))
+	or die('Unable to insert new ml'."\n");
+print int($count).' mailing lists inserted'."\n";
+
 ($count = $slave_dbh->do(q{INSERT INTO vhffs_mx_ml_subscribers(sub_id, member, perm, hash,
-    ml_id, language) SELECT sub_id, member, perm, hash, ml_id, language FROM
-    tmp_ml_subscribers ms WHERE ms.sub_id NOT IN(SELECT sub_id FROM
-    vhffs_mx_ml_subscribers)}))
-    or die("Unable to insert new subscribers\n");
-print "$count subscribers inserted\n";
+	ml_id, language) SELECT sub_id, member, perm, hash, ml_id, language FROM
+	tmp_mx_ml_subscribers ms WHERE ms.sub_id NOT IN(SELECT sub_id FROM
+	vhffs_mx_ml_subscribers)}))
+	or die('Unable to insert new subscribers'."\n");
+print int($count).' subscribers inserted'."\n";
+
 $slave_dbh->commit();
 $slave_dbh->disconnect();
-

Modified: trunk/vhffs-backend/src/mirror/mx1-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.sql	2012-04-15 18:16:59 UTC (rev 2148)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.sql	2012-04-15 22:32:46 UTC (rev 2149)
@@ -1,99 +1,101 @@
-CREATE TABLE vhffs_mxdomain
-(
-    mxdomain_id integer,
-    domain varchar,
-    boxes_path varchar NOT NULL,
-    catchall varchar NOT NULL,
-    object_id int4 NOT NULL,
-    CONSTRAINT vhffs_mxdomain_pkey PRIMARY KEY (mxdomain_id)
+CREATE TABLE vhffs_object (
+	object_id integer,
+	owner_uid int4,
+	owner_gid int4,
+	date_creation int8,
+	type int4 NOT NULL DEFAULT 0,
+	CONSTRAINT vhffs_object_pkey PRIMARY KEY (object_id)
 ) WITH OIDS;
+CREATE INDEX idx_vhffs_object_owner_uid ON vhffs_object(owner_uid);
+CREATE INDEX idx_vhffs_object_owner_gid ON vhffs_object(owner_gid);
+CREATE INDEX idx_vhffs_object_type ON vhffs_object(type);
+CREATE INDEX idx_vhffs_object_date_creation ON vhffs_object(date_creation);
 
-CREATE TABLE vhffs_boxes
-(
-    domain varchar,
-    local_part varchar,
-    domain_hash varchar NOT NULL,
-    mbox_name varchar NOT NULL,
-    password varchar NOT NULL,
-    nospam boolean,
-    novirus boolean,
-    allowpop boolean NOT NULL,
-    allowimap boolean NOT NULL,
-    state int4 NOT NULL,
-    CONSTRAINT vhffs_boxes_pkey PRIMARY KEY (domain,local_part)
-) WITH OIDS;
+CREATE TABLE vhffs_mx (
+	mx_id serial,
+	domain varchar NOT NULL,
+	object_id int4 NOT NULL,
+	CONSTRAINT vhffs_mx_pkey PRIMARY KEY (mx_id),
+	CONSTRAINT vhffs_mx_unique_domain UNIQUE (domain),
+	CONSTRAINT vhffs_mx_unique_object_id UNIQUE (object_id)
+) WITH (OIDS);
 
-CREATE TABLE vhffs_forward
-(
-    domain varchar NOT NULL,
-    local_part varchar NOT NULL,
-    remote_name varchar NOT NULL,
-    password varchar NOT NULL,
-    CONSTRAINT vhffs_forward_pkey PRIMARY KEY (domain , local_part)
-) WITH OIDS;
+CREATE TABLE vhffs_mx_catchall (
+	catchall_id serial,
+	mx_id int4 NOT NULL,
+	box_id int4 NOT NULL,
+	CONSTRAINT vhffs_mx_catchall_pkey PRIMARY KEY (catchall_id),
+	CONSTRAINT vhffs_mx_catchall_unique_domain_box UNIQUE (mx_id, box_id)
+) WITH (OIDS);
 
-CREATE TABLE vhffs_ml
-(
-    ml_id integer,
-    local_part varchar(256) NOT NULL,
-    domain varchar(256) NOT NULL,
-    prefix varchar(32),
-    object_id int4 NOT NULL,
-    sub_ctrl int4 NOT NULL,
-    post_ctrl int4 NOT NULL,
-    reply_to boolean,
-    open_archive boolean,
-    signature varchar(250),
-    CONSTRAINT vhffs_ml_pkey PRIMARY KEY (ml_id)
-) WITH OIDS;
+CREATE TABLE vhffs_mx_localpart (
+	localpart_id serial,
+	mx_id int4 NOT NULL,
+	localpart varchar NOT NULL,
+	password varchar,
+	nospam boolean NOT NULL DEFAULT FALSE,
+	novirus boolean NOT NULL DEFAULT FALSE,
+	CONSTRAINT vhffs_mx_localpart_pkey PRIMARY KEY (localpart_id),
+	CONSTRAINT vhffs_mx_localpart_unique_domain_localpart UNIQUE (mx_id, localpart)
+) WITH (OIDS);
 
+CREATE TABLE vhffs_mx_redirect (
+	redirect_id serial,
+	localpart_id int4 NOT NULL,
+	redirect varchar NOT NULL,
+	CONSTRAINT vhffs_mx_redirect_pkey PRIMARY KEY (redirect_id),
+	CONSTRAINT vhffs_mx_redirect_unique_localpart_redirect UNIQUE (localpart_id, redirect)
+) WITH (OIDS);
 
-CREATE TABLE vhffs_mx_ml_subscribers
-(
-    sub_id integer,
-    member varchar(256) NOT NULL,
-    perm int4 NOT NULL,
-    hash varchar,
-    ml_id int4 NOT NULL,
-    language varchar(16),
-    CONSTRAINT vhffs_mx_ml_subscribers_pkey PRIMARY KEY (sub_id)
-) WITH OIDS;
+CREATE TABLE vhffs_mx_box (
+	box_id serial,
+	localpart_id int4 NOT NULL,
+	allowpop boolean NOT NULL DEFAULT TRUE,
+	allowimap boolean NOT NULL DEFAULT TRUE,
+	CONSTRAINT vhffs_mx_box_pkey PRIMARY KEY (box_id),
+	CONSTRAINT vhffs_mx_box_unique_domain_localpart UNIQUE (localpart_id)
+) WITH (OIDS);
 
-CREATE TABLE vhffs_object
-(
-    object_id integer,
-    owner_uid int4,
-    owner_gid int4,
-    date_creation int8,
-    state int4 NOT NULL,
-    description TEXT,
-    refuse_reason TEXT,
-    type int4 NOT NULL DEFAULT 0,
-    CONSTRAINT vhffs_object_pkey PRIMARY KEY (object_id)
-) WITH OIDS;
+CREATE TABLE vhffs_mx_ml (
+	ml_id serial,
+	localpart_id int4 NOT NULL,
+	object_id int4 NOT NULL,
+	prefix varchar,
+	sub_ctrl int4 NOT NULL,
+	post_ctrl int4 NOT NULL,
+	reply_to boolean NOT NULL DEFAULT FALSE,
+	open_archive boolean NOT NULL DEFAULT FALSE,
+	signature text,
+	CONSTRAINT vhffs_mx_ml_pkey PRIMARY KEY (ml_id),
+	CONSTRAINT vhffs_mx_ml_unique_domain_localpart UNIQUE (localpart_id),
+	CONSTRAINT vhffs_mx_ml_unique_object_id UNIQUE (object_id)
+) WITH (OIDS);
+CREATE INDEX idx_vhffs_mx_ml_open_archive ON vhffs_mx_ml(open_archive);
 
--- Some indices, you could add more
-CREATE INDEX idx_vhffs_ml_domain ON vhffs_ml(domain);
-CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
-CREATE INDEX idx_vhffs_boxes_state ON vhffs_boxes(state);
-CREATE INDEX idx_vhffs_ml_open_archive ON vhffs_ml(open_archive);
-CREATE INDEX idx_vhffs_object_owner_uid ON vhffs_object(owner_uid);
-CREATE INDEX idx_vhffs_object_owner_gid ON vhffs_object(owner_gid);
-CREATE INDEX idx_vhffs_object_type ON vhffs_object(type);
-CREATE INDEX idx_vhffs_ml_object_id ON vhffs_ml(object_id);
-CREATE INDEX idx_vhffs_mxdomain_object_id ON vhffs_mxdomain(object_id);
-CREATE INDEX idx_vhffs_object_date_creation ON vhffs_object(date_creation);
+CREATE TABLE vhffs_mx_ml_subscribers (
+	sub_id serial,
+	ml_id int4 NOT NULL,
+	member varchar NOT NULL,
+	perm int4 NOT NULL,
+	hash varchar,
+	language varchar(16),
+	CONSTRAINT vhffs_mx_ml_subscribers_pkey PRIMARY KEY (sub_id),
+	CONSTRAINT vhffs_mx_ml_subscribers_member_list UNIQUE (ml_id, member)
+) WITH (OIDS);
 
 -- Foreign key constraints
-ALTER TABLE vhffs_boxes ADD CONSTRAINT fk_vhffs_boxes_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
-ALTER TABLE vhffs_forward ADD CONSTRAINT fk_vhffs_forward_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
-ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
-ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
-ALTER TABLE vhffs_mx_ml_subscribers ADD CONSTRAINT fk_vhffs_mx_ml_subscribers_vhffs_ml FOREIGN KEY (ml_id) REFERENCES vhffs_ml(ml_id) ON DELETE CASCADE;
-ALTER TABLE vhffs_mxdomain ADD CONSTRAINT fk_vhffs_mxdomain_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx ADD CONSTRAINT fk_vhffs_mx_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
 
--- unique constraints (implicitely create indices)
-ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_domainname UNIQUE (domain);
-ALTER TABLE vhffs_ml ADD CONSTRAINT vhffs_ml_unique_address UNIQUE (local_part, domain);
-ALTER TABLE vhffs_mx_ml_subscribers ADD CONSTRAINT vhffs_mx_ml_subscribers_member_list UNIQUE (ml_id, member);
+ALTER TABLE vhffs_mx_localpart ADD CONSTRAINT fk_vhffs_mx_localpart_vhffs_mx FOREIGN KEY (mx_id) REFERENCES vhffs_mx(mx_id) ON DELETE CASCADE;
 
+ALTER TABLE vhffs_mx_catchall ADD CONSTRAINT fk_vhffs_mx_catchall_vhffs_mx FOREIGN KEY (mx_id) REFERENCES vhffs_mx(mx_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_catchall ADD CONSTRAINT fk_vhffs_mx_catchall_vhffs_mx_box FOREIGN KEY (box_id) REFERENCES vhffs_mx_box(box_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_box ADD CONSTRAINT fk_vhffs_mx_box_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_redirect ADD CONSTRAINT fk_vhffs_mx_redirect_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_ml ADD CONSTRAINT fk_vhffs_mx_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_mx_ml ADD CONSTRAINT fk_vhffs_mx_ml_vhffs_localpart FOREIGN KEY (localpart_id) REFERENCES vhffs_mx_localpart(localpart_id) ON DELETE CASCADE;
+
+ALTER TABLE vhffs_mx_ml_subscribers ADD CONSTRAINT fk_vhffs_mx_ml_subscribers_vhffs_mx_ml FOREIGN KEY (ml_id) REFERENCES vhffs_mx_ml(ml_id) ON DELETE CASCADE;

Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2012-04-15 18:16:59 UTC (rev 2148)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2012-04-15 22:32:46 UTC (rev 2149)
@@ -31,9 +31,12 @@
 
 # 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_mx2 and
-# vhffs_mx2_localpart tables (from mx2-mirror.sql).
 
+# Slave database must have
+#  - vhffs_mx2
+#  - vhffs_mx2_localpart
+# tables from mx2-mirror.sql
+
 use DBI;
 use strict;
 use utf8;
@@ -49,7 +52,7 @@
 my $SLAVE_DB_PASS = 'vhffs';
 
 # We've to connect to the master DB, fetch
-# mxdomain, boxes, forward & ml
+# mx, boxes, forward & ml
 # tables and reinject them in slave DB
 # We just fetch necessary fields for address verification
 
@@ -78,17 +81,17 @@
 	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");
+	or die('Unable to prepare SELECT query for vhffs_mx'."\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");
+	or die('Unable to prepare INSERT query for tmp_mx'."\n");
 
 $msth->execute()
-	or die('Unable to execute SELECT query for vhffs_mxdomain'."\n");
+	or die('Unable to execute SELECT query for vhffs_mx'."\n");
 
-while(my @array = $msth->fetchrow_array) {
-	$ssth->execute(@array)
-	or die('Unable to insert mxdomain #'.$array[0]."\n");
+while(my $r = $msth->fetchrow_hashref) {
+	$ssth->execute( $r->{mx_id}, $r->{domain} )
+	or die('Unable to insert mx #'.$r->{mx_id}."\n");
 }
 
 $ssth->finish();
@@ -113,7 +116,7 @@
 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");
+		or die('Unable to insert localpart #'.$r->{localpart_id}."\n");
 		$distinct->{$r->{mx_id}}->{$r->{localpart}} = undef;
 	}
 }
@@ -132,7 +135,7 @@
 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");
+		or die('Unable to insert localpart #'.$r->{localpart_id}."\n");
 		$distinct->{$r->{mx_id}}->{$r->{localpart}} = undef;
 	}
 }
@@ -152,12 +155,12 @@
 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");
+		or die('Unable to insert localpart #'.$r->{localpart_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");
+	or die('Unable to insert ml request address for localpart #'.$r->{localpart_id}."\n");
 }
 $msth->finish();
 
@@ -171,29 +174,29 @@
 
 # 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");
+	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_mx2
-    WHERE mx_id NOT IN(SELECT mx_id FROM tmp_mx2)}))
-    or die('Unable to delete o more existing domains'."\n");
+	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";
 
 # 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");
+	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{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");
+	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";
 
 $slave_dbh->commit();


Mail converted by MHonArc 2.6.19+ http://listengine.tuxfamily.org/