Debian Codegraph @

doc/html/_sources/imap/concepts/deployment/databases.rst.txt

raw
1 .. _databases:
2
3 =========
4 Databases
5 =========
6
7 Overview
8 ========
9
10 Cyrus stores a lot of information. Rather than use traditional relational
11 database servers, its information is stored on disk in structured files.
12
13 These files should only be accessed via Cyrus API, not via a file editor.
14
15 File type and file location can be changed by updating the relevant entries
16 in :cyrusman:`imapd.conf(5)`. Files, by default, are stored in the configdirectory.
17
18 Changing the :ref:`storage type <storagetypes>` for a file will cause
19 Cyrus to automatically convert the file to the new format.
20
21 The data in these files can usually be rebuilt from the mail files themselves by
22 :ref:`running reconstruct <reconstructing-mailboxes>`.
23
24 File list
25 =========
26
27 One per system:
28
29 * `Mailbox List (mailboxes.db)`_
30 * `Annotations (annotations.db)`_
31 * `Quotas (quotas.db)`_
32 * `Duplicate Delivery (deliver.db)`_
33 * `TLS cache (tls_sessions.db)`_
34 * `PTS cache (ptscache.db)`_
35 * `STATUS cache (statuscache.db)`_
36 * `User Access (user_deny.db)`_
37 * `News database (fetchnews.db)`_
38 * `Zoneinfo db (zoneinfo.db)`_
39
40 One per user:
41
42 * `Conversations (<userid>.conversations)`_
43 * `Counters (<userid>.counters)`_
44 * `DAV Index (<userid>.dav)`_
45 * `Mailbox Keys (<userid>.mboxkey)`_
46 * `Seen State (<userid>.seen)`_
47 * `Subscriptions (<userid>.sub)`_
48 * `Search Indexes (cyrus.squat, <userid>.xapianactive, cyrus.indexed.db)`_
49
50 .. _imap-concepts-deployment-db-mailboxes:
51
52 Mailbox List (mailboxes.db)
53 ---------------------------
54
55 This database contains the master list of all mailboxes on the system. The
56 database is indexed by mailbox name and each data record contains the mailbox
57 type, the partition on which the mailbox resides and the ACL on the mailbox.
58 The format of each record is as follows::
59
60 Key: <Mailbox Name>
61
62 Data: <Type Number>SP<Partition>SP<ACL (space-separated userid/rights pairs)>
63
64 File type can be: `twoskip`_ (default), `flat`_, `skiplist`_, or `sql`_.
65
66 .. _imap-concepts-deployment-db-annotations:
67
68 Annotations (annotations.db)
69 ----------------------------
70
71 This database contains mailbox and server annotations, including WebDAV
72 properties. The database is indexed by mailbox name (empty for server
73 annotations) + annotation name + userid (empty for shared annotations) and each
74 data record contains the value size, value data, content-type of the data and
75 timestamp of the record. The format is each record is as follows::
76
77 Key: <Mailbox Name>\0<Annotation Name>\0<Userid>\0
78
79 Data: <Value Size (4 bytes)><Value>\0<Content-Type>\0<Timestamp (4 bytes)>
80
81 File type can be: `twoskip`_ (default) or `skiplist`_.
82
83 .. _imap-concepts-deployment-db-quotas:
84
85 Quotas (quotas.db)
86 ------------------
87
88 This database contains the master list of quotaroots on the system. The
89 database is indexed by quota root and each data record contains the current
90 usage of all mailboxes under the quota root and the limit of the quota root.
91 The format of each record is as follows::
92
93 Key: <Quota Root>
94
95 Data: <Usage (in bytes)>SP<Limit (in Kbytes)>
96
97 File type can be: `quotalegacy`_ (default), `flat`_, `skiplist`_, `sql`_, or `twoskip`_.
98
99
100 **Legacy Quotas**
101
102 The legacy quota database uses a distributed system in which each quota root is
103 stored in a separate file named by quota root and the contents had the
104 following format in older versions::
105
106 <Usage (in bytes)>\n
107 <Limit (in Kbytes)>\n
108
109 Newer versions are stored as a DList file with keys for each type of quota, and
110 values with both usage and limit for each type. A limit value of -1 means no
111 limit.
112
113 The translation to/from this data record format is handled by the quota_legacy
114 cyrusdb backend.
115
116 .. _imap-concepts-deployment-db-deliver:
117
118 Duplicate Delivery (deliver.db)
119 -------------------------------
120
121 This database is used for duplicate delivery suppression, retrieving usenet
122 articles by message-id, and tracking Sieve redirects and vacation responses.
123 The database is indexed by message-id + recipient (either mailbox or email
124 address) and each data record contains the timestamp of the record and the
125 UID of the message within the mailbox (if delivered locally). The format of
126 each record is as follows::
127
128 Key: <Message-ID>\0<Recipient>\0
129
130 Data: <Timestamp (4 bytes)><Message UID (4 bytes)>
131
132 File type can be: `twoskip`_ (default), `skiplist`_, or `sql`_.
133
134
135 .. _imap-concepts-deployment-db-tls:
136
137 TLS cache (tls_sessions.db)
138 ---------------------------
139
140 This database caches SSL/TLS sessions so that subsequent connections using the
141 same session-id can bypass the SSL/TLS handshaking, resulting is shorter
142 connection times. The database is indexed by session-id and each data record
143 contains the timestamp of the record and the ASN1 representation of the session
144 data. The format of each record is as follows::
145
146 Key: <Session-ID (multi-byte)>
147
148 Data: <Timestamp (4 bytes)><Session Data (multi-byte)>
149
150 File type can be: `twoskip`_ (default), `skiplist`_, or `sql`_.
151
152
153 .. _imap-concepts-deployment-db-pts:
154
155 PTS cache (ptscache.db)
156 -----------------------
157
158 This database caches authentication state records, resulting in shorter
159 authentication/canonicalization times. The database is indexed by userid and
160 each data record contains an authentication state for the userid. The format
161 of each record is as follows::
162
163 Key: <Userid>
164
165 Data: <Auth State (multi-byte)>
166
167 File type can be: `twoskip`_ (default) or `skiplist`_.
168
169
170 .. _imap-concepts-deployment-db-status:
171
172 STATUS cache (statuscache.db)
173 -----------------------------
174
175 This database caches IMAP STATUS information resulting in less I/O when the
176 STATUS information hasn't changed (mailbox and \Seen state unchanged). The
177 database is indexed by mailbox name + userid and each data record contains
178 the database version number, a bitmask of the stored status items, the mtime,
179 inode, and size of the cyrus.index file at the time the record was written,
180 the total number of messages in the mailbox, the number of recent messages,
181 the next UID value, the mailbox UID validity value, the number of unseen
182 messages, and the highest modification sequence in the mailbox. The format of
183 each record is as follows::
184
185 Key: <Mailbox Name>\0<Userid>\0
186
187 Data: <Version>SP<Bitmask of Items>SP<Mtime of Index>SP<Inode of Index>SP<Size of Index>SP<- of Messages>SP<- of Recent Messages>SP<Next UID>SP<UID Validity>SP<- of Unseen Messages>SP<Highest Mod Sequence>
188
189 File type can be: `twoskip`_ (default), `skiplist`_, or `sql`_.
190
191
192 .. _imap-concepts-deployment-db-userdeny:
193
194 User Access (user_deny.db)
195 --------------------------
196
197 This database contains a list of users that are denied access to Cyrus
198 services. The database is indexed by userid and each data record contains the
199 database version number (currently 2), a list of "wildmat" patterns (per
200 :rfc:`3977#section-4`) specifying Cyrus services to be denied, and a text
201 message to be displayed to the user upon denial. The service names to be
202 matched are those as used in :cyrusman:`cyrus.conf(5)`. The format of each
203 record is as follows::
204
205 Key: <Userid>
206
207 Data: <Version>TAB<Deny List (comma-separated wildmat patterns)>TAB<Deny Message>
208
209 File type can be: `flat`_ (default), `skiplist`_, `sql`_, or `twoskip`_.
210
211 .. _imap-concepts-deployment-db-conversations:
212
213 Conversations (<userid>.conversations)
214 --------------------------------------
215
216 This file contains all the message-id fields from every email that has been seen
217 in the past three months, mapping to the conversation IDs in which this message
218 ID has been seen, and the timestamp when it was last seen.
219
220 It also has a records for each conversation ID with details about which folders
221 have that conversations ID in them, and counts of messages and flags.
222
223 Finally there are records for each folder with the counts of conversations in
224 that folder.
225
226 File type can be: `skiplist`_ (default), `sql`_, or `twoskip`_.
227
228 .. _imap-concepts-deployment-db-counters:
229
230 Counters (<userid>.counters)
231 ----------------------------
232
233 File format not selectable.
234
235 TODO
236
237 .. _imap-concepts-deployment-db-fetchnews:
238
239 News database (fetchnews.db)
240 ----------------------------
241
242 File format not selectable.
243
244 .. _imap-concepts-deployment-db-search:
245
246 Search Indexes (cyrus.squat, <userid>.xapianactive, cyrus.indexed.db)
247 ---------------------------------------------------------------------
248
249 This is either cyrus.squat in each folder, or if you're using Xapian a single
250 <userid>.xapianactive file listing active databases with tier name and number.
251
252 cyrus.indexed.db is used by the Xapian search engine. Its file type
253 can be: `twoskip`_ (default), `flat`_, `skiplist`_, or ``zeroskip`` and is
254 determined by `search_indexed_db` in :cyrusman:`imapd.conf(5)`.
255
256 The xapianactive file contains a space separated list of tiers and databases within
257 the tier. The first element is the active tier/database, to which new entries are
258 added by `squatter -R`.
259
260 .. _imap-concepts-deployment-db-zoneinfo:
261
262 Zoneinfo db (zoneinfo.db)
263 -------------------------
264
265 This database is used for the timezone service and contains records
266 relating to timezones and their aliases. The database is indexed by
267 timezone ID and each data record contains the database version
268 number, a record type, a timestamp, and an optional list of strings
269 (either aliases for a timezone or the reference timezone for an
270 alias). The format of each record is as follows::
271
272 Key: <TZID>
273
274 Data: <Version>SP<Record Type>SP<Timestamp>SP<Data Strings (TAB-separated)>
275
276 File type can be: `twoskip`_ (default), `flat`_, or `skiplist`_.
277
278 .. _imap-concepts-deployment-db-seen:
279
280 Seen State (<userid>.seen)
281 --------------------------
282
283 This database is a per-user database and maintains the list of messages that
284 the user has read in each mailbox. The database is indexed by mailbox
285 unique-id and each data record contains the database version number, the
286 timestamp of when a message was last read, the message unique-id of the last
287 read message, the timestamp of the last record change and a list of message
288 unique-ids which have been read. The format of each record is as follows::
289
290 Key: <Mailbox UID>
291
292 Data: <Version>SP<Last Read Time>SP<Last Read UID>SP<Last Change Time>SP<List of Read UIDs>
293
294 File type can be: `twoskip`_ (default), `flat`_, or `skiplist`_.
295
296 .. _imap-concepts-deployment-db-sub:
297
298 Subscriptions (<userid>.sub)
299 ----------------------------
300
301 This database is a per-user database and contains the list of mailboxes to
302 which the user has subscribed. The database is indexed by mailbox name and
303 each data record contains no data. The format of each record is follows::
304
305 Key: <Mailbox Name>
306
307 Data: None
308
309 File type can be: `flat`_ (default), `skiplist`_, or `twoskip`_.
310
311 .. _imap-concepts-deployment-db-xapianactive:
312
313 Search Index DB List (<userid>.xapianactive)
314 --------------------------------------------
315
316 TODO
317
318
319 .. _imap-concepts-deployment-db-mboxkey:
320
321 Mailbox Keys (<userid>.mboxkey)
322 -------------------------------
323
324 This database is a per-user database and contains the list of mailbox access
325 keys which are used for generating URLAUTH-authorized URLs. The database is
326 indexed by mailbox name and each data record contains the database version
327 number and the associated access key. The format of each record is follows::
328
329 Key: <Mailbox Name>
330
331 Data: <Version (2 bytes)><Access Key (multi-byte)>
332
333 File type can be: `twoskip`_ (default) or `skiplist`_.
334
335 .. _imap-concepts-deployment-db-userdav:
336
337 DAV Index (<userid>.dav)
338 ------------------------
339
340 This embedded SQLite database is per-user and primarily maintains a
341 mapping from DAV resource names (URLs) to the corresponding Cyrus
342 mailboxes and IMAP message UIDs. The database is designed to have
343 one table per resource type (iCalendar, vCard, Sieve, etc) with each table
344 containing metadata specific to that resource type.
345
346 CalDAV
347 ######
348
349 The format of the iCalendar table used by CalDAV is as follows::
350
351 CREATE TABLE ical_objs (
352 rowid INTEGER PRIMARY KEY,
353 creationdate INTEGER,
354 mailbox TEXT NOT NULL,
355 resource TEXT NOT NULL,
356 imap_uid INTEGER,
357 lock_token TEXT,
358 lock_owner TEXT,
359 lock_ownerid TEXT,
360 lock_expire INTEGER,
361 comp_type INTEGER,
362 ical_uid TEXT,
363 organizer TEXT,
364 dtstart TEXT,
365 dtend TEXT,
366 comp_flags INTEGER,
367 sched_tag TEXT,
368 UNIQUE( mailbox, resource )
369 );
370
371
372 Because CalDAV Scheduling requires the server to locate a resource
373 by iCalendar UID regardless of which calendar collection (mailbox)
374 it resides in, the iCalendar table has an additional index as follows::
375
376 CREATE INDEX idx_ical_uid ON ical_objs ( ical_uid );
377
378
379 CardDAV
380 #######
381
382 The format of the vCard table used by CardDAV is as follows::
383
384 CREATE TABLE vcard_objs (
385 rowid INTEGER PRIMARY KEY,
386 creationdate INTEGER,
387 mailbox TEXT NOT NULL,
388 resource TEXT NOT NULL,
389 imap_uid INTEGER,
390 lock_token TEXT,
391 lock_owner TEXT,
392 lock_ownerid TEXT,
393 lock_expire INTEGER,
394 version INTEGER,
395 vcard_uid TEXT,
396 kind INTEGER,
397 fullname TEXT,
398 name TEXT,
399 nickname TEXT,
400 email TEXT,
401 UNIQUE( mailbox, resource )
402 );
403
404
405 Sieve
406 #######
407
408 The format of the Sieve table used by JMAP and ManageSieve is as follows::
409
410 CREATE TABLE sieve_scripts (
411 rowid INTEGER PRIMARY KEY,
412 creationdate INTEGER,
413 lastupdated INTEGER,
414 mailbox TEXT NOT NULL,
415 imap_uid INTEGER,
416 modseq INTEGER,
417 createdmodseq INTEGER,
418 id TEXT NOT NULL,
419 name TEXT NOT NULL,
420 content TEXT NOT NULL,
421 isactive INTEGER,
422 alive INTEGER,
423 UNIQUE( mailbox, imap_uid ),
424 UNIQUE( id )
425 );
426
427
428 Because ManageSieve requires the server to locate a resource
429 by name, the Sieve table has an additional index as follows::
430
431 CREATE INDEX idx_sieve_name ON sieve_scripts ( name );
432
433
434 .. _storagetypes:
435
436 Storage types
437 =============
438
439 Flat
440 ----
441
442 Only for debugging. The file format is human-readable, but it is
443 slow for reads and writes, and is easily corrupted.
444
445 Twoskip
446 -------
447
448 **Recommended**. A robust implementation of `Skip List <https://en.wikipedia.org/wiki/Skip_list>`_.
449 Developers interested in the details can find more information at `these talk slides <http://opera.brong.fastmail.fm.user.fm/talks/twoskip/twoskip-yapc12.pdf>`_.
450
451 Skiplist
452 --------
453
454 An implementation of the `Skip List <https://en.wikipedia.org/wiki/Skip_list>`_
455 data structure. Deprecated in favour of `Twoskip`_ as it is not robust in
456 the face of disk failure.
457
458 sql
459 ---
460
461 It is possible to store data in a normal relational SQL database. Generally
462 `Twoskip`_ is preferred as it is less operational overhead (the files can live
463 alongside Cyrus itself without requiring a separate server and DBA expertise
464 to manage). In addition, Cyrus performs much of the backups/replication/transactional
465 robustness that a SQL server provides, so the tradeoff is less compelling.
466
467 quotalegacy
468 -----------
469
470 Only valid for the `Quotas (quotas.db)`_. Has the advantage of
471 virtually no lock contention.