liquid_feedback_core

annotate demo.sql @ 151:dca335a33e0b

find changesets by author, revision, files, or words in the commit message
Bugfix in function "clean_issue": Temporarily update "issue"."state" to avoid constraint violation
author jbe
date Thu Jun 02 02:33:10 2011 +0200 (2011-06-02)
parents e3bfa2d7954c
children 8e7d583e02f9
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@139 95 (4, 22, FALSE);
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@139 193 (2, 'Initiative A'), -- id 8
jbe@139 194 (2, 'Initiative B'), -- id 9
jbe@139 195 (2, 'Initiative C'), -- id 10
jbe@139 196 (2, 'Initiative D'); -- id 11
jbe@134 197
jbe@134 198 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@139 199 ( 8, 1, 'Lorem ipsum...'), -- id 8
jbe@139 200 ( 9, 2, 'Lorem ipsum...'), -- id 9
jbe@139 201 (10, 3, 'Lorem ipsum...'), -- id 10
jbe@139 202 (11, 4, 'Lorem ipsum...'); -- id 11
jbe@134 203
jbe@134 204 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@139 205 ( 8, 1),
jbe@139 206 ( 9, 2),
jbe@139 207 (10, 3),
jbe@139 208 (11, 4);
jbe@134 209
jbe@134 210 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@139 211 (1, 8, 8),
jbe@139 212 (1, 9, 9),
jbe@139 213 (1, 10, 10),
jbe@139 214 (1, 11, 11),
jbe@139 215 (2, 8, 8),
jbe@139 216 (2, 9, 9),
jbe@139 217 (2, 10, 10),
jbe@139 218 (2, 11, 11),
jbe@139 219 (3, 8, 8),
jbe@139 220 (3, 9, 9),
jbe@139 221 (3, 10, 10),
jbe@139 222 (3, 11, 11),
jbe@139 223 (4, 8, 8),
jbe@139 224 (4, 9, 9),
jbe@139 225 (4, 10, 10),
jbe@139 226 (4, 11, 11),
jbe@139 227 (5, 8, 8),
jbe@139 228 (5, 9, 9),
jbe@139 229 (5, 10, 10),
jbe@139 230 (5, 11, 11),
jbe@139 231 (6, 8, 8),
jbe@139 232 (6, 9, 9),
jbe@139 233 (6, 10, 10),
jbe@139 234 (6, 11, 11);
jbe@139 235
jbe@3 236 SELECT "time_warp"();
jbe@3 237 SELECT "time_warp"();
jbe@3 238 SELECT "time_warp"();
jbe@0 239
jbe@0 240 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0 241 ( 8, 1),
jbe@0 242 ( 9, 1),
jbe@0 243 (11, 1),
jbe@0 244 (12, 1),
jbe@0 245 (14, 1),
jbe@0 246 (19, 1),
jbe@0 247 (20, 1),
jbe@0 248 (21, 1);
jbe@0 249
jbe@0 250 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0 251 ( 8, 1, 1, 1),
jbe@0 252 ( 8, 1, 2, 1),
jbe@0 253 ( 8, 1, 3, 1),
jbe@0 254 ( 8, 1, 4, 1),
jbe@0 255 ( 8, 1, 5, 1),
jbe@0 256 ( 8, 1, 6, -1),
jbe@0 257 ( 8, 1, 7, -1),
jbe@0 258 ( 9, 1, 1, -2),
jbe@0 259 ( 9, 1, 2, -3),
jbe@0 260 ( 9, 1, 3, -2),
jbe@0 261 ( 9, 1, 4, -2),
jbe@0 262 ( 9, 1, 5, -2),
jbe@0 263 ( 9, 1, 6, -1),
jbe@0 264 (11, 1, 1, -1),
jbe@0 265 (11, 1, 2, -1),
jbe@0 266 (11, 1, 3, -1),
jbe@0 267 (11, 1, 4, -1),
jbe@0 268 (11, 1, 5, -1),
jbe@0 269 (11, 1, 6, 2),
jbe@0 270 (11, 1, 7, 1),
jbe@0 271 (12, 1, 1, -1),
jbe@0 272 (12, 1, 3, -1),
jbe@0 273 (12, 1, 4, -1),
jbe@0 274 (12, 1, 5, -1),
jbe@0 275 (12, 1, 6, -2),
jbe@0 276 (12, 1, 7, 1),
jbe@0 277 (14, 1, 1, 1),
jbe@0 278 (14, 1, 2, 3),
jbe@0 279 (14, 1, 3, 1),
jbe@0 280 (14, 1, 4, 2),
jbe@0 281 (14, 1, 5, 1),
jbe@0 282 (14, 1, 6, 1),
jbe@0 283 (14, 1, 7, 1),
jbe@0 284 (19, 1, 1, 3),
jbe@0 285 (19, 1, 2, 4),
jbe@0 286 (19, 1, 3, 2),
jbe@0 287 (19, 1, 4, 2),
jbe@0 288 (19, 1, 5, 2),
jbe@0 289 (19, 1, 7, 1),
jbe@0 290 (20, 1, 1, 1),
jbe@0 291 (20, 1, 2, 2),
jbe@0 292 (20, 1, 3, 1),
jbe@0 293 (20, 1, 4, 1),
jbe@0 294 (20, 1, 5, 1),
jbe@0 295 (21, 1, 5, -1);
jbe@0 296
jbe@134 297 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@134 298 ( 1, 2),
jbe@134 299 ( 2, 2),
jbe@134 300 ( 3, 2),
jbe@134 301 ( 4, 2),
jbe@134 302 ( 5, 2),
jbe@134 303 ( 6, 2),
jbe@134 304 ( 7, 2),
jbe@134 305 ( 8, 2),
jbe@134 306 ( 9, 2),
jbe@134 307 (10, 2),
jbe@134 308 (11, 2),
jbe@134 309 (12, 2),
jbe@134 310 (13, 2),
jbe@134 311 (14, 2),
jbe@134 312 (15, 2),
jbe@134 313 (16, 2),
jbe@134 314 (17, 2),
jbe@134 315 (18, 2),
jbe@134 316 (19, 2),
jbe@134 317 (20, 2);
jbe@134 318
jbe@134 319 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@139 320 ( 1, 2, 8, 3),
jbe@139 321 ( 1, 2, 9, 4),
jbe@139 322 ( 1, 2, 10, 2),
jbe@139 323 ( 1, 2, 11, 1),
jbe@139 324 ( 2, 2, 8, 3),
jbe@139 325 ( 2, 2, 9, 4),
jbe@139 326 ( 2, 2, 10, 2),
jbe@139 327 ( 2, 2, 11, 1),
jbe@139 328 ( 3, 2, 8, 4),
jbe@139 329 ( 3, 2, 9, 3),
jbe@139 330 ( 3, 2, 10, 2),
jbe@139 331 ( 3, 2, 11, 1),
jbe@139 332 ( 4, 2, 8, 4),
jbe@139 333 ( 4, 2, 9, 3),
jbe@139 334 ( 4, 2, 10, 2),
jbe@139 335 ( 4, 2, 11, 1),
jbe@139 336 ( 5, 2, 8, 4),
jbe@139 337 ( 5, 2, 9, 3),
jbe@139 338 ( 5, 2, 10, 2),
jbe@139 339 ( 5, 2, 11, 1),
jbe@139 340 ( 6, 2, 8, 4),
jbe@139 341 ( 6, 2, 9, 3),
jbe@139 342 ( 6, 2, 10, 2),
jbe@139 343 ( 6, 2, 11, 1),
jbe@139 344 ( 7, 2, 8, 4),
jbe@139 345 ( 7, 2, 9, 3),
jbe@139 346 ( 7, 2, 10, 2),
jbe@139 347 ( 7, 2, 11, 1),
jbe@139 348 ( 8, 2, 8, 4),
jbe@139 349 ( 8, 2, 9, 3),
jbe@139 350 ( 8, 2, 10, 2),
jbe@139 351 ( 8, 2, 11, 1),
jbe@139 352 ( 9, 2, 8, -1),
jbe@139 353 ( 9, 2, 9, 1),
jbe@139 354 ( 9, 2, 10, 3),
jbe@139 355 ( 9, 2, 11, 2),
jbe@139 356 (10, 2, 8, -1),
jbe@139 357 (10, 2, 9, 1),
jbe@139 358 (10, 2, 10, 3),
jbe@139 359 (10, 2, 11, 2),
jbe@139 360 (11, 2, 8, -1),
jbe@139 361 (11, 2, 9, 1),
jbe@139 362 (11, 2, 10, 3),
jbe@139 363 (11, 2, 11, 2),
jbe@139 364 (12, 2, 8, -1),
jbe@139 365 (12, 2, 9, 1),
jbe@139 366 (12, 2, 10, 3),
jbe@139 367 (12, 2, 11, 2),
jbe@139 368 (13, 2, 8, -1),
jbe@139 369 (13, 2, 9, 1),
jbe@139 370 (13, 2, 10, 3),
jbe@139 371 (13, 2, 11, 2),
jbe@139 372 (14, 2, 8, -1),
jbe@139 373 (14, 2, 9, 1),
jbe@139 374 (14, 2, 10, 3),
jbe@139 375 (14, 2, 11, 2),
jbe@139 376 (15, 2, 8, -1),
jbe@139 377 (15, 2, 9, -3),
jbe@139 378 (15, 2, 10, -4),
jbe@139 379 (15, 2, 11, -2),
jbe@139 380 (16, 2, 8, -1),
jbe@139 381 (16, 2, 9, -3),
jbe@139 382 (16, 2, 10, -4),
jbe@139 383 (16, 2, 11, -2),
jbe@139 384 (17, 2, 8, -1),
jbe@139 385 (17, 2, 9, -3),
jbe@139 386 (17, 2, 10, -4),
jbe@139 387 (17, 2, 11, -2),
jbe@139 388 (18, 2, 8, -1),
jbe@139 389 (18, 2, 9, 1),
jbe@139 390 (18, 2, 10, -2),
jbe@139 391 (18, 2, 11, 2),
jbe@139 392 (19, 2, 8, -1),
jbe@139 393 (19, 2, 9, 1),
jbe@139 394 (19, 2, 10, -2),
jbe@139 395 (19, 2, 11, 2),
jbe@139 396 (20, 2, 8, 1),
jbe@139 397 (20, 2, 9, 2),
jbe@139 398 (20, 2, 10, -1),
jbe@139 399 (20, 2, 11, 3);
jbe@134 400
jbe@3 401 SELECT "time_warp"();
jbe@3 402
jbe@3 403 DROP FUNCTION "time_warp"();
jbe@0 404
poelzi@78 405 -- Test policies that help with testing specific frontend parts
poelzi@78 406
poelzi@78 407 INSERT INTO "policy" (
poelzi@78 408 "index",
poelzi@78 409 "active",
poelzi@78 410 "name",
poelzi@78 411 "description",
poelzi@78 412 "admission_time",
poelzi@78 413 "discussion_time",
poelzi@78 414 "verification_time",
poelzi@78 415 "voting_time",
poelzi@78 416 "issue_quorum_num",
poelzi@78 417 "issue_quorum_den",
poelzi@78 418 "initiative_quorum_num",
poelzi@78 419 "initiative_quorum_den"
poelzi@78 420 ) VALUES (
poelzi@78 421 1,
poelzi@78 422 TRUE,
poelzi@78 423 'Test New',
poelzi@78 424 DEFAULT,
poelzi@78 425 '2 days',
poelzi@78 426 '1 second',
poelzi@78 427 '1 second',
poelzi@78 428 '1 second',
poelzi@82 429 0, 100,
poelzi@82 430 0, 100
poelzi@78 431 ), (
poelzi@78 432 1,
poelzi@78 433 TRUE,
poelzi@78 434 'Test Accept',
poelzi@78 435 DEFAULT,
poelzi@78 436 '1 second',
poelzi@78 437 '2 days',
poelzi@78 438 '1 second',
poelzi@78 439 '1 second',
poelzi@82 440 0, 100,
poelzi@82 441 0, 100
poelzi@78 442 ), (
poelzi@78 443 1,
poelzi@78 444 TRUE,
poelzi@78 445 'Test Frozen',
poelzi@78 446 DEFAULT,
poelzi@78 447 '1 second',
poelzi@78 448 '5 minutes',
poelzi@78 449 '2 days',
poelzi@78 450 '1 second',
poelzi@82 451 0, 100,
poelzi@82 452 0, 100
poelzi@78 453 ), (
poelzi@78 454 1,
poelzi@78 455 TRUE,
poelzi@78 456 'Test Voting',
poelzi@78 457 DEFAULT,
poelzi@78 458 '1 second',
poelzi@78 459 '5 minutes',
poelzi@78 460 '1 second',
poelzi@78 461 '2 days',
poelzi@82 462 0, 100,
poelzi@82 463 0, 100
poelzi@78 464 );
jbe@0 465 END;
jbe@0 466

Impressum / About Us