liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 617:8e9d0878b5dc

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

Impressum / About Us