fpc/packages/fcl-db/src/sqlite/customsqliteds.pas

unit customsqliteds;

{
    This is TCustomSqliteDataset, a TDataset descendant class for use with fpc compiler
    Copyright (C) 2004  Luiz Américo Pereira Câmara
    Email: [email protected]

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation; either version 2.1 of the License, or
    (at your option) any later 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 Lesser General Public License for more details.

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

}

{$Mode ObjFpc}
{$H+}
{.$Define DEBUG_SQLITEDS}
{.$Define DEBUGACTIVEBUFFER}

interface

uses
  Classes, SysUtils, Db;

type
  PDataRecord = ^DataRecord;
  PPDataRecord = ^PDataRecord;
  DataRecord = record
    Row: PPchar;
    BookmarkFlag: TBookmarkFlag;
    Next: PDataRecord;
    Previous: PDataRecord;
  end;
  
  TDSStream = class(TStream)
  private
    FActiveItem:PDataRecord;
    FFieldRow:PChar;  
    FFieldIndex:Integer;
    FRowSize: Integer;
    FPosition: Longint;
  public
    constructor Create(const ActiveItem: PDataRecord; FieldIndex:Integer);
    function Write(const Buffer; Count: Longint): Longint; override;
    function Read(var Buffer; Count: Longint): Longint; override;
    function Seek(Offset: Longint; Origin: Word): Longint; override;
  end;

  //callback types
  TSqliteCdeclCallback = function (UserData:Pointer; Count:LongInt; Values:PPChar; Names:PPChar):LongInt;cdecl;
  TSqliteCallback = function (UserData:Pointer; Count:LongInt; Values:PPChar; Names:PPChar):LongInt of Object;
  TCallbackInfo = record
    Proc: TSqliteCallback;
    Data: Pointer;
  end;
  PCallbackInfo = ^TCallbackInfo;
  
  TRecordState = (rsAdded,rsDeleted,rsUpdated);
  TRecordStateSet = set of TRecordState;
  TQueryUpdatesCallback = procedure (UserData: Pointer; Values: PPChar; ABookmark: TBookmark; RecordState: TRecordState) of Object;

  TGetSqlStrFunction = function (APChar: PChar): String;

  { TCustomSqliteDataset }

  TCustomSqliteDataset = class(TDataSet)
  private
    {$ifdef DEBUGACTIVEBUFFER}
    FFCurrentItem: PDataRecord;
    {$else}
    FCurrentItem: PDataRecord;
    {$endif}
    FInternalActiveBuffer: PDataRecord;
    FInsertBookmark: PDataRecord;
    FExpectedAppends: Integer;
    FExpectedDeletes: Integer;
    FExpectedUpdates: Integer;
    FOnCallback: TSqliteCallback;
    FSaveOnClose: Boolean;
    FSaveOnRefetch: Boolean;
    FAutoIncrementKey: Boolean;
    FMasterLink: TMasterDataLink;
    FIndexFieldNames: String;
    FIndexFieldList: TList;
    FSqlList:TStrings;
    procedure CopyCacheToItem(AItem: PDataRecord);
    function GetIndexFields(Value: Integer): TField;
    procedure UpdateIndexFields;
    function FindRecordItem(StartItem: PDataRecord; const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions; DoResync:Boolean):PDataRecord;
  protected
    FPrimaryKey: String;
    FPrimaryKeyNo: Integer;
    FFileName: String;
    FSql: String;
    FTableName: String;
    FSelectSqlStr: String;
    FAutoIncFieldNo: Integer;
    FNextAutoInc:Integer;
    FUpdatedItems: TFPList;
    FAddedItems: TFPList;
    FDeletedItems: TFPList;
    FReturnCode: Integer;
    FSqliteHandle: Pointer;
    FDataAllocated: Boolean;
    FRowBufferSize: Integer;
    FRowCount: Integer;
    FRecordCount: Integer;
    FBeginItem: PDataRecord;
    FEndItem: PDataRecord;
    FCacheItem: PDataRecord;
    FGetSqlStr: array of TGetSqlStrFunction;
    function SqliteExec(Sql:PChar; ACallback: TSqliteCdeclCallback; Data: Pointer):Integer;virtual; abstract;
    procedure InternalCloseHandle;virtual;abstract;
    function InternalGetHandle: Pointer; virtual; abstract;
    procedure GetSqliteHandle;
    function GetSqliteVersion: String; virtual; abstract;
    procedure BuildLinkedList; virtual; abstract;
    procedure FreeItem(AItem: PDataRecord);
    procedure DisposeLinkedList;
    procedure SetDetailFilter;
    procedure MasterChanged(Sender: TObject);
    procedure MasterDisabled(Sender: TObject);
    procedure SetMasterFields(Value:String);
    function GetMasterFields:String;
    procedure SetMasterSource(Value: TDataSource);
    function GetMasterSource:TDataSource;
    procedure SetFileName(Value: String);
    function GetRowsAffected:Integer; virtual;abstract;
    //TDataSet overrides
    function AllocRecordBuffer: PChar; override;
    function CreateBlobStream(Field: TField; Mode: TBlobStreamMode): TStream; override;
    procedure DoAfterInsert; override;
    procedure DoBeforeInsert; override;
    procedure FreeRecordBuffer(var Buffer: PChar); override;
    procedure GetBookmarkData(Buffer: PChar; Data: Pointer); override;
    function GetBookmarkFlag(Buffer: PChar): TBookmarkFlag; override;
    function GetRecord(Buffer: PChar; GetMode: TGetMode; DoCheck: Boolean): TGetResult; override;
    function GetRecordCount: Integer; override;
    function GetRecNo: Integer; override;
    function GetRecordSize: Word; override; 
    procedure InternalAddRecord(Buffer: Pointer; DoAppend: Boolean); override;
    procedure InternalClose; override;
    procedure InternalCancel; override;
    procedure InternalDelete; override;
    procedure InternalEdit; override;
    procedure InternalFirst; override;
    procedure InternalGotoBookmark(ABookmark: Pointer); override;
    procedure InternalInitRecord(Buffer: PChar); override;
    procedure InternalLast; override;
    procedure InternalOpen; override;
    procedure InternalPost; override;
    procedure InternalSetToRecord(Buffer: PChar); override;
    function IsCursorOpen: Boolean; override;
    procedure SetBookmarkData(Buffer: PChar; Data: Pointer); override;
    procedure SetBookmarkFlag(Buffer: PChar; Value: TBookmarkFlag); override;
    procedure SetExpectedAppends(AValue:Integer);
    procedure SetExpectedUpdates(AValue:Integer);
    procedure SetExpectedDeletes(AValue:Integer);
    procedure SetFieldData(Field: TField; Buffer: Pointer); override;
    procedure SetFieldData(Field: TField; Buffer: Pointer; NativeFormat: Boolean); override;
    procedure SetRecNo(Value: Integer); override;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function BookmarkValid(ABookmark: TBookmark): Boolean; override;
    function CompareBookmarks(Bookmark1, Bookmark2: TBookmark): Longint; override;
    function GetFieldData(Field: TField; Buffer: Pointer): Boolean; override;
    function GetFieldData(Field: TField; Buffer: Pointer; NativeFormat: Boolean): Boolean; override;
    function Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions) : Boolean; override;
    function LocateNext(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions) : Boolean;
    function Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant;override;
    // Additional procedures
    function ApplyUpdates: Boolean;
    function CreateTable: Boolean;
    function CreateTable(const ATableName: String): Boolean;
    procedure ExecCallback(const ASql: String; UserData: Pointer = nil);
    procedure ExecSQL;
    procedure ExecSQL(const ASql:String);
    procedure ExecSQLList;
    procedure ExecuteDirect(const ASql: String);virtual;abstract;
    procedure QueryUpdates(RecordStates: TRecordStateSet; Callback: TQueryUpdatesCallback; UserData: Pointer=nil);
    function QuickQuery(const ASql:String):String;overload;
    function QuickQuery(const ASql:String;const AStrList: TStrings):String;overload;
    function QuickQuery(const ASql:String;const AStrList: TStrings;FillObjects:Boolean):String;virtual;abstract;overload;
    procedure RefetchData;
    function ReturnString: String; virtual;abstract;
    function TableExists: Boolean;
    function TableExists(const ATableName:String):Boolean;
    function UpdatesPending: Boolean;
    {$ifdef DEBUGACTIVEBUFFER}
    procedure SetCurrentItem(Value:PDataRecord);
    property FCurrentItem: PDataRecord read FFCurrentItem write SetCurrentItem;
    {$endif}
    property ExpectedAppends: Integer read FExpectedAppends write SetExpectedAppends;
    property ExpectedUpdates: Integer read FExpectedUpdates write SetExpectedUpdates;
    property ExpectedDeletes: Integer read FExpectedDeletes write SetExpectedDeletes;
    property IndexFields[Value: Integer]: TField read GetIndexFields;
    property RowsAffected: Integer read GetRowsAffected;
    property ReturnCode: Integer read FReturnCode;
    property SqliteHandle: Pointer read FSqliteHandle;
    property SqliteVersion: String read GetSqliteVersion;
    property SQLList:TStrings read FSqlList;
   published
    property AutoIncrementKey: Boolean read FAutoIncrementKey write FAutoIncrementKey;
    property IndexFieldNames: string read FIndexFieldNames write FIndexFieldNames;
    property FileName: String read FFileName write SetFileName;
    property OnCallback: TSqliteCallback read FOnCallback write FOnCallback;
    property PrimaryKey: String read FPrimaryKey write FPrimaryKey;
    property SaveOnClose: Boolean read FSaveOnClose write FSaveOnClose; 
    property SaveOnRefetch: Boolean read FSaveOnRefetch write FSaveOnRefetch;
    property SQL: String read FSql write FSql;
    property TableName: String read FTableName write FTableName;   
    property MasterSource: TDataSource read GetMasterSource write SetMasterSource;
    property MasterFields: string read GetMasterFields write SetMasterFields;
    
    property Active;
       
    //Events
    property BeforeOpen;
    property AfterOpen;
    property BeforeClose;
    property AfterClose;
    property BeforeInsert;
    property AfterInsert;
    property BeforeEdit;
    property AfterEdit;
    property BeforePost;
    property AfterPost;
    property BeforeCancel;
    property AfterCancel;
    property BeforeDelete;
    property AfterDelete;
    property BeforeScroll;
    property AfterScroll;
    property BeforeRefresh;
    property AfterRefresh;
    property OnDeleteError;
    property OnEditError;
    property OnNewRecord;
    property OnPostError;
  end;
  
  function Num2SqlStr(APChar: PChar): String;
  function Char2SqlStr(APChar: PChar): String;


implementation

uses
  strutils, variants, dbconst;

const
  //sqlite2.x.x and sqlite3.x.x define these constants equally
  SQLITE_OK = 0;
  SQLITE_ROW = 100;
  
  NullString = 'NULL';
  

function CallbackDispatcher(UserData:Pointer; Count:LongInt; Values:PPchar; Names:PPchar):LongInt;cdecl;
begin
  with PCallbackInfo(UserData)^ do
    Result:= Proc(Data,Count,Values,Names);
end;
  
function Num2SqlStr(APChar: PChar): String;
begin
  if APChar = nil then
  begin
    Result:=NullString;
    Exit;
  end;
  Result:=StrPas(APChar);
end;

function Char2SqlStr(APChar: PChar): String;
begin
  if APChar = nil then
  begin
    Result:=NullString;
    Exit;
  end;
  //todo: create custom routine to directly transform PChar -> SQL str
  Result:=StrPas(APChar);
  if Pos('''',Result) > 0 then
    Result:=AnsiReplaceStr(Result,'''','''''');
  Result:=''''+Result+'''';
end;

// TDSStream

constructor TDSStream.Create(const ActiveItem: PDataRecord; FieldIndex:Integer);
begin
  inherited Create;
  FPosition:=0;
  FActiveItem:=ActiveItem;
  FFieldIndex:=FieldIndex;
  FFieldRow:=ActiveItem^.Row[FieldIndex];
  if FFieldRow <> nil then
    FRowSize:=StrLen(FFieldRow)
  else
    FRowSize:=0;  
end;  

function TDSStream.Seek(Offset: Longint; Origin: Word): Longint;
begin
  Case Origin of
    soFromBeginning : FPosition:=Offset;
    soFromEnd       : FPosition:=FRowSize+Offset;
    soFromCurrent   : FPosition:=FPosition+Offset;
  end;
  Result:=FPosition;
end;

function TDSStream.Write(const Buffer; Count: Longint): Longint; 
var
  NewRow:PChar;
begin
  Result:=Count;
  if Count = 0 then
    Exit;
  //FRowSize is always 0 when FPosition = 0,
  //so there's no need to check FPosition
  NewRow:=StrAlloc(FRowSize+Count+1);
  (NewRow+Count+FRowSize)^:=#0;
  if FRowSize > 0 then
    Move(FFieldRow^,NewRow^,FRowSize);
  Move(Buffer,(NewRow+FRowSize)^,Count);
  FActiveItem^.Row[FFieldIndex]:=NewRow;    
  StrDispose(FFieldRow);
  {$ifdef DEBUG_SQLITEDS}
  WriteLn('##TDSStream.Write##');
  WriteLn('  FPosition(Before): ',FPosition);
  WriteLn('  FRowSize(Before): ',FRowSize);
  WriteLn('  FPosition(After): ',FPosition+Count);
  WriteLn('  FRowSize(After): ',StrLen(NewRow));
  //WriteLn('  Stream Value: ',NewRow);
  {$endif}
  FFieldRow:=NewRow;
  FRowSize:=StrLen(NewRow);
  Inc(FPosition,Count);
end; 
 
function TDSStream.Read(var Buffer; Count: Longint): Longint; 
var
  BytesToMove:Integer;
begin
  if (FRowSize - FPosition) >= Count then
    BytesToMove:=Count
  else
    BytesToMove:=FRowSize - FPosition;   
  Move((FFieldRow+FPosition)^,Buffer,BytesToMove);
  Inc(FPosition,BytesToMove);
  Result:=BytesToMove;  
  {$ifdef DEBUG_SQLITEDS}
  WriteLn('##TDSStream.Read##');
  WriteLn('  Bytes requested: ',Count);
  WriteLn('  Bytes moved: ',BytesToMove);
  WriteLn('  Stream.Size: ',FRowSize);
  //WriteLn('  Stream Value: ',FFieldRow);
  {$endif}
end; 
 
// TCustomSqliteDataset override methods

function TCustomSqliteDataset.AllocRecordBuffer: PChar;
begin
  Result := AllocMem(SizeOf(PPDataRecord));
  PDataRecord(Pointer(Result)^):=FBeginItem;
end;

constructor TCustomSqliteDataset.Create(AOwner: TComponent);
begin
  // setup special items
  New(FBeginItem);
  New(FCacheItem);
  New(FEndItem);
  
  FBeginItem^.Previous:=nil;
  FEndItem^.Next:=nil;
  
  FBeginItem^.BookMarkFlag:=bfBOF;
  FEndItem^.BookMarkFlag:=bfEOF;
  
  FMasterLink:=TMasterDataLink.Create(Self);
  FMasterLink.OnMasterChange:=@MasterChanged;
  FMasterLink.OnMasterDisable:=@MasterDisabled;
  FIndexFieldList:=TList.Create;
  BookmarkSize := SizeOf(Pointer);
  FUpdatedItems:= TFPList.Create;
  FAddedItems:= TFPList.Create;
  FDeletedItems:= TFPList.Create;
  FSqlList:=TStringList.Create;
  inherited Create(AOwner);
end;

function TCustomSqliteDataset.CreateBlobStream(Field: TField; Mode: TBlobStreamMode): TStream;
begin
  if Mode = bmWrite then
  begin
    if not (State in [dsEdit, dsInsert]) then
    begin
      DatabaseErrorFmt(SNotEditing,[Name],Self);
      Exit;
    end;
    StrDispose(FCacheItem^.Row[Field.FieldNo - 1]);
    FCacheItem^.Row[Field.FieldNo - 1]:=nil;
  end;
  Result:= TDSStream.Create(PPDataRecord(ActiveBuffer)^,Field.FieldNo - 1);
end;

procedure TCustomSqliteDataset.DoAfterInsert;
begin
  //an append or an insert in an empty dataset
  if EOF then
    FInsertBookmark:=FEndItem
  else
    FInsertBookmark:=FInternalActiveBuffer;
  inherited DoAfterInsert;
end;

procedure TCustomSqliteDataset.DoBeforeInsert;
begin
  FInternalActiveBuffer:=PPDataRecord(ActiveBuffer)^;
  inherited DoBeforeInsert;
end;

destructor TCustomSqliteDataset.Destroy;
begin
  inherited Destroy;
  if FSqliteHandle <> nil then
    InternalCloseHandle;
  FUpdatedItems.Destroy;
  FAddedItems.Destroy;
  FDeletedItems.Destroy;
  FMasterLink.Destroy;
  FIndexFieldList.Destroy;
  FSqlList.Destroy;
  // dispose special items
  Dispose(FBeginItem);
  Dispose(FCacheItem);
  Dispose(FEndItem);
end;

function TCustomSqliteDataset.BookmarkValid(ABookmark: TBookmark): Boolean;
var
  TempItem: PDataRecord;
begin
  Result:=False;
  TempItem:=FBeginItem^.Next;
  while TempItem <> FEndItem do
  begin
    if TempItem = PPDataRecord(ABookmark)^ then
    begin
      Result:=True;
      Exit;
    end;
    TempItem:=TempItem^.Next;
  end;
end;

function TCustomSqliteDataset.CompareBookmarks(Bookmark1, Bookmark2: TBookmark
  ): Longint;
var
  TempItem: PDataRecord;
begin
  if PPDataRecord(Bookmark1)^ = PPDataRecord(Bookmark2)^  then
  begin
    Result:=0;
    Exit;
  end;
  //assume  Bookmark1 < Bookmark2
  Result:=-1;
  TempItem:=PPDataRecord(Bookmark1)^^.Previous;
  while TempItem <> FBeginItem do
  begin
    if TempItem = PPDataRecord(Bookmark2)^ then
    begin
      //Bookmark1 is greater than Bookmark2
      Result:=1;
      Exit;
    end;
    TempItem:=TempItem^.Previous;
  end;
end;

procedure TCustomSqliteDataset.CopyCacheToItem(AItem: PDataRecord);
var
  i:Integer;
begin
  for i := 0 to FRowCount - 1 do
  begin
    StrDispose(AItem^.Row[i]);
    AItem^.Row[i]:=FCacheItem^.Row[i];
    FCacheItem^.Row[i]:=nil;
  end;
  AItem^.BookmarkFlag:=FCacheItem^.BookmarkFlag;
end;

function TCustomSqliteDataset.GetIndexFields(Value: Integer): TField;
begin
  if (Value < 0) or (Value > FIndexFieldList.Count - 1) then
    DatabaseError('Error acessing IndexFields: Index out of bonds',Self);
  Result:= TField(FIndexFieldList[Value]);
end;

procedure TCustomSqliteDataset.DisposeLinkedList;
var
  TempItem:PDataRecord;
  i:Integer;
begin
  //Todo: insert debug info
  //Todo: see if FDataAllocated is still necessary
  FDataAllocated:=False;
  TempItem:=FBeginItem^.Next;
  while TempItem^.Next <> nil do
  begin
    TempItem:=TempItem^.Next;
    FreeItem(TempItem^.Previous);
  end; 

  //Dispose Deleted Items
  //Directly access list pointer since the index check is already done in the loop
  for i:= 0 to FDeletedItems.Count - 1 do
    FreeItem(PDataRecord(FDeletedItems.List^[i]));

  //Dispose FBeginItem.Row
  FreeMem(FBeginItem^.Row,FRowBufferSize);
    
  //Dispose cache item row
  for i:= 0 to FRowCount - 1 do
    StrDispose(FCacheItem^.Row[i]);
  FreeMem(FCacheItem^.Row,FRowBufferSize);
end;

procedure TCustomSqliteDataset.FreeRecordBuffer(var Buffer: PChar);
begin
  FreeMem(Buffer);
end;

procedure TCustomSqliteDataset.GetBookmarkData(Buffer: PChar; Data: Pointer);
begin
  Pointer(Data^) := PPDataRecord(Buffer)^;
end;

function TCustomSqliteDataset.GetBookmarkFlag(Buffer: PChar): TBookmarkFlag;
begin
  Result := PPDataRecord(Buffer)^^.BookmarkFlag;
end;

function TCustomSqliteDataset.GetFieldData(Field: TField; Buffer: Pointer;
  NativeFormat: Boolean): Boolean;
var
  ValError:Word;
  FieldRow:PChar;
begin
  FieldRow:=PPDataRecord(ActiveBuffer)^^.Row[Field.FieldNo - 1];
  Result := FieldRow <> nil;  
  if Result and (Buffer <> nil) then //supports GetIsNull
  begin
    case Field.Datatype of
    ftString:
      begin
        Move(FieldRow^,PChar(Buffer)^,StrLen(FieldRow)+1);
      end;
    ftInteger,ftAutoInc:
      begin
        Val(StrPas(FieldRow),LongInt(Buffer^),ValError);
        Result:= ValError = 0;  
      end;
    ftBoolean,ftWord:
      begin
        Val(StrPas(FieldRow),Word(Buffer^),ValError);
        Result:= ValError = 0;
      end;    
    ftFloat,ftDateTime,ftTime,ftDate,ftCurrency:
      begin
        Val(StrPas(FieldRow),Double(Buffer^),ValError);
        Result:= ValError = 0; 
      end;
    ftLargeInt:
      begin
        Val(StrPas(FieldRow),Int64(Buffer^),ValError);
        Result:= ValError = 0;
      end;        
    end;
  end;        
end;

function TCustomSqliteDataset.GetFieldData(Field: TField; Buffer: Pointer): Boolean;
begin
  Result:=GetFieldData(Field, Buffer, False);
end;

function TCustomSqliteDataset.GetRecord(Buffer: PChar; GetMode: TGetMode; DoCheck: Boolean): TGetResult;
begin
  Result := grOk;
  case GetMode of
    gmPrior:
      if (FCurrentItem^.Previous = FBeginItem) or (FCurrentItem = FBeginItem) then
      begin
        Result := grBOF;
        FCurrentItem := FBeginItem;
      end
      else
        FCurrentItem:=FCurrentItem^.Previous;
    gmCurrent:
      if (FCurrentItem = FBeginItem) or (FCurrentItem = FEndItem) then
         Result := grError;
    gmNext:
      if (FCurrentItem = FEndItem) or (FCurrentItem^.Next = FEndItem) then
        Result := grEOF
      else
        FCurrentItem:=FCurrentItem^.Next;
  end; //case
  if Result = grOk then
  begin
    PDataRecord(Pointer(Buffer)^):=FCurrentItem;
    FCurrentItem^.BookmarkFlag := bfCurrent;
  end
    else if (Result = grError) and DoCheck then
      DatabaseError('No records found',Self);
end;

function TCustomSqliteDataset.GetRecordCount: Integer;
begin
  Result := FRecordCount;
end;

function TCustomSqliteDataset.GetRecNo: Integer;
var
  TempItem,TempActive:PDataRecord;
begin
  Result:= -1;
  if (FRecordCount = 0) or (State = dsInsert) then
    Exit;  
  TempItem:=FBeginItem;
  TempActive:=PPDataRecord(ActiveBuffer)^;
  if TempActive = FCacheItem then // Record is being edited
  begin
    TempActive:=FInternalActiveBuffer;
  end;
  //RecNo is 1 based
  inc(Result);
  while TempActive <> TempItem do
  begin
    if TempItem^.Next <> nil then
    begin
      inc(Result);
      TempItem:=TempItem^.Next;
    end  
    else
    begin
      Result:=-1;
      DatabaseError('GetRecNo - ActiveItem Not Found',Self);
      break;    
    end;      
  end;  
end;

function TCustomSqliteDataset.GetRecordSize: Word;
begin
  Result := SizeOf(PPDataRecord); //??
end;

procedure TCustomSqliteDataset.InternalAddRecord(Buffer: Pointer; DoAppend: Boolean);
var
  NewItem: PDataRecord;
begin
  {$ifdef DEBUG_SQLITEDS}
  if PPDataRecord(Buffer)^ <> FCacheItem then
    DatabaseError('PPDataRecord(Buffer) <> FCacheItem - Problem',Self);
  {$endif}
  New(NewItem);
  GetMem(NewItem^.Row,FRowBufferSize);
  //necessary to nullify the Row before copy the cache
  FillChar(NewItem^.Row^,FRowBufferSize,#0);
  CopyCacheToItem(NewItem);

  //insert in the linked list
  FInsertBookmark^.Previous^.Next:=NewItem;
  NewItem^.Next:=FInsertBookmark;
  NewItem^.Previous:=FInsertBookmark^.Previous;
  FInsertBookmark^.Previous:=NewItem;
  
  //update the cursor
  FCurrentItem:=NewItem;
  
  Inc(FRecordCount);
  if FAutoIncFieldNo <> - 1 then
    Inc(FNextAutoInc);
  FAddedItems.Add(NewItem);
end;

procedure TCustomSqliteDataset.InternalClose;
begin
  if FSaveOnClose then
    ApplyUpdates;
  //BindFields(False);
  if DefaultFields then
    DestroyFields;
  if FDataAllocated then
    DisposeLinkedList;  
  FAddedItems.Clear;
  FUpdatedItems.Clear;
  FDeletedItems.Clear;
  FRecordCount:=0;
end;

procedure TCustomSqliteDataset.InternalCancel;
var
  i: Integer;
begin
  PPDataRecord(ActiveBuffer)^:=FInternalActiveBuffer;
  //free the cache
  for i:= 0 to FRowCount - 1 do
  begin
    StrDispose(FCacheItem^.Row[i]);
    FCacheItem^.Row[i]:=nil;
  end;
end;

procedure TCustomSqliteDataset.InternalDelete;
var
  TempItem:PDataRecord;
  ValError,TempInteger:Integer;
begin
  if FRecordCount = 0 then
    Exit;
  Dec(FRecordCount);
  TempItem:=PPDataRecord(ActiveBuffer)^;
  TempItem^.Next^.Previous:=TempItem^.Previous;
  TempItem^.Previous^.Next:=TempItem^.Next;
  if FCurrentItem = TempItem then
  begin
    if FCurrentItem^.Previous <> FBeginItem then
      FCurrentItem:= FCurrentItem^.Previous
    else
      FCurrentItem:= FCurrentItem^.Next;  
  end; 
  // Dec FNextAutoInc (only if deleted item is the last record)  
  if FAutoIncFieldNo <> -1 then
  begin
    Val(StrPas(TempItem^.Row[FAutoIncFieldNo]),TempInteger,ValError);
    if (ValError = 0) and (TempInteger = (FNextAutoInc - 1)) then
      Dec(FNextAutoInc);
  end;    
  // Update item lists
  FUpdatedItems.Remove(TempItem);
  if FAddedItems.Remove(TempItem) = -1 then
    FDeletedItems.Add(TempItem)
  else
    FreeItem(TempItem);
end;

procedure TCustomSqliteDataset.InternalEdit;
var
  i: Integer;
begin
  FInternalActiveBuffer:=PPDataRecord(ActiveBuffer)^;
  //copy active item to cache
  for i:= 0 to FRowCount - 1 do
    FCacheItem^.Row[i]:=StrNew(FInternalActiveBuffer^.Row[i]);
  FCacheItem^.BookmarkFlag:=FInternalActiveBuffer^.BookmarkFlag;
  //now active buffer is the cache item
  PPDataRecord(ActiveBuffer)^:=FCacheItem;
end;

procedure TCustomSqliteDataset.InternalFirst;
begin
  FCurrentItem := FBeginItem;
end;

procedure TCustomSqliteDataset.InternalGotoBookmark(ABookmark: Pointer);
begin
  FCurrentItem := PDataRecord(ABookmark^);
end;

procedure TCustomSqliteDataset.InternalInitRecord(Buffer: PChar);
var
  TempStr:String;  
begin
  if FAutoIncFieldNo <> - 1 then
  begin
    Str(FNextAutoInc,TempStr);
    FCacheItem^.Row[FAutoIncFieldNo]:=StrAlloc(Length(TempStr)+1);
    StrPCopy(FCacheItem^.Row[FAutoIncFieldNo],TempStr);
  end;  
  PPDataRecord(Buffer)^:=FCacheItem;    
  FCacheItem^.BookmarkFlag:=bfInserted;
end;

procedure TCustomSqliteDataset.InternalLast;
begin
  FCurrentItem := FEndItem;
end;

procedure TCustomSqliteDataset.InternalOpen;
var
  i:Integer;
begin
  if MasterSource <> nil then
  begin
    //todo: retrieve only necessary fields
    FSql := 'Select * from '+FTableName+';'; // forced to obtain all fields
    FMasterLink.FieldNames:=FMasterLink.FieldNames; //workaround to fill MasterLinks.Fields
    //if FMasterLink.Fields.Count = 0 MasterChanged will not be called anyway so ignore it
  end;

  if FSql = '' then
  begin
    if FTablename = '' then
      DatabaseError('Tablename not set',Self);
    FSql := 'Select * from '+FTableName+';';
  end;

  if FSqliteHandle = nil then
    GetSqliteHandle;
    
  InternalInitFieldDefs;
  //todo: move this to InitFieldDefs
  FSelectSqlStr:='SELECT ';
  for i:= 0 to FieldDefs.Count - 2 do
    FSelectSqlStr:=FSelectSqlStr+FieldDefs[i].Name+',';
  FSelectSqlStr:=FSelectSqlStr+FieldDefs[FieldDefs.Count - 1].Name+
    ' FROM '+FTableName;

  if DefaultFields then 
    CreateFields;
  BindFields(True);

  UpdateIndexFields;
  if FMasterLink.Active then
  begin
    if FIndexFieldList.Count <> FMasterLink.Fields.Count then
      DatabaseError('MasterFields count doesn''t match IndexFields count',Self);
    //Set FSql considering MasterSource active record
    SetDetailFilter;
  end;

  // Get PrimaryKeyNo if available
  if Fields.FindField(FPrimaryKey) <> nil then
    FPrimaryKeyNo:=Fields.FindField(FPrimaryKey).FieldNo - 1  
  else
    FPrimaryKeyNo:=FAutoIncFieldNo; // -1 if there's no AutoIncField 

  BuildLinkedList;               
  FCurrentItem:=FBeginItem;
end;

procedure TCustomSqliteDataset.InternalPost;
begin
  if State <> dsEdit then
    InternalAddRecord(ActiveBuffer,True)
  else
  begin
    CopyCacheToItem(FInternalActiveBuffer);
    PPDataRecord(ActiveBuffer)^:=FInternalActiveBuffer;
    if (FUpdatedItems.IndexOf(FInternalActiveBuffer) = -1) and
      (FAddedItems.IndexOf(FInternalActiveBuffer) = -1) then
      FUpdatedItems.Add(FInternalActiveBuffer);
  end;
end;

procedure TCustomSqliteDataset.InternalSetToRecord(Buffer: PChar);
begin
  FCurrentItem:=PPDataRecord(Buffer)^;
end;

function TCustomSqliteDataset.IsCursorOpen: Boolean;
begin
   Result := FDataAllocated;
end;

function TCustomSqliteDataset.FindRecordItem(StartItem: PDataRecord; const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions; DoResync:Boolean):PDataRecord;
var
  ValueArray: array of string;
  IndexArray: array of integer;
  AFieldList: TList;
  i,AFieldCount:Integer;
  MatchRecord: Boolean;
  AValue:String;
  TempItem:PDataRecord;
begin
  Result:=nil;
  CheckBrowseMode;
  // Currently ignore options
  AFieldList:=TList.Create;
  try
    GetFieldList(AFieldList,KeyFields);
    AFieldCount:=AFieldList.Count;
    if AFieldCount > 1 then
    begin
      if VarIsArray(KeyValues) then
      begin
        if Succ(VarArrayHighBound(KeyValues,1)) <> AFieldCount then
          DatabaseError('Number of fields does not correspond to number of values',Self);
      end
      else
        DatabaseError('Wrong number of values specified: expected an array of variants got a variant',Self);
    end;
    
    //set the array of values and indexes

    SetLength(ValueArray,AFieldCount);
    SetLength(IndexArray,AFieldCount);
    for i:= 0 to AFieldCount - 1 do
      with TField(AFieldList[i]) do
      begin
        //get float types in appropriate format
        if not (DataType in [ftFloat,ftDateTime,ftTime,ftDate]) then
        begin
          if VarIsArray(KeyValues) then
            ValueArray[i]:=keyvalues[i]
          else
            ValueArray[i]:=keyvalues;
        end
        else
        begin
          if VarIsArray(KeyValues) then
            Str(VarToDateTime(keyvalues[i]),AValue)
          else
            Str(VarToDateTime(keyvalues),AValue);
          ValueArray[i]:=Trim(AValue);
        end;
        IndexArray[i]:=FieldNo - 1;
      end;
  finally
    AFieldList.Destroy;
  end;
  {$ifdef DEBUG_SQLITEDS}
  writeln('##TCustomSqliteDataset.FindRecordItem##');
  writeln('  KeyFields: ',keyfields);
  writeln('  KeyValues: ',keyvalues);
  writeln('  AValue: ',AValue);
  {$endif}        
  //Search the list
  TempItem:=StartItem;
  while TempItem <> FEndItem do
  begin
    MatchRecord:=True;
    for i:= 0 to AFieldCount - 1 do
    begin
      //todo: handle null values??
      if (TempItem^.Row[IndexArray[i]] = nil) or
        (StrComp(TempItem^.Row[IndexArray[i]],PChar(ValueArray[i])) <> 0) then
      begin
        MatchRecord:= False;
        Break;//for
      end;
    end;
    if MatchRecord then
    begin
      Result:=TempItem;
      if DoResync then
      begin
        FCurrentItem:=TempItem;
        Resync([]);
      end;
      Break;//while
    end;
    TempItem:=TempItem^.Next;
  end;      
end;

procedure TCustomSqliteDataset.GetSqliteHandle;
begin
  if FFileName = '' then
    DatabaseError ('Filename not set',Self);
  //todo:Handle opening non db files
  FSqliteHandle:=InternalGetHandle;
end;

procedure TCustomSqliteDataset.FreeItem(AItem: PDataRecord);
var
  i: Integer;
begin
  for i:= 0 to FRowCount - 1 do
    StrDispose(AItem^.Row[i]);
  FreeMem(AItem^.Row,FRowBufferSize);
  Dispose(AItem);
end;

function TCustomSqliteDataset.Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions) : Boolean;
begin
  Result:=FindRecordItem(FBeginItem^.Next,KeyFields,KeyValues,Options,True) <> nil;  
end;
  
function TCustomSqliteDataset.LocateNext(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions) : Boolean;
begin
  Result:=FindRecordItem(PPDataRecord(ActiveBuffer)^^.Next,KeyFields,KeyValues,Options,True) <> nil;
end;
  
function TCustomSqliteDataset.Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant;
var
  TempItem:PDataRecord;
begin
  TempItem:=FindRecordItem(FBeginItem^.Next,KeyFields,KeyValues,[],False);
  if TempItem <> nil then
    Result:=TempItem^.Row[FieldByName(ResultFields).FieldNo - 1]
  else
    Result:=False;      
end;  

procedure TCustomSqliteDataset.SetBookmarkData(Buffer: PChar; Data: Pointer);
begin
  //The BookMarkData is the Buffer itself: no need to set nothing;
end;

procedure TCustomSqliteDataset.SetBookmarkFlag(Buffer: PChar; Value: TBookmarkFlag);
begin
  PPDataRecord(Buffer)^^.BookmarkFlag := Value;
end;

procedure TCustomSqliteDataset.SetExpectedAppends(AValue:Integer);
begin
  FAddedItems.Capacity:=AValue;
end;  

procedure TCustomSqliteDataset.SetExpectedUpdates(AValue:Integer);
begin
  FUpdatedItems.Capacity:=AValue;
end;  

procedure TCustomSqliteDataset.SetExpectedDeletes(AValue:Integer);
begin
  FDeletedItems.Capacity:=AValue;
end;  

procedure TCustomSqliteDataset.SetFieldData(Field: TField; Buffer: Pointer;
  NativeFormat: Boolean);
var
  TempStr:String;
begin
  if not (State in [dsEdit, dsInsert]) then
  begin
    DatabaseErrorFmt(SNotEditing,[Name],Self);
    Exit;
  end;
  StrDispose(FCacheItem^.Row[Pred(Field.FieldNo)]);
  if Buffer <> nil then
  begin
    case Field.Datatype of
    ftString:
      begin            
        FCacheItem^.Row[Pred(Field.FieldNo)]:=StrNew(PChar(Buffer));
      end;
    ftInteger:
      begin          
        Str(LongInt(Buffer^),TempStr);  
        FCacheItem^.Row[Pred(Field.FieldNo)]:=StrAlloc(Length(TempStr)+1);
        Move(PChar(TempStr)^,(FCacheItem^.Row[Pred(Field.FieldNo)])^,Length(TempStr)+1);
      end;
    ftBoolean,ftWord:
      begin
        Str(Word(Buffer^),TempStr);  
        FCacheItem^.Row[Pred(Field.FieldNo)]:=StrAlloc(Length(TempStr)+1);
        Move(PChar(TempStr)^,(FCacheItem^.Row[Pred(Field.FieldNo)])^,Length(TempStr)+1);
      end;  
    ftFloat,ftDateTime,ftDate,ftTime,ftCurrency:
      begin
        Str(Double(Buffer^),TempStr);  
        FCacheItem^.Row[Pred(Field.FieldNo)]:=StrAlloc(Length(TempStr));
        //Skips the first space that str returns
        //todo: make a custom Str?
        Move((PChar(TempStr)+1)^,(FCacheItem^.Row[Pred(Field.FieldNo)])^,Length(TempStr));
      end;
    ftLargeInt:
      begin
        Str(Int64(Buffer^),TempStr);  
        FCacheItem^.Row[Pred(Field.FieldNo)]:=StrAlloc(Length(TempStr)+1);
        Move(PChar(TempStr)^,(FCacheItem^.Row[Pred(Field.FieldNo)])^,Length(TempStr)+1);
      end;        
    end;// case
  end//if
  else
    FCacheItem^.Row[Pred(Field.FieldNo)]:=nil;
  if not (State in [dsCalcFields, dsFilter, dsNewValue]) then
    DataEvent(deFieldChange, Ptrint(Field));  
end;

procedure TCustomSqliteDataset.SetFieldData(Field: TField; Buffer: Pointer);
begin
  SetFieldData(Field, Buffer, False);
end;

procedure TCustomSqliteDataset.SetRecNo(Value: Integer);
var
  Counter:Integer;
  TempItem:PDataRecord;
begin
  if (Value > FRecordCount) or (Value <= 0) then
    DatabaseError('Record Number Out Of Range',Self);
  CheckBrowseMode;
  TempItem:=FBeginItem;
  for Counter := 1 to Value do
    TempItem:=TempItem^.Next;
  FCurrentItem:=TempItem;
  Resync([]);
end;

// Specific functions 

procedure TCustomSqliteDataset.SetDetailFilter;
  function FieldToSqlStr(AField:TField):String;
  begin
    if not AField.IsNull then
    begin
      case AField.DataType of
        ftString,ftMemo: Result:='"'+AField.AsString+'"';//todo: handle " caracter properly
        ftDateTime,ftDate,ftTime:Str(AField.AsDateTime,Result);
      else
        Result:=AField.AsString;
      end;//case
    end
    else
      Result:=NullString;
  end;//function

var
  AFilter:String;
  i:Integer;
begin
  if FMasterLink.Dataset.RecordCount = 0 then //Retrieve all data
    FSql:='Select * from '+FTableName
  else
  begin
    AFilter:=' where ';
    for i:= 0 to FMasterLink.Fields.Count - 1 do
    begin
      AFilter:=AFilter + IndexFields[i].FieldName +' = '+ FieldToSqlStr(TField(FMasterLink.Fields[i]));
      if i <> FMasterLink.Fields.Count - 1 then
        AFilter:= AFilter + ' and ';
    end;
    FSql:='Select * from '+FTableName+AFilter;
  end;
end;

procedure TCustomSqliteDataset.MasterChanged(Sender: TObject);
begin
  SetDetailFilter;
  {$ifdef DEBUG_SQLITEDS}
  writeln('##TCustomSqliteDataset.MasterChanged##');
  writeln('  SQL used to filter detail dataset:');
  writeln('  ',FSql);
  {$endif}
  RefetchData;
end;

procedure TCustomSqliteDataset.MasterDisabled(Sender: TObject);
begin
  FSql:='Select * from '+FTableName+';'; 
  RefetchData;
end;

procedure TCustomSqliteDataset.SetMasterFields(Value: String);
begin
  FMasterLink.FieldNames:=Value;
  if Active and FMasterLink.Active then
  begin
    UpdateIndexFields;
    if (FIndexFieldList.Count <> FMasterLink.Fields.Count) then
      DatabaseError('MasterFields count doesn''t match IndexFields count',Self);
  end;
end;

function TCustomSqliteDataset.GetMasterFields: String;
begin
  Result:=FMasterLink.FieldNames;
end;

procedure TCustomSqliteDataset.UpdateIndexFields;
begin
  FIndexFieldList.Clear;
  if FIndexFieldNames <> '' then
  begin
    try
      GetFieldList(FIndexFieldList, FIndexFieldNames);
    except
      FIndexFieldList.Clear;
      raise;
    end;
  end;
end;

function TCustomSqliteDataset.GetMasterSource: TDataSource;
begin
  Result := FMasterLink.DataSource;
end;

procedure TCustomSqliteDataset.SetFileName(Value: String);
begin
  if Value <> FFileName then
  begin
    if Active then
      DatabaseError('It''s not allowed to change Filename in an open dataset',Self);
    if FSqliteHandle <> nil then
      InternalCloseHandle;
    FFileName:=Value;
  end;
end;

procedure TCustomSqliteDataset.SetMasterSource(Value: TDataSource);
begin
  FMasterLink.DataSource := Value;
end;

procedure TCustomSqliteDataset.ExecSQL(const ASql:String);
begin
  if FSqliteHandle = nil then
    GetSqliteHandle;
  ExecuteDirect(ASql);
end;

procedure TCustomSqliteDataset.ExecSQLList;
begin
  if FSqliteHandle = nil then
    GetSqliteHandle;
  SqliteExec(PChar(FSqlList.Text),nil,nil);
end;

procedure TCustomSqliteDataset.ExecSQL;
begin
  ExecSQL(FSql);
end;

function TCustomSqliteDataset.ApplyUpdates:Boolean;
var
  iFields,iItems,StatementsCounter:Integer;
  SqlTemp,WhereKeyNameEqual,ASqlLine,TemplateStr:String;
  TempItem: PDataRecord;
begin
  CheckBrowseMode;
  if not UpdatesPending then
  begin
    Result:=True;
    Exit;
  end;
  Result:=False;
  if FPrimaryKeyNo <> -1 then
  begin
    StatementsCounter:=0;
    WhereKeyNameEqual:=' WHERE '+Fields[FPrimaryKeyNo].FieldName+' = ';
    {$ifdef DEBUG_SQLITEDS}
    WriteLn('##TCustomSqliteDataset.ApplyUpdates##');
    if FPrimaryKeyNo = FAutoIncFieldNo then
      WriteLn('  Using an AutoInc field as primary key');
    WriteLn('  PrimaryKey: ',WhereKeyNameEqual);
    WriteLn('  PrimaryKeyNo: ',FPrimaryKeyNo);
    {$endif}
    SqlTemp:='BEGIN;';
    // Delete Records
    if FDeletedItems.Count > 0 then
      TemplateStr:='DELETE FROM '+FTableName+WhereKeyNameEqual;
    for iItems:= 0 to FDeletedItems.Count - 1 do
    begin
      TempItem:=PDataRecord(FDeletedItems.List^[iItems]);
      SqlTemp:=SqlTemp+(TemplateStr+
        StrPas(TempItem^.Row[FPrimaryKeyNo])+';');
      FreeItem(TempItem);
      inc(StatementsCounter);
      //ApplyUpdates each 400 statements
      if StatementsCounter = 400 then
      begin
        SqlTemp:=SqlTemp+'COMMIT;';
        FReturnCode:=SqliteExec(PChar(SqlTemp),nil,nil);
        StatementsCounter:=0;
        SqlTemp:='BEGIN;';
      end;    
    end;
    // Update changed records
    if FUpdatedItems.Count > 0 then
      TemplateStr:='UPDATE '+FTableName+' SET ';
    for iItems:= 0 to FUpdatedItems.Count - 1 do
    begin
      ASqlLine:=TemplateStr;
      for iFields:= 0 to Fields.Count - 2 do
      begin
        ASqlLine:=ASqlLine + (Fields[iFields].FieldName +' = '+
          FGetSqlStr[iFields](PDataRecord(FUpdatedItems[iItems])^.Row[iFields])+',');
      end;
      iFields:=Fields.Count - 1;
      ASqlLine:=ASqlLine + (Fields[iFields].FieldName +' = '+
        FGetSqlStr[iFields](PDataRecord(FUpdatedItems[iItems])^.Row[iFields])+
        WhereKeyNameEqual+StrPas(PDataRecord(FUpdatedItems[iItems])^.Row[FPrimaryKeyNo])+';');
      SqlTemp:=SqlTemp + ASqlLine;
      inc(StatementsCounter);
      //ApplyUpdates each 400 statements
      if StatementsCounter = 400 then
      begin
        SqlTemp:=SqlTemp+'COMMIT;';
        FReturnCode:=SqliteExec(PChar(SqlTemp),nil,nil);
        StatementsCounter:=0;
        SqlTemp:='BEGIN;';
      end;  
    end;
    // Add new records
    // Build TemplateStr
    if FAddedItems.Count > 0 then
    begin
      TemplateStr:='INSERT INTO '+FTableName+ ' (';
      for iFields:= 0 to Fields.Count - 2 do
        TemplateStr:=TemplateStr + Fields[iFields].FieldName+',';
      TemplateStr:= TemplateStr+Fields[Fields.Count - 1].FieldName+') VALUES (';
    end;  
    for iItems:= 0 to FAddedItems.Count - 1 do
    begin
      ASqlLine:=TemplateStr;
      for iFields:= 0 to Fields.Count - 2 do
      begin
        ASqlLine:=ASqlLine + (FGetSqlStr[iFields](PDataRecord(FAddedItems[iItems])^.Row[iFields])+',');
      end;
      //todo: see if i can assume iFields = Fields.Count-2 safely
      iFields:=Fields.Count - 1;
      ASqlLine:=ASqlLine + (FGetSqlStr[iFields](PDataRecord(FAddedItems[iItems])^.Row[iFields])+');');
      SqlTemp:=SqlTemp + ASqlLine;    
      inc(StatementsCounter);
      //ApplyUpdates each 400 statements
      if StatementsCounter = 400 then
      begin
        SqlTemp:=SqlTemp+'COMMIT;';
        FReturnCode:=SqliteExec(PChar(SqlTemp),nil,nil);
        StatementsCounter:=0;
        SqlTemp:='BEGIN;';
      end;  
    end;  
    SqlTemp:=SqlTemp+'COMMIT;';
    {$ifdef DEBUG_SQLITEDS}
    writeln('  SQL: ',SqlTemp);
    {$endif}  
   FAddedItems.Clear;
   FUpdatedItems.Clear;
   FDeletedItems.Clear;   
   FReturnCode:=SqliteExec(PChar(SqlTemp),nil,nil);
   Result:= FReturnCode = SQLITE_OK;
  end;  
  {$ifdef DEBUG_SQLITEDS}
    writeln('  Result: ',Result);
  {$endif}   
end;    

function TCustomSqliteDataset.CreateTable: Boolean;
begin
  Result:=CreateTable(FTableName);
end;

function TCustomSqliteDataset.CreateTable(const ATableName: String): Boolean;
var
  SqlTemp:String;
  i:Integer;
begin
  {$ifdef DEBUG_SQLITEDS}
  writeln('##TCustomSqliteDataset.CreateTable##');
  if ATableName = '' then
    WriteLn('  TableName Not Set');
  if FieldDefs.Count = 0 then
    WriteLn('  FieldDefs Not Initialized');
  {$endif}
  if (ATableName <> '') and (FieldDefs.Count > 0) then
  begin
    SqlTemp:='CREATE TABLE '+ATableName+' (';
    for i := 0 to FieldDefs.Count-1 do
    begin
      //todo: add index to autoinc field
      SqlTemp:=SqlTemp + FieldDefs[i].Name;
      case FieldDefs[i].DataType of
        ftInteger:
          SqlTemp:=SqlTemp + ' INTEGER';
        ftString:
          SqlTemp:=SqlTemp + ' VARCHAR';
        ftBoolean:
          SqlTemp:=SqlTemp + ' BOOL_INT';
        ftFloat:
          SqlTemp:=SqlTemp + ' FLOAT';
        ftWord:
          SqlTemp:=SqlTemp + ' WORD';
        ftDateTime:
          SqlTemp:=SqlTemp + ' DATETIME';
        ftDate:
          SqlTemp:=SqlTemp + ' DATE';
        ftTime:
          SqlTemp:=SqlTemp + ' TIME';
        ftLargeInt:
          SqlTemp:=SqlTemp + ' LARGEINT';
        ftCurrency:
          SqlTemp:=SqlTemp + ' CURRENCY';
        ftAutoInc:
          SqlTemp:=SqlTemp + ' AUTOINC_INT';
        ftMemo:
          SqlTemp:=SqlTemp + ' TEXT';
      else
        DatabaseError('Field type "'+FieldTypeNames[FieldDefs[i].DataType]+'" not supported',Self);
      end;
      if UpperCase(FieldDefs[i].Name) = UpperCase(FPrimaryKey) then
        SqlTemp:=SqlTemp + ' PRIMARY KEY';
      if i <> FieldDefs.Count - 1 then
        SqlTemp:=SqlTemp+ ' , ';
    end;
    SqlTemp:=SqlTemp+');';
    {$ifdef DEBUG_SQLITEDS}
    writeln('  SQL: ',SqlTemp);
    {$endif}
    ExecSQL(SqlTemp);
    Result:= FReturnCode = SQLITE_OK;
  end
  else
    Result:=False;
end;

procedure TCustomSqliteDataset.ExecCallback(const ASql: String; UserData: Pointer = nil);
var
  CallbackInfo: TCallbackInfo;
begin
  if not Assigned(FOnCallback) then
    DatabaseError('OnCallback property not set',Self);
  if FSqliteHandle = nil then
    GetSqliteHandle;
  CallbackInfo.Data:=UserData;
  CallbackInfo.Proc:=FOnCallback;
  SqliteExec(PChar(ASql),@CallbackDispatcher,@CallbackInfo);
end;


procedure TCustomSqliteDataset.QueryUpdates(RecordStates: TRecordStateSet; Callback: TQueryUpdatesCallback;
  UserData: Pointer = nil);
var
  i: Integer;
  TempItem: PDataRecord;
begin
  if not Assigned(Callback) then
    DatabaseError('Callback parameter not set',Self);
  CheckBrowseMode;
  if rsDeleted in RecordStates then
    with FDeletedItems do
    for i:= 0 to Count - 1 do
      Callback(UserData,PDataRecord(Items[i])^.Row,nil,rsDeleted);
  if rsUpdated in RecordStates then
    with FUpdatedItems do
    for i:= 0 to Count - 1 do
    begin
      TempItem:=PDataRecord(Items[i]);
      Callback(UserData,TempItem^.Row,TBookmark(@TempItem),rsUpdated);
    end;
  if rsAdded in RecordStates then
    with FAddedItems do
    for i:= 0 to Count - 1 do
    begin
      TempItem:=PDataRecord(Items[i]);
      Callback(UserData,TempItem^.Row,TBookmark(@TempItem),rsAdded);
    end;
end;


procedure TCustomSqliteDataset.RefetchData;
var
  i:Integer;
begin
  //Close
  if FSaveOnRefetch then
    ApplyUpdates;
  if FDataAllocated then
    DisposeLinkedList;  
  FAddedItems.Clear;
  FUpdatedItems.Clear;
  FDeletedItems.Clear;
  //Reopen
  BuildLinkedList;               
  FCurrentItem:=FBeginItem;
  for i := 0 to BufferCount - 1 do
    PPDataRecord(Buffers[i])^:=FBeginItem;
  Resync([]);
  DoAfterScroll;
end;

function TCustomSqliteDataset.TableExists: Boolean;
begin
  Result:=TableExists(FTableName);
end;

function TCustomSqliteDataset.TableExists(const ATableName: String): Boolean;
begin
  Result:=False;
  if not (ATableName = '') and FileExists(FFileName) then
  begin
    ExecSql('SELECT name FROM SQLITE_MASTER WHERE type = ''table'' AND name LIKE '''+ ATableName+ ''';');
    Result:=FReturnCode = SQLITE_ROW;
  end;
end;

function TCustomSqliteDataset.UpdatesPending: Boolean;
begin
  Result:= (FUpdatedItems.Count > 0) or
    (FAddedItems.Count > 0) or (FDeletedItems.Count > 0);
end;

function TCustomSqliteDataset.QuickQuery(const ASql:String):String;
begin
  Result:=QuickQuery(ASql,nil,False); 
end;

function TCustomSqliteDataset.QuickQuery(const ASql:String;const AStrList: TStrings):String;
begin
  Result:=QuickQuery(ASql,AStrList,False)
end;  


{$ifdef DEBUGACTIVEBUFFER}
procedure TCustomSqliteDataset.SetCurrentItem(Value:PDataRecord);
var
 ANo:Integer;

  function GetItemPos:Integer;
  var
    TempItem:PDataRecord;
  begin
    Result:= -1;
    TempItem:=FBeginItem;
    if Value = FCacheItem then
       Result:=-2
    else
    while Value <> TempItem do
    begin
     if TempItem^.Next <> nil then
     begin
       inc(Result);
       TempItem:=TempItem^.Next;
     end
     else
     begin
      Result:=-1;
      break;
     end;
    end;
  end;

begin
  if Value = FBeginItem then
  begin
    writeln('FCurrentItem set to FBeginItem: ',IntToHex(Integer(Value),0));
    FFCurrentItem:=Value;
  end
  else
    if Value = FEndItem then
    begin
      writeln('FCurrentItem set to FEndItem: ',IntToHex(Integer(Value),0));
      FFCurrentItem:=Value;
    end
    else
      if Value = FCacheItem then
      begin
        writeln('FCurrentItem set to FCacheItem: ',IntToHex(Integer(Value),0));
        FFCurrentItem:=Value;
      end
      else
      begin
        writeln('FCurrentItem set from ',IntToHex(Integer(FFCurrentItem),0),' to ',IntToHex(Integer(Value),0));
        Ano:=GetItemPos;
        writeln('Item position is ',ANo);
        FFCurrentItem:=Value;
      end;
end;
{$endif}

end.