SQLite FTS5 Tam Metin Arama: Sorgu Sözdizimi, Türkçe ve Sıralama

FTS5 derlemenizde var mı

Belgelere göre FTS5, 3.9.0 sürümünden beri SQLite'ın birleşik kaynak dosyasına dahildir, ancak derleme sırasında SQLITE_ENABLE_FTS5 ile etkinleştirilmesi gerekir; ayrıca yüklenebilir eklenti olarak da derlenebilir. Kullandığınız aracın FTS5 içerip içermediğini SELECT name FROM pragma_module_list WHERE name = 'fts5'; sorgusuyla öğrenebilirsiniz; satır dönmüyorsa modül yoktur. Bu yazıdaki örnekler FTS5 içeren SQLite 3.45.3 ile çalıştırıldı; denediğimiz 3.50.6 komut satırı aracında FTS5 bulunmuyordu.

İlk tablo ve ilk arama

FTS5 tablosu oluştururken yalnızca sütun adlarını yazarsınız. Belgelere göre tip, kısıt veya PRIMARY KEY eklemek hatadır; her FTS5 tablosunun örtük bir rowid sütunu vardır. Arama üç eşdeğer biçimde yazılabilir: MATCH işleci, = işleci ya da tablo adını fonksiyon gibi kullanan tablo değerli fonksiyon sözdizimi.

CREATE VIRTUAL TABLE makale USING fts5(baslik, govde);
INSERT INTO makale(baslik, govde) VALUES
  ('SQLite ile tam metin arama', 'FTS5 modülü büyük metin koleksiyonlarında hızlı arama yapar.'),
  ('Şehir rehberi', 'İstanbul ve İzmir için gezi notları; şehir merkezinde yürüyüş.'),
  ('Yedekleme notları', 'Canlı veritabanını yedeklemek için VACUUM INTO kullanılabilir.'),
  ('Okuma önerileri', 'Bu ay okunan kitaplar ve okuma kulübü notları.'),
  ('Işık ve renk', 'Fotoğrafta ışık ayarı; örnekler e-posta ile gönderildi.');

SELECT rowid, baslik FROM makale WHERE makale MATCH 'notları' ORDER BY rank;
-- çıktı:
-- 3|Yedekleme notları
-- 4|Okuma önerileri
-- 2|Şehir rehberi

-- Aynı aramanın iki eşdeğer yazımı:
SELECT rowid FROM makale WHERE makale = 'vacuum';
-- çıktı: 3
SELECT rowid FROM makale('İZMİR');
-- çıktı: 2

rank gizli bir sütundur ve tam metin sorgularında varsayılan olarak bm25() puanını taşır. Sıralama yapmazsanız sonuç sırası belgelere göre belirsizdir. Son sorguya dikkat edin: büyük harfle yazılan İZMİR araması küçük harfli metni buldu. FTS5 aramaları varsayılan olarak büyük-küçük harfe duyarsızdır; bunun Türkçe harflerde nasıl çalıştığına aşağıda ayrıca bakacağız.

Sorgu sözdizimi

MATCH işlecinin sağındaki metin FTS5'in kendi sorgu dilidir. Tırnak içindeki dizi bir "ifade" (phrase) oluşturur ve kelimelerin o sırayla art arda gelmesini ister. Kelimenin sonuna eklenen yıldız önek araması yapar. AND, OR ve NOT işleçleri büyük harfle yazılmalıdır; aralarında işleç olmayan kelimeler örtük olarak AND ile bağlanır. Sütun adı ve iki nokta üst üste aramayı o sütunla sınırlar, başına eklenen eksi işareti ise o sütunu aramanın dışında bırakır. ^ ifadenin sütunun ilk belirteci olmasını, NEAR(a b, N) ise iki ifade arasında en fazla N belirteç bulunmasını ister.

SELECT group_concat(rowid) FROM makale WHERE makale MATCH '"tam metin"';     -- ifade
-- çıktı: 1
SELECT count(*) FROM makale WHERE makale MATCH '"metin tam"';                -- sıra önemli
-- çıktı: 0

SELECT count(*) FROM makale WHERE makale MATCH 'kitap';                      -- tam belirteç
-- çıktı: 0
SELECT group_concat(rowid) FROM makale WHERE makale MATCH 'kitap*';          -- önek
-- çıktı: 4

SELECT group_concat(rowid) FROM makale WHERE makale MATCH 'notları NOT okuma';
-- çıktı: 2,3
SELECT group_concat(rowid) FROM makale WHERE makale MATCH 'yedekleme OR gezi';
-- çıktı: 2,3

SELECT group_concat(rowid) FROM makale WHERE makale MATCH 'baslik : notları';    -- yalnız başlık
-- çıktı: 3
SELECT group_concat(rowid) FROM makale WHERE makale MATCH '- baslik : notları';  -- başlık hariç
-- çıktı: 2,4
SELECT group_concat(rowid) FROM makale WHERE makale MATCH '^şehir';              -- sütun başı
-- çıktı: 2

SELECT group_concat(rowid) FROM makale WHERE makale MATCH 'NEAR(canlı vacuum, 3)';
-- çıktı: 3
SELECT count(*) FROM makale WHERE makale MATCH 'NEAR(canlı vacuum, 2)';
-- çıktı: 0

İşleç önceliği belgelerde şöyle verilir: en sıkı NOT, sonra AND, en gevşek OR. Örtük AND ise hepsinden sıkı bağlanır. Bu yüzden a OR b c sorgusu a OR (b AND c) anlamına gelir. Karışık sorgularda parantez kullanmak hem okunabilirliği hem doğruluğu artırır. NEAR için N verilmezse varsayılan 10'dur; örnekteki "canlı" ile "vacuum" arasında üç belirteç bulunduğu için N=3 eşleşti, N=2 eşleşmedi.

Hangi karakterler tırnak ister

Belgelere göre tırnaksız bir kelime (bareword) yalnızca ASCII harfleri, rakamlar, alt çizgi ve ASCII dışındaki karakterlerden oluşabilir. Tire, nokta, iki nokta üst üste gibi karakterler içeren terimler tırnağa alınmalıdır. Tırnak unutulduğunda hata mesajı yanıltıcı olabilir: e-posta sorgusunda eksi işareti sütun filtresi olarak yorumlandı ve SQLite "posta" adlı bir sütun aradı.

SELECT count(*) FROM makale WHERE makale MATCH 'e-posta';
-- hata: no such column: posta
SELECT group_concat(rowid) FROM makale WHERE makale MATCH '"e-posta"';
-- çıktı: 5

SELECT count(*) FROM makale WHERE makale MATCH 'notları not okuma';   -- küçük "not" işleç değil
-- çıktı: 0

SELECT count(*) FROM makale WHERE makale MATCH 'arama AND';
-- hata: fts5: syntax error near ""

Kullanıcının arama kutusuna yazdığı metni doğrudan MATCH sağına koymak bu nedenle sorunludur: tek bir tire veya yarım kalmış bir işleç sorguyu hataya düşürür. Güvenli bir yol, metni kelimelere bölüp her birini çift tırnağa almak, içindeki çift tırnakları ikilemek ve sorguyu yine parametre olarak göndermektir. Parametreli sorgular için SQL injection önleme yazısına bakın.

Türkçe metinlerde harf ve aksan davranışı

Varsayılan belirteçleyici unicode61'dir. Belgelere göre harf ve rakamları belirteç, boşluk ve noktalama işaretlerini ayırıcı sayar; büyük-küçük harf dönüşümünü Unicode 6.1 kurallarına göre yapar ve varsayılan olarak Latin harflerindeki aksanları kaldırır. Türkçe için bunun iki önemli sonucu var. Birincisi, ş, ç, ğ, ö, ü harfleri aksansız karşılıklarıyla eşleşir: "modulu" araması "modülü" kelimesini bulur. İkincisi, noktalı ve noktasız i harfleri Türkçe kurallara göre değil, dilden bağımsız Unicode kurallarına göre dönüştürülür.

CREATE VIRTUAL TABLE t USING fts5(x);
INSERT INTO t VALUES ('Işık ve ışık'), ('İzmir ve izmir'), ('ISIK');

SELECT group_concat(rowid) FROM t WHERE t MATCH 'ışık';
-- çıktı: 1
SELECT group_concat(rowid) FROM t WHERE t MATCH 'isik';
-- çıktı: 3
SELECT group_concat(rowid) FROM t WHERE t MATCH 'İZMİR';
-- çıktı: 2

-- Dizine hangi belirteçlerin yazıldığını görmek için:
CREATE VIRTUAL TABLE t_v USING fts5vocab(t, row);
SELECT term, doc, cnt FROM t_v;
-- çıktı:
-- isik|1|1
-- isık|1|1
-- izmir|1|2
-- ve|2|2
-- ısık|1|1

fts5vocab sanal tablosu dizine yazılan belirteçleri gösterir ve sonucu açıkça ortaya koyar. Büyük I harfi Türkçedeki gibi ı harfine değil i harfine dönüştü; ı ise olduğu gibi kaldı. Böylece "Işık" kelimesi isık, "ışık" kelimesi ısık, "ISIK" ise isik olarak dizine girdi ve üçü birbirini bulmadı. İ harfi ise i harfine dönüştüğü için "İzmir" ve "izmir" aynı belirteci üretti.

Pratikte bunun anlamı şudur: kullanıcıların "ı" ve "I" harflerini tutarlı yazmadığı bir arama kutusunda "ışık" araması başlıklarda büyük harfle yazılmış "IŞIK" ya da "Işık" kelimelerini kaçırabilir. Basit bir çözüm, dizine eklemeden önce ve aramadan önce metni uygulamanızda aynı biçime getirmektir; örneğin hem saklanan metindeki hem sorgudaki ı harflerini i'ye çevirmek. Özel bir belirteçleyici yazmak da mümkündür, ancak bu C ile yapılır ve bu yazının kapsamı dışındadır. Türkçe harflerle büyük-küçük harf karşılaştırmasının genel sınırları SQLite COLLATE ve NOCASE yazısında anlatılıyor.

Aksanları korumak

"Şehir" ile "sehir" kelimelerinin ayrı tutulmasını istiyorsanız remove_diacritics 0 seçeneğini verin. Belgelere göre seçeneğin varsayılanı 1'dir; 2 değeri tek bir kod noktasında birden fazla aksan taşıyan nadir harflerde de aksanları doğru kaldırır.

CREATE VIRTUAL TABLE varsayilan USING fts5(x);
CREATE VIRTUAL TABLE ayrimli USING fts5(x, tokenize = 'unicode61 remove_diacritics 0');
INSERT INTO varsayilan VALUES ('şehir merkezi'), ('sehir merkezi');
INSERT INTO ayrimli VALUES ('şehir merkezi'), ('sehir merkezi');

SELECT group_concat(rowid) FROM varsayilan WHERE varsayilan MATCH 'şehir';
-- çıktı: 1,2
SELECT group_concat(rowid) FROM ayrimli WHERE ayrimli MATCH 'şehir';
-- çıktı: 1

Türkçe arama için çoğu durumda varsayılan davranış daha kullanışlıdır, çünkü kullanıcılar sıklıkla Türkçe klavye olmadan yazar. Ayrım kelimenin anlamını değiştiriyorsa ya da tam yazımla arama gerekiyorsa aksanları koruyan tabloyu seçin.

highlight() ve snippet()

Yardımcı (auxiliary) fonksiyonlar yalnızca tam metin sorgularında kullanılabilir ve ilk argüman olarak tablo adını alır. highlight() belirtilen sütunun tamamını eşleşmeler işaretlenmiş olarak döndürür; sütun numarası soldan sıfırdan başlar. snippet() ise sütundan kısa bir parça seçer; beşinci argüman parça başında ve sonunda gösterilecek metni, altıncı argüman en fazla belirteç sayısını verir ve belgelere göre 1 ile 64 arasında olmalıdır. Sütun numarası olarak negatif değer verilirse sütun otomatik seçilir.

SELECT highlight(makale, 1, '[', ']') FROM makale WHERE makale MATCH 'notları';
-- çıktı:
-- İstanbul ve İzmir için gezi [notları]; şehir merkezinde yürüyüş.
-- Canlı veritabanını yedeklemek için VACUUM INTO kullanılabilir.
-- Bu ay okunan kitaplar ve okuma kulübü [notları].

SELECT snippet(makale, 1, '<b>', '</b>', '...', 4) FROM makale WHERE makale MATCH 'vacuum';
-- çıktı: ...için <b>VACUUM</b> INTO kullanılabilir.

İkinci satırda işaret yok, çünkü o satırda "notları" kelimesi gövdede değil başlıkta geçiyor ve biz yalnızca gövdeyi (1 numaralı sütun) işaretledik. Fonksiyonlar metnin içine yalnızca verdiğiniz işaretleri ekler; çıktıyı bir web sayfasında gösterecekseniz satır içeriğindeki özel karakterleri kaçırma işini uygulamanızda yapmanız gerekir.

bm25 ile sıralama ve sütun ağırlıkları

bm25() fonksiyonu eşleşmenin ne kadar iyi olduğunu gösteren bir sayı döndürür. Belgelere göre FTS5 bu değeri -1 ile çarpar; böylece daha iyi eşleşme sayısal olarak daha küçük değer alır ve artan sıralama en iyi sonucu başa getirir. Formüldeki k1 ve b sabitleri 1,2 ve 0,75 olarak sabittir. Fonksiyona tablo adından sonra sayılar verirseniz bunlar sütun ağırlıkları olur; ilk sayı en soldaki sütuna uygulanır, eksik sütunlar 1,0 alır.

CREATE VIRTUAL TABLE yazi USING fts5(baslik, govde);
INSERT INTO yazi(baslik, govde) VALUES
  ('SQLite yedekleme', 'Yedek almak için backup komutunu kullanın.'),
  ('Veritabanı seçimi', 'Küçük projelerde sqlite, büyük ekiplerde sunucu veritabanı; sqlite dosya tabanlıdır, sqlite kurulum istemez.'),
  ('Python notları', 'Liste ve sözlük örnekleri.'),
  ('Linux ipuçları', 'Dosya izinleri ve kullanıcı grupları.'),
  ('Ağ ayarları', 'DNS ve IP yapılandırması.'),
  ('Yazılım testleri', 'Birim test ve entegrasyon testi.');

SELECT rowid, baslik, printf('%.4f', bm25(yazi)) FROM yazi WHERE yazi MATCH 'sqlite' ORDER BY rank;
-- çıktı:
-- 2|Veritabanı seçimi|-0.7832
-- 1|SQLite yedekleme|-0.5927

-- Başlıktaki bir eşleşme gövdedeki on eşleşme kadar değerli olsun:
SELECT rowid, baslik, printf('%.4f', bm25(yazi, 10.0, 1.0)) FROM yazi
 WHERE yazi MATCH 'sqlite' ORDER BY bm25(yazi, 10.0, 1.0);
-- çıktı:
-- 1|SQLite yedekleme|-1.1565
-- 2|Veritabanı seçimi|-0.7832

-- Aynı ağırlıkla, daha hızlı sıralanan rank sütunu:
SELECT rowid, baslik FROM yazi WHERE yazi MATCH 'sqlite' AND rank MATCH 'bm25(10.0, 1.0)' ORDER BY rank;
-- çıktı:
-- 1|SQLite yedekleme
-- 2|Veritabanı seçimi

Ağırlık vermediğimizde "sqlite" kelimesini gövdesinde üç kez geçiren satır öne çıktı. Başlığa 10 kat ağırlık verince başlığında kelime geçen satır birinci sıraya yükseldi. Belgeler, sıralama yaparken bm25() çağırmak yerine rank sütununu kullanmanın daha hızlı olduğunu, özellikle sorgu LIMIT içeriyorsa farkın belirginleştiğini belirtir. Ağırlıkları her sorguda yazmak istemiyorsanız INSERT INTO yazi(yazi, rank) VALUES ('rank', 'bm25(10.0, 1.0)'); ile tablonun varsayılanını kalıcı olarak değiştirebilirsiniz. bm25 değerlerini printf() ile yazdırmamızın nedeni ondalık sayıların metne çevrilme biçiminin 3.53.0 ile değişmiş olmasıdır.

IDF bileşeni bir kelimenin tablodaki satırların ne kadarında geçtiğine bağlıdır. Çok az satırlı bir tabloda, terim satırların yarısından fazlasında geçiyorsa bütün puanlar sıfıra çok yakın çıkar; bu bir hata değildir. Sıralamayı gerçekçi verilerle denemenizi öneririz.

Dış içerik tabloları ve tetikleyiciler

Normalde FTS5, dizinin yanında satırların bir kopyasını da saklar. Veriniz zaten sıradan bir tabloda duruyorsa bu kopya gereksiz yer kaplar. content= seçeneği FTS5 tablosunu "dış içerik" tablosu yapar: sütun değerleri gerektiğinde belirtilen tablodan okunur, content_rowid= ise eşleştirmede kullanılacak tamsayı anahtar sütununu verir. Belgeler dizini içerik tablosuyla tutarlı tutmanın kullanıcının sorumluluğunda olduğunu vurgular ve bunun için tetikleyicileri önerir. Aşağıdaki örnek belgelerdeki kalıbı Türkçe bir ürün tablosuna uygular:

CREATE TABLE urun(id INTEGER PRIMARY KEY, ad TEXT, aciklama TEXT, fiyat REAL);
INSERT INTO urun VALUES (1, 'Çelik termos', 'Sıcak ve soğuk içecekler için', 450),
                        (2, 'Cam demlik', 'Çay demlemek için ısıya dayanıklı cam', 320);

CREATE VIRTUAL TABLE urun_fts USING fts5(ad, aciklama, content='urun', content_rowid='id');
SELECT count(*) FROM urun_fts WHERE urun_fts MATCH 'cam';     -- dizin henüz boş
-- çıktı: 0
INSERT INTO urun_fts(urun_fts) VALUES ('rebuild');            -- mevcut satırları dizine al
SELECT count(*) FROM urun_fts WHERE urun_fts MATCH 'cam';
-- çıktı: 1

CREATE TRIGGER urun_ai AFTER INSERT ON urun BEGIN
  INSERT INTO urun_fts(rowid, ad, aciklama) VALUES (new.id, new.ad, new.aciklama);
END;
CREATE TRIGGER urun_ad AFTER DELETE ON urun BEGIN
  INSERT INTO urun_fts(urun_fts, rowid, ad, aciklama) VALUES ('delete', old.id, old.ad, old.aciklama);
END;
CREATE TRIGGER urun_au AFTER UPDATE ON urun BEGIN
  INSERT INTO urun_fts(urun_fts, rowid, ad, aciklama) VALUES ('delete', old.id, old.ad, old.aciklama);
  INSERT INTO urun_fts(rowid, ad, aciklama) VALUES (new.id, new.ad, new.aciklama);
END;

INSERT INTO urun VALUES (3, 'Cam sürahi', 'Su ve limonata için', 210);
UPDATE urun SET aciklama = 'Isıya dayanıklı borosilikat cam' WHERE id = 2;
DELETE FROM urun WHERE id = 1;

SELECT u.id, u.ad, u.fiyat
  FROM urun_fts JOIN urun AS u ON u.id = urun_fts.rowid
 WHERE urun_fts MATCH 'cam' ORDER BY rank;
-- çıktı:
-- 2|Cam demlik|320.0
-- 3|Cam sürahi|210.0
SELECT count(*) FROM urun_fts WHERE urun_fts MATCH 'soğuk';   -- silinen satır dizinden de çıktı
-- çıktı: 0

INSERT INTO urun_fts(urun_fts, rank) VALUES ('integrity-check', 1);
SELECT name FROM sqlite_schema WHERE name LIKE 'urun_fts%' ORDER BY name;
-- çıktı:
-- urun_fts
-- urun_fts_config
-- urun_fts_data
-- urun_fts_docsize
-- urun_fts_idx

İlk sorguda dizin boştu, çünkü dış içerik tablosu oluşturulduğunda mevcut satırlar kendiliğinden dizine alınmaz. Belgeler, tetikleyicileri sonradan eklemenin de mevcut satırları kopyalamadığını ve bu durumda rebuild komutunun dizini içerik tablosundan baştan kurduğunu belirtir. Silme ve güncellemede eski değerlerin 'delete' komutuyla dizine yeniden verilmesi gerekir, çünkü FTS5 hangi belirteçleri sileceğini bu değerlerden hesaplar. Değerler dizindekilerle uyuşmazsa dizinde artık belirteçler kalır ve sorgular tutarsız sonuç verir. Gölge tablolara bakarsanız _content tablosunun oluşmadığını görürsünüz; içerik yalnızca urun tablosunda durur.

Belgelerdeki bir başka ayrıntı: dış içerik tabloları REPLACE çakışma işlemeyi desteklemez ve REPLACE belirten işlemler ABORT gibi ele alınır. Değerleri hiç saklamayan "contentless" tablolar (content='') ise yalnızca rowid döndürebilir; 3.43.0 ile gelen contentless_delete=1 seçeneği bu tablolarda silmeyi ve tüm sütunları veren güncellemeyi de mümkün kılar. Tetikleyicilerin genel kullanımı SQLite trigger yazısında.

Trigram belirteçleyicisi ile alt dize araması

Varsayılan belirteçleyici kelime bazında çalışır; "bulv" araması "Bulvarı" kelimesini bulmaz. Trigram belirteçleyicisi ise metni üçer karakterlik dizilere böler ve bir satırın içindeki herhangi bir alt dizeyi aramayı mümkün kılar. Belgelere göre bu tablolar remove_diacritics açık değilse LIKE ve GLOB desenlerini de dizinle hızlandırır. Bu, adres, ürün kodu veya kelimelere ayrılması zor metinlerde kullanışlıdır.

CREATE VIRTUAL TABLE adres USING fts5(satir, tokenize = 'trigram');
INSERT INTO adres VALUES ('Atatürk Bulvarı No:12 Çankaya'),
                         ('Cumhuriyet Mahallesi 1453. Sokak'),
                         ('Kordonboyu Caddesi Konak');

SELECT group_concat(rowid) FROM adres WHERE adres MATCH 'bulv';      -- kelime ortası da olur
-- çıktı: 1
SELECT group_concat(rowid) FROM adres WHERE satir LIKE '%huriyet%';  -- dizinli LIKE
-- çıktı: 2
SELECT count(*) FROM adres WHERE adres MATCH '14';                   -- 3 karakterden kısa
-- çıktı: 0
SELECT count(*) FROM adres WHERE adres MATCH 'cankaya';
-- çıktı: 0

CREATE VIRTUAL TABLE adres2 USING fts5(satir, tokenize = 'trigram remove_diacritics 1');
INSERT INTO adres2 SELECT satir FROM adres;
SELECT group_concat(rowid) FROM adres2 WHERE adres2 MATCH 'cankaya';
-- çıktı: 1

Trigram tablolarının sınırlarını bilmek gerekir. Üç karakterden kısa alt dizeler tam metin sorgusunda hiçbir satırla eşleşmez; içinde en az bir joker olmayan dizi bulunmayan LIKE desenleri ise tablonun tamamının taranmasına yol açar. case_sensitive 1 ile büyük-küçük harfe duyarlı tablo oluşturulabilir, ancak belgelere göre bu durumda yalnızca GLOB dizinden yararlanır ve remove_diacritics 1 ile birlikte kullanılamaz. ESCAPE içeren LIKE desenleri de dizini kullanamaz. Dizin yalnızca LIKE ve GLOB için kullanılacaksa belgeler detail=none veya detail=column seçeneklerini dizin boyutunu küçültmek için denemeye değer bulur.

Diğer belirteçleyiciler ve tablo seçenekleri

Belgelerdeki dört yerleşik belirteçleyiciden ascii, ASCII dışındaki bütün karakterleri belirteç sayar ve yalnızca ASCII harflerde büyük-küçük harf dönüşümü yapar. porter ise başka bir belirteçleyicinin çıktısına İngilizce kök bulma algoritması uygular. Belgeler bu algoritmanın yalnızca İngilizce için tasarlandığını açıkça belirtir; Türkçe eklerde işe yaramaz. Türkçe metinde çekim eklerini yakalamak için önek araması (kitap*) en basit yoldur.

CREATE VIRTUAL TABLE p USING fts5(x, tokenize = 'porter');
INSERT INTO p VALUES ('connected devices'), ('bağlantılar kuruldu');
SELECT group_concat(rowid) FROM p WHERE p MATCH 'connection';   -- İngilizce kök bulma
-- çıktı: 1
SELECT count(*) FROM p WHERE p MATCH 'bağlantı';                 -- Türkçe ekleri tanımaz
-- çıktı: 0

CREATE VIRTUAL TABLE stok USING fts5(ad, kod UNINDEXED, prefix = '2 3');
INSERT INTO stok VALUES ('Sabun', 'SBN-01'), ('Şampuan', 'SMP-02');
SELECT count(*) FROM stok WHERE stok MATCH 'sbn';                -- dizine alınmayan sütun
-- çıktı: 0
SELECT ad, kod FROM stok WHERE stok MATCH 'şa*';
-- çıktı:
-- Sabun|SBN-01
-- Şampuan|SMP-02

CREATE VIRTUAL TABLE hatali USING fts5(ad TEXT NOT NULL);
-- hata: parse error in "ad TEXT NOT NULL"
CREATE VIRTUAL TABLE hatali2 USING fts5(rank);
-- hata: reserved fts5 column name: rank

UNINDEXED ile işaretlenen sütunlar saklanır ama dizine eklenmez; arama sonuçlarıyla birlikte göstermek istediğiniz kimlik veya kod gibi değerler için uygundur. prefix seçeneği belirtilen uzunluklar için ek önek dizinleri oluşturur ve ab*, abc* gibi kısa önek aramalarını hızlandırır; karşılığında dizin büyür. detail=column ve detail=none seçenekleri dizinden konum bilgisini çıkararak yer kazandırır, ancak ifade ve NEAR sorgularını, none durumunda sütun filtrelerini de devre dışı bırakır. FTS5'in sütun seçeneği olarak UNINDEXED dışında bir şey kabul etmediği ve rank ile rowid adlarının ayrılmış olduğu son iki hatada görülüyor.

Bakım komutları

FTS5 tablolarının bakım işlemleri tablo adıyla aynı adı taşıyan özel sütuna metin eklenerek yapılır. optimize dizini oluşturan bütün B-ağaçlarını tek yapıda birleştirir; belgeler bunun dizini en küçük ve sorgu için en hızlı hâle getirdiğini, ancak büyük tablolarda uzun sürebileceğini belirtir ve işi bölmek için merge komutunu önerir. integrity-check dizinin iç tutarlılığını, rank değeri 1 verildiğinde dış içerik tablosuyla uyumunu da denetler ve sorun bulursa hata verir. rebuild ise dizini silip içerikten yeniden kurar; contentless tablolarda kullanılamaz.

INSERT INTO makale(makale) VALUES ('optimize');          -- dizini tek B-ağacında birleştir
INSERT INTO makale(makale) VALUES ('integrity-check');   -- tutarsızlık varsa hata verir
INSERT INTO urun_fts(urun_fts) VALUES ('rebuild');        -- dış içerikten yeniden kur

Sıradan PRAGMA integrity_check 3.44.0 sürümünden beri FTS5 tablolarını da denetler. Silinen metnin izleri konusunda da bir uyarı var: belgelere göre PRAGMA secure_delete açık olsa bile FTS5 gölge tablolarında silinen içeriğin izleri kalabilir; FTS5'in kendi secure-delete seçeneği bu amaçla vardır. Dosya boyutu ve boş sayfalar için SQLite VACUUM yazısına bakın.

Sık yapılan hatalar

• Kullanıcı metnini doğrudan MATCH'e vermek: Tire, nokta veya yarım işleç sorguyu hataya düşürür; terimleri tırnaklayın.
• Küçük harfli işleç: not, and, or küçük harfle yazıldığında sıradan kelime olarak aranır.
• Dış içerik tablosunu doldurmamak: Mevcut satırlar kendiliğinden dizine girmez; rebuild çalıştırın.
• ı ve I farkını unutmak: unicode61, I harfini i'ye çevirir; Türkçe büyük harfli metinler küçük harfli ı aramalarını kaçırabilir.
• porter belirteçleyicisini Türkçe için kullanmak: Yalnızca İngilizce kökleri tanır.
• Tabloya tip veya kısıt yazmak: FTS5 sütun tanımında tip, NOT NULL veya PRIMARY KEY kabul etmez.

Sık Sorulan Sorular

FTS5 mi, LIKE mı?

LIKE '%kelime%' sıradan bir indeksten yararlanamaz ve tabloyu baştan sona tarar. FTS5 ise kelime dizini kullanır, sonuçları ilgiye göre sıralar ve eşleşmeleri işaretleyebilir. Küçük tablolarda fark hissedilmeyebilir; metin büyüdükçe FTS5 tercih edilir. Alt dize araması gerekiyorsa trigram belirteçleyicisine bakın.

FTS3 veya FTS4 kullanmalı mıyım?

Belgelerin ek bölümü FTS3/4 ile FTS5 arasındaki farkları ve taşıma adımlarını anlatır. Yeni projelerde FTS5 ile başlamanız daha mantıklıdır; eski FTS4 tablolarınız varsa taşımadan önce o bölümü okuyun.

Arama sonuçlarını sayfalayabilir miyim?

Evet. ORDER BY rank LIMIT 10 OFFSET 20 gibi sıradan sayfalama yan tümceleri çalışır ve belgelere göre rank ile sıralama özellikle LIMIT içeren sorgularda bm25() çağrısından daha hızlıdır.

FTS5 dizini ne kadar yer kaplar?

Metne ve seçeneklere bağlıdır. Belgelerdeki bir testte diskte 1636 MiB tutan bir e-posta koleksiyonunun FTS dizini detail=full ile 743 MiB, detail=column ile 340 MiB, detail=none ile 134 MiB tutmuştur. Satırların kopyasını ayrıca saklamamak için dış içerik veya contentless tablo seçebilirsiniz.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite LIKE, GLOB ve REGEXP · SQLite COLLATE ve NOCASE · SQLite trigger · SQLite metin fonksiyonları · SQLite R-Tree