liquid_feedback_core

changeset 285:4868a7d591de

Moved voting comment to table "direct_voter"
author jbe
date Sun Aug 19 20:29:44 2012 +0200 (2012-08-19)
parents 4f935e989ff6
children 3ac6d4259387
files core.sql update/core-update.v2.0.11-v2.1.0.sql
line diff
     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"
     2.1 --- a/update/core-update.v2.0.11-v2.1.0.sql	Sun Aug 19 19:04:22 2012 +0200
     2.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql	Sun Aug 19 20:29:44 2012 +0200
     2.3 @@ -34,8 +34,37 @@
     2.4  COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
     2.5  COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
     2.6  
     2.7 +ALTER TABLE "direct_voter" ADD COLUMN "comment_changed"   TIMESTAMPTZ;
     2.8 +ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
     2.9 +ALTER TABLE "direct_voter" ADD COLUMN "comment"           TEXT;
    2.10 +ALTER TABLE "direct_voter" ADD COLUMN "text_search_data"  TSVECTOR;
    2.11 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
    2.12 +CREATE TRIGGER "update_text_search_data"
    2.13 +  BEFORE INSERT OR UPDATE ON "direct_voter"
    2.14 +  FOR EACH ROW EXECUTE PROCEDURE
    2.15 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
    2.16 +
    2.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';
    2.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';
    2.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.';
    2.20 +
    2.21 +CREATE TABLE "rendered_voter_comment" (
    2.22 +        PRIMARY KEY ("issue_id", "member_id", "format"),
    2.23 +        FOREIGN KEY ("issue_id", "member_id")
    2.24 +          REFERENCES "direct_voter" ("issue_id", "member_id")
    2.25 +          ON DELETE CASCADE ON UPDATE CASCADE,
    2.26 +        "issue_id"              INT4,
    2.27 +        "member_id"             INT4,
    2.28 +        "format"                TEXT,
    2.29 +        "content"               TEXT            NOT NULL );
    2.30 +
    2.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)';
    2.32 +
    2.33 +
    2.34  DROP TABLE "rendered_issue_comment";
    2.35  DROP TABLE "issue_comment";
    2.36 +DROP TABLE "rendered_voting_comment";
    2.37 +DROP TABLE "voting_comment";
    2.38  
    2.39  CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
    2.40    RETURNS TRIGGER
    2.41 @@ -73,6 +102,26 @@
    2.42  COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"()        IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
    2.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")';
    2.44  
    2.45 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
    2.46 +  RETURNS TRIGGER
    2.47 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.48 +    BEGIN
    2.49 +      IF NEW."comment" ISNULL THEN
    2.50 +        NEW."comment_changed" := NULL;
    2.51 +        NEW."formatting_engine" := NULL;
    2.52 +      END IF;
    2.53 +      RETURN NEW;
    2.54 +    END;
    2.55 +  $$;
    2.56 +
    2.57 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
    2.58 +  BEFORE INSERT OR UPDATE ON "direct_voter"
    2.59 +  FOR EACH ROW EXECUTE PROCEDURE
    2.60 +  "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
    2.61 +
    2.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"';
    2.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.';
    2.64 +
    2.65  CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
    2.66    ( "issue_id_p" "issue"."id"%TYPE )
    2.67    RETURNS VOID
    2.68 @@ -149,8 +198,6 @@
    2.69            "closed"          = NULL,
    2.70            "ranks_available" = FALSE
    2.71            WHERE "id" = "issue_id_p";
    2.72 -        DELETE FROM "voting_comment"
    2.73 -          WHERE "issue_id" = "issue_id_p";
    2.74          DELETE FROM "delegating_voter"
    2.75            WHERE "issue_id" = "issue_id_p";
    2.76          DELETE FROM "direct_voter"
    2.77 @@ -180,6 +227,81 @@
    2.78      END;
    2.79    $$;
    2.80  
    2.81 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    2.82 +  RETURNS VOID
    2.83 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.84 +    DECLARE
    2.85 +      "area_id_v"   "area"."id"%TYPE;
    2.86 +      "unit_id_v"   "unit"."id"%TYPE;
    2.87 +      "member_id_v" "member"."id"%TYPE;
    2.88 +    BEGIN
    2.89 +      PERFORM "lock_issue"("issue_id_p");
    2.90 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    2.91 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    2.92 +      -- delete timestamp of voting comment:
    2.93 +      UPDATE "direct_voter" SET "comment_changed" = NULL
    2.94 +        WHERE "issue_id" = "issue_id_p";
    2.95 +      -- delete delegating votes (in cases of manual reset of issue state):
    2.96 +      DELETE FROM "delegating_voter"
    2.97 +        WHERE "issue_id" = "issue_id_p";
    2.98 +      -- delete votes from non-privileged voters:
    2.99 +      DELETE FROM "direct_voter"
   2.100 +        USING (
   2.101 +          SELECT
   2.102 +            "direct_voter"."member_id"
   2.103 +          FROM "direct_voter"
   2.104 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   2.105 +          LEFT JOIN "privilege"
   2.106 +          ON "privilege"."unit_id" = "unit_id_v"
   2.107 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   2.108 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   2.109 +            "member"."active" = FALSE OR
   2.110 +            "privilege"."voting_right" ISNULL OR
   2.111 +            "privilege"."voting_right" = FALSE
   2.112 +          )
   2.113 +        ) AS "subquery"
   2.114 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   2.115 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   2.116 +      -- consider delegations:
   2.117 +      UPDATE "direct_voter" SET "weight" = 1
   2.118 +        WHERE "issue_id" = "issue_id_p";
   2.119 +      PERFORM "add_vote_delegations"("issue_id_p");
   2.120 +      -- set voter count and mark issue as being calculated:
   2.121 +      UPDATE "issue" SET
   2.122 +        "state"  = 'calculation',
   2.123 +        "closed" = now(),
   2.124 +        "voter_count" = (
   2.125 +          SELECT coalesce(sum("weight"), 0)
   2.126 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.127 +        )
   2.128 +        WHERE "id" = "issue_id_p";
   2.129 +      -- materialize battle_view:
   2.130 +      -- NOTE: "closed" column of issue must be set at this point
   2.131 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.132 +      INSERT INTO "battle" (
   2.133 +        "issue_id",
   2.134 +        "winning_initiative_id", "losing_initiative_id",
   2.135 +        "count"
   2.136 +      ) SELECT
   2.137 +        "issue_id",
   2.138 +        "winning_initiative_id", "losing_initiative_id",
   2.139 +        "count"
   2.140 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.141 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   2.142 +      UPDATE "initiative" SET
   2.143 +        "positive_votes" = "battle_win"."count",
   2.144 +        "negative_votes" = "battle_lose"."count"
   2.145 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   2.146 +        WHERE
   2.147 +          "battle_win"."issue_id" = "issue_id_p" AND
   2.148 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   2.149 +          "battle_win"."losing_initiative_id" ISNULL AND
   2.150 +          "battle_lose"."issue_id" = "issue_id_p" AND
   2.151 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   2.152 +          "battle_lose"."winning_initiative_id" ISNULL;
   2.153 +    END;
   2.154 +  $$;
   2.155 +
   2.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.';
   2.157  
   2.158  COMMIT;

Impressum / About Us