liquid_feedback_core
changeset 193:055d232404d6
Renamed v1.5.0 to v2.0.0
author | jbe |
---|---|
date | Sat Sep 10 22:39:06 2011 +0200 (2011-09-10) |
parents | a5fed9a4154e |
children | 5d89f5fcdcfc |
files | core.sql update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql update/core-update.v1.4.0_rc4-v2.0.0.sql |
line diff
1.1 --- a/core.sql Sun Sep 04 23:42:05 2011 +0200 1.2 +++ b/core.sql Sat Sep 10 22:39:06 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_rc1', 1, 5, -1)) 1.8 + SELECT * FROM (VALUES ('2.0.0', 2, 0, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- a/update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql Sun Sep 04 23:42:05 2011 +0200 2.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 2.3 @@ -1,1330 +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_rc1', 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 UNIQUE, 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 "calculate_member_counts"() 2.336 - RETURNS VOID 2.337 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.338 - BEGIN 2.339 - LOCK TABLE "member" IN SHARE MODE; 2.340 - LOCK TABLE "member_count" IN EXCLUSIVE MODE; 2.341 - LOCK TABLE "unit" IN EXCLUSIVE MODE; 2.342 - LOCK TABLE "area" IN EXCLUSIVE MODE; 2.343 - LOCK TABLE "privilege" IN SHARE MODE; 2.344 - LOCK TABLE "membership" IN SHARE MODE; 2.345 - DELETE FROM "member_count"; 2.346 - INSERT INTO "member_count" ("total_count") 2.347 - SELECT "total_count" FROM "member_count_view"; 2.348 - UPDATE "unit" SET "member_count" = "view"."member_count" 2.349 - FROM "unit_member_count" AS "view" 2.350 - WHERE "view"."unit_id" = "unit"."id"; 2.351 - UPDATE "area" SET 2.352 - "direct_member_count" = "view"."direct_member_count", 2.353 - "member_weight" = "view"."member_weight" 2.354 - FROM "area_member_count" AS "view" 2.355 - WHERE "view"."area_id" = "area"."id"; 2.356 - RETURN; 2.357 - END; 2.358 - $$; 2.359 - 2.360 -CREATE OR REPLACE FUNCTION "create_interest_snapshot" 2.361 - ( "issue_id_p" "issue"."id"%TYPE ) 2.362 - RETURNS VOID 2.363 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.364 - DECLARE 2.365 - "member_id_v" "member"."id"%TYPE; 2.366 - BEGIN 2.367 - DELETE FROM "direct_interest_snapshot" 2.368 - WHERE "issue_id" = "issue_id_p" 2.369 - AND "event" = 'periodic'; 2.370 - DELETE FROM "delegating_interest_snapshot" 2.371 - WHERE "issue_id" = "issue_id_p" 2.372 - AND "event" = 'periodic'; 2.373 - DELETE FROM "direct_supporter_snapshot" 2.374 - WHERE "issue_id" = "issue_id_p" 2.375 - AND "event" = 'periodic'; 2.376 - INSERT INTO "direct_interest_snapshot" 2.377 - ("issue_id", "event", "member_id") 2.378 - SELECT 2.379 - "issue_id_p" AS "issue_id", 2.380 - 'periodic' AS "event", 2.381 - "member"."id" AS "member_id" 2.382 - FROM "issue" 2.383 - JOIN "area" ON "issue"."area_id" = "area"."id" 2.384 - JOIN "interest" ON "issue"."id" = "interest"."issue_id" 2.385 - JOIN "member" ON "interest"."member_id" = "member"."id" 2.386 - JOIN "privilege" 2.387 - ON "privilege"."unit_id" = "area"."unit_id" 2.388 - AND "privilege"."member_id" = "member"."id" 2.389 - WHERE "issue"."id" = "issue_id_p" 2.390 - AND "member"."active" AND "privilege"."voting_right"; 2.391 - FOR "member_id_v" IN 2.392 - SELECT "member_id" FROM "direct_interest_snapshot" 2.393 - WHERE "issue_id" = "issue_id_p" 2.394 - AND "event" = 'periodic' 2.395 - LOOP 2.396 - UPDATE "direct_interest_snapshot" SET 2.397 - "weight" = 1 + 2.398 - "weight_of_added_delegations_for_interest_snapshot"( 2.399 - "issue_id_p", 2.400 - "member_id_v", 2.401 - '{}' 2.402 - ) 2.403 - WHERE "issue_id" = "issue_id_p" 2.404 - AND "event" = 'periodic' 2.405 - AND "member_id" = "member_id_v"; 2.406 - END LOOP; 2.407 - INSERT INTO "direct_supporter_snapshot" 2.408 - ( "issue_id", "initiative_id", "event", "member_id", 2.409 - "informed", "satisfied" ) 2.410 - SELECT 2.411 - "issue_id_p" AS "issue_id", 2.412 - "initiative"."id" AS "initiative_id", 2.413 - 'periodic' AS "event", 2.414 - "supporter"."member_id" AS "member_id", 2.415 - "supporter"."draft_id" = "current_draft"."id" AS "informed", 2.416 - NOT EXISTS ( 2.417 - SELECT NULL FROM "critical_opinion" 2.418 - WHERE "initiative_id" = "initiative"."id" 2.419 - AND "member_id" = "supporter"."member_id" 2.420 - ) AS "satisfied" 2.421 - FROM "initiative" 2.422 - JOIN "supporter" 2.423 - ON "supporter"."initiative_id" = "initiative"."id" 2.424 - JOIN "current_draft" 2.425 - ON "initiative"."id" = "current_draft"."initiative_id" 2.426 - JOIN "direct_interest_snapshot" 2.427 - ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 2.428 - AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 2.429 - AND "event" = 'periodic' 2.430 - WHERE "initiative"."issue_id" = "issue_id_p"; 2.431 - RETURN; 2.432 - END; 2.433 - $$; 2.434 - 2.435 -CREATE OR REPLACE FUNCTION "create_snapshot" 2.436 - ( "issue_id_p" "issue"."id"%TYPE ) 2.437 - RETURNS VOID 2.438 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.439 - DECLARE 2.440 - "initiative_id_v" "initiative"."id"%TYPE; 2.441 - "suggestion_id_v" "suggestion"."id"%TYPE; 2.442 - BEGIN 2.443 - PERFORM "lock_issue"("issue_id_p"); 2.444 - PERFORM "create_population_snapshot"("issue_id_p"); 2.445 - PERFORM "create_interest_snapshot"("issue_id_p"); 2.446 - UPDATE "issue" SET 2.447 - "snapshot" = now(), 2.448 - "latest_snapshot_event" = 'periodic', 2.449 - "population" = ( 2.450 - SELECT coalesce(sum("weight"), 0) 2.451 - FROM "direct_population_snapshot" 2.452 - WHERE "issue_id" = "issue_id_p" 2.453 - AND "event" = 'periodic' 2.454 - ) 2.455 - WHERE "id" = "issue_id_p"; 2.456 - FOR "initiative_id_v" IN 2.457 - SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 2.458 - LOOP 2.459 - UPDATE "initiative" SET 2.460 - "supporter_count" = ( 2.461 - SELECT coalesce(sum("di"."weight"), 0) 2.462 - FROM "direct_interest_snapshot" AS "di" 2.463 - JOIN "direct_supporter_snapshot" AS "ds" 2.464 - ON "di"."member_id" = "ds"."member_id" 2.465 - WHERE "di"."issue_id" = "issue_id_p" 2.466 - AND "di"."event" = 'periodic' 2.467 - AND "ds"."initiative_id" = "initiative_id_v" 2.468 - AND "ds"."event" = 'periodic' 2.469 - ), 2.470 - "informed_supporter_count" = ( 2.471 - SELECT coalesce(sum("di"."weight"), 0) 2.472 - FROM "direct_interest_snapshot" AS "di" 2.473 - JOIN "direct_supporter_snapshot" AS "ds" 2.474 - ON "di"."member_id" = "ds"."member_id" 2.475 - WHERE "di"."issue_id" = "issue_id_p" 2.476 - AND "di"."event" = 'periodic' 2.477 - AND "ds"."initiative_id" = "initiative_id_v" 2.478 - AND "ds"."event" = 'periodic' 2.479 - AND "ds"."informed" 2.480 - ), 2.481 - "satisfied_supporter_count" = ( 2.482 - SELECT coalesce(sum("di"."weight"), 0) 2.483 - FROM "direct_interest_snapshot" AS "di" 2.484 - JOIN "direct_supporter_snapshot" AS "ds" 2.485 - ON "di"."member_id" = "ds"."member_id" 2.486 - WHERE "di"."issue_id" = "issue_id_p" 2.487 - AND "di"."event" = 'periodic' 2.488 - AND "ds"."initiative_id" = "initiative_id_v" 2.489 - AND "ds"."event" = 'periodic' 2.490 - AND "ds"."satisfied" 2.491 - ), 2.492 - "satisfied_informed_supporter_count" = ( 2.493 - SELECT coalesce(sum("di"."weight"), 0) 2.494 - FROM "direct_interest_snapshot" AS "di" 2.495 - JOIN "direct_supporter_snapshot" AS "ds" 2.496 - ON "di"."member_id" = "ds"."member_id" 2.497 - WHERE "di"."issue_id" = "issue_id_p" 2.498 - AND "di"."event" = 'periodic' 2.499 - AND "ds"."initiative_id" = "initiative_id_v" 2.500 - AND "ds"."event" = 'periodic' 2.501 - AND "ds"."informed" 2.502 - AND "ds"."satisfied" 2.503 - ) 2.504 - WHERE "id" = "initiative_id_v"; 2.505 - FOR "suggestion_id_v" IN 2.506 - SELECT "id" FROM "suggestion" 2.507 - WHERE "initiative_id" = "initiative_id_v" 2.508 - LOOP 2.509 - UPDATE "suggestion" SET 2.510 - "minus2_unfulfilled_count" = ( 2.511 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.512 - FROM "issue" CROSS JOIN "opinion" 2.513 - JOIN "direct_interest_snapshot" AS "snapshot" 2.514 - ON "snapshot"."issue_id" = "issue"."id" 2.515 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.516 - AND "snapshot"."member_id" = "opinion"."member_id" 2.517 - WHERE "issue"."id" = "issue_id_p" 2.518 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.519 - AND "opinion"."degree" = -2 2.520 - AND "opinion"."fulfilled" = FALSE 2.521 - ), 2.522 - "minus2_fulfilled_count" = ( 2.523 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.524 - FROM "issue" CROSS JOIN "opinion" 2.525 - JOIN "direct_interest_snapshot" AS "snapshot" 2.526 - ON "snapshot"."issue_id" = "issue"."id" 2.527 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.528 - AND "snapshot"."member_id" = "opinion"."member_id" 2.529 - WHERE "issue"."id" = "issue_id_p" 2.530 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.531 - AND "opinion"."degree" = -2 2.532 - AND "opinion"."fulfilled" = TRUE 2.533 - ), 2.534 - "minus1_unfulfilled_count" = ( 2.535 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.536 - FROM "issue" CROSS JOIN "opinion" 2.537 - JOIN "direct_interest_snapshot" AS "snapshot" 2.538 - ON "snapshot"."issue_id" = "issue"."id" 2.539 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.540 - AND "snapshot"."member_id" = "opinion"."member_id" 2.541 - WHERE "issue"."id" = "issue_id_p" 2.542 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.543 - AND "opinion"."degree" = -1 2.544 - AND "opinion"."fulfilled" = FALSE 2.545 - ), 2.546 - "minus1_fulfilled_count" = ( 2.547 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.548 - FROM "issue" CROSS JOIN "opinion" 2.549 - JOIN "direct_interest_snapshot" AS "snapshot" 2.550 - ON "snapshot"."issue_id" = "issue"."id" 2.551 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.552 - AND "snapshot"."member_id" = "opinion"."member_id" 2.553 - WHERE "issue"."id" = "issue_id_p" 2.554 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.555 - AND "opinion"."degree" = -1 2.556 - AND "opinion"."fulfilled" = TRUE 2.557 - ), 2.558 - "plus1_unfulfilled_count" = ( 2.559 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.560 - FROM "issue" CROSS JOIN "opinion" 2.561 - JOIN "direct_interest_snapshot" AS "snapshot" 2.562 - ON "snapshot"."issue_id" = "issue"."id" 2.563 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.564 - AND "snapshot"."member_id" = "opinion"."member_id" 2.565 - WHERE "issue"."id" = "issue_id_p" 2.566 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.567 - AND "opinion"."degree" = 1 2.568 - AND "opinion"."fulfilled" = FALSE 2.569 - ), 2.570 - "plus1_fulfilled_count" = ( 2.571 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.572 - FROM "issue" CROSS JOIN "opinion" 2.573 - JOIN "direct_interest_snapshot" AS "snapshot" 2.574 - ON "snapshot"."issue_id" = "issue"."id" 2.575 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.576 - AND "snapshot"."member_id" = "opinion"."member_id" 2.577 - WHERE "issue"."id" = "issue_id_p" 2.578 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.579 - AND "opinion"."degree" = 1 2.580 - AND "opinion"."fulfilled" = TRUE 2.581 - ), 2.582 - "plus2_unfulfilled_count" = ( 2.583 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.584 - FROM "issue" CROSS JOIN "opinion" 2.585 - JOIN "direct_interest_snapshot" AS "snapshot" 2.586 - ON "snapshot"."issue_id" = "issue"."id" 2.587 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.588 - AND "snapshot"."member_id" = "opinion"."member_id" 2.589 - WHERE "issue"."id" = "issue_id_p" 2.590 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.591 - AND "opinion"."degree" = 2 2.592 - AND "opinion"."fulfilled" = FALSE 2.593 - ), 2.594 - "plus2_fulfilled_count" = ( 2.595 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.596 - FROM "issue" CROSS JOIN "opinion" 2.597 - JOIN "direct_interest_snapshot" AS "snapshot" 2.598 - ON "snapshot"."issue_id" = "issue"."id" 2.599 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.600 - AND "snapshot"."member_id" = "opinion"."member_id" 2.601 - WHERE "issue"."id" = "issue_id_p" 2.602 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.603 - AND "opinion"."degree" = 2 2.604 - AND "opinion"."fulfilled" = TRUE 2.605 - ) 2.606 - WHERE "suggestion"."id" = "suggestion_id_v"; 2.607 - END LOOP; 2.608 - END LOOP; 2.609 - RETURN; 2.610 - END; 2.611 - $$; 2.612 - 2.613 -CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.614 - RETURNS VOID 2.615 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.616 - DECLARE 2.617 - "area_id_v" "area"."id"%TYPE; 2.618 - "unit_id_v" "unit"."id"%TYPE; 2.619 - "member_id_v" "member"."id"%TYPE; 2.620 - BEGIN 2.621 - PERFORM "lock_issue"("issue_id_p"); 2.622 - SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.623 - SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.624 - -- delete delegating votes (in cases of manual reset of issue state): 2.625 - DELETE FROM "delegating_voter" 2.626 - WHERE "issue_id" = "issue_id_p"; 2.627 - -- delete votes from non-privileged voters: 2.628 - DELETE FROM "direct_voter" 2.629 - USING ( 2.630 - SELECT 2.631 - "direct_voter"."member_id" 2.632 - FROM "direct_voter" 2.633 - JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.634 - LEFT JOIN "privilege" 2.635 - ON "privilege"."unit_id" = "unit_id_v" 2.636 - AND "privilege"."member_id" = "direct_voter"."member_id" 2.637 - WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.638 - "member"."active" = FALSE OR 2.639 - "privilege"."voting_right" ISNULL OR 2.640 - "privilege"."voting_right" = FALSE 2.641 - ) 2.642 - ) AS "subquery" 2.643 - WHERE "direct_voter"."issue_id" = "issue_id_p" 2.644 - AND "direct_voter"."member_id" = "subquery"."member_id"; 2.645 - -- consider delegations: 2.646 - UPDATE "direct_voter" SET "weight" = 1 2.647 - WHERE "issue_id" = "issue_id_p"; 2.648 - PERFORM "add_vote_delegations"("issue_id_p"); 2.649 - -- set voter count and mark issue as being calculated: 2.650 - UPDATE "issue" SET 2.651 - "state" = 'calculation', 2.652 - "closed" = now(), 2.653 - "voter_count" = ( 2.654 - SELECT coalesce(sum("weight"), 0) 2.655 - FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.656 - ) 2.657 - WHERE "id" = "issue_id_p"; 2.658 - -- materialize battle_view: 2.659 - -- NOTE: "closed" column of issue must be set at this point 2.660 - DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.661 - INSERT INTO "battle" ( 2.662 - "issue_id", 2.663 - "winning_initiative_id", "losing_initiative_id", 2.664 - "count" 2.665 - ) SELECT 2.666 - "issue_id", 2.667 - "winning_initiative_id", "losing_initiative_id", 2.668 - "count" 2.669 - FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.670 - -- copy "positive_votes" and "negative_votes" from "battle" table: 2.671 - UPDATE "initiative" SET 2.672 - "positive_votes" = "battle_win"."count", 2.673 - "negative_votes" = "battle_lose"."count" 2.674 - FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.675 - WHERE 2.676 - "battle_win"."issue_id" = "issue_id_p" AND 2.677 - "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.678 - "battle_win"."losing_initiative_id" ISNULL AND 2.679 - "battle_lose"."issue_id" = "issue_id_p" AND 2.680 - "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.681 - "battle_lose"."winning_initiative_id" ISNULL; 2.682 - END; 2.683 - $$; 2.684 - 2.685 -DROP FUNCTION "array_init_string"(INTEGER); 2.686 -DROP FUNCTION "square_matrix_init_string"(INTEGER); 2.687 - 2.688 -CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 2.689 - RETURNS VOID 2.690 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.691 - DECLARE 2.692 - "issue_row" "issue"%ROWTYPE; 2.693 - "policy_row" "policy"%ROWTYPE; 2.694 - "dimension_v" INTEGER; 2.695 - "vote_matrix" INT4[][]; -- absolute votes 2.696 - "matrix" INT8[][]; -- defeat strength / best paths 2.697 - "i" INTEGER; 2.698 - "j" INTEGER; 2.699 - "k" INTEGER; 2.700 - "battle_row" "battle"%ROWTYPE; 2.701 - "rank_ary" INT4[]; 2.702 - "rank_v" INT4; 2.703 - "done_v" INTEGER; 2.704 - "winners_ary" INTEGER[]; 2.705 - "initiative_id_v" "initiative"."id"%TYPE; 2.706 - BEGIN 2.707 - SELECT * INTO "issue_row" 2.708 - FROM "issue" WHERE "id" = "issue_id_p" 2.709 - FOR UPDATE; 2.710 - SELECT * INTO "policy_row" 2.711 - FROM "policy" WHERE "id" = "issue_row"."policy_id"; 2.712 - SELECT count(1) INTO "dimension_v" 2.713 - FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 2.714 - -- Create "vote_matrix" with absolute number of votes in pairwise 2.715 - -- comparison: 2.716 - "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 2.717 - "i" := 1; 2.718 - "j" := 2; 2.719 - FOR "battle_row" IN 2.720 - SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 2.721 - ORDER BY 2.722 - "winning_initiative_id" NULLS LAST, 2.723 - "losing_initiative_id" NULLS LAST 2.724 - LOOP 2.725 - "vote_matrix"["i"]["j"] := "battle_row"."count"; 2.726 - IF "j" = "dimension_v" THEN 2.727 - "i" := "i" + 1; 2.728 - "j" := 1; 2.729 - ELSE 2.730 - "j" := "j" + 1; 2.731 - IF "j" = "i" THEN 2.732 - "j" := "j" + 1; 2.733 - END IF; 2.734 - END IF; 2.735 - END LOOP; 2.736 - IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 2.737 - RAISE EXCEPTION 'Wrong battle count (should not happen)'; 2.738 - END IF; 2.739 - -- Store defeat strengths in "matrix" using "defeat_strength" 2.740 - -- function: 2.741 - "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 2.742 - "i" := 1; 2.743 - LOOP 2.744 - "j" := 1; 2.745 - LOOP 2.746 - IF "i" != "j" THEN 2.747 - "matrix"["i"]["j"] := "defeat_strength"( 2.748 - "vote_matrix"["i"]["j"], 2.749 - "vote_matrix"["j"]["i"] 2.750 - ); 2.751 - END IF; 2.752 - EXIT WHEN "j" = "dimension_v"; 2.753 - "j" := "j" + 1; 2.754 - END LOOP; 2.755 - EXIT WHEN "i" = "dimension_v"; 2.756 - "i" := "i" + 1; 2.757 - END LOOP; 2.758 - -- Find best paths: 2.759 - "i" := 1; 2.760 - LOOP 2.761 - "j" := 1; 2.762 - LOOP 2.763 - IF "i" != "j" THEN 2.764 - "k" := 1; 2.765 - LOOP 2.766 - IF "i" != "k" AND "j" != "k" THEN 2.767 - IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 2.768 - IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 2.769 - "matrix"["j"]["k"] := "matrix"["j"]["i"]; 2.770 - END IF; 2.771 - ELSE 2.772 - IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 2.773 - "matrix"["j"]["k"] := "matrix"["i"]["k"]; 2.774 - END IF; 2.775 - END IF; 2.776 - END IF; 2.777 - EXIT WHEN "k" = "dimension_v"; 2.778 - "k" := "k" + 1; 2.779 - END LOOP; 2.780 - END IF; 2.781 - EXIT WHEN "j" = "dimension_v"; 2.782 - "j" := "j" + 1; 2.783 - END LOOP; 2.784 - EXIT WHEN "i" = "dimension_v"; 2.785 - "i" := "i" + 1; 2.786 - END LOOP; 2.787 - -- Determine order of winners: 2.788 - "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 2.789 - "rank_v" := 1; 2.790 - "done_v" := 0; 2.791 - LOOP 2.792 - "winners_ary" := '{}'; 2.793 - "i" := 1; 2.794 - LOOP 2.795 - IF "rank_ary"["i"] ISNULL THEN 2.796 - "j" := 1; 2.797 - LOOP 2.798 - IF 2.799 - "i" != "j" AND 2.800 - "rank_ary"["j"] ISNULL AND 2.801 - "matrix"["j"]["i"] > "matrix"["i"]["j"] 2.802 - THEN 2.803 - -- someone else is better 2.804 - EXIT; 2.805 - END IF; 2.806 - IF "j" = "dimension_v" THEN 2.807 - -- noone is better 2.808 - "winners_ary" := "winners_ary" || "i"; 2.809 - EXIT; 2.810 - END IF; 2.811 - "j" := "j" + 1; 2.812 - END LOOP; 2.813 - END IF; 2.814 - EXIT WHEN "i" = "dimension_v"; 2.815 - "i" := "i" + 1; 2.816 - END LOOP; 2.817 - "i" := 1; 2.818 - LOOP 2.819 - "rank_ary"["winners_ary"["i"]] := "rank_v"; 2.820 - "done_v" := "done_v" + 1; 2.821 - EXIT WHEN "i" = array_upper("winners_ary", 1); 2.822 - "i" := "i" + 1; 2.823 - END LOOP; 2.824 - EXIT WHEN "done_v" = "dimension_v"; 2.825 - "rank_v" := "rank_v" + 1; 2.826 - END LOOP; 2.827 - -- write preliminary results: 2.828 - "i" := 1; 2.829 - FOR "initiative_id_v" IN 2.830 - SELECT "id" FROM "initiative" 2.831 - WHERE "issue_id" = "issue_id_p" AND "admitted" 2.832 - ORDER BY "id" 2.833 - LOOP 2.834 - UPDATE "initiative" SET 2.835 - "direct_majority" = 2.836 - CASE WHEN "policy_row"."direct_majority_strict" THEN 2.837 - "positive_votes" * "policy_row"."direct_majority_den" > 2.838 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.839 - ELSE 2.840 - "positive_votes" * "policy_row"."direct_majority_den" >= 2.841 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.842 - END 2.843 - AND "positive_votes" >= "policy_row"."direct_majority_positive" 2.844 - AND "issue_row"."voter_count"-"negative_votes" >= 2.845 - "policy_row"."direct_majority_non_negative", 2.846 - "indirect_majority" = 2.847 - CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.848 - "positive_votes" * "policy_row"."indirect_majority_den" > 2.849 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.850 - ELSE 2.851 - "positive_votes" * "policy_row"."indirect_majority_den" >= 2.852 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.853 - END 2.854 - AND "positive_votes" >= "policy_row"."indirect_majority_positive" 2.855 - AND "issue_row"."voter_count"-"negative_votes" >= 2.856 - "policy_row"."indirect_majority_non_negative", 2.857 - "schulze_rank" = "rank_ary"["i"], 2.858 - "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 2.859 - "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 2.860 - "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 2.861 - "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 2.862 - "winner" = FALSE 2.863 - WHERE "id" = "initiative_id_v"; 2.864 - "i" := "i" + 1; 2.865 - END LOOP; 2.866 - IF "i" != "dimension_v" THEN 2.867 - RAISE EXCEPTION 'Wrong winner count (should not happen)'; 2.868 - END IF; 2.869 - -- take indirect majorities into account: 2.870 - LOOP 2.871 - UPDATE "initiative" SET "indirect_majority" = TRUE 2.872 - FROM ( 2.873 - SELECT "new_initiative"."id" AS "initiative_id" 2.874 - FROM "initiative" "old_initiative" 2.875 - JOIN "initiative" "new_initiative" 2.876 - ON "new_initiative"."issue_id" = "issue_id_p" 2.877 - AND "new_initiative"."indirect_majority" = FALSE 2.878 - JOIN "battle" "battle_win" 2.879 - ON "battle_win"."issue_id" = "issue_id_p" 2.880 - AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 2.881 - AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 2.882 - JOIN "battle" "battle_lose" 2.883 - ON "battle_lose"."issue_id" = "issue_id_p" 2.884 - AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 2.885 - AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 2.886 - WHERE "old_initiative"."issue_id" = "issue_id_p" 2.887 - AND "old_initiative"."indirect_majority" = TRUE 2.888 - AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.889 - "battle_win"."count" * "policy_row"."indirect_majority_den" > 2.890 - "policy_row"."indirect_majority_num" * 2.891 - ("battle_win"."count"+"battle_lose"."count") 2.892 - ELSE 2.893 - "battle_win"."count" * "policy_row"."indirect_majority_den" >= 2.894 - "policy_row"."indirect_majority_num" * 2.895 - ("battle_win"."count"+"battle_lose"."count") 2.896 - END 2.897 - AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 2.898 - AND "issue_row"."voter_count"-"battle_lose"."count" >= 2.899 - "policy_row"."indirect_majority_non_negative" 2.900 - ) AS "subquery" 2.901 - WHERE "id" = "subquery"."initiative_id"; 2.902 - EXIT WHEN NOT FOUND; 2.903 - END LOOP; 2.904 - -- set "multistage_majority" for remaining matching initiatives: 2.905 - UPDATE "initiative" SET "multistage_majority" = TRUE 2.906 - FROM ( 2.907 - SELECT "losing_initiative"."id" AS "initiative_id" 2.908 - FROM "initiative" "losing_initiative" 2.909 - JOIN "initiative" "winning_initiative" 2.910 - ON "winning_initiative"."issue_id" = "issue_id_p" 2.911 - AND "winning_initiative"."admitted" 2.912 - JOIN "battle" "battle_win" 2.913 - ON "battle_win"."issue_id" = "issue_id_p" 2.914 - AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 2.915 - AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 2.916 - JOIN "battle" "battle_lose" 2.917 - ON "battle_lose"."issue_id" = "issue_id_p" 2.918 - AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 2.919 - AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 2.920 - WHERE "losing_initiative"."issue_id" = "issue_id_p" 2.921 - AND "losing_initiative"."admitted" 2.922 - AND "winning_initiative"."schulze_rank" < 2.923 - "losing_initiative"."schulze_rank" 2.924 - AND "battle_win"."count" > "battle_lose"."count" 2.925 - AND ( 2.926 - "battle_win"."count" > "winning_initiative"."positive_votes" OR 2.927 - "battle_lose"."count" < "losing_initiative"."negative_votes" ) 2.928 - ) AS "subquery" 2.929 - WHERE "id" = "subquery"."initiative_id"; 2.930 - -- mark eligible initiatives: 2.931 - UPDATE "initiative" SET "eligible" = TRUE 2.932 - WHERE "issue_id" = "issue_id_p" 2.933 - AND "initiative"."direct_majority" 2.934 - AND "initiative"."indirect_majority" 2.935 - AND "initiative"."better_than_status_quo" 2.936 - AND ( 2.937 - "policy_row"."no_multistage_majority" = FALSE OR 2.938 - "initiative"."multistage_majority" = FALSE ) 2.939 - AND ( 2.940 - "policy_row"."no_reverse_beat_path" = FALSE OR 2.941 - "initiative"."reverse_beat_path" = FALSE ); 2.942 - -- mark final winner: 2.943 - UPDATE "initiative" SET "winner" = TRUE 2.944 - FROM ( 2.945 - SELECT "id" AS "initiative_id" 2.946 - FROM "initiative" 2.947 - WHERE "issue_id" = "issue_id_p" AND "eligible" 2.948 - ORDER BY "schulze_rank", "id" 2.949 - LIMIT 1 2.950 - ) AS "subquery" 2.951 - WHERE "id" = "subquery"."initiative_id"; 2.952 - -- write (final) ranks: 2.953 - "rank_v" := 1; 2.954 - FOR "initiative_id_v" IN 2.955 - SELECT "id" 2.956 - FROM "initiative" 2.957 - WHERE "issue_id" = "issue_id_p" AND "admitted" 2.958 - ORDER BY 2.959 - "winner" DESC, 2.960 - ("direct_majority" AND "indirect_majority") DESC, 2.961 - "schulze_rank", 2.962 - "id" 2.963 - LOOP 2.964 - UPDATE "initiative" SET "rank" = "rank_v" 2.965 - WHERE "id" = "initiative_id_v"; 2.966 - "rank_v" := "rank_v" + 1; 2.967 - END LOOP; 2.968 - -- set schulze rank of status quo and mark issue as finished: 2.969 - UPDATE "issue" SET 2.970 - "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 2.971 - "state" = 2.972 - CASE WHEN EXISTS ( 2.973 - SELECT NULL FROM "initiative" 2.974 - WHERE "issue_id" = "issue_id_p" AND "winner" 2.975 - ) THEN 2.976 - 'finished_with_winner'::"issue_state" 2.977 - ELSE 2.978 - 'finished_without_winner'::"issue_state" 2.979 - END, 2.980 - "ranks_available" = TRUE 2.981 - WHERE "id" = "issue_id_p"; 2.982 - RETURN; 2.983 - END; 2.984 - $$; 2.985 - 2.986 -CREATE OR REPLACE FUNCTION "check_issue" 2.987 - ( "issue_id_p" "issue"."id"%TYPE ) 2.988 - RETURNS VOID 2.989 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.990 - DECLARE 2.991 - "issue_row" "issue"%ROWTYPE; 2.992 - "policy_row" "policy"%ROWTYPE; 2.993 - BEGIN 2.994 - PERFORM "lock_issue"("issue_id_p"); 2.995 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.996 - -- only process open issues: 2.997 - IF "issue_row"."closed" ISNULL THEN 2.998 - SELECT * INTO "policy_row" FROM "policy" 2.999 - WHERE "id" = "issue_row"."policy_id"; 2.1000 - -- create a snapshot, unless issue is already fully frozen: 2.1001 - IF "issue_row"."fully_frozen" ISNULL THEN 2.1002 - PERFORM "create_snapshot"("issue_id_p"); 2.1003 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1004 - END IF; 2.1005 - -- eventually close or accept issues, which have not been accepted: 2.1006 - IF "issue_row"."accepted" ISNULL THEN 2.1007 - IF EXISTS ( 2.1008 - SELECT NULL FROM "initiative" 2.1009 - WHERE "issue_id" = "issue_id_p" 2.1010 - AND "supporter_count" > 0 2.1011 - AND "supporter_count" * "policy_row"."issue_quorum_den" 2.1012 - >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.1013 - ) THEN 2.1014 - -- accept issues, if supporter count is high enough 2.1015 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1016 - -- NOTE: "issue_row" used later 2.1017 - "issue_row"."state" := 'discussion'; 2.1018 - "issue_row"."accepted" := now(); 2.1019 - UPDATE "issue" SET 2.1020 - "state" = "issue_row"."state", 2.1021 - "accepted" = "issue_row"."accepted" 2.1022 - WHERE "id" = "issue_row"."id"; 2.1023 - ELSIF 2.1024 - now() >= "issue_row"."created" + "issue_row"."admission_time" 2.1025 - THEN 2.1026 - -- close issues, if admission time has expired 2.1027 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.1028 - UPDATE "issue" SET 2.1029 - "state" = 'canceled_issue_not_accepted', 2.1030 - "closed" = now() 2.1031 - WHERE "id" = "issue_row"."id"; 2.1032 - END IF; 2.1033 - END IF; 2.1034 - -- eventually half freeze issues: 2.1035 - IF 2.1036 - -- NOTE: issue can't be closed at this point, if it has been accepted 2.1037 - "issue_row"."accepted" NOTNULL AND 2.1038 - "issue_row"."half_frozen" ISNULL 2.1039 - THEN 2.1040 - IF 2.1041 - now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.1042 - THEN 2.1043 - PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.1044 - -- NOTE: "issue_row" used later 2.1045 - "issue_row"."state" := 'verification'; 2.1046 - "issue_row"."half_frozen" := now(); 2.1047 - UPDATE "issue" SET 2.1048 - "state" = "issue_row"."state", 2.1049 - "half_frozen" = "issue_row"."half_frozen" 2.1050 - WHERE "id" = "issue_row"."id"; 2.1051 - END IF; 2.1052 - END IF; 2.1053 - -- close issues after some time, if all initiatives have been revoked: 2.1054 - IF 2.1055 - "issue_row"."closed" ISNULL AND 2.1056 - NOT EXISTS ( 2.1057 - -- all initiatives are revoked 2.1058 - SELECT NULL FROM "initiative" 2.1059 - WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.1060 - ) AND ( 2.1061 - -- and issue has not been accepted yet 2.1062 - "issue_row"."accepted" ISNULL OR 2.1063 - NOT EXISTS ( 2.1064 - -- or no initiatives have been revoked lately 2.1065 - SELECT NULL FROM "initiative" 2.1066 - WHERE "issue_id" = "issue_id_p" 2.1067 - AND now() < "revoked" + "issue_row"."verification_time" 2.1068 - ) OR ( 2.1069 - -- or verification time has elapsed 2.1070 - "issue_row"."half_frozen" NOTNULL AND 2.1071 - "issue_row"."fully_frozen" ISNULL AND 2.1072 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1073 - ) 2.1074 - ) 2.1075 - THEN 2.1076 - -- NOTE: "issue_row" used later 2.1077 - IF "issue_row"."accepted" ISNULL THEN 2.1078 - "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.1079 - ELSIF "issue_row"."half_frozen" ISNULL THEN 2.1080 - "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.1081 - ELSE 2.1082 - "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.1083 - END IF; 2.1084 - "issue_row"."closed" := now(); 2.1085 - UPDATE "issue" SET 2.1086 - "state" = "issue_row"."state", 2.1087 - "closed" = "issue_row"."closed" 2.1088 - WHERE "id" = "issue_row"."id"; 2.1089 - END IF; 2.1090 - -- fully freeze issue after verification time: 2.1091 - IF 2.1092 - "issue_row"."half_frozen" NOTNULL AND 2.1093 - "issue_row"."fully_frozen" ISNULL AND 2.1094 - "issue_row"."closed" ISNULL AND 2.1095 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.1096 - THEN 2.1097 - PERFORM "freeze_after_snapshot"("issue_id_p"); 2.1098 - -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.1099 - END IF; 2.1100 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.1101 - -- close issue by calling close_voting(...) after voting time: 2.1102 - IF 2.1103 - "issue_row"."closed" ISNULL AND 2.1104 - "issue_row"."fully_frozen" NOTNULL AND 2.1105 - now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.1106 - THEN 2.1107 - PERFORM "close_voting"("issue_id_p"); 2.1108 - -- calculate ranks will not consume much time and can be done now 2.1109 - PERFORM "calculate_ranks"("issue_id_p"); 2.1110 - END IF; 2.1111 - END IF; 2.1112 - RETURN; 2.1113 - END; 2.1114 - $$; 2.1115 - 2.1116 -CREATE OR REPLACE FUNCTION "check_everything"() 2.1117 - RETURNS VOID 2.1118 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1119 - DECLARE 2.1120 - "issue_id_v" "issue"."id"%TYPE; 2.1121 - BEGIN 2.1122 - PERFORM "check_activity"(); 2.1123 - PERFORM "calculate_member_counts"(); 2.1124 - FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 2.1125 - PERFORM "check_issue"("issue_id_v"); 2.1126 - END LOOP; 2.1127 - FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 2.1128 - PERFORM "calculate_ranks"("issue_id_v"); 2.1129 - END LOOP; 2.1130 - RETURN; 2.1131 - END; 2.1132 - $$; 2.1133 - 2.1134 -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.1135 - RETURNS VOID 2.1136 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1137 - BEGIN 2.1138 - UPDATE "member" SET 2.1139 - "last_login" = NULL, 2.1140 - "login" = NULL, 2.1141 - "password" = NULL, 2.1142 - "locked" = TRUE, 2.1143 - "active" = FALSE, 2.1144 - "notify_email" = NULL, 2.1145 - "notify_email_unconfirmed" = NULL, 2.1146 - "notify_email_secret" = NULL, 2.1147 - "notify_email_secret_expiry" = NULL, 2.1148 - "notify_email_lock_expiry" = NULL, 2.1149 - "password_reset_secret" = NULL, 2.1150 - "password_reset_secret_expiry" = NULL, 2.1151 - "organizational_unit" = NULL, 2.1152 - "internal_posts" = NULL, 2.1153 - "realname" = NULL, 2.1154 - "birthday" = NULL, 2.1155 - "address" = NULL, 2.1156 - "email" = NULL, 2.1157 - "xmpp_address" = NULL, 2.1158 - "website" = NULL, 2.1159 - "phone" = NULL, 2.1160 - "mobile_phone" = NULL, 2.1161 - "profession" = NULL, 2.1162 - "external_memberships" = NULL, 2.1163 - "external_posts" = NULL, 2.1164 - "statement" = NULL 2.1165 - WHERE "id" = "member_id_p"; 2.1166 - -- "text_search_data" is updated by triggers 2.1167 - DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.1168 - DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.1169 - DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.1170 - DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.1171 - DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.1172 - DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 2.1173 - DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.1174 - DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.1175 - DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 2.1176 - DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.1177 - DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.1178 - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.1179 - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.1180 - DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 2.1181 - DELETE FROM "direct_voter" USING "issue" 2.1182 - WHERE "direct_voter"."issue_id" = "issue"."id" 2.1183 - AND "issue"."closed" ISNULL 2.1184 - AND "member_id" = "member_id_p"; 2.1185 - RETURN; 2.1186 - END; 2.1187 - $$; 2.1188 - 2.1189 -CREATE OR REPLACE FUNCTION "delete_private_data"() 2.1190 - RETURNS VOID 2.1191 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.1192 - BEGIN 2.1193 - UPDATE "member" SET 2.1194 - "last_login" = NULL, 2.1195 - "login" = NULL, 2.1196 - "password" = NULL, 2.1197 - "notify_email" = NULL, 2.1198 - "notify_email_unconfirmed" = NULL, 2.1199 - "notify_email_secret" = NULL, 2.1200 - "notify_email_secret_expiry" = NULL, 2.1201 - "notify_email_lock_expiry" = NULL, 2.1202 - "password_reset_secret" = NULL, 2.1203 - "password_reset_secret_expiry" = NULL, 2.1204 - "organizational_unit" = NULL, 2.1205 - "internal_posts" = NULL, 2.1206 - "realname" = NULL, 2.1207 - "birthday" = NULL, 2.1208 - "address" = NULL, 2.1209 - "email" = NULL, 2.1210 - "xmpp_address" = NULL, 2.1211 - "website" = NULL, 2.1212 - "phone" = NULL, 2.1213 - "mobile_phone" = NULL, 2.1214 - "profession" = NULL, 2.1215 - "external_memberships" = NULL, 2.1216 - "external_posts" = NULL, 2.1217 - "statement" = NULL; 2.1218 - -- "text_search_data" is updated by triggers 2.1219 - DELETE FROM "invite_code"; 2.1220 - DELETE FROM "setting"; 2.1221 - DELETE FROM "setting_map"; 2.1222 - DELETE FROM "member_relation_setting"; 2.1223 - DELETE FROM "member_image"; 2.1224 - DELETE FROM "contact"; 2.1225 - DELETE FROM "ignored_member"; 2.1226 - DELETE FROM "area_setting"; 2.1227 - DELETE FROM "issue_setting"; 2.1228 - DELETE FROM "ignored_initiative"; 2.1229 - DELETE FROM "initiative_setting"; 2.1230 - DELETE FROM "suggestion_setting"; 2.1231 - DELETE FROM "non_voter"; 2.1232 - DELETE FROM "direct_voter" USING "issue" 2.1233 - WHERE "direct_voter"."issue_id" = "issue"."id" 2.1234 - AND "issue"."closed" ISNULL; 2.1235 - RETURN; 2.1236 - END; 2.1237 - $$; 2.1238 - 2.1239 -COMMIT; 2.1240 - 2.1241 -BEGIN; 2.1242 - 2.1243 -UPDATE "member" SET 2.1244 - "activated" = "created", 2.1245 - "last_activity" = CASE WHEN "active" THEN 2.1246 - coalesce("last_login"::DATE, now()) 2.1247 - ELSE 2.1248 - "last_login"::DATE 2.1249 - END; 2.1250 - 2.1251 -UPDATE "member" SET 2.1252 - "created" = "invite_code"."created", 2.1253 - "invite_code" = "invite_code"."code", 2.1254 - "admin_comment" = "invite_code"."comment" 2.1255 - FROM "invite_code" 2.1256 - WHERE "member"."id" = "invite_code"."member_id"; 2.1257 - 2.1258 -DROP TABLE "invite_code"; 2.1259 - 2.1260 -UPDATE "initiative" SET 2.1261 - "direct_majority" = "rank" NOTNULL, 2.1262 - "indirect_majority" = "rank" NOTNULL, 2.1263 - "schulze_rank" = "rank", 2.1264 - "better_than_status_quo" = "rank" NOTNULL, 2.1265 - "worse_than_status_quo" = "rank" ISNULL, 2.1266 - "reverse_beat_path" = "rank" ISNULL, 2.1267 - "multistage_majority" = "rank" ISNULL, 2.1268 - "eligible" = "rank" NOTNULL, 2.1269 - "winner" = ("rank" = 1) 2.1270 - FROM "issue" 2.1271 - WHERE "issue"."id" = "initiative"."issue_id" 2.1272 - AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 2.1273 - AND "initiative"."admitted"; 2.1274 - 2.1275 -UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" 2.1276 - FROM ( 2.1277 - SELECT 2.1278 - "issue"."id" AS "issue_id", 2.1279 - COALESCE(max("initiative"."rank") + 1, 1) AS "rank" 2.1280 - FROM "issue" JOIN "initiative" 2.1281 - ON "issue"."id" = "initiative"."issue_id" 2.1282 - WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 2.1283 - AND "initiative"."admitted" 2.1284 - GROUP BY "issue"."id" 2.1285 - ) AS "subquery" 2.1286 - WHERE "issue"."id" = "subquery"."issue_id"; 2.1287 - 2.1288 -CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) 2.1289 - RETURNS VOID 2.1290 - LANGUAGE 'plpgsql' AS $$ 2.1291 - DECLARE 2.1292 - "rank_v" INT4; 2.1293 - "initiative_id_v" INT4; 2.1294 - BEGIN 2.1295 - SELECT "status_quo_schulze_rank" INTO "rank_v" 2.1296 - FROM "issue" WHERE "id" = "issue_id_p"; 2.1297 - FOR "initiative_id_v" IN 2.1298 - SELECT "id" FROM "initiative" 2.1299 - WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL 2.1300 - ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC 2.1301 - LOOP 2.1302 - UPDATE "initiative" SET 2.1303 - "schulze_rank" = "rank_v" + 1, 2.1304 - "rank" = "rank_v" 2.1305 - WHERE "id" = "initiative_id_v"; 2.1306 - "rank_v" := "rank_v" + 1; 2.1307 - END LOOP; 2.1308 - RETURN; 2.1309 - END; 2.1310 - $$; 2.1311 - 2.1312 -SELECT "update__set_remaining_ranks"("id") FROM "issue" 2.1313 - WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); 2.1314 - 2.1315 -DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); 2.1316 - 2.1317 -UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" 2.1318 - FROM ( 2.1319 - SELECT DISTINCT ON ("suggestion"."id") 2.1320 - "suggestion"."id" AS "suggestion_id", 2.1321 - "draft"."id" AS "draft_id" 2.1322 - FROM "suggestion" JOIN "draft" 2.1323 - ON "suggestion"."initiative_id" = "draft"."initiative_id" 2.1324 - WHERE "draft"."created" <= "suggestion"."created" 2.1325 - ORDER BY "suggestion"."id", "draft"."created" DESC 2.1326 - ) AS "subquery" 2.1327 - WHERE "suggestion"."id" = "subquery"."suggestion_id"; 2.1328 - 2.1329 -COMMIT; 2.1330 - 2.1331 -ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" 2.1332 - CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); 2.1333 -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-v2.0.0.sql Sat Sep 10 22:39:06 2011 +0200 3.3 @@ -0,0 +1,1330 @@ 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 ('2.0.0', 2, 0, 0)) 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 UNIQUE, 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 "calculate_member_counts"() 3.336 + RETURNS VOID 3.337 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.338 + BEGIN 3.339 + LOCK TABLE "member" IN SHARE MODE; 3.340 + LOCK TABLE "member_count" IN EXCLUSIVE MODE; 3.341 + LOCK TABLE "unit" IN EXCLUSIVE MODE; 3.342 + LOCK TABLE "area" IN EXCLUSIVE MODE; 3.343 + LOCK TABLE "privilege" IN SHARE MODE; 3.344 + LOCK TABLE "membership" IN SHARE MODE; 3.345 + DELETE FROM "member_count"; 3.346 + INSERT INTO "member_count" ("total_count") 3.347 + SELECT "total_count" FROM "member_count_view"; 3.348 + UPDATE "unit" SET "member_count" = "view"."member_count" 3.349 + FROM "unit_member_count" AS "view" 3.350 + WHERE "view"."unit_id" = "unit"."id"; 3.351 + UPDATE "area" SET 3.352 + "direct_member_count" = "view"."direct_member_count", 3.353 + "member_weight" = "view"."member_weight" 3.354 + FROM "area_member_count" AS "view" 3.355 + WHERE "view"."area_id" = "area"."id"; 3.356 + RETURN; 3.357 + END; 3.358 + $$; 3.359 + 3.360 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 3.361 + ( "issue_id_p" "issue"."id"%TYPE ) 3.362 + RETURNS VOID 3.363 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.364 + DECLARE 3.365 + "member_id_v" "member"."id"%TYPE; 3.366 + BEGIN 3.367 + DELETE FROM "direct_interest_snapshot" 3.368 + WHERE "issue_id" = "issue_id_p" 3.369 + AND "event" = 'periodic'; 3.370 + DELETE FROM "delegating_interest_snapshot" 3.371 + WHERE "issue_id" = "issue_id_p" 3.372 + AND "event" = 'periodic'; 3.373 + DELETE FROM "direct_supporter_snapshot" 3.374 + WHERE "issue_id" = "issue_id_p" 3.375 + AND "event" = 'periodic'; 3.376 + INSERT INTO "direct_interest_snapshot" 3.377 + ("issue_id", "event", "member_id") 3.378 + SELECT 3.379 + "issue_id_p" AS "issue_id", 3.380 + 'periodic' AS "event", 3.381 + "member"."id" AS "member_id" 3.382 + FROM "issue" 3.383 + JOIN "area" ON "issue"."area_id" = "area"."id" 3.384 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 3.385 + JOIN "member" ON "interest"."member_id" = "member"."id" 3.386 + JOIN "privilege" 3.387 + ON "privilege"."unit_id" = "area"."unit_id" 3.388 + AND "privilege"."member_id" = "member"."id" 3.389 + WHERE "issue"."id" = "issue_id_p" 3.390 + AND "member"."active" AND "privilege"."voting_right"; 3.391 + FOR "member_id_v" IN 3.392 + SELECT "member_id" FROM "direct_interest_snapshot" 3.393 + WHERE "issue_id" = "issue_id_p" 3.394 + AND "event" = 'periodic' 3.395 + LOOP 3.396 + UPDATE "direct_interest_snapshot" SET 3.397 + "weight" = 1 + 3.398 + "weight_of_added_delegations_for_interest_snapshot"( 3.399 + "issue_id_p", 3.400 + "member_id_v", 3.401 + '{}' 3.402 + ) 3.403 + WHERE "issue_id" = "issue_id_p" 3.404 + AND "event" = 'periodic' 3.405 + AND "member_id" = "member_id_v"; 3.406 + END LOOP; 3.407 + INSERT INTO "direct_supporter_snapshot" 3.408 + ( "issue_id", "initiative_id", "event", "member_id", 3.409 + "informed", "satisfied" ) 3.410 + SELECT 3.411 + "issue_id_p" AS "issue_id", 3.412 + "initiative"."id" AS "initiative_id", 3.413 + 'periodic' AS "event", 3.414 + "supporter"."member_id" AS "member_id", 3.415 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 3.416 + NOT EXISTS ( 3.417 + SELECT NULL FROM "critical_opinion" 3.418 + WHERE "initiative_id" = "initiative"."id" 3.419 + AND "member_id" = "supporter"."member_id" 3.420 + ) AS "satisfied" 3.421 + FROM "initiative" 3.422 + JOIN "supporter" 3.423 + ON "supporter"."initiative_id" = "initiative"."id" 3.424 + JOIN "current_draft" 3.425 + ON "initiative"."id" = "current_draft"."initiative_id" 3.426 + JOIN "direct_interest_snapshot" 3.427 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 3.428 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 3.429 + AND "event" = 'periodic' 3.430 + WHERE "initiative"."issue_id" = "issue_id_p"; 3.431 + RETURN; 3.432 + END; 3.433 + $$; 3.434 + 3.435 +CREATE OR REPLACE FUNCTION "create_snapshot" 3.436 + ( "issue_id_p" "issue"."id"%TYPE ) 3.437 + RETURNS VOID 3.438 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.439 + DECLARE 3.440 + "initiative_id_v" "initiative"."id"%TYPE; 3.441 + "suggestion_id_v" "suggestion"."id"%TYPE; 3.442 + BEGIN 3.443 + PERFORM "lock_issue"("issue_id_p"); 3.444 + PERFORM "create_population_snapshot"("issue_id_p"); 3.445 + PERFORM "create_interest_snapshot"("issue_id_p"); 3.446 + UPDATE "issue" SET 3.447 + "snapshot" = now(), 3.448 + "latest_snapshot_event" = 'periodic', 3.449 + "population" = ( 3.450 + SELECT coalesce(sum("weight"), 0) 3.451 + FROM "direct_population_snapshot" 3.452 + WHERE "issue_id" = "issue_id_p" 3.453 + AND "event" = 'periodic' 3.454 + ) 3.455 + WHERE "id" = "issue_id_p"; 3.456 + FOR "initiative_id_v" IN 3.457 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 3.458 + LOOP 3.459 + UPDATE "initiative" SET 3.460 + "supporter_count" = ( 3.461 + SELECT coalesce(sum("di"."weight"), 0) 3.462 + FROM "direct_interest_snapshot" AS "di" 3.463 + JOIN "direct_supporter_snapshot" AS "ds" 3.464 + ON "di"."member_id" = "ds"."member_id" 3.465 + WHERE "di"."issue_id" = "issue_id_p" 3.466 + AND "di"."event" = 'periodic' 3.467 + AND "ds"."initiative_id" = "initiative_id_v" 3.468 + AND "ds"."event" = 'periodic' 3.469 + ), 3.470 + "informed_supporter_count" = ( 3.471 + SELECT coalesce(sum("di"."weight"), 0) 3.472 + FROM "direct_interest_snapshot" AS "di" 3.473 + JOIN "direct_supporter_snapshot" AS "ds" 3.474 + ON "di"."member_id" = "ds"."member_id" 3.475 + WHERE "di"."issue_id" = "issue_id_p" 3.476 + AND "di"."event" = 'periodic' 3.477 + AND "ds"."initiative_id" = "initiative_id_v" 3.478 + AND "ds"."event" = 'periodic' 3.479 + AND "ds"."informed" 3.480 + ), 3.481 + "satisfied_supporter_count" = ( 3.482 + SELECT coalesce(sum("di"."weight"), 0) 3.483 + FROM "direct_interest_snapshot" AS "di" 3.484 + JOIN "direct_supporter_snapshot" AS "ds" 3.485 + ON "di"."member_id" = "ds"."member_id" 3.486 + WHERE "di"."issue_id" = "issue_id_p" 3.487 + AND "di"."event" = 'periodic' 3.488 + AND "ds"."initiative_id" = "initiative_id_v" 3.489 + AND "ds"."event" = 'periodic' 3.490 + AND "ds"."satisfied" 3.491 + ), 3.492 + "satisfied_informed_supporter_count" = ( 3.493 + SELECT coalesce(sum("di"."weight"), 0) 3.494 + FROM "direct_interest_snapshot" AS "di" 3.495 + JOIN "direct_supporter_snapshot" AS "ds" 3.496 + ON "di"."member_id" = "ds"."member_id" 3.497 + WHERE "di"."issue_id" = "issue_id_p" 3.498 + AND "di"."event" = 'periodic' 3.499 + AND "ds"."initiative_id" = "initiative_id_v" 3.500 + AND "ds"."event" = 'periodic' 3.501 + AND "ds"."informed" 3.502 + AND "ds"."satisfied" 3.503 + ) 3.504 + WHERE "id" = "initiative_id_v"; 3.505 + FOR "suggestion_id_v" IN 3.506 + SELECT "id" FROM "suggestion" 3.507 + WHERE "initiative_id" = "initiative_id_v" 3.508 + LOOP 3.509 + UPDATE "suggestion" SET 3.510 + "minus2_unfulfilled_count" = ( 3.511 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.512 + FROM "issue" CROSS JOIN "opinion" 3.513 + JOIN "direct_interest_snapshot" AS "snapshot" 3.514 + ON "snapshot"."issue_id" = "issue"."id" 3.515 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.516 + AND "snapshot"."member_id" = "opinion"."member_id" 3.517 + WHERE "issue"."id" = "issue_id_p" 3.518 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.519 + AND "opinion"."degree" = -2 3.520 + AND "opinion"."fulfilled" = FALSE 3.521 + ), 3.522 + "minus2_fulfilled_count" = ( 3.523 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.524 + FROM "issue" CROSS JOIN "opinion" 3.525 + JOIN "direct_interest_snapshot" AS "snapshot" 3.526 + ON "snapshot"."issue_id" = "issue"."id" 3.527 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.528 + AND "snapshot"."member_id" = "opinion"."member_id" 3.529 + WHERE "issue"."id" = "issue_id_p" 3.530 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.531 + AND "opinion"."degree" = -2 3.532 + AND "opinion"."fulfilled" = TRUE 3.533 + ), 3.534 + "minus1_unfulfilled_count" = ( 3.535 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.536 + FROM "issue" CROSS JOIN "opinion" 3.537 + JOIN "direct_interest_snapshot" AS "snapshot" 3.538 + ON "snapshot"."issue_id" = "issue"."id" 3.539 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.540 + AND "snapshot"."member_id" = "opinion"."member_id" 3.541 + WHERE "issue"."id" = "issue_id_p" 3.542 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.543 + AND "opinion"."degree" = -1 3.544 + AND "opinion"."fulfilled" = FALSE 3.545 + ), 3.546 + "minus1_fulfilled_count" = ( 3.547 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.548 + FROM "issue" CROSS JOIN "opinion" 3.549 + JOIN "direct_interest_snapshot" AS "snapshot" 3.550 + ON "snapshot"."issue_id" = "issue"."id" 3.551 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.552 + AND "snapshot"."member_id" = "opinion"."member_id" 3.553 + WHERE "issue"."id" = "issue_id_p" 3.554 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.555 + AND "opinion"."degree" = -1 3.556 + AND "opinion"."fulfilled" = TRUE 3.557 + ), 3.558 + "plus1_unfulfilled_count" = ( 3.559 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.560 + FROM "issue" CROSS JOIN "opinion" 3.561 + JOIN "direct_interest_snapshot" AS "snapshot" 3.562 + ON "snapshot"."issue_id" = "issue"."id" 3.563 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.564 + AND "snapshot"."member_id" = "opinion"."member_id" 3.565 + WHERE "issue"."id" = "issue_id_p" 3.566 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.567 + AND "opinion"."degree" = 1 3.568 + AND "opinion"."fulfilled" = FALSE 3.569 + ), 3.570 + "plus1_fulfilled_count" = ( 3.571 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.572 + FROM "issue" CROSS JOIN "opinion" 3.573 + JOIN "direct_interest_snapshot" AS "snapshot" 3.574 + ON "snapshot"."issue_id" = "issue"."id" 3.575 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.576 + AND "snapshot"."member_id" = "opinion"."member_id" 3.577 + WHERE "issue"."id" = "issue_id_p" 3.578 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.579 + AND "opinion"."degree" = 1 3.580 + AND "opinion"."fulfilled" = TRUE 3.581 + ), 3.582 + "plus2_unfulfilled_count" = ( 3.583 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.584 + FROM "issue" CROSS JOIN "opinion" 3.585 + JOIN "direct_interest_snapshot" AS "snapshot" 3.586 + ON "snapshot"."issue_id" = "issue"."id" 3.587 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.588 + AND "snapshot"."member_id" = "opinion"."member_id" 3.589 + WHERE "issue"."id" = "issue_id_p" 3.590 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.591 + AND "opinion"."degree" = 2 3.592 + AND "opinion"."fulfilled" = FALSE 3.593 + ), 3.594 + "plus2_fulfilled_count" = ( 3.595 + SELECT coalesce(sum("snapshot"."weight"), 0) 3.596 + FROM "issue" CROSS JOIN "opinion" 3.597 + JOIN "direct_interest_snapshot" AS "snapshot" 3.598 + ON "snapshot"."issue_id" = "issue"."id" 3.599 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 3.600 + AND "snapshot"."member_id" = "opinion"."member_id" 3.601 + WHERE "issue"."id" = "issue_id_p" 3.602 + AND "opinion"."suggestion_id" = "suggestion_id_v" 3.603 + AND "opinion"."degree" = 2 3.604 + AND "opinion"."fulfilled" = TRUE 3.605 + ) 3.606 + WHERE "suggestion"."id" = "suggestion_id_v"; 3.607 + END LOOP; 3.608 + END LOOP; 3.609 + RETURN; 3.610 + END; 3.611 + $$; 3.612 + 3.613 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 3.614 + RETURNS VOID 3.615 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.616 + DECLARE 3.617 + "area_id_v" "area"."id"%TYPE; 3.618 + "unit_id_v" "unit"."id"%TYPE; 3.619 + "member_id_v" "member"."id"%TYPE; 3.620 + BEGIN 3.621 + PERFORM "lock_issue"("issue_id_p"); 3.622 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 3.623 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 3.624 + -- delete delegating votes (in cases of manual reset of issue state): 3.625 + DELETE FROM "delegating_voter" 3.626 + WHERE "issue_id" = "issue_id_p"; 3.627 + -- delete votes from non-privileged voters: 3.628 + DELETE FROM "direct_voter" 3.629 + USING ( 3.630 + SELECT 3.631 + "direct_voter"."member_id" 3.632 + FROM "direct_voter" 3.633 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 3.634 + LEFT JOIN "privilege" 3.635 + ON "privilege"."unit_id" = "unit_id_v" 3.636 + AND "privilege"."member_id" = "direct_voter"."member_id" 3.637 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 3.638 + "member"."active" = FALSE OR 3.639 + "privilege"."voting_right" ISNULL OR 3.640 + "privilege"."voting_right" = FALSE 3.641 + ) 3.642 + ) AS "subquery" 3.643 + WHERE "direct_voter"."issue_id" = "issue_id_p" 3.644 + AND "direct_voter"."member_id" = "subquery"."member_id"; 3.645 + -- consider delegations: 3.646 + UPDATE "direct_voter" SET "weight" = 1 3.647 + WHERE "issue_id" = "issue_id_p"; 3.648 + PERFORM "add_vote_delegations"("issue_id_p"); 3.649 + -- set voter count and mark issue as being calculated: 3.650 + UPDATE "issue" SET 3.651 + "state" = 'calculation', 3.652 + "closed" = now(), 3.653 + "voter_count" = ( 3.654 + SELECT coalesce(sum("weight"), 0) 3.655 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 3.656 + ) 3.657 + WHERE "id" = "issue_id_p"; 3.658 + -- materialize battle_view: 3.659 + -- NOTE: "closed" column of issue must be set at this point 3.660 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.661 + INSERT INTO "battle" ( 3.662 + "issue_id", 3.663 + "winning_initiative_id", "losing_initiative_id", 3.664 + "count" 3.665 + ) SELECT 3.666 + "issue_id", 3.667 + "winning_initiative_id", "losing_initiative_id", 3.668 + "count" 3.669 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 3.670 + -- copy "positive_votes" and "negative_votes" from "battle" table: 3.671 + UPDATE "initiative" SET 3.672 + "positive_votes" = "battle_win"."count", 3.673 + "negative_votes" = "battle_lose"."count" 3.674 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 3.675 + WHERE 3.676 + "battle_win"."issue_id" = "issue_id_p" AND 3.677 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 3.678 + "battle_win"."losing_initiative_id" ISNULL AND 3.679 + "battle_lose"."issue_id" = "issue_id_p" AND 3.680 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 3.681 + "battle_lose"."winning_initiative_id" ISNULL; 3.682 + END; 3.683 + $$; 3.684 + 3.685 +DROP FUNCTION "array_init_string"(INTEGER); 3.686 +DROP FUNCTION "square_matrix_init_string"(INTEGER); 3.687 + 3.688 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 3.689 + RETURNS VOID 3.690 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.691 + DECLARE 3.692 + "issue_row" "issue"%ROWTYPE; 3.693 + "policy_row" "policy"%ROWTYPE; 3.694 + "dimension_v" INTEGER; 3.695 + "vote_matrix" INT4[][]; -- absolute votes 3.696 + "matrix" INT8[][]; -- defeat strength / best paths 3.697 + "i" INTEGER; 3.698 + "j" INTEGER; 3.699 + "k" INTEGER; 3.700 + "battle_row" "battle"%ROWTYPE; 3.701 + "rank_ary" INT4[]; 3.702 + "rank_v" INT4; 3.703 + "done_v" INTEGER; 3.704 + "winners_ary" INTEGER[]; 3.705 + "initiative_id_v" "initiative"."id"%TYPE; 3.706 + BEGIN 3.707 + SELECT * INTO "issue_row" 3.708 + FROM "issue" WHERE "id" = "issue_id_p" 3.709 + FOR UPDATE; 3.710 + SELECT * INTO "policy_row" 3.711 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 3.712 + SELECT count(1) INTO "dimension_v" 3.713 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 3.714 + -- Create "vote_matrix" with absolute number of votes in pairwise 3.715 + -- comparison: 3.716 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 3.717 + "i" := 1; 3.718 + "j" := 2; 3.719 + FOR "battle_row" IN 3.720 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 3.721 + ORDER BY 3.722 + "winning_initiative_id" NULLS LAST, 3.723 + "losing_initiative_id" NULLS LAST 3.724 + LOOP 3.725 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 3.726 + IF "j" = "dimension_v" THEN 3.727 + "i" := "i" + 1; 3.728 + "j" := 1; 3.729 + ELSE 3.730 + "j" := "j" + 1; 3.731 + IF "j" = "i" THEN 3.732 + "j" := "j" + 1; 3.733 + END IF; 3.734 + END IF; 3.735 + END LOOP; 3.736 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 3.737 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 3.738 + END IF; 3.739 + -- Store defeat strengths in "matrix" using "defeat_strength" 3.740 + -- function: 3.741 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 3.742 + "i" := 1; 3.743 + LOOP 3.744 + "j" := 1; 3.745 + LOOP 3.746 + IF "i" != "j" THEN 3.747 + "matrix"["i"]["j"] := "defeat_strength"( 3.748 + "vote_matrix"["i"]["j"], 3.749 + "vote_matrix"["j"]["i"] 3.750 + ); 3.751 + END IF; 3.752 + EXIT WHEN "j" = "dimension_v"; 3.753 + "j" := "j" + 1; 3.754 + END LOOP; 3.755 + EXIT WHEN "i" = "dimension_v"; 3.756 + "i" := "i" + 1; 3.757 + END LOOP; 3.758 + -- Find best paths: 3.759 + "i" := 1; 3.760 + LOOP 3.761 + "j" := 1; 3.762 + LOOP 3.763 + IF "i" != "j" THEN 3.764 + "k" := 1; 3.765 + LOOP 3.766 + IF "i" != "k" AND "j" != "k" THEN 3.767 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 3.768 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 3.769 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 3.770 + END IF; 3.771 + ELSE 3.772 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 3.773 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 3.774 + END IF; 3.775 + END IF; 3.776 + END IF; 3.777 + EXIT WHEN "k" = "dimension_v"; 3.778 + "k" := "k" + 1; 3.779 + END LOOP; 3.780 + END IF; 3.781 + EXIT WHEN "j" = "dimension_v"; 3.782 + "j" := "j" + 1; 3.783 + END LOOP; 3.784 + EXIT WHEN "i" = "dimension_v"; 3.785 + "i" := "i" + 1; 3.786 + END LOOP; 3.787 + -- Determine order of winners: 3.788 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 3.789 + "rank_v" := 1; 3.790 + "done_v" := 0; 3.791 + LOOP 3.792 + "winners_ary" := '{}'; 3.793 + "i" := 1; 3.794 + LOOP 3.795 + IF "rank_ary"["i"] ISNULL THEN 3.796 + "j" := 1; 3.797 + LOOP 3.798 + IF 3.799 + "i" != "j" AND 3.800 + "rank_ary"["j"] ISNULL AND 3.801 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 3.802 + THEN 3.803 + -- someone else is better 3.804 + EXIT; 3.805 + END IF; 3.806 + IF "j" = "dimension_v" THEN 3.807 + -- noone is better 3.808 + "winners_ary" := "winners_ary" || "i"; 3.809 + EXIT; 3.810 + END IF; 3.811 + "j" := "j" + 1; 3.812 + END LOOP; 3.813 + END IF; 3.814 + EXIT WHEN "i" = "dimension_v"; 3.815 + "i" := "i" + 1; 3.816 + END LOOP; 3.817 + "i" := 1; 3.818 + LOOP 3.819 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 3.820 + "done_v" := "done_v" + 1; 3.821 + EXIT WHEN "i" = array_upper("winners_ary", 1); 3.822 + "i" := "i" + 1; 3.823 + END LOOP; 3.824 + EXIT WHEN "done_v" = "dimension_v"; 3.825 + "rank_v" := "rank_v" + 1; 3.826 + END LOOP; 3.827 + -- write preliminary results: 3.828 + "i" := 1; 3.829 + FOR "initiative_id_v" IN 3.830 + SELECT "id" FROM "initiative" 3.831 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.832 + ORDER BY "id" 3.833 + LOOP 3.834 + UPDATE "initiative" SET 3.835 + "direct_majority" = 3.836 + CASE WHEN "policy_row"."direct_majority_strict" THEN 3.837 + "positive_votes" * "policy_row"."direct_majority_den" > 3.838 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.839 + ELSE 3.840 + "positive_votes" * "policy_row"."direct_majority_den" >= 3.841 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.842 + END 3.843 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 3.844 + AND "issue_row"."voter_count"-"negative_votes" >= 3.845 + "policy_row"."direct_majority_non_negative", 3.846 + "indirect_majority" = 3.847 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.848 + "positive_votes" * "policy_row"."indirect_majority_den" > 3.849 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.850 + ELSE 3.851 + "positive_votes" * "policy_row"."indirect_majority_den" >= 3.852 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.853 + END 3.854 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 3.855 + AND "issue_row"."voter_count"-"negative_votes" >= 3.856 + "policy_row"."indirect_majority_non_negative", 3.857 + "schulze_rank" = "rank_ary"["i"], 3.858 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 3.859 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 3.860 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 3.861 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 3.862 + "winner" = FALSE 3.863 + WHERE "id" = "initiative_id_v"; 3.864 + "i" := "i" + 1; 3.865 + END LOOP; 3.866 + IF "i" != "dimension_v" THEN 3.867 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 3.868 + END IF; 3.869 + -- take indirect majorities into account: 3.870 + LOOP 3.871 + UPDATE "initiative" SET "indirect_majority" = TRUE 3.872 + FROM ( 3.873 + SELECT "new_initiative"."id" AS "initiative_id" 3.874 + FROM "initiative" "old_initiative" 3.875 + JOIN "initiative" "new_initiative" 3.876 + ON "new_initiative"."issue_id" = "issue_id_p" 3.877 + AND "new_initiative"."indirect_majority" = FALSE 3.878 + JOIN "battle" "battle_win" 3.879 + ON "battle_win"."issue_id" = "issue_id_p" 3.880 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 3.881 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 3.882 + JOIN "battle" "battle_lose" 3.883 + ON "battle_lose"."issue_id" = "issue_id_p" 3.884 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 3.885 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 3.886 + WHERE "old_initiative"."issue_id" = "issue_id_p" 3.887 + AND "old_initiative"."indirect_majority" = TRUE 3.888 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.889 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 3.890 + "policy_row"."indirect_majority_num" * 3.891 + ("battle_win"."count"+"battle_lose"."count") 3.892 + ELSE 3.893 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 3.894 + "policy_row"."indirect_majority_num" * 3.895 + ("battle_win"."count"+"battle_lose"."count") 3.896 + END 3.897 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 3.898 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 3.899 + "policy_row"."indirect_majority_non_negative" 3.900 + ) AS "subquery" 3.901 + WHERE "id" = "subquery"."initiative_id"; 3.902 + EXIT WHEN NOT FOUND; 3.903 + END LOOP; 3.904 + -- set "multistage_majority" for remaining matching initiatives: 3.905 + UPDATE "initiative" SET "multistage_majority" = TRUE 3.906 + FROM ( 3.907 + SELECT "losing_initiative"."id" AS "initiative_id" 3.908 + FROM "initiative" "losing_initiative" 3.909 + JOIN "initiative" "winning_initiative" 3.910 + ON "winning_initiative"."issue_id" = "issue_id_p" 3.911 + AND "winning_initiative"."admitted" 3.912 + JOIN "battle" "battle_win" 3.913 + ON "battle_win"."issue_id" = "issue_id_p" 3.914 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 3.915 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 3.916 + JOIN "battle" "battle_lose" 3.917 + ON "battle_lose"."issue_id" = "issue_id_p" 3.918 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 3.919 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 3.920 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 3.921 + AND "losing_initiative"."admitted" 3.922 + AND "winning_initiative"."schulze_rank" < 3.923 + "losing_initiative"."schulze_rank" 3.924 + AND "battle_win"."count" > "battle_lose"."count" 3.925 + AND ( 3.926 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 3.927 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 3.928 + ) AS "subquery" 3.929 + WHERE "id" = "subquery"."initiative_id"; 3.930 + -- mark eligible initiatives: 3.931 + UPDATE "initiative" SET "eligible" = TRUE 3.932 + WHERE "issue_id" = "issue_id_p" 3.933 + AND "initiative"."direct_majority" 3.934 + AND "initiative"."indirect_majority" 3.935 + AND "initiative"."better_than_status_quo" 3.936 + AND ( 3.937 + "policy_row"."no_multistage_majority" = FALSE OR 3.938 + "initiative"."multistage_majority" = FALSE ) 3.939 + AND ( 3.940 + "policy_row"."no_reverse_beat_path" = FALSE OR 3.941 + "initiative"."reverse_beat_path" = FALSE ); 3.942 + -- mark final winner: 3.943 + UPDATE "initiative" SET "winner" = TRUE 3.944 + FROM ( 3.945 + SELECT "id" AS "initiative_id" 3.946 + FROM "initiative" 3.947 + WHERE "issue_id" = "issue_id_p" AND "eligible" 3.948 + ORDER BY "schulze_rank", "id" 3.949 + LIMIT 1 3.950 + ) AS "subquery" 3.951 + WHERE "id" = "subquery"."initiative_id"; 3.952 + -- write (final) ranks: 3.953 + "rank_v" := 1; 3.954 + FOR "initiative_id_v" IN 3.955 + SELECT "id" 3.956 + FROM "initiative" 3.957 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.958 + ORDER BY 3.959 + "winner" DESC, 3.960 + ("direct_majority" AND "indirect_majority") DESC, 3.961 + "schulze_rank", 3.962 + "id" 3.963 + LOOP 3.964 + UPDATE "initiative" SET "rank" = "rank_v" 3.965 + WHERE "id" = "initiative_id_v"; 3.966 + "rank_v" := "rank_v" + 1; 3.967 + END LOOP; 3.968 + -- set schulze rank of status quo and mark issue as finished: 3.969 + UPDATE "issue" SET 3.970 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 3.971 + "state" = 3.972 + CASE WHEN EXISTS ( 3.973 + SELECT NULL FROM "initiative" 3.974 + WHERE "issue_id" = "issue_id_p" AND "winner" 3.975 + ) THEN 3.976 + 'finished_with_winner'::"issue_state" 3.977 + ELSE 3.978 + 'finished_without_winner'::"issue_state" 3.979 + END, 3.980 + "ranks_available" = TRUE 3.981 + WHERE "id" = "issue_id_p"; 3.982 + RETURN; 3.983 + END; 3.984 + $$; 3.985 + 3.986 +CREATE OR REPLACE FUNCTION "check_issue" 3.987 + ( "issue_id_p" "issue"."id"%TYPE ) 3.988 + RETURNS VOID 3.989 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.990 + DECLARE 3.991 + "issue_row" "issue"%ROWTYPE; 3.992 + "policy_row" "policy"%ROWTYPE; 3.993 + BEGIN 3.994 + PERFORM "lock_issue"("issue_id_p"); 3.995 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.996 + -- only process open issues: 3.997 + IF "issue_row"."closed" ISNULL THEN 3.998 + SELECT * INTO "policy_row" FROM "policy" 3.999 + WHERE "id" = "issue_row"."policy_id"; 3.1000 + -- create a snapshot, unless issue is already fully frozen: 3.1001 + IF "issue_row"."fully_frozen" ISNULL THEN 3.1002 + PERFORM "create_snapshot"("issue_id_p"); 3.1003 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.1004 + END IF; 3.1005 + -- eventually close or accept issues, which have not been accepted: 3.1006 + IF "issue_row"."accepted" ISNULL THEN 3.1007 + IF EXISTS ( 3.1008 + SELECT NULL FROM "initiative" 3.1009 + WHERE "issue_id" = "issue_id_p" 3.1010 + AND "supporter_count" > 0 3.1011 + AND "supporter_count" * "policy_row"."issue_quorum_den" 3.1012 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 3.1013 + ) THEN 3.1014 + -- accept issues, if supporter count is high enough 3.1015 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 3.1016 + -- NOTE: "issue_row" used later 3.1017 + "issue_row"."state" := 'discussion'; 3.1018 + "issue_row"."accepted" := now(); 3.1019 + UPDATE "issue" SET 3.1020 + "state" = "issue_row"."state", 3.1021 + "accepted" = "issue_row"."accepted" 3.1022 + WHERE "id" = "issue_row"."id"; 3.1023 + ELSIF 3.1024 + now() >= "issue_row"."created" + "issue_row"."admission_time" 3.1025 + THEN 3.1026 + -- close issues, if admission time has expired 3.1027 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 3.1028 + UPDATE "issue" SET 3.1029 + "state" = 'canceled_issue_not_accepted', 3.1030 + "closed" = now() 3.1031 + WHERE "id" = "issue_row"."id"; 3.1032 + END IF; 3.1033 + END IF; 3.1034 + -- eventually half freeze issues: 3.1035 + IF 3.1036 + -- NOTE: issue can't be closed at this point, if it has been accepted 3.1037 + "issue_row"."accepted" NOTNULL AND 3.1038 + "issue_row"."half_frozen" ISNULL 3.1039 + THEN 3.1040 + IF 3.1041 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 3.1042 + THEN 3.1043 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 3.1044 + -- NOTE: "issue_row" used later 3.1045 + "issue_row"."state" := 'verification'; 3.1046 + "issue_row"."half_frozen" := now(); 3.1047 + UPDATE "issue" SET 3.1048 + "state" = "issue_row"."state", 3.1049 + "half_frozen" = "issue_row"."half_frozen" 3.1050 + WHERE "id" = "issue_row"."id"; 3.1051 + END IF; 3.1052 + END IF; 3.1053 + -- close issues after some time, if all initiatives have been revoked: 3.1054 + IF 3.1055 + "issue_row"."closed" ISNULL AND 3.1056 + NOT EXISTS ( 3.1057 + -- all initiatives are revoked 3.1058 + SELECT NULL FROM "initiative" 3.1059 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 3.1060 + ) AND ( 3.1061 + -- and issue has not been accepted yet 3.1062 + "issue_row"."accepted" ISNULL OR 3.1063 + NOT EXISTS ( 3.1064 + -- or no initiatives have been revoked lately 3.1065 + SELECT NULL FROM "initiative" 3.1066 + WHERE "issue_id" = "issue_id_p" 3.1067 + AND now() < "revoked" + "issue_row"."verification_time" 3.1068 + ) OR ( 3.1069 + -- or verification time has elapsed 3.1070 + "issue_row"."half_frozen" NOTNULL AND 3.1071 + "issue_row"."fully_frozen" ISNULL AND 3.1072 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 3.1073 + ) 3.1074 + ) 3.1075 + THEN 3.1076 + -- NOTE: "issue_row" used later 3.1077 + IF "issue_row"."accepted" ISNULL THEN 3.1078 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 3.1079 + ELSIF "issue_row"."half_frozen" ISNULL THEN 3.1080 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 3.1081 + ELSE 3.1082 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 3.1083 + END IF; 3.1084 + "issue_row"."closed" := now(); 3.1085 + UPDATE "issue" SET 3.1086 + "state" = "issue_row"."state", 3.1087 + "closed" = "issue_row"."closed" 3.1088 + WHERE "id" = "issue_row"."id"; 3.1089 + END IF; 3.1090 + -- fully freeze issue after verification time: 3.1091 + IF 3.1092 + "issue_row"."half_frozen" NOTNULL AND 3.1093 + "issue_row"."fully_frozen" ISNULL AND 3.1094 + "issue_row"."closed" ISNULL AND 3.1095 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 3.1096 + THEN 3.1097 + PERFORM "freeze_after_snapshot"("issue_id_p"); 3.1098 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 3.1099 + END IF; 3.1100 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.1101 + -- close issue by calling close_voting(...) after voting time: 3.1102 + IF 3.1103 + "issue_row"."closed" ISNULL AND 3.1104 + "issue_row"."fully_frozen" NOTNULL AND 3.1105 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 3.1106 + THEN 3.1107 + PERFORM "close_voting"("issue_id_p"); 3.1108 + -- calculate ranks will not consume much time and can be done now 3.1109 + PERFORM "calculate_ranks"("issue_id_p"); 3.1110 + END IF; 3.1111 + END IF; 3.1112 + RETURN; 3.1113 + END; 3.1114 + $$; 3.1115 + 3.1116 +CREATE OR REPLACE FUNCTION "check_everything"() 3.1117 + RETURNS VOID 3.1118 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1119 + DECLARE 3.1120 + "issue_id_v" "issue"."id"%TYPE; 3.1121 + BEGIN 3.1122 + PERFORM "check_activity"(); 3.1123 + PERFORM "calculate_member_counts"(); 3.1124 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 3.1125 + PERFORM "check_issue"("issue_id_v"); 3.1126 + END LOOP; 3.1127 + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 3.1128 + PERFORM "calculate_ranks"("issue_id_v"); 3.1129 + END LOOP; 3.1130 + RETURN; 3.1131 + END; 3.1132 + $$; 3.1133 + 3.1134 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 3.1135 + RETURNS VOID 3.1136 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1137 + BEGIN 3.1138 + UPDATE "member" SET 3.1139 + "last_login" = NULL, 3.1140 + "login" = NULL, 3.1141 + "password" = NULL, 3.1142 + "locked" = TRUE, 3.1143 + "active" = FALSE, 3.1144 + "notify_email" = NULL, 3.1145 + "notify_email_unconfirmed" = NULL, 3.1146 + "notify_email_secret" = NULL, 3.1147 + "notify_email_secret_expiry" = NULL, 3.1148 + "notify_email_lock_expiry" = NULL, 3.1149 + "password_reset_secret" = NULL, 3.1150 + "password_reset_secret_expiry" = NULL, 3.1151 + "organizational_unit" = NULL, 3.1152 + "internal_posts" = NULL, 3.1153 + "realname" = NULL, 3.1154 + "birthday" = NULL, 3.1155 + "address" = NULL, 3.1156 + "email" = NULL, 3.1157 + "xmpp_address" = NULL, 3.1158 + "website" = NULL, 3.1159 + "phone" = NULL, 3.1160 + "mobile_phone" = NULL, 3.1161 + "profession" = NULL, 3.1162 + "external_memberships" = NULL, 3.1163 + "external_posts" = NULL, 3.1164 + "statement" = NULL 3.1165 + WHERE "id" = "member_id_p"; 3.1166 + -- "text_search_data" is updated by triggers 3.1167 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 3.1168 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 3.1169 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 3.1170 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 3.1171 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 3.1172 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 3.1173 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 3.1174 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 3.1175 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 3.1176 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 3.1177 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 3.1178 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 3.1179 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 3.1180 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 3.1181 + DELETE FROM "direct_voter" USING "issue" 3.1182 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.1183 + AND "issue"."closed" ISNULL 3.1184 + AND "member_id" = "member_id_p"; 3.1185 + RETURN; 3.1186 + END; 3.1187 + $$; 3.1188 + 3.1189 +CREATE OR REPLACE FUNCTION "delete_private_data"() 3.1190 + RETURNS VOID 3.1191 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1192 + BEGIN 3.1193 + UPDATE "member" SET 3.1194 + "last_login" = NULL, 3.1195 + "login" = NULL, 3.1196 + "password" = NULL, 3.1197 + "notify_email" = NULL, 3.1198 + "notify_email_unconfirmed" = NULL, 3.1199 + "notify_email_secret" = NULL, 3.1200 + "notify_email_secret_expiry" = NULL, 3.1201 + "notify_email_lock_expiry" = NULL, 3.1202 + "password_reset_secret" = NULL, 3.1203 + "password_reset_secret_expiry" = NULL, 3.1204 + "organizational_unit" = NULL, 3.1205 + "internal_posts" = NULL, 3.1206 + "realname" = NULL, 3.1207 + "birthday" = NULL, 3.1208 + "address" = NULL, 3.1209 + "email" = NULL, 3.1210 + "xmpp_address" = NULL, 3.1211 + "website" = NULL, 3.1212 + "phone" = NULL, 3.1213 + "mobile_phone" = NULL, 3.1214 + "profession" = NULL, 3.1215 + "external_memberships" = NULL, 3.1216 + "external_posts" = NULL, 3.1217 + "statement" = NULL; 3.1218 + -- "text_search_data" is updated by triggers 3.1219 + DELETE FROM "invite_code"; 3.1220 + DELETE FROM "setting"; 3.1221 + DELETE FROM "setting_map"; 3.1222 + DELETE FROM "member_relation_setting"; 3.1223 + DELETE FROM "member_image"; 3.1224 + DELETE FROM "contact"; 3.1225 + DELETE FROM "ignored_member"; 3.1226 + DELETE FROM "area_setting"; 3.1227 + DELETE FROM "issue_setting"; 3.1228 + DELETE FROM "ignored_initiative"; 3.1229 + DELETE FROM "initiative_setting"; 3.1230 + DELETE FROM "suggestion_setting"; 3.1231 + DELETE FROM "non_voter"; 3.1232 + DELETE FROM "direct_voter" USING "issue" 3.1233 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.1234 + AND "issue"."closed" ISNULL; 3.1235 + RETURN; 3.1236 + END; 3.1237 + $$; 3.1238 + 3.1239 +COMMIT; 3.1240 + 3.1241 +BEGIN; 3.1242 + 3.1243 +UPDATE "member" SET 3.1244 + "activated" = "created", 3.1245 + "last_activity" = CASE WHEN "active" THEN 3.1246 + coalesce("last_login"::DATE, now()) 3.1247 + ELSE 3.1248 + "last_login"::DATE 3.1249 + END; 3.1250 + 3.1251 +UPDATE "member" SET 3.1252 + "created" = "invite_code"."created", 3.1253 + "invite_code" = "invite_code"."code", 3.1254 + "admin_comment" = "invite_code"."comment" 3.1255 + FROM "invite_code" 3.1256 + WHERE "member"."id" = "invite_code"."member_id"; 3.1257 + 3.1258 +DROP TABLE "invite_code"; 3.1259 + 3.1260 +UPDATE "initiative" SET 3.1261 + "direct_majority" = "rank" NOTNULL, 3.1262 + "indirect_majority" = "rank" NOTNULL, 3.1263 + "schulze_rank" = "rank", 3.1264 + "better_than_status_quo" = "rank" NOTNULL, 3.1265 + "worse_than_status_quo" = "rank" ISNULL, 3.1266 + "reverse_beat_path" = "rank" ISNULL, 3.1267 + "multistage_majority" = "rank" ISNULL, 3.1268 + "eligible" = "rank" NOTNULL, 3.1269 + "winner" = ("rank" = 1) 3.1270 + FROM "issue" 3.1271 + WHERE "issue"."id" = "initiative"."issue_id" 3.1272 + AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 3.1273 + AND "initiative"."admitted"; 3.1274 + 3.1275 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" 3.1276 + FROM ( 3.1277 + SELECT 3.1278 + "issue"."id" AS "issue_id", 3.1279 + COALESCE(max("initiative"."rank") + 1, 1) AS "rank" 3.1280 + FROM "issue" JOIN "initiative" 3.1281 + ON "issue"."id" = "initiative"."issue_id" 3.1282 + WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') 3.1283 + AND "initiative"."admitted" 3.1284 + GROUP BY "issue"."id" 3.1285 + ) AS "subquery" 3.1286 + WHERE "issue"."id" = "subquery"."issue_id"; 3.1287 + 3.1288 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) 3.1289 + RETURNS VOID 3.1290 + LANGUAGE 'plpgsql' AS $$ 3.1291 + DECLARE 3.1292 + "rank_v" INT4; 3.1293 + "initiative_id_v" INT4; 3.1294 + BEGIN 3.1295 + SELECT "status_quo_schulze_rank" INTO "rank_v" 3.1296 + FROM "issue" WHERE "id" = "issue_id_p"; 3.1297 + FOR "initiative_id_v" IN 3.1298 + SELECT "id" FROM "initiative" 3.1299 + WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL 3.1300 + ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC 3.1301 + LOOP 3.1302 + UPDATE "initiative" SET 3.1303 + "schulze_rank" = "rank_v" + 1, 3.1304 + "rank" = "rank_v" 3.1305 + WHERE "id" = "initiative_id_v"; 3.1306 + "rank_v" := "rank_v" + 1; 3.1307 + END LOOP; 3.1308 + RETURN; 3.1309 + END; 3.1310 + $$; 3.1311 + 3.1312 +SELECT "update__set_remaining_ranks"("id") FROM "issue" 3.1313 + WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); 3.1314 + 3.1315 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); 3.1316 + 3.1317 +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" 3.1318 + FROM ( 3.1319 + SELECT DISTINCT ON ("suggestion"."id") 3.1320 + "suggestion"."id" AS "suggestion_id", 3.1321 + "draft"."id" AS "draft_id" 3.1322 + FROM "suggestion" JOIN "draft" 3.1323 + ON "suggestion"."initiative_id" = "draft"."initiative_id" 3.1324 + WHERE "draft"."created" <= "suggestion"."created" 3.1325 + ORDER BY "suggestion"."id", "draft"."created" DESC 3.1326 + ) AS "subquery" 3.1327 + WHERE "suggestion"."id" = "subquery"."suggestion_id"; 3.1328 + 3.1329 +COMMIT; 3.1330 + 3.1331 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" 3.1332 + CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); 3.1333 +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;