CODEWITHSUNDEEP
sqlitesql-viewfts3
glittershark
glittershark

Reputation: 544

SQLite View with functions?

So let's say I have an FTS[34] table data, and a regular table info, that are bound together by a info.ID = data.rowid. I have a view master that represents this join, that I use to query overall information. Here are the CREATE TABLE statements:

CREATE VIRTUAL TABLE data USING fts4(
    Name,
    Keywords,
    Aliases
    Description);

CREATE TABLE info (
    ID INTEGER PRIMARY KEY,
    -- A bunch of other columns...
    );

CREATE VIEW master AS SELECT
    info.ID AS ID
    data.Name AS Name,
    data.Keywords AS Keywords,
    data.Aliases AS Aliases,
    data.Description AS Description,
    -- A bunch of other columns...
FROM info JOIN data ON info.ID = data.rowid;

Now what I want to do is create another view that instead of selecting the entire cell from the data table, selects the result of the SQLite snippet function as performed on each column of the data table:

CREATE VIEW search AS SELECT
    master.*,
    snippet(data '<b>', '</b>', '...', 0) AS sn_Name,
    snippet(data '<b>', '</b>', '...', 1) AS sn_Keywords,
    snippet(data '<b>', '</b>', '...', 2) AS sn_Aliases,
    snippet(data '<b>', '</b>', '...', 3) AS sn_Description
FROM master JOIN data ON master.ID = data.rowid;

However, when I execute that statement from within Python I get the following:

sqlite3.OperationalError: near "'<b>'": syntax error

Upvotes: 0

Views: 313

Answers (1)

CL.
CL.

Reputation: 180070

The comma between the data and '<b>' parameter values is missing.

Upvotes: 1

Related Questions