liquid_feedback_core
view test.sql @ 302:548cec6b7a79
Better tie-breaking
| author | jbe |
|---|---|
| date | Sat Sep 29 23:41:37 2012 +0200 (2012-09-29) |
| parents | 44e489d77306 |
| children | e403f47525ce |
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 INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES
7 ('now', 'now', TRUE, 'user1', 'User #1'), -- id 1
8 ('now', 'now', TRUE, 'user2', 'User #2'), -- id 2
9 ('now', 'now', TRUE, 'user3', 'User #3'), -- id 3
10 ('now', 'now', TRUE, 'user4', 'User #4'), -- id 4
11 ('now', 'now', TRUE, 'user5', 'User #5'), -- id 5
12 ('now', 'now', TRUE, 'user6', 'User #6'), -- id 6
13 ('now', 'now', TRUE, 'user7', 'User #7'), -- id 7
14 ('now', 'now', TRUE, 'user8', 'User #8'), -- id 8
15 ('now', 'now', TRUE, 'user9', 'User #9'), -- id 9
16 ('now', 'now', TRUE, 'user10', 'User #10'), -- id 10
17 ('now', 'now', TRUE, 'user11', 'User #11'), -- id 11
18 ('now', 'now', TRUE, 'user12', 'User #12'), -- id 12
19 ('now', 'now', TRUE, 'user13', 'User #13'), -- id 13
20 ('now', 'now', TRUE, 'user14', 'User #14'), -- id 14
21 ('now', 'now', TRUE, 'user15', 'User #15'), -- id 15
22 ('now', 'now', TRUE, 'user16', 'User #16'), -- id 16
23 ('now', 'now', TRUE, 'user17', 'User #17'), -- id 17
24 ('now', 'now', TRUE, 'user18', 'User #18'), -- id 18
25 ('now', 'now', TRUE, 'user19', 'User #19'), -- id 19
26 ('now', 'now', TRUE, 'user20', 'User #20'), -- id 20
27 ('now', 'now', TRUE, 'user21', 'User #21'), -- id 21
28 ('now', 'now', TRUE, 'user22', 'User #22'), -- id 22
29 ('now', 'now', TRUE, 'user23', 'User #23'); -- id 23
31 -- set password to "login"
32 UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
34 INSERT INTO "policy" (
35 "index",
36 "name",
37 "admission_time",
38 "discussion_time",
39 "verification_time",
40 "voting_time",
41 "issue_quorum_num", "issue_quorum_den",
42 "initiative_quorum_num", "initiative_quorum_den",
43 "direct_majority_num", "direct_majority_den", "direct_majority_strict",
44 "no_reverse_beat_path", "no_multistage_majority",
45 "schulze_variant"
46 ) VALUES (
47 1,
48 'Default policy',
49 '1 hour', '1 hour', '1 hour', '1 hour',
50 25, 100,
51 20, 100,
52 1, 2, TRUE,
53 TRUE, FALSE,
54 'tie_breaking_with_negative_strength'::"schulze_variant" );
56 CREATE FUNCTION "time_warp"() RETURNS VOID
57 LANGUAGE 'plpgsql' VOLATILE AS $$
58 BEGIN
59 UPDATE "issue" SET
60 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
61 "created" = "created" - '1 hour 1 minute'::INTERVAL,
62 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
63 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
64 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
65 PERFORM "check_everything"();
66 RETURN;
67 END;
68 $$;
70 INSERT INTO "unit" ("name") VALUES ('Main');
72 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
73 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
74 FROM "member";
76 INSERT INTO "area" ("unit_id", "name") VALUES
77 (1, 'Area #1'), -- id 1
78 (1, 'Area #2'), -- id 2
79 (1, 'Area #3'), -- id 3
80 (1, 'Area #4'); -- id 4
82 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
83 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
85 INSERT INTO "membership" ("area_id", "member_id") VALUES
86 (1, 9),
87 (1, 19),
88 (2, 9),
89 (2, 10),
90 (2, 17),
91 (3, 9),
92 (3, 11),
93 (3, 12),
94 (3, 14),
95 (3, 20),
96 (3, 21),
97 (3, 22),
98 (4, 6),
99 (4, 9),
100 (4, 13),
101 (4, 22);
103 -- global delegations
104 INSERT INTO "delegation"
105 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
106 ( 1, 'unit', 1, 9),
107 ( 2, 'unit', 1, 11),
108 ( 3, 'unit', 1, 12),
109 ( 4, 'unit', 1, 13),
110 ( 5, 'unit', 1, 14),
111 ( 6, 'unit', 1, 7),
112 ( 7, 'unit', 1, 8),
113 ( 8, 'unit', 1, 6),
114 (10, 'unit', 1, 9),
115 (11, 'unit', 1, 9),
116 (12, 'unit', 1, 21),
117 (15, 'unit', 1, 10),
118 (16, 'unit', 1, 17),
119 (17, 'unit', 1, 19),
120 (18, 'unit', 1, 19),
121 (23, 'unit', 1, 22);
123 -- no delegations in area #1
124 INSERT INTO "delegation"
125 ("truster_id", "scope", "area_id", "trustee_id") VALUES
126 ( 1, 'area', 1, NULL),
127 ( 2, 'area', 1, NULL),
128 ( 3, 'area', 1, NULL),
129 ( 4, 'area', 1, NULL),
130 ( 5, 'area', 1, NULL),
131 ( 6, 'area', 1, NULL),
132 ( 7, 'area', 1, NULL),
133 ( 8, 'area', 1, NULL),
134 ( 9, 'area', 1, NULL),
135 (10, 'area', 1, NULL),
136 (11, 'area', 1, NULL),
137 (12, 'area', 1, NULL),
138 (13, 'area', 1, NULL),
139 (14, 'area', 1, NULL),
140 (15, 'area', 1, NULL),
141 (16, 'area', 1, NULL),
142 (17, 'area', 1, NULL),
143 (18, 'area', 1, NULL),
144 (19, 'area', 1, NULL),
145 (20, 'area', 1, NULL),
146 (21, 'area', 1, NULL),
147 (22, 'area', 1, NULL),
148 (23, 'area', 1, NULL);
150 -- delegations for topics
151 INSERT INTO "delegation"
152 ("area_id", "truster_id", "scope", "trustee_id") VALUES
153 --(1, 3, 'area', 17),
154 (2, 5, 'area', 10),
155 (2, 9, 'area', 10),
156 (3, 4, 'area', 14),
157 (3, 16, 'area', 20),
158 (3, 19, 'area', 20),
159 (4, 5, 'area', 13),
160 (4, 12, 'area', 22);
162 INSERT INTO "issue" ("area_id", "policy_id") VALUES
163 (3, 1); -- id 1
165 INSERT INTO "initiative" ("issue_id", "name") VALUES
166 (1, 'Initiative #1'), -- id 1
167 (1, 'Initiative #2'), -- id 2
168 (1, 'Initiative #3'), -- id 3
169 (1, 'Initiative #4'), -- id 4
170 (1, 'Initiative #5'), -- id 5
171 (1, 'Initiative #6'), -- id 6
172 (1, 'Initiative #7'); -- id 7
174 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
175 (1, 17, 'Lorem ipsum...'), -- id 1
176 (2, 20, 'Lorem ipsum...'), -- id 2
177 (3, 20, 'Lorem ipsum...'), -- id 3
178 (4, 20, 'Lorem ipsum...'), -- id 4
179 (5, 14, 'Lorem ipsum...'), -- id 5
180 (6, 11, 'Lorem ipsum...'), -- id 6
181 (7, 12, 'Lorem ipsum...'); -- id 7
183 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
184 (1, 17),
185 (1, 19),
186 (2, 20),
187 (3, 20),
188 (4, 20),
189 (5, 14),
190 (6, 11),
191 (7, 12);
193 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
194 ( 7, 4, 4),
195 ( 8, 2, 2),
196 (11, 6, 6),
197 (12, 7, 7),
198 (14, 1, 1),
199 (14, 2, 2),
200 (14, 3, 3),
201 (14, 4, 4),
202 (14, 5, 5),
203 (14, 6, 6),
204 (14, 7, 7),
205 (17, 1, 1),
206 (17, 3, 3),
207 (19, 1, 1),
208 (19, 2, 2),
209 (20, 1, 1),
210 (20, 2, 2),
211 (20, 3, 3),
212 (20, 4, 4),
213 (20, 5, 5);
215 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
216 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
217 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
218 (14, 1, 2, FALSE);
219 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
220 (19, 1, 2, FALSE);
222 INSERT INTO "issue" ("area_id", "policy_id") VALUES
223 (4, 1); -- id 2
225 INSERT INTO "initiative" ("issue_id", "name") VALUES
226 (2, 'Initiative A'), -- id 8
227 (2, 'Initiative B'), -- id 9
228 (2, 'Initiative C'), -- id 10
229 (2, 'Initiative D'); -- id 11
231 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
232 ( 8, 1, 'Lorem ipsum...'), -- id 8
233 ( 9, 2, 'Lorem ipsum...'), -- id 9
234 (10, 3, 'Lorem ipsum...'), -- id 10
235 (11, 4, 'Lorem ipsum...'); -- id 11
237 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
238 ( 8, 1),
239 ( 9, 2),
240 (10, 3),
241 (11, 4);
243 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
244 (1, 8, 8),
245 (1, 9, 9),
246 (1, 10, 10),
247 (1, 11, 11),
248 (2, 8, 8),
249 (2, 9, 9),
250 (2, 10, 10),
251 (2, 11, 11),
252 (3, 8, 8),
253 (3, 9, 9),
254 (3, 10, 10),
255 (3, 11, 11),
256 (4, 8, 8),
257 (4, 9, 9),
258 (4, 10, 10),
259 (4, 11, 11),
260 (5, 8, 8),
261 (5, 9, 9),
262 (5, 10, 10),
263 (5, 11, 11),
264 (6, 8, 8),
265 (6, 9, 9),
266 (6, 10, 10),
267 (6, 11, 11);
269 INSERT INTO "issue" ("area_id", "policy_id") VALUES
270 (1, 1); -- id 3
272 INSERT INTO "initiative" ("issue_id", "name") VALUES
273 (3, 'First initiative'), -- id 12
274 (3, 'Second initiative'); -- id 13
276 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
277 (12, 1, 'Lorem ipsum...'), -- id 12
278 (13, 2, 'Lorem ipsum...'); -- id 13
280 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
281 (12, 1),
282 (13, 2);
284 INSERT INTO "supporter" ("initiative_id", "member_id") VALUES
285 (12, 1),
286 (13, 2);
288 SELECT "time_warp"();
289 SELECT "time_warp"();
290 SELECT "time_warp"();
292 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
293 ( 8, 1),
294 ( 9, 1),
295 (11, 1),
296 (12, 1),
297 (14, 1),
298 (19, 1),
299 (20, 1),
300 (21, 1);
302 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
303 ( 8, 1, 1, 1),
304 ( 8, 1, 2, 1),
305 ( 8, 1, 3, 1),
306 ( 8, 1, 4, 1),
307 ( 8, 1, 5, 1),
308 ( 8, 1, 6, -1),
309 ( 8, 1, 7, -1),
310 ( 9, 1, 1, -2),
311 ( 9, 1, 2, -3),
312 ( 9, 1, 3, -2),
313 ( 9, 1, 4, -2),
314 ( 9, 1, 5, -2),
315 ( 9, 1, 6, -1),
316 (11, 1, 1, -1),
317 (11, 1, 2, -1),
318 (11, 1, 3, -1),
319 (11, 1, 4, -1),
320 (11, 1, 5, -1),
321 (11, 1, 6, 2),
322 (11, 1, 7, 1),
323 (12, 1, 1, -1),
324 (12, 1, 3, -1),
325 (12, 1, 4, -1),
326 (12, 1, 5, -1),
327 (12, 1, 6, -2),
328 (12, 1, 7, 1),
329 (14, 1, 1, 1),
330 (14, 1, 2, 3),
331 (14, 1, 3, 1),
332 (14, 1, 4, 2),
333 (14, 1, 5, 1),
334 (14, 1, 6, 1),
335 (14, 1, 7, 1),
336 (19, 1, 1, 3),
337 (19, 1, 2, 4),
338 (19, 1, 3, 2),
339 (19, 1, 4, 2),
340 (19, 1, 5, 2),
341 (19, 1, 7, 1),
342 (20, 1, 1, 1),
343 (20, 1, 2, 2),
344 (20, 1, 3, 1),
345 (20, 1, 4, 1),
346 (20, 1, 5, 1),
347 (21, 1, 5, -1);
349 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
350 ( 1, 2),
351 ( 2, 2),
352 ( 3, 2),
353 ( 4, 2),
354 ( 5, 2),
355 ( 6, 2),
356 ( 7, 2),
357 ( 8, 2),
358 ( 9, 2),
359 (10, 2),
360 (11, 2),
361 (12, 2),
362 (13, 2),
363 (14, 2),
364 (15, 2),
365 (16, 2),
366 (17, 2),
367 (18, 2),
368 (19, 2),
369 (20, 2);
371 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
372 ( 1, 2, 8, 3),
373 ( 1, 2, 9, 4),
374 ( 1, 2, 10, 2),
375 ( 1, 2, 11, 1),
376 ( 2, 2, 8, 3),
377 ( 2, 2, 9, 4),
378 ( 2, 2, 10, 2),
379 ( 2, 2, 11, 1),
380 ( 3, 2, 8, 4),
381 ( 3, 2, 9, 3),
382 ( 3, 2, 10, 2),
383 ( 3, 2, 11, 1),
384 ( 4, 2, 8, 4),
385 ( 4, 2, 9, 3),
386 ( 4, 2, 10, 2),
387 ( 4, 2, 11, 1),
388 ( 5, 2, 8, 4),
389 ( 5, 2, 9, 3),
390 ( 5, 2, 10, 2),
391 ( 5, 2, 11, 1),
392 ( 6, 2, 8, 4),
393 ( 6, 2, 9, 3),
394 ( 6, 2, 10, 2),
395 ( 6, 2, 11, 1),
396 ( 7, 2, 8, 4),
397 ( 7, 2, 9, 3),
398 ( 7, 2, 10, 2),
399 ( 7, 2, 11, 1),
400 ( 8, 2, 8, 4),
401 ( 8, 2, 9, 3),
402 ( 8, 2, 10, 2),
403 ( 8, 2, 11, 1),
404 ( 9, 2, 8, -1),
405 ( 9, 2, 9, 1),
406 ( 9, 2, 10, 3),
407 ( 9, 2, 11, 2),
408 (10, 2, 8, -1),
409 (10, 2, 9, 1),
410 (10, 2, 10, 3),
411 (10, 2, 11, 2),
412 (11, 2, 8, -1),
413 (11, 2, 9, 1),
414 (11, 2, 10, 3),
415 (11, 2, 11, 2),
416 (12, 2, 8, -1),
417 (12, 2, 9, 1),
418 (12, 2, 10, 3),
419 (12, 2, 11, 2),
420 (13, 2, 8, -1),
421 (13, 2, 9, 1),
422 (13, 2, 10, 3),
423 (13, 2, 11, 2),
424 (14, 2, 8, -1),
425 (14, 2, 9, 1),
426 (14, 2, 10, 3),
427 (14, 2, 11, 2),
428 (15, 2, 8, -1),
429 (15, 2, 9, -3),
430 (15, 2, 10, -4),
431 (15, 2, 11, -2),
432 (16, 2, 8, -1),
433 (16, 2, 9, -3),
434 (16, 2, 10, -4),
435 (16, 2, 11, -2),
436 (17, 2, 8, -1),
437 (17, 2, 9, -3),
438 (17, 2, 10, -4),
439 (17, 2, 11, -2),
440 (18, 2, 8, -1),
441 (18, 2, 9, 1),
442 (18, 2, 10, -2),
443 (18, 2, 11, 2),
444 (19, 2, 8, -1),
445 (19, 2, 9, 1),
446 (19, 2, 10, -2),
447 (19, 2, 11, 2),
448 (20, 2, 8, 1),
449 (20, 2, 9, 2),
450 (20, 2, 10, -1),
451 (20, 2, 11, 3);
453 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
454 ( 1, 3),
455 ( 2, 3),
456 ( 3, 3),
457 ( 4, 3),
458 ( 5, 3);
460 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
461 (1, 3, 12, 1),
462 (1, 3, 13, 1),
463 (2, 3, 12, 1),
464 (2, 3, 13, 1),
465 (3, 3, 12, 0),
466 (3, 3, 13, 1),
467 (4, 3, 12, 0),
468 (4, 3, 13, -1),
469 (5, 3, 12, -1),
470 (5, 3, 13, -1);
472 SELECT "time_warp"();
474 DROP FUNCTION "time_warp"();
476 -- Test policies that help with testing specific frontend parts
478 INSERT INTO "policy" (
479 "index",
480 "active",
481 "name",
482 "description",
483 "admission_time",
484 "discussion_time",
485 "verification_time",
486 "voting_time",
487 "issue_quorum_num",
488 "issue_quorum_den",
489 "initiative_quorum_num",
490 "initiative_quorum_den"
491 ) VALUES (
492 1,
493 TRUE,
494 'Test New',
495 DEFAULT,
496 '2 days',
497 '1 second',
498 '1 second',
499 '1 second',
500 0, 100,
501 0, 100
502 ), (
503 1,
504 TRUE,
505 'Test Accept',
506 DEFAULT,
507 '1 second',
508 '2 days',
509 '1 second',
510 '1 second',
511 0, 100,
512 0, 100
513 ), (
514 1,
515 TRUE,
516 'Test Frozen',
517 DEFAULT,
518 '1 second',
519 '5 minutes',
520 '2 days',
521 '1 second',
522 0, 100,
523 0, 100
524 ), (
525 1,
526 TRUE,
527 'Test Voting',
528 DEFAULT,
529 '1 second',
530 '5 minutes',
531 '1 second',
532 '2 days',
533 0, 100,
534 0, 100
535 );
536 END;