| commit | author | age | ||
| e019f2 | 1 | -- Roundcube Webmail update script for Postgres databases |
| ac26b9 | 2 | -- Updates from version 0.1-stable to 0.1.1 |
| f7bfec | 3 | |
| 6cb778 | 4 | CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key); |
| A | 5 | CREATE INDEX contacts_user_id_idx ON contacts (user_id); |
| 6 | CREATE INDEX identities_user_id_idx ON identities (user_id); | |
| 7 | ||
| 8 | CREATE INDEX users_username_id_idx ON users (username); | |
| 9 | CREATE INDEX users_alias_id_idx ON users (alias); | |
| 10 | ||
| 11 | -- added ON DELETE/UPDATE actions | |
| 12 | ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey; | |
| 13 | ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; | |
| 14 | ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey; | |
| 15 | ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; | |
| 16 | ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey; | |
| 17 | ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; | |
| 18 | ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey; | |
| 19 | ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; | |
| c98f3b | 20 | |
| A | 21 | -- Updates from version 0.2-alpha |
| 22 | ||
| 23 | CREATE INDEX messages_created_idx ON messages (created); | |
| be7d3b | 24 | |
| A | 25 | -- Updates from version 0.2-beta |
| 26 | ||
| dcf780 | 27 | ALTER TABLE cache DROP session_id; |
| 3e48d2 | 28 | |
| A | 29 | CREATE INDEX session_changed_idx ON session (changed); |
| e4d9f0 | 30 | CREATE INDEX cache_created_idx ON "cache" (created); |
| debdda | 31 | |
| A | 32 | ALTER TABLE users ALTER "language" DROP NOT NULL; |
| 33 | ALTER TABLE users ALTER "language" DROP DEFAULT; | |
| a493ea | 34 | |
| A | 35 | ALTER TABLE identities ALTER del TYPE smallint; |
| 36 | ALTER TABLE identities ALTER standard TYPE smallint; | |
| 37 | ALTER TABLE contacts ALTER del TYPE smallint; | |
| 38 | ALTER TABLE messages ALTER del TYPE smallint; | |
| 3d601d | 39 | |
| A | 40 | -- Updates from version 0.3-stable |
| 41 | ||
| 7fec6b | 42 | TRUNCATE messages; |
| f47e2d | 43 | CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); |
| 23b765 | 44 | DROP INDEX contacts_user_id_idx; |
| A | 45 | CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); |
| 94fe9c | 46 | |
| A | 47 | -- Updates from version 0.3.1 |
| 48 | ||
| 49 | DROP INDEX identities_user_id_idx; | |
| 50 | CREATE INDEX identities_user_id_idx ON identities (user_id, del); | |
| 22d6b5 | 51 | |
| 8a79f2 | 52 | ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL; |
| a35062 | 53 | |
| 22d6b5 | 54 | CREATE SEQUENCE contactgroups_ids |
| A | 55 | INCREMENT BY 1 |
| 56 | NO MAXVALUE | |
| 57 | NO MINVALUE | |
| 58 | CACHE 1; | |
| ace511 | 59 | |
| 22d6b5 | 60 | CREATE TABLE contactgroups ( |
| A | 61 | contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, |
| 62 | user_id integer NOT NULL | |
| 63 | REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 64 | changed timestamp with time zone DEFAULT now() NOT NULL, | |
| 65 | del smallint NOT NULL DEFAULT 0, | |
| 66 | name varchar(128) NOT NULL DEFAULT '' | |
| 67 | ); | |
| 68 | ||
| 69 | CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); | |
| 70 | ||
| 71 | CREATE TABLE contactgroupmembers ( | |
| 72 | contactgroup_id integer NOT NULL | |
| 73 | REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 74 | contact_id integer NOT NULL | |
| 75 | REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 76 | created timestamp with time zone DEFAULT now() NOT NULL, | |
| 77 | PRIMARY KEY (contactgroup_id, contact_id) | |
| 78 | ); | |
| e2402e | 79 | |
| A | 80 | -- Updates from version 0.4-beta |
| 81 | ||
| 82 | ALTER TABLE users ALTER last_login DROP NOT NULL; | |
| 83 | ALTER TABLE users ALTER last_login SET DEFAULT NULL; | |
| ace511 | 84 | |
| A | 85 | -- Updates from version 0.4.2 |
| 86 | ||
| 87 | DROP INDEX users_username_id_idx; | |
| 8381ec | 88 | ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host); |
| 6f0968 | 89 | ALTER TABLE contacts ALTER email TYPE varchar(255); |
| ace511 | 90 | |
| cdb8b9 | 91 | TRUNCATE messages; |
| 3e2637 | 92 | |
| faf10e | 93 | -- Updates from version 0.5.1 |
| T | 94 | -- Updates from version 0.5.2 |
| 95 | -- Updates from version 0.5.3 | |
| 96 | -- Updates from version 0.5.4 | |
| 3e2637 | 97 | |
| T | 98 | ALTER TABLE contacts ADD words TEXT NULL; |
| 3a5476 | 99 | CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); |
| d08333 | 100 | |
| A | 101 | TRUNCATE messages; |
| 102 | TRUNCATE cache; | |
| 66df08 | 103 | |
| 502436 | 104 | -- Updates from version 0.6 |
| 66df08 | 105 | |
| A | 106 | CREATE TABLE dictionary ( |
| 107 | user_id integer DEFAULT NULL | |
| 108 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 109 | "language" varchar(5) NOT NULL, | |
| 110 | data text NOT NULL, | |
| 111 | CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") | |
| 112 | ); | |
| f8e48d | 113 | |
| A | 114 | CREATE SEQUENCE search_ids |
| 115 | INCREMENT BY 1 | |
| 116 | NO MAXVALUE | |
| 117 | NO MINVALUE | |
| 118 | CACHE 1; | |
| 119 | ||
| 120 | CREATE TABLE searches ( | |
| 121 | search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, | |
| 122 | user_id integer NOT NULL | |
| 123 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 124 | "type" smallint DEFAULT 0 NOT NULL, | |
| 125 | name varchar(128) NOT NULL, | |
| 126 | data text NOT NULL, | |
| 127 | CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) | |
| 128 | ); | |
| 80152b | 129 | |
| 5f481d | 130 | DROP SEQUENCE message_ids; |
| 80152b | 131 | DROP TABLE messages; |
| A | 132 | |
| 133 | CREATE TABLE cache_index ( | |
| 134 | user_id integer NOT NULL | |
| 135 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 136 | mailbox varchar(255) NOT NULL, | |
| 137 | changed timestamp with time zone DEFAULT now() NOT NULL, | |
| 7b2bf7 | 138 | valid smallint NOT NULL DEFAULT 0, |
| 80152b | 139 | data text NOT NULL, |
| A | 140 | PRIMARY KEY (user_id, mailbox) |
| 141 | ); | |
| 142 | ||
| 143 | CREATE INDEX cache_index_changed_idx ON cache_index (changed); | |
| 144 | ||
| 145 | CREATE TABLE cache_thread ( | |
| 146 | user_id integer NOT NULL | |
| 147 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 148 | mailbox varchar(255) NOT NULL, | |
| 149 | changed timestamp with time zone DEFAULT now() NOT NULL, | |
| 150 | data text NOT NULL, | |
| 151 | PRIMARY KEY (user_id, mailbox) | |
| 152 | ); | |
| 153 | ||
| 154 | CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); | |
| 155 | ||
| 156 | CREATE TABLE cache_messages ( | |
| 157 | user_id integer NOT NULL | |
| 158 | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, | |
| 159 | mailbox varchar(255) NOT NULL, | |
| 160 | uid integer NOT NULL, | |
| 161 | changed timestamp with time zone DEFAULT now() NOT NULL, | |
| 162 | data text NOT NULL, | |
| 609d39 | 163 | flags integer NOT NULL DEFAULT 0, |
| 80152b | 164 | PRIMARY KEY (user_id, mailbox, uid) |
| A | 165 | ); |
| 166 | ||
| 167 | CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); | |
| b8ae0e | 168 | |
| f45074 | 169 | -- Updates from version 0.7-beta |
| A | 170 | |
| b8ae0e | 171 | ALTER TABLE "session" ALTER sess_id TYPE varchar(128); |
| 48be8f | 172 | |
| A | 173 | -- Updates from version 0.7 |
| 174 | ||
| 175 | DROP INDEX contacts_user_id_idx; | |
| 176 | CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); | |
| 177 | ALTER TABLE contacts ALTER email TYPE text; | |
