All Questions
Tagged with full-text-search sqlite
268 questions
-1
votes
1
answer
66
views
SQLite slow combined query from two fast SELECT queries
Select join query with FTS table is slow if other WHERE conditions are specified. But these WHERE conditions without fts MATCH condition are not slow and fts MATCH is not slow alone. How to combine ...
- 71
0
votes
0
answers
48
views
Add an existing SQLite database to a React Native project
I am currently developing a React Native project and I need to incorporate an already populated SQLite3 database into the project. I intend to utilize SQLite FTS5, and after some research, I have ...
1
vote
0
answers
60
views
SQLite is stubborn on using the suboptimal row ID index
Let's say I have a following database structure:
CREATE TABLE "posts" (
"id" INTEGER PRIMARY KEY NOT NULL,
"isDeleted" INTEGER,
"isPublished" INTEGER,
&...
0
votes
1
answer
65
views
SQLite FTS5 Table search inconsistency
I have a TRANSACTIONS Table which was created with the following query:
CREATE TABLE TRANSACTIONS (ID INTEGER PRIMARY KEY, DESC VARCHAR(255), COMMENT VARCHAR(1024), INVOICE VARCHAR(255));
Then I ...
- 495
6
votes
1
answer
950
views
"unsafe use of virtual table" when creating triggers for sqlite full text search (fts)
I am attempting to make use of SQLite's full text search (FTS) extension in an external content table by following the official documentation. I am running sqlite version 3.42.0 from the Arch linux ...
- 523
0
votes
1
answer
230
views
sqlite fts 4 trigger didn't update index
I face an index issue with a virtual table from sqlite using FTS 4.
I try to optimise the index speed of my virtual table.
This virtual table take its content from a real table. Previously, to refresh ...
- 1,703
0
votes
1
answer
327
views
SQLite FTS5 triggers to index subset of rows
My goal is a sort of wiki-like notebook using SQLite as the data store. The idea is that there is one big pages table where every revision of every page gets stored. Every page has only three ...
- 113
0
votes
1
answer
69
views
Why does `ON (fts.text MATCH 'word' AND fts.id = item.id)` and `...WHERE fts.text MATCH 'word'` have the same query plan?
I have the typical small sqlite database with 3 tables, one for items (which are manga, and have ids), another for tags (|id|name|), and another with the associations between them (|tag_id| manga_id|) ...
- 95
0
votes
1
answer
562
views
How to load Trigram tokenizer for sqlite using EF core?
I am using Microsoft.EntityFrameworkCore 3.1.8 with my UWP app.
I need to run this sqlite query using Entity framework core and Code first approach:
CREATE VIRTUAL TABLE TrialFTS USING fts5(search, ...
- 61
1
vote
1
answer
2k
views
How to search a substring using FTS5 in SQLite
Suppose I have created a virtual table using FTS5 module in SQLite 3.35 like this:
CREATE VIRTUAL TABLE TrialFTS USING fts5(search);
I have prepopulated the table with data.
Now to search any row ...
- 61
0
votes
1
answer
415
views
Lossless SQLite FTS5 search of a substring
Using a FTS5 virtual table returns nothing for postfix searches.
It only can search for the entire word tokens, or for the prefixes of the word tokens if I append * to the search.
For example, it does ...
- 3,193
-2
votes
1
answer
140
views
SQLite FTS search if there's a word that contains string
I want to select all rows that contains an string (for example abc)
example:
abc
abcd
0abc
0abcd
I want all the above to be returned.
My first approach was:
SELECT * FROM notes WHERE notes MATCH '...
user15249905
1
vote
0
answers
670
views
How to create a Full Text Search (FTS) virtual table from existing table without explicit column name?
This question describes how to create a FTS table (tabel2) from existing table (table1) by explicitly providing column names which boils down to:
CREATE VIRTUAL TABLE table2 USING FTS5(col1, col2);
...
- 2,191
6
votes
2
answers
2k
views
Compressed Sqlite database and indexing
I read that it is possible to compress sqlite databases with extensions like sqlite-zstd.
In Sqlite3, are there methods, ready to use in Python, that allow both:
compression of a text column (let's ...
- 46k
0
votes
1
answer
422
views
SQLite with full-text index and B-tree index on another column
I have a table like this:
id | status | message
1 | 200 | Some long text
2 | 400 | Other text
Sometimes I need to find a row based on the id, sometimes based on the status (including range ...
- 35.3k
2
votes
1
answer
548
views
How to search for a single value in multiple columns?
I'm implementing a search-bar for my program that will query a database based on user input. When the user enters a single search term with spaces, example: I am searching for something, the program ...
user19610681
4
votes
1
answer
1k
views
How do I use the snippet() function using a FTS5 virtual table in SQLite?
In the SQLite documentation for FTS5 it is claimed that 5 parameters must be passed to the snippet function:
The snippet() function is similar to highlight(), except that
instead of returning entire ...
- 1,072
1
vote
1
answer
373
views
SQLite FTS5 SELECT doesn't MATCH Ukrainian 'Усмiшки' with search string 'усмi*', but with 'усм*' it does
I created FTS5 virtual table in SQLite 3 and inserted many titles in it
CREATE VIRTUAL TABLE books_fts USING fts5(title, content='');
When I try to do Full Text Search with
SELECT rowid FROM books_fts ...
- 21
0
votes
1
answer
101
views
Consecutive JOIN and aliases: order of execution
I am trying to use FULLTEXT search as a preliminary filter before fetching data from another table. Consecutive JOINs follow to further refine the query and to mix-and-match rows (in reality there are ...
- 28.4k
0
votes
1
answer
65
views
Use two columns for FTS4 from sqlite3
I have a table with two columns (ldap, name). I want to be able to full text search any of those columns with the library FTS4. Here I have a couple of statements I'm using to create the virtual table ...
- 127
1
vote
2
answers
2k
views
sqlite3 fts5 contentless or content=external table, how store and read a non-FTS column value
I am working with an SQLite3 database. And I want to create an FTS5 content-less (content="") or content="external table" table to save space and prevent duplication of data (which ...
- 71
1
vote
1
answer
189
views
How to modify languageid column in a SQLite FTS table?
In SQLite FTS tables there is a hidden languageid column that I want to make use of: I need to populate my FTS4 table with rows in two different languages, which I will then distinguish by languageid ...
- 2,115
3
votes
1
answer
5k
views
Populate virtual SQLite FTS5 (full text search) table from content table
I've followed https://kimsereylam.com/sqlite/2020/03/06/full-text-search-with-sqlite.html to set up SQLite's virtual table extension FTS5 for full text search on an external content table.
While the ...
- 6,279
3
votes
1
answer
2k
views
SQLite FTS5 Match is returning nothing
I have a SQLite3 table:
CREATE TABLE "test" (
"title" TEXT,
"shortdesc" TEXT,
"longdesc" TEXT,
"id" TEXT,
PRIMARY KEY("id&...
- 417
3
votes
2
answers
462
views
Unary NOT in SQLite FTS5 MATCH query
The SQLite FTS5 docs say that search queries such as SELECT ... WHERE MATCH '<query1> NOT <query2>' are supported, but it looks like there's no support for the unary NOT operator.
For ...
- 844
0
votes
1
answer
604
views
Can Peewee use highlight(), which is SQLite's FTS5 (full text search) auxiliary function?
SQLite's FTS5 supports highlight(). That auxiliary function returns tags for results from full text search queries: see the official documentation.
Peewee's code on Github, in the <sqlite_ext.py&...
- 2,120
1
vote
2
answers
298
views
"<result column> expected, got 'end'" error when using FTS tokenizer
I am using SQLite with the FTS tokenizer to tokenize a sentence in an Android application in Java.
Table:
CREATE VIRTUAL TABLE IF NOT EXISTS tokenizer USING fts3tokenize(icu, en);
Tokenize code:
...
- 471
1
vote
1
answer
1k
views
Sqllite FTS5: Substring matching using Trigram Tokenizer
I'am thinking of using Trigram Tokenizer to support substring matching in FTS.
Reference: https://sqlite.org/fts5.html#the_experimental_trigram_tokenizer
I created a dummy table and data as given ...
- 873
0
votes
0
answers
138
views
How does full-text search snowball algorithm interpret words of an unspecified language
I build a full-ext search index with sqlite and don't understand what is going on internally when i'm scanning documents contain few languages.
For example, i describe a programming topic i'm learning ...
- 139
1
vote
1
answer
155
views
SQLITE Perform Reverse full text search
Imagine we have two tables, One named tags and one named texts as follows:
tags table:
id
name
1
T1
2
T2
3
T3
4
T4
Texts table:
id
text
1
Text includes T2
2
Text includes T4
3
Text
4
Text ...
- 1,770
0
votes
1
answer
134
views
Build LINQ query for One-to-One and filtering by child
I have very simple model with two entities related as One-to-One via reference navigation properties:
class Post //"Main"
{
public int RowId { get; set; }
public string SomeInfo { ...
- 529
0
votes
0
answers
405
views
Wrong tokenization of Japanese verbs using SQLite FTS ICU tokenizer
I'm trying to use SQLite FTS ICU tokenizer for Japanese on an Android device.
Here is my query:
CREATE VIRTUAL TABLE IF NOT EXISTS tokenizer_ja USING fts3tokenize(icu, ja);
SELECT token, position, ...
- 471
1
vote
1
answer
252
views
Full Text Search FTS4 error - unable to use function MATCH
I am trying to implement FTS4 in my app which also uses Room. It's not a very complicated set-up based on the documentation and tutorials I have read but I have a persistent error triggered by my ...
- 746
3
votes
1
answer
1k
views
Postgresql full text search 8 times slower than sqlite fts search
So I want to do some fulltext searching, looked up how to do it with sqlite, implemented that and figured it's to slow. So I chose postgresql as I have been using that database anyway in the last ...
- 1,379
3
votes
1
answer
1k
views
How to highlight prefix match in SQLite FTS5
I have a SQLite FTS5 virtual table and am trying to highlight text in my prefix query results. I am aware of the highlight() and snippet() auxiliary functions, however they don't seem to support ...
- 498
1
vote
1
answer
133
views
How can you select entries in a FTS SQL db that only matches part of the query?
CREATE VIRTUAL TABLE TEST USING FTS5(SomeText);
INSERT INTO TEST(SomeText) VALUES
('Covid 19');
SELECT SomeText
FROM TEST
WHERE SomeText MATCH 'sars covid 19';
=> no results
How could I make ...
- 669
2
votes
0
answers
449
views
Sqlite3 fts5 search returns nothing if one of its phrases have a typo
It's not a duplicate question based on this:Sqlite with real “Full Text Search” and spelling mistakes (FTS+spellfix together)
imagine a doc like:
'aaa bbb ccc ddd'
mysql query against it:
select * ...
- 49
3
votes
2
answers
527
views
What's the difference of normal index and FTS in SQLite
Both use index to speed up string searching. So what's the difference?
In the docs for CREATE INDEX, there's no mention of tokenization, which makes me think that normal indices on text columns only ...
- 1,053
0
votes
1
answer
3k
views
OperationalError: no such module: fts4 ? Also No Extensions Available in SQLite in Python
I am trying to use fuzzymatcher, but when I run the code I get the following error:
OperationalError: no such module: fts4
Also, I tried to load the extensions by placing the latest sqlite.dll file ...
- 51
1
vote
0
answers
132
views
Room Migration failed while query for creating FTS table
I am trying to work on a FTS4 . I already have a table company for which full text search has to be enabled but facing issues.
database.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `companyFts` ...
- 5,533
2
votes
1
answer
2k
views
How to fix the error "Wrong number of arguments to function rank()" on SQLite Android using FTS4?
I have two tables:
persons
persons_fts.
Here are the definitions of the tables:
CREATE TABLE persons(name TEXT PRIMARY KEY NOT NULL, details TEXT);
CREATE VIRTUAL TABLE persons_fts USING FTS4(name ...
- 1,157
0
votes
1
answer
137
views
Android SQLite additional WHERE condition after MATCH
In Android SQLite i got tabel like this
domainObjectId: String // like '9876543210'
name: String
description: String
I want to use FTS on this to search without worrying about diacritical marks, how ...
- 1,410
1
vote
1
answer
1k
views
Room 2.1 SQLite FTS: inserting new single FTS object in database
I have been trying to use new Android's Room 2.1 feature of FTS table to enable search functionality.
Entity:
@Entity
class ChatMessageEntity(
@PrimaryKey
var ...
- 679
0
votes
1
answer
1k
views
sqlite full text search sqlite breaks when there is a dash in input
I've got a form with a search bar on my web app
@app.route('/search', methods=['POST','GET'])
def row_search():
if request.method == 'POST':
result = request.form
print(result['...
user10301987
0
votes
1
answer
321
views
SQlite FTS table is not being populated
DROP TABLE IF EXISTS TEXTS;
CREATE TABLE TEXTS (text_id int PRIMARY KEY, text ntext,last_update datetime DEFAULT
CURRENT_TIMESTAMP);
DROP TABLE IF EXISTS FTSTEXTS;
CREATE VIRTUAL TABLE FTSTEXTS USING ...
- 553
0
votes
0
answers
187
views
Empty result of fetchone when querying a FTS5 table in SQLite
I'm trying the Full-text Search in SQLite and I'm having an issue with the FTS5 tables.
Following this guide I've created some virtual tables, for example:
CREATE VIRTUAL TABLE IF NOT EXISTS ...
- 540
0
votes
1
answer
239
views
Android sqlite fts - using Offsets function with exact phrase search
I have a book reader type app and I am using sqlite to store texts and provide search function which highlights search results returned by. My problem is that when for example I have the following ...
- 6,608
3
votes
1
answer
2k
views
How can I reference a searchable FTS4 table in my DAO without getting a compiler error?
The android [FTS4 documentation][1] for android states "This class will have a mapping SQLite FTS4 table in the database". Should I interpret that as a mapping SQLite FTS4 table will be generated ...
- 721
0
votes
1
answer
137
views
How to combine NEAR with OR
I am using SQLite FTS5 feature.
This is the query I have tried so far:
SELECT * FROM fts_table WHERE fts_table MATCH 'NEAR((one OR two) three)';
It throw me an error: fts5: syntax error near "(".
...
- 946
3
votes
0
answers
945
views
SQLite: foreign key referencing data in an FTS5 shadow table
The full-text search extensions of SQLite (FTS3, FTS4, FTS5) create shadow tables. E.g., if I create FTS5-table
CREATE VIRTUAL TABLE test USING fts5(textData);
that will also automatically create ...
- 667