From ae42461f44cf63a66a087cf8c2660c3d914f474c Mon Sep 17 00:00:00 2001 From: Andy Shaw Date: Tue, 22 May 2018 15:22:23 +0200 Subject: MySQL: Handle TIME types as a string to allow the full range of data As the full range of TIME is '-838:59:59' to '838:59:59' then we cannot use QTime as the object to store this data in. Therefore a QString is used instead for passing the data to and from. This does not impact existing code using QTime already as it will still convert it from the QString to a QTime to give the same result as before. [ChangeLog][QtSql][MySQL] The TIME data type is now treated like a string-based type in order to respect the full range of the TIME data type. Task-number: QTBUG-57028 Change-Id: Ieb7105bff3043b845f76bc873d088e6bac1e4f10 Reviewed-by: Edward Welbourne --- tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp | 55 +++++++++++++++++++++++ 1 file changed, 55 insertions(+) (limited to 'tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp') diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp index c64310a715..4ce1009c90 100644 --- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp @@ -180,6 +180,8 @@ private slots: void timeStampParsing(); void sqliteVirtualTable_data() { generic_data("QSQLITE"); } void sqliteVirtualTable(); + void mysql_timeType_data() { generic_data("QMYSQL"); } + void mysql_timeType(); #ifdef NOT_READY_YET void task_229811(); @@ -4722,5 +4724,58 @@ void tst_QSqlQuery::sqliteVirtualTable() QCOMPARE(qry.value(1).toString(), "Peter"); } +void tst_QSqlQuery::mysql_timeType() +{ + // The TIME data type is different to the standard with MySQL as it has a range of + // '-838:59:59' to '838:59:59'. + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + const auto tableName = qTableName("mysqlTimeType", __FILE__, db); + tst_Databases::safeDropTables(db, { tableName }); + QSqlQuery qry(db); + QVERIFY_SQL(qry, exec("create table " + tableName + " (t time(6))")); + + // MySQL will convert days into hours and add them together so 17 days 11 hours becomes 419 hours + const QStringList timeData = { "-838:59:59.000000", "-123:45:56.789", "000:00:00.0", "123:45:56.789", + "838:59:59.000000", "15:50", "12", "1213", "0 1:2:3", "17 11:22:33" }; + const QStringList resultTimeData = { "-838:59:59.000000", "-123:45:56.789000", "00:00:00.000000", + "123:45:56.789000", "838:59:59.000000", "15:50:00.000000", "00:00:12.000000", "00:12:13.000000", + "01:02:03.000000", "419:22:33.000000" }; + for (const QString &time : timeData) + QVERIFY_SQL(qry, exec("insert into " + tableName + " (t) VALUES ('" + time + "')")); + + QVERIFY_SQL(qry, exec("select * from " + tableName)); + for (const QString &time : qAsConst(resultTimeData)) { + QVERIFY(qry.next()); + QCOMPARE(qry.value(0).toString(), time); + } + + QVERIFY_SQL(qry, exec("delete from " + tableName)); + for (const QString &time : timeData) { + QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)")); + qry.bindValue(0, time); + QVERIFY_SQL(qry, exec()); + } + QVERIFY_SQL(qry, exec("select * from " + tableName)); + for (const QString &time : resultTimeData) { + QVERIFY(qry.next()); + QCOMPARE(qry.value(0).toString(), time); + } + + QVERIFY_SQL(qry, exec("delete from " + tableName)); + const QList qTimeBasedData = { QTime(), QTime(1, 2, 3, 4), QTime(0, 0, 0, 0), QTime(23,59,59,999) }; + for (const QTime &time : qTimeBasedData) { + QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)")); + qry.bindValue(0, time); + QVERIFY_SQL(qry, exec()); + } + QVERIFY_SQL(qry, exec("select * from " + tableName)); + for (const QTime &time : qTimeBasedData) { + QVERIFY(qry.next()); + QCOMPARE(qry.value(0).toTime(), time); + } +} + QTEST_MAIN( tst_QSqlQuery ) #include "tst_qsqlquery.moc" -- cgit v1.2.3