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