liquid_feedback_core
view update/core-update.v1.0.3-v1.1.0.sql @ 582:225a0c047691
Bugfix in function "write_event_initiative_revoked_trigger" (wrong use of SELECT, needs to be PERFORM)
| author | jbe |
|---|---|
| date | Tue Sep 04 14:30:41 2018 +0200 (2018-09-04) |
| parents | b63515611a60 |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member" ADD COLUMN "notify_email_lock_expiry" TIMESTAMPTZ;
8 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
10 ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists";
12 CREATE OR REPLACE FUNCTION "create_population_snapshot"
13 ( "issue_id_p" "issue"."id"%TYPE )
14 RETURNS VOID
15 LANGUAGE 'plpgsql' VOLATILE AS $$
16 DECLARE
17 "member_id_v" "member"."id"%TYPE;
18 BEGIN
19 DELETE FROM "direct_population_snapshot"
20 WHERE "issue_id" = "issue_id_p"
21 AND "event" = 'periodic';
22 DELETE FROM "delegating_population_snapshot"
23 WHERE "issue_id" = "issue_id_p"
24 AND "event" = 'periodic';
25 INSERT INTO "direct_population_snapshot"
26 ("issue_id", "event", "member_id")
27 SELECT
28 "issue_id_p" AS "issue_id",
29 'periodic'::"snapshot_event" AS "event",
30 "member"."id" AS "member_id"
31 FROM "issue"
32 JOIN "area" ON "issue"."area_id" = "area"."id"
33 JOIN "membership" ON "area"."id" = "membership"."area_id"
34 JOIN "member" ON "membership"."member_id" = "member"."id"
35 WHERE "issue"."id" = "issue_id_p"
36 AND "member"."active"
37 UNION
38 SELECT
39 "issue_id_p" AS "issue_id",
40 'periodic'::"snapshot_event" AS "event",
41 "member"."id" AS "member_id"
42 FROM "interest" JOIN "member"
43 ON "interest"."member_id" = "member"."id"
44 WHERE "interest"."issue_id" = "issue_id_p"
45 AND "member"."active";
46 FOR "member_id_v" IN
47 SELECT "member_id" FROM "direct_population_snapshot"
48 WHERE "issue_id" = "issue_id_p"
49 AND "event" = 'periodic'
50 LOOP
51 UPDATE "direct_population_snapshot" SET
52 "weight" = 1 +
53 "weight_of_added_delegations_for_population_snapshot"(
54 "issue_id_p",
55 "member_id_v",
56 '{}'
57 )
58 WHERE "issue_id" = "issue_id_p"
59 AND "event" = 'periodic'
60 AND "member_id" = "member_id_v";
61 END LOOP;
62 RETURN;
63 END;
64 $$;
66 COMMENT ON FUNCTION "freeze_after_snapshot"
67 ( "issue"."id"%TYPE )
68 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
70 COMMENT ON FUNCTION "manual_freeze"
71 ( "issue"."id"%TYPE )
72 IS 'Freeze an issue manually (fully) and start voting';
74 DROP FUNCTION "delete_member_data"("member"."id"%TYPE);
76 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
77 RETURNS VOID
78 LANGUAGE 'plpgsql' VOLATILE AS $$
79 BEGIN
80 UPDATE "member" SET
81 "login" = NULL,
82 "password" = NULL,
83 "active" = FALSE,
84 "notify_email" = NULL,
85 "notify_email_unconfirmed" = NULL,
86 "notify_email_secret" = NULL,
87 "notify_email_secret_expiry" = NULL,
88 "password_reset_secret" = NULL,
89 "password_reset_secret_expiry" = NULL,
90 "organizational_unit" = NULL,
91 "internal_posts" = NULL,
92 "realname" = NULL,
93 "birthday" = NULL,
94 "address" = NULL,
95 "email" = NULL,
96 "xmpp_address" = NULL,
97 "website" = NULL,
98 "phone" = NULL,
99 "mobile_phone" = NULL,
100 "profession" = NULL,
101 "external_memberships" = NULL,
102 "external_posts" = NULL,
103 "statement" = NULL
104 WHERE "id" = "member_id_p";
105 -- "text_search_data" is updated by triggers
106 UPDATE "member_history" SET "login" = NULL
107 WHERE "member_id" = "member_id_p";
108 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
109 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
110 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
111 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
112 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
113 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
114 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
115 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
116 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
117 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
118 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
119 RETURN;
120 END;
121 $$;
123 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
125 CREATE OR REPLACE FUNCTION "delete_private_data"()
126 RETURNS VOID
127 LANGUAGE 'plpgsql' VOLATILE AS $$
128 BEGIN
129 UPDATE "member" SET
130 "login" = NULL,
131 "password" = NULL,
132 "notify_email" = NULL,
133 "notify_email_unconfirmed" = NULL,
134 "notify_email_secret" = NULL,
135 "notify_email_secret_expiry" = NULL,
136 "password_reset_secret" = NULL,
137 "password_reset_secret_expiry" = NULL,
138 "organizational_unit" = NULL,
139 "internal_posts" = NULL,
140 "realname" = NULL,
141 "birthday" = NULL,
142 "address" = NULL,
143 "email" = NULL,
144 "xmpp_address" = NULL,
145 "website" = NULL,
146 "phone" = NULL,
147 "mobile_phone" = NULL,
148 "profession" = NULL,
149 "external_memberships" = NULL,
150 "external_posts" = NULL,
151 "statement" = NULL;
152 -- "text_search_data" is updated by triggers
153 UPDATE "member_history" SET "login" = NULL;
154 DELETE FROM "invite_code";
155 DELETE FROM "setting";
156 DELETE FROM "setting_map";
157 DELETE FROM "member_relation_setting";
158 DELETE FROM "member_image";
159 DELETE FROM "contact";
160 DELETE FROM "session";
161 DELETE FROM "area_setting";
162 DELETE FROM "issue_setting";
163 DELETE FROM "initiative_setting";
164 DELETE FROM "suggestion_setting";
165 DELETE FROM "direct_voter" USING "issue"
166 WHERE "direct_voter"."issue_id" = "issue"."id"
167 AND "issue"."closed" ISNULL;
168 RETURN;
169 END;
170 $$;
172 COMMIT;