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