# HG changeset patch # User jbe # Date 1612525980 -3600 # Node ID 5b7c4082d7b30fa46cb5801f7b35240a8aec7458 # Parent 9e4e3913613622e1dd6794df2544d8c761841dc3 Version 4.2.2 with geoindices uninstalled by default diff -r 9e4e39136136 -r 5b7c4082d7b3 LICENSE --- a/LICENSE Sat Dec 05 18:56:20 2020 +0100 +++ b/LICENSE Fri Feb 05 12:53:00 2021 +0100 @@ -1,4 +1,4 @@ -Copyright (c) 2009-2020 Public Software Group e. V., Berlin, Germany +Copyright (c) 2009-2021 Public Software Group e. V., Berlin, Germany Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), diff -r 9e4e39136136 -r 5b7c4082d7b3 README --- a/README Sat Dec 05 18:56:20 2020 +0100 +++ b/README Fri Feb 05 12:53:00 2021 +0100 @@ -1,12 +1,15 @@ - -LiquidFeedback Core requires the pgLatLon extension to be installed. -This extension may be obtained from: -http://www.public-software-group.org/pgLatLon Setup the database: $ createdb liquid_feedback $ psql -v ON_ERROR_STOP=1 -f core.sql liquid_feedback +Optionally add geospatial indices (if desired): +$ psql -v ON_ERROR_STOP=1 -f geoindex_install.sql liquid_feedback + +Note: Geospatial indices require the pgLatLon extension to be +installed, which may be obtained from: +http://www.public-software-group.org/pgLatLon + Optionally insert demo data: $ psql -v ON_ERROR_STOP=1 -f demo.sql liquid_feedback diff -r 9e4e39136136 -r 5b7c4082d7b3 core.sql --- a/core.sql Sat Dec 05 18:56:20 2020 +0100 +++ b/core.sql Fri Feb 05 12:53:00 2021 +0100 @@ -3,10 +3,8 @@ BEGIN; -CREATE EXTENSION IF NOT EXISTS latlon; - CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) + SELECT * FROM (VALUES ('4.2.2', 4, 2, 2)) AS "subquery"("string", "major", "minor", "revision"); @@ -125,7 +123,6 @@ CHECK ("activated" ISNULL OR "name" NOTNULL) ); CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); CREATE INDEX "member_active_idx" ON "member" ("active"); -CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location"))); COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; @@ -640,7 +637,6 @@ CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); CREATE INDEX "unit_active_idx" ON "unit" ("active"); -CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location"))); COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; @@ -679,7 +675,6 @@ "external_reference" TEXT, "location" JSONB ); CREATE INDEX "area_active_idx" ON "area" ("active"); -CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location"))); COMMENT ON TABLE "area" IS 'Subject areas'; @@ -928,7 +923,6 @@ CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE), CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") ); CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); -CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location"))); 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.'; @@ -998,7 +992,6 @@ "external_reference" TEXT ); CREATE INDEX "draft_created_idx" ON "draft" ("created"); CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); -CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location"))); 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.'; @@ -1051,7 +1044,6 @@ "proportional_order" INT4 ); CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); -CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location"))); 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'; @@ -4868,86 +4860,6 @@ ------------------------- --- Geospatial lookups -- ------------------------- - -/* -CREATE FUNCTION "closed_initiatives_in_bounding_box" - ( "bounding_box_p" EBOX, - "limit_p" INT4 ) - RETURNS SETOF "initiative" - LANGUAGE 'plpgsql' STABLE AS $$ - DECLARE - "limit_v" INT4; - "count_v" INT4; - BEGIN - "limit_v" := "limit_p" + 1; - LOOP - SELECT count(1) INTO "count_v" - FROM "initiative" - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" - WHERE "issue"."closed" NOTNULL - AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" - LIMIT "limit_v"; - IF "count_v" < "limit_v" THEN - RETURN QUERY SELECT "initiative".* - FROM ( - SELECT - "initiative"."id" AS "initiative_id", - "issue"."closed" - FROM "initiative" - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" - WHERE "issue"."closed" NOTNULL - AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" - ) AS "subquery" - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" - ORDER BY "subquery"."closed" DESC - LIMIT "limit_p"; - RETURN; - END IF; - SELECT count(1) INTO "count_v" - FROM ( - SELECT "initiative"."id" AS "initiative_id" - FROM "initiative" - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" - WHERE "issue"."closed" NOTNULL - ORDER BY "closed" DESC - LIMIT "limit_v" - ) AS "subquery" - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" - WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" - LIMIT "limit_p"; - IF "count_v" >= "limit_p" THEN - RETURN QUERY SELECT "initiative".* - FROM ( - SELECT - "initiative"."id" AS "initiative_id", - "issue"."closed" - FROM "initiative" - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" - WHERE "issue"."closed" NOTNULL - ORDER BY "closed" DESC - LIMIT "limit_v" - ) AS "subquery" - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" - WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" - ORDER BY "subquery"."closed" DESC - LIMIT "limit_p"; - RETURN; - END IF; - "limit_v" := "limit_v" * 2; - END LOOP; - END; - $$; - -COMMENT ON FUNCTION "closed_initiatives_in_bounding_box" - ( EBOX, INT4 ) - IS 'TODO'; -*/ - - - --------------------------- -- Transaction isolation -- --------------------------- diff -r 9e4e39136136 -r 5b7c4082d7b3 geoindex_install.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geoindex_install.sql Fri Feb 05 12:53:00 2021 +0100 @@ -0,0 +1,110 @@ +BEGIN; + + +CREATE EXTENSION IF NOT EXISTS latlon; + + +------------------------ +-- Geospatial indices -- +------------------------ + +CREATE INDEX IF NOT EXISTS "member_location_idx" + ON "member" + USING gist ((GeoJSON_to_ecluster("location"))); +CREATE INDEX IF NOT EXISTS "unit_location_idx" + ON "unit" + USING gist ((GeoJSON_to_ecluster("location"))); +CREATE INDEX IF NOT EXISTS "area_location_idx" + ON "area" + USING gist ((GeoJSON_to_ecluster("location"))); +CREATE INDEX IF NOT EXISTS "initiative_location_idx" + ON "initiative" + USING gist ((GeoJSON_to_ecluster("location"))); +CREATE INDEX IF NOT EXISTS "draft_location_idx" + ON "draft" + USING gist ((GeoJSON_to_ecluster("location"))); +CREATE INDEX IF NOT EXISTS "suggestion_location_idx" + ON "suggestion" + USING gist ((GeoJSON_to_ecluster("location"))); + + +------------------------ +-- Geospatial lookups -- +------------------------ + +/* +CREATE OR REPLACE FUNCTION "closed_initiatives_in_bounding_box" + ( "bounding_box_p" EBOX, + "limit_p" INT4 ) + RETURNS SETOF "initiative" + LANGUAGE 'plpgsql' STABLE AS $$ + DECLARE + "limit_v" INT4; + "count_v" INT4; + BEGIN + "limit_v" := "limit_p" + 1; + LOOP + SELECT count(1) INTO "count_v" + FROM "initiative" + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" + WHERE "issue"."closed" NOTNULL + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" + LIMIT "limit_v"; + IF "count_v" < "limit_v" THEN + RETURN QUERY SELECT "initiative".* + FROM ( + SELECT + "initiative"."id" AS "initiative_id", + "issue"."closed" + FROM "initiative" + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" + WHERE "issue"."closed" NOTNULL + AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" + ) AS "subquery" + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" + ORDER BY "subquery"."closed" DESC + LIMIT "limit_p"; + RETURN; + END IF; + SELECT count(1) INTO "count_v" + FROM ( + SELECT "initiative"."id" AS "initiative_id" + FROM "initiative" + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" + WHERE "issue"."closed" NOTNULL + ORDER BY "closed" DESC + LIMIT "limit_v" + ) AS "subquery" + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" + LIMIT "limit_p"; + IF "count_v" >= "limit_p" THEN + RETURN QUERY SELECT "initiative".* + FROM ( + SELECT + "initiative"."id" AS "initiative_id", + "issue"."closed" + FROM "initiative" + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" + WHERE "issue"."closed" NOTNULL + ORDER BY "closed" DESC + LIMIT "limit_v" + ) AS "subquery" + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" + WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" + ORDER BY "subquery"."closed" DESC + LIMIT "limit_p"; + RETURN; + END IF; + "limit_v" := "limit_v" * 2; + END LOOP; + END; + $$; + +COMMENT ON FUNCTION "closed_initiatives_in_bounding_box" + ( EBOX, INT4 ) + IS 'TODO'; +*/ + + +COMMIT; diff -r 9e4e39136136 -r 5b7c4082d7b3 geoindex_uninstall.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geoindex_uninstall.sql Fri Feb 05 12:53:00 2021 +0100 @@ -0,0 +1,13 @@ +BEGIN; + +--DROP FUNCTION IF EXISTS "closed_initiatives_in_bounding_box" (EBOX, INT4); +DROP INDEX IF EXISTS "suggestion_location_idx"; -- since v4.2.2 +DROP INDEX IF EXISTS "draft_location_idx"; -- since v4.2.2 +DROP INDEX IF EXISTS "initiative_location_idx"; -- since v4.2.2 +DROP INDEX IF EXISTS "area_location_idx"; -- since v4.2.2 +DROP INDEX IF EXISTS "unit_location_idx"; -- since v4.2.2 +DROP INDEX IF EXISTS "member_location_idx"; -- since v4.2.2 + +DROP EXTENSION IF EXISTS latlon; -- since v4.2.2 + +COMMIT; diff -r 9e4e39136136 -r 5b7c4082d7b3 update/core-update.v4.2.1-v4.2.2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v4.2.1-v4.2.2.sql Fri Feb 05 12:53:00 2021 +0100 @@ -0,0 +1,17 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('4.2.2', 4, 2, 2)) + AS "subquery"("string", "major", "minor", "revision"); + +--DROP FUNCTION "closed_initiatives_in_bounding_box" (EBOX, INT4); +DROP INDEX "suggestion_location_idx"; +DROP INDEX "draft_location_idx"; +DROP INDEX "initiative_location_idx"; +DROP INDEX "area_location_idx"; +DROP INDEX "unit_location_idx"; +DROP INDEX "member_location_idx"; + +DROP EXTENSION latlon; + +COMMIT;