liquid_feedback_core
annotate update/core-update.v2.2.4-v2.2.5.sql @ 423:73c2ab2d068f
Work on configuration of complexity of counting of the votes (extent of tie-breaking):
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
| author | jbe |
|---|---|
| date | Thu Apr 10 00:20:03 2014 +0200 (2014-04-10) |
| parents | 992ec266356d |
| children |
| rev | line source |
|---|---|
| jbe@390 | 1 -- NOTICE: This update script requires PostgreSQL version 9.1 or higher. |
| jbe@390 | 2 -- If you use an older version, please dump your database and |
| jbe@390 | 3 -- update it on a system with PostgreSQL 9.1 or higher, before |
| jbe@390 | 4 -- restoring it on your original system. |
| jbe@390 | 5 |
| jbe@389 | 6 -- NOTICE: The following command cannot be executed within a transaction block |
| jbe@389 | 7 -- and must be rolled back manually, if the update fails: |
| jbe@389 | 8 ALTER TYPE "issue_state" ADD VALUE 'canceled_by_admin' AFTER 'voting'; |
| jbe@389 | 9 |
| jbe@389 | 10 BEGIN; |
| jbe@389 | 11 |
| jbe@389 | 12 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
| jbe@389 | 13 SELECT * FROM (VALUES ('2.2.5', 2, 2, 5)) |
| jbe@389 | 14 AS "subquery"("string", "major", "minor", "revision"); |
| jbe@389 | 15 |
| jbe@389 | 16 ALTER TABLE "issue" ADD COLUMN "admin_notice" TEXT; |
| jbe@389 | 17 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; |
| jbe@389 | 18 |
| jbe@389 | 19 ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; |
| jbe@389 | 20 ALTER TABLE "issue" ADD |
| jbe@389 | 21 CONSTRAINT "valid_state" CHECK ( |
| jbe@389 | 22 ( |
| jbe@389 | 23 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR |
| jbe@389 | 24 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR |
| jbe@389 | 25 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR |
| jbe@389 | 26 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL) |
| jbe@389 | 27 ) AND ( |
| jbe@389 | 28 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR |
| jbe@389 | 29 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR |
| jbe@389 | 30 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR |
| jbe@389 | 31 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR |
| jbe@389 | 32 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR |
| jbe@389 | 33 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR |
| jbe@389 | 34 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR |
| jbe@389 | 35 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR |
| jbe@389 | 36 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR |
| jbe@389 | 37 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR |
| jbe@389 | 38 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR |
| jbe@389 | 39 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") |
| jbe@389 | 40 )); |
| jbe@389 | 41 |
| jbe@389 | 42 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 43 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 44 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 45 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 46 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 47 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; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 48 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 49 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; frontends must ensure that no votes are added modified or removed when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table'; |
| jbe@389 | 50 |
| jbe@389 | 51 COMMIT; |