{ An oop wrapper for the sqlite3 database library.

  Copyright (C) 2010 by Simon Ameis <simon (dot) ameis (at) web (dot) de>

  This library is free software; you can redistribute it and/or modify it
  under the terms of the GNU Library General Public License as published by
  the Free Software Foundation; either version 2 of the License, or (at your
  option) any later version with the following modification:

  As a special exception, the copyright holders of this library give you
  permission to link this library with independent modules to produce an
  executable, regardless of the license terms of these independent modules,and
  to copy and distribute the resulting executable under terms of your choice,
  provided that you also meet, for each linked independent module, the terms
  and conditions of the license of that module. An independent module is a
  module which is not derived from or based on this library. If you modify
  this library, you may extend this exception to your version of the library,
  but you are not obligated to do so. If you do not wish to do so, delete this
  exception statement from your version.

  This program is distributed in the hope that it will be useful, but WITHOUT
  ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License
  for more details.

  You should have received a copy of the GNU Library General Public License
  along with this library; if not, write to the Free Software Foundation,
  Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.


  Changelog:
  2012-01-22   Simon Ameis <simon.ameis@web.de>
  * Make TSQLite3Database and TSQLite3Statment TComponent descendants
  + Assignment operator to create statements from strings
  + TSQLite3Database.ExecuteFile()

  2010-12-25   Simon Ameis <simon.ameis@web.de>
  + TSQLite3Value
  + TSQLite3BLOB

  2010-08-10   Simon Ameis <simon.ameis@web.de>
  * Import of the old version

  TODO:
  * Base TSQLite3BLOB on TStream or create a TSQLite3BLOBStream
}

unit sqlite3obj;

{$mode objfpc}{$H+}
interface

uses
  lazutf8classes,
  Classes, SysUtils, sqlite3, strutils, typinfo, variants,
  math;

resourcestring
  rsSQLiteBLOBOpenError = 'Failed opening BLOB handle: %s';
  rsSQLiteCloseError = 'Database is busy. Please close database handle '
    +'manually.';
  rsSQLiteNoDefaulDatabase = 'Default SQLite3 database not assigned.';
  rsSQLiteStreamEmpty = 'End of stream reached.';

type
  sqlite3_int64 = type sqlite3.sqlite3_int64;

  TSQLiteDatabaseObjectType = (
    dotNothing,    // no database object with this name
    dotTable,      // table (in sqlite_master)
    dotIndex,      // index (in sqlite_master)
    dotTempTable,  // table (in sqlite_temp_master)
    dotTempIndex   // index (in sqlite_temp_master)
  );

  ESQLiteError = class(Exception);
  ESQLiteNoDefaultDatabase = class(ESQLiteError);
  ESQliteBLOBOpenError = class(ESQLiteError);
  ESQLiteStreamEmpty = class(ESQLiteError);

  { ESQLiteCloseError }

  ESQLiteCloseError = class(ESQLiteError)
  private
    FHandle: psqlite3;
  public
    constructor Create(const msg: string; aHandle: psqlite3);
    property DatabaseHandle: psqlite3 read FHandle write FHandle;
  end;

  TSQLite3Database  = class;
  TSQLite3Statement = class;
  TSQLite3BLOB      = class;

threadvar
  DefaultDatabase: TSQLite3Database;

type
{ TSQLite3Component }
  TSQLite3Component = class(TComponent)
  public
    constructor Create(aDatabase: TSQLite3Database); virtual;
    constructor Create(); virtual;
  end;

  { TSQLite3Database }

  TSQLite3Database = class(TComponent)
  private
    FHandle: psqlite3;
    FTransactionBeginStmt, FTransactionCommitStmt, FTransactionRollbackStmt: TSQLite3Statement;
    function GetOpened: Boolean;
  public
    constructor Create(aOwner: TComponent); override;
    constructor Create(aOwner: TComponent; const aDatabaseFile: string);
    constructor Create(const aDatabaseFile: String); deprecated 'Use Constructor with owner.';
    constructor Create(aOwner: TComponent; dbHandle: psqlite3);
    destructor Destroy; override;
    // class function => independent of database connection/need only library
    class function IsThreadsafe: boolean;
    class function LibVersion: String;
    class function CheckTableName(const aName: String): Boolean;
    class function LibName: String;
    procedure Open(const aFileName: TFilename);
    function Close: Boolean;
    function LastError: string;
    function LastErrorCode: Longint;
    function DBFileName(const aDBName: String): String;
    function LastInsertRowid: sqlite3_int64;
    procedure BeginTransaction;
    procedure CommitTransaction;
    procedure RollbackTranscation;
    function ExecuteSQL(const aSQL: String): Longint;
    function ExecuteFile(const aFileName: UTF8String): Longint;
    function ExecuteStream(aStream: TStream): Longint;
    function BLOBOpen(const zDB, zTable, zColumn: String; const iRow: sqlite3_int64; flags: longint): TSQLite3BLOB;
    function CheckDatabaseObject(const aName: String): TSQLiteDatabaseObjectType;
    property Handle: psqlite3 read FHandle;
    property Opened: Boolean read GetOpened;
  end;

type
  { TSQLite3Statement }

  TSQLite3Statement = class(TSQLite3Component)
  private
    FHandle: psqlite3_stmt;
    FDatabase: TSQLite3Database;
    fSQL: String;
    function GetColumnName(aColumn: longint): String;
    procedure SetSQL(const aValue: String);
  public
    constructor Create(aDatabase: TSQLite3Database); override;
    destructor Destroy; override;
    function Prepare(const aSQL: string): boolean;
    function PrepareFmt(const aSQL: String; const Args: array of const): boolean;
    procedure Finalize;
    function Step: Longint;
    function Reset: boolean;
    function ClearBindings: boolean;
    function BindBLOB(Index: longint; const Buffer; count: longint): boolean;
    function BindBoolean(Index: longint; aValue: Boolean): boolean;
    function BindString(Index: longint; const aStr: string): boolean;
    function BindInt(Index: longint; aValue: longint): boolean;
    function BindInt64(Index: longint; const aValue: int64): boolean;
    function BindDouble(Index: longint; const aValue: double): boolean;
    function BindWideString(Index: longint; const aValue: WideString): boolean;
    function BindUnicodeString(Index: longint; const aValue: UnicodeString): boolean;
    function BindZeroBLOB(Index: longint; aLength: longint): boolean;
    function Bind(values: array of const): Boolean;
    function ColumnCount: longint;
    function ColumnType(aCol: longint): longint;
    procedure ColumnToStream(aCol: longint; aStream: TStream);
    function ColumnBoolean(aCol: longint): Boolean;
    function ColumnString(aCol: longint): string;
    function ColumnString16(aCol: longint): UnicodeString;
    function ColumnInt(aCol: longint): longint;
    function ColumnInt64(aCol: longint): int64;
    function ColumnDouble(aCol: longint): double;
    function ColumnIsNULL(aCol: longint): Boolean;
    function ColumnBytes(aCol: longint): longint;
    function ColumnVariant(aCol: longint): Variant;
    procedure ValuesToObject(aObj: TObject);

    property SQL: String read fSQL write SetSQL;
    property Handle: psqlite3_stmt read FHandle;
    property ColumnNames[aColumn: longint]: String read GetColumnName;
    property Database: TSQLite3Database read FDatabase;
  end;

  (* EXPERIMENTAL

     This operator can be used to create SQLite statements by
     assigning strings to them

     var
       stmt: TSQLite3Statement;
     begin
       sqlite3obj.DefaultDatabase := TSQLite3Database.Create(nil, 'mydb.sqlite');
       stmt := 'SELECT * FROM mytable;';
       stmt.Step;
     end;

     The old statement (if there was one) can't be freed, so do it yourself!
     If you don't do so, it will be freed, when the TSQLite3Database is
     destroyed because of a TComponent ownership.
   *)
  operator := (const aSQL: String): TSQLite3Statement;

type
  { TSQLite3BLOB }

  TSQLite3BLOB = class(TObject)
  private
    FHandle: psqlite3_blob;
  public
    constructor Create(aDatabase: TSQLite3Database; const zDB, zTable, zColumn: String; const iRow: sqlite3_int64; flags: longint);
    constructor Create(aDatabase: psqlite3; const zDB, zTable, zColumn: String; const iRow: sqlite3_int64; flags: longint);
    constructor Create(aBLOBHandle: psqlite3_blob);
    destructor Destroy; override;
    function Write(const Buffer; count, iOffset: longint): longint;
    function Read(out Buffer; count, iOffset: longint): longint;
    function Bytes: longint;
    property Handle: psqlite3_blob read FHandle;
  end;

  { TSQLite3BLOBStream }

  TSQLite3BLOBStream = class(TStream)
  private
    fBLOB: TSQLite3BLOB;
    fPosition: longint;
    fOwnsBlob: Boolean;
  protected
    function GetSize: Int64; override;
    property OwnsBlob: Boolean read fOwnsBlob write fOwnsBlob;
  public
    constructor Create(aBLOB: TSQLite3BLOB; aOwnsBLOB: Boolean);
    destructor Destroy; override;
    function Seek(Offset: Longint; Origin: Word): Longint; override; overload;
    function Read(var Buffer; Count: Longint): Longint; override;
    function Write(const Buffer; Count: Longint): Longint; override;
  end;

{ defined here until defined in unit sqlite3
  http://bugs.freepascal.org/view.php?id=22678
}
  function sqlite3_db_filename(db: psqlite3; zDbName: PChar): PChar; cdecl; external Sqlite3Lib;
implementation

{ TSQLite3BLOBStream }

function TSQLite3BLOBStream.GetSize: Int64;
begin
  Result := fBLOB.Bytes;
end;

constructor TSQLite3BLOBStream.Create(aBLOB: TSQLite3BLOB; aOwnsBLOB: Boolean);
begin
  fBLOB := aBLOB;
  fOwnsBlob := aOwnsBLOB;
end;

destructor TSQLite3BLOBStream.Destroy;
begin
  if OwnsBlob then
    fBLOB.Free;
  inherited Destroy;
end;

function TSQLite3BLOBStream.Seek(Offset: Longint; Origin: Word): Longint;
begin
  case TSeekOrigin(Origin) of
    soBeginning:
      if Offset < 0 then
        InvalidSeek
      else
        fPosition := Offset;
    soEnd:
      if Offset > 0 then
        InvalidSeek
      else
        fPosition := max(0, (Size + Offset));
    soCurrent:
      fPosition := EnsureRange(Int64(fPosition) + INt64(Offset), 0, Size);
  end;
  Result := fPosition;
end;

function TSQLite3BLOBStream.Read(var Buffer; Count: Longint): Longint;
begin
  if Count < 0 then
    InvalidSeek;
  if (int64(fPosition) + INt64(Count)) < Size then  // read to maximum
    Count := Count - fPosition;
  if Count = 0 then
    exit(0);
  Result := fBLOB.Read(Buffer, Count, fPosition);
  if Result = SQLITE_OK then
    Result := Count
  else
    Result := 0;
end;

function TSQLite3BLOBStream.Write(const Buffer; Count: Longint): Longint;
begin
  if count < 0 then
    InvalidSeek;
  if (Int64(fPosition) + Int64(count)) > Size then  // write to maximum size of blob
    Count := Size - fPosition;
  if Count = 0 then
    exit(0);
  Result := fblob.Write(Buffer, Count, fPosition);
  if Result = SQLITE_OK then
    Result := Count
  else
    Result := 0
end;

{ TSQLite3Component }

constructor TSQLite3Component.Create(aDatabase: TSQLite3Database);
begin
  inherited Create(aDatabase);
end;

constructor TSQLite3Component.Create;
begin
  if not Assigned(DefaultDatabase) then
    raise ESQLiteNoDefaultDatabase.Create(rsSQLiteNoDefaulDatabase);
  Create(DefaultDatabase);
end;

{ TSQLite3BLOB }

constructor TSQLite3BLOB.Create(aDatabase: TSQLite3Database; const zDB, zTable,
  zColumn: String; const iRow: sqlite3_int64; flags: longint);
begin
  Create(aDatabase.Handle, zDB, zTable, zColumn, iRow, flags);
end;

constructor TSQLite3BLOB.Create(aDatabase: psqlite3; const zDB, zTable,
  zColumn: String; const iRow: sqlite3_int64; flags: longint);
begin
  if sqlite3_blob_open(aDatabase, PChar(zDB), PChar(zTable), PChar(zColumn), iRow, flags, @FHandle) <> SQLITE_OK then
    raise ESQliteBLOBOpenError.CreateFmt(rsSQLiteBLOBOpenError,
      [sqlite3_errmsg(aDatabase)]);
end;

constructor TSQLite3BLOB.Create(aBLOBHandle: psqlite3_blob);
begin
  FHandle := aBLOBHandle;
end;

destructor TSQLite3BLOB.Destroy;
begin
  sqlite3_blob_close(FHandle);
  inherited Destroy;
end;

function TSQLite3BLOB.Write(const Buffer; count, iOffset: longint): longint;
begin
  Result := sqlite3_blob_write(FHandle, @Buffer, count, iOffset);
end;

function TSQLite3BLOB.Read(out Buffer; count, iOffset: longint): longint;
begin
  Result :=  sqlite3_blob_read(FHandle, @Buffer, count, iOffset);
end;

function TSQLite3BLOB.Bytes: longint;
begin
  Result := sqlite3_blob_bytes(FHandle);
end;

{ TSQLite3Database }

constructor TSQLite3Database.Create(const aDatabaseFile: String);
begin
  Create(nil);
  Open(aDatabaseFile);
end;

constructor TSQLite3Database.Create(aOwner: TComponent; dbHandle: psqlite3);
begin
  Create(aOwner);
  FHandle := dbHandle;
end;

class function TSQLite3Database.IsThreadsafe: boolean;
begin
  Result := longbool(sqlite3_threadsafe());
end;

class function TSQLite3Database.LibVersion: String;
begin
  Result := sqlite3_libversion();
end;

class function TSQLite3Database.CheckTableName(const aName: String): Boolean;
begin
  // table name may not ...
  // be empty
  // start with 'sqlite_'
  // contain quote signs (")
  // see http://www.sqlite.org/lang_createtable.html
  Result := (aName <> '') and (LeftStr(aName, 7) <> 'sqlite_') and (Pos(aName, '"') < 1);
end;

class function TSQLite3Database.LibName: String;
begin
  Result := sqlite3lib;
end;

procedure TSQLite3Database.Open(const aFileName: TFilename);
begin
  if sqlite3_open(PChar(aFileName), @FHandle) <> SQLITE_OK then
  begin
    if FHandle = nil then
      OutOfMemoryError
    else
      raise ESQLiteError.Create(sqlite3_errmsg(FHandle));
  end;
end;

function TSQLite3Database.Close: Boolean;
begin
  // closing nil results in SQLITE_OK
  Result := sqlite3_close(FHandle) = SQLITE_OK;
  if Result then
    FHandle := nil;
end;

function TSQLite3Database.LastError: string;
begin
  Result := sqlite3_errmsg(FHandle);
end;

function TSQLite3Database.LastErrorCode: Longint;
begin
  Result := sqlite3_errcode(Handle);
end;

function TSQLite3Database.DBFileName(const aDBName: String): String;
var
  r: PChar;
begin
  Result := sqlite3_db_filename(FHandle, PChar(aDBName));
end;

function TSQLite3Database.LastInsertRowid: sqlite3_int64;
begin
  Result := sqlite3_last_insert_rowid(FHandle);
end;

procedure TSQLite3Database.BeginTransaction;
begin
  if FTransactionBeginStmt = nil then
  begin
    FTransactionBeginStmt := TSQLite3Statement.Create(Self);
    FTransactionBeginStmt.Prepare('BEGIN');
  end;
  FTransactionBeginStmt.Step;
  FTransactionBeginStmt.Reset;
end;

procedure TSQLite3Database.CommitTransaction;
begin
  if FTransactionCommitStmt = nil then
  begin
    FTransactionCommitStmt := TSQLite3Statement.Create(Self);
    FTransactionCommitStmt.Prepare('COMMIT');
  end;
  FTransactionCommitStmt.Step;
  FTransactionCommitStmt.Reset;
end;

procedure TSQLite3Database.RollbackTranscation;
begin
  if FTransactionRollbackStmt = nil then
  begin
    FTransactionRollbackStmt := TSQLite3Statement.Create(Self);
    FTransactionRollbackStmt.Prepare('ROLLBACK');
  end;
  FTransactionRollbackStmt.Step;
  FTransactionRollbackStmt.Reset;
end;

function TSQLite3Database.ExecuteSQL(const aSQL: String): Longint;
begin
  Result := sqlite3_exec(FHandle, PChar(aSQL), nil, nil, nil);
end;

function TSQLite3Database.ExecuteFile(const aFileName: UTF8String): Longint;
(* Despite of the fact, that this method does not use the sqlite3 library
   itself to access a file but the pascal stream objects, it requires an
   UTF-8 file name.

   This is because sqlite3 does not support single byte character sets at all.
   All filenames are required in Unicode (UTF-8 or UTF-16 LE/BE). With this
   workaround the whole unit uses UTF-8 strings and file names.
*)
var
  f: TFileStreamUTF8;
begin
  f := TFileStreamUTF8.Create(aFileName, fmOpenRead or fmShareDenyWrite);
  try
    Result := ExecuteStream(f);
  finally
    f.Free;
  end;
end;

function TSQLite3Database.ExecuteStream(aStream: TStream): Longint;
var
  sql: String;
  l: Sizeint;
begin
  l := aStream.Size - aStream.Position;
  if l = 0 then
    raise ESQLiteStreamEmpty.Create(rsSQLiteStreamEmpty);

  SetLength(sql, l);
  astream.Read(sql[1], l);
  Result := ExecuteSQL(sql);
end;

function TSQLite3Database.BLOBOpen(const zDB, zTable, zColumn: String;
  const iRow: sqlite3_int64; flags: longint): TSQLite3BLOB;
begin
  Result := TSQLite3BLOB.Create(Self, zDB, zTable, zColumn, iRow, flags);
end;

function TSQLite3Database.CheckDatabaseObject(const aName: String
  ): TSQLiteDatabaseObjectType;
const
  selMaster: TSQLite3Statement = nil;
  selTempMaster: TSQLite3Statement = nil;
begin
  Result := dotNothing;
  if not Assigned(selMaster) then
  begin
    selMaster := TSQLite3Statement.Create(Self);
    selMaster.SQL := 'SELECT type FROM sqlite_master WHERE name = ?1;';
  end;
  if not Assigned(selTempMaster) then
  begin
    selTempMaster := TSQLite3Statement.Create(Self);
    selTempMaster.SQL := 'SELECT type FROM sqlite_temp_master WHERE name = ?1;';
  end;
  selMaster.BindString(1, aName);
  if selMaster.Step = SQLITE_ROW then
  begin
    case selMaster.ColumnString(0) of
      'table': Result := dotTable;
      'index': Result := dotIndex;
    end;
  end else
  begin
    if selTempMaster.Step = SQLITE_ROW then
    case selMaster.ColumnString(0) of
      'table': Result := dotTempTable;
      'index': Result := dotTempIndex;
    end;
  end;
  selMaster.Reset;
  selTempMaster.Reset;
end;

function TSQLite3Database.GetOpened: Boolean;
begin
  Result := Assigned(FHandle);
end;

constructor TSQLite3Database.Create(aOwner: TComponent);
begin
  inherited Create(aOwner);
end;

constructor TSQLite3Database.Create(aOwner: TComponent;
  const aDatabaseFile: string);
begin
  Create(aOwner);
  Open(aDatabaseFile);
end;

destructor TSQLite3Database.Destroy;
var
  i: Integer;
begin
  // free transaction objects
  FreeAndNil(FTransactionBeginStmt);
  FreeAndNil(FTransactionCommitStmt);
  FreeAndNil(FTransactionRollbackStmt);

  // reverse order; we will delete some objects
  for i := ComponentCount - 1 downto 0 do
    if Components[i] is TSQLite3Component then
      Components[i].Free;

  // checking for nil is not required
  if not Self.Close then
    raise ESQLiteCloseError.Create(rsSQLiteCloseError, FHandle);
  inherited Destroy;
end;

{ ESQLiteCloseError }

constructor ESQLiteCloseError.Create(const msg: string; aHandle: psqlite3);
begin
  inherited Create(msg);
  FHandle := aHandle;
end;

{ TSQLite3Statement }

procedure TSQLite3Statement.SetSQL(const aValue: String);
begin
  if not Prepare(aValue) then
    raise ESQLiteError.CreateFmt('Prepare returned error code %d.', [self.Database.LastErrorCode]);
end;

constructor TSQLite3Statement.Create(aDatabase: TSQLite3Database);
begin
  inherited Create(aDatabase);
  FDatabase := aDatabase;
end;

destructor TSQLite3Statement.Destroy;
begin
  if FHandle <> nil then
    Finalize;
  inherited Destroy;
end;

function TSQLite3Statement.Prepare(const aSQL: string): boolean;
begin
  Finalize;
  Result := sqlite3_prepare_v2(FDatabase.Handle, PChar(aSQL), Length(
    aSQL) + 1, @FHandle, nil) = SQLITE_OK;
  fSQL := aSQL
end;

function TSQLite3Statement.PrepareFmt(const aSQL: String;
  const Args: array of const): boolean;
begin
  Result := Prepare(Format(aSQL, Args));
end;

procedure TSQLite3Statement.Finalize;
begin
  sqlite3_finalize(FHandle);
  FHandle := nil;
  fSQL := '';
end;

function TSQLite3Statement.Step: Longint;
begin
  Result := sqlite3_step(FHandle);
end;

function TSQLite3Statement.Reset: boolean;
begin
  Result := sqlite3_reset(FHandle) = SQLITE_OK;
end;

function TSQLite3Statement.ClearBindings: boolean;
begin
  Result := sqlite3_clear_bindings(FHandle) = SQLITE_OK;
end;

function TSQLite3Statement.BindBLOB(Index: longint; const Buffer; count: longint
  ): boolean;
begin
  Result := sqlite3_bind_blob(FHandle, Index, @Buffer, count,
    sqlite3_destructor_type(SQLITE_TRANSIENT)) = SQLITE_OK;
end;

function TSQLite3Statement.BindBoolean(Index: longint; aValue: Boolean
  ): boolean;
begin
  Result := BindInt(Index, ord(aValue));
end;

function TSQLite3Statement.BindString(Index: longint; const aStr: string): boolean;
begin
  Result := sqlite3_bind_text(FHandle, Index, PChar(aStr), Length(aStr),
    sqlite3_destructor_type(SQLITE_TRANSIENT)) = SQLITE_OK;
end;

function TSQLite3Statement.BindInt(Index: longint; aValue: longint): boolean;
begin
  Result := sqlite3_bind_int(FHandle, Index, aValue) = SQLITE_OK;
end;

function TSQLite3Statement.BindInt64(Index: longint; const aValue: int64): boolean;
begin
  Result := sqlite3_bind_int64(FHandle, Index, aValue) = SQLITE_OK;
end;

function TSQLite3Statement.BindDouble(Index: longint; const aValue: double): boolean;
begin
  Result := sqlite3_bind_double(FHandle, Index, aValue) = SQLITE_OK;
end;

function TSQLite3Statement.BindWideString(Index: longint;
  const aValue: WideString): boolean;
begin
  Result := sqlite3_bind_text16(Handle, Index, PWideChar(aValue), length(aValue) * Sizeof(WideChar), sqlite3_destructor_type(SQLITE_TRANSIENT)) = SQLITE_OK;
end;

function TSQLite3Statement.BindUnicodeString(Index: longint;
  const aValue: UnicodeString): boolean;
begin
  Result := sqlite3_bind_text16(Handle, Index, PUnicodeChar(aValue), length(aValue) * Sizeof(WideChar), sqlite3_destructor_type(SQLITE_TRANSIENT)) = SQLITE_OK;
end;

function TSQLite3Statement.BindZeroBLOB(Index: longint; aLength: longint
  ): boolean;
begin
  Result := sqlite3_bind_zeroblob(FHandle, Index, aLength) = SQLITE_OK;
end;

function TSQLite3Statement.Bind(values: array of const): Boolean;
var
  i: Sizeint;
begin
  Result := True;
  for i := Low(values) to high(values) do
  begin
   if not Result then
     exit;
    case values[i].VType of
      vtInteger       :
        Result := BindInt(i+1, values[i].VInteger);
      vtBoolean       :
        Result := BindInt(i+1, Byte(values[i].VBoolean));

      // 64 bit integer types
      vtCurrency      ,
      vtInt64         ,
      vtQWord         :
          Result := BindInt64(i+1, values[i].VInt64^);

{$ifndef FPUNONE}
      vtExtended      :
          Result := BindDouble(i+1, values[i].VExtended^);
{$endif}

      vtChar          :
        Result := BindString(i+1, values[i].VChar);
      vtString        :
        Result := BindString(i+1, values[i].VString^);
      vtPChar         :
        Result := BindString(i+1, values[i].VPChar);
      vtAnsiString    :
        Result := BindString(i+1, String(values[i].VAnsiString));

      vtPointer       ,
      vtObject        ,
      vtClass         ,
      vtVariant       ,
      vtInterface     :
        raise ESQLiteError.CreateFmt('Invalid variable type %d at array position for automatic binding.', [values[i].VType, i]);

      vtWideChar      :
        Result := BindWideString(i+1, values[i].VWideChar);
      vtPWideChar     :
        Result := BindWideString(i+1, values[i].VPWideChar);
      vtWideString    :
        Result := BindWideString(i+1, WideString(values[i].VWideString));
      vtUnicodeString :
        Result := BindWideString(i+1, UnicodeString(values[i].VUnicodeString));
    end;
  end;
end;

function TSQLite3Statement.ColumnCount: longint;
begin
  Result := sqlite3_column_count(FHandle);
end;

function TSQLite3Statement.ColumnType(aCol: longint): longint;
begin
  Result := sqlite3_column_type(self.Handle, aCol);
end;

procedure TSQLite3Statement.ColumnToStream(aCol: longint; aStream: TStream);
var
  p: PByte;
  l: sizeint;
begin
  p := sqlite3_column_blob(self.Handle, aCol);
  l := ColumnBytes(aCol);
  aStream.WriteBuffer(p^, l);
end;

function TSQLite3Statement.ColumnBoolean(aCol: longint): Boolean;
begin
  Result := QWordBool(ColumnInt64(acol));
end;

function TSQLite3Statement.ColumnString(aCol: longint): string;
begin
  Result := sqlite3_column_text(FHandle, aCol);
end;

function TSQLite3Statement.ColumnString16(aCol: longint): UnicodeString;
begin
  Result := sqlite3_column_text16(FHandle, aCol);
end;

function TSQLite3Statement.ColumnInt(aCol: longint): longint;
begin
  Result := sqlite3_column_int(FHandle, aCol);
end;

function TSQLite3Statement.ColumnInt64(aCol: longint): int64;
begin
  Result := sqlite3_column_int64(FHandle, aCol);
end;

function TSQLite3Statement.ColumnDouble(aCol: longint): double;
begin
  Result := sqlite3_column_double(FHandle, aCol);
end;

function TSQLite3Statement.ColumnIsNULL(aCol: longint): Boolean;
begin
  Result := ColumnType(aCol) = SQLITE_NULL;
end;

function TSQLite3Statement.ColumnBytes(aCol: longint): longint;
begin
  Result := sqlite3_column_bytes(self.Handle, aCol);
end;

function TSQLite3Statement.ColumnVariant(aCol: longint): Variant;
var
  stype: LongInt;
  rint64: Int64;
  rlongi: longint;
  rstr: String;
begin
  stype := ColumnType(aCol);
  case stype of
    SQLITE_INTEGER:
    begin
      rint64 := ColumnInt64(aCol);
      rlongi := rint64;
      if rlongi = rint64 then
        Result := rlongi
      else
        Result := rint64
    end;
    SQLITE_FLOAT:
      Result := ColumnDouble(aCol);
    SQLITE3_TEXT:
    begin
     rstr := ColumnString(aCol);
     if length(rstr) = 1 then
       Result := rstr[1]
     else
       Result := rstr;
    end;
    SQLITE_BLOB:
      Result := ColumnString(aCol);
    SQLITE_NULL:
      Result := Null;
  end;
end;

procedure TSQLite3Statement.ValuesToObject(aObj: TObject);
var
  i: Integer;
  fieldname: String;
  stype: LongInt;
  pinfo: PPropInfo;
begin
  for i := 0 to self.ColumnCount - 1 do
  begin
    fieldname := self.ColumnNames[i];
    stype := ColumnType(i);
    case stype of
      SQLITE_INTEGER:
        SetPropValue(aObj, fieldname, ColumnInt64(i));
      SQLITE_FLOAT:
        SetFloatProp(aObj, fieldname, ColumnDouble(i));
      SQLITE3_TEXT:
        begin
          pinfo := GetPropInfo(aObj, fieldname);
          case pinfo^.PropType^.Kind of
            tkWString, tkWChar, tkUString,tkUChar:
              SetPropValue(aObj, fieldname, ColumnString16(i));
          else
            SetPropValue(aObj, fieldname, ColumnString(i));
          end;
        end;
      SQLITE_BLOB, SQLITE_NULL:
        SetPropValue(aObj, fieldname, ColumnString(i));
    end;
  end;
end;

function TSQLite3Statement.GetColumnName(aColumn: longint): String;
begin
  Result := String(sqlite3_column_name(self.Handle, aColumn));
end;

operator := (const aSQL: String): TSQLite3Statement;
begin
  if not Assigned(DefaultDatabase) then
    raise ESQLiteNoDefaultDatabase.Create(rsSQLiteNoDefaulDatabase);
  Result := TSQLite3Statement.Create(DefaultDatabase);
  Result.Prepare(aSQL);
end;

end.