1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9))

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

6

7

8 ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f';

9

10 CREATE FUNCTION VOLATILE update_supporter_drafts()

11 RETURNS trigger

12 LANGUAGE 'plpgsql' AS $$

13 BEGIN

14 UPDATE supporter SET draft_id = NEW.id

15 WHERE initiative_id = NEW.initiative_id AND

16 (auto_support = 't' OR member_id = NEW.author_id);

17 RETURN new;

18 END

19 $$;

20

21 COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled';

22

23 CREATE TRIGGER "update_draft_supporter"

24 AFTER INSERT ON "draft"

25 FOR EACH ROW EXECUTE PROCEDURE

26 update_supporter_drafts();

27

28 CREATE FUNCTION "check_delegation"()

29 RETURNS TRIGGER

30 LANGUAGE 'plpgsql' VOLATILE AS $$

31 BEGIN

32 IF EXISTS (

33 SELECT NULL FROM "member" WHERE

34 "id" = NEW."trustee_id" AND active = 'n'

35 ) THEN

36 RAISE EXCEPTION 'Cannot delegate to an inactive member';

37 END IF;

38 RETURN NEW;

39 END;

40 $$;

41

42 CREATE TRIGGER "update_delegation"

43 BEFORE INSERT OR UPDATE ON "delegation"

44 FOR EACH ROW EXECUTE PROCEDURE

45 check_delegation();

46

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

48 RETURNS VOID

49 LANGUAGE 'plpgsql' VOLATILE AS $$

50 BEGIN

51 UPDATE "member" SET

52 "last_login" = NULL,

53 "login" = NULL,

54 "password" = NULL,

55 "active" = FALSE,

56 "notify_email" = NULL,

57 "notify_email_unconfirmed" = NULL,

58 "notify_email_secret" = NULL,

59 "notify_email_secret_expiry" = NULL,

60 "notify_email_lock_expiry" = NULL,

61 "password_reset_secret" = NULL,

62 "password_reset_secret_expiry" = NULL,

63 "organizational_unit" = NULL,

64 "internal_posts" = NULL,

65 "realname" = NULL,

66 "birthday" = NULL,

67 "address" = NULL,

68 "email" = NULL,

69 "xmpp_address" = NULL,

70 "website" = NULL,

71 "phone" = NULL,

72 "mobile_phone" = NULL,

73 "profession" = NULL,

74 "external_memberships" = NULL,

75 "external_posts" = NULL,

76 "statement" = NULL

77 WHERE "id" = "member_id_p";

78 -- "text_search_data" is updated by triggers

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

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

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

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

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

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

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

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

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

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

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

90 DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p";

91 DELETE FROM "direct_voter" USING "issue"

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

93 AND "issue"."closed" ISNULL

94 AND "member_id" = "member_id_p";

95 RETURN;

96 END;

97 $$;

98

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

100

101 CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id");

102

103 COMMIT;