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@571
|
27 SELECT * FROM (VALUES ('4.0.0', 4, 0, 0))
|
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@570
|
582 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
|
jbe@570
|
583
|
jbe@570
|
584 UPDATE "issue" SET
|
jbe@570
|
585 "issue_quorum" = ceil(
|
jbe@570
|
586 ("subquery"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /
|
jbe@570
|
587 "policy"."issue_quorum_den"::FLOAT8
|
jbe@570
|
588 ),
|
jbe@570
|
589 "initiative_quorum" = ceil(
|
jbe@570
|
590 ("issue"."population"::INT8 * "policy"."initiative_quorum_num"::INT8) /
|
jbe@570
|
591 "policy"."initiative_quorum_den"::FLOAT8
|
jbe@570
|
592 )
|
jbe@570
|
593 FROM (
|
jbe@570
|
594 SELECT "issue_id", sum("weight") AS "population"
|
jbe@570
|
595 FROM "direct_population_snapshot"
|
jbe@570
|
596 WHERE "event" = 'end_of_admission'
|
jbe@570
|
597 GROUP BY "issue_id"
|
jbe@570
|
598 ) AS "subquery", "policy"
|
jbe@570
|
599 WHERE "issue"."id" = "subquery"."issue_id"
|
jbe@570
|
600 AND "issue"."policy_id" = "policy"."id";
|
jbe@570
|
601
|
jbe@570
|
602 ALTER TABLE "issue" ENABLE TRIGGER USER;
|
jbe@570
|
603
|
jbe@570
|
604
|
jbe@532
|
605 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
606
|
jbe@532
|
607
|
jbe@532
|
608 ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";
|
jbe@532
|
609 ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
jbe@532
|
610
|
jbe@532
|
611 ALTER TABLE "initiative" ADD COLUMN "location" JSONB;
|
jbe@532
|
612 ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;
|
jbe@532
|
613
|
jbe@532
|
614 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
615 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
|
jbe@532
|
616
|
jbe@532
|
617 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
|
jbe@532
|
618
|
jbe@532
|
619
|
jbe@532
|
620 ALTER TABLE "draft" ADD COLUMN "location" JSONB;
|
jbe@532
|
621
|
jbe@532
|
622 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
623
|
jbe@532
|
624 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
|
625
|
jbe@532
|
626
|
jbe@532
|
627 ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;
|
jbe@532
|
628
|
jbe@532
|
629 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
|
jbe@532
|
630
|
jbe@532
|
631 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';
|
jbe@532
|
632
|
jbe@532
|
633
|
jbe@532
|
634 CREATE TABLE "temporary_suggestion_counts" (
|
jbe@532
|
635 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
636 "minus2_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
637 "minus2_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
638 "minus1_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
639 "minus1_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
640 "plus1_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
641 "plus1_fulfilled_count" INT4 NOT NULL,
|
jbe@532
|
642 "plus2_unfulfilled_count" INT4 NOT NULL,
|
jbe@532
|
643 "plus2_fulfilled_count" INT4 NOT NULL );
|
jbe@532
|
644
|
jbe@532
|
645 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
|
jbe@532
|
646
|
jbe@532
|
647 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
|
648
|
jbe@532
|
649
|
jbe@532
|
650 ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";
|
jbe@532
|
651 ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
652
|
jbe@532
|
653
|
jbe@532
|
654 ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";
|
jbe@532
|
655 ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
656
|
jbe@532
|
657
|
jbe@532
|
658 ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";
|
jbe@532
|
659 ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
660
|
jbe@532
|
661
|
jbe@532
|
662 CREATE TABLE "snapshot_issue" (
|
jbe@532
|
663 PRIMARY KEY ("snapshot_id", "issue_id"),
|
jbe@532
|
664 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
665 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@532
|
666 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
|
jbe@532
|
667
|
jbe@532
|
668 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
|
jbe@532
|
669
|
jbe@532
|
670 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
|
671
|
jbe@532
|
672
|
jbe@532
|
673 ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old"; -- TODO!
|
jbe@532
|
674 ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";
|
jbe@532
|
675 ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";
|
jbe@532
|
676
|
jbe@532
|
677 ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old"; -- TODO!
|
jbe@532
|
678 ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";
|
jbe@532
|
679 ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";
|
jbe@532
|
680
|
jbe@532
|
681 ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old"; -- TODO!
|
jbe@532
|
682 ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";
|
jbe@532
|
683 ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";
|
jbe@532
|
684
|
jbe@532
|
685
|
jbe@532
|
686 CREATE TABLE "direct_interest_snapshot" (
|
jbe@532
|
687 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
|
jbe@532
|
688 "snapshot_id" INT8,
|
jbe@532
|
689 "issue_id" INT4,
|
jbe@532
|
690 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
691 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
692 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
693 "weight" INT4 );
|
jbe@532
|
694 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
|
jbe@532
|
695
|
jbe@532
|
696 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
|
697
|
jbe@532
|
698 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
|
jbe@532
|
699
|
jbe@532
|
700
|
jbe@532
|
701 CREATE TABLE "delegating_interest_snapshot" (
|
jbe@532
|
702 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
|
jbe@532
|
703 "snapshot_id" INT8,
|
jbe@532
|
704 "issue_id" INT4,
|
jbe@532
|
705 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
706 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
707 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
708 "weight" INT4,
|
jbe@532
|
709 "scope" "delegation_scope" NOT NULL,
|
jbe@532
|
710 "delegate_member_ids" INT4[] NOT NULL );
|
jbe@532
|
711 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
|
jbe@532
|
712
|
jbe@532
|
713 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
|
714
|
jbe@532
|
715 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
|
jbe@532
|
716 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
|
jbe@532
|
717 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
|
718
|
jbe@532
|
719
|
jbe@532
|
720 CREATE TABLE "direct_supporter_snapshot" (
|
jbe@532
|
721 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
|
jbe@532
|
722 "snapshot_id" INT8,
|
jbe@532
|
723 "issue_id" INT4 NOT NULL,
|
jbe@532
|
724 FOREIGN KEY ("snapshot_id", "issue_id")
|
jbe@532
|
725 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
726 "initiative_id" INT4,
|
jbe@532
|
727 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
jbe@532
|
728 "draft_id" INT8 NOT NULL,
|
jbe@532
|
729 "informed" BOOLEAN NOT NULL,
|
jbe@532
|
730 "satisfied" BOOLEAN NOT NULL,
|
jbe@532
|
731 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@532
|
732 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
|
jbe@532
|
733 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
|
734 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
|
jbe@532
|
735
|
jbe@532
|
736 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
|
737
|
jbe@532
|
738 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
|
739 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
|
jbe@532
|
740 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
|
jbe@532
|
741
|
jbe@532
|
742
|
jbe@532
|
743 ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";
|
jbe@532
|
744 DROP INDEX "non_voter_member_id_idx";
|
jbe@532
|
745
|
jbe@532
|
746 ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");
|
jbe@532
|
747 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
|
jbe@532
|
748
|
jbe@532
|
749
|
jbe@545
|
750 INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")
|
jbe@545
|
751 SELECT
|
jbe@545
|
752 "member_id",
|
jbe@545
|
753 regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",
|
jbe@545
|
754 regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"
|
jbe@545
|
755 FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';
|
jbe@545
|
756
|
jbe@545
|
757
|
jbe@542
|
758 DROP TABLE "setting";
|
jbe@542
|
759 DROP TABLE "setting_map";
|
jbe@542
|
760 DROP TABLE "member_relation_setting";
|
jbe@542
|
761 DROP TABLE "unit_setting";
|
jbe@542
|
762 DROP TABLE "area_setting";
|
jbe@542
|
763 DROP TABLE "initiative_setting";
|
jbe@542
|
764 DROP TABLE "suggestion_setting";
|
jbe@542
|
765
|
jbe@542
|
766
|
jbe@532
|
767 ALTER TABLE "event" ADD COLUMN "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
jbe@532
|
768 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope";
|
jbe@532
|
769 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
770 ALTER TABLE "event" ADD COLUMN "area_id" INT4;
|
jbe@536
|
771 ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
772 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN;
|
jbe@532
|
773 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4;
|
jbe@532
|
774 ALTER TABLE "event" ADD COLUMN "text_value" TEXT;
|
jbe@532
|
775 ALTER TABLE "event" ADD COLUMN "old_text_value" TEXT;
|
jbe@532
|
776
|
jbe@532
|
777 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
778 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@536
|
779 ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
|
jbe@532
|
780
|
jbe@532
|
781 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
|
jbe@532
|
782 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
|
jbe@532
|
783 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";
|
jbe@532
|
784 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";
|
jbe@532
|
785
|
jbe@553
|
786 UPDATE "event" SET
|
jbe@553
|
787 "unit_id" = "area"."unit_id",
|
jbe@553
|
788 "area_id" = "issue"."area_id",
|
jbe@553
|
789 "policy_id" = "issue"."policy_id"
|
jbe@532
|
790 FROM "issue", "area"
|
jbe@532
|
791 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
|
jbe@532
|
792
|
jbe@532
|
793 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
|
jbe@532
|
794 "event" != 'issue_state_changed' OR (
|
jbe@532
|
795 "member_id" ISNULL AND
|
jbe@532
|
796 "other_member_id" ISNULL AND
|
jbe@532
|
797 "scope" ISNULL AND
|
jbe@532
|
798 "unit_id" NOTNULL AND
|
jbe@532
|
799 "area_id" NOTNULL AND
|
jbe@536
|
800 "policy_id" NOTNULL AND
|
jbe@532
|
801 "issue_id" NOTNULL AND
|
jbe@532
|
802 "state" NOTNULL AND
|
jbe@532
|
803 "initiative_id" ISNULL AND
|
jbe@532
|
804 "draft_id" ISNULL AND
|
jbe@532
|
805 "suggestion_id" ISNULL AND
|
jbe@532
|
806 "boolean_value" ISNULL AND
|
jbe@532
|
807 "numeric_value" ISNULL AND
|
jbe@532
|
808 "text_value" ISNULL AND
|
jbe@532
|
809 "old_text_value" ISNULL ));
|
jbe@532
|
810 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
|
jbe@532
|
811 "event" NOT IN (
|
jbe@532
|
812 'initiative_created_in_new_issue',
|
jbe@532
|
813 'initiative_created_in_existing_issue',
|
jbe@532
|
814 'initiative_revoked',
|
jbe@532
|
815 'new_draft_created'
|
jbe@532
|
816 ) OR (
|
jbe@532
|
817 "member_id" NOTNULL AND
|
jbe@532
|
818 "other_member_id" ISNULL AND
|
jbe@532
|
819 "scope" ISNULL AND
|
jbe@532
|
820 "unit_id" NOTNULL AND
|
jbe@532
|
821 "area_id" NOTNULL AND
|
jbe@536
|
822 "policy_id" NOTNULL AND
|
jbe@532
|
823 "issue_id" NOTNULL AND
|
jbe@532
|
824 "state" NOTNULL AND
|
jbe@532
|
825 "initiative_id" NOTNULL AND
|
jbe@532
|
826 "draft_id" NOTNULL AND
|
jbe@532
|
827 "suggestion_id" ISNULL AND
|
jbe@532
|
828 "boolean_value" ISNULL AND
|
jbe@532
|
829 "numeric_value" ISNULL AND
|
jbe@532
|
830 "text_value" ISNULL AND
|
jbe@532
|
831 "old_text_value" ISNULL ));
|
jbe@532
|
832 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
|
jbe@532
|
833 "event" != 'suggestion_created' OR (
|
jbe@532
|
834 "member_id" NOTNULL AND
|
jbe@532
|
835 "other_member_id" ISNULL AND
|
jbe@532
|
836 "scope" ISNULL AND
|
jbe@532
|
837 "unit_id" NOTNULL AND
|
jbe@532
|
838 "area_id" NOTNULL AND
|
jbe@536
|
839 "policy_id" NOTNULL AND
|
jbe@532
|
840 "issue_id" NOTNULL AND
|
jbe@532
|
841 "state" NOTNULL AND
|
jbe@532
|
842 "initiative_id" NOTNULL AND
|
jbe@532
|
843 "draft_id" ISNULL AND
|
jbe@532
|
844 "suggestion_id" NOTNULL AND
|
jbe@532
|
845 "boolean_value" ISNULL AND
|
jbe@532
|
846 "numeric_value" ISNULL AND
|
jbe@532
|
847 "text_value" ISNULL AND
|
jbe@532
|
848 "old_text_value" ISNULL ));
|
jbe@532
|
849 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
|
jbe@554
|
850 "event" != 'suggestion_deleted' OR (
|
jbe@532
|
851 "member_id" ISNULL AND
|
jbe@532
|
852 "other_member_id" ISNULL AND
|
jbe@532
|
853 "scope" ISNULL AND
|
jbe@532
|
854 "unit_id" NOTNULL AND
|
jbe@532
|
855 "area_id" NOTNULL AND
|
jbe@536
|
856 "policy_id" NOTNULL AND
|
jbe@532
|
857 "issue_id" NOTNULL AND
|
jbe@532
|
858 "state" NOTNULL AND
|
jbe@532
|
859 "initiative_id" NOTNULL AND
|
jbe@532
|
860 "draft_id" ISNULL AND
|
jbe@532
|
861 "suggestion_id" NOTNULL AND
|
jbe@532
|
862 "boolean_value" ISNULL AND
|
jbe@532
|
863 "numeric_value" ISNULL AND
|
jbe@532
|
864 "text_value" ISNULL AND
|
jbe@532
|
865 "old_text_value" ISNULL ));
|
jbe@532
|
866 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
|
jbe@532
|
867 "event" NOT IN (
|
jbe@532
|
868 'member_activated',
|
jbe@554
|
869 'member_deleted',
|
jbe@532
|
870 'member_profile_updated',
|
jbe@532
|
871 'member_image_updated'
|
jbe@532
|
872 ) OR (
|
jbe@532
|
873 "member_id" NOTNULL AND
|
jbe@532
|
874 "other_member_id" ISNULL AND
|
jbe@532
|
875 "scope" ISNULL AND
|
jbe@532
|
876 "unit_id" ISNULL AND
|
jbe@532
|
877 "area_id" ISNULL AND
|
jbe@536
|
878 "policy_id" ISNULL AND
|
jbe@532
|
879 "issue_id" ISNULL AND
|
jbe@532
|
880 "state" ISNULL AND
|
jbe@532
|
881 "initiative_id" ISNULL AND
|
jbe@532
|
882 "draft_id" ISNULL AND
|
jbe@532
|
883 "suggestion_id" ISNULL AND
|
jbe@532
|
884 "boolean_value" ISNULL AND
|
jbe@532
|
885 "numeric_value" ISNULL AND
|
jbe@532
|
886 "text_value" ISNULL AND
|
jbe@532
|
887 "old_text_value" ISNULL ));
|
jbe@532
|
888 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
|
jbe@532
|
889 "event" != 'member_active' OR (
|
jbe@532
|
890 "member_id" NOTNULL AND
|
jbe@532
|
891 "other_member_id" ISNULL AND
|
jbe@532
|
892 "scope" ISNULL AND
|
jbe@532
|
893 "unit_id" ISNULL AND
|
jbe@532
|
894 "area_id" ISNULL AND
|
jbe@536
|
895 "policy_id" ISNULL AND
|
jbe@532
|
896 "issue_id" ISNULL AND
|
jbe@532
|
897 "state" ISNULL AND
|
jbe@532
|
898 "initiative_id" ISNULL AND
|
jbe@532
|
899 "draft_id" ISNULL AND
|
jbe@532
|
900 "suggestion_id" ISNULL AND
|
jbe@532
|
901 "boolean_value" NOTNULL AND
|
jbe@532
|
902 "numeric_value" ISNULL AND
|
jbe@532
|
903 "text_value" ISNULL AND
|
jbe@532
|
904 "old_text_value" ISNULL ));
|
jbe@532
|
905 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
|
jbe@532
|
906 "event" != 'member_name_updated' OR (
|
jbe@532
|
907 "member_id" NOTNULL AND
|
jbe@532
|
908 "other_member_id" ISNULL AND
|
jbe@532
|
909 "scope" ISNULL AND
|
jbe@532
|
910 "unit_id" ISNULL AND
|
jbe@532
|
911 "area_id" ISNULL AND
|
jbe@536
|
912 "policy_id" ISNULL AND
|
jbe@532
|
913 "issue_id" ISNULL AND
|
jbe@532
|
914 "state" ISNULL AND
|
jbe@532
|
915 "initiative_id" ISNULL AND
|
jbe@532
|
916 "draft_id" ISNULL AND
|
jbe@532
|
917 "suggestion_id" ISNULL AND
|
jbe@532
|
918 "boolean_value" ISNULL AND
|
jbe@532
|
919 "numeric_value" ISNULL AND
|
jbe@532
|
920 "text_value" NOTNULL AND
|
jbe@532
|
921 "old_text_value" NOTNULL ));
|
jbe@532
|
922 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
|
jbe@532
|
923 "event" != 'interest' OR (
|
jbe@532
|
924 "member_id" NOTNULL AND
|
jbe@532
|
925 "other_member_id" ISNULL AND
|
jbe@532
|
926 "scope" ISNULL AND
|
jbe@532
|
927 "unit_id" NOTNULL AND
|
jbe@532
|
928 "area_id" NOTNULL AND
|
jbe@536
|
929 "policy_id" NOTNULL AND
|
jbe@532
|
930 "issue_id" NOTNULL AND
|
jbe@532
|
931 "state" NOTNULL AND
|
jbe@532
|
932 "initiative_id" ISNULL AND
|
jbe@532
|
933 "draft_id" ISNULL AND
|
jbe@532
|
934 "suggestion_id" ISNULL AND
|
jbe@532
|
935 "boolean_value" NOTNULL AND
|
jbe@532
|
936 "numeric_value" ISNULL AND
|
jbe@532
|
937 "text_value" ISNULL AND
|
jbe@532
|
938 "old_text_value" ISNULL ));
|
jbe@532
|
939 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
|
jbe@532
|
940 "event" != 'initiator' OR (
|
jbe@532
|
941 "member_id" NOTNULL AND
|
jbe@532
|
942 "other_member_id" ISNULL AND
|
jbe@532
|
943 "scope" ISNULL AND
|
jbe@532
|
944 "unit_id" NOTNULL AND
|
jbe@532
|
945 "area_id" NOTNULL AND
|
jbe@536
|
946 "policy_id" NOTNULL AND
|
jbe@532
|
947 "issue_id" NOTNULL AND
|
jbe@532
|
948 "state" NOTNULL AND
|
jbe@532
|
949 "initiative_id" NOTNULL AND
|
jbe@532
|
950 "draft_id" ISNULL AND
|
jbe@532
|
951 "suggestion_id" ISNULL AND
|
jbe@532
|
952 "boolean_value" NOTNULL AND
|
jbe@532
|
953 "numeric_value" ISNULL AND
|
jbe@532
|
954 "text_value" ISNULL AND
|
jbe@532
|
955 "old_text_value" ISNULL ));
|
jbe@532
|
956 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
|
jbe@532
|
957 "event" != 'support' OR (
|
jbe@532
|
958 "member_id" NOTNULL AND
|
jbe@532
|
959 "other_member_id" ISNULL AND
|
jbe@532
|
960 "scope" ISNULL AND
|
jbe@532
|
961 "unit_id" NOTNULL AND
|
jbe@532
|
962 "area_id" NOTNULL AND
|
jbe@536
|
963 "policy_id" NOTNULL AND
|
jbe@532
|
964 "issue_id" NOTNULL AND
|
jbe@532
|
965 "state" NOTNULL AND
|
jbe@532
|
966 "initiative_id" NOTNULL AND
|
jbe@532
|
967 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
|
jbe@532
|
968 "suggestion_id" ISNULL AND
|
jbe@532
|
969 "boolean_value" NOTNULL AND
|
jbe@532
|
970 "numeric_value" ISNULL AND
|
jbe@532
|
971 "text_value" ISNULL AND
|
jbe@532
|
972 "old_text_value" ISNULL ));
|
jbe@532
|
973 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
|
jbe@532
|
974 "event" != 'support_updated' OR (
|
jbe@532
|
975 "member_id" NOTNULL AND
|
jbe@532
|
976 "other_member_id" ISNULL AND
|
jbe@532
|
977 "scope" ISNULL AND
|
jbe@532
|
978 "unit_id" NOTNULL AND
|
jbe@532
|
979 "area_id" NOTNULL AND
|
jbe@536
|
980 "policy_id" NOTNULL AND
|
jbe@532
|
981 "issue_id" NOTNULL AND
|
jbe@532
|
982 "state" NOTNULL AND
|
jbe@532
|
983 "initiative_id" NOTNULL AND
|
jbe@532
|
984 "draft_id" NOTNULL AND
|
jbe@532
|
985 "suggestion_id" ISNULL AND
|
jbe@532
|
986 "boolean_value" ISNULL AND
|
jbe@532
|
987 "numeric_value" ISNULL AND
|
jbe@532
|
988 "text_value" ISNULL AND
|
jbe@532
|
989 "old_text_value" ISNULL ));
|
jbe@532
|
990 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
|
jbe@532
|
991 "event" != 'suggestion_rated' OR (
|
jbe@532
|
992 "member_id" NOTNULL AND
|
jbe@532
|
993 "other_member_id" ISNULL AND
|
jbe@532
|
994 "scope" ISNULL AND
|
jbe@532
|
995 "unit_id" NOTNULL AND
|
jbe@532
|
996 "area_id" NOTNULL AND
|
jbe@536
|
997 "policy_id" NOTNULL AND
|
jbe@532
|
998 "issue_id" NOTNULL AND
|
jbe@532
|
999 "state" NOTNULL AND
|
jbe@532
|
1000 "initiative_id" NOTNULL AND
|
jbe@532
|
1001 "draft_id" ISNULL AND
|
jbe@532
|
1002 "suggestion_id" NOTNULL AND
|
jbe@532
|
1003 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
|
jbe@532
|
1004 "numeric_value" NOTNULL AND
|
jbe@532
|
1005 "numeric_value" IN (-2, -1, 0, 1, 2) AND
|
jbe@532
|
1006 "text_value" ISNULL AND
|
jbe@532
|
1007 "old_text_value" ISNULL ));
|
jbe@532
|
1008 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
|
jbe@532
|
1009 "event" != 'delegation' OR (
|
jbe@532
|
1010 "member_id" NOTNULL AND
|
bsw@564
|
1011 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND
|
jbe@532
|
1012 "scope" NOTNULL AND
|
jbe@532
|
1013 "unit_id" NOTNULL AND
|
jbe@532
|
1014 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
|
jbe@536
|
1015 "policy_id" ISNULL AND
|
jbe@532
|
1016 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
|
jbe@532
|
1017 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
|
jbe@532
|
1018 "initiative_id" ISNULL AND
|
jbe@532
|
1019 "draft_id" ISNULL AND
|
jbe@532
|
1020 "suggestion_id" ISNULL AND
|
jbe@532
|
1021 "boolean_value" NOTNULL AND
|
jbe@532
|
1022 "numeric_value" ISNULL AND
|
jbe@532
|
1023 "text_value" ISNULL AND
|
jbe@532
|
1024 "old_text_value" ISNULL ));
|
jbe@532
|
1025 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
|
jbe@532
|
1026 "event" != 'contact' OR (
|
jbe@532
|
1027 "member_id" NOTNULL AND
|
jbe@532
|
1028 "other_member_id" NOTNULL AND
|
jbe@532
|
1029 "scope" ISNULL AND
|
jbe@532
|
1030 "unit_id" ISNULL AND
|
jbe@532
|
1031 "area_id" ISNULL AND
|
jbe@536
|
1032 "policy_id" ISNULL AND
|
jbe@532
|
1033 "issue_id" ISNULL AND
|
jbe@532
|
1034 "state" ISNULL AND
|
jbe@532
|
1035 "initiative_id" ISNULL AND
|
jbe@532
|
1036 "draft_id" ISNULL AND
|
jbe@532
|
1037 "suggestion_id" ISNULL AND
|
jbe@532
|
1038 "boolean_value" NOTNULL AND
|
jbe@532
|
1039 "numeric_value" ISNULL AND
|
jbe@532
|
1040 "text_value" ISNULL AND
|
jbe@532
|
1041 "old_text_value" ISNULL ));
|
jbe@532
|
1042
|
jbe@532
|
1043
|
jbe@534
|
1044 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
|
jbe@534
|
1045 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
|
jbe@534
|
1046
|
jbe@534
|
1047 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
|
1048 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
|
jbe@534
|
1049
|
jbe@534
|
1050
|
jbe@537
|
1051 CREATE FUNCTION "write_event_unit_trigger"()
|
jbe@537
|
1052 RETURNS TRIGGER
|
jbe@537
|
1053 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1054 DECLARE
|
jbe@538
|
1055 "event_v" "event_type";
|
jbe@537
|
1056 BEGIN
|
jbe@538
|
1057 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1058 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1059 RETURN NULL;
|
jbe@547
|
1060 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1061 -- "event_v" := 'unit_created';
|
jbe@547
|
1062 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1063 -- "event_v" := 'unit_deleted';
|
jbe@546
|
1064 ELSIF OLD != NEW THEN
|
jbe@546
|
1065 "event_v" := 'unit_updated';
|
jbe@538
|
1066 ELSE
|
jbe@546
|
1067 RETURN NULL;
|
jbe@538
|
1068 END IF;
|
jbe@538
|
1069 ELSE
|
jbe@538
|
1070 "event_v" := 'unit_created';
|
jbe@538
|
1071 END IF;
|
jbe@538
|
1072 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1073 RETURN NULL;
|
jbe@537
|
1074 END;
|
jbe@537
|
1075 $$;
|
jbe@537
|
1076
|
jbe@537
|
1077 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
|
jbe@537
|
1078 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
|
jbe@537
|
1079
|
jbe@537
|
1080 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
|
jbe@537
|
1081 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
|
jbe@537
|
1082
|
jbe@537
|
1083
|
jbe@537
|
1084 CREATE FUNCTION "write_event_area_trigger"()
|
jbe@537
|
1085 RETURNS TRIGGER
|
jbe@537
|
1086 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1087 DECLARE
|
jbe@538
|
1088 "event_v" "event_type";
|
jbe@537
|
1089 BEGIN
|
jbe@538
|
1090 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1091 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1092 RETURN NULL;
|
jbe@547
|
1093 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1094 -- "event_v" := 'area_created';
|
jbe@547
|
1095 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1096 -- "event_v" := 'area_deleted';
|
jbe@546
|
1097 ELSIF OLD != NEW THEN
|
jbe@546
|
1098 "event_v" := 'area_updated';
|
jbe@538
|
1099 ELSE
|
jbe@546
|
1100 RETURN NULL;
|
jbe@538
|
1101 END IF;
|
jbe@538
|
1102 ELSE
|
jbe@538
|
1103 "event_v" := 'area_created';
|
jbe@538
|
1104 END IF;
|
jbe@538
|
1105 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1106 RETURN NULL;
|
jbe@537
|
1107 END;
|
jbe@537
|
1108 $$;
|
jbe@537
|
1109
|
jbe@537
|
1110 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
|
jbe@537
|
1111 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
|
jbe@537
|
1112
|
jbe@537
|
1113 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
|
jbe@537
|
1114 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
|
jbe@537
|
1115
|
jbe@537
|
1116
|
jbe@537
|
1117 CREATE FUNCTION "write_event_policy_trigger"()
|
jbe@537
|
1118 RETURNS TRIGGER
|
jbe@537
|
1119 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@538
|
1120 DECLARE
|
jbe@538
|
1121 "event_v" "event_type";
|
jbe@537
|
1122 BEGIN
|
jbe@538
|
1123 IF TG_OP = 'UPDATE' THEN
|
jbe@538
|
1124 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
|
jbe@538
|
1125 RETURN NULL;
|
jbe@547
|
1126 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
|
jbe@547
|
1127 -- "event_v" := 'policy_created';
|
jbe@547
|
1128 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
|
jbe@554
|
1129 -- "event_v" := 'policy_deleted';
|
jbe@546
|
1130 ELSIF OLD != NEW THEN
|
jbe@546
|
1131 "event_v" := 'policy_updated';
|
jbe@538
|
1132 ELSE
|
jbe@546
|
1133 RETURN NULL;
|
jbe@538
|
1134 END IF;
|
jbe@538
|
1135 ELSE
|
jbe@538
|
1136 "event_v" := 'policy_created';
|
jbe@538
|
1137 END IF;
|
jbe@538
|
1138 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
|
jbe@537
|
1139 RETURN NULL;
|
jbe@537
|
1140 END;
|
jbe@537
|
1141 $$;
|
jbe@537
|
1142
|
jbe@537
|
1143 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
|
jbe@537
|
1144 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
|
jbe@537
|
1145
|
jbe@537
|
1146 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
|
jbe@537
|
1147 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
|
jbe@537
|
1148
|
jbe@537
|
1149
|
jbe@532
|
1150 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
|
jbe@532
|
1151 RETURNS TRIGGER
|
jbe@532
|
1152 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1153 DECLARE
|
jbe@532
|
1154 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1155 BEGIN
|
jbe@532
|
1156 IF NEW."state" != OLD."state" THEN
|
jbe@532
|
1157 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
|
jbe@532
|
1158 FOR SHARE;
|
jbe@532
|
1159 INSERT INTO "event" (
|
jbe@532
|
1160 "event",
|
jbe@536
|
1161 "unit_id", "area_id", "policy_id", "issue_id", "state"
|
jbe@532
|
1162 ) VALUES (
|
jbe@532
|
1163 'issue_state_changed',
|
jbe@536
|
1164 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
|
jbe@536
|
1165 NEW."id", NEW."state"
|
jbe@532
|
1166 );
|
jbe@532
|
1167 END IF;
|
jbe@532
|
1168 RETURN NULL;
|
jbe@532
|
1169 END;
|
jbe@532
|
1170 $$;
|
jbe@532
|
1171
|
jbe@532
|
1172
|
jbe@532
|
1173 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
|
jbe@532
|
1174 RETURNS TRIGGER
|
jbe@532
|
1175 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1176 DECLARE
|
jbe@532
|
1177 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1178 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1179 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1180 "event_v" "event_type";
|
jbe@532
|
1181 BEGIN
|
jbe@532
|
1182 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1183 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1184 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1185 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1186 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1187 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1188 IF EXISTS (
|
jbe@532
|
1189 SELECT NULL FROM "draft"
|
jbe@532
|
1190 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
|
jbe@532
|
1191 FOR SHARE
|
jbe@532
|
1192 ) THEN
|
jbe@532
|
1193 "event_v" := 'new_draft_created';
|
jbe@532
|
1194 ELSE
|
jbe@532
|
1195 IF EXISTS (
|
jbe@532
|
1196 SELECT NULL FROM "initiative"
|
jbe@532
|
1197 WHERE "issue_id" = "initiative_row"."issue_id"
|
jbe@532
|
1198 AND "id" != "initiative_row"."id"
|
jbe@532
|
1199 FOR SHARE
|
jbe@532
|
1200 ) THEN
|
jbe@532
|
1201 "event_v" := 'initiative_created_in_existing_issue';
|
jbe@532
|
1202 ELSE
|
jbe@532
|
1203 "event_v" := 'initiative_created_in_new_issue';
|
jbe@532
|
1204 END IF;
|
jbe@532
|
1205 END IF;
|
jbe@532
|
1206 INSERT INTO "event" (
|
jbe@532
|
1207 "event", "member_id",
|
jbe@536
|
1208 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1209 "initiative_id", "draft_id"
|
jbe@532
|
1210 ) VALUES (
|
jbe@532
|
1211 "event_v", NEW."author_id",
|
jbe@536
|
1212 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
|
jbe@532
|
1213 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1214 NEW."initiative_id", NEW."id"
|
jbe@532
|
1215 );
|
jbe@532
|
1216 RETURN NULL;
|
jbe@532
|
1217 END;
|
jbe@532
|
1218 $$;
|
jbe@532
|
1219
|
jbe@532
|
1220
|
jbe@532
|
1221 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
|
jbe@532
|
1222 RETURNS TRIGGER
|
jbe@532
|
1223 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1224 DECLARE
|
jbe@532
|
1225 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1226 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1227 "draft_id_v" "draft"."id"%TYPE;
|
jbe@532
|
1228 BEGIN
|
jbe@532
|
1229 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
|
jbe@563
|
1230 -- NOTE: lock for primary key update to avoid new drafts
|
jbe@563
|
1231 SELECT NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
|
jbe@532
|
1232 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1233 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1234 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1235 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@563
|
1236 -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
|
jbe@563
|
1237 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
|
jbe@532
|
1238 SELECT "id" INTO "draft_id_v" FROM "current_draft"
|
jbe@563
|
1239 WHERE "initiative_id" = NEW."id";
|
jbe@532
|
1240 INSERT INTO "event" (
|
jbe@532
|
1241 "event", "member_id",
|
jbe@536
|
1242 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1243 "initiative_id", "draft_id"
|
jbe@532
|
1244 ) VALUES (
|
jbe@532
|
1245 'initiative_revoked', NEW."revoked_by_member_id",
|
jbe@532
|
1246 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1247 "issue_row"."policy_id",
|
jbe@532
|
1248 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1249 NEW."id", "draft_id_v"
|
jbe@532
|
1250 );
|
jbe@532
|
1251 END IF;
|
jbe@532
|
1252 RETURN NULL;
|
jbe@532
|
1253 END;
|
jbe@532
|
1254 $$;
|
jbe@532
|
1255
|
jbe@532
|
1256
|
jbe@532
|
1257 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
|
jbe@532
|
1258 RETURNS TRIGGER
|
jbe@532
|
1259 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1260 DECLARE
|
jbe@532
|
1261 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1262 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1263 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1264 BEGIN
|
jbe@532
|
1265 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1266 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1267 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1268 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1269 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1270 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1271 INSERT INTO "event" (
|
jbe@532
|
1272 "event", "member_id",
|
jbe@536
|
1273 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1274 "initiative_id", "suggestion_id"
|
jbe@532
|
1275 ) VALUES (
|
jbe@532
|
1276 'suggestion_created', NEW."author_id",
|
jbe@536
|
1277 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
|
jbe@532
|
1278 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1279 NEW."initiative_id", NEW."id"
|
jbe@532
|
1280 );
|
jbe@532
|
1281 RETURN NULL;
|
jbe@532
|
1282 END;
|
jbe@532
|
1283 $$;
|
jbe@532
|
1284
|
jbe@532
|
1285
|
jbe@532
|
1286 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
|
jbe@532
|
1287 RETURNS TRIGGER
|
jbe@532
|
1288 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1289 DECLARE
|
jbe@532
|
1290 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1291 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1292 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1293 BEGIN
|
jbe@532
|
1294 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1295 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1296 IF "initiative_row"."id" NOTNULL THEN
|
jbe@532
|
1297 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1298 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1299 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1300 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1301 INSERT INTO "event" (
|
jbe@532
|
1302 "event",
|
jbe@536
|
1303 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1304 "initiative_id", "suggestion_id"
|
jbe@532
|
1305 ) VALUES (
|
jbe@554
|
1306 'suggestion_deleted',
|
jbe@532
|
1307 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1308 "issue_row"."policy_id",
|
jbe@532
|
1309 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1310 OLD."initiative_id", OLD."id"
|
jbe@532
|
1311 );
|
jbe@532
|
1312 END IF;
|
jbe@532
|
1313 RETURN NULL;
|
jbe@532
|
1314 END;
|
jbe@532
|
1315 $$;
|
jbe@532
|
1316
|
jbe@532
|
1317 CREATE TRIGGER "write_event_suggestion_removed"
|
jbe@532
|
1318 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1319 "write_event_suggestion_removed_trigger"();
|
jbe@532
|
1320
|
jbe@532
|
1321 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
|
jbe@532
|
1322 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
|
jbe@532
|
1323
|
jbe@532
|
1324
|
jbe@532
|
1325 CREATE FUNCTION "write_event_member_trigger"()
|
jbe@532
|
1326 RETURNS TRIGGER
|
jbe@532
|
1327 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1328 BEGIN
|
jbe@532
|
1329 IF TG_OP = 'INSERT' THEN
|
jbe@552
|
1330 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
|
jbe@532
|
1331 INSERT INTO "event" ("event", "member_id")
|
jbe@532
|
1332 VALUES ('member_activated', NEW."id");
|
jbe@532
|
1333 END IF;
|
jbe@532
|
1334 IF NEW."active" THEN
|
jbe@532
|
1335 INSERT INTO "event" ("event", "member_id", "boolean_value")
|
jbe@532
|
1336 VALUES ('member_active', NEW."id", TRUE);
|
jbe@532
|
1337 END IF;
|
jbe@532
|
1338 ELSIF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1339 IF OLD."id" != NEW."id" THEN
|
jbe@532
|
1340 RAISE EXCEPTION 'Cannot change member ID';
|
jbe@532
|
1341 END IF;
|
jbe@552
|
1342 IF
|
jbe@552
|
1343 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
|
jbe@552
|
1344 NEW."activated" NOTNULL AND NEW."deleted" ISNULL
|
jbe@552
|
1345 THEN
|
jbe@552
|
1346 INSERT INTO "event" ("event", "member_id")
|
jbe@552
|
1347 VALUES ('member_activated', NEW."id");
|
jbe@552
|
1348 END IF;
|
jbe@552
|
1349 IF OLD."active" != NEW."active" THEN
|
jbe@552
|
1350 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
|
jbe@552
|
1351 'member_active', NEW."id", NEW."active"
|
jbe@552
|
1352 );
|
jbe@552
|
1353 END IF;
|
jbe@532
|
1354 IF OLD."name" != NEW."name" THEN
|
jbe@532
|
1355 INSERT INTO "event" (
|
jbe@532
|
1356 "event", "member_id", "text_value", "old_text_value"
|
jbe@532
|
1357 ) VALUES (
|
jbe@532
|
1358 'member_name_updated', NEW."id", NEW."name", OLD."name"
|
jbe@532
|
1359 );
|
jbe@532
|
1360 END IF;
|
jbe@532
|
1361 IF
|
jbe@552
|
1362 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
|
jbe@552
|
1363 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
|
jbe@532
|
1364 THEN
|
jbe@532
|
1365 INSERT INTO "event" ("event", "member_id")
|
jbe@554
|
1366 VALUES ('member_deleted', NEW."id");
|
jbe@532
|
1367 END IF;
|
jbe@532
|
1368 END IF;
|
jbe@532
|
1369 RETURN NULL;
|
jbe@532
|
1370 END;
|
jbe@532
|
1371 $$;
|
jbe@532
|
1372
|
jbe@532
|
1373 CREATE TRIGGER "write_event_member"
|
jbe@532
|
1374 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1375 "write_event_member_trigger"();
|
jbe@532
|
1376
|
jbe@532
|
1377 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
|
jbe@532
|
1378 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
|
jbe@532
|
1379
|
jbe@532
|
1380
|
jbe@532
|
1381 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
|
jbe@532
|
1382 RETURNS TRIGGER
|
jbe@532
|
1383 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1384 BEGIN
|
jbe@532
|
1385 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1386 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
|
jbe@532
|
1387 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1388 'member_profile_updated', OLD."member_id"
|
jbe@532
|
1389 );
|
jbe@532
|
1390 END IF;
|
jbe@532
|
1391 END IF;
|
jbe@532
|
1392 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1393 IF OLD."member_id" = NEW."member_id" THEN
|
jbe@532
|
1394 RETURN NULL;
|
jbe@532
|
1395 END IF;
|
jbe@532
|
1396 END IF;
|
jbe@532
|
1397 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1398 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1399 'member_profile_updated', NEW."member_id"
|
jbe@532
|
1400 );
|
jbe@532
|
1401 END IF;
|
jbe@532
|
1402 RETURN NULL;
|
jbe@532
|
1403 END;
|
jbe@532
|
1404 $$;
|
jbe@532
|
1405
|
jbe@532
|
1406 CREATE TRIGGER "write_event_member_profile_updated"
|
jbe@532
|
1407 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
|
jbe@532
|
1408 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1409 "write_event_member_profile_updated_trigger"();
|
jbe@532
|
1410
|
jbe@532
|
1411 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
|
jbe@532
|
1412 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
|
jbe@532
|
1413
|
jbe@532
|
1414
|
jbe@532
|
1415 CREATE FUNCTION "write_event_member_image_updated_trigger"()
|
jbe@532
|
1416 RETURNS TRIGGER
|
jbe@532
|
1417 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1418 BEGIN
|
jbe@532
|
1419 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1420 IF NOT OLD."scaled" THEN
|
jbe@532
|
1421 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
|
jbe@532
|
1422 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1423 'member_image_updated', OLD."member_id"
|
jbe@532
|
1424 );
|
jbe@532
|
1425 END IF;
|
jbe@532
|
1426 END IF;
|
jbe@532
|
1427 END IF;
|
jbe@532
|
1428 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1429 IF
|
jbe@532
|
1430 OLD."member_id" = NEW."member_id" AND
|
jbe@532
|
1431 OLD."scaled" = NEW."scaled"
|
jbe@532
|
1432 THEN
|
jbe@532
|
1433 RETURN NULL;
|
jbe@532
|
1434 END IF;
|
jbe@532
|
1435 END IF;
|
jbe@532
|
1436 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1437 IF NOT NEW."scaled" THEN
|
jbe@532
|
1438 INSERT INTO "event" ("event", "member_id") VALUES (
|
jbe@532
|
1439 'member_image_updated', NEW."member_id"
|
jbe@532
|
1440 );
|
jbe@532
|
1441 END IF;
|
jbe@532
|
1442 END IF;
|
jbe@532
|
1443 RETURN NULL;
|
jbe@532
|
1444 END;
|
jbe@532
|
1445 $$;
|
jbe@532
|
1446
|
jbe@532
|
1447 CREATE TRIGGER "write_event_member_image_updated"
|
jbe@532
|
1448 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
|
jbe@532
|
1449 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1450 "write_event_member_image_updated_trigger"();
|
jbe@532
|
1451
|
jbe@532
|
1452 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
|
jbe@532
|
1453 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
|
jbe@532
|
1454
|
jbe@532
|
1455
|
jbe@532
|
1456 CREATE FUNCTION "write_event_interest_trigger"()
|
jbe@532
|
1457 RETURNS TRIGGER
|
jbe@532
|
1458 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1459 DECLARE
|
jbe@532
|
1460 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1461 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1462 BEGIN
|
jbe@532
|
1463 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1464 IF OLD = NEW THEN
|
jbe@532
|
1465 RETURN NULL;
|
jbe@532
|
1466 END IF;
|
jbe@532
|
1467 END IF;
|
jbe@532
|
1468 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1469 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1470 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1471 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1472 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1473 IF "issue_row"."id" NOTNULL THEN
|
jbe@532
|
1474 INSERT INTO "event" (
|
jbe@532
|
1475 "event", "member_id",
|
jbe@536
|
1476 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1477 "boolean_value"
|
jbe@532
|
1478 ) VALUES (
|
jbe@532
|
1479 'interest', OLD."member_id",
|
jbe@532
|
1480 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1481 "issue_row"."policy_id",
|
jbe@532
|
1482 OLD."issue_id", "issue_row"."state",
|
jbe@532
|
1483 FALSE
|
jbe@532
|
1484 );
|
jbe@532
|
1485 END IF;
|
jbe@532
|
1486 END IF;
|
jbe@532
|
1487 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1488 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1489 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1490 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1491 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1492 INSERT INTO "event" (
|
jbe@532
|
1493 "event", "member_id",
|
jbe@536
|
1494 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1495 "boolean_value"
|
jbe@532
|
1496 ) VALUES (
|
jbe@532
|
1497 'interest', NEW."member_id",
|
jbe@532
|
1498 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1499 "issue_row"."policy_id",
|
jbe@532
|
1500 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1501 TRUE
|
jbe@532
|
1502 );
|
jbe@532
|
1503 END IF;
|
jbe@532
|
1504 RETURN NULL;
|
jbe@532
|
1505 END;
|
jbe@532
|
1506 $$;
|
jbe@532
|
1507
|
jbe@532
|
1508 CREATE TRIGGER "write_event_interest"
|
jbe@532
|
1509 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1510 "write_event_interest_trigger"();
|
jbe@532
|
1511
|
jbe@532
|
1512 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
|
jbe@532
|
1513 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
|
jbe@532
|
1514
|
jbe@532
|
1515
|
jbe@532
|
1516 CREATE FUNCTION "write_event_initiator_trigger"()
|
jbe@532
|
1517 RETURNS TRIGGER
|
jbe@532
|
1518 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1519 DECLARE
|
jbe@532
|
1520 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1521 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1522 "area_row" "area"%ROWTYPE;
|
jbe@549
|
1523 "accepted_v" BOOLEAN = FALSE;
|
jbe@549
|
1524 "rejected_v" BOOLEAN = FALSE;
|
jbe@532
|
1525 BEGIN
|
jbe@532
|
1526 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1527 IF
|
jbe@532
|
1528 OLD."initiative_id" = NEW."initiative_id" AND
|
jbe@549
|
1529 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1530 THEN
|
jbe@549
|
1531 IF
|
jbe@549
|
1532 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
|
jbe@549
|
1533 THEN
|
jbe@549
|
1534 RETURN NULL;
|
jbe@549
|
1535 END IF;
|
jbe@549
|
1536 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
|
jbe@549
|
1537 "accepted_v" := TRUE;
|
jbe@549
|
1538 ELSE
|
jbe@549
|
1539 "rejected_v" := TRUE;
|
jbe@549
|
1540 END IF;
|
jbe@532
|
1541 END IF;
|
jbe@532
|
1542 END IF;
|
jbe@532
|
1543 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
|
jbe@532
|
1544 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
|
jbe@532
|
1545 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1546 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1547 IF "initiative_row"."id" NOTNULL THEN
|
jbe@532
|
1548 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1549 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1550 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1551 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1552 INSERT INTO "event" (
|
jbe@532
|
1553 "event", "member_id",
|
jbe@536
|
1554 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1555 "initiative_id", "boolean_value"
|
jbe@532
|
1556 ) VALUES (
|
jbe@532
|
1557 'initiator', OLD."member_id",
|
jbe@532
|
1558 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1559 "issue_row"."policy_id",
|
jbe@532
|
1560 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1561 OLD."initiative_id", FALSE
|
jbe@532
|
1562 );
|
jbe@532
|
1563 END IF;
|
jbe@532
|
1564 END IF;
|
jbe@532
|
1565 END IF;
|
jbe@532
|
1566 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
|
jbe@532
|
1567 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
|
jbe@532
|
1568 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1569 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1570 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1571 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1572 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1573 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1574 INSERT INTO "event" (
|
jbe@532
|
1575 "event", "member_id",
|
jbe@536
|
1576 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1577 "initiative_id", "boolean_value"
|
jbe@532
|
1578 ) VALUES (
|
jbe@532
|
1579 'initiator', NEW."member_id",
|
jbe@532
|
1580 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1581 "issue_row"."policy_id",
|
jbe@532
|
1582 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1583 NEW."initiative_id", TRUE
|
jbe@532
|
1584 );
|
jbe@532
|
1585 END IF;
|
jbe@532
|
1586 END IF;
|
jbe@532
|
1587 RETURN NULL;
|
jbe@532
|
1588 END;
|
jbe@532
|
1589 $$;
|
jbe@532
|
1590
|
jbe@532
|
1591 CREATE TRIGGER "write_event_initiator"
|
jbe@532
|
1592 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1593 "write_event_initiator_trigger"();
|
jbe@532
|
1594
|
jbe@532
|
1595 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
|
jbe@532
|
1596 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
|
1597
|
jbe@532
|
1598
|
jbe@532
|
1599 CREATE FUNCTION "write_event_support_trigger"()
|
jbe@532
|
1600 RETURNS TRIGGER
|
jbe@532
|
1601 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1602 DECLARE
|
jbe@532
|
1603 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1604 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1605 BEGIN
|
jbe@532
|
1606 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1607 IF
|
jbe@532
|
1608 OLD."initiative_id" = NEW."initiative_id" AND
|
jbe@532
|
1609 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1610 THEN
|
jbe@532
|
1611 IF OLD."draft_id" != NEW."draft_id" THEN
|
jbe@532
|
1612 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1613 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1614 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1615 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1616 INSERT INTO "event" (
|
jbe@532
|
1617 "event", "member_id",
|
jbe@536
|
1618 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1619 "initiative_id", "draft_id"
|
jbe@532
|
1620 ) VALUES (
|
jbe@532
|
1621 'support_updated', NEW."member_id",
|
jbe@532
|
1622 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1623 "issue_row"."policy_id",
|
jbe@532
|
1624 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1625 NEW."initiative_id", NEW."draft_id"
|
jbe@532
|
1626 );
|
jbe@532
|
1627 END IF;
|
jbe@532
|
1628 RETURN NULL;
|
jbe@532
|
1629 END IF;
|
jbe@532
|
1630 END IF;
|
jbe@532
|
1631 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1632 IF EXISTS (
|
jbe@532
|
1633 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
|
jbe@532
|
1634 FOR SHARE
|
jbe@532
|
1635 ) THEN
|
jbe@532
|
1636 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1637 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1638 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1639 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1640 INSERT INTO "event" (
|
jbe@532
|
1641 "event", "member_id",
|
jbe@536
|
1642 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@535
|
1643 "initiative_id", "boolean_value"
|
jbe@532
|
1644 ) VALUES (
|
jbe@532
|
1645 'support', OLD."member_id",
|
jbe@532
|
1646 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1647 "issue_row"."policy_id",
|
jbe@532
|
1648 "issue_row"."id", "issue_row"."state",
|
jbe@535
|
1649 OLD."initiative_id", FALSE
|
jbe@532
|
1650 );
|
jbe@532
|
1651 END IF;
|
jbe@532
|
1652 END IF;
|
jbe@532
|
1653 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1654 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1655 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1656 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1657 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1658 INSERT INTO "event" (
|
jbe@532
|
1659 "event", "member_id",
|
jbe@536
|
1660 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1661 "initiative_id", "draft_id", "boolean_value"
|
jbe@532
|
1662 ) VALUES (
|
jbe@532
|
1663 'support', NEW."member_id",
|
jbe@532
|
1664 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1665 "issue_row"."policy_id",
|
jbe@532
|
1666 "issue_row"."id", "issue_row"."state",
|
jbe@532
|
1667 NEW."initiative_id", NEW."draft_id", TRUE
|
jbe@532
|
1668 );
|
jbe@532
|
1669 END IF;
|
jbe@532
|
1670 RETURN NULL;
|
jbe@532
|
1671 END;
|
jbe@532
|
1672 $$;
|
jbe@532
|
1673
|
jbe@532
|
1674 CREATE TRIGGER "write_event_support"
|
jbe@532
|
1675 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1676 "write_event_support_trigger"();
|
jbe@532
|
1677
|
jbe@532
|
1678 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
|
jbe@532
|
1679 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
|
jbe@532
|
1680
|
jbe@532
|
1681
|
jbe@532
|
1682 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
|
jbe@532
|
1683 RETURNS TRIGGER
|
jbe@532
|
1684 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1685 DECLARE
|
jbe@532
|
1686 "same_pkey_v" BOOLEAN = FALSE;
|
jbe@532
|
1687 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
1688 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1689 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1690 BEGIN
|
jbe@532
|
1691 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1692 IF
|
jbe@532
|
1693 OLD."suggestion_id" = NEW."suggestion_id" AND
|
jbe@532
|
1694 OLD."member_id" = NEW."member_id"
|
jbe@532
|
1695 THEN
|
jbe@532
|
1696 IF
|
jbe@532
|
1697 OLD."degree" = NEW."degree" AND
|
jbe@532
|
1698 OLD."fulfilled" = NEW."fulfilled"
|
jbe@532
|
1699 THEN
|
jbe@532
|
1700 RETURN NULL;
|
jbe@532
|
1701 END IF;
|
jbe@532
|
1702 "same_pkey_v" := TRUE;
|
jbe@532
|
1703 END IF;
|
jbe@532
|
1704 END IF;
|
jbe@532
|
1705 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
|
jbe@532
|
1706 IF EXISTS (
|
jbe@532
|
1707 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
|
jbe@532
|
1708 FOR SHARE
|
jbe@532
|
1709 ) THEN
|
jbe@532
|
1710 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1711 WHERE "id" = OLD."initiative_id" FOR SHARE;
|
jbe@532
|
1712 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1713 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1714 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1715 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1716 INSERT INTO "event" (
|
jbe@532
|
1717 "event", "member_id",
|
jbe@536
|
1718 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1719 "initiative_id", "suggestion_id",
|
jbe@532
|
1720 "boolean_value", "numeric_value"
|
jbe@532
|
1721 ) VALUES (
|
jbe@532
|
1722 'suggestion_rated', OLD."member_id",
|
jbe@532
|
1723 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1724 "issue_row"."policy_id",
|
jbe@532
|
1725 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1726 OLD."initiative_id", OLD."suggestion_id",
|
jbe@532
|
1727 NULL, 0
|
jbe@532
|
1728 );
|
jbe@532
|
1729 END IF;
|
jbe@532
|
1730 END IF;
|
jbe@532
|
1731 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1732 SELECT * INTO "initiative_row" FROM "initiative"
|
jbe@532
|
1733 WHERE "id" = NEW."initiative_id" FOR SHARE;
|
jbe@532
|
1734 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1735 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
|
jbe@532
|
1736 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1737 WHERE "id" = "issue_row"."area_id" FOR SHARE;
|
jbe@532
|
1738 INSERT INTO "event" (
|
jbe@532
|
1739 "event", "member_id",
|
jbe@536
|
1740 "unit_id", "area_id", "policy_id", "issue_id", "state",
|
jbe@532
|
1741 "initiative_id", "suggestion_id",
|
jbe@532
|
1742 "boolean_value", "numeric_value"
|
jbe@532
|
1743 ) VALUES (
|
jbe@532
|
1744 'suggestion_rated', NEW."member_id",
|
jbe@532
|
1745 "area_row"."unit_id", "issue_row"."area_id",
|
jbe@536
|
1746 "issue_row"."policy_id",
|
jbe@532
|
1747 "initiative_row"."issue_id", "issue_row"."state",
|
jbe@532
|
1748 NEW."initiative_id", NEW."suggestion_id",
|
jbe@532
|
1749 NEW."fulfilled", NEW."degree"
|
jbe@532
|
1750 );
|
jbe@532
|
1751 END IF;
|
jbe@532
|
1752 RETURN NULL;
|
jbe@532
|
1753 END;
|
jbe@532
|
1754 $$;
|
jbe@532
|
1755
|
jbe@532
|
1756 CREATE TRIGGER "write_event_suggestion_rated"
|
jbe@532
|
1757 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1758 "write_event_suggestion_rated_trigger"();
|
jbe@532
|
1759
|
jbe@532
|
1760 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
|
jbe@532
|
1761 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
|
jbe@532
|
1762
|
jbe@532
|
1763
|
jbe@532
|
1764 CREATE FUNCTION "write_event_delegation_trigger"()
|
jbe@532
|
1765 RETURNS TRIGGER
|
jbe@532
|
1766 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1767 DECLARE
|
jbe@532
|
1768 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
1769 "area_row" "area"%ROWTYPE;
|
jbe@532
|
1770 BEGIN
|
jbe@532
|
1771 IF TG_OP = 'DELETE' THEN
|
jbe@532
|
1772 IF EXISTS (
|
jbe@532
|
1773 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
|
jbe@532
|
1774 ) AND (CASE OLD."scope"
|
jbe@532
|
1775 WHEN 'unit'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1776 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
|
jbe@532
|
1777 )
|
jbe@532
|
1778 WHEN 'area'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1779 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
|
jbe@532
|
1780 )
|
jbe@532
|
1781 WHEN 'issue'::"delegation_scope" THEN EXISTS (
|
jbe@532
|
1782 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
|
jbe@532
|
1783 )
|
jbe@532
|
1784 END) THEN
|
jbe@532
|
1785 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1786 WHERE "id" = OLD."issue_id" FOR SHARE;
|
jbe@532
|
1787 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1788 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
|
jbe@532
|
1789 FOR SHARE;
|
jbe@532
|
1790 INSERT INTO "event" (
|
jbe@532
|
1791 "event", "member_id", "scope",
|
jbe@532
|
1792 "unit_id", "area_id", "issue_id", "state",
|
jbe@532
|
1793 "boolean_value"
|
jbe@532
|
1794 ) VALUES (
|
jbe@532
|
1795 'delegation', OLD."truster_id", OLD."scope",
|
jbe@532
|
1796 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
|
jbe@532
|
1797 OLD."issue_id", "issue_row"."state",
|
jbe@532
|
1798 FALSE
|
jbe@532
|
1799 );
|
jbe@532
|
1800 END IF;
|
jbe@532
|
1801 ELSE
|
jbe@532
|
1802 SELECT * INTO "issue_row" FROM "issue"
|
jbe@532
|
1803 WHERE "id" = NEW."issue_id" FOR SHARE;
|
jbe@532
|
1804 SELECT * INTO "area_row" FROM "area"
|
jbe@532
|
1805 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
|
jbe@532
|
1806 FOR SHARE;
|
jbe@532
|
1807 INSERT INTO "event" (
|
jbe@532
|
1808 "event", "member_id", "other_member_id", "scope",
|
jbe@532
|
1809 "unit_id", "area_id", "issue_id", "state",
|
jbe@532
|
1810 "boolean_value"
|
jbe@532
|
1811 ) VALUES (
|
jbe@532
|
1812 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
|
jbe@532
|
1813 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
|
jbe@532
|
1814 NEW."issue_id", "issue_row"."state",
|
jbe@532
|
1815 TRUE
|
jbe@532
|
1816 );
|
jbe@532
|
1817 END IF;
|
jbe@532
|
1818 RETURN NULL;
|
jbe@532
|
1819 END;
|
jbe@532
|
1820 $$;
|
jbe@532
|
1821
|
jbe@532
|
1822 CREATE TRIGGER "write_event_delegation"
|
jbe@532
|
1823 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1824 "write_event_delegation_trigger"();
|
jbe@532
|
1825
|
jbe@532
|
1826 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
|
jbe@532
|
1827 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
|
jbe@532
|
1828
|
jbe@532
|
1829
|
jbe@532
|
1830 CREATE FUNCTION "write_event_contact_trigger"()
|
jbe@532
|
1831 RETURNS TRIGGER
|
jbe@532
|
1832 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1833 BEGIN
|
jbe@532
|
1834 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1835 IF
|
jbe@532
|
1836 OLD."member_id" = NEW."member_id" AND
|
jbe@532
|
1837 OLD."other_member_id" = NEW."other_member_id" AND
|
jbe@532
|
1838 OLD."public" = NEW."public"
|
jbe@532
|
1839 THEN
|
jbe@532
|
1840 RETURN NULL;
|
jbe@532
|
1841 END IF;
|
jbe@532
|
1842 END IF;
|
jbe@532
|
1843 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1844 IF OLD."public" THEN
|
jbe@532
|
1845 IF EXISTS (
|
jbe@532
|
1846 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
|
jbe@532
|
1847 FOR SHARE
|
jbe@532
|
1848 ) AND EXISTS (
|
jbe@532
|
1849 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
|
jbe@532
|
1850 FOR SHARE
|
jbe@532
|
1851 ) THEN
|
jbe@532
|
1852 INSERT INTO "event" (
|
jbe@532
|
1853 "event", "member_id", "other_member_id", "boolean_value"
|
jbe@532
|
1854 ) VALUES (
|
jbe@532
|
1855 'contact', OLD."member_id", OLD."other_member_id", FALSE
|
jbe@532
|
1856 );
|
jbe@532
|
1857 END IF;
|
jbe@532
|
1858 END IF;
|
jbe@532
|
1859 END IF;
|
jbe@532
|
1860 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
jbe@532
|
1861 IF NEW."public" THEN
|
jbe@532
|
1862 INSERT INTO "event" (
|
jbe@532
|
1863 "event", "member_id", "other_member_id", "boolean_value"
|
jbe@532
|
1864 ) VALUES (
|
jbe@532
|
1865 'contact', NEW."member_id", NEW."other_member_id", TRUE
|
jbe@532
|
1866 );
|
jbe@532
|
1867 END IF;
|
jbe@532
|
1868 END IF;
|
jbe@532
|
1869 RETURN NULL;
|
jbe@532
|
1870 END;
|
jbe@532
|
1871 $$;
|
jbe@532
|
1872
|
jbe@532
|
1873 CREATE TRIGGER "write_event_contact"
|
jbe@532
|
1874 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1875 "write_event_contact_trigger"();
|
jbe@532
|
1876
|
jbe@532
|
1877 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
|
jbe@532
|
1878 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
|
jbe@532
|
1879
|
jbe@532
|
1880
|
jbe@532
|
1881 CREATE FUNCTION "send_event_notify_trigger"()
|
jbe@532
|
1882 RETURNS TRIGGER
|
jbe@532
|
1883 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1884 BEGIN
|
jbe@532
|
1885 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
|
jbe@532
|
1886 RETURN NULL;
|
jbe@532
|
1887 END;
|
jbe@532
|
1888 $$;
|
jbe@532
|
1889
|
jbe@532
|
1890 CREATE TRIGGER "send_notify"
|
jbe@532
|
1891 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1892 "send_event_notify_trigger"();
|
jbe@532
|
1893
|
jbe@532
|
1894
|
jbe@532
|
1895 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
|
jbe@532
|
1896 RETURNS TRIGGER
|
jbe@532
|
1897 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1898 DECLARE
|
jbe@532
|
1899 "system_application_row" "system_application"%ROWTYPE;
|
jbe@532
|
1900 BEGIN
|
jbe@532
|
1901 IF OLD."system_application_id" NOTNULL THEN
|
jbe@532
|
1902 SELECT * FROM "system_application" INTO "system_application_row"
|
jbe@532
|
1903 WHERE "id" = OLD."system_application_id";
|
jbe@532
|
1904 DELETE FROM "token"
|
jbe@532
|
1905 WHERE "member_id" = OLD."member_id"
|
jbe@532
|
1906 AND "system_application_id" = OLD."system_application_id"
|
jbe@532
|
1907 AND NOT COALESCE(
|
jbe@532
|
1908 regexp_split_to_array("scope", E'\\s+') <@
|
jbe@532
|
1909 regexp_split_to_array(
|
jbe@532
|
1910 "system_application_row"."automatic_scope", E'\\s+'
|
jbe@532
|
1911 ),
|
jbe@532
|
1912 FALSE
|
jbe@532
|
1913 );
|
jbe@532
|
1914 END IF;
|
jbe@532
|
1915 RETURN OLD;
|
jbe@532
|
1916 END;
|
jbe@532
|
1917 $$;
|
jbe@532
|
1918
|
jbe@532
|
1919 CREATE TRIGGER "delete_extended_scope_tokens"
|
jbe@532
|
1920 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1921 "delete_extended_scope_tokens_trigger"();
|
jbe@532
|
1922
|
jbe@532
|
1923
|
jbe@532
|
1924 CREATE FUNCTION "detach_token_from_session_trigger"()
|
jbe@532
|
1925 RETURNS TRIGGER
|
jbe@532
|
1926 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1927 BEGIN
|
jbe@532
|
1928 UPDATE "token" SET "session_id" = NULL
|
jbe@532
|
1929 WHERE "session_id" = OLD."id";
|
jbe@532
|
1930 RETURN OLD;
|
jbe@532
|
1931 END;
|
jbe@532
|
1932 $$;
|
jbe@532
|
1933
|
jbe@532
|
1934 CREATE TRIGGER "detach_token_from_session"
|
jbe@532
|
1935 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1936 "detach_token_from_session_trigger"();
|
jbe@532
|
1937
|
jbe@532
|
1938
|
jbe@532
|
1939 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
|
jbe@532
|
1940 RETURNS TRIGGER
|
jbe@532
|
1941 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1942 BEGIN
|
jbe@532
|
1943 IF NEW."session_id" ISNULL THEN
|
jbe@532
|
1944 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
|
jbe@532
|
1945 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
|
jbe@532
|
1946 WHERE "element" LIKE '%_detached';
|
jbe@532
|
1947 END IF;
|
jbe@532
|
1948 RETURN NEW;
|
jbe@532
|
1949 END;
|
jbe@532
|
1950 $$;
|
jbe@532
|
1951
|
jbe@532
|
1952 CREATE TRIGGER "delete_non_detached_scope_with_session"
|
jbe@532
|
1953 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1954 "delete_non_detached_scope_with_session_trigger"();
|
jbe@532
|
1955
|
jbe@532
|
1956
|
jbe@532
|
1957 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
|
jbe@532
|
1958 RETURNS TRIGGER
|
jbe@532
|
1959 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1960 BEGIN
|
jbe@532
|
1961 IF NEW."scope" = '' THEN
|
jbe@532
|
1962 DELETE FROM "token" WHERE "id" = NEW."id";
|
jbe@532
|
1963 END IF;
|
jbe@532
|
1964 RETURN NULL;
|
jbe@532
|
1965 END;
|
jbe@532
|
1966 $$;
|
jbe@532
|
1967
|
jbe@532
|
1968 CREATE TRIGGER "delete_token_with_empty_scope"
|
jbe@532
|
1969 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1970 "delete_token_with_empty_scope_trigger"();
|
jbe@532
|
1971
|
jbe@532
|
1972
|
jbe@532
|
1973 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
|
jbe@532
|
1974 RETURNS TRIGGER
|
jbe@532
|
1975 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
1976 BEGIN
|
jbe@532
|
1977 IF TG_OP = 'UPDATE' THEN
|
jbe@532
|
1978 IF
|
jbe@532
|
1979 OLD."snapshot_id" = NEW."snapshot_id" AND
|
jbe@532
|
1980 OLD."issue_id" = NEW."issue_id"
|
jbe@532
|
1981 THEN
|
jbe@532
|
1982 RETURN NULL;
|
jbe@532
|
1983 END IF;
|
jbe@532
|
1984 END IF;
|
jbe@532
|
1985 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
|
jbe@532
|
1986 RETURN NULL;
|
jbe@532
|
1987 END;
|
jbe@532
|
1988 $$;
|
jbe@532
|
1989
|
jbe@532
|
1990 CREATE TRIGGER "delete_snapshot_on_partial_delete"
|
jbe@532
|
1991 AFTER UPDATE OR DELETE ON "snapshot_issue"
|
jbe@532
|
1992 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
1993 "delete_snapshot_on_partial_delete_trigger"();
|
jbe@532
|
1994
|
jbe@532
|
1995 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
|
jbe@532
|
1996 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
|
1997
|
jbe@532
|
1998
|
jbe@532
|
1999 CREATE FUNCTION "copy_current_draft_data"
|
jbe@532
|
2000 ("initiative_id_p" "initiative"."id"%TYPE )
|
jbe@532
|
2001 RETURNS VOID
|
jbe@532
|
2002 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2003 BEGIN
|
jbe@532
|
2004 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
|
jbe@532
|
2005 FOR UPDATE;
|
jbe@532
|
2006 UPDATE "initiative" SET
|
jbe@532
|
2007 "location" = "draft"."location",
|
jbe@532
|
2008 "draft_text_search_data" = "draft"."text_search_data"
|
jbe@532
|
2009 FROM "current_draft" AS "draft"
|
jbe@532
|
2010 WHERE "initiative"."id" = "initiative_id_p"
|
jbe@532
|
2011 AND "draft"."initiative_id" = "initiative_id_p";
|
jbe@532
|
2012 END;
|
jbe@532
|
2013 $$;
|
jbe@532
|
2014
|
jbe@532
|
2015 COMMENT ON FUNCTION "copy_current_draft_data"
|
jbe@532
|
2016 ( "initiative"."id"%TYPE )
|
jbe@532
|
2017 IS 'Helper function for function "copy_current_draft_data_trigger"';
|
jbe@532
|
2018
|
jbe@532
|
2019
|
jbe@532
|
2020 CREATE FUNCTION "copy_current_draft_data_trigger"()
|
jbe@532
|
2021 RETURNS TRIGGER
|
jbe@532
|
2022 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2023 BEGIN
|
jbe@532
|
2024 IF TG_OP='DELETE' THEN
|
jbe@532
|
2025 PERFORM "copy_current_draft_data"(OLD."initiative_id");
|
jbe@532
|
2026 ELSE
|
jbe@532
|
2027 IF TG_OP='UPDATE' THEN
|
jbe@532
|
2028 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
|
jbe@532
|
2029 PERFORM "copy_current_draft_data"(OLD."initiative_id");
|
jbe@532
|
2030 END IF;
|
jbe@532
|
2031 END IF;
|
jbe@532
|
2032 PERFORM "copy_current_draft_data"(NEW."initiative_id");
|
jbe@532
|
2033 END IF;
|
jbe@532
|
2034 RETURN NULL;
|
jbe@532
|
2035 END;
|
jbe@532
|
2036 $$;
|
jbe@532
|
2037
|
jbe@532
|
2038 CREATE TRIGGER "copy_current_draft_data"
|
jbe@532
|
2039 AFTER INSERT OR UPDATE OR DELETE ON "draft"
|
jbe@532
|
2040 FOR EACH ROW EXECUTE PROCEDURE
|
jbe@532
|
2041 "copy_current_draft_data_trigger"();
|
jbe@532
|
2042
|
jbe@532
|
2043 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
|
jbe@532
|
2044 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
|
jbe@532
|
2045
|
jbe@532
|
2046
|
jbe@532
|
2047 CREATE VIEW "area_quorum" AS
|
jbe@532
|
2048 SELECT
|
jbe@532
|
2049 "area"."id" AS "area_id",
|
jbe@532
|
2050 ceil(
|
jbe@532
|
2051 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
|
jbe@532
|
2052 coalesce(
|
jbe@532
|
2053 ( SELECT sum(
|
jbe@532
|
2054 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
|
jbe@532
|
2055 extract(epoch from
|
jbe@532
|
2056 ("issue"."accepted"-"issue"."created") +
|
jbe@532
|
2057 "issue"."discussion_time" +
|
jbe@532
|
2058 "issue"."verification_time" +
|
jbe@532
|
2059 "issue"."voting_time"
|
jbe@532
|
2060 )::FLOAT8
|
jbe@532
|
2061 ) ^ "area"."quorum_exponent"::FLOAT8
|
jbe@532
|
2062 )
|
jbe@532
|
2063 FROM "issue" JOIN "policy"
|
jbe@532
|
2064 ON "issue"."policy_id" = "policy"."id"
|
jbe@532
|
2065 WHERE "issue"."area_id" = "area"."id"
|
jbe@532
|
2066 AND "issue"."accepted" NOTNULL
|
jbe@532
|
2067 AND "issue"."closed" ISNULL
|
jbe@532
|
2068 AND "policy"."polling" = FALSE
|
jbe@532
|
2069 )::FLOAT8, 0::FLOAT8
|
jbe@532
|
2070 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
|
jbe@532
|
2071 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
|
jbe@532
|
2072 SELECT "snapshot"."population"
|
jbe@532
|
2073 FROM "snapshot"
|
jbe@532
|
2074 WHERE "snapshot"."area_id" = "area"."id"
|
jbe@532
|
2075 AND "snapshot"."issue_id" ISNULL
|
jbe@532
|
2076 ORDER BY "snapshot"."id" DESC
|
jbe@532
|
2077 LIMIT 1
|
jbe@532
|
2078 ) END / coalesce("area"."quorum_den", 1)
|
jbe@532
|
2079
|
jbe@532
|
2080 )::INT4 AS "issue_quorum"
|
jbe@532
|
2081 FROM "area";
|
jbe@532
|
2082
|
jbe@532
|
2083 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
|
jbe@532
|
2084
|
jbe@532
|
2085
|
jbe@568
|
2086 CREATE VIEW "issue_quorum" AS
|
jbe@568
|
2087 SELECT DISTINCT ON ("issue_id")
|
jbe@568
|
2088 "issue"."id" AS "issue_id",
|
jbe@568
|
2089 "subquery"."issue_quorum"
|
jbe@568
|
2090 FROM "issue"
|
jbe@568
|
2091 CROSS JOIN LATERAL (
|
jbe@568
|
2092 SELECT "area_quorum"."issue_quorum"
|
jbe@568
|
2093 FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id"
|
jbe@568
|
2094 UNION ALL
|
jbe@568
|
2095 SELECT "policy"."issue_quorum"
|
jbe@568
|
2096 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
|
jbe@568
|
2097 UNION ALL
|
jbe@568
|
2098 SELECT
|
jbe@568
|
2099 ceil(
|
jbe@568
|
2100 ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /
|
jbe@568
|
2101 "policy"."issue_quorum_den"::FLOAT8
|
jbe@568
|
2102 )::INT4
|
jbe@568
|
2103 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
|
jbe@568
|
2104 ) AS "subquery"
|
jbe@568
|
2105 ORDER BY "issue_id", "issue_quorum" DESC;
|
jbe@568
|
2106
|
jbe@568
|
2107 COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission';
|
jbe@568
|
2108
|
jbe@568
|
2109
|
jbe@532
|
2110 CREATE VIEW "area_with_unaccepted_issues" AS
|
jbe@532
|
2111 SELECT DISTINCT ON ("area"."id") "area".*
|
jbe@532
|
2112 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2113 WHERE "issue"."state" = 'admission';
|
jbe@532
|
2114
|
jbe@532
|
2115 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
|
jbe@532
|
2116
|
jbe@532
|
2117
|
jbe@532
|
2118 DROP VIEW "area_member_count";
|
jbe@532
|
2119
|
jbe@532
|
2120
|
jbe@532
|
2121 DROP TABLE "membership";
|
jbe@532
|
2122
|
jbe@532
|
2123
|
jbe@532
|
2124 DROP FUNCTION "membership_weight"
|
jbe@532
|
2125 ( "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2126 "member_id_p" "member"."id"%TYPE );
|
jbe@532
|
2127
|
jbe@532
|
2128
|
jbe@532
|
2129 DROP FUNCTION "membership_weight_with_skipping"
|
jbe@532
|
2130 ( "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2131 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2132 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
|
jbe@532
|
2133
|
jbe@532
|
2134
|
jbe@532
|
2135 CREATE OR REPLACE VIEW "issue_delegation" AS
|
jbe@532
|
2136 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
jbe@532
|
2137 "issue"."id" AS "issue_id",
|
jbe@532
|
2138 "delegation"."id",
|
jbe@532
|
2139 "delegation"."truster_id",
|
jbe@532
|
2140 "delegation"."trustee_id",
|
jbe@532
|
2141 "delegation"."scope"
|
jbe@532
|
2142 FROM "issue"
|
jbe@532
|
2143 JOIN "area"
|
jbe@532
|
2144 ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2145 JOIN "delegation"
|
jbe@532
|
2146 ON "delegation"."unit_id" = "area"."unit_id"
|
jbe@532
|
2147 OR "delegation"."area_id" = "area"."id"
|
jbe@532
|
2148 OR "delegation"."issue_id" = "issue"."id"
|
jbe@532
|
2149 JOIN "member"
|
jbe@532
|
2150 ON "delegation"."truster_id" = "member"."id"
|
jbe@556
|
2151 JOIN "privilege"
|
jbe@556
|
2152 ON "area"."unit_id" = "privilege"."unit_id"
|
jbe@556
|
2153 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@556
|
2154 WHERE "member"."active" AND "privilege"."voting_right"
|
jbe@532
|
2155 ORDER BY
|
jbe@532
|
2156 "issue"."id",
|
jbe@532
|
2157 "delegation"."truster_id",
|
jbe@532
|
2158 "delegation"."scope" DESC;
|
jbe@532
|
2159
|
jbe@532
|
2160
|
jbe@532
|
2161 CREATE VIEW "unit_member" AS
|
jbe@532
|
2162 SELECT
|
jbe@532
|
2163 "unit"."id" AS "unit_id",
|
jbe@532
|
2164 "member"."id" AS "member_id"
|
jbe@556
|
2165 FROM "privilege"
|
jbe@569
|
2166 JOIN "unit" ON "unit"."id" = "privilege"."unit_id"
|
jbe@556
|
2167 JOIN "member" ON "member"."id" = "privilege"."member_id"
|
jbe@556
|
2168 WHERE "privilege"."voting_right" AND "member"."active";
|
jbe@532
|
2169
|
jbe@532
|
2170 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
|
jbe@532
|
2171
|
jbe@532
|
2172
|
jbe@532
|
2173 CREATE OR REPLACE VIEW "unit_member_count" AS
|
jbe@532
|
2174 SELECT
|
jbe@532
|
2175 "unit"."id" AS "unit_id",
|
jbe@532
|
2176 count("unit_member"."member_id") AS "member_count"
|
jbe@532
|
2177 FROM "unit" LEFT JOIN "unit_member"
|
jbe@532
|
2178 ON "unit"."id" = "unit_member"."unit_id"
|
jbe@532
|
2179 GROUP BY "unit"."id";
|
jbe@532
|
2180
|
jbe@532
|
2181 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
|
jbe@532
|
2182
|
jbe@532
|
2183
|
jbe@532
|
2184 CREATE OR REPLACE VIEW "opening_draft" AS
|
jbe@532
|
2185 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
|
jbe@532
|
2186 ORDER BY "initiative_id", "id";
|
jbe@532
|
2187
|
jbe@532
|
2188
|
jbe@532
|
2189 CREATE OR REPLACE VIEW "current_draft" AS
|
jbe@532
|
2190 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
|
jbe@532
|
2191 ORDER BY "initiative_id", "id" DESC;
|
jbe@532
|
2192
|
jbe@532
|
2193
|
jbe@532
|
2194 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
|
jbe@532
|
2195 SELECT
|
jbe@532
|
2196 "area"."unit_id",
|
jbe@532
|
2197 "issue"."area_id",
|
jbe@532
|
2198 "issue"."id" AS "issue_id",
|
jbe@532
|
2199 "supporter"."member_id",
|
jbe@532
|
2200 "direct_interest_snapshot"."weight"
|
jbe@532
|
2201 FROM "issue"
|
jbe@532
|
2202 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@532
|
2203 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
|
jbe@532
|
2204 JOIN "direct_interest_snapshot"
|
jbe@532
|
2205 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2206 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
2207 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
|
jbe@532
|
2208 WHERE "issue"."state" = 'admission'::"issue_state";
|
jbe@532
|
2209
|
jbe@532
|
2210
|
jbe@532
|
2211 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
|
jbe@532
|
2212 SELECT
|
jbe@532
|
2213 "opinion"."initiative_id",
|
jbe@532
|
2214 "opinion"."member_id",
|
jbe@532
|
2215 "direct_interest_snapshot"."weight",
|
jbe@532
|
2216 CASE WHEN
|
jbe@532
|
2217 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@532
|
2218 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
|
jbe@532
|
2219 THEN 1 ELSE
|
jbe@532
|
2220 CASE WHEN
|
jbe@532
|
2221 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@532
|
2222 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
|
jbe@532
|
2223 THEN 2 ELSE
|
jbe@532
|
2224 CASE WHEN
|
jbe@532
|
2225 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
|
jbe@532
|
2226 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
|
jbe@532
|
2227 THEN 3 ELSE 4 END
|
jbe@532
|
2228 END
|
jbe@532
|
2229 END AS "preference",
|
jbe@532
|
2230 "opinion"."suggestion_id"
|
jbe@532
|
2231 FROM "opinion"
|
jbe@532
|
2232 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
|
jbe@532
|
2233 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2234 JOIN "direct_interest_snapshot"
|
jbe@532
|
2235 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2236 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
2237 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
|
jbe@532
|
2238
|
jbe@532
|
2239
|
jbe@532
|
2240 CREATE VIEW "expired_session" AS
|
jbe@532
|
2241 SELECT * FROM "session" WHERE now() > "expiry";
|
jbe@532
|
2242
|
jbe@532
|
2243 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
|
jbe@532
|
2244 DELETE FROM "session" WHERE "id" = OLD."id";
|
jbe@532
|
2245
|
jbe@532
|
2246 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
|
jbe@532
|
2247 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
|
2248
|
jbe@532
|
2249
|
jbe@532
|
2250 CREATE VIEW "expired_token" AS
|
jbe@532
|
2251 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
|
jbe@532
|
2252 "token_type" = 'authorization' AND "used" AND EXISTS (
|
jbe@532
|
2253 SELECT NULL FROM "token" AS "other"
|
jbe@532
|
2254 WHERE "other"."authorization_token_id" = "id" ) );
|
jbe@532
|
2255
|
jbe@532
|
2256 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
|
jbe@532
|
2257 DELETE FROM "token" WHERE "id" = OLD."id";
|
jbe@532
|
2258
|
jbe@532
|
2259 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
|
2260
|
jbe@532
|
2261
|
jbe@532
|
2262 CREATE VIEW "unused_snapshot" AS
|
jbe@532
|
2263 SELECT "snapshot".* FROM "snapshot"
|
jbe@532
|
2264 LEFT JOIN "issue"
|
jbe@532
|
2265 ON "snapshot"."id" = "issue"."latest_snapshot_id"
|
jbe@532
|
2266 OR "snapshot"."id" = "issue"."admission_snapshot_id"
|
jbe@532
|
2267 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
|
jbe@532
|
2268 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
|
jbe@532
|
2269 WHERE "issue"."id" ISNULL;
|
jbe@532
|
2270
|
jbe@532
|
2271 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
|
jbe@532
|
2272 DELETE FROM "snapshot" WHERE "id" = OLD."id";
|
jbe@532
|
2273
|
jbe@532
|
2274 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
|
2275
|
jbe@532
|
2276
|
jbe@532
|
2277 CREATE VIEW "expired_snapshot" AS
|
jbe@532
|
2278 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
|
jbe@532
|
2279 WHERE "unused_snapshot"."calculated" <
|
jbe@532
|
2280 now() - "system_setting"."snapshot_retention";
|
jbe@532
|
2281
|
jbe@532
|
2282 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
|
jbe@532
|
2283 DELETE FROM "snapshot" WHERE "id" = OLD."id";
|
jbe@532
|
2284
|
jbe@532
|
2285 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
|
jbe@532
|
2286
|
jbe@532
|
2287
|
jbe@532
|
2288 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
|
2289
|
jbe@532
|
2290
|
jbe@532
|
2291 CREATE OR REPLACE FUNCTION "delegation_chain"
|
jbe@532
|
2292 ( "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2293 "unit_id_p" "unit"."id"%TYPE,
|
jbe@532
|
2294 "area_id_p" "area"."id"%TYPE,
|
jbe@532
|
2295 "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2296 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
|
jbe@532
|
2297 "simulate_default_p" BOOLEAN DEFAULT FALSE )
|
jbe@532
|
2298 RETURNS SETOF "delegation_chain_row"
|
jbe@532
|
2299 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@532
|
2300 DECLARE
|
jbe@532
|
2301 "scope_v" "delegation_scope";
|
jbe@532
|
2302 "unit_id_v" "unit"."id"%TYPE;
|
jbe@532
|
2303 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
2304 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
2305 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@532
|
2306 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@532
|
2307 "output_row" "delegation_chain_row";
|
jbe@532
|
2308 "output_rows" "delegation_chain_row"[];
|
jbe@532
|
2309 "simulate_v" BOOLEAN;
|
jbe@532
|
2310 "simulate_here_v" BOOLEAN;
|
jbe@532
|
2311 "delegation_row" "delegation"%ROWTYPE;
|
jbe@532
|
2312 "row_count" INT4;
|
jbe@532
|
2313 "i" INT4;
|
jbe@532
|
2314 "loop_v" BOOLEAN;
|
jbe@532
|
2315 BEGIN
|
jbe@532
|
2316 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
|
jbe@532
|
2317 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
|
jbe@532
|
2318 END IF;
|
jbe@532
|
2319 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
|
jbe@532
|
2320 "simulate_v" := TRUE;
|
jbe@532
|
2321 ELSE
|
jbe@532
|
2322 "simulate_v" := FALSE;
|
jbe@532
|
2323 END IF;
|
jbe@532
|
2324 IF
|
jbe@532
|
2325 "unit_id_p" NOTNULL AND
|
jbe@532
|
2326 "area_id_p" ISNULL AND
|
jbe@532
|
2327 "issue_id_p" ISNULL
|
jbe@532
|
2328 THEN
|
jbe@532
|
2329 "scope_v" := 'unit';
|
jbe@532
|
2330 "unit_id_v" := "unit_id_p";
|
jbe@532
|
2331 ELSIF
|
jbe@532
|
2332 "unit_id_p" ISNULL AND
|
jbe@532
|
2333 "area_id_p" NOTNULL AND
|
jbe@532
|
2334 "issue_id_p" ISNULL
|
jbe@532
|
2335 THEN
|
jbe@532
|
2336 "scope_v" := 'area';
|
jbe@532
|
2337 "area_id_v" := "area_id_p";
|
jbe@532
|
2338 SELECT "unit_id" INTO "unit_id_v"
|
jbe@532
|
2339 FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2340 ELSIF
|
jbe@532
|
2341 "unit_id_p" ISNULL AND
|
jbe@532
|
2342 "area_id_p" ISNULL AND
|
jbe@532
|
2343 "issue_id_p" NOTNULL
|
jbe@532
|
2344 THEN
|
jbe@532
|
2345 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2346 IF "issue_row"."id" ISNULL THEN
|
jbe@532
|
2347 RETURN;
|
jbe@532
|
2348 END IF;
|
jbe@532
|
2349 IF "issue_row"."closed" NOTNULL THEN
|
jbe@532
|
2350 IF "simulate_v" THEN
|
jbe@532
|
2351 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
|
jbe@532
|
2352 END IF;
|
jbe@532
|
2353 FOR "output_row" IN
|
jbe@532
|
2354 SELECT * FROM
|
jbe@532
|
2355 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
|
jbe@532
|
2356 LOOP
|
jbe@532
|
2357 RETURN NEXT "output_row";
|
jbe@532
|
2358 END LOOP;
|
jbe@532
|
2359 RETURN;
|
jbe@532
|
2360 END IF;
|
jbe@532
|
2361 "scope_v" := 'issue';
|
jbe@532
|
2362 SELECT "area_id" INTO "area_id_v"
|
jbe@532
|
2363 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2364 SELECT "unit_id" INTO "unit_id_v"
|
jbe@532
|
2365 FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2366 ELSE
|
jbe@532
|
2367 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
|
jbe@532
|
2368 END IF;
|
jbe@532
|
2369 "visited_member_ids" := '{}';
|
jbe@532
|
2370 "loop_member_id_v" := NULL;
|
jbe@532
|
2371 "output_rows" := '{}';
|
jbe@532
|
2372 "output_row"."index" := 0;
|
jbe@532
|
2373 "output_row"."member_id" := "member_id_p";
|
jbe@532
|
2374 "output_row"."member_valid" := TRUE;
|
jbe@532
|
2375 "output_row"."participation" := FALSE;
|
jbe@532
|
2376 "output_row"."overridden" := FALSE;
|
jbe@532
|
2377 "output_row"."disabled_out" := FALSE;
|
jbe@532
|
2378 "output_row"."scope_out" := NULL;
|
jbe@532
|
2379 LOOP
|
jbe@532
|
2380 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@532
|
2381 "loop_member_id_v" := "output_row"."member_id";
|
jbe@532
|
2382 ELSE
|
jbe@532
|
2383 "visited_member_ids" :=
|
jbe@532
|
2384 "visited_member_ids" || "output_row"."member_id";
|
jbe@532
|
2385 END IF;
|
jbe@532
|
2386 IF "output_row"."participation" ISNULL THEN
|
jbe@532
|
2387 "output_row"."overridden" := NULL;
|
jbe@532
|
2388 ELSIF "output_row"."participation" THEN
|
jbe@532
|
2389 "output_row"."overridden" := TRUE;
|
jbe@532
|
2390 END IF;
|
jbe@532
|
2391 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@532
|
2392 "output_row"."member_valid" := EXISTS (
|
jbe@556
|
2393 SELECT NULL FROM "member" JOIN "privilege"
|
jbe@556
|
2394 ON "privilege"."member_id" = "member"."id"
|
jbe@556
|
2395 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@532
|
2396 WHERE "id" = "output_row"."member_id"
|
jbe@556
|
2397 AND "member"."active" AND "privilege"."voting_right"
|
jbe@532
|
2398 );
|
jbe@532
|
2399 "simulate_here_v" := (
|
jbe@532
|
2400 "simulate_v" AND
|
jbe@532
|
2401 "output_row"."member_id" = "member_id_p"
|
jbe@532
|
2402 );
|
jbe@532
|
2403 "delegation_row" := ROW(NULL);
|
jbe@532
|
2404 IF "output_row"."member_valid" OR "simulate_here_v" THEN
|
jbe@532
|
2405 IF "scope_v" = 'unit' THEN
|
jbe@532
|
2406 IF NOT "simulate_here_v" THEN
|
jbe@532
|
2407 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2408 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2409 AND "unit_id" = "unit_id_v";
|
jbe@532
|
2410 END IF;
|
jbe@532
|
2411 ELSIF "scope_v" = 'area' THEN
|
jbe@532
|
2412 IF "simulate_here_v" THEN
|
jbe@532
|
2413 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@532
|
2414 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2415 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2416 AND "unit_id" = "unit_id_v";
|
jbe@532
|
2417 END IF;
|
jbe@532
|
2418 ELSE
|
jbe@532
|
2419 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2420 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2421 AND (
|
jbe@532
|
2422 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2423 "area_id" = "area_id_v"
|
jbe@532
|
2424 )
|
jbe@532
|
2425 ORDER BY "scope" DESC;
|
jbe@532
|
2426 END IF;
|
jbe@532
|
2427 ELSIF "scope_v" = 'issue' THEN
|
jbe@532
|
2428 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@532
|
2429 "output_row"."participation" := EXISTS (
|
jbe@532
|
2430 SELECT NULL FROM "interest"
|
jbe@532
|
2431 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
2432 AND "member_id" = "output_row"."member_id"
|
jbe@532
|
2433 );
|
jbe@532
|
2434 ELSE
|
jbe@532
|
2435 IF "output_row"."member_id" = "member_id_p" THEN
|
jbe@532
|
2436 "output_row"."participation" := EXISTS (
|
jbe@532
|
2437 SELECT NULL FROM "direct_voter"
|
jbe@532
|
2438 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
2439 AND "member_id" = "output_row"."member_id"
|
jbe@532
|
2440 );
|
jbe@532
|
2441 ELSE
|
jbe@532
|
2442 "output_row"."participation" := NULL;
|
jbe@532
|
2443 END IF;
|
jbe@532
|
2444 END IF;
|
jbe@532
|
2445 IF "simulate_here_v" THEN
|
jbe@532
|
2446 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@532
|
2447 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2448 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2449 AND (
|
jbe@532
|
2450 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2451 "area_id" = "area_id_v"
|
jbe@532
|
2452 )
|
jbe@532
|
2453 ORDER BY "scope" DESC;
|
jbe@532
|
2454 END IF;
|
jbe@532
|
2455 ELSE
|
jbe@532
|
2456 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@532
|
2457 WHERE "truster_id" = "output_row"."member_id"
|
jbe@532
|
2458 AND (
|
jbe@532
|
2459 "unit_id" = "unit_id_v" OR
|
jbe@532
|
2460 "area_id" = "area_id_v" OR
|
jbe@532
|
2461 "issue_id" = "issue_id_p"
|
jbe@532
|
2462 )
|
jbe@532
|
2463 ORDER BY "scope" DESC;
|
jbe@532
|
2464 END IF;
|
jbe@532
|
2465 END IF;
|
jbe@532
|
2466 ELSE
|
jbe@532
|
2467 "output_row"."participation" := FALSE;
|
jbe@532
|
2468 END IF;
|
jbe@532
|
2469 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
|
jbe@532
|
2470 "output_row"."scope_out" := "scope_v";
|
jbe@532
|
2471 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2472 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@532
|
2473 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@532
|
2474 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@532
|
2475 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2476 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@532
|
2477 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@532
|
2478 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@532
|
2479 "output_row"."disabled_out" := TRUE;
|
jbe@532
|
2480 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2481 EXIT;
|
jbe@532
|
2482 ELSE
|
jbe@532
|
2483 "output_row"."scope_out" := NULL;
|
jbe@532
|
2484 "output_rows" := "output_rows" || "output_row";
|
jbe@532
|
2485 EXIT;
|
jbe@532
|
2486 END IF;
|
jbe@532
|
2487 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@532
|
2488 "output_row"."index" := "output_row"."index" + 1;
|
jbe@532
|
2489 END LOOP;
|
jbe@532
|
2490 "row_count" := array_upper("output_rows", 1);
|
jbe@532
|
2491 "i" := 1;
|
jbe@532
|
2492 "loop_v" := FALSE;
|
jbe@532
|
2493 LOOP
|
jbe@532
|
2494 "output_row" := "output_rows"["i"];
|
jbe@532
|
2495 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
|
jbe@532
|
2496 IF "loop_v" THEN
|
jbe@532
|
2497 IF "i" + 1 = "row_count" THEN
|
jbe@532
|
2498 "output_row"."loop" := 'last';
|
jbe@532
|
2499 ELSIF "i" = "row_count" THEN
|
jbe@532
|
2500 "output_row"."loop" := 'repetition';
|
jbe@532
|
2501 ELSE
|
jbe@532
|
2502 "output_row"."loop" := 'intermediate';
|
jbe@532
|
2503 END IF;
|
jbe@532
|
2504 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@532
|
2505 "output_row"."loop" := 'first';
|
jbe@532
|
2506 "loop_v" := TRUE;
|
jbe@532
|
2507 END IF;
|
jbe@532
|
2508 IF "scope_v" = 'unit' THEN
|
jbe@532
|
2509 "output_row"."participation" := NULL;
|
jbe@532
|
2510 END IF;
|
jbe@532
|
2511 RETURN NEXT "output_row";
|
jbe@532
|
2512 "i" := "i" + 1;
|
jbe@532
|
2513 END LOOP;
|
jbe@532
|
2514 RETURN;
|
jbe@532
|
2515 END;
|
jbe@532
|
2516 $$;
|
jbe@532
|
2517
|
jbe@532
|
2518
|
jbe@532
|
2519 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
|
jbe@532
|
2520 ( "recipient_id_p" "member"."id"%TYPE )
|
jbe@532
|
2521 RETURNS SETOF "initiative_for_notification"
|
jbe@532
|
2522 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2523 DECLARE
|
jbe@532
|
2524 "result_row" "initiative_for_notification"%ROWTYPE;
|
jbe@532
|
2525 "last_draft_id_v" "draft"."id"%TYPE;
|
jbe@532
|
2526 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@532
|
2527 BEGIN
|
jbe@532
|
2528 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2529 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
jbe@532
|
2530 FOR "result_row" IN
|
jbe@532
|
2531 SELECT * FROM "initiative_for_notification"
|
jbe@532
|
2532 WHERE "recipient_id" = "recipient_id_p"
|
jbe@532
|
2533 LOOP
|
jbe@532
|
2534 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
jbe@532
|
2535 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
jbe@532
|
2536 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2537 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
jbe@532
|
2538 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
jbe@532
|
2539 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2540 INSERT INTO "notification_initiative_sent"
|
jbe@532
|
2541 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
jbe@532
|
2542 VALUES (
|
jbe@532
|
2543 "recipient_id_p",
|
jbe@532
|
2544 "result_row"."initiative_id",
|
jbe@532
|
2545 "last_draft_id_v",
|
jbe@532
|
2546 "last_suggestion_id_v" )
|
jbe@532
|
2547 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
jbe@532
|
2548 "last_draft_id" = "last_draft_id_v",
|
jbe@532
|
2549 "last_suggestion_id" = "last_suggestion_id_v";
|
jbe@532
|
2550 RETURN NEXT "result_row";
|
jbe@532
|
2551 END LOOP;
|
jbe@532
|
2552 DELETE FROM "notification_initiative_sent"
|
jbe@532
|
2553 USING "initiative", "issue"
|
jbe@532
|
2554 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
|
jbe@532
|
2555 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
|
jbe@532
|
2556 AND "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2557 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
jbe@532
|
2558 UPDATE "member" SET
|
jbe@532
|
2559 "notification_counter" = "notification_counter" + 1,
|
jbe@532
|
2560 "notification_sent" = now()
|
jbe@532
|
2561 WHERE "id" = "recipient_id_p";
|
jbe@532
|
2562 RETURN;
|
jbe@532
|
2563 END;
|
jbe@532
|
2564 $$;
|
jbe@532
|
2565
|
jbe@532
|
2566
|
jbe@532
|
2567 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
|
jbe@532
|
2568 RETURNS VOID
|
jbe@532
|
2569 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2570 BEGIN
|
jbe@532
|
2571 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2572 DELETE FROM "member_count";
|
jbe@532
|
2573 INSERT INTO "member_count" ("total_count")
|
jbe@532
|
2574 SELECT "total_count" FROM "member_count_view";
|
jbe@532
|
2575 UPDATE "unit" SET "member_count" = "view"."member_count"
|
jbe@532
|
2576 FROM "unit_member_count" AS "view"
|
jbe@532
|
2577 WHERE "view"."unit_id" = "unit"."id";
|
jbe@532
|
2578 RETURN;
|
jbe@532
|
2579 END;
|
jbe@532
|
2580 $$;
|
jbe@532
|
2581
|
jbe@532
|
2582 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
|
2583
|
jbe@532
|
2584
|
jbe@532
|
2585 DROP VIEW "remaining_harmonic_initiative_weight_summands";
|
jbe@532
|
2586 DROP VIEW "remaining_harmonic_supporter_weight";
|
jbe@532
|
2587
|
jbe@532
|
2588
|
jbe@532
|
2589 CREATE VIEW "remaining_harmonic_supporter_weight" AS
|
jbe@532
|
2590 SELECT
|
jbe@532
|
2591 "direct_interest_snapshot"."snapshot_id",
|
jbe@532
|
2592 "direct_interest_snapshot"."issue_id",
|
jbe@532
|
2593 "direct_interest_snapshot"."member_id",
|
jbe@532
|
2594 "direct_interest_snapshot"."weight" AS "weight_num",
|
jbe@532
|
2595 count("initiative"."id") AS "weight_den"
|
jbe@532
|
2596 FROM "issue"
|
jbe@532
|
2597 JOIN "direct_interest_snapshot"
|
jbe@532
|
2598 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
|
jbe@532
|
2599 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
|
jbe@532
|
2600 JOIN "initiative"
|
jbe@532
|
2601 ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
2602 AND "initiative"."harmonic_weight" ISNULL
|
jbe@532
|
2603 JOIN "direct_supporter_snapshot"
|
jbe@532
|
2604 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
|
jbe@532
|
2605 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
|
jbe@532
|
2606 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@532
|
2607 AND (
|
jbe@532
|
2608 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@532
|
2609 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@532
|
2610 )
|
jbe@532
|
2611 GROUP BY
|
jbe@532
|
2612 "direct_interest_snapshot"."snapshot_id",
|
jbe@532
|
2613 "direct_interest_snapshot"."issue_id",
|
jbe@532
|
2614 "direct_interest_snapshot"."member_id",
|
jbe@532
|
2615 "direct_interest_snapshot"."weight";
|
jbe@532
|
2616
|
jbe@532
|
2617
|
jbe@532
|
2618 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
|
jbe@532
|
2619 SELECT
|
jbe@532
|
2620 "initiative"."issue_id",
|
jbe@532
|
2621 "initiative"."id" AS "initiative_id",
|
jbe@532
|
2622 "initiative"."admitted",
|
jbe@532
|
2623 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
|
jbe@532
|
2624 "remaining_harmonic_supporter_weight"."weight_den"
|
jbe@532
|
2625 FROM "remaining_harmonic_supporter_weight"
|
jbe@532
|
2626 JOIN "initiative"
|
jbe@532
|
2627 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
|
jbe@532
|
2628 AND "initiative"."harmonic_weight" ISNULL
|
jbe@532
|
2629 JOIN "direct_supporter_snapshot"
|
jbe@532
|
2630 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
|
jbe@532
|
2631 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
|
jbe@532
|
2632 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@532
|
2633 AND (
|
jbe@532
|
2634 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@532
|
2635 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@532
|
2636 )
|
jbe@532
|
2637 GROUP BY
|
jbe@532
|
2638 "initiative"."issue_id",
|
jbe@532
|
2639 "initiative"."id",
|
jbe@532
|
2640 "initiative"."admitted",
|
jbe@532
|
2641 "remaining_harmonic_supporter_weight"."weight_den";
|
jbe@532
|
2642
|
jbe@532
|
2643
|
jbe@532
|
2644 DROP FUNCTION "create_population_snapshot"
|
jbe@532
|
2645 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2646
|
jbe@532
|
2647
|
jbe@532
|
2648 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
jbe@532
|
2649 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2650 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2651 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
|
jbe@532
|
2652
|
jbe@532
|
2653
|
jbe@532
|
2654 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
jbe@532
|
2655 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2656 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2657 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
|
jbe@532
|
2658
|
jbe@532
|
2659
|
jbe@532
|
2660 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
|
jbe@532
|
2661 ( "snapshot_id_p" "snapshot"."id"%TYPE,
|
jbe@532
|
2662 "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2663 "member_id_p" "member"."id"%TYPE,
|
jbe@532
|
2664 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@532
|
2665 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
jbe@532
|
2666 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2667 DECLARE
|
jbe@532
|
2668 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@532
|
2669 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
jbe@532
|
2670 "weight_v" INT4;
|
jbe@532
|
2671 "sub_weight_v" INT4;
|
jbe@532
|
2672 BEGIN
|
jbe@532
|
2673 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2674 "weight_v" := 0;
|
jbe@532
|
2675 FOR "issue_delegation_row" IN
|
jbe@532
|
2676 SELECT * FROM "issue_delegation"
|
jbe@532
|
2677 WHERE "trustee_id" = "member_id_p"
|
jbe@532
|
2678 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2679 LOOP
|
jbe@532
|
2680 IF NOT EXISTS (
|
jbe@532
|
2681 SELECT NULL FROM "direct_interest_snapshot"
|
jbe@532
|
2682 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2683 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2684 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@532
|
2685 ) AND NOT EXISTS (
|
jbe@532
|
2686 SELECT NULL FROM "delegating_interest_snapshot"
|
jbe@532
|
2687 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2688 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2689 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@532
|
2690 ) THEN
|
jbe@532
|
2691 "delegate_member_ids_v" :=
|
jbe@532
|
2692 "member_id_p" || "delegate_member_ids_p";
|
jbe@532
|
2693 INSERT INTO "delegating_interest_snapshot" (
|
jbe@532
|
2694 "snapshot_id",
|
jbe@532
|
2695 "issue_id",
|
jbe@532
|
2696 "member_id",
|
jbe@532
|
2697 "scope",
|
jbe@532
|
2698 "delegate_member_ids"
|
jbe@532
|
2699 ) VALUES (
|
jbe@532
|
2700 "snapshot_id_p",
|
jbe@532
|
2701 "issue_id_p",
|
jbe@532
|
2702 "issue_delegation_row"."truster_id",
|
jbe@532
|
2703 "issue_delegation_row"."scope",
|
jbe@532
|
2704 "delegate_member_ids_v"
|
jbe@532
|
2705 );
|
jbe@532
|
2706 "sub_weight_v" := 1 +
|
jbe@532
|
2707 "weight_of_added_delegations_for_snapshot"(
|
jbe@532
|
2708 "snapshot_id_p",
|
jbe@532
|
2709 "issue_id_p",
|
jbe@532
|
2710 "issue_delegation_row"."truster_id",
|
jbe@532
|
2711 "delegate_member_ids_v"
|
jbe@532
|
2712 );
|
jbe@532
|
2713 UPDATE "delegating_interest_snapshot"
|
jbe@532
|
2714 SET "weight" = "sub_weight_v"
|
jbe@532
|
2715 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@532
|
2716 AND "issue_id" = "issue_id_p"
|
jbe@532
|
2717 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@532
|
2718 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@532
|
2719 END IF;
|
jbe@532
|
2720 END LOOP;
|
jbe@532
|
2721 RETURN "weight_v";
|
jbe@532
|
2722 END;
|
jbe@532
|
2723 $$;
|
jbe@532
|
2724
|
jbe@532
|
2725 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
|
jbe@532
|
2726 ( "snapshot"."id"%TYPE,
|
jbe@532
|
2727 "issue"."id"%TYPE,
|
jbe@532
|
2728 "member"."id"%TYPE,
|
jbe@532
|
2729 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@532
|
2730 IS 'Helper function for "fill_snapshot" function';
|
jbe@532
|
2731
|
jbe@532
|
2732
|
jbe@532
|
2733 DROP FUNCTION "create_interest_snapshot"
|
jbe@532
|
2734 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2735
|
jbe@532
|
2736
|
jbe@532
|
2737 DROP FUNCTION "create_snapshot"
|
jbe@532
|
2738 ( "issue_id_p" "issue"."id"%TYPE );
|
jbe@532
|
2739
|
jbe@532
|
2740
|
jbe@532
|
2741 CREATE FUNCTION "take_snapshot"
|
jbe@532
|
2742 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2743 "area_id_p" "area"."id"%TYPE = NULL )
|
jbe@532
|
2744 RETURNS "snapshot"."id"%TYPE
|
jbe@532
|
2745 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2746 DECLARE
|
jbe@532
|
2747 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
2748 "unit_id_v" "unit"."id"%TYPE;
|
jbe@532
|
2749 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
2750 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
2751 "member_id_v" "member"."id"%TYPE;
|
jbe@532
|
2752 BEGIN
|
jbe@532
|
2753 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
|
jbe@532
|
2754 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
|
jbe@532
|
2755 END IF;
|
jbe@532
|
2756 PERFORM "require_transaction_isolation"();
|
jbe@532
|
2757 IF "issue_id_p" ISNULL THEN
|
jbe@532
|
2758 "area_id_v" := "area_id_p";
|
jbe@532
|
2759 ELSE
|
jbe@532
|
2760 SELECT "area_id" INTO "area_id_v"
|
jbe@532
|
2761 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
2762 END IF;
|
jbe@562
|
2763 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@532
|
2764 INSERT INTO "snapshot" ("area_id", "issue_id")
|
jbe@532
|
2765 VALUES ("area_id_v", "issue_id_p")
|
jbe@532
|
2766 RETURNING "id" INTO "snapshot_id_v";
|
jbe@532
|
2767 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
2768 SELECT "snapshot_id_v", "member_id"
|
jbe@532
|
2769 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
|
jbe@532
|
2770 UPDATE "snapshot" SET
|
jbe@532
|
2771 "population" = (
|
jbe@532
|
2772 SELECT count(1) FROM "snapshot_population"
|
jbe@532
|
2773 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2774 ) WHERE "id" = "snapshot_id_v";
|
jbe@532
|
2775 FOR "issue_id_v" IN
|
jbe@532
|
2776 SELECT "id" FROM "issue"
|
jbe@532
|
2777 WHERE CASE WHEN "issue_id_p" ISNULL THEN
|
jbe@532
|
2778 "area_id" = "area_id_p" AND
|
jbe@532
|
2779 "state" = 'admission'
|
jbe@532
|
2780 ELSE
|
jbe@532
|
2781 "id" = "issue_id_p"
|
jbe@532
|
2782 END
|
jbe@532
|
2783 LOOP
|
jbe@532
|
2784 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
|
jbe@532
|
2785 VALUES ("snapshot_id_v", "issue_id_v");
|
jbe@532
|
2786 INSERT INTO "direct_interest_snapshot"
|
jbe@532
|
2787 ("snapshot_id", "issue_id", "member_id")
|
jbe@532
|
2788 SELECT
|
jbe@532
|
2789 "snapshot_id_v" AS "snapshot_id",
|
jbe@532
|
2790 "issue_id_v" AS "issue_id",
|
jbe@532
|
2791 "member"."id" AS "member_id"
|
jbe@532
|
2792 FROM "issue"
|
jbe@532
|
2793 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@532
|
2794 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
jbe@532
|
2795 JOIN "member" ON "interest"."member_id" = "member"."id"
|
jbe@556
|
2796 JOIN "privilege"
|
jbe@556
|
2797 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@556
|
2798 AND "privilege"."member_id" = "member"."id"
|
jbe@532
|
2799 WHERE "issue"."id" = "issue_id_v"
|
jbe@556
|
2800 AND "member"."active" AND "privilege"."voting_right";
|
jbe@532
|
2801 FOR "member_id_v" IN
|
jbe@532
|
2802 SELECT "member_id" FROM "direct_interest_snapshot"
|
jbe@532
|
2803 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2804 AND "issue_id" = "issue_id_v"
|
jbe@532
|
2805 LOOP
|
jbe@532
|
2806 UPDATE "direct_interest_snapshot" SET
|
jbe@532
|
2807 "weight" = 1 +
|
jbe@532
|
2808 "weight_of_added_delegations_for_snapshot"(
|
jbe@532
|
2809 "snapshot_id_v",
|
jbe@532
|
2810 "issue_id_v",
|
jbe@532
|
2811 "member_id_v",
|
jbe@532
|
2812 '{}'
|
jbe@532
|
2813 )
|
jbe@532
|
2814 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2815 AND "issue_id" = "issue_id_v"
|
jbe@532
|
2816 AND "member_id" = "member_id_v";
|
jbe@532
|
2817 END LOOP;
|
jbe@532
|
2818 INSERT INTO "direct_supporter_snapshot"
|
jbe@532
|
2819 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
|
jbe@532
|
2820 "draft_id", "informed", "satisfied" )
|
jbe@532
|
2821 SELECT
|
jbe@532
|
2822 "snapshot_id_v" AS "snapshot_id",
|
jbe@532
|
2823 "issue_id_v" AS "issue_id",
|
jbe@532
|
2824 "initiative"."id" AS "initiative_id",
|
jbe@532
|
2825 "supporter"."member_id" AS "member_id",
|
jbe@532
|
2826 "supporter"."draft_id" AS "draft_id",
|
jbe@532
|
2827 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
jbe@532
|
2828 NOT EXISTS (
|
jbe@532
|
2829 SELECT NULL FROM "critical_opinion"
|
jbe@532
|
2830 WHERE "initiative_id" = "initiative"."id"
|
jbe@532
|
2831 AND "member_id" = "supporter"."member_id"
|
jbe@532
|
2832 ) AS "satisfied"
|
jbe@532
|
2833 FROM "initiative"
|
jbe@532
|
2834 JOIN "supporter"
|
jbe@532
|
2835 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@532
|
2836 JOIN "current_draft"
|
jbe@532
|
2837 ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@532
|
2838 JOIN "direct_interest_snapshot"
|
jbe@532
|
2839 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
|
jbe@532
|
2840 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
|
jbe@532
|
2841 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
jbe@532
|
2842 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@532
|
2843 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
2844 INSERT INTO "temporary_suggestion_counts"
|
jbe@532
|
2845 ( "id",
|
jbe@532
|
2846 "minus2_unfulfilled_count", "minus2_fulfilled_count",
|
jbe@532
|
2847 "minus1_unfulfilled_count", "minus1_fulfilled_count",
|
jbe@532
|
2848 "plus1_unfulfilled_count", "plus1_fulfilled_count",
|
jbe@532
|
2849 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
|
jbe@532
|
2850 SELECT
|
jbe@532
|
2851 "suggestion"."id",
|
jbe@532
|
2852 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2853 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2854 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2855 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2856 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2857 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2858 AND "opinion"."degree" = -2
|
jbe@532
|
2859 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2860 ) AS "minus2_unfulfilled_count",
|
jbe@532
|
2861 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2862 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2863 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2864 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2865 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2866 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2867 AND "opinion"."degree" = -2
|
jbe@532
|
2868 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2869 ) AS "minus2_fulfilled_count",
|
jbe@532
|
2870 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2871 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2872 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2873 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2874 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2875 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2876 AND "opinion"."degree" = -1
|
jbe@532
|
2877 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2878 ) AS "minus1_unfulfilled_count",
|
jbe@532
|
2879 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2880 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2881 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2882 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2883 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2884 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2885 AND "opinion"."degree" = -1
|
jbe@532
|
2886 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2887 ) AS "minus1_fulfilled_count",
|
jbe@532
|
2888 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2889 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2890 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2891 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2892 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2893 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2894 AND "opinion"."degree" = 1
|
jbe@532
|
2895 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2896 ) AS "plus1_unfulfilled_count",
|
jbe@532
|
2897 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2898 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2899 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2900 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2901 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2902 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2903 AND "opinion"."degree" = 1
|
jbe@532
|
2904 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2905 ) AS "plus1_fulfilled_count",
|
jbe@532
|
2906 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2907 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2908 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2909 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2910 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2911 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2912 AND "opinion"."degree" = 2
|
jbe@532
|
2913 AND "opinion"."fulfilled" = FALSE
|
jbe@532
|
2914 ) AS "plus2_unfulfilled_count",
|
jbe@532
|
2915 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2916 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@532
|
2917 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2918 AND "di"."issue_id" = "issue_id_v"
|
jbe@532
|
2919 AND "di"."member_id" = "opinion"."member_id"
|
jbe@532
|
2920 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@532
|
2921 AND "opinion"."degree" = 2
|
jbe@532
|
2922 AND "opinion"."fulfilled" = TRUE
|
jbe@532
|
2923 ) AS "plus2_fulfilled_count"
|
jbe@532
|
2924 FROM "suggestion" JOIN "initiative"
|
jbe@532
|
2925 ON "suggestion"."initiative_id" = "initiative"."id"
|
jbe@532
|
2926 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@532
|
2927 END LOOP;
|
jbe@532
|
2928 RETURN "snapshot_id_v";
|
jbe@532
|
2929 END;
|
jbe@532
|
2930 $$;
|
jbe@532
|
2931
|
jbe@532
|
2932 COMMENT ON FUNCTION "take_snapshot"
|
jbe@532
|
2933 ( "issue"."id"%TYPE,
|
jbe@532
|
2934 "area"."id"%TYPE )
|
jbe@532
|
2935 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
|
2936
|
jbe@532
|
2937
|
jbe@532
|
2938 DROP FUNCTION "set_snapshot_event"
|
jbe@532
|
2939 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
2940 "event_p" "snapshot_event" );
|
jbe@532
|
2941
|
jbe@532
|
2942
|
jbe@532
|
2943 CREATE FUNCTION "finish_snapshot"
|
jbe@532
|
2944 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@532
|
2945 RETURNS VOID
|
jbe@532
|
2946 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
2947 DECLARE
|
jbe@532
|
2948 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
2949 BEGIN
|
jbe@532
|
2950 -- NOTE: function does not require snapshot isolation but we don't call
|
jbe@532
|
2951 -- "dont_require_snapshot_isolation" here because this function is
|
jbe@532
|
2952 -- also invoked by "check_issue"
|
jbe@532
|
2953 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
|
jbe@532
|
2954 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
|
jbe@532
|
2955 ORDER BY "id" DESC LIMIT 1;
|
jbe@532
|
2956 UPDATE "issue" SET
|
jbe@532
|
2957 "calculated" = "snapshot"."calculated",
|
jbe@532
|
2958 "latest_snapshot_id" = "snapshot_id_v",
|
jbe@568
|
2959 "population" = "snapshot"."population",
|
jbe@568
|
2960 "initiative_quorum" = CASE WHEN
|
jbe@568
|
2961 "policy"."initiative_quorum" > ceil(
|
jbe@568
|
2962 ( "issue"."population"::INT8 *
|
jbe@568
|
2963 "policy"."initiative_quorum_num"::INT8 ) /
|
jbe@568
|
2964 "policy"."initiative_quorum_den"::FLOAT8
|
jbe@568
|
2965 )::INT4
|
jbe@568
|
2966 THEN
|
jbe@568
|
2967 "policy"."initiative_quorum"
|
jbe@568
|
2968 ELSE
|
jbe@568
|
2969 ceil(
|
jbe@568
|
2970 ( "issue"."population"::INT8 *
|
jbe@568
|
2971 "policy"."initiative_quorum_num"::INT8 ) /
|
jbe@568
|
2972 "policy"."initiative_quorum_den"::FLOAT8
|
jbe@568
|
2973 )::INT4
|
jbe@568
|
2974 END
|
jbe@568
|
2975 FROM "snapshot", "policy"
|
jbe@532
|
2976 WHERE "issue"."id" = "issue_id_p"
|
jbe@568
|
2977 AND "snapshot"."id" = "snapshot_id_v"
|
jbe@568
|
2978 AND "policy"."id" = "issue"."policy_id";
|
jbe@532
|
2979 UPDATE "initiative" SET
|
jbe@532
|
2980 "supporter_count" = (
|
jbe@532
|
2981 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2982 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2983 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2984 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2985 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2986 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2987 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2988 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2989 ),
|
jbe@532
|
2990 "informed_supporter_count" = (
|
jbe@532
|
2991 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
2992 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
2993 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
2994 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
2995 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2996 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
2997 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
2998 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
2999 AND "ds"."informed"
|
jbe@532
|
3000 ),
|
jbe@532
|
3001 "satisfied_supporter_count" = (
|
jbe@532
|
3002 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
3003 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
3004 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
3005 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
3006 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
3007 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
3008 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
3009 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
3010 AND "ds"."satisfied"
|
jbe@532
|
3011 ),
|
jbe@532
|
3012 "satisfied_informed_supporter_count" = (
|
jbe@532
|
3013 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@532
|
3014 FROM "direct_interest_snapshot" AS "di"
|
jbe@532
|
3015 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@532
|
3016 ON "di"."member_id" = "ds"."member_id"
|
jbe@532
|
3017 WHERE "di"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
3018 AND "di"."issue_id" = "issue_id_p"
|
jbe@532
|
3019 AND "ds"."snapshot_id" = "snapshot_id_v"
|
jbe@532
|
3020 AND "ds"."initiative_id" = "initiative"."id"
|
jbe@532
|
3021 AND "ds"."informed"
|
jbe@532
|
3022 AND "ds"."satisfied"
|
jbe@532
|
3023 )
|
jbe@532
|
3024 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3025 UPDATE "suggestion" SET
|
jbe@532
|
3026 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
|
jbe@532
|
3027 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
|
jbe@532
|
3028 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
|
jbe@532
|
3029 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
|
jbe@532
|
3030 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
|
jbe@532
|
3031 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
|
jbe@532
|
3032 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
|
jbe@532
|
3033 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
|
jbe@532
|
3034 FROM "temporary_suggestion_counts" AS "temp", "initiative"
|
jbe@532
|
3035 WHERE "temp"."id" = "suggestion"."id"
|
jbe@532
|
3036 AND "initiative"."issue_id" = "issue_id_p"
|
jbe@532
|
3037 AND "suggestion"."initiative_id" = "initiative"."id";
|
jbe@532
|
3038 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
3039 RETURN;
|
jbe@532
|
3040 END;
|
jbe@532
|
3041 $$;
|
jbe@532
|
3042
|
jbe@532
|
3043 COMMENT ON FUNCTION "finish_snapshot"
|
jbe@532
|
3044 ( "issue"."id"%TYPE )
|
jbe@532
|
3045 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
|
3046
|
jbe@532
|
3047
|
jbe@532
|
3048 CREATE FUNCTION "issue_admission"
|
jbe@532
|
3049 ( "area_id_p" "area"."id"%TYPE )
|
jbe@532
|
3050 RETURNS BOOLEAN
|
jbe@532
|
3051 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3052 DECLARE
|
jbe@532
|
3053 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
3054 BEGIN
|
jbe@532
|
3055 PERFORM "dont_require_transaction_isolation"();
|
jbe@532
|
3056 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
|
jbe@532
|
3057 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
|
jbe@532
|
3058 FROM "area_quorum" AS "view"
|
jbe@532
|
3059 WHERE "area"."id" = "view"."area_id"
|
jbe@532
|
3060 AND "area"."id" = "area_id_p";
|
jbe@532
|
3061 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
|
jbe@532
|
3062 WHERE "area_id" = "area_id_p";
|
jbe@532
|
3063 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
|
jbe@532
|
3064 UPDATE "issue" SET
|
jbe@532
|
3065 "admission_snapshot_id" = "latest_snapshot_id",
|
jbe@532
|
3066 "state" = 'discussion',
|
jbe@532
|
3067 "accepted" = now(),
|
jbe@568
|
3068 "phase_finished" = NULL,
|
jbe@568
|
3069 "issue_quorum" = "issue_quorum"."issue_quorum"
|
jbe@568
|
3070 FROM "issue_quorum"
|
jbe@568
|
3071 WHERE "id" = "issue_id_v"
|
jbe@568
|
3072 AND "issue_quorum"."issue_id" = "issue_id_v";
|
jbe@532
|
3073 RETURN TRUE;
|
jbe@532
|
3074 END;
|
jbe@532
|
3075 $$;
|
jbe@532
|
3076
|
jbe@532
|
3077 COMMENT ON FUNCTION "issue_admission"
|
jbe@532
|
3078 ( "area"."id"%TYPE )
|
jbe@532
|
3079 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
|
3080
|
jbe@532
|
3081
|
jbe@532
|
3082 CREATE OR REPLACE FUNCTION "check_issue"
|
jbe@532
|
3083 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@532
|
3084 "persist" "check_issue_persistence" )
|
jbe@532
|
3085 RETURNS "check_issue_persistence"
|
jbe@532
|
3086 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3087 DECLARE
|
jbe@532
|
3088 "issue_row" "issue"%ROWTYPE;
|
jbe@532
|
3089 "last_calculated_v" "snapshot"."calculated"%TYPE;
|
jbe@532
|
3090 "policy_row" "policy"%ROWTYPE;
|
jbe@532
|
3091 "initiative_row" "initiative"%ROWTYPE;
|
jbe@532
|
3092 "state_v" "issue_state";
|
jbe@532
|
3093 BEGIN
|
jbe@532
|
3094 PERFORM "require_transaction_isolation"();
|
jbe@532
|
3095 IF "persist" ISNULL THEN
|
jbe@532
|
3096 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3097 FOR UPDATE;
|
jbe@532
|
3098 SELECT "calculated" INTO "last_calculated_v"
|
jbe@532
|
3099 FROM "snapshot" JOIN "snapshot_issue"
|
jbe@532
|
3100 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
|
jbe@532
|
3101 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
|
jbe@532
|
3102 IF "issue_row"."closed" NOTNULL THEN
|
jbe@532
|
3103 RETURN NULL;
|
jbe@532
|
3104 END IF;
|
jbe@532
|
3105 "persist"."state" := "issue_row"."state";
|
jbe@532
|
3106 IF
|
jbe@532
|
3107 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
|
jbe@532
|
3108 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
|
jbe@532
|
3109 ( "issue_row"."state" = 'discussion' AND now() >=
|
jbe@532
|
3110 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
|
jbe@532
|
3111 ( "issue_row"."state" = 'verification' AND now() >=
|
jbe@532
|
3112 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
|
jbe@532
|
3113 ( "issue_row"."state" = 'voting' AND now() >=
|
jbe@532
|
3114 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
|
jbe@532
|
3115 THEN
|
jbe@532
|
3116 "persist"."phase_finished" := TRUE;
|
jbe@532
|
3117 ELSE
|
jbe@532
|
3118 "persist"."phase_finished" := FALSE;
|
jbe@532
|
3119 END IF;
|
jbe@532
|
3120 IF
|
jbe@532
|
3121 NOT EXISTS (
|
jbe@532
|
3122 -- all initiatives are revoked
|
jbe@532
|
3123 SELECT NULL FROM "initiative"
|
jbe@532
|
3124 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@532
|
3125 ) AND (
|
jbe@532
|
3126 -- and issue has not been accepted yet
|
jbe@532
|
3127 "persist"."state" = 'admission' OR
|
jbe@532
|
3128 -- or verification time has elapsed
|
jbe@532
|
3129 ( "persist"."state" = 'verification' AND
|
jbe@532
|
3130 "persist"."phase_finished" ) OR
|
jbe@532
|
3131 -- or no initiatives have been revoked lately
|
jbe@532
|
3132 NOT EXISTS (
|
jbe@532
|
3133 SELECT NULL FROM "initiative"
|
jbe@532
|
3134 WHERE "issue_id" = "issue_id_p"
|
jbe@532
|
3135 AND now() < "revoked" + "issue_row"."verification_time"
|
jbe@532
|
3136 )
|
jbe@532
|
3137 )
|
jbe@532
|
3138 THEN
|
jbe@532
|
3139 "persist"."issue_revoked" := TRUE;
|
jbe@532
|
3140 ELSE
|
jbe@532
|
3141 "persist"."issue_revoked" := FALSE;
|
jbe@532
|
3142 END IF;
|
jbe@532
|
3143 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
|
jbe@532
|
3144 UPDATE "issue" SET "phase_finished" = now()
|
jbe@532
|
3145 WHERE "id" = "issue_row"."id";
|
jbe@532
|
3146 RETURN "persist";
|
jbe@532
|
3147 ELSIF
|
jbe@532
|
3148 "persist"."state" IN ('admission', 'discussion', 'verification')
|
jbe@532
|
3149 THEN
|
jbe@532
|
3150 RETURN "persist";
|
jbe@532
|
3151 ELSE
|
jbe@532
|
3152 RETURN NULL;
|
jbe@532
|
3153 END IF;
|
jbe@532
|
3154 END IF;
|
jbe@532
|
3155 IF
|
jbe@532
|
3156 "persist"."state" IN ('admission', 'discussion', 'verification') AND
|
jbe@532
|
3157 coalesce("persist"."snapshot_created", FALSE) = FALSE
|
jbe@532
|
3158 THEN
|
jbe@532
|
3159 IF "persist"."state" != 'admission' THEN
|
jbe@532
|
3160 PERFORM "take_snapshot"("issue_id_p");
|
jbe@532
|
3161 PERFORM "finish_snapshot"("issue_id_p");
|
jbe@568
|
3162 ELSE
|
jbe@568
|
3163 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
|
jbe@568
|
3164 FROM "issue_quorum"
|
jbe@568
|
3165 WHERE "id" = "issue_id_p"
|
jbe@568
|
3166 AND "issue_quorum"."issue_id" = "issue_id_p";
|
jbe@532
|
3167 END IF;
|
jbe@532
|
3168 "persist"."snapshot_created" = TRUE;
|
jbe@532
|
3169 IF "persist"."phase_finished" THEN
|
jbe@532
|
3170 IF "persist"."state" = 'admission' THEN
|
jbe@561
|
3171 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3172 WHERE "id" = "issue_id_p";
|
jbe@532
|
3173 ELSIF "persist"."state" = 'discussion' THEN
|
jbe@561
|
3174 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3175 WHERE "id" = "issue_id_p";
|
jbe@532
|
3176 ELSIF "persist"."state" = 'verification' THEN
|
jbe@561
|
3177 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
|
jbe@561
|
3178 WHERE "id" = "issue_id_p";
|
jbe@532
|
3179 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@532
|
3180 FOR "initiative_row" IN
|
jbe@532
|
3181 SELECT * FROM "initiative"
|
jbe@532
|
3182 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
jbe@532
|
3183 FOR UPDATE
|
jbe@532
|
3184 LOOP
|
jbe@532
|
3185 IF
|
jbe@568
|
3186 "initiative_row"."polling" OR
|
jbe@568
|
3187 "initiative_row"."satisfied_supporter_count" >=
|
jbe@568
|
3188 "issue_row"."initiative_quorum"
|
jbe@532
|
3189 THEN
|
jbe@532
|
3190 UPDATE "initiative" SET "admitted" = TRUE
|
jbe@532
|
3191 WHERE "id" = "initiative_row"."id";
|
jbe@532
|
3192 ELSE
|
jbe@532
|
3193 UPDATE "initiative" SET "admitted" = FALSE
|
jbe@532
|
3194 WHERE "id" = "initiative_row"."id";
|
jbe@532
|
3195 END IF;
|
jbe@532
|
3196 END LOOP;
|
jbe@532
|
3197 END IF;
|
jbe@532
|
3198 END IF;
|
jbe@532
|
3199 RETURN "persist";
|
jbe@532
|
3200 END IF;
|
jbe@532
|
3201 IF
|
jbe@532
|
3202 "persist"."state" IN ('admission', 'discussion', 'verification') AND
|
jbe@532
|
3203 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
|
jbe@532
|
3204 THEN
|
jbe@532
|
3205 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
|
jbe@532
|
3206 "persist"."harmonic_weights_set" = TRUE;
|
jbe@532
|
3207 IF
|
jbe@532
|
3208 "persist"."phase_finished" OR
|
jbe@532
|
3209 "persist"."issue_revoked" OR
|
jbe@532
|
3210 "persist"."state" = 'admission'
|
jbe@532
|
3211 THEN
|
jbe@532
|
3212 RETURN "persist";
|
jbe@532
|
3213 ELSE
|
jbe@532
|
3214 RETURN NULL;
|
jbe@532
|
3215 END IF;
|
jbe@532
|
3216 END IF;
|
jbe@532
|
3217 IF "persist"."issue_revoked" THEN
|
jbe@532
|
3218 IF "persist"."state" = 'admission' THEN
|
jbe@532
|
3219 "state_v" := 'canceled_revoked_before_accepted';
|
jbe@532
|
3220 ELSIF "persist"."state" = 'discussion' THEN
|
jbe@532
|
3221 "state_v" := 'canceled_after_revocation_during_discussion';
|
jbe@532
|
3222 ELSIF "persist"."state" = 'verification' THEN
|
jbe@532
|
3223 "state_v" := 'canceled_after_revocation_during_verification';
|
jbe@532
|
3224 END IF;
|
jbe@532
|
3225 UPDATE "issue" SET
|
jbe@532
|
3226 "state" = "state_v",
|
jbe@532
|
3227 "closed" = "phase_finished",
|
jbe@532
|
3228 "phase_finished" = NULL
|
jbe@532
|
3229 WHERE "id" = "issue_id_p";
|
jbe@532
|
3230 RETURN NULL;
|
jbe@532
|
3231 END IF;
|
jbe@532
|
3232 IF "persist"."state" = 'admission' THEN
|
jbe@532
|
3233 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3234 FOR UPDATE;
|
jbe@532
|
3235 IF "issue_row"."phase_finished" NOTNULL THEN
|
jbe@532
|
3236 UPDATE "issue" SET
|
jbe@532
|
3237 "state" = 'canceled_issue_not_accepted',
|
jbe@532
|
3238 "closed" = "phase_finished",
|
jbe@532
|
3239 "phase_finished" = NULL
|
jbe@532
|
3240 WHERE "id" = "issue_id_p";
|
jbe@532
|
3241 END IF;
|
jbe@532
|
3242 RETURN NULL;
|
jbe@532
|
3243 END IF;
|
jbe@532
|
3244 IF "persist"."phase_finished" THEN
|
jbe@532
|
3245 IF "persist"."state" = 'discussion' THEN
|
jbe@532
|
3246 UPDATE "issue" SET
|
jbe@532
|
3247 "state" = 'verification',
|
jbe@532
|
3248 "half_frozen" = "phase_finished",
|
jbe@532
|
3249 "phase_finished" = NULL
|
jbe@532
|
3250 WHERE "id" = "issue_id_p";
|
jbe@532
|
3251 RETURN NULL;
|
jbe@532
|
3252 END IF;
|
jbe@532
|
3253 IF "persist"."state" = 'verification' THEN
|
jbe@532
|
3254 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@532
|
3255 FOR UPDATE;
|
jbe@532
|
3256 SELECT * INTO "policy_row" FROM "policy"
|
jbe@532
|
3257 WHERE "id" = "issue_row"."policy_id";
|
jbe@532
|
3258 IF EXISTS (
|
jbe@532
|
3259 SELECT NULL FROM "initiative"
|
jbe@532
|
3260 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
jbe@532
|
3261 ) THEN
|
jbe@532
|
3262 UPDATE "issue" SET
|
jbe@532
|
3263 "state" = 'voting',
|
jbe@532
|
3264 "fully_frozen" = "phase_finished",
|
jbe@532
|
3265 "phase_finished" = NULL
|
jbe@532
|
3266 WHERE "id" = "issue_id_p";
|
jbe@532
|
3267 ELSE
|
jbe@532
|
3268 UPDATE "issue" SET
|
jbe@532
|
3269 "state" = 'canceled_no_initiative_admitted',
|
jbe@532
|
3270 "fully_frozen" = "phase_finished",
|
jbe@532
|
3271 "closed" = "phase_finished",
|
jbe@532
|
3272 "phase_finished" = NULL
|
jbe@532
|
3273 WHERE "id" = "issue_id_p";
|
jbe@532
|
3274 -- NOTE: The following DELETE statements have effect only when
|
jbe@532
|
3275 -- issue state has been manipulated
|
jbe@532
|
3276 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3277 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3278 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3279 END IF;
|
jbe@532
|
3280 RETURN NULL;
|
jbe@532
|
3281 END IF;
|
jbe@532
|
3282 IF "persist"."state" = 'voting' THEN
|
jbe@532
|
3283 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
|
jbe@532
|
3284 PERFORM "close_voting"("issue_id_p");
|
jbe@532
|
3285 "persist"."closed_voting" = TRUE;
|
jbe@532
|
3286 RETURN "persist";
|
jbe@532
|
3287 END IF;
|
jbe@532
|
3288 PERFORM "calculate_ranks"("issue_id_p");
|
jbe@532
|
3289 RETURN NULL;
|
jbe@532
|
3290 END IF;
|
jbe@532
|
3291 END IF;
|
jbe@532
|
3292 RAISE WARNING 'should not happen';
|
jbe@532
|
3293 RETURN NULL;
|
jbe@532
|
3294 END;
|
jbe@532
|
3295 $$;
|
jbe@532
|
3296
|
jbe@532
|
3297
|
jbe@532
|
3298 CREATE OR REPLACE FUNCTION "check_everything"()
|
jbe@532
|
3299 RETURNS VOID
|
jbe@532
|
3300 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3301 DECLARE
|
jbe@532
|
3302 "area_id_v" "area"."id"%TYPE;
|
jbe@532
|
3303 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@532
|
3304 "issue_id_v" "issue"."id"%TYPE;
|
jbe@532
|
3305 "persist_v" "check_issue_persistence";
|
jbe@532
|
3306 BEGIN
|
jbe@532
|
3307 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
|
jbe@532
|
3308 DELETE FROM "expired_session";
|
jbe@532
|
3309 DELETE FROM "expired_token";
|
jbe@532
|
3310 DELETE FROM "expired_snapshot";
|
jbe@532
|
3311 PERFORM "check_activity"();
|
jbe@532
|
3312 PERFORM "calculate_member_counts"();
|
jbe@532
|
3313 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
|
jbe@532
|
3314 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
|
jbe@532
|
3315 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
|
jbe@532
|
3316 WHERE "snapshot_id" = "snapshot_id_v";
|
jbe@532
|
3317 LOOP
|
jbe@532
|
3318 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
|
jbe@532
|
3319 END LOOP;
|
jbe@532
|
3320 END LOOP;
|
jbe@532
|
3321 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
|
jbe@532
|
3322 "persist_v" := NULL;
|
jbe@532
|
3323 LOOP
|
jbe@532
|
3324 "persist_v" := "check_issue"("issue_id_v", "persist_v");
|
jbe@532
|
3325 EXIT WHEN "persist_v" ISNULL;
|
jbe@532
|
3326 END LOOP;
|
jbe@532
|
3327 END LOOP;
|
jbe@532
|
3328 RETURN;
|
jbe@532
|
3329 END;
|
jbe@532
|
3330 $$;
|
jbe@532
|
3331
|
jbe@532
|
3332 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
|
3333
|
jbe@532
|
3334
|
jbe@532
|
3335 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@532
|
3336 RETURNS VOID
|
jbe@532
|
3337 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3338 BEGIN
|
jbe@532
|
3339 IF EXISTS (
|
jbe@532
|
3340 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
|
jbe@532
|
3341 ) THEN
|
jbe@532
|
3342 -- override protection triggers:
|
jbe@532
|
3343 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@532
|
3344 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@532
|
3345 -- clean data:
|
jbe@532
|
3346 DELETE FROM "delegating_voter"
|
jbe@532
|
3347 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3348 DELETE FROM "direct_voter"
|
jbe@532
|
3349 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3350 DELETE FROM "delegating_interest_snapshot"
|
jbe@532
|
3351 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3352 DELETE FROM "direct_interest_snapshot"
|
jbe@532
|
3353 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3354 DELETE FROM "non_voter"
|
jbe@532
|
3355 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3356 DELETE FROM "delegation"
|
jbe@532
|
3357 WHERE "issue_id" = "issue_id_p";
|
jbe@532
|
3358 DELETE FROM "supporter"
|
jbe@532
|
3359 USING "initiative" -- NOTE: due to missing index on issue_id
|
jbe@532
|
3360 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@532
|
3361 AND "supporter"."initiative_id" = "initiative_id";
|
jbe@532
|
3362 -- mark issue as cleaned:
|
jbe@532
|
3363 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
|
jbe@532
|
3364 -- finish overriding protection triggers (avoids garbage):
|
jbe@532
|
3365 DELETE FROM "temporary_transaction_data"
|
jbe@532
|
3366 WHERE "key" = 'override_protection_triggers';
|
jbe@532
|
3367 END IF;
|
jbe@532
|
3368 RETURN;
|
jbe@532
|
3369 END;
|
jbe@532
|
3370 $$;
|
jbe@532
|
3371
|
jbe@532
|
3372
|
jbe@532
|
3373 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
jbe@532
|
3374 RETURNS VOID
|
jbe@532
|
3375 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3376 BEGIN
|
jbe@532
|
3377 UPDATE "member" SET
|
jbe@532
|
3378 "last_login" = NULL,
|
jbe@532
|
3379 "last_delegation_check" = NULL,
|
jbe@532
|
3380 "login" = NULL,
|
jbe@532
|
3381 "password" = NULL,
|
jbe@532
|
3382 "authority" = NULL,
|
jbe@532
|
3383 "authority_uid" = NULL,
|
jbe@532
|
3384 "authority_login" = NULL,
|
jbe@552
|
3385 "deleted" = coalesce("deleted", now()),
|
jbe@532
|
3386 "locked" = TRUE,
|
jbe@532
|
3387 "active" = FALSE,
|
jbe@532
|
3388 "notify_email" = NULL,
|
jbe@532
|
3389 "notify_email_unconfirmed" = NULL,
|
jbe@532
|
3390 "notify_email_secret" = NULL,
|
jbe@532
|
3391 "notify_email_secret_expiry" = NULL,
|
jbe@532
|
3392 "notify_email_lock_expiry" = NULL,
|
jbe@532
|
3393 "disable_notifications" = TRUE,
|
jbe@532
|
3394 "notification_counter" = DEFAULT,
|
jbe@532
|
3395 "notification_sample_size" = 0,
|
jbe@532
|
3396 "notification_dow" = NULL,
|
jbe@532
|
3397 "notification_hour" = NULL,
|
jbe@543
|
3398 "notification_sent" = NULL,
|
jbe@532
|
3399 "login_recovery_expiry" = NULL,
|
jbe@532
|
3400 "password_reset_secret" = NULL,
|
jbe@532
|
3401 "password_reset_secret_expiry" = NULL,
|
jbe@532
|
3402 "location" = NULL
|
jbe@532
|
3403 WHERE "id" = "member_id_p";
|
jbe@532
|
3404 -- "text_search_data" is updated by triggers
|
jbe@544
|
3405 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3406 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3407 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3408 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3409 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3410 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3411 DELETE FROM "session" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3412 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3413 DELETE FROM "token" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3414 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
|
jbe@543
|
3415 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3416 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3417 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
jbe@532
|
3418 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3419 DELETE FROM "direct_voter" USING "issue"
|
jbe@532
|
3420 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@532
|
3421 AND "issue"."closed" ISNULL
|
jbe@532
|
3422 AND "member_id" = "member_id_p";
|
jbe@543
|
3423 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
|
jbe@532
|
3424 RETURN;
|
jbe@532
|
3425 END;
|
jbe@532
|
3426 $$;
|
jbe@532
|
3427
|
jbe@532
|
3428
|
jbe@532
|
3429 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@532
|
3430 RETURNS VOID
|
jbe@532
|
3431 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@532
|
3432 BEGIN
|
jbe@532
|
3433 DELETE FROM "temporary_transaction_data";
|
jbe@543
|
3434 DELETE FROM "temporary_suggestion_counts";
|
jbe@532
|
3435 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@532
|
3436 UPDATE "member" SET
|
jbe@532
|
3437 "invite_code" = NULL,
|
jbe@532
|
3438 "invite_code_expiry" = NULL,
|
jbe@532
|
3439 "admin_comment" = NULL,
|
jbe@532
|
3440 "last_login" = NULL,
|
jbe@532
|
3441 "last_delegation_check" = NULL,
|
jbe@532
|
3442 "login" = NULL,
|
jbe@532
|
3443 "password" = NULL,
|
jbe@532
|
3444 "authority" = NULL,
|
jbe@532
|
3445 "authority_uid" = NULL,
|
jbe@532
|
3446 "authority_login" = NULL,
|
jbe@532
|
3447 "lang" = NULL,
|
jbe@532
|
3448 "notify_email" = NULL,
|
jbe@532
|
3449 "notify_email_unconfirmed" = NULL,
|
jbe@532
|
3450 "notify_email_secret" = NULL,
|
jbe@532
|
3451 "notify_email_secret_expiry" = NULL,
|
jbe@532
|
3452 "notify_email_lock_expiry" = NULL,
|
jbe@532
|
3453 "disable_notifications" = TRUE,
|
jbe@532
|
3454 "notification_counter" = DEFAULT,
|
jbe@532
|
3455 "notification_sample_size" = 0,
|
jbe@532
|
3456 "notification_dow" = NULL,
|
jbe@532
|
3457 "notification_hour" = NULL,
|
jbe@543
|
3458 "notification_sent" = NULL,
|
jbe@532
|
3459 "login_recovery_expiry" = NULL,
|
jbe@532
|
3460 "password_reset_secret" = NULL,
|
jbe@532
|
3461 "password_reset_secret_expiry" = NULL,
|
jbe@532
|
3462 "location" = NULL;
|
jbe@532
|
3463 -- "text_search_data" is updated by triggers
|
jbe@557
|
3464 DELETE FROM "verification";
|
jbe@544
|
3465 DELETE FROM "member_settings";
|
jbe@544
|
3466 DELETE FROM "member_useterms";
|
jbe@543
|
3467 DELETE FROM "member_profile";
|
jbe@543
|
3468 DELETE FROM "rendered_member_statement";
|
jbe@532
|
3469 DELETE FROM "member_image";
|
jbe@532
|
3470 DELETE FROM "contact";
|
jbe@532
|
3471 DELETE FROM "ignored_member";
|
jbe@532
|
3472 DELETE FROM "session";
|
jbe@543
|
3473 DELETE FROM "system_application";
|
jbe@543
|
3474 DELETE FROM "system_application_redirect_uri";
|
jbe@543
|
3475 DELETE FROM "dynamic_application_scope";
|
jbe@543
|
3476 DELETE FROM "member_application";
|
jbe@543
|
3477 DELETE FROM "token";
|
jbe@543
|
3478 DELETE FROM "subscription";
|
jbe@543
|
3479 DELETE FROM "ignored_area";
|
jbe@532
|
3480 DELETE FROM "ignored_initiative";
|
jbe@532
|
3481 DELETE FROM "non_voter";
|
jbe@532
|
3482 DELETE FROM "direct_voter" USING "issue"
|
jbe@532
|
3483 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@532
|
3484 AND "issue"."closed" ISNULL;
|
jbe@543
|
3485 DELETE FROM "event_processed";
|
jbe@543
|
3486 DELETE FROM "notification_initiative_sent";
|
jbe@543
|
3487 DELETE FROM "newsletter";
|
jbe@532
|
3488 RETURN;
|
jbe@532
|
3489 END;
|
jbe@532
|
3490 $$;
|
jbe@532
|
3491
|
jbe@532
|
3492
|
jbe@532
|
3493 CREATE TEMPORARY TABLE "old_snapshot" AS
|
jbe@532
|
3494 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
|
jbe@532
|
3495 FROM (
|
jbe@532
|
3496 SELECT * FROM (
|
jbe@532
|
3497 SELECT
|
jbe@532
|
3498 "id" AS "issue_id",
|
jbe@532
|
3499 'end_of_admission'::"snapshot_event" AS "event",
|
jbe@532
|
3500 "accepted" AS "calculated"
|
jbe@532
|
3501 FROM "issue" WHERE "accepted" NOTNULL
|
jbe@532
|
3502 UNION ALL
|
jbe@532
|
3503 SELECT
|
jbe@532
|
3504 "id" AS "issue_id",
|
jbe@532
|
3505 'half_freeze'::"snapshot_event" AS "event",
|
jbe@532
|
3506 "half_frozen" AS "calculated"
|
jbe@532
|
3507 FROM "issue" WHERE "half_frozen" NOTNULL
|
jbe@532
|
3508 UNION ALL
|
jbe@532
|
3509 SELECT
|
jbe@532
|
3510 "id" AS "issue_id",
|
jbe@532
|
3511 'full_freeze'::"snapshot_event" AS "event",
|
jbe@532
|
3512 "fully_frozen" AS "calculated"
|
jbe@532
|
3513 FROM "issue" WHERE "fully_frozen" NOTNULL
|
jbe@532
|
3514 ) AS "unordered"
|
jbe@532
|
3515 ORDER BY "calculated", "issue_id", "event"
|
jbe@532
|
3516 ) AS "ordered";
|
jbe@532
|
3517
|
jbe@532
|
3518
|
jbe@532
|
3519 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
|
jbe@532
|
3520 SELECT
|
jbe@532
|
3521 "old_snapshot"."snapshot_id" AS "id",
|
jbe@532
|
3522 "old_snapshot"."calculated",
|
jbe@532
|
3523 ( SELECT COALESCE(sum("weight"), 0)
|
jbe@532
|
3524 FROM "direct_population_snapshot" "dps"
|
jbe@532
|
3525 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
|
jbe@532
|
3526 AND "dps"."event" = "old_snapshot"."event"
|
jbe@532
|
3527 ) AS "population",
|
jbe@532
|
3528 "issue"."area_id" AS "area_id",
|
jbe@532
|
3529 "issue"."id" AS "issue_id"
|
jbe@532
|
3530 FROM "old_snapshot" JOIN "issue"
|
jbe@532
|
3531 ON "old_snapshot"."issue_id" = "issue"."id";
|
jbe@532
|
3532
|
jbe@532
|
3533
|
jbe@532
|
3534 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
|
jbe@532
|
3535 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
|
jbe@532
|
3536
|
jbe@532
|
3537
|
jbe@532
|
3538 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
3539 SELECT
|
jbe@532
|
3540 "old_snapshot"."snapshot_id",
|
jbe@532
|
3541 "direct_population_snapshot"."member_id"
|
jbe@532
|
3542 FROM "old_snapshot" JOIN "direct_population_snapshot"
|
jbe@532
|
3543 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
|
jbe@532
|
3544 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
|
jbe@532
|
3545
|
jbe@532
|
3546 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
|
jbe@532
|
3547 SELECT
|
jbe@532
|
3548 "old_snapshot"."snapshot_id",
|
jbe@532
|
3549 "delegating_population_snapshot"."member_id"
|
jbe@532
|
3550 FROM "old_snapshot" JOIN "delegating_population_snapshot"
|
jbe@532
|
3551 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
|
jbe@532
|
3552 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
|
jbe@532
|
3553
|
jbe@532
|
3554
|
jbe@532
|
3555 INSERT INTO "direct_interest_snapshot"
|
jbe@532
|
3556 ("snapshot_id", "issue_id", "member_id", "weight")
|
jbe@532
|
3557 SELECT
|
jbe@532
|
3558 "old_snapshot"."snapshot_id",
|
jbe@532
|
3559 "old_snapshot"."issue_id",
|
jbe@532
|
3560 "direct_interest_snapshot_old"."member_id",
|
jbe@532
|
3561 "direct_interest_snapshot_old"."weight"
|
jbe@532
|
3562 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
|
jbe@532
|
3563 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
|
jbe@532
|
3564 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
|
jbe@532
|
3565
|
jbe@532
|
3566 INSERT INTO "delegating_interest_snapshot"
|
jbe@532
|
3567 ( "snapshot_id", "issue_id",
|
jbe@532
|
3568 "member_id", "weight", "scope", "delegate_member_ids" )
|
jbe@532
|
3569 SELECT
|
jbe@532
|
3570 "old_snapshot"."snapshot_id",
|
jbe@532
|
3571 "old_snapshot"."issue_id",
|
jbe@532
|
3572 "delegating_interest_snapshot_old"."member_id",
|
jbe@532
|
3573 "delegating_interest_snapshot_old"."weight",
|
jbe@532
|
3574 "delegating_interest_snapshot_old"."scope",
|
jbe@532
|
3575 "delegating_interest_snapshot_old"."delegate_member_ids"
|
jbe@532
|
3576 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
|
jbe@532
|
3577 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
|
jbe@532
|
3578 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
|
jbe@532
|
3579
|
jbe@532
|
3580 INSERT INTO "direct_supporter_snapshot"
|
jbe@532
|
3581 ( "snapshot_id", "issue_id",
|
jbe@532
|
3582 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
|
jbe@532
|
3583 SELECT
|
jbe@532
|
3584 "old_snapshot"."snapshot_id",
|
jbe@532
|
3585 "old_snapshot"."issue_id",
|
jbe@532
|
3586 "direct_supporter_snapshot_old"."initiative_id",
|
jbe@532
|
3587 "direct_supporter_snapshot_old"."member_id",
|
jbe@532
|
3588 "direct_supporter_snapshot_old"."draft_id",
|
jbe@532
|
3589 "direct_supporter_snapshot_old"."informed",
|
jbe@532
|
3590 "direct_supporter_snapshot_old"."satisfied"
|
jbe@532
|
3591 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
|
jbe@532
|
3592 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
|
jbe@532
|
3593 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
|
jbe@532
|
3594
|
jbe@532
|
3595
|
jbe@532
|
3596 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
|
jbe@532
|
3597
|
jbe@532
|
3598 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
|
jbe@532
|
3599 FROM (
|
jbe@532
|
3600 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
|
jbe@532
|
3601 FROM "snapshot" ORDER BY "issue_id", "id" DESC
|
jbe@532
|
3602 ) AS "snapshot"
|
jbe@532
|
3603 WHERE "snapshot"."issue_id" = "issue"."id";
|
jbe@532
|
3604
|
jbe@532
|
3605 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3606 FROM "old_snapshot"
|
jbe@532
|
3607 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3608 AND "old_snapshot"."event" = 'end_of_admission';
|
jbe@532
|
3609
|
jbe@532
|
3610 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3611 FROM "old_snapshot"
|
jbe@532
|
3612 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3613 AND "old_snapshot"."event" = 'half_freeze';
|
jbe@532
|
3614
|
jbe@532
|
3615 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
|
jbe@532
|
3616 FROM "old_snapshot"
|
jbe@532
|
3617 WHERE "old_snapshot"."issue_id" = "issue"."id"
|
jbe@532
|
3618 AND "old_snapshot"."event" = 'full_freeze';
|
jbe@532
|
3619
|
jbe@532
|
3620 ALTER TABLE "issue" ENABLE TRIGGER USER;
|
jbe@532
|
3621
|
jbe@532
|
3622
|
jbe@532
|
3623 DROP TABLE "old_snapshot";
|
jbe@532
|
3624
|
jbe@532
|
3625 DROP TABLE "direct_supporter_snapshot_old";
|
jbe@532
|
3626 DROP TABLE "delegating_interest_snapshot_old";
|
jbe@532
|
3627 DROP TABLE "direct_interest_snapshot_old";
|
jbe@532
|
3628 DROP TABLE "delegating_population_snapshot";
|
jbe@532
|
3629 DROP TABLE "direct_population_snapshot";
|
jbe@532
|
3630
|
jbe@532
|
3631
|
jbe@532
|
3632 DROP VIEW "open_issue";
|
jbe@532
|
3633
|
jbe@532
|
3634
|
jbe@532
|
3635 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
|
jbe@532
|
3636
|
jbe@532
|
3637
|
jbe@532
|
3638 CREATE VIEW "open_issue" AS
|
jbe@532
|
3639 SELECT * FROM "issue" WHERE "closed" ISNULL;
|
jbe@532
|
3640
|
jbe@532
|
3641 COMMENT ON VIEW "open_issue" IS 'All open issues';
|
jbe@532
|
3642
|
jbe@532
|
3643
|
jbe@532
|
3644 -- NOTE: create "issue_for_admission" view after altering table "issue"
|
jbe@532
|
3645 CREATE VIEW "issue_for_admission" AS
|
jbe@532
|
3646 SELECT DISTINCT ON ("issue"."area_id")
|
jbe@532
|
3647 "issue".*,
|
jbe@532
|
3648 max("initiative"."supporter_count") AS "max_supporter_count"
|
jbe@532
|
3649 FROM "issue"
|
jbe@532
|
3650 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@532
|
3651 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
jbe@532
|
3652 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@532
|
3653 WHERE "issue"."state" = 'admission'::"issue_state"
|
jbe@532
|
3654 AND now() >= "issue"."created" + "issue"."min_admission_time"
|
jbe@532
|
3655 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
|
jbe@532
|
3656 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
|
jbe@532
|
3657 "issue"."population" * "policy"."issue_quorum_num"
|
jbe@532
|
3658 AND "initiative"."supporter_count" >= "area"."issue_quorum"
|
jbe@532
|
3659 AND "initiative"."revoked" ISNULL
|
jbe@532
|
3660 GROUP BY "issue"."id"
|
jbe@532
|
3661 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
|
jbe@532
|
3662
|
jbe@532
|
3663 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
|
3664
|
jbe@532
|
3665
|
jbe@532
|
3666 DROP TYPE "snapshot_event";
|
jbe@532
|
3667
|
jbe@532
|
3668
|
jbe@532
|
3669 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
|
jbe@532
|
3670 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
|
jbe@532
|
3671 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
|
jbe@532
|
3672
|
jbe@532
|
3673
|
jbe@532
|
3674 COMMIT;
|