liquid_feedback_core

annotate demo.sql @ 142:54ac8c473263

find changesets by author, revision, files, or words in the commit message
Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X
author jbe
date Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
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