[vhffs-dev] [690] Added mx2 temporary migration script (will be improved

[vhffs-dev] [690] Added mx2 temporary migration script (will be improved soon) and doc.

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

To: vhffs-dev@xxxxxxxxx
Subject: [vhffs-dev] [690] Added mx2 temporary migration script (will be improved soon) and doc.
From: subversion@xxxxxxxxx
Date: Sun, 08 Jul 2007 15:47:26 +0200

Revision: 690
Author:   beuss
Date:     2007-07-08 13:47:25 +0000 (Sun, 08 Jul 2007)

Log Message:
-----------
Added mx2 temporary migration script (will be improved soon) and doc.

Modified Paths:
--------------
    trunk/vhffs-doc/config/exim4-mx2/exim4.conf
    trunk/vhffs-doc/src/fr/part1/mirror.xml
    trunk/vhffs-doc/src/fr/vhffs-doc.xml

Added Paths:
-----------
    trunk/vhffs-backend/src/mirror/mx2-mirror.pl
    trunk/vhffs-backend/src/mirror/mx2-mirror.sql
    trunk/vhffs-doc/src/fr/part1/exim.xml

Added: trunk/vhffs-backend/src/mirror/mx2-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.pl	2007-07-08 13:47:25 UTC (rev 690)
@@ -0,0 +1,229 @@
+#!/usr/bin/perl
+# Copyright (c) vhffs project and its contributors
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions
+# are met:
+#
+# 1. Redistributions of source code must retain the above copyright
+#   notice, this list of conditions and the following disclaimer.
+#2. Redistributions in binary form must reproduce the above copyright
+#   notice, this list of conditions and the following disclaimer in
+#   the documentation and/or other materials provided with the
+#   distribution.
+#3. Neither the name of vhffs nor the names of its contributors
+#   may be used to endorse or promote products derived from this
+#   software without specific prior written permission.
+#
+#THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+#"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+#LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
+#FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
+#COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
+#INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+#BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+#LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+#CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
+# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
+# ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
+# POSSIBILITY OF SUCH DAMAGE.
+
+# 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 
+
+use DBI;
+use strict;
+
+# Master DB params
+my $MASTER_DB_HOST = 'localhost';
+my $MASTER_DB_PORT = 5432;
+my $MASTER_DB_NAME = 'vhffs_auth';
+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 = 'exim';
+my $SLAVE_DB_USER = 'vhffs';
+my $SLAVE_DB_PASS = 'vhffs';
+
+# We've to connect to the master DB, fetch
+# mxdomain, boxes, forward & ml
+# 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 $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");
+
+# 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");
+
+$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");
+
+# Replicate vhffs_mxdomain table
+my $msth = $master_dbh->prepare(q{SELECT d.domain, d.catchall
+    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(domain, catchall)
+    VALUES(?, ?)}) 
+    or die("Unable to prepare INSERT query for tmp_mxdomain\n");
+
+$msth->execute()
+    or die("Unable to execute SELECT query for vhffs_mxdomain\n");
+
+while(my $row = $msth->fetchrow_hashref()) {
+    $ssth->execute($row->{domain}, $row->{catchall})
+    or die('Unable to insert mxdomain #'.$row->{id}."\n");
+}
+
+$ssth->finish();
+$msth->finish();
+
+# Replicate vhffs_boxes table
+
+$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)
+    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");
+
+while(my $row = $msth->fetchrow_hashref()) {
+    $ssth->execute($row->{domain}, $row->{local_part})
+            or die('Unable to insert box '.$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"); 
+
+# 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})
+    or die("Unable to update catchall for existing domains\n");
+
+# Insert new boxes/forward/ml/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_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();

Added: trunk/vhffs-backend/src/mirror/mx2-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2007-07-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-backend/src/mirror/mx2-mirror.sql	2007-07-08 13:47:25 UTC (rev 690)
@@ -0,0 +1,21 @@
+CREATE TABLE vhffs_mxdomain(
+    domain VARCHAR,
+    catchall VARCHAR
+);
+
+CREATE TABLE vhffs_boxes(
+    domain VARCHAR,
+    local_part 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);

Modified: trunk/vhffs-doc/config/exim4-mx2/exim4.conf
===================================================================
--- trunk/vhffs-doc/config/exim4-mx2/exim4.conf	2007-07-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-doc/config/exim4-mx2/exim4.conf	2007-07-08 13:47:25 UTC (rev 690)
@@ -1,5 +1,5 @@
 hide pgsql_servers = PGHOST/PGDB/PGUSER/PGPASS
-PGSQL_RELAY_DOMAIN = ${lookup pgsql{SELECT DISTINCT domain FROM vhffs_mxdomain WHERE domain = '$domain'}}
+PGSQL_RELAY_DOMAIN = ${lookup pgsql{SELECT domain FROM vhffs_mxdomain WHERE domain = '$domain'}}
 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')))}}
 
 exim_path = /usr/sbin/exim4

Added: trunk/vhffs-doc/src/fr/part1/exim.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/exim.xml	2007-07-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-doc/src/fr/part1/exim.xml	2007-07-08 13:47:25 UTC (rev 690)
@@ -0,0 +1,10 @@
+<?xml version="1.0" encoding="US-ASCII"?>
+<chapter id="chap-exim">
+  <title id="title-exim">Configuration de Exim (serveur mail)</title>
+
+  <section>
+    <title>Introduction</title>
+
+    <para></para>
+  </section>
+</chapter>
\ 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-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-doc/src/fr/part1/mirror.xml	2007-07-08 13:47:25 UTC (rev 690)
@@ -192,15 +192,106 @@
 
       <para>Le troisi&egrave;me service &agrave; disposer de script de
       r&eacute;plication livr&eacute;s avec VHFFS est le serveur mail Exim.
-      Cela permet d'avoir un mx2, en particulier, avec un l&eacute;ger retard
-      sur la configuration principale mais n'effectuant aucune requ&ecirc;te
-      distante.</para>
+      Deux scripts sont fournis, le premier permet une r&eacute;plication sur
+      le serveur mail primaire (appel&eacute; mx1 dans la suite du document),
+      le second une r&eacute;plication sur le serveur mail secondaire. La
+      principale diff&eacute;rence entre les deux tient en la quantit&eacute;
+      de donn&eacute;es r&eacute;pliqu&eacute;e. Dans le cas du mx1, il est
+      n&eacute;cessaire d'assurer le bon fonctionnement de listengine, aussi
+      beaucoup de donn&eacute;es propres &agrave; vhffs sont
+      r&eacute;pliqu&eacute;es&nbsp;; dans le cas du mx2, seules les
+      donn&eacute;es permettant de v&eacute;rifier qu'une adresse existe bien
+      sont r&eacute;pliqu&eacute;es.</para>
+
+      <para>Les configurations des serveurs mail sont fournies dans les
+      r&eacute;pertoires <filename
+      class="directory">vhffs-doc/config/exim4-mx1/</filename> et <filename
+      class="directory">vhffs-doc/config/exim4-mx2/</filename> &agrave; la
+      racine des sources. Vous trouverez plus d'informations dans le <xref
+      linkend="chap-exim" /> (<xref endterm="title-exim"
+      linkend="chap-exim" />).</para>
     </section>
 
     <section>
-      <title>Configuration</title>
+      <title>Serveur mail primaire</title>
 
-      <para></para>
+      <section>
+        <title>Configuration</title>
+
+        <para>La configuration de la r&eacute;plication pour le serveur mail
+        primaire consiste &agrave; positionner les diff&eacute;rentes
+        variables <varname>$MASTER_DB_HOST</varname>,
+        <varname>$MASTER_DB_PORT</varname>,
+        <varname>$MASTER_DB_NAME</varname>, <varname>$MASTER_DB_USER</varname>
+        et <varname>$MASTER_DB_USER</varname> (ainsi que leurs homologues
+        pr&eacute;fix&eacute;es par <varname>$SLAVE_</varname>) dans le script
+        <filename>vhffs-backend/mirror/mx1-mirror.pl</filename>.</para>
+      </section>
     </section>
+
+    <section>
+      <title>Serveur mail secondaire</title>
+
+      <section>
+        <title>Configuration</title>
+
+        <para>La configuration du script est analogue &agrave; celle du script
+        concernant le serveur mail primaire. Il suffit de positionner les
+        diff&eacute;rentes variables du script aux valeurs ad&eacute;quates
+        pour que la connexion aux deux bases de donn&eacute;es se fasse
+        convenablement. Enfin, le script doit &ecirc;tre lanc&eacute; par
+        <systemitem class="daemon">cron</systemitem>.</para>
+      </section>
+
+      <section>
+        <title>Sch&eacute;ma de la base de donn&eacute;es</title>
+
+        <para>Le serveur mail secondaire, lorsqu'il est utilis&eacute; avec la
+        configuration pr&eacute;sent&eacute;e dans ce manuel, se contente de
+        v&eacute;rifier que les adresses email sont connues du syst&egrave;me.
+        Aussi, il a besoin de tr&egrave;s peu d'informations&nbsp;: les
+        domaines mail, les bo&icirc;tes, les redirections et les listes de
+        diffusion existants.</para>
+
+        <para>Le sch&eacute;ma de la base de donn&eacute;es est disponible
+        dans le r&eacute;pertoire
+        <filename>vhffs-backend/src/mirror/mx2-mirror.sql</filename>. La base
+        esclave utilis&eacute;e peut contenir des champs
+        suppl&eacute;mentaires, cependant, ceux-ci devront avoir des valeurs
+        par d&eacute;faut pour &eacute;viter toute interruption du
+        script.</para>
+
+        <example>
+          <title>Sch&eacute;ma de base de donn&eacute;es du serveur mail
+          secondaire</title>
+
+          <literallayout>CREATE TABLE vhffs_mxdomain(
+    domain VARCHAR,
+    catchall VARCHAR
+);
+
+CREATE TABLE vhffs_boxes(
+    domain VARCHAR,
+    local_part 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);</literallayout>
+        </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>
+      </section>
+    </section>
   </section>
 </chapter>
\ No newline at end of file

Modified: trunk/vhffs-doc/src/fr/vhffs-doc.xml
===================================================================
--- trunk/vhffs-doc/src/fr/vhffs-doc.xml	2007-07-08 02:32:11 UTC (rev 689)
+++ trunk/vhffs-doc/src/fr/vhffs-doc.xml	2007-07-08 13:47:25 UTC (rev 690)
@@ -2,6 +2,7 @@
 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
 "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd"; [
 <!ENTITY __mydns__f3rg7ac0 SYSTEM "part1/mydns.xml">
+<!ENTITY __exim__f3vgz5it SYSTEM "part1/exim.xml">
 <!ENTITY __mirror__f3rb0jwd SYSTEM "part1/mirror.xml">
 ]>
 <book lang="fr">
@@ -28,6 +29,8 @@
 
     &__mydns__f3rg7ac0;
 
+    &__exim__f3vgz5it;
+
     &__mirror__f3rb0jwd;
   </part>
 
@@ -40,4 +43,4 @@
       <para>&nbsp;</para>
     </chapter>
   </part>
-</book>
\ No newline at end of file
+</book>

Messages sorted by: [ date | thread ]
Prev by Date: [vhffs-dev] [689] Some improvements in avatar support (well, nobody is going to use this feature...)
Next by Date: [vhffs-dev] [691] Well...
Previous by thread: [vhffs-dev] [689] Some improvements in avatar support (well, nobody is going to use this feature...)
Next by thread: [vhffs-dev] [691] Well...

Mail converted by MHonArc 2.6.19+

http://listengine.tuxfamily.org/