liquid_feedback_core

view update/core-update.v1.1.0-v1.2.0.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
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;

Impressum / About Us