Create.vmail.sql - Revision history

Saruman!: added GeSHi

2009-02-12T19:40:36Z

added GeSHi

@@ Line 1: / Line 1: @@
- USE mysql;
+<source lang="mysql">
+USE mysql;
 Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):
   mysql> show databases;

Saruman!: added describe view_aliases

2008-11-01T12:03:49Z

added describe view_aliases

← Older revision		Revision as of 12:03, 1 November 2008
Line 82:		Line 82:
	+-----------------+		+-----------------+
	\| relaydomains \|		\| relaydomains \|
			\| view_aliases \|
	\| view_users \|		\| view_users \|
	\| virtual_aliases \|		\| virtual_aliases \|
Line 87:		Line 88:
	\| virtual_users \|		\| virtual_users \|
	+-----------------+		+-----------------+
	5 rows in set		6 rows in set

	mysql> describe relaydomains;		mysql> describe relaydomains;
Line 99:		Line 100:
	3 rows in set		3 rows in set

	describe view_users;		mysql> describe view_aliases;
			+-------------+--------------+------+-----+---------+-------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+-------------+--------------+------+-----+---------+-------+
			\| email \| varchar(131) \| YES \| \| NULL \| \|
			\| destination \| varchar(80) \| NO \| \| NULL \| \|
			+-------------+--------------+------+-----+---------+-------+
			2 rows in set

			mysql> describe view_users;
	+--------+-------------+------+-----+---------+-------+		+--------+-------------+------+-----+---------+-------+
	\| Field \| Type \| Null \| Key \| Default \| Extra \|		\| Field \| Type \| Null \| Key \| Default \| Extra \|

Saruman!: added view_aliases

2008-11-01T11:58:56Z

added view_aliases

← Older revision		Revision as of 11:58, 1 November 2008
Line 57:		Line 57:
	AS email, virtual_users.passwd FROM virtual_users		AS email, virtual_users.passwd FROM virtual_users
	LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;		LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

			CREATE VIEW view_aliases AS
			SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.vdomain)
			AS email, destination FROM virtual_aliases
			LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;

	Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):		Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):

Saruman!: added view_users

2008-11-01T09:46:05Z

added view_users

← Older revision		Revision as of 09:46, 1 November 2008
Line 52:		Line 52:

	FLUSH PRIVILEGES;		FLUSH PRIVILEGES;

			CREATE VIEW view_users AS
			SELECT CONCAT(virtual_users.user, '@', virtual_domains.vdomain)
			AS email, virtual_users.passwd FROM virtual_users
			LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;

	Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):		Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):
	mysql> show databases;		mysql> show databases;
Line 71:		Line 77:
	+-----------------+		+-----------------+
	\| relaydomains \|		\| relaydomains \|
			\| view_users \|
	\| virtual_aliases \|		\| virtual_aliases \|
	\| virtual_domains \|		\| virtual_domains \|
	\| virtual_users \|		\| virtual_users \|
	+-----------------+		+-----------------+
	4 rows in set		5 rows in set

	mysql> describe relaydomains;		mysql> describe relaydomains;
Line 86:		Line 93:
	+-------------+-------------+------+-----+---------+----------------+		+-------------+-------------+------+-----+---------+----------------+
	3 rows in set		3 rows in set

			describe view_users;
			+--------+-------------+------+-----+---------+-------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+--------+-------------+------+-----+---------+-------+
			\| email \| varchar(91) \| YES \| \| NULL \| \|
			\| passwd \| varchar(32) \| NO \| \| NULL \| \|
			+--------+-------------+------+-----+---------+-------+
			2 rows in set

	mysql> describe virtual_aliases;		mysql> describe virtual_aliases;

Saruman!: Added output description

2008-10-31T00:08:34Z

Added output description

← Older revision		Revision as of 00:08, 31 October 2008
Line 52:		Line 52:

	FLUSH PRIVILEGES;		FLUSH PRIVILEGES;
			Using this script to create the ''vmail'' database should result in the following database (log into ''mysql'' as a root user, and type the commands shown after ''mysql>'' to check):
			mysql> show databases;
			+--------------------+
			\| Database \|
			+--------------------+
			\| information_schema \|
			\| mysql \|
			\| vmail \|
			+--------------------+
			3 rows in set

			mysql> use vmail;

			Database changed
			mysql> show tables;
			+-----------------+
			\| Tables_in_vmail \|
			+-----------------+
			\| relaydomains \|
			\| virtual_aliases \|
			\| virtual_domains \|
			\| virtual_users \|
			+-----------------+
			4 rows in set

			mysql> describe relaydomains;
			+-------------+-------------+------+-----+---------+----------------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+-------------+-------------+------+-----+---------+----------------+
			\| id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
			\| relaydomain \| varchar(80) \| NO \| \| NULL \| \|
			\| transport \| varchar(80) \| NO \| \| NULL \| \|
			+-------------+-------------+------+-----+---------+----------------+
			3 rows in set

			mysql> describe virtual_aliases;
			+-------------+-------------+------+-----+---------+----------------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+-------------+-------------+------+-----+---------+----------------+
			\| id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
			\| domain_id \| int(11) \| NO \| MUL \| NULL \| \|
			\| source \| varchar(80) \| NO \| \| NULL \| \|
			\| destination \| varchar(80) \| NO \| \| NULL \| \|
			+-------------+-------------+------+-----+---------+----------------+
			4 rows in set

			mysql> describe virtual_domains;
			+---------+-------------+------+-----+---------+----------------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+---------+-------------+------+-----+---------+----------------+
			\| id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
			\| vdomain \| varchar(50) \| NO \| \| NULL \| \|
			+---------+-------------+------+-----+---------+----------------+
			2 rows in set

			mysql> describe virtual_users;
			+-----------+-------------+------+-----+---------+----------------+
			\| Field \| Type \| Null \| Key \| Default \| Extra \|
			+-----------+-------------+------+-----+---------+----------------+
			\| id \| int(11) \| NO \| PRI \| NULL \| auto_increment \|
			\| domain_id \| int(11) \| NO \| \| NULL \| \|
			\| user \| varchar(40) \| NO \| \| NULL \| \|
			\| passwd \| varchar(32) \| NO \| \| NULL \| \|
			+-----------+-------------+------+-----+---------+----------------+
			4 rows in set

Saruman!: Updated to MySQL5.0, InnoDB and relational tables

2008-10-31T00:01:53Z

Updated to MySQL5.0, InnoDB and relational tables

← Older revision		Revision as of 00:01, 31 October 2008
Line 14:		Line 14:
	'vmail_admin',		'vmail_admin',
	'Y'		'Y'
	);		);

	-- Make sure that priviliges are reloaded.		-- Make sure that priviliges are reloaded.
	FLUSH PRIVILEGES;		FLUSH PRIVILEGES;

	-- ~~we're assuming the~~ database ~~does not exist yet~~		-- You should drop a pre-existing "vmail"
			-- database manually to avoid CREATE errors.
	CREATE DATABASE vmail;		CREATE DATABASE vmail;

	USE vmail;		USE vmail;

	CREATE TABLE ~~domains~~ (		CREATE TABLE virtual_domains (
	~~domain~~ VARCHAR(50) NOT NULL,		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			vdomain VARCHAR(50) NOT NULL
			) ENGINE = InnoDB;

	PRIMARY KEY (~~domain~~) ) ~~TYPE~~=~~MyISAM~~;		CREATE TABLE relaydomains (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			relaydomain VARCHAR(80) NOT NULL,
			transport VARCHAR(80) NOT NULL
			) ENGINE = InnoDB;

			CREATE TABLE virtual_users (
			id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
			domain_id INT(11) NOT NULL,
			user VARCHAR(40) NOT NULL,
			passwd VARCHAR(32) NOT NULL
			) ENGINE = InnoDB;

	CREATE TABLE ~~forwardings~~ (		CREATE TABLE virtual_aliases (
			id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
			domain_id INT(11) NOT NULL,
	source VARCHAR(80) NOT NULL,		source VARCHAR(80) NOT NULL,
	destination ~~TEXT NOT NULL,~~		destination VARCHAR(80) NOT NULL,
			FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
	~~PRIMARY KEY (source) ) TYPE=MyISAM;~~		) ENGINE = InnoDB;

	~~CREATE TABLE users (~~
	~~emailaddr~~ VARCHAR(80) NOT NULL,
	~~passwd VARCHAR(30) NOT NULL,~~

	~~PRIMARY~~ KEY (~~emailaddr)~~ ) ~~TYPE=MyISAM;~~

	~~CREATE TABLE relaydomains~~ (
	~~rdomain VARCHAR(80~~) ~~NOT NULL,~~
	~~transport VARCHAR(80) NOT NULL,~~

	~~PRIMARY KEY (rdomain)~~ ) ~~TYPE~~=~~MyISAM~~;

	FLUSH PRIVILEGES;		FLUSH PRIVILEGES;

Saruman!: limited vmal_admin rights

2008-10-30T21:04:36Z

limited vmal_admin rights

← Older revision		Revision as of 21:04, 30 October 2008
Line 1:		Line 1:
	USE mysql;		USE mysql;

	REPLACE INTO user (host, user, password)		REPLACE INTO user (host, user, password)
	VALUES (		VALUES (
	'localhost',		'localhost',
	'vmail_admin',		'vmail_admin',
	PASSWORD('SuperSecret')		PASSWORD('SuperSecret')
	);		);

	REPLACE INTO db (host, db, user, select_priv~~, insert_priv, update_priv,~~		REPLACE INTO db (host, db, user, select_priv)
	~~delete_priv, create_priv, drop_priv, index_priv~~)		VALUES (
	VALUES (		'localhost',
	'localhost',		'vmail',
	'vmail',		'vmail_admin',
	'vmail_admin',		'Y'
	~~'Y', 'Y', 'Y', 'Y',~~		);
	~~'Y', 'Y',~~ 'Y'
	);		-- Make sure that priviliges are reloaded.
			FLUSH PRIVILEGES;
	-- Make sure that priviliges are reloaded.
	FLUSH PRIVILEGES;		-- we're assuming the database does not exist yet
			CREATE DATABASE vmail;
	-- we're assuming the database does not exist yet
	CREATE DATABASE vmail;		USE vmail;

	USE vmail;		CREATE TABLE domains (
			domain VARCHAR(50) NOT NULL,
	CREATE TABLE domains (
	domain VARCHAR(50) NOT NULL,		PRIMARY KEY (domain) ) TYPE=MyISAM;

	PRIMARY KEY (domain) ) TYPE=MyISAM;		CREATE TABLE forwardings (
			source VARCHAR(80) NOT NULL,
	CREATE TABLE forwardings (		destination TEXT NOT NULL,
	source VARCHAR(80) NOT NULL,
	destination TEXT NOT NULL,		PRIMARY KEY (source) ) TYPE=MyISAM;

	PRIMARY KEY (source) ) TYPE=MyISAM;		CREATE TABLE users (
			emailaddr VARCHAR(80) NOT NULL,
	CREATE TABLE users (		passwd VARCHAR(30) NOT NULL,
	emailaddr VARCHAR(80) NOT NULL,
	passwd VARCHAR(30) NOT NULL,		PRIMARY KEY (emailaddr) ) TYPE=MyISAM;

	PRIMARY KEY (emailaddr) ) TYPE=MyISAM;		CREATE TABLE relaydomains (
			rdomain VARCHAR(80) NOT NULL,
	CREATE TABLE relaydomains (		transport VARCHAR(80) NOT NULL,
	rdomain VARCHAR(80) NOT NULL,
	transport VARCHAR(80) NOT NULL,		PRIMARY KEY (rdomain) ) TYPE=MyISAM;

	PRIMARY KEY (rdomain) ) TYPE=MyISAM;		FLUSH PRIVILEGES;

	FLUSH PRIVILEGES;

Saruman!: DB creation script

2008-10-30T20:58:36Z

DB creation script

New page

USE mysql;

REPLACE INTO user (host, user, password)
VALUES (
'localhost',
'vmail_admin',
PASSWORD('SuperSecret')
);

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
delete_priv, create_priv, drop_priv, index_priv)
VALUES (
'localhost',
'vmail',
'vmail_admin',
'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y'
);

-- Make sure that priviliges are reloaded.
FLUSH PRIVILEGES;

-- we're assuming the database does not exist yet
CREATE DATABASE vmail;

USE vmail;

CREATE TABLE domains (
domain VARCHAR(50) NOT NULL,

PRIMARY KEY (domain) ) TYPE=MyISAM;

CREATE TABLE forwardings (
source VARCHAR(80) NOT NULL,
destination TEXT NOT NULL,

PRIMARY KEY (source) ) TYPE=MyISAM;

CREATE TABLE users (
emailaddr VARCHAR(80) NOT NULL,
passwd VARCHAR(30) NOT NULL,

PRIMARY KEY (emailaddr) ) TYPE=MyISAM;

CREATE TABLE relaydomains (
rdomain VARCHAR(80) NOT NULL,
transport VARCHAR(80) NOT NULL,

PRIMARY KEY (rdomain) ) TYPE=MyISAM;

FLUSH PRIVILEGES;