liquid_feedback_core
view test.sql @ 453:fb7ccc4933a8
Update script for v3.1.0
| author | jbe |
|---|---|
| date | Thu Dec 03 22:09:25 2015 +0100 (2015-12-03) |
| parents | 78e9a2071b0c |
| children | ca21a3f49e4c |
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_num", "issue_quorum_den",
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 25, 100,
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_num",
426 "issue_quorum_den",
427 "initiative_quorum_num",
428 "initiative_quorum_den"
429 ) VALUES (
430 1,
431 TRUE,
432 'Test New',
433 DEFAULT,
434 '0',
435 '2 days',
436 '1 second',
437 '1 second',
438 '1 second',
439 0, 100,
440 0, 100
441 ), (
442 1,
443 TRUE,
444 'Test Accept',
445 DEFAULT,
446 '0',
447 '1 second',
448 '2 days',
449 '1 second',
450 '1 second',
451 0, 100,
452 0, 100
453 ), (
454 1,
455 TRUE,
456 'Test Frozen',
457 DEFAULT,
458 '0',
459 '1 second',
460 '5 minutes',
461 '2 days',
462 '1 second',
463 0, 100,
464 0, 100
465 ), (
466 1,
467 TRUE,
468 'Test Voting',
469 DEFAULT,
470 '0',
471 '1 second',
472 '5 minutes',
473 '1 second',
474 '2 days',
475 0, 100,
476 0, 100
477 );
479 END;