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
1
vote
1 answer

SQL FTS and comparison statements

Short story. I am working on a project where I need to communicate with SQLite database. And there I have several problems: There is one FTS table with nodeId and nodeName columns. I need to select all nodeIds for which nodeNames contains some text…
besworland
  • 747
  • 2
  • 7
  • 17
1
vote
1 answer

joining tables : one a normal one and the other FTS virtual table

is it a good idea to join a normal table with FTS table? I want to know whether it is going to effect the original performance of the FTS table or nor? The query definitely would be using MATCH clause of course.
Hammad Dar
  • 475
  • 8
  • 18
0
votes
0 answers

sqlite fts4 offsets data with diacritics Latin characters

sqlite fts4 extension specify for remove diacritics from Latin script characters tokenize=unicode61 "remove_diacritics=2" but selected offsets queries return different values ​​when diacritics are in text. I use offsets information to mark matched…
Orlay Garcia Duconge
  • 570
  • 5
  • 16
0
votes
1 answer

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 the index of the virtual table, we used the…
Eng
  • 1,617
  • 2
  • 12
  • 25
0
votes
1 answer

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|) So now I need a way to search for items by title…
M I P A
  • 63
  • 1
  • 6
0
votes
1 answer

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 but when I create a statement using Match the…
Sebastián Ardila
  • 127
  • 9
0
votes
1 answer

Android Room FTS4 how to drop triggers created automatically

Can I have control over 'insert/update/delete' triggers that are created automatically by Android Room to sync data between content and virtual tables. Can I remove update triggers that I don't need? I've created virtual table with external content…
devger
  • 703
  • 4
  • 12
  • 26
0
votes
0 answers

Query to select next few rows

I'm working on a search tool, the words (to be searched) are in object array first I search the database(about 450k rows) using FTS4 and sqlite. This works fast, the SQL I'm using is: String s = "select * from fts_sggs where word_text MATCH ?…
Isherpal Singh
  • 13
  • 4
0
votes
1 answer

SQLite UPDATE statement is not working inside AFTER INSERT Trigger (Android, Room)

My SQL: this is not working: I want this trigger to work. CREATE TRIGGER sync_VerseSearch AFTER INSERT ON `VerseTranslation` BEGIN UPDATE VerseSearch SET text = VerseSearch.text || ' \n ' || NEW.text WHERE VerseSearch.verse_id =…
Riajul
  • 1,140
  • 15
  • 20
0
votes
1 answer

Using SQLite Triggers on Android Returns Error

I'm new to SQLite, I'm trying to create an application where a user can create tasks and attach reminders to them. I'm using SQLite database to save these items. Everything works perfectly, now i want to implement a full text search feature, i have…
Tamim Attafi
  • 2,253
  • 2
  • 17
  • 34
0
votes
0 answers

Progress Bar with FTS4 SQLite3 search

Mint-19.1, Lazarus 1.8.4 I am using SQlite3 and FTS4 text search and it works fine retrieving results in about 15 to 20 seconds for 3.5GB of data file. But, the process hogs everything and I cannot even activate a Progress Bar in Marquee mode. It…
LinuxFerLife
  • 199
  • 7
0
votes
1 answer

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?
Karsten W.
  • 17,826
  • 11
  • 69
  • 103
0
votes
1 answer

Sqlite fts4 case sensitive

Very new to Sqlite3 and I finally have an FTS4 search working, but am stumped how to code for Case-Sensitive. I have a Database with tblMain that has two BLOB_TEXT columns, advOne and advTwo CREATE VIRTUAL TABLE tFind USING FTS4(aOne, aTwo); //…
LinuxFerLife
  • 199
  • 7
0
votes
1 answer

Corrupted Database Table cannot DROP

Using SQLite3 and Ubuntu 14.04-LTS. I managed to mis-type when creating a Virtual table for a FTS search. Now I cannot remove the Table. This is what I wanted: CREATE VIRTUAL TABLE tFind USING FTS4(main TEXT, base TEXT, hash TEXT); But, I must have…
LinuxFerLife
  • 199
  • 7
0
votes
0 answers

SQlite full-text search MATCH miltiple columns with OR and AND

I am learning how to use FTS4 in SQlite and trying to translate something like this: SELECT * FROM table WHERE (postal_code LIKE q1 OR place_name LIKE q1) AND (address LIKE q2) which works perfectly with the normal table, to something like…
Vladimir
  • 1,045
  • 1
  • 11
  • 23
1 2 3 4
5
6 7