Recent

Author Topic: [SOLVED] Is SQLite case sensitive on Linux  (Read 2372 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 796
[SOLVED] Is SQLite case sensitive on Linux
« on: May 06, 2021, 06:14:35 pm »
Hi folks

I've had some utterly hero help from dseligo her in the forums. He has helped me so much with lots of SQL for SQLite which I use with my application.

But, I keep hitting snags when I take working, valid SQL code from Windows that runs just fine, to Linux and OSX. And I have a feeling it is case sensitivity, as some Googling suggests that it is sensitive to table names. I keep getting errors like :

"raised exception class 'ESQLDatabaseError' with message:
SQLite3Connection1 : near "(": syntax error"

But, I've gone through and checked the CREATE statement with all my other references to the tables, and the case seems to be consistant. So I'm not certain it is case sensitivty. But as it works fine on Windows, I am assuming it must be.


Q1) Is SQLite case sensitive on Linux?
Q2) Is SQLite case sensitive on OSX?
Q3) Is there a way to simply disable SQL case sensitivity within my Lazarus project? Or a way to ensure that when its compiled, the resulting Linux combiled binary contains SQL that is in the appropriate case? 
Q4) If Q3 is "No", is there a way to automatically correct\format large SQL statements to the correct formatting\case?

Thanks
« Last Edit: May 12, 2021, 09:53:07 am by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

lucamar

  • Hero Member
  • *****
  • Posts: 4009
Re: Is SQLite case sensitive on Linux
« Reply #1 on: May 06, 2021, 07:10:35 pm »
I'm not entirely sure, having used it but little, but AFAIK SQLite is not case sensitive, except with respect to the database name itself, which depends on the underlying file system.

One thing that could be happening instead is that you have got different versions with slightly "different" (read: "buggy" or "patched") sintaxes. It would help troubleshooting to see the statements giving errors and in which version they work (the Windows one) and which don't (the Linux and Mac OS ones).
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus/FPC 2.0.8/3.0.4 & 2.0.12/3.2.0 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

dseligo

  • Sr. Member
  • ****
  • Posts: 275
Re: Is SQLite case sensitive on Linux
« Reply #2 on: May 06, 2021, 07:27:04 pm »
One thing that could be happening instead is that you have got different versions with slightly "different" (read: "buggy" or "patched") sintaxes. It would help troubleshooting to see the statements giving errors and in which version they work (the Windows one) and which don't (the Linux and Mac OS ones).

I think that too, I asked him to check SQlite version on OSX and Linux (in PM).
I think the problem is in use of row_number() window function which was added in 3.25.0 version of SQlite.

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #3 on: May 06, 2021, 07:36:00 pm »
OK, so on Windows we are using SQLite v3.35.5.0 valid as of April 2021 (replacing former version 3.21.0.0)

On Linux Mint, I have version 3.22.0 2018-01-22. On Apple OSX Big Sur (latest OSX) I have version 3.32.3 2020-06-18. So seemingly Apple and Linux LTS systems are significantly behind v3.35.5.0

So, given dseligo's remark above, this might explain the problem! I just need to try and find a v3.35.5.0 debian package, or compile it from source I suppose. And then try.

Problem next, though (assuming that does work) is how I ensure my users, who will not all have bang up to date bleeding edge Linux distributions, can use the application. But I guess that's up to them. Stay current or not...we can't change the world can we. But then I too am one of those who is sticking with what I like and know works (Linux Mint v19)

UPDATE: https://sqlite.org/2021/sqlite-tools-linux-x86-3350500.zip has a pre-compiled Linux binary, v3.35.5.0! So I hoped I could just ship that, but I need the SO library file, not the complete binary, called libsqlite3.so.0. I will probably have to compile from source.

The code, for reference, is :

Code: Pascal  [Select][+][-]
  1. // COMPARE TWO FOLDERS tab update grid routine
  2. procedure TfrmSQLiteDBases.UpdateGridCOMPARETWOFOLDERSTAB(Sender: TObject);
  3.   begin
  4.     try
  5.     sqlCOMPARETWOFOLDERS.Close;
  6.     //sqlCOMPARETWOFOLDERS.SQL.Text := 'SELECT * FROM TBL_COMPARE_TWO_FOLDERS';                     //DS (original)
  7.     sqlCOMPARETWOFOLDERS.SQL.Text:=                                                                 //DS (new)
  8.      'select row_number() over (order by FileName) rownum, * '+
  9.       'from ( '+
  10.       'select a.FileName,a.FilePath as FilePathA, a.FileHash as FileHashA, '+
  11.       ' b.FilePath as FilePathB, b.FileHash as FileHashB '+
  12.       'from TBL_COMPARE_TWO_FOLDERS a '+
  13.       '  left join TBL_COMPARE_TWO_FOLDERS b on a.FileName=b.FileName and b.FilePath=:FilePathB '+
  14.       'where a.FilePath=:FilePathA '+
  15.       'union '+
  16.       'select c.FileName,d.FilePath as FilePathA, d.FileHash as FileHashA, '+
  17.       ' c.FilePath as FilePathB, c.FileHash as FileHashB '+
  18.       'from TBL_COMPARE_TWO_FOLDERS c '+
  19.       '  left join TBL_COMPARE_TWO_FOLDERS d on c.FileName=d.FileName and d.FilePath=:FilePathA '+
  20.       'where c.FilePath=:FilePathB)';
  21.  
  22.     sqlCOMPARETWOFOLDERS.ParamByName('FilePathA').AsString:=FFilePathA;                             //DS (new)
  23.     sqlCOMPARETWOFOLDERS.ParamByName('FilePathB').AsString:=FFilePathB;                             //DS (new)
  24.     SQLite3Connection1.Connected := True;
  25.     SQLTransaction1.Active := True;
  26.     sqlCOMPARETWOFOLDERS.Open;  [b]// ERROR IS THROWN HERE[/b]
  27.     frmDisplayGrid3.dbGridC2F.Options := frmDisplayGrid3.dbGridC2F.Options + [dgAutoSizeColumns];
  28.     except
  29.     on E: EDatabaseError do
  30.     begin
  31.       MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
  32.     end;
  33.   end;
  34. end;
  35. [/pascal]
« Last Edit: May 07, 2021, 10:55:43 am by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

lucamar

  • Hero Member
  • *****
  • Posts: 4009
Re: Is SQLite case sensitive on Linux
« Reply #4 on: May 06, 2021, 07:52:39 pm »
Quite a statement, that! :o
If the error is in the SQL, might not you might find better help in the SQLite forum?

Just saying :-[
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus/FPC 2.0.8/3.0.4 & 2.0.12/3.2.0 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #5 on: May 06, 2021, 07:56:00 pm »
I did say it was hero help, that he gave me!! :-)
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #6 on: May 06, 2021, 08:14:13 pm »
OK, so, the plot thicken.

I managed to compile libsqlite3.so.0 on Linux by downloading the SQLite source (https://sqlite.org/2021/sqlite-amalgamation-3350500.zip) and executing :

gcc -shared -o libsqlite3.so.0 -fPIC sqlite3.c

I then copied that so.0 file to my project folder, and adjusted the dlopen path to the literal location (because using just the filename makes it load the Linux distribution version) so that it used the most recent version instead of my installed version (which is 3.22.0 2018-01-22 ).

LibHandle := dlopen('/home/me/Documents/Projects/QH/libsqlite3.so.0', RTLD_LAZY);

I then run the steps that execute the SQL code, and no error :-) Hooray

But, there is now no result, either! :-(  Hoorooo :-(

So, this confirms the error message was related to the version being used. The problem now is a) ensuring my program load the so file I ship with it, and not the users inbuilt version. Is the best way to do that : LibHandle := dlopen('./libsqlite3.so.0', RTLD_LAZY); ? Is LoadLibrary a better choice  than dlopen (I use LoadLibrary for Windows now, so thinking of trying to use it for both Linux and OSX too)

 And b) working out why I now do not get any data returned by the SQL.
« Last Edit: May 07, 2021, 10:54:13 am by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

dseligo

  • Sr. Member
  • ****
  • Posts: 275
Re: Is SQLite case sensitive on Linux
« Reply #7 on: May 06, 2021, 08:39:38 pm »
Or, I could rewrite SQL so row_number() is not used.

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #8 on: May 06, 2021, 09:25:20 pm »
That would be super helpful. I hope it is indeed row_number causing the problem though. It seems plausible for sure but it is tricky to confirm. I wondered if we could use COUNT instead of ROW_NUMBER? But having tried that in Linux I still get the same error. It is complaining at one of the opening brackets. I'm not sure if it is the one that immediately follows row_number or one of the other ones.

I've had a go at using SQLite in Linux natively to see if the error is any more meaningful. As the screenshot shows, the table is created OK. But then row_number and count both produce the same error (this is using the native version of SQLite to my Linux system, v3.22.0 2018-01-22) but it does not tell me which bracket is the problem one

So I headed over to https://www.eversql.com/sql-syntax-check-validator/ and pasted the SQL. Please note that this SQL works flawlessly on Windows! :

Code: SQL  [Select][+][-]
  1. SELECT
  2.   ROW_NUMBER() OVER (
  3.     ORDER BY
  4.       FileName
  5.   ) rownum,
  6.   *
  7. FROM
  8.   (
  9.     SELECT
  10.       a.FileName,
  11.       a.FilePath AS FilePathA,
  12.       a.FileHash AS FileHashA,
  13.       b.FilePath AS FilePathB,
  14.       b.FileHash AS FileHashB
  15.     FROM
  16.       TBL_COMPARE_TWO_FOLDERS a
  17.       LEFT JOIN TBL_COMPARE_TWO_FOLDERS b ON a.FileName = b.FileName
  18.       AND b.FilePath = :FilePathB
  19.     WHERE
  20.       a.FilePath = :FilePathA
  21.     UNION
  22.     SELECT
  23.       c.FileName,
  24.       d.FilePath AS FilePathA,
  25.       d.FileHash AS FileHashA,
  26.       c.FilePath AS FilePathB,
  27.       c.FileHash AS FileHashB
  28.     FROM
  29.       TBL_COMPARE_TWO_FOLDERS c
  30.       LEFT JOIN TBL_COMPARE_TWO_FOLDERS d ON c.FileName = d.FileName
  31.       AND d.FilePath = :FilePathA
  32.     WHERE
  33.       c.FilePath = :FilePathB
  34.   )
  35.  

and it says :

You have an error in your SQL syntax; it seems the error is around: '* from ( select a.FileName, a.FilePath as FilePathA, a.F' at line 6

So there must be something afoot that SQLite on Windows is happy with but SQLite on Linux and OSX do not like. But I am just not good enough with SQLite to work out what. As Martin suggests, I have also asked on SO (https://stackoverflow.com/questions/67433489/sqlite-syntax-error-you-have-an-error-in-your-sql-syntax-it-seems-the-error-i) - I'll no doubt get flamed on there but hey ho. I am desperate here.

If we can simplify it, even if it has a small performance hit, but will work on the SQLite versions listed that should enable greater reach of the users, to be able to use the program.

Because we will have the same problem for OSX too. I have Big Sur (latest OSX) and it has SQLite version 3.32.3 2020-06-18
« Last Edit: May 07, 2021, 12:47:22 pm by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #9 on: May 07, 2021, 03:00:22 pm »
OK, this just gets weirder, and weirder.

On the Windows platform, I tried the new version of my program using the old version of SQLite that I used before all this great new SQL that dseligo has added. Up until the other day, when I compiled new SQLIte DLL's to v3.35.5.0, I had used former version 3.21.0.0. So I tried using the v3.21.0.0 DLL's with the new code. And on Windows, it all still works perfectly.

So I don't know how that is possible, given that row_number was added in v3.25.x, but it seems to work just fine even when using v3.21.0.0 DLLs.

The problem is, that suggests to me that the problem on Linux etc is indeed syntactical, and not related to the use or row_number, and Windows is somehow not complaining about it but Linux etc is. So now I am even more confused.


UPDATE AS BELOW : IT MUST BE VERSION RELATED
« Last Edit: May 07, 2021, 05:55:44 pm by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

dseligo

  • Sr. Member
  • ****
  • Posts: 275
Re: Is SQLite case sensitive on Linux
« Reply #10 on: May 07, 2021, 03:47:52 pm »
OK, this just gets weirder, and weirder.

On the Windows platform, I tried the new version of my program using the old version of SQLite that I used before all this great new SQL that dseligo has added. Up until the other day, when I compiled new SQLIte DLL's to v3.35.5.0, I had used former version 3.21.0.0. So I tried using the v3.21.0.0 DLL's with the new code. And on Windows, it all still works perfectly.

So I don't know how that is possible, given that row_number was added in v3.25.x, but it seems to work just fine even when using v3.21.0.0 DLLs.

The problem is, that suggests to me that the problem on Linux etc is indeed syntactical, and not related to the use or row_number, and Windows is somehow not complaining about it but Linux etc is. So now I am even more confused.

Check what version of SQlite you are using with:
Code: MySQL  [Select][+][-]
  1. select sqlite_version()

It is possible that your program picks some other DLL (with newer version).
Also, if you implement version check in your program, you can inform users if they use too old version.

dseligo

  • Sr. Member
  • ****
  • Posts: 275
Re: Is SQLite case sensitive on Linux
« Reply #11 on: May 07, 2021, 03:55:41 pm »
I just checked with DLL that is shipped with older version of Quickhash.

I get an error.

Gizmo

  • Hero Member
  • *****
  • Posts: 796
Re: Is SQLite case sensitive on Linux
« Reply #12 on: May 07, 2021, 05:43:37 pm »
You are entirely right, as ever.

1) I took your great tip, and added an SQLite version lookup to the About menu.

2) I don't know what I did earlier, but you are correct. It does not work with the original DLL's. See screenshot below (I iwll push these new changes to Github later today, to the v3.3.0 branch)

So we are back to where we were earlier - how to make a more version friendly method of the above SQL that will work on older SQLite that will be found in Linux and OSX. Obviously we cant be expected to go too far back. But I might suggest any code should work with v3.21.x (which dates back to 2017 I think) and newer which should cover users who are a couple of years into their production environments and cant easily just patch or upgrade their operating systems.

As per your PM, if you can help, I am grateful for certain when you can.
« Last Edit: May 07, 2021, 05:54:11 pm by Gizmo »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

Zvoni

  • Hero Member
  • *****
  • Posts: 589
Re: Is SQLite case sensitive on Linux
« Reply #13 on: May 10, 2021, 11:19:52 am »

You have an error in your SQL syntax; it seems the error is around: '* from ( select a.FileName, a.FilePath as FilePathA, a.F' at line 6

IIRC, you have to Alias any inner SELECT-Statement (The SELECT within the () in the FROM-Clause)

SELECT
 ROW_NUMBER() OVER(ORDER BY T1.Filename) rownum,
 T1.*,
FROM
 (Here comes your Select-Statment...
.
.
.) T1

EDIT: Forget what i said. That was in MySQL 5.5
« Last Edit: May 10, 2021, 02:50:56 pm by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

Zvoni

  • Hero Member
  • *****
  • Posts: 589
Re: Is SQLite case sensitive on Linux
« Reply #14 on: May 10, 2021, 02:47:39 pm »
So we are back to where we were earlier - how to make a more version friendly method of the above SQL that will work on older SQLite that will be found in Linux and OSX. Obviously we cant be expected to go too far back. But I might suggest any code should work with v3.21.x (which dates back to 2017 I think) and newer which should cover users who are a couple of years into their production environments and cant easily just patch or upgrade their operating systems.

As per your PM, if you can help, I am grateful for certain when you can.

EDIT: How to mimic ROW_NUMBER
https://stackoverflow.com/questions/35130545/how-to-mimic-a-row-number-functionality-without-using-row-number
Look at accepted answer
You have to find your "key"-fields to compare ">="
Do you have any sample-data?
as CSV or "ready" sqlite-db
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

 

TinyPortal © 2005-2018