# HG changeset patch # User jbe # Date 1288370196 -7200 # Node ID d7eadecc7b053655bf0ee4c019d4fab09a2dcb87 # Parent dfa00eaa508105b8e79cf1ed874bd3eaa74351cf# Parent 3a86196ed0bf0460c710420f2c6795bc58a8a635 Merged planned changes for version 1.3.0 with latest bugfix for version 1.2.9 diff -r dfa00eaa5081 -r d7eadecc7b05 .hgtags --- a/.hgtags Tue Oct 26 22:25:47 2010 +0200 +++ b/.hgtags Fri Oct 29 18:36:36 2010 +0200 @@ -37,4 +37,5 @@ 72e5356b5454443864423d19e653bc4ae020373d v1.2.5 5745db0b1a34d52ee903f6841768cf99471ad155 v1.2.6 dcaa1525c38812eae0a0615b1217ac3bd9c63fa2 v1.2.7 +f77c0f3d443c79d7c70e3f5075b31a9afe6ff4cb v1.2.8 e588fdf1676e1e069e9c84ba791465482ca2720c v1.2.9 diff -r dfa00eaa5081 -r d7eadecc7b05 core.sql --- a/core.sql Tue Oct 26 22:25:47 2010 +0200 +++ b/core.sql Fri Oct 29 18:36:36 2010 +0200 @@ -6,7 +6,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) AS "subquery"("string", "major", "minor", "revision"); @@ -389,7 +389,7 @@ COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 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.'; -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.'; +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.'; 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.'; COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; @@ -579,14 +579,14 @@ COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; -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'; +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.'; CREATE TABLE "interest" ( PRIMARY KEY ("issue_id", "member_id"), "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - "autoreject" BOOLEAN NOT NULL, + "autoreject" BOOLEAN, "voting_requested" BOOLEAN ); CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); @@ -596,6 +596,24 @@ COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply'; +CREATE TABLE "ignored_issue" ( + PRIMARY KEY ("issue_id", "member_id"), + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "new" BOOLEAN NOT NULL DEFAULT FALSE, + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); + +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; + +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; + + CREATE TABLE "initiator" ( PRIMARY KEY ("initiative_id", "member_id"), "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, @@ -614,13 +632,15 @@ "initiative_id" INT4, "member_id" INT4, "draft_id" INT8 NOT NULL, + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE, FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); 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.'; -COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; CREATE TABLE "opinion" ( @@ -647,11 +667,13 @@ CREATE TABLE "delegation" ( "id" SERIAL8 PRIMARY KEY, "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "scope" "delegation_scope" NOT NULL, "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"), + CONSTRAINT "no_global_delegation_to_null" + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'), CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK ( ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR @@ -1146,31 +1168,6 @@ COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.'; -CREATE FUNCTION "copy_autoreject_trigger"() - RETURNS TRIGGER - LANGUAGE 'plpgsql' VOLATILE AS $$ - BEGIN - IF NEW."autoreject" ISNULL THEN - SELECT "membership"."autoreject" INTO NEW."autoreject" - FROM "issue" JOIN "membership" - ON "issue"."area_id" = "membership"."area_id" - WHERE "issue"."id" = NEW."issue_id" - AND "membership"."member_id" = NEW."member_id"; - END IF; - IF NEW."autoreject" ISNULL THEN - NEW."autoreject" := FALSE; - END IF; - RETURN NEW; - END; - $$; - -CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest" - FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"(); - -COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"'; -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'; - - CREATE FUNCTION "supporter_default_for_draft_id_trigger"() RETURNS TRIGGER LANGUAGE 'plpgsql' VOLATILE AS $$ @@ -1261,7 +1258,7 @@ JOIN "member" ON "delegation"."truster_id" = "member"."id" WHERE "member"."active" = TRUE; -COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; +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'; CREATE VIEW "global_delegation" AS @@ -1286,7 +1283,7 @@ "delegation"."truster_id", "delegation"."scope" DESC; -COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; CREATE VIEW "issue_delegation" AS @@ -1305,7 +1302,7 @@ "delegation"."truster_id", "delegation"."scope" DESC; -COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; CREATE FUNCTION "membership_weight_with_skipping" @@ -1700,6 +1697,7 @@ "overridden" BOOLEAN, "scope_in" "delegation_scope", "scope_out" "delegation_scope", + "disabled_out" BOOLEAN, "loop" "delegation_chain_loop_tag" ); COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; @@ -1709,6 +1707,7 @@ COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; @@ -1739,6 +1738,7 @@ "output_row"."member_active" := TRUE; "output_row"."participation" := FALSE; "output_row"."overridden" := FALSE; + "output_row"."disabled_out" := FALSE; "output_row"."scope_out" := NULL; LOOP IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN @@ -1806,6 +1806,11 @@ "output_row"."scope_out" := "delegation_row"."scope"; "output_rows" := "output_rows" || "output_row"; "output_row"."member_id" := "delegation_row"."trustee_id"; + ELSIF "delegation_row"."scope" NOTNULL THEN + "output_row"."scope_out" := "delegation_row"."scope"; + "output_row"."disabled_out" := TRUE; + "output_rows" := "output_rows" || "output_row"; + EXIT; ELSE "output_row"."scope_out" := NULL; "output_rows" := "output_rows" || "output_row"; @@ -1819,7 +1824,7 @@ "loop_v" := FALSE; LOOP "output_row" := "output_rows"["i"]; - EXIT WHEN "output_row"."member_id" ISNULL; + EXIT WHEN "output_row" ISNULL; IF "loop_v" THEN IF "i" + 1 = "row_count" THEN "output_row"."loop" := 'last'; @@ -3383,6 +3388,7 @@ DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "membership" WHERE "member_id" = "member_id_p"; DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; DELETE FROM "direct_voter" USING "issue" WHERE "direct_voter"."issue_id" = "issue"."id" AND "issue"."closed" ISNULL @@ -3435,6 +3441,7 @@ DELETE FROM "issue_setting"; DELETE FROM "initiative_setting"; DELETE FROM "suggestion_setting"; + DELETE FROM "ignored_voting"; DELETE FROM "direct_voter" USING "issue" WHERE "direct_voter"."issue_id" = "issue"."id" AND "issue"."closed" ISNULL; diff -r dfa00eaa5081 -r d7eadecc7b05 demo.sql --- a/demo.sql Tue Oct 26 22:25:47 2010 +0200 +++ b/demo.sql Fri Oct 29 18:36:36 2010 +0200 @@ -28,7 +28,8 @@ ('user22', 'User #22'), -- id 22 ('user23', 'User #23'); -- id 23 -UPDATE "member" SET "password" = "login"; +-- set password to "login" +UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0'; INSERT INTO "policy" ( "index", @@ -244,5 +245,65 @@ DROP FUNCTION "time_warp"(); +-- Test policies that help with testing specific frontend parts + +INSERT INTO "policy" ( + "index", + "active", + "name", + "description", + "admission_time", + "discussion_time", + "verification_time", + "voting_time", + "issue_quorum_num", + "issue_quorum_den", + "initiative_quorum_num", + "initiative_quorum_den" + ) VALUES ( + 1, + TRUE, + 'Test New', + DEFAULT, + '2 days', + '1 second', + '1 second', + '1 second', + 0, 100, + 0, 100 + ), ( + 1, + TRUE, + 'Test Accept', + DEFAULT, + '1 second', + '2 days', + '1 second', + '1 second', + 0, 100, + 0, 100 + ), ( + 1, + TRUE, + 'Test Frozen', + DEFAULT, + '1 second', + '5 minutes', + '2 days', + '1 second', + 0, 100, + 0, 100 + ), ( + 1, + TRUE, + 'Test Voting', + DEFAULT, + '1 second', + '5 minutes', + '1 second', + '2 days', + 0, 100, + 0, 100 + ); END; diff -r dfa00eaa5081 -r d7eadecc7b05 update/core-update.v1.2.9-v1.3.0.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v1.2.9-v1.3.0.sql Fri Oct 29 18:36:36 2010 +0200 @@ -0,0 +1,384 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('1.3.0', 1, 3, 0)) + AS "subquery"("string", "major", "minor", "revision"); + +ALTER TABLE "supporter" ADD COLUMN + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; + +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; + + +-- update comment for column "fully_frozen" of table "issue" + +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.'; + + +-- update comment for column "autoreject" of table "membership" + +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.'; + + +-- allow column "autoreject" of table "interest" to be NULL +-- (thus defaulting to "membership") + +ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL; + + +-- new table "ignored_issue" to allow members to ignore particular issues in certain states + +CREATE TABLE "ignored_issue" ( + PRIMARY KEY ("issue_id", "member_id"), + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "new" BOOLEAN NOT NULL DEFAULT FALSE, + "accepted" BOOLEAN NOT NULL DEFAULT FALSE, + "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, + "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); +CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); + +COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; + +COMMENT ON COLUMN "ignored_issue"."new" IS 'Selects issues which are neither closed nor accepted'; +COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Selects issues which are accepted but not (half_)frozen or closed'; +COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Selects issues which are half_frozen but not fully_frozen or closed'; +COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Selects issues which are fully_frozen (in voting) and not closed'; + + +-- allow area and issue delegations with trustee_id set to NULL +-- (indicating that global or area delegation is void for that area or issue) + +ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL; + +ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null" + CHECK ("trustee_id" NOTNULL OR "scope" != 'global'); + + +-- disable and delete "copy_autoreject" trigger on table "interest" + +DROP TRIGGER "copy_autoreject" ON "interest"; +DROP FUNCTION "copy_autoreject_trigger"(); + + +-- update comments on delegation views + +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'; + +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL'; + +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL'; + + +-- + +DROP FUNCTION "delegation_chain" + ( "member"."id"%TYPE, + "area"."id"%TYPE, + "issue"."id"%TYPE ); + +DROP FUNCTION "delegation_chain" + ( "member"."id"%TYPE, + "area"."id"%TYPE, + "issue"."id"%TYPE, + "member"."id"%TYPE ); + +DROP TYPE "delegation_chain_row"; + +CREATE TYPE "delegation_chain_row" AS ( + "index" INT4, + "member_id" INT4, + "member_active" BOOLEAN, + "participation" BOOLEAN, + "overridden" BOOLEAN, + "scope_in" "delegation_scope", + "scope_out" "delegation_scope", + "disabled_out" BOOLEAN, + "loop" "delegation_chain_loop_tag" ); + +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; + +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; +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'; +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; + + +CREATE FUNCTION "delegation_chain" + ( "member_id_p" "member"."id"%TYPE, + "area_id_p" "area"."id"%TYPE, + "issue_id_p" "issue"."id"%TYPE, + "simulate_trustee_id_p" "member"."id"%TYPE ) + RETURNS SETOF "delegation_chain_row" + LANGUAGE 'plpgsql' STABLE AS $$ + DECLARE + "issue_row" "issue"%ROWTYPE; + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] + "loop_member_id_v" "member"."id"%TYPE; + "output_row" "delegation_chain_row"; + "output_rows" "delegation_chain_row"[]; + "delegation_row" "delegation"%ROWTYPE; + "row_count" INT4; + "i" INT4; + "loop_v" BOOLEAN; + BEGIN + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; + "visited_member_ids" := '{}'; + "loop_member_id_v" := NULL; + "output_rows" := '{}'; + "output_row"."index" := 0; + "output_row"."member_id" := "member_id_p"; + "output_row"."member_active" := TRUE; + "output_row"."participation" := FALSE; + "output_row"."overridden" := FALSE; + "output_row"."disabled_out" := FALSE; + "output_row"."scope_out" := NULL; + LOOP + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN + "loop_member_id_v" := "output_row"."member_id"; + ELSE + "visited_member_ids" := + "visited_member_ids" || "output_row"."member_id"; + END IF; + IF "output_row"."participation" THEN + "output_row"."overridden" := TRUE; + END IF; + "output_row"."scope_in" := "output_row"."scope_out"; + IF EXISTS ( + SELECT NULL FROM "member" + WHERE "id" = "output_row"."member_id" AND "active" + ) THEN + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN + SELECT * INTO "delegation_row" FROM "delegation" + WHERE "truster_id" = "output_row"."member_id" + AND "scope" = 'global'; + ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN + "output_row"."participation" := EXISTS ( + SELECT NULL FROM "membership" + WHERE "area_id" = "area_id_p" + AND "member_id" = "output_row"."member_id" + ); + SELECT * INTO "delegation_row" FROM "delegation" + WHERE "truster_id" = "output_row"."member_id" + AND ("scope" = 'global' OR "area_id" = "area_id_p") + ORDER BY "scope" DESC; + ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN + "output_row"."participation" := EXISTS ( + SELECT NULL FROM "interest" + WHERE "issue_id" = "issue_id_p" + AND "member_id" = "output_row"."member_id" + ); + SELECT * INTO "delegation_row" FROM "delegation" + WHERE "truster_id" = "output_row"."member_id" + AND ("scope" = 'global' OR + "area_id" = "issue_row"."area_id" OR + "issue_id" = "issue_id_p" + ) + ORDER BY "scope" DESC; + ELSE + RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; + END IF; + ELSE + "output_row"."member_active" := FALSE; + "output_row"."participation" := FALSE; + "output_row"."scope_out" := NULL; + "delegation_row" := ROW(NULL); + END IF; + IF + "output_row"."member_id" = "member_id_p" AND + "simulate_trustee_id_p" NOTNULL + THEN + "output_row"."scope_out" := CASE + WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' + WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' + WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' + END; + "output_rows" := "output_rows" || "output_row"; + "output_row"."member_id" := "simulate_trustee_id_p"; + ELSIF "delegation_row"."trustee_id" NOTNULL THEN + "output_row"."scope_out" := "delegation_row"."scope"; + "output_rows" := "output_rows" || "output_row"; + "output_row"."member_id" := "delegation_row"."trustee_id"; + ELSIF "delegation_row"."scope" NOTNULL THEN + "output_row"."scope_out" := "delegation_row"."scope"; + "output_row"."disabled_out" := TRUE; + "output_rows" := "output_rows" || "output_row"; + EXIT; + ELSE + "output_row"."scope_out" := NULL; + "output_rows" := "output_rows" || "output_row"; + EXIT; + END IF; + EXIT WHEN "loop_member_id_v" NOTNULL; + "output_row"."index" := "output_row"."index" + 1; + END LOOP; + "row_count" := array_upper("output_rows", 1); + "i" := 1; + "loop_v" := FALSE; + LOOP + "output_row" := "output_rows"["i"]; + EXIT WHEN "output_row" ISNULL; + IF "loop_v" THEN + IF "i" + 1 = "row_count" THEN + "output_row"."loop" := 'last'; + ELSIF "i" = "row_count" THEN + "output_row"."loop" := 'repetition'; + ELSE + "output_row"."loop" := 'intermediate'; + END IF; + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN + "output_row"."loop" := 'first'; + "loop_v" := TRUE; + END IF; + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN + "output_row"."participation" := NULL; + END IF; + RETURN NEXT "output_row"; + "i" := "i" + 1; + END LOOP; + RETURN; + END; + $$; + +COMMENT ON FUNCTION "delegation_chain" + ( "member"."id"%TYPE, + "area"."id"%TYPE, + "issue"."id"%TYPE, + "member"."id"%TYPE ) + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; + +CREATE FUNCTION "delegation_chain" + ( "member_id_p" "member"."id"%TYPE, + "area_id_p" "area"."id"%TYPE, + "issue_id_p" "issue"."id"%TYPE ) + RETURNS SETOF "delegation_chain_row" + LANGUAGE 'plpgsql' STABLE AS $$ + DECLARE + "result_row" "delegation_chain_row"; + BEGIN + FOR "result_row" IN + SELECT * FROM "delegation_chain"( + "member_id_p", "area_id_p", "issue_id_p", NULL + ) + LOOP + RETURN NEXT "result_row"; + END LOOP; + RETURN; + END; + $$; + +COMMENT ON FUNCTION "delegation_chain" + ( "member"."id"%TYPE, + "area"."id"%TYPE, + "issue"."id"%TYPE ) + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; + + +-- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions + +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "login" = NULL, + "password" = NULL, + "active" = FALSE, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL + WHERE "id" = "member_id_p"; + -- "text_search_data" is updated by triggers + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL + AND "member_id" = "member_id_p"; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "delete_private_data"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "login" = NULL, + "password" = NULL, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL; + -- "text_search_data" is updated by triggers + DELETE FROM "invite_code"; + DELETE FROM "setting"; + DELETE FROM "setting_map"; + DELETE FROM "member_relation_setting"; + DELETE FROM "member_image"; + DELETE FROM "contact"; + DELETE FROM "session"; + DELETE FROM "area_setting"; + DELETE FROM "issue_setting"; + DELETE FROM "initiative_setting"; + DELETE FROM "suggestion_setting"; + DELETE FROM "ignored_voting"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL; + RETURN; + END; + $$; + + +COMMIT;