1.1 --- a/update/core-update.v2.0.11-v2.1.0.sql	Sun Aug 19 19:04:22 2012 +0200
     1.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql	Sun Aug 19 20:29:44 2012 +0200
     1.3 @@ -34,8 +34,37 @@
     1.4  COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
     1.5  COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
     1.6  
     1.7 +ALTER TABLE "direct_voter" ADD COLUMN "comment_changed"   TIMESTAMPTZ;
     1.8 +ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
     1.9 +ALTER TABLE "direct_voter" ADD COLUMN "comment"           TEXT;
    1.10 +ALTER TABLE "direct_voter" ADD COLUMN "text_search_data"  TSVECTOR;
    1.11 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
    1.12 +CREATE TRIGGER "update_text_search_data"
    1.13 +  BEFORE INSERT OR UPDATE ON "direct_voter"
    1.14 +  FOR EACH ROW EXECUTE PROCEDURE
    1.15 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
    1.16 +
    1.17 +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.18 +COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
    1.19 +COMMENT ON COLUMN "direct_voter"."comment"           IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
    1.20 +
    1.21 +CREATE TABLE "rendered_voter_comment" (
    1.22 +        PRIMARY KEY ("issue_id", "member_id", "format"),
    1.23 +        FOREIGN KEY ("issue_id", "member_id")
    1.24 +          REFERENCES "direct_voter" ("issue_id", "member_id")
    1.25 +          ON DELETE CASCADE ON UPDATE CASCADE,
    1.26 +        "issue_id"              INT4,
    1.27 +        "member_id"             INT4,
    1.28 +        "format"                TEXT,
    1.29 +        "content"               TEXT            NOT NULL );
    1.30 +
    1.31 +COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
    1.32 +
    1.33 +
    1.34  DROP TABLE "rendered_issue_comment";
    1.35  DROP TABLE "issue_comment";
    1.36 +DROP TABLE "rendered_voting_comment";
    1.37 +DROP TABLE "voting_comment";
    1.38  
    1.39  CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
    1.40    RETURNS TRIGGER
    1.41 @@ -73,6 +102,26 @@
    1.42  COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"()        IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
    1.43  COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
    1.44  
    1.45 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
    1.46 +  RETURNS TRIGGER
    1.47 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.48 +    BEGIN
    1.49 +      IF NEW."comment" ISNULL THEN
    1.50 +        NEW."comment_changed" := NULL;
    1.51 +        NEW."formatting_engine" := NULL;
    1.52 +      END IF;
    1.53 +      RETURN NEW;
    1.54 +    END;
    1.55 +  $$;
    1.56 +
    1.57 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
    1.58 +  BEFORE INSERT OR UPDATE ON "direct_voter"
    1.59 +  FOR EACH ROW EXECUTE PROCEDURE
    1.60 +  "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
    1.61 +
    1.62 +COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
    1.63 +COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
    1.64 +
    1.65  CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
    1.66    ( "issue_id_p" "issue"."id"%TYPE )
    1.67    RETURNS VOID
    1.68 @@ -149,8 +198,6 @@
    1.69            "closed"          = NULL,
    1.70            "ranks_available" = FALSE
    1.71            WHERE "id" = "issue_id_p";
    1.72 -        DELETE FROM "voting_comment"
    1.73 -          WHERE "issue_id" = "issue_id_p";
    1.74          DELETE FROM "delegating_voter"
    1.75            WHERE "issue_id" = "issue_id_p";
    1.76          DELETE FROM "direct_voter"
    1.77 @@ -180,6 +227,81 @@
    1.78      END;
    1.79    $$;
    1.80  
    1.81 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    1.82 +  RETURNS VOID
    1.83 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.84 +    DECLARE
    1.85 +      "area_id_v"   "area"."id"%TYPE;
    1.86 +      "unit_id_v"   "unit"."id"%TYPE;
    1.87 +      "member_id_v" "member"."id"%TYPE;
    1.88 +    BEGIN
    1.89 +      PERFORM "lock_issue"("issue_id_p");
    1.90 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    1.91 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    1.92 +      -- delete timestamp of voting comment:
    1.93 +      UPDATE "direct_voter" SET "comment_changed" = NULL
    1.94 +        WHERE "issue_id" = "issue_id_p";
    1.95 +      -- delete delegating votes (in cases of manual reset of issue state):
    1.96 +      DELETE FROM "delegating_voter"
    1.97 +        WHERE "issue_id" = "issue_id_p";
    1.98 +      -- delete votes from non-privileged voters:
    1.99 +      DELETE FROM "direct_voter"
   1.100 +        USING (
   1.101 +          SELECT
   1.102 +            "direct_voter"."member_id"
   1.103 +          FROM "direct_voter"
   1.104 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.105 +          LEFT JOIN "privilege"
   1.106 +          ON "privilege"."unit_id" = "unit_id_v"
   1.107 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.108 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.109 +            "member"."active" = FALSE OR
   1.110 +            "privilege"."voting_right" ISNULL OR
   1.111 +            "privilege"."voting_right" = FALSE
   1.112 +          )
   1.113 +        ) AS "subquery"
   1.114 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.115 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.116 +      -- consider delegations:
   1.117 +      UPDATE "direct_voter" SET "weight" = 1
   1.118 +        WHERE "issue_id" = "issue_id_p";
   1.119 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.120 +      -- set voter count and mark issue as being calculated:
   1.121 +      UPDATE "issue" SET
   1.122 +        "state"  = 'calculation',
   1.123 +        "closed" = now(),
   1.124 +        "voter_count" = (
   1.125 +          SELECT coalesce(sum("weight"), 0)
   1.126 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.127 +        )
   1.128 +        WHERE "id" = "issue_id_p";
   1.129 +      -- materialize battle_view:
   1.130 +      -- NOTE: "closed" column of issue must be set at this point
   1.131 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.132 +      INSERT INTO "battle" (
   1.133 +        "issue_id",
   1.134 +        "winning_initiative_id", "losing_initiative_id",
   1.135 +        "count"
   1.136 +      ) SELECT
   1.137 +        "issue_id",
   1.138 +        "winning_initiative_id", "losing_initiative_id",
   1.139 +        "count"
   1.140 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.141 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.142 +      UPDATE "initiative" SET
   1.143 +        "positive_votes" = "battle_win"."count",
   1.144 +        "negative_votes" = "battle_lose"."count"
   1.145 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   1.146 +        WHERE
   1.147 +          "battle_win"."issue_id" = "issue_id_p" AND
   1.148 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   1.149 +          "battle_win"."losing_initiative_id" ISNULL AND
   1.150 +          "battle_lose"."issue_id" = "issue_id_p" AND
   1.151 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   1.152 +          "battle_lose"."winning_initiative_id" ISNULL;
   1.153 +    END;
   1.154 +  $$;
   1.155 +
   1.156  COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
   1.157  
   1.158  COMMIT;