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;

Impressum / About Us