====== Exim4 - SQL ======

===== Email Accounts Database =====


Create the Email Accounts SQL database

<code bash>
mysql> CREATE DATABASE email_accounts;
mysql> GRANT ALL PRIVILEGES ON email_accounts.* TO mail@localhost
    -> IDENTIFIED BY 'my_password' WITH GRANT OPTION;
mysql> quit
</code>


<code mysql>
CREATE TABLE mailboxes (
  id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  domain_id INT(10) NOT NULL,
  local_part VARCHAR(250) NOT NULL,
  description VARCHAR(250) NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created TIMESTAMP NOT NULL DEFAULT NOW(),
  modified TIMESTAMP NULL
);

CREATE TABLE aliases (
  id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  domain_id INT(10) NOT NULL,
  local_part VARCHAR(250) NOT NULL,
  goto VARCHAR(250) NOT NULL,
  description VARCHAR(250) NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created TIMESTAMP NOT NULL DEFAULT NOW(),
  modified TIMESTAMP NULL
);

CREATE TABLE vacations (
  id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  mailbox_id INT(10) NOT NULL,
  subject VARCHAR(250) NOT NULL,
  body TEXT NOT NULL,
  description VARCHAR(250) NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created TIMESTAMP NOT NULL DEFAULT NOW(),
  modified TIMESTAMP NULL
);

CREATE TABLE domains ( 
  id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,  
  fqdn VARCHAR(250) NOT NULL,
  type ENUM('local','relay') NOT NULL DEFAULT 'local',
  description VARCHAR(250) NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created TIMESTAMP NOT NULL DEFAULT NOW(),
  modified TIMESTAMP NULL
);


ALTER DATABASE email_accounts CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

ALTER TABLE aliases CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE aliases CHANGE local_part local_part VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE aliases CHANGE goto goto VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE aliases CHANGE description description VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
DESCRIBE aliases;

ALTER TABLE domains CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE domains CHANGE fqdn fqdn VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE domains CHANGE description description VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
DESCRIBE domains;

ALTER TABLE mailboxes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mailboxes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE mailboxes CHANGE local_part local_part VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE mailboxes CHANGE description description VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
DESCRIBE mailboxes;

ALTER TABLE vacations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE vacations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE vacations CHANGE description description VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE vacations CHANGE subject subject VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
DESCRIBE vacations;

</code>

Populate the tables.

<code mysql>
INSERT INTO domains VALUES (NULL, 'sharewiz.net', 'local', 'sharewiz.net', 1, NOW(), NOW());
INSERT INTO domains VALUES (NULL,'localhost','local','Domain for local delivery',1,NOW(),NOW());

INSERT INTO mailboxes VALUES (NULL, 1, 'john', MD5('john_password'), 'Email account for john@sharewiz.net', 1, NOW(), NOW());

INSERT INTO mailboxes VALUES (NULL,1,'admin',ENCRYPT('the_password', CONCAT('$6$', SUBSTRING(SHA('a_word'), -16))),'Account for admin@sharewiz.net',1,NOW(),NOW());

INSERT INTO mailboxes VALUES (NULL,1,'ham',ENCRYPT('the_password', CONCAT('$6$', SUBSTRING(SHA('a_word'), -16))),'Account for ham@sharewiz.net',1,NOW(),NOW());

INSERT INTO mailboxes VALUES (NULL,1,'spam',ENCRYPT('the_password', CONCAT('$6$', SUBSTRING(SHA('a_word'), -16))),'Account for spam@sharewiz.net',1,NOW(),NOW());

INSERT INTO aliases VALUES (NULL,1,'root','john@sharewiz.net','Redirect root@ to John',1,NOW(),NOW());

</code>



===== Exim Database =====

Create the Exim SQL database

<code bash>
mysql> CREATE DATABASE exim;
mysql> GRANT ALL PRIVILEGES ON exim.* TO mail@localhost
    -> IDENTIFIED BY 'my_password' WITH GRANT OPTION;
mysql> quit
</code>

<code mysql>
# Create the aliases table.
CREATE TABLE aliases (
  local_part varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default 'sharewiz.net',
  recipients text,
  PRIMARY KEY  (local_part,domain)
);

# Populate aliases.
INSERT INTO aliases VALUES ('postmaster', 'sharewiz.net', 'admin');
INSERT INTO aliases VALUES ('mailer-daemon', 'sharewiz.net', 'postmaster');
INSERT INTO aliases VALUES ('root', 'sharewiz.net', 'postmaster');
INSERT INTO aliases VALUES ('bin', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('daemon', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('sync', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('mail', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('pop', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('uucp', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('ftp', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('nobody', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('www', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('named', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('postgres', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('mysql', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('squid', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('operator', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('abuse', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('hostmaster', 'sharewiz.net', 'root');
INSERT INTO aliases VALUES ('webmaster', 'sharewiz.net', 'root');

# Create the domains table.
CREATE TABLE domains (
  domain varchar(128) NOT NULL default '',
  type enum('LOCAL','RELAY','VIRTUAL') default 'LOCAL',
  PRIMARY KEY  (domain)
);

# Populate the domains table
INSERT INTO domains VALUES ('sharewiz.net', 'LOCAL');

# Create the userforward table.
CREATE TABLE userforward (
  local_part varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  recipients text,
  PRIMARY KEY  (local_part,domain)
);

# Create the users table.
CREATE TABLE users (
  login varchar(64) NOT NULL default '',
  name varchar(128) NOT NULL default '',
  password varchar(64) NOT NULL default '',
  decrypt varchar(64) NOT NULL default '',
  uid int(10) unsigned default '1003',
  gid int(10) unsigned default '6',
  domain varchar(128) NOT NULL default 'sharewiz.net',
  quota tinyint(4) default '0',
  status enum('0','1') default '1',
  PRIMARY KEY  (login,domain)
);
</code>


<code bash>
mysql -u mail -p exim
mysql> \. exim.sql
mysql> INSERT INTO users (login,name,password,decrypt) 
    -> VALUES ('admin','John',encrypt('my_password'),'my_password');
mysql> quit
</code>