Here is a simple classic ASP FormMail script, it is just an example on how to use JMail for a basic formail type function, while this version obviously requires Jmail, it can be very easily modified to use any mail component. It has some security features such as only allowing a certain number of recipiants, email domain must exist in an array list and can force certain fields as being required.

I did start this a long time ago, but it should still be some help to somebody! It is based on a stock install of Centos 4 Server.

At the end you should have a DSPAM appliance server that synchronises email addresses with H-Sphere and allows the postmaster the access the webbased DSPAM console.cess the webbased DSPAM console.

After installing the OS

Make sure the OS is up to date...

# rpm --import htshutdown -r now # yum install gcc gcc-c++ perl-DBI pcre-devel zlib-devel openssl-devel db4-devel

Setup some users...

# groupadd -# groupadd -g 2000 postfix
# groupadd -g 2002 dspam
# groupadd -g 2003 apache
# groupadd -g 3000 postdrop
# useradd -u 2000 -g 2000 -d /var/empty -c "Postfix Server" -s /sbin/nologin postfix
# useradd -u 2002 -g 2002 -d /var/empty -c "DSPAM Server" -s /sbin/nologin -G postdrop dspam
# useradd -u 2003 -g 2003 -d /var/empty -c "Apache Server" -s /sbin/nologin -G postdrop apache

Setup MySQL

Get the following from your favorite mirror...

• MySQL-devel-standard-5.0.27-0.rhel4.i386.rpm
• MySQL-shared-standard-5.0.27-0.rhel4.i386.rpm
• MySQL-server-standard-5.0.27-0.rhel4.i386.rpm

Note : These were what I used at the time - you may want more recent releases

# rpm -iv MySQL-devel-standard-5.0.27-0.rhel4.i386.rpm
# rpm -iv MySQL-shared-standard-5.0.27-0.rhel4.i386.rpm
# rpm -iv MySQL-server-standard-5.0.27-0.rhel4.i386.rpm
# rpm -iv MySQL-client-standard-5.0.27-0.rhel4.i386.rpm

# /usr/bin/mysqladmin -u root password 'yourPassword'
# /usr/bin/mysqladmin -u root -h localhost password 'yourPassword' -p
# /usr/bin/mysqladmin -u root -h yourControlPanel password 'yourPassword' -p

Note : replace yourPassword with anything you want to use for the MySQL root user and replace yourControlPanel with the IP address of your control panel server that will access this MySQL server

Now create the dspam database we will need later

# mysql -e "create database dspam" -p
# mysql -e "grant all on dspam.* to [email protected] identified by 'DSPAMSQLPASS'" -p
# mysql -e "grant all on dspam.* to [email protected] identified by 'DSPAMSQLPASS'" -p
# mysql -e "grant all on dspam.* to [email protected] identified by 'anotherPasswordOfYours'" -p
# mysql
> use dspam
> CREATE TABLE tmp_htpasswd_user_info ( domain varchar(255) NOT NULL default '', pass varchar(255) NOT NULL default '', PRIMARY KEY (domain) ) TYPE=MyISAM PACK_KEYS=1;

> CREATE TABLE htpasswd_host_info ( id int(14) NOT NULL auto_increment, host char(255) NOT NULL default '', host_group int(14) NOT NULL default '0', created timestamp(14) NOT NULL, updated timestamp(14) NOT NULL, PRIMARY KEY (id), KEY host (host) ) TYPE=MyISAM PACK_KEYS=1;

> CREATE TABLE htpasswd_user_group ( id int(14) NOT NULL auto_increment, user_name char(50) NOT NULL default '', user_group char(20) NOT NULL default '', host_group int(14) default NULL, created timestamp(14) NOT NULL, updated timestamp(14) NOT NULL, PRIMARY KEY (id), KEY host_group (host_group), KEY user_group (user_group) ) TYPE=MyISAM PACK_KEYS=1;

> CREATE TABLE htpasswd_user_info ( id int(14) NOT NULL auto_increment, user_name char(30) NOT NULL default '', user_passwd char(20) NOT NULL default '', host_group int(14) NOT NULL default '0', created timestamp(14) NOT NULL, updated timestamp(14) NOT NULL, isadmin tinyint(4) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY user_name (user_name,host_group) ) TYPE=MyISAM PACK_KEYS=1;

> CREATE TABLE `hsphere_mailobjects` ( `mailtype` varchar(20) default NULL, `uid` smallint(6) default NULL, `address` varchar(100) default NULL, `alias` varchar(100) default NULL, KEY `mailtype` (`mailtype`), KEY `uid` (`uid`), KEY `address` (`address`), KEY `alias` (`alias`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

> CREATE TABLE `transport` ( `domain` VARCHAR(100) NOT NULL, `nexthop` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`domain`))TYPE=MyISAM;

> CREATE TABLE `relay_domains` ( `domain` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`domain`))TYPE=MyISAM;

> CREATE TABLE `relay_recipients` ( `username` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`username`))TYPE=MyISAM;

> CREATE TABLE `virtual_mailbox_maps` ( `username` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`username`))TYPE=MyISAM;

> CREATE TABLE `tmp_virtual_mailbox_maps` ( `username` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`username`))TYPE=MyISAM;

> CREATE TABLE `virtual_mailbox_domains` ( `domain` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`domain`))TYPE=MyISAM;

> CREATE TABLE `tmp_virtual_mailbox_domains` ( `domain` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`domain`))TYPE=MyISAM;

> CREATE TABLE `virtual_mailbox_maps` ( `username` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`username`))TYPE=MyISAM;

> CREATE TABLE `tmp_virtual_mailbox_maps` ( `username` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`username`))TYPE=MyISAM;

> CREATE TABLE `virtual_alias_domains` ( `maindomainname` VARCHAR(100) NOT NULL, `aliasdomainname` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`aliasdomainname`))TYPE=MyISAM;

> CREATE TABLE `virtual_alias_maps` ( `mainusername` VARCHAR(100) NOT NULL, `aliasusername` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`aliasusername`))TYPE=MyISAM;

> CREATE TABLE `tmp_virtual_alias_domains` ( `maindomainname` VARCHAR(100) NOT NULL, `aliasdomainname` VARCHAR(100) NOT NULL, `sourceflag` CHAR(1) DEFAULT 'H' COMMENT 'Reserved for future use', PRIMARY KEY (`aliasdomainname`))TYPE=MyISAM;

> CREATE TABLE `dspam_virtual_uids` ( `uid` bigint(20) unsigned NOT NULL, `username` varchar(128) default NULL, PRIMARY KEY `id_virtual_uids_01` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

> CREATE TABLE `tmp_dspam_virtual_uids` ( `uid` bigint(20) unsigned NOT NULL, `username` varchar(128) default NULL, PRIMARY KEY `id_virtual_uids_01` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

> \q

Setup PostFix

# wget ftp://postfix.teleglobe.net/postfix/official/postfix-2.3.6.tar.gz
# service sendmail stop
# rpm -e --nodeps sendmail-8.13.1-3.RHEL4.5
# tar xzvf postfix-2.3.6.tar.gz
# cd postfix-2.3.6
# make -f Makefile.init makefiles \
  'CCARGS=-DHAS_MYSQL -I/usr/include/mysql' \
  'AUXLIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm'
# make && make install

Note : At this point you should be able to accept all of the defaults for the postfix install

# vi /etc/aliases
    # add line at end
    # root: [email protected]
    # and save the file
# /usr/bin/newaliases

# vi /etc/postfix/main.cf
    # myorigin = $mydomain
    # mydestination =
    # mynetworks_style = host # To only trust local machine for relaing mail
    # and save the file

Below it is assumed this is only working for a single mail server, if used for more then add value in to hashtable or use mysql etc.
Replace mail.yourserver.com with your own mail server host name.

# cd /etc/postfix/
# echo "* smtp:[mail.yourserver.com]" >> transport
# postmap transport

# cd /usr/local/src
# wget http://policyd.sourceforge.net/policyd-v1.80.tar.gz
# tar xzvf policyd-v1.80.tar.gz
# cd policyd-v1.80 # wget http://policyd.sourceforge.net/policyd-v1.80.tar.gz
# tar xzvf policyd-v1.80.tar.gz
# cd policyd-v1.80

Prepare some required files and greylisting

# wget http://switch.dl.sourceforge.net/sourceforge/libdbi/libdbi-0.8.1.tar.gz
# tar xzvf libdbi-0.8.1.tar.gz
# cd libdbi-0.8.1
# ./configure
# make
# make check
# make install
# cd ..

# wget http://heanet.dl.sourceforge.net/sourceforge/libdbi-drivers/libdbi-drivers-0.8.1a.tar.gz
# tar xzvf libdbi-drivers-0.8.1a.tar.gz
# cd libdbi-drivers-0.8.1
# ./configure
# make
# make check
# make install
# cd ..

# wget wget http://mimo.gn.apc.org/gps/gps-1.004.tar.gz
# tar xzvf gps-1.004.tar.gz
# cd gps-1.004
# ./configure
# make
# make install

# wget http://www.gasmi.net/down/gld-1.7.tgz
# tar xzvf gld-1.7.tgz
# cd gld-1.7
# ./configure
# make install
# mysql -e "create database gps" -p
# mysql -e "grant all on greylist.* to [email protected] identified by 'DSPAMSQLPASS'" -p
# mysql gld -p < tables.mysql

# vi /etc/gld.conf.sample
    Set mysql details at bottom as required and save file
# mv /etc/gld.conf.sample /etc/gld.conf

# vi /etc/postfix/main.cf
    add the following line to the end of your smtpd_recipient_restrictions statement and save the file...
    check_policy_service inet:127.0.0.1:2525

Create the files as below

/etc/postfix/relay_domains.cf
user = dspam
password = DSPAMSQLPASS
dbname = dspam
query = SELECT `domain` FROM `relay_domains` WHERE `domain`='%s'
table = relay_domains
host = 127.0.0.1
select_field = domain
where_field = domain
additional_conditions =

/etc/postfix/relay_recipients.cf
user = dspam
password = DSPAMSQLPASS
dbname = dspam
query = SELECT `username` FROM `relay_recipients` WHERE `username`='%s'
table = relay_recipients
host = 127.0.0.1
select_field = username
where_field = username
additional_conditions =

/etc/postfix/virtual_mailbox_maps.cf
user = dspam
password = DSPAMSQLPASS
dbname = dspam
query = SELECT `username` FROM `virtual_mailbox_maps` WHERE `username`='%s'
table = virtual_mailbox_maps
host = 127.0.0.1
select_field = username
where_field = username
additional_conditions =

/etc/postfix/virtual_alias_domains.cf
user = dspam
password = DSPAMSQLPASS
dbname = dspam
query = SELECT `domain` FROM `virtual_mailbox_domains` WHERE `domain`='%s'
table = virtual_mailbox_domains
host = 127.0.0.1
select_field = domain
where_field = domain
additional_conditions =

/etc/postfix/virtual_mailbox_domains.cf
user = dspam
password = DSPAMSQLPASS
dbname = dspam
query = SELECT `domainname` FROM `virtual_alias_domains` WHERE `aliasdomainname`='%s'
table = virtual_alias_domains
host = 127.0.0.1
select_field = maindomainname
where_field = aliasdomainname
additional_conditions =

Setup Apache

Get httpd-2.2.4.tar.gz from your favorite mirror - this was the version at the time of originally doing this

# cd /usr/local/src/
# wget fromYourFavoriteMirror/httpd-2.2.4.tar.gz
# tar xzvf httpd-2.2.4.tar.gz
# cd httpd-2.2.4
# ./configure \
  --enable-rewrite \
  --enable-cgi \
  --disable-userdir \
  --enable-suexec \
  --with-suexec-caller=apache \
  --with-suexec-docroot=/var/www \
  --with-suexec-uidmin=1000 \
  --with-suexec-gidmin=1000 \
  --enable-ssl \
  --enable-so
# make
# make install
# /usr/local/apache2/bin/apachectl -k start
    make sure "It works!"
# /usr/local/apache2/bin/apachectl -k stop

# wget http://www.heuer.org/mod_auth_mysql/mod_auth_mysql.c
# /usr/local/apache2/bin/apxs -cia -lmysqlclient mod_auth_mysql.c

# /usr/local/apache2/bin/apachectl -k start; tail -f /usr/local/apache2/logs/error_log
    make sure all looks good
# /usr/local/apache2/bin/apachectl -k stop

# mkdir -p /var/www/etc/
# chown apache.dspam /var/www/etc/
# /usr/local/apache2/bin/htpasswd -c /var/www/etc/htpasswd root

# mkdir /usr/local/apache2/conf/ssl.key
# mkdir /usr/local/apache2/conf/ssl.crt
# openssl genrsa 1024 > /usr/local/apache2/conf/ssl.key/dspam.key
# chmod 400 /usr/local/apache2/conf/ssl.key/dspam.key
# openssl req -new -x509 -nodes -sha1 -days 365 \
  -key /usr/local/apache2/conf/ssl.key/dspam.key \
  > /usr/local/apache2/conf/ssl.crt/dspam.crt
# openssl req -new -key /usr/local/apache2/conf/ssl.key/dspam.key -out /usr/local/apache2/conf/ssl.key/dspam.csr

# vi /usr/local/apache2/conf/httpd.conf

Edit line "Listen 80" and replace with "Listen 443" and make sure the end looks like this (but replace yourdomain.com with your own dspam server name )...

User apache
Group apache

AddHandler cgi-script .cgi

<VirtualHost *:443>
    DocumentRoot "/var/www/htdocs/yourdomain.com"
    ServerName yourdomain.com
    ServerAdmin [email protected]
    ErrorLog /usr/local/apache2/logs/yourdomain.com-error_log
    TransferLog /usr/local/apache2/logs/yourdomain.com-access_log
    SSLEngine on
    SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP:+eNULL
    SSLCertificateFile /usr/local/apache2/conf/ssl.crt/dspam.crt
    SSLCertificateKeyFile /usr/local/apache2/conf/ssl.key/dspam.key
    RewriteEngine on
    RewriteRule ^/$ /dspam.cgi [R]
    SuexecUserGroup dspam dspam
    <Directory "/var/www/htdocs/yourdomain.com">
        DirectoryIndex dspam.cgi
        Options FollowSymLinks ExecCGI
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from all
        SSLRequireSSL

        AuthBasicAuthoritative Off

        AuthType Basic

        AuthMySQLHost localhost
        AuthMySQLUser htpasswd
        AuthMySQLPassword anotherPasswordOfYours
        AuthMySQLDB dspam

        AuthMySQLUserTable htpasswd_user_info
        AuthMySQLHostTable htpasswd_host_info
        AuthMySQLGroupTable htpasswd_user_group

        AuthMySQLAuthoritative On
        AuthMySQLKeepAlive Off
        AuthMySQLEnable On
        AuthMySQLVirtualHost Off

        AuthName "Authentication required"
        require valid-user
    </Directory>
</VirtualHost>
    

Setup DSPAM

# cd /usr/local/src/
# wget http://dspam.nuclearelephant.com/sources/dspam-3.6.8.tar.gz
# tar xzvf dspam-3.6.8.tar.gz
# cd dspam-3.6.8

# ./configure \
  --with-dspam-home=/var/dspam \
  --with-dspam-home-mode=770 \
  --with-dspam-home-owner=dspam \
  --with-dspam-home-group=postdrop \
  --with-dspam-mode=2510 \
  --with-dspam-owner=dspam \
  --with-dspam-group=postfix \
  --with-delivery-agent=/usr/sbin/sendmail \
  --with-storage-driver=mysql_drv \
  --with-mysql-includes=/usr/include/mysql \
  --with-mysql-libraries=/usr/lib/mysql \
  --enable-domain-scale \
  --enable-preferences-extension \
  --enable-virtual-users \
  --enable-daemon \
  --enable-debug
# make && make install
# mkdir -p /usr/local/share/dspam/
# DOMAINNAME="yourdomainname.com"
# mkdir -p /var/www/htdocs/$DOMAINNAME
# chmod 555 /var/www/htdocs/$DOMAINNAME
# chown dspam.dspam /var/www/htdocs/$DOMAINNAME
# cd /var/www/htdocs/$DOMAINNAME
# cp -r /usr/local/src/dspam-3.6.8/webui/cgi-bin/* .
# cp /usr/local/src/dspam-3.6.8/webui/htdocs/* .
# rm -f Makefile* templates/Makefile*
# chown -R dspam.dspam *
# chmod 444 *.*
# chmod 554 *.cgi
# chmod 555 templates
# chmod 444 templates/*

# vi /usr/local/etc/dspam.conf
    add the following lines and settings and save file...
 Trust dspam
 Trust apache
 Trust postfix
 AllowOverride localStore
 AllowOverride fallbackDomain
 FallbackDomains on
 MySQLUIDInSignature on
 MySQLServer /tmp/mysql.sock
 MySQLPort 3306
 MySQLUser dspam
 MySQLPass DSPAMSQLPASS
 MySQLDb dspam
 MySQLCompress true

# dspam_admin ch pref default trainingMode TEFT
# dspam_admin ch pref default spamAction quarantine
# dspam_admin ch pref default spamSubject "[SPAM]"
# dspam_admin ch pref default enableWhitelist on
# dspam_admin ch pref default statisticalSedation 3
# dspam_admin ch pref default signatureLocation message
# dspam_admin ch pref default showFactors off
# dspam_admin ch pref default enableWhitelist on
# dspam_admin ch pref default enableBNR on
# dspam_admin ch pref default showFactors off

# vi /var/www/htdocs/$DOMAINNAME/configure.pl
    update $CONFIG{'LOCAL_DOMAIN'} to your own domain

# vi /var/www/htdocs/$DOMAINNAME/templates/nav_performance.html
 - <strong>[email protected]</strong>
 + <strong>spam-$REMOTE_USER$</strong>

# cd /usr/local/src/dspam-3.6.8/src/tools.mysql_drv
# mysql dspam -p < mysql_objects-4.1.sql
# mysql dspam -p < virtual_user_aliases.sql
# cp purge-4.1.sql /usr/local/share/dspam/

\\ Now configure postfix to filter with dspam
# cd /etc/postfix/
# vi master.cf
 \ Add after first "smtp" line
     -o content_filter=dspam:
     dspam unix - n n - 10 pipe
         flags=Rhqu user=dspam argv=/usr/local/bin/dspam --deliver=innocent --user ${recipient} -i -f ${sender} -- ${recipient}

# yum install gd gd-devel

Other bits

Make sure everything starts in correct order

# vi /etc/rc.local
    # start mysql
    /usr/share/mysql/mysql.server start
    # Start GLD - must start before postfix
    /usr/local/bin/gld
    # start postfix
    /usr/sbin/postfix start

Setup H-Sphere Sync

Coming soonSphere Sync

#!Setup H-Sphere Sync

Create a new file with the code as below and cron it to run as often as you want H-Sphere email details to sync with the DSPAM server - note this SQL can take around 1 minutes to run for around 10,000 email addresses

You need to make sure you replace DSPAMPASS andf MYSQLSERVERHOST as needed.

Once the data is sync'd you will be able to log on the the DSPAM web interface using the details @domainname.com as the user along with the postmaster password for domainname.com - this will manage all of the accounts under that domain.

#!/bin/sh

ALIAS_FORWARDERS_TO_POSTMASTER=1_USER=`grep ^DB_USER $HSP|awk '{print $3}'`
export PGPASSWORD=`grep ^DB_PASSWORD $HSP|awk '{print $3}'`
DB_NAME=hsphere
DEBUGOUTPUT=1

MYSQLUSERNAME=dspam
MYSQLPASSWORD=DSPAMPASS
MYSQLHOST=MYSQLSERVERHOST
MYSQLDBNAME=dspam

pgtmp='mailoutput.tmp';


dispmsg()
{
if [ $DEBUGOUTPUT -eq 1 ]; then
echo "$1"
fi
}

executemysql()
{
dispmsg "MYSQL:$1"
mysql --user=$MYSQLUSERNAME --password=$MYSQLPASSWORD --host=$MYSQLHOST --database=$MYSQLDBNAME --e "$1"
}

executepgsql()
{
dispmsg "PGSQL:$1"
psql -q -t -A -U $DB_USER $DB_NAME -c "$1" > $pgtmp
}


# dspam_virtual_uids : select domain.id and all maildomains (includes aliased mail domains too), This should be used for dspam_virtual_uids

executepgsql "select id, '@'||CASE WHEN domainname IS NULL THEN name ELSE domainname END from ( select d.id, d.name, mda2.domain_alias as domainname from domains d join ( select account_id, parent_id, child_id from parent_child where parent_type IN (2,31,34,35,37,3515,6400) and child_type=6400 ) da on d.id=da.parent_id join ( select parent_id, child_id from parent_child where child_type=1009 ) mda on da.child_id=mda.parent_id join mail_domain_aliases mda2 on mda.child_id=mda2.id union select d.id, d.name, null as domainname from domains d join ( select account_id, parent_id, child_id from parent_child where parent_type IN (2,31,34,35,37,3515,6400) and child_type=1000 ) da on d.id=da.parent_id join ( select parent_id, child_id from parent_child where child_type=1001 ) mda on da.child_id=mda.parent_id join mail_domain mda2 on mda.child_id=mda2.id) as answer";

executemysql "DELETE FROM tmp_dspam_virtual_uids;"

executemysql "LOAD DATA LOCAL INFILE '$pgtmp' INTO TABLE tmp_dspam_virtual_uids FIELDS TERMINATED BY '|'"

# Add any missing mail domains (Use REPLACE to be sure IDs are correct - this is becase aliases can changes etc)
executemysql "REPLACE INTO dspam_virtual_uids SELECT * FROM tmp_dspam_virtual_uids"


# Add fallbackDomain to all dspam_preferences where it doesn't exist
# Shouldn't need this since we have this on in default
# executemysql "INSERT IGNORE INTO dspam_preferences SELECT uid, 'fallbackDomain', 'on' FROM tmp_dspam_virtual_uids"

# Add localStore = UID to all dspam_preferences where it doesn't exist
executemysql "INSERT IGNORE INTO dspam_preferences SELECT uid, 'localStore', uid FROM tmp_dspam_virtual_uids"

# Delete old domains, we will assume that all H-Sphere UIDs are less than 5000000 (as they are domain ids) then we could use ID's higher than 5000000 for non h-sphere accounts ifneeded
executemysql "DELETE dspam_virtual_uids d FROM dspam_virtual_uids d WHERE d.uid BETWEEN 100 AND 5000000 AND d.uid NOT IN ( SELECT t.uid FROM tmp_dspam_virtual_uids t );"


# virtual_alias_domains : Mail aliases used to create postfix virtual_alias_domains

executepgsql "select d.name, mda2.domain_alias as domainname, 'H' from domains d join ( select account_id, parent_id, child_id from parent_child where parent_type=2 and child_type=6400 ) da on d.id=da.parent_id join ( select parent_id, child_id from parent_child where child_type=1009 ) mda on da.child_id=mda.parent_id join mail_domain_aliases mda2 on mda.child_id=mda2.id"

executemysql "DELETE FROM tmp_virtual_alias_domains;"

executemysql "LOAD DATA LOCAL INFILE '$pgtmp' INTO TABLE tmp_virtual_alias_domains FIELDS TERMINATED BY '|'"

# Add any missing mail aliases
executemysql "REPLACE INTO virtual_alias_domains SELECT * FROM tmp_virtual_alias_domains"

# Delete any old mail aliases (where source is H (for H-Sphere))
executemysql "DELETE virtual_alias_domains v FROM virtual_alias_domains v WHERE sourceflag='H' AND NOT EXISTS ( SELECT 1 FROM tmp_virtual_alias_domains t WHERE v.aliasdomainname = t.aliasdomainname)"


# virtual_mailbox_maps : select all email address on h-sphere (includes @domain.com for catchalls)
executepgsql "select distinct mo.full_email, 'dummy', 'H' from mailobject mo join parent_child pc on mo.id = pc.child_id union select '@'||dm.name as catchalls, 'dummy', 'H' from parent_child as pc join mail_services as ms on pc.child_id=ms.id join parent_child as pc2 on pc.child_id=pc2.parent_id join mail_domain as md on md.id=pc2.child_id join domains as dm on dm.id=pc.parent_id where pc2.child_type=1001 and md.catch_all!=''"

executemysql "DELETE FROM tmp_virtual_mailbox_maps;"

executemysql "LOAD DATA LOCAL INFILE '$pgtmp' INTO TABLE tmp_virtual_mailbox_maps FIELDS TERMINATED BY '|'"

# Add any missing mail aliases
executemysql "REPLACE INTO virtual_mailbox_maps SELECT * FROM tmp_virtual_mailbox_maps"

# Delete any old mail aliases (where source is H (for H-Sphere))
executemysql "DELETE virtual_mailbox_maps v FROM virtual_mailbox_maps v WHERE sourceflag='H' AND NOT EXISTS ( SELECT 1 FROM tmp_virtual_mailbox_maps t WHERE v.username = t.username)"


executemysql "DELETE FROM tmp_virtual_mailbox_domains;"
#
executemysql "INSERT INTO tmp_virtual_mailbox_domains SELECT DISTINCT REVERSE(LEFT(REVERSE(username),LOCATE('@',REVERSE(username)) - 1)), 'H' FROM tmp_virtual_mailbox_maps"

# Add any missing mail aliases
executemysql "INSERT IGNORE INTO virtual_mailbox_domains SELECT * FROM tmp_virtual_mailbox_domains"

# Delete any old mail aliases (where source is H (for H-Sphere))
executemysql "DELETE virtual_mailbox_domains v FROM virtual_mailbox_domains v WHERE sourceflag='H' AND NOT EXISTS ( SELECT 1 FROM tmp_virtual_mailbox_domains t WHERE v.domain = t.domain)"

# Make sure new virtual aliases email addresses exist
executemysql "REPLACE INTO virtual_alias_maps SELECT username, REPLACE(username, maindomainname, aliasdomainname), 'H' FROM tmp_virtual_alias_domains t JOIN tmp_virtual_mailbox_maps m on REVERSE(LEFT(REVERSE(username),LOCATE('@',REVERSE(username)) - 1)) = t.maindomainname"

# Delete old virtual alias maps

# Make sure FallbackDomain is setup on all users
executemysql "REPLACE INTO dspam_preferences select uid, 'FallbackDomain', 'on' from dspam_virtual_uids where uid > 999"

# Update the htpasswd table
executepgsql "select '@'||d.name, md.password from mail_domain md join parent_child pcmd on md.id=pcmd.child_id join parent_child pc2 on pcmd.parent_id = pc2.child_id join domains d on d.id=pc2.parent_id"

executemysql "DELETE FROM tmp_htpasswd_user_info;"

executemysql "LOAD DATA LOCAL INFILE '$pgtmp' INTO TABLE tmp_htpasswd_user_info FIELDS TERMINATED BY '|'"

executemysql "REPLACE INTO htpasswd_user_info (user_name, user_passwd, host_group) SELECT domain, encrypt(pass), 1 FROM tmp_htpasswd_user_info;"

executemysql "DELETE htpasswd_user_info h FROM htpasswd_user_info h WHERE NOT EXISTS (SELECT 1 FROM tmp_htpasswd_user_info t WHERE t.domain=h.user_name )"

executemysql "DELETE FROM tmp_htpasswd_user_info;"

# Add user accounts for aliased domains (if you want)
executemysql "REPLACE INTO htpasswd_user_info (user_name, user_passwd, host_group) SELECT CONCAT('@',t.aliasdomainname), h.user_passwd, 1 FROM tmp_virtual_alias_domains t JOIN htpasswd_user_info h ON h.user_name=CONCAT('@',t.maindomainname)"

# Delete any mail addresses no longer setup...
# Could do with being optimized, may also consider running at longer periods so all tken data etc is not deleted

#delete from dspam_signature_data where NOT EXISTS ()

#delete from dspam_stats where NOT EXISTS ()

#delete from dspam_token_data where NOT EXISTS ()

#delete from dspam_virtual_uids where NOT EXISTS ()

rm -f $pgtmp