rev |
line source |
jbe@0
|
1 -- NOTE: This file requires that sequence generators have not been used.
|
jbe@0
|
2 -- (All new rows need to start with id '1'.)
|
jbe@0
|
3
|
jbe@0
|
4 BEGIN;
|
jbe@0
|
5
|
jbe@0
|
6 INSERT INTO "member" ("login", "name") VALUES
|
jbe@0
|
7 ('user1', 'User #1'), -- id 1
|
jbe@0
|
8 ('user2', 'User #2'), -- id 2
|
jbe@0
|
9 ('user3', 'User #3'), -- id 3
|
jbe@0
|
10 ('user4', 'User #4'), -- id 4
|
jbe@0
|
11 ('user5', 'User #5'), -- id 5
|
jbe@0
|
12 ('user6', 'User #6'), -- id 6
|
jbe@0
|
13 ('user7', 'User #7'), -- id 7
|
jbe@0
|
14 ('user8', 'User #8'), -- id 8
|
jbe@0
|
15 ('user9', 'User #9'), -- id 9
|
jbe@0
|
16 ('user10', 'User #10'), -- id 10
|
jbe@0
|
17 ('user11', 'User #11'), -- id 11
|
jbe@0
|
18 ('user12', 'User #12'), -- id 12
|
jbe@0
|
19 ('user13', 'User #13'), -- id 13
|
jbe@0
|
20 ('user14', 'User #14'), -- id 14
|
jbe@0
|
21 ('user15', 'User #15'), -- id 15
|
jbe@0
|
22 ('user16', 'User #16'), -- id 16
|
jbe@0
|
23 ('user17', 'User #17'), -- id 17
|
jbe@0
|
24 ('user18', 'User #18'), -- id 18
|
jbe@0
|
25 ('user19', 'User #19'), -- id 19
|
jbe@0
|
26 ('user20', 'User #20'), -- id 20
|
jbe@0
|
27 ('user21', 'User #21'), -- id 21
|
jbe@0
|
28 ('user22', 'User #22'), -- id 22
|
jbe@0
|
29 ('user23', 'User #23'); -- id 23
|
jbe@0
|
30
|
poelzi@77
|
31 -- set password to "login"
|
poelzi@77
|
32 UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
|
jbe@0
|
33
|
jbe@0
|
34 INSERT INTO "policy" (
|
jbe@9
|
35 "index",
|
jbe@0
|
36 "name",
|
jbe@3
|
37 "admission_time",
|
jbe@3
|
38 "discussion_time",
|
jbe@3
|
39 "verification_time",
|
jbe@3
|
40 "voting_time",
|
jbe@0
|
41 "issue_quorum_num", "issue_quorum_den",
|
jbe@0
|
42 "initiative_quorum_num", "initiative_quorum_den"
|
jbe@0
|
43 ) VALUES (
|
jbe@9
|
44 1,
|
jbe@0
|
45 'Default policy',
|
jbe@3
|
46 '1 hour', '1 hour', '1 hour', '1 hour',
|
jbe@0
|
47 25, 100,
|
jbe@0
|
48 20, 100 );
|
jbe@0
|
49
|
jbe@3
|
50 CREATE FUNCTION "time_warp"() RETURNS VOID
|
jbe@3
|
51 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@3
|
52 BEGIN
|
jbe@3
|
53 UPDATE "issue" SET
|
jbe@3
|
54 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
|
jbe@3
|
55 "created" = "created" - '1 hour 1 minute'::INTERVAL,
|
jbe@3
|
56 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
|
jbe@3
|
57 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
|
jbe@3
|
58 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
|
jbe@3
|
59 PERFORM "check_everything"();
|
jbe@3
|
60 RETURN;
|
jbe@3
|
61 END;
|
jbe@3
|
62 $$;
|
jbe@3
|
63
|
jbe@97
|
64 INSERT INTO "unit" ("name") VALUES ('Main');
|
jbe@97
|
65
|
jbe@97
|
66 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
|
jbe@97
|
67 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
|
jbe@97
|
68 FROM "member";
|
jbe@97
|
69
|
jbe@97
|
70 INSERT INTO "area" ("unit_id", "name") VALUES
|
jbe@97
|
71 (1, 'Area #1'), -- id 1
|
jbe@97
|
72 (1, 'Area #2'), -- id 2
|
jbe@97
|
73 (1, 'Area #3'), -- id 3
|
jbe@97
|
74 (1, 'Area #4'); -- id 4
|
jbe@0
|
75
|
jbe@9
|
76 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
|
jbe@9
|
77 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
|
jbe@9
|
78
|
jbe@0
|
79 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
|
jbe@0
|
80 (1, 9, FALSE),
|
jbe@0
|
81 (1, 19, FALSE),
|
jbe@0
|
82 (2, 9, TRUE),
|
jbe@0
|
83 (2, 10, TRUE),
|
jbe@0
|
84 (2, 17, TRUE),
|
jbe@0
|
85 (3, 9, FALSE),
|
jbe@0
|
86 (3, 11, FALSE),
|
jbe@0
|
87 (3, 12, TRUE),
|
jbe@0
|
88 (3, 14, FALSE),
|
jbe@0
|
89 (3, 20, FALSE),
|
jbe@0
|
90 (3, 21, TRUE),
|
jbe@0
|
91 (3, 22, TRUE),
|
jbe@0
|
92 (4, 6, FALSE),
|
jbe@0
|
93 (4, 9, FALSE),
|
jbe@0
|
94 (4, 13, FALSE),
|
jbe@0
|
95 (4, 22, TRUE);
|
jbe@0
|
96
|
jbe@0
|
97 -- global delegations
|
jbe@0
|
98 INSERT INTO "delegation"
|
jbe@97
|
99 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
|
jbe@97
|
100 ( 1, 'unit', 1, 9),
|
jbe@97
|
101 ( 2, 'unit', 1, 11),
|
jbe@97
|
102 ( 3, 'unit', 1, 12),
|
jbe@97
|
103 ( 4, 'unit', 1, 13),
|
jbe@97
|
104 ( 5, 'unit', 1, 14),
|
jbe@97
|
105 ( 6, 'unit', 1, 7),
|
jbe@97
|
106 ( 7, 'unit', 1, 8),
|
jbe@97
|
107 ( 8, 'unit', 1, 6),
|
jbe@97
|
108 (10, 'unit', 1, 9),
|
jbe@97
|
109 (11, 'unit', 1, 9),
|
jbe@97
|
110 (12, 'unit', 1, 21),
|
jbe@97
|
111 (15, 'unit', 1, 10),
|
jbe@97
|
112 (16, 'unit', 1, 17),
|
jbe@97
|
113 (17, 'unit', 1, 19),
|
jbe@97
|
114 (18, 'unit', 1, 19),
|
jbe@97
|
115 (23, 'unit', 1, 22);
|
jbe@0
|
116
|
jbe@0
|
117 -- delegations for topics
|
jbe@0
|
118 INSERT INTO "delegation"
|
jbe@10
|
119 ("area_id", "truster_id", "scope", "trustee_id") VALUES
|
jbe@10
|
120 (1, 3, 'area', 17),
|
jbe@10
|
121 (2, 5, 'area', 10),
|
jbe@10
|
122 (2, 9, 'area', 10),
|
jbe@10
|
123 (3, 4, 'area', 14),
|
jbe@10
|
124 (3, 16, 'area', 20),
|
jbe@10
|
125 (3, 19, 'area', 20),
|
jbe@10
|
126 (4, 5, 'area', 13),
|
jbe@10
|
127 (4, 12, 'area', 22);
|
jbe@0
|
128
|
jbe@0
|
129 INSERT INTO "issue" ("area_id", "policy_id") VALUES
|
jbe@0
|
130 (3, 1); -- id 1
|
jbe@0
|
131
|
jbe@0
|
132 INSERT INTO "initiative" ("issue_id", "name") VALUES
|
jbe@0
|
133 (1, 'Initiative #1'), -- id 1
|
jbe@0
|
134 (1, 'Initiative #2'), -- id 2
|
jbe@0
|
135 (1, 'Initiative #3'), -- id 3
|
jbe@0
|
136 (1, 'Initiative #4'), -- id 4
|
jbe@0
|
137 (1, 'Initiative #5'), -- id 5
|
jbe@0
|
138 (1, 'Initiative #6'), -- id 6
|
jbe@0
|
139 (1, 'Initiative #7'); -- id 7
|
jbe@0
|
140
|
jbe@0
|
141 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
|
jbe@0
|
142 (1, 17, 'Lorem ipsum...'), -- id 1
|
jbe@0
|
143 (2, 20, 'Lorem ipsum...'), -- id 2
|
jbe@0
|
144 (3, 20, 'Lorem ipsum...'), -- id 3
|
jbe@0
|
145 (4, 20, 'Lorem ipsum...'), -- id 4
|
jbe@0
|
146 (5, 14, 'Lorem ipsum...'), -- id 5
|
jbe@0
|
147 (6, 11, 'Lorem ipsum...'), -- id 6
|
jbe@0
|
148 (7, 12, 'Lorem ipsum...'); -- id 7
|
jbe@0
|
149
|
jbe@0
|
150 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
|
jbe@0
|
151 (1, 17),
|
jbe@0
|
152 (1, 19),
|
jbe@0
|
153 (2, 20),
|
jbe@0
|
154 (3, 20),
|
jbe@0
|
155 (4, 20),
|
jbe@0
|
156 (5, 14),
|
jbe@0
|
157 (6, 11),
|
jbe@0
|
158 (7, 12);
|
jbe@0
|
159
|
jbe@0
|
160 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
|
jbe@0
|
161 ( 7, 4, 4),
|
jbe@0
|
162 ( 8, 2, 2),
|
jbe@0
|
163 (11, 6, 6),
|
jbe@0
|
164 (12, 7, 7),
|
jbe@0
|
165 (14, 1, 1),
|
jbe@0
|
166 (14, 2, 2),
|
jbe@0
|
167 (14, 3, 3),
|
jbe@0
|
168 (14, 4, 4),
|
jbe@0
|
169 (14, 5, 5),
|
jbe@0
|
170 (14, 6, 6),
|
jbe@0
|
171 (14, 7, 7),
|
jbe@0
|
172 (17, 1, 1),
|
jbe@0
|
173 (17, 3, 3),
|
jbe@0
|
174 (19, 1, 1),
|
jbe@0
|
175 (19, 2, 2),
|
jbe@0
|
176 (20, 1, 1),
|
jbe@0
|
177 (20, 2, 2),
|
jbe@0
|
178 (20, 3, 3),
|
jbe@0
|
179 (20, 4, 4),
|
jbe@0
|
180 (20, 5, 5);
|
jbe@0
|
181
|
jbe@0
|
182 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
|
jbe@0
|
183 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
|
jbe@0
|
184 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
|
jbe@0
|
185 (14, 1, 2, FALSE);
|
jbe@0
|
186 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
|
jbe@0
|
187 (19, 1, 2, FALSE);
|
jbe@0
|
188
|
jbe@134
|
189 INSERT INTO "issue" ("area_id", "policy_id") VALUES
|
jbe@134
|
190 (4, 1); -- id 2
|
jbe@134
|
191
|
jbe@134
|
192 INSERT INTO "initiative" ("issue_id", "name") VALUES
|
jbe@134
|
193 (2, 'Initiative A'), -- id 8
|
jbe@134
|
194 (2, 'Initiative B'); -- id 9
|
jbe@134
|
195
|
jbe@134
|
196 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
|
jbe@134
|
197 (8, 1, 'Lorem ipsum...'), -- id 8
|
jbe@134
|
198 (9, 2, 'Lorem ipsum...'); -- id 9
|
jbe@134
|
199
|
jbe@134
|
200 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
|
jbe@134
|
201 (8, 1),
|
jbe@134
|
202 (9, 2);
|
jbe@134
|
203
|
jbe@134
|
204 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
|
jbe@134
|
205 (1, 8, 8),
|
jbe@134
|
206 (1, 9, 9),
|
jbe@134
|
207 (2, 8, 8),
|
jbe@134
|
208 (2, 9, 9),
|
jbe@134
|
209 (3, 8, 8),
|
jbe@134
|
210 (3, 9, 9),
|
jbe@134
|
211 (4, 8, 8),
|
jbe@134
|
212 (4, 9, 9),
|
jbe@134
|
213 (5, 8, 8),
|
jbe@134
|
214 (5, 9, 9),
|
jbe@134
|
215 (6, 8, 8),
|
jbe@134
|
216 (6, 9, 9);
|
jbe@134
|
217
|
jbe@3
|
218 SELECT "time_warp"();
|
jbe@3
|
219 SELECT "time_warp"();
|
jbe@3
|
220 SELECT "time_warp"();
|
jbe@0
|
221
|
jbe@0
|
222 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
|
jbe@0
|
223 ( 8, 1),
|
jbe@0
|
224 ( 9, 1),
|
jbe@0
|
225 (11, 1),
|
jbe@0
|
226 (12, 1),
|
jbe@0
|
227 (14, 1),
|
jbe@0
|
228 (19, 1),
|
jbe@0
|
229 (20, 1),
|
jbe@0
|
230 (21, 1);
|
jbe@0
|
231
|
jbe@0
|
232 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
|
jbe@0
|
233 ( 8, 1, 1, 1),
|
jbe@0
|
234 ( 8, 1, 2, 1),
|
jbe@0
|
235 ( 8, 1, 3, 1),
|
jbe@0
|
236 ( 8, 1, 4, 1),
|
jbe@0
|
237 ( 8, 1, 5, 1),
|
jbe@0
|
238 ( 8, 1, 6, -1),
|
jbe@0
|
239 ( 8, 1, 7, -1),
|
jbe@0
|
240 ( 9, 1, 1, -2),
|
jbe@0
|
241 ( 9, 1, 2, -3),
|
jbe@0
|
242 ( 9, 1, 3, -2),
|
jbe@0
|
243 ( 9, 1, 4, -2),
|
jbe@0
|
244 ( 9, 1, 5, -2),
|
jbe@0
|
245 ( 9, 1, 6, -1),
|
jbe@0
|
246 (11, 1, 1, -1),
|
jbe@0
|
247 (11, 1, 2, -1),
|
jbe@0
|
248 (11, 1, 3, -1),
|
jbe@0
|
249 (11, 1, 4, -1),
|
jbe@0
|
250 (11, 1, 5, -1),
|
jbe@0
|
251 (11, 1, 6, 2),
|
jbe@0
|
252 (11, 1, 7, 1),
|
jbe@0
|
253 (12, 1, 1, -1),
|
jbe@0
|
254 (12, 1, 3, -1),
|
jbe@0
|
255 (12, 1, 4, -1),
|
jbe@0
|
256 (12, 1, 5, -1),
|
jbe@0
|
257 (12, 1, 6, -2),
|
jbe@0
|
258 (12, 1, 7, 1),
|
jbe@0
|
259 (14, 1, 1, 1),
|
jbe@0
|
260 (14, 1, 2, 3),
|
jbe@0
|
261 (14, 1, 3, 1),
|
jbe@0
|
262 (14, 1, 4, 2),
|
jbe@0
|
263 (14, 1, 5, 1),
|
jbe@0
|
264 (14, 1, 6, 1),
|
jbe@0
|
265 (14, 1, 7, 1),
|
jbe@0
|
266 (19, 1, 1, 3),
|
jbe@0
|
267 (19, 1, 2, 4),
|
jbe@0
|
268 (19, 1, 3, 2),
|
jbe@0
|
269 (19, 1, 4, 2),
|
jbe@0
|
270 (19, 1, 5, 2),
|
jbe@0
|
271 (19, 1, 7, 1),
|
jbe@0
|
272 (20, 1, 1, 1),
|
jbe@0
|
273 (20, 1, 2, 2),
|
jbe@0
|
274 (20, 1, 3, 1),
|
jbe@0
|
275 (20, 1, 4, 1),
|
jbe@0
|
276 (20, 1, 5, 1),
|
jbe@0
|
277 (21, 1, 5, -1);
|
jbe@0
|
278
|
jbe@134
|
279 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
|
jbe@134
|
280 ( 1, 2),
|
jbe@134
|
281 ( 2, 2),
|
jbe@134
|
282 ( 3, 2),
|
jbe@134
|
283 ( 4, 2),
|
jbe@134
|
284 ( 5, 2),
|
jbe@134
|
285 ( 6, 2),
|
jbe@134
|
286 ( 7, 2),
|
jbe@134
|
287 ( 8, 2),
|
jbe@134
|
288 ( 9, 2),
|
jbe@134
|
289 (10, 2),
|
jbe@134
|
290 (11, 2),
|
jbe@134
|
291 (12, 2),
|
jbe@134
|
292 (13, 2),
|
jbe@134
|
293 (14, 2),
|
jbe@134
|
294 (15, 2),
|
jbe@134
|
295 (16, 2),
|
jbe@134
|
296 (17, 2),
|
jbe@134
|
297 (18, 2),
|
jbe@134
|
298 (19, 2),
|
jbe@134
|
299 (20, 2);
|
jbe@134
|
300
|
jbe@134
|
301 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
|
jbe@134
|
302 ( 1, 2, 8, 2),
|
jbe@134
|
303 ( 1, 2, 9, 1),
|
jbe@134
|
304 ( 2, 2, 8, 2),
|
jbe@134
|
305 ( 2, 2, 9, 1),
|
jbe@134
|
306 ( 3, 2, 8, 2),
|
jbe@134
|
307 ( 3, 2, 9, 1),
|
jbe@134
|
308 ( 4, 2, 8, 2),
|
jbe@134
|
309 ( 4, 2, 9, 1),
|
jbe@134
|
310 ( 5, 2, 8, 2),
|
jbe@134
|
311 ( 5, 2, 9, 1),
|
jbe@134
|
312 ( 6, 2, 8, 2),
|
jbe@134
|
313 ( 6, 2, 9, 1),
|
jbe@134
|
314 ( 7, 2, 8, 2),
|
jbe@134
|
315 ( 7, 2, 9, 1),
|
jbe@134
|
316 ( 8, 2, 8, 2),
|
jbe@134
|
317 ( 8, 2, 9, 1),
|
jbe@134
|
318 ( 9, 2, 8, 2),
|
jbe@134
|
319 ( 9, 2, 9, 1),
|
jbe@134
|
320 (10, 2, 8, -1),
|
jbe@134
|
321 (10, 2, 9, 1),
|
jbe@134
|
322 (11, 2, 8, -1),
|
jbe@134
|
323 (11, 2, 9, 1),
|
jbe@134
|
324 (12, 2, 8, -1),
|
jbe@134
|
325 (12, 2, 9, 1),
|
jbe@134
|
326 (13, 2, 8, -1),
|
jbe@134
|
327 (13, 2, 9, 1),
|
jbe@134
|
328 (14, 2, 8, -1),
|
jbe@134
|
329 (14, 2, 9, 1),
|
jbe@134
|
330 (15, 2, 8, -1),
|
jbe@134
|
331 (15, 2, 9, 1),
|
jbe@134
|
332 (16, 2, 8, -1),
|
jbe@134
|
333 (16, 2, 9, 1),
|
jbe@134
|
334 (17, 2, 8, -1),
|
jbe@134
|
335 (17, 2, 9, -2),
|
jbe@134
|
336 (18, 2, 8, -1),
|
jbe@134
|
337 (18, 2, 9, -2),
|
jbe@134
|
338 (19, 2, 8, -1),
|
jbe@134
|
339 (19, 2, 9, -2),
|
jbe@134
|
340 (20, 2, 8, -1),
|
jbe@134
|
341 (20, 2, 9, -2);
|
jbe@134
|
342
|
jbe@3
|
343 SELECT "time_warp"();
|
jbe@3
|
344
|
jbe@3
|
345 DROP FUNCTION "time_warp"();
|
jbe@0
|
346
|
poelzi@78
|
347 -- Test policies that help with testing specific frontend parts
|
poelzi@78
|
348
|
poelzi@78
|
349 INSERT INTO "policy" (
|
poelzi@78
|
350 "index",
|
poelzi@78
|
351 "active",
|
poelzi@78
|
352 "name",
|
poelzi@78
|
353 "description",
|
poelzi@78
|
354 "admission_time",
|
poelzi@78
|
355 "discussion_time",
|
poelzi@78
|
356 "verification_time",
|
poelzi@78
|
357 "voting_time",
|
poelzi@78
|
358 "issue_quorum_num",
|
poelzi@78
|
359 "issue_quorum_den",
|
poelzi@78
|
360 "initiative_quorum_num",
|
poelzi@78
|
361 "initiative_quorum_den"
|
poelzi@78
|
362 ) VALUES (
|
poelzi@78
|
363 1,
|
poelzi@78
|
364 TRUE,
|
poelzi@78
|
365 'Test New',
|
poelzi@78
|
366 DEFAULT,
|
poelzi@78
|
367 '2 days',
|
poelzi@78
|
368 '1 second',
|
poelzi@78
|
369 '1 second',
|
poelzi@78
|
370 '1 second',
|
poelzi@82
|
371 0, 100,
|
poelzi@82
|
372 0, 100
|
poelzi@78
|
373 ), (
|
poelzi@78
|
374 1,
|
poelzi@78
|
375 TRUE,
|
poelzi@78
|
376 'Test Accept',
|
poelzi@78
|
377 DEFAULT,
|
poelzi@78
|
378 '1 second',
|
poelzi@78
|
379 '2 days',
|
poelzi@78
|
380 '1 second',
|
poelzi@78
|
381 '1 second',
|
poelzi@82
|
382 0, 100,
|
poelzi@82
|
383 0, 100
|
poelzi@78
|
384 ), (
|
poelzi@78
|
385 1,
|
poelzi@78
|
386 TRUE,
|
poelzi@78
|
387 'Test Frozen',
|
poelzi@78
|
388 DEFAULT,
|
poelzi@78
|
389 '1 second',
|
poelzi@78
|
390 '5 minutes',
|
poelzi@78
|
391 '2 days',
|
poelzi@78
|
392 '1 second',
|
poelzi@82
|
393 0, 100,
|
poelzi@82
|
394 0, 100
|
poelzi@78
|
395 ), (
|
poelzi@78
|
396 1,
|
poelzi@78
|
397 TRUE,
|
poelzi@78
|
398 'Test Voting',
|
poelzi@78
|
399 DEFAULT,
|
poelzi@78
|
400 '1 second',
|
poelzi@78
|
401 '5 minutes',
|
poelzi@78
|
402 '1 second',
|
poelzi@78
|
403 '2 days',
|
poelzi@82
|
404 0, 100,
|
poelzi@82
|
405 0, 100
|
poelzi@78
|
406 );
|
jbe@0
|
407 END;
|
jbe@0
|
408
|