liquid_feedback_core
annotate demo.sql @ 134:bd0cd909189d
Added new column "promising" to table "initiative"; Simplified tie-breaking
Also added example to demo.sql, which results in a promising initiative.
Also added example to demo.sql, which results in a promising initiative.
author | jbe |
---|---|
date | Tue May 24 23:35:29 2011 +0200 (2011-05-24) |
parents | 72792038f7f0 |
children | e3bfa2d7954c |
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@0 | 95 (4, 22, TRUE); |
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@134 | 193 (2, 'Initiative A'), -- id 8 |
jbe@134 | 194 (2, 'Initiative B'); -- id 9 |
jbe@134 | 195 |
jbe@134 | 196 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES |
jbe@134 | 197 (8, 1, 'Lorem ipsum...'), -- id 8 |
jbe@134 | 198 (9, 2, 'Lorem ipsum...'); -- id 9 |
jbe@134 | 199 |
jbe@134 | 200 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES |
jbe@134 | 201 (8, 1), |
jbe@134 | 202 (9, 2); |
jbe@134 | 203 |
jbe@134 | 204 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES |
jbe@134 | 205 (1, 8, 8), |
jbe@134 | 206 (1, 9, 9), |
jbe@134 | 207 (2, 8, 8), |
jbe@134 | 208 (2, 9, 9), |
jbe@134 | 209 (3, 8, 8), |
jbe@134 | 210 (3, 9, 9), |
jbe@134 | 211 (4, 8, 8), |
jbe@134 | 212 (4, 9, 9), |
jbe@134 | 213 (5, 8, 8), |
jbe@134 | 214 (5, 9, 9), |
jbe@134 | 215 (6, 8, 8), |
jbe@134 | 216 (6, 9, 9); |
jbe@134 | 217 |
jbe@3 | 218 SELECT "time_warp"(); |
jbe@3 | 219 SELECT "time_warp"(); |
jbe@3 | 220 SELECT "time_warp"(); |
jbe@0 | 221 |
jbe@0 | 222 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES |
jbe@0 | 223 ( 8, 1), |
jbe@0 | 224 ( 9, 1), |
jbe@0 | 225 (11, 1), |
jbe@0 | 226 (12, 1), |
jbe@0 | 227 (14, 1), |
jbe@0 | 228 (19, 1), |
jbe@0 | 229 (20, 1), |
jbe@0 | 230 (21, 1); |
jbe@0 | 231 |
jbe@0 | 232 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES |
jbe@0 | 233 ( 8, 1, 1, 1), |
jbe@0 | 234 ( 8, 1, 2, 1), |
jbe@0 | 235 ( 8, 1, 3, 1), |
jbe@0 | 236 ( 8, 1, 4, 1), |
jbe@0 | 237 ( 8, 1, 5, 1), |
jbe@0 | 238 ( 8, 1, 6, -1), |
jbe@0 | 239 ( 8, 1, 7, -1), |
jbe@0 | 240 ( 9, 1, 1, -2), |
jbe@0 | 241 ( 9, 1, 2, -3), |
jbe@0 | 242 ( 9, 1, 3, -2), |
jbe@0 | 243 ( 9, 1, 4, -2), |
jbe@0 | 244 ( 9, 1, 5, -2), |
jbe@0 | 245 ( 9, 1, 6, -1), |
jbe@0 | 246 (11, 1, 1, -1), |
jbe@0 | 247 (11, 1, 2, -1), |
jbe@0 | 248 (11, 1, 3, -1), |
jbe@0 | 249 (11, 1, 4, -1), |
jbe@0 | 250 (11, 1, 5, -1), |
jbe@0 | 251 (11, 1, 6, 2), |
jbe@0 | 252 (11, 1, 7, 1), |
jbe@0 | 253 (12, 1, 1, -1), |
jbe@0 | 254 (12, 1, 3, -1), |
jbe@0 | 255 (12, 1, 4, -1), |
jbe@0 | 256 (12, 1, 5, -1), |
jbe@0 | 257 (12, 1, 6, -2), |
jbe@0 | 258 (12, 1, 7, 1), |
jbe@0 | 259 (14, 1, 1, 1), |
jbe@0 | 260 (14, 1, 2, 3), |
jbe@0 | 261 (14, 1, 3, 1), |
jbe@0 | 262 (14, 1, 4, 2), |
jbe@0 | 263 (14, 1, 5, 1), |
jbe@0 | 264 (14, 1, 6, 1), |
jbe@0 | 265 (14, 1, 7, 1), |
jbe@0 | 266 (19, 1, 1, 3), |
jbe@0 | 267 (19, 1, 2, 4), |
jbe@0 | 268 (19, 1, 3, 2), |
jbe@0 | 269 (19, 1, 4, 2), |
jbe@0 | 270 (19, 1, 5, 2), |
jbe@0 | 271 (19, 1, 7, 1), |
jbe@0 | 272 (20, 1, 1, 1), |
jbe@0 | 273 (20, 1, 2, 2), |
jbe@0 | 274 (20, 1, 3, 1), |
jbe@0 | 275 (20, 1, 4, 1), |
jbe@0 | 276 (20, 1, 5, 1), |
jbe@0 | 277 (21, 1, 5, -1); |
jbe@0 | 278 |
jbe@134 | 279 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES |
jbe@134 | 280 ( 1, 2), |
jbe@134 | 281 ( 2, 2), |
jbe@134 | 282 ( 3, 2), |
jbe@134 | 283 ( 4, 2), |
jbe@134 | 284 ( 5, 2), |
jbe@134 | 285 ( 6, 2), |
jbe@134 | 286 ( 7, 2), |
jbe@134 | 287 ( 8, 2), |
jbe@134 | 288 ( 9, 2), |
jbe@134 | 289 (10, 2), |
jbe@134 | 290 (11, 2), |
jbe@134 | 291 (12, 2), |
jbe@134 | 292 (13, 2), |
jbe@134 | 293 (14, 2), |
jbe@134 | 294 (15, 2), |
jbe@134 | 295 (16, 2), |
jbe@134 | 296 (17, 2), |
jbe@134 | 297 (18, 2), |
jbe@134 | 298 (19, 2), |
jbe@134 | 299 (20, 2); |
jbe@134 | 300 |
jbe@134 | 301 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES |
jbe@134 | 302 ( 1, 2, 8, 2), |
jbe@134 | 303 ( 1, 2, 9, 1), |
jbe@134 | 304 ( 2, 2, 8, 2), |
jbe@134 | 305 ( 2, 2, 9, 1), |
jbe@134 | 306 ( 3, 2, 8, 2), |
jbe@134 | 307 ( 3, 2, 9, 1), |
jbe@134 | 308 ( 4, 2, 8, 2), |
jbe@134 | 309 ( 4, 2, 9, 1), |
jbe@134 | 310 ( 5, 2, 8, 2), |
jbe@134 | 311 ( 5, 2, 9, 1), |
jbe@134 | 312 ( 6, 2, 8, 2), |
jbe@134 | 313 ( 6, 2, 9, 1), |
jbe@134 | 314 ( 7, 2, 8, 2), |
jbe@134 | 315 ( 7, 2, 9, 1), |
jbe@134 | 316 ( 8, 2, 8, 2), |
jbe@134 | 317 ( 8, 2, 9, 1), |
jbe@134 | 318 ( 9, 2, 8, 2), |
jbe@134 | 319 ( 9, 2, 9, 1), |
jbe@134 | 320 (10, 2, 8, -1), |
jbe@134 | 321 (10, 2, 9, 1), |
jbe@134 | 322 (11, 2, 8, -1), |
jbe@134 | 323 (11, 2, 9, 1), |
jbe@134 | 324 (12, 2, 8, -1), |
jbe@134 | 325 (12, 2, 9, 1), |
jbe@134 | 326 (13, 2, 8, -1), |
jbe@134 | 327 (13, 2, 9, 1), |
jbe@134 | 328 (14, 2, 8, -1), |
jbe@134 | 329 (14, 2, 9, 1), |
jbe@134 | 330 (15, 2, 8, -1), |
jbe@134 | 331 (15, 2, 9, 1), |
jbe@134 | 332 (16, 2, 8, -1), |
jbe@134 | 333 (16, 2, 9, 1), |
jbe@134 | 334 (17, 2, 8, -1), |
jbe@134 | 335 (17, 2, 9, -2), |
jbe@134 | 336 (18, 2, 8, -1), |
jbe@134 | 337 (18, 2, 9, -2), |
jbe@134 | 338 (19, 2, 8, -1), |
jbe@134 | 339 (19, 2, 9, -2), |
jbe@134 | 340 (20, 2, 8, -1), |
jbe@134 | 341 (20, 2, 9, -2); |
jbe@134 | 342 |
jbe@3 | 343 SELECT "time_warp"(); |
jbe@3 | 344 |
jbe@3 | 345 DROP FUNCTION "time_warp"(); |
jbe@0 | 346 |
poelzi@78 | 347 -- Test policies that help with testing specific frontend parts |
poelzi@78 | 348 |
poelzi@78 | 349 INSERT INTO "policy" ( |
poelzi@78 | 350 "index", |
poelzi@78 | 351 "active", |
poelzi@78 | 352 "name", |
poelzi@78 | 353 "description", |
poelzi@78 | 354 "admission_time", |
poelzi@78 | 355 "discussion_time", |
poelzi@78 | 356 "verification_time", |
poelzi@78 | 357 "voting_time", |
poelzi@78 | 358 "issue_quorum_num", |
poelzi@78 | 359 "issue_quorum_den", |
poelzi@78 | 360 "initiative_quorum_num", |
poelzi@78 | 361 "initiative_quorum_den" |
poelzi@78 | 362 ) VALUES ( |
poelzi@78 | 363 1, |
poelzi@78 | 364 TRUE, |
poelzi@78 | 365 'Test New', |
poelzi@78 | 366 DEFAULT, |
poelzi@78 | 367 '2 days', |
poelzi@78 | 368 '1 second', |
poelzi@78 | 369 '1 second', |
poelzi@78 | 370 '1 second', |
poelzi@82 | 371 0, 100, |
poelzi@82 | 372 0, 100 |
poelzi@78 | 373 ), ( |
poelzi@78 | 374 1, |
poelzi@78 | 375 TRUE, |
poelzi@78 | 376 'Test Accept', |
poelzi@78 | 377 DEFAULT, |
poelzi@78 | 378 '1 second', |
poelzi@78 | 379 '2 days', |
poelzi@78 | 380 '1 second', |
poelzi@78 | 381 '1 second', |
poelzi@82 | 382 0, 100, |
poelzi@82 | 383 0, 100 |
poelzi@78 | 384 ), ( |
poelzi@78 | 385 1, |
poelzi@78 | 386 TRUE, |
poelzi@78 | 387 'Test Frozen', |
poelzi@78 | 388 DEFAULT, |
poelzi@78 | 389 '1 second', |
poelzi@78 | 390 '5 minutes', |
poelzi@78 | 391 '2 days', |
poelzi@78 | 392 '1 second', |
poelzi@82 | 393 0, 100, |
poelzi@82 | 394 0, 100 |
poelzi@78 | 395 ), ( |
poelzi@78 | 396 1, |
poelzi@78 | 397 TRUE, |
poelzi@78 | 398 'Test Voting', |
poelzi@78 | 399 DEFAULT, |
poelzi@78 | 400 '1 second', |
poelzi@78 | 401 '5 minutes', |
poelzi@78 | 402 '1 second', |
poelzi@78 | 403 '2 days', |
poelzi@82 | 404 0, 100, |
poelzi@82 | 405 0, 100 |
poelzi@78 | 406 ); |
jbe@0 | 407 END; |
jbe@0 | 408 |