| commit | author | age | ||
| e019f2 | 1 | -- Roundcube Webmail initial database structure |
| 798ad5 | 2 | |
| 977a29 | 3 | -- |
| T | 4 | -- Sequence "user_ids" |
| 5 | -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres | |
| 6 | -- | |
| 7 | ||
| 8 | CREATE SEQUENCE user_ids | |
| 9 | INCREMENT BY 1 | |
| 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 ( | |
| 15a9d1 | 20 | user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY, |
| 22d6b5 | 21 | username varchar(128) DEFAULT '' NOT NULL, |
| A | 22 | mail_host varchar(128) DEFAULT '' NOT NULL, |
| 23 | alias varchar(128) DEFAULT '' NOT NULL, | |
| f5dc2a | 24 | created timestamp with time zone DEFAULT now() NOT NULL, |
| e2402e | 25 | last_login timestamp with time zone DEFAULT NULL, |
| 22d6b5 | 26 | "language" varchar(5), |
| ace511 | 27 | preferences text DEFAULT ''::text NOT NULL, |
| 8381ec | 28 | CONSTRAINT users_username_key UNIQUE (username, mail_host) |
| f5dc2a | 29 | ); |
| S | 30 | |
| 6cb778 | 31 | CREATE INDEX users_alias_id_idx ON users (alias); |
| f5dc2a | 32 | |
| b59474 | 33 | |
| f5dc2a | 34 | -- |
| 1cded8 | 35 | -- Table "session" |
| T | 36 | -- Name: session; Type: TABLE; Schema: public; Owner: postgres |
| 37 | -- | |
| 38 | ||
| 39 | CREATE TABLE "session" ( | |
| 22d6b5 | 40 | sess_id varchar(40) DEFAULT '' PRIMARY KEY, |
| 1cded8 | 41 | created timestamp with time zone DEFAULT now() NOT NULL, |
| T | 42 | changed timestamp with time zone DEFAULT now() NOT NULL, |
| 22d6b5 | 43 | ip varchar(41) NOT NULL, |
| 1cded8 | 44 | vars text NOT NULL |
| T | 45 | ); |
| 46 | ||
| 3e48d2 | 47 | CREATE INDEX session_changed_idx ON session (changed); |
| 1cded8 | 48 | |
| b59474 | 49 | |
| T | 50 | -- |
| 51 | -- Sequence "identity_ids" | |
| 52 | -- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres | |
| 53 | -- | |
| 54 | ||
| 55 | CREATE SEQUENCE identity_ids | |
| 56 | START WITH 1 | |
| 57 | INCREMENT BY 1 | |
| 58 | NO MAXVALUE | |
| 59 | NO MINVALUE | |
| 60 | CACHE 1; | |
| 1cded8 | 61 | |
| T | 62 | -- |
| 63 | -- Table "identities" | |
| 64 | -- Name: identities; Type: TABLE; Schema: public; Owner: postgres | |
| 65 | -- | |
| 66 | ||
| 67 | CREATE TABLE identities ( | |
| 15a9d1 | 68 | identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, |
| 22d6b5 | 69 | user_id integer NOT NULL |
| A | 70 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| a35062 | 71 | changed timestamp with time zone DEFAULT now() NOT NULL, |
| a493ea | 72 | del smallint DEFAULT 0 NOT NULL, |
| A | 73 | standard smallint DEFAULT 0 NOT NULL, |
| 22d6b5 | 74 | name varchar(128) NOT NULL, |
| A | 75 | organization varchar(128), |
| 76 | email varchar(128) NOT NULL, | |
| 77 | "reply-to" varchar(128), | |
| 78 | bcc varchar(128), | |
| a0109c | 79 | signature text, |
| S | 80 | html_signature integer DEFAULT 0 NOT NULL |
| 1cded8 | 81 | ); |
| T | 82 | |
| 94fe9c | 83 | CREATE INDEX identities_user_id_idx ON identities (user_id, del); |
| b59474 | 84 | |
| T | 85 | |
| 86 | -- | |
| 87 | -- Sequence "contact_ids" | |
| 88 | -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres | |
| 89 | -- | |
| 90 | ||
| 91 | CREATE SEQUENCE contact_ids | |
| 92 | START WITH 1 | |
| 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 ( | |
| 15a9d1 | 104 | contact_id integer DEFAULT nextval('contact_ids'::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, |
| 6f0968 | 110 | email varchar(255) 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 | |
| 23b765 | 117 | CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); |
| 22d6b5 | 118 | |
| A | 119 | -- |
| 120 | -- Sequence "contactgroups_ids" | |
| 121 | -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres | |
| 122 | -- | |
| 123 | ||
| 124 | CREATE SEQUENCE contactgroups_ids | |
| 125 | INCREMENT BY 1 | |
| 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 ( | |
| 136 | contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, | |
| 137 | user_id integer NOT NULL | |
| 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 | -- |
| b59474 | 163 | -- Sequence "cache_ids" |
| T | 164 | -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres |
| 165 | -- | |
| 166 | ||
| 167 | CREATE SEQUENCE cache_ids | |
| 168 | INCREMENT BY 1 | |
| 169 | NO MAXVALUE | |
| 170 | NO MINVALUE | |
| 171 | CACHE 1; | |
| 172 | ||
| 173 | -- | |
| 1cded8 | 174 | -- Table "cache" |
| T | 175 | -- Name: cache; Type: TABLE; Schema: public; Owner: postgres |
| 176 | -- | |
| 177 | ||
| 178 | CREATE TABLE "cache" ( | |
| 15a9d1 | 179 | cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, |
| 22d6b5 | 180 | user_id integer NOT NULL |
| A | 181 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 182 | cache_key varchar(128) DEFAULT '' NOT NULL, | |
| 1cded8 | 183 | created timestamp with time zone DEFAULT now() NOT NULL, |
| T | 184 | data text NOT NULL |
| 185 | ); | |
| 186 | ||
| edc63c | 187 | CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); |
| e4d9f0 | 188 | CREATE INDEX cache_created_idx ON "cache" (created); |
| 1cded8 | 189 | |
| T | 190 | -- |
| b59474 | 191 | -- Sequence "message_ids" |
| T | 192 | -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres |
| 193 | -- | |
| 194 | ||
| 195 | CREATE SEQUENCE message_ids | |
| 196 | INCREMENT BY 1 | |
| 197 | NO MAXVALUE | |
| 198 | NO MINVALUE | |
| 199 | CACHE 1; | |
| 200 | ||
| 201 | -- | |
| 1cded8 | 202 | -- Table "messages" |
| T | 203 | -- Name: messages; Type: TABLE; Schema: public; Owner: postgres |
| 204 | -- | |
| 205 | ||
| c98f3b | 206 | CREATE TABLE messages ( |
| 15a9d1 | 207 | message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, |
| 22d6b5 | 208 | user_id integer NOT NULL |
| A | 209 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| a493ea | 210 | del smallint DEFAULT 0 NOT NULL, |
| 22d6b5 | 211 | cache_key varchar(128) DEFAULT '' NOT NULL, |
| b59474 | 212 | created timestamp with time zone DEFAULT now() NOT NULL, |
| 1cded8 | 213 | idx integer DEFAULT 0 NOT NULL, |
| T | 214 | uid integer DEFAULT 0 NOT NULL, |
| 22d6b5 | 215 | subject varchar(128) DEFAULT '' NOT NULL, |
| A | 216 | "from" varchar(128) DEFAULT '' NOT NULL, |
| 217 | "to" varchar(128) DEFAULT '' NOT NULL, | |
| 218 | cc varchar(128) DEFAULT '' NOT NULL, | |
| 1cded8 | 219 | date timestamp with time zone NOT NULL, |
| T | 220 | size integer DEFAULT 0 NOT NULL, |
| 221 | headers text NOT NULL, | |
| ace511 | 222 | structure text, |
| 8381ec | 223 | CONSTRAINT messages_user_id_key UNIQUE (user_id, cache_key, uid) |
| 1cded8 | 224 | ); |
| T | 225 | |
| 3d601d | 226 | CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); |
| c98f3b | 227 | CREATE INDEX messages_created_idx ON messages (created); |
