From 7775eb248ac9cab0ea1e882889f00bec0efb178f Mon Sep 17 00:00:00 2001 From: Andy Shaw Date: Thu, 1 Mar 2018 06:45:45 +0100 Subject: sqlite: Support using execBatch() with duplicated named placeholders Also expands the tst_qsqlquery::batchExec() test to account for this case and generally test the functionality. In addition it is made to be more robust to avoid any discrepencies with the testing data. The test in general is also cleaned up to enable more of it being tested with the different database drivers where possible. An expected fail is added for MySQL due to the fact that it has a bug where null timestamp entries are being converted to the current datetime when adding it as a bind value. Change-Id: I0061bd1c69ae35b4858afc49420f13ce59cf48ae Reviewed-by: Edward Welbourne --- src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp | 25 ++++ tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp | 142 +++++++++++++--------- 2 files changed, 109 insertions(+), 58 deletions(-) diff --git a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp index 91d2e9b205..6375825720 100644 --- a/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp +++ b/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp @@ -56,6 +56,7 @@ #include #endif #include +#include #if defined Q_OS_WIN # include @@ -129,6 +130,7 @@ protected: bool gotoNext(QSqlCachedResult::ValueCache& row, int idx) override; bool reset(const QString &query) override; bool prepare(const QString &query) override; + bool execBatch(bool arrayBind) override; bool exec() override; int size() override; int numRowsAffected() override; @@ -443,6 +445,29 @@ static QString timespecToString(const QDateTime &dateTime) } } +bool QSQLiteResult::execBatch(bool arrayBind) +{ + Q_UNUSED(arrayBind); + Q_D(QSqlResult); + QScopedValueRollback> valuesScope(d->values); + QVector values = d->values; + if (values.count() == 0) + return false; + + for (int i = 0; i < values.at(0).toList().count(); ++i) { + d->values.clear(); + QScopedValueRollback>> indexesScope(d->indexes); + QHash>::const_iterator it = d->indexes.constBegin(); + while (it != d->indexes.constEnd()) { + bindValue(it.key(), values.at(it.value().first()).toList().at(i), QSql::In); + ++it; + } + if (!exec()) + return false; + } + return true; +} + bool QSQLiteResult::exec() { Q_D(QSQLiteResult); diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp index 6843ff7d4a..9093485c40 100644 --- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp @@ -359,7 +359,6 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db ) << qTableName("more_results", __FILE__, db) << qTableName("blobstest", __FILE__, db) << qTableName("oraRowId", __FILE__, db) - << qTableName("qtest_batch", __FILE__, db) << qTableName("bug43874", __FILE__, db) << qTableName("bug6421", __FILE__, db).toUpper() << qTableName("bug5765", __FILE__, db) @@ -2439,76 +2438,103 @@ void tst_QSqlQuery::batchExec() QSqlDatabase db = QSqlDatabase::database( dbName ); CHECK_DATABASE( db ); - if ( !db.driver()->hasFeature( QSqlDriver::BatchOperations ) ) - QSKIP( "Database can't do BatchOperations"); - QSqlQuery q( db ); const QString tableName = qTableName("qtest_batch", __FILE__, db); - - QVERIFY_SQL(q, exec("create table " + tableName + " (id int, name varchar(20), dt date, num numeric(8, 4), dtstamp TIMESTAMP(3))")); - QVERIFY_SQL(q, prepare("insert into " + tableName + " (id, name, dt, num, dtstamp) values (?, ?, ?, ?, ?)")); - - QVariantList intCol; - intCol << 1 << 2 << QVariant( QVariant::Int ); - - QVariantList charCol; - charCol << QLatin1String( "harald" ) << QLatin1String( "boris" ) << QVariant( QVariant::String ); - - QVariantList dateCol; - QDateTime dt = QDateTime( QDate::currentDate(), QTime( 1, 2, 3 ) ); - dateCol << dt << dt.addDays( -1 ) << QVariant( QVariant::DateTime ); - - QVariantList numCol; - numCol << 2.3 << 3.4 << QVariant( QVariant::Double ); - - QVariantList timeStampCol; - const QDateTime dtStamp = QDateTime(QDate::currentDate(), QTime(1, 2, 3, 4)); - timeStampCol << dtStamp << dtStamp.addDays(-1) << QVariant(QVariant::DateTime); - + tst_Databases::safeDropTable(db, tableName); + QVERIFY_SQL(q, exec(QStringLiteral("create table ") + tableName + + QStringLiteral(" (id int, name varchar(20), dt date, num numeric(8, 4), " + "dtstamp TIMESTAMP(3), extraId int, extraName varchar(20))"))); + + const QVariantList intCol = { 1, 2, QVariant(QVariant::Int) }; + const QVariantList charCol = { QStringLiteral("harald"), QStringLiteral("boris"), + QVariant(QVariant::String) }; + const QDateTime currentDateTime = QDateTime(QDateTime::currentDateTime()); + const QVariantList dateCol = { currentDateTime.date(), currentDateTime.date().addDays(-1), + QVariant(QVariant::Date) }; + const QVariantList numCol = { 2.3, 3.4, QVariant(QVariant::Double) }; + const QVariantList timeStampCol = { currentDateTime, currentDateTime.addDays(-1), + QVariant(QVariant::DateTime) }; + + // Test with positional placeholders + QVERIFY_SQL(q, prepare(QStringLiteral("insert into ") + tableName + + QStringLiteral(" (id, name, dt, num, dtstamp, extraId, extraName) values " + "(?, ?, ?, ?, ?, ?, ?)"))); q.addBindValue(intCol); q.addBindValue( charCol ); q.addBindValue( dateCol ); q.addBindValue( numCol ); q.addBindValue(timeStampCol); + q.addBindValue(intCol); + q.addBindValue(charCol); QVERIFY_SQL( q, execBatch() ); - QVERIFY_SQL(q, exec("select id, name, dt, num, dtstamp from " + tableName + " order by id")); - - QVERIFY( q.next() ); - QCOMPARE( q.value( 0 ).toInt(), 1 ); - QCOMPARE( q.value( 1 ).toString(), QString( "harald" ) ); - QCOMPARE( q.value( 2 ).toDateTime(), dt ); - QCOMPARE( q.value( 3 ).toDouble(), 2.3 ); - QCOMPARE(q.value(4).toDateTime(), dtStamp); + QVERIFY_SQL(q, exec(QStringLiteral("select id, name, dt, num, dtstamp, " + "extraId, extraName from ") + tableName)); - QVERIFY( q.next() ); - QCOMPARE( q.value( 0 ).toInt(), 2 ); - QCOMPARE( q.value( 1 ).toString(), QString( "boris" ) ); - QCOMPARE( q.value( 2 ).toDateTime(), dt.addDays( -1 ) ); - QCOMPARE( q.value( 3 ).toDouble(), 3.4 ); - QCOMPARE(q.value(4).toDateTime(), dtStamp.addDays(-1)); + for (int i = 0; i < intCol.size(); ++i) { + QVERIFY(q.next()); + QCOMPARE(q.value(0).toInt(), intCol.at(i)); + QCOMPARE(q.value(1).toString(), charCol.at(i)); + QCOMPARE(q.value(2).toDate(), dateCol.at(i)); + QCOMPARE(q.value(3).toDouble(), numCol.at(i)); + if (tst_Databases::getDatabaseType(db) == QSqlDriver::MySqlServer && timeStampCol.at(i).isNull()) { + QEXPECT_FAIL("", "This appears to be a bug in MySQL as it converts null datetimes to the " + "current datetime for a timestamp field", Continue); + } + QCOMPARE(q.value(4).toDateTime(), timeStampCol.at(i)); + QCOMPARE(q.value(5).toInt(), intCol.at(i)); + QCOMPARE(q.value(6).toString(), charCol.at(i)); + } - QVERIFY( q.next() ); - QVERIFY( q.value( 0 ).isNull() ); - QVERIFY( q.value( 1 ).isNull() ); - QVERIFY( q.value( 2 ).isNull() ); - QVERIFY( q.value( 3 ).isNull() ); - QVERIFY(q.value(4).isNull()); + // Empty table ready for retesting with duplicated named placeholders + QVERIFY_SQL(q, exec(QStringLiteral("delete from ") + tableName)); + QVERIFY_SQL(q, prepare(QStringLiteral("insert into ") + tableName + + QStringLiteral(" (id, name, dt, num, dtstamp, extraId, extraName) " + "values (:id, :name, :dt, :num, :dtstamp, :id, :name)"))); + q.bindValue(":id", intCol); + q.bindValue(":name", charCol); + q.bindValue(":dt", dateCol); + q.bindValue(":num", numCol); + q.bindValue(":dtstamp", timeStampCol); - const QString procName = qTableName("qtest_batch_proc", __FILE__, db); - QVERIFY_SQL(q, exec("create or replace procedure " + procName + " (x in timestamp, y out timestamp) is\n" - "begin\n" - " y := x;\n" - "end;\n")); - QVERIFY(q.prepare("call " + procName + "(?, ?)")); - q.addBindValue(timeStampCol, QSql::In); - QVariantList emptyDateTimes; - emptyDateTimes.reserve(timeStampCol.size()); - for (int i = 0; i < timeStampCol.size(); i++) - emptyDateTimes << QVariant(QDateTime()); - q.addBindValue(emptyDateTimes, QSql::Out); QVERIFY_SQL(q, execBatch()); - QCOMPARE(q.boundValue(1).toList(), timeStampCol); + QVERIFY_SQL(q, exec(QStringLiteral("select id, name, dt, num, dtstamp, extraId, extraName from ") + + tableName)); + + for (int i = 0; i < intCol.size(); ++i) { + QVERIFY(q.next()); + QCOMPARE(q.value(0).toInt(), intCol.at(i)); + QCOMPARE(q.value(1).toString(), charCol.at(i)); + QCOMPARE(q.value(2).toDate(), dateCol.at(i)); + QCOMPARE(q.value(3).toDouble(), numCol.at(i)); + if (tst_Databases::getDatabaseType(db) == QSqlDriver::MySqlServer && timeStampCol.at(i).isNull()) { + QEXPECT_FAIL("", "This appears to be a bug in MySQL as it converts null datetimes to the " + "current datetime for a timestamp field", Continue); + } + QCOMPARE(q.value(4).toDateTime(), timeStampCol.at(i)); + QCOMPARE(q.value(5).toInt(), intCol.at(i)); + QCOMPARE(q.value(6).toString(), charCol.at(i)); + } + + // Only test the prepared stored procedure approach where the driver has support + // for batch operations as this will not work without it + if (db.driver()->hasFeature(QSqlDriver::BatchOperations)) { + const QString procName = qTableName("qtest_batch_proc", __FILE__, db); + QVERIFY_SQL(q, exec("create or replace procedure " + procName + + " (x in timestamp, y out timestamp) is\n" + "begin\n" + " y := x;\n" + "end;\n")); + QVERIFY(q.prepare("call " + procName + "(?, ?)")); + q.addBindValue(timeStampCol, QSql::In); + QVariantList emptyDateTimes; + emptyDateTimes.reserve(timeStampCol.size()); + for (int i = 0; i < timeStampCol.size(); i++) + emptyDateTimes << QVariant(QDateTime()); + q.addBindValue(emptyDateTimes, QSql::Out); + QVERIFY_SQL(q, execBatch()); + QCOMPARE(q.boundValue(1).toList(), timeStampCol); + } } void tst_QSqlQuery::QTBUG_43874() -- cgit v1.2.3