(2021-08-29) Adding Postgresql Full-Text Search To FluxGarden

Time to add Postgresql Full-Text Search To FluxGarden

Working/starting from this

Aug29

• ALTER TABLE nodes ADD COLUMN __ts_vector__ tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED; → takes few seconds, then done.
• in top section of model.py add

from sqlalchemy.dialects.postgresql import TSVECTOR
import sqlalchemy as sa
class TSVector(sa.types.TypeDecorator):
  impl = TSVECTOR
from sqlalchemy import Index # note dropped the `desc` because it seemed to break things and didn't seem to be used

→ fine

• inside class Node() add

  __ts_vector__ = db.Column(TSVector(),db.Computed(
         "to_tsvector('english', title || ' ' || description)",
         persisted=True))
  __table_args__ = (Index('ix_video___ts_vector__',
          __ts_vector__, postgresql_using='gin'),)

→ fine

• tweak the old form in the header (which has been just posting to Google)
• copy titlesearch router/method to search, modify bit to use filter(Node.__ts_vector__.match(tstring))
• boom works!