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