liquid_feedback_core
view test.sql @ 624:82387194519b
Added code to suppress compiler warning (writing one byte into region of size 0)
| author | jbe |
|---|---|
| date | Fri Feb 05 13:50:45 2021 +0100 (2021-02-05) |
| parents | 5855ff9e5c8f |
| children |
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", "issue_quorum_num", "issue_quorum_den",
46 "initiative_quorum", "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 1, 10, 100,
54 1, 30, 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 "created" = "created" - '1 hour 1 minute'::INTERVAL,
63 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
64 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
65 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
66 PERFORM "check_everything"();
67 RETURN;
68 END;
69 $$;
71 INSERT INTO "unit" ("name") VALUES ('Main');
73 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
74 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
75 FROM "member";
77 INSERT INTO "area" ("unit_id", "name") VALUES
78 (1, 'Area #1'), -- id 1
79 (1, 'Area #2'), -- id 2
80 (1, 'Area #3'), -- id 3
81 (1, 'Area #4'); -- id 4
83 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
84 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
86 -- global delegations
87 INSERT INTO "delegation"
88 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
89 ( 1, 'unit', 1, 9),
90 ( 2, 'unit', 1, 11),
91 ( 3, 'unit', 1, 12),
92 ( 4, 'unit', 1, 13),
93 ( 5, 'unit', 1, 14),
94 ( 6, 'unit', 1, 7),
95 ( 7, 'unit', 1, 8),
96 ( 8, 'unit', 1, 6),
97 (10, 'unit', 1, 9),
98 (11, 'unit', 1, 9),
99 (12, 'unit', 1, 21),
100 (15, 'unit', 1, 10),
101 (16, 'unit', 1, 17),
102 (17, 'unit', 1, 19),
103 (18, 'unit', 1, 19),
104 (23, 'unit', 1, 22);
106 -- delegations for topics
107 INSERT INTO "delegation"
108 ("area_id", "truster_id", "scope", "trustee_id") VALUES
109 (1, 3, 'area', 17),
110 (2, 5, 'area', 10),
111 (2, 9, 'area', 10),
112 (3, 4, 'area', 14),
113 (3, 16, 'area', 20),
114 (3, 19, 'area', 20),
115 (4, 5, 'area', 13),
116 (4, 12, 'area', 22);
118 INSERT INTO "issue" ("area_id", "policy_id") VALUES
119 (3, 1); -- id 1
121 INSERT INTO "initiative" ("issue_id", "name") VALUES
122 (1, 'Initiative #1'), -- id 1
123 (1, 'Initiative #2'), -- id 2
124 (1, 'Initiative #3'), -- id 3
125 (1, 'Initiative #4'), -- id 4
126 (1, 'Initiative #5'), -- id 5
127 (1, 'Initiative #6'), -- id 6
128 (1, 'Initiative #7'); -- id 7
130 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
131 (1, 17, 'Lorem ipsum...'), -- id 1
132 (2, 20, 'Lorem ipsum...'), -- id 2
133 (3, 20, 'Lorem ipsum...'), -- id 3
134 (4, 20, 'Lorem ipsum...'), -- id 4
135 (5, 14, 'Lorem ipsum...'), -- id 5
136 (6, 11, 'Lorem ipsum...'), -- id 6
137 (7, 12, 'Lorem ipsum...'); -- id 7
139 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
140 (1, 17),
141 (1, 19),
142 (2, 20),
143 (3, 20),
144 (4, 20),
145 (5, 14),
146 (6, 11),
147 (7, 12);
149 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
150 ( 7, 4, 4),
151 ( 8, 2, 2),
152 (11, 6, 6),
153 (12, 7, 7),
154 (14, 1, 1),
155 (14, 2, 2),
156 (14, 3, 3),
157 (14, 4, 4),
158 (14, 5, 5),
159 (14, 6, 6),
160 (14, 7, 7),
161 (17, 1, 1),
162 (17, 3, 3),
163 (19, 1, 1),
164 (19, 2, 2),
165 (20, 1, 1),
166 (20, 2, 2),
167 (20, 3, 3),
168 (20, 4, 4),
169 (20, 5, 5);
171 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
172 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
173 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
174 (14, 1, 2, FALSE);
175 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
176 (19, 1, 2, FALSE);
178 INSERT INTO "issue" ("area_id", "policy_id") VALUES
179 (4, 1); -- id 2
181 INSERT INTO "initiative" ("issue_id", "name") VALUES
182 (2, 'Initiative A'), -- id 8
183 (2, 'Initiative B'), -- id 9
184 (2, 'Initiative C'), -- id 10
185 (2, 'Initiative D'); -- id 11
187 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
188 ( 8, 1, 'Lorem ipsum...'), -- id 8
189 ( 9, 2, 'Lorem ipsum...'), -- id 9
190 (10, 3, 'Lorem ipsum...'), -- id 10
191 (11, 4, 'Lorem ipsum...'); -- id 11
193 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
194 ( 8, 1),
195 ( 9, 2),
196 (10, 3),
197 (11, 4);
199 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
200 (1, 8, 8),
201 (1, 9, 9),
202 (1, 10, 10),
203 (1, 11, 11),
204 (2, 8, 8),
205 (2, 9, 9),
206 (2, 10, 10),
207 (2, 11, 11),
208 (3, 8, 8),
209 (3, 9, 9),
210 (3, 10, 10),
211 (3, 11, 11),
212 (4, 8, 8),
213 (4, 9, 9),
214 (4, 10, 10),
215 (4, 11, 11),
216 (5, 8, 8),
217 (5, 9, 9),
218 (5, 10, 10),
219 (5, 11, 11),
220 (6, 8, 8),
221 (6, 9, 9),
222 (6, 10, 10),
223 (6, 11, 11);
225 SELECT "time_warp"();
226 SELECT "time_warp"();
227 SELECT "time_warp"();
229 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
230 ( 8, 1),
231 ( 9, 1),
232 (11, 1),
233 (12, 1),
234 (14, 1),
235 (19, 1),
236 (20, 1),
237 (21, 1);
239 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
240 ( 8, 1, 1, 1),
241 ( 8, 1, 2, 1),
242 ( 8, 1, 3, 1),
243 ( 8, 1, 4, 1),
244 ( 8, 1, 5, 1),
245 ( 8, 1, 6, -1),
246 ( 8, 1, 7, -1),
247 ( 9, 1, 1, -2),
248 ( 9, 1, 2, -3),
249 ( 9, 1, 3, -2),
250 ( 9, 1, 4, -2),
251 ( 9, 1, 5, -2),
252 ( 9, 1, 6, -1),
253 (11, 1, 1, -1),
254 (11, 1, 2, -1),
255 (11, 1, 3, -1),
256 (11, 1, 4, -1),
257 (11, 1, 5, -1),
258 (11, 1, 6, 2),
259 (11, 1, 7, 1),
260 (12, 1, 1, -1),
261 (12, 1, 3, -1),
262 (12, 1, 4, -1),
263 (12, 1, 5, -1),
264 (12, 1, 6, -2),
265 (12, 1, 7, 1),
266 (14, 1, 1, 1),
267 (14, 1, 2, 3),
268 (14, 1, 3, 1),
269 (14, 1, 4, 2),
270 (14, 1, 5, 1),
271 (14, 1, 6, 1),
272 (14, 1, 7, 1),
273 (19, 1, 1, 3),
274 (19, 1, 2, 4),
275 (19, 1, 3, 2),
276 (19, 1, 4, 2),
277 (19, 1, 5, 2),
278 (19, 1, 7, 1),
279 (20, 1, 1, 1),
280 (20, 1, 2, 2),
281 (20, 1, 3, 1),
282 (20, 1, 4, 1),
283 (20, 1, 5, 1),
284 (21, 1, 5, -1);
286 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
287 ( 1, 2),
288 ( 2, 2),
289 ( 3, 2),
290 ( 4, 2),
291 ( 5, 2),
292 ( 6, 2),
293 ( 7, 2),
294 ( 8, 2),
295 ( 9, 2),
296 (10, 2),
297 (11, 2),
298 (12, 2),
299 (13, 2),
300 (14, 2),
301 (15, 2),
302 (16, 2),
303 (17, 2),
304 (18, 2),
305 (19, 2),
306 (20, 2);
308 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
309 ( 1, 2, 8, 3),
310 ( 1, 2, 9, 4),
311 ( 1, 2, 10, 2),
312 ( 1, 2, 11, 1),
313 ( 2, 2, 8, 3),
314 ( 2, 2, 9, 4),
315 ( 2, 2, 10, 2),
316 ( 2, 2, 11, 1),
317 ( 3, 2, 8, 4),
318 ( 3, 2, 9, 3),
319 ( 3, 2, 10, 2),
320 ( 3, 2, 11, 1),
321 ( 4, 2, 8, 4),
322 ( 4, 2, 9, 3),
323 ( 4, 2, 10, 2),
324 ( 4, 2, 11, 1),
325 ( 5, 2, 8, 4),
326 ( 5, 2, 9, 3),
327 ( 5, 2, 10, 2),
328 ( 5, 2, 11, 1),
329 ( 6, 2, 8, 4),
330 ( 6, 2, 9, 3),
331 ( 6, 2, 10, 2),
332 ( 6, 2, 11, 1),
333 ( 7, 2, 8, 4),
334 ( 7, 2, 9, 3),
335 ( 7, 2, 10, 2),
336 ( 7, 2, 11, 1),
337 ( 8, 2, 8, 4),
338 ( 8, 2, 9, 3),
339 ( 8, 2, 10, 2),
340 ( 8, 2, 11, 1),
341 ( 9, 2, 8, -1),
342 ( 9, 2, 9, 1),
343 ( 9, 2, 10, 3),
344 ( 9, 2, 11, 2),
345 (10, 2, 8, -1),
346 (10, 2, 9, 1),
347 (10, 2, 10, 3),
348 (10, 2, 11, 2),
349 (11, 2, 8, -1),
350 (11, 2, 9, 1),
351 (11, 2, 10, 3),
352 (11, 2, 11, 2),
353 (12, 2, 8, -1),
354 (12, 2, 9, 1),
355 (12, 2, 10, 3),
356 (12, 2, 11, 2),
357 (13, 2, 8, -1),
358 (13, 2, 9, 1),
359 (13, 2, 10, 3),
360 (13, 2, 11, 2),
361 (14, 2, 8, -1),
362 (14, 2, 9, 1),
363 (14, 2, 10, 3),
364 (14, 2, 11, 2),
365 (15, 2, 8, -1),
366 (15, 2, 9, -3),
367 (15, 2, 10, -4),
368 (15, 2, 11, -2),
369 (16, 2, 8, -1),
370 (16, 2, 9, -3),
371 (16, 2, 10, -4),
372 (16, 2, 11, -2),
373 (17, 2, 8, -1),
374 (17, 2, 9, -3),
375 (17, 2, 10, -4),
376 (17, 2, 11, -2),
377 (18, 2, 8, -1),
378 (18, 2, 9, 1),
379 (18, 2, 10, -2),
380 (18, 2, 11, 2),
381 (19, 2, 8, -1),
382 (19, 2, 9, 1),
383 (19, 2, 10, -2),
384 (19, 2, 11, 2),
385 (20, 2, 8, 1),
386 (20, 2, 9, 2),
387 (20, 2, 10, -1),
388 (20, 2, 11, 3);
390 SELECT "time_warp"();
392 DROP FUNCTION "time_warp"();
394 -- Test policies that help with testing specific frontend parts
396 INSERT INTO "policy" (
397 "index",
398 "active",
399 "name",
400 "description",
401 "min_admission_time",
402 "max_admission_time",
403 "discussion_time",
404 "verification_time",
405 "voting_time",
406 "issue_quorum",
407 "issue_quorum_num",
408 "issue_quorum_den",
409 "initiative_quorum",
410 "initiative_quorum_num",
411 "initiative_quorum_den"
412 ) VALUES (
413 1,
414 TRUE,
415 'Test New',
416 DEFAULT,
417 '0',
418 '2 days',
419 '1 second',
420 '1 second',
421 '1 second',
422 1, 0, 100,
423 1, 0, 100
424 ), (
425 1,
426 TRUE,
427 'Test Accept',
428 DEFAULT,
429 '0',
430 '1 second',
431 '2 days',
432 '1 second',
433 '1 second',
434 1, 0, 100,
435 1, 0, 100
436 ), (
437 1,
438 TRUE,
439 'Test Frozen',
440 DEFAULT,
441 '0',
442 '1 second',
443 '5 minutes',
444 '2 days',
445 '1 second',
446 1, 0, 100,
447 1, 0, 100
448 ), (
449 1,
450 TRUE,
451 'Test Voting',
452 DEFAULT,
453 '0',
454 '1 second',
455 '5 minutes',
456 '1 second',
457 '2 days',
458 1, 0, 100,
459 1, 0, 100
460 );
462 END;