liquid_feedback_core
view update/core-update.v1.1.0-v1.2.0.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 | a7ad50614d82 |
children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member_history" DROP COLUMN "login";
9 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
10 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
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"
19 THEN
20 INSERT INTO "member_history"
21 ("member_id", "active", "name")
22 VALUES (NEW."id", OLD."active", OLD."name");
23 END IF;
24 RETURN NULL;
25 END;
26 $$;
28 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
30 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
31 RETURNS VOID
32 LANGUAGE 'plpgsql' VOLATILE AS $$
33 BEGIN
34 UPDATE "member" SET
35 "last_login" = NULL,
36 "login" = NULL,
37 "password" = NULL,
38 "active" = FALSE,
39 "notify_email" = NULL,
40 "notify_email_unconfirmed" = NULL,
41 "notify_email_secret" = NULL,
42 "notify_email_secret_expiry" = NULL,
43 "notify_email_lock_expiry" = NULL,
44 "password_reset_secret" = NULL,
45 "password_reset_secret_expiry" = NULL,
46 "organizational_unit" = NULL,
47 "internal_posts" = NULL,
48 "realname" = NULL,
49 "birthday" = NULL,
50 "address" = NULL,
51 "email" = NULL,
52 "xmpp_address" = NULL,
53 "website" = NULL,
54 "phone" = NULL,
55 "mobile_phone" = NULL,
56 "profession" = NULL,
57 "external_memberships" = NULL,
58 "external_posts" = NULL,
59 "statement" = NULL
60 WHERE "id" = "member_id_p";
61 -- "text_search_data" is updated by triggers
62 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
63 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
64 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
65 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
66 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
67 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
68 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
69 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
70 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
71 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
72 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
73 DELETE FROM "direct_voter" USING "issue"
74 WHERE "direct_voter"."issue_id" = "issue"."id"
75 AND "issue"."closed" ISNULL
76 AND "member_id" = "member_id_p";
77 RETURN;
78 END;
79 $$;
81 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
83 CREATE OR REPLACE FUNCTION "delete_private_data"()
84 RETURNS VOID
85 LANGUAGE 'plpgsql' VOLATILE AS $$
86 BEGIN
87 UPDATE "member" SET
88 "last_login" = NULL,
89 "login" = NULL,
90 "password" = NULL,
91 "notify_email" = NULL,
92 "notify_email_unconfirmed" = NULL,
93 "notify_email_secret" = NULL,
94 "notify_email_secret_expiry" = NULL,
95 "notify_email_lock_expiry" = NULL,
96 "password_reset_secret" = NULL,
97 "password_reset_secret_expiry" = NULL,
98 "organizational_unit" = NULL,
99 "internal_posts" = NULL,
100 "realname" = NULL,
101 "birthday" = NULL,
102 "address" = NULL,
103 "email" = NULL,
104 "xmpp_address" = NULL,
105 "website" = NULL,
106 "phone" = NULL,
107 "mobile_phone" = NULL,
108 "profession" = NULL,
109 "external_memberships" = NULL,
110 "external_posts" = NULL,
111 "statement" = NULL;
112 -- "text_search_data" is updated by triggers
113 DELETE FROM "invite_code";
114 DELETE FROM "setting";
115 DELETE FROM "setting_map";
116 DELETE FROM "member_relation_setting";
117 DELETE FROM "member_image";
118 DELETE FROM "contact";
119 DELETE FROM "session";
120 DELETE FROM "area_setting";
121 DELETE FROM "issue_setting";
122 DELETE FROM "initiative_setting";
123 DELETE FROM "suggestion_setting";
124 DELETE FROM "direct_voter" USING "issue"
125 WHERE "direct_voter"."issue_id" = "issue"."id"
126 AND "issue"."closed" ISNULL;
127 RETURN;
128 END;
129 $$;
131 COMMIT;