liquid_feedback_core
view test.sql @ 528:3e28fd842354
Work on new issue admission system (replacing current approach of 1st quorum); Added support for geo-tagging and spatial indexing
| author | jbe |
|---|---|
| date | Sun May 29 20:42:40 2016 +0200 (2016-05-29) |
| parents | fae00a5c1c71 |
| children | 5855ff9e5c8f |
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",
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 3,
54 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 "admission_rule" ("unit_id", "name") VALUES (1, 'General admission rule');
75 INSERT INTO "admission_rule_condition" ("admission_rule_id", "unit_id", "holdoff_time")
76 VALUES (1, 1, '0 seconds');
78 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
79 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
80 FROM "member";
82 INSERT INTO "area" ("unit_id", "name") VALUES
83 (1, 'Area #1'), -- id 1
84 (1, 'Area #2'), -- id 2
85 (1, 'Area #3'), -- id 3
86 (1, 'Area #4'); -- id 4
88 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
89 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
91 -- global delegations
92 INSERT INTO "delegation"
93 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
94 ( 1, 'unit', 1, 9),
95 ( 2, 'unit', 1, 11),
96 ( 3, 'unit', 1, 12),
97 ( 4, 'unit', 1, 13),
98 ( 5, 'unit', 1, 14),
99 ( 6, 'unit', 1, 7),
100 ( 7, 'unit', 1, 8),
101 ( 8, 'unit', 1, 6),
102 (10, 'unit', 1, 9),
103 (11, 'unit', 1, 9),
104 (12, 'unit', 1, 21),
105 (15, 'unit', 1, 10),
106 (16, 'unit', 1, 17),
107 (17, 'unit', 1, 19),
108 (18, 'unit', 1, 19),
109 (23, 'unit', 1, 22);
111 -- delegations for topics
112 INSERT INTO "delegation"
113 ("area_id", "truster_id", "scope", "trustee_id") VALUES
114 (1, 3, 'area', 17),
115 (2, 5, 'area', 10),
116 (2, 9, 'area', 10),
117 (3, 4, 'area', 14),
118 (3, 16, 'area', 20),
119 (3, 19, 'area', 20),
120 (4, 5, 'area', 13),
121 (4, 12, 'area', 22);
123 INSERT INTO "issue" ("area_id", "policy_id") VALUES
124 (3, 1); -- id 1
126 INSERT INTO "initiative" ("issue_id", "name") VALUES
127 (1, 'Initiative #1'), -- id 1
128 (1, 'Initiative #2'), -- id 2
129 (1, 'Initiative #3'), -- id 3
130 (1, 'Initiative #4'), -- id 4
131 (1, 'Initiative #5'), -- id 5
132 (1, 'Initiative #6'), -- id 6
133 (1, 'Initiative #7'); -- id 7
135 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
136 (1, 17, 'Lorem ipsum...'), -- id 1
137 (2, 20, 'Lorem ipsum...'), -- id 2
138 (3, 20, 'Lorem ipsum...'), -- id 3
139 (4, 20, 'Lorem ipsum...'), -- id 4
140 (5, 14, 'Lorem ipsum...'), -- id 5
141 (6, 11, 'Lorem ipsum...'), -- id 6
142 (7, 12, 'Lorem ipsum...'); -- id 7
144 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
145 (1, 17),
146 (1, 19),
147 (2, 20),
148 (3, 20),
149 (4, 20),
150 (5, 14),
151 (6, 11),
152 (7, 12);
154 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
155 ( 7, 4, 4),
156 ( 8, 2, 2),
157 (11, 6, 6),
158 (12, 7, 7),
159 (14, 1, 1),
160 (14, 2, 2),
161 (14, 3, 3),
162 (14, 4, 4),
163 (14, 5, 5),
164 (14, 6, 6),
165 (14, 7, 7),
166 (17, 1, 1),
167 (17, 3, 3),
168 (19, 1, 1),
169 (19, 2, 2),
170 (20, 1, 1),
171 (20, 2, 2),
172 (20, 3, 3),
173 (20, 4, 4),
174 (20, 5, 5);
176 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
177 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
178 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
179 (14, 1, 2, FALSE);
180 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
181 (19, 1, 2, FALSE);
183 INSERT INTO "issue" ("area_id", "policy_id") VALUES
184 (4, 1); -- id 2
186 INSERT INTO "initiative" ("issue_id", "name") VALUES
187 (2, 'Initiative A'), -- id 8
188 (2, 'Initiative B'), -- id 9
189 (2, 'Initiative C'), -- id 10
190 (2, 'Initiative D'); -- id 11
192 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
193 ( 8, 1, 'Lorem ipsum...'), -- id 8
194 ( 9, 2, 'Lorem ipsum...'), -- id 9
195 (10, 3, 'Lorem ipsum...'), -- id 10
196 (11, 4, 'Lorem ipsum...'); -- id 11
198 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
199 ( 8, 1),
200 ( 9, 2),
201 (10, 3),
202 (11, 4);
204 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
205 (1, 8, 8),
206 (1, 9, 9),
207 (1, 10, 10),
208 (1, 11, 11),
209 (2, 8, 8),
210 (2, 9, 9),
211 (2, 10, 10),
212 (2, 11, 11),
213 (3, 8, 8),
214 (3, 9, 9),
215 (3, 10, 10),
216 (3, 11, 11),
217 (4, 8, 8),
218 (4, 9, 9),
219 (4, 10, 10),
220 (4, 11, 11),
221 (5, 8, 8),
222 (5, 9, 9),
223 (5, 10, 10),
224 (5, 11, 11),
225 (6, 8, 8),
226 (6, 9, 9),
227 (6, 10, 10),
228 (6, 11, 11);
230 SELECT "time_warp"();
231 SELECT "time_warp"();
232 SELECT "time_warp"();
234 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
235 ( 8, 1),
236 ( 9, 1),
237 (11, 1),
238 (12, 1),
239 (14, 1),
240 (19, 1),
241 (20, 1),
242 (21, 1);
244 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
245 ( 8, 1, 1, 1),
246 ( 8, 1, 2, 1),
247 ( 8, 1, 3, 1),
248 ( 8, 1, 4, 1),
249 ( 8, 1, 5, 1),
250 ( 8, 1, 6, -1),
251 ( 8, 1, 7, -1),
252 ( 9, 1, 1, -2),
253 ( 9, 1, 2, -3),
254 ( 9, 1, 3, -2),
255 ( 9, 1, 4, -2),
256 ( 9, 1, 5, -2),
257 ( 9, 1, 6, -1),
258 (11, 1, 1, -1),
259 (11, 1, 2, -1),
260 (11, 1, 3, -1),
261 (11, 1, 4, -1),
262 (11, 1, 5, -1),
263 (11, 1, 6, 2),
264 (11, 1, 7, 1),
265 (12, 1, 1, -1),
266 (12, 1, 3, -1),
267 (12, 1, 4, -1),
268 (12, 1, 5, -1),
269 (12, 1, 6, -2),
270 (12, 1, 7, 1),
271 (14, 1, 1, 1),
272 (14, 1, 2, 3),
273 (14, 1, 3, 1),
274 (14, 1, 4, 2),
275 (14, 1, 5, 1),
276 (14, 1, 6, 1),
277 (14, 1, 7, 1),
278 (19, 1, 1, 3),
279 (19, 1, 2, 4),
280 (19, 1, 3, 2),
281 (19, 1, 4, 2),
282 (19, 1, 5, 2),
283 (19, 1, 7, 1),
284 (20, 1, 1, 1),
285 (20, 1, 2, 2),
286 (20, 1, 3, 1),
287 (20, 1, 4, 1),
288 (20, 1, 5, 1),
289 (21, 1, 5, -1);
291 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
292 ( 1, 2),
293 ( 2, 2),
294 ( 3, 2),
295 ( 4, 2),
296 ( 5, 2),
297 ( 6, 2),
298 ( 7, 2),
299 ( 8, 2),
300 ( 9, 2),
301 (10, 2),
302 (11, 2),
303 (12, 2),
304 (13, 2),
305 (14, 2),
306 (15, 2),
307 (16, 2),
308 (17, 2),
309 (18, 2),
310 (19, 2),
311 (20, 2);
313 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
314 ( 1, 2, 8, 3),
315 ( 1, 2, 9, 4),
316 ( 1, 2, 10, 2),
317 ( 1, 2, 11, 1),
318 ( 2, 2, 8, 3),
319 ( 2, 2, 9, 4),
320 ( 2, 2, 10, 2),
321 ( 2, 2, 11, 1),
322 ( 3, 2, 8, 4),
323 ( 3, 2, 9, 3),
324 ( 3, 2, 10, 2),
325 ( 3, 2, 11, 1),
326 ( 4, 2, 8, 4),
327 ( 4, 2, 9, 3),
328 ( 4, 2, 10, 2),
329 ( 4, 2, 11, 1),
330 ( 5, 2, 8, 4),
331 ( 5, 2, 9, 3),
332 ( 5, 2, 10, 2),
333 ( 5, 2, 11, 1),
334 ( 6, 2, 8, 4),
335 ( 6, 2, 9, 3),
336 ( 6, 2, 10, 2),
337 ( 6, 2, 11, 1),
338 ( 7, 2, 8, 4),
339 ( 7, 2, 9, 3),
340 ( 7, 2, 10, 2),
341 ( 7, 2, 11, 1),
342 ( 8, 2, 8, 4),
343 ( 8, 2, 9, 3),
344 ( 8, 2, 10, 2),
345 ( 8, 2, 11, 1),
346 ( 9, 2, 8, -1),
347 ( 9, 2, 9, 1),
348 ( 9, 2, 10, 3),
349 ( 9, 2, 11, 2),
350 (10, 2, 8, -1),
351 (10, 2, 9, 1),
352 (10, 2, 10, 3),
353 (10, 2, 11, 2),
354 (11, 2, 8, -1),
355 (11, 2, 9, 1),
356 (11, 2, 10, 3),
357 (11, 2, 11, 2),
358 (12, 2, 8, -1),
359 (12, 2, 9, 1),
360 (12, 2, 10, 3),
361 (12, 2, 11, 2),
362 (13, 2, 8, -1),
363 (13, 2, 9, 1),
364 (13, 2, 10, 3),
365 (13, 2, 11, 2),
366 (14, 2, 8, -1),
367 (14, 2, 9, 1),
368 (14, 2, 10, 3),
369 (14, 2, 11, 2),
370 (15, 2, 8, -1),
371 (15, 2, 9, -3),
372 (15, 2, 10, -4),
373 (15, 2, 11, -2),
374 (16, 2, 8, -1),
375 (16, 2, 9, -3),
376 (16, 2, 10, -4),
377 (16, 2, 11, -2),
378 (17, 2, 8, -1),
379 (17, 2, 9, -3),
380 (17, 2, 10, -4),
381 (17, 2, 11, -2),
382 (18, 2, 8, -1),
383 (18, 2, 9, 1),
384 (18, 2, 10, -2),
385 (18, 2, 11, 2),
386 (19, 2, 8, -1),
387 (19, 2, 9, 1),
388 (19, 2, 10, -2),
389 (19, 2, 11, 2),
390 (20, 2, 8, 1),
391 (20, 2, 9, 2),
392 (20, 2, 10, -1),
393 (20, 2, 11, 3);
395 SELECT "time_warp"();
397 DROP FUNCTION "time_warp"();
399 -- Test policies that help with testing specific frontend parts
401 INSERT INTO "policy" (
402 "index",
403 "active",
404 "name",
405 "description",
406 "min_admission_time",
407 "max_admission_time",
408 "discussion_time",
409 "verification_time",
410 "voting_time",
411 "issue_quorum",
412 "initiative_quorum_num",
413 "initiative_quorum_den"
414 ) VALUES (
415 1,
416 TRUE,
417 'Test New',
418 DEFAULT,
419 '0',
420 '2 days',
421 '1 second',
422 '1 second',
423 '1 second',
424 1,
425 0, 100
426 ), (
427 1,
428 TRUE,
429 'Test Accept',
430 DEFAULT,
431 '0',
432 '1 second',
433 '2 days',
434 '1 second',
435 '1 second',
436 1,
437 0, 100
438 ), (
439 1,
440 TRUE,
441 'Test Frozen',
442 DEFAULT,
443 '0',
444 '1 second',
445 '5 minutes',
446 '2 days',
447 '1 second',
448 1,
449 0, 100
450 ), (
451 1,
452 TRUE,
453 'Test Voting',
454 DEFAULT,
455 '0',
456 '1 second',
457 '5 minutes',
458 '1 second',
459 '2 days',
460 1,
461 0, 100
462 );
464 END;