Mercurial > hg > rc2

comparison SQL/sqlite.initial.sql @ 0:4681f974d28b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
vanilla 1.3.3 distro, I hope
author Charlie Root
date Thu, 04 Jan 2018 15:52:31 -0500
parents
children bf99236cc5cd
comparison
equal deleted inserted replaced
-1:000000000000 0:4681f974d28b
1 -- Roundcube Webmail initial database structure
2
3 --
4 -- Table structure for table contacts and related
5 --
6
7 CREATE TABLE contacts (
8 contact_id integer NOT NULL PRIMARY KEY,
9 user_id integer NOT NULL,
10 changed datetime NOT NULL default '0000-00-00 00:00:00',
11 del tinyint NOT NULL default '0',
12 name varchar(128) NOT NULL default '',
13 email text NOT NULL default '',
14 firstname varchar(128) NOT NULL default '',
15 surname varchar(128) NOT NULL default '',
16 vcard text NOT NULL default '',
17 words text NOT NULL default ''
18 );
19
20 CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
21
22
23 CREATE TABLE contactgroups (
24 contactgroup_id integer NOT NULL PRIMARY KEY,
25 user_id integer NOT NULL default '0',
26 changed datetime NOT NULL default '0000-00-00 00:00:00',
27 del tinyint NOT NULL default '0',
28 name varchar(128) NOT NULL default ''
29 );
30
31 CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
32
33
34 CREATE TABLE contactgroupmembers (
35 contactgroup_id integer NOT NULL,
36 contact_id integer NOT NULL default '0',
37 created datetime NOT NULL default '0000-00-00 00:00:00',
38 PRIMARY KEY (contactgroup_id, contact_id)
39 );
40
41 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
42
43 --
44 -- Table structure for table identities
45 --
46
47 CREATE TABLE identities (
48 identity_id integer NOT NULL PRIMARY KEY,
49 user_id integer NOT NULL default '0',
50 changed datetime NOT NULL default '0000-00-00 00:00:00',
51 del tinyint NOT NULL default '0',
52 standard tinyint NOT NULL default '0',
53 name varchar(128) NOT NULL default '',
54 organization varchar(128) default '',
55 email varchar(128) NOT NULL default '',
56 "reply-to" varchar(128) NOT NULL default '',
57 bcc varchar(128) NOT NULL default '',
58 signature text NOT NULL default '',
59 html_signature tinyint NOT NULL default '0'
60 );
61
62 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
63 CREATE INDEX ix_identities_email ON identities(email, del);
64
65 --
66 -- Table structure for table users
67 --
68
69 CREATE TABLE users (
70 user_id integer NOT NULL PRIMARY KEY,
71 username varchar(128) NOT NULL default '',
72 mail_host varchar(128) NOT NULL default '',
73 created datetime NOT NULL default '0000-00-00 00:00:00',
74 last_login datetime DEFAULT NULL,
75 language varchar(5),
76 preferences text NOT NULL default ''
77 );
78
79 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
80
81 --
82 -- Table structure for table session
83 --
84
85 CREATE TABLE session (
86 sess_id varchar(128) NOT NULL PRIMARY KEY,
87 created datetime NOT NULL default '0000-00-00 00:00:00',
88 changed datetime NOT NULL default '0000-00-00 00:00:00',
89 ip varchar(40) NOT NULL default '',
90 vars text NOT NULL
91 );
92
93 CREATE INDEX ix_session_changed ON session (changed);
94
95 --
96 -- Table structure for table dictionary
97 --
98
99 CREATE TABLE dictionary (
100 user_id integer DEFAULT NULL,
101 "language" varchar(5) NOT NULL,
102 data text NOT NULL
103 );
104
105 CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
106
107 --
108 -- Table structure for table searches
109 --
110
111 CREATE TABLE searches (
112 search_id integer NOT NULL PRIMARY KEY,
113 user_id integer NOT NULL DEFAULT '0',
114 "type" smallint NOT NULL DEFAULT '0',
115 name varchar(128) NOT NULL,
116 data text NOT NULL
117 );
118
119 CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
120
121 --
122 -- Table structure for table cache
123 --
124
125 CREATE TABLE cache (
126 user_id integer NOT NULL default 0,
127 cache_key varchar(128) NOT NULL default '',
128 created datetime NOT NULL default '0000-00-00 00:00:00',
129 expires datetime DEFAULT NULL,
130 data text NOT NULL
131 );
132
133 CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
134 CREATE INDEX ix_cache_expires ON cache(expires);
135
136 --
137 -- Table structure for table cache_shared
138 --
139
140 CREATE TABLE cache_shared (
141 cache_key varchar(255) NOT NULL,
142 created datetime NOT NULL default '0000-00-00 00:00:00',
143 expires datetime DEFAULT NULL,
144 data text NOT NULL
145 );
146
147 CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key);
148 CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
149
150 --
151 -- Table structure for table cache_index
152 --
153
154 CREATE TABLE cache_index (
155 user_id integer NOT NULL,
156 mailbox varchar(255) NOT NULL,
157 expires datetime DEFAULT NULL,
158 valid smallint NOT NULL DEFAULT '0',
159 data text NOT NULL,
160 PRIMARY KEY (user_id, mailbox)
161 );
162
163 CREATE INDEX ix_cache_index_expires ON cache_index (expires);
164
165 --
166 -- Table structure for table cache_thread
167 --
168
169 CREATE TABLE cache_thread (
170 user_id integer NOT NULL,
171 mailbox varchar(255) NOT NULL,
172 expires datetime DEFAULT NULL,
173 data text NOT NULL,
174 PRIMARY KEY (user_id, mailbox)
175 );
176
177 CREATE INDEX ix_cache_thread_expires ON cache_thread (expires);
178
179 --
180 -- Table structure for table cache_messages
181 --
182
183 CREATE TABLE cache_messages (
184 user_id integer NOT NULL,
185 mailbox varchar(255) NOT NULL,
186 uid integer NOT NULL,
187 expires datetime DEFAULT NULL,
188 data text NOT NULL,
189 flags integer NOT NULL DEFAULT '0',
190 PRIMARY KEY (user_id, mailbox, uid)
191 );
192
193 CREATE INDEX ix_cache_messages_expires ON cache_messages (expires);
194
195 --
196 -- Table structure for table system
197 --
198
199 CREATE TABLE system (
200 name varchar(64) NOT NULL PRIMARY KEY,
201 value text NOT NULL
202 );
203
204 INSERT INTO system (name, value) VALUES ('roundcube-version', '2015030800');