1/****************************************************************************
2**
3** Copyright (C) 2016 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the test suite of the Qt Toolkit.
7**
8** $QT_BEGIN_LICENSE:GPL-EXCEPT$
9** Commercial License Usage
10** Licensees holding valid commercial Qt licenses may use this file in
11** accordance with the commercial license agreement provided with the
12** Software or, alternatively, in accordance with the terms contained in
13** a written agreement between you and The Qt Company. For licensing terms
14** and conditions see https://www.qt.io/terms-conditions. For further
15** information use the contact form at https://www.qt.io/contact-us.
16**
17** GNU General Public License Usage
18** Alternatively, this file may be used under the terms of the GNU
19** General Public License version 3 as published by the Free Software
20** Foundation with exceptions as appearing in the file LICENSE.GPL3-EXCEPT
21** included in the packaging of this file. Please review the following
22** information to ensure the GNU General Public License requirements will
23** be met: https://www.gnu.org/licenses/gpl-3.0.html.
24**
25** $QT_END_LICENSE$
26**
27****************************************************************************/
28/* possible connection parameters */
29
30#ifndef TST_DATABASES_H
31#define TST_DATABASES_H
32
33#include <QSqlDatabase>
34#include <QSqlDriver>
35#include <QSqlError>
36#include <QSqlQuery>
37#include <QRegularExpression>
38#include <QRegularExpressionMatch>
39#include <QDir>
40#include <QScopedPointer>
41#include <QVariant>
42#include <QDebug>
43#include <QSqlTableModel>
44#include <QtSql/private/qsqldriver_p.h>
45#include <QtTest/QtTest>
46
47#define CHECK_DATABASE( db ) \
48 if ( !db.isValid() ) { qFatal( "db is Invalid" ); }
49
50#define QVERIFY_SQL(q, stmt) QVERIFY2((q).stmt, tst_Databases::printError((q).lastError(), db))
51#define QFAIL_SQL(q, stmt) QVERIFY2(!(q).stmt, tst_Databases::printError((q).lastError(), db))
52
53#define DBMS_SPECIFIC(db, driver) \
54 if (!db.driverName().startsWith(driver)) { QSKIP(driver " specific test"); }
55
56// ### use QSystem::hostName if it is integrated in qtest/main
57static QString qGetHostName()
58{
59 static QString hostname;
60
61 if (hostname.isEmpty()) {
62 hostname = QSysInfo::machineHostName();
63 hostname.replace(before: QLatin1Char( '.' ), after: QLatin1Char( '_' ));
64 hostname.replace(before: QLatin1Char( '-' ), after: QLatin1Char( '_' ));
65 }
66
67 return hostname;
68}
69
70// to prevent nameclashes on our database server, each machine
71// will use its own set of table names. Call this function to get
72// "tablename_hostname"
73inline QString fixupTableName(const QString &tableName, QSqlDatabase db)
74{
75 QString tbName = tableName;
76 // On Oracle we are limited to 30 character tablenames
77 QSqlDriverPrivate *d = static_cast<QSqlDriverPrivate *>(QObjectPrivate::get(o: db.driver()));
78 if (d && d->dbmsType == QSqlDriver::Oracle)
79 tbName.truncate(pos: 30);
80 return tbName;
81}
82
83inline static QString qTableName(const QString &prefix, const char *sourceFileName,
84 QSqlDatabase db, bool escape = true)
85{
86 const auto tableStr = fixupTableName(tableName: QString(QLatin1String("dbtst") + db.driverName() +
87 QString::number(qHash(key: QLatin1String(sourceFileName) +
88 "_" + qGetHostName().replace(before: "-", after: "_")), base: 16) +
89 "_" + prefix), db);
90 return escape ? db.driver()->escapeIdentifier(identifier: tableStr, type: QSqlDriver::TableName) : tableStr;
91}
92
93inline static QString qTableName(const QString& prefix, QSqlDatabase db)
94{
95 QString tableStr;
96 if (db.driverName().toLower().contains(s: "ODBC"))
97 tableStr += QLatin1String("_odbc");
98 return fixupTableName(tableName: QString(db.driver()->escapeIdentifier(identifier: prefix + tableStr + QLatin1Char('_') +
99 qGetHostName(), type: QSqlDriver::TableName)),db);
100}
101
102inline static bool testWhiteSpaceNames( const QString &name )
103{
104/* return name.startsWith( "QPSQL" )
105 || name.startsWith( "QODBC" )
106 || name.startsWith( "QSQLITE" )
107 || name.startsWith( "QMYSQL" );*/
108 return name != QLatin1String("QSQLITE2");
109}
110
111inline static QString toHex( const QString& binary )
112{
113 QString str;
114 static char const hexchars[] = "0123456789ABCDEF";
115
116 for ( int i = 0; i < binary.size(); i++ ) {
117 ushort code = binary.at(i).unicode();
118 str += (QChar)(hexchars[ (code >> 12) & 0x0F ]);
119 str += (QChar)(hexchars[ (code >> 8) & 0x0F ]);
120 str += (QChar)(hexchars[ (code >> 4) & 0x0F ]);
121 str += (QChar)(hexchars[ code & 0x0F ]);
122 }
123
124 return str;
125}
126
127
128class tst_Databases
129{
130
131public:
132 tst_Databases(): counter( 0 )
133 {
134 }
135
136 ~tst_Databases()
137 {
138 close();
139 }
140
141 // returns a testtable consisting of the names of all database connections if
142 // driverPrefix is empty, otherwise only those that start with driverPrefix.
143 int fillTestTable( const QString& driverPrefix = QString() ) const
144 {
145 QTest::addColumn<QString>( name: "dbName" );
146 int count = 0;
147
148 for ( int i = 0; i < dbNames.count(); ++i ) {
149 QSqlDatabase db = QSqlDatabase::database( connectionName: dbNames.at( i ) );
150
151 if ( !db.isValid() )
152 continue;
153
154 if ( driverPrefix.isEmpty() || db.driverName().startsWith( s: driverPrefix ) ) {
155 QTest::newRow( dataTag: dbNames.at( i ).toLatin1() ) << dbNames.at( i );
156 ++count;
157 }
158 }
159
160 return count;
161 }
162
163 int fillTestTableWithStrategies( const QString& driverPrefix = QString() ) const
164 {
165 QTest::addColumn<QString>( name: "dbName" );
166 QTest::addColumn<int>(name: "submitpolicy_i");
167 int count = 0;
168
169 for ( int i = 0; i < dbNames.count(); ++i ) {
170 QSqlDatabase db = QSqlDatabase::database( connectionName: dbNames.at( i ) );
171
172 if ( !db.isValid() )
173 continue;
174
175 if ( driverPrefix.isEmpty() || db.driverName().startsWith( s: driverPrefix ) ) {
176 QTest::newRow( dataTag: QString("%1 [field]").arg(a: dbNames.at( i )).toLatin1() ) << dbNames.at( i ) << (int)QSqlTableModel::OnFieldChange;
177 QTest::newRow( dataTag: QString("%1 [row]").arg(a: dbNames.at( i )).toLatin1() ) << dbNames.at( i ) << (int)QSqlTableModel::OnRowChange;
178 QTest::newRow( dataTag: QString("%1 [manual]").arg(a: dbNames.at( i )).toLatin1() ) << dbNames.at( i ) << (int)QSqlTableModel::OnManualSubmit;
179 ++count;
180 }
181 }
182
183 return count;
184 }
185
186 void addDb( const QString& driver, const QString& dbName,
187 const QString& user = QString(), const QString& passwd = QString(),
188 const QString& host = QString(), int port = -1, const QString params = QString() )
189 {
190 QSqlDatabase db;
191
192 if ( !QSqlDatabase::drivers().contains( str: driver ) ) {
193 qWarning() << "Driver" << driver << "is not installed";
194 return;
195 }
196
197 // construct a stupid unique name
198 QString cName = QString::number( counter++ ) + QLatin1Char('_') + driver + QLatin1Char('@');
199
200 cName += host.isEmpty() ? dbName : host;
201
202 if ( port > 0 )
203 cName += QLatin1Char(':') + QString::number( port );
204
205 db = QSqlDatabase::addDatabase( type: driver, connectionName: cName );
206
207 if ( !db.isValid() ) {
208 qWarning( msg: "Could not create database object" );
209 return;
210 }
211
212 db.setDatabaseName( dbName );
213
214 db.setUserName( user );
215 db.setPassword( passwd );
216 db.setHostName( host );
217 db.setPort( port );
218 db.setConnectOptions( params );
219 dbNames.append( t: cName );
220 }
221
222 bool addDbs()
223 {
224 // Test databases can be defined in a file using the following format:
225 //
226 // {
227 // "entries": [
228 // {
229 // "driver": "QPSQL",
230 // "name": "testdb",
231 // "username": "postgres",
232 // "password": "password",
233 // "hostname": "localhost",
234 // "port": 5432,
235 // "parameters": "extraoptions"
236 // },
237 // {
238 // ....
239 // }
240 // ]
241 // }
242
243 bool added = false;
244 const QString databasesFile(qgetenv(varName: "QT_TEST_DATABASES_FILE"));
245 QFile f(databasesFile.isEmpty() ? "testdbs.json" : databasesFile);
246 if (f.exists() && f.open(flags: QIODevice::ReadOnly)) {
247 const QJsonDocument doc = QJsonDocument::fromJson(json: f.readAll());
248 f.close();
249 const QJsonValue entriesV = doc.object().value(key: QLatin1String("entries"));
250 if (!entriesV.isArray()) {
251 qWarning() << "No entries in " + f.fileName();
252 } else {
253 const QJsonArray entriesA = entriesV.toArray();
254 QJsonArray::const_iterator it = entriesA.constBegin();
255 while (it != entriesA.constEnd()) {
256 if ((*it).isObject()) {
257 const QJsonObject object = (*it).toObject();
258 addDb(driver: object.value(QStringLiteral("driver")).toString(),
259 dbName: object.value(QStringLiteral("name")).toString(),
260 user: object.value(QStringLiteral("username")).toString(),
261 passwd: object.value(QStringLiteral("password")).toString(),
262 host: object.value(QStringLiteral("hostname")).toString(),
263 port: object.value(QStringLiteral("port")).toInt(),
264 params: object.value(QStringLiteral("parameters")).toString());
265 added = true;
266 }
267 ++it;
268 }
269 }
270 }
271 QTemporaryDir *sqLiteDir = dbDir();
272 if (sqLiteDir) {
273 addDb(QStringLiteral("QSQLITE"), dbName: QDir::toNativeSeparators(pathName: sqLiteDir->path() + QStringLiteral("/foo.db")));
274 added = true;
275 }
276 return added;
277 }
278
279 // 'false' return indicates a system error, for example failure to create a temporary directory.
280 bool open()
281 {
282 if (!addDbs())
283 return false;
284
285 QStringList::Iterator it = dbNames.begin();
286
287 while ( it != dbNames.end() ) {
288 QSqlDatabase db = QSqlDatabase::database(connectionName: ( *it ), open: false );
289 qDebug() << "Opening:" << (*it);
290
291 if ( db.isValid() && !db.isOpen() ) {
292 if ( !db.open() ) {
293 qWarning( msg: "tst_Databases: Unable to open %s on %s:\n%s", qPrintable( db.driverName() ), qPrintable( *it ), qPrintable( db.lastError().databaseText() ) );
294 // well... opening failed, so we just ignore the server, maybe it is not running
295 it = dbNames.erase( pos: it );
296 } else {
297 ++it;
298 }
299 }
300 }
301 return true;
302 }
303
304 void close()
305 {
306 for ( QStringList::Iterator it = dbNames.begin(); it != dbNames.end(); ++it ) {
307 {
308 QSqlDatabase db = QSqlDatabase::database(connectionName: ( *it ), open: false );
309
310 if ( db.isValid() && db.isOpen() )
311 db.close();
312 }
313
314 QSqlDatabase::removeDatabase(connectionName: ( *it ) );
315 }
316
317 dbNames.clear();
318 }
319
320 // for debugging only: outputs the connection as string
321 static QString dbToString( const QSqlDatabase db )
322 {
323 QString res = db.driverName() + QLatin1Char('@');
324
325 if ( db.driverName().startsWith( s: "QODBC" ) || db.driverName().startsWith( s: "QOCI" ) ) {
326 res += db.databaseName();
327 } else {
328 res += db.hostName();
329 }
330
331 if ( db.port() > 0 ) {
332 res += QLatin1Char(':') + QString::number( db.port() );
333 }
334
335 return res;
336 }
337
338 // drop a table only if it exists to prevent warnings
339 static void safeDropTables( QSqlDatabase db, const QStringList& tableNames )
340 {
341 bool wasDropped;
342 QSqlQuery q( db );
343 QStringList dbtables=db.tables();
344 QSqlDriver::DbmsType dbType = getDatabaseType(db);
345 foreach(const QString &tableName, tableNames)
346 {
347 wasDropped = true;
348 QString table=tableName;
349 if ( db.driver()->isIdentifierEscaped(identifier: table, type: QSqlDriver::TableName))
350 table = db.driver()->stripDelimiters(identifier: table, type: QSqlDriver::TableName);
351
352 if ( dbtables.contains( str: table, cs: Qt::CaseInsensitive ) ) {
353 foreach(const QString &table2, dbtables.filter(table, Qt::CaseInsensitive)) {
354 if(table2.compare(s: table.section(asep: '.', astart: -1, aend: -1), cs: Qt::CaseInsensitive) == 0) {
355 table=db.driver()->escapeIdentifier(identifier: table2, type: QSqlDriver::TableName);
356 if (dbType == QSqlDriver::PostgreSQL)
357 wasDropped = q.exec( query: "drop table " + table + " cascade");
358 else
359 wasDropped = q.exec( query: "drop table " + table);
360 dbtables.removeAll(t: table2);
361 }
362 }
363 }
364 if ( !wasDropped ) {
365 qWarning() << dbToString(db) << "unable to drop table" << tableName << ':' << q.lastError();
366// qWarning() << "last query:" << q.lastQuery();
367// qWarning() << "dbtables:" << dbtables;
368// qWarning() << "db.tables():" << db.tables();
369 }
370 }
371 }
372
373 static void safeDropTable( QSqlDatabase db, const QString& tableName )
374 {
375 safeDropTables(db, tableNames: QStringList() << tableName);
376 }
377
378 static void safeDropViews( QSqlDatabase db, const QStringList &viewNames )
379 {
380 if ( isMSAccess( db ) ) // Access is sooo stupid.
381 safeDropTables( db, tableNames: viewNames );
382
383 bool wasDropped;
384 QSqlQuery q( db );
385 QStringList dbtables=db.tables(type: QSql::Views);
386
387 foreach(QString viewName, viewNames)
388 {
389 wasDropped = true;
390 QString view=viewName;
391 if ( db.driver()->isIdentifierEscaped(identifier: view, type: QSqlDriver::TableName))
392 view = db.driver()->stripDelimiters(identifier: view, type: QSqlDriver::TableName);
393
394 if ( dbtables.contains( str: view, cs: Qt::CaseInsensitive ) ) {
395 foreach(const QString &view2, dbtables.filter(view, Qt::CaseInsensitive)) {
396 if(view2.compare(s: view.section(asep: '.', astart: -1, aend: -1), cs: Qt::CaseInsensitive) == 0) {
397 view=db.driver()->escapeIdentifier(identifier: view2, type: QSqlDriver::TableName);
398 wasDropped = q.exec( query: "drop view " + view);
399 dbtables.removeAll(t: view);
400 }
401 }
402 }
403
404 if ( !wasDropped )
405 qWarning() << dbToString(db) << "unable to drop view" << viewName << ':' << q.lastError();
406// << "\nlast query:" << q.lastQuery()
407// << "\ndbtables:" << dbtables
408// << "\ndb.tables(QSql::Views):" << db.tables(QSql::Views);
409 }
410 }
411
412 static void safeDropView( QSqlDatabase db, const QString& tableName )
413 {
414 safeDropViews(db, viewNames: QStringList() << tableName);
415 }
416
417 // returns the type name of the blob datatype for the database db.
418 // blobSize is only used if the db doesn't have a generic blob type
419 static QString blobTypeName( QSqlDatabase db, int blobSize = 10000 )
420 {
421 const QSqlDriver::DbmsType dbType = getDatabaseType(db);
422 if (dbType == QSqlDriver::MySqlServer)
423 return "longblob";
424
425 if (dbType == QSqlDriver::PostgreSQL)
426 return "bytea";
427
428 if (dbType == QSqlDriver::Sybase
429 || dbType == QSqlDriver::MSSqlServer
430 || isMSAccess( db ) )
431 return "image";
432
433 if (dbType == QSqlDriver::DB2)
434 return QString( "blob(%1)" ).arg( a: blobSize );
435
436 if (dbType == QSqlDriver::Interbase)
437 return QString( "blob sub_type 0 segment size 4096" );
438
439 if (dbType == QSqlDriver::Oracle
440 || dbType == QSqlDriver::SQLite)
441 return "blob";
442
443 qDebug() << "tst_Databases::blobTypeName: Don't know the blob type for" << dbToString( db );
444
445 return "blob";
446 }
447
448 static QString dateTimeTypeName(QSqlDatabase db)
449 {
450 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
451 if (dbType == QSqlDriver::PostgreSQL)
452 return QLatin1String("timestamptz");
453 if (dbType == QSqlDriver::Oracle && getOraVersion(db) >= 9)
454 return QLatin1String("timestamp(0)");
455 return QLatin1String("datetime");
456 }
457
458 static QString autoFieldName( QSqlDatabase db )
459 {
460 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
461 if (dbType == QSqlDriver::MySqlServer)
462 return "AUTO_INCREMENT";
463 if (dbType == QSqlDriver::Sybase || dbType == QSqlDriver::MSSqlServer)
464 return "IDENTITY";
465/* if (dbType == QSqlDriver::PostgreSQL)
466 return "SERIAL";*/
467// if (dbType == QSqlDriver::DB2)
468// return "GENERATED BY DEFAULT AS IDENTITY";
469
470 return QString();
471 }
472
473 static QByteArray printError( const QSqlError& err )
474 {
475 QString result;
476 if (!err.nativeErrorCode().isEmpty())
477 result += '(' + err.nativeErrorCode() + ") ";
478 result += '\'';
479 if(!err.driverText().isEmpty())
480 result += err.driverText() + "' || '";
481 result += err.databaseText() + QLatin1Char('\'');
482 return result.toLocal8Bit();
483 }
484
485 static QByteArray printError( const QSqlError& err, const QSqlDatabase& db )
486 {
487 QString result(dbToString(db) + ": ");
488 if (!err.nativeErrorCode().isEmpty())
489 result += '(' + err.nativeErrorCode() + ") ";
490 result += '\'';
491 if(!err.driverText().isEmpty())
492 result += err.driverText() + "' || '";
493 result += err.databaseText() + QLatin1Char('\'');
494 return result.toLocal8Bit();
495 }
496
497 static QSqlDriver::DbmsType getDatabaseType(QSqlDatabase db)
498 {
499 QSqlDriverPrivate *d = static_cast<QSqlDriverPrivate *>(QObjectPrivate::get(o: db.driver()));
500 return d->dbmsType;
501 }
502
503 static bool isMSAccess( QSqlDatabase db )
504 {
505 return db.databaseName().contains( s: "Access Driver", cs: Qt::CaseInsensitive );
506 }
507
508 // -1 on fail, else Oracle version
509 static int getOraVersion( QSqlDatabase db )
510 {
511 int ver = -1;
512 QSqlQuery q( "SELECT banner FROM v$version", db );
513 q.next();
514
515 QRegularExpression vers("([0-9]+)\\.[0-9\\.]+[0-9]");
516 QRegularExpressionMatch match = vers.match(subject: q.value(i: 0).toString());
517 if (match.hasMatch()) {
518 bool ok;
519 ver = match.captured(nth: 1).toInt(ok: &ok);
520
521 if (!ok)
522 ver = -1;
523 }
524
525 return ver;
526 }
527
528 static QString getMySqlVersion( const QSqlDatabase &db )
529 {
530 QSqlQuery q(db);
531 q.exec( query: "select version()" );
532 if(q.next())
533 return q.value( i: 0 ).toString();
534 else
535 return QString();
536 }
537
538 static QString getPSQLVersion( const QSqlDatabase &db )
539 {
540 QSqlQuery q(db);
541 q.exec( query: "select version()" );
542 if(q.next())
543 return q.value( i: 0 ).toString();
544 else
545 return QString();
546 }
547
548 QStringList dbNames;
549 int counter;
550
551private:
552 QTemporaryDir *dbDir()
553 {
554 if (m_dbDir.isNull()) {
555 m_dbDir.reset(other: new QTemporaryDir);
556 if (!m_dbDir->isValid()) {
557 qWarning() << Q_FUNC_INFO << "Unable to create a temporary directory: " << QDir::toNativeSeparators(pathName: m_dbDir->path());
558 m_dbDir.reset();
559 }
560 }
561 return m_dbDir.data();
562 }
563
564 QScopedPointer<QTemporaryDir> m_dbDir;
565};
566
567#endif
568
569

source code of qtbase/tests/auto/sql/kernel/qsqldatabase/tst_databases.h