liquid_feedback_core
changeset 89:d7eadecc7b05
Merged planned changes for version 1.3.0 with latest bugfix for version 1.2.9
author | jbe |
---|---|
date | Fri Oct 29 18:36:36 2010 +0200 (2010-10-29) |
parents | dfa00eaa5081 3a86196ed0bf |
children | 1934f9b4f803 |
files | .hgtags core.sql update/core-update.v1.2.8-v1.3.0.sql update/core-update.v1.2.9-v1.3.0.sql |
line diff
1.1 --- a/.hgtags Tue Oct 26 22:25:47 2010 +0200 1.2 +++ b/.hgtags Fri Oct 29 18:36:36 2010 +0200 1.3 @@ -37,4 +37,5 @@ 1.4 72e5356b5454443864423d19e653bc4ae020373d v1.2.5 1.5 5745db0b1a34d52ee903f6841768cf99471ad155 v1.2.6 1.6 dcaa1525c38812eae0a0615b1217ac3bd9c63fa2 v1.2.7 1.7 +f77c0f3d443c79d7c70e3f5075b31a9afe6ff4cb v1.2.8 1.8 e588fdf1676e1e069e9c84ba791465482ca2720c v1.2.9
2.1 --- a/core.sql Tue Oct 26 22:25:47 2010 +0200 2.2 +++ b/core.sql Fri Oct 29 18:36:36 2010 +0200 2.3 @@ -6,7 +6,7 @@ 2.4 BEGIN; 2.5 2.6 CREATE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 2.8 + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) 2.9 AS "subquery"("string", "major", "minor", "revision"); 2.10 2.11 2.12 @@ -389,7 +389,7 @@ 2.13 2.14 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 2.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.'; 2.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.'; 2.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.'; 2.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.'; 2.19 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 2.20 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 2.21 @@ -579,14 +579,14 @@ 2.22 2.23 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; 2.24 2.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'; 2.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.'; 2.27 2.28 2.29 CREATE TABLE "interest" ( 2.30 PRIMARY KEY ("issue_id", "member_id"), 2.31 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.32 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.33 - "autoreject" BOOLEAN NOT NULL, 2.34 + "autoreject" BOOLEAN, 2.35 "voting_requested" BOOLEAN ); 2.36 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); 2.37 2.38 @@ -596,6 +596,24 @@ 2.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'; 2.40 2.41 2.42 +CREATE TABLE "ignored_issue" ( 2.43 + PRIMARY KEY ("issue_id", "member_id"), 2.44 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.45 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.46 + "new" BOOLEAN NOT NULL DEFAULT FALSE, 2.47 + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 2.48 + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 2.49 + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 2.50 +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 2.51 + 2.52 +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 2.53 + 2.54 +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; 2.55 +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; 2.56 +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; 2.57 +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; 2.58 + 2.59 + 2.60 CREATE TABLE "initiator" ( 2.61 PRIMARY KEY ("initiative_id", "member_id"), 2.62 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.63 @@ -614,13 +632,15 @@ 2.64 "initiative_id" INT4, 2.65 "member_id" INT4, 2.66 "draft_id" INT8 NOT NULL, 2.67 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE, 2.68 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, 2.69 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.70 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); 2.71 2.72 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.'; 2.73 2.74 -COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 2.75 +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 2.76 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 2.77 2.78 2.79 CREATE TABLE "opinion" ( 2.80 @@ -647,11 +667,13 @@ 2.81 CREATE TABLE "delegation" ( 2.82 "id" SERIAL8 PRIMARY KEY, 2.83 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.84 - "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.85 + "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.86 "scope" "delegation_scope" NOT NULL, 2.87 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.88 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.89 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"), 2.90 + CONSTRAINT "no_global_delegation_to_null" 2.91 + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'), 2.92 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK ( 2.93 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 2.94 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 2.95 @@ -1146,31 +1168,6 @@ 2.96 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.'; 2.97 2.98 2.99 -CREATE FUNCTION "copy_autoreject_trigger"() 2.100 - RETURNS TRIGGER 2.101 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.102 - BEGIN 2.103 - IF NEW."autoreject" ISNULL THEN 2.104 - SELECT "membership"."autoreject" INTO NEW."autoreject" 2.105 - FROM "issue" JOIN "membership" 2.106 - ON "issue"."area_id" = "membership"."area_id" 2.107 - WHERE "issue"."id" = NEW."issue_id" 2.108 - AND "membership"."member_id" = NEW."member_id"; 2.109 - END IF; 2.110 - IF NEW."autoreject" ISNULL THEN 2.111 - NEW."autoreject" := FALSE; 2.112 - END IF; 2.113 - RETURN NEW; 2.114 - END; 2.115 - $$; 2.116 - 2.117 -CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest" 2.118 - FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"(); 2.119 - 2.120 -COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"'; 2.121 -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'; 2.122 - 2.123 - 2.124 CREATE FUNCTION "supporter_default_for_draft_id_trigger"() 2.125 RETURNS TRIGGER 2.126 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.127 @@ -1261,7 +1258,7 @@ 2.128 JOIN "member" ON "delegation"."truster_id" = "member"."id" 2.129 WHERE "member"."active" = TRUE; 2.130 2.131 -COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; 2.132 +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'; 2.133 2.134 2.135 CREATE VIEW "global_delegation" AS 2.136 @@ -1286,7 +1283,7 @@ 2.137 "delegation"."truster_id", 2.138 "delegation"."scope" DESC; 2.139 2.140 -COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; 2.141 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 2.142 2.143 2.144 CREATE VIEW "issue_delegation" AS 2.145 @@ -1305,7 +1302,7 @@ 2.146 "delegation"."truster_id", 2.147 "delegation"."scope" DESC; 2.148 2.149 -COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; 2.150 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 2.151 2.152 2.153 CREATE FUNCTION "membership_weight_with_skipping" 2.154 @@ -1700,6 +1697,7 @@ 2.155 "overridden" BOOLEAN, 2.156 "scope_in" "delegation_scope", 2.157 "scope_out" "delegation_scope", 2.158 + "disabled_out" BOOLEAN, 2.159 "loop" "delegation_chain_loop_tag" ); 2.160 2.161 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 2.162 @@ -1709,6 +1707,7 @@ 2.163 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 2.164 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 2.165 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 2.166 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 2.167 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 2.168 2.169 2.170 @@ -1739,6 +1738,7 @@ 2.171 "output_row"."member_active" := TRUE; 2.172 "output_row"."participation" := FALSE; 2.173 "output_row"."overridden" := FALSE; 2.174 + "output_row"."disabled_out" := FALSE; 2.175 "output_row"."scope_out" := NULL; 2.176 LOOP 2.177 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 2.178 @@ -1806,6 +1806,11 @@ 2.179 "output_row"."scope_out" := "delegation_row"."scope"; 2.180 "output_rows" := "output_rows" || "output_row"; 2.181 "output_row"."member_id" := "delegation_row"."trustee_id"; 2.182 + ELSIF "delegation_row"."scope" NOTNULL THEN 2.183 + "output_row"."scope_out" := "delegation_row"."scope"; 2.184 + "output_row"."disabled_out" := TRUE; 2.185 + "output_rows" := "output_rows" || "output_row"; 2.186 + EXIT; 2.187 ELSE 2.188 "output_row"."scope_out" := NULL; 2.189 "output_rows" := "output_rows" || "output_row"; 2.190 @@ -1819,7 +1824,7 @@ 2.191 "loop_v" := FALSE; 2.192 LOOP 2.193 "output_row" := "output_rows"["i"]; 2.194 - EXIT WHEN "output_row"."member_id" ISNULL; 2.195 + EXIT WHEN "output_row" ISNULL; 2.196 IF "loop_v" THEN 2.197 IF "i" + 1 = "row_count" THEN 2.198 "output_row"."loop" := 'last'; 2.199 @@ -3383,6 +3388,7 @@ 2.200 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.201 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.202 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.203 + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; 2.204 DELETE FROM "direct_voter" USING "issue" 2.205 WHERE "direct_voter"."issue_id" = "issue"."id" 2.206 AND "issue"."closed" ISNULL 2.207 @@ -3435,6 +3441,7 @@ 2.208 DELETE FROM "issue_setting"; 2.209 DELETE FROM "initiative_setting"; 2.210 DELETE FROM "suggestion_setting"; 2.211 + DELETE FROM "ignored_voting"; 2.212 DELETE FROM "direct_voter" USING "issue" 2.213 WHERE "direct_voter"."issue_id" = "issue"."id" 2.214 AND "issue"."closed" ISNULL;
3.1 --- a/demo.sql Tue Oct 26 22:25:47 2010 +0200 3.2 +++ b/demo.sql Fri Oct 29 18:36:36 2010 +0200 3.3 @@ -28,7 +28,8 @@ 3.4 ('user22', 'User #22'), -- id 22 3.5 ('user23', 'User #23'); -- id 23 3.6 3.7 -UPDATE "member" SET "password" = "login"; 3.8 +-- set password to "login" 3.9 +UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0'; 3.10 3.11 INSERT INTO "policy" ( 3.12 "index", 3.13 @@ -244,5 +245,65 @@ 3.14 3.15 DROP FUNCTION "time_warp"(); 3.16 3.17 +-- Test policies that help with testing specific frontend parts 3.18 + 3.19 +INSERT INTO "policy" ( 3.20 + "index", 3.21 + "active", 3.22 + "name", 3.23 + "description", 3.24 + "admission_time", 3.25 + "discussion_time", 3.26 + "verification_time", 3.27 + "voting_time", 3.28 + "issue_quorum_num", 3.29 + "issue_quorum_den", 3.30 + "initiative_quorum_num", 3.31 + "initiative_quorum_den" 3.32 + ) VALUES ( 3.33 + 1, 3.34 + TRUE, 3.35 + 'Test New', 3.36 + DEFAULT, 3.37 + '2 days', 3.38 + '1 second', 3.39 + '1 second', 3.40 + '1 second', 3.41 + 0, 100, 3.42 + 0, 100 3.43 + ), ( 3.44 + 1, 3.45 + TRUE, 3.46 + 'Test Accept', 3.47 + DEFAULT, 3.48 + '1 second', 3.49 + '2 days', 3.50 + '1 second', 3.51 + '1 second', 3.52 + 0, 100, 3.53 + 0, 100 3.54 + ), ( 3.55 + 1, 3.56 + TRUE, 3.57 + 'Test Frozen', 3.58 + DEFAULT, 3.59 + '1 second', 3.60 + '5 minutes', 3.61 + '2 days', 3.62 + '1 second', 3.63 + 0, 100, 3.64 + 0, 100 3.65 + ), ( 3.66 + 1, 3.67 + TRUE, 3.68 + 'Test Voting', 3.69 + DEFAULT, 3.70 + '1 second', 3.71 + '5 minutes', 3.72 + '1 second', 3.73 + '2 days', 3.74 + 0, 100, 3.75 + 0, 100 3.76 + ); 3.77 END; 3.78
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/update/core-update.v1.2.9-v1.3.0.sql Fri Oct 29 18:36:36 2010 +0200 4.3 @@ -0,0 +1,384 @@ 4.4 +BEGIN; 4.5 + 4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) 4.8 + AS "subquery"("string", "major", "minor", "revision"); 4.9 + 4.10 +ALTER TABLE "supporter" ADD COLUMN 4.11 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 4.12 + 4.13 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 4.14 + 4.15 + 4.16 +-- update comment for column "fully_frozen" of table "issue" 4.17 + 4.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.'; 4.19 + 4.20 + 4.21 +-- update comment for column "autoreject" of table "membership" 4.22 + 4.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.'; 4.24 + 4.25 + 4.26 +-- allow column "autoreject" of table "interest" to be NULL 4.27 +-- (thus defaulting to "membership") 4.28 + 4.29 +ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL; 4.30 + 4.31 + 4.32 +-- new table "ignored_issue" to allow members to ignore particular issues in certain states 4.33 + 4.34 +CREATE TABLE "ignored_issue" ( 4.35 + PRIMARY KEY ("issue_id", "member_id"), 4.36 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 4.37 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 4.38 + "new" BOOLEAN NOT NULL DEFAULT FALSE, 4.39 + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 4.40 + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 4.41 + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 4.42 +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 4.43 + 4.44 +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 4.45 + 4.46 +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; 4.47 +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; 4.48 +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; 4.49 +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; 4.50 + 4.51 + 4.52 +-- allow area and issue delegations with trustee_id set to NULL 4.53 +-- (indicating that global or area delegation is void for that area or issue) 4.54 + 4.55 +ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL; 4.56 + 4.57 +ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null" 4.58 + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'); 4.59 + 4.60 + 4.61 +-- disable and delete "copy_autoreject" trigger on table "interest" 4.62 + 4.63 +DROP TRIGGER "copy_autoreject" ON "interest"; 4.64 +DROP FUNCTION "copy_autoreject_trigger"(); 4.65 + 4.66 + 4.67 +-- update comments on delegation views 4.68 + 4.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'; 4.70 + 4.71 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; 4.72 + 4.73 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; 4.74 + 4.75 + 4.76 +-- 4.77 + 4.78 +DROP FUNCTION "delegation_chain" 4.79 + ( "member"."id"%TYPE, 4.80 + "area"."id"%TYPE, 4.81 + "issue"."id"%TYPE ); 4.82 + 4.83 +DROP FUNCTION "delegation_chain" 4.84 + ( "member"."id"%TYPE, 4.85 + "area"."id"%TYPE, 4.86 + "issue"."id"%TYPE, 4.87 + "member"."id"%TYPE ); 4.88 + 4.89 +DROP TYPE "delegation_chain_row"; 4.90 + 4.91 +CREATE TYPE "delegation_chain_row" AS ( 4.92 + "index" INT4, 4.93 + "member_id" INT4, 4.94 + "member_active" BOOLEAN, 4.95 + "participation" BOOLEAN, 4.96 + "overridden" BOOLEAN, 4.97 + "scope_in" "delegation_scope", 4.98 + "scope_out" "delegation_scope", 4.99 + "disabled_out" BOOLEAN, 4.100 + "loop" "delegation_chain_loop_tag" ); 4.101 + 4.102 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 4.103 + 4.104 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 4.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'; 4.106 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 4.107 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 4.108 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 4.109 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 4.110 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 4.111 + 4.112 + 4.113 +CREATE FUNCTION "delegation_chain" 4.114 + ( "member_id_p" "member"."id"%TYPE, 4.115 + "area_id_p" "area"."id"%TYPE, 4.116 + "issue_id_p" "issue"."id"%TYPE, 4.117 + "simulate_trustee_id_p" "member"."id"%TYPE ) 4.118 + RETURNS SETOF "delegation_chain_row" 4.119 + LANGUAGE 'plpgsql' STABLE AS $$ 4.120 + DECLARE 4.121 + "issue_row" "issue"%ROWTYPE; 4.122 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 4.123 + "loop_member_id_v" "member"."id"%TYPE; 4.124 + "output_row" "delegation_chain_row"; 4.125 + "output_rows" "delegation_chain_row"[]; 4.126 + "delegation_row" "delegation"%ROWTYPE; 4.127 + "row_count" INT4; 4.128 + "i" INT4; 4.129 + "loop_v" BOOLEAN; 4.130 + BEGIN 4.131 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 4.132 + "visited_member_ids" := '{}'; 4.133 + "loop_member_id_v" := NULL; 4.134 + "output_rows" := '{}'; 4.135 + "output_row"."index" := 0; 4.136 + "output_row"."member_id" := "member_id_p"; 4.137 + "output_row"."member_active" := TRUE; 4.138 + "output_row"."participation" := FALSE; 4.139 + "output_row"."overridden" := FALSE; 4.140 + "output_row"."disabled_out" := FALSE; 4.141 + "output_row"."scope_out" := NULL; 4.142 + LOOP 4.143 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 4.144 + "loop_member_id_v" := "output_row"."member_id"; 4.145 + ELSE 4.146 + "visited_member_ids" := 4.147 + "visited_member_ids" || "output_row"."member_id"; 4.148 + END IF; 4.149 + IF "output_row"."participation" THEN 4.150 + "output_row"."overridden" := TRUE; 4.151 + END IF; 4.152 + "output_row"."scope_in" := "output_row"."scope_out"; 4.153 + IF EXISTS ( 4.154 + SELECT NULL FROM "member" 4.155 + WHERE "id" = "output_row"."member_id" AND "active" 4.156 + ) THEN 4.157 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 4.158 + SELECT * INTO "delegation_row" FROM "delegation" 4.159 + WHERE "truster_id" = "output_row"."member_id" 4.160 + AND "scope" = 'global'; 4.161 + ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 4.162 + "output_row"."participation" := EXISTS ( 4.163 + SELECT NULL FROM "membership" 4.164 + WHERE "area_id" = "area_id_p" 4.165 + AND "member_id" = "output_row"."member_id" 4.166 + ); 4.167 + SELECT * INTO "delegation_row" FROM "delegation" 4.168 + WHERE "truster_id" = "output_row"."member_id" 4.169 + AND ("scope" = 'global' OR "area_id" = "area_id_p") 4.170 + ORDER BY "scope" DESC; 4.171 + ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 4.172 + "output_row"."participation" := EXISTS ( 4.173 + SELECT NULL FROM "interest" 4.174 + WHERE "issue_id" = "issue_id_p" 4.175 + AND "member_id" = "output_row"."member_id" 4.176 + ); 4.177 + SELECT * INTO "delegation_row" FROM "delegation" 4.178 + WHERE "truster_id" = "output_row"."member_id" 4.179 + AND ("scope" = 'global' OR 4.180 + "area_id" = "issue_row"."area_id" OR 4.181 + "issue_id" = "issue_id_p" 4.182 + ) 4.183 + ORDER BY "scope" DESC; 4.184 + ELSE 4.185 + RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 4.186 + END IF; 4.187 + ELSE 4.188 + "output_row"."member_active" := FALSE; 4.189 + "output_row"."participation" := FALSE; 4.190 + "output_row"."scope_out" := NULL; 4.191 + "delegation_row" := ROW(NULL); 4.192 + END IF; 4.193 + IF 4.194 + "output_row"."member_id" = "member_id_p" AND 4.195 + "simulate_trustee_id_p" NOTNULL 4.196 + THEN 4.197 + "output_row"."scope_out" := CASE 4.198 + WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' 4.199 + WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' 4.200 + WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' 4.201 + END; 4.202 + "output_rows" := "output_rows" || "output_row"; 4.203 + "output_row"."member_id" := "simulate_trustee_id_p"; 4.204 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 4.205 + "output_row"."scope_out" := "delegation_row"."scope"; 4.206 + "output_rows" := "output_rows" || "output_row"; 4.207 + "output_row"."member_id" := "delegation_row"."trustee_id"; 4.208 + ELSIF "delegation_row"."scope" NOTNULL THEN 4.209 + "output_row"."scope_out" := "delegation_row"."scope"; 4.210 + "output_row"."disabled_out" := TRUE; 4.211 + "output_rows" := "output_rows" || "output_row"; 4.212 + EXIT; 4.213 + ELSE 4.214 + "output_row"."scope_out" := NULL; 4.215 + "output_rows" := "output_rows" || "output_row"; 4.216 + EXIT; 4.217 + END IF; 4.218 + EXIT WHEN "loop_member_id_v" NOTNULL; 4.219 + "output_row"."index" := "output_row"."index" + 1; 4.220 + END LOOP; 4.221 + "row_count" := array_upper("output_rows", 1); 4.222 + "i" := 1; 4.223 + "loop_v" := FALSE; 4.224 + LOOP 4.225 + "output_row" := "output_rows"["i"]; 4.226 + EXIT WHEN "output_row" ISNULL; 4.227 + IF "loop_v" THEN 4.228 + IF "i" + 1 = "row_count" THEN 4.229 + "output_row"."loop" := 'last'; 4.230 + ELSIF "i" = "row_count" THEN 4.231 + "output_row"."loop" := 'repetition'; 4.232 + ELSE 4.233 + "output_row"."loop" := 'intermediate'; 4.234 + END IF; 4.235 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 4.236 + "output_row"."loop" := 'first'; 4.237 + "loop_v" := TRUE; 4.238 + END IF; 4.239 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 4.240 + "output_row"."participation" := NULL; 4.241 + END IF; 4.242 + RETURN NEXT "output_row"; 4.243 + "i" := "i" + 1; 4.244 + END LOOP; 4.245 + RETURN; 4.246 + END; 4.247 + $$; 4.248 + 4.249 +COMMENT ON FUNCTION "delegation_chain" 4.250 + ( "member"."id"%TYPE, 4.251 + "area"."id"%TYPE, 4.252 + "issue"."id"%TYPE, 4.253 + "member"."id"%TYPE ) 4.254 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 4.255 + 4.256 +CREATE FUNCTION "delegation_chain" 4.257 + ( "member_id_p" "member"."id"%TYPE, 4.258 + "area_id_p" "area"."id"%TYPE, 4.259 + "issue_id_p" "issue"."id"%TYPE ) 4.260 + RETURNS SETOF "delegation_chain_row" 4.261 + LANGUAGE 'plpgsql' STABLE AS $$ 4.262 + DECLARE 4.263 + "result_row" "delegation_chain_row"; 4.264 + BEGIN 4.265 + FOR "result_row" IN 4.266 + SELECT * FROM "delegation_chain"( 4.267 + "member_id_p", "area_id_p", "issue_id_p", NULL 4.268 + ) 4.269 + LOOP 4.270 + RETURN NEXT "result_row"; 4.271 + END LOOP; 4.272 + RETURN; 4.273 + END; 4.274 + $$; 4.275 + 4.276 +COMMENT ON FUNCTION "delegation_chain" 4.277 + ( "member"."id"%TYPE, 4.278 + "area"."id"%TYPE, 4.279 + "issue"."id"%TYPE ) 4.280 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 4.281 + 4.282 + 4.283 +-- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions 4.284 + 4.285 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 4.286 + RETURNS VOID 4.287 + LANGUAGE 'plpgsql' VOLATILE AS $$ 4.288 + BEGIN 4.289 + UPDATE "member" SET 4.290 + "last_login" = NULL, 4.291 + "login" = NULL, 4.292 + "password" = NULL, 4.293 + "active" = FALSE, 4.294 + "notify_email" = NULL, 4.295 + "notify_email_unconfirmed" = NULL, 4.296 + "notify_email_secret" = NULL, 4.297 + "notify_email_secret_expiry" = NULL, 4.298 + "notify_email_lock_expiry" = NULL, 4.299 + "password_reset_secret" = NULL, 4.300 + "password_reset_secret_expiry" = NULL, 4.301 + "organizational_unit" = NULL, 4.302 + "internal_posts" = NULL, 4.303 + "realname" = NULL, 4.304 + "birthday" = NULL, 4.305 + "address" = NULL, 4.306 + "email" = NULL, 4.307 + "xmpp_address" = NULL, 4.308 + "website" = NULL, 4.309 + "phone" = NULL, 4.310 + "mobile_phone" = NULL, 4.311 + "profession" = NULL, 4.312 + "external_memberships" = NULL, 4.313 + "external_posts" = NULL, 4.314 + "statement" = NULL 4.315 + WHERE "id" = "member_id_p"; 4.316 + -- "text_search_data" is updated by triggers 4.317 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 4.318 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 4.319 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 4.320 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 4.321 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 4.322 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 4.323 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 4.324 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 4.325 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 4.326 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 4.327 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 4.328 + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; 4.329 + DELETE FROM "direct_voter" USING "issue" 4.330 + WHERE "direct_voter"."issue_id" = "issue"."id" 4.331 + AND "issue"."closed" ISNULL 4.332 + AND "member_id" = "member_id_p"; 4.333 + RETURN; 4.334 + END; 4.335 + $$; 4.336 + 4.337 +CREATE OR REPLACE FUNCTION "delete_private_data"() 4.338 + RETURNS VOID 4.339 + LANGUAGE 'plpgsql' VOLATILE AS $$ 4.340 + BEGIN 4.341 + UPDATE "member" SET 4.342 + "last_login" = NULL, 4.343 + "login" = NULL, 4.344 + "password" = NULL, 4.345 + "notify_email" = NULL, 4.346 + "notify_email_unconfirmed" = NULL, 4.347 + "notify_email_secret" = NULL, 4.348 + "notify_email_secret_expiry" = NULL, 4.349 + "notify_email_lock_expiry" = NULL, 4.350 + "password_reset_secret" = NULL, 4.351 + "password_reset_secret_expiry" = NULL, 4.352 + "organizational_unit" = NULL, 4.353 + "internal_posts" = NULL, 4.354 + "realname" = NULL, 4.355 + "birthday" = NULL, 4.356 + "address" = NULL, 4.357 + "email" = NULL, 4.358 + "xmpp_address" = NULL, 4.359 + "website" = NULL, 4.360 + "phone" = NULL, 4.361 + "mobile_phone" = NULL, 4.362 + "profession" = NULL, 4.363 + "external_memberships" = NULL, 4.364 + "external_posts" = NULL, 4.365 + "statement" = NULL; 4.366 + -- "text_search_data" is updated by triggers 4.367 + DELETE FROM "invite_code"; 4.368 + DELETE FROM "setting"; 4.369 + DELETE FROM "setting_map"; 4.370 + DELETE FROM "member_relation_setting"; 4.371 + DELETE FROM "member_image"; 4.372 + DELETE FROM "contact"; 4.373 + DELETE FROM "session"; 4.374 + DELETE FROM "area_setting"; 4.375 + DELETE FROM "issue_setting"; 4.376 + DELETE FROM "initiative_setting"; 4.377 + DELETE FROM "suggestion_setting"; 4.378 + DELETE FROM "ignored_voting"; 4.379 + DELETE FROM "direct_voter" USING "issue" 4.380 + WHERE "direct_voter"."issue_id" = "issue"."id" 4.381 + AND "issue"."closed" ISNULL; 4.382 + RETURN; 4.383 + END; 4.384 + $$; 4.385 + 4.386 + 4.387 +COMMIT;