unit PSQLDatabaseTest;
{$I PSQLDAC.inc}
{$IFDEF DUNITX}
  {$M+}
{$ENDIF}
{

  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 PSQLAccess, PSQLDbTables, PSQLTypes, SysUtils, Classes,
  {$IFNDEF DUNITX}
  TestFramework, {$IFNDEF DELPHI_5}Variants,{$ENDIF} TestExtensions, TestHelper
  {$ELSE}
  DUnitX.TestFramework, TestXHelper
  {$ENDIF};

type

  {$IFDEF DUNITX}[TestFixture]{$ENDIF}
  TestTPSQLDatabase = class({$IFNDEF DUNITX}TTestCase{$ELSE}TTestXCase{$ENDIF})
  published
    procedure HookUp;
    procedure TestPlainConnInfoConnect;
    procedure TestExecute;
    procedure TestGetBackendPID;
    procedure TestSelectString;
    procedure TestSelectString1;
    procedure TestSelectStringDef;
    procedure TestSelectStringDef1;
    procedure TestSelectStrings;
    procedure TestSelectStrings1;
    procedure TestCommit;
    procedure TestGetCharsets;
    procedure TestGetDatabases;
    procedure TestGetSchemaNames;
    procedure TestGetStoredProcNames;
    procedure TestGetTableNames;
    procedure TestGetTablespaces;
    procedure TestGetUserNames;
    procedure TestReset;
    procedure TestRollback;
    procedure TestPing;
    procedure TestPingEx;
    procedure TestIsThreadSafe;
    procedure TestSSLConnect;
    procedure TestLibraryVersion;
  end;

implementation

procedure TestTPSQLDatabase.HookUp;
begin
  Check(True);
end;

procedure TestTPSQLDatabase.TestExecute;
var
  ReturnValue: Integer;
  SQL: string;
begin
  SQL := 'SELECT version()';
  ReturnValue := TestDBSetup.Database.Execute(SQL);
  Check(ReturnValue = 1);
end;

procedure TestTPSQLDatabase.TestGetBackendPID;
var
  ReturnValue: Cardinal;
begin
  ReturnValue := TestDBSetup.Database.GetBackendPID;
  Check(ReturnValue > InvalidOID);
end;

procedure TestTPSQLDatabase.TestSelectString;
var
  ReturnValue: string;
  aFieldName: string;
  IsOk: Boolean;
  aSQL: string;
begin
  aSQL := 'SELECT 12345 as column1';
  aFieldName := 'column1';
  ReturnValue := TestDBSetup.Database.SelectString(aSQL, IsOk, aFieldName);
  Check(IsOk);
  Check('12345' = ReturnValue);
end;

procedure TestTPSQLDatabase.TestSelectString1;
var
  ReturnValue: string;
  aFieldNumber: Integer;
  IsOk: Boolean;
  aSQL: string;
begin
  aSQL := 'SELECT 12345 as column1';
  aFieldNumber := 0;
  ReturnValue := TestDBSetup.Database.SelectString(aSQL, IsOk, aFieldNumber);
  Check(IsOk);
  Check('12345' = ReturnValue);
end;

procedure TestTPSQLDatabase.TestSelectStringDef;
var
  ReturnValue: string;
  aFieldName: string;
  aDefaultValue: string;
  aSQL: string;
begin
  aSQL := 'SELECT 12345 as column1';
  aFieldName := 'column1';
  ReturnValue := TestDBSetup.Database.SelectStringDef(aSQL, aDefaultValue, aFieldName);
  Check('12345' = ReturnValue);
  aSQL := 'SELECT 12345 as column1';
  aFieldName := 'WRONG_COL_NAME';
  aDefaultValue := 'MyDefaultValue';
  ReturnValue := TestDBSetup.Database.SelectStringDef(aSQL, aDefaultValue, aFieldName);
  Check(aDefaultValue = ReturnValue);
end;

procedure TestTPSQLDatabase.TestSelectStringDef1;
var
  ReturnValue: string;
  aFieldNumber: Integer;
  aDefaultValue: string;
  aSQL: string;
begin
  aSQL := 'SELECT 12345 as column1';
  aFieldNumber := 0;
  ReturnValue := TestDBSetup.Database.SelectStringDef(aSQL, aDefaultValue, aFieldNumber);
  Check('12345' = ReturnValue);
  aSQL := 'SELECT 12345 as column1';
  aFieldNumber := -1234214;
  aDefaultValue := 'MyDefaultValue';
  ReturnValue := TestDBSetup.Database.SelectStringDef(aSQL, aDefaultValue, aFieldNumber);
  Check(aDefaultValue = ReturnValue);
end;

procedure TestTPSQLDatabase.TestSelectStrings;
var
  aFieldName: string;
  aList: TStrings;
  aSQL: string;
begin
  aList := TStringList.Create;
  try
    aSQL := 'SELECT 1, g.s FROM generate_series(1,10) as g(s)';
    aFieldName := 's';
    TestDBSetup.Database.SelectStrings(aSQL, aList, aFieldName);
    Check(aList.Count = 10, 'SelectStrings by FieldName failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestSelectStrings1;
var
  aFieldNumber: Integer;
  aList: TStrings;
  aSQL: string;
begin
  aList := TStringList.Create;
  try
    aSQL := 'SELECT 1, g.s FROM generate_series(1,10) as g(s)';
    aFieldNumber := 1;
    TestDBSetup.Database.SelectStrings(aSQL, aList, aFieldNumber);
    Check(aList.Count = 10, 'SelectStrings by FieldNumber failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestSSLConnect;
var
  sslDB: TPSQLDatabase;
begin
  sslDB := TPSQLDatabase.Create(nil);
  try
    sslDB.Assign(TestDBSetup.Database);
    sslDB.Close;
    sslDB.SSLMode := sslVerifyCA;
    sslDB.SSLCert := 'TestData\postgresql.crt';
    sslDB.SSLKey := 'TestData\postgresql.key';
    sslDB.SSLRootCert := 'TestData\root.crt';
    sslDB.Open;
    Check(sslDB.Connected, 'SSL Connection failed');
  finally
    {$IFNDEF NEXTGEN}
    FreeAndNil(sslDB);
    {$ELSE}
    sslDB.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestCommit;
begin
  TestDBSetup.Database.StartTransaction;
  Check(TestDBSetup.Database.TransactionStatus in [trstINTRANS, trstACTIVE], 'Failed to BEGIN transaction');
  TestDBSetup.Database.Execute('CREATE TEMP TABLE foo()');
  TestDBSetup.Database.Commit;
  Check(TestDBSetup.Database.TransactionStatus = trstIDLE, 'Failed to COMMIT transaction');
end;

procedure TestTPSQLDatabase.TestGetCharsets;
var
  aList: TStrings;
begin
  aList := TStringList.Create;
  try
    TestDBSetup.Database.GetCharsets(aList);
    Check(aList.Count > 0, 'GetCharsets failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetDatabases;
var
  aList: TStrings;
  Pattern: string;
begin
  aList := TStringList.Create;
  try
    Pattern := '%';
    TestDBSetup.Database.GetDatabases(Pattern, aList);
    Check(aList.Count > 0, 'GetDatabases failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetSchemaNames;
var
  List: TStrings;
  SystemSchemas: Boolean;
  Pattern: string;
  Count: integer;
begin
  List := TStringList.Create;
  try
    Pattern := '%';
    SystemSchemas := True;
    TestDBSetup.Database.GetSchemaNames(Pattern, SystemSchemas, List);
    Count := List.Count;
    List.Clear;
    TestDBSetup.Database.GetSchemaNames(Pattern, not SystemSchemas, List);
    Check(List.Count <= Count, 'GetSchemaNames failed');
  finally
    {$IFNDEF NEXTGEN}
    List.Free;
    {$ELSE}
    List.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetStoredProcNames;
var
  aList: TStrings;
  Pattern: string;
begin
  aList := TStringList.Create;
  try
    Pattern := '%';
    TestDBSetup.Database.GetStoredProcNames(Pattern, aList);
    Check(aList.Count > 0, 'GetStoredProcNames failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetTableNames;
var
  List: TStrings;
  SystemTables: Boolean;
  Pattern: string;
  Count: integer;
begin
  List := TStringList.Create;
  try
    Pattern := '%';
    SystemTables := True;
    TestDBSetup.Database.GetTableNames(Pattern, SystemTables, List);
    Count := List.Count;
    List.Clear;
    TestDBSetup.Database.GetTableNames(Pattern, not SystemTables, List);
    Check(List.Count <= Count, 'GetTableNames failed');
  finally
    {$IFNDEF NEXTGEN}
    List.Free;
    {$ELSE}
    List.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetTablespaces;
var
  aList: TStrings;
  Pattern: string;
begin
  aList := TStringList.Create;
  try
    Pattern := '%';
    TestDBSetup.Database.GetTablespaces(Pattern, aList);
    Check(aList.Count > 0, 'GetTablespaces failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestGetUserNames;
var
  aList: TStrings;
  Pattern: string;
begin
  aList := TStringList.Create;
  try
    Pattern := '%';
    TestDBSetup.Database.GetUserNames(Pattern, aList);
    Check(aList.Count > 0, 'GetUserNames failed');
  finally
    {$IFNDEF NEXTGEN}
    aList.Free;
    {$ELSE}
    aList.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestIsThreadSafe;
begin
  Check(PSQLTypes.PQIsThreadSafe() = 1, 'Library loaded is thread unsafe');
end;

procedure TestTPSQLDatabase.TestLibraryVersion;
var Ver: Integer;
begin
  Ver := TestDBSetup.Database.LibraryVersionAsInt;
  Check(Ver > 0, 'LibraryVersionAsInt failed');
  Status(Format('Library version: %d', [Ver]));
end;

procedure TestTPSQLDatabase.TestPing;
begin
  Check(TestDBSetup.Database.Ping = pstOK, 'Ping failed');
end;

procedure TestTPSQLDatabase.TestPingEx;
var
  ConnParams: TStringList;
begin
  ConnParams := TStringList.Create;
  try
    ConnParams.Assign(TestDBSetup.Database.Params);
    Check(TestDBSetup.Database.Ping(ConnParams) = pstOK, 'PingEx failed');
  finally
    {$IFNDEF NEXTGEN}
    ConnParams.Free;
    {$ELSE}
    ConnParams.DisposeOf;
    {$ENDIF}
  end;
end;

procedure TestTPSQLDatabase.TestPlainConnInfoConnect;
var
  TempDB: TPSQLDatabase;
begin
  TempDB := TPSQLDatabase.Create(nil);
  TempDB.Assign(TestDBSetup.Database);

  TempDB.UseSingleLineConnInfo := True;
  try
    TempDB.Open;
    Check(TempDB.Connected, 'Failed to connect using PQconnectdb');
  finally
    TempDB.Free;
  end;
end;

procedure TestTPSQLDatabase.TestReset;
var
  TempDB: TPSQLDatabase;
begin
  TempDB := TPSQLDatabase.Create(nil);
  TempDB.Assign(TestDBSetup.Database);

  TempDB.UseSingleLineConnInfo := True;
  try
    TempDB.Open;
    Check(TempDB.Connected, 'Failed to connect using PQconnectdb');
    TempDB.Reset;
    Check(TempDB.Connected, 'Failed to reset using PQreset');
  finally
    TempDB.Free;
  end;
end;

procedure TestTPSQLDatabase.TestRollback;
begin
  TestDBSetup.Database.StartTransaction;
  Check(TestDBSetup.Database.TransactionStatus in [trstINTRANS, trstACTIVE], 'Failed to BEGIN transaction');
  TestDBSetup.Database.Execute('CREATE TEMP TABLE bar()');
  TestDBSetup.Database.Rollback;
  Check(TestDBSetup.Database.TransactionStatus = trstIDLE, 'Failed to ROLLBACK transaction');
end;

initialization

{$IFDEF DUNITX}
  TDUnitX.RegisterTestFixture(TestTPSQLDatabase);
{$ENDIF}
end.

