| [vhffs-dev] [705] Added mx1 mirroring script (quite huge heh!). |
[ Thread Index |
Date Index
| More vhffs.org/vhffs-dev Archives
]
Revision: 705
Author: beuss
Date: 2007-07-10 14:02:36 +0000 (Tue, 10 Jul 2007)
Log Message:
-----------
Added mx1 mirroring script (quite huge heh!).
Modified Paths:
--------------
trunk/vhffs-backend/src/pgsql/initdb.sql.in
trunk/vhffs-doc/src/fr/part1/mirror.xml
Added Paths:
-----------
trunk/vhffs-backend/src/mirror/mx1-mirror.pl
trunk/vhffs-backend/src/mirror/mx1-mirror.sql
Added: trunk/vhffs-backend/src/mirror/mx1-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.pl 2007-07-10 10:09:43 UTC (rev 704)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.pl 2007-07-10 14:02:36 UTC (rev 705)
@@ -0,0 +1,378 @@
+#!/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 mx1.
+# Set master & slave DB params and put it in a cron.
+# Slave database must have at least vhffs_mxdomain,
+# vhffs_boxes, vhffs_forward, vhffs_ml, vhffs_ml_subscribers
+# and vhffs_object 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 = 'mx1';
+my $SLAVE_DB_USER = 'vhffs';
+my $SLAVE_DB_PASS = 'vhffs';
+
+# We've to connect to the master DB, fetch
+# object, mxdomain, boxes, forward, ml & ml_subscribers
+# 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");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_ml_subscribers(LIKE vhffs_ml_subscribers)')
+ or die("Unable to create temporary ml_subscribers table\n");
+$slave_dbh->do('CREATE TEMPORARY TABLE tmp_object(LIKE vhffs_object)')
+ or die("Unable to create temporary object 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_object table
+
+# Type 61 is ml objects.
+my $msth = $master_dbh->prepare(q{SELECT o.object_id, o.owner_uid,
+ o.date_creation, o.state, o.description
+ FROM vhffs_object o
+ WHERE o.state = 6 AND o.type = 61})
+ or die("Unable to prepare SELECT query for vhffs_object\n");
+my $ssth = $slave_dbh->prepare(q{INSERT INTO tmp_object(object_id,
+ owner_uid, date_creation, state, description) VALUES(?, ?, ?, ?, ?)})
+ or die("Unable to prepare INSERT query for tmp_object\n");
+
+$msth->execute()
+ or die("Unable to execute SELECT query for vhffs_object\n");
+
+while(my $row = $msth->fetchrow_hashref()) {
+ $ssth->execute($row->{object_id}, $row->{owner_uid},
+ $row->{date_creation}, $row->{state}, $row->{description})
+ or die('Unable to insert object #'.$row->{object_id}."\n");
+}
+
+$ssth->finish();
+$msth->finish();
+
+# Replicate vhffs_mxdomain table
+my $msth = $master_dbh->prepare(q{SELECT d.mxdomain_id, d.domain,
+ d.unix_user, d.boxes_path, d.max_popbox, d.catchall,
+ d.owner_uid, d.owner_gid, d.object_id
+ 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(mxdomain_id,
+ domain, unix_user, boxes_path, max_popbox, catchall, owner_uid, owner_gid,
+ object_id) 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->{mxdomain_id}, $row->{domain}, $row->{unix_user},
+ $row->{boxes_path}, $row->{max_popbox}, $row->{catchall},
+ $row->{owner_uid}, $row->{owner_gid}, $row->{object_id})
+ 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, b.domain_hash,
+ b.password_hash, b.mbox_name, b.password, b.nospam, b.novirus
+ 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,
+ domain_hash, password_hash, mbox_name, password, nospam, novirus)
+ 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}, $row->{domain_hash},
+ $row->{password_hash}, $row->{mbox_name}, $row->{password},
+ $row->{nospam}, $row->{novirus})
+ 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, f.remote_name,
+ f.password
+ 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, remote_name, password) 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},
+ $row->{remote_name}, $row->{password})
+ or die('Unable to insert forward '.$row->{local_part}.
+ '@'.$row->{domain}."\n");
+}
+
+$ssth->finish();
+$msth->finish();
+
+# Replicate vhffs_ml table
+# Exim & listengine don't really need all the fields but the VHFFS API does.
+
+$msth = $master_dbh->prepare(q{SELECT ml.ml_id, ml.local_part, ml.domain,
+ ml.prefix, ml.owner_uid, ml.owner_gid, ml.object_id, ml.admin,
+ ml.open_archive, ml.reply_to, ml.signature, ml.sub_ctrl, ml.post_ctrl
+ 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");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_ml(ml_id, local_part, domain,
+ prefix, owner_uid, owner_gid, object_id, admin, open_archive, reply_to,
+ signature, sub_ctrl, post_ctrl) 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->{ml_id}, $row->{local_part}, $row->{domain},
+ $row->{prefix}, $row->{owner_uid}, $row->{owner_gid}, $row->{object_id},
+ $row->{admin}, $row->{open_archive}, $row->{reply_to}, $row->{signature},
+ $row->{sub_ctrl}, $row->{post_ctrl})
+ or die('Unable to insert ml '.$row->{local_part}.'@'.
+ $row->{domain}."\n");
+}
+
+$ssth->finish();
+$msth->finish();
+
+# Replicate vhffs_ml_subsribers table
+
+$msth = $master_dbh->prepare(q{SELECT ms.sub_id, ms.member, ms.perm, ms.hash,
+ ms.ml_id, ms.language
+ FROM vhffs_ml_subscribers ms
+ INNER JOIN vhffs_ml ml ON ms.ml_id = ml.ml_id
+ 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_subscribers\n");
+$ssth = $slave_dbh->prepare(q{INSERT INTO tmp_ml_subscribers(sub_id, member,
+ perm, hash, ml_id, language) VALUES(?, ?, ?, ?, ?, ?)})
+ or die("Unable to prepare INSERT query for tmp_ml_subscribers\n");
+
+$msth->execute()
+ or die("Unable to execute SELECT query for vhffs_ml_subscribers\n");
+
+while(my $row = $msth->fetchrow_hashref()) {
+ $ssth->execute($row->{sub_id}, $row->{member}, $row->{perm}, $row->{hash},
+ $row->{ml_id}, $row->{language})
+ or die('Unable to insert ml_subscriber #'.$row->{sub_id});
+}
+
+$ssth->finish();
+$msth->finish();
+
+# We're done fetching data
+$master_dbh->disconnect();
+
+my $count;
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_ml_subscribers WHERE
+ sub_id NOT IN (SELECT sub_id FROM tmp_ml_subscribers)}))
+ or die("Unable to delete no more existing ml users\n");
+print "$count subscribers deleted\n";
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_ml
+ WHERE ml_id NOT IN (SELECT ml_id FROM tmp_ml)}))
+ or die("Unable to delete no more existing ml\n");
+print "$count mailing lists deleted\n";
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_forward
+ WHERE (local_part, domain)
+ NOT IN (SELECT local_part, domain FROM tmp_forward)}))
+ or die("Unable to delete no more existing forwards\n");
+print "$count forwards deleted\n";
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_boxes
+ WHERE (local_part, domain)
+ NOT IN (SELECT local_part, domain FROM tmp_boxes)}))
+ or die("Unable to delete no more existing boxes\n");
+print "$count boxes 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 no more existing domains\n");
+print "$count domains deleted\n";
+
+($count = $slave_dbh->do(q{DELETE FROM vhffs_object
+ WHERE object_id NOT IN(SELECT object_id FROM vhffs_object)}))
+ or die("Unable to delete no more existing objects\n");
+print "$count objects deleted\n";
+
+# Boxes & forwards don't need to be updated since all data are
+# unique (ie. for us, a box/forward is only deleted or inserted).
+
+# The only potential change in object is the description.
+# Type and state are always set to 61 and 6 for us, owner_uid can't be
+# changed.
+($count = $slave_dbh->do(q{UPDATE vhffs_object SET description = tmp.description
+ FROM tmp_object tmp WHERE tmp.object_id = vhffs_object.object_id}))
+ or die("Unable to update object table\n");
+
+# For now, others fields aren't updatable
+$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");
+
+# Password should be deleted from master DB...
+$slave_dbh->do(q{UPDATE vhffs_boxes SET password_hash = tmp.password_hash,
+ password = tmp.password, nospam = tmp.nospam, novirus = tmp.novirus
+ FROM tmp_boxes tmp WHERE tmp.local_part = vhffs_boxes.local_part AND
+ tmp.domain = vhffs_boxes.domain})
+ or die("Unable to update boxes data\n");
+
+$slave_dbh->do(q{UPDATE vhffs_forward SET remote_name = tmp.remote_name,
+ password = tmp.password FROM tmp_forward tmp
+ WHERE tmp.local_part = vhffs_forward.local_part AND
+ tmp.domain = vhffs_forward.domain})
+ or die("Unable to update forwards data\n");
+
+$slave_dbh->do(q{UPDATE vhffs_ml_subscribers SET perm = tmp.perm,
+ hash = tmp.hash, language = tmp.language FROM tmp_ml_subscribers tmp
+ WHERE tmp.sub_id = vhffs_ml_subscribers.sub_id})
+ or die("Unable to update subscribers data\n");
+
+$slave_dbh->do(q{UPDATE vhffs_ml SET prefix = tmp.prefix, admin = tmp.admin,
+ sub_ctrl = tmp.sub_ctrl, post_ctrl = tmp.post_ctrl,
+ reply_to = tmp.reply_to, open_archive = tmp.open_archive,
+ signature = tmp.signature FROM tmp_ml tmp
+ WHERE tmp.ml_id = vhffs_ml.ml_id})
+ or die("Unable to update mailing lists data\n");
+
+
+# Insert new boxes/forward/ml/domains
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_mxdomain(mxdomain_id, domain, unix_user,
+ boxes_path, max_popbox, catchall, owner_uid, owner_gid, object_id)
+ SELECT mxdomain_id, domain, unix_user, boxes_path, max_popbox, catchall,
+ owner_uid, owner_gid, object_id FROM tmp_mxdomain
+ WHERE mxdomain_id NOT IN(SELECT mxdomain_id FROM vhffs_mxdomain)}))
+ or die("Unable to insert new mail domains\n");
+print "$count domains inserted\n";
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_boxes(domain, local_part, domain_hash,
+ password_hash, mbox_name, password, nospam, novirus)
+ SELECT domain, local_part, domain_hash, password_hash, mbox_name,
+ password, nospam, novirus FROM tmp_boxes tmp
+ WHERE (tmp.local_part, tmp.domain)
+ NOT IN(SELECT local_part, domain FROM vhffs_boxes)}))
+ or die("Unable to insert new boxes\n");
+print "$count boxes inserted\n";
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_forward(domain, local_part, remote_name,
+ password)
+ SELECT domain, local_part, remote_name, password FROM tmp_forward tmp
+ WHERE (tmp.local_part, tmp.domain) NOT IN (SELECT local_part, domain
+ FROM vhffs_forward)}))
+ or die("Unable to insert new forwards\n");
+print "$count forwards inserted\n";
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_object(object_id, owner_uid, date_creation,
+ state, description, type) SELECT object_id, owner_uid, date_creation,
+ state, description, 61 FROM tmp_object tmp
+ WHERE tmp.object_id NOT IN(SELECT object_id FROM vhffs_object)}))
+ or die("Unable to insert new objects\n");
+print "$count objects inserted\n";
+
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_ml(ml_id, domain, local_part, prefix,
+ owner_uid, owner_gid, object_id, admin, sub_ctrl, post_ctrl, reply_to,
+ open_archive, signature)
+ SELECT ml_id, domain, local_part, prefix, owner_uid, owner_gid, object_id,
+ admin, sub_ctrl, post_ctrl, reply_to, open_archive, signature
+ FROM tmp_ml tmp WHERE tmp.ml_id NOT IN (SELECT ml_id FROM vhffs_ml)}))
+ or die("Unable to insert new ml\n");
+print "$count mailing lists inserted\n";
+
+($count = $slave_dbh->do(q{INSERT INTO vhffs_ml_subscribers(sub_id, member, perm, hash,
+ ml_id, language) SELECT sub_id, member, perm, hash, ml_id, language FROM
+ tmp_ml_subscribers ms WHERE ms.sub_id NOT IN(SELECT sub_id FROM
+ vhffs_ml_subscribers)}))
+ or die("Unable to insert new subscribers\n");
+print "$count subscribers inserted\n";
+$slave_dbh->commit();
+$slave_dbh->disconnect();
Added: trunk/vhffs-backend/src/mirror/mx1-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.sql 2007-07-10 10:09:43 UTC (rev 704)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.sql 2007-07-10 14:02:36 UTC (rev 705)
@@ -0,0 +1,88 @@
+CREATE TABLE vhffs_mxdomain
+(
+ mxdomain_id integer,
+ domain varchar,
+ unix_user varchar NOT NULL,
+ boxes_path varchar NOT NULL,
+ max_popbox int4 NOT NULL,
+ catchall varchar NOT NULL,
+ owner_uid int4 NOT NULL,
+ owner_gid int4 NOT NULL,
+ object_id int4 NOT NULL,
+ CONSTRAINT vhffs_mxdomain_pkey PRIMARY KEY (mxdomain_id)
+) WITH OIDS;
+
+CREATE TABLE vhffs_boxes
+(
+ domain varchar,
+ local_part varchar,
+ domain_hash varchar NOT NULL,
+ password_hash varchar NOT NULL,
+ mbox_name varchar NOT NULL,
+ password varchar NOT NULL,
+ nospam boolean,
+ novirus boolean,
+ CONSTRAINT vhffs_boxes_pkey PRIMARY KEY (domain,local_part)
+) WITH OIDS;
+
+CREATE TABLE vhffs_forward
+(
+ domain varchar NOT NULL,
+ local_part varchar NOT NULL,
+ remote_name varchar NOT NULL,
+ password varchar NOT NULL,
+ CONSTRAINT vhffs_forward_pkey PRIMARY KEY (domain , local_part)
+) WITH OIDS;
+
+CREATE TABLE vhffs_ml
+(
+ ml_id integer,
+ local_part varchar(256) NOT NULL,
+ domain varchar(256) NOT NULL,
+ prefix varchar(32),
+ owner_uid int4 NOT NULL,
+ owner_gid int4 NOT NULL,
+ object_id int4 NOT NULL,
+ admin varchar(250) NOT NULL,
+ sub_ctrl int4 NOT NULL,
+ post_ctrl int4 NOT NULL,
+ reply_to boolean,
+ open_archive boolean,
+ signature varchar(250),
+ CONSTRAINT vhffs_ml_pkey PRIMARY KEY (ml_id)
+) WITH OIDS;
+
+
+CREATE TABLE vhffs_ml_subscribers
+(
+ sub_id integer,
+ member varchar(256) NOT NULL,
+ perm int4 NOT NULL,
+ hash varchar,
+ ml_id int4 NOT NULL,
+ language varchar(16),
+ CONSTRAINT vhffs_ml_subscribers_pkey PRIMARY KEY (sub_id)
+) WITH OIDS;
+
+CREATE TABLE vhffs_object
+(
+ object_id integer,
+ owner_uid int4,
+ date_creation timestamp,
+ state int4 NOT NULL,
+ description TEXT,
+ type int4 DEFAULT 0,
+ CONSTRAINT vhffs_object_pkey PRIMARY KEY (object_id)
+) WITH OIDS;
+
+-- Some indices, you could add more
+CREATE INDEX idx_vhffs_ml_domain ON vhffs_ml(domain);
+
+-- Foreign key constraints
+ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
+ALTER TABLE vhffs_ml ADD CONSTRAINT fk_vhffs_ml_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
+ALTER TABLE vhffs_ml_subscribers ADD CONSTRAINT fk_vhffs_ml_subscribers_vhffs_ml FOREIGN KEY (ml_id) REFERENCES vhffs_ml(ml_id) ON DELETE CASCADE;
+
+-- unique constraints (implicitely create indices)
+ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_domainname UNIQUE (domain);
+
Modified: trunk/vhffs-backend/src/pgsql/initdb.sql.in
===================================================================
--- trunk/vhffs-backend/src/pgsql/initdb.sql.in 2007-07-10 10:09:43 UTC (rev 704)
+++ trunk/vhffs-backend/src/pgsql/initdb.sql.in 2007-07-10 14:02:36 UTC (rev 705)
@@ -295,7 +295,7 @@
ALTER TABLE vhffs_pgsql ADD CONSTRAINT vhffs_pgsql_unique_dbname UNIQUE (dbname);
ALTER TABLE vhffs_pgsql ADD CONSTRAINT vhffs_pgsql_unique_dbuser UNIQUE (dbuser);
ALTER TABLE vhffs_repository ADD CONSTRAINT vhffs_repository_unique_name UNIQUE (name);
-ALTER TABLE vhffs_svn ADD CONSTRAINT vhffs_svn_unique_reponame UNIQUE (reponame);
+ALTER TABLE vhffs_svn ADD CONSTRAINT vhffs_svn_unique_reponame_per_owner UNIQUE (owner_gid, reponame);
ALTER TABLE vhffs_dns ADD CONSTRAINT vhffs_dns_unique_domain UNIQUE (domain);
ALTER TABLE vhffs_ml ADD CONSTRAINT vhffs_ml_unique_address UNIQUE (local_part, domain);
ALTER TABLE vhffs_ml_subscribers ADD CONSTRAINT vhffs_ml_subscribers_member_list UNIQUE (ml_id, member);
Modified: trunk/vhffs-doc/src/fr/part1/mirror.xml
===================================================================
--- trunk/vhffs-doc/src/fr/part1/mirror.xml 2007-07-10 10:09:43 UTC (rev 704)
+++ trunk/vhffs-doc/src/fr/part1/mirror.xml 2007-07-10 14:02:36 UTC (rev 705)
@@ -192,7 +192,7 @@
<section>
<title>Introduction</title>
- <para>Le troisième service à disposer de script de
+ <para>Le troisième service à disposer de scripts de
réplication livrés avec VHFFS est le serveur mail Exim.
Deux scripts sont fournis, le premier permet une réplication sur
le serveur mail primaire (appelé mx1 dans la suite du document),