{ Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

unit DISQLite3_World_Common;

{$I DICompilers.inc}
{$I DISQLite3.inc}

interface

uses
  {$IFDEF HAS_UNITSCOPE}
  System.Classes, Vcl.Forms, Vcl.Controls, Vcl.ComCtrls, Vcl.StdCtrls, Vcl.Mask,
  Vcl.ExtCtrls, Vcl.Grids, Data.DB, Vcl.DBCtrls, Vcl.DBGrids,
  {$ELSE HAS_UNITSCOPE}
  Classes, Forms, Controls, ComCtrls, StdCtrls, Mask, ExtCtrls, Grids,
  DB, DBCtrls, DBGrids,
  {$ENDIF HAS_UNITSCOPE}
  DISQLite3Api, DISQLite3Database, DISQLite3DataSet;

type
  TfrmWorld_Common = class(TForm)
    StatusBar: TStatusBar;
    DISQLite3Database: TDISQLite3Database;
    pnlMain: TPanel;
    imgFlag: TDBImage;
    DataSource: TDataSource;
    DBNavigator: TDBNavigator;
    DISQLite3UniDirQuery: TDISQLite3UniDirQuery;
    lblCountry: TLabel;
    lblArea: TLabel;
    lblPopulation: TLabel;
    lblBackground: TLabel;
    edtName: TDBEdit;
    edtArea: TDBEdit;
    edtPopulation: TDBEdit;
    edtBackground: TDBMemo;
    lblBirthRate: TLabel;
    edtBirthRate: TDBEdit;
    edtLifeExpectancy: TDBEdit;
    lblLifeExpectancy: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure DISqlite3UniDirQueryInitFieldDef(
      const AColumn: TDISQLite3Column; const AFieldDef: TFieldDef);
  end;

var
  frmWorld_Common: TfrmWorld_Common;

const
  SQL_CREATE_COUNTRIES =
    'CREATE TABLE Countries (' + #13#10 +
    '"ID" INTEGER PRIMARY KEY,' + #13#10 +
    '"Name" TEXT UNIQUE,' + #13#10 +
    '"Background" TEXT,' + #13#10 +
    '"Area" INTEGER,' + #13#10 +
    '"Population" INTEGER,' + #13#10 +
    '"Birth Rate" FLOAT,' + #13#10 +
    '"Life Expectancy" FLOAT,' + #13#10 +
    '"Flag" BLOB' + #13#10 +
    ')';

  APP_TITLE = 'DISQLite3' + {$IFDEF DISQLite3_Personal} ' Personal' + {$ENDIF} ': World Demo';

implementation

uses
  {$IFDEF HAS_UNITSCOPE}System.SysUtils{$ELSE }SysUtils{$ENDIF};

{$R *.dfm}

const
  DATABASE_NAME = '..\World.db3';

procedure TfrmWorld_Common.FormCreate(Sender: TObject);
begin
  Caption := APP_TITLE;
  DISQLite3Database.DatabaseName := DATABASE_NAME;
  try
    DISQLite3Database.Open;
  except
    on e: ESQLite3 do
      if e.ErrorCode = SQLITE_CANTOPEN then
        begin
          DISQLite3Database.CreateDatabase;
          DISQLite3Database.Execute16(SQL_CREATE_COUNTRIES);
        end
      else
        raise
    else
      raise;
  end;
end;

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

procedure TfrmWorld_Common.DISqlite3UniDirQueryInitFieldDef(
  const AColumn: TDISQLite3Column;
  const AFieldDef: TFieldDef);
begin
  { This event is called once for each newly created FieldDef. Use it to adjust
    the FieldDefs of TDISQLite3UniDirDataSet during runtime.

    AColumn provides information about the original DISQLite3 column. AFieldDef
    is a pre-initialized instance of TFieldDef representing this column. }

  if AColumn.ColumnName = 'Background' then
    begin
      { Since DISQLite3 and its TDataSet wrapper can store text fields of
        unlimited length, it does not not require memo fields. Other TDataSet
        descendants (like TClientDataSet), however, can store long text in memo
        fields only. The next line maps 'BackGround' to a memo field so that
        TClientDataSet stores the entire text and does not truncate. }
      AFieldDef.DataType :=
        {$IFDEF COMPILER_10_UP}
      ftWideMemo
        {$ELSE}
      ftMemo // No TWideMemo fields in Delphi 7 an earlier.
      {$ENDIF};
    end
  else
    case AFieldDef.DataType of

      ftLargeInt:
        if AColumn.ColumnIsKey then
          begin
            { !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
              There are known bugs in TClientDataSet/TDataBaseProvider with
              ftLargeInt index fields. They may cause obscure data loss which
              results in wrong values being passed to updates and inserts, which
              are then not not carried out properly.

              I do not know why this happens, but postings in the Borland/CodeGear
              news-groups hold midaslib or midas.dll responsible. Since these are
              closed source, we can do nothing about this bug except to work
              around it.

              To avoid trouble, I generally recommend to downsize ftLargeInt to
              ftInteger for index fields.

              If you prefer to or must use ftLargeInt indexes, you can test if you
              are affected by this problem if you remove the next line of code.
              Then insert and update data. The bug will cause them to fail and
              TDISQLite3UniDirQuery will raise an exception. If your changes are
              stored properly, you should be OK.

              Non-index fields pass tests and seem not to be affected.
              !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!}
            AFieldDef.DataType := ftInteger;
          end;

      ftString, ftWideString:
        begin
          { TDISQLite3UniDirQuery has variable length text fields (for both
            ftString and ftWideString), but they are fixed length for most other
            TDataSets, including TClientDataSet. The default TStringField.Size
            is 20 only, which causes longer strings to be cut off. The next line
            of code therefore increases the size to 64, which is large enough to
            store all strings used in this application completely. }
          AFieldDef.Size := 64;
        end;
    end;
end;

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

initialization
  { Initialize the DISQLite3 library prior to using any other DISQLite3
    functionality. See also sqlite3_shutdown() below.}
  sqlite3_initialize;

finalization
  { Deallocate any resources that were allocated by
    sqlite3_initialize() above. }
  sqlite3_shutdown;

end.

