1.1 --- a/core.sql	Sun Aug 19 19:04:22 2012 +0200
     1.2 +++ b/core.sql	Sun Aug 19 20:29:44 2012 +0200
     1.3 @@ -1020,12 +1020,37 @@
     1.4          PRIMARY KEY ("issue_id", "member_id"),
     1.5          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
     1.6          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
     1.7 -        "weight"                INT4 );
     1.8 +        "weight"                INT4,
     1.9 +        "comment_changed"       TIMESTAMPTZ,
    1.10 +        "formatting_engine"     TEXT,
    1.11 +        "comment"               TEXT,
    1.12 +        "text_search_data"      TSVECTOR );
    1.13  CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
    1.14 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
    1.15 +CREATE TRIGGER "update_text_search_data"
    1.16 +  BEFORE INSERT OR UPDATE ON "direct_voter"
    1.17 +  FOR EACH ROW EXECUTE PROCEDURE
    1.18 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
    1.19  
    1.20  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.21  
    1.22 -COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.23 +COMMENT ON COLUMN "direct_voter"."weight"            IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.24 +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.25 +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.26 +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.27 +
    1.28 +
    1.29 +CREATE TABLE "rendered_voter_comment" (
    1.30 +        PRIMARY KEY ("issue_id", "member_id", "format"),
    1.31 +        FOREIGN KEY ("issue_id", "member_id")
    1.32 +          REFERENCES "direct_voter" ("issue_id", "member_id")
    1.33 +          ON DELETE CASCADE ON UPDATE CASCADE,
    1.34 +        "issue_id"              INT4,
    1.35 +        "member_id"             INT4,
    1.36 +        "format"                TEXT,
    1.37 +        "content"               TEXT            NOT NULL );
    1.38 +
    1.39 +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.40  
    1.41  
    1.42  CREATE TABLE "delegating_voter" (
    1.43 @@ -1060,39 +1085,6 @@
    1.44  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.';
    1.45  
    1.46  
    1.47 -CREATE TABLE "voting_comment" (
    1.48 -        PRIMARY KEY ("issue_id", "member_id"),
    1.49 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.50 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.51 -        "changed"               TIMESTAMPTZ,
    1.52 -        "formatting_engine"     TEXT,
    1.53 -        "content"               TEXT            NOT NULL,
    1.54 -        "text_search_data"      TSVECTOR );
    1.55 -CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
    1.56 -CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
    1.57 -CREATE TRIGGER "update_text_search_data"
    1.58 -  BEFORE INSERT OR UPDATE ON "voting_comment"
    1.59 -  FOR EACH ROW EXECUTE PROCEDURE
    1.60 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
    1.61 -
    1.62 -COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
    1.63 -
    1.64 -COMMENT ON COLUMN "voting_comment"."changed" 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.65 -
    1.66 -
    1.67 -CREATE TABLE "rendered_voting_comment" (
    1.68 -        PRIMARY KEY ("issue_id", "member_id", "format"),
    1.69 -        FOREIGN KEY ("issue_id", "member_id")
    1.70 -          REFERENCES "voting_comment" ("issue_id", "member_id")
    1.71 -          ON DELETE CASCADE ON UPDATE CASCADE,
    1.72 -        "issue_id"              INT4,
    1.73 -        "member_id"             INT4,
    1.74 -        "format"                TEXT,
    1.75 -        "content"               TEXT            NOT NULL );
    1.76 -
    1.77 -COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
    1.78 -
    1.79 -
    1.80  CREATE TYPE "event_type" AS ENUM (
    1.81          'issue_state_changed',
    1.82          'initiative_created_in_new_issue',
    1.83 @@ -1548,6 +1540,26 @@
    1.84  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.85  
    1.86  
    1.87 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
    1.88 +  RETURNS TRIGGER
    1.89 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.90 +    BEGIN
    1.91 +      IF NEW."comment" ISNULL THEN
    1.92 +        NEW."comment_changed" := NULL;
    1.93 +        NEW."formatting_engine" := NULL;
    1.94 +      END IF;
    1.95 +      RETURN NEW;
    1.96 +    END;
    1.97 +  $$;
    1.98 +
    1.99 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
   1.100 +  BEFORE INSERT OR UPDATE ON "direct_voter"
   1.101 +  FOR EACH ROW EXECUTE PROCEDURE
   1.102 +  "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
   1.103 +
   1.104 +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.105 +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.106 +
   1.107  
   1.108  ---------------------------------------------------------------
   1.109  -- Ensure that votes are not modified when issues are frozen --
   1.110 @@ -3735,6 +3747,9 @@
   1.111        PERFORM "lock_issue"("issue_id_p");
   1.112        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.113        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.114 +      -- delete timestamp of voting comment:
   1.115 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   1.116 +        WHERE "issue_id" = "issue_id_p";
   1.117        -- delete delegating votes (in cases of manual reset of issue state):
   1.118        DELETE FROM "delegating_voter"
   1.119          WHERE "issue_id" = "issue_id_p";
   1.120 @@ -4312,8 +4327,6 @@
   1.121            "closed"          = NULL,
   1.122            "ranks_available" = FALSE
   1.123            WHERE "id" = "issue_id_p";
   1.124 -        DELETE FROM "voting_comment"
   1.125 -          WHERE "issue_id" = "issue_id_p";
   1.126          DELETE FROM "delegating_voter"
   1.127            WHERE "issue_id" = "issue_id_p";
   1.128          DELETE FROM "direct_voter"