| 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@5
|
8 CREATE VIEW "liquid_feedback_version" AS
|
|
jbe@74
|
9 SELECT * FROM (VALUES ('1.2.8', 1, 2, 8))
|
|
jbe@5
|
10 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@5
|
11
|
|
jbe@0
|
12
|
|
jbe@0
|
13
|
|
jbe@7
|
14 ----------------------
|
|
jbe@7
|
15 -- Full text search --
|
|
jbe@7
|
16 ----------------------
|
|
jbe@7
|
17
|
|
jbe@7
|
18
|
|
jbe@7
|
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
|
|
jbe@7
|
20 RETURNS TSQUERY
|
|
jbe@7
|
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@7
|
22 BEGIN
|
|
jbe@7
|
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
|
|
jbe@7
|
24 END;
|
|
jbe@7
|
25 $$;
|
|
jbe@7
|
26
|
|
jbe@7
|
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
|
|
jbe@7
|
28
|
|
jbe@7
|
29
|
|
jbe@7
|
30 CREATE FUNCTION "highlight"
|
|
jbe@7
|
31 ( "body_p" TEXT,
|
|
jbe@7
|
32 "query_text_p" TEXT )
|
|
jbe@7
|
33 RETURNS TEXT
|
|
jbe@7
|
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@7
|
35 BEGIN
|
|
jbe@7
|
36 RETURN ts_headline(
|
|
jbe@7
|
37 'pg_catalog.simple',
|
|
jbe@8
|
38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
|
|
jbe@7
|
39 "text_search_query"("query_text_p"),
|
|
jbe@7
|
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
|
|
jbe@7
|
41 END;
|
|
jbe@7
|
42 $$;
|
|
jbe@7
|
43
|
|
jbe@7
|
44 COMMENT ON FUNCTION "highlight"
|
|
jbe@7
|
45 ( "body_p" TEXT,
|
|
jbe@7
|
46 "query_text_p" TEXT )
|
|
jbe@7
|
47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
|
|
jbe@7
|
48
|
|
jbe@7
|
49
|
|
jbe@7
|
50
|
|
jbe@0
|
51 -------------------------
|
|
jbe@0
|
52 -- Tables and indicies --
|
|
jbe@0
|
53 -------------------------
|
|
jbe@0
|
54
|
|
jbe@8
|
55
|
|
jbe@0
|
56 CREATE TABLE "member" (
|
|
jbe@0
|
57 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@13
|
58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@42
|
59 "last_login" TIMESTAMPTZ,
|
|
jbe@45
|
60 "login" TEXT UNIQUE,
|
|
jbe@0
|
61 "password" TEXT,
|
|
jbe@0
|
62 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
63 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@7
|
64 "notify_email" TEXT,
|
|
jbe@11
|
65 "notify_email_unconfirmed" TEXT,
|
|
jbe@11
|
66 "notify_email_secret" TEXT UNIQUE,
|
|
jbe@11
|
67 "notify_email_secret_expiry" TIMESTAMPTZ,
|
|
jbe@55
|
68 "notify_email_lock_expiry" TIMESTAMPTZ,
|
|
jbe@11
|
69 "password_reset_secret" TEXT UNIQUE,
|
|
jbe@11
|
70 "password_reset_secret_expiry" TIMESTAMPTZ,
|
|
jbe@7
|
71 "name" TEXT NOT NULL UNIQUE,
|
|
jbe@7
|
72 "identification" TEXT UNIQUE,
|
|
jbe@7
|
73 "organizational_unit" TEXT,
|
|
jbe@7
|
74 "internal_posts" TEXT,
|
|
jbe@7
|
75 "realname" TEXT,
|
|
jbe@7
|
76 "birthday" DATE,
|
|
jbe@7
|
77 "address" TEXT,
|
|
jbe@7
|
78 "email" TEXT,
|
|
jbe@7
|
79 "xmpp_address" TEXT,
|
|
jbe@7
|
80 "website" TEXT,
|
|
jbe@7
|
81 "phone" TEXT,
|
|
jbe@7
|
82 "mobile_phone" TEXT,
|
|
jbe@7
|
83 "profession" TEXT,
|
|
jbe@7
|
84 "external_memberships" TEXT,
|
|
jbe@7
|
85 "external_posts" TEXT,
|
|
jbe@7
|
86 "statement" TEXT,
|
|
jbe@10
|
87 "text_search_data" TSVECTOR );
|
|
jbe@0
|
88 CREATE INDEX "member_active_idx" ON "member" ("active");
|
|
jbe@8
|
89 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
|
|
jbe@7
|
90 CREATE TRIGGER "update_text_search_data"
|
|
jbe@7
|
91 BEFORE INSERT OR UPDATE ON "member"
|
|
jbe@7
|
92 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@7
|
93 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
|
jbe@7
|
94 "name", "identification", "organizational_unit", "internal_posts",
|
|
jbe@7
|
95 "realname", "external_memberships", "external_posts", "statement" );
|
|
jbe@0
|
96
|
|
jbe@0
|
97 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
|
|
jbe@0
|
98
|
|
jbe@10
|
99 COMMENT ON COLUMN "member"."login" IS 'Login name';
|
|
jbe@10
|
100 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
|
|
jbe@10
|
101 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
|
|
jbe@10
|
102 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
|
|
jbe@10
|
103 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
|
|
jbe@10
|
104 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
|
|
jbe@10
|
105 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
|
|
jbe@10
|
106 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
|
|
jbe@55
|
107 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
|
|
jbe@10
|
108 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
|
|
jbe@10
|
109 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
|
|
jbe@10
|
110 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
|
|
jbe@10
|
111 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
|
|
jbe@10
|
112 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
|
|
jbe@10
|
113 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
|
|
jbe@10
|
114 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
|
|
jbe@10
|
115 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
|
|
jbe@10
|
116 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
|
|
jbe@7
|
117
|
|
jbe@7
|
118
|
|
jbe@13
|
119 CREATE TABLE "member_history" (
|
|
jbe@13
|
120 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@13
|
121 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@13
|
122 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@42
|
123 "active" BOOLEAN NOT NULL,
|
|
jbe@13
|
124 "name" TEXT NOT NULL );
|
|
jbe@45
|
125 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
|
|
jbe@13
|
126
|
|
jbe@57
|
127 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
|
|
jbe@13
|
128
|
|
jbe@13
|
129 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
|
|
jbe@57
|
130 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
|
|
jbe@13
|
131
|
|
jbe@13
|
132
|
|
jbe@9
|
133 CREATE TABLE "invite_code" (
|
|
jbe@9
|
134 "code" TEXT PRIMARY KEY,
|
|
jbe@9
|
135 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@9
|
136 "used" TIMESTAMPTZ,
|
|
jbe@9
|
137 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
|
|
jbe@9
|
138 "comment" TEXT,
|
|
jbe@9
|
139 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
|
|
jbe@9
|
140
|
|
jbe@9
|
141 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
|
|
jbe@9
|
142
|
|
jbe@9
|
143 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
|
|
jbe@9
|
144 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
|
|
jbe@9
|
145 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
|
|
jbe@9
|
146 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
|
|
jbe@9
|
147 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
|
|
jbe@9
|
148
|
|
jbe@9
|
149
|
|
jbe@9
|
150 CREATE TABLE "setting" (
|
|
jbe@9
|
151 PRIMARY KEY ("member_id", "key"),
|
|
jbe@9
|
152 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@9
|
153 "key" TEXT NOT NULL,
|
|
jbe@9
|
154 "value" TEXT NOT NULL );
|
|
jbe@9
|
155 CREATE INDEX "setting_key_idx" ON "setting" ("key");
|
|
jbe@9
|
156
|
|
jbe@38
|
157 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
|
|
jbe@9
|
158
|
|
jbe@9
|
159 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
|
|
jbe@9
|
160
|
|
jbe@9
|
161
|
|
jbe@16
|
162 CREATE TABLE "setting_map" (
|
|
jbe@16
|
163 PRIMARY KEY ("member_id", "key", "subkey"),
|
|
jbe@16
|
164 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@16
|
165 "key" TEXT NOT NULL,
|
|
jbe@16
|
166 "subkey" TEXT NOT NULL,
|
|
jbe@16
|
167 "value" TEXT NOT NULL );
|
|
jbe@16
|
168 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
|
|
jbe@16
|
169
|
|
jbe@23
|
170 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
|
|
jbe@16
|
171
|
|
jbe@16
|
172 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
|
|
jbe@16
|
173 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
|
|
jbe@16
|
174 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
|
|
jbe@16
|
175
|
|
jbe@16
|
176
|
|
jbe@23
|
177 CREATE TABLE "member_relation_setting" (
|
|
jbe@23
|
178 PRIMARY KEY ("member_id", "key", "other_member_id"),
|
|
jbe@23
|
179 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
180 "key" TEXT NOT NULL,
|
|
jbe@23
|
181 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
182 "value" TEXT NOT NULL );
|
|
jbe@23
|
183
|
|
jbe@38
|
184 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
|
|
jbe@23
|
185
|
|
jbe@23
|
186
|
|
jbe@7
|
187 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
|
|
jbe@7
|
188
|
|
jbe@7
|
189 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
|
|
jbe@7
|
190
|
|
jbe@7
|
191
|
|
jbe@7
|
192 CREATE TABLE "member_image" (
|
|
jbe@7
|
193 PRIMARY KEY ("member_id", "image_type", "scaled"),
|
|
jbe@7
|
194 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@7
|
195 "image_type" "member_image_type",
|
|
jbe@7
|
196 "scaled" BOOLEAN,
|
|
jbe@7
|
197 "content_type" TEXT,
|
|
jbe@7
|
198 "data" BYTEA NOT NULL );
|
|
jbe@7
|
199
|
|
jbe@7
|
200 COMMENT ON TABLE "member_image" IS 'Images of members';
|
|
jbe@7
|
201
|
|
jbe@7
|
202 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
|
|
jbe@0
|
203
|
|
jbe@0
|
204
|
|
jbe@4
|
205 CREATE TABLE "member_count" (
|
|
jbe@5
|
206 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
jbe@5
|
207 "total_count" INT4 NOT NULL );
|
|
jbe@4
|
208
|
|
jbe@5
|
209 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
|
|
jbe@4
|
210
|
|
jbe@5
|
211 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
|
|
jbe@5
|
212 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
|
|
jbe@4
|
213
|
|
jbe@4
|
214
|
|
jbe@0
|
215 CREATE TABLE "contact" (
|
|
jbe@0
|
216 PRIMARY KEY ("member_id", "other_member_id"),
|
|
jbe@0
|
217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
218 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@11
|
219 "public" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@11
|
220 CONSTRAINT "cant_save_yourself_as_contact"
|
|
jbe@11
|
221 CHECK ("member_id" != "other_member_id") );
|
|
jbe@0
|
222
|
|
jbe@0
|
223 COMMENT ON TABLE "contact" IS 'Contact lists';
|
|
jbe@0
|
224
|
|
jbe@0
|
225 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
|
|
jbe@0
|
226 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
|
|
jbe@0
|
227 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
|
|
jbe@0
|
228
|
|
jbe@0
|
229
|
|
jbe@0
|
230 CREATE TABLE "session" (
|
|
jbe@0
|
231 "ident" TEXT PRIMARY KEY,
|
|
jbe@0
|
232 "additional_secret" TEXT,
|
|
jbe@0
|
233 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
|
jbe@0
|
234 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
|
|
jbe@0
|
235 "lang" TEXT );
|
|
jbe@0
|
236 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
|
jbe@0
|
237
|
|
jbe@0
|
238 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
|
|
jbe@0
|
239
|
|
jbe@0
|
240 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
|
jbe@0
|
241 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
|
jbe@0
|
242 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
|
jbe@0
|
243 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
|
jbe@0
|
244
|
|
jbe@0
|
245
|
|
jbe@0
|
246 CREATE TABLE "policy" (
|
|
jbe@0
|
247 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@9
|
248 "index" INT4 NOT NULL,
|
|
jbe@0
|
249 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
250 "name" TEXT NOT NULL UNIQUE,
|
|
jbe@0
|
251 "description" TEXT NOT NULL DEFAULT '',
|
|
jbe@0
|
252 "admission_time" INTERVAL NOT NULL,
|
|
jbe@0
|
253 "discussion_time" INTERVAL NOT NULL,
|
|
jbe@3
|
254 "verification_time" INTERVAL NOT NULL,
|
|
jbe@0
|
255 "voting_time" INTERVAL NOT NULL,
|
|
jbe@0
|
256 "issue_quorum_num" INT4 NOT NULL,
|
|
jbe@0
|
257 "issue_quorum_den" INT4 NOT NULL,
|
|
jbe@0
|
258 "initiative_quorum_num" INT4 NOT NULL,
|
|
jbe@10
|
259 "initiative_quorum_den" INT4 NOT NULL,
|
|
jbe@10
|
260 "majority_num" INT4 NOT NULL DEFAULT 1,
|
|
jbe@10
|
261 "majority_den" INT4 NOT NULL DEFAULT 2,
|
|
jbe@10
|
262 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
|
|
jbe@0
|
263 CREATE INDEX "policy_active_idx" ON "policy" ("active");
|
|
jbe@0
|
264
|
|
jbe@0
|
265 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
|
|
jbe@0
|
266
|
|
jbe@9
|
267 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
|
|
jbe@0
|
268 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
|
|
jbe@0
|
269 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
|
|
jbe@3
|
270 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
|
|
jbe@3
|
271 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
|
|
jbe@3
|
272 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
|
|
jbe@10
|
273 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
|
|
jbe@10
|
274 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
|
|
jbe@10
|
275 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
|
|
jbe@10
|
276 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
|
|
jbe@10
|
277 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
|
|
jbe@10
|
278 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
|
|
jbe@10
|
279 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
|
|
jbe@0
|
280
|
|
jbe@0
|
281
|
|
jbe@0
|
282 CREATE TABLE "area" (
|
|
jbe@0
|
283 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
284 "active" BOOLEAN NOT NULL DEFAULT TRUE,
|
|
jbe@0
|
285 "name" TEXT NOT NULL,
|
|
jbe@4
|
286 "description" TEXT NOT NULL DEFAULT '',
|
|
jbe@5
|
287 "direct_member_count" INT4,
|
|
jbe@5
|
288 "member_weight" INT4,
|
|
jbe@7
|
289 "autoreject_weight" INT4,
|
|
jbe@7
|
290 "text_search_data" TSVECTOR );
|
|
jbe@0
|
291 CREATE INDEX "area_active_idx" ON "area" ("active");
|
|
jbe@8
|
292 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
|
|
jbe@7
|
293 CREATE TRIGGER "update_text_search_data"
|
|
jbe@7
|
294 BEFORE INSERT OR UPDATE ON "area"
|
|
jbe@7
|
295 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@7
|
296 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
|
jbe@7
|
297 "name", "description" );
|
|
jbe@0
|
298
|
|
jbe@0
|
299 COMMENT ON TABLE "area" IS 'Subject areas';
|
|
jbe@0
|
300
|
|
jbe@5
|
301 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
|
|
jbe@5
|
302 COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
|
|
jbe@5
|
303 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
|
|
jbe@5
|
304 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
|
|
jbe@0
|
305
|
|
jbe@0
|
306
|
|
jbe@23
|
307 CREATE TABLE "area_setting" (
|
|
jbe@23
|
308 PRIMARY KEY ("member_id", "key", "area_id"),
|
|
jbe@23
|
309 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
310 "key" TEXT NOT NULL,
|
|
jbe@23
|
311 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
312 "value" TEXT NOT NULL );
|
|
jbe@23
|
313
|
|
jbe@23
|
314 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
|
|
jbe@23
|
315
|
|
jbe@23
|
316
|
|
jbe@9
|
317 CREATE TABLE "allowed_policy" (
|
|
jbe@9
|
318 PRIMARY KEY ("area_id", "policy_id"),
|
|
jbe@9
|
319 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@9
|
320 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@9
|
321 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@9
|
322 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
|
|
jbe@9
|
323
|
|
jbe@9
|
324 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
|
|
jbe@9
|
325
|
|
jbe@9
|
326 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
|
|
jbe@9
|
327
|
|
jbe@9
|
328
|
|
jbe@21
|
329 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
|
|
jbe@21
|
330
|
|
jbe@21
|
331 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
|
|
jbe@8
|
332
|
|
jbe@8
|
333
|
|
jbe@0
|
334 CREATE TABLE "issue" (
|
|
jbe@0
|
335 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
336 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
337 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
338 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
339 "accepted" TIMESTAMPTZ,
|
|
jbe@3
|
340 "half_frozen" TIMESTAMPTZ,
|
|
jbe@3
|
341 "fully_frozen" TIMESTAMPTZ,
|
|
jbe@0
|
342 "closed" TIMESTAMPTZ,
|
|
jbe@0
|
343 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@59
|
344 "cleaned" TIMESTAMPTZ,
|
|
jbe@22
|
345 "admission_time" INTERVAL NOT NULL,
|
|
jbe@22
|
346 "discussion_time" INTERVAL NOT NULL,
|
|
jbe@22
|
347 "verification_time" INTERVAL NOT NULL,
|
|
jbe@22
|
348 "voting_time" INTERVAL NOT NULL,
|
|
jbe@0
|
349 "snapshot" TIMESTAMPTZ,
|
|
jbe@8
|
350 "latest_snapshot_event" "snapshot_event",
|
|
jbe@0
|
351 "population" INT4,
|
|
jbe@0
|
352 "vote_now" INT4,
|
|
jbe@0
|
353 "vote_later" INT4,
|
|
jbe@4
|
354 "voter_count" INT4,
|
|
jbe@0
|
355 CONSTRAINT "valid_state" CHECK (
|
|
jbe@3
|
356 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
357 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
358 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@34
|
359 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
360 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@34
|
361 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
362 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
|
|
jbe@3
|
364 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
|
|
jbe@3
|
365 CONSTRAINT "state_change_order" CHECK (
|
|
jbe@10
|
366 "created" <= "accepted" AND
|
|
jbe@10
|
367 "accepted" <= "half_frozen" AND
|
|
jbe@10
|
368 "half_frozen" <= "fully_frozen" AND
|
|
jbe@3
|
369 "fully_frozen" <= "closed" ),
|
|
jbe@61
|
370 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
|
|
jbe@61
|
371 "cleaned" ISNULL OR "closed" NOTNULL ),
|
|
jbe@10
|
372 CONSTRAINT "last_snapshot_on_full_freeze"
|
|
jbe@10
|
373 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
|
|
jbe@10
|
374 CONSTRAINT "freeze_requires_snapshot"
|
|
jbe@10
|
375 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
|
|
jbe@10
|
376 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
|
|
jbe@10
|
377 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
|
|
jbe@0
|
378 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
|
|
jbe@0
|
379 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
|
|
jbe@16
|
380 CREATE INDEX "issue_created_idx" ON "issue" ("created");
|
|
jbe@16
|
381 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
|
|
jbe@16
|
382 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
|
|
jbe@16
|
383 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
|
|
jbe@16
|
384 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
|
|
jbe@0
|
385 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
|
|
jbe@16
|
386 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
|
|
jbe@0
|
387
|
|
jbe@0
|
388 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
|
|
jbe@0
|
389
|
|
jbe@8
|
390 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
|
|
jbe@10
|
391 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
|
|
jbe@10
|
392 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
|
|
jbe@10
|
393 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
|
|
jbe@8
|
394 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
|
|
jbe@59
|
395 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
|
|
jbe@22
|
396 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
|
|
jbe@22
|
397 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
|
|
jbe@22
|
398 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
|
|
jbe@22
|
399 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
|
|
jbe@8
|
400 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@8
|
401 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
|
|
jbe@8
|
402 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
|
|
jbe@8
|
403 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
|
|
jbe@8
|
404 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
|
|
jbe@8
|
405 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
|
|
jbe@0
|
406
|
|
jbe@0
|
407
|
|
jbe@23
|
408 CREATE TABLE "issue_setting" (
|
|
jbe@23
|
409 PRIMARY KEY ("member_id", "key", "issue_id"),
|
|
jbe@23
|
410 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
411 "key" TEXT NOT NULL,
|
|
jbe@23
|
412 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
413 "value" TEXT NOT NULL );
|
|
jbe@23
|
414
|
|
jbe@23
|
415 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
|
|
jbe@23
|
416
|
|
jbe@23
|
417
|
|
jbe@0
|
418 CREATE TABLE "initiative" (
|
|
jbe@0
|
419 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
|
|
jbe@0
|
420 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
421 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@0
|
422 "name" TEXT NOT NULL,
|
|
jbe@8
|
423 "discussion_url" TEXT,
|
|
jbe@0
|
424 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
425 "revoked" TIMESTAMPTZ,
|
|
jbe@14
|
426 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
427 "admitted" BOOLEAN,
|
|
jbe@0
|
428 "supporter_count" INT4,
|
|
jbe@0
|
429 "informed_supporter_count" INT4,
|
|
jbe@0
|
430 "satisfied_supporter_count" INT4,
|
|
jbe@0
|
431 "satisfied_informed_supporter_count" INT4,
|
|
jbe@0
|
432 "positive_votes" INT4,
|
|
jbe@0
|
433 "negative_votes" INT4,
|
|
jbe@10
|
434 "agreed" BOOLEAN,
|
|
jbe@0
|
435 "rank" INT4,
|
|
jbe@7
|
436 "text_search_data" TSVECTOR,
|
|
jbe@14
|
437 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
|
|
jbe@14
|
438 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
|
|
jbe@0
|
439 CONSTRAINT "revoked_initiatives_cant_be_admitted"
|
|
jbe@0
|
440 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
|
|
jbe@0
|
441 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
|
|
jbe@10
|
442 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
|
|
jbe@10
|
443 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
|
|
jbe@10
|
444 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
|
|
jbe@10
|
445 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
|
|
jbe@10
|
446 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
|
|
jbe@16
|
447 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
|
|
jbe@16
|
448 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
|
|
poelzi@81
|
449 CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id");
|
|
jbe@8
|
450 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
|
|
jbe@7
|
451 CREATE TRIGGER "update_text_search_data"
|
|
jbe@7
|
452 BEFORE INSERT OR UPDATE ON "initiative"
|
|
jbe@7
|
453 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@8
|
454 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
|
jbe@8
|
455 "name", "discussion_url");
|
|
jbe@0
|
456
|
|
jbe@10
|
457 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
|
|
jbe@0
|
458
|
|
jbe@8
|
459 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
|
|
jbe@0
|
460 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
|
|
jbe@10
|
461 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
|
|
jbe@0
|
462 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
463 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
464 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
465 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
|
|
jbe@0
|
466 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
|
|
jbe@0
|
467 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
|
|
jbe@10
|
468 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
|
|
jbe@0
|
469 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
|
|
jbe@0
|
470
|
|
jbe@0
|
471
|
|
jbe@61
|
472 CREATE TABLE "battle" (
|
|
jbe@61
|
473 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
|
|
jbe@61
|
474 "issue_id" INT4,
|
|
jbe@61
|
475 "winning_initiative_id" INT4,
|
|
jbe@61
|
476 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@61
|
477 "losing_initiative_id" INT4,
|
|
jbe@61
|
478 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@61
|
479 "count" INT4 NOT NULL);
|
|
jbe@61
|
480
|
|
jbe@61
|
481 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
|
|
jbe@61
|
482
|
|
jbe@61
|
483
|
|
jbe@23
|
484 CREATE TABLE "initiative_setting" (
|
|
jbe@23
|
485 PRIMARY KEY ("member_id", "key", "initiative_id"),
|
|
jbe@23
|
486 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
487 "key" TEXT NOT NULL,
|
|
jbe@23
|
488 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
489 "value" TEXT NOT NULL );
|
|
jbe@23
|
490
|
|
jbe@23
|
491 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
|
|
jbe@23
|
492
|
|
jbe@23
|
493
|
|
jbe@0
|
494 CREATE TABLE "draft" (
|
|
jbe@0
|
495 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
|
|
jbe@0
|
496 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
497 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
498 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
499 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@9
|
500 "formatting_engine" TEXT,
|
|
jbe@7
|
501 "content" TEXT NOT NULL,
|
|
jbe@7
|
502 "text_search_data" TSVECTOR );
|
|
jbe@16
|
503 CREATE INDEX "draft_created_idx" ON "draft" ("created");
|
|
jbe@9
|
504 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
|
|
jbe@8
|
505 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
|
|
jbe@7
|
506 CREATE TRIGGER "update_text_search_data"
|
|
jbe@7
|
507 BEFORE INSERT OR UPDATE ON "draft"
|
|
jbe@7
|
508 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@7
|
509 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
|
|
jbe@0
|
510
|
|
jbe@10
|
511 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
|
|
jbe@0
|
512
|
|
jbe@9
|
513 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
|
|
jbe@9
|
514 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
|
|
jbe@9
|
515
|
|
jbe@0
|
516
|
|
jbe@63
|
517 CREATE TABLE "rendered_draft" (
|
|
jbe@63
|
518 PRIMARY KEY ("draft_id", "format"),
|
|
jbe@63
|
519 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@63
|
520 "format" TEXT,
|
|
jbe@63
|
521 "content" TEXT NOT NULL );
|
|
jbe@63
|
522
|
|
jbe@63
|
523 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
|
|
jbe@63
|
524
|
|
jbe@63
|
525
|
|
jbe@0
|
526 CREATE TABLE "suggestion" (
|
|
jbe@0
|
527 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
|
|
jbe@0
|
528 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
529 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
530 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
jbe@0
|
531 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
jbe@0
|
532 "name" TEXT NOT NULL,
|
|
jbe@0
|
533 "description" TEXT NOT NULL DEFAULT '',
|
|
jbe@7
|
534 "text_search_data" TSVECTOR,
|
|
jbe@0
|
535 "minus2_unfulfilled_count" INT4,
|
|
jbe@0
|
536 "minus2_fulfilled_count" INT4,
|
|
jbe@0
|
537 "minus1_unfulfilled_count" INT4,
|
|
jbe@0
|
538 "minus1_fulfilled_count" INT4,
|
|
jbe@0
|
539 "plus1_unfulfilled_count" INT4,
|
|
jbe@0
|
540 "plus1_fulfilled_count" INT4,
|
|
jbe@0
|
541 "plus2_unfulfilled_count" INT4,
|
|
jbe@0
|
542 "plus2_fulfilled_count" INT4 );
|
|
jbe@16
|
543 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
|
|
jbe@9
|
544 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
|
|
jbe@8
|
545 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
|
|
jbe@7
|
546 CREATE TRIGGER "update_text_search_data"
|
|
jbe@7
|
547 BEFORE INSERT OR UPDATE ON "suggestion"
|
|
jbe@7
|
548 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@7
|
549 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
|
|
jbe@7
|
550 "name", "description");
|
|
jbe@0
|
551
|
|
jbe@10
|
552 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
|
|
jbe@0
|
553
|
|
jbe@0
|
554 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
555 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
556 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
557 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
558 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
559 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
560 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
561 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
|
|
jbe@0
|
562
|
|
jbe@0
|
563
|
|
jbe@23
|
564 CREATE TABLE "suggestion_setting" (
|
|
jbe@23
|
565 PRIMARY KEY ("member_id", "key", "suggestion_id"),
|
|
jbe@23
|
566 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
567 "key" TEXT NOT NULL,
|
|
jbe@23
|
568 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@23
|
569 "value" TEXT NOT NULL );
|
|
jbe@23
|
570
|
|
jbe@23
|
571 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
|
|
jbe@23
|
572
|
|
jbe@23
|
573
|
|
jbe@0
|
574 CREATE TABLE "membership" (
|
|
jbe@0
|
575 PRIMARY KEY ("area_id", "member_id"),
|
|
jbe@0
|
576 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
577 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
578 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@0
|
579 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
|
|
jbe@0
|
580
|
|
jbe@0
|
581 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
|
|
jbe@0
|
582
|
|
jbe@0
|
583 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
|
584
|
|
jbe@0
|
585
|
|
jbe@0
|
586 CREATE TABLE "interest" (
|
|
jbe@0
|
587 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
588 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
589 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
590 "autoreject" BOOLEAN NOT NULL,
|
|
jbe@0
|
591 "voting_requested" BOOLEAN );
|
|
jbe@0
|
592 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
|
|
jbe@0
|
593
|
|
jbe@10
|
594 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
|
|
jbe@0
|
595
|
|
jbe@0
|
596 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
|
597 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
|
598
|
|
jbe@0
|
599
|
|
jbe@0
|
600 CREATE TABLE "initiator" (
|
|
jbe@0
|
601 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
602 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
603 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@14
|
604 "accepted" BOOLEAN );
|
|
jbe@0
|
605 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
|
|
jbe@0
|
606
|
|
jbe@10
|
607 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
|
|
jbe@0
|
608
|
|
jbe@14
|
609 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
|
|
jbe@0
|
610
|
|
jbe@0
|
611
|
|
jbe@0
|
612 CREATE TABLE "supporter" (
|
|
jbe@0
|
613 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
614 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
615 "initiative_id" INT4,
|
|
jbe@0
|
616 "member_id" INT4,
|
|
jbe@0
|
617 "draft_id" INT8 NOT NULL,
|
|
poelzi@79
|
618 "auto_support" BOOLEAN NOT NULL DEFAULT 'f',
|
|
jbe@10
|
619 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
620 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
621 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
|
|
jbe@0
|
622
|
|
jbe@10
|
623 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
|
|
jbe@0
|
624
|
|
jbe@2
|
625 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
|
|
jbe@0
|
626
|
|
poelzi@79
|
627 CREATE FUNCTION update_supporter_drafts()
|
|
poelzi@79
|
628 RETURNS trigger
|
|
poelzi@80
|
629 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
poelzi@79
|
630 BEGIN
|
|
poelzi@79
|
631 UPDATE supporter SET draft_id = NEW.id
|
|
poelzi@79
|
632 WHERE initiative_id = NEW.initiative_id AND
|
|
poelzi@79
|
633 (auto_support = 't' OR member_id = NEW.author_id);
|
|
poelzi@79
|
634 RETURN new;
|
|
poelzi@79
|
635 END
|
|
poelzi@79
|
636 $$;
|
|
poelzi@79
|
637
|
|
poelzi@79
|
638 CREATE TRIGGER "update_draft_supporter"
|
|
poelzi@79
|
639 AFTER INSERT ON "draft"
|
|
poelzi@79
|
640 FOR EACH ROW EXECUTE PROCEDURE
|
|
poelzi@79
|
641 update_supporter_drafts();
|
|
poelzi@79
|
642
|
|
poelzi@79
|
643 COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled';
|
|
jbe@0
|
644
|
|
jbe@0
|
645 CREATE TABLE "opinion" (
|
|
jbe@0
|
646 "initiative_id" INT4 NOT NULL,
|
|
jbe@0
|
647 PRIMARY KEY ("suggestion_id", "member_id"),
|
|
jbe@0
|
648 "suggestion_id" INT8,
|
|
jbe@0
|
649 "member_id" INT4,
|
|
jbe@0
|
650 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
|
|
jbe@0
|
651 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
|
|
jbe@42
|
652 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
653 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@10
|
654 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
|
|
jbe@0
|
655
|
|
jbe@10
|
656 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
|
|
jbe@0
|
657
|
|
jbe@0
|
658 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
|
|
jbe@0
|
659
|
|
jbe@0
|
660
|
|
jbe@10
|
661 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
|
|
jbe@10
|
662
|
|
jbe@10
|
663 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
|
|
jbe@10
|
664
|
|
jbe@10
|
665
|
|
jbe@0
|
666 CREATE TABLE "delegation" (
|
|
jbe@0
|
667 "id" SERIAL8 PRIMARY KEY,
|
|
jbe@0
|
668 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
669 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@10
|
670 "scope" "delegation_scope" NOT NULL,
|
|
jbe@0
|
671 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
672 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
673 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
|
|
jbe@10
|
674 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
|
|
jbe@10
|
675 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
|
|
jbe@10
|
676 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
|
|
jbe@10
|
677 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
|
|
jbe@74
|
678 UNIQUE ("area_id", "truster_id"),
|
|
jbe@74
|
679 UNIQUE ("issue_id", "truster_id") );
|
|
jbe@75
|
680 CREATE UNIQUE INDEX "delegation_global_truster_id_unique_idx"
|
|
jbe@74
|
681 ON "delegation" ("truster_id") WHERE "scope" = 'global';
|
|
jbe@0
|
682 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
|
|
jbe@0
|
683 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
|
|
jbe@0
|
684
|
|
jbe@0
|
685 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
|
|
jbe@0
|
686
|
|
jbe@0
|
687 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
|
|
jbe@0
|
688 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
|
|
jbe@0
|
689
|
|
poelzi@79
|
690 CREATE FUNCTION "check_delegation"()
|
|
poelzi@79
|
691 RETURNS TRIGGER
|
|
poelzi@79
|
692 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
poelzi@79
|
693 BEGIN
|
|
poelzi@79
|
694 IF EXISTS (
|
|
poelzi@79
|
695 SELECT NULL FROM "member" WHERE
|
|
poelzi@79
|
696 "id" = NEW."trustee_id" AND active = 'n'
|
|
poelzi@79
|
697 ) THEN
|
|
poelzi@79
|
698 RAISE EXCEPTION 'Cannot delegate to an inactive member';
|
|
poelzi@79
|
699 END IF;
|
|
poelzi@79
|
700 RETURN NEW;
|
|
poelzi@79
|
701 END;
|
|
poelzi@79
|
702 $$;
|
|
poelzi@79
|
703
|
|
poelzi@79
|
704 CREATE TRIGGER "update_delegation"
|
|
poelzi@79
|
705 BEFORE INSERT OR UPDATE ON "delegation"
|
|
poelzi@79
|
706 FOR EACH ROW EXECUTE PROCEDURE
|
|
poelzi@79
|
707 check_delegation();
|
|
poelzi@79
|
708
|
|
poelzi@79
|
709 COMMENT ON FUNCTION "check_delegation"() IS 'Sanity checks for new delegation. Dont allow delegations to inactive members';
|
|
jbe@0
|
710
|
|
jbe@0
|
711 CREATE TABLE "direct_population_snapshot" (
|
|
jbe@0
|
712 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
713 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
714 "event" "snapshot_event",
|
|
jbe@45
|
715 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@54
|
716 "weight" INT4 );
|
|
jbe@0
|
717 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
|
|
jbe@0
|
718
|
|
jbe@0
|
719 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
|
720
|
|
jbe@0
|
721 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
722 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
|
|
jbe@0
|
723
|
|
jbe@0
|
724
|
|
jbe@0
|
725 CREATE TABLE "delegating_population_snapshot" (
|
|
jbe@0
|
726 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
727 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
728 "event" "snapshot_event",
|
|
jbe@45
|
729 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@8
|
730 "weight" INT4,
|
|
jbe@10
|
731 "scope" "delegation_scope" NOT NULL,
|
|
jbe@0
|
732 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@0
|
733 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
|
|
jbe@0
|
734
|
|
jbe@0
|
735 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
|
|
jbe@0
|
736
|
|
jbe@0
|
737 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
738 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
|
|
jbe@8
|
739 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
|
|
jbe@0
|
740 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
|
741
|
|
jbe@0
|
742
|
|
jbe@0
|
743 CREATE TABLE "direct_interest_snapshot" (
|
|
jbe@0
|
744 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
745 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
746 "event" "snapshot_event",
|
|
jbe@45
|
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@0
|
748 "weight" INT4,
|
|
jbe@0
|
749 "voting_requested" BOOLEAN );
|
|
jbe@0
|
750 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
|
|
jbe@0
|
751
|
|
jbe@0
|
752 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
|
|
jbe@0
|
753
|
|
jbe@0
|
754 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
755 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
|
|
jbe@0
|
756 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
|
|
jbe@0
|
757
|
|
jbe@0
|
758
|
|
jbe@0
|
759 CREATE TABLE "delegating_interest_snapshot" (
|
|
jbe@0
|
760 PRIMARY KEY ("issue_id", "event", "member_id"),
|
|
jbe@0
|
761 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
762 "event" "snapshot_event",
|
|
jbe@45
|
763 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@8
|
764 "weight" INT4,
|
|
jbe@10
|
765 "scope" "delegation_scope" NOT NULL,
|
|
jbe@0
|
766 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@0
|
767 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
|
|
jbe@0
|
768
|
|
jbe@0
|
769 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
|
|
jbe@0
|
770
|
|
jbe@0
|
771 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
772 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
|
|
jbe@8
|
773 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
|
|
jbe@0
|
774 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
|
775
|
|
jbe@0
|
776
|
|
jbe@0
|
777 CREATE TABLE "direct_supporter_snapshot" (
|
|
jbe@0
|
778 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
779 PRIMARY KEY ("initiative_id", "event", "member_id"),
|
|
jbe@0
|
780 "initiative_id" INT4,
|
|
jbe@0
|
781 "event" "snapshot_event",
|
|
jbe@45
|
782 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@0
|
783 "informed" BOOLEAN NOT NULL,
|
|
jbe@0
|
784 "satisfied" BOOLEAN NOT NULL,
|
|
jbe@0
|
785 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
786 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
787 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
|
|
jbe@0
|
788
|
|
jbe@8
|
789 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
|
|
jbe@0
|
790
|
|
jbe@0
|
791 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
|
|
jbe@0
|
792 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
|
|
jbe@0
|
793 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
|
|
jbe@0
|
794
|
|
jbe@0
|
795
|
|
jbe@0
|
796 CREATE TABLE "direct_voter" (
|
|
jbe@0
|
797 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
798 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@45
|
799 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@0
|
800 "weight" INT4,
|
|
jbe@0
|
801 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
|
|
jbe@0
|
802 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
|
|
jbe@0
|
803
|
|
jbe@10
|
804 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
|
|
jbe@0
|
805
|
|
jbe@0
|
806 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
|
|
jbe@0
|
807 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
|
|
jbe@0
|
808
|
|
jbe@0
|
809
|
|
jbe@0
|
810 CREATE TABLE "delegating_voter" (
|
|
jbe@0
|
811 PRIMARY KEY ("issue_id", "member_id"),
|
|
jbe@0
|
812 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@45
|
813 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
|
|
jbe@8
|
814 "weight" INT4,
|
|
jbe@10
|
815 "scope" "delegation_scope" NOT NULL,
|
|
jbe@0
|
816 "delegate_member_ids" INT4[] NOT NULL );
|
|
jbe@52
|
817 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
|
|
jbe@0
|
818
|
|
jbe@0
|
819 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
|
|
jbe@0
|
820
|
|
jbe@0
|
821 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
|
|
jbe@8
|
822 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
|
|
jbe@0
|
823 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
|
824
|
|
jbe@0
|
825
|
|
jbe@0
|
826 CREATE TABLE "vote" (
|
|
jbe@0
|
827 "issue_id" INT4 NOT NULL,
|
|
jbe@0
|
828 PRIMARY KEY ("initiative_id", "member_id"),
|
|
jbe@0
|
829 "initiative_id" INT4,
|
|
jbe@0
|
830 "member_id" INT4,
|
|
jbe@0
|
831 "grade" INT4,
|
|
jbe@0
|
832 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@0
|
833 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@0
|
834 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
|
|
jbe@0
|
835
|
|
jbe@10
|
836 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
|
|
jbe@0
|
837
|
|
jbe@0
|
838 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
|
839
|
|
jbe@0
|
840
|
|
jbe@9
|
841 CREATE TABLE "contingent" (
|
|
jbe@9
|
842 "time_frame" INTERVAL PRIMARY KEY,
|
|
jbe@9
|
843 "text_entry_limit" INT4,
|
|
jbe@9
|
844 "initiative_limit" INT4 );
|
|
jbe@9
|
845
|
|
jbe@9
|
846 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
|
|
jbe@9
|
847
|
|
jbe@9
|
848 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
|
|
jbe@9
|
849 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
|
|
jbe@9
|
850
|
|
jbe@9
|
851
|
|
jbe@0
|
852
|
|
jbe@13
|
853 --------------------------------
|
|
jbe@13
|
854 -- Writing of history entries --
|
|
jbe@13
|
855 --------------------------------
|
|
jbe@13
|
856
|
|
jbe@13
|
857 CREATE FUNCTION "write_member_history_trigger"()
|
|
jbe@13
|
858 RETURNS TRIGGER
|
|
jbe@13
|
859 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@13
|
860 BEGIN
|
|
jbe@42
|
861 IF
|
|
jbe@42
|
862 NEW."active" != OLD."active" OR
|
|
jbe@42
|
863 NEW."name" != OLD."name"
|
|
jbe@42
|
864 THEN
|
|
jbe@42
|
865 INSERT INTO "member_history"
|
|
jbe@57
|
866 ("member_id", "active", "name")
|
|
jbe@57
|
867 VALUES (NEW."id", OLD."active", OLD."name");
|
|
jbe@13
|
868 END IF;
|
|
jbe@13
|
869 RETURN NULL;
|
|
jbe@13
|
870 END;
|
|
jbe@13
|
871 $$;
|
|
jbe@13
|
872
|
|
jbe@13
|
873 CREATE TRIGGER "write_member_history"
|
|
jbe@13
|
874 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@13
|
875 "write_member_history_trigger"();
|
|
jbe@13
|
876
|
|
jbe@13
|
877 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
|
|
jbe@57
|
878 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
|
|
jbe@13
|
879
|
|
jbe@13
|
880
|
|
jbe@13
|
881
|
|
jbe@0
|
882 ----------------------------
|
|
jbe@0
|
883 -- Additional constraints --
|
|
jbe@0
|
884 ----------------------------
|
|
jbe@0
|
885
|
|
jbe@0
|
886
|
|
jbe@0
|
887 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
|
|
jbe@0
|
888 RETURNS TRIGGER
|
|
jbe@0
|
889 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
890 BEGIN
|
|
jbe@0
|
891 IF NOT EXISTS (
|
|
jbe@0
|
892 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
|
|
jbe@0
|
893 ) THEN
|
|
jbe@0
|
894 --RAISE 'Cannot create issue without an initial initiative.' USING
|
|
jbe@0
|
895 -- ERRCODE = 'integrity_constraint_violation',
|
|
jbe@0
|
896 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
|
|
jbe@0
|
897 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
|
|
jbe@0
|
898 END IF;
|
|
jbe@0
|
899 RETURN NULL;
|
|
jbe@0
|
900 END;
|
|
jbe@0
|
901 $$;
|
|
jbe@0
|
902
|
|
jbe@0
|
903 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
|
|
jbe@0
|
904 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
905 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
906 "issue_requires_first_initiative_trigger"();
|
|
jbe@0
|
907
|
|
jbe@0
|
908 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
|
|
jbe@0
|
909 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
|
|
jbe@0
|
910
|
|
jbe@0
|
911
|
|
jbe@0
|
912 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
|
|
jbe@0
|
913 RETURNS TRIGGER
|
|
jbe@0
|
914 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
915 DECLARE
|
|
jbe@0
|
916 "reference_lost" BOOLEAN;
|
|
jbe@0
|
917 BEGIN
|
|
jbe@0
|
918 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
919 "reference_lost" := TRUE;
|
|
jbe@0
|
920 ELSE
|
|
jbe@0
|
921 "reference_lost" := NEW."issue_id" != OLD."issue_id";
|
|
jbe@0
|
922 END IF;
|
|
jbe@0
|
923 IF
|
|
jbe@0
|
924 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
925 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
|
|
jbe@0
|
926 )
|
|
jbe@0
|
927 THEN
|
|
jbe@0
|
928 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
|
|
jbe@0
|
929 END IF;
|
|
jbe@0
|
930 RETURN NULL;
|
|
jbe@0
|
931 END;
|
|
jbe@0
|
932 $$;
|
|
jbe@0
|
933
|
|
jbe@0
|
934 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
|
|
jbe@0
|
935 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
936 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
937 "last_initiative_deletes_issue_trigger"();
|
|
jbe@0
|
938
|
|
jbe@0
|
939 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
|
|
jbe@0
|
940 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
|
|
jbe@0
|
941
|
|
jbe@0
|
942
|
|
jbe@0
|
943 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
|
|
jbe@0
|
944 RETURNS TRIGGER
|
|
jbe@0
|
945 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
946 BEGIN
|
|
jbe@0
|
947 IF NOT EXISTS (
|
|
jbe@0
|
948 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
|
|
jbe@0
|
949 ) THEN
|
|
jbe@0
|
950 --RAISE 'Cannot create initiative without an initial draft.' USING
|
|
jbe@0
|
951 -- ERRCODE = 'integrity_constraint_violation',
|
|
jbe@0
|
952 -- HINT = 'Create issue, initiative and draft within the same transaction.';
|
|
jbe@0
|
953 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
|
|
jbe@0
|
954 END IF;
|
|
jbe@0
|
955 RETURN NULL;
|
|
jbe@0
|
956 END;
|
|
jbe@0
|
957 $$;
|
|
jbe@0
|
958
|
|
jbe@0
|
959 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
|
|
jbe@0
|
960 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
961 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
962 "initiative_requires_first_draft_trigger"();
|
|
jbe@0
|
963
|
|
jbe@0
|
964 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
|
|
jbe@0
|
965 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
|
|
jbe@0
|
966
|
|
jbe@0
|
967
|
|
jbe@0
|
968 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
|
|
jbe@0
|
969 RETURNS TRIGGER
|
|
jbe@0
|
970 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
971 DECLARE
|
|
jbe@0
|
972 "reference_lost" BOOLEAN;
|
|
jbe@0
|
973 BEGIN
|
|
jbe@0
|
974 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
975 "reference_lost" := TRUE;
|
|
jbe@0
|
976 ELSE
|
|
jbe@0
|
977 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
|
|
jbe@0
|
978 END IF;
|
|
jbe@0
|
979 IF
|
|
jbe@0
|
980 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
981 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
|
|
jbe@0
|
982 )
|
|
jbe@0
|
983 THEN
|
|
jbe@0
|
984 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
|
|
jbe@0
|
985 END IF;
|
|
jbe@0
|
986 RETURN NULL;
|
|
jbe@0
|
987 END;
|
|
jbe@0
|
988 $$;
|
|
jbe@0
|
989
|
|
jbe@0
|
990 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
|
|
jbe@0
|
991 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
992 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
993 "last_draft_deletes_initiative_trigger"();
|
|
jbe@0
|
994
|
|
jbe@0
|
995 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
|
|
jbe@0
|
996 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
|
|
jbe@0
|
997
|
|
jbe@0
|
998
|
|
jbe@0
|
999 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
|
|
jbe@0
|
1000 RETURNS TRIGGER
|
|
jbe@0
|
1001 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1002 BEGIN
|
|
jbe@0
|
1003 IF NOT EXISTS (
|
|
jbe@0
|
1004 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
|
|
jbe@0
|
1005 ) THEN
|
|
jbe@0
|
1006 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
|
|
jbe@0
|
1007 END IF;
|
|
jbe@0
|
1008 RETURN NULL;
|
|
jbe@0
|
1009 END;
|
|
jbe@0
|
1010 $$;
|
|
jbe@0
|
1011
|
|
jbe@0
|
1012 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
|
|
jbe@0
|
1013 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
1014 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
1015 "suggestion_requires_first_opinion_trigger"();
|
|
jbe@0
|
1016
|
|
jbe@0
|
1017 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
|
|
jbe@0
|
1018 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
|
|
jbe@0
|
1019
|
|
jbe@0
|
1020
|
|
jbe@0
|
1021 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
|
|
jbe@0
|
1022 RETURNS TRIGGER
|
|
jbe@0
|
1023 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1024 DECLARE
|
|
jbe@0
|
1025 "reference_lost" BOOLEAN;
|
|
jbe@0
|
1026 BEGIN
|
|
jbe@0
|
1027 IF TG_OP = 'DELETE' THEN
|
|
jbe@0
|
1028 "reference_lost" := TRUE;
|
|
jbe@0
|
1029 ELSE
|
|
jbe@0
|
1030 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
|
|
jbe@0
|
1031 END IF;
|
|
jbe@0
|
1032 IF
|
|
jbe@0
|
1033 "reference_lost" AND NOT EXISTS (
|
|
jbe@0
|
1034 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
|
|
jbe@0
|
1035 )
|
|
jbe@0
|
1036 THEN
|
|
jbe@0
|
1037 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
|
|
jbe@0
|
1038 END IF;
|
|
jbe@0
|
1039 RETURN NULL;
|
|
jbe@0
|
1040 END;
|
|
jbe@0
|
1041 $$;
|
|
jbe@0
|
1042
|
|
jbe@0
|
1043 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
|
|
jbe@0
|
1044 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
|
|
jbe@0
|
1045 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@0
|
1046 "last_opinion_deletes_suggestion_trigger"();
|
|
jbe@0
|
1047
|
|
jbe@0
|
1048 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
|
|
jbe@0
|
1049 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
|
|
jbe@0
|
1050
|
|
jbe@0
|
1051
|
|
jbe@0
|
1052
|
|
jbe@20
|
1053 ---------------------------------------------------------------
|
|
jbe@20
|
1054 -- Ensure that votes are not modified when issues are frozen --
|
|
jbe@20
|
1055 ---------------------------------------------------------------
|
|
jbe@20
|
1056
|
|
jbe@20
|
1057 -- NOTE: Frontends should ensure this anyway, but in case of programming
|
|
jbe@20
|
1058 -- errors the following triggers ensure data integrity.
|
|
jbe@20
|
1059
|
|
jbe@20
|
1060
|
|
jbe@20
|
1061 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
|
jbe@20
|
1062 RETURNS TRIGGER
|
|
jbe@20
|
1063 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@20
|
1064 DECLARE
|
|
jbe@32
|
1065 "issue_id_v" "issue"."id"%TYPE;
|
|
jbe@32
|
1066 "issue_row" "issue"%ROWTYPE;
|
|
jbe@20
|
1067 BEGIN
|
|
jbe@32
|
1068 IF TG_OP = 'DELETE' THEN
|
|
jbe@32
|
1069 "issue_id_v" := OLD."issue_id";
|
|
jbe@32
|
1070 ELSE
|
|
jbe@32
|
1071 "issue_id_v" := NEW."issue_id";
|
|
jbe@32
|
1072 END IF;
|
|
jbe@20
|
1073 SELECT INTO "issue_row" * FROM "issue"
|
|
jbe@32
|
1074 WHERE "id" = "issue_id_v" FOR SHARE;
|
|
jbe@20
|
1075 IF "issue_row"."closed" NOTNULL THEN
|
|
jbe@20
|
1076 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
|
|
jbe@20
|
1077 END IF;
|
|
jbe@20
|
1078 RETURN NULL;
|
|
jbe@20
|
1079 END;
|
|
jbe@20
|
1080 $$;
|
|
jbe@20
|
1081
|
|
jbe@20
|
1082 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
|
jbe@20
|
1083 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
|
|
jbe@20
|
1084 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@20
|
1085 "forbid_changes_on_closed_issue_trigger"();
|
|
jbe@20
|
1086
|
|
jbe@20
|
1087 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
|
jbe@20
|
1088 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
|
|
jbe@20
|
1089 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@20
|
1090 "forbid_changes_on_closed_issue_trigger"();
|
|
jbe@20
|
1091
|
|
jbe@20
|
1092 CREATE TRIGGER "forbid_changes_on_closed_issue"
|
|
jbe@20
|
1093 AFTER INSERT OR UPDATE OR DELETE ON "vote"
|
|
jbe@20
|
1094 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@20
|
1095 "forbid_changes_on_closed_issue_trigger"();
|
|
jbe@20
|
1096
|
|
jbe@20
|
1097 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
|
|
jbe@20
|
1098 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
|
jbe@20
|
1099 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
|
jbe@20
|
1100 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
|
|
jbe@20
|
1101
|
|
jbe@20
|
1102
|
|
jbe@20
|
1103
|
|
jbe@0
|
1104 --------------------------------------------------------------------
|
|
jbe@0
|
1105 -- Auto-retrieval of fields only needed for referential integrity --
|
|
jbe@0
|
1106 --------------------------------------------------------------------
|
|
jbe@0
|
1107
|
|
jbe@20
|
1108
|
|
jbe@0
|
1109 CREATE FUNCTION "autofill_issue_id_trigger"()
|
|
jbe@0
|
1110 RETURNS TRIGGER
|
|
jbe@0
|
1111 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1112 BEGIN
|
|
jbe@0
|
1113 IF NEW."issue_id" ISNULL THEN
|
|
jbe@0
|
1114 SELECT "issue_id" INTO NEW."issue_id"
|
|
jbe@0
|
1115 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
|
jbe@0
|
1116 END IF;
|
|
jbe@0
|
1117 RETURN NEW;
|
|
jbe@0
|
1118 END;
|
|
jbe@0
|
1119 $$;
|
|
jbe@0
|
1120
|
|
jbe@0
|
1121 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
|
|
jbe@0
|
1122 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
|
jbe@0
|
1123
|
|
jbe@0
|
1124 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
|
|
jbe@0
|
1125 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
|
|
jbe@0
|
1126
|
|
jbe@0
|
1127 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
|
|
jbe@0
|
1128 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
|
|
jbe@0
|
1129 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
|
|
jbe@0
|
1130
|
|
jbe@0
|
1131
|
|
jbe@0
|
1132 CREATE FUNCTION "autofill_initiative_id_trigger"()
|
|
jbe@0
|
1133 RETURNS TRIGGER
|
|
jbe@0
|
1134 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1135 BEGIN
|
|
jbe@0
|
1136 IF NEW."initiative_id" ISNULL THEN
|
|
jbe@0
|
1137 SELECT "initiative_id" INTO NEW."initiative_id"
|
|
jbe@0
|
1138 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
|
jbe@0
|
1139 END IF;
|
|
jbe@0
|
1140 RETURN NEW;
|
|
jbe@0
|
1141 END;
|
|
jbe@0
|
1142 $$;
|
|
jbe@0
|
1143
|
|
jbe@0
|
1144 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
|
|
jbe@0
|
1145 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
|
|
jbe@0
|
1146
|
|
jbe@0
|
1147 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
|
|
jbe@0
|
1148 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
|
|
jbe@0
|
1149
|
|
jbe@0
|
1150
|
|
jbe@0
|
1151
|
|
jbe@4
|
1152 -----------------------------------------------------
|
|
jbe@4
|
1153 -- Automatic calculation of certain default values --
|
|
jbe@4
|
1154 -----------------------------------------------------
|
|
jbe@0
|
1155
|
|
jbe@22
|
1156
|
|
jbe@22
|
1157 CREATE FUNCTION "copy_timings_trigger"()
|
|
jbe@22
|
1158 RETURNS TRIGGER
|
|
jbe@22
|
1159 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@22
|
1160 DECLARE
|
|
jbe@22
|
1161 "policy_row" "policy"%ROWTYPE;
|
|
jbe@22
|
1162 BEGIN
|
|
jbe@22
|
1163 SELECT * INTO "policy_row" FROM "policy"
|
|
jbe@22
|
1164 WHERE "id" = NEW."policy_id";
|
|
jbe@22
|
1165 IF NEW."admission_time" ISNULL THEN
|
|
jbe@22
|
1166 NEW."admission_time" := "policy_row"."admission_time";
|
|
jbe@22
|
1167 END IF;
|
|
jbe@22
|
1168 IF NEW."discussion_time" ISNULL THEN
|
|
jbe@22
|
1169 NEW."discussion_time" := "policy_row"."discussion_time";
|
|
jbe@22
|
1170 END IF;
|
|
jbe@22
|
1171 IF NEW."verification_time" ISNULL THEN
|
|
jbe@22
|
1172 NEW."verification_time" := "policy_row"."verification_time";
|
|
jbe@22
|
1173 END IF;
|
|
jbe@22
|
1174 IF NEW."voting_time" ISNULL THEN
|
|
jbe@22
|
1175 NEW."voting_time" := "policy_row"."voting_time";
|
|
jbe@22
|
1176 END IF;
|
|
jbe@22
|
1177 RETURN NEW;
|
|
jbe@22
|
1178 END;
|
|
jbe@22
|
1179 $$;
|
|
jbe@22
|
1180
|
|
jbe@22
|
1181 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
|
|
jbe@22
|
1182 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
|
|
jbe@22
|
1183
|
|
jbe@22
|
1184 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
|
|
jbe@22
|
1185 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
|
|
jbe@22
|
1186
|
|
jbe@22
|
1187
|
|
jbe@0
|
1188 CREATE FUNCTION "copy_autoreject_trigger"()
|
|
jbe@0
|
1189 RETURNS TRIGGER
|
|
jbe@0
|
1190 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1191 BEGIN
|
|
jbe@0
|
1192 IF NEW."autoreject" ISNULL THEN
|
|
jbe@0
|
1193 SELECT "membership"."autoreject" INTO NEW."autoreject"
|
|
jbe@0
|
1194 FROM "issue" JOIN "membership"
|
|
jbe@0
|
1195 ON "issue"."area_id" = "membership"."area_id"
|
|
jbe@0
|
1196 WHERE "issue"."id" = NEW."issue_id"
|
|
jbe@0
|
1197 AND "membership"."member_id" = NEW."member_id";
|
|
jbe@0
|
1198 END IF;
|
|
jbe@0
|
1199 IF NEW."autoreject" ISNULL THEN
|
|
jbe@0
|
1200 NEW."autoreject" := FALSE;
|
|
jbe@0
|
1201 END IF;
|
|
jbe@0
|
1202 RETURN NEW;
|
|
jbe@0
|
1203 END;
|
|
jbe@0
|
1204 $$;
|
|
jbe@0
|
1205
|
|
jbe@0
|
1206 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
|
|
jbe@0
|
1207 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
|
|
jbe@0
|
1208
|
|
jbe@0
|
1209 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
|
|
jbe@0
|
1210 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
|
1211
|
|
jbe@0
|
1212
|
|
jbe@2
|
1213 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
|
|
jbe@2
|
1214 RETURNS TRIGGER
|
|
jbe@2
|
1215 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@2
|
1216 BEGIN
|
|
jbe@2
|
1217 IF NEW."draft_id" ISNULL THEN
|
|
jbe@2
|
1218 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
|
|
jbe@2
|
1219 WHERE "initiative_id" = NEW."initiative_id";
|
|
jbe@2
|
1220 END IF;
|
|
jbe@2
|
1221 RETURN NEW;
|
|
jbe@2
|
1222 END;
|
|
jbe@2
|
1223 $$;
|
|
jbe@2
|
1224
|
|
jbe@2
|
1225 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
|
|
jbe@2
|
1226 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
|
|
jbe@2
|
1227
|
|
jbe@2
|
1228 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
|
|
jbe@2
|
1229 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
|
|
jbe@2
|
1230
|
|
jbe@2
|
1231
|
|
jbe@0
|
1232
|
|
jbe@0
|
1233 ----------------------------------------
|
|
jbe@0
|
1234 -- Automatic creation of dependencies --
|
|
jbe@0
|
1235 ----------------------------------------
|
|
jbe@0
|
1236
|
|
jbe@22
|
1237
|
|
jbe@0
|
1238 CREATE FUNCTION "autocreate_interest_trigger"()
|
|
jbe@0
|
1239 RETURNS TRIGGER
|
|
jbe@0
|
1240 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1241 BEGIN
|
|
jbe@0
|
1242 IF NOT EXISTS (
|
|
jbe@0
|
1243 SELECT NULL FROM "initiative" JOIN "interest"
|
|
jbe@0
|
1244 ON "initiative"."issue_id" = "interest"."issue_id"
|
|
jbe@0
|
1245 WHERE "initiative"."id" = NEW."initiative_id"
|
|
jbe@0
|
1246 AND "interest"."member_id" = NEW."member_id"
|
|
jbe@0
|
1247 ) THEN
|
|
jbe@0
|
1248 BEGIN
|
|
jbe@0
|
1249 INSERT INTO "interest" ("issue_id", "member_id")
|
|
jbe@0
|
1250 SELECT "issue_id", NEW."member_id"
|
|
jbe@0
|
1251 FROM "initiative" WHERE "id" = NEW."initiative_id";
|
|
jbe@0
|
1252 EXCEPTION WHEN unique_violation THEN END;
|
|
jbe@0
|
1253 END IF;
|
|
jbe@0
|
1254 RETURN NEW;
|
|
jbe@0
|
1255 END;
|
|
jbe@0
|
1256 $$;
|
|
jbe@0
|
1257
|
|
jbe@0
|
1258 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
|
|
jbe@0
|
1259 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
|
|
jbe@0
|
1260
|
|
jbe@0
|
1261 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
|
|
jbe@0
|
1262 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
|
1263
|
|
jbe@0
|
1264
|
|
jbe@0
|
1265 CREATE FUNCTION "autocreate_supporter_trigger"()
|
|
jbe@0
|
1266 RETURNS TRIGGER
|
|
jbe@0
|
1267 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1268 BEGIN
|
|
jbe@0
|
1269 IF NOT EXISTS (
|
|
jbe@0
|
1270 SELECT NULL FROM "suggestion" JOIN "supporter"
|
|
jbe@0
|
1271 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@0
|
1272 WHERE "suggestion"."id" = NEW."suggestion_id"
|
|
jbe@0
|
1273 AND "supporter"."member_id" = NEW."member_id"
|
|
jbe@0
|
1274 ) THEN
|
|
jbe@0
|
1275 BEGIN
|
|
jbe@0
|
1276 INSERT INTO "supporter" ("initiative_id", "member_id")
|
|
jbe@0
|
1277 SELECT "initiative_id", NEW."member_id"
|
|
jbe@0
|
1278 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
|
|
jbe@0
|
1279 EXCEPTION WHEN unique_violation THEN END;
|
|
jbe@0
|
1280 END IF;
|
|
jbe@0
|
1281 RETURN NEW;
|
|
jbe@0
|
1282 END;
|
|
jbe@0
|
1283 $$;
|
|
jbe@0
|
1284
|
|
jbe@0
|
1285 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
|
|
jbe@0
|
1286 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
|
|
jbe@0
|
1287
|
|
jbe@0
|
1288 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
|
|
jbe@0
|
1289 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
|
1290
|
|
jbe@0
|
1291
|
|
jbe@0
|
1292
|
|
jbe@0
|
1293 ------------------------------------------
|
|
jbe@0
|
1294 -- Views and helper functions for views --
|
|
jbe@0
|
1295 ------------------------------------------
|
|
jbe@0
|
1296
|
|
jbe@5
|
1297
|
|
jbe@70
|
1298 CREATE VIEW "active_delegation" AS
|
|
jbe@70
|
1299 SELECT "delegation".* FROM "delegation"
|
|
jbe@70
|
1300 JOIN "member" ON "delegation"."truster_id" = "member"."id"
|
|
jbe@70
|
1301 WHERE "member"."active" = TRUE;
|
|
jbe@70
|
1302
|
|
jbe@70
|
1303 COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member';
|
|
jbe@70
|
1304
|
|
jbe@70
|
1305
|
|
jbe@5
|
1306 CREATE VIEW "global_delegation" AS
|
|
jbe@72
|
1307 SELECT "id", "truster_id", "trustee_id"
|
|
jbe@72
|
1308 FROM "active_delegation" WHERE "scope" = 'global';
|
|
jbe@5
|
1309
|
|
jbe@69
|
1310 COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members';
|
|
jbe@5
|
1311
|
|
jbe@5
|
1312
|
|
jbe@5
|
1313 CREATE VIEW "area_delegation" AS
|
|
jbe@70
|
1314 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
|
|
jbe@70
|
1315 "area"."id" AS "area_id",
|
|
jbe@70
|
1316 "delegation"."id",
|
|
jbe@70
|
1317 "delegation"."truster_id",
|
|
jbe@70
|
1318 "delegation"."trustee_id",
|
|
jbe@70
|
1319 "delegation"."scope"
|
|
jbe@70
|
1320 FROM "area" JOIN "active_delegation" AS "delegation"
|
|
jbe@70
|
1321 ON "delegation"."scope" = 'global'
|
|
jbe@70
|
1322 OR "delegation"."area_id" = "area"."id"
|
|
jbe@70
|
1323 ORDER BY
|
|
jbe@70
|
1324 "area"."id",
|
|
jbe@70
|
1325 "delegation"."truster_id",
|
|
jbe@70
|
1326 "delegation"."scope" DESC;
|
|
jbe@70
|
1327
|
|
jbe@70
|
1328 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members';
|
|
jbe@5
|
1329
|
|
jbe@5
|
1330
|
|
jbe@5
|
1331 CREATE VIEW "issue_delegation" AS
|
|
jbe@70
|
1332 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
|
jbe@70
|
1333 "issue"."id" AS "issue_id",
|
|
jbe@70
|
1334 "delegation"."id",
|
|
jbe@70
|
1335 "delegation"."truster_id",
|
|
jbe@70
|
1336 "delegation"."trustee_id",
|
|
jbe@70
|
1337 "delegation"."scope"
|
|
jbe@70
|
1338 FROM "issue" JOIN "active_delegation" AS "delegation"
|
|
jbe@70
|
1339 ON "delegation"."scope" = 'global'
|
|
jbe@70
|
1340 OR "delegation"."area_id" = "issue"."area_id"
|
|
jbe@70
|
1341 OR "delegation"."issue_id" = "issue"."id"
|
|
jbe@70
|
1342 ORDER BY
|
|
jbe@70
|
1343 "issue"."id",
|
|
jbe@70
|
1344 "delegation"."truster_id",
|
|
jbe@70
|
1345 "delegation"."scope" DESC;
|
|
jbe@70
|
1346
|
|
jbe@70
|
1347 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members';
|
|
jbe@5
|
1348
|
|
jbe@5
|
1349
|
|
jbe@5
|
1350 CREATE FUNCTION "membership_weight_with_skipping"
|
|
jbe@5
|
1351 ( "area_id_p" "area"."id"%TYPE,
|
|
jbe@5
|
1352 "member_id_p" "member"."id"%TYPE,
|
|
jbe@5
|
1353 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
|
|
jbe@5
|
1354 RETURNS INT4
|
|
jbe@5
|
1355 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@5
|
1356 DECLARE
|
|
jbe@5
|
1357 "sum_v" INT4;
|
|
jbe@5
|
1358 "delegation_row" "area_delegation"%ROWTYPE;
|
|
jbe@5
|
1359 BEGIN
|
|
jbe@5
|
1360 "sum_v" := 1;
|
|
jbe@5
|
1361 FOR "delegation_row" IN
|
|
jbe@5
|
1362 SELECT "area_delegation".*
|
|
jbe@5
|
1363 FROM "area_delegation" LEFT JOIN "membership"
|
|
jbe@5
|
1364 ON "membership"."area_id" = "area_id_p"
|
|
jbe@5
|
1365 AND "membership"."member_id" = "area_delegation"."truster_id"
|
|
jbe@5
|
1366 WHERE "area_delegation"."area_id" = "area_id_p"
|
|
jbe@5
|
1367 AND "area_delegation"."trustee_id" = "member_id_p"
|
|
jbe@5
|
1368 AND "membership"."member_id" ISNULL
|
|
jbe@5
|
1369 LOOP
|
|
jbe@5
|
1370 IF NOT
|
|
jbe@5
|
1371 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
|
|
jbe@5
|
1372 THEN
|
|
jbe@5
|
1373 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
|
|
jbe@5
|
1374 "area_id_p",
|
|
jbe@5
|
1375 "delegation_row"."truster_id",
|
|
jbe@5
|
1376 "skip_member_ids_p" || "delegation_row"."truster_id"
|
|
jbe@5
|
1377 );
|
|
jbe@5
|
1378 END IF;
|
|
jbe@5
|
1379 END LOOP;
|
|
jbe@5
|
1380 RETURN "sum_v";
|
|
jbe@5
|
1381 END;
|
|
jbe@5
|
1382 $$;
|
|
jbe@5
|
1383
|
|
jbe@8
|
1384 COMMENT ON FUNCTION "membership_weight_with_skipping"
|
|
jbe@8
|
1385 ( "area"."id"%TYPE,
|
|
jbe@8
|
1386 "member"."id"%TYPE,
|
|
jbe@8
|
1387 INT4[] )
|
|
jbe@8
|
1388 IS 'Helper function for "membership_weight" function';
|
|
jbe@8
|
1389
|
|
jbe@8
|
1390
|
|
jbe@5
|
1391 CREATE FUNCTION "membership_weight"
|
|
jbe@5
|
1392 ( "area_id_p" "area"."id"%TYPE,
|
|
jbe@5
|
1393 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
|
|
jbe@5
|
1394 RETURNS INT4
|
|
jbe@5
|
1395 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@5
|
1396 BEGIN
|
|
jbe@5
|
1397 RETURN "membership_weight_with_skipping"(
|
|
jbe@5
|
1398 "area_id_p",
|
|
jbe@5
|
1399 "member_id_p",
|
|
jbe@5
|
1400 ARRAY["member_id_p"]
|
|
jbe@5
|
1401 );
|
|
jbe@5
|
1402 END;
|
|
jbe@5
|
1403 $$;
|
|
jbe@5
|
1404
|
|
jbe@8
|
1405 COMMENT ON FUNCTION "membership_weight"
|
|
jbe@8
|
1406 ( "area"."id"%TYPE,
|
|
jbe@8
|
1407 "member"."id"%TYPE )
|
|
jbe@8
|
1408 IS 'Calculates the potential voting weight of a member in a given area';
|
|
jbe@8
|
1409
|
|
jbe@5
|
1410
|
|
jbe@4
|
1411 CREATE VIEW "member_count_view" AS
|
|
jbe@5
|
1412 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
|
|
jbe@4
|
1413
|
|
jbe@4
|
1414 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
|
|
jbe@4
|
1415
|
|
jbe@4
|
1416
|
|
jbe@4
|
1417 CREATE VIEW "area_member_count" AS
|
|
jbe@5
|
1418 SELECT
|
|
jbe@5
|
1419 "area"."id" AS "area_id",
|
|
jbe@5
|
1420 count("member"."id") AS "direct_member_count",
|
|
jbe@5
|
1421 coalesce(
|
|
jbe@5
|
1422 sum(
|
|
jbe@5
|
1423 CASE WHEN "member"."id" NOTNULL THEN
|
|
jbe@5
|
1424 "membership_weight"("area"."id", "member"."id")
|
|
jbe@5
|
1425 ELSE 0 END
|
|
jbe@5
|
1426 )
|
|
jbe@5
|
1427 ) AS "member_weight",
|
|
jbe@5
|
1428 coalesce(
|
|
jbe@5
|
1429 sum(
|
|
jbe@5
|
1430 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
|
|
jbe@5
|
1431 "membership_weight"("area"."id", "member"."id")
|
|
jbe@5
|
1432 ELSE 0 END
|
|
jbe@5
|
1433 )
|
|
jbe@5
|
1434 ) AS "autoreject_weight"
|
|
jbe@4
|
1435 FROM "area"
|
|
jbe@4
|
1436 LEFT JOIN "membership"
|
|
jbe@4
|
1437 ON "area"."id" = "membership"."area_id"
|
|
jbe@4
|
1438 LEFT JOIN "member"
|
|
jbe@4
|
1439 ON "membership"."member_id" = "member"."id"
|
|
jbe@4
|
1440 AND "member"."active"
|
|
jbe@4
|
1441 GROUP BY "area"."id";
|
|
jbe@4
|
1442
|
|
jbe@4
|
1443 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
|
|
jbe@4
|
1444
|
|
jbe@4
|
1445
|
|
jbe@9
|
1446 CREATE VIEW "opening_draft" AS
|
|
jbe@9
|
1447 SELECT "draft".* FROM (
|
|
jbe@9
|
1448 SELECT
|
|
jbe@9
|
1449 "initiative"."id" AS "initiative_id",
|
|
jbe@9
|
1450 min("draft"."id") AS "draft_id"
|
|
jbe@9
|
1451 FROM "initiative" JOIN "draft"
|
|
jbe@9
|
1452 ON "initiative"."id" = "draft"."initiative_id"
|
|
jbe@9
|
1453 GROUP BY "initiative"."id"
|
|
jbe@9
|
1454 ) AS "subquery"
|
|
jbe@9
|
1455 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
|
|
jbe@9
|
1456
|
|
jbe@9
|
1457 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
|
|
jbe@9
|
1458
|
|
jbe@9
|
1459
|
|
jbe@0
|
1460 CREATE VIEW "current_draft" AS
|
|
jbe@0
|
1461 SELECT "draft".* FROM (
|
|
jbe@0
|
1462 SELECT
|
|
jbe@0
|
1463 "initiative"."id" AS "initiative_id",
|
|
jbe@0
|
1464 max("draft"."id") AS "draft_id"
|
|
jbe@0
|
1465 FROM "initiative" JOIN "draft"
|
|
jbe@0
|
1466 ON "initiative"."id" = "draft"."initiative_id"
|
|
jbe@0
|
1467 GROUP BY "initiative"."id"
|
|
jbe@0
|
1468 ) AS "subquery"
|
|
jbe@0
|
1469 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
|
|
jbe@0
|
1470
|
|
jbe@0
|
1471 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
|
|
jbe@0
|
1472
|
|
jbe@0
|
1473
|
|
jbe@0
|
1474 CREATE VIEW "critical_opinion" AS
|
|
jbe@0
|
1475 SELECT * FROM "opinion"
|
|
jbe@0
|
1476 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
|
|
jbe@0
|
1477 OR ("degree" = -2 AND "fulfilled" = TRUE);
|
|
jbe@0
|
1478
|
|
jbe@0
|
1479 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
|
|
jbe@0
|
1480
|
|
jbe@0
|
1481
|
|
jbe@61
|
1482 CREATE VIEW "battle_view" AS
|
|
jbe@0
|
1483 SELECT
|
|
jbe@0
|
1484 "issue"."id" AS "issue_id",
|
|
jbe@10
|
1485 "winning_initiative"."id" AS "winning_initiative_id",
|
|
jbe@10
|
1486 "losing_initiative"."id" AS "losing_initiative_id",
|
|
jbe@0
|
1487 sum(
|
|
jbe@0
|
1488 CASE WHEN
|
|
jbe@0
|
1489 coalesce("better_vote"."grade", 0) >
|
|
jbe@0
|
1490 coalesce("worse_vote"."grade", 0)
|
|
jbe@0
|
1491 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@0
|
1492 ) AS "count"
|
|
jbe@0
|
1493 FROM "issue"
|
|
jbe@0
|
1494 LEFT JOIN "direct_voter"
|
|
jbe@0
|
1495 ON "issue"."id" = "direct_voter"."issue_id"
|
|
jbe@10
|
1496 JOIN "initiative" AS "winning_initiative"
|
|
jbe@10
|
1497 ON "issue"."id" = "winning_initiative"."issue_id"
|
|
jbe@10
|
1498 AND "winning_initiative"."agreed"
|
|
jbe@10
|
1499 JOIN "initiative" AS "losing_initiative"
|
|
jbe@10
|
1500 ON "issue"."id" = "losing_initiative"."issue_id"
|
|
jbe@10
|
1501 AND "losing_initiative"."agreed"
|
|
jbe@0
|
1502 LEFT JOIN "vote" AS "better_vote"
|
|
jbe@10
|
1503 ON "direct_voter"."member_id" = "better_vote"."member_id"
|
|
jbe@10
|
1504 AND "winning_initiative"."id" = "better_vote"."initiative_id"
|
|
jbe@0
|
1505 LEFT JOIN "vote" AS "worse_vote"
|
|
jbe@10
|
1506 ON "direct_voter"."member_id" = "worse_vote"."member_id"
|
|
jbe@10
|
1507 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
|
|
jbe@61
|
1508 WHERE "issue"."closed" NOTNULL
|
|
jbe@61
|
1509 AND "issue"."cleaned" ISNULL
|
|
jbe@61
|
1510 AND "winning_initiative"."id" != "losing_initiative"."id"
|
|
jbe@0
|
1511 GROUP BY
|
|
jbe@0
|
1512 "issue"."id",
|
|
jbe@10
|
1513 "winning_initiative"."id",
|
|
jbe@10
|
1514 "losing_initiative"."id";
|
|
jbe@0
|
1515
|
|
jbe@61
|
1516 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
|
|
jbe@0
|
1517
|
|
jbe@0
|
1518
|
|
jbe@1
|
1519 CREATE VIEW "expired_session" AS
|
|
jbe@1
|
1520 SELECT * FROM "session" WHERE now() > "expiry";
|
|
jbe@1
|
1521
|
|
jbe@1
|
1522 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
|
|
jbe@1
|
1523 DELETE FROM "session" WHERE "ident" = OLD."ident";
|
|
jbe@1
|
1524
|
|
jbe@1
|
1525 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
|
|
jbe@1
|
1526 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
|
|
jbe@1
|
1527
|
|
jbe@1
|
1528
|
|
jbe@0
|
1529 CREATE VIEW "open_issue" AS
|
|
jbe@0
|
1530 SELECT * FROM "issue" WHERE "closed" ISNULL;
|
|
jbe@0
|
1531
|
|
jbe@0
|
1532 COMMENT ON VIEW "open_issue" IS 'All open issues';
|
|
jbe@0
|
1533
|
|
jbe@0
|
1534
|
|
jbe@0
|
1535 CREATE VIEW "issue_with_ranks_missing" AS
|
|
jbe@0
|
1536 SELECT * FROM "issue"
|
|
jbe@3
|
1537 WHERE "fully_frozen" NOTNULL
|
|
jbe@0
|
1538 AND "closed" NOTNULL
|
|
jbe@0
|
1539 AND "ranks_available" = FALSE;
|
|
jbe@0
|
1540
|
|
jbe@0
|
1541 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
|
|
jbe@0
|
1542
|
|
jbe@0
|
1543
|
|
jbe@9
|
1544 CREATE VIEW "member_contingent" AS
|
|
jbe@9
|
1545 SELECT
|
|
jbe@9
|
1546 "member"."id" AS "member_id",
|
|
jbe@9
|
1547 "contingent"."time_frame",
|
|
jbe@9
|
1548 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
|
|
jbe@9
|
1549 (
|
|
jbe@9
|
1550 SELECT count(1) FROM "draft"
|
|
jbe@9
|
1551 WHERE "draft"."author_id" = "member"."id"
|
|
jbe@9
|
1552 AND "draft"."created" > now() - "contingent"."time_frame"
|
|
jbe@9
|
1553 ) + (
|
|
jbe@9
|
1554 SELECT count(1) FROM "suggestion"
|
|
jbe@9
|
1555 WHERE "suggestion"."author_id" = "member"."id"
|
|
jbe@9
|
1556 AND "suggestion"."created" > now() - "contingent"."time_frame"
|
|
jbe@9
|
1557 )
|
|
jbe@9
|
1558 ELSE NULL END AS "text_entry_count",
|
|
jbe@9
|
1559 "contingent"."text_entry_limit",
|
|
jbe@9
|
1560 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
|
|
jbe@9
|
1561 SELECT count(1) FROM "opening_draft"
|
|
jbe@9
|
1562 WHERE "opening_draft"."author_id" = "member"."id"
|
|
jbe@9
|
1563 AND "opening_draft"."created" > now() - "contingent"."time_frame"
|
|
jbe@9
|
1564 ) ELSE NULL END AS "initiative_count",
|
|
jbe@9
|
1565 "contingent"."initiative_limit"
|
|
jbe@9
|
1566 FROM "member" CROSS JOIN "contingent";
|
|
jbe@9
|
1567
|
|
jbe@9
|
1568 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
|
|
jbe@9
|
1569
|
|
jbe@9
|
1570 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
|
|
jbe@9
|
1571 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
|
|
jbe@9
|
1572
|
|
jbe@9
|
1573
|
|
jbe@9
|
1574 CREATE VIEW "member_contingent_left" AS
|
|
jbe@9
|
1575 SELECT
|
|
jbe@9
|
1576 "member_id",
|
|
jbe@9
|
1577 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
|
|
jbe@9
|
1578 max("initiative_limit" - "initiative_count") AS "initiatives_left"
|
|
jbe@9
|
1579 FROM "member_contingent" GROUP BY "member_id";
|
|
jbe@9
|
1580
|
|
jbe@9
|
1581 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
|
|
jbe@9
|
1582
|
|
jbe@9
|
1583
|
|
jbe@16
|
1584 CREATE TYPE "timeline_event" AS ENUM (
|
|
jbe@16
|
1585 'issue_created',
|
|
jbe@16
|
1586 'issue_canceled',
|
|
jbe@16
|
1587 'issue_accepted',
|
|
jbe@16
|
1588 'issue_half_frozen',
|
|
jbe@16
|
1589 'issue_finished_without_voting',
|
|
jbe@16
|
1590 'issue_voting_started',
|
|
jbe@16
|
1591 'issue_finished_after_voting',
|
|
jbe@16
|
1592 'initiative_created',
|
|
jbe@16
|
1593 'initiative_revoked',
|
|
jbe@16
|
1594 'draft_created',
|
|
jbe@16
|
1595 'suggestion_created');
|
|
jbe@16
|
1596
|
|
jbe@16
|
1597 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
|
|
jbe@16
|
1598
|
|
jbe@16
|
1599
|
|
jbe@16
|
1600 CREATE VIEW "timeline_issue" AS
|
|
jbe@16
|
1601 SELECT
|
|
jbe@16
|
1602 "created" AS "occurrence",
|
|
jbe@16
|
1603 'issue_created'::"timeline_event" AS "event",
|
|
jbe@16
|
1604 "id" AS "issue_id"
|
|
jbe@16
|
1605 FROM "issue"
|
|
jbe@16
|
1606 UNION ALL
|
|
jbe@16
|
1607 SELECT
|
|
jbe@16
|
1608 "closed" AS "occurrence",
|
|
jbe@16
|
1609 'issue_canceled'::"timeline_event" AS "event",
|
|
jbe@16
|
1610 "id" AS "issue_id"
|
|
jbe@16
|
1611 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
|
|
jbe@16
|
1612 UNION ALL
|
|
jbe@16
|
1613 SELECT
|
|
jbe@16
|
1614 "accepted" AS "occurrence",
|
|
jbe@16
|
1615 'issue_accepted'::"timeline_event" AS "event",
|
|
jbe@16
|
1616 "id" AS "issue_id"
|
|
jbe@16
|
1617 FROM "issue" WHERE "accepted" NOTNULL
|
|
jbe@16
|
1618 UNION ALL
|
|
jbe@16
|
1619 SELECT
|
|
jbe@16
|
1620 "half_frozen" AS "occurrence",
|
|
jbe@16
|
1621 'issue_half_frozen'::"timeline_event" AS "event",
|
|
jbe@16
|
1622 "id" AS "issue_id"
|
|
jbe@16
|
1623 FROM "issue" WHERE "half_frozen" NOTNULL
|
|
jbe@16
|
1624 UNION ALL
|
|
jbe@16
|
1625 SELECT
|
|
jbe@16
|
1626 "fully_frozen" AS "occurrence",
|
|
jbe@16
|
1627 'issue_voting_started'::"timeline_event" AS "event",
|
|
jbe@16
|
1628 "id" AS "issue_id"
|
|
jbe@16
|
1629 FROM "issue"
|
|
jbe@17
|
1630 WHERE "fully_frozen" NOTNULL
|
|
jbe@17
|
1631 AND ("closed" ISNULL OR "closed" != "fully_frozen")
|
|
jbe@16
|
1632 UNION ALL
|
|
jbe@16
|
1633 SELECT
|
|
jbe@16
|
1634 "closed" AS "occurrence",
|
|
jbe@16
|
1635 CASE WHEN "fully_frozen" = "closed" THEN
|
|
jbe@16
|
1636 'issue_finished_without_voting'::"timeline_event"
|
|
jbe@16
|
1637 ELSE
|
|
jbe@16
|
1638 'issue_finished_after_voting'::"timeline_event"
|
|
jbe@16
|
1639 END AS "event",
|
|
jbe@16
|
1640 "id" AS "issue_id"
|
|
jbe@16
|
1641 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
|
|
jbe@16
|
1642
|
|
jbe@16
|
1643 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
|
|
jbe@16
|
1644
|
|
jbe@16
|
1645
|
|
jbe@16
|
1646 CREATE VIEW "timeline_initiative" AS
|
|
jbe@16
|
1647 SELECT
|
|
jbe@16
|
1648 "created" AS "occurrence",
|
|
jbe@16
|
1649 'initiative_created'::"timeline_event" AS "event",
|
|
jbe@16
|
1650 "id" AS "initiative_id"
|
|
jbe@16
|
1651 FROM "initiative"
|
|
jbe@16
|
1652 UNION ALL
|
|
jbe@16
|
1653 SELECT
|
|
jbe@16
|
1654 "revoked" AS "occurrence",
|
|
jbe@16
|
1655 'initiative_revoked'::"timeline_event" AS "event",
|
|
jbe@16
|
1656 "id" AS "initiative_id"
|
|
jbe@16
|
1657 FROM "initiative" WHERE "revoked" NOTNULL;
|
|
jbe@16
|
1658
|
|
jbe@16
|
1659 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
|
|
jbe@16
|
1660
|
|
jbe@16
|
1661
|
|
jbe@16
|
1662 CREATE VIEW "timeline_draft" AS
|
|
jbe@16
|
1663 SELECT
|
|
jbe@16
|
1664 "created" AS "occurrence",
|
|
jbe@16
|
1665 'draft_created'::"timeline_event" AS "event",
|
|
jbe@16
|
1666 "id" AS "draft_id"
|
|
jbe@16
|
1667 FROM "draft";
|
|
jbe@16
|
1668
|
|
jbe@16
|
1669 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
|
|
jbe@16
|
1670
|
|
jbe@16
|
1671
|
|
jbe@16
|
1672 CREATE VIEW "timeline_suggestion" AS
|
|
jbe@16
|
1673 SELECT
|
|
jbe@16
|
1674 "created" AS "occurrence",
|
|
jbe@16
|
1675 'suggestion_created'::"timeline_event" AS "event",
|
|
jbe@16
|
1676 "id" AS "suggestion_id"
|
|
jbe@16
|
1677 FROM "suggestion";
|
|
jbe@16
|
1678
|
|
jbe@16
|
1679 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
|
|
jbe@16
|
1680
|
|
jbe@16
|
1681
|
|
jbe@16
|
1682 CREATE VIEW "timeline" AS
|
|
jbe@16
|
1683 SELECT
|
|
jbe@16
|
1684 "occurrence",
|
|
jbe@16
|
1685 "event",
|
|
jbe@16
|
1686 "issue_id",
|
|
jbe@16
|
1687 NULL AS "initiative_id",
|
|
jbe@16
|
1688 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
|
|
jbe@16
|
1689 NULL::INT8 AS "suggestion_id"
|
|
jbe@16
|
1690 FROM "timeline_issue"
|
|
jbe@16
|
1691 UNION ALL
|
|
jbe@16
|
1692 SELECT
|
|
jbe@16
|
1693 "occurrence",
|
|
jbe@16
|
1694 "event",
|
|
jbe@16
|
1695 NULL AS "issue_id",
|
|
jbe@16
|
1696 "initiative_id",
|
|
jbe@16
|
1697 NULL AS "draft_id",
|
|
jbe@16
|
1698 NULL AS "suggestion_id"
|
|
jbe@16
|
1699 FROM "timeline_initiative"
|
|
jbe@16
|
1700 UNION ALL
|
|
jbe@16
|
1701 SELECT
|
|
jbe@16
|
1702 "occurrence",
|
|
jbe@16
|
1703 "event",
|
|
jbe@16
|
1704 NULL AS "issue_id",
|
|
jbe@16
|
1705 NULL AS "initiative_id",
|
|
jbe@16
|
1706 "draft_id",
|
|
jbe@16
|
1707 NULL AS "suggestion_id"
|
|
jbe@16
|
1708 FROM "timeline_draft"
|
|
jbe@16
|
1709 UNION ALL
|
|
jbe@16
|
1710 SELECT
|
|
jbe@16
|
1711 "occurrence",
|
|
jbe@16
|
1712 "event",
|
|
jbe@16
|
1713 NULL AS "issue_id",
|
|
jbe@16
|
1714 NULL AS "initiative_id",
|
|
jbe@16
|
1715 NULL AS "draft_id",
|
|
jbe@16
|
1716 "suggestion_id"
|
|
jbe@16
|
1717 FROM "timeline_suggestion";
|
|
jbe@16
|
1718
|
|
jbe@16
|
1719 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
|
|
jbe@16
|
1720
|
|
jbe@16
|
1721
|
|
jbe@0
|
1722
|
|
jbe@5
|
1723 --------------------------------------------------
|
|
jbe@5
|
1724 -- Set returning function for delegation chains --
|
|
jbe@5
|
1725 --------------------------------------------------
|
|
jbe@5
|
1726
|
|
jbe@5
|
1727
|
|
jbe@5
|
1728 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
|
|
jbe@5
|
1729 ('first', 'intermediate', 'last', 'repetition');
|
|
jbe@5
|
1730
|
|
jbe@5
|
1731 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
|
|
jbe@5
|
1732
|
|
jbe@5
|
1733
|
|
jbe@5
|
1734 CREATE TYPE "delegation_chain_row" AS (
|
|
jbe@5
|
1735 "index" INT4,
|
|
jbe@5
|
1736 "member_id" INT4,
|
|
jbe@5
|
1737 "member_active" BOOLEAN,
|
|
jbe@5
|
1738 "participation" BOOLEAN,
|
|
jbe@5
|
1739 "overridden" BOOLEAN,
|
|
jbe@5
|
1740 "scope_in" "delegation_scope",
|
|
jbe@5
|
1741 "scope_out" "delegation_scope",
|
|
jbe@5
|
1742 "loop" "delegation_chain_loop_tag" );
|
|
jbe@5
|
1743
|
|
jbe@5
|
1744 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
|
|
jbe@5
|
1745
|
|
jbe@5
|
1746 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
|
|
jbe@5
|
1747 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
|
|
jbe@5
|
1748 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
|
|
jbe@5
|
1749 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
|
|
jbe@5
|
1750 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
|
|
jbe@5
|
1751 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
|
|
jbe@5
|
1752
|
|
jbe@5
|
1753
|
|
jbe@5
|
1754 CREATE FUNCTION "delegation_chain"
|
|
jbe@5
|
1755 ( "member_id_p" "member"."id"%TYPE,
|
|
jbe@5
|
1756 "area_id_p" "area"."id"%TYPE,
|
|
jbe@5
|
1757 "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@5
|
1758 "simulate_trustee_id_p" "member"."id"%TYPE )
|
|
jbe@5
|
1759 RETURNS SETOF "delegation_chain_row"
|
|
jbe@5
|
1760 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@5
|
1761 DECLARE
|
|
jbe@5
|
1762 "issue_row" "issue"%ROWTYPE;
|
|
jbe@5
|
1763 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
|
jbe@5
|
1764 "loop_member_id_v" "member"."id"%TYPE;
|
|
jbe@5
|
1765 "output_row" "delegation_chain_row";
|
|
jbe@5
|
1766 "output_rows" "delegation_chain_row"[];
|
|
jbe@5
|
1767 "delegation_row" "delegation"%ROWTYPE;
|
|
jbe@5
|
1768 "row_count" INT4;
|
|
jbe@5
|
1769 "i" INT4;
|
|
jbe@5
|
1770 "loop_v" BOOLEAN;
|
|
jbe@5
|
1771 BEGIN
|
|
jbe@5
|
1772 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@5
|
1773 "visited_member_ids" := '{}';
|
|
jbe@5
|
1774 "loop_member_id_v" := NULL;
|
|
jbe@5
|
1775 "output_rows" := '{}';
|
|
jbe@5
|
1776 "output_row"."index" := 0;
|
|
jbe@5
|
1777 "output_row"."member_id" := "member_id_p";
|
|
jbe@5
|
1778 "output_row"."member_active" := TRUE;
|
|
jbe@5
|
1779 "output_row"."participation" := FALSE;
|
|
jbe@5
|
1780 "output_row"."overridden" := FALSE;
|
|
jbe@5
|
1781 "output_row"."scope_out" := NULL;
|
|
jbe@5
|
1782 LOOP
|
|
jbe@5
|
1783 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
|
jbe@5
|
1784 "loop_member_id_v" := "output_row"."member_id";
|
|
jbe@5
|
1785 ELSE
|
|
jbe@5
|
1786 "visited_member_ids" :=
|
|
jbe@5
|
1787 "visited_member_ids" || "output_row"."member_id";
|
|
jbe@5
|
1788 END IF;
|
|
jbe@5
|
1789 IF "output_row"."participation" THEN
|
|
jbe@5
|
1790 "output_row"."overridden" := TRUE;
|
|
jbe@5
|
1791 END IF;
|
|
jbe@5
|
1792 "output_row"."scope_in" := "output_row"."scope_out";
|
|
jbe@5
|
1793 IF EXISTS (
|
|
jbe@5
|
1794 SELECT NULL FROM "member"
|
|
jbe@5
|
1795 WHERE "id" = "output_row"."member_id" AND "active"
|
|
jbe@5
|
1796 ) THEN
|
|
jbe@5
|
1797 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
|
jbe@5
|
1798 SELECT * INTO "delegation_row" FROM "delegation"
|
|
jbe@5
|
1799 WHERE "truster_id" = "output_row"."member_id"
|
|
jbe@10
|
1800 AND "scope" = 'global';
|
|
jbe@5
|
1801 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
|
|
jbe@5
|
1802 "output_row"."participation" := EXISTS (
|
|
jbe@5
|
1803 SELECT NULL FROM "membership"
|
|
jbe@5
|
1804 WHERE "area_id" = "area_id_p"
|
|
jbe@5
|
1805 AND "member_id" = "output_row"."member_id"
|
|
jbe@5
|
1806 );
|
|
jbe@5
|
1807 SELECT * INTO "delegation_row" FROM "delegation"
|
|
jbe@5
|
1808 WHERE "truster_id" = "output_row"."member_id"
|
|
jbe@10
|
1809 AND ("scope" = 'global' OR "area_id" = "area_id_p")
|
|
jbe@10
|
1810 ORDER BY "scope" DESC;
|
|
jbe@5
|
1811 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
|
|
jbe@5
|
1812 "output_row"."participation" := EXISTS (
|
|
jbe@5
|
1813 SELECT NULL FROM "interest"
|
|
jbe@5
|
1814 WHERE "issue_id" = "issue_id_p"
|
|
jbe@5
|
1815 AND "member_id" = "output_row"."member_id"
|
|
jbe@5
|
1816 );
|
|
jbe@5
|
1817 SELECT * INTO "delegation_row" FROM "delegation"
|
|
jbe@5
|
1818 WHERE "truster_id" = "output_row"."member_id"
|
|
jbe@10
|
1819 AND ("scope" = 'global' OR
|
|
jbe@10
|
1820 "area_id" = "issue_row"."area_id" OR
|
|
jbe@10
|
1821 "issue_id" = "issue_id_p"
|
|
jbe@10
|
1822 )
|
|
jbe@10
|
1823 ORDER BY "scope" DESC;
|
|
jbe@5
|
1824 ELSE
|
|
jbe@5
|
1825 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
|
|
jbe@5
|
1826 END IF;
|
|
jbe@5
|
1827 ELSE
|
|
jbe@5
|
1828 "output_row"."member_active" := FALSE;
|
|
jbe@5
|
1829 "output_row"."participation" := FALSE;
|
|
jbe@5
|
1830 "output_row"."scope_out" := NULL;
|
|
jbe@5
|
1831 "delegation_row" := ROW(NULL);
|
|
jbe@5
|
1832 END IF;
|
|
jbe@5
|
1833 IF
|
|
jbe@5
|
1834 "output_row"."member_id" = "member_id_p" AND
|
|
jbe@5
|
1835 "simulate_trustee_id_p" NOTNULL
|
|
jbe@5
|
1836 THEN
|
|
jbe@5
|
1837 "output_row"."scope_out" := CASE
|
|
jbe@5
|
1838 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
|
|
jbe@5
|
1839 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
|
|
jbe@5
|
1840 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
|
|
jbe@5
|
1841 END;
|
|
jbe@5
|
1842 "output_rows" := "output_rows" || "output_row";
|
|
jbe@5
|
1843 "output_row"."member_id" := "simulate_trustee_id_p";
|
|
jbe@5
|
1844 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
|
jbe@10
|
1845 "output_row"."scope_out" := "delegation_row"."scope";
|
|
jbe@5
|
1846 "output_rows" := "output_rows" || "output_row";
|
|
jbe@5
|
1847 "output_row"."member_id" := "delegation_row"."trustee_id";
|
|
jbe@5
|
1848 ELSE
|
|
jbe@5
|
1849 "output_row"."scope_out" := NULL;
|
|
jbe@5
|
1850 "output_rows" := "output_rows" || "output_row";
|
|
jbe@5
|
1851 EXIT;
|
|
jbe@5
|
1852 END IF;
|
|
jbe@5
|
1853 EXIT WHEN "loop_member_id_v" NOTNULL;
|
|
jbe@5
|
1854 "output_row"."index" := "output_row"."index" + 1;
|
|
jbe@5
|
1855 END LOOP;
|
|
jbe@5
|
1856 "row_count" := array_upper("output_rows", 1);
|
|
jbe@5
|
1857 "i" := 1;
|
|
jbe@5
|
1858 "loop_v" := FALSE;
|
|
jbe@5
|
1859 LOOP
|
|
jbe@5
|
1860 "output_row" := "output_rows"["i"];
|
|
jbe@5
|
1861 EXIT WHEN "output_row"."member_id" ISNULL;
|
|
jbe@5
|
1862 IF "loop_v" THEN
|
|
jbe@5
|
1863 IF "i" + 1 = "row_count" THEN
|
|
jbe@5
|
1864 "output_row"."loop" := 'last';
|
|
jbe@5
|
1865 ELSIF "i" = "row_count" THEN
|
|
jbe@5
|
1866 "output_row"."loop" := 'repetition';
|
|
jbe@5
|
1867 ELSE
|
|
jbe@5
|
1868 "output_row"."loop" := 'intermediate';
|
|
jbe@5
|
1869 END IF;
|
|
jbe@5
|
1870 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
|
jbe@5
|
1871 "output_row"."loop" := 'first';
|
|
jbe@5
|
1872 "loop_v" := TRUE;
|
|
jbe@5
|
1873 END IF;
|
|
jbe@5
|
1874 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
|
jbe@5
|
1875 "output_row"."participation" := NULL;
|
|
jbe@5
|
1876 END IF;
|
|
jbe@5
|
1877 RETURN NEXT "output_row";
|
|
jbe@5
|
1878 "i" := "i" + 1;
|
|
jbe@5
|
1879 END LOOP;
|
|
jbe@5
|
1880 RETURN;
|
|
jbe@5
|
1881 END;
|
|
jbe@5
|
1882 $$;
|
|
jbe@5
|
1883
|
|
jbe@5
|
1884 COMMENT ON FUNCTION "delegation_chain"
|
|
jbe@5
|
1885 ( "member"."id"%TYPE,
|
|
jbe@5
|
1886 "area"."id"%TYPE,
|
|
jbe@5
|
1887 "issue"."id"%TYPE,
|
|
jbe@5
|
1888 "member"."id"%TYPE )
|
|
jbe@5
|
1889 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
|
|
jbe@5
|
1890
|
|
jbe@5
|
1891 CREATE FUNCTION "delegation_chain"
|
|
jbe@5
|
1892 ( "member_id_p" "member"."id"%TYPE,
|
|
jbe@5
|
1893 "area_id_p" "area"."id"%TYPE,
|
|
jbe@5
|
1894 "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@5
|
1895 RETURNS SETOF "delegation_chain_row"
|
|
jbe@5
|
1896 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@5
|
1897 DECLARE
|
|
jbe@5
|
1898 "result_row" "delegation_chain_row";
|
|
jbe@5
|
1899 BEGIN
|
|
jbe@5
|
1900 FOR "result_row" IN
|
|
jbe@5
|
1901 SELECT * FROM "delegation_chain"(
|
|
jbe@5
|
1902 "member_id_p", "area_id_p", "issue_id_p", NULL
|
|
jbe@5
|
1903 )
|
|
jbe@5
|
1904 LOOP
|
|
jbe@5
|
1905 RETURN NEXT "result_row";
|
|
jbe@5
|
1906 END LOOP;
|
|
jbe@5
|
1907 RETURN;
|
|
jbe@5
|
1908 END;
|
|
jbe@5
|
1909 $$;
|
|
jbe@5
|
1910
|
|
jbe@5
|
1911 COMMENT ON FUNCTION "delegation_chain"
|
|
jbe@5
|
1912 ( "member"."id"%TYPE,
|
|
jbe@5
|
1913 "area"."id"%TYPE,
|
|
jbe@5
|
1914 "issue"."id"%TYPE )
|
|
jbe@5
|
1915 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
|
|
jbe@5
|
1916
|
|
jbe@5
|
1917
|
|
jbe@5
|
1918
|
|
jbe@0
|
1919 ------------------------------
|
|
jbe@0
|
1920 -- Comparison by vote count --
|
|
jbe@0
|
1921 ------------------------------
|
|
jbe@0
|
1922
|
|
jbe@0
|
1923 CREATE FUNCTION "vote_ratio"
|
|
jbe@0
|
1924 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
|
|
jbe@0
|
1925 "negative_votes_p" "initiative"."negative_votes"%TYPE )
|
|
jbe@0
|
1926 RETURNS FLOAT8
|
|
jbe@0
|
1927 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@0
|
1928 BEGIN
|
|
jbe@30
|
1929 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
|
|
jbe@30
|
1930 RETURN
|
|
jbe@30
|
1931 "positive_votes_p"::FLOAT8 /
|
|
jbe@30
|
1932 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
|
|
jbe@30
|
1933 ELSIF "positive_votes_p" > 0 THEN
|
|
jbe@30
|
1934 RETURN "positive_votes_p";
|
|
jbe@30
|
1935 ELSIF "negative_votes_p" > 0 THEN
|
|
jbe@30
|
1936 RETURN 1 - "negative_votes_p";
|
|
jbe@0
|
1937 ELSE
|
|
jbe@0
|
1938 RETURN 0.5;
|
|
jbe@0
|
1939 END IF;
|
|
jbe@0
|
1940 END;
|
|
jbe@0
|
1941 $$;
|
|
jbe@0
|
1942
|
|
jbe@0
|
1943 COMMENT ON FUNCTION "vote_ratio"
|
|
jbe@0
|
1944 ( "initiative"."positive_votes"%TYPE,
|
|
jbe@0
|
1945 "initiative"."negative_votes"%TYPE )
|
|
jbe@30
|
1946 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
|
|
jbe@0
|
1947
|
|
jbe@0
|
1948
|
|
jbe@0
|
1949
|
|
jbe@0
|
1950 ------------------------------------------------
|
|
jbe@0
|
1951 -- Locking for snapshots and voting procedure --
|
|
jbe@0
|
1952 ------------------------------------------------
|
|
jbe@0
|
1953
|
|
jbe@67
|
1954
|
|
jbe@67
|
1955 CREATE FUNCTION "share_row_lock_issue_trigger"()
|
|
jbe@67
|
1956 RETURNS TRIGGER
|
|
jbe@67
|
1957 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
1958 BEGIN
|
|
jbe@67
|
1959 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
|
|
jbe@67
|
1960 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
|
|
jbe@67
|
1961 END IF;
|
|
jbe@67
|
1962 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
jbe@67
|
1963 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
|
|
jbe@67
|
1964 RETURN NEW;
|
|
jbe@67
|
1965 ELSE
|
|
jbe@67
|
1966 RETURN OLD;
|
|
jbe@67
|
1967 END IF;
|
|
jbe@67
|
1968 END;
|
|
jbe@67
|
1969 $$;
|
|
jbe@67
|
1970
|
|
jbe@67
|
1971 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
|
|
jbe@67
|
1972
|
|
jbe@67
|
1973
|
|
jbe@67
|
1974 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
|
|
jbe@67
|
1975 RETURNS TRIGGER
|
|
jbe@0
|
1976 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
1977 BEGIN
|
|
jbe@67
|
1978 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
|
|
jbe@67
|
1979 PERFORM NULL FROM "issue"
|
|
jbe@67
|
1980 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@67
|
1981 WHERE "initiative"."id" = OLD."initiative_id"
|
|
jbe@67
|
1982 FOR SHARE OF "issue";
|
|
jbe@67
|
1983 END IF;
|
|
jbe@67
|
1984 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
jbe@67
|
1985 PERFORM NULL FROM "issue"
|
|
jbe@67
|
1986 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@67
|
1987 WHERE "initiative"."id" = NEW."initiative_id"
|
|
jbe@67
|
1988 FOR SHARE OF "issue";
|
|
jbe@67
|
1989 RETURN NEW;
|
|
jbe@67
|
1990 ELSE
|
|
jbe@67
|
1991 RETURN OLD;
|
|
jbe@67
|
1992 END IF;
|
|
jbe@67
|
1993 END;
|
|
jbe@67
|
1994 $$;
|
|
jbe@67
|
1995
|
|
jbe@67
|
1996 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
|
|
jbe@67
|
1997
|
|
jbe@67
|
1998
|
|
jbe@67
|
1999 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2000 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
|
|
jbe@67
|
2001 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2002 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2003
|
|
jbe@67
|
2004 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2005 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
|
|
jbe@67
|
2006 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2007 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2008
|
|
jbe@67
|
2009 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2010 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
|
|
jbe@67
|
2011 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2012 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2013
|
|
jbe@67
|
2014 CREATE TRIGGER "share_row_lock_issue_via_initiative"
|
|
jbe@67
|
2015 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
|
|
jbe@67
|
2016 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2017 "share_row_lock_issue_via_initiative_trigger"();
|
|
jbe@67
|
2018
|
|
jbe@67
|
2019 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2020 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
|
|
jbe@67
|
2021 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2022 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2023
|
|
jbe@67
|
2024 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2025 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
|
|
jbe@67
|
2026 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2027 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2028
|
|
jbe@67
|
2029 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
2030 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
|
|
jbe@67
|
2031 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
2032 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
2033
|
|
jbe@67
|
2034 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
|
|
jbe@67
|
2035 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
|
|
jbe@67
|
2036 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
|
|
jbe@67
|
2037 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
|
|
jbe@67
|
2038 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
|
|
jbe@67
|
2039 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
|
|
jbe@67
|
2040 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
|
|
jbe@67
|
2041
|
|
jbe@67
|
2042
|
|
jbe@67
|
2043 CREATE FUNCTION "lock_issue"
|
|
jbe@67
|
2044 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@67
|
2045 RETURNS VOID
|
|
jbe@67
|
2046 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
2047 BEGIN
|
|
jbe@67
|
2048 LOCK TABLE "member" IN SHARE MODE;
|
|
jbe@67
|
2049 LOCK TABLE "membership" IN SHARE MODE;
|
|
jbe@67
|
2050 LOCK TABLE "policy" IN SHARE MODE;
|
|
jbe@67
|
2051 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
|
jbe@67
|
2052 -- NOTE: The row-level exclusive lock in combination with the
|
|
jbe@67
|
2053 -- share_row_lock_issue(_via_initiative)_trigger functions (which
|
|
jbe@67
|
2054 -- acquire a row-level share lock on the issue) ensure that no data
|
|
jbe@67
|
2055 -- is changed, which could affect calculation of snapshots or
|
|
jbe@67
|
2056 -- counting of votes. Table "delegation" must be table-level-locked,
|
|
jbe@67
|
2057 -- as it also contains issue- and global-scope delegations.
|
|
jbe@67
|
2058 LOCK TABLE "delegation" IN SHARE MODE;
|
|
jbe@0
|
2059 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
2060 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
2061 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
2062 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
2063 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@0
|
2064 RETURN;
|
|
jbe@0
|
2065 END;
|
|
jbe@0
|
2066 $$;
|
|
jbe@0
|
2067
|
|
jbe@67
|
2068 COMMENT ON FUNCTION "lock_issue"
|
|
jbe@67
|
2069 ( "issue"."id"%TYPE )
|
|
jbe@67
|
2070 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
|
|
jbe@0
|
2071
|
|
jbe@0
|
2072
|
|
jbe@0
|
2073
|
|
jbe@4
|
2074 -------------------------------
|
|
jbe@4
|
2075 -- Materialize member counts --
|
|
jbe@4
|
2076 -------------------------------
|
|
jbe@4
|
2077
|
|
jbe@4
|
2078 CREATE FUNCTION "calculate_member_counts"()
|
|
jbe@4
|
2079 RETURNS VOID
|
|
jbe@4
|
2080 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@4
|
2081 BEGIN
|
|
jbe@67
|
2082 LOCK TABLE "member" IN SHARE MODE;
|
|
jbe@67
|
2083 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
|
|
jbe@67
|
2084 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
|
jbe@67
|
2085 LOCK TABLE "membership" IN SHARE MODE;
|
|
jbe@4
|
2086 DELETE FROM "member_count";
|
|
jbe@5
|
2087 INSERT INTO "member_count" ("total_count")
|
|
jbe@5
|
2088 SELECT "total_count" FROM "member_count_view";
|
|
jbe@5
|
2089 UPDATE "area" SET
|
|
jbe@5
|
2090 "direct_member_count" = "view"."direct_member_count",
|
|
jbe@5
|
2091 "member_weight" = "view"."member_weight",
|
|
jbe@5
|
2092 "autoreject_weight" = "view"."autoreject_weight"
|
|
jbe@5
|
2093 FROM "area_member_count" AS "view"
|
|
jbe@5
|
2094 WHERE "view"."area_id" = "area"."id";
|
|
jbe@4
|
2095 RETURN;
|
|
jbe@4
|
2096 END;
|
|
jbe@4
|
2097 $$;
|
|
jbe@4
|
2098
|
|
jbe@4
|
2099 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
|
|
jbe@4
|
2100
|
|
jbe@4
|
2101
|
|
jbe@4
|
2102
|
|
jbe@0
|
2103 ------------------------------
|
|
jbe@0
|
2104 -- Calculation of snapshots --
|
|
jbe@0
|
2105 ------------------------------
|
|
jbe@0
|
2106
|
|
jbe@0
|
2107 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
|
jbe@0
|
2108 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
2109 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
2110 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2111 RETURNS "direct_population_snapshot"."weight"%TYPE
|
|
jbe@0
|
2112 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2113 DECLARE
|
|
jbe@0
|
2114 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
2115 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
2116 "weight_v" INT4;
|
|
jbe@8
|
2117 "sub_weight_v" INT4;
|
|
jbe@0
|
2118 BEGIN
|
|
jbe@0
|
2119 "weight_v" := 0;
|
|
jbe@0
|
2120 FOR "issue_delegation_row" IN
|
|
jbe@0
|
2121 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
2122 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
2123 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
2124 LOOP
|
|
jbe@0
|
2125 IF NOT EXISTS (
|
|
jbe@0
|
2126 SELECT NULL FROM "direct_population_snapshot"
|
|
jbe@0
|
2127 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2128 AND "event" = 'periodic'
|
|
jbe@0
|
2129 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2130 ) AND NOT EXISTS (
|
|
jbe@0
|
2131 SELECT NULL FROM "delegating_population_snapshot"
|
|
jbe@0
|
2132 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2133 AND "event" = 'periodic'
|
|
jbe@0
|
2134 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2135 ) THEN
|
|
jbe@0
|
2136 "delegate_member_ids_v" :=
|
|
jbe@0
|
2137 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@10
|
2138 INSERT INTO "delegating_population_snapshot" (
|
|
jbe@10
|
2139 "issue_id",
|
|
jbe@10
|
2140 "event",
|
|
jbe@10
|
2141 "member_id",
|
|
jbe@10
|
2142 "scope",
|
|
jbe@10
|
2143 "delegate_member_ids"
|
|
jbe@10
|
2144 ) VALUES (
|
|
jbe@0
|
2145 "issue_id_p",
|
|
jbe@0
|
2146 'periodic',
|
|
jbe@0
|
2147 "issue_delegation_row"."truster_id",
|
|
jbe@10
|
2148 "issue_delegation_row"."scope",
|
|
jbe@0
|
2149 "delegate_member_ids_v"
|
|
jbe@0
|
2150 );
|
|
jbe@8
|
2151 "sub_weight_v" := 1 +
|
|
jbe@0
|
2152 "weight_of_added_delegations_for_population_snapshot"(
|
|
jbe@0
|
2153 "issue_id_p",
|
|
jbe@0
|
2154 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
2155 "delegate_member_ids_v"
|
|
jbe@0
|
2156 );
|
|
jbe@8
|
2157 UPDATE "delegating_population_snapshot"
|
|
jbe@8
|
2158 SET "weight" = "sub_weight_v"
|
|
jbe@8
|
2159 WHERE "issue_id" = "issue_id_p"
|
|
jbe@8
|
2160 AND "event" = 'periodic'
|
|
jbe@8
|
2161 AND "member_id" = "issue_delegation_row"."truster_id";
|
|
jbe@8
|
2162 "weight_v" := "weight_v" + "sub_weight_v";
|
|
jbe@0
|
2163 END IF;
|
|
jbe@0
|
2164 END LOOP;
|
|
jbe@0
|
2165 RETURN "weight_v";
|
|
jbe@0
|
2166 END;
|
|
jbe@0
|
2167 $$;
|
|
jbe@0
|
2168
|
|
jbe@0
|
2169 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
|
|
jbe@0
|
2170 ( "issue"."id"%TYPE,
|
|
jbe@0
|
2171 "member"."id"%TYPE,
|
|
jbe@0
|
2172 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2173 IS 'Helper function for "create_population_snapshot" function';
|
|
jbe@0
|
2174
|
|
jbe@0
|
2175
|
|
jbe@0
|
2176 CREATE FUNCTION "create_population_snapshot"
|
|
jbe@0
|
2177 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2178 RETURNS VOID
|
|
jbe@0
|
2179 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2180 DECLARE
|
|
jbe@0
|
2181 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
2182 BEGIN
|
|
jbe@0
|
2183 DELETE FROM "direct_population_snapshot"
|
|
jbe@0
|
2184 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2185 AND "event" = 'periodic';
|
|
jbe@0
|
2186 DELETE FROM "delegating_population_snapshot"
|
|
jbe@0
|
2187 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2188 AND "event" = 'periodic';
|
|
jbe@0
|
2189 INSERT INTO "direct_population_snapshot"
|
|
jbe@54
|
2190 ("issue_id", "event", "member_id")
|
|
jbe@54
|
2191 SELECT
|
|
jbe@54
|
2192 "issue_id_p" AS "issue_id",
|
|
jbe@54
|
2193 'periodic'::"snapshot_event" AS "event",
|
|
jbe@54
|
2194 "member"."id" AS "member_id"
|
|
jbe@54
|
2195 FROM "issue"
|
|
jbe@54
|
2196 JOIN "area" ON "issue"."area_id" = "area"."id"
|
|
jbe@54
|
2197 JOIN "membership" ON "area"."id" = "membership"."area_id"
|
|
jbe@54
|
2198 JOIN "member" ON "membership"."member_id" = "member"."id"
|
|
jbe@54
|
2199 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@54
|
2200 AND "member"."active"
|
|
jbe@54
|
2201 UNION
|
|
jbe@54
|
2202 SELECT
|
|
jbe@54
|
2203 "issue_id_p" AS "issue_id",
|
|
jbe@54
|
2204 'periodic'::"snapshot_event" AS "event",
|
|
jbe@54
|
2205 "member"."id" AS "member_id"
|
|
jbe@54
|
2206 FROM "interest" JOIN "member"
|
|
jbe@54
|
2207 ON "interest"."member_id" = "member"."id"
|
|
jbe@54
|
2208 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@54
|
2209 AND "member"."active";
|
|
jbe@0
|
2210 FOR "member_id_v" IN
|
|
jbe@0
|
2211 SELECT "member_id" FROM "direct_population_snapshot"
|
|
jbe@0
|
2212 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2213 AND "event" = 'periodic'
|
|
jbe@0
|
2214 LOOP
|
|
jbe@0
|
2215 UPDATE "direct_population_snapshot" SET
|
|
jbe@0
|
2216 "weight" = 1 +
|
|
jbe@0
|
2217 "weight_of_added_delegations_for_population_snapshot"(
|
|
jbe@0
|
2218 "issue_id_p",
|
|
jbe@0
|
2219 "member_id_v",
|
|
jbe@0
|
2220 '{}'
|
|
jbe@0
|
2221 )
|
|
jbe@0
|
2222 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2223 AND "event" = 'periodic'
|
|
jbe@0
|
2224 AND "member_id" = "member_id_v";
|
|
jbe@0
|
2225 END LOOP;
|
|
jbe@0
|
2226 RETURN;
|
|
jbe@0
|
2227 END;
|
|
jbe@0
|
2228 $$;
|
|
jbe@0
|
2229
|
|
jbe@0
|
2230 COMMENT ON FUNCTION "create_population_snapshot"
|
|
jbe@67
|
2231 ( "issue"."id"%TYPE )
|
|
jbe@0
|
2232 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
|
2233
|
|
jbe@0
|
2234
|
|
jbe@0
|
2235 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
|
jbe@0
|
2236 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
2237 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
2238 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2239 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
|
jbe@0
|
2240 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2241 DECLARE
|
|
jbe@0
|
2242 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
2243 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
2244 "weight_v" INT4;
|
|
jbe@8
|
2245 "sub_weight_v" INT4;
|
|
jbe@0
|
2246 BEGIN
|
|
jbe@0
|
2247 "weight_v" := 0;
|
|
jbe@0
|
2248 FOR "issue_delegation_row" IN
|
|
jbe@0
|
2249 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
2250 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
2251 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
2252 LOOP
|
|
jbe@0
|
2253 IF NOT EXISTS (
|
|
jbe@0
|
2254 SELECT NULL FROM "direct_interest_snapshot"
|
|
jbe@0
|
2255 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2256 AND "event" = 'periodic'
|
|
jbe@0
|
2257 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2258 ) AND NOT EXISTS (
|
|
jbe@0
|
2259 SELECT NULL FROM "delegating_interest_snapshot"
|
|
jbe@0
|
2260 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2261 AND "event" = 'periodic'
|
|
jbe@0
|
2262 AND "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2263 ) THEN
|
|
jbe@0
|
2264 "delegate_member_ids_v" :=
|
|
jbe@0
|
2265 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@10
|
2266 INSERT INTO "delegating_interest_snapshot" (
|
|
jbe@10
|
2267 "issue_id",
|
|
jbe@10
|
2268 "event",
|
|
jbe@10
|
2269 "member_id",
|
|
jbe@10
|
2270 "scope",
|
|
jbe@10
|
2271 "delegate_member_ids"
|
|
jbe@10
|
2272 ) VALUES (
|
|
jbe@0
|
2273 "issue_id_p",
|
|
jbe@0
|
2274 'periodic',
|
|
jbe@0
|
2275 "issue_delegation_row"."truster_id",
|
|
jbe@10
|
2276 "issue_delegation_row"."scope",
|
|
jbe@0
|
2277 "delegate_member_ids_v"
|
|
jbe@0
|
2278 );
|
|
jbe@8
|
2279 "sub_weight_v" := 1 +
|
|
jbe@0
|
2280 "weight_of_added_delegations_for_interest_snapshot"(
|
|
jbe@0
|
2281 "issue_id_p",
|
|
jbe@0
|
2282 "issue_delegation_row"."truster_id",
|
|
jbe@0
|
2283 "delegate_member_ids_v"
|
|
jbe@0
|
2284 );
|
|
jbe@8
|
2285 UPDATE "delegating_interest_snapshot"
|
|
jbe@8
|
2286 SET "weight" = "sub_weight_v"
|
|
jbe@8
|
2287 WHERE "issue_id" = "issue_id_p"
|
|
jbe@8
|
2288 AND "event" = 'periodic'
|
|
jbe@8
|
2289 AND "member_id" = "issue_delegation_row"."truster_id";
|
|
jbe@8
|
2290 "weight_v" := "weight_v" + "sub_weight_v";
|
|
jbe@0
|
2291 END IF;
|
|
jbe@0
|
2292 END LOOP;
|
|
jbe@0
|
2293 RETURN "weight_v";
|
|
jbe@0
|
2294 END;
|
|
jbe@0
|
2295 $$;
|
|
jbe@0
|
2296
|
|
jbe@0
|
2297 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
|
|
jbe@0
|
2298 ( "issue"."id"%TYPE,
|
|
jbe@0
|
2299 "member"."id"%TYPE,
|
|
jbe@0
|
2300 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2301 IS 'Helper function for "create_interest_snapshot" function';
|
|
jbe@0
|
2302
|
|
jbe@0
|
2303
|
|
jbe@0
|
2304 CREATE FUNCTION "create_interest_snapshot"
|
|
jbe@0
|
2305 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2306 RETURNS VOID
|
|
jbe@0
|
2307 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2308 DECLARE
|
|
jbe@0
|
2309 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
2310 BEGIN
|
|
jbe@0
|
2311 DELETE FROM "direct_interest_snapshot"
|
|
jbe@0
|
2312 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2313 AND "event" = 'periodic';
|
|
jbe@0
|
2314 DELETE FROM "delegating_interest_snapshot"
|
|
jbe@0
|
2315 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2316 AND "event" = 'periodic';
|
|
jbe@0
|
2317 DELETE FROM "direct_supporter_snapshot"
|
|
jbe@0
|
2318 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2319 AND "event" = 'periodic';
|
|
jbe@0
|
2320 INSERT INTO "direct_interest_snapshot"
|
|
jbe@0
|
2321 ("issue_id", "event", "member_id", "voting_requested")
|
|
jbe@0
|
2322 SELECT
|
|
jbe@0
|
2323 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
2324 'periodic' AS "event",
|
|
jbe@0
|
2325 "member"."id" AS "member_id",
|
|
jbe@0
|
2326 "interest"."voting_requested"
|
|
jbe@0
|
2327 FROM "interest" JOIN "member"
|
|
jbe@0
|
2328 ON "interest"."member_id" = "member"."id"
|
|
jbe@0
|
2329 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2330 AND "member"."active";
|
|
jbe@0
|
2331 FOR "member_id_v" IN
|
|
jbe@0
|
2332 SELECT "member_id" FROM "direct_interest_snapshot"
|
|
jbe@0
|
2333 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2334 AND "event" = 'periodic'
|
|
jbe@0
|
2335 LOOP
|
|
jbe@0
|
2336 UPDATE "direct_interest_snapshot" SET
|
|
jbe@0
|
2337 "weight" = 1 +
|
|
jbe@0
|
2338 "weight_of_added_delegations_for_interest_snapshot"(
|
|
jbe@0
|
2339 "issue_id_p",
|
|
jbe@0
|
2340 "member_id_v",
|
|
jbe@0
|
2341 '{}'
|
|
jbe@0
|
2342 )
|
|
jbe@0
|
2343 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2344 AND "event" = 'periodic'
|
|
jbe@0
|
2345 AND "member_id" = "member_id_v";
|
|
jbe@0
|
2346 END LOOP;
|
|
jbe@0
|
2347 INSERT INTO "direct_supporter_snapshot"
|
|
jbe@0
|
2348 ( "issue_id", "initiative_id", "event", "member_id",
|
|
jbe@0
|
2349 "informed", "satisfied" )
|
|
jbe@0
|
2350 SELECT
|
|
jbe@0
|
2351 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
2352 "initiative"."id" AS "initiative_id",
|
|
jbe@0
|
2353 'periodic' AS "event",
|
|
jbe@0
|
2354 "member"."id" AS "member_id",
|
|
jbe@0
|
2355 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
|
jbe@0
|
2356 NOT EXISTS (
|
|
jbe@0
|
2357 SELECT NULL FROM "critical_opinion"
|
|
jbe@0
|
2358 WHERE "initiative_id" = "initiative"."id"
|
|
jbe@0
|
2359 AND "member_id" = "member"."id"
|
|
jbe@0
|
2360 ) AS "satisfied"
|
|
jbe@0
|
2361 FROM "supporter"
|
|
jbe@0
|
2362 JOIN "member"
|
|
jbe@0
|
2363 ON "supporter"."member_id" = "member"."id"
|
|
jbe@0
|
2364 JOIN "initiative"
|
|
jbe@0
|
2365 ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@0
|
2366 JOIN "current_draft"
|
|
jbe@0
|
2367 ON "initiative"."id" = "current_draft"."initiative_id"
|
|
jbe@0
|
2368 JOIN "direct_interest_snapshot"
|
|
jbe@0
|
2369 ON "member"."id" = "direct_interest_snapshot"."member_id"
|
|
jbe@0
|
2370 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
|
jbe@3
|
2371 AND "event" = 'periodic'
|
|
jbe@0
|
2372 WHERE "member"."active"
|
|
jbe@0
|
2373 AND "initiative"."issue_id" = "issue_id_p";
|
|
jbe@0
|
2374 RETURN;
|
|
jbe@0
|
2375 END;
|
|
jbe@0
|
2376 $$;
|
|
jbe@0
|
2377
|
|
jbe@0
|
2378 COMMENT ON FUNCTION "create_interest_snapshot"
|
|
jbe@0
|
2379 ( "issue"."id"%TYPE )
|
|
jbe@0
|
2380 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
|
2381
|
|
jbe@0
|
2382
|
|
jbe@0
|
2383 CREATE FUNCTION "create_snapshot"
|
|
jbe@0
|
2384 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2385 RETURNS VOID
|
|
jbe@0
|
2386 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2387 DECLARE
|
|
jbe@0
|
2388 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@0
|
2389 "suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@0
|
2390 BEGIN
|
|
jbe@67
|
2391 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@0
|
2392 PERFORM "create_population_snapshot"("issue_id_p");
|
|
jbe@0
|
2393 PERFORM "create_interest_snapshot"("issue_id_p");
|
|
jbe@0
|
2394 UPDATE "issue" SET
|
|
jbe@8
|
2395 "snapshot" = now(),
|
|
jbe@8
|
2396 "latest_snapshot_event" = 'periodic',
|
|
jbe@0
|
2397 "population" = (
|
|
jbe@0
|
2398 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
2399 FROM "direct_population_snapshot"
|
|
jbe@0
|
2400 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2401 AND "event" = 'periodic'
|
|
jbe@0
|
2402 ),
|
|
jbe@8
|
2403 "vote_now" = (
|
|
jbe@0
|
2404 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
2405 FROM "direct_interest_snapshot"
|
|
jbe@0
|
2406 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2407 AND "event" = 'periodic'
|
|
jbe@0
|
2408 AND "voting_requested" = TRUE
|
|
jbe@0
|
2409 ),
|
|
jbe@0
|
2410 "vote_later" = (
|
|
jbe@0
|
2411 SELECT coalesce(sum("weight"), 0)
|
|
jbe@0
|
2412 FROM "direct_interest_snapshot"
|
|
jbe@0
|
2413 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2414 AND "event" = 'periodic'
|
|
jbe@0
|
2415 AND "voting_requested" = FALSE
|
|
jbe@0
|
2416 )
|
|
jbe@0
|
2417 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
2418 FOR "initiative_id_v" IN
|
|
jbe@0
|
2419 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2420 LOOP
|
|
jbe@0
|
2421 UPDATE "initiative" SET
|
|
jbe@0
|
2422 "supporter_count" = (
|
|
jbe@0
|
2423 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
2424 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
2425 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
2426 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
2427 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2428 AND "di"."event" = 'periodic'
|
|
jbe@0
|
2429 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
2430 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
2431 ),
|
|
jbe@0
|
2432 "informed_supporter_count" = (
|
|
jbe@0
|
2433 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
2434 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
2435 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
2436 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
2437 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2438 AND "di"."event" = 'periodic'
|
|
jbe@0
|
2439 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
2440 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
2441 AND "ds"."informed"
|
|
jbe@0
|
2442 ),
|
|
jbe@0
|
2443 "satisfied_supporter_count" = (
|
|
jbe@0
|
2444 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
2445 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
2446 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
2447 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
2448 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2449 AND "di"."event" = 'periodic'
|
|
jbe@0
|
2450 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
2451 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
2452 AND "ds"."satisfied"
|
|
jbe@0
|
2453 ),
|
|
jbe@0
|
2454 "satisfied_informed_supporter_count" = (
|
|
jbe@0
|
2455 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@0
|
2456 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@0
|
2457 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@0
|
2458 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@0
|
2459 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2460 AND "di"."event" = 'periodic'
|
|
jbe@0
|
2461 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@0
|
2462 AND "ds"."event" = 'periodic'
|
|
jbe@0
|
2463 AND "ds"."informed"
|
|
jbe@0
|
2464 AND "ds"."satisfied"
|
|
jbe@0
|
2465 )
|
|
jbe@0
|
2466 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
2467 FOR "suggestion_id_v" IN
|
|
jbe@0
|
2468 SELECT "id" FROM "suggestion"
|
|
jbe@0
|
2469 WHERE "initiative_id" = "initiative_id_v"
|
|
jbe@0
|
2470 LOOP
|
|
jbe@0
|
2471 UPDATE "suggestion" SET
|
|
jbe@0
|
2472 "minus2_unfulfilled_count" = (
|
|
jbe@0
|
2473 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2474 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2475 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2476 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2477 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2478 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2479 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2480 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2481 AND "opinion"."degree" = -2
|
|
jbe@0
|
2482 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
2483 ),
|
|
jbe@0
|
2484 "minus2_fulfilled_count" = (
|
|
jbe@0
|
2485 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2486 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2487 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2488 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2489 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2490 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2491 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2492 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2493 AND "opinion"."degree" = -2
|
|
jbe@0
|
2494 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
2495 ),
|
|
jbe@0
|
2496 "minus1_unfulfilled_count" = (
|
|
jbe@0
|
2497 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2498 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2499 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2500 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2501 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2502 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2503 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2504 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2505 AND "opinion"."degree" = -1
|
|
jbe@0
|
2506 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
2507 ),
|
|
jbe@0
|
2508 "minus1_fulfilled_count" = (
|
|
jbe@0
|
2509 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2510 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2511 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2512 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2513 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2514 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2515 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2516 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2517 AND "opinion"."degree" = -1
|
|
jbe@0
|
2518 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
2519 ),
|
|
jbe@0
|
2520 "plus1_unfulfilled_count" = (
|
|
jbe@0
|
2521 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2522 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2523 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2524 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2525 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2526 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2527 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2528 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2529 AND "opinion"."degree" = 1
|
|
jbe@0
|
2530 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
2531 ),
|
|
jbe@0
|
2532 "plus1_fulfilled_count" = (
|
|
jbe@0
|
2533 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2534 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2535 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2536 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2537 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2538 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2539 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2540 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2541 AND "opinion"."degree" = 1
|
|
jbe@0
|
2542 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
2543 ),
|
|
jbe@0
|
2544 "plus2_unfulfilled_count" = (
|
|
jbe@0
|
2545 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2546 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2547 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2548 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2549 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2550 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2551 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2552 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2553 AND "opinion"."degree" = 2
|
|
jbe@0
|
2554 AND "opinion"."fulfilled" = FALSE
|
|
jbe@0
|
2555 ),
|
|
jbe@0
|
2556 "plus2_fulfilled_count" = (
|
|
jbe@0
|
2557 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
2558 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
2559 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
2560 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
2561 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
2562 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
2563 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
2564 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@0
|
2565 AND "opinion"."degree" = 2
|
|
jbe@0
|
2566 AND "opinion"."fulfilled" = TRUE
|
|
jbe@0
|
2567 )
|
|
jbe@0
|
2568 WHERE "suggestion"."id" = "suggestion_id_v";
|
|
jbe@0
|
2569 END LOOP;
|
|
jbe@0
|
2570 END LOOP;
|
|
jbe@0
|
2571 RETURN;
|
|
jbe@0
|
2572 END;
|
|
jbe@0
|
2573 $$;
|
|
jbe@0
|
2574
|
|
jbe@0
|
2575 COMMENT ON FUNCTION "create_snapshot"
|
|
jbe@0
|
2576 ( "issue"."id"%TYPE )
|
|
jbe@0
|
2577 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
|
2578
|
|
jbe@0
|
2579
|
|
jbe@0
|
2580 CREATE FUNCTION "set_snapshot_event"
|
|
jbe@0
|
2581 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
2582 "event_p" "snapshot_event" )
|
|
jbe@0
|
2583 RETURNS VOID
|
|
jbe@0
|
2584 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@21
|
2585 DECLARE
|
|
jbe@21
|
2586 "event_v" "issue"."latest_snapshot_event"%TYPE;
|
|
jbe@0
|
2587 BEGIN
|
|
jbe@21
|
2588 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
|
|
jbe@21
|
2589 WHERE "id" = "issue_id_p" FOR UPDATE;
|
|
jbe@8
|
2590 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
|
|
jbe@8
|
2591 WHERE "id" = "issue_id_p";
|
|
jbe@3
|
2592 UPDATE "direct_population_snapshot" SET "event" = "event_p"
|
|
jbe@21
|
2593 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
|
jbe@3
|
2594 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
|
|
jbe@21
|
2595 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
|
jbe@3
|
2596 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
|
|
jbe@21
|
2597 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
|
jbe@3
|
2598 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
|
|
jbe@21
|
2599 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
|
jbe@3
|
2600 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
|
|
jbe@21
|
2601 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
|
|
jbe@0
|
2602 RETURN;
|
|
jbe@0
|
2603 END;
|
|
jbe@0
|
2604 $$;
|
|
jbe@0
|
2605
|
|
jbe@0
|
2606 COMMENT ON FUNCTION "set_snapshot_event"
|
|
jbe@0
|
2607 ( "issue"."id"%TYPE,
|
|
jbe@0
|
2608 "snapshot_event" )
|
|
jbe@0
|
2609 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
|
|
jbe@0
|
2610
|
|
jbe@0
|
2611
|
|
jbe@0
|
2612
|
|
jbe@0
|
2613 ---------------------
|
|
jbe@0
|
2614 -- Freezing issues --
|
|
jbe@0
|
2615 ---------------------
|
|
jbe@0
|
2616
|
|
jbe@0
|
2617 CREATE FUNCTION "freeze_after_snapshot"
|
|
jbe@0
|
2618 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2619 RETURNS VOID
|
|
jbe@0
|
2620 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2621 DECLARE
|
|
jbe@0
|
2622 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
2623 "policy_row" "policy"%ROWTYPE;
|
|
jbe@0
|
2624 "initiative_row" "initiative"%ROWTYPE;
|
|
jbe@0
|
2625 BEGIN
|
|
jbe@0
|
2626 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
2627 SELECT * INTO "policy_row"
|
|
jbe@0
|
2628 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
|
jbe@21
|
2629 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
|
|
jbe@3
|
2630 UPDATE "issue" SET
|
|
jbe@4
|
2631 "accepted" = coalesce("accepted", now()),
|
|
jbe@4
|
2632 "half_frozen" = coalesce("half_frozen", now()),
|
|
jbe@3
|
2633 "fully_frozen" = now()
|
|
jbe@3
|
2634 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
2635 FOR "initiative_row" IN
|
|
jbe@15
|
2636 SELECT * FROM "initiative"
|
|
jbe@15
|
2637 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
|
jbe@0
|
2638 LOOP
|
|
jbe@0
|
2639 IF
|
|
jbe@0
|
2640 "initiative_row"."satisfied_supporter_count" > 0 AND
|
|
jbe@0
|
2641 "initiative_row"."satisfied_supporter_count" *
|
|
jbe@0
|
2642 "policy_row"."initiative_quorum_den" >=
|
|
jbe@0
|
2643 "issue_row"."population" * "policy_row"."initiative_quorum_num"
|
|
jbe@0
|
2644 THEN
|
|
jbe@0
|
2645 UPDATE "initiative" SET "admitted" = TRUE
|
|
jbe@0
|
2646 WHERE "id" = "initiative_row"."id";
|
|
jbe@0
|
2647 ELSE
|
|
jbe@0
|
2648 UPDATE "initiative" SET "admitted" = FALSE
|
|
jbe@0
|
2649 WHERE "id" = "initiative_row"."id";
|
|
jbe@0
|
2650 END IF;
|
|
jbe@0
|
2651 END LOOP;
|
|
jbe@9
|
2652 IF NOT EXISTS (
|
|
jbe@9
|
2653 SELECT NULL FROM "initiative"
|
|
jbe@9
|
2654 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
|
|
jbe@9
|
2655 ) THEN
|
|
jbe@9
|
2656 PERFORM "close_voting"("issue_id_p");
|
|
jbe@9
|
2657 END IF;
|
|
jbe@0
|
2658 RETURN;
|
|
jbe@0
|
2659 END;
|
|
jbe@0
|
2660 $$;
|
|
jbe@0
|
2661
|
|
jbe@0
|
2662 COMMENT ON FUNCTION "freeze_after_snapshot"
|
|
jbe@0
|
2663 ( "issue"."id"%TYPE )
|
|
jbe@9
|
2664 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
|
|
jbe@0
|
2665
|
|
jbe@0
|
2666
|
|
jbe@0
|
2667 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
2668 RETURNS VOID
|
|
jbe@0
|
2669 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2670 DECLARE
|
|
jbe@0
|
2671 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
2672 BEGIN
|
|
jbe@0
|
2673 PERFORM "create_snapshot"("issue_id_p");
|
|
jbe@0
|
2674 PERFORM "freeze_after_snapshot"("issue_id_p");
|
|
jbe@0
|
2675 RETURN;
|
|
jbe@0
|
2676 END;
|
|
jbe@0
|
2677 $$;
|
|
jbe@0
|
2678
|
|
jbe@55
|
2679 COMMENT ON FUNCTION "manual_freeze"
|
|
jbe@0
|
2680 ( "issue"."id"%TYPE )
|
|
jbe@3
|
2681 IS 'Freeze an issue manually (fully) and start voting';
|
|
jbe@0
|
2682
|
|
jbe@0
|
2683
|
|
jbe@0
|
2684
|
|
jbe@0
|
2685 -----------------------
|
|
jbe@0
|
2686 -- Counting of votes --
|
|
jbe@0
|
2687 -----------------------
|
|
jbe@0
|
2688
|
|
jbe@0
|
2689
|
|
jbe@5
|
2690 CREATE FUNCTION "weight_of_added_vote_delegations"
|
|
jbe@0
|
2691 ( "issue_id_p" "issue"."id"%TYPE,
|
|
jbe@0
|
2692 "member_id_p" "member"."id"%TYPE,
|
|
jbe@0
|
2693 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2694 RETURNS "direct_voter"."weight"%TYPE
|
|
jbe@0
|
2695 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2696 DECLARE
|
|
jbe@0
|
2697 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
|
jbe@0
|
2698 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
|
|
jbe@0
|
2699 "weight_v" INT4;
|
|
jbe@8
|
2700 "sub_weight_v" INT4;
|
|
jbe@0
|
2701 BEGIN
|
|
jbe@0
|
2702 "weight_v" := 0;
|
|
jbe@0
|
2703 FOR "issue_delegation_row" IN
|
|
jbe@0
|
2704 SELECT * FROM "issue_delegation"
|
|
jbe@0
|
2705 WHERE "trustee_id" = "member_id_p"
|
|
jbe@0
|
2706 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
2707 LOOP
|
|
jbe@0
|
2708 IF NOT EXISTS (
|
|
jbe@0
|
2709 SELECT NULL FROM "direct_voter"
|
|
jbe@0
|
2710 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2711 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
2712 ) AND NOT EXISTS (
|
|
jbe@0
|
2713 SELECT NULL FROM "delegating_voter"
|
|
jbe@0
|
2714 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
|
jbe@0
|
2715 AND "issue_id" = "issue_id_p"
|
|
jbe@0
|
2716 ) THEN
|
|
jbe@0
|
2717 "delegate_member_ids_v" :=
|
|
jbe@0
|
2718 "member_id_p" || "delegate_member_ids_p";
|
|
jbe@10
|
2719 INSERT INTO "delegating_voter" (
|
|
jbe@10
|
2720 "issue_id",
|
|
jbe@10
|
2721 "member_id",
|
|
jbe@10
|
2722 "scope",
|
|
jbe@10
|
2723 "delegate_member_ids"
|
|
jbe@10
|
2724 ) VALUES (
|
|
jbe@5
|
2725 "issue_id_p",
|
|
jbe@5
|
2726 "issue_delegation_row"."truster_id",
|
|
jbe@10
|
2727 "issue_delegation_row"."scope",
|
|
jbe@5
|
2728 "delegate_member_ids_v"
|
|
jbe@5
|
2729 );
|
|
jbe@8
|
2730 "sub_weight_v" := 1 +
|
|
jbe@8
|
2731 "weight_of_added_vote_delegations"(
|
|
jbe@8
|
2732 "issue_id_p",
|
|
jbe@8
|
2733 "issue_delegation_row"."truster_id",
|
|
jbe@8
|
2734 "delegate_member_ids_v"
|
|
jbe@8
|
2735 );
|
|
jbe@8
|
2736 UPDATE "delegating_voter"
|
|
jbe@8
|
2737 SET "weight" = "sub_weight_v"
|
|
jbe@8
|
2738 WHERE "issue_id" = "issue_id_p"
|
|
jbe@8
|
2739 AND "member_id" = "issue_delegation_row"."truster_id";
|
|
jbe@8
|
2740 "weight_v" := "weight_v" + "sub_weight_v";
|
|
jbe@0
|
2741 END IF;
|
|
jbe@0
|
2742 END LOOP;
|
|
jbe@0
|
2743 RETURN "weight_v";
|
|
jbe@0
|
2744 END;
|
|
jbe@0
|
2745 $$;
|
|
jbe@0
|
2746
|
|
jbe@5
|
2747 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
|
|
jbe@0
|
2748 ( "issue"."id"%TYPE,
|
|
jbe@0
|
2749 "member"."id"%TYPE,
|
|
jbe@0
|
2750 "delegating_voter"."delegate_member_ids"%TYPE )
|
|
jbe@0
|
2751 IS 'Helper function for "add_vote_delegations" function';
|
|
jbe@0
|
2752
|
|
jbe@0
|
2753
|
|
jbe@0
|
2754 CREATE FUNCTION "add_vote_delegations"
|
|
jbe@0
|
2755 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2756 RETURNS VOID
|
|
jbe@0
|
2757 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2758 DECLARE
|
|
jbe@0
|
2759 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
2760 BEGIN
|
|
jbe@0
|
2761 FOR "member_id_v" IN
|
|
jbe@0
|
2762 SELECT "member_id" FROM "direct_voter"
|
|
jbe@0
|
2763 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2764 LOOP
|
|
jbe@0
|
2765 UPDATE "direct_voter" SET
|
|
jbe@5
|
2766 "weight" = "weight" + "weight_of_added_vote_delegations"(
|
|
jbe@0
|
2767 "issue_id_p",
|
|
jbe@0
|
2768 "member_id_v",
|
|
jbe@0
|
2769 '{}'
|
|
jbe@0
|
2770 )
|
|
jbe@0
|
2771 WHERE "member_id" = "member_id_v"
|
|
jbe@0
|
2772 AND "issue_id" = "issue_id_p";
|
|
jbe@0
|
2773 END LOOP;
|
|
jbe@0
|
2774 RETURN;
|
|
jbe@0
|
2775 END;
|
|
jbe@0
|
2776 $$;
|
|
jbe@0
|
2777
|
|
jbe@0
|
2778 COMMENT ON FUNCTION "add_vote_delegations"
|
|
jbe@0
|
2779 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
2780 IS 'Helper function for "close_voting" function';
|
|
jbe@0
|
2781
|
|
jbe@0
|
2782
|
|
jbe@0
|
2783 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
2784 RETURNS VOID
|
|
jbe@0
|
2785 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
2786 DECLARE
|
|
jbe@0
|
2787 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
2788 "member_id_v" "member"."id"%TYPE;
|
|
jbe@0
|
2789 BEGIN
|
|
jbe@67
|
2790 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@0
|
2791 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
2792 DELETE FROM "delegating_voter"
|
|
jbe@0
|
2793 WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
2794 DELETE FROM "direct_voter"
|
|
jbe@0
|
2795 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
2796 AND "autoreject" = TRUE;
|
|
jbe@0
|
2797 DELETE FROM "direct_voter" USING "member"
|
|
jbe@0
|
2798 WHERE "direct_voter"."member_id" = "member"."id"
|
|
jbe@0
|
2799 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2800 AND "member"."active" = FALSE;
|
|
jbe@0
|
2801 UPDATE "direct_voter" SET "weight" = 1
|
|
jbe@0
|
2802 WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
2803 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@0
|
2804 FOR "member_id_v" IN
|
|
jbe@0
|
2805 SELECT "interest"."member_id"
|
|
jbe@0
|
2806 FROM "interest"
|
|
jbe@0
|
2807 LEFT JOIN "direct_voter"
|
|
jbe@0
|
2808 ON "interest"."member_id" = "direct_voter"."member_id"
|
|
jbe@0
|
2809 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
|
jbe@0
|
2810 LEFT JOIN "delegating_voter"
|
|
jbe@0
|
2811 ON "interest"."member_id" = "delegating_voter"."member_id"
|
|
jbe@0
|
2812 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
|
jbe@0
|
2813 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2814 AND "interest"."autoreject" = TRUE
|
|
jbe@0
|
2815 AND "direct_voter"."member_id" ISNULL
|
|
jbe@0
|
2816 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@0
|
2817 UNION SELECT "membership"."member_id"
|
|
jbe@0
|
2818 FROM "membership"
|
|
jbe@0
|
2819 LEFT JOIN "interest"
|
|
jbe@0
|
2820 ON "membership"."member_id" = "interest"."member_id"
|
|
jbe@0
|
2821 AND "interest"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2822 LEFT JOIN "direct_voter"
|
|
jbe@0
|
2823 ON "membership"."member_id" = "direct_voter"."member_id"
|
|
jbe@0
|
2824 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2825 LEFT JOIN "delegating_voter"
|
|
jbe@0
|
2826 ON "membership"."member_id" = "delegating_voter"."member_id"
|
|
jbe@0
|
2827 AND "delegating_voter"."issue_id" = "issue_id_p"
|
|
jbe@0
|
2828 WHERE "membership"."area_id" = "issue_row"."area_id"
|
|
jbe@0
|
2829 AND "membership"."autoreject" = TRUE
|
|
jbe@0
|
2830 AND "interest"."autoreject" ISNULL
|
|
jbe@0
|
2831 AND "direct_voter"."member_id" ISNULL
|
|
jbe@0
|
2832 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@0
|
2833 LOOP
|
|
jbe@11
|
2834 INSERT INTO "direct_voter"
|
|
jbe@11
|
2835 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
|
jbe@11
|
2836 ("member_id_v", "issue_id_p", 1, TRUE);
|
|
jbe@0
|
2837 INSERT INTO "vote" (
|
|
jbe@0
|
2838 "member_id",
|
|
jbe@0
|
2839 "issue_id",
|
|
jbe@0
|
2840 "initiative_id",
|
|
jbe@0
|
2841 "grade"
|
|
jbe@0
|
2842 ) SELECT
|
|
jbe@0
|
2843 "member_id_v" AS "member_id",
|
|
jbe@0
|
2844 "issue_id_p" AS "issue_id",
|
|
jbe@0
|
2845 "id" AS "initiative_id",
|
|
jbe@0
|
2846 -1 AS "grade"
|
|
jbe@0
|
2847 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
2848 END LOOP;
|
|
jbe@0
|
2849 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@4
|
2850 UPDATE "issue" SET
|
|
jbe@61
|
2851 "closed" = now(),
|
|
jbe@4
|
2852 "voter_count" = (
|
|
jbe@4
|
2853 SELECT coalesce(sum("weight"), 0)
|
|
jbe@4
|
2854 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
|
jbe@6
|
2855 )
|
|
jbe@6
|
2856 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
2857 UPDATE "initiative" SET
|
|
jbe@10
|
2858 "positive_votes" = "vote_counts"."positive_votes",
|
|
jbe@10
|
2859 "negative_votes" = "vote_counts"."negative_votes",
|
|
jbe@10
|
2860 "agreed" = CASE WHEN "majority_strict" THEN
|
|
jbe@10
|
2861 "vote_counts"."positive_votes" * "majority_den" >
|
|
jbe@10
|
2862 "majority_num" *
|
|
jbe@10
|
2863 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
|
jbe@10
|
2864 ELSE
|
|
jbe@10
|
2865 "vote_counts"."positive_votes" * "majority_den" >=
|
|
jbe@10
|
2866 "majority_num" *
|
|
jbe@10
|
2867 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
|
jbe@10
|
2868 END
|
|
jbe@10
|
2869 FROM
|
|
jbe@10
|
2870 ( SELECT
|
|
jbe@10
|
2871 "initiative"."id" AS "initiative_id",
|
|
jbe@10
|
2872 coalesce(
|
|
jbe@10
|
2873 sum(
|
|
jbe@10
|
2874 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@10
|
2875 ),
|
|
jbe@10
|
2876 0
|
|
jbe@10
|
2877 ) AS "positive_votes",
|
|
jbe@10
|
2878 coalesce(
|
|
jbe@10
|
2879 sum(
|
|
jbe@10
|
2880 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@10
|
2881 ),
|
|
jbe@10
|
2882 0
|
|
jbe@10
|
2883 ) AS "negative_votes"
|
|
jbe@10
|
2884 FROM "initiative"
|
|
jbe@10
|
2885 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
|
jbe@10
|
2886 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
|
jbe@10
|
2887 LEFT JOIN "direct_voter"
|
|
jbe@10
|
2888 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
|
jbe@10
|
2889 LEFT JOIN "vote"
|
|
jbe@10
|
2890 ON "vote"."initiative_id" = "initiative"."id"
|
|
jbe@10
|
2891 AND "vote"."member_id" = "direct_voter"."member_id"
|
|
jbe@10
|
2892 WHERE "initiative"."issue_id" = "issue_id_p"
|
|
jbe@15
|
2893 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
|
jbe@10
|
2894 GROUP BY "initiative"."id"
|
|
jbe@10
|
2895 ) AS "vote_counts",
|
|
jbe@10
|
2896 "issue",
|
|
jbe@10
|
2897 "policy"
|
|
jbe@10
|
2898 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
|
jbe@10
|
2899 AND "issue"."id" = "initiative"."issue_id"
|
|
jbe@10
|
2900 AND "policy"."id" = "issue"."policy_id";
|
|
jbe@61
|
2901 -- NOTE: "closed" column of issue must be set at this point
|
|
jbe@61
|
2902 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
|
jbe@61
|
2903 INSERT INTO "battle" (
|
|
jbe@61
|
2904 "issue_id",
|
|
jbe@61
|
2905 "winning_initiative_id", "losing_initiative_id",
|
|
jbe@61
|
2906 "count"
|
|
jbe@61
|
2907 ) SELECT
|
|
jbe@61
|
2908 "issue_id",
|
|
jbe@61
|
2909 "winning_initiative_id", "losing_initiative_id",
|
|
jbe@61
|
2910 "count"
|
|
jbe@61
|
2911 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
|
jbe@0
|
2912 END;
|
|
jbe@0
|
2913 $$;
|
|
jbe@0
|
2914
|
|
jbe@0
|
2915 COMMENT ON FUNCTION "close_voting"
|
|
jbe@0
|
2916 ( "issue"."id"%TYPE )
|
|
jbe@0
|
2917 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
|
2918
|
|
jbe@0
|
2919
|
|
jbe@30
|
2920 CREATE FUNCTION "defeat_strength"
|
|
jbe@30
|
2921 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
|
|
jbe@30
|
2922 RETURNS INT8
|
|
jbe@30
|
2923 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@30
|
2924 BEGIN
|
|
jbe@30
|
2925 IF "positive_votes_p" > "negative_votes_p" THEN
|
|
jbe@30
|
2926 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
|
|
jbe@30
|
2927 ELSIF "positive_votes_p" = "negative_votes_p" THEN
|
|
jbe@30
|
2928 RETURN 0;
|
|
jbe@30
|
2929 ELSE
|
|
jbe@30
|
2930 RETURN -1;
|
|
jbe@30
|
2931 END IF;
|
|
jbe@30
|
2932 END;
|
|
jbe@30
|
2933 $$;
|
|
jbe@30
|
2934
|
|
jbe@30
|
2935 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
|
|
jbe@30
|
2936
|
|
jbe@30
|
2937
|
|
jbe@30
|
2938 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
|
|
jbe@30
|
2939 RETURNS TEXT
|
|
jbe@0
|
2940 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@0
|
2941 DECLARE
|
|
jbe@0
|
2942 "i" INTEGER;
|
|
jbe@0
|
2943 "ary_text_v" TEXT;
|
|
jbe@0
|
2944 BEGIN
|
|
jbe@0
|
2945 IF "dim_p" >= 1 THEN
|
|
jbe@0
|
2946 "ary_text_v" := '{NULL';
|
|
jbe@0
|
2947 "i" := "dim_p";
|
|
jbe@0
|
2948 LOOP
|
|
jbe@0
|
2949 "i" := "i" - 1;
|
|
jbe@0
|
2950 EXIT WHEN "i" = 0;
|
|
jbe@0
|
2951 "ary_text_v" := "ary_text_v" || ',NULL';
|
|
jbe@0
|
2952 END LOOP;
|
|
jbe@0
|
2953 "ary_text_v" := "ary_text_v" || '}';
|
|
jbe@30
|
2954 RETURN "ary_text_v";
|
|
jbe@0
|
2955 ELSE
|
|
jbe@0
|
2956 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
|
jbe@0
|
2957 END IF;
|
|
jbe@0
|
2958 END;
|
|
jbe@0
|
2959 $$;
|
|
jbe@0
|
2960
|
|
jbe@30
|
2961 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
|
jbe@30
|
2962
|
|
jbe@30
|
2963
|
|
jbe@30
|
2964 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
|
|
jbe@30
|
2965 RETURNS TEXT
|
|
jbe@0
|
2966 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
|
jbe@0
|
2967 DECLARE
|
|
jbe@0
|
2968 "i" INTEGER;
|
|
jbe@0
|
2969 "row_text_v" TEXT;
|
|
jbe@0
|
2970 "ary_text_v" TEXT;
|
|
jbe@0
|
2971 BEGIN
|
|
jbe@0
|
2972 IF "dim_p" >= 1 THEN
|
|
jbe@0
|
2973 "row_text_v" := '{NULL';
|
|
jbe@0
|
2974 "i" := "dim_p";
|
|
jbe@0
|
2975 LOOP
|
|
jbe@0
|
2976 "i" := "i" - 1;
|
|
jbe@0
|
2977 EXIT WHEN "i" = 0;
|
|
jbe@0
|
2978 "row_text_v" := "row_text_v" || ',NULL';
|
|
jbe@0
|
2979 END LOOP;
|
|
jbe@0
|
2980 "row_text_v" := "row_text_v" || '}';
|
|
jbe@0
|
2981 "ary_text_v" := '{' || "row_text_v";
|
|
jbe@0
|
2982 "i" := "dim_p";
|
|
jbe@0
|
2983 LOOP
|
|
jbe@0
|
2984 "i" := "i" - 1;
|
|
jbe@0
|
2985 EXIT WHEN "i" = 0;
|
|
jbe@0
|
2986 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
|
|
jbe@0
|
2987 END LOOP;
|
|
jbe@0
|
2988 "ary_text_v" := "ary_text_v" || '}';
|
|
jbe@30
|
2989 RETURN "ary_text_v";
|
|
jbe@0
|
2990 ELSE
|
|
jbe@0
|
2991 RAISE EXCEPTION 'Dimension needs to be at least 1.';
|
|
jbe@0
|
2992 END IF;
|
|
jbe@0
|
2993 END;
|
|
jbe@0
|
2994 $$;
|
|
jbe@0
|
2995
|
|
jbe@30
|
2996 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
|
|
jbe@0
|
2997
|
|
jbe@0
|
2998
|
|
jbe@0
|
2999 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@0
|
3000 RETURNS VOID
|
|
jbe@0
|
3001 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
3002 DECLARE
|
|
jbe@0
|
3003 "dimension_v" INTEGER;
|
|
jbe@30
|
3004 "vote_matrix" INT4[][]; -- absolute votes
|
|
jbe@30
|
3005 "matrix" INT8[][]; -- defeat strength / best paths
|
|
jbe@0
|
3006 "i" INTEGER;
|
|
jbe@0
|
3007 "j" INTEGER;
|
|
jbe@0
|
3008 "k" INTEGER;
|
|
jbe@0
|
3009 "battle_row" "battle"%ROWTYPE;
|
|
jbe@0
|
3010 "rank_ary" INT4[];
|
|
jbe@0
|
3011 "rank_v" INT4;
|
|
jbe@0
|
3012 "done_v" INTEGER;
|
|
jbe@0
|
3013 "winners_ary" INTEGER[];
|
|
jbe@0
|
3014 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@0
|
3015 BEGIN
|
|
jbe@0
|
3016 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
|
jbe@10
|
3017 SELECT count(1) INTO "dimension_v" FROM "initiative"
|
|
jbe@10
|
3018 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
|
jbe@0
|
3019 IF "dimension_v" = 1 THEN
|
|
jbe@10
|
3020 UPDATE "initiative" SET "rank" = 1
|
|
jbe@10
|
3021 WHERE "issue_id" = "issue_id_p" AND "agreed";
|
|
jbe@0
|
3022 ELSIF "dimension_v" > 1 THEN
|
|
jbe@30
|
3023 -- Create "vote_matrix" with absolute number of votes in pairwise
|
|
jbe@30
|
3024 -- comparison:
|
|
jbe@30
|
3025 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
|
jbe@0
|
3026 "i" := 1;
|
|
jbe@0
|
3027 "j" := 2;
|
|
jbe@0
|
3028 FOR "battle_row" IN
|
|
jbe@0
|
3029 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
3030 ORDER BY "winning_initiative_id", "losing_initiative_id"
|
|
jbe@0
|
3031 LOOP
|
|
jbe@30
|
3032 "vote_matrix"["i"]["j"] := "battle_row"."count";
|
|
jbe@0
|
3033 IF "j" = "dimension_v" THEN
|
|
jbe@0
|
3034 "i" := "i" + 1;
|
|
jbe@0
|
3035 "j" := 1;
|
|
jbe@0
|
3036 ELSE
|
|
jbe@0
|
3037 "j" := "j" + 1;
|
|
jbe@0
|
3038 IF "j" = "i" THEN
|
|
jbe@0
|
3039 "j" := "j" + 1;
|
|
jbe@0
|
3040 END IF;
|
|
jbe@0
|
3041 END IF;
|
|
jbe@0
|
3042 END LOOP;
|
|
jbe@0
|
3043 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
|
jbe@0
|
3044 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
|
jbe@0
|
3045 END IF;
|
|
jbe@30
|
3046 -- Store defeat strengths in "matrix" using "defeat_strength"
|
|
jbe@30
|
3047 -- function:
|
|
jbe@30
|
3048 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
|
jbe@0
|
3049 "i" := 1;
|
|
jbe@0
|
3050 LOOP
|
|
jbe@30
|
3051 "j" := 1;
|
|
jbe@0
|
3052 LOOP
|
|
jbe@0
|
3053 IF "i" != "j" THEN
|
|
jbe@30
|
3054 "matrix"["i"]["j"] := "defeat_strength"(
|
|
jbe@30
|
3055 "vote_matrix"["i"]["j"],
|
|
jbe@30
|
3056 "vote_matrix"["j"]["i"]
|
|
jbe@30
|
3057 );
|
|
jbe@0
|
3058 END IF;
|
|
jbe@0
|
3059 EXIT WHEN "j" = "dimension_v";
|
|
jbe@0
|
3060 "j" := "j" + 1;
|
|
jbe@0
|
3061 END LOOP;
|
|
jbe@30
|
3062 EXIT WHEN "i" = "dimension_v";
|
|
jbe@0
|
3063 "i" := "i" + 1;
|
|
jbe@0
|
3064 END LOOP;
|
|
jbe@0
|
3065 -- Find best paths:
|
|
jbe@0
|
3066 "i" := 1;
|
|
jbe@0
|
3067 LOOP
|
|
jbe@0
|
3068 "j" := 1;
|
|
jbe@0
|
3069 LOOP
|
|
jbe@0
|
3070 IF "i" != "j" THEN
|
|
jbe@0
|
3071 "k" := 1;
|
|
jbe@0
|
3072 LOOP
|
|
jbe@0
|
3073 IF "i" != "k" AND "j" != "k" THEN
|
|
jbe@0
|
3074 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
|
jbe@0
|
3075 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
|
jbe@0
|
3076 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
|
jbe@0
|
3077 END IF;
|
|
jbe@0
|
3078 ELSE
|
|
jbe@0
|
3079 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
|
jbe@0
|
3080 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
|
jbe@0
|
3081 END IF;
|
|
jbe@0
|
3082 END IF;
|
|
jbe@0
|
3083 END IF;
|
|
jbe@0
|
3084 EXIT WHEN "k" = "dimension_v";
|
|
jbe@0
|
3085 "k" := "k" + 1;
|
|
jbe@0
|
3086 END LOOP;
|
|
jbe@0
|
3087 END IF;
|
|
jbe@0
|
3088 EXIT WHEN "j" = "dimension_v";
|
|
jbe@0
|
3089 "j" := "j" + 1;
|
|
jbe@0
|
3090 END LOOP;
|
|
jbe@0
|
3091 EXIT WHEN "i" = "dimension_v";
|
|
jbe@0
|
3092 "i" := "i" + 1;
|
|
jbe@0
|
3093 END LOOP;
|
|
jbe@0
|
3094 -- Determine order of winners:
|
|
jbe@30
|
3095 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
|
|
jbe@0
|
3096 "rank_v" := 1;
|
|
jbe@0
|
3097 "done_v" := 0;
|
|
jbe@0
|
3098 LOOP
|
|
jbe@0
|
3099 "winners_ary" := '{}';
|
|
jbe@0
|
3100 "i" := 1;
|
|
jbe@0
|
3101 LOOP
|
|
jbe@0
|
3102 IF "rank_ary"["i"] ISNULL THEN
|
|
jbe@0
|
3103 "j" := 1;
|
|
jbe@0
|
3104 LOOP
|
|
jbe@0
|
3105 IF
|
|
jbe@0
|
3106 "i" != "j" AND
|
|
jbe@0
|
3107 "rank_ary"["j"] ISNULL AND
|
|
jbe@0
|
3108 "matrix"["j"]["i"] > "matrix"["i"]["j"]
|
|
jbe@0
|
3109 THEN
|
|
jbe@0
|
3110 -- someone else is better
|
|
jbe@0
|
3111 EXIT;
|
|
jbe@0
|
3112 END IF;
|
|
jbe@0
|
3113 IF "j" = "dimension_v" THEN
|
|
jbe@0
|
3114 -- noone is better
|
|
jbe@0
|
3115 "winners_ary" := "winners_ary" || "i";
|
|
jbe@0
|
3116 EXIT;
|
|
jbe@0
|
3117 END IF;
|
|
jbe@0
|
3118 "j" := "j" + 1;
|
|
jbe@0
|
3119 END LOOP;
|
|
jbe@0
|
3120 END IF;
|
|
jbe@0
|
3121 EXIT WHEN "i" = "dimension_v";
|
|
jbe@0
|
3122 "i" := "i" + 1;
|
|
jbe@0
|
3123 END LOOP;
|
|
jbe@0
|
3124 "i" := 1;
|
|
jbe@0
|
3125 LOOP
|
|
jbe@0
|
3126 "rank_ary"["winners_ary"["i"]] := "rank_v";
|
|
jbe@0
|
3127 "done_v" := "done_v" + 1;
|
|
jbe@0
|
3128 EXIT WHEN "i" = array_upper("winners_ary", 1);
|
|
jbe@0
|
3129 "i" := "i" + 1;
|
|
jbe@0
|
3130 END LOOP;
|
|
jbe@0
|
3131 EXIT WHEN "done_v" = "dimension_v";
|
|
jbe@0
|
3132 "rank_v" := "rank_v" + 1;
|
|
jbe@0
|
3133 END LOOP;
|
|
jbe@0
|
3134 -- write preliminary ranks:
|
|
jbe@0
|
3135 "i" := 1;
|
|
jbe@0
|
3136 FOR "initiative_id_v" IN
|
|
jbe@10
|
3137 SELECT "id" FROM "initiative"
|
|
jbe@10
|
3138 WHERE "issue_id" = "issue_id_p" AND "agreed"
|
|
jbe@10
|
3139 ORDER BY "id"
|
|
jbe@0
|
3140 LOOP
|
|
jbe@0
|
3141 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
|
|
jbe@0
|
3142 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
3143 "i" := "i" + 1;
|
|
jbe@0
|
3144 END LOOP;
|
|
jbe@0
|
3145 IF "i" != "dimension_v" + 1 THEN
|
|
jbe@0
|
3146 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
|
jbe@0
|
3147 END IF;
|
|
jbe@0
|
3148 -- straighten ranks (start counting with 1, no equal ranks):
|
|
jbe@0
|
3149 "rank_v" := 1;
|
|
jbe@0
|
3150 FOR "initiative_id_v" IN
|
|
jbe@0
|
3151 SELECT "id" FROM "initiative"
|
|
jbe@0
|
3152 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
|
|
jbe@0
|
3153 ORDER BY
|
|
jbe@0
|
3154 "rank",
|
|
jbe@0
|
3155 "vote_ratio"("positive_votes", "negative_votes") DESC,
|
|
jbe@0
|
3156 "id"
|
|
jbe@0
|
3157 LOOP
|
|
jbe@0
|
3158 UPDATE "initiative" SET "rank" = "rank_v"
|
|
jbe@0
|
3159 WHERE "id" = "initiative_id_v";
|
|
jbe@0
|
3160 "rank_v" := "rank_v" + 1;
|
|
jbe@0
|
3161 END LOOP;
|
|
jbe@0
|
3162 END IF;
|
|
jbe@0
|
3163 -- mark issue as finished
|
|
jbe@0
|
3164 UPDATE "issue" SET "ranks_available" = TRUE
|
|
jbe@0
|
3165 WHERE "id" = "issue_id_p";
|
|
jbe@0
|
3166 RETURN;
|
|
jbe@0
|
3167 END;
|
|
jbe@0
|
3168 $$;
|
|
jbe@0
|
3169
|
|
jbe@0
|
3170 COMMENT ON FUNCTION "calculate_ranks"
|
|
jbe@0
|
3171 ( "issue"."id"%TYPE )
|
|
jbe@0
|
3172 IS 'Determine ranking (Votes have to be counted first)';
|
|
jbe@0
|
3173
|
|
jbe@0
|
3174
|
|
jbe@0
|
3175
|
|
jbe@0
|
3176 -----------------------------
|
|
jbe@0
|
3177 -- Automatic state changes --
|
|
jbe@0
|
3178 -----------------------------
|
|
jbe@0
|
3179
|
|
jbe@0
|
3180
|
|
jbe@0
|
3181 CREATE FUNCTION "check_issue"
|
|
jbe@0
|
3182 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@0
|
3183 RETURNS VOID
|
|
jbe@0
|
3184 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
3185 DECLARE
|
|
jbe@0
|
3186 "issue_row" "issue"%ROWTYPE;
|
|
jbe@0
|
3187 "policy_row" "policy"%ROWTYPE;
|
|
jbe@0
|
3188 "voting_requested_v" BOOLEAN;
|
|
jbe@0
|
3189 BEGIN
|
|
jbe@67
|
3190 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@0
|
3191 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@24
|
3192 -- only process open issues:
|
|
jbe@0
|
3193 IF "issue_row"."closed" ISNULL THEN
|
|
jbe@0
|
3194 SELECT * INTO "policy_row" FROM "policy"
|
|
jbe@0
|
3195 WHERE "id" = "issue_row"."policy_id";
|
|
jbe@24
|
3196 -- create a snapshot, unless issue is already fully frozen:
|
|
jbe@3
|
3197 IF "issue_row"."fully_frozen" ISNULL THEN
|
|
jbe@0
|
3198 PERFORM "create_snapshot"("issue_id_p");
|
|
jbe@0
|
3199 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
3200 END IF;
|
|
jbe@24
|
3201 -- eventually close or accept issues, which have not been accepted:
|
|
jbe@0
|
3202 IF "issue_row"."accepted" ISNULL THEN
|
|
jbe@0
|
3203 IF EXISTS (
|
|
jbe@0
|
3204 SELECT NULL FROM "initiative"
|
|
jbe@0
|
3205 WHERE "issue_id" = "issue_id_p"
|
|
jbe@0
|
3206 AND "supporter_count" > 0
|
|
jbe@0
|
3207 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
|
jbe@0
|
3208 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
|
jbe@0
|
3209 ) THEN
|
|
jbe@24
|
3210 -- accept issues, if supporter count is high enough
|
|
jbe@3
|
3211 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
|
jbe@0
|
3212 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
|
|
jbe@0
|
3213 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
|
|
jbe@0
|
3214 WHERE "id" = "issue_row"."id";
|
|
jbe@0
|
3215 ELSIF
|
|
jbe@22
|
3216 now() >= "issue_row"."created" + "issue_row"."admission_time"
|
|
jbe@0
|
3217 THEN
|
|
jbe@24
|
3218 -- close issues, if admission time has expired
|
|
jbe@0
|
3219 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
|
jbe@0
|
3220 UPDATE "issue" SET "closed" = now()
|
|
jbe@0
|
3221 WHERE "id" = "issue_row"."id";
|
|
jbe@0
|
3222 END IF;
|
|
jbe@0
|
3223 END IF;
|
|
jbe@24
|
3224 -- eventually half freeze issues:
|
|
jbe@0
|
3225 IF
|
|
jbe@24
|
3226 -- NOTE: issue can't be closed at this point, if it has been accepted
|
|
jbe@0
|
3227 "issue_row"."accepted" NOTNULL AND
|
|
jbe@3
|
3228 "issue_row"."half_frozen" ISNULL
|
|
jbe@0
|
3229 THEN
|
|
jbe@0
|
3230 SELECT
|
|
jbe@0
|
3231 CASE
|
|
jbe@0
|
3232 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
|
jbe@0
|
3233 TRUE
|
|
jbe@0
|
3234 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
|
jbe@0
|
3235 FALSE
|
|
jbe@0
|
3236 ELSE NULL
|
|
jbe@0
|
3237 END
|
|
jbe@0
|
3238 INTO "voting_requested_v"
|
|
jbe@0
|
3239 FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@0
|
3240 IF
|
|
jbe@0
|
3241 "voting_requested_v" OR (
|
|
jbe@3
|
3242 "voting_requested_v" ISNULL AND
|
|
jbe@22
|
3243 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
|
|
jbe@0
|
3244 )
|
|
jbe@0
|
3245 THEN
|
|
jbe@21
|
3246 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
|
|
jbe@3
|
3247 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
|
|
jbe@3
|
3248 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
|
|
jbe@3
|
3249 WHERE "id" = "issue_row"."id";
|
|
jbe@0
|
3250 END IF;
|
|
jbe@0
|
3251 END IF;
|
|
jbe@24
|
3252 -- close issues after some time, if all initiatives have been revoked:
|
|
jbe@24
|
3253 IF
|
|
jbe@24
|
3254 "issue_row"."closed" ISNULL AND
|
|
jbe@24
|
3255 NOT EXISTS (
|
|
jbe@24
|
3256 -- all initiatives are revoked
|
|
jbe@24
|
3257 SELECT NULL FROM "initiative"
|
|
jbe@24
|
3258 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
|
jbe@24
|
3259 ) AND (
|
|
jbe@24
|
3260 NOT EXISTS (
|
|
jbe@24
|
3261 -- and no initiatives have been revoked lately
|
|
jbe@24
|
3262 SELECT NULL FROM "initiative"
|
|
jbe@24
|
3263 WHERE "issue_id" = "issue_id_p"
|
|
jbe@24
|
3264 AND now() < "revoked" + "issue_row"."verification_time"
|
|
jbe@24
|
3265 ) OR (
|
|
jbe@24
|
3266 -- or verification time has elapsed
|
|
jbe@24
|
3267 "issue_row"."half_frozen" NOTNULL AND
|
|
jbe@24
|
3268 "issue_row"."fully_frozen" ISNULL AND
|
|
jbe@24
|
3269 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
|
jbe@24
|
3270 )
|
|
jbe@24
|
3271 )
|
|
jbe@24
|
3272 THEN
|
|
jbe@24
|
3273 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
|
|
jbe@24
|
3274 UPDATE "issue" SET "closed" = "issue_row"."closed"
|
|
jbe@24
|
3275 WHERE "id" = "issue_row"."id";
|
|
jbe@24
|
3276 END IF;
|
|
jbe@24
|
3277 -- fully freeze issue after verification time:
|
|
jbe@0
|
3278 IF
|
|
jbe@3
|
3279 "issue_row"."half_frozen" NOTNULL AND
|
|
jbe@3
|
3280 "issue_row"."fully_frozen" ISNULL AND
|
|
jbe@24
|
3281 "issue_row"."closed" ISNULL AND
|
|
jbe@22
|
3282 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
|
jbe@3
|
3283 THEN
|
|
jbe@3
|
3284 PERFORM "freeze_after_snapshot"("issue_id_p");
|
|
jbe@24
|
3285 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
|
|
jbe@3
|
3286 END IF;
|
|
jbe@9
|
3287 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@24
|
3288 -- close issue by calling close_voting(...) after voting time:
|
|
jbe@3
|
3289 IF
|
|
jbe@9
|
3290 "issue_row"."closed" ISNULL AND
|
|
jbe@3
|
3291 "issue_row"."fully_frozen" NOTNULL AND
|
|
jbe@22
|
3292 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
|
|
jbe@0
|
3293 THEN
|
|
jbe@0
|
3294 PERFORM "close_voting"("issue_id_p");
|
|
jbe@0
|
3295 END IF;
|
|
jbe@0
|
3296 END IF;
|
|
jbe@0
|
3297 RETURN;
|
|
jbe@0
|
3298 END;
|
|
jbe@0
|
3299 $$;
|
|
jbe@0
|
3300
|
|
jbe@0
|
3301 COMMENT ON FUNCTION "check_issue"
|
|
jbe@0
|
3302 ( "issue"."id"%TYPE )
|
|
jbe@0
|
3303 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
|
3304
|
|
jbe@0
|
3305
|
|
jbe@0
|
3306 CREATE FUNCTION "check_everything"()
|
|
jbe@0
|
3307 RETURNS VOID
|
|
jbe@0
|
3308 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@0
|
3309 DECLARE
|
|
jbe@0
|
3310 "issue_id_v" "issue"."id"%TYPE;
|
|
jbe@0
|
3311 BEGIN
|
|
jbe@1
|
3312 DELETE FROM "expired_session";
|
|
jbe@4
|
3313 PERFORM "calculate_member_counts"();
|
|
jbe@4
|
3314 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
|
|
jbe@0
|
3315 PERFORM "check_issue"("issue_id_v");
|
|
jbe@0
|
3316 END LOOP;
|
|
jbe@4
|
3317 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
|
|
jbe@0
|
3318 PERFORM "calculate_ranks"("issue_id_v");
|
|
jbe@0
|
3319 END LOOP;
|
|
jbe@0
|
3320 RETURN;
|
|
jbe@0
|
3321 END;
|
|
jbe@0
|
3322 $$;
|
|
jbe@0
|
3323
|
|
jbe@0
|
3324 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
|
3325
|
|
jbe@0
|
3326
|
|
jbe@0
|
3327
|
|
jbe@59
|
3328 ----------------------
|
|
jbe@59
|
3329 -- Deletion of data --
|
|
jbe@59
|
3330 ----------------------
|
|
jbe@59
|
3331
|
|
jbe@59
|
3332
|
|
jbe@59
|
3333 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@59
|
3334 RETURNS VOID
|
|
jbe@59
|
3335 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@59
|
3336 DECLARE
|
|
jbe@59
|
3337 "issue_row" "issue"%ROWTYPE;
|
|
jbe@59
|
3338 BEGIN
|
|
jbe@59
|
3339 SELECT * INTO "issue_row"
|
|
jbe@59
|
3340 FROM "issue" WHERE "id" = "issue_id_p"
|
|
jbe@59
|
3341 FOR UPDATE;
|
|
jbe@59
|
3342 IF "issue_row"."cleaned" ISNULL THEN
|
|
jbe@59
|
3343 UPDATE "issue" SET
|
|
jbe@59
|
3344 "closed" = NULL,
|
|
jbe@59
|
3345 "ranks_available" = FALSE
|
|
jbe@59
|
3346 WHERE "id" = "issue_id_p";
|
|
jbe@59
|
3347 DELETE FROM "delegating_voter"
|
|
jbe@59
|
3348 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3349 DELETE FROM "direct_voter"
|
|
jbe@59
|
3350 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3351 DELETE FROM "delegating_interest_snapshot"
|
|
jbe@59
|
3352 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3353 DELETE FROM "direct_interest_snapshot"
|
|
jbe@59
|
3354 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3355 DELETE FROM "delegating_population_snapshot"
|
|
jbe@59
|
3356 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3357 DELETE FROM "direct_population_snapshot"
|
|
jbe@59
|
3358 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3359 DELETE FROM "delegation"
|
|
jbe@59
|
3360 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3361 DELETE FROM "supporter"
|
|
jbe@59
|
3362 WHERE "issue_id" = "issue_id_p";
|
|
jbe@59
|
3363 UPDATE "issue" SET
|
|
jbe@59
|
3364 "closed" = "issue_row"."closed",
|
|
jbe@59
|
3365 "ranks_available" = "issue_row"."ranks_available",
|
|
jbe@59
|
3366 "cleaned" = now()
|
|
jbe@59
|
3367 WHERE "id" = "issue_id_p";
|
|
jbe@59
|
3368 END IF;
|
|
jbe@59
|
3369 RETURN;
|
|
jbe@59
|
3370 END;
|
|
jbe@59
|
3371 $$;
|
|
jbe@59
|
3372
|
|
jbe@59
|
3373 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
|
|
jbe@8
|
3374
|
|
jbe@8
|
3375
|
|
jbe@54
|
3376 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
|
jbe@8
|
3377 RETURNS VOID
|
|
jbe@8
|
3378 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@8
|
3379 BEGIN
|
|
jbe@9
|
3380 UPDATE "member" SET
|
|
jbe@57
|
3381 "last_login" = NULL,
|
|
jbe@45
|
3382 "login" = NULL,
|
|
jbe@11
|
3383 "password" = NULL,
|
|
jbe@54
|
3384 "active" = FALSE,
|
|
jbe@11
|
3385 "notify_email" = NULL,
|
|
jbe@11
|
3386 "notify_email_unconfirmed" = NULL,
|
|
jbe@11
|
3387 "notify_email_secret" = NULL,
|
|
jbe@11
|
3388 "notify_email_secret_expiry" = NULL,
|
|
jbe@57
|
3389 "notify_email_lock_expiry" = NULL,
|
|
jbe@11
|
3390 "password_reset_secret" = NULL,
|
|
jbe@11
|
3391 "password_reset_secret_expiry" = NULL,
|
|
jbe@11
|
3392 "organizational_unit" = NULL,
|
|
jbe@11
|
3393 "internal_posts" = NULL,
|
|
jbe@11
|
3394 "realname" = NULL,
|
|
jbe@11
|
3395 "birthday" = NULL,
|
|
jbe@11
|
3396 "address" = NULL,
|
|
jbe@11
|
3397 "email" = NULL,
|
|
jbe@11
|
3398 "xmpp_address" = NULL,
|
|
jbe@11
|
3399 "website" = NULL,
|
|
jbe@11
|
3400 "phone" = NULL,
|
|
jbe@11
|
3401 "mobile_phone" = NULL,
|
|
jbe@11
|
3402 "profession" = NULL,
|
|
jbe@11
|
3403 "external_memberships" = NULL,
|
|
jbe@11
|
3404 "external_posts" = NULL,
|
|
jbe@45
|
3405 "statement" = NULL
|
|
jbe@45
|
3406 WHERE "id" = "member_id_p";
|
|
jbe@11
|
3407 -- "text_search_data" is updated by triggers
|
|
jbe@45
|
3408 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3409 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3410 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3411 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3412 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3413 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3414 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3415 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@45
|
3416 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@54
|
3417 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
|
jbe@54
|
3418 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
|
poelzi@79
|
3419 DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p";
|
|
jbe@57
|
3420 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@57
|
3421 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@57
|
3422 AND "issue"."closed" ISNULL
|
|
jbe@57
|
3423 AND "member_id" = "member_id_p";
|
|
jbe@45
|
3424 RETURN;
|
|
jbe@45
|
3425 END;
|
|
jbe@45
|
3426 $$;
|
|
jbe@45
|
3427
|
|
jbe@57
|
3428 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
|
|
jbe@45
|
3429
|
|
jbe@45
|
3430
|
|
jbe@45
|
3431 CREATE FUNCTION "delete_private_data"()
|
|
jbe@45
|
3432 RETURNS VOID
|
|
jbe@45
|
3433 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@45
|
3434 BEGIN
|
|
jbe@50
|
3435 UPDATE "member" SET
|
|
jbe@57
|
3436 "last_login" = NULL,
|
|
jbe@50
|
3437 "login" = NULL,
|
|
jbe@50
|
3438 "password" = NULL,
|
|
jbe@50
|
3439 "notify_email" = NULL,
|
|
jbe@50
|
3440 "notify_email_unconfirmed" = NULL,
|
|
jbe@50
|
3441 "notify_email_secret" = NULL,
|
|
jbe@50
|
3442 "notify_email_secret_expiry" = NULL,
|
|
jbe@57
|
3443 "notify_email_lock_expiry" = NULL,
|
|
jbe@50
|
3444 "password_reset_secret" = NULL,
|
|
jbe@50
|
3445 "password_reset_secret_expiry" = NULL,
|
|
jbe@50
|
3446 "organizational_unit" = NULL,
|
|
jbe@50
|
3447 "internal_posts" = NULL,
|
|
jbe@50
|
3448 "realname" = NULL,
|
|
jbe@50
|
3449 "birthday" = NULL,
|
|
jbe@50
|
3450 "address" = NULL,
|
|
jbe@50
|
3451 "email" = NULL,
|
|
jbe@50
|
3452 "xmpp_address" = NULL,
|
|
jbe@50
|
3453 "website" = NULL,
|
|
jbe@50
|
3454 "phone" = NULL,
|
|
jbe@50
|
3455 "mobile_phone" = NULL,
|
|
jbe@50
|
3456 "profession" = NULL,
|
|
jbe@50
|
3457 "external_memberships" = NULL,
|
|
jbe@50
|
3458 "external_posts" = NULL,
|
|
jbe@50
|
3459 "statement" = NULL;
|
|
jbe@50
|
3460 -- "text_search_data" is updated by triggers
|
|
jbe@54
|
3461 DELETE FROM "invite_code";
|
|
jbe@50
|
3462 DELETE FROM "setting";
|
|
jbe@50
|
3463 DELETE FROM "setting_map";
|
|
jbe@50
|
3464 DELETE FROM "member_relation_setting";
|
|
jbe@50
|
3465 DELETE FROM "member_image";
|
|
jbe@50
|
3466 DELETE FROM "contact";
|
|
jbe@54
|
3467 DELETE FROM "session";
|
|
jbe@50
|
3468 DELETE FROM "area_setting";
|
|
jbe@50
|
3469 DELETE FROM "issue_setting";
|
|
jbe@50
|
3470 DELETE FROM "initiative_setting";
|
|
jbe@50
|
3471 DELETE FROM "suggestion_setting";
|
|
jbe@8
|
3472 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@8
|
3473 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@8
|
3474 AND "issue"."closed" ISNULL;
|
|
jbe@8
|
3475 RETURN;
|
|
jbe@8
|
3476 END;
|
|
jbe@8
|
3477 $$;
|
|
jbe@8
|
3478
|
|
jbe@8
|
3479 COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
|
|
jbe@8
|
3480
|
|
jbe@8
|
3481
|
|
jbe@8
|
3482
|
|
jbe@0
|
3483 COMMIT;
|