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;