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

Impressum / About Us