liquid_feedback_core
changeset 596:85489702edd2
Support for hashtag search
author | jbe |
---|---|
date | Thu Jan 10 16:52:08 2019 +0100 (2019-01-10) |
parents | 87914d1b757f |
children | d34f8403d2c6 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Dec 08 23:21:25 2018 +0100 1.2 +++ b/core.sql Thu Jan 10 16:52:08 2019 +0100 1.3 @@ -1317,6 +1317,7 @@ 1.4 1.5 1.6 CREATE TABLE "posting" ( 1.7 + UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme" 1.8 "id" SERIAL8 PRIMARY KEY, 1.9 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.10 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.11 @@ -1356,6 +1357,17 @@ 1.12 COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion'; 1.13 1.14 1.15 +CREATE TABLE "posting_lexeme" ( 1.16 + PRIMARY KEY ("posting_id", "lexeme"), 1.17 + FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.18 + "posting_id" INT8, 1.19 + "lexeme" TEXT, 1.20 + "author_id" INT4 ); 1.21 +CREATE INDEX "posting_lexeme_idx" ON "posting_lexeme" USING gist ("lexeme", (pstamp("author_id", "posting_id"))); 1.22 + 1.23 +COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.'; 1.24 + 1.25 + 1.26 CREATE TYPE "event_type" AS ENUM ( 1.27 'unit_created', 1.28 'unit_updated', 1.29 @@ -1854,6 +1866,40 @@ 1.30 (("to_tsvector"("direct_voter".*))); 1.31 1.32 1.33 +CREATE FUNCTION "update_posting_lexeme_trigger"() 1.34 + RETURNS TRIGGER 1.35 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.36 + DECLARE 1.37 + "lexeme_v" TEXT; 1.38 + BEGIN 1.39 + IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN 1.40 + DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id"; 1.41 + END IF; 1.42 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.43 + FOR "lexeme_v" IN 1.44 + SELECT regexp_matches[1] 1.45 + FROM regexp_matches(NEW."message", '#[^\s.,;:]+') 1.46 + LOOP 1.47 + INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme") 1.48 + VALUES ( 1.49 + NEW."id", 1.50 + NEW."author_id", 1.51 + "lexeme_v" ) 1.52 + ON CONFLICT ("posting_id", "lexeme") DO NOTHING; 1.53 + END LOOP; 1.54 + END IF; 1.55 + RETURN NULL; 1.56 + END; 1.57 + $$; 1.58 + 1.59 +CREATE TRIGGER "update_posting_lexeme" 1.60 + AFTER INSERT OR UPDATE OR DELETE ON "posting" 1.61 + FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"(); 1.62 + 1.63 +COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"'; 1.64 +COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date'; 1.65 + 1.66 + 1.67 1.68 ---------------------------------------------- 1.69 -- Writing of history entries and event log --