liquid_feedback_core
annotate update/core-update.v2.0.10-v2.0.11.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
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; |