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