1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('3.0.4', 3, 0, 4))

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

6

7 ALTER TABLE "member" ADD COLUMN "authority" TEXT;

8 ALTER TABLE "member" ADD COLUMN "authority_uid" TEXT;

9 ALTER TABLE "member" ADD COLUMN "authority_login" TEXT;

10

11 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';

12 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';

13 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';

14

15 ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa"

16 CHECK ("authority" NOTNULL = "authority_uid" NOTNULL);

17

18 ALTER TABLE "member" ADD CONSTRAINT "authority_uid_unique_per_authority"

19 UNIQUE ("authority", "authority_uid");

20

21 ALTER TABLE "member" ADD CONSTRAINT "authority_login_requires_authority"

22 CHECK ("authority" NOTNULL OR "authority_login" ISNULL);

23

24 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");

25

26 ALTER TABLE "session" ADD COLUMN "authority" TEXT;

27 ALTER TABLE "session" ADD COLUMN "authority_uid" TEXT;

28 ALTER TABLE "session" ADD COLUMN "authority_login" TEXT;

29

30 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';

31 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';

32 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';

33

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

35 RETURNS VOID

36 LANGUAGE 'plpgsql' VOLATILE AS $$

37 BEGIN

38 UPDATE "member" SET

39 "last_login" = NULL,

40 "last_delegation_check" = NULL,

41 "login" = NULL,

42 "password" = NULL,

43 "authority" = NULL,

44 "authority_uid" = NULL,

45 "authority_login" = NULL,

46 "locked" = TRUE,

47 "active" = FALSE,

48 "notify_email" = NULL,

49 "notify_email_unconfirmed" = NULL,

50 "notify_email_secret" = NULL,

51 "notify_email_secret_expiry" = NULL,

52 "notify_email_lock_expiry" = NULL,

53 "login_recovery_expiry" = NULL,

54 "password_reset_secret" = NULL,

55 "password_reset_secret_expiry" = NULL,

56 "organizational_unit" = NULL,

57 "internal_posts" = NULL,

58 "realname" = NULL,

59 "birthday" = NULL,

60 "address" = NULL,

61 "email" = NULL,

62 "xmpp_address" = NULL,

63 "website" = NULL,

64 "phone" = NULL,

65 "mobile_phone" = NULL,

66 "profession" = NULL,

67 "external_memberships" = NULL,

68 "external_posts" = NULL,

69 "statement" = NULL

70 WHERE "id" = "member_id_p";

71 -- "text_search_data" is updated by triggers

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

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

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

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

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

77 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

78 DELETE FROM "session" WHERE "member_id" = "member_id_p";

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

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

81 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

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

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

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

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

86 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

87 DELETE FROM "direct_voter" USING "issue"

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

89 AND "issue"."closed" ISNULL

90 AND "member_id" = "member_id_p";

91 RETURN;

92 END;

93 $$;

94

95 CREATE OR REPLACE FUNCTION "delete_private_data"()

96 RETURNS VOID

97 LANGUAGE 'plpgsql' VOLATILE AS $$

98 BEGIN

99 DELETE FROM "temporary_transaction_data";

100 DELETE FROM "member" WHERE "activated" ISNULL;

101 UPDATE "member" SET

102 "invite_code" = NULL,

103 "invite_code_expiry" = NULL,

104 "admin_comment" = NULL,

105 "last_login" = NULL,

106 "last_delegation_check" = NULL,

107 "login" = NULL,

108 "password" = NULL,

109 "authority" = NULL,

110 "authority_uid" = NULL,

111 "authority_login" = NULL,

112 "lang" = NULL,

113 "notify_email" = NULL,

114 "notify_email_unconfirmed" = NULL,

115 "notify_email_secret" = NULL,

116 "notify_email_secret_expiry" = NULL,

117 "notify_email_lock_expiry" = NULL,

118 "notify_level" = NULL,

119 "login_recovery_expiry" = NULL,

120 "password_reset_secret" = NULL,

121 "password_reset_secret_expiry" = NULL,

122 "organizational_unit" = NULL,

123 "internal_posts" = NULL,

124 "realname" = NULL,

125 "birthday" = NULL,

126 "address" = NULL,

127 "email" = NULL,

128 "xmpp_address" = NULL,

129 "website" = NULL,

130 "phone" = NULL,

131 "mobile_phone" = NULL,

132 "profession" = NULL,

133 "external_memberships" = NULL,

134 "external_posts" = NULL,

135 "formatting_engine" = NULL,

136 "statement" = NULL;

137 -- "text_search_data" is updated by triggers

138 DELETE FROM "setting";

139 DELETE FROM "setting_map";

140 DELETE FROM "member_relation_setting";

141 DELETE FROM "member_image";

142 DELETE FROM "contact";

143 DELETE FROM "ignored_member";

144 DELETE FROM "session";

145 DELETE FROM "area_setting";

146 DELETE FROM "issue_setting";

147 DELETE FROM "ignored_initiative";

148 DELETE FROM "initiative_setting";

149 DELETE FROM "suggestion_setting";

150 DELETE FROM "non_voter";

151 DELETE FROM "direct_voter" USING "issue"

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

153 AND "issue"."closed" ISNULL;

154 RETURN;

155 END;

156 $$;

157

158 COMMIT;