[vhffs-dev] [702] Updated mx2 mirror script to use the new view vhffs

[vhffs-dev] [702] Updated mx2 mirror script to use the new view vhffs_addresses.

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

To: vhffs-dev@xxxxxxxxx
Subject: [vhffs-dev] [702] Updated mx2 mirror script to use the new view vhffs_addresses.
From: subversion@xxxxxxxxx
Date: Mon, 09 Jul 2007 11:38:12 +0200

Revision: 702
Author:   beuss
Date:     2007-07-09 09:38:11 +0000 (Mon, 09 Jul 2007)

Log Message:
-----------
Updated mx2 mirror script to use the new view vhffs_addresses.
Finished doc on mx2 mirroring.

Modified Paths:
--------------
    trunk/vhffs-backend/src/mirror/mx2-mirror.pl
    trunk/vhffs-backend/src/mirror/mx2-mirror.sql
    trunk/vhffs-backend/src/pgsql/initdb.sql.in
    trunk/vhffs-compat/4.0.sql.in
    trunk/vhffs-doc/src/fr/part1/mirror.xml

Added Paths:
-----------
    trunk/vhffs-doc/src/fr/part1/applis.xml
    trunk/vhffs-doc/src/fr/part1/services.xml

Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-09 09:38:11 UTC (rev 702)
@@ -65,12 +65,8 @@
 # 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)')
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_addresses(LIKE vhffs_addresses)')
     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");
 
 $master_dbh->{AutoCommit} = 0;
 $slave_dbh->{AutoCommit} = 0;
@@ -101,96 +97,33 @@
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_boxes table
+# Replicate vhffs_addresses view
 
-$msth = $master_dbh->prepare(q{SELECT b.domain, b.local_part
-    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)
+$msth = $master_dbh->prepare(q{SELECT domain, local_part
+    FROM vhffs_addresses a})
+    or die("Unable to prepare SELECT query for vhffs_addresses\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_addresses(domain, local_part)
     VALUES(?, ?)})
-    or die("Unable to prepare INSERT query for tmp_boxes\n");
+    or die("Unable to prepare INSERT query for tmp_addresses\n");
 
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_boxes\n");
+    or die("Unable to execute SELECT query for vhffs_addresses\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
     $ssth->execute($row->{domain}, $row->{local_part})
-            or die('Unable to insert box '.$row->{local_part}.'@'.
+            or die('Unable to insert address '.$row->{local_part}.'@'.
                 $row->{domain}."\n");
 }
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_forward table
-
-$msth = $master_dbh->prepare(q{SELECT f.domain, f.local_part
-    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) 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");
-
-while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{domain}, $row->{local_part})
-            or die('Unable to insert forward '.$row->{local_part}.
-                '@'.$row->{domain}."\n");
-}
-
-$ssth->finish();
-$msth->finish();
-
-# Replicate vhffs_ml table
-
-$msth = $master_dbh->prepare(q{SELECT ml.domain, ml.local_part
-    FROM vhffs_ml ml 
-    INNER JOIN vhffs_mxdomain d ON d.domain = ml.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_ml\n");
-$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_ml(domain,
-    local_part) VALUES(?, ?)})
-    or die("Unable to prepare INSERT query for tmp_ml\n");
-
-$msth->execute()
-    or die("Unable to execute SELECT query for vhffs_ml\n");
-
-while(my $row = $msth->fetchrow_hashref()) {
-    $ssth->execute($row->{domain}, $row->{local_part})
-            or die('Unable to insert ml '.$row->{local_part}.'@'.
-                $row->{domain}."\n");
-}
-
-$ssth->finish();
-$msth->finish();
-
-
 # We're done fetching data
 $master_dbh->disconnect();
 
-$slave_dbh->do(q{DELETE FROM vhffs_ml WHERE NOT EXISTS(SELECT * FROM
-    tmp_ml t WHERE t.domain = vhffs_ml.domain
-    AND t.local_part = vhffs_ml.local_part)})
-    or die("Unable to delete no more existing ml\n");
-$slave_dbh->do(q{DELETE FROM vhffs_forward WHERE NOT EXISTS(SELECT *
-    FROM tmp_forward t WHERE t.domain = vhffs_forward.domain AND
-    t.local_part = vhffs_forward.local_part)})
-    or die("Unable to delete no more existing forwards\n");
-$slave_dbh->do(q{DELETE FROM vhffs_boxes WHERE NOT EXISTS(SELECT *
-    FROM tmp_boxes t WHERE t.domain = vhffs_boxes.domain
-    AND t.local_part = vhffs_boxes.local_part)})
-    or die("Unable to delete no more existing boxes\n");
-$slave_dbh->do(q{DELETE FROM vhffs_mxdomain WHERE NOT EXISTS(SELECT *
-    FROM tmp_mxdomain t WHERE t.domain = vhffs_mxdomain.domain)})
-    or die("Unable to DELETE no more existing domains\n"); 
+$slave_dbh->do(q{DELETE FROM vhffs_addresses WHERE NOT EXISTS(SELECT * FROM
+    tmp_addresses t WHERE t.domain = vhffs_addresses.domain
+    AND t.local_part = vhffs_addresses.local_part)})
+    or die("Unable to delete no more existing addresses\n");
 
 # The only update case is domain catchall since others data are
 # unique
@@ -199,31 +132,17 @@
     FROM tmp_mxdomain tmp WHERE tmp.domain = vhffs_mxdomain.domain})
     or die("Unable to update catchall for existing domains\n");
 
-# Insert new boxes/forward/ml/domains
+# Insert new addresses/domains
 
 $slave_dbh->do(q{INSERT INTO vhffs_mxdomain(domain, catchall)
     SELECT domain, catchall FROM tmp_mxdomain WHERE domain NOT
     IN(SELECT domain FROM vhffs_mxdomain)})
     or die("Unable to insert new mail domains\n");
 
-$slave_dbh->do(q{INSERT INTO vhffs_boxes(domain, local_part)
-    SELECT domain, local_part FROM tmp_boxes tmp WHERE NOT EXISTS(
-    SELECT * FROM vhffs_boxes b WHERE b.domain = tmp.domain AND
-    b.local_part = tmp.local_part)})
-    or die("Unable to insert new boxes\n");
+$slave_dbh->do(q{INSERT INTO vhffs_addresses(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
+    a.local_part = tmp.local_part)})
+    or die("Unable to insert new addresses\n");
 
-$slave_dbh->do(q{INSERT INTO vhffs_forward(domain, local_part)
-    SELECT domain, local_part FROM tmp_forward tmp WHERE NOT EXISTS(
-    SELECT * FROM vhffs_forward f WHERE f.domain = tmp.domain AND
-    f.local_part = tmp.local_part)})
-    or die("Unable to insert new forwards\n");
-
-
-$slave_dbh->do(q{INSERT INTO vhffs_ml(domain, local_part)
-    SELECT domain, local_part FROM tmp_ml tmp WHERE NOT EXISTS(
-    SELECT * FROM vhffs_ml m WHERE m.domain = tmp.domain AND
-    m.local_part = tmp.local_part)})
-    or die("Unable to insert new ml\n");
-
-
 $slave_dbh->commit();

Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2007-07-09 09:38:11 UTC (rev 702)
@@ -3,19 +3,11 @@
     catchall VARCHAR
 );
 
-CREATE TABLE vhffs_boxes(
-    domain VARCHAR,
-    local_part VARCHAR
+CREATE TABLE vhffs_addresses(
+    local_part VARCHAR,
+    domain VARCHAR
 );
 
-CREATE TABLE vhffs_forward(
-    domain VARCHAR,
-    local_part VARCHAR
-);
-
-CREATE TABLE vhffs_ml(
-    domain VARCHAR,
-    local_part VARCHAR
-);
-
 CREATE UNIQUE INDEX vhffs_mxdomain_unique_domain ON vhffs_mxdomain(domain);
+
+CREATE UNIQUE INDEX vhffs_addresses_unique_couple ON vhffs_addresses(local_part, domain);

Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-07-09 09:38:11 UTC (rev 702)
@@ -386,3 +386,17 @@
 CREATE VIEW vhffs_dns_soa AS
 SELECT dns.dns_id AS id, dns.domain || '.' AS origin , dns.ns, dns.mbox, dns.serial, dns.refresh, dns.retry, dns.expire, dns.minimum, dns.ttl
 FROM vhffs_dns dns INNER JOIN vhffs_object o ON o.object_id=dns.object_id WHERE o.state = 6;
+
+-- View containing all mail addresses on the system
+-- usefull for mx2 direct use or mirroring
+CREATE VIEW vhffs_addresses AS
+    SELECT a.local_part, a.domain FROM
+    (SELECT local_part, domain FROM vhffs_boxes
+    UNION
+    SELECT local_part, domain FROM vhffs_forward
+    UNION
+    SELECT local_part, domain FROM vhffs_ml) AS a
+    INNER JOIN vhffs_mxdomain d ON d.domain = a.domain
+    INNER JOIN vhffs_object o ON o.object_id = d.object_id
+    WHERE o.state = 6;
+

Modified: trunk/vhffs-compat/4.0.sql.in
===================================================================
--- trunk/vhffs-compat/4.0.sql.in	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-compat/4.0.sql.in	2007-07-09 09:38:11 UTC (rev 702)
@@ -226,6 +226,19 @@
 FROM vhffs_users users, vhffs_object object
 WHERE object.object_id=users.object_id;
 
+-- Create new view vhffs_addresses 
+-- usefull for mx2 direct use or mirroring
+CREATE VIEW vhffs_addresses AS
+    SELECT a.local_part, a.domain FROM
+    (SELECT local_part, domain FROM vhffs_boxes
+    UNION
+    SELECT local_part, domain FROM vhffs_forward
+    UNION
+    SELECT local_part, domain FROM vhffs_ml) AS a
+    INNER JOIN vhffs_mxdomain d ON d.domain = a.domain
+    INNER JOIN vhffs_object o ON o.object_id = d.object_id
+    WHERE o.state = 6;
+
 -- add ml_name to vhffs_svn for commit system.
 ALTER TABLE vhffs_svn ADD COLUMN ml_name varchar;
 

Added: trunk/vhffs-doc/src/fr/part1/applis.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/applis.xml	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-doc/src/fr/part1/applis.xml	2007-07-09 09:38:11 UTC (rev 702)
@@ -0,0 +1,17 @@
+<?xml version="1.0" encoding="US-ASCII"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
+"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd";>
+<chapter>
+  <title>Applications tierces</title>
+
+  <section>
+    <title>Introduction</title>
+
+    <para>Ce chapitre pr&eacute;sente la configuration des applications
+    tierces.</para>
+  </section>
+
+  <xi:include href="exim.xml" xmlns:xi="http://www.w3.org/2003/XInclude"; />
+
+  <xi:include href="mydns.xml" xmlns:xi="http://www.w3.org/2003/XInclude"; />
+</chapter>

Modified: trunk/vhffs-doc/src/fr/part1/mirror.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/mirror.xml	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-doc/src/fr/part1/mirror.xml	2007-07-09 09:38:11 UTC (rev 702)
@@ -266,7 +266,7 @@
           <title>Sch&eacute;ma de base de donn&eacute;es du serveur mail
           secondaire</title>
 
-          <literallayout>CREATE TABLE vhffs_mxdomain(
+          <programlisting>CREATE TABLE vhffs_mxdomain(
     domain VARCHAR,
     catchall VARCHAR
 );
@@ -278,16 +278,22 @@
 
 CREATE UNIQUE INDEX vhffs_mxdomain_unique_domain ON vhffs_mxdomain(domain);
 
-CREATE UNIQUE INDEX vhffs_addresses_unique_couple ON vhffs_addresses(local_part, domain);</literallayout>
+CREATE UNIQUE INDEX vhffs_addresses_unique_couple ON vhffs_addresses(local_part, domain);
+</programlisting>
         </example>
 
-        <para>Vous pouvez ajouter d'autres index (en particulier sur les
-        couples domain/local_part) pour acc&eacute;l&eacute;rer les
-        diff&eacute;rentes requ&ecirc;tes.</para>
-
         <para><warning>
-            <para>Si vous migrez</para>
-          </warning>VHFFS-4.0</para>
+            <para>Si vous migrez depuis VHFFS 4.0, il est possible que la vue
+            vhffs_addresses de la base de donn&eacute;es ma&icirc;tre
+            contienne des doublons. Cela vient du fait qu'il n'y avait pas
+            assez de v&eacute;rifications et qu'il &eacute;tait possible de
+            cr&eacute;er une liste de diffusion ayant le m&ecirc;me nom qu'une
+            bo&icirc;te mail ou qu'un forward et <foreignphrase>vice
+            versa</foreignphrase>. V&eacute;rifiez donc bien qu'il n'y a pas
+            de doublons dans la vue avant de mettre en place la
+            r&eacute;plication, sinon cette derni&egrave;re ne fonctionnera
+            pas.</para>
+          </warning></para>
       </section>
     </section>
   </section>

Added: trunk/vhffs-doc/src/fr/part1/services.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/services.xml	2007-07-09 08:45:53 UTC (rev 701)
+++ trunk/vhffs-doc/src/fr/part1/services.xml	2007-07-09 09:38:11 UTC (rev 702)
@@ -0,0 +1,15 @@
+<?xml version="1.0" encoding="US-ASCII"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
+"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd";>
+<chapter>
+  <title>Services VHFFS</title>
+
+  <section>
+    <title>Introduction</title>
+
+    <para>Ce chapitre pr&eacute;sente les diff&eacute;rents services de VHFFS
+    et leur fa&ccedil;on de fonctionner.</para>
+
+    <para></para>
+  </section>
+</chapter>

Messages sorted by: [ date | thread ]
Prev by Date: [vhffs-dev] [701] xsltproc is now directly used for doc building.
Next by Date: [vhffs-dev] [703] Updated exim configuration for mirrored DB.
Previous by thread: [vhffs-dev] [701] xsltproc is now directly used for doc building.
Next by thread: [vhffs-dev] [703] Updated exim configuration for mirrored DB.

Mail converted by MHonArc 2.6.19+

http://listengine.tuxfamily.org/