liquid_feedback_core

view demo.sql @ 8:e6faf5ff83af

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

Added missing indicies on TSVECTOR fields

New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken

Added weight field also for (intermediate) delegating members in snapshot and voter tables

Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)

Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()

Field in member table to be used by a frontend to store information about hints being hidden by the user

Minor cleanup / New comments
author jbe
date Mon Nov 30 12:00:00 2009 +0100 (2009-11-30)
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