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