liquid_feedback_core: update/core-update.v3.2.2-v4.0.0.sql annotate

liquid_feedback_core

annotate update/core-update.v3.2.2-v4.0.0.sql @ 543:4e0ee8416bbc

Updates for "delete_member" and "delete_private_data" functions

author	jbe
date	Fri Jul 14 20:46:31 2017 +0200 (2017-07-14)
parents	f5c5d2b12726
children	ff2c21f883ce

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