summaryrefslogtreecommitdiffstats
path: root/data/sql
diff options
context:
space:
mode:
Diffstat (limited to 'data/sql')
-rw-r--r--data/sql/lib.model.schema.sql181
-rw-r--r--data/sql/sqldb.map2
2 files changed, 183 insertions, 0 deletions
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;
diff --git a/data/sql/sqldb.map b/data/sql/sqldb.map
new file mode 100644
index 0000000..83d47c5
--- /dev/null
+++ b/data/sql/sqldb.map
@@ -0,0 +1,2 @@
+# Sqlfile -> Database map
+lib.model.schema.sql=propel