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 @ 557:0fc78541dc15

Added "verification" table to store verification requests

author	jbe
date	Mon Sep 18 01:44:32 2017 +0200 (2017-09-18)
parents	3f21631a7f6d
children	25b551e53da2

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