liquid_feedback_core
changeset 187:aaf5f62b1aa2
Added update script from v1.3.1 to v1.4.0_rc4
author | jbe |
---|---|
date | Sat Jul 30 01:54:59 2011 +0200 (2011-07-30) |
parents | 969ce2bea98c |
children | 8b496fa85a65 |
files | update/core-update.v1.3.1-v1.4.0_rc4.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.3.1-v1.4.0_rc4.sql Sat Jul 30 01:54:59 2011 +0200 1.3 @@ -0,0 +1,2232 @@ 1.4 +BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block! 1.5 + 1.6 + 1.7 +-- Update version information: 1.8 + 1.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.10 + SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1)) 1.11 + AS "subquery"("string", "major", "minor", "revision"); 1.12 + 1.13 + 1.14 +-- New columns "notify_level" and "notify_event_id" in "member" table: 1.15 + 1.16 +CREATE TYPE "notify_level" AS ENUM 1.17 + ('none', 'voting', 'verification', 'discussion', 'all'); 1.18 + 1.19 +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'; 1.20 + 1.21 +ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none'; 1.22 +ALTER TABLE "member" ADD "notify_event_id" INT8; 1.23 + 1.24 +COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; 1.25 +COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; 1.26 + 1.27 + 1.28 +-- Add primary key with type SERIAL8 (INT8) for "invite_code" table: 1.29 + 1.30 +ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey"; 1.31 +ALTER TABLE "invite_code" ALTER "code" SET NOT NULL; 1.32 +ALTER TABLE "invite_code" ADD UNIQUE ("code"); 1.33 +ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY; 1.34 + 1.35 + 1.36 +-- Add index for "other_member_id" column of "contact" table: 1.37 + 1.38 +CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); 1.39 + 1.40 + 1.41 +-- New table "ignored_member": 1.42 + 1.43 +CREATE TABLE "ignored_member" ( 1.44 + PRIMARY KEY ("member_id", "other_member_id"), 1.45 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 + "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.47 +CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id"); 1.48 + 1.49 +COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members'; 1.50 + 1.51 +COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone'; 1.52 +COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; 1.53 + 1.54 + 1.55 +-- New table "unit" with default entry: 1.56 + 1.57 +CREATE TABLE "unit" ( 1.58 + "id" SERIAL4 PRIMARY KEY, 1.59 + "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.60 + "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.61 + "name" TEXT NOT NULL, 1.62 + "description" TEXT NOT NULL DEFAULT '', 1.63 + "member_count" INT4, 1.64 + "text_search_data" TSVECTOR ); 1.65 +CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.66 +CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.67 +CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.68 +CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 1.69 +CREATE TRIGGER "update_text_search_data" 1.70 + BEFORE INSERT OR UPDATE ON "unit" 1.71 + FOR EACH ROW EXECUTE PROCEDURE 1.72 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.73 + "name", "description" ); 1.74 + 1.75 +COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; 1.76 + 1.77 +COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; 1.78 +COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area'; 1.79 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; 1.80 + 1.81 +INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1 1.82 + 1.83 + 1.84 +-- New column "unit_id" in table "area": 1.85 + 1.86 +ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1 1.87 + NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 1.88 +ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT; 1.89 + 1.90 +CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); 1.91 + 1.92 + 1.93 +-- Issue states: 1.94 + 1.95 +CREATE TYPE "issue_state" AS ENUM ( 1.96 + 'admission', 'discussion', 'verification', 'voting', 1.97 + 'canceled_revoked_before_accepted', 1.98 + 'canceled_issue_not_accepted', 1.99 + 'canceled_after_revocation_during_discussion', 1.100 + 'canceled_after_revocation_during_verification', 1.101 + 'calculation', 1.102 + 'canceled_no_initiative_admitted', 1.103 + 'finished_without_winner', 'finished_with_winner'); 1.104 + 1.105 +COMMENT ON TYPE "issue_state" IS 'State of issues'; 1.106 + 1.107 +ALTER TABLE "issue" ADD "state" "issue_state"; 1.108 + 1.109 +-- NOTE: Filling new column with values is done after this transaction (see below) 1.110 + 1.111 + 1.112 +-- New column "revoked_by_member_id" in table "initiative": 1.113 + 1.114 +ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 1.115 + 1.116 +COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative'; 1.117 + 1.118 +-- NOTE: Filling new column with values is done after this transaction (see below) 1.119 + 1.120 + 1.121 +-- New table "ignored_initiative": 1.122 + 1.123 +CREATE TABLE "ignored_initiative" ( 1.124 + PRIMARY KEY ("initiative_id", "member_id"), 1.125 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.126 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.127 +CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id"); 1.128 + 1.129 +COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; 1.130 + 1.131 + 1.132 +-- New table "invite_code_unit": 1.133 + 1.134 +CREATE TABLE "invite_code_unit" ( 1.135 + PRIMARY KEY ("invite_code_id", "unit_id"), 1.136 + "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.137 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.138 + 1.139 +COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; 1.140 + 1.141 +INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id") 1.142 + SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code"; 1.143 + 1.144 + 1.145 +-- New table "privilege": 1.146 + 1.147 +CREATE TABLE "privilege" ( 1.148 + PRIMARY KEY ("unit_id", "member_id"), 1.149 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.150 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.151 + "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.152 + "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.153 + "area_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.154 + "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE, 1.155 + "voting_right" BOOLEAN NOT NULL DEFAULT TRUE ); 1.156 + 1.157 +COMMENT ON TABLE "privilege" IS 'Members rights related to each unit'; 1.158 + 1.159 +COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users'; 1.160 +COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units'; 1.161 +COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters'; 1.162 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit'; 1.163 +COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; 1.164 + 1.165 +INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") 1.166 + SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" 1.167 + FROM "member"; 1.168 + 1.169 + 1.170 +-- Remove table "ignored_issue", which is no longer existent: 1.171 + 1.172 +DROP TABLE "ignored_issue"; 1.173 + 1.174 + 1.175 +-- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit': 1.176 + 1.177 +ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later 1.178 +CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue'); 1.179 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)'; 1.180 + 1.181 + 1.182 +-- Delete views and functions being dependent on type "delegation_scope": 1.183 + 1.184 +DROP FUNCTION "delegation_chain" 1.185 + ( "member_id_p" "member"."id"%TYPE, 1.186 + "area_id_p" "area"."id"%TYPE, 1.187 + "issue_id_p" "issue"."id"%TYPE ); 1.188 + 1.189 +DROP FUNCTION "delegation_chain" 1.190 + ( "member_id_p" "member"."id"%TYPE, 1.191 + "area_id_p" "area"."id"%TYPE, 1.192 + "issue_id_p" "issue"."id"%TYPE, 1.193 + "simulate_trustee_id_p" "member"."id"%TYPE ); 1.194 + 1.195 +DROP TYPE "delegation_chain_row"; 1.196 + 1.197 +DROP VIEW "issue_delegation"; 1.198 +DROP VIEW "area_delegation"; 1.199 +DROP VIEW "global_delegation"; 1.200 +DROP VIEW "active_delegation"; 1.201 + 1.202 + 1.203 +-- Modify "delegation" table to use new "delegation_scope" type: 1.204 + 1.205 +ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null"; 1.206 +ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope"; 1.207 + 1.208 +DROP INDEX "delegation_global_truster_id_unique_idx"; 1.209 + 1.210 +ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope" 1.211 + USING CASE WHEN "scope" = 'global' 1.212 + THEN 'unit'::"delegation_scope" 1.213 + ELSE "scope"::text::"delegation_scope" END; 1.214 + 1.215 +ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 1.216 + 1.217 +ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null" 1.218 + CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'); 1.219 + 1.220 +ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id"); 1.221 + 1.222 +COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL'; 1.223 + 1.224 +-- NOTE: Column "unit_id" filled after transaction (see below) 1.225 + 1.226 + 1.227 +-- Modify snapshot tables to use new "delegation_scope" type: 1.228 + 1.229 +ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope" 1.230 + USING CASE WHEN "scope" = 'global' 1.231 + THEN 'unit'::"delegation_scope" 1.232 + ELSE "scope"::text::"delegation_scope" END; 1.233 + 1.234 +ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope" 1.235 + USING CASE WHEN "scope" = 'global' 1.236 + THEN 'unit'::"delegation_scope" 1.237 + ELSE "scope"::text::"delegation_scope" END; 1.238 + 1.239 +ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope" 1.240 + USING CASE WHEN "scope" = 'global' 1.241 + THEN 'unit'::"delegation_scope" 1.242 + ELSE "scope"::text::"delegation_scope" END; 1.243 + 1.244 + 1.245 +-- New table "non_voter": 1.246 + 1.247 +CREATE TABLE "non_voter" ( 1.248 + PRIMARY KEY ("issue_id", "member_id"), 1.249 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.250 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.251 +CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); 1.252 + 1.253 +COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; 1.254 + 1.255 + 1.256 +-- New tables "issue_comment" and "rendered_issue_comment": 1.257 + 1.258 +CREATE TABLE "issue_comment" ( 1.259 + PRIMARY KEY ("issue_id", "member_id"), 1.260 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.261 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.262 + "changed" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.263 + "formatting_engine" TEXT, 1.264 + "content" TEXT NOT NULL, 1.265 + "text_search_data" TSVECTOR ); 1.266 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id"); 1.267 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data"); 1.268 +CREATE TRIGGER "update_text_search_data" 1.269 + BEFORE INSERT OR UPDATE ON "issue_comment" 1.270 + FOR EACH ROW EXECUTE PROCEDURE 1.271 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.272 + 1.273 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues'; 1.274 + 1.275 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed'; 1.276 + 1.277 +CREATE TABLE "rendered_issue_comment" ( 1.278 + PRIMARY KEY ("issue_id", "member_id", "format"), 1.279 + FOREIGN KEY ("issue_id", "member_id") 1.280 + REFERENCES "issue_comment" ("issue_id", "member_id") 1.281 + ON DELETE CASCADE ON UPDATE CASCADE, 1.282 + "issue_id" INT4, 1.283 + "member_id" INT4, 1.284 + "format" TEXT, 1.285 + "content" TEXT NOT NULL ); 1.286 + 1.287 +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)'; 1.288 + 1.289 + 1.290 +-- New tables "voting_comment" and "rendered_voting_comment": 1.291 + 1.292 +CREATE TABLE "voting_comment" ( 1.293 + PRIMARY KEY ("issue_id", "member_id"), 1.294 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.295 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.296 + "changed" TIMESTAMPTZ, 1.297 + "formatting_engine" TEXT, 1.298 + "content" TEXT NOT NULL, 1.299 + "text_search_data" TSVECTOR ); 1.300 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id"); 1.301 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data"); 1.302 +CREATE TRIGGER "update_text_search_data" 1.303 + BEFORE INSERT OR UPDATE ON "voting_comment" 1.304 + FOR EACH ROW EXECUTE PROCEDURE 1.305 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.306 + 1.307 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.'; 1.308 + 1.309 +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.'; 1.310 + 1.311 +CREATE TABLE "rendered_voting_comment" ( 1.312 + PRIMARY KEY ("issue_id", "member_id", "format"), 1.313 + FOREIGN KEY ("issue_id", "member_id") 1.314 + REFERENCES "voting_comment" ("issue_id", "member_id") 1.315 + ON DELETE CASCADE ON UPDATE CASCADE, 1.316 + "issue_id" INT4, 1.317 + "member_id" INT4, 1.318 + "format" TEXT, 1.319 + "content" TEXT NOT NULL ); 1.320 + 1.321 +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)'; 1.322 + 1.323 + 1.324 +-- New table "event": 1.325 + 1.326 +CREATE TYPE "event_type" AS ENUM ( 1.327 + 'issue_state_changed', 1.328 + 'initiative_created_in_new_issue', 1.329 + 'initiative_created_in_existing_issue', 1.330 + 'initiative_revoked', 1.331 + 'new_draft_created', 1.332 + 'suggestion_created'); 1.333 + 1.334 +COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; 1.335 + 1.336 +CREATE TABLE "event" ( 1.337 + "id" SERIAL8 PRIMARY KEY, 1.338 + "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.339 + "event" "event_type" NOT NULL, 1.340 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.341 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.342 + "state" "issue_state" CHECK ("state" != 'calculation'), 1.343 + "initiative_id" INT4, 1.344 + "draft_id" INT8, 1.345 + "suggestion_id" INT8, 1.346 + FOREIGN KEY ("issue_id", "initiative_id") 1.347 + REFERENCES "initiative" ("issue_id", "id") 1.348 + ON DELETE CASCADE ON UPDATE CASCADE, 1.349 + FOREIGN KEY ("initiative_id", "draft_id") 1.350 + REFERENCES "draft" ("initiative_id", "id") 1.351 + ON DELETE CASCADE ON UPDATE CASCADE, 1.352 + FOREIGN KEY ("initiative_id", "suggestion_id") 1.353 + REFERENCES "suggestion" ("initiative_id", "id") 1.354 + ON DELETE CASCADE ON UPDATE CASCADE, 1.355 + CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( 1.356 + "event" != 'issue_state_changed' OR ( 1.357 + "member_id" ISNULL AND 1.358 + "issue_id" NOTNULL AND 1.359 + "state" NOTNULL AND 1.360 + "initiative_id" ISNULL AND 1.361 + "draft_id" ISNULL AND 1.362 + "suggestion_id" ISNULL )), 1.363 + CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 1.364 + "event" NOT IN ( 1.365 + 'initiative_created_in_new_issue', 1.366 + 'initiative_created_in_existing_issue', 1.367 + 'initiative_revoked', 1.368 + 'new_draft_created' 1.369 + ) OR ( 1.370 + "member_id" NOTNULL AND 1.371 + "issue_id" NOTNULL AND 1.372 + "state" NOTNULL AND 1.373 + "initiative_id" NOTNULL AND 1.374 + "draft_id" NOTNULL AND 1.375 + "suggestion_id" ISNULL )), 1.376 + CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( 1.377 + "event" != 'suggestion_created' OR ( 1.378 + "member_id" NOTNULL AND 1.379 + "issue_id" NOTNULL AND 1.380 + "state" NOTNULL AND 1.381 + "initiative_id" NOTNULL AND 1.382 + "draft_id" ISNULL AND 1.383 + "suggestion_id" NOTNULL )) ); 1.384 + 1.385 +COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; 1.386 + 1.387 +COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred'; 1.388 +COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")'; 1.389 +COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable'; 1.390 +COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state'; 1.391 + 1.392 + 1.393 +-- Triggers to fill "event" table: 1.394 + 1.395 +CREATE FUNCTION "write_event_issue_state_changed_trigger"() 1.396 + RETURNS TRIGGER 1.397 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.398 + BEGIN 1.399 + IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN 1.400 + INSERT INTO "event" ("event", "issue_id", "state") 1.401 + VALUES ('issue_state_changed', NEW."id", NEW."state"); 1.402 + END IF; 1.403 + RETURN NULL; 1.404 + END; 1.405 + $$; 1.406 + 1.407 +CREATE TRIGGER "write_event_issue_state_changed" 1.408 + AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE 1.409 + "write_event_issue_state_changed_trigger"(); 1.410 + 1.411 +COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"'; 1.412 +COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change'; 1.413 + 1.414 +CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"() 1.415 + RETURNS TRIGGER 1.416 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.417 + DECLARE 1.418 + "initiative_row" "initiative"%ROWTYPE; 1.419 + "issue_row" "issue"%ROWTYPE; 1.420 + "event_v" "event_type"; 1.421 + BEGIN 1.422 + SELECT * INTO "initiative_row" FROM "initiative" 1.423 + WHERE "id" = NEW."initiative_id"; 1.424 + SELECT * INTO "issue_row" FROM "issue" 1.425 + WHERE "id" = "initiative_row"."issue_id"; 1.426 + IF EXISTS ( 1.427 + SELECT NULL FROM "draft" 1.428 + WHERE "initiative_id" = NEW."initiative_id" 1.429 + AND "id" != NEW."id" 1.430 + ) THEN 1.431 + "event_v" := 'new_draft_created'; 1.432 + ELSE 1.433 + IF EXISTS ( 1.434 + SELECT NULL FROM "initiative" 1.435 + WHERE "issue_id" = "initiative_row"."issue_id" 1.436 + AND "id" != "initiative_row"."id" 1.437 + ) THEN 1.438 + "event_v" := 'initiative_created_in_existing_issue'; 1.439 + ELSE 1.440 + "event_v" := 'initiative_created_in_new_issue'; 1.441 + END IF; 1.442 + END IF; 1.443 + INSERT INTO "event" ( 1.444 + "event", "member_id", 1.445 + "issue_id", "state", "initiative_id", "draft_id" 1.446 + ) VALUES ( 1.447 + "event_v", 1.448 + NEW."author_id", 1.449 + "initiative_row"."issue_id", 1.450 + "issue_row"."state", 1.451 + "initiative_row"."id", 1.452 + NEW."id" ); 1.453 + RETURN NULL; 1.454 + END; 1.455 + $$; 1.456 + 1.457 +CREATE TRIGGER "write_event_initiative_or_draft_created" 1.458 + AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE 1.459 + "write_event_initiative_or_draft_created_trigger"(); 1.460 + 1.461 +COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"'; 1.462 +COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation'; 1.463 + 1.464 +CREATE FUNCTION "write_event_initiative_revoked_trigger"() 1.465 + RETURNS TRIGGER 1.466 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.467 + DECLARE 1.468 + "issue_row" "issue"%ROWTYPE; 1.469 + BEGIN 1.470 + SELECT * INTO "issue_row" FROM "issue" 1.471 + WHERE "id" = NEW."issue_id"; 1.472 + IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN 1.473 + INSERT INTO "event" ( 1.474 + "event", "member_id", "issue_id", "state", "initiative_id" 1.475 + ) VALUES ( 1.476 + 'initiative_revoked', 1.477 + NEW."revoked_by_member_id", 1.478 + NEW."issue_id", 1.479 + "issue_row"."state", 1.480 + NEW."id" ); 1.481 + END IF; 1.482 + RETURN NULL; 1.483 + END; 1.484 + $$; 1.485 + 1.486 +CREATE TRIGGER "write_event_initiative_revoked" 1.487 + AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE 1.488 + "write_event_initiative_revoked_trigger"(); 1.489 + 1.490 +COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"'; 1.491 +COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked'; 1.492 + 1.493 +CREATE FUNCTION "write_event_suggestion_created_trigger"() 1.494 + RETURNS TRIGGER 1.495 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.496 + DECLARE 1.497 + "initiative_row" "initiative"%ROWTYPE; 1.498 + "issue_row" "issue"%ROWTYPE; 1.499 + BEGIN 1.500 + SELECT * INTO "initiative_row" FROM "initiative" 1.501 + WHERE "id" = NEW."initiative_id"; 1.502 + SELECT * INTO "issue_row" FROM "issue" 1.503 + WHERE "id" = "initiative_row"."issue_id"; 1.504 + INSERT INTO "event" ( 1.505 + "event", "member_id", 1.506 + "issue_id", "state", "initiative_id", "suggestion_id" 1.507 + ) VALUES ( 1.508 + 'suggestion_created', 1.509 + NEW."author_id", 1.510 + "initiative_row"."issue_id", 1.511 + "issue_row"."state", 1.512 + "initiative_row"."id", 1.513 + NEW."id" ); 1.514 + RETURN NULL; 1.515 + END; 1.516 + $$; 1.517 + 1.518 +CREATE TRIGGER "write_event_suggestion_created" 1.519 + AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE 1.520 + "write_event_suggestion_created_trigger"(); 1.521 + 1.522 +COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"'; 1.523 +COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation'; 1.524 + 1.525 + 1.526 +-- Modified views: 1.527 + 1.528 +CREATE VIEW "unit_delegation" AS 1.529 + SELECT 1.530 + "unit"."id" AS "unit_id", 1.531 + "delegation"."id", 1.532 + "delegation"."truster_id", 1.533 + "delegation"."trustee_id", 1.534 + "delegation"."scope" 1.535 + FROM "unit" 1.536 + JOIN "delegation" 1.537 + ON "delegation"."unit_id" = "unit"."id" 1.538 + JOIN "member" 1.539 + ON "delegation"."truster_id" = "member"."id" 1.540 + JOIN "privilege" 1.541 + ON "delegation"."unit_id" = "privilege"."unit_id" 1.542 + AND "delegation"."truster_id" = "privilege"."member_id" 1.543 + WHERE "member"."active" AND "privilege"."voting_right"; 1.544 + 1.545 +COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right'; 1.546 + 1.547 +CREATE VIEW "area_delegation" AS 1.548 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.549 + "area"."id" AS "area_id", 1.550 + "delegation"."id", 1.551 + "delegation"."truster_id", 1.552 + "delegation"."trustee_id", 1.553 + "delegation"."scope" 1.554 + FROM "area" 1.555 + JOIN "delegation" 1.556 + ON "delegation"."unit_id" = "area"."unit_id" 1.557 + OR "delegation"."area_id" = "area"."id" 1.558 + JOIN "member" 1.559 + ON "delegation"."truster_id" = "member"."id" 1.560 + JOIN "privilege" 1.561 + ON "area"."unit_id" = "privilege"."unit_id" 1.562 + AND "delegation"."truster_id" = "privilege"."member_id" 1.563 + WHERE "member"."active" AND "privilege"."voting_right" 1.564 + ORDER BY 1.565 + "area"."id", 1.566 + "delegation"."truster_id", 1.567 + "delegation"."scope" DESC; 1.568 + 1.569 +COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right'; 1.570 + 1.571 +CREATE VIEW "issue_delegation" AS 1.572 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.573 + "issue"."id" AS "issue_id", 1.574 + "delegation"."id", 1.575 + "delegation"."truster_id", 1.576 + "delegation"."trustee_id", 1.577 + "delegation"."scope" 1.578 + FROM "issue" 1.579 + JOIN "area" 1.580 + ON "area"."id" = "issue"."area_id" 1.581 + JOIN "delegation" 1.582 + ON "delegation"."unit_id" = "area"."unit_id" 1.583 + OR "delegation"."area_id" = "area"."id" 1.584 + OR "delegation"."issue_id" = "issue"."id" 1.585 + JOIN "member" 1.586 + ON "delegation"."truster_id" = "member"."id" 1.587 + JOIN "privilege" 1.588 + ON "area"."unit_id" = "privilege"."unit_id" 1.589 + AND "delegation"."truster_id" = "privilege"."member_id" 1.590 + WHERE "member"."active" AND "privilege"."voting_right" 1.591 + ORDER BY 1.592 + "issue"."id", 1.593 + "delegation"."truster_id", 1.594 + "delegation"."scope" DESC; 1.595 + 1.596 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 1.597 + 1.598 +CREATE VIEW "unit_member_count" AS 1.599 + SELECT 1.600 + "unit"."id" AS "unit_id", 1.601 + sum("member"."id") AS "member_count" 1.602 + FROM "unit" 1.603 + LEFT JOIN "privilege" 1.604 + ON "privilege"."unit_id" = "unit"."id" 1.605 + AND "privilege"."voting_right" 1.606 + LEFT JOIN "member" 1.607 + ON "member"."id" = "privilege"."member_id" 1.608 + AND "member"."active" 1.609 + GROUP BY "unit"."id"; 1.610 + 1.611 +COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; 1.612 + 1.613 +DROP VIEW "area_member_count"; 1.614 +CREATE VIEW "area_member_count" AS 1.615 + SELECT 1.616 + "area"."id" AS "area_id", 1.617 + count("member"."id") AS "direct_member_count", 1.618 + coalesce( 1.619 + sum( 1.620 + CASE WHEN "member"."id" NOTNULL THEN 1.621 + "membership_weight"("area"."id", "member"."id") 1.622 + ELSE 0 END 1.623 + ) 1.624 + ) AS "member_weight", 1.625 + coalesce( 1.626 + sum( 1.627 + CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN 1.628 + "membership_weight"("area"."id", "member"."id") 1.629 + ELSE 0 END 1.630 + ) 1.631 + ) AS "autoreject_weight" 1.632 + FROM "area" 1.633 + LEFT JOIN "membership" 1.634 + ON "area"."id" = "membership"."area_id" 1.635 + LEFT JOIN "privilege" 1.636 + ON "privilege"."unit_id" = "area"."unit_id" 1.637 + AND "privilege"."member_id" = "membership"."member_id" 1.638 + AND "privilege"."voting_right" 1.639 + LEFT JOIN "member" 1.640 + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 1.641 + AND "member"."active" 1.642 + GROUP BY "area"."id"; 1.643 + 1.644 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"'; 1.645 + 1.646 + 1.647 +-- New view "event_seen_by_member": 1.648 + 1.649 +CREATE VIEW "event_seen_by_member" AS 1.650 + SELECT 1.651 + "member"."id" AS "seen_by_member_id", 1.652 + CASE WHEN "event"."state" IN ( 1.653 + 'voting', 1.654 + 'finished_without_winner', 1.655 + 'finished_with_winner' 1.656 + ) THEN 1.657 + 'voting'::"notify_level" 1.658 + ELSE 1.659 + CASE WHEN "event"."state" IN ( 1.660 + 'verification', 1.661 + 'canceled_after_revocation_during_verification', 1.662 + 'canceled_no_initiative_admitted' 1.663 + ) THEN 1.664 + 'verification'::"notify_level" 1.665 + ELSE 1.666 + CASE WHEN "event"."state" IN ( 1.667 + 'discussion', 1.668 + 'canceled_after_revocation_during_discussion' 1.669 + ) THEN 1.670 + 'discussion'::"notify_level" 1.671 + ELSE 1.672 + 'all'::"notify_level" 1.673 + END 1.674 + END 1.675 + END AS "notify_level", 1.676 + "event".* 1.677 + FROM "member" CROSS JOIN "event" 1.678 + LEFT JOIN "issue" 1.679 + ON "event"."issue_id" = "issue"."id" 1.680 + LEFT JOIN "membership" 1.681 + ON "member"."id" = "membership"."member_id" 1.682 + AND "issue"."area_id" = "membership"."area_id" 1.683 + LEFT JOIN "interest" 1.684 + ON "member"."id" = "interest"."member_id" 1.685 + AND "event"."issue_id" = "interest"."issue_id" 1.686 + LEFT JOIN "supporter" 1.687 + ON "member"."id" = "supporter"."member_id" 1.688 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.689 + LEFT JOIN "ignored_member" 1.690 + ON "member"."id" = "ignored_member"."member_id" 1.691 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.692 + LEFT JOIN "ignored_initiative" 1.693 + ON "member"."id" = "ignored_initiative"."member_id" 1.694 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.695 + WHERE ( 1.696 + "supporter"."member_id" NOTNULL OR 1.697 + "interest"."member_id" NOTNULL OR 1.698 + ( "membership"."member_id" NOTNULL AND 1.699 + "event"."event" IN ( 1.700 + 'issue_state_changed', 1.701 + 'initiative_created_in_new_issue', 1.702 + 'initiative_created_in_existing_issue', 1.703 + 'initiative_revoked' ) ) ) 1.704 + AND "ignored_member"."member_id" ISNULL 1.705 + AND "ignored_initiative"."member_id" ISNULL; 1.706 + 1.707 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; 1.708 + 1.709 + 1.710 +-- New view "pending_notification": 1.711 + 1.712 +CREATE VIEW "pending_notification" AS 1.713 + SELECT 1.714 + "member"."id" AS "seen_by_member_id", 1.715 + "event".* 1.716 + FROM "member" CROSS JOIN "event" 1.717 + LEFT JOIN "issue" 1.718 + ON "event"."issue_id" = "issue"."id" 1.719 + LEFT JOIN "membership" 1.720 + ON "member"."id" = "membership"."member_id" 1.721 + AND "issue"."area_id" = "membership"."area_id" 1.722 + LEFT JOIN "interest" 1.723 + ON "member"."id" = "interest"."member_id" 1.724 + AND "event"."issue_id" = "interest"."issue_id" 1.725 + LEFT JOIN "supporter" 1.726 + ON "member"."id" = "supporter"."member_id" 1.727 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.728 + LEFT JOIN "ignored_member" 1.729 + ON "member"."id" = "ignored_member"."member_id" 1.730 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.731 + LEFT JOIN "ignored_initiative" 1.732 + ON "member"."id" = "ignored_initiative"."member_id" 1.733 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.734 + WHERE ( 1.735 + "member"."notify_event_id" ISNULL OR 1.736 + ( "member"."notify_event_id" NOTNULL AND 1.737 + "member"."notify_event_id" < "event"."id" ) ) 1.738 + AND ( 1.739 + ( "member"."notify_level" >= 'all' ) OR 1.740 + ( "member"."notify_level" >= 'voting' AND 1.741 + "event"."state" IN ( 1.742 + 'voting', 1.743 + 'finished_without_winner', 1.744 + 'finished_with_winner' ) ) OR 1.745 + ( "member"."notify_level" >= 'verification' AND 1.746 + "event"."state" IN ( 1.747 + 'verification', 1.748 + 'canceled_after_revocation_during_verification', 1.749 + 'canceled_no_initiative_admitted' ) ) OR 1.750 + ( "member"."notify_level" >= 'discussion' AND 1.751 + "event"."state" IN ( 1.752 + 'discussion', 1.753 + 'canceled_after_revocation_during_discussion' ) ) ) 1.754 + AND ( 1.755 + "supporter"."member_id" NOTNULL OR 1.756 + "interest"."member_id" NOTNULL OR 1.757 + ( "membership"."member_id" NOTNULL AND 1.758 + "event"."event" IN ( 1.759 + 'issue_state_changed', 1.760 + 'initiative_created_in_new_issue', 1.761 + 'initiative_created_in_existing_issue', 1.762 + 'initiative_revoked' ) ) ) 1.763 + AND "ignored_member"."member_id" ISNULL 1.764 + AND "ignored_initiative"."member_id" ISNULL; 1.765 + 1.766 +COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; 1.767 + 1.768 + 1.769 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)'; 1.770 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.771 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.772 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.773 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.774 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)'; 1.775 + 1.776 + 1.777 +-- Modified "delegation_chain" functions: 1.778 + 1.779 +CREATE TYPE "delegation_chain_row" AS ( 1.780 + "index" INT4, 1.781 + "member_id" INT4, 1.782 + "member_valid" BOOLEAN, 1.783 + "participation" BOOLEAN, 1.784 + "overridden" BOOLEAN, 1.785 + "scope_in" "delegation_scope", 1.786 + "scope_out" "delegation_scope", 1.787 + "disabled_out" BOOLEAN, 1.788 + "loop" "delegation_chain_loop_tag" ); 1.789 + 1.790 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 1.791 + 1.792 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 1.793 +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'; 1.794 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 1.795 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 1.796 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 1.797 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 1.798 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 1.799 + 1.800 + 1.801 +CREATE FUNCTION "delegation_chain" 1.802 + ( "member_id_p" "member"."id"%TYPE, 1.803 + "unit_id_p" "unit"."id"%TYPE, 1.804 + "area_id_p" "area"."id"%TYPE, 1.805 + "issue_id_p" "issue"."id"%TYPE, 1.806 + "simulate_trustee_id_p" "member"."id"%TYPE ) 1.807 + RETURNS SETOF "delegation_chain_row" 1.808 + LANGUAGE 'plpgsql' STABLE AS $$ 1.809 + DECLARE 1.810 + "scope_v" "delegation_scope"; 1.811 + "unit_id_v" "unit"."id"%TYPE; 1.812 + "area_id_v" "area"."id"%TYPE; 1.813 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.814 + "loop_member_id_v" "member"."id"%TYPE; 1.815 + "output_row" "delegation_chain_row"; 1.816 + "output_rows" "delegation_chain_row"[]; 1.817 + "delegation_row" "delegation"%ROWTYPE; 1.818 + "row_count" INT4; 1.819 + "i" INT4; 1.820 + "loop_v" BOOLEAN; 1.821 + BEGIN 1.822 + IF 1.823 + "unit_id_p" NOTNULL AND 1.824 + "area_id_p" ISNULL AND 1.825 + "issue_id_p" ISNULL 1.826 + THEN 1.827 + "scope_v" := 'unit'; 1.828 + "unit_id_v" := "unit_id_p"; 1.829 + ELSIF 1.830 + "unit_id_p" ISNULL AND 1.831 + "area_id_p" NOTNULL AND 1.832 + "issue_id_p" ISNULL 1.833 + THEN 1.834 + "scope_v" := 'area'; 1.835 + "area_id_v" := "area_id_p"; 1.836 + SELECT "unit_id" INTO "unit_id_v" 1.837 + FROM "area" WHERE "id" = "area_id_v"; 1.838 + ELSIF 1.839 + "unit_id_p" ISNULL AND 1.840 + "area_id_p" ISNULL AND 1.841 + "issue_id_p" NOTNULL 1.842 + THEN 1.843 + "scope_v" := 'issue'; 1.844 + SELECT "area_id" INTO "area_id_v" 1.845 + FROM "issue" WHERE "id" = "issue_id_p"; 1.846 + SELECT "unit_id" INTO "unit_id_v" 1.847 + FROM "area" WHERE "id" = "area_id_v"; 1.848 + ELSE 1.849 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 1.850 + END IF; 1.851 + "visited_member_ids" := '{}'; 1.852 + "loop_member_id_v" := NULL; 1.853 + "output_rows" := '{}'; 1.854 + "output_row"."index" := 0; 1.855 + "output_row"."member_id" := "member_id_p"; 1.856 + "output_row"."member_valid" := TRUE; 1.857 + "output_row"."participation" := FALSE; 1.858 + "output_row"."overridden" := FALSE; 1.859 + "output_row"."disabled_out" := FALSE; 1.860 + "output_row"."scope_out" := NULL; 1.861 + LOOP 1.862 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.863 + "loop_member_id_v" := "output_row"."member_id"; 1.864 + ELSE 1.865 + "visited_member_ids" := 1.866 + "visited_member_ids" || "output_row"."member_id"; 1.867 + END IF; 1.868 + IF "output_row"."participation" THEN 1.869 + "output_row"."overridden" := TRUE; 1.870 + END IF; 1.871 + "output_row"."scope_in" := "output_row"."scope_out"; 1.872 + IF EXISTS ( 1.873 + SELECT NULL FROM "member" JOIN "privilege" 1.874 + ON "privilege"."member_id" = "member"."id" 1.875 + AND "privilege"."unit_id" = "unit_id_v" 1.876 + WHERE "id" = "output_row"."member_id" 1.877 + AND "member"."active" AND "privilege"."voting_right" 1.878 + ) THEN 1.879 + IF "scope_v" = 'unit' THEN 1.880 + SELECT * INTO "delegation_row" FROM "delegation" 1.881 + WHERE "truster_id" = "output_row"."member_id" 1.882 + AND "unit_id" = "unit_id_v"; 1.883 + ELSIF "scope_v" = 'area' THEN 1.884 + "output_row"."participation" := EXISTS ( 1.885 + SELECT NULL FROM "membership" 1.886 + WHERE "area_id" = "area_id_p" 1.887 + AND "member_id" = "output_row"."member_id" 1.888 + ); 1.889 + SELECT * INTO "delegation_row" FROM "delegation" 1.890 + WHERE "truster_id" = "output_row"."member_id" 1.891 + AND ( 1.892 + "unit_id" = "unit_id_v" OR 1.893 + "area_id" = "area_id_v" 1.894 + ) 1.895 + ORDER BY "scope" DESC; 1.896 + ELSIF "scope_v" = 'issue' THEN 1.897 + "output_row"."participation" := EXISTS ( 1.898 + SELECT NULL FROM "interest" 1.899 + WHERE "issue_id" = "issue_id_p" 1.900 + AND "member_id" = "output_row"."member_id" 1.901 + ); 1.902 + SELECT * INTO "delegation_row" FROM "delegation" 1.903 + WHERE "truster_id" = "output_row"."member_id" 1.904 + AND ( 1.905 + "unit_id" = "unit_id_v" OR 1.906 + "area_id" = "area_id_v" OR 1.907 + "issue_id" = "issue_id_p" 1.908 + ) 1.909 + ORDER BY "scope" DESC; 1.910 + END IF; 1.911 + ELSE 1.912 + "output_row"."member_valid" := FALSE; 1.913 + "output_row"."participation" := FALSE; 1.914 + "output_row"."scope_out" := NULL; 1.915 + "delegation_row" := ROW(NULL); 1.916 + END IF; 1.917 + IF 1.918 + "output_row"."member_id" = "member_id_p" AND 1.919 + "simulate_trustee_id_p" NOTNULL 1.920 + THEN 1.921 + "output_row"."scope_out" := "scope_v"; 1.922 + "output_rows" := "output_rows" || "output_row"; 1.923 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.924 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.925 + "output_row"."scope_out" := "delegation_row"."scope"; 1.926 + "output_rows" := "output_rows" || "output_row"; 1.927 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.928 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.929 + "output_row"."scope_out" := "delegation_row"."scope"; 1.930 + "output_row"."disabled_out" := TRUE; 1.931 + "output_rows" := "output_rows" || "output_row"; 1.932 + EXIT; 1.933 + ELSE 1.934 + "output_row"."scope_out" := NULL; 1.935 + "output_rows" := "output_rows" || "output_row"; 1.936 + EXIT; 1.937 + END IF; 1.938 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.939 + "output_row"."index" := "output_row"."index" + 1; 1.940 + END LOOP; 1.941 + "row_count" := array_upper("output_rows", 1); 1.942 + "i" := 1; 1.943 + "loop_v" := FALSE; 1.944 + LOOP 1.945 + "output_row" := "output_rows"["i"]; 1.946 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 1.947 + IF "loop_v" THEN 1.948 + IF "i" + 1 = "row_count" THEN 1.949 + "output_row"."loop" := 'last'; 1.950 + ELSIF "i" = "row_count" THEN 1.951 + "output_row"."loop" := 'repetition'; 1.952 + ELSE 1.953 + "output_row"."loop" := 'intermediate'; 1.954 + END IF; 1.955 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.956 + "output_row"."loop" := 'first'; 1.957 + "loop_v" := TRUE; 1.958 + END IF; 1.959 + IF "scope_v" = 'unit' THEN 1.960 + "output_row"."participation" := NULL; 1.961 + END IF; 1.962 + RETURN NEXT "output_row"; 1.963 + "i" := "i" + 1; 1.964 + END LOOP; 1.965 + RETURN; 1.966 + END; 1.967 + $$; 1.968 + 1.969 +COMMENT ON FUNCTION "delegation_chain" 1.970 + ( "member"."id"%TYPE, 1.971 + "unit"."id"%TYPE, 1.972 + "area"."id"%TYPE, 1.973 + "issue"."id"%TYPE, 1.974 + "member"."id"%TYPE ) 1.975 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 1.976 + 1.977 + 1.978 +CREATE FUNCTION "delegation_chain" 1.979 + ( "member_id_p" "member"."id"%TYPE, 1.980 + "unit_id_p" "unit"."id"%TYPE, 1.981 + "area_id_p" "area"."id"%TYPE, 1.982 + "issue_id_p" "issue"."id"%TYPE ) 1.983 + RETURNS SETOF "delegation_chain_row" 1.984 + LANGUAGE 'plpgsql' STABLE AS $$ 1.985 + DECLARE 1.986 + "result_row" "delegation_chain_row"; 1.987 + BEGIN 1.988 + FOR "result_row" IN 1.989 + SELECT * FROM "delegation_chain"( 1.990 + "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL 1.991 + ) 1.992 + LOOP 1.993 + RETURN NEXT "result_row"; 1.994 + END LOOP; 1.995 + RETURN; 1.996 + END; 1.997 + $$; 1.998 + 1.999 +COMMENT ON FUNCTION "delegation_chain" 1.1000 + ( "member"."id"%TYPE, 1.1001 + "unit"."id"%TYPE, 1.1002 + "area"."id"%TYPE, 1.1003 + "issue"."id"%TYPE ) 1.1004 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 1.1005 + 1.1006 + 1.1007 +-- Other modified functions: 1.1008 + 1.1009 +CREATE OR REPLACE FUNCTION "lock_issue" 1.1010 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1011 + RETURNS VOID 1.1012 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1013 + BEGIN 1.1014 + LOCK TABLE "member" IN SHARE MODE; 1.1015 + LOCK TABLE "privilege" IN SHARE MODE; 1.1016 + LOCK TABLE "membership" IN SHARE MODE; 1.1017 + LOCK TABLE "policy" IN SHARE MODE; 1.1018 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.1019 + -- NOTE: The row-level exclusive lock in combination with the 1.1020 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 1.1021 + -- acquire a row-level share lock on the issue) ensure that no data 1.1022 + -- is changed, which could affect calculation of snapshots or 1.1023 + -- counting of votes. Table "delegation" must be table-level-locked, 1.1024 + -- as it also contains issue- and global-scope delegations. 1.1025 + LOCK TABLE "delegation" IN SHARE MODE; 1.1026 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 1.1027 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 1.1028 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 1.1029 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 1.1030 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 1.1031 + RETURN; 1.1032 + END; 1.1033 + $$; 1.1034 + 1.1035 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.1036 + RETURNS VOID 1.1037 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1038 + BEGIN 1.1039 + LOCK TABLE "member" IN SHARE MODE; 1.1040 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.1041 + LOCK TABLE "unit" IN EXCLUSIVE MODE; 1.1042 + LOCK TABLE "area" IN EXCLUSIVE MODE; 1.1043 + LOCK TABLE "privilege" IN SHARE MODE; 1.1044 + LOCK TABLE "membership" IN SHARE MODE; 1.1045 + DELETE FROM "member_count"; 1.1046 + INSERT INTO "member_count" ("total_count") 1.1047 + SELECT "total_count" FROM "member_count_view"; 1.1048 + UPDATE "unit" SET "member_count" = "view"."member_count" 1.1049 + FROM "unit_member_count" AS "view" 1.1050 + WHERE "view"."unit_id" = "unit"."id"; 1.1051 + UPDATE "area" SET 1.1052 + "direct_member_count" = "view"."direct_member_count", 1.1053 + "member_weight" = "view"."member_weight", 1.1054 + "autoreject_weight" = "view"."autoreject_weight" 1.1055 + FROM "area_member_count" AS "view" 1.1056 + WHERE "view"."area_id" = "area"."id"; 1.1057 + RETURN; 1.1058 + END; 1.1059 + $$; 1.1060 + 1.1061 +CREATE OR REPLACE FUNCTION "create_population_snapshot" 1.1062 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1063 + RETURNS VOID 1.1064 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1065 + DECLARE 1.1066 + "member_id_v" "member"."id"%TYPE; 1.1067 + BEGIN 1.1068 + DELETE FROM "direct_population_snapshot" 1.1069 + WHERE "issue_id" = "issue_id_p" 1.1070 + AND "event" = 'periodic'; 1.1071 + DELETE FROM "delegating_population_snapshot" 1.1072 + WHERE "issue_id" = "issue_id_p" 1.1073 + AND "event" = 'periodic'; 1.1074 + INSERT INTO "direct_population_snapshot" 1.1075 + ("issue_id", "event", "member_id") 1.1076 + SELECT 1.1077 + "issue_id_p" AS "issue_id", 1.1078 + 'periodic'::"snapshot_event" AS "event", 1.1079 + "member"."id" AS "member_id" 1.1080 + FROM "issue" 1.1081 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.1082 + JOIN "membership" ON "area"."id" = "membership"."area_id" 1.1083 + JOIN "member" ON "membership"."member_id" = "member"."id" 1.1084 + JOIN "privilege" 1.1085 + ON "privilege"."unit_id" = "area"."unit_id" 1.1086 + AND "privilege"."member_id" = "member"."id" 1.1087 + WHERE "issue"."id" = "issue_id_p" 1.1088 + AND "member"."active" AND "privilege"."voting_right" 1.1089 + UNION 1.1090 + SELECT 1.1091 + "issue_id_p" AS "issue_id", 1.1092 + 'periodic'::"snapshot_event" AS "event", 1.1093 + "member"."id" AS "member_id" 1.1094 + FROM "issue" 1.1095 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.1096 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1097 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.1098 + JOIN "privilege" 1.1099 + ON "privilege"."unit_id" = "area"."unit_id" 1.1100 + AND "privilege"."member_id" = "member"."id" 1.1101 + WHERE "issue"."id" = "issue_id_p" 1.1102 + AND "member"."active" AND "privilege"."voting_right"; 1.1103 + FOR "member_id_v" IN 1.1104 + SELECT "member_id" FROM "direct_population_snapshot" 1.1105 + WHERE "issue_id" = "issue_id_p" 1.1106 + AND "event" = 'periodic' 1.1107 + LOOP 1.1108 + UPDATE "direct_population_snapshot" SET 1.1109 + "weight" = 1 + 1.1110 + "weight_of_added_delegations_for_population_snapshot"( 1.1111 + "issue_id_p", 1.1112 + "member_id_v", 1.1113 + '{}' 1.1114 + ) 1.1115 + WHERE "issue_id" = "issue_id_p" 1.1116 + AND "event" = 'periodic' 1.1117 + AND "member_id" = "member_id_v"; 1.1118 + END LOOP; 1.1119 + RETURN; 1.1120 + END; 1.1121 + $$; 1.1122 + 1.1123 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 1.1124 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1125 + RETURNS VOID 1.1126 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1127 + DECLARE 1.1128 + "member_id_v" "member"."id"%TYPE; 1.1129 + BEGIN 1.1130 + DELETE FROM "direct_interest_snapshot" 1.1131 + WHERE "issue_id" = "issue_id_p" 1.1132 + AND "event" = 'periodic'; 1.1133 + DELETE FROM "delegating_interest_snapshot" 1.1134 + WHERE "issue_id" = "issue_id_p" 1.1135 + AND "event" = 'periodic'; 1.1136 + DELETE FROM "direct_supporter_snapshot" 1.1137 + WHERE "issue_id" = "issue_id_p" 1.1138 + AND "event" = 'periodic'; 1.1139 + INSERT INTO "direct_interest_snapshot" 1.1140 + ("issue_id", "event", "member_id", "voting_requested") 1.1141 + SELECT 1.1142 + "issue_id_p" AS "issue_id", 1.1143 + 'periodic' AS "event", 1.1144 + "member"."id" AS "member_id", 1.1145 + "interest"."voting_requested" 1.1146 + FROM "issue" 1.1147 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.1148 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1149 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.1150 + JOIN "privilege" 1.1151 + ON "privilege"."unit_id" = "area"."unit_id" 1.1152 + AND "privilege"."member_id" = "member"."id" 1.1153 + WHERE "issue"."id" = "issue_id_p" 1.1154 + AND "member"."active" AND "privilege"."voting_right"; 1.1155 + FOR "member_id_v" IN 1.1156 + SELECT "member_id" FROM "direct_interest_snapshot" 1.1157 + WHERE "issue_id" = "issue_id_p" 1.1158 + AND "event" = 'periodic' 1.1159 + LOOP 1.1160 + UPDATE "direct_interest_snapshot" SET 1.1161 + "weight" = 1 + 1.1162 + "weight_of_added_delegations_for_interest_snapshot"( 1.1163 + "issue_id_p", 1.1164 + "member_id_v", 1.1165 + '{}' 1.1166 + ) 1.1167 + WHERE "issue_id" = "issue_id_p" 1.1168 + AND "event" = 'periodic' 1.1169 + AND "member_id" = "member_id_v"; 1.1170 + END LOOP; 1.1171 + INSERT INTO "direct_supporter_snapshot" 1.1172 + ( "issue_id", "initiative_id", "event", "member_id", 1.1173 + "informed", "satisfied" ) 1.1174 + SELECT 1.1175 + "issue_id_p" AS "issue_id", 1.1176 + "initiative"."id" AS "initiative_id", 1.1177 + 'periodic' AS "event", 1.1178 + "supporter"."member_id" AS "member_id", 1.1179 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.1180 + NOT EXISTS ( 1.1181 + SELECT NULL FROM "critical_opinion" 1.1182 + WHERE "initiative_id" = "initiative"."id" 1.1183 + AND "member_id" = "supporter"."member_id" 1.1184 + ) AS "satisfied" 1.1185 + FROM "initiative" 1.1186 + JOIN "supporter" 1.1187 + ON "supporter"."initiative_id" = "initiative"."id" 1.1188 + JOIN "current_draft" 1.1189 + ON "initiative"."id" = "current_draft"."initiative_id" 1.1190 + JOIN "direct_interest_snapshot" 1.1191 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.1192 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.1193 + AND "event" = 'periodic' 1.1194 + WHERE "initiative"."issue_id" = "issue_id_p"; 1.1195 + RETURN; 1.1196 + END; 1.1197 + $$; 1.1198 + 1.1199 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 1.1200 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1201 + RETURNS VOID 1.1202 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1203 + DECLARE 1.1204 + "issue_row" "issue"%ROWTYPE; 1.1205 + "policy_row" "policy"%ROWTYPE; 1.1206 + "initiative_row" "initiative"%ROWTYPE; 1.1207 + BEGIN 1.1208 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1209 + SELECT * INTO "policy_row" 1.1210 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.1211 + PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 1.1212 + FOR "initiative_row" IN 1.1213 + SELECT * FROM "initiative" 1.1214 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1215 + LOOP 1.1216 + IF 1.1217 + "initiative_row"."satisfied_supporter_count" > 0 AND 1.1218 + "initiative_row"."satisfied_supporter_count" * 1.1219 + "policy_row"."initiative_quorum_den" >= 1.1220 + "issue_row"."population" * "policy_row"."initiative_quorum_num" 1.1221 + THEN 1.1222 + UPDATE "initiative" SET "admitted" = TRUE 1.1223 + WHERE "id" = "initiative_row"."id"; 1.1224 + ELSE 1.1225 + UPDATE "initiative" SET "admitted" = FALSE 1.1226 + WHERE "id" = "initiative_row"."id"; 1.1227 + END IF; 1.1228 + END LOOP; 1.1229 + IF EXISTS ( 1.1230 + SELECT NULL FROM "initiative" 1.1231 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.1232 + ) THEN 1.1233 + UPDATE "issue" SET 1.1234 + "state" = 'voting', 1.1235 + "accepted" = coalesce("accepted", now()), 1.1236 + "half_frozen" = coalesce("half_frozen", now()), 1.1237 + "fully_frozen" = now() 1.1238 + WHERE "id" = "issue_id_p"; 1.1239 + ELSE 1.1240 + UPDATE "issue" SET 1.1241 + "state" = 'canceled_no_initiative_admitted', 1.1242 + "accepted" = coalesce("accepted", now()), 1.1243 + "half_frozen" = coalesce("half_frozen", now()), 1.1244 + "fully_frozen" = now(), 1.1245 + "closed" = now(), 1.1246 + "ranks_available" = TRUE 1.1247 + WHERE "id" = "issue_id_p"; 1.1248 + -- NOTE: The following DELETE statements have effect only when 1.1249 + -- issue state has been manipulated 1.1250 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 1.1251 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 1.1252 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.1253 + END IF; 1.1254 + RETURN; 1.1255 + END; 1.1256 + $$; 1.1257 + 1.1258 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.1259 + RETURNS VOID 1.1260 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1261 + DECLARE 1.1262 + "area_id_v" "area"."id"%TYPE; 1.1263 + "unit_id_v" "unit"."id"%TYPE; 1.1264 + "member_id_v" "member"."id"%TYPE; 1.1265 + BEGIN 1.1266 + PERFORM "lock_issue"("issue_id_p"); 1.1267 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.1268 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.1269 + DELETE FROM "delegating_voter" 1.1270 + WHERE "issue_id" = "issue_id_p"; 1.1271 + DELETE FROM "direct_voter" 1.1272 + WHERE "issue_id" = "issue_id_p" 1.1273 + AND "autoreject" = TRUE; 1.1274 + DELETE FROM "direct_voter" 1.1275 + USING ( 1.1276 + SELECT 1.1277 + "direct_voter"."member_id" 1.1278 + FROM "direct_voter" 1.1279 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.1280 + LEFT JOIN "privilege" 1.1281 + ON "privilege"."unit_id" = "unit_id_v" 1.1282 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.1283 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.1284 + "member"."active" = FALSE OR 1.1285 + "privilege"."voting_right" ISNULL OR 1.1286 + "privilege"."voting_right" = FALSE 1.1287 + ) 1.1288 + ) AS "subquery" 1.1289 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.1290 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.1291 + UPDATE "direct_voter" SET "weight" = 1 1.1292 + WHERE "issue_id" = "issue_id_p"; 1.1293 + PERFORM "add_vote_delegations"("issue_id_p"); 1.1294 + FOR "member_id_v" IN 1.1295 + SELECT "interest"."member_id" 1.1296 + FROM "interest" 1.1297 + JOIN "member" 1.1298 + ON "interest"."member_id" = "member"."id" 1.1299 + JOIN "privilege" 1.1300 + ON "privilege"."unit_id" = "unit_id_v" 1.1301 + AND "privilege"."member_id" = "member"."id" 1.1302 + LEFT JOIN "direct_voter" 1.1303 + ON "interest"."member_id" = "direct_voter"."member_id" 1.1304 + AND "interest"."issue_id" = "direct_voter"."issue_id" 1.1305 + LEFT JOIN "delegating_voter" 1.1306 + ON "interest"."member_id" = "delegating_voter"."member_id" 1.1307 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 1.1308 + WHERE "interest"."issue_id" = "issue_id_p" 1.1309 + AND "interest"."autoreject" = TRUE 1.1310 + AND "member"."active" 1.1311 + AND "privilege"."voting_right" 1.1312 + AND "direct_voter"."member_id" ISNULL 1.1313 + AND "delegating_voter"."member_id" ISNULL 1.1314 + UNION SELECT "membership"."member_id" 1.1315 + FROM "membership" 1.1316 + JOIN "member" 1.1317 + ON "membership"."member_id" = "member"."id" 1.1318 + JOIN "privilege" 1.1319 + ON "privilege"."unit_id" = "unit_id_v" 1.1320 + AND "privilege"."member_id" = "member"."id" 1.1321 + LEFT JOIN "interest" 1.1322 + ON "membership"."member_id" = "interest"."member_id" 1.1323 + AND "interest"."issue_id" = "issue_id_p" 1.1324 + LEFT JOIN "direct_voter" 1.1325 + ON "membership"."member_id" = "direct_voter"."member_id" 1.1326 + AND "direct_voter"."issue_id" = "issue_id_p" 1.1327 + LEFT JOIN "delegating_voter" 1.1328 + ON "membership"."member_id" = "delegating_voter"."member_id" 1.1329 + AND "delegating_voter"."issue_id" = "issue_id_p" 1.1330 + WHERE "membership"."area_id" = "area_id_v" 1.1331 + AND "membership"."autoreject" = TRUE 1.1332 + AND "member"."active" 1.1333 + AND "privilege"."voting_right" 1.1334 + AND "interest"."autoreject" ISNULL 1.1335 + AND "direct_voter"."member_id" ISNULL 1.1336 + AND "delegating_voter"."member_id" ISNULL 1.1337 + LOOP 1.1338 + INSERT INTO "direct_voter" 1.1339 + ("member_id", "issue_id", "weight", "autoreject") VALUES 1.1340 + ("member_id_v", "issue_id_p", 1, TRUE); 1.1341 + INSERT INTO "vote" ( 1.1342 + "member_id", 1.1343 + "issue_id", 1.1344 + "initiative_id", 1.1345 + "grade" 1.1346 + ) SELECT 1.1347 + "member_id_v" AS "member_id", 1.1348 + "issue_id_p" AS "issue_id", 1.1349 + "id" AS "initiative_id", 1.1350 + -1 AS "grade" 1.1351 + FROM "initiative" 1.1352 + WHERE "issue_id" = "issue_id_p" AND "admitted"; 1.1353 + END LOOP; 1.1354 + PERFORM "add_vote_delegations"("issue_id_p"); 1.1355 + UPDATE "issue" SET 1.1356 + "state" = 'calculation', 1.1357 + "closed" = now(), 1.1358 + "voter_count" = ( 1.1359 + SELECT coalesce(sum("weight"), 0) 1.1360 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.1361 + ) 1.1362 + WHERE "id" = "issue_id_p"; 1.1363 + UPDATE "initiative" SET 1.1364 + "positive_votes" = "vote_counts"."positive_votes", 1.1365 + "negative_votes" = "vote_counts"."negative_votes", 1.1366 + "agreed" = CASE WHEN "majority_strict" THEN 1.1367 + "vote_counts"."positive_votes" * "majority_den" > 1.1368 + "majority_num" * 1.1369 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.1370 + ELSE 1.1371 + "vote_counts"."positive_votes" * "majority_den" >= 1.1372 + "majority_num" * 1.1373 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.1374 + END 1.1375 + FROM 1.1376 + ( SELECT 1.1377 + "initiative"."id" AS "initiative_id", 1.1378 + coalesce( 1.1379 + sum( 1.1380 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.1381 + ), 1.1382 + 0 1.1383 + ) AS "positive_votes", 1.1384 + coalesce( 1.1385 + sum( 1.1386 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 1.1387 + ), 1.1388 + 0 1.1389 + ) AS "negative_votes" 1.1390 + FROM "initiative" 1.1391 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.1392 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.1393 + LEFT JOIN "direct_voter" 1.1394 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.1395 + LEFT JOIN "vote" 1.1396 + ON "vote"."initiative_id" = "initiative"."id" 1.1397 + AND "vote"."member_id" = "direct_voter"."member_id" 1.1398 + WHERE "initiative"."issue_id" = "issue_id_p" 1.1399 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 1.1400 + GROUP BY "initiative"."id" 1.1401 + ) AS "vote_counts", 1.1402 + "issue", 1.1403 + "policy" 1.1404 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.1405 + AND "issue"."id" = "initiative"."issue_id" 1.1406 + AND "policy"."id" = "issue"."policy_id"; 1.1407 + -- NOTE: "closed" column of issue must be set at this point 1.1408 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.1409 + INSERT INTO "battle" ( 1.1410 + "issue_id", 1.1411 + "winning_initiative_id", "losing_initiative_id", 1.1412 + "count" 1.1413 + ) SELECT 1.1414 + "issue_id", 1.1415 + "winning_initiative_id", "losing_initiative_id", 1.1416 + "count" 1.1417 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.1418 + END; 1.1419 + $$; 1.1420 + 1.1421 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 1.1422 + RETURNS VOID 1.1423 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1424 + DECLARE 1.1425 + "dimension_v" INTEGER; 1.1426 + "vote_matrix" INT4[][]; -- absolute votes 1.1427 + "matrix" INT8[][]; -- defeat strength / best paths 1.1428 + "i" INTEGER; 1.1429 + "j" INTEGER; 1.1430 + "k" INTEGER; 1.1431 + "battle_row" "battle"%ROWTYPE; 1.1432 + "rank_ary" INT4[]; 1.1433 + "rank_v" INT4; 1.1434 + "done_v" INTEGER; 1.1435 + "winners_ary" INTEGER[]; 1.1436 + "initiative_id_v" "initiative"."id"%TYPE; 1.1437 + BEGIN 1.1438 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.1439 + SELECT count(1) INTO "dimension_v" FROM "initiative" 1.1440 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.1441 + IF "dimension_v" = 1 THEN 1.1442 + UPDATE "initiative" SET "rank" = 1 1.1443 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.1444 + ELSIF "dimension_v" > 1 THEN 1.1445 + -- Create "vote_matrix" with absolute number of votes in pairwise 1.1446 + -- comparison: 1.1447 + "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.1448 + "i" := 1; 1.1449 + "j" := 2; 1.1450 + FOR "battle_row" IN 1.1451 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.1452 + ORDER BY "winning_initiative_id", "losing_initiative_id" 1.1453 + LOOP 1.1454 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.1455 + IF "j" = "dimension_v" THEN 1.1456 + "i" := "i" + 1; 1.1457 + "j" := 1; 1.1458 + ELSE 1.1459 + "j" := "j" + 1; 1.1460 + IF "j" = "i" THEN 1.1461 + "j" := "j" + 1; 1.1462 + END IF; 1.1463 + END IF; 1.1464 + END LOOP; 1.1465 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.1466 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.1467 + END IF; 1.1468 + -- Store defeat strengths in "matrix" using "defeat_strength" 1.1469 + -- function: 1.1470 + "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.1471 + "i" := 1; 1.1472 + LOOP 1.1473 + "j" := 1; 1.1474 + LOOP 1.1475 + IF "i" != "j" THEN 1.1476 + "matrix"["i"]["j"] := "defeat_strength"( 1.1477 + "vote_matrix"["i"]["j"], 1.1478 + "vote_matrix"["j"]["i"] 1.1479 + ); 1.1480 + END IF; 1.1481 + EXIT WHEN "j" = "dimension_v"; 1.1482 + "j" := "j" + 1; 1.1483 + END LOOP; 1.1484 + EXIT WHEN "i" = "dimension_v"; 1.1485 + "i" := "i" + 1; 1.1486 + END LOOP; 1.1487 + -- Find best paths: 1.1488 + "i" := 1; 1.1489 + LOOP 1.1490 + "j" := 1; 1.1491 + LOOP 1.1492 + IF "i" != "j" THEN 1.1493 + "k" := 1; 1.1494 + LOOP 1.1495 + IF "i" != "k" AND "j" != "k" THEN 1.1496 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 1.1497 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 1.1498 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 1.1499 + END IF; 1.1500 + ELSE 1.1501 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 1.1502 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 1.1503 + END IF; 1.1504 + END IF; 1.1505 + END IF; 1.1506 + EXIT WHEN "k" = "dimension_v"; 1.1507 + "k" := "k" + 1; 1.1508 + END LOOP; 1.1509 + END IF; 1.1510 + EXIT WHEN "j" = "dimension_v"; 1.1511 + "j" := "j" + 1; 1.1512 + END LOOP; 1.1513 + EXIT WHEN "i" = "dimension_v"; 1.1514 + "i" := "i" + 1; 1.1515 + END LOOP; 1.1516 + -- Determine order of winners: 1.1517 + "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.1518 + "rank_v" := 1; 1.1519 + "done_v" := 0; 1.1520 + LOOP 1.1521 + "winners_ary" := '{}'; 1.1522 + "i" := 1; 1.1523 + LOOP 1.1524 + IF "rank_ary"["i"] ISNULL THEN 1.1525 + "j" := 1; 1.1526 + LOOP 1.1527 + IF 1.1528 + "i" != "j" AND 1.1529 + "rank_ary"["j"] ISNULL AND 1.1530 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 1.1531 + THEN 1.1532 + -- someone else is better 1.1533 + EXIT; 1.1534 + END IF; 1.1535 + IF "j" = "dimension_v" THEN 1.1536 + -- noone is better 1.1537 + "winners_ary" := "winners_ary" || "i"; 1.1538 + EXIT; 1.1539 + END IF; 1.1540 + "j" := "j" + 1; 1.1541 + END LOOP; 1.1542 + END IF; 1.1543 + EXIT WHEN "i" = "dimension_v"; 1.1544 + "i" := "i" + 1; 1.1545 + END LOOP; 1.1546 + "i" := 1; 1.1547 + LOOP 1.1548 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 1.1549 + "done_v" := "done_v" + 1; 1.1550 + EXIT WHEN "i" = array_upper("winners_ary", 1); 1.1551 + "i" := "i" + 1; 1.1552 + END LOOP; 1.1553 + EXIT WHEN "done_v" = "dimension_v"; 1.1554 + "rank_v" := "rank_v" + 1; 1.1555 + END LOOP; 1.1556 + -- write preliminary ranks: 1.1557 + "i" := 1; 1.1558 + FOR "initiative_id_v" IN 1.1559 + SELECT "id" FROM "initiative" 1.1560 + WHERE "issue_id" = "issue_id_p" AND "agreed" 1.1561 + ORDER BY "id" 1.1562 + LOOP 1.1563 + UPDATE "initiative" SET "rank" = "rank_ary"["i"] 1.1564 + WHERE "id" = "initiative_id_v"; 1.1565 + "i" := "i" + 1; 1.1566 + END LOOP; 1.1567 + IF "i" != "dimension_v" + 1 THEN 1.1568 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.1569 + END IF; 1.1570 + -- straighten ranks (start counting with 1, no equal ranks): 1.1571 + "rank_v" := 1; 1.1572 + FOR "initiative_id_v" IN 1.1573 + SELECT "id" FROM "initiative" 1.1574 + WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL 1.1575 + ORDER BY 1.1576 + "rank", 1.1577 + "vote_ratio"("positive_votes", "negative_votes") DESC, 1.1578 + "id" 1.1579 + LOOP 1.1580 + UPDATE "initiative" SET "rank" = "rank_v" 1.1581 + WHERE "id" = "initiative_id_v"; 1.1582 + "rank_v" := "rank_v" + 1; 1.1583 + END LOOP; 1.1584 + END IF; 1.1585 + -- mark issue as finished 1.1586 + UPDATE "issue" SET 1.1587 + "state" = 1.1588 + CASE WHEN "dimension_v" = 0 THEN 1.1589 + 'finished_without_winner'::"issue_state" 1.1590 + ELSE 1.1591 + 'finished_with_winner'::"issue_state" 1.1592 + END, 1.1593 + "ranks_available" = TRUE 1.1594 + WHERE "id" = "issue_id_p"; 1.1595 + RETURN; 1.1596 + END; 1.1597 + $$; 1.1598 + 1.1599 +CREATE OR REPLACE FUNCTION "check_issue" 1.1600 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1601 + RETURNS VOID 1.1602 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1603 + DECLARE 1.1604 + "issue_row" "issue"%ROWTYPE; 1.1605 + "policy_row" "policy"%ROWTYPE; 1.1606 + "voting_requested_v" BOOLEAN; 1.1607 + BEGIN 1.1608 + PERFORM "lock_issue"("issue_id_p"); 1.1609 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1610 + -- only process open issues: 1.1611 + IF "issue_row"."closed" ISNULL THEN 1.1612 + SELECT * INTO "policy_row" FROM "policy" 1.1613 + WHERE "id" = "issue_row"."policy_id"; 1.1614 + -- create a snapshot, unless issue is already fully frozen: 1.1615 + IF "issue_row"."fully_frozen" ISNULL THEN 1.1616 + PERFORM "create_snapshot"("issue_id_p"); 1.1617 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1618 + END IF; 1.1619 + -- eventually close or accept issues, which have not been accepted: 1.1620 + IF "issue_row"."accepted" ISNULL THEN 1.1621 + IF EXISTS ( 1.1622 + SELECT NULL FROM "initiative" 1.1623 + WHERE "issue_id" = "issue_id_p" 1.1624 + AND "supporter_count" > 0 1.1625 + AND "supporter_count" * "policy_row"."issue_quorum_den" 1.1626 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.1627 + ) THEN 1.1628 + -- accept issues, if supporter count is high enough 1.1629 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1630 + -- NOTE: "issue_row" used later 1.1631 + "issue_row"."state" := 'discussion'; 1.1632 + "issue_row"."accepted" := now(); 1.1633 + UPDATE "issue" SET 1.1634 + "state" = "issue_row"."state", 1.1635 + "accepted" = "issue_row"."accepted" 1.1636 + WHERE "id" = "issue_row"."id"; 1.1637 + ELSIF 1.1638 + now() >= "issue_row"."created" + "issue_row"."admission_time" 1.1639 + THEN 1.1640 + -- close issues, if admission time has expired 1.1641 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1642 + UPDATE "issue" SET 1.1643 + "state" = 'canceled_issue_not_accepted', 1.1644 + "closed" = now() 1.1645 + WHERE "id" = "issue_row"."id"; 1.1646 + END IF; 1.1647 + END IF; 1.1648 + -- eventually half freeze issues: 1.1649 + IF 1.1650 + -- NOTE: issue can't be closed at this point, if it has been accepted 1.1651 + "issue_row"."accepted" NOTNULL AND 1.1652 + "issue_row"."half_frozen" ISNULL 1.1653 + THEN 1.1654 + SELECT 1.1655 + CASE 1.1656 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 1.1657 + TRUE 1.1658 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 1.1659 + FALSE 1.1660 + ELSE NULL 1.1661 + END 1.1662 + INTO "voting_requested_v" 1.1663 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1664 + IF 1.1665 + "voting_requested_v" OR ( 1.1666 + "voting_requested_v" ISNULL AND 1.1667 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.1668 + ) 1.1669 + THEN 1.1670 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.1671 + -- NOTE: "issue_row" used later 1.1672 + "issue_row"."state" := 'verification'; 1.1673 + "issue_row"."half_frozen" := now(); 1.1674 + UPDATE "issue" SET 1.1675 + "state" = "issue_row"."state", 1.1676 + "half_frozen" = "issue_row"."half_frozen" 1.1677 + WHERE "id" = "issue_row"."id"; 1.1678 + END IF; 1.1679 + END IF; 1.1680 + -- close issues after some time, if all initiatives have been revoked: 1.1681 + IF 1.1682 + "issue_row"."closed" ISNULL AND 1.1683 + NOT EXISTS ( 1.1684 + -- all initiatives are revoked 1.1685 + SELECT NULL FROM "initiative" 1.1686 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1687 + ) AND ( 1.1688 + -- and issue has not been accepted yet 1.1689 + "issue_row"."accepted" ISNULL OR 1.1690 + NOT EXISTS ( 1.1691 + -- or no initiatives have been revoked lately 1.1692 + SELECT NULL FROM "initiative" 1.1693 + WHERE "issue_id" = "issue_id_p" 1.1694 + AND now() < "revoked" + "issue_row"."verification_time" 1.1695 + ) OR ( 1.1696 + -- or verification time has elapsed 1.1697 + "issue_row"."half_frozen" NOTNULL AND 1.1698 + "issue_row"."fully_frozen" ISNULL AND 1.1699 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1700 + ) 1.1701 + ) 1.1702 + THEN 1.1703 + -- NOTE: "issue_row" used later 1.1704 + IF "issue_row"."accepted" ISNULL THEN 1.1705 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 1.1706 + ELSIF "issue_row"."half_frozen" ISNULL THEN 1.1707 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 1.1708 + ELSE 1.1709 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 1.1710 + END IF; 1.1711 + "issue_row"."closed" := now(); 1.1712 + UPDATE "issue" SET 1.1713 + "state" = "issue_row"."state", 1.1714 + "closed" = "issue_row"."closed" 1.1715 + WHERE "id" = "issue_row"."id"; 1.1716 + END IF; 1.1717 + -- fully freeze issue after verification time: 1.1718 + IF 1.1719 + "issue_row"."half_frozen" NOTNULL AND 1.1720 + "issue_row"."fully_frozen" ISNULL AND 1.1721 + "issue_row"."closed" ISNULL AND 1.1722 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1723 + THEN 1.1724 + PERFORM "freeze_after_snapshot"("issue_id_p"); 1.1725 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 1.1726 + END IF; 1.1727 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1728 + -- close issue by calling close_voting(...) after voting time: 1.1729 + IF 1.1730 + "issue_row"."closed" ISNULL AND 1.1731 + "issue_row"."fully_frozen" NOTNULL AND 1.1732 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.1733 + THEN 1.1734 + PERFORM "close_voting"("issue_id_p"); 1.1735 + -- calculate ranks will not consume much time and can be done now 1.1736 + PERFORM "calculate_ranks"("issue_id_p"); 1.1737 + END IF; 1.1738 + END IF; 1.1739 + RETURN; 1.1740 + END; 1.1741 + $$; 1.1742 + 1.1743 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 1.1744 + RETURNS VOID 1.1745 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1746 + DECLARE 1.1747 + "issue_row" "issue"%ROWTYPE; 1.1748 + BEGIN 1.1749 + SELECT * INTO "issue_row" 1.1750 + FROM "issue" WHERE "id" = "issue_id_p" 1.1751 + FOR UPDATE; 1.1752 + IF "issue_row"."cleaned" ISNULL THEN 1.1753 + UPDATE "issue" SET 1.1754 + "state" = 'voting', 1.1755 + "closed" = NULL, 1.1756 + "ranks_available" = FALSE 1.1757 + WHERE "id" = "issue_id_p"; 1.1758 + DELETE FROM "issue_comment" 1.1759 + WHERE "issue_id" = "issue_id_p"; 1.1760 + DELETE FROM "voting_comment" 1.1761 + WHERE "issue_id" = "issue_id_p"; 1.1762 + DELETE FROM "delegating_voter" 1.1763 + WHERE "issue_id" = "issue_id_p"; 1.1764 + DELETE FROM "direct_voter" 1.1765 + WHERE "issue_id" = "issue_id_p"; 1.1766 + DELETE FROM "delegating_interest_snapshot" 1.1767 + WHERE "issue_id" = "issue_id_p"; 1.1768 + DELETE FROM "direct_interest_snapshot" 1.1769 + WHERE "issue_id" = "issue_id_p"; 1.1770 + DELETE FROM "delegating_population_snapshot" 1.1771 + WHERE "issue_id" = "issue_id_p"; 1.1772 + DELETE FROM "direct_population_snapshot" 1.1773 + WHERE "issue_id" = "issue_id_p"; 1.1774 + DELETE FROM "non_voter" 1.1775 + WHERE "issue_id" = "issue_id_p"; 1.1776 + DELETE FROM "delegation" 1.1777 + WHERE "issue_id" = "issue_id_p"; 1.1778 + DELETE FROM "supporter" 1.1779 + WHERE "issue_id" = "issue_id_p"; 1.1780 + UPDATE "issue" SET 1.1781 + "state" = "issue_row"."state", 1.1782 + "closed" = "issue_row"."closed", 1.1783 + "ranks_available" = "issue_row"."ranks_available", 1.1784 + "cleaned" = now() 1.1785 + WHERE "id" = "issue_id_p"; 1.1786 + END IF; 1.1787 + RETURN; 1.1788 + END; 1.1789 + $$; 1.1790 + 1.1791 +CREATE OR REPLACE FUNCTION "check_issue" 1.1792 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1793 + RETURNS VOID 1.1794 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1795 + DECLARE 1.1796 + "issue_row" "issue"%ROWTYPE; 1.1797 + "policy_row" "policy"%ROWTYPE; 1.1798 + "voting_requested_v" BOOLEAN; 1.1799 + BEGIN 1.1800 + PERFORM "lock_issue"("issue_id_p"); 1.1801 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1802 + -- only process open issues: 1.1803 + IF "issue_row"."closed" ISNULL THEN 1.1804 + SELECT * INTO "policy_row" FROM "policy" 1.1805 + WHERE "id" = "issue_row"."policy_id"; 1.1806 + -- create a snapshot, unless issue is already fully frozen: 1.1807 + IF "issue_row"."fully_frozen" ISNULL THEN 1.1808 + PERFORM "create_snapshot"("issue_id_p"); 1.1809 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1810 + END IF; 1.1811 + -- eventually close or accept issues, which have not been accepted: 1.1812 + IF "issue_row"."accepted" ISNULL THEN 1.1813 + IF EXISTS ( 1.1814 + SELECT NULL FROM "initiative" 1.1815 + WHERE "issue_id" = "issue_id_p" 1.1816 + AND "supporter_count" > 0 1.1817 + AND "supporter_count" * "policy_row"."issue_quorum_den" 1.1818 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.1819 + ) THEN 1.1820 + -- accept issues, if supporter count is high enough 1.1821 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1822 + -- NOTE: "issue_row" used later 1.1823 + "issue_row"."state" := 'discussion'; 1.1824 + "issue_row"."accepted" := now(); 1.1825 + UPDATE "issue" SET 1.1826 + "state" = "issue_row"."state", 1.1827 + "accepted" = "issue_row"."accepted" 1.1828 + WHERE "id" = "issue_row"."id"; 1.1829 + ELSIF 1.1830 + now() >= "issue_row"."created" + "issue_row"."admission_time" 1.1831 + THEN 1.1832 + -- close issues, if admission time has expired 1.1833 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1834 + UPDATE "issue" SET 1.1835 + "state" = 'canceled_issue_not_accepted', 1.1836 + "closed" = now() 1.1837 + WHERE "id" = "issue_row"."id"; 1.1838 + END IF; 1.1839 + END IF; 1.1840 + -- eventually half freeze issues: 1.1841 + IF 1.1842 + -- NOTE: issue can't be closed at this point, if it has been accepted 1.1843 + "issue_row"."accepted" NOTNULL AND 1.1844 + "issue_row"."half_frozen" ISNULL 1.1845 + THEN 1.1846 + SELECT 1.1847 + CASE 1.1848 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 1.1849 + TRUE 1.1850 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 1.1851 + FALSE 1.1852 + ELSE NULL 1.1853 + END 1.1854 + INTO "voting_requested_v" 1.1855 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1856 + IF 1.1857 + "voting_requested_v" OR ( 1.1858 + "voting_requested_v" ISNULL AND 1.1859 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.1860 + ) 1.1861 + THEN 1.1862 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.1863 + -- NOTE: "issue_row" used later 1.1864 + "issue_row"."state" := 'verification'; 1.1865 + "issue_row"."half_frozen" := now(); 1.1866 + UPDATE "issue" SET 1.1867 + "state" = "issue_row"."state", 1.1868 + "half_frozen" = "issue_row"."half_frozen" 1.1869 + WHERE "id" = "issue_row"."id"; 1.1870 + END IF; 1.1871 + END IF; 1.1872 + -- close issues after some time, if all initiatives have been revoked: 1.1873 + IF 1.1874 + "issue_row"."closed" ISNULL AND 1.1875 + NOT EXISTS ( 1.1876 + -- all initiatives are revoked 1.1877 + SELECT NULL FROM "initiative" 1.1878 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1879 + ) AND ( 1.1880 + -- and issue has not been accepted yet 1.1881 + "issue_row"."accepted" ISNULL OR 1.1882 + NOT EXISTS ( 1.1883 + -- or no initiatives have been revoked lately 1.1884 + SELECT NULL FROM "initiative" 1.1885 + WHERE "issue_id" = "issue_id_p" 1.1886 + AND now() < "revoked" + "issue_row"."verification_time" 1.1887 + ) OR ( 1.1888 + -- or verification time has elapsed 1.1889 + "issue_row"."half_frozen" NOTNULL AND 1.1890 + "issue_row"."fully_frozen" ISNULL AND 1.1891 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1892 + ) 1.1893 + ) 1.1894 + THEN 1.1895 + -- NOTE: "issue_row" used later 1.1896 + IF "issue_row"."accepted" ISNULL THEN 1.1897 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 1.1898 + ELSIF "issue_row"."half_frozen" ISNULL THEN 1.1899 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 1.1900 + ELSE 1.1901 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 1.1902 + END IF; 1.1903 + "issue_row"."closed" := now(); 1.1904 + UPDATE "issue" SET 1.1905 + "state" = "issue_row"."state", 1.1906 + "closed" = "issue_row"."closed" 1.1907 + WHERE "id" = "issue_row"."id"; 1.1908 + END IF; 1.1909 + -- fully freeze issue after verification time: 1.1910 + IF 1.1911 + "issue_row"."half_frozen" NOTNULL AND 1.1912 + "issue_row"."fully_frozen" ISNULL AND 1.1913 + "issue_row"."closed" ISNULL AND 1.1914 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1915 + THEN 1.1916 + PERFORM "freeze_after_snapshot"("issue_id_p"); 1.1917 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 1.1918 + END IF; 1.1919 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1920 + -- close issue by calling close_voting(...) after voting time: 1.1921 + IF 1.1922 + "issue_row"."closed" ISNULL AND 1.1923 + "issue_row"."fully_frozen" NOTNULL AND 1.1924 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.1925 + THEN 1.1926 + PERFORM "close_voting"("issue_id_p"); 1.1927 + -- calculate ranks will not consume much time and can be done now 1.1928 + PERFORM "calculate_ranks"("issue_id_p"); 1.1929 + END IF; 1.1930 + END IF; 1.1931 + RETURN; 1.1932 + END; 1.1933 + $$; 1.1934 + 1.1935 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.1936 + RETURNS VOID 1.1937 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1938 + BEGIN 1.1939 + UPDATE "member" SET 1.1940 + "last_login" = NULL, 1.1941 + "login" = NULL, 1.1942 + "password" = NULL, 1.1943 + "notify_email" = NULL, 1.1944 + "notify_email_unconfirmed" = NULL, 1.1945 + "notify_email_secret" = NULL, 1.1946 + "notify_email_secret_expiry" = NULL, 1.1947 + "notify_email_lock_expiry" = NULL, 1.1948 + "password_reset_secret" = NULL, 1.1949 + "password_reset_secret_expiry" = NULL, 1.1950 + "organizational_unit" = NULL, 1.1951 + "internal_posts" = NULL, 1.1952 + "realname" = NULL, 1.1953 + "birthday" = NULL, 1.1954 + "address" = NULL, 1.1955 + "email" = NULL, 1.1956 + "xmpp_address" = NULL, 1.1957 + "website" = NULL, 1.1958 + "phone" = NULL, 1.1959 + "mobile_phone" = NULL, 1.1960 + "profession" = NULL, 1.1961 + "external_memberships" = NULL, 1.1962 + "external_posts" = NULL, 1.1963 + "statement" = NULL; 1.1964 + -- "text_search_data" is updated by triggers 1.1965 + DELETE FROM "invite_code"; 1.1966 + DELETE FROM "setting"; 1.1967 + DELETE FROM "setting_map"; 1.1968 + DELETE FROM "member_relation_setting"; 1.1969 + DELETE FROM "member_image"; 1.1970 + DELETE FROM "contact"; 1.1971 + DELETE FROM "ignored_member"; 1.1972 + DELETE FROM "session"; 1.1973 + DELETE FROM "area_setting"; 1.1974 + DELETE FROM "issue_setting"; 1.1975 + DELETE FROM "ignored_initiative"; 1.1976 + DELETE FROM "initiative_setting"; 1.1977 + DELETE FROM "suggestion_setting"; 1.1978 + DELETE FROM "non_voter"; 1.1979 + DELETE FROM "direct_voter" USING "issue" 1.1980 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.1981 + AND "issue"."closed" ISNULL; 1.1982 + RETURN; 1.1983 + END; 1.1984 + $$; 1.1985 + 1.1986 + 1.1987 +-- Delete old "delegation_scope" TYPE: 1.1988 + 1.1989 +DROP TYPE "delegation_scope_old"; 1.1990 + 1.1991 + 1.1992 +COMMIT; 1.1993 + 1.1994 + 1.1995 +-- Generate issue states and add constraints: 1.1996 + 1.1997 +UPDATE "issue" SET "state" = 1.1998 + CASE 1.1999 + WHEN "closed" ISNULL THEN 1.2000 + CASE 1.2001 + WHEN "accepted" ISNULL THEN 1.2002 + 'admission'::"issue_state" 1.2003 + WHEN "half_frozen" ISNULL THEN 1.2004 + 'discussion'::"issue_state" 1.2005 + WHEN "fully_frozen" ISNULL THEN 1.2006 + 'verification'::"issue_state" 1.2007 + ELSE 1.2008 + 'voting'::"issue_state" 1.2009 + END 1.2010 + WHEN "fully_frozen" NOTNULL THEN 1.2011 + CASE 1.2012 + WHEN "fully_frozen" = "closed" THEN 1.2013 + 'canceled_no_initiative_admitted'::"issue_state" 1.2014 + ELSE 1.2015 + 'finished_without_winner'::"issue_state" -- NOTE: corrected later 1.2016 + END 1.2017 + WHEN "half_frozen" NOTNULL THEN 1.2018 + 'canceled_after_revocation_during_verification'::"issue_state" 1.2019 + WHEN "accepted" NOTNULL THEN 1.2020 + 'canceled_after_revocation_during_discussion'::"issue_state" 1.2021 + ELSE 1.2022 + 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later 1.2023 + END; 1.2024 +UPDATE "issue" SET "state" = 'finished_with_winner' 1.2025 + FROM "initiative" 1.2026 + WHERE "issue"."id" = "initiative"."issue_id" 1.2027 + AND "issue"."state" = 'finished_without_winner' 1.2028 + AND "initiative"."agreed"; 1.2029 +UPDATE "issue" SET "state" = 'canceled_issue_not_accepted' 1.2030 + FROM "initiative" 1.2031 + WHERE "issue"."id" = "initiative"."issue_id" 1.2032 + AND "issue"."state" = 'canceled_revoked_before_accepted' 1.2033 + AND "initiative"."revoked" ISNULL; 1.2034 + 1.2035 +ALTER TABLE "issue" ALTER "state" SET NOT NULL; 1.2036 + 1.2037 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; 1.2038 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK (( 1.2039 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.2040 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.2041 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.2042 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.2043 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.2044 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.2045 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.2046 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.2047 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) 1.2048 + ) AND ( 1.2049 + ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR 1.2050 + ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR 1.2051 + ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR 1.2052 + ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR 1.2053 + ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.2054 + ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.2055 + ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR 1.2056 + ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 1.2057 + ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR 1.2058 + ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.2059 + ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.2060 + ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) 1.2061 + )); 1.2062 + 1.2063 + 1.2064 +-- Guess "revoked_by_member_id" values based on author of current draft and add constraint: 1.2065 + 1.2066 +UPDATE "initiative" SET "revoked_by_member_id" = "author_id" 1.2067 + FROM "current_draft" 1.2068 + WHERE "initiative"."id" = "current_draft"."initiative_id" 1.2069 + AND "initiative"."revoked" NOTNULL; 1.2070 + 1.2071 +ALTER TABLE "initiative" ADD 1.2072 + CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.2073 + CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL); 1.2074 + 1.2075 + 1.2076 +-- Fill "unit_id" column with default value where neccessary and add constraints: 1.2077 + 1.2078 +UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit'; 1.2079 + 1.2080 +ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope" 1.2081 + CHECK ( 1.2082 + ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 1.2083 + ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 1.2084 + ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ); 1.2085 + 1.2086 + 1.2087 +-- Filling of "event" table with old (reconstructed) events: 1.2088 + 1.2089 +DELETE FROM "event"; 1.2090 +SELECT setval('event_id_seq', 1, false); 1.2091 + 1.2092 +INSERT INTO "event" 1.2093 + ( "occurrence", "event", "member_id", "issue_id", "state", 1.2094 + "initiative_id", "draft_id", "suggestion_id" ) 1.2095 + SELECT * FROM ( 1.2096 + SELECT * FROM ( 1.2097 + SELECT DISTINCT ON ("initiative"."id") 1.2098 + "timeline"."occurrence", 1.2099 + CASE WHEN "issue_creation"."issue_id" NOTNULL THEN 1.2100 + 'initiative_created_in_new_issue'::"event_type" 1.2101 + ELSE 1.2102 + 'initiative_created_in_existing_issue'::"event_type" 1.2103 + END, 1.2104 + "draft"."author_id", 1.2105 + "issue"."id", 1.2106 + CASE 1.2107 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.2108 + 'admission'::"issue_state" 1.2109 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.2110 + 'discussion'::"issue_state" 1.2111 + ELSE 1.2112 + 'verification'::"issue_state" 1.2113 + END, 1.2114 + "initiative"."id", 1.2115 + "draft"."id", 1.2116 + NULL::INT8 1.2117 + FROM "timeline" 1.2118 + JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" 1.2119 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.2120 + LEFT JOIN "timeline" AS "issue_creation" 1.2121 + ON "initiative"."issue_id" = "issue_creation"."issue_id" 1.2122 + AND "issue_creation"."event" = 'issue_created' 1.2123 + AND "timeline"."occurrence" = "issue_creation"."occurrence" 1.2124 + JOIN "draft" 1.2125 + ON "initiative"."id" = "draft"."initiative_id" 1.2126 + WHERE "timeline"."event" = 'initiative_created' 1.2127 + ORDER BY "initiative"."id", "draft"."id" 1.2128 + ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER 1.2129 + UNION ALL 1.2130 + SELECT 1.2131 + "timeline"."occurrence", 1.2132 + 'issue_state_changed'::"event_type", 1.2133 + NULL, 1.2134 + "issue"."id", 1.2135 + CASE 1.2136 + WHEN "timeline"."event" IN ( 1.2137 + 'issue_canceled', 1.2138 + 'issue_finished_without_voting', 1.2139 + 'issue_finished_after_voting' 1.2140 + ) THEN 1.2141 + "issue"."state" 1.2142 + WHEN "timeline"."event" = 'issue_accepted' THEN 1.2143 + 'discussion'::"issue_state" 1.2144 + WHEN "timeline"."event" = 'issue_half_frozen' THEN 1.2145 + 'verification'::"issue_state" 1.2146 + WHEN "timeline"."event" = 'issue_voting_started' THEN 1.2147 + 'voting'::"issue_state" 1.2148 + END, 1.2149 + NULL, 1.2150 + NULL, 1.2151 + NULL 1.2152 + FROM "timeline" 1.2153 + JOIN "issue" ON "timeline"."issue_id" = "issue"."id" 1.2154 + WHERE "timeline"."event" IN ( 1.2155 + 'issue_canceled', 1.2156 + 'issue_accepted', 1.2157 + 'issue_half_frozen', 1.2158 + 'issue_finished_without_voting', 1.2159 + 'issue_voting_started', 1.2160 + 'issue_finished_after_voting' ) 1.2161 + UNION ALL 1.2162 + SELECT 1.2163 + "timeline"."occurrence", 1.2164 + 'initiative_revoked'::"event_type", 1.2165 + "initiative"."revoked_by_member_id", 1.2166 + "issue"."id", 1.2167 + CASE 1.2168 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.2169 + 'admission'::"issue_state" 1.2170 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.2171 + 'discussion'::"issue_state" 1.2172 + ELSE 1.2173 + 'verification'::"issue_state" 1.2174 + END, 1.2175 + "initiative"."id", 1.2176 + "current_draft"."id", 1.2177 + NULL 1.2178 + FROM "timeline" 1.2179 + JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" 1.2180 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.2181 + JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id" 1.2182 + WHERE "timeline"."event" = 'initiative_revoked' 1.2183 + UNION ALL 1.2184 + SELECT 1.2185 + "timeline"."occurrence", 1.2186 + 'new_draft_created'::"event_type", 1.2187 + "draft"."author_id", 1.2188 + "issue"."id", 1.2189 + CASE 1.2190 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.2191 + 'admission'::"issue_state" 1.2192 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.2193 + 'discussion'::"issue_state" 1.2194 + ELSE 1.2195 + 'verification'::"issue_state" 1.2196 + END, 1.2197 + "initiative"."id", 1.2198 + "draft"."id", 1.2199 + NULL 1.2200 + FROM "timeline" 1.2201 + JOIN "draft" ON "timeline"."draft_id" = "draft"."id" 1.2202 + JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id" 1.2203 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.2204 + LEFT JOIN "timeline" AS "initiative_creation" 1.2205 + ON "initiative"."id" = "initiative_creation"."initiative_id" 1.2206 + AND "initiative_creation"."event" = 'initiative_created' 1.2207 + AND "timeline"."occurrence" = "initiative_creation"."occurrence" 1.2208 + WHERE "timeline"."event" = 'draft_created' 1.2209 + AND "initiative_creation"."initiative_id" ISNULL 1.2210 + UNION ALL 1.2211 + SELECT 1.2212 + "timeline"."occurrence", 1.2213 + 'suggestion_created'::"event_type", 1.2214 + "suggestion"."author_id", 1.2215 + "issue"."id", 1.2216 + CASE 1.2217 + WHEN "timeline"."occurrence" < "issue"."accepted" THEN 1.2218 + 'admission'::"issue_state" 1.2219 + WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN 1.2220 + 'discussion'::"issue_state" 1.2221 + ELSE 1.2222 + 'verification'::"issue_state" 1.2223 + END, 1.2224 + "initiative"."id", 1.2225 + NULL, 1.2226 + "suggestion"."id" 1.2227 + FROM "timeline" 1.2228 + JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id" 1.2229 + JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id" 1.2230 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.2231 + WHERE "timeline"."event" = 'suggestion_created' 1.2232 + ) AS "subquery" 1.2233 + ORDER BY "occurrence"; 1.2234 + 1.2235 +