liquid_feedback_core
changeset 568:b1326d149247
New columns "issue_quorum" and "initiative_quorum" for "issue" table
author | jbe |
---|---|
date | Fri Sep 29 01:57:43 2017 +0200 (2017-09-29) |
parents | 797282760db4 |
children | 622c71ca6428 |
files | core.sql update/core-update.v3.2.2-v4.0.0.sql |
line diff
1.1 --- a/core.sql Thu Sep 28 20:12:23 2017 +0200 1.2 +++ b/core.sql Fri Sep 29 01:57:43 2017 +0200 1.3 @@ -769,6 +769,8 @@ 1.4 "full_freeze_snapshot_id" INT8, 1.5 FOREIGN KEY ("id", "full_freeze_snapshot_id") 1.6 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.7 + "issue_quorum" INT4, 1.8 + "initiative_quorum" INT4, 1.9 "population" INT4, 1.10 "voter_count" INT4, 1.11 "status_quo_schulze_rank" INT4, 1.12 @@ -841,6 +843,8 @@ 1.13 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase'; 1.14 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase'; 1.15 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase'; 1.16 +COMMENT ON COLUMN "issue"."issue_quorum" IS 'Calculated number of supporters needed by an initiative of the issue to be "accepted", i.e. pass from ''admission'' to ''discussion'' state'; 1.17 +COMMENT ON COLUMN "issue"."initiative_quorum" IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting'; 1.18 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"'; 1.19 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 1.20 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.21 @@ -3324,6 +3328,30 @@ 1.22 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues'; 1.23 1.24 1.25 +CREATE VIEW "issue_quorum" AS 1.26 + SELECT DISTINCT ON ("issue_id") 1.27 + "issue"."id" AS "issue_id", 1.28 + "subquery"."issue_quorum" 1.29 + FROM "issue" 1.30 + CROSS JOIN LATERAL ( 1.31 + SELECT "area_quorum"."issue_quorum" 1.32 + FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id" 1.33 + UNION ALL 1.34 + SELECT "policy"."issue_quorum" 1.35 + FROM "policy" WHERE "policy"."id" = "issue"."policy_id" 1.36 + UNION ALL 1.37 + SELECT 1.38 + ceil( 1.39 + ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) / 1.40 + "policy"."issue_quorum_den"::FLOAT8 1.41 + )::INT4 1.42 + FROM "policy" WHERE "policy"."id" = "issue"."policy_id" 1.43 + ) AS "subquery" 1.44 + ORDER BY "issue_id", "issue_quorum" DESC; 1.45 + 1.46 +COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission'; 1.47 + 1.48 + 1.49 CREATE VIEW "area_with_unaccepted_issues" AS 1.50 SELECT DISTINCT ON ("area"."id") "area".* 1.51 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id" 1.52 @@ -5122,10 +5150,26 @@ 1.53 UPDATE "issue" SET 1.54 "calculated" = "snapshot"."calculated", 1.55 "latest_snapshot_id" = "snapshot_id_v", 1.56 - "population" = "snapshot"."population" 1.57 - FROM "snapshot" 1.58 + "population" = "snapshot"."population", 1.59 + "initiative_quorum" = CASE WHEN 1.60 + "policy"."initiative_quorum" > ceil( 1.61 + ( "issue"."population"::INT8 * 1.62 + "policy"."initiative_quorum_num"::INT8 ) / 1.63 + "policy"."initiative_quorum_den"::FLOAT8 1.64 + )::INT4 1.65 + THEN 1.66 + "policy"."initiative_quorum" 1.67 + ELSE 1.68 + ceil( 1.69 + ( "issue"."population"::INT8 * 1.70 + "policy"."initiative_quorum_num"::INT8 ) / 1.71 + "policy"."initiative_quorum_den"::FLOAT8 1.72 + )::INT4 1.73 + END 1.74 + FROM "snapshot", "policy" 1.75 WHERE "issue"."id" = "issue_id_p" 1.76 - AND "snapshot"."id" = "snapshot_id_v"; 1.77 + AND "snapshot"."id" = "snapshot_id_v" 1.78 + AND "policy"."id" = "issue"."policy_id"; 1.79 UPDATE "initiative" SET 1.80 "supporter_count" = ( 1.81 SELECT coalesce(sum("di"."weight"), 0) 1.82 @@ -5944,8 +5988,11 @@ 1.83 "admission_snapshot_id" = "latest_snapshot_id", 1.84 "state" = 'discussion', 1.85 "accepted" = now(), 1.86 - "phase_finished" = NULL 1.87 - WHERE "id" = "issue_id_v"; 1.88 + "phase_finished" = NULL, 1.89 + "issue_quorum" = "issue_quorum"."issue_quorum" 1.90 + FROM "issue_quorum" 1.91 + WHERE "id" = "issue_id_v" 1.92 + AND "issue_quorum"."issue_id" = "issue_id_v"; 1.93 RETURN TRUE; 1.94 END; 1.95 $$; 1.96 @@ -6046,6 +6093,11 @@ 1.97 IF "persist"."state" != 'admission' THEN 1.98 PERFORM "take_snapshot"("issue_id_p"); 1.99 PERFORM "finish_snapshot"("issue_id_p"); 1.100 + ELSE 1.101 + UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum" 1.102 + FROM "issue_quorum" 1.103 + WHERE "id" = "issue_id_p" 1.104 + AND "issue_quorum"."issue_id" = "issue_id_p"; 1.105 END IF; 1.106 "persist"."snapshot_created" = TRUE; 1.107 IF "persist"."phase_finished" THEN 1.108 @@ -6059,21 +6111,15 @@ 1.109 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id" 1.110 WHERE "id" = "issue_id_p"; 1.111 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.112 - SELECT * INTO "policy_row" FROM "policy" 1.113 - WHERE "id" = "issue_row"."policy_id"; 1.114 FOR "initiative_row" IN 1.115 SELECT * FROM "initiative" 1.116 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.117 FOR UPDATE 1.118 LOOP 1.119 IF 1.120 - "initiative_row"."polling" OR ( 1.121 - "initiative_row"."satisfied_supporter_count" >= 1.122 - "policy_row"."initiative_quorum" AND 1.123 - "initiative_row"."satisfied_supporter_count" * 1.124 - "policy_row"."initiative_quorum_den" >= 1.125 - "issue_row"."population" * "policy_row"."initiative_quorum_num" 1.126 - ) 1.127 + "initiative_row"."polling" OR 1.128 + "initiative_row"."satisfied_supporter_count" >= 1.129 + "issue_row"."initiative_quorum" 1.130 THEN 1.131 UPDATE "initiative" SET "admitted" = TRUE 1.132 WHERE "id" = "initiative_row"."id";
2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql Thu Sep 28 20:12:23 2017 +0200 2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Fri Sep 29 01:57:43 2017 +0200 2.3 @@ -550,6 +550,8 @@ 2.4 ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE; 2.5 ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8; 2.6 ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8; 2.7 +ALTER TABLE "issue" ADD COLUMN "issue_quorum" INT4; 2.8 +ALTER TABLE "issue" ADD COLUMN "initiative_quorum" INT4; 2.9 2.10 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id") 2.11 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE; 2.12 @@ -572,6 +574,8 @@ 2.13 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase'; 2.14 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase'; 2.15 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase'; 2.16 +COMMENT ON COLUMN "issue"."issue_quorum" IS 'Calculated number of supporters needed by an initiative of the issue to be "accepted", i.e. pass from ''admission'' to ''discussion'' state'; 2.17 +COMMENT ON COLUMN "issue"."initiative_quorum" IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting'; 2.18 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"'; 2.19 2.20 2.21 @@ -2056,6 +2060,30 @@ 2.22 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues'; 2.23 2.24 2.25 +CREATE VIEW "issue_quorum" AS 2.26 + SELECT DISTINCT ON ("issue_id") 2.27 + "issue"."id" AS "issue_id", 2.28 + "subquery"."issue_quorum" 2.29 + FROM "issue" 2.30 + CROSS JOIN LATERAL ( 2.31 + SELECT "area_quorum"."issue_quorum" 2.32 + FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id" 2.33 + UNION ALL 2.34 + SELECT "policy"."issue_quorum" 2.35 + FROM "policy" WHERE "policy"."id" = "issue"."policy_id" 2.36 + UNION ALL 2.37 + SELECT 2.38 + ceil( 2.39 + ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) / 2.40 + "policy"."issue_quorum_den"::FLOAT8 2.41 + )::INT4 2.42 + FROM "policy" WHERE "policy"."id" = "issue"."policy_id" 2.43 + ) AS "subquery" 2.44 + ORDER BY "issue_id", "issue_quorum" DESC; 2.45 + 2.46 +COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission'; 2.47 + 2.48 + 2.49 CREATE VIEW "area_with_unaccepted_issues" AS 2.50 SELECT DISTINCT ON ("area"."id") "area".* 2.51 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id" 2.52 @@ -2905,10 +2933,26 @@ 2.53 UPDATE "issue" SET 2.54 "calculated" = "snapshot"."calculated", 2.55 "latest_snapshot_id" = "snapshot_id_v", 2.56 - "population" = "snapshot"."population" 2.57 - FROM "snapshot" 2.58 + "population" = "snapshot"."population", 2.59 + "initiative_quorum" = CASE WHEN 2.60 + "policy"."initiative_quorum" > ceil( 2.61 + ( "issue"."population"::INT8 * 2.62 + "policy"."initiative_quorum_num"::INT8 ) / 2.63 + "policy"."initiative_quorum_den"::FLOAT8 2.64 + )::INT4 2.65 + THEN 2.66 + "policy"."initiative_quorum" 2.67 + ELSE 2.68 + ceil( 2.69 + ( "issue"."population"::INT8 * 2.70 + "policy"."initiative_quorum_num"::INT8 ) / 2.71 + "policy"."initiative_quorum_den"::FLOAT8 2.72 + )::INT4 2.73 + END 2.74 + FROM "snapshot", "policy" 2.75 WHERE "issue"."id" = "issue_id_p" 2.76 - AND "snapshot"."id" = "snapshot_id_v"; 2.77 + AND "snapshot"."id" = "snapshot_id_v" 2.78 + AND "policy"."id" = "issue"."policy_id"; 2.79 UPDATE "initiative" SET 2.80 "supporter_count" = ( 2.81 SELECT coalesce(sum("di"."weight"), 0) 2.82 @@ -2998,8 +3042,11 @@ 2.83 "admission_snapshot_id" = "latest_snapshot_id", 2.84 "state" = 'discussion', 2.85 "accepted" = now(), 2.86 - "phase_finished" = NULL 2.87 - WHERE "id" = "issue_id_v"; 2.88 + "phase_finished" = NULL, 2.89 + "issue_quorum" = "issue_quorum"."issue_quorum" 2.90 + FROM "issue_quorum" 2.91 + WHERE "id" = "issue_id_v" 2.92 + AND "issue_quorum"."issue_id" = "issue_id_v"; 2.93 RETURN TRUE; 2.94 END; 2.95 $$; 2.96 @@ -3089,6 +3136,11 @@ 2.97 IF "persist"."state" != 'admission' THEN 2.98 PERFORM "take_snapshot"("issue_id_p"); 2.99 PERFORM "finish_snapshot"("issue_id_p"); 2.100 + ELSE 2.101 + UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum" 2.102 + FROM "issue_quorum" 2.103 + WHERE "id" = "issue_id_p" 2.104 + AND "issue_quorum"."issue_id" = "issue_id_p"; 2.105 END IF; 2.106 "persist"."snapshot_created" = TRUE; 2.107 IF "persist"."phase_finished" THEN 2.108 @@ -3102,21 +3154,15 @@ 2.109 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id" 2.110 WHERE "id" = "issue_id_p"; 2.111 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.112 - SELECT * INTO "policy_row" FROM "policy" 2.113 - WHERE "id" = "issue_row"."policy_id"; 2.114 FOR "initiative_row" IN 2.115 SELECT * FROM "initiative" 2.116 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.117 FOR UPDATE 2.118 LOOP 2.119 IF 2.120 - "initiative_row"."polling" OR ( 2.121 - "initiative_row"."satisfied_supporter_count" >= 2.122 - "policy_row"."initiative_quorum" AND 2.123 - "initiative_row"."satisfied_supporter_count" * 2.124 - "policy_row"."initiative_quorum_den" >= 2.125 - "issue_row"."population" * "policy_row"."initiative_quorum_num" 2.126 - ) 2.127 + "initiative_row"."polling" OR 2.128 + "initiative_row"."satisfied_supporter_count" >= 2.129 + "issue_row"."initiative_quorum" 2.130 THEN 2.131 UPDATE "initiative" SET "admitted" = TRUE 2.132 WHERE "id" = "initiative_row"."id";