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
32
33#include "../qsqldatabase/tst_databases.h"
34
35#include <QtCore>
36#include <QtSql>
37#include "qdebug.h"
38
39const QString qtest(qTableName(prefix: "qtest", __FILE__, db: QSqlDatabase()));
40// set this define if Oracle is built with threading support
41//#define QOCI_THREADED
42
43class tst_QSqlThread : public QObject
44{
45 Q_OBJECT
46
47public:
48 tst_QSqlThread();
49 virtual ~tst_QSqlThread();
50
51
52 void dropTestTables();
53 void createTestTables();
54 void recreateTestTables();
55 void repopulateTestTables();
56
57 void generic_data(const QString &engine=QString());
58 tst_Databases dbs;
59
60public slots:
61 void initTestCase();
62 void cleanupTestCase();
63 void init();
64 void cleanup();
65
66protected slots:
67 void threadFinished() {
68 ++threadFinishedCount;
69 qDebug(msg: "Thread finished, total finished: %d", threadFinishedCount);
70 }
71
72private slots:
73 void simpleThreading_data() { generic_data(); }
74 void simpleThreading();
75 void readWriteThreading_data() { generic_data(); }
76 void readWriteThreading();
77 void readFromSingleConnection_data() { generic_data(); }
78 void readFromSingleConnection();
79 void readWriteFromSingleConnection_data() { generic_data(); }
80 void readWriteFromSingleConnection();
81 void preparedReadWriteFromSingleConnection_data() { generic_data(); }
82 void preparedReadWriteFromSingleConnection();
83 void transactionsFromSingleConnection_data() { generic_data(); }
84 void transactionsFromSingleConnection();
85
86private:
87 int threadFinishedCount;
88};
89
90static QAtomicInt counter;
91
92class QtTestSqlThread : public QThread
93{
94 Q_OBJECT
95public:
96 QtTestSqlThread(const QSqlDatabase &aDb, QObject *parent = 0)
97 : QThread(parent), sourceDb(aDb) {}
98
99 void runHelper(const QString &dbName)
100 {
101 QSqlDatabase db = QSqlDatabase::cloneDatabase(other: sourceDb, connectionName: dbName);
102 QVERIFY_SQL(db, open());
103
104 int sum = 0;
105 QSqlQuery q("select id from " + qtest, db);
106 QVERIFY_SQL(q, isActive());
107 while (q.next())
108 sum += q.value(i: 0).toInt();
109 QCOMPARE(sum, 6);
110 q.clear();
111 }
112
113 void run()
114 {
115 QString dbName = QString("QThreadDb%1").arg(a: (size_t)currentThreadId());
116 runHelper(dbName);
117
118 QSqlDatabase::database(connectionName: dbName).close();
119 QSqlDatabase::removeDatabase(connectionName: dbName);
120 }
121
122private:
123 QSqlDatabase sourceDb;
124};
125
126enum { ProdConIterations = 10 };
127
128class SqlProducer: public QThread
129{
130 Q_OBJECT
131public:
132 SqlProducer(const QSqlDatabase &aDb, QObject *parent = 0)
133 : QThread(parent), sourceDb(aDb) {}
134
135 void runHelper(const QString &dbName)
136 {
137 QSqlDatabase db = QSqlDatabase::cloneDatabase(other: sourceDb, connectionName: dbName);
138 QVERIFY_SQL(db, open());
139 QSqlQuery q(db);
140 QVERIFY_SQL(q, prepare("insert into " + qtest + " values (?, ?, ?)"));
141 int id = 10;
142 for (int i = 0; i < ProdConIterations; ++i) {
143 q.bindValue(pos: 0, val: ++id);
144 q.bindValue(pos: 1, val: "threaddy");
145 q.bindValue(pos: 2, val: 10);
146 QVERIFY_SQL(q, exec());
147 QThread::yieldCurrentThread();
148 }
149 }
150
151 void run()
152 {
153 QString dbName = QString("Producer%1").arg(a: (size_t)currentThreadId());
154 runHelper(dbName);
155 QSqlDatabase::database(connectionName: dbName).close();
156 QSqlDatabase::removeDatabase(connectionName: dbName);
157 }
158private:
159 QSqlDatabase sourceDb;
160};
161
162class SqlConsumer: public QThread
163{
164 Q_OBJECT
165
166public:
167 SqlConsumer(const QSqlDatabase &aDb, QObject *parent = 0)
168 : QThread(parent), sourceDb(aDb) {}
169
170 void runHelper(const QString &dbName)
171 {
172 QSqlDatabase db = QSqlDatabase::cloneDatabase(other: sourceDb, connectionName: dbName);
173 QVERIFY_SQL(db, open());
174 QSqlQuery q1(db), q2(db);
175 QVERIFY_SQL(q2, prepare("delete from " + qtest + " where id = :id"));
176
177 for (int i = 0; i < ProdConIterations; ++i) {
178 QVERIFY_SQL(q1, exec("select max(id) from " + qtest));
179 q1.first();
180 q2.bindValue(placeholder: ":id", val: q1.value(i: 0));
181 q1.clear();
182 QVERIFY_SQL(q2, exec());
183 QThread::yieldCurrentThread();
184 }
185 }
186
187 void run()
188 {
189 QString dbName = QString("Consumer%1").arg(a: (size_t)currentThreadId());
190 runHelper(dbName);
191 QSqlDatabase::database(connectionName: dbName).close();
192 QSqlDatabase::removeDatabase(connectionName: dbName);
193 }
194
195private:
196 QSqlDatabase sourceDb;
197};
198
199class SqlThread: public QThread
200{
201 Q_OBJECT
202
203public:
204 enum Mode { SimpleReading, PreparedReading, SimpleWriting, PreparedWriting };
205
206 SqlThread(Mode m, const QSqlDatabase &db, QObject *parent = 0)
207 : QThread(parent), sourceDb(db), mode(m) {}
208
209 void run()
210 {
211 QSqlDatabase &db = sourceDb;
212 switch (mode) {
213 case SimpleReading: {
214 // Executes a Query for reading, iterates over the first 4 results
215 QSqlQuery q(sourceDb);
216 for (int j = 0; j < ProdConIterations; ++j) {
217 QVERIFY_SQL(q, exec("select id,name from " + qtest + " order by id"));
218 for (int i = 1; i < 4; ++i) {
219 QVERIFY_SQL(q, next());
220 QCOMPARE(q.value(0).toInt(), i);
221 }
222 }
223 break; }
224 case SimpleWriting: {
225 // Executes a query for writing (appends a new row)
226 QSqlQuery q(sourceDb);
227 for (int j = 0; j < ProdConIterations; ++j) {
228 QVERIFY_SQL(q, exec(QString("insert into " + qtest
229 + " (id, name) values(%1, '%2')")
230 .arg(counter.fetchAndAddRelaxed(1)).arg("Robert")));
231 }
232 break; }
233 case PreparedReading: {
234 // Prepares a query for reading and iterates over the results
235 QSqlQuery q(sourceDb);
236 QVERIFY_SQL(q, prepare("select id, name from " + qtest + " where id = ?"));
237 for (int j = 0; j < ProdConIterations; ++j) {
238 q.addBindValue(val: j % 3 + 1);
239 QVERIFY_SQL(q, exec());
240 QVERIFY_SQL(q, next());
241 QCOMPARE(q.value(0).toInt(), j % 3 + 1);
242 }
243 break; }
244 case PreparedWriting: {
245 QSqlQuery q(sourceDb);
246 QVERIFY_SQL(q, prepare("insert into " + qtest + " (id, name) "
247 "values(?, ?)"));
248 for (int i = 0; i < ProdConIterations; ++i) {
249 q.addBindValue(val: counter.fetchAndAddRelaxed(valueToAdd: 1));
250 q.addBindValue(val: "Robert");
251 QVERIFY_SQL(q, exec());
252 }
253 break; }
254 }
255 }
256
257private:
258 QSqlDatabase sourceDb;
259 Mode mode;
260};
261
262
263tst_QSqlThread::tst_QSqlThread()
264 : threadFinishedCount(0)
265{
266}
267
268tst_QSqlThread::~tst_QSqlThread()
269{
270}
271
272void tst_QSqlThread::generic_data(const QString& engine)
273{
274 if ( dbs.fillTestTable(driverPrefix: engine) == 0 ) {
275 if(engine.isEmpty())
276 QSKIP( "No database drivers are available in this Qt configuration");
277 else
278 QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit());
279 }
280}
281
282void tst_QSqlThread::dropTestTables()
283{
284 for (int i = 0; i < dbs.dbNames.count(); ++i) {
285 QSqlDatabase db = QSqlDatabase::database(connectionName: dbs.dbNames.at(i));
286 QSqlQuery q(db);
287
288 tst_Databases::safeDropTables(db, tableNames: QStringList() << qtest << qTableName(prefix: "qtest2", __FILE__, db) << qTableName(prefix: "emptytable", __FILE__, db));
289 }
290}
291
292void tst_QSqlThread::createTestTables()
293{
294 for (int i = 0; i < dbs.dbNames.count(); ++i) {
295 QSqlDatabase db = QSqlDatabase::database(connectionName: dbs.dbNames.at(i));
296 QSqlQuery q(db);
297
298 QVERIFY_SQL(q, exec("create table " + qtest
299 + "(id int NOT NULL primary key, name varchar(20), title int)"));
300
301 QVERIFY_SQL(q, exec("create table " + qTableName("qtest2", __FILE__, db)
302 + "(id int NOT NULL primary key, title varchar(20))"));
303
304 QVERIFY_SQL(q, exec("create table " + qTableName("emptytable", __FILE__, db)
305 + "(id int NOT NULL primary key)"));
306 }
307}
308
309void tst_QSqlThread::repopulateTestTables()
310{
311 for (int i = 0; i < dbs.dbNames.count(); ++i) {
312 QSqlDatabase db = QSqlDatabase::database(connectionName: dbs.dbNames.at(i));
313 QSqlQuery q(db);
314
315 QVERIFY_SQL(q, exec("delete from " + qtest));
316 QVERIFY_SQL(q, exec("insert into " + qtest + " values(1, 'harry', 1)"));
317 QVERIFY_SQL(q, exec("insert into " + qtest + " values(2, 'trond', 2)"));
318 QVERIFY_SQL(q, exec("insert into " + qtest + " values(3, 'vohi', 3)"));
319
320 QVERIFY_SQL(q, exec("delete from " + qTableName("qtest2", __FILE__, db)));
321 QVERIFY_SQL(q, exec("insert into " + qTableName("qtest2", __FILE__, db) + " values(1, 'herr')"));
322 QVERIFY_SQL(q, exec("insert into " + qTableName("qtest2", __FILE__, db) + " values(2, 'mister')"));
323 }
324}
325
326void tst_QSqlThread::recreateTestTables()
327{
328 dropTestTables();
329 createTestTables();
330 repopulateTestTables();
331}
332
333void tst_QSqlThread::initTestCase()
334{
335 QVERIFY(dbs.open());
336 recreateTestTables();
337}
338
339void tst_QSqlThread::cleanupTestCase()
340{
341 dropTestTables();
342 dbs.close();
343}
344
345void tst_QSqlThread::init()
346{
347 threadFinishedCount = 0;
348 counter.storeRelaxed(newValue: 4);
349}
350
351void tst_QSqlThread::cleanup()
352{
353// repopulateTestTables();
354}
355
356// This test creates two threads that clone their db connection and read
357// from it
358void tst_QSqlThread::simpleThreading()
359{
360 QFETCH(QString, dbName);
361 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
362 CHECK_DATABASE(db);
363
364 if (db.databaseName() == ":memory:")
365 QSKIP("does not work with in-memory databases");
366
367 QtTestSqlThread t1(db);
368 QtTestSqlThread t2(db);
369
370 connect(sender: &t1, SIGNAL(finished()), receiver: this, SLOT(threadFinished()), Qt::QueuedConnection);
371 connect(sender: &t2, SIGNAL(finished()), receiver: this, SLOT(threadFinished()), Qt::QueuedConnection);
372
373 t1.start();
374 t2.start();
375
376 QTRY_VERIFY(threadFinishedCount >= 2);
377}
378
379// This test creates two threads that clone their db connection and read
380// or write
381void tst_QSqlThread::readWriteThreading()
382{
383 QFETCH(QString, dbName);
384 QSqlDatabase db = QSqlDatabase::database(connectionName: dbName);
385 CHECK_DATABASE(db);
386
387 if (db.databaseName() == ":memory:")
388 QSKIP("does not work with in-memory databases");
389 else if (tst_Databases::isMSAccess(db))
390 QSKIP("does not work with MS Access databases");
391
392 SqlProducer producer(db);
393 SqlConsumer consumer(db);
394
395 connect(sender: &producer, SIGNAL(finished()), receiver: this, SLOT(threadFinished()), Qt::QueuedConnection);
396 connect(sender: &consumer, SIGNAL(finished()), receiver: this, SLOT(threadFinished()), Qt::QueuedConnection);
397
398 producer.start();
399 consumer.start();
400
401 QTRY_VERIFY_WITH_TIMEOUT(threadFinishedCount >= 2, 10000);
402}
403
404#ifdef QOCI_THREADED
405// run with n threads in parallel. Change this constant to hammer the poor DB server even more
406static const int maxThreadCount = 4;
407#endif
408
409void tst_QSqlThread::readFromSingleConnection()
410{
411#ifdef QOCI_THREADED
412 QFETCH(QString, dbName);
413 QSqlDatabase db = QSqlDatabase::database(dbName);
414 CHECK_DATABASE(db);
415
416 if (db.databaseName() == ":memory:")
417 QSKIP("does not work with in-memory databases");
418
419 QObject cleanupHelper; // make sure the threads die when we exit the scope
420 for (int i = 0; i < maxThreadCount; ++i) {
421 SqlThread *reader = new SqlThread(SqlThread::SimpleReading, db, &cleanupHelper);
422 connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);
423 reader->start();
424 }
425
426 QTRY_VERIFY(threadFinishedCount >= maxThreadCount);
427#endif
428}
429
430void tst_QSqlThread::readWriteFromSingleConnection()
431{
432#ifdef QOCI_THREADED
433 QFETCH(QString, dbName);
434 QSqlDatabase db = QSqlDatabase::database(dbName);
435 CHECK_DATABASE(db);
436
437 if (db.databaseName() == ":memory:")
438 QSKIP("does not work with in-memory databases");
439
440 QObject cleanupHelper;
441 for (int i = 0; i < maxThreadCount; ++i) {
442 SqlThread *reader = new SqlThread(SqlThread::SimpleReading, db, &cleanupHelper);
443 connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);
444 reader->start();
445
446 SqlThread *writer = new SqlThread(SqlThread::SimpleWriting, db, &cleanupHelper);
447 connect(writer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);
448 writer->start();
449 }
450
451 QTRY_VERIFY(threadFinishedCount >= maxThreadCount * 2);
452#endif
453}
454
455void tst_QSqlThread::preparedReadWriteFromSingleConnection()
456{
457#ifdef QOCI_THREADED
458 QFETCH(QString, dbName);
459 QSqlDatabase db = QSqlDatabase::database(dbName);
460 CHECK_DATABASE(db);
461
462 if (db.databaseName() == ":memory:")
463 QSKIP("does not work with in-memory databases");
464
465 QObject cleanupHelper;
466 for (int i = 0; i < maxThreadCount; ++i) {
467 SqlThread *reader = new SqlThread(SqlThread::PreparedReading, db, &cleanupHelper);
468 connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);
469 reader->start();
470
471 SqlThread *writer = new SqlThread(SqlThread::PreparedWriting, db, &cleanupHelper);
472 connect(writer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);
473 writer->start();
474 }
475
476 QTRY_VERIFY(threadFinishedCount >= maxThreadCount * 2);
477#endif
478}
479
480void tst_QSqlThread::transactionsFromSingleConnection()
481{
482#ifdef QOCI_THREADED
483 QFETCH(QString, dbName);
484 QSqlDatabase db = QSqlDatabase::database(dbName);
485 CHECK_DATABASE(db);
486
487 if (db.databaseName() == ":memory:")
488 QSKIP("does not work with in-memory databases");
489
490 // start and commit a transaction
491 QVERIFY_SQL(db, db.transaction());
492 preparedReadWriteFromSingleConnection(); // read and write from multiple threads
493 if (QTest::currentTestFailed())
494 return;
495 QVERIFY_SQL(db, db.commit());
496
497 // reset test environment
498 threadFinishedCount = 0;
499
500 // start and roll back a transaction
501 QVERIFY_SQL(db, db.transaction());
502 preparedReadWriteFromSingleConnection(); // read and write from multiple threads
503 if (QTest::currentTestFailed())
504 return;
505 QVERIFY_SQL(db, db.rollback());
506#endif
507}
508
509QTEST_MAIN(tst_QSqlThread)
510#include "tst_qsqlthread.moc"
511

source code of qtbase/tests/auto/sql/kernel/qsqlthread/tst_qsqlthread.cpp