liquid_feedback_core
view demo.sql @ 53:c391eb3cd040
Added tag v1.0.3 for changeset 3fbf9e18ee52
| author | jbe |
|---|---|
| date | Thu Jun 17 23:12:34 2010 +0200 (2010-06-17) |
| parents | effdd7a04ea7 |
| children | 5d7b482241fa |
line source
1 -- NOTE: This file requires that sequence generators have not been used.
2 -- (All new rows need to start with id '1'.)
4 BEGIN;
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
31 UPDATE "member" SET "password" = "login";
33 INSERT INTO "policy" (
34 "index",
35 "name",
36 "admission_time",
37 "discussion_time",
38 "verification_time",
39 "voting_time",
40 "issue_quorum_num", "issue_quorum_den",
41 "initiative_quorum_num", "initiative_quorum_den"
42 ) VALUES (
43 1,
44 'Default policy',
45 '1 hour', '1 hour', '1 hour', '1 hour',
46 25, 100,
47 20, 100 );
49 CREATE FUNCTION "time_warp"() RETURNS VOID
50 LANGUAGE 'plpgsql' VOLATILE AS $$
51 BEGIN
52 UPDATE "issue" SET
53 "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL,
54 "created" = "created" - '1 hour 1 minute'::INTERVAL,
55 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL,
56 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL,
57 "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL;
58 PERFORM "check_everything"();
59 RETURN;
60 END;
61 $$;
63 INSERT INTO "area" ("name") VALUES
64 ('Area #1'), -- id 1
65 ('Area #2'), -- id 2
66 ('Area #3'), -- id 3
67 ('Area #4'); -- id 4
69 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy")
70 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE);
72 INSERT INTO "membership" ("area_id", "member_id", "autoreject") VALUES
73 (1, 9, FALSE),
74 (1, 19, FALSE),
75 (2, 9, TRUE),
76 (2, 10, TRUE),
77 (2, 17, TRUE),
78 (3, 9, FALSE),
79 (3, 11, FALSE),
80 (3, 12, TRUE),
81 (3, 14, FALSE),
82 (3, 20, FALSE),
83 (3, 21, TRUE),
84 (3, 22, TRUE),
85 (4, 6, FALSE),
86 (4, 9, FALSE),
87 (4, 13, FALSE),
88 (4, 22, TRUE);
90 -- global delegations
91 INSERT INTO "delegation"
92 ("truster_id", "scope", "trustee_id") VALUES
93 ( 1, 'global', 9),
94 ( 2, 'global', 11),
95 ( 3, 'global', 12),
96 ( 4, 'global', 13),
97 ( 5, 'global', 14),
98 ( 6, 'global', 7),
99 ( 7, 'global', 8),
100 ( 8, 'global', 6),
101 (10, 'global', 9),
102 (11, 'global', 9),
103 (12, 'global', 21),
104 (15, 'global', 10),
105 (16, 'global', 17),
106 (17, 'global', 19),
107 (18, 'global', 19),
108 (23, 'global', 22);
110 -- delegations for topics
111 INSERT INTO "delegation"
112 ("area_id", "truster_id", "scope", "trustee_id") VALUES
113 (1, 3, 'area', 17),
114 (2, 5, 'area', 10),
115 (2, 9, 'area', 10),
116 (3, 4, 'area', 14),
117 (3, 16, 'area', 20),
118 (3, 19, 'area', 20),
119 (4, 5, 'area', 13),
120 (4, 12, 'area', 22);
122 INSERT INTO "issue" ("area_id", "policy_id") VALUES
123 (3, 1); -- id 1
125 INSERT INTO "initiative" ("issue_id", "name") VALUES
126 (1, 'Initiative #1'), -- id 1
127 (1, 'Initiative #2'), -- id 2
128 (1, 'Initiative #3'), -- id 3
129 (1, 'Initiative #4'), -- id 4
130 (1, 'Initiative #5'), -- id 5
131 (1, 'Initiative #6'), -- id 6
132 (1, 'Initiative #7'); -- id 7
134 INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
135 (1, 17, 'Lorem ipsum...'), -- id 1
136 (2, 20, 'Lorem ipsum...'), -- id 2
137 (3, 20, 'Lorem ipsum...'), -- id 3
138 (4, 20, 'Lorem ipsum...'), -- id 4
139 (5, 14, 'Lorem ipsum...'), -- id 5
140 (6, 11, 'Lorem ipsum...'), -- id 6
141 (7, 12, 'Lorem ipsum...'); -- id 7
143 INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
144 (1, 17),
145 (1, 19),
146 (2, 20),
147 (3, 20),
148 (4, 20),
149 (5, 14),
150 (6, 11),
151 (7, 12);
153 INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
154 ( 7, 4, 4),
155 ( 8, 2, 2),
156 (11, 6, 6),
157 (12, 7, 7),
158 (14, 1, 1),
159 (14, 2, 2),
160 (14, 3, 3),
161 (14, 4, 4),
162 (14, 5, 5),
163 (14, 6, 6),
164 (14, 7, 7),
165 (17, 1, 1),
166 (17, 3, 3),
167 (19, 1, 1),
168 (19, 2, 2),
169 (20, 1, 1),
170 (20, 2, 2),
171 (20, 3, 3),
172 (20, 4, 4),
173 (20, 5, 5);
175 INSERT INTO "suggestion" ("initiative_id", "author_id", "name", "description") VALUES
176 (1, 19, 'Suggestion #1', 'Lorem ipsum...'); -- id 1
177 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
178 (14, 1, 2, FALSE);
179 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
180 (19, 1, 2, FALSE);
182 SELECT "time_warp"();
183 SELECT "time_warp"();
184 SELECT "time_warp"();
186 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
187 ( 8, 1),
188 ( 9, 1),
189 (11, 1),
190 (12, 1),
191 (14, 1),
192 (19, 1),
193 (20, 1),
194 (21, 1);
196 INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
197 ( 8, 1, 1, 1),
198 ( 8, 1, 2, 1),
199 ( 8, 1, 3, 1),
200 ( 8, 1, 4, 1),
201 ( 8, 1, 5, 1),
202 ( 8, 1, 6, -1),
203 ( 8, 1, 7, -1),
204 ( 9, 1, 1, -2),
205 ( 9, 1, 2, -3),
206 ( 9, 1, 3, -2),
207 ( 9, 1, 4, -2),
208 ( 9, 1, 5, -2),
209 ( 9, 1, 6, -1),
210 (11, 1, 1, -1),
211 (11, 1, 2, -1),
212 (11, 1, 3, -1),
213 (11, 1, 4, -1),
214 (11, 1, 5, -1),
215 (11, 1, 6, 2),
216 (11, 1, 7, 1),
217 (12, 1, 1, -1),
218 (12, 1, 3, -1),
219 (12, 1, 4, -1),
220 (12, 1, 5, -1),
221 (12, 1, 6, -2),
222 (12, 1, 7, 1),
223 (14, 1, 1, 1),
224 (14, 1, 2, 3),
225 (14, 1, 3, 1),
226 (14, 1, 4, 2),
227 (14, 1, 5, 1),
228 (14, 1, 6, 1),
229 (14, 1, 7, 1),
230 (19, 1, 1, 3),
231 (19, 1, 2, 4),
232 (19, 1, 3, 2),
233 (19, 1, 4, 2),
234 (19, 1, 5, 2),
235 (19, 1, 7, 1),
236 (20, 1, 1, 1),
237 (20, 1, 2, 2),
238 (20, 1, 3, 1),
239 (20, 1, 4, 1),
240 (20, 1, 5, 1),
241 (21, 1, 5, -1);
243 SELECT "time_warp"();
245 DROP FUNCTION "time_warp"();
247 END;