liquid_feedback_core

annotate demo.sql @ 169:ddd8e17d8f82

Complete removal of autoreject feature
author jbe
date Tue Jun 07 18:02:58 2011 +0200 (2011-06-07)
parents de59f6b1daf3
children e3b0ea7ab2ad
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@155 35 "index",
jbe@155 36 "name",
jbe@155 37 "admission_time",
jbe@155 38 "discussion_time",
jbe@155 39 "verification_time",
jbe@155 40 "voting_time",
jbe@155 41 "issue_quorum_num", "issue_quorum_den",
jbe@155 42 "initiative_quorum_num", "initiative_quorum_den",
jbe@167 43 "direct_majority_num", "direct_majority_den", "direct_majority_strict",
jbe@155 44 "no_reverse_beat_path", "no_multistage_majority"
jbe@155 45 ) VALUES (
jbe@155 46 1,
jbe@155 47 'Default policy',
jbe@155 48 '1 hour', '1 hour', '1 hour', '1 hour',
jbe@155 49 25, 100,
jbe@155 50 20, 100,
jbe@155 51 1, 2, TRUE,
jbe@155 52 TRUE, FALSE );
jbe@0 53
jbe@3 54 CREATE FUNCTION "time_warp"() RETURNS VOID
jbe@3 55 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@3 56 BEGIN
jbe@3 57 UPDATE "issue" SET
jbe@3 58 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
jbe@3 59 "created" = "created" - '1 hour 1 minute'::INTERVAL,
jbe@3 60 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
jbe@3 61 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
jbe@3 62 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
jbe@3 63 PERFORM "check_everything"();
jbe@3 64 RETURN;
jbe@3 65 END;
jbe@3 66 $$;
jbe@3 67
jbe@97 68 INSERT INTO "unit" ("name") VALUES ('Main');
jbe@97 69
jbe@97 70 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@97 71 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@97 72 FROM "member";
jbe@97 73
jbe@97 74 INSERT INTO "area" ("unit_id", "name") VALUES
jbe@97 75 (1, 'Area #1'), -- id 1
jbe@97 76 (1, 'Area #2'), -- id 2
jbe@97 77 (1, 'Area #3'), -- id 3
jbe@97 78 (1, 'Area #4'); -- id 4
jbe@0 79
jbe@9 80 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
jbe@9 81 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
jbe@9 82
jbe@169 83 INSERT INTO "membership" ("area_id", "member_id") VALUES
jbe@169 84 (1, 9),
jbe@169 85 (1, 19),
jbe@169 86 (2, 9),
jbe@169 87 (2, 10),
jbe@169 88 (2, 17),
jbe@169 89 (3, 9),
jbe@169 90 (3, 11),
jbe@169 91 (3, 12),
jbe@169 92 (3, 14),
jbe@169 93 (3, 20),
jbe@169 94 (3, 21),
jbe@169 95 (3, 22),
jbe@169 96 (4, 6),
jbe@169 97 (4, 9),
jbe@169 98 (4, 13),
jbe@169 99 (4, 22);
jbe@0 100
jbe@0 101 -- global delegations
jbe@0 102 INSERT INTO "delegation"
jbe@97 103 ("truster_id", "scope", "unit_id", "trustee_id") VALUES
jbe@97 104 ( 1, 'unit', 1, 9),
jbe@97 105 ( 2, 'unit', 1, 11),
jbe@97 106 ( 3, 'unit', 1, 12),
jbe@97 107 ( 4, 'unit', 1, 13),
jbe@97 108 ( 5, 'unit', 1, 14),
jbe@97 109 ( 6, 'unit', 1, 7),
jbe@97 110 ( 7, 'unit', 1, 8),
jbe@97 111 ( 8, 'unit', 1, 6),
jbe@97 112 (10, 'unit', 1, 9),
jbe@97 113 (11, 'unit', 1, 9),
jbe@97 114 (12, 'unit', 1, 21),
jbe@97 115 (15, 'unit', 1, 10),
jbe@97 116 (16, 'unit', 1, 17),
jbe@97 117 (17, 'unit', 1, 19),
jbe@97 118 (18, 'unit', 1, 19),
jbe@97 119 (23, 'unit', 1, 22);
jbe@0 120
jbe@0 121 -- delegations for topics
jbe@0 122 INSERT INTO "delegation"
jbe@10 123 ("area_id", "truster_id", "scope", "trustee_id") VALUES
jbe@10 124 (1, 3, 'area', 17),
jbe@10 125 (2, 5, 'area', 10),
jbe@10 126 (2, 9, 'area', 10),
jbe@10 127 (3, 4, 'area', 14),
jbe@10 128 (3, 16, 'area', 20),
jbe@10 129 (3, 19, 'area', 20),
jbe@10 130 (4, 5, 'area', 13),
jbe@10 131 (4, 12, 'area', 22);
jbe@0 132
jbe@0 133 INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@0 134 (3, 1); -- id 1
jbe@0 135
jbe@0 136 INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@0 137 (1, 'Initiative #1'), -- id 1
jbe@0 138 (1, 'Initiative #2'), -- id 2
jbe@0 139 (1, 'Initiative #3'), -- id 3
jbe@0 140 (1, 'Initiative #4'), -- id 4
jbe@0 141 (1, 'Initiative #5'), -- id 5
jbe@0 142 (1, 'Initiative #6'), -- id 6
jbe@0 143 (1, 'Initiative #7'); -- id 7
jbe@0 144
jbe@0 145 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@0 146 (1, 17, 'Lorem ipsum...'), -- id 1
jbe@0 147 (2, 20, 'Lorem ipsum...'), -- id 2
jbe@0 148 (3, 20, 'Lorem ipsum...'), -- id 3
jbe@0 149 (4, 20, 'Lorem ipsum...'), -- id 4
jbe@0 150 (5, 14, 'Lorem ipsum...'), -- id 5
jbe@0 151 (6, 11, 'Lorem ipsum...'), -- id 6
jbe@0 152 (7, 12, 'Lorem ipsum...'); -- id 7
jbe@0 153
jbe@0 154 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@0 155 (1, 17),
jbe@0 156 (1, 19),
jbe@0 157 (2, 20),
jbe@0 158 (3, 20),
jbe@0 159 (4, 20),
jbe@0 160 (5, 14),
jbe@0 161 (6, 11),
jbe@0 162 (7, 12);
jbe@0 163
jbe@0 164 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@0 165 ( 7, 4, 4),
jbe@0 166 ( 8, 2, 2),
jbe@0 167 (11, 6, 6),
jbe@0 168 (12, 7, 7),
jbe@0 169 (14, 1, 1),
jbe@0 170 (14, 2, 2),
jbe@0 171 (14, 3, 3),
jbe@0 172 (14, 4, 4),
jbe@0 173 (14, 5, 5),
jbe@0 174 (14, 6, 6),
jbe@0 175 (14, 7, 7),
jbe@0 176 (17, 1, 1),
jbe@0 177 (17, 3, 3),
jbe@0 178 (19, 1, 1),
jbe@0 179 (19, 2, 2),
jbe@0 180 (20, 1, 1),
jbe@0 181 (20, 2, 2),
jbe@0 182 (20, 3, 3),
jbe@0 183 (20, 4, 4),
jbe@0 184 (20, 5, 5);
jbe@0 185
jbe@159 186 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "content") VALUES
jbe@0 187 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
jbe@0 188 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 189 (14, 1, 2, FALSE);
jbe@0 190 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 191 (19, 1, 2, FALSE);
jbe@0 192
jbe@134 193 INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@134 194 (4, 1); -- id 2
jbe@134 195
jbe@134 196 INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@139 197 (2, 'Initiative A'), -- id 8
jbe@139 198 (2, 'Initiative B'), -- id 9
jbe@139 199 (2, 'Initiative C'), -- id 10
jbe@139 200 (2, 'Initiative D'); -- id 11
jbe@134 201
jbe@134 202 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@139 203 ( 8, 1, 'Lorem ipsum...'), -- id 8
jbe@139 204 ( 9, 2, 'Lorem ipsum...'), -- id 9
jbe@139 205 (10, 3, 'Lorem ipsum...'), -- id 10
jbe@139 206 (11, 4, 'Lorem ipsum...'); -- id 11
jbe@134 207
jbe@134 208 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@139 209 ( 8, 1),
jbe@139 210 ( 9, 2),
jbe@139 211 (10, 3),
jbe@139 212 (11, 4);
jbe@134 213
jbe@134 214 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@139 215 (1, 8, 8),
jbe@139 216 (1, 9, 9),
jbe@139 217 (1, 10, 10),
jbe@139 218 (1, 11, 11),
jbe@139 219 (2, 8, 8),
jbe@139 220 (2, 9, 9),
jbe@139 221 (2, 10, 10),
jbe@139 222 (2, 11, 11),
jbe@139 223 (3, 8, 8),
jbe@139 224 (3, 9, 9),
jbe@139 225 (3, 10, 10),
jbe@139 226 (3, 11, 11),
jbe@139 227 (4, 8, 8),
jbe@139 228 (4, 9, 9),
jbe@139 229 (4, 10, 10),
jbe@139 230 (4, 11, 11),
jbe@139 231 (5, 8, 8),
jbe@139 232 (5, 9, 9),
jbe@139 233 (5, 10, 10),
jbe@139 234 (5, 11, 11),
jbe@139 235 (6, 8, 8),
jbe@139 236 (6, 9, 9),
jbe@139 237 (6, 10, 10),
jbe@139 238 (6, 11, 11);
jbe@139 239
jbe@3 240 SELECT "time_warp"();
jbe@3 241 SELECT "time_warp"();
jbe@3 242 SELECT "time_warp"();
jbe@0 243
jbe@0 244 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0 245 ( 8, 1),
jbe@0 246 ( 9, 1),
jbe@0 247 (11, 1),
jbe@0 248 (12, 1),
jbe@0 249 (14, 1),
jbe@0 250 (19, 1),
jbe@0 251 (20, 1),
jbe@0 252 (21, 1);
jbe@0 253
jbe@0 254 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0 255 ( 8, 1, 1, 1),
jbe@0 256 ( 8, 1, 2, 1),
jbe@0 257 ( 8, 1, 3, 1),
jbe@0 258 ( 8, 1, 4, 1),
jbe@0 259 ( 8, 1, 5, 1),
jbe@0 260 ( 8, 1, 6, -1),
jbe@0 261 ( 8, 1, 7, -1),
jbe@0 262 ( 9, 1, 1, -2),
jbe@0 263 ( 9, 1, 2, -3),
jbe@0 264 ( 9, 1, 3, -2),
jbe@0 265 ( 9, 1, 4, -2),
jbe@0 266 ( 9, 1, 5, -2),
jbe@0 267 ( 9, 1, 6, -1),
jbe@0 268 (11, 1, 1, -1),
jbe@0 269 (11, 1, 2, -1),
jbe@0 270 (11, 1, 3, -1),
jbe@0 271 (11, 1, 4, -1),
jbe@0 272 (11, 1, 5, -1),
jbe@0 273 (11, 1, 6, 2),
jbe@0 274 (11, 1, 7, 1),
jbe@0 275 (12, 1, 1, -1),
jbe@0 276 (12, 1, 3, -1),
jbe@0 277 (12, 1, 4, -1),
jbe@0 278 (12, 1, 5, -1),
jbe@0 279 (12, 1, 6, -2),
jbe@0 280 (12, 1, 7, 1),
jbe@0 281 (14, 1, 1, 1),
jbe@0 282 (14, 1, 2, 3),
jbe@0 283 (14, 1, 3, 1),
jbe@0 284 (14, 1, 4, 2),
jbe@0 285 (14, 1, 5, 1),
jbe@0 286 (14, 1, 6, 1),
jbe@0 287 (14, 1, 7, 1),
jbe@0 288 (19, 1, 1, 3),
jbe@0 289 (19, 1, 2, 4),
jbe@0 290 (19, 1, 3, 2),
jbe@0 291 (19, 1, 4, 2),
jbe@0 292 (19, 1, 5, 2),
jbe@0 293 (19, 1, 7, 1),
jbe@0 294 (20, 1, 1, 1),
jbe@0 295 (20, 1, 2, 2),
jbe@0 296 (20, 1, 3, 1),
jbe@0 297 (20, 1, 4, 1),
jbe@0 298 (20, 1, 5, 1),
jbe@0 299 (21, 1, 5, -1);
jbe@0 300
jbe@134 301 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@134 302 ( 1, 2),
jbe@134 303 ( 2, 2),
jbe@134 304 ( 3, 2),
jbe@134 305 ( 4, 2),
jbe@134 306 ( 5, 2),
jbe@134 307 ( 6, 2),
jbe@134 308 ( 7, 2),
jbe@134 309 ( 8, 2),
jbe@134 310 ( 9, 2),
jbe@134 311 (10, 2),
jbe@134 312 (11, 2),
jbe@134 313 (12, 2),
jbe@134 314 (13, 2),
jbe@134 315 (14, 2),
jbe@134 316 (15, 2),
jbe@134 317 (16, 2),
jbe@134 318 (17, 2),
jbe@134 319 (18, 2),
jbe@134 320 (19, 2),
jbe@134 321 (20, 2);
jbe@134 322
jbe@134 323 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@139 324 ( 1, 2, 8, 3),
jbe@139 325 ( 1, 2, 9, 4),
jbe@139 326 ( 1, 2, 10, 2),
jbe@139 327 ( 1, 2, 11, 1),
jbe@139 328 ( 2, 2, 8, 3),
jbe@139 329 ( 2, 2, 9, 4),
jbe@139 330 ( 2, 2, 10, 2),
jbe@139 331 ( 2, 2, 11, 1),
jbe@139 332 ( 3, 2, 8, 4),
jbe@139 333 ( 3, 2, 9, 3),
jbe@139 334 ( 3, 2, 10, 2),
jbe@139 335 ( 3, 2, 11, 1),
jbe@139 336 ( 4, 2, 8, 4),
jbe@139 337 ( 4, 2, 9, 3),
jbe@139 338 ( 4, 2, 10, 2),
jbe@139 339 ( 4, 2, 11, 1),
jbe@139 340 ( 5, 2, 8, 4),
jbe@139 341 ( 5, 2, 9, 3),
jbe@139 342 ( 5, 2, 10, 2),
jbe@139 343 ( 5, 2, 11, 1),
jbe@139 344 ( 6, 2, 8, 4),
jbe@139 345 ( 6, 2, 9, 3),
jbe@139 346 ( 6, 2, 10, 2),
jbe@139 347 ( 6, 2, 11, 1),
jbe@139 348 ( 7, 2, 8, 4),
jbe@139 349 ( 7, 2, 9, 3),
jbe@139 350 ( 7, 2, 10, 2),
jbe@139 351 ( 7, 2, 11, 1),
jbe@139 352 ( 8, 2, 8, 4),
jbe@139 353 ( 8, 2, 9, 3),
jbe@139 354 ( 8, 2, 10, 2),
jbe@139 355 ( 8, 2, 11, 1),
jbe@139 356 ( 9, 2, 8, -1),
jbe@139 357 ( 9, 2, 9, 1),
jbe@139 358 ( 9, 2, 10, 3),
jbe@139 359 ( 9, 2, 11, 2),
jbe@139 360 (10, 2, 8, -1),
jbe@139 361 (10, 2, 9, 1),
jbe@139 362 (10, 2, 10, 3),
jbe@139 363 (10, 2, 11, 2),
jbe@139 364 (11, 2, 8, -1),
jbe@139 365 (11, 2, 9, 1),
jbe@139 366 (11, 2, 10, 3),
jbe@139 367 (11, 2, 11, 2),
jbe@139 368 (12, 2, 8, -1),
jbe@139 369 (12, 2, 9, 1),
jbe@139 370 (12, 2, 10, 3),
jbe@139 371 (12, 2, 11, 2),
jbe@139 372 (13, 2, 8, -1),
jbe@139 373 (13, 2, 9, 1),
jbe@139 374 (13, 2, 10, 3),
jbe@139 375 (13, 2, 11, 2),
jbe@139 376 (14, 2, 8, -1),
jbe@139 377 (14, 2, 9, 1),
jbe@139 378 (14, 2, 10, 3),
jbe@139 379 (14, 2, 11, 2),
jbe@139 380 (15, 2, 8, -1),
jbe@139 381 (15, 2, 9, -3),
jbe@139 382 (15, 2, 10, -4),
jbe@139 383 (15, 2, 11, -2),
jbe@139 384 (16, 2, 8, -1),
jbe@139 385 (16, 2, 9, -3),
jbe@139 386 (16, 2, 10, -4),
jbe@139 387 (16, 2, 11, -2),
jbe@139 388 (17, 2, 8, -1),
jbe@139 389 (17, 2, 9, -3),
jbe@139 390 (17, 2, 10, -4),
jbe@139 391 (17, 2, 11, -2),
jbe@139 392 (18, 2, 8, -1),
jbe@139 393 (18, 2, 9, 1),
jbe@139 394 (18, 2, 10, -2),
jbe@139 395 (18, 2, 11, 2),
jbe@139 396 (19, 2, 8, -1),
jbe@139 397 (19, 2, 9, 1),
jbe@139 398 (19, 2, 10, -2),
jbe@139 399 (19, 2, 11, 2),
jbe@139 400 (20, 2, 8, 1),
jbe@139 401 (20, 2, 9, 2),
jbe@139 402 (20, 2, 10, -1),
jbe@139 403 (20, 2, 11, 3);
jbe@134 404
jbe@3 405 SELECT "time_warp"();
jbe@3 406
jbe@3 407 DROP FUNCTION "time_warp"();
jbe@0 408
poelzi@78 409 -- Test policies that help with testing specific frontend parts
poelzi@78 410
poelzi@78 411 INSERT INTO "policy" (
poelzi@78 412 "index",
poelzi@78 413 "active",
poelzi@78 414 "name",
poelzi@78 415 "description",
poelzi@78 416 "admission_time",
poelzi@78 417 "discussion_time",
poelzi@78 418 "verification_time",
poelzi@78 419 "voting_time",
poelzi@78 420 "issue_quorum_num",
poelzi@78 421 "issue_quorum_den",
poelzi@78 422 "initiative_quorum_num",
poelzi@78 423 "initiative_quorum_den"
poelzi@78 424 ) VALUES (
poelzi@78 425 1,
poelzi@78 426 TRUE,
poelzi@78 427 'Test New',
poelzi@78 428 DEFAULT,
poelzi@78 429 '2 days',
poelzi@78 430 '1 second',
poelzi@78 431 '1 second',
poelzi@78 432 '1 second',
poelzi@82 433 0, 100,
poelzi@82 434 0, 100
poelzi@78 435 ), (
poelzi@78 436 1,
poelzi@78 437 TRUE,
poelzi@78 438 'Test Accept',
poelzi@78 439 DEFAULT,
poelzi@78 440 '1 second',
poelzi@78 441 '2 days',
poelzi@78 442 '1 second',
poelzi@78 443 '1 second',
poelzi@82 444 0, 100,
poelzi@82 445 0, 100
poelzi@78 446 ), (
poelzi@78 447 1,
poelzi@78 448 TRUE,
poelzi@78 449 'Test Frozen',
poelzi@78 450 DEFAULT,
poelzi@78 451 '1 second',
poelzi@78 452 '5 minutes',
poelzi@78 453 '2 days',
poelzi@78 454 '1 second',
poelzi@82 455 0, 100,
poelzi@82 456 0, 100
poelzi@78 457 ), (
poelzi@78 458 1,
poelzi@78 459 TRUE,
poelzi@78 460 'Test Voting',
poelzi@78 461 DEFAULT,
poelzi@78 462 '1 second',
poelzi@78 463 '5 minutes',
poelzi@78 464 '1 second',
poelzi@78 465 '2 days',
poelzi@82 466 0, 100,
poelzi@82 467 0, 100
poelzi@78 468 );
jbe@0 469 END;
jbe@0 470

Impressum / About Us