1 BEGIN;

2

3 DROP VIEW "liquid_feedback_version";

4 CREATE VIEW "liquid_feedback_version" AS

5 SELECT * FROM (VALUES ('1.0.0', 1, 0, 0))

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

7

8 ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL;

9

10 ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL;

11

12 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");

13

14 ALTER TABLE "direct_population_snapshot" DROP

15 CONSTRAINT "direct_population_snapshot_member_id_fkey";

16 ALTER TABLE "direct_population_snapshot" ADD

17 CONSTRAINT "direct_population_snapshot_member_id_fkey"

18 FOREIGN KEY ("member_id")

19 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

20

21 ALTER TABLE "delegating_population_snapshot" DROP

22 CONSTRAINT "delegating_population_snapshot_member_id_fkey";

23 ALTER TABLE "delegating_population_snapshot" ADD

24 CONSTRAINT "delegating_population_snapshot_member_id_fkey"

25 FOREIGN KEY ("member_id")

26 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

27

28 ALTER TABLE "direct_interest_snapshot" DROP

29 CONSTRAINT "direct_interest_snapshot_member_id_fkey";

30 ALTER TABLE "direct_interest_snapshot" ADD

31 CONSTRAINT "direct_interest_snapshot_member_id_fkey"

32 FOREIGN KEY ("member_id")

33 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

34

35 ALTER TABLE "delegating_interest_snapshot" DROP

36 CONSTRAINT "delegating_interest_snapshot_member_id_fkey";

37 ALTER TABLE "delegating_interest_snapshot" ADD

38 CONSTRAINT "delegating_interest_snapshot_member_id_fkey"

39 FOREIGN KEY ("member_id")

40 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

41

42 ALTER TABLE "direct_supporter_snapshot" DROP

43 CONSTRAINT "direct_supporter_snapshot_member_id_fkey";

44 ALTER TABLE "direct_supporter_snapshot" ADD

45 CONSTRAINT "direct_supporter_snapshot_member_id_fkey"

46 FOREIGN KEY ("member_id")

47 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

48

49 ALTER TABLE "direct_voter" DROP

50 CONSTRAINT "direct_voter_member_id_fkey";

51 ALTER TABLE "direct_voter" ADD

52 CONSTRAINT "direct_voter_member_id_fkey"

53 FOREIGN KEY ("member_id")

54 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

55

56 ALTER TABLE "delegating_voter" DROP

57 CONSTRAINT "delegating_voter_member_id_fkey";

58 ALTER TABLE "delegating_voter" ADD

59 CONSTRAINT "delegating_voter_member_id_fkey"

60 FOREIGN KEY ("member_id")

61 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

62

63 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()

64 RETURNS TRIGGER

65 LANGUAGE 'plpgsql' VOLATILE AS $$

66 BEGIN

67 IF

68 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND

69 NEW."login" != OLD."login" ) OR

70 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR

71 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR

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

73 NEW."name" != OLD."name"

74 THEN

75 INSERT INTO "member_history"

76 ("member_id", "login", "active", "name")

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

78 END IF;

79 RETURN NULL;

80 END;

81 $$;

82

83 CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE)

84 RETURNS VOID

85 LANGUAGE 'plpgsql' VOLATILE AS $$

86 BEGIN

87 UPDATE "member" SET

88 "login" = NULL,

89 "password" = NULL,

90 "notify_email" = NULL,

91 "notify_email_unconfirmed" = NULL,

92 "notify_email_secret" = NULL,

93 "notify_email_secret_expiry" = NULL,

94 "password_reset_secret" = NULL,

95 "password_reset_secret_expiry" = NULL,

96 "organizational_unit" = NULL,

97 "internal_posts" = NULL,

98 "realname" = NULL,

99 "birthday" = NULL,

100 "address" = NULL,

101 "email" = NULL,

102 "xmpp_address" = NULL,

103 "website" = NULL,

104 "phone" = NULL,

105 "mobile_phone" = NULL,

106 "profession" = NULL,

107 "external_memberships" = NULL,

108 "external_posts" = NULL,

109 "statement" = NULL

110 WHERE "id" = "member_id_p";

111 -- "text_search_data" is updated by triggers

112 UPDATE "member_history" SET "login" = NULL

113 WHERE "member_id" = "member_id_p";

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

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

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

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

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

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

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

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

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

123 RETURN;

124 END;

125 $$;

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)';

127

128 CREATE OR REPLACE FUNCTION "delete_private_data"()

129 RETURNS VOID

130 LANGUAGE 'plpgsql' VOLATILE AS $$

131 BEGIN

132 PERFORM "delete_member_data"("id") FROM "member";

133 DELETE FROM "invite_code";

134 DELETE FROM "session";

135 DELETE FROM "direct_voter" USING "issue"

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

137 AND "issue"."closed" ISNULL;

138 RETURN;

139 END;

140 $$;

141

142 COMMIT;