liquid_feedback_core
annotate update/core-update.v2.0.5-v2.0.6.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | f022016f6748 |
children |
rev | line source |
---|---|
jbe@233 | 1 BEGIN; |
jbe@233 | 2 |
jbe@233 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@233 | 4 SELECT * FROM (VALUES ('2.0.6', 2, 0, 6)) |
jbe@233 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@233 | 6 |
jbe@233 | 7 -- add column "invite_code_expiry" to table "member": |
jbe@233 | 8 ALTER TABLE "member" ADD COLUMN "invite_code_expiry" TIMESTAMPTZ; |
jbe@233 | 9 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"'; |
jbe@233 | 10 |
jbe@233 | 11 -- write member history only for activated accounts: |
jbe@233 | 12 CREATE OR REPLACE FUNCTION "write_member_history_trigger"() |
jbe@233 | 13 RETURNS TRIGGER |
jbe@233 | 14 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@233 | 15 BEGIN |
jbe@233 | 16 IF |
jbe@233 | 17 ( NEW."active" != OLD."active" OR |
jbe@233 | 18 NEW."name" != OLD."name" ) AND |
jbe@233 | 19 OLD."activated" NOTNULL |
jbe@233 | 20 THEN |
jbe@233 | 21 INSERT INTO "member_history" |
jbe@233 | 22 ("member_id", "active", "name") |
jbe@233 | 23 VALUES (NEW."id", OLD."active", OLD."name"); |
jbe@233 | 24 END IF; |
jbe@233 | 25 RETURN NULL; |
jbe@233 | 26 END; |
jbe@233 | 27 $$; |
jbe@233 | 28 |
jbe@233 | 29 -- set "draft_id" in "event" table on event 'initiative_revoked': |
jbe@233 | 30 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"() |
jbe@233 | 31 RETURNS TRIGGER |
jbe@233 | 32 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@233 | 33 DECLARE |
jbe@233 | 34 "issue_row" "issue"%ROWTYPE; |
jbe@233 | 35 "draft_id_v" "draft"."id"%TYPE; |
jbe@233 | 36 BEGIN |
jbe@233 | 37 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN |
jbe@233 | 38 SELECT * INTO "issue_row" FROM "issue" |
jbe@233 | 39 WHERE "id" = NEW."issue_id"; |
jbe@233 | 40 SELECT "id" INTO "draft_id_v" FROM "current_draft" |
jbe@233 | 41 WHERE "initiative_id" = NEW."id"; |
jbe@233 | 42 INSERT INTO "event" ( |
jbe@233 | 43 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id" |
jbe@233 | 44 ) VALUES ( |
jbe@233 | 45 'initiative_revoked', |
jbe@233 | 46 NEW."revoked_by_member_id", |
jbe@233 | 47 NEW."issue_id", |
jbe@233 | 48 "issue_row"."state", |
jbe@233 | 49 NEW."id", |
jbe@233 | 50 "draft_id_v"); |
jbe@233 | 51 END IF; |
jbe@233 | 52 RETURN NULL; |
jbe@233 | 53 END; |
jbe@233 | 54 $$; |
jbe@233 | 55 |
jbe@233 | 56 -- delete column "invite_code_expiry" in function "delete_private_data": |
jbe@233 | 57 CREATE OR REPLACE FUNCTION "delete_private_data"() |
jbe@233 | 58 RETURNS VOID |
jbe@233 | 59 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@233 | 60 BEGIN |
jbe@233 | 61 DELETE FROM "member" WHERE "activated" ISNULL; |
jbe@233 | 62 UPDATE "member" SET |
jbe@233 | 63 "invite_code" = NULL, |
jbe@233 | 64 "invite_code_expiry" = NULL, |
jbe@233 | 65 "admin_comment" = NULL, |
jbe@233 | 66 "last_login" = NULL, |
jbe@233 | 67 "login" = NULL, |
jbe@233 | 68 "password" = NULL, |
jbe@233 | 69 "notify_email" = NULL, |
jbe@233 | 70 "notify_email_unconfirmed" = NULL, |
jbe@233 | 71 "notify_email_secret" = NULL, |
jbe@233 | 72 "notify_email_secret_expiry" = NULL, |
jbe@233 | 73 "notify_email_lock_expiry" = NULL, |
jbe@233 | 74 "password_reset_secret" = NULL, |
jbe@233 | 75 "password_reset_secret_expiry" = NULL, |
jbe@233 | 76 "organizational_unit" = NULL, |
jbe@233 | 77 "internal_posts" = NULL, |
jbe@233 | 78 "realname" = NULL, |
jbe@233 | 79 "birthday" = NULL, |
jbe@233 | 80 "address" = NULL, |
jbe@233 | 81 "email" = NULL, |
jbe@233 | 82 "xmpp_address" = NULL, |
jbe@233 | 83 "website" = NULL, |
jbe@233 | 84 "phone" = NULL, |
jbe@233 | 85 "mobile_phone" = NULL, |
jbe@233 | 86 "profession" = NULL, |
jbe@233 | 87 "external_memberships" = NULL, |
jbe@233 | 88 "external_posts" = NULL, |
jbe@233 | 89 "statement" = NULL; |
jbe@233 | 90 -- "text_search_data" is updated by triggers |
jbe@233 | 91 DELETE FROM "setting"; |
jbe@233 | 92 DELETE FROM "setting_map"; |
jbe@233 | 93 DELETE FROM "member_relation_setting"; |
jbe@233 | 94 DELETE FROM "member_image"; |
jbe@233 | 95 DELETE FROM "contact"; |
jbe@233 | 96 DELETE FROM "ignored_member"; |
jbe@233 | 97 DELETE FROM "area_setting"; |
jbe@233 | 98 DELETE FROM "issue_setting"; |
jbe@233 | 99 DELETE FROM "ignored_initiative"; |
jbe@233 | 100 DELETE FROM "initiative_setting"; |
jbe@233 | 101 DELETE FROM "suggestion_setting"; |
jbe@233 | 102 DELETE FROM "non_voter"; |
jbe@233 | 103 DELETE FROM "direct_voter" USING "issue" |
jbe@233 | 104 WHERE "direct_voter"."issue_id" = "issue"."id" |
jbe@233 | 105 AND "issue"."closed" ISNULL; |
jbe@233 | 106 RETURN; |
jbe@233 | 107 END; |
jbe@233 | 108 $$; |
jbe@233 | 109 |
jbe@233 | 110 COMMIT; |