liquid_feedback_core
diff core.sql @ 622:5b7c4082d7b3
Version 4.2.2 with geoindices uninstalled by default
author | jbe |
---|---|
date | Fri Feb 05 12:53:00 2021 +0100 (2021-02-05) |
parents | e0b8175fda29 |
children | 7f488033d5ee |
line diff
1.1 --- a/core.sql Sat Dec 05 18:56:20 2020 +0100 1.2 +++ b/core.sql Fri Feb 05 12:53:00 2021 +0100 1.3 @@ -3,10 +3,8 @@ 1.4 1.5 BEGIN; 1.6 1.7 -CREATE EXTENSION IF NOT EXISTS latlon; 1.8 - 1.9 CREATE VIEW "liquid_feedback_version" AS 1.10 - SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 1.11 + SELECT * FROM (VALUES ('4.2.2', 4, 2, 2)) 1.12 AS "subquery"("string", "major", "minor", "revision"); 1.13 1.14 1.15 @@ -125,7 +123,6 @@ 1.16 CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.17 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.18 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.19 -CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location"))); 1.20 1.21 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; 1.22 1.23 @@ -640,7 +637,6 @@ 1.24 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.25 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.26 CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.27 -CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location"))); 1.28 1.29 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; 1.30 1.31 @@ -679,7 +675,6 @@ 1.32 "external_reference" TEXT, 1.33 "location" JSONB ); 1.34 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.35 -CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location"))); 1.36 1.37 COMMENT ON TABLE "area" IS 'Subject areas'; 1.38 1.39 @@ -928,7 +923,6 @@ 1.40 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE), 1.41 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") ); 1.42 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.43 -CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location"))); 1.44 1.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.'; 1.46 1.47 @@ -998,7 +992,6 @@ 1.48 "external_reference" TEXT ); 1.49 CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.50 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 1.51 -CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location"))); 1.52 1.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.'; 1.54 1.55 @@ -1051,7 +1044,6 @@ 1.56 "proportional_order" INT4 ); 1.57 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.58 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.59 -CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location"))); 1.60 1.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'; 1.62 1.63 @@ -4868,86 +4860,6 @@ 1.64 1.65 1.66 1.67 ------------------------- 1.68 --- Geospatial lookups -- 1.69 ------------------------- 1.70 - 1.71 -/* 1.72 -CREATE FUNCTION "closed_initiatives_in_bounding_box" 1.73 - ( "bounding_box_p" EBOX, 1.74 - "limit_p" INT4 ) 1.75 - RETURNS SETOF "initiative" 1.76 - LANGUAGE 'plpgsql' STABLE AS $$ 1.77 - DECLARE 1.78 - "limit_v" INT4; 1.79 - "count_v" INT4; 1.80 - BEGIN 1.81 - "limit_v" := "limit_p" + 1; 1.82 - LOOP 1.83 - SELECT count(1) INTO "count_v" 1.84 - FROM "initiative" 1.85 - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.86 - WHERE "issue"."closed" NOTNULL 1.87 - AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.88 - LIMIT "limit_v"; 1.89 - IF "count_v" < "limit_v" THEN 1.90 - RETURN QUERY SELECT "initiative".* 1.91 - FROM ( 1.92 - SELECT 1.93 - "initiative"."id" AS "initiative_id", 1.94 - "issue"."closed" 1.95 - FROM "initiative" 1.96 - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.97 - WHERE "issue"."closed" NOTNULL 1.98 - AND GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.99 - ) AS "subquery" 1.100 - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.101 - ORDER BY "subquery"."closed" DESC 1.102 - LIMIT "limit_p"; 1.103 - RETURN; 1.104 - END IF; 1.105 - SELECT count(1) INTO "count_v" 1.106 - FROM ( 1.107 - SELECT "initiative"."id" AS "initiative_id" 1.108 - FROM "initiative" 1.109 - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.110 - WHERE "issue"."closed" NOTNULL 1.111 - ORDER BY "closed" DESC 1.112 - LIMIT "limit_v" 1.113 - ) AS "subquery" 1.114 - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.115 - WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.116 - LIMIT "limit_p"; 1.117 - IF "count_v" >= "limit_p" THEN 1.118 - RETURN QUERY SELECT "initiative".* 1.119 - FROM ( 1.120 - SELECT 1.121 - "initiative"."id" AS "initiative_id", 1.122 - "issue"."closed" 1.123 - FROM "initiative" 1.124 - JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.125 - WHERE "issue"."closed" NOTNULL 1.126 - ORDER BY "closed" DESC 1.127 - LIMIT "limit_v" 1.128 - ) AS "subquery" 1.129 - JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id" 1.130 - WHERE GeoJSON_to_ecluster("initiative"."location") && "bounding_box_p" 1.131 - ORDER BY "subquery"."closed" DESC 1.132 - LIMIT "limit_p"; 1.133 - RETURN; 1.134 - END IF; 1.135 - "limit_v" := "limit_v" * 2; 1.136 - END LOOP; 1.137 - END; 1.138 - $$; 1.139 - 1.140 -COMMENT ON FUNCTION "closed_initiatives_in_bounding_box" 1.141 - ( EBOX, INT4 ) 1.142 - IS 'TODO'; 1.143 -*/ 1.144 - 1.145 - 1.146 - 1.147 --------------------------- 1.148 -- Transaction isolation -- 1.149 ---------------------------