liquid_feedback_core

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

Impressum / About Us