freepascal.compiler/packages/fcl-db/tests/testbasics.pas

unit TestBasics;

{$IFDEF FPC}
  {$mode objfpc}{$H+}
{$ENDIF}

interface

uses
  fpcunit, testutils, testregistry, testdecorator,
  Classes, SysUtils, db;

type

  { TTestBasics }

  TTestBasics = class(TTestCase)
  private
    function CreateDatasetWith3Fields: TDataset;
  protected
  published
    procedure TestParseSQL;
    procedure TestInitFielddefsFromFields;
    procedure TestDoubleFieldDef;
    procedure TestFieldDefWithoutDS;
    procedure TestGetParamList;
    procedure TestGetFieldList;
    procedure TestExtractFieldName; //move record then copy. Is copy identical? Has record position changed?
    procedure TestCheckFieldNames;
    procedure TestFindField;
  end;

implementation

uses toolsunit;

Type HackedDataset = class(TDataset);

{ TTestBasics }

function TTestBasics.CreateDatasetWith3Fields: TDataset;
var
  F: TField;
begin
  Result := TDataSet.Create(nil);
  F := TIntegerField.Create(Result);
  F.FieldName := 'Field1';
  F.DataSet := Result;

  F := TIntegerField.Create(Result);
  F.FieldName := 'Field2';
  F.DataSet := Result;

  F := TIntegerField.Create(Result);
  F.FieldName := 'Field3';
  F.DataSet := Result;
end;

procedure TTestBasics.TestParseSQL;
var Params  : TParams;
    ReplStr : string;
    pb      : TParamBinding;
    i       : integer;
    SQLStr  : string;
begin
  Params := TParams.Create;

  AssertEquals(     'select * from table where id = $1',
    params.ParseSQL('select * from table where id = :id',true,True,True,psPostgreSQL));

  AssertEquals(     'select * from table where id = $1',
    params.ParseSQL('select * from table where id = :id',false,True,True,psPostgreSQL));

  AssertEquals(     'update test set 1=$1 2=$2 3=$3 4=$4 5=$5 6=$6 7=$7 8=$8 9=$9 where (id = $2)',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 where (id = :2)',true,True,True,psPostgreSQL));

  AssertEquals(     'update test set 1=$1 2=$2 3=$3 4=$4 5=$5 6=$6 7=$7 8=$8 9=$9 where (id = $3) and (test=''$test'')',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 where (id = :par3) and (test=''$test'')',true,true,true,psPostgreSQL));

  AssertEquals(     'update test set 1=$1 2=$2 3=$3 4=$4 5=$5 6=$6 7=$7 8=$8 9=$9 10=$10 11=$11 12=$5 where (id = $3) and (test=''$test'')',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 10=:par10 11=:11 12=:par5 where (id = :par3) and (test=''$test'')',true,true,true,psPostgreSQL));

  AssertEquals(     'select * from table where id = $1',
    params.ParseSQL('select * from table where id = :id',true,true,false,psSimulated,pb,ReplStr));
  AssertEquals('$',ReplStr);

  AssertEquals(     'update test set 1=$1 2=$2 3=$3 4=$4 5=$5 6=$6 7=$7 8=$8 9=$9 where (id = $2)',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 where (id = :2)',true,true,false,psSimulated,pb,ReplStr));
  AssertEquals('$',ReplStr);

  AssertEquals(     'update test set 1=$$1 2=$$2 3=$$3 4=$$4 5=$$5 6=$$6 7=$$7 8=$$8 9=$$9 where (id = $$3) and (test=''$test'')',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 where (id = :par3) and (test=''$test'')',true,true,false,psSimulated,pb,ReplStr));
  AssertEquals('$$',ReplStr);

  AssertEquals(     'update test set 1=$$1 2=$$2 3=$$3 4=$$4 5=$$5 6=$$6 7=$$7 8=$$8 9=$$9 10=$$10 11=$$11 12=$$5 where (id = $$3) and (test=''$test'')',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 10=:par10 11=:11 12=:par5 where (id = :par3) and (test=''$test'')',true,True,True,psSimulated));
  AssertEquals('$$',ReplStr);

  AssertEquals(     'update test set 1=$$$1 2=$$$2 3=$$$3 4=$$$4 5=$$$5 6=$$$6 7=$$$7 8=$$$8 9=$$$9 10=$$$10 11=$$$11 12=$$$5 where (id$$ = $$$3) and (test$=''$test'')',
    params.ParseSQL('update test set 1=:1 2=:2 3=:par3 4=:par4 5=:par5 6=:par6 7=:par7 8=:par8 9=:par9 10=:par10 11=:11 12=:par5 where (id$$ = :par3) and (test$=''$test'')',true,true,False,psSimulated,pb,ReplStr));
  AssertEquals('$$$',ReplStr);

  AssertEquals(     'select * from table where id = ?',
    params.ParseSQL('select * from table where id = :id',true,true,true,psInterbase));

// Test bug 10345
  AssertEquals(     'select email from table where upper(email) like ''%''||?||''%''',
    params.ParseSQL('select email from table where upper(email) like ''%''||:email||''%''',true,true,true,psInterbase));

// Test escape-sequences:
  AssertEquals(     'select * from table where ''id '''' = :id''',
    params.ParseSQL('select * from table where ''id '''' = :id''',true,False,True,psPostgreSQL));

  AssertEquals(     'select * from table where "id "" = :id"',
    params.ParseSQL('select * from table where "id "" = :id"',true,False,True,psPostgreSQL));

  AssertEquals(     'select * from table where "id \" = :id"',
    params.ParseSQL('select * from table where "id \" = :id"',true,True,False,psPostgreSQL));

  AssertEquals(     'select * from table where "id \" = $1',
    params.ParseSQL('select * from table where "id \" = :id',true,False,False,psPostgreSQL));

  AssertEquals(     'select * from table where "id  = :id\',
    params.ParseSQL('select * from table where "id  = :id\',true,True,True,psInterbase));

// Test strange-field names
  AssertEquals(     'select * from table where "field-name" = ?',
    params.ParseSQL('select * from table where "field-name" = :"field-name"',true,True,True,psInterbase));
  AssertEquals('field-name',Params.Items[0].Name);

  AssertEquals(     'select * from table where "field-name" = ?',
    params.ParseSQL('select * from table where "field-name" = :"field-name',true,True,True,psInterbase));

// Test more than 99 params - bug 19645
  SQLStr := 'update test set';
  for i := 1 to 101 do
    SQLStr := format('%s field%d=:par%d', [SQLStr,i,i]);
  AssertEquals( StringReplace(SQLStr, ':par', '$', [rfReplaceAll]),
    Params.ParseSQL(SQLStr, True, True, True, psPostgreSQL) );

// Test comments:
  // Simple comment
  AssertEquals(     'select * from table where id= --comment :c'#10'$1-$2 or id= --:c'#13'-$3',
    Params.ParseSQL('select * from table where id= --comment :c'#10':a-:b or id= --:c'#13'-:d', True, True, True, psPostgreSQL));
  // Bracketed comment
  AssertEquals(     'select * from table where id=/*comment :c*/$1-$2',
    Params.ParseSQL('select * from table where id=/*comment :c*/:a-:b', True, True, True, psPostgreSQL));

  Params.Free;
end;

procedure TTestBasics.TestInitFielddefsFromFields;

var ds       : TDataset;
    F1,F2,F3 : Tfield;
    
  Procedure CompareFieldAndFieldDef(Fld: TField; FldDef : TFieldDef);
  
  begin
    AssertEquals(Fld.FieldName,FldDef.Name);
    AssertEquals(Fld.Size,FldDef.Size);
    AssertEquals(Fld.Required,FldDef.Required);
    AssertTrue(Fld.DataType=FldDef.DataType);
  end;
    
begin
  ds := TDataset.Create(nil);
  try

  F1:=TStringField.Create(ds);
  F1.Size := 10;
  F1.Name := 'StringFld';
  F1.FieldName := 'FStringFld';
  F1.Required := false;
  F1.Dataset:=ds;

  F2:=TIntegerField.Create(ds);
  F2.Name := 'IntegerFld';
  F2.FieldName := 'FIntegerFld';
  F2.Required := True;
  F2.Dataset:=ds;

  F3:=TBCDField.Create(ds);
  F3.Name := 'BCDFld';
  F3.FieldName := 'FBCDFld';
  F3.Required := false;
  F3.Dataset:=ds;
  (f3 as TBCDField).Precision := 2;
  
  HackedDataset(ds).InitFieldDefsFromfields;
  
  AssertEquals(3,ds.FieldDefs.Count);
  
  CompareFieldAndFieldDef(F1,ds.FieldDefs[0]);
  CompareFieldAndFieldDef(F2,ds.FieldDefs[1]);
  CompareFieldAndFieldDef(F3,ds.FieldDefs[2]);
  finally
    ds.Free;
  end;

end;

procedure TTestBasics.TestDoubleFieldDef;
var ds : TDataset;
    PassException : boolean;
begin
  // If a second field with the same name is added to a TFieldDefs, an exception
  // should occur
  ds := TDataset.create(nil);
  try
    ds.FieldDefs.Add('Field1',ftInteger);
    PassException:=False;
    try
      ds.FieldDefs.Add('Field1',ftString,10,false)
    except
      on E: EDatabaseError do PassException := True;
    end;
    AssertTrue(PassException);
  finally
    ds.Free;
  end;
end;

procedure TTestBasics.TestFieldDefWithoutDS;
var FieldDefs : TFieldDefs;
begin
  FieldDefs := TFieldDefs.Create(nil);
  try
    FieldDefs.Add('test',ftString);
  finally
    FieldDefs.Free;
  end;
end;

procedure TTestBasics.TestGetFieldList;
var
  ds: TDataSet;
  List: TList;
  ExceptionRaised: Boolean;
begin
  ds := CreateDatasetWith3Fields;
  try
    List := TList.Create;
    try
      //should not
      List.Clear;
      ds.GetFieldList(List, '');
      AssertEquals(0, List.Count);

      List.Clear;
      ExceptionRaised := False;
      try
        ds.GetFieldList(List, ' ');
      except
        on E: EDatabaseError do ExceptionRaised := True;
      end;
      AssertTrue(ExceptionRaised);

      List.Clear;
      ds.GetFieldList(List, 'Field1');
      AssertEquals(1, List.Count);

      List.Clear;
      ds.GetFieldList(List, ' Field1 ');
      AssertEquals(1, List.Count);

      List.Clear;
      ds.GetFieldList(List, 'Field1;Field2');
      AssertEquals(2, List.Count);

      List.Clear;
      ds.GetFieldList(List, 'Field1;Field2;');
      AssertEquals(2, List.Count);

      List.Clear;
      ds.GetFieldList(List, 'Field1;Field2;Field3');
      AssertEquals(3, List.Count);
    finally
      List.Destroy;
    end;
  finally
    ds.Destroy;
  end;
end;

procedure TTestBasics.TestGetParamList;
var
  Params: TParams;
  P: TParam;
  List: TList;
  ExceptionRaised: Boolean;
begin
  Params := TParams.Create(nil);
  try
    P := TParam.Create(Params, ptInput);
    P.Name := 'Param1';

    P := TParam.Create(Params, ptInput);
    P.Name := 'Param2';

    P := TParam.Create(Params, ptInput);
    P.Name := 'Param3';

    List := TList.Create;
    try
      List.Clear;
      Params.GetParamList(List, '');
      AssertEquals(0, List.Count);

      List.Clear;
      ExceptionRaised := False;
      try
        Params.GetParamList(List, ' ');
      except
        on E: EDatabaseError do ExceptionRaised := True;
      end;
      AssertTrue(ExceptionRaised);

      List.Clear;
      Params.GetParamList(List, 'Param1');
      AssertEquals(1, List.Count);

      List.Clear;
      Params.GetParamList(List, ' Param1 ');
      AssertEquals(1, List.Count);

      List.Clear;
      Params.GetParamList(List, 'Param1;');
      AssertEquals(1, List.Count);

      List.Clear;
      Params.GetParamList(List, 'Param1;Param2');
      AssertEquals(2, List.Count);

      List.Clear;
      Params.GetParamList(List, 'Param1;Param2;Param3');
      AssertEquals(3, List.Count);
    finally
      List.Destroy;
    end;
  finally
    Params.Destroy;
  end;
end;


procedure TTestBasics.TestExtractFieldName;
var
  i: Integer;
  Fields: String;
  FieldName: String;
begin
  Fields := '';
  i := 1;
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(1, i);
  AssertEquals('', FieldName);

  Fields := 'test';
  i := 1;
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(5, i);
  AssertEquals('test', FieldName);

  Fields := 'test;';
  i := 1;
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(6, i);
  AssertEquals('test', FieldName);

  Fields := ' test ';
  i := 1;
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(7, i);
  AssertEquals('test', FieldName);

  Fields := 'test;xxx';
  i := 1;
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(6, i);
  AssertEquals('test', FieldName);
  FieldName := ExtractFieldName(Fields, i);
  AssertEquals(9, i);
  AssertEquals('xxx', FieldName);
end;

procedure TTestBasics.TestCheckFieldNames;
var
  ds: TDataSet;
  ExceptionRaised: Boolean;
begin
  ds := CreateDatasetWith3Fields;
  try
    ExceptionRaised := False;
    try
      ds.Fields.CheckFieldNames('');
    except
      ExceptionRaised := True;
    end;
    AssertFalse(ExceptionRaised);

    ExceptionRaised := False;
    try
      ds.Fields.CheckFieldNames('Field1;Field2');
    except
      ExceptionRaised := True;
    end;
    AssertFalse(ExceptionRaised);

    ExceptionRaised := False;
    try
      ds.Fields.CheckFieldNames('Field1;NonExistentField');
    except
      ExceptionRaised := True;
    end;
    AssertTrue(ExceptionRaised);
  finally
    ds.Destroy;
  end;
end;

procedure TTestBasics.TestFindField;
var
  ds: TDataSet;
  F: TField;
begin
  ds := CreateDatasetWith3Fields;
  try
    F := ds.FindField('');
    AssertTrue(F = nil);

    F := ds.FindField('field3');
    AssertTrue(F <> nil);

    F := ds.FindField('NonExistentField');
    AssertTrue(F = nil);
  finally
    ds.Destroy;
  end;
end;

initialization
  RegisterTest(TTestBasics);
end.