liquid_feedback_core

changeset 169:ddd8e17d8f82

Complete removal of autoreject feature
author jbe
date Tue Jun 07 18:02:58 2011 +0200 (2011-06-07)
parents 9d6fe34d8bbb
children b52f3281e769
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Tue Jun 07 17:23:41 2011 +0200
     1.2 +++ b/core.sql	Tue Jun 07 18:02:58 2011 +0200
     1.3 @@ -392,7 +392,6 @@
     1.4          "description"           TEXT            NOT NULL DEFAULT '',
     1.5          "direct_member_count"   INT4,
     1.6          "member_weight"         INT4,
     1.7 -        "autoreject_weight"     INT4,
     1.8          "text_search_data"      TSVECTOR );
     1.9  CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
    1.10  CREATE INDEX "area_active_idx" ON "area" ("active");
    1.11 @@ -408,7 +407,6 @@
    1.12  COMMENT ON COLUMN "area"."active"              IS 'TRUE means new issues can be created in this area';
    1.13  COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
    1.14  COMMENT ON COLUMN "area"."member_weight"       IS 'Same as "direct_member_count" but respecting delegations';
    1.15 -COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
    1.16  
    1.17  
    1.18  CREATE TABLE "area_setting" (
    1.19 @@ -779,14 +777,11 @@
    1.20  CREATE TABLE "membership" (
    1.21          PRIMARY KEY ("area_id", "member_id"),
    1.22          "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.23 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.24 -        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
    1.25 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.26  CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
    1.27  
    1.28  COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
    1.29  
    1.30 -COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Entries in the "issue_autoreject" table can override this setting.';
    1.31 -
    1.32  
    1.33  CREATE TABLE "interest" (
    1.34          PRIMARY KEY ("issue_id", "member_id"),
    1.35 @@ -797,16 +792,6 @@
    1.36  COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
    1.37  
    1.38  
    1.39 -CREATE TABLE "issue_autoreject" (
    1.40 -        PRIMARY KEY ("issue_id", "member_id"),
    1.41 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.42 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.43 -        "autoreject"            BOOLEAN         NOT NULL );
    1.44 -CREATE INDEX "issue_autoreject_member_id_idx" ON "issue_autoreject" ("member_id");
    1.45 -
    1.46 -COMMENT ON TABLE "issue_autoreject" IS 'If autoreject=TRUE, then member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Values of either TRUE or FALSE override settings in "membership" table.';
    1.47 -
    1.48 -
    1.49  CREATE TABLE "initiator" (
    1.50          PRIMARY KEY ("initiative_id", "member_id"),
    1.51          "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.52 @@ -891,7 +876,7 @@
    1.53          "weight"                INT4 );
    1.54  CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
    1.55  
    1.56 -COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area", an "interest" in the "issue", or "issue_autoreject"."autoreject" set to TRUE';
    1.57 +COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
    1.58  
    1.59  COMMENT ON COLUMN "direct_population_snapshot"."event"  IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.60  COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
    1.61 @@ -979,14 +964,12 @@
    1.62          PRIMARY KEY ("issue_id", "member_id"),
    1.63          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.64          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
    1.65 -        "weight"                INT4,
    1.66 -        "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
    1.67 +        "weight"                INT4 );
    1.68  CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
    1.69  
    1.70  COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
    1.71  
    1.72  COMMENT ON COLUMN "direct_voter"."weight"     IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.73 -COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
    1.74  
    1.75  
    1.76  CREATE TABLE "delegating_voter" (
    1.77 @@ -1878,14 +1861,7 @@
    1.78            "membership_weight"("area"."id", "member"."id")
    1.79          ELSE 0 END
    1.80        )
    1.81 -    ) AS "member_weight",
    1.82 -    coalesce(
    1.83 -      sum(
    1.84 -        CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
    1.85 -          "membership_weight"("area"."id", "member"."id")
    1.86 -        ELSE 0 END
    1.87 -      )
    1.88 -    ) AS "autoreject_weight"
    1.89 +    ) AS "member_weight"
    1.90    FROM "area"
    1.91    LEFT JOIN "membership"
    1.92    ON "area"."id" = "membership"."area_id"
    1.93 @@ -1898,7 +1874,7 @@
    1.94    AND "member"."active"
    1.95    GROUP BY "area"."id";
    1.96  
    1.97 -COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
    1.98 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
    1.99  
   1.100  
   1.101  CREATE VIEW "opening_draft" AS
   1.102 @@ -2760,8 +2736,7 @@
   1.103          WHERE "view"."unit_id" = "unit"."id";
   1.104        UPDATE "area" SET
   1.105          "direct_member_count" = "view"."direct_member_count",
   1.106 -        "member_weight"       = "view"."member_weight",
   1.107 -        "autoreject_weight"   = "view"."autoreject_weight"
   1.108 +        "member_weight"       = "view"."member_weight"
   1.109          FROM "area_member_count" AS "view"
   1.110          WHERE "view"."area_id" = "area"."id";
   1.111        RETURN;
   1.112 @@ -2886,20 +2861,6 @@
   1.113            ON "privilege"."unit_id" = "area"."unit_id"
   1.114            AND "privilege"."member_id" = "member"."id"
   1.115          WHERE "issue"."id" = "issue_id_p"
   1.116 -        AND "member"."active" AND "privilege"."voting_right"
   1.117 -        UNION
   1.118 -        SELECT
   1.119 -          "issue_id_p"                 AS "issue_id",
   1.120 -          'periodic'::"snapshot_event" AS "event",
   1.121 -          "member"."id"                AS "member_id"
   1.122 -        FROM "issue"
   1.123 -        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.124 -        JOIN "issue_autoreject" ON "issue"."id" = "issue_autoreject"."issue_id"
   1.125 -        JOIN "member" ON "issue_autoreject"."member_id" = "member"."id"
   1.126 -        JOIN "privilege"
   1.127 -          ON "privilege"."unit_id" = "area"."unit_id"
   1.128 -          AND "privilege"."member_id" = "member"."id"
   1.129 -        WHERE "issue"."id" = "issue_id_p"
   1.130          AND "member"."active" AND "privilege"."voting_right";
   1.131        FOR "member_id_v" IN
   1.132          SELECT "member_id" FROM "direct_population_snapshot"
   1.133 @@ -3486,12 +3447,10 @@
   1.134        PERFORM "lock_issue"("issue_id_p");
   1.135        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.136        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.137 -      -- consider delegations and auto-reject:
   1.138 +      -- delete delegating votes (in cases of manual reset of issue state):
   1.139        DELETE FROM "delegating_voter"
   1.140          WHERE "issue_id" = "issue_id_p";
   1.141 -      DELETE FROM "direct_voter"
   1.142 -        WHERE "issue_id" = "issue_id_p"
   1.143 -        AND "autoreject" = TRUE;
   1.144 +      -- delete votes from non-privileged voters:
   1.145        DELETE FROM "direct_voter"
   1.146          USING (
   1.147            SELECT
   1.148 @@ -3509,71 +3468,10 @@
   1.149          ) AS "subquery"
   1.150          WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.151          AND "direct_voter"."member_id" = "subquery"."member_id";
   1.152 +      -- consider delegations:
   1.153        UPDATE "direct_voter" SET "weight" = 1
   1.154          WHERE "issue_id" = "issue_id_p";
   1.155        PERFORM "add_vote_delegations"("issue_id_p");
   1.156 -      FOR "member_id_v" IN
   1.157 -        SELECT "issue_autoreject"."member_id"
   1.158 -          FROM "issue_autoreject"
   1.159 -          JOIN "member"
   1.160 -            ON "issue_autoreject"."member_id" = "member"."id"
   1.161 -          JOIN "privilege"
   1.162 -            ON "privilege"."unit_id" = "unit_id_v"
   1.163 -            AND "privilege"."member_id" = "member"."id"
   1.164 -          LEFT JOIN "direct_voter"
   1.165 -            ON "issue_autoreject"."member_id" = "direct_voter"."member_id"
   1.166 -            AND "issue_autoreject"."issue_id" = "direct_voter"."issue_id"
   1.167 -          LEFT JOIN "delegating_voter"
   1.168 -            ON "issue_autoreject"."member_id" = "delegating_voter"."member_id"
   1.169 -            AND "issue_autoreject"."issue_id" = "delegating_voter"."issue_id"
   1.170 -          WHERE "issue_autoreject"."issue_id" = "issue_id_p"
   1.171 -          AND "issue_autoreject"."autoreject" = TRUE
   1.172 -          AND "member"."active"
   1.173 -          AND "privilege"."voting_right"
   1.174 -          AND "direct_voter"."member_id" ISNULL
   1.175 -          AND "delegating_voter"."member_id" ISNULL
   1.176 -        UNION
   1.177 -        SELECT "membership"."member_id"
   1.178 -          FROM "membership"
   1.179 -          JOIN "member"
   1.180 -            ON "membership"."member_id" = "member"."id"
   1.181 -          JOIN "privilege"
   1.182 -            ON "privilege"."unit_id" = "unit_id_v"
   1.183 -            AND "privilege"."member_id" = "member"."id"
   1.184 -          LEFT JOIN "issue_autoreject"
   1.185 -            ON "membership"."member_id" = "issue_autoreject"."member_id"
   1.186 -            AND "issue_autoreject"."issue_id" = "issue_id_p"
   1.187 -          LEFT JOIN "direct_voter"
   1.188 -            ON "membership"."member_id" = "direct_voter"."member_id"
   1.189 -            AND "direct_voter"."issue_id" = "issue_id_p"
   1.190 -          LEFT JOIN "delegating_voter"
   1.191 -            ON "membership"."member_id" = "delegating_voter"."member_id"
   1.192 -            AND "delegating_voter"."issue_id" = "issue_id_p"
   1.193 -          WHERE "membership"."area_id" = "area_id_v"
   1.194 -          AND "membership"."autoreject" = TRUE
   1.195 -          AND "member"."active"
   1.196 -          AND "privilege"."voting_right"
   1.197 -          AND "issue_autoreject"."autoreject" ISNULL
   1.198 -          AND "direct_voter"."member_id" ISNULL
   1.199 -          AND "delegating_voter"."member_id" ISNULL
   1.200 -      LOOP
   1.201 -        INSERT INTO "direct_voter"
   1.202 -          ("member_id", "issue_id", "weight", "autoreject") VALUES
   1.203 -          ("member_id_v", "issue_id_p", 1, TRUE);
   1.204 -        INSERT INTO "vote" (
   1.205 -          "member_id",
   1.206 -          "issue_id",
   1.207 -          "initiative_id",
   1.208 -          "grade"
   1.209 -          ) SELECT
   1.210 -            "member_id_v" AS "member_id",
   1.211 -            "issue_id_p"  AS "issue_id",
   1.212 -            "id"          AS "initiative_id",
   1.213 -            -1            AS "grade"
   1.214 -          FROM "initiative"
   1.215 -          WHERE "issue_id" = "issue_id_p" AND "admitted";
   1.216 -      END LOOP;
   1.217 -      PERFORM "add_vote_delegations"("issue_id_p");
   1.218        -- set voter count and mark issue as being calculated:
   1.219        UPDATE "issue" SET
   1.220          "state"  = 'calculation',
   1.221 @@ -4136,8 +4034,6 @@
   1.222            WHERE "issue_id" = "issue_id_p";
   1.223          DELETE FROM "supporter"
   1.224            WHERE "issue_id" = "issue_id_p";
   1.225 -        DELETE FROM "issue_autoreject"
   1.226 -          WHERE "issue_id" = "issue_id_p";
   1.227          UPDATE "issue" SET
   1.228            "state"           = "issue_row"."state",
   1.229            "closed"          = "issue_row"."closed",
     2.1 --- a/demo.sql	Tue Jun 07 17:23:41 2011 +0200
     2.2 +++ b/demo.sql	Tue Jun 07 18:02:58 2011 +0200
     2.3 @@ -80,23 +80,23 @@
     2.4  INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
     2.5    VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
     2.6  
     2.7 -INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
     2.8 -  (1,  9, FALSE),
     2.9 -  (1, 19, FALSE),
    2.10 -  (2,  9, TRUE),
    2.11 -  (2, 10, TRUE),
    2.12 -  (2, 17, TRUE),
    2.13 -  (3,  9, FALSE),
    2.14 -  (3, 11, FALSE),
    2.15 -  (3, 12, TRUE),
    2.16 -  (3, 14, FALSE),
    2.17 -  (3, 20, FALSE),
    2.18 -  (3, 21, TRUE),
    2.19 -  (3, 22, TRUE),
    2.20 -  (4,  6, FALSE),
    2.21 -  (4,  9, FALSE),
    2.22 -  (4, 13, FALSE),
    2.23 -  (4, 22, FALSE);
    2.24 +INSERT INTO "membership" ("area_id", "member_id") VALUES
    2.25 +  (1,  9),
    2.26 +  (1, 19),
    2.27 +  (2,  9),
    2.28 +  (2, 10),
    2.29 +  (2, 17),
    2.30 +  (3,  9),
    2.31 +  (3, 11),
    2.32 +  (3, 12),
    2.33 +  (3, 14),
    2.34 +  (3, 20),
    2.35 +  (3, 21),
    2.36 +  (3, 22),
    2.37 +  (4,  6),
    2.38 +  (4,  9),
    2.39 +  (4, 13),
    2.40 +  (4, 22);
    2.41  
    2.42  -- global delegations
    2.43  INSERT INTO "delegation"

Impressum / About Us