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 @ 555:5d098bcc631a

Member verification through organizational units

author	jbe
date	Sun Sep 17 01:48:45 2017 +0200 (2017-09-17)
parents	3e7ad7233404
children	3f21631a7f6d

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