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;

Impressum / About Us