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