diff options
Diffstat (limited to 'tests/auto/qsqldatabase')
| -rw-r--r-- | tests/auto/qsqldatabase/.gitignore | 1 | ||||
| -rw-r--r-- | tests/auto/qsqldatabase/qsqldatabase.pro | 20 | ||||
| -rwxr-xr-x | tests/auto/qsqldatabase/testdata/qtest.mdb | bin | 0 -> 65536 bytes | |||
| -rw-r--r-- | tests/auto/qsqldatabase/tst_databases.h | 454 | ||||
| -rw-r--r-- | tests/auto/qsqldatabase/tst_qsqldatabase.cpp | 2319 |
5 files changed, 2794 insertions, 0 deletions
diff --git a/tests/auto/qsqldatabase/.gitignore b/tests/auto/qsqldatabase/.gitignore new file mode 100644 index 0000000..1dfa12b --- /dev/null +++ b/tests/auto/qsqldatabase/.gitignore @@ -0,0 +1 @@ +tst_qsqldatabase diff --git a/tests/auto/qsqldatabase/qsqldatabase.pro b/tests/auto/qsqldatabase/qsqldatabase.pro new file mode 100644 index 0000000..534a2d3 --- /dev/null +++ b/tests/auto/qsqldatabase/qsqldatabase.pro @@ -0,0 +1,20 @@ +load(qttest_p4) +SOURCES += tst_qsqldatabase.cpp + +QT += sql + +contains(QT_CONFIG, qt3support): QT += qt3support + +win32:!wince*: LIBS += -lws2_32 + +wince*: { + DEPLOYMENT_PLUGIN += qsqlite + + testData.sources = testdata + testData.path = . + + DEPLOYMENT += testData +} + + + diff --git a/tests/auto/qsqldatabase/testdata/qtest.mdb b/tests/auto/qsqldatabase/testdata/qtest.mdb Binary files differnew file mode 100755 index 0000000..f388f19 --- /dev/null +++ b/tests/auto/qsqldatabase/testdata/qtest.mdb diff --git a/tests/auto/qsqldatabase/tst_databases.h b/tests/auto/qsqldatabase/tst_databases.h new file mode 100644 index 0000000..b529b7e --- /dev/null +++ b/tests/auto/qsqldatabase/tst_databases.h @@ -0,0 +1,454 @@ +/**************************************************************************** +** +** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies). +** Contact: Qt Software Information (qt-info@nokia.com) +** +** This file is part of the test suite of the Qt Toolkit. +** +** $QT_BEGIN_LICENSE:LGPL$ +** No Commercial Usage +** This file contains pre-release code and may not be distributed. +** You may use this file in accordance with the terms and conditions +** contained in the either Technology Preview License Agreement or the +** Beta Release License Agreement. +** +** GNU Lesser General Public License Usage +** Alternatively, this file may be used under the terms of the GNU Lesser +** General Public License version 2.1 as published by the Free Software +** Foundation and appearing in the file LICENSE.LGPL included in the +** packaging of this file. Please review the following information to +** ensure the GNU Lesser General Public License version 2.1 requirements +** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html. +** +** In addition, as a special exception, Nokia gives you certain +** additional rights. These rights are described in the Nokia Qt LGPL +** Exception version 1.0, included in the file LGPL_EXCEPTION.txt in this +** package. +** +** GNU General Public License Usage +** Alternatively, this file may be used under the terms of the GNU +** General Public License version 3.0 as published by the Free Software +** Foundation and appearing in the file LICENSE.GPL included in the +** packaging of this file. Please review the following information to +** ensure the GNU General Public License version 3.0 requirements will be +** met: http://www.gnu.org/copyleft/gpl.html. +** +** If you are unsure which license is appropriate for your use, please +** contact the sales department at qt-sales@nokia.com. +** $QT_END_LICENSE$ +** +****************************************************************************/ +/* possible connection parameters */ + +#ifndef TST_DATABASES_H +#define TST_DATABASES_H + +#include <QSqlDatabase> +#include <QSqlDriver> +#include <QSqlError> +#include <QSqlQuery> +#include <QRegExp> +#include <QDir> +#include <QVariant> +#include <QDebug> + +#include <QtTest/QtTest> + +#if defined (Q_OS_WIN) || defined (Q_OS_WIN32) +# include <qt_windows.h> +# if defined (Q_OS_WINCE) +# include <winsock2.h> +# endif +#else +#include <unistd.h> +#endif + +#define CHECK_DATABASE( db ) \ + if ( !db.isValid() ) { qFatal( "db is Invalid" ); } + +#define QVERIFY_SQL(q, stmt) QVERIFY2((q).stmt, tst_Databases::printError((q).lastError(), db)) +#define QFAIL_SQL(q, stmt) QVERIFY2(!(q).stmt, tst_Databases::printError((q).lastError(), db)) + +#define DBMS_SPECIFIC(db, driver) \ + if (!db.driverName().startsWith(driver)) { QSKIP(driver " specific test", SkipSingle); return; } + +// ### use QSystem::hostName if it is integrated in qtest/main +static QString qGetHostName() +{ + static QString hostname; + + if ( !hostname.isEmpty() ) + return hostname; + + char hn[257]; + + if ( gethostname( hn, 255 ) == 0 ) { + hn[256] = '\0'; + hostname = QString::fromLatin1( hn ); + hostname.replace( QLatin1Char( '.' ), QLatin1Char( '_' ) ); + hostname.replace( QLatin1Char( '-' ), QLatin1Char( '_' ) ); + } + + return hostname; +} + +// to prevent nameclashes on our database server, each machine +// will use its own set of table names. Call this function to get +// "tablename_hostname" +inline static QString qTableName( const QString& prefix, QSqlDriver* driver = 0 ) +{ + if ( !driver ) + return prefix + "_" + qGetHostName().replace( "-", "_" ); + else + return driver->escapeIdentifier( prefix + "_" + qGetHostName(), QSqlDriver::TableName ); +} + +inline static bool testWhiteSpaceNames( const QString &name ) +{ + return name.startsWith( "QPSQL" ) + || name.startsWith( "QODBC" ) + || name.startsWith( "QSQLITE" ) + || name.startsWith( "QMYSQL" ); +} + +inline static QString toHex( const QString& binary ) +{ + QString str; + static char const hexchars[] = "0123456789ABCDEF"; + + for ( int i = 0; i < binary.size(); i++ ) { + ushort code = binary.at(i).unicode(); + str += (QChar)(hexchars[ (code >> 12) & 0x0F ]); + str += (QChar)(hexchars[ (code >> 8) & 0x0F ]); + str += (QChar)(hexchars[ (code >> 4) & 0x0F ]); + str += (QChar)(hexchars[ code & 0x0F ]); + } + + return str; +} + + +class tst_Databases +{ + +public: + tst_Databases(): counter( 0 ) + { + } + + ~tst_Databases() + { + close(); + } + + // returns a testtable consisting of the names of all database connections if + // driverPrefix is empty, otherwise only those that start with driverPrefix. + int fillTestTable( const QString& driverPrefix = QString() ) const + { + QTest::addColumn<QString>( "dbName" ); + int count = 0; + + for ( int i = 0; i < dbNames.count(); ++i ) { + QSqlDatabase db = QSqlDatabase::database( dbNames.at( i ) ); + + if ( !db.isValid() ) + continue; + + if ( driverPrefix.isEmpty() || db.driverName().startsWith( driverPrefix ) ) { + QTest::newRow( dbNames.at( i ).toLatin1() ) << dbNames.at( i ); + ++count; + } + } + + return count; + } + + void addDb( const QString& driver, const QString& dbName, + const QString& user = QString(), const QString& passwd = QString(), + const QString& host = QString(), int port = -1, const QString params = QString() ) + { + QSqlDatabase db; + + if ( !QSqlDatabase::drivers().contains( driver ) ) { + qWarning() << "Driver" << driver << "is not installed"; + return; + } + + // construct a stupid unique name + QString cName = QString::number( counter++ ) + "_" + driver + "@"; + + cName += host.isEmpty() ? dbName : host; + + if ( port > 0 ) + cName += ":" + QString::number( port ); + + db = QSqlDatabase::addDatabase( driver, cName ); + + if ( !db.isValid() ) { + qWarning( "Could not create database object" ); + return; + } + + db.setDatabaseName( dbName ); + + db.setUserName( user ); + db.setPassword( passwd ); + db.setHostName( host ); + db.setPort( port ); + db.setConnectOptions( params ); + dbNames.append( cName ); + } + + void addDbs() + { +// addDb( "QOCI8", "//horsehead.nokia.troll.no:1521/pony.troll.no", "scott", "tiger" ); // Oracle 9i on horsehead +// addDb( "QOCI8", "//horsehead.nokia.troll.no:1521/ustest.troll.no", "scott", "tiger", "" ); // Oracle 9i on horsehead +// addDb( "QOCI8", "ICE", "scott", "tiger", "" ); // Oracle 8 on iceblink +// addDb( "QOCI", "//silence.nokia.troll.no:1521/testdb", "scott", "tiger" ); // Oracle 10g on silence +// addDb( "QOCI", "//oracle10g-nokia.trolltech.com.au:1521/XE", "scott", "tiger" ); // Oracle 10gexpress on xen + +// This requires a local ODBC data source to be configured( pointing to a MySql database ) +// addDb( "QODBC", "mysqlodbc", "troll", "trond" ); +// addDb( "QODBC", "SqlServer", "troll", "trond" ); +// addDb( "QTDS7", "testdb", "troll", "trondk", "horsehead" ); +// addDb( "QODBC", "silencetestdb", "troll", "trond", "silence" ); +// addDb( "QODBC", "horseheadtestdb", "troll", "trondk", "horsehead" ); + +// addDb( "QMYSQL3", "testdb", "troll", "trond", "horsehead.nokia.troll.no" ); +// addDb( "QMYSQL3", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 3307 ); +// addDb( "QMYSQL3", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 3308, "CLIENT_COMPRESS=1;CLIENT_SSL=1" ); // MySQL 4.1.1 +// addDb( "QMYSQL3", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 3309, "CLIENT_COMPRESS=1;CLIENT_SSL=1" ); // MySQL 5.0.18 Linux +// addDb( "QMYSQL3", "testdb", "troll", "trond", "iceblink.nokia.troll.no" ); // MySQL 5.0.13 Windows +// addDb( "QMYSQL3", "testdb", "testuser", "Ee4Gabf6_", "mysql4-nokia.trolltech.com.au" ); // MySQL 4.1.22-2.el4 linux +// addDb( "QMYSQL3", "testdb", "testuser", "Ee4Gabf6_", "mysql5-nokia.trolltech.com.au" ); // MySQL 5.0.45-7.el5 linux + +// addDb( "QPSQL7", "testdb", "troll", "trond", "horsehead.nokia.troll.no" ); // V7.2 NOT SUPPORTED! +// addDb( "QPSQL7", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 5434 ); // V7.2 NOT SUPPORTED! Multi-byte +// addDb( "QPSQL7", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 5435 ); // V7.3 +// addDb( "QPSQL7", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 5436 ); // V7.4 +// addDb( "QPSQL7", "testdb", "troll", "trond", "horsehead.nokia.troll.no", 5437 ); // V8.0.3 +// addDb( "QPSQL7", "testdb", "troll", "trond", "silence.nokia.troll.no" ); // V8.2.1, UTF-8 +// addDb( "QPSQL7", "testdb", "testuser", "Ee4Gabf6_", "postgres74-nokia.trolltech.com.au" ); // Version 7.4.19-1.el4_6.1 +// addDb( "QPSQL7", "testdb", "testuser", "Ee4Gabf6_", "postgres81-nokia.trolltech.com.au" ); // Version 8.1.11-1.el5_1.1 + +// addDb( "QDB2", "testdb", "troll", "trond", "silence" ); // DB2 v9.1 on silence + +// yes - interbase really wants the physical path on the host machine. +// addDb( "QIBASE", "/opt/interbase/qttest.gdb", "SYSDBA", "masterkey", "horsehead.nokia.troll.no" ); +// addDb( "QIBASE", "silence.troll.no:c:\\ibase\\testdb", "SYSDBA", "masterkey", "" ); // InterBase 7.5 on silence +// addDb( "QIBASE", "silence.troll.no:c:\\ibase\\testdb_ascii", "SYSDBA", "masterkey", "" ); // InterBase 7.5 on silence +// addDb( "QIBASE", "/opt/firebird/databases/testdb.fdb", "testuser", "Ee4Gabf6_", "firebird1-nokia.trolltech.com.au" ); // Firebird 1.5.5 +// addDb( "QIBASE", "/opt/firebird/databases/testdb.fdb", "testuser", "Ee4Gabf6_", "firebird2-nokia.trolltech.com.au" ); // Firebird 2.1.1 + +// Anders' local Firebird and InterBase test databases +// addDb("QIBASE", "localhost:c:\\Firebird\\Firebird_2_0\\TESTDB.FDB", "SYSDBA", "masterkey", ""); // FireBird 2.0 +// addDb("QIBASE", "localhost:c:\\Borland\\InterBase\\TESTDB_UTF8.IB", "SYSDBA", "masterkey", ""); // InterBase 2007 Developer + +// use in-memory database to prevent local files +// addDb("QSQLITE", ":memory:"); + addDb( "QSQLITE", QDir::toNativeSeparators(QDir::tempPath()+"/foo.db") ); +// addDb( "QSQLITE2", QDir::toNativeSeparators(QDir::tempPath()+"/foo2.db") ); +// addDb( "QODBC3", "DRIVER={SQL SERVER};SERVER=iceblink.nokia.troll.no\\ICEBLINK", "troll", "trond", "" ); +// addDb( "QODBC3", "DRIVER={SQL Native Client};SERVER=silence.nokia.troll.no\\SQLEXPRESS", "troll", "trond", "" ); + + } + + void open() + { + addDbs(); + + QStringList::Iterator it = dbNames.begin(); + + while ( it != dbNames.end() ) { + QSqlDatabase db = QSqlDatabase::database(( *it ), false ); + qDebug() << "Opening:" << (*it); + + if ( db.isValid() && !db.isOpen() ) { + if ( !db.open() ) { + qWarning( "tst_Databases: Unable to open %s on %s:\n%s", qPrintable( db.driverName() ), qPrintable( *it ), qPrintable( db.lastError().databaseText() ) ); + // well... opening failed, so we just ignore the server, maybe it is not running + it = dbNames.erase( it ); + } else { + ++it; + } + } + } + } + + void close() + { + for ( QStringList::Iterator it = dbNames.begin(); it != dbNames.end(); ++it ) { + { + QSqlDatabase db = QSqlDatabase::database(( *it ), false ); + + if ( db.isValid() && db.isOpen() ) + db.close(); + } + + QSqlDatabase::removeDatabase(( *it ) ); + } + + dbNames.clear(); + } + + // for debugging only: outputs the connection as string + static QString dbToString( const QSqlDatabase db ) + { + QString res = db.driverName() + "@"; + + if ( db.driverName().startsWith( "QODBC" ) || db.driverName().startsWith( "QOCI" ) ) { + res += db.databaseName(); + } else { + res += db.hostName(); + } + + if ( db.port() > 0 ) { + res += ":" + QString::number( db.port() ); + } + + return res; + } + + // drop a table only if it exists to prevent warnings + static void safeDropTables( QSqlDatabase db, const QStringList& tableNames ) + { + int wasDropped = true; + QSqlQuery q( db ); + QStringList dbtables=db.tables(); + + foreach(QString tableName, tableNames) + { + + if ( dbtables.contains( tableName, Qt::CaseSensitive ) ) + wasDropped = q.exec( "drop table " + db.driver()->escapeIdentifier( tableName, QSqlDriver::TableName ) ); + else if ( dbtables.contains( tableName, Qt::CaseInsensitive ) ) + wasDropped = q.exec( "drop table " + tableName ); + + if ( !wasDropped ) + qWarning() << "unable to drop table" << tableName << ':' << q.lastError().text(); + } + } + + static void safeDropViews( QSqlDatabase db, const QStringList &viewNames ) + { + if ( isMSAccess( db ) ) // Access is sooo stupid. + safeDropTables( db, viewNames ); + + QStringList dbtables=db.tables(QSql::Views); + + foreach(QString viewName, viewNames) + { + if ( dbtables.contains( viewName, Qt::CaseInsensitive ) ) { + QSqlQuery q( "drop view " + viewName, db ); + + if ( !q.isActive() ) + qWarning() << "unable to drop view" << viewName << ':' << q.lastError().text(); + } else if ( db.driverName().startsWith( "QMYSQL" ) + && dbtables.contains( viewName, Qt::CaseInsensitive ) ) { // MySql is a bit stupid too + QSqlQuery q( "drop view " + viewName, db ); + } + } + } + + // returns the type name of the blob datatype for the database db. + // blobSize is only used if the db doesn't have a generic blob type + static QString blobTypeName( QSqlDatabase db, int blobSize = 10000 ) + { + if ( db.driverName().startsWith( "QMYSQL" ) ) + return "longblob"; + + if ( db.driverName().startsWith( "QPSQL" ) ) + return "bytea"; + + if ( db.driverName().startsWith( "QTDS" ) + || isSqlServer( db ) + || isMSAccess( db ) ) + return "image"; + + if ( db.driverName().startsWith( "QDB2" ) ) + return QString( "blob(%1)" ).arg( blobSize ); + + if ( db.driverName().startsWith( "QIBASE" ) ) + return QString( "blob sub_type 0 segment size 4096" ); + + if ( db.driverName().startsWith( "QOCI" ) + || db.driverName().startsWith( "QSQLITE" ) ) + return "blob"; + + qDebug() << "tst_Databases::blobTypeName: Don't know the blob type for" << dbToString( db ); + + return "blob"; + } + + static QString autoFieldName( QSqlDatabase db ) + { + if ( db.driverName().startsWith( "QMYSQL" ) ) + return "AUTO_INCREMENT"; + if ( db.driverName().startsWith( "QTDS" ) ) + return "IDENTITY"; +/* if ( db.driverName().startsWith( "QPSQL" ) ) + return "SERIAL";*/ + if ( db.driverName().startsWith( "QDB2" ) ) + return "GENERATED BY DEFAULT AS IDENTITY"; + + return QString(); + } + + static QByteArray printError( const QSqlError& err ) + { + return QString( "'" + err.driverText() + "' || '" + err.databaseText() + "'" ).toLocal8Bit(); + } + + static QByteArray printError( const QSqlError& err, const QSqlDatabase& db ) + { + return QString( db.driverName() + ": '" + err.driverText() + "' || '" + err.databaseText() + "'" ).toLocal8Bit(); + } + + static bool isSqlServer( QSqlDatabase db ) + { + return db.databaseName().contains( "sql server", Qt::CaseInsensitive ) + || db.databaseName().contains( "sqlserver", Qt::CaseInsensitive ) + || db.databaseName().contains( "sql native client", Qt::CaseInsensitive ); + } + + static bool isMSAccess( QSqlDatabase db ) + { + return db.databaseName().contains( "Access Driver", Qt::CaseInsensitive ); + } + + // -1 on fail, else Oracle version + static int getOraVersion( QSqlDatabase db ) + { + int ver = -1; + QSqlQuery q( "SELECT banner FROM v$version", db ); + q.next(); + + QRegExp vers( "([0-9]+)\\.[0-9\\.]+[0-9]" ); + + if ( vers.indexIn( q.value( 0 ).toString() ) ) { + bool ok; + ver = vers.cap( 1 ).toInt( &ok ); + + if ( !ok ) + ver = -1; + } + + return ver; + } + + static QString getMySqlVersion( const QSqlDatabase &db ) + { + QSqlQuery q(db); + q.exec( "select version()" ); + if(q.next()) + return q.value( 0 ).toString(); + else + return QString(); + } + + QStringList dbNames; + int counter; +}; + +#endif + diff --git a/tests/auto/qsqldatabase/tst_qsqldatabase.cpp b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp new file mode 100644 index 0000000..349db65 --- /dev/null +++ b/tests/auto/qsqldatabase/tst_qsqldatabase.cpp @@ -0,0 +1,2319 @@ +/**************************************************************************** +** +** Copyright (C) 2009 Nokia Corporation and/or its subsidiary(-ies). +** Contact: Qt Software Information (qt-info@nokia.com) +** +** This file is part of the test suite of the Qt Toolkit. +** +** $QT_BEGIN_LICENSE:LGPL$ +** No Commercial Usage +** This file contains pre-release code and may not be distributed. +** You may use this file in accordance with the terms and conditions +** contained in the either Technology Preview License Agreement or the +** Beta Release License Agreement. +** +** GNU Lesser General Public License Usage +** Alternatively, this file may be used under the terms of the GNU Lesser +** General Public License version 2.1 as published by the Free Software +** Foundation and appearing in the file LICENSE.LGPL included in the +** packaging of this file. Please review the following information to +** ensure the GNU Lesser General Public License version 2.1 requirements +** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html. +** +** In addition, as a special exception, Nokia gives you certain +** additional rights. These rights are described in the Nokia Qt LGPL +** Exception version 1.0, included in the file LGPL_EXCEPTION.txt in this +** package. +** +** GNU General Public License Usage +** Alternatively, this file may be used under the terms of the GNU +** General Public License version 3.0 as published by the Free Software +** Foundation and appearing in the file LICENSE.GPL included in the +** packaging of this file. Please review the following information to +** ensure the GNU General Public License version 3.0 requirements will be +** met: http://www.gnu.org/copyleft/gpl.html. +** +** If you are unsure which license is appropriate for your use, please +** contact the sales department at qt-sales@nokia.com. +** $QT_END_LICENSE$ +** +****************************************************************************/ + + +#include <QtTest/QtTest> +#include <qsqldatabase.h> +#include <qsqlquery.h> +#include <qsqldriver.h> +#ifdef QT3_SUPPORT +#include <q3sqlcursor.h> +#include <q3sqlrecordinfo.h> +#include <q3cstring.h> +#endif +#include <qsqlrecord.h> +#include <qsqlfield.h> +#include <qsqlindex.h> +#include <qregexp.h> +#include <qvariant.h> +#include <qdatetime.h> +#include <qdebug.h> + +#define NODATABASE_SKIP "No database drivers are available in this Qt configuration" + +#include "tst_databases.h" + +//TESTED_FILES= + +QT_FORWARD_DECLARE_CLASS(QSqlDatabase) +struct FieldDef; + +class tst_QSqlDatabase : public QObject +{ + Q_OBJECT + +public: + tst_QSqlDatabase(); + virtual ~tst_QSqlDatabase(); + +public slots: + void initTestCase(); + void cleanupTestCase(); + void init(); + void cleanup(); +private slots: + void record_data() { generic_data(); } + //void record(); + void open_data() { generic_data(); } + void open(); + void tables_data() { generic_data(); } + void tables(); + void transaction_data() { generic_data(); } + void transaction(); + void eventNotification_data() { generic_data(); } + void eventNotification(); + void addDatabase(); + + //database specific tests + void recordMySQL_data(); + void recordMySQL(); + void recordPSQL_data(); + void recordPSQL(); + void recordOCI_data(); + void recordOCI(); + void recordTDS_data(); + void recordTDS(); + void recordDB2_data(); + void recordDB2(); + void recordSQLite_data(); + void recordSQLite(); + void recordAccess_data(); + void recordAccess(); + void recordSQLServer_data(); + void recordSQLServer(); + void recordIBase_data(); + void recordIBase(); + + void eventNotificationIBase_data() { generic_data(); } + void eventNotificationIBase(); + void eventNotificationPSQL_data() { generic_data(); } + void eventNotificationPSQL(); + + //database specific 64 bit integer test + void bigIntField_data() { generic_data(); } + void bigIntField(); + + // general tests + void getConnectionName_data() { generic_data(); } + void getConnectionName(); // For task 129992 + + //problem specific tests + void alterTable_data() { generic_data(); } + void alterTable(); + void recordNonSelect_data() { generic_data(); } + void recordNonSelect(); + void caseSensivity_data() { generic_data(); } + void caseSensivity(); + void noEscapedFieldNamesInRecord_data() { generic_data(); } + void noEscapedFieldNamesInRecord(); + void whitespaceInIdentifiers_data() { generic_data(); } + void whitespaceInIdentifiers(); + void formatValueTrimStrings_data() { generic_data(); } + void formatValueTrimStrings(); + void precisionPolicy_data() { generic_data(); } + void precisionPolicy(); + + void db2_valueCacheUpdate_data() { generic_data(); } + void db2_valueCacheUpdate(); + + void psql_schemas_data(); + void psql_schemas(); + void psql_escapedIdentifiers_data(){ psql_schemas_data(); } + void psql_escapedIdentifiers(); + void psql_escapeBytea_data() { generic_data(); } + void psql_escapeBytea(); + + void mysqlOdbc_unsignedIntegers_data() { generic_data(); } + void mysqlOdbc_unsignedIntegers(); + void mysql_multiselect_data() { generic_data(); } + void mysql_multiselect(); // For task 144331 + + void accessOdbc_strings_data() { generic_data(); } + void accessOdbc_strings(); + + void ibase_numericFields_data() { generic_data(); } + void ibase_numericFields(); // For task 125053 + void ibase_fetchBlobs_data() { generic_data(); } + void ibase_fetchBlobs(); // For task 143471 + void ibase_useCustomCharset_data() { generic_data(); } + void ibase_useCustomCharset(); // For task 134608 + void ibase_procWithoutReturnValues_data() { generic_data(); } // For task 165423 + void ibase_procWithoutReturnValues(); + void ibase_procWithReturnValues_data() { generic_data(); } // For task 177530 + void ibase_procWithReturnValues(); + + void odbc_reopenDatabase_data() { generic_data(); } + void odbc_reopenDatabase(); + void odbc_uniqueidentifier_data() { generic_data(); } + void odbc_uniqueidentifier(); // For task 141822 + void odbc_uintfield_data() { generic_data(); } + void odbc_uintfield(); + void odbc_bindBoolean_data() { generic_data(); } + void odbc_bindBoolean(); + + void oci_serverDetach_data() { generic_data(); } + void oci_serverDetach(); // For task 154518 + void oci_xmltypeSupport_data() { generic_data(); } + void oci_xmltypeSupport(); + void oci_fieldLength_data() { generic_data(); } + void oci_fieldLength(); + + void sqlite_bindAndFetchUInt_data() { generic_data(); } + void sqlite_bindAndFetchUInt(); + +private: + void createTestTables(QSqlDatabase db); + void dropTestTables(QSqlDatabase db); + void populateTestTables(QSqlDatabase db); + void generic_data(); + +#ifdef QT3_SUPPORT + void testRecordInfo(const FieldDef fieldDefs[], const Q3SqlRecordInfo& inf); +#endif + void testRecord(const FieldDef fieldDefs[], const QSqlRecord& inf, QSqlDatabase db); + void commonFieldTest(const FieldDef fieldDefs[], QSqlDatabase, const int); + void checkValues(const FieldDef fieldDefs[], QSqlDatabase db); + void checkNullValues(const FieldDef fieldDefs[], QSqlDatabase db); + + tst_Databases dbs; +}; + +// number of records to be inserted per testfunction +static const int ITERATION_COUNT = 2; +static int pkey = 1; + +//helper class for database specific tests +struct FieldDef { + FieldDef(QString tn = QString(), + QVariant::Type t = QVariant::Invalid, + QVariant v = QVariant(), + bool nl = true): + typeName(tn), type(t), val(v), nullable(nl) {} + + QString fieldName() const + { + QString rt = typeName; + rt.replace(QRegExp("\\s"), QString("_")); +#ifdef QT3_SUPPORT + int i = rt.find("("); +#else + int i = rt.indexOf("("); +#endif + if (i == -1) + i = rt.length(); + if (i > 20) + i = 20; + return "t_" + rt.left(i); + } + QString typeName; + QVariant::Type type; + QVariant val; + bool nullable; +}; + +// creates a table out of the FieldDefs and returns the number of fields +// excluding the primary key field +static int createFieldTable(const FieldDef fieldDefs[], QSqlDatabase db) +{ + tst_Databases::safeDropTables(db, QStringList() << qTableName("qtestfields")); + QSqlQuery q(db); + // construct a create table statement consisting of all fieldtypes + QString qs = "create table " + qTableName("qtestfields"); + QString autoName = tst_Databases::autoFieldName(db); + if (tst_Databases::isMSAccess(db)) + qs.append(" (id int not null"); + else + qs.append(QString("(id integer not null %1 primary key").arg(autoName)); + + int i = 0; + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + qs += QString(",\n %1 %2").arg(fieldDefs[ i ].fieldName()).arg(fieldDefs[ i ].typeName); + if ((db.driverName().startsWith("QTDS") || tst_Databases::isSqlServer(db)) && fieldDefs[ i ].nullable) { + qs += " null"; + } + } + + if (tst_Databases::isMSAccess(db)) + qs.append(",\n primary key (id)"); + + qs += ')'; + if (!q.exec(qs)) { + qDebug() << "Creation of Table failed:" << tst_Databases::printError(q.lastError(), db); + qDebug() << "Query: " << qs; + return -1; + } + return i; +} + +tst_QSqlDatabase::tst_QSqlDatabase() +{ +} + +tst_QSqlDatabase::~tst_QSqlDatabase() +{ +} + +void tst_QSqlDatabase::createTestTables(QSqlDatabase db) +{ + if (!db.isValid()) + return; + QSqlQuery q(db); + if (db.driverName().startsWith("QMYSQL")) + // ### stupid workaround until we find a way to hardcode this + // in the MySQL server startup script + q.exec("set table_type=innodb"); + + // please never ever change this table; otherwise fix all tests ;) + if (tst_Databases::isMSAccess(db)) { + QVERIFY_SQL(q, exec("create table " + qTableName("qtest") + + " (id int not null, t_varchar varchar(40) not null, t_char char(40), " + "t_numeric number, primary key (id, t_varchar))")); + } else { + QVERIFY_SQL(q, exec("create table " + qTableName("qtest") + + " (id integer not null, t_varchar varchar(40) not null, " + "t_char char(40), t_numeric numeric(6, 3), primary key (id, t_varchar))")); + } + + if (testWhiteSpaceNames(db.driverName())) { + QString qry = "create table " + + db.driver()->escapeIdentifier(qTableName("qtest") + " test", QSqlDriver::TableName) + + '(' + + db.driver()->escapeIdentifier(QLatin1String("test test"), QSqlDriver::FieldName) + + " int not null primary key)"; + QVERIFY_SQL(q, exec(qry)); + } +} + +void tst_QSqlDatabase::dropTestTables(QSqlDatabase db) +{ + if (!db.isValid()) + return; + // drop the view first, otherwise we'll get dependency problems + tst_Databases::safeDropViews(db, QStringList() << qTableName("qtest_view") << qTableName("qtest_view2")); + + QStringList tableNames; + tableNames << qTableName("qtest") + << qTableName("qtestfields") + << qTableName("qtestalter") + << qTableName("qtest_temp") + << qTableName("qtest_bigint") + << qTableName("qtest_xmltype") + << qTableName("latin1table") + << qTableName("qtest_sqlguid") + << qTableName("batable") + << qTableName("qtest_prec") + << qTableName("uint") + << qTableName("strings") + << qTableName("numericfields") + << qTableName("qtest_ibaseblobs") + << qTableName("qtestBindBool") + << qTableName("qtest_sqlguid") + << qTableName("uint_table") + << qTableName("uint_test"); + + QSqlQuery q(0, db); + if (db.driverName().startsWith("QPSQL")) + q.exec("drop schema " + qTableName("qtestschema") + " cascade"); + + if (testWhiteSpaceNames(db.driverName())) + tableNames << (qTableName("qtest") + " test"); + + tst_Databases::safeDropTables(db, tableNames); +} + +void tst_QSqlDatabase::populateTestTables(QSqlDatabase db) +{ + if (!db.isValid()) + return; + QSqlQuery q(db); + + q.exec("delete from " + qTableName("qtest")); //non-fatal + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (0, 'VarChar0', 'Char0', 1.1)")); + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (1, 'VarChar1', 'Char1', 2.2)")); + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (2, 'VarChar2', 'Char2', 3.3)")); + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " (id, t_varchar, t_char, t_numeric) values (3, 'VarChar3', 'Char3', 4.4)")); +} + +void tst_QSqlDatabase::initTestCase() +{ + dbs.open(); + + for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) { + QSqlDatabase db = QSqlDatabase::database((*it)); + CHECK_DATABASE(db); + dropTestTables(db); //in case of leftovers + createTestTables(db); + populateTestTables(db); + } +} + +void tst_QSqlDatabase::cleanupTestCase() +{ + for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) { + QSqlDatabase db = QSqlDatabase::database((*it)); + CHECK_DATABASE(db); + dropTestTables(db); + } + + dbs.close(); +} + +void tst_QSqlDatabase::init() +{ +} + +void tst_QSqlDatabase::cleanup() +{ +} + +void tst_QSqlDatabase::recordOCI_data() +{ + if (dbs.fillTestTable("QOCI") == 0) + QSKIP("No Oracle database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordPSQL_data() +{ + if (dbs.fillTestTable("QPSQL") == 0) + QSKIP("No PostgreSQL database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordMySQL_data() +{ + if (dbs.fillTestTable("QMYSQL") == 0) + QSKIP("No MySQL database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordTDS_data() +{ + if (dbs.fillTestTable("QTDS") == 0) + QSKIP("No TDS database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordDB2_data() +{ + if (dbs.fillTestTable("QDB2") == 0) + QSKIP("No DB2 database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordSQLite_data() +{ + if (dbs.fillTestTable("QSQLITE") == 0) + QSKIP("No SQLite database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordAccess_data() +{ + if (dbs.fillTestTable("QODBC") == 0) + QSKIP("No ODBC database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordSQLServer_data() +{ + if (dbs.fillTestTable("QODBC") == 0) + QSKIP("No ODBC database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::recordIBase_data() +{ + if (dbs.fillTestTable("QIBASE") == 0) + QSKIP("No Interbase database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::psql_schemas_data() +{ + if (dbs.fillTestTable("QPSQL") == 0) + QSKIP("No Postgres database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::generic_data() +{ + if (dbs.fillTestTable() == 0) + QSKIP("No database drivers are available in this Qt configuration", SkipAll); +} + +void tst_QSqlDatabase::addDatabase() +{ + QTest::ignoreMessage(QtWarningMsg, "QSqlDatabase: BLAH_FOO_NONEXISTENT_DRIVER driver not loaded"); + QTest::ignoreMessage(QtWarningMsg, qPrintable("QSqlDatabase: available drivers: " + QSqlDatabase::drivers().join(QLatin1String(" ")))); + { + QSqlDatabase db = QSqlDatabase::addDatabase("BLAH_FOO_NONEXISTENT_DRIVER", + "INVALID_CONNECTION"); + QVERIFY(!db.isValid()); + } + QVERIFY(QSqlDatabase::contains("INVALID_CONNECTION")); + QSqlDatabase::removeDatabase("INVALID_CONNECTION"); + QVERIFY(!QSqlDatabase::contains("INVALID_CONNECTION")); +} + +void tst_QSqlDatabase::open() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + int i; + for (i = 0; i < 10; ++i) { + db.close(); + QVERIFY(!db.isOpen()); + QVERIFY_SQL(db, open()); + QVERIFY(db.isOpen()); + QVERIFY(!db.isOpenError()); + } + + if (db.driverName().startsWith("QSQLITE") && db.databaseName() == ":memory:") { + // tables in in-memory databases don't survive an open/close + createTestTables(db); + populateTestTables(db); + } +} + +void tst_QSqlDatabase::recordNonSelect() +{ +#ifdef QT3_SUPPORT + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QSqlQuery q(db); + + // nothing should happen on an empty query + QSqlRecord rec = db.record(q); + QVERIFY(rec.isEmpty()); + Q3SqlRecordInfo rInf = db.recordInfo(q); + QVERIFY(rInf.isEmpty()); + + QVERIFY_SQL(q, exec("create table " + qTableName("qtest_temp") + " (id int)")); + + // query without result set should return empty record + rec = db.record(q); + QVERIFY(rec.isEmpty()); + rInf = db.recordInfo(q); + QVERIFY(rInf.isEmpty()); +#endif +} + +void tst_QSqlDatabase::tables() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + bool views = true; + bool tempTables = false; + + QSqlQuery q(db); + if ( db.driverName().startsWith( "QMYSQL" ) && tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt()<5 ) + QSKIP( "Test requires MySQL >= 5.0", SkipSingle ); + + + if (!q.exec("CREATE VIEW " + qTableName("qtest_view") + " as select * from " + qTableName("qtest"))) { + qDebug(QString("DBMS '%1' cannot handle VIEWs: %2").arg( + tst_Databases::dbToString(db)).arg(QString(tst_Databases::printError(q.lastError()))).toLatin1()); + views = false; + } + + if (db.driverName().startsWith("QSQLITE3")) { + QVERIFY_SQL(q, exec("CREATE TEMPORARY TABLE " + qTableName("temp_tab") + " (id int)")); + tempTables = true; + } + + QStringList tables = db.tables(QSql::Tables); + QVERIFY(tables.contains(qTableName("qtest"), Qt::CaseInsensitive)); + QVERIFY(!tables.contains("sql_features", Qt::CaseInsensitive)); //check for postgres 7.4 internal tables + if (views) { + if (db.driverName().startsWith("QMYSQL")) + // MySQL doesn't differentiate between tables and views when calling QSqlDatabase::tables() + // May be fixable by doing a select on informational_schema.tables instead of using the client library api + QEXPECT_FAIL("", "MySQL driver thinks that views are tables", Continue); + QVERIFY(!tables.contains(qTableName("qtest_view"), Qt::CaseInsensitive)); + } + if (tempTables) + QVERIFY(tables.contains(qTableName("temp_tab"), Qt::CaseInsensitive)); + + tables = db.tables(QSql::Views); + if (views) { + if (db.driverName().startsWith("QMYSQL")) + // MySQL doesn't give back anything when calling QSqlDatabase::tables() with QSql::Views + // May be fixable by doing a select on informational_schema.views instead of using the client library api + QEXPECT_FAIL("", "MySQL driver thinks that views are tables", Continue); + QVERIFY(tables.contains(qTableName("qtest_view"), Qt::CaseInsensitive)); + } + if (tempTables) + QVERIFY(!tables.contains(qTableName("temp_tab"), Qt::CaseInsensitive)); + QVERIFY(!tables.contains(qTableName("qtest"), Qt::CaseInsensitive)); + + tables = db.tables(QSql::SystemTables); + QVERIFY(!tables.contains(qTableName("qtest"), Qt::CaseInsensitive)); + QVERIFY(!tables.contains(qTableName("qtest_view"), Qt::CaseInsensitive)); + QVERIFY(!tables.contains(qTableName("temp_tab"), Qt::CaseInsensitive)); + + tables = db.tables(QSql::AllTables); + if (views) + QVERIFY(tables.contains(qTableName("qtest_view"), Qt::CaseInsensitive)); + if (tempTables) + QVERIFY(tables.contains(qTableName("temp_tab"), Qt::CaseInsensitive)); + QVERIFY(tables.contains(qTableName("qtest"), Qt::CaseInsensitive)); + + if (tst_Databases::isMSAccess(db)) + QSqlQuery("drop table " + qTableName("qtest_view"), db); + else + QSqlQuery("drop view " + qTableName("qtest_view"), db); + + if (db.driverName().startsWith("QPSQL")) { + QVERIFY(tables.contains(qTableName("qtest") + " test")); + } +} + +void tst_QSqlDatabase::whitespaceInIdentifiers() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (testWhiteSpaceNames(db.driverName())) { + QString tableName = qTableName("qtest") + " test"; + QVERIFY(db.tables().contains(tableName, Qt::CaseInsensitive)); + + QSqlRecord rec = db.record(tableName); + QCOMPARE(rec.count(), 1); + QCOMPARE(rec.fieldName(0), QString("test test")); + if(db.driverName().startsWith("QOCI")) + QCOMPARE(rec.field(0).type(), QVariant::String); + else + QCOMPARE(rec.field(0).type(), QVariant::Int); + + QSqlIndex idx = db.primaryIndex(tableName); + QCOMPARE(idx.count(), 1); + QCOMPARE(idx.fieldName(0), QString("test test")); + if(db.driverName().startsWith("QOCI")) + QCOMPARE(idx.field(0).type(), QVariant::String); + else + QCOMPARE(idx.field(0).type(), QVariant::Int); + } else { + QSKIP("DBMS does not support whitespaces in identifiers", SkipSingle); + } +} + +void tst_QSqlDatabase::alterTable() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QSqlQuery q(db); + + QVERIFY_SQL(q, exec("create table " + qTableName("qtestalter") + " (F1 char(20), F2 char(20), F3 char(20))")); + QSqlRecord rec = db.record(qTableName("qtestalter")); + QCOMPARE((int)rec.count(), 3); +#ifdef QT3_SUPPORT + Q3SqlRecordInfo rinf = db.recordInfo(qTableName("qtestalter")); + QCOMPARE((int)rinf.count(), 3); +#endif + + + int i; + for (i = 0; i < 3; ++i) { + QCOMPARE(rec.field(i).name().toUpper(), QString("F%1").arg(i + 1)); +#ifdef QT3_SUPPORT + QCOMPARE(rinf[ i ].name().upper(), QString("F%1").arg(i + 1)); +#endif + } + + if (!q.exec("alter table " + qTableName("qtestalter") + " drop column F2")) { + QSKIP("DBMS doesn't support dropping columns in ALTER TABLE statement", SkipSingle); + } + + rec = db.record(qTableName("qtestalter")); +#ifdef QT3_SUPPORT + rinf = db.recordInfo(qTableName("qtestalter")); +#endif + + QCOMPARE((int)rec.count(), 2); +#ifdef QT3_SUPPORT + QCOMPARE((int)rinf.count(), 2); +#endif + + QCOMPARE(rec.field(0).name().toUpper(), QString("F1")); + QCOMPARE(rec.field(1).name().toUpper(), QString("F3")); +#ifdef QT3_SUPPORT + QCOMPARE(rinf[ 0 ].name().upper(), QString("F1")); + QCOMPARE(rinf[ 1 ].name().upper(), QString("F3")); +#endif + + q.exec("select * from " + qTableName("qtestalter")); + +#ifdef QT3_SUPPORT + rec = db.record(q); + rinf = db.recordInfo(q); + + QCOMPARE((int)rec.count(), 2); + QCOMPARE((int)rinf.count(), 2); + + QCOMPARE(rec.field(0).name().upper(), QString("F1")); + QCOMPARE(rec.field(1).name().upper(), QString("F3")); + QCOMPARE(rinf[ 0 ].name().upper(), QString("F1")); + QCOMPARE(rinf[ 1 ].name().upper(), QString("F3")); +#endif +} + +#if 0 +// this is the general test that should work on all databases. +// unfortunately no DBMS supports SQL 92/ 99 so the general +// test is more or less a joke. Please write a test for each +// database plugin (see recordOCI and so on). Use this test +// as a template. +void tst_QSqlDatabase::record() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + static const FieldDef fieldDefs[] = { + FieldDef("char(20)", QVariant::String, QString("blah1"), false), + FieldDef("varchar(20)", QVariant::String, QString("blah2"), false), + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + +// doesn't work with oracle: checkNullValues(fieldDefs, db); + commonFieldTest(fieldDefs, db, fieldCount); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} +#endif + +#ifdef QT3_SUPPORT +void tst_QSqlDatabase::testRecordInfo(const FieldDef fieldDefs[], const Q3SqlRecordInfo& inf) +{ + int i = 0; + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + QCOMPARE(inf[i+1].name().upper(), fieldDefs[ i ].fieldName().upper()); + if (inf[i+1].type() != fieldDefs[ i ].type) { + QFAIL(QString(" Expected: '%1' Received: '%2' for field %3 in testRecordInfo").arg( + QVariant::typeToName(fieldDefs[ i ].type)).arg( + QVariant::typeToName(inf[i+1].type())).arg( + fieldDefs[ i ].fieldName())); + } + } +} +#endif + +void tst_QSqlDatabase::testRecord(const FieldDef fieldDefs[], const QSqlRecord& inf, QSqlDatabase db) +{ + int i = 0; + if (!tst_Databases::autoFieldName(db).isEmpty()) // Currently only MySQL is tested + QVERIFY2(inf.field(i).isAutoValue(), qPrintable(inf.field(i).name() + " should be reporting as an autovalue")); + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + QCOMPARE(inf.field(i+1).name().toUpper(), fieldDefs[ i ].fieldName().toUpper()); + if (inf.field(i+1).type() != fieldDefs[ i ].type) { + QFAIL(qPrintable(QString(" Expected: '%1' Received: '%2' for field %3 in testRecord").arg( + QVariant::typeToName(fieldDefs[ i ].type)).arg( + QVariant::typeToName(inf.field(i+1).type())).arg( + fieldDefs[ i ].fieldName()))); + } + QVERIFY(!inf.field(i+1).isAutoValue()); + +// qDebug(QString(" field: %1 type: %2 variant type: %3").arg(fieldDefs[ i ].fieldName()).arg(QVariant::typeToName(inf.field(i+1)->type())).arg(QVariant::typeToName(inf.field(i+1)->value().type()))); + } +} + +// non-dbms specific tests +void tst_QSqlDatabase::commonFieldTest(const FieldDef fieldDefs[], QSqlDatabase db, const int fieldCount) +{ + CHECK_DATABASE(db); + + // check whether recordInfo returns the right types +#ifdef QT3_SUPPORT + Q3SqlRecordInfo inf = db.recordInfo(qTableName("qtestfields")); + QCOMPARE((int)inf.count(), fieldCount+1); + testRecordInfo(fieldDefs, inf); +#endif + + QSqlRecord rec = db.record(qTableName("qtestfields")); + QCOMPARE((int)rec.count(), fieldCount+1); + testRecord(fieldDefs, rec, db); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("select * from " + qTableName("qtestfields"))); + +#ifdef QT3_SUPPORT + inf = db.recordInfo(q); + QCOMPARE((int)inf.count(), fieldCount+1); + testRecordInfo(fieldDefs, inf); + + rec = db.record(q); + QCOMPARE((int)rec.count(), fieldCount+1); + testRecord(fieldDefs, rec, db); +#endif +} + +// inserts testdata into the testtable, fetches and compares them +void tst_QSqlDatabase::checkValues(const FieldDef fieldDefs[], QSqlDatabase db) +{ + Q_UNUSED(fieldDefs); +#ifdef QT3_SUPPORT + CHECK_DATABASE(db); + + Q3SqlCursor cur(qTableName("qtestfields"), true, db); + QVERIFY_SQL(cur, select()); + QSqlRecord* rec = cur.primeInsert(); + Q_ASSERT(rec); + rec->setValue("id", pkey++); + int i = 0; + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + rec->setValue(fieldDefs[ i ].fieldName(), fieldDefs[ i ].val); +// qDebug(QString("inserting %1 into %2").arg(fieldDefs[ i ].val.toString()).arg(fieldDefs[ i ].fieldName())); + } + if (!cur.insert()) { + QFAIL(QString("Couldn't insert record: %1 %2").arg(cur.lastError().databaseText()).arg(cur.lastError().driverText())); + } + cur.setForwardOnly(true); + QVERIFY_SQL(cur, select("id = " + QString::number(pkey - 1))); + QVERIFY_SQL(cur, next()); + + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + bool ok = false; + QVariant val1 = cur.value(fieldDefs[ i ].fieldName()); + QVariant val2 = fieldDefs[ i ].val; + if (val1.type() == QVariant::String) + //TDS Workaround + val1 = val1.toString().stripWhiteSpace(); + if (fieldDefs[ i ].fieldName() == "t_real") { + // strip precision + val1 = (float)val1.toDouble(); + val2 = (float)val2.toDouble(); + } + if (val1.canCast(QVariant::Double) && val2.type() == QVariant::Double) { + // we don't care about precision here, we just want to know whether + // we can insert/fetch the right values + ok = (val1.toDouble() - val2.toDouble() < 0.00001); + } else if (val1.type() == val2.type()) { + ok = (val1 == val2); + } else { + ok = (val1.toString() == val2.toString()); + } + if (!ok) { + if (val2.type() == QVariant::DateTime || val2.type() == QVariant::Time) + qDebug("Expected Time: " + val2.toTime().toString("hh:mm:ss.zzz")); + if (val1.type() == QVariant::DateTime || val1.type() == QVariant::Time) + qDebug("Received Time: " + val1.toTime().toString("hh:mm:ss.zzz")); + QFAIL(QString(" Expected: '%1' Received: '%2' for field %3 (etype %4 rtype %5) in checkValues").arg( + val2.toString()).arg( + val1.toString()).arg( + fieldDefs[ i ].fieldName()).arg( + val2.typeName()).arg( + val1.typeName()) + ); + } + } +#endif +} + +// inserts a NULL value for each nullable field in testdata, fetches and checks whether +// we get back NULL +void tst_QSqlDatabase::checkNullValues(const FieldDef fieldDefs[], QSqlDatabase db) +{ + Q_UNUSED(fieldDefs); +#ifdef QT3_SUPPORT + CHECK_DATABASE(db); + + Q3SqlCursor cur(qTableName("qtestfields"), true, db); + QVERIFY_SQL(cur, select()); + QSqlRecord* rec = cur.primeInsert(); + Q_ASSERT(rec); + rec->setValue("id", pkey++); + int i = 0; + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + if (fieldDefs[ i ].fieldName(), fieldDefs[ i ].nullable) + rec->setNull(fieldDefs[ i ].fieldName()); + else + rec->setValue(fieldDefs[ i ].fieldName(), fieldDefs[ i ].val); + } + if (!cur.insert()) { + QFAIL(QString("Couldn't insert record: %1 %2").arg(cur.lastError().databaseText()).arg(cur.lastError().driverText())); + } + cur.setForwardOnly(true); + QVERIFY_SQL(cur, select("id = " + QString::number(pkey - 1))); + QVERIFY_SQL(cur, next()); + + for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) { + if (fieldDefs[ i ].nullable == false) + continue; + // multiple inheritance sucks so much + QVERIFY2(((QSqlQuery)cur).isNull(i + 1), "Check whether '" + fieldDefs[ i ].fieldName() + "' is null in QSqlQuery"); + QVERIFY2(((QSqlRecord)cur).isNull(fieldDefs[ i ].fieldName()), "Check whether '" + fieldDefs[ i ].fieldName() + "' is null in QSqlRecord"); + if (!cur.value(fieldDefs[ i ].fieldName()).isNull()) + qDebug(QString("QVariant is not null for NULL-Value in Field '%1'").arg(fieldDefs[ i ].fieldName())); + } +#endif +} + +void tst_QSqlDatabase::recordTDS() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QTDS"); + + static const FieldDef fieldDefs[] = { + FieldDef("tinyint", QVariant::Int, 255), + FieldDef("smallint", QVariant::Int, 32767), + FieldDef("int", QVariant::Int, 2147483647), + FieldDef("numeric(10,9)", QVariant::Double, 1.23456789), + FieldDef("decimal(10,9)", QVariant::Double, 1.23456789), + FieldDef("float(4)", QVariant::Double, 1.23456789), + FieldDef("double precision", QVariant::Double, 1.23456789), + FieldDef("real", QVariant::Double, 1.23456789), + FieldDef("smallmoney", QVariant::Double, 100.42), + FieldDef("money", QVariant::Double, 200.42), + // accuracy is that of a minute + FieldDef("smalldatetime", QVariant::DateTime, QDateTime(QDate::currentDate(), QTime(1, 2, 0, 0))), + // accuracy is that of a second + FieldDef("datetime", QVariant::DateTime, QDateTime(QDate::currentDate(), QTime(1, 2, 3, 0))), + FieldDef("char(20)", QVariant::String, "blah1"), + FieldDef("varchar(20)", QVariant::String, "blah2"), + FieldDef("nchar(20)", QVariant::String, "blah3"), + FieldDef("nvarchar(20)", QVariant::String, "blah4"), + FieldDef("text", QVariant::String, "blah5"), +#ifdef QT3_SUPPORT + FieldDef("binary(20)", QVariant::ByteArray, Q3CString("blah6")), + FieldDef("varbinary(20)", QVariant::ByteArray, Q3CString("blah7")), + FieldDef("image", QVariant::ByteArray, Q3CString("blah8")), +#endif + FieldDef("bit", QVariant::Int, 1, false), + + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordOCI() +{ + bool hasTimeStamp = false; + + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QOCI"); + + // runtime check for Oracle version since V8 doesn't support TIMESTAMPs + if (tst_Databases::getOraVersion(db) >= 9) { + qDebug("Detected Oracle >= 9, TIMESTAMP test enabled"); + hasTimeStamp = true; + } else { + qDebug("Detected Oracle < 9, TIMESTAMP test disabled"); + } + + FieldDef tsdef; + FieldDef tstzdef; + FieldDef tsltzdef; + FieldDef intytm; + FieldDef intdts; + + static const QDateTime dt(QDate::currentDate(), QTime(1, 2, 3, 0)); + + if (hasTimeStamp) { + tsdef = FieldDef("timestamp", QVariant::DateTime, dt); + tstzdef = FieldDef("timestamp with time zone", QVariant::DateTime, dt); + tsltzdef = FieldDef("timestamp with local time zone", QVariant::DateTime, dt); + intytm = FieldDef("interval year to month", QVariant::String, QString("+01-01")); + intdts = FieldDef("interval day to second", QVariant::String, QString("+01 00:00:01.000000")); + } + + const FieldDef fieldDefs[] = { + FieldDef("char(20)", QVariant::String, QString("blah1")), + FieldDef("varchar(20)", QVariant::String, QString("blah2")), + FieldDef("nchar(20)", QVariant::String, QString("blah3")), + FieldDef("nvarchar2(20)", QVariant::String, QString("blah4")), + FieldDef("number(10,5)", QVariant::String, 1.1234567), + FieldDef("date", QVariant::DateTime, dt), +#ifdef QT3_SUPPORT +//X? FieldDef("long raw", QVariant::ByteArray, QByteArray(Q3CString("blah5"))), + FieldDef("raw(2000)", QVariant::ByteArray, QByteArray(Q3CString("blah6")), false), + FieldDef("blob", QVariant::ByteArray, QByteArray(Q3CString("blah7"))), +#endif +//FIXME FieldDef("clob", QVariant::CString, Q3CString("blah8")), +//FIXME FieldDef("nclob", QVariant::CString, Q3CString("blah9")), +//X FieldDef("bfile", QVariant::ByteArray, QByteArray(Q3CString("blah10"))), + + intytm, + intdts, + tsdef, + tstzdef, + tsltzdef, + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } + + // some additional tests + QSqlRecord rec = db.record(qTableName("qtestfields")); + QCOMPARE(rec.field("T_NUMBER").length(), 10); + QCOMPARE(rec.field("T_NUMBER").precision(), 5); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("SELECT * FROM " + qTableName("qtestfields"))); + rec = q.record(); + QCOMPARE(rec.field("T_NUMBER").length(), 10); + QCOMPARE(rec.field("T_NUMBER").precision(), 5); +} + +void tst_QSqlDatabase::recordPSQL() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QPSQL"); + + FieldDef byteadef; + if (db.driver()->hasFeature(QSqlDriver::BLOB)) +#ifdef QT3_SUPPORT + byteadef = FieldDef("bytea", QVariant::ByteArray, QByteArray(Q3CString("bl\\ah"))); +#else + byteadef = FieldDef("bytea", QVariant::ByteArray, QByteArray("bl\\ah")); +#endif + static FieldDef fieldDefs[] = { + FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)), + FieldDef("bigserial", QVariant::LongLong, 100, false), + FieldDef("bit", QVariant::String, "1"), // a bit in postgres is a bit-string +#ifdef QT3_SUPPORT + FieldDef("boolean", QVariant::Bool, QVariant(bool(true), 0)), +#endif + FieldDef("box", QVariant::String, "(5,6),(1,2)"), + FieldDef("char(20)", QVariant::String, "blah5678901234567890"), + FieldDef("varchar(20)", QVariant::String, "blah5678901234567890"), + FieldDef("cidr", QVariant::String, "12.123.0.0/24"), + FieldDef("circle", QVariant::String, "<(1,2),3>"), + FieldDef("date", QVariant::Date, QDate::currentDate()), + FieldDef("float8", QVariant::Double, 1.12345678912), + FieldDef("inet", QVariant::String, "12.123.12.23"), + FieldDef("integer", QVariant::Int, 2147483647), + FieldDef("interval", QVariant::String, "1 day 12:59:10"), +// LOL... you can create a "line" datatype in PostgreSQL <= 7.2.x but +// as soon as you want to insert data you get a "not implemented yet" error +// FieldDef("line", QVariant::Polygon, QPolygon(QRect(1, 2, 3, 4))), + FieldDef("lseg", QVariant::String, "[(1,1),(2,2)]"), + FieldDef("macaddr", QVariant::String, "08:00:2b:01:02:03"), + FieldDef("money", QVariant::String, "$12.23"), + FieldDef("numeric", QVariant::Double, 1.2345678912), + FieldDef("path", QVariant::String, "((1,2),(3,2),(3,5),(1,5))"), + FieldDef("point", QVariant::String, "(1,2)"), + FieldDef("polygon", QVariant::String, "((1,2),(3,2),(3,5),(1,5))"), + FieldDef("real", QVariant::Double, 1.1234), + FieldDef("smallint", QVariant::Int, 32767), + FieldDef("serial", QVariant::Int, 100, false), + FieldDef("text", QVariant::String, "blah"), + FieldDef("time(6)", QVariant::Time, QTime(1, 2, 3)), + FieldDef("timetz", QVariant::Time, QTime(1, 2, 3)), + FieldDef("timestamp(6)", QVariant::DateTime, QDateTime::currentDateTime()), + FieldDef("timestamptz", QVariant::DateTime, QDateTime::currentDateTime()), + byteadef, + + FieldDef() + }; + + QSqlQuery q(db); + q.exec("drop sequence " + qTableName("qtestfields") + "_t_bigserial_seq"); + q.exec("drop sequence " + qTableName("qtestfields") + "_t_serial_seq"); + // older psql cut off the table name + q.exec("drop sequence " + qTableName("qtestfields").left(15) + "_t_bigserial_seq"); + q.exec("drop sequence " + qTableName("qtestfields").left(18) + "_t_serial_seq"); + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + // increase serial values + for (int i2 = 0; !fieldDefs[ i2 ].typeName.isNull(); ++i2) { + if (fieldDefs[ i2 ].typeName == "serial" || + fieldDefs[ i2 ].typeName == "bigserial") { + + FieldDef def = fieldDefs[ i2 ]; +#ifdef QT3_SUPPORT + def.val = def.val.asInt() + 1; +#else + def.val = def.val.toInt() + 1; +#endif + fieldDefs[ i2 ] = def; + } + } + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordMySQL() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QMYSQL"); + + FieldDef bin10, varbin10; + int major = tst_Databases::getMySqlVersion( db ).section( QChar('.'), 0, 0 ).toInt(); + int minor = tst_Databases::getMySqlVersion( db ).section( QChar('.'), 1, 1 ).toInt(); + int revision = tst_Databases::getMySqlVersion( db ).section( QChar('.'), 2, 2 ).toInt(); + + /* The below is broken in mysql below 5.0.15 + see http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html + specifically: Before MySQL 5.0.15, the pad value is space. Values are right-padded + with space on insert, and trailing spaces are removed on select. + */ + if ( major >5 || ( major == 5 && minor > 0) || ( major == 5 && minor == 0 && revision >= 15) ) { + bin10 = FieldDef("binary(10)", QVariant::ByteArray, QByteArray(Q3CString("123abc "))); + varbin10 = FieldDef("varbinary(10)", QVariant::ByteArray, QByteArray(Q3CString("123abcv "))); + } + + static QDateTime dt(QDate::currentDate(), QTime(1, 2, 3, 0)); + static const FieldDef fieldDefs[] = { + FieldDef("tinyint", QVariant::Int, 127), + FieldDef("tinyint unsigned", QVariant::UInt, 255), + FieldDef("smallint", QVariant::Int, 32767), + FieldDef("smallint unsigned", QVariant::UInt, 65535), + FieldDef("mediumint", QVariant::Int, 8388607), + FieldDef("mediumint unsigned", QVariant::UInt, 16777215), + FieldDef("integer", QVariant::Int, 2147483647), + FieldDef("integer unsigned", QVariant::UInt, 4294967295u), + FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)), + FieldDef("bigint unsigned", QVariant::ULongLong, Q_UINT64_C(18446744073709551615)), + FieldDef("float", QVariant::Double, 1.12345), + FieldDef("double", QVariant::Double, 1.123456789), + FieldDef("decimal(10, 9)", QVariant::String,1.123456789), + FieldDef("numeric(5, 2)", QVariant::String, 123.67), + FieldDef("date", QVariant::Date, QDate::currentDate()), + FieldDef("datetime", QVariant::DateTime, dt), + FieldDef("timestamp", QVariant::DateTime, dt, false), + FieldDef("time", QVariant::Time, dt.time()), + FieldDef("year", QVariant::Int, 2003), + FieldDef("char(20)", QVariant::String, "Blah"), + FieldDef("varchar(20)", QVariant::String, "BlahBlah"), +#ifdef QT3_SUPPORT + FieldDef("tinyblob", QVariant::ByteArray, QByteArray(Q3CString("blah1"))), + FieldDef("blob", QVariant::ByteArray, QByteArray(Q3CString("blah2"))), + FieldDef("mediumblob", QVariant::ByteArray,QByteArray(Q3CString("blah3"))), + FieldDef("longblob", QVariant::ByteArray, QByteArray(Q3CString("blah4"))), +#endif + FieldDef("tinytext", QVariant::String, QString("blah5")), + FieldDef("text", QVariant::String, QString("blah6")), + FieldDef("mediumtext", QVariant::String, QString("blah7")), + FieldDef("longtext", QVariant::String, QString("blah8")), +#ifdef QT3_SUPPORT + bin10, + varbin10, +#endif + // SET OF? + + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)")); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toDateTime().date(), QDate::currentDate().addDays(-2)); +} + +void tst_QSqlDatabase::recordDB2() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QDB2"); + + static const FieldDef fieldDefs[] = { + FieldDef("char(20)", QVariant::String, QString("Blah1")), + FieldDef("varchar(20)", QVariant::String, QString("Blah2")), + FieldDef("long varchar", QVariant::String, QString("Blah3")), + // using BOOLEAN results in "SQL0486N The BOOLEAN data type is currently only supported internally." +//X FieldDef("boolean" , QVariant::Bool, QVariant(true, 1)), + FieldDef("smallint", QVariant::Int, 32767), + FieldDef("integer", QVariant::Int, 2147483647), + FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)), + FieldDef("real", QVariant::Double, 1.12345), + FieldDef("double", QVariant::Double, 1.23456789), + FieldDef("float", QVariant::Double, 1.23456789), + FieldDef("decimal(10,9)", QVariant::Double, 1.234567891), + FieldDef("numeric(10,9)", QVariant::Double, 1.234567891), + FieldDef("date", QVariant::Date, QDate::currentDate()), + FieldDef("time", QVariant::Time, QTime(1, 2, 3)), + FieldDef("timestamp", QVariant::DateTime, QDateTime::currentDateTime()), +// FieldDef("graphic(20)", QVariant::String, QString("Blah4")), +// FieldDef("vargraphic(20)", QVariant::String, QString("Blah5")), +// FieldDef("long vargraphic", QVariant::String, QString("Blah6")), +#ifdef QT3_SUPPORT +// FieldDef("clob(20)", QVariant::CString, QString("Blah7")), +// FieldDef("dbclob(20)", QVariant::CString, QString("Blah8")), +// FieldDef("blob(20)", QVariant::ByteArray, QByteArray(Q3CString("Blah9"))), +#endif + //X FieldDef("datalink", QVariant::String, QString("DLVALUE('Blah10')")), + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordIBase() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + + static const FieldDef fieldDefs[] = { + FieldDef("char(20)", QVariant::String, QString("Blah1"), false), + FieldDef("varchar(20)", QVariant::String, QString("Blah2")), + FieldDef("smallint", QVariant::Int, 32767), + FieldDef("float", QVariant::Double, 1.2345), + FieldDef("double precision", QVariant::Double, 1.2345678), + FieldDef("timestamp", QVariant::DateTime, QDateTime::currentDateTime()), + FieldDef("time", QVariant::Time, QTime::currentTime()), + FieldDef("decimal(18)", QVariant::LongLong, Q_INT64_C(9223372036854775807)), + FieldDef("numeric(5,2)", QVariant::Double, 123.45), + + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordSQLite() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QSQLITE"); + + static const FieldDef fieldDefs[] = { + // The affinity of these fields are TEXT so SQLite should give us strings, not ints or doubles. + FieldDef("char(20)", QVariant::String, QString("123")), + FieldDef("varchar(20)", QVariant::String, QString("123.4")), + FieldDef("clob", QVariant::String, QString("123.45")), + FieldDef("text", QVariant::String, QString("123.456")), + + FieldDef("integer", QVariant::Int, QVariant(13)), + FieldDef("int", QVariant::Int, QVariant(12)), + + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordSQLServer() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!tst_Databases::isSqlServer(db)) { + QSKIP("SQL server specific test", SkipSingle); + return; + } + + // ### TODO: Add the rest of the fields + static const FieldDef fieldDefs[] = { + FieldDef("varchar(20)", QVariant::String, QString("Blah1")), + FieldDef("bigint", QVariant::LongLong, 12345), + FieldDef("int", QVariant::Int, 123456), + FieldDef("tinyint", QVariant::Int, 255), +#ifdef QT3_SUPPORT + FieldDef("image", QVariant::ByteArray, Q3CString("Blah1")), +#endif + FieldDef("float", QVariant::Double, 1.12345), + FieldDef("numeric(5,2)", QVariant::Double, 123.45), + FieldDef("uniqueidentifier", QVariant::String, + QString("AA7DF450-F119-11CD-8465-00AA00425D90")), + + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::recordAccess() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!tst_Databases::isMSAccess(db)) { + QSKIP("MS Access specific test", SkipSingle); + return; + } + + QString memo; + for (int i = 0; i < 32; i++) + memo.append("ABCDEFGH12345678abcdefgh12345678"); + + // ### TODO: Add the rest of the fields + static const FieldDef fieldDefs[] = { + FieldDef("varchar(20)", QVariant::String, QString("Blah1")), + FieldDef("single", QVariant::Double, 1.12345), + FieldDef("double", QVariant::Double, 1.123456), + FieldDef("byte", QVariant::Int, 255), +#ifdef QT3_SUPPORT + FieldDef("binary", QVariant::ByteArray, Q3CString("Blah2")), +#endif + FieldDef("long", QVariant::Int, 2147483647), + FieldDef("memo", QVariant::String, memo), + FieldDef() + }; + + const int fieldCount = createFieldTable(fieldDefs, db); + QVERIFY(fieldCount > 0); + + commonFieldTest(fieldDefs, db, fieldCount); + checkNullValues(fieldDefs, db); + for (int i = 0; i < ITERATION_COUNT; ++i) { + checkValues(fieldDefs, db); + } +} + +void tst_QSqlDatabase::transaction() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!db.driver()->hasFeature(QSqlDriver::Transactions)) { + QSKIP("DBMS not transaction capable", SkipSingle); + } + + QVERIFY(db.transaction()); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " values (40, 'VarChar40', 'Char40', 40.40)")); + QVERIFY_SQL(q, exec("select * from " + qTableName("qtest") + " where id = 40")); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toInt(), 40); + q.clear(); + + QVERIFY(db.commit()); + + QVERIFY(db.transaction()); + QVERIFY_SQL(q, exec("select * from " + qTableName("qtest") + " where id = 40")); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toInt(), 40); + q.clear(); + QVERIFY(db.commit()); + + QVERIFY(db.transaction()); + QVERIFY_SQL(q, exec("insert into " + qTableName("qtest") + " values (41, 'VarChar41', 'Char41', 41.41)")); + QVERIFY_SQL(q, exec("select * from " + qTableName("qtest") + " where id = 41")); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toInt(), 41); + q.clear(); // for SQLite which does not allow any references on rows that shall be rolled back + if (!db.rollback()) { + if (db.driverName().startsWith("QMYSQL")) { + qDebug("MySQL: " + tst_Databases::printError(db.lastError())); + QSKIP("MySQL transaction failed ", SkipSingle); //non-fatal + } else { + QFAIL("Could not rollback transaction: " + tst_Databases::printError(db.lastError())); + } + } + + QVERIFY_SQL(q, exec("select * from " + qTableName("qtest") + " where id = 41")); + QVERIFY(!q.next()); + + populateTestTables(db); +} + +void tst_QSqlDatabase::bigIntField() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QSqlQuery q(db); + q.setForwardOnly(true); + + QString drvName = db.driverName(); + if (drvName.startsWith("QMYSQL")) { + QVERIFY_SQL(q, exec("create table " + qTableName("qtest_bigint") + " (id int, t_s64bit bigint, t_u64bit bigint unsigned)")); + } else if (drvName.startsWith("QPSQL") + || drvName.startsWith("QDB2") + || tst_Databases::isSqlServer(db)) { + QVERIFY_SQL(q, exec("create table " + qTableName("qtest_bigint") + "(id int, t_s64bit bigint, t_u64bit bigint)")); + } else if (drvName.startsWith("QOCI")) { + QVERIFY_SQL(q, exec("create table " + qTableName("qtest_bigint") + " (id int, t_s64bit int, t_u64bit int)")); + } else { + QSKIP("no 64 bit integer support", SkipAll); + } + QVERIFY(q.prepare("insert into " + qTableName("qtest_bigint") + " values (?, ?, ?)")); + qlonglong ll = Q_INT64_C(9223372036854775807); + qulonglong ull = Q_UINT64_C(18446744073709551615); + + if (drvName.startsWith("QMYSQL") || drvName.startsWith("QOCI")) { + q.bindValue(0, 0); + q.bindValue(1, ll); + q.bindValue(2, ull); + QVERIFY_SQL(q, exec()); + q.bindValue(0, 1); + q.bindValue(1, -ll); + q.bindValue(2, ull); + QVERIFY_SQL(q, exec()); + } else { + // usinged bigint fields not supported - a cast is necessary + q.bindValue(0, 0); + q.bindValue(1, ll); + q.bindValue(2, (qlonglong) ull); + QVERIFY_SQL(q, exec()); + q.bindValue(0, 1); + q.bindValue(1, -ll); + q.bindValue(2, (qlonglong) ull); + QVERIFY_SQL(q, exec()); + } + QVERIFY(q.exec("select * from " + qTableName("qtest_bigint") + " order by id")); + QVERIFY(q.next()); + QCOMPARE(q.value(1).toLongLong(), ll); + QCOMPARE(q.value(2).toULongLong(), ull); + QVERIFY(q.next()); + QCOMPARE(q.value(1).toLongLong(), -ll); + QCOMPARE(q.value(2).toULongLong(), ull); +} + +void tst_QSqlDatabase::caseSensivity() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + bool cs = false; + if (db.driverName().startsWith("QMYSQL") + || db.driverName().startsWith("QSQLITE") + || db.driverName().startsWith("QTDS")) + cs = true; + + QSqlRecord rec = db.record(qTableName("qtest")); + QVERIFY((int)rec.count() > 0); + if (!cs) { + rec = db.record(qTableName("QTEST").toUpper()); + QVERIFY((int)rec.count() > 0); + rec = db.record(qTableName("qTesT")); + QVERIFY((int)rec.count() > 0); + } + +#ifdef QT3_SUPPORT + Q3SqlRecordInfo rInf = db.recordInfo(qTableName("qtest")); + QVERIFY((int)rInf.count() > 0); + if (!cs) { + rInf = db.recordInfo(qTableName("QTEST").upper()); + QVERIFY((int)rInf.count() > 0); + rInf = db.recordInfo(qTableName("qTesT")); + QVERIFY((int)rInf.count() > 0); + } +#endif + + rec = db.primaryIndex(qTableName("qtest")); + QVERIFY((int)rec.count() > 0); + if (!cs) { + rec = db.primaryIndex(qTableName("QTEST").toUpper()); + QVERIFY((int)rec.count() > 0); + rec = db.primaryIndex(qTableName("qTesT")); + QVERIFY((int)rec.count() > 0); + } +} + +void tst_QSqlDatabase::noEscapedFieldNamesInRecord() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QString fieldname("t_varchar"); + if (db.driverName().startsWith("QOCI") || db.driverName().startsWith("QIBASE") || db.driverName().startsWith("QDB2")) + fieldname = fieldname.toUpper(); + + QSqlQuery q(db); + QString query = "SELECT " + db.driver()->escapeIdentifier(fieldname, QSqlDriver::FieldName) + " FROM " + qTableName("qtest"); + QVERIFY_SQL(q, exec(query)); + QCOMPARE(q.record().fieldName(0), fieldname); +} + +void tst_QSqlDatabase::psql_schemas() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!db.tables(QSql::SystemTables).contains("pg_namespace")) + QSKIP("server does not support schemas", SkipSingle); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("CREATE SCHEMA " + qTableName("qtestschema"))); + + QString table = qTableName("qtestschema") + '.' + qTableName("qtesttable"); + QVERIFY_SQL(q, exec("CREATE TABLE " + table + " (id int primary key, name varchar(20))")); + + QVERIFY(db.tables().contains(table)); + + QSqlRecord rec = db.record(table); + QCOMPARE(rec.count(), 2); + QCOMPARE(rec.fieldName(0), QString("id")); + QCOMPARE(rec.fieldName(1), QString("name")); + +#ifdef QT3_SUPPORT + rec = db.record(QSqlQuery("select * from " + table, db)); + QCOMPARE(rec.count(), 2); + QCOMPARE(rec.fieldName(0), QString("id")); + QCOMPARE(rec.fieldName(1), QString("name")); +#endif + QSqlIndex idx = db.primaryIndex(table); + QCOMPARE(idx.count(), 1); + QCOMPARE(idx.fieldName(0), QString("id")); +} + +void tst_QSqlDatabase::psql_escapedIdentifiers() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + QSqlDriver* drv = db.driver(); + CHECK_DATABASE(db); + + if (!db.tables(QSql::SystemTables).contains("pg_namespace")) + QSKIP("server does not support schemas", SkipSingle); + + QSqlQuery q(db); + + QString schemaName = qTableName("qtestScHeMa"); + QString tableName = qTableName("qtestTaBlE"); + QString field1Name = QString("fIeLdNaMe"); + QString field2Name = QString("ZuLu"); + + QString createSchema = QString("CREATE SCHEMA \"%1\"").arg(schemaName); + QVERIFY_SQL(q, exec(createSchema)); + QString createTable = QString("CREATE TABLE \"%1\".\"%2\" (\"%3\" int PRIMARY KEY, \"%4\" varchar(20))").arg(schemaName).arg(tableName).arg(field1Name).arg(field2Name); + QVERIFY_SQL(q, exec(createTable)); + + QVERIFY(db.tables().contains(schemaName + '.' + tableName, Qt::CaseSensitive)); + + QSqlField fld1(field1Name, QVariant::Int); + QSqlField fld2(field2Name, QVariant::String); + QSqlRecord rec; + rec.append(fld1); + rec.append(fld2); + + QVERIFY_SQL(q, exec(drv->sqlStatement(QSqlDriver::SelectStatement, schemaName + '.' + tableName, rec, false))); + + rec = q.record(); + QCOMPARE(rec.count(), 2); + QCOMPARE(rec.fieldName(0), field1Name); + QCOMPARE(rec.fieldName(1), field2Name); + QCOMPARE(rec.field(0).type(), QVariant::Int); + + q.exec(QString("DROP SCHEMA \"%1\" CASCADE").arg(schemaName)); +} + + +void tst_QSqlDatabase::psql_escapeBytea() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QPSQL"); + + const char dta[4] = {'\x71', '\x14', '\x32', '\x81'}; + QByteArray ba(dta, 4); + + QSqlQuery q(db); + QString tableName = qTableName("batable"); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (ba bytea)").arg(tableName))); + + QSqlQuery iq(db); + QVERIFY_SQL(iq, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName))); + iq.bindValue(0, QVariant(ba)); + QVERIFY_SQL(iq, exec()); + + QVERIFY_SQL(q, exec(QString("SELECT ba FROM %1").arg(tableName))); + QVERIFY_SQL(q, next()); + + QByteArray res = q.value(0).toByteArray(); + int i = 0; + for (; i < ba.size(); ++i){ + if (ba[i] != res[i]) + break; + } + + QCOMPARE(i, 4); +} + +// This test should be rewritten to work with Oracle as well - or the Oracle driver +// should be fixed to make this test pass (handle overflows) +void tst_QSqlDatabase::precisionPolicy() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); +// DBMS_SPECIFIC(db, "QPSQL"); + + QSqlQuery q(db); + QString tableName = qTableName("qtest_prec"); + if(!db.driver()->hasFeature(QSqlDriver::LowPrecisionNumbers)) + QSKIP("Driver or database doesn't support setting precision policy", SkipSingle); + + // Create a test table with some data + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id smallint, num numeric(20,0))").arg(tableName))); + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?)").arg(tableName))); + q.bindValue(0, 1); + q.bindValue(1, 123); + QVERIFY_SQL(q, exec()); + q.bindValue(0, 2); + q.bindValue(1, QString("18500000000000000000")); + QVERIFY_SQL(q, exec()); + + // These are expected to pass + QString query = QString("SELECT num FROM %1 WHERE id = 1").arg(tableName); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::String); + + q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::LongLong); + QCOMPARE(q.value(0).toLongLong(), (qlonglong)123); + + q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt32); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::Int); + QCOMPARE(q.value(0).toInt(), 123); + + q.setNumericalPrecisionPolicy(QSql::LowPrecisionDouble); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::Double); + QCOMPARE(q.value(0).toDouble(), (double)123); + + query = QString("SELECT num FROM %1 WHERE id = 2").arg(tableName); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::Double); + QCOMPARE(q.value(0).toDouble(), QString("18500000000000000000").toDouble()); + + // Postgres returns invalid QVariants on overflow + q.setNumericalPrecisionPolicy(QSql::HighPrecision); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::String); + + q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::Invalid); + + q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt32); + QVERIFY_SQL(q, exec(query)); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).type(), QVariant::Invalid); +} + +// This test needs a ODBC data source containing MYSQL in it's name +void tst_QSqlDatabase::mysqlOdbc_unsignedIntegers() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!db.driverName().startsWith("QODBC") || !dbName.toUpper().contains("MYSQL")) { + QSKIP("MySQL through ODBC-driver specific test", SkipSingle); + return; + } + + QSqlQuery q(db); + QString tableName = qTableName("uint"); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (foo integer(10) unsigned, bar integer(10))").arg(tableName))); + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (-4000000000, -4000000000)").arg(tableName))); + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (4000000000, 4000000000)").arg(tableName))); + + QVERIFY_SQL(q, exec(QString("SELECT foo, bar FROM %1").arg(tableName))); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toString(), QString("0")); + QCOMPARE(q.value(1).toString(), QString("-2147483648")); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toString(), QString("4000000000")); + QCOMPARE(q.value(1).toString(), QString("2147483647")); +} + +void tst_QSqlDatabase::accessOdbc_strings() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!tst_Databases::isMSAccess(db)) { + QSKIP("MS Access specific test", SkipSingle); + return; + } + + QSqlQuery q(db); + QString tableName = qTableName("strings"); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (aStr memo, bStr memo, cStr memo, dStr memo" + ", eStr memo, fStr memo, gStr memo, hStr memo)").arg(tableName))); + + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?, ?, ?, ?, ?, ?, ?)").arg(tableName))); + QString aStr, bStr, cStr, dStr, eStr, fStr, gStr, hStr; + + q.bindValue(0, aStr.fill('A', 32)); + q.bindValue(1, bStr.fill('B', 127)); + q.bindValue(2, cStr.fill('C', 128)); + q.bindValue(3, dStr.fill('D', 129)); + q.bindValue(4, eStr.fill('E', 254)); + q.bindValue(5, fStr.fill('F', 255)); + q.bindValue(6, gStr.fill('G', 256)); + q.bindValue(7, hStr.fill('H', 512)); + + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, exec(QString("SELECT aStr, bStr, cStr, dStr, eStr, fStr, gStr, hStr FROM %1").arg(tableName))); + q.next(); + QCOMPARE(q.value(0).toString(), aStr); + QCOMPARE(q.value(1).toString(), bStr); + QCOMPARE(q.value(2).toString(), cStr); + QCOMPARE(q.value(3).toString(), dStr); + QCOMPARE(q.value(4).toString(), eStr); + QCOMPARE(q.value(5).toString(), fStr); + QCOMPARE(q.value(6).toString(), gStr); + QCOMPARE(q.value(7).toString(), hStr); +} + +// For task 125053 +void tst_QSqlDatabase::ibase_numericFields() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + + QSqlQuery q(db); + QString tableName = qTableName("numericfields"); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id int not null, num1 NUMERIC(2,1), " + "num2 NUMERIC(5,2), num3 NUMERIC(10,3), " + "num4 NUMERIC(18,4))").arg(tableName))); + + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (1, 1.1, 123.45, 1234567.123, 10203040506070.8090)").arg(tableName))); + + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?, ?, ?, ?)").arg(tableName))); + + double num1 = 1.1; + double num2 = 123.45; + double num3 = 1234567.123; + double num4 = 10203040506070.8090; + + q.bindValue(0, 2); + q.bindValue(1, QVariant(num1)); + q.bindValue(2, QVariant(num2)); + q.bindValue(3, QVariant(num3)); + q.bindValue(4, QVariant(num4)); + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, exec(QString("SELECT id, num1, num2, num3, num4 FROM %1").arg(tableName))); + + int id = 0; + while (q.next()) { + QCOMPARE(q.value(0).toInt(), ++id); + QCOMPARE(q.value(1).toString(), QString("%1").arg(num1)); + QCOMPARE(q.value(2).toString(), QString("%1").arg(num2)); + QCOMPARE(QString("%1").arg(q.value(3).toDouble()), QString("%1").arg(num3)); + QCOMPARE(QString("%1").arg(q.value(4).toDouble()), QString("%1").arg(num4)); + QVERIFY(q.value(0).type() == QVariant::Int); + QVERIFY(q.value(1).type() == QVariant::Double); + QVERIFY(q.value(2).type() == QVariant::Double); + QVERIFY(q.value(3).type() == QVariant::Double); + QVERIFY(q.value(4).type() == QVariant::Double); + + QCOMPARE(q.record().field(1).length(), 2); + QCOMPARE(q.record().field(1).precision(), 1); + QCOMPARE(q.record().field(2).length(), 5); + QCOMPARE(q.record().field(2).precision(), 2); + QCOMPARE(q.record().field(3).length(), 10); + QCOMPARE(q.record().field(3).precision(), 3); + QCOMPARE(q.record().field(4).length(), 18); + QCOMPARE(q.record().field(4).precision(), 4); + QVERIFY(q.record().field(0).requiredStatus() == QSqlField::Required); + QVERIFY(q.record().field(1).requiredStatus() == QSqlField::Optional); + } + + QSqlRecord r = db.record(tableName); + QVERIFY(r.field(0).type() == QVariant::Int); + QVERIFY(r.field(1).type() == QVariant::Double); + QVERIFY(r.field(2).type() == QVariant::Double); + QVERIFY(r.field(3).type() == QVariant::Double); + QVERIFY(r.field(4).type() == QVariant::Double); + QCOMPARE(r.field(1).length(), 2); + QCOMPARE(r.field(1).precision(), 1); + QCOMPARE(r.field(2).length(), 5); + QCOMPARE(r.field(2).precision(), 2); + QCOMPARE(r.field(3).length(), 10); + QCOMPARE(r.field(3).precision(), 3); + QCOMPARE(r.field(4).length(), 18); + QCOMPARE(r.field(4).precision(), 4); + QVERIFY(r.field(0).requiredStatus() == QSqlField::Required); + QVERIFY(r.field(1).requiredStatus() == QSqlField::Optional); +} + +void tst_QSqlDatabase::ibase_fetchBlobs() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + + QString tableName = qTableName("qtest_ibaseblobs"); + QSqlQuery q(db); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (blob1 BLOB segment size 256)").arg(tableName))); + + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName))); + q.bindValue(0, QByteArray().fill('x', 1024)); + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName))); + q.bindValue(0, QByteArray().fill('x', 16383)); + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName))); + q.bindValue(0, QByteArray().fill('x', 17408)); + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, exec(QString("SELECT * FROM %1").arg(tableName))); + + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toByteArray().size(), 1024); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toByteArray().size(), 16383); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toByteArray().size(), 17408); +} + +void tst_QSqlDatabase::ibase_procWithoutReturnValues() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + + QSqlQuery q(db); + QString procName = qTableName("qtest_proc1"); + q.exec(QString("drop procedure %1").arg(procName)); + QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " (str VARCHAR(10))\nAS BEGIN\nstr='test';\nEND;")); + QVERIFY_SQL(q, exec(QString("execute procedure %1('qtest')").arg(procName))); + q.exec(QString("drop procedure %1").arg(procName)); +} + +void tst_QSqlDatabase::ibase_procWithReturnValues() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!db.driverName().startsWith("QIBASE")) { + QSKIP("InterBase specific test", SkipSingle); + return; + } + + QString procName = qTableName("qtest_proc2"); + + QSqlQuery q(db); + q.exec(QString("drop procedure %1").arg(procName)); + QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " (" + "\nABC INTEGER)" + "\nRETURNS (" + "\nRESULT INTEGER)" + "\nAS" + "\nbegin" + "\nRESULT = 10;" + "\nsuspend;" + "\nend")); + + // Interbase procedures can be executed in two ways: EXECUTE PROCEDURE or SELECT + QVERIFY_SQL(q, exec(QString("execute procedure %1(123)").arg(procName))); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toInt(), 10); + QVERIFY_SQL(q, exec(QString("select result from %1(456)").arg(procName))); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toInt(), 10); + + q.exec(QString("drop procedure %1").arg(procName)); +} + +void tst_QSqlDatabase::formatValueTrimStrings() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QSqlQuery q(db); + + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (50, 'Trim Test ', 'Trim Test 2 ')").arg(qTableName("qtest")))); + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (51, 'TrimTest', 'Trim Test 2')").arg(qTableName("qtest")))); + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (52, ' ', ' ')").arg(qTableName("qtest")))); + + QVERIFY_SQL(q, exec(QString("SELECT t_varchar, t_char FROM %1 WHERE id >= 50 AND id <= 52 ORDER BY id").arg(qTableName("qtest")))); + + QVERIFY_SQL(q, next()); + + QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("'Trim Test'")); + QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("'Trim Test 2'")); + + QVERIFY_SQL(q, next()); + QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("'TrimTest'")); + QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("'Trim Test 2'")); + + QVERIFY_SQL(q, next()); + QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("''")); + QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("''")); + +} + +void tst_QSqlDatabase::odbc_reopenDatabase() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + if (!tst_Databases::isSqlServer(db)) { + QSKIP("SQL Server (ODBC) specific test", SkipSingle); + return; + } + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("SELECT * from " + qTableName("qtest"))); + QVERIFY_SQL(q, next()); + db.open(); + QVERIFY_SQL(q, exec("SELECT * from " + qTableName("qtest"))); + QVERIFY_SQL(q, next()); + db.open(); +} + +void tst_QSqlDatabase::odbc_bindBoolean() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QODBC"); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("CREATE TABLE " + qTableName("qtestBindBool") + "(id int, boolvalue bit)")); + + // Bind and insert + QVERIFY_SQL(q, prepare("INSERT INTO " + qTableName("qtestBindBool") + " VALUES(?, ?)")); + q.bindValue(0, 1); + q.bindValue(1, true); + QVERIFY_SQL(q, exec()); + q.bindValue(0, 2); + q.bindValue(1, false); + QVERIFY_SQL(q, exec()); + + // Retrive + QVERIFY_SQL(q, exec("SELECT id, boolvalue FROM " + qTableName("qtestBindBool") + " ORDER BY id")); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toInt(), 1); + QCOMPARE(q.value(1).toBool(), true); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toInt(), 2); + QCOMPARE(q.value(1).toBool(), false); +} + +void tst_QSqlDatabase::mysql_multiselect() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QMYSQL"); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("select version()")); + QVERIFY_SQL(q, next()); + QString version=tst_Databases::getMySqlVersion( db ); + int ver=version.section(QChar::fromLatin1('.'),0,1).toDouble(); + if (ver >= 4.1) + QSKIP("Test requires MySQL >= 4.1", SkipSingle); + + QVERIFY_SQL(q, exec("SELECT * FROM " + qTableName("qtest") + "; SELECT * FROM " + qTableName("qtest"))); + QVERIFY_SQL(q, next()); + QVERIFY_SQL(q, exec("SELECT * FROM " + qTableName("qtest") + "; SELECT * FROM " + qTableName("qtest"))); + QVERIFY_SQL(q, next()); + QVERIFY_SQL(q, exec("SELECT * FROM " + qTableName("qtest"))); +} + +void tst_QSqlDatabase::ibase_useCustomCharset() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + QString nonlatin1string("��"); + + db.close(); + db.setConnectOptions("ISC_DPB_LC_CTYPE=Latin1"); + db.open(); + + QString tableName = qTableName("latin1table"); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(text VARCHAR(6) CHARACTER SET Latin1)").arg(tableName))); + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName))); + q.addBindValue(nonlatin1string); + QVERIFY_SQL(q, exec()); + QVERIFY_SQL(q, exec(QString("SELECT text FROM %1").arg(tableName))); + QVERIFY_SQL(q, next()); + QCOMPARE(toHex(q.value(0).toString()), toHex(nonlatin1string)); +} + +void tst_QSqlDatabase::oci_serverDetach() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QOCI"); + + for (int i = 0; i < 2; i++) { + db.close(); + if (db.open()) { + QSqlQuery query(db); + query.exec("SELECT 1 FROM DUAL"); + db.close(); + } else { + QFAIL(tst_Databases::printError(db.lastError(), db)); + } + } + if(!db.open()) + qFatal(tst_Databases::printError(db.lastError(), db)); +} + +void tst_QSqlDatabase::oci_xmltypeSupport() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QOCI"); + + QString tableName = qTableName("qtest_xmltype"); + QString xml("<?xml version=\"1.0\"?><TABLE_NAME>MY_TABLE</TABLE_NAME>"); + QSqlQuery q(db); + + // Embedding the XML in the statement + if(!q.exec(QString("CREATE TABLE %1(xmldata xmltype)").arg(tableName))) + QSKIP("This test requries xml type support", SkipSingle); + QVERIFY_SQL(q, exec(QString("INSERT INTO %1 values('%2')").arg(tableName).arg(xml))); + QVERIFY_SQL(q, exec(QString("SELECT a.xmldata.getStringVal() FROM %1 a").arg(tableName))); + QVERIFY_SQL(q, last()); + QCOMPARE(q.value(0).toString(), xml); + + // Binding the XML with a prepared statement + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 values(?)").arg(tableName))); + q.addBindValue(xml); + QVERIFY_SQL(q, exec()); + QVERIFY_SQL(q, exec(QString("SELECT a.xmldata.getStringVal() FROM %1 a").arg(tableName))); + QVERIFY_SQL(q, last()); + QCOMPARE(q.value(0).toString(), xml); +} + + +void tst_QSqlDatabase::oci_fieldLength() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QOCI"); + + QString tableName = qTableName("qtest"); + QSqlQuery q(db); + + QVERIFY_SQL(q, exec(QString("SELECT t_varchar, t_char FROM %1").arg(tableName))); + QVERIFY_SQL(q, next()); + QCOMPARE(q.record().field(0).length(), 40); + QCOMPARE(q.record().field(1).length(), 40); +} + +// This test isn't really necessary as SQL_GUID / uniqueidentifier is +// already tested in recordSQLServer(). +void tst_QSqlDatabase::odbc_uniqueidentifier() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + if (!tst_Databases::isSqlServer(db)) { + QSKIP("SQL Server (ODBC) specific test", SkipSingle); + return; + } + + QString tableName = qTableName("qtest_sqlguid"); + QString guid = QString("AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE"); + QString invalidGuid = QString("GAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE"); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(id uniqueidentifier)").arg(tableName))); + + q.prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName));; + q.addBindValue(guid); + QVERIFY_SQL(q, exec()); + + q.addBindValue(invalidGuid); + QEXPECT_FAIL("", "The GUID string is required to be correctly formated!", + Continue); + QVERIFY_SQL(q, exec()); + + QVERIFY_SQL(q, exec(QString("SELECT id FROM %1").arg(tableName))); + QVERIFY_SQL(q, next()); + QCOMPARE(q.value(0).toString(), guid); +} + +void tst_QSqlDatabase::getConnectionName() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QCOMPARE(db.connectionName(), dbName); + QSqlDatabase clone = QSqlDatabase::cloneDatabase(db, "clonedDatabase"); + QCOMPARE(clone.connectionName(), QString("clonedDatabase")); + QTest::ignoreMessage(QtWarningMsg, "QSqlDatabasePrivate::removeDatabase: " + "connection 'clonedDatabase' is still in use, all queries will cease to work."); + QSqlDatabase::removeDatabase("clonedDatabase"); + QCOMPARE(clone.connectionName(), QString()); + QCOMPARE(db.connectionName(), dbName); +} + +void tst_QSqlDatabase::odbc_uintfield() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QODBC"); + + QString tableName = qTableName("uint_table"); + unsigned int val = 4294967295U; + + QSqlQuery q(db); + q.exec(QString("CREATE TABLE %1(num numeric(10))").arg(tableName)); + q.prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName)); + q.addBindValue(val); + QVERIFY_SQL(q, exec()); + + q.exec(QString("SELECT num FROM %1").arg(tableName)); + if (q.next()) + QCOMPARE(q.value(0).toUInt(), val); +} + +void tst_QSqlDatabase::eventNotification() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + + QSqlDriver *driver = db.driver(); + if (!driver->hasFeature(QSqlDriver::EventNotifications)) + QSKIP("DBMS doesn't support event notifications", SkipSingle); + + // Not subscribed to any events yet + QCOMPARE(driver->subscribedToNotifications().size(), 0); + + // Subscribe to "event_foo" + QVERIFY_SQL(*driver, subscribeToNotification(QLatin1String("event_foo"))); + QCOMPARE(driver->subscribedToNotifications().size(), 1); + QVERIFY(driver->subscribedToNotifications().contains("event_foo")); + + // Can't subscribe to the same event multiple times + QVERIFY2(!driver->subscribeToNotification(QLatin1String("event_foo")), "Shouldn't be able to subscribe to event_foo twice"); + QCOMPARE(driver->subscribedToNotifications().size(), 1); + + // Unsubscribe from "event_foo" + QVERIFY_SQL(*driver, unsubscribeFromNotification(QLatin1String("event_foo"))); + QCOMPARE(driver->subscribedToNotifications().size(), 0); + + // Re-subscribing to "event_foo" now is allowed + QVERIFY_SQL(*driver, subscribeToNotification(QLatin1String("event_foo"))); + QCOMPARE(driver->subscribedToNotifications().size(), 1); + + // closing the connection causes automatically unsubscription from all events + db.close(); + QCOMPARE(driver->subscribedToNotifications().size(), 0); + + // Can't subscribe to anything while database is closed + QVERIFY2(!driver->subscribeToNotification(QLatin1String("event_foo")), "Shouldn't be able to subscribe to event_foo"); + QCOMPARE(driver->subscribedToNotifications().size(), 0); + + db.open(); +} + +void tst_QSqlDatabase::eventNotificationIBase() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QIBASE"); + + QString procedureName = qTableName("posteventProc"); + QSqlDriver *driver=db.driver(); + QVERIFY_SQL(*driver, subscribeToNotification(procedureName)); + QTest::qWait(300); // Interbase needs some time to call the driver callback. + + db.transaction(); // InterBase events are posted from within transactions. + QSqlQuery q(db); + q.exec(QString("DROP PROCEDURE %1").arg(procedureName)); + q.exec(QString("CREATE PROCEDURE %1\nAS BEGIN\nPOST_EVENT '%1';\nEND;").arg(procedureName)); + q.exec(QString("EXECUTE PROCEDURE %1").arg(procedureName)); + QSignalSpy spy(driver, SIGNAL(notification(const QString&))); + db.commit(); // No notifications are posted until the transaction is committed. + QTest::qWait(300); // Interbase needs some time to post the notification and call the driver callback. + // This happends from another thread, and we have to process events in order for the + // event handler in the driver to be executed and emit the notification signal. + + QCOMPARE(spy.count(), 1); + QList<QVariant> arguments = spy.takeFirst(); + QVERIFY(arguments.at(0).toString() == procedureName); + QVERIFY_SQL(*driver, unsubscribeFromNotification(procedureName)); + q.exec(QString("DROP PROCEDURE %1").arg(procedureName)); +} + +void tst_QSqlDatabase::eventNotificationPSQL() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QPSQL"); + + QSqlQuery query(db); + QString procedureName = qTableName("posteventProc"); + + QSqlDriver *driver=db.driver(); + QVERIFY_SQL(*driver, subscribeToNotification(procedureName)); + QSignalSpy spy(db.driver(), SIGNAL(notification(const QString&))); + query.exec(QString("NOTIFY \"%1\"").arg(procedureName)); + QCoreApplication::processEvents(); + QCOMPARE(spy.count(), 1); + QList<QVariant> arguments = spy.takeFirst(); + QVERIFY(arguments.at(0).toString() == procedureName); + QVERIFY_SQL(*driver, unsubscribeFromNotification(procedureName)); +} + +void tst_QSqlDatabase::sqlite_bindAndFetchUInt() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QSQLITE3"); + + QSqlQuery q(db); + QString tableName = qTableName("uint_test"); + QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(uint_field UNSIGNED INTEGER)").arg(tableName))); + QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName))); + q.addBindValue(4000000000U); + QVERIFY_SQL(q, exec()); + QVERIFY_SQL(q, exec(QString("SELECT uint_field FROM %1").arg(tableName))); + QVERIFY_SQL(q, next()); + + // All integers in SQLite are signed, so even though we bound the value + // as an UInt it will come back as a LongLong + QCOMPARE(q.value(0).type(), QVariant::LongLong); + QCOMPARE(q.value(0).toUInt(), 4000000000U); +} + +void tst_QSqlDatabase::db2_valueCacheUpdate() +{ + QFETCH(QString, dbName); + QSqlDatabase db = QSqlDatabase::database(dbName); + CHECK_DATABASE(db); + DBMS_SPECIFIC(db, "QDB2"); + + QString tableName = qTableName("qtest"); + QSqlQuery q(db); + q.exec(QString("SELECT id, t_varchar, t_char, t_numeric FROM %1").arg(tableName)); + q.next(); + QVariant c4 = q.value(3); + QVariant c3 = q.value(2); + QVariant c2 = q.value(1); + QVariant c1 = q.value(0); + QCOMPARE(c4.toString(), q.value(3).toString()); + QCOMPARE(c3.toString(), q.value(2).toString()); + QCOMPARE(c2.toString(), q.value(1).toString()); + QCOMPARE(c1.toString(), q.value(0).toString()); +} + +QTEST_MAIN(tst_QSqlDatabase) +#include "tst_qsqldatabase.moc" |