| commit | author | age | ||
| e019f2 | 1 | -- Roundcube Webmail initial database structure |
| 79fe17 | 2 | |
| T | 3 | -- |
| 80152b | 4 | -- Table structure for table cache |
| 79fe17 | 5 | -- |
| T | 6 | |
| 7 | CREATE TABLE cache ( | |
| 8 | cache_id integer NOT NULL PRIMARY KEY, | |
| 9 | user_id integer NOT NULL default 0, | |
| 10 | cache_key varchar(128) NOT NULL default '', | |
| 11 | created datetime NOT NULL default '0000-00-00 00:00:00', | |
| 80152b | 12 | data text NOT NULL |
| 79fe17 | 13 | ); |
| T | 14 | |
| ac26b9 | 15 | CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); |
| e4d9f0 | 16 | CREATE INDEX ix_cache_created ON cache(created); |
| 1cded8 | 17 | |
| 79fe17 | 18 | |
| T | 19 | -- -------------------------------------------------------- |
| 20 | ||
| 21 | -- | |
| a61bbb | 22 | -- Table structure for table contacts and related |
| 79fe17 | 23 | -- |
| T | 24 | |
| 25 | CREATE TABLE contacts ( | |
| 26 | contact_id integer NOT NULL PRIMARY KEY, | |
| 27 | user_id integer NOT NULL default '0', | |
| 58e360 | 28 | changed datetime NOT NULL default '0000-00-00 00:00:00', |
| 1cded8 | 29 | del tinyint NOT NULL default '0', |
| 79fe17 | 30 | name varchar(128) NOT NULL default '', |
| 6f0968 | 31 | email varchar(255) NOT NULL default '', |
| 79fe17 | 32 | firstname varchar(128) NOT NULL default '', |
| T | 33 | surname varchar(128) NOT NULL default '', |
| 3e2637 | 34 | vcard text NOT NULL default '', |
| T | 35 | words text NOT NULL default '' |
| 79fe17 | 36 | ); |
| T | 37 | |
| 23b765 | 38 | CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); |
| 79fe17 | 39 | |
| a61bbb | 40 | |
| T | 41 | CREATE TABLE contactgroups ( |
| 42 | contactgroup_id integer NOT NULL PRIMARY KEY, | |
| 43 | user_id integer NOT NULL default '0', | |
| 44 | changed datetime NOT NULL default '0000-00-00 00:00:00', | |
| 45 | del tinyint NOT NULL default '0', | |
| 46 | name varchar(128) NOT NULL default '' | |
| 47 | ); | |
| 48 | ||
| 49 | CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); | |
| 50 | ||
| 51 | ||
| 52 | CREATE TABLE contactgroupmembers ( | |
| 53 | contactgroup_id integer NOT NULL, | |
| 54 | contact_id integer NOT NULL default '0', | |
| 55 | created datetime NOT NULL default '0000-00-00 00:00:00', | |
| 56 | PRIMARY KEY (contactgroup_id, contact_id) | |
| 57 | ); | |
| 58 | ||
| 3a5476 | 59 | CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); |
| A | 60 | |
| a61bbb | 61 | |
| 79fe17 | 62 | -- -------------------------------------------------------- |
| T | 63 | |
| 64 | -- | |
| 65 | -- Table structure for table identities | |
| 66 | -- | |
| 67 | ||
| 68 | CREATE TABLE identities ( | |
| 69 | identity_id integer NOT NULL PRIMARY KEY, | |
| 70 | user_id integer NOT NULL default '0', | |
| a35062 | 71 | changed datetime NOT NULL default '0000-00-00 00:00:00', |
| 1cded8 | 72 | del tinyint NOT NULL default '0', |
| T | 73 | standard tinyint NOT NULL default '0', |
| 79fe17 | 74 | name varchar(128) NOT NULL default '', |
| 1cded8 | 75 | organization varchar(128) default '', |
| 79fe17 | 76 | email varchar(128) NOT NULL default '', |
| T | 77 | "reply-to" varchar(128) NOT NULL default '', |
| 78 | bcc varchar(128) NOT NULL default '', | |
| a0109c | 79 | signature text NOT NULL default '', |
| S | 80 | html_signature tinyint NOT NULL default '0' |
| 79fe17 | 81 | ); |
| T | 82 | |
| 94fe9c | 83 | CREATE INDEX ix_identities_user_id ON identities(user_id, del); |
| 79fe17 | 84 | |
| T | 85 | |
| 86 | -- -------------------------------------------------------- | |
| 87 | ||
| 88 | -- | |
| 89 | -- Table structure for table users | |
| 90 | -- | |
| 91 | ||
| 92 | CREATE TABLE users ( | |
| 93 | user_id integer NOT NULL PRIMARY KEY, | |
| 94 | username varchar(128) NOT NULL default '', | |
| 95 | mail_host varchar(128) NOT NULL default '', | |
| 96 | alias varchar(128) NOT NULL default '', | |
| 97 | created datetime NOT NULL default '0000-00-00 00:00:00', | |
| e2402e | 98 | last_login datetime DEFAULT NULL, |
| debdda | 99 | language varchar(5), |
| 79fe17 | 100 | preferences text NOT NULL default '' |
| T | 101 | ); |
| 1cded8 | 102 | |
| ace511 | 103 | CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); |
| 6cb778 | 104 | CREATE INDEX ix_users_alias ON users(alias); |
| 1cded8 | 105 | |
| T | 106 | -- -------------------------------------------------------- |
| 107 | ||
| 108 | -- | |
| 109 | -- Table structure for table session | |
| 110 | -- | |
| 111 | ||
| 112 | CREATE TABLE session ( | |
| b8ae0e | 113 | sess_id varchar(128) NOT NULL PRIMARY KEY, |
| 1cded8 | 114 | created datetime NOT NULL default '0000-00-00 00:00:00', |
| T | 115 | changed datetime NOT NULL default '0000-00-00 00:00:00', |
| 84d06e | 116 | ip varchar(40) NOT NULL default '', |
| 1cded8 | 117 | vars text NOT NULL |
| T | 118 | ); |
| 119 | ||
| 3e48d2 | 120 | CREATE INDEX ix_session_changed ON session (changed); |
| 1cded8 | 121 | |
| T | 122 | -- -------------------------------------------------------- |
| 123 | ||
| 66df08 | 124 | -- |
| A | 125 | -- Table structure for table dictionary |
| 126 | -- | |
| 127 | ||
| 128 | CREATE TABLE dictionary ( | |
| 129 | user_id integer DEFAULT NULL, | |
| 130 | "language" varchar(5) NOT NULL, | |
| 131 | data text NOT NULL | |
| 132 | ); | |
| 133 | ||
| 134 | CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); | |
| 135 | ||
| f8e48d | 136 | -- -------------------------------------------------------- |
| A | 137 | |
| 138 | -- | |
| 139 | -- Table structure for table searches | |
| 140 | -- | |
| 141 | ||
| 142 | CREATE TABLE searches ( | |
| 143 | search_id integer NOT NULL PRIMARY KEY, | |
| 144 | user_id integer NOT NULL DEFAULT '0', | |
| 145 | "type" smallint NOT NULL DEFAULT '0', | |
| 146 | name varchar(128) NOT NULL, | |
| 147 | data text NOT NULL | |
| 148 | ); | |
| 149 | ||
| 150 | CREATE UNIQUE INDEX ix_searches_user_type_name (user_id, type, name); | |
| 80152b | 151 | |
| A | 152 | -- -------------------------------------------------------- |
| 153 | ||
| 154 | -- | |
| 155 | -- Table structure for table cache_index | |
| 156 | -- | |
| 157 | ||
| 158 | CREATE TABLE cache_index ( | |
| 159 | user_id integer NOT NULL, | |
| 160 | mailbox varchar(255) NOT NULL, | |
| 161 | changed datetime NOT NULL default '0000-00-00 00:00:00', | |
| 609d39 | 162 | valid smallint NOT NULL DEFAULT '0', |
| 80152b | 163 | data text NOT NULL, |
| A | 164 | PRIMARY KEY (user_id, mailbox) |
| 165 | ); | |
| 166 | ||
| 167 | CREATE INDEX ix_cache_index_changed ON cache_index (changed); | |
| 168 | ||
| 169 | -- -------------------------------------------------------- | |
| 170 | ||
| 171 | -- | |
| 172 | -- Table structure for table cache_thread | |
| 173 | -- | |
| 174 | ||
| 175 | CREATE TABLE cache_thread ( | |
| 176 | user_id integer NOT NULL, | |
| 177 | mailbox varchar(255) NOT NULL, | |
| 178 | changed datetime NOT NULL default '0000-00-00 00:00:00', | |
| 179 | data text NOT NULL, | |
| 180 | PRIMARY KEY (user_id, mailbox) | |
| 181 | ); | |
| 182 | ||
| 183 | CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); | |
| 184 | ||
| 185 | -- -------------------------------------------------------- | |
| 186 | ||
| 187 | -- | |
| 188 | -- Table structure for table cache_messages | |
| 189 | -- | |
| 190 | ||
| 191 | CREATE TABLE cache_messages ( | |
| 192 | user_id integer NOT NULL, | |
| 193 | mailbox varchar(255) NOT NULL, | |
| 194 | uid integer NOT NULL, | |
| 195 | changed datetime NOT NULL default '0000-00-00 00:00:00', | |
| 196 | data text NOT NULL, | |
| 609d39 | 197 | flags integer NOT NULL DEFAULT '0', |
| 80152b | 198 | PRIMARY KEY (user_id, mailbox, uid) |
| A | 199 | ); |
| 200 | ||
| 201 | CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); | |
