udr/testsuite/Test17.pas

(*
 *  Firebird Interface (fbintf) Test suite. This program is used to
 *  test the Firebird Pascal Interface and provide a semi-automated
 *  pass/fail check for each test.
 *
 *  The contents of this file are subject to the Initial Developer's
 *  Public License Version 1.0 (the "License"); you may not use this
 *  file except in compliance with the License. You may obtain a copy
 *  of the License here:
 *
 *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing rights
 *  and limitations under the License.
 *
 *  The Initial Developer of the Original Code is Tony Whyman.
 *
 *  The Original Code is (C) 2020 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*)

unit Test17;

{$IFDEF MSWINDOWS}
{$DEFINE WINDOWS}
{$ENDIF}

{$IFDEF FPC}
{$mode delphi}
{$codepage UTF8}
{$ENDIF}

{Test 17: Date/Time tests and Firebird 4 extensions}

{ $DEFINE CORE6303FIXED}

{
  This test provides a test of the ISQLTimestamp interface both for pre-Firebird 4
  attachments and with the Time Zone SQL types introduced in Firebird 4. It also
  provides tests for the new DECFloat16 and DECFloat34 types.

  1. A new temporary database is created and a single table added containing
     columns for each time zone type (all Firebird versions).

  2. Data insert is performed for the various ways of setting the column values.

  3. A Select query is used to read back the rows, testing out the data read variations.

  4. If the client library and attached server are Firebird 4 or later, then the
     tests are repeated with time zones added.
}

interface

uses
  Classes, SysUtils, TestApplication, FBTestApp, IB {$IFDEF WINDOWS},Windows{$ENDIF};

type

  { TTest17 }

  TTest17 = class(TFBTestBase)
  private
    FOnDate: TDateTime; //used for Time with Time Zone conversions
    procedure TestArrayTZDataTypes(Attachment: IAttachment);
    procedure TestFBTimezoneSettings(Attachment: IAttachment);
    procedure UpdateDatabase(Attachment: IAttachment);
    procedure UpdateDatabase4_TZ(Attachment: IAttachment);
    procedure QueryDatabase(Attachment: IAttachment);
    procedure QueryDatabase4_TZ(Attachment: IAttachment);
  public
    function TestTitle: AnsiString; override;
    procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
  end;


implementation

uses IBUtils, FmtBCD;

const
    sqlCreateTable =
    'Create Table TestData ('+
    'RowID Integer not null,'+
    'DateCol DATE,'+
    'TimeCol TIME,'+
    'TimestampCol TIMESTAMP,'+
    'Primary Key(RowID)'+
    ')';


    sqlInsert2 = 'Insert into TestData(RowID,DateCol,TimeCol,TimestampCol) VALUES(?,?,?,?)';

    sqlCreateTable2 =
    'Create Table FB4TestData_TZ ('+
    'RowID Integer not null,'+
    'TimeCol TIME WITH TIME ZONE,'+
    'TimestampCol TIMESTAMP WITH TIME ZONE,'+
    'Primary Key(RowID)'+
    ')';

    sqlCreateTable3 =
    'Create Table FB4TestData_ARTZ ('+
    'RowID Integer not null,'+
    'TimeCol TIME WITH TIME ZONE [0:16],'+
    'TimestampCol TIMESTAMP WITH TIME ZONE [0:16],'+
    'Primary Key(RowID)'+
    ')';


{ TTest17 }

procedure TTest17.TestArrayTZDataTypes(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    sqlInsert: AnsiString;
    ar: IArray;
    aDateTime: TDateTime;
    i: integer;
    ResultSet: IResultSet;
    Bounds: TArrayBounds;
    tzName: AnsiString;
    dstOffset: smallint;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  sqlInsert := 'Insert into FB4TestData_ARTZ(RowID) Values(1)';
  Statement := Attachment.Prepare(Transaction,sqlInsert);
  Statement.Execute;

  ar := Attachment.CreateArray(Transaction,'FB4TestData_ARTZ','TimeCol');
  for i := 0 to 16 do
  begin
    aDateTime := EncodeTime(16,i,0,0);
    ar.SetAsTime(i,aDateTime,FOnDate,TimeZoneID_GMT + 10*i);
  end;

  Statement := Attachment.Prepare(Transaction,'Update FB4TestData_ARTZ Set TimeCol = ? Where RowID = 1');
  Statement.SQLParams[0].AsArray := ar;
  Statement.Execute;

  ar := Attachment.CreateArray(Transaction,'FB4TestData_ARTZ','TimestampCol');
  for i := 0 to 16 do
  begin
    aDateTime := EncodeDate(2020,5,1) + EncodeTime(12,i,0,0);
    ar.SetAsDateTime(i,aDateTime,'America/New_York');
  end;
  Statement := Attachment.Prepare(Transaction,'Update FB4TestData_ARTZ Set TimestampCol = ? Where RowID = 1');
  Statement.SQLParams[0].AsArray := ar;
  Statement.Execute;

  Statement := Attachment.Prepare(Transaction,'Select * From FB4TestData_ARTZ');
  PrintMetaData(Statement.Metadata);
  writeln(OutFile);
  writeln(OutFile,'TimeZone Arrays');
  ResultSet := Statement.OpenCursor;
  while ResultSet.FetchNext do
  begin
    writeln(OutFile,'Row No ',ResultSet[0].AsInteger);
    ar := ResultSet[1].AsArray;
    Bounds := ar.GetBounds;
    for i := Bounds[0].LowerBound to Bounds[0].UpperBound do
    begin
      ar.GetAsDateTime(i,aDateTime,dstOffset,tzName);
      writeln(OutFile,'Time [',i,'] = ',TimeToStr(aDateTime),' dstOffset = ',dstOffset,' Time Zone = ',tzName);
    end;
    ar := ResultSet[2].AsArray;
    Bounds := ar.GetBounds;
    for i := Bounds[0].LowerBound to Bounds[0].UpperBound do
    begin
      ar.GetAsDateTime(i,aDateTime,dstOffset,tzName);
      writeln(OutFile,'Timestamp [',i,'] = ',DateTimeToStr(aDateTime),' dstOffset = ',dstOffset,' Time Zone = ',tzName);
    end;
  end;
end;

procedure TTest17.TestFBTimezoneSettings(Attachment: IAttachment);
var aDateTime: TDateTime;
    aTimeZone: AnsiString;
begin
  writeln(OutFile,'Test Time Zone Setting');
  with FirebirdAPI, Attachment.GetTimeZoneServices do
  begin
    writeln(OutFile,'Time Zone GMT = ',TimeZoneID2TimeZoneName(65535));
    writeln(OutFile,'Time Zone Zagreb = ',TimeZoneID2TimeZoneName(65037));
    writeln(OutFile,'Time Zone Offset -08:00 = ',TimeZoneID2TimeZoneName(959));
    writeln(OutFile,'Time Zone Offset +13:39 = ',TimeZoneID2TimeZoneName(2258));
    writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('+06:00'));
    writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('-08:00'),', UTC Time = ',
      DateTimeToStr(LocalTimeToGMT(EncodeDate(2020,1,23) + EncodeTime(2,30,0,0), '-08:00')));
    writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('US/Arizona'));
    writeln(OutFile,'Local Time Zone ID = ',TimeZoneName2TimeZoneID(''));
    writeln(OutFile,'Local Time = ',DateTimeToStr(GMTToLocalTime(EncodeDate(2020,1,23) + EncodeTime(2,30,0,0),'-08:00')),', Time Zone = -08:00');
    writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/1989 23:00:00'),'Europe/London'));
    writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/2000 23:00:00'),'Europe/London'));
    writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/1966 23:00:00'),'Europe/London'));
    writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/2/1989 12:00:00'),'Europe/London'));
    writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/4/1989 12:00:00'),'Europe/London'));
    try
      writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('26/3/1989 01:30:00'),'Europe/London'));
    except On E: Exception do
      writeln(OutFile,E.Message);
    end;
  end;
  if ParseDateTimeTZString('29/11/1969 23:30:00 GMT',aDateTime,aTimeZone) then
    writeln(OutFile,'Date = ',DateTimeToStr(aDateTime),', TimeZone = ',aTimeZone)
  else
    writeln(OutFile,'ParseDateTimeTZString failed');
  if ParseDateTimeTZString('1/4/2001 22:30:10.001 -08:00',aDateTime,aTimeZone) then
  {$if declared(DefaultFormatSettings)}
    with DefaultFormatSettings do
  {$else}
  {$if declared(FormatSettings)}
    with FormatSettings do
  {$ifend}{$ifend}
    writeln(OutFile,'Date = ',FBFormatDateTime(ShortDateFormat + ' ' + LongTimeFormat + '.zzzz',aDateTime),', TimeZone = ',aTimeZone)
  else
    writeln(OutFile,'ParseDateTimeTZString failed');
  if ParseDateTimeTZString('23:59:10.2 Europe/London',aDateTime,aTimeZone,true) then
  {$if declared(DefaultFormatSettings)}
    with DefaultFormatSettings do
  {$else}
  {$if declared(FormatSettings)}
    with FormatSettings do
  {$ifend}{$ifend}
    writeln(OutFile,'Time = ',FBFormatDateTime(LongTimeFormat + '.zzzz',aDateTime),', TimeZone = ',aTimeZone)
  else
    writeln(OutFile,'ParseDateTimeTZString failed');
end;

procedure TTest17.UpdateDatabase(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    FPCTimestamp: TTimestamp;
    sqlInsert: AnsiString;
    SysTime: TSystemTime;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  sqlInsert := 'Insert into TestData(RowID,DateCol,TimeCol,TimestampCol) Values(1,''2019.4.1'''+
              ',''11:31:05.0001'',''2016.2.29 22:2:35.0001'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);

  Statement := Attachment.Prepare(Transaction,SQLInsert2);

  Statement.SQLParams[0].AsInteger := 2;
  Statement.SQLParams[1].AsDate := EncodeDate(1939,9,3);
  Statement.SQLParams[2].AsTime := FBEncodeTime(15,40,0,2);
  Statement.SQLParams[3].AsDateTime := EncodeDate(1918,11,11) + EncodeTime(11,11,0,0);
  Statement.Execute;

  Statement.SQLParams[0].AsInteger := 3;
  Statement.SQLParams[1].AsDate := EncodeDate(1066,10,14);
  Statement.SQLParams[2].AsTime := FBEncodeTime(23,59,59,9994);
  {$IFDEF FPC}
  with SysTime do
  begin
    Year := 1918;
    Month := 11;
    Day := 11;
    Hour := 11;
    Minute := 11;
    Second := 0;
    Millisecond := 0;
  end;
  {$ELSE}
  with SysTime do
  begin
    wYear := 1918;
    wMonth := 11;
    wDay := 11;
    wHour := 11;
    wMinute := 11;
    wSecond := 0;
    wMilliseconds := 0;
  end;
  {$ENDIF}
  Statement.SQLParams[3].SetAsDateTime(SystemTimeToDateTime(SysTime));
  Statement.Execute;

  Statement.SQLParams[0].AsInteger := 4;
  Statement.SQLParams[1].SetAsDate(EncodeDate(1815,6,18));
  Statement.SQLParams[2].SetAsTime(FBEncodeTime(0,1,40,1115));
  FPCTimestamp.Date := Trunc(EncodeDate(1945,5,8)) + DateDelta;
  FPCTimestamp.Time :=  ((22*MinsPerHour + 10)*SecsPerMin)*MSecsPerSec + 1; {22:10:0.001)}
  Statement.SQLParams[3].SetAsDateTime(TimestampToDateTime(FPCTimestamp));
  Statement.Execute;
end;

procedure TTest17.UpdateDatabase4_TZ(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    sqlInsert: AnsiString;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  Attachment.GetTimeZoneServices.SetTimeTZDate(FOnDate);
  sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) ' +
               'Values(1,''11:32:10.0002 -05:00'',''2020.4.1'+
               ' 11:31:05.0001 +01:00'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);


  Statement := Attachment.Prepare(Transaction,'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(?,?,?)');

  Statement.SQLParams[0].AsInteger := 2;
  Statement.SQLParams[1].SetAsTime(EncodeTime(14,02,10,5),FOnDate,'-08:00');
  Statement.SQLParams[2].SetAsDateTime(EncodeDate(1918,11,11) + EncodeTime(11,11,0,0),'Europe/London');
  Statement.Execute;
  Statement.SQLParams[0].AsInteger := 3;
  Statement.SQLParams[1].SetAsTime(EncodeTime(22,02,10,5),FOnDate,'-08:00');
  Statement.SQLParams[2].SetAsDateTime(EncodeDate(1918,11,11) + EncodeTime(0,11,0,0),'+04:00');
  writeln(OutFile,'Show Parameter 2');
  writeSQLData(Statement.SQLParams[2] as ISQLData);
  Statement.Execute;
  Statement.SQLParams[0].AsInteger := 4;
  Statement.SQLParams[1].SetAsString('14:02:10.5 -08:00');
  Statement.SQLParams[2].SetAsString('11/11/1918 11:11:0');
  Statement.Execute;
  sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(5,'+
              '''11:31:05.0001 America/New_York'',''2020.7.4 07:00:00 America/New_York'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
  sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(6,'+
              '''11:31:05.0001 EST5EDT'',''01.JUL.2020 7:00 America/New_York'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
  sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(7,'+
              '''11:31:05.0001 BST'',''01.JUL.2020 7:00 EST5EDT'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
  sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(8,'+
              '''11:31:05.0001 EUrope/Paris'',''01.JUL.2020 6:00 EST'')';
  Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
end;

procedure TTest17.QueryDatabase(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    Results: IResultSet;
    SysTime: TSystemTime;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  Statement := Attachment.Prepare(Transaction,'Select * from  TestData');
  writeln(OutFile);
  writeln(OutFile,'Testdata');
  writeln(OutFile);
  PrintMetaData(Statement.MetaData);
  ReportResults(Statement);
  Statement := Attachment.Prepare(Transaction,'Select * from  TestData');
  writeln(OutFile);
  writeln(OutFile,'Testdata - second pass');
  writeln(OutFile);
  Results := Statement.OpenCursor;
  if Results.FetchNext then
    writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
      TimeToStr(Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
  if Results.FetchNext then
    writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
      FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
  if Results.FetchNext then
    writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
      FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
  if Results.FetchNext then
  begin
    writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
      FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
   DateTimeToSystemTime(Results[3].GetAsDateTime,SysTime);
   with SysTime do
   begin
      {$IFDEF FPC}
      writeln(OutFile,'Sys Time = ',Year, ', ',Month, ', ',Day, ', ',DayOfWeek, ', ',Hour, ', ',Minute, ', ',Second, ', ',MilliSecond);
      {$ELSE}
      writeln(OutFile,'Sys Time = ',wYear, ', ',wMonth, ', ',wDay, ', ',wDayOfWeek, ', ',wHour, ', ',wMinute, ', ',wSecond, ', ',wMilliSeconds);
      {$ENDIF}
    end;
  end;
end;

procedure TTest17.QueryDatabase4_TZ(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  Statement := Attachment.Prepare(Transaction,'Select ROWID, TIMECOL,TimeStampCol, Extract(TIMEZONE_HOUR FROM TIMECOL) AS TZ_HOUR,'+
                     'Extract(TIMEZONE_MINUTE FROM TIMECOL) as TZ_MINUTE from  FB4TestData_TZ');
  writeln(OutFile);
  writeln(OutFile,'FB4 Testdata_TZ');
  writeln(OutFile);
  PrintMetaData(Statement.MetaData);
  ReportResults(Statement);
end;

function TTest17.TestTitle: AnsiString;
begin
  Result := 'Test 17: Date/Time tests and Firebird 4 extensions';
end;

procedure TTest17.RunTest(CharSet: AnsiString; SQLDialect: integer);
var DPB: IDPB;
    Attachment: IAttachment;
    VerStrings: TStringList;
begin
  FOnDate := EncodeDate(2020,1,1);
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
  DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
  DPB.Add(isc_dpb_lc_ctype).setAsString('UTF8');
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
  Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
  VerStrings := TStringList.Create;
  try
    Attachment.getFBVersion(VerStrings);
    writeln(OutFile,' FBVersion = ',VerStrings[0]);
  finally
    VerStrings.Free;
  end;
  writeln(Outfile,'Has Local TZ DB = ',FirebirdAPI.HasLocalTZDB);
  Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
  UpdateDatabase(Attachment);
  QueryDatabase(Attachment);

  if (FirebirdAPI.GetClientMajor < 4) or (Attachment.GetODSMajorVersion < 13) then
    writeln(OutFile,'Skipping Firebird 4 and later test part')
  else
  begin
    writeln(OutFile);
    writeln(OutFile,'Firebird 4 extension types');
    writeln(OutFile,'==========================');
    writeln(OutFile);
    writeln(Outfile);
    writeln(Outfile,'Using local FB Client with server TZ database');
    writeln(Outfile);
    writeln(OutFile,'Local Time Zone Name = ',Attachment.GetTimeZoneServices.GetLocalTimeZoneName,
                     ', ID = ',Attachment.GetTimeZoneServices.GetLocalTimeZoneID);
    try
      TestFBTimezoneSettings(Attachment);

      Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
      Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable3);
      UpdateDatabase4_TZ(Attachment);
      QueryDatabase4_TZ(Attachment);
      {$IFDEF CORE6303FIXED}
      TestArrayTZDataTypes(Attachment);
      {$ENDIF}
    finally
      Attachment.DropDatabase;
    end;
    Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);

    writeln(Outfile);
    writeln(Outfile,'Using local TZ database');
    writeln(Outfile);
    Attachment.GetTimeZoneServices.SetUseLocalTZDB(true);
    TestFBTimezoneSettings(Attachment);
    Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
    UpdateDatabase4_TZ(Attachment);
    QueryDatabase4_TZ(Attachment);
    if FirebirdAPI.HasExtendedTZSupport then
    begin
     writeln(Outfile);
     writeln(Outfile,'Using Extended TZ Data Type');
     writeln(Outfile);
     Attachment.DropDatabase;
     Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);

     Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],'SET BIND OF TIME ZONE TO EXTENDED');
     Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
     UpdateDatabase4_TZ(Attachment);
     QueryDatabase4_TZ(Attachment);
    end;
  end;
  Attachment.DropDatabase;
end;

initialization
  RegisterTest(TTest17);
end.

Revision:	379
Committed:	Mon Jan 10 10:08:03 2022 UTC (2 years, 3 months ago) by tony
Content type:	text/x-pascal
File size:	18520 byte(s)
Log Message:	set line ending property
#	User	Rev	Content
1	tony	335	(*
2			* Firebird Interface (fbintf) Test suite. This program is used to
3			* test the Firebird Pascal Interface and provide a semi-automated
4			* pass/fail check for each test.
5			*
6			* The contents of this file are subject to the Initial Developer's
7			* Public License Version 1.0 (the "License"); you may not use this
8			* file except in compliance with the License. You may obtain a copy
9			* of the License here:
10			*
11			* http://www.firebirdsql.org/index.php?op=doc&id=idpl
12			*
13			* Software distributed under the License is distributed on an "AS
14			* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
15			* implied. See the License for the specific language governing rights
16			* and limitations under the License.
17			*
18			* The Initial Developer of the Original Code is Tony Whyman.
19			*
20			* The Original Code is (C) 2020 Tony Whyman, MWA Software
21			* (http://www.mwasoftware.co.uk).
22			*
23			* All Rights Reserved.
24			*
25			* Contributor(s): ______________________________________.
26			*
27			*)
28
29			unit Test17;
30
31			{$IFDEF MSWINDOWS}
32			{$DEFINE WINDOWS}
33			{$ENDIF}
34
35			{$IFDEF FPC}
36			{$mode delphi}
37			{$codepage UTF8}
38			{$ENDIF}
39
40			{Test 17: Date/Time tests and Firebird 4 extensions}
41
42			{ $DEFINE CORE6303FIXED}
43
44			{
45			This test provides a test of the ISQLTimestamp interface both for pre-Firebird 4
46			attachments and with the Time Zone SQL types introduced in Firebird 4. It also
47			provides tests for the new DECFloat16 and DECFloat34 types.
48
49			1. A new temporary database is created and a single table added containing
50			columns for each time zone type (all Firebird versions).
51
52			2. Data insert is performed for the various ways of setting the column values.
53
54			3. A Select query is used to read back the rows, testing out the data read variations.
55
56			4. If the client library and attached server are Firebird 4 or later, then the
57			tests are repeated with time zones added.
58			}
59
60			interface
61
62			uses
63			Classes, SysUtils, TestApplication, FBTestApp, IB {$IFDEF WINDOWS},Windows{$ENDIF};
64
65			type
66
67			{ TTest17 }
68
69			TTest17 = class(TFBTestBase)
70			private
71			FOnDate: TDateTime; //used for Time with Time Zone conversions
72			procedure TestArrayTZDataTypes(Attachment: IAttachment);
73			procedure TestFBTimezoneSettings(Attachment: IAttachment);
74			procedure UpdateDatabase(Attachment: IAttachment);
75			procedure UpdateDatabase4_TZ(Attachment: IAttachment);
76			procedure QueryDatabase(Attachment: IAttachment);
77			procedure QueryDatabase4_TZ(Attachment: IAttachment);
78			public
79			function TestTitle: AnsiString; override;
80			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
81			end;
82
83
84			implementation
85
86			uses IBUtils, FmtBCD;
87
88			const
89			sqlCreateTable =
90			'Create Table TestData ('+
91			'RowID Integer not null,'+
92			'DateCol DATE,'+
93			'TimeCol TIME,'+
94			'TimestampCol TIMESTAMP,'+
95			'Primary Key(RowID)'+
96			')';
97
98
99			sqlInsert2 = 'Insert into TestData(RowID,DateCol,TimeCol,TimestampCol) VALUES(?,?,?,?)';
100
101			sqlCreateTable2 =
102			'Create Table FB4TestData_TZ ('+
103			'RowID Integer not null,'+
104			'TimeCol TIME WITH TIME ZONE,'+
105			'TimestampCol TIMESTAMP WITH TIME ZONE,'+
106			'Primary Key(RowID)'+
107			')';
108
109			sqlCreateTable3 =
110			'Create Table FB4TestData_ARTZ ('+
111			'RowID Integer not null,'+
112			'TimeCol TIME WITH TIME ZONE [0:16],'+
113			'TimestampCol TIMESTAMP WITH TIME ZONE [0:16],'+
114			'Primary Key(RowID)'+
115			')';
116
117
118			{ TTest17 }
119
120			procedure TTest17.TestArrayTZDataTypes(Attachment: IAttachment);
121			var Transaction: ITransaction;
122			Statement: IStatement;
123			sqlInsert: AnsiString;
124			ar: IArray;
125			aDateTime: TDateTime;
126			i: integer;
127			ResultSet: IResultSet;
128			Bounds: TArrayBounds;
129			tzName: AnsiString;
130			dstOffset: smallint;
131			begin
132			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
133			sqlInsert := 'Insert into FB4TestData_ARTZ(RowID) Values(1)';
134			Statement := Attachment.Prepare(Transaction,sqlInsert);
135			Statement.Execute;
136
137			ar := Attachment.CreateArray(Transaction,'FB4TestData_ARTZ','TimeCol');
138			for i := 0 to 16 do
139			begin
140			aDateTime := EncodeTime(16,i,0,0);
141			ar.SetAsTime(i,aDateTime,FOnDate,TimeZoneID_GMT + 10*i);
142			end;
143
144			Statement := Attachment.Prepare(Transaction,'Update FB4TestData_ARTZ Set TimeCol = ? Where RowID = 1');
145			Statement.SQLParams[0].AsArray := ar;
146			Statement.Execute;
147
148			ar := Attachment.CreateArray(Transaction,'FB4TestData_ARTZ','TimestampCol');
149			for i := 0 to 16 do
150			begin
151			aDateTime := EncodeDate(2020,5,1) + EncodeTime(12,i,0,0);
152			ar.SetAsDateTime(i,aDateTime,'America/New_York');
153			end;
154			Statement := Attachment.Prepare(Transaction,'Update FB4TestData_ARTZ Set TimestampCol = ? Where RowID = 1');
155			Statement.SQLParams[0].AsArray := ar;
156			Statement.Execute;
157
158			Statement := Attachment.Prepare(Transaction,'Select * From FB4TestData_ARTZ');
159			PrintMetaData(Statement.Metadata);
160			writeln(OutFile);
161			writeln(OutFile,'TimeZone Arrays');
162			ResultSet := Statement.OpenCursor;
163			while ResultSet.FetchNext do
164			begin
165			writeln(OutFile,'Row No ',ResultSet[0].AsInteger);
166			ar := ResultSet[1].AsArray;
167			Bounds := ar.GetBounds;
168			for i := Bounds[0].LowerBound to Bounds[0].UpperBound do
169			begin
170			ar.GetAsDateTime(i,aDateTime,dstOffset,tzName);
171			writeln(OutFile,'Time [',i,'] = ',TimeToStr(aDateTime),' dstOffset = ',dstOffset,' Time Zone = ',tzName);
172			end;
173			ar := ResultSet[2].AsArray;
174			Bounds := ar.GetBounds;
175			for i := Bounds[0].LowerBound to Bounds[0].UpperBound do
176			begin
177			ar.GetAsDateTime(i,aDateTime,dstOffset,tzName);
178			writeln(OutFile,'Timestamp [',i,'] = ',DateTimeToStr(aDateTime),' dstOffset = ',dstOffset,' Time Zone = ',tzName);
179			end;
180			end;
181			end;
182
183			procedure TTest17.TestFBTimezoneSettings(Attachment: IAttachment);
184			var aDateTime: TDateTime;
185			aTimeZone: AnsiString;
186			begin
187			writeln(OutFile,'Test Time Zone Setting');
188			with FirebirdAPI, Attachment.GetTimeZoneServices do
189			begin
190			writeln(OutFile,'Time Zone GMT = ',TimeZoneID2TimeZoneName(65535));
191			writeln(OutFile,'Time Zone Zagreb = ',TimeZoneID2TimeZoneName(65037));
192			writeln(OutFile,'Time Zone Offset -08:00 = ',TimeZoneID2TimeZoneName(959));
193			writeln(OutFile,'Time Zone Offset +13:39 = ',TimeZoneID2TimeZoneName(2258));
194			writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('+06:00'));
195			writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('-08:00'),', UTC Time = ',
196			DateTimeToStr(LocalTimeToGMT(EncodeDate(2020,1,23) + EncodeTime(2,30,0,0), '-08:00')));
197			writeln(OutFile,'Time Zone ID = ',TimeZoneName2TimeZoneID('US/Arizona'));
198			writeln(OutFile,'Local Time Zone ID = ',TimeZoneName2TimeZoneID(''));
199			writeln(OutFile,'Local Time = ',DateTimeToStr(GMTToLocalTime(EncodeDate(2020,1,23) + EncodeTime(2,30,0,0),'-08:00')),', Time Zone = -08:00');
200			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/1989 23:00:00'),'Europe/London'));
201			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/2000 23:00:00'),'Europe/London'));
202			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/7/1966 23:00:00'),'Europe/London'));
203			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/2/1989 12:00:00'),'Europe/London'));
204			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('1/4/1989 12:00:00'),'Europe/London'));
205			try
206			writeln(OutFile,'Time Zone Offset = ',GetEffectiveOffsetMins(StrToDateTime('26/3/1989 01:30:00'),'Europe/London'));
207			except On E: Exception do
208			writeln(OutFile,E.Message);
209			end;
210			end;
211			if ParseDateTimeTZString('29/11/1969 23:30:00 GMT',aDateTime,aTimeZone) then
212			writeln(OutFile,'Date = ',DateTimeToStr(aDateTime),', TimeZone = ',aTimeZone)
213			else
214			writeln(OutFile,'ParseDateTimeTZString failed');
215			if ParseDateTimeTZString('1/4/2001 22:30:10.001 -08:00',aDateTime,aTimeZone) then
216			{$if declared(DefaultFormatSettings)}
217			with DefaultFormatSettings do
218			{$else}
219			{$if declared(FormatSettings)}
220			with FormatSettings do
221			{$ifend}{$ifend}
222			writeln(OutFile,'Date = ',FBFormatDateTime(ShortDateFormat + ' ' + LongTimeFormat + '.zzzz',aDateTime),', TimeZone = ',aTimeZone)
223			else
224			writeln(OutFile,'ParseDateTimeTZString failed');
225			if ParseDateTimeTZString('23:59:10.2 Europe/London',aDateTime,aTimeZone,true) then
226			{$if declared(DefaultFormatSettings)}
227			with DefaultFormatSettings do
228			{$else}
229			{$if declared(FormatSettings)}
230			with FormatSettings do
231			{$ifend}{$ifend}
232			writeln(OutFile,'Time = ',FBFormatDateTime(LongTimeFormat + '.zzzz',aDateTime),', TimeZone = ',aTimeZone)
233			else
234			writeln(OutFile,'ParseDateTimeTZString failed');
235			end;
236
237			procedure TTest17.UpdateDatabase(Attachment: IAttachment);
238			var Transaction: ITransaction;
239			Statement: IStatement;
240			FPCTimestamp: TTimestamp;
241			sqlInsert: AnsiString;
242			SysTime: TSystemTime;
243			begin
244			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
245			sqlInsert := 'Insert into TestData(RowID,DateCol,TimeCol,TimestampCol) Values(1,''2019.4.1'''+
246			',''11:31:05.0001'',''2016.2.29 22:2:35.0001'')';
247			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
248
249			Statement := Attachment.Prepare(Transaction,SQLInsert2);
250
251			Statement.SQLParams[0].AsInteger := 2;
252			Statement.SQLParams[1].AsDate := EncodeDate(1939,9,3);
253			Statement.SQLParams[2].AsTime := FBEncodeTime(15,40,0,2);
254			Statement.SQLParams[3].AsDateTime := EncodeDate(1918,11,11) + EncodeTime(11,11,0,0);
255			Statement.Execute;
256
257			Statement.SQLParams[0].AsInteger := 3;
258			Statement.SQLParams[1].AsDate := EncodeDate(1066,10,14);
259			Statement.SQLParams[2].AsTime := FBEncodeTime(23,59,59,9994);
260			{$IFDEF FPC}
261			with SysTime do
262			begin
263			Year := 1918;
264			Month := 11;
265			Day := 11;
266			Hour := 11;
267			Minute := 11;
268			Second := 0;
269			Millisecond := 0;
270			end;
271			{$ELSE}
272			with SysTime do
273			begin
274			wYear := 1918;
275			wMonth := 11;
276			wDay := 11;
277			wHour := 11;
278			wMinute := 11;
279			wSecond := 0;
280			wMilliseconds := 0;
281			end;
282			{$ENDIF}
283			Statement.SQLParams[3].SetAsDateTime(SystemTimeToDateTime(SysTime));
284			Statement.Execute;
285
286			Statement.SQLParams[0].AsInteger := 4;
287			Statement.SQLParams[1].SetAsDate(EncodeDate(1815,6,18));
288			Statement.SQLParams[2].SetAsTime(FBEncodeTime(0,1,40,1115));
289			FPCTimestamp.Date := Trunc(EncodeDate(1945,5,8)) + DateDelta;
290			FPCTimestamp.Time := ((22MinsPerHour + 10)SecsPerMin)*MSecsPerSec + 1; {22:10:0.001)}
291			Statement.SQLParams[3].SetAsDateTime(TimestampToDateTime(FPCTimestamp));
292			Statement.Execute;
293			end;
294
295			procedure TTest17.UpdateDatabase4_TZ(Attachment: IAttachment);
296			var Transaction: ITransaction;
297			Statement: IStatement;
298			sqlInsert: AnsiString;
299			begin
300			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
301			Attachment.GetTimeZoneServices.SetTimeTZDate(FOnDate);
302			sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) ' +
303			'Values(1,''11:32:10.0002 -05:00'',''2020.4.1'+
304			' 11:31:05.0001 +01:00'')';
305			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
306
307
308			Statement := Attachment.Prepare(Transaction,'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(?,?,?)');
309
310			Statement.SQLParams[0].AsInteger := 2;
311			Statement.SQLParams[1].SetAsTime(EncodeTime(14,02,10,5),FOnDate,'-08:00');
312			Statement.SQLParams[2].SetAsDateTime(EncodeDate(1918,11,11) + EncodeTime(11,11,0,0),'Europe/London');
313			Statement.Execute;
314			Statement.SQLParams[0].AsInteger := 3;
315			Statement.SQLParams[1].SetAsTime(EncodeTime(22,02,10,5),FOnDate,'-08:00');
316			Statement.SQLParams[2].SetAsDateTime(EncodeDate(1918,11,11) + EncodeTime(0,11,0,0),'+04:00');
317			writeln(OutFile,'Show Parameter 2');
318			writeSQLData(Statement.SQLParams[2] as ISQLData);
319			Statement.Execute;
320			Statement.SQLParams[0].AsInteger := 4;
321			Statement.SQLParams[1].SetAsString('14:02:10.5 -08:00');
322			Statement.SQLParams[2].SetAsString('11/11/1918 11:11:0');
323			Statement.Execute;
324			sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(5,'+
325			'''11:31:05.0001 America/New_York'',''2020.7.4 07:00:00 America/New_York'')';
326			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
327			sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(6,'+
328			'''11:31:05.0001 EST5EDT'',''01.JUL.2020 7:00 America/New_York'')';
329			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
330			sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(7,'+
331			'''11:31:05.0001 BST'',''01.JUL.2020 7:00 EST5EDT'')';
332			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
333			sqlInsert := 'Insert into FB4TestData_TZ(RowID,TimeCol,TimestampCol) Values(8,'+
334			'''11:31:05.0001 EUrope/Paris'',''01.JUL.2020 6:00 EST'')';
335			Attachment.ExecuteSQL(Transaction,sqlInsert,[]);
336			end;
337
338			procedure TTest17.QueryDatabase(Attachment: IAttachment);
339			var Transaction: ITransaction;
340			Statement: IStatement;
341			Results: IResultSet;
342			SysTime: TSystemTime;
343			begin
344			Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
345			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
346			writeln(OutFile);
347			writeln(OutFile,'Testdata');
348			writeln(OutFile);
349			PrintMetaData(Statement.MetaData);
350			ReportResults(Statement);
351			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
352			writeln(OutFile);
353			writeln(OutFile,'Testdata - second pass');
354			writeln(OutFile);
355			Results := Statement.OpenCursor;
356			if Results.FetchNext then
357			writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
358			TimeToStr(Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
359			if Results.FetchNext then
360			writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
361			FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
362			if Results.FetchNext then
363			writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
364			FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
365			if Results.FetchNext then
366			begin
367			writeln(OutFile,Results[0].AsInteger,', ',DateToStr(Results[1].AsDate),', ',
368			FormatDateTime('hh:nn:ss.zzz',Results[2].AsTime),', ', DateTimeToStr(Results[3].AsDateTime));
369			DateTimeToSystemTime(Results[3].GetAsDateTime,SysTime);
370			with SysTime do
371			begin
372			{$IFDEF FPC}
373			writeln(OutFile,'Sys Time = ',Year, ', ',Month, ', ',Day, ', ',DayOfWeek, ', ',Hour, ', ',Minute, ', ',Second, ', ',MilliSecond);
374			{$ELSE}
375			writeln(OutFile,'Sys Time = ',wYear, ', ',wMonth, ', ',wDay, ', ',wDayOfWeek, ', ',wHour, ', ',wMinute, ', ',wSecond, ', ',wMilliSeconds);
376			{$ENDIF}
377			end;
378			end;
379			end;
380
381			procedure TTest17.QueryDatabase4_TZ(Attachment: IAttachment);
382			var Transaction: ITransaction;
383			Statement: IStatement;
384			begin
385			Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
386			Statement := Attachment.Prepare(Transaction,'Select ROWID, TIMECOL,TimeStampCol, Extract(TIMEZONE_HOUR FROM TIMECOL) AS TZ_HOUR,'+
387			'Extract(TIMEZONE_MINUTE FROM TIMECOL) as TZ_MINUTE from FB4TestData_TZ');
388			writeln(OutFile);
389			writeln(OutFile,'FB4 Testdata_TZ');
390			writeln(OutFile);
391			PrintMetaData(Statement.MetaData);
392			ReportResults(Statement);
393			end;
394
395			function TTest17.TestTitle: AnsiString;
396			begin
397			Result := 'Test 17: Date/Time tests and Firebird 4 extensions';
398			end;
399
400			procedure TTest17.RunTest(CharSet: AnsiString; SQLDialect: integer);
401			var DPB: IDPB;
402			Attachment: IAttachment;
403			VerStrings: TStringList;
404			begin
405			FOnDate := EncodeDate(2020,1,1);
406			DPB := FirebirdAPI.AllocateDPB;
407			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
408			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
409			DPB.Add(isc_dpb_lc_ctype).setAsString('UTF8');
410			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
411			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
412			VerStrings := TStringList.Create;
413			try
414			Attachment.getFBVersion(VerStrings);
415			writeln(OutFile,' FBVersion = ',VerStrings[0]);
416			finally
417			VerStrings.Free;
418			end;
419			writeln(Outfile,'Has Local TZ DB = ',FirebirdAPI.HasLocalTZDB);
420			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
421			UpdateDatabase(Attachment);
422			QueryDatabase(Attachment);
423
424			if (FirebirdAPI.GetClientMajor < 4) or (Attachment.GetODSMajorVersion < 13) then
425			writeln(OutFile,'Skipping Firebird 4 and later test part')
426			else
427			begin
428			writeln(OutFile);
429			writeln(OutFile,'Firebird 4 extension types');
430			writeln(OutFile,'==========================');
431			writeln(OutFile);
432			writeln(Outfile);
433			writeln(Outfile,'Using local FB Client with server TZ database');
434			writeln(Outfile);
435			writeln(OutFile,'Local Time Zone Name = ',Attachment.GetTimeZoneServices.GetLocalTimeZoneName,
436			', ID = ',Attachment.GetTimeZoneServices.GetLocalTimeZoneID);
437			try
438			TestFBTimezoneSettings(Attachment);
439
440			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
441			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable3);
442			UpdateDatabase4_TZ(Attachment);
443			QueryDatabase4_TZ(Attachment);
444			{$IFDEF CORE6303FIXED}
445			TestArrayTZDataTypes(Attachment);
446			{$ENDIF}
447			finally
448			Attachment.DropDatabase;
449			end;
450			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
451
452			writeln(Outfile);
453			writeln(Outfile,'Using local TZ database');
454			writeln(Outfile);
455			Attachment.GetTimeZoneServices.SetUseLocalTZDB(true);
456			TestFBTimezoneSettings(Attachment);
457			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
458			UpdateDatabase4_TZ(Attachment);
459			QueryDatabase4_TZ(Attachment);
460			if FirebirdAPI.HasExtendedTZSupport then
461			begin
462			writeln(Outfile);
463			writeln(Outfile,'Using Extended TZ Data Type');
464			writeln(Outfile);
465			Attachment.DropDatabase;
466			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
467
468			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],'SET BIND OF TIME ZONE TO EXTENDED');
469			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable2);
470			UpdateDatabase4_TZ(Attachment);
471			QueryDatabase4_TZ(Attachment);
472			end;
473			end;
474			Attachment.DropDatabase;
475			end;
476
477			initialization
478			RegisterTest(TTest17);
479			end.
480