liquid_feedback_core

changeset 596:85489702edd2

Support for hashtag search
author jbe
date Thu Jan 10 16:52:08 2019 +0100 (14 months ago)
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 --

Impressum / About Us