liquid_feedback_core
view update/core-update.v2.0.6-v2.0.7.sql @ 271:02a72de117e9
Work on OAuth2
| author | jbe |
|---|---|
| date | Thu Aug 09 00:11:01 2012 +0200 (2012-08-09) |
| 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;