liquid_feedback_core
changeset 233:f022016f6748 v2.0.6
Update script to v2.0.6
author | jbe |
---|---|
date | Sun Mar 11 17:05:45 2012 +0100 (2012-03-11) |
parents | cf8a090503c0 |
children | 876f366b2311 |
files | update/core-update.v2.0.5-v2.0.6.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.0.5-v2.0.6.sql Sun Mar 11 17:05:45 2012 +0100 1.3 @@ -0,0 +1,110 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.0.6', 2, 0, 6)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +-- add column "invite_code_expiry" to table "member": 1.11 +ALTER TABLE "member" ADD COLUMN "invite_code_expiry" TIMESTAMPTZ; 1.12 +COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"'; 1.13 + 1.14 +-- write member history only for activated accounts: 1.15 +CREATE OR REPLACE FUNCTION "write_member_history_trigger"() 1.16 + RETURNS TRIGGER 1.17 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.18 + BEGIN 1.19 + IF 1.20 + ( NEW."active" != OLD."active" OR 1.21 + NEW."name" != OLD."name" ) AND 1.22 + OLD."activated" NOTNULL 1.23 + THEN 1.24 + INSERT INTO "member_history" 1.25 + ("member_id", "active", "name") 1.26 + VALUES (NEW."id", OLD."active", OLD."name"); 1.27 + END IF; 1.28 + RETURN NULL; 1.29 + END; 1.30 + $$; 1.31 + 1.32 +-- set "draft_id" in "event" table on event 'initiative_revoked': 1.33 +CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"() 1.34 + RETURNS TRIGGER 1.35 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.36 + DECLARE 1.37 + "issue_row" "issue"%ROWTYPE; 1.38 + "draft_id_v" "draft"."id"%TYPE; 1.39 + BEGIN 1.40 + IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN 1.41 + SELECT * INTO "issue_row" FROM "issue" 1.42 + WHERE "id" = NEW."issue_id"; 1.43 + SELECT "id" INTO "draft_id_v" FROM "current_draft" 1.44 + WHERE "initiative_id" = NEW."id"; 1.45 + INSERT INTO "event" ( 1.46 + "event", "member_id", "issue_id", "state", "initiative_id", "draft_id" 1.47 + ) VALUES ( 1.48 + 'initiative_revoked', 1.49 + NEW."revoked_by_member_id", 1.50 + NEW."issue_id", 1.51 + "issue_row"."state", 1.52 + NEW."id", 1.53 + "draft_id_v"); 1.54 + END IF; 1.55 + RETURN NULL; 1.56 + END; 1.57 + $$; 1.58 + 1.59 +-- delete column "invite_code_expiry" in function "delete_private_data": 1.60 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.61 + RETURNS VOID 1.62 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.63 + BEGIN 1.64 + DELETE FROM "member" WHERE "activated" ISNULL; 1.65 + UPDATE "member" SET 1.66 + "invite_code" = NULL, 1.67 + "invite_code_expiry" = NULL, 1.68 + "admin_comment" = NULL, 1.69 + "last_login" = NULL, 1.70 + "login" = NULL, 1.71 + "password" = NULL, 1.72 + "notify_email" = NULL, 1.73 + "notify_email_unconfirmed" = NULL, 1.74 + "notify_email_secret" = NULL, 1.75 + "notify_email_secret_expiry" = NULL, 1.76 + "notify_email_lock_expiry" = NULL, 1.77 + "password_reset_secret" = NULL, 1.78 + "password_reset_secret_expiry" = NULL, 1.79 + "organizational_unit" = NULL, 1.80 + "internal_posts" = NULL, 1.81 + "realname" = NULL, 1.82 + "birthday" = NULL, 1.83 + "address" = NULL, 1.84 + "email" = NULL, 1.85 + "xmpp_address" = NULL, 1.86 + "website" = NULL, 1.87 + "phone" = NULL, 1.88 + "mobile_phone" = NULL, 1.89 + "profession" = NULL, 1.90 + "external_memberships" = NULL, 1.91 + "external_posts" = NULL, 1.92 + "statement" = NULL; 1.93 + -- "text_search_data" is updated by triggers 1.94 + DELETE FROM "setting"; 1.95 + DELETE FROM "setting_map"; 1.96 + DELETE FROM "member_relation_setting"; 1.97 + DELETE FROM "member_image"; 1.98 + DELETE FROM "contact"; 1.99 + DELETE FROM "ignored_member"; 1.100 + DELETE FROM "area_setting"; 1.101 + DELETE FROM "issue_setting"; 1.102 + DELETE FROM "ignored_initiative"; 1.103 + DELETE FROM "initiative_setting"; 1.104 + DELETE FROM "suggestion_setting"; 1.105 + DELETE FROM "non_voter"; 1.106 + DELETE FROM "direct_voter" USING "issue" 1.107 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.108 + AND "issue"."closed" ISNULL; 1.109 + RETURN; 1.110 + END; 1.111 + $$; 1.112 + 1.113 +COMMIT;