aardio 范例: SQLite - FTS5 + BM25 全文搜索

参考 SQLite 全文搜索 | 参考标准库 string.bm25

//SQLite - FTS5 + BM25 全文搜索
//参考 SQLite 全文搜索: https://www.aardio.com/zh-cn/doc/example/Database/sqlite/fts.html
//参考标准库 string.bm25: https://www.aardio.com/zh-cn/doc/library-reference/string/bm25.md5

import console;
import sqlite;

//创建内存数据库
var db = sqlite(":memory:");

//创建 FTS5 虚拟表（全文搜索表）
db.exec(`
    CREATE VIRTUAL TABLE articles USING fts5(
        title,
        content,
        tokenize = 'unicode61'
    );
`);

//插入测试数据
var articles = {
    { title = "aardio 编程入门"; content = "aardio 是适合快速开发 Windows 桌面应用程序的编程语言。" };
    { title = "SQLite 数据库教程"; content = "SQLite 是一个轻量级的嵌入式数据库，支持全文搜索 FTS5 扩展。" };
    { title = "全文搜索技术"; content = "FTS5 是 SQLite 的全文搜索引擎，支持 BM25 排序算法。" };
    { title = "aardio 与数据库"; content = "aardio 可以方便地操作 SQLite 数据库，进行增删改查操作。" };
    { title = "编程语言比较"; content = "不同的编程语言有不同的特点，选择合适的语言很重要。" };
    { title = "Windows 应用开发"; content = "使用 aardio 开发 Windows 应用程序非常简单高效。" };
}

var cmd = db.prepare("INSERT INTO articles(title, content) VALUES(@title, @content)");
for(i, article in articles) {
    cmd.step(article);
}
cmd.finalize();

console.log("=== FTS5 + BM25 全文搜索演示 ===");
console.log("SQLite 版本:", sqlite.version());
console.log();

//基本全文搜索
console.log("【搜索: aardio】");
console.log("-------------------------------------------------");
for title, content in db.each(`
    SELECT title, content FROM articles WHERE articles MATCH 'aardio'
`) {
    console.log("标题:", title);
    console.log("内容:", content);
    console.log();
}

//使用 BM25 排序（分数越小越相关，负数）
console.log("【搜索: aardio OR SQLite（按 BM25 相关度排序）】");
console.log("-------------------------------------------------");
for title, content, score in db.each(`
    SELECT title, content, bm25(articles) as score 
    FROM articles 
    WHERE articles MATCH 'aardio OR SQLite'
    ORDER BY score
`) {
    console.log("标题:", title, " | 相关度:", score);
    console.log("内容:", content);
    console.log();
}

//短语搜索
console.log("【短语搜索: 全文搜索】");
console.log("-------------------------------------------------");
var result = db.getTable(`
    SELECT title, content FROM articles WHERE articles MATCH '"aardio"'
`);
console.dumpJson(result);

//使用 highlight() 标记匹配内容
console.log("【标记: 数据库】");
console.log("-------------------------------------------------");
for title, highlighted in db.each(`
    SELECT highlight(articles, 0, '→', '←') as title,
           highlight(articles, 1, '→', '←') as highlighted
    FROM articles 
    WHERE articles MATCH '数据库'
`) {
    console.log("标题:", title);
    console.log("标记:", highlighted);
    console.log();
}

//使用 snippet() 显示摘要
console.log("【搜索摘要: 编程】");
console.log("-------------------------------------------------");
for title, snippet, score in db.each(`
    SELECT title, 
           snippet(articles, 1, '[', ']', '...', 15) as snippet,
           bm25(articles) as score
    FROM articles 
    WHERE articles MATCH '编程'
    ORDER BY score
`) {
    console.log("标题:", title, " | BM25:", score);
    console.log("摘要:", snippet);
    console.log();
}

//参数化搜索
console.log("【参数化搜索】");
console.log("-------------------------------------------------");
var keyword = "Windows";
var results = db.getTable(`
    SELECT title, content, bm25(articles) as score 
    FROM articles 
    WHERE articles MATCH @keyword
    ORDER BY score
`, { keyword = keyword });
console.dumpJson(results);

db.close();
console.pause();