1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/demo.sql	Tue Oct 27 12:00:00 2009 +0100
     1.3 @@ -0,0 +1,228 @@
     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" ("login", "name") VALUES
    1.10 +  ('user1',  'User #1'),   -- id  1
    1.11 +  ('user2',  'User #2'),   -- id  2
    1.12 +  ('user3',  'User #3'),   -- id  3
    1.13 +  ('user4',  'User #4'),   -- id  4
    1.14 +  ('user5',  'User #5'),   -- id  5
    1.15 +  ('user6',  'User #6'),   -- id  6
    1.16 +  ('user7',  'User #7'),   -- id  7
    1.17 +  ('user8',  'User #8'),   -- id  8
    1.18 +  ('user9',  'User #9'),   -- id  9
    1.19 +  ('user10', 'User #10'),  -- id 10
    1.20 +  ('user11', 'User #11'),  -- id 11
    1.21 +  ('user12', 'User #12'),  -- id 12
    1.22 +  ('user13', 'User #13'),  -- id 13
    1.23 +  ('user14', 'User #14'),  -- id 14
    1.24 +  ('user15', 'User #15'),  -- id 15
    1.25 +  ('user16', 'User #16'),  -- id 16
    1.26 +  ('user17', 'User #17'),  -- id 17
    1.27 +  ('user18', 'User #18'),  -- id 18
    1.28 +  ('user19', 'User #19'),  -- id 19
    1.29 +  ('user20', 'User #20'),  -- id 20
    1.30 +  ('user21', 'User #21'),  -- id 21
    1.31 +  ('user22', 'User #22'),  -- id 22
    1.32 +  ('user23', 'User #23');  -- id 23
    1.33 +
    1.34 +UPDATE "member" SET "password" = "login";
    1.35 +
    1.36 +INSERT INTO "policy" (
    1.37 +        "name",
    1.38 +        "admission_time", "discussion_time", "voting_time",
    1.39 +        "issue_quorum_num", "issue_quorum_den",
    1.40 +        "initiative_quorum_num", "initiative_quorum_den"
    1.41 +    ) VALUES (
    1.42 +        'Default policy',
    1.43 +        '1 hour', '1 hour', '1 hour',
    1.44 +        25, 100,
    1.45 +        20, 100 );
    1.46 +
    1.47 +INSERT INTO "area" ("name") VALUES
    1.48 +  ('Area #1'),  -- id 1
    1.49 +  ('Area #2'),  -- id 2
    1.50 +  ('Area #3'),  -- id 3
    1.51 +  ('Area #4');  -- id 4
    1.52 +
    1.53 +INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
    1.54 +  (1,  9, FALSE),
    1.55 +  (1, 19, FALSE),
    1.56 +  (2,  9, TRUE),
    1.57 +  (2, 10, TRUE),
    1.58 +  (2, 17, TRUE),
    1.59 +  (3,  9, FALSE),
    1.60 +  (3, 11, FALSE),
    1.61 +  (3, 12, TRUE),
    1.62 +  (3, 14, FALSE),
    1.63 +  (3, 20, FALSE),
    1.64 +  (3, 21, TRUE),
    1.65 +  (3, 22, TRUE),
    1.66 +  (4,  6, FALSE),
    1.67 +  (4,  9, FALSE),
    1.68 +  (4, 13, FALSE),
    1.69 +  (4, 22, TRUE);
    1.70 +
    1.71 +-- global delegations
    1.72 +INSERT INTO "delegation"
    1.73 +  ("truster_id", "trustee_id") VALUES
    1.74 +  ( 1,  9),
    1.75 +  ( 2, 11),
    1.76 +  ( 3, 12),
    1.77 +  ( 4, 13),
    1.78 +  ( 5, 14),
    1.79 +  ( 6,  7),
    1.80 +  ( 7,  8),
    1.81 +  ( 8,  6),
    1.82 +  (10,  9),
    1.83 +  (11,  9),
    1.84 +  (12, 21),
    1.85 +  (15, 10),
    1.86 +  (16, 17),
    1.87 +  (17, 19),
    1.88 +  (18, 19),
    1.89 +  (23, 22);
    1.90 +
    1.91 +-- delegations for topics
    1.92 +INSERT INTO "delegation"
    1.93 +  ("area_id", "truster_id", "trustee_id") VALUES
    1.94 +  (1,  3, 17),
    1.95 +  (2,  5, 10),
    1.96 +  (2,  9, 10),
    1.97 +  (3,  4, 14),
    1.98 +  (3, 16, 20),
    1.99 +  (3, 19, 20),
   1.100 +  (4,  5, 13),
   1.101 +  (4, 12, 22);
   1.102 +
   1.103 +INSERT INTO "issue" ("area_id", "policy_id") VALUES
   1.104 +  (3, 1);  -- id 1
   1.105 +
   1.106 +INSERT INTO "initiative" ("issue_id", "name") VALUES
   1.107 +  (1, 'Initiative #1'),  -- id 1
   1.108 +  (1, 'Initiative #2'),  -- id 2
   1.109 +  (1, 'Initiative #3'),  -- id 3
   1.110 +  (1, 'Initiative #4'),  -- id 4
   1.111 +  (1, 'Initiative #5'),  -- id 5
   1.112 +  (1, 'Initiative #6'),  -- id 6
   1.113 +  (1, 'Initiative #7');  -- id 7
   1.114 +
   1.115 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
   1.116 +  (1, 17, 'Lorem ipsum...'),  -- id 1
   1.117 +  (2, 20, 'Lorem ipsum...'),  -- id 2
   1.118 +  (3, 20, 'Lorem ipsum...'),  -- id 3
   1.119 +  (4, 20, 'Lorem ipsum...'),  -- id 4
   1.120 +  (5, 14, 'Lorem ipsum...'),  -- id 5
   1.121 +  (6, 11, 'Lorem ipsum...'),  -- id 6
   1.122 +  (7, 12, 'Lorem ipsum...');  -- id 7
   1.123 +
   1.124 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
   1.125 +  (1, 17),
   1.126 +  (1, 19),
   1.127 +  (2, 20),
   1.128 +  (3, 20),
   1.129 +  (4, 20),
   1.130 +  (5, 14),
   1.131 +  (6, 11),
   1.132 +  (7, 12);
   1.133 +
   1.134 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
   1.135 +  ( 7,  4,  4),
   1.136 +  ( 8,  2,  2),
   1.137 +  (11,  6,  6),
   1.138 +  (12,  7,  7),
   1.139 +  (14,  1,  1),
   1.140 +  (14,  2,  2),
   1.141 +  (14,  3,  3),
   1.142 +  (14,  4,  4),
   1.143 +  (14,  5,  5),
   1.144 +  (14,  6,  6),
   1.145 +  (14,  7,  7),
   1.146 +  (17,  1,  1),
   1.147 +  (17,  3,  3),
   1.148 +  (19,  1,  1),
   1.149 +  (19,  2,  2),
   1.150 +  (20,  1,  1),
   1.151 +  (20,  2,  2),
   1.152 +  (20,  3,  3),
   1.153 +  (20,  4,  4),
   1.154 +  (20,  5,  5);
   1.155 +
   1.156 +INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
   1.157 +  (1, 19, 'Suggestion #1', 'Lorem ipsum...');  -- id 1
   1.158 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
   1.159 +  (14, 1, 2, FALSE);
   1.160 +INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
   1.161 +  (19, 1, 2, FALSE);
   1.162 +
   1.163 +UPDATE "issue" SET "created" = "created" - '3 hour 3 minute'::INTERVAL;
   1.164 +SELECT check_everything();
   1.165 +UPDATE "issue" SET "accepted" = "accepted" - '2 hour 2 minute'::INTERVAL;
   1.166 +SELECT check_everything();
   1.167 +
   1.168 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
   1.169 +  ( 8, 1),
   1.170 +  ( 9, 1),
   1.171 +  (11, 1),
   1.172 +  (12, 1),
   1.173 +  (14, 1),
   1.174 +  (19, 1),
   1.175 +  (20, 1),
   1.176 +  (21, 1);
   1.177 +
   1.178 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
   1.179 +  ( 8, 1, 1,  1),
   1.180 +  ( 8, 1, 2,  1),
   1.181 +  ( 8, 1, 3,  1),
   1.182 +  ( 8, 1, 4,  1),
   1.183 +  ( 8, 1, 5,  1),
   1.184 +  ( 8, 1, 6, -1),
   1.185 +  ( 8, 1, 7, -1),
   1.186 +  ( 9, 1, 1, -2),
   1.187 +  ( 9, 1, 2, -3),
   1.188 +  ( 9, 1, 3, -2),
   1.189 +  ( 9, 1, 4, -2),
   1.190 +  ( 9, 1, 5, -2),
   1.191 +  ( 9, 1, 6, -1),
   1.192 +  (11, 1, 1, -1),
   1.193 +  (11, 1, 2, -1),
   1.194 +  (11, 1, 3, -1),
   1.195 +  (11, 1, 4, -1),
   1.196 +  (11, 1, 5, -1),
   1.197 +  (11, 1, 6,  2),
   1.198 +  (11, 1, 7,  1),
   1.199 +  (12, 1, 1, -1),
   1.200 +  (12, 1, 3, -1),
   1.201 +  (12, 1, 4, -1),
   1.202 +  (12, 1, 5, -1),
   1.203 +  (12, 1, 6, -2),
   1.204 +  (12, 1, 7,  1),
   1.205 +  (14, 1, 1,  1),
   1.206 +  (14, 1, 2,  3),
   1.207 +  (14, 1, 3,  1),
   1.208 +  (14, 1, 4,  2),
   1.209 +  (14, 1, 5,  1),
   1.210 +  (14, 1, 6,  1),
   1.211 +  (14, 1, 7,  1),
   1.212 +  (19, 1, 1,  3),
   1.213 +  (19, 1, 2,  4),
   1.214 +  (19, 1, 3,  2),
   1.215 +  (19, 1, 4,  2),
   1.216 +  (19, 1, 5,  2),
   1.217 +  (19, 1, 7,  1),
   1.218 +  (20, 1, 1,  1),
   1.219 +  (20, 1, 2,  2),
   1.220 +  (20, 1, 3,  1),
   1.221 +  (20, 1, 4,  1),
   1.222 +  (20, 1, 5,  1),
   1.223 +  (21, 1, 5, -1);
   1.224 +
   1.225 +UPDATE "issue" SET
   1.226 +  "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
   1.227 +  "frozen" = "frozen" - '1 hour 1 minute'::INTERVAL;
   1.228 +SELECT check_everything();
   1.229 +
   1.230 +END;
   1.231 +