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