liquid_feedback_core
changeset 116:1cec513e27ac
Fixed file name of SQL update script (v1.3.1(!) to v1.4.0_rc1)
author | jbe |
---|---|
date | Mon Mar 07 03:50:00 2011 +0100 (2011-03-07) |
parents | 30e0200f82e9 |
children | a5d39efbfe5b |
files | update/core-update.v1.3.0-v1.4.0_rc1.sql update/core-update.v1.3.1-v1.4.0_rc1.sql |
line diff
1.1 --- a/update/core-update.v1.3.0-v1.4.0_rc1.sql Mon Mar 07 03:35:25 2011 +0100 1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 1.3 @@ -1,2081 +0,0 @@ 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) );
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.3.1-v1.4.0_rc1.sql Mon Mar 07 03:50:00 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) );