liquid_feedback_core

annotate demo.sql @ 9:4af4df1415f9

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

Voting will be skipped, if no initiative is admitted for voting

Invite code feature allows people having an invite code to create one account

Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time

Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers

New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)

Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field

Bugfixes:
- In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
- In delete_private_data() function: Secret fields of member table are now deleted too
- Major bug fixed in lf_export, which caused data loss on execution
author jbe
date Thu Dec 10 12:00:00 2009 +0100 (2009-12-10)
parents 3da35844c874
children effdd7a04ea7
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@0 92 ("truster_id", "trustee_id") VALUES
jbe@0 93 ( 1, 9),
jbe@0 94 ( 2, 11),
jbe@0 95 ( 3, 12),
jbe@0 96 ( 4, 13),
jbe@0 97 ( 5, 14),
jbe@0 98 ( 6, 7),
jbe@0 99 ( 7, 8),
jbe@0 100 ( 8, 6),
jbe@0 101 (10, 9),
jbe@0 102 (11, 9),
jbe@0 103 (12, 21),
jbe@0 104 (15, 10),
jbe@0 105 (16, 17),
jbe@0 106 (17, 19),
jbe@0 107 (18, 19),
jbe@0 108 (23, 22);
jbe@0 109
jbe@0 110 -- delegations for topics
jbe@0 111 INSERT INTO "delegation"
jbe@0 112 ("area_id", "truster_id", "trustee_id") VALUES
jbe@0 113 (1, 3, 17),
jbe@0 114 (2, 5, 10),
jbe@0 115 (2, 9, 10),
jbe@0 116 (3, 4, 14),
jbe@0 117 (3, 16, 20),
jbe@0 118 (3, 19, 20),
jbe@0 119 (4, 5, 13),
jbe@0 120 (4, 12, 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