[vhffs-dev] [2154] added a necessary catchall boolean field on mx2 mirror script

[subversion@xxxxxxxxxxxxx]

Revision: 2154
Author:   gradator
Date:     2012-04-24 22:53:45 +0200 (Tue, 24 Apr 2012)
Log Message:
-----------
added a necessary catchall boolean field on mx2 mirror script

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-23 22:38:09 UTC (rev 2153)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2012-04-24 20:53:45 UTC (rev 2154)
@@ -82,9 +82,9 @@
 	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_mx2(mx_id, domain)
-	VALUES(?, ?)})
-	or die('Unable to prepare INSERT query for tmp_mx'."\n");
+my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx2(mx_id, domain, catchall)
+	VALUES(?, ?, false)})
+	or die('Unable to prepare INSERT query for tmp_mx2'."\n");
 
 $msth->execute()
 	or die('Unable to execute SELECT query for vhffs_mx'."\n");
@@ -97,6 +97,27 @@
 $ssth->finish();
 $msth->finish();
 
+# Replicate vhffs_mx_catchall table
+my $msth = $master_dbh->prepare(q{SELECT mx.mx_id FROM vhffs_mx mx
+	INNER JOIN vhffs_object o ON o.object_id=mx.object_id
+	INNER JOIN vhffs_mx_catchall ca ON mx.mx_id=ca.mx_id
+	INNER JOIN vhffs_mx_box box ON box.box_id=ca.box_id
+	WHERE box.state=6 AND o.state=6 GROUP BY mx.mx_id})
+	or die('Unable to prepare SELECT query for vhffs_mx_catchall'."\n");
+my $ssth = $slave_dbh->prepare(q{UPDATE tmp_mx2 SET catchall=true WHERE mx_id=?})
+	or die('Unable to prepare UPDATE query for tmp_mx2'."\n");
+
+$msth->execute()
+	or die('Unable to execute SELECT query for vhffs_mx_catchall'."\n");
+
+while(my $r = $msth->fetchrow_hashref) {
+	$ssth->execute( $r->{mx_id} )
+	or die('Unable to set catchall for mx #'.$r->{mx_id}."\n");
+}
+
+$ssth->finish();
+$msth->finish();
+
 # Replicate all localparts
 $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_mx2_localpart(mx_id, localpart)
 	VALUES(?, ?)})
@@ -186,8 +207,8 @@
 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
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mx2(mx_id, domain, catchall)
+	SELECT mx_id, domain, catchall 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";
@@ -199,5 +220,10 @@
 	or die('Unable to insert new localparts'."\n");
 print int($count).' localparts added'."\n";
 
+# Update catchall field
+$slave_dbh->do(q{UPDATE vhffs_mx2 SET catchall = tmp.catchall
+	FROM tmp_mx2 tmp WHERE tmp.mx_id = vhffs_mx2.mx_id})
+	or die('Unable to update mx data'."\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-23 22:38:09 UTC (rev 2153)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2012-04-24 20:53:45 UTC (rev 2154)
@@ -1,6 +1,7 @@
 CREATE TABLE vhffs_mx2 (
 	mx_id int4 NOT NULL,
 	domain varchar NOT NULL,
+	catchall bool NOT NULL DEFAULT false,
 	CONSTRAINT vhffs_mx2_unique_mx_id PRIMARY KEY (mx_id),
 	CONSTRAINT vhffs_mx2_unique_domain UNIQUE (domain)
 ) WITH (OIDS);