User Tools

Site Tools


Account migration from legacy mail system

Legacy account database format

CREATE TABLE `boxes` (
  `username` char(60) NOT NULL,
  `domain` char(60) NOT NULL,
  `pass` char(255) NOT NULL,
  `comments` char(255) NOT NULL,
  `which_server` enum('KGD','MSK') NOT NULL DEFAULT 'KGD',
  `is_active` enum('Y','N') NOT NULL DEFAULT 'Y',
  `permit_web` enum('NO','USER','ADMIN') NOT NULL DEFAULT 'NO' COMMENT 'NO = no web access at all, USER = webmail only, ADMIN = webmail and web control',
  `Is_IMAP` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'Should IMAP be enabled for  him?',
  `is_never_autoclean` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'Will autoclean script clean old mails in cur dir of box',
  `belong_to_hq` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'Info for backup purposes, Y = store forever, N = store short time',
  `force_ssl` enum('N','Y') NOT NULL DEFAULT 'N' COMMENT 'If YES, then no uncrypted session allowed',
  `imap_really` enum('Y','N','YES','NO') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`username`,`domain`),
  KEY `is_active` (`is_active`),
  KEY `which_server` (`which_server`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `aliases` (
  `username` char(60) NOT NULL,
  `domain` char(60) NOT NULL,
  `sendto` char(70) NOT NULL,
  `copy_self` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'Y = copy to self and remote box, N = redirect only to remote',
  `comment` char(255) NOT NULL,
  `is_active` enum('Y','N') NOT NULL DEFAULT 'Y' COMMENT 'Y = redirect is active, N = just like it is not exists at all',
  `which_server` enum('KGD','MSK') NOT NULL DEFAULT 'KGD' COMMENT 'which server will do redirect',
  `is_maillist` enum('Y','N') NOT NULL DEFAULT 'N',
  `is_alias_from_personal` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`username`,`domain`,`sendto`),
  KEY `which_server` (`which_server`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Vimbadmin account database format

CREATE TABLE alias (
    id bigint NOT NULL,
    address character varying(255) NOT NULL,
    goto text NOT NULL,
    active boolean DEFAULT true NOT NULL,
    created timestamp(0) without time zone NOT NULL,
    modified timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    domain_id bigint
);
 
 
CREATE TABLE domain (
    id bigint NOT NULL,
    domain character varying(255) NOT NULL,
    description character varying(255) DEFAULT NULL::character varying,
    max_aliases integer DEFAULT 0 NOT NULL,
    alias_count bigint DEFAULT 0 NOT NULL,
    max_mailboxes integer DEFAULT 0 NOT NULL,
    mailbox_count bigint DEFAULT 0 NOT NULL,
    max_quota bigint DEFAULT 0 NOT NULL,
    quota bigint DEFAULT 0 NOT NULL,
    transport character varying(255) DEFAULT 'virtual'::character varying NOT NULL,
    backupmx boolean DEFAULT false NOT NULL,
    active boolean DEFAULT true NOT NULL,
    homedir character varying(255) DEFAULT NULL::character varying,
    maildir character varying(255) DEFAULT NULL::character varying,
    uid integer,
    gid integer,
    created timestamp(0) without time zone NOT NULL,
    modified timestamp(0) without time zone DEFAULT NULL::timestamp without time zone
);
 
 
CREATE TABLE mailbox (
    id bigint NOT NULL,
    username character varying(255) NOT NULL,
    password character varying(255) NOT NULL,
    name character varying(255) DEFAULT NULL::character varying,
    alt_email character varying(255) DEFAULT NULL::character varying,
    quota bigint DEFAULT 0 NOT NULL,
    local_part character varying(255) NOT NULL,
    active boolean DEFAULT false NOT NULL,
    access_restriction character varying(100) DEFAULT 'ALL'::character varying NOT NULL,
    homedir character varying(255) DEFAULT NULL::character varying,
    maildir character varying(255) DEFAULT NULL::character varying,
    uid bigint,
    gid bigint,
    homedir_size bigint,
    maildir_size bigint,
    size_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    delete_pending boolean DEFAULT false,
    created timestamp(0) without time zone NOT NULL,
    modified timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    domain_id bigint
);

Rapid scripts for conversation

dbi-mysql-mail2-account.pl

dbi-mysql-mail2-account.pl
#!/usr/local/bin/perl
 
use DBI;
use Data::Dumper;
 
my $dsn = 'DBI:mysql:mail2:localhost';
 
my $db_username = 'root';
my $db_password = '';
 
my $dbh = DBI->connect($dsn, $db_username, $db_password);
 
if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}
 
$query = "select username, domain, trim(pass) as pass, is_active, is_imap from boxes;";
 
$sth = $dbh->prepare($query);
$rv = $sth->execute();
 
if (!defined $rv) {
  print "query error: " . $dbh->errstr . "\n";
  exit(0);
}
 
my $id=100;
while ($ref = $sth->fetchrow_hashref()) {
#  print Dumper($ref);
  my $full_username = $ref->{'username'} . '@' . $ref->{'domain'};
  my $username = $ref->{'username'};
  my $domain = $ref->{'domain'};
  my $password = $ref->{'pass'};
  my $active = $ref->{'is_active'};
  my $imap = $ref->{'is_imap'};
 
  $active =~ s/Y/true/;
  $active =~ s/N/false/;
 
  $imap =~ s/Y/SMTP,IMAP,POP,WEB/;
  $imap =~ s/N/SMTP,POP/;
 
  my $pg_dbname = "mail";
  my $pg_username = "exim";
  my $pg_password = "xxxxxx";
  my $pg_dbhost = "localhost";
  my $pg_dbport = "5432";
 
  my $pg_dbh = DBI->connect("dbi:Pg:dbname=$pg_dbname;host=$pg_dbhost;port=$pg_dbport",
                    "$pg_username","$pg_password", {PrintError => 0});
 
  $pg_query = "select id from domain where domain = '$domain' limit 1;";
  $pg_sth = $pg_dbh->prepare($pg_query);
  $pg_rv = $pg_sth->execute();
 
  $pg_ref = $pg_sth->fetchrow_hashref();
  $domain_id = $pg_ref->{'id'};
 
  $pg_sth->finish();
  $pg_dbh->disconnect();
  print "INSERT INTO mailbox (id, username, password, name, alt_email, quota, local_part, ";
  print " active, access_restriction, homedir, maildir, uid, gid, ";
  print " homedir_size, maildir_size, size_at, delete_pending, created, modified, domain_id) ";
  print " VALUES ($id, '$full_username', '$password', '', '', 0, '$username', $active, '$imap', ";
  print " '/data/vmail/$domain/$username', 'maildir:/data/vmail/$domain/$username:LAYOUT=fs', ";
  print " 1005, 1005, NULL, NULL, NULL, false, '2016-06-23 13:52:39', '2016-06-28 14:37:14', $domain_id);";
  print "\n";
  $id++;
}
 
$sth->finish();
$dbh->disconnect();

dbi-mysql-mail2-alias.pl

dbi-mysql-mail2-alias.pl
#!/usr/local/bin/perl
 
use DBI;
use Data::Dumper;
 
my $dsn = 'DBI:mysql:mail2:localhost';
 
my $db_username = 'root';
my $db_password = '';
 
$dbh = DBI->connect($dsn, $db_username, $db_password);
 
if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}
 
my $query = "select distinct username, domain from aliases group by domain,username;";
 
$sth = $dbh->prepare($query);
$rv = $sth->execute();
 
if (!defined $rv) {
  print "query error: " . $dbh->errstr . "\n";
  exit(0);
}
 
my $id = 100;
 
while ($ref = $sth->fetchrow_hashref()) {
 
    my $username = $ref->{'username'};
    my $domain = $ref->{'domain'};
    my $full_username = $ref->{'username'} . '@' . $ref->{'domain'};
    my $goto = $ref->{'sendto'};
    my $active = $ref->{'is_active'};
    my $internal = $ref->{'is_maillist'};
 
 
 
    # for alias collection
    my $query = "select distinct username, domain, sendto, is_active, is_maillist from aliases where username='" . $username ."' and domain='" . $domain ."';";
    my $sth = $dbh->prepare($query);
    my $rv = $sth->execute();
    if (!defined $rv) {
        print "query error: " . $dbh->errstr . "\n";
        exit(0);
    }
 
    my $count = 0;
    my $goto_all='';
 
    my $active = '';
    my $internal = '';
 
    while ($ref = $sth->fetchrow_hashref()) {
        my $goto = $ref->{'sendto'};
        $goto_all = "$goto_all,$goto";
 
        $active = $ref->{'is_active'};
        $internal = $ref->{'is_maillist'};
 
        $count++;
    }
    $goto_all =~ s/^,//;
 
    $active =~ s/Y/true/;
    $internal =~ s/Y/true/;
    $active =~ s/N/false/;
    $internal =~ s/N/false/;
 
 
    # domain id search
    my $pg_dbname = "mail";
    my $pg_username = "exim";
    my $pg_password = "xxxxxxx";
    my $pg_dbhost = "localhost";
    my $pg_dbport = "5432";
    my $pg_dbh = DBI->connect("dbi:Pg:dbname=$pg_dbname;host=$pg_dbhost;port=$pg_dbport",
                    "$pg_username","$pg_password", {PrintError => 0});
 
    my $pg_query = "select id from domain where domain = '$domain' limit 1;";
    my $pg_sth = $pg_dbh->prepare($pg_query);
    my $pg_rv = $pg_sth->execute();
 
    $pg_ref = $pg_sth->fetchrow_hashref();
    my $domain_id = $pg_ref->{'id'};
    $pg_sth->finish();
    $pg_dbh->disconnect();
 
    print "INSERT INTO alias (id, address, goto, active, internal, created, modified, domain_id)\n";
    print "  VALUES ($id, '$full_username', '$goto_all', $active, $internal, '2016-06-27 06:41:23', '2016-06-28 13:39:03', $domain_id);\n";
    $id++;
    $sth->finish();
}
 
#INSERT INTO alias (id, address, goto, active, internal, created, modified, domain_id) 
#VALUES (15, '$username', '$goto', true, false, '2016-06-27 06:41:23', '2016-06-28 13:39:03', 20);
 
 
$sth->finish();
$dbh->disconnect();
#EOF
 

dbi-mysql-mail2-domain.pl

dbi-mysql-mail2-domain.pl
#!/usr/local/bin/perl
 
use DBI;
use Data::Dumper;
 
my $dsn = 'DBI:mysql:mail2:localhost';
 
my $db_username = 'root';
my $db_password = '';
 
$dbh = DBI->connect($dsn, $db_username, $db_password);
 
if ($DBI::err != 0) {
  print $DBI::errstr . "\n";
  exit($DBI::err);
}
 
#my $query = "select distinct username, domain, is_active, is_maillist from aliases where is_maillist = 'Y';";
my $query = "select distinct domain from boxes order by domain;";
#my $query = "select distinct domain from aliases order by domain;";
 
$sth = $dbh->prepare($query);
$rv = $sth->execute();
 
if (!defined $rv) {
  print "query error: " . $dbh->errstr . "\n";
  exit(0);
}
 
my $id = 100;
while ($ref = $sth->fetchrow_hashref()) {
#    my $domain = $ref->{'domain'};
    print "$domain\n";
    print "INSERT INTO domain (id, domain, description, max_aliases, alias_count, max_mailboxes, ";
    print "   mailbox_count, max_quota, quota, transport, backupmx, active, homedir, maildir, ";
    print "   uid, gid, created, modified) ";
    print "VALUES ($id, '$domain', '', 0, 0, 0, 0, 0, 0, 'virtual', false, false, NULL, NULL, NULL, NULL, '2016-06-23 13:52:11', NULL);\n";
    $id++;
}
 
$sth->finish();
 
$dbh->disconnect();
#EOF

SQL code for correcting account counter

update-counter.sql
update domain set alias_count = p.count 
   from (select count(domain.domain) as count, 
                domain.domain as domain, 
                domain.id as myid 
         from domain,alias 
         where alias.domain_id = domain.id 
         group by domain.domain,domain.id) p 
   where domain.id = p.myid;
 
update domain set mailbox_count = p.count 
   from (select count(domain.domain) as count,
                domain.domain,domain.id as myid 
         from domain,mailbox 
         where mailbox.domain_id = domain.id 
         group by domain.domain,domain) p 
   where domain.id = p.myid;

First PagePrevious PageBack to overviewNext PageLast Page