[SOLVED] SQLite query to Lazarus

[DanishMale]

Hi,

I've gotten a SQL query directly from DB Browser for SQLite and I need to convert this to use in a program compiled by Lazarus with use of TSQLite3Connection

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

1. SELECT *, SUBSTR(`COL2`,1,INSTR(`COL2`,'.')-1) AS SHORT_COL2, COUNT(*) AS Found FROM `TABLE` WHERE
2. LOWER(`COL3`) REGEXP '(This).(is).(a).(test).(Query)'
3. OR LOWER(`COL3`) REGEXP '(This).(is).(a).(test).(.*)'
4. OR LOWER(`COL3`) REGEXP '(This).(is).(a).(.*).(Query)'
5. OR LOWER(`COL3`) REGEXP '(This).(is).(.*).(test).(Query)'
6. OR LOWER(`COL3`) REGEXP '(This).(.*).(a).(test).(Query)'
7. OR LOWER(`COL3`) REGEXP '(This).(is).(a).(.*).(.*)'
8. OR LOWER(`COL3`) REGEXP '(This).(is).(.*).(.*).(.*)'
9. OR LOWER(`COL3`) REGEXP '(This).(.*).(.*).(.*).(.*)'
10.  
11.  

If I take the above query and try it in my Lazarus program with TSQLite3Connection I get the error:

Project raised exception class 'ESQLDatabaseError' with message:
TSQLite3Connection : no such function REGEXP

I wonder how to convert this or what ever solution which may be available to do this, which is to get a result from complete match to the best possible match from the database

[PierceNg]

As per https://sqlite.org/lang_expr.html

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

DB Browser has its own regexp() function. To get your query running as-is in your Pascal program, your options are:

- implement regexp() in your Pascal application as an SQLite custom function
- rebuild SQLite DLL/so/dylib to incorporate a regexp() function statically
- if your SQLite DLL/so/dylib allows loading extensions, load a regexp() implementation DLL/so/dylib

SQLite's source distribution contains a regexp.c. This file is incorporated into shell.c in the amalgamation, and building the SQLite CLI from shell.c from source gives you the regexp() function. You can incorporate regexp.c into your own build of SQLite DLL/so, or build it as a standalone SQLite extension DLL/so.

There are other REGEXP implementations. E.g. https://github.com/asg017/sqlite-regex which is implemented in Rust and has a richer API than regexp.c.

[CharlyTango]

Obviously you can extend SQLite
https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query

I'm not familiar with these cryptic strings regex afficionados produce. Therefore i asked Grok to reformat the statement.
Im convinced that the answer ist not really working but can give a hint how to do it

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

1. SELECT *,
2.        SUBSTR(`COL2`, 1, INSTR(`COL2`, '.') - 1) AS SHORT_COL2,
3.        COUNT(*) AS Found
4. FROM `TABLE`
5. WHERE LOWER(`COL3`) LIKE 'this_is_a_test_query'
6.    OR LOWER(`COL3`) LIKE 'this_is_a_test%'
7.    OR LOWER(`COL3`) LIKE 'this_is_a%query'
8.    OR LOWER(`COL3`) LIKE 'this_is%test_query'
9.    OR LOWER(`COL3`) LIKE 'this%a_test_query'
10.    OR LOWER(`COL3`) LIKE 'this_is_a%%'
11.    OR LOWER(`COL3`) LIKE 'this_is%%%'
12.    OR LOWER(`COL3`) LIKE 'this%%%%'

Perplexity and Claude have another opinion

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

1. SELECT *,
2.        SUBSTR(`COL2`, 1, INSTR(`COL2`, '.') - 1) AS SHORT_COL2,
3.        COUNT(*) AS Found
4. FROM `TABLE`
5. WHERE
6.     LOWER(`COL3`) LIKE '%this%is%a%test%query%'
7.  OR LOWER(`COL3`) LIKE '%this%is%a%test%'
8.  OR LOWER(`COL3`) LIKE '%this%is%a%query%'
9.  OR LOWER(`COL3`) LIKE '%this%is%test%query%'
10.  OR LOWER(`COL3`) LIKE '%this%a%test%query%'
11.  OR LOWER(`COL3`) LIKE '%this%is%a%'
12.  OR LOWER(`COL3`) LIKE '%this%is%'
13.  OR LOWER(`COL3`) LIKE '%this%'
14. ;

I'd prefer the second one for a first test ;-)

[Hansvb]

Both options with like are strange. Like is usually something:
like ‘ %some text'. Then all strings ending in some text are found.
like 'some text%'. Then all strings that start with some text are found.
like '%some text%. Then you have all the strings that contain some text.

[PierceNg]

A capable regexp() function will be able to do both case-sensitive and -insensitive matching, making the lower() calls redundant.

[DanishMale]

Thanks to every one who came up with a solution which all brought me to the answer I needed and choose to go with CharlyTango's suggestion .....

Again thanks a lot to all Have a nice day

[Zvoni]

From my own Functions:

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

1. unit USQLiteRegexP;
2. {$mode ObjFPC}{$H+}
3. interface                  
4. uses
5.   Classes, SysUtils, sqlite3dyn, sqlite3conn, sqltypes;
6. Type
7.   TSQLiteHelper=class helper for TSQLite3Connection
8.   Public
9.     Procedure RegisterRegexp;    
10.   end;
11.  
12. implementation
13. Uses regexpr;
14.  
15. Var
16.   regex:TRegexpr;
17.  
18. //Own regexp-operator if sqlite-extension is not available for whatever reason
19. procedure RegExp(ctx: psqlite3_context; n: Integer; args: ppsqlite3_value);cdecl;
20. Var
21.   b:Boolean=False;
22. begin
23.   regex.ModifierI:=True;  //Customize to suite
24.   regex.ModifierG:=True;  //Customize to suite
25.   Try
26.     regex.Expression:=strpas(sqlite3_value_text(args[0]));
27.     regex.InputString:=strpas(sqlite3_value_text(args[1]));
28.     b:=regex.Exec;
29.   Except
30.     On E:ERegExpr Do
31.       Begin
32.         b:=False;        
33.       end;
34.   end;
35.   sqlite3_result_int(ctx, b.ToInteger);
36. end;                                                
37.  
38. { TSQLiteHelper }
39. procedure TSQLiteHelper.RegisterRegexp;
40. begin
41.   checkerror(sqlite3_create_function(Self.Handle,
42.                                PChar('regexp'),
43.                                2,
44.                                SQLITE_UTF8 Or SQLITE_DETERMINISTIC,
45.                                Nil,
46.                                @RegExp,
47.                                Nil,
48.                                Nil));
49. end;                                                    
50.  
51. Initialization
52. If Not Assigned(regex) Then regex:=TRegexpr.Create;
53. finalization
54. If Assigned(regex) Then regex.Free;
55. end.    

place USQliteRegExP in Uses-clause, specifically AFTER unit "sqlite3conn"
and after opening the SQLite-Connection execute MyCon.RegisterRegexp

Not really tested to the last possible use-case, nevermind it was more or less just a proof-of-concept which worked

[PierceNg]

From my own Functions:
...
Not really tested to the last possible use-case, nevermind it was more or less just a proof-of-concept which worked

Nice!

Btw, mORMot links SQLite statically, and regexp.c is incorporated. Thus, when using mORMot, the SQLite regexp() function is available. Here's a complete mORMot program doing regex seach on the Sakila database. Obtaining a copy of the database in SQLite format is left as an exercise for the reader. :-)

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

1. program mormotregex;
2.  
3. {$mode delphi}{$H+}
4. {$define FPC_X64MM}
5. {$define FPCMM_SERVER}
6. {$define FPCMM_REPORTMEMORYLEAKS}
7. {$define FPCMM_DEBUG}
8.  
9. {$I mormot.defines.inc}
10.  
11. uses
12.   cthreads, sysutils,
13.   mormot.db.raw.sqlite3, mormot.db.raw.sqlite3.static,
14.   mormot.db.sql, mormot.db.sql.sqlite3;
15.  
16. procedure sqlite_ormless_regexp;
17. var
18.   connprop: TSqlDBSQLite3ConnectionProperties;
19.   dbconn: TSqlDBSQLite3Connection;
20.   sql: String;
21.   stmt: TSqlDBStatement;
22. begin
23.   connprop := TSqlDBSQLite3ConnectionProperties.Create('sakila.db', '', '', '');
24.   dbconn := TSqlDBSQLite3Connection.Create(connprop);
25.   try
26.     dbconn.Connect;
27.     sql := 'SELECT first_name, last_name, count(*) films FROM actor ' +
28.             'JOIN film_actor ' +
29.             'ON film_actor.actor_id = actor.actor_id ' +
30.             'AND actor.first_name REGEXP ''ILL'' ' +
31.             'GROUP BY actor.actor_id, first_name, last_name ' +
32.             'ORDER BY films DESC ';
33.     stmt := dbconn.NewStatement;
34.     try
35.       stmt.Prepare(sql, true);
36.       stmt.ExecutePrepared;
37.       while stmt.Step do
38.         writeln(Format('%s %s made %d films', [stmt.ColumnUtf8(0), stmt.ColumnUtf8(1), stmt.ColumnInt(2)]));
39.     finally
40.       stmt.ReleaseRows;
41.       stmt.Free;
42.     end;
43.     dbconn.Disconnect;
44.   finally
45.     dbconn.Free;
46.     connprop.Free;
47.   end;
48. end;
49.  
50. begin
51.   sqlite_ormless_regexp;
52. end.

When run, the program produces:

Code: Text  [Select][+][-]

1. WILL WILSON made 31 films
2. LUCILLE TRACY made 30 films
3. MILLA KEITEL made 28 films
4. WILLIAM HACKMAN made 27 films
5. MILLA PECK made 24 films
6. LUCILLE DEE made 24 films

The REGEXP operator in SQLite is case-sensitive, so searching for 'ill' in the above would yield no row. However, it is also possible to use function call syntax, and SQLite provides both regexp() and regexpi(), the latter for case-insensitive matching.

When using regexpi(), the query becomes:

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

1. SELECT first_name, last_name, COUNT(*) films FROM actor
2. JOIN film_actor
3. ON film_actor.actor_id = actor.actor_id
4. AND regexpi('ill', actor.first_name) = 1
5. GROUP BY actor.actor_id, first_name, last_name
6. ORDER BY films DESC

The regexpi() function is available in the SQLite CLI, but unfortunately not in current mORMot, because mORMot is using an older version of regexp.c.