version-001.sql 2.03 KB
Newer Older
1

2 3 4 5
--
-- FolderTable
--

6 7 8
CREATE TABLE FolderTable (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
9 10 11 12 13 14 15 16 17 18 19 20
    parent_id INTEGER REFERENCES FolderTable ON DELETE RESTRICT
);

CREATE INDEX FolderTableNameIndex ON FolderTable(name);
CREATE INDEX FolderTableParentIndex ON FolderTable(parent_id);

--
-- MessageTable
--

CREATE TABLE MessageTable (
    id INTEGER PRIMARY KEY,
21
    fields INTEGER,
22 23 24 25 26 27 28 29 30 31 32 33 34 35
    
    date_field TEXT,
    date_time_t INTEGER,
    
    from_field TEXT,
    sender TEXT,
    reply_to TEXT,
    
    to_field TEXT,
    cc TEXT,
    bcc TEXT,
    
    message_id TEXT,
    in_reply_to TEXT,
36
    reference_ids TEXT,
37 38 39 40 41
    
    subject TEXT,
    
    header TEXT,
    
42 43 44
    body TEXT,
    
    preview TEXT
45 46 47 48 49 50 51 52 53 54 55 56
);

CREATE INDEX MessageTableMessageIDIndex ON MessageTable(message_id);

--
-- MessageLocationTable
--

CREATE TABLE MessageLocationTable (
    id INTEGER PRIMARY KEY,
    message_id INTEGER REFERENCES MessageTable ON DELETE CASCADE,
    folder_id INTEGER REFERENCES FolderTable ON DELETE CASCADE,
57 58
    ordering INTEGER,
    remove_marker INTEGER DEFAULT 0
59 60 61 62
);

CREATE INDEX MessageLocationTableMessageIDIndex ON MessageLocationTable(message_id);
CREATE INDEX MessageLocationTableFolderIDIndex ON MessageLocationTable(folder_id);
63
CREATE INDEX MessageLocationTableOrderingIndex ON MessageLocationTable(ordering ASC);
64 65 66 67 68 69 70 71 72 73 74 75

--
-- IMAP-specific tables
--

--
-- ImapFolderPropertiesTable
--

CREATE TABLE ImapFolderPropertiesTable (
    id INTEGER PRIMARY KEY,
    folder_id INTEGER UNIQUE REFERENCES FolderTable ON DELETE CASCADE,
76
    last_seen_total INTEGER,
77
    uid_validity INTEGER,
78
    uid_next INTEGER,
79
    attributes TEXT
80 81 82 83 84 85 86 87 88 89 90
);

CREATE INDEX ImapFolderPropertiesTableFolderIDIndex ON ImapFolderPropertiesTable(folder_id);

--
-- ImapMessagePropertiesTable
--

CREATE TABLE ImapMessagePropertiesTable (
    id INTEGER PRIMARY KEY,
    message_id INTEGER UNIQUE REFERENCES MessageTable ON DELETE CASCADE,
91 92 93
    flags TEXT,
    internaldate TEXT,
    rfc822_size INTEGER
94 95
);

96
CREATE INDEX ImapMessagePropertiesTableMessageIDIndex ON ImapMessagePropertiesTable(message_id);
97