liquid_feedback_core
view test.sql @ 341:607e8f5d66eb
Write "NOW()" as "now()"
| author | jbe |
|---|---|
| date | Thu Feb 21 16:40:17 2013 +0100 (2013-02-21) |
| parents | a2ab4fb1d0c7 |
| children | c83b2ac5477b |
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 "admission_time",
41 "discussion_time",
42 "verification_time",
43 "voting_time",
44 "issue_quorum_num", "issue_quorum_den",
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 '1 hour', '1 hour', '1 hour', '1 hour',
52 25, 100,
53 20, 100,
54 1, 2, TRUE,
55 TRUE, FALSE );
57 CREATE FUNCTION "time_warp"() RETURNS VOID
58 LANGUAGE 'plpgsql' VOLATILE AS $$
59 BEGIN
60 UPDATE "issue" SET
61 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
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 INSERT INTO "membership" ("area_id", "member_id") VALUES
87 (1, 9),
88 (1, 19),
89 (2, 9),
90 (2, 10),
91 (2, 17),
92 (3, 9),
93 (3, 11),
94 (3, 12),
95 (3, 14),
96 (3, 20),
97 (3, 21),
98 (3, 22),
99 (4, 6),
100 (4, 9),
101 (4, 13),
102 (4, 22);
104 -- global delegations
105 INSERT INTO "delegation"
106 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
107 ( 1, 'unit', 1, 9),
108 ( 2, 'unit', 1, 11),
109 ( 3, 'unit', 1, 12),
110 ( 4, 'unit', 1, 13),
111 ( 5, 'unit', 1, 14),
112 ( 6, 'unit', 1, 7),
113 ( 7, 'unit', 1, 8),
114 ( 8, 'unit', 1, 6),
115 (10, 'unit', 1, 9),
116 (11, 'unit', 1, 9),
117 (12, 'unit', 1, 21),
118 (15, 'unit', 1, 10),
119 (16, 'unit', 1, 17),
120 (17, 'unit', 1, 19),
121 (18, 'unit', 1, 19),
122 (23, 'unit', 1, 22);
124 -- delegations for topics
125 INSERT INTO "delegation"
126 ("area_id", "truster_id", "scope", "trustee_id") VALUES
127 (1, 3, 'area', 17),
128 (2, 5, 'area', 10),
129 (2, 9, 'area', 10),
130 (3, 4, 'area', 14),
131 (3, 16, 'area', 20),
132 (3, 19, 'area', 20),
133 (4, 5, 'area', 13),
134 (4, 12, 'area', 22);
136 INSERT INTO "issue" ("area_id", "policy_id") VALUES
137 (3, 1); -- id 1
139 INSERT INTO "initiative" ("issue_id", "name") VALUES
140 (1, 'Initiative #1'), -- id 1
141 (1, 'Initiative #2'), -- id 2
142 (1, 'Initiative #3'), -- id 3
143 (1, 'Initiative #4'), -- id 4
144 (1, 'Initiative #5'), -- id 5
145 (1, 'Initiative #6'), -- id 6
146 (1, 'Initiative #7'); -- id 7
148 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
149 (1, 17, 'Lorem ipsum...'), -- id 1
150 (2, 20, 'Lorem ipsum...'), -- id 2
151 (3, 20, 'Lorem ipsum...'), -- id 3
152 (4, 20, 'Lorem ipsum...'), -- id 4
153 (5, 14, 'Lorem ipsum...'), -- id 5
154 (6, 11, 'Lorem ipsum...'), -- id 6
155 (7, 12, 'Lorem ipsum...'); -- id 7
157 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
158 (1, 17),
159 (1, 19),
160 (2, 20),
161 (3, 20),
162 (4, 20),
163 (5, 14),
164 (6, 11),
165 (7, 12);
167 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
168 ( 7, 4, 4),
169 ( 8, 2, 2),
170 (11, 6, 6),
171 (12, 7, 7),
172 (14, 1, 1),
173 (14, 2, 2),
174 (14, 3, 3),
175 (14, 4, 4),
176 (14, 5, 5),
177 (14, 6, 6),
178 (14, 7, 7),
179 (17, 1, 1),
180 (17, 3, 3),
181 (19, 1, 1),
182 (19, 2, 2),
183 (20, 1, 1),
184 (20, 2, 2),
185 (20, 3, 3),
186 (20, 4, 4),
187 (20, 5, 5);
189 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
190 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
191 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
192 (14, 1, 2, FALSE);
193 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
194 (19, 1, 2, FALSE);
196 INSERT INTO "issue" ("area_id", "policy_id") VALUES
197 (4, 1); -- id 2
199 INSERT INTO "initiative" ("issue_id", "name") VALUES
200 (2, 'Initiative A'), -- id 8
201 (2, 'Initiative B'), -- id 9
202 (2, 'Initiative C'), -- id 10
203 (2, 'Initiative D'); -- id 11
205 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
206 ( 8, 1, 'Lorem ipsum...'), -- id 8
207 ( 9, 2, 'Lorem ipsum...'), -- id 9
208 (10, 3, 'Lorem ipsum...'), -- id 10
209 (11, 4, 'Lorem ipsum...'); -- id 11
211 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
212 ( 8, 1),
213 ( 9, 2),
214 (10, 3),
215 (11, 4);
217 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
218 (1, 8, 8),
219 (1, 9, 9),
220 (1, 10, 10),
221 (1, 11, 11),
222 (2, 8, 8),
223 (2, 9, 9),
224 (2, 10, 10),
225 (2, 11, 11),
226 (3, 8, 8),
227 (3, 9, 9),
228 (3, 10, 10),
229 (3, 11, 11),
230 (4, 8, 8),
231 (4, 9, 9),
232 (4, 10, 10),
233 (4, 11, 11),
234 (5, 8, 8),
235 (5, 9, 9),
236 (5, 10, 10),
237 (5, 11, 11),
238 (6, 8, 8),
239 (6, 9, 9),
240 (6, 10, 10),
241 (6, 11, 11);
243 SELECT "time_warp"();
244 SELECT "time_warp"();
245 SELECT "time_warp"();
247 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
248 ( 8, 1),
249 ( 9, 1),
250 (11, 1),
251 (12, 1),
252 (14, 1),
253 (19, 1),
254 (20, 1),
255 (21, 1);
257 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
258 ( 8, 1, 1, 1),
259 ( 8, 1, 2, 1),
260 ( 8, 1, 3, 1),
261 ( 8, 1, 4, 1),
262 ( 8, 1, 5, 1),
263 ( 8, 1, 6, -1),
264 ( 8, 1, 7, -1),
265 ( 9, 1, 1, -2),
266 ( 9, 1, 2, -3),
267 ( 9, 1, 3, -2),
268 ( 9, 1, 4, -2),
269 ( 9, 1, 5, -2),
270 ( 9, 1, 6, -1),
271 (11, 1, 1, -1),
272 (11, 1, 2, -1),
273 (11, 1, 3, -1),
274 (11, 1, 4, -1),
275 (11, 1, 5, -1),
276 (11, 1, 6, 2),
277 (11, 1, 7, 1),
278 (12, 1, 1, -1),
279 (12, 1, 3, -1),
280 (12, 1, 4, -1),
281 (12, 1, 5, -1),
282 (12, 1, 6, -2),
283 (12, 1, 7, 1),
284 (14, 1, 1, 1),
285 (14, 1, 2, 3),
286 (14, 1, 3, 1),
287 (14, 1, 4, 2),
288 (14, 1, 5, 1),
289 (14, 1, 6, 1),
290 (14, 1, 7, 1),
291 (19, 1, 1, 3),
292 (19, 1, 2, 4),
293 (19, 1, 3, 2),
294 (19, 1, 4, 2),
295 (19, 1, 5, 2),
296 (19, 1, 7, 1),
297 (20, 1, 1, 1),
298 (20, 1, 2, 2),
299 (20, 1, 3, 1),
300 (20, 1, 4, 1),
301 (20, 1, 5, 1),
302 (21, 1, 5, -1);
304 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
305 ( 1, 2),
306 ( 2, 2),
307 ( 3, 2),
308 ( 4, 2),
309 ( 5, 2),
310 ( 6, 2),
311 ( 7, 2),
312 ( 8, 2),
313 ( 9, 2),
314 (10, 2),
315 (11, 2),
316 (12, 2),
317 (13, 2),
318 (14, 2),
319 (15, 2),
320 (16, 2),
321 (17, 2),
322 (18, 2),
323 (19, 2),
324 (20, 2);
326 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
327 ( 1, 2, 8, 3),
328 ( 1, 2, 9, 4),
329 ( 1, 2, 10, 2),
330 ( 1, 2, 11, 1),
331 ( 2, 2, 8, 3),
332 ( 2, 2, 9, 4),
333 ( 2, 2, 10, 2),
334 ( 2, 2, 11, 1),
335 ( 3, 2, 8, 4),
336 ( 3, 2, 9, 3),
337 ( 3, 2, 10, 2),
338 ( 3, 2, 11, 1),
339 ( 4, 2, 8, 4),
340 ( 4, 2, 9, 3),
341 ( 4, 2, 10, 2),
342 ( 4, 2, 11, 1),
343 ( 5, 2, 8, 4),
344 ( 5, 2, 9, 3),
345 ( 5, 2, 10, 2),
346 ( 5, 2, 11, 1),
347 ( 6, 2, 8, 4),
348 ( 6, 2, 9, 3),
349 ( 6, 2, 10, 2),
350 ( 6, 2, 11, 1),
351 ( 7, 2, 8, 4),
352 ( 7, 2, 9, 3),
353 ( 7, 2, 10, 2),
354 ( 7, 2, 11, 1),
355 ( 8, 2, 8, 4),
356 ( 8, 2, 9, 3),
357 ( 8, 2, 10, 2),
358 ( 8, 2, 11, 1),
359 ( 9, 2, 8, -1),
360 ( 9, 2, 9, 1),
361 ( 9, 2, 10, 3),
362 ( 9, 2, 11, 2),
363 (10, 2, 8, -1),
364 (10, 2, 9, 1),
365 (10, 2, 10, 3),
366 (10, 2, 11, 2),
367 (11, 2, 8, -1),
368 (11, 2, 9, 1),
369 (11, 2, 10, 3),
370 (11, 2, 11, 2),
371 (12, 2, 8, -1),
372 (12, 2, 9, 1),
373 (12, 2, 10, 3),
374 (12, 2, 11, 2),
375 (13, 2, 8, -1),
376 (13, 2, 9, 1),
377 (13, 2, 10, 3),
378 (13, 2, 11, 2),
379 (14, 2, 8, -1),
380 (14, 2, 9, 1),
381 (14, 2, 10, 3),
382 (14, 2, 11, 2),
383 (15, 2, 8, -1),
384 (15, 2, 9, -3),
385 (15, 2, 10, -4),
386 (15, 2, 11, -2),
387 (16, 2, 8, -1),
388 (16, 2, 9, -3),
389 (16, 2, 10, -4),
390 (16, 2, 11, -2),
391 (17, 2, 8, -1),
392 (17, 2, 9, -3),
393 (17, 2, 10, -4),
394 (17, 2, 11, -2),
395 (18, 2, 8, -1),
396 (18, 2, 9, 1),
397 (18, 2, 10, -2),
398 (18, 2, 11, 2),
399 (19, 2, 8, -1),
400 (19, 2, 9, 1),
401 (19, 2, 10, -2),
402 (19, 2, 11, 2),
403 (20, 2, 8, 1),
404 (20, 2, 9, 2),
405 (20, 2, 10, -1),
406 (20, 2, 11, 3);
408 SELECT "time_warp"();
410 DROP FUNCTION "time_warp"();
412 -- Test policies that help with testing specific frontend parts
414 INSERT INTO "policy" (
415 "index",
416 "active",
417 "name",
418 "description",
419 "admission_time",
420 "discussion_time",
421 "verification_time",
422 "voting_time",
423 "issue_quorum_num",
424 "issue_quorum_den",
425 "initiative_quorum_num",
426 "initiative_quorum_den"
427 ) VALUES (
428 1,
429 TRUE,
430 'Test New',
431 DEFAULT,
432 '2 days',
433 '1 second',
434 '1 second',
435 '1 second',
436 0, 100,
437 0, 100
438 ), (
439 1,
440 TRUE,
441 'Test Accept',
442 DEFAULT,
443 '1 second',
444 '2 days',
445 '1 second',
446 '1 second',
447 0, 100,
448 0, 100
449 ), (
450 1,
451 TRUE,
452 'Test Frozen',
453 DEFAULT,
454 '1 second',
455 '5 minutes',
456 '2 days',
457 '1 second',
458 0, 100,
459 0, 100
460 ), (
461 1,
462 TRUE,
463 'Test Voting',
464 DEFAULT,
465 '1 second',
466 '5 minutes',
467 '1 second',
468 '2 days',
469 0, 100,
470 0, 100
471 );
473 END;