liquid_feedback_core

view demo.sql @ 4:6133c0a62378

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

Precalculation of certain values to increase performance:
- Count of active members per area
- Count of active members altogether
- Count of people involved in the voting process for an issue

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

Impressum / About Us