Stack Overflow
Stack Overflow

Questions tagged [fts4]

FTS3 and FTS4 are an SQLite virtual table modules that allows users to perform full-text searches on a set of documents.

The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified.

103 questions
16
votes
1 answer

how can I get faster FTS4 query results ordered by a field in another table?

Background I'm implementing full-text search over a body of email messages stored in SQLite, making use of its fantastic built-in FTS4 engine. I'm getting some rather poor query performance, although not exactly where I would expect. Let's take a…
chazomaticus
  • 15,476
  • 4
  • 30
  • 31
13
votes
1 answer

Android Room database query does not return id column

The problem is that the query returns all columns except 'id' I use fts4 and in docs it says: FTS-enabled tables always use a primary key of type INTEGER and with the column name "rowid". If your FTS-table-backed entity defines a primary key,…
Arsham
  • 161
  • 2
  • 9
13
votes
4 answers

How to setup FTS3/FTS4 with python2.7 on Windows

FTS3/FTS4 doesn't work in python by default (up to 2.7). I get the error: sqlite3.OperationalError: no such module: fts3 or sqlite3.OperationalError: no such module: fts4 How can this be resolved?
Naveen
  • 5,910
  • 5
  • 30
  • 38
12
votes
1 answer

SQLite External Content FTS requires rebuild every time?

I set up an external content FTS4 virtual table in my app to allow full text search of an existing database. I also set up triggers similar to the documentation, so when my main content table is updated the FTS table gets new entries as well. CREATE…
Jason Clardy
  • 153
  • 6
10
votes
2 answers

How to escape string for SQLite FTS query

I'm trying to perform a SQLite FTS query with untrusted user input. I do not want to give the user access to the query syntax, that is they will not be able to perform a match query like foo OR bar AND cats. If they tried to query with that string I…
wxs
  • 5,617
  • 5
  • 36
  • 51
9
votes
2 answers

Searching in multiple columns using Full Text Search(FTS) with multiple tokens using OR operator

I am using FTS for querying my database for increasing the search speed as I need to search in text description also, When I am trying to query using single column its working fine like below select * from productsearch where productsearch match…
Lalit Poptani
  • 67,150
  • 23
  • 161
  • 242
8
votes
1 answer

iOS SQLite full text search example

I am trying to build an app that utilizes SQLite FTS. I found a very interesting post on how to do this just the way I want to. However, I am not a very experienced programmer at all and especially not in CoreData which is used in this post. Is…
infobug
  • 313
  • 5
  • 17
7
votes
2 answers

Do SQLite FTS tables need to be manually populated?

The documentation for SQLite FTS implies that FTS tables should be populated and updated using INSERT, UPDATE, DELETE, etc. That's what I was doing - adding rows, deleting them, etc., but recently I've noticed that as soon as I create the FTS table,…
laurent
  • 88,262
  • 77
  • 290
  • 428
6
votes
1 answer

Exact phrase first before anything else in SQLite FTS?

Suppose the search input is 'what is'. This will match 'whatever it is' and also 'what is' which is the exact phrase. Now, how do I make the exact phrase first in the sorting? I've been thinking about this since yesterday and I keep on coming up…
Sid Go
  • 2,041
  • 3
  • 16
  • 29
5
votes
0 answers

React Native SQLite Full Text Search

I'm trying to implement full-text search with SQLite in React Native. I'm using react-native-sqlite-storage. Android doesn't support FTS5. But, I've been able to create a virtual table and run queries using FTS4. The problem is ranking the search…
ataravati
  • 8,891
  • 9
  • 57
  • 89
5
votes
3 answers

SQLite FTS example doesn't work

I've downloaded the latest SQLite 3.7.15.2 shell (Win32) and tried to execute one of the FTS examples exactly as it is written at http://sqlite.org/fts3.html#section_3 -- Virtual table declaration CREATE VIRTUAL TABLE docs USING fts3(); -- Virtual…
Stan Lagun
  • 53
  • 1
  • 4
5
votes
1 answer

SQLite FTS4 search with special characters

I have an Android app which searches for data in an SQLite database with FTS4 virtual tables. It works fine, but when the data inside the tables contain special characters (like 'á', 'é', 'í', 'ó', 'ú' or 'ñ') the SQLite MATCH function gives no…
user1387981
  • 133
  • 1
  • 7
4
votes
0 answers

Sqlite Query to MATCH Multiple Words in FTS4 Table Android

I am trying to do this query in android FTS4 table and this works perfectly: SELECT * from table WHERE table MATCH 'description: paint* OR alias: paint*' I need to match multiple words in multiple columns like this: SELECT * from table WHERE table…
Aayush Thakur
  • 634
  • 1
  • 10
  • 22
4
votes
1 answer

How to query an external content FTS4 table but return additional columns from the original content table

I am creating an FTS4 external content table in SQLite like this: CREATE TABLE t2(id INTEGER PRIMARY KEY, col_a, col_b, col_text); CREATE VIRTUAL TABLE fts_table USING fts4(content="t2", col_text); I'm using an external content table so that I…
Suragch
  • 484,302
  • 314
  • 1,365
  • 1,393
3
votes
1 answer

System.Data.Sqlite and FTS4

Why when i write a query with fulltext Search syntax like: SELECT * FROM TABLENAME WHERE TABLENAME MATCH 'ColumnA:word1 OR ColumnB:word2' The query result always return 0 rows? I'm using VBnet and the latest Ado.net provider from sqlite.org The…
popa big
  • 31
  • 1
  • 2
1
2 3 4 5 6 7