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