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 @ 560:318827ecd041

Changes to "verification" table

author	jbe
date	Thu Sep 21 17:45:13 2017 +0200 (2017-09-21)
parents	71f431fb78d4
children	fb2663ca1e6b

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