liquid_feedback_core
view update/core-update.v2.0.6-v2.0.7.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 | 4346d48b1780 |
children |
line source
1 BEGIN;
3 -- NOTE: "lf_update" needs to be recompiled to complete this update!
5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
6 SELECT * FROM (VALUES ('2.0.7', 2, 0, 7))
7 AS "subquery"("string", "major", "minor", "revision");
9 CREATE VIEW "expired_session" AS
10 SELECT * FROM "session" WHERE now() > "expiry";
12 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
13 DELETE FROM "session" WHERE "ident" = OLD."ident";
15 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
16 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
18 CREATE OR REPLACE FUNCTION "check_everything"()
19 RETURNS VOID
20 LANGUAGE 'plpgsql' VOLATILE AS $$
21 DECLARE
22 "issue_id_v" "issue"."id"%TYPE;
23 BEGIN
24 DELETE FROM "expired_session";
25 PERFORM "check_activity"();
26 PERFORM "calculate_member_counts"();
27 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
28 PERFORM "check_issue"("issue_id_v");
29 END LOOP;
30 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
31 PERFORM "calculate_ranks"("issue_id_v");
32 END LOOP;
33 RETURN;
34 END;
35 $$;
37 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
38 RETURNS VOID
39 LANGUAGE 'plpgsql' VOLATILE AS $$
40 BEGIN
41 UPDATE "member" SET
42 "last_login" = NULL,
43 "login" = NULL,
44 "password" = NULL,
45 "locked" = TRUE,
46 "active" = FALSE,
47 "notify_email" = NULL,
48 "notify_email_unconfirmed" = NULL,
49 "notify_email_secret" = NULL,
50 "notify_email_secret_expiry" = NULL,
51 "notify_email_lock_expiry" = NULL,
52 "password_reset_secret" = NULL,
53 "password_reset_secret_expiry" = NULL,
54 "organizational_unit" = NULL,
55 "internal_posts" = NULL,
56 "realname" = NULL,
57 "birthday" = NULL,
58 "address" = NULL,
59 "email" = NULL,
60 "xmpp_address" = NULL,
61 "website" = NULL,
62 "phone" = NULL,
63 "mobile_phone" = NULL,
64 "profession" = NULL,
65 "external_memberships" = NULL,
66 "external_posts" = NULL,
67 "statement" = NULL
68 WHERE "id" = "member_id_p";
69 -- "text_search_data" is updated by triggers
70 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
71 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
72 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
73 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
74 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
75 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
76 DELETE FROM "session" WHERE "member_id" = "member_id_p";
77 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
78 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
79 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
80 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
81 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
82 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
83 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
84 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
85 DELETE FROM "direct_voter" USING "issue"
86 WHERE "direct_voter"."issue_id" = "issue"."id"
87 AND "issue"."closed" ISNULL
88 AND "member_id" = "member_id_p";
89 RETURN;
90 END;
91 $$;
93 CREATE OR REPLACE FUNCTION "delete_private_data"()
94 RETURNS VOID
95 LANGUAGE 'plpgsql' VOLATILE AS $$
96 BEGIN
97 DELETE FROM "member" WHERE "activated" ISNULL;
98 UPDATE "member" SET
99 "invite_code" = NULL,
100 "invite_code_expiry" = NULL,
101 "admin_comment" = NULL,
102 "last_login" = NULL,
103 "login" = NULL,
104 "password" = NULL,
105 "notify_email" = NULL,
106 "notify_email_unconfirmed" = NULL,
107 "notify_email_secret" = NULL,
108 "notify_email_secret_expiry" = NULL,
109 "notify_email_lock_expiry" = NULL,
110 "password_reset_secret" = NULL,
111 "password_reset_secret_expiry" = NULL,
112 "organizational_unit" = NULL,
113 "internal_posts" = NULL,
114 "realname" = NULL,
115 "birthday" = NULL,
116 "address" = NULL,
117 "email" = NULL,
118 "xmpp_address" = NULL,
119 "website" = NULL,
120 "phone" = NULL,
121 "mobile_phone" = NULL,
122 "profession" = NULL,
123 "external_memberships" = NULL,
124 "external_posts" = NULL,
125 "statement" = NULL;
126 -- "text_search_data" is updated by triggers
127 DELETE FROM "setting";
128 DELETE FROM "setting_map";
129 DELETE FROM "member_relation_setting";
130 DELETE FROM "member_image";
131 DELETE FROM "contact";
132 DELETE FROM "ignored_member";
133 DELETE FROM "session";
134 DELETE FROM "area_setting";
135 DELETE FROM "issue_setting";
136 DELETE FROM "ignored_initiative";
137 DELETE FROM "initiative_setting";
138 DELETE FROM "suggestion_setting";
139 DELETE FROM "non_voter";
140 DELETE FROM "direct_voter" USING "issue"
141 WHERE "direct_voter"."issue_id" = "issue"."id"
142 AND "issue"."closed" ISNULL;
143 RETURN;
144 END;
145 $$;
147 COMMIT;