From 9f108dd1a969473375341d92a7b1252fa2cedc9a Mon Sep 17 00:00:00 2001
From: mszulecki
Date: Thu, 14 Jun 2007 17:09:01 +0000
Subject: Initial import.

git-svn-id: http://svn.sukimashita.com/repos/mailadmin/trunk@2 4281df72-ff29-0410-8fee-2d9ac0c5f5a7
---
 data/sql/lib.model.schema.sql | 181 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 181 insertions(+)
 create mode 100644 data/sql/lib.model.schema.sql

(limited to 'data/sql/lib.model.schema.sql')

diff --git a/data/sql/lib.model.schema.sql b/data/sql/lib.model.schema.sql
new file mode 100644
index 0000000..ffbe542
--- /dev/null
+++ b/data/sql/lib.model.schema.sql
@@ -0,0 +1,181 @@
+
+# This is a fix for InnoDB in MySQL >= 4.1.x
+# It "suspends judgement" for fkey relationships until are tables are set.
+SET FOREIGN_KEY_CHECKS = 0;
+
+#-----------------------------------------------------------------------------
+#-- role
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `role`;
+
+
+CREATE TABLE `role`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`name` VARCHAR(255),
+	`credentials` VARCHAR(255),
+	PRIMARY KEY (`id`)
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- user
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `user`;
+
+
+CREATE TABLE `user`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`parent_user_id` INTEGER default -1,
+	`nickname` VARCHAR(50),
+	`first_name` VARCHAR(100),
+	`last_name` VARCHAR(100),
+	`email` VARCHAR(255),
+	`sha1_password` VARCHAR(40),
+	`salt` VARCHAR(32),
+	`role_id` INTEGER,
+	`last_login` DATETIME,
+	`created_at` DATETIME,
+	PRIMARY KEY (`id`),
+	INDEX `user_FI_1` (`parent_user_id`),
+	CONSTRAINT `user_FK_1`
+		FOREIGN KEY (`parent_user_id`)
+		REFERENCES `user` (`id`)
+		ON DELETE SET NULL,
+	INDEX `user_FI_2` (`role_id`),
+	CONSTRAINT `user_FK_2`
+		FOREIGN KEY (`role_id`)
+		REFERENCES `role` (`id`)
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- domain_permission
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `domain_permission`;
+
+
+CREATE TABLE `domain_permission`
+(
+	`user_id` INTEGER,
+	`domain_id` INTEGER,
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	PRIMARY KEY (`id`),
+	INDEX `domain_permission_FI_1` (`user_id`),
+	CONSTRAINT `domain_permission_FK_1`
+		FOREIGN KEY (`user_id`)
+		REFERENCES `user` (`id`)
+		ON DELETE CASCADE,
+	INDEX `domain_permission_FI_2` (`domain_id`),
+	CONSTRAINT `domain_permission_FK_2`
+		FOREIGN KEY (`domain_id`)
+		REFERENCES `domain` (`id`)
+		ON DELETE CASCADE
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- domain
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `domain`;
+
+
+CREATE TABLE `domain`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`name` VARCHAR(255),
+	`creator_id` INTEGER,
+	`mailbox_prefix` VARCHAR(255),
+	`max_mailbox_count` INTEGER,
+	`quota` INTEGER,
+	`default_mailbox_quota` INTEGER,
+	PRIMARY KEY (`id`),
+	UNIQUE KEY `i2` (`name`),
+	INDEX `domain_FI_1` (`creator_id`),
+	CONSTRAINT `domain_FK_1`
+		FOREIGN KEY (`creator_id`)
+		REFERENCES `user` (`id`)
+		ON DELETE SET NULL
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- mailbox
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `mailbox`;
+
+
+CREATE TABLE `mailbox`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`domain_id` INTEGER,
+	`name` VARCHAR(30),
+	`password` VARCHAR(40),
+	`max_quota` INTEGER,
+	`max_address_count` INTEGER,
+	`last_login` DATETIME,
+	`active` INTEGER default 1,
+	PRIMARY KEY (`id`),
+	INDEX `mailbox_FI_1` (`domain_id`),
+	CONSTRAINT `mailbox_FK_1`
+		FOREIGN KEY (`domain_id`)
+		REFERENCES `domain` (`id`)
+		ON DELETE SET NULL
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- address
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `address`;
+
+
+CREATE TABLE `address`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`mailbox_id` INTEGER,
+	`localpart` VARCHAR(255),
+	`domain_id` INTEGER,
+	`alias` VARCHAR(255),
+	`destination` TEXT,
+	`active` INTEGER default 1,
+	`save_in_mailbox` INTEGER default 1,
+	PRIMARY KEY (`id`),
+	INDEX `address_FI_1` (`mailbox_id`),
+	CONSTRAINT `address_FK_1`
+		FOREIGN KEY (`mailbox_id`)
+		REFERENCES `mailbox` (`id`)
+		ON DELETE CASCADE,
+	INDEX `address_FI_2` (`domain_id`),
+	CONSTRAINT `address_FK_2`
+		FOREIGN KEY (`domain_id`)
+		REFERENCES `domain` (`id`)
+)Type=MyISAM;
+
+#-----------------------------------------------------------------------------
+#-- log
+#-----------------------------------------------------------------------------
+
+DROP TABLE IF EXISTS `log`;
+
+
+CREATE TABLE `log`
+(
+	`id` INTEGER  NOT NULL AUTO_INCREMENT,
+	`user_id` INTEGER default null,
+	`created_at` DATETIME,
+	`message` TEXT,
+	`host` VARCHAR(255),
+	`priority` INTEGER,
+	PRIMARY KEY (`id`),
+	INDEX `log_FI_1` (`user_id`),
+	CONSTRAINT `log_FK_1`
+		FOREIGN KEY (`user_id`)
+		REFERENCES `user` (`id`)
+		ON DELETE SET NULL
+)Type=MyISAM;
+
+# This restores the fkey checks, after having unset them earlier
+SET FOREIGN_KEY_CHECKS = 1;
-- 
cgit v1.1-32-gdbae

