{ DISQLite3 example project showing how to create user defined collation
  sequences which can be used for advanced sorting operations.

  !!! Important: This project requires the full version of DISQLite3 since
  !!!            it uses some functions not available in DISQLite Personal.

  This demo uses the table created by the Create_Table demo. Compile and
  execute the Create_Table demo at least once before running the project.

  Visit the DISQLite3 Internet site for latest information and updates:

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

  Copyright (c) 2005-2009 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>

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

program DISQLite3_Create_Collation;

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

{$IFDEF DISQLite3_Personal}
!!! This project requires functionality unavailable in DISQLite3 Personal. !!!
!!! To compile, download DISQLite3 Pro from www.yunqa.de/delphi/           !!!
{$ENDIF DISQLite3_Personal}

{$APPTYPE CONSOLE}

uses
  {$IFDEF FastMM}FastMM4, {$ENDIF}DISystemCompat, Windows, SysUtils,
  DISQLite3Api, DISQLite3Collations,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

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

{ This is a callback function triggered for each row in the table.
  We use it to write all fields of a record to the console. }
function WriteRowCallback(UserData: Pointer; ColumnCount: Integer; ColumnValues, ColumnNames: PPUtf8CharArray): Integer;
var
  i: Integer;
begin
  for i := 0 to ColumnCount - 1 do
    begin
      if i > 0 then Write('|');
      Write(ColumnValues[i]);
    end;
  WriteLn;
  Result := 0; // Return 0 to retrieve the next record, 1 to abort.
end;

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

{ Shows the results of an SQL SELECT. }
procedure ShowSelect(const ADescription, ASQL: AnsiString);
begin
  WriteLn('* ', ADescription, ':');
  WriteLn('  ', ASQL);
  WriteLn;
  sqlite3_check(sqlite3_exec(DB, PAnsiChar(ASQL), WriteRowCallback, nil, nil), DB);
  WriteLn;
end;

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

begin
  WriteLn('SQLite Version ', sqlite3_libversion); WriteLn;

  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
    functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      Open_Demo_Database;
      try
        { Register new custom collation sequences with the database handle. }

        sqlite3_create_collation(// Compile Error? Read first comment above!
          DB, // Handle to database
          'SYSTEM', // The new collations's name.
          SQLITE_UTF8, // String encoding for function callback.
          nil, // User data.
          SQLite3_Compare_System_Ansi); // Function callback

        sqlite3_create_collation(
          DB,
          'SYSTEMNOCASE',
          SQLITE_UTF8,
          nil,
          SQLite3_Compare_System_NoCase_Ansi);

        sqlite3_create_collation(
          DB,
          'dutch',
          SQLITE_UTF8,
          Pointer(LANG_DUTCH), // Cast the LCID Locale to Pointer.
          SQLite3_Compare_Custom_Ansi);

        sqlite3_create_collation(
          DB,
          'dutch_nocase',
          SQLITE_UTF8,
          Pointer(LANG_DUTCH or COMPARE_CUSTOM_IGNORE_CASE),
          SQLite3_Compare_Custom_Ansi);

        { Run some example queries to demonstrate the functions.}

        ShowSelect(
          'Sorting by default System locale, case sensitive',
          'SELECT * FROM People ORDER BY LastName COLLATE System LIMIT 5;');

        ShowSelect(
          'Sorting by default System locale, case insensitive',
          'SELECT * FROM People ORDER BY LastName COLLATE SystemNoCase LIMIT 5;');

        ShowSelect(
          'Sorting by default System locale, case insensitive',
          'SELECT * FROM People ORDER BY LastName COLLATE dutch LIMIT 5;');

        ShowSelect(
          'Sorting by default System locale, case insensitive',
          'SELECT * FROM People ORDER BY LastName COLLATE dutch_nocase LIMIT 5;');

      finally
        Close_Demo_Database;
      end;

    finally
      { Deallocate any resources that were allocated by
        sqlite3_initialize() above. }
      sqlite3_shutdown;
    end;

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

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

