Posts by vaclav

vaclav · mORMot 1

Thanks, works as expected!

vaclav · mORMot 1

Functions Utf16SQLCompCase and Utf16SQLCompNoCase provide incorrect result for {$ifdef MSWINDOWS} when one or both input strings are empty (s1Len = 0 and/or s2Len = 0):

Utf16SQLCompCase(nonempty, empty) = -2 (should be 1!!! (see WideCompareText))
Utf16SQLCompCase(empty, nonempty) = -2 (should be -1!!! (see WideCompareText); fortunately, for SQLite -1 or -2 makes no difference)
Utf16SQLCompCase(empty, empty) = -2 (should be 0!!! (see WideCompareText))

Therefore indexes do not reach their value, which is - simply fatal...

select * from Table where IndexedField like 'value' => value normally found
select * from Table where IndexedField = 'value' => nothing found!!!

I propose the following fix:

function Utf16SQLCompCase(CollateParam: pointer; s1Len: integer; S1: pointer;
    s2Len: integer; S2: pointer) : integer; {$ifndef SQLITE3_FASTCALL}cdecl;{$endif}
{$ifndef MSWINDOWS}
var W1,W2: WideString;
{$endif}
begin
  if (s1Len<=0) and (s2Len<=0) then
    result:=0
  else if s1Len<=0 then
    result:=-1
  else if s2Len<=0 then
    result:=1
  else begin
    {$ifdef MSWINDOWS}
    result := CompareStringW(GetThreadLocale,0,S1,s1len shr 1,S2,s2Len shr 1)-2;
    {$else} // cross-platform but slower version using two temporary WideString
    SetString(W1,PWideChar(S1),s1len shr 1);
    SetString(W2,PWideChar(S2),s2len shr 1);
    result := WideCompareStr(W1,W2); // use OS string comparison API
    {$endif}
  end;
end;

function Utf16SQLCompNoCase(CollateParam: pointer; s1Len: integer; s1: pointer;
    s2Len: integer; s2: pointer) : integer; {$ifndef SQLITE3_FASTCALL}cdecl;{$endif}
{$ifndef MSWINDOWS}
var W1,W2: WideString;
{$endif}
begin
  if (s1Len<=0) and (s2Len<=0) then
    result:=0
  else if s1Len<=0 then
    result:=-1
  else if s2Len<=0 then
    result:=1
  else begin
    {$ifdef MSWINDOWS}
    result := CompareStringW(GetThreadLocale,NORM_IGNORECASE,S1,s1len shr 1,S2,s2Len shr 1)-2;
    {$else} // cross-platform but slower version using two temporary WideString
    SetString(W1,PWideChar(S1),s1len shr 1);
    SetString(W2,PWideChar(S2),s2len shr 1);
    result := WideCompareText(W1,W2); // use OS string comparison API
    {$endif}
  end;
end;

Tested on Delphi 2006 and Windows 10.

vaclav · mORMot 1

The solution seems to be making changes in SynSQLite3.pas (CompareStringW needs unicode character count, not bytes count):

function Utf16SQLCompCase(...)
begin
  ...
  result := CompareStringW(GetThreadLocale, 0, S1, S1len div 2, S2, S2Len div 2) - 2;
  ...
end;

function Utf16SQLCompNoCase(...)
begin
  ...
  result := CompareStringW(GetThreadLocale, NORM_IGNORECASE, S1, S1len div 2, S2, S2Len div 2) - 2;
  ...
end;

Please, could you verify my thoughts and implement the correction?

vaclav · mORMot 1

Hello Arnaud,
for very specific strings (like 'd:') the FillPrepare doesn't work correctly under WIN32CASE or WIN32NOCASE collation. The result of the following program is TRUE, but when activating

aModel.SetCustomCollationForAllRawUTF8('WIN32NOCASE');

the result is FALSE.
Maybe it could be connected with SQL WHERE ':(...):' query envelope. But I need the Name field values like 'd:', i.e. folder names.
Thank you in advance for any idea!

program Demo;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  SynCommons,
  mORMot,
  mORMotSQlite3,
  SynSQLite3,
  SynSQLite3Static;

const
  aDatabase = 'D:\demo.db3';

type
  TSQLFolder = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 index 1024 read fName write fName;
  end;

var
  aModel: TSQLModel;
  aServer: TSQLRestServerDB;
  aFolder: TSQLFolder;

begin
  DeleteFile(aDatabase);
  aModel := TSQLModel.Create([TSQLFolder]);
  try
    //aModel.SetCustomCollationForAllRawUTF8('WIN32NOCASE'); // the same for WIN32CASE
    aServer := TSQLRestServerDB.Create(aModel, aDatabase);
    aFolder := TSQLFolder.Create;
    try
      aServer.CreateMissingTables;
      aFolder.Name := StringToUTF8('d:');
      aServer.Add(aFolder, True);
      aFolder.FillPrepare(aServer, 'Name = ?', [StringToUTF8('d:')]);
      Write(aFolder.FillOne);
      Readln;
    finally
      aFolder.Free;
      aServer.Free;
    end;
  finally
    aModel.Free;
  end;
end.

mORMot Open Source

#1 Re: mORMot 1 » Problem with WIN32CASE and WIN32NOCASE collations » 2016-04-18 21:07:36

#2 mORMot 1 » Problem with WIN32CASE and WIN32NOCASE collations » 2016-04-18 17:29:17

#3 Re: mORMot 1 » FillPrepare doesn't work correctly for WIN32* collation » 2015-03-22 20:31:36

#4 mORMot 1 » FillPrepare doesn't work correctly for WIN32* collation » 2015-02-24 14:29:09

Board footer