1.1 --- a/core.sql	Tue Oct 26 22:25:47 2010 +0200
     1.2 +++ b/core.sql	Fri Oct 29 18:36:36 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 @@ -614,13 +632,15 @@
    1.64          "initiative_id"         INT4,
    1.65          "member_id"             INT4,
    1.66          "draft_id"              INT8            NOT NULL,
    1.67 +        "auto_support"          BOOLEAN         NOT NULL DEFAULT FALSE,
    1.68          FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.69          FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.70  CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
    1.71  
    1.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.';
    1.73  
    1.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")';
    1.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")';
    1.76 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative';
    1.77  
    1.78  
    1.79  CREATE TABLE "opinion" (
    1.80 @@ -647,11 +667,13 @@
    1.81  CREATE TABLE "delegation" (
    1.82          "id"                    SERIAL8         PRIMARY KEY,
    1.83          "truster_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.84 -        "trustee_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.85 +        "trustee_id"            INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.86          "scope"              "delegation_scope" NOT NULL,
    1.87          "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.88          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.89          CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
    1.90 +        CONSTRAINT "no_global_delegation_to_null"
    1.91 +          CHECK ("trustee_id" NOTNULL OR "scope" != 'global'),
    1.92          CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
    1.93            ("scope" = 'global' AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
    1.94            ("scope" = 'area'   AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
    1.95 @@ -1146,31 +1168,6 @@
    1.96  COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
    1.97  
    1.98  
    1.99 -CREATE FUNCTION "copy_autoreject_trigger"()
   1.100 -  RETURNS TRIGGER
   1.101 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.102 -    BEGIN
   1.103 -      IF NEW."autoreject" ISNULL THEN
   1.104 -        SELECT "membership"."autoreject" INTO NEW."autoreject"
   1.105 -          FROM "issue" JOIN "membership"
   1.106 -          ON "issue"."area_id" = "membership"."area_id"
   1.107 -          WHERE "issue"."id" = NEW."issue_id"
   1.108 -          AND "membership"."member_id" = NEW."member_id";
   1.109 -      END IF;
   1.110 -      IF NEW."autoreject" ISNULL THEN 
   1.111 -        NEW."autoreject" := FALSE;
   1.112 -      END IF;
   1.113 -      RETURN NEW;
   1.114 -    END;
   1.115 -  $$;
   1.116 -
   1.117 -CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
   1.118 -  FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
   1.119 -
   1.120 -COMMENT ON FUNCTION "copy_autoreject_trigger"()    IS 'Implementation of trigger "copy_autoreject" on table "interest"';
   1.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';
   1.122 -
   1.123 -
   1.124  CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
   1.125    RETURNS TRIGGER
   1.126    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.127 @@ -1261,7 +1258,7 @@
   1.128    JOIN "member" ON "delegation"."truster_id" = "member"."id"
   1.129    WHERE "member"."active" = TRUE;
   1.130  
   1.131 -COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member';
   1.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';
   1.133  
   1.134  
   1.135  CREATE VIEW "global_delegation" AS
   1.136 @@ -1286,7 +1283,7 @@
   1.137      "delegation"."truster_id",
   1.138      "delegation"."scope" DESC;
   1.139  
   1.140 -COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members';
   1.141 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';
   1.142  
   1.143  
   1.144  CREATE VIEW "issue_delegation" AS
   1.145 @@ -1305,7 +1302,7 @@
   1.146      "delegation"."truster_id",
   1.147      "delegation"."scope" DESC;
   1.148  
   1.149 -COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members';
   1.150 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';
   1.151  
   1.152  
   1.153  CREATE FUNCTION "membership_weight_with_skipping"
   1.154 @@ -1700,6 +1697,7 @@
   1.155          "overridden"            BOOLEAN,
   1.156          "scope_in"              "delegation_scope",
   1.157          "scope_out"             "delegation_scope",
   1.158 +        "disabled_out"          BOOLEAN,
   1.159          "loop"                  "delegation_chain_loop_tag" );
   1.160  
   1.161  COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
   1.162 @@ -1709,6 +1707,7 @@
   1.163  COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
   1.164  COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
   1.165  COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
   1.166 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
   1.167  COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
   1.168  
   1.169  
   1.170 @@ -1739,6 +1738,7 @@
   1.171        "output_row"."member_active" := TRUE;
   1.172        "output_row"."participation" := FALSE;
   1.173        "output_row"."overridden"    := FALSE;
   1.174 +      "output_row"."disabled_out"  := FALSE;
   1.175        "output_row"."scope_out"     := NULL;
   1.176        LOOP
   1.177          IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.178 @@ -1806,6 +1806,11 @@
   1.179            "output_row"."scope_out" := "delegation_row"."scope";
   1.180            "output_rows" := "output_rows" || "output_row";
   1.181            "output_row"."member_id" := "delegation_row"."trustee_id";
   1.182 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   1.183 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.184 +          "output_row"."disabled_out" := TRUE;
   1.185 +          "output_rows" := "output_rows" || "output_row";
   1.186 +          EXIT;
   1.187          ELSE
   1.188            "output_row"."scope_out" := NULL;
   1.189            "output_rows" := "output_rows" || "output_row";
   1.190 @@ -1819,7 +1824,7 @@
   1.191        "loop_v" := FALSE;
   1.192        LOOP
   1.193          "output_row" := "output_rows"["i"];
   1.194 -        EXIT WHEN "output_row"."member_id" ISNULL;
   1.195 +        EXIT WHEN "output_row" ISNULL;
   1.196          IF "loop_v" THEN
   1.197            IF "i" + 1 = "row_count" THEN
   1.198              "output_row"."loop" := 'last';
   1.199 @@ -3383,6 +3388,7 @@
   1.200        DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
   1.201        DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
   1.202        DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
   1.203 +      DELETE FROM "ignored_voting"     WHERE "member_id" = "member_id_p";
   1.204        DELETE FROM "direct_voter" USING "issue"
   1.205          WHERE "direct_voter"."issue_id" = "issue"."id"
   1.206          AND "issue"."closed" ISNULL
   1.207 @@ -3435,6 +3441,7 @@
   1.208        DELETE FROM "issue_setting";
   1.209        DELETE FROM "initiative_setting";
   1.210        DELETE FROM "suggestion_setting";
   1.211 +      DELETE FROM "ignored_voting";
   1.212        DELETE FROM "direct_voter" USING "issue"
   1.213          WHERE "direct_voter"."issue_id" = "issue"."id"
   1.214          AND "issue"."closed" ISNULL;