fbintf/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:	335
Committed:	Fri Feb 26 16:45:54 2021 UTC (3 years, 8 months ago) by tony
Content type:	text/x-pascal
File size:	18520 byte(s)
Log Message:	Add missing fbintf tests
#	Content
1	(*
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