Recent

Author Topic: $20 Task: Full Text Search in SQLite  (Read 3419 times)

Handoko

  • Hero Member
  • *****
  • Posts: 5145
  • My goal: build my own game engine using Lazarus
$20 Task: Full Text Search in SQLite
« on: February 21, 2024, 04:29:27 pm »
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

  • Hero Member
  • *****
  • Posts: 5145
  • My goal: build my own game engine using Lazarus
Re: $20 Task: Full Text Search in SQLite
« Reply #1 on: February 21, 2024, 05:35:20 pm »
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

  • Full Member
  • ***
  • Posts: 143
Re: $20 Task: Full Text Search in SQLite
« Reply #2 on: February 21, 2024, 05:35:58 pm »
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

  • Hero Member
  • *****
  • Posts: 5145
  • My goal: build my own game engine using Lazarus
Re: $20 Task: Full Text Search in SQLite
« Reply #3 on: February 21, 2024, 05:41:19 pm »
Thank you for the links.
That of course will be useful for others.

TRon

  • Hero Member
  • *****
  • Posts: 2484
Re: $20 Task: Full Text Search in SQLite
« Reply #4 on: February 21, 2024, 05:42:42 pm »
That of course will be useful for others.
In that case see fts5  :)

Also came across this to get you going.
« Last Edit: February 21, 2024, 05:47:41 pm by TRon »

paweld

  • Hero Member
  • *****
  • Posts: 981
Re: $20 Task: Full Text Search in SQLite
« Reply #5 on: March 03, 2024, 06:51:08 pm »
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
« Last Edit: March 03, 2024, 09:33:35 pm by paweld »
Best regards / Pozdrawiam
paweld

Handoko

  • Hero Member
  • *****
  • Posts: 5145
  • My goal: build my own game engine using Lazarus
Re: $20 Task: Full Text Search in SQLite
« Reply #6 on: March 04, 2024, 09:57:55 am »
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

  • Hero Member
  • *****
  • Posts: 2325
Re: $20 Task: Full Text Search in SQLite
« Reply #7 on: March 04, 2024, 10:32:32 am »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

paweld

  • Hero Member
  • *****
  • Posts: 981
Re: $20 Task: Full Text Search in SQLite
« Reply #8 on: March 04, 2024, 11:10:25 am »
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 :-)
Best regards / Pozdrawiam
paweld

Dzandaa

  • Full Member
  • ***
  • Posts: 249
  • From C# to Lazarus
Re: $20 Task: Full Text Search in SQLite
« Reply #9 on: March 04, 2024, 11:35:14 am »
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->
Dzandaa

Zvoni

  • Hero Member
  • *****
  • Posts: 2325
Re: $20 Task: Full Text Search in SQLite
« Reply #10 on: March 04, 2024, 11:43:53 am »
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....
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

paweld

  • Hero Member
  • *****
  • Posts: 981
Re: $20 Task: Full Text Search in SQLite
« Reply #11 on: March 04, 2024, 11:50:00 am »
@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.
Best regards / Pozdrawiam
paweld

Zvoni

  • Hero Member
  • *****
  • Posts: 2325
Re: $20 Task: Full Text Search in SQLite
« Reply #12 on: March 04, 2024, 12:01:57 pm »
@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
« Last Edit: March 04, 2024, 12:37:39 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

paweld

  • Hero Member
  • *****
  • Posts: 981
Re: $20 Task: Full Text Search in SQLite
« Reply #13 on: March 04, 2024, 12:35:49 pm »
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
Best regards / Pozdrawiam
paweld

Zvoni

  • Hero Member
  • *****
  • Posts: 2325
Re: $20 Task: Full Text Search in SQLite
« Reply #14 on: March 04, 2024, 12:43:26 pm »
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? :D
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018