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