| [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
]
- To: vhffs-dev@xxxxxxxxx
- Subject: [vhffs-dev] [2149] updated mx1 mirroring script using the new mail database schema, improved mx2 mirroring script
- From: subversion@xxxxxxxxxxxxx
- Date: Mon, 16 Apr 2012 00:32:47 +0200
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();