liquid_feedback_core

view demo.sql @ 6:3ea7a72ed7e7

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

Fixed bug in function close_voting(...) leading to wrong voter_count values in issue table

Added avatar column to member table for storing a small picture of the member
author jbe
date Wed Nov 18 12:00:00 2009 +0100 (2009-11-18)
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