tests/auto/sql/kernel/qsqlthread/tst_qsqlthread.cpp

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