liquid_feedback_core

annotate update/core-update.v1.2.9-v1.3.0.sql @ 89:d7eadecc7b05

find changesets by author, revision, files, or words in the commit message
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
rev   line source
poelzi@79 1 BEGIN;
poelzi@79 2
poelzi@79 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@86 4 SELECT * FROM (VALUES ('1.3.0', 1, 3, 0))
poelzi@79 5 AS "subquery"("string", "major", "minor", "revision");
poelzi@79 6
jbe@84 7 ALTER TABLE "supporter" ADD COLUMN
jbe@84 8 "auto_support" BOOLEAN NOT NULL DEFAULT FALSE;
poelzi@81 9
jbe@85 10 COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative';
jbe@85 11
jbe@86 12
jbe@86 13 -- update comment for column "fully_frozen" of table "issue"
jbe@86 14
jbe@86 15 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.';
jbe@86 16
jbe@86 17
jbe@86 18 -- update comment for column "autoreject" of table "membership"
jbe@86 19
jbe@86 20 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.';
jbe@86 21
jbe@86 22
jbe@86 23 -- allow column "autoreject" of table "interest" to be NULL
jbe@86 24 -- (thus defaulting to "membership")
jbe@86 25
jbe@86 26 ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL;
jbe@86 27
jbe@86 28
jbe@86 29 -- new table "ignored_issue" to allow members to ignore particular issues in certain states
jbe@86 30
jbe@86 31 CREATE TABLE "ignored_issue" (
jbe@86 32 PRIMARY KEY ("issue_id", "member_id"),
jbe@86 33 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@86 34 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@86 35 "new" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@86 36 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@86 37 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@86 38 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
jbe@86 39 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
jbe@86 40
jbe@86 41 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
jbe@86 42
jbe@86 43 COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted';
jbe@86 44 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed';
jbe@86 45 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed';
jbe@86 46 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed';
jbe@86 47
jbe@86 48
jbe@86 49 -- allow area and issue delegations with trustee_id set to NULL
jbe@86 50 -- (indicating that global or area delegation is void for that area or issue)
jbe@86 51
jbe@86 52 ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL;
jbe@86 53
jbe@86 54 ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null"
jbe@86 55 CHECK ("trustee_id" NOTNULL OR "scope" != 'global');
jbe@86 56
jbe@86 57
jbe@86 58 -- disable and delete "copy_autoreject" trigger on table "interest"
jbe@86 59
jbe@86 60 DROP TRIGGER "copy_autoreject" ON "interest";
jbe@86 61 DROP FUNCTION "copy_autoreject_trigger"();
jbe@86 62
jbe@86 63
jbe@86 64 -- update comments on delegation views
jbe@86 65
jbe@86 66 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';
jbe@86 67
jbe@86 68 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';
jbe@86 69
jbe@86 70 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';
jbe@86 71
jbe@86 72
jbe@86 73 --
jbe@86 74
jbe@86 75 DROP FUNCTION "delegation_chain"
jbe@86 76 ( "member"."id"%TYPE,
jbe@86 77 "area"."id"%TYPE,
jbe@86 78 "issue"."id"%TYPE );
jbe@86 79
jbe@86 80 DROP FUNCTION "delegation_chain"
jbe@86 81 ( "member"."id"%TYPE,
jbe@86 82 "area"."id"%TYPE,
jbe@86 83 "issue"."id"%TYPE,
jbe@86 84 "member"."id"%TYPE );
jbe@86 85
jbe@86 86 DROP TYPE "delegation_chain_row";
jbe@86 87
jbe@86 88 CREATE TYPE "delegation_chain_row" AS (
jbe@86 89 "index" INT4,
jbe@86 90 "member_id" INT4,
jbe@86 91 "member_active" BOOLEAN,
jbe@86 92 "participation" BOOLEAN,
jbe@86 93 "overridden" BOOLEAN,
jbe@86 94 "scope_in" "delegation_scope",
jbe@86 95 "scope_out" "delegation_scope",
jbe@86 96 "disabled_out" BOOLEAN,
jbe@86 97 "loop" "delegation_chain_loop_tag" );
jbe@86 98
jbe@86 99 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@86 100
jbe@86 101 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@86 102 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';
jbe@86 103 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@86 104 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@86 105 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@86 106 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
jbe@86 107 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@86 108
jbe@86 109
jbe@86 110 CREATE FUNCTION "delegation_chain"
jbe@86 111 ( "member_id_p" "member"."id"%TYPE,
jbe@86 112 "area_id_p" "area"."id"%TYPE,
jbe@86 113 "issue_id_p" "issue"."id"%TYPE,
jbe@86 114 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@86 115 RETURNS SETOF "delegation_chain_row"
jbe@86 116 LANGUAGE 'plpgsql' STABLE AS $$
jbe@86 117 DECLARE
jbe@86 118 "issue_row" "issue"%ROWTYPE;
jbe@86 119 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@86 120 "loop_member_id_v" "member"."id"%TYPE;
jbe@86 121 "output_row" "delegation_chain_row";
jbe@86 122 "output_rows" "delegation_chain_row"[];
jbe@86 123 "delegation_row" "delegation"%ROWTYPE;
jbe@86 124 "row_count" INT4;
jbe@86 125 "i" INT4;
jbe@86 126 "loop_v" BOOLEAN;
jbe@86 127 BEGIN
jbe@86 128 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@86 129 "visited_member_ids" := '{}';
jbe@86 130 "loop_member_id_v" := NULL;
jbe@86 131 "output_rows" := '{}';
jbe@86 132 "output_row"."index" := 0;
jbe@86 133 "output_row"."member_id" := "member_id_p";
jbe@86 134 "output_row"."member_active" := TRUE;
jbe@86 135 "output_row"."participation" := FALSE;
jbe@86 136 "output_row"."overridden" := FALSE;
jbe@86 137 "output_row"."disabled_out" := FALSE;
jbe@86 138 "output_row"."scope_out" := NULL;
jbe@86 139 LOOP
jbe@86 140 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@86 141 "loop_member_id_v" := "output_row"."member_id";
jbe@86 142 ELSE
jbe@86 143 "visited_member_ids" :=
jbe@86 144 "visited_member_ids" || "output_row"."member_id";
jbe@86 145 END IF;
jbe@86 146 IF "output_row"."participation" THEN
jbe@86 147 "output_row"."overridden" := TRUE;
jbe@86 148 END IF;
jbe@86 149 "output_row"."scope_in" := "output_row"."scope_out";
jbe@86 150 IF EXISTS (
jbe@86 151 SELECT NULL FROM "member"
jbe@86 152 WHERE "id" = "output_row"."member_id" AND "active"
jbe@86 153 ) THEN
jbe@86 154 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@86 155 SELECT * INTO "delegation_row" FROM "delegation"
jbe@86 156 WHERE "truster_id" = "output_row"."member_id"
jbe@86 157 AND "scope" = 'global';
jbe@86 158 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
jbe@86 159 "output_row"."participation" := EXISTS (
jbe@86 160 SELECT NULL FROM "membership"
jbe@86 161 WHERE "area_id" = "area_id_p"
jbe@86 162 AND "member_id" = "output_row"."member_id"
jbe@86 163 );
jbe@86 164 SELECT * INTO "delegation_row" FROM "delegation"
jbe@86 165 WHERE "truster_id" = "output_row"."member_id"
jbe@86 166 AND ("scope" = 'global' OR "area_id" = "area_id_p")
jbe@86 167 ORDER BY "scope" DESC;
jbe@86 168 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
jbe@86 169 "output_row"."participation" := EXISTS (
jbe@86 170 SELECT NULL FROM "interest"
jbe@86 171 WHERE "issue_id" = "issue_id_p"
jbe@86 172 AND "member_id" = "output_row"."member_id"
jbe@86 173 );
jbe@86 174 SELECT * INTO "delegation_row" FROM "delegation"
jbe@86 175 WHERE "truster_id" = "output_row"."member_id"
jbe@86 176 AND ("scope" = 'global' OR
jbe@86 177 "area_id" = "issue_row"."area_id" OR
jbe@86 178 "issue_id" = "issue_id_p"
jbe@86 179 )
jbe@86 180 ORDER BY "scope" DESC;
jbe@86 181 ELSE
jbe@86 182 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
jbe@86 183 END IF;
jbe@86 184 ELSE
jbe@86 185 "output_row"."member_active" := FALSE;
jbe@86 186 "output_row"."participation" := FALSE;
jbe@86 187 "output_row"."scope_out" := NULL;
jbe@86 188 "delegation_row" := ROW(NULL);
jbe@86 189 END IF;
jbe@86 190 IF
jbe@86 191 "output_row"."member_id" = "member_id_p" AND
jbe@86 192 "simulate_trustee_id_p" NOTNULL
jbe@86 193 THEN
jbe@86 194 "output_row"."scope_out" := CASE
jbe@86 195 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
jbe@86 196 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
jbe@86 197 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
jbe@86 198 END;
jbe@86 199 "output_rows" := "output_rows" || "output_row";
jbe@86 200 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@86 201 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@86 202 "output_row"."scope_out" := "delegation_row"."scope";
jbe@86 203 "output_rows" := "output_rows" || "output_row";
jbe@86 204 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@86 205 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@86 206 "output_row"."scope_out" := "delegation_row"."scope";
jbe@86 207 "output_row"."disabled_out" := TRUE;
jbe@86 208 "output_rows" := "output_rows" || "output_row";
jbe@86 209 EXIT;
jbe@86 210 ELSE
jbe@86 211 "output_row"."scope_out" := NULL;
jbe@86 212 "output_rows" := "output_rows" || "output_row";
jbe@86 213 EXIT;
jbe@86 214 END IF;
jbe@86 215 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@86 216 "output_row"."index" := "output_row"."index" + 1;
jbe@86 217 END LOOP;
jbe@86 218 "row_count" := array_upper("output_rows", 1);
jbe@86 219 "i" := 1;
jbe@86 220 "loop_v" := FALSE;
jbe@86 221 LOOP
jbe@86 222 "output_row" := "output_rows"["i"];
jbe@86 223 EXIT WHEN "output_row" ISNULL;
jbe@86 224 IF "loop_v" THEN
jbe@86 225 IF "i" + 1 = "row_count" THEN
jbe@86 226 "output_row"."loop" := 'last';
jbe@86 227 ELSIF "i" = "row_count" THEN
jbe@86 228 "output_row"."loop" := 'repetition';
jbe@86 229 ELSE
jbe@86 230 "output_row"."loop" := 'intermediate';
jbe@86 231 END IF;
jbe@86 232 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@86 233 "output_row"."loop" := 'first';
jbe@86 234 "loop_v" := TRUE;
jbe@86 235 END IF;
jbe@86 236 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@86 237 "output_row"."participation" := NULL;
jbe@86 238 END IF;
jbe@86 239 RETURN NEXT "output_row";
jbe@86 240 "i" := "i" + 1;
jbe@86 241 END LOOP;
jbe@86 242 RETURN;
jbe@86 243 END;
jbe@86 244 $$;
jbe@86 245
jbe@86 246 COMMENT ON FUNCTION "delegation_chain"
jbe@86 247 ( "member"."id"%TYPE,
jbe@86 248 "area"."id"%TYPE,
jbe@86 249 "issue"."id"%TYPE,
jbe@86 250 "member"."id"%TYPE )
jbe@86 251 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@86 252
jbe@86 253 CREATE FUNCTION "delegation_chain"
jbe@86 254 ( "member_id_p" "member"."id"%TYPE,
jbe@86 255 "area_id_p" "area"."id"%TYPE,
jbe@86 256 "issue_id_p" "issue"."id"%TYPE )
jbe@86 257 RETURNS SETOF "delegation_chain_row"
jbe@86 258 LANGUAGE 'plpgsql' STABLE AS $$
jbe@86 259 DECLARE
jbe@86 260 "result_row" "delegation_chain_row";
jbe@86 261 BEGIN
jbe@86 262 FOR "result_row" IN
jbe@86 263 SELECT * FROM "delegation_chain"(
jbe@86 264 "member_id_p", "area_id_p", "issue_id_p", NULL
jbe@86 265 )
jbe@86 266 LOOP
jbe@86 267 RETURN NEXT "result_row";
jbe@86 268 END LOOP;
jbe@86 269 RETURN;
jbe@86 270 END;
jbe@86 271 $$;
jbe@86 272
jbe@86 273 COMMENT ON FUNCTION "delegation_chain"
jbe@86 274 ( "member"."id"%TYPE,
jbe@86 275 "area"."id"%TYPE,
jbe@86 276 "issue"."id"%TYPE )
jbe@86 277 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@86 278
jbe@86 279
jbe@86 280 -- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions
jbe@86 281
jbe@86 282 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@86 283 RETURNS VOID
jbe@86 284 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@86 285 BEGIN
jbe@86 286 UPDATE "member" SET
jbe@86 287 "last_login" = NULL,
jbe@86 288 "login" = NULL,
jbe@86 289 "password" = NULL,
jbe@86 290 "active" = FALSE,
jbe@86 291 "notify_email" = NULL,
jbe@86 292 "notify_email_unconfirmed" = NULL,
jbe@86 293 "notify_email_secret" = NULL,
jbe@86 294 "notify_email_secret_expiry" = NULL,
jbe@86 295 "notify_email_lock_expiry" = NULL,
jbe@86 296 "password_reset_secret" = NULL,
jbe@86 297 "password_reset_secret_expiry" = NULL,
jbe@86 298 "organizational_unit" = NULL,
jbe@86 299 "internal_posts" = NULL,
jbe@86 300 "realname" = NULL,
jbe@86 301 "birthday" = NULL,
jbe@86 302 "address" = NULL,
jbe@86 303 "email" = NULL,
jbe@86 304 "xmpp_address" = NULL,
jbe@86 305 "website" = NULL,
jbe@86 306 "phone" = NULL,
jbe@86 307 "mobile_phone" = NULL,
jbe@86 308 "profession" = NULL,
jbe@86 309 "external_memberships" = NULL,
jbe@86 310 "external_posts" = NULL,
jbe@86 311 "statement" = NULL
jbe@86 312 WHERE "id" = "member_id_p";
jbe@86 313 -- "text_search_data" is updated by triggers
jbe@86 314 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@86 315 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@86 316 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@86 317 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@86 318 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@86 319 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@86 320 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@86 321 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@86 322 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@86 323 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@86 324 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@86 325 DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p";
jbe@86 326 DELETE FROM "direct_voter" USING "issue"
jbe@86 327 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@86 328 AND "issue"."closed" ISNULL
jbe@86 329 AND "member_id" = "member_id_p";
jbe@86 330 RETURN;
jbe@86 331 END;
jbe@86 332 $$;
jbe@86 333
jbe@86 334 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@86 335 RETURNS VOID
jbe@86 336 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@86 337 BEGIN
jbe@86 338 UPDATE "member" SET
jbe@86 339 "last_login" = NULL,
jbe@86 340 "login" = NULL,
jbe@86 341 "password" = NULL,
jbe@86 342 "notify_email" = NULL,
jbe@86 343 "notify_email_unconfirmed" = NULL,
jbe@86 344 "notify_email_secret" = NULL,
jbe@86 345 "notify_email_secret_expiry" = NULL,
jbe@86 346 "notify_email_lock_expiry" = NULL,
jbe@86 347 "password_reset_secret" = NULL,
jbe@86 348 "password_reset_secret_expiry" = NULL,
jbe@86 349 "organizational_unit" = NULL,
jbe@86 350 "internal_posts" = NULL,
jbe@86 351 "realname" = NULL,
jbe@86 352 "birthday" = NULL,
jbe@86 353 "address" = NULL,
jbe@86 354 "email" = NULL,
jbe@86 355 "xmpp_address" = NULL,
jbe@86 356 "website" = NULL,
jbe@86 357 "phone" = NULL,
jbe@86 358 "mobile_phone" = NULL,
jbe@86 359 "profession" = NULL,
jbe@86 360 "external_memberships" = NULL,
jbe@86 361 "external_posts" = NULL,
jbe@86 362 "statement" = NULL;
jbe@86 363 -- "text_search_data" is updated by triggers
jbe@86 364 DELETE FROM "invite_code";
jbe@86 365 DELETE FROM "setting";
jbe@86 366 DELETE FROM "setting_map";
jbe@86 367 DELETE FROM "member_relation_setting";
jbe@86 368 DELETE FROM "member_image";
jbe@86 369 DELETE FROM "contact";
jbe@86 370 DELETE FROM "session";
jbe@86 371 DELETE FROM "area_setting";
jbe@86 372 DELETE FROM "issue_setting";
jbe@86 373 DELETE FROM "initiative_setting";
jbe@86 374 DELETE FROM "suggestion_setting";
jbe@86 375 DELETE FROM "ignored_voting";
jbe@86 376 DELETE FROM "direct_voter" USING "issue"
jbe@86 377 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@86 378 AND "issue"."closed" ISNULL;
jbe@86 379 RETURN;
jbe@86 380 END;
jbe@86 381 $$;
jbe@86 382
jbe@86 383
poelzi@79 384 COMMIT;

Impressum / About Us