liquid_feedback_core
changeset 287:13a9f609c3de
Commented update/core-update.v2.0.11-v2.1.0.sql
author | jbe |
---|---|
date | Sun Aug 19 21:04:47 2012 +0200 (2012-08-19) |
parents | 3ac6d4259387 |
children | fb66e74d94b8 |
files | update/core-update.v2.0.11-v2.1.0.sql |
line diff
1.1 --- a/update/core-update.v2.0.11-v2.1.0.sql Sun Aug 19 20:35:22 2012 +0200 1.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql Sun Aug 19 21:04:47 2012 +0200 1.3 @@ -1,12 +1,21 @@ 1.4 BEGIN; 1.5 1.6 + 1.7 +-- update version number 1.8 + 1.9 CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.10 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0)) 1.11 AS "subquery"("string", "major", "minor", "revision"); 1.12 1.13 + 1.14 +-- old API tables are now deprecated 1.15 + 1.16 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API'; 1.17 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API'; 1.18 1.19 + 1.20 +-- new polling mode and changed privileges 1.21 + 1.22 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; 1.23 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL; 1.24 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL; 1.25 @@ -37,94 +46,6 @@ 1.26 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote'; 1.27 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")'; 1.28 1.29 -ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ; 1.30 -ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT; 1.31 -ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT; 1.32 -ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR; 1.33 -CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); 1.34 -CREATE TRIGGER "update_text_search_data" 1.35 - BEFORE INSERT OR UPDATE ON "direct_voter" 1.36 - FOR EACH ROW EXECUTE PROCEDURE 1.37 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); 1.38 - 1.39 -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.40 -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.41 -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.42 - 1.43 -CREATE TABLE "rendered_voter_comment" ( 1.44 - PRIMARY KEY ("issue_id", "member_id", "format"), 1.45 - FOREIGN KEY ("issue_id", "member_id") 1.46 - REFERENCES "direct_voter" ("issue_id", "member_id") 1.47 - ON DELETE CASCADE ON UPDATE CASCADE, 1.48 - "issue_id" INT4, 1.49 - "member_id" INT4, 1.50 - "format" TEXT, 1.51 - "content" TEXT NOT NULL ); 1.52 - 1.53 -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.54 - 1.55 - 1.56 -DROP TABLE "rendered_issue_comment"; 1.57 -DROP TABLE "issue_comment"; 1.58 -DROP TABLE "rendered_voting_comment"; 1.59 -DROP TABLE "voting_comment"; 1.60 - 1.61 -CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"() 1.62 - RETURNS TRIGGER 1.63 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.64 - BEGIN 1.65 - DELETE FROM "direct_voter" 1.66 - WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 1.67 - RETURN NULL; 1.68 - END; 1.69 - $$; 1.70 - 1.71 -CREATE TRIGGER "non_voter_deletes_direct_voter" 1.72 - AFTER INSERT OR UPDATE ON "non_voter" 1.73 - FOR EACH ROW EXECUTE PROCEDURE 1.74 - "non_voter_deletes_direct_voter_trigger"(); 1.75 - 1.76 -COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"'; 1.77 -COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")'; 1.78 - 1.79 -CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"() 1.80 - RETURNS TRIGGER 1.81 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.82 - BEGIN 1.83 - DELETE FROM "non_voter" 1.84 - WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 1.85 - RETURN NULL; 1.86 - END; 1.87 - $$; 1.88 - 1.89 -CREATE TRIGGER "direct_voter_deletes_non_voter" 1.90 - AFTER INSERT OR UPDATE ON "direct_voter" 1.91 - FOR EACH ROW EXECUTE PROCEDURE 1.92 - "direct_voter_deletes_non_voter_trigger"(); 1.93 - 1.94 -COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"'; 1.95 -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.96 - 1.97 -CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() 1.98 - RETURNS TRIGGER 1.99 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.100 - BEGIN 1.101 - IF NEW."comment" ISNULL THEN 1.102 - NEW."comment_changed" := NULL; 1.103 - NEW."formatting_engine" := NULL; 1.104 - END IF; 1.105 - RETURN NEW; 1.106 - END; 1.107 - $$; 1.108 - 1.109 -CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" 1.110 - BEFORE INSERT OR UPDATE ON "direct_voter" 1.111 - FOR EACH ROW EXECUTE PROCEDURE 1.112 - "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"(); 1.113 - 1.114 -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.115 -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.116 - 1.117 CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 1.118 ( "issue_id_p" "issue"."id"%TYPE ) 1.119 RETURNS VOID 1.120 @@ -186,6 +107,60 @@ 1.121 END; 1.122 $$; 1.123 1.124 + 1.125 +-- issue comments removed, voting comments integrated in "direct_voter" table 1.126 + 1.127 +ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ; 1.128 +ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT; 1.129 +ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT; 1.130 +ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR; 1.131 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); 1.132 +CREATE TRIGGER "update_text_search_data" 1.133 + BEFORE INSERT OR UPDATE ON "direct_voter" 1.134 + FOR EACH ROW EXECUTE PROCEDURE 1.135 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); 1.136 + 1.137 +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.138 +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.139 +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.140 + 1.141 +CREATE TABLE "rendered_voter_comment" ( 1.142 + PRIMARY KEY ("issue_id", "member_id", "format"), 1.143 + FOREIGN KEY ("issue_id", "member_id") 1.144 + REFERENCES "direct_voter" ("issue_id", "member_id") 1.145 + ON DELETE CASCADE ON UPDATE CASCADE, 1.146 + "issue_id" INT4, 1.147 + "member_id" INT4, 1.148 + "format" TEXT, 1.149 + "content" TEXT NOT NULL ); 1.150 + 1.151 +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.152 + 1.153 +DROP TABLE "rendered_issue_comment"; 1.154 +DROP TABLE "issue_comment"; 1.155 +DROP TABLE "rendered_voting_comment"; 1.156 +DROP TABLE "voting_comment"; 1.157 + 1.158 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() 1.159 + RETURNS TRIGGER 1.160 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.161 + BEGIN 1.162 + IF NEW."comment" ISNULL THEN 1.163 + NEW."comment_changed" := NULL; 1.164 + NEW."formatting_engine" := NULL; 1.165 + END IF; 1.166 + RETURN NEW; 1.167 + END; 1.168 + $$; 1.169 + 1.170 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" 1.171 + BEFORE INSERT OR UPDATE ON "direct_voter" 1.172 + FOR EACH ROW EXECUTE PROCEDURE 1.173 + "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"(); 1.174 + 1.175 +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.176 +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.177 + 1.178 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 1.179 RETURNS VOID 1.180 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.181 @@ -305,6 +280,49 @@ 1.182 END; 1.183 $$; 1.184 1.185 + 1.186 +-- "non_voter" deletes "direct_voter" and vice versa 1.187 + 1.188 +CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"() 1.189 + RETURNS TRIGGER 1.190 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.191 + BEGIN 1.192 + DELETE FROM "direct_voter" 1.193 + WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 1.194 + RETURN NULL; 1.195 + END; 1.196 + $$; 1.197 + 1.198 +CREATE TRIGGER "non_voter_deletes_direct_voter" 1.199 + AFTER INSERT OR UPDATE ON "non_voter" 1.200 + FOR EACH ROW EXECUTE PROCEDURE 1.201 + "non_voter_deletes_direct_voter_trigger"(); 1.202 + 1.203 +COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"'; 1.204 +COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")'; 1.205 + 1.206 +CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"() 1.207 + RETURNS TRIGGER 1.208 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.209 + BEGIN 1.210 + DELETE FROM "non_voter" 1.211 + WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 1.212 + RETURN NULL; 1.213 + END; 1.214 + $$; 1.215 + 1.216 +CREATE TRIGGER "direct_voter_deletes_non_voter" 1.217 + AFTER INSERT OR UPDATE ON "direct_voter" 1.218 + FOR EACH ROW EXECUTE PROCEDURE 1.219 + "direct_voter_deletes_non_voter_trigger"(); 1.220 + 1.221 +COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"'; 1.222 +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.223 + 1.224 + 1.225 +-- new comment on function "delete_private_data"() 1.226 + 1.227 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.228 1.229 + 1.230 COMMIT;