liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 564:dc48b79b8a7e

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

Impressum / About Us