liquid_feedback_core
changeset 86:3a86196ed0bf
Allow to disable delegations (trustee_id = NULL) and to ignore issues
author | jbe |
---|---|
date | Mon Oct 18 19:11:56 2010 +0200 (2010-10-18) |
parents | 1a412ec5e14e |
children | d7eadecc7b05 |
files | core.sql update/core-update.v1.2.8-v1.2.9.sql update/core-update.v1.2.8-v1.3.0.sql |
line diff
1.1 --- a/core.sql Sun Oct 17 01:44:13 2010 +0200 1.2 +++ b/core.sql Mon Oct 18 19:11:56 2010 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 1.8 + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -389,7 +389,7 @@ 1.13 1.14 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.15 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.16 -COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; 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.'; 1.17 +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.'; 1.18 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.19 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.20 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.21 @@ -579,14 +579,14 @@ 1.22 1.23 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; 1.24 1.25 -COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence'; 1.26 +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.'; 1.27 1.28 1.29 CREATE TABLE "interest" ( 1.30 PRIMARY KEY ("issue_id", "member_id"), 1.31 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.32 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.33 - "autoreject" BOOLEAN NOT NULL, 1.34 + "autoreject" BOOLEAN, 1.35 "voting_requested" BOOLEAN ); 1.36 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); 1.37 1.38 @@ -596,6 +596,24 @@ 1.39 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply'; 1.40 1.41 1.42 +CREATE TABLE "ignored_issue" ( 1.43 + PRIMARY KEY ("issue_id", "member_id"), 1.44 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.45 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 + "new" BOOLEAN NOT NULL DEFAULT FALSE, 1.47 + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 1.48 + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 1.49 + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 1.50 +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 1.51 + 1.52 +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 1.53 + 1.54 +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; 1.55 +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; 1.56 +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; 1.57 +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; 1.58 + 1.59 + 1.60 CREATE TABLE "initiator" ( 1.61 PRIMARY KEY ("initiative_id", "member_id"), 1.62 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.63 @@ -649,11 +667,13 @@ 1.64 CREATE TABLE "delegation" ( 1.65 "id" SERIAL8 PRIMARY KEY, 1.66 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.67 - "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.68 + "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.69 "scope" "delegation_scope" NOT NULL, 1.70 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.71 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.72 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"), 1.73 + CONSTRAINT "no_global_delegation_to_null" 1.74 + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'), 1.75 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK ( 1.76 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 1.77 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 1.78 @@ -1148,31 +1168,6 @@ 1.79 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.'; 1.80 1.81 1.82 -CREATE FUNCTION "copy_autoreject_trigger"() 1.83 - RETURNS TRIGGER 1.84 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.85 - BEGIN 1.86 - IF NEW."autoreject" ISNULL THEN 1.87 - SELECT "membership"."autoreject" INTO NEW."autoreject" 1.88 - FROM "issue" JOIN "membership" 1.89 - ON "issue"."area_id" = "membership"."area_id" 1.90 - WHERE "issue"."id" = NEW."issue_id" 1.91 - AND "membership"."member_id" = NEW."member_id"; 1.92 - END IF; 1.93 - IF NEW."autoreject" ISNULL THEN 1.94 - NEW."autoreject" := FALSE; 1.95 - END IF; 1.96 - RETURN NEW; 1.97 - END; 1.98 - $$; 1.99 - 1.100 -CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest" 1.101 - FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"(); 1.102 - 1.103 -COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"'; 1.104 -COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent'; 1.105 - 1.106 - 1.107 CREATE FUNCTION "supporter_default_for_draft_id_trigger"() 1.108 RETURNS TRIGGER 1.109 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.110 @@ -1263,7 +1258,7 @@ 1.111 JOIN "member" ON "delegation"."truster_id" = "member"."id" 1.112 WHERE "member"."active" = TRUE; 1.113 1.114 -COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; 1.115 +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'; 1.116 1.117 1.118 CREATE VIEW "global_delegation" AS 1.119 @@ -1288,7 +1283,7 @@ 1.120 "delegation"."truster_id", 1.121 "delegation"."scope" DESC; 1.122 1.123 -COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; 1.124 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 1.125 1.126 1.127 CREATE VIEW "issue_delegation" AS 1.128 @@ -1307,7 +1302,7 @@ 1.129 "delegation"."truster_id", 1.130 "delegation"."scope" DESC; 1.131 1.132 -COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; 1.133 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 1.134 1.135 1.136 CREATE FUNCTION "membership_weight_with_skipping" 1.137 @@ -1702,6 +1697,7 @@ 1.138 "overridden" BOOLEAN, 1.139 "scope_in" "delegation_scope", 1.140 "scope_out" "delegation_scope", 1.141 + "disabled_out" BOOLEAN, 1.142 "loop" "delegation_chain_loop_tag" ); 1.143 1.144 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 1.145 @@ -1711,6 +1707,7 @@ 1.146 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 1.147 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 1.148 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 1.149 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 1.150 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 1.151 1.152 1.153 @@ -1741,6 +1738,7 @@ 1.154 "output_row"."member_active" := TRUE; 1.155 "output_row"."participation" := FALSE; 1.156 "output_row"."overridden" := FALSE; 1.157 + "output_row"."disabled_out" := FALSE; 1.158 "output_row"."scope_out" := NULL; 1.159 LOOP 1.160 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.161 @@ -1808,6 +1806,11 @@ 1.162 "output_row"."scope_out" := "delegation_row"."scope"; 1.163 "output_rows" := "output_rows" || "output_row"; 1.164 "output_row"."member_id" := "delegation_row"."trustee_id"; 1.165 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.166 + "output_row"."scope_out" := "delegation_row"."scope"; 1.167 + "output_row"."disabled_out" := TRUE; 1.168 + "output_rows" := "output_rows" || "output_row"; 1.169 + EXIT; 1.170 ELSE 1.171 "output_row"."scope_out" := NULL; 1.172 "output_rows" := "output_rows" || "output_row"; 1.173 @@ -1821,7 +1824,7 @@ 1.174 "loop_v" := FALSE; 1.175 LOOP 1.176 "output_row" := "output_rows"["i"]; 1.177 - EXIT WHEN "output_row"."member_id" ISNULL; 1.178 + EXIT WHEN "output_row" ISNULL; 1.179 IF "loop_v" THEN 1.180 IF "i" + 1 = "row_count" THEN 1.181 "output_row"."loop" := 'last'; 1.182 @@ -3379,6 +3382,7 @@ 1.183 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.184 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.185 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.186 + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; 1.187 DELETE FROM "direct_voter" USING "issue" 1.188 WHERE "direct_voter"."issue_id" = "issue"."id" 1.189 AND "issue"."closed" ISNULL 1.190 @@ -3431,6 +3435,7 @@ 1.191 DELETE FROM "issue_setting"; 1.192 DELETE FROM "initiative_setting"; 1.193 DELETE FROM "suggestion_setting"; 1.194 + DELETE FROM "ignored_voting"; 1.195 DELETE FROM "direct_voter" USING "issue" 1.196 WHERE "direct_voter"."issue_id" = "issue"."id" 1.197 AND "issue"."closed" ISNULL;
2.1 --- a/update/core-update.v1.2.8-v1.2.9.sql Sun Oct 17 01:44:13 2010 +0200 2.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 2.3 @@ -1,12 +0,0 @@ 2.4 -BEGIN; 2.5 - 2.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 2.8 - AS "subquery"("string", "major", "minor", "revision"); 2.9 - 2.10 -ALTER TABLE "supporter" ADD COLUMN 2.11 - "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 2.12 - 2.13 -COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 2.14 - 2.15 -COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v1.2.8-v1.3.0.sql Mon Oct 18 19:11:56 2010 +0200 3.3 @@ -0,0 +1,384 @@ 3.4 +BEGIN; 3.5 + 3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) 3.8 + AS "subquery"("string", "major", "minor", "revision"); 3.9 + 3.10 +ALTER TABLE "supporter" ADD COLUMN 3.11 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 3.12 + 3.13 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 3.14 + 3.15 + 3.16 +-- update comment for column "fully_frozen" of table "issue" 3.17 + 3.18 +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.'; 3.19 + 3.20 + 3.21 +-- update comment for column "autoreject" of table "membership" 3.22 + 3.23 +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.'; 3.24 + 3.25 + 3.26 +-- allow column "autoreject" of table "interest" to be NULL 3.27 +-- (thus defaulting to "membership") 3.28 + 3.29 +ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL; 3.30 + 3.31 + 3.32 +-- new table "ignored_issue" to allow members to ignore particular issues in certain states 3.33 + 3.34 +CREATE TABLE "ignored_issue" ( 3.35 + PRIMARY KEY ("issue_id", "member_id"), 3.36 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.37 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.38 + "new" BOOLEAN NOT NULL DEFAULT FALSE, 3.39 + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 3.40 + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 3.41 + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 3.42 +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 3.43 + 3.44 +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 3.45 + 3.46 +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; 3.47 +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; 3.48 +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; 3.49 +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; 3.50 + 3.51 + 3.52 +-- allow area and issue delegations with trustee_id set to NULL 3.53 +-- (indicating that global or area delegation is void for that area or issue) 3.54 + 3.55 +ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL; 3.56 + 3.57 +ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null" 3.58 + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'); 3.59 + 3.60 + 3.61 +-- disable and delete "copy_autoreject" trigger on table "interest" 3.62 + 3.63 +DROP TRIGGER "copy_autoreject" ON "interest"; 3.64 +DROP FUNCTION "copy_autoreject_trigger"(); 3.65 + 3.66 + 3.67 +-- update comments on delegation views 3.68 + 3.69 +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'; 3.70 + 3.71 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 3.72 + 3.73 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 3.74 + 3.75 + 3.76 +-- 3.77 + 3.78 +DROP FUNCTION "delegation_chain" 3.79 + ( "member"."id"%TYPE, 3.80 + "area"."id"%TYPE, 3.81 + "issue"."id"%TYPE ); 3.82 + 3.83 +DROP FUNCTION "delegation_chain" 3.84 + ( "member"."id"%TYPE, 3.85 + "area"."id"%TYPE, 3.86 + "issue"."id"%TYPE, 3.87 + "member"."id"%TYPE ); 3.88 + 3.89 +DROP TYPE "delegation_chain_row"; 3.90 + 3.91 +CREATE TYPE "delegation_chain_row" AS ( 3.92 + "index" INT4, 3.93 + "member_id" INT4, 3.94 + "member_active" BOOLEAN, 3.95 + "participation" BOOLEAN, 3.96 + "overridden" BOOLEAN, 3.97 + "scope_in" "delegation_scope", 3.98 + "scope_out" "delegation_scope", 3.99 + "disabled_out" BOOLEAN, 3.100 + "loop" "delegation_chain_loop_tag" ); 3.101 + 3.102 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 3.103 + 3.104 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 3.105 +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'; 3.106 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 3.107 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 3.108 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 3.109 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 3.110 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 3.111 + 3.112 + 3.113 +CREATE FUNCTION "delegation_chain" 3.114 + ( "member_id_p" "member"."id"%TYPE, 3.115 + "area_id_p" "area"."id"%TYPE, 3.116 + "issue_id_p" "issue"."id"%TYPE, 3.117 + "simulate_trustee_id_p" "member"."id"%TYPE ) 3.118 + RETURNS SETOF "delegation_chain_row" 3.119 + LANGUAGE 'plpgsql' STABLE AS $$ 3.120 + DECLARE 3.121 + "issue_row" "issue"%ROWTYPE; 3.122 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 3.123 + "loop_member_id_v" "member"."id"%TYPE; 3.124 + "output_row" "delegation_chain_row"; 3.125 + "output_rows" "delegation_chain_row"[]; 3.126 + "delegation_row" "delegation"%ROWTYPE; 3.127 + "row_count" INT4; 3.128 + "i" INT4; 3.129 + "loop_v" BOOLEAN; 3.130 + BEGIN 3.131 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.132 + "visited_member_ids" := '{}'; 3.133 + "loop_member_id_v" := NULL; 3.134 + "output_rows" := '{}'; 3.135 + "output_row"."index" := 0; 3.136 + "output_row"."member_id" := "member_id_p"; 3.137 + "output_row"."member_active" := TRUE; 3.138 + "output_row"."participation" := FALSE; 3.139 + "output_row"."overridden" := FALSE; 3.140 + "output_row"."disabled_out" := FALSE; 3.141 + "output_row"."scope_out" := NULL; 3.142 + LOOP 3.143 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 3.144 + "loop_member_id_v" := "output_row"."member_id"; 3.145 + ELSE 3.146 + "visited_member_ids" := 3.147 + "visited_member_ids" || "output_row"."member_id"; 3.148 + END IF; 3.149 + IF "output_row"."participation" THEN 3.150 + "output_row"."overridden" := TRUE; 3.151 + END IF; 3.152 + "output_row"."scope_in" := "output_row"."scope_out"; 3.153 + IF EXISTS ( 3.154 + SELECT NULL FROM "member" 3.155 + WHERE "id" = "output_row"."member_id" AND "active" 3.156 + ) THEN 3.157 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 3.158 + SELECT * INTO "delegation_row" FROM "delegation" 3.159 + WHERE "truster_id" = "output_row"."member_id" 3.160 + AND "scope" = 'global'; 3.161 + ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 3.162 + "output_row"."participation" := EXISTS ( 3.163 + SELECT NULL FROM "membership" 3.164 + WHERE "area_id" = "area_id_p" 3.165 + AND "member_id" = "output_row"."member_id" 3.166 + ); 3.167 + SELECT * INTO "delegation_row" FROM "delegation" 3.168 + WHERE "truster_id" = "output_row"."member_id" 3.169 + AND ("scope" = 'global' OR "area_id" = "area_id_p") 3.170 + ORDER BY "scope" DESC; 3.171 + ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 3.172 + "output_row"."participation" := EXISTS ( 3.173 + SELECT NULL FROM "interest" 3.174 + WHERE "issue_id" = "issue_id_p" 3.175 + AND "member_id" = "output_row"."member_id" 3.176 + ); 3.177 + SELECT * INTO "delegation_row" FROM "delegation" 3.178 + WHERE "truster_id" = "output_row"."member_id" 3.179 + AND ("scope" = 'global' OR 3.180 + "area_id" = "issue_row"."area_id" OR 3.181 + "issue_id" = "issue_id_p" 3.182 + ) 3.183 + ORDER BY "scope" DESC; 3.184 + ELSE 3.185 + RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 3.186 + END IF; 3.187 + ELSE 3.188 + "output_row"."member_active" := FALSE; 3.189 + "output_row"."participation" := FALSE; 3.190 + "output_row"."scope_out" := NULL; 3.191 + "delegation_row" := ROW(NULL); 3.192 + END IF; 3.193 + IF 3.194 + "output_row"."member_id" = "member_id_p" AND 3.195 + "simulate_trustee_id_p" NOTNULL 3.196 + THEN 3.197 + "output_row"."scope_out" := CASE 3.198 + WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' 3.199 + WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' 3.200 + WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' 3.201 + END; 3.202 + "output_rows" := "output_rows" || "output_row"; 3.203 + "output_row"."member_id" := "simulate_trustee_id_p"; 3.204 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 3.205 + "output_row"."scope_out" := "delegation_row"."scope"; 3.206 + "output_rows" := "output_rows" || "output_row"; 3.207 + "output_row"."member_id" := "delegation_row"."trustee_id"; 3.208 + ELSIF "delegation_row"."scope" NOTNULL THEN 3.209 + "output_row"."scope_out" := "delegation_row"."scope"; 3.210 + "output_row"."disabled_out" := TRUE; 3.211 + "output_rows" := "output_rows" || "output_row"; 3.212 + EXIT; 3.213 + ELSE 3.214 + "output_row"."scope_out" := NULL; 3.215 + "output_rows" := "output_rows" || "output_row"; 3.216 + EXIT; 3.217 + END IF; 3.218 + EXIT WHEN "loop_member_id_v" NOTNULL; 3.219 + "output_row"."index" := "output_row"."index" + 1; 3.220 + END LOOP; 3.221 + "row_count" := array_upper("output_rows", 1); 3.222 + "i" := 1; 3.223 + "loop_v" := FALSE; 3.224 + LOOP 3.225 + "output_row" := "output_rows"["i"]; 3.226 + EXIT WHEN "output_row" ISNULL; 3.227 + IF "loop_v" THEN 3.228 + IF "i" + 1 = "row_count" THEN 3.229 + "output_row"."loop" := 'last'; 3.230 + ELSIF "i" = "row_count" THEN 3.231 + "output_row"."loop" := 'repetition'; 3.232 + ELSE 3.233 + "output_row"."loop" := 'intermediate'; 3.234 + END IF; 3.235 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 3.236 + "output_row"."loop" := 'first'; 3.237 + "loop_v" := TRUE; 3.238 + END IF; 3.239 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 3.240 + "output_row"."participation" := NULL; 3.241 + END IF; 3.242 + RETURN NEXT "output_row"; 3.243 + "i" := "i" + 1; 3.244 + END LOOP; 3.245 + RETURN; 3.246 + END; 3.247 + $$; 3.248 + 3.249 +COMMENT ON FUNCTION "delegation_chain" 3.250 + ( "member"."id"%TYPE, 3.251 + "area"."id"%TYPE, 3.252 + "issue"."id"%TYPE, 3.253 + "member"."id"%TYPE ) 3.254 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 3.255 + 3.256 +CREATE FUNCTION "delegation_chain" 3.257 + ( "member_id_p" "member"."id"%TYPE, 3.258 + "area_id_p" "area"."id"%TYPE, 3.259 + "issue_id_p" "issue"."id"%TYPE ) 3.260 + RETURNS SETOF "delegation_chain_row" 3.261 + LANGUAGE 'plpgsql' STABLE AS $$ 3.262 + DECLARE 3.263 + "result_row" "delegation_chain_row"; 3.264 + BEGIN 3.265 + FOR "result_row" IN 3.266 + SELECT * FROM "delegation_chain"( 3.267 + "member_id_p", "area_id_p", "issue_id_p", NULL 3.268 + ) 3.269 + LOOP 3.270 + RETURN NEXT "result_row"; 3.271 + END LOOP; 3.272 + RETURN; 3.273 + END; 3.274 + $$; 3.275 + 3.276 +COMMENT ON FUNCTION "delegation_chain" 3.277 + ( "member"."id"%TYPE, 3.278 + "area"."id"%TYPE, 3.279 + "issue"."id"%TYPE ) 3.280 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 3.281 + 3.282 + 3.283 +-- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions 3.284 + 3.285 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 3.286 + RETURNS VOID 3.287 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.288 + BEGIN 3.289 + UPDATE "member" SET 3.290 + "last_login" = NULL, 3.291 + "login" = NULL, 3.292 + "password" = NULL, 3.293 + "active" = FALSE, 3.294 + "notify_email" = NULL, 3.295 + "notify_email_unconfirmed" = NULL, 3.296 + "notify_email_secret" = NULL, 3.297 + "notify_email_secret_expiry" = NULL, 3.298 + "notify_email_lock_expiry" = NULL, 3.299 + "password_reset_secret" = NULL, 3.300 + "password_reset_secret_expiry" = NULL, 3.301 + "organizational_unit" = NULL, 3.302 + "internal_posts" = NULL, 3.303 + "realname" = NULL, 3.304 + "birthday" = NULL, 3.305 + "address" = NULL, 3.306 + "email" = NULL, 3.307 + "xmpp_address" = NULL, 3.308 + "website" = NULL, 3.309 + "phone" = NULL, 3.310 + "mobile_phone" = NULL, 3.311 + "profession" = NULL, 3.312 + "external_memberships" = NULL, 3.313 + "external_posts" = NULL, 3.314 + "statement" = NULL 3.315 + WHERE "id" = "member_id_p"; 3.316 + -- "text_search_data" is updated by triggers 3.317 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 3.318 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 3.319 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 3.320 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 3.321 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 3.322 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 3.323 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 3.324 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 3.325 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 3.326 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 3.327 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 3.328 + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; 3.329 + DELETE FROM "direct_voter" USING "issue" 3.330 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.331 + AND "issue"."closed" ISNULL 3.332 + AND "member_id" = "member_id_p"; 3.333 + RETURN; 3.334 + END; 3.335 + $$; 3.336 + 3.337 +CREATE OR REPLACE FUNCTION "delete_private_data"() 3.338 + RETURNS VOID 3.339 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.340 + BEGIN 3.341 + UPDATE "member" SET 3.342 + "last_login" = NULL, 3.343 + "login" = NULL, 3.344 + "password" = NULL, 3.345 + "notify_email" = NULL, 3.346 + "notify_email_unconfirmed" = NULL, 3.347 + "notify_email_secret" = NULL, 3.348 + "notify_email_secret_expiry" = NULL, 3.349 + "notify_email_lock_expiry" = NULL, 3.350 + "password_reset_secret" = NULL, 3.351 + "password_reset_secret_expiry" = NULL, 3.352 + "organizational_unit" = NULL, 3.353 + "internal_posts" = NULL, 3.354 + "realname" = NULL, 3.355 + "birthday" = NULL, 3.356 + "address" = NULL, 3.357 + "email" = NULL, 3.358 + "xmpp_address" = NULL, 3.359 + "website" = NULL, 3.360 + "phone" = NULL, 3.361 + "mobile_phone" = NULL, 3.362 + "profession" = NULL, 3.363 + "external_memberships" = NULL, 3.364 + "external_posts" = NULL, 3.365 + "statement" = NULL; 3.366 + -- "text_search_data" is updated by triggers 3.367 + DELETE FROM "invite_code"; 3.368 + DELETE FROM "setting"; 3.369 + DELETE FROM "setting_map"; 3.370 + DELETE FROM "member_relation_setting"; 3.371 + DELETE FROM "member_image"; 3.372 + DELETE FROM "contact"; 3.373 + DELETE FROM "session"; 3.374 + DELETE FROM "area_setting"; 3.375 + DELETE FROM "issue_setting"; 3.376 + DELETE FROM "initiative_setting"; 3.377 + DELETE FROM "suggestion_setting"; 3.378 + DELETE FROM "ignored_voting"; 3.379 + DELETE FROM "direct_voter" USING "issue" 3.380 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.381 + AND "issue"."closed" ISNULL; 3.382 + RETURN; 3.383 + END; 3.384 + $$; 3.385 + 3.386 + 3.387 +COMMIT;