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"