[vhffs-dev] [1377] modified mx2 mirror, now using different tables name of these used in main installation, in order to allow both on the same database

[subversion@xxxxxxxxxxxxx]

Revision: 1377
Author:   gradator
Date:     2009-03-15 16:27:33 +0100 (Sun, 15 Mar 2009)

Log Message:
-----------
modified mx2 mirror, now using different tables name of these used in main installation, in order to allow both on the same database

Modified Paths:
--------------
    trunk/vhffs-backend/src/mirror/mx2-mirror.pl
    trunk/vhffs-backend/src/mirror/mx2-mirror.sql
    trunk/vhffs-doc/config/exim4-mx2/exim4.conf


Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2009-03-15 13:58:30 UTC (rev 1376)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2009-03-15 15:27:33 UTC (rev 1377)
@@ -31,8 +31,8 @@
 
 # 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_mxdomain and
-# vhffs_addresses tables.
+# Slave database must have at least vhffs_mx2domain and
+# vhffs_mx2addresses tables.
 
 use DBI;
 use strict;
@@ -64,9 +64,9 @@
     or die("Unable to open slave connection\n");
 
 # Create temporary tables
-$slave_dbh->do('CREATE TEMPORARY TABLE tmp_mxdomain(LIKE vhffs_mxdomain)')
+$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_addresses)')
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_addresses(LIKE vhffs_mx2addresses)')
     or die("Unable to create temporary boxes table\n");
 
 $master_dbh->{AutoCommit} = 0;
@@ -157,14 +157,14 @@
 
 my $count;
 
-($count = $slave_dbh->do(q{DELETE FROM vhffs_addresses WHERE
+($count = $slave_dbh->do(q{DELETE FROM vhffs_mx2addresses WHERE
     NOT EXISTS(SELECT * FROM tmp_addresses t 
-    WHERE t.domain = vhffs_addresses.domain
-    AND t.local_part = vhffs_addresses.local_part)}))
+    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";
 
-($count = $slave_dbh->do(q{DELETE from vhffs_mxdomain
+($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";
@@ -172,22 +172,22 @@
 # The only update case is domain catchall since others data are
 # unique
 
-($count = $slave_dbh->do(q{UPDATE vhffs_mxdomain SET catchall = tmp.catchall
-    FROM tmp_mxdomain tmp WHERE tmp.domain = vhffs_mxdomain.domain}))
+($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";
 
 # Insert new addresses/domains
 
-($count = $slave_dbh->do(q{INSERT INTO vhffs_mxdomain(domain, catchall)
+($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_mxdomain)}))
+    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_addresses(domain, local_part)
+($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_addresses a WHERE a.domain = tmp.domain AND
+    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";

Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2009-03-15 13:58:30 UTC (rev 1376)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2009-03-15 15:27:33 UTC (rev 1377)
@@ -1,13 +1,13 @@
-CREATE TABLE vhffs_mxdomain(
+CREATE TABLE vhffs_mx2domain(
     domain VARCHAR,
     catchall VARCHAR
 );
 
-CREATE TABLE vhffs_addresses(
+CREATE TABLE vhffs_mx2addresses(
     local_part VARCHAR,
     domain VARCHAR
 );
 
-CREATE UNIQUE INDEX vhffs_mxdomain_unique_domain ON vhffs_mxdomain(domain);
+CREATE UNIQUE INDEX vhffs_mx2domain_unique_domain ON vhffs_mx2domain(domain);
 
-CREATE UNIQUE INDEX vhffs_addresses_unique_couple ON vhffs_addresses(local_part, domain);
+CREATE UNIQUE INDEX vhffs_mx2addresses_unique_couple ON vhffs_mx2addresses(local_part, domain);

Modified: trunk/vhffs-doc/config/exim4-mx2/exim4.conf
===================================================================
--- trunk/vhffs-doc/config/exim4-mx2/exim4.conf	2009-03-15 13:58:30 UTC (rev 1376)
+++ trunk/vhffs-doc/config/exim4-mx2/exim4.conf	2009-03-15 15:27:33 UTC (rev 1377)
@@ -1,9 +1,10 @@
 hide pgsql_servers = PGHOST/PGDB/PGUSER/PGPASS
+# Use these query if your MX2 directly uses VHFFS database
 PGSQL_RELAY_DOMAIN = ${lookup pgsql{SELECT domain FROM vhffs_mxdomain WHERE domain = '${quote_pgsql:$domain}'}}
-# Use this query if your MX2 directly uses VHFFS database
 PGSQL_RELAY_CHECKLOCALPART = ${lookup pgsql{SELECT d.domain FROM vhffs_mxdomain d WHERE d.domain = '${quote_pgsql:$domain}' AND (d.catchall != '' OR EXISTS (SELECT domain FROM vhffs_boxes WHERE domain = '${quote_pgsql:$domain}' AND local_part = '${quote_pgsql:$local_part}' AND state = 6) OR EXISTS (SELECT domain FROM vhffs_forward WHERE domain = '${quote_pgsql:$domain}' AND local_part = '${quote_pgsql:$local_part}') OR EXISTS (SELECT domain FROM vhffs_ml WHERE domain = '${quote_pgsql:$domain}' AND (local_part = '${quote_pgsql:$local_part}' OR local_part || '-request' = '${quote_pgsql:$local_part}')))}}
-# Use this query if your MX has a mirrored VHFFS db (using mirror-mx2.pl)
-# PGSQL_RELAY_CHECKLOCALPART = ${lookup pgsql{SELECT d.domain FROM vhffs_mxdomain d WHERE d.domain = '${quote_pgsql:$domain}' AND (d.catchall != '' OR EXISTS (SELECT domain FROM vhffs_addresses WHERE domain = '${quote_pgsql:$domain}' AND local_part = '${quote_pgsql:$local_part}'))}}
+# Use these query if your MX has a mirrored VHFFS db (using mirror-mx2.pl)
+# PGSQL_RELAY_DOMAIN = ${lookup pgsql{SELECT domain FROM vhffs_mx2domain WHERE domain = '${quote_pgsql:$domain}'}}
+# PGSQL_RELAY_CHECKLOCALPART = ${lookup pgsql{SELECT d.domain FROM vhffs_mx2domain d WHERE d.domain = '${quote_pgsql:$domain}' AND (d.catchall != '' OR EXISTS (SELECT domain FROM vhffs_mx2addresses WHERE domain = '${quote_pgsql:$domain}' AND local_part = '${quote_pgsql:$local_part}'))}}
 
 exim_path = /usr/sbin/exim4