liquid_feedback_core
annotate update/core-update.v2.0.9-v2.0.10.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 | 389200fd973d |
children |
rev | line source |
---|---|
jbe@253 | 1 BEGIN; |
jbe@253 | 2 |
jbe@253 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@253 | 4 SELECT * FROM (VALUES ('2.0.10', 2, 0, 10)) |
jbe@253 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@253 | 6 |
jbe@253 | 7 DROP FUNCTION "delegation_info" |
jbe@253 | 8 ( "member"."id"%TYPE, |
jbe@253 | 9 "unit"."id"%TYPE, |
jbe@253 | 10 "area"."id"%TYPE, |
jbe@253 | 11 "issue"."id"%TYPE, |
jbe@253 | 12 "member"."id"%TYPE ); |
jbe@253 | 13 |
jbe@253 | 14 DROP TYPE "delegation_info_type"; |
jbe@253 | 15 |
jbe@253 | 16 |
jbe@253 | 17 CREATE TYPE "delegation_info_type" AS ( |
jbe@253 | 18 "own_participation" BOOLEAN, |
jbe@253 | 19 "own_delegation_scope" "delegation_scope", |
jbe@253 | 20 "first_trustee_id" INT4, |
jbe@253 | 21 "first_trustee_participation" BOOLEAN, |
jbe@253 | 22 "first_trustee_ellipsis" BOOLEAN, |
jbe@253 | 23 "other_trustee_id" INT4, |
jbe@253 | 24 "other_trustee_participation" BOOLEAN, |
jbe@253 | 25 "other_trustee_ellipsis" BOOLEAN, |
jbe@253 | 26 "delegation_loop" "delegation_info_loop_type", |
jbe@253 | 27 "participating_member_id" INT4 ); |
jbe@253 | 28 |
jbe@253 | 29 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@253 | 30 |
jbe@253 | 31 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating'; |
jbe@253 | 32 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member'; |
jbe@253 | 33 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member'; |
jbe@253 | 34 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating'; |
jbe@253 | 35 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"'; |
jbe@253 | 36 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)'; |
jbe@253 | 37 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@253 | 38 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; |
jbe@253 | 39 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@253 | 40 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain'; |
jbe@253 | 41 |
jbe@253 | 42 |
jbe@253 | 43 CREATE FUNCTION "delegation_info" |
jbe@253 | 44 ( "member_id_p" "member"."id"%TYPE, |
jbe@253 | 45 "unit_id_p" "unit"."id"%TYPE, |
jbe@253 | 46 "area_id_p" "area"."id"%TYPE, |
jbe@253 | 47 "issue_id_p" "issue"."id"%TYPE, |
jbe@253 | 48 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) |
jbe@253 | 49 RETURNS "delegation_info_type" |
jbe@253 | 50 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@253 | 51 DECLARE |
jbe@253 | 52 "current_row" "delegation_chain_row"; |
jbe@253 | 53 "result" "delegation_info_type"; |
jbe@253 | 54 BEGIN |
jbe@253 | 55 "result"."own_participation" := FALSE; |
jbe@253 | 56 FOR "current_row" IN |
jbe@253 | 57 SELECT * FROM "delegation_chain"( |
jbe@253 | 58 "member_id_p", |
jbe@253 | 59 "unit_id_p", "area_id_p", "issue_id_p", |
jbe@253 | 60 "simulate_trustee_id_p") |
jbe@253 | 61 LOOP |
jbe@253 | 62 IF |
jbe@253 | 63 "result"."participating_member_id" ISNULL AND |
jbe@253 | 64 "current_row"."participation" |
jbe@253 | 65 THEN |
jbe@253 | 66 "result"."participating_member_id" := "current_row"."member_id"; |
jbe@253 | 67 END IF; |
jbe@253 | 68 IF "current_row"."member_id" = "member_id_p" THEN |
jbe@253 | 69 "result"."own_participation" := "current_row"."participation"; |
jbe@253 | 70 "result"."own_delegation_scope" := "current_row"."scope_out"; |
jbe@253 | 71 IF "current_row"."loop" = 'first' THEN |
jbe@253 | 72 "result"."delegation_loop" := 'own'; |
jbe@253 | 73 END IF; |
jbe@253 | 74 ELSIF |
jbe@253 | 75 "current_row"."member_valid" AND |
jbe@253 | 76 ( "current_row"."loop" ISNULL OR |
jbe@253 | 77 "current_row"."loop" != 'repetition' ) |
jbe@253 | 78 THEN |
jbe@253 | 79 IF "result"."first_trustee_id" ISNULL THEN |
jbe@253 | 80 "result"."first_trustee_id" := "current_row"."member_id"; |
jbe@253 | 81 "result"."first_trustee_participation" := "current_row"."participation"; |
jbe@253 | 82 "result"."first_trustee_ellipsis" := FALSE; |
jbe@253 | 83 IF "current_row"."loop" = 'first' THEN |
jbe@253 | 84 "result"."delegation_loop" := 'first'; |
jbe@253 | 85 END IF; |
jbe@253 | 86 ELSIF "result"."other_trustee_id" ISNULL THEN |
jbe@253 | 87 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN |
jbe@253 | 88 "result"."other_trustee_id" := "current_row"."member_id"; |
jbe@253 | 89 "result"."other_trustee_participation" := TRUE; |
jbe@253 | 90 "result"."other_trustee_ellipsis" := FALSE; |
jbe@253 | 91 IF "current_row"."loop" = 'first' THEN |
jbe@253 | 92 "result"."delegation_loop" := 'other'; |
jbe@253 | 93 END IF; |
jbe@253 | 94 ELSE |
jbe@253 | 95 "result"."first_trustee_ellipsis" := TRUE; |
jbe@253 | 96 IF "current_row"."loop" = 'first' THEN |
jbe@253 | 97 "result"."delegation_loop" := 'first_ellipsis'; |
jbe@253 | 98 END IF; |
jbe@253 | 99 END IF; |
jbe@253 | 100 ELSE |
jbe@253 | 101 "result"."other_trustee_ellipsis" := TRUE; |
jbe@253 | 102 IF "current_row"."loop" = 'first' THEN |
jbe@253 | 103 "result"."delegation_loop" := 'other_ellipsis'; |
jbe@253 | 104 END IF; |
jbe@253 | 105 END IF; |
jbe@253 | 106 END IF; |
jbe@253 | 107 END LOOP; |
jbe@253 | 108 RETURN "result"; |
jbe@253 | 109 END; |
jbe@253 | 110 $$; |
jbe@253 | 111 |
jbe@253 | 112 COMMENT ON FUNCTION "delegation_info" |
jbe@253 | 113 ( "member"."id"%TYPE, |
jbe@253 | 114 "unit"."id"%TYPE, |
jbe@253 | 115 "area"."id"%TYPE, |
jbe@253 | 116 "issue"."id"%TYPE, |
jbe@253 | 117 "member"."id"%TYPE ) |
jbe@253 | 118 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; |
jbe@253 | 119 |
jbe@253 | 120 |
jbe@253 | 121 COMMIT; |