liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 538:1bc3dfe5823e

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

Impressum / About Us