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 @ 539:eaa330e4c0fb

Renamed three "event_type"s

author	jbe
date	Mon Jun 26 18:25:29 2017 +0200 (2017-06-26)
parents	1bc3dfe5823e
children	ec84707b459a

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