liquid_feedback_core

changeset 622:5b7c4082d7b3 v4.2.2

Version 4.2.2 with geoindices uninstalled by default
author jbe
date Fri Feb 05 12:53:00 2021 +0100 (2021-02-05)
parents 9e4e39136136
children 8dc9b96cc5db
files LICENSE README core.sql geoindex_install.sql geoindex_uninstall.sql update/core-update.v4.2.1-v4.2.2.sql
line diff
     1.1 --- a/LICENSE	Sat Dec 05 18:56:20 2020 +0100
     1.2 +++ b/LICENSE	Fri Feb 05 12:53:00 2021 +0100
     1.3 @@ -1,4 +1,4 @@
     1.4 -Copyright (c) 2009-2020 Public Software Group e. V., Berlin, Germany
     1.5 +Copyright (c) 2009-2021 Public Software Group e. V., Berlin, Germany
     1.6  
     1.7  Permission is hereby granted, free of charge, to any person obtaining a
     1.8  copy of this software and associated documentation files (the "Software"),
     2.1 --- a/README	Sat Dec 05 18:56:20 2020 +0100
     2.2 +++ b/README	Fri Feb 05 12:53:00 2021 +0100
     2.3 @@ -1,12 +1,15 @@
     2.4 -
     2.5 -LiquidFeedback Core requires the pgLatLon extension to be installed.
     2.6 -This extension may be obtained from:
     2.7 -http://www.public-software-group.org/pgLatLon
     2.8  
     2.9  Setup the database:
    2.10  $ createdb liquid_feedback
    2.11  $ psql -v ON_ERROR_STOP=1 -f core.sql liquid_feedback
    2.12  
    2.13 +Optionally add geospatial indices (if desired):
    2.14 +$ psql -v ON_ERROR_STOP=1 -f geoindex_install.sql liquid_feedback
    2.15 +
    2.16 +Note: Geospatial indices require the pgLatLon extension to be
    2.17 +installed, which may be obtained from:
    2.18 +http://www.public-software-group.org/pgLatLon
    2.19 +
    2.20  Optionally insert demo data:
    2.21  $ psql -v ON_ERROR_STOP=1 -f demo.sql liquid_feedback
    2.22  
     3.1 --- a/core.sql	Sat Dec 05 18:56:20 2020 +0100
     3.2 +++ b/core.sql	Fri Feb 05 12:53:00 2021 +0100
     3.3 @@ -3,10 +3,8 @@
     3.4  
     3.5  BEGIN;
     3.6  
     3.7 -CREATE EXTENSION IF NOT EXISTS latlon;
     3.8 -
     3.9  CREATE VIEW "liquid_feedback_version" AS
    3.10 -  SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
    3.11 +  SELECT * FROM (VALUES ('4.2.2', 4, 2, 2))
    3.12    AS "subquery"("string", "major", "minor", "revision");
    3.13  
    3.14  
    3.15 @@ -125,7 +123,6 @@
    3.16            CHECK ("activated" ISNULL OR "name" NOTNULL) );
    3.17  CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
    3.18  CREATE INDEX "member_active_idx" ON "member" ("active");
    3.19 -CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
    3.20  
    3.21  COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    3.22  
    3.23 @@ -640,7 +637,6 @@
    3.24  CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    3.25  CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    3.26  CREATE INDEX "unit_active_idx" ON "unit" ("active");
    3.27 -CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
    3.28  
    3.29  COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    3.30  
    3.31 @@ -679,7 +675,6 @@
    3.32          "external_reference"    TEXT,
    3.33          "location"              JSONB );
    3.34  CREATE INDEX "area_active_idx" ON "area" ("active");
    3.35 -CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));
    3.36  
    3.37  COMMENT ON TABLE "area" IS 'Subject areas';
    3.38  
    3.39 @@ -928,7 +923,6 @@
    3.40          CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
    3.41          CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
    3.42  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    3.43 -CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
    3.44  
    3.45  COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
    3.46  
    3.47 @@ -998,7 +992,6 @@
    3.48          "external_reference"    TEXT );
    3.49  CREATE INDEX "draft_created_idx" ON "draft" ("created");
    3.50  CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
    3.51 -CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
    3.52  
    3.53  COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
    3.54  
    3.55 @@ -1051,7 +1044,6 @@
    3.56          "proportional_order"    INT4 );
    3.57  CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    3.58  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    3.59 -CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
    3.60  
    3.61  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';
    3.62  
    3.63 @@ -4868,86 +4860,6 @@
    3.64  
    3.65  
    3.66  
    3.67 -------------------------
    3.68 --- Geospatial lookups --
    3.69 -------------------------
    3.70 -
    3.71 -/*
    3.72 -CREATE FUNCTION "closed_initiatives_in_bounding_box"
    3.73 -  ( "bounding_box_p" EBOX,
    3.74 -    "limit_p"        INT4 )
    3.75 -  RETURNS SETOF "initiative"
    3.76 -  LANGUAGE 'plpgsql' STABLE AS $$
    3.77 -    DECLARE
    3.78 -      "limit_v" INT4;
    3.79 -      "count_v" INT4;
    3.80 -    BEGIN
    3.81 -      "limit_v" := "limit_p" + 1;
    3.82 -      LOOP
    3.83 -        SELECT count(1) INTO "count_v"
    3.84 -          FROM "initiative"
    3.85 -          JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    3.86 -          WHERE "issue"."closed" NOTNULL
    3.87 -          AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    3.88 -          LIMIT "limit_v";
    3.89 -        IF "count_v" < "limit_v" THEN
    3.90 -          RETURN QUERY SELECT "initiative".*
    3.91 -            FROM (
    3.92 -              SELECT
    3.93 -                "initiative"."id" AS "initiative_id",
    3.94 -                "issue"."closed"
    3.95 -              FROM "initiative"
    3.96 -              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    3.97 -              WHERE "issue"."closed" NOTNULL
    3.98 -              AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    3.99 -            ) AS "subquery"
   3.100 -            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   3.101 -            ORDER BY "subquery"."closed" DESC
   3.102 -            LIMIT "limit_p";
   3.103 -          RETURN;
   3.104 -        END IF;
   3.105 -        SELECT count(1) INTO "count_v"
   3.106 -          FROM (
   3.107 -            SELECT "initiative"."id" AS "initiative_id"
   3.108 -            FROM "initiative"
   3.109 -            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   3.110 -            WHERE "issue"."closed" NOTNULL
   3.111 -            ORDER BY "closed" DESC
   3.112 -            LIMIT "limit_v"
   3.113 -          ) AS "subquery"
   3.114 -          JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   3.115 -          WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
   3.116 -          LIMIT "limit_p";
   3.117 -        IF "count_v" >= "limit_p" THEN
   3.118 -          RETURN QUERY SELECT "initiative".*
   3.119 -            FROM (
   3.120 -              SELECT
   3.121 -                "initiative"."id" AS "initiative_id",
   3.122 -                "issue"."closed"
   3.123 -              FROM "initiative"
   3.124 -              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   3.125 -              WHERE "issue"."closed" NOTNULL
   3.126 -              ORDER BY "closed" DESC
   3.127 -              LIMIT "limit_v"
   3.128 -            ) AS "subquery"
   3.129 -            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
   3.130 -            WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
   3.131 -            ORDER BY "subquery"."closed" DESC
   3.132 -            LIMIT "limit_p";
   3.133 -          RETURN;
   3.134 -        END IF;
   3.135 -        "limit_v" := "limit_v" * 2;
   3.136 -      END LOOP;
   3.137 -    END;
   3.138 -  $$;
   3.139 -
   3.140 -COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
   3.141 -  ( EBOX, INT4 )
   3.142 -  IS 'TODO';
   3.143 -*/
   3.144 -
   3.145 -
   3.146 -
   3.147  ---------------------------
   3.148  -- Transaction isolation --
   3.149  ---------------------------
     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/geoindex_install.sql	Fri Feb 05 12:53:00 2021 +0100
     4.3 @@ -0,0 +1,110 @@
     4.4 +BEGIN;
     4.5 +
     4.6 +
     4.7 +CREATE EXTENSION IF NOT EXISTS latlon;
     4.8 +
     4.9 +
    4.10 +------------------------
    4.11 +-- Geospatial indices --
    4.12 +------------------------
    4.13 +
    4.14 +CREATE INDEX IF NOT EXISTS "member_location_idx"
    4.15 +  ON "member"
    4.16 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.17 +CREATE INDEX IF NOT EXISTS "unit_location_idx"
    4.18 +  ON "unit"
    4.19 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.20 +CREATE INDEX IF NOT EXISTS "area_location_idx"
    4.21 +  ON "area"
    4.22 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.23 +CREATE INDEX IF NOT EXISTS "initiative_location_idx"
    4.24 +  ON "initiative"
    4.25 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.26 +CREATE INDEX IF NOT EXISTS "draft_location_idx"
    4.27 +  ON "draft"
    4.28 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.29 +CREATE INDEX IF NOT EXISTS "suggestion_location_idx"
    4.30 +  ON "suggestion"
    4.31 +  USING gist ((GeoJSON_to_ecluster("location")));
    4.32 +
    4.33 +
    4.34 +------------------------
    4.35 +-- Geospatial lookups --
    4.36 +------------------------
    4.37 +
    4.38 +/*
    4.39 +CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box"
    4.40 +  ( "bounding_box_p" EBOX,
    4.41 +    "limit_p"        INT4 )
    4.42 +  RETURNS SETOF "initiative"
    4.43 +  LANGUAGE 'plpgsql' STABLE AS $$
    4.44 +    DECLARE
    4.45 +      "limit_v" INT4;
    4.46 +      "count_v" INT4;
    4.47 +    BEGIN
    4.48 +      "limit_v" := "limit_p" + 1;
    4.49 +      LOOP
    4.50 +        SELECT count(1) INTO "count_v"
    4.51 +          FROM "initiative"
    4.52 +          JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    4.53 +          WHERE "issue"."closed" NOTNULL
    4.54 +          AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    4.55 +          LIMIT "limit_v";
    4.56 +        IF "count_v" < "limit_v" THEN
    4.57 +          RETURN QUERY SELECT "initiative".*
    4.58 +            FROM (
    4.59 +              SELECT
    4.60 +                "initiative"."id" AS "initiative_id",
    4.61 +                "issue"."closed"
    4.62 +              FROM "initiative"
    4.63 +              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    4.64 +              WHERE "issue"."closed" NOTNULL
    4.65 +              AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    4.66 +            ) AS "subquery"
    4.67 +            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    4.68 +            ORDER BY "subquery"."closed" DESC
    4.69 +            LIMIT "limit_p";
    4.70 +          RETURN;
    4.71 +        END IF;
    4.72 +        SELECT count(1) INTO "count_v"
    4.73 +          FROM (
    4.74 +            SELECT "initiative"."id" AS "initiative_id"
    4.75 +            FROM "initiative"
    4.76 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    4.77 +            WHERE "issue"."closed" NOTNULL
    4.78 +            ORDER BY "closed" DESC
    4.79 +            LIMIT "limit_v"
    4.80 +          ) AS "subquery"
    4.81 +          JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    4.82 +          WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    4.83 +          LIMIT "limit_p";
    4.84 +        IF "count_v" >= "limit_p" THEN
    4.85 +          RETURN QUERY SELECT "initiative".*
    4.86 +            FROM (
    4.87 +              SELECT
    4.88 +                "initiative"."id" AS "initiative_id",
    4.89 +                "issue"."closed"
    4.90 +              FROM "initiative"
    4.91 +              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    4.92 +              WHERE "issue"."closed" NOTNULL
    4.93 +              ORDER BY "closed" DESC
    4.94 +              LIMIT "limit_v"
    4.95 +            ) AS "subquery"
    4.96 +            JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"
    4.97 +            WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p"
    4.98 +            ORDER BY "subquery"."closed" DESC
    4.99 +            LIMIT "limit_p";
   4.100 +          RETURN;
   4.101 +        END IF;
   4.102 +        "limit_v" := "limit_v" * 2;
   4.103 +      END LOOP;
   4.104 +    END;
   4.105 +  $$;
   4.106 +
   4.107 +COMMENT ON FUNCTION "closed_initiatives_in_bounding_box"
   4.108 +  ( EBOX, INT4 )
   4.109 +  IS 'TODO';
   4.110 +*/
   4.111 +
   4.112 +
   4.113 +COMMIT;
     5.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     5.2 +++ b/geoindex_uninstall.sql	Fri Feb 05 12:53:00 2021 +0100
     5.3 @@ -0,0 +1,13 @@
     5.4 +BEGIN;
     5.5 +
     5.6 +--DROP FUNCTION IF EXISTS "closed_initiatives_in_bounding_box" (EBOX, INT4);
     5.7 +DROP INDEX IF EXISTS "suggestion_location_idx";  -- since v4.2.2
     5.8 +DROP INDEX IF EXISTS "draft_location_idx";       -- since v4.2.2
     5.9 +DROP INDEX IF EXISTS "initiative_location_idx";  -- since v4.2.2
    5.10 +DROP INDEX IF EXISTS "area_location_idx";        -- since v4.2.2
    5.11 +DROP INDEX IF EXISTS "unit_location_idx";        -- since v4.2.2
    5.12 +DROP INDEX IF EXISTS "member_location_idx";      -- since v4.2.2
    5.13 +
    5.14 +DROP EXTENSION IF EXISTS latlon;  -- since v4.2.2
    5.15 +
    5.16 +COMMIT;
     6.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     6.2 +++ b/update/core-update.v4.2.1-v4.2.2.sql	Fri Feb 05 12:53:00 2021 +0100
     6.3 @@ -0,0 +1,17 @@
     6.4 +BEGIN;
     6.5 +
     6.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     6.7 +  SELECT * FROM (VALUES ('4.2.2', 4, 2, 2))
     6.8 +  AS "subquery"("string", "major", "minor", "revision");
     6.9 +
    6.10 +--DROP FUNCTION "closed_initiatives_in_bounding_box" (EBOX, INT4);
    6.11 +DROP INDEX "suggestion_location_idx";
    6.12 +DROP INDEX "draft_location_idx";
    6.13 +DROP INDEX "initiative_location_idx";
    6.14 +DROP INDEX "area_location_idx";
    6.15 +DROP INDEX "unit_location_idx";
    6.16 +DROP INDEX "member_location_idx";
    6.17 +
    6.18 +DROP EXTENSION latlon;
    6.19 +
    6.20 +COMMIT;

Impressum / About Us