[vhffs-dev] [1332] updated mx1-mirror to the new database schema ( yes

[vhffs-dev] [1332] updated mx1-mirror to the new database schema ( yes it was non-working for more than 2 years ;-)

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

To: vhffs-dev@xxxxxxxxx
Subject: [vhffs-dev] [1332] updated mx1-mirror to the new database schema ( yes it was non-working for more than 2 years ;-)
From: subversion@xxxxxxxxxxxxx
Date: Sun, 01 Feb 2009 03:27:15 +0100

Revision: 1332
Author:   gradator
Date:     2009-02-01 03:27:14 +0100 (Sun, 01 Feb 2009)

Log Message:
-----------
updated mx1-mirror to the new database schema (yes it was non-working for more than 2 years ;-)

Modified Paths:
--------------
    trunk/vhffs-backend/src/mirror/mx1-mirror.pl
    trunk/vhffs-backend/src/mirror/mx1-mirror.sql

Modified: trunk/vhffs-backend/src/mirror/mx1-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.pl	2009-01-29 23:58:43 UTC (rev 1331)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.pl	2009-02-01 02:27:14 UTC (rev 1332)
@@ -42,15 +42,15 @@
 # Master DB params
 my $MASTER_DB_HOST = 'localhost';
 my $MASTER_DB_PORT = 5432;
-my $MASTER_DB_NAME = 'vhffs_auth';
+my $MASTER_DB_NAME = 'vhffs';
 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_NAME = 'mailmirror';
+my $SLAVE_DB_USER = 'mailmirror';
 my $SLAVE_DB_PASS = 'vhffs';
 
 # We've to connect to the master DB, fetch
@@ -90,22 +90,23 @@
 
 # Replicate vhffs_object table
 
-# Type 61 is ml objects.
+# Type 60 is mail objects and 61 is ml objects.
 my $msth = $master_dbh->prepare(q{SELECT o.object_id, o.owner_uid,
-    o.date_creation, o.state, o.description
+    o.owner_gid, o.date_creation, o.state, o.description, o.refuse_reason, o.type
     FROM vhffs_object o
-    WHERE o.state = 6 AND o.type = 61})
+    WHERE o.state = 6 AND (o.type = 60 OR 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(?, ?, ?, ?, ?)}) 
+    owner_uid, owner_gid, date_creation, state, description, refuse_reason, type) 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})
+    $ssth->execute($row->{object_id}, $row->{owner_uid}, $row->{owner_gid},
+    $row->{date_creation}, $row->{state}, $row->{description}, $row->{refuse_reason},
+    $row->{type} )
     or die('Unable to insert object #'.$row->{object_id}."\n");
 }
 
@@ -114,14 +115,12 @@
 
 # Replicate vhffs_mxdomain table
 my $msth = $master_dbh->prepare(q{SELECT d.mxdomain_id, d.domain,
-    d.boxes_path, 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
+    d.boxes_path, d.catchall, 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, boxes_path, catchall, owner_uid, owner_gid,
-    object_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)}) 
+    domain, boxes_path, catchall, object_id) VALUES(?, ?, ?, ?, ?)}) 
     or die("Unable to prepare INSERT query for tmp_mxdomain\n");
 
 $msth->execute()
@@ -129,8 +128,7 @@
 
 while(my $row = $msth->fetchrow_hashref()) {
     $ssth->execute($row->{mxdomain_id}, $row->{domain},
-    $row->{boxes_path}, $row->{catchall},
-    $row->{owner_uid}, $row->{owner_gid}, $row->{object_id})
+    $row->{boxes_path}, $row->{catchall}, $row->{object_id})
     or die('Unable to insert mxdomain #'.$row->{id}."\n");
 }
 
@@ -140,15 +138,16 @@
 # 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 
+    b.mbox_name, b.password, b.nospam, b.novirus, b.allowpop, b.allowimap,
+    b.state 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(?, ?, ?, ?, ?, ?, ?, ?)})
+    domain_hash, mbox_name, password, nospam, novirus, allowpop, allowimap,
+    state)
+    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})
     or die("Unable to prepare INSERT query for tmp_boxes\n");
 
 $msth->execute()
@@ -156,8 +155,8 @@
 
 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})
+    $row->{mbox_name}, $row->{password}, $row->{nospam}, $row->{novirus},
+    $row->{allowpop}, $row->{allowimap}, $row->{state} )
             or die('Unable to insert box '.$row->{local_part}.'@'.
                 $row->{domain}."\n");
 }
@@ -194,16 +193,14 @@
 # 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
+    ml.prefix, ml.object_id, ml.sub_ctrl, ml.post_ctrl, ml.reply_to, ml.open_archive,
+    ml.signature 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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
-    ?, ?)})
+    prefix, object_id, sub_ctrl, post_ctrl, reply_to, open_archive, 
+    signature) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})
     or die("Unable to prepare INSERT query for tmp_ml\n");
 
 $msth->execute()
@@ -211,9 +208,8 @@
 
 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})
+    $row->{prefix}, $row->{object_id}, $row->{sub_ctrl}, $row->{post_ctrl},
+    $row->{reply_to}, $row->{open_archive}, $row->{signature})
             or die('Unable to insert ml '.$row->{local_part}.'@'.
                 $row->{domain}."\n");
 }
@@ -289,8 +285,9 @@
 # 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}))
+($count = $slave_dbh->do(q{UPDATE vhffs_object SET description = tmp.description,
+    refuse_reason = tmp.refuse_reason, owner_uid = tmp.owner_uid, owner_gid = tmp.owner_gid,
+    state = tmp.state 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
@@ -299,8 +296,9 @@
     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
+$slave_dbh->do(q{UPDATE vhffs_boxes SET password = tmp.password,
+    nospam = tmp.nospam, novirus = tmp.novirus, allowpop = tmp.allowpop,
+    allowimap = tmp.allowimap, state = tmp.state
     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");
@@ -316,7 +314,7 @@
     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,
+$slave_dbh->do(q{UPDATE vhffs_ml SET prefix = tmp.prefix,
     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
@@ -326,18 +324,25 @@
 
 # Insert new boxes/forward/ml/domains
 
+($count = $slave_dbh->do(q{INSERT INTO vhffs_object(object_id, owner_uid, owner_gid,
+    date_creation, state, description, refuse_reason, type) SELECT object_id,
+    owner_uid, owner_gid, date_creation, state, description, refuse_reason, type
+    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_mxdomain(mxdomain_id, domain,
-    boxes_path, catchall, owner_uid, owner_gid, object_id)
+    boxes_path, catchall, object_id)
     SELECT mxdomain_id, domain, boxes_path, catchall,
-    owner_uid, owner_gid, object_id FROM tmp_mxdomain 
+    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 
+    mbox_name, password, nospam, novirus, allowpop, allowimap, state)
+    SELECT domain, local_part, domain_hash, mbox_name, password, nospam, novirus,
+    allowpop, allowimap, state 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");
@@ -351,19 +356,10 @@
     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
+    object_id, sub_ctrl, post_ctrl, reply_to, open_archive, signature)
+    SELECT ml_id, domain, local_part, prefix, object_id, 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";
@@ -376,3 +372,4 @@
 print "$count subscribers inserted\n";
 $slave_dbh->commit();
 $slave_dbh->disconnect();
+

Modified: trunk/vhffs-backend/src/mirror/mx1-mirror.sql
===================================================================
--- trunk/vhffs-backend/src/mirror/mx1-mirror.sql	2009-01-29 23:58:43 UTC (rev 1331)
+++ trunk/vhffs-backend/src/mirror/mx1-mirror.sql	2009-02-01 02:27:14 UTC (rev 1332)
@@ -4,8 +4,6 @@
     domain varchar,
     boxes_path varchar 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;
@@ -15,11 +13,13 @@
     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,
+    allowpop boolean NOT NULL,
+    allowimap boolean NOT NULL,
+    state int4 NOT NULL,
     CONSTRAINT vhffs_boxes_pkey PRIMARY KEY (domain,local_part)
 ) WITH OIDS;
 
@@ -38,10 +38,7 @@
     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,
@@ -66,21 +63,37 @@
 (
     object_id integer,
     owner_uid int4,
-    date_creation timestamp,
+    owner_gid int4,
+    date_creation int8,
     state int4 NOT NULL,
     description TEXT,
-    type int4 DEFAULT 0,
+    refuse_reason TEXT,
+    type int4 NOT NULL 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);
+CREATE INDEX idx_vhffs_object_state ON vhffs_object(state);
+CREATE INDEX idx_vhffs_boxes_state ON vhffs_boxes(state);
+CREATE INDEX idx_vhffs_ml_open_archive ON vhffs_ml(open_archive);
+CREATE INDEX idx_vhffs_object_owner_uid ON vhffs_object(owner_uid);
+CREATE INDEX idx_vhffs_object_owner_gid ON vhffs_object(owner_gid);
+CREATE INDEX idx_vhffs_object_type ON vhffs_object(type);
+CREATE INDEX idx_vhffs_ml_object_id ON vhffs_ml(object_id);
+CREATE INDEX idx_vhffs_mxdomain_object_id ON vhffs_mxdomain(object_id);
+CREATE INDEX idx_vhffs_object_date_creation ON vhffs_object(date_creation);
 
 -- Foreign key constraints
+ALTER TABLE vhffs_boxes ADD CONSTRAINT fk_vhffs_boxes_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
+ALTER TABLE vhffs_forward ADD CONSTRAINT fk_vhffs_forward_vhffs_mxdomain FOREIGN KEY (domain) REFERENCES vhffs_mxdomain(domain) ON DELETE CASCADE;
 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;
+ALTER TABLE vhffs_mxdomain ADD CONSTRAINT fk_vhffs_mxdomain_vhffs_object FOREIGN KEY (object_id) REFERENCES vhffs_object(object_id) ON DELETE CASCADE;
 
 -- unique constraints (implicitely create indices)
 ALTER TABLE vhffs_mxdomain ADD CONSTRAINT vhffs_mxdomain_unique_domainname 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);

Messages sorted by: [ date | thread ]
Next by Date: [vhffs-dev] [1333] Added new all-groups part (yes last commit was a long time ago but I 'm not dead)
Next by thread: [vhffs-dev] [1333] Added new all-groups part (yes last commit was a long time ago but I 'm not dead)

Mail converted by MHonArc 2.6.19+

http://listengine.tuxfamily.org/