Поиск: FTS5, и почему параметр спасает не от всего

Зачем это нужно

Поиск по блогу обычно начинают с like '%слово%' — и в уроке про теги мы уже видели, чем это кончается: запрос %go% нашёл golang.

Есть и другие беды. like не умеет искать сразу по заголовку и телу с разным весом. Не умеет сказать, какая статья подходит лучше. Не подсвечивает найденное. И читает всю таблицу целиком: на трёх статьях это незаметно, на трёх тысячах — уже нет.

В SQLite для этого есть отдельный инструмент — FTS5, полнотекстовый поиск. Он встроен, ставить ничего не нужно, и работает через обычный database/sql.

Сразу целиком

Новая папка, go mod init sabaq31, драйвер тот же:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"strings"

	_ "modernc.org/sqlite"
)

func main() {
	os.Remove("blog.db")
	db, err := sql.Open("sqlite", "blog.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	setup(db)

	for _, q := range []string{"степь", "СТЕПЬ", "сте", "степь зелёная", "степь OR", "\"", "нетслова"} {
		fmt.Printf("%-12q -> %s\n", q, search(db, q))
	}
}

// ftsQuery превращает то, что набрал человек, в запрос для FTS5. Каждое слово
// берётся в кавычки — тогда AND, OR и звёздочка внутри остаются буквами, — и
// к нему приписывается звёздочка снаружи, чтобы искалось по началу слова.
func ftsQuery(s string) string {
	var parts []string
	for _, w := range strings.Fields(s) {
		w = strings.ReplaceAll(w, `"`, `""`)
		parts = append(parts, `"`+w+`"*`)
	}
	return strings.Join(parts, " AND ")
}

func search(db *sql.DB, q string) string {
	fts := ftsQuery(q)
	if fts == "" {
		return "пустой запрос"
	}
	rows, err := db.Query(`select a.slug, snippet(articles_fts, 1, '[', ']', '…', 5)
		from articles_fts
		join articles a on a.id = articles_fts.rowid
		where articles_fts match ?
		order by bm25(articles_fts, 10.0, 1.0)`, fts)
	if err != nil {
		return "ошибка: " + err.Error()
	}
	defer rows.Close()

	var out []string
	for rows.Next() {
		var slug, frag string
		if err := rows.Scan(&slug, &frag); err != nil {
			return "ошибка: " + err.Error()
		}
		out = append(out, slug+": "+frag)
	}
	if err := rows.Err(); err != nil {
		return "ошибка: " + err.Error()
	}
	if len(out) == 0 {
		return "ничего не нашлось"
	}
	return strings.Join(out, " | ")
}

func setup(db *sql.DB) {
	for _, q := range []string{
		`create table articles (
			id    integer primary key,
			slug  text not null unique,
			title text not null,
			body  text not null
		) strict`,
		// content='articles' — таблица поиска не хранит текст ещё раз, а
		// смотрит в articles; content_rowid говорит, по какому столбцу.
		`create virtual table articles_fts using fts5(
			title, body, content='articles', content_rowid='id'
		)`,
		`create trigger articles_ai after insert on articles begin
			insert into articles_fts (rowid, title, body)
			values (new.id, new.title, new.body);
		end`,
		`create trigger articles_ad after delete on articles begin
			insert into articles_fts (articles_fts, rowid, title, body)
			values ('delete', old.id, old.title, old.body);
		end`,
		`create trigger articles_au after update on articles begin
			insert into articles_fts (articles_fts, rowid, title, body)
			values ('delete', old.id, old.title, old.body);
			insert into articles_fts (rowid, title, body)
			values (new.id, new.title, new.body);
		end`,
	} {
		if _, err := db.Exec(q); err != nil {
			log.Fatal(err)
		}
	}
	for _, a := range [][3]string{
		{"step", "Степь", "Степь летом жёлтая, весной зелёная"},
		{"privet", "Привет, мир", "Первая запись. Блог начинается отсюда"},
		{"go", "Язык Go", "Go — компилируемый язык"},
		{"shanyraq", "Что такое шанырак", "Шанырак — круг на вершине юрты"},
		{"vesna", "Весна", "Весной степь зелёная"},
		{"test", "Что такое тест", "Тест говорит, что сломалось при правке"},
	} {
		if _, err := db.Exec(
			`insert into articles (slug, title, body) values (?, ?, ?)`, a[0], a[1], a[2]); err != nil {
			log.Fatal(err)
		}
	}
}

Вывод:

"степь"      -> step: [Степь] летом жёлтая, весной зелёная | vesna: Весной [степь] зелёная
"СТЕПЬ"      -> step: [Степь] летом жёлтая, весной зелёная | vesna: Весной [степь] зелёная
"сте"        -> step: [Степь] летом жёлтая, весной зелёная | vesna: Весной [степь] зелёная
"степь зелёная" -> step: [Степь] летом жёлтая, весной [зелёная] | vesna: Весной [степь] [зелёная]
"степь OR"   -> ничего не нашлось
"\""         -> ничего не нашлось
"нетслова"   -> ничего не нашлось

Разбор

Виртуальная таблица: индекс, а не копия

create virtual table ... using fts5 заводит таблицу, которой на диске нет в обычном смысле. Внутри — обратный указатель: для каждого слова список статей, где оно встречается. Так книга находит страницу по слову за секунду, а не перелистыванием.

Ключевые здесь два слова в скобках:

create virtual table articles_fts using fts5(
	title, body, content='articles', content_rowid='id'
)

content='articles' говорит: сам текст не храни, за ним ходи в articles. Без этой строки блог хранил бы каждую статью дважды. content_rowid='id' объясняет, по какому столбцу связывать строки.

Образ. Предметный указатель в конце книги. Он не пересказывает книгу, он только говорит, на какой странице искать слово. Выдрать указатель — книга останется, выдрать книгу — указатель станет бесполезен.

Три триггера: индекс, который не отстаёт

Раз текст не копируется, синхронность приходится держать самим. Это делают триггеры — маленькие правила, которые база выполняет сама при изменении таблицы.

Их ровно три: после вставки, после удаления, после правки. Удаление записывается странно на вид:

insert into articles_fts (articles_fts, rowid, title, body)
values ('delete', old.id, old.title, old.body);

Это не описка. У таблиц с content= удаление из индекса оформляется как вставка служебной команды 'delete' — вместе со старыми значениями, иначе индекс не найдёт, что убирать. Правка — это удаление плюс вставка, поэтому третий триггер делает оба действия подряд.

Забудете триггеры — поиск будет находить удалённые статьи и не находить новые, причём молча.

match, а не like

Запрос к поиску пишется через match:

where articles_fts match ?

Слева — имя таблицы поиска, справа — запрос. Совпадение ищется по словам целиком, а не по кускам строки, поэтому степь не найдёт степной, зато найдёт Степь с большой буквы: регистр FTS5 не различает. В выводе это вторая строка — СТЕПЬ нашла то же самое.

Что печатает человек — не то, что понимает FTS5

Вот главное место урока.

У FTS5 свой язык запросов: AND, OR, NOT, кавычки для фразы, звёздочка для начала слова, title:слово для поиска по одному столбцу. Человек в строке поиска про это не знает и печатает что угодно.

Уберём ftsQuery и отдадим введённое как есть:

"сте"        -> ничего не нашлось
"степь OR"   -> ошибка: SQL logic error: fts5: syntax error near "" (1)
"\""         -> ошибка: SQL logic error: unterminated string (1)

Заметьте, что это не инъекция: значение шло через ?, в текст запроса оно не попало, дописать свой SQL читатель не может. Но запрос он всё равно сломал — потому что разбирает его теперь не SQL, а FTS5, и у неё свои правила.

Отсюда правило: перед match введённое всегда готовят. Наш ftsQuery делает минимум, которого хватает:

w = strings.ReplaceAll(w, `"`, `""`)
parts = append(parts, `"`+w+`"*`)

Каждое слово берётся в кавычки — внутри кавычек OR перестаёт быть командой и становится словом. Кавычка внутри слова удваивается, иначе она закроет нашу. Звёздочка снаружи означает «слово начинается на это», поэтому сте находит Степь. Слова соединяются через AND: найдётся то, где есть все.

order by rank: чем меньше, тем лучше

Порядок задаёт bm25 — мера, которой десятки лет и которая учитывает, сколько раз слово встретилось, насколько оно редкое и какой длины текст.

┌───────┬────────┐
│ rowid │  rank  │
├───────┼────────┤
│ 1     │ -0.826 │
│ 5     │ -0.698 │
└───────┴────────┘

Числа отрицательные, и меньше значит лучше — поэтому order by rank без desc. Первая статья подходит сильнее второй.

bm25 принимает веса — по одному на столбец:

order by bm25(articles_fts, 10.0, 1.0)

Заголовок вдесятеро важнее тела, и разрыв растёт: -1.173 против -0.698. Это то, чего от поиска ждут: статья с искомым словом в заголовке должна быть первой.

Одна странность, на которой легко решить, что ранжирование сломано: если статей совсем мало и слово есть почти в каждой, bm25 вернёт ноль для всех. На трёх статьях я это и увидел. Дело не в коде — редкое слово тем и ценно, что редкое, а в крошечной базе редких слов не бывает.

snippet: показать, где нашлось

Ссылка на статью без объяснения, почему она нашлась, — половина ответа. snippet вырезает кусок вокруг найденного слова и обрамляет его:

snippet(articles_fts, 1, '[', ']', '…', 5)

Аргументы: столбец (1 — это body, счёт с нуля), чем открыть, чем закрыть, чем обозначить обрыв и сколько слов показать. В настоящем блоге вместо скобок ставят <mark> и </mark> — но тогда шаблон обязан вывести это как разметку, а не как текст, и здесь нужна осторожность из урока про шаблоны: подсвечивать можно только то, что вернула база, а не то, что прислал читатель.

Казахские буквы: что работает, а что нет

Проверим, как FTS5 разбирает слова не на латинице:

create virtual table t using fts5(w);
insert into t values ('café'), ('ёлка'), ('әже');

• поиск cafe находит café;
• поиск елка не находит ёлка;
• поиск аже не находит әже.

Причина в настройке remove_diacritics, которая включена по умолчанию. Она снимает надстрочные знаки там, где буква собрана из основы и знака — как é из e. А ё, ә, ң, қ — не украшенные буквы, а отдельные буквы своего алфавита, и сниматься там нечему.

Для казахского блога это значит простую вещь: читатель, набравший алем, не найдёт әлем. Лечится это не настройкой, а работой: приведением запроса и текста к одному виду перед индексацией. Мы этого делать не будем — но знать, почему поиск «не находит очевидное», нужно заранее.

Карта урока

Скажите своими словами

Не подглядывая, ответьте вслух или на бумаге. Ответы — в конце урока.

1. Что делает content='articles' и что было бы без него?
2. Почему ? не спасает от того, что человек напечатал OR в строке поиска?
3. Почему order by rank пишут без desc?

Задание

Обязательное. Сделайте поиск в блоге. Миграция заводит таблицу поиска и три триггера — и заодно заполняет индекс по уже написанным статьям. На главной появляется форма GET /search?q=, результаты выводятся с подсветкой и с сообщением «ничего не нашлось», когда пусто.

Всё это сделано в step-19 — сверьтесь после того, как напишете сами.

По желанию.

• Добавьте в поиск теги: третий столбец в таблице поиска и триггеры, которые его наполняют.
• Выведите число найденного и постраничный вывод через limit и offset.
• Замерьте like '%дала%' и match на десяти тысячах строк. Разница объяснит, зачем всё это было.

Куда это встанет в блоге

Поиск — первая часть блога, которая работает не с одной статьёй, а со всеми сразу. И первая, где ответ зависит от того, насколько хорошо мы подготовили чужой ввод.

Долги. Индекс пока не знает про теги. Казахские буквы ищутся буквально. И на странице поиска нет ни постраничного вывода, ни запоминания запроса — этим займётся урок про макет, где у всех страниц появится общая рамка.

Ответы

Источники

• SQLite: FTS5
• SQLite: функции snippet и bm25