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