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 @ 548:1adb0ad5900c

Added "discovery_baseurl" for system applications

author	jbe
date	Sat Aug 05 16:10:43 2017 +0200 (2017-08-05)
parents	3cde0bb68adf
children	81a35235b450

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@532	444 ALTER TABLE "unit" ADD COLUMN "region" JSONB;
jbe@532	445
jbe@532	446 CREATE INDEX "unit_region_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("region")));
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@532	449 COMMENT ON COLUMN "unit"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
jbe@532	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@532	462 ALTER TABLE "area" ADD COLUMN "region" 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@532	467 CREATE INDEX "area_region_idx" ON "area" USING gist ((GeoJSON_to_ecluster("region")));
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@532	477 COMMENT ON COLUMN "area"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
jbe@532	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@532	1442 BEGIN
jbe@532	1443 IF TG_OP = 'UPDATE' THEN
jbe@532	1444 IF
jbe@532	1445 OLD."initiative_id" = NEW."initiative_id" AND
jbe@532	1446 OLD."member_id" = NEW."member_id" AND
jbe@532	1447 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
jbe@532	1448 THEN
jbe@532	1449 RETURN NULL;
jbe@532	1450 END IF;
jbe@532	1451 END IF;
jbe@532	1452 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
jbe@532	1453 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
jbe@532	1454 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1455 WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532	1456 IF "initiative_row"."id" NOTNULL THEN
jbe@532	1457 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1458 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1459 SELECT * INTO "area_row" FROM "area"
jbe@532	1460 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1461 INSERT INTO "event" (
jbe@532	1462 "event", "member_id",
jbe@536	1463 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1464 "initiative_id", "boolean_value"
jbe@532	1465 ) VALUES (
jbe@532	1466 'initiator', OLD."member_id",
jbe@532	1467 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1468 "issue_row"."policy_id",
jbe@532	1469 "issue_row"."id", "issue_row"."state",
jbe@532	1470 OLD."initiative_id", FALSE
jbe@532	1471 );
jbe@532	1472 END IF;
jbe@532	1473 END IF;
jbe@532	1474 END IF;
jbe@532	1475 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
jbe@532	1476 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
jbe@532	1477 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1478 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	1479 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1480 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1481 SELECT * INTO "area_row" FROM "area"
jbe@532	1482 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1483 INSERT INTO "event" (
jbe@532	1484 "event", "member_id",
jbe@536	1485 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1486 "initiative_id", "boolean_value"
jbe@532	1487 ) VALUES (
jbe@532	1488 'initiator', NEW."member_id",
jbe@532	1489 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1490 "issue_row"."policy_id",
jbe@532	1491 "issue_row"."id", "issue_row"."state",
jbe@532	1492 NEW."initiative_id", TRUE
jbe@532	1493 );
jbe@532	1494 END IF;
jbe@532	1495 END IF;
jbe@532	1496 RETURN NULL;
jbe@532	1497 END;
jbe@532	1498 $$;
jbe@532	1499
jbe@532	1500 CREATE TRIGGER "write_event_initiator"
jbe@532	1501 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1502 "write_event_initiator_trigger"();
jbe@532	1503
jbe@532	1504 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
jbe@532	1505 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	1506
jbe@532	1507
jbe@532	1508 CREATE FUNCTION "write_event_support_trigger"()
jbe@532	1509 RETURNS TRIGGER
jbe@532	1510 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1511 DECLARE
jbe@532	1512 "issue_row" "issue"%ROWTYPE;
jbe@532	1513 "area_row" "area"%ROWTYPE;
jbe@532	1514 BEGIN
jbe@532	1515 IF TG_OP = 'UPDATE' THEN
jbe@532	1516 IF
jbe@532	1517 OLD."initiative_id" = NEW."initiative_id" AND
jbe@532	1518 OLD."member_id" = NEW."member_id"
jbe@532	1519 THEN
jbe@532	1520 IF OLD."draft_id" != NEW."draft_id" THEN
jbe@532	1521 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1522 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1523 SELECT * INTO "area_row" FROM "area"
jbe@532	1524 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1525 INSERT INTO "event" (
jbe@532	1526 "event", "member_id",
jbe@536	1527 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1528 "initiative_id", "draft_id"
jbe@532	1529 ) VALUES (
jbe@532	1530 'support_updated', NEW."member_id",
jbe@532	1531 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1532 "issue_row"."policy_id",
jbe@532	1533 "issue_row"."id", "issue_row"."state",
jbe@532	1534 NEW."initiative_id", NEW."draft_id"
jbe@532	1535 );
jbe@532	1536 END IF;
jbe@532	1537 RETURN NULL;
jbe@532	1538 END IF;
jbe@532	1539 END IF;
jbe@532	1540 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1541 IF EXISTS (
jbe@532	1542 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
jbe@532	1543 FOR SHARE
jbe@532	1544 ) THEN
jbe@532	1545 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1546 WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532	1547 SELECT * INTO "area_row" FROM "area"
jbe@532	1548 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1549 INSERT INTO "event" (
jbe@532	1550 "event", "member_id",
jbe@536	1551 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@535	1552 "initiative_id", "boolean_value"
jbe@532	1553 ) VALUES (
jbe@532	1554 'support', OLD."member_id",
jbe@532	1555 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1556 "issue_row"."policy_id",
jbe@532	1557 "issue_row"."id", "issue_row"."state",
jbe@535	1558 OLD."initiative_id", FALSE
jbe@532	1559 );
jbe@532	1560 END IF;
jbe@532	1561 END IF;
jbe@532	1562 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1563 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1564 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1565 SELECT * INTO "area_row" FROM "area"
jbe@532	1566 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1567 INSERT INTO "event" (
jbe@532	1568 "event", "member_id",
jbe@536	1569 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1570 "initiative_id", "draft_id", "boolean_value"
jbe@532	1571 ) VALUES (
jbe@532	1572 'support', NEW."member_id",
jbe@532	1573 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1574 "issue_row"."policy_id",
jbe@532	1575 "issue_row"."id", "issue_row"."state",
jbe@532	1576 NEW."initiative_id", NEW."draft_id", TRUE
jbe@532	1577 );
jbe@532	1578 END IF;
jbe@532	1579 RETURN NULL;
jbe@532	1580 END;
jbe@532	1581 $$;
jbe@532	1582
jbe@532	1583 CREATE TRIGGER "write_event_support"
jbe@532	1584 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1585 "write_event_support_trigger"();
jbe@532	1586
jbe@532	1587 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
jbe@532	1588 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532	1589
jbe@532	1590
jbe@532	1591 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
jbe@532	1592 RETURNS TRIGGER
jbe@532	1593 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1594 DECLARE
jbe@532	1595 "same_pkey_v" BOOLEAN = FALSE;
jbe@532	1596 "initiative_row" "initiative"%ROWTYPE;
jbe@532	1597 "issue_row" "issue"%ROWTYPE;
jbe@532	1598 "area_row" "area"%ROWTYPE;
jbe@532	1599 BEGIN
jbe@532	1600 IF TG_OP = 'UPDATE' THEN
jbe@532	1601 IF
jbe@532	1602 OLD."suggestion_id" = NEW."suggestion_id" AND
jbe@532	1603 OLD."member_id" = NEW."member_id"
jbe@532	1604 THEN
jbe@532	1605 IF
jbe@532	1606 OLD."degree" = NEW."degree" AND
jbe@532	1607 OLD."fulfilled" = NEW."fulfilled"
jbe@532	1608 THEN
jbe@532	1609 RETURN NULL;
jbe@532	1610 END IF;
jbe@532	1611 "same_pkey_v" := TRUE;
jbe@532	1612 END IF;
jbe@532	1613 END IF;
jbe@532	1614 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
jbe@532	1615 IF EXISTS (
jbe@532	1616 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
jbe@532	1617 FOR SHARE
jbe@532	1618 ) THEN
jbe@532	1619 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1620 WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532	1621 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1622 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1623 SELECT * INTO "area_row" FROM "area"
jbe@532	1624 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1625 INSERT INTO "event" (
jbe@532	1626 "event", "member_id",
jbe@536	1627 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1628 "initiative_id", "suggestion_id",
jbe@532	1629 "boolean_value", "numeric_value"
jbe@532	1630 ) VALUES (
jbe@532	1631 'suggestion_rated', OLD."member_id",
jbe@532	1632 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1633 "issue_row"."policy_id",
jbe@532	1634 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1635 OLD."initiative_id", OLD."suggestion_id",
jbe@532	1636 NULL, 0
jbe@532	1637 );
jbe@532	1638 END IF;
jbe@532	1639 END IF;
jbe@532	1640 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1641 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1642 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	1643 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1644 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1645 SELECT * INTO "area_row" FROM "area"
jbe@532	1646 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1647 INSERT INTO "event" (
jbe@532	1648 "event", "member_id",
jbe@536	1649 "unit_id", "area_id", "policy_id", "issue_id", "state",
jbe@532	1650 "initiative_id", "suggestion_id",
jbe@532	1651 "boolean_value", "numeric_value"
jbe@532	1652 ) VALUES (
jbe@532	1653 'suggestion_rated', NEW."member_id",
jbe@532	1654 "area_row"."unit_id", "issue_row"."area_id",
jbe@536	1655 "issue_row"."policy_id",
jbe@532	1656 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1657 NEW."initiative_id", NEW."suggestion_id",
jbe@532	1658 NEW."fulfilled", NEW."degree"
jbe@532	1659 );
jbe@532	1660 END IF;
jbe@532	1661 RETURN NULL;
jbe@532	1662 END;
jbe@532	1663 $$;
jbe@532	1664
jbe@532	1665 CREATE TRIGGER "write_event_suggestion_rated"
jbe@532	1666 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1667 "write_event_suggestion_rated_trigger"();
jbe@532	1668
jbe@532	1669 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
jbe@532	1670 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532	1671
jbe@532	1672
jbe@532	1673 CREATE FUNCTION "write_event_delegation_trigger"()
jbe@532	1674 RETURNS TRIGGER
jbe@532	1675 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1676 DECLARE
jbe@532	1677 "issue_row" "issue"%ROWTYPE;
jbe@532	1678 "area_row" "area"%ROWTYPE;
jbe@532	1679 BEGIN
jbe@532	1680 IF TG_OP = 'DELETE' THEN
jbe@532	1681 IF EXISTS (
jbe@532	1682 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
jbe@532	1683 ) AND (CASE OLD."scope"
jbe@532	1684 WHEN 'unit'::"delegation_scope" THEN EXISTS (
jbe@532	1685 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
jbe@532	1686 )
jbe@532	1687 WHEN 'area'::"delegation_scope" THEN EXISTS (
jbe@532	1688 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
jbe@532	1689 )
jbe@532	1690 WHEN 'issue'::"delegation_scope" THEN EXISTS (
jbe@532	1691 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
jbe@532	1692 )
jbe@532	1693 END) THEN
jbe@532	1694 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1695 WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532	1696 SELECT * INTO "area_row" FROM "area"
jbe@532	1697 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
jbe@532	1698 FOR SHARE;
jbe@532	1699 INSERT INTO "event" (
jbe@532	1700 "event", "member_id", "scope",
jbe@532	1701 "unit_id", "area_id", "issue_id", "state",
jbe@532	1702 "boolean_value"
jbe@532	1703 ) VALUES (
jbe@532	1704 'delegation', OLD."truster_id", OLD."scope",
jbe@532	1705 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532	1706 OLD."issue_id", "issue_row"."state",
jbe@532	1707 FALSE
jbe@532	1708 );
jbe@532	1709 END IF;
jbe@532	1710 ELSE
jbe@532	1711 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1712 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1713 SELECT * INTO "area_row" FROM "area"
jbe@532	1714 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
jbe@532	1715 FOR SHARE;
jbe@532	1716 INSERT INTO "event" (
jbe@532	1717 "event", "member_id", "other_member_id", "scope",
jbe@532	1718 "unit_id", "area_id", "issue_id", "state",
jbe@532	1719 "boolean_value"
jbe@532	1720 ) VALUES (
jbe@532	1721 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
jbe@532	1722 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532	1723 NEW."issue_id", "issue_row"."state",
jbe@532	1724 TRUE
jbe@532	1725 );
jbe@532	1726 END IF;
jbe@532	1727 RETURN NULL;
jbe@532	1728 END;
jbe@532	1729 $$;
jbe@532	1730
jbe@532	1731 CREATE TRIGGER "write_event_delegation"
jbe@532	1732 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1733 "write_event_delegation_trigger"();
jbe@532	1734
jbe@532	1735 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
jbe@532	1736 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
jbe@532	1737
jbe@532	1738
jbe@532	1739 CREATE FUNCTION "write_event_contact_trigger"()
jbe@532	1740 RETURNS TRIGGER
jbe@532	1741 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1742 BEGIN
jbe@532	1743 IF TG_OP = 'UPDATE' THEN
jbe@532	1744 IF
jbe@532	1745 OLD."member_id" = NEW."member_id" AND
jbe@532	1746 OLD."other_member_id" = NEW."other_member_id" AND
jbe@532	1747 OLD."public" = NEW."public"
jbe@532	1748 THEN
jbe@532	1749 RETURN NULL;
jbe@532	1750 END IF;
jbe@532	1751 END IF;
jbe@532	1752 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1753 IF OLD."public" THEN
jbe@532	1754 IF EXISTS (
jbe@532	1755 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
jbe@532	1756 FOR SHARE
jbe@532	1757 ) AND EXISTS (
jbe@532	1758 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
jbe@532	1759 FOR SHARE
jbe@532	1760 ) THEN
jbe@532	1761 INSERT INTO "event" (
jbe@532	1762 "event", "member_id", "other_member_id", "boolean_value"
jbe@532	1763 ) VALUES (
jbe@532	1764 'contact', OLD."member_id", OLD."other_member_id", FALSE
jbe@532	1765 );
jbe@532	1766 END IF;
jbe@532	1767 END IF;
jbe@532	1768 END IF;
jbe@532	1769 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1770 IF NEW."public" 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', NEW."member_id", NEW."other_member_id", TRUE
jbe@532	1775 );
jbe@532	1776 END IF;
jbe@532	1777 END IF;
jbe@532	1778 RETURN NULL;
jbe@532	1779 END;
jbe@532	1780 $$;
jbe@532	1781
jbe@532	1782 CREATE TRIGGER "write_event_contact"
jbe@532	1783 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1784 "write_event_contact_trigger"();
jbe@532	1785
jbe@532	1786 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
jbe@532	1787 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
jbe@532	1788
jbe@532	1789
jbe@532	1790 CREATE FUNCTION "send_event_notify_trigger"()
jbe@532	1791 RETURNS TRIGGER
jbe@532	1792 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1793 BEGIN
jbe@532	1794 EXECUTE 'NOTIFY "event", ''' \|\| NEW."event" \|\| '''';
jbe@532	1795 RETURN NULL;
jbe@532	1796 END;
jbe@532	1797 $$;
jbe@532	1798
jbe@532	1799 CREATE TRIGGER "send_notify"
jbe@532	1800 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1801 "send_event_notify_trigger"();
jbe@532	1802
jbe@532	1803
jbe@532	1804 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
jbe@532	1805 RETURNS TRIGGER
jbe@532	1806 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1807 DECLARE
jbe@532	1808 "system_application_row" "system_application"%ROWTYPE;
jbe@532	1809 BEGIN
jbe@532	1810 IF OLD."system_application_id" NOTNULL THEN
jbe@532	1811 SELECT * FROM "system_application" INTO "system_application_row"
jbe@532	1812 WHERE "id" = OLD."system_application_id";
jbe@532	1813 DELETE FROM "token"
jbe@532	1814 WHERE "member_id" = OLD."member_id"
jbe@532	1815 AND "system_application_id" = OLD."system_application_id"
jbe@532	1816 AND NOT COALESCE(
jbe@532	1817 regexp_split_to_array("scope", E'\\s+') <@
jbe@532	1818 regexp_split_to_array(
jbe@532	1819 "system_application_row"."automatic_scope", E'\\s+'
jbe@532	1820 ),
jbe@532	1821 FALSE
jbe@532	1822 );
jbe@532	1823 END IF;
jbe@532	1824 RETURN OLD;
jbe@532	1825 END;
jbe@532	1826 $$;
jbe@532	1827
jbe@532	1828 CREATE TRIGGER "delete_extended_scope_tokens"
jbe@532	1829 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1830 "delete_extended_scope_tokens_trigger"();
jbe@532	1831
jbe@532	1832
jbe@532	1833 CREATE FUNCTION "detach_token_from_session_trigger"()
jbe@532	1834 RETURNS TRIGGER
jbe@532	1835 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1836 BEGIN
jbe@532	1837 UPDATE "token" SET "session_id" = NULL
jbe@532	1838 WHERE "session_id" = OLD."id";
jbe@532	1839 RETURN OLD;
jbe@532	1840 END;
jbe@532	1841 $$;
jbe@532	1842
jbe@532	1843 CREATE TRIGGER "detach_token_from_session"
jbe@532	1844 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1845 "detach_token_from_session_trigger"();
jbe@532	1846
jbe@532	1847
jbe@532	1848 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
jbe@532	1849 RETURNS TRIGGER
jbe@532	1850 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1851 BEGIN
jbe@532	1852 IF NEW."session_id" ISNULL THEN
jbe@532	1853 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
jbe@532	1854 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
jbe@532	1855 WHERE "element" LIKE '%_detached';
jbe@532	1856 END IF;
jbe@532	1857 RETURN NEW;
jbe@532	1858 END;
jbe@532	1859 $$;
jbe@532	1860
jbe@532	1861 CREATE TRIGGER "delete_non_detached_scope_with_session"
jbe@532	1862 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1863 "delete_non_detached_scope_with_session_trigger"();
jbe@532	1864
jbe@532	1865
jbe@532	1866 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
jbe@532	1867 RETURNS TRIGGER
jbe@532	1868 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1869 BEGIN
jbe@532	1870 IF NEW."scope" = '' THEN
jbe@532	1871 DELETE FROM "token" WHERE "id" = NEW."id";
jbe@532	1872 END IF;
jbe@532	1873 RETURN NULL;
jbe@532	1874 END;
jbe@532	1875 $$;
jbe@532	1876
jbe@532	1877 CREATE TRIGGER "delete_token_with_empty_scope"
jbe@532	1878 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1879 "delete_token_with_empty_scope_trigger"();
jbe@532	1880
jbe@532	1881
jbe@532	1882 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
jbe@532	1883 RETURNS TRIGGER
jbe@532	1884 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1885 BEGIN
jbe@532	1886 IF TG_OP = 'UPDATE' THEN
jbe@532	1887 IF
jbe@532	1888 OLD."snapshot_id" = NEW."snapshot_id" AND
jbe@532	1889 OLD."issue_id" = NEW."issue_id"
jbe@532	1890 THEN
jbe@532	1891 RETURN NULL;
jbe@532	1892 END IF;
jbe@532	1893 END IF;
jbe@532	1894 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
jbe@532	1895 RETURN NULL;
jbe@532	1896 END;
jbe@532	1897 $$;
jbe@532	1898
jbe@532	1899 CREATE TRIGGER "delete_snapshot_on_partial_delete"
jbe@532	1900 AFTER UPDATE OR DELETE ON "snapshot_issue"
jbe@532	1901 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1902 "delete_snapshot_on_partial_delete_trigger"();
jbe@532	1903
jbe@532	1904 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
jbe@532	1905 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	1906
jbe@532	1907
jbe@532	1908 CREATE FUNCTION "copy_current_draft_data"
jbe@532	1909 ("initiative_id_p" "initiative"."id"%TYPE )
jbe@532	1910 RETURNS VOID
jbe@532	1911 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1912 BEGIN
jbe@532	1913 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
jbe@532	1914 FOR UPDATE;
jbe@532	1915 UPDATE "initiative" SET
jbe@532	1916 "location" = "draft"."location",
jbe@532	1917 "draft_text_search_data" = "draft"."text_search_data"
jbe@532	1918 FROM "current_draft" AS "draft"
jbe@532	1919 WHERE "initiative"."id" = "initiative_id_p"
jbe@532	1920 AND "draft"."initiative_id" = "initiative_id_p";
jbe@532	1921 END;
jbe@532	1922 $$;
jbe@532	1923
jbe@532	1924 COMMENT ON FUNCTION "copy_current_draft_data"
jbe@532	1925 ( "initiative"."id"%TYPE )
jbe@532	1926 IS 'Helper function for function "copy_current_draft_data_trigger"';
jbe@532	1927
jbe@532	1928
jbe@532	1929 CREATE FUNCTION "copy_current_draft_data_trigger"()
jbe@532	1930 RETURNS TRIGGER
jbe@532	1931 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1932 BEGIN
jbe@532	1933 IF TG_OP='DELETE' THEN
jbe@532	1934 PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532	1935 ELSE
jbe@532	1936 IF TG_OP='UPDATE' THEN
jbe@532	1937 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
jbe@532	1938 PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532	1939 END IF;
jbe@532	1940 END IF;
jbe@532	1941 PERFORM "copy_current_draft_data"(NEW."initiative_id");
jbe@532	1942 END IF;
jbe@532	1943 RETURN NULL;
jbe@532	1944 END;
jbe@532	1945 $$;
jbe@532	1946
jbe@532	1947 CREATE TRIGGER "copy_current_draft_data"
jbe@532	1948 AFTER INSERT OR UPDATE OR DELETE ON "draft"
jbe@532	1949 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1950 "copy_current_draft_data_trigger"();
jbe@532	1951
jbe@532	1952 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
jbe@532	1953 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
jbe@532	1954
jbe@532	1955
jbe@532	1956 CREATE VIEW "area_quorum" AS
jbe@532	1957 SELECT
jbe@532	1958 "area"."id" AS "area_id",
jbe@532	1959 ceil(
jbe@532	1960 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
jbe@532	1961 coalesce(
jbe@532	1962 ( SELECT sum(
jbe@532	1963 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
jbe@532	1964 extract(epoch from
jbe@532	1965 ("issue"."accepted"-"issue"."created") +
jbe@532	1966 "issue"."discussion_time" +
jbe@532	1967 "issue"."verification_time" +
jbe@532	1968 "issue"."voting_time"
jbe@532	1969 )::FLOAT8
jbe@532	1970 ) ^ "area"."quorum_exponent"::FLOAT8
jbe@532	1971 )
jbe@532	1972 FROM "issue" JOIN "policy"
jbe@532	1973 ON "issue"."policy_id" = "policy"."id"
jbe@532	1974 WHERE "issue"."area_id" = "area"."id"
jbe@532	1975 AND "issue"."accepted" NOTNULL
jbe@532	1976 AND "issue"."closed" ISNULL
jbe@532	1977 AND "policy"."polling" = FALSE
jbe@532	1978 )::FLOAT8, 0::FLOAT8
jbe@532	1979 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
jbe@532	1980 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
jbe@532	1981 SELECT "snapshot"."population"
jbe@532	1982 FROM "snapshot"
jbe@532	1983 WHERE "snapshot"."area_id" = "area"."id"
jbe@532	1984 AND "snapshot"."issue_id" ISNULL
jbe@532	1985 ORDER BY "snapshot"."id" DESC
jbe@532	1986 LIMIT 1
jbe@532	1987 ) END / coalesce("area"."quorum_den", 1)
jbe@532	1988
jbe@532	1989 )::INT4 AS "issue_quorum"
jbe@532	1990 FROM "area";
jbe@532	1991
jbe@532	1992 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
jbe@532	1993
jbe@532	1994
jbe@532	1995 CREATE VIEW "area_with_unaccepted_issues" AS
jbe@532	1996 SELECT DISTINCT ON ("area"."id") "area".*
jbe@532	1997 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
jbe@532	1998 WHERE "issue"."state" = 'admission';
jbe@532	1999
jbe@532	2000 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
jbe@532	2001
jbe@532	2002
jbe@532	2003 DROP VIEW "area_member_count";
jbe@532	2004
jbe@532	2005
jbe@532	2006 DROP TABLE "membership";
jbe@532	2007
jbe@532	2008
jbe@532	2009 DROP FUNCTION "membership_weight"
jbe@532	2010 ( "area_id_p" "area"."id"%TYPE,
jbe@532	2011 "member_id_p" "member"."id"%TYPE );
jbe@532	2012
jbe@532	2013
jbe@532	2014 DROP FUNCTION "membership_weight_with_skipping"
jbe@532	2015 ( "area_id_p" "area"."id"%TYPE,
jbe@532	2016 "member_id_p" "member"."id"%TYPE,
jbe@532	2017 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
jbe@532	2018
jbe@532	2019
jbe@532	2020 CREATE OR REPLACE VIEW "issue_delegation" AS
jbe@532	2021 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@532	2022 "issue"."id" AS "issue_id",
jbe@532	2023 "delegation"."id",
jbe@532	2024 "delegation"."truster_id",
jbe@532	2025 "delegation"."trustee_id",
jbe@532	2026 "delegation"."scope"
jbe@532	2027 FROM "issue"
jbe@532	2028 JOIN "area"
jbe@532	2029 ON "area"."id" = "issue"."area_id"
jbe@532	2030 JOIN "delegation"
jbe@532	2031 ON "delegation"."unit_id" = "area"."unit_id"
jbe@532	2032 OR "delegation"."area_id" = "area"."id"
jbe@532	2033 OR "delegation"."issue_id" = "issue"."id"
jbe@532	2034 JOIN "member"
jbe@532	2035 ON "delegation"."truster_id" = "member"."id"
jbe@532	2036 JOIN "privilege"
jbe@532	2037 ON "area"."unit_id" = "privilege"."unit_id"
jbe@532	2038 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@532	2039 WHERE "member"."active" AND "privilege"."voting_right"
jbe@532	2040 ORDER BY
jbe@532	2041 "issue"."id",
jbe@532	2042 "delegation"."truster_id",
jbe@532	2043 "delegation"."scope" DESC;
jbe@532	2044
jbe@532	2045
jbe@532	2046 CREATE VIEW "unit_member" AS
jbe@532	2047 SELECT
jbe@532	2048 "unit"."id" AS "unit_id",
jbe@532	2049 "member"."id" AS "member_id"
jbe@532	2050 FROM "privilege"
jbe@532	2051 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
jbe@532	2052 JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@532	2053 WHERE "privilege"."voting_right" AND "member"."active";
jbe@532	2054
jbe@532	2055 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
jbe@532	2056
jbe@532	2057
jbe@532	2058 CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@532	2059 SELECT
jbe@532	2060 "unit"."id" AS "unit_id",
jbe@532	2061 count("unit_member"."member_id") AS "member_count"
jbe@532	2062 FROM "unit" LEFT JOIN "unit_member"
jbe@532	2063 ON "unit"."id" = "unit_member"."unit_id"
jbe@532	2064 GROUP BY "unit"."id";
jbe@532	2065
jbe@532	2066 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@532	2067
jbe@532	2068
jbe@532	2069 CREATE OR REPLACE VIEW "opening_draft" AS
jbe@532	2070 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532	2071 ORDER BY "initiative_id", "id";
jbe@532	2072
jbe@532	2073
jbe@532	2074 CREATE OR REPLACE VIEW "current_draft" AS
jbe@532	2075 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532	2076 ORDER BY "initiative_id", "id" DESC;
jbe@532	2077
jbe@532	2078
jbe@532	2079 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
jbe@532	2080 SELECT
jbe@532	2081 "area"."unit_id",
jbe@532	2082 "issue"."area_id",
jbe@532	2083 "issue"."id" AS "issue_id",
jbe@532	2084 "supporter"."member_id",
jbe@532	2085 "direct_interest_snapshot"."weight"
jbe@532	2086 FROM "issue"
jbe@532	2087 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@532	2088 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@532	2089 JOIN "direct_interest_snapshot"
jbe@532	2090 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532	2091 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532	2092 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@532	2093 WHERE "issue"."state" = 'admission'::"issue_state";
jbe@532	2094
jbe@532	2095
jbe@532	2096 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
jbe@532	2097 SELECT
jbe@532	2098 "opinion"."initiative_id",
jbe@532	2099 "opinion"."member_id",
jbe@532	2100 "direct_interest_snapshot"."weight",
jbe@532	2101 CASE WHEN
jbe@532	2102 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
jbe@532	2103 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
jbe@532	2104 THEN 1 ELSE
jbe@532	2105 CASE WHEN
jbe@532	2106 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
jbe@532	2107 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
jbe@532	2108 THEN 2 ELSE
jbe@532	2109 CASE WHEN
jbe@532	2110 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
jbe@532	2111 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
jbe@532	2112 THEN 3 ELSE 4 END
jbe@532	2113 END
jbe@532	2114 END AS "preference",
jbe@532	2115 "opinion"."suggestion_id"
jbe@532	2116 FROM "opinion"
jbe@532	2117 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
jbe@532	2118 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@532	2119 JOIN "direct_interest_snapshot"
jbe@532	2120 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532	2121 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532	2122 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
jbe@532	2123
jbe@532	2124
jbe@532	2125 CREATE VIEW "expired_session" AS
jbe@532	2126 SELECT * FROM "session" WHERE now() > "expiry";
jbe@532	2127
jbe@532	2128 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@532	2129 DELETE FROM "session" WHERE "id" = OLD."id";
jbe@532	2130
jbe@532	2131 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@532	2132 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	2133
jbe@532	2134
jbe@532	2135 CREATE VIEW "expired_token" AS
jbe@532	2136 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
jbe@532	2137 "token_type" = 'authorization' AND "used" AND EXISTS (
jbe@532	2138 SELECT NULL FROM "token" AS "other"
jbe@532	2139 WHERE "other"."authorization_token_id" = "id" ) );
jbe@532	2140
jbe@532	2141 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
jbe@532	2142 DELETE FROM "token" WHERE "id" = OLD."id";
jbe@532	2143
jbe@532	2144 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	2145
jbe@532	2146
jbe@532	2147 CREATE VIEW "unused_snapshot" AS
jbe@532	2148 SELECT "snapshot".* FROM "snapshot"
jbe@532	2149 LEFT JOIN "issue"
jbe@532	2150 ON "snapshot"."id" = "issue"."latest_snapshot_id"
jbe@532	2151 OR "snapshot"."id" = "issue"."admission_snapshot_id"
jbe@532	2152 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
jbe@532	2153 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
jbe@532	2154 WHERE "issue"."id" ISNULL;
jbe@532	2155
jbe@532	2156 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
jbe@532	2157 DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532	2158
jbe@532	2159 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	2160
jbe@532	2161
jbe@532	2162 CREATE VIEW "expired_snapshot" AS
jbe@532	2163 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
jbe@532	2164 WHERE "unused_snapshot"."calculated" <
jbe@532	2165 now() - "system_setting"."snapshot_retention";
jbe@532	2166
jbe@532	2167 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
jbe@532	2168 DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532	2169
jbe@532	2170 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
jbe@532	2171
jbe@532	2172
jbe@532	2173 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	2174
jbe@532	2175
jbe@532	2176 CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@532	2177 ( "member_id_p" "member"."id"%TYPE,
jbe@532	2178 "unit_id_p" "unit"."id"%TYPE,
jbe@532	2179 "area_id_p" "area"."id"%TYPE,
jbe@532	2180 "issue_id_p" "issue"."id"%TYPE,
jbe@532	2181 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@532	2182 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@532	2183 RETURNS SETOF "delegation_chain_row"
jbe@532	2184 LANGUAGE 'plpgsql' STABLE AS $$
jbe@532	2185 DECLARE
jbe@532	2186 "scope_v" "delegation_scope";
jbe@532	2187 "unit_id_v" "unit"."id"%TYPE;
jbe@532	2188 "area_id_v" "area"."id"%TYPE;
jbe@532	2189 "issue_row" "issue"%ROWTYPE;
jbe@532	2190 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@532	2191 "loop_member_id_v" "member"."id"%TYPE;
jbe@532	2192 "output_row" "delegation_chain_row";
jbe@532	2193 "output_rows" "delegation_chain_row"[];
jbe@532	2194 "simulate_v" BOOLEAN;
jbe@532	2195 "simulate_here_v" BOOLEAN;
jbe@532	2196 "delegation_row" "delegation"%ROWTYPE;
jbe@532	2197 "row_count" INT4;
jbe@532	2198 "i" INT4;
jbe@532	2199 "loop_v" BOOLEAN;
jbe@532	2200 BEGIN
jbe@532	2201 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@532	2202 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@532	2203 END IF;
jbe@532	2204 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@532	2205 "simulate_v" := TRUE;
jbe@532	2206 ELSE
jbe@532	2207 "simulate_v" := FALSE;
jbe@532	2208 END IF;
jbe@532	2209 IF
jbe@532	2210 "unit_id_p" NOTNULL AND
jbe@532	2211 "area_id_p" ISNULL AND
jbe@532	2212 "issue_id_p" ISNULL
jbe@532	2213 THEN
jbe@532	2214 "scope_v" := 'unit';
jbe@532	2215 "unit_id_v" := "unit_id_p";
jbe@532	2216 ELSIF
jbe@532	2217 "unit_id_p" ISNULL AND
jbe@532	2218 "area_id_p" NOTNULL AND
jbe@532	2219 "issue_id_p" ISNULL
jbe@532	2220 THEN
jbe@532	2221 "scope_v" := 'area';
jbe@532	2222 "area_id_v" := "area_id_p";
jbe@532	2223 SELECT "unit_id" INTO "unit_id_v"
jbe@532	2224 FROM "area" WHERE "id" = "area_id_v";
jbe@532	2225 ELSIF
jbe@532	2226 "unit_id_p" ISNULL AND
jbe@532	2227 "area_id_p" ISNULL AND
jbe@532	2228 "issue_id_p" NOTNULL
jbe@532	2229 THEN
jbe@532	2230 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2231 IF "issue_row"."id" ISNULL THEN
jbe@532	2232 RETURN;
jbe@532	2233 END IF;
jbe@532	2234 IF "issue_row"."closed" NOTNULL THEN
jbe@532	2235 IF "simulate_v" THEN
jbe@532	2236 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@532	2237 END IF;
jbe@532	2238 FOR "output_row" IN
jbe@532	2239 SELECT * FROM
jbe@532	2240 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@532	2241 LOOP
jbe@532	2242 RETURN NEXT "output_row";
jbe@532	2243 END LOOP;
jbe@532	2244 RETURN;
jbe@532	2245 END IF;
jbe@532	2246 "scope_v" := 'issue';
jbe@532	2247 SELECT "area_id" INTO "area_id_v"
jbe@532	2248 FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2249 SELECT "unit_id" INTO "unit_id_v"
jbe@532	2250 FROM "area" WHERE "id" = "area_id_v";
jbe@532	2251 ELSE
jbe@532	2252 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@532	2253 END IF;
jbe@532	2254 "visited_member_ids" := '{}';
jbe@532	2255 "loop_member_id_v" := NULL;
jbe@532	2256 "output_rows" := '{}';
jbe@532	2257 "output_row"."index" := 0;
jbe@532	2258 "output_row"."member_id" := "member_id_p";
jbe@532	2259 "output_row"."member_valid" := TRUE;
jbe@532	2260 "output_row"."participation" := FALSE;
jbe@532	2261 "output_row"."overridden" := FALSE;
jbe@532	2262 "output_row"."disabled_out" := FALSE;
jbe@532	2263 "output_row"."scope_out" := NULL;
jbe@532	2264 LOOP
jbe@532	2265 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@532	2266 "loop_member_id_v" := "output_row"."member_id";
jbe@532	2267 ELSE
jbe@532	2268 "visited_member_ids" :=
jbe@532	2269 "visited_member_ids" \|\| "output_row"."member_id";
jbe@532	2270 END IF;
jbe@532	2271 IF "output_row"."participation" ISNULL THEN
jbe@532	2272 "output_row"."overridden" := NULL;
jbe@532	2273 ELSIF "output_row"."participation" THEN
jbe@532	2274 "output_row"."overridden" := TRUE;
jbe@532	2275 END IF;
jbe@532	2276 "output_row"."scope_in" := "output_row"."scope_out";
jbe@532	2277 "output_row"."member_valid" := EXISTS (
jbe@532	2278 SELECT NULL FROM "member" JOIN "privilege"
jbe@532	2279 ON "privilege"."member_id" = "member"."id"
jbe@532	2280 AND "privilege"."unit_id" = "unit_id_v"
jbe@532	2281 WHERE "id" = "output_row"."member_id"
jbe@532	2282 AND "member"."active" AND "privilege"."voting_right"
jbe@532	2283 );
jbe@532	2284 "simulate_here_v" := (
jbe@532	2285 "simulate_v" AND
jbe@532	2286 "output_row"."member_id" = "member_id_p"
jbe@532	2287 );
jbe@532	2288 "delegation_row" := ROW(NULL);
jbe@532	2289 IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@532	2290 IF "scope_v" = 'unit' THEN
jbe@532	2291 IF NOT "simulate_here_v" THEN
jbe@532	2292 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2293 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2294 AND "unit_id" = "unit_id_v";
jbe@532	2295 END IF;
jbe@532	2296 ELSIF "scope_v" = 'area' THEN
jbe@532	2297 IF "simulate_here_v" THEN
jbe@532	2298 IF "simulate_trustee_id_p" ISNULL THEN
jbe@532	2299 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2300 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2301 AND "unit_id" = "unit_id_v";
jbe@532	2302 END IF;
jbe@532	2303 ELSE
jbe@532	2304 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2305 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2306 AND (
jbe@532	2307 "unit_id" = "unit_id_v" OR
jbe@532	2308 "area_id" = "area_id_v"
jbe@532	2309 )
jbe@532	2310 ORDER BY "scope" DESC;
jbe@532	2311 END IF;
jbe@532	2312 ELSIF "scope_v" = 'issue' THEN
jbe@532	2313 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@532	2314 "output_row"."participation" := EXISTS (
jbe@532	2315 SELECT NULL FROM "interest"
jbe@532	2316 WHERE "issue_id" = "issue_id_p"
jbe@532	2317 AND "member_id" = "output_row"."member_id"
jbe@532	2318 );
jbe@532	2319 ELSE
jbe@532	2320 IF "output_row"."member_id" = "member_id_p" THEN
jbe@532	2321 "output_row"."participation" := EXISTS (
jbe@532	2322 SELECT NULL FROM "direct_voter"
jbe@532	2323 WHERE "issue_id" = "issue_id_p"
jbe@532	2324 AND "member_id" = "output_row"."member_id"
jbe@532	2325 );
jbe@532	2326 ELSE
jbe@532	2327 "output_row"."participation" := NULL;
jbe@532	2328 END IF;
jbe@532	2329 END IF;
jbe@532	2330 IF "simulate_here_v" THEN
jbe@532	2331 IF "simulate_trustee_id_p" ISNULL THEN
jbe@532	2332 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2333 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2334 AND (
jbe@532	2335 "unit_id" = "unit_id_v" OR
jbe@532	2336 "area_id" = "area_id_v"
jbe@532	2337 )
jbe@532	2338 ORDER BY "scope" DESC;
jbe@532	2339 END IF;
jbe@532	2340 ELSE
jbe@532	2341 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2342 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2343 AND (
jbe@532	2344 "unit_id" = "unit_id_v" OR
jbe@532	2345 "area_id" = "area_id_v" OR
jbe@532	2346 "issue_id" = "issue_id_p"
jbe@532	2347 )
jbe@532	2348 ORDER BY "scope" DESC;
jbe@532	2349 END IF;
jbe@532	2350 END IF;
jbe@532	2351 ELSE
jbe@532	2352 "output_row"."participation" := FALSE;
jbe@532	2353 END IF;
jbe@532	2354 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@532	2355 "output_row"."scope_out" := "scope_v";
jbe@532	2356 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2357 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@532	2358 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@532	2359 "output_row"."scope_out" := "delegation_row"."scope";
jbe@532	2360 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2361 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@532	2362 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@532	2363 "output_row"."scope_out" := "delegation_row"."scope";
jbe@532	2364 "output_row"."disabled_out" := TRUE;
jbe@532	2365 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2366 EXIT;
jbe@532	2367 ELSE
jbe@532	2368 "output_row"."scope_out" := NULL;
jbe@532	2369 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2370 EXIT;
jbe@532	2371 END IF;
jbe@532	2372 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@532	2373 "output_row"."index" := "output_row"."index" + 1;
jbe@532	2374 END LOOP;
jbe@532	2375 "row_count" := array_upper("output_rows", 1);
jbe@532	2376 "i" := 1;
jbe@532	2377 "loop_v" := FALSE;
jbe@532	2378 LOOP
jbe@532	2379 "output_row" := "output_rows"["i"];
jbe@532	2380 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@532	2381 IF "loop_v" THEN
jbe@532	2382 IF "i" + 1 = "row_count" THEN
jbe@532	2383 "output_row"."loop" := 'last';
jbe@532	2384 ELSIF "i" = "row_count" THEN
jbe@532	2385 "output_row"."loop" := 'repetition';
jbe@532	2386 ELSE
jbe@532	2387 "output_row"."loop" := 'intermediate';
jbe@532	2388 END IF;
jbe@532	2389 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@532	2390 "output_row"."loop" := 'first';
jbe@532	2391 "loop_v" := TRUE;
jbe@532	2392 END IF;
jbe@532	2393 IF "scope_v" = 'unit' THEN
jbe@532	2394 "output_row"."participation" := NULL;
jbe@532	2395 END IF;
jbe@532	2396 RETURN NEXT "output_row";
jbe@532	2397 "i" := "i" + 1;
jbe@532	2398 END LOOP;
jbe@532	2399 RETURN;
jbe@532	2400 END;
jbe@532	2401 $$;
jbe@532	2402
jbe@532	2403
jbe@532	2404 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
jbe@532	2405 ( "recipient_id_p" "member"."id"%TYPE )
jbe@532	2406 RETURNS SETOF "initiative_for_notification"
jbe@532	2407 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2408 DECLARE
jbe@532	2409 "result_row" "initiative_for_notification"%ROWTYPE;
jbe@532	2410 "last_draft_id_v" "draft"."id"%TYPE;
jbe@532	2411 "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@532	2412 BEGIN
jbe@532	2413 PERFORM "require_transaction_isolation"();
jbe@532	2414 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@532	2415 FOR "result_row" IN
jbe@532	2416 SELECT * FROM "initiative_for_notification"
jbe@532	2417 WHERE "recipient_id" = "recipient_id_p"
jbe@532	2418 LOOP
jbe@532	2419 SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@532	2420 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@532	2421 ORDER BY "id" DESC LIMIT 1;
jbe@532	2422 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@532	2423 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@532	2424 ORDER BY "id" DESC LIMIT 1;
jbe@532	2425 INSERT INTO "notification_initiative_sent"
jbe@532	2426 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@532	2427 VALUES (
jbe@532	2428 "recipient_id_p",
jbe@532	2429 "result_row"."initiative_id",
jbe@532	2430 "last_draft_id_v",
jbe@532	2431 "last_suggestion_id_v" )
jbe@532	2432 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@532	2433 "last_draft_id" = "last_draft_id_v",
jbe@532	2434 "last_suggestion_id" = "last_suggestion_id_v";
jbe@532	2435 RETURN NEXT "result_row";
jbe@532	2436 END LOOP;
jbe@532	2437 DELETE FROM "notification_initiative_sent"
jbe@532	2438 USING "initiative", "issue"
jbe@532	2439 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
jbe@532	2440 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
jbe@532	2441 AND "issue"."id" = "initiative"."issue_id"
jbe@532	2442 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@532	2443 UPDATE "member" SET
jbe@532	2444 "notification_counter" = "notification_counter" + 1,
jbe@532	2445 "notification_sent" = now()
jbe@532	2446 WHERE "id" = "recipient_id_p";
jbe@532	2447 RETURN;
jbe@532	2448 END;
jbe@532	2449 $$;
jbe@532	2450
jbe@532	2451
jbe@532	2452 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@532	2453 RETURNS VOID
jbe@532	2454 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2455 BEGIN
jbe@532	2456 PERFORM "require_transaction_isolation"();
jbe@532	2457 DELETE FROM "member_count";
jbe@532	2458 INSERT INTO "member_count" ("total_count")
jbe@532	2459 SELECT "total_count" FROM "member_count_view";
jbe@532	2460 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@532	2461 FROM "unit_member_count" AS "view"
jbe@532	2462 WHERE "view"."unit_id" = "unit"."id";
jbe@532	2463 RETURN;
jbe@532	2464 END;
jbe@532	2465 $$;
jbe@532	2466
jbe@532	2467 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	2468
jbe@532	2469
jbe@532	2470 CREATE FUNCTION "calculate_area_quorum"()
jbe@532	2471 RETURNS VOID
jbe@532	2472 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2473 BEGIN
jbe@532	2474 PERFORM "dont_require_transaction_isolation"();
jbe@532	2475 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532	2476 FROM "area_quorum" AS "view"
jbe@532	2477 WHERE "view"."area_id" = "area"."id";
jbe@532	2478 RETURN;
jbe@532	2479 END;
jbe@532	2480 $$;
jbe@532	2481
jbe@532	2482 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
jbe@532	2483
jbe@532	2484
jbe@532	2485 DROP VIEW "remaining_harmonic_initiative_weight_summands";
jbe@532	2486 DROP VIEW "remaining_harmonic_supporter_weight";
jbe@532	2487
jbe@532	2488
jbe@532	2489 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@532	2490 SELECT
jbe@532	2491 "direct_interest_snapshot"."snapshot_id",
jbe@532	2492 "direct_interest_snapshot"."issue_id",
jbe@532	2493 "direct_interest_snapshot"."member_id",
jbe@532	2494 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@532	2495 count("initiative"."id") AS "weight_den"
jbe@532	2496 FROM "issue"
jbe@532	2497 JOIN "direct_interest_snapshot"
jbe@532	2498 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
jbe@532	2499 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@532	2500 JOIN "initiative"
jbe@532	2501 ON "issue"."id" = "initiative"."issue_id"
jbe@532	2502 AND "initiative"."harmonic_weight" ISNULL
jbe@532	2503 JOIN "direct_supporter_snapshot"
jbe@532	2504 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532	2505 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532	2506 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532	2507 AND (
jbe@532	2508 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532	2509 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532	2510 )
jbe@532	2511 GROUP BY
jbe@532	2512 "direct_interest_snapshot"."snapshot_id",
jbe@532	2513 "direct_interest_snapshot"."issue_id",
jbe@532	2514 "direct_interest_snapshot"."member_id",
jbe@532	2515 "direct_interest_snapshot"."weight";
jbe@532	2516
jbe@532	2517
jbe@532	2518 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@532	2519 SELECT
jbe@532	2520 "initiative"."issue_id",
jbe@532	2521 "initiative"."id" AS "initiative_id",
jbe@532	2522 "initiative"."admitted",
jbe@532	2523 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@532	2524 "remaining_harmonic_supporter_weight"."weight_den"
jbe@532	2525 FROM "remaining_harmonic_supporter_weight"
jbe@532	2526 JOIN "initiative"
jbe@532	2527 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@532	2528 AND "initiative"."harmonic_weight" ISNULL
jbe@532	2529 JOIN "direct_supporter_snapshot"
jbe@532	2530 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532	2531 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532	2532 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532	2533 AND (
jbe@532	2534 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532	2535 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532	2536 )
jbe@532	2537 GROUP BY
jbe@532	2538 "initiative"."issue_id",
jbe@532	2539 "initiative"."id",
jbe@532	2540 "initiative"."admitted",
jbe@532	2541 "remaining_harmonic_supporter_weight"."weight_den";
jbe@532	2542
jbe@532	2543
jbe@532	2544 DROP FUNCTION "create_population_snapshot"
jbe@532	2545 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2546
jbe@532	2547
jbe@532	2548 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@532	2549 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2550 "member_id_p" "member"."id"%TYPE,
jbe@532	2551 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
jbe@532	2552
jbe@532	2553
jbe@532	2554 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@532	2555 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2556 "member_id_p" "member"."id"%TYPE,
jbe@532	2557 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
jbe@532	2558
jbe@532	2559
jbe@532	2560 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532	2561 ( "snapshot_id_p" "snapshot"."id"%TYPE,
jbe@532	2562 "issue_id_p" "issue"."id"%TYPE,
jbe@532	2563 "member_id_p" "member"."id"%TYPE,
jbe@532	2564 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@532	2565 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@532	2566 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2567 DECLARE
jbe@532	2568 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@532	2569 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@532	2570 "weight_v" INT4;
jbe@532	2571 "sub_weight_v" INT4;
jbe@532	2572 BEGIN
jbe@532	2573 PERFORM "require_transaction_isolation"();
jbe@532	2574 "weight_v" := 0;
jbe@532	2575 FOR "issue_delegation_row" IN
jbe@532	2576 SELECT * FROM "issue_delegation"
jbe@532	2577 WHERE "trustee_id" = "member_id_p"
jbe@532	2578 AND "issue_id" = "issue_id_p"
jbe@532	2579 LOOP
jbe@532	2580 IF NOT EXISTS (
jbe@532	2581 SELECT NULL FROM "direct_interest_snapshot"
jbe@532	2582 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2583 AND "issue_id" = "issue_id_p"
jbe@532	2584 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532	2585 ) AND NOT EXISTS (
jbe@532	2586 SELECT NULL FROM "delegating_interest_snapshot"
jbe@532	2587 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2588 AND "issue_id" = "issue_id_p"
jbe@532	2589 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532	2590 ) THEN
jbe@532	2591 "delegate_member_ids_v" :=
jbe@532	2592 "member_id_p" \|\| "delegate_member_ids_p";
jbe@532	2593 INSERT INTO "delegating_interest_snapshot" (
jbe@532	2594 "snapshot_id",
jbe@532	2595 "issue_id",
jbe@532	2596 "member_id",
jbe@532	2597 "scope",
jbe@532	2598 "delegate_member_ids"
jbe@532	2599 ) VALUES (
jbe@532	2600 "snapshot_id_p",
jbe@532	2601 "issue_id_p",
jbe@532	2602 "issue_delegation_row"."truster_id",
jbe@532	2603 "issue_delegation_row"."scope",
jbe@532	2604 "delegate_member_ids_v"
jbe@532	2605 );
jbe@532	2606 "sub_weight_v" := 1 +
jbe@532	2607 "weight_of_added_delegations_for_snapshot"(
jbe@532	2608 "snapshot_id_p",
jbe@532	2609 "issue_id_p",
jbe@532	2610 "issue_delegation_row"."truster_id",
jbe@532	2611 "delegate_member_ids_v"
jbe@532	2612 );
jbe@532	2613 UPDATE "delegating_interest_snapshot"
jbe@532	2614 SET "weight" = "sub_weight_v"
jbe@532	2615 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2616 AND "issue_id" = "issue_id_p"
jbe@532	2617 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@532	2618 "weight_v" := "weight_v" + "sub_weight_v";
jbe@532	2619 END IF;
jbe@532	2620 END LOOP;
jbe@532	2621 RETURN "weight_v";
jbe@532	2622 END;
jbe@532	2623 $$;
jbe@532	2624
jbe@532	2625 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532	2626 ( "snapshot"."id"%TYPE,
jbe@532	2627 "issue"."id"%TYPE,
jbe@532	2628 "member"."id"%TYPE,
jbe@532	2629 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@532	2630 IS 'Helper function for "fill_snapshot" function';
jbe@532	2631
jbe@532	2632
jbe@532	2633 DROP FUNCTION "create_interest_snapshot"
jbe@532	2634 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2635
jbe@532	2636
jbe@532	2637 DROP FUNCTION "create_snapshot"
jbe@532	2638 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2639
jbe@532	2640
jbe@532	2641 CREATE FUNCTION "take_snapshot"
jbe@532	2642 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2643 "area_id_p" "area"."id"%TYPE = NULL )
jbe@532	2644 RETURNS "snapshot"."id"%TYPE
jbe@532	2645 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2646 DECLARE
jbe@532	2647 "area_id_v" "area"."id"%TYPE;
jbe@532	2648 "unit_id_v" "unit"."id"%TYPE;
jbe@532	2649 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	2650 "issue_id_v" "issue"."id"%TYPE;
jbe@532	2651 "member_id_v" "member"."id"%TYPE;
jbe@532	2652 BEGIN
jbe@532	2653 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@532	2654 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@532	2655 END IF;
jbe@532	2656 PERFORM "require_transaction_isolation"();
jbe@532	2657 IF "issue_id_p" ISNULL THEN
jbe@532	2658 "area_id_v" := "area_id_p";
jbe@532	2659 ELSE
jbe@532	2660 SELECT "area_id" INTO "area_id_v"
jbe@532	2661 FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2662 END IF;
jbe@532	2663 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
jbe@532	2664 INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@532	2665 VALUES ("area_id_v", "issue_id_p")
jbe@532	2666 RETURNING "id" INTO "snapshot_id_v";
jbe@532	2667 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	2668 SELECT "snapshot_id_v", "member_id"
jbe@532	2669 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
jbe@532	2670 UPDATE "snapshot" SET
jbe@532	2671 "population" = (
jbe@532	2672 SELECT count(1) FROM "snapshot_population"
jbe@532	2673 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2674 ) WHERE "id" = "snapshot_id_v";
jbe@532	2675 FOR "issue_id_v" IN
jbe@532	2676 SELECT "id" FROM "issue"
jbe@532	2677 WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@532	2678 "area_id" = "area_id_p" AND
jbe@532	2679 "state" = 'admission'
jbe@532	2680 ELSE
jbe@532	2681 "id" = "issue_id_p"
jbe@532	2682 END
jbe@532	2683 LOOP
jbe@532	2684 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532	2685 VALUES ("snapshot_id_v", "issue_id_v");
jbe@532	2686 INSERT INTO "direct_interest_snapshot"
jbe@532	2687 ("snapshot_id", "issue_id", "member_id")
jbe@532	2688 SELECT
jbe@532	2689 "snapshot_id_v" AS "snapshot_id",
jbe@532	2690 "issue_id_v" AS "issue_id",
jbe@532	2691 "member"."id" AS "member_id"
jbe@532	2692 FROM "issue"
jbe@532	2693 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532	2694 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@532	2695 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@532	2696 JOIN "privilege"
jbe@532	2697 ON "privilege"."unit_id" = "area"."unit_id"
jbe@532	2698 AND "privilege"."member_id" = "member"."id"
jbe@532	2699 WHERE "issue"."id" = "issue_id_v"
jbe@532	2700 AND "member"."active" AND "privilege"."voting_right";
jbe@532	2701 FOR "member_id_v" IN
jbe@532	2702 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@532	2703 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2704 AND "issue_id" = "issue_id_v"
jbe@532	2705 LOOP
jbe@532	2706 UPDATE "direct_interest_snapshot" SET
jbe@532	2707 "weight" = 1 +
jbe@532	2708 "weight_of_added_delegations_for_snapshot"(
jbe@532	2709 "snapshot_id_v",
jbe@532	2710 "issue_id_v",
jbe@532	2711 "member_id_v",
jbe@532	2712 '{}'
jbe@532	2713 )
jbe@532	2714 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2715 AND "issue_id" = "issue_id_v"
jbe@532	2716 AND "member_id" = "member_id_v";
jbe@532	2717 END LOOP;
jbe@532	2718 INSERT INTO "direct_supporter_snapshot"
jbe@532	2719 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@532	2720 "draft_id", "informed", "satisfied" )
jbe@532	2721 SELECT
jbe@532	2722 "snapshot_id_v" AS "snapshot_id",
jbe@532	2723 "issue_id_v" AS "issue_id",
jbe@532	2724 "initiative"."id" AS "initiative_id",
jbe@532	2725 "supporter"."member_id" AS "member_id",
jbe@532	2726 "supporter"."draft_id" AS "draft_id",
jbe@532	2727 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@532	2728 NOT EXISTS (
jbe@532	2729 SELECT NULL FROM "critical_opinion"
jbe@532	2730 WHERE "initiative_id" = "initiative"."id"
jbe@532	2731 AND "member_id" = "supporter"."member_id"
jbe@532	2732 ) AS "satisfied"
jbe@532	2733 FROM "initiative"
jbe@532	2734 JOIN "supporter"
jbe@532	2735 ON "supporter"."initiative_id" = "initiative"."id"
jbe@532	2736 JOIN "current_draft"
jbe@532	2737 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@532	2738 JOIN "direct_interest_snapshot"
jbe@532	2739 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@532	2740 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@532	2741 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@532	2742 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532	2743 DELETE FROM "temporary_suggestion_counts";
jbe@532	2744 INSERT INTO "temporary_suggestion_counts"
jbe@532	2745 ( "id",
jbe@532	2746 "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@532	2747 "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@532	2748 "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@532	2749 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@532	2750 SELECT
jbe@532	2751 "suggestion"."id",
jbe@532	2752 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2753 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2754 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2755 AND "di"."issue_id" = "issue_id_v"
jbe@532	2756 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2757 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2758 AND "opinion"."degree" = -2
jbe@532	2759 AND "opinion"."fulfilled" = FALSE
jbe@532	2760 ) AS "minus2_unfulfilled_count",
jbe@532	2761 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2762 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2763 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2764 AND "di"."issue_id" = "issue_id_v"
jbe@532	2765 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2766 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2767 AND "opinion"."degree" = -2
jbe@532	2768 AND "opinion"."fulfilled" = TRUE
jbe@532	2769 ) AS "minus2_fulfilled_count",
jbe@532	2770 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2771 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2772 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2773 AND "di"."issue_id" = "issue_id_v"
jbe@532	2774 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2775 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2776 AND "opinion"."degree" = -1
jbe@532	2777 AND "opinion"."fulfilled" = FALSE
jbe@532	2778 ) AS "minus1_unfulfilled_count",
jbe@532	2779 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2780 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2781 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2782 AND "di"."issue_id" = "issue_id_v"
jbe@532	2783 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2784 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2785 AND "opinion"."degree" = -1
jbe@532	2786 AND "opinion"."fulfilled" = TRUE
jbe@532	2787 ) AS "minus1_fulfilled_count",
jbe@532	2788 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2789 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2790 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2791 AND "di"."issue_id" = "issue_id_v"
jbe@532	2792 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2793 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2794 AND "opinion"."degree" = 1
jbe@532	2795 AND "opinion"."fulfilled" = FALSE
jbe@532	2796 ) AS "plus1_unfulfilled_count",
jbe@532	2797 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2798 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2799 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2800 AND "di"."issue_id" = "issue_id_v"
jbe@532	2801 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2802 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2803 AND "opinion"."degree" = 1
jbe@532	2804 AND "opinion"."fulfilled" = TRUE
jbe@532	2805 ) AS "plus1_fulfilled_count",
jbe@532	2806 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2807 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2808 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2809 AND "di"."issue_id" = "issue_id_v"
jbe@532	2810 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2811 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2812 AND "opinion"."degree" = 2
jbe@532	2813 AND "opinion"."fulfilled" = FALSE
jbe@532	2814 ) AS "plus2_unfulfilled_count",
jbe@532	2815 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2816 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2817 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2818 AND "di"."issue_id" = "issue_id_v"
jbe@532	2819 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2820 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2821 AND "opinion"."degree" = 2
jbe@532	2822 AND "opinion"."fulfilled" = TRUE
jbe@532	2823 ) AS "plus2_fulfilled_count"
jbe@532	2824 FROM "suggestion" JOIN "initiative"
jbe@532	2825 ON "suggestion"."initiative_id" = "initiative"."id"
jbe@532	2826 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532	2827 END LOOP;
jbe@532	2828 RETURN "snapshot_id_v";
jbe@532	2829 END;
jbe@532	2830 $$;
jbe@532	2831
jbe@532	2832 COMMENT ON FUNCTION "take_snapshot"
jbe@532	2833 ( "issue"."id"%TYPE,
jbe@532	2834 "area"."id"%TYPE )
jbe@532	2835 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	2836
jbe@532	2837
jbe@532	2838 DROP FUNCTION "set_snapshot_event"
jbe@532	2839 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2840 "event_p" "snapshot_event" );
jbe@532	2841
jbe@532	2842
jbe@532	2843 CREATE FUNCTION "finish_snapshot"
jbe@532	2844 ( "issue_id_p" "issue"."id"%TYPE )
jbe@532	2845 RETURNS VOID
jbe@532	2846 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2847 DECLARE
jbe@532	2848 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	2849 BEGIN
jbe@532	2850 -- NOTE: function does not require snapshot isolation but we don't call
jbe@532	2851 -- "dont_require_snapshot_isolation" here because this function is
jbe@532	2852 -- also invoked by "check_issue"
jbe@532	2853 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532	2854 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
jbe@532	2855 ORDER BY "id" DESC LIMIT 1;
jbe@532	2856 UPDATE "issue" SET
jbe@532	2857 "calculated" = "snapshot"."calculated",
jbe@532	2858 "latest_snapshot_id" = "snapshot_id_v",
jbe@532	2859 "population" = "snapshot"."population"
jbe@532	2860 FROM "snapshot"
jbe@532	2861 WHERE "issue"."id" = "issue_id_p"
jbe@532	2862 AND "snapshot"."id" = "snapshot_id_v";
jbe@532	2863 UPDATE "initiative" SET
jbe@532	2864 "supporter_count" = (
jbe@532	2865 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2866 FROM "direct_interest_snapshot" AS "di"
jbe@532	2867 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2868 ON "di"."member_id" = "ds"."member_id"
jbe@532	2869 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2870 AND "di"."issue_id" = "issue_id_p"
jbe@532	2871 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2872 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2873 ),
jbe@532	2874 "informed_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 AND "ds"."informed"
jbe@532	2884 ),
jbe@532	2885 "satisfied_supporter_count" = (
jbe@532	2886 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2887 FROM "direct_interest_snapshot" AS "di"
jbe@532	2888 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2889 ON "di"."member_id" = "ds"."member_id"
jbe@532	2890 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2891 AND "di"."issue_id" = "issue_id_p"
jbe@532	2892 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2893 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2894 AND "ds"."satisfied"
jbe@532	2895 ),
jbe@532	2896 "satisfied_informed_supporter_count" = (
jbe@532	2897 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2898 FROM "direct_interest_snapshot" AS "di"
jbe@532	2899 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2900 ON "di"."member_id" = "ds"."member_id"
jbe@532	2901 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2902 AND "di"."issue_id" = "issue_id_p"
jbe@532	2903 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2904 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2905 AND "ds"."informed"
jbe@532	2906 AND "ds"."satisfied"
jbe@532	2907 )
jbe@532	2908 WHERE "issue_id" = "issue_id_p";
jbe@532	2909 UPDATE "suggestion" SET
jbe@532	2910 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
jbe@532	2911 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
jbe@532	2912 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
jbe@532	2913 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
jbe@532	2914 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
jbe@532	2915 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
jbe@532	2916 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
jbe@532	2917 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
jbe@532	2918 FROM "temporary_suggestion_counts" AS "temp", "initiative"
jbe@532	2919 WHERE "temp"."id" = "suggestion"."id"
jbe@532	2920 AND "initiative"."issue_id" = "issue_id_p"
jbe@532	2921 AND "suggestion"."initiative_id" = "initiative"."id";
jbe@532	2922 DELETE FROM "temporary_suggestion_counts";
jbe@532	2923 RETURN;
jbe@532	2924 END;
jbe@532	2925 $$;
jbe@532	2926
jbe@532	2927 COMMENT ON FUNCTION "finish_snapshot"
jbe@532	2928 ( "issue"."id"%TYPE )
jbe@532	2929 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	2930
jbe@532	2931
jbe@532	2932 CREATE FUNCTION "issue_admission"
jbe@532	2933 ( "area_id_p" "area"."id"%TYPE )
jbe@532	2934 RETURNS BOOLEAN
jbe@532	2935 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2936 DECLARE
jbe@532	2937 "issue_id_v" "issue"."id"%TYPE;
jbe@532	2938 BEGIN
jbe@532	2939 PERFORM "dont_require_transaction_isolation"();
jbe@532	2940 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532	2941 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532	2942 FROM "area_quorum" AS "view"
jbe@532	2943 WHERE "area"."id" = "view"."area_id"
jbe@532	2944 AND "area"."id" = "area_id_p";
jbe@532	2945 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
jbe@532	2946 WHERE "area_id" = "area_id_p";
jbe@532	2947 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
jbe@532	2948 UPDATE "issue" SET
jbe@532	2949 "admission_snapshot_id" = "latest_snapshot_id",
jbe@532	2950 "state" = 'discussion',
jbe@532	2951 "accepted" = now(),
jbe@532	2952 "phase_finished" = NULL
jbe@532	2953 WHERE "id" = "issue_id_v";
jbe@532	2954 RETURN TRUE;
jbe@532	2955 END;
jbe@532	2956 $$;
jbe@532	2957
jbe@532	2958 COMMENT ON FUNCTION "issue_admission"
jbe@532	2959 ( "area"."id"%TYPE )
jbe@532	2960 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	2961
jbe@532	2962
jbe@532	2963 CREATE OR REPLACE FUNCTION "check_issue"
jbe@532	2964 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2965 "persist" "check_issue_persistence" )
jbe@532	2966 RETURNS "check_issue_persistence"
jbe@532	2967 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2968 DECLARE
jbe@532	2969 "issue_row" "issue"%ROWTYPE;
jbe@532	2970 "last_calculated_v" "snapshot"."calculated"%TYPE;
jbe@532	2971 "policy_row" "policy"%ROWTYPE;
jbe@532	2972 "initiative_row" "initiative"%ROWTYPE;
jbe@532	2973 "state_v" "issue_state";
jbe@532	2974 BEGIN
jbe@532	2975 PERFORM "require_transaction_isolation"();
jbe@532	2976 IF "persist" ISNULL THEN
jbe@532	2977 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	2978 FOR UPDATE;
jbe@532	2979 SELECT "calculated" INTO "last_calculated_v"
jbe@532	2980 FROM "snapshot" JOIN "snapshot_issue"
jbe@532	2981 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
jbe@532	2982 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
jbe@532	2983 IF "issue_row"."closed" NOTNULL THEN
jbe@532	2984 RETURN NULL;
jbe@532	2985 END IF;
jbe@532	2986 "persist"."state" := "issue_row"."state";
jbe@532	2987 IF
jbe@532	2988 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
jbe@532	2989 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@532	2990 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@532	2991 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@532	2992 ( "issue_row"."state" = 'verification' AND now() >=
jbe@532	2993 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@532	2994 ( "issue_row"."state" = 'voting' AND now() >=
jbe@532	2995 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@532	2996 THEN
jbe@532	2997 "persist"."phase_finished" := TRUE;
jbe@532	2998 ELSE
jbe@532	2999 "persist"."phase_finished" := FALSE;
jbe@532	3000 END IF;
jbe@532	3001 IF
jbe@532	3002 NOT EXISTS (
jbe@532	3003 -- all initiatives are revoked
jbe@532	3004 SELECT NULL FROM "initiative"
jbe@532	3005 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532	3006 ) AND (
jbe@532	3007 -- and issue has not been accepted yet
jbe@532	3008 "persist"."state" = 'admission' OR
jbe@532	3009 -- or verification time has elapsed
jbe@532	3010 ( "persist"."state" = 'verification' AND
jbe@532	3011 "persist"."phase_finished" ) OR
jbe@532	3012 -- or no initiatives have been revoked lately
jbe@532	3013 NOT EXISTS (
jbe@532	3014 SELECT NULL FROM "initiative"
jbe@532	3015 WHERE "issue_id" = "issue_id_p"
jbe@532	3016 AND now() < "revoked" + "issue_row"."verification_time"
jbe@532	3017 )
jbe@532	3018 )
jbe@532	3019 THEN
jbe@532	3020 "persist"."issue_revoked" := TRUE;
jbe@532	3021 ELSE
jbe@532	3022 "persist"."issue_revoked" := FALSE;
jbe@532	3023 END IF;
jbe@532	3024 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@532	3025 UPDATE "issue" SET "phase_finished" = now()
jbe@532	3026 WHERE "id" = "issue_row"."id";
jbe@532	3027 RETURN "persist";
jbe@532	3028 ELSIF
jbe@532	3029 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@532	3030 THEN
jbe@532	3031 RETURN "persist";
jbe@532	3032 ELSE
jbe@532	3033 RETURN NULL;
jbe@532	3034 END IF;
jbe@532	3035 END IF;
jbe@532	3036 IF
jbe@532	3037 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532	3038 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@532	3039 THEN
jbe@532	3040 IF "persist"."state" != 'admission' THEN
jbe@532	3041 PERFORM "take_snapshot"("issue_id_p");
jbe@532	3042 PERFORM "finish_snapshot"("issue_id_p");
jbe@532	3043 END IF;
jbe@532	3044 "persist"."snapshot_created" = TRUE;
jbe@532	3045 IF "persist"."phase_finished" THEN
jbe@532	3046 IF "persist"."state" = 'admission' THEN
jbe@532	3047 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
jbe@532	3048 ELSIF "persist"."state" = 'discussion' THEN
jbe@532	3049 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532	3050 ELSIF "persist"."state" = 'verification' THEN
jbe@532	3051 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532	3052 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	3053 SELECT * INTO "policy_row" FROM "policy"
jbe@532	3054 WHERE "id" = "issue_row"."policy_id";
jbe@532	3055 FOR "initiative_row" IN
jbe@532	3056 SELECT * FROM "initiative"
jbe@532	3057 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532	3058 FOR UPDATE
jbe@532	3059 LOOP
jbe@532	3060 IF
jbe@532	3061 "initiative_row"."polling" OR (
jbe@532	3062 "initiative_row"."satisfied_supporter_count" >
jbe@532	3063 "policy_row"."initiative_quorum" AND
jbe@532	3064 "initiative_row"."satisfied_supporter_count" *
jbe@532	3065 "policy_row"."initiative_quorum_den" >=
jbe@532	3066 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@532	3067 )
jbe@532	3068 THEN
jbe@532	3069 UPDATE "initiative" SET "admitted" = TRUE
jbe@532	3070 WHERE "id" = "initiative_row"."id";
jbe@532	3071 ELSE
jbe@532	3072 UPDATE "initiative" SET "admitted" = FALSE
jbe@532	3073 WHERE "id" = "initiative_row"."id";
jbe@532	3074 END IF;
jbe@532	3075 END LOOP;
jbe@532	3076 END IF;
jbe@532	3077 END IF;
jbe@532	3078 RETURN "persist";
jbe@532	3079 END IF;
jbe@532	3080 IF
jbe@532	3081 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532	3082 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@532	3083 THEN
jbe@532	3084 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@532	3085 "persist"."harmonic_weights_set" = TRUE;
jbe@532	3086 IF
jbe@532	3087 "persist"."phase_finished" OR
jbe@532	3088 "persist"."issue_revoked" OR
jbe@532	3089 "persist"."state" = 'admission'
jbe@532	3090 THEN
jbe@532	3091 RETURN "persist";
jbe@532	3092 ELSE
jbe@532	3093 RETURN NULL;
jbe@532	3094 END IF;
jbe@532	3095 END IF;
jbe@532	3096 IF "persist"."issue_revoked" THEN
jbe@532	3097 IF "persist"."state" = 'admission' THEN
jbe@532	3098 "state_v" := 'canceled_revoked_before_accepted';
jbe@532	3099 ELSIF "persist"."state" = 'discussion' THEN
jbe@532	3100 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@532	3101 ELSIF "persist"."state" = 'verification' THEN
jbe@532	3102 "state_v" := 'canceled_after_revocation_during_verification';
jbe@532	3103 END IF;
jbe@532	3104 UPDATE "issue" SET
jbe@532	3105 "state" = "state_v",
jbe@532	3106 "closed" = "phase_finished",
jbe@532	3107 "phase_finished" = NULL
jbe@532	3108 WHERE "id" = "issue_id_p";
jbe@532	3109 RETURN NULL;
jbe@532	3110 END IF;
jbe@532	3111 IF "persist"."state" = 'admission' THEN
jbe@532	3112 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	3113 FOR UPDATE;
jbe@532	3114 IF "issue_row"."phase_finished" NOTNULL THEN
jbe@532	3115 UPDATE "issue" SET
jbe@532	3116 "state" = 'canceled_issue_not_accepted',
jbe@532	3117 "closed" = "phase_finished",
jbe@532	3118 "phase_finished" = NULL
jbe@532	3119 WHERE "id" = "issue_id_p";
jbe@532	3120 END IF;
jbe@532	3121 RETURN NULL;
jbe@532	3122 END IF;
jbe@532	3123 IF "persist"."phase_finished" THEN
jbe@532	3124 IF "persist"."state" = 'discussion' THEN
jbe@532	3125 UPDATE "issue" SET
jbe@532	3126 "state" = 'verification',
jbe@532	3127 "half_frozen" = "phase_finished",
jbe@532	3128 "phase_finished" = NULL
jbe@532	3129 WHERE "id" = "issue_id_p";
jbe@532	3130 RETURN NULL;
jbe@532	3131 END IF;
jbe@532	3132 IF "persist"."state" = 'verification' THEN
jbe@532	3133 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	3134 FOR UPDATE;
jbe@532	3135 SELECT * INTO "policy_row" FROM "policy"
jbe@532	3136 WHERE "id" = "issue_row"."policy_id";
jbe@532	3137 IF EXISTS (
jbe@532	3138 SELECT NULL FROM "initiative"
jbe@532	3139 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@532	3140 ) THEN
jbe@532	3141 UPDATE "issue" SET
jbe@532	3142 "state" = 'voting',
jbe@532	3143 "fully_frozen" = "phase_finished",
jbe@532	3144 "phase_finished" = NULL
jbe@532	3145 WHERE "id" = "issue_id_p";
jbe@532	3146 ELSE
jbe@532	3147 UPDATE "issue" SET
jbe@532	3148 "state" = 'canceled_no_initiative_admitted',
jbe@532	3149 "fully_frozen" = "phase_finished",
jbe@532	3150 "closed" = "phase_finished",
jbe@532	3151 "phase_finished" = NULL
jbe@532	3152 WHERE "id" = "issue_id_p";
jbe@532	3153 -- NOTE: The following DELETE statements have effect only when
jbe@532	3154 -- issue state has been manipulated
jbe@532	3155 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@532	3156 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@532	3157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@532	3158 END IF;
jbe@532	3159 RETURN NULL;
jbe@532	3160 END IF;
jbe@532	3161 IF "persist"."state" = 'voting' THEN
jbe@532	3162 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@532	3163 PERFORM "close_voting"("issue_id_p");
jbe@532	3164 "persist"."closed_voting" = TRUE;
jbe@532	3165 RETURN "persist";
jbe@532	3166 END IF;
jbe@532	3167 PERFORM "calculate_ranks"("issue_id_p");
jbe@532	3168 RETURN NULL;
jbe@532	3169 END IF;
jbe@532	3170 END IF;
jbe@532	3171 RAISE WARNING 'should not happen';
jbe@532	3172 RETURN NULL;
jbe@532	3173 END;
jbe@532	3174 $$;
jbe@532	3175
jbe@532	3176
jbe@532	3177 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@532	3178 RETURNS VOID
jbe@532	3179 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3180 DECLARE
jbe@532	3181 "area_id_v" "area"."id"%TYPE;
jbe@532	3182 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	3183 "issue_id_v" "issue"."id"%TYPE;
jbe@532	3184 "persist_v" "check_issue_persistence";
jbe@532	3185 BEGIN
jbe@532	3186 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@532	3187 DELETE FROM "expired_session";
jbe@532	3188 DELETE FROM "expired_token";
jbe@532	3189 DELETE FROM "expired_snapshot";
jbe@532	3190 PERFORM "check_activity"();
jbe@532	3191 PERFORM "calculate_member_counts"();
jbe@532	3192 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
jbe@532	3193 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
jbe@532	3194 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
jbe@532	3195 WHERE "snapshot_id" = "snapshot_id_v";
jbe@532	3196 LOOP
jbe@532	3197 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
jbe@532	3198 END LOOP;
jbe@532	3199 END LOOP;
jbe@532	3200 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@532	3201 "persist_v" := NULL;
jbe@532	3202 LOOP
jbe@532	3203 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@532	3204 EXIT WHEN "persist_v" ISNULL;
jbe@532	3205 END LOOP;
jbe@532	3206 END LOOP;
jbe@532	3207 RETURN;
jbe@532	3208 END;
jbe@532	3209 $$;
jbe@532	3210
jbe@532	3211 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	3212
jbe@532	3213
jbe@532	3214 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@532	3215 RETURNS VOID
jbe@532	3216 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3217 BEGIN
jbe@532	3218 IF EXISTS (
jbe@532	3219 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@532	3220 ) THEN
jbe@532	3221 -- override protection triggers:
jbe@532	3222 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@532	3223 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@532	3224 -- clean data:
jbe@532	3225 DELETE FROM "delegating_voter"
jbe@532	3226 WHERE "issue_id" = "issue_id_p";
jbe@532	3227 DELETE FROM "direct_voter"
jbe@532	3228 WHERE "issue_id" = "issue_id_p";
jbe@532	3229 DELETE FROM "delegating_interest_snapshot"
jbe@532	3230 WHERE "issue_id" = "issue_id_p";
jbe@532	3231 DELETE FROM "direct_interest_snapshot"
jbe@532	3232 WHERE "issue_id" = "issue_id_p";
jbe@532	3233 DELETE FROM "non_voter"
jbe@532	3234 WHERE "issue_id" = "issue_id_p";
jbe@532	3235 DELETE FROM "delegation"
jbe@532	3236 WHERE "issue_id" = "issue_id_p";
jbe@532	3237 DELETE FROM "supporter"
jbe@532	3238 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@532	3239 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@532	3240 AND "supporter"."initiative_id" = "initiative_id";
jbe@532	3241 -- mark issue as cleaned:
jbe@532	3242 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@532	3243 -- finish overriding protection triggers (avoids garbage):
jbe@532	3244 DELETE FROM "temporary_transaction_data"
jbe@532	3245 WHERE "key" = 'override_protection_triggers';
jbe@532	3246 END IF;
jbe@532	3247 RETURN;
jbe@532	3248 END;
jbe@532	3249 $$;
jbe@532	3250
jbe@532	3251
jbe@532	3252 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@532	3253 RETURNS VOID
jbe@532	3254 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3255 BEGIN
jbe@532	3256 UPDATE "member" SET
jbe@532	3257 "last_login" = NULL,
jbe@532	3258 "last_delegation_check" = NULL,
jbe@532	3259 "login" = NULL,
jbe@532	3260 "password" = NULL,
jbe@532	3261 "authority" = NULL,
jbe@532	3262 "authority_uid" = NULL,
jbe@532	3263 "authority_login" = NULL,
jbe@532	3264 "locked" = TRUE,
jbe@532	3265 "active" = FALSE,
jbe@532	3266 "notify_email" = NULL,
jbe@532	3267 "notify_email_unconfirmed" = NULL,
jbe@532	3268 "notify_email_secret" = NULL,
jbe@532	3269 "notify_email_secret_expiry" = NULL,
jbe@532	3270 "notify_email_lock_expiry" = NULL,
jbe@532	3271 "disable_notifications" = TRUE,
jbe@532	3272 "notification_counter" = DEFAULT,
jbe@532	3273 "notification_sample_size" = 0,
jbe@532	3274 "notification_dow" = NULL,
jbe@532	3275 "notification_hour" = NULL,
jbe@543	3276 "notification_sent" = NULL,
jbe@532	3277 "login_recovery_expiry" = NULL,
jbe@532	3278 "password_reset_secret" = NULL,
jbe@532	3279 "password_reset_secret_expiry" = NULL,
jbe@532	3280 "location" = NULL
jbe@532	3281 WHERE "id" = "member_id_p";
jbe@532	3282 -- "text_search_data" is updated by triggers
jbe@544	3283 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
jbe@543	3284 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
jbe@543	3285 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
jbe@532	3286 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@532	3287 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@532	3288 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@532	3289 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@543	3290 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
jbe@543	3291 DELETE FROM "token" WHERE "member_id" = "member_id_p";
jbe@543	3292 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
jbe@543	3293 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
jbe@532	3294 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@532	3295 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@532	3296 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@532	3297 DELETE FROM "direct_voter" USING "issue"
jbe@532	3298 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532	3299 AND "issue"."closed" ISNULL
jbe@532	3300 AND "member_id" = "member_id_p";
jbe@543	3301 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
jbe@532	3302 RETURN;
jbe@532	3303 END;
jbe@532	3304 $$;
jbe@532	3305
jbe@532	3306
jbe@532	3307 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@532	3308 RETURNS VOID
jbe@532	3309 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3310 BEGIN
jbe@532	3311 DELETE FROM "temporary_transaction_data";
jbe@543	3312 DELETE FROM "temporary_suggestion_counts";
jbe@532	3313 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@532	3314 UPDATE "member" SET
jbe@532	3315 "invite_code" = NULL,
jbe@532	3316 "invite_code_expiry" = NULL,
jbe@532	3317 "admin_comment" = NULL,
jbe@532	3318 "last_login" = NULL,
jbe@532	3319 "last_delegation_check" = NULL,
jbe@532	3320 "login" = NULL,
jbe@532	3321 "password" = NULL,
jbe@532	3322 "authority" = NULL,
jbe@532	3323 "authority_uid" = NULL,
jbe@532	3324 "authority_login" = NULL,
jbe@532	3325 "lang" = NULL,
jbe@532	3326 "notify_email" = NULL,
jbe@532	3327 "notify_email_unconfirmed" = NULL,
jbe@532	3328 "notify_email_secret" = NULL,
jbe@532	3329 "notify_email_secret_expiry" = NULL,
jbe@532	3330 "notify_email_lock_expiry" = NULL,
jbe@532	3331 "disable_notifications" = TRUE,
jbe@532	3332 "notification_counter" = DEFAULT,
jbe@532	3333 "notification_sample_size" = 0,
jbe@532	3334 "notification_dow" = NULL,
jbe@532	3335 "notification_hour" = NULL,
jbe@543	3336 "notification_sent" = NULL,
jbe@532	3337 "login_recovery_expiry" = NULL,
jbe@532	3338 "password_reset_secret" = NULL,
jbe@532	3339 "password_reset_secret_expiry" = NULL,
jbe@532	3340 "location" = NULL;
jbe@532	3341 -- "text_search_data" is updated by triggers
jbe@544	3342 DELETE FROM "member_settings";
jbe@544	3343 DELETE FROM "member_useterms";
jbe@543	3344 DELETE FROM "member_profile";
jbe@543	3345 DELETE FROM "rendered_member_statement";
jbe@532	3346 DELETE FROM "member_image";
jbe@532	3347 DELETE FROM "contact";
jbe@532	3348 DELETE FROM "ignored_member";
jbe@532	3349 DELETE FROM "session";
jbe@543	3350 DELETE FROM "system_application";
jbe@543	3351 DELETE FROM "system_application_redirect_uri";
jbe@543	3352 DELETE FROM "dynamic_application_scope";
jbe@543	3353 DELETE FROM "member_application";
jbe@543	3354 DELETE FROM "token";
jbe@543	3355 DELETE FROM "subscription";
jbe@543	3356 DELETE FROM "ignored_area";
jbe@532	3357 DELETE FROM "ignored_initiative";
jbe@532	3358 DELETE FROM "non_voter";
jbe@532	3359 DELETE FROM "direct_voter" USING "issue"
jbe@532	3360 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532	3361 AND "issue"."closed" ISNULL;
jbe@543	3362 DELETE FROM "event_processed";
jbe@543	3363 DELETE FROM "notification_initiative_sent";
jbe@543	3364 DELETE FROM "newsletter";
jbe@532	3365 RETURN;
jbe@532	3366 END;
jbe@532	3367 $$;
jbe@532	3368
jbe@532	3369
jbe@532	3370 CREATE TEMPORARY TABLE "old_snapshot" AS
jbe@532	3371 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
jbe@532	3372 FROM (
jbe@532	3373 SELECT * FROM (
jbe@532	3374 SELECT
jbe@532	3375 "id" AS "issue_id",
jbe@532	3376 'end_of_admission'::"snapshot_event" AS "event",
jbe@532	3377 "accepted" AS "calculated"
jbe@532	3378 FROM "issue" WHERE "accepted" NOTNULL
jbe@532	3379 UNION ALL
jbe@532	3380 SELECT
jbe@532	3381 "id" AS "issue_id",
jbe@532	3382 'half_freeze'::"snapshot_event" AS "event",
jbe@532	3383 "half_frozen" AS "calculated"
jbe@532	3384 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@532	3385 UNION ALL
jbe@532	3386 SELECT
jbe@532	3387 "id" AS "issue_id",
jbe@532	3388 'full_freeze'::"snapshot_event" AS "event",
jbe@532	3389 "fully_frozen" AS "calculated"
jbe@532	3390 FROM "issue" WHERE "fully_frozen" NOTNULL
jbe@532	3391 ) AS "unordered"
jbe@532	3392 ORDER BY "calculated", "issue_id", "event"
jbe@532	3393 ) AS "ordered";
jbe@532	3394
jbe@532	3395
jbe@532	3396 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
jbe@532	3397 SELECT
jbe@532	3398 "old_snapshot"."snapshot_id" AS "id",
jbe@532	3399 "old_snapshot"."calculated",
jbe@532	3400 ( SELECT COALESCE(sum("weight"), 0)
jbe@532	3401 FROM "direct_population_snapshot" "dps"
jbe@532	3402 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
jbe@532	3403 AND "dps"."event" = "old_snapshot"."event"
jbe@532	3404 ) AS "population",
jbe@532	3405 "issue"."area_id" AS "area_id",
jbe@532	3406 "issue"."id" AS "issue_id"
jbe@532	3407 FROM "old_snapshot" JOIN "issue"
jbe@532	3408 ON "old_snapshot"."issue_id" = "issue"."id";
jbe@532	3409
jbe@532	3410
jbe@532	3411 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532	3412 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
jbe@532	3413
jbe@532	3414
jbe@532	3415 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	3416 SELECT
jbe@532	3417 "old_snapshot"."snapshot_id",
jbe@532	3418 "direct_population_snapshot"."member_id"
jbe@532	3419 FROM "old_snapshot" JOIN "direct_population_snapshot"
jbe@532	3420 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
jbe@532	3421 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
jbe@532	3422
jbe@532	3423 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	3424 SELECT
jbe@532	3425 "old_snapshot"."snapshot_id",
jbe@532	3426 "delegating_population_snapshot"."member_id"
jbe@532	3427 FROM "old_snapshot" JOIN "delegating_population_snapshot"
jbe@532	3428 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
jbe@532	3429 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
jbe@532	3430
jbe@532	3431
jbe@532	3432 INSERT INTO "direct_interest_snapshot"
jbe@532	3433 ("snapshot_id", "issue_id", "member_id", "weight")
jbe@532	3434 SELECT
jbe@532	3435 "old_snapshot"."snapshot_id",
jbe@532	3436 "old_snapshot"."issue_id",
jbe@532	3437 "direct_interest_snapshot_old"."member_id",
jbe@532	3438 "direct_interest_snapshot_old"."weight"
jbe@532	3439 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
jbe@532	3440 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
jbe@532	3441 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
jbe@532	3442
jbe@532	3443 INSERT INTO "delegating_interest_snapshot"
jbe@532	3444 ( "snapshot_id", "issue_id",
jbe@532	3445 "member_id", "weight", "scope", "delegate_member_ids" )
jbe@532	3446 SELECT
jbe@532	3447 "old_snapshot"."snapshot_id",
jbe@532	3448 "old_snapshot"."issue_id",
jbe@532	3449 "delegating_interest_snapshot_old"."member_id",
jbe@532	3450 "delegating_interest_snapshot_old"."weight",
jbe@532	3451 "delegating_interest_snapshot_old"."scope",
jbe@532	3452 "delegating_interest_snapshot_old"."delegate_member_ids"
jbe@532	3453 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
jbe@532	3454 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
jbe@532	3455 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
jbe@532	3456
jbe@532	3457 INSERT INTO "direct_supporter_snapshot"
jbe@532	3458 ( "snapshot_id", "issue_id",
jbe@532	3459 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
jbe@532	3460 SELECT
jbe@532	3461 "old_snapshot"."snapshot_id",
jbe@532	3462 "old_snapshot"."issue_id",
jbe@532	3463 "direct_supporter_snapshot_old"."initiative_id",
jbe@532	3464 "direct_supporter_snapshot_old"."member_id",
jbe@532	3465 "direct_supporter_snapshot_old"."draft_id",
jbe@532	3466 "direct_supporter_snapshot_old"."informed",
jbe@532	3467 "direct_supporter_snapshot_old"."satisfied"
jbe@532	3468 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
jbe@532	3469 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
jbe@532	3470 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
jbe@532	3471
jbe@532	3472
jbe@532	3473 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
jbe@532	3474
jbe@532	3475 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
jbe@532	3476 FROM (
jbe@532	3477 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
jbe@532	3478 FROM "snapshot" ORDER BY "issue_id", "id" DESC
jbe@532	3479 ) AS "snapshot"
jbe@532	3480 WHERE "snapshot"."issue_id" = "issue"."id";
jbe@532	3481
jbe@532	3482 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532	3483 FROM "old_snapshot"
jbe@532	3484 WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532	3485 AND "old_snapshot"."event" = 'end_of_admission';
jbe@532	3486
jbe@532	3487 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532	3488 FROM "old_snapshot"
jbe@532	3489 WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532	3490 AND "old_snapshot"."event" = 'half_freeze';
jbe@532	3491
jbe@532	3492 UPDATE "issue" SET "full_freeze_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" = 'full_freeze';
jbe@532	3496
jbe@532	3497 ALTER TABLE "issue" ENABLE TRIGGER USER;
jbe@532	3498
jbe@532	3499
jbe@532	3500 DROP TABLE "old_snapshot";
jbe@532	3501
jbe@532	3502 DROP TABLE "direct_supporter_snapshot_old";
jbe@532	3503 DROP TABLE "delegating_interest_snapshot_old";
jbe@532	3504 DROP TABLE "direct_interest_snapshot_old";
jbe@532	3505 DROP TABLE "delegating_population_snapshot";
jbe@532	3506 DROP TABLE "direct_population_snapshot";
jbe@532	3507
jbe@532	3508
jbe@532	3509 DROP VIEW "open_issue";
jbe@532	3510
jbe@532	3511
jbe@532	3512 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
jbe@532	3513
jbe@532	3514
jbe@532	3515 CREATE VIEW "open_issue" AS
jbe@532	3516 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@532	3517
jbe@532	3518 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@532	3519
jbe@532	3520
jbe@532	3521 -- NOTE: create "issue_for_admission" view after altering table "issue"
jbe@532	3522 CREATE VIEW "issue_for_admission" AS
jbe@532	3523 SELECT DISTINCT ON ("issue"."area_id")
jbe@532	3524 "issue".*,
jbe@532	3525 max("initiative"."supporter_count") AS "max_supporter_count"
jbe@532	3526 FROM "issue"
jbe@532	3527 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@532	3528 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@532	3529 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532	3530 WHERE "issue"."state" = 'admission'::"issue_state"
jbe@532	3531 AND now() >= "issue"."created" + "issue"."min_admission_time"
jbe@532	3532 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
jbe@532	3533 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
jbe@532	3534 "issue"."population" * "policy"."issue_quorum_num"
jbe@532	3535 AND "initiative"."supporter_count" >= "area"."issue_quorum"
jbe@532	3536 AND "initiative"."revoked" ISNULL
jbe@532	3537 GROUP BY "issue"."id"
jbe@532	3538 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
jbe@532	3539
jbe@532	3540 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	3541
jbe@532	3542
jbe@532	3543 DROP TYPE "snapshot_event";
jbe@532	3544
jbe@532	3545
jbe@532	3546 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
jbe@532	3547 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
jbe@532	3548 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
jbe@532	3549
jbe@532	3550
jbe@532	3551 COMMIT;