$20 Task: Full Text Search in SQLite

[Handoko]

I will pay U$ 20 via PayPal for someone for writing a beginner-friendly detailed step-by-step instruction how to do full text search in SQLite. Don't send it to me but post it here so it can be useful for others too.

Short Story

This is a how-to-do contest for writing the instruction for doing full text search in SQLite in Windows, it will be better if you can also explain how doing it in Linux. That should include the instruction for preparing/compiling SQLite and the extension and a demo project (Lazarus or FPC). Please also provide necessary images or screenshots or links if needed. Post them here and one or two weeks later I will start a poll to let users here to decide who provides the best instruction.

Long Story

After spending quite some time learning SQLite and Firebird, I want to use the skill for real projects. But I haven't explored the full text search topic. I now got a task for writing a program that needs single user database, I decided to use SQLite. As a bonus I wish the program to have full text search feature. I already doing some research on the Internet, both SQLite and Firebird have full text search feature but it is not provided by the default download/installation.

Normally, I will search the web, read the wikis, test and study them myself. Unfortunately I am very busy at the moment. Wouldn't it be nice if someone can gather the information, write a short demo and provide me the instruction. I know USD 20 may not be worthy for your time, but that should be enough to buy you a cup of coffee. :)

[Handoko]

I forgot to mention, you don't have to write the demo from scratch. You can add your code to this SQLite demo:

https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.html#msg496461

[Red_prig]

I once used full-text search in sqllte, but I'm too lazy to write tutorials, I can only say that I used information from the official documentation https://www.sqlite.org/fts3.html , and for easy connection I used ZeosDBO components https://wiki.freepascal.org/ZeosDBO but of course this won’t pass for a tutorial

[Handoko]

Thank you for the links.
That of course will be useful for others.

[TRon]

That of course will be useful for others.

In that case see fts5 

Also came across this to get you going.

[paweld]

I'll just throw in some general information - without participating in the contest.

So, it turns out that the default library already includes FTS5, I checked on Windows 10 - the library downloaded from the official site, and on Debian 12 - installed using apt.

However, if you need to recompile the library with non-standard switches, it is not that complicated:

- on Windows:
  * download MinGW: https://winlibs.com/#download-release (I downloaded UCRT runtime).
  * unzip
  * add an entry to the environment variables with the path to the bin directory.
  * download the sqlite source code sqlite-amalgamation-*.zip from the official sqlite website and unzip.
  * at the command line you navigate to the directory with the unzipped sqlite source files.
  * run the compilation:

Code: [Select]

gcc -O3 -shared -DSQLITE_ENABLE_FTS5 sqlite3.c -o sqlite3.dll.

- on Linux
  * download the sqlite source code sqlite-amalgamation-*.zip from the official sqlite website and unzip.
  * in terminal navigate to the directory with the unzipped sqlite source files.
  * run the compilation:

Code: [Select]

gcc -O3 -shared -fPIC -DSQLITE_ENABLE_FTS5 sqlite3.c -o sqlite3.so.

As for the application itself, instructions on how to use full text search are readily available on the Internet. Attached is a simple application I created for testing purposes. It uses ZEOS compoenents (in SQLdb I encountered a problem - with long texts it does not return the entire content), and HtmlViewer to display the results.
I used pubmed data downloaded from the NCBI website - you can download the data needed to run the application from: https://files.brudnopis.ovh/file/SxhNwkLSx3zIlj6Y/MS2idYGARCKNFjP5/pubmed.7z
Edit: change library extension for linux

[Handoko]

Thank you very much everyone who shares the links and information. I hope more information will be collected/shared, so it can be useful for anyone who needs this information.

[Zvoni]

It uses ZEOS compoenents (in SQLdb I encountered a problem - with long texts it does not return the entire content), and HtmlViewer to display the results.

By any chance: Did you use CHAR/VARCHAR in your DDL's?
See here Post #4:
https://forum.lazarus.freepascal.org/index.php/topic,40253.msg278029.html#msg278029

[paweld]

When defining a virtual FTS table, you do not specify the data type. Zeos immediately recognizes it as ftMemo, while SQLdb recognizes it as ftString - so I chose ZEOS :-)

[Dzandaa]

Hi,

You can try this Template I made for creating simple SQLite Databases.

Just unzip it in CreateDB Directory and put it in your template Directory.

Then run Lazarus and in Files->New Project from Template->SQL DB Application

Fill the values and after compilation for Windows, don't forget to put the SQLite.dll in the same directory as you exe.

Also work in Linux.

It is not fancy nor very smart, but it is a base for beginners.

B->

[Zvoni]

When defining a virtual FTS table, you do not specify the data type. Zeos immediately recognizes it as ftMemo, while SQLdb recognizes it as ftString - so I chose ZEOS :-)

*sigh*
and because it's ftString with no size, it get's cut off at 255 characters....

[paweld]

@Zvoni: according to this page: https://sqlite.org/fts5.html when defining a virtual table full text search do not specify the type of data in the columns.

[Zvoni]

@Zvoni: according to this page: https://sqlite.org/fts5.html when defining a virtual table full text search do not specify the type of data in the columns.

I know that, though logic dictates, that it should be a form of "String" or "Text" (otherwise why should it be called Full TEXT Search....)

I think the culprit is not in sqldb, but in sqlite3conn
found this in AddFieldDefs-Method

Code: Pascal  [Select][+][-]

1. // In case of an empty fieldtype (FD='', which is allowed and used in calculated
2.     // columns (aggregates) and by pragma-statements) or an unknown fieldtype,
3.     // use the field's affinity:
4.     if FT=ftUnknown then
5.       case TStorageType(sqlite3_column_type(st,i)) of
6.         stInteger: FT:=ftLargeInt;
7.         stFloat:   FT:=ftFloat;
8.         stBlob:    FT:=ftBlob;
9.         else       FT:=ftString;
10.       end;                                

Hmm...maybe run a test with an FTS-Table, and then via Breakpoint, check what's really returned by sqlite3_column_type

EDIT: Yep. Just did a testrun.
It jumps into the Else-Part of the case

[paweld]

for query:

Code: SQL  [Select][+][-]

1. SELECT issn, journaltitle, articletitle, articletext, CAST(articletext AS text) atre, CAST(''a'' AS text) test1, CAST(''a'' AS VARCHAR(1000)) test2 FROM pubmed

all columns are: String size 1021

[Zvoni]

for query:

Code: SQL  [Select][+][-]

1. SELECT issn, journaltitle, articletitle, articletext, CAST(articletext AS text) atre, CAST(''a'' AS text) test1, CAST(''a'' AS VARCHAR(1000)) test2 FROM pubmed

all columns are: String size 1021

With Zeos?

btw: I did another Testrun with sqldb/sqlite3conn, but reading out explicitely the StorageType sqlite3_column_type returns for a Query on a FTS5 virtual table

Hold your pants: it's stText

Soooo.....anyone up for an improvement proposal?