liquid_feedback_core
changeset 595:87914d1b757f
Revised full text search
author | jbe |
---|---|
date | Sat Dec 08 23:21:25 2018 +0100 (2018-12-08) |
parents | a2c156197bc7 |
children | 85489702edd2 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Dec 08 02:05:53 2018 +0100 1.2 +++ b/core.sql Sat Dec 08 23:21:25 2018 +0100 1.3 @@ -13,43 +13,6 @@ 1.4 1.5 1.6 1.7 ----------------------- 1.8 --- Full text search -- 1.9 ----------------------- 1.10 - 1.11 - 1.12 -CREATE FUNCTION "text_search_query"("query_text_p" TEXT) 1.13 - RETURNS TSQUERY 1.14 - LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.15 - BEGIN 1.16 - RETURN plainto_tsquery('pg_catalog.simple', "query_text_p"); 1.17 - END; 1.18 - $$; 1.19 - 1.20 -COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')'; 1.21 - 1.22 - 1.23 -CREATE FUNCTION "highlight" 1.24 - ( "body_p" TEXT, 1.25 - "query_text_p" TEXT ) 1.26 - RETURNS TEXT 1.27 - LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.28 - BEGIN 1.29 - RETURN ts_headline( 1.30 - 'pg_catalog.simple', 1.31 - replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 1.32 - "text_search_query"("query_text_p"), 1.33 - 'StartSel=* StopSel=* HighlightAll=TRUE' ); 1.34 - END; 1.35 - $$; 1.36 - 1.37 -COMMENT ON FUNCTION "highlight" 1.38 - ( "body_p" TEXT, 1.39 - "query_text_p" TEXT ) 1.40 - IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.'; 1.41 - 1.42 - 1.43 - 1.44 ------------------------- 1.45 -- Tables and indicies -- 1.46 ------------------------- 1.47 @@ -125,12 +88,11 @@ 1.48 "login_recovery_expiry" TIMESTAMPTZ, 1.49 "password_reset_secret" TEXT UNIQUE, 1.50 "password_reset_secret_expiry" TIMESTAMPTZ, 1.51 - "name" TEXT UNIQUE, 1.52 - "identification" TEXT UNIQUE, 1.53 + "name" TEXT UNIQUE, -- full text search 1.54 + "identification" TEXT UNIQUE, -- full text search 1.55 "authentication" TEXT, 1.56 "role" BOOLEAN NOT NULL DEFAULT FALSE, 1.57 "location" JSONB, 1.58 - "text_search_data" TSVECTOR, 1.59 CONSTRAINT "deleted_requires_locked" 1.60 CHECK ("deleted" ISNULL OR "locked" = TRUE), 1.61 CONSTRAINT "active_requires_activated_and_last_activity" 1.62 @@ -148,12 +110,6 @@ 1.63 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.64 CREATE INDEX "member_active_idx" ON "member" ("active"); 1.65 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location"))); 1.66 -CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data"); 1.67 -CREATE TRIGGER "update_text_search_data" 1.68 - BEFORE INSERT OR UPDATE ON "member" 1.69 - FOR EACH ROW EXECUTE PROCEDURE 1.70 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.71 - "name", "identification"); 1.72 1.73 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; 1.74 1.75 @@ -312,16 +268,9 @@ 1.76 CREATE TABLE "member_profile" ( 1.77 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.78 "formatting_engine" TEXT, 1.79 - "statement" TEXT, 1.80 + "statement" TEXT, -- full text search 1.81 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'), 1.82 - "profile_text_data" TEXT, 1.83 - "text_search_data" TSVECTOR ); 1.84 -CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data"); 1.85 -CREATE TRIGGER "update_text_search_data" 1.86 - BEFORE INSERT OR UPDATE ON "member_profile" 1.87 - FOR EACH ROW EXECUTE PROCEDURE 1.88 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.89 - 'statement', 'profile_text_data'); 1.90 + "profile_text_data" TEXT ); -- full text search 1.91 1.92 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"'; 1.93 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile'; 1.94 @@ -665,22 +614,15 @@ 1.95 "id" SERIAL4 PRIMARY KEY, 1.96 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.97 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.98 - "name" TEXT NOT NULL, 1.99 - "description" TEXT NOT NULL DEFAULT '', 1.100 + "name" TEXT NOT NULL, -- full text search 1.101 + "description" TEXT NOT NULL DEFAULT '', -- full text search 1.102 "external_reference" TEXT, 1.103 "member_count" INT4, 1.104 - "location" JSONB, 1.105 - "text_search_data" TSVECTOR ); 1.106 + "location" JSONB ); 1.107 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; 1.108 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); 1.109 CREATE INDEX "unit_active_idx" ON "unit" ("active"); 1.110 CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location"))); 1.111 -CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); 1.112 -CREATE TRIGGER "update_text_search_data" 1.113 - BEFORE INSERT OR UPDATE ON "unit" 1.114 - FOR EACH ROW EXECUTE PROCEDURE 1.115 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.116 - "name", "description" ); 1.117 1.118 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; 1.119 1.120 @@ -705,8 +647,8 @@ 1.121 "id" SERIAL4 PRIMARY KEY, 1.122 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.123 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.124 - "name" TEXT NOT NULL, 1.125 - "description" TEXT NOT NULL DEFAULT '', 1.126 + "name" TEXT NOT NULL, -- full text search 1.127 + "description" TEXT NOT NULL DEFAULT '', -- full text search 1.128 "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0), 1.129 "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0), 1.130 "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL), 1.131 @@ -715,16 +657,9 @@ 1.132 "quorum_den" INT4 CHECK ("quorum_den" > 0), 1.133 "issue_quorum" INT4, 1.134 "external_reference" TEXT, 1.135 - "location" JSONB, 1.136 - "text_search_data" TSVECTOR ); 1.137 + "location" JSONB ); 1.138 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.139 CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location"))); 1.140 -CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data"); 1.141 -CREATE TRIGGER "update_text_search_data" 1.142 - BEFORE INSERT OR UPDATE ON "area" 1.143 - FOR EACH ROW EXECUTE PROCEDURE 1.144 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.145 - "name", "description" ); 1.146 1.147 COMMENT ON TABLE "area" IS 'Subject areas'; 1.148 1.149 @@ -919,13 +854,14 @@ 1.150 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote" 1.151 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.152 "id" SERIAL4 PRIMARY KEY, 1.153 - "name" TEXT NOT NULL, 1.154 + "name" TEXT NOT NULL, -- full text index 1.155 "polling" BOOLEAN NOT NULL DEFAULT FALSE, 1.156 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.157 "revoked" TIMESTAMPTZ, 1.158 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.159 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE, 1.160 "location" JSONB, 1.161 + "content" TEXT, -- full text search 1.162 "external_reference" TEXT, 1.163 "admitted" BOOLEAN, 1.164 "supporter_count" INT4, 1.165 @@ -947,8 +883,6 @@ 1.166 "eligible" BOOLEAN, 1.167 "winner" BOOLEAN, 1.168 "rank" INT4, 1.169 - "text_search_data" TSVECTOR, 1.170 - "draft_text_search_data" TSVECTOR, 1.171 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.172 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)), 1.173 CONSTRAINT "non_revoked_initiatives_cant_suggest_other" 1.174 @@ -974,12 +908,6 @@ 1.175 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") ); 1.176 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.177 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location"))); 1.178 -CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.179 -CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data"); 1.180 -CREATE TRIGGER "update_text_search_data" 1.181 - BEFORE INSERT OR UPDATE ON "initiative" 1.182 - FOR EACH ROW EXECUTE PROCEDURE 1.183 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name"); 1.184 1.185 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.186 1.187 @@ -987,6 +915,7 @@ 1.188 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.189 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative'; 1.190 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)'; 1.191 +COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)'; 1.192 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference'; 1.193 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.194 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.195 @@ -1043,18 +972,12 @@ 1.196 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.197 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.198 "formatting_engine" TEXT, 1.199 - "content" TEXT NOT NULL, 1.200 + "content" TEXT NOT NULL, -- full text search 1.201 "location" JSONB, 1.202 - "external_reference" TEXT, 1.203 - "text_search_data" TSVECTOR ); 1.204 + "external_reference" TEXT ); 1.205 CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.206 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 1.207 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location"))); 1.208 -CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 1.209 -CREATE TRIGGER "update_text_search_data" 1.210 - BEFORE INSERT OR UPDATE ON "draft" 1.211 - FOR EACH ROW EXECUTE PROCEDURE 1.212 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); 1.213 1.214 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.215 1.216 @@ -1081,12 +1004,11 @@ 1.217 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE, 1.218 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.219 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.220 - "name" TEXT NOT NULL, 1.221 + "name" TEXT NOT NULL, -- full text search 1.222 "formatting_engine" TEXT, 1.223 - "content" TEXT NOT NULL DEFAULT '', 1.224 + "content" TEXT NOT NULL DEFAULT '', -- full text search 1.225 "location" JSONB, 1.226 "external_reference" TEXT, 1.227 - "text_search_data" TSVECTOR, 1.228 "minus2_unfulfilled_count" INT4, 1.229 "minus2_fulfilled_count" INT4, 1.230 "minus1_unfulfilled_count" INT4, 1.231 @@ -1099,12 +1021,6 @@ 1.232 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.233 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.234 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location"))); 1.235 -CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.236 -CREATE TRIGGER "update_text_search_data" 1.237 - BEFORE INSERT OR UPDATE ON "suggestion" 1.238 - FOR EACH ROW EXECUTE PROCEDURE 1.239 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', 1.240 - "name", "content"); 1.241 1.242 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.243 1.244 @@ -1340,14 +1256,8 @@ 1.245 "weight" INT4, 1.246 "comment_changed" TIMESTAMPTZ, 1.247 "formatting_engine" TEXT, 1.248 - "comment" TEXT, 1.249 - "text_search_data" TSVECTOR ); 1.250 + "comment" TEXT ); -- full text index 1.251 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id"); 1.252 -CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); 1.253 -CREATE TRIGGER "update_text_search_data" 1.254 - BEFORE INSERT OR UPDATE ON "direct_voter" 1.255 - FOR EACH ROW EXECUTE PROCEDURE 1.256 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); 1.257 1.258 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; frontends must ensure that no voters are added or removed to/from this table when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table'; 1.259 1.260 @@ -1856,6 +1766,95 @@ 1.261 1.262 1.263 1.264 +---------------------- 1.265 +-- Full text search -- 1.266 +---------------------- 1.267 + 1.268 + 1.269 +CREATE FUNCTION "highlight" 1.270 + ( "body_p" TEXT, 1.271 + "query_text_p" TEXT ) 1.272 + RETURNS TEXT 1.273 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.274 + BEGIN 1.275 + RETURN ts_headline( 1.276 + replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 1.277 + "text_search_query"("query_text_p"), 1.278 + 'StartSel=* StopSel=* HighlightAll=TRUE' ); 1.279 + END; 1.280 + $$; 1.281 + 1.282 +COMMENT ON FUNCTION "highlight" 1.283 + ( "body_p" TEXT, 1.284 + "query_text_p" TEXT ) 1.285 + IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.'; 1.286 + 1.287 + 1.288 +CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR 1.289 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.290 + $1."name", 1.291 + $1."identification" 1.292 + )) $$; 1.293 +CREATE INDEX ON "member" USING gin 1.294 + (("to_tsvector"("member".*))); 1.295 + 1.296 + 1.297 +CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR 1.298 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.299 + $1."statement", 1.300 + $1."profile_text_data" 1.301 + )) $$; 1.302 +CREATE INDEX ON "member_profile" USING gin 1.303 + (("to_tsvector"("member_profile".*))); 1.304 + 1.305 +CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR 1.306 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.307 + $1."name", 1.308 + $1."description" 1.309 + )) $$; 1.310 +CREATE INDEX ON "unit" USING gin 1.311 + (("to_tsvector"("unit".*))); 1.312 + 1.313 +CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR 1.314 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.315 + $1."name", 1.316 + $1."description" 1.317 + )) $$; 1.318 +CREATE INDEX ON "area" USING gin 1.319 + (("to_tsvector"("area".*))); 1.320 + 1.321 +CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR 1.322 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.323 + $1."name", 1.324 + $1."content" 1.325 + )) $$; 1.326 +CREATE INDEX ON "initiative" USING gin 1.327 + (("to_tsvector"("initiative".*))); 1.328 + 1.329 +CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR 1.330 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.331 + $1."content" 1.332 + )) $$; 1.333 +CREATE INDEX ON "draft" USING gin 1.334 + (("to_tsvector"("draft".*))); 1.335 + 1.336 +CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR 1.337 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.338 + $1."name", 1.339 + $1."content" 1.340 + )) $$; 1.341 +CREATE INDEX ON "suggestion" USING gin 1.342 + (("to_tsvector"("suggestion".*))); 1.343 + 1.344 +CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR 1.345 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.346 + $1."comment" 1.347 + )) $$; 1.348 +CREATE INDEX ON "direct_voter" USING gin 1.349 + (("to_tsvector"("direct_voter".*))); 1.350 + 1.351 + 1.352 + 1.353 ---------------------------------------------- 1.354 -- Writing of history entries and event log -- 1.355 ---------------------------------------------- 1.356 @@ -3263,7 +3262,7 @@ 1.357 FOR UPDATE; 1.358 UPDATE "initiative" SET 1.359 "location" = "draft"."location", 1.360 - "draft_text_search_data" = "draft"."text_search_data" 1.361 + "content" = "draft"."content" 1.362 FROM "current_draft" AS "draft" 1.363 WHERE "initiative"."id" = "initiative_id_p" 1.364 AND "draft"."initiative_id" = "initiative_id_p"; 1.365 @@ -6586,7 +6585,6 @@ 1.366 "password_reset_secret_expiry" = NULL, 1.367 "location" = NULL 1.368 WHERE "id" = "member_id_p"; 1.369 - -- "text_search_data" is updated by triggers 1.370 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p"; 1.371 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p"; 1.372 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p"; 1.373 @@ -6647,7 +6645,6 @@ 1.374 "password_reset_secret" = NULL, 1.375 "password_reset_secret_expiry" = NULL, 1.376 "location" = NULL; 1.377 - -- "text_search_data" is updated by triggers 1.378 DELETE FROM "verification"; 1.379 DELETE FROM "member_settings"; 1.380 DELETE FROM "member_useterms";