Description
Introduction
I can't use prepared statements with SQLPage and SQLite's FTS5.
Here's my simplified schema:
CREATE TABLE memes(
id INTEGER PRIMARY KEY,
alt TEXT
);
CREATE VIRTUAL TABLE memes_fts USING fts5(
alt,
tokenize = 'unicode61 remove_diacritics 2',
content = memes,
content_rowid = id
);Triggers to maintain data in sync
CREATE TRIGGER memes_ai AFTER INSERT ON memes BEGIN
-- Insert
INSERT INTO memes_fts (rowid, alt)
VALUES (new.id, new.alt);
END;
CREATE TRIGGER memes_ad AFTER DELETE ON memes BEGIN
-- Delete
INSERT INTO memes_fts (memes_fts, rowid, alt)
VALUES ('delete', old.id, old.alt);
END;
CREATE TRIGGER memes_au AFTER UPDATE ON memes BEGIN
-- Delete
INSERT INTO memes_fts (memes_fts, rowid, alt)
VALUES ('delete', old.id, old.alt);
-- Insert
INSERT INTO memes_fts (rowid, alt)
VALUES (new.id, new.alt);
END;I can't run this parametrized query in SQLPage:
SELECT alt AS title
FROM memes_fts('"' || :s || '"');Which produces this error in SQLPage:
In "search.sql": The following error occurred while executing an SQL statement:
error returned from database: (code: 1) fts5: syntax error near ""
The SQL statement sent by SQLPage was:
SELECT alt AS title FROM memes_fts('"' || CAST(?1 AS TEXT) || '"');But I can successfully run this query, which is as close as possible, without dynamic parameters:
SELECT 'card' AS component, 'memes' AS title;
SELECT alt AS title
FROM memes_fts('"' || CAST('raccoons' AS TEXT) || '"');I tried running the same prepared statement in a Javascript/Deno context, to check if the problem was in SQLite's FTS5:
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("sqlpage/sqlpage.db");
const res2 = db.prepare(`
SELECT alt AS title
FROM memes_fts('"' || CAST(? AS TEXT) || '"');
`).all("raccoons");
console.log(res2);
db.close();But it does work as intended, so I guess SQLPage isn't telling everything about this prepared statement.
Another issue:
Before trying this ... FROM table(query), I tried using ... FROM table WHERE table MATCH query, which SQLPage couldn't parse.
Are there some examples of successful uses of FTS5 with SQLPage?
In the source code, the only instance of 'FTS5' I could find is from the 0.8.0 changelog, which seems to indicate that the extension must be loaded with the sqlite_extensions config. The extension is already bundled in the SQLite version used by SQLPage;
I'll gladly propose my code as an example when I'll have something successful!
Version information
- OS: Linux
- Database: SQLite
- SQLPage Version: v0.34.0