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 @ 547:3cde0bb68adf

Use unit/area/policy_updated event instead of unit/area/policy_removed

author	jbe
date	Tue Jul 18 12:28:45 2017 +0200 (2017-07-18)
parents	f46ebb677898
children	1adb0ad5900c

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