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