| rev |
line source |
|
jbe@45
|
1 BEGIN;
|
|
jbe@45
|
2
|
|
jbe@45
|
3 DROP VIEW "liquid_feedback_version";
|
|
jbe@45
|
4 CREATE VIEW "liquid_feedback_version" AS
|
|
jbe@45
|
5 SELECT * FROM (VALUES ('1.0.0', 1, 0, 0))
|
|
jbe@45
|
6 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@45
|
7
|
|
jbe@45
|
8 ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL;
|
|
jbe@45
|
9
|
|
jbe@45
|
10 ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL;
|
|
jbe@45
|
11
|
|
jbe@45
|
12 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
|
|
jbe@45
|
13
|
|
jbe@45
|
14 ALTER TABLE "direct_population_snapshot" DROP
|
|
jbe@45
|
15 CONSTRAINT "direct_population_snapshot_member_id_fkey";
|
|
jbe@45
|
16 ALTER TABLE "direct_population_snapshot" ADD
|
|
jbe@45
|
17 CONSTRAINT "direct_population_snapshot_member_id_fkey"
|
|
jbe@45
|
18 FOREIGN KEY ("member_id")
|
|
jbe@45
|
19 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
20
|
|
jbe@45
|
21 ALTER TABLE "delegating_population_snapshot" DROP
|
|
jbe@45
|
22 CONSTRAINT "delegating_population_snapshot_member_id_fkey";
|
|
jbe@45
|
23 ALTER TABLE "delegating_population_snapshot" ADD
|
|
jbe@45
|
24 CONSTRAINT "delegating_population_snapshot_member_id_fkey"
|
|
jbe@45
|
25 FOREIGN KEY ("member_id")
|
|
jbe@45
|
26 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
27
|
|
jbe@45
|
28 ALTER TABLE "direct_interest_snapshot" DROP
|
|
jbe@45
|
29 CONSTRAINT "direct_interest_snapshot_member_id_fkey";
|
|
jbe@45
|
30 ALTER TABLE "direct_interest_snapshot" ADD
|
|
jbe@45
|
31 CONSTRAINT "direct_interest_snapshot_member_id_fkey"
|
|
jbe@45
|
32 FOREIGN KEY ("member_id")
|
|
jbe@45
|
33 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
34
|
|
jbe@45
|
35 ALTER TABLE "delegating_interest_snapshot" DROP
|
|
jbe@45
|
36 CONSTRAINT "delegating_interest_snapshot_member_id_fkey";
|
|
jbe@45
|
37 ALTER TABLE "delegating_interest_snapshot" ADD
|
|
jbe@45
|
38 CONSTRAINT "delegating_interest_snapshot_member_id_fkey"
|
|
jbe@45
|
39 FOREIGN KEY ("member_id")
|
|
jbe@45
|
40 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
41
|
|
jbe@45
|
42 ALTER TABLE "direct_supporter_snapshot" DROP
|
|
jbe@45
|
43 CONSTRAINT "direct_supporter_snapshot_member_id_fkey";
|
|
jbe@45
|
44 ALTER TABLE "direct_supporter_snapshot" ADD
|
|
jbe@45
|
45 CONSTRAINT "direct_supporter_snapshot_member_id_fkey"
|
|
jbe@45
|
46 FOREIGN KEY ("member_id")
|
|
jbe@45
|
47 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
48
|
|
jbe@45
|
49 ALTER TABLE "direct_voter" DROP
|
|
jbe@45
|
50 CONSTRAINT "direct_voter_member_id_fkey";
|
|
jbe@45
|
51 ALTER TABLE "direct_voter" ADD
|
|
jbe@45
|
52 CONSTRAINT "direct_voter_member_id_fkey"
|
|
jbe@45
|
53 FOREIGN KEY ("member_id")
|
|
jbe@45
|
54 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
55
|
|
jbe@45
|
56 ALTER TABLE "delegating_voter" DROP
|
|
jbe@45
|
57 CONSTRAINT "delegating_voter_member_id_fkey";
|
|
jbe@45
|
58 ALTER TABLE "delegating_voter" ADD
|
|
jbe@45
|
59 CONSTRAINT "delegating_voter_member_id_fkey"
|
|
jbe@45
|
60 FOREIGN KEY ("member_id")
|
|
jbe@45
|
61 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
|
|
jbe@45
|
62
|
|
jbe@45
|
63 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
|
|
jbe@45
|
64 RETURNS TRIGGER
|
|
jbe@45
|
65 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@45
|
66 BEGIN
|
|
jbe@45
|
67 IF
|
|
jbe@45
|
68 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
|
|
jbe@45
|
69 NEW."login" != OLD."login" ) OR
|
|
jbe@45
|
70 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
|
|
jbe@45
|
71 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
|
|
jbe@45
|
72 NEW."active" != OLD."active" OR
|
|
jbe@45
|
73 NEW."name" != OLD."name"
|
|
jbe@45
|
74 THEN
|
|
jbe@45
|
75 INSERT INTO "member_history"
|
|
jbe@45
|
76 ("member_id", "login", "active", "name")
|
|
jbe@45
|
77 VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
|
|
jbe@45
|
78 END IF;
|
|
jbe@45
|
79 RETURN NULL;
|
|
jbe@45
|
80 END;
|
|
jbe@45
|
81 $$;
|
|
jbe@45
|
82
|
|
jbe@45
|
83 CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE)
|
|
jbe@45
|
84 RETURNS VOID
|
|
jbe@45
|
85 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@45
|
86 BEGIN
|
|
jbe@45
|
87 UPDATE "member" SET
|
|
jbe@45
|
88 "login" = NULL,
|
|
jbe@45
|
89 "password" = NULL,
|
|
jbe@45
|
90 "notify_email" = NULL,
|
|
jbe@45
|
91 "notify_email_unconfirmed" = NULL,
|
|
jbe@45
|
92 "notify_email_secret" = NULL,
|
|
jbe@45
|
93 "notify_email_secret_expiry" = NULL,
|
|
jbe@45
|
94 "password_reset_secret" = NULL,
|
|
jbe@45
|
95 "password_reset_secret_expiry" = NULL,
|
|
jbe@45
|
96 "organizational_unit" = NULL,
|
|
jbe@45
|
97 "internal_posts" = NULL,
|
|
jbe@45
|
98 "realname" = NULL,
|
|
jbe@45
|
99 "birthday" = NULL,
|
|
jbe@45
|
100 "address" = NULL,
|
|
jbe@45
|
101 "email" = NULL,
|
|
jbe@45
|
102 "xmpp_address" = NULL,
|
|
jbe@45
|
103 "website" = NULL,
|
|
jbe@45
|
104 "phone" = NULL,
|
|
jbe@45
|
105 "mobile_phone" = NULL,
|
|
jbe@45
|
106 "profession" = NULL,
|
|
jbe@45
|
107 "external_memberships" = NULL,
|
|
jbe@45
|
108 "external_posts" = NULL,
|
|
jbe@45
|
109 "statement" = NULL
|
|
jbe@45
|
110 WHERE "id" = "member_id_p";
|
|
jbe@45
|
111 -- "text_search_data" is updated by triggers
|
|
jbe@45
|
112 UPDATE "member_history" SET "login" = NULL
|
|
jbe@45
|
113 WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
114 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
115 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
116 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
117 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
118 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
119 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
120 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
121 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
122 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
123 RETURN;
|
|
jbe@45
|
124 END;
|
|
jbe@45
|
125 $$;
|
|
jbe@45
|
126 COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
|
|
jbe@45
|
127
|
|
jbe@45
|
128 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
|
jbe@45
|
129 RETURNS VOID
|
|
jbe@45
|
130 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@45
|
131 BEGIN
|
|
jbe@45
|
132 PERFORM "delete_member_data"("id") FROM "member";
|
|
jbe@45
|
133 DELETE FROM "invite_code";
|
|
jbe@45
|
134 DELETE FROM "session";
|
|
jbe@45
|
135 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@45
|
136 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@45
|
137 AND "issue"."closed" ISNULL;
|
|
jbe@45
|
138 RETURN;
|
|
jbe@45
|
139 END;
|
|
jbe@45
|
140 $$;
|
|
jbe@45
|
141
|
|
jbe@45
|
142 COMMIT;
|