liquid_feedback_core

view update/core-update.beta26-v1.0.0.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
parents 5a01d558565b
children
line source
1 BEGIN;
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");
8 ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL;
10 ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL;
12 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
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;
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;
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;
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;
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;
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;
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;
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 $$;
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)';
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 $$;
142 COMMIT;

Impressum / About Us