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)
(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: