1.1 --- a/core.sql	Sun Jul 14 00:42:11 2013 +0200
     1.2 +++ b/core.sql	Sun Aug 11 21:34:58 2013 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
     1.8 +  SELECT * FROM (VALUES ('2.2.5', 2, 2, 5))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -506,6 +506,7 @@
    1.13  
    1.14  CREATE TYPE "issue_state" AS ENUM (
    1.15          'admission', 'discussion', 'verification', 'voting',
    1.16 +        'canceled_by_admin',
    1.17          'canceled_revoked_before_accepted',
    1.18          'canceled_issue_not_accepted',
    1.19          'canceled_after_revocation_during_discussion',
    1.20 @@ -520,6 +521,7 @@
    1.21          "id"                    SERIAL4         PRIMARY KEY,
    1.22          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.23          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.24 +        "admin_notice"          TEXT,
    1.25          "state"                 "issue_state"   NOT NULL DEFAULT 'admission',
    1.26          "phase_finished"        TIMESTAMPTZ,
    1.27          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.28 @@ -550,6 +552,7 @@
    1.29              ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
    1.30              ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
    1.31              ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
    1.32 +            ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
    1.33              ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.34              ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.35              ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
    1.36 @@ -585,6 +588,7 @@
    1.37  
    1.38  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.39  
    1.40 +COMMENT ON COLUMN "issue"."admin_notice"            IS 'Public notice by admin to explain manual interventions, or to announce corrections';
    1.41  COMMENT ON COLUMN "issue"."phase_finished"          IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.42  COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.43  COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.44 @@ -959,7 +963,7 @@
    1.45          "weight"                INT4 );
    1.46  CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
    1.47  
    1.48 -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.49 +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';
    1.50  
    1.51  COMMENT ON COLUMN "direct_population_snapshot"."event"  IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.52  COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
    1.53 @@ -975,7 +979,7 @@
    1.54          "delegate_member_ids"   INT4[]          NOT NULL );
    1.55  CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
    1.56  
    1.57 -COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
    1.58 +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';
    1.59  
    1.60  COMMENT ON COLUMN "delegating_population_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.61  COMMENT ON COLUMN "delegating_population_snapshot"."member_id"           IS 'Delegating member';
    1.62 @@ -991,7 +995,7 @@
    1.63          "weight"                INT4 );
    1.64  CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
    1.65  
    1.66 -COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
    1.67 +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';
    1.68  
    1.69  COMMENT ON COLUMN "direct_interest_snapshot"."event"            IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.70  COMMENT ON COLUMN "direct_interest_snapshot"."weight"           IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
    1.71 @@ -1007,7 +1011,7 @@
    1.72          "delegate_member_ids"   INT4[]          NOT NULL );
    1.73  CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
    1.74  
    1.75 -COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
    1.76 +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';
    1.77  
    1.78  COMMENT ON COLUMN "delegating_interest_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.79  COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
    1.80 @@ -1029,7 +1033,7 @@
    1.81          FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.82  CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
    1.83  
    1.84 -COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
    1.85 +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';
    1.86  
    1.87  COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id"  IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    1.88  COMMENT ON COLUMN "direct_supporter_snapshot"."event"     IS 'Reason for snapshot, see "snapshot_event" type for details';
    1.89 @@ -1062,7 +1066,7 @@
    1.90    FOR EACH ROW EXECUTE PROCEDURE
    1.91    tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
    1.92  
    1.93 -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.94 +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';
    1.95  
    1.96  COMMENT ON COLUMN "direct_voter"."weight"            IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.97  COMMENT ON COLUMN "direct_voter"."comment_changed"   IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
    1.98 @@ -1092,7 +1096,7 @@
    1.99          "delegate_member_ids"   INT4[]          NOT NULL );
   1.100  CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
   1.101  
   1.102 -COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
   1.103 +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';
   1.104  
   1.105  COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
   1.106  COMMENT ON COLUMN "delegating_voter"."weight"              IS 'Intermediate weight';
   1.107 @@ -1109,7 +1113,7 @@
   1.108          FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.109  CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
   1.110  
   1.111 -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.';
   1.112 +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';
   1.113  
   1.114  COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
   1.115  COMMENT ON COLUMN "vote"."grade"    IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';

     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v2.2.4-v2.2.5.sql	Sun Aug 11 21:34:58 2013 +0200
     2.3 @@ -0,0 +1,46 @@
     2.4 +-- NOTICE: The following command cannot be executed within a transaction block
     2.5 +--         and must be rolled back manually, if the update fails:
     2.6 +ALTER TYPE "issue_state" ADD VALUE 'canceled_by_admin' AFTER 'voting';
     2.7 +
     2.8 +BEGIN;
     2.9 +
    2.10 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    2.11 +  SELECT * FROM (VALUES ('2.2.5', 2, 2, 5))
    2.12 +  AS "subquery"("string", "major", "minor", "revision");
    2.13 +
    2.14 +ALTER TABLE "issue" ADD COLUMN "admin_notice" TEXT;
    2.15 +COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
    2.16 +
    2.17 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
    2.18 +ALTER TABLE "issue" ADD
    2.19 +        CONSTRAINT "valid_state" CHECK (
    2.20 +          (
    2.21 +            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    2.22 +            ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    2.23 +            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
    2.24 +            ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
    2.25 +          ) AND (
    2.26 +            ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
    2.27 +            ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
    2.28 +            ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
    2.29 +            ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
    2.30 +            ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
    2.31 +            ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
    2.32 +            ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
    2.33 +            ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
    2.34 +            ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
    2.35 +            ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
    2.36 +            ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
    2.37 +            ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
    2.38 +          ));
    2.39 +
    2.40 +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';
    2.41 +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';
    2.42 +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';
    2.43 +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';
    2.44 +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';
    2.45 +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';
    2.46 +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';
    2.47 +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';
    2.48 +
    2.49 +COMMIT;