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;