liquid_feedback_core
changeset 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 | 2abc6bc59f06 |
children | 1cec513e27ac |
files | core.sql update/core-update.v1.3.0-v1.4.0_rc1.sql |
line diff
1.1 --- a/core.sql Mon Mar 07 03:34:26 2011 +0100 1.2 +++ b/core.sql Mon Mar 07 03:35:25 2011 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, 0)) 1.8 + SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.3.0-v1.4.0_rc1.sql Mon Mar 07 03:35:25 2011 +0100 2.3 @@ -0,0 +1,2081 @@ 2.4 +BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block! 2.5 + 2.6 + 2.7 +-- Update version information: 2.8 + 2.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.10 + SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) 2.11 + AS "subquery"("string", "major", "minor", "revision"); 2.12 + 2.13 + 2.14 +-- New columns "notify_level" and "notify_event_id" in "member" table: 2.15 + 2.16 +CREATE TYPE "notify_level" AS ENUM 2.17 + ('none', 'voting', 'verification', 'discussion', 'all'); 2.18 + 2.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'; 2.20 + 2.21 +ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none'; 2.22 +ALTER TABLE "member" ADD "notify_event_id" INT8; 2.23 + 2.24 +COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; 2.25 +COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; 2.26 + 2.27 + 2.28 +-- Add primary key with type SERIAL8 (INT8) for "invite_code" table: 2.29 + 2.30 +ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey"; 2.31 +ALTER TABLE "invite_code" ALTER "code" SET NOT NULL; 2.32 +ALTER TABLE "invite_code" ADD UNIQUE ("code"); 2.33 +ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY; 2.34 + 2.35 + 2.36 +-- Add index for "other_member_id" column of "contact" table: 2.37 + 2.38 +CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); 2.39 + 2.40 + 2.41 +-- New table "ignored_member": 2.42 + 2.43 +CREATE TABLE "ignored_member" ( 2.44 + PRIMARY KEY ("member_id", "other_member_id"), 2.45 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.46 + "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.47 +CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id"); 2.48 + 2.49 +COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members'; 2.50 + 2.51 +COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone'; 2.52 +COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; 2.53 + 2.54 + 2.55 +-- New table "unit" with default entry: 2.56 + 2.57 +CREATE TABLE "unit" ( 2.58 + "id" SERIAL4 PRIMARY KEY, 2.59 + "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.60 + "active" BOOLEAN NOT NULL DEFAULT TRUE, 2.61 + "name" TEXT NOT NULL, 2.62 + "description" TEXT NOT NULL DEFAULT '', 2.63 + "member_count" INT4, 2.64 + "text_search_data" TSVECTOR ); 2.65 +CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 2.66 +CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 2.67 +CREATE INDEX "unit_active_idx" ON "unit" ("active"); 2.68 +CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 2.69 +CREATE TRIGGER "update_text_search_data" 2.70 + BEFORE INSERT OR UPDATE ON "unit" 2.71 + FOR EACH ROW EXECUTE PROCEDURE 2.72 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 2.73 + "name", "description" ); 2.74 + 2.75 +COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; 2.76 + 2.77 +COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; 2.78 +COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area'; 2.79 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; 2.80 + 2.81 +INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1 2.82 + 2.83 + 2.84 +-- New column "unit_id" in table "area": 2.85 + 2.86 +ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1 2.87 + NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 2.88 +ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT; 2.89 + 2.90 +CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); 2.91 + 2.92 + 2.93 +-- Issue states: 2.94 + 2.95 +CREATE TYPE "issue_state" AS ENUM ( 2.96 + 'admission', 'discussion', 'verification', 'voting', 2.97 + 'canceled_revoked_before_accepted', 2.98 + 'canceled_issue_not_accepted', 2.99 + 'canceled_after_revocation_during_discussion', 2.100 + 'canceled_after_revocation_during_verification', 2.101 + 'calculation', 2.102 + 'canceled_no_initiative_admitted', 2.103 + 'finished_without_winner', 'finished_with_winner'); 2.104 + 2.105 +COMMENT ON TYPE "issue_state" IS 'State of issues'; 2.106 + 2.107 +ALTER TABLE "issue" ADD "state" "issue_state"; 2.108 + 2.109 +-- NOTE: Filling new column with values is done after this transaction (see below) 2.110 + 2.111 + 2.112 +-- New column "revoked_by_member_id" in table "initiative": 2.113 + 2.114 +ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 2.115 + 2.116 +COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative'; 2.117 + 2.118 +-- NOTE: Filling new column with values is done after this transaction (see below) 2.119 + 2.120 + 2.121 +-- New table "ignored_initiative": 2.122 + 2.123 +CREATE TABLE "ignored_initiative" ( 2.124 + PRIMARY KEY ("initiative_id", "member_id"), 2.125 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.126 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.127 +CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id"); 2.128 + 2.129 +COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; 2.130 + 2.131 + 2.132 +-- New table "invite_code_unit": 2.133 + 2.134 +CREATE TABLE "invite_code_unit" ( 2.135 + PRIMARY KEY ("invite_code_id", "unit_id"), 2.136 + "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.137 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.138 + 2.139 +COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; 2.140 + 2.141 +-- TODO: Table is filled after this transaction (see below) 2.142 + 2.143 + 2.144 +-- New table "privilege": 2.145 + 2.146 +CREATE TABLE "privilege" ( 2.147 + PRIMARY KEY ("unit_id", "member_id"), 2.148 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.149 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.150 + "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE, 2.151 + "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE, 2.152 + "area_manager" BOOLEAN NOT NULL DEFAULT FALSE, 2.153 + "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE, 2.154 + "voting_right" BOOLEAN NOT NULL DEFAULT TRUE ); 2.155 + 2.156 +COMMENT ON TABLE "privilege" IS 'Members rights related to each unit'; 2.157 + 2.158 +COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users'; 2.159 +COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units'; 2.160 +COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters'; 2.161 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit'; 2.162 +COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; 2.163 + 2.164 + 2.165 +-- Remove table "ignored_issue", which is no longer existent: 2.166 + 2.167 +DROP TABLE "ignored_issue"; 2.168 + 2.169 + 2.170 +-- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit': 2.171 + 2.172 +ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later 2.173 +CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue'); 2.174 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)'; 2.175 + 2.176 + 2.177 +-- Delete views and functions being dependent on TYPE "delegation_scope": 2.178 + 2.179 +DROP FUNCTION "delegation_chain" 2.180 + ( "member_id_p" "member"."id"%TYPE, 2.181 + "area_id_p" "area"."id"%TYPE, 2.182 + "issue_id_p" "issue"."id"%TYPE ); 2.183 + 2.184 +DROP FUNCTION "delegation_chain" 2.185 + ( "member_id_p" "member"."id"%TYPE, 2.186 + "area_id_p" "area"."id"%TYPE, 2.187 + "issue_id_p" "issue"."id"%TYPE, 2.188 + "simulate_trustee_id_p" "member"."id"%TYPE ); 2.189 + 2.190 +DROP TYPE "delegation_chain_row"; 2.191 + 2.192 +DROP VIEW "issue_delegation"; 2.193 +DROP VIEW "area_delegation"; 2.194 +DROP VIEW "global_delegation"; 2.195 +DROP VIEW "active_delegation"; 2.196 + 2.197 + 2.198 +-- Modify "delegation" table to use new "delegation_scope" TYPE: 2.199 + 2.200 +ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null"; 2.201 +ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope"; 2.202 + 2.203 +DROP INDEX "delegation_global_truster_id_unique_idx"; 2.204 + 2.205 +ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope" 2.206 + USING CASE WHEN "scope" = 'global' 2.207 + THEN 'unit'::"delegation_scope" 2.208 + ELSE "scope"::text::"delegation_scope" END; 2.209 + 2.210 +ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 2.211 + 2.212 +ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null" 2.213 + CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'); 2.214 + 2.215 +ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id"); 2.216 + 2.217 +COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL'; 2.218 + 2.219 +-- NOTE: Column "unit_id" filled after transaction (see below) 2.220 + 2.221 + 2.222 +-- Modify snapshot tables to use new "delegation_scope" TYPE: 2.223 + 2.224 +ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope" 2.225 + USING CASE WHEN "scope" = 'global' 2.226 + THEN 'unit'::"delegation_scope" 2.227 + ELSE "scope"::text::"delegation_scope" END; 2.228 + 2.229 +ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope" 2.230 + USING CASE WHEN "scope" = 'global' 2.231 + THEN 'unit'::"delegation_scope" 2.232 + ELSE "scope"::text::"delegation_scope" END; 2.233 + 2.234 +ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope" 2.235 + USING CASE WHEN "scope" = 'global' 2.236 + THEN 'unit'::"delegation_scope" 2.237 + ELSE "scope"::text::"delegation_scope" END; 2.238 + 2.239 + 2.240 +-- New table "non_voter": 2.241 + 2.242 +CREATE TABLE "non_voter" ( 2.243 + PRIMARY KEY ("issue_id", "member_id"), 2.244 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.245 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.246 +CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); 2.247 + 2.248 +COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; 2.249 + 2.250 + 2.251 +-- New table "issue_comment": 2.252 + 2.253 +CREATE TABLE "issue_comment" ( 2.254 + PRIMARY KEY ("issue_id", "member_id"), 2.255 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.256 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.257 + "changed" TIMESTAMPTZ NOT NULL DEFAULT now(), 2.258 + "formatting_engine" TEXT, 2.259 + "content" TEXT NOT NULL, 2.260 + "text_search_data" TSVECTOR ); 2.261 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id"); 2.262 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data"); 2.263 +CREATE TRIGGER "update_text_search_data" 2.264 + BEFORE INSERT OR UPDATE ON "issue_comment" 2.265 + FOR EACH ROW EXECUTE PROCEDURE 2.266 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 2.267 + 2.268 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues'; 2.269 + 2.270 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed'; 2.271 + 2.272 + 2.273 +-- New table "rendered_issue_comment": 2.274 + 2.275 +CREATE TABLE "rendered_issue_comment" ( 2.276 + PRIMARY KEY ("issue_id", "member_id", "format"), 2.277 + FOREIGN KEY ("issue_id", "member_id") 2.278 + REFERENCES "issue_comment" ("issue_id", "member_id") 2.279 + ON DELETE CASCADE ON UPDATE CASCADE, 2.280 + "issue_id" INT4, 2.281 + "member_id" INT4, 2.282 + "format" TEXT, 2.283 + "content" TEXT NOT NULL ); 2.284 + 2.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)'; 2.286 + 2.287 + 2.288 +-- New table "voting_comment": 2.289 + 2.290 +CREATE TABLE "voting_comment" ( 2.291 + PRIMARY KEY ("issue_id", "member_id"), 2.292 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.293 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.294 + "changed" TIMESTAMPTZ, 2.295 + "formatting_engine" TEXT, 2.296 + "content" TEXT NOT NULL, 2.297 + "text_search_data" TSVECTOR ); 2.298 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id"); 2.299 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data"); 2.300 +CREATE TRIGGER "update_text_search_data" 2.301 + BEFORE INSERT OR UPDATE ON "voting_comment" 2.302 + FOR EACH ROW EXECUTE PROCEDURE 2.303 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 2.304 + 2.305 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.'; 2.306 + 2.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.'; 2.308 + 2.309 + 2.310 +-- New table "rendered_voting_comment": 2.311 + 2.312 +CREATE TABLE "rendered_voting_comment" ( 2.313 + PRIMARY KEY ("issue_id", "member_id", "format"), 2.314 + FOREIGN KEY ("issue_id", "member_id") 2.315 + REFERENCES "voting_comment" ("issue_id", "member_id") 2.316 + ON DELETE CASCADE ON UPDATE CASCADE, 2.317 + "issue_id" INT4, 2.318 + "member_id" INT4, 2.319 + "format" TEXT, 2.320 + "content" TEXT NOT NULL ); 2.321 + 2.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)'; 2.323 + 2.324 + 2.325 +-- New table "event": 2.326 + 2.327 +CREATE TYPE "event_type" AS ENUM ( 2.328 + 'issue_state_changed', 2.329 + 'initiative_created_in_new_issue', 2.330 + 'initiative_created_in_existing_issue', 2.331 + 'initiative_revoked', 2.332 + 'new_draft_created', 2.333 + 'suggestion_created'); 2.334 + 2.335 +COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; 2.336 + 2.337 +CREATE TABLE "event" ( 2.338 + "id" SERIAL8 PRIMARY KEY, 2.339 + "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), 2.340 + "event" "event_type" NOT NULL, 2.341 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.342 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.343 + "state" "issue_state" CHECK ("state" != 'calculation'), 2.344 + "initiative_id" INT4, 2.345 + "draft_id" INT8, 2.346 + "suggestion_id" INT8, 2.347 + FOREIGN KEY ("issue_id", "initiative_id") 2.348 + REFERENCES "initiative" ("issue_id", "id") 2.349 + ON DELETE CASCADE ON UPDATE CASCADE, 2.350 + FOREIGN KEY ("initiative_id", "draft_id") 2.351 + REFERENCES "draft" ("initiative_id", "id") 2.352 + ON DELETE CASCADE ON UPDATE CASCADE, 2.353 + FOREIGN KEY ("initiative_id", "suggestion_id") 2.354 + REFERENCES "suggestion" ("initiative_id", "id") 2.355 + ON DELETE CASCADE ON UPDATE CASCADE, 2.356 + CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( 2.357 + "event" != 'issue_state_changed' OR ( 2.358 + "member_id" ISNULL AND 2.359 + "issue_id" NOTNULL AND 2.360 + "state" NOTNULL AND 2.361 + "initiative_id" ISNULL AND 2.362 + "draft_id" ISNULL AND 2.363 + "suggestion_id" ISNULL )), 2.364 + CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 2.365 + "event" NOT IN ( 2.366 + 'initiative_created_in_new_issue', 2.367 + 'initiative_created_in_existing_issue', 2.368 + 'initiative_revoked', 2.369 + 'new_draft_created' 2.370 + ) OR ( 2.371 + "member_id" NOTNULL AND 2.372 + "issue_id" NOTNULL AND 2.373 + "state" NOTNULL AND 2.374 + "initiative_id" NOTNULL AND 2.375 + "draft_id" NOTNULL AND 2.376 + "suggestion_id" ISNULL )), 2.377 + CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( 2.378 + "event" != 'suggestion_created' OR ( 2.379 + "member_id" NOTNULL AND 2.380 + "issue_id" NOTNULL AND 2.381 + "state" NOTNULL AND 2.382 + "initiative_id" NOTNULL AND 2.383 + "draft_id" ISNULL AND 2.384 + "suggestion_id" NOTNULL )) ); 2.385 + 2.386 +COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; 2.387 + 2.388 +COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred'; 2.389 +COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")'; 2.390 +COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable'; 2.391 +COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state'; 2.392 + 2.393 + 2.394 +-- Triggers to fill "event" table: 2.395 + 2.396 +CREATE FUNCTION "write_event_issue_state_changed_trigger"() 2.397 + RETURNS TRIGGER 2.398 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.399 + BEGIN 2.400 + IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN 2.401 + INSERT INTO "event" ("event", "issue_id", "state") 2.402 + VALUES ('issue_state_changed', NEW."id", NEW."state"); 2.403 + END IF; 2.404 + RETURN NULL; 2.405 + END; 2.406 + $$; 2.407 + 2.408 +CREATE TRIGGER "write_event_issue_state_changed" 2.409 + AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE 2.410 + "write_event_issue_state_changed_trigger"(); 2.411 + 2.412 +COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"'; 2.413 +COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change'; 2.414 + 2.415 +CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"() 2.416 + RETURNS TRIGGER 2.417 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.418 + DECLARE 2.419 + "initiative_row" "initiative"%ROWTYPE; 2.420 + "issue_row" "issue"%ROWTYPE; 2.421 + "event_v" "event_type"; 2.422 + BEGIN 2.423 + SELECT * INTO "initiative_row" FROM "initiative" 2.424 + WHERE "id" = NEW."initiative_id"; 2.425 + SELECT * INTO "issue_row" FROM "issue" 2.426 + WHERE "id" = "initiative_row"."issue_id"; 2.427 + IF EXISTS ( 2.428 + SELECT NULL FROM "draft" 2.429 + WHERE "initiative_id" = NEW."initiative_id" 2.430 + AND "id" != NEW."id" 2.431 + ) THEN 2.432 + "event_v" := 'new_draft_created'; 2.433 + ELSE 2.434 + IF EXISTS ( 2.435 + SELECT NULL FROM "initiative" 2.436 + WHERE "issue_id" = "initiative_row"."issue_id" 2.437 + AND "id" != "initiative_row"."id" 2.438 + ) THEN 2.439 + "event_v" := 'initiative_created_in_existing_issue'; 2.440 + ELSE 2.441 + "event_v" := 'initiative_created_in_new_issue'; 2.442 + END IF; 2.443 + END IF; 2.444 + INSERT INTO "event" ( 2.445 + "event", "member_id", 2.446 + "issue_id", "state", "initiative_id", "draft_id" 2.447 + ) VALUES ( 2.448 + "event_v", 2.449 + NEW."author_id", 2.450 + "initiative_row"."issue_id", 2.451 + "issue_row"."state", 2.452 + "initiative_row"."id", 2.453 + NEW."id" ); 2.454 + RETURN NULL; 2.455 + END; 2.456 + $$; 2.457 + 2.458 +CREATE TRIGGER "write_event_initiative_or_draft_created" 2.459 + AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE 2.460 + "write_event_initiative_or_draft_created_trigger"(); 2.461 + 2.462 +COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"'; 2.463 +COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation'; 2.464 + 2.465 +CREATE FUNCTION "write_event_initiative_revoked_trigger"() 2.466 + RETURNS TRIGGER 2.467 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.468 + DECLARE 2.469 + "issue_row" "issue"%ROWTYPE; 2.470 + BEGIN 2.471 + SELECT * INTO "issue_row" FROM "issue" 2.472 + WHERE "id" = NEW."issue_id"; 2.473 + IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN 2.474 + INSERT INTO "event" ( 2.475 + "event", "member_id", "issue_id", "state", "initiative_id" 2.476 + ) VALUES ( 2.477 + 'initiative_revoked', 2.478 + NEW."revoked_by_member_id", 2.479 + NEW."issue_id", 2.480 + "issue_row"."state", 2.481 + NEW."id" ); 2.482 + END IF; 2.483 + RETURN NULL; 2.484 + END; 2.485 + $$; 2.486 + 2.487 +CREATE TRIGGER "write_event_initiative_revoked" 2.488 + AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE 2.489 + "write_event_initiative_revoked_trigger"(); 2.490 + 2.491 +COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"'; 2.492 +COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked'; 2.493 + 2.494 +CREATE FUNCTION "write_event_suggestion_created_trigger"() 2.495 + RETURNS TRIGGER 2.496 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.497 + DECLARE 2.498 + "initiative_row" "initiative"%ROWTYPE; 2.499 + "issue_row" "issue"%ROWTYPE; 2.500 + BEGIN 2.501 + SELECT * INTO "initiative_row" FROM "initiative" 2.502 + WHERE "id" = NEW."initiative_id"; 2.503 + SELECT * INTO "issue_row" FROM "issue" 2.504 + WHERE "id" = "initiative_row"."issue_id"; 2.505 + INSERT INTO "event" ( 2.506 + "event", "member_id", 2.507 + "issue_id", "state", "initiative_id", "suggestion_id" 2.508 + ) VALUES ( 2.509 + 'suggestion_created', 2.510 + NEW."author_id", 2.511 + "initiative_row"."issue_id", 2.512 + "issue_row"."state", 2.513 + "initiative_row"."id", 2.514 + NEW."id" ); 2.515 + RETURN NULL; 2.516 + END; 2.517 + $$; 2.518 + 2.519 +CREATE TRIGGER "write_event_suggestion_created" 2.520 + AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE 2.521 + "write_event_suggestion_created_trigger"(); 2.522 + 2.523 +COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"'; 2.524 +COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation'; 2.525 + 2.526 + 2.527 +-- Modified views: 2.528 + 2.529 +CREATE VIEW "unit_delegation" AS 2.530 + SELECT 2.531 + "unit"."id" AS "unit_id", 2.532 + "delegation"."id", 2.533 + "delegation"."truster_id", 2.534 + "delegation"."trustee_id", 2.535 + "delegation"."scope" 2.536 + FROM "unit" 2.537 + JOIN "delegation" 2.538 + ON "delegation"."unit_id" = "unit"."id" 2.539 + JOIN "member" 2.540 + ON "delegation"."truster_id" = "member"."id" 2.541 + JOIN "privilege" 2.542 + ON "delegation"."unit_id" = "privilege"."unit_id" 2.543 + AND "delegation"."truster_id" = "privilege"."member_id" 2.544 + WHERE "member"."active" AND "privilege"."voting_right"; 2.545 + 2.546 +COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right'; 2.547 + 2.548 +CREATE VIEW "area_delegation" AS 2.549 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 2.550 + "area"."id" AS "area_id", 2.551 + "delegation"."id", 2.552 + "delegation"."truster_id", 2.553 + "delegation"."trustee_id", 2.554 + "delegation"."scope" 2.555 + FROM "area" 2.556 + JOIN "delegation" 2.557 + ON "delegation"."unit_id" = "area"."unit_id" 2.558 + OR "delegation"."area_id" = "area"."id" 2.559 + JOIN "member" 2.560 + ON "delegation"."truster_id" = "member"."id" 2.561 + JOIN "privilege" 2.562 + ON "area"."unit_id" = "privilege"."unit_id" 2.563 + AND "delegation"."truster_id" = "privilege"."member_id" 2.564 + WHERE "member"."active" AND "privilege"."voting_right" 2.565 + ORDER BY 2.566 + "area"."id", 2.567 + "delegation"."truster_id", 2.568 + "delegation"."scope" DESC; 2.569 + 2.570 +COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right'; 2.571 + 2.572 +CREATE VIEW "issue_delegation" AS 2.573 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 2.574 + "issue"."id" AS "issue_id", 2.575 + "delegation"."id", 2.576 + "delegation"."truster_id", 2.577 + "delegation"."trustee_id", 2.578 + "delegation"."scope" 2.579 + FROM "issue" 2.580 + JOIN "area" 2.581 + ON "area"."id" = "issue"."area_id" 2.582 + JOIN "delegation" 2.583 + ON "delegation"."unit_id" = "area"."unit_id" 2.584 + OR "delegation"."area_id" = "area"."id" 2.585 + OR "delegation"."issue_id" = "issue"."id" 2.586 + JOIN "member" 2.587 + ON "delegation"."truster_id" = "member"."id" 2.588 + JOIN "privilege" 2.589 + ON "area"."unit_id" = "privilege"."unit_id" 2.590 + AND "delegation"."truster_id" = "privilege"."member_id" 2.591 + WHERE "member"."active" AND "privilege"."voting_right" 2.592 + ORDER BY 2.593 + "issue"."id", 2.594 + "delegation"."truster_id", 2.595 + "delegation"."scope" DESC; 2.596 + 2.597 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 2.598 + 2.599 +CREATE VIEW "unit_member_count" AS 2.600 + SELECT 2.601 + "unit"."id" AS "unit_id", 2.602 + sum("member"."id") AS "member_count" 2.603 + FROM "unit" 2.604 + LEFT JOIN "privilege" 2.605 + ON "privilege"."unit_id" = "unit"."id" 2.606 + AND "privilege"."voting_right" 2.607 + LEFT JOIN "member" 2.608 + ON "member"."id" = "privilege"."member_id" 2.609 + AND "member"."active" 2.610 + GROUP BY "unit"."id"; 2.611 + 2.612 +COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; 2.613 + 2.614 +DROP VIEW "area_member_count"; 2.615 +CREATE VIEW "area_member_count" AS 2.616 + SELECT 2.617 + "area"."id" AS "area_id", 2.618 + count("member"."id") AS "direct_member_count", 2.619 + coalesce( 2.620 + sum( 2.621 + CASE WHEN "member"."id" NOTNULL THEN 2.622 + "membership_weight"("area"."id", "member"."id") 2.623 + ELSE 0 END 2.624 + ) 2.625 + ) AS "member_weight", 2.626 + coalesce( 2.627 + sum( 2.628 + CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN 2.629 + "membership_weight"("area"."id", "member"."id") 2.630 + ELSE 0 END 2.631 + ) 2.632 + ) AS "autoreject_weight" 2.633 + FROM "area" 2.634 + LEFT JOIN "membership" 2.635 + ON "area"."id" = "membership"."area_id" 2.636 + LEFT JOIN "privilege" 2.637 + ON "privilege"."unit_id" = "area"."unit_id" 2.638 + AND "privilege"."member_id" = "membership"."member_id" 2.639 + AND "privilege"."voting_right" 2.640 + LEFT JOIN "member" 2.641 + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 2.642 + AND "member"."active" 2.643 + GROUP BY "area"."id"; 2.644 + 2.645 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"'; 2.646 + 2.647 + 2.648 +-- New view "event_seen_by_member": 2.649 + 2.650 +CREATE VIEW "event_seen_by_member" AS 2.651 + SELECT 2.652 + "member"."id" AS "seen_by_member_id", 2.653 + CASE WHEN "event"."state" IN ( 2.654 + 'voting', 2.655 + 'finished_without_winner', 2.656 + 'finished_with_winner' 2.657 + ) THEN 2.658 + 'voting'::"notify_level" 2.659 + ELSE 2.660 + CASE WHEN "event"."state" IN ( 2.661 + 'verification', 2.662 + 'canceled_after_revocation_during_verification', 2.663 + 'canceled_no_initiative_admitted' 2.664 + ) THEN 2.665 + 'verification'::"notify_level" 2.666 + ELSE 2.667 + CASE WHEN "event"."state" IN ( 2.668 + 'discussion', 2.669 + 'canceled_after_revocation_during_discussion' 2.670 + ) THEN 2.671 + 'discussion'::"notify_level" 2.672 + ELSE 2.673 + 'all'::"notify_level" 2.674 + END 2.675 + END 2.676 + END AS "notify_level", 2.677 + "event".* 2.678 + FROM "member" CROSS JOIN "event" 2.679 + LEFT JOIN "issue" 2.680 + ON "event"."issue_id" = "issue"."id" 2.681 + LEFT JOIN "membership" 2.682 + ON "member"."id" = "membership"."member_id" 2.683 + AND "issue"."area_id" = "membership"."area_id" 2.684 + LEFT JOIN "interest" 2.685 + ON "member"."id" = "interest"."member_id" 2.686 + AND "event"."issue_id" = "interest"."issue_id" 2.687 + LEFT JOIN "supporter" 2.688 + ON "member"."id" = "supporter"."member_id" 2.689 + AND "event"."initiative_id" = "supporter"."initiative_id" 2.690 + LEFT JOIN "ignored_member" 2.691 + ON "member"."id" = "ignored_member"."member_id" 2.692 + AND "event"."member_id" = "ignored_member"."other_member_id" 2.693 + LEFT JOIN "ignored_initiative" 2.694 + ON "member"."id" = "ignored_initiative"."member_id" 2.695 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 2.696 + WHERE ( 2.697 + "supporter"."member_id" NOTNULL OR 2.698 + "interest"."member_id" NOTNULL OR 2.699 + ( "membership"."member_id" NOTNULL AND 2.700 + "event"."event" IN ( 2.701 + 'issue_state_changed', 2.702 + 'initiative_created_in_new_issue', 2.703 + 'initiative_created_in_existing_issue', 2.704 + 'initiative_revoked' ) ) ) 2.705 + AND "ignored_member"."member_id" ISNULL 2.706 + AND "ignored_initiative"."member_id" ISNULL; 2.707 + 2.708 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; 2.709 + 2.710 + 2.711 +-- New view "pending_notification": 2.712 + 2.713 +CREATE VIEW "pending_notification" AS 2.714 + SELECT 2.715 + "member"."id" AS "seen_by_member_id", 2.716 + "event".* 2.717 + FROM "member" CROSS JOIN "event" 2.718 + LEFT JOIN "issue" 2.719 + ON "event"."issue_id" = "issue"."id" 2.720 + LEFT JOIN "membership" 2.721 + ON "member"."id" = "membership"."member_id" 2.722 + AND "issue"."area_id" = "membership"."area_id" 2.723 + LEFT JOIN "interest" 2.724 + ON "member"."id" = "interest"."member_id" 2.725 + AND "event"."issue_id" = "interest"."issue_id" 2.726 + LEFT JOIN "supporter" 2.727 + ON "member"."id" = "supporter"."member_id" 2.728 + AND "event"."initiative_id" = "supporter"."initiative_id" 2.729 + LEFT JOIN "ignored_member" 2.730 + ON "member"."id" = "ignored_member"."member_id" 2.731 + AND "event"."member_id" = "ignored_member"."other_member_id" 2.732 + LEFT JOIN "ignored_initiative" 2.733 + ON "member"."id" = "ignored_initiative"."member_id" 2.734 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 2.735 + WHERE ( 2.736 + "member"."notify_event_id" ISNULL OR 2.737 + ( "member"."notify_event_id" NOTNULL AND 2.738 + "member"."notify_event_id" < "event"."id" ) ) 2.739 + AND ( 2.740 + ( "member"."notify_level" >= 'all' ) OR 2.741 + ( "member"."notify_level" >= 'voting' AND 2.742 + "event"."state" IN ( 2.743 + 'voting', 2.744 + 'finished_without_winner', 2.745 + 'finished_with_winner' ) ) OR 2.746 + ( "member"."notify_level" >= 'verification' AND 2.747 + "event"."state" IN ( 2.748 + 'verification', 2.749 + 'canceled_after_revocation_during_verification', 2.750 + 'canceled_no_initiative_admitted' ) ) OR 2.751 + ( "member"."notify_level" >= 'discussion' AND 2.752 + "event"."state" IN ( 2.753 + 'discussion', 2.754 + 'canceled_after_revocation_during_discussion' ) ) ) 2.755 + AND ( 2.756 + "supporter"."member_id" NOTNULL OR 2.757 + "interest"."member_id" NOTNULL OR 2.758 + ( "membership"."member_id" NOTNULL AND 2.759 + "event"."event" IN ( 2.760 + 'issue_state_changed', 2.761 + 'initiative_created_in_new_issue', 2.762 + 'initiative_created_in_existing_issue', 2.763 + 'initiative_revoked' ) ) ) 2.764 + AND "ignored_member"."member_id" ISNULL 2.765 + AND "ignored_initiative"."member_id" ISNULL; 2.766 + 2.767 +COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; 2.768 + 2.769 + 2.770 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)'; 2.771 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)'; 2.772 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)'; 2.773 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)'; 2.774 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)'; 2.775 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)'; 2.776 + 2.777 + 2.778 +-- Modified "delegation_chain" functions: 2.779 + 2.780 +CREATE TYPE "delegation_chain_row" AS ( 2.781 + "index" INT4, 2.782 + "member_id" INT4, 2.783 + "member_valid" BOOLEAN, 2.784 + "participation" BOOLEAN, 2.785 + "overridden" BOOLEAN, 2.786 + "scope_in" "delegation_scope", 2.787 + "scope_out" "delegation_scope", 2.788 + "disabled_out" BOOLEAN, 2.789 + "loop" "delegation_chain_loop_tag" ); 2.790 + 2.791 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 2.792 + 2.793 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 2.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'; 2.795 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 2.796 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 2.797 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 2.798 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; 2.799 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 2.800 + 2.801 + 2.802 +CREATE FUNCTION "delegation_chain" 2.803 + ( "member_id_p" "member"."id"%TYPE, 2.804 + "unit_id_p" "unit"."id"%TYPE, 2.805 + "area_id_p" "area"."id"%TYPE, 2.806 + "issue_id_p" "issue"."id"%TYPE, 2.807 + "simulate_trustee_id_p" "member"."id"%TYPE ) 2.808 + RETURNS SETOF "delegation_chain_row" 2.809 + LANGUAGE 'plpgsql' STABLE AS $$ 2.810 + DECLARE 2.811 + "scope_v" "delegation_scope"; 2.812 + "unit_id_v" "unit"."id"%TYPE; 2.813 + "area_id_v" "area"."id"%TYPE; 2.814 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 2.815 + "loop_member_id_v" "member"."id"%TYPE; 2.816 + "output_row" "delegation_chain_row"; 2.817 + "output_rows" "delegation_chain_row"[]; 2.818 + "delegation_row" "delegation"%ROWTYPE; 2.819 + "row_count" INT4; 2.820 + "i" INT4; 2.821 + "loop_v" BOOLEAN; 2.822 + BEGIN 2.823 + IF 2.824 + "unit_id_p" NOTNULL AND 2.825 + "area_id_p" ISNULL AND 2.826 + "issue_id_p" ISNULL 2.827 + THEN 2.828 + "scope_v" := 'unit'; 2.829 + "unit_id_v" := "unit_id_p"; 2.830 + ELSIF 2.831 + "unit_id_p" ISNULL AND 2.832 + "area_id_p" NOTNULL AND 2.833 + "issue_id_p" ISNULL 2.834 + THEN 2.835 + "scope_v" := 'area'; 2.836 + "area_id_v" := "area_id_p"; 2.837 + SELECT "unit_id" INTO "unit_id_v" 2.838 + FROM "area" WHERE "id" = "area_id_v"; 2.839 + ELSIF 2.840 + "unit_id_p" ISNULL AND 2.841 + "area_id_p" ISNULL AND 2.842 + "issue_id_p" NOTNULL 2.843 + THEN 2.844 + "scope_v" := 'issue'; 2.845 + SELECT "area_id" INTO "area_id_v" 2.846 + FROM "issue" WHERE "id" = "issue_id_p"; 2.847 + SELECT "unit_id" INTO "unit_id_v" 2.848 + FROM "area" WHERE "id" = "area_id_v"; 2.849 + ELSE 2.850 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 2.851 + END IF; 2.852 + "visited_member_ids" := '{}'; 2.853 + "loop_member_id_v" := NULL; 2.854 + "output_rows" := '{}'; 2.855 + "output_row"."index" := 0; 2.856 + "output_row"."member_id" := "member_id_p"; 2.857 + "output_row"."member_valid" := TRUE; 2.858 + "output_row"."participation" := FALSE; 2.859 + "output_row"."overridden" := FALSE; 2.860 + "output_row"."disabled_out" := FALSE; 2.861 + "output_row"."scope_out" := NULL; 2.862 + LOOP 2.863 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 2.864 + "loop_member_id_v" := "output_row"."member_id"; 2.865 + ELSE 2.866 + "visited_member_ids" := 2.867 + "visited_member_ids" || "output_row"."member_id"; 2.868 + END IF; 2.869 + IF "output_row"."participation" THEN 2.870 + "output_row"."overridden" := TRUE; 2.871 + END IF; 2.872 + "output_row"."scope_in" := "output_row"."scope_out"; 2.873 + IF EXISTS ( 2.874 + SELECT NULL FROM "member" JOIN "privilege" 2.875 + ON "privilege"."member_id" = "member"."id" 2.876 + AND "privilege"."unit_id" = "unit_id_v" 2.877 + WHERE "id" = "output_row"."member_id" 2.878 + AND "member"."active" AND "privilege"."voting_right" 2.879 + ) THEN 2.880 + IF "scope_v" = 'unit' THEN 2.881 + SELECT * INTO "delegation_row" FROM "delegation" 2.882 + WHERE "truster_id" = "output_row"."member_id" 2.883 + AND "unit_id" = "unit_id_v"; 2.884 + ELSIF "scope_v" = 'area' THEN 2.885 + "output_row"."participation" := EXISTS ( 2.886 + SELECT NULL FROM "membership" 2.887 + WHERE "area_id" = "area_id_p" 2.888 + AND "member_id" = "output_row"."member_id" 2.889 + ); 2.890 + SELECT * INTO "delegation_row" FROM "delegation" 2.891 + WHERE "truster_id" = "output_row"."member_id" 2.892 + AND ( 2.893 + "unit_id" = "unit_id_v" OR 2.894 + "area_id" = "area_id_v" 2.895 + ) 2.896 + ORDER BY "scope" DESC; 2.897 + ELSIF "scope_v" = 'issue' THEN 2.898 + "output_row"."participation" := EXISTS ( 2.899 + SELECT NULL FROM "interest" 2.900 + WHERE "issue_id" = "issue_id_p" 2.901 + AND "member_id" = "output_row"."member_id" 2.902 + ); 2.903 + SELECT * INTO "delegation_row" FROM "delegation" 2.904 + WHERE "truster_id" = "output_row"."member_id" 2.905 + AND ( 2.906 + "unit_id" = "unit_id_v" OR 2.907 + "area_id" = "area_id_v" OR 2.908 + "issue_id" = "issue_id_p" 2.909 + ) 2.910 + ORDER BY "scope" DESC; 2.911 + END IF; 2.912 + ELSE 2.913 + "output_row"."member_valid" := FALSE; 2.914 + "output_row"."participation" := FALSE; 2.915 + "output_row"."scope_out" := NULL; 2.916 + "delegation_row" := ROW(NULL); 2.917 + END IF; 2.918 + IF 2.919 + "output_row"."member_id" = "member_id_p" AND 2.920 + "simulate_trustee_id_p" NOTNULL 2.921 + THEN 2.922 + "output_row"."scope_out" := "scope_v"; 2.923 + "output_rows" := "output_rows" || "output_row"; 2.924 + "output_row"."member_id" := "simulate_trustee_id_p"; 2.925 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 2.926 + "output_row"."scope_out" := "delegation_row"."scope"; 2.927 + "output_rows" := "output_rows" || "output_row"; 2.928 + "output_row"."member_id" := "delegation_row"."trustee_id"; 2.929 + ELSIF "delegation_row"."scope" NOTNULL THEN 2.930 + "output_row"."scope_out" := "delegation_row"."scope"; 2.931 + "output_row"."disabled_out" := TRUE; 2.932 + "output_rows" := "output_rows" || "output_row"; 2.933 + EXIT; 2.934 + ELSE 2.935 + "output_row"."scope_out" := NULL; 2.936 + "output_rows" := "output_rows" || "output_row"; 2.937 + EXIT; 2.938 + END IF; 2.939 + EXIT WHEN "loop_member_id_v" NOTNULL; 2.940 + "output_row"."index" := "output_row"."index" + 1; 2.941 + END LOOP; 2.942 + "row_count" := array_upper("output_rows", 1); 2.943 + "i" := 1; 2.944 + "loop_v" := FALSE; 2.945 + LOOP 2.946 + "output_row" := "output_rows"["i"]; 2.947 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 2.948 + IF "loop_v" THEN 2.949 + IF "i" + 1 = "row_count" THEN 2.950 + "output_row"."loop" := 'last'; 2.951 + ELSIF "i" = "row_count" THEN 2.952 + "output_row"."loop" := 'repetition'; 2.953 + ELSE 2.954 + "output_row"."loop" := 'intermediate'; 2.955 + END IF; 2.956 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 2.957 + "output_row"."loop" := 'first'; 2.958 + "loop_v" := TRUE; 2.959 + END IF; 2.960 + IF "scope_v" = 'unit' THEN 2.961 + "output_row"."participation" := NULL; 2.962 + END IF; 2.963 + RETURN NEXT "output_row"; 2.964 + "i" := "i" + 1; 2.965 + END LOOP; 2.966 + RETURN; 2.967 + END; 2.968 + $$; 2.969 + 2.970 +COMMENT ON FUNCTION "delegation_chain" 2.971 + ( "member"."id"%TYPE, 2.972 + "unit"."id"%TYPE, 2.973 + "area"."id"%TYPE, 2.974 + "issue"."id"%TYPE, 2.975 + "member"."id"%TYPE ) 2.976 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 2.977 + 2.978 + 2.979 +CREATE FUNCTION "delegation_chain" 2.980 + ( "member_id_p" "member"."id"%TYPE, 2.981 + "unit_id_p" "unit"."id"%TYPE, 2.982 + "area_id_p" "area"."id"%TYPE, 2.983 + "issue_id_p" "issue"."id"%TYPE ) 2.984 + RETURNS SETOF "delegation_chain_row" 2.985 + LANGUAGE 'plpgsql' STABLE AS $$ 2.986 + DECLARE 2.987 + "result_row" "delegation_chain_row"; 2.988 + BEGIN 2.989 + FOR "result_row" IN 2.990 + SELECT * FROM "delegation_chain"( 2.991 + "member_id_p", "area_id_p", "issue_id_p", NULL 2.992 + ) 2.993 + LOOP 2.994 + RETURN NEXT "result_row"; 2.995 + END LOOP; 2.996 + RETURN; 2.997 + END; 2.998 + $$; 2.999 + 2.1000 +COMMENT ON FUNCTION "delegation_chain" 2.1001 + ( "member"."id"%TYPE, 2.1002 + "unit"."id"%TYPE, 2.1003 + "area"."id"%TYPE, 2.1004 + "issue"."id"%TYPE ) 2.1005 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 2.1006 + 2.1007 + 2.1008 +-- Modified core functions: 2.1009 + 2.1010 +CREATE OR REPLACE FUNCTION "lock_issue" 2.1011 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1012 + RETURNS VOID 2.1013 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1014 + BEGIN 2.1015 + LOCK TABLE "member" IN SHARE MODE; 2.1016 + LOCK TABLE "privilege" IN SHARE MODE; 2.1017 + LOCK TABLE "membership" IN SHARE MODE; 2.1018 + LOCK TABLE "policy" IN SHARE MODE; 2.1019 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 2.1020 + -- NOTE: The row-level exclusive lock in combination with the 2.1021 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 2.1022 + -- acquire a row-level share lock on the issue) ensure that no data 2.1023 + -- is changed, which could affect calculation of snapshots or 2.1024 + -- counting of votes. Table "delegation" must be table-level-locked, 2.1025 + -- as it also contains issue- and global-scope delegations. 2.1026 + LOCK TABLE "delegation" IN SHARE MODE; 2.1027 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 2.1028 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 2.1029 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 2.1030 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 2.1031 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 2.1032 + RETURN; 2.1033 + END; 2.1034 + $$; 2.1035 + 2.1036 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 2.1037 + RETURNS VOID 2.1038 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1039 + BEGIN 2.1040 + LOCK TABLE "member" IN SHARE MODE; 2.1041 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 2.1042 + LOCK TABLE "unit" IN EXCLUSIVE MODE; 2.1043 + LOCK TABLE "area" IN EXCLUSIVE MODE; 2.1044 + LOCK TABLE "privilege" IN SHARE MODE; 2.1045 + LOCK TABLE "membership" IN SHARE MODE; 2.1046 + DELETE FROM "member_count"; 2.1047 + INSERT INTO "member_count" ("total_count") 2.1048 + SELECT "total_count" FROM "member_count_view"; 2.1049 + UPDATE "unit" SET "member_count" = "view"."member_count" 2.1050 + FROM "unit_member_count" AS "view" 2.1051 + WHERE "view"."unit_id" = "unit"."id"; 2.1052 + UPDATE "area" SET 2.1053 + "direct_member_count" = "view"."direct_member_count", 2.1054 + "member_weight" = "view"."member_weight", 2.1055 + "autoreject_weight" = "view"."autoreject_weight" 2.1056 + FROM "area_member_count" AS "view" 2.1057 + WHERE "view"."area_id" = "area"."id"; 2.1058 + RETURN; 2.1059 + END; 2.1060 + $$; 2.1061 + 2.1062 +CREATE OR REPLACE FUNCTION "create_population_snapshot" 2.1063 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1064 + RETURNS VOID 2.1065 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1066 + DECLARE 2.1067 + "member_id_v" "member"."id"%TYPE; 2.1068 + BEGIN 2.1069 + DELETE FROM "direct_population_snapshot" 2.1070 + WHERE "issue_id" = "issue_id_p" 2.1071 + AND "event" = 'periodic'; 2.1072 + DELETE FROM "delegating_population_snapshot" 2.1073 + WHERE "issue_id" = "issue_id_p" 2.1074 + AND "event" = 'periodic'; 2.1075 + INSERT INTO "direct_population_snapshot" 2.1076 + ("issue_id", "event", "member_id") 2.1077 + SELECT 2.1078 + "issue_id_p" AS "issue_id", 2.1079 + 'periodic'::"snapshot_event" AS "event", 2.1080 + "member"."id" AS "member_id" 2.1081 + FROM "issue" 2.1082 + JOIN "area" ON "issue"."area_id" = "area"."id" 2.1083 + JOIN "membership" ON "area"."id" = "membership"."area_id" 2.1084 + JOIN "member" ON "membership"."member_id" = "member"."id" 2.1085 + JOIN "privilege" 2.1086 + ON "privilege"."unit_id" = "area"."unit_id" 2.1087 + AND "privilege"."member_id" = "member"."id" 2.1088 + WHERE "issue"."id" = "issue_id_p" 2.1089 + AND "member"."active" AND "privilege"."voting_right" 2.1090 + UNION 2.1091 + SELECT 2.1092 + "issue_id_p" AS "issue_id", 2.1093 + 'periodic'::"snapshot_event" AS "event", 2.1094 + "member"."id" AS "member_id" 2.1095 + FROM "issue" 2.1096 + JOIN "area" ON "issue"."area_id" = "area"."id" 2.1097 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 2.1098 + JOIN "member" ON "interest"."member_id" = "member"."id" 2.1099 + JOIN "privilege" 2.1100 + ON "privilege"."unit_id" = "area"."unit_id" 2.1101 + AND "privilege"."member_id" = "member"."id" 2.1102 + WHERE "issue"."id" = "issue_id_p" 2.1103 + AND "member"."active" AND "privilege"."voting_right"; 2.1104 + FOR "member_id_v" IN 2.1105 + SELECT "member_id" FROM "direct_population_snapshot" 2.1106 + WHERE "issue_id" = "issue_id_p" 2.1107 + AND "event" = 'periodic' 2.1108 + LOOP 2.1109 + UPDATE "direct_population_snapshot" SET 2.1110 + "weight" = 1 + 2.1111 + "weight_of_added_delegations_for_population_snapshot"( 2.1112 + "issue_id_p", 2.1113 + "member_id_v", 2.1114 + '{}' 2.1115 + ) 2.1116 + WHERE "issue_id" = "issue_id_p" 2.1117 + AND "event" = 'periodic' 2.1118 + AND "member_id" = "member_id_v"; 2.1119 + END LOOP; 2.1120 + RETURN; 2.1121 + END; 2.1122 + $$; 2.1123 + 2.1124 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 2.1125 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1126 + RETURNS VOID 2.1127 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1128 + DECLARE 2.1129 + "member_id_v" "member"."id"%TYPE; 2.1130 + BEGIN 2.1131 + DELETE FROM "direct_interest_snapshot" 2.1132 + WHERE "issue_id" = "issue_id_p" 2.1133 + AND "event" = 'periodic'; 2.1134 + DELETE FROM "delegating_interest_snapshot" 2.1135 + WHERE "issue_id" = "issue_id_p" 2.1136 + AND "event" = 'periodic'; 2.1137 + DELETE FROM "direct_supporter_snapshot" 2.1138 + WHERE "issue_id" = "issue_id_p" 2.1139 + AND "event" = 'periodic'; 2.1140 + INSERT INTO "direct_interest_snapshot" 2.1141 + ("issue_id", "event", "member_id", "voting_requested") 2.1142 + SELECT 2.1143 + "issue_id_p" AS "issue_id", 2.1144 + 'periodic' AS "event", 2.1145 + "member"."id" AS "member_id", 2.1146 + "interest"."voting_requested" 2.1147 + FROM "issue" 2.1148 + JOIN "area" ON "issue"."area_id" = "area"."id" 2.1149 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 2.1150 + JOIN "member" ON "interest"."member_id" = "member"."id" 2.1151 + JOIN "privilege" 2.1152 + ON "privilege"."unit_id" = "area"."unit_id" 2.1153 + AND "privilege"."member_id" = "member"."id" 2.1154 + WHERE "issue"."id" = "issue_id_p" 2.1155 + AND "member"."active" AND "privilege"."voting_right"; 2.1156 + FOR "member_id_v" IN 2.1157 + SELECT "member_id" FROM "direct_interest_snapshot" 2.1158 + WHERE "issue_id" = "issue_id_p" 2.1159 + AND "event" = 'periodic' 2.1160 + LOOP 2.1161 + UPDATE "direct_interest_snapshot" SET 2.1162 + "weight" = 1 + 2.1163 + "weight_of_added_delegations_for_interest_snapshot"( 2.1164 + "issue_id_p", 2.1165 + "member_id_v", 2.1166 + '{}' 2.1167 + ) 2.1168 + WHERE "issue_id" = "issue_id_p" 2.1169 + AND "event" = 'periodic' 2.1170 + AND "member_id" = "member_id_v"; 2.1171 + END LOOP; 2.1172 + INSERT INTO "direct_supporter_snapshot" 2.1173 + ( "issue_id", "initiative_id", "event", "member_id", 2.1174 + "informed", "satisfied" ) 2.1175 + SELECT 2.1176 + "issue_id_p" AS "issue_id", 2.1177 + "initiative"."id" AS "initiative_id", 2.1178 + 'periodic' AS "event", 2.1179 + "supporter"."member_id" AS "member_id", 2.1180 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 2.1181 + NOT EXISTS ( 2.1182 + SELECT NULL FROM "critical_opinion" 2.1183 + WHERE "initiative_id" = "initiative"."id" 2.1184 + AND "member_id" = "supporter"."member_id" 2.1185 + ) AS "satisfied" 2.1186 + FROM "initiative" 2.1187 + JOIN "supporter" 2.1188 + ON "supporter"."initiative_id" = "initiative"."id" 2.1189 + JOIN "current_draft" 2.1190 + ON "initiative"."id" = "current_draft"."initiative_id" 2.1191 + JOIN "direct_interest_snapshot" 2.1192 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 2.1193 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 2.1194 + AND "event" = 'periodic' 2.1195 + WHERE "initiative"."issue_id" = "issue_id_p"; 2.1196 + RETURN; 2.1197 + END; 2.1198 + $$; 2.1199 + 2.1200 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 2.1201 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1202 + RETURNS VOID 2.1203 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1204 + DECLARE 2.1205 + "issue_row" "issue"%ROWTYPE; 2.1206 + "policy_row" "policy"%ROWTYPE; 2.1207 + "initiative_row" "initiative"%ROWTYPE; 2.1208 + BEGIN 2.1209 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1210 + SELECT * INTO "policy_row" 2.1211 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 2.1212 + PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 2.1213 + FOR "initiative_row" IN 2.1214 + SELECT * FROM "initiative" 2.1215 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.1216 + LOOP 2.1217 + IF 2.1218 + "initiative_row"."satisfied_supporter_count" > 0 AND 2.1219 + "initiative_row"."satisfied_supporter_count" * 2.1220 + "policy_row"."initiative_quorum_den" >= 2.1221 + "issue_row"."population" * "policy_row"."initiative_quorum_num" 2.1222 + THEN 2.1223 + UPDATE "initiative" SET "admitted" = TRUE 2.1224 + WHERE "id" = "initiative_row"."id"; 2.1225 + ELSE 2.1226 + UPDATE "initiative" SET "admitted" = FALSE 2.1227 + WHERE "id" = "initiative_row"."id"; 2.1228 + END IF; 2.1229 + END LOOP; 2.1230 + IF EXISTS ( 2.1231 + SELECT NULL FROM "initiative" 2.1232 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 2.1233 + ) THEN 2.1234 + UPDATE "issue" SET 2.1235 + "state" = 'voting', 2.1236 + "accepted" = coalesce("accepted", now()), 2.1237 + "half_frozen" = coalesce("half_frozen", now()), 2.1238 + "fully_frozen" = now() 2.1239 + WHERE "id" = "issue_id_p"; 2.1240 + ELSE 2.1241 + UPDATE "issue" SET 2.1242 + "state" = 'canceled_no_initiative_admitted', 2.1243 + "accepted" = coalesce("accepted", now()), 2.1244 + "half_frozen" = coalesce("half_frozen", now()), 2.1245 + "fully_frozen" = now(), 2.1246 + "closed" = now() 2.1247 + WHERE "id" = "issue_id_p"; 2.1248 + -- NOTE: The following DELETE statements have effect only when 2.1249 + -- issue state has been manipulated 2.1250 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 2.1251 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 2.1252 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.1253 + END IF; 2.1254 + RETURN; 2.1255 + END; 2.1256 + $$; 2.1257 + 2.1258 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.1259 + RETURNS VOID 2.1260 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1261 + DECLARE 2.1262 + "area_id_v" "area"."id"%TYPE; 2.1263 + "unit_id_v" "unit"."id"%TYPE; 2.1264 + "member_id_v" "member"."id"%TYPE; 2.1265 + BEGIN 2.1266 + PERFORM "lock_issue"("issue_id_p"); 2.1267 + SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.1268 + SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.1269 + DELETE FROM "delegating_voter" 2.1270 + WHERE "issue_id" = "issue_id_p"; 2.1271 + DELETE FROM "direct_voter" 2.1272 + WHERE "issue_id" = "issue_id_p" 2.1273 + AND "autoreject" = TRUE; 2.1274 + DELETE FROM "direct_voter" 2.1275 + USING ( 2.1276 + SELECT 2.1277 + "direct_voter"."member_id" 2.1278 + FROM "direct_voter" 2.1279 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.1280 + LEFT JOIN "privilege" 2.1281 + ON "privilege"."unit_id" = "unit_id_v" 2.1282 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.1283 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.1284 + "member"."active" = FALSE OR 2.1285 + "privilege"."voting_right" ISNULL OR 2.1286 + "privilege"."voting_right" = FALSE 2.1287 + ) 2.1288 + ) AS "subquery" 2.1289 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.1290 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.1291 + UPDATE "direct_voter" SET "weight" = 1 2.1292 + WHERE "issue_id" = "issue_id_p"; 2.1293 + PERFORM "add_vote_delegations"("issue_id_p"); 2.1294 + FOR "member_id_v" IN 2.1295 + SELECT "interest"."member_id" 2.1296 + FROM "interest" 2.1297 + JOIN "member" 2.1298 + ON "interest"."member_id" = "member"."id" 2.1299 + LEFT JOIN "direct_voter" 2.1300 + ON "interest"."member_id" = "direct_voter"."member_id" 2.1301 + AND "interest"."issue_id" = "direct_voter"."issue_id" 2.1302 + LEFT JOIN "delegating_voter" 2.1303 + ON "interest"."member_id" = "delegating_voter"."member_id" 2.1304 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 2.1305 + WHERE "interest"."issue_id" = "issue_id_p" 2.1306 + AND "interest"."autoreject" = TRUE 2.1307 + AND "member"."active" 2.1308 + AND "direct_voter"."member_id" ISNULL 2.1309 + AND "delegating_voter"."member_id" ISNULL 2.1310 + UNION SELECT "membership"."member_id" 2.1311 + FROM "membership" 2.1312 + JOIN "member" 2.1313 + ON "membership"."member_id" = "member"."id" 2.1314 + LEFT JOIN "interest" 2.1315 + ON "membership"."member_id" = "interest"."member_id" 2.1316 + AND "interest"."issue_id" = "issue_id_p" 2.1317 + LEFT JOIN "direct_voter" 2.1318 + ON "membership"."member_id" = "direct_voter"."member_id" 2.1319 + AND "direct_voter"."issue_id" = "issue_id_p" 2.1320 + LEFT JOIN "delegating_voter" 2.1321 + ON "membership"."member_id" = "delegating_voter"."member_id" 2.1322 + AND "delegating_voter"."issue_id" = "issue_id_p" 2.1323 + WHERE "membership"."area_id" = "area_id_v" 2.1324 + AND "membership"."autoreject" = TRUE 2.1325 + AND "member"."active" 2.1326 + AND "interest"."autoreject" ISNULL 2.1327 + AND "direct_voter"."member_id" ISNULL 2.1328 + AND "delegating_voter"."member_id" ISNULL 2.1329 + LOOP 2.1330 + INSERT INTO "direct_voter" 2.1331 + ("member_id", "issue_id", "weight", "autoreject") VALUES 2.1332 + ("member_id_v", "issue_id_p", 1, TRUE); 2.1333 + INSERT INTO "vote" ( 2.1334 + "member_id", 2.1335 + "issue_id", 2.1336 + "initiative_id", 2.1337 + "grade" 2.1338 + ) SELECT 2.1339 + "member_id_v" AS "member_id", 2.1340 + "issue_id_p" AS "issue_id", 2.1341 + "id" AS "initiative_id", 2.1342 + -1 AS "grade" 2.1343 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.1344 + END LOOP; 2.1345 + PERFORM "add_vote_delegations"("issue_id_p"); 2.1346 + UPDATE "issue" SET 2.1347 + "state" = 'calculation', 2.1348 + "closed" = now(), 2.1349 + "voter_count" = ( 2.1350 + SELECT coalesce(sum("weight"), 0) 2.1351 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.1352 + ) 2.1353 + WHERE "id" = "issue_id_p"; 2.1354 + UPDATE "initiative" SET 2.1355 + "positive_votes" = "vote_counts"."positive_votes", 2.1356 + "negative_votes" = "vote_counts"."negative_votes", 2.1357 + "agreed" = CASE WHEN "majority_strict" THEN 2.1358 + "vote_counts"."positive_votes" * "majority_den" > 2.1359 + "majority_num" * 2.1360 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.1361 + ELSE 2.1362 + "vote_counts"."positive_votes" * "majority_den" >= 2.1363 + "majority_num" * 2.1364 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.1365 + END 2.1366 + FROM 2.1367 + ( SELECT 2.1368 + "initiative"."id" AS "initiative_id", 2.1369 + coalesce( 2.1370 + sum( 2.1371 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 2.1372 + ), 2.1373 + 0 2.1374 + ) AS "positive_votes", 2.1375 + coalesce( 2.1376 + sum( 2.1377 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 2.1378 + ), 2.1379 + 0 2.1380 + ) AS "negative_votes" 2.1381 + FROM "initiative" 2.1382 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 2.1383 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 2.1384 + LEFT JOIN "direct_voter" 2.1385 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 2.1386 + LEFT JOIN "vote" 2.1387 + ON "vote"."initiative_id" = "initiative"."id" 2.1388 + AND "vote"."member_id" = "direct_voter"."member_id" 2.1389 + WHERE "initiative"."issue_id" = "issue_id_p" 2.1390 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 2.1391 + GROUP BY "initiative"."id" 2.1392 + ) AS "vote_counts", 2.1393 + "issue", 2.1394 + "policy" 2.1395 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 2.1396 + AND "issue"."id" = "initiative"."issue_id" 2.1397 + AND "policy"."id" = "issue"."policy_id"; 2.1398 + -- NOTE: "closed" column of issue must be set at this point 2.1399 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.1400 + INSERT INTO "battle" ( 2.1401 + "issue_id", 2.1402 + "winning_initiative_id", "losing_initiative_id", 2.1403 + "count" 2.1404 + ) SELECT 2.1405 + "issue_id", 2.1406 + "winning_initiative_id", "losing_initiative_id", 2.1407 + "count" 2.1408 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.1409 + END; 2.1410 + $$; 2.1411 + 2.1412 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 2.1413 + RETURNS VOID 2.1414 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1415 + DECLARE 2.1416 + "dimension_v" INTEGER; 2.1417 + "vote_matrix" INT4[][]; -- absolute votes 2.1418 + "matrix" INT8[][]; -- defeat strength / best paths 2.1419 + "i" INTEGER; 2.1420 + "j" INTEGER; 2.1421 + "k" INTEGER; 2.1422 + "battle_row" "battle"%ROWTYPE; 2.1423 + "rank_ary" INT4[]; 2.1424 + "rank_v" INT4; 2.1425 + "done_v" INTEGER; 2.1426 + "winners_ary" INTEGER[]; 2.1427 + "initiative_id_v" "initiative"."id"%TYPE; 2.1428 + BEGIN 2.1429 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 2.1430 + SELECT count(1) INTO "dimension_v" FROM "initiative" 2.1431 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 2.1432 + IF "dimension_v" = 1 THEN 2.1433 + UPDATE "initiative" SET "rank" = 1 2.1434 + WHERE "issue_id" = "issue_id_p" AND "agreed"; 2.1435 + ELSIF "dimension_v" > 1 THEN 2.1436 + -- Create "vote_matrix" with absolute number of votes in pairwise 2.1437 + -- comparison: 2.1438 + "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 2.1439 + "i" := 1; 2.1440 + "j" := 2; 2.1441 + FOR "battle_row" IN 2.1442 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 2.1443 + ORDER BY "winning_initiative_id", "losing_initiative_id" 2.1444 + LOOP 2.1445 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 2.1446 + IF "j" = "dimension_v" THEN 2.1447 + "i" := "i" + 1; 2.1448 + "j" := 1; 2.1449 + ELSE 2.1450 + "j" := "j" + 1; 2.1451 + IF "j" = "i" THEN 2.1452 + "j" := "j" + 1; 2.1453 + END IF; 2.1454 + END IF; 2.1455 + END LOOP; 2.1456 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 2.1457 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 2.1458 + END IF; 2.1459 + -- Store defeat strengths in "matrix" using "defeat_strength" 2.1460 + -- function: 2.1461 + "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 2.1462 + "i" := 1; 2.1463 + LOOP 2.1464 + "j" := 1; 2.1465 + LOOP 2.1466 + IF "i" != "j" THEN 2.1467 + "matrix"["i"]["j"] := "defeat_strength"( 2.1468 + "vote_matrix"["i"]["j"], 2.1469 + "vote_matrix"["j"]["i"] 2.1470 + ); 2.1471 + END IF; 2.1472 + EXIT WHEN "j" = "dimension_v"; 2.1473 + "j" := "j" + 1; 2.1474 + END LOOP; 2.1475 + EXIT WHEN "i" = "dimension_v"; 2.1476 + "i" := "i" + 1; 2.1477 + END LOOP; 2.1478 + -- Find best paths: 2.1479 + "i" := 1; 2.1480 + LOOP 2.1481 + "j" := 1; 2.1482 + LOOP 2.1483 + IF "i" != "j" THEN 2.1484 + "k" := 1; 2.1485 + LOOP 2.1486 + IF "i" != "k" AND "j" != "k" THEN 2.1487 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 2.1488 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 2.1489 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 2.1490 + END IF; 2.1491 + ELSE 2.1492 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 2.1493 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 2.1494 + END IF; 2.1495 + END IF; 2.1496 + END IF; 2.1497 + EXIT WHEN "k" = "dimension_v"; 2.1498 + "k" := "k" + 1; 2.1499 + END LOOP; 2.1500 + END IF; 2.1501 + EXIT WHEN "j" = "dimension_v"; 2.1502 + "j" := "j" + 1; 2.1503 + END LOOP; 2.1504 + EXIT WHEN "i" = "dimension_v"; 2.1505 + "i" := "i" + 1; 2.1506 + END LOOP; 2.1507 + -- Determine order of winners: 2.1508 + "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 2.1509 + "rank_v" := 1; 2.1510 + "done_v" := 0; 2.1511 + LOOP 2.1512 + "winners_ary" := '{}'; 2.1513 + "i" := 1; 2.1514 + LOOP 2.1515 + IF "rank_ary"["i"] ISNULL THEN 2.1516 + "j" := 1; 2.1517 + LOOP 2.1518 + IF 2.1519 + "i" != "j" AND 2.1520 + "rank_ary"["j"] ISNULL AND 2.1521 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 2.1522 + THEN 2.1523 + -- someone else is better 2.1524 + EXIT; 2.1525 + END IF; 2.1526 + IF "j" = "dimension_v" THEN 2.1527 + -- noone is better 2.1528 + "winners_ary" := "winners_ary" || "i"; 2.1529 + EXIT; 2.1530 + END IF; 2.1531 + "j" := "j" + 1; 2.1532 + END LOOP; 2.1533 + END IF; 2.1534 + EXIT WHEN "i" = "dimension_v"; 2.1535 + "i" := "i" + 1; 2.1536 + END LOOP; 2.1537 + "i" := 1; 2.1538 + LOOP 2.1539 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 2.1540 + "done_v" := "done_v" + 1; 2.1541 + EXIT WHEN "i" = array_upper("winners_ary", 1); 2.1542 + "i" := "i" + 1; 2.1543 + END LOOP; 2.1544 + EXIT WHEN "done_v" = "dimension_v"; 2.1545 + "rank_v" := "rank_v" + 1; 2.1546 + END LOOP; 2.1547 + -- write preliminary ranks: 2.1548 + "i" := 1; 2.1549 + FOR "initiative_id_v" IN 2.1550 + SELECT "id" FROM "initiative" 2.1551 + WHERE "issue_id" = "issue_id_p" AND "agreed" 2.1552 + ORDER BY "id" 2.1553 + LOOP 2.1554 + UPDATE "initiative" SET "rank" = "rank_ary"["i"] 2.1555 + WHERE "id" = "initiative_id_v"; 2.1556 + "i" := "i" + 1; 2.1557 + END LOOP; 2.1558 + IF "i" != "dimension_v" + 1 THEN 2.1559 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 2.1560 + END IF; 2.1561 + -- straighten ranks (start counting with 1, no equal ranks): 2.1562 + "rank_v" := 1; 2.1563 + FOR "initiative_id_v" IN 2.1564 + SELECT "id" FROM "initiative" 2.1565 + WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL 2.1566 + ORDER BY 2.1567 + "rank", 2.1568 + "vote_ratio"("positive_votes", "negative_votes") DESC, 2.1569 + "id" 2.1570 + LOOP 2.1571 + UPDATE "initiative" SET "rank" = "rank_v" 2.1572 + WHERE "id" = "initiative_id_v"; 2.1573 + "rank_v" := "rank_v" + 1; 2.1574 + END LOOP; 2.1575 + END IF; 2.1576 + -- mark issue as finished 2.1577 + UPDATE "issue" SET 2.1578 + "state" = 2.1579 + CASE WHEN NOT EXISTS ( 2.1580 + SELECT NULL FROM "initiative" 2.1581 + WHERE "issue_id" = "issue_id_p" AND "admitted" 2.1582 + ) THEN 2.1583 + 'canceled_no_initiative_admitted'::"issue_state" 2.1584 + ELSE 2.1585 + CASE WHEN "dimension_v" = 0 THEN 2.1586 + 'finished_without_winner'::"issue_state" 2.1587 + ELSE 2.1588 + 'finished_with_winner'::"issue_state" 2.1589 + END 2.1590 + END, 2.1591 + "ranks_available" = TRUE 2.1592 + WHERE "id" = "issue_id_p"; 2.1593 + RETURN; 2.1594 + END; 2.1595 + $$; 2.1596 + 2.1597 +CREATE OR REPLACE FUNCTION "check_issue" 2.1598 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1599 + RETURNS VOID 2.1600 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1601 + DECLARE 2.1602 + "issue_row" "issue"%ROWTYPE; 2.1603 + "policy_row" "policy"%ROWTYPE; 2.1604 + "voting_requested_v" BOOLEAN; 2.1605 + BEGIN 2.1606 + PERFORM "lock_issue"("issue_id_p"); 2.1607 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1608 + -- only process open issues: 2.1609 + IF "issue_row"."closed" ISNULL THEN 2.1610 + SELECT * INTO "policy_row" FROM "policy" 2.1611 + WHERE "id" = "issue_row"."policy_id"; 2.1612 + -- create a snapshot, unless issue is already fully frozen: 2.1613 + IF "issue_row"."fully_frozen" ISNULL THEN 2.1614 + PERFORM "create_snapshot"("issue_id_p"); 2.1615 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1616 + END IF; 2.1617 + -- eventually close or accept issues, which have not been accepted: 2.1618 + IF "issue_row"."accepted" ISNULL THEN 2.1619 + IF EXISTS ( 2.1620 + SELECT NULL FROM "initiative" 2.1621 + WHERE "issue_id" = "issue_id_p" 2.1622 + AND "supporter_count" > 0 2.1623 + AND "supporter_count" * "policy_row"."issue_quorum_den" 2.1624 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.1625 + ) THEN 2.1626 + -- accept issues, if supporter count is high enough 2.1627 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1628 + -- NOTE: "issue_row" used later 2.1629 + "issue_row"."state" := 'discussion'; 2.1630 + "issue_row"."accepted" := now(); 2.1631 + UPDATE "issue" SET 2.1632 + "state" = "issue_row"."state", 2.1633 + "accepted" = "issue_row"."accepted" 2.1634 + WHERE "id" = "issue_row"."id"; 2.1635 + ELSIF 2.1636 + now() >= "issue_row"."created" + "issue_row"."admission_time" 2.1637 + THEN 2.1638 + -- close issues, if admission time has expired 2.1639 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1640 + UPDATE "issue" SET 2.1641 + "state" = 'canceled_issue_not_accepted', 2.1642 + "closed" = now() 2.1643 + WHERE "id" = "issue_row"."id"; 2.1644 + END IF; 2.1645 + END IF; 2.1646 + -- eventually half freeze issues: 2.1647 + IF 2.1648 + -- NOTE: issue can't be closed at this point, if it has been accepted 2.1649 + "issue_row"."accepted" NOTNULL AND 2.1650 + "issue_row"."half_frozen" ISNULL 2.1651 + THEN 2.1652 + SELECT 2.1653 + CASE 2.1654 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 2.1655 + TRUE 2.1656 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 2.1657 + FALSE 2.1658 + ELSE NULL 2.1659 + END 2.1660 + INTO "voting_requested_v" 2.1661 + FROM "issue" WHERE "id" = "issue_id_p"; 2.1662 + IF 2.1663 + "voting_requested_v" OR ( 2.1664 + "voting_requested_v" ISNULL AND 2.1665 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.1666 + ) 2.1667 + THEN 2.1668 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.1669 + -- NOTE: "issue_row" used later 2.1670 + "issue_row"."state" := 'verification'; 2.1671 + "issue_row"."half_frozen" := now(); 2.1672 + UPDATE "issue" SET 2.1673 + "state" = "issue_row"."state", 2.1674 + "half_frozen" = "issue_row"."half_frozen" 2.1675 + WHERE "id" = "issue_row"."id"; 2.1676 + END IF; 2.1677 + END IF; 2.1678 + -- close issues after some time, if all initiatives have been revoked: 2.1679 + IF 2.1680 + "issue_row"."closed" ISNULL AND 2.1681 + NOT EXISTS ( 2.1682 + -- all initiatives are revoked 2.1683 + SELECT NULL FROM "initiative" 2.1684 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.1685 + ) AND ( 2.1686 + -- and issue has not been accepted yet 2.1687 + "issue_row"."accepted" ISNULL OR 2.1688 + NOT EXISTS ( 2.1689 + -- or no initiatives have been revoked lately 2.1690 + SELECT NULL FROM "initiative" 2.1691 + WHERE "issue_id" = "issue_id_p" 2.1692 + AND now() < "revoked" + "issue_row"."verification_time" 2.1693 + ) OR ( 2.1694 + -- or verification time has elapsed 2.1695 + "issue_row"."half_frozen" NOTNULL AND 2.1696 + "issue_row"."fully_frozen" ISNULL AND 2.1697 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1698 + ) 2.1699 + ) 2.1700 + THEN 2.1701 + -- NOTE: "issue_row" used later 2.1702 + IF "issue_row"."accepted" ISNULL THEN 2.1703 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.1704 + ELSIF "issue_row"."half_frozen" ISNULL THEN 2.1705 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.1706 + ELSE 2.1707 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.1708 + END IF; 2.1709 + "issue_row"."closed" := now(); 2.1710 + UPDATE "issue" SET 2.1711 + "state" = "issue_row"."state", 2.1712 + "closed" = "issue_row"."closed" 2.1713 + WHERE "id" = "issue_row"."id"; 2.1714 + END IF; 2.1715 + -- fully freeze issue after verification time: 2.1716 + IF 2.1717 + "issue_row"."half_frozen" NOTNULL AND 2.1718 + "issue_row"."fully_frozen" ISNULL AND 2.1719 + "issue_row"."closed" ISNULL AND 2.1720 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1721 + THEN 2.1722 + PERFORM "freeze_after_snapshot"("issue_id_p"); 2.1723 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.1724 + END IF; 2.1725 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1726 + -- close issue by calling close_voting(...) after voting time: 2.1727 + IF 2.1728 + "issue_row"."closed" ISNULL AND 2.1729 + "issue_row"."fully_frozen" NOTNULL AND 2.1730 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.1731 + THEN 2.1732 + PERFORM "close_voting"("issue_id_p"); 2.1733 + -- calculate ranks will not consume much time and can be done now 2.1734 + PERFORM "calculate_ranks"("issue_id_p"); 2.1735 + END IF; 2.1736 + END IF; 2.1737 + RETURN; 2.1738 + END; 2.1739 + $$; 2.1740 + 2.1741 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 2.1742 + RETURNS VOID 2.1743 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1744 + DECLARE 2.1745 + "issue_row" "issue"%ROWTYPE; 2.1746 + BEGIN 2.1747 + SELECT * INTO "issue_row" 2.1748 + FROM "issue" WHERE "id" = "issue_id_p" 2.1749 + FOR UPDATE; 2.1750 + IF "issue_row"."cleaned" ISNULL THEN 2.1751 + UPDATE "issue" SET 2.1752 + "closed" = NULL, 2.1753 + "ranks_available" = FALSE 2.1754 + WHERE "id" = "issue_id_p"; 2.1755 + DELETE FROM "delegating_voter" 2.1756 + WHERE "issue_id" = "issue_id_p"; 2.1757 + DELETE FROM "direct_voter" 2.1758 + WHERE "issue_id" = "issue_id_p"; 2.1759 + DELETE FROM "delegating_interest_snapshot" 2.1760 + WHERE "issue_id" = "issue_id_p"; 2.1761 + DELETE FROM "direct_interest_snapshot" 2.1762 + WHERE "issue_id" = "issue_id_p"; 2.1763 + DELETE FROM "delegating_population_snapshot" 2.1764 + WHERE "issue_id" = "issue_id_p"; 2.1765 + DELETE FROM "direct_population_snapshot" 2.1766 + WHERE "issue_id" = "issue_id_p"; 2.1767 + DELETE FROM "non_voter" 2.1768 + WHERE "issue_id" = "issue_id_p"; 2.1769 + DELETE FROM "delegation" 2.1770 + WHERE "issue_id" = "issue_id_p"; 2.1771 + DELETE FROM "supporter" 2.1772 + WHERE "issue_id" = "issue_id_p"; 2.1773 + UPDATE "issue" SET 2.1774 + "closed" = "issue_row"."closed", 2.1775 + "ranks_available" = "issue_row"."ranks_available", 2.1776 + "cleaned" = now() 2.1777 + WHERE "id" = "issue_id_p"; 2.1778 + END IF; 2.1779 + RETURN; 2.1780 + END; 2.1781 + $$; 2.1782 + 2.1783 +CREATE OR REPLACE FUNCTION "check_issue" 2.1784 + ( "issue_id_p" "issue"."id"%TYPE ) 2.1785 + RETURNS VOID 2.1786 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1787 + DECLARE 2.1788 + "issue_row" "issue"%ROWTYPE; 2.1789 + "policy_row" "policy"%ROWTYPE; 2.1790 + "voting_requested_v" BOOLEAN; 2.1791 + BEGIN 2.1792 + PERFORM "lock_issue"("issue_id_p"); 2.1793 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1794 + -- only process open issues: 2.1795 + IF "issue_row"."closed" ISNULL THEN 2.1796 + SELECT * INTO "policy_row" FROM "policy" 2.1797 + WHERE "id" = "issue_row"."policy_id"; 2.1798 + -- create a snapshot, unless issue is already fully frozen: 2.1799 + IF "issue_row"."fully_frozen" ISNULL THEN 2.1800 + PERFORM "create_snapshot"("issue_id_p"); 2.1801 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1802 + END IF; 2.1803 + -- eventually close or accept issues, which have not been accepted: 2.1804 + IF "issue_row"."accepted" ISNULL THEN 2.1805 + IF EXISTS ( 2.1806 + SELECT NULL FROM "initiative" 2.1807 + WHERE "issue_id" = "issue_id_p" 2.1808 + AND "supporter_count" > 0 2.1809 + AND "supporter_count" * "policy_row"."issue_quorum_den" 2.1810 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.1811 + ) THEN 2.1812 + -- accept issues, if supporter count is high enough 2.1813 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1814 + -- NOTE: "issue_row" used later 2.1815 + "issue_row"."state" := 'discussion'; 2.1816 + "issue_row"."accepted" := now(); 2.1817 + UPDATE "issue" SET 2.1818 + "state" = "issue_row"."state", 2.1819 + "accepted" = "issue_row"."accepted" 2.1820 + WHERE "id" = "issue_row"."id"; 2.1821 + ELSIF 2.1822 + now() >= "issue_row"."created" + "issue_row"."admission_time" 2.1823 + THEN 2.1824 + -- close issues, if admission time has expired 2.1825 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1826 + UPDATE "issue" SET 2.1827 + "state" = 'canceled_issue_not_accepted', 2.1828 + "closed" = now() 2.1829 + WHERE "id" = "issue_row"."id"; 2.1830 + END IF; 2.1831 + END IF; 2.1832 + -- eventually half freeze issues: 2.1833 + IF 2.1834 + -- NOTE: issue can't be closed at this point, if it has been accepted 2.1835 + "issue_row"."accepted" NOTNULL AND 2.1836 + "issue_row"."half_frozen" ISNULL 2.1837 + THEN 2.1838 + SELECT 2.1839 + CASE 2.1840 + WHEN "vote_now" * 2 > "issue_row"."population" THEN 2.1841 + TRUE 2.1842 + WHEN "vote_later" * 2 > "issue_row"."population" THEN 2.1843 + FALSE 2.1844 + ELSE NULL 2.1845 + END 2.1846 + INTO "voting_requested_v" 2.1847 + FROM "issue" WHERE "id" = "issue_id_p"; 2.1848 + IF 2.1849 + "voting_requested_v" OR ( 2.1850 + "voting_requested_v" ISNULL AND 2.1851 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.1852 + ) 2.1853 + THEN 2.1854 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.1855 + -- NOTE: "issue_row" used later 2.1856 + "issue_row"."state" := 'verification'; 2.1857 + "issue_row"."half_frozen" := now(); 2.1858 + UPDATE "issue" SET 2.1859 + "state" = "issue_row"."state", 2.1860 + "half_frozen" = "issue_row"."half_frozen" 2.1861 + WHERE "id" = "issue_row"."id"; 2.1862 + END IF; 2.1863 + END IF; 2.1864 + -- close issues after some time, if all initiatives have been revoked: 2.1865 + IF 2.1866 + "issue_row"."closed" ISNULL AND 2.1867 + NOT EXISTS ( 2.1868 + -- all initiatives are revoked 2.1869 + SELECT NULL FROM "initiative" 2.1870 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.1871 + ) AND ( 2.1872 + -- and issue has not been accepted yet 2.1873 + "issue_row"."accepted" ISNULL OR 2.1874 + NOT EXISTS ( 2.1875 + -- or no initiatives have been revoked lately 2.1876 + SELECT NULL FROM "initiative" 2.1877 + WHERE "issue_id" = "issue_id_p" 2.1878 + AND now() < "revoked" + "issue_row"."verification_time" 2.1879 + ) OR ( 2.1880 + -- or verification time has elapsed 2.1881 + "issue_row"."half_frozen" NOTNULL AND 2.1882 + "issue_row"."fully_frozen" ISNULL AND 2.1883 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1884 + ) 2.1885 + ) 2.1886 + THEN 2.1887 + -- NOTE: "issue_row" used later 2.1888 + IF "issue_row"."accepted" ISNULL THEN 2.1889 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.1890 + ELSIF "issue_row"."half_frozen" ISNULL THEN 2.1891 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.1892 + ELSE 2.1893 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.1894 + END IF; 2.1895 + "issue_row"."closed" := now(); 2.1896 + UPDATE "issue" SET 2.1897 + "state" = "issue_row"."state", 2.1898 + "closed" = "issue_row"."closed" 2.1899 + WHERE "id" = "issue_row"."id"; 2.1900 + END IF; 2.1901 + -- fully freeze issue after verification time: 2.1902 + IF 2.1903 + "issue_row"."half_frozen" NOTNULL AND 2.1904 + "issue_row"."fully_frozen" ISNULL AND 2.1905 + "issue_row"."closed" ISNULL AND 2.1906 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1907 + THEN 2.1908 + PERFORM "freeze_after_snapshot"("issue_id_p"); 2.1909 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.1910 + END IF; 2.1911 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1912 + -- close issue by calling close_voting(...) after voting time: 2.1913 + IF 2.1914 + "issue_row"."closed" ISNULL AND 2.1915 + "issue_row"."fully_frozen" NOTNULL AND 2.1916 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.1917 + THEN 2.1918 + PERFORM "close_voting"("issue_id_p"); 2.1919 + -- calculate ranks will not consume much time and can be done now 2.1920 + PERFORM "calculate_ranks"("issue_id_p"); 2.1921 + END IF; 2.1922 + END IF; 2.1923 + RETURN; 2.1924 + END; 2.1925 + $$; 2.1926 + 2.1927 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.1928 + RETURNS VOID 2.1929 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1930 + BEGIN 2.1931 + UPDATE "member" SET 2.1932 + "last_login" = NULL, 2.1933 + "login" = NULL, 2.1934 + "password" = NULL, 2.1935 + "notify_email" = NULL, 2.1936 + "notify_email_unconfirmed" = NULL, 2.1937 + "notify_email_secret" = NULL, 2.1938 + "notify_email_secret_expiry" = NULL, 2.1939 + "notify_email_lock_expiry" = NULL, 2.1940 + "password_reset_secret" = NULL, 2.1941 + "password_reset_secret_expiry" = NULL, 2.1942 + "organizational_unit" = NULL, 2.1943 + "internal_posts" = NULL, 2.1944 + "realname" = NULL, 2.1945 + "birthday" = NULL, 2.1946 + "address" = NULL, 2.1947 + "email" = NULL, 2.1948 + "xmpp_address" = NULL, 2.1949 + "website" = NULL, 2.1950 + "phone" = NULL, 2.1951 + "mobile_phone" = NULL, 2.1952 + "profession" = NULL, 2.1953 + "external_memberships" = NULL, 2.1954 + "external_posts" = NULL, 2.1955 + "statement" = NULL; 2.1956 + -- "text_search_data" is updated by triggers 2.1957 + DELETE FROM "invite_code"; 2.1958 + DELETE FROM "setting"; 2.1959 + DELETE FROM "setting_map"; 2.1960 + DELETE FROM "member_relation_setting"; 2.1961 + DELETE FROM "member_image"; 2.1962 + DELETE FROM "contact"; 2.1963 + DELETE FROM "ignored_member"; 2.1964 + DELETE FROM "session"; 2.1965 + DELETE FROM "area_setting"; 2.1966 + DELETE FROM "issue_setting"; 2.1967 + DELETE FROM "ignored_initiative"; 2.1968 + DELETE FROM "initiative_setting"; 2.1969 + DELETE FROM "suggestion_setting"; 2.1970 + DELETE FROM "non_voter"; 2.1971 + DELETE FROM "direct_voter" USING "issue" 2.1972 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.1973 + AND "issue"."closed" ISNULL; 2.1974 + RETURN; 2.1975 + END; 2.1976 + $$; 2.1977 + 2.1978 + 2.1979 +-- Delete old "delegation_scope" TYPE: 2.1980 + 2.1981 +DROP TYPE "delegation_scope_old"; 2.1982 + 2.1983 + 2.1984 +COMMIT; 2.1985 + 2.1986 + 2.1987 +-- Generate issue states: 2.1988 + 2.1989 +UPDATE "issue" SET "state" = 2.1990 + CASE 2.1991 + WHEN "closed" ISNULL THEN 2.1992 + CASE 2.1993 + WHEN "accepted" ISNULL THEN 2.1994 + 'admission'::"issue_state" 2.1995 + WHEN "half_frozen" ISNULL THEN 2.1996 + 'discussion'::"issue_state" 2.1997 + WHEN "fully_frozen" ISNULL THEN 2.1998 + 'verification'::"issue_state" 2.1999 + ELSE 2.2000 + 'voting'::"issue_state" 2.2001 + END 2.2002 + WHEN "fully_frozen" NOTNULL THEN 2.2003 + CASE 2.2004 + WHEN "fully_frozen" = "closed" THEN 2.2005 + 'canceled_no_initiative_admitted'::"issue_state" 2.2006 + ELSE 2.2007 + 'finished_without_winner'::"issue_state" -- NOTE: corrected later 2.2008 + END 2.2009 + WHEN "half_frozen" NOTNULL THEN 2.2010 + 'canceled_after_revocation_during_verification'::"issue_state" 2.2011 + WHEN "accepted" NOTNULL THEN 2.2012 + 'canceled_after_revocation_during_discussion'::"issue_state" 2.2013 + ELSE 2.2014 + 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later 2.2015 + END; 2.2016 + 2.2017 +ALTER TABLE "issue" ALTER "state" SET NOT NULL; 2.2018 + 2.2019 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; 2.2020 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK (( 2.2021 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 2.2022 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 2.2023 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 2.2024 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 2.2025 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 2.2026 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 2.2027 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 2.2028 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 2.2029 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) 2.2030 + ) AND ( 2.2031 + ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR 2.2032 + ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR 2.2033 + ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR 2.2034 + ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR 2.2035 + ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 2.2036 + ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 2.2037 + ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR 2.2038 + ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 2.2039 + ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR 2.2040 + ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 2.2041 + ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 2.2042 + ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) 2.2043 + )); 2.2044 + 2.2045 +UPDATE "issue" SET "state" = 'finished_with_winner' 2.2046 + FROM "initiative" 2.2047 + WHERE "issue"."id" = "initiative"."issue_id" 2.2048 + AND "issue"."state" = 'finished_without_winner' 2.2049 + AND "initiative"."agreed"; 2.2050 +UPDATE "issue" SET "state" = 'canceled_issue_not_accepted' 2.2051 + FROM "initiative" 2.2052 + WHERE "issue"."id" = "initiative"."issue_id" 2.2053 + AND "issue"."state" = 'canceled_revoked_before_accepted' 2.2054 + AND "initiative"."revoked" ISNULL; 2.2055 + 2.2056 + 2.2057 +-- Guess "revoked_by_member_id" values based on author of current draft: 2.2058 + 2.2059 +UPDATE "initiative" SET "revoked_by_member_id" = "author_id" 2.2060 + FROM "current_draft" 2.2061 + WHERE "initiative"."id" = "current_draft"."initiative_id" 2.2062 + AND "initiative"."revoked" NOTNULL; 2.2063 + 2.2064 +ALTER TABLE "initiative" ADD 2.2065 + CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 2.2066 + CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL); 2.2067 + 2.2068 + 2.2069 +-- Attach existing and unused invite codes with unit id 1: 2.2070 + 2.2071 +INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id") 2.2072 + SELECT "id" AS "invite_code_id", 1 AS "unit_id" 2.2073 + FROM "invite_code" WHERE "used" ISNULL; 2.2074 + 2.2075 + 2.2076 +-- Fill "unit_id" column with default value where neccessary: 2.2077 + 2.2078 +UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit'; 2.2079 + 2.2080 +ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope" 2.2081 + CHECK ( 2.2082 + ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR 2.2083 + ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR 2.2084 + ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );