{ 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.

  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_2;

{$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
  Windows, SysUtils,
  DISQLite3Api, DISQLite3Database, DISQLite3Collations;

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

var
  DB: TDISQLite3Database;

  { Show the testdata ordered by ACollation. }
procedure Show(const ACollation: Utf8String);
var
  SQL: Utf8String;
  Stmt: TDISQLite3Statement;
begin
  WriteLn;
  SQL := 'SELECT a FROM t ORDER BY 1 COLLATE ' + ACollation + ';';
  WriteLn(SQL);

  Stmt := DB.Prepare(SQL);
  try
    while Stmt.Step = SQLITE_ROW do
      WriteLn(Stmt.column_str(0));
  finally
    Stmt.Free;
  end;
end;

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

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

    DB := TDISQLite3Database.Create(nil);
    try
      { Create a database in memory. }
      DB.DatabaseName := ':memory:';
      DB.Open;

      { Register two new collation sequences, "greek" and "greek_nocase". }
      sqlite3_create_collation(
        DB.Handle,
        'greek',
        SQLITE_UTF8,
        Pointer(LANG_GREEK),
        SQLite3_Compare_Custom_Ansi);

      sqlite3_create_collation(
        DB.Handle,
        'greek_nocase',
        SQLITE_UTF8,
        Pointer(LANG_GREEK or COMPARE_CUSTOM_IGNORE_CASE),
        SQLite3_Compare_Custom_Ansi);

      { Create a table and add some data for testing. }
      DB.Execute('CREATE TABLE t(a TEXT);');
      DB.Execute('INSERT INTO t VALUES (''AAA'');');
      DB.Execute('INSERT INTO t VALUES (''aaa'');');
      DB.Execute('INSERT INTO t VALUES (''BBB'');');
      DB.Execute('INSERT INTO t VALUES (''bbb'');');
      DB.Execute('INSERT INTO t VALUES (''CCC'');');
      DB.Execute('INSERT INTO t VALUES (''ccc'');');

      { Show data ordered by build-in "binary" collation sequence. }
      Show('binary');

      { Show data ordered by our user defined "greek" and "greek_nocase"
        collation sequences. }
      Show('greek');
      Show('greek_nocase');

    finally
      DB.Free;
    end;

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

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

