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 @ 534:b341544beb75

Renamed table "notification_event_sent" to "event_processed"

author	jbe
date	Fri Apr 21 12:42:43 2017 +0200 (2017-04-21)
parents	5855ff9e5c8f
children	8b6433096a58

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