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