wagoe.search.shell.query-sql — com.wagoe/wagoe-search 1.2.0

SQL for a search, from what wagoe.search.core.query describes.

Two query paths:

• PostgreSQL: to_tsvector/plainto_tsquery + ts_rank + ts_headline
• Fallback (H2/SQLite/MySQL, PostgreSQL suggest without pg_trgm): the database adapter's case-insensitive like

Filter values are matched against the JSON "filters" column using DB-appropriate syntax. Here rather than in core because the SQL is the store's business (BOU-875).

build-fallback-count-sql clj

(build-fallback-count-sql index-id entity-type match filters)

Build a parameterized count SQL for fallback search.

Args: index-id - string entity-type - string match - the adapter's like condition on content_all filters - map of keyword->any (optional, nil treated as empty)

Returns: JDBC SQL vector [sql & params]

build-fallback-search-sql clj

(build-fallback-search-sql index-id entity-type match limit offset filters)

Build a parameterized JDBC SQL vector for LIKE-based fallback search. Returns a static rank of 1.0, no snippet.

Args: index-id - string entity-type - string match - the adapter's like condition on content_all limit - integer offset - integer filters - map of keyword->any (optional, nil treated as empty)

Returns: JDBC SQL vector [sql & params]

build-fallback-suggest-sql clj

(build-fallback-suggest-sql index-id entity-type match limit)

(build-fallback-suggest-sql index-id entity-type match limit filters)

Build a LIKE-based fallback suggest query for H2, SQLite and MySQL.

Args: index-id - string entity-type - string match - the adapter's like condition on content_all limit - integer filters - map of keyword->any (optional)

Returns: JDBC SQL vector [sql & params]

build-postgres-count-sql clj

(build-postgres-count-sql index-id entity-type language query filters)

Build a parameterized JDBC SQL vector for counting PostgreSQL FTS results.

Args: index-id - string entity-type - string language - string query - string filters - map of keyword->any (optional, nil treated as empty)

Returns: JDBC SQL vector [sql & params]

build-postgres-like-suggest-sql clj

(build-postgres-like-suggest-sql index-id entity-type match limit filters)

The LIKE suggest query with PostgreSQL's filter SQL, for a database without pg_trgm (BOU-661). Same arguments as build-fallback-suggest-sql.

build-postgres-search-sql clj

(build-postgres-search-sql index-id
                           entity-type
                           language
                           query
                           limit
                           offset
                           highlight?
                           filters)

Build a parameterized JDBC SQL vector for PostgreSQL FTS search.

Uses a CTE to compute the tsvector and tsquery once, then ranks and optionally highlights results. Uses plainto_tsquery for safe handling of arbitrary user input (no FTS syntax required).

Args: index-id - string (e.g. "product-search") entity-type - string (e.g. "product") language - string FTS config (e.g. "english") query - string (user search query) limit - integer offset - integer highlight? - boolean filters - map of keyword->any (optional, nil treated as empty)

Returns: JDBC SQL vector [sql & params]

build-postgres-suggest-sql clj

(build-postgres-suggest-sql index-id entity-type query limit filters)

Build a parameterized SQL vector for trigram similarity suggestions.

Requires pg_trgm. word_similarity, not similarity: a prefix scored against a whole document stays under any useful threshold (BOU-661). The <% operator rather than a word_similarity comparison, so the trigram index on content_all can serve it; its threshold comes from build-trigram-threshold-sql (BOU-676).

Args: index-id - string entity-type - string query - string (partial user input) limit - integer filters - map of keyword->any (optional)

Returns: JDBC SQL vector [sql & params]

build-trigram-threshold-sql clj

(build-trigram-threshold-sql threshold)

Set pg_trgm.word_similarity_threshold, which <% compares against, for the current transaction. Run it in the transaction of build-postgres-suggest-sql.