unit PSQLFieldsTest;
{$I PSQLDAC.inc}
{

  Delphi DUnit Test Case
  ----------------------
  This unit contains a skeleton test case class generated by the Test Case Wizard.
  Modify the generated code to correctly setup and call the methods from the unit 
  being tested.

}

interface

uses
  PSQLFields, PSQLGeomTypes, PSQLAccess, PSQLDbTables, PSQLTypes, Db,
  Classes, SysUtils, Data.FmtBcd, System.Math,
  {$IFNDEF DUNITX}
  TestFramework, TestExtensions, TestHelper
  {$ELSE}
  DUnitX.TestFramework, TestXHelper
  {$ENDIF};

type

  {$IFDEF DUNITX}[TestFixture]{$ENDIF}
  TestTPSQLGuidField = class({$IFNDEF DUNITX}TTestCase{$ELSE}TTestXCase{$ENDIF})
  public
    {$IFNDEF DUNITX}
    procedure TearDown; override;
    {$ELSE}
    [TearDown]
    procedure TearDown;
    {$ENDIF}
    procedure TestGUIDField;
    procedure TestGUIDInsert;
    procedure TestGUIDUpdate;
    procedure TestGUIDDelete;
  published
    procedure TestSelectUUID;
    procedure TesTGUIDField_ASCII;
    procedure TestGUIDInsert_ASCII;
    procedure TestGUIDUpdate_ASCII;
    procedure TestGUIDDelete_ASCII;
    procedure TestGUIDField_UTF8;
    procedure TestGUIDInsert_UTF8;
    procedure TestGUIDUpdate_UTF8;
    procedure TestGUIDDelete_UTF8;
    {$IFDEF DUNITX}
    [SetupFixture]
    procedure SetupFixture;
    [TearDownFixture]
    procedure TearDownFixture;
    {$ENDIF}
  end;

  // Test methods for class TPSQLPointField
  {$IFDEF DUNITX}[TestFixture]{$ENDIF}
  TestGeometricFields = class({$IFNDEF DUNITX}TTestCase{$ELSE}TTestXCase{$ENDIF})
  public
    {$IFNDEF DUNITX}
    procedure TearDown; override;
    {$ELSE}
    [TearDown]
    procedure TearDown;
    {$ENDIF}
  published
    procedure TestSelectGeoms;
    procedure TestInsertGeoms;
    procedure TestUpdateGeoms;
    {$IFDEF DUNITX}
    [SetupFixture]
    procedure SetupFixture;
    [TearDownFixture]
    procedure TearDownFixture;
    {$ENDIF}
  end;

  // Test methods for class TPSQLRangeField
  {$IFDEF DUNITX}[TestFixture]{$ENDIF}
  TestTPSQLRangeField = class({$IFNDEF DUNITX}TTestCase{$ELSE}TTestXCase{$ENDIF})
  public
    {$IFNDEF DUNITX}
    procedure TearDown; override;
    {$ELSE}
    [TearDown]
    procedure TearDown;
    {$ENDIF}
  published
    procedure TestSelectEmptyRange;
    procedure TestSelectOpenRange;
    procedure TestSelectClosedRange;
    procedure TestSelectUpperInfinityRange;
    procedure TestSelectLowerInfinityRange;
    procedure TestSelectRange;
    procedure TestInsertRange;
    procedure TestUpdateRange;
    {$IFDEF DUNITX}
    [SetupFixture]
    procedure SetupFixture;
    [TearDownFixture]
    procedure TearDownFixture;
    {$ENDIF}
  end;

  {$IFDEF DUNITX}[TestFixture]{$ENDIF}
  TestNativeNumericField = class({$IFNDEF DUNITX}TTestCase{$ELSE}TTestXCase{$ENDIF})
  public
    {$IFNDEF DUNITX}
    procedure TearDown; override;
    {$ELSE}
    [TearDown]
    procedure TearDown;
    {$ENDIF}
  published
    procedure TestNumericTypeMapping;
    procedure TestNumericSelectInt;
    procedure TestNumericSelectFrac;
    procedure TestNumericPrecScale;
    {$IFDEF DUNITX}
    [SetupFixture]
    procedure SetupFixture;
    [TearDownFixture]
    procedure TearDownFixture;
    {$ENDIF}
  end;

implementation

{$IFDEF DELPHI_5}
function CoCreateGuid(out guid: TGUID): HResult; stdcall; external 'ole32.dll' name 'CoCreateGuid';

function CreateGUID(out Guid: TGUID): HResult;
begin
  Result := CoCreateGuid(Guid);
end;
{$ENDIF}

{$IFDEF DUNITX}
procedure TestTPSQLGuidField.SetupFixture;
begin
end;

procedure TestTPSQLGuidField.TearDownFixture;
begin
end;
{$ENDIF}

procedure TestTPSQLGuidField.TearDown;
begin
 TestDBSetup.Query.Close;
 TestDBSetup.Query.SQL.Clear;
end;

procedure TestTPSQLGuidField.TestSelectUUID;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT ''35c6c84e-4157-466c-0091-31a4714aca34''::uuid';
  TestDBSetup.Query.Open;
  Check(TestDBSetup.Query.Active, 'Cannot select UUID value');
end;

procedure TestTPSQLGuidField.TestGUIDDelete;
begin
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM uuid_test_case_table';
  TestDBSetup.Query.Open;
  TestDBSetup.Query.Delete;
  Check(TestDBSetup.Query.RowsAffected = 1, 'Cannot delete UUID ' + TestDBSetup.Query.Fields[0].ClassName);
end;

procedure TestTPSQLGuidField.TestGUIDDelete_ASCII;
begin
  TestDBSetup.Database.CharSet := 'SQL_ASCII';
  try
    TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDDelete;
    TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDDelete;
  finally
    TestDBSetup.Database.CharSet := 'UNICODE';
  end;
end;

procedure TestTPSQLGuidField.TestGUIDDelete_UTF8;
begin
  TestDBSetup.Database.CharSet := 'UNICODE';
  TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDDelete;
  TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDDelete;
end;

procedure TestTPSQLGuidField.TestGUIDField;
var
  G1, G2: TGUID;
begin
  G1 := StringToGuid('{35c6c84e-4157-466c-0091-31a4714aca34}');
  TestDBSetup.Query.SQL.Text := 'SELECT ''35c6c84e-4157-466c-0091-31a4714aca34''::uuid';
  TestDBSetup.Query.Open;
  Check(TestDBSetup.Query.Active, 'Cannot select UUID value');
  Check(TestDBSetup.Query.Fields[0].AsString = UpperCase('{35c6c84e-4157-466c-0091-31a4714aca34}'), 'UUID value is corrupted in SQL_ASCII charset using TGUIDField');
  if not (dsoUseGUIDField in TestDBSetup.Query.Options) then
   G2 := TGUIDField(TestDBSetup.Query.Fields[0]).AsGuid
  else
   G2 := TPSQLGUIDField(TestDBSetup.Query.Fields[0]).AsGuid;
  Check(IsEqualGUID(G1, G2), 'GUID comparison failed: ' + TestDBSetup.Query.Fields[0].ClassName);
end;

procedure TestTPSQLGuidField.TestGUIDInsert;
var
  G: TGUID;
begin
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM uuid_test_case_table';
  TestDBSetup.Query.Open;
  TestDBSetup.Query.Insert;
  Check(CreateGUID(G) = 0, 'GUID generation failed');
  PSQLAccess.LogDebugMessage('GUID generated value:', G.ToString);
  if TestDBSetup.Query.Fields[0] is TGUIDField then
    if not (dsoUseGUIDField in TestDBSetup.Query.Options) then
      TGUIDField(TestDBSetup.Query.Fields[0]).AsGuid := G
  else
   (TestDBSetup.Query.Fields[0] as TPSQLGUIDField).AsGuid := G;
  TestDBSetup.Query.Post;
  Check(TestDBSetup.Query.RowsAffected = 1, 'Cannot insert UUID: ' + TestDBSetup.Query.Fields[0].ClassName);
end;

procedure TestTPSQLGuidField.TestGUIDInsert_ASCII;
begin
  TestDBSetup.Database.CharSet := 'SQL_ASCII';
  try
    TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDInsert;
    TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDInsert;
  finally
    TestDBSetup.Database.CharSet := 'UNICODE';
  end;
end;

procedure TestTPSQLGuidField.TestGUIDInsert_UTF8;
begin
  TestDBSetup.Database.CharSet := 'UNICODE';
  TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDInsert;
  TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDInsert;
end;

procedure TestTPSQLGuidField.TestGUIDUpdate;
var G: TGUID;
begin
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM uuid_test_case_table';
  TestDBSetup.Query.Open;
  TestDBSetup.Query.Edit;
  CreateGUID(G);
  if not (dsoUseGUIDField in TestDBSetup.Query.Options) then
    TGUIDField(TestDBSetup.Query.Fields[0]).AsGuid := G
  else
    TPSQLGUIDField(TestDBSetup.Query.Fields[0]).AsGuid := G;
  TestDBSetup.Query.Post;
  Check(TestDBSetup.Query.RowsAffected = 1, 'Cannot update UUID ' + TestDBSetup.Query.Fields[0].ClassName);
end;

procedure TestTPSQLGuidField.TestGUIDUpdate_ASCII;
begin
  TestDBSetup.Database.CharSet := 'SQL_ASCII';
  try
    TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDUpdate;
    TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDUpdate;
  finally
    TestDBSetup.Database.CharSet := 'UNICODE';
  end;
end;

procedure TestTPSQLGuidField.TestGUIDUpdate_UTF8;
begin
  TestDBSetup.Database.CharSet := 'UNICODE';
  TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDUpdate;
  TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDUpdate;
end;

procedure TestTPSQLGuidField.TestGUIDField_ASCII;
begin
  TestDBSetup.Database.CharSet := 'SQL_ASCII';
  try
    TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDField;
    TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDField;
  finally
    TestDBSetup.Database.CharSet := 'UNICODE';
  end;
end;

procedure TestTPSQLGuidField.TestGUIDField_UTF8;
begin
  TestDBSetup.Database.CharSet := 'UNICODE';
  TestDBSetup.Query.Options := TestDBSetup.Query.Options + [dsoUseGUIDField];
  TestGUIDField;
  TestDBSetup.Query.Options := TestDBSetup.Query.Options - [dsoUseGUIDField];
  TestGUIDField;
end;

{ TestTPSQLPointField }

{$IFDEF DUNITX}
procedure TestGeometricFields.SetupFixture;
begin
end;
procedure TestGeometricFields.TearDownFixture;
begin
end;
{$ENDIF}

procedure TestGeometricFields.TearDown;
begin
  inherited;
  TestDBSetup.Query.Close;
  TestDBSetup.Query.SQL.Clear;
end;

procedure TestGeometricFields.TestInsertGeoms;
const
  P: TPSQLPoint = (X: 2.5; Y: 3.5);
  C: TPSQLCircle = (R: 1.34; X: 2.5; Y: 3.5);
  B: TPSQLBox = (Right: 2.12; Top: 7.89; Left: -0.14; Bottom: 0.1);
  L: TPSQLLSeg = (X1: 1.2; Y1: 0.4; X2: -5.5; Y2: -0.2);
begin
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM geometry_test_case_table';
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.Open;
  TestDBSetup.Query.Insert;
  TestDBSetup.Query.FieldByName('id').AsInteger := 1;
  (TestDBSetup.Query.FieldByName('p') as TPSQLPointField).Value := P;
  (TestDBSetup.Query.FieldByName('c') as TPSQLCircleField).Value := C;
  (TestDBSetup.Query.FieldByName('b') as TPSQLBoxField).Value := B;
  (TestDBSetup.Query.FieldByName('l') as TPSQLLSegField).Value := L;
  TestDBSetup.Query.Post;
  Check(TPSQLPointField(TestDBSetup.Query.FieldByName('p')).Value = P, 'Wrong value for "point" field after insert');
  Check(TPSQLCircleField(TestDBSetup.Query.FieldByName('c')).Value = C, 'Wrong value for "circle" field after insert');
  Check(TPSQLBoxField(TestDBSetup.Query.FieldByName('b')).Value = B, 'Wrong value for "box" field after insert');
  Check(TPSQLLSegField(TestDBSetup.Query.FieldByName('l')).Value = L, 'Wrong value for "lseg" field after insert');
end;

procedure TestGeometricFields.TestSelectGeoms;
const
  P: TPSQLPoint = (X: 2.5; Y: 3.5);
  C: TPSQLCircle = (R: 1.34; X: 2.5; Y: 3.5);
  B: TPSQLBox = (Right: 2.12; Top: 7.89; Left: -0.14; Bottom: 0.1);
  L: TPSQLLSeg = (X1: 1.2; Y1: 0.4; X2: -5.5; Y2: -0.2);
begin
  TestDBSetup.Query.SQL.Text := 'SELECT ''( 2.5 , 3.5 )''::point, '+
                     ' ''<( 2.5 , 3.5 ) , 1.34>''::circle, '+
                     ' ''(2.12, 7.89) , (-0.14, 0.1)''::box, '+
                     ' ''[(1.2,0.4),(-5.5,-0.2)]''::lseg ';
  TestDBSetup.Query.Open;
  Check(TPSQLPointField(TestDBSetup.Query.Fields[0]).Value = P, 'Wrong value for "point" field after SELECT');
  Check(TPSQLCircleField(TestDBSetup.Query.Fields[1]).Value = C, 'Wrong value for "circle" field after SELECT');
  Check(TPSQLBoxField(TestDBSetup.Query.Fields[2]).Value = B, 'Wrong value for "box" field after SELECT');
  Check((TestDBSetup.Query.Fields[3] as TPSQLLSegField).Value = L, 'Wrong value for "lseg" field after SELECT');
end;

procedure TestGeometricFields.TestUpdateGeoms;
const
  P: TPSQLPoint = (X: pi; Y: 2.818281828);
  C: TPSQLCircle = (R: 1.34; X: pi; Y: 2.818281828);
  B: TPSQLBox = (Right: 3.12; Top: 9.89; Left: -1.14; Bottom: -10.1);
  L: TPSQLLSeg = (X1: 8.2; Y1: 1.4; X2: -255.5; Y2: -13845.14212);
begin
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM geometry_test_case_table';
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.Open;
  if TestDBSetup.Query.RecordCount = 0 then TestInsertGeoms;
  TestDBSetup.Query.Edit;
  (TestDBSetup.Query.FieldByName('p') as TPSQLPointField).Value := P;
  (TestDBSetup.Query.FieldByName('c') as TPSQLCircleField).Value := C;
  (TestDBSetup.Query.FieldByName('b') as TPSQLBoxField).Value := B;
  (TestDBSetup.Query.FieldByName('l') as TPSQLLSegField).Value := L;
  TestDBSetup.Query.Post;
  Check(TPSQLPointField(TestDBSetup.Query.FieldByName('p')).Value = P, 'Wrong value for "point" field after update');
  Check(TPSQLCircleField(TestDBSetup.Query.FieldByName('c')).Value = C, 'Wrong value for "circle" field after update');
  Check(TPSQLBoxField(TestDBSetup.Query.FieldByName('b')).Value = B, 'Wrong value for "box" field after update');
  Check(TPSQLLSegField(TestDBSetup.Query.FieldByName('l')).Value = L, 'Wrong value for "lseg" field after update');
end;

{ TestTPSQLRangeField }

{$IFDEF DUNITX}
procedure TestTPSQLRangeField.SetupFixture;
begin
end;

procedure TestTPSQLRangeField.TearDownFixture;
begin
end;
{$ENDIF}

procedure TestTPSQLRangeField.TearDown;
begin
  inherited;
  TestDBSetup.Query.Close;
  TestDBSetup.Query.SQL.Clear;
end;

procedure TestTPSQLRangeField.TestInsertRange;
var
  R, RF, RD, RTS: TPSQLRange;
begin
  R.Create('[4,6)', FIELD_TYPE_INT4RANGE);
  RF.Create('[3.1,4.6]', FIELD_TYPE_NUMRANGE);
  RD.Create('[2010-01-01,2010-01-11)', FIELD_TYPE_DATERANGE);
  RTS.Create('["2010-01-01 14:45:00","2014-11-20 00:00:00")', FIELD_TYPE_TSRANGE);
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM range_test_case_table';
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.Open;
  TestDBSetup.Query.Insert;
  TestDBSetup.Query.FieldByName('id').AsInteger := 1;
  (TestDBSetup.Query.FieldByName('intr') as TPSQLRangeField).Value := R;
  (TestDBSetup.Query.FieldByName('numr') as TPSQLRangeField).Value := RF;
  (TestDBSetup.Query.FieldByName('dater') as TPSQLRangeField).Value := RD;
  (TestDBSetup.Query.FieldByName('tsr') as TPSQLRangeField).Value := RTS;
  (TestDBSetup.Query.FieldByName('tstzr') as TPSQLRangeField).Value := RTS;
  TestDBSetup.Query.Post;
  Check((TestDBSetup.Query.FieldByName('intr') as TPSQLRangeField).Value = R, 'Wrong value for "intrange" field after insert');
  Check((TestDBSetup.Query.FieldByName('numr') as TPSQLRangeField).Value = RF, 'Wrong value for "numrange" field after insert');
  Check((TestDBSetup.Query.FieldByName('dater') as TPSQLRangeField).Value = RD, 'Wrong value for "daterange" field after insert');
  Check((TestDBSetup.Query.FieldByName('tsr') as TPSQLRangeField).Value = RTS, 'Wrong value for "timestamprange" field after insert');
  Check((TestDBSetup.Query.FieldByName('tstzr') as TPSQLRangeField).Value = RTS, 'Wrong value for "timestamptzrange" field after insert');
end;

procedure TestTPSQLRangeField.TestSelectClosedRange;
var
  i: Integer;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT ''[3.3, 4.45]''::numrange, '+
                     ' ''[2010-01-01 14:45, 2010-01-01 15:45]''::tsrange,' +
                     ' ''[2010-01-01 14:45 UTC, 2010-01-01 15:45 PST]''::tstzrange';

  //expected output
  //"[3.3,4.45]"; - numrange
  //"["2010-01-01 14:45:00","2010-01-01 15:45:00"]"; - tsrange
  //"["2010-01-01 16:45:00+02","2010-01-02 01:45:00+02"]"; - tstzrange

  TestDBSetup.Query.Open;
  for i := 0 to TestDBSetup.Query.FieldCount - 1 do
   with (TestDBSetup.Query.Fields[i] as TPSQLRangeField).Value do
    Check((UpperBound.State = rbsInclusive) and
          (LowerBound.State = rbsInclusive), 'Range must be closed');
end;

procedure TestTPSQLRangeField.TestSelectEmptyRange;
var
  i: Integer;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT ''empty''::numrange, '+
                     ' ''empty''::int4range,' +
                     ' ''empty''::int8range,' +
                     ' ''empty''::tsrange,' +
                     ' ''empty''::daterange,' +
                     ' ''empty''::tstzrange';
  TestDBSetup.Query.Open;
  for i := 0 to TestDBSetup.Query.FieldCount - 1 do
    Check((TestDBSetup.Query.Fields[i] as TPSQLRangeField).IsEmpty, 'Range field must be empty');
end;

procedure TestTPSQLRangeField.TestSelectLowerInfinityRange;
var
  i: Integer;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT numrange(NULL, 3.3),  '+
                     'tsrange(NULL, ''2010-01-01 14:45''), '+
                     'tstzrange(NULL, ''2010-01-01 14:45 UTC''), '+
                     'int4range(NULL, 1), int8range(NULL, 22), '+
                     'daterange(NULL, ''2010-01-01'')';
  //expected output
  //(,3.3); (,"2010-01-01 14:45:00"); (,"2010-01-01 16:45:00+02"); (,1); (,22); (,2010-01-01)
  TestDBSetup.Query.Open;
  for i := 0 to TestDBSetup.Query.FieldCount - 1 do
   with (TestDBSetup.Query.Fields[i] as TPSQLRangeField).Value do
    Check((LowerBound.State = rbsInfinite), 'Range must gave infinite lower range');
end;

procedure TestTPSQLRangeField.TestSelectOpenRange;
var
  i: Integer;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT ''(3,4)''::numrange, '+
                     ' ''(2010-01-01 14:45, 2010-01-01 15:45)''::tsrange,' +
                     ' ''(2010-01-01 14:45 UTC, 2010-01-01 15:45 PST)''::tstzrange';
  TestDBSetup.Query.Open;
  for i := 0 to TestDBSetup.Query.FieldCount - 1 do
   with (TestDBSetup.Query.Fields[i] as TPSQLRangeField).Value do
    Check((UpperBound.State = rbsExclusive) and
          (LowerBound.State = rbsExclusive), 'Range must be open');
end;

procedure TestTPSQLRangeField.TestSelectRange;
var R: TPSQLRange;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT numrange(3.1, 5.2, ''()''), '+
                     ' int4range(1, 3, ''[)''), ' +
                     ' int4range(1, 1, ''()'') ';
  TestDBSetup.Query.Open;
  Check(TestDBSetup.Query.Active, 'Cannot select "point" value');
  R := (TestDBSetup.Query.Fields[0] as TPSQLRangeField).Value;
  Check(not R.Empty, 'Range is empty');
  Check(R.LowerBound.State = rbsExclusive, 'numrange lower bound must be exclusive');
  Check(R.UpperBound.State = rbsExclusive, 'numrange lower bound must be exclusive');
  Check(SameValue(R.LowerBound.AsFloat, 3.1), 'Wrong numrange lower bound value');
  Check(SameValue(R.UpperBound.AsFloat, 5.2), 'Wrong numrange upper bound value');

  R := TPSQLRangeField(TestDBSetup.Query.Fields[1]).Value;
  Check(not R.Empty, 'Range is empty');
  Check(R.LowerBound.State = rbsInclusive, 'Range lower bound must be inclusive');
  Check(R.UpperBound.State = rbsExclusive, 'Range lower bound must be exclusive');
  Check(R.LowerBound.AsInteger = 1, 'Wrong lower bound value');
  Check(R.UpperBound.AsInteger = 3, 'Wrong upper bound value');
end;

procedure TestTPSQLRangeField.TestSelectUpperInfinityRange;
var
  i: Integer;
begin
  TestDBSetup.Query.SQL.Text := 'SELECT numrange(3.3, NULL), '+
                     'tsrange(''2010-01-01 14:45'', NULL), '+
                     'tstzrange(''2010-01-01 14:45 UTC'', NULL), '+
                     'int4range(1, NULL), int8range(22, NULL), '+
                     'daterange(''2010-01-01'', NULL)';
  //expected output
  //[3.3,); ["2010-01-01 14:45:00",); ["2010-01-01 16:45:00+02",); [1,); [22,); [2010-01-01,)
  TestDBSetup.Query.Open;
  for i := 0 to TestDBSetup.Query.FieldCount - 1 do
   with (TestDBSetup.Query.Fields[i] as TPSQLRangeField).Value do
    Check((UpperBound.State = rbsInfinite), 'Range must gave infinite upper range');
end;

procedure TestTPSQLRangeField.TestUpdateRange;
var
  R, RF, RD, RTS: TPSQLRange;
begin
  R.Create('[7,8)', FIELD_TYPE_INT4RANGE);
  RF.Create('[8.12,124.46]', FIELD_TYPE_NUMRANGE);
  RD.Create('[2012-01-01,2013-01-11)', FIELD_TYPE_DATERANGE);
  RTS.Create('["2011-01-01 14:45:00","2012-11-20 00:00:00")', FIELD_TYPE_TSRANGE);
  TestDBSetup.Query.SQL.Text := 'SELECT * FROM range_test_case_table';
  TestDBSetup.Query.RequestLive := True;
  TestDBSetup.Query.Open;
  if TestDBSetup.Query.RecordCount = 0 then TestInsertRange;
  TestDBSetup.Query.Edit;
  (TestDBSetup.Query.FieldByName('intr') as TPSQLRangeField).Value := R;
  (TestDBSetup.Query.FieldByName('numr') as TPSQLRangeField).Value := RF;
  (TestDBSetup.Query.FieldByName('dater') as TPSQLRangeField).Value := RD;
  (TestDBSetup.Query.FieldByName('tsr') as TPSQLRangeField).Value := RTS;
  (TestDBSetup.Query.FieldByName('tstzr') as TPSQLRangeField).Value := RTS;
  TestDBSetup.Query.Post;
  Check((TestDBSetup.Query.FieldByName('intr') as TPSQLRangeField).Value = R, 'Wrong value for "intrange" field after update');
  Check((TestDBSetup.Query.FieldByName('numr') as TPSQLRangeField).Value = RF, 'Wrong value for "numrange" field after update');
  Check((TestDBSetup.Query.FieldByName('dater') as TPSQLRangeField).Value = RD, 'Wrong value for "daterange" field after update');
  Check((TestDBSetup.Query.FieldByName('tsr') as TPSQLRangeField).Value = RTS, 'Wrong value for "timestamprange" field after update');
  Check((TestDBSetup.Query.FieldByName('tstzr') as TPSQLRangeField).Value = RTS, 'Wrong value for "timestamptzrange" field after update');
end;

{ TestNativeNumericField }
{$IFDEF DUNITX}
procedure TestNativeNumericField.SetupFixture;
begin
end;

procedure TestNativeNumericField.TearDownFixture;
begin
end;
{$ENDIF}

procedure TestNativeNumericField.TearDown;
begin
  inherited;
  //
end;

procedure TestNativeNumericField.TestNumericPrecScale;
var F: TFMTBCDField;
begin
  TestDBSetup.Query.ParamCheck := False;
  TestDBSetup.Query.SQL.Text := 'SELECT 1 :: numeric(8, 3), ' +
                                '2 :: numeric(32, 3), ' +
                                '3 :: numeric(10), ' +
                                '4 :: numeric ';
  TestDBSetup.Query.Open;
  with TestDBSetup.Query do
  begin
    F := Fields[0] as TFMTBCDField;
    Check(F.Precision = 8, 'Incorrect precision for NUMERIC(8, 3)');
    Check(F.Size = 3, 'Incorrect scale for NUMERIC(8, 3)');
    F := Fields[1] as TFMTBCDField;
    Check(F.Precision = 32, 'Incorrect precision for NUMERIC(32, 3)');
    Check(F.Size = 3, 'Incorrect scale for NUMERIC(32, 3)');
    F := Fields[2] as TFMTBCDField;
    Check(F.Precision = 10, 'Incorrect precision for NUMERIC(10)');
    Check(F.Size = 0, 'Incorrect scale for NUMERIC(10)');
    F := Fields[3] as TFMTBCDField;
    Check(F.Precision = NUMERIC_PREC, 'Incorrect precision for NUMERIC');
    Check(F.Size = NUMERIC_SCALE, 'Incorrect scale for NUMERIC');
  end;
end;

procedure TestNativeNumericField.TestNumericSelectFrac;
var _Num: Tbcd;
begin
  _Num := StrToBcd('98765432100123456789.98765432100123456789', PSQL_FS);
  TestDBSetup.Query.ParamCheck := False;
  TestDBSetup.Query.SQL.Text := 'SELECT 98765432100123456789.98765432100123456789 :: numeric';
  TestDBSetup.Query.Open;
  Check(_Num = TestDBSetup.Query.Fields[0].AsBCD, 'Incorrect value for NUMERIC');
end;

procedure TestNativeNumericField.TestNumericSelectInt;
const _Num = '98765432100123456789';
var S: string;
begin
  TestDBSetup.Query.ParamCheck := False;
  TestDBSetup.Query.SQL.Text := 'SELECT 98765432100123456789 :: numeric(20, 0)';
  TestDBSetup.Query.Open;
  S := TestDBSetup.Query.Fields[0].AsString;
  Check(_Num = S, 'Incorrect value for NUMERIC');
//  CheckEqualsString(_Num, S, 'Incorrect value for NUMERIC');
end;

procedure TestNativeNumericField.TestNumericTypeMapping;
begin
  TestDBSetup.Query.ParamCheck := False;
  TestDBSetup.Query.SQL.Text := 'SELECT 12.13 :: numeric';
  TestDBSetup.Query.Open;
  {$IFNDEF DUNITX}
  CheckIs(TestDBSetup.Query.Fields[0], TFMTBCDField, 'Incorrect NUMERIC mapping');
  {$ELSE}
  Assert.InheritsFrom(TestDBSetup.Query.Fields[0].ClassType, TFMTBCDField, 'Incorrect NUMERIC mapping');
  {$ENDIF}
end;

initialization

{$IFDEF DUNITX}
  TDUnitX.RegisterTestFixture(TestTPSQLGuidField);
  TDUnitX.RegisterTestFixture(TestGeometricFields);
  TDUnitX.RegisterTestFixture(TestTPSQLRangeField);
  TDUnitX.RegisterTestFixture(TestNativeNumericField);
{$ENDIF}

end.

