| rev |
line source |
|
jbe@0
|
1
|
|
jbe@0
|
2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
|
|
jbe@0
|
3
|
|
jbe@0
|
4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
|
|
jbe@0
|
5
|
|
jbe@0
|
6 BEGIN;
|
|
jbe@0
|
7
|
|
jbe@0
|
8
|
|
jbe@0
|
9
|
|
jbe@0
|
10 -------------------------
|
|
jbe@0
|
11 -- Tables and indicies --
|
|
jbe@0
|
12 -------------------------
|
|
jbe@0
|
13
|
|
jbe@0
|
14
|
|
jbe@0
|
15 CREATE TABLE "member" (
|
|
jbe@0
|
16 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
17 "login" TEXT NOT NULL UNIQUE,
|
|
jbe@0
|
18 "password" TEXT,
|
|
jbe@0
|
19 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
20 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@0
|
21 "name" TEXT,
|
|
jbe@0
|
22 "ident_number" TEXT UNIQUE );
|
|
jbe@0
|
23 CREATE INDEX "member_active_idx" ON "member" ("active");
|
|
jbe@0
|
24
|
|
jbe@0
|
25 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
|
|
jbe@0
|
26
|
|
jbe@0
|
27 COMMENT ON COLUMN "member"."login" IS 'Login name';
|
|
jbe@0
|
28 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
|
|
jbe@0
|
29 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
|
|
jbe@0
|
30 COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
|
|
jbe@0
|
31
|
|
jbe@0
|
32
|
|
jbe@0
|
33 CREATE TABLE "contact" (
|
|
jbe@0
|
34 PRIMARY KEY ("member_id", "other_member_id"),
|
|
jbe@0
|
35 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
36 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
37 "public" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@0
|
38
|
|
jbe@0
|
39 COMMENT ON TABLE "contact" IS 'Contact lists';
|
|
jbe@0
|
40
|
|
jbe@0
|
41 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
|
|
jbe@0
|
42 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
|
|
jbe@0
|
43 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
|
|
jbe@0
|
44
|
|
jbe@0
|
45
|
|
jbe@0
|
46 CREATE TABLE "session" (
|
|
jbe@0
|
47 "ident" TEXT PRIMARY KEY,
|
|
jbe@0
|
48 "additional_secret" TEXT,
|
|
jbe@0
|
49 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
|
jbe@0
|
50 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
|
|
jbe@0
|
51 "lang" TEXT );
|
|
jbe@0
|
52 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
|
jbe@0
|
53
|
|
jbe@0
|
54 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
|
|
jbe@0
|
55
|
|
jbe@0
|
56 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
|
jbe@0
|
57 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
|
jbe@0
|
58 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
|
jbe@0
|
59 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
|
jbe@0
|
60
|
|
jbe@0
|
61
|
|
jbe@0
|
62 CREATE TABLE "policy" (
|
|
jbe@0
|
63 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
64 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
65 "name" TEXT NOT NULL UNIQUE,
|
|
jbe@0
|
66 "description" TEXT NOT NULL DEFAULT '',
|
|
jbe@0
|
67 "admission_time" INTERVAL NOT NULL,
|
|
jbe@0
|
68 "discussion_time" INTERVAL NOT NULL,
|
|
jbe@0
|
69 "voting_time" INTERVAL NOT NULL,
|
|
jbe@0
|
70 "issue_quorum_num" INT4 NOT NULL,
|
|
jbe@0
|
71 "issue_quorum_den" INT4 NOT NULL,
|
|
jbe@0
|
72 "initiative_quorum_num" INT4 NOT NULL,
|
|
jbe@0
|
73 "initiative_quorum_den" INT4 NOT NULL );
|
|
jbe@0
|
74 CREATE INDEX "policy_active_idx" ON "policy" ("active");
|
|
jbe@0
|
75
|
|
jbe@0
|
76 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
|
|
jbe@0
|
77
|
|
jbe@0
|
78 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
|
|
jbe@0
|
79 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
|
|
jbe@0
|
80 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "frozen" after being "accepted"';
|
|
jbe@0
|
81 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "frozen" but not "closed"';
|
|
jbe@0
|
82 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
|
|
jbe@0
|
83 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
|
|
jbe@0
|
84 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
|
|
jbe@0
|
85 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
|
|
jbe@0
|
86
|
|
jbe@0
|
87
|
|
jbe@0
|
88 CREATE TABLE "area" (
|
|
jbe@0
|
89 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
90 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
91 "name" TEXT NOT NULL,
|
|
jbe@0
|
92 "description" TEXT NOT NULL DEFAULT '' );
|
|
jbe@0
|
93 CREATE INDEX "area_active_idx" ON "area" ("active");
|
|
jbe@0
|
94
|
|
jbe@0
|
95 COMMENT ON TABLE "area" IS 'Subject areas';
|
|
jbe@0
|
96
|
|
jbe@0
|
97 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
|
|
jbe@0
|
98
|
|
jbe@0
|
99
|
|
jbe@0
|
100 CREATE TABLE "issue" (
|
|
jbe@0
|
101 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
102 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
103 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
104 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
105 "accepted" TIMESTAMPTZ,
|
|
jbe@0
|
106 "frozen" TIMESTAMPTZ,
|
|
jbe@0
|
107 "closed" TIMESTAMPTZ,
|
|
jbe@0
|
108 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@0
|
109 "snapshot" TIMESTAMPTZ,
|
|
jbe@0
|
110 "population" INT4,
|
|
jbe@0
|
111 "vote_now" INT4,
|
|
jbe@0
|
112 "vote_later" INT4,
|
|
jbe@0
|
113 CONSTRAINT "valid_state" CHECK (
|
|
jbe@0
|
114 ("accepted" ISNULL AND "frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@0
|
115 ("accepted" ISNULL AND "frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@0
|
116 ("accepted" NOTNULL AND "frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@0
|
117 ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@0
|
118 ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@0
|
119 ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
|
|
jbe@0
|
120 CONSTRAINT "state_change_order" CHECK ("created" <= "accepted" AND "accepted" <= "frozen" AND "frozen" <= "closed"),
|
|
jbe@0
|
121 CONSTRAINT "last_snapshot_on_freeze" CHECK ("snapshot" = "frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
|
|
jbe@0
|
122 CONSTRAINT "freeze_requires_snapshot" CHECK ("frozen" ISNULL OR "snapshot" NOTNULL) );
|
|
jbe@0
|
123 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
|
|
jbe@0
|
124 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
|
|
jbe@0
|
125 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
|
|
jbe@0
|
126
|
|
jbe@0
|
127 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
|
|
jbe@0
|
128
|
|
jbe@0
|
129 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
|
|
jbe@0
|
130 COMMENT ON COLUMN "issue"."frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting.';
|
|
jbe@0
|
131 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" is over, and issue is no longer active';
|
|
jbe@0
|
132 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
|
|
jbe@0
|
133 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
|
|
jbe@0
|
134 COMMENT ON COLUMN "issue"."population" IS 'Calculated from table "direct_population_snapshot"';
|
|
jbe@0
|
135 COMMENT ON COLUMN "issue"."vote_now" IS 'Calculated from table "direct_interest_snapshot"';
|
|
jbe@0
|
136 COMMENT ON COLUMN "issue"."vote_later" IS 'Calculated from table "direct_interest_snapshot"';
|
|
jbe@0
|
137
|
|
jbe@0
|
138
|
|
jbe@0
|
139 CREATE TABLE "initiative" (
|
|
jbe@0
|
140 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
|
|
jbe@0
|
141 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
142 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
143 "name" TEXT NOT NULL,
|
|
jbe@0
|
144 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
145 "revoked" TIMESTAMPTZ,
|
|
jbe@0
|
146 "admitted" BOOLEAN,
|
|
jbe@0
|
147 "supporter_count" INT4,
|
|
jbe@0
|
148 "informed_supporter_count" INT4,
|
|
jbe@0
|
149 "satisfied_supporter_count" INT4,
|
|
jbe@0
|
150 "satisfied_informed_supporter_count" INT4,
|
|
jbe@0
|
151 "positive_votes" INT4,
|
|
jbe@0
|
152 "negative_votes" INT4,
|
|
jbe@0
|
153 "rank" INT4,
|
|
jbe@0
|
154 CONSTRAINT "revoked_initiatives_cant_be_admitted"
|
|
jbe@0
|
155 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
|
|
jbe@0
|
156 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
|
|
jbe@0
|
157 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
|
|
jbe@0
|
158
|
|
jbe@0
|
159 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
|
|
jbe@0
|
160
|
|
jbe@0
|
161 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
|
|
jbe@0
|
162 COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
|
|
jbe@0
|
163 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
164 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
165 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
166 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
167 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
|
|
jbe@0
|
168 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
|
|
jbe@0
|
169 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
|
|
jbe@0
|
170
|
|
jbe@0
|
171
|
|
jbe@0
|
172 CREATE TABLE "draft" (
|
|
jbe@0
|
173 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
|
|
jbe@0
|
174 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
175 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
176 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
177 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
178 "content" TEXT NOT NULL );
|
|
jbe@0
|
179
|
|
jbe@0
|
180 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
|
|
jbe@0
|
181
|
|
jbe@0
|
182
|
|
jbe@0
|
183 CREATE TABLE "suggestion" (
|
|
jbe@0
|
184 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
|
|
jbe@0
|
185 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
186 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
187 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
188 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
189 "name" TEXT NOT NULL,
|
|
jbe@0
|
190 "description" TEXT NOT NULL DEFAULT '',
|
|
jbe@0
|
191 "minus2_unfulfilled_count" INT4,
|
|
jbe@0
|
192 "minus2_fulfilled_count" INT4,
|
|
jbe@0
|
193 "minus1_unfulfilled_count" INT4,
|
|
jbe@0
|
194 "minus1_fulfilled_count" INT4,
|
|
jbe@0
|
195 "plus1_unfulfilled_count" INT4,
|
|
jbe@0
|
196 "plus1_fulfilled_count" INT4,
|
|
jbe@0
|
197 "plus2_unfulfilled_count" INT4,
|
|
jbe@0
|
198 "plus2_fulfilled_count" INT4 );
|
|
jbe@0
|
199
|
|
jbe@0
|
200 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
|
|
jbe@0
|
201
|
|
jbe@0
|
202 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
203 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
204 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
205 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
206 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
207 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
208 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
209 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
210
|
|
jbe@0
|
211
|
|
jbe@0
|
212 CREATE TABLE "membership" (
|
|
jbe@0
|
213 PRIMARY KEY ("area_id", "member_id"),
|
|
jbe@0
|
214 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
215 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
216 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@0
|
217 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
|
|
jbe@0
|
218
|
|
jbe@0
|
219 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
|
|
jbe@0
|
220
|
|
jbe@0
|
221 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
|
|
jbe@0
|
222
|
|
jbe@0
|
223
|
|
jbe@0
|
224 CREATE TABLE "interest" (
|
|
jbe@0
|
225 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
226 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
227 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
228 "autoreject" BOOLEAN NOT NULL,
|
|
jbe@0
|
229 "voting_requested" BOOLEAN );
|
|
jbe@0
|
230 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
|
|
jbe@0
|
231
|
|
jbe@0
|
232 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
|
|
jbe@0
|
233
|
|
jbe@0
|
234 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
|
|
jbe@0
|
235 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
|
|
jbe@0
|
236
|
|
jbe@0
|
237
|
|
jbe@0
|
238 CREATE TABLE "initiator" (
|
|
jbe@0
|
239 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
240 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
241 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
242 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );
|
|
jbe@0
|
243 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
|
|
jbe@0
|
244
|
|
jbe@0
|
245 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
|
|
jbe@0
|
246
|
|
jbe@0
|
247 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
|
|
jbe@0
|
248
|
|
jbe@0
|
249
|
|
jbe@0
|
250 CREATE TABLE "supporter" (
|
|
jbe@0
|
251 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
252 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
253 "initiative_id" INT4,
|
|
jbe@0
|
254 "member_id" INT4,
|
|
jbe@0
|
255 "draft_id" INT8 NOT NULL,
|
|
jbe@0
|
256 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
257 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
258 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
|
|
jbe@0
|
259
|
|
jbe@0
|
260 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
|
|
jbe@0
|
261
|
|
jbe@0
|
262 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft';
|
|
jbe@0
|
263
|
|
jbe@0
|
264
|
|
jbe@0
|
265 CREATE TABLE "opinion" (
|
|
jbe@0
|
266 "initiative_id" INT4 NOT NULL,
|
|
jbe@0
|
267 PRIMARY KEY ("suggestion_id", "member_id"),
|
|
jbe@0
|
268 "suggestion_id" INT8,
|
|
jbe@0
|
269 "member_id" INT4,
|
|
jbe@0
|
270 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
|
|
jbe@0
|
271 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@0
|
272 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
273 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
274 CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
|
|
jbe@0
|
275
|
|
jbe@0
|
276 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
|
|
jbe@0
|
277
|
|
jbe@0
|
278 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
|
|
jbe@0
|
279
|
|
jbe@0
|
280
|
|
jbe@0
|
281 CREATE TABLE "delegation" (
|
|
jbe@0
|
282 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
283 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
284 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
285 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
286 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
287 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
|
|
jbe@0
|
288 CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
|
|
jbe@0
|
289 UNIQUE ("area_id", "truster_id", "trustee_id"),
|
|
jbe@0
|
290 UNIQUE ("issue_id", "truster_id", "trustee_id") );
|
|
jbe@0
|
291 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
|
|
jbe@0
|
292 ON "delegation" ("truster_id", "trustee_id")
|
|
jbe@0
|
293 WHERE "area_id" ISNULL AND "issue_id" ISNULL;
|
|
jbe@0
|
294 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
|
|
jbe@0
|
295 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
|
|
jbe@0
|
296
|
|
jbe@0
|
297 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
|
|
jbe@0
|
298
|
|
jbe@0
|
299 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
|
|
jbe@0
|
300 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
|
|
jbe@0
|
301
|
|
jbe@0
|
302
|
|
jbe@0
|
303 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'end_of_discussion');
|
|
jbe@0
|
304
|
|
jbe@0
|
305 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''end_of_discussion'' = saved state at end of discussion period';
|
|
jbe@0
|
306
|
|
jbe@0
|
307
|
|
jbe@0
|
308 CREATE TABLE "direct_population_snapshot" (
|
|
jbe@0
|
309 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
310 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
311 "event" "snapshot_event",
|
|
jbe@0
|
312 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
313 "weight" INT4,
|
|
jbe@0
|
314 "interest_exists" BOOLEAN NOT NULL );
|
|
jbe@0
|
315 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
|
|
jbe@0
|
316
|
|
jbe@0
|
317 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
|
|
jbe@0
|
318
|
|
jbe@0
|
319 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
320 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
|
|
jbe@0
|
321 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
|
|
jbe@0
|
322
|
|
jbe@0
|
323
|
|
jbe@0
|
324 CREATE TABLE "delegating_population_snapshot" (
|
|
jbe@0
|
325 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
326 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
327 "event" "snapshot_event",
|
|
jbe@0
|
328 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
329 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@0
|
330 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
|
|
jbe@0
|
331
|
|
jbe@0
|
332 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
|
|
jbe@0
|
333
|
|
jbe@0
|
334 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
335 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
|
|
jbe@0
|
336 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
|
|
jbe@0
|
337
|
|
jbe@0
|
338
|
|
jbe@0
|
339 CREATE TABLE "direct_interest_snapshot" (
|
|
jbe@0
|
340 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
341 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
342 "event" "snapshot_event",
|
|
jbe@0
|
343 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
344 "weight" INT4,
|
|
jbe@0
|
345 "voting_requested" BOOLEAN );
|
|
jbe@0
|
346 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
|
|
jbe@0
|
347
|
|
jbe@0
|
348 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
|
|
jbe@0
|
349
|
|
jbe@0
|
350 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
351 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
|
|
jbe@0
|
352 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
|
|
jbe@0
|
353
|
|
jbe@0
|
354
|
|
jbe@0
|
355 CREATE TABLE "delegating_interest_snapshot" (
|
|
jbe@0
|
356 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
357 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
358 "event" "snapshot_event",
|
|
jbe@0
|
359 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
360 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@0
|
361 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
|
|
jbe@0
|
362
|
|
jbe@0
|
363 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
|
|
jbe@0
|
364
|
|
jbe@0
|
365 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
366 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
|
|
jbe@0
|
367 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
|
|
jbe@0
|
368
|
|
jbe@0
|
369
|
|
jbe@0
|
370 CREATE TABLE "direct_supporter_snapshot" (
|
|
jbe@0
|
371 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
372 PRIMARY KEY ("initiative_id", "event", "member_id"),
|
|
jbe@0
|
373 "initiative_id" INT4,
|
|
jbe@0
|
374 "event" "snapshot_event",
|
|
jbe@0
|
375 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
376 "informed" BOOLEAN NOT NULL,
|
|
jbe@0
|
377 "satisfied" BOOLEAN NOT NULL,
|
|
jbe@0
|
378 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
379 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
380 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
|
|
jbe@0
|
381
|
|
jbe@0
|
382 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
|
|
jbe@0
|
383
|
|
jbe@0
|
384 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
385 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
|
|
jbe@0
|
386 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
|
|
jbe@0
|
387
|
|
jbe@0
|
388
|
|
jbe@0
|
389 CREATE TABLE "direct_voter" (
|
|
jbe@0
|
390 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
391 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
392 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
393 "weight" INT4,
|
|
jbe@0
|
394 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@0
|
395 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
|
|
jbe@0
|
396
|
|
jbe@0
|
397 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
|
|
jbe@0
|
398
|
|
jbe@0
|
399 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
|
|
jbe@0
|
400 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
|
|
jbe@0
|
401
|
|
jbe@0
|
402
|
|
jbe@0
|
403 CREATE TABLE "delegating_voter" (
|
|
jbe@0
|
404 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
405 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
406 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
407 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@0
|
408 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
|
|
jbe@0
|
409
|
|
jbe@0
|
410 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
|
|
jbe@0
|
411
|
|
jbe@0
|
412 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
|
|
jbe@0
|
413 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
|
|
jbe@0
|
414
|
|
jbe@0
|
415
|
|
jbe@0
|
416 CREATE TABLE "vote" (
|
|
jbe@0
|
417 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
418 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
419 "initiative_id" INT4,
|
|
jbe@0
|
420 "member_id" INT4,
|
|
jbe@0
|
421 "grade" INT4,
|
|
jbe@0
|
422 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
423 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
424 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
|
|
jbe@0
|
425
|
|
jbe@0
|
426 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
|
|
jbe@0
|
427
|
|
jbe@0
|
428 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
|
|
jbe@0
|
429
|
|
jbe@0
|
430
|
|
jbe@0
|
431
|
|
jbe@0
|
432 ----------------------------
|
|
jbe@0
|
433 -- Additional constraints --
|
|
jbe@0
|
434 ----------------------------
|
|
jbe@0
|
435
|
|
jbe@0
|
436
|
|
jbe@0
|
437 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
|
|
jbe@0
|
438 RETURNS TRIGGER
|
|
jbe@0
|
439 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
440 BEGIN
|
|
jbe@0
|
441 IF NOT EXISTS (
|
|
jbe@0
|
442 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
|
|
jbe@0
|
443 ) THEN
|
|
jbe@0
|
444 --RAISE 'Cannot create issue without an initial initiative.' USING
|
|
jbe@0
|
445 -- ERRCODE = 'integrity_constraint_violation',
|
|
jbe@0
|
446 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
|
|
jbe@0
|
447 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
|
|
jbe@0
|
448 END IF;
|
|
jbe@0
|
449 RETURN NULL;
|
|
jbe@0
|
450 END;
|
|
jbe@0
|
451 $$;
|
|
jbe@0
|
452
|
|
jbe@0
|
453 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
|
|
jbe@0
|
454 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
455 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
456 "issue_requires_first_initiative_trigger"();
|
|
jbe@0
|
457
|
|
jbe@0
|
458 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
|
|
jbe@0
|
459 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
|
|
jbe@0
|
460
|
|
jbe@0
|
461
|
|
jbe@0
|
462 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
|
|
jbe@0
|
463 RETURNS TRIGGER
|
|
jbe@0
|
464 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
465 DECLARE
|
|
jbe@0
|
466 "reference_lost" BOOLEAN;
|
|
jbe@0
|
467 BEGIN
|
|
jbe@0
|
468 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
469 "reference_lost" := TRUE;
|
|
jbe@0
|
470 ELSE
|
|
jbe@0
|
471 "reference_lost" := NEW."issue_id" != OLD."issue_id";
|
|
jbe@0
|
472 END IF;
|
|
jbe@0
|
473 IF
|
|
jbe@0
|
474 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
475 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
|
|
jbe@0
|
476 )
|
|
jbe@0
|
477 THEN
|
|
jbe@0
|
478 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
|
|
jbe@0
|
479 END IF;
|
|
jbe@0
|
480 RETURN NULL;
|
|
jbe@0
|
481 END;
|
|
jbe@0
|
482 $$;
|
|
jbe@0
|
483
|
|
jbe@0
|
484 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
|
|
jbe@0
|
485 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
486 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
487 "last_initiative_deletes_issue_trigger"();
|
|
jbe@0
|
488
|
|
jbe@0
|
489 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
|
|
jbe@0
|
490 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
|
|
jbe@0
|
491
|
|
jbe@0
|
492
|
|
jbe@0
|
493 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
|
|
jbe@0
|
494 RETURNS TRIGGER
|
|
jbe@0
|
495 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
496 BEGIN
|
|
jbe@0
|
497 IF NOT EXISTS (
|
|
jbe@0
|
498 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
|
|
jbe@0
|
499 ) THEN
|
|
jbe@0
|
500 --RAISE 'Cannot create initiative without an initial draft.' USING
|
|
jbe@0
|
501 -- ERRCODE = 'integrity_constraint_violation',
|
|
jbe@0
|
502 -- HINT = 'Create issue, initiative and draft within the same transaction.';
|
|
jbe@0
|
503 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
|
|
jbe@0
|
504 END IF;
|
|
jbe@0
|
505 RETURN NULL;
|
|
jbe@0
|
506 END;
|
|
jbe@0
|
507 $$;
|
|
jbe@0
|
508
|
|
jbe@0
|
509 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
|
|
jbe@0
|
510 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
511 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
512 "initiative_requires_first_draft_trigger"();
|
|
jbe@0
|
513
|
|
jbe@0
|
514 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
|
|
jbe@0
|
515 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
|
|
jbe@0
|
516
|
|
jbe@0
|
517
|
|
jbe@0
|
518 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
|
|
jbe@0
|
519 RETURNS TRIGGER
|
|
jbe@0
|
520 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
521 DECLARE
|
|
jbe@0
|
522 "reference_lost" BOOLEAN;
|
|
jbe@0
|
523 BEGIN
|
|
jbe@0
|
524 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
525 "reference_lost" := TRUE;
|
|
jbe@0
|
526 ELSE
|
|
jbe@0
|
527 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
|
|
jbe@0
|
528 END IF;
|
|
jbe@0
|
529 IF
|
|
jbe@0
|
530 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
531 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
|
|
jbe@0
|
532 )
|
|
jbe@0
|
533 THEN
|
|
jbe@0
|
534 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
|
|
jbe@0
|
535 END IF;
|
|
jbe@0
|
536 RETURN NULL;
|
|
jbe@0
|
537 END;
|
|
jbe@0
|
538 $$;
|
|
jbe@0
|
539
|
|
jbe@0
|
540 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
|
|
jbe@0
|
541 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
542 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
543 "last_draft_deletes_initiative_trigger"();
|
|
jbe@0
|
544
|
|
jbe@0
|
545 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
|
|
jbe@0
|
546 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
|
|
jbe@0
|
547
|
|
jbe@0
|
548
|
|
jbe@0
|
549 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
|
|
jbe@0
|
550 RETURNS TRIGGER
|
|
jbe@0
|
551 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
552 BEGIN
|
|
jbe@0
|
553 IF NOT EXISTS (
|
|
jbe@0
|
554 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
|
|
jbe@0
|
555 ) THEN
|
|
jbe@0
|
556 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
|
|
jbe@0
|
557 END IF;
|
|
jbe@0
|
558 RETURN NULL;
|
|
jbe@0
|
559 END;
|
|
jbe@0
|
560 $$;
|
|
jbe@0
|
561
|
|
jbe@0
|
562 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
|
|
jbe@0
|
563 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
564 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
565 "suggestion_requires_first_opinion_trigger"();
|
|
jbe@0
|
566
|
|
jbe@0
|
567 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
|
|
jbe@0
|
568 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
|
|
jbe@0
|
569
|
|
jbe@0
|
570
|
|
jbe@0
|
571 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
|
|
jbe@0
|
572 RETURNS TRIGGER
|
|
jbe@0
|
573 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
574 DECLARE
|
|
jbe@0
|
575 "reference_lost" BOOLEAN;
|
|
jbe@0
|
576 BEGIN
|
|
jbe@0
|
577 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
578 "reference_lost" := TRUE;
|
|
jbe@0
|
579 ELSE
|
|
jbe@0
|
580 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
|
|
jbe@0
|
581 END IF;
|
|
jbe@0
|
582 IF
|
|
jbe@0
|
583 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
584 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
|
|
jbe@0
|
585 )
|
|
jbe@0
|
586 THEN
|
|
jbe@0
|
587 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
|
|
jbe@0
|
588 END IF;
|
|
jbe@0
|
589 RETURN NULL;
|
|
jbe@0
|
590 END;
|
|
jbe@0
|
591 $$;
|
|
jbe@0
|
592
|
|
jbe@0
|
593 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
|
|
jbe@0
|
594 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
595 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
596 "last_opinion_deletes_suggestion_trigger"();
|
|
jbe@0
|
597
|
|
jbe@0
|
598 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
|
|
jbe@0
|
599 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
|
|
jbe@0
|
600
|
|
jbe@0
|
601
|
|
jbe@0
|
602
|
|
jbe@0
|
603 --------------------------------------------------------------------
|
|
jbe@0
|
604 -- Auto-retrieval of fields only needed for referential integrity --
|
|
jbe@0
|
605 --------------------------------------------------------------------
|
|
jbe@0
|
606
|
|
jbe@0
|
607 CREATE FUNCTION "autofill_issue_id_trigger"()
|
|
jbe@0
|
608 RETURNS TRIGGER
|
|
jbe@0
|
609 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
610 BEGIN
|
|
jbe@0
|
611 IF NEW."issue_id" ISNULL THEN
|
|
jbe@0
|
612 SELECT "issue_id" INTO NEW."issue_id"
|
|
jbe@0
|
613 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
|
jbe@0
|
614 END IF;
|
|
jbe@0
|
615 RETURN NEW;
|
|
jbe@0
|
616 END;
|
|
jbe@0
|
617 $$;
|
|
jbe@0
|
618
|
|
jbe@0
|
619 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
|
|
jbe@0
|
620 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
|
jbe@0
|
621
|
|
jbe@0
|
622 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
|
|
jbe@0
|
623 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
|
jbe@0
|
624
|
|
jbe@0
|
625 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
|
|
jbe@0
|
626 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
|
|
jbe@0
|
627 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
|
|
jbe@0
|
628
|
|
jbe@0
|
629
|
|
jbe@0
|
630 CREATE FUNCTION "autofill_initiative_id_trigger"()
|
|
jbe@0
|
631 RETURNS TRIGGER
|
|
jbe@0
|
632 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
633 BEGIN
|
|
jbe@0
|
634 IF NEW."initiative_id" ISNULL THEN
|
|
jbe@0
|
635 SELECT "initiative_id" INTO NEW."initiative_id"
|
|
jbe@0
|
636 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
|
jbe@0
|
637 END IF;
|
|
jbe@0
|
638 RETURN NEW;
|
|
jbe@0
|
639 END;
|
|
jbe@0
|
640 $$;
|
|
jbe@0
|
641
|
|
jbe@0
|
642 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
|
|
jbe@0
|
643 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
|
|
jbe@0
|
644
|
|
jbe@0
|
645 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
|
|
jbe@0
|
646 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
|
|
jbe@0
|
647
|
|
jbe@0
|
648
|
|
jbe@0
|
649
|
|
jbe@0
|
650 -----------------------------------------------------------------------
|
|
jbe@0
|
651 -- Automatic copy of autoreject settings from membership to interest --
|
|
jbe@0
|
652 -----------------------------------------------------------------------
|
|
jbe@0
|
653
|
|
jbe@0
|
654 CREATE FUNCTION "copy_autoreject_trigger"()
|
|
jbe@0
|
655 RETURNS TRIGGER
|
|
jbe@0
|
656 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
657 BEGIN
|
|
jbe@0
|
658 IF NEW."autoreject" ISNULL THEN
|
|
jbe@0
|
659 SELECT "membership"."autoreject" INTO NEW."autoreject"
|
|
jbe@0
|
660 FROM "issue" JOIN "membership"
|
|
jbe@0
|
661 ON "issue"."area_id" = "membership"."area_id"
|
|
jbe@0
|
662 WHERE "issue"."id" = NEW."issue_id"
|
|
jbe@0
|
663 AND "membership"."member_id" = NEW."member_id";
|
|
jbe@0
|
664 END IF;
|
|
jbe@0
|
665 IF NEW."autoreject" ISNULL THEN
|
|
jbe@0
|
666 NEW."autoreject" := FALSE;
|
|
jbe@0
|
667 END IF;
|
|
jbe@0
|
668 RETURN NEW;
|
|
jbe@0
|
669 END;
|
|
jbe@0
|
670 $$;
|
|
jbe@0
|
671
|
|
jbe@0
|
672 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
|
|
jbe@0
|
673 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
|
|
jbe@0
|
674
|
|
jbe@0
|
675 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
|
|
jbe@0
|
676 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
|
|
jbe@0
|
677
|
|
jbe@0
|
678
|
|
jbe@0
|
679
|
|
jbe@0
|
680 ----------------------------------------
|
|
jbe@0
|
681 -- Automatic creation of dependencies --
|
|
jbe@0
|
682 ----------------------------------------
|
|
jbe@0
|
683
|
|
jbe@0
|
684 CREATE FUNCTION "autocreate_interest_trigger"()
|
|
jbe@0
|
685 RETURNS TRIGGER
|
|
jbe@0
|
686 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
687 BEGIN
|
|
jbe@0
|
688 IF NOT EXISTS (
|
|
jbe@0
|
689 SELECT NULL FROM "initiative" JOIN "interest"
|
|
jbe@0
|
690 ON "initiative"."issue_id" = "interest"."issue_id"
|
|
jbe@0
|
691 WHERE "initiative"."id" = NEW."initiative_id"
|
|
jbe@0
|
692 AND "interest"."member_id" = NEW."member_id"
|
|
jbe@0
|
693 ) THEN
|
|
jbe@0
|
694 BEGIN
|
|
jbe@0
|
695 INSERT INTO "interest" ("issue_id", "member_id")
|
|
jbe@0
|
696 SELECT "issue_id", NEW."member_id"
|
|
jbe@0
|
697 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
|
jbe@0
|
698 EXCEPTION WHEN unique_violation THEN END;
|
|
jbe@0
|
699 END IF;
|
|
jbe@0
|
700 RETURN NEW;
|
|
jbe@0
|
701 END;
|
|
jbe@0
|
702 $$;
|
|
jbe@0
|
703
|
|
jbe@0
|
704 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
|
|
jbe@0
|
705 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
|
|
jbe@0
|
706
|
|
jbe@0
|
707 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
|
|
jbe@0
|
708 COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
|
|
jbe@0
|
709
|
|
jbe@0
|
710
|
|
jbe@0
|
711 CREATE FUNCTION "autocreate_supporter_trigger"()
|
|
jbe@0
|
712 RETURNS TRIGGER
|
|
jbe@0
|
713 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
714 BEGIN
|
|
jbe@0
|
715 IF NOT EXISTS (
|
|
jbe@0
|
716 SELECT NULL FROM "suggestion" JOIN "supporter"
|
|
jbe@0
|
717 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@0
|
718 WHERE "suggestion"."id" = NEW."suggestion_id"
|
|
jbe@0
|
719 AND "supporter"."member_id" = NEW."member_id"
|
|
jbe@0
|
720 ) THEN
|
|
jbe@0
|
721 BEGIN
|
|
jbe@0
|
722 INSERT INTO "supporter" ("initiative_id", "member_id")
|
|
jbe@0
|
723 SELECT "initiative_id", NEW."member_id"
|
|
jbe@0
|
724 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
|
jbe@0
|
725 EXCEPTION WHEN unique_violation THEN END;
|
|
jbe@0
|
726 END IF;
|
|
jbe@0
|
727 RETURN NEW;
|
|
jbe@0
|
728 END;
|
|
jbe@0
|
729 $$;
|
|
jbe@0
|
730
|
|
jbe@0
|
731 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
|
|
jbe@0
|
732 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
|
|
jbe@0
|
733
|
|
jbe@0
|
734 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
|
|
jbe@0
|
735 COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
|
|
jbe@0
|
736
|
|
jbe@0
|
737
|
|
jbe@0
|
738
|
|
jbe@0
|
739 ------------------------------------------
|
|
jbe@0
|
740 -- Views and helper functions for views --
|
|
jbe@0
|
741 ------------------------------------------
|
|
jbe@0
|
742
|
|
jbe@0
|
743 CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS
|
|
jbe@0
|
744 SELECT "delegation".*, "issue"."id" AS "resulting_issue_id"
|
|
jbe@0
|
745 FROM "delegation"
|
|
jbe@0
|
746 JOIN "issue" ON
|
|
jbe@0
|
747 ("delegation"."area_id" ISNULL AND "delegation"."issue_id" ISNULL) OR
|
|
jbe@0
|
748 "delegation"."area_id" = "issue"."area_id" OR
|
|
jbe@0
|
749 "delegation"."issue_id" = "issue"."id";
|
|
jbe@0
|
750
|
|
jbe@0
|
751 COMMENT ON VIEW "issue_delegation_with_overridden_and_inactive" IS 'Helper view for "issue_delegation"';
|
|
jbe@0
|
752
|
|
jbe@0
|
753
|
|
jbe@0
|
754 CREATE VIEW "issue_delegation" AS
|
|
jbe@0
|
755 SELECT
|
|
jbe@0
|
756 "entry"."id" AS "id",
|
|
jbe@0
|
757 "entry"."truster_id" AS "truster_id",
|
|
jbe@0
|
758 "entry"."trustee_id" AS "trustee_id",
|
|
jbe@0
|
759 "entry"."resulting_issue_id" AS "issue_id"
|
|
jbe@0
|
760 FROM "issue_delegation_with_overridden_and_inactive" AS "entry"
|
|
jbe@0
|
761 JOIN "member" AS "truster" ON "entry"."truster_id" = "truster"."id"
|
|
jbe@0
|
762 JOIN "member" AS "trustee" ON "entry"."trustee_id" = "trustee"."id"
|
|
jbe@0
|
763 LEFT JOIN "issue_delegation_with_overridden_and_inactive" AS "override"
|
|
jbe@0
|
764 ON "entry"."truster_id" = "override"."truster_id"
|
|
jbe@0
|
765 AND "entry"."id" != "override"."id"
|
|
jbe@0
|
766 AND (
|
|
jbe@0
|
767 ("entry"."area_id" ISNULL AND "entry"."issue_id" ISNULL) OR
|
|
jbe@0
|
768 "override"."issue_id" NOTNULL
|
|
jbe@0
|
769 )
|
|
jbe@0
|
770 WHERE "truster"."active" AND "trustee"."active"
|
|
jbe@0
|
771 AND "override"."truster_id" ISNULL;
|
|
jbe@0
|
772
|
|
jbe@0
|
773 COMMENT ON VIEW "issue_delegation" IS 'Resulting delegations for issues, without those involving inactive members';
|
|
jbe@0
|
774
|
|
jbe@0
|
775
|
|
jbe@0
|
776 CREATE VIEW "current_draft" AS
|
|
jbe@0
|
777 SELECT "draft".* FROM (
|
|
jbe@0
|
778 SELECT
|
|
jbe@0
|
779 "initiative"."id" AS "initiative_id",
|
|
jbe@0
|
780 max("draft"."id") AS "draft_id"
|
|
jbe@0
|
781 FROM "initiative" JOIN "draft"
|
|
jbe@0
|
782 ON "initiative"."id" = "draft"."initiative_id"
|
|
jbe@0
|
783 GROUP BY "initiative"."id"
|
|
jbe@0
|
784 ) AS "subquery"
|
|
jbe@0
|
785 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
|
|
jbe@0
|
786
|
|
jbe@0
|
787 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
|
|
jbe@0
|
788
|
|
jbe@0
|
789
|
|
jbe@0
|
790 CREATE VIEW "critical_opinion" AS
|
|
jbe@0
|
791 SELECT * FROM "opinion"
|
|
jbe@0
|
792 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
|
|
jbe@0
|
793 OR ("degree" = -2 AND "fulfilled" = TRUE);
|
|
jbe@0
|
794
|
|
jbe@0
|
795 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
|
|
jbe@0
|
796
|
|
jbe@0
|
797
|
|
jbe@0
|
798 CREATE VIEW "battle_participant" AS
|
|
jbe@0
|
799 SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
|
|
jbe@0
|
800 WHERE "admitted"
|
|
jbe@0
|
801 AND "positive_votes" > "negative_votes";
|
|
jbe@0
|
802
|
|
jbe@0
|
803 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
|
|
jbe@0
|
804
|
|
jbe@0
|
805
|
|
jbe@0
|
806 CREATE VIEW "battle" AS
|
|
jbe@0
|
807 SELECT
|
|
jbe@0
|
808 "issue"."id" AS "issue_id",
|
|
jbe@0
|
809 "winning_initiative"."initiative_id" AS "winning_initiative_id",
|
|
jbe@0
|
810 "losing_initiative"."initiative_id" AS "losing_initiative_id",
|
|
jbe@0
|
811 sum(
|
|
jbe@0
|
812 CASE WHEN
|
|
jbe@0
|
813 coalesce("better_vote"."grade", 0) >
|
|
jbe@0
|
814 coalesce("worse_vote"."grade", 0)
|
|
jbe@0
|
815 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@0
|
816 ) AS "count"
|
|
jbe@0
|
817 FROM "issue"
|
|
jbe@0
|
818 LEFT JOIN "direct_voter"
|
|
jbe@0
|
819 ON "issue"."id" = "direct_voter"."issue_id"
|
|
jbe@0
|
820 JOIN "battle_participant" AS "winning_initiative"
|
|
jbe@0
|
821 ON "issue"."id" = "winning_initiative"."issue_id"
|
|
jbe@0
|
822 JOIN "battle_participant" AS "losing_initiative"
|
|
jbe@0
|
823 ON "issue"."id" = "losing_initiative"."issue_id"
|
|
jbe@0
|
824 LEFT JOIN "vote" AS "better_vote"
|
|
jbe@0
|
825 ON "direct_voter"."member_id" = "better_vote"."member_id"
|
|
jbe@0
|
826 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
|
|
jbe@0
|
827 LEFT JOIN "vote" AS "worse_vote"
|
|
jbe@0
|
828 ON "direct_voter"."member_id" = "worse_vote"."member_id"
|
|
jbe@0
|
829 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
|
|
jbe@0
|
830 WHERE
|
|
jbe@0
|
831 "winning_initiative"."initiative_id" !=
|
|
jbe@0
|
832 "losing_initiative"."initiative_id"
|
|
jbe@0
|
833 GROUP BY
|
|
jbe@0
|
834 "issue"."id",
|
|
jbe@0
|
835 "winning_initiative"."initiative_id",
|
|
jbe@0
|
836 "losing_initiative"."initiative_id";
|
|
jbe@0
|
837
|
|
jbe@0
|
838 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
|
|
jbe@0
|
839
|
|
jbe@0
|
840
|
|
jbe@0
|
841 CREATE VIEW "open_issue" AS
|
|
jbe@0
|
842 SELECT * FROM "issue" WHERE "closed" ISNULL;
|
|
jbe@0
|
843
|
|
jbe@0
|
844 COMMENT ON VIEW "open_issue" IS 'All open issues';
|
|
jbe@0
|
845
|
|
jbe@0
|
846
|
|
jbe@0
|
847 CREATE VIEW "issue_with_ranks_missing" AS
|
|
jbe@0
|
848 SELECT * FROM "issue"
|
|
jbe@0
|
849 WHERE "frozen" NOTNULL
|
|
jbe@0
|
850 AND "closed" NOTNULL
|
|
jbe@0
|
851 AND "ranks_available" = FALSE;
|
|
jbe@0
|
852
|
|
jbe@0
|
853 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
|
|
jbe@0
|
854
|
|
jbe@0
|
855
|
|
jbe@0
|
856
|
|
jbe@0
|
857 ------------------------------
|
|
jbe@0
|
858 -- Comparison by vote count --
|
|
jbe@0
|
859 ------------------------------
|
|
jbe@0
|
860
|
|
jbe@0
|
861 CREATE FUNCTION "vote_ratio"
|
|
jbe@0
|
862 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
|
|
jbe@0
|
863 "negative_votes_p" "initiative"."negative_votes"%TYPE )
|
|
jbe@0
|
864 RETURNS FLOAT8
|
|
jbe@0
|
865 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@0
|
866 DECLARE
|
|
jbe@0
|
867 "total_v" INT4;
|
|
jbe@0
|
868 BEGIN
|
|
jbe@0
|
869 "total_v" := "positive_votes_p" + "negative_votes_p";
|
|
jbe@0
|
870 IF "total_v" > 0 THEN
|
|
jbe@0
|
871 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
|
|
jbe@0
|
872 ELSE
|
|
jbe@0
|
873 RETURN 0.5;
|
|
jbe@0
|
874 END IF;
|
|
jbe@0
|
875 END;
|
|
jbe@0
|
876 $$;
|
|
jbe@0
|
877
|
|
jbe@0
|
878 COMMENT ON FUNCTION "vote_ratio"
|
|
jbe@0
|
879 ( "initiative"."positive_votes"%TYPE,
|
|
jbe@0
|
880 "initiative"."negative_votes"%TYPE )
|
|
jbe@0
|
881 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
|
|
jbe@0
|
882
|
|
jbe@0
|
883
|
|
jbe@0
|
884
|
|
jbe@0
|
885 ------------------------------------------------
|
|
jbe@0
|
886 -- Locking for snapshots and voting procedure --
|
|
jbe@0
|
887 ------------------------------------------------
|
|
jbe@0
|
888
|
|
jbe@0
|
889 CREATE FUNCTION "global_lock"() RETURNS VOID
|
|
jbe@0
|
890 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
891 BEGIN
|
|
jbe@0
|
892 -- NOTE: PostgreSQL allows reading, while tables are locked in
|
|
jbe@0
|
893 -- exclusive move. Transactions should be kept short anyway!
|
|
jbe@0
|
894 LOCK TABLE "member" IN EXCLUSIVE MODE;
|
|
jbe@0
|
895 LOCK TABLE "policy" IN EXCLUSIVE MODE;
|
|
jbe@0
|
896 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
|
jbe@0
|
897 LOCK TABLE "issue" IN EXCLUSIVE MODE;
|
|
jbe@0
|
898 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
|
|
jbe@0
|
899 LOCK TABLE "draft" IN EXCLUSIVE MODE;
|
|
jbe@0
|
900 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
|
|
jbe@0
|
901 LOCK TABLE "membership" IN EXCLUSIVE MODE;
|
|
jbe@0
|
902 LOCK TABLE "interest" IN EXCLUSIVE MODE;
|
|
jbe@0
|
903 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
|
|
jbe@0
|
904 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
|
|
jbe@0
|
905 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
|
|
jbe@0
|
906 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
|
|
jbe@0
|
907 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
908 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
909 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
910 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
911 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
912 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
|
|
jbe@0
|
913 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
|
|
jbe@0
|
914 LOCK TABLE "vote" IN EXCLUSIVE MODE;
|
|
jbe@0
|
915 RETURN;
|
|
jbe@0
|
916 END;
|
|
jbe@0
|
917 $$;
|
|
jbe@0
|
918
|
|
jbe@0
|
919 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
|
|
jbe@0
|
920
|
|
jbe@0
|
921
|
|
jbe@0
|
922
|
|
jbe@0
|
923 ------------------------------
|
|
jbe@0
|
924 -- Calculation of snapshots --
|
|
jbe@0
|
925 ------------------------------
|
|
jbe@0
|
926
|
|
jbe@0
|
927 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
|
jbe@0
|
928 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
929 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
930 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
931 RETURNS "direct_population_snapshot"."weight"%TYPE
|
|
jbe@0
|
932 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
933 DECLARE
|
|
jbe@0
|
934 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
935 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
936 "weight_v" INT4;
|
|
jbe@0
|
937 BEGIN
|
|
jbe@0
|
938 "weight_v" := 0;
|
|
jbe@0
|
939 FOR "issue_delegation_row" IN
|
|
jbe@0
|
940 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
941 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
942 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
943 LOOP
|
|
jbe@0
|
944 IF NOT EXISTS (
|
|
jbe@0
|
945 SELECT NULL FROM "direct_population_snapshot"
|
|
jbe@0
|
946 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
947 AND "event" = 'periodic'
|
|
jbe@0
|
948 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
949 ) AND NOT EXISTS (
|
|
jbe@0
|
950 SELECT NULL FROM "delegating_population_snapshot"
|
|
jbe@0
|
951 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
952 AND "event" = 'periodic'
|
|
jbe@0
|
953 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
954 ) THEN
|
|
jbe@0
|
955 "delegate_member_ids_v" :=
|
|
jbe@0
|
956 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@0
|
957 INSERT INTO "delegating_population_snapshot"
|
|
jbe@0
|
958 ("issue_id", "event", "member_id", "delegate_member_ids")
|
|
jbe@0
|
959 VALUES (
|
|
jbe@0
|
960 "issue_id_p",
|
|
jbe@0
|
961 'periodic',
|
|
jbe@0
|
962 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
963 "delegate_member_ids_v"
|
|
jbe@0
|
964 );
|
|
jbe@0
|
965 "weight_v" := "weight_v" + 1 +
|
|
jbe@0
|
966 "weight_of_added_delegations_for_population_snapshot"(
|
|
jbe@0
|
967 "issue_id_p",
|
|
jbe@0
|
968 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
969 "delegate_member_ids_v"
|
|
jbe@0
|
970 );
|
|
jbe@0
|
971 END IF;
|
|
jbe@0
|
972 END LOOP;
|
|
jbe@0
|
973 RETURN "weight_v";
|
|
jbe@0
|
974 END;
|
|
jbe@0
|
975 $$;
|
|
jbe@0
|
976
|
|
jbe@0
|
977 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
|
jbe@0
|
978 ( "issue"."id"%TYPE,
|
|
jbe@0
|
979 "member"."id"%TYPE,
|
|
jbe@0
|
980 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
981 IS 'Helper function for "create_population_snapshot" function';
|
|
jbe@0
|
982
|
|
jbe@0
|
983
|
|
jbe@0
|
984 CREATE FUNCTION "create_population_snapshot"
|
|
jbe@0
|
985 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
986 RETURNS VOID
|
|
jbe@0
|
987 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
988 DECLARE
|
|
jbe@0
|
989 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
990 BEGIN
|
|
jbe@0
|
991 DELETE FROM "direct_population_snapshot"
|
|
jbe@0
|
992 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
993 AND "event" = 'periodic';
|
|
jbe@0
|
994 DELETE FROM "delegating_population_snapshot"
|
|
jbe@0
|
995 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
996 AND "event" = 'periodic';
|
|
jbe@0
|
997 INSERT INTO "direct_population_snapshot"
|
|
jbe@0
|
998 ("issue_id", "event", "member_id", "interest_exists")
|
|
jbe@0
|
999 SELECT DISTINCT ON ("issue_id", "member_id")
|
|
jbe@0
|
1000 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
1001 'periodic' AS "event",
|
|
jbe@0
|
1002 "subquery"."member_id",
|
|
jbe@0
|
1003 "subquery"."interest_exists"
|
|
jbe@0
|
1004 FROM (
|
|
jbe@0
|
1005 SELECT
|
|
jbe@0
|
1006 "member"."id" AS "member_id",
|
|
jbe@0
|
1007 FALSE AS "interest_exists"
|
|
jbe@0
|
1008 FROM "issue"
|
|
jbe@0
|
1009 JOIN "area" ON "issue"."area_id" = "area"."id"
|
|
jbe@0
|
1010 JOIN "membership" ON "area"."id" = "membership"."area_id"
|
|
jbe@0
|
1011 JOIN "member" ON "membership"."member_id" = "member"."id"
|
|
jbe@0
|
1012 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@0
|
1013 AND "member"."active"
|
|
jbe@0
|
1014 UNION
|
|
jbe@0
|
1015 SELECT
|
|
jbe@0
|
1016 "member"."id" AS "member_id",
|
|
jbe@0
|
1017 TRUE AS "interest_exists"
|
|
jbe@0
|
1018 FROM "interest" JOIN "member"
|
|
jbe@0
|
1019 ON "interest"."member_id" = "member"."id"
|
|
jbe@0
|
1020 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1021 AND "member"."active"
|
|
jbe@0
|
1022 ) AS "subquery"
|
|
jbe@0
|
1023 ORDER BY
|
|
jbe@0
|
1024 "issue_id_p",
|
|
jbe@0
|
1025 "subquery"."member_id",
|
|
jbe@0
|
1026 "subquery"."interest_exists" DESC;
|
|
jbe@0
|
1027 FOR "member_id_v" IN
|
|
jbe@0
|
1028 SELECT "member_id" FROM "direct_population_snapshot"
|
|
jbe@0
|
1029 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1030 AND "event" = 'periodic'
|
|
jbe@0
|
1031 LOOP
|
|
jbe@0
|
1032 UPDATE "direct_population_snapshot" SET
|
|
jbe@0
|
1033 "weight" = 1 +
|
|
jbe@0
|
1034 "weight_of_added_delegations_for_population_snapshot"(
|
|
jbe@0
|
1035 "issue_id_p",
|
|
jbe@0
|
1036 "member_id_v",
|
|
jbe@0
|
1037 '{}'
|
|
jbe@0
|
1038 )
|
|
jbe@0
|
1039 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1040 AND "event" = 'periodic'
|
|
jbe@0
|
1041 AND "member_id" = "member_id_v";
|
|
jbe@0
|
1042 END LOOP;
|
|
jbe@0
|
1043 RETURN;
|
|
jbe@0
|
1044 END;
|
|
jbe@0
|
1045 $$;
|
|
jbe@0
|
1046
|
|
jbe@0
|
1047 COMMENT ON FUNCTION "create_population_snapshot"
|
|
jbe@0
|
1048 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1049 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
|
|
jbe@0
|
1050
|
|
jbe@0
|
1051
|
|
jbe@0
|
1052 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
|
jbe@0
|
1053 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
1054 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
1055 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
1056 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
|
jbe@0
|
1057 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1058 DECLARE
|
|
jbe@0
|
1059 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
1060 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
1061 "weight_v" INT4;
|
|
jbe@0
|
1062 BEGIN
|
|
jbe@0
|
1063 "weight_v" := 0;
|
|
jbe@0
|
1064 FOR "issue_delegation_row" IN
|
|
jbe@0
|
1065 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
1066 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
1067 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
1068 LOOP
|
|
jbe@0
|
1069 IF NOT EXISTS (
|
|
jbe@0
|
1070 SELECT NULL FROM "direct_interest_snapshot"
|
|
jbe@0
|
1071 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1072 AND "event" = 'periodic'
|
|
jbe@0
|
1073 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
1074 ) AND NOT EXISTS (
|
|
jbe@0
|
1075 SELECT NULL FROM "delegating_interest_snapshot"
|
|
jbe@0
|
1076 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1077 AND "event" = 'periodic'
|
|
jbe@0
|
1078 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
1079 ) THEN
|
|
jbe@0
|
1080 "delegate_member_ids_v" :=
|
|
jbe@0
|
1081 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@0
|
1082 INSERT INTO "delegating_interest_snapshot"
|
|
jbe@0
|
1083 ("issue_id", "event", "member_id", "delegate_member_ids")
|
|
jbe@0
|
1084 VALUES (
|
|
jbe@0
|
1085 "issue_id_p",
|
|
jbe@0
|
1086 'periodic',
|
|
jbe@0
|
1087 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
1088 "delegate_member_ids_v"
|
|
jbe@0
|
1089 );
|
|
jbe@0
|
1090 "weight_v" := "weight_v" + 1 +
|
|
jbe@0
|
1091 "weight_of_added_delegations_for_interest_snapshot"(
|
|
jbe@0
|
1092 "issue_id_p",
|
|
jbe@0
|
1093 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
1094 "delegate_member_ids_v"
|
|
jbe@0
|
1095 );
|
|
jbe@0
|
1096 END IF;
|
|
jbe@0
|
1097 END LOOP;
|
|
jbe@0
|
1098 RETURN "weight_v";
|
|
jbe@0
|
1099 END;
|
|
jbe@0
|
1100 $$;
|
|
jbe@0
|
1101
|
|
jbe@0
|
1102 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
|
jbe@0
|
1103 ( "issue"."id"%TYPE,
|
|
jbe@0
|
1104 "member"."id"%TYPE,
|
|
jbe@0
|
1105 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
1106 IS 'Helper function for "create_interest_snapshot" function';
|
|
jbe@0
|
1107
|
|
jbe@0
|
1108
|
|
jbe@0
|
1109 CREATE FUNCTION "create_interest_snapshot"
|
|
jbe@0
|
1110 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1111 RETURNS VOID
|
|
jbe@0
|
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1113 DECLARE
|
|
jbe@0
|
1114 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
1115 BEGIN
|
|
jbe@0
|
1116 DELETE FROM "direct_interest_snapshot"
|
|
jbe@0
|
1117 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1118 AND "event" = 'periodic';
|
|
jbe@0
|
1119 DELETE FROM "delegating_interest_snapshot"
|
|
jbe@0
|
1120 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1121 AND "event" = 'periodic';
|
|
jbe@0
|
1122 DELETE FROM "direct_supporter_snapshot"
|
|
jbe@0
|
1123 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1124 AND "event" = 'periodic';
|
|
jbe@0
|
1125 INSERT INTO "direct_interest_snapshot"
|
|
jbe@0
|
1126 ("issue_id", "event", "member_id", "voting_requested")
|
|
jbe@0
|
1127 SELECT
|
|
jbe@0
|
1128 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
1129 'periodic' AS "event",
|
|
jbe@0
|
1130 "member"."id" AS "member_id",
|
|
jbe@0
|
1131 "interest"."voting_requested"
|
|
jbe@0
|
1132 FROM "interest" JOIN "member"
|
|
jbe@0
|
1133 ON "interest"."member_id" = "member"."id"
|
|
jbe@0
|
1134 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1135 AND "member"."active";
|
|
jbe@0
|
1136 FOR "member_id_v" IN
|
|
jbe@0
|
1137 SELECT "member_id" FROM "direct_interest_snapshot"
|
|
jbe@0
|
1138 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1139 AND "event" = 'periodic'
|
|
jbe@0
|
1140 LOOP
|
|
jbe@0
|
1141 UPDATE "direct_interest_snapshot" SET
|
|
jbe@0
|
1142 "weight" = 1 +
|
|
jbe@0
|
1143 "weight_of_added_delegations_for_interest_snapshot"(
|
|
jbe@0
|
1144 "issue_id_p",
|
|
jbe@0
|
1145 "member_id_v",
|
|
jbe@0
|
1146 '{}'
|
|
jbe@0
|
1147 )
|
|
jbe@0
|
1148 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1149 AND "event" = 'periodic'
|
|
jbe@0
|
1150 AND "member_id" = "member_id_v";
|
|
jbe@0
|
1151 END LOOP;
|
|
jbe@0
|
1152 INSERT INTO "direct_supporter_snapshot"
|
|
jbe@0
|
1153 ( "issue_id", "initiative_id", "event", "member_id",
|
|
jbe@0
|
1154 "informed", "satisfied" )
|
|
jbe@0
|
1155 SELECT
|
|
jbe@0
|
1156 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
1157 "initiative"."id" AS "initiative_id",
|
|
jbe@0
|
1158 'periodic' AS "event",
|
|
jbe@0
|
1159 "member"."id" AS "member_id",
|
|
jbe@0
|
1160 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
|
jbe@0
|
1161 NOT EXISTS (
|
|
jbe@0
|
1162 SELECT NULL FROM "critical_opinion"
|
|
jbe@0
|
1163 WHERE "initiative_id" = "initiative"."id"
|
|
jbe@0
|
1164 AND "member_id" = "member"."id"
|
|
jbe@0
|
1165 ) AS "satisfied"
|
|
jbe@0
|
1166 FROM "supporter"
|
|
jbe@0
|
1167 JOIN "member"
|
|
jbe@0
|
1168 ON "supporter"."member_id" = "member"."id"
|
|
jbe@0
|
1169 JOIN "initiative"
|
|
jbe@0
|
1170 ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@0
|
1171 JOIN "current_draft"
|
|
jbe@0
|
1172 ON "initiative"."id" = "current_draft"."initiative_id"
|
|
jbe@0
|
1173 JOIN "direct_interest_snapshot"
|
|
jbe@0
|
1174 ON "member"."id" = "direct_interest_snapshot"."member_id"
|
|
jbe@0
|
1175 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
|
jbe@0
|
1176 WHERE "member"."active"
|
|
jbe@0
|
1177 AND "initiative"."issue_id" = "issue_id_p";
|
|
jbe@0
|
1178 RETURN;
|
|
jbe@0
|
1179 END;
|
|
jbe@0
|
1180 $$;
|
|
jbe@0
|
1181
|
|
jbe@0
|
1182 COMMENT ON FUNCTION "create_interest_snapshot"
|
|
jbe@0
|
1183 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1184 IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
|
|
jbe@0
|
1185
|
|
jbe@0
|
1186
|
|
jbe@0
|
1187 CREATE FUNCTION "create_snapshot"
|
|
jbe@0
|
1188 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1189 RETURNS VOID
|
|
jbe@0
|
1190 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1191 DECLARE
|
|
jbe@0
|
1192 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@0
|
1193 "suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@0
|
1194 BEGIN
|
|
jbe@0
|
1195 PERFORM "global_lock"();
|
|
jbe@0
|
1196 PERFORM "create_population_snapshot"("issue_id_p");
|
|
jbe@0
|
1197 PERFORM "create_interest_snapshot"("issue_id_p");
|
|
jbe@0
|
1198 UPDATE "issue" SET
|
|
jbe@0
|
1199 "snapshot" = now(),
|
|
jbe@0
|
1200 "population" = (
|
|
jbe@0
|
1201 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
1202 FROM "direct_population_snapshot"
|
|
jbe@0
|
1203 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1204 AND "event" = 'periodic'
|
|
jbe@0
|
1205 ),
|
|
jbe@0
|
1206 "vote_now" = (
|
|
jbe@0
|
1207 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
1208 FROM "direct_interest_snapshot"
|
|
jbe@0
|
1209 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1210 AND "event" = 'periodic'
|
|
jbe@0
|
1211 AND "voting_requested" = TRUE
|
|
jbe@0
|
1212 ),
|
|
jbe@0
|
1213 "vote_later" = (
|
|
jbe@0
|
1214 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
1215 FROM "direct_interest_snapshot"
|
|
jbe@0
|
1216 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1217 AND "event" = 'periodic'
|
|
jbe@0
|
1218 AND "voting_requested" = FALSE
|
|
jbe@0
|
1219 )
|
|
jbe@0
|
1220 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1221 FOR "initiative_id_v" IN
|
|
jbe@0
|
1222 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1223 LOOP
|
|
jbe@0
|
1224 UPDATE "initiative" SET
|
|
jbe@0
|
1225 "supporter_count" = (
|
|
jbe@0
|
1226 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
1227 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
1228 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
1229 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
1230 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1231 AND "di"."event" = 'periodic'
|
|
jbe@0
|
1232 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1233 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
1234 ),
|
|
jbe@0
|
1235 "informed_supporter_count" = (
|
|
jbe@0
|
1236 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
1237 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
1238 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
1239 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
1240 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1241 AND "di"."event" = 'periodic'
|
|
jbe@0
|
1242 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1243 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
1244 AND "ds"."informed"
|
|
jbe@0
|
1245 ),
|
|
jbe@0
|
1246 "satisfied_supporter_count" = (
|
|
jbe@0
|
1247 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
1248 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
1249 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
1250 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
1251 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1252 AND "di"."event" = 'periodic'
|
|
jbe@0
|
1253 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1254 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
1255 AND "ds"."satisfied"
|
|
jbe@0
|
1256 ),
|
|
jbe@0
|
1257 "satisfied_informed_supporter_count" = (
|
|
jbe@0
|
1258 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
1259 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
1260 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
1261 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
1262 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1263 AND "di"."event" = 'periodic'
|
|
jbe@0
|
1264 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1265 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
1266 AND "ds"."informed"
|
|
jbe@0
|
1267 AND "ds"."satisfied"
|
|
jbe@0
|
1268 )
|
|
jbe@0
|
1269 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
1270 FOR "suggestion_id_v" IN
|
|
jbe@0
|
1271 SELECT "id" FROM "suggestion"
|
|
jbe@0
|
1272 WHERE "initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1273 LOOP
|
|
jbe@0
|
1274 UPDATE "suggestion" SET
|
|
jbe@0
|
1275 "minus2_unfulfilled_count" = (
|
|
jbe@0
|
1276 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1277 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1278 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1279 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1280 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1281 AND "opinion"."degree" = -2
|
|
jbe@0
|
1282 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
1283 ),
|
|
jbe@0
|
1284 "minus2_fulfilled_count" = (
|
|
jbe@0
|
1285 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1286 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1287 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1288 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1289 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1290 AND "opinion"."degree" = -2
|
|
jbe@0
|
1291 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
1292 ),
|
|
jbe@0
|
1293 "minus1_unfulfilled_count" = (
|
|
jbe@0
|
1294 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1295 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1296 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1297 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1298 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1299 AND "opinion"."degree" = -1
|
|
jbe@0
|
1300 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
1301 ),
|
|
jbe@0
|
1302 "minus1_fulfilled_count" = (
|
|
jbe@0
|
1303 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1304 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1305 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1306 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1307 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1308 AND "opinion"."degree" = -1
|
|
jbe@0
|
1309 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
1310 ),
|
|
jbe@0
|
1311 "plus1_unfulfilled_count" = (
|
|
jbe@0
|
1312 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1313 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1314 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1315 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1316 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1317 AND "opinion"."degree" = 1
|
|
jbe@0
|
1318 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
1319 ),
|
|
jbe@0
|
1320 "plus1_fulfilled_count" = (
|
|
jbe@0
|
1321 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1322 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1323 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1324 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1325 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1326 AND "opinion"."degree" = 1
|
|
jbe@0
|
1327 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
1328 ),
|
|
jbe@0
|
1329 "plus2_unfulfilled_count" = (
|
|
jbe@0
|
1330 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1331 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1332 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1333 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1334 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1335 AND "opinion"."degree" = 2
|
|
jbe@0
|
1336 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
1337 ),
|
|
jbe@0
|
1338 "plus2_fulfilled_count" = (
|
|
jbe@0
|
1339 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@0
|
1340 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@0
|
1341 ON "opinion"."member_id" = "snapshot"."member_id"
|
|
jbe@0
|
1342 WHERE "opinion"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
1343 AND "snapshot"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1344 AND "opinion"."degree" = 2
|
|
jbe@0
|
1345 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
1346 )
|
|
jbe@0
|
1347 WHERE "suggestion"."id" = "suggestion_id_v";
|
|
jbe@0
|
1348 END LOOP;
|
|
jbe@0
|
1349 END LOOP;
|
|
jbe@0
|
1350 RETURN;
|
|
jbe@0
|
1351 END;
|
|
jbe@0
|
1352 $$;
|
|
jbe@0
|
1353
|
|
jbe@0
|
1354 COMMENT ON FUNCTION "create_snapshot"
|
|
jbe@0
|
1355 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1356 IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
|
|
jbe@0
|
1357
|
|
jbe@0
|
1358
|
|
jbe@0
|
1359 CREATE FUNCTION "set_snapshot_event"
|
|
jbe@0
|
1360 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
1361 "event_p" "snapshot_event" )
|
|
jbe@0
|
1362 RETURNS VOID
|
|
jbe@0
|
1363 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1364 BEGIN
|
|
jbe@0
|
1365 UPDATE "direct_population_snapshot"
|
|
jbe@0
|
1366 SET "event" = 'end_of_discussion'
|
|
jbe@0
|
1367 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
|
|
jbe@0
|
1368 UPDATE "delegating_population_snapshot"
|
|
jbe@0
|
1369 SET "event" = 'end_of_discussion'
|
|
jbe@0
|
1370 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
|
|
jbe@0
|
1371 UPDATE "direct_interest_snapshot"
|
|
jbe@0
|
1372 SET "event" = 'end_of_discussion'
|
|
jbe@0
|
1373 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
|
|
jbe@0
|
1374 UPDATE "delegating_interest_snapshot"
|
|
jbe@0
|
1375 SET "event" = 'end_of_discussion'
|
|
jbe@0
|
1376 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
|
|
jbe@0
|
1377 UPDATE "direct_supporter_snapshot"
|
|
jbe@0
|
1378 SET "event" = 'end_of_discussion'
|
|
jbe@0
|
1379 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
|
|
jbe@0
|
1380 RETURN;
|
|
jbe@0
|
1381 END;
|
|
jbe@0
|
1382 $$;
|
|
jbe@0
|
1383
|
|
jbe@0
|
1384 COMMENT ON FUNCTION "set_snapshot_event"
|
|
jbe@0
|
1385 ( "issue"."id"%TYPE,
|
|
jbe@0
|
1386 "snapshot_event" )
|
|
jbe@0
|
1387 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
|
|
jbe@0
|
1388
|
|
jbe@0
|
1389
|
|
jbe@0
|
1390
|
|
jbe@0
|
1391 ---------------------
|
|
jbe@0
|
1392 -- Freezing issues --
|
|
jbe@0
|
1393 ---------------------
|
|
jbe@0
|
1394
|
|
jbe@0
|
1395 CREATE FUNCTION "freeze_after_snapshot"
|
|
jbe@0
|
1396 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1397 RETURNS VOID
|
|
jbe@0
|
1398 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1399 DECLARE
|
|
jbe@0
|
1400 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
1401 "policy_row" "policy"%ROWTYPE;
|
|
jbe@0
|
1402 "initiative_row" "initiative"%ROWTYPE;
|
|
jbe@0
|
1403 BEGIN
|
|
jbe@0
|
1404 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1405 SELECT * INTO "policy_row"
|
|
jbe@0
|
1406 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
|
jbe@0
|
1407 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_discussion');
|
|
jbe@0
|
1408 UPDATE "issue" SET "frozen" = now() WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1409 FOR "initiative_row" IN
|
|
jbe@0
|
1410 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1411 LOOP
|
|
jbe@0
|
1412 IF
|
|
jbe@0
|
1413 "initiative_row"."satisfied_supporter_count" > 0 AND
|
|
jbe@0
|
1414 "initiative_row"."satisfied_supporter_count" *
|
|
jbe@0
|
1415 "policy_row"."initiative_quorum_den" >=
|
|
jbe@0
|
1416 "issue_row"."population" * "policy_row"."initiative_quorum_num"
|
|
jbe@0
|
1417 THEN
|
|
jbe@0
|
1418 UPDATE "initiative" SET "admitted" = TRUE
|
|
jbe@0
|
1419 WHERE "id" = "initiative_row"."id";
|
|
jbe@0
|
1420 ELSE
|
|
jbe@0
|
1421 UPDATE "initiative" SET "admitted" = FALSE
|
|
jbe@0
|
1422 WHERE "id" = "initiative_row"."id";
|
|
jbe@0
|
1423 END IF;
|
|
jbe@0
|
1424 END LOOP;
|
|
jbe@0
|
1425 RETURN;
|
|
jbe@0
|
1426 END;
|
|
jbe@0
|
1427 $$;
|
|
jbe@0
|
1428
|
|
jbe@0
|
1429 COMMENT ON FUNCTION "freeze_after_snapshot"
|
|
jbe@0
|
1430 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1431 IS 'This function freezes an issue, but must only be called when "create_snapshot" was called in the same transaction';
|
|
jbe@0
|
1432
|
|
jbe@0
|
1433
|
|
jbe@0
|
1434 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
1435 RETURNS VOID
|
|
jbe@0
|
1436 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1437 DECLARE
|
|
jbe@0
|
1438 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
1439 BEGIN
|
|
jbe@0
|
1440 PERFORM "create_snapshot"("issue_id_p");
|
|
jbe@0
|
1441 PERFORM "freeze_after_snapshot"("issue_id_p");
|
|
jbe@0
|
1442 RETURN;
|
|
jbe@0
|
1443 END;
|
|
jbe@0
|
1444 $$;
|
|
jbe@0
|
1445
|
|
jbe@0
|
1446 COMMENT ON FUNCTION "freeze_after_snapshot"
|
|
jbe@0
|
1447 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1448 IS 'Freeze an issue manually';
|
|
jbe@0
|
1449
|
|
jbe@0
|
1450
|
|
jbe@0
|
1451
|
|
jbe@0
|
1452 -----------------------
|
|
jbe@0
|
1453 -- Counting of votes --
|
|
jbe@0
|
1454 -----------------------
|
|
jbe@0
|
1455
|
|
jbe@0
|
1456
|
|
jbe@0
|
1457 CREATE FUNCTION "weight_of_added_delegations"
|
|
jbe@0
|
1458 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
1459 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
1460 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
1461 RETURNS "direct_voter"."weight"%TYPE
|
|
jbe@0
|
1462 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1463 DECLARE
|
|
jbe@0
|
1464 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
1465 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
1466 "weight_v" INT4;
|
|
jbe@0
|
1467 BEGIN
|
|
jbe@0
|
1468 "weight_v" := 0;
|
|
jbe@0
|
1469 FOR "issue_delegation_row" IN
|
|
jbe@0
|
1470 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
1471 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
1472 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
1473 LOOP
|
|
jbe@0
|
1474 IF NOT EXISTS (
|
|
jbe@0
|
1475 SELECT NULL FROM "direct_voter"
|
|
jbe@0
|
1476 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
1477 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
1478 ) AND NOT EXISTS (
|
|
jbe@0
|
1479 SELECT NULL FROM "delegating_voter"
|
|
jbe@0
|
1480 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
1481 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
1482 ) THEN
|
|
jbe@0
|
1483 "delegate_member_ids_v" :=
|
|
jbe@0
|
1484 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@0
|
1485 INSERT INTO "delegating_voter"
|
|
jbe@0
|
1486 ("member_id", "issue_id", "delegate_member_ids")
|
|
jbe@0
|
1487 VALUES (
|
|
jbe@0
|
1488 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
1489 "issue_id_p",
|
|
jbe@0
|
1490 "delegate_member_ids_v"
|
|
jbe@0
|
1491 );
|
|
jbe@0
|
1492 "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"(
|
|
jbe@0
|
1493 "issue_id_p",
|
|
jbe@0
|
1494 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
1495 "delegate_member_ids_v"
|
|
jbe@0
|
1496 );
|
|
jbe@0
|
1497 END IF;
|
|
jbe@0
|
1498 END LOOP;
|
|
jbe@0
|
1499 RETURN "weight_v";
|
|
jbe@0
|
1500 END;
|
|
jbe@0
|
1501 $$;
|
|
jbe@0
|
1502
|
|
jbe@0
|
1503 COMMENT ON FUNCTION "weight_of_added_delegations"
|
|
jbe@0
|
1504 ( "issue"."id"%TYPE,
|
|
jbe@0
|
1505 "member"."id"%TYPE,
|
|
jbe@0
|
1506 "delegating_voter"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
1507 IS 'Helper function for "add_vote_delegations" function';
|
|
jbe@0
|
1508
|
|
jbe@0
|
1509
|
|
jbe@0
|
1510 CREATE FUNCTION "add_vote_delegations"
|
|
jbe@0
|
1511 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1512 RETURNS VOID
|
|
jbe@0
|
1513 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1514 DECLARE
|
|
jbe@0
|
1515 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
1516 BEGIN
|
|
jbe@0
|
1517 FOR "member_id_v" IN
|
|
jbe@0
|
1518 SELECT "member_id" FROM "direct_voter"
|
|
jbe@0
|
1519 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1520 LOOP
|
|
jbe@0
|
1521 UPDATE "direct_voter" SET
|
|
jbe@0
|
1522 "weight" = "weight" + "weight_of_added_delegations"(
|
|
jbe@0
|
1523 "issue_id_p",
|
|
jbe@0
|
1524 "member_id_v",
|
|
jbe@0
|
1525 '{}'
|
|
jbe@0
|
1526 )
|
|
jbe@0
|
1527 WHERE "member_id" = "member_id_v"
|
|
jbe@0
|
1528 AND "issue_id" = "issue_id_p";
|
|
jbe@0
|
1529 END LOOP;
|
|
jbe@0
|
1530 RETURN;
|
|
jbe@0
|
1531 END;
|
|
jbe@0
|
1532 $$;
|
|
jbe@0
|
1533
|
|
jbe@0
|
1534 COMMENT ON FUNCTION "add_vote_delegations"
|
|
jbe@0
|
1535 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1536 IS 'Helper function for "close_voting" function';
|
|
jbe@0
|
1537
|
|
jbe@0
|
1538
|
|
jbe@0
|
1539 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
1540 RETURNS VOID
|
|
jbe@0
|
1541 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1542 DECLARE
|
|
jbe@0
|
1543 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
1544 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
1545 BEGIN
|
|
jbe@0
|
1546 PERFORM "global_lock"();
|
|
jbe@0
|
1547 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1548 DELETE FROM "delegating_voter"
|
|
jbe@0
|
1549 WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
1550 DELETE FROM "direct_voter"
|
|
jbe@0
|
1551 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1552 AND "autoreject" = TRUE;
|
|
jbe@0
|
1553 DELETE FROM "direct_voter" USING "member"
|
|
jbe@0
|
1554 WHERE "direct_voter"."member_id" = "member"."id"
|
|
jbe@0
|
1555 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1556 AND "member"."active" = FALSE;
|
|
jbe@0
|
1557 UPDATE "direct_voter" SET "weight" = 1
|
|
jbe@0
|
1558 WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
1559 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@0
|
1560 FOR "member_id_v" IN
|
|
jbe@0
|
1561 SELECT "interest"."member_id"
|
|
jbe@0
|
1562 FROM "interest"
|
|
jbe@0
|
1563 LEFT JOIN "direct_voter"
|
|
jbe@0
|
1564 ON "interest"."member_id" = "direct_voter"."member_id"
|
|
jbe@0
|
1565 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
|
jbe@0
|
1566 LEFT JOIN "delegating_voter"
|
|
jbe@0
|
1567 ON "interest"."member_id" = "delegating_voter"."member_id"
|
|
jbe@0
|
1568 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
|
jbe@0
|
1569 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1570 AND "interest"."autoreject" = TRUE
|
|
jbe@0
|
1571 AND "direct_voter"."member_id" ISNULL
|
|
jbe@0
|
1572 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@0
|
1573 UNION SELECT "membership"."member_id"
|
|
jbe@0
|
1574 FROM "membership"
|
|
jbe@0
|
1575 LEFT JOIN "interest"
|
|
jbe@0
|
1576 ON "membership"."member_id" = "interest"."member_id"
|
|
jbe@0
|
1577 AND "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1578 LEFT JOIN "direct_voter"
|
|
jbe@0
|
1579 ON "membership"."member_id" = "direct_voter"."member_id"
|
|
jbe@0
|
1580 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1581 LEFT JOIN "delegating_voter"
|
|
jbe@0
|
1582 ON "membership"."member_id" = "delegating_voter"."member_id"
|
|
jbe@0
|
1583 AND "delegating_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1584 WHERE "membership"."area_id" = "issue_row"."area_id"
|
|
jbe@0
|
1585 AND "membership"."autoreject" = TRUE
|
|
jbe@0
|
1586 AND "interest"."autoreject" ISNULL
|
|
jbe@0
|
1587 AND "direct_voter"."member_id" ISNULL
|
|
jbe@0
|
1588 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@0
|
1589 LOOP
|
|
jbe@0
|
1590 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
|
|
jbe@0
|
1591 VALUES ("member_id_v", "issue_id_p", TRUE);
|
|
jbe@0
|
1592 INSERT INTO "vote" (
|
|
jbe@0
|
1593 "member_id",
|
|
jbe@0
|
1594 "issue_id",
|
|
jbe@0
|
1595 "initiative_id",
|
|
jbe@0
|
1596 "grade"
|
|
jbe@0
|
1597 ) SELECT
|
|
jbe@0
|
1598 "member_id_v" AS "member_id",
|
|
jbe@0
|
1599 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
1600 "id" AS "initiative_id",
|
|
jbe@0
|
1601 -1 AS "grade"
|
|
jbe@0
|
1602 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
1603 END LOOP;
|
|
jbe@0
|
1604 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@0
|
1605 UPDATE "initiative" SET
|
|
jbe@0
|
1606 "positive_votes" = "subquery"."positive_votes",
|
|
jbe@0
|
1607 "negative_votes" = "subquery"."negative_votes"
|
|
jbe@0
|
1608 FROM (
|
|
jbe@0
|
1609 SELECT
|
|
jbe@0
|
1610 "initiative_id",
|
|
jbe@0
|
1611 sum(
|
|
jbe@0
|
1612 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@0
|
1613 ) AS "positive_votes",
|
|
jbe@0
|
1614 sum (
|
|
jbe@0
|
1615 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@0
|
1616 ) AS "negative_votes"
|
|
jbe@0
|
1617 FROM "vote" JOIN "direct_voter"
|
|
jbe@0
|
1618 ON "vote"."member_id" = "direct_voter"."member_id"
|
|
jbe@0
|
1619 AND "vote"."issue_id" = "direct_voter"."issue_id"
|
|
jbe@0
|
1620 WHERE "vote"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1621 GROUP BY "initiative_id"
|
|
jbe@0
|
1622 ) AS "subquery"
|
|
jbe@0
|
1623 WHERE "initiative"."admitted"
|
|
jbe@0
|
1624 AND "initiative"."id" = "subquery"."initiative_id";
|
|
jbe@0
|
1625 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1626 END;
|
|
jbe@0
|
1627 $$;
|
|
jbe@0
|
1628
|
|
jbe@0
|
1629 COMMENT ON FUNCTION "close_voting"
|
|
jbe@0
|
1630 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1631 IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
|
|
jbe@0
|
1632
|
|
jbe@0
|
1633
|
|
jbe@0
|
1634 CREATE FUNCTION "init_array"("dim_p" INTEGER)
|
|
jbe@0
|
1635 RETURNS INT4[]
|
|
jbe@0
|
1636 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@0
|
1637 DECLARE
|
|
jbe@0
|
1638 "i" INTEGER;
|
|
jbe@0
|
1639 "ary_text_v" TEXT;
|
|
jbe@0
|
1640 BEGIN
|
|
jbe@0
|
1641 IF "dim_p" >= 1 THEN
|
|
jbe@0
|
1642 "ary_text_v" := '{NULL';
|
|
jbe@0
|
1643 "i" := "dim_p";
|
|
jbe@0
|
1644 LOOP
|
|
jbe@0
|
1645 "i" := "i" - 1;
|
|
jbe@0
|
1646 EXIT WHEN "i" = 0;
|
|
jbe@0
|
1647 "ary_text_v" := "ary_text_v" || ',NULL';
|
|
jbe@0
|
1648 END LOOP;
|
|
jbe@0
|
1649 "ary_text_v" := "ary_text_v" || '}';
|
|
jbe@0
|
1650 RETURN "ary_text_v"::INT4[][];
|
|
jbe@0
|
1651 ELSE
|
|
jbe@0
|
1652 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
|
jbe@0
|
1653 END IF;
|
|
jbe@0
|
1654 END;
|
|
jbe@0
|
1655 $$;
|
|
jbe@0
|
1656
|
|
jbe@0
|
1657 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
|
jbe@0
|
1658
|
|
jbe@0
|
1659
|
|
jbe@0
|
1660 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
|
|
jbe@0
|
1661 RETURNS INT4[][]
|
|
jbe@0
|
1662 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@0
|
1663 DECLARE
|
|
jbe@0
|
1664 "i" INTEGER;
|
|
jbe@0
|
1665 "row_text_v" TEXT;
|
|
jbe@0
|
1666 "ary_text_v" TEXT;
|
|
jbe@0
|
1667 BEGIN
|
|
jbe@0
|
1668 IF "dim_p" >= 1 THEN
|
|
jbe@0
|
1669 "row_text_v" := '{NULL';
|
|
jbe@0
|
1670 "i" := "dim_p";
|
|
jbe@0
|
1671 LOOP
|
|
jbe@0
|
1672 "i" := "i" - 1;
|
|
jbe@0
|
1673 EXIT WHEN "i" = 0;
|
|
jbe@0
|
1674 "row_text_v" := "row_text_v" || ',NULL';
|
|
jbe@0
|
1675 END LOOP;
|
|
jbe@0
|
1676 "row_text_v" := "row_text_v" || '}';
|
|
jbe@0
|
1677 "ary_text_v" := '{' || "row_text_v";
|
|
jbe@0
|
1678 "i" := "dim_p";
|
|
jbe@0
|
1679 LOOP
|
|
jbe@0
|
1680 "i" := "i" - 1;
|
|
jbe@0
|
1681 EXIT WHEN "i" = 0;
|
|
jbe@0
|
1682 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
|
|
jbe@0
|
1683 END LOOP;
|
|
jbe@0
|
1684 "ary_text_v" := "ary_text_v" || '}';
|
|
jbe@0
|
1685 RETURN "ary_text_v"::INT4[][];
|
|
jbe@0
|
1686 ELSE
|
|
jbe@0
|
1687 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
|
jbe@0
|
1688 END IF;
|
|
jbe@0
|
1689 END;
|
|
jbe@0
|
1690 $$;
|
|
jbe@0
|
1691
|
|
jbe@0
|
1692 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
|
jbe@0
|
1693
|
|
jbe@0
|
1694
|
|
jbe@0
|
1695 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
1696 RETURNS VOID
|
|
jbe@0
|
1697 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1698 DECLARE
|
|
jbe@0
|
1699 "dimension_v" INTEGER;
|
|
jbe@0
|
1700 "matrix" INT4[][];
|
|
jbe@0
|
1701 "i" INTEGER;
|
|
jbe@0
|
1702 "j" INTEGER;
|
|
jbe@0
|
1703 "k" INTEGER;
|
|
jbe@0
|
1704 "battle_row" "battle"%ROWTYPE;
|
|
jbe@0
|
1705 "rank_ary" INT4[];
|
|
jbe@0
|
1706 "rank_v" INT4;
|
|
jbe@0
|
1707 "done_v" INTEGER;
|
|
jbe@0
|
1708 "winners_ary" INTEGER[];
|
|
jbe@0
|
1709 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@0
|
1710 BEGIN
|
|
jbe@0
|
1711 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
|
jbe@0
|
1712 -- Prepare matrix for Schulze-Method:
|
|
jbe@0
|
1713 SELECT count(1) INTO "dimension_v"
|
|
jbe@0
|
1714 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
1715 IF "dimension_v" = 1 THEN
|
|
jbe@0
|
1716 UPDATE "initiative" SET
|
|
jbe@0
|
1717 "rank" = 1
|
|
jbe@0
|
1718 FROM "battle_participant"
|
|
jbe@0
|
1719 WHERE "initiative"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1720 AND "initiative"."id" = "battle_participant"."initiative_id";
|
|
jbe@0
|
1721 ELSIF "dimension_v" > 1 THEN
|
|
jbe@0
|
1722 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
|
jbe@0
|
1723 "i" := 1;
|
|
jbe@0
|
1724 "j" := 2;
|
|
jbe@0
|
1725 -- Fill matrix with data from "battle" view
|
|
jbe@0
|
1726 FOR "battle_row" IN
|
|
jbe@0
|
1727 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1728 ORDER BY "winning_initiative_id", "losing_initiative_id"
|
|
jbe@0
|
1729 LOOP
|
|
jbe@0
|
1730 "matrix"["i"]["j"] := "battle_row"."count";
|
|
jbe@0
|
1731 IF "j" = "dimension_v" THEN
|
|
jbe@0
|
1732 "i" := "i" + 1;
|
|
jbe@0
|
1733 "j" := 1;
|
|
jbe@0
|
1734 ELSE
|
|
jbe@0
|
1735 "j" := "j" + 1;
|
|
jbe@0
|
1736 IF "j" = "i" THEN
|
|
jbe@0
|
1737 "j" := "j" + 1;
|
|
jbe@0
|
1738 END IF;
|
|
jbe@0
|
1739 END IF;
|
|
jbe@0
|
1740 END LOOP;
|
|
jbe@0
|
1741 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
|
jbe@0
|
1742 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
|
jbe@0
|
1743 END IF;
|
|
jbe@0
|
1744 -- Delete losers from matrix:
|
|
jbe@0
|
1745 "i" := 1;
|
|
jbe@0
|
1746 LOOP
|
|
jbe@0
|
1747 "j" := "i" + 1;
|
|
jbe@0
|
1748 LOOP
|
|
jbe@0
|
1749 IF "i" != "j" THEN
|
|
jbe@0
|
1750 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
|
|
jbe@0
|
1751 "matrix"["i"]["j"] := 0;
|
|
jbe@0
|
1752 ELSIF matrix[j][i] < matrix[i][j] THEN
|
|
jbe@0
|
1753 "matrix"["j"]["i"] := 0;
|
|
jbe@0
|
1754 ELSE
|
|
jbe@0
|
1755 "matrix"["i"]["j"] := 0;
|
|
jbe@0
|
1756 "matrix"["j"]["i"] := 0;
|
|
jbe@0
|
1757 END IF;
|
|
jbe@0
|
1758 END IF;
|
|
jbe@0
|
1759 EXIT WHEN "j" = "dimension_v";
|
|
jbe@0
|
1760 "j" := "j" + 1;
|
|
jbe@0
|
1761 END LOOP;
|
|
jbe@0
|
1762 EXIT WHEN "i" = "dimension_v" - 1;
|
|
jbe@0
|
1763 "i" := "i" + 1;
|
|
jbe@0
|
1764 END LOOP;
|
|
jbe@0
|
1765 -- Find best paths:
|
|
jbe@0
|
1766 "i" := 1;
|
|
jbe@0
|
1767 LOOP
|
|
jbe@0
|
1768 "j" := 1;
|
|
jbe@0
|
1769 LOOP
|
|
jbe@0
|
1770 IF "i" != "j" THEN
|
|
jbe@0
|
1771 "k" := 1;
|
|
jbe@0
|
1772 LOOP
|
|
jbe@0
|
1773 IF "i" != "k" AND "j" != "k" THEN
|
|
jbe@0
|
1774 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
|
jbe@0
|
1775 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
|
jbe@0
|
1776 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
|
jbe@0
|
1777 END IF;
|
|
jbe@0
|
1778 ELSE
|
|
jbe@0
|
1779 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
|
jbe@0
|
1780 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
|
jbe@0
|
1781 END IF;
|
|
jbe@0
|
1782 END IF;
|
|
jbe@0
|
1783 END IF;
|
|
jbe@0
|
1784 EXIT WHEN "k" = "dimension_v";
|
|
jbe@0
|
1785 "k" := "k" + 1;
|
|
jbe@0
|
1786 END LOOP;
|
|
jbe@0
|
1787 END IF;
|
|
jbe@0
|
1788 EXIT WHEN "j" = "dimension_v";
|
|
jbe@0
|
1789 "j" := "j" + 1;
|
|
jbe@0
|
1790 END LOOP;
|
|
jbe@0
|
1791 EXIT WHEN "i" = "dimension_v";
|
|
jbe@0
|
1792 "i" := "i" + 1;
|
|
jbe@0
|
1793 END LOOP;
|
|
jbe@0
|
1794 -- Determine order of winners:
|
|
jbe@0
|
1795 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
|
jbe@0
|
1796 "rank_v" := 1;
|
|
jbe@0
|
1797 "done_v" := 0;
|
|
jbe@0
|
1798 LOOP
|
|
jbe@0
|
1799 "winners_ary" := '{}';
|
|
jbe@0
|
1800 "i" := 1;
|
|
jbe@0
|
1801 LOOP
|
|
jbe@0
|
1802 IF "rank_ary"["i"] ISNULL THEN
|
|
jbe@0
|
1803 "j" := 1;
|
|
jbe@0
|
1804 LOOP
|
|
jbe@0
|
1805 IF
|
|
jbe@0
|
1806 "i" != "j" AND
|
|
jbe@0
|
1807 "rank_ary"["j"] ISNULL AND
|
|
jbe@0
|
1808 "matrix"["j"]["i"] > "matrix"["i"]["j"]
|
|
jbe@0
|
1809 THEN
|
|
jbe@0
|
1810 -- someone else is better
|
|
jbe@0
|
1811 EXIT;
|
|
jbe@0
|
1812 END IF;
|
|
jbe@0
|
1813 IF "j" = "dimension_v" THEN
|
|
jbe@0
|
1814 -- noone is better
|
|
jbe@0
|
1815 "winners_ary" := "winners_ary" || "i";
|
|
jbe@0
|
1816 EXIT;
|
|
jbe@0
|
1817 END IF;
|
|
jbe@0
|
1818 "j" := "j" + 1;
|
|
jbe@0
|
1819 END LOOP;
|
|
jbe@0
|
1820 END IF;
|
|
jbe@0
|
1821 EXIT WHEN "i" = "dimension_v";
|
|
jbe@0
|
1822 "i" := "i" + 1;
|
|
jbe@0
|
1823 END LOOP;
|
|
jbe@0
|
1824 "i" := 1;
|
|
jbe@0
|
1825 LOOP
|
|
jbe@0
|
1826 "rank_ary"["winners_ary"["i"]] := "rank_v";
|
|
jbe@0
|
1827 "done_v" := "done_v" + 1;
|
|
jbe@0
|
1828 EXIT WHEN "i" = array_upper("winners_ary", 1);
|
|
jbe@0
|
1829 "i" := "i" + 1;
|
|
jbe@0
|
1830 END LOOP;
|
|
jbe@0
|
1831 EXIT WHEN "done_v" = "dimension_v";
|
|
jbe@0
|
1832 "rank_v" := "rank_v" + 1;
|
|
jbe@0
|
1833 END LOOP;
|
|
jbe@0
|
1834 -- write preliminary ranks:
|
|
jbe@0
|
1835 "i" := 1;
|
|
jbe@0
|
1836 FOR "initiative_id_v" IN
|
|
jbe@0
|
1837 SELECT "initiative"."id"
|
|
jbe@0
|
1838 FROM "initiative" JOIN "battle_participant"
|
|
jbe@0
|
1839 ON "initiative"."id" = "battle_participant"."initiative_id"
|
|
jbe@0
|
1840 WHERE "initiative"."issue_id" = "issue_id_p"
|
|
jbe@0
|
1841 ORDER BY "initiative"."id"
|
|
jbe@0
|
1842 LOOP
|
|
jbe@0
|
1843 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
|
|
jbe@0
|
1844 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
1845 "i" := "i" + 1;
|
|
jbe@0
|
1846 END LOOP;
|
|
jbe@0
|
1847 IF "i" != "dimension_v" + 1 THEN
|
|
jbe@0
|
1848 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
|
jbe@0
|
1849 END IF;
|
|
jbe@0
|
1850 -- straighten ranks (start counting with 1, no equal ranks):
|
|
jbe@0
|
1851 "rank_v" := 1;
|
|
jbe@0
|
1852 FOR "initiative_id_v" IN
|
|
jbe@0
|
1853 SELECT "id" FROM "initiative"
|
|
jbe@0
|
1854 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
|
|
jbe@0
|
1855 ORDER BY
|
|
jbe@0
|
1856 "rank",
|
|
jbe@0
|
1857 "vote_ratio"("positive_votes", "negative_votes") DESC,
|
|
jbe@0
|
1858 "id"
|
|
jbe@0
|
1859 LOOP
|
|
jbe@0
|
1860 UPDATE "initiative" SET "rank" = "rank_v"
|
|
jbe@0
|
1861 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
1862 "rank_v" := "rank_v" + 1;
|
|
jbe@0
|
1863 END LOOP;
|
|
jbe@0
|
1864 END IF;
|
|
jbe@0
|
1865 -- mark issue as finished
|
|
jbe@0
|
1866 UPDATE "issue" SET "ranks_available" = TRUE
|
|
jbe@0
|
1867 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1868 RETURN;
|
|
jbe@0
|
1869 END;
|
|
jbe@0
|
1870 $$;
|
|
jbe@0
|
1871
|
|
jbe@0
|
1872 COMMENT ON FUNCTION "calculate_ranks"
|
|
jbe@0
|
1873 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1874 IS 'Determine ranking (Votes have to be counted first)';
|
|
jbe@0
|
1875
|
|
jbe@0
|
1876
|
|
jbe@0
|
1877
|
|
jbe@0
|
1878 -----------------------------
|
|
jbe@0
|
1879 -- Automatic state changes --
|
|
jbe@0
|
1880 -----------------------------
|
|
jbe@0
|
1881
|
|
jbe@0
|
1882
|
|
jbe@0
|
1883 CREATE FUNCTION "check_issue"
|
|
jbe@0
|
1884 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
1885 RETURNS VOID
|
|
jbe@0
|
1886 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1887 DECLARE
|
|
jbe@0
|
1888 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
1889 "policy_row" "policy"%ROWTYPE;
|
|
jbe@0
|
1890 "voting_requested_v" BOOLEAN;
|
|
jbe@0
|
1891 BEGIN
|
|
jbe@0
|
1892 PERFORM "global_lock"();
|
|
jbe@0
|
1893 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1894 IF "issue_row"."closed" ISNULL THEN
|
|
jbe@0
|
1895 SELECT * INTO "policy_row" FROM "policy"
|
|
jbe@0
|
1896 WHERE "id" = "issue_row"."policy_id";
|
|
jbe@0
|
1897 IF "issue_row"."frozen" ISNULL THEN
|
|
jbe@0
|
1898 PERFORM "create_snapshot"("issue_id_p");
|
|
jbe@0
|
1899 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1900 END IF;
|
|
jbe@0
|
1901 IF "issue_row"."accepted" ISNULL THEN
|
|
jbe@0
|
1902 IF EXISTS (
|
|
jbe@0
|
1903 SELECT NULL FROM "initiative"
|
|
jbe@0
|
1904 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
1905 AND "supporter_count" > 0
|
|
jbe@0
|
1906 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
|
jbe@0
|
1907 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
|
jbe@0
|
1908 ) THEN
|
|
jbe@0
|
1909 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
|
|
jbe@0
|
1910 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
|
|
jbe@0
|
1911 WHERE "id" = "issue_row"."id";
|
|
jbe@0
|
1912 ELSIF
|
|
jbe@0
|
1913 now() > "issue_row"."created" + "policy_row"."admission_time"
|
|
jbe@0
|
1914 THEN
|
|
jbe@0
|
1915 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
|
jbe@0
|
1916 UPDATE "issue" SET "closed" = now()
|
|
jbe@0
|
1917 WHERE "id" = "issue_row"."id";
|
|
jbe@0
|
1918 END IF;
|
|
jbe@0
|
1919 END IF;
|
|
jbe@0
|
1920 IF
|
|
jbe@0
|
1921 "issue_row"."accepted" NOTNULL AND
|
|
jbe@0
|
1922 "issue_row"."frozen" ISNULL
|
|
jbe@0
|
1923 THEN
|
|
jbe@0
|
1924 SELECT
|
|
jbe@0
|
1925 CASE
|
|
jbe@0
|
1926 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
|
jbe@0
|
1927 TRUE
|
|
jbe@0
|
1928 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
|
jbe@0
|
1929 FALSE
|
|
jbe@0
|
1930 ELSE NULL
|
|
jbe@0
|
1931 END
|
|
jbe@0
|
1932 INTO "voting_requested_v"
|
|
jbe@0
|
1933 FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
1934 IF
|
|
jbe@0
|
1935 "voting_requested_v" OR (
|
|
jbe@0
|
1936 "voting_requested_v" ISNULL AND now() >
|
|
jbe@0
|
1937 "issue_row"."accepted" + "policy_row"."discussion_time"
|
|
jbe@0
|
1938 )
|
|
jbe@0
|
1939 THEN
|
|
jbe@0
|
1940 PERFORM "freeze_after_snapshot"("issue_id_p");
|
|
jbe@0
|
1941 END IF;
|
|
jbe@0
|
1942 END IF;
|
|
jbe@0
|
1943 IF
|
|
jbe@0
|
1944 "issue_row"."frozen" NOTNULL AND
|
|
jbe@0
|
1945 now() > "issue_row"."frozen" + "policy_row"."voting_time"
|
|
jbe@0
|
1946 THEN
|
|
jbe@0
|
1947 PERFORM "close_voting"("issue_id_p");
|
|
jbe@0
|
1948 END IF;
|
|
jbe@0
|
1949 END IF;
|
|
jbe@0
|
1950 RETURN;
|
|
jbe@0
|
1951 END;
|
|
jbe@0
|
1952 $$;
|
|
jbe@0
|
1953
|
|
jbe@0
|
1954 COMMENT ON FUNCTION "check_issue"
|
|
jbe@0
|
1955 ( "issue"."id"%TYPE )
|
|
jbe@0
|
1956 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
|
|
jbe@0
|
1957
|
|
jbe@0
|
1958
|
|
jbe@0
|
1959 CREATE FUNCTION "check_everything"()
|
|
jbe@0
|
1960 RETURNS VOID
|
|
jbe@0
|
1961 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1962 DECLARE
|
|
jbe@0
|
1963 "issue_id_v" "issue"."id"%TYPE;
|
|
jbe@0
|
1964 BEGIN
|
|
jbe@0
|
1965 FOR "issue_id_v" IN
|
|
jbe@0
|
1966 SELECT "id" FROM "issue" WHERE "closed" ISNULL
|
|
jbe@0
|
1967 LOOP
|
|
jbe@0
|
1968 PERFORM "check_issue"("issue_id_v");
|
|
jbe@0
|
1969 END LOOP;
|
|
jbe@0
|
1970 FOR "issue_id_v" IN
|
|
jbe@0
|
1971 SELECT "id" FROM "issue"
|
|
jbe@0
|
1972 WHERE "frozen" NOTNULL
|
|
jbe@0
|
1973 AND "closed" NOTNULL
|
|
jbe@0
|
1974 AND "ranks_available" = FALSE FOR UPDATE
|
|
jbe@0
|
1975 LOOP
|
|
jbe@0
|
1976 PERFORM "calculate_ranks"("issue_id_v");
|
|
jbe@0
|
1977 END LOOP;
|
|
jbe@0
|
1978 RETURN;
|
|
jbe@0
|
1979 END;
|
|
jbe@0
|
1980 $$;
|
|
jbe@0
|
1981
|
|
jbe@0
|
1982 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
|
|
jbe@0
|
1983
|
|
jbe@0
|
1984
|
|
jbe@0
|
1985
|
|
jbe@0
|
1986 COMMIT;
|