X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=SQL%2Fsqlite.initial.sql;h=8c8da5c0f6b0c9b906455689eccc2b73587e04b2;hb=76507f7c63a660742e76889ad6e3919f3dde3bb0;hp=5120ea0b7a27eacedd8b6b505724b844e96b2fe6;hpb=3adad46e27086084a8b28a32fc4fbc953dbfef6c;p=roundcube.git diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 5120ea0..8c8da5c 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -1,11 +1,7 @@ --- RoundCube Webmail initial database structure --- Version 0.1 --- - --- -------------------------------------------------------- +-- Roundcube Webmail initial database structure -- --- Table structure for table `cache` +-- Table structure for table cache -- CREATE TABLE cache ( @@ -13,7 +9,7 @@ CREATE TABLE cache ( user_id integer NOT NULL default 0, cache_key varchar(128) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', - data longtext NOT NULL + data text NOT NULL ); CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); @@ -23,7 +19,7 @@ CREATE INDEX ix_cache_created ON cache(created); -- -------------------------------------------------------- -- --- Table structure for table contacts +-- Table structure for table contacts and related -- CREATE TABLE contacts ( @@ -32,13 +28,36 @@ CREATE TABLE contacts ( changed datetime NOT NULL default '0000-00-00 00:00:00', del tinyint NOT NULL default '0', name varchar(128) NOT NULL default '', - email varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', firstname varchar(128) NOT NULL default '', surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '' + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); + + +CREATE TABLE contactgroups ( + contactgroup_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '' ); -CREATE INDEX ix_contacts_user_id ON contacts(user_id); +CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); + + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL, + contact_id integer NOT NULL default '0', + created datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (contactgroup_id, contact_id) +); + +CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); + -- -------------------------------------------------------- @@ -49,6 +68,7 @@ CREATE INDEX ix_contacts_user_id ON contacts(user_id); CREATE TABLE identities ( identity_id integer NOT NULL PRIMARY KEY, user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', del tinyint NOT NULL default '0', standard tinyint NOT NULL default '0', name varchar(128) NOT NULL default '', @@ -60,7 +80,7 @@ CREATE TABLE identities ( html_signature tinyint NOT NULL default '0' ); -CREATE INDEX ix_identities_user_id ON identities(user_id); +CREATE INDEX ix_identities_user_id ON identities(user_id, del); -- -------------------------------------------------------- @@ -75,12 +95,12 @@ CREATE TABLE users ( mail_host varchar(128) NOT NULL default '', alias varchar(128) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', - last_login datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime DEFAULT NULL, language varchar(5), preferences text NOT NULL default '' ); -CREATE INDEX ix_users_username ON users(username); +CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); CREATE INDEX ix_users_alias ON users(alias); -- -------------------------------------------------------- @@ -90,7 +110,7 @@ CREATE INDEX ix_users_alias ON users(alias); -- CREATE TABLE session ( - sess_id varchar(40) NOT NULL PRIMARY KEY, + sess_id varchar(128) NOT NULL PRIMARY KEY, 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 default '', @@ -101,27 +121,81 @@ CREATE INDEX ix_session_changed ON session (changed); -- -------------------------------------------------------- --- --- Table structure for table messages --- +-- +-- Table structure for table dictionary +-- -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 TABLE dictionary ( + user_id integer DEFAULT NULL, + "language" varchar(5) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); + +-- -------------------------------------------------------- + +-- +-- Table structure for table searches +-- + +CREATE TABLE searches ( + search_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL DEFAULT '0', + "type" smallint NOT NULL DEFAULT '0', + name varchar(128) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_index +-- + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + valid smallint NOT NULL DEFAULT '0', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_index_changed ON cache_index (changed); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_thread +-- + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); + +-- -------------------------------------------------------- + +-- +-- Table structure for table cache_messages +-- + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + flags integer NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) ); -CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); -CREATE INDEX ix_messages_created ON messages (created); +CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);