From fa898a4a84202f0ba28c27a39e059c6f5a442c4e Mon Sep 17 00:00:00 2001 From: thomascube Date: Sat, 29 Nov 2008 17:18:52 +0000 Subject: Simplify MYSQL database initialization instructions --- INSTALL | 24 +++------ SQL/mysql.initial.sql | 131 +++++++++++++++++++++++++++++++++++++++++++++++++ SQL/mysql5.initial.sql | 131 ------------------------------------------------- 3 files changed, 138 insertions(+), 148 deletions(-) create mode 100644 SQL/mysql.initial.sql delete mode 100644 SQL/mysql5.initial.sql diff --git a/INSTALL b/INSTALL index 0c3b05dee..8d57eb2a1 100644 --- a/INSTALL +++ b/INSTALL @@ -46,8 +46,8 @@ INSTALLATION DATABASE SETUP ============== -* MySQL 4.0.x -------------- +* MySQL +------- Setting up the mysql database can be done by creating an empty database, importing the table layout and granting the proper permissions to the roundcube user. Here is an example of that procedure: @@ -57,26 +57,16 @@ roundcube user. Here is an example of that procedure: > GRANT ALL PRIVILEGES ON roundcubemail.* TO roundcube@localhost IDENTIFIED BY 'password'; > quit -# mysql roundcubemail < SQL/mysql.initial.sql - - -* MySQL 4.1.x/5.x ------------------ -For MySQL version 4.1 and up, it's recommended to create the database for -RoundCube with utf-8 charset. Here's an example of the init procedure: - -# mysql -> CREATE DATABASE roundcubemail DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; -> GRANT ALL PRIVILEGES ON roundcubemail.* TO roundcube@localhost - IDENTIFIED BY 'password'; -> quit -# mysql roundcubemail < SQL/mysql5.initial.sql +# mysql roundcubemail < SQL/mysql.initial.sql -Note: 'password' is the master password for the roundcube user. It is strongly +Note 1: 'password' is the master password for the roundcube user. It is strongly recommended you replace this with a more secure password. Please keep in mind: You need to specify this password later in 'config/db.inc.php'. +Note 2: For MySQL version 4.1 and up, it's recommended to create the database for +RoundCube with utf-8 charset. + * SQLite -------- diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql new file mode 100644 index 000000000..5a1bfd1b9 --- /dev/null +++ b/SQL/mysql.initial.sql @@ -0,0 +1,131 @@ +-- RoundCube Webmail initial database structure +-- Version 0.2 + +-- -------------------------------------------------------- + +/*!40014 SET FOREIGN_KEY_CHECKS=0 */; + + +-- Table structure for table `session` + +CREATE TABLE `session` ( + `sess_id` varchar(40) NOT NULL, + `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `ip` varchar(40) NOT NULL, + `vars` text NOT NULL, + PRIMARY KEY(`sess_id`), + INDEX `changed_index` (`changed`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `users` + +CREATE TABLE `users` ( + `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `username` varchar(128) NOT NULL, + `mail_host` varchar(128) NOT NULL, + `alias` varchar(128) NOT NULL, + `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `language` varchar(5), + `preferences` text, + PRIMARY KEY(`user_id`), + INDEX `username_index` (`username`), + INDEX `alias_index` (`alias`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `messages` + +CREATE TABLE `messages` ( + `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `del` tinyint(1) NOT NULL DEFAULT '0', + `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, + `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `idx` int(11) UNSIGNED NOT NULL DEFAULT '0', + `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', + `subject` varchar(255) NOT NULL, + `from` varchar(255) NOT NULL, + `to` varchar(255) NOT NULL, + `cc` varchar(255) NOT NULL, + `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `size` int(11) UNSIGNED NOT NULL DEFAULT '0', + `headers` text NOT NULL, + `structure` text, + PRIMARY KEY(`message_id`), + INDEX `created_index` (`created`), + UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`), + CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) + /*!40008 + ON DELETE CASCADE + ON UPDATE CASCADE */ +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `cache` + +CREATE TABLE `cache` ( + `cache_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL , + `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `data` longtext NOT NULL, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY(`cache_id`), + INDEX `created_index` (`created`), + INDEX `user_cache_index` (`user_id`,`cache_key`), + CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) + /*!40008 + ON DELETE CASCADE + ON UPDATE CASCADE */ +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `contacts` + +CREATE TABLE `contacts` ( + `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `del` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `email` varchar(128) NOT NULL, + `firstname` varchar(128) NOT NULL, + `surname` varchar(128) NOT NULL, + `vcard` text NULL, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY(`contact_id`), + CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) + /*!40008 + ON DELETE CASCADE + ON UPDATE CASCADE */ +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +-- Table structure for table `identities` + +CREATE TABLE `identities` ( + `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `del` tinyint(1) NOT NULL DEFAULT '0', + `standard` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `organization` varchar(128) NOT NULL DEFAULT '', + `email` varchar(128) NOT NULL, + `reply-to` varchar(128) NOT NULL DEFAULT '', + `bcc` varchar(128) NOT NULL DEFAULT '', + `signature` text, + `html_signature` tinyint(1) NOT NULL DEFAULT '0', + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + PRIMARY KEY(`identity_id`), + CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) + /*!40008 + ON DELETE CASCADE + ON UPDATE CASCADE */ +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + + +/*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql deleted file mode 100644 index 5a1bfd1b9..000000000 --- a/SQL/mysql5.initial.sql +++ /dev/null @@ -1,131 +0,0 @@ --- RoundCube Webmail initial database structure --- Version 0.2 - --- -------------------------------------------------------- - -/*!40014 SET FOREIGN_KEY_CHECKS=0 */; - - --- Table structure for table `session` - -CREATE TABLE `session` ( - `sess_id` varchar(40) NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `ip` varchar(40) NOT NULL, - `vars` text NOT NULL, - PRIMARY KEY(`sess_id`), - INDEX `changed_index` (`changed`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - --- Table structure for table `users` - -CREATE TABLE `users` ( - `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `username` varchar(128) NOT NULL, - `mail_host` varchar(128) NOT NULL, - `alias` varchar(128) NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `language` varchar(5), - `preferences` text, - PRIMARY KEY(`user_id`), - INDEX `username_index` (`username`), - INDEX `alias_index` (`alias`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - --- Table structure for table `messages` - -CREATE TABLE `messages` ( - `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `del` tinyint(1) NOT NULL DEFAULT '0', - `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL, - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `idx` int(11) UNSIGNED NOT NULL DEFAULT '0', - `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', - `subject` varchar(255) NOT NULL, - `from` varchar(255) NOT NULL, - `to` varchar(255) NOT NULL, - `cc` varchar(255) NOT NULL, - `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `size` int(11) UNSIGNED NOT NULL DEFAULT '0', - `headers` text NOT NULL, - `structure` text, - PRIMARY KEY(`message_id`), - INDEX `created_index` (`created`), - UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`), - CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - --- Table structure for table `cache` - -CREATE TABLE `cache` ( - `cache_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL , - `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `data` longtext NOT NULL, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY(`cache_id`), - INDEX `created_index` (`created`), - INDEX `user_cache_index` (`user_id`,`cache_key`), - CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - --- Table structure for table `contacts` - -CREATE TABLE `contacts` ( - `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `del` tinyint(1) NOT NULL DEFAULT '0', - `name` varchar(128) NOT NULL, - `email` varchar(128) NOT NULL, - `firstname` varchar(128) NOT NULL, - `surname` varchar(128) NOT NULL, - `vcard` text NULL, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY(`contact_id`), - CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - --- Table structure for table `identities` - -CREATE TABLE `identities` ( - `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `del` tinyint(1) NOT NULL DEFAULT '0', - `standard` tinyint(1) NOT NULL DEFAULT '0', - `name` varchar(128) NOT NULL, - `organization` varchar(128) NOT NULL DEFAULT '', - `email` varchar(128) NOT NULL, - `reply-to` varchar(128) NOT NULL DEFAULT '', - `bcc` varchar(128) NOT NULL DEFAULT '', - `signature` text, - `html_signature` tinyint(1) NOT NULL DEFAULT '0', - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY(`identity_id`), - CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - - -/*!40014 SET FOREIGN_KEY_CHECKS=1 */; -- cgit v1.2.3