liquid_feedback_core
changeset 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 | eaa4836e04ee |
children | 96ee2db56bec |
files | core.sql test.sql |
line diff
1.1 --- a/core.sql Fri May 27 09:48:34 2016 +0200 1.2 +++ b/core.sql Sun May 29 20:42:40 2016 +0200 1.3 @@ -1,6 +1,3 @@ 1.4 - 1.5 --- Execute the following command manually for PostgreSQL prior version 9.0: 1.6 --- CREATE LANGUAGE plpgsql; 1.7 1.8 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index 1.9 1.10 @@ -49,6 +46,586 @@ 1.11 1.12 1.13 1.14 +----------------------------------------------- 1.15 +-- spatial indexing and distance calculation -- 1.16 +----------------------------------------------- 1.17 + 1.18 + 1.19 +CREATE DOMAIN "geoindex" AS point CHECK (VALUE <@ '((-1,-1),(1,1))'::box); 1.20 + 1.21 +COMMENT ON DOMAIN "geoindex" IS 'Data type used for indexing geographical coordinates'; 1.22 + 1.23 + 1.24 +CREATE FUNCTION "geoindex" 1.25 + ( "latitude_p" FLOAT, 1.26 + "longitude_p" FLOAT ) 1.27 + RETURNS "geoindex" 1.28 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.29 + SELECT point("latitude_p" / 90, "longitude_p" / 180)::"geoindex" 1.30 + $$; 1.31 + 1.32 +COMMENT ON FUNCTION "geoindex" 1.33 + (FLOAT, FLOAT) 1.34 + 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'; 1.35 + 1.36 + 1.37 +CREATE TYPE "geosearch" AS ( 1.38 + "lat" FLOAT, 1.39 + "lon" FLOAT, 1.40 + "c3" FLOAT, 1.41 + "c2" FLOAT, 1.42 + "c1" FLOAT, 1.43 + "c0" FLOAT, 1.44 + "dist" FLOAT, 1.45 + "bbox1" BOX, 1.46 + "bbox2" BOX ); 1.47 + 1.48 +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'; 1.49 + 1.50 +COMMENT ON COLUMN "geosearch"."lat" IS 'Latitude of center of search area'; 1.51 +COMMENT ON COLUMN "geosearch"."lon" IS 'Longitude of center of search area'; 1.52 +COMMENT ON COLUMN "geosearch"."c3" IS 'Coefficient for distance calculation'; 1.53 +COMMENT ON COLUMN "geosearch"."c2" IS 'Coefficient for distance calculation'; 1.54 +COMMENT ON COLUMN "geosearch"."c1" IS 'Coefficient for distance calculation'; 1.55 +COMMENT ON COLUMN "geosearch"."c0" IS 'Coefficient for distance calculation'; 1.56 +COMMENT ON COLUMN "geosearch"."bbox1" IS 'Bounding box suitable for use with output of "geoindex" function'; 1.57 +COMMENT ON COLUMN "geosearch"."bbox2" IS 'Second bounding box (see column "bbox1") set if search crosses the 180th meridian'; 1.58 + 1.59 + 1.60 +CREATE FUNCTION "geosearch" 1.61 + ( "latitude_p" FLOAT, 1.62 + "longitude_p" FLOAT ) 1.63 + RETURNS "geosearch" 1.64 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.65 + DECLARE 1.66 + -- constants: 1.67 + -- (with a = radius at equator, and b = radius at pole) 1.68 + "degrad" FLOAT := 0.017453292519943295; -- pi / 180 1.69 + "degrad2" FLOAT := 0.00030461741978670857; -- (pi / 180)^2 1.70 + "a2" FLOAT := 40680631590769; -- a^2 1.71 + "eps2" FLOAT := 0.006694379990141316; -- (a^2 - b^2) / a^2 1.72 + "subeps2" FLOAT := 0.9933056200098587; -- 1 - "eps2" 1.73 + "subeps22" FLOAT := 0.9866560547431698; -- (1 - "eps2")^2 1.74 + -- latitude in radians: 1.75 + "phi0rad" FLOAT := "degrad" * "latitude_p"; 1.76 + -- helper variables: 1.77 + "sinp0" FLOAT; -- sin("phi0rad") 1.78 + "cosp0" FLOAT; -- cos("phi0rad") 1.79 + "sinp02" FLOAT; -- sin("phi0rad")^2 1.80 + "e2sinp02" FLOAT; -- "eps2" * sin("phi0rad")^2 1.81 + "subsin" FLOAT; -- 1 - sin("phi0rad")^2 1.82 + "subse" FLOAT; -- 1 - "eps2" * sin("phi0rad")^2 1.83 + "subse2" FLOAT; -- ("subse")^2 1.84 + "t20" FLOAT; -- taylor coefficient for (delta latitude)^2 1.85 + "t02" FLOAT; -- taylor coefficient for (delta longitude)^2 / "t20" 1.86 + "t12" FLOAT; -- taylor coefficient for (d lat)^1*(d lon)^2 / "t20" 1.87 + "t22" FLOAT; -- taylor coefficient for (d lat)^2*(d lon)^2 / "t20" 1.88 + -- result: 1.89 + "res" "geosearch"; 1.90 + BEGIN 1.91 + PERFORM "geoindex"("latitude_p", "longitude_p"); -- check bounds 1.92 + "sinp0" := sin("phi0rad"); 1.93 + "cosp0" := cos("phi0rad"); 1.94 + "sinp02" := "sinp0" * "sinp0"; 1.95 + "e2sinp02" := "eps2" * "sinp02"; 1.96 + "subsin" := 1 - "sinp02"; 1.97 + "subse" := 1 - "e2sinp02"; 1.98 + "subse2" := "subse" * "subse"; 1.99 + "t20" := "a2" * "subeps22" / ("subse" * "subse2"); 1.100 + "t02" := "subsin" * "subse2" / "subeps22"; 1.101 + "t12" := -"sinp0" * "cosp0" * "subse" / "subeps2"; 1.102 + "t22" := -"subsin" * (0.5+"e2sinp02") / "subeps2"; 1.103 + "res"."lat" := "latitude_p"; 1.104 + "res"."lon" := "longitude_p"; 1.105 + "res"."c3" := "degrad2" * "t20"; 1.106 + "res"."c2" := "degrad2" * "t22"; 1.107 + "res"."c1" := "degrad" * ("t12" - 2 * "t22" * "phi0rad"); 1.108 + "res"."c0" := ("t22" * "phi0rad" - "t12") * "phi0rad" + "t02"; 1.109 + "res"."dist" := 'Infinity'::FLOAT; 1.110 + "res"."bbox1" := box(point(1, 1), point(-1, -1)); 1.111 + "res"."bbox2" := NULL; 1.112 + RETURN "res"; 1.113 + END; 1.114 + $$; 1.115 + 1.116 +COMMENT ON FUNCTION "geosearch" 1.117 + ( FLOAT, FLOAT ) 1.118 + 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'; 1.119 + 1.120 + 1.121 +CREATE FUNCTION "geosearch" 1.122 + ( "latitude_p" FLOAT, 1.123 + "longitude_p" FLOAT, 1.124 + "radius_p" FLOAT ) 1.125 + RETURNS "geosearch" 1.126 + LANGUAGE plpgsql IMMUTABLE AS $$ 1.127 + DECLARE 1.128 + -- constants: 1.129 + "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90) 1.130 + "margin" FLOAT := 1.0001; -- slightly increase search area 1.131 + -- NOTE: search area is increased by "margin", search radius gets 1.132 + -- increased by sqrt("margin"); choose a value > 1.0 to avoid 1.133 + -- floating point errors 1.134 + "fixzero" FLOAT := 0.0001; -- small value > 0 to fix singularity 1.135 + -- result: 1.136 + "res" "geosearch"; 1.137 + -- helper variables: 1.138 + "rc3mar" FLOAT; -- ("radius_p")^2 / "c3", multiplied with "margin" 1.139 + "subc02" FLOAT; -- 2 * ("c0" - "c2" * "rc3mar") 1.140 + "b4" FLOAT; -- ("c2")^2 1.141 + "b3" FLOAT; -- 2 * "c1" * "c2" 1.142 + "b2" FLOAT; -- ("c1")^2 + "subc02" * "c2" 1.143 + "b1" FLOAT; -- "subc02" * "c1" 1.144 + "b0" FLOAT; -- ("c2")^2 * ("rc3mar")^2 + 1.145 + -- 2 * "c0" * "c2" - ("c1")^2 * ("rc3mar")^1 + 1.146 + -- ("c0")^2 * ("rc3mar")^0 1.147 + "sqrtval" FLOAT; -- "b4" * ("latitude_p")^4 + 1.148 + -- "b3" * ("latitude_p")^3 + 1.149 + -- "b2" * ("latitude_p")^2 + 1.150 + -- "b1" * ("latitude_p")^1 + 1.151 + -- "b0" * ("latitude_p")^0 1.152 + "phic" FLOAT; -- ( "c2" * (("latitude_p")^2 - "rc3mar") - "c0" 1.153 + -- + sqrt("sqrtval") 1.154 + -- ) / ( 2 * "c2" * "latitude_p" + "c1" ) 1.155 + "dlat" FLOAT; -- delta latitude in degrees = sqrt("rc3mar") 1.156 + "dlon2" FLOAT; -- square of delta longitude 1.157 + "dlon" FLOAT; -- delta longitude in degrees = 1.158 + -- sqrt( 1.159 + -- ( "rc3mar" - ("phic" - "latitude_p")^2 ) / 1.160 + -- ( "c2" * ("phic")^2 + 1.161 + -- "c1" * ("phic")^1 + 1.162 + -- "c0" ) 1.163 + -- ) 1.164 + "lat_min" FLOAT; 1.165 + "lat_max" FLOAT; 1.166 + "lon_min" FLOAT; 1.167 + "lon_max" FLOAT; 1.168 + BEGIN 1.169 + "res" := "geosearch"("latitude_p", "longitude_p"); 1.170 + IF "res" ISNULL THEN RETURN NULL; END IF; 1.171 + IF "radius_p" ISNULL OR "radius_p" = 'Infinity'::FLOAT THEN 1.172 + RETURN "res"; 1.173 + END IF; 1.174 + "res"."dist" := "radius_p"; 1.175 + "rc3mar" := "margin" * "radius_p" * "radius_p" / "res"."c3"; 1.176 + "subc02" := 2 * ("res"."c0" - "res"."c2" * "rc3mar"); 1.177 + "b4" := "res"."c2" * "res"."c2"; 1.178 + "b3" := 2 * "res"."c1" * "res"."c2"; 1.179 + "b2" := "res"."c1" * "res"."c1" + "subc02" * "res"."c2"; 1.180 + "b1" := "subc02" * "res"."c1"; 1.181 + "b0" := ( "b4" * "rc3mar" + 1.182 + 2 * "res"."c0" * "res"."c2" - "res"."c1" * "res"."c1" 1.183 + ) * "rc3mar" + "res"."c0" * "res"."c0"; 1.184 + "sqrtval" := ( ( ( ( "b4" 1.185 + ) * "latitude_p" + "b3" 1.186 + ) * "latitude_p" + "b2" 1.187 + ) * "latitude_p" + "b1" 1.188 + ) * "latitude_p" + "b0"; 1.189 + IF "sqrtval" < 0 THEN 1.190 + IF "latitude_p" >= 0 THEN "phic" := "philimit"; 1.191 + ELSE "phic" := -"philimit"; END IF; 1.192 + ELSE 1.193 + IF abs("latitude_p") <= "fixzero" THEN 1.194 + "phic" := "latitude_p"; 1.195 + ELSE 1.196 + "phic" := ( 1.197 + "res"."c2" * ("latitude_p" * "latitude_p" - "rc3mar") 1.198 + - "res"."c0" + sqrt("sqrtval") 1.199 + ) / ( 1.200 + 2 * "res"."c2" * "latitude_p" + "res"."c1" 1.201 + ); 1.202 + IF "phic" > "philimit" THEN "phic" := "philimit"; 1.203 + ELSIF "phic" < -"philimit" THEN "phic" := -"philimit"; END IF; 1.204 + END IF; 1.205 + END IF; 1.206 + "dlat" := sqrt("rc3mar"); 1.207 + "dlon2" := ( 1.208 + ( "rc3mar" - ("phic" - "latitude_p")^2 ) / 1.209 + ( ( "res"."c2" * "phic" + "res"."c1" ) * "phic" + "res"."c0" ) 1.210 + ); 1.211 + IF "dlon2" > 0 THEN "dlon" := sqrt("dlon2"); 1.212 + ELSE "dlon" := 0; END IF; 1.213 + "lat_min" := "latitude_p" - "dlat"; 1.214 + "lat_max" := "latitude_p" + "dlat"; 1.215 + IF "lat_min" < -90 THEN "lat_min" := -90; END IF; 1.216 + IF "lat_max" > 90 THEN "lat_max" := 90; END IF; 1.217 + "lon_min" := "longitude_p" - "dlon"; 1.218 + "lon_max" := "longitude_p" + "dlon"; 1.219 + IF "lon_min" < -180 THEN 1.220 + IF "lon_max" > 180 THEN 1.221 + "res"."bbox1" := box( 1.222 + "geoindex"("lat_min", -180), 1.223 + "geoindex"("lat_max", 180) ); 1.224 + "res"."bbox2" := NULL; 1.225 + ELSE 1.226 + "res"."bbox1" := box( 1.227 + "geoindex"("lat_min", -180), 1.228 + "geoindex"("lat_max", "lon_max") ); 1.229 + "res"."bbox2" := box( 1.230 + "geoindex"("lat_min", "lon_min" + 360), 1.231 + "geoindex"("lat_max", 180) ); 1.232 + END IF; 1.233 + ELSIF "lon_max" > 180 THEN 1.234 + "res"."bbox1" := box( 1.235 + "geoindex"("lat_min", "lon_min"), 1.236 + "geoindex"("lat_max", 180) ); 1.237 + "res"."bbox2" := box( 1.238 + "geoindex"("lat_min", -180), 1.239 + "geoindex"("lat_max", "lon_max" - 360) ); 1.240 + ELSE 1.241 + "res"."bbox1" := box( 1.242 + "geoindex"("lat_min", "lon_min"), 1.243 + "geoindex"("lat_max", "lon_max") ); 1.244 + "res"."bbox2" := NULL; 1.245 + END IF; 1.246 + RETURN "res"; 1.247 + END; 1.248 + $$; 1.249 + 1.250 +COMMENT ON FUNCTION "geosearch" 1.251 + ( FLOAT, FLOAT, FLOAT ) 1.252 + IS 'Takes latitude, longitude (both in degrees), and a radius (in meters), and returns a "geoindex" structure suitable for searching and distance calculation'; 1.253 + 1.254 + 1.255 +CREATE FUNCTION "geodist" 1.256 + ( "geosearch_p" "geosearch", 1.257 + "latitude_p" FLOAT, 1.258 + "longitude_p" FLOAT ) 1.259 + RETURNS FLOAT 1.260 + LANGUAGE sql IMMUTABLE AS $$ 1.261 + SELECT sqrt( 1.262 + "geosearch_p"."c3" * ( 1.263 + ("latitude_p" - "geosearch_p"."lat")^2 + 1.264 + ( ( "geosearch_p"."c2" * "latitude_p" + "geosearch_p"."c1" ) 1.265 + * "latitude_p" + "geosearch_p"."c0" ) * 1.266 + ( "longitude_p" + 1.267 + CASE WHEN "longitude_p" - "geosearch_p"."lon" > 180 1.268 + THEN -360 ELSE 1.269 + CASE WHEN "longitude_p" - "geosearch_p"."lon" < -180 1.270 + THEN 360 1.271 + ELSE 0 END 1.272 + END - "geosearch_p"."lon" 1.273 + )^2 1.274 + ) 1.275 + ) 1.276 + $$; 1.277 + 1.278 +COMMENT ON FUNCTION "geodist" 1.279 + ( "geosearch", FLOAT, FLOAT ) 1.280 + IS 'Takes a "geosearch" structure as well as geographical coordinates in degrees and returns the distance on earth in meters'; 1.281 + 1.282 + 1.283 +/* Usage of spatial indexing and distance calculation functions for points: 1.284 + 1.285 +-- create table containing geographical coordinates in degrees: 1.286 +CREATE TABLE "t1" ( 1.287 + "id" SERIAL4 PRIMARY KEY, 1.288 + "latitude" NUMERIC NOT NULL, 1.289 + "longitude" NUMERIC NOT NULL ); 1.290 + 1.291 +-- create an SP-GiST index (with default quad_point_ops operator class): 1.292 +CREATE INDEX "t1_geoindex" ON "t1" USING spgist (("geoindex"("latitude", "longitude"))); 1.293 + 1.294 +-- return results within 10,000 meters of Brandenburg Gate: 1.295 +SELECT "t1".*, "distance" 1.296 + FROM "t1" 1.297 + CROSS JOIN "geosearch"(52.5162746, 13.377704, 10000) 1.298 + CROSS JOIN LATERAL "geodist"("geosearch", "latitude", "longitude") AS "distance" 1.299 + WHERE ( 1.300 + "geoindex"("latitude", "longitude") <@ "geosearch"."bbox1" OR 1.301 + "geoindex"("latitude", "longitude") <@ "geosearch"."bbox2" ) 1.302 + AND "distance" <= "geosearch"."dist"; 1.303 + 1.304 +*/ 1.305 + 1.306 + 1.307 +CREATE FUNCTION "polyindex" 1.308 + ( "point_p" POINT ) 1.309 + RETURNS POLYGON 1.310 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.311 + SELECT "point_p"::TEXT::POLYGON; 1.312 + $$; 1.313 + 1.314 +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'; 1.315 + 1.316 + 1.317 +CREATE FUNCTION "polyindex" 1.318 + ( "latitude_p" FLOAT, 1.319 + "longitude_p" FLOAT ) 1.320 + RETURNS POLYGON 1.321 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.322 + SELECT "polyindex"(point("latitude_p", "longitude_p")) 1.323 + $$; 1.324 + 1.325 +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'; 1.326 + 1.327 + 1.328 +CREATE FUNCTION "polyindex" 1.329 + ( "polygon_p" POLYGON ) 1.330 + RETURNS POLYGON 1.331 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.332 + SELECT "polygon_p" 1.333 + $$; 1.334 + 1.335 +COMMENT ON FUNCTION "polyindex"(POLYGON) IS 'Does nothing but returning the polygon unchanged (for GiST poly_ops indexing)'; 1.336 + 1.337 + 1.338 +CREATE FUNCTION "polyindex" 1.339 + ( "box_p" BOX ) 1.340 + RETURNS POLYGON 1.341 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.342 + SELECT "box_p"::POLYGON 1.343 + $$; 1.344 + 1.345 +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'; 1.346 + 1.347 + 1.348 +CREATE FUNCTION "polyindex" 1.349 + ( "path_p" PATH ) 1.350 + RETURNS POLYGON 1.351 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.352 + DECLARE 1.353 + "match_v" TEXT[]; 1.354 + "points_v" TEXT[]; 1.355 + "idx1" INTEGER; 1.356 + "idx2" INTEGER; 1.357 + BEGIN 1.358 + IF isclosed("path_p") THEN 1.359 + RETURN "path_p"::POLYGON; 1.360 + ELSE 1.361 + "points_v" := '{}'; 1.362 + "idx1" := 0; 1.363 + FOR "match_v" IN 1.364 + SELECT regexp_matches("path_p"::TEXT, E'\\(([^()]*)\\)', 'g') 1.365 + LOOP 1.366 + "idx1" := "idx1" + 1; 1.367 + "points_v"["idx1"] := "match_v"[1]; 1.368 + END LOOP; 1.369 + "idx2" := "idx1"; 1.370 + LOOP 1.371 + EXIT WHEN "idx1" < 3; 1.372 + "idx1" := "idx1" - 1; 1.373 + "idx2" := "idx2" + 1; 1.374 + "points_v"["idx2"] := "points_v"["idx1"]; 1.375 + END LOOP; 1.376 + RETURN array_to_string("points_v", ',')::POLYGON; 1.377 + END IF; 1.378 + END; 1.379 + $$; 1.380 + 1.381 +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'; 1.382 + 1.383 + 1.384 +CREATE FUNCTION "polyindex" 1.385 + ( "lseg_p" LSEG ) 1.386 + RETURNS POLYGON 1.387 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.388 + SELECT "polyindex"("lseg_p"::TEXT::PATH) 1.389 + $$; 1.390 + 1.391 +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'; 1.392 + 1.393 + 1.394 +CREATE FUNCTION "geoshift180" 1.395 + ( "point_p" POINT ) 1.396 + RETURNS POINT 1.397 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.398 + SELECT CASE WHEN "point_p" <^ '0,0'::POINT THEN 1.399 + "point_p" + '0,360'::POINT 1.400 + ELSE 1.401 + "point_p" - '0,360'::POINT 1.402 + END 1.403 + $$; 1.404 + 1.405 +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'; 1.406 + 1.407 + 1.408 +CREATE FUNCTION "geoshift180" 1.409 + ( "polygon_p" POLYGON ) 1.410 + RETURNS POLYGON 1.411 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.412 + SELECT CASE WHEN center("polygon_p"::CIRCLE) <^ '0,0'::POINT THEN 1.413 + ("polygon_p"::PATH + '0,360'::POINT)::POLYGON 1.414 + ELSE 1.415 + ("polygon_p"::PATH - '0,360'::POINT)::POLYGON 1.416 + END 1.417 + $$; 1.418 + 1.419 +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'; 1.420 + 1.421 + 1.422 +CREATE FUNCTION "polysearch" 1.423 + ( "geosearch_p" "geosearch", 1.424 + "steps_p" INTEGER = 24 ) 1.425 + RETURNS POLYGON 1.426 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.427 + DECLARE 1.428 + "philimit" FLOAT := 84; -- maximum latitude (needs to be less than 90) 1.429 + "halfsteps" INTEGER; 1.430 + "step" INTEGER := 0; 1.431 + "angle" FLOAT; 1.432 + "dlat" FLOAT := "geosearch_p"."dist" / sqrt("geosearch_p"."c3"); 1.433 + "lat" FLOAT; 1.434 + "dlon" FLOAT; 1.435 + "coords" TEXT[]; 1.436 + BEGIN 1.437 + IF "steps_p" < 4 OR "steps_p" % 2 != 0 THEN 1.438 + RAISE EXCEPTION 'Invalid step count'; 1.439 + END IF; 1.440 + "halfsteps" := "steps_p" / 2; 1.441 + LOOP 1.442 + "angle" := 2.0 * pi() * "step" / "steps_p"; 1.443 + "lat" := "geosearch_p"."lat" + "dlat" * cos("angle"); 1.444 + IF "lat" > "philimit" THEN "lat" := "philimit"; 1.445 + ELSIF "lat" < -"philimit" THEN "lat" := -"philimit"; 1.446 + END IF; 1.447 + "dlon" := "dlat" * sin("angle") / sqrt( 1.448 + ("geosearch_p"."c2" * "lat" + "geosearch_p"."c1") * "lat" + 1.449 + "geosearch_p"."c0" ); 1.450 + "coords"[2*"step"+1] := "lat"; 1.451 + "coords"[2*"step"+2] := "geosearch_p"."lon" + "dlon"; 1.452 + EXIT WHEN "step" >= "halfsteps"; 1.453 + IF "step" > 0 THEN 1.454 + "coords"[2*("steps_p"-"step")+1] := "lat"; 1.455 + "coords"[2*("steps_p"-"step")+2] := "geosearch_p"."lon" - "dlon"; 1.456 + END IF; 1.457 + "step" := "step" + 1; 1.458 + END LOOP; 1.459 + RETURN array_to_string("coords", ',')::POLYGON; 1.460 + END; 1.461 + $$; 1.462 + 1.463 +COMMENT ON FUNCTION "polysearch" 1.464 + ("geosearch", INTEGER) 1.465 + 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)'; 1.466 + 1.467 + 1.468 +CREATE FUNCTION "polysearch" 1.469 + ( "latitude_p" FLOAT, 1.470 + "longitude_p" FLOAT, 1.471 + "radius_p" FLOAT, 1.472 + "steps_p" INTEGER = 24 ) 1.473 + RETURNS POLYGON 1.474 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.475 + DECLARE 1.476 + "geosearch_v" "geosearch"; 1.477 + BEGIN 1.478 + "geosearch_v" := "geosearch"("latitude_p", "longitude_p"); 1.479 + "geosearch_v"."dist" := "radius_p"; 1.480 + RETURN "polysearch"("geosearch_v", "steps_p"); 1.481 + END; 1.482 + $$; 1.483 + 1.484 +COMMENT ON FUNCTION "polysearch" 1.485 + ("geosearch", INTEGER) 1.486 + 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)'; 1.487 + 1.488 + 1.489 +/* Usage of spatial indexing for other basic geometric objects: 1.490 + 1.491 +-- create table containing geographical coordinates in degrees: 1.492 +CREATE TABLE "t2" ( 1.493 + "id" SERIAL4 PRIMARY KEY, 1.494 + "geometry" PATH ); 1.495 + 1.496 +-- create a GiST (not SP-GiST!) index (with default poly_ops operator class): 1.497 +CREATE INDEX "t2_geoindex" ON "t2" USING gist (("polyindex"("geometry"))); 1.498 + 1.499 +-- return results within 10,000 meters of Brandenburg Gate: 1.500 +-- NOTE: use && operator for overlapping, use <@ operator for containment 1.501 +SELECT "t2".* 1.502 + FROM "t2" 1.503 + CROSS JOIN "polysearch"(52.5162746, 13.377704, 10000) AS "poly1" 1.504 + CROSS JOIN LATERAL "geoshift180"("poly1") AS "poly2" 1.505 + WHERE ( 1.506 + "polyindex"("geometry") && "poly1" OR 1.507 + "polyindex"("geometry") && "poly2" ); 1.508 + 1.509 +*/ 1.510 + 1.511 + 1.512 +CREATE FUNCTION "scattered_polygon_contains_point" 1.513 + ( "scattered_polygon_p" POLYGON[], 1.514 + "point_p" POINT ) 1.515 + RETURNS BOOLEAN 1.516 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.517 + SELECT count(1) % 2 = 1 1.518 + FROM unnest("scattered_polygon_p") AS "entry" 1.519 + WHERE "entry" @> "point_p" 1.520 + $$; 1.521 + 1.522 +COMMENT ON FUNCTION "scattered_polygon_contains_point" 1.523 + ( POLYGON[], POINT ) 1.524 + 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'; 1.525 + 1.526 + 1.527 +CREATE FUNCTION "scattered_polygon_contains_point" 1.528 + ( "scattered_polygon_p" POLYGON[], 1.529 + "point_x_p" FLOAT, 1.530 + "point_y_p" FLOAT ) 1.531 + RETURNS BOOLEAN 1.532 + LANGUAGE sql IMMUTABLE STRICT AS $$ 1.533 + SELECT "scattered_polygon_contains_point"( 1.534 + "scattered_polygon_p", 1.535 + point("point_x_p", "point_y_p") 1.536 + ) 1.537 + $$; 1.538 + 1.539 +COMMENT ON FUNCTION "scattered_polygon_contains_point" 1.540 + ( POLYGON[], FLOAT, FLOAT ) 1.541 + 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'; 1.542 + 1.543 + 1.544 +CREATE FUNCTION "scattered_polygon_bound_box" 1.545 + ( "scattered_polygon_p" POLYGON[] ) 1.546 + RETURNS BOX 1.547 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.548 + DECLARE 1.549 + "first_v" BOOLEAN; 1.550 + "bound_box_v" BOX; 1.551 + "polygon_v" POLYGON; 1.552 + BEGIN 1.553 + "first_v" := TRUE; 1.554 + FOR "polygon_v" IN SELECT * FROM unnest("scattered_polygon_p") LOOP 1.555 + IF "first_v" THEN 1.556 + "bound_box_v" := box("polygon_v"); 1.557 + "first_v" := FALSE; 1.558 + ELSE 1.559 + "bound_box_v" := bound_box("bound_box_v", box("polygon_v")); 1.560 + END IF; 1.561 + END LOOP; 1.562 + RETURN "bound_box_v"; 1.563 + END; 1.564 + $$; 1.565 + 1.566 +COMMENT ON FUNCTION "scattered_polygon_bound_box" 1.567 + ( POLYGON[] ) 1.568 + IS 'Takes a scattered (or hollow) polygon represented as an array of polygons and returns a bounding box'; 1.569 + 1.570 + 1.571 +/* Usage of spatial indexing for scattered (or hollow) polygons: 1.572 + 1.573 +-- create table containing geographical coordinates in degrees: 1.574 +CREATE TABLE "t3" ( 1.575 + "id" SERIAL4 PRIMARY KEY, 1.576 + "region" POLYGON[] ); 1.577 + 1.578 +-- create a GiST (not SP-GiST!) index (with default box_ops operator class): 1.579 +CREATE INDEX "t3_geoindex" ON "t3" USING gist (("scattered_polygon_bound_box"("region"))); 1.580 + 1.581 +-- return rows containing Brandenburg Gate's location: 1.582 +SELECT "t3".* 1.583 + FROM "t3" 1.584 + CROSS JOIN point(52.5162746, 13.377704) AS "point1" 1.585 + CROSS JOIN LATERAL "geoshift180"("point1") AS "point2" 1.586 + WHERE ( 1.587 + "scattered_polygon_contains_point"("region", "point1") OR 1.588 + "scattered_polygon_contains_point"("region", "point2") ); 1.589 + 1.590 +*/ 1.591 + 1.592 + 1.593 + 1.594 ------------------------- 1.595 -- Tables and indicies -- 1.596 ------------------------- 1.597 @@ -140,6 +717,8 @@ 1.598 "external_posts" TEXT, 1.599 "formatting_engine" TEXT, 1.600 "statement" TEXT, 1.601 + "latitude" NUMERIC, 1.602 + "longitude" NUMERIC, 1.603 "text_search_data" TSVECTOR, 1.604 CONSTRAINT "active_requires_activated_and_last_activity" 1.605 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)), 1.606 @@ -152,9 +731,12 @@ 1.607 CONSTRAINT "notification_dow_requires_notification_hour" 1.608 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL), 1.609 CONSTRAINT "name_not_null_if_activated" 1.610 - CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.611 + CHECK ("activated" ISNULL OR "name" NOTNULL), 1.612 + CONSTRAINT "latitude_and_lontitude_both_null_or_not_null" 1.613 + CHECK (("latitude" NOTNULL) = ("longitude" NOTNULL)) ); 1.614 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.615 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.616 +CREATE INDEX "member_geolocation_idx" ON "member" USING spgist (("geoindex"("latitude", "longitude"))); 1.617 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 1.618 CREATE TRIGGER "update_text_search_data" 1.619 BEFORE INSERT OR UPDATE ON "member" 1.620 @@ -207,6 +789,8 @@ 1.621 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization'; 1.622 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; 1.623 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; 1.624 +COMMENT ON COLUMN "member"."latitude" IS 'Latitude (in degrees) of member''s location'; 1.625 +COMMENT ON COLUMN "member"."longitude" IS 'Longitude (in degrees) of member''s location'; 1.626 1.627 1.628 CREATE TABLE "member_history" ( 1.629 @@ -390,10 +974,11 @@ 1.630 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE, 1.631 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 1.632 "polling" = ("issue_quorum" ISNULL) ), 1.633 - CONSTRAINT "timing" CHECK ( 1.634 + CONSTRAINT "min_admission_time_smaller_than_max_admission_time" CHECK ( 1.635 + "min_admission_time" < "max_admission_time" ), 1.636 + CONSTRAINT "timing_null_or_not_null_constraints" CHECK ( 1.637 ( "polling" = FALSE AND 1.638 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND 1.639 - "min_admission_time" <= "max_admission_time" AND 1.640 "discussion_time" NOTNULL AND 1.641 "verification_time" NOTNULL AND 1.642 "voting_time" NOTNULL ) OR 1.643 @@ -417,13 +1002,13 @@ 1.644 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings'; 1.645 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues'; 1.646 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'; 1.647 -COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open'; 1.648 +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"'; 1.649 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"'; 1.650 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"'; 1.651 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"'; 1.652 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'')'; 1.653 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")'; 1.654 -COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.655 +COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.656 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.657 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'; 1.658 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended'; 1.659 @@ -449,10 +1034,12 @@ 1.660 "description" TEXT NOT NULL DEFAULT '', 1.661 "external_reference" TEXT, 1.662 "member_count" INT4, 1.663 + "region" POLYGON[], 1.664 "text_search_data" TSVECTOR ); 1.665 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.666 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.667 CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.668 +CREATE INDEX "unit_geolocation_idx" ON "unit" USING gist (("scattered_polygon_bound_box"("region"))); 1.669 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 1.670 CREATE TRIGGER "update_text_search_data" 1.671 BEFORE INSERT OR UPDATE ON "unit" 1.672 @@ -465,7 +1052,8 @@ 1.673 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; 1.674 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit'; 1.675 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference'; 1.676 -COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; 1.677 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)'; 1.678 +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'; 1.679 1.680 1.681 CREATE TABLE "subscription" ( 1.682 @@ -495,11 +1083,11 @@ 1.683 "name" TEXT NOT NULL, 1.684 "description" TEXT NOT NULL DEFAULT '', 1.685 "external_reference" TEXT, 1.686 - "direct_member_count" INT4, 1.687 - "member_weight" INT4, 1.688 + "region" POLYGON[], 1.689 "text_search_data" TSVECTOR ); 1.690 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); 1.691 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.692 +CREATE INDEX "area_geolocation_idx" ON "area" USING gist (("scattered_polygon_bound_box"("region"))); 1.693 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data"); 1.694 CREATE TRIGGER "update_text_search_data" 1.695 BEFORE INSERT OR UPDATE ON "area" 1.696 @@ -509,10 +1097,9 @@ 1.697 1.698 COMMENT ON TABLE "area" IS 'Subject areas'; 1.699 1.700 -COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area'; 1.701 -COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference'; 1.702 -COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"'; 1.703 -COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations'; 1.704 +COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area'; 1.705 +COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference'; 1.706 +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'; 1.707 1.708 1.709 CREATE TABLE "ignored_area" ( 1.710 @@ -549,7 +1136,9 @@ 1.711 CREATE TABLE "admission_rule" ( 1.712 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition" 1.713 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.714 - "id" SERIAL4 PRIMARY KEY ); 1.715 + "id" SERIAL4 PRIMARY KEY, 1.716 + "name" TEXT NOT NULL, 1.717 + "description" TEXT NOT NULL DEFAULT '' ); 1.718 1.719 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'; 1.720 1.721 @@ -576,9 +1165,11 @@ 1.722 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'; 1.723 1.724 1.725 -CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze'); 1.726 - 1.727 -COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period'; 1.728 +CREATE TABLE "snapshot" ( 1.729 + "id" SERIAL8 PRIMARY KEY, 1.730 + "calculated" TIMESTAMPTZ NOT NULL DEFAULT now() ); 1.731 + 1.732 +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'; 1.733 1.734 1.735 CREATE TYPE "issue_state" AS ENUM ( 1.736 @@ -613,8 +1204,10 @@ 1.737 "discussion_time" INTERVAL NOT NULL, 1.738 "verification_time" INTERVAL NOT NULL, 1.739 "voting_time" INTERVAL NOT NULL, 1.740 - "snapshot" TIMESTAMPTZ, 1.741 - "latest_snapshot_event" "snapshot_event", 1.742 + "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.743 + "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE, 1.744 + "half_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.745 + "full_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.746 "population" INT4, 1.747 "voter_count" INT4, 1.748 "status_quo_schulze_rank" INT4, 1.749 @@ -650,14 +1243,13 @@ 1.750 "fully_frozen" <= "closed" ), 1.751 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK ( 1.752 "cleaned" ISNULL OR "closed" NOTNULL ), 1.753 - CONSTRAINT "last_snapshot_on_full_freeze" 1.754 - CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.755 - CONSTRAINT "freeze_requires_snapshot" 1.756 - CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL), 1.757 - CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" 1.758 - CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) ); 1.759 + CONSTRAINT "snapshot_required" CHECK ( 1.760 + --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND 1.761 + ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND 1.762 + ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) ); 1.763 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id"); 1.764 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 1.765 +CREATE INDEX "issue_state_idx" ON "issue" ("state"); 1.766 CREATE INDEX "issue_created_idx" ON "issue" ("created"); 1.767 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 1.768 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen"); 1.769 @@ -665,13 +1257,17 @@ 1.770 CREATE INDEX "issue_closed_idx" ON "issue" ("closed"); 1.771 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL; 1.772 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL; 1.773 +CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id"); 1.774 +CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id"); 1.775 +CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id"); 1.776 +CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id"); 1.777 1.778 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 1.779 1.780 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; 1.781 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference'; 1.782 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'; 1.783 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum" (TODO)'; 1.784 +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")'; 1.785 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.'; 1.786 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.'; 1.787 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.'; 1.788 @@ -681,8 +1277,10 @@ 1.789 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.790 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.791 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.792 -COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.793 -COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.794 +COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot'; 1.795 +COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase'; 1.796 +COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase'; 1.797 +COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase'; 1.798 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.799 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'; 1.800 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.801 @@ -720,6 +1318,10 @@ 1.802 "revoked" TIMESTAMPTZ, 1.803 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.804 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.805 + "latitude1" NUMERIC, 1.806 + "longitude1" NUMERIC, 1.807 + "latitude2" NUMERIC, 1.808 + "longitude2" NUMERIC, 1.809 "external_reference" TEXT, 1.810 "admitted" BOOLEAN, 1.811 "supporter_count" INT4, 1.812 @@ -742,10 +1344,17 @@ 1.813 "winner" BOOLEAN, 1.814 "rank" INT4, 1.815 "text_search_data" TSVECTOR, 1.816 + "draft_text_search_data" TSVECTOR, 1.817 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.818 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)), 1.819 CONSTRAINT "non_revoked_initiatives_cant_suggest_other" 1.820 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 1.821 + CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.822 + CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.823 + CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.824 + CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.825 + CONSTRAINT "latutude2_requires_latitude1" 1.826 + CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL), 1.827 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.828 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.829 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.830 @@ -767,7 +1376,10 @@ 1.831 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") ); 1.832 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.833 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.834 +CREATE INDEX "initiative_geolocation1_idx" ON "initiative" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.835 +CREATE INDEX "initiative_geolocation2_idx" ON "initiative" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.836 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.837 +CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data"); 1.838 CREATE TRIGGER "update_text_search_data" 1.839 BEFORE INSERT OR UPDATE ON "initiative" 1.840 FOR EACH ROW EXECUTE PROCEDURE 1.841 @@ -778,6 +1390,10 @@ 1.842 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")'; 1.843 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.844 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative'; 1.845 +COMMENT ON COLUMN "initiative"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied from most recent draft)'; 1.846 +COMMENT ON COLUMN "initiative"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied from most recent draft)'; 1.847 +COMMENT ON COLUMN "initiative"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)'; 1.848 +COMMENT ON COLUMN "initiative"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied from most recent draft)'; 1.849 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference'; 1.850 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.851 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.852 @@ -846,10 +1462,22 @@ 1.853 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.854 "formatting_engine" TEXT, 1.855 "content" TEXT NOT NULL, 1.856 + "latitude1" NUMERIC, 1.857 + "longitude1" NUMERIC, 1.858 + "latitude2" NUMERIC, 1.859 + "longitude2" NUMERIC, 1.860 "external_reference" TEXT, 1.861 - "text_search_data" TSVECTOR ); 1.862 + "text_search_data" TSVECTOR, 1.863 + CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.864 + CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.865 + CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.866 + CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.867 + CONSTRAINT "latutude2_requires_latitude1" 1.868 + CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) ); 1.869 CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.870 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 1.871 +CREATE INDEX "draft_geolocation1_idx" ON "draft" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.872 +CREATE INDEX "draft_geolocation2_idx" ON "draft" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.873 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 1.874 CREATE TRIGGER "update_text_search_data" 1.875 BEFORE INSERT OR UPDATE ON "draft" 1.876 @@ -860,6 +1488,10 @@ 1.877 1.878 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used'; 1.879 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"'; 1.880 +COMMENT ON COLUMN "draft"."latitude1" IS 'Latitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)'; 1.881 +COMMENT ON COLUMN "draft"."longitude1" IS 'Longitude (in degrees) of initiative (automatically copied to "initiative" table if draft is most recent)'; 1.882 +COMMENT ON COLUMN "draft"."latitude2" IS 'Latitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)'; 1.883 +COMMENT ON COLUMN "draft"."longitude2" IS 'Longitude (in degrees) of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)'; 1.884 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference'; 1.885 1.886 1.887 @@ -883,6 +1515,10 @@ 1.888 "name" TEXT NOT NULL, 1.889 "formatting_engine" TEXT, 1.890 "content" TEXT NOT NULL DEFAULT '', 1.891 + "latitude1" NUMERIC, 1.892 + "longitude1" NUMERIC, 1.893 + "latitude2" NUMERIC, 1.894 + "longitude2" NUMERIC, 1.895 "external_reference" TEXT, 1.896 "text_search_data" TSVECTOR, 1.897 "minus2_unfulfilled_count" INT4, 1.898 @@ -893,9 +1529,17 @@ 1.899 "plus1_fulfilled_count" INT4, 1.900 "plus2_unfulfilled_count" INT4, 1.901 "plus2_fulfilled_count" INT4, 1.902 - "proportional_order" INT4 ); 1.903 + "proportional_order" INT4, 1.904 + CONSTRAINT "latitude1_and_lontitude1_both_null_or_not_null" 1.905 + CHECK (("latitude1" NOTNULL) = ("longitude1" NOTNULL)), 1.906 + CONSTRAINT "latitude2_and_longitude2_both_null_or_not_null" 1.907 + CHECK (("latitude2" NOTNULL) = ("longitude2" NOTNULL)), 1.908 + CONSTRAINT "latutude2_requires_latitude1" 1.909 + CHECK ("latitude2" ISNULL OR "latitude1" NOTNULL) ); 1.910 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.911 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.912 +CREATE INDEX "suggestion_geolocation1_idx" ON "suggestion" USING spgist (("geoindex"("latitude1", "longitude1"))); 1.913 +CREATE INDEX "suggestion_geolocation2_idx" ON "suggestion" USING spgist (("geoindex"("latitude2", "longitude2"))); 1.914 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.915 CREATE TRIGGER "update_text_search_data" 1.916 BEFORE INSERT OR UPDATE ON "suggestion" 1.917 @@ -906,6 +1550,10 @@ 1.918 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'; 1.919 1.920 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")'; 1.921 +COMMENT ON COLUMN "suggestion"."latitude1" IS 'Latitude (in degrees) of suggestion'; 1.922 +COMMENT ON COLUMN "suggestion"."longitude1" IS 'Longitude (in degrees) of suggestion'; 1.923 +COMMENT ON COLUMN "suggestion"."latitude2" IS 'Latitude (in degrees) of suggestion''s second marker'; 1.924 +COMMENT ON COLUMN "suggestion"."longitude2" IS 'Longitude (in degrees) of suggestion''s second marker'; 1.925 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference'; 1.926 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.927 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.928 @@ -937,6 +1585,22 @@ 1.929 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings'; 1.930 1.931 1.932 +CREATE TABLE "temporary_suggestion_counts" ( 1.933 + "id" INT8 PRIMARY KEY, --REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.934 + "minus2_unfulfilled_count" INT4 NOT NULL, 1.935 + "minus2_fulfilled_count" INT4 NOT NULL, 1.936 + "minus1_unfulfilled_count" INT4 NOT NULL, 1.937 + "minus1_fulfilled_count" INT4 NOT NULL, 1.938 + "plus1_unfulfilled_count" INT4 NOT NULL, 1.939 + "plus1_fulfilled_count" INT4 NOT NULL, 1.940 + "plus2_unfulfilled_count" INT4 NOT NULL, 1.941 + "plus2_fulfilled_count" INT4 NOT NULL ); 1.942 + 1.943 +COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"'; 1.944 + 1.945 +COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; 1.946 + 1.947 + 1.948 CREATE TABLE "privilege" ( 1.949 PRIMARY KEY ("unit_id", "member_id"), 1.950 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.951 @@ -960,15 +1624,6 @@ 1.952 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'; 1.953 1.954 1.955 -CREATE TABLE "membership" ( 1.956 - PRIMARY KEY ("area_id", "member_id"), 1.957 - "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.958 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.959 -CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id"); 1.960 - 1.961 -COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; 1.962 - 1.963 - 1.964 CREATE TABLE "interest" ( 1.965 PRIMARY KEY ("issue_id", "member_id"), 1.966 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.967 @@ -1055,56 +1710,36 @@ 1.968 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; 1.969 1.970 1.971 -CREATE TABLE "direct_population_snapshot" ( 1.972 - PRIMARY KEY ("issue_id", "event", "member_id"), 1.973 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.974 - "event" "snapshot_event", 1.975 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.976 - "weight" INT4 ); 1.977 -CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id"); 1.978 - 1.979 -COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table'; 1.980 - 1.981 -COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.982 -COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"'; 1.983 - 1.984 - 1.985 -CREATE TABLE "delegating_population_snapshot" ( 1.986 - PRIMARY KEY ("issue_id", "event", "member_id"), 1.987 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.988 - "event" "snapshot_event", 1.989 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.990 - "weight" INT4, 1.991 - "scope" "delegation_scope" NOT NULL, 1.992 - "delegate_member_ids" INT4[] NOT NULL ); 1.993 -CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id"); 1.994 - 1.995 -COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table; for corrections refer to column "issue_notice" of "issue" table'; 1.996 - 1.997 -COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.998 -COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member'; 1.999 -COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight'; 1.1000 -COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"'; 1.1001 +CREATE TABLE "snapshot_issue" ( 1.1002 + PRIMARY KEY ("snapshot_id", "issue_id"), 1.1003 + "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1004 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.1005 +CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id"); 1.1006 + 1.1007 +COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot'; 1.1008 1.1009 1.1010 CREATE TABLE "direct_interest_snapshot" ( 1.1011 - PRIMARY KEY ("issue_id", "event", "member_id"), 1.1012 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1013 - "event" "snapshot_event", 1.1014 + PRIMARY KEY ("snapshot_id", "issue_id", "member_id"), 1.1015 + "snapshot_id" INT8, 1.1016 + "issue_id" INT4, 1.1017 + FOREIGN KEY ("snapshot_id", "issue_id") 1.1018 + REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1019 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.1020 "weight" INT4 ); 1.1021 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id"); 1.1022 1.1023 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'; 1.1024 1.1025 -COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.1026 -COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"'; 1.1027 +COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"'; 1.1028 1.1029 1.1030 CREATE TABLE "delegating_interest_snapshot" ( 1.1031 - PRIMARY KEY ("issue_id", "event", "member_id"), 1.1032 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1033 - "event" "snapshot_event", 1.1034 + PRIMARY KEY ("snapshot_id", "issue_id", "member_id"), 1.1035 + "snapshot_id" INT8, 1.1036 + "issue_id" INT4, 1.1037 + FOREIGN KEY ("snapshot_id", "issue_id") 1.1038 + REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1039 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.1040 "weight" INT4, 1.1041 "scope" "delegation_scope" NOT NULL, 1.1042 @@ -1113,39 +1748,39 @@ 1.1043 1.1044 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'; 1.1045 1.1046 -COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.1047 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member'; 1.1048 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight'; 1.1049 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"'; 1.1050 1.1051 1.1052 CREATE TABLE "direct_supporter_snapshot" ( 1.1053 + PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"), 1.1054 + "snapshot_id" INT8, 1.1055 "issue_id" INT4 NOT NULL, 1.1056 - PRIMARY KEY ("initiative_id", "event", "member_id"), 1.1057 + FOREIGN KEY ("snapshot_id", "issue_id") 1.1058 + REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1059 "initiative_id" INT4, 1.1060 - "event" "snapshot_event", 1.1061 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.1062 "draft_id" INT8 NOT NULL, 1.1063 "informed" BOOLEAN NOT NULL, 1.1064 "satisfied" BOOLEAN NOT NULL, 1.1065 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1066 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE, 1.1067 - FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.1068 + FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.1069 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id"); 1.1070 1.1071 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'; 1.1072 1.1073 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'; 1.1074 -COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.1075 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative'; 1.1076 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s'; 1.1077 1.1078 1.1079 CREATE TABLE "non_voter" ( 1.1080 - PRIMARY KEY ("issue_id", "member_id"), 1.1081 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1082 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.1083 -CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); 1.1084 + PRIMARY KEY ("member_id", "issue_id"), 1.1085 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1086 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.1087 +CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id"); 1.1088 1.1089 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; 1.1090 1.1091 @@ -1735,6 +2370,31 @@ 1.1092 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.'; 1.1093 1.1094 1.1095 + 1.1096 +--------------------------------- 1.1097 +-- Delete incomplete snapshots -- 1.1098 +--------------------------------- 1.1099 + 1.1100 + 1.1101 +CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"() 1.1102 + RETURNS TRIGGER 1.1103 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1104 + BEGIN 1.1105 + DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id"; 1.1106 + RETURN NULL; 1.1107 + END; 1.1108 + $$; 1.1109 + 1.1110 +CREATE TRIGGER "delete_snapshot_on_partial_delete" 1.1111 + AFTER DELETE ON "snapshot_issue" 1.1112 + FOR EACH ROW EXECUTE PROCEDURE 1.1113 + "delete_snapshot_on_partial_delete_trigger"(); 1.1114 + 1.1115 +COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"'; 1.1116 +COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot'; 1.1117 + 1.1118 + 1.1119 + 1.1120 --------------------------------------------------------------- 1.1121 -- Ensure that votes are not modified when issues are closed -- 1.1122 --------------------------------------------------------------- 1.1123 @@ -1879,6 +2539,63 @@ 1.1124 1.1125 1.1126 1.1127 +------------------------------------------------------- 1.1128 +-- Automatic copying of values for indexing purposes -- 1.1129 +------------------------------------------------------- 1.1130 + 1.1131 + 1.1132 +CREATE FUNCTION "copy_current_draft_data" 1.1133 + ("initiative_id_p" "initiative"."id"%TYPE ) 1.1134 + RETURNS VOID 1.1135 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1136 + BEGIN 1.1137 + PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p" 1.1138 + FOR UPDATE; 1.1139 + UPDATE "initiative" SET 1.1140 + "latitude1" = "draft"."latitude1", 1.1141 + "longitude1" = "draft"."longitude1", 1.1142 + "latitude2" = "draft"."latitude2", 1.1143 + "longitude2" = "draft"."longitude2", 1.1144 + "draft_text_search_data" = "draft"."text_search_data" 1.1145 + FROM "current_draft" AS "draft" 1.1146 + WHERE "initiative"."id" = "initiative_id_p" 1.1147 + AND "draft"."initiative_id" = "initiative_id_p"; 1.1148 + END; 1.1149 + $$; 1.1150 + 1.1151 +COMMENT ON FUNCTION "copy_current_draft_data" 1.1152 + ( "initiative"."id"%TYPE ) 1.1153 + IS 'Helper function for function "copy_current_draft_data_trigger"'; 1.1154 + 1.1155 + 1.1156 +CREATE FUNCTION "copy_current_draft_data_trigger"() 1.1157 + RETURNS TRIGGER 1.1158 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1159 + BEGIN 1.1160 + IF TG_OP='DELETE' THEN 1.1161 + PERFORM "copy_current_draft_data"(OLD."initiative_id"); 1.1162 + ELSE 1.1163 + IF TG_OP='UPDATE' THEN 1.1164 + IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN 1.1165 + PERFORM "copy_current_draft_data"(OLD."initiative_id"); 1.1166 + END IF; 1.1167 + END IF; 1.1168 + PERFORM "copy_current_draft_data"(NEW."initiative_id"); 1.1169 + END IF; 1.1170 + RETURN NULL; 1.1171 + END; 1.1172 + $$; 1.1173 + 1.1174 +CREATE TRIGGER "copy_current_draft_data" 1.1175 + AFTER INSERT OR UPDATE OR DELETE ON "draft" 1.1176 + FOR EACH ROW EXECUTE PROCEDURE 1.1177 + "copy_current_draft_data_trigger"(); 1.1178 + 1.1179 +COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"'; 1.1180 +COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"'; 1.1181 + 1.1182 + 1.1183 + 1.1184 ----------------------------------------------------- 1.1185 -- Automatic calculation of certain default values -- 1.1186 ----------------------------------------------------- 1.1187 @@ -2061,6 +2778,7 @@ 1.1188 "issue".*, 1.1189 max("initiative"."supporter_count") AS "max_supporter_count" 1.1190 FROM "issue" 1.1191 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.1192 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.1193 JOIN "area" ON "issue"."area_id" = "area"."id" 1.1194 JOIN "admission_rule_condition" 1.1195 @@ -2071,15 +2789,19 @@ 1.1196 ) 1.1197 AND ( 1.1198 "admission_rule_condition"."area_id" ISNULL OR 1.1199 - "admission_rule_condition"."area_id" = "area"."id" 1.1200 + "admission_rule_condition"."area_id" = "issue"."area_id" 1.1201 ) 1.1202 JOIN "applicable_admission_rule" 1.1203 ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id" 1.1204 WHERE "issue"."state" = 'admission'::"issue_state" 1.1205 + AND now() >= "issue"."created" + "issue"."min_admission_time" 1.1206 + AND "initiative"."supporter_count" >= "policy"."issue_quorum" 1.1207 + AND "initiative"."revoked" ISNULL 1.1208 GROUP BY "issue"."id" 1.1209 - ORDER BY "max_supporter_count" DESC, "issue"."id"; 1.1210 - 1.1211 -COMMENT ON VIEW "issue_for_admission" IS 'Issues that are eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the first issue in this view'; 1.1212 + ORDER BY "max_supporter_count" DESC, "issue"."id" 1.1213 + LIMIT 1; 1.1214 + 1.1215 +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'; 1.1216 1.1217 1.1218 CREATE VIEW "unit_delegation" AS 1.1219 @@ -2155,67 +2877,6 @@ 1.1220 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 1.1221 1.1222 1.1223 -CREATE FUNCTION "membership_weight_with_skipping" 1.1224 - ( "area_id_p" "area"."id"%TYPE, 1.1225 - "member_id_p" "member"."id"%TYPE, 1.1226 - "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[] 1.1227 - RETURNS INT4 1.1228 - LANGUAGE 'plpgsql' STABLE AS $$ 1.1229 - DECLARE 1.1230 - "sum_v" INT4; 1.1231 - "delegation_row" "area_delegation"%ROWTYPE; 1.1232 - BEGIN 1.1233 - "sum_v" := 1; 1.1234 - FOR "delegation_row" IN 1.1235 - SELECT "area_delegation".* 1.1236 - FROM "area_delegation" LEFT JOIN "membership" 1.1237 - ON "membership"."area_id" = "area_id_p" 1.1238 - AND "membership"."member_id" = "area_delegation"."truster_id" 1.1239 - WHERE "area_delegation"."area_id" = "area_id_p" 1.1240 - AND "area_delegation"."trustee_id" = "member_id_p" 1.1241 - AND "membership"."member_id" ISNULL 1.1242 - LOOP 1.1243 - IF NOT 1.1244 - "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"] 1.1245 - THEN 1.1246 - "sum_v" := "sum_v" + "membership_weight_with_skipping"( 1.1247 - "area_id_p", 1.1248 - "delegation_row"."truster_id", 1.1249 - "skip_member_ids_p" || "delegation_row"."truster_id" 1.1250 - ); 1.1251 - END IF; 1.1252 - END LOOP; 1.1253 - RETURN "sum_v"; 1.1254 - END; 1.1255 - $$; 1.1256 - 1.1257 -COMMENT ON FUNCTION "membership_weight_with_skipping" 1.1258 - ( "area"."id"%TYPE, 1.1259 - "member"."id"%TYPE, 1.1260 - INT4[] ) 1.1261 - IS 'Helper function for "membership_weight" function'; 1.1262 - 1.1263 - 1.1264 -CREATE FUNCTION "membership_weight" 1.1265 - ( "area_id_p" "area"."id"%TYPE, 1.1266 - "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[] 1.1267 - RETURNS INT4 1.1268 - LANGUAGE 'plpgsql' STABLE AS $$ 1.1269 - BEGIN 1.1270 - RETURN "membership_weight_with_skipping"( 1.1271 - "area_id_p", 1.1272 - "member_id_p", 1.1273 - ARRAY["member_id_p"] 1.1274 - ); 1.1275 - END; 1.1276 - $$; 1.1277 - 1.1278 -COMMENT ON FUNCTION "membership_weight" 1.1279 - ( "area"."id"%TYPE, 1.1280 - "member"."id"%TYPE ) 1.1281 - IS 'Calculates the potential voting weight of a member in a given area'; 1.1282 - 1.1283 - 1.1284 CREATE VIEW "member_count_view" AS 1.1285 SELECT count(1) AS "total_count" FROM "member" WHERE "active"; 1.1286 1.1287 @@ -2238,56 +2899,16 @@ 1.1288 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; 1.1289 1.1290 1.1291 -CREATE VIEW "area_member_count" AS 1.1292 - SELECT 1.1293 - "area"."id" AS "area_id", 1.1294 - count("member"."id") AS "direct_member_count", 1.1295 - coalesce( 1.1296 - sum( 1.1297 - CASE WHEN "member"."id" NOTNULL THEN 1.1298 - "membership_weight"("area"."id", "member"."id") 1.1299 - ELSE 0 END 1.1300 - ) 1.1301 - ) AS "member_weight" 1.1302 - FROM "area" 1.1303 - LEFT JOIN "membership" 1.1304 - ON "area"."id" = "membership"."area_id" 1.1305 - LEFT JOIN "privilege" 1.1306 - ON "privilege"."unit_id" = "area"."unit_id" 1.1307 - AND "privilege"."member_id" = "membership"."member_id" 1.1308 - AND "privilege"."voting_right" 1.1309 - LEFT JOIN "member" 1.1310 - ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! 1.1311 - AND "member"."active" 1.1312 - GROUP BY "area"."id"; 1.1313 - 1.1314 -COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; 1.1315 - 1.1316 - 1.1317 CREATE VIEW "opening_draft" AS 1.1318 - SELECT "draft".* FROM ( 1.1319 - SELECT 1.1320 - "initiative"."id" AS "initiative_id", 1.1321 - min("draft"."id") AS "draft_id" 1.1322 - FROM "initiative" JOIN "draft" 1.1323 - ON "initiative"."id" = "draft"."initiative_id" 1.1324 - GROUP BY "initiative"."id" 1.1325 - ) AS "subquery" 1.1326 - JOIN "draft" ON "subquery"."draft_id" = "draft"."id"; 1.1327 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1328 + ORDER BY "initiative_id", "id"; 1.1329 1.1330 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives'; 1.1331 1.1332 1.1333 CREATE VIEW "current_draft" AS 1.1334 - SELECT "draft".* FROM ( 1.1335 - SELECT 1.1336 - "initiative"."id" AS "initiative_id", 1.1337 - max("draft"."id") AS "draft_id" 1.1338 - FROM "initiative" JOIN "draft" 1.1339 - ON "initiative"."id" = "draft"."initiative_id" 1.1340 - GROUP BY "initiative"."id" 1.1341 - ) AS "subquery" 1.1342 - JOIN "draft" ON "subquery"."draft_id" = "draft"."id"; 1.1343 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1344 + ORDER BY "initiative_id", "id" DESC; 1.1345 1.1346 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative'; 1.1347 1.1348 @@ -2301,7 +2922,7 @@ 1.1349 1.1350 1.1351 CREATE VIEW "issue_supporter_in_admission_state" AS 1.1352 - SELECT DISTINCT -- TODO: DISTINCT needed? 1.1353 + SELECT 1.1354 "area"."unit_id", 1.1355 "issue"."area_id", 1.1356 "issue"."id" AS "issue_id", 1.1357 @@ -2311,8 +2932,8 @@ 1.1358 JOIN "area" ON "area"."id" = "issue"."area_id" 1.1359 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 1.1360 JOIN "direct_interest_snapshot" 1.1361 - ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.1362 - AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.1363 + ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id" 1.1364 + AND "direct_interest_snapshot"."issue_id" = "issue"."id" 1.1365 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id" 1.1366 WHERE "issue"."state" = 'admission'::"issue_state"; 1.1367 1.1368 @@ -2357,8 +2978,8 @@ 1.1369 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" 1.1370 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.1371 JOIN "direct_interest_snapshot" 1.1372 - ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.1373 - AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.1374 + ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id" 1.1375 + AND "direct_interest_snapshot"."issue_id" = "issue"."id" 1.1376 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; 1.1377 1.1378 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative'; 1.1379 @@ -3462,11 +4083,6 @@ 1.1380 UPDATE "unit" SET "member_count" = "view"."member_count" 1.1381 FROM "unit_member_count" AS "view" 1.1382 WHERE "view"."unit_id" = "unit"."id"; 1.1383 - UPDATE "area" SET 1.1384 - "direct_member_count" = "view"."direct_member_count", 1.1385 - "member_weight" = "view"."member_weight" 1.1386 - FROM "area_member_count" AS "view" 1.1387 - WHERE "view"."area_id" = "area"."id"; 1.1388 RETURN; 1.1389 END; 1.1390 $$; 1.1391 @@ -3482,29 +4098,29 @@ 1.1392 1.1393 CREATE VIEW "remaining_harmonic_supporter_weight" AS 1.1394 SELECT 1.1395 + "direct_interest_snapshot"."snapshot_id", 1.1396 "direct_interest_snapshot"."issue_id", 1.1397 - "direct_interest_snapshot"."event", 1.1398 "direct_interest_snapshot"."member_id", 1.1399 "direct_interest_snapshot"."weight" AS "weight_num", 1.1400 count("initiative"."id") AS "weight_den" 1.1401 FROM "issue" 1.1402 JOIN "direct_interest_snapshot" 1.1403 - ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.1404 - AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.1405 + ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id" 1.1406 + AND "issue"."id" = "direct_interest_snapshot"."issue_id" 1.1407 JOIN "initiative" 1.1408 ON "issue"."id" = "initiative"."issue_id" 1.1409 AND "initiative"."harmonic_weight" ISNULL 1.1410 JOIN "direct_supporter_snapshot" 1.1411 - ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.1412 - AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 1.1413 + ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id" 1.1414 + AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.1415 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" 1.1416 AND ( 1.1417 "direct_supporter_snapshot"."satisfied" = TRUE OR 1.1418 coalesce("initiative"."admitted", FALSE) = FALSE 1.1419 ) 1.1420 GROUP BY 1.1421 + "direct_interest_snapshot"."snapshot_id", 1.1422 "direct_interest_snapshot"."issue_id", 1.1423 - "direct_interest_snapshot"."event", 1.1424 "direct_interest_snapshot"."member_id", 1.1425 "direct_interest_snapshot"."weight"; 1.1426 1.1427 @@ -3523,8 +4139,8 @@ 1.1428 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id" 1.1429 AND "initiative"."harmonic_weight" ISNULL 1.1430 JOIN "direct_supporter_snapshot" 1.1431 - ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.1432 - AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" 1.1433 + ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id" 1.1434 + AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.1435 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" 1.1436 AND ( 1.1437 "direct_supporter_snapshot"."satisfied" = TRUE OR 1.1438 @@ -3644,146 +4260,9 @@ 1.1439 ------------------------------ 1.1440 1.1441 1.1442 -CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot" 1.1443 - ( "issue_id_p" "issue"."id"%TYPE, 1.1444 - "member_id_p" "member"."id"%TYPE, 1.1445 - "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE ) 1.1446 - RETURNS "direct_population_snapshot"."weight"%TYPE 1.1447 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1448 - DECLARE 1.1449 - "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.1450 - "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE; 1.1451 - "weight_v" INT4; 1.1452 - "sub_weight_v" INT4; 1.1453 - BEGIN 1.1454 - PERFORM "require_transaction_isolation"(); 1.1455 - "weight_v" := 0; 1.1456 - FOR "issue_delegation_row" IN 1.1457 - SELECT * FROM "issue_delegation" 1.1458 - WHERE "trustee_id" = "member_id_p" 1.1459 - AND "issue_id" = "issue_id_p" 1.1460 - LOOP 1.1461 - IF NOT EXISTS ( 1.1462 - SELECT NULL FROM "direct_population_snapshot" 1.1463 - WHERE "issue_id" = "issue_id_p" 1.1464 - AND "event" = 'periodic' 1.1465 - AND "member_id" = "issue_delegation_row"."truster_id" 1.1466 - ) AND NOT EXISTS ( 1.1467 - SELECT NULL FROM "delegating_population_snapshot" 1.1468 - WHERE "issue_id" = "issue_id_p" 1.1469 - AND "event" = 'periodic' 1.1470 - AND "member_id" = "issue_delegation_row"."truster_id" 1.1471 - ) THEN 1.1472 - "delegate_member_ids_v" := 1.1473 - "member_id_p" || "delegate_member_ids_p"; 1.1474 - INSERT INTO "delegating_population_snapshot" ( 1.1475 - "issue_id", 1.1476 - "event", 1.1477 - "member_id", 1.1478 - "scope", 1.1479 - "delegate_member_ids" 1.1480 - ) VALUES ( 1.1481 - "issue_id_p", 1.1482 - 'periodic', 1.1483 - "issue_delegation_row"."truster_id", 1.1484 - "issue_delegation_row"."scope", 1.1485 - "delegate_member_ids_v" 1.1486 - ); 1.1487 - "sub_weight_v" := 1 + 1.1488 - "weight_of_added_delegations_for_population_snapshot"( 1.1489 - "issue_id_p", 1.1490 - "issue_delegation_row"."truster_id", 1.1491 - "delegate_member_ids_v" 1.1492 - ); 1.1493 - UPDATE "delegating_population_snapshot" 1.1494 - SET "weight" = "sub_weight_v" 1.1495 - WHERE "issue_id" = "issue_id_p" 1.1496 - AND "event" = 'periodic' 1.1497 - AND "member_id" = "issue_delegation_row"."truster_id"; 1.1498 - "weight_v" := "weight_v" + "sub_weight_v"; 1.1499 - END IF; 1.1500 - END LOOP; 1.1501 - RETURN "weight_v"; 1.1502 - END; 1.1503 - $$; 1.1504 - 1.1505 -COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot" 1.1506 - ( "issue"."id"%TYPE, 1.1507 - "member"."id"%TYPE, 1.1508 - "delegating_population_snapshot"."delegate_member_ids"%TYPE ) 1.1509 - IS 'Helper function for "create_population_snapshot" function'; 1.1510 - 1.1511 - 1.1512 -CREATE FUNCTION "create_population_snapshot" 1.1513 - ( "issue_id_p" "issue"."id"%TYPE ) 1.1514 - RETURNS VOID 1.1515 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1516 - DECLARE 1.1517 - "member_id_v" "member"."id"%TYPE; 1.1518 - BEGIN 1.1519 - PERFORM "require_transaction_isolation"(); 1.1520 - DELETE FROM "direct_population_snapshot" 1.1521 - WHERE "issue_id" = "issue_id_p" 1.1522 - AND "event" = 'periodic'; 1.1523 - DELETE FROM "delegating_population_snapshot" 1.1524 - WHERE "issue_id" = "issue_id_p" 1.1525 - AND "event" = 'periodic'; 1.1526 - INSERT INTO "direct_population_snapshot" 1.1527 - ("issue_id", "event", "member_id") 1.1528 - SELECT 1.1529 - "issue_id_p" AS "issue_id", 1.1530 - 'periodic'::"snapshot_event" AS "event", 1.1531 - "member"."id" AS "member_id" 1.1532 - FROM "issue" 1.1533 - JOIN "area" ON "issue"."area_id" = "area"."id" 1.1534 - JOIN "membership" ON "area"."id" = "membership"."area_id" 1.1535 - JOIN "member" ON "membership"."member_id" = "member"."id" 1.1536 - JOIN "privilege" 1.1537 - ON "privilege"."unit_id" = "area"."unit_id" 1.1538 - AND "privilege"."member_id" = "member"."id" 1.1539 - WHERE "issue"."id" = "issue_id_p" 1.1540 - AND "member"."active" AND "privilege"."voting_right" 1.1541 - UNION 1.1542 - SELECT 1.1543 - "issue_id_p" AS "issue_id", 1.1544 - 'periodic'::"snapshot_event" AS "event", 1.1545 - "member"."id" AS "member_id" 1.1546 - FROM "issue" 1.1547 - JOIN "area" ON "issue"."area_id" = "area"."id" 1.1548 - JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1549 - JOIN "member" ON "interest"."member_id" = "member"."id" 1.1550 - JOIN "privilege" 1.1551 - ON "privilege"."unit_id" = "area"."unit_id" 1.1552 - AND "privilege"."member_id" = "member"."id" 1.1553 - WHERE "issue"."id" = "issue_id_p" 1.1554 - AND "member"."active" AND "privilege"."voting_right"; 1.1555 - FOR "member_id_v" IN 1.1556 - SELECT "member_id" FROM "direct_population_snapshot" 1.1557 - WHERE "issue_id" = "issue_id_p" 1.1558 - AND "event" = 'periodic' 1.1559 - LOOP 1.1560 - UPDATE "direct_population_snapshot" SET 1.1561 - "weight" = 1 + 1.1562 - "weight_of_added_delegations_for_population_snapshot"( 1.1563 - "issue_id_p", 1.1564 - "member_id_v", 1.1565 - '{}' 1.1566 - ) 1.1567 - WHERE "issue_id" = "issue_id_p" 1.1568 - AND "event" = 'periodic' 1.1569 - AND "member_id" = "member_id_v"; 1.1570 - END LOOP; 1.1571 - RETURN; 1.1572 - END; 1.1573 - $$; 1.1574 - 1.1575 -COMMENT ON FUNCTION "create_population_snapshot" 1.1576 - ( "issue"."id"%TYPE ) 1.1577 - IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.'; 1.1578 - 1.1579 - 1.1580 -CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot" 1.1581 - ( "issue_id_p" "issue"."id"%TYPE, 1.1582 +CREATE FUNCTION "weight_of_added_delegations_for_snapshot" 1.1583 + ( "snapshot_id_p" "snapshot"."id"%TYPE, 1.1584 + "issue_id_p" "issue"."id"%TYPE, 1.1585 "member_id_p" "member"."id"%TYPE, 1.1586 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 1.1587 RETURNS "direct_interest_snapshot"."weight"%TYPE 1.1588 @@ -3803,40 +4282,41 @@ 1.1589 LOOP 1.1590 IF NOT EXISTS ( 1.1591 SELECT NULL FROM "direct_interest_snapshot" 1.1592 - WHERE "issue_id" = "issue_id_p" 1.1593 - AND "event" = 'periodic' 1.1594 + WHERE "snapshot_id" = "snapshot_id_p" 1.1595 + AND "issue_id" = "issue_id_p" 1.1596 AND "member_id" = "issue_delegation_row"."truster_id" 1.1597 ) AND NOT EXISTS ( 1.1598 SELECT NULL FROM "delegating_interest_snapshot" 1.1599 - WHERE "issue_id" = "issue_id_p" 1.1600 - AND "event" = 'periodic' 1.1601 + WHERE "snapshot_id" = "snapshot_id_p" 1.1602 + AND "issue_id" = "issue_id_p" 1.1603 AND "member_id" = "issue_delegation_row"."truster_id" 1.1604 ) THEN 1.1605 "delegate_member_ids_v" := 1.1606 "member_id_p" || "delegate_member_ids_p"; 1.1607 INSERT INTO "delegating_interest_snapshot" ( 1.1608 + "snapshot_id", 1.1609 "issue_id", 1.1610 - "event", 1.1611 "member_id", 1.1612 "scope", 1.1613 "delegate_member_ids" 1.1614 ) VALUES ( 1.1615 + "snapshot_id_p", 1.1616 "issue_id_p", 1.1617 - 'periodic', 1.1618 "issue_delegation_row"."truster_id", 1.1619 "issue_delegation_row"."scope", 1.1620 "delegate_member_ids_v" 1.1621 ); 1.1622 "sub_weight_v" := 1 + 1.1623 - "weight_of_added_delegations_for_interest_snapshot"( 1.1624 + "weight_of_added_delegations_for_snapshot"( 1.1625 + "snapshot_id_p", 1.1626 "issue_id_p", 1.1627 "issue_delegation_row"."truster_id", 1.1628 "delegate_member_ids_v" 1.1629 ); 1.1630 UPDATE "delegating_interest_snapshot" 1.1631 SET "weight" = "sub_weight_v" 1.1632 - WHERE "issue_id" = "issue_id_p" 1.1633 - AND "event" = 'periodic' 1.1634 + WHERE "snapshot_id" = "snapshot_id_p" 1.1635 + AND "issue_id" = "issue_id_p" 1.1636 AND "member_id" = "issue_delegation_row"."truster_id"; 1.1637 "weight_v" := "weight_v" + "sub_weight_v"; 1.1638 END IF; 1.1639 @@ -3845,314 +4325,273 @@ 1.1640 END; 1.1641 $$; 1.1642 1.1643 -COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot" 1.1644 - ( "issue"."id"%TYPE, 1.1645 +COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot" 1.1646 + ( "snapshot"."id"%TYPE, 1.1647 + "issue"."id"%TYPE, 1.1648 "member"."id"%TYPE, 1.1649 "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 1.1650 - IS 'Helper function for "create_interest_snapshot" function'; 1.1651 - 1.1652 - 1.1653 -CREATE FUNCTION "create_interest_snapshot" 1.1654 + IS 'Helper function for "fill_snapshot" function'; 1.1655 + 1.1656 + 1.1657 +CREATE FUNCTION "take_snapshot" 1.1658 ( "issue_id_p" "issue"."id"%TYPE ) 1.1659 - RETURNS VOID 1.1660 + RETURNS "snapshot"."id"%TYPE 1.1661 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1662 DECLARE 1.1663 - "member_id_v" "member"."id"%TYPE; 1.1664 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.1665 + "issue_id_v" "issue"."id"%TYPE; 1.1666 + "member_id_v" "member"."id"%TYPE; 1.1667 BEGIN 1.1668 PERFORM "require_transaction_isolation"(); 1.1669 - DELETE FROM "direct_interest_snapshot" 1.1670 - WHERE "issue_id" = "issue_id_p" 1.1671 - AND "event" = 'periodic'; 1.1672 - DELETE FROM "delegating_interest_snapshot" 1.1673 - WHERE "issue_id" = "issue_id_p" 1.1674 - AND "event" = 'periodic'; 1.1675 - DELETE FROM "direct_supporter_snapshot" 1.1676 - USING "initiative" -- NOTE: due to missing index on issue_id 1.1677 - WHERE "initiative"."issue_id" = "issue_id_p" 1.1678 - AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.1679 - AND "direct_supporter_snapshot"."event" = 'periodic'; 1.1680 - INSERT INTO "direct_interest_snapshot" 1.1681 - ("issue_id", "event", "member_id") 1.1682 - SELECT 1.1683 - "issue_id_p" AS "issue_id", 1.1684 - 'periodic' AS "event", 1.1685 - "member"."id" AS "member_id" 1.1686 - FROM "issue" 1.1687 - JOIN "area" ON "issue"."area_id" = "area"."id" 1.1688 - JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1689 - JOIN "member" ON "interest"."member_id" = "member"."id" 1.1690 - JOIN "privilege" 1.1691 - ON "privilege"."unit_id" = "area"."unit_id" 1.1692 - AND "privilege"."member_id" = "member"."id" 1.1693 - WHERE "issue"."id" = "issue_id_p" 1.1694 - AND "member"."active" AND "privilege"."voting_right"; 1.1695 - FOR "member_id_v" IN 1.1696 - SELECT "member_id" FROM "direct_interest_snapshot" 1.1697 - WHERE "issue_id" = "issue_id_p" 1.1698 - AND "event" = 'periodic' 1.1699 + INSERT INTO "snapshot" DEFAULT VALUES 1.1700 + RETURNING "id" INTO "snapshot_id_v"; 1.1701 + FOR "issue_id_v" IN 1.1702 + SELECT "id" FROM "issue" 1.1703 + WHERE CASE WHEN "issue_id_p" ISNULL THEN 1.1704 + "state" = 'admission' 1.1705 + ELSE 1.1706 + "id" = "issue_id_p" 1.1707 + END 1.1708 LOOP 1.1709 - UPDATE "direct_interest_snapshot" SET 1.1710 - "weight" = 1 + 1.1711 - "weight_of_added_delegations_for_interest_snapshot"( 1.1712 - "issue_id_p", 1.1713 - "member_id_v", 1.1714 - '{}' 1.1715 - ) 1.1716 - WHERE "issue_id" = "issue_id_p" 1.1717 - AND "event" = 'periodic' 1.1718 - AND "member_id" = "member_id_v"; 1.1719 + INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id") 1.1720 + VALUES ("snapshot_id_v", "issue_id_v"); 1.1721 + INSERT INTO "direct_interest_snapshot" 1.1722 + ("snapshot_id", "issue_id", "member_id") 1.1723 + SELECT 1.1724 + "snapshot_id_v" AS "snapshot_id", 1.1725 + "issue_id_v" AS "issue_id", 1.1726 + "member"."id" AS "member_id" 1.1727 + FROM "issue" 1.1728 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.1729 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1730 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.1731 + JOIN "privilege" 1.1732 + ON "privilege"."unit_id" = "area"."unit_id" 1.1733 + AND "privilege"."member_id" = "member"."id" 1.1734 + WHERE "issue"."id" = "issue_id_v" 1.1735 + AND "member"."active" AND "privilege"."voting_right"; 1.1736 + FOR "member_id_v" IN 1.1737 + SELECT "member_id" FROM "direct_interest_snapshot" 1.1738 + WHERE "snapshot_id" = "snapshot_id_v" 1.1739 + AND "issue_id" = "issue_id_v" 1.1740 + LOOP 1.1741 + UPDATE "direct_interest_snapshot" SET 1.1742 + "weight" = 1 + 1.1743 + "weight_of_added_delegations_for_snapshot"( 1.1744 + "snapshot_id_v", 1.1745 + "issue_id_v", 1.1746 + "member_id_v", 1.1747 + '{}' 1.1748 + ) 1.1749 + WHERE "snapshot_id" = "snapshot_id_v" 1.1750 + AND "issue_id" = "issue_id_v" 1.1751 + AND "member_id" = "member_id_v"; 1.1752 + END LOOP; 1.1753 + INSERT INTO "direct_supporter_snapshot" 1.1754 + ( "snapshot_id", "issue_id", "initiative_id", "member_id", 1.1755 + "draft_id", "informed", "satisfied" ) 1.1756 + SELECT 1.1757 + "snapshot_id_v" AS "snapshot_id", 1.1758 + "issue_id_v" AS "issue_id", 1.1759 + "initiative"."id" AS "initiative_id", 1.1760 + "supporter"."member_id" AS "member_id", 1.1761 + "supporter"."draft_id" AS "draft_id", 1.1762 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.1763 + NOT EXISTS ( 1.1764 + SELECT NULL FROM "critical_opinion" 1.1765 + WHERE "initiative_id" = "initiative"."id" 1.1766 + AND "member_id" = "supporter"."member_id" 1.1767 + ) AS "satisfied" 1.1768 + FROM "initiative" 1.1769 + JOIN "supporter" 1.1770 + ON "supporter"."initiative_id" = "initiative"."id" 1.1771 + JOIN "current_draft" 1.1772 + ON "initiative"."id" = "current_draft"."initiative_id" 1.1773 + JOIN "direct_interest_snapshot" 1.1774 + ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id" 1.1775 + AND "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.1776 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.1777 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.1778 + DELETE FROM "temporary_suggestion_counts"; 1.1779 + INSERT INTO "temporary_suggestion_counts" 1.1780 + ( "id", 1.1781 + "minus2_unfulfilled_count", "minus2_fulfilled_count", 1.1782 + "minus1_unfulfilled_count", "minus1_fulfilled_count", 1.1783 + "plus1_unfulfilled_count", "plus1_fulfilled_count", 1.1784 + "plus2_unfulfilled_count", "plus2_fulfilled_count" ) 1.1785 + SELECT 1.1786 + "suggestion"."id", 1.1787 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1788 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1789 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1790 + AND "di"."issue_id" = "issue_id_v" 1.1791 + AND "di"."member_id" = "opinion"."member_id" 1.1792 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1793 + AND "opinion"."degree" = -2 1.1794 + AND "opinion"."fulfilled" = FALSE 1.1795 + ) AS "minus2_unfulfilled_count", 1.1796 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1797 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1798 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1799 + AND "di"."issue_id" = "issue_id_v" 1.1800 + AND "di"."member_id" = "opinion"."member_id" 1.1801 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1802 + AND "opinion"."degree" = -2 1.1803 + AND "opinion"."fulfilled" = TRUE 1.1804 + ) AS "minus2_fulfilled_count", 1.1805 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1806 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1807 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1808 + AND "di"."issue_id" = "issue_id_v" 1.1809 + AND "di"."member_id" = "opinion"."member_id" 1.1810 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1811 + AND "opinion"."degree" = -1 1.1812 + AND "opinion"."fulfilled" = FALSE 1.1813 + ) AS "minus1_unfulfilled_count", 1.1814 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1815 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1816 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1817 + AND "di"."issue_id" = "issue_id_v" 1.1818 + AND "di"."member_id" = "opinion"."member_id" 1.1819 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1820 + AND "opinion"."degree" = -1 1.1821 + AND "opinion"."fulfilled" = TRUE 1.1822 + ) AS "minus1_fulfilled_count", 1.1823 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1824 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1825 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1826 + AND "di"."issue_id" = "issue_id_v" 1.1827 + AND "di"."member_id" = "opinion"."member_id" 1.1828 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1829 + AND "opinion"."degree" = 1 1.1830 + AND "opinion"."fulfilled" = FALSE 1.1831 + ) AS "plus1_unfulfilled_count", 1.1832 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1833 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1834 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1835 + AND "di"."issue_id" = "issue_id_v" 1.1836 + AND "di"."member_id" = "opinion"."member_id" 1.1837 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1838 + AND "opinion"."degree" = 1 1.1839 + AND "opinion"."fulfilled" = TRUE 1.1840 + ) AS "plus1_fulfilled_count", 1.1841 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1842 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1843 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1844 + AND "di"."issue_id" = "issue_id_v" 1.1845 + AND "di"."member_id" = "opinion"."member_id" 1.1846 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1847 + AND "opinion"."degree" = 2 1.1848 + AND "opinion"."fulfilled" = FALSE 1.1849 + ) AS "plus2_unfulfilled_count", 1.1850 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1851 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1852 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1853 + AND "di"."issue_id" = "issue_id_v" 1.1854 + AND "di"."member_id" = "opinion"."member_id" 1.1855 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1856 + AND "opinion"."degree" = 2 1.1857 + AND "opinion"."fulfilled" = TRUE 1.1858 + ) AS "plus2_fulfilled_count" 1.1859 + FROM "suggestion" JOIN "initiative" 1.1860 + ON "suggestion"."initiative_id" = "initiative"."id" 1.1861 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.1862 END LOOP; 1.1863 - INSERT INTO "direct_supporter_snapshot" 1.1864 - ( "issue_id", "initiative_id", "event", "member_id", 1.1865 - "draft_id", "informed", "satisfied" ) 1.1866 - SELECT 1.1867 - "issue_id_p" AS "issue_id", 1.1868 - "initiative"."id" AS "initiative_id", 1.1869 - 'periodic' AS "event", 1.1870 - "supporter"."member_id" AS "member_id", 1.1871 - "supporter"."draft_id" AS "draft_id", 1.1872 - "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.1873 - NOT EXISTS ( 1.1874 - SELECT NULL FROM "critical_opinion" 1.1875 - WHERE "initiative_id" = "initiative"."id" 1.1876 - AND "member_id" = "supporter"."member_id" 1.1877 - ) AS "satisfied" 1.1878 - FROM "initiative" 1.1879 - JOIN "supporter" 1.1880 - ON "supporter"."initiative_id" = "initiative"."id" 1.1881 - JOIN "current_draft" 1.1882 - ON "initiative"."id" = "current_draft"."initiative_id" 1.1883 - JOIN "direct_interest_snapshot" 1.1884 - ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.1885 - AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.1886 - AND "event" = 'periodic' 1.1887 - WHERE "initiative"."issue_id" = "issue_id_p"; 1.1888 - RETURN; 1.1889 + RETURN "snapshot_id_v"; 1.1890 END; 1.1891 $$; 1.1892 1.1893 -COMMENT ON FUNCTION "create_interest_snapshot" 1.1894 +COMMENT ON FUNCTION "take_snapshot" 1.1895 ( "issue"."id"%TYPE ) 1.1896 - IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.'; 1.1897 - 1.1898 - 1.1899 -CREATE FUNCTION "create_snapshot" 1.1900 + 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.'; 1.1901 + 1.1902 + 1.1903 +CREATE FUNCTION "finish_snapshot" 1.1904 ( "issue_id_p" "issue"."id"%TYPE ) 1.1905 RETURNS VOID 1.1906 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1907 DECLARE 1.1908 - "initiative_id_v" "initiative"."id"%TYPE; 1.1909 - "suggestion_id_v" "suggestion"."id"%TYPE; 1.1910 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.1911 BEGIN 1.1912 - PERFORM "require_transaction_isolation"(); 1.1913 - PERFORM "create_population_snapshot"("issue_id_p"); 1.1914 - PERFORM "create_interest_snapshot"("issue_id_p"); 1.1915 + LOCK TABLE "snapshot" IN EXCLUSIVE MODE; 1.1916 + SELECT "id" INTO "snapshot_id_v" FROM "snapshot" 1.1917 + ORDER BY "id" DESC LIMIT 1; 1.1918 UPDATE "issue" SET 1.1919 - "snapshot" = coalesce("phase_finished", now()), 1.1920 - "latest_snapshot_event" = 'periodic', 1.1921 + "latest_snapshot_id" = "snapshot_id_v", 1.1922 "population" = ( 1.1923 SELECT coalesce(sum("weight"), 0) 1.1924 - FROM "direct_population_snapshot" 1.1925 - WHERE "issue_id" = "issue_id_p" 1.1926 - AND "event" = 'periodic' 1.1927 + FROM "direct_interest_snapshot" 1.1928 + WHERE "snapshot_id" = "snapshot_id_v" 1.1929 + AND "issue_id" = "issue_id_p" 1.1930 ) 1.1931 WHERE "id" = "issue_id_p"; 1.1932 - FOR "initiative_id_v" IN 1.1933 - SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.1934 - LOOP 1.1935 - UPDATE "initiative" SET 1.1936 - "supporter_count" = ( 1.1937 - SELECT coalesce(sum("di"."weight"), 0) 1.1938 - FROM "direct_interest_snapshot" AS "di" 1.1939 - JOIN "direct_supporter_snapshot" AS "ds" 1.1940 - ON "di"."member_id" = "ds"."member_id" 1.1941 - WHERE "di"."issue_id" = "issue_id_p" 1.1942 - AND "di"."event" = 'periodic' 1.1943 - AND "ds"."initiative_id" = "initiative_id_v" 1.1944 - AND "ds"."event" = 'periodic' 1.1945 - ), 1.1946 - "informed_supporter_count" = ( 1.1947 - SELECT coalesce(sum("di"."weight"), 0) 1.1948 - FROM "direct_interest_snapshot" AS "di" 1.1949 - JOIN "direct_supporter_snapshot" AS "ds" 1.1950 - ON "di"."member_id" = "ds"."member_id" 1.1951 - WHERE "di"."issue_id" = "issue_id_p" 1.1952 - AND "di"."event" = 'periodic' 1.1953 - AND "ds"."initiative_id" = "initiative_id_v" 1.1954 - AND "ds"."event" = 'periodic' 1.1955 - AND "ds"."informed" 1.1956 - ), 1.1957 - "satisfied_supporter_count" = ( 1.1958 - SELECT coalesce(sum("di"."weight"), 0) 1.1959 - FROM "direct_interest_snapshot" AS "di" 1.1960 - JOIN "direct_supporter_snapshot" AS "ds" 1.1961 - ON "di"."member_id" = "ds"."member_id" 1.1962 - WHERE "di"."issue_id" = "issue_id_p" 1.1963 - AND "di"."event" = 'periodic' 1.1964 - AND "ds"."initiative_id" = "initiative_id_v" 1.1965 - AND "ds"."event" = 'periodic' 1.1966 - AND "ds"."satisfied" 1.1967 - ), 1.1968 - "satisfied_informed_supporter_count" = ( 1.1969 - SELECT coalesce(sum("di"."weight"), 0) 1.1970 - FROM "direct_interest_snapshot" AS "di" 1.1971 - JOIN "direct_supporter_snapshot" AS "ds" 1.1972 - ON "di"."member_id" = "ds"."member_id" 1.1973 - WHERE "di"."issue_id" = "issue_id_p" 1.1974 - AND "di"."event" = 'periodic' 1.1975 - AND "ds"."initiative_id" = "initiative_id_v" 1.1976 - AND "ds"."event" = 'periodic' 1.1977 - AND "ds"."informed" 1.1978 - AND "ds"."satisfied" 1.1979 - ) 1.1980 - WHERE "id" = "initiative_id_v"; 1.1981 - FOR "suggestion_id_v" IN 1.1982 - SELECT "id" FROM "suggestion" 1.1983 - WHERE "initiative_id" = "initiative_id_v" 1.1984 - LOOP 1.1985 - UPDATE "suggestion" SET 1.1986 - "minus2_unfulfilled_count" = ( 1.1987 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.1988 - FROM "issue" CROSS JOIN "opinion" 1.1989 - JOIN "direct_interest_snapshot" AS "snapshot" 1.1990 - ON "snapshot"."issue_id" = "issue"."id" 1.1991 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.1992 - AND "snapshot"."member_id" = "opinion"."member_id" 1.1993 - WHERE "issue"."id" = "issue_id_p" 1.1994 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.1995 - AND "opinion"."degree" = -2 1.1996 - AND "opinion"."fulfilled" = FALSE 1.1997 - ), 1.1998 - "minus2_fulfilled_count" = ( 1.1999 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2000 - FROM "issue" CROSS JOIN "opinion" 1.2001 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2002 - ON "snapshot"."issue_id" = "issue"."id" 1.2003 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2004 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2005 - WHERE "issue"."id" = "issue_id_p" 1.2006 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2007 - AND "opinion"."degree" = -2 1.2008 - AND "opinion"."fulfilled" = TRUE 1.2009 - ), 1.2010 - "minus1_unfulfilled_count" = ( 1.2011 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2012 - FROM "issue" CROSS JOIN "opinion" 1.2013 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2014 - ON "snapshot"."issue_id" = "issue"."id" 1.2015 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2016 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2017 - WHERE "issue"."id" = "issue_id_p" 1.2018 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2019 - AND "opinion"."degree" = -1 1.2020 - AND "opinion"."fulfilled" = FALSE 1.2021 - ), 1.2022 - "minus1_fulfilled_count" = ( 1.2023 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2024 - FROM "issue" CROSS JOIN "opinion" 1.2025 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2026 - ON "snapshot"."issue_id" = "issue"."id" 1.2027 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2028 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2029 - WHERE "issue"."id" = "issue_id_p" 1.2030 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2031 - AND "opinion"."degree" = -1 1.2032 - AND "opinion"."fulfilled" = TRUE 1.2033 - ), 1.2034 - "plus1_unfulfilled_count" = ( 1.2035 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2036 - FROM "issue" CROSS JOIN "opinion" 1.2037 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2038 - ON "snapshot"."issue_id" = "issue"."id" 1.2039 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2040 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2041 - WHERE "issue"."id" = "issue_id_p" 1.2042 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2043 - AND "opinion"."degree" = 1 1.2044 - AND "opinion"."fulfilled" = FALSE 1.2045 - ), 1.2046 - "plus1_fulfilled_count" = ( 1.2047 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2048 - FROM "issue" CROSS JOIN "opinion" 1.2049 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2050 - ON "snapshot"."issue_id" = "issue"."id" 1.2051 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2052 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2053 - WHERE "issue"."id" = "issue_id_p" 1.2054 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2055 - AND "opinion"."degree" = 1 1.2056 - AND "opinion"."fulfilled" = TRUE 1.2057 - ), 1.2058 - "plus2_unfulfilled_count" = ( 1.2059 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2060 - FROM "issue" CROSS JOIN "opinion" 1.2061 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2062 - ON "snapshot"."issue_id" = "issue"."id" 1.2063 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2064 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2065 - WHERE "issue"."id" = "issue_id_p" 1.2066 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2067 - AND "opinion"."degree" = 2 1.2068 - AND "opinion"."fulfilled" = FALSE 1.2069 - ), 1.2070 - "plus2_fulfilled_count" = ( 1.2071 - SELECT coalesce(sum("snapshot"."weight"), 0) 1.2072 - FROM "issue" CROSS JOIN "opinion" 1.2073 - JOIN "direct_interest_snapshot" AS "snapshot" 1.2074 - ON "snapshot"."issue_id" = "issue"."id" 1.2075 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.2076 - AND "snapshot"."member_id" = "opinion"."member_id" 1.2077 - WHERE "issue"."id" = "issue_id_p" 1.2078 - AND "opinion"."suggestion_id" = "suggestion_id_v" 1.2079 - AND "opinion"."degree" = 2 1.2080 - AND "opinion"."fulfilled" = TRUE 1.2081 - ) 1.2082 - WHERE "suggestion"."id" = "suggestion_id_v"; 1.2083 - END LOOP; 1.2084 - END LOOP; 1.2085 + UPDATE "initiative" SET 1.2086 + "supporter_count" = ( 1.2087 + SELECT coalesce(sum("di"."weight"), 0) 1.2088 + FROM "direct_interest_snapshot" AS "di" 1.2089 + JOIN "direct_supporter_snapshot" AS "ds" 1.2090 + ON "di"."member_id" = "ds"."member_id" 1.2091 + WHERE "di"."snapshot_id" = "snapshot_id_v" 1.2092 + AND "di"."issue_id" = "issue_id_p" 1.2093 + AND "ds"."snapshot_id" = "snapshot_id_v" 1.2094 + AND "ds"."initiative_id" = "initiative"."id" 1.2095 + ), 1.2096 + "informed_supporter_count" = ( 1.2097 + SELECT coalesce(sum("di"."weight"), 0) 1.2098 + FROM "direct_interest_snapshot" AS "di" 1.2099 + JOIN "direct_supporter_snapshot" AS "ds" 1.2100 + ON "di"."member_id" = "ds"."member_id" 1.2101 + WHERE "di"."snapshot_id" = "snapshot_id_v" 1.2102 + AND "di"."issue_id" = "issue_id_p" 1.2103 + AND "ds"."snapshot_id" = "snapshot_id_v" 1.2104 + AND "ds"."initiative_id" = "initiative"."id" 1.2105 + AND "ds"."informed" 1.2106 + ), 1.2107 + "satisfied_supporter_count" = ( 1.2108 + SELECT coalesce(sum("di"."weight"), 0) 1.2109 + FROM "direct_interest_snapshot" AS "di" 1.2110 + JOIN "direct_supporter_snapshot" AS "ds" 1.2111 + ON "di"."member_id" = "ds"."member_id" 1.2112 + WHERE "di"."snapshot_id" = "snapshot_id_v" 1.2113 + AND "di"."issue_id" = "issue_id_p" 1.2114 + AND "ds"."snapshot_id" = "snapshot_id_v" 1.2115 + AND "ds"."initiative_id" = "initiative"."id" 1.2116 + AND "ds"."satisfied" 1.2117 + ), 1.2118 + "satisfied_informed_supporter_count" = ( 1.2119 + SELECT coalesce(sum("di"."weight"), 0) 1.2120 + FROM "direct_interest_snapshot" AS "di" 1.2121 + JOIN "direct_supporter_snapshot" AS "ds" 1.2122 + ON "di"."member_id" = "ds"."member_id" 1.2123 + WHERE "di"."snapshot_id" = "snapshot_id_v" 1.2124 + AND "di"."issue_id" = "issue_id_p" 1.2125 + AND "ds"."snapshot_id" = "snapshot_id_v" 1.2126 + AND "ds"."initiative_id" = "initiative"."id" 1.2127 + AND "ds"."informed" 1.2128 + AND "ds"."satisfied" 1.2129 + ) 1.2130 + WHERE "issue_id" = "issue_id_p"; 1.2131 + UPDATE "suggestion" SET 1.2132 + "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count", 1.2133 + "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count", 1.2134 + "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count", 1.2135 + "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count", 1.2136 + "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count", 1.2137 + "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count", 1.2138 + "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count", 1.2139 + "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count" 1.2140 + FROM "temporary_suggestion_counts" AS "temp", "initiative" 1.2141 + WHERE "temp"."id" = "suggestion"."id" 1.2142 + AND "initiative"."issue_id" = "issue_id_p" 1.2143 + AND "suggestion"."initiative_id" = "initiative"."id"; 1.2144 + DELETE FROM "temporary_suggestion_counts"; 1.2145 RETURN; 1.2146 END; 1.2147 $$; 1.2148 1.2149 -COMMENT ON FUNCTION "create_snapshot" 1.2150 +COMMENT ON FUNCTION "finish_snapshot" 1.2151 ( "issue"."id"%TYPE ) 1.2152 - IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.'; 1.2153 - 1.2154 - 1.2155 -CREATE FUNCTION "set_snapshot_event" 1.2156 - ( "issue_id_p" "issue"."id"%TYPE, 1.2157 - "event_p" "snapshot_event" ) 1.2158 - RETURNS VOID 1.2159 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2160 - DECLARE 1.2161 - "event_v" "issue"."latest_snapshot_event"%TYPE; 1.2162 - BEGIN 1.2163 - PERFORM "require_transaction_isolation"(); 1.2164 - SELECT "latest_snapshot_event" INTO "event_v" FROM "issue" 1.2165 - WHERE "id" = "issue_id_p" FOR UPDATE; 1.2166 - UPDATE "issue" SET "latest_snapshot_event" = "event_p" 1.2167 - WHERE "id" = "issue_id_p"; 1.2168 - UPDATE "direct_population_snapshot" SET "event" = "event_p" 1.2169 - WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.2170 - UPDATE "delegating_population_snapshot" SET "event" = "event_p" 1.2171 - WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.2172 - UPDATE "direct_interest_snapshot" SET "event" = "event_p" 1.2173 - WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.2174 - UPDATE "delegating_interest_snapshot" SET "event" = "event_p" 1.2175 - WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.2176 - UPDATE "direct_supporter_snapshot" SET "event" = "event_p" 1.2177 - FROM "initiative" -- NOTE: due to missing index on issue_id 1.2178 - WHERE "initiative"."issue_id" = "issue_id_p" 1.2179 - AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.2180 - AND "direct_supporter_snapshot"."event" = "event_v"; 1.2181 - RETURN; 1.2182 - END; 1.2183 - $$; 1.2184 - 1.2185 -COMMENT ON FUNCTION "set_snapshot_event" 1.2186 - ( "issue"."id"%TYPE, 1.2187 - "snapshot_event" ) 1.2188 - IS 'Change "event" attribute of the previous ''periodic'' snapshot'; 1.2189 + 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)'; 1.2190 1.2191 1.2192 1.2193 @@ -4884,6 +5323,29 @@ 1.2194 ----------------------------- 1.2195 1.2196 1.2197 +CREATE FUNCTION "issue_admission"() 1.2198 + RETURNS BOOLEAN 1.2199 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2200 + DECLARE 1.2201 + "issue_id_v" "issue"."id"%TYPE; 1.2202 + BEGIN 1.2203 + PERFORM "dont_require_transaction_isolation"(); 1.2204 + LOCK TABLE "snapshot" IN EXCLUSIVE MODE; 1.2205 + SELECT "id" INTO "issue_id_v" FROM "issue_for_admission" LIMIT 1; 1.2206 + IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF; 1.2207 + UPDATE "issue" SET 1.2208 + "admission_snapshot_id" = "latest_snapshot_id", 1.2209 + "state" = 'discussion', 1.2210 + "accepted" = now(), 1.2211 + "phase_finished" = NULL 1.2212 + WHERE "id" = "issue_id_v"; 1.2213 + RETURN TRUE; 1.2214 + END; 1.2215 + $$; 1.2216 + 1.2217 +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'; 1.2218 + 1.2219 + 1.2220 CREATE TYPE "check_issue_persistence" AS ( 1.2221 "state" "issue_state", 1.2222 "phase_finished" BOOLEAN, 1.2223 @@ -4901,21 +5363,26 @@ 1.2224 RETURNS "check_issue_persistence" 1.2225 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2226 DECLARE 1.2227 - "issue_row" "issue"%ROWTYPE; 1.2228 - "policy_row" "policy"%ROWTYPE; 1.2229 - "initiative_row" "initiative"%ROWTYPE; 1.2230 - "state_v" "issue_state"; 1.2231 + "issue_row" "issue"%ROWTYPE; 1.2232 + "last_calculated_v" "snapshot"."calculated"%TYPE; 1.2233 + "policy_row" "policy"%ROWTYPE; 1.2234 + "initiative_row" "initiative"%ROWTYPE; 1.2235 + "state_v" "issue_state"; 1.2236 BEGIN 1.2237 PERFORM "require_transaction_isolation"(); 1.2238 IF "persist" ISNULL THEN 1.2239 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.2240 FOR UPDATE; 1.2241 + SELECT "calculated" INTO "last_calculated_v" 1.2242 + FROM "snapshot" JOIN "snapshot_issue" 1.2243 + ON "snapshot"."id" = "snapshot_issue"."snapshot_id" 1.2244 + WHERE "snapshot_issue"."issue_id" = "issue_id_p"; 1.2245 IF "issue_row"."closed" NOTNULL THEN 1.2246 RETURN NULL; 1.2247 END IF; 1.2248 "persist"."state" := "issue_row"."state"; 1.2249 IF 1.2250 - ( "issue_row"."state" = 'admission' AND now() >= 1.2251 + ( "issue_row"."state" = 'admission' AND "last_calculated_v" >= 1.2252 "issue_row"."created" + "issue_row"."max_admission_time" ) OR 1.2253 ( "issue_row"."state" = 'discussion' AND now() >= 1.2254 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR 1.2255 @@ -4967,15 +5434,18 @@ 1.2256 "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.2257 coalesce("persist"."snapshot_created", FALSE) = FALSE 1.2258 THEN 1.2259 - PERFORM "create_snapshot"("issue_id_p"); 1.2260 + IF "persist"."state" != 'admission' THEN 1.2261 + PERFORM "take_snapshot"("issue_id_p"); 1.2262 + PERFORM "finish_snapshot"("issue_id_p"); 1.2263 + END IF; 1.2264 "persist"."snapshot_created" = TRUE; 1.2265 IF "persist"."phase_finished" THEN 1.2266 IF "persist"."state" = 'admission' THEN 1.2267 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.2268 + UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"; 1.2269 ELSIF "persist"."state" = 'discussion' THEN 1.2270 - PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.2271 + UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"; 1.2272 ELSIF "persist"."state" = 'verification' THEN 1.2273 - PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 1.2274 + UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"; 1.2275 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.2276 SELECT * INTO "policy_row" FROM "policy" 1.2277 WHERE "id" = "issue_row"."policy_id"; 1.2278 @@ -5037,23 +5507,7 @@ 1.2279 IF "persist"."state" = 'admission' THEN 1.2280 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.2281 FOR UPDATE; 1.2282 - SELECT * INTO "policy_row" 1.2283 - FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.2284 - IF 1.2285 - ( now() >= 1.2286 - "issue_row"."created" + "issue_row"."min_admission_time" ) AND 1.2287 - -- TODO: implement new mechanism for issue admission 1.2288 - EXISTS ( 1.2289 - SELECT NULL FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.2290 - AND "supporter_count" >= "policy_row"."issue_quorum" 1.2291 - ) 1.2292 - THEN 1.2293 - UPDATE "issue" SET 1.2294 - "state" = 'discussion', 1.2295 - "accepted" = coalesce("phase_finished", now()), 1.2296 - "phase_finished" = NULL 1.2297 - WHERE "id" = "issue_id_p"; 1.2298 - ELSIF "issue_row"."phase_finished" NOTNULL THEN 1.2299 + IF "issue_row"."phase_finished" NOTNULL THEN 1.2300 UPDATE "issue" SET 1.2301 "state" = 'canceled_issue_not_accepted', 1.2302 "closed" = "phase_finished", 1.2303 @@ -5125,13 +5579,20 @@ 1.2304 RETURNS VOID 1.2305 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2306 DECLARE 1.2307 - "issue_id_v" "issue"."id"%TYPE; 1.2308 - "persist_v" "check_issue_persistence"; 1.2309 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.2310 + "issue_id_v" "issue"."id"%TYPE; 1.2311 + "persist_v" "check_issue_persistence"; 1.2312 BEGIN 1.2313 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 1.2314 DELETE FROM "expired_session"; 1.2315 PERFORM "check_activity"(); 1.2316 PERFORM "calculate_member_counts"(); 1.2317 + SELECT "take_snapshot"(NULL) INTO "snapshot_id_v"; 1.2318 + PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue" 1.2319 + WHERE "snapshot_id" = "snapshot_id_v"; 1.2320 + LOOP 1.2321 + EXIT WHEN "issue_admission"() = FALSE; 1.2322 + END LOOP; 1.2323 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.2324 "persist_v" := NULL; 1.2325 LOOP 1.2326 @@ -5143,7 +5604,7 @@ 1.2327 END; 1.2328 $$; 1.2329 1.2330 -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.'; 1.2331 +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.'; 1.2332 1.2333 1.2334
2.1 --- a/test.sql Fri May 27 09:48:34 2016 +0200 2.2 +++ b/test.sql Sun May 29 20:42:40 2016 +0200 2.3 @@ -51,7 +51,7 @@ 2.4 'Default policy', 2.5 '0', '1 hour', '1 hour', '1 hour', '1 hour', 2.6 3, 2.7 - 20, 100, 2.8 + 30, 100, 2.9 1, 2, TRUE, 2.10 TRUE, FALSE ); 2.11 2.12 @@ -59,7 +59,6 @@ 2.13 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.14 BEGIN 2.15 UPDATE "issue" SET 2.16 - "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL, 2.17 "created" = "created" - '1 hour 1 minute'::INTERVAL, 2.18 "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL, 2.19 "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL, 2.20 @@ -71,6 +70,11 @@ 2.21 2.22 INSERT INTO "unit" ("name") VALUES ('Main'); 2.23 2.24 +INSERT INTO "admission_rule" ("unit_id", "name") VALUES (1, 'General admission rule'); 2.25 + 2.26 +INSERT INTO "admission_rule_condition" ("admission_rule_id", "unit_id", "holdoff_time") 2.27 + VALUES (1, 1, '0 seconds'); 2.28 + 2.29 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") 2.30 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" 2.31 FROM "member"; 2.32 @@ -84,24 +88,6 @@ 2.33 INSERT INTO "allowed_policy" ("area_id", "policy_id", "default_policy") 2.34 VALUES (1, 1, TRUE), (2, 1, TRUE), (3, 1, TRUE), (4, 1, TRUE); 2.35 2.36 -INSERT INTO "membership" ("area_id", "member_id") VALUES 2.37 - (1, 9), 2.38 - (1, 19), 2.39 - (2, 9), 2.40 - (2, 10), 2.41 - (2, 17), 2.42 - (3, 9), 2.43 - (3, 11), 2.44 - (3, 12), 2.45 - (3, 14), 2.46 - (3, 20), 2.47 - (3, 21), 2.48 - (3, 22), 2.49 - (4, 6), 2.50 - (4, 9), 2.51 - (4, 13), 2.52 - (4, 22); 2.53 - 2.54 -- global delegations 2.55 INSERT INTO "delegation" 2.56 ("truster_id", "scope", "unit_id", "trustee_id") VALUES