liquid_feedback_core
diff update/core-update.v1.4.0_rc4-v2.0.0.sql @ 193:055d232404d6
Renamed v1.5.0 to v2.0.0
| author | jbe |
|---|---|
| date | Sat Sep 10 22:39:06 2011 +0200 (2011-09-10) |
| parents | update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql@9d811ba751c6 |
| children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.4.0_rc4-v2.0.0.sql Sat Sep 10 22:39:06 2011 +0200 1.3 @@ -0,0 +1,1330 @@ 1.4 +SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing"; 1.5 + 1.6 +BEGIN; 1.7 + 1.8 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.9 + SELECT * FROM (VALUES ('2.0.0', 2, 0, 0)) 1.10 + AS "subquery"("string", "major", "minor", "revision"); 1.11 + 1.12 +ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE; 1.13 +ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT; 1.14 +ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ; 1.15 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE; 1.16 +ALTER TABLE "member" DROP COLUMN "last_login_public"; 1.17 +ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; 1.18 +ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; 1.19 + 1.20 +COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; 1.21 +COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; 1.22 +COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes'; 1.23 +COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members'; 1.24 +COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; 1.25 +COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".'; 1.26 +COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; 1.27 + 1.28 +CREATE TYPE "application_access_level" AS ENUM 1.29 + ('member', 'full', 'pseudonymous', 'anonymous'); 1.30 + 1.31 +COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API'; 1.32 + 1.33 +CREATE TABLE "member_application" ( 1.34 + "id" SERIAL8 PRIMARY KEY, 1.35 + UNIQUE ("member_id", "name"), 1.36 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") 1.37 + ON DELETE CASCADE ON UPDATE CASCADE, 1.38 + "name" TEXT NOT NULL, 1.39 + "comment" TEXT, 1.40 + "access_level" "application_access_level" NOT NULL, 1.41 + "key" TEXT NOT NULL UNIQUE, 1.42 + "last_usage" TIMESTAMPTZ ); 1.43 + 1.44 +COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API'; 1.45 + 1.46 +CREATE TABLE "rendered_member_statement" ( 1.47 + PRIMARY KEY ("member_id", "format"), 1.48 + "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.49 + "format" TEXT, 1.50 + "content" TEXT NOT NULL ); 1.51 + 1.52 +COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)'; 1.53 + 1.54 +DROP VIEW "expired_session"; 1.55 +DROP TABLE "session"; 1.56 + 1.57 +ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; 1.58 +ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; 1.59 +ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 1.60 +ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; 1.61 +ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; 1.62 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; 1.63 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; 1.64 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 1.65 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; 1.66 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; 1.67 +ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; 1.68 +ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; 1.69 + 1.70 +UPDATE "policy" SET 1.71 + "direct_majority_num" = "majority_num", 1.72 + "direct_majority_den" = "majority_den", 1.73 + "direct_majority_strict" = "majority_strict", 1.74 + "indirect_majority_num" = "majority_num", 1.75 + "indirect_majority_den" = "majority_den", 1.76 + "indirect_majority_strict" = "majority_strict"; 1.77 + 1.78 +ALTER TABLE "policy" DROP COLUMN "majority_num"; 1.79 +ALTER TABLE "policy" DROP COLUMN "majority_den"; 1.80 +ALTER TABLE "policy" DROP COLUMN "majority_strict"; 1.81 + 1.82 +COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; 1.83 +COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; 1.84 +COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.'; 1.85 +COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; 1.86 +COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner'; 1.87 +COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner'; 1.88 +COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner'; 1.89 +COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.'; 1.90 +COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner'; 1.91 +COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner'; 1.92 +COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.'; 1.93 +COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").'; 1.94 + 1.95 +ALTER TABLE "area" DROP COLUMN "autoreject_weight"; 1.96 + 1.97 +DROP VIEW "open_issue"; 1.98 +DROP VIEW "issue_with_ranks_missing"; 1.99 + 1.100 +ALTER TABLE "issue" DROP COLUMN "vote_now"; 1.101 +ALTER TABLE "issue" DROP COLUMN "vote_later"; 1.102 +ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; 1.103 + 1.104 +CREATE VIEW "open_issue" AS 1.105 + SELECT * FROM "issue" WHERE "closed" ISNULL; 1.106 + 1.107 +COMMENT ON VIEW "open_issue" IS 'All open issues'; 1.108 + 1.109 +CREATE VIEW "issue_with_ranks_missing" AS 1.110 + SELECT * FROM "issue" 1.111 + WHERE "fully_frozen" NOTNULL 1.112 + AND "closed" NOTNULL 1.113 + AND "ranks_available" = FALSE; 1.114 + 1.115 +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; 1.116 + 1.117 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.118 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.119 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.120 + 1.121 +DROP VIEW "battle_view"; 1.122 + 1.123 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 1.124 +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; 1.125 +ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; 1.126 + 1.127 +ALTER TABLE "initiative" DROP COLUMN "agreed"; 1.128 +ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; 1.129 +ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; 1.130 +ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; 1.131 +ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; 1.132 +ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; 1.133 +ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; 1.134 +ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; 1.135 +ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; 1.136 +ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; 1.137 + 1.138 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.139 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 1.140 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 1.141 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 1.142 + "schulze_rank" ISNULL AND 1.143 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 1.144 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 1.145 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 1.146 +ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); 1.147 +ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( 1.148 + "eligible" = FALSE OR 1.149 +("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); 1.150 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); 1.151 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); 1.152 +ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); 1.153 +ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); 1.154 + 1.155 +COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; 1.156 +COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; 1.157 +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 1.158 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.159 +COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.160 +COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo'; 1.161 +COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X'; 1.162 +COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; 1.163 +COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; 1.164 +COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives'; 1.165 + 1.166 +ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; 1.167 +ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; 1.168 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 1.169 + "winning_initiative_id" != "losing_initiative_id" OR 1.170 + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 1.171 + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); 1.172 + 1.173 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 1.174 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 1.175 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 1.176 + 1.177 +ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; 1.178 +ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 1.179 +ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; 1.180 +ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; 1.181 + 1.182 +DROP TRIGGER "update_text_search_data" ON "suggestion"; 1.183 + 1.184 +CREATE TRIGGER "update_text_search_data" 1.185 + BEFORE INSERT OR UPDATE ON "suggestion" 1.186 + FOR EACH ROW EXECUTE PROCEDURE 1.187 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.188 + "name", "content"); 1.189 + 1.190 +COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.191 + 1.192 +CREATE TABLE "rendered_suggestion" ( 1.193 + PRIMARY KEY ("suggestion_id", "format"), 1.194 + "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.195 + "format" TEXT, 1.196 + "content" TEXT NOT NULL ); 1.197 + 1.198 +COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)'; 1.199 + 1.200 +DROP TABLE "invite_code_unit"; 1.201 + 1.202 +DROP VIEW "area_member_count"; 1.203 + 1.204 +ALTER TABLE "membership" DROP COLUMN "autoreject"; 1.205 + 1.206 +ALTER TABLE "interest" DROP COLUMN "autoreject"; 1.207 +ALTER TABLE "interest" DROP COLUMN "voting_requested"; 1.208 + 1.209 +ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; 1.210 +ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 1.211 + 1.212 +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.213 + 1.214 +ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; 1.215 +ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; 1.216 + 1.217 +DROP TRIGGER "default_for_draft_id" ON "supporter"; 1.218 +DROP FUNCTION "supporter_default_for_draft_id_trigger"(); 1.219 + 1.220 +CREATE FUNCTION "default_for_draft_id_trigger"() 1.221 + RETURNS TRIGGER 1.222 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.223 + BEGIN 1.224 + IF NEW."draft_id" ISNULL THEN 1.225 + SELECT "id" INTO NEW."draft_id" FROM "current_draft" 1.226 + WHERE "initiative_id" = NEW."initiative_id"; 1.227 + END IF; 1.228 + RETURN NEW; 1.229 + END; 1.230 + $$; 1.231 + 1.232 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" 1.233 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 1.234 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" 1.235 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 1.236 + 1.237 +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; 1.238 +COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; 1.239 +COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; 1.240 + 1.241 +CREATE VIEW "area_member_count" AS 1.242 + SELECT 1.243 + "area"."id" AS "area_id", 1.244 + count("member"."id") AS "direct_member_count", 1.245 + coalesce( 1.246 + sum( 1.247 + CASE WHEN "member"."id" NOTNULL THEN 1.248 + "membership_weight"("area"."id", "member"."id") 1.249 + ELSE 0 END 1.250 + ) 1.251 + ) AS "member_weight" 1.252 + FROM "area" 1.253 + LEFT JOIN "membership" 1.254 + ON "area"."id" = "membership"."area_id" 1.255 + LEFT JOIN "privilege" 1.256 + ON "privilege"."unit_id" = "area"."unit_id" 1.257 + AND "privilege"."member_id" = "membership"."member_id" 1.258 + AND "privilege"."voting_right" 1.259 + LEFT JOIN "member" 1.260 + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 1.261 + AND "member"."active" 1.262 + GROUP BY "area"."id"; 1.263 + 1.264 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; 1.265 + 1.266 +CREATE VIEW "battle_participant" AS 1.267 + SELECT "initiative"."id", "initiative"."issue_id" 1.268 + FROM "issue" JOIN "initiative" 1.269 + ON "issue"."id" = "initiative"."issue_id" 1.270 + WHERE "initiative"."admitted" 1.271 + UNION ALL 1.272 + SELECT NULL, "id" AS "issue_id" 1.273 + FROM "issue"; 1.274 + 1.275 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 1.276 + 1.277 +CREATE VIEW "battle_view" AS 1.278 + SELECT 1.279 + "issue"."id" AS "issue_id", 1.280 + "winning_initiative"."id" AS "winning_initiative_id", 1.281 + "losing_initiative"."id" AS "losing_initiative_id", 1.282 + sum( 1.283 + CASE WHEN 1.284 + coalesce("better_vote"."grade", 0) > 1.285 + coalesce("worse_vote"."grade", 0) 1.286 + THEN "direct_voter"."weight" ELSE 0 END 1.287 + ) AS "count" 1.288 + FROM "issue" 1.289 + LEFT JOIN "direct_voter" 1.290 + ON "issue"."id" = "direct_voter"."issue_id" 1.291 + JOIN "battle_participant" AS "winning_initiative" 1.292 + ON "issue"."id" = "winning_initiative"."issue_id" 1.293 + JOIN "battle_participant" AS "losing_initiative" 1.294 + ON "issue"."id" = "losing_initiative"."issue_id" 1.295 + LEFT JOIN "vote" AS "better_vote" 1.296 + ON "direct_voter"."member_id" = "better_vote"."member_id" 1.297 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.298 + LEFT JOIN "vote" AS "worse_vote" 1.299 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 1.300 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.301 + WHERE "issue"."closed" NOTNULL 1.302 + AND "issue"."cleaned" ISNULL 1.303 + AND ( 1.304 + "winning_initiative"."id" != "losing_initiative"."id" OR 1.305 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 1.306 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 1.307 + GROUP BY 1.308 + "issue"."id", 1.309 + "winning_initiative"."id", 1.310 + "losing_initiative"."id"; 1.311 + 1.312 +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table'; 1.313 + 1.314 +DROP FUNCTION "check_last_login"(); 1.315 + 1.316 +CREATE FUNCTION "check_activity"() 1.317 + RETURNS VOID 1.318 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.319 + DECLARE 1.320 + "system_setting_row" "system_setting"%ROWTYPE; 1.321 + BEGIN 1.322 + SELECT * INTO "system_setting_row" FROM "system_setting"; 1.323 + LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; 1.324 + IF "system_setting_row"."member_ttl" NOTNULL THEN 1.325 + UPDATE "member" SET "active" = FALSE 1.326 + WHERE "active" = TRUE 1.327 + AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; 1.328 + END IF; 1.329 + RETURN; 1.330 + END; 1.331 + $$; 1.332 + 1.333 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".'; 1.334 + 1.335 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.336 + RETURNS VOID 1.337 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.338 + BEGIN 1.339 + LOCK TABLE "member" IN SHARE MODE; 1.340 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.341 + LOCK TABLE "unit" IN EXCLUSIVE MODE; 1.342 + LOCK TABLE "area" IN EXCLUSIVE MODE; 1.343 + LOCK TABLE "privilege" IN SHARE MODE; 1.344 + LOCK TABLE "membership" IN SHARE MODE; 1.345 + DELETE FROM "member_count"; 1.346 + INSERT INTO "member_count" ("total_count") 1.347 + SELECT "total_count" FROM "member_count_view"; 1.348 + UPDATE "unit" SET "member_count" = "view"."member_count" 1.349 + FROM "unit_member_count" AS "view" 1.350 + WHERE "view"."unit_id" = "unit"."id"; 1.351 + UPDATE "area" SET 1.352 + "direct_member_count" = "view"."direct_member_count", 1.353 + "member_weight" = "view"."member_weight" 1.354 + FROM "area_member_count" AS "view" 1.355 + WHERE "view"."area_id" = "area"."id"; 1.356 + RETURN; 1.357 + END; 1.358 + $$; 1.359 + 1.360 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 1.361 + ( "issue_id_p" "issue"."id"%TYPE ) 1.362 + RETURNS VOID 1.363 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.364 + DECLARE 1.365 + "member_id_v" "member"."id"%TYPE; 1.366 + BEGIN 1.367 + DELETE FROM "direct_interest_snapshot" 1.368 + WHERE "issue_id" = "issue_id_p" 1.369 + AND "event" = 'periodic'; 1.370 + DELETE FROM "delegating_interest_snapshot" 1.371 + WHERE "issue_id" = "issue_id_p" 1.372 + AND "event" = 'periodic'; 1.373 + DELETE FROM "direct_supporter_snapshot" 1.374 + WHERE "issue_id" = "issue_id_p" 1.375 + AND "event" = 'periodic'; 1.376 + INSERT INTO "direct_interest_snapshot" 1.377 + ("issue_id", "event", "member_id") 1.378 + SELECT 1.379 + "issue_id_p" AS "issue_id", 1.380 + 'periodic' AS "event", 1.381 + "member"."id" AS "member_id" 1.382 + FROM "issue" 1.383 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.384 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.385 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.386 + JOIN "privilege" 1.387 + ON "privilege"."unit_id" = "area"."unit_id" 1.388 + AND "privilege"."member_id" = "member"."id" 1.389 + WHERE "issue"."id" = "issue_id_p" 1.390 + AND "member"."active" AND "privilege"."voting_right"; 1.391 + FOR "member_id_v" IN 1.392 + SELECT "member_id" FROM "direct_interest_snapshot" 1.393 + WHERE "issue_id" = "issue_id_p" 1.394 + AND "event" = 'periodic' 1.395 + LOOP 1.396 + UPDATE "direct_interest_snapshot" SET 1.397 + "weight" = 1 + 1.398 + "weight_of_added_delegations_for_interest_snapshot"( 1.399 + "issue_id_p", 1.400 + "member_id_v", 1.401 + '{}' 1.402 + ) 1.403 + WHERE "issue_id" = "issue_id_p" 1.404 + AND "event" = 'periodic' 1.405 + AND "member_id" = "member_id_v"; 1.406 + END LOOP; 1.407 + INSERT INTO "direct_supporter_snapshot" 1.408 + ( "issue_id", "initiative_id", "event", "member_id", 1.409 + "informed", "satisfied" ) 1.410 + SELECT 1.411 + "issue_id_p" AS "issue_id", 1.412 + "initiative"."id" AS "initiative_id", 1.413 + 'periodic' AS "event", 1.414 + "supporter"."member_id" AS "member_id", 1.415 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.416 + NOT EXISTS ( 1.417 + SELECT NULL FROM "critical_opinion" 1.418 + WHERE "initiative_id" = "initiative"."id" 1.419 + AND "member_id" = "supporter"."member_id" 1.420 + ) AS "satisfied" 1.421 + FROM "initiative" 1.422 + JOIN "supporter" 1.423 + ON "supporter"."initiative_id" = "initiative"."id" 1.424 + JOIN "current_draft" 1.425 + ON "initiative"."id" = "current_draft"."initiative_id" 1.426 + JOIN "direct_interest_snapshot" 1.427 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.428 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.429 + AND "event" = 'periodic' 1.430 + WHERE "initiative"."issue_id" = "issue_id_p"; 1.431 + RETURN; 1.432 + END; 1.433 + $$; 1.434 + 1.435 +CREATE OR REPLACE FUNCTION "create_snapshot" 1.436 + ( "issue_id_p" "issue"."id"%TYPE ) 1.437 + RETURNS VOID 1.438 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.439 + DECLARE 1.440 + "initiative_id_v" "initiative"."id"%TYPE; 1.441 + "suggestion_id_v" "suggestion"."id"%TYPE; 1.442 + BEGIN 1.443 + PERFORM "lock_issue"("issue_id_p"); 1.444 + PERFORM "create_population_snapshot"("issue_id_p"); 1.445 + PERFORM "create_interest_snapshot"("issue_id_p"); 1.446 + UPDATE "issue" SET 1.447 + "snapshot" = now(), 1.448 + "latest_snapshot_event" = 'periodic', 1.449 + "population" = ( 1.450 + SELECT coalesce(sum("weight"), 0) 1.451 + FROM "direct_population_snapshot" 1.452 + WHERE "issue_id" = "issue_id_p" 1.453 + AND "event" = 'periodic' 1.454 + ) 1.455 + WHERE "id" = "issue_id_p"; 1.456 + FOR "initiative_id_v" IN 1.457 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.458 + LOOP 1.459 + UPDATE "initiative" SET 1.460 + "supporter_count" = ( 1.461 + SELECT coalesce(sum("di"."weight"), 0) 1.462 + FROM "direct_interest_snapshot" AS "di" 1.463 + JOIN "direct_supporter_snapshot" AS "ds" 1.464 + ON "di"."member_id" = "ds"."member_id" 1.465 + WHERE "di"."issue_id" = "issue_id_p" 1.466 + AND "di"."event" = 'periodic' 1.467 + AND "ds"."initiative_id" = "initiative_id_v" 1.468 + AND "ds"."event" = 'periodic' 1.469 + ), 1.470 + "informed_supporter_count" = ( 1.471 + SELECT coalesce(sum("di"."weight"), 0) 1.472 + FROM "direct_interest_snapshot" AS "di" 1.473 + JOIN "direct_supporter_snapshot" AS "ds" 1.474 + ON "di"."member_id" = "ds"."member_id" 1.475 + WHERE "di"."issue_id" = "issue_id_p" 1.476 + AND "di"."event" = 'periodic' 1.477 + AND "ds"."initiative_id" = "initiative_id_v" 1.478 + AND "ds"."event" = 'periodic' 1.479 + AND "ds"."informed" 1.480 + ), 1.481 + "satisfied_supporter_count" = ( 1.482 + SELECT coalesce(sum("di"."weight"), 0) 1.483 + FROM "direct_interest_snapshot" AS "di" 1.484 + JOIN "direct_supporter_snapshot" AS "ds" 1.485 + ON "di"."member_id" = "ds"."member_id" 1.486 + WHERE "di"."issue_id" = "issue_id_p" 1.487 + AND "di"."event" = 'periodic' 1.488 + AND "ds"."initiative_id" = "initiative_id_v" 1.489 + AND "ds"."event" = 'periodic' 1.490 + AND "ds"."satisfied" 1.491 + ), 1.492 + "satisfied_informed_supporter_count" = ( 1.493 + SELECT coalesce(sum("di"."weight"), 0) 1.494 + FROM "direct_interest_snapshot" AS "di" 1.495 + JOIN "direct_supporter_snapshot" AS "ds" 1.496 + ON "di"."member_id" = "ds"."member_id" 1.497 + WHERE "di"."issue_id" = "issue_id_p" 1.498 + AND "di"."event" = 'periodic' 1.499 + AND "ds"."initiative_id" = "initiative_id_v" 1.500 + AND "ds"."event" = 'periodic' 1.501 + AND "ds"."informed" 1.502 + AND "ds"."satisfied" 1.503 + ) 1.504 + WHERE "id" = "initiative_id_v"; 1.505 + FOR "suggestion_id_v" IN 1.506 + SELECT "id" FROM "suggestion" 1.507 + WHERE "initiative_id" = "initiative_id_v" 1.508 + LOOP 1.509 + UPDATE "suggestion" SET 1.510 + "minus2_unfulfilled_count" = ( 1.511 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.512 + FROM "issue" CROSS JOIN "opinion" 1.513 + JOIN "direct_interest_snapshot" AS "snapshot" 1.514 + ON "snapshot"."issue_id" = "issue"."id" 1.515 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.516 + AND "snapshot"."member_id" = "opinion"."member_id" 1.517 + WHERE "issue"."id" = "issue_id_p" 1.518 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.519 + AND "opinion"."degree" = -2 1.520 + AND "opinion"."fulfilled" = FALSE 1.521 + ), 1.522 + "minus2_fulfilled_count" = ( 1.523 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.524 + FROM "issue" CROSS JOIN "opinion" 1.525 + JOIN "direct_interest_snapshot" AS "snapshot" 1.526 + ON "snapshot"."issue_id" = "issue"."id" 1.527 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.528 + AND "snapshot"."member_id" = "opinion"."member_id" 1.529 + WHERE "issue"."id" = "issue_id_p" 1.530 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.531 + AND "opinion"."degree" = -2 1.532 + AND "opinion"."fulfilled" = TRUE 1.533 + ), 1.534 + "minus1_unfulfilled_count" = ( 1.535 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.536 + FROM "issue" CROSS JOIN "opinion" 1.537 + JOIN "direct_interest_snapshot" AS "snapshot" 1.538 + ON "snapshot"."issue_id" = "issue"."id" 1.539 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.540 + AND "snapshot"."member_id" = "opinion"."member_id" 1.541 + WHERE "issue"."id" = "issue_id_p" 1.542 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.543 + AND "opinion"."degree" = -1 1.544 + AND "opinion"."fulfilled" = FALSE 1.545 + ), 1.546 + "minus1_fulfilled_count" = ( 1.547 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.548 + FROM "issue" CROSS JOIN "opinion" 1.549 + JOIN "direct_interest_snapshot" AS "snapshot" 1.550 + ON "snapshot"."issue_id" = "issue"."id" 1.551 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.552 + AND "snapshot"."member_id" = "opinion"."member_id" 1.553 + WHERE "issue"."id" = "issue_id_p" 1.554 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.555 + AND "opinion"."degree" = -1 1.556 + AND "opinion"."fulfilled" = TRUE 1.557 + ), 1.558 + "plus1_unfulfilled_count" = ( 1.559 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.560 + FROM "issue" CROSS JOIN "opinion" 1.561 + JOIN "direct_interest_snapshot" AS "snapshot" 1.562 + ON "snapshot"."issue_id" = "issue"."id" 1.563 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.564 + AND "snapshot"."member_id" = "opinion"."member_id" 1.565 + WHERE "issue"."id" = "issue_id_p" 1.566 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.567 + AND "opinion"."degree" = 1 1.568 + AND "opinion"."fulfilled" = FALSE 1.569 + ), 1.570 + "plus1_fulfilled_count" = ( 1.571 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.572 + FROM "issue" CROSS JOIN "opinion" 1.573 + JOIN "direct_interest_snapshot" AS "snapshot" 1.574 + ON "snapshot"."issue_id" = "issue"."id" 1.575 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.576 + AND "snapshot"."member_id" = "opinion"."member_id" 1.577 + WHERE "issue"."id" = "issue_id_p" 1.578 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.579 + AND "opinion"."degree" = 1 1.580 + AND "opinion"."fulfilled" = TRUE 1.581 + ), 1.582 + "plus2_unfulfilled_count" = ( 1.583 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.584 + FROM "issue" CROSS JOIN "opinion" 1.585 + JOIN "direct_interest_snapshot" AS "snapshot" 1.586 + ON "snapshot"."issue_id" = "issue"."id" 1.587 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.588 + AND "snapshot"."member_id" = "opinion"."member_id" 1.589 + WHERE "issue"."id" = "issue_id_p" 1.590 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.591 + AND "opinion"."degree" = 2 1.592 + AND "opinion"."fulfilled" = FALSE 1.593 + ), 1.594 + "plus2_fulfilled_count" = ( 1.595 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.596 + FROM "issue" CROSS JOIN "opinion" 1.597 + JOIN "direct_interest_snapshot" AS "snapshot" 1.598 + ON "snapshot"."issue_id" = "issue"."id" 1.599 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.600 + AND "snapshot"."member_id" = "opinion"."member_id" 1.601 + WHERE "issue"."id" = "issue_id_p" 1.602 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.603 + AND "opinion"."degree" = 2 1.604 + AND "opinion"."fulfilled" = TRUE 1.605 + ) 1.606 + WHERE "suggestion"."id" = "suggestion_id_v"; 1.607 + END LOOP; 1.608 + END LOOP; 1.609 + RETURN; 1.610 + END; 1.611 + $$; 1.612 + 1.613 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.614 + RETURNS VOID 1.615 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.616 + DECLARE 1.617 + "area_id_v" "area"."id"%TYPE; 1.618 + "unit_id_v" "unit"."id"%TYPE; 1.619 + "member_id_v" "member"."id"%TYPE; 1.620 + BEGIN 1.621 + PERFORM "lock_issue"("issue_id_p"); 1.622 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.623 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.624 + -- delete delegating votes (in cases of manual reset of issue state): 1.625 + DELETE FROM "delegating_voter" 1.626 + WHERE "issue_id" = "issue_id_p"; 1.627 + -- delete votes from non-privileged voters: 1.628 + DELETE FROM "direct_voter" 1.629 + USING ( 1.630 + SELECT 1.631 + "direct_voter"."member_id" 1.632 + FROM "direct_voter" 1.633 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.634 + LEFT JOIN "privilege" 1.635 + ON "privilege"."unit_id" = "unit_id_v" 1.636 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.637 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.638 + "member"."active" = FALSE OR 1.639 + "privilege"."voting_right" ISNULL OR 1.640 + "privilege"."voting_right" = FALSE 1.641 + ) 1.642 + ) AS "subquery" 1.643 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.644 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.645 + -- consider delegations: 1.646 + UPDATE "direct_voter" SET "weight" = 1 1.647 + WHERE "issue_id" = "issue_id_p"; 1.648 + PERFORM "add_vote_delegations"("issue_id_p"); 1.649 + -- set voter count and mark issue as being calculated: 1.650 + UPDATE "issue" SET 1.651 + "state" = 'calculation', 1.652 + "closed" = now(), 1.653 + "voter_count" = ( 1.654 + SELECT coalesce(sum("weight"), 0) 1.655 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.656 + ) 1.657 + WHERE "id" = "issue_id_p"; 1.658 + -- materialize battle_view: 1.659 + -- NOTE: "closed" column of issue must be set at this point 1.660 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.661 + INSERT INTO "battle" ( 1.662 + "issue_id", 1.663 + "winning_initiative_id", "losing_initiative_id", 1.664 + "count" 1.665 + ) SELECT 1.666 + "issue_id", 1.667 + "winning_initiative_id", "losing_initiative_id", 1.668 + "count" 1.669 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.670 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.671 + UPDATE "initiative" SET 1.672 + "positive_votes" = "battle_win"."count", 1.673 + "negative_votes" = "battle_lose"."count" 1.674 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.675 + WHERE 1.676 + "battle_win"."issue_id" = "issue_id_p" AND 1.677 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.678 + "battle_win"."losing_initiative_id" ISNULL AND 1.679 + "battle_lose"."issue_id" = "issue_id_p" AND 1.680 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.681 + "battle_lose"."winning_initiative_id" ISNULL; 1.682 + END; 1.683 + $$; 1.684 + 1.685 +DROP FUNCTION "array_init_string"(INTEGER); 1.686 +DROP FUNCTION "square_matrix_init_string"(INTEGER); 1.687 + 1.688 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 1.689 + RETURNS VOID 1.690 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.691 + DECLARE 1.692 + "issue_row" "issue"%ROWTYPE; 1.693 + "policy_row" "policy"%ROWTYPE; 1.694 + "dimension_v" INTEGER; 1.695 + "vote_matrix" INT4[][]; -- absolute votes 1.696 + "matrix" INT8[][]; -- defeat strength / best paths 1.697 + "i" INTEGER; 1.698 + "j" INTEGER; 1.699 + "k" INTEGER; 1.700 + "battle_row" "battle"%ROWTYPE; 1.701 + "rank_ary" INT4[]; 1.702 + "rank_v" INT4; 1.703 + "done_v" INTEGER; 1.704 + "winners_ary" INTEGER[]; 1.705 + "initiative_id_v" "initiative"."id"%TYPE; 1.706 + BEGIN 1.707 + SELECT * INTO "issue_row" 1.708 + FROM "issue" WHERE "id" = "issue_id_p" 1.709 + FOR UPDATE; 1.710 + SELECT * INTO "policy_row" 1.711 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.712 + SELECT count(1) INTO "dimension_v" 1.713 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.714 + -- Create "vote_matrix" with absolute number of votes in pairwise 1.715 + -- comparison: 1.716 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 1.717 + "i" := 1; 1.718 + "j" := 2; 1.719 + FOR "battle_row" IN 1.720 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.721 + ORDER BY 1.722 + "winning_initiative_id" NULLS LAST, 1.723 + "losing_initiative_id" NULLS LAST 1.724 + LOOP 1.725 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.726 + IF "j" = "dimension_v" THEN 1.727 + "i" := "i" + 1; 1.728 + "j" := 1; 1.729 + ELSE 1.730 + "j" := "j" + 1; 1.731 + IF "j" = "i" THEN 1.732 + "j" := "j" + 1; 1.733 + END IF; 1.734 + END IF; 1.735 + END LOOP; 1.736 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.737 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.738 + END IF; 1.739 + -- Store defeat strengths in "matrix" using "defeat_strength" 1.740 + -- function: 1.741 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 1.742 + "i" := 1; 1.743 + LOOP 1.744 + "j" := 1; 1.745 + LOOP 1.746 + IF "i" != "j" THEN 1.747 + "matrix"["i"]["j"] := "defeat_strength"( 1.748 + "vote_matrix"["i"]["j"], 1.749 + "vote_matrix"["j"]["i"] 1.750 + ); 1.751 + END IF; 1.752 + EXIT WHEN "j" = "dimension_v"; 1.753 + "j" := "j" + 1; 1.754 + END LOOP; 1.755 + EXIT WHEN "i" = "dimension_v"; 1.756 + "i" := "i" + 1; 1.757 + END LOOP; 1.758 + -- Find best paths: 1.759 + "i" := 1; 1.760 + LOOP 1.761 + "j" := 1; 1.762 + LOOP 1.763 + IF "i" != "j" THEN 1.764 + "k" := 1; 1.765 + LOOP 1.766 + IF "i" != "k" AND "j" != "k" THEN 1.767 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 1.768 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 1.769 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 1.770 + END IF; 1.771 + ELSE 1.772 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 1.773 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 1.774 + END IF; 1.775 + END IF; 1.776 + END IF; 1.777 + EXIT WHEN "k" = "dimension_v"; 1.778 + "k" := "k" + 1; 1.779 + END LOOP; 1.780 + END IF; 1.781 + EXIT WHEN "j" = "dimension_v"; 1.782 + "j" := "j" + 1; 1.783 + END LOOP; 1.784 + EXIT WHEN "i" = "dimension_v"; 1.785 + "i" := "i" + 1; 1.786 + END LOOP; 1.787 + -- Determine order of winners: 1.788 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 1.789 + "rank_v" := 1; 1.790 + "done_v" := 0; 1.791 + LOOP 1.792 + "winners_ary" := '{}'; 1.793 + "i" := 1; 1.794 + LOOP 1.795 + IF "rank_ary"["i"] ISNULL THEN 1.796 + "j" := 1; 1.797 + LOOP 1.798 + IF 1.799 + "i" != "j" AND 1.800 + "rank_ary"["j"] ISNULL AND 1.801 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 1.802 + THEN 1.803 + -- someone else is better 1.804 + EXIT; 1.805 + END IF; 1.806 + IF "j" = "dimension_v" THEN 1.807 + -- noone is better 1.808 + "winners_ary" := "winners_ary" || "i"; 1.809 + EXIT; 1.810 + END IF; 1.811 + "j" := "j" + 1; 1.812 + END LOOP; 1.813 + END IF; 1.814 + EXIT WHEN "i" = "dimension_v"; 1.815 + "i" := "i" + 1; 1.816 + END LOOP; 1.817 + "i" := 1; 1.818 + LOOP 1.819 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 1.820 + "done_v" := "done_v" + 1; 1.821 + EXIT WHEN "i" = array_upper("winners_ary", 1); 1.822 + "i" := "i" + 1; 1.823 + END LOOP; 1.824 + EXIT WHEN "done_v" = "dimension_v"; 1.825 + "rank_v" := "rank_v" + 1; 1.826 + END LOOP; 1.827 + -- write preliminary results: 1.828 + "i" := 1; 1.829 + FOR "initiative_id_v" IN 1.830 + SELECT "id" FROM "initiative" 1.831 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.832 + ORDER BY "id" 1.833 + LOOP 1.834 + UPDATE "initiative" SET 1.835 + "direct_majority" = 1.836 + CASE WHEN "policy_row"."direct_majority_strict" THEN 1.837 + "positive_votes" * "policy_row"."direct_majority_den" > 1.838 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.839 + ELSE 1.840 + "positive_votes" * "policy_row"."direct_majority_den" >= 1.841 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.842 + END 1.843 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 1.844 + AND "issue_row"."voter_count"-"negative_votes" >= 1.845 + "policy_row"."direct_majority_non_negative", 1.846 + "indirect_majority" = 1.847 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.848 + "positive_votes" * "policy_row"."indirect_majority_den" > 1.849 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.850 + ELSE 1.851 + "positive_votes" * "policy_row"."indirect_majority_den" >= 1.852 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.853 + END 1.854 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 1.855 + AND "issue_row"."voter_count"-"negative_votes" >= 1.856 + "policy_row"."indirect_majority_non_negative", 1.857 + "schulze_rank" = "rank_ary"["i"], 1.858 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.859 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.860 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.861 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 1.862 + "winner" = FALSE 1.863 + WHERE "id" = "initiative_id_v"; 1.864 + "i" := "i" + 1; 1.865 + END LOOP; 1.866 + IF "i" != "dimension_v" THEN 1.867 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.868 + END IF; 1.869 + -- take indirect majorities into account: 1.870 + LOOP 1.871 + UPDATE "initiative" SET "indirect_majority" = TRUE 1.872 + FROM ( 1.873 + SELECT "new_initiative"."id" AS "initiative_id" 1.874 + FROM "initiative" "old_initiative" 1.875 + JOIN "initiative" "new_initiative" 1.876 + ON "new_initiative"."issue_id" = "issue_id_p" 1.877 + AND "new_initiative"."indirect_majority" = FALSE 1.878 + JOIN "battle" "battle_win" 1.879 + ON "battle_win"."issue_id" = "issue_id_p" 1.880 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.881 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.882 + JOIN "battle" "battle_lose" 1.883 + ON "battle_lose"."issue_id" = "issue_id_p" 1.884 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.885 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.886 + WHERE "old_initiative"."issue_id" = "issue_id_p" 1.887 + AND "old_initiative"."indirect_majority" = TRUE 1.888 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.889 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 1.890 + "policy_row"."indirect_majority_num" * 1.891 + ("battle_win"."count"+"battle_lose"."count") 1.892 + ELSE 1.893 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 1.894 + "policy_row"."indirect_majority_num" * 1.895 + ("battle_win"."count"+"battle_lose"."count") 1.896 + END 1.897 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 1.898 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 1.899 + "policy_row"."indirect_majority_non_negative" 1.900 + ) AS "subquery" 1.901 + WHERE "id" = "subquery"."initiative_id"; 1.902 + EXIT WHEN NOT FOUND; 1.903 + END LOOP; 1.904 + -- set "multistage_majority" for remaining matching initiatives: 1.905 + UPDATE "initiative" SET "multistage_majority" = TRUE 1.906 + FROM ( 1.907 + SELECT "losing_initiative"."id" AS "initiative_id" 1.908 + FROM "initiative" "losing_initiative" 1.909 + JOIN "initiative" "winning_initiative" 1.910 + ON "winning_initiative"."issue_id" = "issue_id_p" 1.911 + AND "winning_initiative"."admitted" 1.912 + JOIN "battle" "battle_win" 1.913 + ON "battle_win"."issue_id" = "issue_id_p" 1.914 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.915 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.916 + JOIN "battle" "battle_lose" 1.917 + ON "battle_lose"."issue_id" = "issue_id_p" 1.918 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.919 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.920 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.921 + AND "losing_initiative"."admitted" 1.922 + AND "winning_initiative"."schulze_rank" < 1.923 + "losing_initiative"."schulze_rank" 1.924 + AND "battle_win"."count" > "battle_lose"."count" 1.925 + AND ( 1.926 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.927 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.928 + ) AS "subquery" 1.929 + WHERE "id" = "subquery"."initiative_id"; 1.930 + -- mark eligible initiatives: 1.931 + UPDATE "initiative" SET "eligible" = TRUE 1.932 + WHERE "issue_id" = "issue_id_p" 1.933 + AND "initiative"."direct_majority" 1.934 + AND "initiative"."indirect_majority" 1.935 + AND "initiative"."better_than_status_quo" 1.936 + AND ( 1.937 + "policy_row"."no_multistage_majority" = FALSE OR 1.938 + "initiative"."multistage_majority" = FALSE ) 1.939 + AND ( 1.940 + "policy_row"."no_reverse_beat_path" = FALSE OR 1.941 + "initiative"."reverse_beat_path" = FALSE ); 1.942 + -- mark final winner: 1.943 + UPDATE "initiative" SET "winner" = TRUE 1.944 + FROM ( 1.945 + SELECT "id" AS "initiative_id" 1.946 + FROM "initiative" 1.947 + WHERE "issue_id" = "issue_id_p" AND "eligible" 1.948 + ORDER BY "schulze_rank", "id" 1.949 + LIMIT 1 1.950 + ) AS "subquery" 1.951 + WHERE "id" = "subquery"."initiative_id"; 1.952 + -- write (final) ranks: 1.953 + "rank_v" := 1; 1.954 + FOR "initiative_id_v" IN 1.955 + SELECT "id" 1.956 + FROM "initiative" 1.957 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.958 + ORDER BY 1.959 + "winner" DESC, 1.960 + ("direct_majority" AND "indirect_majority") DESC, 1.961 + "schulze_rank", 1.962 + "id" 1.963 + LOOP 1.964 + UPDATE "initiative" SET "rank" = "rank_v" 1.965 + WHERE "id" = "initiative_id_v"; 1.966 + "rank_v" := "rank_v" + 1; 1.967 + END LOOP; 1.968 + -- set schulze rank of status quo and mark issue as finished: 1.969 + UPDATE "issue" SET 1.970 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 1.971 + "state" = 1.972 + CASE WHEN EXISTS ( 1.973 + SELECT NULL FROM "initiative" 1.974 + WHERE "issue_id" = "issue_id_p" AND "winner" 1.975 + ) THEN 1.976 + 'finished_with_winner'::"issue_state" 1.977 + ELSE 1.978 + 'finished_without_winner'::"issue_state" 1.979 + END, 1.980 + "ranks_available" = TRUE 1.981 + WHERE "id" = "issue_id_p"; 1.982 + RETURN; 1.983 + END; 1.984 + $$; 1.985 + 1.986 +CREATE OR REPLACE FUNCTION "check_issue" 1.987 + ( "issue_id_p" "issue"."id"%TYPE ) 1.988 + RETURNS VOID 1.989 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.990 + DECLARE 1.991 + "issue_row" "issue"%ROWTYPE; 1.992 + "policy_row" "policy"%ROWTYPE; 1.993 + BEGIN 1.994 + PERFORM "lock_issue"("issue_id_p"); 1.995 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.996 + -- only process open issues: 1.997 + IF "issue_row"."closed" ISNULL THEN 1.998 + SELECT * INTO "policy_row" FROM "policy" 1.999 + WHERE "id" = "issue_row"."policy_id"; 1.1000 + -- create a snapshot, unless issue is already fully frozen: 1.1001 + IF "issue_row"."fully_frozen" ISNULL THEN 1.1002 + PERFORM "create_snapshot"("issue_id_p"); 1.1003 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1004 + END IF; 1.1005 + -- eventually close or accept issues, which have not been accepted: 1.1006 + IF "issue_row"."accepted" ISNULL THEN 1.1007 + IF EXISTS ( 1.1008 + SELECT NULL FROM "initiative" 1.1009 + WHERE "issue_id" = "issue_id_p" 1.1010 + AND "supporter_count" > 0 1.1011 + AND "supporter_count" * "policy_row"."issue_quorum_den" 1.1012 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.1013 + ) THEN 1.1014 + -- accept issues, if supporter count is high enough 1.1015 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1016 + -- NOTE: "issue_row" used later 1.1017 + "issue_row"."state" := 'discussion'; 1.1018 + "issue_row"."accepted" := now(); 1.1019 + UPDATE "issue" SET 1.1020 + "state" = "issue_row"."state", 1.1021 + "accepted" = "issue_row"."accepted" 1.1022 + WHERE "id" = "issue_row"."id"; 1.1023 + ELSIF 1.1024 + now() >= "issue_row"."created" + "issue_row"."admission_time" 1.1025 + THEN 1.1026 + -- close issues, if admission time has expired 1.1027 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1028 + UPDATE "issue" SET 1.1029 + "state" = 'canceled_issue_not_accepted', 1.1030 + "closed" = now() 1.1031 + WHERE "id" = "issue_row"."id"; 1.1032 + END IF; 1.1033 + END IF; 1.1034 + -- eventually half freeze issues: 1.1035 + IF 1.1036 + -- NOTE: issue can't be closed at this point, if it has been accepted 1.1037 + "issue_row"."accepted" NOTNULL AND 1.1038 + "issue_row"."half_frozen" ISNULL 1.1039 + THEN 1.1040 + IF 1.1041 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.1042 + THEN 1.1043 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.1044 + -- NOTE: "issue_row" used later 1.1045 + "issue_row"."state" := 'verification'; 1.1046 + "issue_row"."half_frozen" := now(); 1.1047 + UPDATE "issue" SET 1.1048 + "state" = "issue_row"."state", 1.1049 + "half_frozen" = "issue_row"."half_frozen" 1.1050 + WHERE "id" = "issue_row"."id"; 1.1051 + END IF; 1.1052 + END IF; 1.1053 + -- close issues after some time, if all initiatives have been revoked: 1.1054 + IF 1.1055 + "issue_row"."closed" ISNULL AND 1.1056 + NOT EXISTS ( 1.1057 + -- all initiatives are revoked 1.1058 + SELECT NULL FROM "initiative" 1.1059 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1060 + ) AND ( 1.1061 + -- and issue has not been accepted yet 1.1062 + "issue_row"."accepted" ISNULL OR 1.1063 + NOT EXISTS ( 1.1064 + -- or no initiatives have been revoked lately 1.1065 + SELECT NULL FROM "initiative" 1.1066 + WHERE "issue_id" = "issue_id_p" 1.1067 + AND now() < "revoked" + "issue_row"."verification_time" 1.1068 + ) OR ( 1.1069 + -- or verification time has elapsed 1.1070 + "issue_row"."half_frozen" NOTNULL AND 1.1071 + "issue_row"."fully_frozen" ISNULL AND 1.1072 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1073 + ) 1.1074 + ) 1.1075 + THEN 1.1076 + -- NOTE: "issue_row" used later 1.1077 + IF "issue_row"."accepted" ISNULL THEN 1.1078 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 1.1079 + ELSIF "issue_row"."half_frozen" ISNULL THEN 1.1080 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 1.1081 + ELSE 1.1082 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 1.1083 + END IF; 1.1084 + "issue_row"."closed" := now(); 1.1085 + UPDATE "issue" SET 1.1086 + "state" = "issue_row"."state", 1.1087 + "closed" = "issue_row"."closed" 1.1088 + WHERE "id" = "issue_row"."id"; 1.1089 + END IF; 1.1090 + -- fully freeze issue after verification time: 1.1091 + IF 1.1092 + "issue_row"."half_frozen" NOTNULL AND 1.1093 + "issue_row"."fully_frozen" ISNULL AND 1.1094 + "issue_row"."closed" ISNULL AND 1.1095 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.1096 + THEN 1.1097 + PERFORM "freeze_after_snapshot"("issue_id_p"); 1.1098 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 1.1099 + END IF; 1.1100 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1101 + -- close issue by calling close_voting(...) after voting time: 1.1102 + IF 1.1103 + "issue_row"."closed" ISNULL AND 1.1104 + "issue_row"."fully_frozen" NOTNULL AND 1.1105 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.1106 + THEN 1.1107 + PERFORM "close_voting"("issue_id_p"); 1.1108 + -- calculate ranks will not consume much time and can be done now 1.1109 + PERFORM "calculate_ranks"("issue_id_p"); 1.1110 + END IF; 1.1111 + END IF; 1.1112 + RETURN; 1.1113 + END; 1.1114 + $$; 1.1115 + 1.1116 +CREATE OR REPLACE FUNCTION "check_everything"() 1.1117 + RETURNS VOID 1.1118 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1119 + DECLARE 1.1120 + "issue_id_v" "issue"."id"%TYPE; 1.1121 + BEGIN 1.1122 + PERFORM "check_activity"(); 1.1123 + PERFORM "calculate_member_counts"(); 1.1124 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.1125 + PERFORM "check_issue"("issue_id_v"); 1.1126 + END LOOP; 1.1127 + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 1.1128 + PERFORM "calculate_ranks"("issue_id_v"); 1.1129 + END LOOP; 1.1130 + RETURN; 1.1131 + END; 1.1132 + $$; 1.1133 + 1.1134 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.1135 + RETURNS VOID 1.1136 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1137 + BEGIN 1.1138 + UPDATE "member" SET 1.1139 + "last_login" = NULL, 1.1140 + "login" = NULL, 1.1141 + "password" = NULL, 1.1142 + "locked" = TRUE, 1.1143 + "active" = FALSE, 1.1144 + "notify_email" = NULL, 1.1145 + "notify_email_unconfirmed" = NULL, 1.1146 + "notify_email_secret" = NULL, 1.1147 + "notify_email_secret_expiry" = NULL, 1.1148 + "notify_email_lock_expiry" = NULL, 1.1149 + "password_reset_secret" = NULL, 1.1150 + "password_reset_secret_expiry" = NULL, 1.1151 + "organizational_unit" = NULL, 1.1152 + "internal_posts" = NULL, 1.1153 + "realname" = NULL, 1.1154 + "birthday" = NULL, 1.1155 + "address" = NULL, 1.1156 + "email" = NULL, 1.1157 + "xmpp_address" = NULL, 1.1158 + "website" = NULL, 1.1159 + "phone" = NULL, 1.1160 + "mobile_phone" = NULL, 1.1161 + "profession" = NULL, 1.1162 + "external_memberships" = NULL, 1.1163 + "external_posts" = NULL, 1.1164 + "statement" = NULL 1.1165 + WHERE "id" = "member_id_p"; 1.1166 + -- "text_search_data" is updated by triggers 1.1167 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.1168 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.1169 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.1170 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.1171 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.1172 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.1173 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.1174 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.1175 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.1176 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.1177 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.1178 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.1179 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.1180 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.1181 + DELETE FROM "direct_voter" USING "issue" 1.1182 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.1183 + AND "issue"."closed" ISNULL 1.1184 + AND "member_id" = "member_id_p"; 1.1185 + RETURN; 1.1186 + END; 1.1187 + $$; 1.1188 + 1.1189 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.1190 + RETURNS VOID 1.1191 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1192 + BEGIN 1.1193 + UPDATE "member" SET 1.1194 + "last_login" = NULL, 1.1195 + "login" = NULL, 1.1196 + "password" = NULL, 1.1197 + "notify_email" = NULL, 1.1198 + "notify_email_unconfirmed" = NULL, 1.1199 + "notify_email_secret" = NULL, 1.1200 + "notify_email_secret_expiry" = NULL, 1.1201 + "notify_email_lock_expiry" = NULL, 1.1202 + "password_reset_secret" = NULL, 1.1203 + "password_reset_secret_expiry" = NULL, 1.1204 + "organizational_unit" = NULL, 1.1205 + "internal_posts" = NULL, 1.1206 + "realname" = NULL, 1.1207 + "birthday" = NULL, 1.1208 + "address" = NULL, 1.1209 + "email" = NULL, 1.1210 + "xmpp_address" = NULL, 1.1211 + "website" = NULL, 1.1212 + "phone" = NULL, 1.1213 + "mobile_phone" = NULL, 1.1214 + "profession" = NULL, 1.1215 + "external_memberships" = NULL, 1.1216 + "external_posts" = NULL, 1.1217 + "statement" = NULL; 1.1218 + -- "text_search_data" is updated by triggers 1.1219 + DELETE FROM "invite_code"; 1.1220 + DELETE FROM "setting"; 1.1221 + DELETE FROM "setting_map"; 1.1222 + DELETE FROM "member_relation_setting"; 1.1223 + DELETE FROM "member_image"; 1.1224 + DELETE FROM "contact"; 1.1225 + DELETE FROM "ignored_member"; 1.1226 + DELETE FROM "area_setting"; 1.1227 + DELETE FROM "issue_setting"; 1.1228 + DELETE FROM "ignored_initiative"; 1.1229 + DELETE FROM "initiative_setting"; 1.1230 + DELETE FROM "suggestion_setting"; 1.1231 + DELETE FROM "non_voter"; 1.1232 + DELETE FROM "direct_voter" USING "issue" 1.1233 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.1234 + AND "issue"."closed" ISNULL; 1.1235 + RETURN; 1.1236 + END; 1.1237 + $$; 1.1238 + 1.1239 +COMMIT; 1.1240 + 1.1241 +BEGIN; 1.1242 + 1.1243 +UPDATE "member" SET 1.1244 + "activated" = "created", 1.1245 + "last_activity" = CASE WHEN "active" THEN 1.1246 + coalesce("last_login"::DATE, now()) 1.1247 + ELSE 1.1248 + "last_login"::DATE 1.1249 + END; 1.1250 + 1.1251 +UPDATE "member" SET 1.1252 + "created" = "invite_code"."created", 1.1253 + "invite_code" = "invite_code"."code", 1.1254 + "admin_comment" = "invite_code"."comment" 1.1255 + FROM "invite_code" 1.1256 + WHERE "member"."id" = "invite_code"."member_id"; 1.1257 + 1.1258 +DROP TABLE "invite_code"; 1.1259 + 1.1260 +UPDATE "initiative" SET 1.1261 + "direct_majority" = "rank" NOTNULL, 1.1262 + "indirect_majority" = "rank" NOTNULL, 1.1263 + "schulze_rank" = "rank", 1.1264 + "better_than_status_quo" = "rank" NOTNULL, 1.1265 + "worse_than_status_quo" = "rank" ISNULL, 1.1266 + "reverse_beat_path" = "rank" ISNULL, 1.1267 + "multistage_majority" = "rank" ISNULL, 1.1268 + "eligible" = "rank" NOTNULL, 1.1269 + "winner" = ("rank" = 1) 1.1270 + FROM "issue" 1.1271 + WHERE "issue"."id" = "initiative"."issue_id" 1.1272 + AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 1.1273 + AND "initiative"."admitted"; 1.1274 + 1.1275 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" 1.1276 + FROM ( 1.1277 + SELECT 1.1278 + "issue"."id" AS "issue_id", 1.1279 + COALESCE(max("initiative"."rank") + 1, 1) AS "rank" 1.1280 + FROM "issue" JOIN "initiative" 1.1281 + ON "issue"."id" = "initiative"."issue_id" 1.1282 + WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 1.1283 + AND "initiative"."admitted" 1.1284 + GROUP BY "issue"."id" 1.1285 + ) AS "subquery" 1.1286 + WHERE "issue"."id" = "subquery"."issue_id"; 1.1287 + 1.1288 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) 1.1289 + RETURNS VOID 1.1290 + LANGUAGE 'plpgsql' AS $$ 1.1291 + DECLARE 1.1292 + "rank_v" INT4; 1.1293 + "initiative_id_v" INT4; 1.1294 + BEGIN 1.1295 + SELECT "status_quo_schulze_rank" INTO "rank_v" 1.1296 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1297 + FOR "initiative_id_v" IN 1.1298 + SELECT "id" FROM "initiative" 1.1299 + WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL 1.1300 + ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC 1.1301 + LOOP 1.1302 + UPDATE "initiative" SET 1.1303 + "schulze_rank" = "rank_v" + 1, 1.1304 + "rank" = "rank_v" 1.1305 + WHERE "id" = "initiative_id_v"; 1.1306 + "rank_v" := "rank_v" + 1; 1.1307 + END LOOP; 1.1308 + RETURN; 1.1309 + END; 1.1310 + $$; 1.1311 + 1.1312 +SELECT "update__set_remaining_ranks"("id") FROM "issue" 1.1313 + WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); 1.1314 + 1.1315 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); 1.1316 + 1.1317 +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" 1.1318 + FROM ( 1.1319 + SELECT DISTINCT ON ("suggestion"."id") 1.1320 + "suggestion"."id" AS "suggestion_id", 1.1321 + "draft"."id" AS "draft_id" 1.1322 + FROM "suggestion" JOIN "draft" 1.1323 + ON "suggestion"."initiative_id" = "draft"."initiative_id" 1.1324 + WHERE "draft"."created" <= "suggestion"."created" 1.1325 + ORDER BY "suggestion"."id", "draft"."created" DESC 1.1326 + ) AS "subquery" 1.1327 + WHERE "suggestion"."id" = "subquery"."suggestion_id"; 1.1328 + 1.1329 +COMMIT; 1.1330 + 1.1331 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" 1.1332 + CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); 1.1333 +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;