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