liquid_feedback_core
diff update/core-update.v1.2.9-v1.3.0.sql @ 89:d7eadecc7b05
Merged planned changes for version 1.3.0 with latest bugfix for version 1.2.9
author | jbe |
---|---|
date | Fri Oct 29 18:36:36 2010 +0200 (2010-10-29) |
parents | update/core-update.v1.2.8-v1.3.0.sql@3a86196ed0bf |
children | 20be30c73831 |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.2.9-v1.3.0.sql Fri Oct 29 18:36:36 2010 +0200 1.3 @@ -0,0 +1,384 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TABLE "supporter" ADD COLUMN 1.11 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 1.12 + 1.13 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 1.14 + 1.15 + 1.16 +-- update comment for column "fully_frozen" of table "issue" 1.17 + 1.18 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.19 + 1.20 + 1.21 +-- update comment for column "autoreject" of table "membership" 1.22 + 1.23 +COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.'; 1.24 + 1.25 + 1.26 +-- allow column "autoreject" of table "interest" to be NULL 1.27 +-- (thus defaulting to "membership") 1.28 + 1.29 +ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL; 1.30 + 1.31 + 1.32 +-- new table "ignored_issue" to allow members to ignore particular issues in certain states 1.33 + 1.34 +CREATE TABLE "ignored_issue" ( 1.35 + PRIMARY KEY ("issue_id", "member_id"), 1.36 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.37 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.38 + "new" BOOLEAN NOT NULL DEFAULT FALSE, 1.39 + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 1.40 + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 1.41 + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 1.42 +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 1.43 + 1.44 +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 1.45 + 1.46 +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; 1.47 +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; 1.48 +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; 1.49 +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; 1.50 + 1.51 + 1.52 +-- allow area and issue delegations with trustee_id set to NULL 1.53 +-- (indicating that global or area delegation is void for that area or issue) 1.54 + 1.55 +ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL; 1.56 + 1.57 +ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null" 1.58 + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'); 1.59 + 1.60 + 1.61 +-- disable and delete "copy_autoreject" trigger on table "interest" 1.62 + 1.63 +DROP TRIGGER "copy_autoreject" ON "interest"; 1.64 +DROP FUNCTION "copy_autoreject_trigger"(); 1.65 + 1.66 + 1.67 +-- update comments on delegation views 1.68 + 1.69 +COMMENT ON VIEW "active_delegation" IS 'Helper view for views "global_delegation", "area_delegation" and "issue_delegation": Contains delegations where the truster_id refers to an active member and includes those delegations where trustee_id is NULL'; 1.70 + 1.71 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 1.72 + 1.73 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 1.74 + 1.75 + 1.76 +-- 1.77 + 1.78 +DROP FUNCTION "delegation_chain" 1.79 + ( "member"."id"%TYPE, 1.80 + "area"."id"%TYPE, 1.81 + "issue"."id"%TYPE ); 1.82 + 1.83 +DROP FUNCTION "delegation_chain" 1.84 + ( "member"."id"%TYPE, 1.85 + "area"."id"%TYPE, 1.86 + "issue"."id"%TYPE, 1.87 + "member"."id"%TYPE ); 1.88 + 1.89 +DROP TYPE "delegation_chain_row"; 1.90 + 1.91 +CREATE TYPE "delegation_chain_row" AS ( 1.92 + "index" INT4, 1.93 + "member_id" INT4, 1.94 + "member_active" BOOLEAN, 1.95 + "participation" BOOLEAN, 1.96 + "overridden" BOOLEAN, 1.97 + "scope_in" "delegation_scope", 1.98 + "scope_out" "delegation_scope", 1.99 + "disabled_out" BOOLEAN, 1.100 + "loop" "delegation_chain_loop_tag" ); 1.101 + 1.102 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 1.103 + 1.104 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 1.105 +COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null'; 1.106 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 1.107 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 1.108 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 1.109 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 1.110 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 1.111 + 1.112 + 1.113 +CREATE FUNCTION "delegation_chain" 1.114 + ( "member_id_p" "member"."id"%TYPE, 1.115 + "area_id_p" "area"."id"%TYPE, 1.116 + "issue_id_p" "issue"."id"%TYPE, 1.117 + "simulate_trustee_id_p" "member"."id"%TYPE ) 1.118 + RETURNS SETOF "delegation_chain_row" 1.119 + LANGUAGE 'plpgsql' STABLE AS $$ 1.120 + DECLARE 1.121 + "issue_row" "issue"%ROWTYPE; 1.122 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.123 + "loop_member_id_v" "member"."id"%TYPE; 1.124 + "output_row" "delegation_chain_row"; 1.125 + "output_rows" "delegation_chain_row"[]; 1.126 + "delegation_row" "delegation"%ROWTYPE; 1.127 + "row_count" INT4; 1.128 + "i" INT4; 1.129 + "loop_v" BOOLEAN; 1.130 + BEGIN 1.131 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.132 + "visited_member_ids" := '{}'; 1.133 + "loop_member_id_v" := NULL; 1.134 + "output_rows" := '{}'; 1.135 + "output_row"."index" := 0; 1.136 + "output_row"."member_id" := "member_id_p"; 1.137 + "output_row"."member_active" := TRUE; 1.138 + "output_row"."participation" := FALSE; 1.139 + "output_row"."overridden" := FALSE; 1.140 + "output_row"."disabled_out" := FALSE; 1.141 + "output_row"."scope_out" := NULL; 1.142 + LOOP 1.143 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.144 + "loop_member_id_v" := "output_row"."member_id"; 1.145 + ELSE 1.146 + "visited_member_ids" := 1.147 + "visited_member_ids" || "output_row"."member_id"; 1.148 + END IF; 1.149 + IF "output_row"."participation" THEN 1.150 + "output_row"."overridden" := TRUE; 1.151 + END IF; 1.152 + "output_row"."scope_in" := "output_row"."scope_out"; 1.153 + IF EXISTS ( 1.154 + SELECT NULL FROM "member" 1.155 + WHERE "id" = "output_row"."member_id" AND "active" 1.156 + ) THEN 1.157 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.158 + SELECT * INTO "delegation_row" FROM "delegation" 1.159 + WHERE "truster_id" = "output_row"."member_id" 1.160 + AND "scope" = 'global'; 1.161 + ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 1.162 + "output_row"."participation" := EXISTS ( 1.163 + SELECT NULL FROM "membership" 1.164 + WHERE "area_id" = "area_id_p" 1.165 + AND "member_id" = "output_row"."member_id" 1.166 + ); 1.167 + SELECT * INTO "delegation_row" FROM "delegation" 1.168 + WHERE "truster_id" = "output_row"."member_id" 1.169 + AND ("scope" = 'global' OR "area_id" = "area_id_p") 1.170 + ORDER BY "scope" DESC; 1.171 + ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 1.172 + "output_row"."participation" := EXISTS ( 1.173 + SELECT NULL FROM "interest" 1.174 + WHERE "issue_id" = "issue_id_p" 1.175 + AND "member_id" = "output_row"."member_id" 1.176 + ); 1.177 + SELECT * INTO "delegation_row" FROM "delegation" 1.178 + WHERE "truster_id" = "output_row"."member_id" 1.179 + AND ("scope" = 'global' OR 1.180 + "area_id" = "issue_row"."area_id" OR 1.181 + "issue_id" = "issue_id_p" 1.182 + ) 1.183 + ORDER BY "scope" DESC; 1.184 + ELSE 1.185 + RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 1.186 + END IF; 1.187 + ELSE 1.188 + "output_row"."member_active" := FALSE; 1.189 + "output_row"."participation" := FALSE; 1.190 + "output_row"."scope_out" := NULL; 1.191 + "delegation_row" := ROW(NULL); 1.192 + END IF; 1.193 + IF 1.194 + "output_row"."member_id" = "member_id_p" AND 1.195 + "simulate_trustee_id_p" NOTNULL 1.196 + THEN 1.197 + "output_row"."scope_out" := CASE 1.198 + WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' 1.199 + WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' 1.200 + WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' 1.201 + END; 1.202 + "output_rows" := "output_rows" || "output_row"; 1.203 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.204 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.205 + "output_row"."scope_out" := "delegation_row"."scope"; 1.206 + "output_rows" := "output_rows" || "output_row"; 1.207 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.208 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.209 + "output_row"."scope_out" := "delegation_row"."scope"; 1.210 + "output_row"."disabled_out" := TRUE; 1.211 + "output_rows" := "output_rows" || "output_row"; 1.212 + EXIT; 1.213 + ELSE 1.214 + "output_row"."scope_out" := NULL; 1.215 + "output_rows" := "output_rows" || "output_row"; 1.216 + EXIT; 1.217 + END IF; 1.218 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.219 + "output_row"."index" := "output_row"."index" + 1; 1.220 + END LOOP; 1.221 + "row_count" := array_upper("output_rows", 1); 1.222 + "i" := 1; 1.223 + "loop_v" := FALSE; 1.224 + LOOP 1.225 + "output_row" := "output_rows"["i"]; 1.226 + EXIT WHEN "output_row" ISNULL; 1.227 + IF "loop_v" THEN 1.228 + IF "i" + 1 = "row_count" THEN 1.229 + "output_row"."loop" := 'last'; 1.230 + ELSIF "i" = "row_count" THEN 1.231 + "output_row"."loop" := 'repetition'; 1.232 + ELSE 1.233 + "output_row"."loop" := 'intermediate'; 1.234 + END IF; 1.235 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.236 + "output_row"."loop" := 'first'; 1.237 + "loop_v" := TRUE; 1.238 + END IF; 1.239 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.240 + "output_row"."participation" := NULL; 1.241 + END IF; 1.242 + RETURN NEXT "output_row"; 1.243 + "i" := "i" + 1; 1.244 + END LOOP; 1.245 + RETURN; 1.246 + END; 1.247 + $$; 1.248 + 1.249 +COMMENT ON FUNCTION "delegation_chain" 1.250 + ( "member"."id"%TYPE, 1.251 + "area"."id"%TYPE, 1.252 + "issue"."id"%TYPE, 1.253 + "member"."id"%TYPE ) 1.254 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 1.255 + 1.256 +CREATE FUNCTION "delegation_chain" 1.257 + ( "member_id_p" "member"."id"%TYPE, 1.258 + "area_id_p" "area"."id"%TYPE, 1.259 + "issue_id_p" "issue"."id"%TYPE ) 1.260 + RETURNS SETOF "delegation_chain_row" 1.261 + LANGUAGE 'plpgsql' STABLE AS $$ 1.262 + DECLARE 1.263 + "result_row" "delegation_chain_row"; 1.264 + BEGIN 1.265 + FOR "result_row" IN 1.266 + SELECT * FROM "delegation_chain"( 1.267 + "member_id_p", "area_id_p", "issue_id_p", NULL 1.268 + ) 1.269 + LOOP 1.270 + RETURN NEXT "result_row"; 1.271 + END LOOP; 1.272 + RETURN; 1.273 + END; 1.274 + $$; 1.275 + 1.276 +COMMENT ON FUNCTION "delegation_chain" 1.277 + ( "member"."id"%TYPE, 1.278 + "area"."id"%TYPE, 1.279 + "issue"."id"%TYPE ) 1.280 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 1.281 + 1.282 + 1.283 +-- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions 1.284 + 1.285 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.286 + RETURNS VOID 1.287 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.288 + BEGIN 1.289 + UPDATE "member" SET 1.290 + "last_login" = NULL, 1.291 + "login" = NULL, 1.292 + "password" = NULL, 1.293 + "active" = FALSE, 1.294 + "notify_email" = NULL, 1.295 + "notify_email_unconfirmed" = NULL, 1.296 + "notify_email_secret" = NULL, 1.297 + "notify_email_secret_expiry" = NULL, 1.298 + "notify_email_lock_expiry" = NULL, 1.299 + "password_reset_secret" = NULL, 1.300 + "password_reset_secret_expiry" = NULL, 1.301 + "organizational_unit" = NULL, 1.302 + "internal_posts" = NULL, 1.303 + "realname" = NULL, 1.304 + "birthday" = NULL, 1.305 + "address" = NULL, 1.306 + "email" = NULL, 1.307 + "xmpp_address" = NULL, 1.308 + "website" = NULL, 1.309 + "phone" = NULL, 1.310 + "mobile_phone" = NULL, 1.311 + "profession" = NULL, 1.312 + "external_memberships" = NULL, 1.313 + "external_posts" = NULL, 1.314 + "statement" = NULL 1.315 + WHERE "id" = "member_id_p"; 1.316 + -- "text_search_data" is updated by triggers 1.317 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.318 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.319 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.320 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.321 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.322 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.323 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.324 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.325 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.326 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.327 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.328 + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; 1.329 + DELETE FROM "direct_voter" USING "issue" 1.330 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.331 + AND "issue"."closed" ISNULL 1.332 + AND "member_id" = "member_id_p"; 1.333 + RETURN; 1.334 + END; 1.335 + $$; 1.336 + 1.337 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.338 + RETURNS VOID 1.339 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.340 + BEGIN 1.341 + UPDATE "member" SET 1.342 + "last_login" = NULL, 1.343 + "login" = NULL, 1.344 + "password" = NULL, 1.345 + "notify_email" = NULL, 1.346 + "notify_email_unconfirmed" = NULL, 1.347 + "notify_email_secret" = NULL, 1.348 + "notify_email_secret_expiry" = NULL, 1.349 + "notify_email_lock_expiry" = NULL, 1.350 + "password_reset_secret" = NULL, 1.351 + "password_reset_secret_expiry" = NULL, 1.352 + "organizational_unit" = NULL, 1.353 + "internal_posts" = NULL, 1.354 + "realname" = NULL, 1.355 + "birthday" = NULL, 1.356 + "address" = NULL, 1.357 + "email" = NULL, 1.358 + "xmpp_address" = NULL, 1.359 + "website" = NULL, 1.360 + "phone" = NULL, 1.361 + "mobile_phone" = NULL, 1.362 + "profession" = NULL, 1.363 + "external_memberships" = NULL, 1.364 + "external_posts" = NULL, 1.365 + "statement" = NULL; 1.366 + -- "text_search_data" is updated by triggers 1.367 + DELETE FROM "invite_code"; 1.368 + DELETE FROM "setting"; 1.369 + DELETE FROM "setting_map"; 1.370 + DELETE FROM "member_relation_setting"; 1.371 + DELETE FROM "member_image"; 1.372 + DELETE FROM "contact"; 1.373 + DELETE FROM "session"; 1.374 + DELETE FROM "area_setting"; 1.375 + DELETE FROM "issue_setting"; 1.376 + DELETE FROM "initiative_setting"; 1.377 + DELETE FROM "suggestion_setting"; 1.378 + DELETE FROM "ignored_voting"; 1.379 + DELETE FROM "direct_voter" USING "issue" 1.380 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.381 + AND "issue"."closed" ISNULL; 1.382 + RETURN; 1.383 + END; 1.384 + $$; 1.385 + 1.386 + 1.387 +COMMIT;