liquid_feedback_core

annotate update/core-update.v2.0.10-v2.0.11.sql @ 580:78f6833f5f19

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

Impressum / About Us