Stack Overflow
Stack Overflow

Questions tagged [fts5]

FTS5 is an SQLite virtual table module that provides full-text search functionality to database applications. In their most elementary form, full-text search engines allow the user to efficiently search a large collection of documents for the subset that contain one or more instances of a search term. The search functionality provided to world wide web users by Google is, among other things, a full-text search engine, as it allows users to search for all documents on the web that contain, for example, the term "fts5".

For more information, refer here

48 questions
2
votes
1 answer

Using Match in a sqlite fts5 query but need more control over ranking?

I have a virtual table created using fts5: import sqlite3 # create a db in memory con = sqlite3.connect(':memory:') con.execute('create virtual table operators using fts5(family, operator, label, summary, tokenize=porter)') # some sample…
Markus Heckmann
  • 307
  • 1
  • 6
2
votes
1 answer

How to fetch names of virtual tables?

Example schema: CREATE VIRTUAL TABLE posts USING FTS5(title, body); Select table names: SELECT name FROM sqlite_master WHERE type='table'; Result: posts posts_data posts_idx posts_content posts_docsize posts_config How to fetch result only for…
Андрей Антонов
  • 298
  • 1
  • 4
  • 16
2
votes
3 answers

How to enable FTS5 search a string with ".", "_" and "0-9"?

I have a table holding 300K records of strings using alphanumeric, digit, dot, underscore and brackets []. I use FTS5 extension to sqlite3 to enable fast search over that table. This is how i create the FTS virtual table: database =…
NirMH
  • 4,769
  • 3
  • 44
  • 69
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

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

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 below: CREATE VIRTUAL TABLE tri_test USING fts5(a,…
psms
  • 851
  • 3
  • 18
  • 35
1
vote
0 answers

How to enable sqlite3 FTS5 extension in python (on windows)?

I am trying to use FTS5 from sqlite3 in python, but I see that the extension is not enabled. Online, I can only find tutorials on how to enable this on linux or mac. Is there an easy way to enable FTS5 on windows? (I have only recently started to…
plumbummm
  • 11
  • 1
1
vote
1 answer

C# SQLite FTS5 Table and Triger creation

I am creating a virtual table with sqlite fts5 and I am having the following error message: SQL Logic error no such module: FTS5. Below is my code: Using Package manager in VS 2017 I have already download SQLite and SQLite FTS5. private static…
Bainn
  • 37
  • 12
1
vote
0 answers

Sqlite FTS5 initial token query syntax?

I have set up FTS5 (full-text search) with virtual tables in sqlite. MATCH queries are working except it always throws a syntax error on initial token queries. For example: SELECT * FROM fts_article WHERE fts_article MATCH '^suo' throws Error…
vortek
  • 474
  • 2
  • 14
1
vote
0 answers

FTS5 cannot use Operators with Match operator

I'm doing full text search using sqlite and below are some select query examples that I'm using. Ex: SELECT * FROM table WHERE table MATCH 'column:father* OR for*' ORDER BY rank; SELECT * FROM table WHERE table MATCH 'column:exam* AND yo*' ORDER BY…
Chamitha Narawita
  • 53
  • 3
0
votes
1 answer

How to count word frequency in a fulltext column?

From following schema I'd like to return the count by year of any given word: CREATE TABLE treatments ( id INTEGER PRIMARY KEY, treatmentId TEXT UNIQUE NOT NULL CHECK(Length(treatmentId) = 32), treatmentTitle TEXT COLLATE NOCASE, …
punkish
  • 13,598
  • 26
  • 66
  • 101
0
votes
0 answers

How to make joining query with FTS5 faster?

I have a database where the main operation is read (once a month all entries will be dropped and re-written). All fields are type TEXT so I use FTS5 and it works, except when I run a JOIN query. Tables monographs (books, 3.5M entries) and authors…
user20654520
0
votes
1 answer

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 properties: the title of the page the revision (a…
eil
  • 113
  • 1
  • 6
0
votes
1 answer

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, tokenize="trigram"); This gets executed in sqlite…
Sayansen
  • 51
  • 1
  • 8
1
2
3 4