liquid_feedback_core

view demo.sql @ 96:07e6a4f11b5b

find changesets by author, revision, files, or words in the commit message
Removed unneccessary JOIN in "create_interest_snapshot"(...)

The INSERT INTO "direct_supporter_snapshot" in function "create_interest_snapshot"(...) does not need to check if members are active.
The previous step ensures that the joined table "direct_interest_snapshot" does not contain entries from disabled members.
author jbe
date Mon Dec 06 23:50:32 2010 +0100 (2010-12-06)
parents 7744034552a3
children 58451b5565ae
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 -- set password to "login"
32 UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
33
34 INSERT INTO "policy" (
35 "index",
36 "name",
37 "admission_time",
38 "discussion_time",
39 "verification_time",
40 "voting_time",
41 "issue_quorum_num", "issue_quorum_den",
42 "initiative_quorum_num", "initiative_quorum_den"
43 ) VALUES (
44 1,
45 'Default policy',
46 '1 hour', '1 hour', '1 hour', '1 hour',
47 25, 100,
48 20, 100 );
49
50 CREATE FUNCTION "time_warp"() RETURNS VOID
51 LANGUAGE 'plpgsql' VOLATILE AS $$
52 BEGIN
53 UPDATE "issue" SET
54 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
55 "created" = "created" - '1 hour 1 minute'::INTERVAL,
56 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
57 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
58 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
59 PERFORM "check_everything"();
60 RETURN;
61 END;
62 $$;
63
64 INSERT INTO "area" ("name") VALUES
65 ('Area #1'), -- id 1
66 ('Area #2'), -- id 2
67 ('Area #3'), -- id 3
68 ('Area #4'); -- id 4
69
70 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
71 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
72
73 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
74 (1, 9, FALSE),
75 (1, 19, FALSE),
76 (2, 9, TRUE),
77 (2, 10, TRUE),
78 (2, 17, TRUE),
79 (3, 9, FALSE),
80 (3, 11, FALSE),
81 (3, 12, TRUE),
82 (3, 14, FALSE),
83 (3, 20, FALSE),
84 (3, 21, TRUE),
85 (3, 22, TRUE),
86 (4, 6, FALSE),
87 (4, 9, FALSE),
88 (4, 13, FALSE),
89 (4, 22, TRUE);
90
91 -- global delegations
92 INSERT INTO "delegation"
93 ("truster_id", "scope", "trustee_id") VALUES
94 ( 1, 'global', 9),
95 ( 2, 'global', 11),
96 ( 3, 'global', 12),
97 ( 4, 'global', 13),
98 ( 5, 'global', 14),
99 ( 6, 'global', 7),
100 ( 7, 'global', 8),
101 ( 8, 'global', 6),
102 (10, 'global', 9),
103 (11, 'global', 9),
104 (12, 'global', 21),
105 (15, 'global', 10),
106 (16, 'global', 17),
107 (17, 'global', 19),
108 (18, 'global', 19),
109 (23, 'global', 22);
110
111 -- delegations for topics
112 INSERT INTO "delegation"
113 ("area_id", "truster_id", "scope", "trustee_id") VALUES
114 (1, 3, 'area', 17),
115 (2, 5, 'area', 10),
116 (2, 9, 'area', 10),
117 (3, 4, 'area', 14),
118 (3, 16, 'area', 20),
119 (3, 19, 'area', 20),
120 (4, 5, 'area', 13),
121 (4, 12, 'area', 22);
122
123 INSERT INTO "issue" ("area_id", "policy_id") VALUES
124 (3, 1); -- id 1
125
126 INSERT INTO "initiative" ("issue_id", "name") VALUES
127 (1, 'Initiative #1'), -- id 1
128 (1, 'Initiative #2'), -- id 2
129 (1, 'Initiative #3'), -- id 3
130 (1, 'Initiative #4'), -- id 4
131 (1, 'Initiative #5'), -- id 5
132 (1, 'Initiative #6'), -- id 6
133 (1, 'Initiative #7'); -- id 7
134
135 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
136 (1, 17, 'Lorem ipsum...'), -- id 1
137 (2, 20, 'Lorem ipsum...'), -- id 2
138 (3, 20, 'Lorem ipsum...'), -- id 3
139 (4, 20, 'Lorem ipsum...'), -- id 4
140 (5, 14, 'Lorem ipsum...'), -- id 5
141 (6, 11, 'Lorem ipsum...'), -- id 6
142 (7, 12, 'Lorem ipsum...'); -- id 7
143
144 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
145 (1, 17),
146 (1, 19),
147 (2, 20),
148 (3, 20),
149 (4, 20),
150 (5, 14),
151 (6, 11),
152 (7, 12);
153
154 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
155 ( 7, 4, 4),
156 ( 8, 2, 2),
157 (11, 6, 6),
158 (12, 7, 7),
159 (14, 1, 1),
160 (14, 2, 2),
161 (14, 3, 3),
162 (14, 4, 4),
163 (14, 5, 5),
164 (14, 6, 6),
165 (14, 7, 7),
166 (17, 1, 1),
167 (17, 3, 3),
168 (19, 1, 1),
169 (19, 2, 2),
170 (20, 1, 1),
171 (20, 2, 2),
172 (20, 3, 3),
173 (20, 4, 4),
174 (20, 5, 5);
175
176 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
177 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
178 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
179 (14, 1, 2, FALSE);
180 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
181 (19, 1, 2, FALSE);
182
183 SELECT "time_warp"();
184 SELECT "time_warp"();
185 SELECT "time_warp"();
186
187 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
188 ( 8, 1),
189 ( 9, 1),
190 (11, 1),
191 (12, 1),
192 (14, 1),
193 (19, 1),
194 (20, 1),
195 (21, 1);
196
197 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
198 ( 8, 1, 1, 1),
199 ( 8, 1, 2, 1),
200 ( 8, 1, 3, 1),
201 ( 8, 1, 4, 1),
202 ( 8, 1, 5, 1),
203 ( 8, 1, 6, -1),
204 ( 8, 1, 7, -1),
205 ( 9, 1, 1, -2),
206 ( 9, 1, 2, -3),
207 ( 9, 1, 3, -2),
208 ( 9, 1, 4, -2),
209 ( 9, 1, 5, -2),
210 ( 9, 1, 6, -1),
211 (11, 1, 1, -1),
212 (11, 1, 2, -1),
213 (11, 1, 3, -1),
214 (11, 1, 4, -1),
215 (11, 1, 5, -1),
216 (11, 1, 6, 2),
217 (11, 1, 7, 1),
218 (12, 1, 1, -1),
219 (12, 1, 3, -1),
220 (12, 1, 4, -1),
221 (12, 1, 5, -1),
222 (12, 1, 6, -2),
223 (12, 1, 7, 1),
224 (14, 1, 1, 1),
225 (14, 1, 2, 3),
226 (14, 1, 3, 1),
227 (14, 1, 4, 2),
228 (14, 1, 5, 1),
229 (14, 1, 6, 1),
230 (14, 1, 7, 1),
231 (19, 1, 1, 3),
232 (19, 1, 2, 4),
233 (19, 1, 3, 2),
234 (19, 1, 4, 2),
235 (19, 1, 5, 2),
236 (19, 1, 7, 1),
237 (20, 1, 1, 1),
238 (20, 1, 2, 2),
239 (20, 1, 3, 1),
240 (20, 1, 4, 1),
241 (20, 1, 5, 1),
242 (21, 1, 5, -1);
243
244 SELECT "time_warp"();
245
246 DROP FUNCTION "time_warp"();
247
248 -- Test policies that help with testing specific frontend parts
249
250 INSERT INTO "policy" (
251 "index",
252 "active",
253 "name",
254 "description",
255 "admission_time",
256 "discussion_time",
257 "verification_time",
258 "voting_time",
259 "issue_quorum_num",
260 "issue_quorum_den",
261 "initiative_quorum_num",
262 "initiative_quorum_den"
263 ) VALUES (
264 1,
265 TRUE,
266 'Test New',
267 DEFAULT,
268 '2 days',
269 '1 second',
270 '1 second',
271 '1 second',
272 0, 100,
273 0, 100
274 ), (
275 1,
276 TRUE,
277 'Test Accept',
278 DEFAULT,
279 '1 second',
280 '2 days',
281 '1 second',
282 '1 second',
283 0, 100,
284 0, 100
285 ), (
286 1,
287 TRUE,
288 'Test Frozen',
289 DEFAULT,
290 '1 second',
291 '5 minutes',
292 '2 days',
293 '1 second',
294 0, 100,
295 0, 100
296 ), (
297 1,
298 TRUE,
299 'Test Voting',
300 DEFAULT,
301 '1 second',
302 '5 minutes',
303 '1 second',
304 '2 days',
305 0, 100,
306 0, 100
307 );
308 END;
309

Impressum / About Us