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