liquid_feedback_core
changeset 188:8b496fa85a65
Renamed core-update.v1.4.0-v1.5.0.sql to core-update.v1.4.0_rc4-v1.5.0_rc1.sql; Set version info to v1.5.0_rc1
author | jbe |
---|---|
date | Sat Jul 30 01:57:27 2011 +0200 (2011-07-30) |
parents | aaf5f62b1aa2 |
children | 548152fa67e5 |
files | core.sql update/core-update.v1.4.0-v1.5.0.sql update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql |
line diff
1.1 --- a/core.sql Sat Jul 30 01:54:59 2011 +0200 1.2 +++ b/core.sql Sat Jul 30 01:57:27 2011 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1)) 1.8 + SELECT * FROM (VALUES ('1.5.0_rc1', 1, 5, -1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- a/update/core-update.v1.4.0-v1.5.0.sql Sat Jul 30 01:54:59 2011 +0200 2.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 2.3 @@ -1,1305 +0,0 @@ 2.4 -SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing"; 2.5 - 2.6 -BEGIN; 2.7 - 2.8 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.9 - SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1)) 2.10 - AS "subquery"("string", "major", "minor", "revision"); 2.11 - 2.12 -ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE; 2.13 -ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT; 2.14 -ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ; 2.15 -ALTER TABLE "member" ADD COLUMN "last_activity" DATE; 2.16 -ALTER TABLE "member" DROP COLUMN "last_login_public"; 2.17 -ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; 2.18 -ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; 2.19 - 2.20 -COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; 2.21 -COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; 2.22 -COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes'; 2.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'; 2.24 -COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; 2.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".'; 2.26 -COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; 2.27 - 2.28 -CREATE TYPE "application_access_level" AS ENUM 2.29 - ('member', 'full', 'pseudonymous', 'anonymous'); 2.30 - 2.31 -COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API'; 2.32 - 2.33 -CREATE TABLE "member_application" ( 2.34 - "id" SERIAL8 PRIMARY KEY, 2.35 - UNIQUE ("member_id", "name"), 2.36 - "member_id" INT4 NOT NULL REFERENCES "member" ("id") 2.37 - ON DELETE CASCADE ON UPDATE CASCADE, 2.38 - "name" TEXT NOT NULL, 2.39 - "comment" TEXT, 2.40 - "access_level" "application_access_level" NOT NULL, 2.41 - "key" TEXT NOT NULL, 2.42 - "last_usage" TIMESTAMPTZ ); 2.43 - 2.44 -COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API'; 2.45 - 2.46 -CREATE TABLE "rendered_member_statement" ( 2.47 - PRIMARY KEY ("member_id", "format"), 2.48 - "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.49 - "format" TEXT, 2.50 - "content" TEXT NOT NULL ); 2.51 - 2.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)'; 2.53 - 2.54 -DROP VIEW "expired_session"; 2.55 -DROP TABLE "session"; 2.56 - 2.57 -ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; 2.58 -ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; 2.59 -ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 2.60 -ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; 2.61 -ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; 2.62 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; 2.63 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; 2.64 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 2.65 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; 2.66 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; 2.67 -ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; 2.68 -ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; 2.69 - 2.70 -UPDATE "policy" SET 2.71 - "direct_majority_num" = "majority_num", 2.72 - "direct_majority_den" = "majority_den", 2.73 - "direct_majority_strict" = "majority_strict", 2.74 - "indirect_majority_num" = "majority_num", 2.75 - "indirect_majority_den" = "majority_den", 2.76 - "indirect_majority_strict" = "majority_strict"; 2.77 - 2.78 -ALTER TABLE "policy" DROP COLUMN "majority_num"; 2.79 -ALTER TABLE "policy" DROP COLUMN "majority_den"; 2.80 -ALTER TABLE "policy" DROP COLUMN "majority_strict"; 2.81 - 2.82 -COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; 2.83 -COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; 2.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.'; 2.85 -COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; 2.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'; 2.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'; 2.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'; 2.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.'; 2.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'; 2.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'; 2.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.'; 2.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").'; 2.94 - 2.95 -ALTER TABLE "area" DROP COLUMN "autoreject_weight"; 2.96 - 2.97 -DROP VIEW "open_issue"; 2.98 -DROP VIEW "issue_with_ranks_missing"; 2.99 - 2.100 -ALTER TABLE "issue" DROP COLUMN "vote_now"; 2.101 -ALTER TABLE "issue" DROP COLUMN "vote_later"; 2.102 -ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; 2.103 - 2.104 -CREATE VIEW "open_issue" AS 2.105 - SELECT * FROM "issue" WHERE "closed" ISNULL; 2.106 - 2.107 -COMMENT ON VIEW "open_issue" IS 'All open issues'; 2.108 - 2.109 -CREATE VIEW "issue_with_ranks_missing" AS 2.110 - SELECT * FROM "issue" 2.111 - WHERE "fully_frozen" NOTNULL 2.112 - AND "closed" NOTNULL 2.113 - AND "ranks_available" = FALSE; 2.114 - 2.115 -COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; 2.116 - 2.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.'; 2.118 -COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 2.119 -COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 2.120 - 2.121 -DROP VIEW "battle_view"; 2.122 - 2.123 -ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 2.124 -ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; 2.125 -ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; 2.126 - 2.127 -ALTER TABLE "initiative" DROP COLUMN "agreed"; 2.128 -ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; 2.129 -ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; 2.130 -ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; 2.131 -ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; 2.132 -ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; 2.133 -ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; 2.134 -ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; 2.135 -ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; 2.136 -ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; 2.137 - 2.138 -ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 2.139 - ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 2.140 - ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 2.141 - "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 2.142 - "schulze_rank" ISNULL AND 2.143 - "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 2.144 - "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 2.145 - "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 2.146 -ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); 2.147 -ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( 2.148 - "eligible" = FALSE OR 2.149 -("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); 2.150 -ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); 2.151 -ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); 2.152 -ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); 2.153 -ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); 2.154 - 2.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"'; 2.156 -COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; 2.157 -COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 2.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)'; 2.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)'; 2.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'; 2.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'; 2.162 -COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; 2.163 -COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; 2.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'; 2.165 - 2.166 -ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; 2.167 -ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; 2.168 -ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 2.169 - "winning_initiative_id" != "losing_initiative_id" OR 2.170 - ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 2.171 - ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); 2.172 - 2.173 -CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 2.174 -CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 2.175 -CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 2.176 - 2.177 -ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; 2.178 -ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 2.179 -ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; 2.180 -ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; 2.181 - 2.182 -DROP TRIGGER "update_text_search_data" ON "suggestion"; 2.183 - 2.184 -CREATE TRIGGER "update_text_search_data" 2.185 - BEFORE INSERT OR UPDATE ON "suggestion" 2.186 - FOR EACH ROW EXECUTE PROCEDURE 2.187 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 2.188 - "name", "content"); 2.189 - 2.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")'; 2.191 - 2.192 -CREATE TABLE "rendered_suggestion" ( 2.193 - PRIMARY KEY ("suggestion_id", "format"), 2.194 - "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.195 - "format" TEXT, 2.196 - "content" TEXT NOT NULL ); 2.197 - 2.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)'; 2.199 - 2.200 -DROP TABLE "invite_code_unit"; 2.201 - 2.202 -DROP VIEW "area_member_count"; 2.203 - 2.204 -ALTER TABLE "membership" DROP COLUMN "autoreject"; 2.205 - 2.206 -ALTER TABLE "interest" DROP COLUMN "autoreject"; 2.207 -ALTER TABLE "interest" DROP COLUMN "voting_requested"; 2.208 - 2.209 -ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; 2.210 -ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 2.211 - 2.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")'; 2.213 - 2.214 -ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; 2.215 -ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; 2.216 - 2.217 -DROP TRIGGER "default_for_draft_id" ON "supporter"; 2.218 -DROP FUNCTION "supporter_default_for_draft_id_trigger"(); 2.219 - 2.220 -CREATE FUNCTION "default_for_draft_id_trigger"() 2.221 - RETURNS TRIGGER 2.222 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.223 - BEGIN 2.224 - IF NEW."draft_id" ISNULL THEN 2.225 - SELECT "id" INTO NEW."draft_id" FROM "current_draft" 2.226 - WHERE "initiative_id" = NEW."initiative_id"; 2.227 - END IF; 2.228 - RETURN NEW; 2.229 - END; 2.230 - $$; 2.231 - 2.232 -CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" 2.233 - FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 2.234 -CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" 2.235 - FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 2.236 - 2.237 -COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; 2.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'; 2.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'; 2.240 - 2.241 -CREATE VIEW "area_member_count" AS 2.242 - SELECT 2.243 - "area"."id" AS "area_id", 2.244 - count("member"."id") AS "direct_member_count", 2.245 - coalesce( 2.246 - sum( 2.247 - CASE WHEN "member"."id" NOTNULL THEN 2.248 - "membership_weight"("area"."id", "member"."id") 2.249 - ELSE 0 END 2.250 - ) 2.251 - ) AS "member_weight" 2.252 - FROM "area" 2.253 - LEFT JOIN "membership" 2.254 - ON "area"."id" = "membership"."area_id" 2.255 - LEFT JOIN "privilege" 2.256 - ON "privilege"."unit_id" = "area"."unit_id" 2.257 - AND "privilege"."member_id" = "membership"."member_id" 2.258 - AND "privilege"."voting_right" 2.259 - LEFT JOIN "member" 2.260 - ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 2.261 - AND "member"."active" 2.262 - GROUP BY "area"."id"; 2.263 - 2.264 -COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; 2.265 - 2.266 -CREATE VIEW "battle_participant" AS 2.267 - SELECT "initiative"."id", "initiative"."issue_id" 2.268 - FROM "issue" JOIN "initiative" 2.269 - ON "issue"."id" = "initiative"."issue_id" 2.270 - WHERE "initiative"."admitted" 2.271 - UNION ALL 2.272 - SELECT NULL, "id" AS "issue_id" 2.273 - FROM "issue"; 2.274 - 2.275 -COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 2.276 - 2.277 -CREATE VIEW "battle_view" AS 2.278 - SELECT 2.279 - "issue"."id" AS "issue_id", 2.280 - "winning_initiative"."id" AS "winning_initiative_id", 2.281 - "losing_initiative"."id" AS "losing_initiative_id", 2.282 - sum( 2.283 - CASE WHEN 2.284 - coalesce("better_vote"."grade", 0) > 2.285 - coalesce("worse_vote"."grade", 0) 2.286 - THEN "direct_voter"."weight" ELSE 0 END 2.287 - ) AS "count" 2.288 - FROM "issue" 2.289 - LEFT JOIN "direct_voter" 2.290 - ON "issue"."id" = "direct_voter"."issue_id" 2.291 - JOIN "battle_participant" AS "winning_initiative" 2.292 - ON "issue"."id" = "winning_initiative"."issue_id" 2.293 - JOIN "battle_participant" AS "losing_initiative" 2.294 - ON "issue"."id" = "losing_initiative"."issue_id" 2.295 - LEFT JOIN "vote" AS "better_vote" 2.296 - ON "direct_voter"."member_id" = "better_vote"."member_id" 2.297 - AND "winning_initiative"."id" = "better_vote"."initiative_id" 2.298 - LEFT JOIN "vote" AS "worse_vote" 2.299 - ON "direct_voter"."member_id" = "worse_vote"."member_id" 2.300 - AND "losing_initiative"."id" = "worse_vote"."initiative_id" 2.301 - WHERE "issue"."closed" NOTNULL 2.302 - AND "issue"."cleaned" ISNULL 2.303 - AND ( 2.304 - "winning_initiative"."id" != "losing_initiative"."id" OR 2.305 - ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 2.306 - ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 2.307 - GROUP BY 2.308 - "issue"."id", 2.309 - "winning_initiative"."id", 2.310 - "losing_initiative"."id"; 2.311 - 2.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'; 2.313 - 2.314 -DROP FUNCTION "check_last_login"(); 2.315 - 2.316 -CREATE FUNCTION "check_activity"() 2.317 - RETURNS VOID 2.318 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.319 - DECLARE 2.320 - "system_setting_row" "system_setting"%ROWTYPE; 2.321 - BEGIN 2.322 - SELECT * INTO "system_setting_row" FROM "system_setting"; 2.323 - LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; 2.324 - IF "system_setting_row"."member_ttl" NOTNULL THEN 2.325 - UPDATE "member" SET "active" = FALSE 2.326 - WHERE "active" = TRUE 2.327 - AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; 2.328 - END IF; 2.329 - RETURN; 2.330 - END; 2.331 - $$; 2.332 - 2.333 -COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".'; 2.334 - 2.335 -CREATE OR REPLACE FUNCTION "create_interest_snapshot" 2.336 - ( "issue_id_p" "issue"."id"%TYPE ) 2.337 - RETURNS VOID 2.338 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.339 - DECLARE 2.340 - "member_id_v" "member"."id"%TYPE; 2.341 - BEGIN 2.342 - DELETE FROM "direct_interest_snapshot" 2.343 - WHERE "issue_id" = "issue_id_p" 2.344 - AND "event" = 'periodic'; 2.345 - DELETE FROM "delegating_interest_snapshot" 2.346 - WHERE "issue_id" = "issue_id_p" 2.347 - AND "event" = 'periodic'; 2.348 - DELETE FROM "direct_supporter_snapshot" 2.349 - WHERE "issue_id" = "issue_id_p" 2.350 - AND "event" = 'periodic'; 2.351 - INSERT INTO "direct_interest_snapshot" 2.352 - ("issue_id", "event", "member_id") 2.353 - SELECT 2.354 - "issue_id_p" AS "issue_id", 2.355 - 'periodic' AS "event", 2.356 - "member"."id" AS "member_id" 2.357 - FROM "issue" 2.358 - JOIN "area" ON "issue"."area_id" = "area"."id" 2.359 - JOIN "interest" ON "issue"."id" = "interest"."issue_id" 2.360 - JOIN "member" ON "interest"."member_id" = "member"."id" 2.361 - JOIN "privilege" 2.362 - ON "privilege"."unit_id" = "area"."unit_id" 2.363 - AND "privilege"."member_id" = "member"."id" 2.364 - WHERE "issue"."id" = "issue_id_p" 2.365 - AND "member"."active" AND "privilege"."voting_right"; 2.366 - FOR "member_id_v" IN 2.367 - SELECT "member_id" FROM "direct_interest_snapshot" 2.368 - WHERE "issue_id" = "issue_id_p" 2.369 - AND "event" = 'periodic' 2.370 - LOOP 2.371 - UPDATE "direct_interest_snapshot" SET 2.372 - "weight" = 1 + 2.373 - "weight_of_added_delegations_for_interest_snapshot"( 2.374 - "issue_id_p", 2.375 - "member_id_v", 2.376 - '{}' 2.377 - ) 2.378 - WHERE "issue_id" = "issue_id_p" 2.379 - AND "event" = 'periodic' 2.380 - AND "member_id" = "member_id_v"; 2.381 - END LOOP; 2.382 - INSERT INTO "direct_supporter_snapshot" 2.383 - ( "issue_id", "initiative_id", "event", "member_id", 2.384 - "informed", "satisfied" ) 2.385 - SELECT 2.386 - "issue_id_p" AS "issue_id", 2.387 - "initiative"."id" AS "initiative_id", 2.388 - 'periodic' AS "event", 2.389 - "supporter"."member_id" AS "member_id", 2.390 - "supporter"."draft_id" = "current_draft"."id" AS "informed", 2.391 - NOT EXISTS ( 2.392 - SELECT NULL FROM "critical_opinion" 2.393 - WHERE "initiative_id" = "initiative"."id" 2.394 - AND "member_id" = "supporter"."member_id" 2.395 - ) AS "satisfied" 2.396 - FROM "initiative" 2.397 - JOIN "supporter" 2.398 - ON "supporter"."initiative_id" = "initiative"."id" 2.399 - JOIN "current_draft" 2.400 - ON "initiative"."id" = "current_draft"."initiative_id" 2.401 - JOIN "direct_interest_snapshot" 2.402 - ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 2.403 - AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 2.404 - AND "event" = 'periodic' 2.405 - WHERE "initiative"."issue_id" = "issue_id_p"; 2.406 - RETURN; 2.407 - END; 2.408 - $$; 2.409 - 2.410 -CREATE OR REPLACE FUNCTION "create_snapshot" 2.411 - ( "issue_id_p" "issue"."id"%TYPE ) 2.412 - RETURNS VOID 2.413 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.414 - DECLARE 2.415 - "initiative_id_v" "initiative"."id"%TYPE; 2.416 - "suggestion_id_v" "suggestion"."id"%TYPE; 2.417 - BEGIN 2.418 - PERFORM "lock_issue"("issue_id_p"); 2.419 - PERFORM "create_population_snapshot"("issue_id_p"); 2.420 - PERFORM "create_interest_snapshot"("issue_id_p"); 2.421 - UPDATE "issue" SET 2.422 - "snapshot" = now(), 2.423 - "latest_snapshot_event" = 'periodic', 2.424 - "population" = ( 2.425 - SELECT coalesce(sum("weight"), 0) 2.426 - FROM "direct_population_snapshot" 2.427 - WHERE "issue_id" = "issue_id_p" 2.428 - AND "event" = 'periodic' 2.429 - ) 2.430 - WHERE "id" = "issue_id_p"; 2.431 - FOR "initiative_id_v" IN 2.432 - SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 2.433 - LOOP 2.434 - UPDATE "initiative" SET 2.435 - "supporter_count" = ( 2.436 - SELECT coalesce(sum("di"."weight"), 0) 2.437 - FROM "direct_interest_snapshot" AS "di" 2.438 - JOIN "direct_supporter_snapshot" AS "ds" 2.439 - ON "di"."member_id" = "ds"."member_id" 2.440 - WHERE "di"."issue_id" = "issue_id_p" 2.441 - AND "di"."event" = 'periodic' 2.442 - AND "ds"."initiative_id" = "initiative_id_v" 2.443 - AND "ds"."event" = 'periodic' 2.444 - ), 2.445 - "informed_supporter_count" = ( 2.446 - SELECT coalesce(sum("di"."weight"), 0) 2.447 - FROM "direct_interest_snapshot" AS "di" 2.448 - JOIN "direct_supporter_snapshot" AS "ds" 2.449 - ON "di"."member_id" = "ds"."member_id" 2.450 - WHERE "di"."issue_id" = "issue_id_p" 2.451 - AND "di"."event" = 'periodic' 2.452 - AND "ds"."initiative_id" = "initiative_id_v" 2.453 - AND "ds"."event" = 'periodic' 2.454 - AND "ds"."informed" 2.455 - ), 2.456 - "satisfied_supporter_count" = ( 2.457 - SELECT coalesce(sum("di"."weight"), 0) 2.458 - FROM "direct_interest_snapshot" AS "di" 2.459 - JOIN "direct_supporter_snapshot" AS "ds" 2.460 - ON "di"."member_id" = "ds"."member_id" 2.461 - WHERE "di"."issue_id" = "issue_id_p" 2.462 - AND "di"."event" = 'periodic' 2.463 - AND "ds"."initiative_id" = "initiative_id_v" 2.464 - AND "ds"."event" = 'periodic' 2.465 - AND "ds"."satisfied" 2.466 - ), 2.467 - "satisfied_informed_supporter_count" = ( 2.468 - SELECT coalesce(sum("di"."weight"), 0) 2.469 - FROM "direct_interest_snapshot" AS "di" 2.470 - JOIN "direct_supporter_snapshot" AS "ds" 2.471 - ON "di"."member_id" = "ds"."member_id" 2.472 - WHERE "di"."issue_id" = "issue_id_p" 2.473 - AND "di"."event" = 'periodic' 2.474 - AND "ds"."initiative_id" = "initiative_id_v" 2.475 - AND "ds"."event" = 'periodic' 2.476 - AND "ds"."informed" 2.477 - AND "ds"."satisfied" 2.478 - ) 2.479 - WHERE "id" = "initiative_id_v"; 2.480 - FOR "suggestion_id_v" IN 2.481 - SELECT "id" FROM "suggestion" 2.482 - WHERE "initiative_id" = "initiative_id_v" 2.483 - LOOP 2.484 - UPDATE "suggestion" SET 2.485 - "minus2_unfulfilled_count" = ( 2.486 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.487 - FROM "issue" CROSS JOIN "opinion" 2.488 - JOIN "direct_interest_snapshot" AS "snapshot" 2.489 - ON "snapshot"."issue_id" = "issue"."id" 2.490 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.491 - AND "snapshot"."member_id" = "opinion"."member_id" 2.492 - WHERE "issue"."id" = "issue_id_p" 2.493 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.494 - AND "opinion"."degree" = -2 2.495 - AND "opinion"."fulfilled" = FALSE 2.496 - ), 2.497 - "minus2_fulfilled_count" = ( 2.498 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.499 - FROM "issue" CROSS JOIN "opinion" 2.500 - JOIN "direct_interest_snapshot" AS "snapshot" 2.501 - ON "snapshot"."issue_id" = "issue"."id" 2.502 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.503 - AND "snapshot"."member_id" = "opinion"."member_id" 2.504 - WHERE "issue"."id" = "issue_id_p" 2.505 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.506 - AND "opinion"."degree" = -2 2.507 - AND "opinion"."fulfilled" = TRUE 2.508 - ), 2.509 - "minus1_unfulfilled_count" = ( 2.510 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.511 - FROM "issue" CROSS JOIN "opinion" 2.512 - JOIN "direct_interest_snapshot" AS "snapshot" 2.513 - ON "snapshot"."issue_id" = "issue"."id" 2.514 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.515 - AND "snapshot"."member_id" = "opinion"."member_id" 2.516 - WHERE "issue"."id" = "issue_id_p" 2.517 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.518 - AND "opinion"."degree" = -1 2.519 - AND "opinion"."fulfilled" = FALSE 2.520 - ), 2.521 - "minus1_fulfilled_count" = ( 2.522 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.523 - FROM "issue" CROSS JOIN "opinion" 2.524 - JOIN "direct_interest_snapshot" AS "snapshot" 2.525 - ON "snapshot"."issue_id" = "issue"."id" 2.526 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.527 - AND "snapshot"."member_id" = "opinion"."member_id" 2.528 - WHERE "issue"."id" = "issue_id_p" 2.529 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.530 - AND "opinion"."degree" = -1 2.531 - AND "opinion"."fulfilled" = TRUE 2.532 - ), 2.533 - "plus1_unfulfilled_count" = ( 2.534 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.535 - FROM "issue" CROSS JOIN "opinion" 2.536 - JOIN "direct_interest_snapshot" AS "snapshot" 2.537 - ON "snapshot"."issue_id" = "issue"."id" 2.538 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.539 - AND "snapshot"."member_id" = "opinion"."member_id" 2.540 - WHERE "issue"."id" = "issue_id_p" 2.541 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.542 - AND "opinion"."degree" = 1 2.543 - AND "opinion"."fulfilled" = FALSE 2.544 - ), 2.545 - "plus1_fulfilled_count" = ( 2.546 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.547 - FROM "issue" CROSS JOIN "opinion" 2.548 - JOIN "direct_interest_snapshot" AS "snapshot" 2.549 - ON "snapshot"."issue_id" = "issue"."id" 2.550 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.551 - AND "snapshot"."member_id" = "opinion"."member_id" 2.552 - WHERE "issue"."id" = "issue_id_p" 2.553 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.554 - AND "opinion"."degree" = 1 2.555 - AND "opinion"."fulfilled" = TRUE 2.556 - ), 2.557 - "plus2_unfulfilled_count" = ( 2.558 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.559 - FROM "issue" CROSS JOIN "opinion" 2.560 - JOIN "direct_interest_snapshot" AS "snapshot" 2.561 - ON "snapshot"."issue_id" = "issue"."id" 2.562 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.563 - AND "snapshot"."member_id" = "opinion"."member_id" 2.564 - WHERE "issue"."id" = "issue_id_p" 2.565 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.566 - AND "opinion"."degree" = 2 2.567 - AND "opinion"."fulfilled" = FALSE 2.568 - ), 2.569 - "plus2_fulfilled_count" = ( 2.570 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.571 - FROM "issue" CROSS JOIN "opinion" 2.572 - JOIN "direct_interest_snapshot" AS "snapshot" 2.573 - ON "snapshot"."issue_id" = "issue"."id" 2.574 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.575 - AND "snapshot"."member_id" = "opinion"."member_id" 2.576 - WHERE "issue"."id" = "issue_id_p" 2.577 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.578 - AND "opinion"."degree" = 2 2.579 - AND "opinion"."fulfilled" = TRUE 2.580 - ) 2.581 - WHERE "suggestion"."id" = "suggestion_id_v"; 2.582 - END LOOP; 2.583 - END LOOP; 2.584 - RETURN; 2.585 - END; 2.586 - $$; 2.587 - 2.588 -CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.589 - RETURNS VOID 2.590 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.591 - DECLARE 2.592 - "area_id_v" "area"."id"%TYPE; 2.593 - "unit_id_v" "unit"."id"%TYPE; 2.594 - "member_id_v" "member"."id"%TYPE; 2.595 - BEGIN 2.596 - PERFORM "lock_issue"("issue_id_p"); 2.597 - SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.598 - SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.599 - -- delete delegating votes (in cases of manual reset of issue state): 2.600 - DELETE FROM "delegating_voter" 2.601 - WHERE "issue_id" = "issue_id_p"; 2.602 - -- delete votes from non-privileged voters: 2.603 - DELETE FROM "direct_voter" 2.604 - USING ( 2.605 - SELECT 2.606 - "direct_voter"."member_id" 2.607 - FROM "direct_voter" 2.608 - JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.609 - LEFT JOIN "privilege" 2.610 - ON "privilege"."unit_id" = "unit_id_v" 2.611 - AND "privilege"."member_id" = "direct_voter"."member_id" 2.612 - WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.613 - "member"."active" = FALSE OR 2.614 - "privilege"."voting_right" ISNULL OR 2.615 - "privilege"."voting_right" = FALSE 2.616 - ) 2.617 - ) AS "subquery" 2.618 - WHERE "direct_voter"."issue_id" = "issue_id_p" 2.619 - AND "direct_voter"."member_id" = "subquery"."member_id"; 2.620 - -- consider delegations: 2.621 - UPDATE "direct_voter" SET "weight" = 1 2.622 - WHERE "issue_id" = "issue_id_p"; 2.623 - PERFORM "add_vote_delegations"("issue_id_p"); 2.624 - -- set voter count and mark issue as being calculated: 2.625 - UPDATE "issue" SET 2.626 - "state" = 'calculation', 2.627 - "closed" = now(), 2.628 - "voter_count" = ( 2.629 - SELECT coalesce(sum("weight"), 0) 2.630 - FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.631 - ) 2.632 - WHERE "id" = "issue_id_p"; 2.633 - -- materialize battle_view: 2.634 - -- NOTE: "closed" column of issue must be set at this point 2.635 - DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.636 - INSERT INTO "battle" ( 2.637 - "issue_id", 2.638 - "winning_initiative_id", "losing_initiative_id", 2.639 - "count" 2.640 - ) SELECT 2.641 - "issue_id", 2.642 - "winning_initiative_id", "losing_initiative_id", 2.643 - "count" 2.644 - FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.645 - -- copy "positive_votes" and "negative_votes" from "battle" table: 2.646 - UPDATE "initiative" SET 2.647 - "positive_votes" = "battle_win"."count", 2.648 - "negative_votes" = "battle_lose"."count" 2.649 - FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.650 - WHERE 2.651 - "battle_win"."issue_id" = "issue_id_p" AND 2.652 - "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.653 - "battle_win"."losing_initiative_id" ISNULL AND 2.654 - "battle_lose"."issue_id" = "issue_id_p" AND 2.655 - "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.656 - "battle_lose"."winning_initiative_id" ISNULL; 2.657 - END; 2.658 - $$; 2.659 - 2.660 -DROP FUNCTION "array_init_string"(INTEGER); 2.661 -DROP FUNCTION "square_matrix_init_string"(INTEGER); 2.662 - 2.663 -CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 2.664 - RETURNS VOID 2.665 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.666 - DECLARE 2.667 - "issue_row" "issue"%ROWTYPE; 2.668 - "policy_row" "policy"%ROWTYPE; 2.669 - "dimension_v" INTEGER; 2.670 - "vote_matrix" INT4[][]; -- absolute votes 2.671 - "matrix" INT8[][]; -- defeat strength / best paths 2.672 - "i" INTEGER; 2.673 - "j" INTEGER; 2.674 - "k" INTEGER; 2.675 - "battle_row" "battle"%ROWTYPE; 2.676 - "rank_ary" INT4[]; 2.677 - "rank_v" INT4; 2.678 - "done_v" INTEGER; 2.679 - "winners_ary" INTEGER[]; 2.680 - "initiative_id_v" "initiative"."id"%TYPE; 2.681 - BEGIN 2.682 - SELECT * INTO "issue_row" 2.683 - FROM "issue" WHERE "id" = "issue_id_p" 2.684 - FOR UPDATE; 2.685 - SELECT * INTO "policy_row" 2.686 - FROM "policy" WHERE "id" = "issue_row"."policy_id"; 2.687 - SELECT count(1) INTO "dimension_v" 2.688 - FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 2.689 - -- Create "vote_matrix" with absolute number of votes in pairwise 2.690 - -- comparison: 2.691 - "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 2.692 - "i" := 1; 2.693 - "j" := 2; 2.694 - FOR "battle_row" IN 2.695 - SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 2.696 - ORDER BY 2.697 - "winning_initiative_id" NULLS LAST, 2.698 - "losing_initiative_id" NULLS LAST 2.699 - LOOP 2.700 - "vote_matrix"["i"]["j"] := "battle_row"."count"; 2.701 - IF "j" = "dimension_v" THEN 2.702 - "i" := "i" + 1; 2.703 - "j" := 1; 2.704 - ELSE 2.705 - "j" := "j" + 1; 2.706 - IF "j" = "i" THEN 2.707 - "j" := "j" + 1; 2.708 - END IF; 2.709 - END IF; 2.710 - END LOOP; 2.711 - IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 2.712 - RAISE EXCEPTION 'Wrong battle count (should not happen)'; 2.713 - END IF; 2.714 - -- Store defeat strengths in "matrix" using "defeat_strength" 2.715 - -- function: 2.716 - "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 2.717 - "i" := 1; 2.718 - LOOP 2.719 - "j" := 1; 2.720 - LOOP 2.721 - IF "i" != "j" THEN 2.722 - "matrix"["i"]["j"] := "defeat_strength"( 2.723 - "vote_matrix"["i"]["j"], 2.724 - "vote_matrix"["j"]["i"] 2.725 - ); 2.726 - END IF; 2.727 - EXIT WHEN "j" = "dimension_v"; 2.728 - "j" := "j" + 1; 2.729 - END LOOP; 2.730 - EXIT WHEN "i" = "dimension_v"; 2.731 - "i" := "i" + 1; 2.732 - END LOOP; 2.733 - -- Find best paths: 2.734 - "i" := 1; 2.735 - LOOP 2.736 - "j" := 1; 2.737 - LOOP 2.738 - IF "i" != "j" THEN 2.739 - "k" := 1; 2.740 - LOOP 2.741 - IF "i" != "k" AND "j" != "k" THEN 2.742 - IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 2.743 - IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 2.744 - "matrix"["j"]["k"] := "matrix"["j"]["i"]; 2.745 - END IF; 2.746 - ELSE 2.747 - IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 2.748 - "matrix"["j"]["k"] := "matrix"["i"]["k"]; 2.749 - END IF; 2.750 - END IF; 2.751 - END IF; 2.752 - EXIT WHEN "k" = "dimension_v"; 2.753 - "k" := "k" + 1; 2.754 - END LOOP; 2.755 - END IF; 2.756 - EXIT WHEN "j" = "dimension_v"; 2.757 - "j" := "j" + 1; 2.758 - END LOOP; 2.759 - EXIT WHEN "i" = "dimension_v"; 2.760 - "i" := "i" + 1; 2.761 - END LOOP; 2.762 - -- Determine order of winners: 2.763 - "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 2.764 - "rank_v" := 1; 2.765 - "done_v" := 0; 2.766 - LOOP 2.767 - "winners_ary" := '{}'; 2.768 - "i" := 1; 2.769 - LOOP 2.770 - IF "rank_ary"["i"] ISNULL THEN 2.771 - "j" := 1; 2.772 - LOOP 2.773 - IF 2.774 - "i" != "j" AND 2.775 - "rank_ary"["j"] ISNULL AND 2.776 - "matrix"["j"]["i"] > "matrix"["i"]["j"] 2.777 - THEN 2.778 - -- someone else is better 2.779 - EXIT; 2.780 - END IF; 2.781 - IF "j" = "dimension_v" THEN 2.782 - -- noone is better 2.783 - "winners_ary" := "winners_ary" || "i"; 2.784 - EXIT; 2.785 - END IF; 2.786 - "j" := "j" + 1; 2.787 - END LOOP; 2.788 - END IF; 2.789 - EXIT WHEN "i" = "dimension_v"; 2.790 - "i" := "i" + 1; 2.791 - END LOOP; 2.792 - "i" := 1; 2.793 - LOOP 2.794 - "rank_ary"["winners_ary"["i"]] := "rank_v"; 2.795 - "done_v" := "done_v" + 1; 2.796 - EXIT WHEN "i" = array_upper("winners_ary", 1); 2.797 - "i" := "i" + 1; 2.798 - END LOOP; 2.799 - EXIT WHEN "done_v" = "dimension_v"; 2.800 - "rank_v" := "rank_v" + 1; 2.801 - END LOOP; 2.802 - -- write preliminary results: 2.803 - "i" := 1; 2.804 - FOR "initiative_id_v" IN 2.805 - SELECT "id" FROM "initiative" 2.806 - WHERE "issue_id" = "issue_id_p" AND "admitted" 2.807 - ORDER BY "id" 2.808 - LOOP 2.809 - UPDATE "initiative" SET 2.810 - "direct_majority" = 2.811 - CASE WHEN "policy_row"."direct_majority_strict" THEN 2.812 - "positive_votes" * "policy_row"."direct_majority_den" > 2.813 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.814 - ELSE 2.815 - "positive_votes" * "policy_row"."direct_majority_den" >= 2.816 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.817 - END 2.818 - AND "positive_votes" >= "policy_row"."direct_majority_positive" 2.819 - AND "issue_row"."voter_count"-"negative_votes" >= 2.820 - "policy_row"."direct_majority_non_negative", 2.821 - "indirect_majority" = 2.822 - CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.823 - "positive_votes" * "policy_row"."indirect_majority_den" > 2.824 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.825 - ELSE 2.826 - "positive_votes" * "policy_row"."indirect_majority_den" >= 2.827 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.828 - END 2.829 - AND "positive_votes" >= "policy_row"."indirect_majority_positive" 2.830 - AND "issue_row"."voter_count"-"negative_votes" >= 2.831 - "policy_row"."indirect_majority_non_negative", 2.832 - "schulze_rank" = "rank_ary"["i"], 2.833 - "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 2.834 - "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 2.835 - "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 2.836 - "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 2.837 - "winner" = FALSE 2.838 - WHERE "id" = "initiative_id_v"; 2.839 - "i" := "i" + 1; 2.840 - END LOOP; 2.841 - IF "i" != "dimension_v" THEN 2.842 - RAISE EXCEPTION 'Wrong winner count (should not happen)'; 2.843 - END IF; 2.844 - -- take indirect majorities into account: 2.845 - LOOP 2.846 - UPDATE "initiative" SET "indirect_majority" = TRUE 2.847 - FROM ( 2.848 - SELECT "new_initiative"."id" AS "initiative_id" 2.849 - FROM "initiative" "old_initiative" 2.850 - JOIN "initiative" "new_initiative" 2.851 - ON "new_initiative"."issue_id" = "issue_id_p" 2.852 - AND "new_initiative"."indirect_majority" = FALSE 2.853 - JOIN "battle" "battle_win" 2.854 - ON "battle_win"."issue_id" = "issue_id_p" 2.855 - AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 2.856 - AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 2.857 - JOIN "battle" "battle_lose" 2.858 - ON "battle_lose"."issue_id" = "issue_id_p" 2.859 - AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 2.860 - AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 2.861 - WHERE "old_initiative"."issue_id" = "issue_id_p" 2.862 - AND "old_initiative"."indirect_majority" = TRUE 2.863 - AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.864 - "battle_win"."count" * "policy_row"."indirect_majority_den" > 2.865 - "policy_row"."indirect_majority_num" * 2.866 - ("battle_win"."count"+"battle_lose"."count") 2.867 - ELSE 2.868 - "battle_win"."count" * "policy_row"."indirect_majority_den" >= 2.869 - "policy_row"."indirect_majority_num" * 2.870 - ("battle_win"."count"+"battle_lose"."count") 2.871 - END 2.872 - AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 2.873 - AND "issue_row"."voter_count"-"battle_lose"."count" >= 2.874 - "policy_row"."indirect_majority_non_negative" 2.875 - ) AS "subquery" 2.876 - WHERE "id" = "subquery"."initiative_id"; 2.877 - EXIT WHEN NOT FOUND; 2.878 - END LOOP; 2.879 - -- set "multistage_majority" for remaining matching initiatives: 2.880 - UPDATE "initiative" SET "multistage_majority" = TRUE 2.881 - FROM ( 2.882 - SELECT "losing_initiative"."id" AS "initiative_id" 2.883 - FROM "initiative" "losing_initiative" 2.884 - JOIN "initiative" "winning_initiative" 2.885 - ON "winning_initiative"."issue_id" = "issue_id_p" 2.886 - AND "winning_initiative"."admitted" 2.887 - JOIN "battle" "battle_win" 2.888 - ON "battle_win"."issue_id" = "issue_id_p" 2.889 - AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 2.890 - AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 2.891 - JOIN "battle" "battle_lose" 2.892 - ON "battle_lose"."issue_id" = "issue_id_p" 2.893 - AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 2.894 - AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 2.895 - WHERE "losing_initiative"."issue_id" = "issue_id_p" 2.896 - AND "losing_initiative"."admitted" 2.897 - AND "winning_initiative"."schulze_rank" < 2.898 - "losing_initiative"."schulze_rank" 2.899 - AND "battle_win"."count" > "battle_lose"."count" 2.900 - AND ( 2.901 - "battle_win"."count" > "winning_initiative"."positive_votes" OR 2.902 - "battle_lose"."count" < "losing_initiative"."negative_votes" ) 2.903 - ) AS "subquery" 2.904 - WHERE "id" = "subquery"."initiative_id"; 2.905 - -- mark eligible initiatives: 2.906 - UPDATE "initiative" SET "eligible" = TRUE 2.907 - WHERE "issue_id" = "issue_id_p" 2.908 - AND "initiative"."direct_majority" 2.909 - AND "initiative"."indirect_majority" 2.910 - AND "initiative"."better_than_status_quo" 2.911 - AND ( 2.912 - "policy_row"."no_multistage_majority" = FALSE OR 2.913 - "initiative"."multistage_majority" = FALSE ) 2.914 - AND ( 2.915 - "policy_row"."no_reverse_beat_path" = FALSE OR 2.916 - "initiative"."reverse_beat_path" = FALSE ); 2.917 - -- mark final winner: 2.918 - UPDATE "initiative" SET "winner" = TRUE 2.919 - FROM ( 2.920 - SELECT "id" AS "initiative_id" 2.921 - FROM "initiative" 2.922 - WHERE "issue_id" = "issue_id_p" AND "eligible" 2.923 - ORDER BY "schulze_rank", "id" 2.924 - LIMIT 1 2.925 - ) AS "subquery" 2.926 - WHERE "id" = "subquery"."initiative_id"; 2.927 - -- write (final) ranks: 2.928 - "rank_v" := 1; 2.929 - FOR "initiative_id_v" IN 2.930 - SELECT "id" 2.931 - FROM "initiative" 2.932 - WHERE "issue_id" = "issue_id_p" AND "admitted" 2.933 - ORDER BY 2.934 - "winner" DESC, 2.935 - ("direct_majority" AND "indirect_majority") DESC, 2.936 - "schulze_rank", 2.937 - "id" 2.938 - LOOP 2.939 - UPDATE "initiative" SET "rank" = "rank_v" 2.940 - WHERE "id" = "initiative_id_v"; 2.941 - "rank_v" := "rank_v" + 1; 2.942 - END LOOP; 2.943 - -- set schulze rank of status quo and mark issue as finished: 2.944 - UPDATE "issue" SET 2.945 - "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 2.946 - "state" = 2.947 - CASE WHEN EXISTS ( 2.948 - SELECT NULL FROM "initiative" 2.949 - WHERE "issue_id" = "issue_id_p" AND "winner" 2.950 - ) THEN 2.951 - 'finished_with_winner'::"issue_state" 2.952 - ELSE 2.953 - 'finished_without_winner'::"issue_state" 2.954 - END, 2.955 - "ranks_available" = TRUE 2.956 - WHERE "id" = "issue_id_p"; 2.957 - RETURN; 2.958 - END; 2.959 - $$; 2.960 - 2.961 -CREATE OR REPLACE FUNCTION "check_issue" 2.962 - ( "issue_id_p" "issue"."id"%TYPE ) 2.963 - RETURNS VOID 2.964 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.965 - DECLARE 2.966 - "issue_row" "issue"%ROWTYPE; 2.967 - "policy_row" "policy"%ROWTYPE; 2.968 - BEGIN 2.969 - PERFORM "lock_issue"("issue_id_p"); 2.970 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.971 - -- only process open issues: 2.972 - IF "issue_row"."closed" ISNULL THEN 2.973 - SELECT * INTO "policy_row" FROM "policy" 2.974 - WHERE "id" = "issue_row"."policy_id"; 2.975 - -- create a snapshot, unless issue is already fully frozen: 2.976 - IF "issue_row"."fully_frozen" ISNULL THEN 2.977 - PERFORM "create_snapshot"("issue_id_p"); 2.978 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.979 - END IF; 2.980 - -- eventually close or accept issues, which have not been accepted: 2.981 - IF "issue_row"."accepted" ISNULL THEN 2.982 - IF EXISTS ( 2.983 - SELECT NULL FROM "initiative" 2.984 - WHERE "issue_id" = "issue_id_p" 2.985 - AND "supporter_count" > 0 2.986 - AND "supporter_count" * "policy_row"."issue_quorum_den" 2.987 - >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.988 - ) THEN 2.989 - -- accept issues, if supporter count is high enough 2.990 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.991 - -- NOTE: "issue_row" used later 2.992 - "issue_row"."state" := 'discussion'; 2.993 - "issue_row"."accepted" := now(); 2.994 - UPDATE "issue" SET 2.995 - "state" = "issue_row"."state", 2.996 - "accepted" = "issue_row"."accepted" 2.997 - WHERE "id" = "issue_row"."id"; 2.998 - ELSIF 2.999 - now() >= "issue_row"."created" + "issue_row"."admission_time" 2.1000 - THEN 2.1001 - -- close issues, if admission time has expired 2.1002 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1003 - UPDATE "issue" SET 2.1004 - "state" = 'canceled_issue_not_accepted', 2.1005 - "closed" = now() 2.1006 - WHERE "id" = "issue_row"."id"; 2.1007 - END IF; 2.1008 - END IF; 2.1009 - -- eventually half freeze issues: 2.1010 - IF 2.1011 - -- NOTE: issue can't be closed at this point, if it has been accepted 2.1012 - "issue_row"."accepted" NOTNULL AND 2.1013 - "issue_row"."half_frozen" ISNULL 2.1014 - THEN 2.1015 - IF 2.1016 - now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.1017 - THEN 2.1018 - PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.1019 - -- NOTE: "issue_row" used later 2.1020 - "issue_row"."state" := 'verification'; 2.1021 - "issue_row"."half_frozen" := now(); 2.1022 - UPDATE "issue" SET 2.1023 - "state" = "issue_row"."state", 2.1024 - "half_frozen" = "issue_row"."half_frozen" 2.1025 - WHERE "id" = "issue_row"."id"; 2.1026 - END IF; 2.1027 - END IF; 2.1028 - -- close issues after some time, if all initiatives have been revoked: 2.1029 - IF 2.1030 - "issue_row"."closed" ISNULL AND 2.1031 - NOT EXISTS ( 2.1032 - -- all initiatives are revoked 2.1033 - SELECT NULL FROM "initiative" 2.1034 - WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.1035 - ) AND ( 2.1036 - -- and issue has not been accepted yet 2.1037 - "issue_row"."accepted" ISNULL OR 2.1038 - NOT EXISTS ( 2.1039 - -- or no initiatives have been revoked lately 2.1040 - SELECT NULL FROM "initiative" 2.1041 - WHERE "issue_id" = "issue_id_p" 2.1042 - AND now() < "revoked" + "issue_row"."verification_time" 2.1043 - ) OR ( 2.1044 - -- or verification time has elapsed 2.1045 - "issue_row"."half_frozen" NOTNULL AND 2.1046 - "issue_row"."fully_frozen" ISNULL AND 2.1047 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1048 - ) 2.1049 - ) 2.1050 - THEN 2.1051 - -- NOTE: "issue_row" used later 2.1052 - IF "issue_row"."accepted" ISNULL THEN 2.1053 - "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.1054 - ELSIF "issue_row"."half_frozen" ISNULL THEN 2.1055 - "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.1056 - ELSE 2.1057 - "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.1058 - END IF; 2.1059 - "issue_row"."closed" := now(); 2.1060 - UPDATE "issue" SET 2.1061 - "state" = "issue_row"."state", 2.1062 - "closed" = "issue_row"."closed" 2.1063 - WHERE "id" = "issue_row"."id"; 2.1064 - END IF; 2.1065 - -- fully freeze issue after verification time: 2.1066 - IF 2.1067 - "issue_row"."half_frozen" NOTNULL AND 2.1068 - "issue_row"."fully_frozen" ISNULL AND 2.1069 - "issue_row"."closed" ISNULL AND 2.1070 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1071 - THEN 2.1072 - PERFORM "freeze_after_snapshot"("issue_id_p"); 2.1073 - -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.1074 - END IF; 2.1075 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1076 - -- close issue by calling close_voting(...) after voting time: 2.1077 - IF 2.1078 - "issue_row"."closed" ISNULL AND 2.1079 - "issue_row"."fully_frozen" NOTNULL AND 2.1080 - now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.1081 - THEN 2.1082 - PERFORM "close_voting"("issue_id_p"); 2.1083 - -- calculate ranks will not consume much time and can be done now 2.1084 - PERFORM "calculate_ranks"("issue_id_p"); 2.1085 - END IF; 2.1086 - END IF; 2.1087 - RETURN; 2.1088 - END; 2.1089 - $$; 2.1090 - 2.1091 -CREATE OR REPLACE FUNCTION "check_everything"() 2.1092 - RETURNS VOID 2.1093 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1094 - DECLARE 2.1095 - "issue_id_v" "issue"."id"%TYPE; 2.1096 - BEGIN 2.1097 - PERFORM "check_activity"(); 2.1098 - PERFORM "calculate_member_counts"(); 2.1099 - FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 2.1100 - PERFORM "check_issue"("issue_id_v"); 2.1101 - END LOOP; 2.1102 - FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 2.1103 - PERFORM "calculate_ranks"("issue_id_v"); 2.1104 - END LOOP; 2.1105 - RETURN; 2.1106 - END; 2.1107 - $$; 2.1108 - 2.1109 -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.1110 - RETURNS VOID 2.1111 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1112 - BEGIN 2.1113 - UPDATE "member" SET 2.1114 - "last_login" = NULL, 2.1115 - "login" = NULL, 2.1116 - "password" = NULL, 2.1117 - "locked" = TRUE, 2.1118 - "active" = FALSE, 2.1119 - "notify_email" = NULL, 2.1120 - "notify_email_unconfirmed" = NULL, 2.1121 - "notify_email_secret" = NULL, 2.1122 - "notify_email_secret_expiry" = NULL, 2.1123 - "notify_email_lock_expiry" = NULL, 2.1124 - "password_reset_secret" = NULL, 2.1125 - "password_reset_secret_expiry" = NULL, 2.1126 - "organizational_unit" = NULL, 2.1127 - "internal_posts" = NULL, 2.1128 - "realname" = NULL, 2.1129 - "birthday" = NULL, 2.1130 - "address" = NULL, 2.1131 - "email" = NULL, 2.1132 - "xmpp_address" = NULL, 2.1133 - "website" = NULL, 2.1134 - "phone" = NULL, 2.1135 - "mobile_phone" = NULL, 2.1136 - "profession" = NULL, 2.1137 - "external_memberships" = NULL, 2.1138 - "external_posts" = NULL, 2.1139 - "statement" = NULL 2.1140 - WHERE "id" = "member_id_p"; 2.1141 - -- "text_search_data" is updated by triggers 2.1142 - DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.1143 - DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.1144 - DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.1145 - DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.1146 - DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.1147 - DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 2.1148 - DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.1149 - DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.1150 - DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 2.1151 - DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.1152 - DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.1153 - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.1154 - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.1155 - DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 2.1156 - DELETE FROM "direct_voter" USING "issue" 2.1157 - WHERE "direct_voter"."issue_id" = "issue"."id" 2.1158 - AND "issue"."closed" ISNULL 2.1159 - AND "member_id" = "member_id_p"; 2.1160 - RETURN; 2.1161 - END; 2.1162 - $$; 2.1163 - 2.1164 -CREATE OR REPLACE FUNCTION "delete_private_data"() 2.1165 - RETURNS VOID 2.1166 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1167 - BEGIN 2.1168 - UPDATE "member" SET 2.1169 - "last_login" = NULL, 2.1170 - "login" = NULL, 2.1171 - "password" = NULL, 2.1172 - "notify_email" = NULL, 2.1173 - "notify_email_unconfirmed" = NULL, 2.1174 - "notify_email_secret" = NULL, 2.1175 - "notify_email_secret_expiry" = NULL, 2.1176 - "notify_email_lock_expiry" = NULL, 2.1177 - "password_reset_secret" = NULL, 2.1178 - "password_reset_secret_expiry" = NULL, 2.1179 - "organizational_unit" = NULL, 2.1180 - "internal_posts" = NULL, 2.1181 - "realname" = NULL, 2.1182 - "birthday" = NULL, 2.1183 - "address" = NULL, 2.1184 - "email" = NULL, 2.1185 - "xmpp_address" = NULL, 2.1186 - "website" = NULL, 2.1187 - "phone" = NULL, 2.1188 - "mobile_phone" = NULL, 2.1189 - "profession" = NULL, 2.1190 - "external_memberships" = NULL, 2.1191 - "external_posts" = NULL, 2.1192 - "statement" = NULL; 2.1193 - -- "text_search_data" is updated by triggers 2.1194 - DELETE FROM "invite_code"; 2.1195 - DELETE FROM "setting"; 2.1196 - DELETE FROM "setting_map"; 2.1197 - DELETE FROM "member_relation_setting"; 2.1198 - DELETE FROM "member_image"; 2.1199 - DELETE FROM "contact"; 2.1200 - DELETE FROM "ignored_member"; 2.1201 - DELETE FROM "area_setting"; 2.1202 - DELETE FROM "issue_setting"; 2.1203 - DELETE FROM "ignored_initiative"; 2.1204 - DELETE FROM "initiative_setting"; 2.1205 - DELETE FROM "suggestion_setting"; 2.1206 - DELETE FROM "non_voter"; 2.1207 - DELETE FROM "direct_voter" USING "issue" 2.1208 - WHERE "direct_voter"."issue_id" = "issue"."id" 2.1209 - AND "issue"."closed" ISNULL; 2.1210 - RETURN; 2.1211 - END; 2.1212 - $$; 2.1213 - 2.1214 -COMMIT; 2.1215 - 2.1216 -BEGIN; 2.1217 - 2.1218 -UPDATE "member" SET 2.1219 - "activated" = "created", 2.1220 - "last_activity" = CASE WHEN "active" THEN 2.1221 - coalesce("last_login"::DATE, now()) 2.1222 - ELSE 2.1223 - "last_login"::DATE 2.1224 - END; 2.1225 - 2.1226 -UPDATE "member" SET 2.1227 - "created" = "invite_code"."created", 2.1228 - "invite_code" = "invite_code"."code", 2.1229 - "admin_comment" = "invite_code"."comment" 2.1230 - FROM "invite_code" 2.1231 - WHERE "member"."id" = "invite_code"."member_id"; 2.1232 - 2.1233 -DROP TABLE "invite_code"; 2.1234 - 2.1235 -UPDATE "initiative" SET 2.1236 - "direct_majority" = "rank" NOTNULL, 2.1237 - "indirect_majority" = "rank" NOTNULL, 2.1238 - "schulze_rank" = "rank", 2.1239 - "better_than_status_quo" = "rank" NOTNULL, 2.1240 - "worse_than_status_quo" = "rank" ISNULL, 2.1241 - "reverse_beat_path" = "rank" ISNULL, 2.1242 - "multistage_majority" = "rank" ISNULL, 2.1243 - "eligible" = "rank" NOTNULL, 2.1244 - "winner" = ("rank" = 1) 2.1245 - FROM "issue" 2.1246 - WHERE "issue"."id" = "initiative"."issue_id" 2.1247 - AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 2.1248 - AND "initiative"."admitted"; 2.1249 - 2.1250 -UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" 2.1251 - FROM ( 2.1252 - SELECT 2.1253 - "issue"."id" AS "issue_id", 2.1254 - COALESCE(max("initiative"."rank") + 1, 1) AS "rank" 2.1255 - FROM "issue" JOIN "initiative" 2.1256 - ON "issue"."id" = "initiative"."issue_id" 2.1257 - WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 2.1258 - AND "initiative"."admitted" 2.1259 - GROUP BY "issue"."id" 2.1260 - ) AS "subquery" 2.1261 - WHERE "issue"."id" = "subquery"."issue_id"; 2.1262 - 2.1263 -CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) 2.1264 - RETURNS VOID 2.1265 - LANGUAGE 'plpgsql' AS $$ 2.1266 - DECLARE 2.1267 - "rank_v" INT4; 2.1268 - "initiative_id_v" INT4; 2.1269 - BEGIN 2.1270 - SELECT "status_quo_schulze_rank" INTO "rank_v" 2.1271 - FROM "issue" WHERE "id" = "issue_id_p"; 2.1272 - FOR "initiative_id_v" IN 2.1273 - SELECT "id" FROM "initiative" 2.1274 - WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL 2.1275 - ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC 2.1276 - LOOP 2.1277 - UPDATE "initiative" SET 2.1278 - "schulze_rank" = "rank_v" + 1, 2.1279 - "rank" = "rank_v" 2.1280 - WHERE "id" = "initiative_id_v"; 2.1281 - "rank_v" := "rank_v" + 1; 2.1282 - END LOOP; 2.1283 - RETURN; 2.1284 - END; 2.1285 - $$; 2.1286 - 2.1287 -SELECT "update__set_remaining_ranks"("id") FROM "issue" 2.1288 - WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); 2.1289 - 2.1290 -DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); 2.1291 - 2.1292 -UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" 2.1293 - FROM ( 2.1294 - SELECT DISTINCT ON ("suggestion"."id") 2.1295 - "suggestion"."id" AS "suggestion_id", 2.1296 - "draft"."id" AS "draft_id" 2.1297 - FROM "suggestion" JOIN "draft" 2.1298 - ON "suggestion"."initiative_id" = "draft"."initiative_id" 2.1299 - WHERE "draft"."created" <= "suggestion"."created" 2.1300 - ORDER BY "suggestion"."id", "draft"."created" DESC 2.1301 - ) AS "subquery" 2.1302 - WHERE "suggestion"."id" = "subquery"."suggestion_id"; 2.1303 - 2.1304 -COMMIT; 2.1305 - 2.1306 -ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" 2.1307 - CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); 2.1308 -ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql Sat Jul 30 01:57:27 2011 +0200 3.3 @@ -0,0 +1,1305 @@ 3.4 +SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing"; 3.5 + 3.6 +BEGIN; 3.7 + 3.8 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.9 + SELECT * FROM (VALUES ('1.5.0_rc1', 1, 5, -1)) 3.10 + AS "subquery"("string", "major", "minor", "revision"); 3.11 + 3.12 +ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE; 3.13 +ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT; 3.14 +ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ; 3.15 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE; 3.16 +ALTER TABLE "member" DROP COLUMN "last_login_public"; 3.17 +ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; 3.18 +ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; 3.19 + 3.20 +COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; 3.21 +COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; 3.22 +COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes'; 3.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'; 3.24 +COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; 3.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".'; 3.26 +COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; 3.27 + 3.28 +CREATE TYPE "application_access_level" AS ENUM 3.29 + ('member', 'full', 'pseudonymous', 'anonymous'); 3.30 + 3.31 +COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API'; 3.32 + 3.33 +CREATE TABLE "member_application" ( 3.34 + "id" SERIAL8 PRIMARY KEY, 3.35 + UNIQUE ("member_id", "name"), 3.36 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") 3.37 + ON DELETE CASCADE ON UPDATE CASCADE, 3.38 + "name" TEXT NOT NULL, 3.39 + "comment" TEXT, 3.40 + "access_level" "application_access_level" NOT NULL, 3.41 + "key" TEXT NOT NULL, 3.42 + "last_usage" TIMESTAMPTZ ); 3.43 + 3.44 +COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API'; 3.45 + 3.46 +CREATE TABLE "rendered_member_statement" ( 3.47 + PRIMARY KEY ("member_id", "format"), 3.48 + "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.49 + "format" TEXT, 3.50 + "content" TEXT NOT NULL ); 3.51 + 3.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)'; 3.53 + 3.54 +DROP VIEW "expired_session"; 3.55 +DROP TABLE "session"; 3.56 + 3.57 +ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; 3.58 +ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; 3.59 +ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 3.60 +ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; 3.61 +ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; 3.62 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; 3.63 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; 3.64 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; 3.65 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; 3.66 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; 3.67 +ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; 3.68 +ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; 3.69 + 3.70 +UPDATE "policy" SET 3.71 + "direct_majority_num" = "majority_num", 3.72 + "direct_majority_den" = "majority_den", 3.73 + "direct_majority_strict" = "majority_strict", 3.74 + "indirect_majority_num" = "majority_num", 3.75 + "indirect_majority_den" = "majority_den", 3.76 + "indirect_majority_strict" = "majority_strict"; 3.77 + 3.78 +ALTER TABLE "policy" DROP COLUMN "majority_num"; 3.79 +ALTER TABLE "policy" DROP COLUMN "majority_den"; 3.80 +ALTER TABLE "policy" DROP COLUMN "majority_strict"; 3.81 + 3.82 +COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; 3.83 +COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; 3.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.'; 3.85 +COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; 3.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'; 3.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'; 3.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'; 3.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.'; 3.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'; 3.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'; 3.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.'; 3.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").'; 3.94 + 3.95 +ALTER TABLE "area" DROP COLUMN "autoreject_weight"; 3.96 + 3.97 +DROP VIEW "open_issue"; 3.98 +DROP VIEW "issue_with_ranks_missing"; 3.99 + 3.100 +ALTER TABLE "issue" DROP COLUMN "vote_now"; 3.101 +ALTER TABLE "issue" DROP COLUMN "vote_later"; 3.102 +ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; 3.103 + 3.104 +CREATE VIEW "open_issue" AS 3.105 + SELECT * FROM "issue" WHERE "closed" ISNULL; 3.106 + 3.107 +COMMENT ON VIEW "open_issue" IS 'All open issues'; 3.108 + 3.109 +CREATE VIEW "issue_with_ranks_missing" AS 3.110 + SELECT * FROM "issue" 3.111 + WHERE "fully_frozen" NOTNULL 3.112 + AND "closed" NOTNULL 3.113 + AND "ranks_available" = FALSE; 3.114 + 3.115 +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; 3.116 + 3.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.'; 3.118 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 3.119 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 3.120 + 3.121 +DROP VIEW "battle_view"; 3.122 + 3.123 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 3.124 +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; 3.125 +ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; 3.126 + 3.127 +ALTER TABLE "initiative" DROP COLUMN "agreed"; 3.128 +ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; 3.129 +ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; 3.130 +ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; 3.131 +ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; 3.132 +ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; 3.133 +ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; 3.134 +ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; 3.135 +ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; 3.136 +ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; 3.137 + 3.138 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 3.139 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 3.140 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 3.141 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 3.142 + "schulze_rank" ISNULL AND 3.143 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 3.144 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 3.145 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 3.146 +ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); 3.147 +ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( 3.148 + "eligible" = FALSE OR 3.149 +("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); 3.150 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); 3.151 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); 3.152 +ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); 3.153 +ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); 3.154 + 3.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"'; 3.156 +COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; 3.157 +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 3.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)'; 3.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)'; 3.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'; 3.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'; 3.162 +COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; 3.163 +COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; 3.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'; 3.165 + 3.166 +ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; 3.167 +ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; 3.168 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 3.169 + "winning_initiative_id" != "losing_initiative_id" OR 3.170 + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 3.171 + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); 3.172 + 3.173 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 3.174 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 3.175 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 3.176 + 3.177 +ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; 3.178 +ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 3.179 +ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; 3.180 +ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; 3.181 + 3.182 +DROP TRIGGER "update_text_search_data" ON "suggestion"; 3.183 + 3.184 +CREATE TRIGGER "update_text_search_data" 3.185 + BEFORE INSERT OR UPDATE ON "suggestion" 3.186 + FOR EACH ROW EXECUTE PROCEDURE 3.187 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 3.188 + "name", "content"); 3.189 + 3.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")'; 3.191 + 3.192 +CREATE TABLE "rendered_suggestion" ( 3.193 + PRIMARY KEY ("suggestion_id", "format"), 3.194 + "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.195 + "format" TEXT, 3.196 + "content" TEXT NOT NULL ); 3.197 + 3.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)'; 3.199 + 3.200 +DROP TABLE "invite_code_unit"; 3.201 + 3.202 +DROP VIEW "area_member_count"; 3.203 + 3.204 +ALTER TABLE "membership" DROP COLUMN "autoreject"; 3.205 + 3.206 +ALTER TABLE "interest" DROP COLUMN "autoreject"; 3.207 +ALTER TABLE "interest" DROP COLUMN "voting_requested"; 3.208 + 3.209 +ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; 3.210 +ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; 3.211 + 3.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")'; 3.213 + 3.214 +ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; 3.215 +ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; 3.216 + 3.217 +DROP TRIGGER "default_for_draft_id" ON "supporter"; 3.218 +DROP FUNCTION "supporter_default_for_draft_id_trigger"(); 3.219 + 3.220 +CREATE FUNCTION "default_for_draft_id_trigger"() 3.221 + RETURNS TRIGGER 3.222 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.223 + BEGIN 3.224 + IF NEW."draft_id" ISNULL THEN 3.225 + SELECT "id" INTO NEW."draft_id" FROM "current_draft" 3.226 + WHERE "initiative_id" = NEW."initiative_id"; 3.227 + END IF; 3.228 + RETURN NEW; 3.229 + END; 3.230 + $$; 3.231 + 3.232 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" 3.233 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 3.234 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" 3.235 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 3.236 + 3.237 +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; 3.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'; 3.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'; 3.240 + 3.241 +CREATE VIEW "area_member_count" AS 3.242 + SELECT 3.243 + "area"."id" AS "area_id", 3.244 + count("member"."id") AS "direct_member_count", 3.245 + coalesce( 3.246 + sum( 3.247 + CASE WHEN "member"."id" NOTNULL THEN 3.248 + "membership_weight"("area"."id", "member"."id") 3.249 + ELSE 0 END 3.250 + ) 3.251 + ) AS "member_weight" 3.252 + FROM "area" 3.253 + LEFT JOIN "membership" 3.254 + ON "area"."id" = "membership"."area_id" 3.255 + LEFT JOIN "privilege" 3.256 + ON "privilege"."unit_id" = "area"."unit_id" 3.257 + AND "privilege"."member_id" = "membership"."member_id" 3.258 + AND "privilege"."voting_right" 3.259 + LEFT JOIN "member" 3.260 + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 3.261 + AND "member"."active" 3.262 + GROUP BY "area"."id"; 3.263 + 3.264 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; 3.265 + 3.266 +CREATE VIEW "battle_participant" AS 3.267 + SELECT "initiative"."id", "initiative"."issue_id" 3.268 + FROM "issue" JOIN "initiative" 3.269 + ON "issue"."id" = "initiative"."issue_id" 3.270 + WHERE "initiative"."admitted" 3.271 + UNION ALL 3.272 + SELECT NULL, "id" AS "issue_id" 3.273 + FROM "issue"; 3.274 + 3.275 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 3.276 + 3.277 +CREATE VIEW "battle_view" AS 3.278 + SELECT 3.279 + "issue"."id" AS "issue_id", 3.280 + "winning_initiative"."id" AS "winning_initiative_id", 3.281 + "losing_initiative"."id" AS "losing_initiative_id", 3.282 + sum( 3.283 + CASE WHEN 3.284 + coalesce("better_vote"."grade", 0) > 3.285 + coalesce("worse_vote"."grade", 0) 3.286 + THEN "direct_voter"."weight" ELSE 0 END 3.287 + ) AS "count" 3.288 + FROM "issue" 3.289 + LEFT JOIN "direct_voter" 3.290 + ON "issue"."id" = "direct_voter"."issue_id" 3.291 + JOIN "battle_participant" AS "winning_initiative" 3.292 + ON "issue"."id" = "winning_initiative"."issue_id" 3.293 + JOIN "battle_participant" AS "losing_initiative" 3.294 + ON "issue"."id" = "losing_initiative"."issue_id" 3.295 + LEFT JOIN "vote" AS "better_vote" 3.296 + ON "direct_voter"."member_id" = "better_vote"."member_id" 3.297 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 3.298 + LEFT JOIN "vote" AS "worse_vote" 3.299 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 3.300 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 3.301 + WHERE "issue"."closed" NOTNULL 3.302 + AND "issue"."cleaned" ISNULL 3.303 + AND ( 3.304 + "winning_initiative"."id" != "losing_initiative"."id" OR 3.305 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 3.306 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 3.307 + GROUP BY 3.308 + "issue"."id", 3.309 + "winning_initiative"."id", 3.310 + "losing_initiative"."id"; 3.311 + 3.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'; 3.313 + 3.314 +DROP FUNCTION "check_last_login"(); 3.315 + 3.316 +CREATE FUNCTION "check_activity"() 3.317 + RETURNS VOID 3.318 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.319 + DECLARE 3.320 + "system_setting_row" "system_setting"%ROWTYPE; 3.321 + BEGIN 3.322 + SELECT * INTO "system_setting_row" FROM "system_setting"; 3.323 + LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; 3.324 + IF "system_setting_row"."member_ttl" NOTNULL THEN 3.325 + UPDATE "member" SET "active" = FALSE 3.326 + WHERE "active" = TRUE 3.327 + AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; 3.328 + END IF; 3.329 + RETURN; 3.330 + END; 3.331 + $$; 3.332 + 3.333 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".'; 3.334 + 3.335 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 3.336 + ( "issue_id_p" "issue"."id"%TYPE ) 3.337 + RETURNS VOID 3.338 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.339 + DECLARE 3.340 + "member_id_v" "member"."id"%TYPE; 3.341 + BEGIN 3.342 + DELETE FROM "direct_interest_snapshot" 3.343 + WHERE "issue_id" = "issue_id_p" 3.344 + AND "event" = 'periodic'; 3.345 + DELETE FROM "delegating_interest_snapshot" 3.346 + WHERE "issue_id" = "issue_id_p" 3.347 + AND "event" = 'periodic'; 3.348 + DELETE FROM "direct_supporter_snapshot" 3.349 + WHERE "issue_id" = "issue_id_p" 3.350 + AND "event" = 'periodic'; 3.351 + INSERT INTO "direct_interest_snapshot" 3.352 + ("issue_id", "event", "member_id") 3.353 + SELECT 3.354 + "issue_id_p" AS "issue_id", 3.355 + 'periodic' AS "event", 3.356 + "member"."id" AS "member_id" 3.357 + FROM "issue" 3.358 + JOIN "area" ON "issue"."area_id" = "area"."id" 3.359 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 3.360 + JOIN "member" ON "interest"."member_id" = "member"."id" 3.361 + JOIN "privilege" 3.362 + ON "privilege"."unit_id" = "area"."unit_id" 3.363 + AND "privilege"."member_id" = "member"."id" 3.364 + WHERE "issue"."id" = "issue_id_p" 3.365 + AND "member"."active" AND "privilege"."voting_right"; 3.366 + FOR "member_id_v" IN 3.367 + SELECT "member_id" FROM "direct_interest_snapshot" 3.368 + WHERE "issue_id" = "issue_id_p" 3.369 + AND "event" = 'periodic' 3.370 + LOOP 3.371 + UPDATE "direct_interest_snapshot" SET 3.372 + "weight" = 1 + 3.373 + "weight_of_added_delegations_for_interest_snapshot"( 3.374 + "issue_id_p", 3.375 + "member_id_v", 3.376 + '{}' 3.377 + ) 3.378 + WHERE "issue_id" = "issue_id_p" 3.379 + AND "event" = 'periodic' 3.380 + AND "member_id" = "member_id_v"; 3.381 + END LOOP; 3.382 + INSERT INTO "direct_supporter_snapshot" 3.383 + ( "issue_id", "initiative_id", "event", "member_id", 3.384 + "informed", "satisfied" ) 3.385 + SELECT 3.386 + "issue_id_p" AS "issue_id", 3.387 + "initiative"."id" AS "initiative_id", 3.388 + 'periodic' AS "event", 3.389 + "supporter"."member_id" AS "member_id", 3.390 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 3.391 + NOT EXISTS ( 3.392 + SELECT NULL FROM "critical_opinion" 3.393 + WHERE "initiative_id" = "initiative"."id" 3.394 + AND "member_id" = "supporter"."member_id" 3.395 + ) AS "satisfied" 3.396 + FROM "initiative" 3.397 + JOIN "supporter" 3.398 + ON "supporter"."initiative_id" = "initiative"."id" 3.399 + JOIN "current_draft" 3.400 + ON "initiative"."id" = "current_draft"."initiative_id" 3.401 + JOIN "direct_interest_snapshot" 3.402 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 3.403 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 3.404 + AND "event" = 'periodic' 3.405 + WHERE "initiative"."issue_id" = "issue_id_p"; 3.406 + RETURN; 3.407 + END; 3.408 + $$; 3.409 + 3.410 +CREATE OR REPLACE FUNCTION "create_snapshot" 3.411 + ( "issue_id_p" "issue"."id"%TYPE ) 3.412 + RETURNS VOID 3.413 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.414 + DECLARE 3.415 + "initiative_id_v" "initiative"."id"%TYPE; 3.416 + "suggestion_id_v" "suggestion"."id"%TYPE; 3.417 + BEGIN 3.418 + PERFORM "lock_issue"("issue_id_p"); 3.419 + PERFORM "create_population_snapshot"("issue_id_p"); 3.420 + PERFORM "create_interest_snapshot"("issue_id_p"); 3.421 + UPDATE "issue" SET 3.422 + "snapshot" = now(), 3.423 + "latest_snapshot_event" = 'periodic', 3.424 + "population" = ( 3.425 + SELECT coalesce(sum("weight"), 0) 3.426 + FROM "direct_population_snapshot" 3.427 + WHERE "issue_id" = "issue_id_p" 3.428 + AND "event" = 'periodic' 3.429 + ) 3.430 + WHERE "id" = "issue_id_p"; 3.431 + FOR "initiative_id_v" IN 3.432 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 3.433 + LOOP 3.434 + UPDATE "initiative" SET 3.435 + "supporter_count" = ( 3.436 + SELECT coalesce(sum("di"."weight"), 0) 3.437 + FROM "direct_interest_snapshot" AS "di" 3.438 + JOIN "direct_supporter_snapshot" AS "ds" 3.439 + ON "di"."member_id" = "ds"."member_id" 3.440 + WHERE "di"."issue_id" = "issue_id_p" 3.441 + AND "di"."event" = 'periodic' 3.442 + AND "ds"."initiative_id" = "initiative_id_v" 3.443 + AND "ds"."event" = 'periodic' 3.444 + ), 3.445 + "informed_supporter_count" = ( 3.446 + SELECT coalesce(sum("di"."weight"), 0) 3.447 + FROM "direct_interest_snapshot" AS "di" 3.448 + JOIN "direct_supporter_snapshot" AS "ds" 3.449 + ON "di"."member_id" = "ds"."member_id" 3.450 + WHERE "di"."issue_id" = "issue_id_p" 3.451 + AND "di"."event" = 'periodic' 3.452 + AND "ds"."initiative_id" = "initiative_id_v" 3.453 + AND "ds"."event" = 'periodic' 3.454 + AND "ds"."informed" 3.455 + ), 3.456 + "satisfied_supporter_count" = ( 3.457 + SELECT coalesce(sum("di"."weight"), 0) 3.458 + FROM "direct_interest_snapshot" AS "di" 3.459 + JOIN "direct_supporter_snapshot" AS "ds" 3.460 + ON "di"."member_id" = "ds"."member_id" 3.461 + WHERE "di"."issue_id" = "issue_id_p" 3.462 + AND "di"."event" = 'periodic' 3.463 + AND "ds"."initiative_id" = "initiative_id_v" 3.464 + AND "ds"."event" = 'periodic' 3.465 + AND "ds"."satisfied" 3.466 + ), 3.467 + "satisfied_informed_supporter_count" = ( 3.468 + SELECT coalesce(sum("di"."weight"), 0) 3.469 + FROM "direct_interest_snapshot" AS "di" 3.470 + JOIN "direct_supporter_snapshot" AS "ds" 3.471 + ON "di"."member_id" = "ds"."member_id" 3.472 + WHERE "di"."issue_id" = "issue_id_p" 3.473 + AND "di"."event" = 'periodic' 3.474 + AND "ds"."initiative_id" = "initiative_id_v" 3.475 + AND "ds"."event" = 'periodic' 3.476 + AND "ds"."informed" 3.477 + AND "ds"."satisfied" 3.478 + ) 3.479 + WHERE "id" = "initiative_id_v"; 3.480 + FOR "suggestion_id_v" IN 3.481 + SELECT "id" FROM "suggestion" 3.482 + WHERE "initiative_id" = "initiative_id_v" 3.483 + LOOP 3.484 + UPDATE "suggestion" SET 3.485 + "minus2_unfulfilled_count" = ( 3.486 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.487 + FROM "issue" CROSS JOIN "opinion" 3.488 + JOIN "direct_interest_snapshot" AS "snapshot" 3.489 + ON "snapshot"."issue_id" = "issue"."id" 3.490 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.491 + AND "snapshot"."member_id" = "opinion"."member_id" 3.492 + WHERE "issue"."id" = "issue_id_p" 3.493 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.494 + AND "opinion"."degree" = -2 3.495 + AND "opinion"."fulfilled" = FALSE 3.496 + ), 3.497 + "minus2_fulfilled_count" = ( 3.498 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.499 + FROM "issue" CROSS JOIN "opinion" 3.500 + JOIN "direct_interest_snapshot" AS "snapshot" 3.501 + ON "snapshot"."issue_id" = "issue"."id" 3.502 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.503 + AND "snapshot"."member_id" = "opinion"."member_id" 3.504 + WHERE "issue"."id" = "issue_id_p" 3.505 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.506 + AND "opinion"."degree" = -2 3.507 + AND "opinion"."fulfilled" = TRUE 3.508 + ), 3.509 + "minus1_unfulfilled_count" = ( 3.510 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.511 + FROM "issue" CROSS JOIN "opinion" 3.512 + JOIN "direct_interest_snapshot" AS "snapshot" 3.513 + ON "snapshot"."issue_id" = "issue"."id" 3.514 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.515 + AND "snapshot"."member_id" = "opinion"."member_id" 3.516 + WHERE "issue"."id" = "issue_id_p" 3.517 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.518 + AND "opinion"."degree" = -1 3.519 + AND "opinion"."fulfilled" = FALSE 3.520 + ), 3.521 + "minus1_fulfilled_count" = ( 3.522 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.523 + FROM "issue" CROSS JOIN "opinion" 3.524 + JOIN "direct_interest_snapshot" AS "snapshot" 3.525 + ON "snapshot"."issue_id" = "issue"."id" 3.526 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.527 + AND "snapshot"."member_id" = "opinion"."member_id" 3.528 + WHERE "issue"."id" = "issue_id_p" 3.529 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.530 + AND "opinion"."degree" = -1 3.531 + AND "opinion"."fulfilled" = TRUE 3.532 + ), 3.533 + "plus1_unfulfilled_count" = ( 3.534 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.535 + FROM "issue" CROSS JOIN "opinion" 3.536 + JOIN "direct_interest_snapshot" AS "snapshot" 3.537 + ON "snapshot"."issue_id" = "issue"."id" 3.538 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.539 + AND "snapshot"."member_id" = "opinion"."member_id" 3.540 + WHERE "issue"."id" = "issue_id_p" 3.541 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.542 + AND "opinion"."degree" = 1 3.543 + AND "opinion"."fulfilled" = FALSE 3.544 + ), 3.545 + "plus1_fulfilled_count" = ( 3.546 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.547 + FROM "issue" CROSS JOIN "opinion" 3.548 + JOIN "direct_interest_snapshot" AS "snapshot" 3.549 + ON "snapshot"."issue_id" = "issue"."id" 3.550 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.551 + AND "snapshot"."member_id" = "opinion"."member_id" 3.552 + WHERE "issue"."id" = "issue_id_p" 3.553 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.554 + AND "opinion"."degree" = 1 3.555 + AND "opinion"."fulfilled" = TRUE 3.556 + ), 3.557 + "plus2_unfulfilled_count" = ( 3.558 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.559 + FROM "issue" CROSS JOIN "opinion" 3.560 + JOIN "direct_interest_snapshot" AS "snapshot" 3.561 + ON "snapshot"."issue_id" = "issue"."id" 3.562 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.563 + AND "snapshot"."member_id" = "opinion"."member_id" 3.564 + WHERE "issue"."id" = "issue_id_p" 3.565 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.566 + AND "opinion"."degree" = 2 3.567 + AND "opinion"."fulfilled" = FALSE 3.568 + ), 3.569 + "plus2_fulfilled_count" = ( 3.570 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.571 + FROM "issue" CROSS JOIN "opinion" 3.572 + JOIN "direct_interest_snapshot" AS "snapshot" 3.573 + ON "snapshot"."issue_id" = "issue"."id" 3.574 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.575 + AND "snapshot"."member_id" = "opinion"."member_id" 3.576 + WHERE "issue"."id" = "issue_id_p" 3.577 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.578 + AND "opinion"."degree" = 2 3.579 + AND "opinion"."fulfilled" = TRUE 3.580 + ) 3.581 + WHERE "suggestion"."id" = "suggestion_id_v"; 3.582 + END LOOP; 3.583 + END LOOP; 3.584 + RETURN; 3.585 + END; 3.586 + $$; 3.587 + 3.588 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 3.589 + RETURNS VOID 3.590 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.591 + DECLARE 3.592 + "area_id_v" "area"."id"%TYPE; 3.593 + "unit_id_v" "unit"."id"%TYPE; 3.594 + "member_id_v" "member"."id"%TYPE; 3.595 + BEGIN 3.596 + PERFORM "lock_issue"("issue_id_p"); 3.597 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 3.598 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 3.599 + -- delete delegating votes (in cases of manual reset of issue state): 3.600 + DELETE FROM "delegating_voter" 3.601 + WHERE "issue_id" = "issue_id_p"; 3.602 + -- delete votes from non-privileged voters: 3.603 + DELETE FROM "direct_voter" 3.604 + USING ( 3.605 + SELECT 3.606 + "direct_voter"."member_id" 3.607 + FROM "direct_voter" 3.608 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 3.609 + LEFT JOIN "privilege" 3.610 + ON "privilege"."unit_id" = "unit_id_v" 3.611 + AND "privilege"."member_id" = "direct_voter"."member_id" 3.612 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 3.613 + "member"."active" = FALSE OR 3.614 + "privilege"."voting_right" ISNULL OR 3.615 + "privilege"."voting_right" = FALSE 3.616 + ) 3.617 + ) AS "subquery" 3.618 + WHERE "direct_voter"."issue_id" = "issue_id_p" 3.619 + AND "direct_voter"."member_id" = "subquery"."member_id"; 3.620 + -- consider delegations: 3.621 + UPDATE "direct_voter" SET "weight" = 1 3.622 + WHERE "issue_id" = "issue_id_p"; 3.623 + PERFORM "add_vote_delegations"("issue_id_p"); 3.624 + -- set voter count and mark issue as being calculated: 3.625 + UPDATE "issue" SET 3.626 + "state" = 'calculation', 3.627 + "closed" = now(), 3.628 + "voter_count" = ( 3.629 + SELECT coalesce(sum("weight"), 0) 3.630 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 3.631 + ) 3.632 + WHERE "id" = "issue_id_p"; 3.633 + -- materialize battle_view: 3.634 + -- NOTE: "closed" column of issue must be set at this point 3.635 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.636 + INSERT INTO "battle" ( 3.637 + "issue_id", 3.638 + "winning_initiative_id", "losing_initiative_id", 3.639 + "count" 3.640 + ) SELECT 3.641 + "issue_id", 3.642 + "winning_initiative_id", "losing_initiative_id", 3.643 + "count" 3.644 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 3.645 + -- copy "positive_votes" and "negative_votes" from "battle" table: 3.646 + UPDATE "initiative" SET 3.647 + "positive_votes" = "battle_win"."count", 3.648 + "negative_votes" = "battle_lose"."count" 3.649 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 3.650 + WHERE 3.651 + "battle_win"."issue_id" = "issue_id_p" AND 3.652 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 3.653 + "battle_win"."losing_initiative_id" ISNULL AND 3.654 + "battle_lose"."issue_id" = "issue_id_p" AND 3.655 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 3.656 + "battle_lose"."winning_initiative_id" ISNULL; 3.657 + END; 3.658 + $$; 3.659 + 3.660 +DROP FUNCTION "array_init_string"(INTEGER); 3.661 +DROP FUNCTION "square_matrix_init_string"(INTEGER); 3.662 + 3.663 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 3.664 + RETURNS VOID 3.665 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.666 + DECLARE 3.667 + "issue_row" "issue"%ROWTYPE; 3.668 + "policy_row" "policy"%ROWTYPE; 3.669 + "dimension_v" INTEGER; 3.670 + "vote_matrix" INT4[][]; -- absolute votes 3.671 + "matrix" INT8[][]; -- defeat strength / best paths 3.672 + "i" INTEGER; 3.673 + "j" INTEGER; 3.674 + "k" INTEGER; 3.675 + "battle_row" "battle"%ROWTYPE; 3.676 + "rank_ary" INT4[]; 3.677 + "rank_v" INT4; 3.678 + "done_v" INTEGER; 3.679 + "winners_ary" INTEGER[]; 3.680 + "initiative_id_v" "initiative"."id"%TYPE; 3.681 + BEGIN 3.682 + SELECT * INTO "issue_row" 3.683 + FROM "issue" WHERE "id" = "issue_id_p" 3.684 + FOR UPDATE; 3.685 + SELECT * INTO "policy_row" 3.686 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 3.687 + SELECT count(1) INTO "dimension_v" 3.688 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 3.689 + -- Create "vote_matrix" with absolute number of votes in pairwise 3.690 + -- comparison: 3.691 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 3.692 + "i" := 1; 3.693 + "j" := 2; 3.694 + FOR "battle_row" IN 3.695 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 3.696 + ORDER BY 3.697 + "winning_initiative_id" NULLS LAST, 3.698 + "losing_initiative_id" NULLS LAST 3.699 + LOOP 3.700 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 3.701 + IF "j" = "dimension_v" THEN 3.702 + "i" := "i" + 1; 3.703 + "j" := 1; 3.704 + ELSE 3.705 + "j" := "j" + 1; 3.706 + IF "j" = "i" THEN 3.707 + "j" := "j" + 1; 3.708 + END IF; 3.709 + END IF; 3.710 + END LOOP; 3.711 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 3.712 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 3.713 + END IF; 3.714 + -- Store defeat strengths in "matrix" using "defeat_strength" 3.715 + -- function: 3.716 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 3.717 + "i" := 1; 3.718 + LOOP 3.719 + "j" := 1; 3.720 + LOOP 3.721 + IF "i" != "j" THEN 3.722 + "matrix"["i"]["j"] := "defeat_strength"( 3.723 + "vote_matrix"["i"]["j"], 3.724 + "vote_matrix"["j"]["i"] 3.725 + ); 3.726 + END IF; 3.727 + EXIT WHEN "j" = "dimension_v"; 3.728 + "j" := "j" + 1; 3.729 + END LOOP; 3.730 + EXIT WHEN "i" = "dimension_v"; 3.731 + "i" := "i" + 1; 3.732 + END LOOP; 3.733 + -- Find best paths: 3.734 + "i" := 1; 3.735 + LOOP 3.736 + "j" := 1; 3.737 + LOOP 3.738 + IF "i" != "j" THEN 3.739 + "k" := 1; 3.740 + LOOP 3.741 + IF "i" != "k" AND "j" != "k" THEN 3.742 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 3.743 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 3.744 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 3.745 + END IF; 3.746 + ELSE 3.747 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 3.748 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 3.749 + END IF; 3.750 + END IF; 3.751 + END IF; 3.752 + EXIT WHEN "k" = "dimension_v"; 3.753 + "k" := "k" + 1; 3.754 + END LOOP; 3.755 + END IF; 3.756 + EXIT WHEN "j" = "dimension_v"; 3.757 + "j" := "j" + 1; 3.758 + END LOOP; 3.759 + EXIT WHEN "i" = "dimension_v"; 3.760 + "i" := "i" + 1; 3.761 + END LOOP; 3.762 + -- Determine order of winners: 3.763 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 3.764 + "rank_v" := 1; 3.765 + "done_v" := 0; 3.766 + LOOP 3.767 + "winners_ary" := '{}'; 3.768 + "i" := 1; 3.769 + LOOP 3.770 + IF "rank_ary"["i"] ISNULL THEN 3.771 + "j" := 1; 3.772 + LOOP 3.773 + IF 3.774 + "i" != "j" AND 3.775 + "rank_ary"["j"] ISNULL AND 3.776 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 3.777 + THEN 3.778 + -- someone else is better 3.779 + EXIT; 3.780 + END IF; 3.781 + IF "j" = "dimension_v" THEN 3.782 + -- noone is better 3.783 + "winners_ary" := "winners_ary" || "i"; 3.784 + EXIT; 3.785 + END IF; 3.786 + "j" := "j" + 1; 3.787 + END LOOP; 3.788 + END IF; 3.789 + EXIT WHEN "i" = "dimension_v"; 3.790 + "i" := "i" + 1; 3.791 + END LOOP; 3.792 + "i" := 1; 3.793 + LOOP 3.794 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 3.795 + "done_v" := "done_v" + 1; 3.796 + EXIT WHEN "i" = array_upper("winners_ary", 1); 3.797 + "i" := "i" + 1; 3.798 + END LOOP; 3.799 + EXIT WHEN "done_v" = "dimension_v"; 3.800 + "rank_v" := "rank_v" + 1; 3.801 + END LOOP; 3.802 + -- write preliminary results: 3.803 + "i" := 1; 3.804 + FOR "initiative_id_v" IN 3.805 + SELECT "id" FROM "initiative" 3.806 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.807 + ORDER BY "id" 3.808 + LOOP 3.809 + UPDATE "initiative" SET 3.810 + "direct_majority" = 3.811 + CASE WHEN "policy_row"."direct_majority_strict" THEN 3.812 + "positive_votes" * "policy_row"."direct_majority_den" > 3.813 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.814 + ELSE 3.815 + "positive_votes" * "policy_row"."direct_majority_den" >= 3.816 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.817 + END 3.818 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 3.819 + AND "issue_row"."voter_count"-"negative_votes" >= 3.820 + "policy_row"."direct_majority_non_negative", 3.821 + "indirect_majority" = 3.822 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.823 + "positive_votes" * "policy_row"."indirect_majority_den" > 3.824 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.825 + ELSE 3.826 + "positive_votes" * "policy_row"."indirect_majority_den" >= 3.827 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.828 + END 3.829 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 3.830 + AND "issue_row"."voter_count"-"negative_votes" >= 3.831 + "policy_row"."indirect_majority_non_negative", 3.832 + "schulze_rank" = "rank_ary"["i"], 3.833 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 3.834 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 3.835 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 3.836 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 3.837 + "winner" = FALSE 3.838 + WHERE "id" = "initiative_id_v"; 3.839 + "i" := "i" + 1; 3.840 + END LOOP; 3.841 + IF "i" != "dimension_v" THEN 3.842 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 3.843 + END IF; 3.844 + -- take indirect majorities into account: 3.845 + LOOP 3.846 + UPDATE "initiative" SET "indirect_majority" = TRUE 3.847 + FROM ( 3.848 + SELECT "new_initiative"."id" AS "initiative_id" 3.849 + FROM "initiative" "old_initiative" 3.850 + JOIN "initiative" "new_initiative" 3.851 + ON "new_initiative"."issue_id" = "issue_id_p" 3.852 + AND "new_initiative"."indirect_majority" = FALSE 3.853 + JOIN "battle" "battle_win" 3.854 + ON "battle_win"."issue_id" = "issue_id_p" 3.855 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 3.856 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 3.857 + JOIN "battle" "battle_lose" 3.858 + ON "battle_lose"."issue_id" = "issue_id_p" 3.859 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 3.860 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 3.861 + WHERE "old_initiative"."issue_id" = "issue_id_p" 3.862 + AND "old_initiative"."indirect_majority" = TRUE 3.863 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.864 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 3.865 + "policy_row"."indirect_majority_num" * 3.866 + ("battle_win"."count"+"battle_lose"."count") 3.867 + ELSE 3.868 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 3.869 + "policy_row"."indirect_majority_num" * 3.870 + ("battle_win"."count"+"battle_lose"."count") 3.871 + END 3.872 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 3.873 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 3.874 + "policy_row"."indirect_majority_non_negative" 3.875 + ) AS "subquery" 3.876 + WHERE "id" = "subquery"."initiative_id"; 3.877 + EXIT WHEN NOT FOUND; 3.878 + END LOOP; 3.879 + -- set "multistage_majority" for remaining matching initiatives: 3.880 + UPDATE "initiative" SET "multistage_majority" = TRUE 3.881 + FROM ( 3.882 + SELECT "losing_initiative"."id" AS "initiative_id" 3.883 + FROM "initiative" "losing_initiative" 3.884 + JOIN "initiative" "winning_initiative" 3.885 + ON "winning_initiative"."issue_id" = "issue_id_p" 3.886 + AND "winning_initiative"."admitted" 3.887 + JOIN "battle" "battle_win" 3.888 + ON "battle_win"."issue_id" = "issue_id_p" 3.889 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 3.890 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 3.891 + JOIN "battle" "battle_lose" 3.892 + ON "battle_lose"."issue_id" = "issue_id_p" 3.893 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 3.894 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 3.895 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 3.896 + AND "losing_initiative"."admitted" 3.897 + AND "winning_initiative"."schulze_rank" < 3.898 + "losing_initiative"."schulze_rank" 3.899 + AND "battle_win"."count" > "battle_lose"."count" 3.900 + AND ( 3.901 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 3.902 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 3.903 + ) AS "subquery" 3.904 + WHERE "id" = "subquery"."initiative_id"; 3.905 + -- mark eligible initiatives: 3.906 + UPDATE "initiative" SET "eligible" = TRUE 3.907 + WHERE "issue_id" = "issue_id_p" 3.908 + AND "initiative"."direct_majority" 3.909 + AND "initiative"."indirect_majority" 3.910 + AND "initiative"."better_than_status_quo" 3.911 + AND ( 3.912 + "policy_row"."no_multistage_majority" = FALSE OR 3.913 + "initiative"."multistage_majority" = FALSE ) 3.914 + AND ( 3.915 + "policy_row"."no_reverse_beat_path" = FALSE OR 3.916 + "initiative"."reverse_beat_path" = FALSE ); 3.917 + -- mark final winner: 3.918 + UPDATE "initiative" SET "winner" = TRUE 3.919 + FROM ( 3.920 + SELECT "id" AS "initiative_id" 3.921 + FROM "initiative" 3.922 + WHERE "issue_id" = "issue_id_p" AND "eligible" 3.923 + ORDER BY "schulze_rank", "id" 3.924 + LIMIT 1 3.925 + ) AS "subquery" 3.926 + WHERE "id" = "subquery"."initiative_id"; 3.927 + -- write (final) ranks: 3.928 + "rank_v" := 1; 3.929 + FOR "initiative_id_v" IN 3.930 + SELECT "id" 3.931 + FROM "initiative" 3.932 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.933 + ORDER BY 3.934 + "winner" DESC, 3.935 + ("direct_majority" AND "indirect_majority") DESC, 3.936 + "schulze_rank", 3.937 + "id" 3.938 + LOOP 3.939 + UPDATE "initiative" SET "rank" = "rank_v" 3.940 + WHERE "id" = "initiative_id_v"; 3.941 + "rank_v" := "rank_v" + 1; 3.942 + END LOOP; 3.943 + -- set schulze rank of status quo and mark issue as finished: 3.944 + UPDATE "issue" SET 3.945 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 3.946 + "state" = 3.947 + CASE WHEN EXISTS ( 3.948 + SELECT NULL FROM "initiative" 3.949 + WHERE "issue_id" = "issue_id_p" AND "winner" 3.950 + ) THEN 3.951 + 'finished_with_winner'::"issue_state" 3.952 + ELSE 3.953 + 'finished_without_winner'::"issue_state" 3.954 + END, 3.955 + "ranks_available" = TRUE 3.956 + WHERE "id" = "issue_id_p"; 3.957 + RETURN; 3.958 + END; 3.959 + $$; 3.960 + 3.961 +CREATE OR REPLACE FUNCTION "check_issue" 3.962 + ( "issue_id_p" "issue"."id"%TYPE ) 3.963 + RETURNS VOID 3.964 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.965 + DECLARE 3.966 + "issue_row" "issue"%ROWTYPE; 3.967 + "policy_row" "policy"%ROWTYPE; 3.968 + BEGIN 3.969 + PERFORM "lock_issue"("issue_id_p"); 3.970 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.971 + -- only process open issues: 3.972 + IF "issue_row"."closed" ISNULL THEN 3.973 + SELECT * INTO "policy_row" FROM "policy" 3.974 + WHERE "id" = "issue_row"."policy_id"; 3.975 + -- create a snapshot, unless issue is already fully frozen: 3.976 + IF "issue_row"."fully_frozen" ISNULL THEN 3.977 + PERFORM "create_snapshot"("issue_id_p"); 3.978 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.979 + END IF; 3.980 + -- eventually close or accept issues, which have not been accepted: 3.981 + IF "issue_row"."accepted" ISNULL THEN 3.982 + IF EXISTS ( 3.983 + SELECT NULL FROM "initiative" 3.984 + WHERE "issue_id" = "issue_id_p" 3.985 + AND "supporter_count" > 0 3.986 + AND "supporter_count" * "policy_row"."issue_quorum_den" 3.987 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 3.988 + ) THEN 3.989 + -- accept issues, if supporter count is high enough 3.990 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 3.991 + -- NOTE: "issue_row" used later 3.992 + "issue_row"."state" := 'discussion'; 3.993 + "issue_row"."accepted" := now(); 3.994 + UPDATE "issue" SET 3.995 + "state" = "issue_row"."state", 3.996 + "accepted" = "issue_row"."accepted" 3.997 + WHERE "id" = "issue_row"."id"; 3.998 + ELSIF 3.999 + now() >= "issue_row"."created" + "issue_row"."admission_time" 3.1000 + THEN 3.1001 + -- close issues, if admission time has expired 3.1002 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 3.1003 + UPDATE "issue" SET 3.1004 + "state" = 'canceled_issue_not_accepted', 3.1005 + "closed" = now() 3.1006 + WHERE "id" = "issue_row"."id"; 3.1007 + END IF; 3.1008 + END IF; 3.1009 + -- eventually half freeze issues: 3.1010 + IF 3.1011 + -- NOTE: issue can't be closed at this point, if it has been accepted 3.1012 + "issue_row"."accepted" NOTNULL AND 3.1013 + "issue_row"."half_frozen" ISNULL 3.1014 + THEN 3.1015 + IF 3.1016 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 3.1017 + THEN 3.1018 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 3.1019 + -- NOTE: "issue_row" used later 3.1020 + "issue_row"."state" := 'verification'; 3.1021 + "issue_row"."half_frozen" := now(); 3.1022 + UPDATE "issue" SET 3.1023 + "state" = "issue_row"."state", 3.1024 + "half_frozen" = "issue_row"."half_frozen" 3.1025 + WHERE "id" = "issue_row"."id"; 3.1026 + END IF; 3.1027 + END IF; 3.1028 + -- close issues after some time, if all initiatives have been revoked: 3.1029 + IF 3.1030 + "issue_row"."closed" ISNULL AND 3.1031 + NOT EXISTS ( 3.1032 + -- all initiatives are revoked 3.1033 + SELECT NULL FROM "initiative" 3.1034 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 3.1035 + ) AND ( 3.1036 + -- and issue has not been accepted yet 3.1037 + "issue_row"."accepted" ISNULL OR 3.1038 + NOT EXISTS ( 3.1039 + -- or no initiatives have been revoked lately 3.1040 + SELECT NULL FROM "initiative" 3.1041 + WHERE "issue_id" = "issue_id_p" 3.1042 + AND now() < "revoked" + "issue_row"."verification_time" 3.1043 + ) OR ( 3.1044 + -- or verification time has elapsed 3.1045 + "issue_row"."half_frozen" NOTNULL AND 3.1046 + "issue_row"."fully_frozen" ISNULL AND 3.1047 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 3.1048 + ) 3.1049 + ) 3.1050 + THEN 3.1051 + -- NOTE: "issue_row" used later 3.1052 + IF "issue_row"."accepted" ISNULL THEN 3.1053 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 3.1054 + ELSIF "issue_row"."half_frozen" ISNULL THEN 3.1055 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 3.1056 + ELSE 3.1057 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 3.1058 + END IF; 3.1059 + "issue_row"."closed" := now(); 3.1060 + UPDATE "issue" SET 3.1061 + "state" = "issue_row"."state", 3.1062 + "closed" = "issue_row"."closed" 3.1063 + WHERE "id" = "issue_row"."id"; 3.1064 + END IF; 3.1065 + -- fully freeze issue after verification time: 3.1066 + IF 3.1067 + "issue_row"."half_frozen" NOTNULL AND 3.1068 + "issue_row"."fully_frozen" ISNULL AND 3.1069 + "issue_row"."closed" ISNULL AND 3.1070 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 3.1071 + THEN 3.1072 + PERFORM "freeze_after_snapshot"("issue_id_p"); 3.1073 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 3.1074 + END IF; 3.1075 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.1076 + -- close issue by calling close_voting(...) after voting time: 3.1077 + IF 3.1078 + "issue_row"."closed" ISNULL AND 3.1079 + "issue_row"."fully_frozen" NOTNULL AND 3.1080 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 3.1081 + THEN 3.1082 + PERFORM "close_voting"("issue_id_p"); 3.1083 + -- calculate ranks will not consume much time and can be done now 3.1084 + PERFORM "calculate_ranks"("issue_id_p"); 3.1085 + END IF; 3.1086 + END IF; 3.1087 + RETURN; 3.1088 + END; 3.1089 + $$; 3.1090 + 3.1091 +CREATE OR REPLACE FUNCTION "check_everything"() 3.1092 + RETURNS VOID 3.1093 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1094 + DECLARE 3.1095 + "issue_id_v" "issue"."id"%TYPE; 3.1096 + BEGIN 3.1097 + PERFORM "check_activity"(); 3.1098 + PERFORM "calculate_member_counts"(); 3.1099 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 3.1100 + PERFORM "check_issue"("issue_id_v"); 3.1101 + END LOOP; 3.1102 + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 3.1103 + PERFORM "calculate_ranks"("issue_id_v"); 3.1104 + END LOOP; 3.1105 + RETURN; 3.1106 + END; 3.1107 + $$; 3.1108 + 3.1109 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 3.1110 + RETURNS VOID 3.1111 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1112 + BEGIN 3.1113 + UPDATE "member" SET 3.1114 + "last_login" = NULL, 3.1115 + "login" = NULL, 3.1116 + "password" = NULL, 3.1117 + "locked" = TRUE, 3.1118 + "active" = FALSE, 3.1119 + "notify_email" = NULL, 3.1120 + "notify_email_unconfirmed" = NULL, 3.1121 + "notify_email_secret" = NULL, 3.1122 + "notify_email_secret_expiry" = NULL, 3.1123 + "notify_email_lock_expiry" = NULL, 3.1124 + "password_reset_secret" = NULL, 3.1125 + "password_reset_secret_expiry" = NULL, 3.1126 + "organizational_unit" = NULL, 3.1127 + "internal_posts" = NULL, 3.1128 + "realname" = NULL, 3.1129 + "birthday" = NULL, 3.1130 + "address" = NULL, 3.1131 + "email" = NULL, 3.1132 + "xmpp_address" = NULL, 3.1133 + "website" = NULL, 3.1134 + "phone" = NULL, 3.1135 + "mobile_phone" = NULL, 3.1136 + "profession" = NULL, 3.1137 + "external_memberships" = NULL, 3.1138 + "external_posts" = NULL, 3.1139 + "statement" = NULL 3.1140 + WHERE "id" = "member_id_p"; 3.1141 + -- "text_search_data" is updated by triggers 3.1142 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 3.1143 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 3.1144 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 3.1145 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 3.1146 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 3.1147 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 3.1148 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 3.1149 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 3.1150 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 3.1151 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 3.1152 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 3.1153 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 3.1154 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 3.1155 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 3.1156 + DELETE FROM "direct_voter" USING "issue" 3.1157 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.1158 + AND "issue"."closed" ISNULL 3.1159 + AND "member_id" = "member_id_p"; 3.1160 + RETURN; 3.1161 + END; 3.1162 + $$; 3.1163 + 3.1164 +CREATE OR REPLACE FUNCTION "delete_private_data"() 3.1165 + RETURNS VOID 3.1166 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1167 + BEGIN 3.1168 + UPDATE "member" SET 3.1169 + "last_login" = NULL, 3.1170 + "login" = NULL, 3.1171 + "password" = NULL, 3.1172 + "notify_email" = NULL, 3.1173 + "notify_email_unconfirmed" = NULL, 3.1174 + "notify_email_secret" = NULL, 3.1175 + "notify_email_secret_expiry" = NULL, 3.1176 + "notify_email_lock_expiry" = NULL, 3.1177 + "password_reset_secret" = NULL, 3.1178 + "password_reset_secret_expiry" = NULL, 3.1179 + "organizational_unit" = NULL, 3.1180 + "internal_posts" = NULL, 3.1181 + "realname" = NULL, 3.1182 + "birthday" = NULL, 3.1183 + "address" = NULL, 3.1184 + "email" = NULL, 3.1185 + "xmpp_address" = NULL, 3.1186 + "website" = NULL, 3.1187 + "phone" = NULL, 3.1188 + "mobile_phone" = NULL, 3.1189 + "profession" = NULL, 3.1190 + "external_memberships" = NULL, 3.1191 + "external_posts" = NULL, 3.1192 + "statement" = NULL; 3.1193 + -- "text_search_data" is updated by triggers 3.1194 + DELETE FROM "invite_code"; 3.1195 + DELETE FROM "setting"; 3.1196 + DELETE FROM "setting_map"; 3.1197 + DELETE FROM "member_relation_setting"; 3.1198 + DELETE FROM "member_image"; 3.1199 + DELETE FROM "contact"; 3.1200 + DELETE FROM "ignored_member"; 3.1201 + DELETE FROM "area_setting"; 3.1202 + DELETE FROM "issue_setting"; 3.1203 + DELETE FROM "ignored_initiative"; 3.1204 + DELETE FROM "initiative_setting"; 3.1205 + DELETE FROM "suggestion_setting"; 3.1206 + DELETE FROM "non_voter"; 3.1207 + DELETE FROM "direct_voter" USING "issue" 3.1208 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.1209 + AND "issue"."closed" ISNULL; 3.1210 + RETURN; 3.1211 + END; 3.1212 + $$; 3.1213 + 3.1214 +COMMIT; 3.1215 + 3.1216 +BEGIN; 3.1217 + 3.1218 +UPDATE "member" SET 3.1219 + "activated" = "created", 3.1220 + "last_activity" = CASE WHEN "active" THEN 3.1221 + coalesce("last_login"::DATE, now()) 3.1222 + ELSE 3.1223 + "last_login"::DATE 3.1224 + END; 3.1225 + 3.1226 +UPDATE "member" SET 3.1227 + "created" = "invite_code"."created", 3.1228 + "invite_code" = "invite_code"."code", 3.1229 + "admin_comment" = "invite_code"."comment" 3.1230 + FROM "invite_code" 3.1231 + WHERE "member"."id" = "invite_code"."member_id"; 3.1232 + 3.1233 +DROP TABLE "invite_code"; 3.1234 + 3.1235 +UPDATE "initiative" SET 3.1236 + "direct_majority" = "rank" NOTNULL, 3.1237 + "indirect_majority" = "rank" NOTNULL, 3.1238 + "schulze_rank" = "rank", 3.1239 + "better_than_status_quo" = "rank" NOTNULL, 3.1240 + "worse_than_status_quo" = "rank" ISNULL, 3.1241 + "reverse_beat_path" = "rank" ISNULL, 3.1242 + "multistage_majority" = "rank" ISNULL, 3.1243 + "eligible" = "rank" NOTNULL, 3.1244 + "winner" = ("rank" = 1) 3.1245 + FROM "issue" 3.1246 + WHERE "issue"."id" = "initiative"."issue_id" 3.1247 + AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 3.1248 + AND "initiative"."admitted"; 3.1249 + 3.1250 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" 3.1251 + FROM ( 3.1252 + SELECT 3.1253 + "issue"."id" AS "issue_id", 3.1254 + COALESCE(max("initiative"."rank") + 1, 1) AS "rank" 3.1255 + FROM "issue" JOIN "initiative" 3.1256 + ON "issue"."id" = "initiative"."issue_id" 3.1257 + WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 3.1258 + AND "initiative"."admitted" 3.1259 + GROUP BY "issue"."id" 3.1260 + ) AS "subquery" 3.1261 + WHERE "issue"."id" = "subquery"."issue_id"; 3.1262 + 3.1263 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) 3.1264 + RETURNS VOID 3.1265 + LANGUAGE 'plpgsql' AS $$ 3.1266 + DECLARE 3.1267 + "rank_v" INT4; 3.1268 + "initiative_id_v" INT4; 3.1269 + BEGIN 3.1270 + SELECT "status_quo_schulze_rank" INTO "rank_v" 3.1271 + FROM "issue" WHERE "id" = "issue_id_p"; 3.1272 + FOR "initiative_id_v" IN 3.1273 + SELECT "id" FROM "initiative" 3.1274 + WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL 3.1275 + ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC 3.1276 + LOOP 3.1277 + UPDATE "initiative" SET 3.1278 + "schulze_rank" = "rank_v" + 1, 3.1279 + "rank" = "rank_v" 3.1280 + WHERE "id" = "initiative_id_v"; 3.1281 + "rank_v" := "rank_v" + 1; 3.1282 + END LOOP; 3.1283 + RETURN; 3.1284 + END; 3.1285 + $$; 3.1286 + 3.1287 +SELECT "update__set_remaining_ranks"("id") FROM "issue" 3.1288 + WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); 3.1289 + 3.1290 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); 3.1291 + 3.1292 +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" 3.1293 + FROM ( 3.1294 + SELECT DISTINCT ON ("suggestion"."id") 3.1295 + "suggestion"."id" AS "suggestion_id", 3.1296 + "draft"."id" AS "draft_id" 3.1297 + FROM "suggestion" JOIN "draft" 3.1298 + ON "suggestion"."initiative_id" = "draft"."initiative_id" 3.1299 + WHERE "draft"."created" <= "suggestion"."created" 3.1300 + ORDER BY "suggestion"."id", "draft"."created" DESC 3.1301 + ) AS "subquery" 3.1302 + WHERE "suggestion"."id" = "subquery"."suggestion_id"; 3.1303 + 3.1304 +COMMIT; 3.1305 + 3.1306 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" 3.1307 + CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); 3.1308 +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;