liquid_feedback_core
view demo.sql @ 10:effdd7a04ea7
Version beta11
Support is now automatically deleted, when interest is deleted
All tables dealing with delegations now contain a column scope (including table delegation)
Changes in fields related to notify_email of member
Policies can now specify larger majorities to be reached (e.g. 2/3)
New column agreed in table initiative to mark, if initiative has reached majority specified by policy
Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table
Function delete_private_data() no longer deletes invite codes, which have been already used
Removed view battle_participant
Added index on opinion (member_id, initiative_id)
Support is now automatically deleted, when interest is deleted
All tables dealing with delegations now contain a column scope (including table delegation)
Changes in fields related to notify_email of member
Policies can now specify larger majorities to be reached (e.g. 2/3)
New column agreed in table initiative to mark, if initiative has reached majority specified by policy
Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table
Function delete_private_data() no longer deletes invite codes, which have been already used
Removed view battle_participant
Added index on opinion (member_id, initiative_id)
| author | jbe |
|---|---|
| date | Fri Dec 25 12:00:00 2009 +0100 (2009-12-25) |
| parents | 4af4df1415f9 |
| 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;