liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 568:b1326d149247

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

Impressum / About Us