liquid_feedback_core

view demo.sql @ 11:015825e225ca

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

Changed function delete_private_data() to delete more data, including tables setting and member_image, for better data protection

Bugfix related to linux-sh in shell script lf_export

New fields password_reset_secret and password_reset_secret_expiry in member table

UNIQUE constraint for notify_email_secret and password_reset_secret fields in member table

Database constraint forbids adding yourself as a contact
author jbe
date Sat Jan 02 12:00:00 2010 +0100 (2010-01-02)
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'.)
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 "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 );
48
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 $$;
62
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
68
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);
71
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);
89
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);
109
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);
121
122 INSERT INTO "issue" ("area_id", "policy_id") VALUES
123 (3, 1); -- id 1
124
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
133
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
142
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);
152
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);
174
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);
181
182 SELECT "time_warp"();
183 SELECT "time_warp"();
184 SELECT "time_warp"();
185
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);
195
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);
242
243 SELECT "time_warp"();
244
245 DROP FUNCTION "time_warp"();
246
247 END;
248

Impressum / About Us