| commit | author | age | ||
| e019f2 | 1 | -- Roundcube Webmail initial database structure |
| 798ad5 | 2 | |
| 977a29 | 3 | -- |
| 399db1 | 4 | -- Sequence "users_seq" |
| AM | 5 | -- Name: users_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| 977a29 | 6 | -- |
| T | 7 | |
| 399db1 | 8 | CREATE SEQUENCE users_seq |
| 977a29 | 9 | INCREMENT BY 1 |
| T | 10 | NO MAXVALUE |
| 11 | NO MINVALUE | |
| 12 | CACHE 1; | |
| 13 | ||
| f5dc2a | 14 | -- |
| 1cded8 | 15 | -- Table "users" |
| f5dc2a | 16 | -- Name: users; Type: TABLE; Schema: public; Owner: postgres |
| S | 17 | -- |
| 18 | ||
| 19 | CREATE TABLE users ( | |
| 399db1 | 20 | user_id integer DEFAULT nextval('users_seq'::text) PRIMARY KEY, |
| 22d6b5 | 21 | username varchar(128) DEFAULT '' NOT NULL, |
| A | 22 | mail_host varchar(128) DEFAULT '' NOT NULL, |
| f5dc2a | 23 | created timestamp with time zone DEFAULT now() NOT NULL, |
| e2402e | 24 | last_login timestamp with time zone DEFAULT NULL, |
| a15d87 | 25 | failed_login timestamp with time zone DEFAULT NULL, |
| AM | 26 | failed_login_counter integer DEFAULT NULL, |
| 22d6b5 | 27 | "language" varchar(5), |
| ace511 | 28 | preferences text DEFAULT ''::text NOT NULL, |
| 8381ec | 29 | CONSTRAINT users_username_key UNIQUE (username, mail_host) |
| f5dc2a | 30 | ); |
| S | 31 | |
| 32 | ||
| 33 | -- | |
| 1cded8 | 34 | -- Table "session" |
| T | 35 | -- Name: session; Type: TABLE; Schema: public; Owner: postgres |
| 36 | -- | |
| 37 | ||
| 38 | CREATE TABLE "session" ( | |
| b8ae0e | 39 | sess_id varchar(128) DEFAULT '' PRIMARY KEY, |
| 1cded8 | 40 | created timestamp with time zone DEFAULT now() NOT NULL, |
| T | 41 | changed timestamp with time zone DEFAULT now() NOT NULL, |
| 22d6b5 | 42 | ip varchar(41) NOT NULL, |
| 1cded8 | 43 | vars text NOT NULL |
| T | 44 | ); |
| 45 | ||
| 3e48d2 | 46 | CREATE INDEX session_changed_idx ON session (changed); |
| 1cded8 | 47 | |
| b59474 | 48 | |
| T | 49 | -- |
| 399db1 | 50 | -- Sequence "identities_seq" |
| AM | 51 | -- Name: identities_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| b59474 | 52 | -- |
| T | 53 | |
| 399db1 | 54 | CREATE SEQUENCE identities_seq |
| b59474 | 55 | START WITH 1 |
| T | 56 | INCREMENT BY 1 |
| 57 | NO MAXVALUE | |
| 58 | NO MINVALUE | |
| 59 | CACHE 1; | |
| 1cded8 | 60 | |
| T | 61 | -- |
| 62 | -- Table "identities" | |
| 63 | -- Name: identities; Type: TABLE; Schema: public; Owner: postgres | |
| 64 | -- | |
| 65 | ||
| 66 | CREATE TABLE identities ( | |
| 399db1 | 67 | identity_id integer DEFAULT nextval('identities_seq'::text) PRIMARY KEY, |
| 22d6b5 | 68 | user_id integer NOT NULL |
| 80152b | 69 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| a35062 | 70 | changed timestamp with time zone DEFAULT now() NOT NULL, |
| a493ea | 71 | del smallint DEFAULT 0 NOT NULL, |
| A | 72 | standard smallint DEFAULT 0 NOT NULL, |
| 22d6b5 | 73 | name varchar(128) NOT NULL, |
| A | 74 | organization varchar(128), |
| 75 | email varchar(128) NOT NULL, | |
| 76 | "reply-to" varchar(128), | |
| 77 | bcc varchar(128), | |
| a0109c | 78 | signature text, |
| S | 79 | html_signature integer DEFAULT 0 NOT NULL |
| 1cded8 | 80 | ); |
| T | 81 | |
| 94fe9c | 82 | CREATE INDEX identities_user_id_idx ON identities (user_id, del); |
| 565c47 | 83 | CREATE INDEX identities_email_idx ON identities (email, del); |
| b59474 | 84 | |
| T | 85 | |
| 86 | -- | |
| 399db1 | 87 | -- Sequence "contacts_seq" |
| AM | 88 | -- Name: contacts_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| b59474 | 89 | -- |
| T | 90 | |
| 399db1 | 91 | CREATE SEQUENCE contacts_seq |
| b59474 | 92 | START WITH 1 |
| T | 93 | INCREMENT BY 1 |
| 94 | NO MAXVALUE | |
| 95 | NO MINVALUE | |
| 96 | CACHE 1; | |
| 1cded8 | 97 | |
| T | 98 | -- |
| 99 | -- Table "contacts" | |
| 100 | -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres | |
| 101 | -- | |
| 102 | ||
| 103 | CREATE TABLE contacts ( | |
| 399db1 | 104 | contact_id integer DEFAULT nextval('contacts_seq'::text) PRIMARY KEY, |
| 22d6b5 | 105 | user_id integer NOT NULL |
| 3e2637 | 106 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1cded8 | 107 | changed timestamp with time zone DEFAULT now() NOT NULL, |
| a493ea | 108 | del smallint DEFAULT 0 NOT NULL, |
| 22d6b5 | 109 | name varchar(128) DEFAULT '' NOT NULL, |
| 48be8f | 110 | email text DEFAULT '' NOT NULL, |
| 22d6b5 | 111 | firstname varchar(128) DEFAULT '' NOT NULL, |
| A | 112 | surname varchar(128) DEFAULT '' NOT NULL, |
| 3e2637 | 113 | vcard text, |
| T | 114 | words text |
| 1cded8 | 115 | ); |
| T | 116 | |
| 48be8f | 117 | CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); |
| 22d6b5 | 118 | |
| A | 119 | -- |
| 399db1 | 120 | -- Sequence "contactgroups_seq" |
| AM | 121 | -- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| 22d6b5 | 122 | -- |
| A | 123 | |
| 399db1 | 124 | CREATE SEQUENCE contactgroups_seq |
| 22d6b5 | 125 | INCREMENT BY 1 |
| A | 126 | NO MAXVALUE |
| 127 | NO MINVALUE | |
| 128 | CACHE 1; | |
| 129 | ||
| 130 | -- | |
| 131 | -- Table "contactgroups" | |
| 132 | -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres | |
| 133 | -- | |
| 134 | ||
| 135 | CREATE TABLE contactgroups ( | |
| 399db1 | 136 | contactgroup_id integer DEFAULT nextval('contactgroups_seq'::text) PRIMARY KEY, |
| 22d6b5 | 137 | user_id integer NOT NULL |
| A | 138 | REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 139 | changed timestamp with time zone DEFAULT now() NOT NULL, | |
| 140 | del smallint NOT NULL DEFAULT 0, | |
| 141 | name varchar(128) NOT NULL DEFAULT '' | |
| 142 | ); | |
| 143 | ||
| 144 | CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); | |
| 145 | ||
| 146 | -- | |
| 147 | -- Table "contactgroupmembers" | |
| 148 | -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres | |
| 149 | -- | |
| ace511 | 150 | |
| 22d6b5 | 151 | CREATE TABLE contactgroupmembers ( |
| A | 152 | contactgroup_id integer NOT NULL |
| 153 | REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 154 | contact_id integer NOT NULL | |
| 155 | REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 156 | created timestamp with time zone DEFAULT now() NOT NULL, | |
| 157 | PRIMARY KEY (contactgroup_id, contact_id) | |
| 158 | ); | |
| 1cded8 | 159 | |
| 3a5476 | 160 | CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); |
| A | 161 | |
| 1cded8 | 162 | -- |
| T | 163 | -- Table "cache" |
| 164 | -- Name: cache; Type: TABLE; Schema: public; Owner: postgres | |
| 165 | -- | |
| 166 | ||
| 167 | CREATE TABLE "cache" ( | |
| 22d6b5 | 168 | user_id integer NOT NULL |
| 60b6d7 | 169 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 22d6b5 | 170 | cache_key varchar(128) DEFAULT '' NOT NULL, |
| 1cded8 | 171 | created timestamp with time zone DEFAULT now() NOT NULL, |
| 60b6d7 | 172 | expires timestamp with time zone DEFAULT NULL, |
| 1cded8 | 173 | data text NOT NULL |
| T | 174 | ); |
| 175 | ||
| edc63c | 176 | CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); |
| 60b6d7 | 177 | CREATE INDEX cache_expires_idx ON "cache" (expires); |
| 1cded8 | 178 | |
| T | 179 | -- |
| 50abd5 | 180 | -- Table "cache_shared" |
| AM | 181 | -- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres |
| 182 | -- | |
| 183 | ||
| 184 | CREATE TABLE "cache_shared" ( | |
| 185 | cache_key varchar(255) NOT NULL, | |
| 186 | created timestamp with time zone DEFAULT now() NOT NULL, | |
| 60b6d7 | 187 | expires timestamp with time zone DEFAULT NULL, |
| 50abd5 | 188 | data text NOT NULL |
| AM | 189 | ); |
| 190 | ||
| 191 | CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key); | |
| 60b6d7 | 192 | CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires); |
| 50abd5 | 193 | |
| AM | 194 | -- |
| 80152b | 195 | -- Table "cache_index" |
| A | 196 | -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres |
| b59474 | 197 | -- |
| T | 198 | |
| 80152b | 199 | CREATE TABLE cache_index ( |
| 22d6b5 | 200 | user_id integer NOT NULL |
| 60b6d7 | 201 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 80152b | 202 | mailbox varchar(255) NOT NULL, |
| 60b6d7 | 203 | expires timestamp with time zone DEFAULT NULL, |
| 609d39 | 204 | valid smallint NOT NULL DEFAULT 0, |
| 80152b | 205 | data text NOT NULL, |
| A | 206 | PRIMARY KEY (user_id, mailbox) |
| 1cded8 | 207 | ); |
| T | 208 | |
| 60b6d7 | 209 | CREATE INDEX cache_index_expires_idx ON cache_index (expires); |
| 80152b | 210 | |
| A | 211 | -- |
| 212 | -- Table "cache_thread" | |
| 213 | -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres | |
| 214 | -- | |
| 215 | ||
| 216 | CREATE TABLE cache_thread ( | |
| 217 | user_id integer NOT NULL | |
| 60b6d7 | 218 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 80152b | 219 | mailbox varchar(255) NOT NULL, |
| 60b6d7 | 220 | expires timestamp with time zone DEFAULT NULL, |
| 80152b | 221 | data text NOT NULL, |
| A | 222 | PRIMARY KEY (user_id, mailbox) |
| 223 | ); | |
| 224 | ||
| 60b6d7 | 225 | CREATE INDEX cache_thread_expires_idx ON cache_thread (expires); |
| 80152b | 226 | |
| A | 227 | -- |
| 228 | -- Table "cache_messages" | |
| 229 | -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres | |
| 230 | -- | |
| 231 | ||
| 232 | CREATE TABLE cache_messages ( | |
| 233 | user_id integer NOT NULL | |
| 60b6d7 | 234 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 80152b | 235 | mailbox varchar(255) NOT NULL, |
| A | 236 | uid integer NOT NULL, |
| 60b6d7 | 237 | expires timestamp with time zone DEFAULT NULL, |
| 80152b | 238 | data text NOT NULL, |
| 609d39 | 239 | flags integer NOT NULL DEFAULT 0, |
| 80152b | 240 | PRIMARY KEY (user_id, mailbox, uid) |
| A | 241 | ); |
| 242 | ||
| 60b6d7 | 243 | CREATE INDEX cache_messages_expires_idx ON cache_messages (expires); |
| 66df08 | 244 | |
| A | 245 | -- |
| 246 | -- Table "dictionary" | |
| 247 | -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres | |
| 248 | -- | |
| 249 | ||
| 250 | CREATE TABLE dictionary ( | |
| 251 | user_id integer DEFAULT NULL | |
| 60b6d7 | 252 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 66df08 | 253 | "language" varchar(5) NOT NULL, |
| A | 254 | data text NOT NULL, |
| 255 | CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") | |
| 256 | ); | |
| f8e48d | 257 | |
| A | 258 | -- |
| 399db1 | 259 | -- Sequence "searches_seq" |
| AM | 260 | -- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| f8e48d | 261 | -- |
| A | 262 | |
| 399db1 | 263 | CREATE SEQUENCE searches_seq |
| f8e48d | 264 | INCREMENT BY 1 |
| A | 265 | NO MAXVALUE |
| 266 | NO MINVALUE | |
| 267 | CACHE 1; | |
| 268 | ||
| 269 | -- | |
| 270 | -- Table "searches" | |
| 271 | -- Name: searches; Type: TABLE; Schema: public; Owner: postgres | |
| 272 | -- | |
| 273 | ||
| 274 | CREATE TABLE searches ( | |
| 399db1 | 275 | search_id integer DEFAULT nextval('searches_seq'::text) PRIMARY KEY, |
| f8e48d | 276 | user_id integer NOT NULL |
| A | 277 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 278 | "type" smallint DEFAULT 0 NOT NULL, | |
| 279 | name varchar(128) NOT NULL, | |
| 280 | data text NOT NULL, | |
| 281 | CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) | |
| 282 | ); | |
| b7e7c8 | 283 | |
| AM | 284 | |
| 285 | -- | |
| 286 | -- Table "system" | |
| 287 | -- Name: system; Type: TABLE; Schema: public; Owner: postgres | |
| 288 | -- | |
| 289 | ||
| 290 | CREATE TABLE "system" ( | |
| 291 | name varchar(64) NOT NULL PRIMARY KEY, | |
| 292 | value text | |
| 293 | ); | |
| 294 | ||
| a15d87 | 295 | INSERT INTO system (name, value) VALUES ('roundcube-version', '2015111100'); |
