[vhffs-dev] [704] OK, the view wasn't a good idea, the script will do th

[vhffs-dev] [704] OK, the view wasn't a good idea, the script will do the job.

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

To: vhffs-dev@xxxxxxxxx
Subject: [vhffs-dev] [704] OK, the view wasn't a good idea, the script will do the job.
From: subversion@xxxxxxxxx
Date: Tue, 10 Jul 2007 12:09:44 +0200

Revision: 704
Author:   beuss
Date:     2007-07-10 10:09:43 +0000 (Tue, 10 Jul 2007)

Log Message:
-----------
OK, the view wasn't a good idea, the script will do the job.
Added missing deletion of no more existing domains.

Modified Paths:
--------------
    trunk/vhffs-api/src/Vhffs/Services/Mailing.pm
    trunk/vhffs-backend/src/mirror/mx2-mirror.pl
    trunk/vhffs-backend/src/pgsql/initdb.sql.in
    trunk/vhffs-compat/4.0.sql.in
    trunk/vhffs-doc/src/fr/part1/exim.xml
    trunk/vhffs-doc/src/fr/part1/mirror.xml
    trunk/vhffs-doc/src/fr/vhffs-doc.xml

Modified: trunk/vhffs-api/src/Vhffs/Services/Mailing.pm
===================================================================
--- trunk/vhffs-api/src/Vhffs/Services/Mailing.pm	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-api/src/Vhffs/Services/Mailing.pm	2007-07-10 10:09:43 UTC (rev 704)
@@ -83,6 +83,7 @@
     $dbh->begin_work;
 
     eval {
+        # Group must be the mail domain owner or user default mail domain.
         my $sql = 'SELECT mxdomain_id FROM vhffs_mxdomain WHERE domain = ? AND owner_gid = ?';
         die('Mail domain not found') unless($domain eq $main->get_config()->get_service("mailing")->{'default_domain'} ||
                             $dbh->do($sql, undef, $domain, $group->get_gid) > 0);

Modified: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-10 10:09:43 UTC (rev 704)
@@ -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,
-# vhffs_boxes and vhffs_forward tables 
+# Slave database must have at least vhffs_mxdomain and
+# vhffs_addresses tables.
 
 use DBI;
 use strict;
@@ -97,52 +97,99 @@
 $ssth->finish();
 $msth->finish();
 
-# Replicate vhffs_addresses view
+# Replicate all addresses (boxes, forward & ml)
 
-$msth = $master_dbh->prepare(q{SELECT domain, local_part
-    FROM vhffs_addresses a})
-    or die("Unable to prepare SELECT query for vhffs_addresses\n");
+# First, we fetch boxes & fowards since it's the
+# same scheme (a box/forward depends from a domain which
+# is active or not.
+$msth = $master_dbh->prepare(q{SELECT bf.domain,  bf.local_part
+    FROM (SELECT domain AS domain, local_part AS local_part
+        FROM vhffs_boxes b
+        UNION 
+        SELECT domain AS domain, local_part AS local_part
+        FROM vhffs_forward f) AS bf
+    INNER JOIN vhffs_mxdomain d ON d.domain = bf.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/forward\n");
 $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_addresses(domain, local_part)
     VALUES(?, ?)})
     or die("Unable to prepare INSERT query for tmp_addresses\n");
 
 $msth->execute()
-    or die("Unable to execute SELECT query for vhffs_addresses\n");
+    or die("Unable to execute SELECT query for vhffs_boxes/forward\n");
 
 while(my $row = $msth->fetchrow_hashref()) {
     $ssth->execute($row->{domain}, $row->{local_part})
-            or die('Unable to insert address '.$row->{local_part}.'@'.
+            or die('Unable to insert box/forward '.$row->{local_part}.'@'.
                 $row->{domain}."\n");
 }
 $ssth->finish();
 $msth->finish();
 
+# OK, now we've to fetch mailing lists. The scheme isn't the same since a
+# mailing list is an object. Its activation doesn't really depends from its
+# domain activation. Moreover we've to add -request address.
+
+$msth = $master_dbh->prepare(q{SELECT ml.domain,  ml.local_part
+    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");
+
+$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->execute($row->{domain}, $row->{local_part}.'-request')
+        or die('Unable to insert ml request
+        address'.$row->{local_part}.'-request@'.$row->{domain}."\n");
+}
+$ssth->finish();
+$msth->finish();
+
 # We're done fetching data
 $master_dbh->disconnect();
 
-$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)})
+my $count;
+
+($count = $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");
+print "$count addresses 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 o more existing domains\n");
+print "$count domains deleted\n";
+
 # The only update case is domain catchall since others data are
 # unique
 
-$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_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");
+print "$count domains updated\n";
 
 # Insert new addresses/domains
 
-$slave_dbh->do(q{INSERT INTO vhffs_mxdomain(domain, catchall)
+($count = $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)})
+    IN(SELECT domain FROM vhffs_mxdomain)}))
     or die("Unable to insert new mail domains\n");
+print "$count domains added\n";
 
-$slave_dbh->do(q{INSERT INTO vhffs_addresses(domain, local_part)
+($count = $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)})
+    a.local_part = tmp.local_part)}))
     or die("Unable to insert new addresses\n");
+print "$count addresses added\n";
 
 $slave_dbh->commit();
+$slave_dbh->disconnect();

Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in	2007-07-10 10:09:43 UTC (rev 704)
@@ -387,18 +387,3 @@
 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
-    UNION
-    SELECT local_part || '-request' AS 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 11:04:23 UTC (rev 703)
+++ trunk/vhffs-compat/4.0.sql.in	2007-07-10 10:09:43 UTC (rev 704)
@@ -226,19 +226,6 @@
 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;
 

Modified: trunk/vhffs-doc/src/fr/part1/exim.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/exim.xml	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-doc/src/fr/part1/exim.xml	2007-07-10 10:09:43 UTC (rev 704)
@@ -6,4 +6,128 @@
 
   <para>Cette section pr&eacute;sente la configuration du serveur de mail
   utilis&eacute; g&eacute;n&eacute;ralement avec VHFFS&nbsp;: Exim.</para>
+
+  <para>Si vous voulez offrir un service de qualit&eacute;, il est
+  n&eacute;cessaire de disposer d'au moins deux serveurs mail, le
+  <emphasis>serveur mail primaire</emphasis> et <emphasis>le (ou les)
+  serveur(s) mail secondaire(s)</emphasis>. Leur configuration diff&egrave;re,
+  nous les pr&eacute;sentons dans cette section.</para>
+
+  <section>
+    <title>Serveur mail primaire (mx1)</title>
+
+    <para>Ce serveur est appel&eacute; <literal>mx1</literal> dans les
+    documents relatifs &agrave; VHFFS (du fait qu'il s'appelle
+    g&eacute;n&eacute;ralement mx1.domaine.tld). Sa configuration est plus
+    complexe que pour le serveur mail secondaire. En effet, il va devoir
+    d&eacute;terminer si les adresses sont des bo&icirc;tes mail, des
+    redirections ou des listes de diffusion et agir en
+    cons&eacute;quence.</para>
+
+    <para>Il est possible de faire en sorte que le mx1 utilise directement la
+    base de VHFFS ou bien de r&eacute;pliquer celle-ci afin de r&eacute;duire
+    les co&ucirc;ts d'interrogation (pour plus d'information sur la
+    r&eacute;plication, consultez <xref linkend="sec-mirror-mx1" />).</para>
+
+    <para>La configuration &agrave; adapter se trouve dans le
+    r&eacute;pertoire <filename
+    class="directory">vhffs-doc/config/exim4-mx1/</filename> &agrave; la
+    racine des sources.</para>
+
+    <section>
+      <title>Param&egrave;tres de connexion</title>
+
+      <para>Les param&egrave;tres de connexion sont le premier
+      &eacute;l&eacute;ment &agrave; &ecirc;tre d&eacute;finis par la biais de
+      la variable pgsql_servers. Elle doit &ecirc;tre
+      pr&eacute;c&eacute;d&eacute;e du mot cl&eacute; hide pour &eacute;viter
+      que des utilisateurs "ordinaires" puissent y acc&eacute;der. Vous devez
+      sp&eacute;cifier, dans l'ordre, l'adresse du serveur PostgreSQL (sous la
+      forme h&ocirc;te::port, il y a deux fois deux-points, ce n'est pas une
+      erreur typographique, si vous utilisez le port par d&eacute;faut vour
+      pouvez l'omettre), le nom de la base de donn&eacute;es, le nom
+      d'utilisateur ayant acc&egrave;s aux donn&eacute;es n&eacute;cessaires
+      et enfin son mot de passe.</para>
+    </section>
+
+    <section>
+      <title>Configuration des requ&ecirc;tes</title>
+
+      <para></para>
+    </section>
+  </section>
+
+  <section>
+    <title>Serveur mail secondaire (mx2)</title>
+
+    <para>Le serveur mail secondaire peut &ecirc;tre configur&eacute; de deux
+    fa&ccedil;ons diff&eacute;rentes (en tout cas, nous n'en pr&eacute;sentons
+    que deux). La configuration &agrave; utiliser d&eacute;pend de la
+    fa&ccedil;on dont vous souhaitez organiser votre architecture. Vous avez
+    le choix entre faire en sorte que le serveur mail utilise directement la
+    base de donn&eacute;es VHFFS ou bien r&eacute;pliquer celle-ci &agrave;
+    intervalles r&eacute;guliers sur le mx2 (pour plus d'information,
+    consultez <xref linkend="sec-mirror-mx2" />).</para>
+
+    <para>La seule t&acirc;che du mx2 consiste &agrave; v&eacute;rifier que
+    les adresses qu'on lui soumet existent afin de relayer les mails
+    correspondants au mx1, les adresses inexistantes seront
+    ignor&eacute;es.</para>
+
+    <para>Nous ne rentrons pas ici dans les d&eacute;tails de la
+    configuration, nous pr&eacute;sentons simplement l'interfa&ccedil;age avec
+    VHFFS, c'est-&agrave;-dire principalement les requ&ecirc;tes
+    n&eacute;cessaires &agrave; l'exploitation des adresses VHFFS par
+    Exim.</para>
+
+    <section>
+      <title>Utilisation directe de la base VHFFS</title>
+
+      <section>
+        <title>Param&egrave;tres de connexion</title>
+
+        <para>Les param&egrave;tres de connexion se d&eacute;finissent de la
+        m&ecirc;me fa&ccedil;on que pour le serveur mail primaire.</para>
+      </section>
+
+      <section>
+        <title>Configuration des requ&ecirc;tes</title>
+
+        <para>Seule la requ&ecirc;te
+        <literal>PGSQL_RELAY_CHECKLOCALPART</literal> est &agrave; configurer,
+        elle permet de d&eacute;terminer si une adresse est valide et doit
+        &ecirc;tre relay&eacute;e vers le mx1. Si vous utilisez directement la
+        base de donn&eacute;es VHFFS, elle doit contenir la requ&ecirc;te
+        suivante&nbsp;:</para>
+
+        <para><example>
+            <title>Configuration du mx2 (utilisation de la base VHFFS)</title>
+
+            <para><programlisting>PGSQL_RELAY_CHECKLOCALPART = ${lookup pgsql{SELECT d.domain 
+    FROM vhffs_mxdomain d 
+    WHERE d.domain = '$domain' AND (d.catchall != '' OR
+    EXISTS (SELECT domain FROM vhffs_boxes WHERE domain = '$domain' AND local_part = '$local_part') OR
+    EXISTS (SELECT domain FROM vhffs_forward WHERE domain = '$domain' AND local_part = '$local_part') OR
+    EXISTS (SELECT domain FROM vhffs_ml WHERE domain = '$domain' 
+        AND (local_part = '$local_part' OR local_part || '-request' = '$local_part')))}}</programlisting>En
+            cas d'utilisation de la r&eacute;plication, la requ&ecirc;te est
+            beaucoup plus simple, &eacute;vite de surcharger le serveur de
+            base de donn&eacute;es principal et devrait offrir de meilleures
+            performances.</para>
+
+            <para><example>
+                <title>Configuration du mx2 (r&eacute;plication)</title>
+
+                <para><programlisting>PGSQL_RELAY_CHECKLOCALPART = ${lookup pgsql{SELECT d.domain
+    FROM vhffs_mxdomain WHERE d.domain = '$domain' AND (d.catchall != '' OR
+    EXISTS (SELECT domain FROM vhffs_addresses WHERE domain = '$domain' AND local_part = '$local_part'))}}</programlisting>Le
+                reste de la configuration rel&egrave;ve d'une configuration
+                classique d'Exim. Vous trouverez plus d'informations &agrave;
+                ce sujet sur <ulink
+                url="http://www.exim.org";>http://www.exim.org</ulink>.</para>
+              </example></para>
+          </example></para>
+      </section>
+    </section>
+  </section>
 </section>
\ No newline at end of file

Modified: trunk/vhffs-doc/src/fr/part1/mirror.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/mirror.xml	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-doc/src/fr/part1/mirror.xml	2007-07-10 10:09:43 UTC (rev 704)
@@ -1,7 +1,7 @@
 <?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>
+<chapter id="chap-mirror">
   <title>Scripts de r&eacute;plication</title>
 
   <para>Dans le cadre d'une architecture distribu&eacute;e, il peut &ecirc;tre
@@ -17,7 +17,7 @@
   de donn&eacute;es esclave (les scripts &eacute;tant ex&eacute;cut&eacute;s
   &agrave; intervalle r&eacute;gulier).</para>
 
-  <section>
+  <section id="sec-mirror-nss">
     <title>Name Service Switch (NSS)</title>
 
     <section>
@@ -137,7 +137,7 @@
     </section>
   </section>
 
-  <section>
+  <section id="sec-mirror-mydns">
     <title>myDNS</title>
 
     <section>
@@ -186,8 +186,8 @@
     </section>
   </section>
 
-  <section>
-    <title>exim</title>
+  <section id="sec-mirror-exim">
+    <title id="mirror-mx1">exim</title>
 
     <section>
       <title>Introduction</title>
@@ -213,7 +213,7 @@
       linkend="sec-exim" />.</para>
     </section>
 
-    <section>
+    <section id="sec-mirror-mx1">
       <title>Serveur mail primaire</title>
 
       <section>
@@ -230,7 +230,7 @@
       </section>
     </section>
 
-    <section>
+    <section id="sec-mirror-mx2">
       <title>Serveur mail secondaire</title>
 
       <section>
@@ -283,16 +283,20 @@
         </example>
 
         <para><warning>
-            <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
+            <para>Si vous migrez depuis VHFFS 4.0, il est possible que les
+            tables <database class="table">vhffs_boxes</database>, <database
+            class="table">vhffs_forward</database> et <database
+            class="table">vhffs_ml</database> de la base de donn&eacute;es
+            ma&icirc;tre contienne des doublons entre elles. 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 avant de mettre en place la
             r&eacute;plication, sinon cette derni&egrave;re ne fonctionnera
-            pas.</para>
+            pas (vous pouvez &eacute;galement tenter de lancer la
+            r&eacute;plication &agrave; la main, elle vous indiquera si des
+            erreurs surviennent).</para>
           </warning></para>
       </section>
     </section>

Modified: trunk/vhffs-doc/src/fr/vhffs-doc.xml
===================================================================
--- trunk/vhffs-doc/src/fr/vhffs-doc.xml	2007-07-09 11:04:23 UTC (rev 703)
+++ trunk/vhffs-doc/src/fr/vhffs-doc.xml	2007-07-10 10:09:43 UTC (rev 704)
@@ -1,7 +1,7 @@
 <?xml version="1.0" encoding="US-ASCII"?>
 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
 "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd";>
-<book lang="fr" xmlns:xi="htpp://www.w3.org/2001/XInclude">
+<book lang="fr">
   <title>Documentation de VHFFS</title>
 
   <bookinfo>

Messages sorted by: [ date | thread ]
Prev by Date: [vhffs-dev] [703] Updated exim configuration for mirrored DB.
Next by Date: [vhffs-dev] [705] Added mx1 mirroring script (quite huge heh!).
Previous by thread: [vhffs-dev] [703] Updated exim configuration for mirrored DB.
Next by thread: [vhffs-dev] [705] Added mx1 mirroring script (quite huge heh!).

Mail converted by MHonArc 2.6.19+

http://listengine.tuxfamily.org/