summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mysql.initial.sql8
-rw-r--r--SQL/mysql.update-0.1a.sql51
-rw-r--r--SQL/mysql.update.sql60
-rw-r--r--SQL/mysql5.initial.sql18
-rwxr-xr-xSQL/postgres.initial.sql3
-rw-r--r--SQL/postgres.update.sql7
-rw-r--r--SQL/sqlite.initial.sql4
-rw-r--r--SQL/sqlite.update.sql27
8 files changed, 108 insertions, 70 deletions
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 7546e52da..716268b1a 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1beta2
+-- Version 0.1-beta2
--
-- --------------------------------------------------------
@@ -116,12 +116,12 @@ CREATE TABLE `messages` (
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`size` int(11) unsigned NOT NULL default '0',
`headers` text NOT NULL,
- `body` longtext,
+ `structure` text,
PRIMARY KEY (`message_id`),
KEY `user_id` (`user_id`),
- KEY `cache_key` (`cache_key`),
KEY `idx` (`idx`),
- KEY `uid` (`uid`)
+ KEY `uid` (`uid`),
+ UNIQUE `uniqueness` (`cache_key`, `uid`)
);
diff --git a/SQL/mysql.update-0.1a.sql b/SQL/mysql.update-0.1a.sql
new file mode 100644
index 000000000..054814af5
--- /dev/null
+++ b/SQL/mysql.update-0.1a.sql
@@ -0,0 +1,51 @@
+-- RoundCube Webmail update script for MySQL databases
+-- Updates from version 0.1-20051007
+
+
+ALTER TABLE `session` ADD `ip` VARCHAR(15) NOT NULL AFTER changed;
+ALTER TABLE `users` ADD `alias` VARCHAR(128) NOT NULL AFTER mail_host;
+
+
+
+-- RoundCube Webmail update script for MySQL databases
+-- Updates from version 0.1-20051021
+
+ALTER TABLE `session` CHANGE `sess_id` `sess_id` VARCHAR(40) NOT NULL;
+
+ALTER TABLE `contacts` CHANGE `del` `del` TINYINT(1) NOT NULL;
+ALTER TABLE `contacts` ADD `changed` DATETIME NOT NULL AFTER `user_id`;
+
+UPDATE `contacts` SET `del`=0 WHERE `del`=1;
+UPDATE `contacts` SET `del`=1 WHERE `del`=2;
+
+ALTER TABLE `identities` CHANGE `default` `standard` TINYINT(1) NOT NULL;
+ALTER TABLE `identities` CHANGE `del` `del` TINYINT(1) NOT NULL;
+
+UPDATE `identities` SET `del`=0 WHERE `del`=1;
+UPDATE `identities` SET `del`=1 WHERE `del`=2;
+UPDATE `identities` SET `standard`=0 WHERE `standard`=1;
+UPDATE `identities` SET `standard`=1 WHERE `standard`=2;
+
+CREATE TABLE `messages` (
+ `message_id` int(11) unsigned NOT NULL auto_increment,
+ `user_id` int(11) unsigned NOT NULL default '0',
+ `del` tinyint(1) NOT NULL default '0',
+ `cache_key` varchar(128) NOT NULL default '',
+ `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 default '',
+ `from` varchar(255) NOT NULL default '',
+ `to` varchar(255) NOT NULL default '',
+ `cc` varchar(255) NOT NULL default '',
+ `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`),
+ KEY `user_id` (`user_id`),
+ KEY `idx` (`idx`),
+ KEY `uid` (`uid`),
+ UNIQUE `uniqueness` (`cache_key`, `uid`)
+) TYPE=MyISAM;
+
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index 7fa296b0b..cfd5af361 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -1,57 +1,9 @@
-- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051007
+-- Updates from version 0.1-beta and 0.1-beta2
+ALTER TABLE `messages`
+ DROP `body`,
+ DROP INDEX `cache_key`,
+ ADD `structure` TEXT,
+ ADD UNIQUE `uniqueness` (`cache_key`, `uid`);
-ALTER TABLE `session` ADD `ip` VARCHAR(15) NOT NULL AFTER changed;
-ALTER TABLE `users` ADD `alias` VARCHAR(128) NOT NULL AFTER mail_host;
-
-
-
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051021
-
-ALTER TABLE `session` CHANGE `sess_id` `sess_id` VARCHAR(40) NOT NULL;
-
-ALTER TABLE `contacts` CHANGE `del` `del` TINYINT(1) NOT NULL;
-ALTER TABLE `contacts` ADD `changed` DATETIME NOT NULL AFTER `user_id`;
-
-UPDATE `contacts` SET `del`=0 WHERE `del`=1;
-UPDATE `contacts` SET `del`=1 WHERE `del`=2;
-
-ALTER TABLE `identities` CHANGE `default` `standard` TINYINT(1) NOT NULL;
-ALTER TABLE `identities` CHANGE `del` `del` TINYINT(1) NOT NULL;
-
-UPDATE `identities` SET `del`=0 WHERE `del`=1;
-UPDATE `identities` SET `del`=1 WHERE `del`=2;
-UPDATE `identities` SET `standard`=0 WHERE `standard`=1;
-UPDATE `identities` SET `standard`=1 WHERE `standard`=2;
-
-CREATE TABLE `messages` (
- `message_id` int(11) unsigned NOT NULL auto_increment,
- `user_id` int(11) unsigned NOT NULL default '0',
- `del` tinyint(1) NOT NULL default '0',
- `cache_key` varchar(128) NOT NULL default '',
- `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 default '',
- `from` varchar(255) NOT NULL default '',
- `to` varchar(255) NOT NULL default '',
- `cc` varchar(255) NOT NULL default '',
- `date` datetime NOT NULL default '0000-00-00 00:00:00',
- `size` int(11) unsigned NOT NULL default '0',
- `headers` text NOT NULL,
- `body` longtext,
- PRIMARY KEY (`message_id`),
- KEY `user_id` (`user_id`),
- KEY `cache_key` (`cache_key`),
- KEY `idx` (`idx`),
- KEY `uid` (`uid`)
-) TYPE=MyISAM;
-
-
-
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051216
-
-ALTER TABLE `messages` ADD `created` DATETIME NOT NULL AFTER `cache_key` ;
diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql
index 0116468ee..24e143119 100644
--- a/SQL/mysql5.initial.sql
+++ b/SQL/mysql5.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1beta2
+-- Version 0.1-beta2
--
-- --------------------------------------------------------
@@ -16,7 +16,7 @@ CREATE TABLE `session` (
`ip` varchar(15) NOT NULL,
`vars` text NOT NULL,
PRIMARY KEY(`sess_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=MYISAM CHARACTER SET ascii COLLATE ascii_general_ci;
-- Table structure for table `users`
@@ -31,15 +31,16 @@ CREATE TABLE `users` (
`language` varchar(5) NOT NULL DEFAULT 'en',
`preferences` text NOT NULL,
PRIMARY KEY(`user_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=MYISAM CHARACTER SET ascii COLLATE ascii_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) NOT NULL,
+ `cache_key` varchar(128) 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',
@@ -50,12 +51,11 @@ CREATE TABLE `messages` (
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`size` int(11) UNSIGNED NOT NULL DEFAULT '0',
`headers` text NOT NULL,
- `body` longtext,
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `structure` text,
PRIMARY KEY(`message_id`),
- INDEX `cache_key`(`cache_key`),
INDEX `idx`(`idx`),
INDEX `uid`(`uid`),
+ UNIQUE `uniqueness` (`cache_key`, `uid`),
CONSTRAINT `User_ID_FK_messages` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
@@ -67,8 +67,8 @@ CREATE TABLE `messages` (
CREATE TABLE `cache` (
`cache_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `session_id` varchar(40),
- `cache_key` varchar(128) NOT NULL,
+ `session_id` varchar(40) CHARACTER SET ascii COLLATE ascii_general_ci,
+ `cache_key` varchar(128) 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',
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 55e139603..029afaab3 100755
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -163,6 +163,7 @@ CREATE TABLE "messages" (
date timestamp with time zone NOT NULL,
size integer DEFAULT 0 NOT NULL,
headers text NOT NULL,
- body text
+ structure text
);
+ALTER TABLE "messages" ADD UNIQUE (cache_key, uid);
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
new file mode 100644
index 000000000..6ca855edc
--- /dev/null
+++ b/SQL/postgres.update.sql
@@ -0,0 +1,7 @@
+-- RoundCube Webmail update script for Postres databases
+-- Updates from version 0.1-beta and older
+
+ALTER TABLE "messages" DROP body;
+ALTER TABLE "messages" ADD structure TEXT;
+ALTER TABLE "messages" ADD UNIQUE (cache_key, uid);
+
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index ae16a31d8..4ddc40d5d 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1a
+-- Version 0.1-beta2
--
-- --------------------------------------------------------
@@ -118,7 +118,7 @@ CREATE TABLE messages (
date datetime NOT NULL default '0000-00-00 00:00:00',
size integer NOT NULL default '0',
headers text NOT NULL,
- body text
+ structure text
);
CREATE INDEX ix_messages_user_id ON messages(user_id);
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
new file mode 100644
index 000000000..e725729ad
--- /dev/null
+++ b/SQL/sqlite.update.sql
@@ -0,0 +1,27 @@
+-- RoundCube Webmail update script for SQLite databases
+-- Updates from version 0.1-beta2 and older
+
+DROP TABLE messages;
+
+CREATE TABLE messages (
+ message_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ del tinyint NOT NULL default '0',
+ cache_key varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ idx integer NOT NULL default '0',
+ uid integer NOT NULL default '0',
+ subject varchar(255) NOT NULL default '',
+ "from" varchar(255) NOT NULL default '',
+ "to" varchar(255) NOT NULL default '',
+ cc varchar(255) NOT NULL default '',
+ date datetime NOT NULL default '0000-00-00 00:00:00',
+ size integer NOT NULL default '0',
+ headers text NOT NULL,
+ structure text
+);
+
+CREATE INDEX ix_messages_user_id ON messages(user_id);
+CREATE INDEX ix_messages_cache_key ON messages(cache_key);
+CREATE INDEX ix_messages_idx ON messages(idx);
+CREATE INDEX ix_messages_uid ON messages(uid);
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-05 19:25:30 +0000