Іздеу: 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{
		{"dala", "Дала туралы", "Дала жазда сары, көктемде жасыл болады"},
		{"salem", "Сәлем, әлем", "Бірінші жазба. Блог осыдан басталады"},
		{"go", "Go тілі", "Go — компиляцияланатын тіл"},
		{"shanyraq", "Шаңырақ деген не", "Шаңырақ — киіз үйдің төбесіндегі шеңбер"},
		{"koktem", "Көктем", "Көктемде дала жасыл болады"},
		{"test", "Тест деген не", "Тест — өзгерткенде не сынғанын айтады"},
	} {
		if _, err := db.Exec(
			`insert into articles (slug, title, body) values (?, ?, ?)`, a[0], a[1], a[2]); err != nil {
			log.Fatal(err)
		}
	}
}

Шығатыны:

"дала"       -> dala: [Дала] жазда сары, көктемде жасыл… | koktem: Көктемде [дала] жасыл болады
"ДАЛА"       -> dala: [Дала] жазда сары, көктемде жасыл… | koktem: Көктемде [дала] жасыл болады
"дал"        -> dala: [Дала] жазда сары, көктемде жасыл… | koktem: Көктемде [дала] жасыл болады
"дала жасыл" -> dala: [Дала] жазда сары, көктемде [жасыл]… | koktem: Көктемде [дала] [жасыл] болады
"дала 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' қызметтік пәрменін кірістіру түрінде рәсімделеді — әрі ескі мәндермен бірге, әйтпесе көрсеткіш нені алып тастау керегін таппайды. Түзету — өшіру мен кірістіру, сондықтан үшінші триггер екі әрекетті қатарынан істейді.

Триггерді ұмытсаңыз — іздеу өшірілген мақалаларды тауып, жаңаларын таппай қалады, әрі бұл үнсіз болады.

like емес, match

Іздеуге сұраныс 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.771 │
│ 5     │ -0.66  │
└───────┴────────┘

Сандар теріс, әрі аз болса — жақсы — сондықтан order by rank desc-сіз жазылады. Бірінші мақала екіншісінен күштірек келеді.

bm25 салмақ қабылдайды — әр бағанға біреуден:

order by bm25(articles_fts, 10.0, 1.0)

Тақырып денеден он есе маңызды, әрі айырма өседі: -1.151 пен -0.66. Іздеуден күтетіні де осы: ізделген сөз тақырыбында тұрған мақала бірінші болуға тиіс.

Реттеу бұзылды деп ойлауға оңай бір оғаштық: мақала өте аз болып, сөз солардың бәрінде дерлік болса, 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 функциялары