1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))

5 AS "subquery"("string", "major", "minor", "revision");

6

7 ALTER TABLE "member_history" DROP COLUMN "login";

8

9 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';

10 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';

11

12 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()

13 RETURNS TRIGGER

14 LANGUAGE 'plpgsql' VOLATILE AS $$

15 BEGIN

16 IF

17 NEW."active" != OLD."active" OR

18 NEW."name" != OLD."name"

19 THEN

20 INSERT INTO "member_history"

21 ("member_id", "active", "name")

22 VALUES (NEW."id", OLD."active", OLD."name");

23 END IF;

24 RETURN NULL;

25 END;

26 $$;

27

28 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';

29

30 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

31 RETURNS VOID

32 LANGUAGE 'plpgsql' VOLATILE AS $$

33 BEGIN

34 UPDATE "member" SET

35 "last_login" = NULL,

36 "login" = NULL,

37 "password" = NULL,

38 "active" = FALSE,

39 "notify_email" = NULL,

40 "notify_email_unconfirmed" = NULL,

41 "notify_email_secret" = NULL,

42 "notify_email_secret_expiry" = NULL,

43 "notify_email_lock_expiry" = NULL,

44 "password_reset_secret" = NULL,

45 "password_reset_secret_expiry" = NULL,

46 "organizational_unit" = NULL,

47 "internal_posts" = NULL,

48 "realname" = NULL,

49 "birthday" = NULL,

50 "address" = NULL,

51 "email" = NULL,

52 "xmpp_address" = NULL,

53 "website" = NULL,

54 "phone" = NULL,

55 "mobile_phone" = NULL,

56 "profession" = NULL,

57 "external_memberships" = NULL,

58 "external_posts" = NULL,

59 "statement" = NULL

60 WHERE "id" = "member_id_p";

61 -- "text_search_data" is updated by triggers

62 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

63 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

64 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

65 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

66 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

67 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

68 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

69 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

70 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

71 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

72 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

73 DELETE FROM "direct_voter" USING "issue"

74 WHERE "direct_voter"."issue_id" = "issue"."id"

75 AND "issue"."closed" ISNULL

76 AND "member_id" = "member_id_p";

77 RETURN;

78 END;

79 $$;

80

81 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';

82

83 CREATE OR REPLACE FUNCTION "delete_private_data"()

84 RETURNS VOID

85 LANGUAGE 'plpgsql' VOLATILE AS $$

86 BEGIN

87 UPDATE "member" SET

88 "last_login" = NULL,

89 "login" = NULL,

90 "password" = NULL,

91 "notify_email" = NULL,

92 "notify_email_unconfirmed" = NULL,

93 "notify_email_secret" = NULL,

94 "notify_email_secret_expiry" = NULL,

95 "notify_email_lock_expiry" = NULL,

96 "password_reset_secret" = NULL,

97 "password_reset_secret_expiry" = NULL,

98 "organizational_unit" = NULL,

99 "internal_posts" = NULL,

100 "realname" = NULL,

101 "birthday" = NULL,

102 "address" = NULL,

103 "email" = NULL,

104 "xmpp_address" = NULL,

105 "website" = NULL,

106 "phone" = NULL,

107 "mobile_phone" = NULL,

108 "profession" = NULL,

109 "external_memberships" = NULL,

110 "external_posts" = NULL,

111 "statement" = NULL;

112 -- "text_search_data" is updated by triggers

113 DELETE FROM "invite_code";

114 DELETE FROM "setting";

115 DELETE FROM "setting_map";

116 DELETE FROM "member_relation_setting";

117 DELETE FROM "member_image";

118 DELETE FROM "contact";

119 DELETE FROM "session";

120 DELETE FROM "area_setting";

121 DELETE FROM "issue_setting";

122 DELETE FROM "initiative_setting";

123 DELETE FROM "suggestion_setting";

124 DELETE FROM "direct_voter" USING "issue"

125 WHERE "direct_voter"."issue_id" = "issue"."id"

126 AND "issue"."closed" ISNULL;

127 RETURN;

128 END;

129 $$;

130

131 COMMIT;