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