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