From a245c312b87a87ed6707e255de627aa8d1e0be7b Mon Sep 17 00:00:00 2001 From: Andy Shaw Date: Wed, 10 Jan 2018 15:52:11 +0100 Subject: psql: Fix SQL tests This also accounts for some quirks on the PostgreSQL side: - Null values for a related table are placed in a different order when sorted. - Functions (sum, count) return a different type than other databases - Using quotes to account for case sensitivity with tables Task-number: QTBUG-63861 Change-Id: Ib1894fa8d0c77d7045941f7c57be0d0acd8d117e Reviewed-by: Edward Welbourne --- tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp | 75 +++++++++++++++------- .../tst_qsqlrelationaltablemodel.cpp | 33 ++++++---- .../models/qsqltablemodel/tst_qsqltablemodel.cpp | 2 +- 3 files changed, 73 insertions(+), 37 deletions(-) diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp index ada57996f4..aba99a9e20 100644 --- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp @@ -2711,8 +2711,22 @@ void tst_QSqlQuery::lastInsertId() QSqlQuery q( db ); - QVERIFY_SQL( q, exec( "insert into " + qtest + " values (41, 'VarChar41', 'Char41')" ) ); - + const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db); + // PostgreSQL >= 8.1 relies on lastval() which does not work if a value is + // manually inserted to the serial field, so we create a table specifically + if (dbType == QSqlDriver::PostgreSQL) { + const auto tst_lastInsertId = qTableName("tst_lastInsertId", __FILE__, db); + tst_Databases::safeDropTable(db, tst_lastInsertId); + QVERIFY_SQL(q, exec(QStringLiteral("create table ") + tst_lastInsertId + + QStringLiteral(" (id serial not null, t_varchar " + "varchar(20), t_char char(20), primary key(id))"))); + QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + tst_lastInsertId + + QStringLiteral(" (t_varchar, t_char) values " + "('VarChar41', 'Char41')"))); + } else { + QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + qtest + + QStringLiteral(" values (41, 'VarChar41', 'Char41')"))); + } QVariant v = q.lastInsertId(); QVERIFY( v.isValid() ); @@ -3269,10 +3283,19 @@ void tst_QSqlQuery::timeStampParsing() const QString tableName(qTableName("timeStampParsing", __FILE__, db)); tst_Databases::safeDropTable(db, tableName); QSqlQuery q(db); - QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName - + QStringLiteral(" (id integer, datefield timestamp)"))); - QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO ") + tableName - + QStringLiteral(" (datefield) VALUES (current_timestamp)"))); + QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db); + if (dbType == QSqlDriver::PostgreSQL) { + QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("(" + "id serial NOT NULL, " + "datefield timestamp, primary key(id));"))); + } else { + QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("(" + "\"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT," + "\"datefield\" timestamp);"))); + } + QVERIFY_SQL(q, exec( + QStringLiteral("INSERT INTO ") + tableName + QStringLiteral(" (datefield) VALUES (current_timestamp);" + ))); QVERIFY_SQL(q, exec(QStringLiteral("SELECT * FROM ") + tableName)); while (q.next()) QVERIFY(q.value(1).toDateTime().isValid()); @@ -3643,15 +3666,17 @@ void tst_QSqlQuery::QTBUG_18435() void tst_QSqlQuery::QTBUG_5251() { + // Since QSqlTableModel will escape the identifiers, we need to escape + // them for databases that are case sensitive QFETCH( QString, dbName ); QSqlDatabase db = QSqlDatabase::database( dbName ); CHECK_DATABASE( db ); const QString timetest(qTableName("timetest", __FILE__, db)); - + tst_Databases::safeDropTable(db, timetest); QSqlQuery q(db); - q.exec("DROP TABLE " + timetest); - QVERIFY_SQL(q, exec("CREATE TABLE " + timetest + " (t TIME)")); - QVERIFY_SQL(q, exec("INSERT INTO " + timetest + " VALUES ('1:2:3.666')")); + QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE \"") + timetest + QStringLiteral("\" (t TIME)"))); + QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO \"") + timetest + + QStringLiteral("\" VALUES ('1:2:3.666')"))); QSqlTableModel timetestModel(0,db); timetestModel.setEditStrategy(QSqlTableModel::OnManualSubmit); @@ -3664,7 +3689,8 @@ void tst_QSqlQuery::QTBUG_5251() QVERIFY_SQL(timetestModel, submitAll()); QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500")); - QVERIFY_SQL(q, exec("UPDATE " + timetest + " SET t = '0:11:22.33'")); + QVERIFY_SQL(q, exec(QStringLiteral("UPDATE \"") + timetest + + QStringLiteral("\" SET t = '0:11:22.33'"))); QVERIFY_SQL(timetestModel, select()); QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:11:22.330")); @@ -4241,12 +4267,18 @@ void tst_QSqlQuery::aggregateFunctionTypes() QSqlDatabase db = QSqlDatabase::database(dbName); CHECK_DATABASE(db); QVariant::Type intType = QVariant::Int; + QVariant::Type sumType = intType; + QVariant::Type countType = intType; // QPSQL uses LongLong for manipulation of integers const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db); - if (dbType == QSqlDriver::PostgreSQL) - intType = QVariant::LongLong; - else if (dbType == QSqlDriver::Oracle) - intType = QVariant::Double; + if (dbType == QSqlDriver::PostgreSQL) { + sumType = countType = QVariant::LongLong; + } else if (dbType == QSqlDriver::Oracle) { + intType = sumType = countType = QVariant::Double; + } else if (dbType == QSqlDriver::MySqlServer) { + sumType = QVariant::Double; + countType = QVariant::LongLong; + } { const QString tableName(qTableName("numericFunctionsWithIntValues", __FILE__, db)); tst_Databases::safeDropTable( db, tableName ); @@ -4259,10 +4291,8 @@ void tst_QSqlQuery::aggregateFunctionTypes() QVERIFY(q.next()); if (dbType == QSqlDriver::SQLite) QCOMPARE(q.record().field(0).type(), QVariant::Invalid); - else if (dbType == QSqlDriver::MySqlServer) - QCOMPARE(q.record().field(0).type(), QVariant::Double); else - QCOMPARE(q.record().field(0).type(), intType); + QCOMPARE(q.record().field(0).type(), sumType); QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1)")); QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2)")); @@ -4270,10 +4300,7 @@ void tst_QSqlQuery::aggregateFunctionTypes() QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName)); QVERIFY(q.next()); QCOMPARE(q.value(0).toInt(), 3); - if (dbType == QSqlDriver::MySqlServer) - QCOMPARE(q.record().field(0).type(), QVariant::Double); - else - QCOMPARE(q.record().field(0).type(), intType); + QCOMPARE(q.record().field(0).type(), sumType); QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName)); QVERIFY(q.next()); @@ -4289,7 +4316,7 @@ void tst_QSqlQuery::aggregateFunctionTypes() QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName)); QVERIFY(q.next()); QCOMPARE(q.value(0).toInt(), 2); - QCOMPARE(q.record().field(0).type(), dbType != QSqlDriver::MySqlServer ? intType : QVariant::LongLong); + QCOMPARE(q.record().field(0).type(), countType); QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName)); QVERIFY(q.next()); @@ -4332,7 +4359,7 @@ void tst_QSqlQuery::aggregateFunctionTypes() QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName)); QVERIFY(q.next()); QCOMPARE(q.value(0).toInt(), 2); - QCOMPARE(q.record().field(0).type(), dbType != QSqlDriver::MySqlServer ? intType : QVariant::LongLong); + QCOMPARE(q.record().field(0).type(), countType); QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName)); QVERIFY(q.next()); diff --git a/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp b/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp index 84cca482fb..f1c55df1ef 100644 --- a/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp +++ b/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp @@ -385,6 +385,7 @@ void tst_QSqlRelationalTableModel::setData() model.setRelation(1, QSqlRelation(reltest5, "title", "abbrev")); model.setEditStrategy(QSqlTableModel::OnManualSubmit); model.setJoinMode(QSqlRelationalTableModel::LeftJoin); + model.setSort(0, Qt::AscendingOrder); QVERIFY_SQL(model, select()); QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr")); @@ -783,24 +784,32 @@ void tst_QSqlRelationalTableModel::sort() QVERIFY_SQL(model, select()); QCOMPARE(model.rowCount(), 6); - QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister")); - QCOMPARE(model.data(model.index(1, 2)).toString(), QString("mister")); - QCOMPARE(model.data(model.index(2, 2)).toString(), QString("herr")); - QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr")); - QCOMPARE(model.data(model.index(4, 2)).toString(), QString("")); - QCOMPARE(model.data(model.index(5, 2)).toString(), QString("")); + + QStringList stringsInDatabaseOrder; + // PostgreSQL puts the null ones (from the table with the original value) first in descending order + // which translate to empty strings in the related table + if (dbType == QSqlDriver::PostgreSQL) + stringsInDatabaseOrder << "" << "" << "mister" << "mister" << "herr" << "herr"; + else + stringsInDatabaseOrder << "mister" << "mister" << "herr" << "herr" << "" << ""; + for (int i = 0; i < 6; ++i) + QCOMPARE(model.data(model.index(i, 2)).toString(), stringsInDatabaseOrder.at(i)); model.setSort(3, Qt::AscendingOrder); QVERIFY_SQL(model, select()); + // PostgreSQL puts the null ones (from the table with the original value) first in descending order + // which translate to empty strings in the related table + stringsInDatabaseOrder.clear(); + if (dbType == QSqlDriver::PostgreSQL) + stringsInDatabaseOrder << "herr" << "mister" << "mister" << "mister" << "mister" << ""; + else if (dbType != QSqlDriver::Sybase) + stringsInDatabaseOrder << "" << "herr" << "mister" << "mister" << "mister" << "mister"; + if (dbType != QSqlDriver::Sybase) { QCOMPARE(model.rowCount(), 6); - QCOMPARE(model.data(model.index(0, 3)).toString(), QString("")); - QCOMPARE(model.data(model.index(1, 3)).toString(), QString("herr")); - QCOMPARE(model.data(model.index(2, 3)).toString(), QString("mister")); - QCOMPARE(model.data(model.index(3, 3)).toString(), QString("mister")); - QCOMPARE(model.data(model.index(4, 3)).toString(), QString("mister")); - QCOMPARE(model.data(model.index(5, 3)).toString(), QString("mister")); + for (int i = 0; i < 6; ++i) + QCOMPARE(model.data(model.index(i, 3)).toString(), stringsInDatabaseOrder.at(i)); } else { QCOMPARE(model.data(model.index(0, 3)).toInt(), 1); QCOMPARE(model.data(model.index(1, 3)).toInt(), 2); diff --git a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp index bf76ed2bb6..ded360ef8d 100644 --- a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp +++ b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp @@ -275,7 +275,7 @@ void tst_QSqlTableModel::init() void tst_QSqlTableModel::cleanup() { - repopulateTestTables(); + recreateTestTables(); } void tst_QSqlTableModel::select() -- cgit v1.2.3