rev |
line source |
jbe@115
|
1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
|
jbe@115
|
2
|
jbe@115
|
3
|
jbe@115
|
4 -- Update version information:
|
jbe@115
|
5
|
jbe@115
|
6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@115
|
7 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
|
jbe@115
|
8 AS "subquery"("string", "major", "minor", "revision");
|
jbe@115
|
9
|
jbe@115
|
10
|
jbe@115
|
11 -- New columns "notify_level" and "notify_event_id" in "member" table:
|
jbe@115
|
12
|
jbe@115
|
13 CREATE TYPE "notify_level" AS ENUM
|
jbe@115
|
14 ('none', 'voting', 'verification', 'discussion', 'all');
|
jbe@115
|
15
|
jbe@115
|
16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
|
jbe@115
|
17
|
jbe@115
|
18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
|
jbe@115
|
19 ALTER TABLE "member" ADD "notify_event_id" INT8;
|
jbe@115
|
20
|
jbe@115
|
21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
|
jbe@115
|
22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
|
jbe@115
|
23
|
jbe@115
|
24
|
jbe@115
|
25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
|
jbe@115
|
26
|
jbe@115
|
27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
|
jbe@115
|
28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
|
jbe@115
|
29 ALTER TABLE "invite_code" ADD UNIQUE ("code");
|
jbe@115
|
30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
|
jbe@115
|
31
|
jbe@115
|
32
|
jbe@115
|
33 -- Add index for "other_member_id" column of "contact" table:
|
jbe@115
|
34
|
jbe@115
|
35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
|
jbe@115
|
36
|
jbe@115
|
37
|
jbe@115
|
38 -- New table "ignored_member":
|
jbe@115
|
39
|
jbe@115
|
40 CREATE TABLE "ignored_member" (
|
jbe@115
|
41 PRIMARY KEY ("member_id", "other_member_id"),
|
jbe@115
|
42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@115
|
44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
|
jbe@115
|
45
|
jbe@115
|
46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
|
jbe@115
|
47
|
jbe@115
|
48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
|
jbe@115
|
49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
|
jbe@115
|
50
|
jbe@115
|
51
|
jbe@115
|
52 -- New table "unit" with default entry:
|
jbe@115
|
53
|
jbe@115
|
54 CREATE TABLE "unit" (
|
jbe@115
|
55 "id" SERIAL4 PRIMARY KEY,
|
jbe@115
|
56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
57 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
jbe@115
|
58 "name" TEXT NOT NULL,
|
jbe@115
|
59 "description" TEXT NOT NULL DEFAULT '',
|
jbe@115
|
60 "member_count" INT4,
|
jbe@115
|
61 "text_search_data" TSVECTOR );
|
jbe@115
|
62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
|
jbe@115
|
63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
|
jbe@115
|
64 CREATE INDEX "unit_active_idx" ON "unit" ("active");
|
jbe@115
|
65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
|
jbe@115
|
66 CREATE TRIGGER "update_text_search_data"
|
jbe@115
|
67 BEFORE INSERT OR UPDATE ON "unit"
|
jbe@115
|
68 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
69 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@115
|
70 "name", "description" );
|
jbe@115
|
71
|
jbe@115
|
72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
|
jbe@115
|
73
|
jbe@115
|
74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
|
jbe@115
|
75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
|
jbe@115
|
76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
|
jbe@115
|
77
|
jbe@115
|
78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1
|
jbe@115
|
79
|
jbe@115
|
80
|
jbe@115
|
81 -- New column "unit_id" in table "area":
|
jbe@115
|
82
|
jbe@115
|
83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
|
jbe@115
|
84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@115
|
85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
|
jbe@115
|
86
|
jbe@115
|
87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
|
jbe@115
|
88
|
jbe@115
|
89
|
jbe@115
|
90 -- Issue states:
|
jbe@115
|
91
|
jbe@115
|
92 CREATE TYPE "issue_state" AS ENUM (
|
jbe@115
|
93 'admission', 'discussion', 'verification', 'voting',
|
jbe@115
|
94 'canceled_revoked_before_accepted',
|
jbe@115
|
95 'canceled_issue_not_accepted',
|
jbe@115
|
96 'canceled_after_revocation_during_discussion',
|
jbe@115
|
97 'canceled_after_revocation_during_verification',
|
jbe@115
|
98 'calculation',
|
jbe@115
|
99 'canceled_no_initiative_admitted',
|
jbe@115
|
100 'finished_without_winner', 'finished_with_winner');
|
jbe@115
|
101
|
jbe@115
|
102 COMMENT ON TYPE "issue_state" IS 'State of issues';
|
jbe@115
|
103
|
jbe@115
|
104 ALTER TABLE "issue" ADD "state" "issue_state";
|
jbe@115
|
105
|
jbe@115
|
106 -- NOTE: Filling new column with values is done after this transaction (see below)
|
jbe@115
|
107
|
jbe@115
|
108
|
jbe@115
|
109 -- New column "revoked_by_member_id" in table "initiative":
|
jbe@115
|
110
|
jbe@115
|
111 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@115
|
112
|
jbe@115
|
113 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
|
jbe@115
|
114
|
jbe@115
|
115 -- NOTE: Filling new column with values is done after this transaction (see below)
|
jbe@115
|
116
|
jbe@115
|
117
|
jbe@115
|
118 -- New table "ignored_initiative":
|
jbe@115
|
119
|
jbe@115
|
120 CREATE TABLE "ignored_initiative" (
|
jbe@115
|
121 PRIMARY KEY ("initiative_id", "member_id"),
|
jbe@115
|
122 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
123 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@115
|
124 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
|
jbe@115
|
125
|
jbe@115
|
126 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
|
jbe@115
|
127
|
jbe@115
|
128
|
jbe@115
|
129 -- New table "invite_code_unit":
|
jbe@115
|
130
|
jbe@115
|
131 CREATE TABLE "invite_code_unit" (
|
jbe@115
|
132 PRIMARY KEY ("invite_code_id", "unit_id"),
|
jbe@115
|
133 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
134 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@115
|
135
|
jbe@115
|
136 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
|
jbe@115
|
137
|
jbe@117
|
138 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
|
jbe@117
|
139 SELECT "id" AS "invite_code_id", 1 AS "unit_id"
|
jbe@117
|
140 FROM "invite_code" WHERE "used" ISNULL;
|
jbe@115
|
141
|
jbe@115
|
142
|
jbe@115
|
143 -- New table "privilege":
|
jbe@115
|
144
|
jbe@115
|
145 CREATE TABLE "privilege" (
|
jbe@115
|
146 PRIMARY KEY ("unit_id", "member_id"),
|
jbe@115
|
147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@115
|
150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@115
|
151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@115
|
152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@115
|
153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
|
jbe@115
|
154
|
jbe@115
|
155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
|
jbe@115
|
156
|
jbe@115
|
157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
|
jbe@115
|
158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
|
jbe@115
|
159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
|
jbe@115
|
160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
|
jbe@115
|
161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
|
jbe@115
|
162
|
jbe@115
|
163
|
jbe@115
|
164 -- Remove table "ignored_issue", which is no longer existent:
|
jbe@115
|
165
|
jbe@115
|
166 DROP TABLE "ignored_issue";
|
jbe@115
|
167
|
jbe@115
|
168
|
jbe@115
|
169 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
|
jbe@115
|
170
|
jbe@115
|
171 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later
|
jbe@115
|
172 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
|
jbe@115
|
173 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
|
jbe@115
|
174
|
jbe@115
|
175
|
jbe@122
|
176 -- Delete views and functions being dependent on type "delegation_scope":
|
jbe@115
|
177
|
jbe@115
|
178 DROP FUNCTION "delegation_chain"
|
jbe@115
|
179 ( "member_id_p" "member"."id"%TYPE,
|
jbe@115
|
180 "area_id_p" "area"."id"%TYPE,
|
jbe@115
|
181 "issue_id_p" "issue"."id"%TYPE );
|
jbe@115
|
182
|
jbe@115
|
183 DROP FUNCTION "delegation_chain"
|
jbe@115
|
184 ( "member_id_p" "member"."id"%TYPE,
|
jbe@115
|
185 "area_id_p" "area"."id"%TYPE,
|
jbe@115
|
186 "issue_id_p" "issue"."id"%TYPE,
|
jbe@115
|
187 "simulate_trustee_id_p" "member"."id"%TYPE );
|
jbe@115
|
188
|
jbe@115
|
189 DROP TYPE "delegation_chain_row";
|
jbe@115
|
190
|
jbe@115
|
191 DROP VIEW "issue_delegation";
|
jbe@115
|
192 DROP VIEW "area_delegation";
|
jbe@115
|
193 DROP VIEW "global_delegation";
|
jbe@115
|
194 DROP VIEW "active_delegation";
|
jbe@115
|
195
|
jbe@115
|
196
|
jbe@122
|
197 -- Modify "delegation" table to use new "delegation_scope" type:
|
jbe@115
|
198
|
jbe@115
|
199 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
|
jbe@115
|
200 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
|
jbe@115
|
201
|
jbe@115
|
202 DROP INDEX "delegation_global_truster_id_unique_idx";
|
jbe@115
|
203
|
jbe@115
|
204 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
|
jbe@115
|
205 USING CASE WHEN "scope" = 'global'
|
jbe@115
|
206 THEN 'unit'::"delegation_scope"
|
jbe@115
|
207 ELSE "scope"::text::"delegation_scope" END;
|
jbe@115
|
208
|
jbe@115
|
209 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@115
|
210
|
jbe@115
|
211 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
|
jbe@115
|
212 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
|
jbe@115
|
213
|
jbe@115
|
214 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
|
jbe@115
|
215
|
jbe@115
|
216 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
|
jbe@115
|
217
|
jbe@115
|
218 -- NOTE: Column "unit_id" filled after transaction (see below)
|
jbe@115
|
219
|
jbe@115
|
220
|
jbe@122
|
221 -- Modify snapshot tables to use new "delegation_scope" type:
|
jbe@115
|
222
|
jbe@115
|
223 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
|
jbe@115
|
224 USING CASE WHEN "scope" = 'global'
|
jbe@115
|
225 THEN 'unit'::"delegation_scope"
|
jbe@115
|
226 ELSE "scope"::text::"delegation_scope" END;
|
jbe@115
|
227
|
jbe@115
|
228 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
|
jbe@115
|
229 USING CASE WHEN "scope" = 'global'
|
jbe@115
|
230 THEN 'unit'::"delegation_scope"
|
jbe@115
|
231 ELSE "scope"::text::"delegation_scope" END;
|
jbe@115
|
232
|
jbe@115
|
233 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
|
jbe@115
|
234 USING CASE WHEN "scope" = 'global'
|
jbe@115
|
235 THEN 'unit'::"delegation_scope"
|
jbe@115
|
236 ELSE "scope"::text::"delegation_scope" END;
|
jbe@115
|
237
|
jbe@115
|
238
|
jbe@115
|
239 -- New table "non_voter":
|
jbe@115
|
240
|
jbe@115
|
241 CREATE TABLE "non_voter" (
|
jbe@115
|
242 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@115
|
243 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
244 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@115
|
245 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
|
jbe@115
|
246
|
jbe@115
|
247 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
|
jbe@115
|
248
|
jbe@115
|
249
|
jbe@122
|
250 -- New tables "issue_comment" and "rendered_issue_comment":
|
jbe@115
|
251
|
jbe@115
|
252 CREATE TABLE "issue_comment" (
|
jbe@115
|
253 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@115
|
254 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
256 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@115
|
257 "formatting_engine" TEXT,
|
jbe@115
|
258 "content" TEXT NOT NULL,
|
jbe@115
|
259 "text_search_data" TSVECTOR );
|
jbe@115
|
260 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
|
jbe@115
|
261 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
|
jbe@115
|
262 CREATE TRIGGER "update_text_search_data"
|
jbe@115
|
263 BEFORE INSERT OR UPDATE ON "issue_comment"
|
jbe@115
|
264 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
265 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
|
jbe@115
|
266
|
jbe@115
|
267 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
|
jbe@115
|
268
|
jbe@115
|
269 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
|
jbe@115
|
270
|
jbe@115
|
271 CREATE TABLE "rendered_issue_comment" (
|
jbe@115
|
272 PRIMARY KEY ("issue_id", "member_id", "format"),
|
jbe@115
|
273 FOREIGN KEY ("issue_id", "member_id")
|
jbe@115
|
274 REFERENCES "issue_comment" ("issue_id", "member_id")
|
jbe@115
|
275 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
276 "issue_id" INT4,
|
jbe@115
|
277 "member_id" INT4,
|
jbe@115
|
278 "format" TEXT,
|
jbe@115
|
279 "content" TEXT NOT NULL );
|
jbe@115
|
280
|
jbe@115
|
281 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
|
jbe@115
|
282
|
jbe@115
|
283
|
jbe@122
|
284 -- New tables "voting_comment" and "rendered_voting_comment":
|
jbe@115
|
285
|
jbe@115
|
286 CREATE TABLE "voting_comment" (
|
jbe@115
|
287 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@115
|
288 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
289 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
290 "changed" TIMESTAMPTZ,
|
jbe@115
|
291 "formatting_engine" TEXT,
|
jbe@115
|
292 "content" TEXT NOT NULL,
|
jbe@115
|
293 "text_search_data" TSVECTOR );
|
jbe@115
|
294 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
|
jbe@115
|
295 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
|
jbe@115
|
296 CREATE TRIGGER "update_text_search_data"
|
jbe@115
|
297 BEFORE INSERT OR UPDATE ON "voting_comment"
|
jbe@115
|
298 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
299 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
|
jbe@115
|
300
|
jbe@115
|
301 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
|
jbe@115
|
302
|
jbe@115
|
303 COMMENT ON COLUMN "voting_comment"."changed" 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@115
|
304
|
jbe@115
|
305 CREATE TABLE "rendered_voting_comment" (
|
jbe@115
|
306 PRIMARY KEY ("issue_id", "member_id", "format"),
|
jbe@115
|
307 FOREIGN KEY ("issue_id", "member_id")
|
jbe@115
|
308 REFERENCES "voting_comment" ("issue_id", "member_id")
|
jbe@115
|
309 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
310 "issue_id" INT4,
|
jbe@115
|
311 "member_id" INT4,
|
jbe@115
|
312 "format" TEXT,
|
jbe@115
|
313 "content" TEXT NOT NULL );
|
jbe@115
|
314
|
jbe@115
|
315 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
|
jbe@115
|
316
|
jbe@115
|
317
|
jbe@115
|
318 -- New table "event":
|
jbe@115
|
319
|
jbe@115
|
320 CREATE TYPE "event_type" AS ENUM (
|
jbe@115
|
321 'issue_state_changed',
|
jbe@115
|
322 'initiative_created_in_new_issue',
|
jbe@115
|
323 'initiative_created_in_existing_issue',
|
jbe@115
|
324 'initiative_revoked',
|
jbe@115
|
325 'new_draft_created',
|
jbe@115
|
326 'suggestion_created');
|
jbe@115
|
327
|
jbe@115
|
328 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
|
jbe@115
|
329
|
jbe@115
|
330 CREATE TABLE "event" (
|
jbe@115
|
331 "id" SERIAL8 PRIMARY KEY,
|
jbe@115
|
332 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@115
|
333 "event" "event_type" NOT NULL,
|
jbe@115
|
334 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@115
|
335 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
336 "state" "issue_state" CHECK ("state" != 'calculation'),
|
jbe@115
|
337 "initiative_id" INT4,
|
jbe@115
|
338 "draft_id" INT8,
|
jbe@115
|
339 "suggestion_id" INT8,
|
jbe@115
|
340 FOREIGN KEY ("issue_id", "initiative_id")
|
jbe@115
|
341 REFERENCES "initiative" ("issue_id", "id")
|
jbe@115
|
342 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
343 FOREIGN KEY ("initiative_id", "draft_id")
|
jbe@115
|
344 REFERENCES "draft" ("initiative_id", "id")
|
jbe@115
|
345 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
346 FOREIGN KEY ("initiative_id", "suggestion_id")
|
jbe@115
|
347 REFERENCES "suggestion" ("initiative_id", "id")
|
jbe@115
|
348 ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@115
|
349 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
|
jbe@115
|
350 "event" != 'issue_state_changed' OR (
|
jbe@115
|
351 "member_id" ISNULL AND
|
jbe@115
|
352 "issue_id" NOTNULL AND
|
jbe@115
|
353 "state" NOTNULL AND
|
jbe@115
|
354 "initiative_id" ISNULL AND
|
jbe@115
|
355 "draft_id" ISNULL AND
|
jbe@115
|
356 "suggestion_id" ISNULL )),
|
jbe@115
|
357 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
|
jbe@115
|
358 "event" NOT IN (
|
jbe@115
|
359 'initiative_created_in_new_issue',
|
jbe@115
|
360 'initiative_created_in_existing_issue',
|
jbe@115
|
361 'initiative_revoked',
|
jbe@115
|
362 'new_draft_created'
|
jbe@115
|
363 ) OR (
|
jbe@115
|
364 "member_id" NOTNULL AND
|
jbe@115
|
365 "issue_id" NOTNULL AND
|
jbe@115
|
366 "state" NOTNULL AND
|
jbe@115
|
367 "initiative_id" NOTNULL AND
|
jbe@115
|
368 "draft_id" NOTNULL AND
|
jbe@115
|
369 "suggestion_id" ISNULL )),
|
jbe@115
|
370 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
|
jbe@115
|
371 "event" != 'suggestion_created' OR (
|
jbe@115
|
372 "member_id" NOTNULL AND
|
jbe@115
|
373 "issue_id" NOTNULL AND
|
jbe@115
|
374 "state" NOTNULL AND
|
jbe@115
|
375 "initiative_id" NOTNULL AND
|
jbe@115
|
376 "draft_id" ISNULL AND
|
jbe@115
|
377 "suggestion_id" NOTNULL )) );
|
jbe@115
|
378
|
jbe@115
|
379 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
|
jbe@115
|
380
|
jbe@115
|
381 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
|
jbe@115
|
382 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
|
jbe@115
|
383 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
|
jbe@115
|
384 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
|
jbe@115
|
385
|
jbe@115
|
386
|
jbe@115
|
387 -- Triggers to fill "event" table:
|
jbe@115
|
388
|
jbe@115
|
389 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
|
jbe@115
|
390 RETURNS TRIGGER
|
jbe@115
|
391 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
392 BEGIN
|
jbe@115
|
393 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
|
jbe@115
|
394 INSERT INTO "event" ("event", "issue_id", "state")
|
jbe@115
|
395 VALUES ('issue_state_changed', NEW."id", NEW."state");
|
jbe@115
|
396 END IF;
|
jbe@115
|
397 RETURN NULL;
|
jbe@115
|
398 END;
|
jbe@115
|
399 $$;
|
jbe@115
|
400
|
jbe@115
|
401 CREATE TRIGGER "write_event_issue_state_changed"
|
jbe@115
|
402 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
403 "write_event_issue_state_changed_trigger"();
|
jbe@115
|
404
|
jbe@115
|
405 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
|
jbe@115
|
406 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
|
jbe@115
|
407
|
jbe@115
|
408 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
|
jbe@115
|
409 RETURNS TRIGGER
|
jbe@115
|
410 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
411 DECLARE
|
jbe@115
|
412 "initiative_row" "initiative"%ROWTYPE;
|
jbe@115
|
413 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
414 "event_v" "event_type";
|
jbe@115
|
415 BEGIN
|
jbe@115
|
416 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@115
|
417 WHERE "id" = NEW."initiative_id";
|
jbe@115
|
418 SELECT * INTO "issue_row" FROM "issue"
|
jbe@115
|
419 WHERE "id" = "initiative_row"."issue_id";
|
jbe@115
|
420 IF EXISTS (
|
jbe@115
|
421 SELECT NULL FROM "draft"
|
jbe@115
|
422 WHERE "initiative_id" = NEW."initiative_id"
|
jbe@115
|
423 AND "id" != NEW."id"
|
jbe@115
|
424 ) THEN
|
jbe@115
|
425 "event_v" := 'new_draft_created';
|
jbe@115
|
426 ELSE
|
jbe@115
|
427 IF EXISTS (
|
jbe@115
|
428 SELECT NULL FROM "initiative"
|
jbe@115
|
429 WHERE "issue_id" = "initiative_row"."issue_id"
|
jbe@115
|
430 AND "id" != "initiative_row"."id"
|
jbe@115
|
431 ) THEN
|
jbe@115
|
432 "event_v" := 'initiative_created_in_existing_issue';
|
jbe@115
|
433 ELSE
|
jbe@115
|
434 "event_v" := 'initiative_created_in_new_issue';
|
jbe@115
|
435 END IF;
|
jbe@115
|
436 END IF;
|
jbe@115
|
437 INSERT INTO "event" (
|
jbe@115
|
438 "event", "member_id",
|
jbe@115
|
439 "issue_id", "state", "initiative_id", "draft_id"
|
jbe@115
|
440 ) VALUES (
|
jbe@115
|
441 "event_v",
|
jbe@115
|
442 NEW."author_id",
|
jbe@115
|
443 "initiative_row"."issue_id",
|
jbe@115
|
444 "issue_row"."state",
|
jbe@115
|
445 "initiative_row"."id",
|
jbe@115
|
446 NEW."id" );
|
jbe@115
|
447 RETURN NULL;
|
jbe@115
|
448 END;
|
jbe@115
|
449 $$;
|
jbe@115
|
450
|
jbe@115
|
451 CREATE TRIGGER "write_event_initiative_or_draft_created"
|
jbe@115
|
452 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
453 "write_event_initiative_or_draft_created_trigger"();
|
jbe@115
|
454
|
jbe@115
|
455 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
|
jbe@115
|
456 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
|
jbe@115
|
457
|
jbe@115
|
458 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
|
jbe@115
|
459 RETURNS TRIGGER
|
jbe@115
|
460 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
461 DECLARE
|
jbe@115
|
462 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
463 BEGIN
|
jbe@115
|
464 SELECT * INTO "issue_row" FROM "issue"
|
jbe@115
|
465 WHERE "id" = NEW."issue_id";
|
jbe@115
|
466 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
|
jbe@115
|
467 INSERT INTO "event" (
|
jbe@115
|
468 "event", "member_id", "issue_id", "state", "initiative_id"
|
jbe@115
|
469 ) VALUES (
|
jbe@115
|
470 'initiative_revoked',
|
jbe@115
|
471 NEW."revoked_by_member_id",
|
jbe@115
|
472 NEW."issue_id",
|
jbe@115
|
473 "issue_row"."state",
|
jbe@115
|
474 NEW."id" );
|
jbe@115
|
475 END IF;
|
jbe@115
|
476 RETURN NULL;
|
jbe@115
|
477 END;
|
jbe@115
|
478 $$;
|
jbe@115
|
479
|
jbe@115
|
480 CREATE TRIGGER "write_event_initiative_revoked"
|
jbe@115
|
481 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
482 "write_event_initiative_revoked_trigger"();
|
jbe@115
|
483
|
jbe@115
|
484 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
|
jbe@115
|
485 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
|
jbe@115
|
486
|
jbe@115
|
487 CREATE FUNCTION "write_event_suggestion_created_trigger"()
|
jbe@115
|
488 RETURNS TRIGGER
|
jbe@115
|
489 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
490 DECLARE
|
jbe@115
|
491 "initiative_row" "initiative"%ROWTYPE;
|
jbe@115
|
492 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
493 BEGIN
|
jbe@115
|
494 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@115
|
495 WHERE "id" = NEW."initiative_id";
|
jbe@115
|
496 SELECT * INTO "issue_row" FROM "issue"
|
jbe@115
|
497 WHERE "id" = "initiative_row"."issue_id";
|
jbe@115
|
498 INSERT INTO "event" (
|
jbe@115
|
499 "event", "member_id",
|
jbe@115
|
500 "issue_id", "state", "initiative_id", "suggestion_id"
|
jbe@115
|
501 ) VALUES (
|
jbe@115
|
502 'suggestion_created',
|
jbe@115
|
503 NEW."author_id",
|
jbe@115
|
504 "initiative_row"."issue_id",
|
jbe@115
|
505 "issue_row"."state",
|
jbe@115
|
506 "initiative_row"."id",
|
jbe@115
|
507 NEW."id" );
|
jbe@115
|
508 RETURN NULL;
|
jbe@115
|
509 END;
|
jbe@115
|
510 $$;
|
jbe@115
|
511
|
jbe@115
|
512 CREATE TRIGGER "write_event_suggestion_created"
|
jbe@115
|
513 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@115
|
514 "write_event_suggestion_created_trigger"();
|
jbe@115
|
515
|
jbe@115
|
516 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
|
jbe@115
|
517 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
|
jbe@115
|
518
|
jbe@115
|
519
|
jbe@115
|
520 -- Modified views:
|
jbe@115
|
521
|
jbe@115
|
522 CREATE VIEW "unit_delegation" AS
|
jbe@115
|
523 SELECT
|
jbe@115
|
524 "unit"."id" AS "unit_id",
|
jbe@115
|
525 "delegation"."id",
|
jbe@115
|
526 "delegation"."truster_id",
|
jbe@115
|
527 "delegation"."trustee_id",
|
jbe@115
|
528 "delegation"."scope"
|
jbe@115
|
529 FROM "unit"
|
jbe@115
|
530 JOIN "delegation"
|
jbe@115
|
531 ON "delegation"."unit_id" = "unit"."id"
|
jbe@115
|
532 JOIN "member"
|
jbe@115
|
533 ON "delegation"."truster_id" = "member"."id"
|
jbe@115
|
534 JOIN "privilege"
|
jbe@115
|
535 ON "delegation"."unit_id" = "privilege"."unit_id"
|
jbe@115
|
536 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@115
|
537 WHERE "member"."active" AND "privilege"."voting_right";
|
jbe@115
|
538
|
jbe@115
|
539 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
|
jbe@115
|
540
|
jbe@115
|
541 CREATE VIEW "area_delegation" AS
|
jbe@115
|
542 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
|
jbe@115
|
543 "area"."id" AS "area_id",
|
jbe@115
|
544 "delegation"."id",
|
jbe@115
|
545 "delegation"."truster_id",
|
jbe@115
|
546 "delegation"."trustee_id",
|
jbe@115
|
547 "delegation"."scope"
|
jbe@115
|
548 FROM "area"
|
jbe@115
|
549 JOIN "delegation"
|
jbe@115
|
550 ON "delegation"."unit_id" = "area"."unit_id"
|
jbe@115
|
551 OR "delegation"."area_id" = "area"."id"
|
jbe@115
|
552 JOIN "member"
|
jbe@115
|
553 ON "delegation"."truster_id" = "member"."id"
|
jbe@115
|
554 JOIN "privilege"
|
jbe@115
|
555 ON "area"."unit_id" = "privilege"."unit_id"
|
jbe@115
|
556 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@115
|
557 WHERE "member"."active" AND "privilege"."voting_right"
|
jbe@115
|
558 ORDER BY
|
jbe@115
|
559 "area"."id",
|
jbe@115
|
560 "delegation"."truster_id",
|
jbe@115
|
561 "delegation"."scope" DESC;
|
jbe@115
|
562
|
jbe@115
|
563 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
|
jbe@115
|
564
|
jbe@115
|
565 CREATE VIEW "issue_delegation" AS
|
jbe@115
|
566 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
jbe@115
|
567 "issue"."id" AS "issue_id",
|
jbe@115
|
568 "delegation"."id",
|
jbe@115
|
569 "delegation"."truster_id",
|
jbe@115
|
570 "delegation"."trustee_id",
|
jbe@115
|
571 "delegation"."scope"
|
jbe@115
|
572 FROM "issue"
|
jbe@115
|
573 JOIN "area"
|
jbe@115
|
574 ON "area"."id" = "issue"."area_id"
|
jbe@115
|
575 JOIN "delegation"
|
jbe@115
|
576 ON "delegation"."unit_id" = "area"."unit_id"
|
jbe@115
|
577 OR "delegation"."area_id" = "area"."id"
|
jbe@115
|
578 OR "delegation"."issue_id" = "issue"."id"
|
jbe@115
|
579 JOIN "member"
|
jbe@115
|
580 ON "delegation"."truster_id" = "member"."id"
|
jbe@115
|
581 JOIN "privilege"
|
jbe@115
|
582 ON "area"."unit_id" = "privilege"."unit_id"
|
jbe@115
|
583 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@115
|
584 WHERE "member"."active" AND "privilege"."voting_right"
|
jbe@115
|
585 ORDER BY
|
jbe@115
|
586 "issue"."id",
|
jbe@115
|
587 "delegation"."truster_id",
|
jbe@115
|
588 "delegation"."scope" DESC;
|
jbe@115
|
589
|
jbe@115
|
590 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
|
jbe@115
|
591
|
jbe@115
|
592 CREATE VIEW "unit_member_count" AS
|
jbe@115
|
593 SELECT
|
jbe@115
|
594 "unit"."id" AS "unit_id",
|
jbe@115
|
595 sum("member"."id") AS "member_count"
|
jbe@115
|
596 FROM "unit"
|
jbe@115
|
597 LEFT JOIN "privilege"
|
jbe@115
|
598 ON "privilege"."unit_id" = "unit"."id"
|
jbe@115
|
599 AND "privilege"."voting_right"
|
jbe@115
|
600 LEFT JOIN "member"
|
jbe@115
|
601 ON "member"."id" = "privilege"."member_id"
|
jbe@115
|
602 AND "member"."active"
|
jbe@115
|
603 GROUP BY "unit"."id";
|
jbe@115
|
604
|
jbe@115
|
605 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
|
jbe@115
|
606
|
jbe@115
|
607 DROP VIEW "area_member_count";
|
jbe@115
|
608 CREATE VIEW "area_member_count" AS
|
jbe@115
|
609 SELECT
|
jbe@115
|
610 "area"."id" AS "area_id",
|
jbe@115
|
611 count("member"."id") AS "direct_member_count",
|
jbe@115
|
612 coalesce(
|
jbe@115
|
613 sum(
|
jbe@115
|
614 CASE WHEN "member"."id" NOTNULL THEN
|
jbe@115
|
615 "membership_weight"("area"."id", "member"."id")
|
jbe@115
|
616 ELSE 0 END
|
jbe@115
|
617 )
|
jbe@115
|
618 ) AS "member_weight",
|
jbe@115
|
619 coalesce(
|
jbe@115
|
620 sum(
|
jbe@115
|
621 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
|
jbe@115
|
622 "membership_weight"("area"."id", "member"."id")
|
jbe@115
|
623 ELSE 0 END
|
jbe@115
|
624 )
|
jbe@115
|
625 ) AS "autoreject_weight"
|
jbe@115
|
626 FROM "area"
|
jbe@115
|
627 LEFT JOIN "membership"
|
jbe@115
|
628 ON "area"."id" = "membership"."area_id"
|
jbe@115
|
629 LEFT JOIN "privilege"
|
jbe@115
|
630 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@115
|
631 AND "privilege"."member_id" = "membership"."member_id"
|
jbe@115
|
632 AND "privilege"."voting_right"
|
jbe@115
|
633 LEFT JOIN "member"
|
jbe@115
|
634 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
|
jbe@115
|
635 AND "member"."active"
|
jbe@115
|
636 GROUP BY "area"."id";
|
jbe@115
|
637
|
jbe@115
|
638 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
|
jbe@115
|
639
|
jbe@115
|
640
|
jbe@115
|
641 -- New view "event_seen_by_member":
|
jbe@115
|
642
|
jbe@115
|
643 CREATE VIEW "event_seen_by_member" AS
|
jbe@115
|
644 SELECT
|
jbe@115
|
645 "member"."id" AS "seen_by_member_id",
|
jbe@115
|
646 CASE WHEN "event"."state" IN (
|
jbe@115
|
647 'voting',
|
jbe@115
|
648 'finished_without_winner',
|
jbe@115
|
649 'finished_with_winner'
|
jbe@115
|
650 ) THEN
|
jbe@115
|
651 'voting'::"notify_level"
|
jbe@115
|
652 ELSE
|
jbe@115
|
653 CASE WHEN "event"."state" IN (
|
jbe@115
|
654 'verification',
|
jbe@115
|
655 'canceled_after_revocation_during_verification',
|
jbe@115
|
656 'canceled_no_initiative_admitted'
|
jbe@115
|
657 ) THEN
|
jbe@115
|
658 'verification'::"notify_level"
|
jbe@115
|
659 ELSE
|
jbe@115
|
660 CASE WHEN "event"."state" IN (
|
jbe@115
|
661 'discussion',
|
jbe@115
|
662 'canceled_after_revocation_during_discussion'
|
jbe@115
|
663 ) THEN
|
jbe@115
|
664 'discussion'::"notify_level"
|
jbe@115
|
665 ELSE
|
jbe@115
|
666 'all'::"notify_level"
|
jbe@115
|
667 END
|
jbe@115
|
668 END
|
jbe@115
|
669 END AS "notify_level",
|
jbe@115
|
670 "event".*
|
jbe@115
|
671 FROM "member" CROSS JOIN "event"
|
jbe@115
|
672 LEFT JOIN "issue"
|
jbe@115
|
673 ON "event"."issue_id" = "issue"."id"
|
jbe@115
|
674 LEFT JOIN "membership"
|
jbe@115
|
675 ON "member"."id" = "membership"."member_id"
|
jbe@115
|
676 AND "issue"."area_id" = "membership"."area_id"
|
jbe@115
|
677 LEFT JOIN "interest"
|
jbe@115
|
678 ON "member"."id" = "interest"."member_id"
|
jbe@115
|
679 AND "event"."issue_id" = "interest"."issue_id"
|
jbe@115
|
680 LEFT JOIN "supporter"
|
jbe@115
|
681 ON "member"."id" = "supporter"."member_id"
|
jbe@115
|
682 AND "event"."initiative_id" = "supporter"."initiative_id"
|
jbe@115
|
683 LEFT JOIN "ignored_member"
|
jbe@115
|
684 ON "member"."id" = "ignored_member"."member_id"
|
jbe@115
|
685 AND "event"."member_id" = "ignored_member"."other_member_id"
|
jbe@115
|
686 LEFT JOIN "ignored_initiative"
|
jbe@115
|
687 ON "member"."id" = "ignored_initiative"."member_id"
|
jbe@115
|
688 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
jbe@115
|
689 WHERE (
|
jbe@115
|
690 "supporter"."member_id" NOTNULL OR
|
jbe@115
|
691 "interest"."member_id" NOTNULL OR
|
jbe@115
|
692 ( "membership"."member_id" NOTNULL AND
|
jbe@115
|
693 "event"."event" IN (
|
jbe@115
|
694 'issue_state_changed',
|
jbe@115
|
695 'initiative_created_in_new_issue',
|
jbe@115
|
696 'initiative_created_in_existing_issue',
|
jbe@115
|
697 'initiative_revoked' ) ) )
|
jbe@115
|
698 AND "ignored_member"."member_id" ISNULL
|
jbe@115
|
699 AND "ignored_initiative"."member_id" ISNULL;
|
jbe@115
|
700
|
jbe@115
|
701 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
|
jbe@115
|
702
|
jbe@115
|
703
|
jbe@115
|
704 -- New view "pending_notification":
|
jbe@115
|
705
|
jbe@115
|
706 CREATE VIEW "pending_notification" AS
|
jbe@115
|
707 SELECT
|
jbe@115
|
708 "member"."id" AS "seen_by_member_id",
|
jbe@115
|
709 "event".*
|
jbe@115
|
710 FROM "member" CROSS JOIN "event"
|
jbe@115
|
711 LEFT JOIN "issue"
|
jbe@115
|
712 ON "event"."issue_id" = "issue"."id"
|
jbe@115
|
713 LEFT JOIN "membership"
|
jbe@115
|
714 ON "member"."id" = "membership"."member_id"
|
jbe@115
|
715 AND "issue"."area_id" = "membership"."area_id"
|
jbe@115
|
716 LEFT JOIN "interest"
|
jbe@115
|
717 ON "member"."id" = "interest"."member_id"
|
jbe@115
|
718 AND "event"."issue_id" = "interest"."issue_id"
|
jbe@115
|
719 LEFT JOIN "supporter"
|
jbe@115
|
720 ON "member"."id" = "supporter"."member_id"
|
jbe@115
|
721 AND "event"."initiative_id" = "supporter"."initiative_id"
|
jbe@115
|
722 LEFT JOIN "ignored_member"
|
jbe@115
|
723 ON "member"."id" = "ignored_member"."member_id"
|
jbe@115
|
724 AND "event"."member_id" = "ignored_member"."other_member_id"
|
jbe@115
|
725 LEFT JOIN "ignored_initiative"
|
jbe@115
|
726 ON "member"."id" = "ignored_initiative"."member_id"
|
jbe@115
|
727 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
jbe@115
|
728 WHERE (
|
jbe@115
|
729 "member"."notify_event_id" ISNULL OR
|
jbe@115
|
730 ( "member"."notify_event_id" NOTNULL AND
|
jbe@115
|
731 "member"."notify_event_id" < "event"."id" ) )
|
jbe@115
|
732 AND (
|
jbe@115
|
733 ( "member"."notify_level" >= 'all' ) OR
|
jbe@115
|
734 ( "member"."notify_level" >= 'voting' AND
|
jbe@115
|
735 "event"."state" IN (
|
jbe@115
|
736 'voting',
|
jbe@115
|
737 'finished_without_winner',
|
jbe@115
|
738 'finished_with_winner' ) ) OR
|
jbe@115
|
739 ( "member"."notify_level" >= 'verification' AND
|
jbe@115
|
740 "event"."state" IN (
|
jbe@115
|
741 'verification',
|
jbe@115
|
742 'canceled_after_revocation_during_verification',
|
jbe@115
|
743 'canceled_no_initiative_admitted' ) ) OR
|
jbe@115
|
744 ( "member"."notify_level" >= 'discussion' AND
|
jbe@115
|
745 "event"."state" IN (
|
jbe@115
|
746 'discussion',
|
jbe@115
|
747 'canceled_after_revocation_during_discussion' ) ) )
|
jbe@115
|
748 AND (
|
jbe@115
|
749 "supporter"."member_id" NOTNULL OR
|
jbe@115
|
750 "interest"."member_id" NOTNULL OR
|
jbe@115
|
751 ( "membership"."member_id" NOTNULL AND
|
jbe@115
|
752 "event"."event" IN (
|
jbe@115
|
753 'issue_state_changed',
|
jbe@115
|
754 'initiative_created_in_new_issue',
|
jbe@115
|
755 'initiative_created_in_existing_issue',
|
jbe@115
|
756 'initiative_revoked' ) ) )
|
jbe@115
|
757 AND "ignored_member"."member_id" ISNULL
|
jbe@115
|
758 AND "ignored_initiative"."member_id" ISNULL;
|
jbe@115
|
759
|
jbe@115
|
760 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
|
jbe@115
|
761
|
jbe@115
|
762
|
jbe@115
|
763 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
|
jbe@115
|
764 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
|
jbe@115
|
765 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
|
jbe@115
|
766 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
|
jbe@115
|
767 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
|
jbe@115
|
768 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
|
jbe@115
|
769
|
jbe@115
|
770
|
jbe@115
|
771 -- Modified "delegation_chain" functions:
|
jbe@115
|
772
|
jbe@115
|
773 CREATE TYPE "delegation_chain_row" AS (
|
jbe@115
|
774 "index" INT4,
|
jbe@115
|
775 "member_id" INT4,
|
jbe@115
|
776 "member_valid" BOOLEAN,
|
jbe@115
|
777 "participation" BOOLEAN,
|
jbe@115
|
778 "overridden" BOOLEAN,
|
jbe@115
|
779 "scope_in" "delegation_scope",
|
jbe@115
|
780 "scope_out" "delegation_scope",
|
jbe@115
|
781 "disabled_out" BOOLEAN,
|
jbe@115
|
782 "loop" "delegation_chain_loop_tag" );
|
jbe@115
|
783
|
jbe@115
|
784 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
|
jbe@115
|
785
|
jbe@115
|
786 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
|
jbe@115
|
787 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
|
jbe@115
|
788 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
|
jbe@115
|
789 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
|
jbe@115
|
790 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
|
jbe@115
|
791 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
|
jbe@115
|
792 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
|
jbe@115
|
793
|
jbe@115
|
794
|
jbe@115
|
795 CREATE FUNCTION "delegation_chain"
|
jbe@115
|
796 ( "member_id_p" "member"."id"%TYPE,
|
jbe@115
|
797 "unit_id_p" "unit"."id"%TYPE,
|
jbe@115
|
798 "area_id_p" "area"."id"%TYPE,
|
jbe@115
|
799 "issue_id_p" "issue"."id"%TYPE,
|
jbe@115
|
800 "simulate_trustee_id_p" "member"."id"%TYPE )
|
jbe@115
|
801 RETURNS SETOF "delegation_chain_row"
|
jbe@115
|
802 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@115
|
803 DECLARE
|
jbe@115
|
804 "scope_v" "delegation_scope";
|
jbe@115
|
805 "unit_id_v" "unit"."id"%TYPE;
|
jbe@115
|
806 "area_id_v" "area"."id"%TYPE;
|
jbe@115
|
807 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@115
|
808 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@115
|
809 "output_row" "delegation_chain_row";
|
jbe@115
|
810 "output_rows" "delegation_chain_row"[];
|
jbe@115
|
811 "delegation_row" "delegation"%ROWTYPE;
|
jbe@115
|
812 "row_count" INT4;
|
jbe@115
|
813 "i" INT4;
|
jbe@115
|
814 "loop_v" BOOLEAN;
|
jbe@115
|
815 BEGIN
|
jbe@115
|
816 IF
|
jbe@115
|
817 "unit_id_p" NOTNULL AND
|
jbe@115
|
818 "area_id_p" ISNULL AND
|
jbe@115
|
819 "issue_id_p" ISNULL
|
jbe@115
|
820 THEN
|
jbe@115
|
821 "scope_v" := 'unit';
|
jbe@115
|
822 "unit_id_v" := "unit_id_p";
|
jbe@115
|
823 ELSIF
|
jbe@115
|
824 "unit_id_p" ISNULL AND
|
jbe@115
|
825 "area_id_p" NOTNULL AND
|
jbe@115
|
826 "issue_id_p" ISNULL
|
jbe@115
|
827 THEN
|
jbe@115
|
828 "scope_v" := 'area';
|
jbe@115
|
829 "area_id_v" := "area_id_p";
|
jbe@115
|
830 SELECT "unit_id" INTO "unit_id_v"
|
jbe@115
|
831 FROM "area" WHERE "id" = "area_id_v";
|
jbe@115
|
832 ELSIF
|
jbe@115
|
833 "unit_id_p" ISNULL AND
|
jbe@115
|
834 "area_id_p" ISNULL AND
|
jbe@115
|
835 "issue_id_p" NOTNULL
|
jbe@115
|
836 THEN
|
jbe@115
|
837 "scope_v" := 'issue';
|
jbe@115
|
838 SELECT "area_id" INTO "area_id_v"
|
jbe@115
|
839 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
840 SELECT "unit_id" INTO "unit_id_v"
|
jbe@115
|
841 FROM "area" WHERE "id" = "area_id_v";
|
jbe@115
|
842 ELSE
|
jbe@115
|
843 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
|
jbe@115
|
844 END IF;
|
jbe@115
|
845 "visited_member_ids" := '{}';
|
jbe@115
|
846 "loop_member_id_v" := NULL;
|
jbe@115
|
847 "output_rows" := '{}';
|
jbe@115
|
848 "output_row"."index" := 0;
|
jbe@115
|
849 "output_row"."member_id" := "member_id_p";
|
jbe@115
|
850 "output_row"."member_valid" := TRUE;
|
jbe@115
|
851 "output_row"."participation" := FALSE;
|
jbe@115
|
852 "output_row"."overridden" := FALSE;
|
jbe@115
|
853 "output_row"."disabled_out" := FALSE;
|
jbe@115
|
854 "output_row"."scope_out" := NULL;
|
jbe@115
|
855 LOOP
|
jbe@115
|
856 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@115
|
857 "loop_member_id_v" := "output_row"."member_id";
|
jbe@115
|
858 ELSE
|
jbe@115
|
859 "visited_member_ids" :=
|
jbe@115
|
860 "visited_member_ids" || "output_row"."member_id";
|
jbe@115
|
861 END IF;
|
jbe@115
|
862 IF "output_row"."participation" THEN
|
jbe@115
|
863 "output_row"."overridden" := TRUE;
|
jbe@115
|
864 END IF;
|
jbe@115
|
865 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@115
|
866 IF EXISTS (
|
jbe@115
|
867 SELECT NULL FROM "member" JOIN "privilege"
|
jbe@115
|
868 ON "privilege"."member_id" = "member"."id"
|
jbe@115
|
869 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@115
|
870 WHERE "id" = "output_row"."member_id"
|
jbe@115
|
871 AND "member"."active" AND "privilege"."voting_right"
|
jbe@115
|
872 ) THEN
|
jbe@115
|
873 IF "scope_v" = 'unit' THEN
|
jbe@115
|
874 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@115
|
875 WHERE "truster_id" = "output_row"."member_id"
|
jbe@115
|
876 AND "unit_id" = "unit_id_v";
|
jbe@115
|
877 ELSIF "scope_v" = 'area' THEN
|
jbe@115
|
878 "output_row"."participation" := EXISTS (
|
jbe@115
|
879 SELECT NULL FROM "membership"
|
jbe@115
|
880 WHERE "area_id" = "area_id_p"
|
jbe@115
|
881 AND "member_id" = "output_row"."member_id"
|
jbe@115
|
882 );
|
jbe@115
|
883 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@115
|
884 WHERE "truster_id" = "output_row"."member_id"
|
jbe@115
|
885 AND (
|
jbe@115
|
886 "unit_id" = "unit_id_v" OR
|
jbe@115
|
887 "area_id" = "area_id_v"
|
jbe@115
|
888 )
|
jbe@115
|
889 ORDER BY "scope" DESC;
|
jbe@115
|
890 ELSIF "scope_v" = 'issue' THEN
|
jbe@115
|
891 "output_row"."participation" := EXISTS (
|
jbe@115
|
892 SELECT NULL FROM "interest"
|
jbe@115
|
893 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
894 AND "member_id" = "output_row"."member_id"
|
jbe@115
|
895 );
|
jbe@115
|
896 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@115
|
897 WHERE "truster_id" = "output_row"."member_id"
|
jbe@115
|
898 AND (
|
jbe@115
|
899 "unit_id" = "unit_id_v" OR
|
jbe@115
|
900 "area_id" = "area_id_v" OR
|
jbe@115
|
901 "issue_id" = "issue_id_p"
|
jbe@115
|
902 )
|
jbe@115
|
903 ORDER BY "scope" DESC;
|
jbe@115
|
904 END IF;
|
jbe@115
|
905 ELSE
|
jbe@115
|
906 "output_row"."member_valid" := FALSE;
|
jbe@115
|
907 "output_row"."participation" := FALSE;
|
jbe@115
|
908 "output_row"."scope_out" := NULL;
|
jbe@115
|
909 "delegation_row" := ROW(NULL);
|
jbe@115
|
910 END IF;
|
jbe@115
|
911 IF
|
jbe@115
|
912 "output_row"."member_id" = "member_id_p" AND
|
jbe@115
|
913 "simulate_trustee_id_p" NOTNULL
|
jbe@115
|
914 THEN
|
jbe@115
|
915 "output_row"."scope_out" := "scope_v";
|
jbe@115
|
916 "output_rows" := "output_rows" || "output_row";
|
jbe@115
|
917 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@115
|
918 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@115
|
919 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@115
|
920 "output_rows" := "output_rows" || "output_row";
|
jbe@115
|
921 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@115
|
922 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@115
|
923 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@115
|
924 "output_row"."disabled_out" := TRUE;
|
jbe@115
|
925 "output_rows" := "output_rows" || "output_row";
|
jbe@115
|
926 EXIT;
|
jbe@115
|
927 ELSE
|
jbe@115
|
928 "output_row"."scope_out" := NULL;
|
jbe@115
|
929 "output_rows" := "output_rows" || "output_row";
|
jbe@115
|
930 EXIT;
|
jbe@115
|
931 END IF;
|
jbe@115
|
932 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@115
|
933 "output_row"."index" := "output_row"."index" + 1;
|
jbe@115
|
934 END LOOP;
|
jbe@115
|
935 "row_count" := array_upper("output_rows", 1);
|
jbe@115
|
936 "i" := 1;
|
jbe@115
|
937 "loop_v" := FALSE;
|
jbe@115
|
938 LOOP
|
jbe@115
|
939 "output_row" := "output_rows"["i"];
|
jbe@115
|
940 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
|
jbe@115
|
941 IF "loop_v" THEN
|
jbe@115
|
942 IF "i" + 1 = "row_count" THEN
|
jbe@115
|
943 "output_row"."loop" := 'last';
|
jbe@115
|
944 ELSIF "i" = "row_count" THEN
|
jbe@115
|
945 "output_row"."loop" := 'repetition';
|
jbe@115
|
946 ELSE
|
jbe@115
|
947 "output_row"."loop" := 'intermediate';
|
jbe@115
|
948 END IF;
|
jbe@115
|
949 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@115
|
950 "output_row"."loop" := 'first';
|
jbe@115
|
951 "loop_v" := TRUE;
|
jbe@115
|
952 END IF;
|
jbe@115
|
953 IF "scope_v" = 'unit' THEN
|
jbe@115
|
954 "output_row"."participation" := NULL;
|
jbe@115
|
955 END IF;
|
jbe@115
|
956 RETURN NEXT "output_row";
|
jbe@115
|
957 "i" := "i" + 1;
|
jbe@115
|
958 END LOOP;
|
jbe@115
|
959 RETURN;
|
jbe@115
|
960 END;
|
jbe@115
|
961 $$;
|
jbe@115
|
962
|
jbe@115
|
963 COMMENT ON FUNCTION "delegation_chain"
|
jbe@115
|
964 ( "member"."id"%TYPE,
|
jbe@115
|
965 "unit"."id"%TYPE,
|
jbe@115
|
966 "area"."id"%TYPE,
|
jbe@115
|
967 "issue"."id"%TYPE,
|
jbe@115
|
968 "member"."id"%TYPE )
|
jbe@115
|
969 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
|
jbe@115
|
970
|
jbe@115
|
971
|
jbe@115
|
972 CREATE FUNCTION "delegation_chain"
|
jbe@115
|
973 ( "member_id_p" "member"."id"%TYPE,
|
jbe@115
|
974 "unit_id_p" "unit"."id"%TYPE,
|
jbe@115
|
975 "area_id_p" "area"."id"%TYPE,
|
jbe@115
|
976 "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
977 RETURNS SETOF "delegation_chain_row"
|
jbe@115
|
978 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@115
|
979 DECLARE
|
jbe@115
|
980 "result_row" "delegation_chain_row";
|
jbe@115
|
981 BEGIN
|
jbe@115
|
982 FOR "result_row" IN
|
jbe@115
|
983 SELECT * FROM "delegation_chain"(
|
jbe@115
|
984 "member_id_p", "area_id_p", "issue_id_p", NULL
|
jbe@115
|
985 )
|
jbe@115
|
986 LOOP
|
jbe@115
|
987 RETURN NEXT "result_row";
|
jbe@115
|
988 END LOOP;
|
jbe@115
|
989 RETURN;
|
jbe@115
|
990 END;
|
jbe@115
|
991 $$;
|
jbe@115
|
992
|
jbe@115
|
993 COMMENT ON FUNCTION "delegation_chain"
|
jbe@115
|
994 ( "member"."id"%TYPE,
|
jbe@115
|
995 "unit"."id"%TYPE,
|
jbe@115
|
996 "area"."id"%TYPE,
|
jbe@115
|
997 "issue"."id"%TYPE )
|
jbe@115
|
998 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
|
jbe@115
|
999
|
jbe@115
|
1000
|
jbe@122
|
1001 -- Other modified functions:
|
jbe@115
|
1002
|
jbe@115
|
1003 CREATE OR REPLACE FUNCTION "lock_issue"
|
jbe@115
|
1004 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1005 RETURNS VOID
|
jbe@115
|
1006 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1007 BEGIN
|
jbe@115
|
1008 LOCK TABLE "member" IN SHARE MODE;
|
jbe@115
|
1009 LOCK TABLE "privilege" IN SHARE MODE;
|
jbe@115
|
1010 LOCK TABLE "membership" IN SHARE MODE;
|
jbe@115
|
1011 LOCK TABLE "policy" IN SHARE MODE;
|
jbe@115
|
1012 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
jbe@115
|
1013 -- NOTE: The row-level exclusive lock in combination with the
|
jbe@115
|
1014 -- share_row_lock_issue(_via_initiative)_trigger functions (which
|
jbe@115
|
1015 -- acquire a row-level share lock on the issue) ensure that no data
|
jbe@115
|
1016 -- is changed, which could affect calculation of snapshots or
|
jbe@115
|
1017 -- counting of votes. Table "delegation" must be table-level-locked,
|
jbe@115
|
1018 -- as it also contains issue- and global-scope delegations.
|
jbe@115
|
1019 LOCK TABLE "delegation" IN SHARE MODE;
|
jbe@115
|
1020 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@115
|
1021 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@115
|
1022 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@115
|
1023 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@115
|
1024 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
jbe@115
|
1025 RETURN;
|
jbe@115
|
1026 END;
|
jbe@115
|
1027 $$;
|
jbe@115
|
1028
|
jbe@115
|
1029 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
|
jbe@115
|
1030 RETURNS VOID
|
jbe@115
|
1031 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1032 BEGIN
|
jbe@115
|
1033 LOCK TABLE "member" IN SHARE MODE;
|
jbe@115
|
1034 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
|
jbe@115
|
1035 LOCK TABLE "unit" IN EXCLUSIVE MODE;
|
jbe@115
|
1036 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
jbe@115
|
1037 LOCK TABLE "privilege" IN SHARE MODE;
|
jbe@115
|
1038 LOCK TABLE "membership" IN SHARE MODE;
|
jbe@115
|
1039 DELETE FROM "member_count";
|
jbe@115
|
1040 INSERT INTO "member_count" ("total_count")
|
jbe@115
|
1041 SELECT "total_count" FROM "member_count_view";
|
jbe@115
|
1042 UPDATE "unit" SET "member_count" = "view"."member_count"
|
jbe@115
|
1043 FROM "unit_member_count" AS "view"
|
jbe@115
|
1044 WHERE "view"."unit_id" = "unit"."id";
|
jbe@115
|
1045 UPDATE "area" SET
|
jbe@115
|
1046 "direct_member_count" = "view"."direct_member_count",
|
jbe@115
|
1047 "member_weight" = "view"."member_weight",
|
jbe@115
|
1048 "autoreject_weight" = "view"."autoreject_weight"
|
jbe@115
|
1049 FROM "area_member_count" AS "view"
|
jbe@115
|
1050 WHERE "view"."area_id" = "area"."id";
|
jbe@115
|
1051 RETURN;
|
jbe@115
|
1052 END;
|
jbe@115
|
1053 $$;
|
jbe@115
|
1054
|
jbe@115
|
1055 CREATE OR REPLACE FUNCTION "create_population_snapshot"
|
jbe@115
|
1056 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1057 RETURNS VOID
|
jbe@115
|
1058 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1059 DECLARE
|
jbe@115
|
1060 "member_id_v" "member"."id"%TYPE;
|
jbe@115
|
1061 BEGIN
|
jbe@115
|
1062 DELETE FROM "direct_population_snapshot"
|
jbe@115
|
1063 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1064 AND "event" = 'periodic';
|
jbe@115
|
1065 DELETE FROM "delegating_population_snapshot"
|
jbe@115
|
1066 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1067 AND "event" = 'periodic';
|
jbe@115
|
1068 INSERT INTO "direct_population_snapshot"
|
jbe@115
|
1069 ("issue_id", "event", "member_id")
|
jbe@115
|
1070 SELECT
|
jbe@115
|
1071 "issue_id_p" AS "issue_id",
|
jbe@115
|
1072 'periodic'::"snapshot_event" AS "event",
|
jbe@115
|
1073 "member"."id" AS "member_id"
|
jbe@115
|
1074 FROM "issue"
|
jbe@115
|
1075 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@115
|
1076 JOIN "membership" ON "area"."id" = "membership"."area_id"
|
jbe@115
|
1077 JOIN "member" ON "membership"."member_id" = "member"."id"
|
jbe@115
|
1078 JOIN "privilege"
|
jbe@115
|
1079 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@115
|
1080 AND "privilege"."member_id" = "member"."id"
|
jbe@115
|
1081 WHERE "issue"."id" = "issue_id_p"
|
jbe@115
|
1082 AND "member"."active" AND "privilege"."voting_right"
|
jbe@115
|
1083 UNION
|
jbe@115
|
1084 SELECT
|
jbe@115
|
1085 "issue_id_p" AS "issue_id",
|
jbe@115
|
1086 'periodic'::"snapshot_event" AS "event",
|
jbe@115
|
1087 "member"."id" AS "member_id"
|
jbe@115
|
1088 FROM "issue"
|
jbe@115
|
1089 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@115
|
1090 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
jbe@115
|
1091 JOIN "member" ON "interest"."member_id" = "member"."id"
|
jbe@115
|
1092 JOIN "privilege"
|
jbe@115
|
1093 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@115
|
1094 AND "privilege"."member_id" = "member"."id"
|
jbe@115
|
1095 WHERE "issue"."id" = "issue_id_p"
|
jbe@115
|
1096 AND "member"."active" AND "privilege"."voting_right";
|
jbe@115
|
1097 FOR "member_id_v" IN
|
jbe@115
|
1098 SELECT "member_id" FROM "direct_population_snapshot"
|
jbe@115
|
1099 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1100 AND "event" = 'periodic'
|
jbe@115
|
1101 LOOP
|
jbe@115
|
1102 UPDATE "direct_population_snapshot" SET
|
jbe@115
|
1103 "weight" = 1 +
|
jbe@115
|
1104 "weight_of_added_delegations_for_population_snapshot"(
|
jbe@115
|
1105 "issue_id_p",
|
jbe@115
|
1106 "member_id_v",
|
jbe@115
|
1107 '{}'
|
jbe@115
|
1108 )
|
jbe@115
|
1109 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1110 AND "event" = 'periodic'
|
jbe@115
|
1111 AND "member_id" = "member_id_v";
|
jbe@115
|
1112 END LOOP;
|
jbe@115
|
1113 RETURN;
|
jbe@115
|
1114 END;
|
jbe@115
|
1115 $$;
|
jbe@115
|
1116
|
jbe@115
|
1117 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
|
jbe@115
|
1118 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1119 RETURNS VOID
|
jbe@115
|
1120 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1121 DECLARE
|
jbe@115
|
1122 "member_id_v" "member"."id"%TYPE;
|
jbe@115
|
1123 BEGIN
|
jbe@115
|
1124 DELETE FROM "direct_interest_snapshot"
|
jbe@115
|
1125 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1126 AND "event" = 'periodic';
|
jbe@115
|
1127 DELETE FROM "delegating_interest_snapshot"
|
jbe@115
|
1128 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1129 AND "event" = 'periodic';
|
jbe@115
|
1130 DELETE FROM "direct_supporter_snapshot"
|
jbe@115
|
1131 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1132 AND "event" = 'periodic';
|
jbe@115
|
1133 INSERT INTO "direct_interest_snapshot"
|
jbe@115
|
1134 ("issue_id", "event", "member_id", "voting_requested")
|
jbe@115
|
1135 SELECT
|
jbe@115
|
1136 "issue_id_p" AS "issue_id",
|
jbe@115
|
1137 'periodic' AS "event",
|
jbe@115
|
1138 "member"."id" AS "member_id",
|
jbe@115
|
1139 "interest"."voting_requested"
|
jbe@115
|
1140 FROM "issue"
|
jbe@115
|
1141 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@115
|
1142 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
jbe@115
|
1143 JOIN "member" ON "interest"."member_id" = "member"."id"
|
jbe@115
|
1144 JOIN "privilege"
|
jbe@115
|
1145 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@115
|
1146 AND "privilege"."member_id" = "member"."id"
|
jbe@115
|
1147 WHERE "issue"."id" = "issue_id_p"
|
jbe@115
|
1148 AND "member"."active" AND "privilege"."voting_right";
|
jbe@115
|
1149 FOR "member_id_v" IN
|
jbe@115
|
1150 SELECT "member_id" FROM "direct_interest_snapshot"
|
jbe@115
|
1151 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1152 AND "event" = 'periodic'
|
jbe@115
|
1153 LOOP
|
jbe@115
|
1154 UPDATE "direct_interest_snapshot" SET
|
jbe@115
|
1155 "weight" = 1 +
|
jbe@115
|
1156 "weight_of_added_delegations_for_interest_snapshot"(
|
jbe@115
|
1157 "issue_id_p",
|
jbe@115
|
1158 "member_id_v",
|
jbe@115
|
1159 '{}'
|
jbe@115
|
1160 )
|
jbe@115
|
1161 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1162 AND "event" = 'periodic'
|
jbe@115
|
1163 AND "member_id" = "member_id_v";
|
jbe@115
|
1164 END LOOP;
|
jbe@115
|
1165 INSERT INTO "direct_supporter_snapshot"
|
jbe@115
|
1166 ( "issue_id", "initiative_id", "event", "member_id",
|
jbe@115
|
1167 "informed", "satisfied" )
|
jbe@115
|
1168 SELECT
|
jbe@115
|
1169 "issue_id_p" AS "issue_id",
|
jbe@115
|
1170 "initiative"."id" AS "initiative_id",
|
jbe@115
|
1171 'periodic' AS "event",
|
jbe@115
|
1172 "supporter"."member_id" AS "member_id",
|
jbe@115
|
1173 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
jbe@115
|
1174 NOT EXISTS (
|
jbe@115
|
1175 SELECT NULL FROM "critical_opinion"
|
jbe@115
|
1176 WHERE "initiative_id" = "initiative"."id"
|
jbe@115
|
1177 AND "member_id" = "supporter"."member_id"
|
jbe@115
|
1178 ) AS "satisfied"
|
jbe@115
|
1179 FROM "initiative"
|
jbe@115
|
1180 JOIN "supporter"
|
jbe@115
|
1181 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@115
|
1182 JOIN "current_draft"
|
jbe@115
|
1183 ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@115
|
1184 JOIN "direct_interest_snapshot"
|
jbe@115
|
1185 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
|
jbe@115
|
1186 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
jbe@115
|
1187 AND "event" = 'periodic'
|
jbe@115
|
1188 WHERE "initiative"."issue_id" = "issue_id_p";
|
jbe@115
|
1189 RETURN;
|
jbe@115
|
1190 END;
|
jbe@115
|
1191 $$;
|
jbe@115
|
1192
|
jbe@115
|
1193 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
|
jbe@115
|
1194 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1195 RETURNS VOID
|
jbe@115
|
1196 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1197 DECLARE
|
jbe@115
|
1198 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
1199 "policy_row" "policy"%ROWTYPE;
|
jbe@115
|
1200 "initiative_row" "initiative"%ROWTYPE;
|
jbe@115
|
1201 BEGIN
|
jbe@115
|
1202 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1203 SELECT * INTO "policy_row"
|
jbe@115
|
1204 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@115
|
1205 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
|
jbe@115
|
1206 FOR "initiative_row" IN
|
jbe@115
|
1207 SELECT * FROM "initiative"
|
jbe@115
|
1208 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@115
|
1209 LOOP
|
jbe@115
|
1210 IF
|
jbe@115
|
1211 "initiative_row"."satisfied_supporter_count" > 0 AND
|
jbe@115
|
1212 "initiative_row"."satisfied_supporter_count" *
|
jbe@115
|
1213 "policy_row"."initiative_quorum_den" >=
|
jbe@115
|
1214 "issue_row"."population" * "policy_row"."initiative_quorum_num"
|
jbe@115
|
1215 THEN
|
jbe@115
|
1216 UPDATE "initiative" SET "admitted" = TRUE
|
jbe@115
|
1217 WHERE "id" = "initiative_row"."id";
|
jbe@115
|
1218 ELSE
|
jbe@115
|
1219 UPDATE "initiative" SET "admitted" = FALSE
|
jbe@115
|
1220 WHERE "id" = "initiative_row"."id";
|
jbe@115
|
1221 END IF;
|
jbe@115
|
1222 END LOOP;
|
jbe@115
|
1223 IF EXISTS (
|
jbe@115
|
1224 SELECT NULL FROM "initiative"
|
jbe@115
|
1225 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
jbe@115
|
1226 ) THEN
|
jbe@115
|
1227 UPDATE "issue" SET
|
jbe@115
|
1228 "state" = 'voting',
|
jbe@115
|
1229 "accepted" = coalesce("accepted", now()),
|
jbe@115
|
1230 "half_frozen" = coalesce("half_frozen", now()),
|
jbe@115
|
1231 "fully_frozen" = now()
|
jbe@115
|
1232 WHERE "id" = "issue_id_p";
|
jbe@115
|
1233 ELSE
|
jbe@115
|
1234 UPDATE "issue" SET
|
jbe@121
|
1235 "state" = 'canceled_no_initiative_admitted',
|
jbe@121
|
1236 "accepted" = coalesce("accepted", now()),
|
jbe@121
|
1237 "half_frozen" = coalesce("half_frozen", now()),
|
jbe@121
|
1238 "fully_frozen" = now(),
|
jbe@121
|
1239 "closed" = now(),
|
jbe@121
|
1240 "ranks_available" = TRUE
|
jbe@115
|
1241 WHERE "id" = "issue_id_p";
|
jbe@115
|
1242 -- NOTE: The following DELETE statements have effect only when
|
jbe@115
|
1243 -- issue state has been manipulated
|
jbe@115
|
1244 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1245 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1246 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1247 END IF;
|
jbe@115
|
1248 RETURN;
|
jbe@115
|
1249 END;
|
jbe@115
|
1250 $$;
|
jbe@115
|
1251
|
jbe@115
|
1252 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@115
|
1253 RETURNS VOID
|
jbe@115
|
1254 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1255 DECLARE
|
jbe@115
|
1256 "area_id_v" "area"."id"%TYPE;
|
jbe@115
|
1257 "unit_id_v" "unit"."id"%TYPE;
|
jbe@115
|
1258 "member_id_v" "member"."id"%TYPE;
|
jbe@115
|
1259 BEGIN
|
jbe@115
|
1260 PERFORM "lock_issue"("issue_id_p");
|
jbe@115
|
1261 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1262 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@115
|
1263 DELETE FROM "delegating_voter"
|
jbe@115
|
1264 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1265 DELETE FROM "direct_voter"
|
jbe@115
|
1266 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1267 AND "autoreject" = TRUE;
|
jbe@115
|
1268 DELETE FROM "direct_voter"
|
jbe@115
|
1269 USING (
|
jbe@115
|
1270 SELECT
|
jbe@115
|
1271 "direct_voter"."member_id"
|
jbe@115
|
1272 FROM "direct_voter"
|
jbe@115
|
1273 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@115
|
1274 LEFT JOIN "privilege"
|
jbe@115
|
1275 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@115
|
1276 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@115
|
1277 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@115
|
1278 "member"."active" = FALSE OR
|
jbe@115
|
1279 "privilege"."voting_right" ISNULL OR
|
jbe@115
|
1280 "privilege"."voting_right" = FALSE
|
jbe@115
|
1281 )
|
jbe@115
|
1282 ) AS "subquery"
|
jbe@115
|
1283 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@115
|
1284 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@115
|
1285 UPDATE "direct_voter" SET "weight" = 1
|
jbe@115
|
1286 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1287 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@115
|
1288 FOR "member_id_v" IN
|
jbe@115
|
1289 SELECT "interest"."member_id"
|
jbe@115
|
1290 FROM "interest"
|
jbe@115
|
1291 JOIN "member"
|
jbe@115
|
1292 ON "interest"."member_id" = "member"."id"
|
jbe@115
|
1293 LEFT JOIN "direct_voter"
|
jbe@115
|
1294 ON "interest"."member_id" = "direct_voter"."member_id"
|
jbe@115
|
1295 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
jbe@115
|
1296 LEFT JOIN "delegating_voter"
|
jbe@115
|
1297 ON "interest"."member_id" = "delegating_voter"."member_id"
|
jbe@115
|
1298 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
jbe@115
|
1299 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@115
|
1300 AND "interest"."autoreject" = TRUE
|
jbe@115
|
1301 AND "member"."active"
|
jbe@115
|
1302 AND "direct_voter"."member_id" ISNULL
|
jbe@115
|
1303 AND "delegating_voter"."member_id" ISNULL
|
jbe@115
|
1304 UNION SELECT "membership"."member_id"
|
jbe@115
|
1305 FROM "membership"
|
jbe@115
|
1306 JOIN "member"
|
jbe@115
|
1307 ON "membership"."member_id" = "member"."id"
|
jbe@115
|
1308 LEFT JOIN "interest"
|
jbe@115
|
1309 ON "membership"."member_id" = "interest"."member_id"
|
jbe@115
|
1310 AND "interest"."issue_id" = "issue_id_p"
|
jbe@115
|
1311 LEFT JOIN "direct_voter"
|
jbe@115
|
1312 ON "membership"."member_id" = "direct_voter"."member_id"
|
jbe@115
|
1313 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@115
|
1314 LEFT JOIN "delegating_voter"
|
jbe@115
|
1315 ON "membership"."member_id" = "delegating_voter"."member_id"
|
jbe@115
|
1316 AND "delegating_voter"."issue_id" = "issue_id_p"
|
jbe@115
|
1317 WHERE "membership"."area_id" = "area_id_v"
|
jbe@115
|
1318 AND "membership"."autoreject" = TRUE
|
jbe@115
|
1319 AND "member"."active"
|
jbe@115
|
1320 AND "interest"."autoreject" ISNULL
|
jbe@115
|
1321 AND "direct_voter"."member_id" ISNULL
|
jbe@115
|
1322 AND "delegating_voter"."member_id" ISNULL
|
jbe@115
|
1323 LOOP
|
jbe@115
|
1324 INSERT INTO "direct_voter"
|
jbe@115
|
1325 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
jbe@115
|
1326 ("member_id_v", "issue_id_p", 1, TRUE);
|
jbe@115
|
1327 INSERT INTO "vote" (
|
jbe@115
|
1328 "member_id",
|
jbe@115
|
1329 "issue_id",
|
jbe@115
|
1330 "initiative_id",
|
jbe@115
|
1331 "grade"
|
jbe@115
|
1332 ) SELECT
|
jbe@115
|
1333 "member_id_v" AS "member_id",
|
jbe@115
|
1334 "issue_id_p" AS "issue_id",
|
jbe@115
|
1335 "id" AS "initiative_id",
|
jbe@115
|
1336 -1 AS "grade"
|
jbe@115
|
1337 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1338 END LOOP;
|
jbe@115
|
1339 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@115
|
1340 UPDATE "issue" SET
|
jbe@115
|
1341 "state" = 'calculation',
|
jbe@115
|
1342 "closed" = now(),
|
jbe@115
|
1343 "voter_count" = (
|
jbe@115
|
1344 SELECT coalesce(sum("weight"), 0)
|
jbe@115
|
1345 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1346 )
|
jbe@115
|
1347 WHERE "id" = "issue_id_p";
|
jbe@115
|
1348 UPDATE "initiative" SET
|
jbe@115
|
1349 "positive_votes" = "vote_counts"."positive_votes",
|
jbe@115
|
1350 "negative_votes" = "vote_counts"."negative_votes",
|
jbe@115
|
1351 "agreed" = CASE WHEN "majority_strict" THEN
|
jbe@115
|
1352 "vote_counts"."positive_votes" * "majority_den" >
|
jbe@115
|
1353 "majority_num" *
|
jbe@115
|
1354 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@115
|
1355 ELSE
|
jbe@115
|
1356 "vote_counts"."positive_votes" * "majority_den" >=
|
jbe@115
|
1357 "majority_num" *
|
jbe@115
|
1358 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@115
|
1359 END
|
jbe@115
|
1360 FROM
|
jbe@115
|
1361 ( SELECT
|
jbe@115
|
1362 "initiative"."id" AS "initiative_id",
|
jbe@115
|
1363 coalesce(
|
jbe@115
|
1364 sum(
|
jbe@115
|
1365 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@115
|
1366 ),
|
jbe@115
|
1367 0
|
jbe@115
|
1368 ) AS "positive_votes",
|
jbe@115
|
1369 coalesce(
|
jbe@115
|
1370 sum(
|
jbe@115
|
1371 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@115
|
1372 ),
|
jbe@115
|
1373 0
|
jbe@115
|
1374 ) AS "negative_votes"
|
jbe@115
|
1375 FROM "initiative"
|
jbe@115
|
1376 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@115
|
1377 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@115
|
1378 LEFT JOIN "direct_voter"
|
jbe@115
|
1379 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
jbe@115
|
1380 LEFT JOIN "vote"
|
jbe@115
|
1381 ON "vote"."initiative_id" = "initiative"."id"
|
jbe@115
|
1382 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@115
|
1383 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@115
|
1384 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
jbe@115
|
1385 GROUP BY "initiative"."id"
|
jbe@115
|
1386 ) AS "vote_counts",
|
jbe@115
|
1387 "issue",
|
jbe@115
|
1388 "policy"
|
jbe@115
|
1389 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
jbe@115
|
1390 AND "issue"."id" = "initiative"."issue_id"
|
jbe@115
|
1391 AND "policy"."id" = "issue"."policy_id";
|
jbe@115
|
1392 -- NOTE: "closed" column of issue must be set at this point
|
jbe@115
|
1393 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1394 INSERT INTO "battle" (
|
jbe@115
|
1395 "issue_id",
|
jbe@115
|
1396 "winning_initiative_id", "losing_initiative_id",
|
jbe@115
|
1397 "count"
|
jbe@115
|
1398 ) SELECT
|
jbe@115
|
1399 "issue_id",
|
jbe@115
|
1400 "winning_initiative_id", "losing_initiative_id",
|
jbe@115
|
1401 "count"
|
jbe@115
|
1402 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1403 END;
|
jbe@115
|
1404 $$;
|
jbe@115
|
1405
|
jbe@115
|
1406 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
jbe@115
|
1407 RETURNS VOID
|
jbe@115
|
1408 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1409 DECLARE
|
jbe@115
|
1410 "dimension_v" INTEGER;
|
jbe@115
|
1411 "vote_matrix" INT4[][]; -- absolute votes
|
jbe@115
|
1412 "matrix" INT8[][]; -- defeat strength / best paths
|
jbe@115
|
1413 "i" INTEGER;
|
jbe@115
|
1414 "j" INTEGER;
|
jbe@115
|
1415 "k" INTEGER;
|
jbe@115
|
1416 "battle_row" "battle"%ROWTYPE;
|
jbe@115
|
1417 "rank_ary" INT4[];
|
jbe@115
|
1418 "rank_v" INT4;
|
jbe@115
|
1419 "done_v" INTEGER;
|
jbe@115
|
1420 "winners_ary" INTEGER[];
|
jbe@115
|
1421 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@115
|
1422 BEGIN
|
jbe@115
|
1423 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
jbe@115
|
1424 SELECT count(1) INTO "dimension_v" FROM "initiative"
|
jbe@115
|
1425 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
jbe@115
|
1426 IF "dimension_v" = 1 THEN
|
jbe@115
|
1427 UPDATE "initiative" SET "rank" = 1
|
jbe@115
|
1428 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
jbe@115
|
1429 ELSIF "dimension_v" > 1 THEN
|
jbe@115
|
1430 -- Create "vote_matrix" with absolute number of votes in pairwise
|
jbe@115
|
1431 -- comparison:
|
jbe@115
|
1432 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@115
|
1433 "i" := 1;
|
jbe@115
|
1434 "j" := 2;
|
jbe@115
|
1435 FOR "battle_row" IN
|
jbe@115
|
1436 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1437 ORDER BY "winning_initiative_id", "losing_initiative_id"
|
jbe@115
|
1438 LOOP
|
jbe@115
|
1439 "vote_matrix"["i"]["j"] := "battle_row"."count";
|
jbe@115
|
1440 IF "j" = "dimension_v" THEN
|
jbe@115
|
1441 "i" := "i" + 1;
|
jbe@115
|
1442 "j" := 1;
|
jbe@115
|
1443 ELSE
|
jbe@115
|
1444 "j" := "j" + 1;
|
jbe@115
|
1445 IF "j" = "i" THEN
|
jbe@115
|
1446 "j" := "j" + 1;
|
jbe@115
|
1447 END IF;
|
jbe@115
|
1448 END IF;
|
jbe@115
|
1449 END LOOP;
|
jbe@115
|
1450 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
jbe@115
|
1451 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
jbe@115
|
1452 END IF;
|
jbe@115
|
1453 -- Store defeat strengths in "matrix" using "defeat_strength"
|
jbe@115
|
1454 -- function:
|
jbe@115
|
1455 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@115
|
1456 "i" := 1;
|
jbe@115
|
1457 LOOP
|
jbe@115
|
1458 "j" := 1;
|
jbe@115
|
1459 LOOP
|
jbe@115
|
1460 IF "i" != "j" THEN
|
jbe@115
|
1461 "matrix"["i"]["j"] := "defeat_strength"(
|
jbe@115
|
1462 "vote_matrix"["i"]["j"],
|
jbe@115
|
1463 "vote_matrix"["j"]["i"]
|
jbe@115
|
1464 );
|
jbe@115
|
1465 END IF;
|
jbe@115
|
1466 EXIT WHEN "j" = "dimension_v";
|
jbe@115
|
1467 "j" := "j" + 1;
|
jbe@115
|
1468 END LOOP;
|
jbe@115
|
1469 EXIT WHEN "i" = "dimension_v";
|
jbe@115
|
1470 "i" := "i" + 1;
|
jbe@115
|
1471 END LOOP;
|
jbe@115
|
1472 -- Find best paths:
|
jbe@115
|
1473 "i" := 1;
|
jbe@115
|
1474 LOOP
|
jbe@115
|
1475 "j" := 1;
|
jbe@115
|
1476 LOOP
|
jbe@115
|
1477 IF "i" != "j" THEN
|
jbe@115
|
1478 "k" := 1;
|
jbe@115
|
1479 LOOP
|
jbe@115
|
1480 IF "i" != "k" AND "j" != "k" THEN
|
jbe@115
|
1481 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
jbe@115
|
1482 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
jbe@115
|
1483 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
jbe@115
|
1484 END IF;
|
jbe@115
|
1485 ELSE
|
jbe@115
|
1486 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
jbe@115
|
1487 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
jbe@115
|
1488 END IF;
|
jbe@115
|
1489 END IF;
|
jbe@115
|
1490 END IF;
|
jbe@115
|
1491 EXIT WHEN "k" = "dimension_v";
|
jbe@115
|
1492 "k" := "k" + 1;
|
jbe@115
|
1493 END LOOP;
|
jbe@115
|
1494 END IF;
|
jbe@115
|
1495 EXIT WHEN "j" = "dimension_v";
|
jbe@115
|
1496 "j" := "j" + 1;
|
jbe@115
|
1497 END LOOP;
|
jbe@115
|
1498 EXIT WHEN "i" = "dimension_v";
|
jbe@115
|
1499 "i" := "i" + 1;
|
jbe@115
|
1500 END LOOP;
|
jbe@115
|
1501 -- Determine order of winners:
|
jbe@115
|
1502 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@115
|
1503 "rank_v" := 1;
|
jbe@115
|
1504 "done_v" := 0;
|
jbe@115
|
1505 LOOP
|
jbe@115
|
1506 "winners_ary" := '{}';
|
jbe@115
|
1507 "i" := 1;
|
jbe@115
|
1508 LOOP
|
jbe@115
|
1509 IF "rank_ary"["i"] ISNULL THEN
|
jbe@115
|
1510 "j" := 1;
|
jbe@115
|
1511 LOOP
|
jbe@115
|
1512 IF
|
jbe@115
|
1513 "i" != "j" AND
|
jbe@115
|
1514 "rank_ary"["j"] ISNULL AND
|
jbe@115
|
1515 "matrix"["j"]["i"] > "matrix"["i"]["j"]
|
jbe@115
|
1516 THEN
|
jbe@115
|
1517 -- someone else is better
|
jbe@115
|
1518 EXIT;
|
jbe@115
|
1519 END IF;
|
jbe@115
|
1520 IF "j" = "dimension_v" THEN
|
jbe@115
|
1521 -- noone is better
|
jbe@115
|
1522 "winners_ary" := "winners_ary" || "i";
|
jbe@115
|
1523 EXIT;
|
jbe@115
|
1524 END IF;
|
jbe@115
|
1525 "j" := "j" + 1;
|
jbe@115
|
1526 END LOOP;
|
jbe@115
|
1527 END IF;
|
jbe@115
|
1528 EXIT WHEN "i" = "dimension_v";
|
jbe@115
|
1529 "i" := "i" + 1;
|
jbe@115
|
1530 END LOOP;
|
jbe@115
|
1531 "i" := 1;
|
jbe@115
|
1532 LOOP
|
jbe@115
|
1533 "rank_ary"["winners_ary"["i"]] := "rank_v";
|
jbe@115
|
1534 "done_v" := "done_v" + 1;
|
jbe@115
|
1535 EXIT WHEN "i" = array_upper("winners_ary", 1);
|
jbe@115
|
1536 "i" := "i" + 1;
|
jbe@115
|
1537 END LOOP;
|
jbe@115
|
1538 EXIT WHEN "done_v" = "dimension_v";
|
jbe@115
|
1539 "rank_v" := "rank_v" + 1;
|
jbe@115
|
1540 END LOOP;
|
jbe@115
|
1541 -- write preliminary ranks:
|
jbe@115
|
1542 "i" := 1;
|
jbe@115
|
1543 FOR "initiative_id_v" IN
|
jbe@115
|
1544 SELECT "id" FROM "initiative"
|
jbe@115
|
1545 WHERE "issue_id" = "issue_id_p" AND "agreed"
|
jbe@115
|
1546 ORDER BY "id"
|
jbe@115
|
1547 LOOP
|
jbe@115
|
1548 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
|
jbe@115
|
1549 WHERE "id" = "initiative_id_v";
|
jbe@115
|
1550 "i" := "i" + 1;
|
jbe@115
|
1551 END LOOP;
|
jbe@115
|
1552 IF "i" != "dimension_v" + 1 THEN
|
jbe@115
|
1553 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
jbe@115
|
1554 END IF;
|
jbe@115
|
1555 -- straighten ranks (start counting with 1, no equal ranks):
|
jbe@115
|
1556 "rank_v" := 1;
|
jbe@115
|
1557 FOR "initiative_id_v" IN
|
jbe@115
|
1558 SELECT "id" FROM "initiative"
|
jbe@115
|
1559 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
|
jbe@115
|
1560 ORDER BY
|
jbe@115
|
1561 "rank",
|
jbe@115
|
1562 "vote_ratio"("positive_votes", "negative_votes") DESC,
|
jbe@115
|
1563 "id"
|
jbe@115
|
1564 LOOP
|
jbe@115
|
1565 UPDATE "initiative" SET "rank" = "rank_v"
|
jbe@115
|
1566 WHERE "id" = "initiative_id_v";
|
jbe@115
|
1567 "rank_v" := "rank_v" + 1;
|
jbe@115
|
1568 END LOOP;
|
jbe@115
|
1569 END IF;
|
jbe@115
|
1570 -- mark issue as finished
|
jbe@115
|
1571 UPDATE "issue" SET
|
jbe@115
|
1572 "state" =
|
jbe@121
|
1573 CASE WHEN "dimension_v" = 0 THEN
|
jbe@121
|
1574 'finished_without_winner'::"issue_state"
|
jbe@115
|
1575 ELSE
|
jbe@121
|
1576 'finished_with_winner'::"issue_state"
|
jbe@115
|
1577 END,
|
jbe@115
|
1578 "ranks_available" = TRUE
|
jbe@115
|
1579 WHERE "id" = "issue_id_p";
|
jbe@115
|
1580 RETURN;
|
jbe@115
|
1581 END;
|
jbe@115
|
1582 $$;
|
jbe@115
|
1583
|
jbe@115
|
1584 CREATE OR REPLACE FUNCTION "check_issue"
|
jbe@115
|
1585 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1586 RETURNS VOID
|
jbe@115
|
1587 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1588 DECLARE
|
jbe@115
|
1589 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
1590 "policy_row" "policy"%ROWTYPE;
|
jbe@115
|
1591 "voting_requested_v" BOOLEAN;
|
jbe@115
|
1592 BEGIN
|
jbe@115
|
1593 PERFORM "lock_issue"("issue_id_p");
|
jbe@115
|
1594 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1595 -- only process open issues:
|
jbe@115
|
1596 IF "issue_row"."closed" ISNULL THEN
|
jbe@115
|
1597 SELECT * INTO "policy_row" FROM "policy"
|
jbe@115
|
1598 WHERE "id" = "issue_row"."policy_id";
|
jbe@115
|
1599 -- create a snapshot, unless issue is already fully frozen:
|
jbe@115
|
1600 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@115
|
1601 PERFORM "create_snapshot"("issue_id_p");
|
jbe@115
|
1602 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1603 END IF;
|
jbe@115
|
1604 -- eventually close or accept issues, which have not been accepted:
|
jbe@115
|
1605 IF "issue_row"."accepted" ISNULL THEN
|
jbe@115
|
1606 IF EXISTS (
|
jbe@115
|
1607 SELECT NULL FROM "initiative"
|
jbe@115
|
1608 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1609 AND "supporter_count" > 0
|
jbe@115
|
1610 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
jbe@115
|
1611 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
jbe@115
|
1612 ) THEN
|
jbe@115
|
1613 -- accept issues, if supporter count is high enough
|
jbe@115
|
1614 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@115
|
1615 -- NOTE: "issue_row" used later
|
jbe@115
|
1616 "issue_row"."state" := 'discussion';
|
jbe@115
|
1617 "issue_row"."accepted" := now();
|
jbe@115
|
1618 UPDATE "issue" SET
|
jbe@115
|
1619 "state" = "issue_row"."state",
|
jbe@115
|
1620 "accepted" = "issue_row"."accepted"
|
jbe@115
|
1621 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1622 ELSIF
|
jbe@115
|
1623 now() >= "issue_row"."created" + "issue_row"."admission_time"
|
jbe@115
|
1624 THEN
|
jbe@115
|
1625 -- close issues, if admission time has expired
|
jbe@115
|
1626 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@115
|
1627 UPDATE "issue" SET
|
jbe@115
|
1628 "state" = 'canceled_issue_not_accepted',
|
jbe@115
|
1629 "closed" = now()
|
jbe@115
|
1630 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1631 END IF;
|
jbe@115
|
1632 END IF;
|
jbe@115
|
1633 -- eventually half freeze issues:
|
jbe@115
|
1634 IF
|
jbe@115
|
1635 -- NOTE: issue can't be closed at this point, if it has been accepted
|
jbe@115
|
1636 "issue_row"."accepted" NOTNULL AND
|
jbe@115
|
1637 "issue_row"."half_frozen" ISNULL
|
jbe@115
|
1638 THEN
|
jbe@115
|
1639 SELECT
|
jbe@115
|
1640 CASE
|
jbe@115
|
1641 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
jbe@115
|
1642 TRUE
|
jbe@115
|
1643 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
jbe@115
|
1644 FALSE
|
jbe@115
|
1645 ELSE NULL
|
jbe@115
|
1646 END
|
jbe@115
|
1647 INTO "voting_requested_v"
|
jbe@115
|
1648 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1649 IF
|
jbe@115
|
1650 "voting_requested_v" OR (
|
jbe@115
|
1651 "voting_requested_v" ISNULL AND
|
jbe@115
|
1652 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
|
jbe@115
|
1653 )
|
jbe@115
|
1654 THEN
|
jbe@115
|
1655 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
|
jbe@115
|
1656 -- NOTE: "issue_row" used later
|
jbe@115
|
1657 "issue_row"."state" := 'verification';
|
jbe@115
|
1658 "issue_row"."half_frozen" := now();
|
jbe@115
|
1659 UPDATE "issue" SET
|
jbe@115
|
1660 "state" = "issue_row"."state",
|
jbe@115
|
1661 "half_frozen" = "issue_row"."half_frozen"
|
jbe@115
|
1662 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1663 END IF;
|
jbe@115
|
1664 END IF;
|
jbe@115
|
1665 -- close issues after some time, if all initiatives have been revoked:
|
jbe@115
|
1666 IF
|
jbe@115
|
1667 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1668 NOT EXISTS (
|
jbe@115
|
1669 -- all initiatives are revoked
|
jbe@115
|
1670 SELECT NULL FROM "initiative"
|
jbe@115
|
1671 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@115
|
1672 ) AND (
|
jbe@115
|
1673 -- and issue has not been accepted yet
|
jbe@115
|
1674 "issue_row"."accepted" ISNULL OR
|
jbe@115
|
1675 NOT EXISTS (
|
jbe@115
|
1676 -- or no initiatives have been revoked lately
|
jbe@115
|
1677 SELECT NULL FROM "initiative"
|
jbe@115
|
1678 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1679 AND now() < "revoked" + "issue_row"."verification_time"
|
jbe@115
|
1680 ) OR (
|
jbe@115
|
1681 -- or verification time has elapsed
|
jbe@115
|
1682 "issue_row"."half_frozen" NOTNULL AND
|
jbe@115
|
1683 "issue_row"."fully_frozen" ISNULL AND
|
jbe@115
|
1684 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@115
|
1685 )
|
jbe@115
|
1686 )
|
jbe@115
|
1687 THEN
|
jbe@115
|
1688 -- NOTE: "issue_row" used later
|
jbe@115
|
1689 IF "issue_row"."accepted" ISNULL THEN
|
jbe@115
|
1690 "issue_row"."state" := 'canceled_revoked_before_accepted';
|
jbe@115
|
1691 ELSIF "issue_row"."half_frozen" ISNULL THEN
|
jbe@115
|
1692 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
|
jbe@115
|
1693 ELSE
|
jbe@115
|
1694 "issue_row"."state" := 'canceled_after_revocation_during_verification';
|
jbe@115
|
1695 END IF;
|
jbe@115
|
1696 "issue_row"."closed" := now();
|
jbe@115
|
1697 UPDATE "issue" SET
|
jbe@115
|
1698 "state" = "issue_row"."state",
|
jbe@115
|
1699 "closed" = "issue_row"."closed"
|
jbe@115
|
1700 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1701 END IF;
|
jbe@115
|
1702 -- fully freeze issue after verification time:
|
jbe@115
|
1703 IF
|
jbe@115
|
1704 "issue_row"."half_frozen" NOTNULL AND
|
jbe@115
|
1705 "issue_row"."fully_frozen" ISNULL AND
|
jbe@115
|
1706 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1707 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@115
|
1708 THEN
|
jbe@115
|
1709 PERFORM "freeze_after_snapshot"("issue_id_p");
|
jbe@115
|
1710 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
|
jbe@115
|
1711 END IF;
|
jbe@115
|
1712 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1713 -- close issue by calling close_voting(...) after voting time:
|
jbe@115
|
1714 IF
|
jbe@115
|
1715 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1716 "issue_row"."fully_frozen" NOTNULL AND
|
jbe@115
|
1717 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
|
jbe@115
|
1718 THEN
|
jbe@115
|
1719 PERFORM "close_voting"("issue_id_p");
|
jbe@115
|
1720 -- calculate ranks will not consume much time and can be done now
|
jbe@115
|
1721 PERFORM "calculate_ranks"("issue_id_p");
|
jbe@115
|
1722 END IF;
|
jbe@115
|
1723 END IF;
|
jbe@115
|
1724 RETURN;
|
jbe@115
|
1725 END;
|
jbe@115
|
1726 $$;
|
jbe@115
|
1727
|
jbe@115
|
1728 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@115
|
1729 RETURNS VOID
|
jbe@115
|
1730 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1731 DECLARE
|
jbe@115
|
1732 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
1733 BEGIN
|
jbe@115
|
1734 SELECT * INTO "issue_row"
|
jbe@115
|
1735 FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@115
|
1736 FOR UPDATE;
|
jbe@115
|
1737 IF "issue_row"."cleaned" ISNULL THEN
|
jbe@115
|
1738 UPDATE "issue" SET
|
jbe@115
|
1739 "closed" = NULL,
|
jbe@115
|
1740 "ranks_available" = FALSE
|
jbe@115
|
1741 WHERE "id" = "issue_id_p";
|
jbe@115
|
1742 DELETE FROM "delegating_voter"
|
jbe@115
|
1743 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1744 DELETE FROM "direct_voter"
|
jbe@115
|
1745 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1746 DELETE FROM "delegating_interest_snapshot"
|
jbe@115
|
1747 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1748 DELETE FROM "direct_interest_snapshot"
|
jbe@115
|
1749 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1750 DELETE FROM "delegating_population_snapshot"
|
jbe@115
|
1751 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1752 DELETE FROM "direct_population_snapshot"
|
jbe@115
|
1753 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1754 DELETE FROM "non_voter"
|
jbe@115
|
1755 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1756 DELETE FROM "delegation"
|
jbe@115
|
1757 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1758 DELETE FROM "supporter"
|
jbe@115
|
1759 WHERE "issue_id" = "issue_id_p";
|
jbe@115
|
1760 UPDATE "issue" SET
|
jbe@115
|
1761 "closed" = "issue_row"."closed",
|
jbe@115
|
1762 "ranks_available" = "issue_row"."ranks_available",
|
jbe@115
|
1763 "cleaned" = now()
|
jbe@115
|
1764 WHERE "id" = "issue_id_p";
|
jbe@115
|
1765 END IF;
|
jbe@115
|
1766 RETURN;
|
jbe@115
|
1767 END;
|
jbe@115
|
1768 $$;
|
jbe@115
|
1769
|
jbe@115
|
1770 CREATE OR REPLACE FUNCTION "check_issue"
|
jbe@115
|
1771 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@115
|
1772 RETURNS VOID
|
jbe@115
|
1773 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1774 DECLARE
|
jbe@115
|
1775 "issue_row" "issue"%ROWTYPE;
|
jbe@115
|
1776 "policy_row" "policy"%ROWTYPE;
|
jbe@115
|
1777 "voting_requested_v" BOOLEAN;
|
jbe@115
|
1778 BEGIN
|
jbe@115
|
1779 PERFORM "lock_issue"("issue_id_p");
|
jbe@115
|
1780 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1781 -- only process open issues:
|
jbe@115
|
1782 IF "issue_row"."closed" ISNULL THEN
|
jbe@115
|
1783 SELECT * INTO "policy_row" FROM "policy"
|
jbe@115
|
1784 WHERE "id" = "issue_row"."policy_id";
|
jbe@115
|
1785 -- create a snapshot, unless issue is already fully frozen:
|
jbe@115
|
1786 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@115
|
1787 PERFORM "create_snapshot"("issue_id_p");
|
jbe@115
|
1788 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1789 END IF;
|
jbe@115
|
1790 -- eventually close or accept issues, which have not been accepted:
|
jbe@115
|
1791 IF "issue_row"."accepted" ISNULL THEN
|
jbe@115
|
1792 IF EXISTS (
|
jbe@115
|
1793 SELECT NULL FROM "initiative"
|
jbe@115
|
1794 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1795 AND "supporter_count" > 0
|
jbe@115
|
1796 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
jbe@115
|
1797 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
jbe@115
|
1798 ) THEN
|
jbe@115
|
1799 -- accept issues, if supporter count is high enough
|
jbe@115
|
1800 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@115
|
1801 -- NOTE: "issue_row" used later
|
jbe@115
|
1802 "issue_row"."state" := 'discussion';
|
jbe@115
|
1803 "issue_row"."accepted" := now();
|
jbe@115
|
1804 UPDATE "issue" SET
|
jbe@115
|
1805 "state" = "issue_row"."state",
|
jbe@115
|
1806 "accepted" = "issue_row"."accepted"
|
jbe@115
|
1807 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1808 ELSIF
|
jbe@115
|
1809 now() >= "issue_row"."created" + "issue_row"."admission_time"
|
jbe@115
|
1810 THEN
|
jbe@115
|
1811 -- close issues, if admission time has expired
|
jbe@115
|
1812 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@115
|
1813 UPDATE "issue" SET
|
jbe@115
|
1814 "state" = 'canceled_issue_not_accepted',
|
jbe@115
|
1815 "closed" = now()
|
jbe@115
|
1816 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1817 END IF;
|
jbe@115
|
1818 END IF;
|
jbe@115
|
1819 -- eventually half freeze issues:
|
jbe@115
|
1820 IF
|
jbe@115
|
1821 -- NOTE: issue can't be closed at this point, if it has been accepted
|
jbe@115
|
1822 "issue_row"."accepted" NOTNULL AND
|
jbe@115
|
1823 "issue_row"."half_frozen" ISNULL
|
jbe@115
|
1824 THEN
|
jbe@115
|
1825 SELECT
|
jbe@115
|
1826 CASE
|
jbe@115
|
1827 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
jbe@115
|
1828 TRUE
|
jbe@115
|
1829 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
jbe@115
|
1830 FALSE
|
jbe@115
|
1831 ELSE NULL
|
jbe@115
|
1832 END
|
jbe@115
|
1833 INTO "voting_requested_v"
|
jbe@115
|
1834 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1835 IF
|
jbe@115
|
1836 "voting_requested_v" OR (
|
jbe@115
|
1837 "voting_requested_v" ISNULL AND
|
jbe@115
|
1838 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
|
jbe@115
|
1839 )
|
jbe@115
|
1840 THEN
|
jbe@115
|
1841 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
|
jbe@115
|
1842 -- NOTE: "issue_row" used later
|
jbe@115
|
1843 "issue_row"."state" := 'verification';
|
jbe@115
|
1844 "issue_row"."half_frozen" := now();
|
jbe@115
|
1845 UPDATE "issue" SET
|
jbe@115
|
1846 "state" = "issue_row"."state",
|
jbe@115
|
1847 "half_frozen" = "issue_row"."half_frozen"
|
jbe@115
|
1848 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1849 END IF;
|
jbe@115
|
1850 END IF;
|
jbe@115
|
1851 -- close issues after some time, if all initiatives have been revoked:
|
jbe@115
|
1852 IF
|
jbe@115
|
1853 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1854 NOT EXISTS (
|
jbe@115
|
1855 -- all initiatives are revoked
|
jbe@115
|
1856 SELECT NULL FROM "initiative"
|
jbe@115
|
1857 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@115
|
1858 ) AND (
|
jbe@115
|
1859 -- and issue has not been accepted yet
|
jbe@115
|
1860 "issue_row"."accepted" ISNULL OR
|
jbe@115
|
1861 NOT EXISTS (
|
jbe@115
|
1862 -- or no initiatives have been revoked lately
|
jbe@115
|
1863 SELECT NULL FROM "initiative"
|
jbe@115
|
1864 WHERE "issue_id" = "issue_id_p"
|
jbe@115
|
1865 AND now() < "revoked" + "issue_row"."verification_time"
|
jbe@115
|
1866 ) OR (
|
jbe@115
|
1867 -- or verification time has elapsed
|
jbe@115
|
1868 "issue_row"."half_frozen" NOTNULL AND
|
jbe@115
|
1869 "issue_row"."fully_frozen" ISNULL AND
|
jbe@115
|
1870 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@115
|
1871 )
|
jbe@115
|
1872 )
|
jbe@115
|
1873 THEN
|
jbe@115
|
1874 -- NOTE: "issue_row" used later
|
jbe@115
|
1875 IF "issue_row"."accepted" ISNULL THEN
|
jbe@115
|
1876 "issue_row"."state" := 'canceled_revoked_before_accepted';
|
jbe@115
|
1877 ELSIF "issue_row"."half_frozen" ISNULL THEN
|
jbe@115
|
1878 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
|
jbe@115
|
1879 ELSE
|
jbe@115
|
1880 "issue_row"."state" := 'canceled_after_revocation_during_verification';
|
jbe@115
|
1881 END IF;
|
jbe@115
|
1882 "issue_row"."closed" := now();
|
jbe@115
|
1883 UPDATE "issue" SET
|
jbe@115
|
1884 "state" = "issue_row"."state",
|
jbe@115
|
1885 "closed" = "issue_row"."closed"
|
jbe@115
|
1886 WHERE "id" = "issue_row"."id";
|
jbe@115
|
1887 END IF;
|
jbe@115
|
1888 -- fully freeze issue after verification time:
|
jbe@115
|
1889 IF
|
jbe@115
|
1890 "issue_row"."half_frozen" NOTNULL AND
|
jbe@115
|
1891 "issue_row"."fully_frozen" ISNULL AND
|
jbe@115
|
1892 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1893 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@115
|
1894 THEN
|
jbe@115
|
1895 PERFORM "freeze_after_snapshot"("issue_id_p");
|
jbe@115
|
1896 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
|
jbe@115
|
1897 END IF;
|
jbe@115
|
1898 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@115
|
1899 -- close issue by calling close_voting(...) after voting time:
|
jbe@115
|
1900 IF
|
jbe@115
|
1901 "issue_row"."closed" ISNULL AND
|
jbe@115
|
1902 "issue_row"."fully_frozen" NOTNULL AND
|
jbe@115
|
1903 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
|
jbe@115
|
1904 THEN
|
jbe@115
|
1905 PERFORM "close_voting"("issue_id_p");
|
jbe@115
|
1906 -- calculate ranks will not consume much time and can be done now
|
jbe@115
|
1907 PERFORM "calculate_ranks"("issue_id_p");
|
jbe@115
|
1908 END IF;
|
jbe@115
|
1909 END IF;
|
jbe@115
|
1910 RETURN;
|
jbe@115
|
1911 END;
|
jbe@115
|
1912 $$;
|
jbe@115
|
1913
|
jbe@115
|
1914 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@115
|
1915 RETURNS VOID
|
jbe@115
|
1916 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@115
|
1917 BEGIN
|
jbe@115
|
1918 UPDATE "member" SET
|
jbe@115
|
1919 "last_login" = NULL,
|
jbe@115
|
1920 "login" = NULL,
|
jbe@115
|
1921 "password" = NULL,
|
jbe@115
|
1922 "notify_email" = NULL,
|
jbe@115
|
1923 "notify_email_unconfirmed" = NULL,
|
jbe@115
|
1924 "notify_email_secret" = NULL,
|
jbe@115
|
1925 "notify_email_secret_expiry" = NULL,
|
jbe@115
|
1926 "notify_email_lock_expiry" = NULL,
|
jbe@115
|
1927 "password_reset_secret" = NULL,
|
jbe@115
|
1928 "password_reset_secret_expiry" = NULL,
|
jbe@115
|
1929 "organizational_unit" = NULL,
|
jbe@115
|
1930 "internal_posts" = NULL,
|
jbe@115
|
1931 "realname" = NULL,
|
jbe@115
|
1932 "birthday" = NULL,
|
jbe@115
|
1933 "address" = NULL,
|
jbe@115
|
1934 "email" = NULL,
|
jbe@115
|
1935 "xmpp_address" = NULL,
|
jbe@115
|
1936 "website" = NULL,
|
jbe@115
|
1937 "phone" = NULL,
|
jbe@115
|
1938 "mobile_phone" = NULL,
|
jbe@115
|
1939 "profession" = NULL,
|
jbe@115
|
1940 "external_memberships" = NULL,
|
jbe@115
|
1941 "external_posts" = NULL,
|
jbe@115
|
1942 "statement" = NULL;
|
jbe@115
|
1943 -- "text_search_data" is updated by triggers
|
jbe@115
|
1944 DELETE FROM "invite_code";
|
jbe@115
|
1945 DELETE FROM "setting";
|
jbe@115
|
1946 DELETE FROM "setting_map";
|
jbe@115
|
1947 DELETE FROM "member_relation_setting";
|
jbe@115
|
1948 DELETE FROM "member_image";
|
jbe@115
|
1949 DELETE FROM "contact";
|
jbe@115
|
1950 DELETE FROM "ignored_member";
|
jbe@115
|
1951 DELETE FROM "session";
|
jbe@115
|
1952 DELETE FROM "area_setting";
|
jbe@115
|
1953 DELETE FROM "issue_setting";
|
jbe@115
|
1954 DELETE FROM "ignored_initiative";
|
jbe@115
|
1955 DELETE FROM "initiative_setting";
|
jbe@115
|
1956 DELETE FROM "suggestion_setting";
|
jbe@115
|
1957 DELETE FROM "non_voter";
|
jbe@115
|
1958 DELETE FROM "direct_voter" USING "issue"
|
jbe@115
|
1959 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@115
|
1960 AND "issue"."closed" ISNULL;
|
jbe@115
|
1961 RETURN;
|
jbe@115
|
1962 END;
|
jbe@115
|
1963 $$;
|
jbe@115
|
1964
|
jbe@115
|
1965
|
jbe@115
|
1966 -- Delete old "delegation_scope" TYPE:
|
jbe@115
|
1967
|
jbe@115
|
1968 DROP TYPE "delegation_scope_old";
|
jbe@115
|
1969
|
jbe@115
|
1970
|
jbe@115
|
1971 COMMIT;
|
jbe@115
|
1972
|
jbe@115
|
1973
|
jbe@122
|
1974 -- Generate issue states and add constraints:
|
jbe@115
|
1975
|
jbe@115
|
1976 UPDATE "issue" SET "state" =
|
jbe@115
|
1977 CASE
|
jbe@115
|
1978 WHEN "closed" ISNULL THEN
|
jbe@115
|
1979 CASE
|
jbe@115
|
1980 WHEN "accepted" ISNULL THEN
|
jbe@115
|
1981 'admission'::"issue_state"
|
jbe@115
|
1982 WHEN "half_frozen" ISNULL THEN
|
jbe@115
|
1983 'discussion'::"issue_state"
|
jbe@115
|
1984 WHEN "fully_frozen" ISNULL THEN
|
jbe@115
|
1985 'verification'::"issue_state"
|
jbe@115
|
1986 ELSE
|
jbe@115
|
1987 'voting'::"issue_state"
|
jbe@115
|
1988 END
|
jbe@115
|
1989 WHEN "fully_frozen" NOTNULL THEN
|
jbe@115
|
1990 CASE
|
jbe@115
|
1991 WHEN "fully_frozen" = "closed" THEN
|
jbe@115
|
1992 'canceled_no_initiative_admitted'::"issue_state"
|
jbe@115
|
1993 ELSE
|
jbe@115
|
1994 'finished_without_winner'::"issue_state" -- NOTE: corrected later
|
jbe@115
|
1995 END
|
jbe@115
|
1996 WHEN "half_frozen" NOTNULL THEN
|
jbe@115
|
1997 'canceled_after_revocation_during_verification'::"issue_state"
|
jbe@115
|
1998 WHEN "accepted" NOTNULL THEN
|
jbe@115
|
1999 'canceled_after_revocation_during_discussion'::"issue_state"
|
jbe@115
|
2000 ELSE
|
jbe@115
|
2001 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
|
jbe@115
|
2002 END;
|
jbe@117
|
2003 UPDATE "issue" SET "state" = 'finished_with_winner'
|
jbe@117
|
2004 FROM "initiative"
|
jbe@117
|
2005 WHERE "issue"."id" = "initiative"."issue_id"
|
jbe@117
|
2006 AND "issue"."state" = 'finished_without_winner'
|
jbe@117
|
2007 AND "initiative"."agreed";
|
jbe@117
|
2008 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
|
jbe@117
|
2009 FROM "initiative"
|
jbe@117
|
2010 WHERE "issue"."id" = "initiative"."issue_id"
|
jbe@117
|
2011 AND "issue"."state" = 'canceled_revoked_before_accepted'
|
jbe@117
|
2012 AND "initiative"."revoked" ISNULL;
|
jbe@115
|
2013
|
jbe@115
|
2014 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
|
jbe@115
|
2015
|
jbe@115
|
2016 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
|
jbe@115
|
2017 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
|
jbe@115
|
2018 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2019 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2020 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2021 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2022 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2023 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2024 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2025 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2026 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
|
jbe@115
|
2027 ) AND (
|
jbe@115
|
2028 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
|
jbe@115
|
2029 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
|
jbe@115
|
2030 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
|
jbe@115
|
2031 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
|
jbe@115
|
2032 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
|
jbe@115
|
2033 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
|
jbe@115
|
2034 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
|
jbe@115
|
2035 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
|
jbe@115
|
2036 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@115
|
2037 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
|
jbe@115
|
2038 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
|
jbe@115
|
2039 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
|
jbe@115
|
2040 ));
|
jbe@115
|
2041
|
jbe@115
|
2042
|
jbe@122
|
2043 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
|
jbe@115
|
2044
|
jbe@115
|
2045 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
|
jbe@115
|
2046 FROM "current_draft"
|
jbe@115
|
2047 WHERE "initiative"."id" = "current_draft"."initiative_id"
|
jbe@115
|
2048 AND "initiative"."revoked" NOTNULL;
|
jbe@115
|
2049
|
jbe@115
|
2050 ALTER TABLE "initiative" ADD
|
jbe@115
|
2051 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
|
jbe@115
|
2052 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
|
jbe@115
|
2053
|
jbe@115
|
2054
|
jbe@122
|
2055 -- Fill "unit_id" column with default value where neccessary and add constraints:
|
jbe@115
|
2056
|
jbe@115
|
2057 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
|
jbe@115
|
2058
|
jbe@115
|
2059 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
|
jbe@115
|
2060 CHECK (
|
jbe@115
|
2061 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
|
jbe@115
|
2062 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
|
jbe@115
|
2063 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );
|
jbe@117
|
2064
|
jbe@117
|
2065
|
jbe@118
|
2066 -- Filling of "event" table with old (reconstructed) events:
|
jbe@118
|
2067
|
jbe@118
|
2068 DELETE FROM "event";
|
jbe@118
|
2069 SELECT setval('event_id_seq', 1, false);
|
jbe@118
|
2070
|
jbe@118
|
2071 INSERT INTO "event"
|
jbe@118
|
2072 ( "occurrence", "event", "member_id", "issue_id", "state",
|
jbe@118
|
2073 "initiative_id", "draft_id", "suggestion_id" )
|
jbe@118
|
2074 SELECT * FROM (
|
jbe@118
|
2075 SELECT * FROM (
|
jbe@118
|
2076 SELECT DISTINCT ON ("initiative"."id")
|
jbe@118
|
2077 "timeline"."occurrence",
|
jbe@118
|
2078 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
|
jbe@118
|
2079 'initiative_created_in_new_issue'::"event_type"
|
jbe@118
|
2080 ELSE
|
jbe@118
|
2081 'initiative_created_in_existing_issue'::"event_type"
|
jbe@118
|
2082 END,
|
jbe@118
|
2083 "draft"."author_id",
|
jbe@118
|
2084 "issue"."id",
|
jbe@118
|
2085 CASE
|
jbe@118
|
2086 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
|
jbe@118
|
2087 'admission'::"issue_state"
|
jbe@118
|
2088 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
|
jbe@118
|
2089 'discussion'::"issue_state"
|
jbe@118
|
2090 ELSE
|
jbe@118
|
2091 'verification'::"issue_state"
|
jbe@118
|
2092 END,
|
jbe@118
|
2093 "initiative"."id",
|
jbe@118
|
2094 "draft"."id",
|
jbe@118
|
2095 NULL::INT8
|
jbe@118
|
2096 FROM "timeline"
|
jbe@118
|
2097 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
|
jbe@118
|
2098 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@118
|
2099 LEFT JOIN "timeline" AS "issue_creation"
|
jbe@118
|
2100 ON "initiative"."issue_id" = "issue_creation"."issue_id"
|
jbe@118
|
2101 AND "issue_creation"."event" = 'issue_created'
|
jbe@118
|
2102 AND "timeline"."occurrence" = "issue_creation"."occurrence"
|
jbe@118
|
2103 JOIN "draft"
|
jbe@118
|
2104 ON "initiative"."id" = "draft"."initiative_id"
|
jbe@118
|
2105 WHERE "timeline"."event" = 'initiative_created'
|
jbe@118
|
2106 ORDER BY "initiative"."id", "draft"."id"
|
jbe@118
|
2107 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER
|
jbe@118
|
2108 UNION ALL
|
jbe@118
|
2109 SELECT
|
jbe@118
|
2110 "timeline"."occurrence",
|
jbe@118
|
2111 'issue_state_changed'::"event_type",
|
jbe@118
|
2112 NULL,
|
jbe@118
|
2113 "issue"."id",
|
jbe@118
|
2114 CASE
|
jbe@118
|
2115 WHEN "timeline"."event" IN (
|
jbe@118
|
2116 'issue_canceled',
|
jbe@118
|
2117 'issue_finished_without_voting',
|
jbe@118
|
2118 'issue_finished_after_voting'
|
jbe@118
|
2119 ) THEN
|
jbe@118
|
2120 "issue"."state"
|
jbe@118
|
2121 WHEN "timeline"."event" = 'issue_accepted' THEN
|
jbe@118
|
2122 'discussion'::"issue_state"
|
jbe@118
|
2123 WHEN "timeline"."event" = 'issue_half_frozen' THEN
|
jbe@118
|
2124 'verification'::"issue_state"
|
jbe@118
|
2125 WHEN "timeline"."event" = 'issue_voting_started' THEN
|
jbe@118
|
2126 'voting'::"issue_state"
|
jbe@118
|
2127 END,
|
jbe@118
|
2128 NULL,
|
jbe@118
|
2129 NULL,
|
jbe@118
|
2130 NULL
|
jbe@118
|
2131 FROM "timeline"
|
jbe@118
|
2132 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
|
jbe@118
|
2133 WHERE "timeline"."event" IN (
|
jbe@118
|
2134 'issue_canceled',
|
jbe@118
|
2135 'issue_accepted',
|
jbe@118
|
2136 'issue_half_frozen',
|
jbe@118
|
2137 'issue_finished_without_voting',
|
jbe@118
|
2138 'issue_voting_started',
|
jbe@118
|
2139 'issue_finished_after_voting' )
|
jbe@118
|
2140 UNION ALL
|
jbe@118
|
2141 SELECT
|
jbe@118
|
2142 "timeline"."occurrence",
|
jbe@118
|
2143 'initiative_revoked'::"event_type",
|
jbe@118
|
2144 "initiative"."revoked_by_member_id",
|
jbe@118
|
2145 "issue"."id",
|
jbe@118
|
2146 CASE
|
jbe@118
|
2147 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
|
jbe@118
|
2148 'admission'::"issue_state"
|
jbe@118
|
2149 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
|
jbe@118
|
2150 'discussion'::"issue_state"
|
jbe@118
|
2151 ELSE
|
jbe@118
|
2152 'verification'::"issue_state"
|
jbe@118
|
2153 END,
|
jbe@118
|
2154 "initiative"."id",
|
jbe@118
|
2155 "current_draft"."id",
|
jbe@118
|
2156 NULL
|
jbe@118
|
2157 FROM "timeline"
|
jbe@118
|
2158 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
|
jbe@118
|
2159 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@118
|
2160 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@118
|
2161 WHERE "timeline"."event" = 'initiative_revoked'
|
jbe@118
|
2162 UNION ALL
|
jbe@118
|
2163 SELECT
|
jbe@118
|
2164 "timeline"."occurrence",
|
jbe@118
|
2165 'new_draft_created'::"event_type",
|
jbe@118
|
2166 "draft"."author_id",
|
jbe@118
|
2167 "issue"."id",
|
jbe@118
|
2168 CASE
|
jbe@118
|
2169 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
|
jbe@118
|
2170 'admission'::"issue_state"
|
jbe@118
|
2171 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
|
jbe@118
|
2172 'discussion'::"issue_state"
|
jbe@118
|
2173 ELSE
|
jbe@118
|
2174 'verification'::"issue_state"
|
jbe@118
|
2175 END,
|
jbe@118
|
2176 "initiative"."id",
|
jbe@118
|
2177 "draft"."id",
|
jbe@118
|
2178 NULL
|
jbe@118
|
2179 FROM "timeline"
|
jbe@118
|
2180 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
|
jbe@118
|
2181 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
|
jbe@118
|
2182 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@118
|
2183 LEFT JOIN "timeline" AS "initiative_creation"
|
jbe@118
|
2184 ON "initiative"."id" = "initiative_creation"."initiative_id"
|
jbe@118
|
2185 AND "initiative_creation"."event" = 'initiative_created'
|
jbe@118
|
2186 AND "timeline"."occurrence" = "initiative_creation"."occurrence"
|
jbe@118
|
2187 WHERE "timeline"."event" = 'draft_created'
|
jbe@118
|
2188 AND "initiative_creation"."initiative_id" ISNULL
|
jbe@118
|
2189 UNION ALL
|
jbe@118
|
2190 SELECT
|
jbe@118
|
2191 "timeline"."occurrence",
|
jbe@118
|
2192 'suggestion_created'::"event_type",
|
jbe@118
|
2193 "suggestion"."author_id",
|
jbe@118
|
2194 "issue"."id",
|
jbe@118
|
2195 CASE
|
jbe@118
|
2196 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
|
jbe@118
|
2197 'admission'::"issue_state"
|
jbe@118
|
2198 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
|
jbe@118
|
2199 'discussion'::"issue_state"
|
jbe@118
|
2200 ELSE
|
jbe@118
|
2201 'verification'::"issue_state"
|
jbe@118
|
2202 END,
|
jbe@118
|
2203 "initiative"."id",
|
jbe@118
|
2204 NULL,
|
jbe@118
|
2205 "suggestion"."id"
|
jbe@118
|
2206 FROM "timeline"
|
jbe@118
|
2207 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
|
jbe@118
|
2208 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
|
jbe@118
|
2209 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@118
|
2210 WHERE "timeline"."event" = 'suggestion_created'
|
jbe@118
|
2211 ) AS "subquery"
|
jbe@118
|
2212 ORDER BY "occurrence";
|
jbe@118
|
2213
|
jbe@118
|
2214
|