rev |
line source |
jbe@385
|
1 BEGIN;
|
jbe@385
|
2
|
jbe@385
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@385
|
4 SELECT * FROM (VALUES ('2.2.3', 2, 2, 3))
|
jbe@385
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@385
|
6
|
jbe@385
|
7 DROP TABLE "internal_session_store";
|
jbe@385
|
8
|
jbe@385
|
9 CREATE TABLE "temporary_transaction_data" (
|
jbe@385
|
10 PRIMARY KEY ("txid", "key"),
|
jbe@385
|
11 "txid" INT8 DEFAULT txid_current(),
|
jbe@385
|
12 "key" TEXT,
|
jbe@385
|
13 "value" TEXT NOT NULL );
|
jbe@385
|
14
|
jbe@385
|
15 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
|
jbe@385
|
16
|
jbe@385
|
17 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
|
jbe@385
|
18
|
jbe@385
|
19 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
jbe@385
|
20 RETURNS TRIGGER
|
jbe@385
|
21 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@385
|
22 DECLARE
|
jbe@385
|
23 "issue_id_v" "issue"."id"%TYPE;
|
jbe@385
|
24 "issue_row" "issue"%ROWTYPE;
|
jbe@385
|
25 BEGIN
|
jbe@385
|
26 IF EXISTS (
|
jbe@385
|
27 SELECT NULL FROM "temporary_transaction_data"
|
jbe@385
|
28 WHERE "txid" = txid_current()
|
jbe@385
|
29 AND "key" = 'override_protection_triggers'
|
jbe@385
|
30 AND "value" = TRUE::TEXT
|
jbe@385
|
31 ) THEN
|
jbe@385
|
32 RETURN NULL;
|
jbe@385
|
33 END IF;
|
jbe@385
|
34 IF TG_OP = 'DELETE' THEN
|
jbe@385
|
35 "issue_id_v" := OLD."issue_id";
|
jbe@385
|
36 ELSE
|
jbe@385
|
37 "issue_id_v" := NEW."issue_id";
|
jbe@385
|
38 END IF;
|
jbe@385
|
39 SELECT INTO "issue_row" * FROM "issue"
|
jbe@385
|
40 WHERE "id" = "issue_id_v" FOR SHARE;
|
jbe@385
|
41 IF (
|
jbe@385
|
42 "issue_row"."closed" NOTNULL OR (
|
jbe@385
|
43 "issue_row"."state" = 'voting' AND
|
jbe@385
|
44 "issue_row"."phase_finished" NOTNULL
|
jbe@385
|
45 )
|
jbe@385
|
46 ) THEN
|
jbe@385
|
47 IF
|
jbe@385
|
48 TG_RELID = 'direct_voter'::regclass AND
|
jbe@385
|
49 TG_OP = 'UPDATE'
|
jbe@385
|
50 THEN
|
jbe@385
|
51 IF
|
jbe@385
|
52 OLD."issue_id" = NEW."issue_id" AND
|
jbe@385
|
53 OLD."member_id" = NEW."member_id" AND
|
jbe@385
|
54 OLD."weight" = NEW."weight"
|
jbe@385
|
55 THEN
|
jbe@385
|
56 RETURN NULL; -- allows changing of voter comment
|
jbe@385
|
57 END IF;
|
jbe@385
|
58 END IF;
|
jbe@385
|
59 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
|
jbe@385
|
60 END IF;
|
jbe@385
|
61 RETURN NULL;
|
jbe@385
|
62 END;
|
jbe@385
|
63 $$;
|
jbe@385
|
64
|
jbe@385
|
65 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@385
|
66 RETURNS VOID
|
jbe@385
|
67 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@385
|
68 DECLARE
|
jbe@385
|
69 "area_id_v" "area"."id"%TYPE;
|
jbe@385
|
70 "unit_id_v" "unit"."id"%TYPE;
|
jbe@385
|
71 "member_id_v" "member"."id"%TYPE;
|
jbe@385
|
72 BEGIN
|
jbe@385
|
73 PERFORM "require_transaction_isolation"();
|
jbe@385
|
74 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@385
|
75 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@385
|
76 -- override protection triggers:
|
jbe@385
|
77 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@385
|
78 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@385
|
79 -- delete timestamp of voting comment:
|
jbe@385
|
80 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@385
|
81 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
82 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@385
|
83 DELETE FROM "delegating_voter"
|
jbe@385
|
84 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
85 -- delete votes from non-privileged voters:
|
jbe@385
|
86 DELETE FROM "direct_voter"
|
jbe@385
|
87 USING (
|
jbe@385
|
88 SELECT
|
jbe@385
|
89 "direct_voter"."member_id"
|
jbe@385
|
90 FROM "direct_voter"
|
jbe@385
|
91 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@385
|
92 LEFT JOIN "privilege"
|
jbe@385
|
93 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@385
|
94 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@385
|
95 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@385
|
96 "member"."active" = FALSE OR
|
jbe@385
|
97 "privilege"."voting_right" ISNULL OR
|
jbe@385
|
98 "privilege"."voting_right" = FALSE
|
jbe@385
|
99 )
|
jbe@385
|
100 ) AS "subquery"
|
jbe@385
|
101 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@385
|
102 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@385
|
103 -- consider delegations:
|
jbe@385
|
104 UPDATE "direct_voter" SET "weight" = 1
|
jbe@385
|
105 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
106 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@385
|
107 -- finish overriding protection triggers (avoids garbage):
|
jbe@385
|
108 DELETE FROM "temporary_transaction_data"
|
jbe@385
|
109 WHERE "key" = 'override_protection_triggers';
|
jbe@385
|
110 -- materialize battle_view:
|
jbe@385
|
111 -- NOTE: "closed" column of issue must be set at this point
|
jbe@385
|
112 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
113 INSERT INTO "battle" (
|
jbe@385
|
114 "issue_id",
|
jbe@385
|
115 "winning_initiative_id", "losing_initiative_id",
|
jbe@385
|
116 "count"
|
jbe@385
|
117 ) SELECT
|
jbe@385
|
118 "issue_id",
|
jbe@385
|
119 "winning_initiative_id", "losing_initiative_id",
|
jbe@385
|
120 "count"
|
jbe@385
|
121 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
122 -- set voter count:
|
jbe@385
|
123 UPDATE "issue" SET
|
jbe@385
|
124 "voter_count" = (
|
jbe@385
|
125 SELECT coalesce(sum("weight"), 0)
|
jbe@385
|
126 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@385
|
127 )
|
jbe@385
|
128 WHERE "id" = "issue_id_p";
|
jbe@385
|
129 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@385
|
130 UPDATE "initiative" SET
|
jbe@385
|
131 "positive_votes" = "battle_win"."count",
|
jbe@385
|
132 "negative_votes" = "battle_lose"."count"
|
jbe@385
|
133 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@385
|
134 WHERE
|
jbe@385
|
135 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@385
|
136 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@385
|
137 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@385
|
138 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@385
|
139 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@385
|
140 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@385
|
141 END;
|
jbe@385
|
142 $$;
|
jbe@385
|
143
|
jbe@385
|
144 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@385
|
145 RETURNS VOID
|
jbe@385
|
146 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@385
|
147 BEGIN
|
jbe@385
|
148 IF EXISTS (
|
jbe@385
|
149 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
|
jbe@385
|
150 ) THEN
|
jbe@385
|
151 -- override protection triggers:
|
jbe@385
|
152 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@385
|
153 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@385
|
154 -- clean data:
|
jbe@385
|
155 DELETE FROM "delegating_voter"
|
jbe@385
|
156 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
157 DELETE FROM "direct_voter"
|
jbe@385
|
158 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
159 DELETE FROM "delegating_interest_snapshot"
|
jbe@385
|
160 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
161 DELETE FROM "direct_interest_snapshot"
|
jbe@385
|
162 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
163 DELETE FROM "delegating_population_snapshot"
|
jbe@385
|
164 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
165 DELETE FROM "direct_population_snapshot"
|
jbe@385
|
166 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
167 DELETE FROM "non_voter"
|
jbe@385
|
168 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
169 DELETE FROM "delegation"
|
jbe@385
|
170 WHERE "issue_id" = "issue_id_p";
|
jbe@385
|
171 DELETE FROM "supporter"
|
jbe@385
|
172 USING "initiative" -- NOTE: due to missing index on issue_id
|
jbe@385
|
173 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@385
|
174 AND "supporter"."initiative_id" = "initiative_id";
|
jbe@385
|
175 -- mark issue as cleaned:
|
jbe@385
|
176 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
|
jbe@385
|
177 -- finish overriding protection triggers (avoids garbage):
|
jbe@385
|
178 DELETE FROM "temporary_transaction_data"
|
jbe@385
|
179 WHERE "key" = 'override_protection_triggers';
|
jbe@385
|
180 END IF;
|
jbe@385
|
181 RETURN;
|
jbe@385
|
182 END;
|
jbe@385
|
183 $$;
|
jbe@385
|
184
|
jbe@385
|
185 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@385
|
186 RETURNS VOID
|
jbe@385
|
187 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@385
|
188 BEGIN
|
jbe@385
|
189 DELETE FROM "temporary_transaction_data";
|
jbe@385
|
190 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@385
|
191 UPDATE "member" SET
|
jbe@385
|
192 "invite_code" = NULL,
|
jbe@385
|
193 "invite_code_expiry" = NULL,
|
jbe@385
|
194 "admin_comment" = NULL,
|
jbe@385
|
195 "last_login" = NULL,
|
jbe@385
|
196 "login" = NULL,
|
jbe@385
|
197 "password" = NULL,
|
jbe@385
|
198 "lang" = NULL,
|
jbe@385
|
199 "notify_email" = NULL,
|
jbe@385
|
200 "notify_email_unconfirmed" = NULL,
|
jbe@385
|
201 "notify_email_secret" = NULL,
|
jbe@385
|
202 "notify_email_secret_expiry" = NULL,
|
jbe@385
|
203 "notify_email_lock_expiry" = NULL,
|
jbe@385
|
204 "notify_level" = NULL,
|
jbe@385
|
205 "password_reset_secret" = NULL,
|
jbe@385
|
206 "password_reset_secret_expiry" = NULL,
|
jbe@385
|
207 "organizational_unit" = NULL,
|
jbe@385
|
208 "internal_posts" = NULL,
|
jbe@385
|
209 "realname" = NULL,
|
jbe@385
|
210 "birthday" = NULL,
|
jbe@385
|
211 "address" = NULL,
|
jbe@385
|
212 "email" = NULL,
|
jbe@385
|
213 "xmpp_address" = NULL,
|
jbe@385
|
214 "website" = NULL,
|
jbe@385
|
215 "phone" = NULL,
|
jbe@385
|
216 "mobile_phone" = NULL,
|
jbe@385
|
217 "profession" = NULL,
|
jbe@385
|
218 "external_memberships" = NULL,
|
jbe@385
|
219 "external_posts" = NULL,
|
jbe@385
|
220 "formatting_engine" = NULL,
|
jbe@385
|
221 "statement" = NULL;
|
jbe@385
|
222 -- "text_search_data" is updated by triggers
|
jbe@385
|
223 DELETE FROM "setting";
|
jbe@385
|
224 DELETE FROM "setting_map";
|
jbe@385
|
225 DELETE FROM "member_relation_setting";
|
jbe@385
|
226 DELETE FROM "member_image";
|
jbe@385
|
227 DELETE FROM "contact";
|
jbe@385
|
228 DELETE FROM "ignored_member";
|
jbe@385
|
229 DELETE FROM "session";
|
jbe@385
|
230 DELETE FROM "area_setting";
|
jbe@385
|
231 DELETE FROM "issue_setting";
|
jbe@385
|
232 DELETE FROM "ignored_initiative";
|
jbe@385
|
233 DELETE FROM "initiative_setting";
|
jbe@385
|
234 DELETE FROM "suggestion_setting";
|
jbe@385
|
235 DELETE FROM "non_voter";
|
jbe@385
|
236 DELETE FROM "direct_voter" USING "issue"
|
jbe@385
|
237 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@385
|
238 AND "issue"."closed" ISNULL;
|
jbe@385
|
239 RETURN;
|
jbe@385
|
240 END;
|
jbe@385
|
241 $$;
|
jbe@385
|
242
|
jbe@385
|
243 COMMIT;
|