liquid_feedback_core
view test.sql @ 519:003b4cc8e9ef
Merged experimental work on alternative mechanism to the first (issue) quorum
| author | jbe |
|---|---|
| date | Tue May 03 20:35:30 2016 +0200 (2016-05-03) |
| parents | fae00a5c1c71 |
| children | 3e28fd842354 |
line source
1 -- NOTE: This file requires that sequence generators have not been used.
2 -- (All new rows need to start with id '1'.)
4 BEGIN;
6 -- set transaction isolation level to be able to call "check_everything"() function
7 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
9 INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES
10 ('now', 'now', TRUE, 'user1', 'User #1'), -- id 1
11 ('now', 'now', TRUE, 'user2', 'User #2'), -- id 2
12 ('now', 'now', TRUE, 'user3', 'User #3'), -- id 3
13 ('now', 'now', TRUE, 'user4', 'User #4'), -- id 4
14 ('now', 'now', TRUE, 'user5', 'User #5'), -- id 5
15 ('now', 'now', TRUE, 'user6', 'User #6'), -- id 6
16 ('now', 'now', TRUE, 'user7', 'User #7'), -- id 7
17 ('now', 'now', TRUE, 'user8', 'User #8'), -- id 8
18 ('now', 'now', TRUE, 'user9', 'User #9'), -- id 9
19 ('now', 'now', TRUE, 'user10', 'User #10'), -- id 10
20 ('now', 'now', TRUE, 'user11', 'User #11'), -- id 11
21 ('now', 'now', TRUE, 'user12', 'User #12'), -- id 12
22 ('now', 'now', TRUE, 'user13', 'User #13'), -- id 13
23 ('now', 'now', TRUE, 'user14', 'User #14'), -- id 14
24 ('now', 'now', TRUE, 'user15', 'User #15'), -- id 15
25 ('now', 'now', TRUE, 'user16', 'User #16'), -- id 16
26 ('now', 'now', TRUE, 'user17', 'User #17'), -- id 17
27 ('now', 'now', TRUE, 'user18', 'User #18'), -- id 18
28 ('now', 'now', TRUE, 'user19', 'User #19'), -- id 19
29 ('now', 'now', TRUE, 'user20', 'User #20'), -- id 20
30 ('now', 'now', TRUE, 'user21', 'User #21'), -- id 21
31 ('now', 'now', TRUE, 'user22', 'User #22'), -- id 22
32 ('now', 'now', TRUE, 'user23', 'User #23'); -- id 23
34 -- set password to "login"
35 UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
37 INSERT INTO "policy" (
38 "index",
39 "name",
40 "min_admission_time",
41 "max_admission_time",
42 "discussion_time",
43 "verification_time",
44 "voting_time",
45 "issue_quorum",
46 "initiative_quorum_num", "initiative_quorum_den",
47 "direct_majority_num", "direct_majority_den", "direct_majority_strict",
48 "no_reverse_beat_path", "no_multistage_majority"
49 ) VALUES (
50 1,
51 'Default policy',
52 '0', '1 hour', '1 hour', '1 hour', '1 hour',
53 3,
54 20, 100,
55 1, 2, TRUE,
56 TRUE, FALSE );
58 CREATE FUNCTION "time_warp"() RETURNS VOID
59 LANGUAGE 'plpgsql' VOLATILE AS $$
60 BEGIN
61 UPDATE "issue" SET
62 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
63 "created" = "created" - '1 hour 1 minute'::INTERVAL,
64 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
65 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
66 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
67 PERFORM "check_everything"();
68 RETURN;
69 END;
70 $$;
72 INSERT INTO "unit" ("name") VALUES ('Main');
74 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
75 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
76 FROM "member";
78 INSERT INTO "area" ("unit_id", "name") VALUES
79 (1, 'Area #1'), -- id 1
80 (1, 'Area #2'), -- id 2
81 (1, 'Area #3'), -- id 3
82 (1, 'Area #4'); -- id 4
84 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
85 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
87 INSERT INTO "membership" ("area_id", "member_id") VALUES
88 (1, 9),
89 (1, 19),
90 (2, 9),
91 (2, 10),
92 (2, 17),
93 (3, 9),
94 (3, 11),
95 (3, 12),
96 (3, 14),
97 (3, 20),
98 (3, 21),
99 (3, 22),
100 (4, 6),
101 (4, 9),
102 (4, 13),
103 (4, 22);
105 -- global delegations
106 INSERT INTO "delegation"
107 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
108 ( 1, 'unit', 1, 9),
109 ( 2, 'unit', 1, 11),
110 ( 3, 'unit', 1, 12),
111 ( 4, 'unit', 1, 13),
112 ( 5, 'unit', 1, 14),
113 ( 6, 'unit', 1, 7),
114 ( 7, 'unit', 1, 8),
115 ( 8, 'unit', 1, 6),
116 (10, 'unit', 1, 9),
117 (11, 'unit', 1, 9),
118 (12, 'unit', 1, 21),
119 (15, 'unit', 1, 10),
120 (16, 'unit', 1, 17),
121 (17, 'unit', 1, 19),
122 (18, 'unit', 1, 19),
123 (23, 'unit', 1, 22);
125 -- delegations for topics
126 INSERT INTO "delegation"
127 ("area_id", "truster_id", "scope", "trustee_id") VALUES
128 (1, 3, 'area', 17),
129 (2, 5, 'area', 10),
130 (2, 9, 'area', 10),
131 (3, 4, 'area', 14),
132 (3, 16, 'area', 20),
133 (3, 19, 'area', 20),
134 (4, 5, 'area', 13),
135 (4, 12, 'area', 22);
137 INSERT INTO "issue" ("area_id", "policy_id") VALUES
138 (3, 1); -- id 1
140 INSERT INTO "initiative" ("issue_id", "name") VALUES
141 (1, 'Initiative #1'), -- id 1
142 (1, 'Initiative #2'), -- id 2
143 (1, 'Initiative #3'), -- id 3
144 (1, 'Initiative #4'), -- id 4
145 (1, 'Initiative #5'), -- id 5
146 (1, 'Initiative #6'), -- id 6
147 (1, 'Initiative #7'); -- id 7
149 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
150 (1, 17, 'Lorem ipsum...'), -- id 1
151 (2, 20, 'Lorem ipsum...'), -- id 2
152 (3, 20, 'Lorem ipsum...'), -- id 3
153 (4, 20, 'Lorem ipsum...'), -- id 4
154 (5, 14, 'Lorem ipsum...'), -- id 5
155 (6, 11, 'Lorem ipsum...'), -- id 6
156 (7, 12, 'Lorem ipsum...'); -- id 7
158 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
159 (1, 17),
160 (1, 19),
161 (2, 20),
162 (3, 20),
163 (4, 20),
164 (5, 14),
165 (6, 11),
166 (7, 12);
168 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
169 ( 7, 4, 4),
170 ( 8, 2, 2),
171 (11, 6, 6),
172 (12, 7, 7),
173 (14, 1, 1),
174 (14, 2, 2),
175 (14, 3, 3),
176 (14, 4, 4),
177 (14, 5, 5),
178 (14, 6, 6),
179 (14, 7, 7),
180 (17, 1, 1),
181 (17, 3, 3),
182 (19, 1, 1),
183 (19, 2, 2),
184 (20, 1, 1),
185 (20, 2, 2),
186 (20, 3, 3),
187 (20, 4, 4),
188 (20, 5, 5);
190 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
191 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
192 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
193 (14, 1, 2, FALSE);
194 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
195 (19, 1, 2, FALSE);
197 INSERT INTO "issue" ("area_id", "policy_id") VALUES
198 (4, 1); -- id 2
200 INSERT INTO "initiative" ("issue_id", "name") VALUES
201 (2, 'Initiative A'), -- id 8
202 (2, 'Initiative B'), -- id 9
203 (2, 'Initiative C'), -- id 10
204 (2, 'Initiative D'); -- id 11
206 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
207 ( 8, 1, 'Lorem ipsum...'), -- id 8
208 ( 9, 2, 'Lorem ipsum...'), -- id 9
209 (10, 3, 'Lorem ipsum...'), -- id 10
210 (11, 4, 'Lorem ipsum...'); -- id 11
212 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
213 ( 8, 1),
214 ( 9, 2),
215 (10, 3),
216 (11, 4);
218 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
219 (1, 8, 8),
220 (1, 9, 9),
221 (1, 10, 10),
222 (1, 11, 11),
223 (2, 8, 8),
224 (2, 9, 9),
225 (2, 10, 10),
226 (2, 11, 11),
227 (3, 8, 8),
228 (3, 9, 9),
229 (3, 10, 10),
230 (3, 11, 11),
231 (4, 8, 8),
232 (4, 9, 9),
233 (4, 10, 10),
234 (4, 11, 11),
235 (5, 8, 8),
236 (5, 9, 9),
237 (5, 10, 10),
238 (5, 11, 11),
239 (6, 8, 8),
240 (6, 9, 9),
241 (6, 10, 10),
242 (6, 11, 11);
244 SELECT "time_warp"();
245 SELECT "time_warp"();
246 SELECT "time_warp"();
248 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
249 ( 8, 1),
250 ( 9, 1),
251 (11, 1),
252 (12, 1),
253 (14, 1),
254 (19, 1),
255 (20, 1),
256 (21, 1);
258 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
259 ( 8, 1, 1, 1),
260 ( 8, 1, 2, 1),
261 ( 8, 1, 3, 1),
262 ( 8, 1, 4, 1),
263 ( 8, 1, 5, 1),
264 ( 8, 1, 6, -1),
265 ( 8, 1, 7, -1),
266 ( 9, 1, 1, -2),
267 ( 9, 1, 2, -3),
268 ( 9, 1, 3, -2),
269 ( 9, 1, 4, -2),
270 ( 9, 1, 5, -2),
271 ( 9, 1, 6, -1),
272 (11, 1, 1, -1),
273 (11, 1, 2, -1),
274 (11, 1, 3, -1),
275 (11, 1, 4, -1),
276 (11, 1, 5, -1),
277 (11, 1, 6, 2),
278 (11, 1, 7, 1),
279 (12, 1, 1, -1),
280 (12, 1, 3, -1),
281 (12, 1, 4, -1),
282 (12, 1, 5, -1),
283 (12, 1, 6, -2),
284 (12, 1, 7, 1),
285 (14, 1, 1, 1),
286 (14, 1, 2, 3),
287 (14, 1, 3, 1),
288 (14, 1, 4, 2),
289 (14, 1, 5, 1),
290 (14, 1, 6, 1),
291 (14, 1, 7, 1),
292 (19, 1, 1, 3),
293 (19, 1, 2, 4),
294 (19, 1, 3, 2),
295 (19, 1, 4, 2),
296 (19, 1, 5, 2),
297 (19, 1, 7, 1),
298 (20, 1, 1, 1),
299 (20, 1, 2, 2),
300 (20, 1, 3, 1),
301 (20, 1, 4, 1),
302 (20, 1, 5, 1),
303 (21, 1, 5, -1);
305 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
306 ( 1, 2),
307 ( 2, 2),
308 ( 3, 2),
309 ( 4, 2),
310 ( 5, 2),
311 ( 6, 2),
312 ( 7, 2),
313 ( 8, 2),
314 ( 9, 2),
315 (10, 2),
316 (11, 2),
317 (12, 2),
318 (13, 2),
319 (14, 2),
320 (15, 2),
321 (16, 2),
322 (17, 2),
323 (18, 2),
324 (19, 2),
325 (20, 2);
327 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
328 ( 1, 2, 8, 3),
329 ( 1, 2, 9, 4),
330 ( 1, 2, 10, 2),
331 ( 1, 2, 11, 1),
332 ( 2, 2, 8, 3),
333 ( 2, 2, 9, 4),
334 ( 2, 2, 10, 2),
335 ( 2, 2, 11, 1),
336 ( 3, 2, 8, 4),
337 ( 3, 2, 9, 3),
338 ( 3, 2, 10, 2),
339 ( 3, 2, 11, 1),
340 ( 4, 2, 8, 4),
341 ( 4, 2, 9, 3),
342 ( 4, 2, 10, 2),
343 ( 4, 2, 11, 1),
344 ( 5, 2, 8, 4),
345 ( 5, 2, 9, 3),
346 ( 5, 2, 10, 2),
347 ( 5, 2, 11, 1),
348 ( 6, 2, 8, 4),
349 ( 6, 2, 9, 3),
350 ( 6, 2, 10, 2),
351 ( 6, 2, 11, 1),
352 ( 7, 2, 8, 4),
353 ( 7, 2, 9, 3),
354 ( 7, 2, 10, 2),
355 ( 7, 2, 11, 1),
356 ( 8, 2, 8, 4),
357 ( 8, 2, 9, 3),
358 ( 8, 2, 10, 2),
359 ( 8, 2, 11, 1),
360 ( 9, 2, 8, -1),
361 ( 9, 2, 9, 1),
362 ( 9, 2, 10, 3),
363 ( 9, 2, 11, 2),
364 (10, 2, 8, -1),
365 (10, 2, 9, 1),
366 (10, 2, 10, 3),
367 (10, 2, 11, 2),
368 (11, 2, 8, -1),
369 (11, 2, 9, 1),
370 (11, 2, 10, 3),
371 (11, 2, 11, 2),
372 (12, 2, 8, -1),
373 (12, 2, 9, 1),
374 (12, 2, 10, 3),
375 (12, 2, 11, 2),
376 (13, 2, 8, -1),
377 (13, 2, 9, 1),
378 (13, 2, 10, 3),
379 (13, 2, 11, 2),
380 (14, 2, 8, -1),
381 (14, 2, 9, 1),
382 (14, 2, 10, 3),
383 (14, 2, 11, 2),
384 (15, 2, 8, -1),
385 (15, 2, 9, -3),
386 (15, 2, 10, -4),
387 (15, 2, 11, -2),
388 (16, 2, 8, -1),
389 (16, 2, 9, -3),
390 (16, 2, 10, -4),
391 (16, 2, 11, -2),
392 (17, 2, 8, -1),
393 (17, 2, 9, -3),
394 (17, 2, 10, -4),
395 (17, 2, 11, -2),
396 (18, 2, 8, -1),
397 (18, 2, 9, 1),
398 (18, 2, 10, -2),
399 (18, 2, 11, 2),
400 (19, 2, 8, -1),
401 (19, 2, 9, 1),
402 (19, 2, 10, -2),
403 (19, 2, 11, 2),
404 (20, 2, 8, 1),
405 (20, 2, 9, 2),
406 (20, 2, 10, -1),
407 (20, 2, 11, 3);
409 SELECT "time_warp"();
411 DROP FUNCTION "time_warp"();
413 -- Test policies that help with testing specific frontend parts
415 INSERT INTO "policy" (
416 "index",
417 "active",
418 "name",
419 "description",
420 "min_admission_time",
421 "max_admission_time",
422 "discussion_time",
423 "verification_time",
424 "voting_time",
425 "issue_quorum",
426 "initiative_quorum_num",
427 "initiative_quorum_den"
428 ) VALUES (
429 1,
430 TRUE,
431 'Test New',
432 DEFAULT,
433 '0',
434 '2 days',
435 '1 second',
436 '1 second',
437 '1 second',
438 1,
439 0, 100
440 ), (
441 1,
442 TRUE,
443 'Test Accept',
444 DEFAULT,
445 '0',
446 '1 second',
447 '2 days',
448 '1 second',
449 '1 second',
450 1,
451 0, 100
452 ), (
453 1,
454 TRUE,
455 'Test Frozen',
456 DEFAULT,
457 '0',
458 '1 second',
459 '5 minutes',
460 '2 days',
461 '1 second',
462 1,
463 0, 100
464 ), (
465 1,
466 TRUE,
467 'Test Voting',
468 DEFAULT,
469 '0',
470 '1 second',
471 '5 minutes',
472 '1 second',
473 '2 days',
474 1,
475 0, 100
476 );
478 END;