One search over several tables

A full-text index searches one field of one table. DEFINE SEARCH searches several fields of several tables as one collection: one ranking, one set of statistics, one query language. It arrived in 0.20.0-beta.

Declaring one

DEFINE ANALYZER plain FILTERS lowercase, ascii;
DEFINE COLLECTION notes;
DEFINE COLLECTION articles;
CREATE notes:1 = { title: 'Ada Lovelace', body: 'She wrote the first program for the analytical engine', kind: 'person' };
CREATE notes:2 = { title: 'Looms', body: 'The Jacquard loom read its pattern from punched cards', kind: 'machine' };
CREATE articles:1 = { headline: 'Engines of thought', text: 'Babbage designed an engine; Lovelace saw what it could do', code: 'AE-1', kind: 'machine' };
CREATE articles:2 = { headline: 'Compilers', text: 'Grace Hopper wrote the first compiler', code: 'CC-2', kind: 'person' };

DEFINE SYNONYMS machines { engine: ['loom', 'machine'] };
DEFINE STOPWORDS common ['the', 'a', 'of'];
DEFINE SEARCH knowledge
  ON notes    FIELDS title WEIGHT 3 SNIPPET, body SYNONYMS machines
  ON articles FIELDS headline WEIGHT 2, text, code NO FUZZY NO PREFIX
  ANALYZER plain STOPWORDS common;

The search keeps one member per table, written with the table's records in their own batch — so it is current the moment the write that changed it commits, and a transaction's own writes are answered inside it. Declaring it builds it from the records already there. Its name is unique within the database.

One analyzer. Every member field and every query is read with the search's ANALYZER, not with each field's own: a ranking across fields needs one vocabulary, or the same word would be two terms depending on where it was found.

Reading it

SELECT search::table_name() AS source, search::score() AS score
FROM SEARCH knowledge MATCHES 'lovelace';

1: { score: 1.0166158648212533, source: 'notes' }
1: { score: 0.6288345555595379, source: 'articles' }
(2 record(s), via index)

Records come best first, ties broken by table name and then by id. A search answers in the order it ranks, so an ORDER BY over it is refused rather than quietly re-sorted:

SELECT id FROM SEARCH knowledge MATCHES 'ada' ORDER BY id;

Refused — SearchIsItsOwnOrder.

Ranking is BM25F. Each field's occurrences are measured against that field's own length and its own average, and multiplied by its WEIGHT (default 1), before the score saturates — so a word spread over a title and a body is not counted as two words. The note above ranks first because Lovelace is its whole title, and the title weighs three. A search with one field of weight 1 ranks exactly as search::score over a field index does.

The query is MATCHES's own — quoted phrases with slop, OR, NOT, starred words — and MATCHES PREFIX, MATCHES FUZZY and MATCHES INFIX read every word one way. A conjunction is over the record as one document: 'lovelace engine' holds for notes:1, whose title has one word and whose body has the other. A quoted phrase must sit inside one field.

Each of them answers about a record a search ranked, and only there:

SELECT search::score() AS s FROM notes;

Refused — NotSearched.

SELECT search::table_name() AS source, search::snippet() AS snippet
FROM SEARCH knowledge MATCHES 'lovelace engine';

1: { snippet: { end: 12, field: 'title', start: 0 }, source: 'notes' }
1: { source: 'articles' }
(2 record(s), via index)

The article has no SNIPPET field, so it answers no window rather than an empty one. Offsets are bytes into the text; the store does not render.

Per-field options

NO FUZZY, NO PREFIX and NO PHRASE say a field does not answer that operator — an identifier such as code should not answer a misspelling, and NO PREFIX refuses infix too. SYNONYMS <set> answers a word in that field by its alternatives as well. SNIPPET makes the field one search::snippet() may take its window from. Each option is said once.

Synonyms and stop words are read when a search runs, never by the index, so changing one never needs a rebuild:

SELECT search::table_name() AS source, search::highlight(body) AS marks
FROM SEARCH knowledge MATCHES 'engine';

1: { marks: [{ end: 53, start: 47 }], source: 'notes' }
2: { marks: [{ end: 17, start: 13 }], source: 'notes' }
1: { marks: [], source: 'articles' }
(3 record(s), via index)

notes:2 never says engine: its body's loom answered through machines, and the highlight marks it. The article matched in text, so its body — a field it does not have — marks nothing. A synonym set maps a word to single words — a phrase as an alternative is refused, so a quoted phrase is the same length either way:

DEFINE SYNONYMS phrases { car: ['motor car'] };

Refused — NotOneWord.

A stop word is dropped from the query outside a quoted phrase, and a query of stop words alone answers nothing:

SELECT id FROM SEARCH knowledge MATCHES 'the';

(0 record(s), via index)

Inside quotes it is kept, because a phrase is the words in that order: '"wrote the first"' answers notes:1 and articles:2.

Type-ahead and facets

SELECT term, documents FROM SEARCH knowledge COMPLETE 'lov';

'lovelace': { documents: 2, term: 'lovelace' }
(1 record(s), via index)

The search's words beginning with what was typed, ranked by how many records hold them; under three characters is PrefixTooShort. With a stemming analyzer the words are stems, so a type-ahead box wants a search over an unstemmed one.

A facet is a grouping over the same source, and it counts every answered record rather than one page:

SELECT kind, count(*) AS n FROM SEARCH knowledge MATCHES 'engine' GROUP BY kind;

Read in one transaction with the ranked read, it describes the same snapshot. AFTER, VERSION, FETCH and ORDER BY FUSE are refused over a search.

Grants

A table the reader may not read is not searched, and neither is a member with a field the reader may not read: a member's statistics and dictionary cannot say which field a word came from, so a partial view would leak through the numbers. The collection a reader is ranked against is the members they reach, so one table's statistics never reach a reader of another. Type-ahead follows the same rule.

On a cluster a node holding part of a split member table does not score it against its own part; the read is sent to a node holding the whole table, so a score is always measured against the whole collection.

What it reports, and dropping it

EXPLAIN SELECT id FROM SEARCH knowledge MATCHES 'lovelace';

{ access: 'index', exact: true, index: 'knowledge', shape: 'search from postings', source: 'search' }

search from postings (from 0.22.0-beta) means the postings decided the read: each member posting keeps every field's term frequency and length, so a query of words, prefixes and infixes — with OR, NOT, synonyms and weights — is matched and scored from them and reads only the records it answers, with the same scores. A phrase, a fuzzy word, a condition beside the match, or a member this transaction has written to cannot be decided that way: the read plans as search and reads its records' text.

INFO FOR SEARCH knowledge answers the analyzer, the stop words and, per member, the table, how many records it holds, and each field's weight and options; INFO FOR DATABASE names the database's searches under searches.

From 0.26.0-beta each member also reports tokenizer — the generation of the code that wrote its postings, NULL for a member written before — and rebuild. A member whose postings another tokenizer wrote is still read, because no scan stands in for a search, and every read of it carries the note needs-rebuild: its answer can miss records (why). A search is rebuilt by defining it again, in one transaction so it is never absent:

BEGIN;
DROP SEARCH knowledge;
DEFINE SEARCH knowledge
  ON notes    FIELDS title WEIGHT 3 SNIPPET, body SYNONYMS machines
  ON articles FIELDS headline WEIGHT 2, text, code NO FUZZY NO PREFIX
  ANALYZER plain STOPWORDS common;
COMMIT;

A word set cannot be dropped while a search names it:

DROP SYNONYMS machines;

Refused — StillDepended, naming the search. There is no CASCADE.

DROP SEARCH knowledge;
DROP SYNONYMS machines;
DROP STOPWORDS common;

DROP SEARCH removes every member; the tables and their records are untouched.