SQL/IBASE: Time Zone support (firebird 4.x)

Add support for time zones in the IBASE driver, which was introduced in
firebird 4.x. TIMESTAMP WITH TIME ZONE data type is supported in order
to store and retrieve a QDateTime with the time zone.

Task-number: QTBUG-111879
Change-Id: I631b4262d17796a17630379b7d659f88244a23ad
Reviewed-by: Christian Ehrlicher <ch.ehrlicher@gmx.de>

2 files changed, 126 insertions, 0 deletions

diff --git a/tests/auto/sql/kernel/qsqldatabase/tst_databases.h b/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
index c715c1c2a8..c62f6a97ff 100644
--- a/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
+++ b/tests/auto/sql/kernel/qsqldatabase/tst_databases.h
@@ -9,6 +9,7 @@
 #include <QSqlDriver>
 #include <QSqlError>
 #include <QSqlQuery>
+#include <QSqlRecord>
 #include <QRegularExpression>
 #include <QRegularExpressionMatch>
 #include <QDir>
@@ -20,9 +21,12 @@
 #include <QJsonObject>
 #include <QJsonDocument>
 #include <QSysInfo>
+#include <QVersionNumber>
 #include <QtSql/private/qsqldriver_p.h>
 #include <QTest>
 
+using namespace Qt::StringLiterals;
+
 #define CHECK_DATABASE( db ) \
     if ( !db.isValid() ) { qFatal( "db is Invalid" ); }
 
@@ -434,6 +438,15 @@ public:
         return ver;
     }
 
+    static QVersionNumber getIbaseEngineVersion(const QSqlDatabase &db)
+    {
+        auto q = db.exec("SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;"_L1);
+        q.next();
+        auto record = q.record();
+        auto version = QVersionNumber::fromString(record.value(0).toString());
+        return version;
+    }
+
     QStringList     dbNames;
     int      counter = 0;
 
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 048b9b0984..431c2f6f92 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -246,6 +246,11 @@ private slots:
     void ibaseArray_data() { generic_data("QIBASE"); }
     void ibaseArray();
 
+    void ibaseDateTimeWithTZ_data();
+    void ibaseDateTimeWithTZ();
+    void ibaseTimeStampTzArray_data() { generic_data("QIBASE"); }
+    void ibaseTimeStampTzArray();
+
     // Double addDatabase() with same name leaves system in a state that breaks
     // invalidQuery() if run later; so put this one last !
     void prematureExec_data() { generic_data(); }
@@ -4818,6 +4823,79 @@ void tst_QSqlQuery::dateTime()
     }
 }
 
+void tst_QSqlQuery::ibaseDateTimeWithTZ_data()
+{
+    if (dbs.dbNames.isEmpty())
+        QSKIP("No database drivers are available in this Qt configuration");
+
+    QTest::addColumn<QString>("dbName");
+    QTest::addColumn<QString>("tableName");
+    QTest::addColumn<QList<QDateTime> >("initialDateTimes");
+    QTest::addColumn<QList<QDateTime> >("expectedDateTimes");
+
+    const QTimeZone afterUTCTimeZone("Asia/Hong_Kong");
+    const QTimeZone beforeUTCTimeZone("America/Los_Angeles");
+    const QTimeZone utcTimeZone("UTC");
+
+    const QDateTime dtWithAfterTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), afterUTCTimeZone);
+    const QDateTime dtWithBeforeTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), beforeUTCTimeZone);
+    const QDateTime dtWithUTCTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), utcTimeZone);
+    const QDateTime dtLocalTZ(QDateTime::currentDateTime());
+
+    const QList<QDateTime> dateTimes = {
+        dtWithAfterTZ,
+        dtWithBeforeTZ,
+        dtWithUTCTZ,
+        dtLocalTZ
+    };
+
+    for (const QString &dbName : std::as_const(dbs.dbNames)) {
+        QSqlDatabase db = QSqlDatabase::database(dbName);
+        if (!db.isValid())
+            continue;
+
+        const QString tableNameTSWithTimeZone(qTableName("dateTimeTSWithTZ", __FILE__, db));
+
+        QTest::newRow(QString(dbName + " timestamp with time zone").toLatin1())
+                        << dbName
+                        << tableNameTSWithTimeZone
+                        << dateTimes
+                        << dateTimes;
+    }
+}
+
+void tst_QSqlQuery::ibaseDateTimeWithTZ()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+    if (tst_Databases::getDatabaseType(db) != QSqlDriver::Interbase)
+        QSKIP("Implemented only for Interbase");
+
+    if (tst_Databases::getIbaseEngineVersion(db).majorVersion() < 4)
+        QSKIP("Time zone support only implemented for firebird engine version 4 and greater");
+
+    QFETCH(QString, tableName);
+    TableScope ts(db, tableName);
+
+    QSqlQuery q(db);
+    QVERIFY_SQL(q, exec(QString("CREATE TABLE " + tableName + "(dt timestamp with time zone)")));
+
+    QFETCH(QList<QDateTime>, initialDateTimes);
+    QFETCH(QList<QDateTime>, expectedDateTimes);
+
+    for (const QDateTime &dt : std::as_const(initialDateTimes)) {
+        QVERIFY_SQL(q, prepare(QLatin1String("INSERT INTO %1 values(:dt)").arg(tableName)));
+        q.bindValue(":dt", dt);
+        QVERIFY_SQL(q, exec());
+    }
+    QVERIFY_SQL(q, exec("SELECT * FROM " + tableName));
+    for (const QDateTime &dt : std::as_const(expectedDateTimes)) {
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDateTime(), dt);
+    }
+}
+
 void tst_QSqlQuery::sqliteVirtualTable()
 {
     // Virtual tables can behave differently when it comes to prepared
@@ -4953,6 +5031,41 @@ void tst_QSqlQuery::ibaseArray()
     QCOMPARE(qry.value(3).toList(), boolArray.toList());
 }
 
+void tst_QSqlQuery::ibaseTimeStampTzArray()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    if (tst_Databases::getIbaseEngineVersion(db).majorVersion() < 4)
+        QSKIP("Time zone support only implemented for firebird engine version 4 and greater");
+
+    TableScope ts(db, "ibasetstzarray", __FILE__);
+    QSqlQuery qry(db);
+    QVERIFY_SQL(qry, exec(QLatin1String(
+                              "create table %1 (timeStampData timestamp with time zone[0:4])").arg(ts.tableName())));
+    QVERIFY_SQL(qry, prepare(QLatin1String("insert into %1 (timeStampData)"
+                                           " values(?)").arg(ts.tableName())));
+
+    const QDateTime dtWithAfterTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), QTimeZone("Asia/Hong_Kong"));
+    const QDateTime dtWithBeforeTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), QTimeZone("America/Los_Angeles"));
+    const QDateTime dtWithUTCTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), QTimeZone("UTC"));
+    const QDateTime dtLocalTZ(QDateTime::currentDateTime());
+    const QDateTime dtWithMETTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), QTimeZone("MET"));
+
+
+    const auto timeStampData = QVariant{QVariantList{dtWithAfterTZ,
+                                                     dtWithBeforeTZ,
+                                                     dtWithUTCTZ,
+                                                     dtLocalTZ,
+                                                     dtWithMETTZ}};
+    qry.bindValue(0, timeStampData);
+    QVERIFY_SQL(qry, exec());
+    QVERIFY_SQL(qry, exec("select * from " + ts.tableName()));
+    QVERIFY(qry.next());
+    QCOMPARE(qry.value(0).toList(), timeStampData.toList());
+}
+
 void tst_QSqlQuery::ibase_executeBlock()
 {
     QFETCH(QString, dbName);