liquid_feedback_core
view core.sql @ 528:3e28fd842354
Work on new issue admission system (replacing current approach of 1st quorum); Added support for geo-tagging and spatial indexing
| author | jbe |
|---|---|
| date | Sun May 29 20:42:40 2016 +0200 (2016-05-29) |
| parents | 2a2f76da1177 |
| children | 96ee2db56bec |
line source
2 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
4 BEGIN;
6 CREATE VIEW "liquid_feedback_version" AS
7 SELECT * FROM (VALUES ('4.0.0', 4, 0, 0))
8 AS "subquery"("string", "major", "minor", "revision");
12 ----------------------
13 -- Full text search --
14 ----------------------
17 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
18 RETURNS TSQUERY
19 LANGUAGE 'plpgsql' IMMUTABLE AS $$
20 BEGIN
21 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
22 END;
23 $$;
25 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
28 CREATE FUNCTION "highlight"
29 ( "body_p" TEXT,
30 "query_text_p" TEXT )
31 RETURNS TEXT
32 LANGUAGE 'plpgsql' IMMUTABLE AS $$
33 BEGIN
34 RETURN ts_headline(
35 'pg_catalog.simple',
36 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
37 "text_search_query"("query_text_p"),
38 'StartSel=* StopSel=* HighlightAll=TRUE' );
39 END;
40 $$;
42 COMMENT ON FUNCTION "highlight"
43 ( "body_p" TEXT,
44 "query_text_p" TEXT )
45 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.';
49 -----------------------------------------------
50 -- spatial indexing and distance calculation --
51 -----------------------------------------------
54 CREATE DOMAIN "geoindex" AS point CHECK (VALUE <@ '((-1,-1),(1,1))'::box);
56 COMMENT ON DOMAIN "geoindex" IS 'Data type used for indexing geographical coordinates';
59 CREATE FUNCTION "geoindex"
60 ( "latitude_p" FLOAT,
61 "longitude_p" FLOAT )
62 RETURNS "geoindex"
63 LANGUAGE sql IMMUTABLE STRICT AS $$
64 SELECT point("latitude_p" / 90, "longitude_p" / 180)::"geoindex"
65 $$;
67 COMMENT ON FUNCTION "geoindex"
68 (FLOAT, FLOAT)
69 IS 'Transforms coordinates given as latitude (from -90 to 90) and logitude (from -180 to 180) into a data type suitable for a quadtree index';
72 CREATE TYPE "geosearch" AS (
73 "lat" FLOAT,
74 "lon" FLOAT,
75 "c3" FLOAT,
76 "c2" FLOAT,
77 "c1" FLOAT,
78 "c0" FLOAT,
79 "dist" FLOAT,
80 "bbox1" BOX,
81 "bbox2" BOX );
83 COMMENT ON TYPE "geosearch" IS 'Structure returned by function "geosearch" holding one or two bounding boxes ("bbox1" and optionally "bbox2") for index lookup as well as the starting point (i.e. center point given by "lat" and "lon") and coefficients ("c3" through "c0") for distance approximation';
85 COMMENT ON COLUMN "geosearch"."lat" IS 'Latitude of center of search area';
86 COMMENT ON COLUMN "geosearch"."lon" IS 'Longitude of center of search area';
87 COMMENT ON COLUMN "geosearch"."c3" IS 'Coefficient for distance calculation';
88 COMMENT ON COLUMN "geosearch"."c2" IS 'Coefficient for distance calculation';
89 COMMENT ON COLUMN "geosearch"."c1" IS 'Coefficient for distance calculation';
90 COMMENT ON COLUMN "geosearch"."c0" IS 'Coefficient for distance calculation';
91 COMMENT ON COLUMN "geosearch"."bbox1" IS 'Bounding box suitable for use with output of "geoindex" function';
92 COMMENT ON COLUMN "geosearch"."bbox2" IS 'Second bounding box (see column "bbox1") set if search crosses the 180th meridian';
95 CREATE FUNCTION "geosearch"
96 ( "latitude_p" FLOAT,
97 "longitude_p" FLOAT )
98 RETURNS "geosearch"
99 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
100 DECLARE
101 -- constants:
102 -- (with a = radius at equator, and b = radius at pole)
103 "degrad" FLOAT := 0.017453292519943295; -- pi / 180
104 "degrad2" FLOAT := 0.00030461741978670857; -- (pi / 180)^2
105 "a2" FLOAT := 40680631590769; -- a^2
106 "eps2" FLOAT := 0.006694379990141316; -- (a^2 - b^2) / a^2
107 "subeps2" FLOAT := 0.9933056200098587; -- 1 - "eps2"
108 "subeps22" FLOAT := 0.9866560547431698; -- (1 - "eps2")^2
109 -- latitude in radians:
110 "phi0rad" FLOAT := "degrad" * "latitude_p";
111 -- helper variables:
112 "sinp0" FLOAT; -- sin("phi0rad")
113 "cosp0" FLOAT; -- cos("phi0rad")
114 "sinp02" FLOAT; -- sin("phi0rad")^2
115 "e2sinp02" FLOAT; -- "eps2" * sin("phi0rad")^2
116 "subsin" FLOAT; -- 1 - sin("phi0rad")^2
117 "subse" FLOAT; -- 1 - "eps2" * sin("phi0rad")^2
118 "subse2" FLOAT; -- ("subse")^2
119 "t20" FLOAT; -- taylor coefficient for (delta latitude)^2
120 "t02" FLOAT; -- taylor coefficient for (delta longitude)^2 / "t20"
121 "t12" FLOAT; -- taylor coefficient for (d lat)^1*(d lon)^2 / "t20"
122 "t22" FLOAT; -- taylor coefficient for (d lat)^2*(d lon)^2 / "t20"
123 -- result:
124 "res" "geosearch";
125 BEGIN
126 PERFORM "geoindex"("latitude_p", "longitude_p"); -- check bounds
127 "sinp0" := sin("phi0rad");
128 "cosp0" := cos("phi0rad");
129 "sinp02" := "sinp0" * "sinp0";
130 "e2sinp02" := "eps2" * "sinp02";
131 "subsin" := 1 - "sinp02";
132 "subse" := 1 - "e2sinp02";
133 "subse2" := "subse" * "subse";
134 "t20" := "a2" * "subeps22" / ("subse" * "subse2");
135 "t02" := "subsin" * "subse2" / "subeps22";
136 "t12" := -"sinp0" * "cosp0" * "subse" / "subeps2";
137 "t22" := -"subsin" * (0.5+"e2sinp02") / "subeps2";
138 "res"."lat" := "latitude_p";
139 "res"."lon" := "longitude_p";
140 "res"."c3" := "degrad2" * "t20";
141 "res"."c2" := "degrad2" * "t22";
142 "res"."c1" := "degrad" * ("t12" - 2 * "t22" * "phi0rad");
143 "res"."c0" := ("t22" * "phi0rad" - "t12") * "phi0rad" + "t02";
144 "res"."dist" := 'Infinity'::FLOAT;
145 "res"."bbox1" := box(point(1, 1), point(-1, -1));
146 "res"."bbox2" := NULL;
147 RETURN "res";
148 END;
149 $$;
151 COMMENT ON FUNCTION "geosearch"
152 ( FLOAT, FLOAT )
153 IS 'Takes latitude and longitude (in degrees), and returns a "geosearch" structure suitable for distance calculation; NOTE: use function with same name but three arguments, i.e. "geosearch"(FLOAT, FLOAT, FLOAT), for searches with limited radius';
156 CREATE FUNCTION "geosearch"
157 ( "latitude_p" FLOAT,
158 "longitude_p" FLOAT,
159 "radius_p" FLOAT )
160 RETURNS "geosearch"
161 LANGUAGE plpgsql IMMUTABLE AS $$
162 DECLARE
163 -- constants:
164 "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90)
165 "margin" FLOAT := 1.0001; -- slightly increase search area
166 -- NOTE: search area is increased by "margin", search radius gets
167 -- increased by sqrt("margin"); choose a value > 1.0 to avoid
168 -- floating point errors
169 "fixzero" FLOAT := 0.0001; -- small value > 0 to fix singularity
170 -- result:
171 "res" "geosearch";
172 -- helper variables:
173 "rc3mar" FLOAT; -- ("radius_p")^2 / "c3", multiplied with "margin"
174 "subc02" FLOAT; -- 2 * ("c0" - "c2" * "rc3mar")
175 "b4" FLOAT; -- ("c2")^2
176 "b3" FLOAT; -- 2 * "c1" * "c2"
177 "b2" FLOAT; -- ("c1")^2 + "subc02" * "c2"
178 "b1" FLOAT; -- "subc02" * "c1"
179 "b0" FLOAT; -- ("c2")^2 * ("rc3mar")^2 +
180 -- 2 * "c0" * "c2" - ("c1")^2 * ("rc3mar")^1 +
181 -- ("c0")^2 * ("rc3mar")^0
182 "sqrtval" FLOAT; -- "b4" * ("latitude_p")^4 +
183 -- "b3" * ("latitude_p")^3 +
184 -- "b2" * ("latitude_p")^2 +
185 -- "b1" * ("latitude_p")^1 +
186 -- "b0" * ("latitude_p")^0
187 "phic" FLOAT; -- ( "c2" * (("latitude_p")^2 - "rc3mar") - "c0"
188 -- + sqrt("sqrtval")
189 -- ) / ( 2 * "c2" * "latitude_p" + "c1" )
190 "dlat" FLOAT; -- delta latitude in degrees = sqrt("rc3mar")
191 "dlon2" FLOAT; -- square of delta longitude
192 "dlon" FLOAT; -- delta longitude in degrees =
193 -- sqrt(
194 -- ( "rc3mar" - ("phic" - "latitude_p")^2 ) /
195 -- ( "c2" * ("phic")^2 +
196 -- "c1" * ("phic")^1 +
197 -- "c0" )
198 -- )
199 "lat_min" FLOAT;
200 "lat_max" FLOAT;
201 "lon_min" FLOAT;
202 "lon_max" FLOAT;
203 BEGIN
204 "res" := "geosearch"("latitude_p", "longitude_p");
205 IF "res" ISNULL THEN RETURN NULL; END IF;
206 IF "radius_p" ISNULL OR "radius_p" = 'Infinity'::FLOAT THEN
207 RETURN "res";
208 END IF;
209 "res"."dist" := "radius_p";
210 "rc3mar" := "margin" * "radius_p" * "radius_p" / "res"."c3";
211 "subc02" := 2 * ("res"."c0" - "res"."c2" * "rc3mar");
212 "b4" := "res"."c2" * "res"."c2";
213 "b3" := 2 * "res"."c1" * "res"."c2";
214 "b2" := "res"."c1" * "res"."c1" + "subc02" * "res"."c2";
215 "b1" := "subc02" * "res"."c1";
216 "b0" := ( "b4" * "rc3mar" +
217 2 * "res"."c0" * "res"."c2" - "res"."c1" * "res"."c1"
218 ) * "rc3mar" + "res"."c0" * "res"."c0";
219 "sqrtval" := ( ( ( ( "b4"
220 ) * "latitude_p" + "b3"
221 ) * "latitude_p" + "b2"
222 ) * "latitude_p" + "b1"
223 ) * "latitude_p" + "b0";
224 IF "sqrtval" < 0 THEN
225 IF "latitude_p" >= 0 THEN "phic" := "philimit";
226 ELSE "phic" := -"philimit"; END IF;
227 ELSE
228 IF abs("latitude_p") <= "fixzero" THEN
229 "phic" := "latitude_p";
230 ELSE
231 "phic" := (
232 "res"."c2" * ("latitude_p" * "latitude_p" - "rc3mar")
233 - "res"."c0" + sqrt("sqrtval")
234 ) / (
235 2 * "res"."c2" * "latitude_p" + "res"."c1"
236 );
237 IF "phic" > "philimit" THEN "phic" := "philimit";
238 ELSIF "phic" < -"philimit" THEN "phic" := -"philimit"; END IF;
239 END IF;
240 END IF;
241 "dlat" := sqrt("rc3mar");
242 "dlon2" := (
243 ( "rc3mar" - ("phic" - "latitude_p")^2 ) /
244 ( ( "res"."c2" * "phic" + "res"."c1" ) * "phic" + "res"."c0" )
245 );
246 IF "dlon2" > 0 THEN "dlon" := sqrt("dlon2");
247 ELSE "dlon" := 0; END IF;
248 "lat_min" := "latitude_p" - "dlat";
249 "lat_max" := "latitude_p" + "dlat";
250 IF "lat_min" < -90 THEN "lat_min" := -90; END IF;
251 IF "lat_max" > 90 THEN "lat_max" := 90; END IF;
252 "lon_min" := "longitude_p" - "dlon";
253 "lon_max" := "longitude_p" + "dlon";
254 IF "lon_min" < -180 THEN
255 IF "lon_max" > 180 THEN
256 "res"."bbox1" := box(
257 "geoindex"("lat_min", -180),
258 "geoindex"("lat_max", 180) );
259 "res"."bbox2" := NULL;
260 ELSE
261 "res"."bbox1" := box(
262 "geoindex"("lat_min", -180),
263 "geoindex"("lat_max", "lon_max") );
264 "res"."bbox2" := box(
265 "geoindex"("lat_min", "lon_min" + 360),
266 "geoindex"("lat_max", 180) );
267 END IF;
268 ELSIF "lon_max" > 180 THEN
269 "res"."bbox1" := box(
270 "geoindex"("lat_min", "lon_min"),
271 "geoindex"("lat_max", 180) );
272 "res"."bbox2" := box(
273 "geoindex"("lat_min", -180),
274 "geoindex"("lat_max", "lon_max" - 360) );
275 ELSE
276 "res"."bbox1" := box(
277 "geoindex"("lat_min", "lon_min"),
278 "geoindex"("lat_max", "lon_max") );
279 "res"."bbox2" := NULL;
280 END IF;
281 RETURN "res";
282 END;
283 $$;
285 COMMENT ON FUNCTION "geosearch"
286 ( FLOAT, FLOAT, FLOAT )
287 IS 'Takes latitude, longitude (both in degrees), and a radius (in meters), and returns a "geoindex" structure suitable for searching and distance calculation';
290 CREATE FUNCTION "geodist"
291 ( "geosearch_p" "geosearch",
292 "latitude_p" FLOAT,
293 "longitude_p" FLOAT )
294 RETURNS FLOAT
295 LANGUAGE sql IMMUTABLE AS $$
296 SELECT sqrt(
297 "geosearch_p"."c3" * (
298 ("latitude_p" - "geosearch_p"."lat")^2 +
299 ( ( "geosearch_p"."c2" * "latitude_p" + "geosearch_p"."c1" )
300 * "latitude_p" + "geosearch_p"."c0" ) *
301 ( "longitude_p" +
302 CASE WHEN "longitude_p" - "geosearch_p"."lon" > 180
303 THEN -360 ELSE
304 CASE WHEN "longitude_p" - "geosearch_p"."lon" < -180
305 THEN 360
306 ELSE 0 END
307 END - "geosearch_p"."lon"
308 )^2
309 )
310 )
311 $$;
313 COMMENT ON FUNCTION "geodist"
314 ( "geosearch", FLOAT, FLOAT )
315 IS 'Takes a "geosearch" structure as well as geographical coordinates in degrees and returns the distance on earth in meters';
318 /* Usage of spatial indexing and distance calculation functions for points:
320 -- create table containing geographical coordinates in degrees:
321 CREATE TABLE "t1" (
322 "id" SERIAL4 PRIMARY KEY,
323 "latitude" NUMERIC NOT NULL,
324 "longitude" NUMERIC NOT NULL );
326 -- create an SP-GiST index (with default quad_point_ops operator class):
327 CREATE INDEX "t1_geoindex" ON "t1" USING spgist (("geoindex"("latitude", "longitude")));
329 -- return results within 10,000 meters of Brandenburg Gate:
330 SELECT "t1".*, "distance"
331 FROM "t1"
332 CROSS JOIN "geosearch"(52.5162746, 13.377704, 10000)
333 CROSS JOIN LATERAL "geodist"("geosearch", "latitude", "longitude") AS "distance"
334 WHERE (
335 "geoindex"("latitude", "longitude") <@ "geosearch"."bbox1" OR
336 "geoindex"("latitude", "longitude") <@ "geosearch"."bbox2" )
337 AND "distance" <= "geosearch"."dist";
339 */
342 CREATE FUNCTION "polyindex"
343 ( "point_p" POINT )
344 RETURNS POLYGON
345 LANGUAGE sql IMMUTABLE STRICT AS $$
346 SELECT "point_p"::TEXT::POLYGON;
347 $$;
349 COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a point (with latitude in degrees as first value and longitude in degrees as second value) into a polygon suitable for GiST poly_ops indexing';
352 CREATE FUNCTION "polyindex"
353 ( "latitude_p" FLOAT,
354 "longitude_p" FLOAT )
355 RETURNS POLYGON
356 LANGUAGE sql IMMUTABLE STRICT AS $$
357 SELECT "polyindex"(point("latitude_p", "longitude_p"))
358 $$;
360 COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a point given as latitude and longitude in degrees into a polygon suitable for GiST poly_ops indexing';
363 CREATE FUNCTION "polyindex"
364 ( "polygon_p" POLYGON )
365 RETURNS POLYGON
366 LANGUAGE sql IMMUTABLE STRICT AS $$
367 SELECT "polygon_p"
368 $$;
370 COMMENT ON FUNCTION "polyindex"(POLYGON) IS 'Does nothing but returning the polygon unchanged (for GiST poly_ops indexing)';
373 CREATE FUNCTION "polyindex"
374 ( "box_p" BOX )
375 RETURNS POLYGON
376 LANGUAGE sql IMMUTABLE STRICT AS $$
377 SELECT "box_p"::POLYGON
378 $$;
380 COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a box (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing';
383 CREATE FUNCTION "polyindex"
384 ( "path_p" PATH )
385 RETURNS POLYGON
386 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
387 DECLARE
388 "match_v" TEXT[];
389 "points_v" TEXT[];
390 "idx1" INTEGER;
391 "idx2" INTEGER;
392 BEGIN
393 IF isclosed("path_p") THEN
394 RETURN "path_p"::POLYGON;
395 ELSE
396 "points_v" := '{}';
397 "idx1" := 0;
398 FOR "match_v" IN
399 SELECT regexp_matches("path_p"::TEXT, E'\\(([^()]*)\\)', 'g')
400 LOOP
401 "idx1" := "idx1" + 1;
402 "points_v"["idx1"] := "match_v"[1];
403 END LOOP;
404 "idx2" := "idx1";
405 LOOP
406 EXIT WHEN "idx1" < 3;
407 "idx1" := "idx1" - 1;
408 "idx2" := "idx2" + 1;
409 "points_v"["idx2"] := "points_v"["idx1"];
410 END LOOP;
411 RETURN array_to_string("points_v", ',')::POLYGON;
412 END IF;
413 END;
414 $$;
416 COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a path (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing; NOTE: closed PATHs where isclosed(path)=TRUE represent filled polygons, use an open PATH with the last point equal to the first point (e.g. "[1,1,3,4,6,6,1,1]") for a non-filled structure';
419 CREATE FUNCTION "polyindex"
420 ( "lseg_p" LSEG )
421 RETURNS POLYGON
422 LANGUAGE sql IMMUTABLE STRICT AS $$
423 SELECT "polyindex"("lseg_p"::TEXT::PATH)
424 $$;
426 COMMENT ON FUNCTION "polyindex"(POINT) IS 'Converts a line segment (with latitude in degrees as first value and longitude in degrees as second value in each point) into a polygon suitable for GiST poly_ops indexing';
429 CREATE FUNCTION "geoshift180"
430 ( "point_p" POINT )
431 RETURNS POINT
432 LANGUAGE sql IMMUTABLE STRICT AS $$
433 SELECT CASE WHEN "point_p" <^ '0,0'::POINT THEN
434 "point_p" + '0,360'::POINT
435 ELSE
436 "point_p" - '0,360'::POINT
437 END
438 $$;
440 COMMENT ON FUNCTION "geoshift180"(POINT) IS 'Returns a point (latitude and longitude in degrees) with its longitude translated by 360 degress to allow for searches crossing the 180th meridian';
443 CREATE FUNCTION "geoshift180"
444 ( "polygon_p" POLYGON )
445 RETURNS POLYGON
446 LANGUAGE sql IMMUTABLE STRICT AS $$
447 SELECT CASE WHEN center("polygon_p"::CIRCLE) <^ '0,0'::POINT THEN
448 ("polygon_p"::PATH + '0,360'::POINT)::POLYGON
449 ELSE
450 ("polygon_p"::PATH - '0,360'::POINT)::POLYGON
451 END
452 $$;
454 COMMENT ON FUNCTION "geoshift180"(POLYGON) IS 'Returns a polygon (latitude and longitude in degrees) with its longitude translated by 360 degress to allow for searches crossing the 180th meridian';
457 CREATE FUNCTION "polysearch"
458 ( "geosearch_p" "geosearch",
459 "steps_p" INTEGER = 24 )
460 RETURNS POLYGON
461 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
462 DECLARE
463 "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90)
464 "halfsteps" INTEGER;
465 "step" INTEGER := 0;
466 "angle" FLOAT;
467 "dlat" FLOAT := "geosearch_p"."dist" / sqrt("geosearch_p"."c3");
468 "lat" FLOAT;
469 "dlon" FLOAT;
470 "coords" TEXT[];
471 BEGIN
472 IF "steps_p" < 4 OR "steps_p" % 2 != 0 THEN
473 RAISE EXCEPTION 'Invalid step count';
474 END IF;
475 "halfsteps" := "steps_p" / 2;
476 LOOP
477 "angle" := 2.0 * pi() * "step" / "steps_p";
478 "lat" := "geosearch_p"."lat" + "dlat" * cos("angle");
479 IF "lat" > "philimit" THEN "lat" := "philimit";
480 ELSIF "lat" < -"philimit" THEN "lat" := -"philimit";
481 END IF;
482 "dlon" := "dlat" * sin("angle") / sqrt(
483 ("geosearch_p"."c2" * "lat" + "geosearch_p"."c1") * "lat" +
484 "geosearch_p"."c0" );
485 "coords"[2*"step"+1] := "lat";
486 "coords"[2*"step"+2] := "geosearch_p"."lon" + "dlon";
487 EXIT WHEN "step" >= "halfsteps";
488 IF "step" > 0 THEN
489 "coords"[2*("steps_p"-"step")+1] := "lat";
490 "coords"[2*("steps_p"-"step")+2] := "geosearch_p"."lon" - "dlon";
491 END IF;
492 "step" := "step" + 1;
493 END LOOP;
494 RETURN array_to_string("coords", ',')::POLYGON;
495 END;
496 $$;
498 COMMENT ON FUNCTION "polysearch"
499 ("geosearch", INTEGER)
500 IS 'Returns a polygon approximating the search circle represented by a given "geosearch" structure; The optional second parameter denotes the number of vertices (defaults to 24)';
503 CREATE FUNCTION "polysearch"
504 ( "latitude_p" FLOAT,
505 "longitude_p" FLOAT,
506 "radius_p" FLOAT,
507 "steps_p" INTEGER = 24 )
508 RETURNS POLYGON
509 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
510 DECLARE
511 "geosearch_v" "geosearch";
512 BEGIN
513 "geosearch_v" := "geosearch"("latitude_p", "longitude_p");
514 "geosearch_v"."dist" := "radius_p";
515 RETURN "polysearch"("geosearch_v", "steps_p");
516 END;
517 $$;
519 COMMENT ON FUNCTION "polysearch"
520 ("geosearch", INTEGER)
521 IS 'Returns a polygon approximating a search circle around a point given as latitude and longitude in degrees as well as the radius in meters; The optional second parameter denotes the number of vertices (defaults to 24)';
524 /* Usage of spatial indexing for other basic geometric objects:
526 -- create table containing geographical coordinates in degrees:
527 CREATE TABLE "t2" (
528 "id" SERIAL4 PRIMARY KEY,
529 "geometry" PATH );
531 -- create a GiST (not SP-GiST!) index (with default poly_ops operator class):
532 CREATE INDEX "t2_geoindex" ON "t2" USING gist (("polyindex"("geometry")));
534 -- return results within 10,000 meters of Brandenburg Gate:
535 -- NOTE: use && operator for overlapping, use <@ operator for containment
536 SELECT "t2".*
537 FROM "t2"
538 CROSS JOIN "polysearch"(52.5162746, 13.377704, 10000) AS "poly1"
539 CROSS JOIN LATERAL "geoshift180"("poly1") AS "poly2"
540 WHERE (
541 "polyindex"("geometry") && "poly1" OR
542 "polyindex"("geometry") && "poly2" );
544 */
547 CREATE FUNCTION "scattered_polygon_contains_point"
548 ( "scattered_polygon_p" POLYGON[],
549 "point_p" POINT )
550 RETURNS BOOLEAN
551 LANGUAGE sql IMMUTABLE STRICT AS $$
552 SELECT count(1) % 2 = 1
553 FROM unnest("scattered_polygon_p") AS "entry"
554 WHERE "entry" @> "point_p"
555 $$;
557 COMMENT ON FUNCTION "scattered_polygon_contains_point"
558 ( POLYGON[], POINT )
559 IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and, as a second argument, a point; returns TRUE if the number of polygons in which the point is contained is odd';
562 CREATE FUNCTION "scattered_polygon_contains_point"
563 ( "scattered_polygon_p" POLYGON[],
564 "point_x_p" FLOAT,
565 "point_y_p" FLOAT )
566 RETURNS BOOLEAN
567 LANGUAGE sql IMMUTABLE STRICT AS $$
568 SELECT "scattered_polygon_contains_point"(
569 "scattered_polygon_p",
570 point("point_x_p", "point_y_p")
571 )
572 $$;
574 COMMENT ON FUNCTION "scattered_polygon_contains_point"
575 ( POLYGON[], FLOAT, FLOAT )
576 IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and, as second and third arguments, two coordinates representing a point; returns TRUE if the number of polygons in which the point is contained is odd';
579 CREATE FUNCTION "scattered_polygon_bound_box"
580 ( "scattered_polygon_p" POLYGON[] )
581 RETURNS BOX
582 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
583 DECLARE
584 "first_v" BOOLEAN;
585 "bound_box_v" BOX;
586 "polygon_v" POLYGON;
587 BEGIN
588 "first_v" := TRUE;
589 FOR "polygon_v" IN SELECT * FROM unnest("scattered_polygon_p") LOOP
590 IF "first_v" THEN
591 "bound_box_v" := box("polygon_v");
592 "first_v" := FALSE;
593 ELSE
594 "bound_box_v" := bound_box("bound_box_v", box("polygon_v"));
595 END IF;
596 END LOOP;
597 RETURN "bound_box_v";
598 END;
599 $$;
601 COMMENT ON FUNCTION "scattered_polygon_bound_box"
602 ( POLYGON[] )
603 IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and returns a bounding box';
606 /* Usage of spatial indexing for scattered (or hollow) polygons:
608 -- create table containing geographical coordinates in degrees:
609 CREATE TABLE "t3" (
610 "id" SERIAL4 PRIMARY KEY,
611 "region" POLYGON[] );
613 -- create a GiST (not SP-GiST!) index (with default box_ops operator class):
614 CREATE INDEX "t3_geoindex" ON "t3" USING gist (("scattered_polygon_bound_box"("region")));
616 -- return rows containing Brandenburg Gate's location:
617 SELECT "t3".*
618 FROM "t3"
619 CROSS JOIN point(52.5162746, 13.377704) AS "point1"
620 CROSS JOIN LATERAL "geoshift180"("point1") AS "point2"
621 WHERE (
622 "scattered_polygon_contains_point"("region", "point1") OR
623 "scattered_polygon_contains_point"("region", "point2") );
625 */
629 -------------------------
630 -- Tables and indicies --
631 -------------------------
634 CREATE TABLE "temporary_transaction_data" (
635 PRIMARY KEY ("txid", "key"),
636 "txid" INT8 DEFAULT txid_current(),
637 "key" TEXT,
638 "value" TEXT NOT NULL );
640 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
642 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
645 CREATE TABLE "system_setting" (
646 "member_ttl" INTERVAL );
647 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
649 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
650 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
652 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
655 CREATE TABLE "contingent" (
656 PRIMARY KEY ("polling", "time_frame"),
657 "polling" BOOLEAN,
658 "time_frame" INTERVAL,
659 "text_entry_limit" INT4,
660 "initiative_limit" INT4 );
662 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.';
664 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
665 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';
666 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
669 CREATE TABLE "member" (
670 "id" SERIAL4 PRIMARY KEY,
671 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
672 "invite_code" TEXT UNIQUE,
673 "invite_code_expiry" TIMESTAMPTZ,
674 "admin_comment" TEXT,
675 "activated" TIMESTAMPTZ,
676 "last_activity" DATE,
677 "last_login" TIMESTAMPTZ,
678 "last_delegation_check" TIMESTAMPTZ,
679 "login" TEXT UNIQUE,
680 "password" TEXT,
681 "authority" TEXT,
682 "authority_uid" TEXT,
683 "authority_login" TEXT,
684 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
685 "active" BOOLEAN NOT NULL DEFAULT FALSE,
686 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
687 "lang" TEXT,
688 "notify_email" TEXT,
689 "notify_email_unconfirmed" TEXT,
690 "notify_email_secret" TEXT UNIQUE,
691 "notify_email_secret_expiry" TIMESTAMPTZ,
692 "notify_email_lock_expiry" TIMESTAMPTZ,
693 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
694 "notification_counter" INT4 NOT NULL DEFAULT 1,
695 "notification_sample_size" INT4 NOT NULL DEFAULT 3,
696 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6),
697 "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23),
698 "notification_sent" TIMESTAMP,
699 "login_recovery_expiry" TIMESTAMPTZ,
700 "password_reset_secret" TEXT UNIQUE,
701 "password_reset_secret_expiry" TIMESTAMPTZ,
702 "name" TEXT UNIQUE,
703 "identification" TEXT UNIQUE,
704 "authentication" TEXT,
705 "organizational_unit" TEXT,
706 "internal_posts" TEXT,
707 "realname" TEXT,
708 "birthday" DATE,
709 "address" TEXT,
710 "email" TEXT,
711 "xmpp_address" TEXT,
712 "website" TEXT,
713 "phone" TEXT,
714 "mobile_phone" TEXT,
715 "profession" TEXT,
716 "external_memberships" TEXT,
717 "external_posts" TEXT,
718 "formatting_engine" TEXT,
719 "statement" TEXT,
720 "latitude" NUMERIC,
721 "longitude" NUMERIC,
722 "text_search_data" TSVECTOR,
723 CONSTRAINT "active_requires_activated_and_last_activity"
724 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
725 CONSTRAINT "authority_requires_uid_and_vice_versa"
726 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
727 CONSTRAINT "authority_uid_unique_per_authority"
728 UNIQUE ("authority", "authority_uid"),
729 CONSTRAINT "authority_login_requires_authority"
730 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
731 CONSTRAINT "notification_dow_requires_notification_hour"
732 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL),
733 CONSTRAINT "name_not_null_if_activated"
734 CHECK ("activated" ISNULL OR "name" NOTNULL),
735 CONSTRAINT "latitude_and_lontitude_both_null_or_not_null"
736 CHECK (("latitude" NOTNULL) = ("longitude" NOTNULL)) );
737 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
738 CREATE INDEX "member_active_idx" ON "member" ("active");
739 CREATE INDEX "member_geolocation_idx" ON "member" USING spgist (("geoindex"("latitude", "longitude")));
740 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
741 CREATE TRIGGER "update_text_search_data"
742 BEFORE INSERT OR UPDATE ON "member"
743 FOR EACH ROW EXECUTE PROCEDURE
744 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
745 "name", "identification", "organizational_unit", "internal_posts",
746 "realname", "external_memberships", "external_posts", "statement" );
748 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
750 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
751 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
752 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
753 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
754 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
755 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
756 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
757 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
758 COMMENT ON COLUMN "member"."login" IS 'Login name';
759 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
760 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
761 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
762 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
763 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
764 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
765 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
766 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
767 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
768 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
769 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
770 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
771 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
772 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
773 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
774 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
775 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
776 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';
777 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';
778 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
779 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
780 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
781 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
782 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
783 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
784 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
785 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
786 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
787 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
788 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
789 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
790 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
791 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
792 COMMENT ON COLUMN "member"."latitude" IS 'Latitude (in degrees) of member''s location';
793 COMMENT ON COLUMN "member"."longitude" IS 'Longitude (in degrees) of member''s location';
796 CREATE TABLE "member_history" (
797 "id" SERIAL8 PRIMARY KEY,
798 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
799 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
800 "active" BOOLEAN NOT NULL,
801 "name" TEXT NOT NULL );
802 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
804 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
806 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
807 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
810 CREATE TABLE "rendered_member_statement" (
811 PRIMARY KEY ("member_id", "format"),
812 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
813 "format" TEXT,
814 "content" TEXT NOT NULL );
816 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
819 CREATE TABLE "setting" (
820 PRIMARY KEY ("member_id", "key"),
821 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
822 "key" TEXT NOT NULL,
823 "value" TEXT NOT NULL );
824 CREATE INDEX "setting_key_idx" ON "setting" ("key");
826 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
828 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
831 CREATE TABLE "setting_map" (
832 PRIMARY KEY ("member_id", "key", "subkey"),
833 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
834 "key" TEXT NOT NULL,
835 "subkey" TEXT NOT NULL,
836 "value" TEXT NOT NULL );
837 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
839 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
841 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
842 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
843 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
846 CREATE TABLE "member_relation_setting" (
847 PRIMARY KEY ("member_id", "key", "other_member_id"),
848 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
849 "key" TEXT NOT NULL,
850 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
851 "value" TEXT NOT NULL );
853 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
856 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
858 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
861 CREATE TABLE "member_image" (
862 PRIMARY KEY ("member_id", "image_type", "scaled"),
863 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
864 "image_type" "member_image_type",
865 "scaled" BOOLEAN,
866 "content_type" TEXT,
867 "data" BYTEA NOT NULL );
869 COMMENT ON TABLE "member_image" IS 'Images of members';
871 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
874 CREATE TABLE "member_count" (
875 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
876 "total_count" INT4 NOT NULL );
878 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';
880 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
881 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
884 CREATE TABLE "contact" (
885 PRIMARY KEY ("member_id", "other_member_id"),
886 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
887 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
888 "public" BOOLEAN NOT NULL DEFAULT FALSE,
889 CONSTRAINT "cant_save_yourself_as_contact"
890 CHECK ("member_id" != "other_member_id") );
891 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
893 COMMENT ON TABLE "contact" IS 'Contact lists';
895 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
896 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
897 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
900 CREATE TABLE "ignored_member" (
901 PRIMARY KEY ("member_id", "other_member_id"),
902 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
903 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
904 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
906 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
908 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
909 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
912 CREATE TABLE "session" (
913 "ident" TEXT PRIMARY KEY,
914 "additional_secret" TEXT,
915 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
916 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
917 "authority" TEXT,
918 "authority_uid" TEXT,
919 "authority_login" TEXT,
920 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
921 "lang" TEXT );
922 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
924 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
926 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
927 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
928 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
929 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
930 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
931 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
932 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
933 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
936 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
938 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
941 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
943 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
946 CREATE TABLE "policy" (
947 "id" SERIAL4 PRIMARY KEY,
948 "index" INT4 NOT NULL,
949 "active" BOOLEAN NOT NULL DEFAULT TRUE,
950 "name" TEXT NOT NULL UNIQUE,
951 "description" TEXT NOT NULL DEFAULT '',
952 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
953 "min_admission_time" INTERVAL,
954 "max_admission_time" INTERVAL,
955 "discussion_time" INTERVAL,
956 "verification_time" INTERVAL,
957 "voting_time" INTERVAL,
958 "issue_quorum" INT4 NOT NULL,
959 "initiative_quorum_num" INT4 NOT NULL,
960 "initiative_quorum_den" INT4 NOT NULL,
961 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
962 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
963 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
964 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
965 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
966 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
967 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
968 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
969 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
970 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
971 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
972 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
973 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
974 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
975 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
976 "polling" = ("issue_quorum" ISNULL) ),
977 CONSTRAINT "min_admission_time_smaller_than_max_admission_time" CHECK (
978 "min_admission_time" < "max_admission_time" ),
979 CONSTRAINT "timing_null_or_not_null_constraints" CHECK (
980 ( "polling" = FALSE AND
981 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
982 "discussion_time" NOTNULL AND
983 "verification_time" NOTNULL AND
984 "voting_time" NOTNULL ) OR
985 ( "polling" = TRUE AND
986 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
987 "discussion_time" NOTNULL AND
988 "verification_time" NOTNULL AND
989 "voting_time" NOTNULL ) OR
990 ( "polling" = TRUE AND
991 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
992 "discussion_time" ISNULL AND
993 "verification_time" ISNULL AND
994 "voting_time" ISNULL ) ),
995 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
996 "defeat_strength" = 'tuple'::"defeat_strength" OR
997 "no_reverse_beat_path" = FALSE ) );
998 CREATE INDEX "policy_active_idx" ON "policy" ("active");
1000 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
1002 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
1003 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
1004 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
1005 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open; Note: should be considerably smaller than "max_admission_time"';
1006 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
1007 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
1008 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
1009 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
1010 COMMENT ON COLUMN "policy"."issue_quorum" IS 'Minimum number of supporters needed for one initiative of an issue to allow the issue to pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see tables "admission_rule" and "admission_rule_condition")';
1011 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
1012 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
1013 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
1014 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
1015 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
1016 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
1017 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';
1018 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
1019 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';
1020 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
1021 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
1022 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';
1023 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';
1024 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';
1025 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
1026 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
1029 CREATE TABLE "unit" (
1030 "id" SERIAL4 PRIMARY KEY,
1031 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1032 "active" BOOLEAN NOT NULL DEFAULT TRUE,
1033 "name" TEXT NOT NULL,
1034 "description" TEXT NOT NULL DEFAULT '',
1035 "external_reference" TEXT,
1036 "member_count" INT4,
1037 "region" POLYGON[],
1038 "text_search_data" TSVECTOR );
1039 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
1040 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
1041 CREATE INDEX "unit_active_idx" ON "unit" ("active");
1042 CREATE INDEX "unit_geolocation_idx" ON "unit" USING gist (("scattered_polygon_bound_box"("region")));
1043 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
1044 CREATE TRIGGER "update_text_search_data"
1045 BEFORE INSERT OR UPDATE ON "unit"
1046 FOR EACH ROW EXECUTE PROCEDURE
1047 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
1048 "name", "description" );
1050 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
1052 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
1053 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
1054 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
1055 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
1056 COMMENT ON COLUMN "unit"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
1059 CREATE TABLE "subscription" (
1060 PRIMARY KEY ("member_id", "unit_id"),
1061 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1062 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1063 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
1065 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
1068 CREATE TABLE "unit_setting" (
1069 PRIMARY KEY ("member_id", "key", "unit_id"),
1070 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1071 "key" TEXT NOT NULL,
1072 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1073 "value" TEXT NOT NULL );
1075 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
1078 CREATE TABLE "area" (
1079 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition"
1080 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1081 "id" SERIAL4 PRIMARY KEY,
1082 "active" BOOLEAN NOT NULL DEFAULT TRUE,
1083 "name" TEXT NOT NULL,
1084 "description" TEXT NOT NULL DEFAULT '',
1085 "external_reference" TEXT,
1086 "region" POLYGON[],
1087 "text_search_data" TSVECTOR );
1088 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
1089 CREATE INDEX "area_active_idx" ON "area" ("active");
1090 CREATE INDEX "area_geolocation_idx" ON "area" USING gist (("scattered_polygon_bound_box"("region")));
1091 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
1092 CREATE TRIGGER "update_text_search_data"
1093 BEFORE INSERT OR UPDATE ON "area"
1094 FOR EACH ROW EXECUTE PROCEDURE
1095 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
1096 "name", "description" );
1098 COMMENT ON TABLE "area" IS 'Subject areas';
1100 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
1101 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
1102 COMMENT ON COLUMN "area"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
1105 CREATE TABLE "ignored_area" (
1106 PRIMARY KEY ("member_id", "area_id"),
1107 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1108 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1109 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
1111 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
1114 CREATE TABLE "area_setting" (
1115 PRIMARY KEY ("member_id", "key", "area_id"),
1116 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1117 "key" TEXT NOT NULL,
1118 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1119 "value" TEXT NOT NULL );
1121 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
1124 CREATE TABLE "allowed_policy" (
1125 PRIMARY KEY ("area_id", "policy_id"),
1126 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1127 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1128 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
1129 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
1131 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
1133 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
1136 CREATE TABLE "admission_rule" (
1137 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition"
1138 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1139 "id" SERIAL4 PRIMARY KEY,
1140 "name" TEXT NOT NULL,
1141 "description" TEXT NOT NULL DEFAULT '' );
1143 COMMENT ON TABLE "admission_rule" IS 'Groups entries in "admission_rule_condition" to regulate how many issues may pass from ''admission'' to ''discussion'' state in a given time';
1146 CREATE TABLE "admission_rule_condition" (
1147 "unit_id" INT4 NOT NULL,
1148 "admission_rule_id" INT4,
1149 FOREIGN KEY ("unit_id", "admission_rule_id") REFERENCES "admission_rule" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1150 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1151 "area_id" INT4,
1152 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1153 "holdoff_time" INTERVAL NOT NULL );
1154 CREATE UNIQUE INDEX "admission_rule_condition_unit_idx" ON "admission_rule_condition" ("admission_rule_id") WHERE "policy_id" ISNULL AND "area_id" ISNULL;
1155 CREATE UNIQUE INDEX "admission_rule_condition_policy_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id") WHERE "area_id" ISNULL;
1156 CREATE UNIQUE INDEX "admission_rule_condition_area_idx" ON "admission_rule_condition" ("admission_rule_id", "area_id") WHERE "policy_id" ISNULL;
1157 CREATE UNIQUE INDEX "admission_rule_condition_policy_area_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id", "area_id");
1159 COMMENT ON TABLE "admission_rule_condition" IS 'Regulates how many issues may pass from ''admission'' to ''discussion'' state in a given time; See definition of "issue_for_admission" view for details';
1161 COMMENT ON COLUMN "admission_rule_condition"."unit_id" IS 'Grouped "admission_rule_condition" rows must have the same "unit_id"';
1162 COMMENT ON COLUMN "admission_rule_condition"."admission_rule_id" IS 'Grouping several "admission_rule_condition" rows';
1163 COMMENT ON COLUMN "admission_rule_condition"."policy_id" IS 'Set to link the condition with a given policy, NULL for any policy in the issue';
1164 COMMENT ON COLUMN "admission_rule_condition"."area_id" IS 'Set to link the condition with a given policy, NULL for any area in the issue';
1165 COMMENT ON COLUMN "admission_rule_condition"."holdoff_time" IS 'After an issue in the given unit, policy, and/or area has been admitted, the "admission_rule" is disabled for the selected "holdoff_time", e.g. a "holdoff_time" of ''6 hours'' causes four issues per day to be admitted';
1168 CREATE TABLE "snapshot" (
1169 "id" SERIAL8 PRIMARY KEY,
1170 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now() );
1172 COMMENT ON TABLE "snapshot" IS 'Point in time when a snapshot of one or more issues (see table "snapshot_issue") and their supporter situation is taken';
1175 CREATE TYPE "issue_state" AS ENUM (
1176 'admission', 'discussion', 'verification', 'voting',
1177 'canceled_by_admin',
1178 'canceled_revoked_before_accepted',
1179 'canceled_issue_not_accepted',
1180 'canceled_after_revocation_during_discussion',
1181 'canceled_after_revocation_during_verification',
1182 'canceled_no_initiative_admitted',
1183 'finished_without_winner', 'finished_with_winner');
1185 COMMENT ON TYPE "issue_state" IS 'State of issues';
1188 CREATE TABLE "issue" (
1189 "id" SERIAL4 PRIMARY KEY,
1190 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1191 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1192 "admin_notice" TEXT,
1193 "external_reference" TEXT,
1194 "state" "issue_state" NOT NULL DEFAULT 'admission',
1195 "phase_finished" TIMESTAMPTZ,
1196 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
1197 "accepted" TIMESTAMPTZ,
1198 "half_frozen" TIMESTAMPTZ,
1199 "fully_frozen" TIMESTAMPTZ,
1200 "closed" TIMESTAMPTZ,
1201 "cleaned" TIMESTAMPTZ,
1202 "min_admission_time" INTERVAL,
1203 "max_admission_time" INTERVAL,
1204 "discussion_time" INTERVAL NOT NULL,
1205 "verification_time" INTERVAL NOT NULL,
1206 "voting_time" INTERVAL NOT NULL,
1207 "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1208 "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
1209 "half_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1210 "full_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1211 "population" INT4,
1212 "voter_count" INT4,
1213 "status_quo_schulze_rank" INT4,
1214 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
1215 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
1216 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
1217 CONSTRAINT "valid_state" CHECK (
1218 (
1219 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
1220 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
1221 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
1222 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
1223 ) AND (
1224 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
1225 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
1226 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
1227 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
1228 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
1229 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
1230 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
1231 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
1232 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
1233 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
1234 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
1235 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
1236 )),
1237 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
1238 "phase_finished" ISNULL OR "closed" ISNULL ),
1239 CONSTRAINT "state_change_order" CHECK (
1240 "created" <= "accepted" AND
1241 "accepted" <= "half_frozen" AND
1242 "half_frozen" <= "fully_frozen" AND
1243 "fully_frozen" <= "closed" ),
1244 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
1245 "cleaned" ISNULL OR "closed" NOTNULL ),
1246 CONSTRAINT "snapshot_required" CHECK (
1247 --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND
1248 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
1249 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) );
1250 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
1251 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
1252 CREATE INDEX "issue_state_idx" ON "issue" ("state");
1253 CREATE INDEX "issue_created_idx" ON "issue" ("created");
1254 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
1255 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
1256 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
1257 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
1258 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
1259 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
1260 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
1261 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
1262 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
1263 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
1265 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
1267 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
1268 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
1269 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
1270 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when the issue was accepted for further discussion (see table "admission_rule" and column "issue_quorum" of table "policy")';
1271 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
1272 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.';
1273 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_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.';
1274 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
1275 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
1276 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
1277 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
1278 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
1279 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
1280 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
1281 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
1282 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
1283 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
1284 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
1285 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';
1286 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
1289 CREATE TABLE "issue_order_in_admission_state" (
1290 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1291 "order_in_area" INT4,
1292 "order_in_unit" INT4 );
1294 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
1296 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
1297 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
1298 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
1301 CREATE TABLE "issue_setting" (
1302 PRIMARY KEY ("member_id", "key", "issue_id"),
1303 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1304 "key" TEXT NOT NULL,
1305 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1306 "value" TEXT NOT NULL );
1308 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
1311 CREATE TABLE "initiative" (
1312 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
1313 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1314 "id" SERIAL4 PRIMARY KEY,
1315 "name" TEXT NOT NULL,
1316 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
1317 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
1318 "revoked" TIMESTAMPTZ,
1319 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1320 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1321 "latitude1" NUMERIC,
1322 "longitude1" NUMERIC,
1323 "latitude2" NUMERIC,
1324 "longitude2" NUMERIC,
1325 "external_reference" TEXT,
1326 "admitted" BOOLEAN,
1327 "supporter_count" INT4,
1328 "informed_supporter_count" INT4,
1329 "satisfied_supporter_count" INT4,
1330 "satisfied_informed_supporter_count" INT4,
1331 "harmonic_weight" NUMERIC(12, 3),
1332 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
1333 "first_preference_votes" INT4,
1334 "positive_votes" INT4,
1335 "negative_votes" INT4,
1336 "direct_majority" BOOLEAN,
1337 "indirect_majority" BOOLEAN,
1338 "schulze_rank" INT4,
1339 "better_than_status_quo" BOOLEAN,
1340 "worse_than_status_quo" BOOLEAN,
1341 "reverse_beat_path" BOOLEAN,
1342 "multistage_majority" BOOLEAN,
1343 "eligible" BOOLEAN,
1344 "winner" BOOLEAN,
1345 "rank" INT4,
1346 "text_search_data" TSVECTOR,
1347 "draft_text_search_data" TSVECTOR,
1348 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
1349 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
1350 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
1351 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
1352 CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null"
1353 CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)),
1354 CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null"
1355 CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)),
1356 CONSTRAINT "latutude2_requires_latitude1"
1357 CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL),
1358 CONSTRAINT "revoked_initiatives_cant_be_admitted"
1359 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
1360 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
1361 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
1362 ( "first_preference_votes" ISNULL AND
1363 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
1364 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
1365 "schulze_rank" ISNULL AND
1366 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
1367 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
1368 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
1369 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
1370 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
1371 "eligible" = FALSE OR
1372 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
1373 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
1374 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
1375 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
1376 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
1377 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
1378 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
1379 CREATE INDEX "initiative_geolocation1_idx" ON "initiative" USING spgist (("geoindex"("latitude1", "longitude1")));
1380 CREATE INDEX "initiative_geolocation2_idx" ON "initiative" USING spgist (("geoindex"("latitude2", "longitude2")));
1381 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
1382 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
1383 CREATE TRIGGER "update_text_search_data"
1384 BEFORE INSERT OR UPDATE ON "initiative"
1385 FOR EACH ROW EXECUTE PROCEDURE
1386 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
1388 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.';
1390 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
1391 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
1392 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
1393 COMMENT ON COLUMN "initiative"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied from most recent draft)';
1394 COMMENT ON COLUMN "initiative"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied from most recent draft)';
1395 COMMENT ON COLUMN "initiative"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)';
1396 COMMENT ON COLUMN "initiative"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)';
1397 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
1398 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
1399 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
1400 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
1401 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
1402 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
1403 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
1404 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
1405 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
1406 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
1407 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
1408 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
1409 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
1410 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
1411 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
1412 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
1413 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
1414 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
1415 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
1416 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
1417 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
1420 CREATE TABLE "battle" (
1421 "issue_id" INT4 NOT NULL,
1422 "winning_initiative_id" INT4,
1423 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1424 "losing_initiative_id" INT4,
1425 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1426 "count" INT4 NOT NULL,
1427 CONSTRAINT "initiative_ids_not_equal" CHECK (
1428 "winning_initiative_id" != "losing_initiative_id" OR
1429 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
1430 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
1431 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
1432 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
1433 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
1435 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';
1438 CREATE TABLE "ignored_initiative" (
1439 PRIMARY KEY ("member_id", "initiative_id"),
1440 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1441 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1442 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
1444 COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
1447 CREATE TABLE "initiative_setting" (
1448 PRIMARY KEY ("member_id", "key", "initiative_id"),
1449 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1450 "key" TEXT NOT NULL,
1451 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1452 "value" TEXT NOT NULL );
1454 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
1457 CREATE TABLE "draft" (
1458 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
1459 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1460 "id" SERIAL8 PRIMARY KEY,
1461 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
1462 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1463 "formatting_engine" TEXT,
1464 "content" TEXT NOT NULL,
1465 "latitude1" NUMERIC,
1466 "longitude1" NUMERIC,
1467 "latitude2" NUMERIC,
1468 "longitude2" NUMERIC,
1469 "external_reference" TEXT,
1470 "text_search_data" TSVECTOR,
1471 CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null"
1472 CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)),
1473 CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null"
1474 CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)),
1475 CONSTRAINT "latutude2_requires_latitude1"
1476 CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) );
1477 CREATE INDEX "draft_created_idx" ON "draft" ("created");
1478 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
1479 CREATE INDEX "draft_geolocation1_idx" ON "draft" USING spgist (("geoindex"("latitude1", "longitude1")));
1480 CREATE INDEX "draft_geolocation2_idx" ON "draft" USING spgist (("geoindex"("latitude2", "longitude2")));
1481 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
1482 CREATE TRIGGER "update_text_search_data"
1483 BEFORE INSERT OR UPDATE ON "draft"
1484 FOR EACH ROW EXECUTE PROCEDURE
1485 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1487 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.';
1489 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
1490 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
1491 COMMENT ON COLUMN "draft"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)';
1492 COMMENT ON COLUMN "draft"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)';
1493 COMMENT ON COLUMN "draft"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)';
1494 COMMENT ON COLUMN "draft"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)';
1495 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
1498 CREATE TABLE "rendered_draft" (
1499 PRIMARY KEY ("draft_id", "format"),
1500 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1501 "format" TEXT,
1502 "content" TEXT NOT NULL );
1504 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)';
1507 CREATE TABLE "suggestion" (
1508 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
1509 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1510 "id" SERIAL8 PRIMARY KEY,
1511 "draft_id" INT8 NOT NULL,
1512 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1513 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
1514 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1515 "name" TEXT NOT NULL,
1516 "formatting_engine" TEXT,
1517 "content" TEXT NOT NULL DEFAULT '',
1518 "latitude1" NUMERIC,
1519 "longitude1" NUMERIC,
1520 "latitude2" NUMERIC,
1521 "longitude2" NUMERIC,
1522 "external_reference" TEXT,
1523 "text_search_data" TSVECTOR,
1524 "minus2_unfulfilled_count" INT4,
1525 "minus2_fulfilled_count" INT4,
1526 "minus1_unfulfilled_count" INT4,
1527 "minus1_fulfilled_count" INT4,
1528 "plus1_unfulfilled_count" INT4,
1529 "plus1_fulfilled_count" INT4,
1530 "plus2_unfulfilled_count" INT4,
1531 "plus2_fulfilled_count" INT4,
1532 "proportional_order" INT4,
1533 CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null"
1534 CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)),
1535 CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null"
1536 CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)),
1537 CONSTRAINT "latutude2_requires_latitude1"
1538 CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) );
1539 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
1540 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
1541 CREATE INDEX "suggestion_geolocation1_idx" ON "suggestion" USING spgist (("geoindex"("latitude1", "longitude1")));
1542 CREATE INDEX "suggestion_geolocation2_idx" ON "suggestion" USING spgist (("geoindex"("latitude2", "longitude2")));
1543 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
1544 CREATE TRIGGER "update_text_search_data"
1545 BEFORE INSERT OR UPDATE ON "suggestion"
1546 FOR EACH ROW EXECUTE PROCEDURE
1547 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
1548 "name", "content");
1550 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';
1552 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
1553 COMMENT ON COLUMN "suggestion"."latitude1" IS 'Latitude (in degrees) of suggestion';
1554 COMMENT ON COLUMN "suggestion"."longitude1" IS 'Longitude (in degrees) of suggestion';
1555 COMMENT ON COLUMN "suggestion"."latitude2" IS 'Latitude (in degrees) of suggestion''s second marker';
1556 COMMENT ON COLUMN "suggestion"."longitude2" IS 'Longitude (in degrees) of suggestion''s second marker';
1557 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
1558 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1559 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1560 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1561 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1562 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1563 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1564 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1565 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1566 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';
1569 CREATE TABLE "rendered_suggestion" (
1570 PRIMARY KEY ("suggestion_id", "format"),
1571 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1572 "format" TEXT,
1573 "content" TEXT NOT NULL );
1575 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
1578 CREATE TABLE "suggestion_setting" (
1579 PRIMARY KEY ("member_id", "key", "suggestion_id"),
1580 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1581 "key" TEXT NOT NULL,
1582 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1583 "value" TEXT NOT NULL );
1585 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
1588 CREATE TABLE "temporary_suggestion_counts" (
1589 "id" INT8 PRIMARY KEY, --REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1590 "minus2_unfulfilled_count" INT4 NOT NULL,
1591 "minus2_fulfilled_count" INT4 NOT NULL,
1592 "minus1_unfulfilled_count" INT4 NOT NULL,
1593 "minus1_fulfilled_count" INT4 NOT NULL,
1594 "plus1_unfulfilled_count" INT4 NOT NULL,
1595 "plus1_fulfilled_count" INT4 NOT NULL,
1596 "plus2_unfulfilled_count" INT4 NOT NULL,
1597 "plus2_fulfilled_count" INT4 NOT NULL );
1599 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
1601 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
1604 CREATE TABLE "privilege" (
1605 PRIMARY KEY ("unit_id", "member_id"),
1606 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1607 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1608 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1609 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1610 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1611 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1612 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
1613 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
1614 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
1616 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
1618 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
1619 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
1620 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
1621 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
1622 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
1623 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
1624 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
1627 CREATE TABLE "interest" (
1628 PRIMARY KEY ("issue_id", "member_id"),
1629 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1630 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1631 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
1633 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.';
1636 CREATE TABLE "initiator" (
1637 PRIMARY KEY ("initiative_id", "member_id"),
1638 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1639 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1640 "accepted" BOOLEAN );
1641 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
1643 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.';
1645 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.';
1648 CREATE TABLE "supporter" (
1649 "issue_id" INT4 NOT NULL,
1650 PRIMARY KEY ("initiative_id", "member_id"),
1651 "initiative_id" INT4,
1652 "member_id" INT4,
1653 "draft_id" INT8 NOT NULL,
1654 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1655 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
1656 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
1658 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.';
1660 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1661 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
1664 CREATE TABLE "opinion" (
1665 "initiative_id" INT4 NOT NULL,
1666 PRIMARY KEY ("suggestion_id", "member_id"),
1667 "suggestion_id" INT8,
1668 "member_id" INT4,
1669 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
1670 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
1671 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1672 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1673 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
1675 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.';
1677 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
1680 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
1682 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1685 CREATE TABLE "delegation" (
1686 "id" SERIAL8 PRIMARY KEY,
1687 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1688 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1689 "scope" "delegation_scope" NOT NULL,
1690 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1691 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1692 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1693 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1694 CONSTRAINT "no_unit_delegation_to_null"
1695 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1696 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1697 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1698 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1699 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1700 UNIQUE ("unit_id", "truster_id"),
1701 UNIQUE ("area_id", "truster_id"),
1702 UNIQUE ("issue_id", "truster_id") );
1703 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1704 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1706 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1708 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1709 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1710 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1713 CREATE TABLE "snapshot_issue" (
1714 PRIMARY KEY ("snapshot_id", "issue_id"),
1715 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1716 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1717 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
1719 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
1722 CREATE TABLE "direct_interest_snapshot" (
1723 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1724 "snapshot_id" INT8,
1725 "issue_id" INT4,
1726 FOREIGN KEY ("snapshot_id", "issue_id")
1727 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1728 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1729 "weight" INT4 );
1730 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1732 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
1734 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1737 CREATE TABLE "delegating_interest_snapshot" (
1738 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1739 "snapshot_id" INT8,
1740 "issue_id" INT4,
1741 FOREIGN KEY ("snapshot_id", "issue_id")
1742 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1743 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1744 "weight" INT4,
1745 "scope" "delegation_scope" NOT NULL,
1746 "delegate_member_ids" INT4[] NOT NULL );
1747 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1749 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
1751 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1752 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1753 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"';
1756 CREATE TABLE "direct_supporter_snapshot" (
1757 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
1758 "snapshot_id" INT8,
1759 "issue_id" INT4 NOT NULL,
1760 FOREIGN KEY ("snapshot_id", "issue_id")
1761 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1762 "initiative_id" INT4,
1763 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1764 "draft_id" INT8 NOT NULL,
1765 "informed" BOOLEAN NOT NULL,
1766 "satisfied" BOOLEAN NOT NULL,
1767 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1768 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1769 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1770 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1772 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';
1774 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1775 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1776 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1779 CREATE TABLE "non_voter" (
1780 PRIMARY KEY ("member_id", "issue_id"),
1781 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1782 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1783 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
1785 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1788 CREATE TABLE "direct_voter" (
1789 PRIMARY KEY ("issue_id", "member_id"),
1790 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1791 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1792 "weight" INT4,
1793 "comment_changed" TIMESTAMPTZ,
1794 "formatting_engine" TEXT,
1795 "comment" TEXT,
1796 "text_search_data" TSVECTOR );
1797 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1798 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1799 CREATE TRIGGER "update_text_search_data"
1800 BEFORE INSERT OR UPDATE ON "direct_voter"
1801 FOR EACH ROW EXECUTE PROCEDURE
1802 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1804 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; for corrections refer to column "issue_notice" of "issue" table';
1806 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1807 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
1808 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
1809 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
1812 CREATE TABLE "rendered_voter_comment" (
1813 PRIMARY KEY ("issue_id", "member_id", "format"),
1814 FOREIGN KEY ("issue_id", "member_id")
1815 REFERENCES "direct_voter" ("issue_id", "member_id")
1816 ON DELETE CASCADE ON UPDATE CASCADE,
1817 "issue_id" INT4,
1818 "member_id" INT4,
1819 "format" TEXT,
1820 "content" TEXT NOT NULL );
1822 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
1825 CREATE TABLE "delegating_voter" (
1826 PRIMARY KEY ("issue_id", "member_id"),
1827 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1828 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1829 "weight" INT4,
1830 "scope" "delegation_scope" NOT NULL,
1831 "delegate_member_ids" INT4[] NOT NULL );
1832 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1834 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table';
1836 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1837 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1838 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"';
1841 CREATE TABLE "vote" (
1842 "issue_id" INT4 NOT NULL,
1843 PRIMARY KEY ("initiative_id", "member_id"),
1844 "initiative_id" INT4,
1845 "member_id" INT4,
1846 "grade" INT4 NOT NULL,
1847 "first_preference" BOOLEAN,
1848 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1849 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1850 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1851 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1852 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1854 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; for corrections refer to column "issue_notice" of "issue" table';
1856 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1857 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.';
1858 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
1861 CREATE TYPE "event_type" AS ENUM (
1862 'issue_state_changed',
1863 'initiative_created_in_new_issue',
1864 'initiative_created_in_existing_issue',
1865 'initiative_revoked',
1866 'new_draft_created',
1867 'suggestion_created');
1869 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1872 CREATE TABLE "event" (
1873 "id" SERIAL8 PRIMARY KEY,
1874 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1875 "event" "event_type" NOT NULL,
1876 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1877 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1878 "state" "issue_state",
1879 "initiative_id" INT4,
1880 "draft_id" INT8,
1881 "suggestion_id" INT8,
1882 FOREIGN KEY ("issue_id", "initiative_id")
1883 REFERENCES "initiative" ("issue_id", "id")
1884 ON DELETE CASCADE ON UPDATE CASCADE,
1885 FOREIGN KEY ("initiative_id", "draft_id")
1886 REFERENCES "draft" ("initiative_id", "id")
1887 ON DELETE CASCADE ON UPDATE CASCADE,
1888 FOREIGN KEY ("initiative_id", "suggestion_id")
1889 REFERENCES "suggestion" ("initiative_id", "id")
1890 ON DELETE CASCADE ON UPDATE CASCADE,
1891 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1892 "event" != 'issue_state_changed' OR (
1893 "member_id" ISNULL AND
1894 "issue_id" NOTNULL AND
1895 "state" NOTNULL AND
1896 "initiative_id" ISNULL AND
1897 "draft_id" ISNULL AND
1898 "suggestion_id" ISNULL )),
1899 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1900 "event" NOT IN (
1901 'initiative_created_in_new_issue',
1902 'initiative_created_in_existing_issue',
1903 'initiative_revoked',
1904 'new_draft_created'
1905 ) OR (
1906 "member_id" NOTNULL AND
1907 "issue_id" NOTNULL AND
1908 "state" NOTNULL AND
1909 "initiative_id" NOTNULL AND
1910 "draft_id" NOTNULL AND
1911 "suggestion_id" ISNULL )),
1912 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1913 "event" != 'suggestion_created' OR (
1914 "member_id" NOTNULL AND
1915 "issue_id" NOTNULL AND
1916 "state" NOTNULL AND
1917 "initiative_id" NOTNULL AND
1918 "draft_id" ISNULL AND
1919 "suggestion_id" NOTNULL )) );
1920 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1922 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1924 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1925 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1926 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1927 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1930 CREATE TABLE "notification_event_sent" (
1931 "event_id" INT8 NOT NULL );
1932 CREATE UNIQUE INDEX "notification_event_sent_singleton_idx" ON "notification_event_sent" ((1));
1934 COMMENT ON TABLE "notification_event_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1935 COMMENT ON INDEX "notification_event_sent_singleton_idx" IS 'This index ensures that "notification_event_sent" only contains one row maximum.';
1938 CREATE TABLE "notification_initiative_sent" (
1939 PRIMARY KEY ("member_id", "initiative_id"),
1940 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1941 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1942 "last_draft_id" INT8 NOT NULL,
1943 "last_suggestion_id" INT8 );
1944 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
1946 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
1948 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1949 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1952 CREATE TABLE "newsletter" (
1953 "id" SERIAL4 PRIMARY KEY,
1954 "published" TIMESTAMPTZ NOT NULL,
1955 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1956 "include_all_members" BOOLEAN NOT NULL,
1957 "sent" TIMESTAMPTZ,
1958 "subject" TEXT NOT NULL,
1959 "content" TEXT NOT NULL );
1960 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1961 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1962 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1964 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
1966 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
1967 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
1968 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
1969 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
1970 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
1971 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
1975 ----------------------------------------------
1976 -- Writing of history entries and event log --
1977 ----------------------------------------------
1980 CREATE FUNCTION "write_member_history_trigger"()
1981 RETURNS TRIGGER
1982 LANGUAGE 'plpgsql' VOLATILE AS $$
1983 BEGIN
1984 IF
1985 ( NEW."active" != OLD."active" OR
1986 NEW."name" != OLD."name" ) AND
1987 OLD."activated" NOTNULL
1988 THEN
1989 INSERT INTO "member_history"
1990 ("member_id", "active", "name")
1991 VALUES (NEW."id", OLD."active", OLD."name");
1992 END IF;
1993 RETURN NULL;
1994 END;
1995 $$;
1997 CREATE TRIGGER "write_member_history"
1998 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1999 "write_member_history_trigger"();
2001 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
2002 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
2005 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
2006 RETURNS TRIGGER
2007 LANGUAGE 'plpgsql' VOLATILE AS $$
2008 BEGIN
2009 IF NEW."state" != OLD."state" THEN
2010 INSERT INTO "event" ("event", "issue_id", "state")
2011 VALUES ('issue_state_changed', NEW."id", NEW."state");
2012 END IF;
2013 RETURN NULL;
2014 END;
2015 $$;
2017 CREATE TRIGGER "write_event_issue_state_changed"
2018 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
2019 "write_event_issue_state_changed_trigger"();
2021 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
2022 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
2025 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
2026 RETURNS TRIGGER
2027 LANGUAGE 'plpgsql' VOLATILE AS $$
2028 DECLARE
2029 "initiative_row" "initiative"%ROWTYPE;
2030 "issue_row" "issue"%ROWTYPE;
2031 "event_v" "event_type";
2032 BEGIN
2033 SELECT * INTO "initiative_row" FROM "initiative"
2034 WHERE "id" = NEW."initiative_id";
2035 SELECT * INTO "issue_row" FROM "issue"
2036 WHERE "id" = "initiative_row"."issue_id";
2037 IF EXISTS (
2038 SELECT NULL FROM "draft"
2039 WHERE "initiative_id" = NEW."initiative_id"
2040 AND "id" != NEW."id"
2041 ) THEN
2042 "event_v" := 'new_draft_created';
2043 ELSE
2044 IF EXISTS (
2045 SELECT NULL FROM "initiative"
2046 WHERE "issue_id" = "initiative_row"."issue_id"
2047 AND "id" != "initiative_row"."id"
2048 ) THEN
2049 "event_v" := 'initiative_created_in_existing_issue';
2050 ELSE
2051 "event_v" := 'initiative_created_in_new_issue';
2052 END IF;
2053 END IF;
2054 INSERT INTO "event" (
2055 "event", "member_id",
2056 "issue_id", "state", "initiative_id", "draft_id"
2057 ) VALUES (
2058 "event_v",
2059 NEW."author_id",
2060 "initiative_row"."issue_id",
2061 "issue_row"."state",
2062 "initiative_row"."id",
2063 NEW."id" );
2064 RETURN NULL;
2065 END;
2066 $$;
2068 CREATE TRIGGER "write_event_initiative_or_draft_created"
2069 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
2070 "write_event_initiative_or_draft_created_trigger"();
2072 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
2073 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
2076 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
2077 RETURNS TRIGGER
2078 LANGUAGE 'plpgsql' VOLATILE AS $$
2079 DECLARE
2080 "issue_row" "issue"%ROWTYPE;
2081 "draft_id_v" "draft"."id"%TYPE;
2082 BEGIN
2083 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
2084 SELECT * INTO "issue_row" FROM "issue"
2085 WHERE "id" = NEW."issue_id";
2086 SELECT "id" INTO "draft_id_v" FROM "current_draft"
2087 WHERE "initiative_id" = NEW."id";
2088 INSERT INTO "event" (
2089 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
2090 ) VALUES (
2091 'initiative_revoked',
2092 NEW."revoked_by_member_id",
2093 NEW."issue_id",
2094 "issue_row"."state",
2095 NEW."id",
2096 "draft_id_v");
2097 END IF;
2098 RETURN NULL;
2099 END;
2100 $$;
2102 CREATE TRIGGER "write_event_initiative_revoked"
2103 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
2104 "write_event_initiative_revoked_trigger"();
2106 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
2107 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
2110 CREATE FUNCTION "write_event_suggestion_created_trigger"()
2111 RETURNS TRIGGER
2112 LANGUAGE 'plpgsql' VOLATILE AS $$
2113 DECLARE
2114 "initiative_row" "initiative"%ROWTYPE;
2115 "issue_row" "issue"%ROWTYPE;
2116 BEGIN
2117 SELECT * INTO "initiative_row" FROM "initiative"
2118 WHERE "id" = NEW."initiative_id";
2119 SELECT * INTO "issue_row" FROM "issue"
2120 WHERE "id" = "initiative_row"."issue_id";
2121 INSERT INTO "event" (
2122 "event", "member_id",
2123 "issue_id", "state", "initiative_id", "suggestion_id"
2124 ) VALUES (
2125 'suggestion_created',
2126 NEW."author_id",
2127 "initiative_row"."issue_id",
2128 "issue_row"."state",
2129 "initiative_row"."id",
2130 NEW."id" );
2131 RETURN NULL;
2132 END;
2133 $$;
2135 CREATE TRIGGER "write_event_suggestion_created"
2136 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
2137 "write_event_suggestion_created_trigger"();
2139 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
2140 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
2144 ----------------------------
2145 -- Additional constraints --
2146 ----------------------------
2149 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
2150 RETURNS TRIGGER
2151 LANGUAGE 'plpgsql' VOLATILE AS $$
2152 BEGIN
2153 IF NOT EXISTS (
2154 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
2155 ) THEN
2156 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
2157 ERRCODE = 'integrity_constraint_violation',
2158 HINT = 'Create issue, initiative, and draft within the same transaction.';
2159 END IF;
2160 RETURN NULL;
2161 END;
2162 $$;
2164 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
2165 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
2166 FOR EACH ROW EXECUTE PROCEDURE
2167 "issue_requires_first_initiative_trigger"();
2169 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
2170 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
2173 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
2174 RETURNS TRIGGER
2175 LANGUAGE 'plpgsql' VOLATILE AS $$
2176 DECLARE
2177 "reference_lost" BOOLEAN;
2178 BEGIN
2179 IF TG_OP = 'DELETE' THEN
2180 "reference_lost" := TRUE;
2181 ELSE
2182 "reference_lost" := NEW."issue_id" != OLD."issue_id";
2183 END IF;
2184 IF
2185 "reference_lost" AND NOT EXISTS (
2186 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
2187 )
2188 THEN
2189 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
2190 END IF;
2191 RETURN NULL;
2192 END;
2193 $$;
2195 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
2196 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2197 FOR EACH ROW EXECUTE PROCEDURE
2198 "last_initiative_deletes_issue_trigger"();
2200 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
2201 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
2204 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
2205 RETURNS TRIGGER
2206 LANGUAGE 'plpgsql' VOLATILE AS $$
2207 BEGIN
2208 IF NOT EXISTS (
2209 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
2210 ) THEN
2211 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
2212 ERRCODE = 'integrity_constraint_violation',
2213 HINT = 'Create issue, initiative and draft within the same transaction.';
2214 END IF;
2215 RETURN NULL;
2216 END;
2217 $$;
2219 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
2220 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2221 FOR EACH ROW EXECUTE PROCEDURE
2222 "initiative_requires_first_draft_trigger"();
2224 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
2225 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
2228 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
2229 RETURNS TRIGGER
2230 LANGUAGE 'plpgsql' VOLATILE AS $$
2231 DECLARE
2232 "reference_lost" BOOLEAN;
2233 BEGIN
2234 IF TG_OP = 'DELETE' THEN
2235 "reference_lost" := TRUE;
2236 ELSE
2237 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
2238 END IF;
2239 IF
2240 "reference_lost" AND NOT EXISTS (
2241 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
2242 )
2243 THEN
2244 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
2245 END IF;
2246 RETURN NULL;
2247 END;
2248 $$;
2250 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
2251 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
2252 FOR EACH ROW EXECUTE PROCEDURE
2253 "last_draft_deletes_initiative_trigger"();
2255 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
2256 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
2259 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
2260 RETURNS TRIGGER
2261 LANGUAGE 'plpgsql' VOLATILE AS $$
2262 BEGIN
2263 IF NOT EXISTS (
2264 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
2265 ) THEN
2266 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
2267 ERRCODE = 'integrity_constraint_violation',
2268 HINT = 'Create suggestion and opinion within the same transaction.';
2269 END IF;
2270 RETURN NULL;
2271 END;
2272 $$;
2274 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
2275 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
2276 FOR EACH ROW EXECUTE PROCEDURE
2277 "suggestion_requires_first_opinion_trigger"();
2279 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
2280 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
2283 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
2284 RETURNS TRIGGER
2285 LANGUAGE 'plpgsql' VOLATILE AS $$
2286 DECLARE
2287 "reference_lost" BOOLEAN;
2288 BEGIN
2289 IF TG_OP = 'DELETE' THEN
2290 "reference_lost" := TRUE;
2291 ELSE
2292 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
2293 END IF;
2294 IF
2295 "reference_lost" AND NOT EXISTS (
2296 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
2297 )
2298 THEN
2299 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
2300 END IF;
2301 RETURN NULL;
2302 END;
2303 $$;
2305 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
2306 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
2307 FOR EACH ROW EXECUTE PROCEDURE
2308 "last_opinion_deletes_suggestion_trigger"();
2310 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
2311 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
2314 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
2315 RETURNS TRIGGER
2316 LANGUAGE 'plpgsql' VOLATILE AS $$
2317 BEGIN
2318 DELETE FROM "direct_voter"
2319 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2320 RETURN NULL;
2321 END;
2322 $$;
2324 CREATE TRIGGER "non_voter_deletes_direct_voter"
2325 AFTER INSERT OR UPDATE ON "non_voter"
2326 FOR EACH ROW EXECUTE PROCEDURE
2327 "non_voter_deletes_direct_voter_trigger"();
2329 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
2330 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
2333 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
2334 RETURNS TRIGGER
2335 LANGUAGE 'plpgsql' VOLATILE AS $$
2336 BEGIN
2337 DELETE FROM "non_voter"
2338 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2339 RETURN NULL;
2340 END;
2341 $$;
2343 CREATE TRIGGER "direct_voter_deletes_non_voter"
2344 AFTER INSERT OR UPDATE ON "direct_voter"
2345 FOR EACH ROW EXECUTE PROCEDURE
2346 "direct_voter_deletes_non_voter_trigger"();
2348 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
2349 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
2352 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
2353 RETURNS TRIGGER
2354 LANGUAGE 'plpgsql' VOLATILE AS $$
2355 BEGIN
2356 IF NEW."comment" ISNULL THEN
2357 NEW."comment_changed" := NULL;
2358 NEW."formatting_engine" := NULL;
2359 END IF;
2360 RETURN NEW;
2361 END;
2362 $$;
2364 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
2365 BEFORE INSERT OR UPDATE ON "direct_voter"
2366 FOR EACH ROW EXECUTE PROCEDURE
2367 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
2369 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
2370 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
2374 ---------------------------------
2375 -- Delete incomplete snapshots --
2376 ---------------------------------
2379 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
2380 RETURNS TRIGGER
2381 LANGUAGE 'plpgsql' VOLATILE AS $$
2382 BEGIN
2383 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
2384 RETURN NULL;
2385 END;
2386 $$;
2388 CREATE TRIGGER "delete_snapshot_on_partial_delete"
2389 AFTER DELETE ON "snapshot_issue"
2390 FOR EACH ROW EXECUTE PROCEDURE
2391 "delete_snapshot_on_partial_delete_trigger"();
2393 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
2394 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
2398 ---------------------------------------------------------------
2399 -- Ensure that votes are not modified when issues are closed --
2400 ---------------------------------------------------------------
2402 -- NOTE: Frontends should ensure this anyway, but in case of programming
2403 -- errors the following triggers ensure data integrity.
2406 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
2407 RETURNS TRIGGER
2408 LANGUAGE 'plpgsql' VOLATILE AS $$
2409 DECLARE
2410 "issue_id_v" "issue"."id"%TYPE;
2411 "issue_row" "issue"%ROWTYPE;
2412 BEGIN
2413 IF EXISTS (
2414 SELECT NULL FROM "temporary_transaction_data"
2415 WHERE "txid" = txid_current()
2416 AND "key" = 'override_protection_triggers'
2417 AND "value" = TRUE::TEXT
2418 ) THEN
2419 RETURN NULL;
2420 END IF;
2421 IF TG_OP = 'DELETE' THEN
2422 "issue_id_v" := OLD."issue_id";
2423 ELSE
2424 "issue_id_v" := NEW."issue_id";
2425 END IF;
2426 SELECT INTO "issue_row" * FROM "issue"
2427 WHERE "id" = "issue_id_v" FOR SHARE;
2428 IF (
2429 "issue_row"."closed" NOTNULL OR (
2430 "issue_row"."state" = 'voting' AND
2431 "issue_row"."phase_finished" NOTNULL
2432 )
2433 ) THEN
2434 IF
2435 TG_RELID = 'direct_voter'::regclass AND
2436 TG_OP = 'UPDATE'
2437 THEN
2438 IF
2439 OLD."issue_id" = NEW."issue_id" AND
2440 OLD."member_id" = NEW."member_id" AND
2441 OLD."weight" = NEW."weight"
2442 THEN
2443 RETURN NULL; -- allows changing of voter comment
2444 END IF;
2445 END IF;
2446 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
2447 ERRCODE = 'integrity_constraint_violation';
2448 END IF;
2449 RETURN NULL;
2450 END;
2451 $$;
2453 CREATE TRIGGER "forbid_changes_on_closed_issue"
2454 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
2455 FOR EACH ROW EXECUTE PROCEDURE
2456 "forbid_changes_on_closed_issue_trigger"();
2458 CREATE TRIGGER "forbid_changes_on_closed_issue"
2459 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
2460 FOR EACH ROW EXECUTE PROCEDURE
2461 "forbid_changes_on_closed_issue_trigger"();
2463 CREATE TRIGGER "forbid_changes_on_closed_issue"
2464 AFTER INSERT OR UPDATE OR DELETE ON "vote"
2465 FOR EACH ROW EXECUTE PROCEDURE
2466 "forbid_changes_on_closed_issue_trigger"();
2468 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"';
2469 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';
2470 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';
2471 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';
2475 --------------------------------------------------------------------
2476 -- Auto-retrieval of fields only needed for referential integrity --
2477 --------------------------------------------------------------------
2480 CREATE FUNCTION "autofill_unit_id_from_admission_rule_trigger"()
2481 RETURNS TRIGGER
2482 LANGUAGE 'plpgsql' VOLATILE AS $$
2483 BEGIN
2484 IF NEW."unit_id" ISNULL THEN
2485 SELECT "unit_id" INTO NEW."unit_id"
2486 FROM "admission_rule" WHERE "id" = NEW."admission_rule_id";
2487 END IF;
2488 RETURN NEW;
2489 END;
2490 $$;
2492 CREATE TRIGGER "autofill_unit_id" BEFORE INSERT ON "admission_rule_condition"
2493 FOR EACH ROW EXECUTE PROCEDURE "autofill_unit_id_from_admission_rule_trigger"();
2495 COMMENT ON FUNCTION "autofill_unit_id_from_admission_rule_trigger"() IS 'Implementation of trigger "autofill_admission_rule_id" on table "admission_rule_entry"';
2496 COMMENT ON TRIGGER "autofill_unit_id" ON "admission_rule_condition" IS 'Set "unit_id" field automatically, if NULL';
2499 CREATE FUNCTION "autofill_issue_id_trigger"()
2500 RETURNS TRIGGER
2501 LANGUAGE 'plpgsql' VOLATILE AS $$
2502 BEGIN
2503 IF NEW."issue_id" ISNULL THEN
2504 SELECT "issue_id" INTO NEW."issue_id"
2505 FROM "initiative" WHERE "id" = NEW."initiative_id";
2506 END IF;
2507 RETURN NEW;
2508 END;
2509 $$;
2511 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
2512 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2514 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
2515 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2517 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
2518 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
2519 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
2522 CREATE FUNCTION "autofill_initiative_id_trigger"()
2523 RETURNS TRIGGER
2524 LANGUAGE 'plpgsql' VOLATILE AS $$
2525 BEGIN
2526 IF NEW."initiative_id" ISNULL THEN
2527 SELECT "initiative_id" INTO NEW."initiative_id"
2528 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
2529 END IF;
2530 RETURN NEW;
2531 END;
2532 $$;
2534 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
2535 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
2537 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
2538 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
2542 -------------------------------------------------------
2543 -- Automatic copying of values for indexing purposes --
2544 -------------------------------------------------------
2547 CREATE FUNCTION "copy_current_draft_data"
2548 ("initiative_id_p" "initiative"."id"%TYPE )
2549 RETURNS VOID
2550 LANGUAGE 'plpgsql' VOLATILE AS $$
2551 BEGIN
2552 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
2553 FOR UPDATE;
2554 UPDATE "initiative" SET
2555 "latitude1" = "draft"."latitude1",
2556 "longitude1" = "draft"."longitude1",
2557 "latitude2" = "draft"."latitude2",
2558 "longitude2" = "draft"."longitude2",
2559 "draft_text_search_data" = "draft"."text_search_data"
2560 FROM "current_draft" AS "draft"
2561 WHERE "initiative"."id" = "initiative_id_p"
2562 AND "draft"."initiative_id" = "initiative_id_p";
2563 END;
2564 $$;
2566 COMMENT ON FUNCTION "copy_current_draft_data"
2567 ( "initiative"."id"%TYPE )
2568 IS 'Helper function for function "copy_current_draft_data_trigger"';
2571 CREATE FUNCTION "copy_current_draft_data_trigger"()
2572 RETURNS TRIGGER
2573 LANGUAGE 'plpgsql' VOLATILE AS $$
2574 BEGIN
2575 IF TG_OP='DELETE' THEN
2576 PERFORM "copy_current_draft_data"(OLD."initiative_id");
2577 ELSE
2578 IF TG_OP='UPDATE' THEN
2579 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
2580 PERFORM "copy_current_draft_data"(OLD."initiative_id");
2581 END IF;
2582 END IF;
2583 PERFORM "copy_current_draft_data"(NEW."initiative_id");
2584 END IF;
2585 RETURN NULL;
2586 END;
2587 $$;
2589 CREATE TRIGGER "copy_current_draft_data"
2590 AFTER INSERT OR UPDATE OR DELETE ON "draft"
2591 FOR EACH ROW EXECUTE PROCEDURE
2592 "copy_current_draft_data_trigger"();
2594 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
2595 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
2599 -----------------------------------------------------
2600 -- Automatic calculation of certain default values --
2601 -----------------------------------------------------
2604 CREATE FUNCTION "copy_timings_trigger"()
2605 RETURNS TRIGGER
2606 LANGUAGE 'plpgsql' VOLATILE AS $$
2607 DECLARE
2608 "policy_row" "policy"%ROWTYPE;
2609 BEGIN
2610 SELECT * INTO "policy_row" FROM "policy"
2611 WHERE "id" = NEW."policy_id";
2612 IF NEW."min_admission_time" ISNULL THEN
2613 NEW."min_admission_time" := "policy_row"."min_admission_time";
2614 END IF;
2615 IF NEW."max_admission_time" ISNULL THEN
2616 NEW."max_admission_time" := "policy_row"."max_admission_time";
2617 END IF;
2618 IF NEW."discussion_time" ISNULL THEN
2619 NEW."discussion_time" := "policy_row"."discussion_time";
2620 END IF;
2621 IF NEW."verification_time" ISNULL THEN
2622 NEW."verification_time" := "policy_row"."verification_time";
2623 END IF;
2624 IF NEW."voting_time" ISNULL THEN
2625 NEW."voting_time" := "policy_row"."voting_time";
2626 END IF;
2627 RETURN NEW;
2628 END;
2629 $$;
2631 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
2632 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
2634 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
2635 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
2638 CREATE FUNCTION "default_for_draft_id_trigger"()
2639 RETURNS TRIGGER
2640 LANGUAGE 'plpgsql' VOLATILE AS $$
2641 BEGIN
2642 IF NEW."draft_id" ISNULL THEN
2643 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
2644 WHERE "initiative_id" = NEW."initiative_id";
2645 END IF;
2646 RETURN NEW;
2647 END;
2648 $$;
2650 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
2651 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
2652 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
2653 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
2655 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
2656 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
2657 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';
2661 ----------------------------------------
2662 -- Automatic creation of dependencies --
2663 ----------------------------------------
2666 CREATE FUNCTION "autocreate_interest_trigger"()
2667 RETURNS TRIGGER
2668 LANGUAGE 'plpgsql' VOLATILE AS $$
2669 BEGIN
2670 IF NOT EXISTS (
2671 SELECT NULL FROM "initiative" JOIN "interest"
2672 ON "initiative"."issue_id" = "interest"."issue_id"
2673 WHERE "initiative"."id" = NEW."initiative_id"
2674 AND "interest"."member_id" = NEW."member_id"
2675 ) THEN
2676 BEGIN
2677 INSERT INTO "interest" ("issue_id", "member_id")
2678 SELECT "issue_id", NEW."member_id"
2679 FROM "initiative" WHERE "id" = NEW."initiative_id";
2680 EXCEPTION WHEN unique_violation THEN END;
2681 END IF;
2682 RETURN NEW;
2683 END;
2684 $$;
2686 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
2687 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
2689 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
2690 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';
2693 CREATE FUNCTION "autocreate_supporter_trigger"()
2694 RETURNS TRIGGER
2695 LANGUAGE 'plpgsql' VOLATILE AS $$
2696 BEGIN
2697 IF NOT EXISTS (
2698 SELECT NULL FROM "suggestion" JOIN "supporter"
2699 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
2700 WHERE "suggestion"."id" = NEW."suggestion_id"
2701 AND "supporter"."member_id" = NEW."member_id"
2702 ) THEN
2703 BEGIN
2704 INSERT INTO "supporter" ("initiative_id", "member_id")
2705 SELECT "initiative_id", NEW."member_id"
2706 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
2707 EXCEPTION WHEN unique_violation THEN END;
2708 END IF;
2709 RETURN NEW;
2710 END;
2711 $$;
2713 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
2714 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
2716 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
2717 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.';
2721 ------------------------------------------
2722 -- Views and helper functions for views --
2723 ------------------------------------------
2726 CREATE VIEW "member_eligible_to_be_notified" AS
2727 SELECT * FROM "member"
2728 WHERE "activated" NOTNULL AND "locked" = FALSE;
2730 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
2733 CREATE VIEW "member_to_notify" AS
2734 SELECT * FROM "member_eligible_to_be_notified"
2735 WHERE "disable_notifications" = FALSE;
2737 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
2740 CREATE VIEW "matching_admission_rule_condition" AS
2741 SELECT DISTINCT ON ("issue_id", "admission_rule_condition"."admission_rule_id")
2742 "issue"."id" AS "issue_id",
2743 "admission_rule_condition".*
2744 FROM "admission_rule_condition"
2745 JOIN "area" ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2746 JOIN "issue" ON "area"."id" = "issue"."area_id"
2747 WHERE (
2748 "admission_rule_condition"."policy_id" ISNULL OR
2749 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2750 ) AND (
2751 "admission_rule_condition"."area_id" ISNULL OR
2752 "admission_rule_condition"."area_id" = "area"."id"
2753 )
2754 ORDER BY
2755 "issue_id",
2756 "admission_rule_condition"."admission_rule_id",
2757 "admission_rule_condition"."policy_id" ISNULL,
2758 "admission_rule_condition"."area_id" ISNULL;
2760 COMMENT ON VIEW "matching_admission_rule_condition" IS 'Selects the most fitting "admission_rule_condition" for a given pair of "issue" and "admission_rule"';
2763 CREATE VIEW "applicable_admission_rule" AS
2764 SELECT * FROM "admission_rule"
2765 WHERE NOT EXISTS (
2766 SELECT NULL FROM "issue"
2767 JOIN "matching_admission_rule_condition" AS "condition"
2768 ON "issue"."id" = "condition"."issue_id"
2769 WHERE "condition"."admission_rule_id" = "admission_rule"."id"
2770 AND "issue"."accepted" > now() - "condition"."holdoff_time"
2771 );
2773 COMMENT ON VIEW "applicable_admission_rule" IS 'Filters "admission_rule"s which are not blocked by a recently admitted issue';
2776 CREATE VIEW "issue_for_admission" AS
2777 SELECT
2778 "issue".*,
2779 max("initiative"."supporter_count") AS "max_supporter_count"
2780 FROM "issue"
2781 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2782 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2783 JOIN "area" ON "issue"."area_id" = "area"."id"
2784 JOIN "admission_rule_condition"
2785 ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2786 AND (
2787 "admission_rule_condition"."policy_id" ISNULL OR
2788 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2789 )
2790 AND (
2791 "admission_rule_condition"."area_id" ISNULL OR
2792 "admission_rule_condition"."area_id" = "issue"."area_id"
2793 )
2794 JOIN "applicable_admission_rule"
2795 ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id"
2796 WHERE "issue"."state" = 'admission'::"issue_state"
2797 AND now() >= "issue"."created" + "issue"."min_admission_time"
2798 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
2799 AND "initiative"."revoked" ISNULL
2800 GROUP BY "issue"."id"
2801 ORDER BY "max_supporter_count" DESC, "issue"."id"
2802 LIMIT 1;
2804 COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
2807 CREATE VIEW "unit_delegation" AS
2808 SELECT
2809 "unit"."id" AS "unit_id",
2810 "delegation"."id",
2811 "delegation"."truster_id",
2812 "delegation"."trustee_id",
2813 "delegation"."scope"
2814 FROM "unit"
2815 JOIN "delegation"
2816 ON "delegation"."unit_id" = "unit"."id"
2817 JOIN "member"
2818 ON "delegation"."truster_id" = "member"."id"
2819 JOIN "privilege"
2820 ON "delegation"."unit_id" = "privilege"."unit_id"
2821 AND "delegation"."truster_id" = "privilege"."member_id"
2822 WHERE "member"."active" AND "privilege"."voting_right";
2824 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
2827 CREATE VIEW "area_delegation" AS
2828 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
2829 "area"."id" AS "area_id",
2830 "delegation"."id",
2831 "delegation"."truster_id",
2832 "delegation"."trustee_id",
2833 "delegation"."scope"
2834 FROM "area"
2835 JOIN "delegation"
2836 ON "delegation"."unit_id" = "area"."unit_id"
2837 OR "delegation"."area_id" = "area"."id"
2838 JOIN "member"
2839 ON "delegation"."truster_id" = "member"."id"
2840 JOIN "privilege"
2841 ON "area"."unit_id" = "privilege"."unit_id"
2842 AND "delegation"."truster_id" = "privilege"."member_id"
2843 WHERE "member"."active" AND "privilege"."voting_right"
2844 ORDER BY
2845 "area"."id",
2846 "delegation"."truster_id",
2847 "delegation"."scope" DESC;
2849 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
2852 CREATE VIEW "issue_delegation" AS
2853 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2854 "issue"."id" AS "issue_id",
2855 "delegation"."id",
2856 "delegation"."truster_id",
2857 "delegation"."trustee_id",
2858 "delegation"."scope"
2859 FROM "issue"
2860 JOIN "area"
2861 ON "area"."id" = "issue"."area_id"
2862 JOIN "delegation"
2863 ON "delegation"."unit_id" = "area"."unit_id"
2864 OR "delegation"."area_id" = "area"."id"
2865 OR "delegation"."issue_id" = "issue"."id"
2866 JOIN "member"
2867 ON "delegation"."truster_id" = "member"."id"
2868 JOIN "privilege"
2869 ON "area"."unit_id" = "privilege"."unit_id"
2870 AND "delegation"."truster_id" = "privilege"."member_id"
2871 WHERE "member"."active" AND "privilege"."voting_right"
2872 ORDER BY
2873 "issue"."id",
2874 "delegation"."truster_id",
2875 "delegation"."scope" DESC;
2877 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2880 CREATE VIEW "member_count_view" AS
2881 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2883 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2886 CREATE VIEW "unit_member_count" AS
2887 SELECT
2888 "unit"."id" AS "unit_id",
2889 count("member"."id") AS "member_count"
2890 FROM "unit"
2891 LEFT JOIN "privilege"
2892 ON "privilege"."unit_id" = "unit"."id"
2893 AND "privilege"."voting_right"
2894 LEFT JOIN "member"
2895 ON "member"."id" = "privilege"."member_id"
2896 AND "member"."active"
2897 GROUP BY "unit"."id";
2899 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2902 CREATE VIEW "opening_draft" AS
2903 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2904 ORDER BY "initiative_id", "id";
2906 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2909 CREATE VIEW "current_draft" AS
2910 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2911 ORDER BY "initiative_id", "id" DESC;
2913 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2916 CREATE VIEW "critical_opinion" AS
2917 SELECT * FROM "opinion"
2918 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2919 OR ("degree" = -2 AND "fulfilled" = TRUE);
2921 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2924 CREATE VIEW "issue_supporter_in_admission_state" AS
2925 SELECT
2926 "area"."unit_id",
2927 "issue"."area_id",
2928 "issue"."id" AS "issue_id",
2929 "supporter"."member_id",
2930 "direct_interest_snapshot"."weight"
2931 FROM "issue"
2932 JOIN "area" ON "area"."id" = "issue"."area_id"
2933 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2934 JOIN "direct_interest_snapshot"
2935 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2936 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2937 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2938 WHERE "issue"."state" = 'admission'::"issue_state";
2940 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
2943 CREATE VIEW "initiative_suggestion_order_calculation" AS
2944 SELECT
2945 "initiative"."id" AS "initiative_id",
2946 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2947 FROM "initiative" JOIN "issue"
2948 ON "initiative"."issue_id" = "issue"."id"
2949 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2950 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2952 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2954 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
2957 CREATE VIEW "individual_suggestion_ranking" AS
2958 SELECT
2959 "opinion"."initiative_id",
2960 "opinion"."member_id",
2961 "direct_interest_snapshot"."weight",
2962 CASE WHEN
2963 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2964 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2965 THEN 1 ELSE
2966 CASE WHEN
2967 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2968 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2969 THEN 2 ELSE
2970 CASE WHEN
2971 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2972 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2973 THEN 3 ELSE 4 END
2974 END
2975 END AS "preference",
2976 "opinion"."suggestion_id"
2977 FROM "opinion"
2978 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2979 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2980 JOIN "direct_interest_snapshot"
2981 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2982 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2983 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2985 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2988 CREATE VIEW "battle_participant" AS
2989 SELECT "initiative"."id", "initiative"."issue_id"
2990 FROM "issue" JOIN "initiative"
2991 ON "issue"."id" = "initiative"."issue_id"
2992 WHERE "initiative"."admitted"
2993 UNION ALL
2994 SELECT NULL, "id" AS "issue_id"
2995 FROM "issue";
2997 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
3000 CREATE VIEW "battle_view" AS
3001 SELECT
3002 "issue"."id" AS "issue_id",
3003 "winning_initiative"."id" AS "winning_initiative_id",
3004 "losing_initiative"."id" AS "losing_initiative_id",
3005 sum(
3006 CASE WHEN
3007 coalesce("better_vote"."grade", 0) >
3008 coalesce("worse_vote"."grade", 0)
3009 THEN "direct_voter"."weight" ELSE 0 END
3010 ) AS "count"
3011 FROM "issue"
3012 LEFT JOIN "direct_voter"
3013 ON "issue"."id" = "direct_voter"."issue_id"
3014 JOIN "battle_participant" AS "winning_initiative"
3015 ON "issue"."id" = "winning_initiative"."issue_id"
3016 JOIN "battle_participant" AS "losing_initiative"
3017 ON "issue"."id" = "losing_initiative"."issue_id"
3018 LEFT JOIN "vote" AS "better_vote"
3019 ON "direct_voter"."member_id" = "better_vote"."member_id"
3020 AND "winning_initiative"."id" = "better_vote"."initiative_id"
3021 LEFT JOIN "vote" AS "worse_vote"
3022 ON "direct_voter"."member_id" = "worse_vote"."member_id"
3023 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
3024 WHERE "issue"."state" = 'voting'
3025 AND "issue"."phase_finished" NOTNULL
3026 AND (
3027 "winning_initiative"."id" != "losing_initiative"."id" OR
3028 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
3029 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
3030 GROUP BY
3031 "issue"."id",
3032 "winning_initiative"."id",
3033 "losing_initiative"."id";
3035 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
3038 CREATE VIEW "expired_session" AS
3039 SELECT * FROM "session" WHERE now() > "expiry";
3041 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
3042 DELETE FROM "session" WHERE "ident" = OLD."ident";
3044 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
3045 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
3048 CREATE VIEW "open_issue" AS
3049 SELECT * FROM "issue" WHERE "closed" ISNULL;
3051 COMMENT ON VIEW "open_issue" IS 'All open issues';
3054 CREATE VIEW "member_contingent" AS
3055 SELECT
3056 "member"."id" AS "member_id",
3057 "contingent"."polling",
3058 "contingent"."time_frame",
3059 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
3060 (
3061 SELECT count(1) FROM "draft"
3062 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3063 WHERE "draft"."author_id" = "member"."id"
3064 AND "initiative"."polling" = "contingent"."polling"
3065 AND "draft"."created" > now() - "contingent"."time_frame"
3066 ) + (
3067 SELECT count(1) FROM "suggestion"
3068 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
3069 WHERE "suggestion"."author_id" = "member"."id"
3070 AND "contingent"."polling" = FALSE
3071 AND "suggestion"."created" > now() - "contingent"."time_frame"
3072 )
3073 ELSE NULL END AS "text_entry_count",
3074 "contingent"."text_entry_limit",
3075 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
3076 SELECT count(1) FROM "opening_draft" AS "draft"
3077 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3078 WHERE "draft"."author_id" = "member"."id"
3079 AND "initiative"."polling" = "contingent"."polling"
3080 AND "draft"."created" > now() - "contingent"."time_frame"
3081 ) ELSE NULL END AS "initiative_count",
3082 "contingent"."initiative_limit"
3083 FROM "member" CROSS JOIN "contingent";
3085 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
3087 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
3088 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
3091 CREATE VIEW "member_contingent_left" AS
3092 SELECT
3093 "member_id",
3094 "polling",
3095 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
3096 max("initiative_limit" - "initiative_count") AS "initiatives_left"
3097 FROM "member_contingent" GROUP BY "member_id", "polling";
3099 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.';
3102 CREATE VIEW "event_for_notification" AS
3103 SELECT
3104 "member"."id" AS "recipient_id",
3105 "event".*
3106 FROM "member" CROSS JOIN "event"
3107 JOIN "issue" ON "issue"."id" = "event"."issue_id"
3108 JOIN "area" ON "area"."id" = "issue"."area_id"
3109 LEFT JOIN "privilege" ON
3110 "privilege"."member_id" = "member"."id" AND
3111 "privilege"."unit_id" = "area"."unit_id" AND
3112 "privilege"."voting_right" = TRUE
3113 LEFT JOIN "subscription" ON
3114 "subscription"."member_id" = "member"."id" AND
3115 "subscription"."unit_id" = "area"."unit_id"
3116 LEFT JOIN "ignored_area" ON
3117 "ignored_area"."member_id" = "member"."id" AND
3118 "ignored_area"."area_id" = "issue"."area_id"
3119 LEFT JOIN "interest" ON
3120 "interest"."member_id" = "member"."id" AND
3121 "interest"."issue_id" = "event"."issue_id"
3122 LEFT JOIN "supporter" ON
3123 "supporter"."member_id" = "member"."id" AND
3124 "supporter"."initiative_id" = "event"."initiative_id"
3125 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
3126 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
3127 AND (
3128 "event"."event" = 'issue_state_changed'::"event_type" OR
3129 ( "event"."event" = 'initiative_revoked'::"event_type" AND
3130 "supporter"."member_id" NOTNULL ) );
3132 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
3134 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
3137 CREATE VIEW "updated_initiative" AS
3138 SELECT
3139 "supporter"."member_id" AS "recipient_id",
3140 FALSE AS "featured",
3141 "supporter"."initiative_id"
3142 FROM "supporter"
3143 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
3144 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3145 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3146 "sent"."member_id" = "supporter"."member_id" AND
3147 "sent"."initiative_id" = "supporter"."initiative_id"
3148 LEFT JOIN "ignored_initiative" ON
3149 "ignored_initiative"."member_id" = "supporter"."member_id" AND
3150 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
3151 WHERE "issue"."state" IN ('admission', 'discussion')
3152 AND "initiative"."revoked" ISNULL
3153 AND "ignored_initiative"."member_id" ISNULL
3154 AND (
3155 EXISTS (
3156 SELECT NULL FROM "draft"
3157 LEFT JOIN "ignored_member" ON
3158 "ignored_member"."member_id" = "supporter"."member_id" AND
3159 "ignored_member"."other_member_id" = "draft"."author_id"
3160 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
3161 AND "draft"."id" > "supporter"."draft_id"
3162 AND "ignored_member"."member_id" ISNULL
3163 ) OR EXISTS (
3164 SELECT NULL FROM "suggestion"
3165 LEFT JOIN "opinion" ON
3166 "opinion"."member_id" = "supporter"."member_id" AND
3167 "opinion"."suggestion_id" = "suggestion"."id"
3168 LEFT JOIN "ignored_member" ON
3169 "ignored_member"."member_id" = "supporter"."member_id" AND
3170 "ignored_member"."other_member_id" = "suggestion"."author_id"
3171 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
3172 AND "opinion"."member_id" ISNULL
3173 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3174 AND "ignored_member"."member_id" ISNULL
3175 )
3176 );
3178 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
3181 CREATE FUNCTION "featured_initiative"
3182 ( "recipient_id_p" "member"."id"%TYPE,
3183 "area_id_p" "area"."id"%TYPE )
3184 RETURNS SETOF "initiative"."id"%TYPE
3185 LANGUAGE 'plpgsql' STABLE AS $$
3186 DECLARE
3187 "counter_v" "member"."notification_counter"%TYPE;
3188 "sample_size_v" "member"."notification_sample_size"%TYPE;
3189 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
3190 "match_v" BOOLEAN;
3191 "member_id_v" "member"."id"%TYPE;
3192 "seed_v" TEXT;
3193 "initiative_id_v" "initiative"."id"%TYPE;
3194 BEGIN
3195 SELECT "notification_counter", "notification_sample_size"
3196 INTO "counter_v", "sample_size_v"
3197 FROM "member" WHERE "id" = "recipient_id_p";
3198 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
3199 RETURN;
3200 END IF;
3201 "initiative_id_ary" := '{}';
3202 LOOP
3203 "match_v" := FALSE;
3204 FOR "member_id_v", "seed_v" IN
3205 SELECT * FROM (
3206 SELECT DISTINCT
3207 "supporter"."member_id",
3208 md5(
3209 "recipient_id_p" || '-' ||
3210 "counter_v" || '-' ||
3211 "area_id_p" || '-' ||
3212 "supporter"."member_id"
3213 ) AS "seed"
3214 FROM "supporter"
3215 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
3216 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3217 WHERE "supporter"."member_id" != "recipient_id_p"
3218 AND "issue"."area_id" = "area_id_p"
3219 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3220 ) AS "subquery"
3221 ORDER BY "seed"
3222 LOOP
3223 SELECT "initiative"."id" INTO "initiative_id_v"
3224 FROM "initiative"
3225 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3226 JOIN "area" ON "area"."id" = "issue"."area_id"
3227 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
3228 LEFT JOIN "supporter" AS "self_support" ON
3229 "self_support"."initiative_id" = "initiative"."id" AND
3230 "self_support"."member_id" = "recipient_id_p"
3231 LEFT JOIN "privilege" ON
3232 "privilege"."member_id" = "recipient_id_p" AND
3233 "privilege"."unit_id" = "area"."unit_id" AND
3234 "privilege"."voting_right" = TRUE
3235 LEFT JOIN "subscription" ON
3236 "subscription"."member_id" = "recipient_id_p" AND
3237 "subscription"."unit_id" = "area"."unit_id"
3238 LEFT JOIN "ignored_initiative" ON
3239 "ignored_initiative"."member_id" = "recipient_id_p" AND
3240 "ignored_initiative"."initiative_id" = "initiative"."id"
3241 WHERE "supporter"."member_id" = "member_id_v"
3242 AND "issue"."area_id" = "area_id_p"
3243 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3244 AND "initiative"."revoked" ISNULL
3245 AND "self_support"."member_id" ISNULL
3246 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
3247 AND (
3248 "privilege"."member_id" NOTNULL OR
3249 "subscription"."member_id" NOTNULL )
3250 AND "ignored_initiative"."member_id" ISNULL
3251 AND NOT EXISTS (
3252 SELECT NULL FROM "draft"
3253 JOIN "ignored_member" ON
3254 "ignored_member"."member_id" = "recipient_id_p" AND
3255 "ignored_member"."other_member_id" = "draft"."author_id"
3256 WHERE "draft"."initiative_id" = "initiative"."id"
3257 )
3258 ORDER BY md5("seed_v" || '-' || "initiative"."id")
3259 LIMIT 1;
3260 IF FOUND THEN
3261 "match_v" := TRUE;
3262 RETURN NEXT "initiative_id_v";
3263 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
3264 RETURN;
3265 END IF;
3266 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
3267 END IF;
3268 END LOOP;
3269 EXIT WHEN NOT "match_v";
3270 END LOOP;
3271 RETURN;
3272 END;
3273 $$;
3275 COMMENT ON FUNCTION "featured_initiative"
3276 ( "recipient_id_p" "member"."id"%TYPE,
3277 "area_id_p" "area"."id"%TYPE )
3278 IS 'Helper function for view "updated_or_featured_initiative"';
3281 CREATE VIEW "updated_or_featured_initiative" AS
3282 SELECT
3283 "subquery".*,
3284 NOT EXISTS (
3285 SELECT NULL FROM "initiative" AS "better_initiative"
3286 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
3287 AND
3288 ( COALESCE("better_initiative"."supporter_count", -1),
3289 -"better_initiative"."id" ) >
3290 ( COALESCE("initiative"."supporter_count", -1),
3291 -"initiative"."id" )
3292 ) AS "leading"
3293 FROM (
3294 SELECT * FROM "updated_initiative"
3295 UNION ALL
3296 SELECT
3297 "member"."id" AS "recipient_id",
3298 TRUE AS "featured",
3299 "featured_initiative_id" AS "initiative_id"
3300 FROM "member" CROSS JOIN "area"
3301 CROSS JOIN LATERAL
3302 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
3303 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
3304 ) AS "subquery"
3305 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
3307 COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
3309 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
3310 COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
3311 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3312 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3315 CREATE VIEW "leading_complement_initiative" AS
3316 SELECT * FROM (
3317 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
3318 "uf_initiative"."recipient_id",
3319 FALSE AS "featured",
3320 "uf_initiative"."initiative_id",
3321 TRUE AS "leading"
3322 FROM "updated_or_featured_initiative" AS "uf_initiative"
3323 JOIN "initiative" AS "uf_initiative_full" ON
3324 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
3325 JOIN "initiative" ON
3326 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
3327 WHERE "initiative"."revoked" ISNULL
3328 ORDER BY
3329 "uf_initiative"."recipient_id",
3330 "initiative"."issue_id",
3331 "initiative"."supporter_count" DESC,
3332 "initiative"."id"
3333 ) AS "subquery"
3334 WHERE NOT EXISTS (
3335 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
3336 WHERE "other"."recipient_id" = "subquery"."recipient_id"
3337 AND "other"."initiative_id" = "subquery"."initiative_id"
3338 );
3340 COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
3341 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
3342 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3343 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
3346 CREATE VIEW "unfiltered_initiative_for_notification" AS
3347 SELECT
3348 "subquery".*,
3349 "supporter"."member_id" NOTNULL AS "supported",
3350 CASE WHEN "supporter"."member_id" NOTNULL THEN
3351 EXISTS (
3352 SELECT NULL FROM "draft"
3353 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3354 AND "draft"."id" > "supporter"."draft_id"
3355 )
3356 ELSE
3357 EXISTS (
3358 SELECT NULL FROM "draft"
3359 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3360 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
3361 )
3362 END AS "new_draft",
3363 CASE WHEN "supporter"."member_id" NOTNULL THEN
3364 ( SELECT count(1) FROM "suggestion"
3365 LEFT JOIN "opinion" ON
3366 "opinion"."member_id" = "supporter"."member_id" AND
3367 "opinion"."suggestion_id" = "suggestion"."id"
3368 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3369 AND "opinion"."member_id" ISNULL
3370 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3371 )
3372 ELSE
3373 ( SELECT count(1) FROM "suggestion"
3374 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3375 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3376 )
3377 END AS "new_suggestion_count"
3378 FROM (
3379 SELECT * FROM "updated_or_featured_initiative"
3380 UNION ALL
3381 SELECT * FROM "leading_complement_initiative"
3382 ) AS "subquery"
3383 LEFT JOIN "supporter" ON
3384 "supporter"."member_id" = "subquery"."recipient_id" AND
3385 "supporter"."initiative_id" = "subquery"."initiative_id"
3386 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3387 "sent"."member_id" = "subquery"."recipient_id" AND
3388 "sent"."initiative_id" = "subquery"."initiative_id";
3390 COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
3392 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3393 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
3394 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
3397 CREATE VIEW "initiative_for_notification" AS
3398 SELECT "unfiltered1".*
3399 FROM "unfiltered_initiative_for_notification" "unfiltered1"
3400 JOIN "initiative" AS "initiative1" ON
3401 "initiative1"."id" = "unfiltered1"."initiative_id"
3402 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
3403 WHERE EXISTS (
3404 SELECT NULL
3405 FROM "unfiltered_initiative_for_notification" "unfiltered2"
3406 JOIN "initiative" AS "initiative2" ON
3407 "initiative2"."id" = "unfiltered2"."initiative_id"
3408 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
3409 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
3410 AND "issue1"."area_id" = "issue2"."area_id"
3411 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
3412 );
3414 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
3416 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
3417 COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
3418 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3419 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3420 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3421 COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
3422 COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
3425 CREATE VIEW "scheduled_notification_to_send" AS
3426 SELECT * FROM (
3427 SELECT
3428 "id" AS "recipient_id",
3429 now() - CASE WHEN "notification_dow" ISNULL THEN
3430 ( "notification_sent"::DATE + CASE
3431 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3432 THEN 0 ELSE 1 END
3433 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3434 ELSE
3435 ( "notification_sent"::DATE +
3436 ( 7 + "notification_dow" -
3437 EXTRACT(DOW FROM
3438 ( "notification_sent"::DATE + CASE
3439 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3440 THEN 0 ELSE 1 END
3441 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3442 )::INTEGER
3443 ) % 7 +
3444 CASE
3445 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3446 THEN 0 ELSE 1
3447 END
3448 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3449 END AS "pending"
3450 FROM (
3451 SELECT
3452 "id",
3453 COALESCE("notification_sent", "activated") AS "notification_sent",
3454 "notification_dow",
3455 "notification_hour"
3456 FROM "member_to_notify"
3457 WHERE "notification_hour" NOTNULL
3458 ) AS "subquery1"
3459 ) AS "subquery2"
3460 WHERE "pending" > '0'::INTERVAL;
3462 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
3464 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
3465 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
3468 CREATE VIEW "newsletter_to_send" AS
3469 SELECT
3470 "member"."id" AS "recipient_id",
3471 "newsletter"."id" AS "newsletter_id",
3472 "newsletter"."published"
3473 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
3474 LEFT JOIN "privilege" ON
3475 "privilege"."member_id" = "member"."id" AND
3476 "privilege"."unit_id" = "newsletter"."unit_id" AND
3477 "privilege"."voting_right" = TRUE
3478 LEFT JOIN "subscription" ON
3479 "subscription"."member_id" = "member"."id" AND
3480 "subscription"."unit_id" = "newsletter"."unit_id"
3481 WHERE "newsletter"."published" <= now()
3482 AND "newsletter"."sent" ISNULL
3483 AND (
3484 "member"."disable_notifications" = FALSE OR
3485 "newsletter"."include_all_members" = TRUE )
3486 AND (
3487 "newsletter"."unit_id" ISNULL OR
3488 "privilege"."member_id" NOTNULL OR
3489 "subscription"."member_id" NOTNULL );
3491 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
3493 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
3497 ------------------------------------------------------
3498 -- Row set returning function for delegation chains --
3499 ------------------------------------------------------
3502 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
3503 ('first', 'intermediate', 'last', 'repetition');
3505 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
3508 CREATE TYPE "delegation_chain_row" AS (
3509 "index" INT4,
3510 "member_id" INT4,
3511 "member_valid" BOOLEAN,
3512 "participation" BOOLEAN,
3513 "overridden" BOOLEAN,
3514 "scope_in" "delegation_scope",
3515 "scope_out" "delegation_scope",
3516 "disabled_out" BOOLEAN,
3517 "loop" "delegation_chain_loop_tag" );
3519 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
3521 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
3522 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';
3523 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
3524 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
3525 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
3526 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
3527 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
3530 CREATE FUNCTION "delegation_chain_for_closed_issue"
3531 ( "member_id_p" "member"."id"%TYPE,
3532 "issue_id_p" "issue"."id"%TYPE )
3533 RETURNS SETOF "delegation_chain_row"
3534 LANGUAGE 'plpgsql' STABLE AS $$
3535 DECLARE
3536 "output_row" "delegation_chain_row";
3537 "direct_voter_row" "direct_voter"%ROWTYPE;
3538 "delegating_voter_row" "delegating_voter"%ROWTYPE;
3539 BEGIN
3540 "output_row"."index" := 0;
3541 "output_row"."member_id" := "member_id_p";
3542 "output_row"."member_valid" := TRUE;
3543 "output_row"."participation" := FALSE;
3544 "output_row"."overridden" := FALSE;
3545 "output_row"."disabled_out" := FALSE;
3546 LOOP
3547 SELECT INTO "direct_voter_row" * FROM "direct_voter"
3548 WHERE "issue_id" = "issue_id_p"
3549 AND "member_id" = "output_row"."member_id";
3550 IF "direct_voter_row"."member_id" NOTNULL THEN
3551 "output_row"."participation" := TRUE;
3552 "output_row"."scope_out" := NULL;
3553 "output_row"."disabled_out" := NULL;
3554 RETURN NEXT "output_row";
3555 RETURN;
3556 END IF;
3557 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
3558 WHERE "issue_id" = "issue_id_p"
3559 AND "member_id" = "output_row"."member_id";
3560 IF "delegating_voter_row"."member_id" ISNULL THEN
3561 RETURN;
3562 END IF;
3563 "output_row"."scope_out" := "delegating_voter_row"."scope";
3564 RETURN NEXT "output_row";
3565 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
3566 "output_row"."scope_in" := "output_row"."scope_out";
3567 END LOOP;
3568 END;
3569 $$;
3571 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
3572 ( "member"."id"%TYPE,
3573 "member"."id"%TYPE )
3574 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
3577 CREATE FUNCTION "delegation_chain"
3578 ( "member_id_p" "member"."id"%TYPE,
3579 "unit_id_p" "unit"."id"%TYPE,
3580 "area_id_p" "area"."id"%TYPE,
3581 "issue_id_p" "issue"."id"%TYPE,
3582 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3583 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3584 RETURNS SETOF "delegation_chain_row"
3585 LANGUAGE 'plpgsql' STABLE AS $$
3586 DECLARE
3587 "scope_v" "delegation_scope";
3588 "unit_id_v" "unit"."id"%TYPE;
3589 "area_id_v" "area"."id"%TYPE;
3590 "issue_row" "issue"%ROWTYPE;
3591 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
3592 "loop_member_id_v" "member"."id"%TYPE;
3593 "output_row" "delegation_chain_row";
3594 "output_rows" "delegation_chain_row"[];
3595 "simulate_v" BOOLEAN;
3596 "simulate_here_v" BOOLEAN;
3597 "delegation_row" "delegation"%ROWTYPE;
3598 "row_count" INT4;
3599 "i" INT4;
3600 "loop_v" BOOLEAN;
3601 BEGIN
3602 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
3603 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
3604 END IF;
3605 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
3606 "simulate_v" := TRUE;
3607 ELSE
3608 "simulate_v" := FALSE;
3609 END IF;
3610 IF
3611 "unit_id_p" NOTNULL AND
3612 "area_id_p" ISNULL AND
3613 "issue_id_p" ISNULL
3614 THEN
3615 "scope_v" := 'unit';
3616 "unit_id_v" := "unit_id_p";
3617 ELSIF
3618 "unit_id_p" ISNULL AND
3619 "area_id_p" NOTNULL AND
3620 "issue_id_p" ISNULL
3621 THEN
3622 "scope_v" := 'area';
3623 "area_id_v" := "area_id_p";
3624 SELECT "unit_id" INTO "unit_id_v"
3625 FROM "area" WHERE "id" = "area_id_v";
3626 ELSIF
3627 "unit_id_p" ISNULL AND
3628 "area_id_p" ISNULL AND
3629 "issue_id_p" NOTNULL
3630 THEN
3631 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
3632 IF "issue_row"."id" ISNULL THEN
3633 RETURN;
3634 END IF;
3635 IF "issue_row"."closed" NOTNULL THEN
3636 IF "simulate_v" THEN
3637 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
3638 END IF;
3639 FOR "output_row" IN
3640 SELECT * FROM
3641 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
3642 LOOP
3643 RETURN NEXT "output_row";
3644 END LOOP;
3645 RETURN;
3646 END IF;
3647 "scope_v" := 'issue';
3648 SELECT "area_id" INTO "area_id_v"
3649 FROM "issue" WHERE "id" = "issue_id_p";
3650 SELECT "unit_id" INTO "unit_id_v"
3651 FROM "area" WHERE "id" = "area_id_v";
3652 ELSE
3653 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
3654 END IF;
3655 "visited_member_ids" := '{}';
3656 "loop_member_id_v" := NULL;
3657 "output_rows" := '{}';
3658 "output_row"."index" := 0;
3659 "output_row"."member_id" := "member_id_p";
3660 "output_row"."member_valid" := TRUE;
3661 "output_row"."participation" := FALSE;
3662 "output_row"."overridden" := FALSE;
3663 "output_row"."disabled_out" := FALSE;
3664 "output_row"."scope_out" := NULL;
3665 LOOP
3666 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
3667 "loop_member_id_v" := "output_row"."member_id";
3668 ELSE
3669 "visited_member_ids" :=
3670 "visited_member_ids" || "output_row"."member_id";
3671 END IF;
3672 IF "output_row"."participation" ISNULL THEN
3673 "output_row"."overridden" := NULL;
3674 ELSIF "output_row"."participation" THEN
3675 "output_row"."overridden" := TRUE;
3676 END IF;
3677 "output_row"."scope_in" := "output_row"."scope_out";
3678 "output_row"."member_valid" := EXISTS (
3679 SELECT NULL FROM "member" JOIN "privilege"
3680 ON "privilege"."member_id" = "member"."id"
3681 AND "privilege"."unit_id" = "unit_id_v"
3682 WHERE "id" = "output_row"."member_id"
3683 AND "member"."active" AND "privilege"."voting_right"
3684 );
3685 "simulate_here_v" := (
3686 "simulate_v" AND
3687 "output_row"."member_id" = "member_id_p"
3688 );
3689 "delegation_row" := ROW(NULL);
3690 IF "output_row"."member_valid" OR "simulate_here_v" THEN
3691 IF "scope_v" = 'unit' THEN
3692 IF NOT "simulate_here_v" THEN
3693 SELECT * INTO "delegation_row" FROM "delegation"
3694 WHERE "truster_id" = "output_row"."member_id"
3695 AND "unit_id" = "unit_id_v";
3696 END IF;
3697 ELSIF "scope_v" = 'area' THEN
3698 "output_row"."participation" := EXISTS (
3699 SELECT NULL FROM "membership"
3700 WHERE "area_id" = "area_id_p"
3701 AND "member_id" = "output_row"."member_id"
3702 );
3703 IF "simulate_here_v" THEN
3704 IF "simulate_trustee_id_p" ISNULL THEN
3705 SELECT * INTO "delegation_row" FROM "delegation"
3706 WHERE "truster_id" = "output_row"."member_id"
3707 AND "unit_id" = "unit_id_v";
3708 END IF;
3709 ELSE
3710 SELECT * INTO "delegation_row" FROM "delegation"
3711 WHERE "truster_id" = "output_row"."member_id"
3712 AND (
3713 "unit_id" = "unit_id_v" OR
3714 "area_id" = "area_id_v"
3715 )
3716 ORDER BY "scope" DESC;
3717 END IF;
3718 ELSIF "scope_v" = 'issue' THEN
3719 IF "issue_row"."fully_frozen" ISNULL THEN
3720 "output_row"."participation" := EXISTS (
3721 SELECT NULL FROM "interest"
3722 WHERE "issue_id" = "issue_id_p"
3723 AND "member_id" = "output_row"."member_id"
3724 );
3725 ELSE
3726 IF "output_row"."member_id" = "member_id_p" THEN
3727 "output_row"."participation" := EXISTS (
3728 SELECT NULL FROM "direct_voter"
3729 WHERE "issue_id" = "issue_id_p"
3730 AND "member_id" = "output_row"."member_id"
3731 );
3732 ELSE
3733 "output_row"."participation" := NULL;
3734 END IF;
3735 END IF;
3736 IF "simulate_here_v" THEN
3737 IF "simulate_trustee_id_p" ISNULL THEN
3738 SELECT * INTO "delegation_row" FROM "delegation"
3739 WHERE "truster_id" = "output_row"."member_id"
3740 AND (
3741 "unit_id" = "unit_id_v" OR
3742 "area_id" = "area_id_v"
3743 )
3744 ORDER BY "scope" DESC;
3745 END IF;
3746 ELSE
3747 SELECT * INTO "delegation_row" FROM "delegation"
3748 WHERE "truster_id" = "output_row"."member_id"
3749 AND (
3750 "unit_id" = "unit_id_v" OR
3751 "area_id" = "area_id_v" OR
3752 "issue_id" = "issue_id_p"
3753 )
3754 ORDER BY "scope" DESC;
3755 END IF;
3756 END IF;
3757 ELSE
3758 "output_row"."participation" := FALSE;
3759 END IF;
3760 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
3761 "output_row"."scope_out" := "scope_v";
3762 "output_rows" := "output_rows" || "output_row";
3763 "output_row"."member_id" := "simulate_trustee_id_p";
3764 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
3765 "output_row"."scope_out" := "delegation_row"."scope";
3766 "output_rows" := "output_rows" || "output_row";
3767 "output_row"."member_id" := "delegation_row"."trustee_id";
3768 ELSIF "delegation_row"."scope" NOTNULL THEN
3769 "output_row"."scope_out" := "delegation_row"."scope";
3770 "output_row"."disabled_out" := TRUE;
3771 "output_rows" := "output_rows" || "output_row";
3772 EXIT;
3773 ELSE
3774 "output_row"."scope_out" := NULL;
3775 "output_rows" := "output_rows" || "output_row";
3776 EXIT;
3777 END IF;
3778 EXIT WHEN "loop_member_id_v" NOTNULL;
3779 "output_row"."index" := "output_row"."index" + 1;
3780 END LOOP;
3781 "row_count" := array_upper("output_rows", 1);
3782 "i" := 1;
3783 "loop_v" := FALSE;
3784 LOOP
3785 "output_row" := "output_rows"["i"];
3786 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
3787 IF "loop_v" THEN
3788 IF "i" + 1 = "row_count" THEN
3789 "output_row"."loop" := 'last';
3790 ELSIF "i" = "row_count" THEN
3791 "output_row"."loop" := 'repetition';
3792 ELSE
3793 "output_row"."loop" := 'intermediate';
3794 END IF;
3795 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
3796 "output_row"."loop" := 'first';
3797 "loop_v" := TRUE;
3798 END IF;
3799 IF "scope_v" = 'unit' THEN
3800 "output_row"."participation" := NULL;
3801 END IF;
3802 RETURN NEXT "output_row";
3803 "i" := "i" + 1;
3804 END LOOP;
3805 RETURN;
3806 END;
3807 $$;
3809 COMMENT ON FUNCTION "delegation_chain"
3810 ( "member"."id"%TYPE,
3811 "unit"."id"%TYPE,
3812 "area"."id"%TYPE,
3813 "issue"."id"%TYPE,
3814 "member"."id"%TYPE,
3815 BOOLEAN )
3816 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
3820 ---------------------------------------------------------
3821 -- Single row returning function for delegation chains --
3822 ---------------------------------------------------------
3825 CREATE TYPE "delegation_info_loop_type" AS ENUM
3826 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
3828 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
3831 CREATE TYPE "delegation_info_type" AS (
3832 "own_participation" BOOLEAN,
3833 "own_delegation_scope" "delegation_scope",
3834 "first_trustee_id" INT4,
3835 "first_trustee_participation" BOOLEAN,
3836 "first_trustee_ellipsis" BOOLEAN,
3837 "other_trustee_id" INT4,
3838 "other_trustee_participation" BOOLEAN,
3839 "other_trustee_ellipsis" BOOLEAN,
3840 "delegation_loop" "delegation_info_loop_type",
3841 "participating_member_id" INT4 );
3843 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
3845 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
3846 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
3847 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
3848 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
3849 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
3850 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
3851 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
3852 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
3853 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
3854 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
3857 CREATE FUNCTION "delegation_info"
3858 ( "member_id_p" "member"."id"%TYPE,
3859 "unit_id_p" "unit"."id"%TYPE,
3860 "area_id_p" "area"."id"%TYPE,
3861 "issue_id_p" "issue"."id"%TYPE,
3862 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3863 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3864 RETURNS "delegation_info_type"
3865 LANGUAGE 'plpgsql' STABLE AS $$
3866 DECLARE
3867 "current_row" "delegation_chain_row";
3868 "result" "delegation_info_type";
3869 BEGIN
3870 "result"."own_participation" := FALSE;
3871 FOR "current_row" IN
3872 SELECT * FROM "delegation_chain"(
3873 "member_id_p",
3874 "unit_id_p", "area_id_p", "issue_id_p",
3875 "simulate_trustee_id_p", "simulate_default_p")
3876 LOOP
3877 IF
3878 "result"."participating_member_id" ISNULL AND
3879 "current_row"."participation"
3880 THEN
3881 "result"."participating_member_id" := "current_row"."member_id";
3882 END IF;
3883 IF "current_row"."member_id" = "member_id_p" THEN
3884 "result"."own_participation" := "current_row"."participation";
3885 "result"."own_delegation_scope" := "current_row"."scope_out";
3886 IF "current_row"."loop" = 'first' THEN
3887 "result"."delegation_loop" := 'own';
3888 END IF;
3889 ELSIF
3890 "current_row"."member_valid" AND
3891 ( "current_row"."loop" ISNULL OR
3892 "current_row"."loop" != 'repetition' )
3893 THEN
3894 IF "result"."first_trustee_id" ISNULL THEN
3895 "result"."first_trustee_id" := "current_row"."member_id";
3896 "result"."first_trustee_participation" := "current_row"."participation";
3897 "result"."first_trustee_ellipsis" := FALSE;
3898 IF "current_row"."loop" = 'first' THEN
3899 "result"."delegation_loop" := 'first';
3900 END IF;
3901 ELSIF "result"."other_trustee_id" ISNULL THEN
3902 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3903 "result"."other_trustee_id" := "current_row"."member_id";
3904 "result"."other_trustee_participation" := TRUE;
3905 "result"."other_trustee_ellipsis" := FALSE;
3906 IF "current_row"."loop" = 'first' THEN
3907 "result"."delegation_loop" := 'other';
3908 END IF;
3909 ELSE
3910 "result"."first_trustee_ellipsis" := TRUE;
3911 IF "current_row"."loop" = 'first' THEN
3912 "result"."delegation_loop" := 'first_ellipsis';
3913 END IF;
3914 END IF;
3915 ELSE
3916 "result"."other_trustee_ellipsis" := TRUE;
3917 IF "current_row"."loop" = 'first' THEN
3918 "result"."delegation_loop" := 'other_ellipsis';
3919 END IF;
3920 END IF;
3921 END IF;
3922 END LOOP;
3923 RETURN "result";
3924 END;
3925 $$;
3927 COMMENT ON FUNCTION "delegation_info"
3928 ( "member"."id"%TYPE,
3929 "unit"."id"%TYPE,
3930 "area"."id"%TYPE,
3931 "issue"."id"%TYPE,
3932 "member"."id"%TYPE,
3933 BOOLEAN )
3934 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3938 ---------------------------
3939 -- Transaction isolation --
3940 ---------------------------
3943 CREATE FUNCTION "require_transaction_isolation"()
3944 RETURNS VOID
3945 LANGUAGE 'plpgsql' VOLATILE AS $$
3946 BEGIN
3947 IF
3948 current_setting('transaction_isolation') NOT IN
3949 ('repeatable read', 'serializable')
3950 THEN
3951 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3952 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3953 END IF;
3954 RETURN;
3955 END;
3956 $$;
3958 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3961 CREATE FUNCTION "dont_require_transaction_isolation"()
3962 RETURNS VOID
3963 LANGUAGE 'plpgsql' VOLATILE AS $$
3964 BEGIN
3965 IF
3966 current_setting('transaction_isolation') IN
3967 ('repeatable read', 'serializable')
3968 THEN
3969 RAISE WARNING 'Unneccessary transaction isolation level: %',
3970 current_setting('transaction_isolation');
3971 END IF;
3972 RETURN;
3973 END;
3974 $$;
3976 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3980 -------------------------
3981 -- Notification system --
3982 -------------------------
3984 CREATE FUNCTION "get_initiatives_for_notification"
3985 ( "recipient_id_p" "member"."id"%TYPE )
3986 RETURNS SETOF "initiative_for_notification"
3987 LANGUAGE 'plpgsql' VOLATILE AS $$
3988 DECLARE
3989 "result_row" "initiative_for_notification"%ROWTYPE;
3990 "last_draft_id_v" "draft"."id"%TYPE;
3991 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3992 BEGIN
3993 PERFORM "require_transaction_isolation"();
3994 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
3995 FOR "result_row" IN
3996 SELECT * FROM "initiative_for_notification"
3997 WHERE "recipient_id" = "recipient_id_p"
3998 LOOP
3999 SELECT "id" INTO "last_draft_id_v" FROM "draft"
4000 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
4001 ORDER BY "id" DESC LIMIT 1;
4002 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
4003 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
4004 ORDER BY "id" DESC LIMIT 1;
4005 /* compatibility with PostgreSQL 9.1 */
4006 DELETE FROM "notification_initiative_sent"
4007 WHERE "member_id" = "recipient_id_p"
4008 AND "initiative_id" = "result_row"."initiative_id";
4009 INSERT INTO "notification_initiative_sent"
4010 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
4011 VALUES (
4012 "recipient_id_p",
4013 "result_row"."initiative_id",
4014 "last_draft_id_v",
4015 "last_suggestion_id_v" );
4016 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
4017 INSERT INTO "notification_initiative_sent"
4018 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
4019 VALUES (
4020 "recipient_id_p",
4021 "result_row"."initiative_id",
4022 "last_draft_id_v",
4023 "last_suggestion_id_v" )
4024 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
4025 "last_draft_id" = "last_draft_id_v",
4026 "last_suggestion_id" = "last_suggestion_id_v";
4027 */
4028 RETURN NEXT "result_row";
4029 END LOOP;
4030 DELETE FROM "notification_initiative_sent"
4031 USING "initiative", "issue"
4032 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
4033 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
4034 AND "issue"."id" = "initiative"."issue_id"
4035 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
4036 UPDATE "member" SET
4037 "notification_counter" = "notification_counter" + 1,
4038 "notification_sent" = now()
4039 WHERE "id" = "recipient_id_p";
4040 RETURN;
4041 END;
4042 $$;
4044 COMMENT ON FUNCTION "get_initiatives_for_notification"
4045 ( "member"."id"%TYPE )
4046 IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
4050 ------------------------------------------------------------------------
4051 -- Regular tasks, except calculcation of snapshots and voting results --
4052 ------------------------------------------------------------------------
4055 CREATE FUNCTION "check_activity"()
4056 RETURNS VOID
4057 LANGUAGE 'plpgsql' VOLATILE AS $$
4058 DECLARE
4059 "system_setting_row" "system_setting"%ROWTYPE;
4060 BEGIN
4061 PERFORM "dont_require_transaction_isolation"();
4062 SELECT * INTO "system_setting_row" FROM "system_setting";
4063 IF "system_setting_row"."member_ttl" NOTNULL THEN
4064 UPDATE "member" SET "active" = FALSE
4065 WHERE "active" = TRUE
4066 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
4067 END IF;
4068 RETURN;
4069 END;
4070 $$;
4072 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
4075 CREATE FUNCTION "calculate_member_counts"()
4076 RETURNS VOID
4077 LANGUAGE 'plpgsql' VOLATILE AS $$
4078 BEGIN
4079 PERFORM "require_transaction_isolation"();
4080 DELETE FROM "member_count";
4081 INSERT INTO "member_count" ("total_count")
4082 SELECT "total_count" FROM "member_count_view";
4083 UPDATE "unit" SET "member_count" = "view"."member_count"
4084 FROM "unit_member_count" AS "view"
4085 WHERE "view"."unit_id" = "unit"."id";
4086 RETURN;
4087 END;
4088 $$;
4090 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"';
4094 ------------------------------------
4095 -- Calculation of harmonic weight --
4096 ------------------------------------
4099 CREATE VIEW "remaining_harmonic_supporter_weight" AS
4100 SELECT
4101 "direct_interest_snapshot"."snapshot_id",
4102 "direct_interest_snapshot"."issue_id",
4103 "direct_interest_snapshot"."member_id",
4104 "direct_interest_snapshot"."weight" AS "weight_num",
4105 count("initiative"."id") AS "weight_den"
4106 FROM "issue"
4107 JOIN "direct_interest_snapshot"
4108 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
4109 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
4110 JOIN "initiative"
4111 ON "issue"."id" = "initiative"."issue_id"
4112 AND "initiative"."harmonic_weight" ISNULL
4113 JOIN "direct_supporter_snapshot"
4114 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4115 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4116 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
4117 AND (
4118 "direct_supporter_snapshot"."satisfied" = TRUE OR
4119 coalesce("initiative"."admitted", FALSE) = FALSE
4120 )
4121 GROUP BY
4122 "direct_interest_snapshot"."snapshot_id",
4123 "direct_interest_snapshot"."issue_id",
4124 "direct_interest_snapshot"."member_id",
4125 "direct_interest_snapshot"."weight";
4127 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
4130 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
4131 SELECT
4132 "initiative"."issue_id",
4133 "initiative"."id" AS "initiative_id",
4134 "initiative"."admitted",
4135 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
4136 "remaining_harmonic_supporter_weight"."weight_den"
4137 FROM "remaining_harmonic_supporter_weight"
4138 JOIN "initiative"
4139 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
4140 AND "initiative"."harmonic_weight" ISNULL
4141 JOIN "direct_supporter_snapshot"
4142 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4143 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4144 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
4145 AND (
4146 "direct_supporter_snapshot"."satisfied" = TRUE OR
4147 coalesce("initiative"."admitted", FALSE) = FALSE
4148 )
4149 GROUP BY
4150 "initiative"."issue_id",
4151 "initiative"."id",
4152 "initiative"."admitted",
4153 "remaining_harmonic_supporter_weight"."weight_den";
4155 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
4158 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
4159 SELECT
4160 "issue_id",
4161 "id" AS "initiative_id",
4162 "admitted",
4163 0 AS "weight_num",
4164 1 AS "weight_den"
4165 FROM "initiative"
4166 WHERE "harmonic_weight" ISNULL;
4168 COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all';
4171 CREATE FUNCTION "set_harmonic_initiative_weights"
4172 ( "issue_id_p" "issue"."id"%TYPE )
4173 RETURNS VOID
4174 LANGUAGE 'plpgsql' VOLATILE AS $$
4175 DECLARE
4176 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
4177 "i" INT4;
4178 "count_v" INT4;
4179 "summand_v" FLOAT;
4180 "id_ary" INT4[];
4181 "weight_ary" FLOAT[];
4182 "min_weight_v" FLOAT;
4183 BEGIN
4184 PERFORM "require_transaction_isolation"();
4185 UPDATE "initiative" SET "harmonic_weight" = NULL
4186 WHERE "issue_id" = "issue_id_p";
4187 LOOP
4188 "min_weight_v" := NULL;
4189 "i" := 0;
4190 "count_v" := 0;
4191 FOR "weight_row" IN
4192 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
4193 WHERE "issue_id" = "issue_id_p"
4194 AND (
4195 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4196 SELECT NULL FROM "initiative"
4197 WHERE "issue_id" = "issue_id_p"
4198 AND "harmonic_weight" ISNULL
4199 AND coalesce("admitted", FALSE) = FALSE
4200 )
4201 )
4202 UNION ALL -- needed for corner cases
4203 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
4204 WHERE "issue_id" = "issue_id_p"
4205 AND (
4206 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4207 SELECT NULL FROM "initiative"
4208 WHERE "issue_id" = "issue_id_p"
4209 AND "harmonic_weight" ISNULL
4210 AND coalesce("admitted", FALSE) = FALSE
4211 )
4212 )
4213 ORDER BY "initiative_id" DESC, "weight_den" DESC
4214 -- NOTE: non-admitted initiatives placed first (at last positions),
4215 -- latest initiatives treated worse in case of tie
4216 LOOP
4217 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
4218 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
4219 "i" := "i" + 1;
4220 "count_v" := "i";
4221 "id_ary"["i"] := "weight_row"."initiative_id";
4222 "weight_ary"["i"] := "summand_v";
4223 ELSE
4224 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
4225 END IF;
4226 END LOOP;
4227 EXIT WHEN "count_v" = 0;
4228 "i" := 1;
4229 LOOP
4230 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
4231 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
4232 "min_weight_v" := "weight_ary"["i"];
4233 END IF;
4234 "i" := "i" + 1;
4235 EXIT WHEN "i" > "count_v";
4236 END LOOP;
4237 "i" := 1;
4238 LOOP
4239 IF "weight_ary"["i"] = "min_weight_v" THEN
4240 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
4241 WHERE "id" = "id_ary"["i"];
4242 EXIT;
4243 END IF;
4244 "i" := "i" + 1;
4245 END LOOP;
4246 END LOOP;
4247 UPDATE "initiative" SET "harmonic_weight" = 0
4248 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
4249 END;
4250 $$;
4252 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
4253 ( "issue"."id"%TYPE )
4254 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
4258 ------------------------------
4259 -- Calculation of snapshots --
4260 ------------------------------
4263 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
4264 ( "snapshot_id_p" "snapshot"."id"%TYPE,
4265 "issue_id_p" "issue"."id"%TYPE,
4266 "member_id_p" "member"."id"%TYPE,
4267 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4268 RETURNS "direct_interest_snapshot"."weight"%TYPE
4269 LANGUAGE 'plpgsql' VOLATILE AS $$
4270 DECLARE
4271 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4272 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
4273 "weight_v" INT4;
4274 "sub_weight_v" INT4;
4275 BEGIN
4276 PERFORM "require_transaction_isolation"();
4277 "weight_v" := 0;
4278 FOR "issue_delegation_row" IN
4279 SELECT * FROM "issue_delegation"
4280 WHERE "trustee_id" = "member_id_p"
4281 AND "issue_id" = "issue_id_p"
4282 LOOP
4283 IF NOT EXISTS (
4284 SELECT NULL FROM "direct_interest_snapshot"
4285 WHERE "snapshot_id" = "snapshot_id_p"
4286 AND "issue_id" = "issue_id_p"
4287 AND "member_id" = "issue_delegation_row"."truster_id"
4288 ) AND NOT EXISTS (
4289 SELECT NULL FROM "delegating_interest_snapshot"
4290 WHERE "snapshot_id" = "snapshot_id_p"
4291 AND "issue_id" = "issue_id_p"
4292 AND "member_id" = "issue_delegation_row"."truster_id"
4293 ) THEN
4294 "delegate_member_ids_v" :=
4295 "member_id_p" || "delegate_member_ids_p";
4296 INSERT INTO "delegating_interest_snapshot" (
4297 "snapshot_id",
4298 "issue_id",
4299 "member_id",
4300 "scope",
4301 "delegate_member_ids"
4302 ) VALUES (
4303 "snapshot_id_p",
4304 "issue_id_p",
4305 "issue_delegation_row"."truster_id",
4306 "issue_delegation_row"."scope",
4307 "delegate_member_ids_v"
4308 );
4309 "sub_weight_v" := 1 +
4310 "weight_of_added_delegations_for_snapshot"(
4311 "snapshot_id_p",
4312 "issue_id_p",
4313 "issue_delegation_row"."truster_id",
4314 "delegate_member_ids_v"
4315 );
4316 UPDATE "delegating_interest_snapshot"
4317 SET "weight" = "sub_weight_v"
4318 WHERE "snapshot_id" = "snapshot_id_p"
4319 AND "issue_id" = "issue_id_p"
4320 AND "member_id" = "issue_delegation_row"."truster_id";
4321 "weight_v" := "weight_v" + "sub_weight_v";
4322 END IF;
4323 END LOOP;
4324 RETURN "weight_v";
4325 END;
4326 $$;
4328 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
4329 ( "snapshot"."id"%TYPE,
4330 "issue"."id"%TYPE,
4331 "member"."id"%TYPE,
4332 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4333 IS 'Helper function for "fill_snapshot" function';
4336 CREATE FUNCTION "take_snapshot"
4337 ( "issue_id_p" "issue"."id"%TYPE )
4338 RETURNS "snapshot"."id"%TYPE
4339 LANGUAGE 'plpgsql' VOLATILE AS $$
4340 DECLARE
4341 "snapshot_id_v" "snapshot"."id"%TYPE;
4342 "issue_id_v" "issue"."id"%TYPE;
4343 "member_id_v" "member"."id"%TYPE;
4344 BEGIN
4345 PERFORM "require_transaction_isolation"();
4346 INSERT INTO "snapshot" DEFAULT VALUES
4347 RETURNING "id" INTO "snapshot_id_v";
4348 FOR "issue_id_v" IN
4349 SELECT "id" FROM "issue"
4350 WHERE CASE WHEN "issue_id_p" ISNULL THEN
4351 "state" = 'admission'
4352 ELSE
4353 "id" = "issue_id_p"
4354 END
4355 LOOP
4356 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
4357 VALUES ("snapshot_id_v", "issue_id_v");
4358 INSERT INTO "direct_interest_snapshot"
4359 ("snapshot_id", "issue_id", "member_id")
4360 SELECT
4361 "snapshot_id_v" AS "snapshot_id",
4362 "issue_id_v" AS "issue_id",
4363 "member"."id" AS "member_id"
4364 FROM "issue"
4365 JOIN "area" ON "issue"."area_id" = "area"."id"
4366 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
4367 JOIN "member" ON "interest"."member_id" = "member"."id"
4368 JOIN "privilege"
4369 ON "privilege"."unit_id" = "area"."unit_id"
4370 AND "privilege"."member_id" = "member"."id"
4371 WHERE "issue"."id" = "issue_id_v"
4372 AND "member"."active" AND "privilege"."voting_right";
4373 FOR "member_id_v" IN
4374 SELECT "member_id" FROM "direct_interest_snapshot"
4375 WHERE "snapshot_id" = "snapshot_id_v"
4376 AND "issue_id" = "issue_id_v"
4377 LOOP
4378 UPDATE "direct_interest_snapshot" SET
4379 "weight" = 1 +
4380 "weight_of_added_delegations_for_snapshot"(
4381 "snapshot_id_v",
4382 "issue_id_v",
4383 "member_id_v",
4384 '{}'
4385 )
4386 WHERE "snapshot_id" = "snapshot_id_v"
4387 AND "issue_id" = "issue_id_v"
4388 AND "member_id" = "member_id_v";
4389 END LOOP;
4390 INSERT INTO "direct_supporter_snapshot"
4391 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
4392 "draft_id", "informed", "satisfied" )
4393 SELECT
4394 "snapshot_id_v" AS "snapshot_id",
4395 "issue_id_v" AS "issue_id",
4396 "initiative"."id" AS "initiative_id",
4397 "supporter"."member_id" AS "member_id",
4398 "supporter"."draft_id" AS "draft_id",
4399 "supporter"."draft_id" = "current_draft"."id" AS "informed",
4400 NOT EXISTS (
4401 SELECT NULL FROM "critical_opinion"
4402 WHERE "initiative_id" = "initiative"."id"
4403 AND "member_id" = "supporter"."member_id"
4404 ) AS "satisfied"
4405 FROM "initiative"
4406 JOIN "supporter"
4407 ON "supporter"."initiative_id" = "initiative"."id"
4408 JOIN "current_draft"
4409 ON "initiative"."id" = "current_draft"."initiative_id"
4410 JOIN "direct_interest_snapshot"
4411 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
4412 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
4413 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
4414 WHERE "initiative"."issue_id" = "issue_id_v";
4415 DELETE FROM "temporary_suggestion_counts";
4416 INSERT INTO "temporary_suggestion_counts"
4417 ( "id",
4418 "minus2_unfulfilled_count", "minus2_fulfilled_count",
4419 "minus1_unfulfilled_count", "minus1_fulfilled_count",
4420 "plus1_unfulfilled_count", "plus1_fulfilled_count",
4421 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
4422 SELECT
4423 "suggestion"."id",
4424 ( SELECT coalesce(sum("di"."weight"), 0)
4425 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4426 ON "di"."snapshot_id" = "snapshot_id_v"
4427 AND "di"."issue_id" = "issue_id_v"
4428 AND "di"."member_id" = "opinion"."member_id"
4429 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4430 AND "opinion"."degree" = -2
4431 AND "opinion"."fulfilled" = FALSE
4432 ) AS "minus2_unfulfilled_count",
4433 ( SELECT coalesce(sum("di"."weight"), 0)
4434 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4435 ON "di"."snapshot_id" = "snapshot_id_v"
4436 AND "di"."issue_id" = "issue_id_v"
4437 AND "di"."member_id" = "opinion"."member_id"
4438 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4439 AND "opinion"."degree" = -2
4440 AND "opinion"."fulfilled" = TRUE
4441 ) AS "minus2_fulfilled_count",
4442 ( SELECT coalesce(sum("di"."weight"), 0)
4443 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4444 ON "di"."snapshot_id" = "snapshot_id_v"
4445 AND "di"."issue_id" = "issue_id_v"
4446 AND "di"."member_id" = "opinion"."member_id"
4447 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4448 AND "opinion"."degree" = -1
4449 AND "opinion"."fulfilled" = FALSE
4450 ) AS "minus1_unfulfilled_count",
4451 ( SELECT coalesce(sum("di"."weight"), 0)
4452 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4453 ON "di"."snapshot_id" = "snapshot_id_v"
4454 AND "di"."issue_id" = "issue_id_v"
4455 AND "di"."member_id" = "opinion"."member_id"
4456 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4457 AND "opinion"."degree" = -1
4458 AND "opinion"."fulfilled" = TRUE
4459 ) AS "minus1_fulfilled_count",
4460 ( SELECT coalesce(sum("di"."weight"), 0)
4461 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4462 ON "di"."snapshot_id" = "snapshot_id_v"
4463 AND "di"."issue_id" = "issue_id_v"
4464 AND "di"."member_id" = "opinion"."member_id"
4465 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4466 AND "opinion"."degree" = 1
4467 AND "opinion"."fulfilled" = FALSE
4468 ) AS "plus1_unfulfilled_count",
4469 ( SELECT coalesce(sum("di"."weight"), 0)
4470 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4471 ON "di"."snapshot_id" = "snapshot_id_v"
4472 AND "di"."issue_id" = "issue_id_v"
4473 AND "di"."member_id" = "opinion"."member_id"
4474 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4475 AND "opinion"."degree" = 1
4476 AND "opinion"."fulfilled" = TRUE
4477 ) AS "plus1_fulfilled_count",
4478 ( SELECT coalesce(sum("di"."weight"), 0)
4479 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4480 ON "di"."snapshot_id" = "snapshot_id_v"
4481 AND "di"."issue_id" = "issue_id_v"
4482 AND "di"."member_id" = "opinion"."member_id"
4483 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4484 AND "opinion"."degree" = 2
4485 AND "opinion"."fulfilled" = FALSE
4486 ) AS "plus2_unfulfilled_count",
4487 ( SELECT coalesce(sum("di"."weight"), 0)
4488 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4489 ON "di"."snapshot_id" = "snapshot_id_v"
4490 AND "di"."issue_id" = "issue_id_v"
4491 AND "di"."member_id" = "opinion"."member_id"
4492 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4493 AND "opinion"."degree" = 2
4494 AND "opinion"."fulfilled" = TRUE
4495 ) AS "plus2_fulfilled_count"
4496 FROM "suggestion" JOIN "initiative"
4497 ON "suggestion"."initiative_id" = "initiative"."id"
4498 WHERE "initiative"."issue_id" = "issue_id_v";
4499 END LOOP;
4500 RETURN "snapshot_id_v";
4501 END;
4502 $$;
4504 COMMENT ON FUNCTION "take_snapshot"
4505 ( "issue"."id"%TYPE )
4506 IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the argument is NULL, for all issues in ''admission'' phase. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';
4509 CREATE FUNCTION "finish_snapshot"
4510 ( "issue_id_p" "issue"."id"%TYPE )
4511 RETURNS VOID
4512 LANGUAGE 'plpgsql' VOLATILE AS $$
4513 DECLARE
4514 "snapshot_id_v" "snapshot"."id"%TYPE;
4515 BEGIN
4516 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
4517 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
4518 ORDER BY "id" DESC LIMIT 1;
4519 UPDATE "issue" SET
4520 "latest_snapshot_id" = "snapshot_id_v",
4521 "population" = (
4522 SELECT coalesce(sum("weight"), 0)
4523 FROM "direct_interest_snapshot"
4524 WHERE "snapshot_id" = "snapshot_id_v"
4525 AND "issue_id" = "issue_id_p"
4526 )
4527 WHERE "id" = "issue_id_p";
4528 UPDATE "initiative" SET
4529 "supporter_count" = (
4530 SELECT coalesce(sum("di"."weight"), 0)
4531 FROM "direct_interest_snapshot" AS "di"
4532 JOIN "direct_supporter_snapshot" AS "ds"
4533 ON "di"."member_id" = "ds"."member_id"
4534 WHERE "di"."snapshot_id" = "snapshot_id_v"
4535 AND "di"."issue_id" = "issue_id_p"
4536 AND "ds"."snapshot_id" = "snapshot_id_v"
4537 AND "ds"."initiative_id" = "initiative"."id"
4538 ),
4539 "informed_supporter_count" = (
4540 SELECT coalesce(sum("di"."weight"), 0)
4541 FROM "direct_interest_snapshot" AS "di"
4542 JOIN "direct_supporter_snapshot" AS "ds"
4543 ON "di"."member_id" = "ds"."member_id"
4544 WHERE "di"."snapshot_id" = "snapshot_id_v"
4545 AND "di"."issue_id" = "issue_id_p"
4546 AND "ds"."snapshot_id" = "snapshot_id_v"
4547 AND "ds"."initiative_id" = "initiative"."id"
4548 AND "ds"."informed"
4549 ),
4550 "satisfied_supporter_count" = (
4551 SELECT coalesce(sum("di"."weight"), 0)
4552 FROM "direct_interest_snapshot" AS "di"
4553 JOIN "direct_supporter_snapshot" AS "ds"
4554 ON "di"."member_id" = "ds"."member_id"
4555 WHERE "di"."snapshot_id" = "snapshot_id_v"
4556 AND "di"."issue_id" = "issue_id_p"
4557 AND "ds"."snapshot_id" = "snapshot_id_v"
4558 AND "ds"."initiative_id" = "initiative"."id"
4559 AND "ds"."satisfied"
4560 ),
4561 "satisfied_informed_supporter_count" = (
4562 SELECT coalesce(sum("di"."weight"), 0)
4563 FROM "direct_interest_snapshot" AS "di"
4564 JOIN "direct_supporter_snapshot" AS "ds"
4565 ON "di"."member_id" = "ds"."member_id"
4566 WHERE "di"."snapshot_id" = "snapshot_id_v"
4567 AND "di"."issue_id" = "issue_id_p"
4568 AND "ds"."snapshot_id" = "snapshot_id_v"
4569 AND "ds"."initiative_id" = "initiative"."id"
4570 AND "ds"."informed"
4571 AND "ds"."satisfied"
4572 )
4573 WHERE "issue_id" = "issue_id_p";
4574 UPDATE "suggestion" SET
4575 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
4576 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
4577 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
4578 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
4579 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
4580 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
4581 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
4582 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
4583 FROM "temporary_suggestion_counts" AS "temp", "initiative"
4584 WHERE "temp"."id" = "suggestion"."id"
4585 AND "initiative"."issue_id" = "issue_id_p"
4586 AND "suggestion"."initiative_id" = "initiative"."id";
4587 DELETE FROM "temporary_suggestion_counts";
4588 RETURN;
4589 END;
4590 $$;
4592 COMMENT ON FUNCTION "finish_snapshot"
4593 ( "issue"."id"%TYPE )
4594 IS 'After calling "take_snapshot", this function "finish_snapshot" needs to be called for every issue in the snapshot (separate function calls keep locking time minimal)';
4598 -----------------------
4599 -- Counting of votes --
4600 -----------------------
4603 CREATE FUNCTION "weight_of_added_vote_delegations"
4604 ( "issue_id_p" "issue"."id"%TYPE,
4605 "member_id_p" "member"."id"%TYPE,
4606 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
4607 RETURNS "direct_voter"."weight"%TYPE
4608 LANGUAGE 'plpgsql' VOLATILE AS $$
4609 DECLARE
4610 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4611 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
4612 "weight_v" INT4;
4613 "sub_weight_v" INT4;
4614 BEGIN
4615 PERFORM "require_transaction_isolation"();
4616 "weight_v" := 0;
4617 FOR "issue_delegation_row" IN
4618 SELECT * FROM "issue_delegation"
4619 WHERE "trustee_id" = "member_id_p"
4620 AND "issue_id" = "issue_id_p"
4621 LOOP
4622 IF NOT EXISTS (
4623 SELECT NULL FROM "direct_voter"
4624 WHERE "member_id" = "issue_delegation_row"."truster_id"
4625 AND "issue_id" = "issue_id_p"
4626 ) AND NOT EXISTS (
4627 SELECT NULL FROM "delegating_voter"
4628 WHERE "member_id" = "issue_delegation_row"."truster_id"
4629 AND "issue_id" = "issue_id_p"
4630 ) THEN
4631 "delegate_member_ids_v" :=
4632 "member_id_p" || "delegate_member_ids_p";
4633 INSERT INTO "delegating_voter" (
4634 "issue_id",
4635 "member_id",
4636 "scope",
4637 "delegate_member_ids"
4638 ) VALUES (
4639 "issue_id_p",
4640 "issue_delegation_row"."truster_id",
4641 "issue_delegation_row"."scope",
4642 "delegate_member_ids_v"
4643 );
4644 "sub_weight_v" := 1 +
4645 "weight_of_added_vote_delegations"(
4646 "issue_id_p",
4647 "issue_delegation_row"."truster_id",
4648 "delegate_member_ids_v"
4649 );
4650 UPDATE "delegating_voter"
4651 SET "weight" = "sub_weight_v"
4652 WHERE "issue_id" = "issue_id_p"
4653 AND "member_id" = "issue_delegation_row"."truster_id";
4654 "weight_v" := "weight_v" + "sub_weight_v";
4655 END IF;
4656 END LOOP;
4657 RETURN "weight_v";
4658 END;
4659 $$;
4661 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
4662 ( "issue"."id"%TYPE,
4663 "member"."id"%TYPE,
4664 "delegating_voter"."delegate_member_ids"%TYPE )
4665 IS 'Helper function for "add_vote_delegations" function';
4668 CREATE FUNCTION "add_vote_delegations"
4669 ( "issue_id_p" "issue"."id"%TYPE )
4670 RETURNS VOID
4671 LANGUAGE 'plpgsql' VOLATILE AS $$
4672 DECLARE
4673 "member_id_v" "member"."id"%TYPE;
4674 BEGIN
4675 PERFORM "require_transaction_isolation"();
4676 FOR "member_id_v" IN
4677 SELECT "member_id" FROM "direct_voter"
4678 WHERE "issue_id" = "issue_id_p"
4679 LOOP
4680 UPDATE "direct_voter" SET
4681 "weight" = "weight" + "weight_of_added_vote_delegations"(
4682 "issue_id_p",
4683 "member_id_v",
4684 '{}'
4685 )
4686 WHERE "member_id" = "member_id_v"
4687 AND "issue_id" = "issue_id_p";
4688 END LOOP;
4689 RETURN;
4690 END;
4691 $$;
4693 COMMENT ON FUNCTION "add_vote_delegations"
4694 ( "issue_id_p" "issue"."id"%TYPE )
4695 IS 'Helper function for "close_voting" function';
4698 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4699 RETURNS VOID
4700 LANGUAGE 'plpgsql' VOLATILE AS $$
4701 DECLARE
4702 "area_id_v" "area"."id"%TYPE;
4703 "unit_id_v" "unit"."id"%TYPE;
4704 "member_id_v" "member"."id"%TYPE;
4705 BEGIN
4706 PERFORM "require_transaction_isolation"();
4707 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4708 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4709 -- override protection triggers:
4710 INSERT INTO "temporary_transaction_data" ("key", "value")
4711 VALUES ('override_protection_triggers', TRUE::TEXT);
4712 -- delete timestamp of voting comment:
4713 UPDATE "direct_voter" SET "comment_changed" = NULL
4714 WHERE "issue_id" = "issue_id_p";
4715 -- delete delegating votes (in cases of manual reset of issue state):
4716 DELETE FROM "delegating_voter"
4717 WHERE "issue_id" = "issue_id_p";
4718 -- delete votes from non-privileged voters:
4719 DELETE FROM "direct_voter"
4720 USING (
4721 SELECT
4722 "direct_voter"."member_id"
4723 FROM "direct_voter"
4724 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4725 LEFT JOIN "privilege"
4726 ON "privilege"."unit_id" = "unit_id_v"
4727 AND "privilege"."member_id" = "direct_voter"."member_id"
4728 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4729 "member"."active" = FALSE OR
4730 "privilege"."voting_right" ISNULL OR
4731 "privilege"."voting_right" = FALSE
4732 )
4733 ) AS "subquery"
4734 WHERE "direct_voter"."issue_id" = "issue_id_p"
4735 AND "direct_voter"."member_id" = "subquery"."member_id";
4736 -- consider delegations:
4737 UPDATE "direct_voter" SET "weight" = 1
4738 WHERE "issue_id" = "issue_id_p";
4739 PERFORM "add_vote_delegations"("issue_id_p");
4740 -- mark first preferences:
4741 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4742 FROM (
4743 SELECT
4744 "vote"."initiative_id",
4745 "vote"."member_id",
4746 CASE WHEN "vote"."grade" > 0 THEN
4747 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4748 ELSE NULL
4749 END AS "first_preference"
4750 FROM "vote"
4751 JOIN "initiative" -- NOTE: due to missing index on issue_id
4752 ON "vote"."issue_id" = "initiative"."issue_id"
4753 JOIN "vote" AS "agg"
4754 ON "initiative"."id" = "agg"."initiative_id"
4755 AND "vote"."member_id" = "agg"."member_id"
4756 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4757 ) AS "subquery"
4758 WHERE "vote"."issue_id" = "issue_id_p"
4759 AND "vote"."initiative_id" = "subquery"."initiative_id"
4760 AND "vote"."member_id" = "subquery"."member_id";
4761 -- finish overriding protection triggers (avoids garbage):
4762 DELETE FROM "temporary_transaction_data"
4763 WHERE "key" = 'override_protection_triggers';
4764 -- materialize battle_view:
4765 -- NOTE: "closed" column of issue must be set at this point
4766 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4767 INSERT INTO "battle" (
4768 "issue_id",
4769 "winning_initiative_id", "losing_initiative_id",
4770 "count"
4771 ) SELECT
4772 "issue_id",
4773 "winning_initiative_id", "losing_initiative_id",
4774 "count"
4775 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4776 -- set voter count:
4777 UPDATE "issue" SET
4778 "voter_count" = (
4779 SELECT coalesce(sum("weight"), 0)
4780 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4781 )
4782 WHERE "id" = "issue_id_p";
4783 -- copy "positive_votes" and "negative_votes" from "battle" table:
4784 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4785 UPDATE "initiative" SET
4786 "first_preference_votes" = 0,
4787 "positive_votes" = "battle_win"."count",
4788 "negative_votes" = "battle_lose"."count"
4789 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4790 WHERE
4791 "battle_win"."issue_id" = "issue_id_p" AND
4792 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4793 "battle_win"."losing_initiative_id" ISNULL AND
4794 "battle_lose"."issue_id" = "issue_id_p" AND
4795 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4796 "battle_lose"."winning_initiative_id" ISNULL;
4797 -- calculate "first_preference_votes":
4798 -- NOTE: will only set values not equal to zero
4799 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4800 FROM (
4801 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4802 FROM "vote" JOIN "direct_voter"
4803 ON "vote"."issue_id" = "direct_voter"."issue_id"
4804 AND "vote"."member_id" = "direct_voter"."member_id"
4805 WHERE "vote"."first_preference"
4806 GROUP BY "vote"."initiative_id"
4807 ) AS "subquery"
4808 WHERE "initiative"."issue_id" = "issue_id_p"
4809 AND "initiative"."admitted"
4810 AND "initiative"."id" = "subquery"."initiative_id";
4811 END;
4812 $$;
4814 COMMENT ON FUNCTION "close_voting"
4815 ( "issue"."id"%TYPE )
4816 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.';
4819 CREATE FUNCTION "defeat_strength"
4820 ( "positive_votes_p" INT4,
4821 "negative_votes_p" INT4,
4822 "defeat_strength_p" "defeat_strength" )
4823 RETURNS INT8
4824 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4825 BEGIN
4826 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4827 IF "positive_votes_p" > "negative_votes_p" THEN
4828 RETURN "positive_votes_p";
4829 ELSE
4830 RETURN 0;
4831 END IF;
4832 ELSE
4833 IF "positive_votes_p" > "negative_votes_p" THEN
4834 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4835 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4836 RETURN 0;
4837 ELSE
4838 RETURN -1;
4839 END IF;
4840 END IF;
4841 END;
4842 $$;
4844 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
4847 CREATE FUNCTION "secondary_link_strength"
4848 ( "initiative1_ord_p" INT4,
4849 "initiative2_ord_p" INT4,
4850 "tie_breaking_p" "tie_breaking" )
4851 RETURNS INT8
4852 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4853 BEGIN
4854 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4855 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4856 END IF;
4857 RETURN (
4858 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4859 0
4860 ELSE
4861 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4862 1::INT8 << 62
4863 ELSE 0 END
4864 +
4865 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4866 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4867 ELSE
4868 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4869 END
4870 END
4871 );
4872 END;
4873 $$;
4875 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4878 CREATE TYPE "link_strength" AS (
4879 "primary" INT8,
4880 "secondary" INT8 );
4882 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
4885 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4886 RETURNS "link_strength"[][]
4887 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4888 DECLARE
4889 "dimension_v" INT4;
4890 "matrix_p" "link_strength"[][];
4891 "i" INT4;
4892 "j" INT4;
4893 "k" INT4;
4894 BEGIN
4895 "dimension_v" := array_upper("matrix_d", 1);
4896 "matrix_p" := "matrix_d";
4897 "i" := 1;
4898 LOOP
4899 "j" := 1;
4900 LOOP
4901 IF "i" != "j" THEN
4902 "k" := 1;
4903 LOOP
4904 IF "i" != "k" AND "j" != "k" THEN
4905 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4906 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4907 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4908 END IF;
4909 ELSE
4910 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4911 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4912 END IF;
4913 END IF;
4914 END IF;
4915 EXIT WHEN "k" = "dimension_v";
4916 "k" := "k" + 1;
4917 END LOOP;
4918 END IF;
4919 EXIT WHEN "j" = "dimension_v";
4920 "j" := "j" + 1;
4921 END LOOP;
4922 EXIT WHEN "i" = "dimension_v";
4923 "i" := "i" + 1;
4924 END LOOP;
4925 RETURN "matrix_p";
4926 END;
4927 $$;
4929 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4932 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4933 RETURNS VOID
4934 LANGUAGE 'plpgsql' VOLATILE AS $$
4935 DECLARE
4936 "issue_row" "issue"%ROWTYPE;
4937 "policy_row" "policy"%ROWTYPE;
4938 "dimension_v" INT4;
4939 "matrix_a" INT4[][]; -- absolute votes
4940 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4941 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4942 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4943 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4944 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4945 "i" INT4;
4946 "j" INT4;
4947 "m" INT4;
4948 "n" INT4;
4949 "battle_row" "battle"%ROWTYPE;
4950 "rank_ary" INT4[];
4951 "rank_v" INT4;
4952 "initiative_id_v" "initiative"."id"%TYPE;
4953 BEGIN
4954 PERFORM "require_transaction_isolation"();
4955 SELECT * INTO "issue_row"
4956 FROM "issue" WHERE "id" = "issue_id_p";
4957 SELECT * INTO "policy_row"
4958 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4959 SELECT count(1) INTO "dimension_v"
4960 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4961 -- create "matrix_a" with absolute number of votes in pairwise
4962 -- comparison:
4963 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4964 "i" := 1;
4965 "j" := 2;
4966 FOR "battle_row" IN
4967 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4968 ORDER BY
4969 "winning_initiative_id" NULLS FIRST,
4970 "losing_initiative_id" NULLS FIRST
4971 LOOP
4972 "matrix_a"["i"]["j"] := "battle_row"."count";
4973 IF "j" = "dimension_v" THEN
4974 "i" := "i" + 1;
4975 "j" := 1;
4976 ELSE
4977 "j" := "j" + 1;
4978 IF "j" = "i" THEN
4979 "j" := "j" + 1;
4980 END IF;
4981 END IF;
4982 END LOOP;
4983 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4984 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4985 END IF;
4986 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4987 -- and "secondary_link_strength" functions:
4988 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4989 "i" := 1;
4990 LOOP
4991 "j" := 1;
4992 LOOP
4993 IF "i" != "j" THEN
4994 "matrix_d"["i"]["j"] := (
4995 "defeat_strength"(
4996 "matrix_a"["i"]["j"],
4997 "matrix_a"["j"]["i"],
4998 "policy_row"."defeat_strength"
4999 ),
5000 "secondary_link_strength"(
5001 "i",
5002 "j",
5003 "policy_row"."tie_breaking"
5004 )
5005 )::"link_strength";
5006 END IF;
5007 EXIT WHEN "j" = "dimension_v";
5008 "j" := "j" + 1;
5009 END LOOP;
5010 EXIT WHEN "i" = "dimension_v";
5011 "i" := "i" + 1;
5012 END LOOP;
5013 -- find best paths:
5014 "matrix_p" := "find_best_paths"("matrix_d");
5015 -- create partial order:
5016 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
5017 "i" := 1;
5018 LOOP
5019 "j" := "i" + 1;
5020 LOOP
5021 IF "i" != "j" THEN
5022 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
5023 "matrix_b"["i"]["j"] := TRUE;
5024 "matrix_b"["j"]["i"] := FALSE;
5025 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
5026 "matrix_b"["i"]["j"] := FALSE;
5027 "matrix_b"["j"]["i"] := TRUE;
5028 END IF;
5029 END IF;
5030 EXIT WHEN "j" = "dimension_v";
5031 "j" := "j" + 1;
5032 END LOOP;
5033 EXIT WHEN "i" = "dimension_v" - 1;
5034 "i" := "i" + 1;
5035 END LOOP;
5036 -- tie-breaking by forbidding shared weakest links in beat-paths
5037 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
5038 -- is performed later by initiative id):
5039 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
5040 "m" := 1;
5041 LOOP
5042 "n" := "m" + 1;
5043 LOOP
5044 -- only process those candidates m and n, which are tied:
5045 IF "matrix_b"["m"]["n"] ISNULL THEN
5046 -- start with beat-paths prior tie-breaking:
5047 "matrix_t" := "matrix_p";
5048 -- start with all links allowed:
5049 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
5050 LOOP
5051 -- determine (and forbid) that link that is the weakest link
5052 -- in both the best path from candidate m to candidate n and
5053 -- from candidate n to candidate m:
5054 "i" := 1;
5055 <<forbid_one_link>>
5056 LOOP
5057 "j" := 1;
5058 LOOP
5059 IF "i" != "j" THEN
5060 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
5061 "matrix_f"["i"]["j"] := TRUE;
5062 -- exit for performance reasons,
5063 -- as exactly one link will be found:
5064 EXIT forbid_one_link;
5065 END IF;
5066 END IF;
5067 EXIT WHEN "j" = "dimension_v";
5068 "j" := "j" + 1;
5069 END LOOP;
5070 IF "i" = "dimension_v" THEN
5071 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
5072 END IF;
5073 "i" := "i" + 1;
5074 END LOOP;
5075 -- calculate best beat-paths while ignoring forbidden links:
5076 "i" := 1;
5077 LOOP
5078 "j" := 1;
5079 LOOP
5080 IF "i" != "j" THEN
5081 "matrix_t"["i"]["j"] := CASE
5082 WHEN "matrix_f"["i"]["j"]
5083 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
5084 ELSE "matrix_d"["i"]["j"] END;
5085 END IF;
5086 EXIT WHEN "j" = "dimension_v";
5087 "j" := "j" + 1;
5088 END LOOP;
5089 EXIT WHEN "i" = "dimension_v";
5090 "i" := "i" + 1;
5091 END LOOP;
5092 "matrix_t" := "find_best_paths"("matrix_t");
5093 -- extend partial order, if tie-breaking was successful:
5094 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
5095 "matrix_b"["m"]["n"] := TRUE;
5096 "matrix_b"["n"]["m"] := FALSE;
5097 EXIT;
5098 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
5099 "matrix_b"["m"]["n"] := FALSE;
5100 "matrix_b"["n"]["m"] := TRUE;
5101 EXIT;
5102 END IF;
5103 END LOOP;
5104 END IF;
5105 EXIT WHEN "n" = "dimension_v";
5106 "n" := "n" + 1;
5107 END LOOP;
5108 EXIT WHEN "m" = "dimension_v" - 1;
5109 "m" := "m" + 1;
5110 END LOOP;
5111 END IF;
5112 -- store a unique ranking in "rank_ary":
5113 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
5114 "rank_v" := 1;
5115 LOOP
5116 "i" := 1;
5117 <<assign_next_rank>>
5118 LOOP
5119 IF "rank_ary"["i"] ISNULL THEN
5120 "j" := 1;
5121 LOOP
5122 IF
5123 "i" != "j" AND
5124 "rank_ary"["j"] ISNULL AND
5125 ( "matrix_b"["j"]["i"] OR
5126 -- tie-breaking by "id"
5127 ( "matrix_b"["j"]["i"] ISNULL AND
5128 "j" < "i" ) )
5129 THEN
5130 -- someone else is better
5131 EXIT;
5132 END IF;
5133 IF "j" = "dimension_v" THEN
5134 -- noone is better
5135 "rank_ary"["i"] := "rank_v";
5136 EXIT assign_next_rank;
5137 END IF;
5138 "j" := "j" + 1;
5139 END LOOP;
5140 END IF;
5141 "i" := "i" + 1;
5142 IF "i" > "dimension_v" THEN
5143 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
5144 END IF;
5145 END LOOP;
5146 EXIT WHEN "rank_v" = "dimension_v";
5147 "rank_v" := "rank_v" + 1;
5148 END LOOP;
5149 -- write preliminary results:
5150 "i" := 2; -- omit status quo with "i" = 1
5151 FOR "initiative_id_v" IN
5152 SELECT "id" FROM "initiative"
5153 WHERE "issue_id" = "issue_id_p" AND "admitted"
5154 ORDER BY "id"
5155 LOOP
5156 UPDATE "initiative" SET
5157 "direct_majority" =
5158 CASE WHEN "policy_row"."direct_majority_strict" THEN
5159 "positive_votes" * "policy_row"."direct_majority_den" >
5160 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5161 ELSE
5162 "positive_votes" * "policy_row"."direct_majority_den" >=
5163 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5164 END
5165 AND "positive_votes" >= "policy_row"."direct_majority_positive"
5166 AND "issue_row"."voter_count"-"negative_votes" >=
5167 "policy_row"."direct_majority_non_negative",
5168 "indirect_majority" =
5169 CASE WHEN "policy_row"."indirect_majority_strict" THEN
5170 "positive_votes" * "policy_row"."indirect_majority_den" >
5171 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5172 ELSE
5173 "positive_votes" * "policy_row"."indirect_majority_den" >=
5174 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5175 END
5176 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
5177 AND "issue_row"."voter_count"-"negative_votes" >=
5178 "policy_row"."indirect_majority_non_negative",
5179 "schulze_rank" = "rank_ary"["i"],
5180 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
5181 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
5182 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
5183 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
5184 THEN NULL
5185 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
5186 "eligible" = FALSE,
5187 "winner" = FALSE,
5188 "rank" = NULL -- NOTE: in cases of manual reset of issue state
5189 WHERE "id" = "initiative_id_v";
5190 "i" := "i" + 1;
5191 END LOOP;
5192 IF "i" != "dimension_v" + 1 THEN
5193 RAISE EXCEPTION 'Wrong winner count (should not happen)';
5194 END IF;
5195 -- take indirect majorities into account:
5196 LOOP
5197 UPDATE "initiative" SET "indirect_majority" = TRUE
5198 FROM (
5199 SELECT "new_initiative"."id" AS "initiative_id"
5200 FROM "initiative" "old_initiative"
5201 JOIN "initiative" "new_initiative"
5202 ON "new_initiative"."issue_id" = "issue_id_p"
5203 AND "new_initiative"."indirect_majority" = FALSE
5204 JOIN "battle" "battle_win"
5205 ON "battle_win"."issue_id" = "issue_id_p"
5206 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
5207 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
5208 JOIN "battle" "battle_lose"
5209 ON "battle_lose"."issue_id" = "issue_id_p"
5210 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
5211 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
5212 WHERE "old_initiative"."issue_id" = "issue_id_p"
5213 AND "old_initiative"."indirect_majority" = TRUE
5214 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
5215 "battle_win"."count" * "policy_row"."indirect_majority_den" >
5216 "policy_row"."indirect_majority_num" *
5217 ("battle_win"."count"+"battle_lose"."count")
5218 ELSE
5219 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
5220 "policy_row"."indirect_majority_num" *
5221 ("battle_win"."count"+"battle_lose"."count")
5222 END
5223 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
5224 AND "issue_row"."voter_count"-"battle_lose"."count" >=
5225 "policy_row"."indirect_majority_non_negative"
5226 ) AS "subquery"
5227 WHERE "id" = "subquery"."initiative_id";
5228 EXIT WHEN NOT FOUND;
5229 END LOOP;
5230 -- set "multistage_majority" for remaining matching initiatives:
5231 UPDATE "initiative" SET "multistage_majority" = TRUE
5232 FROM (
5233 SELECT "losing_initiative"."id" AS "initiative_id"
5234 FROM "initiative" "losing_initiative"
5235 JOIN "initiative" "winning_initiative"
5236 ON "winning_initiative"."issue_id" = "issue_id_p"
5237 AND "winning_initiative"."admitted"
5238 JOIN "battle" "battle_win"
5239 ON "battle_win"."issue_id" = "issue_id_p"
5240 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
5241 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
5242 JOIN "battle" "battle_lose"
5243 ON "battle_lose"."issue_id" = "issue_id_p"
5244 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
5245 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
5246 WHERE "losing_initiative"."issue_id" = "issue_id_p"
5247 AND "losing_initiative"."admitted"
5248 AND "winning_initiative"."schulze_rank" <
5249 "losing_initiative"."schulze_rank"
5250 AND "battle_win"."count" > "battle_lose"."count"
5251 AND (
5252 "battle_win"."count" > "winning_initiative"."positive_votes" OR
5253 "battle_lose"."count" < "losing_initiative"."negative_votes" )
5254 ) AS "subquery"
5255 WHERE "id" = "subquery"."initiative_id";
5256 -- mark eligible initiatives:
5257 UPDATE "initiative" SET "eligible" = TRUE
5258 WHERE "issue_id" = "issue_id_p"
5259 AND "initiative"."direct_majority"
5260 AND "initiative"."indirect_majority"
5261 AND "initiative"."better_than_status_quo"
5262 AND (
5263 "policy_row"."no_multistage_majority" = FALSE OR
5264 "initiative"."multistage_majority" = FALSE )
5265 AND (
5266 "policy_row"."no_reverse_beat_path" = FALSE OR
5267 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
5268 -- mark final winner:
5269 UPDATE "initiative" SET "winner" = TRUE
5270 FROM (
5271 SELECT "id" AS "initiative_id"
5272 FROM "initiative"
5273 WHERE "issue_id" = "issue_id_p" AND "eligible"
5274 ORDER BY
5275 "schulze_rank",
5276 "id"
5277 LIMIT 1
5278 ) AS "subquery"
5279 WHERE "id" = "subquery"."initiative_id";
5280 -- write (final) ranks:
5281 "rank_v" := 1;
5282 FOR "initiative_id_v" IN
5283 SELECT "id"
5284 FROM "initiative"
5285 WHERE "issue_id" = "issue_id_p" AND "admitted"
5286 ORDER BY
5287 "winner" DESC,
5288 "eligible" DESC,
5289 "schulze_rank",
5290 "id"
5291 LOOP
5292 UPDATE "initiative" SET "rank" = "rank_v"
5293 WHERE "id" = "initiative_id_v";
5294 "rank_v" := "rank_v" + 1;
5295 END LOOP;
5296 -- set schulze rank of status quo and mark issue as finished:
5297 UPDATE "issue" SET
5298 "status_quo_schulze_rank" = "rank_ary"[1],
5299 "state" =
5300 CASE WHEN EXISTS (
5301 SELECT NULL FROM "initiative"
5302 WHERE "issue_id" = "issue_id_p" AND "winner"
5303 ) THEN
5304 'finished_with_winner'::"issue_state"
5305 ELSE
5306 'finished_without_winner'::"issue_state"
5307 END,
5308 "closed" = "phase_finished",
5309 "phase_finished" = NULL
5310 WHERE "id" = "issue_id_p";
5311 RETURN;
5312 END;
5313 $$;
5315 COMMENT ON FUNCTION "calculate_ranks"
5316 ( "issue"."id"%TYPE )
5317 IS 'Determine ranking (Votes have to be counted first)';
5321 -----------------------------
5322 -- Automatic state changes --
5323 -----------------------------
5326 CREATE FUNCTION "issue_admission"()
5327 RETURNS BOOLEAN
5328 LANGUAGE 'plpgsql' VOLATILE AS $$
5329 DECLARE
5330 "issue_id_v" "issue"."id"%TYPE;
5331 BEGIN
5332 PERFORM "dont_require_transaction_isolation"();
5333 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5334 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission" LIMIT 1;
5335 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
5336 UPDATE "issue" SET
5337 "admission_snapshot_id" = "latest_snapshot_id",
5338 "state" = 'discussion',
5339 "accepted" = now(),
5340 "phase_finished" = NULL
5341 WHERE "id" = "issue_id_v";
5342 RETURN TRUE;
5343 END;
5344 $$;
5346 COMMENT ON FUNCTION "issue_admission"() IS 'Checks if an issue can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';
5349 CREATE TYPE "check_issue_persistence" AS (
5350 "state" "issue_state",
5351 "phase_finished" BOOLEAN,
5352 "issue_revoked" BOOLEAN,
5353 "snapshot_created" BOOLEAN,
5354 "harmonic_weights_set" BOOLEAN,
5355 "closed_voting" BOOLEAN );
5357 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
5360 CREATE FUNCTION "check_issue"
5361 ( "issue_id_p" "issue"."id"%TYPE,
5362 "persist" "check_issue_persistence" )
5363 RETURNS "check_issue_persistence"
5364 LANGUAGE 'plpgsql' VOLATILE AS $$
5365 DECLARE
5366 "issue_row" "issue"%ROWTYPE;
5367 "last_calculated_v" "snapshot"."calculated"%TYPE;
5368 "policy_row" "policy"%ROWTYPE;
5369 "initiative_row" "initiative"%ROWTYPE;
5370 "state_v" "issue_state";
5371 BEGIN
5372 PERFORM "require_transaction_isolation"();
5373 IF "persist" ISNULL THEN
5374 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5375 FOR UPDATE;
5376 SELECT "calculated" INTO "last_calculated_v"
5377 FROM "snapshot" JOIN "snapshot_issue"
5378 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
5379 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
5380 IF "issue_row"."closed" NOTNULL THEN
5381 RETURN NULL;
5382 END IF;
5383 "persist"."state" := "issue_row"."state";
5384 IF
5385 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
5386 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
5387 ( "issue_row"."state" = 'discussion' AND now() >=
5388 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
5389 ( "issue_row"."state" = 'verification' AND now() >=
5390 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
5391 ( "issue_row"."state" = 'voting' AND now() >=
5392 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
5393 THEN
5394 "persist"."phase_finished" := TRUE;
5395 ELSE
5396 "persist"."phase_finished" := FALSE;
5397 END IF;
5398 IF
5399 NOT EXISTS (
5400 -- all initiatives are revoked
5401 SELECT NULL FROM "initiative"
5402 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5403 ) AND (
5404 -- and issue has not been accepted yet
5405 "persist"."state" = 'admission' OR
5406 -- or verification time has elapsed
5407 ( "persist"."state" = 'verification' AND
5408 "persist"."phase_finished" ) OR
5409 -- or no initiatives have been revoked lately
5410 NOT EXISTS (
5411 SELECT NULL FROM "initiative"
5412 WHERE "issue_id" = "issue_id_p"
5413 AND now() < "revoked" + "issue_row"."verification_time"
5414 )
5415 )
5416 THEN
5417 "persist"."issue_revoked" := TRUE;
5418 ELSE
5419 "persist"."issue_revoked" := FALSE;
5420 END IF;
5421 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
5422 UPDATE "issue" SET "phase_finished" = now()
5423 WHERE "id" = "issue_row"."id";
5424 RETURN "persist";
5425 ELSIF
5426 "persist"."state" IN ('admission', 'discussion', 'verification')
5427 THEN
5428 RETURN "persist";
5429 ELSE
5430 RETURN NULL;
5431 END IF;
5432 END IF;
5433 IF
5434 "persist"."state" IN ('admission', 'discussion', 'verification') AND
5435 coalesce("persist"."snapshot_created", FALSE) = FALSE
5436 THEN
5437 IF "persist"."state" != 'admission' THEN
5438 PERFORM "take_snapshot"("issue_id_p");
5439 PERFORM "finish_snapshot"("issue_id_p");
5440 END IF;
5441 "persist"."snapshot_created" = TRUE;
5442 IF "persist"."phase_finished" THEN
5443 IF "persist"."state" = 'admission' THEN
5444 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
5445 ELSIF "persist"."state" = 'discussion' THEN
5446 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
5447 ELSIF "persist"."state" = 'verification' THEN
5448 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
5449 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
5450 SELECT * INTO "policy_row" FROM "policy"
5451 WHERE "id" = "issue_row"."policy_id";
5452 FOR "initiative_row" IN
5453 SELECT * FROM "initiative"
5454 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5455 FOR UPDATE
5456 LOOP
5457 IF
5458 "initiative_row"."polling" OR (
5459 "initiative_row"."satisfied_supporter_count" > 0 AND
5460 "initiative_row"."satisfied_supporter_count" *
5461 "policy_row"."initiative_quorum_den" >=
5462 "issue_row"."population" * "policy_row"."initiative_quorum_num"
5463 )
5464 THEN
5465 UPDATE "initiative" SET "admitted" = TRUE
5466 WHERE "id" = "initiative_row"."id";
5467 ELSE
5468 UPDATE "initiative" SET "admitted" = FALSE
5469 WHERE "id" = "initiative_row"."id";
5470 END IF;
5471 END LOOP;
5472 END IF;
5473 END IF;
5474 RETURN "persist";
5475 END IF;
5476 IF
5477 "persist"."state" IN ('admission', 'discussion', 'verification') AND
5478 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
5479 THEN
5480 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
5481 "persist"."harmonic_weights_set" = TRUE;
5482 IF
5483 "persist"."phase_finished" OR
5484 "persist"."issue_revoked" OR
5485 "persist"."state" = 'admission'
5486 THEN
5487 RETURN "persist";
5488 ELSE
5489 RETURN NULL;
5490 END IF;
5491 END IF;
5492 IF "persist"."issue_revoked" THEN
5493 IF "persist"."state" = 'admission' THEN
5494 "state_v" := 'canceled_revoked_before_accepted';
5495 ELSIF "persist"."state" = 'discussion' THEN
5496 "state_v" := 'canceled_after_revocation_during_discussion';
5497 ELSIF "persist"."state" = 'verification' THEN
5498 "state_v" := 'canceled_after_revocation_during_verification';
5499 END IF;
5500 UPDATE "issue" SET
5501 "state" = "state_v",
5502 "closed" = "phase_finished",
5503 "phase_finished" = NULL
5504 WHERE "id" = "issue_id_p";
5505 RETURN NULL;
5506 END IF;
5507 IF "persist"."state" = 'admission' THEN
5508 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5509 FOR UPDATE;
5510 IF "issue_row"."phase_finished" NOTNULL THEN
5511 UPDATE "issue" SET
5512 "state" = 'canceled_issue_not_accepted',
5513 "closed" = "phase_finished",
5514 "phase_finished" = NULL
5515 WHERE "id" = "issue_id_p";
5516 END IF;
5517 RETURN NULL;
5518 END IF;
5519 IF "persist"."phase_finished" THEN
5520 IF "persist"."state" = 'discussion' THEN
5521 UPDATE "issue" SET
5522 "state" = 'verification',
5523 "half_frozen" = "phase_finished",
5524 "phase_finished" = NULL
5525 WHERE "id" = "issue_id_p";
5526 RETURN NULL;
5527 END IF;
5528 IF "persist"."state" = 'verification' THEN
5529 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5530 FOR UPDATE;
5531 SELECT * INTO "policy_row" FROM "policy"
5532 WHERE "id" = "issue_row"."policy_id";
5533 IF EXISTS (
5534 SELECT NULL FROM "initiative"
5535 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
5536 ) THEN
5537 UPDATE "issue" SET
5538 "state" = 'voting',
5539 "fully_frozen" = "phase_finished",
5540 "phase_finished" = NULL
5541 WHERE "id" = "issue_id_p";
5542 ELSE
5543 UPDATE "issue" SET
5544 "state" = 'canceled_no_initiative_admitted',
5545 "fully_frozen" = "phase_finished",
5546 "closed" = "phase_finished",
5547 "phase_finished" = NULL
5548 WHERE "id" = "issue_id_p";
5549 -- NOTE: The following DELETE statements have effect only when
5550 -- issue state has been manipulated
5551 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
5552 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
5553 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
5554 END IF;
5555 RETURN NULL;
5556 END IF;
5557 IF "persist"."state" = 'voting' THEN
5558 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
5559 PERFORM "close_voting"("issue_id_p");
5560 "persist"."closed_voting" = TRUE;
5561 RETURN "persist";
5562 END IF;
5563 PERFORM "calculate_ranks"("issue_id_p");
5564 RETURN NULL;
5565 END IF;
5566 END IF;
5567 RAISE WARNING 'should not happen';
5568 RETURN NULL;
5569 END;
5570 $$;
5572 COMMENT ON FUNCTION "check_issue"
5573 ( "issue"."id"%TYPE,
5574 "check_issue_persistence" )
5575 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
5578 CREATE FUNCTION "check_everything"()
5579 RETURNS VOID
5580 LANGUAGE 'plpgsql' VOLATILE AS $$
5581 DECLARE
5582 "snapshot_id_v" "snapshot"."id"%TYPE;
5583 "issue_id_v" "issue"."id"%TYPE;
5584 "persist_v" "check_issue_persistence";
5585 BEGIN
5586 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
5587 DELETE FROM "expired_session";
5588 PERFORM "check_activity"();
5589 PERFORM "calculate_member_counts"();
5590 SELECT "take_snapshot"(NULL) INTO "snapshot_id_v";
5591 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
5592 WHERE "snapshot_id" = "snapshot_id_v";
5593 LOOP
5594 EXIT WHEN "issue_admission"() = FALSE;
5595 END LOOP;
5596 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
5597 "persist_v" := NULL;
5598 LOOP
5599 "persist_v" := "check_issue"("issue_id_v", "persist_v");
5600 EXIT WHEN "persist_v" ISNULL;
5601 END LOOP;
5602 END LOOP;
5603 RETURN;
5604 END;
5605 $$;
5607 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks, this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
5611 ----------------------
5612 -- Deletion of data --
5613 ----------------------
5616 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
5617 RETURNS VOID
5618 LANGUAGE 'plpgsql' VOLATILE AS $$
5619 BEGIN
5620 IF EXISTS (
5621 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
5622 ) THEN
5623 -- override protection triggers:
5624 INSERT INTO "temporary_transaction_data" ("key", "value")
5625 VALUES ('override_protection_triggers', TRUE::TEXT);
5626 -- clean data:
5627 DELETE FROM "delegating_voter"
5628 WHERE "issue_id" = "issue_id_p";
5629 DELETE FROM "direct_voter"
5630 WHERE "issue_id" = "issue_id_p";
5631 DELETE FROM "delegating_interest_snapshot"
5632 WHERE "issue_id" = "issue_id_p";
5633 DELETE FROM "direct_interest_snapshot"
5634 WHERE "issue_id" = "issue_id_p";
5635 DELETE FROM "delegating_population_snapshot"
5636 WHERE "issue_id" = "issue_id_p";
5637 DELETE FROM "direct_population_snapshot"
5638 WHERE "issue_id" = "issue_id_p";
5639 DELETE FROM "non_voter"
5640 WHERE "issue_id" = "issue_id_p";
5641 DELETE FROM "delegation"
5642 WHERE "issue_id" = "issue_id_p";
5643 DELETE FROM "supporter"
5644 USING "initiative" -- NOTE: due to missing index on issue_id
5645 WHERE "initiative"."issue_id" = "issue_id_p"
5646 AND "supporter"."initiative_id" = "initiative_id";
5647 -- mark issue as cleaned:
5648 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
5649 -- finish overriding protection triggers (avoids garbage):
5650 DELETE FROM "temporary_transaction_data"
5651 WHERE "key" = 'override_protection_triggers';
5652 END IF;
5653 RETURN;
5654 END;
5655 $$;
5657 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
5660 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
5661 RETURNS VOID
5662 LANGUAGE 'plpgsql' VOLATILE AS $$
5663 BEGIN
5664 UPDATE "member" SET
5665 "last_login" = NULL,
5666 "last_delegation_check" = NULL,
5667 "login" = NULL,
5668 "password" = NULL,
5669 "authority" = NULL,
5670 "authority_uid" = NULL,
5671 "authority_login" = NULL,
5672 "locked" = TRUE,
5673 "active" = FALSE,
5674 "notify_email" = NULL,
5675 "notify_email_unconfirmed" = NULL,
5676 "notify_email_secret" = NULL,
5677 "notify_email_secret_expiry" = NULL,
5678 "notify_email_lock_expiry" = NULL,
5679 "disable_notifications" = TRUE,
5680 "notification_counter" = DEFAULT,
5681 "notification_sample_size" = 0,
5682 "notification_dow" = NULL,
5683 "notification_hour" = NULL,
5684 "login_recovery_expiry" = NULL,
5685 "password_reset_secret" = NULL,
5686 "password_reset_secret_expiry" = NULL,
5687 "organizational_unit" = NULL,
5688 "internal_posts" = NULL,
5689 "realname" = NULL,
5690 "birthday" = NULL,
5691 "address" = NULL,
5692 "email" = NULL,
5693 "xmpp_address" = NULL,
5694 "website" = NULL,
5695 "phone" = NULL,
5696 "mobile_phone" = NULL,
5697 "profession" = NULL,
5698 "external_memberships" = NULL,
5699 "external_posts" = NULL,
5700 "statement" = NULL
5701 WHERE "id" = "member_id_p";
5702 -- "text_search_data" is updated by triggers
5703 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
5704 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
5705 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
5706 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
5707 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5708 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5709 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5710 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5711 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5712 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5713 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5714 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5715 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5716 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5717 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5718 DELETE FROM "direct_voter" USING "issue"
5719 WHERE "direct_voter"."issue_id" = "issue"."id"
5720 AND "issue"."closed" ISNULL
5721 AND "member_id" = "member_id_p";
5722 RETURN;
5723 END;
5724 $$;
5726 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)';
5729 CREATE FUNCTION "delete_private_data"()
5730 RETURNS VOID
5731 LANGUAGE 'plpgsql' VOLATILE AS $$
5732 BEGIN
5733 DELETE FROM "temporary_transaction_data";
5734 DELETE FROM "member" WHERE "activated" ISNULL;
5735 UPDATE "member" SET
5736 "invite_code" = NULL,
5737 "invite_code_expiry" = NULL,
5738 "admin_comment" = NULL,
5739 "last_login" = NULL,
5740 "last_delegation_check" = NULL,
5741 "login" = NULL,
5742 "password" = NULL,
5743 "authority" = NULL,
5744 "authority_uid" = NULL,
5745 "authority_login" = NULL,
5746 "lang" = NULL,
5747 "notify_email" = NULL,
5748 "notify_email_unconfirmed" = NULL,
5749 "notify_email_secret" = NULL,
5750 "notify_email_secret_expiry" = NULL,
5751 "notify_email_lock_expiry" = NULL,
5752 "disable_notifications" = TRUE,
5753 "notification_counter" = DEFAULT,
5754 "notification_sample_size" = 0,
5755 "notification_dow" = NULL,
5756 "notification_hour" = NULL,
5757 "login_recovery_expiry" = NULL,
5758 "password_reset_secret" = NULL,
5759 "password_reset_secret_expiry" = NULL,
5760 "organizational_unit" = NULL,
5761 "internal_posts" = NULL,
5762 "realname" = NULL,
5763 "birthday" = NULL,
5764 "address" = NULL,
5765 "email" = NULL,
5766 "xmpp_address" = NULL,
5767 "website" = NULL,
5768 "phone" = NULL,
5769 "mobile_phone" = NULL,
5770 "profession" = NULL,
5771 "external_memberships" = NULL,
5772 "external_posts" = NULL,
5773 "formatting_engine" = NULL,
5774 "statement" = NULL;
5775 -- "text_search_data" is updated by triggers
5776 DELETE FROM "setting";
5777 DELETE FROM "setting_map";
5778 DELETE FROM "member_relation_setting";
5779 DELETE FROM "member_image";
5780 DELETE FROM "contact";
5781 DELETE FROM "ignored_member";
5782 DELETE FROM "session";
5783 DELETE FROM "area_setting";
5784 DELETE FROM "issue_setting";
5785 DELETE FROM "ignored_initiative";
5786 DELETE FROM "initiative_setting";
5787 DELETE FROM "suggestion_setting";
5788 DELETE FROM "non_voter";
5789 DELETE FROM "direct_voter" USING "issue"
5790 WHERE "direct_voter"."issue_id" = "issue"."id"
5791 AND "issue"."closed" ISNULL;
5792 RETURN;
5793 END;
5794 $$;
5796 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. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
5800 COMMIT;