rev |
line source |
jbe@262
|
1 BEGIN;
|
jbe@262
|
2
|
jbe@287
|
3
|
jbe@287
|
4 -- update version number
|
jbe@287
|
5
|
jbe@262
|
6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@262
|
7 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
|
jbe@262
|
8 AS "subquery"("string", "major", "minor", "revision");
|
jbe@262
|
9
|
jbe@287
|
10
|
jbe@287
|
11 -- old API tables are now deprecated
|
jbe@287
|
12
|
jbe@286
|
13 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
|
jbe@286
|
14 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
|
jbe@286
|
15
|
jbe@287
|
16
|
jbe@287
|
17 -- new polling mode and changed privileges
|
jbe@287
|
18
|
jbe@262
|
19 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@262
|
20 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL;
|
jbe@262
|
21 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL;
|
jbe@262
|
22 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
|
jbe@262
|
23 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL;
|
jbe@292
|
24 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num" DROP NOT NULL;
|
jbe@292
|
25 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den" DROP NOT NULL;
|
jbe@262
|
26 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
|
jbe@262
|
27 ( "polling" = FALSE AND
|
jbe@262
|
28 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
|
jbe@262
|
29 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
|
jbe@262
|
30 ( "polling" = TRUE AND
|
jbe@263
|
31 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
|
jbe@262
|
32 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
|
jbe@262
|
33 ( "polling" = TRUE AND
|
jbe@262
|
34 "admission_time" ISNULL AND "discussion_time" ISNULL AND
|
jbe@262
|
35 "verification_time" ISNULL AND "voting_time" ISNULL ) );
|
jbe@292
|
36 ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
|
jbe@292
|
37 "polling" = "issue_quorum_num" ISNULL AND
|
jbe@292
|
38 "polling" = "issue_quorum_den" ISNULL );
|
jbe@289
|
39 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
|
jbe@262
|
40
|
jbe@291
|
41 ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL;
|
jbe@291
|
42 ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
|
jbe@291
|
43 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") );
|
jbe@291
|
44
|
jbe@262
|
45 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@289
|
46 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
|
jbe@262
|
47
|
jbe@262
|
48 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
|
jbe@262
|
49 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
|
jbe@262
|
50 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@262
|
51 UPDATE "privilege" SET "initiative_right" = "voting_right";
|
jbe@262
|
52 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
|
jbe@262
|
53 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
|
jbe@262
|
54 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
|
jbe@262
|
55 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
|
jbe@289
|
56 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
|
jbe@262
|
57
|
jbe@293
|
58 DROP VIEW "member_contingent_left";
|
jbe@293
|
59 DROP VIEW "member_contingent";
|
jbe@293
|
60 ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey";
|
jbe@293
|
61 ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL;
|
jbe@294
|
62 ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN DEFAULT FALSE;
|
jbe@293
|
63 ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");
|
jbe@294
|
64 ALTER TABLE "contingent" ALTER COLUMN "polling" DROP DEFAULT;
|
jbe@293
|
65 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
|
jbe@293
|
66
|
jbe@293
|
67 CREATE VIEW "member_contingent" AS
|
jbe@293
|
68 SELECT
|
jbe@293
|
69 "member"."id" AS "member_id",
|
jbe@293
|
70 "contingent"."polling",
|
jbe@293
|
71 "contingent"."time_frame",
|
jbe@293
|
72 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
|
jbe@293
|
73 (
|
jbe@293
|
74 SELECT count(1) FROM "draft"
|
jbe@293
|
75 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
|
jbe@293
|
76 WHERE "draft"."author_id" = "member"."id"
|
jbe@293
|
77 AND "initiative"."polling" = "contingent"."polling"
|
jbe@293
|
78 AND "draft"."created" > now() - "contingent"."time_frame"
|
jbe@293
|
79 ) + (
|
jbe@293
|
80 SELECT count(1) FROM "suggestion"
|
jbe@293
|
81 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
|
jbe@293
|
82 WHERE "suggestion"."author_id" = "member"."id"
|
jbe@293
|
83 AND "contingent"."polling" = FALSE
|
jbe@293
|
84 AND "suggestion"."created" > now() - "contingent"."time_frame"
|
jbe@293
|
85 )
|
jbe@293
|
86 ELSE NULL END AS "text_entry_count",
|
jbe@293
|
87 "contingent"."text_entry_limit",
|
jbe@293
|
88 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
|
jbe@293
|
89 SELECT count(1) FROM "opening_draft" AS "draft"
|
jbe@293
|
90 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
|
jbe@293
|
91 WHERE "draft"."author_id" = "member"."id"
|
jbe@293
|
92 AND "initiative"."polling" = "contingent"."polling"
|
jbe@293
|
93 AND "draft"."created" > now() - "contingent"."time_frame"
|
jbe@293
|
94 ) ELSE NULL END AS "initiative_count",
|
jbe@293
|
95 "contingent"."initiative_limit"
|
jbe@293
|
96 FROM "member" CROSS JOIN "contingent";
|
jbe@293
|
97
|
jbe@293
|
98 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
|
jbe@293
|
99
|
jbe@293
|
100 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
|
jbe@293
|
101 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
|
jbe@293
|
102
|
jbe@293
|
103 CREATE VIEW "member_contingent_left" AS
|
jbe@293
|
104 SELECT
|
jbe@293
|
105 "member_id",
|
jbe@293
|
106 "polling",
|
jbe@293
|
107 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
|
jbe@293
|
108 max("initiative_limit" - "initiative_count") AS "initiatives_left"
|
jbe@293
|
109 FROM "member_contingent" GROUP BY "member_id", "polling";
|
jbe@293
|
110
|
jbe@293
|
111 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
|
jbe@293
|
112
|
jbe@262
|
113 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
|
jbe@262
|
114 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@262
|
115 RETURNS VOID
|
jbe@262
|
116 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@262
|
117 DECLARE
|
jbe@262
|
118 "issue_row" "issue"%ROWTYPE;
|
jbe@262
|
119 "policy_row" "policy"%ROWTYPE;
|
jbe@262
|
120 "initiative_row" "initiative"%ROWTYPE;
|
jbe@262
|
121 BEGIN
|
jbe@262
|
122 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@262
|
123 SELECT * INTO "policy_row"
|
jbe@262
|
124 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@262
|
125 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
|
jbe@262
|
126 FOR "initiative_row" IN
|
jbe@262
|
127 SELECT * FROM "initiative"
|
jbe@262
|
128 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@262
|
129 LOOP
|
jbe@262
|
130 IF
|
jbe@262
|
131 "initiative_row"."polling" OR (
|
jbe@262
|
132 "initiative_row"."satisfied_supporter_count" > 0 AND
|
jbe@262
|
133 "initiative_row"."satisfied_supporter_count" *
|
jbe@262
|
134 "policy_row"."initiative_quorum_den" >=
|
jbe@262
|
135 "issue_row"."population" * "policy_row"."initiative_quorum_num"
|
jbe@262
|
136 )
|
jbe@262
|
137 THEN
|
jbe@262
|
138 UPDATE "initiative" SET "admitted" = TRUE
|
jbe@262
|
139 WHERE "id" = "initiative_row"."id";
|
jbe@262
|
140 ELSE
|
jbe@262
|
141 UPDATE "initiative" SET "admitted" = FALSE
|
jbe@262
|
142 WHERE "id" = "initiative_row"."id";
|
jbe@262
|
143 END IF;
|
jbe@262
|
144 END LOOP;
|
jbe@262
|
145 IF EXISTS (
|
jbe@262
|
146 SELECT NULL FROM "initiative"
|
jbe@262
|
147 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
jbe@262
|
148 ) THEN
|
jbe@262
|
149 UPDATE "issue" SET
|
jbe@262
|
150 "state" = 'voting',
|
jbe@262
|
151 "accepted" = coalesce("accepted", now()),
|
jbe@262
|
152 "half_frozen" = coalesce("half_frozen", now()),
|
jbe@262
|
153 "fully_frozen" = now()
|
jbe@262
|
154 WHERE "id" = "issue_id_p";
|
jbe@262
|
155 ELSE
|
jbe@262
|
156 UPDATE "issue" SET
|
jbe@262
|
157 "state" = 'canceled_no_initiative_admitted',
|
jbe@262
|
158 "accepted" = coalesce("accepted", now()),
|
jbe@262
|
159 "half_frozen" = coalesce("half_frozen", now()),
|
jbe@262
|
160 "fully_frozen" = now(),
|
jbe@262
|
161 "closed" = now(),
|
jbe@262
|
162 "ranks_available" = TRUE
|
jbe@262
|
163 WHERE "id" = "issue_id_p";
|
jbe@262
|
164 -- NOTE: The following DELETE statements have effect only when
|
jbe@262
|
165 -- issue state has been manipulated
|
jbe@262
|
166 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@262
|
167 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@262
|
168 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@262
|
169 END IF;
|
jbe@262
|
170 RETURN;
|
jbe@262
|
171 END;
|
jbe@262
|
172 $$;
|
jbe@262
|
173
|
jbe@287
|
174
|
jbe@287
|
175 -- issue comments removed, voting comments integrated in "direct_voter" table
|
jbe@287
|
176
|
jbe@287
|
177 ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ;
|
jbe@287
|
178 ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
|
jbe@287
|
179 ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT;
|
jbe@287
|
180 ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR;
|
jbe@287
|
181 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
|
jbe@287
|
182 CREATE TRIGGER "update_text_search_data"
|
jbe@287
|
183 BEFORE INSERT OR UPDATE ON "direct_voter"
|
jbe@287
|
184 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@287
|
185 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
|
jbe@287
|
186
|
jbe@287
|
187 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
|
jbe@287
|
188 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
|
jbe@287
|
189 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
|
jbe@287
|
190
|
jbe@287
|
191 CREATE TABLE "rendered_voter_comment" (
|
jbe@287
|
192 PRIMARY KEY ("issue_id", "member_id", "format"),
|
jbe@287
|
193 FOREIGN KEY ("issue_id", "member_id")
|
jbe@287
|
194 REFERENCES "direct_voter" ("issue_id", "member_id")
|
jbe@287
|
195 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@287
|
196 "issue_id" INT4,
|
jbe@287
|
197 "member_id" INT4,
|
jbe@287
|
198 "format" TEXT,
|
jbe@287
|
199 "content" TEXT NOT NULL );
|
jbe@287
|
200
|
jbe@287
|
201 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
|
jbe@287
|
202
|
jbe@287
|
203 DROP TABLE "rendered_issue_comment";
|
jbe@287
|
204 DROP TABLE "issue_comment";
|
jbe@287
|
205 DROP TABLE "rendered_voting_comment";
|
jbe@287
|
206 DROP TABLE "voting_comment";
|
jbe@287
|
207
|
jbe@287
|
208 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
|
jbe@287
|
209 RETURNS TRIGGER
|
jbe@287
|
210 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@287
|
211 BEGIN
|
jbe@287
|
212 IF NEW."comment" ISNULL THEN
|
jbe@287
|
213 NEW."comment_changed" := NULL;
|
jbe@287
|
214 NEW."formatting_engine" := NULL;
|
jbe@287
|
215 END IF;
|
jbe@287
|
216 RETURN NEW;
|
jbe@287
|
217 END;
|
jbe@287
|
218 $$;
|
jbe@287
|
219
|
jbe@287
|
220 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
|
jbe@287
|
221 BEFORE INSERT OR UPDATE ON "direct_voter"
|
jbe@287
|
222 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@287
|
223 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
|
jbe@287
|
224
|
jbe@287
|
225 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
|
jbe@287
|
226 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
|
jbe@287
|
227
|
jbe@290
|
228 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
jbe@290
|
229 RETURNS TRIGGER
|
jbe@290
|
230 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@290
|
231 DECLARE
|
jbe@290
|
232 "issue_id_v" "issue"."id"%TYPE;
|
jbe@290
|
233 "issue_row" "issue"%ROWTYPE;
|
jbe@290
|
234 BEGIN
|
jbe@290
|
235 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
|
jbe@290
|
236 IF
|
jbe@290
|
237 OLD."issue_id" = NEW."issue_id" AND
|
jbe@290
|
238 OLD."member_id" = NEW."member_id" AND
|
jbe@290
|
239 OLD."weight" = NEW."weight"
|
jbe@290
|
240 THEN
|
jbe@290
|
241 RETURN NULL; -- allows changing of voter comment
|
jbe@290
|
242 END IF;
|
jbe@290
|
243 END IF;
|
jbe@290
|
244 IF TG_OP = 'DELETE' THEN
|
jbe@290
|
245 "issue_id_v" := OLD."issue_id";
|
jbe@290
|
246 ELSE
|
jbe@290
|
247 "issue_id_v" := NEW."issue_id";
|
jbe@290
|
248 END IF;
|
jbe@290
|
249 SELECT INTO "issue_row" * FROM "issue"
|
jbe@290
|
250 WHERE "id" = "issue_id_v" FOR SHARE;
|
jbe@290
|
251 IF "issue_row"."closed" NOTNULL THEN
|
jbe@290
|
252 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
|
jbe@290
|
253 END IF;
|
jbe@290
|
254 RETURN NULL;
|
jbe@290
|
255 END;
|
jbe@290
|
256 $$;
|
jbe@290
|
257
|
jbe@285
|
258 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@285
|
259 RETURNS VOID
|
jbe@285
|
260 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@285
|
261 DECLARE
|
jbe@285
|
262 "area_id_v" "area"."id"%TYPE;
|
jbe@285
|
263 "unit_id_v" "unit"."id"%TYPE;
|
jbe@285
|
264 "member_id_v" "member"."id"%TYPE;
|
jbe@285
|
265 BEGIN
|
jbe@285
|
266 PERFORM "lock_issue"("issue_id_p");
|
jbe@285
|
267 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@285
|
268 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@285
|
269 -- delete timestamp of voting comment:
|
jbe@285
|
270 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@285
|
271 WHERE "issue_id" = "issue_id_p";
|
jbe@285
|
272 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@285
|
273 DELETE FROM "delegating_voter"
|
jbe@285
|
274 WHERE "issue_id" = "issue_id_p";
|
jbe@285
|
275 -- delete votes from non-privileged voters:
|
jbe@285
|
276 DELETE FROM "direct_voter"
|
jbe@285
|
277 USING (
|
jbe@285
|
278 SELECT
|
jbe@285
|
279 "direct_voter"."member_id"
|
jbe@285
|
280 FROM "direct_voter"
|
jbe@285
|
281 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@285
|
282 LEFT JOIN "privilege"
|
jbe@285
|
283 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@285
|
284 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@285
|
285 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@285
|
286 "member"."active" = FALSE OR
|
jbe@285
|
287 "privilege"."voting_right" ISNULL OR
|
jbe@285
|
288 "privilege"."voting_right" = FALSE
|
jbe@285
|
289 )
|
jbe@285
|
290 ) AS "subquery"
|
jbe@285
|
291 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@285
|
292 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@285
|
293 -- consider delegations:
|
jbe@285
|
294 UPDATE "direct_voter" SET "weight" = 1
|
jbe@285
|
295 WHERE "issue_id" = "issue_id_p";
|
jbe@285
|
296 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@285
|
297 -- set voter count and mark issue as being calculated:
|
jbe@285
|
298 UPDATE "issue" SET
|
jbe@285
|
299 "state" = 'calculation',
|
jbe@285
|
300 "closed" = now(),
|
jbe@285
|
301 "voter_count" = (
|
jbe@285
|
302 SELECT coalesce(sum("weight"), 0)
|
jbe@285
|
303 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@285
|
304 )
|
jbe@285
|
305 WHERE "id" = "issue_id_p";
|
jbe@285
|
306 -- materialize battle_view:
|
jbe@285
|
307 -- NOTE: "closed" column of issue must be set at this point
|
jbe@285
|
308 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@285
|
309 INSERT INTO "battle" (
|
jbe@285
|
310 "issue_id",
|
jbe@285
|
311 "winning_initiative_id", "losing_initiative_id",
|
jbe@285
|
312 "count"
|
jbe@285
|
313 ) SELECT
|
jbe@285
|
314 "issue_id",
|
jbe@285
|
315 "winning_initiative_id", "losing_initiative_id",
|
jbe@285
|
316 "count"
|
jbe@285
|
317 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@285
|
318 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@285
|
319 UPDATE "initiative" SET
|
jbe@285
|
320 "positive_votes" = "battle_win"."count",
|
jbe@285
|
321 "negative_votes" = "battle_lose"."count"
|
jbe@285
|
322 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@285
|
323 WHERE
|
jbe@285
|
324 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@285
|
325 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@285
|
326 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@285
|
327 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@285
|
328 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@285
|
329 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@285
|
330 END;
|
jbe@285
|
331 $$;
|
jbe@285
|
332
|
jbe@288
|
333 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@288
|
334 RETURNS VOID
|
jbe@288
|
335 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@288
|
336 DECLARE
|
jbe@288
|
337 "issue_row" "issue"%ROWTYPE;
|
jbe@288
|
338 BEGIN
|
jbe@288
|
339 SELECT * INTO "issue_row"
|
jbe@288
|
340 FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@288
|
341 FOR UPDATE;
|
jbe@288
|
342 IF "issue_row"."cleaned" ISNULL THEN
|
jbe@288
|
343 UPDATE "issue" SET
|
jbe@288
|
344 "state" = 'voting',
|
jbe@288
|
345 "closed" = NULL,
|
jbe@288
|
346 "ranks_available" = FALSE
|
jbe@288
|
347 WHERE "id" = "issue_id_p";
|
jbe@288
|
348 DELETE FROM "delegating_voter"
|
jbe@288
|
349 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
350 DELETE FROM "direct_voter"
|
jbe@288
|
351 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
352 DELETE FROM "delegating_interest_snapshot"
|
jbe@288
|
353 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
354 DELETE FROM "direct_interest_snapshot"
|
jbe@288
|
355 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
356 DELETE FROM "delegating_population_snapshot"
|
jbe@288
|
357 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
358 DELETE FROM "direct_population_snapshot"
|
jbe@288
|
359 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
360 DELETE FROM "non_voter"
|
jbe@288
|
361 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
362 DELETE FROM "delegation"
|
jbe@288
|
363 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
364 DELETE FROM "supporter"
|
jbe@288
|
365 WHERE "issue_id" = "issue_id_p";
|
jbe@288
|
366 UPDATE "issue" SET
|
jbe@288
|
367 "state" = "issue_row"."state",
|
jbe@288
|
368 "closed" = "issue_row"."closed",
|
jbe@288
|
369 "ranks_available" = "issue_row"."ranks_available",
|
jbe@288
|
370 "cleaned" = now()
|
jbe@288
|
371 WHERE "id" = "issue_id_p";
|
jbe@288
|
372 END IF;
|
jbe@288
|
373 RETURN;
|
jbe@288
|
374 END;
|
jbe@288
|
375 $$;
|
jbe@288
|
376
|
jbe@287
|
377
|
jbe@287
|
378 -- "non_voter" deletes "direct_voter" and vice versa
|
jbe@287
|
379
|
jbe@287
|
380 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
|
jbe@287
|
381 RETURNS TRIGGER
|
jbe@287
|
382 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@287
|
383 BEGIN
|
jbe@287
|
384 DELETE FROM "direct_voter"
|
jbe@287
|
385 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
|
jbe@287
|
386 RETURN NULL;
|
jbe@287
|
387 END;
|
jbe@287
|
388 $$;
|
jbe@287
|
389
|
jbe@287
|
390 CREATE TRIGGER "non_voter_deletes_direct_voter"
|
jbe@287
|
391 AFTER INSERT OR UPDATE ON "non_voter"
|
jbe@287
|
392 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@287
|
393 "non_voter_deletes_direct_voter_trigger"();
|
jbe@287
|
394
|
jbe@287
|
395 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
|
jbe@287
|
396 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
|
jbe@287
|
397
|
jbe@287
|
398 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
|
jbe@287
|
399 RETURNS TRIGGER
|
jbe@287
|
400 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@287
|
401 BEGIN
|
jbe@287
|
402 DELETE FROM "non_voter"
|
jbe@287
|
403 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
|
jbe@287
|
404 RETURN NULL;
|
jbe@287
|
405 END;
|
jbe@287
|
406 $$;
|
jbe@287
|
407
|
jbe@287
|
408 CREATE TRIGGER "direct_voter_deletes_non_voter"
|
jbe@287
|
409 AFTER INSERT OR UPDATE ON "direct_voter"
|
jbe@287
|
410 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@287
|
411 "direct_voter_deletes_non_voter_trigger"();
|
jbe@287
|
412
|
jbe@287
|
413 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
|
jbe@287
|
414 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
|
jbe@287
|
415
|
jbe@287
|
416
|
jbe@297
|
417 -- different locking levels and different locking order to avoid deadlocks
|
jbe@297
|
418
|
jbe@297
|
419 CREATE OR REPLACE FUNCTION "lock_issue"
|
jbe@297
|
420 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@297
|
421 RETURNS VOID
|
jbe@297
|
422 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@297
|
423 BEGIN
|
jbe@297
|
424 -- The following locking order is used:
|
jbe@297
|
425 -- 1st) row-level lock on the issue
|
jbe@297
|
426 -- 2nd) table-level locks in order of occurrence in the core.sql file
|
jbe@297
|
427 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
jbe@297
|
428 -- NOTE: The row-level exclusive lock in combination with the
|
jbe@297
|
429 -- share_row_lock_issue(_via_initiative)_trigger functions (which
|
jbe@297
|
430 -- acquire a row-level share lock on the issue) ensure that no data
|
jbe@297
|
431 -- is changed, which could affect calculation of snapshots or
|
jbe@297
|
432 -- counting of votes. Table "delegation" must be table-level-locked,
|
jbe@297
|
433 -- as it also contains issue- and global-scope delegations.
|
jbe@301
|
434 PERFORM NULL FROM "member" WHERE "active" FOR SHARE;
|
jbe@299
|
435 -- NOTE: As we later cause implicit row-level share locks on many
|
jbe@301
|
436 -- active members, we lock them before locking any other table
|
jbe@301
|
437 -- to avoid deadlocks
|
jbe@301
|
438 LOCK TABLE "member" IN SHARE MODE;
|
jbe@297
|
439 LOCK TABLE "privilege" IN SHARE MODE;
|
jbe@297
|
440 LOCK TABLE "membership" IN SHARE MODE;
|
jbe@297
|
441 LOCK TABLE "policy" IN SHARE MODE;
|
jbe@297
|
442 LOCK TABLE "delegation" IN SHARE MODE;
|
jbe@297
|
443 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@297
|
444 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@297
|
445 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@297
|
446 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@297
|
447 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
jbe@297
|
448 RETURN;
|
jbe@297
|
449 END;
|
jbe@297
|
450 $$;
|
jbe@297
|
451
|
jbe@297
|
452
|
jbe@287
|
453 -- new comment on function "delete_private_data"()
|
jbe@287
|
454
|
jbe@283
|
455 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
|
jbe@283
|
456
|
jbe@287
|
457
|
jbe@308
|
458 -- NOTE: The first version of the previous update script didn't
|
jbe@308
|
459 -- remove the "vote_ratio" function.
|
jbe@308
|
460 -- The function is therefore removed here as well, if existent.
|
jbe@308
|
461
|
jbe@308
|
462 DROP FUNCTION IF EXISTS "vote_ratio"
|
jbe@308
|
463 ( "initiative"."positive_votes"%TYPE,
|
jbe@308
|
464 "initiative"."negative_votes"%TYPE );
|
jbe@308
|
465
|
jbe@308
|
466
|
jbe@262
|
467 COMMIT;
|