| rev |
line source |
|
poelzi@79
|
1 BEGIN;
|
|
poelzi@79
|
2
|
|
poelzi@79
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
poelzi@79
|
4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9))
|
|
poelzi@79
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
poelzi@79
|
6
|
|
poelzi@79
|
7
|
|
poelzi@79
|
8 ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f';
|
|
poelzi@79
|
9
|
|
poelzi@80
|
10 CREATE FUNCTION VOLATILE update_supporter_drafts()
|
|
poelzi@79
|
11 RETURNS trigger
|
|
poelzi@79
|
12 LANGUAGE 'plpgsql' AS $$
|
|
poelzi@79
|
13 BEGIN
|
|
poelzi@79
|
14 UPDATE supporter SET draft_id = NEW.id
|
|
poelzi@79
|
15 WHERE initiative_id = NEW.initiative_id AND
|
|
poelzi@79
|
16 (auto_support = 't' OR member_id = NEW.author_id);
|
|
poelzi@79
|
17 RETURN new;
|
|
poelzi@79
|
18 END
|
|
poelzi@79
|
19 $$;
|
|
poelzi@79
|
20
|
|
poelzi@79
|
21 COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled';
|
|
poelzi@79
|
22
|
|
poelzi@79
|
23 CREATE TRIGGER "update_draft_supporter"
|
|
poelzi@79
|
24 AFTER INSERT ON "draft"
|
|
poelzi@79
|
25 FOR EACH ROW EXECUTE PROCEDURE
|
|
poelzi@79
|
26 update_supporter_drafts();
|
|
poelzi@79
|
27
|
|
poelzi@79
|
28 CREATE FUNCTION "check_delegation"()
|
|
poelzi@79
|
29 RETURNS TRIGGER
|
|
poelzi@79
|
30 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
poelzi@79
|
31 BEGIN
|
|
poelzi@79
|
32 IF EXISTS (
|
|
poelzi@79
|
33 SELECT NULL FROM "member" WHERE
|
|
poelzi@79
|
34 "id" = NEW."trustee_id" AND active = 'n'
|
|
poelzi@79
|
35 ) THEN
|
|
poelzi@79
|
36 RAISE EXCEPTION 'Cannot delegate to an inactive member';
|
|
poelzi@79
|
37 END IF;
|
|
poelzi@79
|
38 RETURN NEW;
|
|
poelzi@79
|
39 END;
|
|
poelzi@79
|
40 $$;
|
|
poelzi@79
|
41
|
|
poelzi@79
|
42 CREATE TRIGGER "update_delegation"
|
|
poelzi@79
|
43 BEFORE INSERT OR UPDATE ON "delegation"
|
|
poelzi@79
|
44 FOR EACH ROW EXECUTE PROCEDURE
|
|
poelzi@79
|
45 check_delegation();
|
|
poelzi@79
|
46
|
|
poelzi@79
|
47 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
|
poelzi@79
|
48 RETURNS VOID
|
|
poelzi@79
|
49 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
poelzi@79
|
50 BEGIN
|
|
poelzi@79
|
51 UPDATE "member" SET
|
|
poelzi@79
|
52 "last_login" = NULL,
|
|
poelzi@79
|
53 "login" = NULL,
|
|
poelzi@79
|
54 "password" = NULL,
|
|
poelzi@79
|
55 "active" = FALSE,
|
|
poelzi@79
|
56 "notify_email" = NULL,
|
|
poelzi@79
|
57 "notify_email_unconfirmed" = NULL,
|
|
poelzi@79
|
58 "notify_email_secret" = NULL,
|
|
poelzi@79
|
59 "notify_email_secret_expiry" = NULL,
|
|
poelzi@79
|
60 "notify_email_lock_expiry" = NULL,
|
|
poelzi@79
|
61 "password_reset_secret" = NULL,
|
|
poelzi@79
|
62 "password_reset_secret_expiry" = NULL,
|
|
poelzi@79
|
63 "organizational_unit" = NULL,
|
|
poelzi@79
|
64 "internal_posts" = NULL,
|
|
poelzi@79
|
65 "realname" = NULL,
|
|
poelzi@79
|
66 "birthday" = NULL,
|
|
poelzi@79
|
67 "address" = NULL,
|
|
poelzi@79
|
68 "email" = NULL,
|
|
poelzi@79
|
69 "xmpp_address" = NULL,
|
|
poelzi@79
|
70 "website" = NULL,
|
|
poelzi@79
|
71 "phone" = NULL,
|
|
poelzi@79
|
72 "mobile_phone" = NULL,
|
|
poelzi@79
|
73 "profession" = NULL,
|
|
poelzi@79
|
74 "external_memberships" = NULL,
|
|
poelzi@79
|
75 "external_posts" = NULL,
|
|
poelzi@79
|
76 "statement" = NULL
|
|
poelzi@79
|
77 WHERE "id" = "member_id_p";
|
|
poelzi@79
|
78 -- "text_search_data" is updated by triggers
|
|
poelzi@79
|
79 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
80 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
81 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
82 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
83 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
84 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
85 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
86 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
87 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
88 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
|
poelzi@79
|
89 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
|
poelzi@79
|
90 DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p";
|
|
poelzi@79
|
91 DELETE FROM "direct_voter" USING "issue"
|
|
poelzi@79
|
92 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
poelzi@79
|
93 AND "issue"."closed" ISNULL
|
|
poelzi@79
|
94 AND "member_id" = "member_id_p";
|
|
poelzi@79
|
95 RETURN;
|
|
poelzi@79
|
96 END;
|
|
poelzi@79
|
97 $$;
|
|
poelzi@79
|
98
|
|
poelzi@79
|
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)';
|
|
poelzi@79
|
100
|
|
poelzi@81
|
101 CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id");
|
|
poelzi@81
|
102
|
|
poelzi@79
|
103 COMMIT;
|