| rev |
line source |
|
jbe@233
|
1 BEGIN;
|
|
jbe@233
|
2
|
|
jbe@233
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@233
|
4 SELECT * FROM (VALUES ('2.0.6', 2, 0, 6))
|
|
jbe@233
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@233
|
6
|
|
jbe@233
|
7 -- add column "invite_code_expiry" to table "member":
|
|
jbe@233
|
8 ALTER TABLE "member" ADD COLUMN "invite_code_expiry" TIMESTAMPTZ;
|
|
jbe@233
|
9 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
|
|
jbe@233
|
10
|
|
jbe@233
|
11 -- write member history only for activated accounts:
|
|
jbe@233
|
12 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
|
|
jbe@233
|
13 RETURNS TRIGGER
|
|
jbe@233
|
14 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@233
|
15 BEGIN
|
|
jbe@233
|
16 IF
|
|
jbe@233
|
17 ( NEW."active" != OLD."active" OR
|
|
jbe@233
|
18 NEW."name" != OLD."name" ) AND
|
|
jbe@233
|
19 OLD."activated" NOTNULL
|
|
jbe@233
|
20 THEN
|
|
jbe@233
|
21 INSERT INTO "member_history"
|
|
jbe@233
|
22 ("member_id", "active", "name")
|
|
jbe@233
|
23 VALUES (NEW."id", OLD."active", OLD."name");
|
|
jbe@233
|
24 END IF;
|
|
jbe@233
|
25 RETURN NULL;
|
|
jbe@233
|
26 END;
|
|
jbe@233
|
27 $$;
|
|
jbe@233
|
28
|
|
jbe@233
|
29 -- set "draft_id" in "event" table on event 'initiative_revoked':
|
|
jbe@233
|
30 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
|
|
jbe@233
|
31 RETURNS TRIGGER
|
|
jbe@233
|
32 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@233
|
33 DECLARE
|
|
jbe@233
|
34 "issue_row" "issue"%ROWTYPE;
|
|
jbe@233
|
35 "draft_id_v" "draft"."id"%TYPE;
|
|
jbe@233
|
36 BEGIN
|
|
jbe@233
|
37 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
|
|
jbe@233
|
38 SELECT * INTO "issue_row" FROM "issue"
|
|
jbe@233
|
39 WHERE "id" = NEW."issue_id";
|
|
jbe@233
|
40 SELECT "id" INTO "draft_id_v" FROM "current_draft"
|
|
jbe@233
|
41 WHERE "initiative_id" = NEW."id";
|
|
jbe@233
|
42 INSERT INTO "event" (
|
|
jbe@233
|
43 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
|
|
jbe@233
|
44 ) VALUES (
|
|
jbe@233
|
45 'initiative_revoked',
|
|
jbe@233
|
46 NEW."revoked_by_member_id",
|
|
jbe@233
|
47 NEW."issue_id",
|
|
jbe@233
|
48 "issue_row"."state",
|
|
jbe@233
|
49 NEW."id",
|
|
jbe@233
|
50 "draft_id_v");
|
|
jbe@233
|
51 END IF;
|
|
jbe@233
|
52 RETURN NULL;
|
|
jbe@233
|
53 END;
|
|
jbe@233
|
54 $$;
|
|
jbe@233
|
55
|
|
jbe@233
|
56 -- delete column "invite_code_expiry" in function "delete_private_data":
|
|
jbe@233
|
57 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
|
jbe@233
|
58 RETURNS VOID
|
|
jbe@233
|
59 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@233
|
60 BEGIN
|
|
jbe@233
|
61 DELETE FROM "member" WHERE "activated" ISNULL;
|
|
jbe@233
|
62 UPDATE "member" SET
|
|
jbe@233
|
63 "invite_code" = NULL,
|
|
jbe@233
|
64 "invite_code_expiry" = NULL,
|
|
jbe@233
|
65 "admin_comment" = NULL,
|
|
jbe@233
|
66 "last_login" = NULL,
|
|
jbe@233
|
67 "login" = NULL,
|
|
jbe@233
|
68 "password" = NULL,
|
|
jbe@233
|
69 "notify_email" = NULL,
|
|
jbe@233
|
70 "notify_email_unconfirmed" = NULL,
|
|
jbe@233
|
71 "notify_email_secret" = NULL,
|
|
jbe@233
|
72 "notify_email_secret_expiry" = NULL,
|
|
jbe@233
|
73 "notify_email_lock_expiry" = NULL,
|
|
jbe@233
|
74 "password_reset_secret" = NULL,
|
|
jbe@233
|
75 "password_reset_secret_expiry" = NULL,
|
|
jbe@233
|
76 "organizational_unit" = NULL,
|
|
jbe@233
|
77 "internal_posts" = NULL,
|
|
jbe@233
|
78 "realname" = NULL,
|
|
jbe@233
|
79 "birthday" = NULL,
|
|
jbe@233
|
80 "address" = NULL,
|
|
jbe@233
|
81 "email" = NULL,
|
|
jbe@233
|
82 "xmpp_address" = NULL,
|
|
jbe@233
|
83 "website" = NULL,
|
|
jbe@233
|
84 "phone" = NULL,
|
|
jbe@233
|
85 "mobile_phone" = NULL,
|
|
jbe@233
|
86 "profession" = NULL,
|
|
jbe@233
|
87 "external_memberships" = NULL,
|
|
jbe@233
|
88 "external_posts" = NULL,
|
|
jbe@233
|
89 "statement" = NULL;
|
|
jbe@233
|
90 -- "text_search_data" is updated by triggers
|
|
jbe@233
|
91 DELETE FROM "setting";
|
|
jbe@233
|
92 DELETE FROM "setting_map";
|
|
jbe@233
|
93 DELETE FROM "member_relation_setting";
|
|
jbe@233
|
94 DELETE FROM "member_image";
|
|
jbe@233
|
95 DELETE FROM "contact";
|
|
jbe@233
|
96 DELETE FROM "ignored_member";
|
|
jbe@233
|
97 DELETE FROM "area_setting";
|
|
jbe@233
|
98 DELETE FROM "issue_setting";
|
|
jbe@233
|
99 DELETE FROM "ignored_initiative";
|
|
jbe@233
|
100 DELETE FROM "initiative_setting";
|
|
jbe@233
|
101 DELETE FROM "suggestion_setting";
|
|
jbe@233
|
102 DELETE FROM "non_voter";
|
|
jbe@233
|
103 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@233
|
104 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@233
|
105 AND "issue"."closed" ISNULL;
|
|
jbe@233
|
106 RETURN;
|
|
jbe@233
|
107 END;
|
|
jbe@233
|
108 $$;
|
|
jbe@233
|
109
|
|
jbe@233
|
110 COMMIT;
|