liquid_feedback_core

view demo.sql @ 7:69d84040fb93

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

More attibutes in member table

Renamed column ident_number of member table to identification

Images of members are now stored in extra table member_image

Minor bugfix in init.sql: Added missing verification_time column

Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes

New function highlight(...), which helps to highlight matching words in search results
author jbe
date Mon Nov 23 12:00:00 2009 +0100 (2009-11-23)
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