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