liquid_feedback_core
view test.sql @ 457:ca21a3f49e4c
Draft for an alternative mechanism to the first (issue) quorum
| author | jbe |
|---|---|
| date | Fri Dec 11 23:44:02 2015 +0100 (2015-12-11) |
| parents | 78e9a2071b0c |
| children | fae00a5c1c71 |
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 "initiative_quorum_num", "initiative_quorum_den",
46 "direct_majority_num", "direct_majority_den", "direct_majority_strict",
47 "no_reverse_beat_path", "no_multistage_majority"
48 ) VALUES (
49 1,
50 'Default policy',
51 '0', '1 hour', '1 hour', '1 hour', '1 hour',
52 20, 100,
53 1, 2, TRUE,
54 TRUE, FALSE );
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 -- delegations for topics
124 INSERT INTO "delegation"
125 ("area_id", "truster_id", "scope", "trustee_id") VALUES
126 (1, 3, 'area', 17),
127 (2, 5, 'area', 10),
128 (2, 9, 'area', 10),
129 (3, 4, 'area', 14),
130 (3, 16, 'area', 20),
131 (3, 19, 'area', 20),
132 (4, 5, 'area', 13),
133 (4, 12, 'area', 22);
135 INSERT INTO "issue" ("area_id", "policy_id") VALUES
136 (3, 1); -- id 1
138 INSERT INTO "initiative" ("issue_id", "name") VALUES
139 (1, 'Initiative #1'), -- id 1
140 (1, 'Initiative #2'), -- id 2
141 (1, 'Initiative #3'), -- id 3
142 (1, 'Initiative #4'), -- id 4
143 (1, 'Initiative #5'), -- id 5
144 (1, 'Initiative #6'), -- id 6
145 (1, 'Initiative #7'); -- id 7
147 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
148 (1, 17, 'Lorem ipsum...'), -- id 1
149 (2, 20, 'Lorem ipsum...'), -- id 2
150 (3, 20, 'Lorem ipsum...'), -- id 3
151 (4, 20, 'Lorem ipsum...'), -- id 4
152 (5, 14, 'Lorem ipsum...'), -- id 5
153 (6, 11, 'Lorem ipsum...'), -- id 6
154 (7, 12, 'Lorem ipsum...'); -- id 7
156 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
157 (1, 17),
158 (1, 19),
159 (2, 20),
160 (3, 20),
161 (4, 20),
162 (5, 14),
163 (6, 11),
164 (7, 12);
166 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
167 ( 7, 4, 4),
168 ( 8, 2, 2),
169 (11, 6, 6),
170 (12, 7, 7),
171 (14, 1, 1),
172 (14, 2, 2),
173 (14, 3, 3),
174 (14, 4, 4),
175 (14, 5, 5),
176 (14, 6, 6),
177 (14, 7, 7),
178 (17, 1, 1),
179 (17, 3, 3),
180 (19, 1, 1),
181 (19, 2, 2),
182 (20, 1, 1),
183 (20, 2, 2),
184 (20, 3, 3),
185 (20, 4, 4),
186 (20, 5, 5);
188 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
189 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
190 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
191 (14, 1, 2, FALSE);
192 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
193 (19, 1, 2, FALSE);
195 INSERT INTO "issue" ("area_id", "policy_id") VALUES
196 (4, 1); -- id 2
198 INSERT INTO "initiative" ("issue_id", "name") VALUES
199 (2, 'Initiative A'), -- id 8
200 (2, 'Initiative B'), -- id 9
201 (2, 'Initiative C'), -- id 10
202 (2, 'Initiative D'); -- id 11
204 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
205 ( 8, 1, 'Lorem ipsum...'), -- id 8
206 ( 9, 2, 'Lorem ipsum...'), -- id 9
207 (10, 3, 'Lorem ipsum...'), -- id 10
208 (11, 4, 'Lorem ipsum...'); -- id 11
210 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
211 ( 8, 1),
212 ( 9, 2),
213 (10, 3),
214 (11, 4);
216 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
217 (1, 8, 8),
218 (1, 9, 9),
219 (1, 10, 10),
220 (1, 11, 11),
221 (2, 8, 8),
222 (2, 9, 9),
223 (2, 10, 10),
224 (2, 11, 11),
225 (3, 8, 8),
226 (3, 9, 9),
227 (3, 10, 10),
228 (3, 11, 11),
229 (4, 8, 8),
230 (4, 9, 9),
231 (4, 10, 10),
232 (4, 11, 11),
233 (5, 8, 8),
234 (5, 9, 9),
235 (5, 10, 10),
236 (5, 11, 11),
237 (6, 8, 8),
238 (6, 9, 9),
239 (6, 10, 10),
240 (6, 11, 11);
242 SELECT "time_warp"();
243 SELECT "time_warp"();
244 SELECT "time_warp"();
246 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
247 ( 8, 1),
248 ( 9, 1),
249 (11, 1),
250 (12, 1),
251 (14, 1),
252 (19, 1),
253 (20, 1),
254 (21, 1);
256 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
257 ( 8, 1, 1, 1),
258 ( 8, 1, 2, 1),
259 ( 8, 1, 3, 1),
260 ( 8, 1, 4, 1),
261 ( 8, 1, 5, 1),
262 ( 8, 1, 6, -1),
263 ( 8, 1, 7, -1),
264 ( 9, 1, 1, -2),
265 ( 9, 1, 2, -3),
266 ( 9, 1, 3, -2),
267 ( 9, 1, 4, -2),
268 ( 9, 1, 5, -2),
269 ( 9, 1, 6, -1),
270 (11, 1, 1, -1),
271 (11, 1, 2, -1),
272 (11, 1, 3, -1),
273 (11, 1, 4, -1),
274 (11, 1, 5, -1),
275 (11, 1, 6, 2),
276 (11, 1, 7, 1),
277 (12, 1, 1, -1),
278 (12, 1, 3, -1),
279 (12, 1, 4, -1),
280 (12, 1, 5, -1),
281 (12, 1, 6, -2),
282 (12, 1, 7, 1),
283 (14, 1, 1, 1),
284 (14, 1, 2, 3),
285 (14, 1, 3, 1),
286 (14, 1, 4, 2),
287 (14, 1, 5, 1),
288 (14, 1, 6, 1),
289 (14, 1, 7, 1),
290 (19, 1, 1, 3),
291 (19, 1, 2, 4),
292 (19, 1, 3, 2),
293 (19, 1, 4, 2),
294 (19, 1, 5, 2),
295 (19, 1, 7, 1),
296 (20, 1, 1, 1),
297 (20, 1, 2, 2),
298 (20, 1, 3, 1),
299 (20, 1, 4, 1),
300 (20, 1, 5, 1),
301 (21, 1, 5, -1);
303 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
304 ( 1, 2),
305 ( 2, 2),
306 ( 3, 2),
307 ( 4, 2),
308 ( 5, 2),
309 ( 6, 2),
310 ( 7, 2),
311 ( 8, 2),
312 ( 9, 2),
313 (10, 2),
314 (11, 2),
315 (12, 2),
316 (13, 2),
317 (14, 2),
318 (15, 2),
319 (16, 2),
320 (17, 2),
321 (18, 2),
322 (19, 2),
323 (20, 2);
325 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
326 ( 1, 2, 8, 3),
327 ( 1, 2, 9, 4),
328 ( 1, 2, 10, 2),
329 ( 1, 2, 11, 1),
330 ( 2, 2, 8, 3),
331 ( 2, 2, 9, 4),
332 ( 2, 2, 10, 2),
333 ( 2, 2, 11, 1),
334 ( 3, 2, 8, 4),
335 ( 3, 2, 9, 3),
336 ( 3, 2, 10, 2),
337 ( 3, 2, 11, 1),
338 ( 4, 2, 8, 4),
339 ( 4, 2, 9, 3),
340 ( 4, 2, 10, 2),
341 ( 4, 2, 11, 1),
342 ( 5, 2, 8, 4),
343 ( 5, 2, 9, 3),
344 ( 5, 2, 10, 2),
345 ( 5, 2, 11, 1),
346 ( 6, 2, 8, 4),
347 ( 6, 2, 9, 3),
348 ( 6, 2, 10, 2),
349 ( 6, 2, 11, 1),
350 ( 7, 2, 8, 4),
351 ( 7, 2, 9, 3),
352 ( 7, 2, 10, 2),
353 ( 7, 2, 11, 1),
354 ( 8, 2, 8, 4),
355 ( 8, 2, 9, 3),
356 ( 8, 2, 10, 2),
357 ( 8, 2, 11, 1),
358 ( 9, 2, 8, -1),
359 ( 9, 2, 9, 1),
360 ( 9, 2, 10, 3),
361 ( 9, 2, 11, 2),
362 (10, 2, 8, -1),
363 (10, 2, 9, 1),
364 (10, 2, 10, 3),
365 (10, 2, 11, 2),
366 (11, 2, 8, -1),
367 (11, 2, 9, 1),
368 (11, 2, 10, 3),
369 (11, 2, 11, 2),
370 (12, 2, 8, -1),
371 (12, 2, 9, 1),
372 (12, 2, 10, 3),
373 (12, 2, 11, 2),
374 (13, 2, 8, -1),
375 (13, 2, 9, 1),
376 (13, 2, 10, 3),
377 (13, 2, 11, 2),
378 (14, 2, 8, -1),
379 (14, 2, 9, 1),
380 (14, 2, 10, 3),
381 (14, 2, 11, 2),
382 (15, 2, 8, -1),
383 (15, 2, 9, -3),
384 (15, 2, 10, -4),
385 (15, 2, 11, -2),
386 (16, 2, 8, -1),
387 (16, 2, 9, -3),
388 (16, 2, 10, -4),
389 (16, 2, 11, -2),
390 (17, 2, 8, -1),
391 (17, 2, 9, -3),
392 (17, 2, 10, -4),
393 (17, 2, 11, -2),
394 (18, 2, 8, -1),
395 (18, 2, 9, 1),
396 (18, 2, 10, -2),
397 (18, 2, 11, 2),
398 (19, 2, 8, -1),
399 (19, 2, 9, 1),
400 (19, 2, 10, -2),
401 (19, 2, 11, 2),
402 (20, 2, 8, 1),
403 (20, 2, 9, 2),
404 (20, 2, 10, -1),
405 (20, 2, 11, 3);
407 SELECT "time_warp"();
409 DROP FUNCTION "time_warp"();
411 -- Test policies that help with testing specific frontend parts
413 INSERT INTO "policy" (
414 "index",
415 "active",
416 "name",
417 "description",
418 "min_admission_time",
419 "max_admission_time",
420 "discussion_time",
421 "verification_time",
422 "voting_time",
423 "initiative_quorum_num",
424 "initiative_quorum_den"
425 ) VALUES (
426 1,
427 TRUE,
428 'Test New',
429 DEFAULT,
430 '0',
431 '2 days',
432 '1 second',
433 '1 second',
434 '1 second',
435 0, 100
436 ), (
437 1,
438 TRUE,
439 'Test Accept',
440 DEFAULT,
441 '0',
442 '1 second',
443 '2 days',
444 '1 second',
445 '1 second',
446 0, 100
447 ), (
448 1,
449 TRUE,
450 'Test Frozen',
451 DEFAULT,
452 '0',
453 '1 second',
454 '5 minutes',
455 '2 days',
456 '1 second',
457 0, 100
458 ), (
459 1,
460 TRUE,
461 'Test Voting',
462 DEFAULT,
463 '0',
464 '1 second',
465 '5 minutes',
466 '1 second',
467 '2 days',
468 0, 100
469 );
471 END;