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 +