liquid_feedback_core

annotate update/core-update.v2.0.8-v2.0.9.sql @ 248:05eb08f56f09

find changesets by author, revision, files, or words in the commit message
Bugfix in view "unit_member_count"
author jbe
date Sat May 12 18:33:56 2012 +0200 (2012-05-12)
parents 4b8cc6fc2d00
children d4c754c4aeaa
rev   line source
jbe@244 1 BEGIN;
jbe@244 2
jbe@244 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@244 4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
jbe@244 5 AS "subquery"("string", "major", "minor", "revision");
jbe@244 6
jbe@248 7 CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@248 8 SELECT
jbe@248 9 "unit"."id" AS "unit_id",
jbe@248 10 count("member"."id") AS "member_count"
jbe@248 11 FROM "unit"
jbe@248 12 LEFT JOIN "privilege"
jbe@248 13 ON "privilege"."unit_id" = "unit"."id"
jbe@248 14 AND "privilege"."voting_right"
jbe@248 15 LEFT JOIN "member"
jbe@248 16 ON "member"."id" = "privilege"."member_id"
jbe@248 17 AND "member"."active"
jbe@248 18 GROUP BY "unit"."id";
jbe@248 19
jbe@244 20 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
jbe@244 21
jbe@244 22 CREATE FUNCTION "delegation_chain_for_closed_issue"
jbe@244 23 ( "member_id_p" "member"."id"%TYPE,
jbe@244 24 "issue_id_p" "issue"."id"%TYPE )
jbe@244 25 RETURNS SETOF "delegation_chain_row"
jbe@244 26 LANGUAGE 'plpgsql' STABLE AS $$
jbe@244 27 DECLARE
jbe@244 28 "output_row" "delegation_chain_row";
jbe@244 29 "direct_voter_row" "direct_voter"%ROWTYPE;
jbe@244 30 "delegating_voter_row" "delegating_voter"%ROWTYPE;
jbe@244 31 BEGIN
jbe@244 32 "output_row"."index" := 0;
jbe@244 33 "output_row"."member_id" := "member_id_p";
jbe@244 34 "output_row"."member_valid" := TRUE;
jbe@244 35 "output_row"."participation" := FALSE;
jbe@244 36 "output_row"."overridden" := FALSE;
jbe@244 37 "output_row"."disabled_out" := FALSE;
jbe@244 38 LOOP
jbe@244 39 SELECT INTO "direct_voter_row" * FROM "direct_voter"
jbe@244 40 WHERE "issue_id" = "issue_id_p"
jbe@244 41 AND "member_id" = "output_row"."member_id";
jbe@244 42 IF "direct_voter_row"."member_id" NOTNULL THEN
jbe@244 43 "output_row"."participation" := TRUE;
jbe@244 44 "output_row"."scope_out" := NULL;
jbe@244 45 "output_row"."disabled_out" := NULL;
jbe@244 46 RETURN NEXT "output_row";
jbe@244 47 RETURN;
jbe@244 48 END IF;
jbe@244 49 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
jbe@244 50 WHERE "issue_id" = "issue_id_p"
jbe@244 51 AND "member_id" = "output_row"."member_id";
jbe@244 52 IF "delegating_voter_row"."member_id" ISNULL THEN
jbe@244 53 RETURN;
jbe@244 54 END IF;
jbe@244 55 "output_row"."scope_out" := "delegating_voter_row"."scope";
jbe@244 56 RETURN NEXT "output_row";
jbe@244 57 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
jbe@244 58 "output_row"."scope_in" := "output_row"."scope_out";
jbe@244 59 END LOOP;
jbe@244 60 END;
jbe@244 61 $$;
jbe@244 62
jbe@244 63 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
jbe@244 64 ( "member"."id"%TYPE,
jbe@244 65 "member"."id"%TYPE )
jbe@244 66 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
jbe@244 67
jbe@244 68 DROP FUNCTION "delegation_chain"
jbe@244 69 ( "member"."id"%TYPE,
jbe@244 70 "unit"."id"%TYPE,
jbe@244 71 "area"."id"%TYPE,
jbe@244 72 "issue"."id"%TYPE );
jbe@244 73
jbe@244 74 DROP FUNCTION "delegation_chain"
jbe@244 75 ( "member"."id"%TYPE,
jbe@244 76 "unit"."id"%TYPE,
jbe@244 77 "area"."id"%TYPE,
jbe@244 78 "issue"."id"%TYPE,
jbe@244 79 "member"."id"%TYPE );
jbe@244 80
jbe@244 81 CREATE FUNCTION "delegation_chain"
jbe@244 82 ( "member_id_p" "member"."id"%TYPE,
jbe@244 83 "unit_id_p" "unit"."id"%TYPE,
jbe@244 84 "area_id_p" "area"."id"%TYPE,
jbe@244 85 "issue_id_p" "issue"."id"%TYPE,
jbe@244 86 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@244 87 RETURNS SETOF "delegation_chain_row"
jbe@244 88 LANGUAGE 'plpgsql' STABLE AS $$
jbe@244 89 DECLARE
jbe@244 90 "scope_v" "delegation_scope";
jbe@244 91 "unit_id_v" "unit"."id"%TYPE;
jbe@244 92 "area_id_v" "area"."id"%TYPE;
jbe@244 93 "issue_row" "issue"%ROWTYPE;
jbe@244 94 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@244 95 "loop_member_id_v" "member"."id"%TYPE;
jbe@244 96 "output_row" "delegation_chain_row";
jbe@244 97 "output_rows" "delegation_chain_row"[];
jbe@244 98 "delegation_row" "delegation"%ROWTYPE;
jbe@244 99 "row_count" INT4;
jbe@244 100 "i" INT4;
jbe@244 101 "loop_v" BOOLEAN;
jbe@244 102 BEGIN
jbe@244 103 IF
jbe@244 104 "unit_id_p" NOTNULL AND
jbe@244 105 "area_id_p" ISNULL AND
jbe@244 106 "issue_id_p" ISNULL
jbe@244 107 THEN
jbe@244 108 "scope_v" := 'unit';
jbe@244 109 "unit_id_v" := "unit_id_p";
jbe@244 110 ELSIF
jbe@244 111 "unit_id_p" ISNULL AND
jbe@244 112 "area_id_p" NOTNULL AND
jbe@244 113 "issue_id_p" ISNULL
jbe@244 114 THEN
jbe@244 115 "scope_v" := 'area';
jbe@244 116 "area_id_v" := "area_id_p";
jbe@244 117 SELECT "unit_id" INTO "unit_id_v"
jbe@244 118 FROM "area" WHERE "id" = "area_id_v";
jbe@244 119 ELSIF
jbe@244 120 "unit_id_p" ISNULL AND
jbe@244 121 "area_id_p" ISNULL AND
jbe@244 122 "issue_id_p" NOTNULL
jbe@244 123 THEN
jbe@244 124 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@244 125 IF "issue_row"."id" ISNULL THEN
jbe@244 126 RETURN;
jbe@244 127 END IF;
jbe@244 128 IF "issue_row"."closed" NOTNULL THEN
jbe@244 129 IF "simulate_trustee_id_p" NOTNULL THEN
jbe@244 130 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@244 131 END IF;
jbe@244 132 FOR "output_row" IN
jbe@244 133 SELECT * FROM
jbe@244 134 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@244 135 LOOP
jbe@244 136 RETURN NEXT "output_row";
jbe@244 137 END LOOP;
jbe@244 138 RETURN;
jbe@244 139 END IF;
jbe@244 140 "scope_v" := 'issue';
jbe@244 141 SELECT "area_id" INTO "area_id_v"
jbe@244 142 FROM "issue" WHERE "id" = "issue_id_p";
jbe@244 143 SELECT "unit_id" INTO "unit_id_v"
jbe@244 144 FROM "area" WHERE "id" = "area_id_v";
jbe@244 145 ELSE
jbe@244 146 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@244 147 END IF;
jbe@244 148 "visited_member_ids" := '{}';
jbe@244 149 "loop_member_id_v" := NULL;
jbe@244 150 "output_rows" := '{}';
jbe@244 151 "output_row"."index" := 0;
jbe@244 152 "output_row"."member_id" := "member_id_p";
jbe@244 153 "output_row"."member_valid" := TRUE;
jbe@244 154 "output_row"."participation" := FALSE;
jbe@244 155 "output_row"."overridden" := FALSE;
jbe@244 156 "output_row"."disabled_out" := FALSE;
jbe@244 157 "output_row"."scope_out" := NULL;
jbe@244 158 LOOP
jbe@244 159 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@244 160 "loop_member_id_v" := "output_row"."member_id";
jbe@244 161 ELSE
jbe@244 162 "visited_member_ids" :=
jbe@244 163 "visited_member_ids" || "output_row"."member_id";
jbe@244 164 END IF;
jbe@244 165 IF "output_row"."participation" ISNULL THEN
jbe@244 166 "output_row"."overridden" := NULL;
jbe@244 167 ELSIF "output_row"."participation" THEN
jbe@244 168 "output_row"."overridden" := TRUE;
jbe@244 169 END IF;
jbe@244 170 "output_row"."scope_in" := "output_row"."scope_out";
jbe@244 171 IF EXISTS (
jbe@244 172 SELECT NULL FROM "member" JOIN "privilege"
jbe@244 173 ON "privilege"."member_id" = "member"."id"
jbe@244 174 AND "privilege"."unit_id" = "unit_id_v"
jbe@244 175 WHERE "id" = "output_row"."member_id"
jbe@244 176 AND "member"."active" AND "privilege"."voting_right"
jbe@244 177 ) THEN
jbe@244 178 IF "scope_v" = 'unit' THEN
jbe@244 179 SELECT * INTO "delegation_row" FROM "delegation"
jbe@244 180 WHERE "truster_id" = "output_row"."member_id"
jbe@244 181 AND "unit_id" = "unit_id_v";
jbe@244 182 ELSIF "scope_v" = 'area' THEN
jbe@244 183 "output_row"."participation" := EXISTS (
jbe@244 184 SELECT NULL FROM "membership"
jbe@244 185 WHERE "area_id" = "area_id_p"
jbe@244 186 AND "member_id" = "output_row"."member_id"
jbe@244 187 );
jbe@244 188 SELECT * INTO "delegation_row" FROM "delegation"
jbe@244 189 WHERE "truster_id" = "output_row"."member_id"
jbe@244 190 AND (
jbe@244 191 "unit_id" = "unit_id_v" OR
jbe@244 192 "area_id" = "area_id_v"
jbe@244 193 )
jbe@244 194 ORDER BY "scope" DESC;
jbe@244 195 ELSIF "scope_v" = 'issue' THEN
jbe@244 196 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@244 197 "output_row"."participation" := EXISTS (
jbe@244 198 SELECT NULL FROM "interest"
jbe@244 199 WHERE "issue_id" = "issue_id_p"
jbe@244 200 AND "member_id" = "output_row"."member_id"
jbe@244 201 );
jbe@244 202 ELSE
jbe@244 203 IF "output_row"."member_id" = "member_id_p" THEN
jbe@244 204 "output_row"."participation" := EXISTS (
jbe@244 205 SELECT NULL FROM "direct_voter"
jbe@244 206 WHERE "issue_id" = "issue_id_p"
jbe@244 207 AND "member_id" = "output_row"."member_id"
jbe@244 208 );
jbe@244 209 ELSE
jbe@244 210 "output_row"."participation" := NULL;
jbe@244 211 END IF;
jbe@244 212 END IF;
jbe@244 213 SELECT * INTO "delegation_row" FROM "delegation"
jbe@244 214 WHERE "truster_id" = "output_row"."member_id"
jbe@244 215 AND (
jbe@244 216 "unit_id" = "unit_id_v" OR
jbe@244 217 "area_id" = "area_id_v" OR
jbe@244 218 "issue_id" = "issue_id_p"
jbe@244 219 )
jbe@244 220 ORDER BY "scope" DESC;
jbe@244 221 END IF;
jbe@244 222 ELSE
jbe@244 223 "output_row"."member_valid" := FALSE;
jbe@244 224 "output_row"."participation" := FALSE;
jbe@244 225 "output_row"."scope_out" := NULL;
jbe@244 226 "delegation_row" := ROW(NULL);
jbe@244 227 END IF;
jbe@244 228 IF
jbe@244 229 "output_row"."member_id" = "member_id_p" AND
jbe@244 230 "simulate_trustee_id_p" NOTNULL
jbe@244 231 THEN
jbe@244 232 "output_row"."scope_out" := "scope_v";
jbe@244 233 "output_rows" := "output_rows" || "output_row";
jbe@244 234 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@244 235 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@244 236 "output_row"."scope_out" := "delegation_row"."scope";
jbe@244 237 "output_rows" := "output_rows" || "output_row";
jbe@244 238 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@244 239 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@244 240 "output_row"."scope_out" := "delegation_row"."scope";
jbe@244 241 "output_row"."disabled_out" := TRUE;
jbe@244 242 "output_rows" := "output_rows" || "output_row";
jbe@244 243 EXIT;
jbe@244 244 ELSE
jbe@244 245 "output_row"."scope_out" := NULL;
jbe@244 246 "output_rows" := "output_rows" || "output_row";
jbe@244 247 EXIT;
jbe@244 248 END IF;
jbe@244 249 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@244 250 "output_row"."index" := "output_row"."index" + 1;
jbe@244 251 END LOOP;
jbe@244 252 "row_count" := array_upper("output_rows", 1);
jbe@244 253 "i" := 1;
jbe@244 254 "loop_v" := FALSE;
jbe@244 255 LOOP
jbe@244 256 "output_row" := "output_rows"["i"];
jbe@244 257 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@244 258 IF "loop_v" THEN
jbe@244 259 IF "i" + 1 = "row_count" THEN
jbe@244 260 "output_row"."loop" := 'last';
jbe@244 261 ELSIF "i" = "row_count" THEN
jbe@244 262 "output_row"."loop" := 'repetition';
jbe@244 263 ELSE
jbe@244 264 "output_row"."loop" := 'intermediate';
jbe@244 265 END IF;
jbe@244 266 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@244 267 "output_row"."loop" := 'first';
jbe@244 268 "loop_v" := TRUE;
jbe@244 269 END IF;
jbe@244 270 IF "scope_v" = 'unit' THEN
jbe@244 271 "output_row"."participation" := NULL;
jbe@244 272 END IF;
jbe@244 273 RETURN NEXT "output_row";
jbe@244 274 "i" := "i" + 1;
jbe@244 275 END LOOP;
jbe@244 276 RETURN;
jbe@244 277 END;
jbe@244 278 $$;
jbe@244 279
jbe@244 280 COMMENT ON FUNCTION "delegation_chain"
jbe@244 281 ( "member"."id"%TYPE,
jbe@244 282 "unit"."id"%TYPE,
jbe@244 283 "area"."id"%TYPE,
jbe@244 284 "issue"."id"%TYPE,
jbe@244 285 "member"."id"%TYPE )
jbe@244 286 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
jbe@244 287
jbe@244 288 CREATE TYPE "delegation_info_loop_type" AS ENUM
jbe@244 289 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
jbe@244 290
jbe@244 291 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
jbe@244 292
jbe@244 293 CREATE TYPE "delegation_info_type" AS (
jbe@244 294 "own_participation" BOOLEAN,
jbe@244 295 "own_delegation_scope" "delegation_scope",
jbe@244 296 "first_trustee_id" INT4,
jbe@244 297 "first_trustee_participation" BOOLEAN,
jbe@244 298 "first_trustee_ellipsis" BOOLEAN,
jbe@244 299 "other_trustee_id" INT4,
jbe@244 300 "other_trustee_participation" BOOLEAN,
jbe@244 301 "other_trustee_ellipsis" BOOLEAN,
jbe@244 302 "delegation_loop" "delegation_info_loop_type");
jbe@244 303
jbe@244 304 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
jbe@244 305
jbe@244 306 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
jbe@244 307 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
jbe@244 308 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
jbe@244 309 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
jbe@244 310 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
jbe@244 311 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
jbe@244 312 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
jbe@244 313 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
jbe@244 314 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
jbe@244 315
jbe@244 316 CREATE FUNCTION "delegation_info"
jbe@244 317 ( "member_id_p" "member"."id"%TYPE,
jbe@244 318 "unit_id_p" "unit"."id"%TYPE,
jbe@244 319 "area_id_p" "area"."id"%TYPE,
jbe@244 320 "issue_id_p" "issue"."id"%TYPE,
jbe@244 321 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@244 322 RETURNS "delegation_info_type"
jbe@244 323 LANGUAGE 'plpgsql' STABLE AS $$
jbe@244 324 DECLARE
jbe@244 325 "current_row" "delegation_chain_row";
jbe@244 326 "result" "delegation_info_type";
jbe@244 327 BEGIN
jbe@244 328 "result"."own_participation" := FALSE;
jbe@244 329 FOR "current_row" IN
jbe@244 330 SELECT * FROM "delegation_chain"(
jbe@244 331 "member_id_p",
jbe@244 332 "unit_id_p", "area_id_p", "issue_id_p",
jbe@244 333 "simulate_trustee_id_p")
jbe@244 334 LOOP
jbe@244 335 IF "current_row"."member_id" = "member_id_p" THEN
jbe@244 336 "result"."own_participation" := "current_row"."participation";
jbe@244 337 "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@244 338 IF "current_row"."loop" = 'first' THEN
jbe@244 339 "result"."delegation_loop" := 'own';
jbe@244 340 END IF;
jbe@244 341 ELSIF
jbe@244 342 "current_row"."member_valid" AND
jbe@244 343 ( "current_row"."loop" ISNULL OR
jbe@244 344 "current_row"."loop" != 'repetition' )
jbe@244 345 THEN
jbe@244 346 IF "result"."first_trustee_id" ISNULL THEN
jbe@244 347 "result"."first_trustee_id" := "current_row"."member_id";
jbe@244 348 "result"."first_trustee_participation" := "current_row"."participation";
jbe@244 349 "result"."first_trustee_ellipsis" := FALSE;
jbe@244 350 IF "current_row"."loop" = 'first' THEN
jbe@244 351 "result"."delegation_loop" := 'first';
jbe@244 352 END IF;
jbe@244 353 ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@247 354 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@244 355 "result"."other_trustee_id" := "current_row"."member_id";
jbe@244 356 "result"."other_trustee_participation" := TRUE;
jbe@244 357 "result"."other_trustee_ellipsis" := FALSE;
jbe@244 358 IF "current_row"."loop" = 'first' THEN
jbe@244 359 "result"."delegation_loop" := 'other';
jbe@244 360 END IF;
jbe@244 361 ELSE
jbe@244 362 "result"."first_trustee_ellipsis" := TRUE;
jbe@244 363 IF "current_row"."loop" = 'first' THEN
jbe@244 364 "result"."delegation_loop" := 'first_ellipsis';
jbe@244 365 END IF;
jbe@244 366 END IF;
jbe@244 367 ELSE
jbe@244 368 "result"."other_trustee_ellipsis" := TRUE;
jbe@244 369 IF "current_row"."loop" = 'first' THEN
jbe@244 370 "result"."delegation_loop" := 'other_ellipsis';
jbe@244 371 END IF;
jbe@244 372 END IF;
jbe@244 373 END IF;
jbe@244 374 END LOOP;
jbe@244 375 RETURN "result";
jbe@244 376 END;
jbe@244 377 $$;
jbe@244 378
jbe@244 379 COMMENT ON FUNCTION "delegation_info"
jbe@244 380 ( "member"."id"%TYPE,
jbe@244 381 "unit"."id"%TYPE,
jbe@244 382 "area"."id"%TYPE,
jbe@244 383 "issue"."id"%TYPE,
jbe@244 384 "member"."id"%TYPE )
jbe@244 385 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@244 386
jbe@244 387 COMMIT;

Impressum / About Us