rev |
line source |
jbe@536
|
1 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
|
jbe@536
|
2 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
|
jbe@539
|
3 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_created';
|
jbe@539
|
4 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_updated';
|
jbe@536
|
5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
|
jbe@536
|
6 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
|
jbe@554
|
7 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_deleted';
|
jbe@532
|
8 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
|
jbe@554
|
9 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_deleted';
|
jbe@532
|
10 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_active';
|
jbe@532
|
11 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_name_updated';
|
jbe@532
|
12 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_profile_updated';
|
jbe@532
|
13 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_image_updated';
|
jbe@532
|
14 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'interest';
|
jbe@532
|
15 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'initiator';
|
jbe@532
|
16 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support';
|
jbe@532
|
17 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support_updated';
|
jbe@532
|
18 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_rated';
|
jbe@532
|
19 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'delegation';
|
jbe@532
|
20 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'contact';
|
jbe@532
|
21
|
jbe@532
|
22
|
jbe@532
|
23 BEGIN;
|
jbe@532
|
24
|
jbe@532
|
25
|
jbe@532
|
26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@532
|
27 SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
|
jbe@532
|
28 AS "subquery"("string", "major", "minor", "revision");
|
jbe@532
|
29
|
jbe@532
|
30
|
jbe@532
|
31 ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
|
jbe@532
|
32
|
jbe@532
|
33 COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';
|
jbe@552
|
34
|
jbe@552
|
35
|
jbe@557
|
36 CREATE TABLE "verification" (
|
jbe@557
|
37 "id" SERIAL8 PRIMARY KEY,
|
jbe@557
|
38 "requested" TIMESTAMPTZ,
|
jbe@557
|
39 "request_origin" JSONB,
|
jbe@557
|
40 "request_data" JSONB,
|
jbe@560
|
41 "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@560
|
42 "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@560
|
43 "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
jbe@557
|
44 "verified" TIMESTAMPTZ,
|
jbe@557
|
45 "verification_data" JSONB,
|
jbe@557
|
46 "denied" TIMESTAMPTZ,
|
jbe@557
|
47 "comment" TEXT,
|
jbe@557
|
48 CONSTRAINT "verified_and_denied_conflict" CHECK (
|
jbe@557
|
49 "verified" ISNULL OR "denied" ISNULL ) );
|
jbe@557
|
50 CREATE INDEX "verification_requested_idx" ON "verification" ("requested");
|
jbe@557
|
51 CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
|
jbe@560
|
52 CREATE INDEX "verification_requesting_member_id_idx" ON "verification" ("requesting_member_id");
|
jbe@560
|
53 CREATE INDEX "verification_verified_member_id_idx" ON "verification" ("verified_member_id");
|
jbe@557
|
54 CREATE INDEX "verification_verified_idx" ON "verification" ("verified");
|
jbe@557
|
55 CREATE INDEX "verification_denied_idx" ON "verification" ("denied");
|
jbe@557
|
56
|
jbe@557
|
57 COMMENT ON TABLE "verification" IS 'Request to verify a participant';
|
jbe@557
|
58
|
jbe@560
|
59 COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted';
|
jbe@560
|
60 COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)';
|
jbe@560
|
61 COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)';
|
jbe@560
|
62 COMMENT ON COLUMN "verification"."requesting_member_id" IS 'Member who requested verification';
|
jbe@560
|
63 COMMENT ON COLUMN "verification"."verifying_member_id" IS 'Member who processed the verification request (i.e. who accepted or denied the request)';
|
jbe@560
|
64 COMMENT ON COLUMN "verification"."verified_member_id" IS 'Member entry containing verified information (not necessarily equal to "requesting_member_id" but may be merged with requesting member later)';
|
jbe@560
|
65 COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority';
|
jbe@560
|
66 COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data or information about the authority or operator who accepted or denied the request, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup';
|
jbe@560
|
67 COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority';
|
jbe@560
|
68 COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment';
|
jbe@557
|
69
|
jbe@557
|
70
|
jbe@552
|
71 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;
|
jbe@552
|
72 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"
|
jbe@552
|
73 CHECK ("deleted" ISNULL OR "locked" = TRUE);
|
jbe@532
|
74
|
jbe@552
|
75 COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';
|
jbe@552
|
76
|
jbe@532
|
77
|
jbe@544
|
78 CREATE TABLE "member_settings" (
|
jbe@544
|
79 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@544
|
80 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
|
jbe@544
|
81
|
jbe@544
|
82 COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
|
jbe@544
|
83
|
jbe@544
|
84
|
jbe@544
|
85 CREATE TABLE "member_useterms" (
|
jbe@559
|
86 "id" SERIAL8 PRIMARY KEY,
|
jbe@559
|
87 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@558
|
88 "accepted" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@544
|
89 "contract_identifier" TEXT NOT NULL );
|
jbe@544
|
90
|
jbe@544
|
91 COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
|
jbe@544
|
92
|
jbe@544
|
93 COMMENT ON COLUMN "member_useterms"."accepted" IS 'Point in time when user accepted the terms of use';
|
jbe@544
|
94 COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
|
jbe@544
|
95
|
jbe@544
|
96
|
jbe@532
|
97 CREATE TABLE "member_profile" (
|
jbe@532
|
98 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
99 "formatting_engine" TEXT,
|
jbe@532
|
100 "statement" TEXT,
|
jbe@544
|
101 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
|
jbe@532
|
102 "profile_text_data" TEXT,
|
jbe@532
|
103 "text_search_data" TSVECTOR );
|
jbe@532
|
104 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
|
jbe@532
|
105 CREATE TRIGGER "update_text_search_data"
|
jbe@532
|
106 BEFORE INSERT OR UPDATE ON "member_profile"
|
jbe@532
|
107 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
108 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@532
|
109 'statement', 'profile_text_data');
|
jbe@532
|
110
|
jbe@532
|
111 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';
|
jbe@532
|
112 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile';
|
jbe@532
|
113 COMMENT ON COLUMN "member_profile"."profile" IS 'Additional profile data as JSON document';
|
jbe@532
|
114 COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';
|
jbe@532
|
115
|
jbe@532
|
116
|
jbe@532
|
117 INSERT INTO "member_profile"
|
jbe@532
|
118 ( "member_id", "formatting_engine", "statement", "profile")
|
jbe@532
|
119 SELECT
|
jbe@532
|
120 "id" AS "member_id",
|
jbe@532
|
121 "formatting_engine",
|
jbe@532
|
122 "statement",
|
jbe@532
|
123 json_build_object(
|
jbe@532
|
124 'organizational_unit', "organizational_unit",
|
jbe@532
|
125 'internal_posts', "internal_posts",
|
jbe@532
|
126 'realname', "realname",
|
jbe@532
|
127 'birthday', to_char("birthday", 'YYYY-MM-DD'),
|
jbe@532
|
128 'address', "address",
|
jbe@532
|
129 'email', "email",
|
jbe@532
|
130 'xmpp_address', "xmpp_address",
|
jbe@532
|
131 'website', "website",
|
jbe@532
|
132 'phone', "phone",
|
jbe@532
|
133 'mobile_phone', "mobile_phone",
|
jbe@532
|
134 'profession', "profession",
|
jbe@532
|
135 'external_memberships', "external_memberships",
|
jbe@532
|
136 'external_posts', "external_posts"
|
jbe@532
|
137 ) AS "profile"
|
jbe@532
|
138 FROM "member";
|
jbe@532
|
139
|
jbe@532
|
140 UPDATE "member_profile" SET "profile_text_data" =
|
jbe@532
|
141 coalesce(("profile"->>'organizational_unit') || ' ', '') ||
|
jbe@532
|
142 coalesce(("profile"->>'internal_posts') || ' ', '') ||
|
jbe@532
|
143 coalesce(("profile"->>'realname') || ' ', '') ||
|
jbe@532
|
144 coalesce(("profile"->>'birthday') || ' ', '') ||
|
jbe@532
|
145 coalesce(("profile"->>'address') || ' ', '') ||
|
jbe@532
|
146 coalesce(("profile"->>'email') || ' ', '') ||
|
jbe@532
|
147 coalesce(("profile"->>'xmpp_address') || ' ', '') ||
|
jbe@532
|
148 coalesce(("profile"->>'website') || ' ', '') ||
|
jbe@532
|
149 coalesce(("profile"->>'phone') || ' ', '') ||
|
jbe@532
|
150 coalesce(("profile"->>'mobile_phone') || ' ', '') ||
|
jbe@532
|
151 coalesce(("profile"->>'profession') || ' ', '') ||
|
jbe@532
|
152 coalesce(("profile"->>'external_memberships') || ' ', '') ||
|
jbe@532
|
153 coalesce(("profile"->>'external_posts') || ' ', '');
|
jbe@532
|
154
|
jbe@532
|
155
|
jbe@532
|
156 DROP VIEW "newsletter_to_send";
|
jbe@532
|
157 DROP VIEW "scheduled_notification_to_send";
|
jbe@532
|
158 DROP VIEW "member_to_notify";
|
jbe@532
|
159 DROP VIEW "member_eligible_to_be_notified";
|
jbe@532
|
160
|
jbe@532
|
161
|
jbe@532
|
162 ALTER TABLE "member" DROP COLUMN "organizational_unit";
|
jbe@532
|
163 ALTER TABLE "member" DROP COLUMN "internal_posts";
|
jbe@532
|
164 ALTER TABLE "member" DROP COLUMN "realname";
|
jbe@532
|
165 ALTER TABLE "member" DROP COLUMN "birthday";
|
jbe@532
|
166 ALTER TABLE "member" DROP COLUMN "address";
|
jbe@532
|
167 ALTER TABLE "member" DROP COLUMN "email";
|
jbe@532
|
168 ALTER TABLE "member" DROP COLUMN "xmpp_address";
|
jbe@532
|
169 ALTER TABLE "member" DROP COLUMN "website";
|
jbe@532
|
170 ALTER TABLE "member" DROP COLUMN "phone";
|
jbe@532
|
171 ALTER TABLE "member" DROP COLUMN "mobile_phone";
|
jbe@532
|
172 ALTER TABLE "member" DROP COLUMN "profession";
|
jbe@532
|
173 ALTER TABLE "member" DROP COLUMN "external_memberships";
|
jbe@532
|
174 ALTER TABLE "member" DROP COLUMN "external_posts";
|
jbe@532
|
175 ALTER TABLE "member" DROP COLUMN "formatting_engine";
|
jbe@532
|
176 ALTER TABLE "member" DROP COLUMN "statement";
|
jbe@532
|
177
|
jbe@532
|
178 ALTER TABLE "member" ADD COLUMN "location" JSONB;
|
jbe@532
|
179 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';
|
jbe@532
|
180 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
181
|
jbe@532
|
182 DROP TRIGGER "update_text_search_data" ON "member";
|
jbe@532
|
183 CREATE TRIGGER "update_text_search_data"
|
jbe@532
|
184 BEFORE INSERT OR UPDATE ON "member"
|
jbe@532
|
185 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
186 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
jbe@532
|
187 "name", "identification");
|
jbe@532
|
188
|
jbe@532
|
189
|
jbe@532
|
190 CREATE VIEW "member_eligible_to_be_notified" AS
|
jbe@532
|
191 SELECT * FROM "member"
|
jbe@532
|
192 WHERE "activated" NOTNULL AND "locked" = FALSE;
|
jbe@532
|
193
|
jbe@532
|
194 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
|
jbe@532
|
195
|
jbe@532
|
196
|
jbe@532
|
197 CREATE VIEW "member_to_notify" AS
|
jbe@532
|
198 SELECT * FROM "member_eligible_to_be_notified"
|
jbe@532
|
199 WHERE "disable_notifications" = FALSE;
|
jbe@532
|
200
|
jbe@532
|
201 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
|
jbe@532
|
202
|
jbe@532
|
203
|
jbe@532
|
204 CREATE VIEW "scheduled_notification_to_send" AS
|
jbe@532
|
205 SELECT * FROM (
|
jbe@532
|
206 SELECT
|
jbe@532
|
207 "id" AS "recipient_id",
|
jbe@532
|
208 now() - CASE WHEN "notification_dow" ISNULL THEN
|
jbe@532
|
209 ( "notification_sent"::DATE + CASE
|
jbe@532
|
210 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@532
|
211 THEN 0 ELSE 1 END
|
jbe@532
|
212 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@532
|
213 ELSE
|
jbe@532
|
214 ( "notification_sent"::DATE +
|
jbe@532
|
215 ( 7 + "notification_dow" -
|
jbe@532
|
216 EXTRACT(DOW FROM
|
jbe@532
|
217 ( "notification_sent"::DATE + CASE
|
jbe@532
|
218 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@532
|
219 THEN 0 ELSE 1 END
|
jbe@532
|
220 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@532
|
221 )::INTEGER
|
jbe@532
|
222 ) % 7 +
|
jbe@532
|
223 CASE
|
jbe@532
|
224 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@532
|
225 THEN 0 ELSE 1
|
jbe@532
|
226 END
|
jbe@532
|
227 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@532
|
228 END AS "pending"
|
jbe@532
|
229 FROM (
|
jbe@532
|
230 SELECT
|
jbe@532
|
231 "id",
|
jbe@532
|
232 COALESCE("notification_sent", "activated") AS "notification_sent",
|
jbe@532
|
233 "notification_dow",
|
jbe@532
|
234 "notification_hour"
|
jbe@532
|
235 FROM "member_to_notify"
|
jbe@532
|
236 WHERE "notification_hour" NOTNULL
|
jbe@532
|
237 ) AS "subquery1"
|
jbe@532
|
238 ) AS "subquery2"
|
jbe@532
|
239 WHERE "pending" > '0'::INTERVAL;
|
jbe@532
|
240
|
jbe@532
|
241 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
|
jbe@532
|
242
|
jbe@532
|
243 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
|
jbe@532
|
244 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
|
jbe@532
|
245
|
jbe@532
|
246
|
jbe@532
|
247 CREATE VIEW "newsletter_to_send" AS
|
jbe@532
|
248 SELECT
|
jbe@532
|
249 "member"."id" AS "recipient_id",
|
jbe@532
|
250 "newsletter"."id" AS "newsletter_id",
|
jbe@532
|
251 "newsletter"."published"
|
jbe@532
|
252 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
|
jbe@556
|
253 LEFT JOIN "privilege" ON
|
jbe@556
|
254 "privilege"."member_id" = "member"."id" AND
|
jbe@556
|
255 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
jbe@556
|
256 "privilege"."voting_right" = TRUE
|
jbe@532
|
257 LEFT JOIN "subscription" ON
|
jbe@532
|
258 "subscription"."member_id" = "member"."id" AND
|
jbe@532
|
259 "subscription"."unit_id" = "newsletter"."unit_id"
|
jbe@532
|
260 WHERE "newsletter"."published" <= now()
|
jbe@532
|
261 AND "newsletter"."sent" ISNULL
|
jbe@532
|
262 AND (
|
jbe@532
|
263 "member"."disable_notifications" = FALSE OR
|
jbe@532
|
264 "newsletter"."include_all_members" = TRUE )
|
jbe@532
|
265 AND (
|
jbe@532
|
266 "newsletter"."unit_id" ISNULL OR
|
jbe@556
|
267 "privilege"."member_id" NOTNULL OR
|
jbe@532
|
268 "subscription"."member_id" NOTNULL );
|
jbe@532
|
269
|
jbe@532
|
270 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
|
jbe@532
|
271
|
jbe@532
|
272 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
|
jbe@532
|
273
|
jbe@532
|
274
|
jbe@532
|
275 DROP VIEW "expired_session";
|
jbe@532
|
276 DROP TABLE "session";
|
jbe@532
|
277
|
jbe@532
|
278
|
jbe@532
|
279 CREATE TABLE "session" (
|
jbe@532
|
280 UNIQUE ("member_id", "id"), -- index needed for foreign-key on table "token"
|
jbe@532
|
281 "id" SERIAL8 PRIMARY KEY,
|
jbe@532
|
282 "ident" TEXT NOT NULL UNIQUE,
|
jbe@532
|
283 "additional_secret" TEXT,
|
jbe@532
|
284 "logout_token" TEXT,
|
jbe@532
|
285 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
jbe@532
|
286 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
|
jbe@532
|
287 "authority" TEXT,
|
jbe@532
|
288 "authority_uid" TEXT,
|
jbe@532
|
289 "authority_login" TEXT,
|
jbe@532
|
290 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@532
|
291 "lang" TEXT );
|
jbe@532
|
292 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
jbe@532
|
293
|
jbe@532
|
294 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
|
jbe@532
|
295
|
jbe@532
|
296 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
jbe@532
|
297 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
jbe@532
|
298 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component';
|
jbe@532
|
299 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
jbe@532
|
300 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
|
jbe@532
|
301 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
|
jbe@532
|
302 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
|
jbe@532
|
303 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
|
jbe@532
|
304 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
jbe@532
|
305
|
jbe@532
|
306
|
jbe@532
|
307 CREATE TYPE "authflow" AS ENUM ('code', 'token');
|
jbe@532
|
308
|
jbe@532
|
309 COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';
|
jbe@532
|
310
|
jbe@532
|
311
|
jbe@532
|
312 CREATE TABLE "system_application" (
|
jbe@532
|
313 "id" SERIAL4 PRIMARY KEY,
|
jbe@532
|
314 "name" TEXT NOT NULL,
|
jbe@548
|
315 "discovery_baseurl" TEXT,
|
jbe@532
|
316 "client_id" TEXT NOT NULL UNIQUE,
|
jbe@532
|
317 "default_redirect_uri" TEXT NOT NULL,
|
jbe@532
|
318 "cert_common_name" TEXT,
|
jbe@532
|
319 "client_cred_scope" TEXT,
|
jbe@532
|
320 "flow" "authflow",
|
jbe@532
|
321 "automatic_scope" TEXT,
|
jbe@532
|
322 "permitted_scope" TEXT,
|
jbe@532
|
323 "forbidden_scope" TEXT );
|
jbe@532
|
324
|
jbe@532
|
325 COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';
|
jbe@532
|
326
|
jbe@532
|
327 COMMENT ON COLUMN "system_application"."name" IS 'Human readable name of application';
|
jbe@548
|
328 COMMENT ON COLUMN "system_application"."discovery_baseurl" IS 'Base URL for application discovery; NULL for hidden application';
|
jbe@532
|
329 COMMENT ON COLUMN "system_application"."client_id" IS 'OAuth 2.0 "client_id"';
|
jbe@532
|
330 COMMENT ON COLUMN "system_application"."cert_common_name" IS 'Value for CN field of TLS client certificate';
|
jbe@532
|
331 COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';
|
jbe@532
|
332 COMMENT ON COLUMN "system_application"."flow" IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';
|
jbe@532
|
333 COMMENT ON COLUMN "system_application"."automatic_scope" IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';
|
jbe@532
|
334 COMMENT ON COLUMN "system_application"."permitted_scope" IS 'Space-separated list of scopes; If set, scope that members may grant to the application is limited to the given value';
|
jbe@532
|
335 COMMENT ON COLUMN "system_application"."forbidden_scope" IS 'Space-separated list of scopes that may not be granted to the application by a member';
|
jbe@532
|
336
|
jbe@532
|
337
|
jbe@532
|
338 CREATE TABLE "system_application_redirect_uri" (
|
jbe@532
|
339 PRIMARY KEY ("system_application_id", "redirect_uri"),
|
jbe@532
|
340 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
341 "redirect_uri" TEXT );
|
jbe@532
|
342
|
jbe@532
|
343 COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';
|
jbe@532
|
344
|
jbe@532
|
345
|
jbe@532
|
346 CREATE TABLE "dynamic_application_scope" (
|
jbe@532
|
347 PRIMARY KEY ("redirect_uri", "flow", "scope"),
|
jbe@532
|
348 "redirect_uri" TEXT,
|
jbe@532
|
349 "flow" TEXT,
|
jbe@532
|
350 "scope" TEXT,
|
jbe@532
|
351 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours' );
|
jbe@532
|
352 CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");
|
jbe@532
|
353 CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");
|
jbe@532
|
354
|
jbe@532
|
355 COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';
|
jbe@532
|
356
|
jbe@532
|
357 COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';
|
jbe@532
|
358 COMMENT ON COLUMN "dynamic_application_scope"."flow" IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';
|
jbe@532
|
359 COMMENT ON COLUMN "dynamic_application_scope"."scope" IS 'Single scope without space characters (use multiple rows for more scopes)';
|
jbe@532
|
360 COMMENT ON COLUMN "dynamic_application_scope"."expiry" IS 'Expiry unless renewed';
|
jbe@532
|
361
|
jbe@532
|
362
|
jbe@532
|
363 CREATE TABLE "member_application" (
|
jbe@532
|
364 "id" SERIAL4 PRIMARY KEY,
|
jbe@532
|
365 UNIQUE ("system_application_id", "member_id"),
|
jbe@532
|
366 UNIQUE ("domain", "member_id"),
|
jbe@532
|
367 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
368 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
369 "domain" TEXT,
|
jbe@532
|
370 "session_id" INT8,
|
jbe@532
|
371 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
372 "scope" TEXT NOT NULL,
|
jbe@532
|
373 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (
|
jbe@532
|
374 ("system_application_id" NOTNULL AND "domain" ISNULL) OR
|
jbe@532
|
375 ("system_application_id" ISNULL AND "domain" NOTNULL) ) );
|
jbe@532
|
376 CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");
|
jbe@532
|
377
|
jbe@532
|
378 COMMENT ON TABLE "member_application" IS 'Application authorized by a member';
|
jbe@532
|
379
|
jbe@532
|
380 COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';
|
jbe@532
|
381 COMMENT ON COLUMN "member_application"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
|
jbe@532
|
382 COMMENT ON COLUMN "member_application"."session_id" IS 'If set, registration ends with session';
|
jbe@532
|
383 COMMENT ON COLUMN "member_application"."scope" IS 'Granted scope as space-separated list of strings';
|
jbe@532
|
384
|
jbe@532
|
385
|
jbe@532
|
386 CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');
|
jbe@532
|
387
|
jbe@532
|
388 COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';
|
jbe@532
|
389
|
jbe@532
|
390
|
jbe@532
|
391 CREATE TABLE "token" (
|
jbe@532
|
392 "id" SERIAL8 PRIMARY KEY,
|
jbe@532
|
393 "token" TEXT NOT NULL UNIQUE,
|
jbe@532
|
394 "token_type" "token_type" NOT NULL,
|
jbe@532
|
395 "authorization_token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
396 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
397 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
398 "domain" TEXT,
|
jbe@532
|
399 FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
400 "session_id" INT8,
|
jbe@532
|
401 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, -- NOTE: deletion through "detach_token_from_session" trigger on table "session"
|
jbe@532
|
402 "redirect_uri" TEXT,
|
jbe@532
|
403 "redirect_uri_explicit" BOOLEAN,
|
jbe@532
|
404 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@532
|
405 "expiry" TIMESTAMPTZ DEFAULT now() + '1 hour',
|
jbe@532
|
406 "used" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@532
|
407 "scope" TEXT NOT NULL,
|
jbe@532
|
408 CONSTRAINT "access_token_needs_expiry"
|
jbe@532
|
409 CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),
|
jbe@532
|
410 CONSTRAINT "authorization_token_needs_redirect_uri"
|
jbe@532
|
411 CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );
|
jbe@532
|
412 CREATE INDEX "token_member_id_idx" ON "token" ("member_id");
|
jbe@532
|
413 CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");
|
jbe@532
|
414 CREATE INDEX "token_expiry_idx" ON "token" ("expiry");
|
jbe@532
|
415
|
jbe@532
|
416 COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';
|
jbe@532
|
417
|
jbe@532
|
418 COMMENT ON COLUMN "token"."token" IS 'String secret (the actual token)';
|
jbe@532
|
419 COMMENT ON COLUMN "token"."authorization_token_id" IS 'Reference to authorization token if tokens were originally created by Authorization Code flow (allows deletion if code is used twice)';
|
jbe@532
|
420 COMMENT ON COLUMN "token"."system_application_id" IS 'If set, then application is a system application';
|
jbe@532
|
421 COMMENT ON COLUMN "token"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
|
jbe@532
|
422 COMMENT ON COLUMN "token"."session_id" IS 'If set, then token is tied to a session; Deletion of session sets value to NULL (via trigger) and removes all scopes without suffix ''_detached''';
|
jbe@532
|
423 COMMENT ON COLUMN "token"."redirect_uri" IS 'Authorization codes must be bound to a specific redirect URI';
|
jbe@532
|
424 COMMENT ON COLUMN "token"."redirect_uri_explicit" IS 'True if ''redirect_uri'' parameter was explicitly specified during authorization request of the Authorization Code flow (since RFC 6749 requires it to be included in the access token request in this case)';
|
jbe@532
|
425 COMMENT ON COLUMN "token"."expiry" IS 'Point in time when code or token expired; In case of "used" authorization codes, authorization code must not be deleted as long as tokens exist which refer to the authorization code';
|
jbe@532
|
426 COMMENT ON COLUMN "token"."used" IS 'Can be set to TRUE for authorization codes that have been used (enables deletion of authorization codes that were used twice)';
|
jbe@532
|
427 COMMENT ON COLUMN "token"."scope" IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';
|
jbe@532
|
428
|
jbe@532
|
429
|
jbe@532
|
430 CREATE TABLE "token_scope" (
|
jbe@532
|
431 PRIMARY KEY ("token_id", "index"),
|
jbe@532
|
432 "token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
433 "index" INT4,
|
jbe@532
|
434 "scope" TEXT NOT NULL );
|
jbe@532
|
435
|
jbe@532
|
436 COMMENT ON TABLE "token_scope" IS 'Additional scopes for an authorization code if ''scope1'', ''scope2'', etc. parameters were used during Authorization Code flow to request several access and refresh tokens at once';
|
jbe@532
|
437
|
jbe@532
|
438
|
jbe@532
|
439 ALTER TABLE "policy" ADD COLUMN "issue_quorum" INT4 CHECK ("issue_quorum" >= 1);
|
jbe@532
|
440 ALTER TABLE "policy" ADD COLUMN "initiative_quorum" INT4 CHECK ("initiative_quorum" >= 1);
|
jbe@532
|
441
|
jbe@532
|
442 UPDATE "policy" SET "issue_quorum" = 1 WHERE "issue_quorum_num" NOTNULL;
|
jbe@532
|
443 UPDATE "policy" SET "initiative_quorum" = 1;
|
jbe@532
|
444
|
jbe@532
|
445 ALTER TABLE "policy" ALTER COLUMN "initiative_quorum" SET NOT NULL;
|
jbe@532
|
446
|
jbe@532
|
447 ALTER TABLE "policy" DROP CONSTRAINT "timing";
|
jbe@532
|
448 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
|
jbe@532
|
449 ALTER TABLE "policy" ADD CONSTRAINT
|
jbe@532
|
450 "issue_quorum_if_and_only_if_not_polling" CHECK (
|
jbe@532
|
451 "polling" = ("issue_quorum" ISNULL) AND
|
jbe@532
|
452 "polling" = ("issue_quorum_num" ISNULL) AND
|
jbe@532
|
453 "polling" = ("issue_quorum_den" ISNULL)
|
jbe@532
|
454 );
|
jbe@532
|
455 ALTER TABLE "policy" ADD CONSTRAINT
|
jbe@532
|
456 "min_admission_time_smaller_than_max_admission_time" CHECK (
|
jbe@532
|
457 "min_admission_time" < "max_admission_time"
|
jbe@532
|
458 );
|
jbe@532
|
459 ALTER TABLE "policy" ADD CONSTRAINT
|
jbe@532
|
460 "timing_null_or_not_null_constraints" CHECK (
|
jbe@532
|
461 ( "polling" = FALSE AND
|
jbe@532
|
462 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
|
jbe@532
|
463 "discussion_time" NOTNULL AND
|
jbe@532
|
464 "verification_time" NOTNULL AND
|
jbe@532
|
465 "voting_time" NOTNULL ) OR
|
jbe@532
|
466 ( "polling" = TRUE AND
|
jbe@532
|
467 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
|
jbe@532
|
468 "discussion_time" NOTNULL AND
|
jbe@532
|
469 "verification_time" NOTNULL AND
|
jbe@532
|
470 "voting_time" NOTNULL ) OR
|
jbe@532
|
471 ( "polling" = TRUE AND
|
jbe@532
|
472 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
|
jbe@532
|
473 "discussion_time" ISNULL AND
|
jbe@532
|
474 "verification_time" ISNULL AND
|
jbe@532
|
475 "voting_time" ISNULL )
|
jbe@532
|
476 );
|
jbe@532
|
477
|
jbe@532
|
478 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open; Note: should be considerably smaller than "max_admission_time"';
|
jbe@532
|
479 COMMENT ON COLUMN "policy"."issue_quorum" IS 'Absolute number of supporters needed by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';
|
jbe@532
|
480 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
|
jbe@532
|
481 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
|
jbe@532
|
482 COMMENT ON COLUMN "policy"."initiative_quorum" IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
|
jbe@532
|
483 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@532
|
484 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@532
|
485
|
jbe@532
|
486
|
jbe@551
|
487 ALTER TABLE "unit" ADD COLUMN "location" JSONB;
|
jbe@551
|
488
|
jbe@551
|
489 CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
490
|
jbe@556
|
491 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
|
jbe@551
|
492 COMMENT ON COLUMN "unit"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
|
jbe@532
|
493
|
jbe@532
|
494
|
jbe@532
|
495 DROP INDEX "area_unit_id_idx";
|
jbe@532
|
496 ALTER TABLE "area" ADD UNIQUE ("unit_id", "id");
|
jbe@532
|
497
|
jbe@532
|
498 ALTER TABLE "area" ADD COLUMN "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0);
|
jbe@532
|
499 ALTER TABLE "area" ADD COLUMN "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0);
|
jbe@532
|
500 ALTER TABLE "area" ADD COLUMN "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL);
|
jbe@532
|
501 ALTER TABLE "area" ADD COLUMN "quorum_exponent" NUMERIC NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1);
|
jbe@532
|
502 ALTER TABLE "area" ADD COLUMN "quorum_factor" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1);
|
jbe@532
|
503 ALTER TABLE "area" ADD COLUMN "quorum_den" INT4 CHECK ("quorum_den" > 0);
|
jbe@532
|
504 ALTER TABLE "area" ADD COLUMN "issue_quorum" INT4;
|
jbe@551
|
505 ALTER TABLE "area" ADD COLUMN "location" JSONB;
|
jbe@532
|
506
|
jbe@532
|
507 ALTER TABLE "area" DROP COLUMN "direct_member_count";
|
jbe@532
|
508 ALTER TABLE "area" DROP COLUMN "member_weight";
|
jbe@532
|
509
|
jbe@551
|
510 CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
511
|
jbe@532
|
512 COMMENT ON COLUMN "area"."quorum_standard" IS 'Parameter for dynamic issue quorum: default quorum';
|
jbe@532
|
513 COMMENT ON COLUMN "area"."quorum_issues" IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';
|
jbe@532
|
514 COMMENT ON COLUMN "area"."quorum_time" IS 'Parameter for dynamic issue quorum: discussion, verification, and voting time of open issues to result in the given default quorum (open issues with shorter time will increase quorum and open issues with longer time will reduce quorum if "quorum_exponent" is greater than zero)';
|
jbe@532
|
515 COMMENT ON COLUMN "area"."quorum_exponent" IS 'Parameter for dynamic issue quorum: set to zero to ignore duration of open issues, set to one to fully take duration of open issues into account; defaults to 0.5';
|
jbe@532
|
516 COMMENT ON COLUMN "area"."quorum_factor" IS 'Parameter for dynamic issue quorum: factor to increase dynamic quorum when a number of "quorum_issues" issues with "quorum_time" duration of discussion, verification, and voting phase are added to the number of open admitted issues';
|
jbe@532
|
517 COMMENT ON COLUMN "area"."quorum_den" IS 'Parameter for dynamic issue quorum: when set, dynamic quorum is multiplied with "issue"."population" and divided by "quorum_den" (and then rounded up)';
|
jbe@532
|
518 COMMENT ON COLUMN "area"."issue_quorum" IS 'Additional dynamic issue quorum based on the number of open accepted issues; automatically calculated by function "issue_admission"';
|
jbe@532
|
519 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
|
jbe@551
|
520 COMMENT ON COLUMN "area"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
|
jbe@532
|
521
|
jbe@532
|
522
|
jbe@532
|
523 CREATE TABLE "snapshot" (
|
jbe@532
|
524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "issue"
|
jbe@532
|
525 "id" SERIAL8 PRIMARY KEY,
|
jbe@532
|
526 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
jbe@532
|
527 "population" INT4,
|
jbe@532
|
528 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
529 "issue_id" INT4 ); -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
jbe@532
|
530
|
jbe@532
|
531 COMMENT ON TABLE "snapshot" IS 'Point in time when a snapshot of one or more issues (see table "snapshot_issue") and their supporter situation is taken';
|
jbe@532
|
532
|
jbe@532
|
533
|
jbe@532
|
534 CREATE TABLE "snapshot_population" (
|
jbe@532
|
535 PRIMARY KEY ("snapshot_id", "member_id"),
|
jbe@532
|
536 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
537 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
|
jbe@532
|
538
|
jbe@532
|
539 COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
|
jbe@532
|
540
|
jbe@532
|
541
|
jbe@532
|
542 ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
|
jbe@532
|
543 DROP INDEX "issue_area_id_idx";
|
jbe@536
|
544 ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
|
jbe@536
|
545 DROP INDEX "issue_policy_id_idx";
|
jbe@532
|
546
|
jbe@532
|
547 ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
|
jbe@532
|
548
|
jbe@532
|
549 ALTER TABLE "issue" ADD COLUMN "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
550 ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
jbe@532
|
551 ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
|
jbe@532
|
552 ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
|
jbe@568
|
553 ALTER TABLE "issue" ADD COLUMN "issue_quorum" INT4;
|
jbe@568
|
554 ALTER TABLE "issue" ADD COLUMN "initiative_quorum" INT4;
|
jbe@532
|
555
|
jbe@532
|
556 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
|
jbe@532
|
557 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
558 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "full_freeze_snapshot_id")
|
jbe@532
|
559 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
560
|
jbe@532
|
561 ALTER TABLE "issue" DROP CONSTRAINT "last_snapshot_on_full_freeze";
|
jbe@532
|
562 ALTER TABLE "issue" DROP CONSTRAINT "freeze_requires_snapshot";
|
jbe@532
|
563 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
|
jbe@532
|
564
|
jbe@532
|
565 CREATE INDEX "issue_state_idx" ON "issue" ("state");
|
jbe@532
|
566 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
|
jbe@532
|
567 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
|
jbe@532
|
568 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
|
jbe@532
|
569 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
|
jbe@532
|
570
|
jbe@532
|
571 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when the issue was accepted for further discussion (see columns "issue_quorum_num" and "issue_quorum_den" of table "policy" and quorum columns of table "area")';
|
jbe@532
|
572 COMMENT ON COLUMN "issue"."calculated" IS 'Point in time, when most recent snapshot and "population" and *_count values were calculated (NOTE: value is equal to "snapshot"."calculated" of snapshot with "id"="issue"."latest_snapshot_id")';
|
jbe@532
|
573 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
|
jbe@532
|
574 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
|
jbe@532
|
575 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
|
jbe@532
|
576 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
|
jbe@568
|
577 COMMENT ON COLUMN "issue"."issue_quorum" IS 'Calculated number of supporters needed by an initiative of the issue to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';
|
jbe@568
|
578 COMMENT ON COLUMN "issue"."initiative_quorum" IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
|
jbe@532
|
579 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
|
jbe@532
|
580
|
jbe@532
|
581
|
jbe@532
|
582 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
583
|
jbe@532
|
584
|
jbe@532
|
585 ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";
|
jbe@532
|
586 ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
jbe@532
|
587
|
jbe@532
|
588 ALTER TABLE "initiative" ADD COLUMN "location" JSONB;
|
jbe@532
|
589 ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;
|
jbe@532
|
590
|
jbe@532
|
591 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
592 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
|
jbe@532
|
593
|
jbe@532
|
594 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
|
jbe@532
|
595
|
jbe@532
|
596
|
jbe@532
|
597 ALTER TABLE "draft" ADD COLUMN "location" JSONB;
|
jbe@532
|
598
|
jbe@532
|
599 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
600
|
jbe@532
|
601 COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';
|
jbe@532
|
602
|
jbe@532
|
603
|
jbe@532
|
604 ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;
|
jbe@532
|
605
|
jbe@532
|
606 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
607
|
jbe@532
|
608 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';
|
jbe@532
|
609
|
jbe@532
|
610
|
jbe@532
|
611 CREATE TABLE "temporary_suggestion_counts" (
|
jbe@532
|
612 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
613 "minus2_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
614 "minus2_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
615 "minus1_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
616 "minus1_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
617 "plus1_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
618 "plus1_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
619 "plus2_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
620 "plus2_fulfilled_count" INT4 NOT NULL );
|
jbe@532
|
621
|
jbe@532
|
622 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
|
jbe@532
|
623
|
jbe@532
|
624 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
|
jbe@532
|
625
|
jbe@532
|
626
|
jbe@532
|
627 ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";
|
jbe@532
|
628 ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
629
|
jbe@532
|
630
|
jbe@532
|
631 ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";
|
jbe@532
|
632 ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
633
|
jbe@532
|
634
|
jbe@532
|
635 ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";
|
jbe@532
|
636 ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
637
|
jbe@532
|
638
|
jbe@532
|
639 CREATE TABLE "snapshot_issue" (
|
jbe@532
|
640 PRIMARY KEY ("snapshot_id", "issue_id"),
|
jbe@532
|
641 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
642 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@532
|
643 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
|
jbe@532
|
644
|
jbe@532
|
645 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
|
jbe@532
|
646
|
jbe@532
|
647 COMMENT ON COLUMN "snapshot_issue"."issue_id" IS 'Issue being part of the snapshot; Trigger "delete_snapshot_on_partial_delete" on "snapshot_issue" table will delete snapshot if an issue of the snapshot is deleted.';
|
jbe@532
|
648
|
jbe@532
|
649
|
jbe@532
|
650 ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old"; -- TODO!
|
jbe@532
|
651 ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";
|
jbe@532
|
652 ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";
|
jbe@532
|
653
|
jbe@532
|
654 ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old"; -- TODO!
|
jbe@532
|
655 ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";
|
jbe@532
|
656 ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";
|
jbe@532
|
657
|
jbe@532
|
658 ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old"; -- TODO!
|
jbe@532
|
659 ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";
|
jbe@532
|
660 ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";
|
jbe@532
|
661
|
jbe@532
|
662
|
jbe@532
|
663 CREATE TABLE "direct_interest_snapshot" (
|
jbe@532
|
664 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
|
jbe@532
|
665 "snapshot_id" INT8,
|
jbe@532
|
666 "issue_id" INT4,
|
jbe@532
|
667 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
668 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
669 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
670 "weight" INT4 );
|
jbe@532
|
671 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
|
jbe@532
|
672
|
jbe@532
|
673 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
|
jbe@532
|
674
|
jbe@532
|
675 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
|
jbe@532
|
676
|
jbe@532
|
677
|
jbe@532
|
678 CREATE TABLE "delegating_interest_snapshot" (
|
jbe@532
|
679 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
|
jbe@532
|
680 "snapshot_id" INT8,
|
jbe@532
|
681 "issue_id" INT4,
|
jbe@532
|
682 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
683 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
684 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
685 "weight" INT4,
|
jbe@532
|
686 "scope" "delegation_scope" NOT NULL,
|
jbe@532
|
687 "delegate_member_ids" INT4[] NOT NULL );
|
jbe@532
|
688 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
|
jbe@532
|
689
|
jbe@532
|
690 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
|
jbe@532
|
691
|
jbe@532
|
692 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
|
jbe@532
|
693 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
|
jbe@532
|
694 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@532
|
695
|
jbe@532
|
696
|
jbe@532
|
697 CREATE TABLE "direct_supporter_snapshot" (
|
jbe@532
|
698 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
|
jbe@532
|
699 "snapshot_id" INT8,
|
jbe@532
|
700 "issue_id" INT4 NOT NULL,
|
jbe@532
|
701 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
702 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
703 "initiative_id" INT4,
|
jbe@532
|
704 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
705 "draft_id" INT8 NOT NULL,
|
jbe@532
|
706 "informed" BOOLEAN NOT NULL,
|
jbe@532
|
707 "satisfied" BOOLEAN NOT NULL,
|
jbe@532
|
708 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
709 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
|
jbe@532
|
710 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@532
|
711 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
|
jbe@532
|
712
|
jbe@532
|
713 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';
|
jbe@532
|
714
|
jbe@532
|
715 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
|
jbe@532
|
716 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
|
jbe@532
|
717 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
|
jbe@532
|
718
|
jbe@532
|
719
|
jbe@532
|
720 ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";
|
jbe@532
|
721 DROP INDEX "non_voter_member_id_idx";
|
jbe@532
|
722
|
jbe@532
|
723 ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");
|
jbe@532
|
724 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
|
jbe@532
|
725
|
jbe@532
|
726
|
jbe@545
|
727 INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")
|
jbe@545
|
728 SELECT
|
jbe@545
|
729 "member_id",
|
jbe@545
|
730 regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",
|
jbe@545
|
731 regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"
|
jbe@545
|
732 FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';
|
jbe@545
|
733
|
jbe@545
|
734
|
jbe@542
|
735 DROP TABLE "setting";
|
jbe@542
|
736 DROP TABLE "setting_map";
|
jbe@542
|
737 DROP TABLE "member_relation_setting";
|
jbe@542
|
738 DROP TABLE "unit_setting";
|
jbe@542
|
739 DROP TABLE "area_setting";
|
jbe@542
|
740 DROP TABLE "initiative_setting";
|
jbe@542
|
741 DROP TABLE "suggestion_setting";
|
jbe@542
|
742
|
jbe@542
|
743
|
jbe@532
|
744 ALTER TABLE "event" ADD COLUMN "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
745 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope";
|
jbe@532
|
746 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
747 ALTER TABLE "event" ADD COLUMN "area_id" INT4;
|
jbe@536
|
748 ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
749 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN;
|
jbe@532
|
750 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4;
|
jbe@532
|
751 ALTER TABLE "event" ADD COLUMN "text_value" TEXT;
|
jbe@532
|
752 ALTER TABLE "event" ADD COLUMN "old_text_value" TEXT;
|
jbe@532
|
753
|
jbe@532
|
754 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
755 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@536
|
756 ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
757
|
jbe@532
|
758 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
|
jbe@532
|
759 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
|
jbe@532
|
760 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";
|
jbe@532
|
761 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";
|
jbe@532
|
762
|
jbe@553
|
763 UPDATE "event" SET
|
jbe@553
|
764 "unit_id" = "area"."unit_id",
|
jbe@553
|
765 "area_id" = "issue"."area_id",
|
jbe@553
|
766 "policy_id" = "issue"."policy_id"
|
jbe@532
|
767 FROM "issue", "area"
|
jbe@532
|
768 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
|
jbe@532
|
769
|
jbe@532
|
770 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
|
jbe@532
|
771 "event" != 'issue_state_changed' OR (
|
jbe@532
|
772 "member_id" ISNULL AND
|
jbe@532
|
773 "other_member_id" ISNULL AND
|
jbe@532
|
774 "scope" ISNULL AND
|
jbe@532
|
775 "unit_id" NOTNULL AND
|
jbe@532
|
776 "area_id" NOTNULL AND
|
jbe@536
|
777 "policy_id" NOTNULL AND
|
jbe@532
|
778 "issue_id" NOTNULL AND
|
jbe@532
|
779 "state" NOTNULL AND
|
jbe@532
|
780 "initiative_id" ISNULL AND
|
jbe@532
|
781 "draft_id" ISNULL AND
|
jbe@532
|
782 "suggestion_id" ISNULL AND
|
jbe@532
|
783 "boolean_value" ISNULL AND
|
jbe@532
|
784 "numeric_value" ISNULL AND
|
jbe@532
|
785 "text_value" ISNULL AND
|
jbe@532
|
786 "old_text_value" ISNULL ));
|
jbe@532
|
787 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
|
jbe@532
|
788 "event" NOT IN (
|
jbe@532
|
789 'initiative_created_in_new_issue',
|
jbe@532
|
790 'initiative_created_in_existing_issue',
|
jbe@532
|
791 'initiative_revoked',
|
jbe@532
|
792 'new_draft_created'
|
jbe@532
|
793 ) OR (
|
jbe@532
|
794 "member_id" NOTNULL AND
|
jbe@532
|
795 "other_member_id" ISNULL AND
|
jbe@532
|
796 "scope" ISNULL AND
|
jbe@532
|
797 "unit_id" NOTNULL AND
|
jbe@532
|
798 "area_id" NOTNULL AND
|
jbe@536
|
799 "policy_id" NOTNULL AND
|
jbe@532
|
800 "issue_id" NOTNULL AND
|
jbe@532
|
801 "state" NOTNULL AND
|
jbe@532
|
802 "initiative_id" NOTNULL AND
|
jbe@532
|
803 "draft_id" NOTNULL AND
|
jbe@532
|
804 "suggestion_id" ISNULL AND
|
jbe@532
|
805 "boolean_value" ISNULL AND
|
jbe@532
|
806 "numeric_value" ISNULL AND
|
jbe@532
|
807 "text_value" ISNULL AND
|
jbe@532
|
808 "old_text_value" ISNULL ));
|
jbe@532
|
809 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
|
jbe@532
|
810 "event" != 'suggestion_created' OR (
|
jbe@532
|
811 "member_id" NOTNULL AND
|
jbe@532
|
812 "other_member_id" ISNULL AND
|
jbe@532
|
813 "scope" ISNULL AND
|
jbe@532
|
814 "unit_id" NOTNULL AND
|
jbe@532
|
815 "area_id" NOTNULL AND
|
jbe@536
|
816 "policy_id" NOTNULL AND
|
jbe@532
|
817 "issue_id" NOTNULL AND
|
jbe@532
|
818 "state" NOTNULL AND
|
jbe@532
|
819 "initiative_id" NOTNULL AND
|
jbe@532
|
820 "draft_id" ISNULL AND
|
jbe@532
|
821 "suggestion_id" NOTNULL AND
|
jbe@532
|
822 "boolean_value" ISNULL AND
|
jbe@532
|
823 "numeric_value" ISNULL AND
|
jbe@532
|
824 "text_value" ISNULL AND
|
jbe@532
|
825 "old_text_value" ISNULL ));
|
jbe@532
|
826 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
|
jbe@554
|
827 "event" != 'suggestion_deleted' OR (
|
jbe@532
|
828 "member_id" ISNULL AND
|
jbe@532
|
829 "other_member_id" ISNULL AND
|
jbe@532
|
830 "scope" ISNULL AND
|
jbe@532
|
831 "unit_id" NOTNULL AND
|
jbe@532
|
832 "area_id" NOTNULL AND
|
jbe@536
|
833 "policy_id" NOTNULL AND
|
jbe@532
|
834 "issue_id" NOTNULL AND
|
jbe@532
|
835 "state" NOTNULL AND
|
jbe@532
|
836 "initiative_id" NOTNULL AND
|
jbe@532
|
837 "draft_id" ISNULL AND
|
jbe@532
|
838 "suggestion_id" NOTNULL AND
|
jbe@532
|
839 "boolean_value" ISNULL AND
|
jbe@532
|
840 "numeric_value" ISNULL AND
|
jbe@532
|
841 "text_value" ISNULL AND
|
jbe@532
|
842 "old_text_value" ISNULL ));
|
jbe@532
|
843 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
|
jbe@532
|
844 "event" NOT IN (
|
jbe@532
|
845 'member_activated',
|
jbe@554
|
846 'member_deleted',
|
jbe@532
|
847 'member_profile_updated',
|
jbe@532
|
848 'member_image_updated'
|
jbe@532
|
849 ) OR (
|
jbe@532
|
850 "member_id" NOTNULL AND
|
jbe@532
|
851 "other_member_id" ISNULL AND
|
jbe@532
|
852 "scope" ISNULL AND
|
jbe@532
|
853 "unit_id" ISNULL AND
|
jbe@532
|
854 "area_id" ISNULL AND
|
jbe@536
|
855 "policy_id" ISNULL AND
|
jbe@532
|
856 "issue_id" ISNULL AND
|
jbe@532
|
857 "state" ISNULL AND
|
jbe@532
|
858 "initiative_id" ISNULL AND
|
jbe@532
|
859 "draft_id" ISNULL AND
|
jbe@532
|
860 "suggestion_id" ISNULL AND
|
jbe@532
|
861 "boolean_value" ISNULL AND
|
jbe@532
|
862 "numeric_value" ISNULL AND
|
jbe@532
|
863 "text_value" ISNULL AND
|
jbe@532
|
864 "old_text_value" ISNULL ));
|
jbe@532
|
865 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
|
jbe@532
|
866 "event" != 'member_active' OR (
|
jbe@532
|
867 "member_id" NOTNULL AND
|
jbe@532
|
868 "other_member_id" ISNULL AND
|
jbe@532
|
869 "scope" ISNULL AND
|
jbe@532
|
870 "unit_id" ISNULL AND
|
jbe@532
|
871 "area_id" ISNULL AND
|
jbe@536
|
872 "policy_id" ISNULL AND
|
jbe@532
|
873 "issue_id" ISNULL AND
|
jbe@532
|
874 "state" ISNULL AND
|
jbe@532
|
875 "initiative_id" ISNULL AND
|
jbe@532
|
876 "draft_id" ISNULL AND
|
jbe@532
|
877 "suggestion_id" ISNULL AND
|
jbe@532
|
878 "boolean_value" NOTNULL AND
|
jbe@532
|
879 "numeric_value" ISNULL AND
|
jbe@532
|
880 "text_value" ISNULL AND
|
jbe@532
|
881 "old_text_value" ISNULL ));
|
jbe@532
|
882 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
|
jbe@532
|
883 "event" != 'member_name_updated' OR (
|
jbe@532
|
884 "member_id" NOTNULL AND
|
jbe@532
|
885 "other_member_id" ISNULL AND
|
jbe@532
|
886 "scope" ISNULL AND
|
jbe@532
|
887 "unit_id" ISNULL AND
|
jbe@532
|
888 "area_id" ISNULL AND
|
jbe@536
|
889 "policy_id" ISNULL AND
|
jbe@532
|
890 "issue_id" ISNULL AND
|
jbe@532
|
891 "state" ISNULL AND
|
jbe@532
|
892 "initiative_id" ISNULL AND
|
jbe@532
|
893 "draft_id" ISNULL AND
|
jbe@532
|
894 "suggestion_id" ISNULL AND
|
jbe@532
|
895 "boolean_value" ISNULL AND
|
jbe@532
|
896 "numeric_value" ISNULL AND
|
jbe@532
|
897 "text_value" NOTNULL AND
|
jbe@532
|
898 "old_text_value" NOTNULL ));
|
jbe@532
|
899 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
|
jbe@532
|
900 "event" != 'interest' OR (
|
jbe@532
|
901 "member_id" NOTNULL AND
|
jbe@532
|
902 "other_member_id" ISNULL AND
|
jbe@532
|
903 "scope" ISNULL AND
|
jbe@532
|
904 "unit_id" NOTNULL AND
|
jbe@532
|
905 "area_id" NOTNULL AND
|
jbe@536
|
906 "policy_id" NOTNULL AND
|
jbe@532
|
907 "issue_id" NOTNULL AND
|
jbe@532
|
908 "state" NOTNULL AND
|
jbe@532
|
909 "initiative_id" ISNULL AND
|
jbe@532
|
910 "draft_id" ISNULL AND
|
jbe@532
|
911 "suggestion_id" ISNULL AND
|
jbe@532
|
912 "boolean_value" NOTNULL AND
|
jbe@532
|
913 "numeric_value" ISNULL AND
|
jbe@532
|
914 "text_value" ISNULL AND
|
jbe@532
|
915 "old_text_value" ISNULL ));
|
jbe@532
|
916 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
|
jbe@532
|
917 "event" != 'initiator' OR (
|
jbe@532
|
918 "member_id" NOTNULL AND
|
jbe@532
|
919 "other_member_id" ISNULL AND
|
jbe@532
|
920 "scope" ISNULL AND
|
jbe@532
|
921 "unit_id" NOTNULL AND
|
jbe@532
|
922 "area_id" NOTNULL AND
|
jbe@536
|
923 "policy_id" NOTNULL AND
|
jbe@532
|
924 "issue_id" NOTNULL AND
|
jbe@532
|
925 "state" NOTNULL AND
|
jbe@532
|
926 "initiative_id" NOTNULL AND
|
jbe@532
|
927 "draft_id" ISNULL AND
|
jbe@532
|
928 "suggestion_id" ISNULL AND
|
jbe@532
|
929 "boolean_value" NOTNULL AND
|
jbe@532
|
930 "numeric_value" ISNULL AND
|
jbe@532
|
931 "text_value" ISNULL AND
|
jbe@532
|
932 "old_text_value" ISNULL ));
|
jbe@532
|
933 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
|
jbe@532
|
934 "event" != 'support' OR (
|
jbe@532
|
935 "member_id" NOTNULL AND
|
jbe@532
|
936 "other_member_id" ISNULL AND
|
jbe@532
|
937 "scope" ISNULL AND
|
jbe@532
|
938 "unit_id" NOTNULL AND
|
jbe@532
|
939 "area_id" NOTNULL AND
|
jbe@536
|
940 "policy_id" NOTNULL AND
|
jbe@532
|
941 "issue_id" NOTNULL AND
|
jbe@532
|
942 "state" NOTNULL AND
|
jbe@532
|
943 "initiative_id" NOTNULL AND
|
jbe@532
|
944 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
|
jbe@532
|
945 "suggestion_id" ISNULL AND
|
jbe@532
|
946 "boolean_value" NOTNULL AND
|
jbe@532
|
947 "numeric_value" ISNULL AND
|
jbe@532
|
948 "text_value" ISNULL AND
|
jbe@532
|
949 "old_text_value" ISNULL ));
|
jbe@532
|
950 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
|
jbe@532
|
951 "event" != 'support_updated' OR (
|
jbe@532
|
952 "member_id" NOTNULL AND
|
jbe@532
|
953 "other_member_id" ISNULL AND
|
jbe@532
|
954 "scope" ISNULL AND
|
jbe@532
|
955 "unit_id" NOTNULL AND
|
jbe@532
|
956 "area_id" NOTNULL AND
|
jbe@536
|
957 "policy_id" NOTNULL AND
|
jbe@532
|
958 "issue_id" NOTNULL AND
|
jbe@532
|
959 "state" NOTNULL AND
|
jbe@532
|
960 "initiative_id" NOTNULL AND
|
jbe@532
|
961 "draft_id" NOTNULL AND
|
jbe@532
|
962 "suggestion_id" ISNULL AND
|
jbe@532
|
963 "boolean_value" ISNULL AND
|
jbe@532
|
964 "numeric_value" ISNULL AND
|
jbe@532
|
965 "text_value" ISNULL AND
|
jbe@532
|
966 "old_text_value" ISNULL ));
|
jbe@532
|
967 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
|
jbe@532
|
968 "event" != 'suggestion_rated' OR (
|
jbe@532
|
969 "member_id" NOTNULL AND
|
jbe@532
|
970 "other_member_id" ISNULL AND
|
jbe@532
|
971 "scope" ISNULL AND
|
jbe@532
|
972 "unit_id" NOTNULL AND
|
jbe@532
|
973 "area_id" NOTNULL AND
|
jbe@536
|
974 "policy_id" NOTNULL AND
|
jbe@532
|
975 "issue_id" NOTNULL AND
|
jbe@532
|
976 "state" NOTNULL AND
|
jbe@532
|
977 "initiative_id" NOTNULL AND
|
jbe@532
|
978 "draft_id" ISNULL AND
|
jbe@532
|
979 "suggestion_id" NOTNULL AND
|
jbe@532
|
980 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
|
jbe@532
|
981 "numeric_value" NOTNULL AND
|
jbe@532
|
982 "numeric_value" IN (-2, -1, 0, 1, 2) AND
|
jbe@532
|
983 "text_value" ISNULL AND
|
jbe@532
|
984 "old_text_value" ISNULL ));
|
jbe@532
|
985 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
|
jbe@532
|
986 "event" != 'delegation' OR (
|
jbe@532
|
987 "member_id" NOTNULL AND
|
bsw@564
|
988 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND
|
jbe@532
|
989 "scope" NOTNULL AND
|
jbe@532
|
990 "unit_id" NOTNULL AND
|
jbe@532
|
991 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
|
jbe@536
|
992 "policy_id" ISNULL AND
|
jbe@532
|
993 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
|
jbe@532
|
994 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
|
jbe@532
|
995 "initiative_id" ISNULL AND
|
jbe@532
|
996 "draft_id" ISNULL AND
|
jbe@532
|
997 "suggestion_id" ISNULL AND
|
jbe@532
|
998 "boolean_value" NOTNULL AND
|
jbe@532
|
999 "numeric_value" ISNULL AND
|
jbe@532
|
1000 "text_value" ISNULL AND
|
jbe@532
|
1001 "old_text_value" ISNULL ));
|
jbe@532
|
1002 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
|
jbe@532
|
1003 "event" != 'contact' OR (
|
jbe@532
|
1004 "member_id" NOTNULL AND
|
jbe@532
|
1005 "other_member_id" NOTNULL AND
|
jbe@532
|
1006 "scope" ISNULL AND
|
jbe@532
|
1007 "unit_id" ISNULL AND
|
jbe@532
|
1008 "area_id" ISNULL AND
|
jbe@536
|
1009 "policy_id" ISNULL AND
|
jbe@532
|
1010 "issue_id" ISNULL AND
|
jbe@532
|
1011 "state" ISNULL AND
|
jbe@532
|
1012 "initiative_id" ISNULL AND
|
jbe@532
|
1013 "draft_id" ISNULL AND
|
jbe@532
|
1014 "suggestion_id" ISNULL AND
|
jbe@532
|
1015 "boolean_value" NOTNULL AND
|
jbe@532
|
1016 "numeric_value" ISNULL AND
|
jbe@532
|
1017 "text_value" ISNULL AND
|
jbe@532
|
1018 "old_text_value" ISNULL ));
|
jbe@532
|
1019
|
jbe@532
|
1020
|
jbe@534
|
1021 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
|
jbe@534
|
1022 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
|
jbe@534
|
1023
|
jbe@534
|
1024 COMMENT ON TABLE "event_processed" IS 'This table stores one row with the last event_id, for which event handlers have been executed (e.g. notifications having been sent out)';
|
jbe@534
|
1025 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
|
jbe@534
|
1026
|
jbe@534
|
1027
|
jbe@537
|
1028 CREATE FUNCTION "write_event_unit_trigger"()
|
jbe@537
|
1029 RETURNS TRIGGER
|
jbe@537
|
1030 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1031 DECLARE
|
jbe@538
|
1032 "event_v" "event_type";
|
jbe@537
|
1033 BEGIN
|
jbe@538
|
1034 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1035 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1036 RETURN NULL;
|
jbe@547
|
1037 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1038 -- "event_v" := 'unit_created';
|
jbe@547
|
1039 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1040 -- "event_v" := 'unit_deleted';
|
jbe@546
|
1041 ELSIF OLD != NEW THEN
|
jbe@546
|
1042 "event_v" := 'unit_updated';
|
jbe@538
|
1043 ELSE
|
jbe@546
|
1044 RETURN NULL;
|
jbe@538
|
1045 END IF;
|
jbe@538
|
1046 ELSE
|
jbe@538
|
1047 "event_v" := 'unit_created';
|
jbe@538
|
1048 END IF;
|
jbe@538
|
1049 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1050 RETURN NULL;
|
jbe@537
|
1051 END;
|
jbe@537
|
1052 $$;
|
jbe@537
|
1053
|
jbe@537
|
1054 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
|
jbe@537
|
1055 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
|
jbe@537
|
1056
|
jbe@537
|
1057 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
|
jbe@537
|
1058 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
|
jbe@537
|
1059
|
jbe@537
|
1060
|
jbe@537
|
1061 CREATE FUNCTION "write_event_area_trigger"()
|
jbe@537
|
1062 RETURNS TRIGGER
|
jbe@537
|
1063 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1064 DECLARE
|
jbe@538
|
1065 "event_v" "event_type";
|
jbe@537
|
1066 BEGIN
|
jbe@538
|
1067 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1068 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1069 RETURN NULL;
|
jbe@547
|
1070 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1071 -- "event_v" := 'area_created';
|
jbe@547
|
1072 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1073 -- "event_v" := 'area_deleted';
|
jbe@546
|
1074 ELSIF OLD != NEW THEN
|
jbe@546
|
1075 "event_v" := 'area_updated';
|
jbe@538
|
1076 ELSE
|
jbe@546
|
1077 RETURN NULL;
|
jbe@538
|
1078 END IF;
|
jbe@538
|
1079 ELSE
|
jbe@538
|
1080 "event_v" := 'area_created';
|
jbe@538
|
1081 END IF;
|
jbe@538
|
1082 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1083 RETURN NULL;
|
jbe@537
|
1084 END;
|
jbe@537
|
1085 $$;
|
jbe@537
|
1086
|
jbe@537
|
1087 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
|
jbe@537
|
1088 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
|
jbe@537
|
1089
|
jbe@537
|
1090 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
|
jbe@537
|
1091 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
|
jbe@537
|
1092
|
jbe@537
|
1093
|
jbe@537
|
1094 CREATE FUNCTION "write_event_policy_trigger"()
|
jbe@537
|
1095 RETURNS TRIGGER
|
jbe@537
|
1096 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1097 DECLARE
|
jbe@538
|
1098 "event_v" "event_type";
|
jbe@537
|
1099 BEGIN
|
jbe@538
|
1100 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1101 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1102 RETURN NULL;
|
jbe@547
|
1103 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1104 -- "event_v" := 'policy_created';
|
jbe@547
|
1105 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1106 -- "event_v" := 'policy_deleted';
|
jbe@546
|
1107 ELSIF OLD != NEW THEN
|
jbe@546
|
1108 "event_v" := 'policy_updated';
|
jbe@538
|
1109 ELSE
|
jbe@546
|
1110 RETURN NULL;
|
jbe@538
|
1111 END IF;
|
jbe@538
|
1112 ELSE
|
jbe@538
|
1113 "event_v" := 'policy_created';
|
jbe@538
|
1114 END IF;
|
jbe@538
|
1115 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1116 RETURN NULL;
|
jbe@537
|
1117 END;
|
jbe@537
|
1118 $$;
|
jbe@537
|
1119
|
jbe@537
|
1120 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
|
jbe@537
|
1121 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
|
jbe@537
|
1122
|
jbe@537
|
1123 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
|
jbe@537
|
1124 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
|
jbe@537
|
1125
|
jbe@537
|
1126
|
jbe@532
|
1127 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
|
jbe@532
|
1128 RETURNS TRIGGER
|
jbe@532
|
1129 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1130 DECLARE
|
jbe@532
|
1131 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1132 BEGIN
|
jbe@532
|
1133 IF NEW."state" != OLD."state" THEN
|
jbe@532
|
1134 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
|
jbe@532
|
1135 FOR SHARE;
|
jbe@532
|
1136 INSERT INTO "event" (
|
jbe@532
|
1137 "event",
|
jbe@536
|
1138 "unit_id", "area_id", "policy_id", "issue_id", "state"
|
jbe@532
|
1139 ) VALUES (
|
jbe@532
|
1140 'issue_state_changed',
|
jbe@536
|
1141 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
|
jbe@536
|
1142 NEW."id", NEW."state"
|
jbe@532
|
1143 );
|
jbe@532
|
1144 END IF;
|
jbe@532
|
1145 RETURN NULL;
|
jbe@532
|
1146 END;
|
jbe@532
|
1147 $$;
|
jbe@532
|
1148
|
jbe@532
|
1149
|
jbe@532
|
1150 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
|
jbe@532
|
1151 RETURNS TRIGGER
|
jbe@532
|
1152 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1153 DECLARE
|
jbe@532
|
1154 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1155 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1156 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1157 "event_v" "event_type";
|
jbe@532
|
1158 BEGIN
|
jbe@532
|
1159 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1160 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1161 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1162 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1163 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1164 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1165 IF EXISTS (
|
jbe@532
|
1166 SELECT NULL FROM "draft"
|
jbe@532
|
1167 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
|
jbe@532
|
1168 FOR SHARE
|
jbe@532
|
1169 ) THEN
|
jbe@532
|
1170 "event_v" := 'new_draft_created';
|
jbe@532
|
1171 ELSE
|
jbe@532
|
1172 IF EXISTS (
|
jbe@532
|
1173 SELECT NULL FROM "initiative"
|
jbe@532
|
1174 WHERE "issue_id" = "initiative_row"."issue_id"
|
jbe@532
|
1175 AND "id" != "initiative_row"."id"
|
jbe@532
|
1176 FOR SHARE
|
jbe@532
|
1177 ) THEN
|
jbe@532
|
1178 "event_v" := 'initiative_created_in_existing_issue';
|
jbe@532
|
1179 ELSE
|
jbe@532
|
1180 "event_v" := 'initiative_created_in_new_issue';
|
jbe@532
|
1181 END IF;
|
jbe@532
|
1182 END IF;
|
jbe@532
|
1183 INSERT INTO "event" (
|
jbe@532
|
1184 "event", "member_id",
|
jbe@536
|
1185 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1186 "initiative_id", "draft_id"
|
jbe@532
|
1187 ) VALUES (
|
jbe@532
|
1188 "event_v", NEW."author_id",
|
jbe@536
|
1189 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
|
jbe@532
|
1190 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1191 NEW."initiative_id", NEW."id"
|
jbe@532
|
1192 );
|
jbe@532
|
1193 RETURN NULL;
|
jbe@532
|
1194 END;
|
jbe@532
|
1195 $$;
|
jbe@532
|
1196
|
jbe@532
|
1197
|
jbe@532
|
1198 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
|
jbe@532
|
1199 RETURNS TRIGGER
|
jbe@532
|
1200 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1201 DECLARE
|
jbe@532
|
1202 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1203 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1204 "draft_id_v" "draft"."id"%TYPE;
|
jbe@532
|
1205 BEGIN
|
jbe@532
|
1206 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
|
jbe@563
|
1207 -- NOTE: lock for primary key update to avoid new drafts
|
jbe@563
|
1208 SELECT NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
|
jbe@532
|
1209 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1210 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1211 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1212 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@563
|
1213 -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
|
jbe@563
|
1214 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
|
jbe@532
|
1215 SELECT "id" INTO "draft_id_v" FROM "current_draft"
|
jbe@563
|
1216 WHERE "initiative_id" = NEW."id";
|
jbe@532
|
1217 INSERT INTO "event" (
|
jbe@532
|
1218 "event", "member_id",
|
jbe@536
|
1219 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1220 "initiative_id", "draft_id"
|
jbe@532
|
1221 ) VALUES (
|
jbe@532
|
1222 'initiative_revoked', NEW."revoked_by_member_id",
|
jbe@532
|
1223 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1224 "issue_row"."policy_id",
|
jbe@532
|
1225 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1226 NEW."id", "draft_id_v"
|
jbe@532
|
1227 );
|
jbe@532
|
1228 END IF;
|
jbe@532
|
1229 RETURN NULL;
|
jbe@532
|
1230 END;
|
jbe@532
|
1231 $$;
|
jbe@532
|
1232
|
jbe@532
|
1233
|
jbe@532
|
1234 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
|
jbe@532
|
1235 RETURNS TRIGGER
|
jbe@532
|
1236 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1237 DECLARE
|
jbe@532
|
1238 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1239 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1240 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1241 BEGIN
|
jbe@532
|
1242 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1243 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1244 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1245 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1246 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1247 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1248 INSERT INTO "event" (
|
jbe@532
|
1249 "event", "member_id",
|
jbe@536
|
1250 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1251 "initiative_id", "suggestion_id"
|
jbe@532
|
1252 ) VALUES (
|
jbe@532
|
1253 'suggestion_created', NEW."author_id",
|
jbe@536
|
1254 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
|
jbe@532
|
1255 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1256 NEW."initiative_id", NEW."id"
|
jbe@532
|
1257 );
|
jbe@532
|
1258 RETURN NULL;
|
jbe@532
|
1259 END;
|
jbe@532
|
1260 $$;
|
jbe@532
|
1261
|
jbe@532
|
1262
|
jbe@532
|
1263 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
|
jbe@532
|
1264 RETURNS TRIGGER
|
jbe@532
|
1265 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1266 DECLARE
|
jbe@532
|
1267 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1268 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1269 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1270 BEGIN
|
jbe@532
|
1271 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1272 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1273 IF "initiative_row"."id" NOTNULL THEN
|
jbe@532
|
1274 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1275 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1276 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1277 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1278 INSERT INTO "event" (
|
jbe@532
|
1279 "event",
|
jbe@536
|
1280 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1281 "initiative_id", "suggestion_id"
|
jbe@532
|
1282 ) VALUES (
|
jbe@554
|
1283 'suggestion_deleted',
|
jbe@532
|
1284 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1285 "issue_row"."policy_id",
|
jbe@532
|
1286 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1287 OLD."initiative_id", OLD."id"
|
jbe@532
|
1288 );
|
jbe@532
|
1289 END IF;
|
jbe@532
|
1290 RETURN NULL;
|
jbe@532
|
1291 END;
|
jbe@532
|
1292 $$;
|
jbe@532
|
1293
|
jbe@532
|
1294 CREATE TRIGGER "write_event_suggestion_removed"
|
jbe@532
|
1295 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1296 "write_event_suggestion_removed_trigger"();
|
jbe@532
|
1297
|
jbe@532
|
1298 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
|
jbe@532
|
1299 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
|
jbe@532
|
1300
|
jbe@532
|
1301
|
jbe@532
|
1302 CREATE FUNCTION "write_event_member_trigger"()
|
jbe@532
|
1303 RETURNS TRIGGER
|
jbe@532
|
1304 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1305 BEGIN
|
jbe@532
|
1306 IF TG_OP = 'INSERT' THEN
|
jbe@552
|
1307 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
|
jbe@532
|
1308 INSERT INTO "event" ("event", "member_id")
|
jbe@532
|
1309 VALUES ('member_activated', NEW."id");
|
jbe@532
|
1310 END IF;
|
jbe@532
|
1311 IF NEW."active" THEN
|
jbe@532
|
1312 INSERT INTO "event" ("event", "member_id", "boolean_value")
|
jbe@532
|
1313 VALUES ('member_active', NEW."id", TRUE);
|
jbe@532
|
1314 END IF;
|
jbe@532
|
1315 ELSIF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1316 IF OLD."id" != NEW."id" THEN
|
jbe@532
|
1317 RAISE EXCEPTION 'Cannot change member ID';
|
jbe@532
|
1318 END IF;
|
jbe@552
|
1319 IF
|
jbe@552
|
1320 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
|
jbe@552
|
1321 NEW."activated" NOTNULL AND NEW."deleted" ISNULL
|
jbe@552
|
1322 THEN
|
jbe@552
|
1323 INSERT INTO "event" ("event", "member_id")
|
jbe@552
|
1324 VALUES ('member_activated', NEW."id");
|
jbe@552
|
1325 END IF;
|
jbe@552
|
1326 IF OLD."active" != NEW."active" THEN
|
jbe@552
|
1327 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
|
jbe@552
|
1328 'member_active', NEW."id", NEW."active"
|
jbe@552
|
1329 );
|
jbe@552
|
1330 END IF;
|
jbe@532
|
1331 IF OLD."name" != NEW."name" THEN
|
jbe@532
|
1332 INSERT INTO "event" (
|
jbe@532
|
1333 "event", "member_id", "text_value", "old_text_value"
|
jbe@532
|
1334 ) VALUES (
|
jbe@532
|
1335 'member_name_updated', NEW."id", NEW."name", OLD."name"
|
jbe@532
|
1336 );
|
jbe@532
|
1337 END IF;
|
jbe@532
|
1338 IF
|
jbe@552
|
1339 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
|
jbe@552
|
1340 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
|
jbe@532
|
1341 THEN
|
jbe@532
|
1342 INSERT INTO "event" ("event", "member_id")
|
jbe@554
|
1343 VALUES ('member_deleted', NEW."id");
|
jbe@532
|
1344 END IF;
|
jbe@532
|
1345 END IF;
|
jbe@532
|
1346 RETURN NULL;
|
jbe@532
|
1347 END;
|
jbe@532
|
1348 $$;
|
jbe@532
|
1349
|
jbe@532
|
1350 CREATE TRIGGER "write_event_member"
|
jbe@532
|
1351 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1352 "write_event_member_trigger"();
|
jbe@532
|
1353
|
jbe@532
|
1354 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
|
jbe@532
|
1355 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
|
jbe@532
|
1356
|
jbe@532
|
1357
|
jbe@532
|
1358 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
|
jbe@532
|
1359 RETURNS TRIGGER
|
jbe@532
|
1360 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1361 BEGIN
|
jbe@532
|
1362 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1363 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
|
jbe@532
|
1364 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1365 'member_profile_updated', OLD."member_id"
|
jbe@532
|
1366 );
|
jbe@532
|
1367 END IF;
|
jbe@532
|
1368 END IF;
|
jbe@532
|
1369 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1370 IF OLD."member_id" = NEW."member_id" THEN
|
jbe@532
|
1371 RETURN NULL;
|
jbe@532
|
1372 END IF;
|
jbe@532
|
1373 END IF;
|
jbe@532
|
1374 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1375 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1376 'member_profile_updated', NEW."member_id"
|
jbe@532
|
1377 );
|
jbe@532
|
1378 END IF;
|
jbe@532
|
1379 RETURN NULL;
|
jbe@532
|
1380 END;
|
jbe@532
|
1381 $$;
|
jbe@532
|
1382
|
jbe@532
|
1383 CREATE TRIGGER "write_event_member_profile_updated"
|
jbe@532
|
1384 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
|
jbe@532
|
1385 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1386 "write_event_member_profile_updated_trigger"();
|
jbe@532
|
1387
|
jbe@532
|
1388 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
|
jbe@532
|
1389 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
|
jbe@532
|
1390
|
jbe@532
|
1391
|
jbe@532
|
1392 CREATE FUNCTION "write_event_member_image_updated_trigger"()
|
jbe@532
|
1393 RETURNS TRIGGER
|
jbe@532
|
1394 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1395 BEGIN
|
jbe@532
|
1396 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1397 IF NOT OLD."scaled" THEN
|
jbe@532
|
1398 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
|
jbe@532
|
1399 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1400 'member_image_updated', OLD."member_id"
|
jbe@532
|
1401 );
|
jbe@532
|
1402 END IF;
|
jbe@532
|
1403 END IF;
|
jbe@532
|
1404 END IF;
|
jbe@532
|
1405 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1406 IF
|
jbe@532
|
1407 OLD."member_id" = NEW."member_id" AND
|
jbe@532
|
1408 OLD."scaled" = NEW."scaled"
|
jbe@532
|
1409 THEN
|
jbe@532
|
1410 RETURN NULL;
|
jbe@532
|
1411 END IF;
|
jbe@532
|
1412 END IF;
|
jbe@532
|
1413 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1414 IF NOT NEW."scaled" THEN
|
jbe@532
|
1415 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1416 'member_image_updated', NEW."member_id"
|
jbe@532
|
1417 );
|
jbe@532
|
1418 END IF;
|
jbe@532
|
1419 END IF;
|
jbe@532
|
1420 RETURN NULL;
|
jbe@532
|
1421 END;
|
jbe@532
|
1422 $$;
|
jbe@532
|
1423
|
jbe@532
|
1424 CREATE TRIGGER "write_event_member_image_updated"
|
jbe@532
|
1425 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
|
jbe@532
|
1426 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1427 "write_event_member_image_updated_trigger"();
|
jbe@532
|
1428
|
jbe@532
|
1429 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
|
jbe@532
|
1430 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
|
jbe@532
|
1431
|
jbe@532
|
1432
|
jbe@532
|
1433 CREATE FUNCTION "write_event_interest_trigger"()
|
jbe@532
|
1434 RETURNS TRIGGER
|
jbe@532
|
1435 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1436 DECLARE
|
jbe@532
|
1437 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1438 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1439 BEGIN
|
jbe@532
|
1440 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1441 IF OLD = NEW THEN
|
jbe@532
|
1442 RETURN NULL;
|
jbe@532
|
1443 END IF;
|
jbe@532
|
1444 END IF;
|
jbe@532
|
1445 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1446 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1447 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1448 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1449 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1450 IF "issue_row"."id" NOTNULL THEN
|
jbe@532
|
1451 INSERT INTO "event" (
|
jbe@532
|
1452 "event", "member_id",
|
jbe@536
|
1453 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1454 "boolean_value"
|
jbe@532
|
1455 ) VALUES (
|
jbe@532
|
1456 'interest', OLD."member_id",
|
jbe@532
|
1457 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1458 "issue_row"."policy_id",
|
jbe@532
|
1459 OLD."issue_id", "issue_row"."state",
|
jbe@532
|
1460 FALSE
|
jbe@532
|
1461 );
|
jbe@532
|
1462 END IF;
|
jbe@532
|
1463 END IF;
|
jbe@532
|
1464 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1465 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1466 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1467 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1468 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1469 INSERT INTO "event" (
|
jbe@532
|
1470 "event", "member_id",
|
jbe@536
|
1471 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1472 "boolean_value"
|
jbe@532
|
1473 ) VALUES (
|
jbe@532
|
1474 'interest', NEW."member_id",
|
jbe@532
|
1475 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1476 "issue_row"."policy_id",
|
jbe@532
|
1477 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1478 TRUE
|
jbe@532
|
1479 );
|
jbe@532
|
1480 END IF;
|
jbe@532
|
1481 RETURN NULL;
|
jbe@532
|
1482 END;
|
jbe@532
|
1483 $$;
|
jbe@532
|
1484
|
jbe@532
|
1485 CREATE TRIGGER "write_event_interest"
|
jbe@532
|
1486 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1487 "write_event_interest_trigger"();
|
jbe@532
|
1488
|
jbe@532
|
1489 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
|
jbe@532
|
1490 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
|
jbe@532
|
1491
|
jbe@532
|
1492
|
jbe@532
|
1493 CREATE FUNCTION "write_event_initiator_trigger"()
|
jbe@532
|
1494 RETURNS TRIGGER
|
jbe@532
|
1495 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1496 DECLARE
|
jbe@532
|
1497 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1498 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1499 "area_row" "area"%ROWTYPE;
|
jbe@549
|
1500 "accepted_v" BOOLEAN = FALSE;
|
jbe@549
|
1501 "rejected_v" BOOLEAN = FALSE;
|
jbe@532
|
1502 BEGIN
|
jbe@532
|
1503 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1504 IF
|
jbe@532
|
1505 OLD."initiative_id" = NEW."initiative_id" AND
|
jbe@549
|
1506 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1507 THEN
|
jbe@549
|
1508 IF
|
jbe@549
|
1509 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
|
jbe@549
|
1510 THEN
|
jbe@549
|
1511 RETURN NULL;
|
jbe@549
|
1512 END IF;
|
jbe@549
|
1513 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
|
jbe@549
|
1514 "accepted_v" := TRUE;
|
jbe@549
|
1515 ELSE
|
jbe@549
|
1516 "rejected_v" := TRUE;
|
jbe@549
|
1517 END IF;
|
jbe@532
|
1518 END IF;
|
jbe@532
|
1519 END IF;
|
jbe@532
|
1520 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
|
jbe@532
|
1521 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
|
jbe@532
|
1522 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1523 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1524 IF "initiative_row"."id" NOTNULL THEN
|
jbe@532
|
1525 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1526 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1527 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1528 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1529 INSERT INTO "event" (
|
jbe@532
|
1530 "event", "member_id",
|
jbe@536
|
1531 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1532 "initiative_id", "boolean_value"
|
jbe@532
|
1533 ) VALUES (
|
jbe@532
|
1534 'initiator', OLD."member_id",
|
jbe@532
|
1535 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1536 "issue_row"."policy_id",
|
jbe@532
|
1537 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1538 OLD."initiative_id", FALSE
|
jbe@532
|
1539 );
|
jbe@532
|
1540 END IF;
|
jbe@532
|
1541 END IF;
|
jbe@532
|
1542 END IF;
|
jbe@532
|
1543 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
|
jbe@532
|
1544 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
|
jbe@532
|
1545 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1546 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1547 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1548 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1549 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1550 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1551 INSERT INTO "event" (
|
jbe@532
|
1552 "event", "member_id",
|
jbe@536
|
1553 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1554 "initiative_id", "boolean_value"
|
jbe@532
|
1555 ) VALUES (
|
jbe@532
|
1556 'initiator', NEW."member_id",
|
jbe@532
|
1557 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1558 "issue_row"."policy_id",
|
jbe@532
|
1559 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1560 NEW."initiative_id", TRUE
|
jbe@532
|
1561 );
|
jbe@532
|
1562 END IF;
|
jbe@532
|
1563 END IF;
|
jbe@532
|
1564 RETURN NULL;
|
jbe@532
|
1565 END;
|
jbe@532
|
1566 $$;
|
jbe@532
|
1567
|
jbe@532
|
1568 CREATE TRIGGER "write_event_initiator"
|
jbe@532
|
1569 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1570 "write_event_initiator_trigger"();
|
jbe@532
|
1571
|
jbe@532
|
1572 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
|
jbe@532
|
1573 COMMENT ON TRIGGER "write_event_initiator" ON "initiator" IS 'Create entry in "event" table when accepting or removing initiatorship (NOTE: trigger does not fire on INSERT to avoid events on initiative creation)';
|
jbe@532
|
1574
|
jbe@532
|
1575
|
jbe@532
|
1576 CREATE FUNCTION "write_event_support_trigger"()
|
jbe@532
|
1577 RETURNS TRIGGER
|
jbe@532
|
1578 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1579 DECLARE
|
jbe@532
|
1580 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1581 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1582 BEGIN
|
jbe@532
|
1583 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1584 IF
|
jbe@532
|
1585 OLD."initiative_id" = NEW."initiative_id" AND
|
jbe@532
|
1586 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1587 THEN
|
jbe@532
|
1588 IF OLD."draft_id" != NEW."draft_id" THEN
|
jbe@532
|
1589 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1590 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1591 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1592 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1593 INSERT INTO "event" (
|
jbe@532
|
1594 "event", "member_id",
|
jbe@536
|
1595 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1596 "initiative_id", "draft_id"
|
jbe@532
|
1597 ) VALUES (
|
jbe@532
|
1598 'support_updated', NEW."member_id",
|
jbe@532
|
1599 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1600 "issue_row"."policy_id",
|
jbe@532
|
1601 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1602 NEW."initiative_id", NEW."draft_id"
|
jbe@532
|
1603 );
|
jbe@532
|
1604 END IF;
|
jbe@532
|
1605 RETURN NULL;
|
jbe@532
|
1606 END IF;
|
jbe@532
|
1607 END IF;
|
jbe@532
|
1608 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1609 IF EXISTS (
|
jbe@532
|
1610 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
|
jbe@532
|
1611 FOR SHARE
|
jbe@532
|
1612 ) THEN
|
jbe@532
|
1613 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1614 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1615 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1616 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1617 INSERT INTO "event" (
|
jbe@532
|
1618 "event", "member_id",
|
jbe@536
|
1619 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@535
|
1620 "initiative_id", "boolean_value"
|
jbe@532
|
1621 ) VALUES (
|
jbe@532
|
1622 'support', OLD."member_id",
|
jbe@532
|
1623 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1624 "issue_row"."policy_id",
|
jbe@532
|
1625 "issue_row"."id", "issue_row"."state",
|
jbe@535
|
1626 OLD."initiative_id", FALSE
|
jbe@532
|
1627 );
|
jbe@532
|
1628 END IF;
|
jbe@532
|
1629 END IF;
|
jbe@532
|
1630 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1631 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1632 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1633 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1634 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1635 INSERT INTO "event" (
|
jbe@532
|
1636 "event", "member_id",
|
jbe@536
|
1637 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1638 "initiative_id", "draft_id", "boolean_value"
|
jbe@532
|
1639 ) VALUES (
|
jbe@532
|
1640 'support', NEW."member_id",
|
jbe@532
|
1641 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1642 "issue_row"."policy_id",
|
jbe@532
|
1643 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1644 NEW."initiative_id", NEW."draft_id", TRUE
|
jbe@532
|
1645 );
|
jbe@532
|
1646 END IF;
|
jbe@532
|
1647 RETURN NULL;
|
jbe@532
|
1648 END;
|
jbe@532
|
1649 $$;
|
jbe@532
|
1650
|
jbe@532
|
1651 CREATE TRIGGER "write_event_support"
|
jbe@532
|
1652 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1653 "write_event_support_trigger"();
|
jbe@532
|
1654
|
jbe@532
|
1655 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
|
jbe@532
|
1656 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
|
jbe@532
|
1657
|
jbe@532
|
1658
|
jbe@532
|
1659 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
|
jbe@532
|
1660 RETURNS TRIGGER
|
jbe@532
|
1661 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1662 DECLARE
|
jbe@532
|
1663 "same_pkey_v" BOOLEAN = FALSE;
|
jbe@532
|
1664 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1665 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1666 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1667 BEGIN
|
jbe@532
|
1668 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1669 IF
|
jbe@532
|
1670 OLD."suggestion_id" = NEW."suggestion_id" AND
|
jbe@532
|
1671 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1672 THEN
|
jbe@532
|
1673 IF
|
jbe@532
|
1674 OLD."degree" = NEW."degree" AND
|
jbe@532
|
1675 OLD."fulfilled" = NEW."fulfilled"
|
jbe@532
|
1676 THEN
|
jbe@532
|
1677 RETURN NULL;
|
jbe@532
|
1678 END IF;
|
jbe@532
|
1679 "same_pkey_v" := TRUE;
|
jbe@532
|
1680 END IF;
|
jbe@532
|
1681 END IF;
|
jbe@532
|
1682 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
|
jbe@532
|
1683 IF EXISTS (
|
jbe@532
|
1684 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
|
jbe@532
|
1685 FOR SHARE
|
jbe@532
|
1686 ) THEN
|
jbe@532
|
1687 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1688 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1689 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1690 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1691 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1692 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1693 INSERT INTO "event" (
|
jbe@532
|
1694 "event", "member_id",
|
jbe@536
|
1695 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1696 "initiative_id", "suggestion_id",
|
jbe@532
|
1697 "boolean_value", "numeric_value"
|
jbe@532
|
1698 ) VALUES (
|
jbe@532
|
1699 'suggestion_rated', OLD."member_id",
|
jbe@532
|
1700 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1701 "issue_row"."policy_id",
|
jbe@532
|
1702 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1703 OLD."initiative_id", OLD."suggestion_id",
|
jbe@532
|
1704 NULL, 0
|
jbe@532
|
1705 );
|
jbe@532
|
1706 END IF;
|
jbe@532
|
1707 END IF;
|
jbe@532
|
1708 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1709 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1710 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1711 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1712 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1713 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1714 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1715 INSERT INTO "event" (
|
jbe@532
|
1716 "event", "member_id",
|
jbe@536
|
1717 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1718 "initiative_id", "suggestion_id",
|
jbe@532
|
1719 "boolean_value", "numeric_value"
|
jbe@532
|
1720 ) VALUES (
|
jbe@532
|
1721 'suggestion_rated', NEW."member_id",
|
jbe@532
|
1722 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1723 "issue_row"."policy_id",
|
jbe@532
|
1724 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1725 NEW."initiative_id", NEW."suggestion_id",
|
jbe@532
|
1726 NEW."fulfilled", NEW."degree"
|
jbe@532
|
1727 );
|
jbe@532
|
1728 END IF;
|
jbe@532
|
1729 RETURN NULL;
|
jbe@532
|
1730 END;
|
jbe@532
|
1731 $$;
|
jbe@532
|
1732
|
jbe@532
|
1733 CREATE TRIGGER "write_event_suggestion_rated"
|
jbe@532
|
1734 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1735 "write_event_suggestion_rated_trigger"();
|
jbe@532
|
1736
|
jbe@532
|
1737 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
|
jbe@532
|
1738 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
|
jbe@532
|
1739
|
jbe@532
|
1740
|
jbe@532
|
1741 CREATE FUNCTION "write_event_delegation_trigger"()
|
jbe@532
|
1742 RETURNS TRIGGER
|
jbe@532
|
1743 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1744 DECLARE
|
jbe@532
|
1745 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1746 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1747 BEGIN
|
jbe@532
|
1748 IF TG_OP = 'DELETE' THEN
|
jbe@532
|
1749 IF EXISTS (
|
jbe@532
|
1750 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
|
jbe@532
|
1751 ) AND (CASE OLD."scope"
|
jbe@532
|
1752 WHEN 'unit'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1753 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
|
jbe@532
|
1754 )
|
jbe@532
|
1755 WHEN 'area'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1756 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
|
jbe@532
|
1757 )
|
jbe@532
|
1758 WHEN 'issue'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1759 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
|
jbe@532
|
1760 )
|
jbe@532
|
1761 END) THEN
|
jbe@532
|
1762 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1763 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1764 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1765 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
|
jbe@532
|
1766 FOR SHARE;
|
jbe@532
|
1767 INSERT INTO "event" (
|
jbe@532
|
1768 "event", "member_id", "scope",
|
jbe@532
|
1769 "unit_id", "area_id", "issue_id", "state",
|
jbe@532
|
1770 "boolean_value"
|
jbe@532
|
1771 ) VALUES (
|
jbe@532
|
1772 'delegation', OLD."truster_id", OLD."scope",
|
jbe@532
|
1773 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
|
jbe@532
|
1774 OLD."issue_id", "issue_row"."state",
|
jbe@532
|
1775 FALSE
|
jbe@532
|
1776 );
|
jbe@532
|
1777 END IF;
|
jbe@532
|
1778 ELSE
|
jbe@532
|
1779 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1780 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1781 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1782 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
|
jbe@532
|
1783 FOR SHARE;
|
jbe@532
|
1784 INSERT INTO "event" (
|
jbe@532
|
1785 "event", "member_id", "other_member_id", "scope",
|
jbe@532
|
1786 "unit_id", "area_id", "issue_id", "state",
|
jbe@532
|
1787 "boolean_value"
|
jbe@532
|
1788 ) VALUES (
|
jbe@532
|
1789 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
|
jbe@532
|
1790 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
|
jbe@532
|
1791 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1792 TRUE
|
jbe@532
|
1793 );
|
jbe@532
|
1794 END IF;
|
jbe@532
|
1795 RETURN NULL;
|
jbe@532
|
1796 END;
|
jbe@532
|
1797 $$;
|
jbe@532
|
1798
|
jbe@532
|
1799 CREATE TRIGGER "write_event_delegation"
|
jbe@532
|
1800 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1801 "write_event_delegation_trigger"();
|
jbe@532
|
1802
|
jbe@532
|
1803 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
|
jbe@532
|
1804 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
|
jbe@532
|
1805
|
jbe@532
|
1806
|
jbe@532
|
1807 CREATE FUNCTION "write_event_contact_trigger"()
|
jbe@532
|
1808 RETURNS TRIGGER
|
jbe@532
|
1809 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1810 BEGIN
|
jbe@532
|
1811 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1812 IF
|
jbe@532
|
1813 OLD."member_id" = NEW."member_id" AND
|
jbe@532
|
1814 OLD."other_member_id" = NEW."other_member_id" AND
|
jbe@532
|
1815 OLD."public" = NEW."public"
|
jbe@532
|
1816 THEN
|
jbe@532
|
1817 RETURN NULL;
|
jbe@532
|
1818 END IF;
|
jbe@532
|
1819 END IF;
|
jbe@532
|
1820 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1821 IF OLD."public" THEN
|
jbe@532
|
1822 IF EXISTS (
|
jbe@532
|
1823 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
|
jbe@532
|
1824 FOR SHARE
|
jbe@532
|
1825 ) AND EXISTS (
|
jbe@532
|
1826 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
|
jbe@532
|
1827 FOR SHARE
|
jbe@532
|
1828 ) THEN
|
jbe@532
|
1829 INSERT INTO "event" (
|
jbe@532
|
1830 "event", "member_id", "other_member_id", "boolean_value"
|
jbe@532
|
1831 ) VALUES (
|
jbe@532
|
1832 'contact', OLD."member_id", OLD."other_member_id", FALSE
|
jbe@532
|
1833 );
|
jbe@532
|
1834 END IF;
|
jbe@532
|
1835 END IF;
|
jbe@532
|
1836 END IF;
|
jbe@532
|
1837 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1838 IF NEW."public" THEN
|
jbe@532
|
1839 INSERT INTO "event" (
|
jbe@532
|
1840 "event", "member_id", "other_member_id", "boolean_value"
|
jbe@532
|
1841 ) VALUES (
|
jbe@532
|
1842 'contact', NEW."member_id", NEW."other_member_id", TRUE
|
jbe@532
|
1843 );
|
jbe@532
|
1844 END IF;
|
jbe@532
|
1845 END IF;
|
jbe@532
|
1846 RETURN NULL;
|
jbe@532
|
1847 END;
|
jbe@532
|
1848 $$;
|
jbe@532
|
1849
|
jbe@532
|
1850 CREATE TRIGGER "write_event_contact"
|
jbe@532
|
1851 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1852 "write_event_contact_trigger"();
|
jbe@532
|
1853
|
jbe@532
|
1854 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
|
jbe@532
|
1855 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
|
jbe@532
|
1856
|
jbe@532
|
1857
|
jbe@532
|
1858 CREATE FUNCTION "send_event_notify_trigger"()
|
jbe@532
|
1859 RETURNS TRIGGER
|
jbe@532
|
1860 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1861 BEGIN
|
jbe@532
|
1862 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
|
jbe@532
|
1863 RETURN NULL;
|
jbe@532
|
1864 END;
|
jbe@532
|
1865 $$;
|
jbe@532
|
1866
|
jbe@532
|
1867 CREATE TRIGGER "send_notify"
|
jbe@532
|
1868 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1869 "send_event_notify_trigger"();
|
jbe@532
|
1870
|
jbe@532
|
1871
|
jbe@532
|
1872 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
|
jbe@532
|
1873 RETURNS TRIGGER
|
jbe@532
|
1874 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1875 DECLARE
|
jbe@532
|
1876 "system_application_row" "system_application"%ROWTYPE;
|
jbe@532
|
1877 BEGIN
|
jbe@532
|
1878 IF OLD."system_application_id" NOTNULL THEN
|
jbe@532
|
1879 SELECT * FROM "system_application" INTO "system_application_row"
|
jbe@532
|
1880 WHERE "id" = OLD."system_application_id";
|
jbe@532
|
1881 DELETE FROM "token"
|
jbe@532
|
1882 WHERE "member_id" = OLD."member_id"
|
jbe@532
|
1883 AND "system_application_id" = OLD."system_application_id"
|
jbe@532
|
1884 AND NOT COALESCE(
|
jbe@532
|
1885 regexp_split_to_array("scope", E'\\s+') <@
|
jbe@532
|
1886 regexp_split_to_array(
|
jbe@532
|
1887 "system_application_row"."automatic_scope", E'\\s+'
|
jbe@532
|
1888 ),
|
jbe@532
|
1889 FALSE
|
jbe@532
|
1890 );
|
jbe@532
|
1891 END IF;
|
jbe@532
|
1892 RETURN OLD;
|
jbe@532
|
1893 END;
|
jbe@532
|
1894 $$;
|
jbe@532
|
1895
|
jbe@532
|
1896 CREATE TRIGGER "delete_extended_scope_tokens"
|
jbe@532
|
1897 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1898 "delete_extended_scope_tokens_trigger"();
|
jbe@532
|
1899
|
jbe@532
|
1900
|
jbe@532
|
1901 CREATE FUNCTION "detach_token_from_session_trigger"()
|
jbe@532
|
1902 RETURNS TRIGGER
|
jbe@532
|
1903 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1904 BEGIN
|
jbe@532
|
1905 UPDATE "token" SET "session_id" = NULL
|
jbe@532
|
1906 WHERE "session_id" = OLD."id";
|
jbe@532
|
1907 RETURN OLD;
|
jbe@532
|
1908 END;
|
jbe@532
|
1909 $$;
|
jbe@532
|
1910
|
jbe@532
|
1911 CREATE TRIGGER "detach_token_from_session"
|
jbe@532
|
1912 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1913 "detach_token_from_session_trigger"();
|
jbe@532
|
1914
|
jbe@532
|
1915
|
jbe@532
|
1916 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
|
jbe@532
|
1917 RETURNS TRIGGER
|
jbe@532
|
1918 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1919 BEGIN
|
jbe@532
|
1920 IF NEW."session_id" ISNULL THEN
|
jbe@532
|
1921 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
|
jbe@532
|
1922 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
|
jbe@532
|
1923 WHERE "element" LIKE '%_detached';
|
jbe@532
|
1924 END IF;
|
jbe@532
|
1925 RETURN NEW;
|
jbe@532
|
1926 END;
|
jbe@532
|
1927 $$;
|
jbe@532
|
1928
|
jbe@532
|
1929 CREATE TRIGGER "delete_non_detached_scope_with_session"
|
jbe@532
|
1930 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1931 "delete_non_detached_scope_with_session_trigger"();
|
jbe@532
|
1932
|
jbe@532
|
1933
|
jbe@532
|
1934 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
|
jbe@532
|
1935 RETURNS TRIGGER
|
jbe@532
|
1936 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1937 BEGIN
|
jbe@532
|
1938 IF NEW."scope" = '' THEN
|
jbe@532
|
1939 DELETE FROM "token" WHERE "id" = NEW."id";
|
jbe@532
|
1940 END IF;
|
jbe@532
|
1941 RETURN NULL;
|
jbe@532
|
1942 END;
|
jbe@532
|
1943 $$;
|
jbe@532
|
1944
|
jbe@532
|
1945 CREATE TRIGGER "delete_token_with_empty_scope"
|
jbe@532
|
1946 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1947 "delete_token_with_empty_scope_trigger"();
|
jbe@532
|
1948
|
jbe@532
|
1949
|
jbe@532
|
1950 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
|
jbe@532
|
1951 RETURNS TRIGGER
|
jbe@532
|
1952 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1953 BEGIN
|
jbe@532
|
1954 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1955 IF
|
jbe@532
|
1956 OLD."snapshot_id" = NEW."snapshot_id" AND
|
jbe@532
|
1957 OLD."issue_id" = NEW."issue_id"
|
jbe@532
|
1958 THEN
|
jbe@532
|
1959 RETURN NULL;
|
jbe@532
|
1960 END IF;
|
jbe@532
|
1961 END IF;
|
jbe@532
|
1962 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
|
jbe@532
|
1963 RETURN NULL;
|
jbe@532
|
1964 END;
|
jbe@532
|
1965 $$;
|
jbe@532
|
1966
|
jbe@532
|
1967 CREATE TRIGGER "delete_snapshot_on_partial_delete"
|
jbe@532
|
1968 AFTER UPDATE OR DELETE ON "snapshot_issue"
|
jbe@532
|
1969 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1970 "delete_snapshot_on_partial_delete_trigger"();
|
jbe@532
|
1971
|
jbe@532
|
1972 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
|
jbe@532
|
1973 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
|
jbe@532
|
1974
|
jbe@532
|
1975
|
jbe@532
|
1976 CREATE FUNCTION "copy_current_draft_data"
|
jbe@532
|
1977 ("initiative_id_p" "initiative"."id"%TYPE )
|
jbe@532
|
1978 RETURNS VOID
|
jbe@532
|
1979 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1980 BEGIN
|
jbe@532
|
1981 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
|
jbe@532
|
1982 FOR UPDATE;
|
jbe@532
|
1983 UPDATE "initiative" SET
|
jbe@532
|
1984 "location" = "draft"."location",
|
jbe@532
|
1985 "draft_text_search_data" = "draft"."text_search_data"
|
jbe@532
|
1986 FROM "current_draft" AS "draft"
|
jbe@532
|
1987 WHERE "initiative"."id" = "initiative_id_p"
|
jbe@532
|
1988 AND "draft"."initiative_id" = "initiative_id_p";
|
jbe@532
|
1989 END;
|
jbe@532
|
1990 $$;
|
jbe@532
|
1991
|
jbe@532
|
1992 COMMENT ON FUNCTION "copy_current_draft_data"
|
jbe@532
|
1993 ( "initiative"."id"%TYPE )
|
jbe@532
|
1994 IS 'Helper function for function "copy_current_draft_data_trigger"';
|
jbe@532
|
1995
|
jbe@532
|
1996
|
jbe@532
|
1997 CREATE FUNCTION "copy_current_draft_data_trigger"()
|
jbe@532
|
1998 RETURNS TRIGGER
|
jbe@532
|
1999 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2000 BEGIN
|
jbe@532
|
2001 IF TG_OP='DELETE' THEN
|
jbe@532
|
2002 PERFORM "copy_current_draft_data"(OLD."initiative_id");
|
jbe@532
|
2003 ELSE
|
jbe@532
|
2004 IF TG_OP='UPDATE' THEN
|
jbe@532
|
2005 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
|
jbe@532
|
2006 PERFORM "copy_current_draft_data"(OLD."initiative_id");
|
jbe@532
|
2007 END IF;
|
jbe@532
|
2008 END IF;
|
jbe@532
|
2009 PERFORM "copy_current_draft_data"(NEW."initiative_id");
|
jbe@532
|
2010 END IF;
|
jbe@532
|
2011 RETURN NULL;
|
jbe@532
|
2012 END;
|
jbe@532
|
2013 $$;
|
jbe@532
|
2014
|
jbe@532
|
2015 CREATE TRIGGER "copy_current_draft_data"
|
jbe@532
|
2016 AFTER INSERT OR UPDATE OR DELETE ON "draft"
|
jbe@532
|
2017 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
2018 "copy_current_draft_data_trigger"();
|
jbe@532
|
2019
|
jbe@532
|
2020 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
|
jbe@532
|
2021 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
|
jbe@532
|
2022
|
jbe@532
|
2023
|
jbe@532
|
2024 CREATE VIEW "area_quorum" AS
|
jbe@532
|
2025 SELECT
|
jbe@532
|
2026 "area"."id" AS "area_id",
|
jbe@532
|
2027 ceil(
|
jbe@532
|
2028 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
|
jbe@532
|
2029 coalesce(
|
jbe@532
|
2030 ( SELECT sum(
|
jbe@532
|
2031 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
|
jbe@532
|
2032 extract(epoch from
|
jbe@532
|
2033 ("issue"."accepted"-"issue"."created") +
|
jbe@532
|
2034 "issue"."discussion_time" +
|
jbe@532
|
2035 "issue"."verification_time" +
|
jbe@532
|
2036 "issue"."voting_time"
|
jbe@532
|
2037 )::FLOAT8
|
jbe@532
|
2038 ) ^ "area"."quorum_exponent"::FLOAT8
|
jbe@532
|
2039 )
|
jbe@532
|
2040 FROM "issue" JOIN "policy"
|
jbe@532
|
2041 ON "issue"."policy_id" = "policy"."id"
|
jbe@532
|
2042 WHERE "issue"."area_id" = "area"."id"
|
jbe@532
|
2043 AND "issue"."accepted" NOTNULL
|
jbe@532
|
2044 AND "issue"."closed" ISNULL
|
jbe@532
|
2045 AND "policy"."polling" = FALSE
|
jbe@532
|
2046 )::FLOAT8, 0::FLOAT8
|
jbe@532
|
2047 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
|
jbe@532
|
2048 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
|
jbe@532
|
2049 SELECT "snapshot"."population"
|
jbe@532
|
2050 FROM "snapshot"
|
jbe@532
|
2051 WHERE "snapshot"."area_id" = "area"."id"
|
jbe@532
|
2052 AND "snapshot"."issue_id" ISNULL
|
jbe@532
|
2053 ORDER BY "snapshot"."id" DESC
|
jbe@532
|
2054 LIMIT 1
|
jbe@532
|
2055 ) END / coalesce("area"."quorum_den", 1)
|
jbe@532
|
2056
|
jbe@532
|
2057 )::INT4 AS "issue_quorum"
|
jbe@532
|
2058 FROM "area";
|
jbe@532
|
2059
|
jbe@532
|
2060 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
|
jbe@532
|
2061
|
jbe@532
|
2062
|
jbe@568
|
2063 CREATE VIEW "issue_quorum" AS
|
jbe@568
|
2064 SELECT DISTINCT ON ("issue_id")
|
jbe@568
|
2065 "issue"."id" AS "issue_id",
|
jbe@568
|
2066 "subquery"."issue_quorum"
|
jbe@568
|
2067 FROM "issue"
|
jbe@568
|
2068 CROSS JOIN LATERAL (
|
jbe@568
|
2069 SELECT "area_quorum"."issue_quorum"
|
jbe@568
|
2070 FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id"
|
jbe@568
|
2071 UNION ALL
|
jbe@568
|
2072 SELECT "policy"."issue_quorum"
|
jbe@568
|
2073 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
|
jbe@568
|
2074 UNION ALL
|
jbe@568
|
2075 SELECT
|
jbe@568
|
2076 ceil(
|
jbe@568
|
2077 ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /
|
jbe@568
|
2078 "policy"."issue_quorum_den"::FLOAT8
|
jbe@568
|
2079 )::INT4
|
jbe@568
|
2080 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
|
jbe@568
|
2081 ) AS "subquery"
|
jbe@568
|
2082 ORDER BY "issue_id", "issue_quorum" DESC;
|
jbe@568
|
2083
|
jbe@568
|
2084 COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission';
|
jbe@568
|
2085
|
jbe@568
|
2086
|
jbe@532
|
2087 CREATE VIEW "area_with_unaccepted_issues" AS
|
jbe@532
|
2088 SELECT DISTINCT ON ("area"."id") "area".*
|
jbe@532
|
2089 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2090 WHERE "issue"."state" = 'admission';
|
jbe@532
|
2091
|
jbe@532
|
2092 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
|
jbe@532
|
2093
|
jbe@532
|
2094
|
jbe@532
|
2095 DROP VIEW "area_member_count";
|
jbe@532
|
2096
|
jbe@532
|
2097
|
jbe@532
|
2098 DROP TABLE "membership";
|
jbe@532
|
2099
|
jbe@532
|
2100
|
jbe@532
|
2101 DROP FUNCTION "membership_weight"
|
jbe@532
|
2102 ( "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2103 "member_id_p" "member"."id"%TYPE );
|
jbe@532
|
2104
|
jbe@532
|
2105
|
jbe@532
|
2106 DROP FUNCTION "membership_weight_with_skipping"
|
jbe@532
|
2107 ( "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2108 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2109 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
|
jbe@532
|
2110
|
jbe@532
|
2111
|
jbe@532
|
2112 CREATE OR REPLACE VIEW "issue_delegation" AS
|
jbe@532
|
2113 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
jbe@532
|
2114 "issue"."id" AS "issue_id",
|
jbe@532
|
2115 "delegation"."id",
|
jbe@532
|
2116 "delegation"."truster_id",
|
jbe@532
|
2117 "delegation"."trustee_id",
|
jbe@532
|
2118 "delegation"."scope"
|
jbe@532
|
2119 FROM "issue"
|
jbe@532
|
2120 JOIN "area"
|
jbe@532
|
2121 ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2122 JOIN "delegation"
|
jbe@532
|
2123 ON "delegation"."unit_id" = "area"."unit_id"
|
jbe@532
|
2124 OR "delegation"."area_id" = "area"."id"
|
jbe@532
|
2125 OR "delegation"."issue_id" = "issue"."id"
|
jbe@532
|
2126 JOIN "member"
|
jbe@532
|
2127 ON "delegation"."truster_id" = "member"."id"
|
jbe@556
|
2128 JOIN "privilege"
|
jbe@556
|
2129 ON "area"."unit_id" = "privilege"."unit_id"
|
jbe@556
|
2130 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@556
|
2131 WHERE "member"."active" AND "privilege"."voting_right"
|
jbe@532
|
2132 ORDER BY
|
jbe@532
|
2133 "issue"."id",
|
jbe@532
|
2134 "delegation"."truster_id",
|
jbe@532
|
2135 "delegation"."scope" DESC;
|
jbe@532
|
2136
|
jbe@532
|
2137
|
jbe@532
|
2138 CREATE VIEW "unit_member" AS
|
jbe@532
|
2139 SELECT
|
jbe@532
|
2140 "unit"."id" AS "unit_id",
|
jbe@532
|
2141 "member"."id" AS "member_id"
|
jbe@556
|
2142 FROM "privilege"
|
jbe@569
|
2143 JOIN "unit" ON "unit"."id" = "privilege"."unit_id"
|
jbe@556
|
2144 JOIN "member" ON "member"."id" = "privilege"."member_id"
|
jbe@556
|
2145 WHERE "privilege"."voting_right" AND "member"."active";
|
jbe@532
|
2146
|
jbe@532
|
2147 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
|
jbe@532
|
2148
|
jbe@532
|
2149
|
jbe@532
|
2150 CREATE OR REPLACE VIEW "unit_member_count" AS
|
jbe@532
|
2151 SELECT
|
jbe@532
|
2152 "unit"."id" AS "unit_id",
|
jbe@532
|
2153 count("unit_member"."member_id") AS "member_count"
|
jbe@532
|
2154 FROM "unit" LEFT JOIN "unit_member"
|
jbe@532
|
2155 ON "unit"."id" = "unit_member"."unit_id"
|
jbe@532
|
2156 GROUP BY "unit"."id";
|
jbe@532
|
2157
|
jbe@532
|
2158 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
|
jbe@532
|
2159
|
jbe@532
|
2160
|
jbe@532
|
2161 CREATE OR REPLACE VIEW "opening_draft" AS
|
jbe@532
|
2162 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
|
jbe@532
|
2163 ORDER BY "initiative_id", "id";
|
jbe@532
|
2164
|
jbe@532
|
2165
|
jbe@532
|
2166 CREATE OR REPLACE VIEW "current_draft" AS
|
jbe@532
|
2167 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
|
jbe@532
|
2168 ORDER BY "initiative_id", "id" DESC;
|
jbe@532
|
2169
|
jbe@532
|
2170
|
jbe@532
|
2171 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
|
jbe@532
|
2172 SELECT
|
jbe@532
|
2173 "area"."unit_id",
|
jbe@532
|
2174 "issue"."area_id",
|
jbe@532
|
2175 "issue"."id" AS "issue_id",
|
jbe@532
|
2176 "supporter"."member_id",
|
jbe@532
|
2177 "direct_interest_snapshot"."weight"
|
jbe@532
|
2178 FROM "issue"
|
jbe@532
|
2179 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2180 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
|
jbe@532
|
2181 JOIN "direct_interest_snapshot"
|
jbe@532
|
2182 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2183 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
2184 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
|
jbe@532
|
2185 WHERE "issue"."state" = 'admission'::"issue_state";
|
jbe@532
|
2186
|
jbe@532
|
2187
|
jbe@532
|
2188 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
|
jbe@532
|
2189 SELECT
|
jbe@532
|
2190 "opinion"."initiative_id",
|
jbe@532
|
2191 "opinion"."member_id",
|
jbe@532
|
2192 "direct_interest_snapshot"."weight",
|
jbe@532
|
2193 CASE WHEN
|
jbe@532
|
2194 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@532
|
2195 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
|
jbe@532
|
2196 THEN 1 ELSE
|
jbe@532
|
2197 CASE WHEN
|
jbe@532
|
2198 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@532
|
2199 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
|
jbe@532
|
2200 THEN 2 ELSE
|
jbe@532
|
2201 CASE WHEN
|
jbe@532
|
2202 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
|
jbe@532
|
2203 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
|
jbe@532
|
2204 THEN 3 ELSE 4 END
|
jbe@532
|
2205 END
|
jbe@532
|
2206 END AS "preference",
|
jbe@532
|
2207 "opinion"."suggestion_id"
|
jbe@532
|
2208 FROM "opinion"
|
jbe@532
|
2209 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
|
jbe@532
|
2210 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2211 JOIN "direct_interest_snapshot"
|
jbe@532
|
2212 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2213 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
2214 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
|
jbe@532
|
2215
|
jbe@532
|
2216
|
jbe@532
|
2217 CREATE VIEW "expired_session" AS
|
jbe@532
|
2218 SELECT * FROM "session" WHERE now() > "expiry";
|
jbe@532
|
2219
|
jbe@532
|
2220 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
|
jbe@532
|
2221 DELETE FROM "session" WHERE "id" = OLD."id";
|
jbe@532
|
2222
|
jbe@532
|
2223 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
|
jbe@532
|
2224 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
|
jbe@532
|
2225
|
jbe@532
|
2226
|
jbe@532
|
2227 CREATE VIEW "expired_token" AS
|
jbe@532
|
2228 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
|
jbe@532
|
2229 "token_type" = 'authorization' AND "used" AND EXISTS (
|
jbe@532
|
2230 SELECT NULL FROM "token" AS "other"
|
jbe@532
|
2231 WHERE "other"."authorization_token_id" = "id" ) );
|
jbe@532
|
2232
|
jbe@532
|
2233 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
|
jbe@532
|
2234 DELETE FROM "token" WHERE "id" = OLD."id";
|
jbe@532
|
2235
|
jbe@532
|
2236 COMMENT ON VIEW "expired_token" IS 'View containing all expired tokens where DELETE is possible; Note that used authorization codes must not be deleted if still referred to by other tokens';
|
jbe@532
|
2237
|
jbe@532
|
2238
|
jbe@532
|
2239 CREATE VIEW "unused_snapshot" AS
|
jbe@532
|
2240 SELECT "snapshot".* FROM "snapshot"
|
jbe@532
|
2241 LEFT JOIN "issue"
|
jbe@532
|
2242 ON "snapshot"."id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2243 OR "snapshot"."id" = "issue"."admission_snapshot_id"
|
jbe@532
|
2244 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
|
jbe@532
|
2245 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
|
jbe@532
|
2246 WHERE "issue"."id" ISNULL;
|
jbe@532
|
2247
|
jbe@532
|
2248 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
|
jbe@532
|
2249 DELETE FROM "snapshot" WHERE "id" = OLD."id";
|
jbe@532
|
2250
|
jbe@532
|
2251 COMMENT ON VIEW "unused_snapshot" IS 'Snapshots that are not referenced by any issue (either as latest snapshot or as snapshot at phase/state change)';
|
jbe@532
|
2252
|
jbe@532
|
2253
|
jbe@532
|
2254 CREATE VIEW "expired_snapshot" AS
|
jbe@532
|
2255 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
|
jbe@532
|
2256 WHERE "unused_snapshot"."calculated" <
|
jbe@532
|
2257 now() - "system_setting"."snapshot_retention";
|
jbe@532
|
2258
|
jbe@532
|
2259 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
|
jbe@532
|
2260 DELETE FROM "snapshot" WHERE "id" = OLD."id";
|
jbe@532
|
2261
|
jbe@532
|
2262 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
|
jbe@532
|
2263
|
jbe@532
|
2264
|
jbe@532
|
2265 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
|
jbe@532
|
2266
|
jbe@532
|
2267
|
jbe@532
|
2268 CREATE OR REPLACE FUNCTION "delegation_chain"
|
jbe@532
|
2269 ( "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2270 "unit_id_p" "unit"."id"%TYPE,
|
jbe@532
|
2271 "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2272 "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2273 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
|
jbe@532
|
2274 "simulate_default_p" BOOLEAN DEFAULT FALSE )
|
jbe@532
|
2275 RETURNS SETOF "delegation_chain_row"
|
jbe@532
|
2276 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@532
|
2277 DECLARE
|
jbe@532
|
2278 "scope_v" "delegation_scope";
|
jbe@532
|
2279 "unit_id_v" "unit"."id"%TYPE;
|
jbe@532
|
2280 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
2281 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
2282 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@532
|
2283 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@532
|
2284 "output_row" "delegation_chain_row";
|
jbe@532
|
2285 "output_rows" "delegation_chain_row"[];
|
jbe@532
|
2286 "simulate_v" BOOLEAN;
|
jbe@532
|
2287 "simulate_here_v" BOOLEAN;
|
jbe@532
|
2288 "delegation_row" "delegation"%ROWTYPE;
|
jbe@532
|
2289 "row_count" INT4;
|
jbe@532
|
2290 "i" INT4;
|
jbe@532
|
2291 "loop_v" BOOLEAN;
|
jbe@532
|
2292 BEGIN
|
jbe@532
|
2293 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
|
jbe@532
|
2294 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
|
jbe@532
|
2295 END IF;
|
jbe@532
|
2296 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
|
jbe@532
|
2297 "simulate_v" := TRUE;
|
jbe@532
|
2298 ELSE
|
jbe@532
|
2299 "simulate_v" := FALSE;
|
jbe@532
|
2300 END IF;
|
jbe@532
|
2301 IF
|
jbe@532
|
2302 "unit_id_p" NOTNULL AND
|
jbe@532
|
2303 "area_id_p" ISNULL AND
|
jbe@532
|
2304 "issue_id_p" ISNULL
|
jbe@532
|
2305 THEN
|
jbe@532
|
2306 "scope_v" := 'unit';
|
jbe@532
|
2307 "unit_id_v" := "unit_id_p";
|
jbe@532
|
2308 ELSIF
|
jbe@532
|
2309 "unit_id_p" ISNULL AND
|
jbe@532
|
2310 "area_id_p" NOTNULL AND
|
jbe@532
|
2311 "issue_id_p" ISNULL
|
jbe@532
|
2312 THEN
|
jbe@532
|
2313 "scope_v" := 'area';
|
jbe@532
|
2314 "area_id_v" := "area_id_p";
|
jbe@532
|
2315 SELECT "unit_id" INTO "unit_id_v"
|
jbe@532
|
2316 FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2317 ELSIF
|
jbe@532
|
2318 "unit_id_p" ISNULL AND
|
jbe@532
|
2319 "area_id_p" ISNULL AND
|
jbe@532
|
2320 "issue_id_p" NOTNULL
|
jbe@532
|
2321 THEN
|
jbe@532
|
2322 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2323 IF "issue_row"."id" ISNULL THEN
|
jbe@532
|
2324 RETURN;
|
jbe@532
|
2325 END IF;
|
jbe@532
|
2326 IF "issue_row"."closed" NOTNULL THEN
|
jbe@532
|
2327 IF "simulate_v" THEN
|
jbe@532
|
2328 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
|
jbe@532
|
2329 END IF;
|
jbe@532
|
2330 FOR "output_row" IN
|
jbe@532
|
2331 SELECT * FROM
|
jbe@532
|
2332 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
|
jbe@532
|
2333 LOOP
|
jbe@532
|
2334 RETURN NEXT "output_row";
|
jbe@532
|
2335 END LOOP;
|
jbe@532
|
2336 RETURN;
|
jbe@532
|
2337 END IF;
|
jbe@532
|
2338 "scope_v" := 'issue';
|
jbe@532
|
2339 SELECT "area_id" INTO "area_id_v"
|
jbe@532
|
2340 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2341 SELECT "unit_id" INTO "unit_id_v"
|
jbe@532
|
2342 FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2343 ELSE
|
jbe@532
|
2344 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
|
jbe@532
|
2345 END IF;
|
jbe@532
|
2346 "visited_member_ids" := '{}';
|
jbe@532
|
2347 "loop_member_id_v" := NULL;
|
jbe@532
|
2348 "output_rows" := '{}';
|
jbe@532
|
2349 "output_row"."index" := 0;
|
jbe@532
|
2350 "output_row"."member_id" := "member_id_p";
|
jbe@532
|
2351 "output_row"."member_valid" := TRUE;
|
jbe@532
|
2352 "output_row"."participation" := FALSE;
|
jbe@532
|
2353 "output_row"."overridden" := FALSE;
|
jbe@532
|
2354 "output_row"."disabled_out" := FALSE;
|
jbe@532
|
2355 "output_row"."scope_out" := NULL;
|
jbe@532
|
2356 LOOP
|
jbe@532
|
2357 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@532
|
2358 "loop_member_id_v" := "output_row"."member_id";
|
jbe@532
|
2359 ELSE
|
jbe@532
|
2360 "visited_member_ids" :=
|
jbe@532
|
2361 "visited_member_ids" || "output_row"."member_id";
|
jbe@532
|
2362 END IF;
|
jbe@532
|
2363 IF "output_row"."participation" ISNULL THEN
|
jbe@532
|
2364 "output_row"."overridden" := NULL;
|
jbe@532
|
2365 ELSIF "output_row"."participation" THEN
|
jbe@532
|
2366 "output_row"."overridden" := TRUE;
|
jbe@532
|
2367 END IF;
|
jbe@532
|
2368 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@532
|
2369 "output_row"."member_valid" := EXISTS (
|
jbe@556
|
2370 SELECT NULL FROM "member" JOIN "privilege"
|
jbe@556
|
2371 ON "privilege"."member_id" = "member"."id"
|
jbe@556
|
2372 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@532
|
2373 WHERE "id" = "output_row"."member_id"
|
jbe@556
|
2374 AND "member"."active" AND "privilege"."voting_right"
|
jbe@532
|
2375 );
|
jbe@532
|
2376 "simulate_here_v" := (
|
jbe@532
|
2377 "simulate_v" AND
|
jbe@532
|
2378 "output_row"."member_id" = "member_id_p"
|
jbe@532
|
2379 );
|
jbe@532
|
2380 "delegation_row" := ROW(NULL);
|
jbe@532
|
2381 IF "output_row"."member_valid" OR "simulate_here_v" THEN
|
jbe@532
|
2382 IF "scope_v" = 'unit' THEN
|
jbe@532
|
2383 IF NOT "simulate_here_v" THEN
|
jbe@532
|
2384 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2385 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2386 AND "unit_id" = "unit_id_v";
|
jbe@532
|
2387 END IF;
|
jbe@532
|
2388 ELSIF "scope_v" = 'area' THEN
|
jbe@532
|
2389 IF "simulate_here_v" THEN
|
jbe@532
|
2390 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@532
|
2391 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2392 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2393 AND "unit_id" = "unit_id_v";
|
jbe@532
|
2394 END IF;
|
jbe@532
|
2395 ELSE
|
jbe@532
|
2396 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2397 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2398 AND (
|
jbe@532
|
2399 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2400 "area_id" = "area_id_v"
|
jbe@532
|
2401 )
|
jbe@532
|
2402 ORDER BY "scope" DESC;
|
jbe@532
|
2403 END IF;
|
jbe@532
|
2404 ELSIF "scope_v" = 'issue' THEN
|
jbe@532
|
2405 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@532
|
2406 "output_row"."participation" := EXISTS (
|
jbe@532
|
2407 SELECT NULL FROM "interest"
|
jbe@532
|
2408 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
2409 AND "member_id" = "output_row"."member_id"
|
jbe@532
|
2410 );
|
jbe@532
|
2411 ELSE
|
jbe@532
|
2412 IF "output_row"."member_id" = "member_id_p" THEN
|
jbe@532
|
2413 "output_row"."participation" := EXISTS (
|
jbe@532
|
2414 SELECT NULL FROM "direct_voter"
|
jbe@532
|
2415 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
2416 AND "member_id" = "output_row"."member_id"
|
jbe@532
|
2417 );
|
jbe@532
|
2418 ELSE
|
jbe@532
|
2419 "output_row"."participation" := NULL;
|
jbe@532
|
2420 END IF;
|
jbe@532
|
2421 END IF;
|
jbe@532
|
2422 IF "simulate_here_v" THEN
|
jbe@532
|
2423 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@532
|
2424 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2425 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2426 AND (
|
jbe@532
|
2427 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2428 "area_id" = "area_id_v"
|
jbe@532
|
2429 )
|
jbe@532
|
2430 ORDER BY "scope" DESC;
|
jbe@532
|
2431 END IF;
|
jbe@532
|
2432 ELSE
|
jbe@532
|
2433 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2434 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2435 AND (
|
jbe@532
|
2436 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2437 "area_id" = "area_id_v" OR
|
jbe@532
|
2438 "issue_id" = "issue_id_p"
|
jbe@532
|
2439 )
|
jbe@532
|
2440 ORDER BY "scope" DESC;
|
jbe@532
|
2441 END IF;
|
jbe@532
|
2442 END IF;
|
jbe@532
|
2443 ELSE
|
jbe@532
|
2444 "output_row"."participation" := FALSE;
|
jbe@532
|
2445 END IF;
|
jbe@532
|
2446 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
|
jbe@532
|
2447 "output_row"."scope_out" := "scope_v";
|
jbe@532
|
2448 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2449 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@532
|
2450 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@532
|
2451 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@532
|
2452 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2453 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@532
|
2454 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@532
|
2455 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@532
|
2456 "output_row"."disabled_out" := TRUE;
|
jbe@532
|
2457 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2458 EXIT;
|
jbe@532
|
2459 ELSE
|
jbe@532
|
2460 "output_row"."scope_out" := NULL;
|
jbe@532
|
2461 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2462 EXIT;
|
jbe@532
|
2463 END IF;
|
jbe@532
|
2464 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@532
|
2465 "output_row"."index" := "output_row"."index" + 1;
|
jbe@532
|
2466 END LOOP;
|
jbe@532
|
2467 "row_count" := array_upper("output_rows", 1);
|
jbe@532
|
2468 "i" := 1;
|
jbe@532
|
2469 "loop_v" := FALSE;
|
jbe@532
|
2470 LOOP
|
jbe@532
|
2471 "output_row" := "output_rows"["i"];
|
jbe@532
|
2472 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
|
jbe@532
|
2473 IF "loop_v" THEN
|
jbe@532
|
2474 IF "i" + 1 = "row_count" THEN
|
jbe@532
|
2475 "output_row"."loop" := 'last';
|
jbe@532
|
2476 ELSIF "i" = "row_count" THEN
|
jbe@532
|
2477 "output_row"."loop" := 'repetition';
|
jbe@532
|
2478 ELSE
|
jbe@532
|
2479 "output_row"."loop" := 'intermediate';
|
jbe@532
|
2480 END IF;
|
jbe@532
|
2481 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@532
|
2482 "output_row"."loop" := 'first';
|
jbe@532
|
2483 "loop_v" := TRUE;
|
jbe@532
|
2484 END IF;
|
jbe@532
|
2485 IF "scope_v" = 'unit' THEN
|
jbe@532
|
2486 "output_row"."participation" := NULL;
|
jbe@532
|
2487 END IF;
|
jbe@532
|
2488 RETURN NEXT "output_row";
|
jbe@532
|
2489 "i" := "i" + 1;
|
jbe@532
|
2490 END LOOP;
|
jbe@532
|
2491 RETURN;
|
jbe@532
|
2492 END;
|
jbe@532
|
2493 $$;
|
jbe@532
|
2494
|
jbe@532
|
2495
|
jbe@532
|
2496 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
|
jbe@532
|
2497 ( "recipient_id_p" "member"."id"%TYPE )
|
jbe@532
|
2498 RETURNS SETOF "initiative_for_notification"
|
jbe@532
|
2499 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2500 DECLARE
|
jbe@532
|
2501 "result_row" "initiative_for_notification"%ROWTYPE;
|
jbe@532
|
2502 "last_draft_id_v" "draft"."id"%TYPE;
|
jbe@532
|
2503 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@532
|
2504 BEGIN
|
jbe@532
|
2505 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2506 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
jbe@532
|
2507 FOR "result_row" IN
|
jbe@532
|
2508 SELECT * FROM "initiative_for_notification"
|
jbe@532
|
2509 WHERE "recipient_id" = "recipient_id_p"
|
jbe@532
|
2510 LOOP
|
jbe@532
|
2511 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
jbe@532
|
2512 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
jbe@532
|
2513 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2514 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
jbe@532
|
2515 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
jbe@532
|
2516 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2517 INSERT INTO "notification_initiative_sent"
|
jbe@532
|
2518 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
jbe@532
|
2519 VALUES (
|
jbe@532
|
2520 "recipient_id_p",
|
jbe@532
|
2521 "result_row"."initiative_id",
|
jbe@532
|
2522 "last_draft_id_v",
|
jbe@532
|
2523 "last_suggestion_id_v" )
|
jbe@532
|
2524 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
jbe@532
|
2525 "last_draft_id" = "last_draft_id_v",
|
jbe@532
|
2526 "last_suggestion_id" = "last_suggestion_id_v";
|
jbe@532
|
2527 RETURN NEXT "result_row";
|
jbe@532
|
2528 END LOOP;
|
jbe@532
|
2529 DELETE FROM "notification_initiative_sent"
|
jbe@532
|
2530 USING "initiative", "issue"
|
jbe@532
|
2531 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
|
jbe@532
|
2532 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
|
jbe@532
|
2533 AND "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2534 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
jbe@532
|
2535 UPDATE "member" SET
|
jbe@532
|
2536 "notification_counter" = "notification_counter" + 1,
|
jbe@532
|
2537 "notification_sent" = now()
|
jbe@532
|
2538 WHERE "id" = "recipient_id_p";
|
jbe@532
|
2539 RETURN;
|
jbe@532
|
2540 END;
|
jbe@532
|
2541 $$;
|
jbe@532
|
2542
|
jbe@532
|
2543
|
jbe@532
|
2544 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
|
jbe@532
|
2545 RETURNS VOID
|
jbe@532
|
2546 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2547 BEGIN
|
jbe@532
|
2548 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2549 DELETE FROM "member_count";
|
jbe@532
|
2550 INSERT INTO "member_count" ("total_count")
|
jbe@532
|
2551 SELECT "total_count" FROM "member_count_view";
|
jbe@532
|
2552 UPDATE "unit" SET "member_count" = "view"."member_count"
|
jbe@532
|
2553 FROM "unit_member_count" AS "view"
|
jbe@532
|
2554 WHERE "view"."unit_id" = "unit"."id";
|
jbe@532
|
2555 RETURN;
|
jbe@532
|
2556 END;
|
jbe@532
|
2557 $$;
|
jbe@532
|
2558
|
jbe@532
|
2559 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 "unit_member_count"';
|
jbe@532
|
2560
|
jbe@532
|
2561
|
jbe@532
|
2562 DROP VIEW "remaining_harmonic_initiative_weight_summands";
|
jbe@532
|
2563 DROP VIEW "remaining_harmonic_supporter_weight";
|
jbe@532
|
2564
|
jbe@532
|
2565
|
jbe@532
|
2566 CREATE VIEW "remaining_harmonic_supporter_weight" AS
|
jbe@532
|
2567 SELECT
|
jbe@532
|
2568 "direct_interest_snapshot"."snapshot_id",
|
jbe@532
|
2569 "direct_interest_snapshot"."issue_id",
|
jbe@532
|
2570 "direct_interest_snapshot"."member_id",
|
jbe@532
|
2571 "direct_interest_snapshot"."weight" AS "weight_num",
|
jbe@532
|
2572 count("initiative"."id") AS "weight_den"
|
jbe@532
|
2573 FROM "issue"
|
jbe@532
|
2574 JOIN "direct_interest_snapshot"
|
jbe@532
|
2575 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
|
jbe@532
|
2576 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
|
jbe@532
|
2577 JOIN "initiative"
|
jbe@532
|
2578 ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2579 AND "initiative"."harmonic_weight" ISNULL
|
jbe@532
|
2580 JOIN "direct_supporter_snapshot"
|
jbe@532
|
2581 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
|
jbe@532
|
2582 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
|
jbe@532
|
2583 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@532
|
2584 AND (
|
jbe@532
|
2585 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@532
|
2586 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@532
|
2587 )
|
jbe@532
|
2588 GROUP BY
|
jbe@532
|
2589 "direct_interest_snapshot"."snapshot_id",
|
jbe@532
|
2590 "direct_interest_snapshot"."issue_id",
|
jbe@532
|
2591 "direct_interest_snapshot"."member_id",
|
jbe@532
|
2592 "direct_interest_snapshot"."weight";
|
jbe@532
|
2593
|
jbe@532
|
2594
|
jbe@532
|
2595 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
|
jbe@532
|
2596 SELECT
|
jbe@532
|
2597 "initiative"."issue_id",
|
jbe@532
|
2598 "initiative"."id" AS "initiative_id",
|
jbe@532
|
2599 "initiative"."admitted",
|
jbe@532
|
2600 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
|
jbe@532
|
2601 "remaining_harmonic_supporter_weight"."weight_den"
|
jbe@532
|
2602 FROM "remaining_harmonic_supporter_weight"
|
jbe@532
|
2603 JOIN "initiative"
|
jbe@532
|
2604 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
|
jbe@532
|
2605 AND "initiative"."harmonic_weight" ISNULL
|
jbe@532
|
2606 JOIN "direct_supporter_snapshot"
|
jbe@532
|
2607 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
|
jbe@532
|
2608 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
|
jbe@532
|
2609 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@532
|
2610 AND (
|
jbe@532
|
2611 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@532
|
2612 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@532
|
2613 )
|
jbe@532
|
2614 GROUP BY
|
jbe@532
|
2615 "initiative"."issue_id",
|
jbe@532
|
2616 "initiative"."id",
|
jbe@532
|
2617 "initiative"."admitted",
|
jbe@532
|
2618 "remaining_harmonic_supporter_weight"."weight_den";
|
jbe@532
|
2619
|
jbe@532
|
2620
|
jbe@532
|
2621 DROP FUNCTION "create_population_snapshot"
|
jbe@532
|
2622 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2623
|
jbe@532
|
2624
|
jbe@532
|
2625 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
jbe@532
|
2626 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2627 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2628 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
|
jbe@532
|
2629
|
jbe@532
|
2630
|
jbe@532
|
2631 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
jbe@532
|
2632 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2633 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2634 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
|
jbe@532
|
2635
|
jbe@532
|
2636
|
jbe@532
|
2637 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
|
jbe@532
|
2638 ( "snapshot_id_p" "snapshot"."id"%TYPE,
|
jbe@532
|
2639 "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2640 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2641 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@532
|
2642 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
jbe@532
|
2643 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2644 DECLARE
|
jbe@532
|
2645 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@532
|
2646 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
jbe@532
|
2647 "weight_v" INT4;
|
jbe@532
|
2648 "sub_weight_v" INT4;
|
jbe@532
|
2649 BEGIN
|
jbe@532
|
2650 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2651 "weight_v" := 0;
|
jbe@532
|
2652 FOR "issue_delegation_row" IN
|
jbe@532
|
2653 SELECT * FROM "issue_delegation"
|
jbe@532
|
2654 WHERE "trustee_id" = "member_id_p"
|
jbe@532
|
2655 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2656 LOOP
|
jbe@532
|
2657 IF NOT EXISTS (
|
jbe@532
|
2658 SELECT NULL FROM "direct_interest_snapshot"
|
jbe@532
|
2659 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2660 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2661 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@532
|
2662 ) AND NOT EXISTS (
|
jbe@532
|
2663 SELECT NULL FROM "delegating_interest_snapshot"
|
jbe@532
|
2664 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2665 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2666 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@532
|
2667 ) THEN
|
jbe@532
|
2668 "delegate_member_ids_v" :=
|
jbe@532
|
2669 "member_id_p" || "delegate_member_ids_p";
|
jbe@532
|
2670 INSERT INTO "delegating_interest_snapshot" (
|
jbe@532
|
2671 "snapshot_id",
|
jbe@532
|
2672 "issue_id",
|
jbe@532
|
2673 "member_id",
|
jbe@532
|
2674 "scope",
|
jbe@532
|
2675 "delegate_member_ids"
|
jbe@532
|
2676 ) VALUES (
|
jbe@532
|
2677 "snapshot_id_p",
|
jbe@532
|
2678 "issue_id_p",
|
jbe@532
|
2679 "issue_delegation_row"."truster_id",
|
jbe@532
|
2680 "issue_delegation_row"."scope",
|
jbe@532
|
2681 "delegate_member_ids_v"
|
jbe@532
|
2682 );
|
jbe@532
|
2683 "sub_weight_v" := 1 +
|
jbe@532
|
2684 "weight_of_added_delegations_for_snapshot"(
|
jbe@532
|
2685 "snapshot_id_p",
|
jbe@532
|
2686 "issue_id_p",
|
jbe@532
|
2687 "issue_delegation_row"."truster_id",
|
jbe@532
|
2688 "delegate_member_ids_v"
|
jbe@532
|
2689 );
|
jbe@532
|
2690 UPDATE "delegating_interest_snapshot"
|
jbe@532
|
2691 SET "weight" = "sub_weight_v"
|
jbe@532
|
2692 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2693 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2694 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@532
|
2695 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@532
|
2696 END IF;
|
jbe@532
|
2697 END LOOP;
|
jbe@532
|
2698 RETURN "weight_v";
|
jbe@532
|
2699 END;
|
jbe@532
|
2700 $$;
|
jbe@532
|
2701
|
jbe@532
|
2702 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
|
jbe@532
|
2703 ( "snapshot"."id"%TYPE,
|
jbe@532
|
2704 "issue"."id"%TYPE,
|
jbe@532
|
2705 "member"."id"%TYPE,
|
jbe@532
|
2706 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@532
|
2707 IS 'Helper function for "fill_snapshot" function';
|
jbe@532
|
2708
|
jbe@532
|
2709
|
jbe@532
|
2710 DROP FUNCTION "create_interest_snapshot"
|
jbe@532
|
2711 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2712
|
jbe@532
|
2713
|
jbe@532
|
2714 DROP FUNCTION "create_snapshot"
|
jbe@532
|
2715 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2716
|
jbe@532
|
2717
|
jbe@532
|
2718 CREATE FUNCTION "take_snapshot"
|
jbe@532
|
2719 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2720 "area_id_p" "area"."id"%TYPE = NULL )
|
jbe@532
|
2721 RETURNS "snapshot"."id"%TYPE
|
jbe@532
|
2722 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2723 DECLARE
|
jbe@532
|
2724 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
2725 "unit_id_v" "unit"."id"%TYPE;
|
jbe@532
|
2726 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
2727 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
2728 "member_id_v" "member"."id"%TYPE;
|
jbe@532
|
2729 BEGIN
|
jbe@532
|
2730 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
|
jbe@532
|
2731 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
|
jbe@532
|
2732 END IF;
|
jbe@532
|
2733 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2734 IF "issue_id_p" ISNULL THEN
|
jbe@532
|
2735 "area_id_v" := "area_id_p";
|
jbe@532
|
2736 ELSE
|
jbe@532
|
2737 SELECT "area_id" INTO "area_id_v"
|
jbe@532
|
2738 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2739 END IF;
|
jbe@562
|
2740 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2741 INSERT INTO "snapshot" ("area_id", "issue_id")
|
jbe@532
|
2742 VALUES ("area_id_v", "issue_id_p")
|
jbe@532
|
2743 RETURNING "id" INTO "snapshot_id_v";
|
jbe@532
|
2744 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
2745 SELECT "snapshot_id_v", "member_id"
|
jbe@532
|
2746 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
|
jbe@532
|
2747 UPDATE "snapshot" SET
|
jbe@532
|
2748 "population" = (
|
jbe@532
|
2749 SELECT count(1) FROM "snapshot_population"
|
jbe@532
|
2750 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2751 ) WHERE "id" = "snapshot_id_v";
|
jbe@532
|
2752 FOR "issue_id_v" IN
|
jbe@532
|
2753 SELECT "id" FROM "issue"
|
jbe@532
|
2754 WHERE CASE WHEN "issue_id_p" ISNULL THEN
|
jbe@532
|
2755 "area_id" = "area_id_p" AND
|
jbe@532
|
2756 "state" = 'admission'
|
jbe@532
|
2757 ELSE
|
jbe@532
|
2758 "id" = "issue_id_p"
|
jbe@532
|
2759 END
|
jbe@532
|
2760 LOOP
|
jbe@532
|
2761 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
|
jbe@532
|
2762 VALUES ("snapshot_id_v", "issue_id_v");
|
jbe@532
|
2763 INSERT INTO "direct_interest_snapshot"
|
jbe@532
|
2764 ("snapshot_id", "issue_id", "member_id")
|
jbe@532
|
2765 SELECT
|
jbe@532
|
2766 "snapshot_id_v" AS "snapshot_id",
|
jbe@532
|
2767 "issue_id_v" AS "issue_id",
|
jbe@532
|
2768 "member"."id" AS "member_id"
|
jbe@532
|
2769 FROM "issue"
|
jbe@532
|
2770 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@532
|
2771 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
jbe@532
|
2772 JOIN "member" ON "interest"."member_id" = "member"."id"
|
jbe@556
|
2773 JOIN "privilege"
|
jbe@556
|
2774 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@556
|
2775 AND "privilege"."member_id" = "member"."id"
|
jbe@532
|
2776 WHERE "issue"."id" = "issue_id_v"
|
jbe@556
|
2777 AND "member"."active" AND "privilege"."voting_right";
|
jbe@532
|
2778 FOR "member_id_v" IN
|
jbe@532
|
2779 SELECT "member_id" FROM "direct_interest_snapshot"
|
jbe@532
|
2780 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2781 AND "issue_id" = "issue_id_v"
|
jbe@532
|
2782 LOOP
|
jbe@532
|
2783 UPDATE "direct_interest_snapshot" SET
|
jbe@532
|
2784 "weight" = 1 +
|
jbe@532
|
2785 "weight_of_added_delegations_for_snapshot"(
|
jbe@532
|
2786 "snapshot_id_v",
|
jbe@532
|
2787 "issue_id_v",
|
jbe@532
|
2788 "member_id_v",
|
jbe@532
|
2789 '{}'
|
jbe@532
|
2790 )
|
jbe@532
|
2791 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2792 AND "issue_id" = "issue_id_v"
|
jbe@532
|
2793 AND "member_id" = "member_id_v";
|
jbe@532
|
2794 END LOOP;
|
jbe@532
|
2795 INSERT INTO "direct_supporter_snapshot"
|
jbe@532
|
2796 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
|
jbe@532
|
2797 "draft_id", "informed", "satisfied" )
|
jbe@532
|
2798 SELECT
|
jbe@532
|
2799 "snapshot_id_v" AS "snapshot_id",
|
jbe@532
|
2800 "issue_id_v" AS "issue_id",
|
jbe@532
|
2801 "initiative"."id" AS "initiative_id",
|
jbe@532
|
2802 "supporter"."member_id" AS "member_id",
|
jbe@532
|
2803 "supporter"."draft_id" AS "draft_id",
|
jbe@532
|
2804 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
jbe@532
|
2805 NOT EXISTS (
|
jbe@532
|
2806 SELECT NULL FROM "critical_opinion"
|
jbe@532
|
2807 WHERE "initiative_id" = "initiative"."id"
|
jbe@532
|
2808 AND "member_id" = "supporter"."member_id"
|
jbe@532
|
2809 ) AS "satisfied"
|
jbe@532
|
2810 FROM "initiative"
|
jbe@532
|
2811 JOIN "supporter"
|
jbe@532
|
2812 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@532
|
2813 JOIN "current_draft"
|
jbe@532
|
2814 ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@532
|
2815 JOIN "direct_interest_snapshot"
|
jbe@532
|
2816 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
|
jbe@532
|
2817 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
|
jbe@532
|
2818 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
jbe@532
|
2819 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@532
|
2820 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
2821 INSERT INTO "temporary_suggestion_counts"
|
jbe@532
|
2822 ( "id",
|
jbe@532
|
2823 "minus2_unfulfilled_count", "minus2_fulfilled_count",
|
jbe@532
|
2824 "minus1_unfulfilled_count", "minus1_fulfilled_count",
|
jbe@532
|
2825 "plus1_unfulfilled_count", "plus1_fulfilled_count",
|
jbe@532
|
2826 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
|
jbe@532
|
2827 SELECT
|
jbe@532
|
2828 "suggestion"."id",
|
jbe@532
|
2829 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2830 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2831 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2832 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2833 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2834 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2835 AND "opinion"."degree" = -2
|
jbe@532
|
2836 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2837 ) AS "minus2_unfulfilled_count",
|
jbe@532
|
2838 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2839 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2840 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2841 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2842 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2843 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2844 AND "opinion"."degree" = -2
|
jbe@532
|
2845 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2846 ) AS "minus2_fulfilled_count",
|
jbe@532
|
2847 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2848 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2849 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2850 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2851 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2852 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2853 AND "opinion"."degree" = -1
|
jbe@532
|
2854 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2855 ) AS "minus1_unfulfilled_count",
|
jbe@532
|
2856 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2857 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2858 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2859 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2860 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2861 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2862 AND "opinion"."degree" = -1
|
jbe@532
|
2863 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2864 ) AS "minus1_fulfilled_count",
|
jbe@532
|
2865 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2866 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2867 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2868 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2869 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2870 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2871 AND "opinion"."degree" = 1
|
jbe@532
|
2872 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2873 ) AS "plus1_unfulfilled_count",
|
jbe@532
|
2874 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2875 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2876 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2877 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2878 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2879 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2880 AND "opinion"."degree" = 1
|
jbe@532
|
2881 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2882 ) AS "plus1_fulfilled_count",
|
jbe@532
|
2883 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2884 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2885 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2886 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2887 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2888 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2889 AND "opinion"."degree" = 2
|
jbe@532
|
2890 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2891 ) AS "plus2_unfulfilled_count",
|
jbe@532
|
2892 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2893 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2894 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2895 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2896 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2897 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2898 AND "opinion"."degree" = 2
|
jbe@532
|
2899 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2900 ) AS "plus2_fulfilled_count"
|
jbe@532
|
2901 FROM "suggestion" JOIN "initiative"
|
jbe@532
|
2902 ON "suggestion"."initiative_id" = "initiative"."id"
|
jbe@532
|
2903 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@532
|
2904 END LOOP;
|
jbe@532
|
2905 RETURN "snapshot_id_v";
|
jbe@532
|
2906 END;
|
jbe@532
|
2907 $$;
|
jbe@532
|
2908
|
jbe@532
|
2909 COMMENT ON FUNCTION "take_snapshot"
|
jbe@532
|
2910 ( "issue"."id"%TYPE,
|
jbe@532
|
2911 "area"."id"%TYPE )
|
jbe@532
|
2912 IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the first argument is NULL, for all issues in ''admission'' phase of the area given as second argument. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';
|
jbe@532
|
2913
|
jbe@532
|
2914
|
jbe@532
|
2915 DROP FUNCTION "set_snapshot_event"
|
jbe@532
|
2916 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2917 "event_p" "snapshot_event" );
|
jbe@532
|
2918
|
jbe@532
|
2919
|
jbe@532
|
2920 CREATE FUNCTION "finish_snapshot"
|
jbe@532
|
2921 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@532
|
2922 RETURNS VOID
|
jbe@532
|
2923 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2924 DECLARE
|
jbe@532
|
2925 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
2926 BEGIN
|
jbe@532
|
2927 -- NOTE: function does not require snapshot isolation but we don't call
|
jbe@532
|
2928 -- "dont_require_snapshot_isolation" here because this function is
|
jbe@532
|
2929 -- also invoked by "check_issue"
|
jbe@532
|
2930 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
|
jbe@532
|
2931 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
|
jbe@532
|
2932 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2933 UPDATE "issue" SET
|
jbe@532
|
2934 "calculated" = "snapshot"."calculated",
|
jbe@532
|
2935 "latest_snapshot_id" = "snapshot_id_v",
|
jbe@568
|
2936 "population" = "snapshot"."population",
|
jbe@568
|
2937 "initiative_quorum" = CASE WHEN
|
jbe@568
|
2938 "policy"."initiative_quorum" > ceil(
|
jbe@568
|
2939 ( "issue"."population"::INT8 *
|
jbe@568
|
2940 "policy"."initiative_quorum_num"::INT8 ) /
|
jbe@568
|
2941 "policy"."initiative_quorum_den"::FLOAT8
|
jbe@568
|
2942 )::INT4
|
jbe@568
|
2943 THEN
|
jbe@568
|
2944 "policy"."initiative_quorum"
|
jbe@568
|
2945 ELSE
|
jbe@568
|
2946 ceil(
|
jbe@568
|
2947 ( "issue"."population"::INT8 *
|
jbe@568
|
2948 "policy"."initiative_quorum_num"::INT8 ) /
|
jbe@568
|
2949 "policy"."initiative_quorum_den"::FLOAT8
|
jbe@568
|
2950 )::INT4
|
jbe@568
|
2951 END
|
jbe@568
|
2952 FROM "snapshot", "policy"
|
jbe@532
|
2953 WHERE "issue"."id" = "issue_id_p"
|
jbe@568
|
2954 AND "snapshot"."id" = "snapshot_id_v"
|
jbe@568
|
2955 AND "policy"."id" = "issue"."policy_id";
|
jbe@532
|
2956 UPDATE "initiative" SET
|
jbe@532
|
2957 "supporter_count" = (
|
jbe@532
|
2958 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2959 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2960 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2961 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2962 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2963 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2964 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2965 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2966 ),
|
jbe@532
|
2967 "informed_supporter_count" = (
|
jbe@532
|
2968 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2969 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2970 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2971 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2972 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2973 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2974 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2975 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2976 AND "ds"."informed"
|
jbe@532
|
2977 ),
|
jbe@532
|
2978 "satisfied_supporter_count" = (
|
jbe@532
|
2979 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2980 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2981 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2982 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2983 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2984 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2985 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2986 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2987 AND "ds"."satisfied"
|
jbe@532
|
2988 ),
|
jbe@532
|
2989 "satisfied_informed_supporter_count" = (
|
jbe@532
|
2990 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2991 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2992 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2993 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2994 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2995 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2996 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2997 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2998 AND "ds"."informed"
|
jbe@532
|
2999 AND "ds"."satisfied"
|
jbe@532
|
3000 )
|
jbe@532
|
3001 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3002 UPDATE "suggestion" SET
|
jbe@532
|
3003 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
|
jbe@532
|
3004 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
|
jbe@532
|
3005 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
|
jbe@532
|
3006 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
|
jbe@532
|
3007 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
|
jbe@532
|
3008 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
|
jbe@532
|
3009 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
|
jbe@532
|
3010 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
|
jbe@532
|
3011 FROM "temporary_suggestion_counts" AS "temp", "initiative"
|
jbe@532
|
3012 WHERE "temp"."id" = "suggestion"."id"
|
jbe@532
|
3013 AND "initiative"."issue_id" = "issue_id_p"
|
jbe@532
|
3014 AND "suggestion"."initiative_id" = "initiative"."id";
|
jbe@532
|
3015 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
3016 RETURN;
|
jbe@532
|
3017 END;
|
jbe@532
|
3018 $$;
|
jbe@532
|
3019
|
jbe@532
|
3020 COMMENT ON FUNCTION "finish_snapshot"
|
jbe@532
|
3021 ( "issue"."id"%TYPE )
|
jbe@532
|
3022 IS 'After calling "take_snapshot", this function "finish_snapshot" needs to be called for every issue in the snapshot (separate function calls keep locking time minimal)';
|
jbe@532
|
3023
|
jbe@532
|
3024
|
jbe@532
|
3025 CREATE FUNCTION "issue_admission"
|
jbe@532
|
3026 ( "area_id_p" "area"."id"%TYPE )
|
jbe@532
|
3027 RETURNS BOOLEAN
|
jbe@532
|
3028 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3029 DECLARE
|
jbe@532
|
3030 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
3031 BEGIN
|
jbe@532
|
3032 PERFORM "dont_require_transaction_isolation"();
|
jbe@532
|
3033 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
|
jbe@532
|
3034 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
|
jbe@532
|
3035 FROM "area_quorum" AS "view"
|
jbe@532
|
3036 WHERE "area"."id" = "view"."area_id"
|
jbe@532
|
3037 AND "area"."id" = "area_id_p";
|
jbe@532
|
3038 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
|
jbe@532
|
3039 WHERE "area_id" = "area_id_p";
|
jbe@532
|
3040 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
|
jbe@532
|
3041 UPDATE "issue" SET
|
jbe@532
|
3042 "admission_snapshot_id" = "latest_snapshot_id",
|
jbe@532
|
3043 "state" = 'discussion',
|
jbe@532
|
3044 "accepted" = now(),
|
jbe@568
|
3045 "phase_finished" = NULL,
|
jbe@568
|
3046 "issue_quorum" = "issue_quorum"."issue_quorum"
|
jbe@568
|
3047 FROM "issue_quorum"
|
jbe@568
|
3048 WHERE "id" = "issue_id_v"
|
jbe@568
|
3049 AND "issue_quorum"."issue_id" = "issue_id_v";
|
jbe@532
|
3050 RETURN TRUE;
|
jbe@532
|
3051 END;
|
jbe@532
|
3052 $$;
|
jbe@532
|
3053
|
jbe@532
|
3054 COMMENT ON FUNCTION "issue_admission"
|
jbe@532
|
3055 ( "area"."id"%TYPE )
|
jbe@532
|
3056 IS 'Checks if an issue in the area can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';
|
jbe@532
|
3057
|
jbe@532
|
3058
|
jbe@532
|
3059 CREATE OR REPLACE FUNCTION "check_issue"
|
jbe@532
|
3060 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
3061 "persist" "check_issue_persistence" )
|
jbe@532
|
3062 RETURNS "check_issue_persistence"
|
jbe@532
|
3063 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3064 DECLARE
|
jbe@532
|
3065 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
3066 "last_calculated_v" "snapshot"."calculated"%TYPE;
|
jbe@532
|
3067 "policy_row" "policy"%ROWTYPE;
|
jbe@532
|
3068 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
3069 "state_v" "issue_state";
|
jbe@532
|
3070 BEGIN
|
jbe@532
|
3071 PERFORM "require_transaction_isolation"();
|
jbe@532
|
3072 IF "persist" ISNULL THEN
|
jbe@532
|
3073 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3074 FOR UPDATE;
|
jbe@532
|
3075 SELECT "calculated" INTO "last_calculated_v"
|
jbe@532
|
3076 FROM "snapshot" JOIN "snapshot_issue"
|
jbe@532
|
3077 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
|
jbe@532
|
3078 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
|
jbe@532
|
3079 IF "issue_row"."closed" NOTNULL THEN
|
jbe@532
|
3080 RETURN NULL;
|
jbe@532
|
3081 END IF;
|
jbe@532
|
3082 "persist"."state" := "issue_row"."state";
|
jbe@532
|
3083 IF
|
jbe@532
|
3084 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
|
jbe@532
|
3085 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
|
jbe@532
|
3086 ( "issue_row"."state" = 'discussion' AND now() >=
|
jbe@532
|
3087 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
|
jbe@532
|
3088 ( "issue_row"."state" = 'verification' AND now() >=
|
jbe@532
|
3089 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
|
jbe@532
|
3090 ( "issue_row"."state" = 'voting' AND now() >=
|
jbe@532
|
3091 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
|
jbe@532
|
3092 THEN
|
jbe@532
|
3093 "persist"."phase_finished" := TRUE;
|
jbe@532
|
3094 ELSE
|
jbe@532
|
3095 "persist"."phase_finished" := FALSE;
|
jbe@532
|
3096 END IF;
|
jbe@532
|
3097 IF
|
jbe@532
|
3098 NOT EXISTS (
|
jbe@532
|
3099 -- all initiatives are revoked
|
jbe@532
|
3100 SELECT NULL FROM "initiative"
|
jbe@532
|
3101 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@532
|
3102 ) AND (
|
jbe@532
|
3103 -- and issue has not been accepted yet
|
jbe@532
|
3104 "persist"."state" = 'admission' OR
|
jbe@532
|
3105 -- or verification time has elapsed
|
jbe@532
|
3106 ( "persist"."state" = 'verification' AND
|
jbe@532
|
3107 "persist"."phase_finished" ) OR
|
jbe@532
|
3108 -- or no initiatives have been revoked lately
|
jbe@532
|
3109 NOT EXISTS (
|
jbe@532
|
3110 SELECT NULL FROM "initiative"
|
jbe@532
|
3111 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
3112 AND now() < "revoked" + "issue_row"."verification_time"
|
jbe@532
|
3113 )
|
jbe@532
|
3114 )
|
jbe@532
|
3115 THEN
|
jbe@532
|
3116 "persist"."issue_revoked" := TRUE;
|
jbe@532
|
3117 ELSE
|
jbe@532
|
3118 "persist"."issue_revoked" := FALSE;
|
jbe@532
|
3119 END IF;
|
jbe@532
|
3120 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
|
jbe@532
|
3121 UPDATE "issue" SET "phase_finished" = now()
|
jbe@532
|
3122 WHERE "id" = "issue_row"."id";
|
jbe@532
|
3123 RETURN "persist";
|
jbe@532
|
3124 ELSIF
|
jbe@532
|
3125 "persist"."state" IN ('admission', 'discussion', 'verification')
|
jbe@532
|
3126 THEN
|
jbe@532
|
3127 RETURN "persist";
|
jbe@532
|
3128 ELSE
|
jbe@532
|
3129 RETURN NULL;
|
jbe@532
|
3130 END IF;
|
jbe@532
|
3131 END IF;
|
jbe@532
|
3132 IF
|
jbe@532
|
3133 "persist"."state" IN ('admission', 'discussion', 'verification') AND
|
jbe@532
|
3134 coalesce("persist"."snapshot_created", FALSE) = FALSE
|
jbe@532
|
3135 THEN
|
jbe@532
|
3136 IF "persist"."state" != 'admission' THEN
|
jbe@532
|
3137 PERFORM "take_snapshot"("issue_id_p");
|
jbe@532
|
3138 PERFORM "finish_snapshot"("issue_id_p");
|
jbe@568
|
3139 ELSE
|
jbe@568
|
3140 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
|
jbe@568
|
3141 FROM "issue_quorum"
|
jbe@568
|
3142 WHERE "id" = "issue_id_p"
|
jbe@568
|
3143 AND "issue_quorum"."issue_id" = "issue_id_p";
|
jbe@532
|
3144 END IF;
|
jbe@532
|
3145 "persist"."snapshot_created" = TRUE;
|
jbe@532
|
3146 IF "persist"."phase_finished" THEN
|
jbe@532
|
3147 IF "persist"."state" = 'admission' THEN
|
jbe@561
|
3148 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3149 WHERE "id" = "issue_id_p";
|
jbe@532
|
3150 ELSIF "persist"."state" = 'discussion' THEN
|
jbe@561
|
3151 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3152 WHERE "id" = "issue_id_p";
|
jbe@532
|
3153 ELSIF "persist"."state" = 'verification' THEN
|
jbe@561
|
3154 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3155 WHERE "id" = "issue_id_p";
|
jbe@532
|
3156 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
3157 FOR "initiative_row" IN
|
jbe@532
|
3158 SELECT * FROM "initiative"
|
jbe@532
|
3159 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@532
|
3160 FOR UPDATE
|
jbe@532
|
3161 LOOP
|
jbe@532
|
3162 IF
|
jbe@568
|
3163 "initiative_row"."polling" OR
|
jbe@568
|
3164 "initiative_row"."satisfied_supporter_count" >=
|
jbe@568
|
3165 "issue_row"."initiative_quorum"
|
jbe@532
|
3166 THEN
|
jbe@532
|
3167 UPDATE "initiative" SET "admitted" = TRUE
|
jbe@532
|
3168 WHERE "id" = "initiative_row"."id";
|
jbe@532
|
3169 ELSE
|
jbe@532
|
3170 UPDATE "initiative" SET "admitted" = FALSE
|
jbe@532
|
3171 WHERE "id" = "initiative_row"."id";
|
jbe@532
|
3172 END IF;
|
jbe@532
|
3173 END LOOP;
|
jbe@532
|
3174 END IF;
|
jbe@532
|
3175 END IF;
|
jbe@532
|
3176 RETURN "persist";
|
jbe@532
|
3177 END IF;
|
jbe@532
|
3178 IF
|
jbe@532
|
3179 "persist"."state" IN ('admission', 'discussion', 'verification') AND
|
jbe@532
|
3180 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
|
jbe@532
|
3181 THEN
|
jbe@532
|
3182 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
|
jbe@532
|
3183 "persist"."harmonic_weights_set" = TRUE;
|
jbe@532
|
3184 IF
|
jbe@532
|
3185 "persist"."phase_finished" OR
|
jbe@532
|
3186 "persist"."issue_revoked" OR
|
jbe@532
|
3187 "persist"."state" = 'admission'
|
jbe@532
|
3188 THEN
|
jbe@532
|
3189 RETURN "persist";
|
jbe@532
|
3190 ELSE
|
jbe@532
|
3191 RETURN NULL;
|
jbe@532
|
3192 END IF;
|
jbe@532
|
3193 END IF;
|
jbe@532
|
3194 IF "persist"."issue_revoked" THEN
|
jbe@532
|
3195 IF "persist"."state" = 'admission' THEN
|
jbe@532
|
3196 "state_v" := 'canceled_revoked_before_accepted';
|
jbe@532
|
3197 ELSIF "persist"."state" = 'discussion' THEN
|
jbe@532
|
3198 "state_v" := 'canceled_after_revocation_during_discussion';
|
jbe@532
|
3199 ELSIF "persist"."state" = 'verification' THEN
|
jbe@532
|
3200 "state_v" := 'canceled_after_revocation_during_verification';
|
jbe@532
|
3201 END IF;
|
jbe@532
|
3202 UPDATE "issue" SET
|
jbe@532
|
3203 "state" = "state_v",
|
jbe@532
|
3204 "closed" = "phase_finished",
|
jbe@532
|
3205 "phase_finished" = NULL
|
jbe@532
|
3206 WHERE "id" = "issue_id_p";
|
jbe@532
|
3207 RETURN NULL;
|
jbe@532
|
3208 END IF;
|
jbe@532
|
3209 IF "persist"."state" = 'admission' THEN
|
jbe@532
|
3210 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3211 FOR UPDATE;
|
jbe@532
|
3212 IF "issue_row"."phase_finished" NOTNULL THEN
|
jbe@532
|
3213 UPDATE "issue" SET
|
jbe@532
|
3214 "state" = 'canceled_issue_not_accepted',
|
jbe@532
|
3215 "closed" = "phase_finished",
|
jbe@532
|
3216 "phase_finished" = NULL
|
jbe@532
|
3217 WHERE "id" = "issue_id_p";
|
jbe@532
|
3218 END IF;
|
jbe@532
|
3219 RETURN NULL;
|
jbe@532
|
3220 END IF;
|
jbe@532
|
3221 IF "persist"."phase_finished" THEN
|
jbe@532
|
3222 IF "persist"."state" = 'discussion' THEN
|
jbe@532
|
3223 UPDATE "issue" SET
|
jbe@532
|
3224 "state" = 'verification',
|
jbe@532
|
3225 "half_frozen" = "phase_finished",
|
jbe@532
|
3226 "phase_finished" = NULL
|
jbe@532
|
3227 WHERE "id" = "issue_id_p";
|
jbe@532
|
3228 RETURN NULL;
|
jbe@532
|
3229 END IF;
|
jbe@532
|
3230 IF "persist"."state" = 'verification' THEN
|
jbe@532
|
3231 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3232 FOR UPDATE;
|
jbe@532
|
3233 SELECT * INTO "policy_row" FROM "policy"
|
jbe@532
|
3234 WHERE "id" = "issue_row"."policy_id";
|
jbe@532
|
3235 IF EXISTS (
|
jbe@532
|
3236 SELECT NULL FROM "initiative"
|
jbe@532
|
3237 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
jbe@532
|
3238 ) THEN
|
jbe@532
|
3239 UPDATE "issue" SET
|
jbe@532
|
3240 "state" = 'voting',
|
jbe@532
|
3241 "fully_frozen" = "phase_finished",
|
jbe@532
|
3242 "phase_finished" = NULL
|
jbe@532
|
3243 WHERE "id" = "issue_id_p";
|
jbe@532
|
3244 ELSE
|
jbe@532
|
3245 UPDATE "issue" SET
|
jbe@532
|
3246 "state" = 'canceled_no_initiative_admitted',
|
jbe@532
|
3247 "fully_frozen" = "phase_finished",
|
jbe@532
|
3248 "closed" = "phase_finished",
|
jbe@532
|
3249 "phase_finished" = NULL
|
jbe@532
|
3250 WHERE "id" = "issue_id_p";
|
jbe@532
|
3251 -- NOTE: The following DELETE statements have effect only when
|
jbe@532
|
3252 -- issue state has been manipulated
|
jbe@532
|
3253 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3254 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3255 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3256 END IF;
|
jbe@532
|
3257 RETURN NULL;
|
jbe@532
|
3258 END IF;
|
jbe@532
|
3259 IF "persist"."state" = 'voting' THEN
|
jbe@532
|
3260 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
|
jbe@532
|
3261 PERFORM "close_voting"("issue_id_p");
|
jbe@532
|
3262 "persist"."closed_voting" = TRUE;
|
jbe@532
|
3263 RETURN "persist";
|
jbe@532
|
3264 END IF;
|
jbe@532
|
3265 PERFORM "calculate_ranks"("issue_id_p");
|
jbe@532
|
3266 RETURN NULL;
|
jbe@532
|
3267 END IF;
|
jbe@532
|
3268 END IF;
|
jbe@532
|
3269 RAISE WARNING 'should not happen';
|
jbe@532
|
3270 RETURN NULL;
|
jbe@532
|
3271 END;
|
jbe@532
|
3272 $$;
|
jbe@532
|
3273
|
jbe@532
|
3274
|
jbe@532
|
3275 CREATE OR REPLACE FUNCTION "check_everything"()
|
jbe@532
|
3276 RETURNS VOID
|
jbe@532
|
3277 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3278 DECLARE
|
jbe@532
|
3279 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
3280 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
3281 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
3282 "persist_v" "check_issue_persistence";
|
jbe@532
|
3283 BEGIN
|
jbe@532
|
3284 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
|
jbe@532
|
3285 DELETE FROM "expired_session";
|
jbe@532
|
3286 DELETE FROM "expired_token";
|
jbe@532
|
3287 DELETE FROM "expired_snapshot";
|
jbe@532
|
3288 PERFORM "check_activity"();
|
jbe@532
|
3289 PERFORM "calculate_member_counts"();
|
jbe@532
|
3290 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
|
jbe@532
|
3291 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
|
jbe@532
|
3292 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
|
jbe@532
|
3293 WHERE "snapshot_id" = "snapshot_id_v";
|
jbe@532
|
3294 LOOP
|
jbe@532
|
3295 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
|
jbe@532
|
3296 END LOOP;
|
jbe@532
|
3297 END LOOP;
|
jbe@532
|
3298 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
|
jbe@532
|
3299 "persist_v" := NULL;
|
jbe@532
|
3300 LOOP
|
jbe@532
|
3301 "persist_v" := "check_issue"("issue_id_v", "persist_v");
|
jbe@532
|
3302 EXIT WHEN "persist_v" ISNULL;
|
jbe@532
|
3303 END LOOP;
|
jbe@532
|
3304 END LOOP;
|
jbe@532
|
3305 RETURN;
|
jbe@532
|
3306 END;
|
jbe@532
|
3307 $$;
|
jbe@532
|
3308
|
jbe@532
|
3309 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks, this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments. For production, use lf_update binary instead';
|
jbe@532
|
3310
|
jbe@532
|
3311
|
jbe@532
|
3312 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@532
|
3313 RETURNS VOID
|
jbe@532
|
3314 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3315 BEGIN
|
jbe@532
|
3316 IF EXISTS (
|
jbe@532
|
3317 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
|
jbe@532
|
3318 ) THEN
|
jbe@532
|
3319 -- override protection triggers:
|
jbe@532
|
3320 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@532
|
3321 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@532
|
3322 -- clean data:
|
jbe@532
|
3323 DELETE FROM "delegating_voter"
|
jbe@532
|
3324 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3325 DELETE FROM "direct_voter"
|
jbe@532
|
3326 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3327 DELETE FROM "delegating_interest_snapshot"
|
jbe@532
|
3328 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3329 DELETE FROM "direct_interest_snapshot"
|
jbe@532
|
3330 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3331 DELETE FROM "non_voter"
|
jbe@532
|
3332 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3333 DELETE FROM "delegation"
|
jbe@532
|
3334 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3335 DELETE FROM "supporter"
|
jbe@532
|
3336 USING "initiative" -- NOTE: due to missing index on issue_id
|
jbe@532
|
3337 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@532
|
3338 AND "supporter"."initiative_id" = "initiative_id";
|
jbe@532
|
3339 -- mark issue as cleaned:
|
jbe@532
|
3340 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
|
jbe@532
|
3341 -- finish overriding protection triggers (avoids garbage):
|
jbe@532
|
3342 DELETE FROM "temporary_transaction_data"
|
jbe@532
|
3343 WHERE "key" = 'override_protection_triggers';
|
jbe@532
|
3344 END IF;
|
jbe@532
|
3345 RETURN;
|
jbe@532
|
3346 END;
|
jbe@532
|
3347 $$;
|
jbe@532
|
3348
|
jbe@532
|
3349
|
jbe@532
|
3350 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
jbe@532
|
3351 RETURNS VOID
|
jbe@532
|
3352 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3353 BEGIN
|
jbe@532
|
3354 UPDATE "member" SET
|
jbe@532
|
3355 "last_login" = NULL,
|
jbe@532
|
3356 "last_delegation_check" = NULL,
|
jbe@532
|
3357 "login" = NULL,
|
jbe@532
|
3358 "password" = NULL,
|
jbe@532
|
3359 "authority" = NULL,
|
jbe@532
|
3360 "authority_uid" = NULL,
|
jbe@532
|
3361 "authority_login" = NULL,
|
jbe@552
|
3362 "deleted" = coalesce("deleted", now()),
|
jbe@532
|
3363 "locked" = TRUE,
|
jbe@532
|
3364 "active" = FALSE,
|
jbe@532
|
3365 "notify_email" = NULL,
|
jbe@532
|
3366 "notify_email_unconfirmed" = NULL,
|
jbe@532
|
3367 "notify_email_secret" = NULL,
|
jbe@532
|
3368 "notify_email_secret_expiry" = NULL,
|
jbe@532
|
3369 "notify_email_lock_expiry" = NULL,
|
jbe@532
|
3370 "disable_notifications" = TRUE,
|
jbe@532
|
3371 "notification_counter" = DEFAULT,
|
jbe@532
|
3372 "notification_sample_size" = 0,
|
jbe@532
|
3373 "notification_dow" = NULL,
|
jbe@532
|
3374 "notification_hour" = NULL,
|
jbe@543
|
3375 "notification_sent" = NULL,
|
jbe@532
|
3376 "login_recovery_expiry" = NULL,
|
jbe@532
|
3377 "password_reset_secret" = NULL,
|
jbe@532
|
3378 "password_reset_secret_expiry" = NULL,
|
jbe@532
|
3379 "location" = NULL
|
jbe@532
|
3380 WHERE "id" = "member_id_p";
|
jbe@532
|
3381 -- "text_search_data" is updated by triggers
|
jbe@544
|
3382 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3383 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3384 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3385 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3386 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3387 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3388 DELETE FROM "session" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3389 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3390 DELETE FROM "token" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3391 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3392 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3393 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3394 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
jbe@532
|
3395 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3396 DELETE FROM "direct_voter" USING "issue"
|
jbe@532
|
3397 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@532
|
3398 AND "issue"."closed" ISNULL
|
jbe@532
|
3399 AND "member_id" = "member_id_p";
|
jbe@543
|
3400 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3401 RETURN;
|
jbe@532
|
3402 END;
|
jbe@532
|
3403 $$;
|
jbe@532
|
3404
|
jbe@532
|
3405
|
jbe@532
|
3406 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@532
|
3407 RETURNS VOID
|
jbe@532
|
3408 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3409 BEGIN
|
jbe@532
|
3410 DELETE FROM "temporary_transaction_data";
|
jbe@543
|
3411 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
3412 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@532
|
3413 UPDATE "member" SET
|
jbe@532
|
3414 "invite_code" = NULL,
|
jbe@532
|
3415 "invite_code_expiry" = NULL,
|
jbe@532
|
3416 "admin_comment" = NULL,
|
jbe@532
|
3417 "last_login" = NULL,
|
jbe@532
|
3418 "last_delegation_check" = NULL,
|
jbe@532
|
3419 "login" = NULL,
|
jbe@532
|
3420 "password" = NULL,
|
jbe@532
|
3421 "authority" = NULL,
|
jbe@532
|
3422 "authority_uid" = NULL,
|
jbe@532
|
3423 "authority_login" = NULL,
|
jbe@532
|
3424 "lang" = NULL,
|
jbe@532
|
3425 "notify_email" = NULL,
|
jbe@532
|
3426 "notify_email_unconfirmed" = NULL,
|
jbe@532
|
3427 "notify_email_secret" = NULL,
|
jbe@532
|
3428 "notify_email_secret_expiry" = NULL,
|
jbe@532
|
3429 "notify_email_lock_expiry" = NULL,
|
jbe@532
|
3430 "disable_notifications" = TRUE,
|
jbe@532
|
3431 "notification_counter" = DEFAULT,
|
jbe@532
|
3432 "notification_sample_size" = 0,
|
jbe@532
|
3433 "notification_dow" = NULL,
|
jbe@532
|
3434 "notification_hour" = NULL,
|
jbe@543
|
3435 "notification_sent" = NULL,
|
jbe@532
|
3436 "login_recovery_expiry" = NULL,
|
jbe@532
|
3437 "password_reset_secret" = NULL,
|
jbe@532
|
3438 "password_reset_secret_expiry" = NULL,
|
jbe@532
|
3439 "location" = NULL;
|
jbe@532
|
3440 -- "text_search_data" is updated by triggers
|
jbe@557
|
3441 DELETE FROM "verification";
|
jbe@544
|
3442 DELETE FROM "member_settings";
|
jbe@544
|
3443 DELETE FROM "member_useterms";
|
jbe@543
|
3444 DELETE FROM "member_profile";
|
jbe@543
|
3445 DELETE FROM "rendered_member_statement";
|
jbe@532
|
3446 DELETE FROM "member_image";
|
jbe@532
|
3447 DELETE FROM "contact";
|
jbe@532
|
3448 DELETE FROM "ignored_member";
|
jbe@532
|
3449 DELETE FROM "session";
|
jbe@543
|
3450 DELETE FROM "system_application";
|
jbe@543
|
3451 DELETE FROM "system_application_redirect_uri";
|
jbe@543
|
3452 DELETE FROM "dynamic_application_scope";
|
jbe@543
|
3453 DELETE FROM "member_application";
|
jbe@543
|
3454 DELETE FROM "token";
|
jbe@543
|
3455 DELETE FROM "subscription";
|
jbe@543
|
3456 DELETE FROM "ignored_area";
|
jbe@532
|
3457 DELETE FROM "ignored_initiative";
|
jbe@532
|
3458 DELETE FROM "non_voter";
|
jbe@532
|
3459 DELETE FROM "direct_voter" USING "issue"
|
jbe@532
|
3460 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@532
|
3461 AND "issue"."closed" ISNULL;
|
jbe@543
|
3462 DELETE FROM "event_processed";
|
jbe@543
|
3463 DELETE FROM "notification_initiative_sent";
|
jbe@543
|
3464 DELETE FROM "newsletter";
|
jbe@532
|
3465 RETURN;
|
jbe@532
|
3466 END;
|
jbe@532
|
3467 $$;
|
jbe@532
|
3468
|
jbe@532
|
3469
|
jbe@532
|
3470 CREATE TEMPORARY TABLE "old_snapshot" AS
|
jbe@532
|
3471 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
|
jbe@532
|
3472 FROM (
|
jbe@532
|
3473 SELECT * FROM (
|
jbe@532
|
3474 SELECT
|
jbe@532
|
3475 "id" AS "issue_id",
|
jbe@532
|
3476 'end_of_admission'::"snapshot_event" AS "event",
|
jbe@532
|
3477 "accepted" AS "calculated"
|
jbe@532
|
3478 FROM "issue" WHERE "accepted" NOTNULL
|
jbe@532
|
3479 UNION ALL
|
jbe@532
|
3480 SELECT
|
jbe@532
|
3481 "id" AS "issue_id",
|
jbe@532
|
3482 'half_freeze'::"snapshot_event" AS "event",
|
jbe@532
|
3483 "half_frozen" AS "calculated"
|
jbe@532
|
3484 FROM "issue" WHERE "half_frozen" NOTNULL
|
jbe@532
|
3485 UNION ALL
|
jbe@532
|
3486 SELECT
|
jbe@532
|
3487 "id" AS "issue_id",
|
jbe@532
|
3488 'full_freeze'::"snapshot_event" AS "event",
|
jbe@532
|
3489 "fully_frozen" AS "calculated"
|
jbe@532
|
3490 FROM "issue" WHERE "fully_frozen" NOTNULL
|
jbe@532
|
3491 ) AS "unordered"
|
jbe@532
|
3492 ORDER BY "calculated", "issue_id", "event"
|
jbe@532
|
3493 ) AS "ordered";
|
jbe@532
|
3494
|
jbe@532
|
3495
|
jbe@532
|
3496 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
|
jbe@532
|
3497 SELECT
|
jbe@532
|
3498 "old_snapshot"."snapshot_id" AS "id",
|
jbe@532
|
3499 "old_snapshot"."calculated",
|
jbe@532
|
3500 ( SELECT COALESCE(sum("weight"), 0)
|
jbe@532
|
3501 FROM "direct_population_snapshot" "dps"
|
jbe@532
|
3502 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
|
jbe@532
|
3503 AND "dps"."event" = "old_snapshot"."event"
|
jbe@532
|
3504 ) AS "population",
|
jbe@532
|
3505 "issue"."area_id" AS "area_id",
|
jbe@532
|
3506 "issue"."id" AS "issue_id"
|
jbe@532
|
3507 FROM "old_snapshot" JOIN "issue"
|
jbe@532
|
3508 ON "old_snapshot"."issue_id" = "issue"."id";
|
jbe@532
|
3509
|
jbe@532
|
3510
|
jbe@532
|
3511 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
|
jbe@532
|
3512 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
|
jbe@532
|
3513
|
jbe@532
|
3514
|
jbe@532
|
3515 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
3516 SELECT
|
jbe@532
|
3517 "old_snapshot"."snapshot_id",
|
jbe@532
|
3518 "direct_population_snapshot"."member_id"
|
jbe@532
|
3519 FROM "old_snapshot" JOIN "direct_population_snapshot"
|
jbe@532
|
3520 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
|
jbe@532
|
3521 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
|
jbe@532
|
3522
|
jbe@532
|
3523 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
3524 SELECT
|
jbe@532
|
3525 "old_snapshot"."snapshot_id",
|
jbe@532
|
3526 "delegating_population_snapshot"."member_id"
|
jbe@532
|
3527 FROM "old_snapshot" JOIN "delegating_population_snapshot"
|
jbe@532
|
3528 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
|
jbe@532
|
3529 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
|
jbe@532
|
3530
|
jbe@532
|
3531
|
jbe@532
|
3532 INSERT INTO "direct_interest_snapshot"
|
jbe@532
|
3533 ("snapshot_id", "issue_id", "member_id", "weight")
|
jbe@532
|
3534 SELECT
|
jbe@532
|
3535 "old_snapshot"."snapshot_id",
|
jbe@532
|
3536 "old_snapshot"."issue_id",
|
jbe@532
|
3537 "direct_interest_snapshot_old"."member_id",
|
jbe@532
|
3538 "direct_interest_snapshot_old"."weight"
|
jbe@532
|
3539 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
|
jbe@532
|
3540 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
|
jbe@532
|
3541 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
|
jbe@532
|
3542
|
jbe@532
|
3543 INSERT INTO "delegating_interest_snapshot"
|
jbe@532
|
3544 ( "snapshot_id", "issue_id",
|
jbe@532
|
3545 "member_id", "weight", "scope", "delegate_member_ids" )
|
jbe@532
|
3546 SELECT
|
jbe@532
|
3547 "old_snapshot"."snapshot_id",
|
jbe@532
|
3548 "old_snapshot"."issue_id",
|
jbe@532
|
3549 "delegating_interest_snapshot_old"."member_id",
|
jbe@532
|
3550 "delegating_interest_snapshot_old"."weight",
|
jbe@532
|
3551 "delegating_interest_snapshot_old"."scope",
|
jbe@532
|
3552 "delegating_interest_snapshot_old"."delegate_member_ids"
|
jbe@532
|
3553 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
|
jbe@532
|
3554 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
|
jbe@532
|
3555 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
|
jbe@532
|
3556
|
jbe@532
|
3557 INSERT INTO "direct_supporter_snapshot"
|
jbe@532
|
3558 ( "snapshot_id", "issue_id",
|
jbe@532
|
3559 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
|
jbe@532
|
3560 SELECT
|
jbe@532
|
3561 "old_snapshot"."snapshot_id",
|
jbe@532
|
3562 "old_snapshot"."issue_id",
|
jbe@532
|
3563 "direct_supporter_snapshot_old"."initiative_id",
|
jbe@532
|
3564 "direct_supporter_snapshot_old"."member_id",
|
jbe@532
|
3565 "direct_supporter_snapshot_old"."draft_id",
|
jbe@532
|
3566 "direct_supporter_snapshot_old"."informed",
|
jbe@532
|
3567 "direct_supporter_snapshot_old"."satisfied"
|
jbe@532
|
3568 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
|
jbe@532
|
3569 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
|
jbe@532
|
3570 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
|
jbe@532
|
3571
|
jbe@532
|
3572
|
jbe@532
|
3573 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
|
jbe@532
|
3574
|
jbe@532
|
3575 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
|
jbe@532
|
3576 FROM (
|
jbe@532
|
3577 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
|
jbe@532
|
3578 FROM "snapshot" ORDER BY "issue_id", "id" DESC
|
jbe@532
|
3579 ) AS "snapshot"
|
jbe@532
|
3580 WHERE "snapshot"."issue_id" = "issue"."id";
|
jbe@532
|
3581
|
jbe@532
|
3582 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3583 FROM "old_snapshot"
|
jbe@532
|
3584 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3585 AND "old_snapshot"."event" = 'end_of_admission';
|
jbe@532
|
3586
|
jbe@532
|
3587 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3588 FROM "old_snapshot"
|
jbe@532
|
3589 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3590 AND "old_snapshot"."event" = 'half_freeze';
|
jbe@532
|
3591
|
jbe@532
|
3592 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3593 FROM "old_snapshot"
|
jbe@532
|
3594 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3595 AND "old_snapshot"."event" = 'full_freeze';
|
jbe@532
|
3596
|
jbe@532
|
3597 ALTER TABLE "issue" ENABLE TRIGGER USER;
|
jbe@532
|
3598
|
jbe@532
|
3599
|
jbe@532
|
3600 DROP TABLE "old_snapshot";
|
jbe@532
|
3601
|
jbe@532
|
3602 DROP TABLE "direct_supporter_snapshot_old";
|
jbe@532
|
3603 DROP TABLE "delegating_interest_snapshot_old";
|
jbe@532
|
3604 DROP TABLE "direct_interest_snapshot_old";
|
jbe@532
|
3605 DROP TABLE "delegating_population_snapshot";
|
jbe@532
|
3606 DROP TABLE "direct_population_snapshot";
|
jbe@532
|
3607
|
jbe@532
|
3608
|
jbe@532
|
3609 DROP VIEW "open_issue";
|
jbe@532
|
3610
|
jbe@532
|
3611
|
jbe@532
|
3612 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
|
jbe@532
|
3613
|
jbe@532
|
3614
|
jbe@532
|
3615 CREATE VIEW "open_issue" AS
|
jbe@532
|
3616 SELECT * FROM "issue" WHERE "closed" ISNULL;
|
jbe@532
|
3617
|
jbe@532
|
3618 COMMENT ON VIEW "open_issue" IS 'All open issues';
|
jbe@532
|
3619
|
jbe@532
|
3620
|
jbe@532
|
3621 -- NOTE: create "issue_for_admission" view after altering table "issue"
|
jbe@532
|
3622 CREATE VIEW "issue_for_admission" AS
|
jbe@532
|
3623 SELECT DISTINCT ON ("issue"."area_id")
|
jbe@532
|
3624 "issue".*,
|
jbe@532
|
3625 max("initiative"."supporter_count") AS "max_supporter_count"
|
jbe@532
|
3626 FROM "issue"
|
jbe@532
|
3627 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@532
|
3628 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
3629 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@532
|
3630 WHERE "issue"."state" = 'admission'::"issue_state"
|
jbe@532
|
3631 AND now() >= "issue"."created" + "issue"."min_admission_time"
|
jbe@532
|
3632 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
|
jbe@532
|
3633 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
|
jbe@532
|
3634 "issue"."population" * "policy"."issue_quorum_num"
|
jbe@532
|
3635 AND "initiative"."supporter_count" >= "area"."issue_quorum"
|
jbe@532
|
3636 AND "initiative"."revoked" ISNULL
|
jbe@532
|
3637 GROUP BY "issue"."id"
|
jbe@532
|
3638 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
|
jbe@532
|
3639
|
jbe@532
|
3640 COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
|
jbe@532
|
3641
|
jbe@532
|
3642
|
jbe@532
|
3643 DROP TYPE "snapshot_event";
|
jbe@532
|
3644
|
jbe@532
|
3645
|
jbe@532
|
3646 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
|
jbe@532
|
3647 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
|
jbe@532
|
3648 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
|
jbe@532
|
3649
|
jbe@532
|
3650
|
jbe@532
|
3651 COMMIT;
|