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