{ DISQLite3 example project showing how to create user defined functions which
  can will become of the SQL syntax and can be executed just like normal SQL
  functions.

  !!! 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_Function;

{$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, SysUtils, DISQLite3Api,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

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

{ This Pascal function implements the DISQLite3 POS function. }
procedure Pos_Function_Callback(pCtx: sqlite3_context; nArgs: Integer; Args: PPointerArray);
var
  Arg1, Arg2: Utf8String;
  p: Integer;
begin
  { Retrieve the two string arguments. }
  Arg1 := sqlite3_value_str(Args[0]); // Compile Error? Read first comment above!
  Arg2 := sqlite3_value_str(Args[1]);
  { Determine the POSition. }
  p := Pos(Arg1, Arg2);
  { Set the result value. }
  sqlite3_result_int(pCtx, p);
end;

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

{ This Pascal function implements the DISQLite3 REPLACE function. }
procedure Replace_Function_Callback(pCtx: sqlite3_context; nArgs: Integer; Args: PPointerArray);
var
  Arg1, Arg2, Arg3: String;
  s8: Utf8String;
begin
  { Retrieve the arguments as strings. }
  Arg1 := sqlite3_decode_utf8(sqlite3_value_str(Args[0]));
  Arg2 := sqlite3_decode_utf8(sqlite3_value_str(Args[1]));
  Arg3 := sqlite3_decode_utf8(sqlite3_value_str(Args[2]));
  { Replace all substrings. }
  s8 := sqlite3_encode_utf8 (StringReplace(Arg1, Arg2, Arg3, [rfReplaceAll]));
  { Return the result. }
  sqlite3_result_text(pCtx, Pointer(s8), Length(s8), SQLITE_TRANSIENT);
end;

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

{ 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;
  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 user functions with the database handle. }

        { The POS function returns the position of the first character in a
          specified substring that occurs in a given string. }
        sqlite3_create_function(
          DB, // Handle to database
          'POS', // The new function's name.
          2, // Number of function arguments.
          SQLITE_UTF8, // Preferred string encoding for function callback.
          nil, // User data.
          Pos_Function_Callback, // Function callback
          nil, // Step callback, not needed.
          nil); // Final callback, not needed.

        { The REPLACE function replaces substrings within a string. }
        sqlite3_create_function(
          DB,
          'REPLACE',
          3,
          SQLITE_UTF8,
          nil,
          Replace_Function_Callback,
          nil,
          nil);

        { Run some example queries to demonstrate the functions.}

        { We use POS to search for a space character in FirstName which
          separates 2 first names. }
        ShowSelect(
          'People with 2 first names determined by POS',
          'SELECT * FROM People WHERE POS('' '', FirstName) > 0 LIMIT 5;');

        { Demonstrate the REPLACE function. For simplicity, this SELECT does not
          return any data, but the REPLACE function can just as well be used with
          all kind of data operations. }
        ShowSelect('Simple, no data REPLACE',
          'SELECT REPLACE (''Text with string to replace.'', ''string'', ''nothing'');');

      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.

