
jbe@0: -- NOTE: This file requires that sequence generators have not been used.
jbe@0: -- (All new rows need to start with id '1'.)
jbe@0: 
jbe@0: BEGIN;
jbe@0: 
jbe@0: INSERT INTO "member" ("login", "name") VALUES
jbe@0:   ('user1',  'User #1'),   -- id  1
jbe@0:   ('user2',  'User #2'),   -- id  2
jbe@0:   ('user3',  'User #3'),   -- id  3
jbe@0:   ('user4',  'User #4'),   -- id  4
jbe@0:   ('user5',  'User #5'),   -- id  5
jbe@0:   ('user6',  'User #6'),   -- id  6
jbe@0:   ('user7',  'User #7'),   -- id  7
jbe@0:   ('user8',  'User #8'),   -- id  8
jbe@0:   ('user9',  'User #9'),   -- id  9
jbe@0:   ('user10', 'User #10'),  -- id 10
jbe@0:   ('user11', 'User #11'),  -- id 11
jbe@0:   ('user12', 'User #12'),  -- id 12
jbe@0:   ('user13', 'User #13'),  -- id 13
jbe@0:   ('user14', 'User #14'),  -- id 14
jbe@0:   ('user15', 'User #15'),  -- id 15
jbe@0:   ('user16', 'User #16'),  -- id 16
jbe@0:   ('user17', 'User #17'),  -- id 17
jbe@0:   ('user18', 'User #18'),  -- id 18
jbe@0:   ('user19', 'User #19'),  -- id 19
jbe@0:   ('user20', 'User #20'),  -- id 20
jbe@0:   ('user21', 'User #21'),  -- id 21
jbe@0:   ('user22', 'User #22'),  -- id 22
jbe@0:   ('user23', 'User #23');  -- id 23
jbe@0: 
poelzi@77: -- set password to "login"
poelzi@77: UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
jbe@0: 
jbe@0: INSERT INTO "policy" (
jbe@155:     "index",
jbe@155:     "name",
jbe@155:     "admission_time",
jbe@155:     "discussion_time",
jbe@155:     "verification_time",
jbe@155:     "voting_time",
jbe@155:     "issue_quorum_num", "issue_quorum_den",
jbe@155:     "initiative_quorum_num", "initiative_quorum_den",
jbe@155:     "majority_num", "majority_den", "majority_strict",
jbe@155:     "majority_indirect",
jbe@155:     "no_reverse_beat_path", "no_multistage_majority"
jbe@155:   ) VALUES (
jbe@155:     1,
jbe@155:     'Default policy',
jbe@155:     '1 hour', '1 hour', '1 hour', '1 hour',
jbe@155:     25, 100,
jbe@155:     20, 100,
jbe@155:     1, 2, TRUE,
jbe@155:     TRUE,
jbe@155:     TRUE, FALSE );
jbe@0: 
jbe@3: CREATE FUNCTION "time_warp"() RETURNS VOID
jbe@3:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@3:     BEGIN
jbe@3:       UPDATE "issue" SET
jbe@3:         "snapshot"     = "snapshot"     - '1 hour 1 minute'::INTERVAL,
jbe@3:         "created"      = "created"      - '1 hour 1 minute'::INTERVAL,
jbe@3:         "accepted"     = "accepted"     - '1 hour 1 minute'::INTERVAL,
jbe@3:         "half_frozen"  = "half_frozen"  - '1 hour 1 minute'::INTERVAL,
jbe@3:         "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
jbe@3:       PERFORM "check_everything"();
jbe@3:       RETURN;
jbe@3:     END;
jbe@3:   $$;
jbe@3: 
jbe@97: INSERT INTO "unit" ("name") VALUES ('Main');
jbe@97: 
jbe@97: INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@97:   SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@97:   FROM "member";
jbe@97: 
jbe@97: INSERT INTO "area" ("unit_id", "name") VALUES
jbe@97:   (1, 'Area #1'),  -- id 1
jbe@97:   (1, 'Area #2'),  -- id 2
jbe@97:   (1, 'Area #3'),  -- id 3
jbe@97:   (1, 'Area #4');  -- id 4
jbe@0: 
jbe@9: INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
jbe@9:   VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
jbe@9: 
jbe@0: INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
jbe@0:   (1,  9, FALSE),
jbe@0:   (1, 19, FALSE),
jbe@0:   (2,  9, TRUE),
jbe@0:   (2, 10, TRUE),
jbe@0:   (2, 17, TRUE),
jbe@0:   (3,  9, FALSE),
jbe@0:   (3, 11, FALSE),
jbe@0:   (3, 12, TRUE),
jbe@0:   (3, 14, FALSE),
jbe@0:   (3, 20, FALSE),
jbe@0:   (3, 21, TRUE),
jbe@0:   (3, 22, TRUE),
jbe@0:   (4,  6, FALSE),
jbe@0:   (4,  9, FALSE),
jbe@0:   (4, 13, FALSE),
jbe@139:   (4, 22, FALSE);
jbe@0: 
jbe@0: -- global delegations
jbe@0: INSERT INTO "delegation"
jbe@97:   ("truster_id", "scope", "unit_id", "trustee_id") VALUES
jbe@97:   ( 1, 'unit', 1,  9),
jbe@97:   ( 2, 'unit', 1, 11),
jbe@97:   ( 3, 'unit', 1, 12),
jbe@97:   ( 4, 'unit', 1, 13),
jbe@97:   ( 5, 'unit', 1, 14),
jbe@97:   ( 6, 'unit', 1,  7),
jbe@97:   ( 7, 'unit', 1,  8),
jbe@97:   ( 8, 'unit', 1,  6),
jbe@97:   (10, 'unit', 1,  9),
jbe@97:   (11, 'unit', 1,  9),
jbe@97:   (12, 'unit', 1, 21),
jbe@97:   (15, 'unit', 1, 10),
jbe@97:   (16, 'unit', 1, 17),
jbe@97:   (17, 'unit', 1, 19),
jbe@97:   (18, 'unit', 1, 19),
jbe@97:   (23, 'unit', 1, 22);
jbe@0: 
jbe@0: -- delegations for topics
jbe@0: INSERT INTO "delegation"
jbe@10:   ("area_id", "truster_id", "scope", "trustee_id") VALUES
jbe@10:   (1,  3, 'area', 17),
jbe@10:   (2,  5, 'area', 10),
jbe@10:   (2,  9, 'area', 10),
jbe@10:   (3,  4, 'area', 14),
jbe@10:   (3, 16, 'area', 20),
jbe@10:   (3, 19, 'area', 20),
jbe@10:   (4,  5, 'area', 13),
jbe@10:   (4, 12, 'area', 22);
jbe@0: 
jbe@0: INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@0:   (3, 1);  -- id 1
jbe@0: 
jbe@0: INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@0:   (1, 'Initiative #1'),  -- id 1
jbe@0:   (1, 'Initiative #2'),  -- id 2
jbe@0:   (1, 'Initiative #3'),  -- id 3
jbe@0:   (1, 'Initiative #4'),  -- id 4
jbe@0:   (1, 'Initiative #5'),  -- id 5
jbe@0:   (1, 'Initiative #6'),  -- id 6
jbe@0:   (1, 'Initiative #7');  -- id 7
jbe@0: 
jbe@0: INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@0:   (1, 17, 'Lorem ipsum...'),  -- id 1
jbe@0:   (2, 20, 'Lorem ipsum...'),  -- id 2
jbe@0:   (3, 20, 'Lorem ipsum...'),  -- id 3
jbe@0:   (4, 20, 'Lorem ipsum...'),  -- id 4
jbe@0:   (5, 14, 'Lorem ipsum...'),  -- id 5
jbe@0:   (6, 11, 'Lorem ipsum...'),  -- id 6
jbe@0:   (7, 12, 'Lorem ipsum...');  -- id 7
jbe@0: 
jbe@0: INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@0:   (1, 17),
jbe@0:   (1, 19),
jbe@0:   (2, 20),
jbe@0:   (3, 20),
jbe@0:   (4, 20),
jbe@0:   (5, 14),
jbe@0:   (6, 11),
jbe@0:   (7, 12);
jbe@0: 
jbe@0: INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@0:   ( 7,  4,  4),
jbe@0:   ( 8,  2,  2),
jbe@0:   (11,  6,  6),
jbe@0:   (12,  7,  7),
jbe@0:   (14,  1,  1),
jbe@0:   (14,  2,  2),
jbe@0:   (14,  3,  3),
jbe@0:   (14,  4,  4),
jbe@0:   (14,  5,  5),
jbe@0:   (14,  6,  6),
jbe@0:   (14,  7,  7),
jbe@0:   (17,  1,  1),
jbe@0:   (17,  3,  3),
jbe@0:   (19,  1,  1),
jbe@0:   (19,  2,  2),
jbe@0:   (20,  1,  1),
jbe@0:   (20,  2,  2),
jbe@0:   (20,  3,  3),
jbe@0:   (20,  4,  4),
jbe@0:   (20,  5,  5);
jbe@0: 
jbe@0: INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
jbe@0:   (1, 19, 'Suggestion #1', 'Lorem ipsum...');  -- id 1
jbe@0: INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0:   (14, 1, 2, FALSE);
jbe@0: INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0:   (19, 1, 2, FALSE);
jbe@0: 
jbe@134: INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@134:   (4, 1);  -- id 2
jbe@134: 
jbe@134: INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@139:   (2, 'Initiative A'),  -- id  8
jbe@139:   (2, 'Initiative B'),  -- id  9
jbe@139:   (2, 'Initiative C'),  -- id 10
jbe@139:   (2, 'Initiative D');  -- id 11
jbe@134: 
jbe@134: INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@139:   ( 8, 1, 'Lorem ipsum...'),  -- id  8
jbe@139:   ( 9, 2, 'Lorem ipsum...'),  -- id  9
jbe@139:   (10, 3, 'Lorem ipsum...'),  -- id 10
jbe@139:   (11, 4, 'Lorem ipsum...');  -- id 11
jbe@134: 
jbe@134: INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@139:   ( 8, 1),
jbe@139:   ( 9, 2),
jbe@139:   (10, 3),
jbe@139:   (11, 4);
jbe@134: 
jbe@134: INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@139:   (1,  8,  8),
jbe@139:   (1,  9,  9),
jbe@139:   (1, 10, 10),
jbe@139:   (1, 11, 11),
jbe@139:   (2,  8,  8),
jbe@139:   (2,  9,  9),
jbe@139:   (2, 10, 10),
jbe@139:   (2, 11, 11),
jbe@139:   (3,  8,  8),
jbe@139:   (3,  9,  9),
jbe@139:   (3, 10, 10),
jbe@139:   (3, 11, 11),
jbe@139:   (4,  8,  8),
jbe@139:   (4,  9,  9),
jbe@139:   (4, 10, 10),
jbe@139:   (4, 11, 11),
jbe@139:   (5,  8,  8),
jbe@139:   (5,  9,  9),
jbe@139:   (5, 10, 10),
jbe@139:   (5, 11, 11),
jbe@139:   (6,  8,  8),
jbe@139:   (6,  9,  9),
jbe@139:   (6, 10, 10),
jbe@139:   (6, 11, 11);
jbe@139:  
jbe@3: SELECT "time_warp"();
jbe@3: SELECT "time_warp"();
jbe@3: SELECT "time_warp"();
jbe@0: 
jbe@0: INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0:   ( 8, 1),
jbe@0:   ( 9, 1),
jbe@0:   (11, 1),
jbe@0:   (12, 1),
jbe@0:   (14, 1),
jbe@0:   (19, 1),
jbe@0:   (20, 1),
jbe@0:   (21, 1);
jbe@0: 
jbe@0: INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0:   ( 8, 1, 1,  1),
jbe@0:   ( 8, 1, 2,  1),
jbe@0:   ( 8, 1, 3,  1),
jbe@0:   ( 8, 1, 4,  1),
jbe@0:   ( 8, 1, 5,  1),
jbe@0:   ( 8, 1, 6, -1),
jbe@0:   ( 8, 1, 7, -1),
jbe@0:   ( 9, 1, 1, -2),
jbe@0:   ( 9, 1, 2, -3),
jbe@0:   ( 9, 1, 3, -2),
jbe@0:   ( 9, 1, 4, -2),
jbe@0:   ( 9, 1, 5, -2),
jbe@0:   ( 9, 1, 6, -1),
jbe@0:   (11, 1, 1, -1),
jbe@0:   (11, 1, 2, -1),
jbe@0:   (11, 1, 3, -1),
jbe@0:   (11, 1, 4, -1),
jbe@0:   (11, 1, 5, -1),
jbe@0:   (11, 1, 6,  2),
jbe@0:   (11, 1, 7,  1),
jbe@0:   (12, 1, 1, -1),
jbe@0:   (12, 1, 3, -1),
jbe@0:   (12, 1, 4, -1),
jbe@0:   (12, 1, 5, -1),
jbe@0:   (12, 1, 6, -2),
jbe@0:   (12, 1, 7,  1),
jbe@0:   (14, 1, 1,  1),
jbe@0:   (14, 1, 2,  3),
jbe@0:   (14, 1, 3,  1),
jbe@0:   (14, 1, 4,  2),
jbe@0:   (14, 1, 5,  1),
jbe@0:   (14, 1, 6,  1),
jbe@0:   (14, 1, 7,  1),
jbe@0:   (19, 1, 1,  3),
jbe@0:   (19, 1, 2,  4),
jbe@0:   (19, 1, 3,  2),
jbe@0:   (19, 1, 4,  2),
jbe@0:   (19, 1, 5,  2),
jbe@0:   (19, 1, 7,  1),
jbe@0:   (20, 1, 1,  1),
jbe@0:   (20, 1, 2,  2),
jbe@0:   (20, 1, 3,  1),
jbe@0:   (20, 1, 4,  1),
jbe@0:   (20, 1, 5,  1),
jbe@0:   (21, 1, 5, -1);
jbe@0: 
jbe@134: INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@134:   ( 1, 2),
jbe@134:   ( 2, 2),
jbe@134:   ( 3, 2),
jbe@134:   ( 4, 2),
jbe@134:   ( 5, 2),
jbe@134:   ( 6, 2),
jbe@134:   ( 7, 2),
jbe@134:   ( 8, 2),
jbe@134:   ( 9, 2),
jbe@134:   (10, 2),
jbe@134:   (11, 2),
jbe@134:   (12, 2),
jbe@134:   (13, 2),
jbe@134:   (14, 2),
jbe@134:   (15, 2),
jbe@134:   (16, 2),
jbe@134:   (17, 2),
jbe@134:   (18, 2),
jbe@134:   (19, 2),
jbe@134:   (20, 2);
jbe@134: 
jbe@134: INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@139:   ( 1, 2,  8,  3),
jbe@139:   ( 1, 2,  9,  4),
jbe@139:   ( 1, 2, 10,  2),
jbe@139:   ( 1, 2, 11,  1),
jbe@139:   ( 2, 2,  8,  3),
jbe@139:   ( 2, 2,  9,  4),
jbe@139:   ( 2, 2, 10,  2),
jbe@139:   ( 2, 2, 11,  1),
jbe@139:   ( 3, 2,  8,  4),
jbe@139:   ( 3, 2,  9,  3),
jbe@139:   ( 3, 2, 10,  2),
jbe@139:   ( 3, 2, 11,  1),
jbe@139:   ( 4, 2,  8,  4),
jbe@139:   ( 4, 2,  9,  3),
jbe@139:   ( 4, 2, 10,  2),
jbe@139:   ( 4, 2, 11,  1),
jbe@139:   ( 5, 2,  8,  4),
jbe@139:   ( 5, 2,  9,  3),
jbe@139:   ( 5, 2, 10,  2),
jbe@139:   ( 5, 2, 11,  1),
jbe@139:   ( 6, 2,  8,  4),
jbe@139:   ( 6, 2,  9,  3),
jbe@139:   ( 6, 2, 10,  2),
jbe@139:   ( 6, 2, 11,  1),
jbe@139:   ( 7, 2,  8,  4),
jbe@139:   ( 7, 2,  9,  3),
jbe@139:   ( 7, 2, 10,  2),
jbe@139:   ( 7, 2, 11,  1),
jbe@139:   ( 8, 2,  8,  4),
jbe@139:   ( 8, 2,  9,  3),
jbe@139:   ( 8, 2, 10,  2),
jbe@139:   ( 8, 2, 11,  1),
jbe@139:   ( 9, 2,  8, -1),
jbe@139:   ( 9, 2,  9,  1),
jbe@139:   ( 9, 2, 10,  3),
jbe@139:   ( 9, 2, 11,  2),
jbe@139:   (10, 2,  8, -1),
jbe@139:   (10, 2,  9,  1),
jbe@139:   (10, 2, 10,  3),
jbe@139:   (10, 2, 11,  2),
jbe@139:   (11, 2,  8, -1),
jbe@139:   (11, 2,  9,  1),
jbe@139:   (11, 2, 10,  3),
jbe@139:   (11, 2, 11,  2),
jbe@139:   (12, 2,  8, -1),
jbe@139:   (12, 2,  9,  1),
jbe@139:   (12, 2, 10,  3),
jbe@139:   (12, 2, 11,  2),
jbe@139:   (13, 2,  8, -1),
jbe@139:   (13, 2,  9,  1),
jbe@139:   (13, 2, 10,  3),
jbe@139:   (13, 2, 11,  2),
jbe@139:   (14, 2,  8, -1),
jbe@139:   (14, 2,  9,  1),
jbe@139:   (14, 2, 10,  3),
jbe@139:   (14, 2, 11,  2),
jbe@139:   (15, 2,  8, -1),
jbe@139:   (15, 2,  9, -3),
jbe@139:   (15, 2, 10, -4),
jbe@139:   (15, 2, 11, -2),
jbe@139:   (16, 2,  8, -1),
jbe@139:   (16, 2,  9, -3),
jbe@139:   (16, 2, 10, -4),
jbe@139:   (16, 2, 11, -2),
jbe@139:   (17, 2,  8, -1),
jbe@139:   (17, 2,  9, -3),
jbe@139:   (17, 2, 10, -4),
jbe@139:   (17, 2, 11, -2),
jbe@139:   (18, 2,  8, -1),
jbe@139:   (18, 2,  9,  1),
jbe@139:   (18, 2, 10, -2),
jbe@139:   (18, 2, 11,  2),
jbe@139:   (19, 2,  8, -1),
jbe@139:   (19, 2,  9,  1),
jbe@139:   (19, 2, 10, -2),
jbe@139:   (19, 2, 11,  2),
jbe@139:   (20, 2,  8,  1),
jbe@139:   (20, 2,  9,  2),
jbe@139:   (20, 2, 10, -1),
jbe@139:   (20, 2, 11,  3);
jbe@134: 
jbe@3: SELECT "time_warp"();
jbe@3: 
jbe@3: DROP FUNCTION "time_warp"();
jbe@0: 
poelzi@78: -- Test policies that help with testing specific frontend parts
poelzi@78: 
poelzi@78: INSERT INTO "policy" (
poelzi@78:         "index",
poelzi@78:         "active",
poelzi@78:         "name",
poelzi@78:         "description",
poelzi@78:         "admission_time",
poelzi@78:         "discussion_time",
poelzi@78:         "verification_time",
poelzi@78:         "voting_time",
poelzi@78:         "issue_quorum_num",
poelzi@78:         "issue_quorum_den",
poelzi@78:         "initiative_quorum_num",
poelzi@78:         "initiative_quorum_den"
poelzi@78:     ) VALUES (
poelzi@78:         1,
poelzi@78:         TRUE,
poelzi@78:         'Test New',
poelzi@78:         DEFAULT,
poelzi@78:         '2 days',
poelzi@78:         '1 second',
poelzi@78:         '1 second',
poelzi@78:         '1 second',
poelzi@82:         0, 100,
poelzi@82:         0, 100
poelzi@78:     ), (
poelzi@78:         1,
poelzi@78:         TRUE,
poelzi@78:         'Test Accept',
poelzi@78:         DEFAULT,
poelzi@78:         '1 second',
poelzi@78:         '2 days',
poelzi@78:         '1 second',
poelzi@78:         '1 second',
poelzi@82:         0, 100,
poelzi@82:         0, 100
poelzi@78:     ), (
poelzi@78:         1,
poelzi@78:         TRUE,
poelzi@78:         'Test Frozen',
poelzi@78:         DEFAULT,
poelzi@78:         '1 second',
poelzi@78:         '5 minutes',
poelzi@78:         '2 days',
poelzi@78:         '1 second',
poelzi@82:         0, 100,
poelzi@82:         0, 100
poelzi@78:     ), (
poelzi@78:         1,
poelzi@78:         TRUE,
poelzi@78:         'Test Voting',
poelzi@78:         DEFAULT,
poelzi@78:         '1 second',
poelzi@78:         '5 minutes',
poelzi@78:         '1 second',
poelzi@78:         '2 days',
poelzi@82:         0, 100,
poelzi@82:         0, 100
poelzi@78:     );
jbe@0: END;
jbe@0: 




