liquid_feedback_core: update/core-update.v1.3.1-v1.4.0.sql annotate

liquid_feedback_core

annotate update/core-update.v1.3.1-v1.4.0.sql @ 548:1adb0ad5900c

Added "discovery_baseurl" for system applications

author	jbe
date	Sat Aug 05 16:10:43 2017 +0200 (2017-08-05)
parents	eee75cff3e5a
children

rev	line source
jbe@115	1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
jbe@115	2
jbe@115	3
jbe@115	4 -- Update version information:
jbe@115	5
jbe@115	6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@194	7 SELECT * FROM (VALUES ('1.4.0', 1, 4, 0))
jbe@115	8 AS "subquery"("string", "major", "minor", "revision");
jbe@115	9
jbe@115	10
jbe@115	11 -- New columns "notify_level" and "notify_event_id" in "member" table:
jbe@115	12
jbe@115	13 CREATE TYPE "notify_level" AS ENUM
jbe@115	14 ('none', 'voting', 'verification', 'discussion', 'all');
jbe@115	15
jbe@115	16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
jbe@115	17
jbe@115	18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
jbe@115	19 ALTER TABLE "member" ADD "notify_event_id" INT8;
jbe@115	20
jbe@115	21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
jbe@115	22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
jbe@115	23
jbe@115	24
jbe@115	25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
jbe@115	26
jbe@115	27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
jbe@115	28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
jbe@115	29 ALTER TABLE "invite_code" ADD UNIQUE ("code");
jbe@115	30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
jbe@115	31
jbe@115	32
jbe@115	33 -- Add index for "other_member_id" column of "contact" table:
jbe@115	34
jbe@115	35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
jbe@115	36
jbe@115	37
jbe@115	38 -- New table "ignored_member":
jbe@115	39
jbe@115	40 CREATE TABLE "ignored_member" (
jbe@115	41 PRIMARY KEY ("member_id", "other_member_id"),
jbe@115	42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115	44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
jbe@115	45
jbe@115	46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
jbe@115	47
jbe@115	48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
jbe@115	49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
jbe@115	50
jbe@115	51
jbe@115	52 -- New table "unit" with default entry:
jbe@115	53
jbe@115	54 CREATE TABLE "unit" (
jbe@115	55 "id" SERIAL4 PRIMARY KEY,
jbe@115	56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	57 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@115	58 "name" TEXT NOT NULL,
jbe@115	59 "description" TEXT NOT NULL DEFAULT '',
jbe@115	60 "member_count" INT4,
jbe@115	61 "text_search_data" TSVECTOR );
jbe@115	62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
jbe@115	63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
jbe@115	64 CREATE INDEX "unit_active_idx" ON "unit" ("active");
jbe@115	65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
jbe@115	66 CREATE TRIGGER "update_text_search_data"
jbe@115	67 BEFORE INSERT OR UPDATE ON "unit"
jbe@115	68 FOR EACH ROW EXECUTE PROCEDURE
jbe@115	69 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@115	70 "name", "description" );
jbe@115	71
jbe@115	72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
jbe@115	73
jbe@115	74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
jbe@115	75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
jbe@115	76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
jbe@115	77
jbe@115	78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1
jbe@115	79
jbe@115	80
jbe@115	81 -- New column "unit_id" in table "area":
jbe@115	82
jbe@115	83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
jbe@115	84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115	85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
jbe@115	86
jbe@115	87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
jbe@115	88
jbe@115	89
jbe@115	90 -- Issue states:
jbe@115	91
jbe@115	92 CREATE TYPE "issue_state" AS ENUM (
jbe@115	93 'admission', 'discussion', 'verification', 'voting',
jbe@115	94 'canceled_revoked_before_accepted',
jbe@115	95 'canceled_issue_not_accepted',
jbe@115	96 'canceled_after_revocation_during_discussion',
jbe@115	97 'canceled_after_revocation_during_verification',
jbe@115	98 'calculation',
jbe@115	99 'canceled_no_initiative_admitted',
jbe@115	100 'finished_without_winner', 'finished_with_winner');
jbe@115	101
jbe@115	102 COMMENT ON TYPE "issue_state" IS 'State of issues';
jbe@115	103
jbe@205	104 ALTER TABLE "issue" ADD "state" "issue_state" DEFAULT NULL;
jbe@205	105 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
jbe@115	106
jbe@115	107 -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115	108
jbe@115	109
jbe@115	110 -- New column "revoked_by_member_id" in table "initiative":
jbe@115	111
jbe@115	112 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@115	113
jbe@115	114 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
jbe@115	115
jbe@115	116 -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115	117
jbe@115	118
jbe@115	119 -- New table "ignored_initiative":
jbe@115	120
jbe@115	121 CREATE TABLE "ignored_initiative" (
jbe@115	122 PRIMARY KEY ("initiative_id", "member_id"),
jbe@115	123 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	124 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115	125 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
jbe@115	126
jbe@115	127 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
jbe@115	128
jbe@115	129
jbe@115	130 -- New table "invite_code_unit":
jbe@115	131
jbe@115	132 CREATE TABLE "invite_code_unit" (
jbe@115	133 PRIMARY KEY ("invite_code_id", "unit_id"),
jbe@115	134 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	135 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115	136
jbe@115	137 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
jbe@115	138
jbe@117	139 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
jbe@124	140 SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";
jbe@115	141
jbe@115	142
jbe@115	143 -- New table "privilege":
jbe@115	144
jbe@115	145 CREATE TABLE "privilege" (
jbe@115	146 PRIMARY KEY ("unit_id", "member_id"),
jbe@115	147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115	150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115	151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115	152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115	153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@115	154
jbe@115	155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
jbe@115	156
jbe@115	157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
jbe@115	158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
jbe@115	159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
jbe@115	160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
jbe@115	161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
jbe@115	162
jbe@124	163 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@124	164 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@124	165 FROM "member";
jbe@124	166
jbe@115	167
jbe@115	168 -- Remove table "ignored_issue", which is no longer existent:
jbe@115	169
jbe@115	170 DROP TABLE "ignored_issue";
jbe@115	171
jbe@115	172
jbe@115	173 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
jbe@115	174
jbe@115	175 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later
jbe@115	176 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
jbe@115	177 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
jbe@115	178
jbe@115	179
jbe@122	180 -- Delete views and functions being dependent on type "delegation_scope":
jbe@115	181
jbe@115	182 DROP FUNCTION "delegation_chain"
jbe@115	183 ( "member_id_p" "member"."id"%TYPE,
jbe@115	184 "area_id_p" "area"."id"%TYPE,
jbe@115	185 "issue_id_p" "issue"."id"%TYPE );
jbe@115	186
jbe@115	187 DROP FUNCTION "delegation_chain"
jbe@115	188 ( "member_id_p" "member"."id"%TYPE,
jbe@115	189 "area_id_p" "area"."id"%TYPE,
jbe@115	190 "issue_id_p" "issue"."id"%TYPE,
jbe@115	191 "simulate_trustee_id_p" "member"."id"%TYPE );
jbe@115	192
jbe@115	193 DROP TYPE "delegation_chain_row";
jbe@115	194
jbe@115	195 DROP VIEW "issue_delegation";
jbe@115	196 DROP VIEW "area_delegation";
jbe@115	197 DROP VIEW "global_delegation";
jbe@115	198 DROP VIEW "active_delegation";
jbe@115	199
jbe@115	200
jbe@122	201 -- Modify "delegation" table to use new "delegation_scope" type:
jbe@115	202
jbe@115	203 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
jbe@115	204 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
jbe@115	205
jbe@115	206 DROP INDEX "delegation_global_truster_id_unique_idx";
jbe@115	207
jbe@115	208 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
jbe@115	209 USING CASE WHEN "scope" = 'global'
jbe@115	210 THEN 'unit'::"delegation_scope"
jbe@115	211 ELSE "scope"::text::"delegation_scope" END;
jbe@115	212
jbe@115	213 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115	214
jbe@115	215 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
jbe@115	216 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
jbe@115	217
jbe@115	218 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
jbe@115	219
jbe@115	220 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
jbe@115	221
jbe@115	222 -- NOTE: Column "unit_id" filled after transaction (see below)
jbe@115	223
jbe@115	224
jbe@122	225 -- Modify snapshot tables to use new "delegation_scope" type:
jbe@115	226
jbe@115	227 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115	228 USING CASE WHEN "scope" = 'global'
jbe@115	229 THEN 'unit'::"delegation_scope"
jbe@115	230 ELSE "scope"::text::"delegation_scope" END;
jbe@115	231
jbe@115	232 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115	233 USING CASE WHEN "scope" = 'global'
jbe@115	234 THEN 'unit'::"delegation_scope"
jbe@115	235 ELSE "scope"::text::"delegation_scope" END;
jbe@115	236
jbe@115	237 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
jbe@115	238 USING CASE WHEN "scope" = 'global'
jbe@115	239 THEN 'unit'::"delegation_scope"
jbe@115	240 ELSE "scope"::text::"delegation_scope" END;
jbe@115	241
jbe@115	242
jbe@115	243 -- New table "non_voter":
jbe@115	244
jbe@115	245 CREATE TABLE "non_voter" (
jbe@115	246 PRIMARY KEY ("issue_id", "member_id"),
jbe@115	247 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	248 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115	249 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
jbe@115	250
jbe@115	251 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
jbe@115	252
jbe@115	253
jbe@122	254 -- New tables "issue_comment" and "rendered_issue_comment":
jbe@115	255
jbe@115	256 CREATE TABLE "issue_comment" (
jbe@115	257 PRIMARY KEY ("issue_id", "member_id"),
jbe@115	258 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	259 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	260 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@115	261 "formatting_engine" TEXT,
jbe@115	262 "content" TEXT NOT NULL,
jbe@115	263 "text_search_data" TSVECTOR );
jbe@115	264 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
jbe@115	265 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
jbe@115	266 CREATE TRIGGER "update_text_search_data"
jbe@115	267 BEFORE INSERT OR UPDATE ON "issue_comment"
jbe@115	268 FOR EACH ROW EXECUTE PROCEDURE
jbe@115	269 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115	270
jbe@115	271 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
jbe@115	272
jbe@115	273 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
jbe@115	274
jbe@115	275 CREATE TABLE "rendered_issue_comment" (
jbe@115	276 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115	277 FOREIGN KEY ("issue_id", "member_id")
jbe@115	278 REFERENCES "issue_comment" ("issue_id", "member_id")
jbe@115	279 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	280 "issue_id" INT4,
jbe@115	281 "member_id" INT4,
jbe@115	282 "format" TEXT,
jbe@115	283 "content" TEXT NOT NULL );
jbe@115	284
jbe@115	285 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
jbe@115	286
jbe@115	287
jbe@122	288 -- New tables "voting_comment" and "rendered_voting_comment":
jbe@115	289
jbe@115	290 CREATE TABLE "voting_comment" (
jbe@115	291 PRIMARY KEY ("issue_id", "member_id"),
jbe@115	292 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	293 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	294 "changed" TIMESTAMPTZ,
jbe@115	295 "formatting_engine" TEXT,
jbe@115	296 "content" TEXT NOT NULL,
jbe@115	297 "text_search_data" TSVECTOR );
jbe@115	298 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
jbe@115	299 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
jbe@115	300 CREATE TRIGGER "update_text_search_data"
jbe@115	301 BEFORE INSERT OR UPDATE ON "voting_comment"
jbe@115	302 FOR EACH ROW EXECUTE PROCEDURE
jbe@115	303 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115	304
jbe@115	305 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
jbe@115	306
jbe@115	307 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
jbe@115	308
jbe@115	309 CREATE TABLE "rendered_voting_comment" (
jbe@115	310 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115	311 FOREIGN KEY ("issue_id", "member_id")
jbe@115	312 REFERENCES "voting_comment" ("issue_id", "member_id")
jbe@115	313 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	314 "issue_id" INT4,
jbe@115	315 "member_id" INT4,
jbe@115	316 "format" TEXT,
jbe@115	317 "content" TEXT NOT NULL );
jbe@115	318
jbe@115	319 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
jbe@115	320
jbe@115	321
jbe@115	322 -- New table "event":
jbe@115	323
jbe@115	324 CREATE TYPE "event_type" AS ENUM (
jbe@115	325 'issue_state_changed',
jbe@115	326 'initiative_created_in_new_issue',
jbe@115	327 'initiative_created_in_existing_issue',
jbe@115	328 'initiative_revoked',
jbe@115	329 'new_draft_created',
jbe@115	330 'suggestion_created');
jbe@115	331
jbe@115	332 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
jbe@115	333
jbe@115	334 CREATE TABLE "event" (
jbe@115	335 "id" SERIAL8 PRIMARY KEY,
jbe@115	336 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@115	337 "event" "event_type" NOT NULL,
jbe@115	338 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@115	339 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	340 "state" "issue_state" CHECK ("state" != 'calculation'),
jbe@115	341 "initiative_id" INT4,
jbe@115	342 "draft_id" INT8,
jbe@115	343 "suggestion_id" INT8,
jbe@115	344 FOREIGN KEY ("issue_id", "initiative_id")
jbe@115	345 REFERENCES "initiative" ("issue_id", "id")
jbe@115	346 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	347 FOREIGN KEY ("initiative_id", "draft_id")
jbe@115	348 REFERENCES "draft" ("initiative_id", "id")
jbe@115	349 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	350 FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@115	351 REFERENCES "suggestion" ("initiative_id", "id")
jbe@115	352 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115	353 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@115	354 "event" != 'issue_state_changed' OR (
jbe@115	355 "member_id" ISNULL AND
jbe@115	356 "issue_id" NOTNULL AND
jbe@115	357 "state" NOTNULL AND
jbe@115	358 "initiative_id" ISNULL AND
jbe@115	359 "draft_id" ISNULL AND
jbe@115	360 "suggestion_id" ISNULL )),
jbe@115	361 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@115	362 "event" NOT IN (
jbe@115	363 'initiative_created_in_new_issue',
jbe@115	364 'initiative_created_in_existing_issue',
jbe@115	365 'initiative_revoked',
jbe@115	366 'new_draft_created'
jbe@115	367 ) OR (
jbe@115	368 "member_id" NOTNULL AND
jbe@115	369 "issue_id" NOTNULL AND
jbe@115	370 "state" NOTNULL AND
jbe@115	371 "initiative_id" NOTNULL AND
jbe@115	372 "draft_id" NOTNULL AND
jbe@115	373 "suggestion_id" ISNULL )),
jbe@115	374 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@115	375 "event" != 'suggestion_created' OR (
jbe@115	376 "member_id" NOTNULL AND
jbe@115	377 "issue_id" NOTNULL AND
jbe@115	378 "state" NOTNULL AND
jbe@115	379 "initiative_id" NOTNULL AND
jbe@115	380 "draft_id" ISNULL AND
jbe@115	381 "suggestion_id" NOTNULL )) );
jbe@115	382
jbe@115	383 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
jbe@115	384
jbe@115	385 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
jbe@115	386 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
jbe@115	387 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
jbe@115	388 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
jbe@115	389
jbe@115	390
jbe@115	391 -- Triggers to fill "event" table:
jbe@115	392
jbe@115	393 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@115	394 RETURNS TRIGGER
jbe@115	395 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	396 BEGIN
jbe@115	397 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
jbe@115	398 INSERT INTO "event" ("event", "issue_id", "state")
jbe@115	399 VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@115	400 END IF;
jbe@115	401 RETURN NULL;
jbe@115	402 END;
jbe@115	403 $$;
jbe@115	404
jbe@115	405 CREATE TRIGGER "write_event_issue_state_changed"
jbe@115	406 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
jbe@115	407 "write_event_issue_state_changed_trigger"();
jbe@115	408
jbe@115	409 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
jbe@115	410 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
jbe@115	411
jbe@115	412 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@115	413 RETURNS TRIGGER
jbe@115	414 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	415 DECLARE
jbe@115	416 "initiative_row" "initiative"%ROWTYPE;
jbe@115	417 "issue_row" "issue"%ROWTYPE;
jbe@115	418 "event_v" "event_type";
jbe@115	419 BEGIN
jbe@115	420 SELECT * INTO "initiative_row" FROM "initiative"
jbe@115	421 WHERE "id" = NEW."initiative_id";
jbe@115	422 SELECT * INTO "issue_row" FROM "issue"
jbe@115	423 WHERE "id" = "initiative_row"."issue_id";
jbe@115	424 IF EXISTS (
jbe@115	425 SELECT NULL FROM "draft"
jbe@115	426 WHERE "initiative_id" = NEW."initiative_id"
jbe@115	427 AND "id" != NEW."id"
jbe@115	428 ) THEN
jbe@115	429 "event_v" := 'new_draft_created';
jbe@115	430 ELSE
jbe@115	431 IF EXISTS (
jbe@115	432 SELECT NULL FROM "initiative"
jbe@115	433 WHERE "issue_id" = "initiative_row"."issue_id"
jbe@115	434 AND "id" != "initiative_row"."id"
jbe@115	435 ) THEN
jbe@115	436 "event_v" := 'initiative_created_in_existing_issue';
jbe@115	437 ELSE
jbe@115	438 "event_v" := 'initiative_created_in_new_issue';
jbe@115	439 END IF;
jbe@115	440 END IF;
jbe@115	441 INSERT INTO "event" (
jbe@115	442 "event", "member_id",
jbe@115	443 "issue_id", "state", "initiative_id", "draft_id"
jbe@115	444 ) VALUES (
jbe@115	445 "event_v",
jbe@115	446 NEW."author_id",
jbe@115	447 "initiative_row"."issue_id",
jbe@115	448 "issue_row"."state",
jbe@115	449 "initiative_row"."id",
jbe@115	450 NEW."id" );
jbe@115	451 RETURN NULL;
jbe@115	452 END;
jbe@115	453 $$;
jbe@115	454
jbe@115	455 CREATE TRIGGER "write_event_initiative_or_draft_created"
jbe@115	456 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
jbe@115	457 "write_event_initiative_or_draft_created_trigger"();
jbe@115	458
jbe@115	459 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
jbe@115	460 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
jbe@115	461
jbe@115	462 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@115	463 RETURNS TRIGGER
jbe@115	464 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	465 DECLARE
jbe@115	466 "issue_row" "issue"%ROWTYPE;
jbe@115	467 BEGIN
jbe@115	468 SELECT * INTO "issue_row" FROM "issue"
jbe@115	469 WHERE "id" = NEW."issue_id";
jbe@115	470 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@115	471 INSERT INTO "event" (
jbe@115	472 "event", "member_id", "issue_id", "state", "initiative_id"
jbe@115	473 ) VALUES (
jbe@115	474 'initiative_revoked',
jbe@115	475 NEW."revoked_by_member_id",
jbe@115	476 NEW."issue_id",
jbe@115	477 "issue_row"."state",
jbe@115	478 NEW."id" );
jbe@115	479 END IF;
jbe@115	480 RETURN NULL;
jbe@115	481 END;
jbe@115	482 $$;
jbe@115	483
jbe@115	484 CREATE TRIGGER "write_event_initiative_revoked"
jbe@115	485 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
jbe@115	486 "write_event_initiative_revoked_trigger"();
jbe@115	487
jbe@115	488 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
jbe@115	489 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
jbe@115	490
jbe@115	491 CREATE FUNCTION "write_event_suggestion_created_trigger"()
jbe@115	492 RETURNS TRIGGER
jbe@115	493 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	494 DECLARE
jbe@115	495 "initiative_row" "initiative"%ROWTYPE;
jbe@115	496 "issue_row" "issue"%ROWTYPE;
jbe@115	497 BEGIN
jbe@115	498 SELECT * INTO "initiative_row" FROM "initiative"
jbe@115	499 WHERE "id" = NEW."initiative_id";
jbe@115	500 SELECT * INTO "issue_row" FROM "issue"
jbe@115	501 WHERE "id" = "initiative_row"."issue_id";
jbe@115	502 INSERT INTO "event" (
jbe@115	503 "event", "member_id",
jbe@115	504 "issue_id", "state", "initiative_id", "suggestion_id"
jbe@115	505 ) VALUES (
jbe@115	506 'suggestion_created',
jbe@115	507 NEW."author_id",
jbe@115	508 "initiative_row"."issue_id",
jbe@115	509 "issue_row"."state",
jbe@115	510 "initiative_row"."id",
jbe@115	511 NEW."id" );
jbe@115	512 RETURN NULL;
jbe@115	513 END;
jbe@115	514 $$;
jbe@115	515
jbe@115	516 CREATE TRIGGER "write_event_suggestion_created"
jbe@115	517 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@115	518 "write_event_suggestion_created_trigger"();
jbe@115	519
jbe@115	520 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
jbe@115	521 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@115	522
jbe@115	523
jbe@115	524 -- Modified views:
jbe@115	525
jbe@115	526 CREATE VIEW "unit_delegation" AS
jbe@115	527 SELECT
jbe@115	528 "unit"."id" AS "unit_id",
jbe@115	529 "delegation"."id",
jbe@115	530 "delegation"."truster_id",
jbe@115	531 "delegation"."trustee_id",
jbe@115	532 "delegation"."scope"
jbe@115	533 FROM "unit"
jbe@115	534 JOIN "delegation"
jbe@115	535 ON "delegation"."unit_id" = "unit"."id"
jbe@115	536 JOIN "member"
jbe@115	537 ON "delegation"."truster_id" = "member"."id"
jbe@115	538 JOIN "privilege"
jbe@115	539 ON "delegation"."unit_id" = "privilege"."unit_id"
jbe@115	540 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115	541 WHERE "member"."active" AND "privilege"."voting_right";
jbe@115	542
jbe@115	543 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
jbe@115	544
jbe@115	545 CREATE VIEW "area_delegation" AS
jbe@115	546 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@115	547 "area"."id" AS "area_id",
jbe@115	548 "delegation"."id",
jbe@115	549 "delegation"."truster_id",
jbe@115	550 "delegation"."trustee_id",
jbe@115	551 "delegation"."scope"
jbe@115	552 FROM "area"
jbe@115	553 JOIN "delegation"
jbe@115	554 ON "delegation"."unit_id" = "area"."unit_id"
jbe@115	555 OR "delegation"."area_id" = "area"."id"
jbe@115	556 JOIN "member"
jbe@115	557 ON "delegation"."truster_id" = "member"."id"
jbe@115	558 JOIN "privilege"
jbe@115	559 ON "area"."unit_id" = "privilege"."unit_id"
jbe@115	560 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115	561 WHERE "member"."active" AND "privilege"."voting_right"
jbe@115	562 ORDER BY
jbe@115	563 "area"."id",
jbe@115	564 "delegation"."truster_id",
jbe@115	565 "delegation"."scope" DESC;
jbe@115	566
jbe@115	567 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
jbe@115	568
jbe@115	569 CREATE VIEW "issue_delegation" AS
jbe@115	570 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@115	571 "issue"."id" AS "issue_id",
jbe@115	572 "delegation"."id",
jbe@115	573 "delegation"."truster_id",
jbe@115	574 "delegation"."trustee_id",
jbe@115	575 "delegation"."scope"
jbe@115	576 FROM "issue"
jbe@115	577 JOIN "area"
jbe@115	578 ON "area"."id" = "issue"."area_id"
jbe@115	579 JOIN "delegation"
jbe@115	580 ON "delegation"."unit_id" = "area"."unit_id"
jbe@115	581 OR "delegation"."area_id" = "area"."id"
jbe@115	582 OR "delegation"."issue_id" = "issue"."id"
jbe@115	583 JOIN "member"
jbe@115	584 ON "delegation"."truster_id" = "member"."id"
jbe@115	585 JOIN "privilege"
jbe@115	586 ON "area"."unit_id" = "privilege"."unit_id"
jbe@115	587 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115	588 WHERE "member"."active" AND "privilege"."voting_right"
jbe@115	589 ORDER BY
jbe@115	590 "issue"."id",
jbe@115	591 "delegation"."truster_id",
jbe@115	592 "delegation"."scope" DESC;
jbe@115	593
jbe@115	594 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@115	595
jbe@115	596 CREATE VIEW "unit_member_count" AS
jbe@115	597 SELECT
jbe@115	598 "unit"."id" AS "unit_id",
jbe@115	599 sum("member"."id") AS "member_count"
jbe@115	600 FROM "unit"
jbe@115	601 LEFT JOIN "privilege"
jbe@115	602 ON "privilege"."unit_id" = "unit"."id"
jbe@115	603 AND "privilege"."voting_right"
jbe@115	604 LEFT JOIN "member"
jbe@115	605 ON "member"."id" = "privilege"."member_id"
jbe@115	606 AND "member"."active"
jbe@115	607 GROUP BY "unit"."id";
jbe@115	608
jbe@115	609 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@115	610
jbe@115	611 DROP VIEW "area_member_count";
jbe@115	612 CREATE VIEW "area_member_count" AS
jbe@115	613 SELECT
jbe@115	614 "area"."id" AS "area_id",
jbe@115	615 count("member"."id") AS "direct_member_count",
jbe@115	616 coalesce(
jbe@115	617 sum(
jbe@115	618 CASE WHEN "member"."id" NOTNULL THEN
jbe@115	619 "membership_weight"("area"."id", "member"."id")
jbe@115	620 ELSE 0 END
jbe@115	621 )
jbe@115	622 ) AS "member_weight",
jbe@115	623 coalesce(
jbe@115	624 sum(
jbe@115	625 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@115	626 "membership_weight"("area"."id", "member"."id")
jbe@115	627 ELSE 0 END
jbe@115	628 )
jbe@115	629 ) AS "autoreject_weight"
jbe@115	630 FROM "area"
jbe@115	631 LEFT JOIN "membership"
jbe@115	632 ON "area"."id" = "membership"."area_id"
jbe@115	633 LEFT JOIN "privilege"
jbe@115	634 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115	635 AND "privilege"."member_id" = "membership"."member_id"
jbe@115	636 AND "privilege"."voting_right"
jbe@115	637 LEFT JOIN "member"
jbe@115	638 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
jbe@115	639 AND "member"."active"
jbe@115	640 GROUP BY "area"."id";
jbe@115	641
jbe@115	642 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
jbe@115	643
jbe@115	644
jbe@115	645 -- New view "event_seen_by_member":
jbe@115	646
jbe@115	647 CREATE VIEW "event_seen_by_member" AS
jbe@115	648 SELECT
jbe@115	649 "member"."id" AS "seen_by_member_id",
jbe@115	650 CASE WHEN "event"."state" IN (
jbe@115	651 'voting',
jbe@115	652 'finished_without_winner',
jbe@115	653 'finished_with_winner'
jbe@115	654 ) THEN
jbe@115	655 'voting'::"notify_level"
jbe@115	656 ELSE
jbe@115	657 CASE WHEN "event"."state" IN (
jbe@115	658 'verification',
jbe@115	659 'canceled_after_revocation_during_verification',
jbe@115	660 'canceled_no_initiative_admitted'
jbe@115	661 ) THEN
jbe@115	662 'verification'::"notify_level"
jbe@115	663 ELSE
jbe@115	664 CASE WHEN "event"."state" IN (
jbe@115	665 'discussion',
jbe@115	666 'canceled_after_revocation_during_discussion'
jbe@115	667 ) THEN
jbe@115	668 'discussion'::"notify_level"
jbe@115	669 ELSE
jbe@115	670 'all'::"notify_level"
jbe@115	671 END
jbe@115	672 END
jbe@115	673 END AS "notify_level",
jbe@115	674 "event".*
jbe@115	675 FROM "member" CROSS JOIN "event"
jbe@115	676 LEFT JOIN "issue"
jbe@115	677 ON "event"."issue_id" = "issue"."id"
jbe@115	678 LEFT JOIN "membership"
jbe@115	679 ON "member"."id" = "membership"."member_id"
jbe@115	680 AND "issue"."area_id" = "membership"."area_id"
jbe@115	681 LEFT JOIN "interest"
jbe@115	682 ON "member"."id" = "interest"."member_id"
jbe@115	683 AND "event"."issue_id" = "interest"."issue_id"
jbe@115	684 LEFT JOIN "supporter"
jbe@115	685 ON "member"."id" = "supporter"."member_id"
jbe@115	686 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115	687 LEFT JOIN "ignored_member"
jbe@115	688 ON "member"."id" = "ignored_member"."member_id"
jbe@115	689 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115	690 LEFT JOIN "ignored_initiative"
jbe@115	691 ON "member"."id" = "ignored_initiative"."member_id"
jbe@115	692 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115	693 WHERE (
jbe@115	694 "supporter"."member_id" NOTNULL OR
jbe@115	695 "interest"."member_id" NOTNULL OR
jbe@115	696 ( "membership"."member_id" NOTNULL AND
jbe@115	697 "event"."event" IN (
jbe@115	698 'issue_state_changed',
jbe@115	699 'initiative_created_in_new_issue',
jbe@115	700 'initiative_created_in_existing_issue',
jbe@115	701 'initiative_revoked' ) ) )
jbe@115	702 AND "ignored_member"."member_id" ISNULL
jbe@115	703 AND "ignored_initiative"."member_id" ISNULL;
jbe@115	704
jbe@115	705 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
jbe@115	706
jbe@115	707
jbe@115	708 -- New view "pending_notification":
jbe@115	709
jbe@115	710 CREATE VIEW "pending_notification" AS
jbe@115	711 SELECT
jbe@115	712 "member"."id" AS "seen_by_member_id",
jbe@115	713 "event".*
jbe@115	714 FROM "member" CROSS JOIN "event"
jbe@115	715 LEFT JOIN "issue"
jbe@115	716 ON "event"."issue_id" = "issue"."id"
jbe@115	717 LEFT JOIN "membership"
jbe@115	718 ON "member"."id" = "membership"."member_id"
jbe@115	719 AND "issue"."area_id" = "membership"."area_id"
jbe@115	720 LEFT JOIN "interest"
jbe@115	721 ON "member"."id" = "interest"."member_id"
jbe@115	722 AND "event"."issue_id" = "interest"."issue_id"
jbe@115	723 LEFT JOIN "supporter"
jbe@115	724 ON "member"."id" = "supporter"."member_id"
jbe@115	725 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115	726 LEFT JOIN "ignored_member"
jbe@115	727 ON "member"."id" = "ignored_member"."member_id"
jbe@115	728 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115	729 LEFT JOIN "ignored_initiative"
jbe@115	730 ON "member"."id" = "ignored_initiative"."member_id"
jbe@115	731 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115	732 WHERE (
jbe@115	733 "member"."notify_event_id" ISNULL OR
jbe@115	734 ( "member"."notify_event_id" NOTNULL AND
jbe@115	735 "member"."notify_event_id" < "event"."id" ) )
jbe@115	736 AND (
jbe@115	737 ( "member"."notify_level" >= 'all' ) OR
jbe@115	738 ( "member"."notify_level" >= 'voting' AND
jbe@115	739 "event"."state" IN (
jbe@115	740 'voting',
jbe@115	741 'finished_without_winner',
jbe@115	742 'finished_with_winner' ) ) OR
jbe@115	743 ( "member"."notify_level" >= 'verification' AND
jbe@115	744 "event"."state" IN (
jbe@115	745 'verification',
jbe@115	746 'canceled_after_revocation_during_verification',
jbe@115	747 'canceled_no_initiative_admitted' ) ) OR
jbe@115	748 ( "member"."notify_level" >= 'discussion' AND
jbe@115	749 "event"."state" IN (
jbe@115	750 'discussion',
jbe@115	751 'canceled_after_revocation_during_discussion' ) ) )
jbe@115	752 AND (
jbe@115	753 "supporter"."member_id" NOTNULL OR
jbe@115	754 "interest"."member_id" NOTNULL OR
jbe@115	755 ( "membership"."member_id" NOTNULL AND
jbe@115	756 "event"."event" IN (
jbe@115	757 'issue_state_changed',
jbe@115	758 'initiative_created_in_new_issue',
jbe@115	759 'initiative_created_in_existing_issue',
jbe@115	760 'initiative_revoked' ) ) )
jbe@115	761 AND "ignored_member"."member_id" ISNULL
jbe@115	762 AND "ignored_initiative"."member_id" ISNULL;
jbe@115	763
jbe@115	764 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
jbe@115	765
jbe@115	766
jbe@115	767 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
jbe@115	768 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115	769 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115	770 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115	771 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115	772 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
jbe@115	773
jbe@115	774
jbe@115	775 -- Modified "delegation_chain" functions:
jbe@115	776
jbe@115	777 CREATE TYPE "delegation_chain_row" AS (
jbe@115	778 "index" INT4,
jbe@115	779 "member_id" INT4,
jbe@115	780 "member_valid" BOOLEAN,
jbe@115	781 "participation" BOOLEAN,
jbe@115	782 "overridden" BOOLEAN,
jbe@115	783 "scope_in" "delegation_scope",
jbe@115	784 "scope_out" "delegation_scope",
jbe@115	785 "disabled_out" BOOLEAN,
jbe@115	786 "loop" "delegation_chain_loop_tag" );
jbe@115	787
jbe@115	788 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@115	789
jbe@115	790 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@115	791 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
jbe@115	792 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@115	793 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@115	794 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@115	795 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
jbe@115	796 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@115	797
jbe@115	798
jbe@115	799 CREATE FUNCTION "delegation_chain"
jbe@115	800 ( "member_id_p" "member"."id"%TYPE,
jbe@115	801 "unit_id_p" "unit"."id"%TYPE,
jbe@115	802 "area_id_p" "area"."id"%TYPE,
jbe@115	803 "issue_id_p" "issue"."id"%TYPE,
jbe@115	804 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@115	805 RETURNS SETOF "delegation_chain_row"
jbe@115	806 LANGUAGE 'plpgsql' STABLE AS $$
jbe@115	807 DECLARE
jbe@115	808 "scope_v" "delegation_scope";
jbe@115	809 "unit_id_v" "unit"."id"%TYPE;
jbe@115	810 "area_id_v" "area"."id"%TYPE;
jbe@115	811 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@115	812 "loop_member_id_v" "member"."id"%TYPE;
jbe@115	813 "output_row" "delegation_chain_row";
jbe@115	814 "output_rows" "delegation_chain_row"[];
jbe@115	815 "delegation_row" "delegation"%ROWTYPE;
jbe@115	816 "row_count" INT4;
jbe@115	817 "i" INT4;
jbe@115	818 "loop_v" BOOLEAN;
jbe@115	819 BEGIN
jbe@115	820 IF
jbe@115	821 "unit_id_p" NOTNULL AND
jbe@115	822 "area_id_p" ISNULL AND
jbe@115	823 "issue_id_p" ISNULL
jbe@115	824 THEN
jbe@115	825 "scope_v" := 'unit';
jbe@115	826 "unit_id_v" := "unit_id_p";
jbe@115	827 ELSIF
jbe@115	828 "unit_id_p" ISNULL AND
jbe@115	829 "area_id_p" NOTNULL AND
jbe@115	830 "issue_id_p" ISNULL
jbe@115	831 THEN
jbe@115	832 "scope_v" := 'area';
jbe@115	833 "area_id_v" := "area_id_p";
jbe@115	834 SELECT "unit_id" INTO "unit_id_v"
jbe@115	835 FROM "area" WHERE "id" = "area_id_v";
jbe@115	836 ELSIF
jbe@115	837 "unit_id_p" ISNULL AND
jbe@115	838 "area_id_p" ISNULL AND
jbe@115	839 "issue_id_p" NOTNULL
jbe@115	840 THEN
jbe@115	841 "scope_v" := 'issue';
jbe@115	842 SELECT "area_id" INTO "area_id_v"
jbe@115	843 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	844 SELECT "unit_id" INTO "unit_id_v"
jbe@115	845 FROM "area" WHERE "id" = "area_id_v";
jbe@115	846 ELSE
jbe@115	847 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@115	848 END IF;
jbe@115	849 "visited_member_ids" := '{}';
jbe@115	850 "loop_member_id_v" := NULL;
jbe@115	851 "output_rows" := '{}';
jbe@115	852 "output_row"."index" := 0;
jbe@115	853 "output_row"."member_id" := "member_id_p";
jbe@115	854 "output_row"."member_valid" := TRUE;
jbe@115	855 "output_row"."participation" := FALSE;
jbe@115	856 "output_row"."overridden" := FALSE;
jbe@115	857 "output_row"."disabled_out" := FALSE;
jbe@115	858 "output_row"."scope_out" := NULL;
jbe@115	859 LOOP
jbe@115	860 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@115	861 "loop_member_id_v" := "output_row"."member_id";
jbe@115	862 ELSE
jbe@115	863 "visited_member_ids" :=
jbe@115	864 "visited_member_ids" \|\| "output_row"."member_id";
jbe@115	865 END IF;
jbe@115	866 IF "output_row"."participation" THEN
jbe@115	867 "output_row"."overridden" := TRUE;
jbe@115	868 END IF;
jbe@115	869 "output_row"."scope_in" := "output_row"."scope_out";
jbe@115	870 IF EXISTS (
jbe@115	871 SELECT NULL FROM "member" JOIN "privilege"
jbe@115	872 ON "privilege"."member_id" = "member"."id"
jbe@115	873 AND "privilege"."unit_id" = "unit_id_v"
jbe@115	874 WHERE "id" = "output_row"."member_id"
jbe@115	875 AND "member"."active" AND "privilege"."voting_right"
jbe@115	876 ) THEN
jbe@115	877 IF "scope_v" = 'unit' THEN
jbe@115	878 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115	879 WHERE "truster_id" = "output_row"."member_id"
jbe@115	880 AND "unit_id" = "unit_id_v";
jbe@115	881 ELSIF "scope_v" = 'area' THEN
jbe@115	882 "output_row"."participation" := EXISTS (
jbe@115	883 SELECT NULL FROM "membership"
jbe@115	884 WHERE "area_id" = "area_id_p"
jbe@115	885 AND "member_id" = "output_row"."member_id"
jbe@115	886 );
jbe@115	887 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115	888 WHERE "truster_id" = "output_row"."member_id"
jbe@115	889 AND (
jbe@115	890 "unit_id" = "unit_id_v" OR
jbe@115	891 "area_id" = "area_id_v"
jbe@115	892 )
jbe@115	893 ORDER BY "scope" DESC;
jbe@115	894 ELSIF "scope_v" = 'issue' THEN
jbe@115	895 "output_row"."participation" := EXISTS (
jbe@115	896 SELECT NULL FROM "interest"
jbe@115	897 WHERE "issue_id" = "issue_id_p"
jbe@115	898 AND "member_id" = "output_row"."member_id"
jbe@115	899 );
jbe@115	900 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115	901 WHERE "truster_id" = "output_row"."member_id"
jbe@115	902 AND (
jbe@115	903 "unit_id" = "unit_id_v" OR
jbe@115	904 "area_id" = "area_id_v" OR
jbe@115	905 "issue_id" = "issue_id_p"
jbe@115	906 )
jbe@115	907 ORDER BY "scope" DESC;
jbe@115	908 END IF;
jbe@115	909 ELSE
jbe@115	910 "output_row"."member_valid" := FALSE;
jbe@115	911 "output_row"."participation" := FALSE;
jbe@115	912 "output_row"."scope_out" := NULL;
jbe@115	913 "delegation_row" := ROW(NULL);
jbe@115	914 END IF;
jbe@115	915 IF
jbe@115	916 "output_row"."member_id" = "member_id_p" AND
jbe@115	917 "simulate_trustee_id_p" NOTNULL
jbe@115	918 THEN
jbe@115	919 "output_row"."scope_out" := "scope_v";
jbe@115	920 "output_rows" := "output_rows" \|\| "output_row";
jbe@115	921 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@115	922 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@115	923 "output_row"."scope_out" := "delegation_row"."scope";
jbe@115	924 "output_rows" := "output_rows" \|\| "output_row";
jbe@115	925 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@115	926 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@115	927 "output_row"."scope_out" := "delegation_row"."scope";
jbe@115	928 "output_row"."disabled_out" := TRUE;
jbe@115	929 "output_rows" := "output_rows" \|\| "output_row";
jbe@115	930 EXIT;
jbe@115	931 ELSE
jbe@115	932 "output_row"."scope_out" := NULL;
jbe@115	933 "output_rows" := "output_rows" \|\| "output_row";
jbe@115	934 EXIT;
jbe@115	935 END IF;
jbe@115	936 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@115	937 "output_row"."index" := "output_row"."index" + 1;
jbe@115	938 END LOOP;
jbe@115	939 "row_count" := array_upper("output_rows", 1);
jbe@115	940 "i" := 1;
jbe@115	941 "loop_v" := FALSE;
jbe@115	942 LOOP
jbe@115	943 "output_row" := "output_rows"["i"];
jbe@115	944 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@115	945 IF "loop_v" THEN
jbe@115	946 IF "i" + 1 = "row_count" THEN
jbe@115	947 "output_row"."loop" := 'last';
jbe@115	948 ELSIF "i" = "row_count" THEN
jbe@115	949 "output_row"."loop" := 'repetition';
jbe@115	950 ELSE
jbe@115	951 "output_row"."loop" := 'intermediate';
jbe@115	952 END IF;
jbe@115	953 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@115	954 "output_row"."loop" := 'first';
jbe@115	955 "loop_v" := TRUE;
jbe@115	956 END IF;
jbe@115	957 IF "scope_v" = 'unit' THEN
jbe@115	958 "output_row"."participation" := NULL;
jbe@115	959 END IF;
jbe@115	960 RETURN NEXT "output_row";
jbe@115	961 "i" := "i" + 1;
jbe@115	962 END LOOP;
jbe@115	963 RETURN;
jbe@115	964 END;
jbe@115	965 $$;
jbe@115	966
jbe@115	967 COMMENT ON FUNCTION "delegation_chain"
jbe@115	968 ( "member"."id"%TYPE,
jbe@115	969 "unit"."id"%TYPE,
jbe@115	970 "area"."id"%TYPE,
jbe@115	971 "issue"."id"%TYPE,
jbe@115	972 "member"."id"%TYPE )
jbe@115	973 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@115	974
jbe@115	975
jbe@115	976 CREATE FUNCTION "delegation_chain"
jbe@115	977 ( "member_id_p" "member"."id"%TYPE,
jbe@115	978 "unit_id_p" "unit"."id"%TYPE,
jbe@115	979 "area_id_p" "area"."id"%TYPE,
jbe@115	980 "issue_id_p" "issue"."id"%TYPE )
jbe@115	981 RETURNS SETOF "delegation_chain_row"
jbe@115	982 LANGUAGE 'plpgsql' STABLE AS $$
jbe@115	983 DECLARE
jbe@115	984 "result_row" "delegation_chain_row";
jbe@115	985 BEGIN
jbe@115	986 FOR "result_row" IN
jbe@115	987 SELECT * FROM "delegation_chain"(
jbe@123	988 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
jbe@115	989 )
jbe@115	990 LOOP
jbe@115	991 RETURN NEXT "result_row";
jbe@115	992 END LOOP;
jbe@115	993 RETURN;
jbe@115	994 END;
jbe@115	995 $$;
jbe@115	996
jbe@115	997 COMMENT ON FUNCTION "delegation_chain"
jbe@115	998 ( "member"."id"%TYPE,
jbe@115	999 "unit"."id"%TYPE,
jbe@115	1000 "area"."id"%TYPE,
jbe@115	1001 "issue"."id"%TYPE )
jbe@115	1002 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@115	1003
jbe@115	1004
jbe@122	1005 -- Other modified functions:
jbe@115	1006
jbe@115	1007 CREATE OR REPLACE FUNCTION "lock_issue"
jbe@115	1008 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1009 RETURNS VOID
jbe@115	1010 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1011 BEGIN
jbe@115	1012 LOCK TABLE "member" IN SHARE MODE;
jbe@115	1013 LOCK TABLE "privilege" IN SHARE MODE;
jbe@115	1014 LOCK TABLE "membership" IN SHARE MODE;
jbe@115	1015 LOCK TABLE "policy" IN SHARE MODE;
jbe@115	1016 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115	1017 -- NOTE: The row-level exclusive lock in combination with the
jbe@115	1018 -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@115	1019 -- acquire a row-level share lock on the issue) ensure that no data
jbe@115	1020 -- is changed, which could affect calculation of snapshots or
jbe@115	1021 -- counting of votes. Table "delegation" must be table-level-locked,
jbe@115	1022 -- as it also contains issue- and global-scope delegations.
jbe@115	1023 LOCK TABLE "delegation" IN SHARE MODE;
jbe@115	1024 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@115	1025 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@115	1026 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@115	1027 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@115	1028 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@115	1029 RETURN;
jbe@115	1030 END;
jbe@115	1031 $$;
jbe@115	1032
jbe@115	1033 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@115	1034 RETURNS VOID
jbe@115	1035 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1036 BEGIN
jbe@115	1037 LOCK TABLE "member" IN SHARE MODE;
jbe@115	1038 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@115	1039 LOCK TABLE "unit" IN EXCLUSIVE MODE;
jbe@115	1040 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@115	1041 LOCK TABLE "privilege" IN SHARE MODE;
jbe@115	1042 LOCK TABLE "membership" IN SHARE MODE;
jbe@115	1043 DELETE FROM "member_count";
jbe@115	1044 INSERT INTO "member_count" ("total_count")
jbe@115	1045 SELECT "total_count" FROM "member_count_view";
jbe@115	1046 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@115	1047 FROM "unit_member_count" AS "view"
jbe@115	1048 WHERE "view"."unit_id" = "unit"."id";
jbe@115	1049 UPDATE "area" SET
jbe@115	1050 "direct_member_count" = "view"."direct_member_count",
jbe@115	1051 "member_weight" = "view"."member_weight",
jbe@115	1052 "autoreject_weight" = "view"."autoreject_weight"
jbe@115	1053 FROM "area_member_count" AS "view"
jbe@115	1054 WHERE "view"."area_id" = "area"."id";
jbe@115	1055 RETURN;
jbe@115	1056 END;
jbe@115	1057 $$;
jbe@115	1058
jbe@115	1059 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@115	1060 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1061 RETURNS VOID
jbe@115	1062 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1063 DECLARE
jbe@115	1064 "member_id_v" "member"."id"%TYPE;
jbe@115	1065 BEGIN
jbe@115	1066 DELETE FROM "direct_population_snapshot"
jbe@115	1067 WHERE "issue_id" = "issue_id_p"
jbe@115	1068 AND "event" = 'periodic';
jbe@115	1069 DELETE FROM "delegating_population_snapshot"
jbe@115	1070 WHERE "issue_id" = "issue_id_p"
jbe@115	1071 AND "event" = 'periodic';
jbe@115	1072 INSERT INTO "direct_population_snapshot"
jbe@115	1073 ("issue_id", "event", "member_id")
jbe@115	1074 SELECT
jbe@115	1075 "issue_id_p" AS "issue_id",
jbe@115	1076 'periodic'::"snapshot_event" AS "event",
jbe@115	1077 "member"."id" AS "member_id"
jbe@115	1078 FROM "issue"
jbe@115	1079 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115	1080 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@115	1081 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@115	1082 JOIN "privilege"
jbe@115	1083 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115	1084 AND "privilege"."member_id" = "member"."id"
jbe@115	1085 WHERE "issue"."id" = "issue_id_p"
jbe@115	1086 AND "member"."active" AND "privilege"."voting_right"
jbe@115	1087 UNION
jbe@115	1088 SELECT
jbe@115	1089 "issue_id_p" AS "issue_id",
jbe@115	1090 'periodic'::"snapshot_event" AS "event",
jbe@115	1091 "member"."id" AS "member_id"
jbe@115	1092 FROM "issue"
jbe@115	1093 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115	1094 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115	1095 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115	1096 JOIN "privilege"
jbe@115	1097 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115	1098 AND "privilege"."member_id" = "member"."id"
jbe@115	1099 WHERE "issue"."id" = "issue_id_p"
jbe@115	1100 AND "member"."active" AND "privilege"."voting_right";
jbe@115	1101 FOR "member_id_v" IN
jbe@115	1102 SELECT "member_id" FROM "direct_population_snapshot"
jbe@115	1103 WHERE "issue_id" = "issue_id_p"
jbe@115	1104 AND "event" = 'periodic'
jbe@115	1105 LOOP
jbe@115	1106 UPDATE "direct_population_snapshot" SET
jbe@115	1107 "weight" = 1 +
jbe@115	1108 "weight_of_added_delegations_for_population_snapshot"(
jbe@115	1109 "issue_id_p",
jbe@115	1110 "member_id_v",
jbe@115	1111 '{}'
jbe@115	1112 )
jbe@115	1113 WHERE "issue_id" = "issue_id_p"
jbe@115	1114 AND "event" = 'periodic'
jbe@115	1115 AND "member_id" = "member_id_v";
jbe@115	1116 END LOOP;
jbe@115	1117 RETURN;
jbe@115	1118 END;
jbe@115	1119 $$;
jbe@115	1120
jbe@115	1121 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@115	1122 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1123 RETURNS VOID
jbe@115	1124 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1125 DECLARE
jbe@115	1126 "member_id_v" "member"."id"%TYPE;
jbe@115	1127 BEGIN
jbe@115	1128 DELETE FROM "direct_interest_snapshot"
jbe@115	1129 WHERE "issue_id" = "issue_id_p"
jbe@115	1130 AND "event" = 'periodic';
jbe@115	1131 DELETE FROM "delegating_interest_snapshot"
jbe@115	1132 WHERE "issue_id" = "issue_id_p"
jbe@115	1133 AND "event" = 'periodic';
jbe@115	1134 DELETE FROM "direct_supporter_snapshot"
jbe@115	1135 WHERE "issue_id" = "issue_id_p"
jbe@115	1136 AND "event" = 'periodic';
jbe@115	1137 INSERT INTO "direct_interest_snapshot"
jbe@115	1138 ("issue_id", "event", "member_id", "voting_requested")
jbe@115	1139 SELECT
jbe@115	1140 "issue_id_p" AS "issue_id",
jbe@115	1141 'periodic' AS "event",
jbe@115	1142 "member"."id" AS "member_id",
jbe@115	1143 "interest"."voting_requested"
jbe@115	1144 FROM "issue"
jbe@115	1145 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115	1146 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115	1147 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115	1148 JOIN "privilege"
jbe@115	1149 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115	1150 AND "privilege"."member_id" = "member"."id"
jbe@115	1151 WHERE "issue"."id" = "issue_id_p"
jbe@115	1152 AND "member"."active" AND "privilege"."voting_right";
jbe@115	1153 FOR "member_id_v" IN
jbe@115	1154 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@115	1155 WHERE "issue_id" = "issue_id_p"
jbe@115	1156 AND "event" = 'periodic'
jbe@115	1157 LOOP
jbe@115	1158 UPDATE "direct_interest_snapshot" SET
jbe@115	1159 "weight" = 1 +
jbe@115	1160 "weight_of_added_delegations_for_interest_snapshot"(
jbe@115	1161 "issue_id_p",
jbe@115	1162 "member_id_v",
jbe@115	1163 '{}'
jbe@115	1164 )
jbe@115	1165 WHERE "issue_id" = "issue_id_p"
jbe@115	1166 AND "event" = 'periodic'
jbe@115	1167 AND "member_id" = "member_id_v";
jbe@115	1168 END LOOP;
jbe@115	1169 INSERT INTO "direct_supporter_snapshot"
jbe@115	1170 ( "issue_id", "initiative_id", "event", "member_id",
jbe@115	1171 "informed", "satisfied" )
jbe@115	1172 SELECT
jbe@115	1173 "issue_id_p" AS "issue_id",
jbe@115	1174 "initiative"."id" AS "initiative_id",
jbe@115	1175 'periodic' AS "event",
jbe@115	1176 "supporter"."member_id" AS "member_id",
jbe@115	1177 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@115	1178 NOT EXISTS (
jbe@115	1179 SELECT NULL FROM "critical_opinion"
jbe@115	1180 WHERE "initiative_id" = "initiative"."id"
jbe@115	1181 AND "member_id" = "supporter"."member_id"
jbe@115	1182 ) AS "satisfied"
jbe@115	1183 FROM "initiative"
jbe@115	1184 JOIN "supporter"
jbe@115	1185 ON "supporter"."initiative_id" = "initiative"."id"
jbe@115	1186 JOIN "current_draft"
jbe@115	1187 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@115	1188 JOIN "direct_interest_snapshot"
jbe@115	1189 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@115	1190 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@115	1191 AND "event" = 'periodic'
jbe@115	1192 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@115	1193 RETURN;
jbe@115	1194 END;
jbe@115	1195 $$;
jbe@115	1196
jbe@115	1197 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@115	1198 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1199 RETURNS VOID
jbe@115	1200 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1201 DECLARE
jbe@115	1202 "issue_row" "issue"%ROWTYPE;
jbe@115	1203 "policy_row" "policy"%ROWTYPE;
jbe@115	1204 "initiative_row" "initiative"%ROWTYPE;
jbe@115	1205 BEGIN
jbe@115	1206 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1207 SELECT * INTO "policy_row"
jbe@115	1208 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@115	1209 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@115	1210 FOR "initiative_row" IN
jbe@115	1211 SELECT * FROM "initiative"
jbe@115	1212 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115	1213 LOOP
jbe@115	1214 IF
jbe@115	1215 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@115	1216 "initiative_row"."satisfied_supporter_count" *
jbe@115	1217 "policy_row"."initiative_quorum_den" >=
jbe@115	1218 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@115	1219 THEN
jbe@115	1220 UPDATE "initiative" SET "admitted" = TRUE
jbe@115	1221 WHERE "id" = "initiative_row"."id";
jbe@115	1222 ELSE
jbe@115	1223 UPDATE "initiative" SET "admitted" = FALSE
jbe@115	1224 WHERE "id" = "initiative_row"."id";
jbe@115	1225 END IF;
jbe@115	1226 END LOOP;
jbe@115	1227 IF EXISTS (
jbe@115	1228 SELECT NULL FROM "initiative"
jbe@115	1229 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@115	1230 ) THEN
jbe@115	1231 UPDATE "issue" SET
jbe@115	1232 "state" = 'voting',
jbe@115	1233 "accepted" = coalesce("accepted", now()),
jbe@115	1234 "half_frozen" = coalesce("half_frozen", now()),
jbe@115	1235 "fully_frozen" = now()
jbe@115	1236 WHERE "id" = "issue_id_p";
jbe@115	1237 ELSE
jbe@115	1238 UPDATE "issue" SET
jbe@121	1239 "state" = 'canceled_no_initiative_admitted',
jbe@121	1240 "accepted" = coalesce("accepted", now()),
jbe@121	1241 "half_frozen" = coalesce("half_frozen", now()),
jbe@121	1242 "fully_frozen" = now(),
jbe@121	1243 "closed" = now(),
jbe@121	1244 "ranks_available" = TRUE
jbe@115	1245 WHERE "id" = "issue_id_p";
jbe@115	1246 -- NOTE: The following DELETE statements have effect only when
jbe@115	1247 -- issue state has been manipulated
jbe@115	1248 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@115	1249 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@115	1250 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@115	1251 END IF;
jbe@115	1252 RETURN;
jbe@115	1253 END;
jbe@115	1254 $$;
jbe@115	1255
jbe@115	1256 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@115	1257 RETURNS VOID
jbe@115	1258 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1259 DECLARE
jbe@115	1260 "area_id_v" "area"."id"%TYPE;
jbe@115	1261 "unit_id_v" "unit"."id"%TYPE;
jbe@115	1262 "member_id_v" "member"."id"%TYPE;
jbe@115	1263 BEGIN
jbe@115	1264 PERFORM "lock_issue"("issue_id_p");
jbe@187	1265 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@187	1266 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@115	1267 DELETE FROM "delegating_voter"
jbe@115	1268 WHERE "issue_id" = "issue_id_p";
jbe@115	1269 DELETE FROM "direct_voter"
jbe@115	1270 WHERE "issue_id" = "issue_id_p"
jbe@115	1271 AND "autoreject" = TRUE;
jbe@115	1272 DELETE FROM "direct_voter"
jbe@115	1273 USING (
jbe@115	1274 SELECT
jbe@115	1275 "direct_voter"."member_id"
jbe@115	1276 FROM "direct_voter"
jbe@115	1277 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@115	1278 LEFT JOIN "privilege"
jbe@115	1279 ON "privilege"."unit_id" = "unit_id_v"
jbe@115	1280 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@115	1281 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@115	1282 "member"."active" = FALSE OR
jbe@115	1283 "privilege"."voting_right" ISNULL OR
jbe@115	1284 "privilege"."voting_right" = FALSE
jbe@115	1285 )
jbe@115	1286 ) AS "subquery"
jbe@115	1287 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@115	1288 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@115	1289 UPDATE "direct_voter" SET "weight" = 1
jbe@115	1290 WHERE "issue_id" = "issue_id_p";
jbe@115	1291 PERFORM "add_vote_delegations"("issue_id_p");
jbe@115	1292 FOR "member_id_v" IN
jbe@115	1293 SELECT "interest"."member_id"
jbe@115	1294 FROM "interest"
jbe@115	1295 JOIN "member"
jbe@115	1296 ON "interest"."member_id" = "member"."id"
jbe@187	1297 JOIN "privilege"
jbe@187	1298 ON "privilege"."unit_id" = "unit_id_v"
jbe@187	1299 AND "privilege"."member_id" = "member"."id"
jbe@115	1300 LEFT JOIN "direct_voter"
jbe@115	1301 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@115	1302 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@115	1303 LEFT JOIN "delegating_voter"
jbe@115	1304 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@115	1305 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@115	1306 WHERE "interest"."issue_id" = "issue_id_p"
jbe@115	1307 AND "interest"."autoreject" = TRUE
jbe@115	1308 AND "member"."active"
jbe@187	1309 AND "privilege"."voting_right"
jbe@115	1310 AND "direct_voter"."member_id" ISNULL
jbe@115	1311 AND "delegating_voter"."member_id" ISNULL
jbe@115	1312 UNION SELECT "membership"."member_id"
jbe@115	1313 FROM "membership"
jbe@115	1314 JOIN "member"
jbe@115	1315 ON "membership"."member_id" = "member"."id"
jbe@187	1316 JOIN "privilege"
jbe@187	1317 ON "privilege"."unit_id" = "unit_id_v"
jbe@187	1318 AND "privilege"."member_id" = "member"."id"
jbe@115	1319 LEFT JOIN "interest"
jbe@115	1320 ON "membership"."member_id" = "interest"."member_id"
jbe@115	1321 AND "interest"."issue_id" = "issue_id_p"
jbe@115	1322 LEFT JOIN "direct_voter"
jbe@115	1323 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@115	1324 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@115	1325 LEFT JOIN "delegating_voter"
jbe@115	1326 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@115	1327 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@115	1328 WHERE "membership"."area_id" = "area_id_v"
jbe@115	1329 AND "membership"."autoreject" = TRUE
jbe@115	1330 AND "member"."active"
jbe@187	1331 AND "privilege"."voting_right"
jbe@115	1332 AND "interest"."autoreject" ISNULL
jbe@115	1333 AND "direct_voter"."member_id" ISNULL
jbe@115	1334 AND "delegating_voter"."member_id" ISNULL
jbe@115	1335 LOOP
jbe@115	1336 INSERT INTO "direct_voter"
jbe@115	1337 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@115	1338 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@115	1339 INSERT INTO "vote" (
jbe@115	1340 "member_id",
jbe@115	1341 "issue_id",
jbe@115	1342 "initiative_id",
jbe@115	1343 "grade"
jbe@115	1344 ) SELECT
jbe@115	1345 "member_id_v" AS "member_id",
jbe@115	1346 "issue_id_p" AS "issue_id",
jbe@115	1347 "id" AS "initiative_id",
jbe@115	1348 -1 AS "grade"
jbe@187	1349 FROM "initiative"
jbe@187	1350 WHERE "issue_id" = "issue_id_p" AND "admitted";
jbe@115	1351 END LOOP;
jbe@115	1352 PERFORM "add_vote_delegations"("issue_id_p");
jbe@115	1353 UPDATE "issue" SET
jbe@115	1354 "state" = 'calculation',
jbe@115	1355 "closed" = now(),
jbe@115	1356 "voter_count" = (
jbe@115	1357 SELECT coalesce(sum("weight"), 0)
jbe@115	1358 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@115	1359 )
jbe@115	1360 WHERE "id" = "issue_id_p";
jbe@115	1361 UPDATE "initiative" SET
jbe@115	1362 "positive_votes" = "vote_counts"."positive_votes",
jbe@115	1363 "negative_votes" = "vote_counts"."negative_votes",
jbe@115	1364 "agreed" = CASE WHEN "majority_strict" THEN
jbe@115	1365 "vote_counts"."positive_votes" * "majority_den" >
jbe@115	1366 "majority_num" *
jbe@115	1367 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115	1368 ELSE
jbe@115	1369 "vote_counts"."positive_votes" * "majority_den" >=
jbe@115	1370 "majority_num" *
jbe@115	1371 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115	1372 END
jbe@115	1373 FROM
jbe@115	1374 ( SELECT
jbe@115	1375 "initiative"."id" AS "initiative_id",
jbe@115	1376 coalesce(
jbe@115	1377 sum(
jbe@115	1378 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115	1379 ),
jbe@115	1380 0
jbe@115	1381 ) AS "positive_votes",
jbe@115	1382 coalesce(
jbe@115	1383 sum(
jbe@115	1384 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115	1385 ),
jbe@115	1386 0
jbe@115	1387 ) AS "negative_votes"
jbe@115	1388 FROM "initiative"
jbe@115	1389 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@115	1390 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@115	1391 LEFT JOIN "direct_voter"
jbe@115	1392 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@115	1393 LEFT JOIN "vote"
jbe@115	1394 ON "vote"."initiative_id" = "initiative"."id"
jbe@115	1395 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@115	1396 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@115	1397 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@115	1398 GROUP BY "initiative"."id"
jbe@115	1399 ) AS "vote_counts",
jbe@115	1400 "issue",
jbe@115	1401 "policy"
jbe@115	1402 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@115	1403 AND "issue"."id" = "initiative"."issue_id"
jbe@115	1404 AND "policy"."id" = "issue"."policy_id";
jbe@115	1405 -- NOTE: "closed" column of issue must be set at this point
jbe@115	1406 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@115	1407 INSERT INTO "battle" (
jbe@115	1408 "issue_id",
jbe@115	1409 "winning_initiative_id", "losing_initiative_id",
jbe@115	1410 "count"
jbe@115	1411 ) SELECT
jbe@115	1412 "issue_id",
jbe@115	1413 "winning_initiative_id", "losing_initiative_id",
jbe@115	1414 "count"
jbe@115	1415 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@115	1416 END;
jbe@115	1417 $$;
jbe@115	1418
jbe@115	1419 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@115	1420 RETURNS VOID
jbe@115	1421 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1422 DECLARE
jbe@115	1423 "dimension_v" INTEGER;
jbe@115	1424 "vote_matrix" INT4[][]; -- absolute votes
jbe@115	1425 "matrix" INT8[][]; -- defeat strength / best paths
jbe@115	1426 "i" INTEGER;
jbe@115	1427 "j" INTEGER;
jbe@115	1428 "k" INTEGER;
jbe@115	1429 "battle_row" "battle"%ROWTYPE;
jbe@115	1430 "rank_ary" INT4[];
jbe@115	1431 "rank_v" INT4;
jbe@115	1432 "done_v" INTEGER;
jbe@115	1433 "winners_ary" INTEGER[];
jbe@115	1434 "initiative_id_v" "initiative"."id"%TYPE;
jbe@115	1435 BEGIN
jbe@115	1436 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115	1437 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@115	1438 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115	1439 IF "dimension_v" = 1 THEN
jbe@115	1440 UPDATE "initiative" SET "rank" = 1
jbe@115	1441 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115	1442 ELSIF "dimension_v" > 1 THEN
jbe@115	1443 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@115	1444 -- comparison:
jbe@115	1445 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115	1446 "i" := 1;
jbe@115	1447 "j" := 2;
jbe@115	1448 FOR "battle_row" IN
jbe@115	1449 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@115	1450 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@115	1451 LOOP
jbe@115	1452 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@115	1453 IF "j" = "dimension_v" THEN
jbe@115	1454 "i" := "i" + 1;
jbe@115	1455 "j" := 1;
jbe@115	1456 ELSE
jbe@115	1457 "j" := "j" + 1;
jbe@115	1458 IF "j" = "i" THEN
jbe@115	1459 "j" := "j" + 1;
jbe@115	1460 END IF;
jbe@115	1461 END IF;
jbe@115	1462 END LOOP;
jbe@115	1463 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@115	1464 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@115	1465 END IF;
jbe@115	1466 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@115	1467 -- function:
jbe@115	1468 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115	1469 "i" := 1;
jbe@115	1470 LOOP
jbe@115	1471 "j" := 1;
jbe@115	1472 LOOP
jbe@115	1473 IF "i" != "j" THEN
jbe@115	1474 "matrix"["i"]["j"] := "defeat_strength"(
jbe@115	1475 "vote_matrix"["i"]["j"],
jbe@115	1476 "vote_matrix"["j"]["i"]
jbe@115	1477 );
jbe@115	1478 END IF;
jbe@115	1479 EXIT WHEN "j" = "dimension_v";
jbe@115	1480 "j" := "j" + 1;
jbe@115	1481 END LOOP;
jbe@115	1482 EXIT WHEN "i" = "dimension_v";
jbe@115	1483 "i" := "i" + 1;
jbe@115	1484 END LOOP;
jbe@115	1485 -- Find best paths:
jbe@115	1486 "i" := 1;
jbe@115	1487 LOOP
jbe@115	1488 "j" := 1;
jbe@115	1489 LOOP
jbe@115	1490 IF "i" != "j" THEN
jbe@115	1491 "k" := 1;
jbe@115	1492 LOOP
jbe@115	1493 IF "i" != "k" AND "j" != "k" THEN
jbe@115	1494 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@115	1495 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@115	1496 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@115	1497 END IF;
jbe@115	1498 ELSE
jbe@115	1499 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@115	1500 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@115	1501 END IF;
jbe@115	1502 END IF;
jbe@115	1503 END IF;
jbe@115	1504 EXIT WHEN "k" = "dimension_v";
jbe@115	1505 "k" := "k" + 1;
jbe@115	1506 END LOOP;
jbe@115	1507 END IF;
jbe@115	1508 EXIT WHEN "j" = "dimension_v";
jbe@115	1509 "j" := "j" + 1;
jbe@115	1510 END LOOP;
jbe@115	1511 EXIT WHEN "i" = "dimension_v";
jbe@115	1512 "i" := "i" + 1;
jbe@115	1513 END LOOP;
jbe@115	1514 -- Determine order of winners:
jbe@115	1515 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115	1516 "rank_v" := 1;
jbe@115	1517 "done_v" := 0;
jbe@115	1518 LOOP
jbe@115	1519 "winners_ary" := '{}';
jbe@115	1520 "i" := 1;
jbe@115	1521 LOOP
jbe@115	1522 IF "rank_ary"["i"] ISNULL THEN
jbe@115	1523 "j" := 1;
jbe@115	1524 LOOP
jbe@115	1525 IF
jbe@115	1526 "i" != "j" AND
jbe@115	1527 "rank_ary"["j"] ISNULL AND
jbe@115	1528 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@115	1529 THEN
jbe@115	1530 -- someone else is better
jbe@115	1531 EXIT;
jbe@115	1532 END IF;
jbe@115	1533 IF "j" = "dimension_v" THEN
jbe@115	1534 -- noone is better
jbe@115	1535 "winners_ary" := "winners_ary" \|\| "i";
jbe@115	1536 EXIT;
jbe@115	1537 END IF;
jbe@115	1538 "j" := "j" + 1;
jbe@115	1539 END LOOP;
jbe@115	1540 END IF;
jbe@115	1541 EXIT WHEN "i" = "dimension_v";
jbe@115	1542 "i" := "i" + 1;
jbe@115	1543 END LOOP;
jbe@115	1544 "i" := 1;
jbe@115	1545 LOOP
jbe@115	1546 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@115	1547 "done_v" := "done_v" + 1;
jbe@115	1548 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@115	1549 "i" := "i" + 1;
jbe@115	1550 END LOOP;
jbe@115	1551 EXIT WHEN "done_v" = "dimension_v";
jbe@115	1552 "rank_v" := "rank_v" + 1;
jbe@115	1553 END LOOP;
jbe@115	1554 -- write preliminary ranks:
jbe@115	1555 "i" := 1;
jbe@115	1556 FOR "initiative_id_v" IN
jbe@115	1557 SELECT "id" FROM "initiative"
jbe@115	1558 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@115	1559 ORDER BY "id"
jbe@115	1560 LOOP
jbe@115	1561 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@115	1562 WHERE "id" = "initiative_id_v";
jbe@115	1563 "i" := "i" + 1;
jbe@115	1564 END LOOP;
jbe@115	1565 IF "i" != "dimension_v" + 1 THEN
jbe@115	1566 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@115	1567 END IF;
jbe@115	1568 -- straighten ranks (start counting with 1, no equal ranks):
jbe@115	1569 "rank_v" := 1;
jbe@115	1570 FOR "initiative_id_v" IN
jbe@115	1571 SELECT "id" FROM "initiative"
jbe@115	1572 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@115	1573 ORDER BY
jbe@115	1574 "rank",
jbe@115	1575 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@115	1576 "id"
jbe@115	1577 LOOP
jbe@115	1578 UPDATE "initiative" SET "rank" = "rank_v"
jbe@115	1579 WHERE "id" = "initiative_id_v";
jbe@115	1580 "rank_v" := "rank_v" + 1;
jbe@115	1581 END LOOP;
jbe@115	1582 END IF;
jbe@115	1583 -- mark issue as finished
jbe@115	1584 UPDATE "issue" SET
jbe@115	1585 "state" =
jbe@121	1586 CASE WHEN "dimension_v" = 0 THEN
jbe@121	1587 'finished_without_winner'::"issue_state"
jbe@115	1588 ELSE
jbe@121	1589 'finished_with_winner'::"issue_state"
jbe@115	1590 END,
jbe@115	1591 "ranks_available" = TRUE
jbe@115	1592 WHERE "id" = "issue_id_p";
jbe@115	1593 RETURN;
jbe@115	1594 END;
jbe@115	1595 $$;
jbe@115	1596
jbe@115	1597 CREATE OR REPLACE FUNCTION "check_issue"
jbe@115	1598 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1599 RETURNS VOID
jbe@115	1600 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1601 DECLARE
jbe@115	1602 "issue_row" "issue"%ROWTYPE;
jbe@115	1603 "policy_row" "policy"%ROWTYPE;
jbe@115	1604 "voting_requested_v" BOOLEAN;
jbe@115	1605 BEGIN
jbe@115	1606 PERFORM "lock_issue"("issue_id_p");
jbe@115	1607 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1608 -- only process open issues:
jbe@115	1609 IF "issue_row"."closed" ISNULL THEN
jbe@115	1610 SELECT * INTO "policy_row" FROM "policy"
jbe@115	1611 WHERE "id" = "issue_row"."policy_id";
jbe@115	1612 -- create a snapshot, unless issue is already fully frozen:
jbe@115	1613 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115	1614 PERFORM "create_snapshot"("issue_id_p");
jbe@115	1615 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1616 END IF;
jbe@115	1617 -- eventually close or accept issues, which have not been accepted:
jbe@115	1618 IF "issue_row"."accepted" ISNULL THEN
jbe@115	1619 IF EXISTS (
jbe@115	1620 SELECT NULL FROM "initiative"
jbe@115	1621 WHERE "issue_id" = "issue_id_p"
jbe@115	1622 AND "supporter_count" > 0
jbe@115	1623 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115	1624 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115	1625 ) THEN
jbe@115	1626 -- accept issues, if supporter count is high enough
jbe@115	1627 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115	1628 -- NOTE: "issue_row" used later
jbe@115	1629 "issue_row"."state" := 'discussion';
jbe@115	1630 "issue_row"."accepted" := now();
jbe@115	1631 UPDATE "issue" SET
jbe@115	1632 "state" = "issue_row"."state",
jbe@115	1633 "accepted" = "issue_row"."accepted"
jbe@115	1634 WHERE "id" = "issue_row"."id";
jbe@115	1635 ELSIF
jbe@115	1636 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115	1637 THEN
jbe@115	1638 -- close issues, if admission time has expired
jbe@115	1639 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115	1640 UPDATE "issue" SET
jbe@115	1641 "state" = 'canceled_issue_not_accepted',
jbe@115	1642 "closed" = now()
jbe@115	1643 WHERE "id" = "issue_row"."id";
jbe@115	1644 END IF;
jbe@115	1645 END IF;
jbe@115	1646 -- eventually half freeze issues:
jbe@115	1647 IF
jbe@115	1648 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115	1649 "issue_row"."accepted" NOTNULL AND
jbe@115	1650 "issue_row"."half_frozen" ISNULL
jbe@115	1651 THEN
jbe@115	1652 SELECT
jbe@115	1653 CASE
jbe@115	1654 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115	1655 TRUE
jbe@115	1656 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115	1657 FALSE
jbe@115	1658 ELSE NULL
jbe@115	1659 END
jbe@115	1660 INTO "voting_requested_v"
jbe@115	1661 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1662 IF
jbe@115	1663 "voting_requested_v" OR (
jbe@115	1664 "voting_requested_v" ISNULL AND
jbe@115	1665 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115	1666 )
jbe@115	1667 THEN
jbe@115	1668 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115	1669 -- NOTE: "issue_row" used later
jbe@115	1670 "issue_row"."state" := 'verification';
jbe@115	1671 "issue_row"."half_frozen" := now();
jbe@115	1672 UPDATE "issue" SET
jbe@115	1673 "state" = "issue_row"."state",
jbe@115	1674 "half_frozen" = "issue_row"."half_frozen"
jbe@115	1675 WHERE "id" = "issue_row"."id";
jbe@115	1676 END IF;
jbe@115	1677 END IF;
jbe@115	1678 -- close issues after some time, if all initiatives have been revoked:
jbe@115	1679 IF
jbe@115	1680 "issue_row"."closed" ISNULL AND
jbe@115	1681 NOT EXISTS (
jbe@115	1682 -- all initiatives are revoked
jbe@115	1683 SELECT NULL FROM "initiative"
jbe@115	1684 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115	1685 ) AND (
jbe@115	1686 -- and issue has not been accepted yet
jbe@115	1687 "issue_row"."accepted" ISNULL OR
jbe@115	1688 NOT EXISTS (
jbe@115	1689 -- or no initiatives have been revoked lately
jbe@115	1690 SELECT NULL FROM "initiative"
jbe@115	1691 WHERE "issue_id" = "issue_id_p"
jbe@115	1692 AND now() < "revoked" + "issue_row"."verification_time"
jbe@115	1693 ) OR (
jbe@115	1694 -- or verification time has elapsed
jbe@115	1695 "issue_row"."half_frozen" NOTNULL AND
jbe@115	1696 "issue_row"."fully_frozen" ISNULL AND
jbe@115	1697 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115	1698 )
jbe@115	1699 )
jbe@115	1700 THEN
jbe@115	1701 -- NOTE: "issue_row" used later
jbe@115	1702 IF "issue_row"."accepted" ISNULL THEN
jbe@115	1703 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115	1704 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115	1705 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115	1706 ELSE
jbe@115	1707 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115	1708 END IF;
jbe@115	1709 "issue_row"."closed" := now();
jbe@115	1710 UPDATE "issue" SET
jbe@115	1711 "state" = "issue_row"."state",
jbe@115	1712 "closed" = "issue_row"."closed"
jbe@115	1713 WHERE "id" = "issue_row"."id";
jbe@115	1714 END IF;
jbe@115	1715 -- fully freeze issue after verification time:
jbe@115	1716 IF
jbe@115	1717 "issue_row"."half_frozen" NOTNULL AND
jbe@115	1718 "issue_row"."fully_frozen" ISNULL AND
jbe@115	1719 "issue_row"."closed" ISNULL AND
jbe@115	1720 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115	1721 THEN
jbe@115	1722 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115	1723 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115	1724 END IF;
jbe@115	1725 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1726 -- close issue by calling close_voting(...) after voting time:
jbe@115	1727 IF
jbe@115	1728 "issue_row"."closed" ISNULL AND
jbe@115	1729 "issue_row"."fully_frozen" NOTNULL AND
jbe@115	1730 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115	1731 THEN
jbe@115	1732 PERFORM "close_voting"("issue_id_p");
jbe@115	1733 -- calculate ranks will not consume much time and can be done now
jbe@115	1734 PERFORM "calculate_ranks"("issue_id_p");
jbe@115	1735 END IF;
jbe@115	1736 END IF;
jbe@115	1737 RETURN;
jbe@115	1738 END;
jbe@115	1739 $$;
jbe@115	1740
jbe@115	1741 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@115	1742 RETURNS VOID
jbe@115	1743 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1744 DECLARE
jbe@115	1745 "issue_row" "issue"%ROWTYPE;
jbe@115	1746 BEGIN
jbe@115	1747 SELECT * INTO "issue_row"
jbe@115	1748 FROM "issue" WHERE "id" = "issue_id_p"
jbe@115	1749 FOR UPDATE;
jbe@115	1750 IF "issue_row"."cleaned" ISNULL THEN
jbe@115	1751 UPDATE "issue" SET
jbe@187	1752 "state" = 'voting',
jbe@187	1753 "closed" = NULL,
jbe@115	1754 "ranks_available" = FALSE
jbe@115	1755 WHERE "id" = "issue_id_p";
jbe@187	1756 DELETE FROM "issue_comment"
jbe@187	1757 WHERE "issue_id" = "issue_id_p";
jbe@187	1758 DELETE FROM "voting_comment"
jbe@187	1759 WHERE "issue_id" = "issue_id_p";
jbe@115	1760 DELETE FROM "delegating_voter"
jbe@115	1761 WHERE "issue_id" = "issue_id_p";
jbe@115	1762 DELETE FROM "direct_voter"
jbe@115	1763 WHERE "issue_id" = "issue_id_p";
jbe@115	1764 DELETE FROM "delegating_interest_snapshot"
jbe@115	1765 WHERE "issue_id" = "issue_id_p";
jbe@115	1766 DELETE FROM "direct_interest_snapshot"
jbe@115	1767 WHERE "issue_id" = "issue_id_p";
jbe@115	1768 DELETE FROM "delegating_population_snapshot"
jbe@115	1769 WHERE "issue_id" = "issue_id_p";
jbe@115	1770 DELETE FROM "direct_population_snapshot"
jbe@115	1771 WHERE "issue_id" = "issue_id_p";
jbe@115	1772 DELETE FROM "non_voter"
jbe@115	1773 WHERE "issue_id" = "issue_id_p";
jbe@115	1774 DELETE FROM "delegation"
jbe@115	1775 WHERE "issue_id" = "issue_id_p";
jbe@115	1776 DELETE FROM "supporter"
jbe@115	1777 WHERE "issue_id" = "issue_id_p";
jbe@115	1778 UPDATE "issue" SET
jbe@187	1779 "state" = "issue_row"."state",
jbe@115	1780 "closed" = "issue_row"."closed",
jbe@115	1781 "ranks_available" = "issue_row"."ranks_available",
jbe@115	1782 "cleaned" = now()
jbe@115	1783 WHERE "id" = "issue_id_p";
jbe@115	1784 END IF;
jbe@115	1785 RETURN;
jbe@115	1786 END;
jbe@115	1787 $$;
jbe@115	1788
jbe@115	1789 CREATE OR REPLACE FUNCTION "check_issue"
jbe@115	1790 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115	1791 RETURNS VOID
jbe@115	1792 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1793 DECLARE
jbe@115	1794 "issue_row" "issue"%ROWTYPE;
jbe@115	1795 "policy_row" "policy"%ROWTYPE;
jbe@115	1796 "voting_requested_v" BOOLEAN;
jbe@115	1797 BEGIN
jbe@115	1798 PERFORM "lock_issue"("issue_id_p");
jbe@115	1799 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1800 -- only process open issues:
jbe@115	1801 IF "issue_row"."closed" ISNULL THEN
jbe@115	1802 SELECT * INTO "policy_row" FROM "policy"
jbe@115	1803 WHERE "id" = "issue_row"."policy_id";
jbe@115	1804 -- create a snapshot, unless issue is already fully frozen:
jbe@115	1805 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115	1806 PERFORM "create_snapshot"("issue_id_p");
jbe@115	1807 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1808 END IF;
jbe@115	1809 -- eventually close or accept issues, which have not been accepted:
jbe@115	1810 IF "issue_row"."accepted" ISNULL THEN
jbe@115	1811 IF EXISTS (
jbe@115	1812 SELECT NULL FROM "initiative"
jbe@115	1813 WHERE "issue_id" = "issue_id_p"
jbe@115	1814 AND "supporter_count" > 0
jbe@115	1815 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115	1816 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115	1817 ) THEN
jbe@115	1818 -- accept issues, if supporter count is high enough
jbe@115	1819 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115	1820 -- NOTE: "issue_row" used later
jbe@115	1821 "issue_row"."state" := 'discussion';
jbe@115	1822 "issue_row"."accepted" := now();
jbe@115	1823 UPDATE "issue" SET
jbe@115	1824 "state" = "issue_row"."state",
jbe@115	1825 "accepted" = "issue_row"."accepted"
jbe@115	1826 WHERE "id" = "issue_row"."id";
jbe@115	1827 ELSIF
jbe@115	1828 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115	1829 THEN
jbe@115	1830 -- close issues, if admission time has expired
jbe@115	1831 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115	1832 UPDATE "issue" SET
jbe@115	1833 "state" = 'canceled_issue_not_accepted',
jbe@115	1834 "closed" = now()
jbe@115	1835 WHERE "id" = "issue_row"."id";
jbe@115	1836 END IF;
jbe@115	1837 END IF;
jbe@115	1838 -- eventually half freeze issues:
jbe@115	1839 IF
jbe@115	1840 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115	1841 "issue_row"."accepted" NOTNULL AND
jbe@115	1842 "issue_row"."half_frozen" ISNULL
jbe@115	1843 THEN
jbe@115	1844 SELECT
jbe@115	1845 CASE
jbe@115	1846 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115	1847 TRUE
jbe@115	1848 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115	1849 FALSE
jbe@115	1850 ELSE NULL
jbe@115	1851 END
jbe@115	1852 INTO "voting_requested_v"
jbe@115	1853 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1854 IF
jbe@115	1855 "voting_requested_v" OR (
jbe@115	1856 "voting_requested_v" ISNULL AND
jbe@115	1857 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115	1858 )
jbe@115	1859 THEN
jbe@115	1860 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115	1861 -- NOTE: "issue_row" used later
jbe@115	1862 "issue_row"."state" := 'verification';
jbe@115	1863 "issue_row"."half_frozen" := now();
jbe@115	1864 UPDATE "issue" SET
jbe@115	1865 "state" = "issue_row"."state",
jbe@115	1866 "half_frozen" = "issue_row"."half_frozen"
jbe@115	1867 WHERE "id" = "issue_row"."id";
jbe@115	1868 END IF;
jbe@115	1869 END IF;
jbe@115	1870 -- close issues after some time, if all initiatives have been revoked:
jbe@115	1871 IF
jbe@115	1872 "issue_row"."closed" ISNULL AND
jbe@115	1873 NOT EXISTS (
jbe@115	1874 -- all initiatives are revoked
jbe@115	1875 SELECT NULL FROM "initiative"
jbe@115	1876 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115	1877 ) AND (
jbe@115	1878 -- and issue has not been accepted yet
jbe@115	1879 "issue_row"."accepted" ISNULL OR
jbe@115	1880 NOT EXISTS (
jbe@115	1881 -- or no initiatives have been revoked lately
jbe@115	1882 SELECT NULL FROM "initiative"
jbe@115	1883 WHERE "issue_id" = "issue_id_p"
jbe@115	1884 AND now() < "revoked" + "issue_row"."verification_time"
jbe@115	1885 ) OR (
jbe@115	1886 -- or verification time has elapsed
jbe@115	1887 "issue_row"."half_frozen" NOTNULL AND
jbe@115	1888 "issue_row"."fully_frozen" ISNULL AND
jbe@115	1889 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115	1890 )
jbe@115	1891 )
jbe@115	1892 THEN
jbe@115	1893 -- NOTE: "issue_row" used later
jbe@115	1894 IF "issue_row"."accepted" ISNULL THEN
jbe@115	1895 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115	1896 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115	1897 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115	1898 ELSE
jbe@115	1899 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115	1900 END IF;
jbe@115	1901 "issue_row"."closed" := now();
jbe@115	1902 UPDATE "issue" SET
jbe@115	1903 "state" = "issue_row"."state",
jbe@115	1904 "closed" = "issue_row"."closed"
jbe@115	1905 WHERE "id" = "issue_row"."id";
jbe@115	1906 END IF;
jbe@115	1907 -- fully freeze issue after verification time:
jbe@115	1908 IF
jbe@115	1909 "issue_row"."half_frozen" NOTNULL AND
jbe@115	1910 "issue_row"."fully_frozen" ISNULL AND
jbe@115	1911 "issue_row"."closed" ISNULL AND
jbe@115	1912 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115	1913 THEN
jbe@115	1914 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115	1915 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115	1916 END IF;
jbe@115	1917 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115	1918 -- close issue by calling close_voting(...) after voting time:
jbe@115	1919 IF
jbe@115	1920 "issue_row"."closed" ISNULL AND
jbe@115	1921 "issue_row"."fully_frozen" NOTNULL AND
jbe@115	1922 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115	1923 THEN
jbe@115	1924 PERFORM "close_voting"("issue_id_p");
jbe@115	1925 -- calculate ranks will not consume much time and can be done now
jbe@115	1926 PERFORM "calculate_ranks"("issue_id_p");
jbe@115	1927 END IF;
jbe@115	1928 END IF;
jbe@115	1929 RETURN;
jbe@115	1930 END;
jbe@115	1931 $$;
jbe@115	1932
jbe@115	1933 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@115	1934 RETURNS VOID
jbe@115	1935 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115	1936 BEGIN
jbe@115	1937 UPDATE "member" SET
jbe@115	1938 "last_login" = NULL,
jbe@115	1939 "login" = NULL,
jbe@115	1940 "password" = NULL,
jbe@115	1941 "notify_email" = NULL,
jbe@115	1942 "notify_email_unconfirmed" = NULL,
jbe@115	1943 "notify_email_secret" = NULL,
jbe@115	1944 "notify_email_secret_expiry" = NULL,
jbe@115	1945 "notify_email_lock_expiry" = NULL,
jbe@115	1946 "password_reset_secret" = NULL,
jbe@115	1947 "password_reset_secret_expiry" = NULL,
jbe@115	1948 "organizational_unit" = NULL,
jbe@115	1949 "internal_posts" = NULL,
jbe@115	1950 "realname" = NULL,
jbe@115	1951 "birthday" = NULL,
jbe@115	1952 "address" = NULL,
jbe@115	1953 "email" = NULL,
jbe@115	1954 "xmpp_address" = NULL,
jbe@115	1955 "website" = NULL,
jbe@115	1956 "phone" = NULL,
jbe@115	1957 "mobile_phone" = NULL,
jbe@115	1958 "profession" = NULL,
jbe@115	1959 "external_memberships" = NULL,
jbe@115	1960 "external_posts" = NULL,
jbe@115	1961 "statement" = NULL;
jbe@115	1962 -- "text_search_data" is updated by triggers
jbe@115	1963 DELETE FROM "invite_code";
jbe@115	1964 DELETE FROM "setting";
jbe@115	1965 DELETE FROM "setting_map";
jbe@115	1966 DELETE FROM "member_relation_setting";
jbe@115	1967 DELETE FROM "member_image";
jbe@115	1968 DELETE FROM "contact";
jbe@115	1969 DELETE FROM "ignored_member";
jbe@115	1970 DELETE FROM "session";
jbe@115	1971 DELETE FROM "area_setting";
jbe@115	1972 DELETE FROM "issue_setting";
jbe@115	1973 DELETE FROM "ignored_initiative";
jbe@115	1974 DELETE FROM "initiative_setting";
jbe@115	1975 DELETE FROM "suggestion_setting";
jbe@115	1976 DELETE FROM "non_voter";
jbe@115	1977 DELETE FROM "direct_voter" USING "issue"
jbe@115	1978 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@115	1979 AND "issue"."closed" ISNULL;
jbe@115	1980 RETURN;
jbe@115	1981 END;
jbe@115	1982 $$;
jbe@115	1983
jbe@115	1984
jbe@115	1985 -- Delete old "delegation_scope" TYPE:
jbe@115	1986
jbe@115	1987 DROP TYPE "delegation_scope_old";
jbe@115	1988
jbe@115	1989
jbe@115	1990 COMMIT;
jbe@115	1991
jbe@115	1992
jbe@122	1993 -- Generate issue states and add constraints:
jbe@115	1994
jbe@115	1995 UPDATE "issue" SET "state" =
jbe@115	1996 CASE
jbe@115	1997 WHEN "closed" ISNULL THEN
jbe@115	1998 CASE
jbe@115	1999 WHEN "accepted" ISNULL THEN
jbe@115	2000 'admission'::"issue_state"
jbe@115	2001 WHEN "half_frozen" ISNULL THEN
jbe@115	2002 'discussion'::"issue_state"
jbe@115	2003 WHEN "fully_frozen" ISNULL THEN
jbe@115	2004 'verification'::"issue_state"
jbe@115	2005 ELSE
jbe@115	2006 'voting'::"issue_state"
jbe@115	2007 END
jbe@115	2008 WHEN "fully_frozen" NOTNULL THEN
jbe@115	2009 CASE
jbe@115	2010 WHEN "fully_frozen" = "closed" THEN
jbe@115	2011 'canceled_no_initiative_admitted'::"issue_state"
jbe@115	2012 ELSE
jbe@115	2013 'finished_without_winner'::"issue_state" -- NOTE: corrected later
jbe@115	2014 END
jbe@115	2015 WHEN "half_frozen" NOTNULL THEN
jbe@115	2016 'canceled_after_revocation_during_verification'::"issue_state"
jbe@115	2017 WHEN "accepted" NOTNULL THEN
jbe@115	2018 'canceled_after_revocation_during_discussion'::"issue_state"
jbe@115	2019 ELSE
jbe@115	2020 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
jbe@115	2021 END;
jbe@117	2022 UPDATE "issue" SET "state" = 'finished_with_winner'
jbe@117	2023 FROM "initiative"
jbe@117	2024 WHERE "issue"."id" = "initiative"."issue_id"
jbe@117	2025 AND "issue"."state" = 'finished_without_winner'
jbe@117	2026 AND "initiative"."agreed";
jbe@117	2027 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
jbe@117	2028 FROM "initiative"
jbe@117	2029 WHERE "issue"."id" = "initiative"."issue_id"
jbe@117	2030 AND "issue"."state" = 'canceled_revoked_before_accepted'
jbe@117	2031 AND "initiative"."revoked" ISNULL;
jbe@115	2032
jbe@115	2033 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
jbe@115	2034
jbe@115	2035 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
jbe@115	2036 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
jbe@115	2037 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115	2038 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115	2039 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115	2040 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115	2041 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115	2042 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115	2043 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115	2044 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115	2045 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
jbe@115	2046 ) AND (
jbe@115	2047 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@115	2048 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@115	2049 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115	2050 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@115	2051 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115	2052 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115	2053 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
jbe@115	2054 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115	2055 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115	2056 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115	2057 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115	2058 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
jbe@115	2059 ));
jbe@115	2060
jbe@115	2061
jbe@122	2062 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
jbe@115	2063
jbe@115	2064 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
jbe@115	2065 FROM "current_draft"
jbe@115	2066 WHERE "initiative"."id" = "current_draft"."initiative_id"
jbe@115	2067 AND "initiative"."revoked" NOTNULL;
jbe@115	2068
jbe@115	2069 ALTER TABLE "initiative" ADD
jbe@115	2070 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
jbe@115	2071 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
jbe@115	2072
jbe@115	2073
jbe@122	2074 -- Fill "unit_id" column with default value where neccessary and add constraints:
jbe@115	2075
jbe@115	2076 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
jbe@115	2077
jbe@115	2078 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
jbe@115	2079 CHECK (
jbe@115	2080 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
jbe@115	2081 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@115	2082 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );
jbe@117	2083
jbe@117	2084
jbe@118	2085 -- Filling of "event" table with old (reconstructed) events:
jbe@118	2086
jbe@118	2087 DELETE FROM "event";
jbe@118	2088 SELECT setval('event_id_seq', 1, false);
jbe@118	2089
jbe@118	2090 INSERT INTO "event"
jbe@118	2091 ( "occurrence", "event", "member_id", "issue_id", "state",
jbe@118	2092 "initiative_id", "draft_id", "suggestion_id" )
jbe@118	2093 SELECT * FROM (
jbe@118	2094 SELECT * FROM (
jbe@118	2095 SELECT DISTINCT ON ("initiative"."id")
jbe@118	2096 "timeline"."occurrence",
jbe@118	2097 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
jbe@118	2098 'initiative_created_in_new_issue'::"event_type"
jbe@118	2099 ELSE
jbe@118	2100 'initiative_created_in_existing_issue'::"event_type"
jbe@118	2101 END,
jbe@118	2102 "draft"."author_id",
jbe@118	2103 "issue"."id",
jbe@118	2104 CASE
jbe@118	2105 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118	2106 'admission'::"issue_state"
jbe@118	2107 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118	2108 'discussion'::"issue_state"
jbe@118	2109 ELSE
jbe@118	2110 'verification'::"issue_state"
jbe@118	2111 END,
jbe@118	2112 "initiative"."id",
jbe@118	2113 "draft"."id",
jbe@118	2114 NULL::INT8
jbe@118	2115 FROM "timeline"
jbe@118	2116 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118	2117 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118	2118 LEFT JOIN "timeline" AS "issue_creation"
jbe@118	2119 ON "initiative"."issue_id" = "issue_creation"."issue_id"
jbe@118	2120 AND "issue_creation"."event" = 'issue_created'
jbe@118	2121 AND "timeline"."occurrence" = "issue_creation"."occurrence"
jbe@118	2122 JOIN "draft"
jbe@118	2123 ON "initiative"."id" = "draft"."initiative_id"
jbe@118	2124 WHERE "timeline"."event" = 'initiative_created'
jbe@118	2125 ORDER BY "initiative"."id", "draft"."id"
jbe@118	2126 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER
jbe@118	2127 UNION ALL
jbe@118	2128 SELECT
jbe@118	2129 "timeline"."occurrence",
jbe@118	2130 'issue_state_changed'::"event_type",
jbe@118	2131 NULL,
jbe@118	2132 "issue"."id",
jbe@118	2133 CASE
jbe@118	2134 WHEN "timeline"."event" IN (
jbe@118	2135 'issue_canceled',
jbe@118	2136 'issue_finished_without_voting',
jbe@118	2137 'issue_finished_after_voting'
jbe@118	2138 ) THEN
jbe@118	2139 "issue"."state"
jbe@118	2140 WHEN "timeline"."event" = 'issue_accepted' THEN
jbe@118	2141 'discussion'::"issue_state"
jbe@118	2142 WHEN "timeline"."event" = 'issue_half_frozen' THEN
jbe@118	2143 'verification'::"issue_state"
jbe@118	2144 WHEN "timeline"."event" = 'issue_voting_started' THEN
jbe@118	2145 'voting'::"issue_state"
jbe@118	2146 END,
jbe@118	2147 NULL,
jbe@118	2148 NULL,
jbe@118	2149 NULL
jbe@118	2150 FROM "timeline"
jbe@118	2151 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
jbe@118	2152 WHERE "timeline"."event" IN (
jbe@118	2153 'issue_canceled',
jbe@118	2154 'issue_accepted',
jbe@118	2155 'issue_half_frozen',
jbe@118	2156 'issue_finished_without_voting',
jbe@118	2157 'issue_voting_started',
jbe@118	2158 'issue_finished_after_voting' )
jbe@118	2159 UNION ALL
jbe@118	2160 SELECT
jbe@118	2161 "timeline"."occurrence",
jbe@118	2162 'initiative_revoked'::"event_type",
jbe@118	2163 "initiative"."revoked_by_member_id",
jbe@118	2164 "issue"."id",
jbe@118	2165 CASE
jbe@118	2166 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118	2167 'admission'::"issue_state"
jbe@118	2168 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118	2169 'discussion'::"issue_state"
jbe@118	2170 ELSE
jbe@118	2171 'verification'::"issue_state"
jbe@118	2172 END,
jbe@118	2173 "initiative"."id",
jbe@118	2174 "current_draft"."id",
jbe@118	2175 NULL
jbe@118	2176 FROM "timeline"
jbe@118	2177 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118	2178 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118	2179 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
jbe@118	2180 WHERE "timeline"."event" = 'initiative_revoked'
jbe@118	2181 UNION ALL
jbe@118	2182 SELECT
jbe@118	2183 "timeline"."occurrence",
jbe@118	2184 'new_draft_created'::"event_type",
jbe@118	2185 "draft"."author_id",
jbe@118	2186 "issue"."id",
jbe@118	2187 CASE
jbe@118	2188 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118	2189 'admission'::"issue_state"
jbe@118	2190 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118	2191 'discussion'::"issue_state"
jbe@118	2192 ELSE
jbe@118	2193 'verification'::"issue_state"
jbe@118	2194 END,
jbe@118	2195 "initiative"."id",
jbe@118	2196 "draft"."id",
jbe@118	2197 NULL
jbe@118	2198 FROM "timeline"
jbe@118	2199 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
jbe@118	2200 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
jbe@118	2201 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118	2202 LEFT JOIN "timeline" AS "initiative_creation"
jbe@118	2203 ON "initiative"."id" = "initiative_creation"."initiative_id"
jbe@118	2204 AND "initiative_creation"."event" = 'initiative_created'
jbe@118	2205 AND "timeline"."occurrence" = "initiative_creation"."occurrence"
jbe@118	2206 WHERE "timeline"."event" = 'draft_created'
jbe@118	2207 AND "initiative_creation"."initiative_id" ISNULL
jbe@118	2208 UNION ALL
jbe@118	2209 SELECT
jbe@118	2210 "timeline"."occurrence",
jbe@118	2211 'suggestion_created'::"event_type",
jbe@118	2212 "suggestion"."author_id",
jbe@118	2213 "issue"."id",
jbe@118	2214 CASE
jbe@118	2215 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118	2216 'admission'::"issue_state"
jbe@118	2217 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118	2218 'discussion'::"issue_state"
jbe@118	2219 ELSE
jbe@118	2220 'verification'::"issue_state"
jbe@118	2221 END,
jbe@118	2222 "initiative"."id",
jbe@118	2223 NULL,
jbe@118	2224 "suggestion"."id"
jbe@118	2225 FROM "timeline"
jbe@118	2226 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
jbe@118	2227 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
jbe@118	2228 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118	2229 WHERE "timeline"."event" = 'suggestion_created'
jbe@118	2230 ) AS "subquery"
jbe@118	2231 ORDER BY "occurrence";
jbe@118	2232
jbe@118	2233