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