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 @ 551:deabd90adae8

Renamed "region" (for "unit" and "area") to "location"

author	jbe
date	Sat Sep 16 15:53:31 2017 +0200 (2017-09-16)
parents	81a35235b450
children	a676d305502f

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