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
2
votes
3 answers

How to stop . being treated as a separator in SQLite FTS4

I want to be able to search for numbers like 2.3 using FTS4 in SQLite, but the . is being treated as a token boundary. Short of writing a full bespoke tokenizer is there any other way of excluding the . from the list of token boundary…
Peter
  • 1,022
  • 9
  • 12
2
votes
1 answer

PhoneGap, SQLite and full-text search

We are trying to scope out a project that has a relatively sophisticated search function. For instance it needs to search variations of words -- with "legal" and "legally" treated the same. I believe the SQLite full-text extensions (FTS3, FTS4)…
David Hammond
  • 3,286
  • 1
  • 24
  • 18
1
vote
0 answers

Android Room - Unknown tokenizer - FtsOptions.TOKENIZER_UNICODE61

I'm using FTS4 with a tokenizer to remove diacritics. The database is abstracted with Room. Something like: Content @Entity( tableName = "test" ) public class Test { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") …
davidm
  • 1,570
  • 11
  • 24
1
vote
1 answer

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 WHERE title MATCH ?; with parameter values…
Serguei Vine
  • 21
  • 4
1
vote
0 answers

Corrupt FTS5 table after declaring triggers a certain way

I have an external content FTS5 table, and I define triggers like this: CREATE TRIGGER fts_content_sync_my_fts_AFTER_INSERT AFTER INSERT ON my_table BEGIN INSERT INTO my_fts(id, meaning) VALUES (new.id, new.meaning); END; CREATE TRIGGER…
Mark
  • 7,446
  • 5
  • 55
  • 75
1
vote
1 answer

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 column. I create my table with a command like…
Eyjafl
  • 1,046
  • 6
  • 14
1
vote
1 answer

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") ); I insert anything in it: INSERT INTO test (id, title, shortdesc, longdesc) VALUES ("abc", "hello…
ifbamoq
  • 377
  • 4
  • 17
1
vote
1 answer

Setting unique indices in @FTS4 Room database causes build error

I tried to make a column of my Room database unique by defining indices. However, this caused my compiler to fail, as it drastically increases the required object heap. If I use @Entity(tableName = "seeds", indices = {@Index(value = {"name"}, unique…
Tobi
  • 858
  • 7
  • 15
1
vote
0 answers

SQLite FTS4 enhanced query syntax behaviour (use of parenthesis)

I cannot really understand the behaviour of parenthesis with FTS4, and would appreciate some explanations. Here are 3 variants of a query which, to my understanding, should all provide similar results, and which are not: sqlite> .version SQLite…
gfc
  • 33
  • 4
1
vote
1 answer

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 messageId: String, var messageUser: String, …
Androidz
  • 261
  • 2
  • 11
1
vote
0 answers

FTS4 indexing phone number

How to index phone number of type (001)12-345-6789 to allow user searching by sub number, for example 567, without using the - sign?
Eyal Muchtar
  • 11
  • 2
1
vote
0 answers

The Kotlin androidx.room.fts4 documentation looks like it's written using Java! Has anyone implemented an fts4 entity using Kotlin?

I'm having some difficulty implementing fts4 into my app. My aim is to build an app entirely in Kotlin using the latest jetpack components including the Room database. However, when I looked at the documentation for fts4, the examples were written…
Josh Hardman
  • 721
  • 6
  • 17
1
vote
1 answer

How to create virtual table FTS with external sqlite content table?

I want to create a SQLite virtual table with a content of a real one. I have a small sample which demonstrates my problem. I already red the official tutorial, but can't find anything wrong in this code. Some users use a rebuild option, but it…
Roman B.
  • 117
  • 9
1
vote
2 answers

Python/PHP SQLite querying Polish letter Ł/ł in FTS4/FTS5

Since SQLite FTS4/FTS5 tokenizer=unicode61 gives us: a=A=ą=Ą=ä=Ä ... z=ż=ź=Z=Ż=Ź=Ž=ž ... etc... Why not l=ł=L=Ł ??? Isn't it a bug? How to query SQLite on keybord not having Polish chars ł/Ł? For example querying for name Żabczyński like…
crooner
  • 69
  • 2
  • 8
1
vote
2 answers

SQLite Android Join operation on FTS4

Table1 is Virtual Table with fts4 and TABLE2 is normal table. Query1 (WORKS) SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.id=TABLE2.id WHERE TABLE1 MATCH 'sometext' LIMIT %d,%d Query 2 (DOES NOT WORK) SELECT * FROM TABLE2 LEFT OUTER…
varuog
  • 3,031
  • 5
  • 28
  • 56
1 2
3
4 5 6 7