liquid_feedback_core

annotate test.sql @ 597:d34f8403d2c6

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

Impressum / About Us