liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 554:3e7ad7233404

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

Impressum / About Us