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 @ 532:5855ff9e5c8f

Several changes/additions for upcoming major release

- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table

author	jbe
date	Thu Mar 30 19:42:38 2017 +0200 (2017-03-30)
parents
children	b341544beb75

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@532	910 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@532	911 RETURNS TRIGGER
jbe@532	912 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	913 DECLARE
jbe@532	914 "area_row" "area"%ROWTYPE;
jbe@532	915 BEGIN
jbe@532	916 IF NEW."state" != OLD."state" THEN
jbe@532	917 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
jbe@532	918 FOR SHARE;
jbe@532	919 INSERT INTO "event" (
jbe@532	920 "event",
jbe@532	921 "unit_id", "area_id", "issue_id", "state"
jbe@532	922 ) VALUES (
jbe@532	923 'issue_state_changed',
jbe@532	924 "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state"
jbe@532	925 );
jbe@532	926 END IF;
jbe@532	927 RETURN NULL;
jbe@532	928 END;
jbe@532	929 $$;
jbe@532	930
jbe@532	931
jbe@532	932 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@532	933 RETURNS TRIGGER
jbe@532	934 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	935 DECLARE
jbe@532	936 "initiative_row" "initiative"%ROWTYPE;
jbe@532	937 "issue_row" "issue"%ROWTYPE;
jbe@532	938 "area_row" "area"%ROWTYPE;
jbe@532	939 "event_v" "event_type";
jbe@532	940 BEGIN
jbe@532	941 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	942 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	943 SELECT * INTO "issue_row" FROM "issue"
jbe@532	944 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	945 SELECT * INTO "area_row" FROM "area"
jbe@532	946 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	947 IF EXISTS (
jbe@532	948 SELECT NULL FROM "draft"
jbe@532	949 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
jbe@532	950 FOR SHARE
jbe@532	951 ) THEN
jbe@532	952 "event_v" := 'new_draft_created';
jbe@532	953 ELSE
jbe@532	954 IF EXISTS (
jbe@532	955 SELECT NULL FROM "initiative"
jbe@532	956 WHERE "issue_id" = "initiative_row"."issue_id"
jbe@532	957 AND "id" != "initiative_row"."id"
jbe@532	958 FOR SHARE
jbe@532	959 ) THEN
jbe@532	960 "event_v" := 'initiative_created_in_existing_issue';
jbe@532	961 ELSE
jbe@532	962 "event_v" := 'initiative_created_in_new_issue';
jbe@532	963 END IF;
jbe@532	964 END IF;
jbe@532	965 INSERT INTO "event" (
jbe@532	966 "event", "member_id",
jbe@532	967 "unit_id", "area_id", "issue_id", "state",
jbe@532	968 "initiative_id", "draft_id"
jbe@532	969 ) VALUES (
jbe@532	970 "event_v", NEW."author_id",
jbe@532	971 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	972 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	973 NEW."initiative_id", NEW."id"
jbe@532	974 );
jbe@532	975 RETURN NULL;
jbe@532	976 END;
jbe@532	977 $$;
jbe@532	978
jbe@532	979
jbe@532	980 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@532	981 RETURNS TRIGGER
jbe@532	982 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	983 DECLARE
jbe@532	984 "issue_row" "issue"%ROWTYPE;
jbe@532	985 "area_row" "area"%ROWTYPE;
jbe@532	986 "draft_id_v" "draft"."id"%TYPE;
jbe@532	987 BEGIN
jbe@532	988 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@532	989 SELECT * INTO "issue_row" FROM "issue"
jbe@532	990 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	991 SELECT * INTO "area_row" FROM "area"
jbe@532	992 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	993 SELECT "id" INTO "draft_id_v" FROM "current_draft"
jbe@532	994 WHERE "initiative_id" = NEW."id" FOR SHARE;
jbe@532	995 INSERT INTO "event" (
jbe@532	996 "event", "member_id",
jbe@532	997 "unit_id", "area_id", "issue_id", "state",
jbe@532	998 "initiative_id", "draft_id"
jbe@532	999 ) VALUES (
jbe@532	1000 'initiative_revoked', NEW."revoked_by_member_id",
jbe@532	1001 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1002 NEW."issue_id", "issue_row"."state",
jbe@532	1003 NEW."id", "draft_id_v"
jbe@532	1004 );
jbe@532	1005 END IF;
jbe@532	1006 RETURN NULL;
jbe@532	1007 END;
jbe@532	1008 $$;
jbe@532	1009
jbe@532	1010
jbe@532	1011 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
jbe@532	1012 RETURNS TRIGGER
jbe@532	1013 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1014 DECLARE
jbe@532	1015 "initiative_row" "initiative"%ROWTYPE;
jbe@532	1016 "issue_row" "issue"%ROWTYPE;
jbe@532	1017 "area_row" "area"%ROWTYPE;
jbe@532	1018 BEGIN
jbe@532	1019 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1020 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	1021 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1022 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1023 SELECT * INTO "area_row" FROM "area"
jbe@532	1024 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1025 INSERT INTO "event" (
jbe@532	1026 "event", "member_id",
jbe@532	1027 "unit_id", "area_id", "issue_id", "state",
jbe@532	1028 "initiative_id", "suggestion_id"
jbe@532	1029 ) VALUES (
jbe@532	1030 'suggestion_created', NEW."author_id",
jbe@532	1031 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1032 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1033 NEW."initiative_id", NEW."id"
jbe@532	1034 );
jbe@532	1035 RETURN NULL;
jbe@532	1036 END;
jbe@532	1037 $$;
jbe@532	1038
jbe@532	1039
jbe@532	1040 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
jbe@532	1041 RETURNS TRIGGER
jbe@532	1042 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1043 DECLARE
jbe@532	1044 "initiative_row" "initiative"%ROWTYPE;
jbe@532	1045 "issue_row" "issue"%ROWTYPE;
jbe@532	1046 "area_row" "area"%ROWTYPE;
jbe@532	1047 BEGIN
jbe@532	1048 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1049 WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532	1050 IF "initiative_row"."id" NOTNULL THEN
jbe@532	1051 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1052 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1053 SELECT * INTO "area_row" FROM "area"
jbe@532	1054 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1055 INSERT INTO "event" (
jbe@532	1056 "event",
jbe@532	1057 "unit_id", "area_id", "issue_id", "state",
jbe@532	1058 "initiative_id", "suggestion_id"
jbe@532	1059 ) VALUES (
jbe@532	1060 'suggestion_removed',
jbe@532	1061 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1062 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1063 OLD."initiative_id", OLD."id"
jbe@532	1064 );
jbe@532	1065 END IF;
jbe@532	1066 RETURN NULL;
jbe@532	1067 END;
jbe@532	1068 $$;
jbe@532	1069
jbe@532	1070 CREATE TRIGGER "write_event_suggestion_removed"
jbe@532	1071 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1072 "write_event_suggestion_removed_trigger"();
jbe@532	1073
jbe@532	1074 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
jbe@532	1075 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@532	1076
jbe@532	1077
jbe@532	1078 CREATE FUNCTION "write_event_member_trigger"()
jbe@532	1079 RETURNS TRIGGER
jbe@532	1080 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1081 BEGIN
jbe@532	1082 IF TG_OP = 'INSERT' THEN
jbe@532	1083 IF NEW."activated" NOTNULL THEN
jbe@532	1084 INSERT INTO "event" ("event", "member_id")
jbe@532	1085 VALUES ('member_activated', NEW."id");
jbe@532	1086 END IF;
jbe@532	1087 IF NEW."active" THEN
jbe@532	1088 INSERT INTO "event" ("event", "member_id", "boolean_value")
jbe@532	1089 VALUES ('member_active', NEW."id", TRUE);
jbe@532	1090 END IF;
jbe@532	1091 ELSIF TG_OP = 'UPDATE' THEN
jbe@532	1092 IF OLD."id" != NEW."id" THEN
jbe@532	1093 RAISE EXCEPTION 'Cannot change member ID';
jbe@532	1094 END IF;
jbe@532	1095 IF OLD."name" != NEW."name" THEN
jbe@532	1096 INSERT INTO "event" (
jbe@532	1097 "event", "member_id", "text_value", "old_text_value"
jbe@532	1098 ) VALUES (
jbe@532	1099 'member_name_updated', NEW."id", NEW."name", OLD."name"
jbe@532	1100 );
jbe@532	1101 END IF;
jbe@532	1102 IF OLD."active" != NEW."active" THEN
jbe@532	1103 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
jbe@532	1104 'member_active', NEW."id", NEW."active"
jbe@532	1105 );
jbe@532	1106 END IF;
jbe@532	1107 IF
jbe@532	1108 OLD."activated" NOTNULL AND
jbe@532	1109 NEW."last_login" ISNULL AND
jbe@532	1110 NEW."login" ISNULL AND
jbe@532	1111 NEW."authority_login" ISNULL AND
jbe@532	1112 NEW."locked" = TRUE
jbe@532	1113 THEN
jbe@532	1114 INSERT INTO "event" ("event", "member_id")
jbe@532	1115 VALUES ('member_removed', NEW."id");
jbe@532	1116 END IF;
jbe@532	1117 END IF;
jbe@532	1118 RETURN NULL;
jbe@532	1119 END;
jbe@532	1120 $$;
jbe@532	1121
jbe@532	1122 CREATE TRIGGER "write_event_member"
jbe@532	1123 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1124 "write_event_member_trigger"();
jbe@532	1125
jbe@532	1126 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
jbe@532	1127 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
jbe@532	1128
jbe@532	1129
jbe@532	1130 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
jbe@532	1131 RETURNS TRIGGER
jbe@532	1132 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1133 BEGIN
jbe@532	1134 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1135 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
jbe@532	1136 INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532	1137 'member_profile_updated', OLD."member_id"
jbe@532	1138 );
jbe@532	1139 END IF;
jbe@532	1140 END IF;
jbe@532	1141 IF TG_OP = 'UPDATE' THEN
jbe@532	1142 IF OLD."member_id" = NEW."member_id" THEN
jbe@532	1143 RETURN NULL;
jbe@532	1144 END IF;
jbe@532	1145 END IF;
jbe@532	1146 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1147 INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532	1148 'member_profile_updated', NEW."member_id"
jbe@532	1149 );
jbe@532	1150 END IF;
jbe@532	1151 RETURN NULL;
jbe@532	1152 END;
jbe@532	1153 $$;
jbe@532	1154
jbe@532	1155 CREATE TRIGGER "write_event_member_profile_updated"
jbe@532	1156 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
jbe@532	1157 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1158 "write_event_member_profile_updated_trigger"();
jbe@532	1159
jbe@532	1160 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
jbe@532	1161 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
jbe@532	1162
jbe@532	1163
jbe@532	1164 CREATE FUNCTION "write_event_member_image_updated_trigger"()
jbe@532	1165 RETURNS TRIGGER
jbe@532	1166 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1167 BEGIN
jbe@532	1168 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1169 IF NOT OLD."scaled" THEN
jbe@532	1170 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
jbe@532	1171 INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532	1172 'member_image_updated', OLD."member_id"
jbe@532	1173 );
jbe@532	1174 END IF;
jbe@532	1175 END IF;
jbe@532	1176 END IF;
jbe@532	1177 IF TG_OP = 'UPDATE' THEN
jbe@532	1178 IF
jbe@532	1179 OLD."member_id" = NEW."member_id" AND
jbe@532	1180 OLD."scaled" = NEW."scaled"
jbe@532	1181 THEN
jbe@532	1182 RETURN NULL;
jbe@532	1183 END IF;
jbe@532	1184 END IF;
jbe@532	1185 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1186 IF NOT NEW."scaled" THEN
jbe@532	1187 INSERT INTO "event" ("event", "member_id") VALUES (
jbe@532	1188 'member_image_updated', NEW."member_id"
jbe@532	1189 );
jbe@532	1190 END IF;
jbe@532	1191 END IF;
jbe@532	1192 RETURN NULL;
jbe@532	1193 END;
jbe@532	1194 $$;
jbe@532	1195
jbe@532	1196 CREATE TRIGGER "write_event_member_image_updated"
jbe@532	1197 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
jbe@532	1198 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1199 "write_event_member_image_updated_trigger"();
jbe@532	1200
jbe@532	1201 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
jbe@532	1202 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
jbe@532	1203
jbe@532	1204
jbe@532	1205 CREATE FUNCTION "write_event_interest_trigger"()
jbe@532	1206 RETURNS TRIGGER
jbe@532	1207 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1208 DECLARE
jbe@532	1209 "issue_row" "issue"%ROWTYPE;
jbe@532	1210 "area_row" "area"%ROWTYPE;
jbe@532	1211 BEGIN
jbe@532	1212 IF TG_OP = 'UPDATE' THEN
jbe@532	1213 IF OLD = NEW THEN
jbe@532	1214 RETURN NULL;
jbe@532	1215 END IF;
jbe@532	1216 END IF;
jbe@532	1217 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1218 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1219 WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532	1220 SELECT * INTO "area_row" FROM "area"
jbe@532	1221 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1222 IF "issue_row"."id" NOTNULL THEN
jbe@532	1223 INSERT INTO "event" (
jbe@532	1224 "event", "member_id",
jbe@532	1225 "unit_id", "area_id", "issue_id", "state",
jbe@532	1226 "boolean_value"
jbe@532	1227 ) VALUES (
jbe@532	1228 'interest', OLD."member_id",
jbe@532	1229 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1230 OLD."issue_id", "issue_row"."state",
jbe@532	1231 FALSE
jbe@532	1232 );
jbe@532	1233 END IF;
jbe@532	1234 END IF;
jbe@532	1235 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1236 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1237 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1238 SELECT * INTO "area_row" FROM "area"
jbe@532	1239 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1240 INSERT INTO "event" (
jbe@532	1241 "event", "member_id",
jbe@532	1242 "unit_id", "area_id", "issue_id", "state",
jbe@532	1243 "boolean_value"
jbe@532	1244 ) VALUES (
jbe@532	1245 'interest', NEW."member_id",
jbe@532	1246 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1247 NEW."issue_id", "issue_row"."state",
jbe@532	1248 TRUE
jbe@532	1249 );
jbe@532	1250 END IF;
jbe@532	1251 RETURN NULL;
jbe@532	1252 END;
jbe@532	1253 $$;
jbe@532	1254
jbe@532	1255 CREATE TRIGGER "write_event_interest"
jbe@532	1256 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1257 "write_event_interest_trigger"();
jbe@532	1258
jbe@532	1259 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
jbe@532	1260 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
jbe@532	1261
jbe@532	1262
jbe@532	1263 CREATE FUNCTION "write_event_initiator_trigger"()
jbe@532	1264 RETURNS TRIGGER
jbe@532	1265 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1266 DECLARE
jbe@532	1267 "initiative_row" "initiative"%ROWTYPE;
jbe@532	1268 "issue_row" "issue"%ROWTYPE;
jbe@532	1269 "area_row" "area"%ROWTYPE;
jbe@532	1270 BEGIN
jbe@532	1271 IF TG_OP = 'UPDATE' THEN
jbe@532	1272 IF
jbe@532	1273 OLD."initiative_id" = NEW."initiative_id" AND
jbe@532	1274 OLD."member_id" = NEW."member_id" AND
jbe@532	1275 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
jbe@532	1276 THEN
jbe@532	1277 RETURN NULL;
jbe@532	1278 END IF;
jbe@532	1279 END IF;
jbe@532	1280 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
jbe@532	1281 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
jbe@532	1282 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1283 WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532	1284 IF "initiative_row"."id" NOTNULL THEN
jbe@532	1285 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1286 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1287 SELECT * INTO "area_row" FROM "area"
jbe@532	1288 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1289 INSERT INTO "event" (
jbe@532	1290 "event", "member_id",
jbe@532	1291 "unit_id", "area_id", "issue_id", "state",
jbe@532	1292 "initiative_id", "boolean_value"
jbe@532	1293 ) VALUES (
jbe@532	1294 'initiator', OLD."member_id",
jbe@532	1295 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1296 "issue_row"."id", "issue_row"."state",
jbe@532	1297 OLD."initiative_id", FALSE
jbe@532	1298 );
jbe@532	1299 END IF;
jbe@532	1300 END IF;
jbe@532	1301 END IF;
jbe@532	1302 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
jbe@532	1303 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
jbe@532	1304 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1305 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	1306 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1307 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1308 SELECT * INTO "area_row" FROM "area"
jbe@532	1309 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1310 INSERT INTO "event" (
jbe@532	1311 "event", "member_id",
jbe@532	1312 "unit_id", "area_id", "issue_id", "state",
jbe@532	1313 "initiative_id", "boolean_value"
jbe@532	1314 ) VALUES (
jbe@532	1315 'initiator', NEW."member_id",
jbe@532	1316 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1317 "issue_row"."id", "issue_row"."state",
jbe@532	1318 NEW."initiative_id", TRUE
jbe@532	1319 );
jbe@532	1320 END IF;
jbe@532	1321 END IF;
jbe@532	1322 RETURN NULL;
jbe@532	1323 END;
jbe@532	1324 $$;
jbe@532	1325
jbe@532	1326 CREATE TRIGGER "write_event_initiator"
jbe@532	1327 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1328 "write_event_initiator_trigger"();
jbe@532	1329
jbe@532	1330 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
jbe@532	1331 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	1332
jbe@532	1333
jbe@532	1334 CREATE FUNCTION "write_event_support_trigger"()
jbe@532	1335 RETURNS TRIGGER
jbe@532	1336 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1337 DECLARE
jbe@532	1338 "issue_row" "issue"%ROWTYPE;
jbe@532	1339 "area_row" "area"%ROWTYPE;
jbe@532	1340 BEGIN
jbe@532	1341 IF TG_OP = 'UPDATE' THEN
jbe@532	1342 IF
jbe@532	1343 OLD."initiative_id" = NEW."initiative_id" AND
jbe@532	1344 OLD."member_id" = NEW."member_id"
jbe@532	1345 THEN
jbe@532	1346 IF OLD."draft_id" != NEW."draft_id" THEN
jbe@532	1347 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1348 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1349 SELECT * INTO "area_row" FROM "area"
jbe@532	1350 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1351 INSERT INTO "event" (
jbe@532	1352 "event", "member_id",
jbe@532	1353 "unit_id", "area_id", "issue_id", "state",
jbe@532	1354 "initiative_id", "draft_id"
jbe@532	1355 ) VALUES (
jbe@532	1356 'support_updated', NEW."member_id",
jbe@532	1357 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1358 "issue_row"."id", "issue_row"."state",
jbe@532	1359 NEW."initiative_id", NEW."draft_id"
jbe@532	1360 );
jbe@532	1361 END IF;
jbe@532	1362 RETURN NULL;
jbe@532	1363 END IF;
jbe@532	1364 END IF;
jbe@532	1365 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1366 IF EXISTS (
jbe@532	1367 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
jbe@532	1368 FOR SHARE
jbe@532	1369 ) THEN
jbe@532	1370 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1371 WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532	1372 SELECT * INTO "area_row" FROM "area"
jbe@532	1373 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1374 INSERT INTO "event" (
jbe@532	1375 "event", "member_id",
jbe@532	1376 "unit_id", "area_id", "issue_id", "state",
jbe@532	1377 "initiative_id", "draft_id", "boolean_value"
jbe@532	1378 ) VALUES (
jbe@532	1379 'support', OLD."member_id",
jbe@532	1380 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1381 "issue_row"."id", "issue_row"."state",
jbe@532	1382 OLD."initiative_id", OLD."draft_id", FALSE
jbe@532	1383 );
jbe@532	1384 END IF;
jbe@532	1385 END IF;
jbe@532	1386 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1387 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1388 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1389 SELECT * INTO "area_row" FROM "area"
jbe@532	1390 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1391 INSERT INTO "event" (
jbe@532	1392 "event", "member_id",
jbe@532	1393 "unit_id", "area_id", "issue_id", "state",
jbe@532	1394 "initiative_id", "draft_id", "boolean_value"
jbe@532	1395 ) VALUES (
jbe@532	1396 'support', NEW."member_id",
jbe@532	1397 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1398 "issue_row"."id", "issue_row"."state",
jbe@532	1399 NEW."initiative_id", NEW."draft_id", TRUE
jbe@532	1400 );
jbe@532	1401 END IF;
jbe@532	1402 RETURN NULL;
jbe@532	1403 END;
jbe@532	1404 $$;
jbe@532	1405
jbe@532	1406 CREATE TRIGGER "write_event_support"
jbe@532	1407 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1408 "write_event_support_trigger"();
jbe@532	1409
jbe@532	1410 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
jbe@532	1411 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532	1412
jbe@532	1413
jbe@532	1414 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
jbe@532	1415 RETURNS TRIGGER
jbe@532	1416 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1417 DECLARE
jbe@532	1418 "same_pkey_v" BOOLEAN = FALSE;
jbe@532	1419 "initiative_row" "initiative"%ROWTYPE;
jbe@532	1420 "issue_row" "issue"%ROWTYPE;
jbe@532	1421 "area_row" "area"%ROWTYPE;
jbe@532	1422 BEGIN
jbe@532	1423 IF TG_OP = 'UPDATE' THEN
jbe@532	1424 IF
jbe@532	1425 OLD."suggestion_id" = NEW."suggestion_id" AND
jbe@532	1426 OLD."member_id" = NEW."member_id"
jbe@532	1427 THEN
jbe@532	1428 IF
jbe@532	1429 OLD."degree" = NEW."degree" AND
jbe@532	1430 OLD."fulfilled" = NEW."fulfilled"
jbe@532	1431 THEN
jbe@532	1432 RETURN NULL;
jbe@532	1433 END IF;
jbe@532	1434 "same_pkey_v" := TRUE;
jbe@532	1435 END IF;
jbe@532	1436 END IF;
jbe@532	1437 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
jbe@532	1438 IF EXISTS (
jbe@532	1439 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
jbe@532	1440 FOR SHARE
jbe@532	1441 ) THEN
jbe@532	1442 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1443 WHERE "id" = OLD."initiative_id" FOR SHARE;
jbe@532	1444 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1445 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1446 SELECT * INTO "area_row" FROM "area"
jbe@532	1447 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1448 INSERT INTO "event" (
jbe@532	1449 "event", "member_id",
jbe@532	1450 "unit_id", "area_id", "issue_id", "state",
jbe@532	1451 "initiative_id", "suggestion_id",
jbe@532	1452 "boolean_value", "numeric_value"
jbe@532	1453 ) VALUES (
jbe@532	1454 'suggestion_rated', OLD."member_id",
jbe@532	1455 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1456 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1457 OLD."initiative_id", OLD."suggestion_id",
jbe@532	1458 NULL, 0
jbe@532	1459 );
jbe@532	1460 END IF;
jbe@532	1461 END IF;
jbe@532	1462 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1463 SELECT * INTO "initiative_row" FROM "initiative"
jbe@532	1464 WHERE "id" = NEW."initiative_id" FOR SHARE;
jbe@532	1465 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1466 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
jbe@532	1467 SELECT * INTO "area_row" FROM "area"
jbe@532	1468 WHERE "id" = "issue_row"."area_id" FOR SHARE;
jbe@532	1469 INSERT INTO "event" (
jbe@532	1470 "event", "member_id",
jbe@532	1471 "unit_id", "area_id", "issue_id", "state",
jbe@532	1472 "initiative_id", "suggestion_id",
jbe@532	1473 "boolean_value", "numeric_value"
jbe@532	1474 ) VALUES (
jbe@532	1475 'suggestion_rated', NEW."member_id",
jbe@532	1476 "area_row"."unit_id", "issue_row"."area_id",
jbe@532	1477 "initiative_row"."issue_id", "issue_row"."state",
jbe@532	1478 NEW."initiative_id", NEW."suggestion_id",
jbe@532	1479 NEW."fulfilled", NEW."degree"
jbe@532	1480 );
jbe@532	1481 END IF;
jbe@532	1482 RETURN NULL;
jbe@532	1483 END;
jbe@532	1484 $$;
jbe@532	1485
jbe@532	1486 CREATE TRIGGER "write_event_suggestion_rated"
jbe@532	1487 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1488 "write_event_suggestion_rated_trigger"();
jbe@532	1489
jbe@532	1490 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
jbe@532	1491 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
jbe@532	1492
jbe@532	1493
jbe@532	1494 CREATE FUNCTION "write_event_delegation_trigger"()
jbe@532	1495 RETURNS TRIGGER
jbe@532	1496 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1497 DECLARE
jbe@532	1498 "issue_row" "issue"%ROWTYPE;
jbe@532	1499 "area_row" "area"%ROWTYPE;
jbe@532	1500 BEGIN
jbe@532	1501 IF TG_OP = 'DELETE' THEN
jbe@532	1502 IF EXISTS (
jbe@532	1503 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
jbe@532	1504 ) AND (CASE OLD."scope"
jbe@532	1505 WHEN 'unit'::"delegation_scope" THEN EXISTS (
jbe@532	1506 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
jbe@532	1507 )
jbe@532	1508 WHEN 'area'::"delegation_scope" THEN EXISTS (
jbe@532	1509 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
jbe@532	1510 )
jbe@532	1511 WHEN 'issue'::"delegation_scope" THEN EXISTS (
jbe@532	1512 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
jbe@532	1513 )
jbe@532	1514 END) THEN
jbe@532	1515 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1516 WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@532	1517 SELECT * INTO "area_row" FROM "area"
jbe@532	1518 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
jbe@532	1519 FOR SHARE;
jbe@532	1520 INSERT INTO "event" (
jbe@532	1521 "event", "member_id", "scope",
jbe@532	1522 "unit_id", "area_id", "issue_id", "state",
jbe@532	1523 "boolean_value"
jbe@532	1524 ) VALUES (
jbe@532	1525 'delegation', OLD."truster_id", OLD."scope",
jbe@532	1526 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532	1527 OLD."issue_id", "issue_row"."state",
jbe@532	1528 FALSE
jbe@532	1529 );
jbe@532	1530 END IF;
jbe@532	1531 ELSE
jbe@532	1532 SELECT * INTO "issue_row" FROM "issue"
jbe@532	1533 WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@532	1534 SELECT * INTO "area_row" FROM "area"
jbe@532	1535 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
jbe@532	1536 FOR SHARE;
jbe@532	1537 INSERT INTO "event" (
jbe@532	1538 "event", "member_id", "other_member_id", "scope",
jbe@532	1539 "unit_id", "area_id", "issue_id", "state",
jbe@532	1540 "boolean_value"
jbe@532	1541 ) VALUES (
jbe@532	1542 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
jbe@532	1543 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
jbe@532	1544 NEW."issue_id", "issue_row"."state",
jbe@532	1545 TRUE
jbe@532	1546 );
jbe@532	1547 END IF;
jbe@532	1548 RETURN NULL;
jbe@532	1549 END;
jbe@532	1550 $$;
jbe@532	1551
jbe@532	1552 CREATE TRIGGER "write_event_delegation"
jbe@532	1553 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1554 "write_event_delegation_trigger"();
jbe@532	1555
jbe@532	1556 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
jbe@532	1557 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
jbe@532	1558
jbe@532	1559
jbe@532	1560 CREATE FUNCTION "write_event_contact_trigger"()
jbe@532	1561 RETURNS TRIGGER
jbe@532	1562 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1563 BEGIN
jbe@532	1564 IF TG_OP = 'UPDATE' THEN
jbe@532	1565 IF
jbe@532	1566 OLD."member_id" = NEW."member_id" AND
jbe@532	1567 OLD."other_member_id" = NEW."other_member_id" AND
jbe@532	1568 OLD."public" = NEW."public"
jbe@532	1569 THEN
jbe@532	1570 RETURN NULL;
jbe@532	1571 END IF;
jbe@532	1572 END IF;
jbe@532	1573 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@532	1574 IF OLD."public" THEN
jbe@532	1575 IF EXISTS (
jbe@532	1576 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
jbe@532	1577 FOR SHARE
jbe@532	1578 ) AND EXISTS (
jbe@532	1579 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
jbe@532	1580 FOR SHARE
jbe@532	1581 ) THEN
jbe@532	1582 INSERT INTO "event" (
jbe@532	1583 "event", "member_id", "other_member_id", "boolean_value"
jbe@532	1584 ) VALUES (
jbe@532	1585 'contact', OLD."member_id", OLD."other_member_id", FALSE
jbe@532	1586 );
jbe@532	1587 END IF;
jbe@532	1588 END IF;
jbe@532	1589 END IF;
jbe@532	1590 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@532	1591 IF NEW."public" THEN
jbe@532	1592 INSERT INTO "event" (
jbe@532	1593 "event", "member_id", "other_member_id", "boolean_value"
jbe@532	1594 ) VALUES (
jbe@532	1595 'contact', NEW."member_id", NEW."other_member_id", TRUE
jbe@532	1596 );
jbe@532	1597 END IF;
jbe@532	1598 END IF;
jbe@532	1599 RETURN NULL;
jbe@532	1600 END;
jbe@532	1601 $$;
jbe@532	1602
jbe@532	1603 CREATE TRIGGER "write_event_contact"
jbe@532	1604 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1605 "write_event_contact_trigger"();
jbe@532	1606
jbe@532	1607 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
jbe@532	1608 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
jbe@532	1609
jbe@532	1610
jbe@532	1611 CREATE FUNCTION "send_event_notify_trigger"()
jbe@532	1612 RETURNS TRIGGER
jbe@532	1613 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1614 BEGIN
jbe@532	1615 EXECUTE 'NOTIFY "event", ''' \|\| NEW."event" \|\| '''';
jbe@532	1616 RETURN NULL;
jbe@532	1617 END;
jbe@532	1618 $$;
jbe@532	1619
jbe@532	1620 CREATE TRIGGER "send_notify"
jbe@532	1621 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1622 "send_event_notify_trigger"();
jbe@532	1623
jbe@532	1624
jbe@532	1625 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
jbe@532	1626 RETURNS TRIGGER
jbe@532	1627 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1628 DECLARE
jbe@532	1629 "system_application_row" "system_application"%ROWTYPE;
jbe@532	1630 BEGIN
jbe@532	1631 IF OLD."system_application_id" NOTNULL THEN
jbe@532	1632 SELECT * FROM "system_application" INTO "system_application_row"
jbe@532	1633 WHERE "id" = OLD."system_application_id";
jbe@532	1634 DELETE FROM "token"
jbe@532	1635 WHERE "member_id" = OLD."member_id"
jbe@532	1636 AND "system_application_id" = OLD."system_application_id"
jbe@532	1637 AND NOT COALESCE(
jbe@532	1638 regexp_split_to_array("scope", E'\\s+') <@
jbe@532	1639 regexp_split_to_array(
jbe@532	1640 "system_application_row"."automatic_scope", E'\\s+'
jbe@532	1641 ),
jbe@532	1642 FALSE
jbe@532	1643 );
jbe@532	1644 END IF;
jbe@532	1645 RETURN OLD;
jbe@532	1646 END;
jbe@532	1647 $$;
jbe@532	1648
jbe@532	1649 CREATE TRIGGER "delete_extended_scope_tokens"
jbe@532	1650 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1651 "delete_extended_scope_tokens_trigger"();
jbe@532	1652
jbe@532	1653
jbe@532	1654 CREATE FUNCTION "detach_token_from_session_trigger"()
jbe@532	1655 RETURNS TRIGGER
jbe@532	1656 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1657 BEGIN
jbe@532	1658 UPDATE "token" SET "session_id" = NULL
jbe@532	1659 WHERE "session_id" = OLD."id";
jbe@532	1660 RETURN OLD;
jbe@532	1661 END;
jbe@532	1662 $$;
jbe@532	1663
jbe@532	1664 CREATE TRIGGER "detach_token_from_session"
jbe@532	1665 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1666 "detach_token_from_session_trigger"();
jbe@532	1667
jbe@532	1668
jbe@532	1669 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
jbe@532	1670 RETURNS TRIGGER
jbe@532	1671 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1672 BEGIN
jbe@532	1673 IF NEW."session_id" ISNULL THEN
jbe@532	1674 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
jbe@532	1675 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
jbe@532	1676 WHERE "element" LIKE '%_detached';
jbe@532	1677 END IF;
jbe@532	1678 RETURN NEW;
jbe@532	1679 END;
jbe@532	1680 $$;
jbe@532	1681
jbe@532	1682 CREATE TRIGGER "delete_non_detached_scope_with_session"
jbe@532	1683 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1684 "delete_non_detached_scope_with_session_trigger"();
jbe@532	1685
jbe@532	1686
jbe@532	1687 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
jbe@532	1688 RETURNS TRIGGER
jbe@532	1689 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1690 BEGIN
jbe@532	1691 IF NEW."scope" = '' THEN
jbe@532	1692 DELETE FROM "token" WHERE "id" = NEW."id";
jbe@532	1693 END IF;
jbe@532	1694 RETURN NULL;
jbe@532	1695 END;
jbe@532	1696 $$;
jbe@532	1697
jbe@532	1698 CREATE TRIGGER "delete_token_with_empty_scope"
jbe@532	1699 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1700 "delete_token_with_empty_scope_trigger"();
jbe@532	1701
jbe@532	1702
jbe@532	1703 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
jbe@532	1704 RETURNS TRIGGER
jbe@532	1705 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1706 BEGIN
jbe@532	1707 IF TG_OP = 'UPDATE' THEN
jbe@532	1708 IF
jbe@532	1709 OLD."snapshot_id" = NEW."snapshot_id" AND
jbe@532	1710 OLD."issue_id" = NEW."issue_id"
jbe@532	1711 THEN
jbe@532	1712 RETURN NULL;
jbe@532	1713 END IF;
jbe@532	1714 END IF;
jbe@532	1715 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
jbe@532	1716 RETURN NULL;
jbe@532	1717 END;
jbe@532	1718 $$;
jbe@532	1719
jbe@532	1720 CREATE TRIGGER "delete_snapshot_on_partial_delete"
jbe@532	1721 AFTER UPDATE OR DELETE ON "snapshot_issue"
jbe@532	1722 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1723 "delete_snapshot_on_partial_delete_trigger"();
jbe@532	1724
jbe@532	1725 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
jbe@532	1726 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	1727
jbe@532	1728
jbe@532	1729 CREATE FUNCTION "copy_current_draft_data"
jbe@532	1730 ("initiative_id_p" "initiative"."id"%TYPE )
jbe@532	1731 RETURNS VOID
jbe@532	1732 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1733 BEGIN
jbe@532	1734 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
jbe@532	1735 FOR UPDATE;
jbe@532	1736 UPDATE "initiative" SET
jbe@532	1737 "location" = "draft"."location",
jbe@532	1738 "draft_text_search_data" = "draft"."text_search_data"
jbe@532	1739 FROM "current_draft" AS "draft"
jbe@532	1740 WHERE "initiative"."id" = "initiative_id_p"
jbe@532	1741 AND "draft"."initiative_id" = "initiative_id_p";
jbe@532	1742 END;
jbe@532	1743 $$;
jbe@532	1744
jbe@532	1745 COMMENT ON FUNCTION "copy_current_draft_data"
jbe@532	1746 ( "initiative"."id"%TYPE )
jbe@532	1747 IS 'Helper function for function "copy_current_draft_data_trigger"';
jbe@532	1748
jbe@532	1749
jbe@532	1750 CREATE FUNCTION "copy_current_draft_data_trigger"()
jbe@532	1751 RETURNS TRIGGER
jbe@532	1752 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	1753 BEGIN
jbe@532	1754 IF TG_OP='DELETE' THEN
jbe@532	1755 PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532	1756 ELSE
jbe@532	1757 IF TG_OP='UPDATE' THEN
jbe@532	1758 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
jbe@532	1759 PERFORM "copy_current_draft_data"(OLD."initiative_id");
jbe@532	1760 END IF;
jbe@532	1761 END IF;
jbe@532	1762 PERFORM "copy_current_draft_data"(NEW."initiative_id");
jbe@532	1763 END IF;
jbe@532	1764 RETURN NULL;
jbe@532	1765 END;
jbe@532	1766 $$;
jbe@532	1767
jbe@532	1768 CREATE TRIGGER "copy_current_draft_data"
jbe@532	1769 AFTER INSERT OR UPDATE OR DELETE ON "draft"
jbe@532	1770 FOR EACH ROW EXECUTE PROCEDURE
jbe@532	1771 "copy_current_draft_data_trigger"();
jbe@532	1772
jbe@532	1773 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
jbe@532	1774 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
jbe@532	1775
jbe@532	1776
jbe@532	1777 CREATE VIEW "area_quorum" AS
jbe@532	1778 SELECT
jbe@532	1779 "area"."id" AS "area_id",
jbe@532	1780 ceil(
jbe@532	1781 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
jbe@532	1782 coalesce(
jbe@532	1783 ( SELECT sum(
jbe@532	1784 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
jbe@532	1785 extract(epoch from
jbe@532	1786 ("issue"."accepted"-"issue"."created") +
jbe@532	1787 "issue"."discussion_time" +
jbe@532	1788 "issue"."verification_time" +
jbe@532	1789 "issue"."voting_time"
jbe@532	1790 )::FLOAT8
jbe@532	1791 ) ^ "area"."quorum_exponent"::FLOAT8
jbe@532	1792 )
jbe@532	1793 FROM "issue" JOIN "policy"
jbe@532	1794 ON "issue"."policy_id" = "policy"."id"
jbe@532	1795 WHERE "issue"."area_id" = "area"."id"
jbe@532	1796 AND "issue"."accepted" NOTNULL
jbe@532	1797 AND "issue"."closed" ISNULL
jbe@532	1798 AND "policy"."polling" = FALSE
jbe@532	1799 )::FLOAT8, 0::FLOAT8
jbe@532	1800 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
jbe@532	1801 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
jbe@532	1802 SELECT "snapshot"."population"
jbe@532	1803 FROM "snapshot"
jbe@532	1804 WHERE "snapshot"."area_id" = "area"."id"
jbe@532	1805 AND "snapshot"."issue_id" ISNULL
jbe@532	1806 ORDER BY "snapshot"."id" DESC
jbe@532	1807 LIMIT 1
jbe@532	1808 ) END / coalesce("area"."quorum_den", 1)
jbe@532	1809
jbe@532	1810 )::INT4 AS "issue_quorum"
jbe@532	1811 FROM "area";
jbe@532	1812
jbe@532	1813 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
jbe@532	1814
jbe@532	1815
jbe@532	1816 CREATE VIEW "area_with_unaccepted_issues" AS
jbe@532	1817 SELECT DISTINCT ON ("area"."id") "area".*
jbe@532	1818 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
jbe@532	1819 WHERE "issue"."state" = 'admission';
jbe@532	1820
jbe@532	1821 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
jbe@532	1822
jbe@532	1823
jbe@532	1824 DROP VIEW "area_member_count";
jbe@532	1825
jbe@532	1826
jbe@532	1827 DROP TABLE "membership";
jbe@532	1828
jbe@532	1829
jbe@532	1830 DROP FUNCTION "membership_weight"
jbe@532	1831 ( "area_id_p" "area"."id"%TYPE,
jbe@532	1832 "member_id_p" "member"."id"%TYPE );
jbe@532	1833
jbe@532	1834
jbe@532	1835 DROP FUNCTION "membership_weight_with_skipping"
jbe@532	1836 ( "area_id_p" "area"."id"%TYPE,
jbe@532	1837 "member_id_p" "member"."id"%TYPE,
jbe@532	1838 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
jbe@532	1839
jbe@532	1840
jbe@532	1841 CREATE OR REPLACE VIEW "issue_delegation" AS
jbe@532	1842 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@532	1843 "issue"."id" AS "issue_id",
jbe@532	1844 "delegation"."id",
jbe@532	1845 "delegation"."truster_id",
jbe@532	1846 "delegation"."trustee_id",
jbe@532	1847 "delegation"."scope"
jbe@532	1848 FROM "issue"
jbe@532	1849 JOIN "area"
jbe@532	1850 ON "area"."id" = "issue"."area_id"
jbe@532	1851 JOIN "delegation"
jbe@532	1852 ON "delegation"."unit_id" = "area"."unit_id"
jbe@532	1853 OR "delegation"."area_id" = "area"."id"
jbe@532	1854 OR "delegation"."issue_id" = "issue"."id"
jbe@532	1855 JOIN "member"
jbe@532	1856 ON "delegation"."truster_id" = "member"."id"
jbe@532	1857 JOIN "privilege"
jbe@532	1858 ON "area"."unit_id" = "privilege"."unit_id"
jbe@532	1859 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@532	1860 WHERE "member"."active" AND "privilege"."voting_right"
jbe@532	1861 ORDER BY
jbe@532	1862 "issue"."id",
jbe@532	1863 "delegation"."truster_id",
jbe@532	1864 "delegation"."scope" DESC;
jbe@532	1865
jbe@532	1866
jbe@532	1867 CREATE VIEW "unit_member" AS
jbe@532	1868 SELECT
jbe@532	1869 "unit"."id" AS "unit_id",
jbe@532	1870 "member"."id" AS "member_id"
jbe@532	1871 FROM "privilege"
jbe@532	1872 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
jbe@532	1873 JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@532	1874 WHERE "privilege"."voting_right" AND "member"."active";
jbe@532	1875
jbe@532	1876 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
jbe@532	1877
jbe@532	1878
jbe@532	1879 CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@532	1880 SELECT
jbe@532	1881 "unit"."id" AS "unit_id",
jbe@532	1882 count("unit_member"."member_id") AS "member_count"
jbe@532	1883 FROM "unit" LEFT JOIN "unit_member"
jbe@532	1884 ON "unit"."id" = "unit_member"."unit_id"
jbe@532	1885 GROUP BY "unit"."id";
jbe@532	1886
jbe@532	1887 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@532	1888
jbe@532	1889
jbe@532	1890 CREATE OR REPLACE VIEW "opening_draft" AS
jbe@532	1891 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532	1892 ORDER BY "initiative_id", "id";
jbe@532	1893
jbe@532	1894
jbe@532	1895 CREATE OR REPLACE VIEW "current_draft" AS
jbe@532	1896 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@532	1897 ORDER BY "initiative_id", "id" DESC;
jbe@532	1898
jbe@532	1899
jbe@532	1900 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
jbe@532	1901 SELECT
jbe@532	1902 "area"."unit_id",
jbe@532	1903 "issue"."area_id",
jbe@532	1904 "issue"."id" AS "issue_id",
jbe@532	1905 "supporter"."member_id",
jbe@532	1906 "direct_interest_snapshot"."weight"
jbe@532	1907 FROM "issue"
jbe@532	1908 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@532	1909 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@532	1910 JOIN "direct_interest_snapshot"
jbe@532	1911 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532	1912 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532	1913 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@532	1914 WHERE "issue"."state" = 'admission'::"issue_state";
jbe@532	1915
jbe@532	1916
jbe@532	1917 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
jbe@532	1918 SELECT
jbe@532	1919 "opinion"."initiative_id",
jbe@532	1920 "opinion"."member_id",
jbe@532	1921 "direct_interest_snapshot"."weight",
jbe@532	1922 CASE WHEN
jbe@532	1923 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
jbe@532	1924 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
jbe@532	1925 THEN 1 ELSE
jbe@532	1926 CASE WHEN
jbe@532	1927 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
jbe@532	1928 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
jbe@532	1929 THEN 2 ELSE
jbe@532	1930 CASE WHEN
jbe@532	1931 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
jbe@532	1932 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
jbe@532	1933 THEN 3 ELSE 4 END
jbe@532	1934 END
jbe@532	1935 END AS "preference",
jbe@532	1936 "opinion"."suggestion_id"
jbe@532	1937 FROM "opinion"
jbe@532	1938 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
jbe@532	1939 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@532	1940 JOIN "direct_interest_snapshot"
jbe@532	1941 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
jbe@532	1942 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@532	1943 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
jbe@532	1944
jbe@532	1945
jbe@532	1946 CREATE VIEW "expired_session" AS
jbe@532	1947 SELECT * FROM "session" WHERE now() > "expiry";
jbe@532	1948
jbe@532	1949 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@532	1950 DELETE FROM "session" WHERE "id" = OLD."id";
jbe@532	1951
jbe@532	1952 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@532	1953 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	1954
jbe@532	1955
jbe@532	1956 CREATE VIEW "expired_token" AS
jbe@532	1957 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
jbe@532	1958 "token_type" = 'authorization' AND "used" AND EXISTS (
jbe@532	1959 SELECT NULL FROM "token" AS "other"
jbe@532	1960 WHERE "other"."authorization_token_id" = "id" ) );
jbe@532	1961
jbe@532	1962 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
jbe@532	1963 DELETE FROM "token" WHERE "id" = OLD."id";
jbe@532	1964
jbe@532	1965 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	1966
jbe@532	1967
jbe@532	1968 CREATE VIEW "unused_snapshot" AS
jbe@532	1969 SELECT "snapshot".* FROM "snapshot"
jbe@532	1970 LEFT JOIN "issue"
jbe@532	1971 ON "snapshot"."id" = "issue"."latest_snapshot_id"
jbe@532	1972 OR "snapshot"."id" = "issue"."admission_snapshot_id"
jbe@532	1973 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
jbe@532	1974 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
jbe@532	1975 WHERE "issue"."id" ISNULL;
jbe@532	1976
jbe@532	1977 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
jbe@532	1978 DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532	1979
jbe@532	1980 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	1981
jbe@532	1982
jbe@532	1983 CREATE VIEW "expired_snapshot" AS
jbe@532	1984 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
jbe@532	1985 WHERE "unused_snapshot"."calculated" <
jbe@532	1986 now() - "system_setting"."snapshot_retention";
jbe@532	1987
jbe@532	1988 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
jbe@532	1989 DELETE FROM "snapshot" WHERE "id" = OLD."id";
jbe@532	1990
jbe@532	1991 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
jbe@532	1992
jbe@532	1993
jbe@532	1994 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	1995
jbe@532	1996
jbe@532	1997 CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@532	1998 ( "member_id_p" "member"."id"%TYPE,
jbe@532	1999 "unit_id_p" "unit"."id"%TYPE,
jbe@532	2000 "area_id_p" "area"."id"%TYPE,
jbe@532	2001 "issue_id_p" "issue"."id"%TYPE,
jbe@532	2002 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@532	2003 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@532	2004 RETURNS SETOF "delegation_chain_row"
jbe@532	2005 LANGUAGE 'plpgsql' STABLE AS $$
jbe@532	2006 DECLARE
jbe@532	2007 "scope_v" "delegation_scope";
jbe@532	2008 "unit_id_v" "unit"."id"%TYPE;
jbe@532	2009 "area_id_v" "area"."id"%TYPE;
jbe@532	2010 "issue_row" "issue"%ROWTYPE;
jbe@532	2011 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@532	2012 "loop_member_id_v" "member"."id"%TYPE;
jbe@532	2013 "output_row" "delegation_chain_row";
jbe@532	2014 "output_rows" "delegation_chain_row"[];
jbe@532	2015 "simulate_v" BOOLEAN;
jbe@532	2016 "simulate_here_v" BOOLEAN;
jbe@532	2017 "delegation_row" "delegation"%ROWTYPE;
jbe@532	2018 "row_count" INT4;
jbe@532	2019 "i" INT4;
jbe@532	2020 "loop_v" BOOLEAN;
jbe@532	2021 BEGIN
jbe@532	2022 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@532	2023 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@532	2024 END IF;
jbe@532	2025 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@532	2026 "simulate_v" := TRUE;
jbe@532	2027 ELSE
jbe@532	2028 "simulate_v" := FALSE;
jbe@532	2029 END IF;
jbe@532	2030 IF
jbe@532	2031 "unit_id_p" NOTNULL AND
jbe@532	2032 "area_id_p" ISNULL AND
jbe@532	2033 "issue_id_p" ISNULL
jbe@532	2034 THEN
jbe@532	2035 "scope_v" := 'unit';
jbe@532	2036 "unit_id_v" := "unit_id_p";
jbe@532	2037 ELSIF
jbe@532	2038 "unit_id_p" ISNULL AND
jbe@532	2039 "area_id_p" NOTNULL AND
jbe@532	2040 "issue_id_p" ISNULL
jbe@532	2041 THEN
jbe@532	2042 "scope_v" := 'area';
jbe@532	2043 "area_id_v" := "area_id_p";
jbe@532	2044 SELECT "unit_id" INTO "unit_id_v"
jbe@532	2045 FROM "area" WHERE "id" = "area_id_v";
jbe@532	2046 ELSIF
jbe@532	2047 "unit_id_p" ISNULL AND
jbe@532	2048 "area_id_p" ISNULL AND
jbe@532	2049 "issue_id_p" NOTNULL
jbe@532	2050 THEN
jbe@532	2051 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2052 IF "issue_row"."id" ISNULL THEN
jbe@532	2053 RETURN;
jbe@532	2054 END IF;
jbe@532	2055 IF "issue_row"."closed" NOTNULL THEN
jbe@532	2056 IF "simulate_v" THEN
jbe@532	2057 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@532	2058 END IF;
jbe@532	2059 FOR "output_row" IN
jbe@532	2060 SELECT * FROM
jbe@532	2061 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@532	2062 LOOP
jbe@532	2063 RETURN NEXT "output_row";
jbe@532	2064 END LOOP;
jbe@532	2065 RETURN;
jbe@532	2066 END IF;
jbe@532	2067 "scope_v" := 'issue';
jbe@532	2068 SELECT "area_id" INTO "area_id_v"
jbe@532	2069 FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2070 SELECT "unit_id" INTO "unit_id_v"
jbe@532	2071 FROM "area" WHERE "id" = "area_id_v";
jbe@532	2072 ELSE
jbe@532	2073 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@532	2074 END IF;
jbe@532	2075 "visited_member_ids" := '{}';
jbe@532	2076 "loop_member_id_v" := NULL;
jbe@532	2077 "output_rows" := '{}';
jbe@532	2078 "output_row"."index" := 0;
jbe@532	2079 "output_row"."member_id" := "member_id_p";
jbe@532	2080 "output_row"."member_valid" := TRUE;
jbe@532	2081 "output_row"."participation" := FALSE;
jbe@532	2082 "output_row"."overridden" := FALSE;
jbe@532	2083 "output_row"."disabled_out" := FALSE;
jbe@532	2084 "output_row"."scope_out" := NULL;
jbe@532	2085 LOOP
jbe@532	2086 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@532	2087 "loop_member_id_v" := "output_row"."member_id";
jbe@532	2088 ELSE
jbe@532	2089 "visited_member_ids" :=
jbe@532	2090 "visited_member_ids" \|\| "output_row"."member_id";
jbe@532	2091 END IF;
jbe@532	2092 IF "output_row"."participation" ISNULL THEN
jbe@532	2093 "output_row"."overridden" := NULL;
jbe@532	2094 ELSIF "output_row"."participation" THEN
jbe@532	2095 "output_row"."overridden" := TRUE;
jbe@532	2096 END IF;
jbe@532	2097 "output_row"."scope_in" := "output_row"."scope_out";
jbe@532	2098 "output_row"."member_valid" := EXISTS (
jbe@532	2099 SELECT NULL FROM "member" JOIN "privilege"
jbe@532	2100 ON "privilege"."member_id" = "member"."id"
jbe@532	2101 AND "privilege"."unit_id" = "unit_id_v"
jbe@532	2102 WHERE "id" = "output_row"."member_id"
jbe@532	2103 AND "member"."active" AND "privilege"."voting_right"
jbe@532	2104 );
jbe@532	2105 "simulate_here_v" := (
jbe@532	2106 "simulate_v" AND
jbe@532	2107 "output_row"."member_id" = "member_id_p"
jbe@532	2108 );
jbe@532	2109 "delegation_row" := ROW(NULL);
jbe@532	2110 IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@532	2111 IF "scope_v" = 'unit' THEN
jbe@532	2112 IF NOT "simulate_here_v" THEN
jbe@532	2113 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2114 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2115 AND "unit_id" = "unit_id_v";
jbe@532	2116 END IF;
jbe@532	2117 ELSIF "scope_v" = 'area' THEN
jbe@532	2118 IF "simulate_here_v" THEN
jbe@532	2119 IF "simulate_trustee_id_p" ISNULL 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 ELSE
jbe@532	2125 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2126 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2127 AND (
jbe@532	2128 "unit_id" = "unit_id_v" OR
jbe@532	2129 "area_id" = "area_id_v"
jbe@532	2130 )
jbe@532	2131 ORDER BY "scope" DESC;
jbe@532	2132 END IF;
jbe@532	2133 ELSIF "scope_v" = 'issue' THEN
jbe@532	2134 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@532	2135 "output_row"."participation" := EXISTS (
jbe@532	2136 SELECT NULL FROM "interest"
jbe@532	2137 WHERE "issue_id" = "issue_id_p"
jbe@532	2138 AND "member_id" = "output_row"."member_id"
jbe@532	2139 );
jbe@532	2140 ELSE
jbe@532	2141 IF "output_row"."member_id" = "member_id_p" THEN
jbe@532	2142 "output_row"."participation" := EXISTS (
jbe@532	2143 SELECT NULL FROM "direct_voter"
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 "output_row"."participation" := NULL;
jbe@532	2149 END IF;
jbe@532	2150 END IF;
jbe@532	2151 IF "simulate_here_v" THEN
jbe@532	2152 IF "simulate_trustee_id_p" ISNULL THEN
jbe@532	2153 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2154 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2155 AND (
jbe@532	2156 "unit_id" = "unit_id_v" OR
jbe@532	2157 "area_id" = "area_id_v"
jbe@532	2158 )
jbe@532	2159 ORDER BY "scope" DESC;
jbe@532	2160 END IF;
jbe@532	2161 ELSE
jbe@532	2162 SELECT * INTO "delegation_row" FROM "delegation"
jbe@532	2163 WHERE "truster_id" = "output_row"."member_id"
jbe@532	2164 AND (
jbe@532	2165 "unit_id" = "unit_id_v" OR
jbe@532	2166 "area_id" = "area_id_v" OR
jbe@532	2167 "issue_id" = "issue_id_p"
jbe@532	2168 )
jbe@532	2169 ORDER BY "scope" DESC;
jbe@532	2170 END IF;
jbe@532	2171 END IF;
jbe@532	2172 ELSE
jbe@532	2173 "output_row"."participation" := FALSE;
jbe@532	2174 END IF;
jbe@532	2175 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@532	2176 "output_row"."scope_out" := "scope_v";
jbe@532	2177 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2178 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@532	2179 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@532	2180 "output_row"."scope_out" := "delegation_row"."scope";
jbe@532	2181 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2182 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@532	2183 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@532	2184 "output_row"."scope_out" := "delegation_row"."scope";
jbe@532	2185 "output_row"."disabled_out" := TRUE;
jbe@532	2186 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2187 EXIT;
jbe@532	2188 ELSE
jbe@532	2189 "output_row"."scope_out" := NULL;
jbe@532	2190 "output_rows" := "output_rows" \|\| "output_row";
jbe@532	2191 EXIT;
jbe@532	2192 END IF;
jbe@532	2193 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@532	2194 "output_row"."index" := "output_row"."index" + 1;
jbe@532	2195 END LOOP;
jbe@532	2196 "row_count" := array_upper("output_rows", 1);
jbe@532	2197 "i" := 1;
jbe@532	2198 "loop_v" := FALSE;
jbe@532	2199 LOOP
jbe@532	2200 "output_row" := "output_rows"["i"];
jbe@532	2201 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@532	2202 IF "loop_v" THEN
jbe@532	2203 IF "i" + 1 = "row_count" THEN
jbe@532	2204 "output_row"."loop" := 'last';
jbe@532	2205 ELSIF "i" = "row_count" THEN
jbe@532	2206 "output_row"."loop" := 'repetition';
jbe@532	2207 ELSE
jbe@532	2208 "output_row"."loop" := 'intermediate';
jbe@532	2209 END IF;
jbe@532	2210 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@532	2211 "output_row"."loop" := 'first';
jbe@532	2212 "loop_v" := TRUE;
jbe@532	2213 END IF;
jbe@532	2214 IF "scope_v" = 'unit' THEN
jbe@532	2215 "output_row"."participation" := NULL;
jbe@532	2216 END IF;
jbe@532	2217 RETURN NEXT "output_row";
jbe@532	2218 "i" := "i" + 1;
jbe@532	2219 END LOOP;
jbe@532	2220 RETURN;
jbe@532	2221 END;
jbe@532	2222 $$;
jbe@532	2223
jbe@532	2224
jbe@532	2225 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
jbe@532	2226 ( "recipient_id_p" "member"."id"%TYPE )
jbe@532	2227 RETURNS SETOF "initiative_for_notification"
jbe@532	2228 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2229 DECLARE
jbe@532	2230 "result_row" "initiative_for_notification"%ROWTYPE;
jbe@532	2231 "last_draft_id_v" "draft"."id"%TYPE;
jbe@532	2232 "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@532	2233 BEGIN
jbe@532	2234 PERFORM "require_transaction_isolation"();
jbe@532	2235 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@532	2236 FOR "result_row" IN
jbe@532	2237 SELECT * FROM "initiative_for_notification"
jbe@532	2238 WHERE "recipient_id" = "recipient_id_p"
jbe@532	2239 LOOP
jbe@532	2240 SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@532	2241 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@532	2242 ORDER BY "id" DESC LIMIT 1;
jbe@532	2243 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@532	2244 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@532	2245 ORDER BY "id" DESC LIMIT 1;
jbe@532	2246 INSERT INTO "notification_initiative_sent"
jbe@532	2247 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@532	2248 VALUES (
jbe@532	2249 "recipient_id_p",
jbe@532	2250 "result_row"."initiative_id",
jbe@532	2251 "last_draft_id_v",
jbe@532	2252 "last_suggestion_id_v" )
jbe@532	2253 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@532	2254 "last_draft_id" = "last_draft_id_v",
jbe@532	2255 "last_suggestion_id" = "last_suggestion_id_v";
jbe@532	2256 RETURN NEXT "result_row";
jbe@532	2257 END LOOP;
jbe@532	2258 DELETE FROM "notification_initiative_sent"
jbe@532	2259 USING "initiative", "issue"
jbe@532	2260 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
jbe@532	2261 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
jbe@532	2262 AND "issue"."id" = "initiative"."issue_id"
jbe@532	2263 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@532	2264 UPDATE "member" SET
jbe@532	2265 "notification_counter" = "notification_counter" + 1,
jbe@532	2266 "notification_sent" = now()
jbe@532	2267 WHERE "id" = "recipient_id_p";
jbe@532	2268 RETURN;
jbe@532	2269 END;
jbe@532	2270 $$;
jbe@532	2271
jbe@532	2272
jbe@532	2273 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@532	2274 RETURNS VOID
jbe@532	2275 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2276 BEGIN
jbe@532	2277 PERFORM "require_transaction_isolation"();
jbe@532	2278 DELETE FROM "member_count";
jbe@532	2279 INSERT INTO "member_count" ("total_count")
jbe@532	2280 SELECT "total_count" FROM "member_count_view";
jbe@532	2281 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@532	2282 FROM "unit_member_count" AS "view"
jbe@532	2283 WHERE "view"."unit_id" = "unit"."id";
jbe@532	2284 RETURN;
jbe@532	2285 END;
jbe@532	2286 $$;
jbe@532	2287
jbe@532	2288 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	2289
jbe@532	2290
jbe@532	2291 CREATE FUNCTION "calculate_area_quorum"()
jbe@532	2292 RETURNS VOID
jbe@532	2293 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2294 BEGIN
jbe@532	2295 PERFORM "dont_require_transaction_isolation"();
jbe@532	2296 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532	2297 FROM "area_quorum" AS "view"
jbe@532	2298 WHERE "view"."area_id" = "area"."id";
jbe@532	2299 RETURN;
jbe@532	2300 END;
jbe@532	2301 $$;
jbe@532	2302
jbe@532	2303 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
jbe@532	2304
jbe@532	2305
jbe@532	2306 DROP VIEW "remaining_harmonic_initiative_weight_summands";
jbe@532	2307 DROP VIEW "remaining_harmonic_supporter_weight";
jbe@532	2308
jbe@532	2309
jbe@532	2310 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@532	2311 SELECT
jbe@532	2312 "direct_interest_snapshot"."snapshot_id",
jbe@532	2313 "direct_interest_snapshot"."issue_id",
jbe@532	2314 "direct_interest_snapshot"."member_id",
jbe@532	2315 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@532	2316 count("initiative"."id") AS "weight_den"
jbe@532	2317 FROM "issue"
jbe@532	2318 JOIN "direct_interest_snapshot"
jbe@532	2319 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
jbe@532	2320 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@532	2321 JOIN "initiative"
jbe@532	2322 ON "issue"."id" = "initiative"."issue_id"
jbe@532	2323 AND "initiative"."harmonic_weight" ISNULL
jbe@532	2324 JOIN "direct_supporter_snapshot"
jbe@532	2325 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532	2326 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532	2327 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532	2328 AND (
jbe@532	2329 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532	2330 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532	2331 )
jbe@532	2332 GROUP BY
jbe@532	2333 "direct_interest_snapshot"."snapshot_id",
jbe@532	2334 "direct_interest_snapshot"."issue_id",
jbe@532	2335 "direct_interest_snapshot"."member_id",
jbe@532	2336 "direct_interest_snapshot"."weight";
jbe@532	2337
jbe@532	2338
jbe@532	2339 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@532	2340 SELECT
jbe@532	2341 "initiative"."issue_id",
jbe@532	2342 "initiative"."id" AS "initiative_id",
jbe@532	2343 "initiative"."admitted",
jbe@532	2344 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@532	2345 "remaining_harmonic_supporter_weight"."weight_den"
jbe@532	2346 FROM "remaining_harmonic_supporter_weight"
jbe@532	2347 JOIN "initiative"
jbe@532	2348 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@532	2349 AND "initiative"."harmonic_weight" ISNULL
jbe@532	2350 JOIN "direct_supporter_snapshot"
jbe@532	2351 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
jbe@532	2352 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@532	2353 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@532	2354 AND (
jbe@532	2355 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@532	2356 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@532	2357 )
jbe@532	2358 GROUP BY
jbe@532	2359 "initiative"."issue_id",
jbe@532	2360 "initiative"."id",
jbe@532	2361 "initiative"."admitted",
jbe@532	2362 "remaining_harmonic_supporter_weight"."weight_den";
jbe@532	2363
jbe@532	2364
jbe@532	2365 DROP FUNCTION "create_population_snapshot"
jbe@532	2366 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2367
jbe@532	2368
jbe@532	2369 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@532	2370 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2371 "member_id_p" "member"."id"%TYPE,
jbe@532	2372 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
jbe@532	2373
jbe@532	2374
jbe@532	2375 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@532	2376 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2377 "member_id_p" "member"."id"%TYPE,
jbe@532	2378 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
jbe@532	2379
jbe@532	2380
jbe@532	2381 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532	2382 ( "snapshot_id_p" "snapshot"."id"%TYPE,
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 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@532	2387 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2388 DECLARE
jbe@532	2389 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@532	2390 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@532	2391 "weight_v" INT4;
jbe@532	2392 "sub_weight_v" INT4;
jbe@532	2393 BEGIN
jbe@532	2394 PERFORM "require_transaction_isolation"();
jbe@532	2395 "weight_v" := 0;
jbe@532	2396 FOR "issue_delegation_row" IN
jbe@532	2397 SELECT * FROM "issue_delegation"
jbe@532	2398 WHERE "trustee_id" = "member_id_p"
jbe@532	2399 AND "issue_id" = "issue_id_p"
jbe@532	2400 LOOP
jbe@532	2401 IF NOT EXISTS (
jbe@532	2402 SELECT NULL FROM "direct_interest_snapshot"
jbe@532	2403 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2404 AND "issue_id" = "issue_id_p"
jbe@532	2405 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532	2406 ) AND NOT EXISTS (
jbe@532	2407 SELECT NULL FROM "delegating_interest_snapshot"
jbe@532	2408 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2409 AND "issue_id" = "issue_id_p"
jbe@532	2410 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@532	2411 ) THEN
jbe@532	2412 "delegate_member_ids_v" :=
jbe@532	2413 "member_id_p" \|\| "delegate_member_ids_p";
jbe@532	2414 INSERT INTO "delegating_interest_snapshot" (
jbe@532	2415 "snapshot_id",
jbe@532	2416 "issue_id",
jbe@532	2417 "member_id",
jbe@532	2418 "scope",
jbe@532	2419 "delegate_member_ids"
jbe@532	2420 ) VALUES (
jbe@532	2421 "snapshot_id_p",
jbe@532	2422 "issue_id_p",
jbe@532	2423 "issue_delegation_row"."truster_id",
jbe@532	2424 "issue_delegation_row"."scope",
jbe@532	2425 "delegate_member_ids_v"
jbe@532	2426 );
jbe@532	2427 "sub_weight_v" := 1 +
jbe@532	2428 "weight_of_added_delegations_for_snapshot"(
jbe@532	2429 "snapshot_id_p",
jbe@532	2430 "issue_id_p",
jbe@532	2431 "issue_delegation_row"."truster_id",
jbe@532	2432 "delegate_member_ids_v"
jbe@532	2433 );
jbe@532	2434 UPDATE "delegating_interest_snapshot"
jbe@532	2435 SET "weight" = "sub_weight_v"
jbe@532	2436 WHERE "snapshot_id" = "snapshot_id_p"
jbe@532	2437 AND "issue_id" = "issue_id_p"
jbe@532	2438 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@532	2439 "weight_v" := "weight_v" + "sub_weight_v";
jbe@532	2440 END IF;
jbe@532	2441 END LOOP;
jbe@532	2442 RETURN "weight_v";
jbe@532	2443 END;
jbe@532	2444 $$;
jbe@532	2445
jbe@532	2446 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@532	2447 ( "snapshot"."id"%TYPE,
jbe@532	2448 "issue"."id"%TYPE,
jbe@532	2449 "member"."id"%TYPE,
jbe@532	2450 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@532	2451 IS 'Helper function for "fill_snapshot" function';
jbe@532	2452
jbe@532	2453
jbe@532	2454 DROP FUNCTION "create_interest_snapshot"
jbe@532	2455 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2456
jbe@532	2457
jbe@532	2458 DROP FUNCTION "create_snapshot"
jbe@532	2459 ( "issue_id_p" "issue"."id"%TYPE );
jbe@532	2460
jbe@532	2461
jbe@532	2462 CREATE FUNCTION "take_snapshot"
jbe@532	2463 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2464 "area_id_p" "area"."id"%TYPE = NULL )
jbe@532	2465 RETURNS "snapshot"."id"%TYPE
jbe@532	2466 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2467 DECLARE
jbe@532	2468 "area_id_v" "area"."id"%TYPE;
jbe@532	2469 "unit_id_v" "unit"."id"%TYPE;
jbe@532	2470 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	2471 "issue_id_v" "issue"."id"%TYPE;
jbe@532	2472 "member_id_v" "member"."id"%TYPE;
jbe@532	2473 BEGIN
jbe@532	2474 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@532	2475 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@532	2476 END IF;
jbe@532	2477 PERFORM "require_transaction_isolation"();
jbe@532	2478 IF "issue_id_p" ISNULL THEN
jbe@532	2479 "area_id_v" := "area_id_p";
jbe@532	2480 ELSE
jbe@532	2481 SELECT "area_id" INTO "area_id_v"
jbe@532	2482 FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2483 END IF;
jbe@532	2484 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
jbe@532	2485 INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@532	2486 VALUES ("area_id_v", "issue_id_p")
jbe@532	2487 RETURNING "id" INTO "snapshot_id_v";
jbe@532	2488 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	2489 SELECT "snapshot_id_v", "member_id"
jbe@532	2490 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
jbe@532	2491 UPDATE "snapshot" SET
jbe@532	2492 "population" = (
jbe@532	2493 SELECT count(1) FROM "snapshot_population"
jbe@532	2494 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2495 ) WHERE "id" = "snapshot_id_v";
jbe@532	2496 FOR "issue_id_v" IN
jbe@532	2497 SELECT "id" FROM "issue"
jbe@532	2498 WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@532	2499 "area_id" = "area_id_p" AND
jbe@532	2500 "state" = 'admission'
jbe@532	2501 ELSE
jbe@532	2502 "id" = "issue_id_p"
jbe@532	2503 END
jbe@532	2504 LOOP
jbe@532	2505 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532	2506 VALUES ("snapshot_id_v", "issue_id_v");
jbe@532	2507 INSERT INTO "direct_interest_snapshot"
jbe@532	2508 ("snapshot_id", "issue_id", "member_id")
jbe@532	2509 SELECT
jbe@532	2510 "snapshot_id_v" AS "snapshot_id",
jbe@532	2511 "issue_id_v" AS "issue_id",
jbe@532	2512 "member"."id" AS "member_id"
jbe@532	2513 FROM "issue"
jbe@532	2514 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532	2515 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@532	2516 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@532	2517 JOIN "privilege"
jbe@532	2518 ON "privilege"."unit_id" = "area"."unit_id"
jbe@532	2519 AND "privilege"."member_id" = "member"."id"
jbe@532	2520 WHERE "issue"."id" = "issue_id_v"
jbe@532	2521 AND "member"."active" AND "privilege"."voting_right";
jbe@532	2522 FOR "member_id_v" IN
jbe@532	2523 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@532	2524 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2525 AND "issue_id" = "issue_id_v"
jbe@532	2526 LOOP
jbe@532	2527 UPDATE "direct_interest_snapshot" SET
jbe@532	2528 "weight" = 1 +
jbe@532	2529 "weight_of_added_delegations_for_snapshot"(
jbe@532	2530 "snapshot_id_v",
jbe@532	2531 "issue_id_v",
jbe@532	2532 "member_id_v",
jbe@532	2533 '{}'
jbe@532	2534 )
jbe@532	2535 WHERE "snapshot_id" = "snapshot_id_v"
jbe@532	2536 AND "issue_id" = "issue_id_v"
jbe@532	2537 AND "member_id" = "member_id_v";
jbe@532	2538 END LOOP;
jbe@532	2539 INSERT INTO "direct_supporter_snapshot"
jbe@532	2540 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@532	2541 "draft_id", "informed", "satisfied" )
jbe@532	2542 SELECT
jbe@532	2543 "snapshot_id_v" AS "snapshot_id",
jbe@532	2544 "issue_id_v" AS "issue_id",
jbe@532	2545 "initiative"."id" AS "initiative_id",
jbe@532	2546 "supporter"."member_id" AS "member_id",
jbe@532	2547 "supporter"."draft_id" AS "draft_id",
jbe@532	2548 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@532	2549 NOT EXISTS (
jbe@532	2550 SELECT NULL FROM "critical_opinion"
jbe@532	2551 WHERE "initiative_id" = "initiative"."id"
jbe@532	2552 AND "member_id" = "supporter"."member_id"
jbe@532	2553 ) AS "satisfied"
jbe@532	2554 FROM "initiative"
jbe@532	2555 JOIN "supporter"
jbe@532	2556 ON "supporter"."initiative_id" = "initiative"."id"
jbe@532	2557 JOIN "current_draft"
jbe@532	2558 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@532	2559 JOIN "direct_interest_snapshot"
jbe@532	2560 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@532	2561 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@532	2562 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@532	2563 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532	2564 DELETE FROM "temporary_suggestion_counts";
jbe@532	2565 INSERT INTO "temporary_suggestion_counts"
jbe@532	2566 ( "id",
jbe@532	2567 "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@532	2568 "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@532	2569 "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@532	2570 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@532	2571 SELECT
jbe@532	2572 "suggestion"."id",
jbe@532	2573 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2574 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2575 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2576 AND "di"."issue_id" = "issue_id_v"
jbe@532	2577 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2578 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2579 AND "opinion"."degree" = -2
jbe@532	2580 AND "opinion"."fulfilled" = FALSE
jbe@532	2581 ) AS "minus2_unfulfilled_count",
jbe@532	2582 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2583 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2584 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2585 AND "di"."issue_id" = "issue_id_v"
jbe@532	2586 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2587 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2588 AND "opinion"."degree" = -2
jbe@532	2589 AND "opinion"."fulfilled" = TRUE
jbe@532	2590 ) AS "minus2_fulfilled_count",
jbe@532	2591 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2592 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2593 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2594 AND "di"."issue_id" = "issue_id_v"
jbe@532	2595 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2596 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2597 AND "opinion"."degree" = -1
jbe@532	2598 AND "opinion"."fulfilled" = FALSE
jbe@532	2599 ) AS "minus1_unfulfilled_count",
jbe@532	2600 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2601 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2602 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2603 AND "di"."issue_id" = "issue_id_v"
jbe@532	2604 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2605 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2606 AND "opinion"."degree" = -1
jbe@532	2607 AND "opinion"."fulfilled" = TRUE
jbe@532	2608 ) AS "minus1_fulfilled_count",
jbe@532	2609 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2610 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2611 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2612 AND "di"."issue_id" = "issue_id_v"
jbe@532	2613 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2614 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2615 AND "opinion"."degree" = 1
jbe@532	2616 AND "opinion"."fulfilled" = FALSE
jbe@532	2617 ) AS "plus1_unfulfilled_count",
jbe@532	2618 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2619 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2620 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2621 AND "di"."issue_id" = "issue_id_v"
jbe@532	2622 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2623 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2624 AND "opinion"."degree" = 1
jbe@532	2625 AND "opinion"."fulfilled" = TRUE
jbe@532	2626 ) AS "plus1_fulfilled_count",
jbe@532	2627 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2628 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2629 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2630 AND "di"."issue_id" = "issue_id_v"
jbe@532	2631 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2632 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2633 AND "opinion"."degree" = 2
jbe@532	2634 AND "opinion"."fulfilled" = FALSE
jbe@532	2635 ) AS "plus2_unfulfilled_count",
jbe@532	2636 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2637 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@532	2638 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2639 AND "di"."issue_id" = "issue_id_v"
jbe@532	2640 AND "di"."member_id" = "opinion"."member_id"
jbe@532	2641 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@532	2642 AND "opinion"."degree" = 2
jbe@532	2643 AND "opinion"."fulfilled" = TRUE
jbe@532	2644 ) AS "plus2_fulfilled_count"
jbe@532	2645 FROM "suggestion" JOIN "initiative"
jbe@532	2646 ON "suggestion"."initiative_id" = "initiative"."id"
jbe@532	2647 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@532	2648 END LOOP;
jbe@532	2649 RETURN "snapshot_id_v";
jbe@532	2650 END;
jbe@532	2651 $$;
jbe@532	2652
jbe@532	2653 COMMENT ON FUNCTION "take_snapshot"
jbe@532	2654 ( "issue"."id"%TYPE,
jbe@532	2655 "area"."id"%TYPE )
jbe@532	2656 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	2657
jbe@532	2658
jbe@532	2659 DROP FUNCTION "set_snapshot_event"
jbe@532	2660 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2661 "event_p" "snapshot_event" );
jbe@532	2662
jbe@532	2663
jbe@532	2664 CREATE FUNCTION "finish_snapshot"
jbe@532	2665 ( "issue_id_p" "issue"."id"%TYPE )
jbe@532	2666 RETURNS VOID
jbe@532	2667 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2668 DECLARE
jbe@532	2669 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	2670 BEGIN
jbe@532	2671 -- NOTE: function does not require snapshot isolation but we don't call
jbe@532	2672 -- "dont_require_snapshot_isolation" here because this function is
jbe@532	2673 -- also invoked by "check_issue"
jbe@532	2674 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532	2675 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
jbe@532	2676 ORDER BY "id" DESC LIMIT 1;
jbe@532	2677 UPDATE "issue" SET
jbe@532	2678 "calculated" = "snapshot"."calculated",
jbe@532	2679 "latest_snapshot_id" = "snapshot_id_v",
jbe@532	2680 "population" = "snapshot"."population"
jbe@532	2681 FROM "snapshot"
jbe@532	2682 WHERE "issue"."id" = "issue_id_p"
jbe@532	2683 AND "snapshot"."id" = "snapshot_id_v";
jbe@532	2684 UPDATE "initiative" SET
jbe@532	2685 "supporter_count" = (
jbe@532	2686 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2687 FROM "direct_interest_snapshot" AS "di"
jbe@532	2688 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2689 ON "di"."member_id" = "ds"."member_id"
jbe@532	2690 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2691 AND "di"."issue_id" = "issue_id_p"
jbe@532	2692 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2693 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2694 ),
jbe@532	2695 "informed_supporter_count" = (
jbe@532	2696 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2697 FROM "direct_interest_snapshot" AS "di"
jbe@532	2698 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2699 ON "di"."member_id" = "ds"."member_id"
jbe@532	2700 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2701 AND "di"."issue_id" = "issue_id_p"
jbe@532	2702 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2703 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2704 AND "ds"."informed"
jbe@532	2705 ),
jbe@532	2706 "satisfied_supporter_count" = (
jbe@532	2707 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2708 FROM "direct_interest_snapshot" AS "di"
jbe@532	2709 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2710 ON "di"."member_id" = "ds"."member_id"
jbe@532	2711 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2712 AND "di"."issue_id" = "issue_id_p"
jbe@532	2713 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2714 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2715 AND "ds"."satisfied"
jbe@532	2716 ),
jbe@532	2717 "satisfied_informed_supporter_count" = (
jbe@532	2718 SELECT coalesce(sum("di"."weight"), 0)
jbe@532	2719 FROM "direct_interest_snapshot" AS "di"
jbe@532	2720 JOIN "direct_supporter_snapshot" AS "ds"
jbe@532	2721 ON "di"."member_id" = "ds"."member_id"
jbe@532	2722 WHERE "di"."snapshot_id" = "snapshot_id_v"
jbe@532	2723 AND "di"."issue_id" = "issue_id_p"
jbe@532	2724 AND "ds"."snapshot_id" = "snapshot_id_v"
jbe@532	2725 AND "ds"."initiative_id" = "initiative"."id"
jbe@532	2726 AND "ds"."informed"
jbe@532	2727 AND "ds"."satisfied"
jbe@532	2728 )
jbe@532	2729 WHERE "issue_id" = "issue_id_p";
jbe@532	2730 UPDATE "suggestion" SET
jbe@532	2731 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
jbe@532	2732 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
jbe@532	2733 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
jbe@532	2734 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
jbe@532	2735 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
jbe@532	2736 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
jbe@532	2737 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
jbe@532	2738 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
jbe@532	2739 FROM "temporary_suggestion_counts" AS "temp", "initiative"
jbe@532	2740 WHERE "temp"."id" = "suggestion"."id"
jbe@532	2741 AND "initiative"."issue_id" = "issue_id_p"
jbe@532	2742 AND "suggestion"."initiative_id" = "initiative"."id";
jbe@532	2743 DELETE FROM "temporary_suggestion_counts";
jbe@532	2744 RETURN;
jbe@532	2745 END;
jbe@532	2746 $$;
jbe@532	2747
jbe@532	2748 COMMENT ON FUNCTION "finish_snapshot"
jbe@532	2749 ( "issue"."id"%TYPE )
jbe@532	2750 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	2751
jbe@532	2752
jbe@532	2753 CREATE FUNCTION "issue_admission"
jbe@532	2754 ( "area_id_p" "area"."id"%TYPE )
jbe@532	2755 RETURNS BOOLEAN
jbe@532	2756 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2757 DECLARE
jbe@532	2758 "issue_id_v" "issue"."id"%TYPE;
jbe@532	2759 BEGIN
jbe@532	2760 PERFORM "dont_require_transaction_isolation"();
jbe@532	2761 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
jbe@532	2762 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
jbe@532	2763 FROM "area_quorum" AS "view"
jbe@532	2764 WHERE "area"."id" = "view"."area_id"
jbe@532	2765 AND "area"."id" = "area_id_p";
jbe@532	2766 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
jbe@532	2767 WHERE "area_id" = "area_id_p";
jbe@532	2768 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
jbe@532	2769 UPDATE "issue" SET
jbe@532	2770 "admission_snapshot_id" = "latest_snapshot_id",
jbe@532	2771 "state" = 'discussion',
jbe@532	2772 "accepted" = now(),
jbe@532	2773 "phase_finished" = NULL
jbe@532	2774 WHERE "id" = "issue_id_v";
jbe@532	2775 RETURN TRUE;
jbe@532	2776 END;
jbe@532	2777 $$;
jbe@532	2778
jbe@532	2779 COMMENT ON FUNCTION "issue_admission"
jbe@532	2780 ( "area"."id"%TYPE )
jbe@532	2781 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	2782
jbe@532	2783
jbe@532	2784 CREATE OR REPLACE FUNCTION "check_issue"
jbe@532	2785 ( "issue_id_p" "issue"."id"%TYPE,
jbe@532	2786 "persist" "check_issue_persistence" )
jbe@532	2787 RETURNS "check_issue_persistence"
jbe@532	2788 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	2789 DECLARE
jbe@532	2790 "issue_row" "issue"%ROWTYPE;
jbe@532	2791 "last_calculated_v" "snapshot"."calculated"%TYPE;
jbe@532	2792 "policy_row" "policy"%ROWTYPE;
jbe@532	2793 "initiative_row" "initiative"%ROWTYPE;
jbe@532	2794 "state_v" "issue_state";
jbe@532	2795 BEGIN
jbe@532	2796 PERFORM "require_transaction_isolation"();
jbe@532	2797 IF "persist" ISNULL THEN
jbe@532	2798 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	2799 FOR UPDATE;
jbe@532	2800 SELECT "calculated" INTO "last_calculated_v"
jbe@532	2801 FROM "snapshot" JOIN "snapshot_issue"
jbe@532	2802 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
jbe@532	2803 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
jbe@532	2804 IF "issue_row"."closed" NOTNULL THEN
jbe@532	2805 RETURN NULL;
jbe@532	2806 END IF;
jbe@532	2807 "persist"."state" := "issue_row"."state";
jbe@532	2808 IF
jbe@532	2809 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
jbe@532	2810 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@532	2811 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@532	2812 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@532	2813 ( "issue_row"."state" = 'verification' AND now() >=
jbe@532	2814 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@532	2815 ( "issue_row"."state" = 'voting' AND now() >=
jbe@532	2816 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@532	2817 THEN
jbe@532	2818 "persist"."phase_finished" := TRUE;
jbe@532	2819 ELSE
jbe@532	2820 "persist"."phase_finished" := FALSE;
jbe@532	2821 END IF;
jbe@532	2822 IF
jbe@532	2823 NOT EXISTS (
jbe@532	2824 -- all initiatives are revoked
jbe@532	2825 SELECT NULL FROM "initiative"
jbe@532	2826 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532	2827 ) AND (
jbe@532	2828 -- and issue has not been accepted yet
jbe@532	2829 "persist"."state" = 'admission' OR
jbe@532	2830 -- or verification time has elapsed
jbe@532	2831 ( "persist"."state" = 'verification' AND
jbe@532	2832 "persist"."phase_finished" ) OR
jbe@532	2833 -- or no initiatives have been revoked lately
jbe@532	2834 NOT EXISTS (
jbe@532	2835 SELECT NULL FROM "initiative"
jbe@532	2836 WHERE "issue_id" = "issue_id_p"
jbe@532	2837 AND now() < "revoked" + "issue_row"."verification_time"
jbe@532	2838 )
jbe@532	2839 )
jbe@532	2840 THEN
jbe@532	2841 "persist"."issue_revoked" := TRUE;
jbe@532	2842 ELSE
jbe@532	2843 "persist"."issue_revoked" := FALSE;
jbe@532	2844 END IF;
jbe@532	2845 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@532	2846 UPDATE "issue" SET "phase_finished" = now()
jbe@532	2847 WHERE "id" = "issue_row"."id";
jbe@532	2848 RETURN "persist";
jbe@532	2849 ELSIF
jbe@532	2850 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@532	2851 THEN
jbe@532	2852 RETURN "persist";
jbe@532	2853 ELSE
jbe@532	2854 RETURN NULL;
jbe@532	2855 END IF;
jbe@532	2856 END IF;
jbe@532	2857 IF
jbe@532	2858 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532	2859 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@532	2860 THEN
jbe@532	2861 IF "persist"."state" != 'admission' THEN
jbe@532	2862 PERFORM "take_snapshot"("issue_id_p");
jbe@532	2863 PERFORM "finish_snapshot"("issue_id_p");
jbe@532	2864 END IF;
jbe@532	2865 "persist"."snapshot_created" = TRUE;
jbe@532	2866 IF "persist"."phase_finished" THEN
jbe@532	2867 IF "persist"."state" = 'admission' THEN
jbe@532	2868 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
jbe@532	2869 ELSIF "persist"."state" = 'discussion' THEN
jbe@532	2870 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532	2871 ELSIF "persist"."state" = 'verification' THEN
jbe@532	2872 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
jbe@532	2873 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@532	2874 SELECT * INTO "policy_row" FROM "policy"
jbe@532	2875 WHERE "id" = "issue_row"."policy_id";
jbe@532	2876 FOR "initiative_row" IN
jbe@532	2877 SELECT * FROM "initiative"
jbe@532	2878 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@532	2879 FOR UPDATE
jbe@532	2880 LOOP
jbe@532	2881 IF
jbe@532	2882 "initiative_row"."polling" OR (
jbe@532	2883 "initiative_row"."satisfied_supporter_count" >
jbe@532	2884 "policy_row"."initiative_quorum" AND
jbe@532	2885 "initiative_row"."satisfied_supporter_count" *
jbe@532	2886 "policy_row"."initiative_quorum_den" >=
jbe@532	2887 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@532	2888 )
jbe@532	2889 THEN
jbe@532	2890 UPDATE "initiative" SET "admitted" = TRUE
jbe@532	2891 WHERE "id" = "initiative_row"."id";
jbe@532	2892 ELSE
jbe@532	2893 UPDATE "initiative" SET "admitted" = FALSE
jbe@532	2894 WHERE "id" = "initiative_row"."id";
jbe@532	2895 END IF;
jbe@532	2896 END LOOP;
jbe@532	2897 END IF;
jbe@532	2898 END IF;
jbe@532	2899 RETURN "persist";
jbe@532	2900 END IF;
jbe@532	2901 IF
jbe@532	2902 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@532	2903 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@532	2904 THEN
jbe@532	2905 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@532	2906 "persist"."harmonic_weights_set" = TRUE;
jbe@532	2907 IF
jbe@532	2908 "persist"."phase_finished" OR
jbe@532	2909 "persist"."issue_revoked" OR
jbe@532	2910 "persist"."state" = 'admission'
jbe@532	2911 THEN
jbe@532	2912 RETURN "persist";
jbe@532	2913 ELSE
jbe@532	2914 RETURN NULL;
jbe@532	2915 END IF;
jbe@532	2916 END IF;
jbe@532	2917 IF "persist"."issue_revoked" THEN
jbe@532	2918 IF "persist"."state" = 'admission' THEN
jbe@532	2919 "state_v" := 'canceled_revoked_before_accepted';
jbe@532	2920 ELSIF "persist"."state" = 'discussion' THEN
jbe@532	2921 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@532	2922 ELSIF "persist"."state" = 'verification' THEN
jbe@532	2923 "state_v" := 'canceled_after_revocation_during_verification';
jbe@532	2924 END IF;
jbe@532	2925 UPDATE "issue" SET
jbe@532	2926 "state" = "state_v",
jbe@532	2927 "closed" = "phase_finished",
jbe@532	2928 "phase_finished" = NULL
jbe@532	2929 WHERE "id" = "issue_id_p";
jbe@532	2930 RETURN NULL;
jbe@532	2931 END IF;
jbe@532	2932 IF "persist"."state" = 'admission' THEN
jbe@532	2933 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	2934 FOR UPDATE;
jbe@532	2935 IF "issue_row"."phase_finished" NOTNULL THEN
jbe@532	2936 UPDATE "issue" SET
jbe@532	2937 "state" = 'canceled_issue_not_accepted',
jbe@532	2938 "closed" = "phase_finished",
jbe@532	2939 "phase_finished" = NULL
jbe@532	2940 WHERE "id" = "issue_id_p";
jbe@532	2941 END IF;
jbe@532	2942 RETURN NULL;
jbe@532	2943 END IF;
jbe@532	2944 IF "persist"."phase_finished" THEN
jbe@532	2945 IF "persist"."state" = 'discussion' THEN
jbe@532	2946 UPDATE "issue" SET
jbe@532	2947 "state" = 'verification',
jbe@532	2948 "half_frozen" = "phase_finished",
jbe@532	2949 "phase_finished" = NULL
jbe@532	2950 WHERE "id" = "issue_id_p";
jbe@532	2951 RETURN NULL;
jbe@532	2952 END IF;
jbe@532	2953 IF "persist"."state" = 'verification' THEN
jbe@532	2954 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@532	2955 FOR UPDATE;
jbe@532	2956 SELECT * INTO "policy_row" FROM "policy"
jbe@532	2957 WHERE "id" = "issue_row"."policy_id";
jbe@532	2958 IF EXISTS (
jbe@532	2959 SELECT NULL FROM "initiative"
jbe@532	2960 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@532	2961 ) THEN
jbe@532	2962 UPDATE "issue" SET
jbe@532	2963 "state" = 'voting',
jbe@532	2964 "fully_frozen" = "phase_finished",
jbe@532	2965 "phase_finished" = NULL
jbe@532	2966 WHERE "id" = "issue_id_p";
jbe@532	2967 ELSE
jbe@532	2968 UPDATE "issue" SET
jbe@532	2969 "state" = 'canceled_no_initiative_admitted',
jbe@532	2970 "fully_frozen" = "phase_finished",
jbe@532	2971 "closed" = "phase_finished",
jbe@532	2972 "phase_finished" = NULL
jbe@532	2973 WHERE "id" = "issue_id_p";
jbe@532	2974 -- NOTE: The following DELETE statements have effect only when
jbe@532	2975 -- issue state has been manipulated
jbe@532	2976 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@532	2977 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@532	2978 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@532	2979 END IF;
jbe@532	2980 RETURN NULL;
jbe@532	2981 END IF;
jbe@532	2982 IF "persist"."state" = 'voting' THEN
jbe@532	2983 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@532	2984 PERFORM "close_voting"("issue_id_p");
jbe@532	2985 "persist"."closed_voting" = TRUE;
jbe@532	2986 RETURN "persist";
jbe@532	2987 END IF;
jbe@532	2988 PERFORM "calculate_ranks"("issue_id_p");
jbe@532	2989 RETURN NULL;
jbe@532	2990 END IF;
jbe@532	2991 END IF;
jbe@532	2992 RAISE WARNING 'should not happen';
jbe@532	2993 RETURN NULL;
jbe@532	2994 END;
jbe@532	2995 $$;
jbe@532	2996
jbe@532	2997
jbe@532	2998 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@532	2999 RETURNS VOID
jbe@532	3000 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3001 DECLARE
jbe@532	3002 "area_id_v" "area"."id"%TYPE;
jbe@532	3003 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@532	3004 "issue_id_v" "issue"."id"%TYPE;
jbe@532	3005 "persist_v" "check_issue_persistence";
jbe@532	3006 BEGIN
jbe@532	3007 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@532	3008 DELETE FROM "expired_session";
jbe@532	3009 DELETE FROM "expired_token";
jbe@532	3010 DELETE FROM "expired_snapshot";
jbe@532	3011 PERFORM "check_activity"();
jbe@532	3012 PERFORM "calculate_member_counts"();
jbe@532	3013 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
jbe@532	3014 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
jbe@532	3015 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
jbe@532	3016 WHERE "snapshot_id" = "snapshot_id_v";
jbe@532	3017 LOOP
jbe@532	3018 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
jbe@532	3019 END LOOP;
jbe@532	3020 END LOOP;
jbe@532	3021 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@532	3022 "persist_v" := NULL;
jbe@532	3023 LOOP
jbe@532	3024 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@532	3025 EXIT WHEN "persist_v" ISNULL;
jbe@532	3026 END LOOP;
jbe@532	3027 END LOOP;
jbe@532	3028 RETURN;
jbe@532	3029 END;
jbe@532	3030 $$;
jbe@532	3031
jbe@532	3032 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	3033
jbe@532	3034
jbe@532	3035 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@532	3036 RETURNS VOID
jbe@532	3037 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3038 BEGIN
jbe@532	3039 IF EXISTS (
jbe@532	3040 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@532	3041 ) THEN
jbe@532	3042 -- override protection triggers:
jbe@532	3043 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@532	3044 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@532	3045 -- clean data:
jbe@532	3046 DELETE FROM "delegating_voter"
jbe@532	3047 WHERE "issue_id" = "issue_id_p";
jbe@532	3048 DELETE FROM "direct_voter"
jbe@532	3049 WHERE "issue_id" = "issue_id_p";
jbe@532	3050 DELETE FROM "delegating_interest_snapshot"
jbe@532	3051 WHERE "issue_id" = "issue_id_p";
jbe@532	3052 DELETE FROM "direct_interest_snapshot"
jbe@532	3053 WHERE "issue_id" = "issue_id_p";
jbe@532	3054 DELETE FROM "non_voter"
jbe@532	3055 WHERE "issue_id" = "issue_id_p";
jbe@532	3056 DELETE FROM "delegation"
jbe@532	3057 WHERE "issue_id" = "issue_id_p";
jbe@532	3058 DELETE FROM "supporter"
jbe@532	3059 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@532	3060 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@532	3061 AND "supporter"."initiative_id" = "initiative_id";
jbe@532	3062 -- mark issue as cleaned:
jbe@532	3063 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@532	3064 -- finish overriding protection triggers (avoids garbage):
jbe@532	3065 DELETE FROM "temporary_transaction_data"
jbe@532	3066 WHERE "key" = 'override_protection_triggers';
jbe@532	3067 END IF;
jbe@532	3068 RETURN;
jbe@532	3069 END;
jbe@532	3070 $$;
jbe@532	3071
jbe@532	3072
jbe@532	3073 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@532	3074 RETURNS VOID
jbe@532	3075 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3076 BEGIN
jbe@532	3077 UPDATE "member" SET
jbe@532	3078 "last_login" = NULL,
jbe@532	3079 "last_delegation_check" = NULL,
jbe@532	3080 "login" = NULL,
jbe@532	3081 "password" = NULL,
jbe@532	3082 "authority" = NULL,
jbe@532	3083 "authority_uid" = NULL,
jbe@532	3084 "authority_login" = NULL,
jbe@532	3085 "locked" = TRUE,
jbe@532	3086 "active" = FALSE,
jbe@532	3087 "notify_email" = NULL,
jbe@532	3088 "notify_email_unconfirmed" = NULL,
jbe@532	3089 "notify_email_secret" = NULL,
jbe@532	3090 "notify_email_secret_expiry" = NULL,
jbe@532	3091 "notify_email_lock_expiry" = NULL,
jbe@532	3092 "disable_notifications" = TRUE,
jbe@532	3093 "notification_counter" = DEFAULT,
jbe@532	3094 "notification_sample_size" = 0,
jbe@532	3095 "notification_dow" = NULL,
jbe@532	3096 "notification_hour" = NULL,
jbe@532	3097 "login_recovery_expiry" = NULL,
jbe@532	3098 "password_reset_secret" = NULL,
jbe@532	3099 "password_reset_secret_expiry" = NULL,
jbe@532	3100 "location" = NULL
jbe@532	3101 WHERE "id" = "member_id_p";
jbe@532	3102 -- "text_search_data" is updated by triggers
jbe@532	3103 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@532	3104 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@532	3105 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@532	3106 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@532	3107 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@532	3108 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@532	3109 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@532	3110 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@532	3111 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@532	3112 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@532	3113 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@532	3114 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@532	3115 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@532	3116 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@532	3117 DELETE FROM "direct_voter" USING "issue"
jbe@532	3118 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532	3119 AND "issue"."closed" ISNULL
jbe@532	3120 AND "member_id" = "member_id_p";
jbe@532	3121 RETURN;
jbe@532	3122 END;
jbe@532	3123 $$;
jbe@532	3124
jbe@532	3125
jbe@532	3126 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@532	3127 RETURNS VOID
jbe@532	3128 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@532	3129 BEGIN
jbe@532	3130 DELETE FROM "temporary_transaction_data";
jbe@532	3131 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@532	3132 UPDATE "member" SET
jbe@532	3133 "invite_code" = NULL,
jbe@532	3134 "invite_code_expiry" = NULL,
jbe@532	3135 "admin_comment" = NULL,
jbe@532	3136 "last_login" = NULL,
jbe@532	3137 "last_delegation_check" = NULL,
jbe@532	3138 "login" = NULL,
jbe@532	3139 "password" = NULL,
jbe@532	3140 "authority" = NULL,
jbe@532	3141 "authority_uid" = NULL,
jbe@532	3142 "authority_login" = NULL,
jbe@532	3143 "lang" = NULL,
jbe@532	3144 "notify_email" = NULL,
jbe@532	3145 "notify_email_unconfirmed" = NULL,
jbe@532	3146 "notify_email_secret" = NULL,
jbe@532	3147 "notify_email_secret_expiry" = NULL,
jbe@532	3148 "notify_email_lock_expiry" = NULL,
jbe@532	3149 "disable_notifications" = TRUE,
jbe@532	3150 "notification_counter" = DEFAULT,
jbe@532	3151 "notification_sample_size" = 0,
jbe@532	3152 "notification_dow" = NULL,
jbe@532	3153 "notification_hour" = NULL,
jbe@532	3154 "login_recovery_expiry" = NULL,
jbe@532	3155 "password_reset_secret" = NULL,
jbe@532	3156 "password_reset_secret_expiry" = NULL,
jbe@532	3157 "location" = NULL;
jbe@532	3158 -- "text_search_data" is updated by triggers
jbe@532	3159 DELETE FROM "setting";
jbe@532	3160 DELETE FROM "setting_map";
jbe@532	3161 DELETE FROM "member_relation_setting";
jbe@532	3162 DELETE FROM "member_image";
jbe@532	3163 DELETE FROM "contact";
jbe@532	3164 DELETE FROM "ignored_member";
jbe@532	3165 DELETE FROM "session";
jbe@532	3166 DELETE FROM "area_setting";
jbe@532	3167 DELETE FROM "issue_setting";
jbe@532	3168 DELETE FROM "ignored_initiative";
jbe@532	3169 DELETE FROM "initiative_setting";
jbe@532	3170 DELETE FROM "suggestion_setting";
jbe@532	3171 DELETE FROM "non_voter";
jbe@532	3172 DELETE FROM "direct_voter" USING "issue"
jbe@532	3173 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@532	3174 AND "issue"."closed" ISNULL;
jbe@532	3175 RETURN;
jbe@532	3176 END;
jbe@532	3177 $$;
jbe@532	3178
jbe@532	3179
jbe@532	3180 CREATE TEMPORARY TABLE "old_snapshot" AS
jbe@532	3181 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
jbe@532	3182 FROM (
jbe@532	3183 SELECT * FROM (
jbe@532	3184 SELECT
jbe@532	3185 "id" AS "issue_id",
jbe@532	3186 'end_of_admission'::"snapshot_event" AS "event",
jbe@532	3187 "accepted" AS "calculated"
jbe@532	3188 FROM "issue" WHERE "accepted" NOTNULL
jbe@532	3189 UNION ALL
jbe@532	3190 SELECT
jbe@532	3191 "id" AS "issue_id",
jbe@532	3192 'half_freeze'::"snapshot_event" AS "event",
jbe@532	3193 "half_frozen" AS "calculated"
jbe@532	3194 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@532	3195 UNION ALL
jbe@532	3196 SELECT
jbe@532	3197 "id" AS "issue_id",
jbe@532	3198 'full_freeze'::"snapshot_event" AS "event",
jbe@532	3199 "fully_frozen" AS "calculated"
jbe@532	3200 FROM "issue" WHERE "fully_frozen" NOTNULL
jbe@532	3201 ) AS "unordered"
jbe@532	3202 ORDER BY "calculated", "issue_id", "event"
jbe@532	3203 ) AS "ordered";
jbe@532	3204
jbe@532	3205
jbe@532	3206 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
jbe@532	3207 SELECT
jbe@532	3208 "old_snapshot"."snapshot_id" AS "id",
jbe@532	3209 "old_snapshot"."calculated",
jbe@532	3210 ( SELECT COALESCE(sum("weight"), 0)
jbe@532	3211 FROM "direct_population_snapshot" "dps"
jbe@532	3212 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
jbe@532	3213 AND "dps"."event" = "old_snapshot"."event"
jbe@532	3214 ) AS "population",
jbe@532	3215 "issue"."area_id" AS "area_id",
jbe@532	3216 "issue"."id" AS "issue_id"
jbe@532	3217 FROM "old_snapshot" JOIN "issue"
jbe@532	3218 ON "old_snapshot"."issue_id" = "issue"."id";
jbe@532	3219
jbe@532	3220
jbe@532	3221 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@532	3222 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
jbe@532	3223
jbe@532	3224
jbe@532	3225 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	3226 SELECT
jbe@532	3227 "old_snapshot"."snapshot_id",
jbe@532	3228 "direct_population_snapshot"."member_id"
jbe@532	3229 FROM "old_snapshot" JOIN "direct_population_snapshot"
jbe@532	3230 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
jbe@532	3231 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
jbe@532	3232
jbe@532	3233 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
jbe@532	3234 SELECT
jbe@532	3235 "old_snapshot"."snapshot_id",
jbe@532	3236 "delegating_population_snapshot"."member_id"
jbe@532	3237 FROM "old_snapshot" JOIN "delegating_population_snapshot"
jbe@532	3238 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
jbe@532	3239 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
jbe@532	3240
jbe@532	3241
jbe@532	3242 INSERT INTO "direct_interest_snapshot"
jbe@532	3243 ("snapshot_id", "issue_id", "member_id", "weight")
jbe@532	3244 SELECT
jbe@532	3245 "old_snapshot"."snapshot_id",
jbe@532	3246 "old_snapshot"."issue_id",
jbe@532	3247 "direct_interest_snapshot_old"."member_id",
jbe@532	3248 "direct_interest_snapshot_old"."weight"
jbe@532	3249 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
jbe@532	3250 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
jbe@532	3251 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
jbe@532	3252
jbe@532	3253 INSERT INTO "delegating_interest_snapshot"
jbe@532	3254 ( "snapshot_id", "issue_id",
jbe@532	3255 "member_id", "weight", "scope", "delegate_member_ids" )
jbe@532	3256 SELECT
jbe@532	3257 "old_snapshot"."snapshot_id",
jbe@532	3258 "old_snapshot"."issue_id",
jbe@532	3259 "delegating_interest_snapshot_old"."member_id",
jbe@532	3260 "delegating_interest_snapshot_old"."weight",
jbe@532	3261 "delegating_interest_snapshot_old"."scope",
jbe@532	3262 "delegating_interest_snapshot_old"."delegate_member_ids"
jbe@532	3263 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
jbe@532	3264 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
jbe@532	3265 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
jbe@532	3266
jbe@532	3267 INSERT INTO "direct_supporter_snapshot"
jbe@532	3268 ( "snapshot_id", "issue_id",
jbe@532	3269 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
jbe@532	3270 SELECT
jbe@532	3271 "old_snapshot"."snapshot_id",
jbe@532	3272 "old_snapshot"."issue_id",
jbe@532	3273 "direct_supporter_snapshot_old"."initiative_id",
jbe@532	3274 "direct_supporter_snapshot_old"."member_id",
jbe@532	3275 "direct_supporter_snapshot_old"."draft_id",
jbe@532	3276 "direct_supporter_snapshot_old"."informed",
jbe@532	3277 "direct_supporter_snapshot_old"."satisfied"
jbe@532	3278 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
jbe@532	3279 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
jbe@532	3280 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
jbe@532	3281
jbe@532	3282
jbe@532	3283 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
jbe@532	3284
jbe@532	3285 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
jbe@532	3286 FROM (
jbe@532	3287 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
jbe@532	3288 FROM "snapshot" ORDER BY "issue_id", "id" DESC
jbe@532	3289 ) AS "snapshot"
jbe@532	3290 WHERE "snapshot"."issue_id" = "issue"."id";
jbe@532	3291
jbe@532	3292 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532	3293 FROM "old_snapshot"
jbe@532	3294 WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532	3295 AND "old_snapshot"."event" = 'end_of_admission';
jbe@532	3296
jbe@532	3297 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532	3298 FROM "old_snapshot"
jbe@532	3299 WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532	3300 AND "old_snapshot"."event" = 'half_freeze';
jbe@532	3301
jbe@532	3302 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
jbe@532	3303 FROM "old_snapshot"
jbe@532	3304 WHERE "old_snapshot"."issue_id" = "issue"."id"
jbe@532	3305 AND "old_snapshot"."event" = 'full_freeze';
jbe@532	3306
jbe@532	3307 ALTER TABLE "issue" ENABLE TRIGGER USER;
jbe@532	3308
jbe@532	3309
jbe@532	3310 DROP TABLE "old_snapshot";
jbe@532	3311
jbe@532	3312 DROP TABLE "direct_supporter_snapshot_old";
jbe@532	3313 DROP TABLE "delegating_interest_snapshot_old";
jbe@532	3314 DROP TABLE "direct_interest_snapshot_old";
jbe@532	3315 DROP TABLE "delegating_population_snapshot";
jbe@532	3316 DROP TABLE "direct_population_snapshot";
jbe@532	3317
jbe@532	3318
jbe@532	3319 DROP VIEW "open_issue";
jbe@532	3320
jbe@532	3321
jbe@532	3322 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
jbe@532	3323
jbe@532	3324
jbe@532	3325 CREATE VIEW "open_issue" AS
jbe@532	3326 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@532	3327
jbe@532	3328 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@532	3329
jbe@532	3330
jbe@532	3331 -- NOTE: create "issue_for_admission" view after altering table "issue"
jbe@532	3332 CREATE VIEW "issue_for_admission" AS
jbe@532	3333 SELECT DISTINCT ON ("issue"."area_id")
jbe@532	3334 "issue".*,
jbe@532	3335 max("initiative"."supporter_count") AS "max_supporter_count"
jbe@532	3336 FROM "issue"
jbe@532	3337 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@532	3338 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@532	3339 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@532	3340 WHERE "issue"."state" = 'admission'::"issue_state"
jbe@532	3341 AND now() >= "issue"."created" + "issue"."min_admission_time"
jbe@532	3342 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
jbe@532	3343 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
jbe@532	3344 "issue"."population" * "policy"."issue_quorum_num"
jbe@532	3345 AND "initiative"."supporter_count" >= "area"."issue_quorum"
jbe@532	3346 AND "initiative"."revoked" ISNULL
jbe@532	3347 GROUP BY "issue"."id"
jbe@532	3348 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
jbe@532	3349
jbe@532	3350 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	3351
jbe@532	3352
jbe@532	3353 DROP TYPE "snapshot_event";
jbe@532	3354
jbe@532	3355
jbe@532	3356 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
jbe@532	3357 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
jbe@532	3358 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
jbe@532	3359
jbe@532	3360
jbe@532	3361 COMMIT;