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;

Impressum / About Us