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