liquid_feedback_core

view demo.sql @ 174:368fe2299d07

Ordering for final rank: 1st winner, 2nd initiatives fultilling direct AND indirect majority, rest ordered by schulze_rank and id
author jbe
date Tue Jun 07 20:56:42 2011 +0200 (2011-06-07)
parents ddd8e17d8f82
children e3b0ea7ab2ad
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" ("login", "name") VALUES
7 ('user1', 'User #1'), -- id 1
8 ('user2', 'User #2'), -- id 2
9 ('user3', 'User #3'), -- id 3
10 ('user4', 'User #4'), -- id 4
11 ('user5', 'User #5'), -- id 5
12 ('user6', 'User #6'), -- id 6
13 ('user7', 'User #7'), -- id 7
14 ('user8', 'User #8'), -- id 8
15 ('user9', 'User #9'), -- id 9
16 ('user10', 'User #10'), -- id 10
17 ('user11', 'User #11'), -- id 11
18 ('user12', 'User #12'), -- id 12
19 ('user13', 'User #13'), -- id 13
20 ('user14', 'User #14'), -- id 14
21 ('user15', 'User #15'), -- id 15
22 ('user16', 'User #16'), -- id 16
23 ('user17', 'User #17'), -- id 17
24 ('user18', 'User #18'), -- id 18
25 ('user19', 'User #19'), -- id 19
26 ('user20', 'User #20'), -- id 20
27 ('user21', 'User #21'), -- id 21
28 ('user22', 'User #22'), -- id 22
29 ('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;

Impressum / About Us