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