Persian search with SQLite FTS5 and Python

import sqlite3

def normalize(text):
    return " ".join(text.replace("ي", "ی").replace("ك", "ک")
                    .replace("\u200c", " ").split())

def phrase(text):
    return '"' + text.replace('"', '""') + '"'

db = sqlite3.connect(":memory:")
db.execute("CREATE VIRTUAL TABLE docs USING fts5(title, body)")
db.executemany("INSERT INTO docs VALUES (?, ?)", [
    ("پرداخت", normalize("راهنمای پرداخت اینترنتی")),
    ("ارسال", normalize("پیگیری ارسال سفارش")),
])
query = normalize("پرداخت")
if query:
    rows = db.execute(
        "SELECT title FROM docs WHERE docs MATCH ? "
        "ORDER BY bm25(docs) LIMIT 5", (phrase(query),)
    ).fetchall()
    print(rows)

Expect only the payment title. This example does not implement authorization or external-content synchronization. A no such module: fts5 error means this runtime lacks the required SQLite extension.

Run in an empty FTS5-enabled test database. An external-content index does not synchronize itself merely because the source table exists. Inserts, updates and deletes must maintain it.

CREATE TABLE source_docs(id INTEGER PRIMARY KEY, title TEXT, body TEXT);
CREATE VIRTUAL TABLE search_docs USING fts5(
    title, body, content='source_docs', content_rowid='id'
);
CREATE TRIGGER docs_insert AFTER INSERT ON source_docs BEGIN
    INSERT INTO search_docs(rowid,title,body)
    VALUES(new.id,new.title,new.body);
END;
CREATE TRIGGER docs_delete AFTER DELETE ON source_docs BEGIN
    INSERT INTO search_docs(search_docs,rowid,title,body)
    VALUES('delete',old.id,old.title,old.body);
END;
CREATE TRIGGER docs_update AFTER UPDATE ON source_docs BEGIN
    INSERT INTO search_docs(search_docs,rowid,title,body)
    VALUES('delete',old.id,old.title,old.body);
    INSERT INTO search_docs(rowid,title,body)
    VALUES(new.id,new.title,new.body);
END;
INSERT INTO source_docs VALUES(1,'Payment guide','refund policy');
SELECT rowid,title FROM search_docs WHERE search_docs MATCH 'refund';
UPDATE source_docs SET body='delivery policy' WHERE id=1;
SELECT count(*) FROM search_docs WHERE search_docs MATCH 'refund';

The initial query returns row 1; the final count must be zero after the update. Existing source data requires an index rebuild after setup. The cover illustrates an older SQLite FTS structure; it is not an FTS5 benchmark or exact on-disk layout.

This standalone example creates its own in-memory database. A bounded limit and explicit phrase quoting define a simple search box rather than an advanced query-language interface.

import sqlite3

def normalize(value):
    return " ".join(value.replace("ي", "ی").replace("ك", "ک")
                    .replace("\u200c", " ").split())

def search(db, user_query):
    query = normalize(user_query)
    if not query:
        return []
    phrase = '"' + query.replace('"', '""') + '"'
    return db.execute(
        "SELECT title, body FROM docs WHERE docs MATCH ? "
        "ORDER BY bm25(docs) LIMIT ?", (phrase, 5)
    ).fetchall()

db = sqlite3.connect(":memory:")
db.execute("CREATE VIRTUAL TABLE docs USING fts5(title,body)")
db.execute("INSERT INTO docs VALUES(?,?)",
           ("پرداخت", normalize("راهنمای پرداخت اینترنتی")))
assert search(db, "  ") == []
assert search(db, "پرداخت")[0][0] == "پرداخت"
assert search(db, 'a "quoted" phrase') == []
print("Search checks passed")
db.close()

Expect Search checks passed. A real API should bound query length and result count on the server, and obtain tenant identity from verified authentication rather than a user-editable parameter.

Correct retrieval also needs safe display. This example inserts title and body as text and treats retrieved content as untrusted even when it comes from the application’s own database.

from html import escape

def normalize(value):
    return " ".join(value.replace("ي", "ی").replace("ك", "ک")
                    .replace("\u200c", " ").split())

def result_card(title, body):
    return "<article><h3>" + escape(title) + "</h3><p>" + escape(body) + "</p></article>"

assert normalize("كتاب") == normalize("کتاب")
assert normalize("می\u200cرود") == normalize("می رود")
markup = result_card("<script>unsafe</script>", "راهنمای پرداخت")
assert "<script>" not in markup
assert "&lt;script&gt;" in markup
print(markup)

The first assertions document normalization and the next ones verify that document markup stays text. For highlighting, escape content before adding application-controlled markers; do not pass raw highlight output directly to innerHTML.