liquid_feedback_core

changeset 148:ec1fdf2fc8c9

Placed issue based autoreject feature inside own relation "issue_autoreject" to allow autoreject without interest
(also added TODO message related to minor bug with autoreject ballot insertion)
author jbe
date Thu Jun 02 01:53:04 2011 +0200 (2011-06-02)
parents 37a264fb5eef
children a07cfe298806
files core.sql
line diff
     1.1 --- a/core.sql	Thu Jun 02 01:25:08 2011 +0200
     1.2 +++ b/core.sql	Thu Jun 02 01:53:04 2011 +0200
     1.3 @@ -741,19 +741,26 @@
     1.4  
     1.5  COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
     1.6  
     1.7 -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.8 +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.9  
    1.10  
    1.11  CREATE TABLE "interest" (
    1.12          PRIMARY KEY ("issue_id", "member_id"),
    1.13          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.14 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.15 -        "autoreject"            BOOLEAN );
    1.16 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.17  CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
    1.18  
    1.19  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.20  
    1.21 -COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
    1.22 +
    1.23 +CREATE TABLE "issue_autoreject" (
    1.24 +        PRIMARY KEY ("issue_id", "member_id"),
    1.25 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.26 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.27 +        "autoreject"            BOOLEAN         NOT NULL );
    1.28 +CREATE INDEX "issue_autoreject_member_id_idx" ON "issue_autoreject" ("member_id");
    1.29 +
    1.30 +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.31  
    1.32  
    1.33  CREATE TABLE "initiator" (
    1.34 @@ -840,10 +847,10 @@
    1.35          "weight"                INT4 );
    1.36  CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
    1.37  
    1.38 -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.39 -
    1.40 -COMMENT ON COLUMN "direct_population_snapshot"."event"           IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.41 -COMMENT ON COLUMN "direct_population_snapshot"."weight"          IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
    1.42 +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.43 +
    1.44 +COMMENT ON COLUMN "direct_population_snapshot"."event"  IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.45 +COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
    1.46  
    1.47  
    1.48  CREATE TABLE "delegating_population_snapshot" (
    1.49 @@ -2832,6 +2839,20 @@
    1.50            ON "privilege"."unit_id" = "area"."unit_id"
    1.51            AND "privilege"."member_id" = "member"."id"
    1.52          WHERE "issue"."id" = "issue_id_p"
    1.53 +        AND "member"."active" AND "privilege"."voting_right"
    1.54 +        UNION
    1.55 +        SELECT
    1.56 +          "issue_id_p"                 AS "issue_id",
    1.57 +          'periodic'::"snapshot_event" AS "event",
    1.58 +          "member"."id"                AS "member_id"
    1.59 +        FROM "issue"
    1.60 +        JOIN "area" ON "issue"."area_id" = "area"."id"
    1.61 +        JOIN "issue_autoreject" ON "issue"."id" = "issue_autoreject"."issue_id"
    1.62 +        JOIN "member" ON "issue_autoreject"."member_id" = "member"."id"
    1.63 +        JOIN "privilege"
    1.64 +          ON "privilege"."unit_id" = "area"."unit_id"
    1.65 +          AND "privilege"."member_id" = "member"."id"
    1.66 +        WHERE "issue"."id" = "issue_id_p"
    1.67          AND "member"."active" AND "privilege"."voting_right";
    1.68        FOR "member_id_v" IN
    1.69          SELECT "member_id" FROM "direct_population_snapshot"
    1.70 @@ -3445,35 +3466,36 @@
    1.71          WHERE "issue_id" = "issue_id_p";
    1.72        PERFORM "add_vote_delegations"("issue_id_p");
    1.73        FOR "member_id_v" IN
    1.74 -        SELECT "interest"."member_id"
    1.75 -          FROM "interest"
    1.76 +        SELECT "issue_autoreject"."member_id"
    1.77 +          FROM "issue_autoreject"
    1.78            JOIN "member"
    1.79 -            ON "interest"."member_id" = "member"."id"
    1.80 +            ON "issue_autoreject"."member_id" = "member"."id"
    1.81            JOIN "privilege"
    1.82              ON "privilege"."unit_id" = "unit_id_v"
    1.83              AND "privilege"."member_id" = "member"."id"
    1.84            LEFT JOIN "direct_voter"
    1.85 -            ON "interest"."member_id" = "direct_voter"."member_id"
    1.86 -            AND "interest"."issue_id" = "direct_voter"."issue_id"
    1.87 +            ON "issue_autoreject"."member_id" = "direct_voter"."member_id"
    1.88 +            AND "issue_autoreject"."issue_id" = "direct_voter"."issue_id"
    1.89            LEFT JOIN "delegating_voter"
    1.90 -            ON "interest"."member_id" = "delegating_voter"."member_id"
    1.91 -            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    1.92 -          WHERE "interest"."issue_id" = "issue_id_p"
    1.93 -          AND "interest"."autoreject" = TRUE
    1.94 +            ON "issue_autoreject"."member_id" = "delegating_voter"."member_id"
    1.95 +            AND "issue_autoreject"."issue_id" = "delegating_voter"."issue_id"
    1.96 +          WHERE "issue_autoreject"."issue_id" = "issue_id_p"
    1.97 +          AND "issue_autoreject"."autoreject" = TRUE
    1.98            AND "member"."active"
    1.99            AND "privilege"."voting_right"
   1.100            AND "direct_voter"."member_id" ISNULL
   1.101            AND "delegating_voter"."member_id" ISNULL
   1.102 -        UNION SELECT "membership"."member_id"
   1.103 +        UNION
   1.104 +        SELECT "membership"."member_id"
   1.105            FROM "membership"
   1.106            JOIN "member"
   1.107              ON "membership"."member_id" = "member"."id"
   1.108            JOIN "privilege"
   1.109              ON "privilege"."unit_id" = "unit_id_v"
   1.110              AND "privilege"."member_id" = "member"."id"
   1.111 -          LEFT JOIN "interest"
   1.112 -            ON "membership"."member_id" = "interest"."member_id"
   1.113 -            AND "interest"."issue_id" = "issue_id_p"
   1.114 +          LEFT JOIN "issue_autoreject"
   1.115 +            ON "membership"."member_id" = "issue_autoreject"."member_id"
   1.116 +            AND "issue_autoreject"."issue_id" = "issue_id_p"
   1.117            LEFT JOIN "direct_voter"
   1.118              ON "membership"."member_id" = "direct_voter"."member_id"
   1.119              AND "direct_voter"."issue_id" = "issue_id_p"
   1.120 @@ -3484,7 +3506,7 @@
   1.121            AND "membership"."autoreject" = TRUE
   1.122            AND "member"."active"
   1.123            AND "privilege"."voting_right"
   1.124 -          AND "interest"."autoreject" ISNULL
   1.125 +          AND "issue_autoreject"."autoreject" ISNULL
   1.126            AND "direct_voter"."member_id" ISNULL
   1.127            AND "delegating_voter"."member_id" ISNULL
   1.128        LOOP
   1.129 @@ -3502,6 +3524,7 @@
   1.130              "id"          AS "initiative_id",
   1.131              -1            AS "grade"
   1.132            FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.133 +        -- TODO: admitted initiatives only?
   1.134        END LOOP;
   1.135        PERFORM "add_vote_delegations"("issue_id_p");
   1.136        -- set voter count and mark issue as being calculated:

Impressum / About Us