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