liquid_feedback_core

view demo.sql @ 3:3da35844c874

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

Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.

Bugfix: end_of_admission snapshots are now created for accepted issues.
author jbe
date Sun Nov 15 12:00:00 2009 +0100 (2009-11-15)
parents 8d021cb5eaf4
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