liquid_feedback_core
diff test.sql @ 191:44e489d77306
Renamed demo.sql to test.sql
author | jbe |
---|---|
date | Sun Sep 04 16:39:13 2011 +0200 (2011-09-04) |
parents | demo.sql@af3d208e81be |
children | 548cec6b7a79 |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/test.sql Sun Sep 04 16:39:13 2011 +0200 1.3 @@ -0,0 +1,470 @@ 1.4 +-- NOTE: This file requires that sequence generators have not been used. 1.5 +-- (All new rows need to start with id '1'.) 1.6 + 1.7 +BEGIN; 1.8 + 1.9 +INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES 1.10 + ('now', 'now', TRUE, 'user1', 'User #1'), -- id 1 1.11 + ('now', 'now', TRUE, 'user2', 'User #2'), -- id 2 1.12 + ('now', 'now', TRUE, 'user3', 'User #3'), -- id 3 1.13 + ('now', 'now', TRUE, 'user4', 'User #4'), -- id 4 1.14 + ('now', 'now', TRUE, 'user5', 'User #5'), -- id 5 1.15 + ('now', 'now', TRUE, 'user6', 'User #6'), -- id 6 1.16 + ('now', 'now', TRUE, 'user7', 'User #7'), -- id 7 1.17 + ('now', 'now', TRUE, 'user8', 'User #8'), -- id 8 1.18 + ('now', 'now', TRUE, 'user9', 'User #9'), -- id 9 1.19 + ('now', 'now', TRUE, 'user10', 'User #10'), -- id 10 1.20 + ('now', 'now', TRUE, 'user11', 'User #11'), -- id 11 1.21 + ('now', 'now', TRUE, 'user12', 'User #12'), -- id 12 1.22 + ('now', 'now', TRUE, 'user13', 'User #13'), -- id 13 1.23 + ('now', 'now', TRUE, 'user14', 'User #14'), -- id 14 1.24 + ('now', 'now', TRUE, 'user15', 'User #15'), -- id 15 1.25 + ('now', 'now', TRUE, 'user16', 'User #16'), -- id 16 1.26 + ('now', 'now', TRUE, 'user17', 'User #17'), -- id 17 1.27 + ('now', 'now', TRUE, 'user18', 'User #18'), -- id 18 1.28 + ('now', 'now', TRUE, 'user19', 'User #19'), -- id 19 1.29 + ('now', 'now', TRUE, 'user20', 'User #20'), -- id 20 1.30 + ('now', 'now', TRUE, 'user21', 'User #21'), -- id 21 1.31 + ('now', 'now', TRUE, 'user22', 'User #22'), -- id 22 1.32 + ('now', 'now', TRUE, 'user23', 'User #23'); -- id 23 1.33 + 1.34 +-- set password to "login" 1.35 +UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0'; 1.36 + 1.37 +INSERT INTO "policy" ( 1.38 + "index", 1.39 + "name", 1.40 + "admission_time", 1.41 + "discussion_time", 1.42 + "verification_time", 1.43 + "voting_time", 1.44 + "issue_quorum_num", "issue_quorum_den", 1.45 + "initiative_quorum_num", "initiative_quorum_den", 1.46 + "direct_majority_num", "direct_majority_den", "direct_majority_strict", 1.47 + "no_reverse_beat_path", "no_multistage_majority" 1.48 + ) VALUES ( 1.49 + 1, 1.50 + 'Default policy', 1.51 + '1 hour', '1 hour', '1 hour', '1 hour', 1.52 + 25, 100, 1.53 + 20, 100, 1.54 + 1, 2, TRUE, 1.55 + TRUE, FALSE ); 1.56 + 1.57 +CREATE FUNCTION "time_warp"() RETURNS VOID 1.58 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.59 + BEGIN 1.60 + UPDATE "issue" SET 1.61 + "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL, 1.62 + "created" = "created" - '1 hour 1 minute'::INTERVAL, 1.63 + "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL, 1.64 + "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL, 1.65 + "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL; 1.66 + PERFORM "check_everything"(); 1.67 + RETURN; 1.68 + END; 1.69 + $$; 1.70 + 1.71 +INSERT INTO "unit" ("name") VALUES ('Main'); 1.72 + 1.73 +INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") 1.74 + SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" 1.75 + FROM "member"; 1.76 + 1.77 +INSERT INTO "area" ("unit_id", "name") VALUES 1.78 + (1, 'Area #1'), -- id 1 1.79 + (1, 'Area #2'), -- id 2 1.80 + (1, 'Area #3'), -- id 3 1.81 + (1, 'Area #4'); -- id 4 1.82 + 1.83 +INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy") 1.84 + VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE); 1.85 + 1.86 +INSERT INTO "membership" ("area_id", "member_id") VALUES 1.87 + (1, 9), 1.88 + (1, 19), 1.89 + (2, 9), 1.90 + (2, 10), 1.91 + (2, 17), 1.92 + (3, 9), 1.93 + (3, 11), 1.94 + (3, 12), 1.95 + (3, 14), 1.96 + (3, 20), 1.97 + (3, 21), 1.98 + (3, 22), 1.99 + (4, 6), 1.100 + (4, 9), 1.101 + (4, 13), 1.102 + (4, 22); 1.103 + 1.104 +-- global delegations 1.105 +INSERT INTO "delegation" 1.106 + ("truster_id", "scope", "unit_id", "trustee_id") VALUES 1.107 + ( 1, 'unit', 1, 9), 1.108 + ( 2, 'unit', 1, 11), 1.109 + ( 3, 'unit', 1, 12), 1.110 + ( 4, 'unit', 1, 13), 1.111 + ( 5, 'unit', 1, 14), 1.112 + ( 6, 'unit', 1, 7), 1.113 + ( 7, 'unit', 1, 8), 1.114 + ( 8, 'unit', 1, 6), 1.115 + (10, 'unit', 1, 9), 1.116 + (11, 'unit', 1, 9), 1.117 + (12, 'unit', 1, 21), 1.118 + (15, 'unit', 1, 10), 1.119 + (16, 'unit', 1, 17), 1.120 + (17, 'unit', 1, 19), 1.121 + (18, 'unit', 1, 19), 1.122 + (23, 'unit', 1, 22); 1.123 + 1.124 +-- delegations for topics 1.125 +INSERT INTO "delegation" 1.126 + ("area_id", "truster_id", "scope", "trustee_id") VALUES 1.127 + (1, 3, 'area', 17), 1.128 + (2, 5, 'area', 10), 1.129 + (2, 9, 'area', 10), 1.130 + (3, 4, 'area', 14), 1.131 + (3, 16, 'area', 20), 1.132 + (3, 19, 'area', 20), 1.133 + (4, 5, 'area', 13), 1.134 + (4, 12, 'area', 22); 1.135 + 1.136 +INSERT INTO "issue" ("area_id", "policy_id") VALUES 1.137 + (3, 1); -- id 1 1.138 + 1.139 +INSERT INTO "initiative" ("issue_id", "name") VALUES 1.140 + (1, 'Initiative #1'), -- id 1 1.141 + (1, 'Initiative #2'), -- id 2 1.142 + (1, 'Initiative #3'), -- id 3 1.143 + (1, 'Initiative #4'), -- id 4 1.144 + (1, 'Initiative #5'), -- id 5 1.145 + (1, 'Initiative #6'), -- id 6 1.146 + (1, 'Initiative #7'); -- id 7 1.147 + 1.148 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES 1.149 + (1, 17, 'Lorem ipsum...'), -- id 1 1.150 + (2, 20, 'Lorem ipsum...'), -- id 2 1.151 + (3, 20, 'Lorem ipsum...'), -- id 3 1.152 + (4, 20, 'Lorem ipsum...'), -- id 4 1.153 + (5, 14, 'Lorem ipsum...'), -- id 5 1.154 + (6, 11, 'Lorem ipsum...'), -- id 6 1.155 + (7, 12, 'Lorem ipsum...'); -- id 7 1.156 + 1.157 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES 1.158 + (1, 17), 1.159 + (1, 19), 1.160 + (2, 20), 1.161 + (3, 20), 1.162 + (4, 20), 1.163 + (5, 14), 1.164 + (6, 11), 1.165 + (7, 12); 1.166 + 1.167 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES 1.168 + ( 7, 4, 4), 1.169 + ( 8, 2, 2), 1.170 + (11, 6, 6), 1.171 + (12, 7, 7), 1.172 + (14, 1, 1), 1.173 + (14, 2, 2), 1.174 + (14, 3, 3), 1.175 + (14, 4, 4), 1.176 + (14, 5, 5), 1.177 + (14, 6, 6), 1.178 + (14, 7, 7), 1.179 + (17, 1, 1), 1.180 + (17, 3, 3), 1.181 + (19, 1, 1), 1.182 + (19, 2, 2), 1.183 + (20, 1, 1), 1.184 + (20, 2, 2), 1.185 + (20, 3, 3), 1.186 + (20, 4, 4), 1.187 + (20, 5, 5); 1.188 + 1.189 +INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES 1.190 + (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1 1.191 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES 1.192 + (14, 1, 2, FALSE); 1.193 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES 1.194 + (19, 1, 2, FALSE); 1.195 + 1.196 +INSERT INTO "issue" ("area_id", "policy_id") VALUES 1.197 + (4, 1); -- id 2 1.198 + 1.199 +INSERT INTO "initiative" ("issue_id", "name") VALUES 1.200 + (2, 'Initiative A'), -- id 8 1.201 + (2, 'Initiative B'), -- id 9 1.202 + (2, 'Initiative C'), -- id 10 1.203 + (2, 'Initiative D'); -- id 11 1.204 + 1.205 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES 1.206 + ( 8, 1, 'Lorem ipsum...'), -- id 8 1.207 + ( 9, 2, 'Lorem ipsum...'), -- id 9 1.208 + (10, 3, 'Lorem ipsum...'), -- id 10 1.209 + (11, 4, 'Lorem ipsum...'); -- id 11 1.210 + 1.211 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES 1.212 + ( 8, 1), 1.213 + ( 9, 2), 1.214 + (10, 3), 1.215 + (11, 4); 1.216 + 1.217 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES 1.218 + (1, 8, 8), 1.219 + (1, 9, 9), 1.220 + (1, 10, 10), 1.221 + (1, 11, 11), 1.222 + (2, 8, 8), 1.223 + (2, 9, 9), 1.224 + (2, 10, 10), 1.225 + (2, 11, 11), 1.226 + (3, 8, 8), 1.227 + (3, 9, 9), 1.228 + (3, 10, 10), 1.229 + (3, 11, 11), 1.230 + (4, 8, 8), 1.231 + (4, 9, 9), 1.232 + (4, 10, 10), 1.233 + (4, 11, 11), 1.234 + (5, 8, 8), 1.235 + (5, 9, 9), 1.236 + (5, 10, 10), 1.237 + (5, 11, 11), 1.238 + (6, 8, 8), 1.239 + (6, 9, 9), 1.240 + (6, 10, 10), 1.241 + (6, 11, 11); 1.242 + 1.243 +SELECT "time_warp"(); 1.244 +SELECT "time_warp"(); 1.245 +SELECT "time_warp"(); 1.246 + 1.247 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES 1.248 + ( 8, 1), 1.249 + ( 9, 1), 1.250 + (11, 1), 1.251 + (12, 1), 1.252 + (14, 1), 1.253 + (19, 1), 1.254 + (20, 1), 1.255 + (21, 1); 1.256 + 1.257 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES 1.258 + ( 8, 1, 1, 1), 1.259 + ( 8, 1, 2, 1), 1.260 + ( 8, 1, 3, 1), 1.261 + ( 8, 1, 4, 1), 1.262 + ( 8, 1, 5, 1), 1.263 + ( 8, 1, 6, -1), 1.264 + ( 8, 1, 7, -1), 1.265 + ( 9, 1, 1, -2), 1.266 + ( 9, 1, 2, -3), 1.267 + ( 9, 1, 3, -2), 1.268 + ( 9, 1, 4, -2), 1.269 + ( 9, 1, 5, -2), 1.270 + ( 9, 1, 6, -1), 1.271 + (11, 1, 1, -1), 1.272 + (11, 1, 2, -1), 1.273 + (11, 1, 3, -1), 1.274 + (11, 1, 4, -1), 1.275 + (11, 1, 5, -1), 1.276 + (11, 1, 6, 2), 1.277 + (11, 1, 7, 1), 1.278 + (12, 1, 1, -1), 1.279 + (12, 1, 3, -1), 1.280 + (12, 1, 4, -1), 1.281 + (12, 1, 5, -1), 1.282 + (12, 1, 6, -2), 1.283 + (12, 1, 7, 1), 1.284 + (14, 1, 1, 1), 1.285 + (14, 1, 2, 3), 1.286 + (14, 1, 3, 1), 1.287 + (14, 1, 4, 2), 1.288 + (14, 1, 5, 1), 1.289 + (14, 1, 6, 1), 1.290 + (14, 1, 7, 1), 1.291 + (19, 1, 1, 3), 1.292 + (19, 1, 2, 4), 1.293 + (19, 1, 3, 2), 1.294 + (19, 1, 4, 2), 1.295 + (19, 1, 5, 2), 1.296 + (19, 1, 7, 1), 1.297 + (20, 1, 1, 1), 1.298 + (20, 1, 2, 2), 1.299 + (20, 1, 3, 1), 1.300 + (20, 1, 4, 1), 1.301 + (20, 1, 5, 1), 1.302 + (21, 1, 5, -1); 1.303 + 1.304 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES 1.305 + ( 1, 2), 1.306 + ( 2, 2), 1.307 + ( 3, 2), 1.308 + ( 4, 2), 1.309 + ( 5, 2), 1.310 + ( 6, 2), 1.311 + ( 7, 2), 1.312 + ( 8, 2), 1.313 + ( 9, 2), 1.314 + (10, 2), 1.315 + (11, 2), 1.316 + (12, 2), 1.317 + (13, 2), 1.318 + (14, 2), 1.319 + (15, 2), 1.320 + (16, 2), 1.321 + (17, 2), 1.322 + (18, 2), 1.323 + (19, 2), 1.324 + (20, 2); 1.325 + 1.326 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES 1.327 + ( 1, 2, 8, 3), 1.328 + ( 1, 2, 9, 4), 1.329 + ( 1, 2, 10, 2), 1.330 + ( 1, 2, 11, 1), 1.331 + ( 2, 2, 8, 3), 1.332 + ( 2, 2, 9, 4), 1.333 + ( 2, 2, 10, 2), 1.334 + ( 2, 2, 11, 1), 1.335 + ( 3, 2, 8, 4), 1.336 + ( 3, 2, 9, 3), 1.337 + ( 3, 2, 10, 2), 1.338 + ( 3, 2, 11, 1), 1.339 + ( 4, 2, 8, 4), 1.340 + ( 4, 2, 9, 3), 1.341 + ( 4, 2, 10, 2), 1.342 + ( 4, 2, 11, 1), 1.343 + ( 5, 2, 8, 4), 1.344 + ( 5, 2, 9, 3), 1.345 + ( 5, 2, 10, 2), 1.346 + ( 5, 2, 11, 1), 1.347 + ( 6, 2, 8, 4), 1.348 + ( 6, 2, 9, 3), 1.349 + ( 6, 2, 10, 2), 1.350 + ( 6, 2, 11, 1), 1.351 + ( 7, 2, 8, 4), 1.352 + ( 7, 2, 9, 3), 1.353 + ( 7, 2, 10, 2), 1.354 + ( 7, 2, 11, 1), 1.355 + ( 8, 2, 8, 4), 1.356 + ( 8, 2, 9, 3), 1.357 + ( 8, 2, 10, 2), 1.358 + ( 8, 2, 11, 1), 1.359 + ( 9, 2, 8, -1), 1.360 + ( 9, 2, 9, 1), 1.361 + ( 9, 2, 10, 3), 1.362 + ( 9, 2, 11, 2), 1.363 + (10, 2, 8, -1), 1.364 + (10, 2, 9, 1), 1.365 + (10, 2, 10, 3), 1.366 + (10, 2, 11, 2), 1.367 + (11, 2, 8, -1), 1.368 + (11, 2, 9, 1), 1.369 + (11, 2, 10, 3), 1.370 + (11, 2, 11, 2), 1.371 + (12, 2, 8, -1), 1.372 + (12, 2, 9, 1), 1.373 + (12, 2, 10, 3), 1.374 + (12, 2, 11, 2), 1.375 + (13, 2, 8, -1), 1.376 + (13, 2, 9, 1), 1.377 + (13, 2, 10, 3), 1.378 + (13, 2, 11, 2), 1.379 + (14, 2, 8, -1), 1.380 + (14, 2, 9, 1), 1.381 + (14, 2, 10, 3), 1.382 + (14, 2, 11, 2), 1.383 + (15, 2, 8, -1), 1.384 + (15, 2, 9, -3), 1.385 + (15, 2, 10, -4), 1.386 + (15, 2, 11, -2), 1.387 + (16, 2, 8, -1), 1.388 + (16, 2, 9, -3), 1.389 + (16, 2, 10, -4), 1.390 + (16, 2, 11, -2), 1.391 + (17, 2, 8, -1), 1.392 + (17, 2, 9, -3), 1.393 + (17, 2, 10, -4), 1.394 + (17, 2, 11, -2), 1.395 + (18, 2, 8, -1), 1.396 + (18, 2, 9, 1), 1.397 + (18, 2, 10, -2), 1.398 + (18, 2, 11, 2), 1.399 + (19, 2, 8, -1), 1.400 + (19, 2, 9, 1), 1.401 + (19, 2, 10, -2), 1.402 + (19, 2, 11, 2), 1.403 + (20, 2, 8, 1), 1.404 + (20, 2, 9, 2), 1.405 + (20, 2, 10, -1), 1.406 + (20, 2, 11, 3); 1.407 + 1.408 +SELECT "time_warp"(); 1.409 + 1.410 +DROP FUNCTION "time_warp"(); 1.411 + 1.412 +-- Test policies that help with testing specific frontend parts 1.413 + 1.414 +INSERT INTO "policy" ( 1.415 + "index", 1.416 + "active", 1.417 + "name", 1.418 + "description", 1.419 + "admission_time", 1.420 + "discussion_time", 1.421 + "verification_time", 1.422 + "voting_time", 1.423 + "issue_quorum_num", 1.424 + "issue_quorum_den", 1.425 + "initiative_quorum_num", 1.426 + "initiative_quorum_den" 1.427 + ) VALUES ( 1.428 + 1, 1.429 + TRUE, 1.430 + 'Test New', 1.431 + DEFAULT, 1.432 + '2 days', 1.433 + '1 second', 1.434 + '1 second', 1.435 + '1 second', 1.436 + 0, 100, 1.437 + 0, 100 1.438 + ), ( 1.439 + 1, 1.440 + TRUE, 1.441 + 'Test Accept', 1.442 + DEFAULT, 1.443 + '1 second', 1.444 + '2 days', 1.445 + '1 second', 1.446 + '1 second', 1.447 + 0, 100, 1.448 + 0, 100 1.449 + ), ( 1.450 + 1, 1.451 + TRUE, 1.452 + 'Test Frozen', 1.453 + DEFAULT, 1.454 + '1 second', 1.455 + '5 minutes', 1.456 + '2 days', 1.457 + '1 second', 1.458 + 0, 100, 1.459 + 0, 100 1.460 + ), ( 1.461 + 1, 1.462 + TRUE, 1.463 + 'Test Voting', 1.464 + DEFAULT, 1.465 + '1 second', 1.466 + '5 minutes', 1.467 + '1 second', 1.468 + '2 days', 1.469 + 0, 100, 1.470 + 0, 100 1.471 + ); 1.472 +END; 1.473 +