liquid_feedback_core
annotate update/core-update.v1.4.0-v2.0.0.sql @ 222:cd5fa6c46e13
Changes in event system:
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
author | jbe |
---|---|
date | Sat Feb 25 15:13:01 2012 +0100 (2012-02-25) |
parents | 5d89f5fcdcfc |
children | 8423373fbadd |
rev | line source |
---|---|
jbe@178 | 1 SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing"; |
jbe@178 | 2 |
jbe@177 | 3 BEGIN; |
jbe@177 | 4 |
jbe@177 | 5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@193 | 6 SELECT * FROM (VALUES ('2.0.0', 2, 0, 0)) |
jbe@177 | 7 AS "subquery"("string", "major", "minor", "revision"); |
jbe@177 | 8 |
jbe@182 | 9 ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE; |
jbe@182 | 10 ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT; |
jbe@182 | 11 ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ; |
jbe@184 | 12 ALTER TABLE "member" ADD COLUMN "last_activity" DATE; |
jbe@184 | 13 ALTER TABLE "member" DROP COLUMN "last_login_public"; |
jbe@182 | 14 ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; |
jbe@177 | 15 ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; |
jbe@182 | 16 |
jbe@182 | 17 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; |
jbe@182 | 18 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; |
jbe@186 | 19 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes'; |
jbe@184 | 20 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members'; |
jbe@184 | 21 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; |
jbe@184 | 22 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".'; |
jbe@177 | 23 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; |
jbe@177 | 24 |
jbe@185 | 25 CREATE TYPE "application_access_level" AS ENUM |
jbe@185 | 26 ('member', 'full', 'pseudonymous', 'anonymous'); |
jbe@185 | 27 |
jbe@185 | 28 COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API'; |
jbe@185 | 29 |
jbe@185 | 30 CREATE TABLE "member_application" ( |
jbe@185 | 31 "id" SERIAL8 PRIMARY KEY, |
jbe@185 | 32 UNIQUE ("member_id", "name"), |
jbe@185 | 33 "member_id" INT4 NOT NULL REFERENCES "member" ("id") |
jbe@185 | 34 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@185 | 35 "name" TEXT NOT NULL, |
jbe@185 | 36 "comment" TEXT, |
jbe@185 | 37 "access_level" "application_access_level" NOT NULL, |
jbe@190 | 38 "key" TEXT NOT NULL UNIQUE, |
jbe@185 | 39 "last_usage" TIMESTAMPTZ ); |
jbe@185 | 40 |
jbe@185 | 41 COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API'; |
jbe@185 | 42 |
jbe@177 | 43 CREATE TABLE "rendered_member_statement" ( |
jbe@177 | 44 PRIMARY KEY ("member_id", "format"), |
jbe@177 | 45 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@177 | 46 "format" TEXT, |
jbe@177 | 47 "content" TEXT NOT NULL ); |
jbe@177 | 48 |
jbe@177 | 49 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)'; |
jbe@177 | 50 |
jbe@183 | 51 DROP VIEW "expired_session"; |
jbe@183 | 52 DROP TABLE "session"; |
jbe@183 | 53 |
jbe@177 | 54 ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; |
jbe@177 | 55 ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; |
jbe@177 | 56 ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; |
jbe@177 | 57 ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; |
jbe@177 | 58 ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; |
jbe@177 | 59 ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; |
jbe@177 | 60 ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; |
jbe@177 | 61 ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; |
jbe@177 | 62 ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; |
jbe@177 | 63 ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; |
jbe@177 | 64 ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; |
jbe@177 | 65 ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; |
jbe@177 | 66 |
jbe@177 | 67 UPDATE "policy" SET |
jbe@177 | 68 "direct_majority_num" = "majority_num", |
jbe@177 | 69 "direct_majority_den" = "majority_den", |
jbe@177 | 70 "direct_majority_strict" = "majority_strict", |
jbe@177 | 71 "indirect_majority_num" = "majority_num", |
jbe@177 | 72 "indirect_majority_den" = "majority_den", |
jbe@177 | 73 "indirect_majority_strict" = "majority_strict"; |
jbe@177 | 74 |
jbe@177 | 75 ALTER TABLE "policy" DROP COLUMN "majority_num"; |
jbe@177 | 76 ALTER TABLE "policy" DROP COLUMN "majority_den"; |
jbe@177 | 77 ALTER TABLE "policy" DROP COLUMN "majority_strict"; |
jbe@177 | 78 |
jbe@177 | 79 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; |
jbe@177 | 80 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; |
jbe@177 | 81 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.'; |
jbe@177 | 82 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; |
jbe@177 | 83 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'; |
jbe@177 | 84 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'; |
jbe@177 | 85 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'; |
jbe@177 | 86 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.'; |
jbe@177 | 87 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'; |
jbe@177 | 88 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'; |
jbe@177 | 89 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.'; |
jbe@177 | 90 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").'; |
jbe@177 | 91 |
jbe@177 | 92 ALTER TABLE "area" DROP COLUMN "autoreject_weight"; |
jbe@177 | 93 |
jbe@177 | 94 DROP VIEW "open_issue"; |
jbe@177 | 95 DROP VIEW "issue_with_ranks_missing"; |
jbe@177 | 96 |
jbe@177 | 97 ALTER TABLE "issue" DROP COLUMN "vote_now"; |
jbe@177 | 98 ALTER TABLE "issue" DROP COLUMN "vote_later"; |
jbe@177 | 99 ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; |
jbe@177 | 100 |
jbe@177 | 101 CREATE VIEW "open_issue" AS |
jbe@177 | 102 SELECT * FROM "issue" WHERE "closed" ISNULL; |
jbe@177 | 103 |
jbe@177 | 104 COMMENT ON VIEW "open_issue" IS 'All open issues'; |
jbe@177 | 105 |
jbe@177 | 106 CREATE VIEW "issue_with_ranks_missing" AS |
jbe@177 | 107 SELECT * FROM "issue" |
jbe@177 | 108 WHERE "fully_frozen" NOTNULL |
jbe@177 | 109 AND "closed" NOTNULL |
jbe@177 | 110 AND "ranks_available" = FALSE; |
jbe@177 | 111 |
jbe@177 | 112 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; |
jbe@177 | 113 |
jbe@177 | 114 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.'; |
jbe@177 | 115 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; |
jbe@177 | 116 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; |
jbe@177 | 117 |
jbe@179 | 118 DROP VIEW "battle_view"; |
jbe@179 | 119 |
jbe@180 | 120 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; |
jbe@180 | 121 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; |
jbe@180 | 122 ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; |
jbe@180 | 123 |
jbe@178 | 124 ALTER TABLE "initiative" DROP COLUMN "agreed"; |
jbe@177 | 125 ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; |
jbe@177 | 126 ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; |
jbe@177 | 127 ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; |
jbe@177 | 128 ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; |
jbe@177 | 129 ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; |
jbe@177 | 130 ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; |
jbe@177 | 131 ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; |
jbe@177 | 132 ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; |
jbe@177 | 133 ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; |
jbe@177 | 134 |
jbe@177 | 135 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( |
jbe@177 | 136 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR |
jbe@177 | 137 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND |
jbe@177 | 138 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND |
jbe@177 | 139 "schulze_rank" ISNULL AND |
jbe@177 | 140 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND |
jbe@177 | 141 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND |
jbe@177 | 142 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); |
jbe@177 | 143 ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); |
jbe@177 | 144 ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( |
jbe@177 | 145 "eligible" = FALSE OR |
jbe@177 | 146 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); |
jbe@177 | 147 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); |
jbe@177 | 148 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); |
jbe@177 | 149 ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); |
jbe@177 | 150 ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); |
jbe@177 | 151 |
jbe@177 | 152 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"'; |
jbe@177 | 153 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; |
jbe@177 | 154 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; |
jbe@177 | 155 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; |
jbe@177 | 156 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; |
jbe@177 | 157 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'; |
jbe@177 | 158 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'; |
jbe@177 | 159 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; |
jbe@177 | 160 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; |
jbe@177 | 161 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'; |
jbe@177 | 162 |
jbe@177 | 163 ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; |
jbe@177 | 164 ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; |
jbe@177 | 165 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( |
jbe@177 | 166 "winning_initiative_id" != "losing_initiative_id" OR |
jbe@177 | 167 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR |
jbe@177 | 168 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); |
jbe@177 | 169 |
jbe@177 | 170 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); |
jbe@177 | 171 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; |
jbe@177 | 172 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; |
jbe@177 | 173 |
jbe@177 | 174 ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; |
jbe@177 | 175 ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; |
jbe@177 | 176 ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; |
jbe@177 | 177 ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; |
jbe@177 | 178 |
jbe@177 | 179 DROP TRIGGER "update_text_search_data" ON "suggestion"; |
jbe@177 | 180 |
jbe@177 | 181 CREATE TRIGGER "update_text_search_data" |
jbe@177 | 182 BEFORE INSERT OR UPDATE ON "suggestion" |
jbe@177 | 183 FOR EACH ROW EXECUTE PROCEDURE |
jbe@177 | 184 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', |
jbe@177 | 185 "name", "content"); |
jbe@177 | 186 |
jbe@177 | 187 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")'; |
jbe@177 | 188 |
jbe@177 | 189 CREATE TABLE "rendered_suggestion" ( |
jbe@177 | 190 PRIMARY KEY ("suggestion_id", "format"), |
jbe@177 | 191 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@177 | 192 "format" TEXT, |
jbe@177 | 193 "content" TEXT NOT NULL ); |
jbe@177 | 194 |
jbe@177 | 195 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)'; |
jbe@177 | 196 |
jbe@183 | 197 DROP TABLE "invite_code_unit"; |
jbe@183 | 198 |
jbe@177 | 199 DROP VIEW "area_member_count"; |
jbe@177 | 200 |
jbe@177 | 201 ALTER TABLE "membership" DROP COLUMN "autoreject"; |
jbe@177 | 202 |
jbe@177 | 203 ALTER TABLE "interest" DROP COLUMN "autoreject"; |
jbe@177 | 204 ALTER TABLE "interest" DROP COLUMN "voting_requested"; |
jbe@177 | 205 |
jbe@177 | 206 ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; |
jbe@177 | 207 ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; |
jbe@177 | 208 |
jbe@177 | 209 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")'; |
jbe@177 | 210 |
jbe@177 | 211 ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; |
jbe@177 | 212 ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; |
jbe@177 | 213 |
jbe@177 | 214 DROP TRIGGER "default_for_draft_id" ON "supporter"; |
jbe@177 | 215 DROP FUNCTION "supporter_default_for_draft_id_trigger"(); |
jbe@177 | 216 |
jbe@177 | 217 CREATE FUNCTION "default_for_draft_id_trigger"() |
jbe@177 | 218 RETURNS TRIGGER |
jbe@177 | 219 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 220 BEGIN |
jbe@177 | 221 IF NEW."draft_id" ISNULL THEN |
jbe@177 | 222 SELECT "id" INTO NEW."draft_id" FROM "current_draft" |
jbe@177 | 223 WHERE "initiative_id" = NEW."initiative_id"; |
jbe@177 | 224 END IF; |
jbe@177 | 225 RETURN NEW; |
jbe@177 | 226 END; |
jbe@177 | 227 $$; |
jbe@177 | 228 |
jbe@177 | 229 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" |
jbe@177 | 230 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); |
jbe@177 | 231 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" |
jbe@177 | 232 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); |
jbe@177 | 233 |
jbe@177 | 234 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; |
jbe@177 | 235 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'; |
jbe@177 | 236 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'; |
jbe@177 | 237 |
jbe@177 | 238 CREATE VIEW "area_member_count" AS |
jbe@177 | 239 SELECT |
jbe@177 | 240 "area"."id" AS "area_id", |
jbe@177 | 241 count("member"."id") AS "direct_member_count", |
jbe@177 | 242 coalesce( |
jbe@177 | 243 sum( |
jbe@177 | 244 CASE WHEN "member"."id" NOTNULL THEN |
jbe@177 | 245 "membership_weight"("area"."id", "member"."id") |
jbe@177 | 246 ELSE 0 END |
jbe@177 | 247 ) |
jbe@177 | 248 ) AS "member_weight" |
jbe@177 | 249 FROM "area" |
jbe@177 | 250 LEFT JOIN "membership" |
jbe@177 | 251 ON "area"."id" = "membership"."area_id" |
jbe@177 | 252 LEFT JOIN "privilege" |
jbe@177 | 253 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@177 | 254 AND "privilege"."member_id" = "membership"."member_id" |
jbe@177 | 255 AND "privilege"."voting_right" |
jbe@177 | 256 LEFT JOIN "member" |
jbe@177 | 257 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! |
jbe@177 | 258 AND "member"."active" |
jbe@177 | 259 GROUP BY "area"."id"; |
jbe@177 | 260 |
jbe@177 | 261 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; |
jbe@177 | 262 |
jbe@177 | 263 CREATE VIEW "battle_participant" AS |
jbe@177 | 264 SELECT "initiative"."id", "initiative"."issue_id" |
jbe@177 | 265 FROM "issue" JOIN "initiative" |
jbe@177 | 266 ON "issue"."id" = "initiative"."issue_id" |
jbe@177 | 267 WHERE "initiative"."admitted" |
jbe@177 | 268 UNION ALL |
jbe@177 | 269 SELECT NULL, "id" AS "issue_id" |
jbe@177 | 270 FROM "issue"; |
jbe@177 | 271 |
jbe@177 | 272 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; |
jbe@177 | 273 |
jbe@179 | 274 CREATE VIEW "battle_view" AS |
jbe@177 | 275 SELECT |
jbe@177 | 276 "issue"."id" AS "issue_id", |
jbe@177 | 277 "winning_initiative"."id" AS "winning_initiative_id", |
jbe@177 | 278 "losing_initiative"."id" AS "losing_initiative_id", |
jbe@177 | 279 sum( |
jbe@177 | 280 CASE WHEN |
jbe@177 | 281 coalesce("better_vote"."grade", 0) > |
jbe@177 | 282 coalesce("worse_vote"."grade", 0) |
jbe@177 | 283 THEN "direct_voter"."weight" ELSE 0 END |
jbe@177 | 284 ) AS "count" |
jbe@177 | 285 FROM "issue" |
jbe@177 | 286 LEFT JOIN "direct_voter" |
jbe@177 | 287 ON "issue"."id" = "direct_voter"."issue_id" |
jbe@177 | 288 JOIN "battle_participant" AS "winning_initiative" |
jbe@177 | 289 ON "issue"."id" = "winning_initiative"."issue_id" |
jbe@177 | 290 JOIN "battle_participant" AS "losing_initiative" |
jbe@177 | 291 ON "issue"."id" = "losing_initiative"."issue_id" |
jbe@177 | 292 LEFT JOIN "vote" AS "better_vote" |
jbe@177 | 293 ON "direct_voter"."member_id" = "better_vote"."member_id" |
jbe@177 | 294 AND "winning_initiative"."id" = "better_vote"."initiative_id" |
jbe@177 | 295 LEFT JOIN "vote" AS "worse_vote" |
jbe@177 | 296 ON "direct_voter"."member_id" = "worse_vote"."member_id" |
jbe@177 | 297 AND "losing_initiative"."id" = "worse_vote"."initiative_id" |
jbe@177 | 298 WHERE "issue"."closed" NOTNULL |
jbe@177 | 299 AND "issue"."cleaned" ISNULL |
jbe@177 | 300 AND ( |
jbe@177 | 301 "winning_initiative"."id" != "losing_initiative"."id" OR |
jbe@177 | 302 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR |
jbe@177 | 303 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) |
jbe@177 | 304 GROUP BY |
jbe@177 | 305 "issue"."id", |
jbe@177 | 306 "winning_initiative"."id", |
jbe@177 | 307 "losing_initiative"."id"; |
jbe@177 | 308 |
jbe@177 | 309 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'; |
jbe@177 | 310 |
jbe@184 | 311 DROP FUNCTION "check_last_login"(); |
jbe@184 | 312 |
jbe@184 | 313 CREATE FUNCTION "check_activity"() |
jbe@184 | 314 RETURNS VOID |
jbe@184 | 315 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@184 | 316 DECLARE |
jbe@184 | 317 "system_setting_row" "system_setting"%ROWTYPE; |
jbe@184 | 318 BEGIN |
jbe@184 | 319 SELECT * INTO "system_setting_row" FROM "system_setting"; |
jbe@184 | 320 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; |
jbe@184 | 321 IF "system_setting_row"."member_ttl" NOTNULL THEN |
jbe@184 | 322 UPDATE "member" SET "active" = FALSE |
jbe@184 | 323 WHERE "active" = TRUE |
jbe@184 | 324 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; |
jbe@184 | 325 END IF; |
jbe@184 | 326 RETURN; |
jbe@184 | 327 END; |
jbe@184 | 328 $$; |
jbe@184 | 329 |
jbe@184 | 330 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".'; |
jbe@184 | 331 |
jbe@189 | 332 CREATE OR REPLACE FUNCTION "calculate_member_counts"() |
jbe@189 | 333 RETURNS VOID |
jbe@189 | 334 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@189 | 335 BEGIN |
jbe@189 | 336 LOCK TABLE "member" IN SHARE MODE; |
jbe@189 | 337 LOCK TABLE "member_count" IN EXCLUSIVE MODE; |
jbe@189 | 338 LOCK TABLE "unit" IN EXCLUSIVE MODE; |
jbe@189 | 339 LOCK TABLE "area" IN EXCLUSIVE MODE; |
jbe@189 | 340 LOCK TABLE "privilege" IN SHARE MODE; |
jbe@189 | 341 LOCK TABLE "membership" IN SHARE MODE; |
jbe@189 | 342 DELETE FROM "member_count"; |
jbe@189 | 343 INSERT INTO "member_count" ("total_count") |
jbe@189 | 344 SELECT "total_count" FROM "member_count_view"; |
jbe@189 | 345 UPDATE "unit" SET "member_count" = "view"."member_count" |
jbe@189 | 346 FROM "unit_member_count" AS "view" |
jbe@189 | 347 WHERE "view"."unit_id" = "unit"."id"; |
jbe@189 | 348 UPDATE "area" SET |
jbe@189 | 349 "direct_member_count" = "view"."direct_member_count", |
jbe@189 | 350 "member_weight" = "view"."member_weight" |
jbe@189 | 351 FROM "area_member_count" AS "view" |
jbe@189 | 352 WHERE "view"."area_id" = "area"."id"; |
jbe@189 | 353 RETURN; |
jbe@189 | 354 END; |
jbe@189 | 355 $$; |
jbe@189 | 356 |
jbe@177 | 357 CREATE OR REPLACE FUNCTION "create_interest_snapshot" |
jbe@177 | 358 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@177 | 359 RETURNS VOID |
jbe@177 | 360 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 361 DECLARE |
jbe@177 | 362 "member_id_v" "member"."id"%TYPE; |
jbe@177 | 363 BEGIN |
jbe@177 | 364 DELETE FROM "direct_interest_snapshot" |
jbe@177 | 365 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 366 AND "event" = 'periodic'; |
jbe@177 | 367 DELETE FROM "delegating_interest_snapshot" |
jbe@177 | 368 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 369 AND "event" = 'periodic'; |
jbe@177 | 370 DELETE FROM "direct_supporter_snapshot" |
jbe@177 | 371 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 372 AND "event" = 'periodic'; |
jbe@177 | 373 INSERT INTO "direct_interest_snapshot" |
jbe@177 | 374 ("issue_id", "event", "member_id") |
jbe@177 | 375 SELECT |
jbe@177 | 376 "issue_id_p" AS "issue_id", |
jbe@177 | 377 'periodic' AS "event", |
jbe@177 | 378 "member"."id" AS "member_id" |
jbe@177 | 379 FROM "issue" |
jbe@177 | 380 JOIN "area" ON "issue"."area_id" = "area"."id" |
jbe@177 | 381 JOIN "interest" ON "issue"."id" = "interest"."issue_id" |
jbe@177 | 382 JOIN "member" ON "interest"."member_id" = "member"."id" |
jbe@177 | 383 JOIN "privilege" |
jbe@177 | 384 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@177 | 385 AND "privilege"."member_id" = "member"."id" |
jbe@177 | 386 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 387 AND "member"."active" AND "privilege"."voting_right"; |
jbe@177 | 388 FOR "member_id_v" IN |
jbe@177 | 389 SELECT "member_id" FROM "direct_interest_snapshot" |
jbe@177 | 390 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 391 AND "event" = 'periodic' |
jbe@177 | 392 LOOP |
jbe@177 | 393 UPDATE "direct_interest_snapshot" SET |
jbe@177 | 394 "weight" = 1 + |
jbe@177 | 395 "weight_of_added_delegations_for_interest_snapshot"( |
jbe@177 | 396 "issue_id_p", |
jbe@177 | 397 "member_id_v", |
jbe@177 | 398 '{}' |
jbe@177 | 399 ) |
jbe@177 | 400 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 401 AND "event" = 'periodic' |
jbe@177 | 402 AND "member_id" = "member_id_v"; |
jbe@177 | 403 END LOOP; |
jbe@177 | 404 INSERT INTO "direct_supporter_snapshot" |
jbe@177 | 405 ( "issue_id", "initiative_id", "event", "member_id", |
jbe@177 | 406 "informed", "satisfied" ) |
jbe@177 | 407 SELECT |
jbe@177 | 408 "issue_id_p" AS "issue_id", |
jbe@177 | 409 "initiative"."id" AS "initiative_id", |
jbe@177 | 410 'periodic' AS "event", |
jbe@177 | 411 "supporter"."member_id" AS "member_id", |
jbe@177 | 412 "supporter"."draft_id" = "current_draft"."id" AS "informed", |
jbe@177 | 413 NOT EXISTS ( |
jbe@177 | 414 SELECT NULL FROM "critical_opinion" |
jbe@177 | 415 WHERE "initiative_id" = "initiative"."id" |
jbe@177 | 416 AND "member_id" = "supporter"."member_id" |
jbe@177 | 417 ) AS "satisfied" |
jbe@177 | 418 FROM "initiative" |
jbe@177 | 419 JOIN "supporter" |
jbe@177 | 420 ON "supporter"."initiative_id" = "initiative"."id" |
jbe@177 | 421 JOIN "current_draft" |
jbe@177 | 422 ON "initiative"."id" = "current_draft"."initiative_id" |
jbe@177 | 423 JOIN "direct_interest_snapshot" |
jbe@177 | 424 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" |
jbe@177 | 425 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" |
jbe@177 | 426 AND "event" = 'periodic' |
jbe@177 | 427 WHERE "initiative"."issue_id" = "issue_id_p"; |
jbe@177 | 428 RETURN; |
jbe@177 | 429 END; |
jbe@177 | 430 $$; |
jbe@177 | 431 |
jbe@177 | 432 CREATE OR REPLACE FUNCTION "create_snapshot" |
jbe@177 | 433 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@177 | 434 RETURNS VOID |
jbe@177 | 435 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 436 DECLARE |
jbe@177 | 437 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@177 | 438 "suggestion_id_v" "suggestion"."id"%TYPE; |
jbe@177 | 439 BEGIN |
jbe@177 | 440 PERFORM "lock_issue"("issue_id_p"); |
jbe@177 | 441 PERFORM "create_population_snapshot"("issue_id_p"); |
jbe@177 | 442 PERFORM "create_interest_snapshot"("issue_id_p"); |
jbe@177 | 443 UPDATE "issue" SET |
jbe@177 | 444 "snapshot" = now(), |
jbe@177 | 445 "latest_snapshot_event" = 'periodic', |
jbe@177 | 446 "population" = ( |
jbe@177 | 447 SELECT coalesce(sum("weight"), 0) |
jbe@177 | 448 FROM "direct_population_snapshot" |
jbe@177 | 449 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 450 AND "event" = 'periodic' |
jbe@177 | 451 ) |
jbe@177 | 452 WHERE "id" = "issue_id_p"; |
jbe@177 | 453 FOR "initiative_id_v" IN |
jbe@177 | 454 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" |
jbe@177 | 455 LOOP |
jbe@177 | 456 UPDATE "initiative" SET |
jbe@177 | 457 "supporter_count" = ( |
jbe@177 | 458 SELECT coalesce(sum("di"."weight"), 0) |
jbe@177 | 459 FROM "direct_interest_snapshot" AS "di" |
jbe@177 | 460 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@177 | 461 ON "di"."member_id" = "ds"."member_id" |
jbe@177 | 462 WHERE "di"."issue_id" = "issue_id_p" |
jbe@177 | 463 AND "di"."event" = 'periodic' |
jbe@177 | 464 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@177 | 465 AND "ds"."event" = 'periodic' |
jbe@177 | 466 ), |
jbe@177 | 467 "informed_supporter_count" = ( |
jbe@177 | 468 SELECT coalesce(sum("di"."weight"), 0) |
jbe@177 | 469 FROM "direct_interest_snapshot" AS "di" |
jbe@177 | 470 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@177 | 471 ON "di"."member_id" = "ds"."member_id" |
jbe@177 | 472 WHERE "di"."issue_id" = "issue_id_p" |
jbe@177 | 473 AND "di"."event" = 'periodic' |
jbe@177 | 474 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@177 | 475 AND "ds"."event" = 'periodic' |
jbe@177 | 476 AND "ds"."informed" |
jbe@177 | 477 ), |
jbe@177 | 478 "satisfied_supporter_count" = ( |
jbe@177 | 479 SELECT coalesce(sum("di"."weight"), 0) |
jbe@177 | 480 FROM "direct_interest_snapshot" AS "di" |
jbe@177 | 481 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@177 | 482 ON "di"."member_id" = "ds"."member_id" |
jbe@177 | 483 WHERE "di"."issue_id" = "issue_id_p" |
jbe@177 | 484 AND "di"."event" = 'periodic' |
jbe@177 | 485 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@177 | 486 AND "ds"."event" = 'periodic' |
jbe@177 | 487 AND "ds"."satisfied" |
jbe@177 | 488 ), |
jbe@177 | 489 "satisfied_informed_supporter_count" = ( |
jbe@177 | 490 SELECT coalesce(sum("di"."weight"), 0) |
jbe@177 | 491 FROM "direct_interest_snapshot" AS "di" |
jbe@177 | 492 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@177 | 493 ON "di"."member_id" = "ds"."member_id" |
jbe@177 | 494 WHERE "di"."issue_id" = "issue_id_p" |
jbe@177 | 495 AND "di"."event" = 'periodic' |
jbe@177 | 496 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@177 | 497 AND "ds"."event" = 'periodic' |
jbe@177 | 498 AND "ds"."informed" |
jbe@177 | 499 AND "ds"."satisfied" |
jbe@177 | 500 ) |
jbe@177 | 501 WHERE "id" = "initiative_id_v"; |
jbe@177 | 502 FOR "suggestion_id_v" IN |
jbe@177 | 503 SELECT "id" FROM "suggestion" |
jbe@177 | 504 WHERE "initiative_id" = "initiative_id_v" |
jbe@177 | 505 LOOP |
jbe@177 | 506 UPDATE "suggestion" SET |
jbe@177 | 507 "minus2_unfulfilled_count" = ( |
jbe@177 | 508 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 509 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 510 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 511 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 512 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 513 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 514 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 515 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 516 AND "opinion"."degree" = -2 |
jbe@177 | 517 AND "opinion"."fulfilled" = FALSE |
jbe@177 | 518 ), |
jbe@177 | 519 "minus2_fulfilled_count" = ( |
jbe@177 | 520 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 521 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 522 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 523 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 524 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 525 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 526 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 527 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 528 AND "opinion"."degree" = -2 |
jbe@177 | 529 AND "opinion"."fulfilled" = TRUE |
jbe@177 | 530 ), |
jbe@177 | 531 "minus1_unfulfilled_count" = ( |
jbe@177 | 532 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 533 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 534 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 535 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 536 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 537 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 538 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 539 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 540 AND "opinion"."degree" = -1 |
jbe@177 | 541 AND "opinion"."fulfilled" = FALSE |
jbe@177 | 542 ), |
jbe@177 | 543 "minus1_fulfilled_count" = ( |
jbe@177 | 544 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 545 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 546 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 547 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 548 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 549 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 550 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 551 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 552 AND "opinion"."degree" = -1 |
jbe@177 | 553 AND "opinion"."fulfilled" = TRUE |
jbe@177 | 554 ), |
jbe@177 | 555 "plus1_unfulfilled_count" = ( |
jbe@177 | 556 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 557 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 558 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 559 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 560 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 561 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 562 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 563 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 564 AND "opinion"."degree" = 1 |
jbe@177 | 565 AND "opinion"."fulfilled" = FALSE |
jbe@177 | 566 ), |
jbe@177 | 567 "plus1_fulfilled_count" = ( |
jbe@177 | 568 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 569 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 570 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 571 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 572 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 573 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 574 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 575 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 576 AND "opinion"."degree" = 1 |
jbe@177 | 577 AND "opinion"."fulfilled" = TRUE |
jbe@177 | 578 ), |
jbe@177 | 579 "plus2_unfulfilled_count" = ( |
jbe@177 | 580 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 581 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 582 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 583 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 584 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 585 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 586 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 587 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 588 AND "opinion"."degree" = 2 |
jbe@177 | 589 AND "opinion"."fulfilled" = FALSE |
jbe@177 | 590 ), |
jbe@177 | 591 "plus2_fulfilled_count" = ( |
jbe@177 | 592 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@177 | 593 FROM "issue" CROSS JOIN "opinion" |
jbe@177 | 594 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@177 | 595 ON "snapshot"."issue_id" = "issue"."id" |
jbe@177 | 596 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@177 | 597 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@177 | 598 WHERE "issue"."id" = "issue_id_p" |
jbe@177 | 599 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@177 | 600 AND "opinion"."degree" = 2 |
jbe@177 | 601 AND "opinion"."fulfilled" = TRUE |
jbe@177 | 602 ) |
jbe@177 | 603 WHERE "suggestion"."id" = "suggestion_id_v"; |
jbe@177 | 604 END LOOP; |
jbe@177 | 605 END LOOP; |
jbe@177 | 606 RETURN; |
jbe@177 | 607 END; |
jbe@177 | 608 $$; |
jbe@177 | 609 |
jbe@177 | 610 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@177 | 611 RETURNS VOID |
jbe@177 | 612 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 613 DECLARE |
jbe@177 | 614 "area_id_v" "area"."id"%TYPE; |
jbe@177 | 615 "unit_id_v" "unit"."id"%TYPE; |
jbe@177 | 616 "member_id_v" "member"."id"%TYPE; |
jbe@177 | 617 BEGIN |
jbe@177 | 618 PERFORM "lock_issue"("issue_id_p"); |
jbe@177 | 619 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@177 | 620 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; |
jbe@177 | 621 -- delete delegating votes (in cases of manual reset of issue state): |
jbe@177 | 622 DELETE FROM "delegating_voter" |
jbe@177 | 623 WHERE "issue_id" = "issue_id_p"; |
jbe@177 | 624 -- delete votes from non-privileged voters: |
jbe@177 | 625 DELETE FROM "direct_voter" |
jbe@177 | 626 USING ( |
jbe@177 | 627 SELECT |
jbe@177 | 628 "direct_voter"."member_id" |
jbe@177 | 629 FROM "direct_voter" |
jbe@177 | 630 JOIN "member" ON "direct_voter"."member_id" = "member"."id" |
jbe@177 | 631 LEFT JOIN "privilege" |
jbe@177 | 632 ON "privilege"."unit_id" = "unit_id_v" |
jbe@177 | 633 AND "privilege"."member_id" = "direct_voter"."member_id" |
jbe@177 | 634 WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( |
jbe@177 | 635 "member"."active" = FALSE OR |
jbe@177 | 636 "privilege"."voting_right" ISNULL OR |
jbe@177 | 637 "privilege"."voting_right" = FALSE |
jbe@177 | 638 ) |
jbe@177 | 639 ) AS "subquery" |
jbe@177 | 640 WHERE "direct_voter"."issue_id" = "issue_id_p" |
jbe@177 | 641 AND "direct_voter"."member_id" = "subquery"."member_id"; |
jbe@177 | 642 -- consider delegations: |
jbe@177 | 643 UPDATE "direct_voter" SET "weight" = 1 |
jbe@177 | 644 WHERE "issue_id" = "issue_id_p"; |
jbe@177 | 645 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@177 | 646 -- set voter count and mark issue as being calculated: |
jbe@177 | 647 UPDATE "issue" SET |
jbe@177 | 648 "state" = 'calculation', |
jbe@177 | 649 "closed" = now(), |
jbe@177 | 650 "voter_count" = ( |
jbe@177 | 651 SELECT coalesce(sum("weight"), 0) |
jbe@177 | 652 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@177 | 653 ) |
jbe@177 | 654 WHERE "id" = "issue_id_p"; |
jbe@177 | 655 -- materialize battle_view: |
jbe@177 | 656 -- NOTE: "closed" column of issue must be set at this point |
jbe@177 | 657 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@177 | 658 INSERT INTO "battle" ( |
jbe@177 | 659 "issue_id", |
jbe@177 | 660 "winning_initiative_id", "losing_initiative_id", |
jbe@177 | 661 "count" |
jbe@177 | 662 ) SELECT |
jbe@177 | 663 "issue_id", |
jbe@177 | 664 "winning_initiative_id", "losing_initiative_id", |
jbe@177 | 665 "count" |
jbe@177 | 666 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@177 | 667 -- copy "positive_votes" and "negative_votes" from "battle" table: |
jbe@177 | 668 UPDATE "initiative" SET |
jbe@177 | 669 "positive_votes" = "battle_win"."count", |
jbe@177 | 670 "negative_votes" = "battle_lose"."count" |
jbe@177 | 671 FROM "battle" AS "battle_win", "battle" AS "battle_lose" |
jbe@177 | 672 WHERE |
jbe@177 | 673 "battle_win"."issue_id" = "issue_id_p" AND |
jbe@177 | 674 "battle_win"."winning_initiative_id" = "initiative"."id" AND |
jbe@177 | 675 "battle_win"."losing_initiative_id" ISNULL AND |
jbe@177 | 676 "battle_lose"."issue_id" = "issue_id_p" AND |
jbe@177 | 677 "battle_lose"."losing_initiative_id" = "initiative"."id" AND |
jbe@177 | 678 "battle_lose"."winning_initiative_id" ISNULL; |
jbe@177 | 679 END; |
jbe@177 | 680 $$; |
jbe@177 | 681 |
jbe@177 | 682 DROP FUNCTION "array_init_string"(INTEGER); |
jbe@177 | 683 DROP FUNCTION "square_matrix_init_string"(INTEGER); |
jbe@177 | 684 |
jbe@177 | 685 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) |
jbe@177 | 686 RETURNS VOID |
jbe@177 | 687 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 688 DECLARE |
jbe@177 | 689 "issue_row" "issue"%ROWTYPE; |
jbe@177 | 690 "policy_row" "policy"%ROWTYPE; |
jbe@177 | 691 "dimension_v" INTEGER; |
jbe@177 | 692 "vote_matrix" INT4[][]; -- absolute votes |
jbe@177 | 693 "matrix" INT8[][]; -- defeat strength / best paths |
jbe@177 | 694 "i" INTEGER; |
jbe@177 | 695 "j" INTEGER; |
jbe@177 | 696 "k" INTEGER; |
jbe@177 | 697 "battle_row" "battle"%ROWTYPE; |
jbe@177 | 698 "rank_ary" INT4[]; |
jbe@177 | 699 "rank_v" INT4; |
jbe@177 | 700 "done_v" INTEGER; |
jbe@177 | 701 "winners_ary" INTEGER[]; |
jbe@177 | 702 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@177 | 703 BEGIN |
jbe@177 | 704 SELECT * INTO "issue_row" |
jbe@177 | 705 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@177 | 706 FOR UPDATE; |
jbe@177 | 707 SELECT * INTO "policy_row" |
jbe@177 | 708 FROM "policy" WHERE "id" = "issue_row"."policy_id"; |
jbe@177 | 709 SELECT count(1) INTO "dimension_v" |
jbe@177 | 710 FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; |
jbe@177 | 711 -- Create "vote_matrix" with absolute number of votes in pairwise |
jbe@177 | 712 -- comparison: |
jbe@177 | 713 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); |
jbe@177 | 714 "i" := 1; |
jbe@177 | 715 "j" := 2; |
jbe@177 | 716 FOR "battle_row" IN |
jbe@177 | 717 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" |
jbe@177 | 718 ORDER BY |
jbe@177 | 719 "winning_initiative_id" NULLS LAST, |
jbe@177 | 720 "losing_initiative_id" NULLS LAST |
jbe@177 | 721 LOOP |
jbe@177 | 722 "vote_matrix"["i"]["j"] := "battle_row"."count"; |
jbe@177 | 723 IF "j" = "dimension_v" THEN |
jbe@177 | 724 "i" := "i" + 1; |
jbe@177 | 725 "j" := 1; |
jbe@177 | 726 ELSE |
jbe@177 | 727 "j" := "j" + 1; |
jbe@177 | 728 IF "j" = "i" THEN |
jbe@177 | 729 "j" := "j" + 1; |
jbe@177 | 730 END IF; |
jbe@177 | 731 END IF; |
jbe@177 | 732 END LOOP; |
jbe@177 | 733 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN |
jbe@177 | 734 RAISE EXCEPTION 'Wrong battle count (should not happen)'; |
jbe@177 | 735 END IF; |
jbe@177 | 736 -- Store defeat strengths in "matrix" using "defeat_strength" |
jbe@177 | 737 -- function: |
jbe@177 | 738 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); |
jbe@177 | 739 "i" := 1; |
jbe@177 | 740 LOOP |
jbe@177 | 741 "j" := 1; |
jbe@177 | 742 LOOP |
jbe@177 | 743 IF "i" != "j" THEN |
jbe@177 | 744 "matrix"["i"]["j"] := "defeat_strength"( |
jbe@177 | 745 "vote_matrix"["i"]["j"], |
jbe@177 | 746 "vote_matrix"["j"]["i"] |
jbe@177 | 747 ); |
jbe@177 | 748 END IF; |
jbe@177 | 749 EXIT WHEN "j" = "dimension_v"; |
jbe@177 | 750 "j" := "j" + 1; |
jbe@177 | 751 END LOOP; |
jbe@177 | 752 EXIT WHEN "i" = "dimension_v"; |
jbe@177 | 753 "i" := "i" + 1; |
jbe@177 | 754 END LOOP; |
jbe@177 | 755 -- Find best paths: |
jbe@177 | 756 "i" := 1; |
jbe@177 | 757 LOOP |
jbe@177 | 758 "j" := 1; |
jbe@177 | 759 LOOP |
jbe@177 | 760 IF "i" != "j" THEN |
jbe@177 | 761 "k" := 1; |
jbe@177 | 762 LOOP |
jbe@177 | 763 IF "i" != "k" AND "j" != "k" THEN |
jbe@177 | 764 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN |
jbe@177 | 765 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN |
jbe@177 | 766 "matrix"["j"]["k"] := "matrix"["j"]["i"]; |
jbe@177 | 767 END IF; |
jbe@177 | 768 ELSE |
jbe@177 | 769 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN |
jbe@177 | 770 "matrix"["j"]["k"] := "matrix"["i"]["k"]; |
jbe@177 | 771 END IF; |
jbe@177 | 772 END IF; |
jbe@177 | 773 END IF; |
jbe@177 | 774 EXIT WHEN "k" = "dimension_v"; |
jbe@177 | 775 "k" := "k" + 1; |
jbe@177 | 776 END LOOP; |
jbe@177 | 777 END IF; |
jbe@177 | 778 EXIT WHEN "j" = "dimension_v"; |
jbe@177 | 779 "j" := "j" + 1; |
jbe@177 | 780 END LOOP; |
jbe@177 | 781 EXIT WHEN "i" = "dimension_v"; |
jbe@177 | 782 "i" := "i" + 1; |
jbe@177 | 783 END LOOP; |
jbe@177 | 784 -- Determine order of winners: |
jbe@177 | 785 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); |
jbe@177 | 786 "rank_v" := 1; |
jbe@177 | 787 "done_v" := 0; |
jbe@177 | 788 LOOP |
jbe@177 | 789 "winners_ary" := '{}'; |
jbe@177 | 790 "i" := 1; |
jbe@177 | 791 LOOP |
jbe@177 | 792 IF "rank_ary"["i"] ISNULL THEN |
jbe@177 | 793 "j" := 1; |
jbe@177 | 794 LOOP |
jbe@177 | 795 IF |
jbe@177 | 796 "i" != "j" AND |
jbe@177 | 797 "rank_ary"["j"] ISNULL AND |
jbe@177 | 798 "matrix"["j"]["i"] > "matrix"["i"]["j"] |
jbe@177 | 799 THEN |
jbe@177 | 800 -- someone else is better |
jbe@177 | 801 EXIT; |
jbe@177 | 802 END IF; |
jbe@177 | 803 IF "j" = "dimension_v" THEN |
jbe@177 | 804 -- noone is better |
jbe@177 | 805 "winners_ary" := "winners_ary" || "i"; |
jbe@177 | 806 EXIT; |
jbe@177 | 807 END IF; |
jbe@177 | 808 "j" := "j" + 1; |
jbe@177 | 809 END LOOP; |
jbe@177 | 810 END IF; |
jbe@177 | 811 EXIT WHEN "i" = "dimension_v"; |
jbe@177 | 812 "i" := "i" + 1; |
jbe@177 | 813 END LOOP; |
jbe@177 | 814 "i" := 1; |
jbe@177 | 815 LOOP |
jbe@177 | 816 "rank_ary"["winners_ary"["i"]] := "rank_v"; |
jbe@177 | 817 "done_v" := "done_v" + 1; |
jbe@177 | 818 EXIT WHEN "i" = array_upper("winners_ary", 1); |
jbe@177 | 819 "i" := "i" + 1; |
jbe@177 | 820 END LOOP; |
jbe@177 | 821 EXIT WHEN "done_v" = "dimension_v"; |
jbe@177 | 822 "rank_v" := "rank_v" + 1; |
jbe@177 | 823 END LOOP; |
jbe@177 | 824 -- write preliminary results: |
jbe@177 | 825 "i" := 1; |
jbe@177 | 826 FOR "initiative_id_v" IN |
jbe@177 | 827 SELECT "id" FROM "initiative" |
jbe@177 | 828 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@177 | 829 ORDER BY "id" |
jbe@177 | 830 LOOP |
jbe@177 | 831 UPDATE "initiative" SET |
jbe@177 | 832 "direct_majority" = |
jbe@177 | 833 CASE WHEN "policy_row"."direct_majority_strict" THEN |
jbe@177 | 834 "positive_votes" * "policy_row"."direct_majority_den" > |
jbe@177 | 835 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@177 | 836 ELSE |
jbe@177 | 837 "positive_votes" * "policy_row"."direct_majority_den" >= |
jbe@177 | 838 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@177 | 839 END |
jbe@177 | 840 AND "positive_votes" >= "policy_row"."direct_majority_positive" |
jbe@177 | 841 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@177 | 842 "policy_row"."direct_majority_non_negative", |
jbe@177 | 843 "indirect_majority" = |
jbe@177 | 844 CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@177 | 845 "positive_votes" * "policy_row"."indirect_majority_den" > |
jbe@177 | 846 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@177 | 847 ELSE |
jbe@177 | 848 "positive_votes" * "policy_row"."indirect_majority_den" >= |
jbe@177 | 849 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@177 | 850 END |
jbe@177 | 851 AND "positive_votes" >= "policy_row"."indirect_majority_positive" |
jbe@177 | 852 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@177 | 853 "policy_row"."indirect_majority_non_negative", |
jbe@177 | 854 "schulze_rank" = "rank_ary"["i"], |
jbe@177 | 855 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], |
jbe@177 | 856 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], |
jbe@177 | 857 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], |
jbe@177 | 858 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, |
jbe@177 | 859 "winner" = FALSE |
jbe@177 | 860 WHERE "id" = "initiative_id_v"; |
jbe@177 | 861 "i" := "i" + 1; |
jbe@177 | 862 END LOOP; |
jbe@177 | 863 IF "i" != "dimension_v" THEN |
jbe@177 | 864 RAISE EXCEPTION 'Wrong winner count (should not happen)'; |
jbe@177 | 865 END IF; |
jbe@177 | 866 -- take indirect majorities into account: |
jbe@177 | 867 LOOP |
jbe@177 | 868 UPDATE "initiative" SET "indirect_majority" = TRUE |
jbe@177 | 869 FROM ( |
jbe@177 | 870 SELECT "new_initiative"."id" AS "initiative_id" |
jbe@177 | 871 FROM "initiative" "old_initiative" |
jbe@177 | 872 JOIN "initiative" "new_initiative" |
jbe@177 | 873 ON "new_initiative"."issue_id" = "issue_id_p" |
jbe@177 | 874 AND "new_initiative"."indirect_majority" = FALSE |
jbe@177 | 875 JOIN "battle" "battle_win" |
jbe@177 | 876 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@177 | 877 AND "battle_win"."winning_initiative_id" = "new_initiative"."id" |
jbe@177 | 878 AND "battle_win"."losing_initiative_id" = "old_initiative"."id" |
jbe@177 | 879 JOIN "battle" "battle_lose" |
jbe@177 | 880 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@177 | 881 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" |
jbe@177 | 882 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" |
jbe@177 | 883 WHERE "old_initiative"."issue_id" = "issue_id_p" |
jbe@177 | 884 AND "old_initiative"."indirect_majority" = TRUE |
jbe@177 | 885 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@177 | 886 "battle_win"."count" * "policy_row"."indirect_majority_den" > |
jbe@177 | 887 "policy_row"."indirect_majority_num" * |
jbe@177 | 888 ("battle_win"."count"+"battle_lose"."count") |
jbe@177 | 889 ELSE |
jbe@177 | 890 "battle_win"."count" * "policy_row"."indirect_majority_den" >= |
jbe@177 | 891 "policy_row"."indirect_majority_num" * |
jbe@177 | 892 ("battle_win"."count"+"battle_lose"."count") |
jbe@177 | 893 END |
jbe@177 | 894 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" |
jbe@177 | 895 AND "issue_row"."voter_count"-"battle_lose"."count" >= |
jbe@177 | 896 "policy_row"."indirect_majority_non_negative" |
jbe@177 | 897 ) AS "subquery" |
jbe@177 | 898 WHERE "id" = "subquery"."initiative_id"; |
jbe@177 | 899 EXIT WHEN NOT FOUND; |
jbe@177 | 900 END LOOP; |
jbe@177 | 901 -- set "multistage_majority" for remaining matching initiatives: |
jbe@177 | 902 UPDATE "initiative" SET "multistage_majority" = TRUE |
jbe@177 | 903 FROM ( |
jbe@177 | 904 SELECT "losing_initiative"."id" AS "initiative_id" |
jbe@177 | 905 FROM "initiative" "losing_initiative" |
jbe@177 | 906 JOIN "initiative" "winning_initiative" |
jbe@177 | 907 ON "winning_initiative"."issue_id" = "issue_id_p" |
jbe@177 | 908 AND "winning_initiative"."admitted" |
jbe@177 | 909 JOIN "battle" "battle_win" |
jbe@177 | 910 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@177 | 911 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" |
jbe@177 | 912 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" |
jbe@177 | 913 JOIN "battle" "battle_lose" |
jbe@177 | 914 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@177 | 915 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" |
jbe@177 | 916 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" |
jbe@177 | 917 WHERE "losing_initiative"."issue_id" = "issue_id_p" |
jbe@177 | 918 AND "losing_initiative"."admitted" |
jbe@177 | 919 AND "winning_initiative"."schulze_rank" < |
jbe@177 | 920 "losing_initiative"."schulze_rank" |
jbe@177 | 921 AND "battle_win"."count" > "battle_lose"."count" |
jbe@177 | 922 AND ( |
jbe@177 | 923 "battle_win"."count" > "winning_initiative"."positive_votes" OR |
jbe@177 | 924 "battle_lose"."count" < "losing_initiative"."negative_votes" ) |
jbe@177 | 925 ) AS "subquery" |
jbe@177 | 926 WHERE "id" = "subquery"."initiative_id"; |
jbe@177 | 927 -- mark eligible initiatives: |
jbe@177 | 928 UPDATE "initiative" SET "eligible" = TRUE |
jbe@177 | 929 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 930 AND "initiative"."direct_majority" |
jbe@177 | 931 AND "initiative"."indirect_majority" |
jbe@177 | 932 AND "initiative"."better_than_status_quo" |
jbe@177 | 933 AND ( |
jbe@177 | 934 "policy_row"."no_multistage_majority" = FALSE OR |
jbe@177 | 935 "initiative"."multistage_majority" = FALSE ) |
jbe@177 | 936 AND ( |
jbe@177 | 937 "policy_row"."no_reverse_beat_path" = FALSE OR |
jbe@177 | 938 "initiative"."reverse_beat_path" = FALSE ); |
jbe@177 | 939 -- mark final winner: |
jbe@177 | 940 UPDATE "initiative" SET "winner" = TRUE |
jbe@177 | 941 FROM ( |
jbe@177 | 942 SELECT "id" AS "initiative_id" |
jbe@177 | 943 FROM "initiative" |
jbe@177 | 944 WHERE "issue_id" = "issue_id_p" AND "eligible" |
jbe@177 | 945 ORDER BY "schulze_rank", "id" |
jbe@177 | 946 LIMIT 1 |
jbe@177 | 947 ) AS "subquery" |
jbe@177 | 948 WHERE "id" = "subquery"."initiative_id"; |
jbe@177 | 949 -- write (final) ranks: |
jbe@177 | 950 "rank_v" := 1; |
jbe@177 | 951 FOR "initiative_id_v" IN |
jbe@177 | 952 SELECT "id" |
jbe@177 | 953 FROM "initiative" |
jbe@177 | 954 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@177 | 955 ORDER BY |
jbe@177 | 956 "winner" DESC, |
jbe@177 | 957 ("direct_majority" AND "indirect_majority") DESC, |
jbe@177 | 958 "schulze_rank", |
jbe@177 | 959 "id" |
jbe@177 | 960 LOOP |
jbe@177 | 961 UPDATE "initiative" SET "rank" = "rank_v" |
jbe@177 | 962 WHERE "id" = "initiative_id_v"; |
jbe@177 | 963 "rank_v" := "rank_v" + 1; |
jbe@177 | 964 END LOOP; |
jbe@177 | 965 -- set schulze rank of status quo and mark issue as finished: |
jbe@177 | 966 UPDATE "issue" SET |
jbe@177 | 967 "status_quo_schulze_rank" = "rank_ary"["dimension_v"], |
jbe@177 | 968 "state" = |
jbe@177 | 969 CASE WHEN EXISTS ( |
jbe@177 | 970 SELECT NULL FROM "initiative" |
jbe@177 | 971 WHERE "issue_id" = "issue_id_p" AND "winner" |
jbe@177 | 972 ) THEN |
jbe@177 | 973 'finished_with_winner'::"issue_state" |
jbe@177 | 974 ELSE |
jbe@177 | 975 'finished_without_winner'::"issue_state" |
jbe@177 | 976 END, |
jbe@177 | 977 "ranks_available" = TRUE |
jbe@177 | 978 WHERE "id" = "issue_id_p"; |
jbe@177 | 979 RETURN; |
jbe@177 | 980 END; |
jbe@177 | 981 $$; |
jbe@177 | 982 |
jbe@177 | 983 CREATE OR REPLACE FUNCTION "check_issue" |
jbe@177 | 984 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@177 | 985 RETURNS VOID |
jbe@177 | 986 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@177 | 987 DECLARE |
jbe@177 | 988 "issue_row" "issue"%ROWTYPE; |
jbe@177 | 989 "policy_row" "policy"%ROWTYPE; |
jbe@177 | 990 BEGIN |
jbe@177 | 991 PERFORM "lock_issue"("issue_id_p"); |
jbe@177 | 992 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@177 | 993 -- only process open issues: |
jbe@177 | 994 IF "issue_row"."closed" ISNULL THEN |
jbe@177 | 995 SELECT * INTO "policy_row" FROM "policy" |
jbe@177 | 996 WHERE "id" = "issue_row"."policy_id"; |
jbe@177 | 997 -- create a snapshot, unless issue is already fully frozen: |
jbe@177 | 998 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@177 | 999 PERFORM "create_snapshot"("issue_id_p"); |
jbe@177 | 1000 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@177 | 1001 END IF; |
jbe@177 | 1002 -- eventually close or accept issues, which have not been accepted: |
jbe@177 | 1003 IF "issue_row"."accepted" ISNULL THEN |
jbe@177 | 1004 IF EXISTS ( |
jbe@177 | 1005 SELECT NULL FROM "initiative" |
jbe@177 | 1006 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 1007 AND "supporter_count" > 0 |
jbe@177 | 1008 AND "supporter_count" * "policy_row"."issue_quorum_den" |
jbe@177 | 1009 >= "issue_row"."population" * "policy_row"."issue_quorum_num" |
jbe@177 | 1010 ) THEN |
jbe@177 | 1011 -- accept issues, if supporter count is high enough |
jbe@177 | 1012 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@177 | 1013 -- NOTE: "issue_row" used later |
jbe@177 | 1014 "issue_row"."state" := 'discussion'; |
jbe@177 | 1015 "issue_row"."accepted" := now(); |
jbe@177 | 1016 UPDATE "issue" SET |
jbe@177 | 1017 "state" = "issue_row"."state", |
jbe@177 | 1018 "accepted" = "issue_row"."accepted" |
jbe@177 | 1019 WHERE "id" = "issue_row"."id"; |
jbe@177 | 1020 ELSIF |
jbe@177 | 1021 now() >= "issue_row"."created" + "issue_row"."admission_time" |
jbe@177 | 1022 THEN |
jbe@177 | 1023 -- close issues, if admission time has expired |
jbe@177 | 1024 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@177 | 1025 UPDATE "issue" SET |
jbe@177 | 1026 "state" = 'canceled_issue_not_accepted', |
jbe@177 | 1027 "closed" = now() |
jbe@177 | 1028 WHERE "id" = "issue_row"."id"; |
jbe@177 | 1029 END IF; |
jbe@177 | 1030 END IF; |
jbe@177 | 1031 -- eventually half freeze issues: |
jbe@177 | 1032 IF |
jbe@177 | 1033 -- NOTE: issue can't be closed at this point, if it has been accepted |
jbe@177 | 1034 "issue_row"."accepted" NOTNULL AND |
jbe@177 | 1035 "issue_row"."half_frozen" ISNULL |
jbe@177 | 1036 THEN |
jbe@177 | 1037 IF |
jbe@177 | 1038 now() >= "issue_row"."accepted" + "issue_row"."discussion_time" |
jbe@177 | 1039 THEN |
jbe@177 | 1040 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); |
jbe@177 | 1041 -- NOTE: "issue_row" used later |
jbe@177 | 1042 "issue_row"."state" := 'verification'; |
jbe@177 | 1043 "issue_row"."half_frozen" := now(); |
jbe@177 | 1044 UPDATE "issue" SET |
jbe@177 | 1045 "state" = "issue_row"."state", |
jbe@177 | 1046 "half_frozen" = "issue_row"."half_frozen" |
jbe@177 | 1047 WHERE "id" = "issue_row"."id"; |
jbe@177 | 1048 END IF; |
jbe@177 | 1049 END IF; |
jbe@177 | 1050 -- close issues after some time, if all initiatives have been revoked: |
jbe@177 | 1051 IF |
jbe@177 | 1052 "issue_row"."closed" ISNULL AND |
jbe@177 | 1053 NOT EXISTS ( |
jbe@177 | 1054 -- all initiatives are revoked |
jbe@177 | 1055 SELECT NULL FROM "initiative" |
jbe@177 | 1056 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@177 | 1057 ) AND ( |
jbe@177 | 1058 -- and issue has not been accepted yet |
jbe@177 | 1059 "issue_row"."accepted" ISNULL OR |
jbe@177 | 1060 NOT EXISTS ( |
jbe@177 | 1061 -- or no initiatives have been revoked lately |
jbe@177 | 1062 SELECT NULL FROM "initiative" |
jbe@177 | 1063 WHERE "issue_id" = "issue_id_p" |
jbe@177 | 1064 AND now() < "revoked" + "issue_row"."verification_time" |
jbe@177 | 1065 ) OR ( |
jbe@177 | 1066 -- or verification time has elapsed |
jbe@177 | 1067 "issue_row"."half_frozen" NOTNULL AND |
jbe@177 | 1068 "issue_row"."fully_frozen" ISNULL AND |
jbe@177 | 1069 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@177 | 1070 ) |
jbe@177 | 1071 ) |
jbe@177 | 1072 THEN |
jbe@177 | 1073 -- NOTE: "issue_row" used later |
jbe@177 | 1074 IF "issue_row"."accepted" ISNULL THEN |
jbe@177 | 1075 "issue_row"."state" := 'canceled_revoked_before_accepted'; |
jbe@177 | 1076 ELSIF "issue_row"."half_frozen" ISNULL THEN |
jbe@177 | 1077 "issue_row"."state" := 'canceled_after_revocation_during_discussion'; |
jbe@177 | 1078 ELSE |
jbe@177 | 1079 "issue_row"."state" := 'canceled_after_revocation_during_verification'; |
jbe@177 | 1080 END IF; |
jbe@177 | 1081 "issue_row"."closed" := now(); |
jbe@177 | 1082 UPDATE "issue" SET |
jbe@177 | 1083 "state" = "issue_row"."state", |
jbe@177 | 1084 "closed" = "issue_row"."closed" |
jbe@177 | 1085 WHERE "id" = "issue_row"."id"; |
jbe@177 | 1086 END IF; |
jbe@177 | 1087 -- fully freeze issue after verification time: |
jbe@177 | 1088 IF |
jbe@177 | 1089 "issue_row"."half_frozen" NOTNULL AND |
jbe@177 | 1090 "issue_row"."fully_frozen" ISNULL AND |
jbe@177 | 1091 "issue_row"."closed" ISNULL AND |
jbe@177 | 1092 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@177 | 1093 THEN |
jbe@177 | 1094 PERFORM "freeze_after_snapshot"("issue_id_p"); |
jbe@177 | 1095 -- NOTE: "issue" might change, thus "issue_row" has to be updated below |
jbe@177 | 1096 END IF; |
jbe@177 | 1097 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@177 | 1098 -- close issue by calling close_voting(...) after voting time: |
jbe@177 | 1099 IF |
jbe@177 | 1100 "issue_row"."closed" ISNULL AND |
jbe@177 | 1101 "issue_row"."fully_frozen" NOTNULL AND |
jbe@177 | 1102 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" |
jbe@177 | 1103 THEN |
jbe@177 | 1104 PERFORM "close_voting"("issue_id_p"); |
jbe@177 | 1105 -- calculate ranks will not consume much time and can be done now |
jbe@177 | 1106 PERFORM "calculate_ranks"("issue_id_p"); |
jbe@177 | 1107 END IF; |
jbe@177 | 1108 END IF; |
jbe@177 | 1109 RETURN; |
jbe@177 | 1110 END; |
jbe@177 | 1111 $$; |
jbe@177 | 1112 |
jbe@183 | 1113 CREATE OR REPLACE FUNCTION "check_everything"() |
jbe@183 | 1114 RETURNS VOID |
jbe@183 | 1115 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@183 | 1116 DECLARE |
jbe@183 | 1117 "issue_id_v" "issue"."id"%TYPE; |
jbe@183 | 1118 BEGIN |
jbe@184 | 1119 PERFORM "check_activity"(); |
jbe@183 | 1120 PERFORM "calculate_member_counts"(); |
jbe@183 | 1121 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP |
jbe@183 | 1122 PERFORM "check_issue"("issue_id_v"); |
jbe@183 | 1123 END LOOP; |
jbe@183 | 1124 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP |
jbe@183 | 1125 PERFORM "calculate_ranks"("issue_id_v"); |
jbe@183 | 1126 END LOOP; |
jbe@183 | 1127 RETURN; |
jbe@183 | 1128 END; |
jbe@183 | 1129 $$; |
jbe@183 | 1130 |
jbe@183 | 1131 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) |
jbe@183 | 1132 RETURNS VOID |
jbe@183 | 1133 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@183 | 1134 BEGIN |
jbe@183 | 1135 UPDATE "member" SET |
jbe@183 | 1136 "last_login" = NULL, |
jbe@183 | 1137 "login" = NULL, |
jbe@183 | 1138 "password" = NULL, |
jbe@183 | 1139 "locked" = TRUE, |
jbe@183 | 1140 "active" = FALSE, |
jbe@183 | 1141 "notify_email" = NULL, |
jbe@183 | 1142 "notify_email_unconfirmed" = NULL, |
jbe@183 | 1143 "notify_email_secret" = NULL, |
jbe@183 | 1144 "notify_email_secret_expiry" = NULL, |
jbe@183 | 1145 "notify_email_lock_expiry" = NULL, |
jbe@183 | 1146 "password_reset_secret" = NULL, |
jbe@183 | 1147 "password_reset_secret_expiry" = NULL, |
jbe@183 | 1148 "organizational_unit" = NULL, |
jbe@183 | 1149 "internal_posts" = NULL, |
jbe@183 | 1150 "realname" = NULL, |
jbe@183 | 1151 "birthday" = NULL, |
jbe@183 | 1152 "address" = NULL, |
jbe@183 | 1153 "email" = NULL, |
jbe@183 | 1154 "xmpp_address" = NULL, |
jbe@183 | 1155 "website" = NULL, |
jbe@183 | 1156 "phone" = NULL, |
jbe@183 | 1157 "mobile_phone" = NULL, |
jbe@183 | 1158 "profession" = NULL, |
jbe@183 | 1159 "external_memberships" = NULL, |
jbe@183 | 1160 "external_posts" = NULL, |
jbe@183 | 1161 "statement" = NULL |
jbe@183 | 1162 WHERE "id" = "member_id_p"; |
jbe@183 | 1163 -- "text_search_data" is updated by triggers |
jbe@183 | 1164 DELETE FROM "setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1165 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1166 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1167 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1168 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1169 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1170 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1171 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1172 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1173 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1174 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1175 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1176 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; |
jbe@183 | 1177 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; |
jbe@183 | 1178 DELETE FROM "direct_voter" USING "issue" |
jbe@183 | 1179 WHERE "direct_voter"."issue_id" = "issue"."id" |
jbe@183 | 1180 AND "issue"."closed" ISNULL |
jbe@183 | 1181 AND "member_id" = "member_id_p"; |
jbe@183 | 1182 RETURN; |
jbe@183 | 1183 END; |
jbe@183 | 1184 $$; |
jbe@183 | 1185 |
jbe@183 | 1186 CREATE OR REPLACE FUNCTION "delete_private_data"() |
jbe@183 | 1187 RETURNS VOID |
jbe@183 | 1188 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@183 | 1189 BEGIN |
jbe@183 | 1190 UPDATE "member" SET |
jbe@183 | 1191 "last_login" = NULL, |
jbe@183 | 1192 "login" = NULL, |
jbe@183 | 1193 "password" = NULL, |
jbe@183 | 1194 "notify_email" = NULL, |
jbe@183 | 1195 "notify_email_unconfirmed" = NULL, |
jbe@183 | 1196 "notify_email_secret" = NULL, |
jbe@183 | 1197 "notify_email_secret_expiry" = NULL, |
jbe@183 | 1198 "notify_email_lock_expiry" = NULL, |
jbe@183 | 1199 "password_reset_secret" = NULL, |
jbe@183 | 1200 "password_reset_secret_expiry" = NULL, |
jbe@183 | 1201 "organizational_unit" = NULL, |
jbe@183 | 1202 "internal_posts" = NULL, |
jbe@183 | 1203 "realname" = NULL, |
jbe@183 | 1204 "birthday" = NULL, |
jbe@183 | 1205 "address" = NULL, |
jbe@183 | 1206 "email" = NULL, |
jbe@183 | 1207 "xmpp_address" = NULL, |
jbe@183 | 1208 "website" = NULL, |
jbe@183 | 1209 "phone" = NULL, |
jbe@183 | 1210 "mobile_phone" = NULL, |
jbe@183 | 1211 "profession" = NULL, |
jbe@183 | 1212 "external_memberships" = NULL, |
jbe@183 | 1213 "external_posts" = NULL, |
jbe@183 | 1214 "statement" = NULL; |
jbe@183 | 1215 -- "text_search_data" is updated by triggers |
jbe@183 | 1216 DELETE FROM "invite_code"; |
jbe@183 | 1217 DELETE FROM "setting"; |
jbe@183 | 1218 DELETE FROM "setting_map"; |
jbe@183 | 1219 DELETE FROM "member_relation_setting"; |
jbe@183 | 1220 DELETE FROM "member_image"; |
jbe@183 | 1221 DELETE FROM "contact"; |
jbe@183 | 1222 DELETE FROM "ignored_member"; |
jbe@183 | 1223 DELETE FROM "area_setting"; |
jbe@183 | 1224 DELETE FROM "issue_setting"; |
jbe@183 | 1225 DELETE FROM "ignored_initiative"; |
jbe@183 | 1226 DELETE FROM "initiative_setting"; |
jbe@183 | 1227 DELETE FROM "suggestion_setting"; |
jbe@183 | 1228 DELETE FROM "non_voter"; |
jbe@183 | 1229 DELETE FROM "direct_voter" USING "issue" |
jbe@183 | 1230 WHERE "direct_voter"."issue_id" = "issue"."id" |
jbe@183 | 1231 AND "issue"."closed" ISNULL; |
jbe@183 | 1232 RETURN; |
jbe@183 | 1233 END; |
jbe@183 | 1234 $$; |
jbe@183 | 1235 |
jbe@178 | 1236 COMMIT; |
jbe@178 | 1237 |
jbe@178 | 1238 BEGIN; |
jbe@178 | 1239 |
jbe@184 | 1240 UPDATE "member" SET |
jbe@184 | 1241 "activated" = "created", |
jbe@184 | 1242 "last_activity" = CASE WHEN "active" THEN |
jbe@184 | 1243 coalesce("last_login"::DATE, now()) |
jbe@184 | 1244 ELSE |
jbe@184 | 1245 "last_login"::DATE |
jbe@184 | 1246 END; |
jbe@182 | 1247 |
jbe@182 | 1248 UPDATE "member" SET |
jbe@182 | 1249 "created" = "invite_code"."created", |
jbe@182 | 1250 "invite_code" = "invite_code"."code", |
jbe@182 | 1251 "admin_comment" = "invite_code"."comment" |
jbe@182 | 1252 FROM "invite_code" |
jbe@182 | 1253 WHERE "member"."id" = "invite_code"."member_id"; |
jbe@182 | 1254 |
jbe@182 | 1255 DROP TABLE "invite_code"; |
jbe@182 | 1256 |
jbe@178 | 1257 UPDATE "initiative" SET |
jbe@178 | 1258 "direct_majority" = "rank" NOTNULL, |
jbe@178 | 1259 "indirect_majority" = "rank" NOTNULL, |
jbe@178 | 1260 "schulze_rank" = "rank", |
jbe@178 | 1261 "better_than_status_quo" = "rank" NOTNULL, |
jbe@178 | 1262 "worse_than_status_quo" = "rank" ISNULL, |
jbe@178 | 1263 "reverse_beat_path" = "rank" ISNULL, |
jbe@178 | 1264 "multistage_majority" = "rank" ISNULL, |
jbe@178 | 1265 "eligible" = "rank" NOTNULL, |
jbe@178 | 1266 "winner" = ("rank" = 1) |
jbe@178 | 1267 FROM "issue" |
jbe@178 | 1268 WHERE "issue"."id" = "initiative"."issue_id" |
jbe@178 | 1269 AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') |
jbe@178 | 1270 AND "initiative"."admitted"; |
jbe@177 | 1271 |
jbe@178 | 1272 UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" |
jbe@178 | 1273 FROM ( |
jbe@178 | 1274 SELECT |
jbe@178 | 1275 "issue"."id" AS "issue_id", |
jbe@186 | 1276 COALESCE(max("initiative"."rank") + 1, 1) AS "rank" |
jbe@178 | 1277 FROM "issue" JOIN "initiative" |
jbe@178 | 1278 ON "issue"."id" = "initiative"."issue_id" |
jbe@178 | 1279 WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') |
jbe@178 | 1280 AND "initiative"."admitted" |
jbe@178 | 1281 GROUP BY "issue"."id" |
jbe@178 | 1282 ) AS "subquery" |
jbe@178 | 1283 WHERE "issue"."id" = "subquery"."issue_id"; |
jbe@178 | 1284 |
jbe@178 | 1285 CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) |
jbe@178 | 1286 RETURNS VOID |
jbe@178 | 1287 LANGUAGE 'plpgsql' AS $$ |
jbe@178 | 1288 DECLARE |
jbe@178 | 1289 "rank_v" INT4; |
jbe@178 | 1290 "initiative_id_v" INT4; |
jbe@178 | 1291 BEGIN |
jbe@178 | 1292 SELECT "status_quo_schulze_rank" INTO "rank_v" |
jbe@178 | 1293 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@178 | 1294 FOR "initiative_id_v" IN |
jbe@178 | 1295 SELECT "id" FROM "initiative" |
jbe@178 | 1296 WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL |
jbe@178 | 1297 ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC |
jbe@178 | 1298 LOOP |
jbe@178 | 1299 UPDATE "initiative" SET |
jbe@178 | 1300 "schulze_rank" = "rank_v" + 1, |
jbe@178 | 1301 "rank" = "rank_v" |
jbe@178 | 1302 WHERE "id" = "initiative_id_v"; |
jbe@178 | 1303 "rank_v" := "rank_v" + 1; |
jbe@178 | 1304 END LOOP; |
jbe@178 | 1305 RETURN; |
jbe@178 | 1306 END; |
jbe@178 | 1307 $$; |
jbe@178 | 1308 |
jbe@178 | 1309 SELECT "update__set_remaining_ranks"("id") FROM "issue" |
jbe@178 | 1310 WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); |
jbe@178 | 1311 |
jbe@178 | 1312 DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); |
jbe@177 | 1313 |
jbe@177 | 1314 UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" |
jbe@177 | 1315 FROM ( |
jbe@177 | 1316 SELECT DISTINCT ON ("suggestion"."id") |
jbe@177 | 1317 "suggestion"."id" AS "suggestion_id", |
jbe@177 | 1318 "draft"."id" AS "draft_id" |
jbe@177 | 1319 FROM "suggestion" JOIN "draft" |
jbe@177 | 1320 ON "suggestion"."initiative_id" = "draft"."initiative_id" |
jbe@177 | 1321 WHERE "draft"."created" <= "suggestion"."created" |
jbe@177 | 1322 ORDER BY "suggestion"."id", "draft"."created" DESC |
jbe@177 | 1323 ) AS "subquery" |
jbe@177 | 1324 WHERE "suggestion"."id" = "subquery"."suggestion_id"; |
jbe@177 | 1325 |
jbe@178 | 1326 COMMIT; |
jbe@178 | 1327 |
jbe@184 | 1328 ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" |
jbe@184 | 1329 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); |
jbe@177 | 1330 ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL; |