liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 559:71f431fb78d4

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

Impressum / About Us