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
29
30#include <QtTest/QtTest>
31#include <qsqldatabase.h>
32#include <qsqlquery.h>
33#include <qsqldriver.h>
34#include <qsqlrecord.h>
35#include <qsqlfield.h>
36#include <qsqlindex.h>
37#include <qregularexpression.h>
38#include <qvariant.h>
39#include <qdatetime.h>
40#include <qdebug.h>
41
42#include "tst_databases.h"
43
44Q_DECLARE_METATYPE(QSqlDriver::NotificationSource)
45
46QT_FORWARD_DECLARE_CLASS(QSqlDatabase)
47struct FieldDef;
48
49class tst_QSqlDatabase : public QObject
50{
51 Q_OBJECT
52
53public:
54 tst_QSqlDatabase();
55 virtual ~tst_QSqlDatabase();
56
57public slots:
58 void initTestCase();
59 void cleanupTestCase();
60 void init();
61 void cleanup();
62private slots:
63 void record_data() { generic_data(); }
64 //void record();
65 void open_data() { generic_data(); }
66 void open();
67 void tables_data() { generic_data(); }
68 void tables();
69 void oci_tables_data() { generic_data(engine: "QOCI"); }
70 void oci_tables();
71 void transaction_data() { generic_data(); }
72 void transaction();
73 void eventNotification_data() { generic_data(); }
74 void eventNotification();
75 void addDatabase();
76 void errorReporting_data();
77 void errorReporting();
78 void cloneDatabase_data() { generic_data(); }
79 void cloneDatabase();
80
81 //database specific tests
82 void recordMySQL_data() { generic_data(engine: "QMYSQL"); }
83 void recordMySQL();
84 void recordPSQL_data() { generic_data(engine: "QPSQL"); }
85 void recordPSQL();
86 void recordOCI_data() { generic_data(engine: "QOCI"); }
87 void recordOCI();
88 void recordTDS_data() { generic_data(engine: "QTDS"); }
89 void recordTDS();
90 void recordDB2_data() { generic_data(engine: "QDB2"); }
91 void recordDB2();
92 void recordSQLite_data() { generic_data(engine: "QSQLITE"); }
93 void recordSQLite();
94 void recordAccess_data() { generic_data(engine: "QODBC"); }
95 void recordAccess();
96 void recordSQLServer_data() { generic_data(engine: "QODBC"); }
97 void recordSQLServer();
98 void recordIBase_data() {generic_data(engine: "QIBASE"); }
99 void recordIBase();
100
101 void eventNotificationIBase_data() { generic_data(engine: "QIBASE"); }
102 void eventNotificationIBase();
103 void eventNotificationPSQL_data() { generic_data(engine: "QPSQL"); }
104 void eventNotificationPSQL();
105 void eventNotificationSQLite_data() { generic_data(engine: "QSQLITE"); }
106 void eventNotificationSQLite();
107
108 //database specific 64 bit integer test
109 void bigIntField_data() { generic_data(); }
110 void bigIntField();
111
112 // general tests
113 void getConnectionName_data() { generic_data(); }
114 void getConnectionName(); // For task 129992
115
116 //problem specific tests
117 void alterTable_data() { generic_data(); }
118 void alterTable();
119 void caseSensivity_data() { generic_data(); }
120 void caseSensivity();
121 void noEscapedFieldNamesInRecord_data() { generic_data(); }
122 void noEscapedFieldNamesInRecord();
123 void whitespaceInIdentifiers_data() { generic_data(); }
124 void whitespaceInIdentifiers();
125 void formatValueTrimStrings_data() { generic_data(); }
126 void formatValueTrimStrings();
127 void precisionPolicy_data() { generic_data(); }
128 void precisionPolicy();
129 void infinityAndNan_data() { generic_data(); }
130 void infinityAndNan();
131 void multipleThreads_data() { generic_data(); }
132 void multipleThreads();
133
134 void db2_valueCacheUpdate_data() { generic_data(engine: "QDB2"); }
135 void db2_valueCacheUpdate();
136
137 void psql_schemas_data() { generic_data(engine: "QPSQL"); }
138 void psql_schemas();
139 void psql_escapedIdentifiers_data() { generic_data(engine: "QPSQL"); }
140 void psql_escapedIdentifiers();
141 void psql_escapeBytea_data() { generic_data(engine: "QPSQL"); }
142 void psql_escapeBytea();
143 void psql_bug249059_data() { generic_data(engine: "QPSQL"); }
144 void psql_bug249059();
145
146 void mysqlOdbc_unsignedIntegers_data() { generic_data(); }
147 void mysqlOdbc_unsignedIntegers();
148 void mysql_multiselect_data() { generic_data(engine: "QMYSQL"); }
149 void mysql_multiselect(); // For task 144331
150 void mysql_savepointtest_data() { generic_data(engine: "QMYSQL"); }
151 void mysql_savepointtest();
152 void mysql_connectWithInvalidAddress();
153
154 void accessOdbc_strings_data() { generic_data(); }
155 void accessOdbc_strings();
156
157 void ibase_numericFields_data() { generic_data(engine: "QIBASE"); }
158 void ibase_numericFields(); // For task 125053
159 void ibase_fetchBlobs_data() { generic_data(engine: "QIBASE"); }
160 void ibase_fetchBlobs(); // For task 143471
161 void ibase_useCustomCharset_data() { generic_data(engine: "QIBASE"); }
162 void ibase_useCustomCharset(); // For task 134608
163 void ibase_procWithoutReturnValues_data() { generic_data(engine: "QIBASE"); } // For task 165423
164 void ibase_procWithoutReturnValues();
165 void ibase_procWithReturnValues_data() { generic_data(engine: "QIBASE"); } // For task 177530
166 void ibase_procWithReturnValues();
167
168 void odbc_reopenDatabase_data() { generic_data(engine: "QODBC"); }
169 void odbc_reopenDatabase();
170 void odbc_uniqueidentifier_data() { generic_data(engine: "QODBC"); }
171 void odbc_uniqueidentifier(); // For task 141822
172 void odbc_uintfield_data() { generic_data(engine: "QODBC"); }
173 void odbc_uintfield();
174 void odbc_bindBoolean_data() { generic_data(engine: "QODBC"); }
175 void odbc_bindBoolean();
176 void odbc_testqGetString_data() { generic_data(engine: "QODBC"); }
177 void odbc_testqGetString();
178
179 void oci_serverDetach_data() { generic_data(engine: "QOCI"); }
180 void oci_serverDetach(); // For task 154518
181 void oci_xmltypeSupport_data() { generic_data(engine: "QOCI"); }
182 void oci_xmltypeSupport();
183 void oci_fieldLength_data() { generic_data(engine: "QOCI"); }
184 void oci_fieldLength();
185 void oci_synonymstest_data() { generic_data(engine: "QOCI"); }
186 void oci_synonymstest();
187
188 void sqlite_bindAndFetchUInt_data() { generic_data(engine: "QSQLITE"); }
189 void sqlite_bindAndFetchUInt();
190
191 void sqlStatementUseIsNull_189093_data() { generic_data(); }
192 void sqlStatementUseIsNull_189093();
193
194 void sqlite_enable_cache_mode_data() { generic_data(engine: "QSQLITE"); }
195 void sqlite_enable_cache_mode();
196
197 void sqlite_enableRegexp_data() { generic_data(engine: "QSQLITE"); }
198 void sqlite_enableRegexp();
199
200 void sqlite_openError();
201
202 void sqlite_check_json1_data() { generic_data(engine: "QSQLITE"); }
203 void sqlite_check_json1();
204
205private:
206 void createTestTables(QSqlDatabase db);
207 void dropTestTables(QSqlDatabase db);
208 void populateTestTables(QSqlDatabase db);
209 void generic_data(const QString &engine=QString());
210
211 void testRecord(const FieldDef fieldDefs[], const QSqlRecord& inf, QSqlDatabase db);
212 void commonFieldTest(const FieldDef fieldDefs[], QSqlDatabase, const int);
213
214 tst_Databases dbs;
215};
216
217// number of records to be inserted per testfunction
218static const int ITERATION_COUNT = 2;
219
220//helper class for database specific tests
221struct FieldDef {
222 FieldDef(QString tn = QString(),
223 QVariant::Type t = QVariant::Invalid,
224 QVariant v = QVariant(),
225 bool nl = true):
226 typeName(tn), type(t), val(v), nullable(nl) {}
227
228 QString fieldName() const
229 {
230 QString rt = typeName;
231 rt.replace(re: QRegularExpression("\\s"), after: QString("_"));
232 int i = rt.indexOf(c: QLatin1Char('('));
233 if (i == -1)
234 i = rt.length();
235 if (i > 20)
236 i = 20;
237 return "t_" + rt.left(n: i);
238 }
239 QString typeName;
240 QVariant::Type type;
241 QVariant val;
242 bool nullable;
243};
244
245// creates a table out of the FieldDefs and returns the number of fields
246// excluding the primary key field
247static int createFieldTable(const FieldDef fieldDefs[], QSqlDatabase db)
248{
249 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
250 const QString tableName = qTableName(prefix: "qtestfields", __FILE__, db);
251 tst_Databases::safeDropTable(db, tableName);
252 QSqlQuery q(db);
253 // construct a create table statement consisting of all fieldtypes
254 QString qs = "create table " + tableName;
255 QString autoName = tst_Databases::autoFieldName(db);
256 if (tst_Databases::isMSAccess(db))
257 qs.append(s: " (id int not null");
258 else if (dbType == QSqlDriver::PostgreSQL)
259 qs.append(s: " (id serial not null");
260 else
261 qs.append(s: QString("(id integer not null %1 primary key").arg(a: autoName));
262
263 int i = 0;
264 for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) {
265 qs += QString(",\n %1 %2").arg(a: fieldDefs[ i ].fieldName()).arg(a: fieldDefs[ i ].typeName);
266 if ((dbType == QSqlDriver::Sybase || dbType == QSqlDriver::MSSqlServer) && fieldDefs[i].nullable)
267 qs += " null";
268 }
269
270 if (tst_Databases::isMSAccess(db))
271 qs.append(s: ",\n primary key (id)");
272
273 qs += ')';
274 if (!q.exec(query: qs)) {
275 qDebug() << "Creation of Table failed:" << tst_Databases::printError(err: q.lastError(), db);
276 qDebug() << "Query: " << qs;
277 return -1;
278 }
279 return i;
280}
281
282tst_QSqlDatabase::tst_QSqlDatabase()
283{
284}
285
286tst_QSqlDatabase::~tst_QSqlDatabase()
287{
288}
289
290void tst_QSqlDatabase::createTestTables(QSqlDatabase db)
291{
292 if (!db.isValid())
293 return;
294 const QString tableName = qTableName(prefix: "qtest", __FILE__, db);
295 QSqlQuery q(db);
296 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
297 if (dbType == QSqlDriver::MySqlServer) {
298 // ### stupid workaround until we find a way to hardcode this
299 // in the MySQL server startup script
300 q.exec(query: "set table_type=innodb");
301 } else if (dbType == QSqlDriver::MSSqlServer) {
302 QVERIFY_SQL(q, exec("SET ANSI_DEFAULTS ON"));
303 QVERIFY_SQL(q, exec("SET IMPLICIT_TRANSACTIONS OFF"));
304 } else if (dbType == QSqlDriver::PostgreSQL) {
305 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
306 }
307 // please never ever change this table; otherwise fix all tests ;)
308 if (tst_Databases::isMSAccess(db)) {
309 QVERIFY_SQL(q, exec("create table " + tableName +
310 " (id int not null, t_varchar varchar(40) not null, t_char char(40), "
311 "t_numeric number, primary key (id, t_varchar))"));
312 } else {
313 QVERIFY_SQL(q, exec("create table " + tableName +
314 " (id integer not null, t_varchar varchar(40) not null, "
315 "t_char char(40), t_numeric numeric(6, 3), primary key (id, t_varchar))"));
316 }
317 if (testWhiteSpaceNames(name: db.driverName())) {
318 QString qry = "create table " + qTableName(prefix: "qtest test", __FILE__, db)
319 + '('
320 + db.driver()->escapeIdentifier(identifier: QLatin1String("test test"), type: QSqlDriver::FieldName)
321 + " int not null primary key)";
322 QVERIFY_SQL(q, exec(qry));
323 }
324}
325
326void tst_QSqlDatabase::dropTestTables(QSqlDatabase db)
327{
328 if (!db.isValid())
329 return;
330
331 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
332 if (dbType == QSqlDriver::PostgreSQL) {
333 QSqlQuery q(db);
334 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
335 }
336
337 // drop the view first, otherwise we'll get dependency problems
338 tst_Databases::safeDropViews(db, viewNames: QStringList() << qTableName(prefix: "qtest_view", __FILE__, db) << qTableName(prefix: "qtest_view2", __FILE__, db));
339 const QString qtestTable = qTableName(prefix: "qtest", __FILE__, db);
340 QStringList tableNames;
341 tableNames << qtestTable
342 << qTableName(prefix: "qtest test", __FILE__, db)
343 << qTableName(prefix: "qtestfields", __FILE__, db)
344 << qTableName(prefix: "qtestalter", __FILE__, db)
345 << qTableName(prefix: "qtest_temp", __FILE__, db)
346 << qTableName(prefix: "qtest_bigint", __FILE__, db)
347 << qTableName(prefix: "qtest_xmltype", __FILE__, db)
348 << qTableName(prefix: "latin1table", __FILE__, db)
349 << qTableName(prefix: "qtest_sqlguid", __FILE__, db)
350 << qTableName(prefix: "batable", __FILE__, db)
351 << qTableName(prefix: "qtest_prec", __FILE__, db)
352 << qTableName(prefix: "uint", __FILE__, db)
353 << qTableName(prefix: "strings", __FILE__, db)
354 << qTableName(prefix: "numericfields", __FILE__, db)
355 << qTableName(prefix: "qtest_ibaseblobs", __FILE__, db)
356 << qTableName(prefix: "qtestBindBool", __FILE__, db)
357 << qTableName(prefix: "testqGetString", __FILE__, db)
358 << qTableName(prefix: "qtest_sqlguid", __FILE__, db)
359 << qTableName(prefix: "uint_table", __FILE__, db)
360 << qTableName(prefix: "uint_test", __FILE__, db)
361 << qTableName(prefix: "bug_249059", __FILE__, db)
362 << qTableName(prefix: "regexp_test", __FILE__, db);
363
364 QSqlQuery q(0, db);
365 if (dbType == QSqlDriver::PostgreSQL) {
366 q.exec(query: "drop schema " + qTableName(prefix: "qtestschema", __FILE__, db) + " cascade");
367 q.exec(query: "drop schema " + qTableName(prefix: "qtestScHeMa", __FILE__, db) + " cascade");
368 }
369
370 if (testWhiteSpaceNames(name: db.driverName())) {
371 tableNames << db.driver()->escapeIdentifier(identifier: qTableName(prefix: "qtest test", __FILE__, db),
372 type: QSqlDriver::TableName);
373 }
374
375 tst_Databases::safeDropTables(db, tableNames);
376
377 if (dbType == QSqlDriver::Oracle) {
378 q.exec(query: "drop user "+qTableName(prefix: "CREATOR", __FILE__, db)+ " cascade");
379 q.exec(query: "drop user "+qTableName(prefix: "APPUSER", __FILE__, db) + " cascade");
380 q.exec(query: "DROP TABLE sys."+qTableName(prefix: "mypassword", __FILE__, db));
381
382 }
383}
384
385void tst_QSqlDatabase::populateTestTables(QSqlDatabase db)
386{
387 if (!db.isValid())
388 return;
389 QSqlQuery q(db);
390 const QString qtest(qTableName(prefix: "qtest", __FILE__, db));
391
392 q.exec(query: "delete from " + qtest); //non-fatal
393 QVERIFY_SQL(q, exec("insert into " + qtest + " (id, t_varchar, t_char, t_numeric) values (0, 'VarChar0', 'Char0', 1.1)"));
394 QVERIFY_SQL(q, exec("insert into " + qtest + " (id, t_varchar, t_char, t_numeric) values (1, 'VarChar1', 'Char1', 2.2)"));
395 QVERIFY_SQL(q, exec("insert into " + qtest + " (id, t_varchar, t_char, t_numeric) values (2, 'VarChar2', 'Char2', 3.3)"));
396 QVERIFY_SQL(q, exec("insert into " + qtest + " (id, t_varchar, t_char, t_numeric) values (3, 'VarChar3', 'Char3', 4.4)"));
397 QVERIFY_SQL(q, exec("insert into " + qtest + " (id, t_varchar, t_char, t_numeric) values (4, 'VarChar4', NULL, NULL)"));
398}
399
400void tst_QSqlDatabase::initTestCase()
401{
402 qRegisterMetaType<QSqlDriver::NotificationSource>(typeName: "QSqlDriver::NotificationSource");
403 QVERIFY(dbs.open());
404
405 for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) {
406 QSqlDatabase db = QSqlDatabase::database(connectionName: (*it));
407 CHECK_DATABASE(db);
408 dropTestTables(db); //in case of leftovers
409 createTestTables(db);
410 populateTestTables(db);
411 }
412}
413
414void tst_QSqlDatabase::cleanupTestCase()
415{
416 for (QStringList::ConstIterator it = dbs.dbNames.begin(); it != dbs.dbNames.end(); ++it) {
417 QSqlDatabase db = QSqlDatabase::database(connectionName: (*it));
418 CHECK_DATABASE(db);
419 dropTestTables(db);
420 }
421
422 dbs.close();
423}
424
425void tst_QSqlDatabase::init()
426{
427}
428
429void tst_QSqlDatabase::cleanup()
430{
431}
432
433void tst_QSqlDatabase::generic_data(const QString& engine)
434{
435 if ( dbs.fillTestTable(driverPrefix: engine) == 0 ) {
436 if(engine.isEmpty())
437 QSKIP( "No database drivers are available in this Qt configuration");
438 else
439 QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit());
440 }
441}
442
443void tst_QSqlDatabase::addDatabase()
444{
445 QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlDatabase: BLAH_FOO_NONEXISTENT_DRIVER driver not loaded");
446 QTest::ignoreMessage(type: QtWarningMsg, qPrintable("QSqlDatabase: available drivers: " + QSqlDatabase::drivers().join(QLatin1Char(' '))));
447 {
448 QSqlDatabase db = QSqlDatabase::addDatabase(type: "BLAH_FOO_NONEXISTENT_DRIVER",
449 connectionName: "INVALID_CONNECTION");
450 QVERIFY(!db.isValid());
451 }
452 QVERIFY(QSqlDatabase::contains("INVALID_CONNECTION"));
453 QSqlDatabase::removeDatabase(connectionName: "INVALID_CONNECTION");
454 QVERIFY(!QSqlDatabase::contains("INVALID_CONNECTION"));
455}
456
457void tst_QSqlDatabase::errorReporting_data()
458{
459 QTest::addColumn<QString>(name: "driver");
460
461 QTest::newRow(dataTag: "QTDS") << QString::fromLatin1(str: "QTDS");
462 QTest::newRow(dataTag: "QTDS7") << QString::fromLatin1(str: "QTDS7");
463}
464
465void tst_QSqlDatabase::errorReporting()
466{
467 QFETCH(QString, driver);
468
469 if (!QSqlDatabase::drivers().contains(str: driver))
470 QSKIP(QString::fromLatin1("Database driver %1 not available").arg(driver).toLocal8Bit().constData());
471
472 const QString dbName = QLatin1String("errorReportingDb-") + driver;
473 QSqlDatabase db = QSqlDatabase::addDatabase(type: driver, connectionName: dbName);
474
475 db.setHostName(QLatin1String("127.0.0.1"));
476 db.setDatabaseName(QLatin1String("NonExistantDatabase"));
477 db.setUserName(QLatin1String("InvalidUser"));
478 db.setPassword(QLatin1String("IncorrectPassword"));
479
480 QVERIFY(!db.open());
481
482 db = QSqlDatabase();
483
484 QSqlDatabase::removeDatabase(connectionName: dbName);
485}
486
487void tst_QSqlDatabase::open()
488{
489 QFETCH(QString, dbName);
490 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
491 CHECK_DATABASE(db);
492
493 int i;
494 for (i = 0; i < 10; ++i) {
495 db.close();
496 QVERIFY(!db.isOpen());
497 QVERIFY_SQL(db, open());
498 QVERIFY(db.isOpen());
499 QVERIFY(!db.isOpenError());
500 }
501
502 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
503 if (dbType == QSqlDriver::SQLite && db.databaseName() == ":memory:") {
504 // tables in in-memory databases don't survive an open/close
505 createTestTables(db);
506 populateTestTables(db);
507 }
508}
509
510void tst_QSqlDatabase::tables()
511{
512 QFETCH(QString, dbName);
513 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
514 CHECK_DATABASE(db);
515 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
516
517 const auto qtest(qTableName(prefix: "qtest", __FILE__, db, escape: false)),
518 qtest_view(qTableName(prefix: "qtest_view", __FILE__, db, escape: false)),
519 temp_tab(qTableName(prefix: "test_tab", __FILE__, db, escape: false)),
520 qtestspace(qTableName(prefix: "qtest test", __FILE__, db, escape: false));
521
522 bool views = true;
523 bool tempTables = false;
524
525 QSqlQuery q(db);
526 if (!q.exec(query: "CREATE VIEW " + qtest_view + " as select * from " + qtest)) {
527 qDebug(msg: "DBMS '%s' cannot handle VIEWs: %s",
528 qPrintable(tst_Databases::dbToString(db)),
529 qPrintable(tst_Databases::printError(q.lastError())));
530 views = false;
531 }
532
533 if (db.driverName().startsWith(s: "QSQLITE3")) {
534 QVERIFY_SQL(q, exec("CREATE TEMPORARY TABLE " + temp_tab + " (id int)"));
535 tempTables = true;
536 }
537
538 QStringList tables = db.tables(type: QSql::Tables);
539 QVERIFY(tables.contains(qtest, Qt::CaseInsensitive));
540 QVERIFY(!tables.contains("sql_features", Qt::CaseInsensitive)); //check for postgres 7.4 internal tables
541 if (views) {
542 QVERIFY(!tables.contains(qtest_view, Qt::CaseInsensitive));
543 }
544 if (tempTables)
545 QVERIFY(tables.contains(temp_tab, Qt::CaseInsensitive));
546
547 tables = db.tables(type: QSql::Views);
548 if (views) {
549 if(!tables.contains(str: qtest_view, cs: Qt::CaseInsensitive))
550 qDebug() << "failed to find" << qtest_view << "in" << tables;
551 QVERIFY(tables.contains(qtest_view, Qt::CaseInsensitive));
552 }
553 if (tempTables)
554 QVERIFY(!tables.contains(temp_tab, Qt::CaseInsensitive));
555 QVERIFY(!tables.contains(qtest, Qt::CaseInsensitive));
556
557 tables = db.tables(type: QSql::SystemTables);
558 QVERIFY(!tables.contains(qtest, Qt::CaseInsensitive));
559 QVERIFY(!tables.contains(qtest_view, Qt::CaseInsensitive));
560 QVERIFY(!tables.contains(temp_tab, Qt::CaseInsensitive));
561
562 tables = db.tables(type: QSql::AllTables);
563 if (views)
564 QVERIFY(tables.contains(qtest_view, Qt::CaseInsensitive));
565 if (tempTables)
566 QVERIFY(tables.contains(temp_tab, Qt::CaseInsensitive));
567 QVERIFY(tables.contains(qtest, Qt::CaseInsensitive));
568
569 if (dbType == QSqlDriver::PostgreSQL)
570 QVERIFY(tables.contains(qtestspace));
571}
572
573void tst_QSqlDatabase::whitespaceInIdentifiers()
574{
575 QFETCH(QString, dbName);
576 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
577 CHECK_DATABASE(db);
578 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
579
580 if (testWhiteSpaceNames(name: db.driverName())) {
581 const auto tableName(qTableName(prefix: "qtest test", __FILE__, db, escape: false));
582 QVERIFY(db.tables().contains(tableName, Qt::CaseInsensitive));
583
584 QSqlRecord rec = db.record(tablename: tableName);
585 QCOMPARE(rec.count(), 1);
586 QCOMPARE(rec.fieldName(0), QString("test test"));
587 if (dbType == QSqlDriver::Oracle)
588 QCOMPARE(rec.field(0).type(), QVariant::Double);
589 else
590 QCOMPARE(rec.field(0).type(), QVariant::Int);
591
592 QSqlIndex idx = db.primaryIndex(tablename: tableName);
593 QCOMPARE(idx.count(), 1);
594 QCOMPARE(idx.fieldName(0), QString("test test"));
595 if (dbType == QSqlDriver::Oracle)
596 QCOMPARE(idx.field(0).type(), QVariant::Double);
597 else
598 QCOMPARE(idx.field(0).type(), QVariant::Int);
599 } else {
600 QSKIP("DBMS does not support whitespaces in identifiers");
601 }
602}
603
604void tst_QSqlDatabase::alterTable()
605{
606 QFETCH(QString, dbName);
607 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
608 CHECK_DATABASE(db);
609 const QString qtestalter(qTableName(prefix: "qtestalter", __FILE__, db));
610 const auto noEscapeAlterTable = qTableName(prefix: "qtestalter", __FILE__, db, escape: false);
611
612 QSqlQuery q(db);
613
614 QVERIFY_SQL(q, exec("create table " + qtestalter + " (F1 char(20), F2 char(20), F3 char(20))"));
615 QSqlRecord rec = db.record(tablename: noEscapeAlterTable);
616 QCOMPARE((int)rec.count(), 3);
617
618 int i;
619 for (i = 0; i < 3; ++i) {
620 QCOMPARE(rec.field(i).name().toUpper(), QString("F%1").arg(i + 1));
621 }
622
623 if (!q.exec(query: "alter table " + qtestalter + " drop column F2")) {
624 QSKIP("DBMS doesn't support dropping columns in ALTER TABLE statement");
625 }
626
627 rec = db.record(tablename: noEscapeAlterTable);
628
629 QCOMPARE((int)rec.count(), 2);
630
631 QCOMPARE(rec.field(0).name().toUpper(), QString("F1"));
632 QCOMPARE(rec.field(1).name().toUpper(), QString("F3"));
633
634 q.exec(query: "select * from " + qtestalter);
635}
636
637#if 0
638// this is the general test that should work on all databases.
639// unfortunately no DBMS supports SQL 92/ 99 so the general
640// test is more or less a joke. Please write a test for each
641// database plugin (see recordOCI and so on). Use this test
642// as a template.
643void tst_QSqlDatabase::record()
644{
645 QFETCH(QString, dbName);
646 QSqlDatabase db = QSqlDatabase::database(dbName);
647 CHECK_DATABASE(db);
648
649 static const FieldDef fieldDefs[] = {
650 FieldDef("char(20)", QVariant::String, QString("blah1"), false),
651 FieldDef("varchar(20)", QVariant::String, QString("blah2"), false),
652 FieldDef()
653 };
654
655 const int fieldCount = createFieldTable(fieldDefs, db);
656 QVERIFY(fieldCount > 0);
657
658 commonFieldTest(fieldDefs, db, fieldCount);
659}
660#endif
661
662void tst_QSqlDatabase::testRecord(const FieldDef fieldDefs[], const QSqlRecord& inf, QSqlDatabase db)
663{
664 int i = 0;
665 if (!tst_Databases::autoFieldName(db).isEmpty()) // Currently only MySQL is tested
666 QVERIFY2(inf.field(i).isAutoValue(), qPrintable(inf.field(i).name() + " should be reporting as an autovalue"));
667 for (i = 0; !fieldDefs[ i ].typeName.isNull(); ++i) {
668 QCOMPARE(inf.field(i+1).name().toUpper(), fieldDefs[ i ].fieldName().toUpper());
669 if (inf.field(i: i+1).type() != fieldDefs[ i ].type) {
670 QFAIL(qPrintable(QString(" Expected: '%1' Received: '%2' for field %3 in testRecord").arg(
671 QVariant::typeToName(fieldDefs[ i ].type)).arg(
672 QVariant::typeToName(inf.field(i+1).type())).arg(
673 fieldDefs[ i ].fieldName())));
674 }
675 QVERIFY(!inf.field(i+1).isAutoValue());
676
677// 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())));
678 }
679}
680
681// non-dbms specific tests
682void tst_QSqlDatabase::commonFieldTest(const FieldDef fieldDefs[], QSqlDatabase db, const int fieldCount)
683{
684 CHECK_DATABASE(db);
685 const QStringList tableNames = { qTableName(prefix: "qtestfields", __FILE__, db),
686 qTableName(prefix: "qtestfields", __FILE__, db, escape: false) };
687 for (const QString table : tableNames) {
688 QSqlRecord rec = db.record(tablename: table);
689 QCOMPARE(rec.count(), fieldCount + 1);
690 testRecord(fieldDefs, inf: rec, db);
691 }
692 QSqlQuery q(db);
693 // Only check the escaped entry
694 QVERIFY_SQL(q, exec("select * from " + tableNames.at(0)));
695}
696
697void tst_QSqlDatabase::recordTDS()
698{
699 QFETCH(QString, dbName);
700 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
701 CHECK_DATABASE(db);
702
703 static const FieldDef fieldDefs[] = {
704 FieldDef("tinyint", QVariant::Int, 255),
705 FieldDef("smallint", QVariant::Int, 32767),
706 FieldDef("int", QVariant::Int, 2147483647),
707 FieldDef("numeric(10,9)", QVariant::Double, 1.23456789),
708 FieldDef("decimal(10,9)", QVariant::Double, 1.23456789),
709 FieldDef("float(4)", QVariant::Double, 1.23456789),
710 FieldDef("double precision", QVariant::Double, 1.23456789),
711 FieldDef("real", QVariant::Double, 1.23456789),
712 FieldDef("smallmoney", QVariant::Double, 100.42),
713 FieldDef("money", QVariant::Double, 200.42),
714 // accuracy is that of a minute
715 FieldDef("smalldatetime", QVariant::DateTime, QDateTime(QDate::currentDate(), QTime(1, 2, 0, 0))),
716 // accuracy is that of a second
717 FieldDef("datetime", QVariant::DateTime, QDateTime(QDate::currentDate(), QTime(1, 2, 3, 0))),
718 FieldDef("char(20)", QVariant::String, "blah1"),
719 FieldDef("varchar(20)", QVariant::String, "blah2"),
720 FieldDef("nchar(20)", QVariant::String, "blah3"),
721 FieldDef("nvarchar(20)", QVariant::String, "blah4"),
722 FieldDef("text", QVariant::String, "blah5"),
723 FieldDef("bit", QVariant::Int, 1, false),
724
725 FieldDef()
726 };
727
728 const int fieldCount = createFieldTable(fieldDefs, db);
729 QVERIFY(fieldCount > 0);
730
731 commonFieldTest(fieldDefs, db, fieldCount);
732}
733
734void tst_QSqlDatabase::recordOCI()
735{
736 bool hasTimeStamp = false;
737
738 QFETCH(QString, dbName);
739 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
740 CHECK_DATABASE(db);
741
742 // runtime check for Oracle version since V8 doesn't support TIMESTAMPs
743 if (tst_Databases::getOraVersion(db) >= 9)
744 hasTimeStamp = true;
745
746 FieldDef tsdef;
747 FieldDef tstzdef;
748 FieldDef tsltzdef;
749 FieldDef intytm;
750 FieldDef intdts;
751
752 static const QDateTime dt(QDate::currentDate(), QTime(1, 2, 3, 0));
753
754 if (hasTimeStamp) {
755 tsdef = FieldDef("timestamp", QVariant::DateTime, dt);
756 tstzdef = FieldDef("timestamp with time zone", QVariant::DateTime, dt);
757 tsltzdef = FieldDef("timestamp with local time zone", QVariant::DateTime, dt);
758 intytm = FieldDef("interval year to month", QVariant::String, QString("+01-01"));
759 intdts = FieldDef("interval day to second", QVariant::String, QString("+01 00:00:01.000000"));
760 }
761
762 const FieldDef fieldDefs[] = {
763 FieldDef("char(20)", QVariant::String, QString("blah1")),
764 FieldDef("varchar(20)", QVariant::String, QString("blah2")),
765 FieldDef("nchar(20)", QVariant::String, QString("blah3")),
766 FieldDef("nvarchar2(20)", QVariant::String, QString("blah4")),
767 FieldDef("number(10,5)", QVariant::Double, 1.1234567),
768 FieldDef("date", QVariant::DateTime, dt),
769 FieldDef("long raw", QVariant::ByteArray, QByteArray("blah5")),
770 FieldDef("raw(2000)", QVariant::ByteArray, QByteArray("blah6"), false),
771 FieldDef("blob", QVariant::ByteArray, QByteArray("blah7")),
772 FieldDef("clob", QVariant::ByteArray, QByteArray("blah8")),
773 FieldDef("nclob", QVariant::ByteArray, QByteArray("blah9")),
774// FieldDef("bfile", QVariant::ByteArray, QByteArray("blah10")),
775
776 intytm,
777 intdts,
778 tsdef,
779 tstzdef,
780 tsltzdef,
781 FieldDef()
782 };
783
784 const int fieldCount = createFieldTable(fieldDefs, db);
785 QVERIFY(fieldCount > 0);
786
787 commonFieldTest(fieldDefs, db, fieldCount);
788
789 // some additional tests
790 const QString tableName = qTableName(prefix: "qtestfields", __FILE__, db);
791 QSqlRecord rec = db.record(tablename: tableName);
792 QCOMPARE(rec.field("T_NUMBER").length(), 10);
793 QCOMPARE(rec.field("T_NUMBER").precision(), 5);
794
795 QSqlQuery q(db);
796 QVERIFY_SQL(q, exec("SELECT * FROM " + tableName));
797 rec = q.record();
798 QCOMPARE(rec.field("T_NUMBER").length(), 10);
799 QCOMPARE(rec.field("T_NUMBER").precision(), 5);
800}
801
802void tst_QSqlDatabase::recordPSQL()
803{
804 QFETCH(QString, dbName);
805 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
806 CHECK_DATABASE(db);
807
808 FieldDef byteadef;
809 if (db.driver()->hasFeature(f: QSqlDriver::BLOB))
810 byteadef = FieldDef("bytea", QVariant::ByteArray, QByteArray("bl\\ah"));
811 static FieldDef fieldDefs[] = {
812 FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)),
813 FieldDef("bigserial", QVariant::LongLong, 100, false),
814 FieldDef("bit", QVariant::String, "1"), // a bit in postgres is a bit-string
815 FieldDef("box", QVariant::String, "(5,6),(1,2)"),
816 FieldDef("char(20)", QVariant::String, "blah5678901234567890"),
817 FieldDef("varchar(20)", QVariant::String, "blah5678901234567890"),
818 FieldDef("cidr", QVariant::String, "12.123.0.0/24"),
819 FieldDef("circle", QVariant::String, "<(1,2),3>"),
820 FieldDef("date", QVariant::Date, QDate::currentDate()),
821 FieldDef("float8", QVariant::Double, 1.12345678912),
822 FieldDef("inet", QVariant::String, "12.123.12.23"),
823 FieldDef("integer", QVariant::Int, 2147483647),
824 FieldDef("interval", QVariant::String, "1 day 12:59:10"),
825// LOL... you can create a "line" datatype in PostgreSQL <= 7.2.x but
826// as soon as you want to insert data you get a "not implemented yet" error
827// FieldDef("line", QVariant::Polygon, QPolygon(QRect(1, 2, 3, 4))),
828 FieldDef("lseg", QVariant::String, "[(1,1),(2,2)]"),
829 FieldDef("macaddr", QVariant::String, "08:00:2b:01:02:03"),
830 FieldDef("money", QVariant::String, "$12.23"),
831 FieldDef("numeric", QVariant::Double, 1.2345678912),
832 FieldDef("path", QVariant::String, "((1,2),(3,2),(3,5),(1,5))"),
833 FieldDef("point", QVariant::String, "(1,2)"),
834 FieldDef("polygon", QVariant::String, "((1,2),(3,2),(3,5),(1,5))"),
835 FieldDef("real", QVariant::Double, 1.1234),
836 FieldDef("smallint", QVariant::Int, 32767),
837 FieldDef("serial", QVariant::Int, 100, false),
838 FieldDef("text", QVariant::String, "blah"),
839 FieldDef("time(6)", QVariant::Time, QTime(1, 2, 3)),
840 FieldDef("timetz", QVariant::Time, QTime(1, 2, 3)),
841 FieldDef("timestamp(6)", QVariant::DateTime, QDateTime::currentDateTime()),
842 FieldDef("timestamptz", QVariant::DateTime, QDateTime::currentDateTime()),
843 byteadef,
844
845 FieldDef()
846 };
847
848 QSqlQuery q(db);
849
850 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
851 if (dbType == QSqlDriver::PostgreSQL)
852 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
853 q.exec(query: "drop sequence " + qTableName(prefix: "qtestfields_t_bigserial_seq", __FILE__, db));
854 q.exec(query: "drop sequence " + qTableName(prefix: "qtestfields_t_serial_seq", __FILE__, db));
855
856 const int fieldCount = createFieldTable(fieldDefs, db);
857 QVERIFY(fieldCount > 0);
858
859 commonFieldTest(fieldDefs, db, fieldCount);
860 for (int i = 0; i < ITERATION_COUNT; ++i) {
861 // increase serial values
862 for (int i2 = 0; !fieldDefs[ i2 ].typeName.isNull(); ++i2) {
863 if (fieldDefs[ i2 ].typeName == "serial" ||
864 fieldDefs[ i2 ].typeName == "bigserial") {
865
866 FieldDef def = fieldDefs[ i2 ];
867 def.val = def.val.toInt() + 1;
868 fieldDefs[ i2 ] = def;
869 }
870 }
871 }
872}
873
874void tst_QSqlDatabase::recordMySQL()
875{
876 QFETCH(QString, dbName);
877 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
878 CHECK_DATABASE(db);
879
880 FieldDef bin10, varbin10;
881 int major = tst_Databases::getMySqlVersion( db ).section( asep: QChar('.'), astart: 0, aend: 0 ).toInt();
882 int minor = tst_Databases::getMySqlVersion( db ).section( asep: QChar('.'), astart: 1, aend: 1 ).toInt();
883 int revision = tst_Databases::getMySqlVersion( db ).section( asep: QChar('.'), astart: 2, aend: 2 ).toInt();
884 int vernum = (major << 16) + (minor << 8) + revision;
885
886 /* The below is broken in mysql below 5.0.15
887 see http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html
888 specifically: Before MySQL 5.0.15, the pad value is space. Values are right-padded
889 with space on insert, and trailing spaces are removed on select.
890 */
891 if( vernum >= ((5 << 16) + 15) ) {
892 bin10 = FieldDef("binary(10)", QVariant::ByteArray, QString("123abc "));
893 varbin10 = FieldDef("varbinary(10)", QVariant::ByteArray, QString("123abcv "));
894 }
895
896 static QDateTime dt(QDate::currentDate(), QTime(1, 2, 3, 0));
897 static const FieldDef fieldDefs[] = {
898 FieldDef("tinyint", static_cast<QVariant::Type>(QMetaType::Char), 127),
899 FieldDef("tinyint unsigned", static_cast<QVariant::Type>(QMetaType::UChar), 255),
900 FieldDef("smallint", static_cast<QVariant::Type>(QMetaType::Short), 32767),
901 FieldDef("smallint unsigned", static_cast<QVariant::Type>(QMetaType::UShort), 65535),
902 FieldDef("mediumint", QVariant::Int, 8388607),
903 FieldDef("mediumint unsigned", QVariant::UInt, 16777215),
904 FieldDef("integer", QVariant::Int, 2147483647),
905 FieldDef("integer unsigned", QVariant::UInt, 4294967295u),
906 FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)),
907 FieldDef("bigint unsigned", QVariant::ULongLong, Q_UINT64_C(18446744073709551615)),
908 FieldDef("float", QVariant::Double, 1.12345),
909 FieldDef("double", QVariant::Double, 1.123456789),
910 FieldDef("decimal(10, 9)", QVariant::Double, 1.123456789),
911 FieldDef("numeric(5, 2)", QVariant::Double, 123.67),
912 FieldDef("date", QVariant::Date, QDate::currentDate()),
913 FieldDef("datetime", QVariant::DateTime, dt),
914 FieldDef("timestamp", QVariant::DateTime, dt, false),
915 FieldDef("time", QVariant::String, dt.time()),
916 FieldDef("year", QVariant::Int, 2003),
917 FieldDef("char(20)", QVariant::String, "Blah"),
918 FieldDef("varchar(20)", QVariant::String, "BlahBlah"),
919 FieldDef("tinytext", QVariant::String, QString("blah5")),
920 FieldDef("text", QVariant::String, QString("blah6")),
921 FieldDef("mediumtext", QVariant::String, QString("blah7")),
922 FieldDef("longtext", QVariant::String, QString("blah8")),
923 // SET OF?
924
925 FieldDef()
926 };
927
928 const int fieldCount = createFieldTable(fieldDefs, db);
929 QVERIFY(fieldCount > 0);
930
931 commonFieldTest(fieldDefs, db, fieldCount);
932
933 QSqlQuery q(db);
934 QVERIFY_SQL(q, exec("SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)"));
935 QVERIFY(q.next());
936 QCOMPARE(q.value(0).toDateTime().date(), QDate::currentDate().addDays(-2));
937}
938
939void tst_QSqlDatabase::recordDB2()
940{
941 QFETCH(QString, dbName);
942 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
943 CHECK_DATABASE(db);
944
945 static const FieldDef fieldDefs[] = {
946 FieldDef("char(20)", QVariant::String, QString("Blah1")),
947 FieldDef("varchar(20)", QVariant::String, QString("Blah2")),
948 FieldDef("long varchar", QVariant::String, QString("Blah3")),
949 // using BOOLEAN results in "SQL0486N The BOOLEAN data type is currently only supported internally."
950//X FieldDef("boolean" , QVariant::Bool, QVariant(true, 1)),
951 FieldDef("smallint", QVariant::Int, 32767),
952 FieldDef("integer", QVariant::Int, 2147483647),
953 FieldDef("bigint", QVariant::LongLong, Q_INT64_C(9223372036854775807)),
954 FieldDef("real", QVariant::Double, 1.12345),
955 FieldDef("double", QVariant::Double, 1.23456789),
956 FieldDef("float", QVariant::Double, 1.23456789),
957 FieldDef("decimal(10,9)", QVariant::Double, 1.234567891),
958 FieldDef("numeric(10,9)", QVariant::Double, 1.234567891),
959 FieldDef("date", QVariant::Date, QDate::currentDate()),
960 FieldDef("time", QVariant::Time, QTime(1, 2, 3)),
961 FieldDef("timestamp", QVariant::DateTime, QDateTime::currentDateTime()),
962// FieldDef("graphic(20)", QVariant::String, QString("Blah4")),
963// FieldDef("vargraphic(20)", QVariant::String, QString("Blah5")),
964// FieldDef("long vargraphic", QVariant::String, QString("Blah6")),
965 //X FieldDef("datalink", QVariant::String, QString("DLVALUE('Blah10')")),
966 FieldDef()
967 };
968
969 const int fieldCount = createFieldTable(fieldDefs, db);
970 QVERIFY(fieldCount > 0);
971
972 commonFieldTest(fieldDefs, db, fieldCount);
973}
974
975void tst_QSqlDatabase::recordIBase()
976{
977 QFETCH(QString, dbName);
978 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
979 CHECK_DATABASE(db);
980
981 static const FieldDef fieldDefs[] = {
982 FieldDef("char(20)", QVariant::String, QString("Blah1"), false),
983 FieldDef("varchar(20)", QVariant::String, QString("Blah2")),
984 FieldDef("smallint", QVariant::Int, 32767),
985 FieldDef("float", QVariant::Double, 1.2345),
986 FieldDef("double precision", QVariant::Double, 1.2345678),
987 FieldDef("timestamp", QVariant::DateTime, QDateTime::currentDateTime()),
988 FieldDef("time", QVariant::Time, QTime::currentTime()),
989 FieldDef("decimal(18)", QVariant::LongLong, Q_INT64_C(9223372036854775807)),
990 FieldDef("numeric(5,2)", QVariant::Double, 123.45),
991
992 FieldDef()
993 };
994
995 const int fieldCount = createFieldTable(fieldDefs, db);
996 QVERIFY(fieldCount > 0);
997
998 commonFieldTest(fieldDefs, db, fieldCount);
999}
1000
1001void tst_QSqlDatabase::recordSQLite()
1002{
1003 QFETCH(QString, dbName);
1004 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1005 CHECK_DATABASE(db);
1006
1007 static const FieldDef fieldDefs[] = {
1008 // The affinity of these fields are TEXT so SQLite should give us strings, not ints or doubles.
1009 FieldDef("char(20)", QVariant::String, QString("123")),
1010 FieldDef("varchar(20)", QVariant::String, QString("123.4")),
1011 FieldDef("clob", QVariant::String, QString("123.45")),
1012 FieldDef("text", QVariant::String, QString("123.456")),
1013
1014 FieldDef("integer", QVariant::Int, QVariant(13)),
1015 FieldDef("int", QVariant::Int, QVariant(12)),
1016 FieldDef("real", QVariant::Double, QVariant(1.234567890123456)),
1017
1018 FieldDef()
1019 };
1020
1021 const int fieldCount = createFieldTable(fieldDefs, db);
1022 QVERIFY(fieldCount > 0);
1023
1024 commonFieldTest(fieldDefs, db, fieldCount);
1025}
1026
1027void tst_QSqlDatabase::recordSQLServer()
1028{
1029 QFETCH(QString, dbName);
1030 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1031 CHECK_DATABASE(db);
1032
1033 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1034 if (dbType != QSqlDriver::MSSqlServer)
1035 QSKIP("SQL server specific test");
1036
1037 // ### TODO: Add the rest of the fields
1038 static const FieldDef fieldDefs[] = {
1039 FieldDef("varchar(20)", QVariant::String, QString("Blah1")),
1040 FieldDef("bigint", QVariant::LongLong, 12345),
1041 FieldDef("int", QVariant::Int, 123456),
1042 FieldDef("tinyint", QVariant::UInt, 255),
1043 FieldDef("float", QVariant::Double, 1.12345),
1044 FieldDef("numeric(5,2)", QVariant::Double, 123.45),
1045 FieldDef("uniqueidentifier", QVariant::String,
1046 QString("AA7DF450-F119-11CD-8465-00AA00425D90")),
1047
1048 FieldDef()
1049 };
1050
1051 const int fieldCount = createFieldTable(fieldDefs, db);
1052 QVERIFY(fieldCount > 0);
1053
1054 commonFieldTest(fieldDefs, db, fieldCount);
1055}
1056
1057void tst_QSqlDatabase::recordAccess()
1058{
1059 QFETCH(QString, dbName);
1060 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1061 CHECK_DATABASE(db);
1062
1063 if (!tst_Databases::isMSAccess(db))
1064 QSKIP("MS Access specific test");
1065
1066 QString memo;
1067 for (int i = 0; i < 32; i++)
1068 memo.append(s: "ABCDEFGH12345678abcdefgh12345678");
1069
1070 // ### TODO: Add the rest of the fields
1071 static const FieldDef fieldDefs[] = {
1072 FieldDef("varchar(20)", QVariant::String, QString("Blah1")),
1073 FieldDef("single", QVariant::Double, 1.12345),
1074 FieldDef("double", QVariant::Double, 1.123456),
1075 FieldDef("byte", QVariant::UInt, 255),
1076 FieldDef("long", QVariant::Int, 2147483647),
1077 FieldDef("memo", QVariant::String, memo),
1078 FieldDef()
1079 };
1080
1081 const int fieldCount = createFieldTable(fieldDefs, db);
1082 QVERIFY(fieldCount > 0);
1083
1084 commonFieldTest(fieldDefs, db, fieldCount);
1085}
1086
1087void tst_QSqlDatabase::transaction()
1088{
1089 QFETCH(QString, dbName);
1090 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1091 CHECK_DATABASE(db);
1092 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1093 const QString qtest(qTableName(prefix: "qtest", __FILE__, db));
1094
1095 if (!db.driver()->hasFeature(f: QSqlDriver::Transactions))
1096 QSKIP("DBMS not transaction capable");
1097
1098 QVERIFY(db.transaction());
1099
1100 QSqlQuery q(db);
1101 QVERIFY_SQL(q, exec("insert into " + qtest + " values (40, 'VarChar40', 'Char40', 40.40)"));
1102 QVERIFY_SQL(q, exec("select * from " + qtest + " where id = 40"));
1103 QVERIFY(q.next());
1104 QCOMPARE(q.value(0).toInt(), 40);
1105 q.clear();
1106
1107 QVERIFY(db.commit());
1108
1109 QVERIFY(db.transaction());
1110 QVERIFY_SQL(q, exec("select * from " + qtest + " where id = 40"));
1111 QVERIFY(q.next());
1112 QCOMPARE(q.value(0).toInt(), 40);
1113 q.clear();
1114 QVERIFY(db.commit());
1115
1116 QVERIFY(db.transaction());
1117 QVERIFY_SQL(q, exec("insert into " + qtest + " values (41, 'VarChar41', 'Char41', 41.41)"));
1118 QVERIFY_SQL(q, exec("select * from " + qtest + " where id = 41"));
1119 QVERIFY(q.next());
1120 QCOMPARE(q.value(0).toInt(), 41);
1121 q.clear(); // for SQLite which does not allow any references on rows that shall be rolled back
1122 if (!db.rollback()) {
1123 if (dbType == QSqlDriver::MySqlServer)
1124 QSKIP("MySQL transaction failed: " + tst_Databases::printError(db.lastError()));
1125 else
1126 QFAIL("Could not rollback transaction: " + tst_Databases::printError(db.lastError()));
1127 }
1128
1129 QVERIFY_SQL(q, exec("select * from " + qtest + " where id = 41"));
1130 QVERIFY(!q.next());
1131
1132 populateTestTables(db);
1133}
1134
1135void tst_QSqlDatabase::bigIntField()
1136{
1137 QFETCH(QString, dbName);
1138 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1139 CHECK_DATABASE(db);
1140 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1141 const QString qtest_bigint(qTableName(prefix: "qtest_bigint", __FILE__, db));
1142
1143 QSqlQuery q(db);
1144 q.setForwardOnly(true);
1145
1146 if (dbType == QSqlDriver::Oracle)
1147 q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64);
1148
1149 if (dbType == QSqlDriver::MySqlServer) {
1150 QVERIFY_SQL(q, exec("create table " + qtest_bigint + " (id int, t_s64bit bigint, t_u64bit bigint unsigned)"));
1151 } else if (dbType == QSqlDriver::PostgreSQL
1152 || dbType == QSqlDriver::DB2
1153 || dbType == QSqlDriver::MSSqlServer) {
1154 QVERIFY_SQL(q, exec("create table " + qtest_bigint + "(id int, t_s64bit bigint, t_u64bit bigint)"));
1155 } else if (dbType == QSqlDriver::Oracle) {
1156 QVERIFY_SQL(q, exec("create table " + qtest_bigint + " (id int, t_s64bit int, t_u64bit int)"));
1157 //} else if (dbType == QSqlDriver::Interbase) {
1158 // QVERIFY_SQL(q, exec("create table " + qtest_bigint + " (id int, t_s64bit int64, t_u64bit int64)"));
1159 } else {
1160 QSKIP("no 64 bit integer support");
1161 }
1162 QVERIFY(q.prepare("insert into " + qtest_bigint + " values (?, ?, ?)"));
1163 qlonglong ll = Q_INT64_C(9223372036854775807);
1164 qulonglong ull = Q_UINT64_C(18446744073709551615);
1165
1166 if (dbType == QSqlDriver::MySqlServer || dbType == QSqlDriver::Oracle) {
1167 q.bindValue(pos: 0, val: 0);
1168 q.bindValue(pos: 1, val: ll);
1169 q.bindValue(pos: 2, val: ull);
1170 QVERIFY_SQL(q, exec());
1171 q.bindValue(pos: 0, val: 1);
1172 q.bindValue(pos: 1, val: -ll);
1173 q.bindValue(pos: 2, val: ull);
1174 QVERIFY_SQL(q, exec());
1175 } else {
1176 // usinged bigint fields not supported - a cast is necessary
1177 q.bindValue(pos: 0, val: 0);
1178 q.bindValue(pos: 1, val: ll);
1179 q.bindValue(pos: 2, val: (qlonglong) ull);
1180 QVERIFY_SQL(q, exec());
1181 q.bindValue(pos: 0, val: 1);
1182 q.bindValue(pos: 1, val: -ll);
1183 q.bindValue(pos: 2, val: (qlonglong) ull);
1184 QVERIFY_SQL(q, exec());
1185 }
1186 QVERIFY(q.exec("select * from " + qtest_bigint + " order by id"));
1187 QVERIFY(q.next());
1188 QCOMPARE(q.value(1).toDouble(), (double)ll);
1189 QCOMPARE(q.value(1).toLongLong(), ll);
1190 if (dbType == QSqlDriver::Oracle)
1191 QEXPECT_FAIL("", "Oracle driver lacks support for unsigned int64 types", Continue);
1192 QCOMPARE(q.value(2).toULongLong(), ull);
1193 QVERIFY(q.next());
1194 QCOMPARE(q.value(1).toLongLong(), -ll);
1195 if (dbType == QSqlDriver::Oracle)
1196 QEXPECT_FAIL("", "Oracle driver lacks support for unsigned int64 types", Continue);
1197 QCOMPARE(q.value(2).toULongLong(), ull);
1198}
1199
1200void tst_QSqlDatabase::caseSensivity()
1201{
1202 QFETCH(QString, dbName);
1203 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1204 CHECK_DATABASE(db);
1205 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1206
1207 bool cs = false;
1208 if (dbType == QSqlDriver::MySqlServer || dbType == QSqlDriver::SQLite
1209 || dbType == QSqlDriver::Sybase || dbType == QSqlDriver::PostgreSQL
1210 || dbType == QSqlDriver::MSSqlServer || db.driverName().startsWith(s: "QODBC"))
1211 cs = true;
1212
1213 QSqlRecord rec = db.record(tablename: qTableName(prefix: "qtest", __FILE__, db, escape: false));
1214 QVERIFY((int)rec.count() > 0);
1215 if (!cs) {
1216 rec = db.record(tablename: qTableName(prefix: "QTEST", __FILE__, db, escape: false).toUpper());
1217 QVERIFY((int)rec.count() > 0);
1218 rec = db.record(tablename: qTableName(prefix: "qTesT", __FILE__, db, escape: false));
1219 QVERIFY((int)rec.count() > 0);
1220 }
1221
1222 rec = db.primaryIndex(tablename: qTableName(prefix: "qtest", __FILE__, db, escape: false));
1223 QVERIFY((int)rec.count() > 0);
1224 if (!cs) {
1225 rec = db.primaryIndex(tablename: qTableName(prefix: "QTEST", __FILE__, db, escape: false).toUpper());
1226 QVERIFY((int)rec.count() > 0);
1227 rec = db.primaryIndex(tablename: qTableName(prefix: "qTesT", __FILE__, db, escape: false));
1228 QVERIFY((int)rec.count() > 0);
1229 }
1230
1231 // Explicit test for case sensitive table creation without quoting
1232 QSqlQuery qry(db);
1233 const auto noQuotesTable = qTableName(prefix: "NoQuotes", __FILE__, db, escape: false);
1234 tst_Databases::safeDropTable(db, tableName: noQuotesTable);
1235 QVERIFY_SQL(qry, exec("CREATE TABLE " + noQuotesTable + " (id INTEGER)"));
1236 QVERIFY_SQL(qry, exec("INSERT INTO " + noQuotesTable + " VALUES(1)"));
1237 QVERIFY_SQL(qry, exec("SELECT * FROM " + noQuotesTable));
1238 QVERIFY_SQL(qry, next());
1239 QCOMPARE(qry.value(0).toInt(), 1);
1240 rec = db.record(tablename: cs ? noQuotesTable.toLower() : noQuotesTable);
1241 QVERIFY(rec.count() > 0);
1242}
1243
1244void tst_QSqlDatabase::noEscapedFieldNamesInRecord()
1245{
1246 QFETCH(QString, dbName);
1247 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1248 CHECK_DATABASE(db);
1249
1250 QString fieldname("t_varchar");
1251 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1252 if (dbType == QSqlDriver::Oracle || dbType == QSqlDriver::Interbase || dbType == QSqlDriver::DB2)
1253 fieldname = fieldname.toUpper();
1254
1255 QSqlQuery q(db);
1256 QString query = "SELECT " + db.driver()->escapeIdentifier(identifier: fieldname, type: QSqlDriver::FieldName) + " FROM " + qTableName(prefix: "qtest", __FILE__, db);
1257 QVERIFY_SQL(q, exec(query));
1258 QCOMPARE(q.record().fieldName(0), fieldname);
1259}
1260
1261void tst_QSqlDatabase::psql_schemas()
1262{
1263 QFETCH(QString, dbName);
1264 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1265 CHECK_DATABASE(db);
1266
1267 if (!db.tables(type: QSql::SystemTables).contains(str: "pg_namespace"))
1268 QSKIP("server does not support schemas");
1269
1270 QSqlQuery q(db);
1271 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
1272
1273 const QString schemaName = qTableName(prefix: "qtestschema", __FILE__, db);
1274 QVERIFY_SQL(q, exec("CREATE SCHEMA " + schemaName));
1275
1276 const auto table = schemaName + '.' + qTableName(prefix: "qtesttable", __FILE__, db);
1277 const auto noescapeTable = qTableName(prefix: "qtestschema", __FILE__, db, escape: false) + '.' +
1278 qTableName(prefix: "qtesttable", __FILE__, db, escape: false);
1279 QVERIFY_SQL(q, exec("CREATE TABLE " + table + " (id int primary key, name varchar(20))"));
1280
1281 QVERIFY(db.tables().contains(noescapeTable, Qt::CaseInsensitive));
1282
1283 QSqlRecord rec = db.record(tablename: noescapeTable);
1284 QCOMPARE(rec.count(), 2);
1285 QCOMPARE(rec.fieldName(0), QString("id"));
1286 QCOMPARE(rec.fieldName(1), QString("name"));
1287
1288 QSqlIndex idx = db.primaryIndex(tablename: noescapeTable);
1289 QCOMPARE(idx.count(), 1);
1290 QCOMPARE(idx.fieldName(0), QString("id"));
1291}
1292
1293void tst_QSqlDatabase::psql_escapedIdentifiers()
1294{
1295 QFETCH(QString, dbName);
1296 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1297 QSqlDriver* drv = db.driver();
1298 CHECK_DATABASE(db);
1299
1300 if (!db.tables(type: QSql::SystemTables).contains(str: "pg_namespace"))
1301 QSKIP("server does not support schemas");
1302
1303 QSqlQuery q(db);
1304 QVERIFY_SQL( q, exec("set client_min_messages='warning'"));
1305
1306 const char bumpyCase[] = "qtestScHeMa";
1307 const QString schemaName(qTableName(prefix: bumpyCase, __FILE__, db)),
1308 tableName(qTableName(prefix: "qtest", __FILE__, db)),
1309 field1Name(QLatin1String("fIeLdNaMe")),
1310 field2Name(QLatin1String("ZuLu"));
1311
1312 q.exec(query: QString("DROP SCHEMA %1 CASCADE").arg(a: schemaName));
1313 const auto createSchema = QString("CREATE SCHEMA %1").arg(a: schemaName);
1314 QVERIFY_SQL(q, exec(createSchema));
1315 const auto createTable = QString("CREATE TABLE %1.%2 (\"%3\" int PRIMARY KEY, \"%4\" varchar(20))")
1316 .arg(a1: schemaName, a2: tableName, a3: field1Name, a4: field2Name);
1317 QVERIFY_SQL(q, exec(createTable));
1318
1319 QVERIFY(db.tables().contains(qTableName(bumpyCase, __FILE__, db, false) + '.' +
1320 qTableName("qtest", __FILE__, db, false), Qt::CaseSensitive));
1321
1322 QSqlField fld1(field1Name, QVariant::Int);
1323 QSqlField fld2(field2Name, QVariant::String);
1324 QSqlRecord rec;
1325 rec.append(field: fld1);
1326 rec.append(field: fld2);
1327
1328 QVERIFY_SQL(q, exec(drv->sqlStatement(QSqlDriver::SelectStatement,
1329 schemaName + '.' + tableName,
1330 rec, false)));
1331
1332 rec = q.record();
1333 QCOMPARE(rec.count(), 2);
1334 QCOMPARE(rec.fieldName(0), field1Name);
1335 QCOMPARE(rec.fieldName(1), field2Name);
1336 QCOMPARE(rec.field(0).type(), QVariant::Int);
1337
1338 q.exec(query: QString("DROP SCHEMA %1 CASCADE").arg(a: schemaName));
1339}
1340
1341void tst_QSqlDatabase::psql_escapeBytea()
1342{
1343 QFETCH(QString, dbName);
1344 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1345 CHECK_DATABASE(db);
1346
1347 const char dta[4] = {'\x71', '\x14', '\x32', '\x81'};
1348 QByteArray ba(dta, 4);
1349
1350 QSqlQuery q(db);
1351 const QString tableName(qTableName(prefix: "batable", __FILE__, db));
1352 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (ba bytea)").arg(tableName)));
1353
1354 QSqlQuery iq(db);
1355 QVERIFY_SQL(iq, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName)));
1356 iq.bindValue(pos: 0, val: QVariant(ba));
1357 QVERIFY_SQL(iq, exec());
1358
1359 QVERIFY_SQL(q, exec(QString("SELECT ba FROM %1").arg(tableName)));
1360 QVERIFY_SQL(q, next());
1361
1362 QByteArray res = q.value(i: 0).toByteArray();
1363 int i = 0;
1364 for (; i < ba.size(); ++i){
1365 if (ba[i] != res[i])
1366 break;
1367 }
1368
1369 QCOMPARE(i, 4);
1370}
1371
1372void tst_QSqlDatabase::psql_bug249059()
1373{
1374 QFETCH(QString, dbName);
1375 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1376 CHECK_DATABASE(db);
1377
1378 QSqlQuery q(db);
1379 const QString tableName(qTableName(prefix: "bug_249059", __FILE__, db));
1380 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (dt timestamp, t time)").arg(tableName)));
1381
1382 QSqlQuery iq(db);
1383 QVERIFY_SQL(iq, prepare(QString("INSERT INTO %1 VALUES (?, ?)").arg(tableName)));
1384 iq.bindValue(pos: 0, val: QVariant(QString("2001-09-09 04:05:06.789 -5:00")));
1385 iq.bindValue(pos: 1, val: QVariant(QString("04:05:06.789 -5:00")));
1386 QVERIFY_SQL(iq, exec());
1387 iq.bindValue(pos: 0, val: QVariant(QString("2001-09-09 04:05:06.789 +5:00")));
1388 iq.bindValue(pos: 1, val: QVariant(QString("04:05:06.789 +5:00")));
1389 QVERIFY_SQL(iq, exec());
1390
1391 QVERIFY_SQL(q, exec(QString("SELECT dt, t FROM %1").arg(tableName)));
1392 QVERIFY_SQL(q, next());
1393 QDateTime dt1=q.value(i: 0).toDateTime();
1394 QTime t1=q.value(i: 1).toTime();
1395 QVERIFY_SQL(q, next());
1396 QDateTime dt2=q.value(i: 0).toDateTime();
1397 QTime t2=q.value(i: 1).toTime();
1398
1399 // These will fail when timezone support is added, when that's the case, set the second record to 14:05:06.789 and it should work correctly
1400 QCOMPARE(dt1, dt2);
1401 QCOMPARE(t1, t2);
1402}
1403
1404// This test should be rewritten to work with Oracle as well - or the Oracle driver
1405// should be fixed to make this test pass (handle overflows)
1406void tst_QSqlDatabase::precisionPolicy()
1407{
1408 QFETCH(QString, dbName);
1409 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1410 CHECK_DATABASE(db);
1411// DBMS_SPECIFIC(db, "QPSQL");
1412
1413 QSqlQuery q(db);
1414 const QString tableName(qTableName(prefix: "qtest_prec", __FILE__, db));
1415 if(!db.driver()->hasFeature(f: QSqlDriver::LowPrecisionNumbers))
1416 QSKIP("Driver or database doesn't support setting precision policy");
1417
1418 // Create a test table with some data
1419 if(tst_Databases::isMSAccess(db))
1420 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id smallint, num number)").arg(tableName)));
1421 else
1422 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id smallint, num numeric(18,5))").arg(tableName)));
1423 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?)").arg(tableName)));
1424 q.bindValue(pos: 0, val: 1);
1425 q.bindValue(pos: 1, val: 123);
1426 QVERIFY_SQL(q, exec());
1427 q.bindValue(pos: 0, val: 2);
1428 q.bindValue(pos: 1, val: 1850000000000.0001);
1429 QVERIFY_SQL(q, exec());
1430
1431 // These are expected to pass
1432 q.setNumericalPrecisionPolicy(QSql::HighPrecision);
1433 QString query = QString("SELECT num FROM %1 WHERE id = 1").arg(a: tableName);
1434 QVERIFY_SQL(q, exec(query));
1435 QVERIFY_SQL(q, next());
1436 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1437 if (dbType == QSqlDriver::SQLite)
1438 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1439 QCOMPARE(q.value(0).type(), QVariant::String);
1440
1441 q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64);
1442 QVERIFY_SQL(q, exec(query));
1443 QVERIFY_SQL(q, next());
1444 if(q.value(i: 0).type() != QVariant::LongLong)
1445 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1446 QCOMPARE(q.value(0).type(), QVariant::LongLong);
1447 QCOMPARE(q.value(0).toLongLong(), (qlonglong)123);
1448
1449 q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt32);
1450 QVERIFY_SQL(q, exec(query));
1451 QVERIFY_SQL(q, next());
1452 if (dbType == QSqlDriver::SQLite)
1453 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1454 QCOMPARE(q.value(0).type(), QVariant::Int);
1455 QCOMPARE(q.value(0).toInt(), 123);
1456
1457 q.setNumericalPrecisionPolicy(QSql::LowPrecisionDouble);
1458 QVERIFY_SQL(q, exec(query));
1459 QVERIFY_SQL(q, next());
1460 if (dbType == QSqlDriver::SQLite)
1461 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1462 QCOMPARE(q.value(0).type(), QVariant::Double);
1463 QCOMPARE(q.value(0).toDouble(), (double)123);
1464
1465 query = QString("SELECT num FROM %1 WHERE id = 2").arg(a: tableName);
1466 QVERIFY_SQL(q, exec(query));
1467 QVERIFY_SQL(q, next());
1468 if (dbType == QSqlDriver::SQLite)
1469 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1470 QCOMPARE(q.value(0).type(), QVariant::Double);
1471 QCOMPARE(q.value(0).toDouble(), QString("1850000000000.0001").toDouble());
1472
1473 // Postgres returns invalid QVariants on overflow
1474 q.setNumericalPrecisionPolicy(QSql::HighPrecision);
1475 QVERIFY_SQL(q, exec(query));
1476 QVERIFY_SQL(q, next());
1477 if (dbType == QSqlDriver::SQLite)
1478 QEXPECT_FAIL("", "SQLite returns this value as determined by contents of the field, not the declaration", Continue);
1479 QCOMPARE(q.value(0).type(), QVariant::String);
1480
1481 q.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64);
1482 QEXPECT_FAIL("QOCI", "Oracle fails here, to retrieve next", Continue);
1483 QVERIFY_SQL(q, exec(query));
1484 QVERIFY_SQL(q, next());
1485 QCOMPARE(q.value(0).type(), QVariant::LongLong);
1486
1487 QSql::NumericalPrecisionPolicy oldPrecision= db.numericalPrecisionPolicy();
1488 db.setNumericalPrecisionPolicy(QSql::LowPrecisionInt64);
1489 QSqlQuery q2(db);
1490 q2.exec(query: QString("SELECT num FROM %1 WHERE id = 2").arg(a: tableName));
1491 QVERIFY_SQL(q2, exec(query));
1492 QVERIFY_SQL(q2, next());
1493 QCOMPARE(q2.value(0).type(), QVariant::LongLong);
1494 db.setNumericalPrecisionPolicy(oldPrecision);
1495}
1496
1497void tst_QSqlDatabase::infinityAndNan()
1498{
1499 QFETCH(QString, dbName);
1500 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1501 CHECK_DATABASE(db);
1502
1503 if (tst_Databases::getDatabaseType(db) != QSqlDriver::PostgreSQL)
1504 QSKIP("checking for infinity/nan currently only works for PostgreSQL");
1505
1506 QSqlQuery q(db);
1507 const QString tableName(qTableName(prefix: "infititytest", __FILE__, db));
1508 tst_Databases::safeDropTables(db, tableNames: {tableName});
1509 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id smallint, val double precision)").arg(tableName)));
1510
1511 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?)").arg(tableName)));
1512
1513 q.bindValue(pos: 0, val: 1);
1514 q.bindValue(pos: 1, val: qQNaN());
1515 QVERIFY_SQL(q, exec());
1516 q.bindValue(pos: 0, val: 2);
1517 q.bindValue(pos: 1, val: qInf());
1518 QVERIFY_SQL(q, exec());
1519 q.bindValue(pos: 0, val: 3);
1520 q.bindValue(pos: 1, val: -qInf());
1521 QVERIFY_SQL(q, exec());
1522
1523 QVERIFY_SQL(q, exec(QString("SELECT val FROM %1 ORDER BY id").arg(tableName)));
1524
1525 QVERIFY_SQL(q, next());
1526 QVERIFY(qIsNaN(q.value(0).toDouble()));
1527
1528 QVERIFY_SQL(q, next());
1529 QVERIFY(qIsInf(q.value(0).toDouble()));
1530 QVERIFY(q.value(0).toDouble() > 0);
1531
1532 QVERIFY_SQL(q, next());
1533 QVERIFY(qIsInf(q.value(0).toDouble()));
1534 QVERIFY(q.value(0).toDouble() < 0);
1535}
1536
1537// This test needs a ODBC data source containing MYSQL in it's name
1538void tst_QSqlDatabase::mysqlOdbc_unsignedIntegers()
1539{
1540 QFETCH(QString, dbName);
1541 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1542 CHECK_DATABASE(db);
1543
1544 if (tst_Databases::getDatabaseType(db) != QSqlDriver::MySqlServer || !db.driverName().startsWith(s: "QODBC"))
1545 QSKIP("MySQL through ODBC-driver specific test");
1546
1547 QSqlQuery q(db);
1548 const QString tableName(qTableName(prefix: "uint", __FILE__, db));
1549 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (foo integer(10) unsigned, bar integer(10))").arg(tableName)));
1550 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (-4000000000, -4000000000)").arg(tableName)));
1551 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (4000000000, 4000000000)").arg(tableName)));
1552
1553 QVERIFY_SQL(q, exec(QString("SELECT foo, bar FROM %1").arg(tableName)));
1554 QVERIFY(q.next());
1555 QCOMPARE(q.value(0).toString(), QString("0"));
1556 QCOMPARE(q.value(1).toString(), QString("-2147483648"));
1557 QVERIFY(q.next());
1558 QCOMPARE(q.value(0).toString(), QString("4000000000"));
1559 QCOMPARE(q.value(1).toString(), QString("2147483647"));
1560}
1561
1562void tst_QSqlDatabase::accessOdbc_strings()
1563{
1564 QFETCH(QString, dbName);
1565 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1566 CHECK_DATABASE(db);
1567
1568 if (!tst_Databases::isMSAccess(db))
1569 QSKIP("MS Access specific test");
1570
1571 QSqlQuery q(db);
1572 const QString tableName(qTableName(prefix: "strings", __FILE__, db));
1573 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (aStr memo, bStr memo, cStr memo, dStr memo"
1574 ", eStr memo, fStr memo, gStr memo, hStr memo)").arg(tableName)));
1575
1576 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?, ?, ?, ?, ?, ?, ?)").arg(tableName)));
1577 QString aStr, bStr, cStr, dStr, eStr, fStr, gStr, hStr;
1578
1579 q.bindValue(pos: 0, val: aStr.fill(c: 'A', size: 32));
1580 q.bindValue(pos: 1, val: bStr.fill(c: 'B', size: 127));
1581 q.bindValue(pos: 2, val: cStr.fill(c: 'C', size: 128));
1582 q.bindValue(pos: 3, val: dStr.fill(c: 'D', size: 129));
1583 q.bindValue(pos: 4, val: eStr.fill(c: 'E', size: 254));
1584 q.bindValue(pos: 5, val: fStr.fill(c: 'F', size: 255));
1585 q.bindValue(pos: 6, val: gStr.fill(c: 'G', size: 256));
1586 q.bindValue(pos: 7, val: hStr.fill(c: 'H', size: 512));
1587
1588 QVERIFY_SQL(q, exec());
1589
1590 QVERIFY_SQL(q, exec(QString("SELECT aStr, bStr, cStr, dStr, eStr, fStr, gStr, hStr FROM %1").arg(tableName)));
1591 q.next();
1592 QCOMPARE(q.value(0).toString(), aStr);
1593 QCOMPARE(q.value(1).toString(), bStr);
1594 QCOMPARE(q.value(2).toString(), cStr);
1595 QCOMPARE(q.value(3).toString(), dStr);
1596 QCOMPARE(q.value(4).toString(), eStr);
1597 QCOMPARE(q.value(5).toString(), fStr);
1598 QCOMPARE(q.value(6).toString(), gStr);
1599 QCOMPARE(q.value(7).toString(), hStr);
1600}
1601
1602// For task 125053
1603void tst_QSqlDatabase::ibase_numericFields()
1604{
1605 QFETCH(QString, dbName);
1606 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1607 CHECK_DATABASE(db);
1608
1609 QSqlQuery q(db);
1610 const QString tableName(qTableName(prefix: "numericfields", __FILE__, db));
1611 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (id int not null, num1 NUMERIC(2,1), "
1612 "num2 NUMERIC(5,2), num3 NUMERIC(10,3), "
1613 "num4 NUMERIC(18,4))").arg(tableName)));
1614
1615 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES (1, 1.1, 123.45, 1234567.123, 10203040506070.8090)").arg(tableName)));
1616
1617 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?, ?, ?, ?, ?)").arg(tableName)));
1618
1619 double num1 = 1.1;
1620 double num2 = 123.45;
1621 double num3 = 1234567.123;
1622 double num4 = 10203040506070.8090;
1623
1624 q.bindValue(pos: 0, val: 2);
1625 q.bindValue(pos: 1, val: QVariant(num1));
1626 q.bindValue(pos: 2, val: QVariant(num2));
1627 q.bindValue(pos: 3, val: QVariant(num3));
1628 q.bindValue(pos: 4, val: QVariant(num4));
1629 QVERIFY_SQL(q, exec());
1630
1631 QVERIFY_SQL(q, exec(QString("SELECT id, num1, num2, num3, num4 FROM %1").arg(tableName)));
1632
1633 int id = 0;
1634 while (q.next()) {
1635 QCOMPARE(q.value(0).toInt(), ++id);
1636 QCOMPARE(q.value(1).toString(), QString("%1").arg(num1));
1637 QCOMPARE(q.value(2).toString(), QString("%1").arg(num2));
1638 QCOMPARE(QString("%1").arg(q.value(3).toDouble()), QString("%1").arg(num3));
1639 QCOMPARE(QString("%1").arg(q.value(4).toDouble()), QString("%1").arg(num4));
1640 QCOMPARE(q.value(0).type(), QVariant::Int);
1641 QCOMPARE(q.value(1).type(), QVariant::Double);
1642 QCOMPARE(q.value(2).type(), QVariant::Double);
1643 QCOMPARE(q.value(3).type(), QVariant::Double);
1644 QCOMPARE(q.value(4).type(), QVariant::Double);
1645
1646 QCOMPARE(q.record().field(1).length(), 2);
1647 QCOMPARE(q.record().field(1).precision(), 1);
1648 QCOMPARE(q.record().field(2).length(), 5);
1649 QCOMPARE(q.record().field(2).precision(), 2);
1650 QCOMPARE(q.record().field(3).length(), 10);
1651 QCOMPARE(q.record().field(3).precision(), 3);
1652 QCOMPARE(q.record().field(4).length(), 18);
1653 QCOMPARE(q.record().field(4).precision(), 4);
1654 QCOMPARE(q.record().field(0).requiredStatus(), QSqlField::Required);
1655 QCOMPARE(q.record().field(1).requiredStatus(), QSqlField::Optional);
1656 }
1657
1658 QSqlRecord r = db.record(tablename: tableName);
1659 QCOMPARE(r.field(0).type(), QVariant::Int);
1660 QCOMPARE(r.field(1).type(), QVariant::Double);
1661 QCOMPARE(r.field(2).type(), QVariant::Double);
1662 QCOMPARE(r.field(3).type(), QVariant::Double);
1663 QCOMPARE(r.field(4).type(), QVariant::Double);
1664 QCOMPARE(r.field(1).length(), 2);
1665 QCOMPARE(r.field(1).precision(), 1);
1666 QCOMPARE(r.field(2).length(), 5);
1667 QCOMPARE(r.field(2).precision(), 2);
1668 QCOMPARE(r.field(3).length(), 10);
1669 QCOMPARE(r.field(3).precision(), 3);
1670 QCOMPARE(r.field(4).length(), 18);
1671 QCOMPARE(r.field(4).precision(), 4);
1672 QCOMPARE(r.field(0).requiredStatus(), QSqlField::Required);
1673 QCOMPARE(r.field(1).requiredStatus(), QSqlField::Optional);
1674}
1675
1676void tst_QSqlDatabase::ibase_fetchBlobs()
1677{
1678 QFETCH(QString, dbName);
1679 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1680 CHECK_DATABASE(db);
1681
1682 const QString tableName(qTableName(prefix: "qtest_ibaseblobs", __FILE__, db));
1683 QSqlQuery q(db);
1684 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1 (blob1 BLOB segment size 256)").arg(tableName)));
1685
1686 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName)));
1687 q.bindValue(pos: 0, val: QByteArray().fill(c: 'x', size: 1024));
1688 QVERIFY_SQL(q, exec());
1689
1690 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName)));
1691 q.bindValue(pos: 0, val: QByteArray().fill(c: 'x', size: 16383));
1692 QVERIFY_SQL(q, exec());
1693
1694 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES (?)").arg(tableName)));
1695 q.bindValue(pos: 0, val: QByteArray().fill(c: 'x', size: 17408));
1696 QVERIFY_SQL(q, exec());
1697
1698 QVERIFY_SQL(q, exec(QString("SELECT * FROM %1").arg(tableName)));
1699
1700 QVERIFY_SQL(q, next());
1701 QCOMPARE(q.value(0).toByteArray().size(), 1024);
1702 QVERIFY_SQL(q, next());
1703 QCOMPARE(q.value(0).toByteArray().size(), 16383);
1704 QVERIFY_SQL(q, next());
1705 QCOMPARE(q.value(0).toByteArray().size(), 17408);
1706}
1707
1708void tst_QSqlDatabase::ibase_procWithoutReturnValues()
1709{
1710 QFETCH(QString, dbName);
1711 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1712 CHECK_DATABASE(db);
1713
1714 QSqlQuery q(db);
1715 const QString procName(qTableName(prefix: "qtest_proc1", __FILE__, db));
1716 q.exec(query: QString("drop procedure %1").arg(a: procName));
1717 QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " (str VARCHAR(10))\nAS BEGIN\nstr='test';\nEND;"));
1718 QVERIFY_SQL(q, exec(QString("execute procedure %1('qtest')").arg(procName)));
1719 q.exec(query: QString("drop procedure %1").arg(a: procName));
1720}
1721
1722void tst_QSqlDatabase::ibase_procWithReturnValues()
1723{
1724 QFETCH(QString, dbName);
1725 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1726 CHECK_DATABASE(db);
1727
1728 const QString procName(qTableName(prefix: "qtest_proc2", __FILE__, db));
1729
1730 QSqlQuery q(db);
1731 q.exec(query: QString("drop procedure %1").arg(a: procName));
1732 QVERIFY_SQL(q, exec("CREATE PROCEDURE " + procName + " ("
1733 "\nABC INTEGER)"
1734 "\nRETURNS ("
1735 "\nRESULT INTEGER)"
1736 "\nAS"
1737 "\nbegin"
1738 "\nRESULT = 10 * ABC;"
1739 "\nsuspend;"
1740 "\nend"));
1741
1742 // Interbase procedures can be executed in two ways: EXECUTE PROCEDURE or SELECT
1743 QVERIFY_SQL(q, exec(QString("execute procedure %1(123)").arg(procName)));
1744 QVERIFY_SQL(q, next());
1745 QCOMPARE(q.value(0).toInt(), 1230);
1746 QVERIFY_SQL(q, exec(QString("select result from %1(456)").arg(procName)));
1747 QVERIFY_SQL(q, next());
1748 QCOMPARE(q.value(0).toInt(), 4560);
1749 QVERIFY_SQL(q, prepare(QLatin1String("execute procedure ")+procName+QLatin1String("(?)")));
1750 q.bindValue(pos: 0, val: 123);
1751 QVERIFY_SQL(q, exec());
1752 QVERIFY_SQL(q, next());
1753 QCOMPARE(q.value(0).toInt(), 1230);
1754 q.bindValue(pos: 0, val: 456);
1755 QVERIFY_SQL(q, exec());
1756 QVERIFY_SQL(q, next());
1757 QCOMPARE(q.value(0).toInt(), 4560);
1758
1759 q.exec(query: QString("drop procedure %1").arg(a: procName));
1760}
1761
1762void tst_QSqlDatabase::formatValueTrimStrings()
1763{
1764 QFETCH(QString, dbName);
1765 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1766 CHECK_DATABASE(db);
1767
1768 QSqlQuery q(db);
1769 const QString tableName = qTableName(prefix: "qtest", __FILE__, db);
1770 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (50, 'Trim Test ', 'Trim Test 2 ')").arg(tableName)));
1771 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (51, 'TrimTest', 'Trim Test 2')").arg(tableName)));
1772 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 (id, t_varchar, t_char) values (52, ' ', ' ')").arg(tableName)));
1773
1774 QVERIFY_SQL(q, exec(QString("SELECT t_varchar, t_char FROM %1 WHERE id >= 50 AND id <= 52 ORDER BY id").arg(tableName)));
1775
1776 QVERIFY_SQL(q, next());
1777
1778 QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("'Trim Test'"));
1779 QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("'Trim Test 2'"));
1780
1781 QVERIFY_SQL(q, next());
1782 QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("'TrimTest'"));
1783 QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("'Trim Test 2'"));
1784
1785 QVERIFY_SQL(q, next());
1786 QCOMPARE(db.driver()->formatValue(q.record().field(0), true), QString("''"));
1787 QCOMPARE(db.driver()->formatValue(q.record().field(1), true), QString("''"));
1788
1789}
1790
1791void tst_QSqlDatabase::odbc_reopenDatabase()
1792{
1793 QFETCH(QString, dbName);
1794 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1795 const QString tableName = qTableName(prefix: "qtest", __FILE__, db);
1796 CHECK_DATABASE(db);
1797
1798 QSqlQuery q(db);
1799 QVERIFY_SQL(q, exec("SELECT * from " + tableName));
1800 QVERIFY_SQL(q, next());
1801 db.open();
1802 QVERIFY_SQL(q, exec("SELECT * from " + tableName));
1803 QVERIFY_SQL(q, next());
1804 db.open();
1805}
1806
1807void tst_QSqlDatabase::odbc_bindBoolean()
1808{
1809 QFETCH(QString, dbName);
1810 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1811 CHECK_DATABASE(db);
1812
1813 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1814 if (dbType == QSqlDriver::MySqlServer)
1815 QSKIP("MySql has inconsistent behaviour of bit field type across versions.");
1816
1817 QSqlQuery q(db);
1818 const QString tableName = qTableName(prefix: "qtestBindBool", __FILE__, db);
1819 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + "(id int, boolvalue bit)"));
1820
1821 // Bind and insert
1822 QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " VALUES(?, ?)"));
1823 q.bindValue(pos: 0, val: 1);
1824 q.bindValue(pos: 1, val: true);
1825 QVERIFY_SQL(q, exec());
1826 q.bindValue(pos: 0, val: 2);
1827 q.bindValue(pos: 1, val: false);
1828 QVERIFY_SQL(q, exec());
1829
1830 // Retrive
1831 QVERIFY_SQL(q, exec("SELECT id, boolvalue FROM " + tableName + " ORDER BY id"));
1832 QVERIFY_SQL(q, next());
1833 QCOMPARE(q.value(0).toInt(), 1);
1834 QCOMPARE(q.value(1).toBool(), true);
1835 QVERIFY_SQL(q, next());
1836 QCOMPARE(q.value(0).toInt(), 2);
1837 QCOMPARE(q.value(1).toBool(), false);
1838}
1839
1840void tst_QSqlDatabase::odbc_testqGetString()
1841{
1842 QFETCH(QString, dbName);
1843 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1844 CHECK_DATABASE(db);
1845 const QString testqGetString(qTableName(prefix: "testqGetString", __FILE__, db));
1846
1847 QSqlQuery q(db);
1848 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
1849 if (dbType == QSqlDriver::MSSqlServer)
1850 QVERIFY_SQL(q, exec("CREATE TABLE " + testqGetString + "(id int, vcvalue varchar(MAX))"));
1851 else if(tst_Databases::isMSAccess(db))
1852 QVERIFY_SQL(q, exec("CREATE TABLE " + testqGetString + "(id int, vcvalue memo)"));
1853 else
1854 QVERIFY_SQL(q, exec("CREATE TABLE " + testqGetString + "(id int, vcvalue varchar(65538))"));
1855
1856 QString largeString;
1857 largeString.fill(c: 'A', size: 65536);
1858
1859 // Bind and insert
1860 QVERIFY_SQL(q, prepare("INSERT INTO " + testqGetString + " VALUES(?, ?)"));
1861 q.bindValue(pos: 0, val: 1);
1862 q.bindValue(pos: 1, val: largeString);
1863 QVERIFY_SQL(q, exec());
1864 q.bindValue(pos: 0, val: 2);
1865 q.bindValue(pos: 1, val: largeString+QLatin1Char('B'));
1866 QVERIFY_SQL(q, exec());
1867 q.bindValue(pos: 0, val: 3);
1868 q.bindValue(pos: 1, val: largeString+QLatin1Char('B')+QLatin1Char('C'));
1869 QVERIFY_SQL(q, exec());
1870
1871 // Retrive
1872 QVERIFY_SQL(q, exec("SELECT id, vcvalue FROM " + testqGetString + " ORDER BY id"));
1873 QVERIFY_SQL(q, next());
1874 QCOMPARE(q.value(0).toInt(), 1);
1875 QCOMPARE(q.value(1).toString().length(), 65536);
1876 QVERIFY_SQL(q, next());
1877 QCOMPARE(q.value(0).toInt(), 2);
1878 QCOMPARE(q.value(1).toString().length(), 65537);
1879 QVERIFY_SQL(q, next());
1880 QCOMPARE(q.value(0).toInt(), 3);
1881 QCOMPARE(q.value(1).toString().length(), 65538);
1882}
1883
1884
1885void tst_QSqlDatabase::mysql_multiselect()
1886{
1887 QFETCH(QString, dbName);
1888 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1889 CHECK_DATABASE(db);
1890 const QString qtest(qTableName(prefix: "qtest", __FILE__, db));
1891
1892 QSqlQuery q(db);
1893 QVERIFY_SQL(q, exec("SELECT * FROM " + qtest + "; SELECT * FROM " + qtest));
1894 QVERIFY_SQL(q, next());
1895 QVERIFY_SQL(q, exec("SELECT * FROM " + qtest + "; SELECT * FROM " + qtest));
1896 QVERIFY_SQL(q, next());
1897 QVERIFY_SQL(q, exec("SELECT * FROM " + qtest));
1898}
1899
1900void tst_QSqlDatabase::ibase_useCustomCharset()
1901{
1902 QFETCH(QString, dbName);
1903 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1904 CHECK_DATABASE(db);
1905 QString nonlatin1string("��");
1906
1907 db.close();
1908 db.setConnectOptions("ISC_DPB_LC_CTYPE=Latin1");
1909 db.open();
1910
1911 const QString tableName(qTableName(prefix: "latin1table", __FILE__, db));
1912
1913 QSqlQuery q(db);
1914 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(text VARCHAR(6) CHARACTER SET Latin1)").arg(tableName)));
1915 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName)));
1916 q.addBindValue(val: nonlatin1string);
1917 QVERIFY_SQL(q, exec());
1918 QVERIFY_SQL(q, exec(QString("SELECT text FROM %1").arg(tableName)));
1919 QVERIFY_SQL(q, next());
1920 QCOMPARE(toHex(q.value(0).toString()), toHex(nonlatin1string));
1921}
1922
1923void tst_QSqlDatabase::oci_serverDetach()
1924{
1925 QFETCH(QString, dbName);
1926 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1927 CHECK_DATABASE(db);
1928
1929 for (int i = 0; i < 2; i++) {
1930 db.close();
1931 if (db.open()) {
1932 QSqlQuery query(db);
1933 query.exec(query: "SELECT 1 FROM DUAL");
1934 db.close();
1935 } else {
1936 QFAIL(tst_Databases::printError(db.lastError(), db));
1937 }
1938 }
1939 if(!db.open())
1940 qFatal(msg: "%s", qPrintable(tst_Databases::printError(db.lastError(), db)));
1941}
1942
1943void tst_QSqlDatabase::oci_xmltypeSupport()
1944{
1945 QFETCH(QString, dbName);
1946 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1947 CHECK_DATABASE(db);
1948
1949 const QString tableName(qTableName(prefix: "qtest_xmltype", __FILE__, db));
1950 QString xml("<?xml version=\"1.0\"?>\n<TABLE_NAME>MY_TABLE</TABLE_NAME>\n");
1951 QSqlQuery q(db);
1952
1953 // Embedding the XML in the statement
1954 if(!q.exec(query: QString("CREATE TABLE %1(xmldata xmltype)").arg(a: tableName)))
1955 QSKIP("This test requries xml type support");
1956 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 values('%2')").arg(tableName).arg(xml)));
1957 QVERIFY_SQL(q, exec(QString("SELECT a.xmldata.getStringVal() FROM %1 a").arg(tableName)));
1958 QVERIFY_SQL(q, last());
1959 QCOMPARE(q.value(0).toString(), xml);
1960
1961 // Binding the XML with a prepared statement
1962 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 values(?)").arg(tableName)));
1963 q.addBindValue(val: xml);
1964 QVERIFY_SQL(q, exec());
1965 QVERIFY_SQL(q, exec(QString("SELECT a.xmldata.getStringVal() FROM %1 a").arg(tableName)));
1966 QVERIFY_SQL(q, last());
1967 QCOMPARE(q.value(0).toString(), xml);
1968}
1969
1970
1971void tst_QSqlDatabase::oci_fieldLength()
1972{
1973 QFETCH(QString, dbName);
1974 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1975 CHECK_DATABASE(db);
1976
1977 const QString tableName(qTableName(prefix: "qtest", __FILE__, db));
1978 QSqlQuery q(db);
1979
1980 QVERIFY_SQL(q, exec(QString("SELECT t_varchar, t_char FROM %1").arg(tableName)));
1981 QVERIFY_SQL(q, next());
1982 QCOMPARE(q.record().field(0).length(), 40);
1983 QCOMPARE(q.record().field(1).length(), 40);
1984}
1985
1986void tst_QSqlDatabase::oci_synonymstest()
1987{
1988 QFETCH(QString, dbName);
1989 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
1990 CHECK_DATABASE(db);
1991
1992 QSqlQuery q(db);
1993 const QString creator(qTableName(prefix: "CREATOR", __FILE__, db)), appuser(qTableName(prefix: "APPUSER", __FILE__, db)), table1(qTableName(prefix: "TABLE1", __FILE__, db));
1994// QVERIFY_SQL(q, exec("drop public synonym "+table1));
1995 QVERIFY_SQL(q, exec(QString("create user %1 identified by %2 default tablespace users temporary tablespace temp").arg(creator).arg(creator)));
1996 QVERIFY_SQL(q, exec(QString("grant CONNECT to %1").arg(creator)));
1997 QVERIFY_SQL(q, exec(QString("grant RESOURCE to %1").arg(creator)));
1998 QSqlDatabase db2=db.cloneDatabase(other: db, connectionName: QLatin1String("oci_synonymstest"));
1999 db2.close();
2000 QVERIFY_SQL(db2, open(creator,creator));
2001 QSqlQuery q2(db2);
2002 QVERIFY_SQL(q2, exec(QString("create table %1(id int primary key)").arg(table1)));
2003 QVERIFY_SQL(q, exec(QString("create user %1 identified by %2 default tablespace users temporary tablespace temp").arg(appuser).arg(appuser)));
2004 QVERIFY_SQL(q, exec(QString("grant CREATE ANY SYNONYM to %1").arg(appuser)));
2005 QVERIFY_SQL(q, exec(QString("grant CONNECT to %1").arg(appuser)));
2006 QVERIFY_SQL(q2, exec(QString("grant select, insert, update, delete on %1 to %2").arg(table1).arg(appuser)));
2007 QSqlDatabase db3=db.cloneDatabase(other: db, connectionName: QLatin1String("oci_synonymstest2"));
2008 db3.close();
2009 QVERIFY_SQL(db3, open(appuser,appuser));
2010 QSqlQuery q3(db3);
2011 QVERIFY_SQL(q3, exec("create synonym " + appuser + '.' + qTableName("synonyms", __FILE__, db) + " for " + creator + '.' + table1));
2012 QVERIFY_SQL(db3, tables().filter(qTableName("synonyms", __FILE__, db), Qt::CaseInsensitive).count() >= 1);
2013}
2014
2015
2016// This test isn't really necessary as SQL_GUID / uniqueidentifier is
2017// already tested in recordSQLServer().
2018void tst_QSqlDatabase::odbc_uniqueidentifier()
2019{
2020 QFETCH(QString, dbName);
2021 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2022 CHECK_DATABASE(db);
2023 QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2024 if (dbType != QSqlDriver::MSSqlServer)
2025 QSKIP("SQL Server (ODBC) specific test");
2026
2027 const QString tableName(qTableName(prefix: "qtest_sqlguid", __FILE__, db));
2028 QString guid = QString("AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE");
2029 QString invalidGuid = QString("GAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE");
2030
2031 QSqlQuery q(db);
2032 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(id uniqueidentifier)").arg(tableName)));
2033
2034 q.prepare(query: QString("INSERT INTO %1 VALUES(?)").arg(a: tableName));;
2035 q.addBindValue(val: guid);
2036 QVERIFY_SQL(q, exec());
2037
2038 q.addBindValue(val: invalidGuid);
2039 QEXPECT_FAIL("", "The GUID string is required to be correctly formated!",
2040 Continue);
2041 QVERIFY_SQL(q, exec());
2042
2043 QVERIFY_SQL(q, exec(QString("SELECT id FROM %1").arg(tableName)));
2044 QVERIFY_SQL(q, next());
2045 QCOMPARE(q.value(0).toString(), guid);
2046}
2047
2048void tst_QSqlDatabase::getConnectionName()
2049{
2050 QFETCH(QString, dbName);
2051 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2052 CHECK_DATABASE(db);
2053
2054 QCOMPARE(db.connectionName(), dbName);
2055 QSqlDatabase clone = QSqlDatabase::cloneDatabase(other: db, connectionName: "clonedDatabase");
2056 QCOMPARE(clone.connectionName(), QString("clonedDatabase"));
2057 QTest::ignoreMessage(type: QtWarningMsg, message: "QSqlDatabasePrivate::removeDatabase: "
2058 "connection 'clonedDatabase' is still in use, all queries will cease to work.");
2059 QSqlDatabase::removeDatabase(connectionName: "clonedDatabase");
2060 QCOMPARE(clone.connectionName(), QString());
2061 QCOMPARE(db.connectionName(), dbName);
2062}
2063
2064void tst_QSqlDatabase::odbc_uintfield()
2065{
2066 QFETCH(QString, dbName);
2067 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2068 CHECK_DATABASE(db);
2069
2070 const QString tableName(qTableName(prefix: "uint_table", __FILE__, db));
2071 unsigned int val = 4294967295U;
2072
2073 QSqlQuery q(db);
2074 if ( tst_Databases::isMSAccess( db ) )
2075 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(num number)").arg(tableName)));
2076 else
2077 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(num numeric(10))").arg(tableName)));
2078 q.prepare(query: QString("INSERT INTO %1 VALUES(?)").arg(a: tableName));
2079 q.addBindValue(val);
2080 QVERIFY_SQL(q, exec());
2081
2082 q.exec(query: QString("SELECT num FROM %1").arg(a: tableName));
2083 if (q.next())
2084 QCOMPARE(q.value(0).toUInt(), val);
2085}
2086
2087void tst_QSqlDatabase::eventNotification()
2088{
2089 QFETCH(QString, dbName);
2090 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2091 CHECK_DATABASE(db);
2092
2093 QSqlDriver *driver = db.driver();
2094 if (!driver->hasFeature(f: QSqlDriver::EventNotifications))
2095 QSKIP("DBMS doesn't support event notifications");
2096
2097 // Not subscribed to any events yet
2098 QCOMPARE(driver->subscribedToNotifications().size(), 0);
2099
2100 // Subscribe to "event_foo"
2101 QVERIFY_SQL(*driver, subscribeToNotification(QLatin1String("event_foo")));
2102 QCOMPARE(driver->subscribedToNotifications().size(), 1);
2103 QVERIFY(driver->subscribedToNotifications().contains("event_foo"));
2104
2105 // Can't subscribe to the same event multiple times
2106 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2107 if (dbType != QSqlDriver::PostgreSQL) {
2108 // We will resubscribe on PostgreSQL in case it is due to a disconnect, the call will
2109 // do nothing on the PostgreSQL side but it will indicate it succeeded anyway and there
2110 // will still only be one entry for it
2111 QVERIFY2(!driver->subscribeToNotification(QLatin1String("event_foo")),
2112 "Shouldn't be able to subscribe to event_foo twice");
2113 }
2114 QCOMPARE(driver->subscribedToNotifications().size(), 1);
2115
2116 // Unsubscribe from "event_foo"
2117 QVERIFY_SQL(*driver, unsubscribeFromNotification(QLatin1String("event_foo")));
2118 QCOMPARE(driver->subscribedToNotifications().size(), 0);
2119
2120 // Re-subscribing to "event_foo" now is allowed
2121 QVERIFY_SQL(*driver, subscribeToNotification(QLatin1String("event_foo")));
2122 QCOMPARE(driver->subscribedToNotifications().size(), 1);
2123
2124 // closing the connection causes automatically unsubscription from all events
2125 db.close();
2126 QCOMPARE(driver->subscribedToNotifications().size(), 0);
2127
2128 // Can't subscribe to anything while database is closed
2129 QVERIFY2(!driver->subscribeToNotification(QLatin1String("event_foo")), "Shouldn't be able to subscribe to event_foo");
2130 QCOMPARE(driver->subscribedToNotifications().size(), 0);
2131
2132 db.open();
2133}
2134
2135void tst_QSqlDatabase::eventNotificationIBase()
2136{
2137 QFETCH(QString, dbName);
2138 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2139 if (db.driverName().compare(other: QLatin1String("QIBASE"), cs: Qt::CaseInsensitive))
2140 QSKIP("QIBASE specific test");
2141 CHECK_DATABASE(db);
2142
2143 const QString procedureName(qTableName(prefix: "posteventProc", __FILE__, db));
2144 QSqlDriver *driver=db.driver();
2145 QVERIFY_SQL(*driver, subscribeToNotification(procedureName));
2146 QTest::qWait(ms: 300); // Interbase needs some time to call the driver callback.
2147
2148 db.transaction(); // InterBase events are posted from within transactions.
2149 QSqlQuery q(db);
2150 q.exec(query: QString("DROP PROCEDURE %1").arg(a: procedureName));
2151 q.exec(query: QString("CREATE PROCEDURE %1\nAS BEGIN\nPOST_EVENT '%1';\nEND;").arg(a: procedureName));
2152 q.exec(query: QString("EXECUTE PROCEDURE %1").arg(a: procedureName));
2153 QSignalSpy spy(driver, QOverload<const QString &, QSqlDriver::NotificationSource, const QVariant &>::of(ptr: &QSqlDriver::notification));
2154 db.commit(); // No notifications are posted until the transaction is committed.
2155 // Interbase needs some time to post the notification and call the driver callback.
2156 // This happends from another thread, and we have to process events in order for the
2157 // event handler in the driver to be executed and emit the notification signal.
2158 QTRY_COMPARE(spy.count(), 1);
2159 QList<QVariant> arguments = spy.takeFirst();
2160 QCOMPARE(arguments.at(0).toString(), procedureName);
2161 QVERIFY_SQL(*driver, unsubscribeFromNotification(procedureName));
2162 q.exec(query: QString("DROP PROCEDURE %1").arg(a: procedureName));
2163}
2164
2165void tst_QSqlDatabase::eventNotificationPSQL()
2166{
2167 QFETCH(QString, dbName);
2168 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2169 if (db.driverName().compare(other: QLatin1String("QPSQL"), cs: Qt::CaseInsensitive))
2170 QSKIP("QPSQL specific test");
2171 CHECK_DATABASE(db);
2172
2173 QSqlQuery query(db);
2174 const auto procedureName = qTableName(prefix: "posteventProc", __FILE__, db, escape: false);
2175 QString payload = "payload";
2176 QSqlDriver *driver = db.driver();
2177 QVERIFY_SQL(*driver, subscribeToNotification(procedureName));
2178 QSignalSpy spy(driver, QOverload<const QString &, QSqlDriver::NotificationSource, const QVariant &>::of(ptr: &QSqlDriver::notification));
2179 query.exec(query: QString("NOTIFY \"%1\", '%2'").arg(a: procedureName).arg(a: payload));
2180 QTRY_COMPARE(spy.count(), 1);
2181 QList<QVariant> arguments = spy.takeFirst();
2182 QCOMPARE(arguments.at(0).toString(), procedureName);
2183 QCOMPARE(qvariant_cast<QSqlDriver::NotificationSource>(arguments.at(1)), QSqlDriver::SelfSource);
2184 QCOMPARE(qvariant_cast<QVariant>(arguments.at(2)).toString(), payload);
2185 QVERIFY_SQL(*driver, unsubscribeFromNotification(procedureName));
2186}
2187
2188void tst_QSqlDatabase::eventNotificationSQLite()
2189{
2190 QFETCH(QString, dbName);
2191 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2192 if (db.driverName().compare(other: QLatin1String("QSQLITE"), cs: Qt::CaseInsensitive))
2193 QSKIP("QSQLITE specific test");
2194 CHECK_DATABASE(db);
2195
2196 const QString tableName(qTableName(prefix: "sqlitnotifytest", __FILE__, db));
2197 const auto noEscapeTableName(qTableName(prefix: "sqlitnotifytest", __FILE__, db, escape: false));
2198 tst_Databases::safeDropTable(db, tableName);
2199
2200 QSqlDriver *driver = db.driver();
2201 QSignalSpy spy(driver, QOverload<const QString &, QSqlDriver::NotificationSource, const QVariant &>::of(ptr: &QSqlDriver::notification));
2202 QSqlQuery q(db);
2203 QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, realVal REAL)"));
2204 driver->subscribeToNotification(name: noEscapeTableName);
2205 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, realVal) VALUES (1, 2.3)"));
2206 QTRY_COMPARE(spy.count(), 1);
2207 QList<QVariant> arguments = spy.takeFirst();
2208 QCOMPARE(arguments.at(0).toString(), noEscapeTableName);
2209 driver->unsubscribeFromNotification(name: noEscapeTableName);
2210 QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, realVal) VALUES (1, 2.3)"));
2211 QTRY_COMPARE(spy.count(), 0);
2212}
2213
2214void tst_QSqlDatabase::sqlite_bindAndFetchUInt()
2215{
2216 QFETCH(QString, dbName);
2217 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2218 CHECK_DATABASE(db);
2219 if (db.driverName().startsWith(s: "QSQLITE2"))
2220 QSKIP("SQLite3 specific test");
2221
2222 QSqlQuery q(db);
2223 const QString tableName(qTableName(prefix: "uint_test", __FILE__, db));
2224 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(uint_field UNSIGNED INTEGER)").arg(tableName)));
2225 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName)));
2226 q.addBindValue(val: 4000000000U);
2227 QVERIFY_SQL(q, exec());
2228 QVERIFY_SQL(q, exec(QString("SELECT uint_field FROM %1").arg(tableName)));
2229 QVERIFY_SQL(q, next());
2230
2231 // All integers in SQLite are signed, so even though we bound the value
2232 // as an UInt it will come back as a LongLong
2233 QCOMPARE(q.value(0).type(), QVariant::LongLong);
2234 QCOMPARE(q.value(0).toUInt(), 4000000000U);
2235}
2236
2237void tst_QSqlDatabase::db2_valueCacheUpdate()
2238{
2239 QFETCH(QString, dbName);
2240 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2241 CHECK_DATABASE(db);
2242
2243 const QString tableName(qTableName(prefix: "qtest", __FILE__, db));
2244 QSqlQuery q(db);
2245 q.exec(query: QString("SELECT id, t_varchar, t_char, t_numeric FROM %1").arg(a: tableName));
2246 q.next();
2247 QVariant c4 = q.value(i: 3);
2248 QVariant c3 = q.value(i: 2);
2249 QVariant c2 = q.value(i: 1);
2250 QVariant c1 = q.value(i: 0);
2251 QCOMPARE(c4.toString(), q.value(3).toString());
2252 QCOMPARE(c3.toString(), q.value(2).toString());
2253 QCOMPARE(c2.toString(), q.value(1).toString());
2254 QCOMPARE(c1.toString(), q.value(0).toString());
2255}
2256
2257void tst_QSqlDatabase::sqlStatementUseIsNull_189093()
2258{
2259 // NULL = NULL is unknown, the sqlStatment must use IS NULL
2260 QFETCH(QString, dbName);
2261 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2262 CHECK_DATABASE(db);
2263
2264 // select a record with NULL value
2265 QSqlQuery q(QString(), db);
2266 QVERIFY_SQL(q, exec("select * from " + qTableName("qtest", __FILE__, db) + " where id = 4"));
2267 QVERIFY_SQL(q, next());
2268
2269 QSqlDriver *driver = db.driver();
2270 QVERIFY(driver);
2271
2272 QString preparedStatment = driver->sqlStatement(type: QSqlDriver::WhereStatement, tableName: QString("qtest"), rec: q.record(), preparedStatement: true);
2273 QCOMPARE(preparedStatment.count("IS NULL", Qt::CaseInsensitive), 2);
2274
2275 QString statment = driver->sqlStatement(type: QSqlDriver::WhereStatement, tableName: QString("qtest"), rec: q.record(), preparedStatement: false);
2276 QCOMPARE(statment.count("IS NULL", Qt::CaseInsensitive), 2);
2277}
2278
2279void tst_QSqlDatabase::mysql_savepointtest()
2280{
2281 QFETCH(QString, dbName);
2282 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2283 CHECK_DATABASE(db);
2284 QSqlQuery q(db);
2285 QVERIFY_SQL(q, exec("begin"));
2286 QVERIFY_SQL(q, exec("insert into " + qTableName("qtest", __FILE__, db) + " VALUES (54, 'foo', 'foo', 54.54)"));
2287 QVERIFY_SQL(q, exec("savepoint foo"));
2288}
2289
2290void tst_QSqlDatabase::mysql_connectWithInvalidAddress()
2291{
2292 // Ensure that giving invalid connection parameters fails correctly
2293 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QMYSQL");
2294 db.setHostName("invalid.local");
2295 QCOMPARE(db.open(), false);
2296}
2297
2298void tst_QSqlDatabase::oci_tables()
2299{
2300 QSKIP("Requires specific permissions to create a system table");
2301 QFETCH(QString, dbName);
2302 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2303 CHECK_DATABASE(db);
2304 QSqlQuery q(db);
2305 const QString systemTableName("sys." + qTableName(prefix: "mypassword", __FILE__, db).toUpper());
2306 QVERIFY_SQL(q, exec("CREATE TABLE "+systemTableName+"(name VARCHAR(20))"));
2307 QVERIFY(!db.tables().contains(systemTableName.toUpper()));
2308 QVERIFY(db.tables(QSql::SystemTables).contains(systemTableName.toUpper()));
2309}
2310
2311void tst_QSqlDatabase::sqlite_enable_cache_mode()
2312{
2313 QFETCH(QString, dbName);
2314 if(dbName.endsWith(s: ":memory:"))
2315 QSKIP( "cache mode is meaningless for :memory: databases");
2316 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2317 CHECK_DATABASE(db);
2318 db.close();
2319 db.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
2320 QVERIFY_SQL(db, open());
2321 QSqlDatabase db2 = QSqlDatabase::cloneDatabase(other: db, connectionName: dbName+":cachemodeconn2");
2322 db2.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
2323 QVERIFY_SQL(db2, open());
2324 QSqlQuery q(db), q2(db2);
2325 QVERIFY_SQL(q, exec("select * from " + qTableName("qtest", __FILE__, db)));
2326 QVERIFY_SQL(q2, exec("select * from " + qTableName("qtest", __FILE__, db)));
2327 db2.close();
2328}
2329
2330void tst_QSqlDatabase::sqlite_enableRegexp()
2331{
2332 QFETCH(QString, dbName);
2333 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2334 CHECK_DATABASE(db);
2335 if (db.driverName().startsWith(s: "QSQLITE2"))
2336 QSKIP("SQLite3 specific test");
2337
2338 db.close();
2339 db.setConnectOptions("QSQLITE_ENABLE_REGEXP");
2340 QVERIFY_SQL(db, open());
2341
2342 QSqlQuery q(db);
2343 const QString tableName(qTableName(prefix: "regexp_test", __FILE__, db));
2344 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(text TEXT)").arg(tableName)));
2345 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName)));
2346 q.addBindValue(val: "a0");
2347 QVERIFY_SQL(q, exec());
2348 q.addBindValue(val: "a1");
2349 QVERIFY_SQL(q, exec());
2350
2351 QVERIFY_SQL(q, exec(QString("SELECT text FROM %1 WHERE text REGEXP 'a[^0]' "
2352 "ORDER BY text").arg(tableName)));
2353 QVERIFY_SQL(q, next());
2354 QCOMPARE(q.value(0).toString(), QString("a1"));
2355 QFAIL_SQL(q, next());
2356}
2357
2358void tst_QSqlDatabase::sqlite_openError()
2359{
2360 // see QTBUG-70506
2361 if (!QSqlDatabase::drivers().contains(str: "QSQLITE"))
2362 QSKIP("Database driver QSQLITE not available");
2363
2364 QSqlDatabase db = QSqlDatabase::addDatabase(type: "QSQLITE", connectionName: "sqlite_openError");
2365 db.setDatabaseName("/doesnotexist/foo.sqlite");
2366 QVERIFY(db.isValid());
2367
2368 QVERIFY(!db.open());
2369 QSqlError error = db.lastError();
2370 QCOMPARE(error.nativeErrorCode(), "14"); // SQLITE_CANTOPEN
2371 QCOMPARE(error.databaseText(), "unable to open database file");
2372}
2373
2374void tst_QSqlDatabase::sqlite_check_json1()
2375{
2376 QFETCH(QString, dbName);
2377 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2378 CHECK_DATABASE(db);
2379 const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
2380 if (dbType != QSqlDriver::SQLite)
2381 QSKIP("SQLite3 specific test");
2382
2383 QSqlQuery q(db);
2384 const QString json1("{\"id\":1}");
2385 const QString tableName(qTableName(prefix: "sqlite_check_json1", __FILE__, db));
2386 tst_Databases::safeDropTable(db, tableName);
2387 QVERIFY_SQL(q, exec(QString("CREATE TABLE %1(text TEXT)").arg(tableName)));
2388 QVERIFY_SQL(q, exec(QString("INSERT INTO %1 VALUES(json('%2'))").arg(tableName, json1)));
2389 QVERIFY_SQL(q, prepare(QString("INSERT INTO %1 VALUES(?)").arg(tableName)));
2390 q.addBindValue(val: "json('{\"id\":2}')");
2391 QVERIFY_SQL(q, prepare(QString("SELECT * from %1 WHERE text = json('%2')").arg(tableName, json1)));
2392 QVERIFY_SQL(q, exec());
2393 QVERIFY_SQL(q, next());
2394 QCOMPARE(q.value(0).toString(), json1);
2395 QFAIL_SQL(q, next());
2396}
2397
2398void tst_QSqlDatabase::cloneDatabase()
2399{
2400 QFETCH(QString, dbName);
2401 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2402 CHECK_DATABASE(db);
2403 {
2404 QSqlDatabase clonedDatabase = QSqlDatabase::cloneDatabase(other: db, connectionName: "clonedDatabase");
2405 QCOMPARE(clonedDatabase.databaseName(), db.databaseName());
2406 QCOMPARE(clonedDatabase.userName(), db.userName());
2407 QCOMPARE(clonedDatabase.password(), db.password());
2408 QCOMPARE(clonedDatabase.hostName(), db.hostName());
2409 QCOMPARE(clonedDatabase.driverName(), db.driverName());
2410 QCOMPARE(clonedDatabase.port(), db.port());
2411 QCOMPARE(clonedDatabase.connectOptions(), db.connectOptions());
2412 QCOMPARE(clonedDatabase.numericalPrecisionPolicy(), db.numericalPrecisionPolicy());
2413 }
2414 {
2415 // Now double check numericalPrecisionPolicy after changing it since it
2416 // is a special case, as changing it can set it on the driver as well as
2417 // the database object. When retrieving the numerical precision policy
2418 // it may just get it from the driver so we have to check that the
2419 // clone has also ensured the copied driver has the correct precision
2420 // policy too.
2421 db.setNumericalPrecisionPolicy(QSql::LowPrecisionDouble);
2422 QSqlDatabase clonedDatabase = QSqlDatabase::cloneDatabase(other: db, connectionName: "clonedDatabaseCopy");
2423 QCOMPARE(clonedDatabase.numericalPrecisionPolicy(), db.numericalPrecisionPolicy());
2424 }
2425}
2426
2427class DatabaseThreadObject : public QObject
2428{
2429 Q_OBJECT
2430public:
2431 DatabaseThreadObject(const QString &name, QObject *parent = nullptr) : QObject(parent), dbName(name)
2432 {}
2433public slots:
2434 void ready()
2435 {
2436 QTest::ignoreMessage(type: QtWarningMsg,
2437 message: "QSqlDatabasePrivate::database: requested database does not belong to the calling thread.");
2438 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2439 QVERIFY(!db.isValid());
2440
2441 QSqlDatabase invalidDb = QSqlDatabase::database(connectionName: "invalid");
2442 QVERIFY(!invalidDb.isValid());
2443
2444 {
2445 QSqlDatabase clonedDatabase = QSqlDatabase::cloneDatabase(other: dbName, connectionName: "CloneDB");
2446 QVERIFY(!clonedDatabase.isOpen());
2447 QVERIFY(clonedDatabase.isValid());
2448 QVERIFY(clonedDatabase.open());
2449 QVERIFY(clonedDatabase.isOpen());
2450 clonedDatabase.close();
2451 }
2452
2453 QThread::currentThread()->exit();
2454 }
2455private:
2456 QString dbName;
2457};
2458
2459void tst_QSqlDatabase::multipleThreads()
2460{
2461 QFETCH(QString, dbName);
2462 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
2463 CHECK_DATABASE(db);
2464 DatabaseThreadObject dto(dbName);
2465 QThread t;
2466 dto.moveToThread(thread: &t);
2467 connect(sender: &t, signal: &QThread::started, receiver: &dto, slot: &DatabaseThreadObject::ready);
2468 t.start();
2469 QTRY_VERIFY(t.isRunning());
2470 QTRY_VERIFY(t.isFinished());
2471}
2472
2473QTEST_MAIN(tst_QSqlDatabase)
2474#include "tst_qsqldatabase.moc"
2475

source code of qtbase/tests/auto/sql/kernel/qsqldatabase/tst_qsqldatabase.cpp