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