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;