liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 546:f46ebb677898

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

Impressum / About Us