liquid_feedback_core
view demo.sql @ 134:bd0cd909189d
Added new column "promising" to table "initiative"; Simplified tie-breaking
Also added example to demo.sql, which results in a promising initiative.
Also added example to demo.sql, which results in a promising initiative.
| author | jbe |
|---|---|
| date | Tue May 24 23:35:29 2011 +0200 (2011-05-24) |
| parents | 72792038f7f0 |
| children | e3bfa2d7954c |
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, TRUE);
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
196 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
197 (8, 1, 'Lorem ipsum...'), -- id 8
198 (9, 2, 'Lorem ipsum...'); -- id 9
200 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
201 (8, 1),
202 (9, 2);
204 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
205 (1, 8, 8),
206 (1, 9, 9),
207 (2, 8, 8),
208 (2, 9, 9),
209 (3, 8, 8),
210 (3, 9, 9),
211 (4, 8, 8),
212 (4, 9, 9),
213 (5, 8, 8),
214 (5, 9, 9),
215 (6, 8, 8),
216 (6, 9, 9);
218 SELECT "time_warp"();
219 SELECT "time_warp"();
220 SELECT "time_warp"();
222 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
223 ( 8, 1),
224 ( 9, 1),
225 (11, 1),
226 (12, 1),
227 (14, 1),
228 (19, 1),
229 (20, 1),
230 (21, 1);
232 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
233 ( 8, 1, 1, 1),
234 ( 8, 1, 2, 1),
235 ( 8, 1, 3, 1),
236 ( 8, 1, 4, 1),
237 ( 8, 1, 5, 1),
238 ( 8, 1, 6, -1),
239 ( 8, 1, 7, -1),
240 ( 9, 1, 1, -2),
241 ( 9, 1, 2, -3),
242 ( 9, 1, 3, -2),
243 ( 9, 1, 4, -2),
244 ( 9, 1, 5, -2),
245 ( 9, 1, 6, -1),
246 (11, 1, 1, -1),
247 (11, 1, 2, -1),
248 (11, 1, 3, -1),
249 (11, 1, 4, -1),
250 (11, 1, 5, -1),
251 (11, 1, 6, 2),
252 (11, 1, 7, 1),
253 (12, 1, 1, -1),
254 (12, 1, 3, -1),
255 (12, 1, 4, -1),
256 (12, 1, 5, -1),
257 (12, 1, 6, -2),
258 (12, 1, 7, 1),
259 (14, 1, 1, 1),
260 (14, 1, 2, 3),
261 (14, 1, 3, 1),
262 (14, 1, 4, 2),
263 (14, 1, 5, 1),
264 (14, 1, 6, 1),
265 (14, 1, 7, 1),
266 (19, 1, 1, 3),
267 (19, 1, 2, 4),
268 (19, 1, 3, 2),
269 (19, 1, 4, 2),
270 (19, 1, 5, 2),
271 (19, 1, 7, 1),
272 (20, 1, 1, 1),
273 (20, 1, 2, 2),
274 (20, 1, 3, 1),
275 (20, 1, 4, 1),
276 (20, 1, 5, 1),
277 (21, 1, 5, -1);
279 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
280 ( 1, 2),
281 ( 2, 2),
282 ( 3, 2),
283 ( 4, 2),
284 ( 5, 2),
285 ( 6, 2),
286 ( 7, 2),
287 ( 8, 2),
288 ( 9, 2),
289 (10, 2),
290 (11, 2),
291 (12, 2),
292 (13, 2),
293 (14, 2),
294 (15, 2),
295 (16, 2),
296 (17, 2),
297 (18, 2),
298 (19, 2),
299 (20, 2);
301 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
302 ( 1, 2, 8, 2),
303 ( 1, 2, 9, 1),
304 ( 2, 2, 8, 2),
305 ( 2, 2, 9, 1),
306 ( 3, 2, 8, 2),
307 ( 3, 2, 9, 1),
308 ( 4, 2, 8, 2),
309 ( 4, 2, 9, 1),
310 ( 5, 2, 8, 2),
311 ( 5, 2, 9, 1),
312 ( 6, 2, 8, 2),
313 ( 6, 2, 9, 1),
314 ( 7, 2, 8, 2),
315 ( 7, 2, 9, 1),
316 ( 8, 2, 8, 2),
317 ( 8, 2, 9, 1),
318 ( 9, 2, 8, 2),
319 ( 9, 2, 9, 1),
320 (10, 2, 8, -1),
321 (10, 2, 9, 1),
322 (11, 2, 8, -1),
323 (11, 2, 9, 1),
324 (12, 2, 8, -1),
325 (12, 2, 9, 1),
326 (13, 2, 8, -1),
327 (13, 2, 9, 1),
328 (14, 2, 8, -1),
329 (14, 2, 9, 1),
330 (15, 2, 8, -1),
331 (15, 2, 9, 1),
332 (16, 2, 8, -1),
333 (16, 2, 9, 1),
334 (17, 2, 8, -1),
335 (17, 2, 9, -2),
336 (18, 2, 8, -1),
337 (18, 2, 9, -2),
338 (19, 2, 8, -1),
339 (19, 2, 9, -2),
340 (20, 2, 8, -1),
341 (20, 2, 9, -2);
343 SELECT "time_warp"();
345 DROP FUNCTION "time_warp"();
347 -- Test policies that help with testing specific frontend parts
349 INSERT INTO "policy" (
350 "index",
351 "active",
352 "name",
353 "description",
354 "admission_time",
355 "discussion_time",
356 "verification_time",
357 "voting_time",
358 "issue_quorum_num",
359 "issue_quorum_den",
360 "initiative_quorum_num",
361 "initiative_quorum_den"
362 ) VALUES (
363 1,
364 TRUE,
365 'Test New',
366 DEFAULT,
367 '2 days',
368 '1 second',
369 '1 second',
370 '1 second',
371 0, 100,
372 0, 100
373 ), (
374 1,
375 TRUE,
376 'Test Accept',
377 DEFAULT,
378 '1 second',
379 '2 days',
380 '1 second',
381 '1 second',
382 0, 100,
383 0, 100
384 ), (
385 1,
386 TRUE,
387 'Test Frozen',
388 DEFAULT,
389 '1 second',
390 '5 minutes',
391 '2 days',
392 '1 second',
393 0, 100,
394 0, 100
395 ), (
396 1,
397 TRUE,
398 'Test Voting',
399 DEFAULT,
400 '1 second',
401 '5 minutes',
402 '1 second',
403 '2 days',
404 0, 100,
405 0, 100
406 );
407 END;