Agora Metin2 1
Agora Metin2
berzahx 1
berzahx
Aliyldrim 2
Aliyldrim
-ECD- 1
-ECD-
Reklam vermek için turkmmo@gmail.com

SQLite Full-Text Search: FTS5 ve MATCH Kullanımı

image1b4bbda66acb6973.jpg


LIKE Operatörünün Yetersizliği

SQLite üzerinde metin araması yaparken çoğunlukla LIKE '%kelime%' yapısını kullanmışsınızdır. Bu yöntem küçük veri setlerinde sorunsuz çalışsa da, büyük tablolarda ciddi performans kayıplarına yol açar. Bunun temel sebebi, SQLite'ın bu sorgu sırasında bir indeks kullanamaması ve her satırı tek tek okuyup büyük/küçük harf ayrımına dikkat ederek tarama yapmasıdır. Ayrıca çok kelimeli sorgular, kelime sınırları, sıralama mantığı ve ön ek eşleşmesi gibi işlemleri manuel olarak yönetmeniz gerekir.

İşte bu noktada SQLite'ın sunduğu güçlü bir çözüm olan FTS5 karşımıza çıkar. FTS5, metin içeren sütunlarınızı ters dizin (inverted index) yapısıyla yöneten, kendine has bir sorgu dili olan ve sonuçları BM25 algoritmasıyla sıralayan özel bir sanal tablodur. Herhangi bir harici kütüphane gerektirmez, doğrudan SQLite ile bütünleşik gelir.

FTS5 Sanal Tablo Oluşturma

FTS5 tablosu oluşturmak için CREATE VIRTUAL TABLE ... USING fts5(...) söz dizimi kullanılır. Burada indekslenmesini istediğiniz sütunları belirtmeniz yeterlidir:

SQL:
CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite tam metin araması', 'FTS5 hızlıdır ve dahili olarak gelir.'),
  ('SQLite''te indeksler', 'B-ağaçları SQLite''teki çoğu aramayı destekler.'),
  ('Neden SQLite kullanmalı', 'Süreç içinde çalışır ve verileri tek bir dosyada saklar.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Burada dikkat etmeniz gereken birkaç teknik detay mevcut: Sütunlar için veri tipi belirtilmez çünkü FTS5 tüm verileri metin olarak işler. MATCH operatörü belirli bir sütun üzerinde değil, doğrudan tablo adı üzerinde çalışır (posts MATCH ...). Ayrıca aramalar tokenize edildiği ve büyük/küçük harf duyarlılığı bulunmadığı için 'sqlite' sorgusu, tablodaki SQLite kayıtlarını da kolaylıkla bulur.

MATCH Sorgulama Dili

MATCH ile yapılan aramalar tek bir kelimeyle sınırlı kalmaz; gelişmiş bir gramer yapısını destekler:

SQL:
CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Tam metin araması', 'FTS5 ifade ve önek sorgularını destekler.'),
  ('İndeksler', 'B-ağacı indeksi eşitlik aramalarını hızlandırır.'),
  ('Tetikleyiciler', 'FTS''i kaynak tabloyla senkronize tutmak için tetikleyicileri kullanın.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Sorgulama yaparken kullanabileceğiniz bazı yöntemler şunlardır:

• 'fts5 AND prefix' - Belirtilen her iki kelimenin de, satırın neresinde olduğuna bakılmaksızın bulunmasını sağlar.
• '"keep fts"' - İfadelerin tam olarak bu sırada ve yan yana geçmesini arar.
• 'trig*' - Önek araması gerçekleştirir; trigger, trigonometry veya triggers gibi terimlerle eşleşir.
• 'index NOT trigger' - index kelimesini içeren ancak trigger içermeyen satırları getirir.

Eğer spesifik bir sütunda arama yapmak isterseniz column:term formatını kullanabilirsiniz; örneğin 'title:sqlite'. Ayrıca parantez kullanımı ve OR operatörü ile çok daha karmaşık arama mantıkları oluşturabilirsiniz.

BM25 ile Sıralama Mantığı

FTS5, her arama sonucuna rank adında gizli bir sütun ekler. Bu sütun, BM25 skorunu (alaka düzeyi) temsil eder. Değer ne kadar küçükse, sonuç sorguyla o kadar örtüşüyor demektir. Sorgu sonucunu en alakalıdan başlayarak listelemek için bu sütunu kullanabilirsiniz:

SQL:
CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite eğitimi', 'SQLite''i sıfırdan öğrenin.'),
  ('SQLite ve Postgres', 'Küçük uygulamalar için SQLite ile Postgres karşılaştırması.'),
  ('SQLite SQLite SQLite', 'SQLite''i defalarca anan bir gönderi.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Belirli sütunlara daha fazla önem vermek isterseniz, bm25() fonksiyonunu özelleştirebilirsiniz:

SQL:
CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite dizinleri', 'B-ağaçları hakkında kısa bir not.'),
  ('Postgres notları', 'Karşılaştırma için sqlite hakkında bir paragraf içerir.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Yukarıdaki örnekte, sqlite terimi title alanında geçtiğinde 10 kat daha değerli kabul edilecektir.

FTS5 ve Veri Senkronizasyonu

FTS5 tabloları, metinlerin bir kopyasını kendi içinde saklayabilir. Ancak verinin asıl kaynağı başka bir tabloysa, "harici içerik" (external content) yöntemini ve tetikleyicileri (trigger) kullanmak en verimli yoldur.

SQL:
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

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;

INSERT INTO articles (title, body) VALUES
  ('SQLite tetikleyicileri', 'AFTER INSERT tetikleyicileri nasıl kullanılır.'),
  ('FTS5 desenleri', 'Harici içerik, veriyi tek bir yerde tutar.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' tanımı, FTS5'e metin verisini kendi içinde tutmak yerine gerektiğinde articles tablosuna başvurmasını söyler. Tetikleyiciler ise ana tabloda yapılan değişikliklerin FTS dizinine anında aktarılmasını sağlar. Buradaki INSERT satırı, dizine veri silme komutu göndermek için kullanılan özel bir FTS5 söz dizimidir.

Sonuçların Önizlenmesi

Arama sonuçlarını kullanıcıya gösterirken, eşleşen kelimelerin vurgulanması veya kısa özetlerin sunulması için iki temel fonksiyon mevcuttur:

SQL:
CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Başlarken', 'SQLite, verileri diskte tek bir dosyada saklayan küçük, hızlı, gömülü bir veritabanı motorudur.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite veritabanı';

• highlight(tablo, sütun_indeksi, aç, kapat): İlgili sütundaki tam metni döndürür ve eşleşen terimleri belirlediğiniz etiketler arasında (örn. <b> ve </b>) gösterir.
• snippet(tablo, sütun_indeksi, aç, kapat, üç_nokta, token_sayısı): Eşleşmenin geçtiği metin kısmından daha kısa bir alıntı üretir.

Önemli İpuçları ve Kısıtlamalar

• MATCH operatörü sadece FTS tablolarında geçerlidir; standart tablolarda kullanılamaz.
• Sıralama yaparken rank sütununu dahil etmeyi unutmayın, aksi takdirde sonuçlar sadece depolanma sırasına göre gelir.
• unicode61 varsayılan tokenizer'dır; ancak kök bulma (stemming) özelliği için tokenize='porter' seçeneğini kullanabilirsiniz.
• FTS5 "bulanık arama" yapmaz; yalnızca önek tabanlı veya tam eşleşme sağlar.
• content='' (içeriksiz) tablolar disk alanından tasarruf sağlar ancak orijinal veriye ihtiyaç duyduğunuzda bunu FTS tablosundan geri çağıramazsınız.

Sırada: Pencere Fonksiyonları (Window Functions)

FTS5 ile metin tabanlı gelişmiş aramaları çözdük. Bir sonraki derste, verileri gruplandırmadan toplamlar ve sıralamalar yapmanıza olanak tanıyan "pencere fonksiyonları" konusuna değineceğiz.
 

Şu an konuyu görüntüleyenler (Toplam : 0, Üye: 0, Misafir: 0)

Geri
Üst