Differences

This shows you the differences between two versions of the page.

--- exim4:sql [2016/11/08 10:49] – peter
+++ exim4:sql [2020/07/15 09:30] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== 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