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