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 @ 552:a676d305502f

Added column "member"."deleted" as TIMESTAMPTZ

author	jbe
date	Sat Sep 16 19:58:27 2017 +0200 (2017-09-16)
parents	deabd90adae8
children	85f25c413238

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