[yunqa.de] Re: (DI)SQLite & case-insensitive LIKE

From: Delphi Inspiration <delphi@xxxxxxxx>
To: yunqa@xxxxxxxxxxxxx
Date: Thu, 01 Nov 2007 20:24:09 +0100
>  I'm sorry for my question, may be you can help. I have a DB (ANSI
>  1251 codepage),

Be advised to avoid codepages other than the default with DISQLite3 (UTF-8 and 
UTF-16). Some build-in SQL functions (for example LENGTH(), UPPER(), LOWER(), 
LIKE(), GLOB(), and possibly others) do not work properly with custom codepages 
unless properly overridden with correcly functioning ones.

>i want to run query via DISQLite like this:
>
>    SELECT Info.*
>    FROM Info
>    WHERE (Info.Name LIKE '%èíô%')            // cp1251 text in original
>    ORDER BY Info.Priority DESC, Info.Name',
>
>  But this LIKE statement is case-sensitive :(

Look at "PRAGMA case_sensitive_like". However, this will also not work with 
custom codepages.

>  I try to "hack" this issue like this:
>
>    SELECT Info.*
>    FROM Info
>    WHERE (UPPER(Info.Name) LIKE '%ÈÍÔ%')     // cp1251 text in upper case
>    ORDER BY Info.Priority DESC, Info.Name',
>
>  Results are terrible. Half table in result. I see built-in UPPER SQLite
>  function. As i understand, all is ok. It must uppercase all symbols,
>  not a-z only.

Do not call " UPPER('a') LIKE 'A' " but instead overwrite the LIKE function to 
work with your custom character-set.

>  So, i wrote my own UPPER function like this (it's not copy-paste, i
>  wrote this text as i remember):
>
>    procedure mUpper(context: Pointer; nArg: Integer; args: PPChar); cdecl;
>    var
>      Buf: PAnsiChar;
>      Size: Integer;
>    begin
>      Buf := StrNew(_sqlite3_value_text(args^));
>      Size := StrLen(p);
>      CharUpperBuff(Buf, Size);
>      // íà ýòîì ýòàïå îòëàäêà ïîêàçûâàåò ÷òî upper âûïîëíåí óñïåøíî
>      SQLite3_Result_Text(context, Buf, Size, 0);
>    end;
>
>    ...
>
>    begin
>     ...
>     SQLite3_Create_Function(DBHnd, 'MUPPER', 1, SQLITE_ANY, nil, @mUpper, 
> nil, nil);
>     ...
>    end;

This will not work with DISQLite3, as DISQLite3 uses the register calling 
convention for performance instead of cdecl.

>  Results are terrible. Half table (really) in result.

Here is a small project that works with DISQLite3 (Pro edition required). Make 
sure to replace CompareText() with your favourite implementation of a wildcard 
match function for your codepage.

Hint: The query optimizer can no longer use indexes if the LIKE() function is 
overloaded. The same is true for the UPPER() solution suggested above because 
both arguments of LIKE must be converted.

Ralf

---------------------------------------

{ DISQLite3 demo project to show how to override the LIKE() SQL function
  to work with a custom codepage.

  CAUTION: This demo is just a stub because a wildcard match function is
  missing from the Delphi RTL and a custom implementation must be used instead.
  Several such functions supporting slightly different syntaxes are available
  on the Internet.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2007 Ralf Junker, The Delphi Inspiration <delphi@xxxxxxxx>

------------------------------------------------------------------------------ }

program DISQLite3_Ansi_Like;

{$I DI.inc}
{$I DISQLite3.inc}
{$APPTYPE Console}

uses
  SysUtils, DISQLite3Api, DISQLite3Database;

//------------------------------------------------------------------------------

{ The SQL LIKE() function callback. This stub is for demonstration purposes
  only. It does not work properly because a wildcard match function is not
  part of the Delphi RTL. CompareText() is used instead. }
procedure Like_Ansi_Callback(
  pCtx: sqlite3_context; nArgs: Integer; Args: PPointerArray);
var
  pattern: AnsiString;
  s: AnsiString;
  Escape: AnsiString;
  r: Integer; // Match result: 1 for success, 0 for failure.
begin
  { Retrieve custom code page pattern and string. }
  pattern := sqlite3_value_str(Args[0]);
  s := sqlite3_value_str(Args[1]);

  { If the optional escape character is passed to this callback, retrieve it
    now. Note: This is for completeness only, it is not used in this demo. }
  if nArgs = 3 then
    begin
      Escape := sqlite3_value_str(Args[2]);
    end;

  { Match the string against the pattern. IMPORTANT: Replace CompareText() with
    your favourite wildcard match function - This is not implemented here
    because such a function is not part of the Delphi RTL. }
  if { !REPLACE HERE! } CompareText(pattern, s) = 0 { !REPLACE HERE! } then
    r := 1
  else
    r := 0;

  sqlite3_result_int(pCtx, r);
end;

//------------------------------------------------------------------------------

var
  DB: TDISQLite3Database;
  Stmt: TDISQLite3Statement;
begin
  try

    DB := TDISQLite3Database.Create(nil);
    try
      DB.DatabaseName := ':memory:'; // Use memory database.
      DB.CreateDatabase;
      { Register the custom codepage LIKE() callback function defined above.
        This will now be called instead of the default implementation. }
      sqlite3_create_function(DB.Handle, 'like', 2,
        SQLITE_UTF8, nil, Like_Ansi_Callback, nil, nil);
      sqlite3_create_function(DB.Handle, 'like', 3,
        SQLITE_UTF8, nil, Like_Ansi_Callback, nil, nil);
      { Create table with sample data. }
      DB.Execute('CREATE TABLE test(t TEXT);');
      DB.Execute('INSERT INTO test (t) VALUES (''one'');');
      DB.Execute('INSERT INTO test (t) VALUES (''two'');');
      { Run the LIKE query. }
      Stmt := DB.Prepare('SELECT * FROM test WHERE t LIKE ''one'';');
      try
        while Stmt.Step = SQLITE_ROW do
          WriteLn(Stmt.Column_Str(0));
      finally
        Stmt.Free;
      end;

    finally
      DB.Free;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to exit');
  ReadLn;
end.  

_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
[yunqa.de] Re: (DI)SQLite & case-insensitive LIKE

Other related posts:
