liquid_feedback_core

annotate demo.sql @ 14:ac7836ac00d9

find changesets by author, revision, files, or words in the commit message
Version beta15

Bugfix: Revoked initiatives will never be admitted

Possibility to recommend another initiative to support when revoking an initiative

Allow "initiator"."accepted" to be NULL (NULL means "no answer yet", while FALSE means "rejected")
author jbe
date Sun Jan 10 12:00:00 2010 +0100 (2010-01-10)
parents effdd7a04ea7
children 5d7b482241fa
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
jbe@0 31 UPDATE "member" SET "password" = "login";
jbe@0 32
jbe@0 33 INSERT INTO "policy" (
jbe@9 34 "index",
jbe@0 35 "name",
jbe@3 36 "admission_time",
jbe@3 37 "discussion_time",
jbe@3 38 "verification_time",
jbe@3 39 "voting_time",
jbe@0 40 "issue_quorum_num", "issue_quorum_den",
jbe@0 41 "initiative_quorum_num", "initiative_quorum_den"
jbe@0 42 ) VALUES (
jbe@9 43 1,
jbe@0 44 'Default policy',
jbe@3 45 '1 hour', '1 hour', '1 hour', '1 hour',
jbe@0 46 25, 100,
jbe@0 47 20, 100 );
jbe@0 48
jbe@3 49 CREATE FUNCTION "time_warp"() RETURNS VOID
jbe@3 50 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@3 51 BEGIN
jbe@3 52 UPDATE "issue" SET
jbe@3 53 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
jbe@3 54 "created" = "created" - '1 hour 1 minute'::INTERVAL,
jbe@3 55 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
jbe@3 56 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
jbe@3 57 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
jbe@3 58 PERFORM "check_everything"();
jbe@3 59 RETURN;
jbe@3 60 END;
jbe@3 61 $$;
jbe@3 62
jbe@0 63 INSERT INTO "area" ("name") VALUES
jbe@0 64 ('Area #1'), -- id 1
jbe@0 65 ('Area #2'), -- id 2
jbe@0 66 ('Area #3'), -- id 3
jbe@0 67 ('Area #4'); -- id 4
jbe@0 68
jbe@9 69 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
jbe@9 70 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
jbe@9 71
jbe@0 72 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
jbe@0 73 (1, 9, FALSE),
jbe@0 74 (1, 19, FALSE),
jbe@0 75 (2, 9, TRUE),
jbe@0 76 (2, 10, TRUE),
jbe@0 77 (2, 17, TRUE),
jbe@0 78 (3, 9, FALSE),
jbe@0 79 (3, 11, FALSE),
jbe@0 80 (3, 12, TRUE),
jbe@0 81 (3, 14, FALSE),
jbe@0 82 (3, 20, FALSE),
jbe@0 83 (3, 21, TRUE),
jbe@0 84 (3, 22, TRUE),
jbe@0 85 (4, 6, FALSE),
jbe@0 86 (4, 9, FALSE),
jbe@0 87 (4, 13, FALSE),
jbe@0 88 (4, 22, TRUE);
jbe@0 89
jbe@0 90 -- global delegations
jbe@0 91 INSERT INTO "delegation"
jbe@10 92 ("truster_id", "scope", "trustee_id") VALUES
jbe@10 93 ( 1, 'global', 9),
jbe@10 94 ( 2, 'global', 11),
jbe@10 95 ( 3, 'global', 12),
jbe@10 96 ( 4, 'global', 13),
jbe@10 97 ( 5, 'global', 14),
jbe@10 98 ( 6, 'global', 7),
jbe@10 99 ( 7, 'global', 8),
jbe@10 100 ( 8, 'global', 6),
jbe@10 101 (10, 'global', 9),
jbe@10 102 (11, 'global', 9),
jbe@10 103 (12, 'global', 21),
jbe@10 104 (15, 'global', 10),
jbe@10 105 (16, 'global', 17),
jbe@10 106 (17, 'global', 19),
jbe@10 107 (18, 'global', 19),
jbe@10 108 (23, 'global', 22);
jbe@0 109
jbe@0 110 -- delegations for topics
jbe@0 111 INSERT INTO "delegation"
jbe@10 112 ("area_id", "truster_id", "scope", "trustee_id") VALUES
jbe@10 113 (1, 3, 'area', 17),
jbe@10 114 (2, 5, 'area', 10),
jbe@10 115 (2, 9, 'area', 10),
jbe@10 116 (3, 4, 'area', 14),
jbe@10 117 (3, 16, 'area', 20),
jbe@10 118 (3, 19, 'area', 20),
jbe@10 119 (4, 5, 'area', 13),
jbe@10 120 (4, 12, 'area', 22);
jbe@0 121
jbe@0 122 INSERT INTO "issue" ("area_id", "policy_id") VALUES
jbe@0 123 (3, 1); -- id 1
jbe@0 124
jbe@0 125 INSERT INTO "initiative" ("issue_id", "name") VALUES
jbe@0 126 (1, 'Initiative #1'), -- id 1
jbe@0 127 (1, 'Initiative #2'), -- id 2
jbe@0 128 (1, 'Initiative #3'), -- id 3
jbe@0 129 (1, 'Initiative #4'), -- id 4
jbe@0 130 (1, 'Initiative #5'), -- id 5
jbe@0 131 (1, 'Initiative #6'), -- id 6
jbe@0 132 (1, 'Initiative #7'); -- id 7
jbe@0 133
jbe@0 134 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
jbe@0 135 (1, 17, 'Lorem ipsum...'), -- id 1
jbe@0 136 (2, 20, 'Lorem ipsum...'), -- id 2
jbe@0 137 (3, 20, 'Lorem ipsum...'), -- id 3
jbe@0 138 (4, 20, 'Lorem ipsum...'), -- id 4
jbe@0 139 (5, 14, 'Lorem ipsum...'), -- id 5
jbe@0 140 (6, 11, 'Lorem ipsum...'), -- id 6
jbe@0 141 (7, 12, 'Lorem ipsum...'); -- id 7
jbe@0 142
jbe@0 143 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
jbe@0 144 (1, 17),
jbe@0 145 (1, 19),
jbe@0 146 (2, 20),
jbe@0 147 (3, 20),
jbe@0 148 (4, 20),
jbe@0 149 (5, 14),
jbe@0 150 (6, 11),
jbe@0 151 (7, 12);
jbe@0 152
jbe@0 153 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
jbe@0 154 ( 7, 4, 4),
jbe@0 155 ( 8, 2, 2),
jbe@0 156 (11, 6, 6),
jbe@0 157 (12, 7, 7),
jbe@0 158 (14, 1, 1),
jbe@0 159 (14, 2, 2),
jbe@0 160 (14, 3, 3),
jbe@0 161 (14, 4, 4),
jbe@0 162 (14, 5, 5),
jbe@0 163 (14, 6, 6),
jbe@0 164 (14, 7, 7),
jbe@0 165 (17, 1, 1),
jbe@0 166 (17, 3, 3),
jbe@0 167 (19, 1, 1),
jbe@0 168 (19, 2, 2),
jbe@0 169 (20, 1, 1),
jbe@0 170 (20, 2, 2),
jbe@0 171 (20, 3, 3),
jbe@0 172 (20, 4, 4),
jbe@0 173 (20, 5, 5);
jbe@0 174
jbe@0 175 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
jbe@0 176 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
jbe@0 177 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 178 (14, 1, 2, FALSE);
jbe@0 179 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
jbe@0 180 (19, 1, 2, FALSE);
jbe@0 181
jbe@3 182 SELECT "time_warp"();
jbe@3 183 SELECT "time_warp"();
jbe@3 184 SELECT "time_warp"();
jbe@0 185
jbe@0 186 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
jbe@0 187 ( 8, 1),
jbe@0 188 ( 9, 1),
jbe@0 189 (11, 1),
jbe@0 190 (12, 1),
jbe@0 191 (14, 1),
jbe@0 192 (19, 1),
jbe@0 193 (20, 1),
jbe@0 194 (21, 1);
jbe@0 195
jbe@0 196 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
jbe@0 197 ( 8, 1, 1, 1),
jbe@0 198 ( 8, 1, 2, 1),
jbe@0 199 ( 8, 1, 3, 1),
jbe@0 200 ( 8, 1, 4, 1),
jbe@0 201 ( 8, 1, 5, 1),
jbe@0 202 ( 8, 1, 6, -1),
jbe@0 203 ( 8, 1, 7, -1),
jbe@0 204 ( 9, 1, 1, -2),
jbe@0 205 ( 9, 1, 2, -3),
jbe@0 206 ( 9, 1, 3, -2),
jbe@0 207 ( 9, 1, 4, -2),
jbe@0 208 ( 9, 1, 5, -2),
jbe@0 209 ( 9, 1, 6, -1),
jbe@0 210 (11, 1, 1, -1),
jbe@0 211 (11, 1, 2, -1),
jbe@0 212 (11, 1, 3, -1),
jbe@0 213 (11, 1, 4, -1),
jbe@0 214 (11, 1, 5, -1),
jbe@0 215 (11, 1, 6, 2),
jbe@0 216 (11, 1, 7, 1),
jbe@0 217 (12, 1, 1, -1),
jbe@0 218 (12, 1, 3, -1),
jbe@0 219 (12, 1, 4, -1),
jbe@0 220 (12, 1, 5, -1),
jbe@0 221 (12, 1, 6, -2),
jbe@0 222 (12, 1, 7, 1),
jbe@0 223 (14, 1, 1, 1),
jbe@0 224 (14, 1, 2, 3),
jbe@0 225 (14, 1, 3, 1),
jbe@0 226 (14, 1, 4, 2),
jbe@0 227 (14, 1, 5, 1),
jbe@0 228 (14, 1, 6, 1),
jbe@0 229 (14, 1, 7, 1),
jbe@0 230 (19, 1, 1, 3),
jbe@0 231 (19, 1, 2, 4),
jbe@0 232 (19, 1, 3, 2),
jbe@0 233 (19, 1, 4, 2),
jbe@0 234 (19, 1, 5, 2),
jbe@0 235 (19, 1, 7, 1),
jbe@0 236 (20, 1, 1, 1),
jbe@0 237 (20, 1, 2, 2),
jbe@0 238 (20, 1, 3, 1),
jbe@0 239 (20, 1, 4, 1),
jbe@0 240 (20, 1, 5, 1),
jbe@0 241 (21, 1, 5, -1);
jbe@0 242
jbe@3 243 SELECT "time_warp"();
jbe@3 244
jbe@3 245 DROP FUNCTION "time_warp"();
jbe@0 246
jbe@0 247 END;
jbe@0 248

Impressum / About Us