rsqlitefts3fts4

Can I use the FTS extension for sqlite from within R`?

Sqlite comes with support for full text special tables. Is it possible to use these features from within R, e.g. from RSQLite?

Solution

  • Found the answer by try-and-error:

    library(RSQLite)
dbGetQuery(con, "pragma compile_options;")

    This shows "ENABLE_FTS3" and "ENABLE_FTS5". So it seems the extensions are activated by default.

    Small test example:

    con <- dbConnect(SQLite(), ":memory:")
dbExecute(con, "CREATE VIRTUAL TABLE mail USING fts3(subject, body);")
dbExecute(con, "INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');")
dbExecute(con, "INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');")
dbExecute(con, "INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');")
dbGetQuery(con, "SELECT * FROM mail WHERE subject MATCH 'software';")

    The last query returns the rows 1 and 2.

    Nice!