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