{ Project which loads and tests the "Extension_Half" extension.

  Please compile DISQLite3_Extension_Half.dpr before running this project.

  For easy debugging, open compile and run both this project and
  DISQLite3_Extension_Half.dpr from within a project group. }
program DISQLite3_Extension_Test;

{ $DEFINE API in the line below this comment to use the sqlite3_load_extension()
  API to load the extension and not the load_extension() SQL function. }
{$DEFINE API} // Default: On

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$C+} // Enable assertions on for testing.

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

uses
  {$IFDEF FastMM}{$I FastMM_uses.inc}{$ENDIF}
  DISystemCompat,
  {$IFDEF HAS_UNITSCOPE}System.SysUtils{$ELSE}SysUtils{$ENDIF},
  DISQLite3Api;

{$R ..\Yunqa.res}

const
  EXTENSION_FILES: array[0..1] of string = (
    'DISQLite3_Extension_Half.dll',
    'DISQLite3_Extension_Collate_Reverse.dll');

var
  DB: sqlite3_ptr;
  {$IFDEF API}e: PUtf8Char; {$ENDIF}
  i: Integer;
  s: string;
  Stmt: sqlite3_stmt_ptr;
begin
  {$IFDEF FastMM}{$I FastMM_init.inc}{$ENDIF}

  try
    try
      sqlite3_open(':memory:', @DB);
      try
        { Authorize extension loading: Call sqlite3_enable_load_extension(). }
        sqlite3_check(sqlite3_enable_load_extension(DB, 1), DB);

        //----------------------------------------------------------------------
        // Load the extensions.
        //----------------------------------------------------------------------

        for i := Low(EXTENSION_FILES) to High(EXTENSION_FILES) do
          begin
            {$IFDEF API}
            { Load the extension using the DISQLite3 API. }
            if sqlite3_load_extension(
              DB, PUtf8Char(sqlite3_encode_utf8(EXTENSION_FILES[i])), nil, @e) <> SQLITE_OK then
              begin
                s := 'Error loading the extension "' + EXTENSION_FILES[i] + '".' + #13#10;
                if Assigned(e) then
                  begin
                    s := s + sqlite3_decode_utf8(e^);
                    sqlite3_free(e);
                  end;
                raise Exception.Create(s);
              end;
            {$ELSE API}
            { Load the extension using the load_extension() SQL function .}
            sqlite3_exec_fast(DB, 'select load_extension(''' + EXTENSION_FILES[i] + ''')');
            {$ENDIF API}
          end;

        //----------------------------------------------------------------------
        // Test DISQLite3_Extension_Half.dll
        //----------------------------------------------------------------------

        { Simple test to verify that the extension is loaded and works well. }
        sqlite3_check(sqlite3_prepare_v2(DB, 'select half(9)', -1, @Stmt, nil), DB);
        try
          sqlite3_check(sqlite3_step(Stmt), DB);
          s := sqlite3_decode_utf8(sqlite3_column_str(Stmt, 0));
          Assert(s = '4.5');
        finally
          sqlite3_check(sqlite3_finalize(Stmt), DB);
        end;

        //----------------------------------------------------------------------
        // Test DISQLite3_Extension_Collate_Reverse.dll
        //----------------------------------------------------------------------

        sqlite3_exec_fast(DB, 'CREATE TABLE t (a text)');
        sqlite3_exec_fast(DB, 'INSERT INTO t VALUES (''a'')');
        sqlite3_exec_fast(DB, 'INSERT INTO t VALUES (''b'')');
        sqlite3_exec_fast(DB, 'INSERT INTO t VALUES (''c'')');

        sqlite3_check(sqlite3_prepare_v2(DB,
          'SELECT group_concat (a) FROM (SELECT a FROM t ORDER BY a COLLATE reverse)', -1, @Stmt, nil), DB);
        try
          sqlite3_check(sqlite3_step(Stmt), DB);
          s := sqlite3_decode_utf8(sqlite3_column_str(Stmt, 0));
          Assert(s = 'c,b,a');
        finally
          sqlite3_check(sqlite3_finalize(Stmt), DB);
        end;

      finally
        sqlite3_close(DB);
      end;

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

      WriteLn('Extensions loaded and executed with no errors.');
    except
      on e: Exception do
        WriteLn(e.Message);
    end;
  finally
    WriteLn;
    WriteLn('Done - Press ENTER to Exit');
    ReadLn;
  end;
end.

