rev |
line source |
jbe@0
|
1
|
jbe@0
|
2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
|
jbe@0
|
3
|
jbe@0
|
4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
|
jbe@0
|
5
|
jbe@0
|
6 BEGIN;
|
jbe@0
|
7
|
jbe@5
|
8 CREATE VIEW "liquid_feedback_version" AS
|
jbe@34
|
9 SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL))
|
jbe@5
|
10 AS "subquery"("string", "major", "minor", "revision");
|
jbe@5
|
11
|
jbe@0
|
12
|
jbe@0
|
13
|
jbe@7
|
14 ----------------------
|
jbe@7
|
15 -- Full text search --
|
jbe@7
|
16 ----------------------
|
jbe@7
|
17
|
jbe@7
|
18
|
jbe@7
|
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
|
jbe@7
|
20 RETURNS TSQUERY
|
jbe@7
|
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@7
|
22 BEGIN
|
jbe@7
|
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
|
jbe@7
|
24 END;
|
jbe@7
|
25 $$;
|
jbe@7
|
26
|
jbe@7
|
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
|
jbe@7
|
28
|
jbe@7
|
29
|
jbe@7
|
30 CREATE FUNCTION "highlight"
|
jbe@7
|
31 ( "body_p" TEXT,
|
jbe@7
|
32 "query_text_p" TEXT )
|
jbe@7
|
33 RETURNS TEXT
|
jbe@7
|
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@7
|
35 BEGIN
|
jbe@7
|
36 RETURN ts_headline(
|
jbe@7
|
37 'pg_catalog.simple',
|
jbe@8
|
38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
|
jbe@7
|
39 "text_search_query"("query_text_p"),
|
jbe@7
|
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
|
jbe@7
|
41 END;
|
jbe@7
|
42 $$;
|
jbe@7
|
43
|
jbe@7
|
44 COMMENT ON FUNCTION "highlight"
|
jbe@7
|
45 ( "body_p" TEXT,
|
jbe@7
|
46 "query_text_p" TEXT )
|
jbe@7
|
47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
|
jbe@7
|
48
|
jbe@7
|
49
|
jbe@7
|
50
|
jbe@0
|
51 -------------------------
|
jbe@0
|
52 -- Tables and indicies --
|
jbe@0
|
53 -------------------------
|
jbe@0
|
54
|
jbe@8
|
55
|
jbe@0
|
56 CREATE TABLE "member" (
|
jbe@0
|
57 "id" SERIAL4 PRIMARY KEY,
|
jbe@13
|
58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@0
|
59 "login" TEXT NOT NULL UNIQUE,
|
jbe@0
|
60 "password" TEXT,
|
jbe@0
|
61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
jbe@0
|
62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@7
|
63 "notify_email" TEXT,
|
jbe@11
|
64 "notify_email_unconfirmed" TEXT,
|
jbe@11
|
65 "notify_email_secret" TEXT UNIQUE,
|
jbe@11
|
66 "notify_email_secret_expiry" TIMESTAMPTZ,
|
jbe@11
|
67 "password_reset_secret" TEXT UNIQUE,
|
jbe@11
|
68 "password_reset_secret_expiry" TIMESTAMPTZ,
|
jbe@7
|
69 "name" TEXT NOT NULL UNIQUE,
|
jbe@7
|
70 "identification" TEXT UNIQUE,
|
jbe@7
|
71 "organizational_unit" TEXT,
|
jbe@7
|
72 "internal_posts" TEXT,
|
jbe@7
|
73 "realname" TEXT,
|
jbe@7
|
74 "birthday" DATE,
|
jbe@7
|
75 "address" TEXT,
|
jbe@7
|
76 "email" TEXT,
|
jbe@7
|
77 "xmpp_address" TEXT,
|
jbe@7
|
78 "website" TEXT,
|
jbe@7
|
79 "phone" TEXT,
|
jbe@7
|
80 "mobile_phone" TEXT,
|
jbe@7
|
81 "profession" TEXT,
|
jbe@7
|
82 "external_memberships" TEXT,
|
jbe@7
|
83 "external_posts" TEXT,
|
jbe@7
|
84 "statement" TEXT,
|
jbe@10
|
85 "text_search_data" TSVECTOR );
|
jbe@0
|
86 CREATE INDEX "member_active_idx" ON "member" ("active");
|
jbe@8
|
87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
|
jbe@7
|
88 CREATE TRIGGER "update_text_search_data"
|
jbe@7
|
89 BEFORE INSERT OR UPDATE ON "member"
|
jbe@7
|
90 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@7
|
91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@7
|
92 "name", "identification", "organizational_unit", "internal_posts",
|
jbe@7
|
93 "realname", "external_memberships", "external_posts", "statement" );
|
jbe@0
|
94
|
jbe@0
|
95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
|
jbe@0
|
96
|
jbe@10
|
97 COMMENT ON COLUMN "member"."login" IS 'Login name';
|
jbe@10
|
98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
|
jbe@10
|
99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
|
jbe@10
|
100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
|
jbe@10
|
101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
|
jbe@10
|
102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
|
jbe@10
|
103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
|
jbe@10
|
104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
|
jbe@10
|
105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
|
jbe@10
|
106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
|
jbe@10
|
107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
|
jbe@10
|
108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
|
jbe@10
|
109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
|
jbe@10
|
110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
|
jbe@10
|
111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
|
jbe@10
|
112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
|
jbe@10
|
113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
|
jbe@7
|
114
|
jbe@7
|
115
|
jbe@13
|
116 CREATE TABLE "member_history" (
|
jbe@13
|
117 "id" SERIAL8 PRIMARY KEY,
|
jbe@13
|
118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@13
|
119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@13
|
120 "login" TEXT NOT NULL,
|
jbe@13
|
121 "name" TEXT NOT NULL );
|
jbe@13
|
122
|
jbe@13
|
123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
|
jbe@13
|
124
|
jbe@13
|
125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
|
jbe@13
|
126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
|
jbe@13
|
127
|
jbe@13
|
128
|
jbe@9
|
129 CREATE TABLE "invite_code" (
|
jbe@9
|
130 "code" TEXT PRIMARY KEY,
|
jbe@9
|
131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@9
|
132 "used" TIMESTAMPTZ,
|
jbe@9
|
133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
jbe@9
|
134 "comment" TEXT,
|
jbe@9
|
135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
|
jbe@9
|
136
|
jbe@9
|
137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
|
jbe@9
|
138
|
jbe@9
|
139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
|
jbe@9
|
140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
|
jbe@9
|
141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
|
jbe@9
|
142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
|
jbe@9
|
143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
|
jbe@9
|
144
|
jbe@9
|
145
|
jbe@9
|
146 CREATE TABLE "setting" (
|
jbe@9
|
147 PRIMARY KEY ("member_id", "key"),
|
jbe@9
|
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@9
|
149 "key" TEXT NOT NULL,
|
jbe@9
|
150 "value" TEXT NOT NULL );
|
jbe@9
|
151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
|
jbe@9
|
152
|
jbe@23
|
153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings for members as a string';
|
jbe@9
|
154
|
jbe@9
|
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
|
jbe@9
|
156
|
jbe@9
|
157
|
jbe@16
|
158 CREATE TABLE "setting_map" (
|
jbe@16
|
159 PRIMARY KEY ("member_id", "key", "subkey"),
|
jbe@16
|
160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@16
|
161 "key" TEXT NOT NULL,
|
jbe@16
|
162 "subkey" TEXT NOT NULL,
|
jbe@16
|
163 "value" TEXT NOT NULL );
|
jbe@16
|
164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
|
jbe@16
|
165
|
jbe@23
|
166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
|
jbe@16
|
167
|
jbe@16
|
168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
|
jbe@16
|
169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
|
jbe@16
|
170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
|
jbe@16
|
171
|
jbe@16
|
172
|
jbe@23
|
173 CREATE TABLE "member_relation_setting" (
|
jbe@23
|
174 PRIMARY KEY ("member_id", "key", "other_member_id"),
|
jbe@23
|
175 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
176 "key" TEXT NOT NULL,
|
jbe@23
|
177 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
178 "value" TEXT NOT NULL );
|
jbe@23
|
179
|
jbe@23
|
180 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings related to relations between members as a string';
|
jbe@23
|
181
|
jbe@23
|
182
|
jbe@7
|
183 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
|
jbe@7
|
184
|
jbe@7
|
185 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
|
jbe@7
|
186
|
jbe@7
|
187
|
jbe@7
|
188 CREATE TABLE "member_image" (
|
jbe@7
|
189 PRIMARY KEY ("member_id", "image_type", "scaled"),
|
jbe@7
|
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@7
|
191 "image_type" "member_image_type",
|
jbe@7
|
192 "scaled" BOOLEAN,
|
jbe@7
|
193 "content_type" TEXT,
|
jbe@7
|
194 "data" BYTEA NOT NULL );
|
jbe@7
|
195
|
jbe@7
|
196 COMMENT ON TABLE "member_image" IS 'Images of members';
|
jbe@7
|
197
|
jbe@7
|
198 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
|
jbe@0
|
199
|
jbe@0
|
200
|
jbe@4
|
201 CREATE TABLE "member_count" (
|
jbe@5
|
202 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
jbe@5
|
203 "total_count" INT4 NOT NULL );
|
jbe@4
|
204
|
jbe@5
|
205 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
|
jbe@4
|
206
|
jbe@5
|
207 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
|
jbe@5
|
208 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
|
jbe@4
|
209
|
jbe@4
|
210
|
jbe@0
|
211 CREATE TABLE "contact" (
|
jbe@0
|
212 PRIMARY KEY ("member_id", "other_member_id"),
|
jbe@0
|
213 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
214 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@11
|
215 "public" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@11
|
216 CONSTRAINT "cant_save_yourself_as_contact"
|
jbe@11
|
217 CHECK ("member_id" != "other_member_id") );
|
jbe@0
|
218
|
jbe@0
|
219 COMMENT ON TABLE "contact" IS 'Contact lists';
|
jbe@0
|
220
|
jbe@0
|
221 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
|
jbe@0
|
222 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
|
jbe@0
|
223 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
|
jbe@0
|
224
|
jbe@0
|
225
|
jbe@0
|
226 CREATE TABLE "session" (
|
jbe@0
|
227 "ident" TEXT PRIMARY KEY,
|
jbe@0
|
228 "additional_secret" TEXT,
|
jbe@0
|
229 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
jbe@0
|
230 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
|
jbe@0
|
231 "lang" TEXT );
|
jbe@0
|
232 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
jbe@0
|
233
|
jbe@0
|
234 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
|
jbe@0
|
235
|
jbe@0
|
236 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
jbe@0
|
237 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
jbe@0
|
238 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
jbe@0
|
239 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
jbe@0
|
240
|
jbe@0
|
241
|
jbe@0
|
242 CREATE TABLE "policy" (
|
jbe@0
|
243 "id" SERIAL4 PRIMARY KEY,
|
jbe@9
|
244 "index" INT4 NOT NULL,
|
jbe@0
|
245 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
jbe@0
|
246 "name" TEXT NOT NULL UNIQUE,
|
jbe@0
|
247 "description" TEXT NOT NULL DEFAULT '',
|
jbe@0
|
248 "admission_time" INTERVAL NOT NULL,
|
jbe@0
|
249 "discussion_time" INTERVAL NOT NULL,
|
jbe@3
|
250 "verification_time" INTERVAL NOT NULL,
|
jbe@0
|
251 "voting_time" INTERVAL NOT NULL,
|
jbe@0
|
252 "issue_quorum_num" INT4 NOT NULL,
|
jbe@0
|
253 "issue_quorum_den" INT4 NOT NULL,
|
jbe@0
|
254 "initiative_quorum_num" INT4 NOT NULL,
|
jbe@10
|
255 "initiative_quorum_den" INT4 NOT NULL,
|
jbe@10
|
256 "majority_num" INT4 NOT NULL DEFAULT 1,
|
jbe@10
|
257 "majority_den" INT4 NOT NULL DEFAULT 2,
|
jbe@10
|
258 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
|
jbe@0
|
259 CREATE INDEX "policy_active_idx" ON "policy" ("active");
|
jbe@0
|
260
|
jbe@0
|
261 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
|
jbe@0
|
262
|
jbe@9
|
263 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
|
jbe@0
|
264 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
|
jbe@0
|
265 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
|
jbe@3
|
266 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
|
jbe@3
|
267 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
|
jbe@3
|
268 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
|
jbe@10
|
269 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
|
jbe@10
|
270 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
|
jbe@10
|
271 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
|
jbe@10
|
272 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
|
jbe@10
|
273 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
|
jbe@10
|
274 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
|
jbe@10
|
275 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
|
jbe@0
|
276
|
jbe@0
|
277
|
jbe@0
|
278 CREATE TABLE "area" (
|
jbe@0
|
279 "id" SERIAL4 PRIMARY KEY,
|
jbe@0
|
280 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
jbe@0
|
281 "name" TEXT NOT NULL,
|
jbe@4
|
282 "description" TEXT NOT NULL DEFAULT '',
|
jbe@5
|
283 "direct_member_count" INT4,
|
jbe@5
|
284 "member_weight" INT4,
|
jbe@7
|
285 "autoreject_weight" INT4,
|
jbe@7
|
286 "text_search_data" TSVECTOR );
|
jbe@0
|
287 CREATE INDEX "area_active_idx" ON "area" ("active");
|
jbe@8
|
288 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
|
jbe@7
|
289 CREATE TRIGGER "update_text_search_data"
|
jbe@7
|
290 BEFORE INSERT OR UPDATE ON "area"
|
jbe@7
|
291 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@7
|
292 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@7
|
293 "name", "description" );
|
jbe@0
|
294
|
jbe@0
|
295 COMMENT ON TABLE "area" IS 'Subject areas';
|
jbe@0
|
296
|
jbe@5
|
297 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
|
jbe@5
|
298 COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
|
jbe@5
|
299 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
|
jbe@5
|
300 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
|
jbe@0
|
301
|
jbe@0
|
302
|
jbe@23
|
303 CREATE TABLE "area_setting" (
|
jbe@23
|
304 PRIMARY KEY ("member_id", "key", "area_id"),
|
jbe@23
|
305 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
306 "key" TEXT NOT NULL,
|
jbe@23
|
307 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
308 "value" TEXT NOT NULL );
|
jbe@23
|
309
|
jbe@23
|
310 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
|
jbe@23
|
311
|
jbe@23
|
312
|
jbe@9
|
313 CREATE TABLE "allowed_policy" (
|
jbe@9
|
314 PRIMARY KEY ("area_id", "policy_id"),
|
jbe@9
|
315 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@9
|
316 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@9
|
317 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
|
jbe@9
|
318 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
|
jbe@9
|
319
|
jbe@9
|
320 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
|
jbe@9
|
321
|
jbe@9
|
322 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
|
jbe@9
|
323
|
jbe@9
|
324
|
jbe@21
|
325 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
|
jbe@21
|
326
|
jbe@21
|
327 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
|
jbe@8
|
328
|
jbe@8
|
329
|
jbe@0
|
330 CREATE TABLE "issue" (
|
jbe@0
|
331 "id" SERIAL4 PRIMARY KEY,
|
jbe@0
|
332 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
333 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
334 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@0
|
335 "accepted" TIMESTAMPTZ,
|
jbe@3
|
336 "half_frozen" TIMESTAMPTZ,
|
jbe@3
|
337 "fully_frozen" TIMESTAMPTZ,
|
jbe@0
|
338 "closed" TIMESTAMPTZ,
|
jbe@0
|
339 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@22
|
340 "admission_time" INTERVAL NOT NULL,
|
jbe@22
|
341 "discussion_time" INTERVAL NOT NULL,
|
jbe@22
|
342 "verification_time" INTERVAL NOT NULL,
|
jbe@22
|
343 "voting_time" INTERVAL NOT NULL,
|
jbe@0
|
344 "snapshot" TIMESTAMPTZ,
|
jbe@8
|
345 "latest_snapshot_event" "snapshot_event",
|
jbe@0
|
346 "population" INT4,
|
jbe@0
|
347 "vote_now" INT4,
|
jbe@0
|
348 "vote_later" INT4,
|
jbe@4
|
349 "voter_count" INT4,
|
jbe@0
|
350 CONSTRAINT "valid_state" CHECK (
|
jbe@3
|
351 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
352 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
353 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@34
|
354 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
355 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@34
|
356 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
357 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
358 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
jbe@3
|
359 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
|
jbe@3
|
360 CONSTRAINT "state_change_order" CHECK (
|
jbe@10
|
361 "created" <= "accepted" AND
|
jbe@10
|
362 "accepted" <= "half_frozen" AND
|
jbe@10
|
363 "half_frozen" <= "fully_frozen" AND
|
jbe@3
|
364 "fully_frozen" <= "closed" ),
|
jbe@10
|
365 CONSTRAINT "last_snapshot_on_full_freeze"
|
jbe@10
|
366 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
|
jbe@10
|
367 CONSTRAINT "freeze_requires_snapshot"
|
jbe@10
|
368 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
|
jbe@10
|
369 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
|
jbe@10
|
370 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
|
jbe@0
|
371 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
|
jbe@0
|
372 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
|
jbe@16
|
373 CREATE INDEX "issue_created_idx" ON "issue" ("created");
|
jbe@16
|
374 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
|
jbe@16
|
375 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
|
jbe@16
|
376 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
|
jbe@16
|
377 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
|
jbe@0
|
378 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
|
jbe@16
|
379 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
|
jbe@0
|
380
|
jbe@0
|
381 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
|
jbe@0
|
382
|
jbe@8
|
383 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
|
jbe@10
|
384 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
|
jbe@10
|
385 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
|
jbe@10
|
386 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
|
jbe@8
|
387 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
|
jbe@22
|
388 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
|
jbe@22
|
389 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
|
jbe@22
|
390 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
|
jbe@22
|
391 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
|
jbe@8
|
392 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
|
jbe@8
|
393 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
|
jbe@8
|
394 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
|
jbe@8
|
395 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
|
jbe@8
|
396 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
|
jbe@8
|
397 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
|
jbe@0
|
398
|
jbe@0
|
399
|
jbe@23
|
400 CREATE TABLE "issue_setting" (
|
jbe@23
|
401 PRIMARY KEY ("member_id", "key", "issue_id"),
|
jbe@23
|
402 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
403 "key" TEXT NOT NULL,
|
jbe@23
|
404 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
405 "value" TEXT NOT NULL );
|
jbe@23
|
406
|
jbe@23
|
407 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
|
jbe@23
|
408
|
jbe@23
|
409
|
jbe@0
|
410 CREATE TABLE "initiative" (
|
jbe@0
|
411 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
|
jbe@0
|
412 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
413 "id" SERIAL4 PRIMARY KEY,
|
jbe@0
|
414 "name" TEXT NOT NULL,
|
jbe@8
|
415 "discussion_url" TEXT,
|
jbe@0
|
416 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@0
|
417 "revoked" TIMESTAMPTZ,
|
jbe@14
|
418 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
419 "admitted" BOOLEAN,
|
jbe@0
|
420 "supporter_count" INT4,
|
jbe@0
|
421 "informed_supporter_count" INT4,
|
jbe@0
|
422 "satisfied_supporter_count" INT4,
|
jbe@0
|
423 "satisfied_informed_supporter_count" INT4,
|
jbe@0
|
424 "positive_votes" INT4,
|
jbe@0
|
425 "negative_votes" INT4,
|
jbe@10
|
426 "agreed" BOOLEAN,
|
jbe@0
|
427 "rank" INT4,
|
jbe@7
|
428 "text_search_data" TSVECTOR,
|
jbe@14
|
429 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
|
jbe@14
|
430 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
|
jbe@0
|
431 CONSTRAINT "revoked_initiatives_cant_be_admitted"
|
jbe@0
|
432 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
|
jbe@0
|
433 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
|
jbe@10
|
434 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
|
jbe@10
|
435 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
|
jbe@10
|
436 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
|
jbe@10
|
437 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
|
jbe@10
|
438 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
|
jbe@16
|
439 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
|
jbe@16
|
440 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
|
jbe@8
|
441 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
|
jbe@7
|
442 CREATE TRIGGER "update_text_search_data"
|
jbe@7
|
443 BEFORE INSERT OR UPDATE ON "initiative"
|
jbe@7
|
444 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@8
|
445 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@8
|
446 "name", "discussion_url");
|
jbe@0
|
447
|
jbe@10
|
448 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
|
jbe@0
|
449
|
jbe@8
|
450 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
|
jbe@0
|
451 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
|
jbe@10
|
452 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
|
jbe@0
|
453 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
jbe@0
|
454 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
jbe@0
|
455 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
jbe@0
|
456 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
jbe@0
|
457 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
|
jbe@0
|
458 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
|
jbe@10
|
459 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
|
jbe@0
|
460 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
|
jbe@0
|
461
|
jbe@0
|
462
|
jbe@23
|
463 CREATE TABLE "initiative_setting" (
|
jbe@23
|
464 PRIMARY KEY ("member_id", "key", "initiative_id"),
|
jbe@23
|
465 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
466 "key" TEXT NOT NULL,
|
jbe@23
|
467 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
468 "value" TEXT NOT NULL );
|
jbe@23
|
469
|
jbe@23
|
470 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
|
jbe@23
|
471
|
jbe@23
|
472
|
jbe@0
|
473 CREATE TABLE "draft" (
|
jbe@0
|
474 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
|
jbe@0
|
475 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
476 "id" SERIAL8 PRIMARY KEY,
|
jbe@0
|
477 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@0
|
478 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@9
|
479 "formatting_engine" TEXT,
|
jbe@7
|
480 "content" TEXT NOT NULL,
|
jbe@7
|
481 "text_search_data" TSVECTOR );
|
jbe@16
|
482 CREATE INDEX "draft_created_idx" ON "draft" ("created");
|
jbe@9
|
483 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
|
jbe@8
|
484 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
|
jbe@7
|
485 CREATE TRIGGER "update_text_search_data"
|
jbe@7
|
486 BEFORE INSERT OR UPDATE ON "draft"
|
jbe@7
|
487 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@7
|
488 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
|
jbe@0
|
489
|
jbe@10
|
490 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
|
jbe@0
|
491
|
jbe@9
|
492 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
|
jbe@9
|
493 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
|
jbe@9
|
494
|
jbe@0
|
495
|
jbe@0
|
496 CREATE TABLE "suggestion" (
|
jbe@0
|
497 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
|
jbe@0
|
498 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
499 "id" SERIAL8 PRIMARY KEY,
|
jbe@0
|
500 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@0
|
501 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
502 "name" TEXT NOT NULL,
|
jbe@0
|
503 "description" TEXT NOT NULL DEFAULT '',
|
jbe@7
|
504 "text_search_data" TSVECTOR,
|
jbe@0
|
505 "minus2_unfulfilled_count" INT4,
|
jbe@0
|
506 "minus2_fulfilled_count" INT4,
|
jbe@0
|
507 "minus1_unfulfilled_count" INT4,
|
jbe@0
|
508 "minus1_fulfilled_count" INT4,
|
jbe@0
|
509 "plus1_unfulfilled_count" INT4,
|
jbe@0
|
510 "plus1_fulfilled_count" INT4,
|
jbe@0
|
511 "plus2_unfulfilled_count" INT4,
|
jbe@0
|
512 "plus2_fulfilled_count" INT4 );
|
jbe@16
|
513 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
|
jbe@9
|
514 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
|
jbe@8
|
515 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
|
jbe@7
|
516 CREATE TRIGGER "update_text_search_data"
|
jbe@7
|
517 BEFORE INSERT OR UPDATE ON "suggestion"
|
jbe@7
|
518 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@7
|
519 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@7
|
520 "name", "description");
|
jbe@0
|
521
|
jbe@10
|
522 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
|
jbe@0
|
523
|
jbe@0
|
524 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
525 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
526 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
527 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
528 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
529 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
530 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
531 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
jbe@0
|
532
|
jbe@0
|
533
|
jbe@23
|
534 CREATE TABLE "suggestion_setting" (
|
jbe@23
|
535 PRIMARY KEY ("member_id", "key", "suggestion_id"),
|
jbe@23
|
536 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
537 "key" TEXT NOT NULL,
|
jbe@23
|
538 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@23
|
539 "value" TEXT NOT NULL );
|
jbe@23
|
540
|
jbe@23
|
541 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
|
jbe@23
|
542
|
jbe@23
|
543
|
jbe@0
|
544 CREATE TABLE "membership" (
|
jbe@0
|
545 PRIMARY KEY ("area_id", "member_id"),
|
jbe@0
|
546 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
547 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
548 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
jbe@0
|
549 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
|
jbe@0
|
550
|
jbe@0
|
551 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
|
jbe@0
|
552
|
jbe@0
|
553 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
|
jbe@0
|
554
|
jbe@0
|
555
|
jbe@0
|
556 CREATE TABLE "interest" (
|
jbe@0
|
557 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@0
|
558 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
559 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
560 "autoreject" BOOLEAN NOT NULL,
|
jbe@0
|
561 "voting_requested" BOOLEAN );
|
jbe@0
|
562 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
|
jbe@0
|
563
|
jbe@10
|
564 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
|
jbe@0
|
565
|
jbe@0
|
566 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
|
jbe@0
|
567 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
|
jbe@0
|
568
|
jbe@0
|
569
|
jbe@0
|
570 CREATE TABLE "initiator" (
|
jbe@0
|
571 PRIMARY KEY ("initiative_id", "member_id"),
|
jbe@0
|
572 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
573 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@14
|
574 "accepted" BOOLEAN );
|
jbe@0
|
575 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
|
jbe@0
|
576
|
jbe@10
|
577 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
|
jbe@0
|
578
|
jbe@14
|
579 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
|
jbe@0
|
580
|
jbe@0
|
581
|
jbe@0
|
582 CREATE TABLE "supporter" (
|
jbe@0
|
583 "issue_id" INT4 NOT NULL,
|
jbe@0
|
584 PRIMARY KEY ("initiative_id", "member_id"),
|
jbe@0
|
585 "initiative_id" INT4,
|
jbe@0
|
586 "member_id" INT4,
|
jbe@0
|
587 "draft_id" INT8 NOT NULL,
|
jbe@10
|
588 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
589 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@0
|
590 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
|
jbe@0
|
591
|
jbe@10
|
592 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
|
jbe@0
|
593
|
jbe@2
|
594 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
|
jbe@0
|
595
|
jbe@0
|
596
|
jbe@0
|
597 CREATE TABLE "opinion" (
|
jbe@0
|
598 "initiative_id" INT4 NOT NULL,
|
jbe@0
|
599 PRIMARY KEY ("suggestion_id", "member_id"),
|
jbe@0
|
600 "suggestion_id" INT8,
|
jbe@0
|
601 "member_id" INT4,
|
jbe@0
|
602 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
|
jbe@0
|
603 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@0
|
604 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
605 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@10
|
606 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
|
jbe@0
|
607
|
jbe@10
|
608 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
|
jbe@0
|
609
|
jbe@0
|
610 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
|
jbe@0
|
611
|
jbe@0
|
612
|
jbe@10
|
613 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
|
jbe@10
|
614
|
jbe@10
|
615 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
|
jbe@10
|
616
|
jbe@10
|
617
|
jbe@0
|
618 CREATE TABLE "delegation" (
|
jbe@0
|
619 "id" SERIAL8 PRIMARY KEY,
|
jbe@0
|
620 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
621 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@10
|
622 "scope" "delegation_scope" NOT NULL,
|
jbe@0
|
623 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
624 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
625 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
|
jbe@10
|
626 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
|
jbe@10
|
627 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
|
jbe@10
|
628 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
|
jbe@10
|
629 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
|
jbe@0
|
630 UNIQUE ("area_id", "truster_id", "trustee_id"),
|
jbe@0
|
631 UNIQUE ("issue_id", "truster_id", "trustee_id") );
|
jbe@10
|
632 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
|
jbe@10
|
633 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
|
jbe@0
|
634 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
|
jbe@0
|
635 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
|
jbe@0
|
636
|
jbe@0
|
637 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
|
jbe@0
|
638
|
jbe@0
|
639 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
|
jbe@0
|
640 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
|
jbe@0
|
641
|
jbe@0
|
642
|
jbe@0
|
643 CREATE TABLE "direct_population_snapshot" (
|
jbe@0
|
644 PRIMARY KEY ("issue_id", "event", "member_id"),
|
jbe@0
|
645 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
646 "event" "snapshot_event",
|
jbe@0
|
647 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
648 "weight" INT4,
|
jbe@0
|
649 "interest_exists" BOOLEAN NOT NULL );
|
jbe@0
|
650 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
|
jbe@0
|
651
|
jbe@0
|
652 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
|
jbe@0
|
653
|
jbe@0
|
654 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
jbe@0
|
655 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
|
jbe@0
|
656 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
|
jbe@0
|
657
|
jbe@0
|
658
|
jbe@0
|
659 CREATE TABLE "delegating_population_snapshot" (
|
jbe@0
|
660 PRIMARY KEY ("issue_id", "event", "member_id"),
|
jbe@0
|
661 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
662 "event" "snapshot_event",
|
jbe@0
|
663 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@8
|
664 "weight" INT4,
|
jbe@10
|
665 "scope" "delegation_scope" NOT NULL,
|
jbe@0
|
666 "delegate_member_ids" INT4[] NOT NULL );
|
jbe@0
|
667 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
|
jbe@0
|
668
|
jbe@0
|
669 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
|
jbe@0
|
670
|
jbe@0
|
671 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
jbe@0
|
672 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
|
jbe@8
|
673 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
|
jbe@0
|
674 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
|
jbe@0
|
675
|
jbe@0
|
676
|
jbe@0
|
677 CREATE TABLE "direct_interest_snapshot" (
|
jbe@0
|
678 PRIMARY KEY ("issue_id", "event", "member_id"),
|
jbe@0
|
679 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
680 "event" "snapshot_event",
|
jbe@0
|
681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
682 "weight" INT4,
|
jbe@0
|
683 "voting_requested" BOOLEAN );
|
jbe@0
|
684 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
|
jbe@0
|
685
|
jbe@0
|
686 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
|
jbe@0
|
687
|
jbe@0
|
688 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
jbe@0
|
689 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
|
jbe@0
|
690 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
|
jbe@0
|
691
|
jbe@0
|
692
|
jbe@0
|
693 CREATE TABLE "delegating_interest_snapshot" (
|
jbe@0
|
694 PRIMARY KEY ("issue_id", "event", "member_id"),
|
jbe@0
|
695 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
696 "event" "snapshot_event",
|
jbe@0
|
697 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@8
|
698 "weight" INT4,
|
jbe@10
|
699 "scope" "delegation_scope" NOT NULL,
|
jbe@0
|
700 "delegate_member_ids" INT4[] NOT NULL );
|
jbe@0
|
701 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
|
jbe@0
|
702
|
jbe@0
|
703 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
|
jbe@0
|
704
|
jbe@0
|
705 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
jbe@0
|
706 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
|
jbe@8
|
707 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
|
jbe@0
|
708 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
|
jbe@0
|
709
|
jbe@0
|
710
|
jbe@0
|
711 CREATE TABLE "direct_supporter_snapshot" (
|
jbe@0
|
712 "issue_id" INT4 NOT NULL,
|
jbe@0
|
713 PRIMARY KEY ("initiative_id", "event", "member_id"),
|
jbe@0
|
714 "initiative_id" INT4,
|
jbe@0
|
715 "event" "snapshot_event",
|
jbe@0
|
716 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
717 "informed" BOOLEAN NOT NULL,
|
jbe@0
|
718 "satisfied" BOOLEAN NOT NULL,
|
jbe@0
|
719 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
720 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@0
|
721 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
|
jbe@0
|
722
|
jbe@8
|
723 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
|
jbe@0
|
724
|
jbe@0
|
725 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
jbe@0
|
726 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
|
jbe@0
|
727 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
|
jbe@0
|
728
|
jbe@0
|
729
|
jbe@0
|
730 CREATE TABLE "direct_voter" (
|
jbe@0
|
731 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@0
|
732 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
733 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@0
|
734 "weight" INT4,
|
jbe@0
|
735 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
jbe@0
|
736 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
|
jbe@0
|
737
|
jbe@10
|
738 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
|
jbe@0
|
739
|
jbe@0
|
740 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
|
jbe@0
|
741 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
|
jbe@0
|
742
|
jbe@0
|
743
|
jbe@0
|
744 CREATE TABLE "delegating_voter" (
|
jbe@0
|
745 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@0
|
746 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@8
|
748 "weight" INT4,
|
jbe@10
|
749 "scope" "delegation_scope" NOT NULL,
|
jbe@0
|
750 "delegate_member_ids" INT4[] NOT NULL );
|
jbe@0
|
751 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
|
jbe@0
|
752
|
jbe@0
|
753 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
|
jbe@0
|
754
|
jbe@0
|
755 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
|
jbe@8
|
756 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
|
jbe@0
|
757 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
|
jbe@0
|
758
|
jbe@0
|
759
|
jbe@0
|
760 CREATE TABLE "vote" (
|
jbe@0
|
761 "issue_id" INT4 NOT NULL,
|
jbe@0
|
762 PRIMARY KEY ("initiative_id", "member_id"),
|
jbe@0
|
763 "initiative_id" INT4,
|
jbe@0
|
764 "member_id" INT4,
|
jbe@0
|
765 "grade" INT4,
|
jbe@0
|
766 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@0
|
767 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@0
|
768 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
|
jbe@0
|
769
|
jbe@10
|
770 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
|
jbe@0
|
771
|
jbe@0
|
772 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
|
jbe@0
|
773
|
jbe@0
|
774
|
jbe@9
|
775 CREATE TABLE "contingent" (
|
jbe@9
|
776 "time_frame" INTERVAL PRIMARY KEY,
|
jbe@9
|
777 "text_entry_limit" INT4,
|
jbe@9
|
778 "initiative_limit" INT4 );
|
jbe@9
|
779
|
jbe@9
|
780 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
|
jbe@9
|
781
|
jbe@9
|
782 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
|
jbe@9
|
783 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
|
jbe@9
|
784
|
jbe@9
|
785
|
jbe@0
|
786
|
jbe@13
|
787 --------------------------------
|
jbe@13
|
788 -- Writing of history entries --
|
jbe@13
|
789 --------------------------------
|
jbe@13
|
790
|
jbe@13
|
791 CREATE FUNCTION "write_member_history_trigger"()
|
jbe@13
|
792 RETURNS TRIGGER
|
jbe@13
|
793 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@13
|
794 BEGIN
|
jbe@13
|
795 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
|
jbe@13
|
796 INSERT INTO "member_history" ("member_id", "login", "name")
|
jbe@13
|
797 VALUES (NEW."id", OLD."login", OLD."name");
|
jbe@13
|
798 END IF;
|
jbe@13
|
799 RETURN NULL;
|
jbe@13
|
800 END;
|
jbe@13
|
801 $$;
|
jbe@13
|
802
|
jbe@13
|
803 CREATE TRIGGER "write_member_history"
|
jbe@13
|
804 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@13
|
805 "write_member_history_trigger"();
|
jbe@13
|
806
|
jbe@13
|
807 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
|
jbe@13
|
808 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
|
jbe@13
|
809
|
jbe@13
|
810
|
jbe@13
|
811
|
jbe@0
|
812 ----------------------------
|
jbe@0
|
813 -- Additional constraints --
|
jbe@0
|
814 ----------------------------
|
jbe@0
|
815
|
jbe@0
|
816
|
jbe@0
|
817 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
|
jbe@0
|
818 RETURNS TRIGGER
|
jbe@0
|
819 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
820 BEGIN
|
jbe@0
|
821 IF NOT EXISTS (
|
jbe@0
|
822 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
|
jbe@0
|
823 ) THEN
|
jbe@0
|
824 --RAISE 'Cannot create issue without an initial initiative.' USING
|
jbe@0
|
825 -- ERRCODE = 'integrity_constraint_violation',
|
jbe@0
|
826 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
|
jbe@0
|
827 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
|
jbe@0
|
828 END IF;
|
jbe@0
|
829 RETURN NULL;
|
jbe@0
|
830 END;
|
jbe@0
|
831 $$;
|
jbe@0
|
832
|
jbe@0
|
833 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
|
jbe@0
|
834 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
835 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
836 "issue_requires_first_initiative_trigger"();
|
jbe@0
|
837
|
jbe@0
|
838 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
|
jbe@0
|
839 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
|
jbe@0
|
840
|
jbe@0
|
841
|
jbe@0
|
842 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
|
jbe@0
|
843 RETURNS TRIGGER
|
jbe@0
|
844 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
845 DECLARE
|
jbe@0
|
846 "reference_lost" BOOLEAN;
|
jbe@0
|
847 BEGIN
|
jbe@0
|
848 IF TG_OP = 'DELETE' THEN
|
jbe@0
|
849 "reference_lost" := TRUE;
|
jbe@0
|
850 ELSE
|
jbe@0
|
851 "reference_lost" := NEW."issue_id" != OLD."issue_id";
|
jbe@0
|
852 END IF;
|
jbe@0
|
853 IF
|
jbe@0
|
854 "reference_lost" AND NOT EXISTS (
|
jbe@0
|
855 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
|
jbe@0
|
856 )
|
jbe@0
|
857 THEN
|
jbe@0
|
858 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
|
jbe@0
|
859 END IF;
|
jbe@0
|
860 RETURN NULL;
|
jbe@0
|
861 END;
|
jbe@0
|
862 $$;
|
jbe@0
|
863
|
jbe@0
|
864 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
|
jbe@0
|
865 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
866 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
867 "last_initiative_deletes_issue_trigger"();
|
jbe@0
|
868
|
jbe@0
|
869 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
|
jbe@0
|
870 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
|
jbe@0
|
871
|
jbe@0
|
872
|
jbe@0
|
873 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
|
jbe@0
|
874 RETURNS TRIGGER
|
jbe@0
|
875 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
876 BEGIN
|
jbe@0
|
877 IF NOT EXISTS (
|
jbe@0
|
878 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
|
jbe@0
|
879 ) THEN
|
jbe@0
|
880 --RAISE 'Cannot create initiative without an initial draft.' USING
|
jbe@0
|
881 -- ERRCODE = 'integrity_constraint_violation',
|
jbe@0
|
882 -- HINT = 'Create issue, initiative and draft within the same transaction.';
|
jbe@0
|
883 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
|
jbe@0
|
884 END IF;
|
jbe@0
|
885 RETURN NULL;
|
jbe@0
|
886 END;
|
jbe@0
|
887 $$;
|
jbe@0
|
888
|
jbe@0
|
889 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
|
jbe@0
|
890 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
891 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
892 "initiative_requires_first_draft_trigger"();
|
jbe@0
|
893
|
jbe@0
|
894 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
|
jbe@0
|
895 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
|
jbe@0
|
896
|
jbe@0
|
897
|
jbe@0
|
898 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
|
jbe@0
|
899 RETURNS TRIGGER
|
jbe@0
|
900 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
901 DECLARE
|
jbe@0
|
902 "reference_lost" BOOLEAN;
|
jbe@0
|
903 BEGIN
|
jbe@0
|
904 IF TG_OP = 'DELETE' THEN
|
jbe@0
|
905 "reference_lost" := TRUE;
|
jbe@0
|
906 ELSE
|
jbe@0
|
907 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
|
jbe@0
|
908 END IF;
|
jbe@0
|
909 IF
|
jbe@0
|
910 "reference_lost" AND NOT EXISTS (
|
jbe@0
|
911 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
|
jbe@0
|
912 )
|
jbe@0
|
913 THEN
|
jbe@0
|
914 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
|
jbe@0
|
915 END IF;
|
jbe@0
|
916 RETURN NULL;
|
jbe@0
|
917 END;
|
jbe@0
|
918 $$;
|
jbe@0
|
919
|
jbe@0
|
920 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
|
jbe@0
|
921 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
922 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
923 "last_draft_deletes_initiative_trigger"();
|
jbe@0
|
924
|
jbe@0
|
925 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
|
jbe@0
|
926 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
|
jbe@0
|
927
|
jbe@0
|
928
|
jbe@0
|
929 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
|
jbe@0
|
930 RETURNS TRIGGER
|
jbe@0
|
931 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
932 BEGIN
|
jbe@0
|
933 IF NOT EXISTS (
|
jbe@0
|
934 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
|
jbe@0
|
935 ) THEN
|
jbe@0
|
936 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
|
jbe@0
|
937 END IF;
|
jbe@0
|
938 RETURN NULL;
|
jbe@0
|
939 END;
|
jbe@0
|
940 $$;
|
jbe@0
|
941
|
jbe@0
|
942 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
|
jbe@0
|
943 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
944 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
945 "suggestion_requires_first_opinion_trigger"();
|
jbe@0
|
946
|
jbe@0
|
947 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
|
jbe@0
|
948 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
|
jbe@0
|
949
|
jbe@0
|
950
|
jbe@0
|
951 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
|
jbe@0
|
952 RETURNS TRIGGER
|
jbe@0
|
953 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
954 DECLARE
|
jbe@0
|
955 "reference_lost" BOOLEAN;
|
jbe@0
|
956 BEGIN
|
jbe@0
|
957 IF TG_OP = 'DELETE' THEN
|
jbe@0
|
958 "reference_lost" := TRUE;
|
jbe@0
|
959 ELSE
|
jbe@0
|
960 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
|
jbe@0
|
961 END IF;
|
jbe@0
|
962 IF
|
jbe@0
|
963 "reference_lost" AND NOT EXISTS (
|
jbe@0
|
964 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
|
jbe@0
|
965 )
|
jbe@0
|
966 THEN
|
jbe@0
|
967 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
|
jbe@0
|
968 END IF;
|
jbe@0
|
969 RETURN NULL;
|
jbe@0
|
970 END;
|
jbe@0
|
971 $$;
|
jbe@0
|
972
|
jbe@0
|
973 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
|
jbe@0
|
974 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
|
jbe@0
|
975 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@0
|
976 "last_opinion_deletes_suggestion_trigger"();
|
jbe@0
|
977
|
jbe@0
|
978 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
|
jbe@0
|
979 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
|
jbe@0
|
980
|
jbe@0
|
981
|
jbe@0
|
982
|
jbe@20
|
983 ---------------------------------------------------------------
|
jbe@20
|
984 -- Ensure that votes are not modified when issues are frozen --
|
jbe@20
|
985 ---------------------------------------------------------------
|
jbe@20
|
986
|
jbe@20
|
987 -- NOTE: Frontends should ensure this anyway, but in case of programming
|
jbe@20
|
988 -- errors the following triggers ensure data integrity.
|
jbe@20
|
989
|
jbe@20
|
990
|
jbe@20
|
991 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
jbe@20
|
992 RETURNS TRIGGER
|
jbe@20
|
993 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@20
|
994 DECLARE
|
jbe@32
|
995 "issue_id_v" "issue"."id"%TYPE;
|
jbe@32
|
996 "issue_row" "issue"%ROWTYPE;
|
jbe@20
|
997 BEGIN
|
jbe@32
|
998 IF TG_OP = 'DELETE' THEN
|
jbe@32
|
999 "issue_id_v" := OLD."issue_id";
|
jbe@32
|
1000 ELSE
|
jbe@32
|
1001 "issue_id_v" := NEW."issue_id";
|
jbe@32
|
1002 END IF;
|
jbe@20
|
1003 SELECT INTO "issue_row" * FROM "issue"
|
jbe@32
|
1004 WHERE "id" = "issue_id_v" FOR SHARE;
|
jbe@20
|
1005 IF "issue_row"."closed" NOTNULL THEN
|
jbe@20
|
1006 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
|
jbe@20
|
1007 END IF;
|
jbe@20
|
1008 RETURN NULL;
|
jbe@20
|
1009 END;
|
jbe@20
|
1010 $$;
|
jbe@20
|
1011
|
jbe@20
|
1012 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
jbe@20
|
1013 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
|
jbe@20
|
1014 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@20
|
1015 "forbid_changes_on_closed_issue_trigger"();
|
jbe@20
|
1016
|
jbe@20
|
1017 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
jbe@20
|
1018 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
|
jbe@20
|
1019 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@20
|
1020 "forbid_changes_on_closed_issue_trigger"();
|
jbe@20
|
1021
|
jbe@20
|
1022 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
jbe@20
|
1023 AFTER INSERT OR UPDATE OR DELETE ON "vote"
|
jbe@20
|
1024 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@20
|
1025 "forbid_changes_on_closed_issue_trigger"();
|
jbe@20
|
1026
|
jbe@20
|
1027 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
|
jbe@20
|
1028 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
jbe@20
|
1029 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
jbe@20
|
1030 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
jbe@20
|
1031
|
jbe@20
|
1032
|
jbe@20
|
1033
|
jbe@0
|
1034 --------------------------------------------------------------------
|
jbe@0
|
1035 -- Auto-retrieval of fields only needed for referential integrity --
|
jbe@0
|
1036 --------------------------------------------------------------------
|
jbe@0
|
1037
|
jbe@20
|
1038
|
jbe@0
|
1039 CREATE FUNCTION "autofill_issue_id_trigger"()
|
jbe@0
|
1040 RETURNS TRIGGER
|
jbe@0
|
1041 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1042 BEGIN
|
jbe@0
|
1043 IF NEW."issue_id" ISNULL THEN
|
jbe@0
|
1044 SELECT "issue_id" INTO NEW."issue_id"
|
jbe@0
|
1045 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
jbe@0
|
1046 END IF;
|
jbe@0
|
1047 RETURN NEW;
|
jbe@0
|
1048 END;
|
jbe@0
|
1049 $$;
|
jbe@0
|
1050
|
jbe@0
|
1051 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
|
jbe@0
|
1052 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
jbe@0
|
1053
|
jbe@0
|
1054 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
|
jbe@0
|
1055 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
jbe@0
|
1056
|
jbe@0
|
1057 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
|
jbe@0
|
1058 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
|
jbe@0
|
1059 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
|
jbe@0
|
1060
|
jbe@0
|
1061
|
jbe@0
|
1062 CREATE FUNCTION "autofill_initiative_id_trigger"()
|
jbe@0
|
1063 RETURNS TRIGGER
|
jbe@0
|
1064 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1065 BEGIN
|
jbe@0
|
1066 IF NEW."initiative_id" ISNULL THEN
|
jbe@0
|
1067 SELECT "initiative_id" INTO NEW."initiative_id"
|
jbe@0
|
1068 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
jbe@0
|
1069 END IF;
|
jbe@0
|
1070 RETURN NEW;
|
jbe@0
|
1071 END;
|
jbe@0
|
1072 $$;
|
jbe@0
|
1073
|
jbe@0
|
1074 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
|
jbe@0
|
1075 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
|
jbe@0
|
1076
|
jbe@0
|
1077 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
|
jbe@0
|
1078 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
|
jbe@0
|
1079
|
jbe@0
|
1080
|
jbe@0
|
1081
|
jbe@4
|
1082 -----------------------------------------------------
|
jbe@4
|
1083 -- Automatic calculation of certain default values --
|
jbe@4
|
1084 -----------------------------------------------------
|
jbe@0
|
1085
|
jbe@22
|
1086
|
jbe@22
|
1087 CREATE FUNCTION "copy_timings_trigger"()
|
jbe@22
|
1088 RETURNS TRIGGER
|
jbe@22
|
1089 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@22
|
1090 DECLARE
|
jbe@22
|
1091 "policy_row" "policy"%ROWTYPE;
|
jbe@22
|
1092 BEGIN
|
jbe@22
|
1093 SELECT * INTO "policy_row" FROM "policy"
|
jbe@22
|
1094 WHERE "id" = NEW."policy_id";
|
jbe@22
|
1095 IF NEW."admission_time" ISNULL THEN
|
jbe@22
|
1096 NEW."admission_time" := "policy_row"."admission_time";
|
jbe@22
|
1097 END IF;
|
jbe@22
|
1098 IF NEW."discussion_time" ISNULL THEN
|
jbe@22
|
1099 NEW."discussion_time" := "policy_row"."discussion_time";
|
jbe@22
|
1100 END IF;
|
jbe@22
|
1101 IF NEW."verification_time" ISNULL THEN
|
jbe@22
|
1102 NEW."verification_time" := "policy_row"."verification_time";
|
jbe@22
|
1103 END IF;
|
jbe@22
|
1104 IF NEW."voting_time" ISNULL THEN
|
jbe@22
|
1105 NEW."voting_time" := "policy_row"."voting_time";
|
jbe@22
|
1106 END IF;
|
jbe@22
|
1107 RETURN NEW;
|
jbe@22
|
1108 END;
|
jbe@22
|
1109 $$;
|
jbe@22
|
1110
|
jbe@22
|
1111 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
|
jbe@22
|
1112 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
|
jbe@22
|
1113
|
jbe@22
|
1114 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
|
jbe@22
|
1115 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
|
jbe@22
|
1116
|
jbe@22
|
1117
|
jbe@0
|
1118 CREATE FUNCTION "copy_autoreject_trigger"()
|
jbe@0
|
1119 RETURNS TRIGGER
|
jbe@0
|
1120 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1121 BEGIN
|
jbe@0
|
1122 IF NEW."autoreject" ISNULL THEN
|
jbe@0
|
1123 SELECT "membership"."autoreject" INTO NEW."autoreject"
|
jbe@0
|
1124 FROM "issue" JOIN "membership"
|
jbe@0
|
1125 ON "issue"."area_id" = "membership"."area_id"
|
jbe@0
|
1126 WHERE "issue"."id" = NEW."issue_id"
|
jbe@0
|
1127 AND "membership"."member_id" = NEW."member_id";
|
jbe@0
|
1128 END IF;
|
jbe@0
|
1129 IF NEW."autoreject" ISNULL THEN
|
jbe@0
|
1130 NEW."autoreject" := FALSE;
|
jbe@0
|
1131 END IF;
|
jbe@0
|
1132 RETURN NEW;
|
jbe@0
|
1133 END;
|
jbe@0
|
1134 $$;
|
jbe@0
|
1135
|
jbe@0
|
1136 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
|
jbe@0
|
1137 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
|
jbe@0
|
1138
|
jbe@0
|
1139 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
|
jbe@0
|
1140 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
|
jbe@0
|
1141
|
jbe@0
|
1142
|
jbe@2
|
1143 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
|
jbe@2
|
1144 RETURNS TRIGGER
|
jbe@2
|
1145 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@2
|
1146 BEGIN
|
jbe@2
|
1147 IF NEW."draft_id" ISNULL THEN
|
jbe@2
|
1148 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
|
jbe@2
|
1149 WHERE "initiative_id" = NEW."initiative_id";
|
jbe@2
|
1150 END IF;
|
jbe@2
|
1151 RETURN NEW;
|
jbe@2
|
1152 END;
|
jbe@2
|
1153 $$;
|
jbe@2
|
1154
|
jbe@2
|
1155 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
|
jbe@2
|
1156 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
|
jbe@2
|
1157
|
jbe@2
|
1158 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
|
jbe@2
|
1159 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
|
jbe@2
|
1160
|
jbe@2
|
1161
|
jbe@0
|
1162
|
jbe@0
|
1163 ----------------------------------------
|
jbe@0
|
1164 -- Automatic creation of dependencies --
|
jbe@0
|
1165 ----------------------------------------
|
jbe@0
|
1166
|
jbe@22
|
1167
|
jbe@0
|
1168 CREATE FUNCTION "autocreate_interest_trigger"()
|
jbe@0
|
1169 RETURNS TRIGGER
|
jbe@0
|
1170 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1171 BEGIN
|
jbe@0
|
1172 IF NOT EXISTS (
|
jbe@0
|
1173 SELECT NULL FROM "initiative" JOIN "interest"
|
jbe@0
|
1174 ON "initiative"."issue_id" = "interest"."issue_id"
|
jbe@0
|
1175 WHERE "initiative"."id" = NEW."initiative_id"
|
jbe@0
|
1176 AND "interest"."member_id" = NEW."member_id"
|
jbe@0
|
1177 ) THEN
|
jbe@0
|
1178 BEGIN
|
jbe@0
|
1179 INSERT INTO "interest" ("issue_id", "member_id")
|
jbe@0
|
1180 SELECT "issue_id", NEW."member_id"
|
jbe@0
|
1181 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
jbe@0
|
1182 EXCEPTION WHEN unique_violation THEN END;
|
jbe@0
|
1183 END IF;
|
jbe@0
|
1184 RETURN NEW;
|
jbe@0
|
1185 END;
|
jbe@0
|
1186 $$;
|
jbe@0
|
1187
|
jbe@0
|
1188 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
|
jbe@0
|
1189 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
|
jbe@0
|
1190
|
jbe@0
|
1191 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
|
jbe@0
|
1192 COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
|
jbe@0
|
1193
|
jbe@0
|
1194
|
jbe@0
|
1195 CREATE FUNCTION "autocreate_supporter_trigger"()
|
jbe@0
|
1196 RETURNS TRIGGER
|
jbe@0
|
1197 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1198 BEGIN
|
jbe@0
|
1199 IF NOT EXISTS (
|
jbe@0
|
1200 SELECT NULL FROM "suggestion" JOIN "supporter"
|
jbe@0
|
1201 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
|
jbe@0
|
1202 WHERE "suggestion"."id" = NEW."suggestion_id"
|
jbe@0
|
1203 AND "supporter"."member_id" = NEW."member_id"
|
jbe@0
|
1204 ) THEN
|
jbe@0
|
1205 BEGIN
|
jbe@0
|
1206 INSERT INTO "supporter" ("initiative_id", "member_id")
|
jbe@0
|
1207 SELECT "initiative_id", NEW."member_id"
|
jbe@0
|
1208 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
jbe@0
|
1209 EXCEPTION WHEN unique_violation THEN END;
|
jbe@0
|
1210 END IF;
|
jbe@0
|
1211 RETURN NEW;
|
jbe@0
|
1212 END;
|
jbe@0
|
1213 $$;
|
jbe@0
|
1214
|
jbe@0
|
1215 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
|
jbe@0
|
1216 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
|
jbe@0
|
1217
|
jbe@0
|
1218 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
|
jbe@0
|
1219 COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
|
jbe@0
|
1220
|
jbe@0
|
1221
|
jbe@0
|
1222
|
jbe@0
|
1223 ------------------------------------------
|
jbe@0
|
1224 -- Views and helper functions for views --
|
jbe@0
|
1225 ------------------------------------------
|
jbe@0
|
1226
|
jbe@5
|
1227
|
jbe@5
|
1228 CREATE VIEW "global_delegation" AS
|
jbe@5
|
1229 SELECT
|
jbe@5
|
1230 "delegation"."id",
|
jbe@5
|
1231 "delegation"."truster_id",
|
jbe@5
|
1232 "delegation"."trustee_id"
|
jbe@5
|
1233 FROM "delegation" JOIN "member"
|
jbe@5
|
1234 ON "delegation"."trustee_id" = "member"."id"
|
jbe@10
|
1235 WHERE "delegation"."scope" = 'global' AND "member"."active";
|
jbe@5
|
1236
|
jbe@5
|
1237 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
|
jbe@5
|
1238
|
jbe@5
|
1239
|
jbe@5
|
1240 CREATE VIEW "area_delegation" AS
|
jbe@5
|
1241 SELECT "subquery".* FROM (
|
jbe@5
|
1242 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
|
jbe@10
|
1243 "area"."id" AS "area_id",
|
jbe@10
|
1244 "delegation"."id",
|
jbe@10
|
1245 "delegation"."truster_id",
|
jbe@10
|
1246 "delegation"."trustee_id",
|
jbe@10
|
1247 "delegation"."scope"
|
jbe@5
|
1248 FROM "area" JOIN "delegation"
|
jbe@10
|
1249 ON "delegation"."scope" = 'global'
|
jbe@10
|
1250 OR "delegation"."area_id" = "area"."id"
|
jbe@5
|
1251 ORDER BY
|
jbe@5
|
1252 "area"."id",
|
jbe@5
|
1253 "delegation"."truster_id",
|
jbe@10
|
1254 "delegation"."scope" DESC
|
jbe@5
|
1255 ) AS "subquery"
|
jbe@5
|
1256 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
|
jbe@5
|
1257 WHERE "member"."active";
|
jbe@5
|
1258
|
jbe@5
|
1259 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
|
jbe@5
|
1260
|
jbe@5
|
1261
|
jbe@5
|
1262 CREATE VIEW "issue_delegation" AS
|
jbe@5
|
1263 SELECT "subquery".* FROM (
|
jbe@5
|
1264 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
jbe@10
|
1265 "issue"."id" AS "issue_id",
|
jbe@10
|
1266 "delegation"."id",
|
jbe@10
|
1267 "delegation"."truster_id",
|
jbe@10
|
1268 "delegation"."trustee_id",
|
jbe@10
|
1269 "delegation"."scope"
|
jbe@5
|
1270 FROM "issue" JOIN "delegation"
|
jbe@10
|
1271 ON "delegation"."scope" = 'global'
|
jbe@10
|
1272 OR "delegation"."area_id" = "issue"."area_id"
|
jbe@10
|
1273 OR "delegation"."issue_id" = "issue"."id"
|
jbe@5
|
1274 ORDER BY
|
jbe@5
|
1275 "issue"."id",
|
jbe@5
|
1276 "delegation"."truster_id",
|
jbe@10
|
1277 "delegation"."scope" DESC
|
jbe@5
|
1278 ) AS "subquery"
|
jbe@5
|
1279 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
|
jbe@5
|
1280 WHERE "member"."active";
|
jbe@5
|
1281
|
jbe@5
|
1282 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
|
jbe@5
|
1283
|
jbe@5
|
1284
|
jbe@5
|
1285 CREATE FUNCTION "membership_weight_with_skipping"
|
jbe@5
|
1286 ( "area_id_p" "area"."id"%TYPE,
|
jbe@5
|
1287 "member_id_p" "member"."id"%TYPE,
|
jbe@5
|
1288 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
|
jbe@5
|
1289 RETURNS INT4
|
jbe@5
|
1290 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@5
|
1291 DECLARE
|
jbe@5
|
1292 "sum_v" INT4;
|
jbe@5
|
1293 "delegation_row" "area_delegation"%ROWTYPE;
|
jbe@5
|
1294 BEGIN
|
jbe@5
|
1295 "sum_v" := 1;
|
jbe@5
|
1296 FOR "delegation_row" IN
|
jbe@5
|
1297 SELECT "area_delegation".*
|
jbe@5
|
1298 FROM "area_delegation" LEFT JOIN "membership"
|
jbe@5
|
1299 ON "membership"."area_id" = "area_id_p"
|
jbe@5
|
1300 AND "membership"."member_id" = "area_delegation"."truster_id"
|
jbe@5
|
1301 WHERE "area_delegation"."area_id" = "area_id_p"
|
jbe@5
|
1302 AND "area_delegation"."trustee_id" = "member_id_p"
|
jbe@5
|
1303 AND "membership"."member_id" ISNULL
|
jbe@5
|
1304 LOOP
|
jbe@5
|
1305 IF NOT
|
jbe@5
|
1306 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
|
jbe@5
|
1307 THEN
|
jbe@5
|
1308 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
|
jbe@5
|
1309 "area_id_p",
|
jbe@5
|
1310 "delegation_row"."truster_id",
|
jbe@5
|
1311 "skip_member_ids_p" || "delegation_row"."truster_id"
|
jbe@5
|
1312 );
|
jbe@5
|
1313 END IF;
|
jbe@5
|
1314 END LOOP;
|
jbe@5
|
1315 RETURN "sum_v";
|
jbe@5
|
1316 END;
|
jbe@5
|
1317 $$;
|
jbe@5
|
1318
|
jbe@8
|
1319 COMMENT ON FUNCTION "membership_weight_with_skipping"
|
jbe@8
|
1320 ( "area"."id"%TYPE,
|
jbe@8
|
1321 "member"."id"%TYPE,
|
jbe@8
|
1322 INT4[] )
|
jbe@8
|
1323 IS 'Helper function for "membership_weight" function';
|
jbe@8
|
1324
|
jbe@8
|
1325
|
jbe@5
|
1326 CREATE FUNCTION "membership_weight"
|
jbe@5
|
1327 ( "area_id_p" "area"."id"%TYPE,
|
jbe@5
|
1328 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
|
jbe@5
|
1329 RETURNS INT4
|
jbe@5
|
1330 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@5
|
1331 BEGIN
|
jbe@5
|
1332 RETURN "membership_weight_with_skipping"(
|
jbe@5
|
1333 "area_id_p",
|
jbe@5
|
1334 "member_id_p",
|
jbe@5
|
1335 ARRAY["member_id_p"]
|
jbe@5
|
1336 );
|
jbe@5
|
1337 END;
|
jbe@5
|
1338 $$;
|
jbe@5
|
1339
|
jbe@8
|
1340 COMMENT ON FUNCTION "membership_weight"
|
jbe@8
|
1341 ( "area"."id"%TYPE,
|
jbe@8
|
1342 "member"."id"%TYPE )
|
jbe@8
|
1343 IS 'Calculates the potential voting weight of a member in a given area';
|
jbe@8
|
1344
|
jbe@5
|
1345
|
jbe@4
|
1346 CREATE VIEW "member_count_view" AS
|
jbe@5
|
1347 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
|
jbe@4
|
1348
|
jbe@4
|
1349 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
|
jbe@4
|
1350
|
jbe@4
|
1351
|
jbe@4
|
1352 CREATE VIEW "area_member_count" AS
|
jbe@5
|
1353 SELECT
|
jbe@5
|
1354 "area"."id" AS "area_id",
|
jbe@5
|
1355 count("member"."id") AS "direct_member_count",
|
jbe@5
|
1356 coalesce(
|
jbe@5
|
1357 sum(
|
jbe@5
|
1358 CASE WHEN "member"."id" NOTNULL THEN
|
jbe@5
|
1359 "membership_weight"("area"."id", "member"."id")
|
jbe@5
|
1360 ELSE 0 END
|
jbe@5
|
1361 )
|
jbe@5
|
1362 ) AS "member_weight",
|
jbe@5
|
1363 coalesce(
|
jbe@5
|
1364 sum(
|
jbe@5
|
1365 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
|
jbe@5
|
1366 "membership_weight"("area"."id", "member"."id")
|
jbe@5
|
1367 ELSE 0 END
|
jbe@5
|
1368 )
|
jbe@5
|
1369 ) AS "autoreject_weight"
|
jbe@4
|
1370 FROM "area"
|
jbe@4
|
1371 LEFT JOIN "membership"
|
jbe@4
|
1372 ON "area"."id" = "membership"."area_id"
|
jbe@4
|
1373 LEFT JOIN "member"
|
jbe@4
|
1374 ON "membership"."member_id" = "member"."id"
|
jbe@4
|
1375 AND "member"."active"
|
jbe@4
|
1376 GROUP BY "area"."id";
|
jbe@4
|
1377
|
jbe@4
|
1378 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
|
jbe@4
|
1379
|
jbe@4
|
1380
|
jbe@9
|
1381 CREATE VIEW "opening_draft" AS
|
jbe@9
|
1382 SELECT "draft".* FROM (
|
jbe@9
|
1383 SELECT
|
jbe@9
|
1384 "initiative"."id" AS "initiative_id",
|
jbe@9
|
1385 min("draft"."id") AS "draft_id"
|
jbe@9
|
1386 FROM "initiative" JOIN "draft"
|
jbe@9
|
1387 ON "initiative"."id" = "draft"."initiative_id"
|
jbe@9
|
1388 GROUP BY "initiative"."id"
|
jbe@9
|
1389 ) AS "subquery"
|
jbe@9
|
1390 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
|
jbe@9
|
1391
|
jbe@9
|
1392 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
|
jbe@9
|
1393
|
jbe@9
|
1394
|
jbe@0
|
1395 CREATE VIEW "current_draft" AS
|
jbe@0
|
1396 SELECT "draft".* FROM (
|
jbe@0
|
1397 SELECT
|
jbe@0
|
1398 "initiative"."id" AS "initiative_id",
|
jbe@0
|
1399 max("draft"."id") AS "draft_id"
|
jbe@0
|
1400 FROM "initiative" JOIN "draft"
|
jbe@0
|
1401 ON "initiative"."id" = "draft"."initiative_id"
|
jbe@0
|
1402 GROUP BY "initiative"."id"
|
jbe@0
|
1403 ) AS "subquery"
|
jbe@0
|
1404 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
|
jbe@0
|
1405
|
jbe@0
|
1406 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
|
jbe@0
|
1407
|
jbe@0
|
1408
|
jbe@0
|
1409 CREATE VIEW "critical_opinion" AS
|
jbe@0
|
1410 SELECT * FROM "opinion"
|
jbe@0
|
1411 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
|
jbe@0
|
1412 OR ("degree" = -2 AND "fulfilled" = TRUE);
|
jbe@0
|
1413
|
jbe@0
|
1414 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
|
jbe@0
|
1415
|
jbe@0
|
1416
|
jbe@0
|
1417 CREATE VIEW "battle" AS
|
jbe@0
|
1418 SELECT
|
jbe@0
|
1419 "issue"."id" AS "issue_id",
|
jbe@10
|
1420 "winning_initiative"."id" AS "winning_initiative_id",
|
jbe@10
|
1421 "losing_initiative"."id" AS "losing_initiative_id",
|
jbe@0
|
1422 sum(
|
jbe@0
|
1423 CASE WHEN
|
jbe@0
|
1424 coalesce("better_vote"."grade", 0) >
|
jbe@0
|
1425 coalesce("worse_vote"."grade", 0)
|
jbe@0
|
1426 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@0
|
1427 ) AS "count"
|
jbe@0
|
1428 FROM "issue"
|
jbe@0
|
1429 LEFT JOIN "direct_voter"
|
jbe@0
|
1430 ON "issue"."id" = "direct_voter"."issue_id"
|
jbe@10
|
1431 JOIN "initiative" AS "winning_initiative"
|
jbe@10
|
1432 ON "issue"."id" = "winning_initiative"."issue_id"
|
jbe@10
|
1433 AND "winning_initiative"."agreed"
|
jbe@10
|
1434 JOIN "initiative" AS "losing_initiative"
|
jbe@10
|
1435 ON "issue"."id" = "losing_initiative"."issue_id"
|
jbe@10
|
1436 AND "losing_initiative"."agreed"
|
jbe@0
|
1437 LEFT JOIN "vote" AS "better_vote"
|
jbe@10
|
1438 ON "direct_voter"."member_id" = "better_vote"."member_id"
|
jbe@10
|
1439 AND "winning_initiative"."id" = "better_vote"."initiative_id"
|
jbe@0
|
1440 LEFT JOIN "vote" AS "worse_vote"
|
jbe@10
|
1441 ON "direct_voter"."member_id" = "worse_vote"."member_id"
|
jbe@10
|
1442 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
|
jbe@0
|
1443 WHERE
|
jbe@10
|
1444 "winning_initiative"."id" != "losing_initiative"."id"
|
jbe@0
|
1445 GROUP BY
|
jbe@0
|
1446 "issue"."id",
|
jbe@10
|
1447 "winning_initiative"."id",
|
jbe@10
|
1448 "losing_initiative"."id";
|
jbe@0
|
1449
|
jbe@0
|
1450 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
|
jbe@0
|
1451
|
jbe@0
|
1452
|
jbe@1
|
1453 CREATE VIEW "expired_session" AS
|
jbe@1
|
1454 SELECT * FROM "session" WHERE now() > "expiry";
|
jbe@1
|
1455
|
jbe@1
|
1456 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
|
jbe@1
|
1457 DELETE FROM "session" WHERE "ident" = OLD."ident";
|
jbe@1
|
1458
|
jbe@1
|
1459 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
|
jbe@1
|
1460 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
|
jbe@1
|
1461
|
jbe@1
|
1462
|
jbe@0
|
1463 CREATE VIEW "open_issue" AS
|
jbe@0
|
1464 SELECT * FROM "issue" WHERE "closed" ISNULL;
|
jbe@0
|
1465
|
jbe@0
|
1466 COMMENT ON VIEW "open_issue" IS 'All open issues';
|
jbe@0
|
1467
|
jbe@0
|
1468
|
jbe@0
|
1469 CREATE VIEW "issue_with_ranks_missing" AS
|
jbe@0
|
1470 SELECT * FROM "issue"
|
jbe@3
|
1471 WHERE "fully_frozen" NOTNULL
|
jbe@0
|
1472 AND "closed" NOTNULL
|
jbe@0
|
1473 AND "ranks_available" = FALSE;
|
jbe@0
|
1474
|
jbe@0
|
1475 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
|
jbe@0
|
1476
|
jbe@0
|
1477
|
jbe@9
|
1478 CREATE VIEW "member_contingent" AS
|
jbe@9
|
1479 SELECT
|
jbe@9
|
1480 "member"."id" AS "member_id",
|
jbe@9
|
1481 "contingent"."time_frame",
|
jbe@9
|
1482 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
|
jbe@9
|
1483 (
|
jbe@9
|
1484 SELECT count(1) FROM "draft"
|
jbe@9
|
1485 WHERE "draft"."author_id" = "member"."id"
|
jbe@9
|
1486 AND "draft"."created" > now() - "contingent"."time_frame"
|
jbe@9
|
1487 ) + (
|
jbe@9
|
1488 SELECT count(1) FROM "suggestion"
|
jbe@9
|
1489 WHERE "suggestion"."author_id" = "member"."id"
|
jbe@9
|
1490 AND "suggestion"."created" > now() - "contingent"."time_frame"
|
jbe@9
|
1491 )
|
jbe@9
|
1492 ELSE NULL END AS "text_entry_count",
|
jbe@9
|
1493 "contingent"."text_entry_limit",
|
jbe@9
|
1494 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
|
jbe@9
|
1495 SELECT count(1) FROM "opening_draft"
|
jbe@9
|
1496 WHERE "opening_draft"."author_id" = "member"."id"
|
jbe@9
|
1497 AND "opening_draft"."created" > now() - "contingent"."time_frame"
|
jbe@9
|
1498 ) ELSE NULL END AS "initiative_count",
|
jbe@9
|
1499 "contingent"."initiative_limit"
|
jbe@9
|
1500 FROM "member" CROSS JOIN "contingent";
|
jbe@9
|
1501
|
jbe@9
|
1502 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
|
jbe@9
|
1503
|
jbe@9
|
1504 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
|
jbe@9
|
1505 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
|
jbe@9
|
1506
|
jbe@9
|
1507
|
jbe@9
|
1508 CREATE VIEW "member_contingent_left" AS
|
jbe@9
|
1509 SELECT
|
jbe@9
|
1510 "member_id",
|
jbe@9
|
1511 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
|
jbe@9
|
1512 max("initiative_limit" - "initiative_count") AS "initiatives_left"
|
jbe@9
|
1513 FROM "member_contingent" GROUP BY "member_id";
|
jbe@9
|
1514
|
jbe@9
|
1515 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
|
jbe@9
|
1516
|
jbe@9
|
1517
|
jbe@16
|
1518 CREATE TYPE "timeline_event" AS ENUM (
|
jbe@16
|
1519 'issue_created',
|
jbe@16
|
1520 'issue_canceled',
|
jbe@16
|
1521 'issue_accepted',
|
jbe@16
|
1522 'issue_half_frozen',
|
jbe@16
|
1523 'issue_finished_without_voting',
|
jbe@16
|
1524 'issue_voting_started',
|
jbe@16
|
1525 'issue_finished_after_voting',
|
jbe@16
|
1526 'initiative_created',
|
jbe@16
|
1527 'initiative_revoked',
|
jbe@16
|
1528 'draft_created',
|
jbe@16
|
1529 'suggestion_created');
|
jbe@16
|
1530
|
jbe@16
|
1531 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
|
jbe@16
|
1532
|
jbe@16
|
1533
|
jbe@16
|
1534 CREATE VIEW "timeline_issue" AS
|
jbe@16
|
1535 SELECT
|
jbe@16
|
1536 "created" AS "occurrence",
|
jbe@16
|
1537 'issue_created'::"timeline_event" AS "event",
|
jbe@16
|
1538 "id" AS "issue_id"
|
jbe@16
|
1539 FROM "issue"
|
jbe@16
|
1540 UNION ALL
|
jbe@16
|
1541 SELECT
|
jbe@16
|
1542 "closed" AS "occurrence",
|
jbe@16
|
1543 'issue_canceled'::"timeline_event" AS "event",
|
jbe@16
|
1544 "id" AS "issue_id"
|
jbe@16
|
1545 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
|
jbe@16
|
1546 UNION ALL
|
jbe@16
|
1547 SELECT
|
jbe@16
|
1548 "accepted" AS "occurrence",
|
jbe@16
|
1549 'issue_accepted'::"timeline_event" AS "event",
|
jbe@16
|
1550 "id" AS "issue_id"
|
jbe@16
|
1551 FROM "issue" WHERE "accepted" NOTNULL
|
jbe@16
|
1552 UNION ALL
|
jbe@16
|
1553 SELECT
|
jbe@16
|
1554 "half_frozen" AS "occurrence",
|
jbe@16
|
1555 'issue_half_frozen'::"timeline_event" AS "event",
|
jbe@16
|
1556 "id" AS "issue_id"
|
jbe@16
|
1557 FROM "issue" WHERE "half_frozen" NOTNULL
|
jbe@16
|
1558 UNION ALL
|
jbe@16
|
1559 SELECT
|
jbe@16
|
1560 "fully_frozen" AS "occurrence",
|
jbe@16
|
1561 'issue_voting_started'::"timeline_event" AS "event",
|
jbe@16
|
1562 "id" AS "issue_id"
|
jbe@16
|
1563 FROM "issue"
|
jbe@17
|
1564 WHERE "fully_frozen" NOTNULL
|
jbe@17
|
1565 AND ("closed" ISNULL OR "closed" != "fully_frozen")
|
jbe@16
|
1566 UNION ALL
|
jbe@16
|
1567 SELECT
|
jbe@16
|
1568 "closed" AS "occurrence",
|
jbe@16
|
1569 CASE WHEN "fully_frozen" = "closed" THEN
|
jbe@16
|
1570 'issue_finished_without_voting'::"timeline_event"
|
jbe@16
|
1571 ELSE
|
jbe@16
|
1572 'issue_finished_after_voting'::"timeline_event"
|
jbe@16
|
1573 END AS "event",
|
jbe@16
|
1574 "id" AS "issue_id"
|
jbe@16
|
1575 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
|
jbe@16
|
1576
|
jbe@16
|
1577 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
|
jbe@16
|
1578
|
jbe@16
|
1579
|
jbe@16
|
1580 CREATE VIEW "timeline_initiative" AS
|
jbe@16
|
1581 SELECT
|
jbe@16
|
1582 "created" AS "occurrence",
|
jbe@16
|
1583 'initiative_created'::"timeline_event" AS "event",
|
jbe@16
|
1584 "id" AS "initiative_id"
|
jbe@16
|
1585 FROM "initiative"
|
jbe@16
|
1586 UNION ALL
|
jbe@16
|
1587 SELECT
|
jbe@16
|
1588 "revoked" AS "occurrence",
|
jbe@16
|
1589 'initiative_revoked'::"timeline_event" AS "event",
|
jbe@16
|
1590 "id" AS "initiative_id"
|
jbe@16
|
1591 FROM "initiative" WHERE "revoked" NOTNULL;
|
jbe@16
|
1592
|
jbe@16
|
1593 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
|
jbe@16
|
1594
|
jbe@16
|
1595
|
jbe@16
|
1596 CREATE VIEW "timeline_draft" AS
|
jbe@16
|
1597 SELECT
|
jbe@16
|
1598 "created" AS "occurrence",
|
jbe@16
|
1599 'draft_created'::"timeline_event" AS "event",
|
jbe@16
|
1600 "id" AS "draft_id"
|
jbe@16
|
1601 FROM "draft";
|
jbe@16
|
1602
|
jbe@16
|
1603 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
|
jbe@16
|
1604
|
jbe@16
|
1605
|
jbe@16
|
1606 CREATE VIEW "timeline_suggestion" AS
|
jbe@16
|
1607 SELECT
|
jbe@16
|
1608 "created" AS "occurrence",
|
jbe@16
|
1609 'suggestion_created'::"timeline_event" AS "event",
|
jbe@16
|
1610 "id" AS "suggestion_id"
|
jbe@16
|
1611 FROM "suggestion";
|
jbe@16
|
1612
|
jbe@16
|
1613 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
|
jbe@16
|
1614
|
jbe@16
|
1615
|
jbe@16
|
1616 CREATE VIEW "timeline" AS
|
jbe@16
|
1617 SELECT
|
jbe@16
|
1618 "occurrence",
|
jbe@16
|
1619 "event",
|
jbe@16
|
1620 "issue_id",
|
jbe@16
|
1621 NULL AS "initiative_id",
|
jbe@16
|
1622 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
|
jbe@16
|
1623 NULL::INT8 AS "suggestion_id"
|
jbe@16
|
1624 FROM "timeline_issue"
|
jbe@16
|
1625 UNION ALL
|
jbe@16
|
1626 SELECT
|
jbe@16
|
1627 "occurrence",
|
jbe@16
|
1628 "event",
|
jbe@16
|
1629 NULL AS "issue_id",
|
jbe@16
|
1630 "initiative_id",
|
jbe@16
|
1631 NULL AS "draft_id",
|
jbe@16
|
1632 NULL AS "suggestion_id"
|
jbe@16
|
1633 FROM "timeline_initiative"
|
jbe@16
|
1634 UNION ALL
|
jbe@16
|
1635 SELECT
|
jbe@16
|
1636 "occurrence",
|
jbe@16
|
1637 "event",
|
jbe@16
|
1638 NULL AS "issue_id",
|
jbe@16
|
1639 NULL AS "initiative_id",
|
jbe@16
|
1640 "draft_id",
|
jbe@16
|
1641 NULL AS "suggestion_id"
|
jbe@16
|
1642 FROM "timeline_draft"
|
jbe@16
|
1643 UNION ALL
|
jbe@16
|
1644 SELECT
|
jbe@16
|
1645 "occurrence",
|
jbe@16
|
1646 "event",
|
jbe@16
|
1647 NULL AS "issue_id",
|
jbe@16
|
1648 NULL AS "initiative_id",
|
jbe@16
|
1649 NULL AS "draft_id",
|
jbe@16
|
1650 "suggestion_id"
|
jbe@16
|
1651 FROM "timeline_suggestion";
|
jbe@16
|
1652
|
jbe@16
|
1653 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
|
jbe@16
|
1654
|
jbe@16
|
1655
|
jbe@0
|
1656
|
jbe@5
|
1657 --------------------------------------------------
|
jbe@5
|
1658 -- Set returning function for delegation chains --
|
jbe@5
|
1659 --------------------------------------------------
|
jbe@5
|
1660
|
jbe@5
|
1661
|
jbe@5
|
1662 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
|
jbe@5
|
1663 ('first', 'intermediate', 'last', 'repetition');
|
jbe@5
|
1664
|
jbe@5
|
1665 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
|
jbe@5
|
1666
|
jbe@5
|
1667
|
jbe@5
|
1668 CREATE TYPE "delegation_chain_row" AS (
|
jbe@5
|
1669 "index" INT4,
|
jbe@5
|
1670 "member_id" INT4,
|
jbe@5
|
1671 "member_active" BOOLEAN,
|
jbe@5
|
1672 "participation" BOOLEAN,
|
jbe@5
|
1673 "overridden" BOOLEAN,
|
jbe@5
|
1674 "scope_in" "delegation_scope",
|
jbe@5
|
1675 "scope_out" "delegation_scope",
|
jbe@5
|
1676 "loop" "delegation_chain_loop_tag" );
|
jbe@5
|
1677
|
jbe@5
|
1678 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
|
jbe@5
|
1679
|
jbe@5
|
1680 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
|
jbe@5
|
1681 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
|
jbe@5
|
1682 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
|
jbe@5
|
1683 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
|
jbe@5
|
1684 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
|
jbe@5
|
1685 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
|
jbe@5
|
1686
|
jbe@5
|
1687
|
jbe@5
|
1688 CREATE FUNCTION "delegation_chain"
|
jbe@5
|
1689 ( "member_id_p" "member"."id"%TYPE,
|
jbe@5
|
1690 "area_id_p" "area"."id"%TYPE,
|
jbe@5
|
1691 "issue_id_p" "issue"."id"%TYPE,
|
jbe@5
|
1692 "simulate_trustee_id_p" "member"."id"%TYPE )
|
jbe@5
|
1693 RETURNS SETOF "delegation_chain_row"
|
jbe@5
|
1694 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@5
|
1695 DECLARE
|
jbe@5
|
1696 "issue_row" "issue"%ROWTYPE;
|
jbe@5
|
1697 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@5
|
1698 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@5
|
1699 "output_row" "delegation_chain_row";
|
jbe@5
|
1700 "output_rows" "delegation_chain_row"[];
|
jbe@5
|
1701 "delegation_row" "delegation"%ROWTYPE;
|
jbe@5
|
1702 "row_count" INT4;
|
jbe@5
|
1703 "i" INT4;
|
jbe@5
|
1704 "loop_v" BOOLEAN;
|
jbe@5
|
1705 BEGIN
|
jbe@5
|
1706 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@5
|
1707 "visited_member_ids" := '{}';
|
jbe@5
|
1708 "loop_member_id_v" := NULL;
|
jbe@5
|
1709 "output_rows" := '{}';
|
jbe@5
|
1710 "output_row"."index" := 0;
|
jbe@5
|
1711 "output_row"."member_id" := "member_id_p";
|
jbe@5
|
1712 "output_row"."member_active" := TRUE;
|
jbe@5
|
1713 "output_row"."participation" := FALSE;
|
jbe@5
|
1714 "output_row"."overridden" := FALSE;
|
jbe@5
|
1715 "output_row"."scope_out" := NULL;
|
jbe@5
|
1716 LOOP
|
jbe@5
|
1717 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@5
|
1718 "loop_member_id_v" := "output_row"."member_id";
|
jbe@5
|
1719 ELSE
|
jbe@5
|
1720 "visited_member_ids" :=
|
jbe@5
|
1721 "visited_member_ids" || "output_row"."member_id";
|
jbe@5
|
1722 END IF;
|
jbe@5
|
1723 IF "output_row"."participation" THEN
|
jbe@5
|
1724 "output_row"."overridden" := TRUE;
|
jbe@5
|
1725 END IF;
|
jbe@5
|
1726 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@5
|
1727 IF EXISTS (
|
jbe@5
|
1728 SELECT NULL FROM "member"
|
jbe@5
|
1729 WHERE "id" = "output_row"."member_id" AND "active"
|
jbe@5
|
1730 ) THEN
|
jbe@5
|
1731 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
jbe@5
|
1732 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@5
|
1733 WHERE "truster_id" = "output_row"."member_id"
|
jbe@10
|
1734 AND "scope" = 'global';
|
jbe@5
|
1735 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
|
jbe@5
|
1736 "output_row"."participation" := EXISTS (
|
jbe@5
|
1737 SELECT NULL FROM "membership"
|
jbe@5
|
1738 WHERE "area_id" = "area_id_p"
|
jbe@5
|
1739 AND "member_id" = "output_row"."member_id"
|
jbe@5
|
1740 );
|
jbe@5
|
1741 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@5
|
1742 WHERE "truster_id" = "output_row"."member_id"
|
jbe@10
|
1743 AND ("scope" = 'global' OR "area_id" = "area_id_p")
|
jbe@10
|
1744 ORDER BY "scope" DESC;
|
jbe@5
|
1745 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
|
jbe@5
|
1746 "output_row"."participation" := EXISTS (
|
jbe@5
|
1747 SELECT NULL FROM "interest"
|
jbe@5
|
1748 WHERE "issue_id" = "issue_id_p"
|
jbe@5
|
1749 AND "member_id" = "output_row"."member_id"
|
jbe@5
|
1750 );
|
jbe@5
|
1751 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@5
|
1752 WHERE "truster_id" = "output_row"."member_id"
|
jbe@10
|
1753 AND ("scope" = 'global' OR
|
jbe@10
|
1754 "area_id" = "issue_row"."area_id" OR
|
jbe@10
|
1755 "issue_id" = "issue_id_p"
|
jbe@10
|
1756 )
|
jbe@10
|
1757 ORDER BY "scope" DESC;
|
jbe@5
|
1758 ELSE
|
jbe@5
|
1759 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
|
jbe@5
|
1760 END IF;
|
jbe@5
|
1761 ELSE
|
jbe@5
|
1762 "output_row"."member_active" := FALSE;
|
jbe@5
|
1763 "output_row"."participation" := FALSE;
|
jbe@5
|
1764 "output_row"."scope_out" := NULL;
|
jbe@5
|
1765 "delegation_row" := ROW(NULL);
|
jbe@5
|
1766 END IF;
|
jbe@5
|
1767 IF
|
jbe@5
|
1768 "output_row"."member_id" = "member_id_p" AND
|
jbe@5
|
1769 "simulate_trustee_id_p" NOTNULL
|
jbe@5
|
1770 THEN
|
jbe@5
|
1771 "output_row"."scope_out" := CASE
|
jbe@5
|
1772 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
|
jbe@5
|
1773 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
|
jbe@5
|
1774 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
|
jbe@5
|
1775 END;
|
jbe@5
|
1776 "output_rows" := "output_rows" || "output_row";
|
jbe@5
|
1777 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@5
|
1778 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@10
|
1779 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@5
|
1780 "output_rows" := "output_rows" || "output_row";
|
jbe@5
|
1781 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@5
|
1782 ELSE
|
jbe@5
|
1783 "output_row"."scope_out" := NULL;
|
jbe@5
|
1784 "output_rows" := "output_rows" || "output_row";
|
jbe@5
|
1785 EXIT;
|
jbe@5
|
1786 END IF;
|
jbe@5
|
1787 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@5
|
1788 "output_row"."index" := "output_row"."index" + 1;
|
jbe@5
|
1789 END LOOP;
|
jbe@5
|
1790 "row_count" := array_upper("output_rows", 1);
|
jbe@5
|
1791 "i" := 1;
|
jbe@5
|
1792 "loop_v" := FALSE;
|
jbe@5
|
1793 LOOP
|
jbe@5
|
1794 "output_row" := "output_rows"["i"];
|
jbe@5
|
1795 EXIT WHEN "output_row"."member_id" ISNULL;
|
jbe@5
|
1796 IF "loop_v" THEN
|
jbe@5
|
1797 IF "i" + 1 = "row_count" THEN
|
jbe@5
|
1798 "output_row"."loop" := 'last';
|
jbe@5
|
1799 ELSIF "i" = "row_count" THEN
|
jbe@5
|
1800 "output_row"."loop" := 'repetition';
|
jbe@5
|
1801 ELSE
|
jbe@5
|
1802 "output_row"."loop" := 'intermediate';
|
jbe@5
|
1803 END IF;
|
jbe@5
|
1804 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@5
|
1805 "output_row"."loop" := 'first';
|
jbe@5
|
1806 "loop_v" := TRUE;
|
jbe@5
|
1807 END IF;
|
jbe@5
|
1808 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
jbe@5
|
1809 "output_row"."participation" := NULL;
|
jbe@5
|
1810 END IF;
|
jbe@5
|
1811 RETURN NEXT "output_row";
|
jbe@5
|
1812 "i" := "i" + 1;
|
jbe@5
|
1813 END LOOP;
|
jbe@5
|
1814 RETURN;
|
jbe@5
|
1815 END;
|
jbe@5
|
1816 $$;
|
jbe@5
|
1817
|
jbe@5
|
1818 COMMENT ON FUNCTION "delegation_chain"
|
jbe@5
|
1819 ( "member"."id"%TYPE,
|
jbe@5
|
1820 "area"."id"%TYPE,
|
jbe@5
|
1821 "issue"."id"%TYPE,
|
jbe@5
|
1822 "member"."id"%TYPE )
|
jbe@5
|
1823 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
|
jbe@5
|
1824
|
jbe@5
|
1825 CREATE FUNCTION "delegation_chain"
|
jbe@5
|
1826 ( "member_id_p" "member"."id"%TYPE,
|
jbe@5
|
1827 "area_id_p" "area"."id"%TYPE,
|
jbe@5
|
1828 "issue_id_p" "issue"."id"%TYPE )
|
jbe@5
|
1829 RETURNS SETOF "delegation_chain_row"
|
jbe@5
|
1830 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@5
|
1831 DECLARE
|
jbe@5
|
1832 "result_row" "delegation_chain_row";
|
jbe@5
|
1833 BEGIN
|
jbe@5
|
1834 FOR "result_row" IN
|
jbe@5
|
1835 SELECT * FROM "delegation_chain"(
|
jbe@5
|
1836 "member_id_p", "area_id_p", "issue_id_p", NULL
|
jbe@5
|
1837 )
|
jbe@5
|
1838 LOOP
|
jbe@5
|
1839 RETURN NEXT "result_row";
|
jbe@5
|
1840 END LOOP;
|
jbe@5
|
1841 RETURN;
|
jbe@5
|
1842 END;
|
jbe@5
|
1843 $$;
|
jbe@5
|
1844
|
jbe@5
|
1845 COMMENT ON FUNCTION "delegation_chain"
|
jbe@5
|
1846 ( "member"."id"%TYPE,
|
jbe@5
|
1847 "area"."id"%TYPE,
|
jbe@5
|
1848 "issue"."id"%TYPE )
|
jbe@5
|
1849 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
|
jbe@5
|
1850
|
jbe@5
|
1851
|
jbe@5
|
1852
|
jbe@0
|
1853 ------------------------------
|
jbe@0
|
1854 -- Comparison by vote count --
|
jbe@0
|
1855 ------------------------------
|
jbe@0
|
1856
|
jbe@0
|
1857 CREATE FUNCTION "vote_ratio"
|
jbe@0
|
1858 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
|
jbe@0
|
1859 "negative_votes_p" "initiative"."negative_votes"%TYPE )
|
jbe@0
|
1860 RETURNS FLOAT8
|
jbe@0
|
1861 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@0
|
1862 BEGIN
|
jbe@30
|
1863 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
|
jbe@30
|
1864 RETURN
|
jbe@30
|
1865 "positive_votes_p"::FLOAT8 /
|
jbe@30
|
1866 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
|
jbe@30
|
1867 ELSIF "positive_votes_p" > 0 THEN
|
jbe@30
|
1868 RETURN "positive_votes_p";
|
jbe@30
|
1869 ELSIF "negative_votes_p" > 0 THEN
|
jbe@30
|
1870 RETURN 1 - "negative_votes_p";
|
jbe@0
|
1871 ELSE
|
jbe@0
|
1872 RETURN 0.5;
|
jbe@0
|
1873 END IF;
|
jbe@0
|
1874 END;
|
jbe@0
|
1875 $$;
|
jbe@0
|
1876
|
jbe@0
|
1877 COMMENT ON FUNCTION "vote_ratio"
|
jbe@0
|
1878 ( "initiative"."positive_votes"%TYPE,
|
jbe@0
|
1879 "initiative"."negative_votes"%TYPE )
|
jbe@30
|
1880 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
|
jbe@0
|
1881
|
jbe@0
|
1882
|
jbe@0
|
1883
|
jbe@0
|
1884 ------------------------------------------------
|
jbe@0
|
1885 -- Locking for snapshots and voting procedure --
|
jbe@0
|
1886 ------------------------------------------------
|
jbe@0
|
1887
|
jbe@0
|
1888 CREATE FUNCTION "global_lock"() RETURNS VOID
|
jbe@0
|
1889 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1890 BEGIN
|
jbe@0
|
1891 -- NOTE: PostgreSQL allows reading, while tables are locked in
|
jbe@0
|
1892 -- exclusive move. Transactions should be kept short anyway!
|
jbe@0
|
1893 LOCK TABLE "member" IN EXCLUSIVE MODE;
|
jbe@4
|
1894 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
jbe@4
|
1895 LOCK TABLE "membership" IN EXCLUSIVE MODE;
|
jbe@4
|
1896 -- NOTE: "member", "area" and "membership" are locked first to
|
jbe@4
|
1897 -- prevent deadlocks in combination with "calculate_member_counts"()
|
jbe@0
|
1898 LOCK TABLE "policy" IN EXCLUSIVE MODE;
|
jbe@0
|
1899 LOCK TABLE "issue" IN EXCLUSIVE MODE;
|
jbe@0
|
1900 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
|
jbe@0
|
1901 LOCK TABLE "draft" IN EXCLUSIVE MODE;
|
jbe@0
|
1902 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
|
jbe@0
|
1903 LOCK TABLE "interest" IN EXCLUSIVE MODE;
|
jbe@0
|
1904 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
|
jbe@0
|
1905 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
|
jbe@0
|
1906 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
|
jbe@0
|
1907 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
|
jbe@0
|
1908 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@0
|
1909 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
jbe@0
|
1910 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@0
|
1911 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
jbe@0
|
1912 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
jbe@0
|
1913 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
|
jbe@0
|
1914 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
|
jbe@0
|
1915 LOCK TABLE "vote" IN EXCLUSIVE MODE;
|
jbe@0
|
1916 RETURN;
|
jbe@0
|
1917 END;
|
jbe@0
|
1918 $$;
|
jbe@0
|
1919
|
jbe@0
|
1920 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
|
jbe@0
|
1921
|
jbe@0
|
1922
|
jbe@0
|
1923
|
jbe@4
|
1924 -------------------------------
|
jbe@4
|
1925 -- Materialize member counts --
|
jbe@4
|
1926 -------------------------------
|
jbe@4
|
1927
|
jbe@4
|
1928 CREATE FUNCTION "calculate_member_counts"()
|
jbe@4
|
1929 RETURNS VOID
|
jbe@4
|
1930 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@4
|
1931 BEGIN
|
jbe@4
|
1932 LOCK TABLE "member" IN EXCLUSIVE MODE;
|
jbe@4
|
1933 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
jbe@4
|
1934 LOCK TABLE "membership" IN EXCLUSIVE MODE;
|
jbe@4
|
1935 DELETE FROM "member_count";
|
jbe@5
|
1936 INSERT INTO "member_count" ("total_count")
|
jbe@5
|
1937 SELECT "total_count" FROM "member_count_view";
|
jbe@5
|
1938 UPDATE "area" SET
|
jbe@5
|
1939 "direct_member_count" = "view"."direct_member_count",
|
jbe@5
|
1940 "member_weight" = "view"."member_weight",
|
jbe@5
|
1941 "autoreject_weight" = "view"."autoreject_weight"
|
jbe@5
|
1942 FROM "area_member_count" AS "view"
|
jbe@5
|
1943 WHERE "view"."area_id" = "area"."id";
|
jbe@4
|
1944 RETURN;
|
jbe@4
|
1945 END;
|
jbe@4
|
1946 $$;
|
jbe@4
|
1947
|
jbe@4
|
1948 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
|
jbe@4
|
1949
|
jbe@4
|
1950
|
jbe@4
|
1951
|
jbe@0
|
1952 ------------------------------
|
jbe@0
|
1953 -- Calculation of snapshots --
|
jbe@0
|
1954 ------------------------------
|
jbe@0
|
1955
|
jbe@0
|
1956 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
jbe@0
|
1957 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@0
|
1958 "member_id_p" "member"."id"%TYPE,
|
jbe@0
|
1959 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
jbe@0
|
1960 RETURNS "direct_population_snapshot"."weight"%TYPE
|
jbe@0
|
1961 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
1962 DECLARE
|
jbe@0
|
1963 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@0
|
1964 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
|
jbe@0
|
1965 "weight_v" INT4;
|
jbe@8
|
1966 "sub_weight_v" INT4;
|
jbe@0
|
1967 BEGIN
|
jbe@0
|
1968 "weight_v" := 0;
|
jbe@0
|
1969 FOR "issue_delegation_row" IN
|
jbe@0
|
1970 SELECT * FROM "issue_delegation"
|
jbe@0
|
1971 WHERE "trustee_id" = "member_id_p"
|
jbe@0
|
1972 AND "issue_id" = "issue_id_p"
|
jbe@0
|
1973 LOOP
|
jbe@0
|
1974 IF NOT EXISTS (
|
jbe@0
|
1975 SELECT NULL FROM "direct_population_snapshot"
|
jbe@0
|
1976 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
1977 AND "event" = 'periodic'
|
jbe@0
|
1978 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
1979 ) AND NOT EXISTS (
|
jbe@0
|
1980 SELECT NULL FROM "delegating_population_snapshot"
|
jbe@0
|
1981 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
1982 AND "event" = 'periodic'
|
jbe@0
|
1983 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
1984 ) THEN
|
jbe@0
|
1985 "delegate_member_ids_v" :=
|
jbe@0
|
1986 "member_id_p" || "delegate_member_ids_p";
|
jbe@10
|
1987 INSERT INTO "delegating_population_snapshot" (
|
jbe@10
|
1988 "issue_id",
|
jbe@10
|
1989 "event",
|
jbe@10
|
1990 "member_id",
|
jbe@10
|
1991 "scope",
|
jbe@10
|
1992 "delegate_member_ids"
|
jbe@10
|
1993 ) VALUES (
|
jbe@0
|
1994 "issue_id_p",
|
jbe@0
|
1995 'periodic',
|
jbe@0
|
1996 "issue_delegation_row"."truster_id",
|
jbe@10
|
1997 "issue_delegation_row"."scope",
|
jbe@0
|
1998 "delegate_member_ids_v"
|
jbe@0
|
1999 );
|
jbe@8
|
2000 "sub_weight_v" := 1 +
|
jbe@0
|
2001 "weight_of_added_delegations_for_population_snapshot"(
|
jbe@0
|
2002 "issue_id_p",
|
jbe@0
|
2003 "issue_delegation_row"."truster_id",
|
jbe@0
|
2004 "delegate_member_ids_v"
|
jbe@0
|
2005 );
|
jbe@8
|
2006 UPDATE "delegating_population_snapshot"
|
jbe@8
|
2007 SET "weight" = "sub_weight_v"
|
jbe@8
|
2008 WHERE "issue_id" = "issue_id_p"
|
jbe@8
|
2009 AND "event" = 'periodic'
|
jbe@8
|
2010 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@8
|
2011 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@0
|
2012 END IF;
|
jbe@0
|
2013 END LOOP;
|
jbe@0
|
2014 RETURN "weight_v";
|
jbe@0
|
2015 END;
|
jbe@0
|
2016 $$;
|
jbe@0
|
2017
|
jbe@0
|
2018 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
jbe@0
|
2019 ( "issue"."id"%TYPE,
|
jbe@0
|
2020 "member"."id"%TYPE,
|
jbe@0
|
2021 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
jbe@0
|
2022 IS 'Helper function for "create_population_snapshot" function';
|
jbe@0
|
2023
|
jbe@0
|
2024
|
jbe@0
|
2025 CREATE FUNCTION "create_population_snapshot"
|
jbe@0
|
2026 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2027 RETURNS VOID
|
jbe@0
|
2028 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2029 DECLARE
|
jbe@0
|
2030 "member_id_v" "member"."id"%TYPE;
|
jbe@0
|
2031 BEGIN
|
jbe@0
|
2032 DELETE FROM "direct_population_snapshot"
|
jbe@0
|
2033 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2034 AND "event" = 'periodic';
|
jbe@0
|
2035 DELETE FROM "delegating_population_snapshot"
|
jbe@0
|
2036 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2037 AND "event" = 'periodic';
|
jbe@0
|
2038 INSERT INTO "direct_population_snapshot"
|
jbe@0
|
2039 ("issue_id", "event", "member_id", "interest_exists")
|
jbe@0
|
2040 SELECT DISTINCT ON ("issue_id", "member_id")
|
jbe@0
|
2041 "issue_id_p" AS "issue_id",
|
jbe@0
|
2042 'periodic' AS "event",
|
jbe@0
|
2043 "subquery"."member_id",
|
jbe@0
|
2044 "subquery"."interest_exists"
|
jbe@0
|
2045 FROM (
|
jbe@0
|
2046 SELECT
|
jbe@0
|
2047 "member"."id" AS "member_id",
|
jbe@0
|
2048 FALSE AS "interest_exists"
|
jbe@0
|
2049 FROM "issue"
|
jbe@0
|
2050 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@0
|
2051 JOIN "membership" ON "area"."id" = "membership"."area_id"
|
jbe@0
|
2052 JOIN "member" ON "membership"."member_id" = "member"."id"
|
jbe@0
|
2053 WHERE "issue"."id" = "issue_id_p"
|
jbe@0
|
2054 AND "member"."active"
|
jbe@0
|
2055 UNION
|
jbe@0
|
2056 SELECT
|
jbe@0
|
2057 "member"."id" AS "member_id",
|
jbe@0
|
2058 TRUE AS "interest_exists"
|
jbe@0
|
2059 FROM "interest" JOIN "member"
|
jbe@0
|
2060 ON "interest"."member_id" = "member"."id"
|
jbe@0
|
2061 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@0
|
2062 AND "member"."active"
|
jbe@0
|
2063 ) AS "subquery"
|
jbe@0
|
2064 ORDER BY
|
jbe@0
|
2065 "issue_id_p",
|
jbe@0
|
2066 "subquery"."member_id",
|
jbe@0
|
2067 "subquery"."interest_exists" DESC;
|
jbe@0
|
2068 FOR "member_id_v" IN
|
jbe@0
|
2069 SELECT "member_id" FROM "direct_population_snapshot"
|
jbe@0
|
2070 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2071 AND "event" = 'periodic'
|
jbe@0
|
2072 LOOP
|
jbe@0
|
2073 UPDATE "direct_population_snapshot" SET
|
jbe@0
|
2074 "weight" = 1 +
|
jbe@0
|
2075 "weight_of_added_delegations_for_population_snapshot"(
|
jbe@0
|
2076 "issue_id_p",
|
jbe@0
|
2077 "member_id_v",
|
jbe@0
|
2078 '{}'
|
jbe@0
|
2079 )
|
jbe@0
|
2080 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2081 AND "event" = 'periodic'
|
jbe@0
|
2082 AND "member_id" = "member_id_v";
|
jbe@0
|
2083 END LOOP;
|
jbe@0
|
2084 RETURN;
|
jbe@0
|
2085 END;
|
jbe@0
|
2086 $$;
|
jbe@0
|
2087
|
jbe@0
|
2088 COMMENT ON FUNCTION "create_population_snapshot"
|
jbe@0
|
2089 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2090 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
|
jbe@0
|
2091
|
jbe@0
|
2092
|
jbe@0
|
2093 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
jbe@0
|
2094 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@0
|
2095 "member_id_p" "member"."id"%TYPE,
|
jbe@0
|
2096 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@0
|
2097 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
jbe@0
|
2098 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2099 DECLARE
|
jbe@0
|
2100 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@0
|
2101 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
jbe@0
|
2102 "weight_v" INT4;
|
jbe@8
|
2103 "sub_weight_v" INT4;
|
jbe@0
|
2104 BEGIN
|
jbe@0
|
2105 "weight_v" := 0;
|
jbe@0
|
2106 FOR "issue_delegation_row" IN
|
jbe@0
|
2107 SELECT * FROM "issue_delegation"
|
jbe@0
|
2108 WHERE "trustee_id" = "member_id_p"
|
jbe@0
|
2109 AND "issue_id" = "issue_id_p"
|
jbe@0
|
2110 LOOP
|
jbe@0
|
2111 IF NOT EXISTS (
|
jbe@0
|
2112 SELECT NULL FROM "direct_interest_snapshot"
|
jbe@0
|
2113 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2114 AND "event" = 'periodic'
|
jbe@0
|
2115 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
2116 ) AND NOT EXISTS (
|
jbe@0
|
2117 SELECT NULL FROM "delegating_interest_snapshot"
|
jbe@0
|
2118 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2119 AND "event" = 'periodic'
|
jbe@0
|
2120 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
2121 ) THEN
|
jbe@0
|
2122 "delegate_member_ids_v" :=
|
jbe@0
|
2123 "member_id_p" || "delegate_member_ids_p";
|
jbe@10
|
2124 INSERT INTO "delegating_interest_snapshot" (
|
jbe@10
|
2125 "issue_id",
|
jbe@10
|
2126 "event",
|
jbe@10
|
2127 "member_id",
|
jbe@10
|
2128 "scope",
|
jbe@10
|
2129 "delegate_member_ids"
|
jbe@10
|
2130 ) VALUES (
|
jbe@0
|
2131 "issue_id_p",
|
jbe@0
|
2132 'periodic',
|
jbe@0
|
2133 "issue_delegation_row"."truster_id",
|
jbe@10
|
2134 "issue_delegation_row"."scope",
|
jbe@0
|
2135 "delegate_member_ids_v"
|
jbe@0
|
2136 );
|
jbe@8
|
2137 "sub_weight_v" := 1 +
|
jbe@0
|
2138 "weight_of_added_delegations_for_interest_snapshot"(
|
jbe@0
|
2139 "issue_id_p",
|
jbe@0
|
2140 "issue_delegation_row"."truster_id",
|
jbe@0
|
2141 "delegate_member_ids_v"
|
jbe@0
|
2142 );
|
jbe@8
|
2143 UPDATE "delegating_interest_snapshot"
|
jbe@8
|
2144 SET "weight" = "sub_weight_v"
|
jbe@8
|
2145 WHERE "issue_id" = "issue_id_p"
|
jbe@8
|
2146 AND "event" = 'periodic'
|
jbe@8
|
2147 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@8
|
2148 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@0
|
2149 END IF;
|
jbe@0
|
2150 END LOOP;
|
jbe@0
|
2151 RETURN "weight_v";
|
jbe@0
|
2152 END;
|
jbe@0
|
2153 $$;
|
jbe@0
|
2154
|
jbe@0
|
2155 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
jbe@0
|
2156 ( "issue"."id"%TYPE,
|
jbe@0
|
2157 "member"."id"%TYPE,
|
jbe@0
|
2158 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@0
|
2159 IS 'Helper function for "create_interest_snapshot" function';
|
jbe@0
|
2160
|
jbe@0
|
2161
|
jbe@0
|
2162 CREATE FUNCTION "create_interest_snapshot"
|
jbe@0
|
2163 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2164 RETURNS VOID
|
jbe@0
|
2165 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2166 DECLARE
|
jbe@0
|
2167 "member_id_v" "member"."id"%TYPE;
|
jbe@0
|
2168 BEGIN
|
jbe@0
|
2169 DELETE FROM "direct_interest_snapshot"
|
jbe@0
|
2170 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2171 AND "event" = 'periodic';
|
jbe@0
|
2172 DELETE FROM "delegating_interest_snapshot"
|
jbe@0
|
2173 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2174 AND "event" = 'periodic';
|
jbe@0
|
2175 DELETE FROM "direct_supporter_snapshot"
|
jbe@0
|
2176 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2177 AND "event" = 'periodic';
|
jbe@0
|
2178 INSERT INTO "direct_interest_snapshot"
|
jbe@0
|
2179 ("issue_id", "event", "member_id", "voting_requested")
|
jbe@0
|
2180 SELECT
|
jbe@0
|
2181 "issue_id_p" AS "issue_id",
|
jbe@0
|
2182 'periodic' AS "event",
|
jbe@0
|
2183 "member"."id" AS "member_id",
|
jbe@0
|
2184 "interest"."voting_requested"
|
jbe@0
|
2185 FROM "interest" JOIN "member"
|
jbe@0
|
2186 ON "interest"."member_id" = "member"."id"
|
jbe@0
|
2187 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@0
|
2188 AND "member"."active";
|
jbe@0
|
2189 FOR "member_id_v" IN
|
jbe@0
|
2190 SELECT "member_id" FROM "direct_interest_snapshot"
|
jbe@0
|
2191 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2192 AND "event" = 'periodic'
|
jbe@0
|
2193 LOOP
|
jbe@0
|
2194 UPDATE "direct_interest_snapshot" SET
|
jbe@0
|
2195 "weight" = 1 +
|
jbe@0
|
2196 "weight_of_added_delegations_for_interest_snapshot"(
|
jbe@0
|
2197 "issue_id_p",
|
jbe@0
|
2198 "member_id_v",
|
jbe@0
|
2199 '{}'
|
jbe@0
|
2200 )
|
jbe@0
|
2201 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2202 AND "event" = 'periodic'
|
jbe@0
|
2203 AND "member_id" = "member_id_v";
|
jbe@0
|
2204 END LOOP;
|
jbe@0
|
2205 INSERT INTO "direct_supporter_snapshot"
|
jbe@0
|
2206 ( "issue_id", "initiative_id", "event", "member_id",
|
jbe@0
|
2207 "informed", "satisfied" )
|
jbe@0
|
2208 SELECT
|
jbe@0
|
2209 "issue_id_p" AS "issue_id",
|
jbe@0
|
2210 "initiative"."id" AS "initiative_id",
|
jbe@0
|
2211 'periodic' AS "event",
|
jbe@0
|
2212 "member"."id" AS "member_id",
|
jbe@0
|
2213 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
jbe@0
|
2214 NOT EXISTS (
|
jbe@0
|
2215 SELECT NULL FROM "critical_opinion"
|
jbe@0
|
2216 WHERE "initiative_id" = "initiative"."id"
|
jbe@0
|
2217 AND "member_id" = "member"."id"
|
jbe@0
|
2218 ) AS "satisfied"
|
jbe@0
|
2219 FROM "supporter"
|
jbe@0
|
2220 JOIN "member"
|
jbe@0
|
2221 ON "supporter"."member_id" = "member"."id"
|
jbe@0
|
2222 JOIN "initiative"
|
jbe@0
|
2223 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@0
|
2224 JOIN "current_draft"
|
jbe@0
|
2225 ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@0
|
2226 JOIN "direct_interest_snapshot"
|
jbe@0
|
2227 ON "member"."id" = "direct_interest_snapshot"."member_id"
|
jbe@0
|
2228 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
jbe@3
|
2229 AND "event" = 'periodic'
|
jbe@0
|
2230 WHERE "member"."active"
|
jbe@0
|
2231 AND "initiative"."issue_id" = "issue_id_p";
|
jbe@0
|
2232 RETURN;
|
jbe@0
|
2233 END;
|
jbe@0
|
2234 $$;
|
jbe@0
|
2235
|
jbe@0
|
2236 COMMENT ON FUNCTION "create_interest_snapshot"
|
jbe@0
|
2237 ( "issue"."id"%TYPE )
|
jbe@0
|
2238 IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
|
jbe@0
|
2239
|
jbe@0
|
2240
|
jbe@0
|
2241 CREATE FUNCTION "create_snapshot"
|
jbe@0
|
2242 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2243 RETURNS VOID
|
jbe@0
|
2244 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2245 DECLARE
|
jbe@0
|
2246 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@0
|
2247 "suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@0
|
2248 BEGIN
|
jbe@0
|
2249 PERFORM "global_lock"();
|
jbe@0
|
2250 PERFORM "create_population_snapshot"("issue_id_p");
|
jbe@0
|
2251 PERFORM "create_interest_snapshot"("issue_id_p");
|
jbe@0
|
2252 UPDATE "issue" SET
|
jbe@8
|
2253 "snapshot" = now(),
|
jbe@8
|
2254 "latest_snapshot_event" = 'periodic',
|
jbe@0
|
2255 "population" = (
|
jbe@0
|
2256 SELECT coalesce(sum("weight"), 0)
|
jbe@0
|
2257 FROM "direct_population_snapshot"
|
jbe@0
|
2258 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2259 AND "event" = 'periodic'
|
jbe@0
|
2260 ),
|
jbe@8
|
2261 "vote_now" = (
|
jbe@0
|
2262 SELECT coalesce(sum("weight"), 0)
|
jbe@0
|
2263 FROM "direct_interest_snapshot"
|
jbe@0
|
2264 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2265 AND "event" = 'periodic'
|
jbe@0
|
2266 AND "voting_requested" = TRUE
|
jbe@0
|
2267 ),
|
jbe@0
|
2268 "vote_later" = (
|
jbe@0
|
2269 SELECT coalesce(sum("weight"), 0)
|
jbe@0
|
2270 FROM "direct_interest_snapshot"
|
jbe@0
|
2271 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2272 AND "event" = 'periodic'
|
jbe@0
|
2273 AND "voting_requested" = FALSE
|
jbe@0
|
2274 )
|
jbe@0
|
2275 WHERE "id" = "issue_id_p";
|
jbe@0
|
2276 FOR "initiative_id_v" IN
|
jbe@0
|
2277 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2278 LOOP
|
jbe@0
|
2279 UPDATE "initiative" SET
|
jbe@0
|
2280 "supporter_count" = (
|
jbe@0
|
2281 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@0
|
2282 FROM "direct_interest_snapshot" AS "di"
|
jbe@0
|
2283 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@0
|
2284 ON "di"."member_id" = "ds"."member_id"
|
jbe@0
|
2285 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@0
|
2286 AND "di"."event" = 'periodic'
|
jbe@0
|
2287 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@0
|
2288 AND "ds"."event" = 'periodic'
|
jbe@0
|
2289 ),
|
jbe@0
|
2290 "informed_supporter_count" = (
|
jbe@0
|
2291 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@0
|
2292 FROM "direct_interest_snapshot" AS "di"
|
jbe@0
|
2293 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@0
|
2294 ON "di"."member_id" = "ds"."member_id"
|
jbe@0
|
2295 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@0
|
2296 AND "di"."event" = 'periodic'
|
jbe@0
|
2297 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@0
|
2298 AND "ds"."event" = 'periodic'
|
jbe@0
|
2299 AND "ds"."informed"
|
jbe@0
|
2300 ),
|
jbe@0
|
2301 "satisfied_supporter_count" = (
|
jbe@0
|
2302 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@0
|
2303 FROM "direct_interest_snapshot" AS "di"
|
jbe@0
|
2304 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@0
|
2305 ON "di"."member_id" = "ds"."member_id"
|
jbe@0
|
2306 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@0
|
2307 AND "di"."event" = 'periodic'
|
jbe@0
|
2308 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@0
|
2309 AND "ds"."event" = 'periodic'
|
jbe@0
|
2310 AND "ds"."satisfied"
|
jbe@0
|
2311 ),
|
jbe@0
|
2312 "satisfied_informed_supporter_count" = (
|
jbe@0
|
2313 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@0
|
2314 FROM "direct_interest_snapshot" AS "di"
|
jbe@0
|
2315 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@0
|
2316 ON "di"."member_id" = "ds"."member_id"
|
jbe@0
|
2317 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@0
|
2318 AND "di"."event" = 'periodic'
|
jbe@0
|
2319 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@0
|
2320 AND "ds"."event" = 'periodic'
|
jbe@0
|
2321 AND "ds"."informed"
|
jbe@0
|
2322 AND "ds"."satisfied"
|
jbe@0
|
2323 )
|
jbe@0
|
2324 WHERE "id" = "initiative_id_v";
|
jbe@0
|
2325 FOR "suggestion_id_v" IN
|
jbe@0
|
2326 SELECT "id" FROM "suggestion"
|
jbe@0
|
2327 WHERE "initiative_id" = "initiative_id_v"
|
jbe@0
|
2328 LOOP
|
jbe@0
|
2329 UPDATE "suggestion" SET
|
jbe@0
|
2330 "minus2_unfulfilled_count" = (
|
jbe@0
|
2331 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2332 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2333 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2334 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2335 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2336 AND "opinion"."degree" = -2
|
jbe@0
|
2337 AND "opinion"."fulfilled" = FALSE
|
jbe@0
|
2338 ),
|
jbe@0
|
2339 "minus2_fulfilled_count" = (
|
jbe@0
|
2340 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2341 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2342 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2343 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2344 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2345 AND "opinion"."degree" = -2
|
jbe@0
|
2346 AND "opinion"."fulfilled" = TRUE
|
jbe@0
|
2347 ),
|
jbe@0
|
2348 "minus1_unfulfilled_count" = (
|
jbe@0
|
2349 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2350 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2351 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2352 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2353 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2354 AND "opinion"."degree" = -1
|
jbe@0
|
2355 AND "opinion"."fulfilled" = FALSE
|
jbe@0
|
2356 ),
|
jbe@0
|
2357 "minus1_fulfilled_count" = (
|
jbe@0
|
2358 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2359 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2360 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2361 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2362 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2363 AND "opinion"."degree" = -1
|
jbe@0
|
2364 AND "opinion"."fulfilled" = TRUE
|
jbe@0
|
2365 ),
|
jbe@0
|
2366 "plus1_unfulfilled_count" = (
|
jbe@0
|
2367 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2368 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2369 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2370 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2371 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2372 AND "opinion"."degree" = 1
|
jbe@0
|
2373 AND "opinion"."fulfilled" = FALSE
|
jbe@0
|
2374 ),
|
jbe@0
|
2375 "plus1_fulfilled_count" = (
|
jbe@0
|
2376 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2377 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2378 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2379 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2380 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2381 AND "opinion"."degree" = 1
|
jbe@0
|
2382 AND "opinion"."fulfilled" = TRUE
|
jbe@0
|
2383 ),
|
jbe@0
|
2384 "plus2_unfulfilled_count" = (
|
jbe@0
|
2385 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2386 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2387 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2388 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2389 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2390 AND "opinion"."degree" = 2
|
jbe@0
|
2391 AND "opinion"."fulfilled" = FALSE
|
jbe@0
|
2392 ),
|
jbe@0
|
2393 "plus2_fulfilled_count" = (
|
jbe@0
|
2394 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@0
|
2395 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@0
|
2396 ON "opinion"."member_id" = "snapshot"."member_id"
|
jbe@1
|
2397 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@0
|
2398 AND "snapshot"."issue_id" = "issue_id_p"
|
jbe@0
|
2399 AND "opinion"."degree" = 2
|
jbe@0
|
2400 AND "opinion"."fulfilled" = TRUE
|
jbe@0
|
2401 )
|
jbe@0
|
2402 WHERE "suggestion"."id" = "suggestion_id_v";
|
jbe@0
|
2403 END LOOP;
|
jbe@0
|
2404 END LOOP;
|
jbe@0
|
2405 RETURN;
|
jbe@0
|
2406 END;
|
jbe@0
|
2407 $$;
|
jbe@0
|
2408
|
jbe@0
|
2409 COMMENT ON FUNCTION "create_snapshot"
|
jbe@0
|
2410 ( "issue"."id"%TYPE )
|
jbe@0
|
2411 IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
|
jbe@0
|
2412
|
jbe@0
|
2413
|
jbe@0
|
2414 CREATE FUNCTION "set_snapshot_event"
|
jbe@0
|
2415 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@0
|
2416 "event_p" "snapshot_event" )
|
jbe@0
|
2417 RETURNS VOID
|
jbe@0
|
2418 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@21
|
2419 DECLARE
|
jbe@21
|
2420 "event_v" "issue"."latest_snapshot_event"%TYPE;
|
jbe@0
|
2421 BEGIN
|
jbe@21
|
2422 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
|
jbe@21
|
2423 WHERE "id" = "issue_id_p" FOR UPDATE;
|
jbe@8
|
2424 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
|
jbe@8
|
2425 WHERE "id" = "issue_id_p";
|
jbe@3
|
2426 UPDATE "direct_population_snapshot" SET "event" = "event_p"
|
jbe@21
|
2427 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
jbe@3
|
2428 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
|
jbe@21
|
2429 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
jbe@3
|
2430 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
|
jbe@21
|
2431 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
jbe@3
|
2432 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
|
jbe@21
|
2433 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
jbe@3
|
2434 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
|
jbe@21
|
2435 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
jbe@0
|
2436 RETURN;
|
jbe@0
|
2437 END;
|
jbe@0
|
2438 $$;
|
jbe@0
|
2439
|
jbe@0
|
2440 COMMENT ON FUNCTION "set_snapshot_event"
|
jbe@0
|
2441 ( "issue"."id"%TYPE,
|
jbe@0
|
2442 "snapshot_event" )
|
jbe@0
|
2443 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
|
jbe@0
|
2444
|
jbe@0
|
2445
|
jbe@0
|
2446
|
jbe@0
|
2447 ---------------------
|
jbe@0
|
2448 -- Freezing issues --
|
jbe@0
|
2449 ---------------------
|
jbe@0
|
2450
|
jbe@0
|
2451 CREATE FUNCTION "freeze_after_snapshot"
|
jbe@0
|
2452 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2453 RETURNS VOID
|
jbe@0
|
2454 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2455 DECLARE
|
jbe@0
|
2456 "issue_row" "issue"%ROWTYPE;
|
jbe@0
|
2457 "policy_row" "policy"%ROWTYPE;
|
jbe@0
|
2458 "initiative_row" "initiative"%ROWTYPE;
|
jbe@0
|
2459 BEGIN
|
jbe@0
|
2460 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@0
|
2461 SELECT * INTO "policy_row"
|
jbe@0
|
2462 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@21
|
2463 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
|
jbe@3
|
2464 UPDATE "issue" SET
|
jbe@4
|
2465 "accepted" = coalesce("accepted", now()),
|
jbe@4
|
2466 "half_frozen" = coalesce("half_frozen", now()),
|
jbe@3
|
2467 "fully_frozen" = now()
|
jbe@3
|
2468 WHERE "id" = "issue_id_p";
|
jbe@0
|
2469 FOR "initiative_row" IN
|
jbe@15
|
2470 SELECT * FROM "initiative"
|
jbe@15
|
2471 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@0
|
2472 LOOP
|
jbe@0
|
2473 IF
|
jbe@0
|
2474 "initiative_row"."satisfied_supporter_count" > 0 AND
|
jbe@0
|
2475 "initiative_row"."satisfied_supporter_count" *
|
jbe@0
|
2476 "policy_row"."initiative_quorum_den" >=
|
jbe@0
|
2477 "issue_row"."population" * "policy_row"."initiative_quorum_num"
|
jbe@0
|
2478 THEN
|
jbe@0
|
2479 UPDATE "initiative" SET "admitted" = TRUE
|
jbe@0
|
2480 WHERE "id" = "initiative_row"."id";
|
jbe@0
|
2481 ELSE
|
jbe@0
|
2482 UPDATE "initiative" SET "admitted" = FALSE
|
jbe@0
|
2483 WHERE "id" = "initiative_row"."id";
|
jbe@0
|
2484 END IF;
|
jbe@0
|
2485 END LOOP;
|
jbe@9
|
2486 IF NOT EXISTS (
|
jbe@9
|
2487 SELECT NULL FROM "initiative"
|
jbe@9
|
2488 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
jbe@9
|
2489 ) THEN
|
jbe@9
|
2490 PERFORM "close_voting"("issue_id_p");
|
jbe@9
|
2491 END IF;
|
jbe@0
|
2492 RETURN;
|
jbe@0
|
2493 END;
|
jbe@0
|
2494 $$;
|
jbe@0
|
2495
|
jbe@0
|
2496 COMMENT ON FUNCTION "freeze_after_snapshot"
|
jbe@0
|
2497 ( "issue"."id"%TYPE )
|
jbe@9
|
2498 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
|
jbe@0
|
2499
|
jbe@0
|
2500
|
jbe@0
|
2501 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
|
jbe@0
|
2502 RETURNS VOID
|
jbe@0
|
2503 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2504 DECLARE
|
jbe@0
|
2505 "issue_row" "issue"%ROWTYPE;
|
jbe@0
|
2506 BEGIN
|
jbe@0
|
2507 PERFORM "create_snapshot"("issue_id_p");
|
jbe@0
|
2508 PERFORM "freeze_after_snapshot"("issue_id_p");
|
jbe@0
|
2509 RETURN;
|
jbe@0
|
2510 END;
|
jbe@0
|
2511 $$;
|
jbe@0
|
2512
|
jbe@0
|
2513 COMMENT ON FUNCTION "freeze_after_snapshot"
|
jbe@0
|
2514 ( "issue"."id"%TYPE )
|
jbe@3
|
2515 IS 'Freeze an issue manually (fully) and start voting';
|
jbe@0
|
2516
|
jbe@0
|
2517
|
jbe@0
|
2518
|
jbe@0
|
2519 -----------------------
|
jbe@0
|
2520 -- Counting of votes --
|
jbe@0
|
2521 -----------------------
|
jbe@0
|
2522
|
jbe@0
|
2523
|
jbe@5
|
2524 CREATE FUNCTION "weight_of_added_vote_delegations"
|
jbe@0
|
2525 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@0
|
2526 "member_id_p" "member"."id"%TYPE,
|
jbe@0
|
2527 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
|
jbe@0
|
2528 RETURNS "direct_voter"."weight"%TYPE
|
jbe@0
|
2529 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2530 DECLARE
|
jbe@0
|
2531 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@0
|
2532 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
|
jbe@0
|
2533 "weight_v" INT4;
|
jbe@8
|
2534 "sub_weight_v" INT4;
|
jbe@0
|
2535 BEGIN
|
jbe@0
|
2536 "weight_v" := 0;
|
jbe@0
|
2537 FOR "issue_delegation_row" IN
|
jbe@0
|
2538 SELECT * FROM "issue_delegation"
|
jbe@0
|
2539 WHERE "trustee_id" = "member_id_p"
|
jbe@0
|
2540 AND "issue_id" = "issue_id_p"
|
jbe@0
|
2541 LOOP
|
jbe@0
|
2542 IF NOT EXISTS (
|
jbe@0
|
2543 SELECT NULL FROM "direct_voter"
|
jbe@0
|
2544 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
2545 AND "issue_id" = "issue_id_p"
|
jbe@0
|
2546 ) AND NOT EXISTS (
|
jbe@0
|
2547 SELECT NULL FROM "delegating_voter"
|
jbe@0
|
2548 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
jbe@0
|
2549 AND "issue_id" = "issue_id_p"
|
jbe@0
|
2550 ) THEN
|
jbe@0
|
2551 "delegate_member_ids_v" :=
|
jbe@0
|
2552 "member_id_p" || "delegate_member_ids_p";
|
jbe@10
|
2553 INSERT INTO "delegating_voter" (
|
jbe@10
|
2554 "issue_id",
|
jbe@10
|
2555 "member_id",
|
jbe@10
|
2556 "scope",
|
jbe@10
|
2557 "delegate_member_ids"
|
jbe@10
|
2558 ) VALUES (
|
jbe@5
|
2559 "issue_id_p",
|
jbe@5
|
2560 "issue_delegation_row"."truster_id",
|
jbe@10
|
2561 "issue_delegation_row"."scope",
|
jbe@5
|
2562 "delegate_member_ids_v"
|
jbe@5
|
2563 );
|
jbe@8
|
2564 "sub_weight_v" := 1 +
|
jbe@8
|
2565 "weight_of_added_vote_delegations"(
|
jbe@8
|
2566 "issue_id_p",
|
jbe@8
|
2567 "issue_delegation_row"."truster_id",
|
jbe@8
|
2568 "delegate_member_ids_v"
|
jbe@8
|
2569 );
|
jbe@8
|
2570 UPDATE "delegating_voter"
|
jbe@8
|
2571 SET "weight" = "sub_weight_v"
|
jbe@8
|
2572 WHERE "issue_id" = "issue_id_p"
|
jbe@8
|
2573 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@8
|
2574 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@0
|
2575 END IF;
|
jbe@0
|
2576 END LOOP;
|
jbe@0
|
2577 RETURN "weight_v";
|
jbe@0
|
2578 END;
|
jbe@0
|
2579 $$;
|
jbe@0
|
2580
|
jbe@5
|
2581 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
|
jbe@0
|
2582 ( "issue"."id"%TYPE,
|
jbe@0
|
2583 "member"."id"%TYPE,
|
jbe@0
|
2584 "delegating_voter"."delegate_member_ids"%TYPE )
|
jbe@0
|
2585 IS 'Helper function for "add_vote_delegations" function';
|
jbe@0
|
2586
|
jbe@0
|
2587
|
jbe@0
|
2588 CREATE FUNCTION "add_vote_delegations"
|
jbe@0
|
2589 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2590 RETURNS VOID
|
jbe@0
|
2591 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2592 DECLARE
|
jbe@0
|
2593 "member_id_v" "member"."id"%TYPE;
|
jbe@0
|
2594 BEGIN
|
jbe@0
|
2595 FOR "member_id_v" IN
|
jbe@0
|
2596 SELECT "member_id" FROM "direct_voter"
|
jbe@0
|
2597 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2598 LOOP
|
jbe@0
|
2599 UPDATE "direct_voter" SET
|
jbe@5
|
2600 "weight" = "weight" + "weight_of_added_vote_delegations"(
|
jbe@0
|
2601 "issue_id_p",
|
jbe@0
|
2602 "member_id_v",
|
jbe@0
|
2603 '{}'
|
jbe@0
|
2604 )
|
jbe@0
|
2605 WHERE "member_id" = "member_id_v"
|
jbe@0
|
2606 AND "issue_id" = "issue_id_p";
|
jbe@0
|
2607 END LOOP;
|
jbe@0
|
2608 RETURN;
|
jbe@0
|
2609 END;
|
jbe@0
|
2610 $$;
|
jbe@0
|
2611
|
jbe@0
|
2612 COMMENT ON FUNCTION "add_vote_delegations"
|
jbe@0
|
2613 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
2614 IS 'Helper function for "close_voting" function';
|
jbe@0
|
2615
|
jbe@0
|
2616
|
jbe@0
|
2617 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@0
|
2618 RETURNS VOID
|
jbe@0
|
2619 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2620 DECLARE
|
jbe@0
|
2621 "issue_row" "issue"%ROWTYPE;
|
jbe@0
|
2622 "member_id_v" "member"."id"%TYPE;
|
jbe@0
|
2623 BEGIN
|
jbe@0
|
2624 PERFORM "global_lock"();
|
jbe@0
|
2625 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@0
|
2626 DELETE FROM "delegating_voter"
|
jbe@0
|
2627 WHERE "issue_id" = "issue_id_p";
|
jbe@0
|
2628 DELETE FROM "direct_voter"
|
jbe@0
|
2629 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2630 AND "autoreject" = TRUE;
|
jbe@0
|
2631 DELETE FROM "direct_voter" USING "member"
|
jbe@0
|
2632 WHERE "direct_voter"."member_id" = "member"."id"
|
jbe@0
|
2633 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@0
|
2634 AND "member"."active" = FALSE;
|
jbe@0
|
2635 UPDATE "direct_voter" SET "weight" = 1
|
jbe@0
|
2636 WHERE "issue_id" = "issue_id_p";
|
jbe@0
|
2637 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@0
|
2638 FOR "member_id_v" IN
|
jbe@0
|
2639 SELECT "interest"."member_id"
|
jbe@0
|
2640 FROM "interest"
|
jbe@0
|
2641 LEFT JOIN "direct_voter"
|
jbe@0
|
2642 ON "interest"."member_id" = "direct_voter"."member_id"
|
jbe@0
|
2643 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
jbe@0
|
2644 LEFT JOIN "delegating_voter"
|
jbe@0
|
2645 ON "interest"."member_id" = "delegating_voter"."member_id"
|
jbe@0
|
2646 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
jbe@0
|
2647 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@0
|
2648 AND "interest"."autoreject" = TRUE
|
jbe@0
|
2649 AND "direct_voter"."member_id" ISNULL
|
jbe@0
|
2650 AND "delegating_voter"."member_id" ISNULL
|
jbe@0
|
2651 UNION SELECT "membership"."member_id"
|
jbe@0
|
2652 FROM "membership"
|
jbe@0
|
2653 LEFT JOIN "interest"
|
jbe@0
|
2654 ON "membership"."member_id" = "interest"."member_id"
|
jbe@0
|
2655 AND "interest"."issue_id" = "issue_id_p"
|
jbe@0
|
2656 LEFT JOIN "direct_voter"
|
jbe@0
|
2657 ON "membership"."member_id" = "direct_voter"."member_id"
|
jbe@0
|
2658 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@0
|
2659 LEFT JOIN "delegating_voter"
|
jbe@0
|
2660 ON "membership"."member_id" = "delegating_voter"."member_id"
|
jbe@0
|
2661 AND "delegating_voter"."issue_id" = "issue_id_p"
|
jbe@0
|
2662 WHERE "membership"."area_id" = "issue_row"."area_id"
|
jbe@0
|
2663 AND "membership"."autoreject" = TRUE
|
jbe@0
|
2664 AND "interest"."autoreject" ISNULL
|
jbe@0
|
2665 AND "direct_voter"."member_id" ISNULL
|
jbe@0
|
2666 AND "delegating_voter"."member_id" ISNULL
|
jbe@0
|
2667 LOOP
|
jbe@11
|
2668 INSERT INTO "direct_voter"
|
jbe@11
|
2669 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
jbe@11
|
2670 ("member_id_v", "issue_id_p", 1, TRUE);
|
jbe@0
|
2671 INSERT INTO "vote" (
|
jbe@0
|
2672 "member_id",
|
jbe@0
|
2673 "issue_id",
|
jbe@0
|
2674 "initiative_id",
|
jbe@0
|
2675 "grade"
|
jbe@0
|
2676 ) SELECT
|
jbe@0
|
2677 "member_id_v" AS "member_id",
|
jbe@0
|
2678 "issue_id_p" AS "issue_id",
|
jbe@0
|
2679 "id" AS "initiative_id",
|
jbe@0
|
2680 -1 AS "grade"
|
jbe@0
|
2681 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
jbe@0
|
2682 END LOOP;
|
jbe@0
|
2683 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@4
|
2684 UPDATE "issue" SET
|
jbe@4
|
2685 "voter_count" = (
|
jbe@4
|
2686 SELECT coalesce(sum("weight"), 0)
|
jbe@4
|
2687 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@6
|
2688 )
|
jbe@6
|
2689 WHERE "id" = "issue_id_p";
|
jbe@0
|
2690 UPDATE "initiative" SET
|
jbe@10
|
2691 "positive_votes" = "vote_counts"."positive_votes",
|
jbe@10
|
2692 "negative_votes" = "vote_counts"."negative_votes",
|
jbe@10
|
2693 "agreed" = CASE WHEN "majority_strict" THEN
|
jbe@10
|
2694 "vote_counts"."positive_votes" * "majority_den" >
|
jbe@10
|
2695 "majority_num" *
|
jbe@10
|
2696 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@10
|
2697 ELSE
|
jbe@10
|
2698 "vote_counts"."positive_votes" * "majority_den" >=
|
jbe@10
|
2699 "majority_num" *
|
jbe@10
|
2700 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@10
|
2701 END
|
jbe@10
|
2702 FROM
|
jbe@10
|
2703 ( SELECT
|
jbe@10
|
2704 "initiative"."id" AS "initiative_id",
|
jbe@10
|
2705 coalesce(
|
jbe@10
|
2706 sum(
|
jbe@10
|
2707 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@10
|
2708 ),
|
jbe@10
|
2709 0
|
jbe@10
|
2710 ) AS "positive_votes",
|
jbe@10
|
2711 coalesce(
|
jbe@10
|
2712 sum(
|
jbe@10
|
2713 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@10
|
2714 ),
|
jbe@10
|
2715 0
|
jbe@10
|
2716 ) AS "negative_votes"
|
jbe@10
|
2717 FROM "initiative"
|
jbe@10
|
2718 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@10
|
2719 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@10
|
2720 LEFT JOIN "direct_voter"
|
jbe@10
|
2721 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
jbe@10
|
2722 LEFT JOIN "vote"
|
jbe@10
|
2723 ON "vote"."initiative_id" = "initiative"."id"
|
jbe@10
|
2724 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@10
|
2725 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@15
|
2726 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
jbe@10
|
2727 GROUP BY "initiative"."id"
|
jbe@10
|
2728 ) AS "vote_counts",
|
jbe@10
|
2729 "issue",
|
jbe@10
|
2730 "policy"
|
jbe@10
|
2731 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
jbe@10
|
2732 AND "issue"."id" = "initiative"."issue_id"
|
jbe@10
|
2733 AND "policy"."id" = "issue"."policy_id";
|
jbe@0
|
2734 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
|
jbe@0
|
2735 END;
|
jbe@0
|
2736 $$;
|
jbe@0
|
2737
|
jbe@0
|
2738 COMMENT ON FUNCTION "close_voting"
|
jbe@0
|
2739 ( "issue"."id"%TYPE )
|
jbe@0
|
2740 IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
|
jbe@0
|
2741
|
jbe@0
|
2742
|
jbe@30
|
2743 CREATE FUNCTION "defeat_strength"
|
jbe@30
|
2744 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
|
jbe@30
|
2745 RETURNS INT8
|
jbe@30
|
2746 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@30
|
2747 BEGIN
|
jbe@30
|
2748 IF "positive_votes_p" > "negative_votes_p" THEN
|
jbe@30
|
2749 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
|
jbe@30
|
2750 ELSIF "positive_votes_p" = "negative_votes_p" THEN
|
jbe@30
|
2751 RETURN 0;
|
jbe@30
|
2752 ELSE
|
jbe@30
|
2753 RETURN -1;
|
jbe@30
|
2754 END IF;
|
jbe@30
|
2755 END;
|
jbe@30
|
2756 $$;
|
jbe@30
|
2757
|
jbe@30
|
2758 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
|
jbe@30
|
2759
|
jbe@30
|
2760
|
jbe@30
|
2761 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
|
jbe@30
|
2762 RETURNS TEXT
|
jbe@0
|
2763 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@0
|
2764 DECLARE
|
jbe@0
|
2765 "i" INTEGER;
|
jbe@0
|
2766 "ary_text_v" TEXT;
|
jbe@0
|
2767 BEGIN
|
jbe@0
|
2768 IF "dim_p" >= 1 THEN
|
jbe@0
|
2769 "ary_text_v" := '{NULL';
|
jbe@0
|
2770 "i" := "dim_p";
|
jbe@0
|
2771 LOOP
|
jbe@0
|
2772 "i" := "i" - 1;
|
jbe@0
|
2773 EXIT WHEN "i" = 0;
|
jbe@0
|
2774 "ary_text_v" := "ary_text_v" || ',NULL';
|
jbe@0
|
2775 END LOOP;
|
jbe@0
|
2776 "ary_text_v" := "ary_text_v" || '}';
|
jbe@30
|
2777 RETURN "ary_text_v";
|
jbe@0
|
2778 ELSE
|
jbe@0
|
2779 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
jbe@0
|
2780 END IF;
|
jbe@0
|
2781 END;
|
jbe@0
|
2782 $$;
|
jbe@0
|
2783
|
jbe@30
|
2784 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
jbe@30
|
2785
|
jbe@30
|
2786
|
jbe@30
|
2787 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
|
jbe@30
|
2788 RETURNS TEXT
|
jbe@0
|
2789 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@0
|
2790 DECLARE
|
jbe@0
|
2791 "i" INTEGER;
|
jbe@0
|
2792 "row_text_v" TEXT;
|
jbe@0
|
2793 "ary_text_v" TEXT;
|
jbe@0
|
2794 BEGIN
|
jbe@0
|
2795 IF "dim_p" >= 1 THEN
|
jbe@0
|
2796 "row_text_v" := '{NULL';
|
jbe@0
|
2797 "i" := "dim_p";
|
jbe@0
|
2798 LOOP
|
jbe@0
|
2799 "i" := "i" - 1;
|
jbe@0
|
2800 EXIT WHEN "i" = 0;
|
jbe@0
|
2801 "row_text_v" := "row_text_v" || ',NULL';
|
jbe@0
|
2802 END LOOP;
|
jbe@0
|
2803 "row_text_v" := "row_text_v" || '}';
|
jbe@0
|
2804 "ary_text_v" := '{' || "row_text_v";
|
jbe@0
|
2805 "i" := "dim_p";
|
jbe@0
|
2806 LOOP
|
jbe@0
|
2807 "i" := "i" - 1;
|
jbe@0
|
2808 EXIT WHEN "i" = 0;
|
jbe@0
|
2809 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
|
jbe@0
|
2810 END LOOP;
|
jbe@0
|
2811 "ary_text_v" := "ary_text_v" || '}';
|
jbe@30
|
2812 RETURN "ary_text_v";
|
jbe@0
|
2813 ELSE
|
jbe@0
|
2814 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
jbe@0
|
2815 END IF;
|
jbe@0
|
2816 END;
|
jbe@0
|
2817 $$;
|
jbe@0
|
2818
|
jbe@30
|
2819 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
jbe@0
|
2820
|
jbe@0
|
2821
|
jbe@0
|
2822 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
jbe@0
|
2823 RETURNS VOID
|
jbe@0
|
2824 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
2825 DECLARE
|
jbe@0
|
2826 "dimension_v" INTEGER;
|
jbe@30
|
2827 "vote_matrix" INT4[][]; -- absolute votes
|
jbe@30
|
2828 "matrix" INT8[][]; -- defeat strength / best paths
|
jbe@0
|
2829 "i" INTEGER;
|
jbe@0
|
2830 "j" INTEGER;
|
jbe@0
|
2831 "k" INTEGER;
|
jbe@0
|
2832 "battle_row" "battle"%ROWTYPE;
|
jbe@0
|
2833 "rank_ary" INT4[];
|
jbe@0
|
2834 "rank_v" INT4;
|
jbe@0
|
2835 "done_v" INTEGER;
|
jbe@0
|
2836 "winners_ary" INTEGER[];
|
jbe@0
|
2837 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@0
|
2838 BEGIN
|
jbe@0
|
2839 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
jbe@10
|
2840 SELECT count(1) INTO "dimension_v" FROM "initiative"
|
jbe@10
|
2841 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
jbe@0
|
2842 IF "dimension_v" = 1 THEN
|
jbe@10
|
2843 UPDATE "initiative" SET "rank" = 1
|
jbe@10
|
2844 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
jbe@0
|
2845 ELSIF "dimension_v" > 1 THEN
|
jbe@30
|
2846 -- Create "vote_matrix" with absolute number of votes in pairwise
|
jbe@30
|
2847 -- comparison:
|
jbe@30
|
2848 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@0
|
2849 "i" := 1;
|
jbe@0
|
2850 "j" := 2;
|
jbe@0
|
2851 FOR "battle_row" IN
|
jbe@0
|
2852 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
2853 ORDER BY "winning_initiative_id", "losing_initiative_id"
|
jbe@0
|
2854 LOOP
|
jbe@30
|
2855 "vote_matrix"["i"]["j"] := "battle_row"."count";
|
jbe@0
|
2856 IF "j" = "dimension_v" THEN
|
jbe@0
|
2857 "i" := "i" + 1;
|
jbe@0
|
2858 "j" := 1;
|
jbe@0
|
2859 ELSE
|
jbe@0
|
2860 "j" := "j" + 1;
|
jbe@0
|
2861 IF "j" = "i" THEN
|
jbe@0
|
2862 "j" := "j" + 1;
|
jbe@0
|
2863 END IF;
|
jbe@0
|
2864 END IF;
|
jbe@0
|
2865 END LOOP;
|
jbe@0
|
2866 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
jbe@0
|
2867 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
jbe@0
|
2868 END IF;
|
jbe@30
|
2869 -- Store defeat strengths in "matrix" using "defeat_strength"
|
jbe@30
|
2870 -- function:
|
jbe@30
|
2871 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@0
|
2872 "i" := 1;
|
jbe@0
|
2873 LOOP
|
jbe@30
|
2874 "j" := 1;
|
jbe@0
|
2875 LOOP
|
jbe@0
|
2876 IF "i" != "j" THEN
|
jbe@30
|
2877 "matrix"["i"]["j"] := "defeat_strength"(
|
jbe@30
|
2878 "vote_matrix"["i"]["j"],
|
jbe@30
|
2879 "vote_matrix"["j"]["i"]
|
jbe@30
|
2880 );
|
jbe@0
|
2881 END IF;
|
jbe@0
|
2882 EXIT WHEN "j" = "dimension_v";
|
jbe@0
|
2883 "j" := "j" + 1;
|
jbe@0
|
2884 END LOOP;
|
jbe@30
|
2885 EXIT WHEN "i" = "dimension_v";
|
jbe@0
|
2886 "i" := "i" + 1;
|
jbe@0
|
2887 END LOOP;
|
jbe@0
|
2888 -- Find best paths:
|
jbe@0
|
2889 "i" := 1;
|
jbe@0
|
2890 LOOP
|
jbe@0
|
2891 "j" := 1;
|
jbe@0
|
2892 LOOP
|
jbe@0
|
2893 IF "i" != "j" THEN
|
jbe@0
|
2894 "k" := 1;
|
jbe@0
|
2895 LOOP
|
jbe@0
|
2896 IF "i" != "k" AND "j" != "k" THEN
|
jbe@0
|
2897 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
jbe@0
|
2898 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
jbe@0
|
2899 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
jbe@0
|
2900 END IF;
|
jbe@0
|
2901 ELSE
|
jbe@0
|
2902 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
jbe@0
|
2903 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
jbe@0
|
2904 END IF;
|
jbe@0
|
2905 END IF;
|
jbe@0
|
2906 END IF;
|
jbe@0
|
2907 EXIT WHEN "k" = "dimension_v";
|
jbe@0
|
2908 "k" := "k" + 1;
|
jbe@0
|
2909 END LOOP;
|
jbe@0
|
2910 END IF;
|
jbe@0
|
2911 EXIT WHEN "j" = "dimension_v";
|
jbe@0
|
2912 "j" := "j" + 1;
|
jbe@0
|
2913 END LOOP;
|
jbe@0
|
2914 EXIT WHEN "i" = "dimension_v";
|
jbe@0
|
2915 "i" := "i" + 1;
|
jbe@0
|
2916 END LOOP;
|
jbe@0
|
2917 -- Determine order of winners:
|
jbe@30
|
2918 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
jbe@0
|
2919 "rank_v" := 1;
|
jbe@0
|
2920 "done_v" := 0;
|
jbe@0
|
2921 LOOP
|
jbe@0
|
2922 "winners_ary" := '{}';
|
jbe@0
|
2923 "i" := 1;
|
jbe@0
|
2924 LOOP
|
jbe@0
|
2925 IF "rank_ary"["i"] ISNULL THEN
|
jbe@0
|
2926 "j" := 1;
|
jbe@0
|
2927 LOOP
|
jbe@0
|
2928 IF
|
jbe@0
|
2929 "i" != "j" AND
|
jbe@0
|
2930 "rank_ary"["j"] ISNULL AND
|
jbe@0
|
2931 "matrix"["j"]["i"] > "matrix"["i"]["j"]
|
jbe@0
|
2932 THEN
|
jbe@0
|
2933 -- someone else is better
|
jbe@0
|
2934 EXIT;
|
jbe@0
|
2935 END IF;
|
jbe@0
|
2936 IF "j" = "dimension_v" THEN
|
jbe@0
|
2937 -- noone is better
|
jbe@0
|
2938 "winners_ary" := "winners_ary" || "i";
|
jbe@0
|
2939 EXIT;
|
jbe@0
|
2940 END IF;
|
jbe@0
|
2941 "j" := "j" + 1;
|
jbe@0
|
2942 END LOOP;
|
jbe@0
|
2943 END IF;
|
jbe@0
|
2944 EXIT WHEN "i" = "dimension_v";
|
jbe@0
|
2945 "i" := "i" + 1;
|
jbe@0
|
2946 END LOOP;
|
jbe@0
|
2947 "i" := 1;
|
jbe@0
|
2948 LOOP
|
jbe@0
|
2949 "rank_ary"["winners_ary"["i"]] := "rank_v";
|
jbe@0
|
2950 "done_v" := "done_v" + 1;
|
jbe@0
|
2951 EXIT WHEN "i" = array_upper("winners_ary", 1);
|
jbe@0
|
2952 "i" := "i" + 1;
|
jbe@0
|
2953 END LOOP;
|
jbe@0
|
2954 EXIT WHEN "done_v" = "dimension_v";
|
jbe@0
|
2955 "rank_v" := "rank_v" + 1;
|
jbe@0
|
2956 END LOOP;
|
jbe@0
|
2957 -- write preliminary ranks:
|
jbe@0
|
2958 "i" := 1;
|
jbe@0
|
2959 FOR "initiative_id_v" IN
|
jbe@10
|
2960 SELECT "id" FROM "initiative"
|
jbe@10
|
2961 WHERE "issue_id" = "issue_id_p" AND "agreed"
|
jbe@10
|
2962 ORDER BY "id"
|
jbe@0
|
2963 LOOP
|
jbe@0
|
2964 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
|
jbe@0
|
2965 WHERE "id" = "initiative_id_v";
|
jbe@0
|
2966 "i" := "i" + 1;
|
jbe@0
|
2967 END LOOP;
|
jbe@0
|
2968 IF "i" != "dimension_v" + 1 THEN
|
jbe@0
|
2969 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
jbe@0
|
2970 END IF;
|
jbe@0
|
2971 -- straighten ranks (start counting with 1, no equal ranks):
|
jbe@0
|
2972 "rank_v" := 1;
|
jbe@0
|
2973 FOR "initiative_id_v" IN
|
jbe@0
|
2974 SELECT "id" FROM "initiative"
|
jbe@0
|
2975 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
|
jbe@0
|
2976 ORDER BY
|
jbe@0
|
2977 "rank",
|
jbe@0
|
2978 "vote_ratio"("positive_votes", "negative_votes") DESC,
|
jbe@0
|
2979 "id"
|
jbe@0
|
2980 LOOP
|
jbe@0
|
2981 UPDATE "initiative" SET "rank" = "rank_v"
|
jbe@0
|
2982 WHERE "id" = "initiative_id_v";
|
jbe@0
|
2983 "rank_v" := "rank_v" + 1;
|
jbe@0
|
2984 END LOOP;
|
jbe@0
|
2985 END IF;
|
jbe@0
|
2986 -- mark issue as finished
|
jbe@0
|
2987 UPDATE "issue" SET "ranks_available" = TRUE
|
jbe@0
|
2988 WHERE "id" = "issue_id_p";
|
jbe@0
|
2989 RETURN;
|
jbe@0
|
2990 END;
|
jbe@0
|
2991 $$;
|
jbe@0
|
2992
|
jbe@0
|
2993 COMMENT ON FUNCTION "calculate_ranks"
|
jbe@0
|
2994 ( "issue"."id"%TYPE )
|
jbe@0
|
2995 IS 'Determine ranking (Votes have to be counted first)';
|
jbe@0
|
2996
|
jbe@0
|
2997
|
jbe@0
|
2998
|
jbe@0
|
2999 -----------------------------
|
jbe@0
|
3000 -- Automatic state changes --
|
jbe@0
|
3001 -----------------------------
|
jbe@0
|
3002
|
jbe@0
|
3003
|
jbe@0
|
3004 CREATE FUNCTION "check_issue"
|
jbe@0
|
3005 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@0
|
3006 RETURNS VOID
|
jbe@0
|
3007 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
3008 DECLARE
|
jbe@0
|
3009 "issue_row" "issue"%ROWTYPE;
|
jbe@0
|
3010 "policy_row" "policy"%ROWTYPE;
|
jbe@0
|
3011 "voting_requested_v" BOOLEAN;
|
jbe@0
|
3012 BEGIN
|
jbe@0
|
3013 PERFORM "global_lock"();
|
jbe@0
|
3014 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@24
|
3015 -- only process open issues:
|
jbe@0
|
3016 IF "issue_row"."closed" ISNULL THEN
|
jbe@0
|
3017 SELECT * INTO "policy_row" FROM "policy"
|
jbe@0
|
3018 WHERE "id" = "issue_row"."policy_id";
|
jbe@24
|
3019 -- create a snapshot, unless issue is already fully frozen:
|
jbe@3
|
3020 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@0
|
3021 PERFORM "create_snapshot"("issue_id_p");
|
jbe@0
|
3022 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@0
|
3023 END IF;
|
jbe@24
|
3024 -- eventually close or accept issues, which have not been accepted:
|
jbe@0
|
3025 IF "issue_row"."accepted" ISNULL THEN
|
jbe@0
|
3026 IF EXISTS (
|
jbe@0
|
3027 SELECT NULL FROM "initiative"
|
jbe@0
|
3028 WHERE "issue_id" = "issue_id_p"
|
jbe@0
|
3029 AND "supporter_count" > 0
|
jbe@0
|
3030 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
jbe@0
|
3031 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
jbe@0
|
3032 ) THEN
|
jbe@24
|
3033 -- accept issues, if supporter count is high enough
|
jbe@3
|
3034 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@0
|
3035 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
|
jbe@0
|
3036 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
|
jbe@0
|
3037 WHERE "id" = "issue_row"."id";
|
jbe@0
|
3038 ELSIF
|
jbe@22
|
3039 now() >= "issue_row"."created" + "issue_row"."admission_time"
|
jbe@0
|
3040 THEN
|
jbe@24
|
3041 -- close issues, if admission time has expired
|
jbe@0
|
3042 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
jbe@0
|
3043 UPDATE "issue" SET "closed" = now()
|
jbe@0
|
3044 WHERE "id" = "issue_row"."id";
|
jbe@0
|
3045 END IF;
|
jbe@0
|
3046 END IF;
|
jbe@24
|
3047 -- eventually half freeze issues:
|
jbe@0
|
3048 IF
|
jbe@24
|
3049 -- NOTE: issue can't be closed at this point, if it has been accepted
|
jbe@0
|
3050 "issue_row"."accepted" NOTNULL AND
|
jbe@3
|
3051 "issue_row"."half_frozen" ISNULL
|
jbe@0
|
3052 THEN
|
jbe@0
|
3053 SELECT
|
jbe@0
|
3054 CASE
|
jbe@0
|
3055 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
jbe@0
|
3056 TRUE
|
jbe@0
|
3057 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
jbe@0
|
3058 FALSE
|
jbe@0
|
3059 ELSE NULL
|
jbe@0
|
3060 END
|
jbe@0
|
3061 INTO "voting_requested_v"
|
jbe@0
|
3062 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@0
|
3063 IF
|
jbe@0
|
3064 "voting_requested_v" OR (
|
jbe@3
|
3065 "voting_requested_v" ISNULL AND
|
jbe@22
|
3066 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
|
jbe@0
|
3067 )
|
jbe@0
|
3068 THEN
|
jbe@21
|
3069 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
|
jbe@3
|
3070 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
|
jbe@3
|
3071 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
|
jbe@3
|
3072 WHERE "id" = "issue_row"."id";
|
jbe@0
|
3073 END IF;
|
jbe@0
|
3074 END IF;
|
jbe@24
|
3075 -- close issues after some time, if all initiatives have been revoked:
|
jbe@24
|
3076 IF
|
jbe@24
|
3077 "issue_row"."closed" ISNULL AND
|
jbe@24
|
3078 NOT EXISTS (
|
jbe@24
|
3079 -- all initiatives are revoked
|
jbe@24
|
3080 SELECT NULL FROM "initiative"
|
jbe@24
|
3081 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@24
|
3082 ) AND (
|
jbe@24
|
3083 NOT EXISTS (
|
jbe@24
|
3084 -- and no initiatives have been revoked lately
|
jbe@24
|
3085 SELECT NULL FROM "initiative"
|
jbe@24
|
3086 WHERE "issue_id" = "issue_id_p"
|
jbe@24
|
3087 AND now() < "revoked" + "issue_row"."verification_time"
|
jbe@24
|
3088 ) OR (
|
jbe@24
|
3089 -- or verification time has elapsed
|
jbe@24
|
3090 "issue_row"."half_frozen" NOTNULL AND
|
jbe@24
|
3091 "issue_row"."fully_frozen" ISNULL AND
|
jbe@24
|
3092 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@24
|
3093 )
|
jbe@24
|
3094 )
|
jbe@24
|
3095 THEN
|
jbe@24
|
3096 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
|
jbe@24
|
3097 UPDATE "issue" SET "closed" = "issue_row"."closed"
|
jbe@24
|
3098 WHERE "id" = "issue_row"."id";
|
jbe@24
|
3099 END IF;
|
jbe@24
|
3100 -- fully freeze issue after verification time:
|
jbe@0
|
3101 IF
|
jbe@3
|
3102 "issue_row"."half_frozen" NOTNULL AND
|
jbe@3
|
3103 "issue_row"."fully_frozen" ISNULL AND
|
jbe@24
|
3104 "issue_row"."closed" ISNULL AND
|
jbe@22
|
3105 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
jbe@3
|
3106 THEN
|
jbe@3
|
3107 PERFORM "freeze_after_snapshot"("issue_id_p");
|
jbe@24
|
3108 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
|
jbe@3
|
3109 END IF;
|
jbe@9
|
3110 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@24
|
3111 -- close issue by calling close_voting(...) after voting time:
|
jbe@3
|
3112 IF
|
jbe@9
|
3113 "issue_row"."closed" ISNULL AND
|
jbe@3
|
3114 "issue_row"."fully_frozen" NOTNULL AND
|
jbe@22
|
3115 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
|
jbe@0
|
3116 THEN
|
jbe@0
|
3117 PERFORM "close_voting"("issue_id_p");
|
jbe@0
|
3118 END IF;
|
jbe@0
|
3119 END IF;
|
jbe@0
|
3120 RETURN;
|
jbe@0
|
3121 END;
|
jbe@0
|
3122 $$;
|
jbe@0
|
3123
|
jbe@0
|
3124 COMMENT ON FUNCTION "check_issue"
|
jbe@0
|
3125 ( "issue"."id"%TYPE )
|
jbe@0
|
3126 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
|
jbe@0
|
3127
|
jbe@0
|
3128
|
jbe@0
|
3129 CREATE FUNCTION "check_everything"()
|
jbe@0
|
3130 RETURNS VOID
|
jbe@0
|
3131 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@0
|
3132 DECLARE
|
jbe@0
|
3133 "issue_id_v" "issue"."id"%TYPE;
|
jbe@0
|
3134 BEGIN
|
jbe@1
|
3135 DELETE FROM "expired_session";
|
jbe@4
|
3136 PERFORM "calculate_member_counts"();
|
jbe@4
|
3137 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
|
jbe@0
|
3138 PERFORM "check_issue"("issue_id_v");
|
jbe@0
|
3139 END LOOP;
|
jbe@4
|
3140 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
|
jbe@0
|
3141 PERFORM "calculate_ranks"("issue_id_v");
|
jbe@0
|
3142 END LOOP;
|
jbe@0
|
3143 RETURN;
|
jbe@0
|
3144 END;
|
jbe@0
|
3145 $$;
|
jbe@0
|
3146
|
jbe@0
|
3147 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
|
jbe@0
|
3148
|
jbe@0
|
3149
|
jbe@0
|
3150
|
jbe@8
|
3151 ------------------------------
|
jbe@8
|
3152 -- Deletion of private data --
|
jbe@8
|
3153 ------------------------------
|
jbe@8
|
3154
|
jbe@8
|
3155
|
jbe@8
|
3156 CREATE FUNCTION "delete_private_data"()
|
jbe@8
|
3157 RETURNS VOID
|
jbe@8
|
3158 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@8
|
3159 DECLARE
|
jbe@8
|
3160 "issue_id_v" "issue"."id"%TYPE;
|
jbe@8
|
3161 BEGIN
|
jbe@9
|
3162 UPDATE "member" SET
|
jbe@11
|
3163 "login" = 'login' || "id"::text,
|
jbe@11
|
3164 "password" = NULL,
|
jbe@11
|
3165 "notify_email" = NULL,
|
jbe@11
|
3166 "notify_email_unconfirmed" = NULL,
|
jbe@11
|
3167 "notify_email_secret" = NULL,
|
jbe@11
|
3168 "notify_email_secret_expiry" = NULL,
|
jbe@11
|
3169 "password_reset_secret" = NULL,
|
jbe@11
|
3170 "password_reset_secret_expiry" = NULL,
|
jbe@11
|
3171 "organizational_unit" = NULL,
|
jbe@11
|
3172 "internal_posts" = NULL,
|
jbe@11
|
3173 "realname" = NULL,
|
jbe@11
|
3174 "birthday" = NULL,
|
jbe@11
|
3175 "address" = NULL,
|
jbe@11
|
3176 "email" = NULL,
|
jbe@11
|
3177 "xmpp_address" = NULL,
|
jbe@11
|
3178 "website" = NULL,
|
jbe@11
|
3179 "phone" = NULL,
|
jbe@11
|
3180 "mobile_phone" = NULL,
|
jbe@11
|
3181 "profession" = NULL,
|
jbe@11
|
3182 "external_memberships" = NULL,
|
jbe@11
|
3183 "external_posts" = NULL,
|
jbe@11
|
3184 "statement" = NULL;
|
jbe@11
|
3185 -- "text_search_data" is updated by triggers
|
jbe@8
|
3186 DELETE FROM "session";
|
jbe@12
|
3187 DELETE FROM "invite_code";
|
jbe@13
|
3188 DELETE FROM "contact";
|
jbe@11
|
3189 DELETE FROM "setting";
|
jbe@11
|
3190 DELETE FROM "member_image";
|
jbe@8
|
3191 DELETE FROM "direct_voter" USING "issue"
|
jbe@8
|
3192 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@8
|
3193 AND "issue"."closed" ISNULL;
|
jbe@8
|
3194 RETURN;
|
jbe@8
|
3195 END;
|
jbe@8
|
3196 $$;
|
jbe@8
|
3197
|
jbe@8
|
3198 COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
|
jbe@8
|
3199
|
jbe@8
|
3200
|
jbe@8
|
3201
|
jbe@0
|
3202 COMMIT;
|