liquid_feedback_core

view demo.sql @ 1:23092eb00e16

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

Serious bugfix in SQL function create_snapshot(...), which caused wrong counting of opinions on suggestions

lf_update now deletes expired sessions

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

Impressum / About Us