1 /****************************************************************************

     2 **

     3 ** Copyright (C) 2009 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 ** No Commercial Usage

    11 ** This file contains pre-release code and may not be distributed.

    12 ** You may use this file in accordance with the terms and conditions

    13 ** contained in the Technology Preview License Agreement accompanying

    14 ** this package.

    15 **

    16 ** GNU Lesser General Public License Usage

    17 ** Alternatively, this file may be used under the terms of the GNU Lesser

    18 ** General Public License version 2.1 as published by the Free Software

    19 ** Foundation and appearing in the file LICENSE.LGPL included in the

    20 ** packaging of this file.  Please review the following information to

    21 ** ensure the GNU Lesser General Public License version 2.1 requirements

    22 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.

    23 **

    24 ** In addition, as a special exception, Nokia gives you certain additional

    25 ** rights.  These rights are described in the Nokia Qt LGPL Exception

    26 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.

    27 **

    28 ** If you have questions regarding the use of this file, please contact

    29 ** Nokia at qt-info@nokia.com.

    30 **

    31 **

    32 **

    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 // set this define if Oracle is built with threading support

    57 //#define QOCI_THREADED

    58 

    59 class tst_QSqlThread : public QObject

    60 {

    61     Q_OBJECT

    62 

    63 public:

    64     tst_QSqlThread();

    65     virtual ~tst_QSqlThread();

    66 

    67 

    68     void dropTestTables();

    69     void createTestTables();

    70     void recreateTestTables();

    71     void repopulateTestTables();

    72 

    73     void generic_data(const QString &engine=QString());

    74     tst_Databases dbs;

    75 

    76 public slots:

    77     void initTestCase();

    78     void cleanupTestCase();

    79     void init();

    80     void cleanup();

    81 

    82 protected slots:

    83     void threadFinished() { ++threadFinishedCount; }

    84 

    85 private slots:

    86     void simpleThreading_data() { generic_data(); }

    87     void simpleThreading();

    88     void readWriteThreading_data() { generic_data(); }

    89     void readWriteThreading();

    90     void readFromSingleConnection_data() { generic_data(); }

    91     void readFromSingleConnection();

    92     void readWriteFromSingleConnection_data() { generic_data(); }

    93     void readWriteFromSingleConnection();

    94     void preparedReadWriteFromSingleConnection_data() { generic_data(); }

    95     void preparedReadWriteFromSingleConnection();

    96     void transactionsFromSingleConnection_data() { generic_data(); }

    97     void transactionsFromSingleConnection();

    98 

    99 private:

   100     int threadFinishedCount;

   101 };

   102 

   103 static QBasicAtomicInt counter;

   104 

   105 class QtTestSqlThread : public QThread

   106 {

   107     Q_OBJECT

   108 public:

   109     QtTestSqlThread(const QSqlDatabase &aDb, QObject *parent = 0)

   110         : QThread(parent), sourceDb(aDb) {}

   111 

   112     void runHelper(const QString &dbName)

   113     {

   114         QSqlDatabase db = QSqlDatabase::cloneDatabase(sourceDb, dbName);

   115         QVERIFY_SQL(db, open());

   116 

   117         int sum = 0;

   118         QSqlQuery q("select id from " + qTableName("test"), db);

   119         QVERIFY_SQL(q, isActive());

   120         while (q.next())

   121             sum += q.value(0).toInt();

   122         QCOMPARE(sum, 6);

   123         q.clear();

   124     }

   125 

   126     void run()

   127     {

   128         QString dbName = QString("QThreadDb%1").arg((size_t)currentThreadId());

   129         runHelper(dbName);

   130 

   131         QSqlDatabase::database(dbName).close();

   132         QSqlDatabase::removeDatabase(dbName);

   133     }

   134 

   135 private:

   136     QSqlDatabase sourceDb;

   137 };

   138 

   139 enum { ProdConIterations = 10 };

   140 

   141 class SqlProducer: public QThread

   142 {

   143     Q_OBJECT

   144 public:

   145     SqlProducer(const QSqlDatabase &aDb, QObject *parent = 0)

   146         : QThread(parent), sourceDb(aDb) {}

   147 

   148     void runHelper(const QString &dbName)

   149     {

   150         QSqlDatabase db = QSqlDatabase::cloneDatabase(sourceDb, dbName);

   151         QVERIFY_SQL(db, open());

   152         QSqlQuery q(db);

   153         QVERIFY_SQL(q, prepare("insert into " + qTableName("test") + " values (?, ?, ?)"));

   154         int id = 10;

   155         for (int i = 0; i < ProdConIterations; ++i) {

   156             q.bindValue(0, ++id);

   157             q.bindValue(1, "threaddy");

   158             q.bindValue(2, 10);

   159             QVERIFY_SQL(q, exec());

   160 #ifdef Q_OS_LINUX

   161             pthread_yield();

   162 #endif

   163         }

   164     }

   165 

   166     void run()

   167     {

   168         QString dbName = QString("Producer%1").arg((size_t)currentThreadId());

   169         runHelper(dbName);

   170         QSqlDatabase::database(dbName).close();

   171         QSqlDatabase::removeDatabase(dbName);

   172     }

   173 private:

   174     QSqlDatabase sourceDb;

   175 };

   176 

   177 class SqlConsumer: public QThread

   178 {

   179     Q_OBJECT

   180 

   181 public:

   182     SqlConsumer(const QSqlDatabase &aDb, QObject *parent = 0)

   183         : QThread(parent), sourceDb(aDb) {}

   184 

   185     void runHelper(const QString &dbName)

   186     {

   187         QSqlDatabase db = QSqlDatabase::cloneDatabase(sourceDb, dbName);

   188         QVERIFY_SQL(db, open());

   189         QSqlQuery q1(db), q2(db);

   190         QVERIFY_SQL(q2, prepare("delete from " + qTableName("test") + " where id = :id"));

   191 

   192         for (int i = 0; i < ProdConIterations; ++i) {

   193             QVERIFY_SQL(q1, exec("select max(id) from " + qTableName("test")));

   194             q1.first();

   195             q2.bindValue("id", q1.value(0));

   196             q1.clear();

   197             QVERIFY_SQL(q2, exec());

   198 #ifdef Q_OS_LINUX

   199             pthread_yield();

   200 #endif

   201         }

   202     }

   203 

   204     void run()

   205     {

   206         QString dbName = QString("Consumer%1").arg((size_t)currentThreadId());

   207         runHelper(dbName);

   208         QSqlDatabase::database(dbName).close();

   209         QSqlDatabase::removeDatabase(dbName);

   210     }

   211 

   212 private:

   213     QSqlDatabase sourceDb;

   214 };

   215 

   216 class SqlThread: public QThread

   217 {

   218     Q_OBJECT

   219 

   220 public:

   221     enum Mode { SimpleReading, PreparedReading, SimpleWriting, PreparedWriting };

   222 

   223     SqlThread(Mode m, const QSqlDatabase &db, QObject *parent = 0)

   224         : QThread(parent), sourceDb(db), mode(m) {}

   225 

   226     void run()

   227     {

   228         QSqlDatabase &db = sourceDb;

   229         switch (mode) {

   230         case SimpleReading: {

   231             // Executes a Query for reading, iterates over the first 4 results

   232             QSqlQuery q(sourceDb);

   233             for (int j = 0; j < ProdConIterations; ++j) {

   234                 QVERIFY_SQL(q, exec("select id,name from " + qTableName("test") + " order by id"));

   235                 for (int i = 1; i < 4; ++i) {

   236                     QVERIFY_SQL(q, next());

   237                     QCOMPARE(q.value(0).toInt(), i);

   238                 }

   239             }

   240             break; }

   241         case SimpleWriting: {

   242             // Executes a query for writing (appends a new row)

   243             QSqlQuery q(sourceDb);

   244             for (int j = 0; j < ProdConIterations; ++j) {

   245                 QVERIFY_SQL(q, exec(QString("insert into " + qTableName("test")

   246                                 + " (id, name) values(%1, '%2')")

   247                                       .arg(counter.fetchAndAddRelaxed(1)).arg("Robert")));

   248             }

   249             break; }

   250         case PreparedReading: {

   251             // Prepares a query for reading and iterates over the results

   252             QSqlQuery q(sourceDb);

   253             QVERIFY_SQL(q, prepare("select id, name from " + qTableName("test") + " where id = ?"));

   254             for (int j = 0; j < ProdConIterations; ++j) {

   255                 q.addBindValue(j % 3 + 1);

   256                 QVERIFY_SQL(q, exec());

   257                 QVERIFY_SQL(q, next());

   258                 QCOMPARE(q.value(0).toInt(), j % 3 + 1);

   259             }

   260             break; }

   261         case PreparedWriting: {

   262             QSqlQuery q(sourceDb);

   263             QVERIFY_SQL(q, prepare("insert into " + qTableName("test") + " (id, name) "

   264                                      "values(?, ?)"));

   265             for (int i = 0; i < ProdConIterations; ++i) {

   266                 q.addBindValue(counter.fetchAndAddRelaxed(1));

   267                 q.addBindValue("Robert");

   268                 QVERIFY_SQL(q, exec());

   269             }

   270             break; }

   271         }

   272     }

   273 

   274 private:

   275     QSqlDatabase sourceDb;

   276     Mode mode;

   277 };

   278 

   279 

   280 tst_QSqlThread::tst_QSqlThread()

   281     : threadFinishedCount(0)

   282 {

   283 }

   284 

   285 tst_QSqlThread::~tst_QSqlThread()

   286 {

   287 }

   288 

   289 void tst_QSqlThread::generic_data(const QString& engine)

   290 {

   291     if ( dbs.fillTestTable(engine) == 0 ) {

   292         if(engine.isEmpty())

   293            QSKIP( "No database drivers are available in this Qt configuration", SkipAll );

   294         else

   295            QSKIP( (QString("No database drivers of type %1 are available in this Qt configuration").arg(engine)).toLocal8Bit(), SkipAll );

   296     }

   297 }

   298 

   299 void tst_QSqlThread::dropTestTables()

   300 {

   301     for (int i = 0; i < dbs.dbNames.count(); ++i) {

   302         QSqlDatabase db = QSqlDatabase::database(dbs.dbNames.at(i));

   303         QSqlQuery q(db);

   304 

   305         tst_Databases::safeDropTables(db, QStringList() << qTableName("test") << qTableName("test2") << qTableName("emptytable"));

   306     }

   307 }

   308 

   309 void tst_QSqlThread::createTestTables()

   310 {

   311     for (int i = 0; i < dbs.dbNames.count(); ++i) {

   312         QSqlDatabase db = QSqlDatabase::database(dbs.dbNames.at(i));

   313         QSqlQuery q(db);

   314 

   315         QVERIFY_SQL(q, exec("create table " + qTableName("test")

   316                        + "(id int NOT NULL primary key, name varchar(20), title int)"));

   317 

   318         QVERIFY_SQL(q, exec("create table " + qTableName("test2")

   319                        + "(id int NOT NULL primary key, title varchar(20))"));

   320 

   321         QVERIFY_SQL(q, exec("create table " + qTableName("emptytable")

   322                        + "(id int NOT NULL primary key)"));

   323     }

   324 }

   325 

   326 void tst_QSqlThread::repopulateTestTables()

   327 {

   328     for (int i = 0; i < dbs.dbNames.count(); ++i) {

   329         QSqlDatabase db = QSqlDatabase::database(dbs.dbNames.at(i));

   330         QSqlQuery q(db);

   331 

   332         QVERIFY_SQL(q, exec("delete from " + qTableName("test")));

   333         QVERIFY_SQL(q, exec("insert into " + qTableName("test") + " values(1, 'harry', 1)"));

   334         QVERIFY_SQL(q, exec("insert into " + qTableName("test") + " values(2, 'trond', 2)"));

   335         QVERIFY_SQL(q, exec("insert into " + qTableName("test") + " values(3, 'vohi', 3)"));

   336 

   337         QVERIFY_SQL(q, exec("delete from " + qTableName("test2")));

   338         QVERIFY_SQL(q, exec("insert into " + qTableName("test2") + " values(1, 'herr')"));

   339         QVERIFY_SQL(q, exec("insert into " + qTableName("test2") + " values(2, 'mister')"));

   340     }

   341 }

   342 

   343 void tst_QSqlThread::recreateTestTables()

   344 {

   345     dropTestTables();

   346     createTestTables();

   347     repopulateTestTables();

   348 }

   349 

   350 void tst_QSqlThread::initTestCase()

   351 {

   352     dbs.open();

   353     recreateTestTables();

   354 }

   355 

   356 void tst_QSqlThread::cleanupTestCase()

   357 {

   358     dropTestTables();

   359     dbs.close();

   360 }

   361 

   362 void tst_QSqlThread::init()

   363 {

   364     threadFinishedCount = 0;

   365     counter = 4;

   366 }

   367 

   368 void tst_QSqlThread::cleanup()

   369 {

   370 //     repopulateTestTables();

   371 }

   372 

   373 // This test creates two threads that clone their db connection and read

   374 // from it

   375 void tst_QSqlThread::simpleThreading()

   376 {

   377     QFETCH(QString, dbName);

   378     QSqlDatabase db = QSqlDatabase::database(dbName);

   379     CHECK_DATABASE(db);

   380 

   381     if (db.databaseName() == ":memory:")

   382         QSKIP("does not work with in-memory databases", SkipSingle);

   383 

   384     QtTestSqlThread t1(db);

   385     QtTestSqlThread t2(db);

   386 

   387     connect(&t1, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   388     connect(&t2, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   389 

   390     t1.start();

   391     t2.start();

   392 

   393     while (threadFinishedCount < 2)

   394         QTest::qWait(100);

   395 }

   396 

   397 // This test creates two threads that clone their db connection and read

   398 // or write

   399 void tst_QSqlThread::readWriteThreading()

   400 {

   401     QFETCH(QString, dbName);

   402     QSqlDatabase db = QSqlDatabase::database(dbName);

   403     CHECK_DATABASE(db);

   404 

   405     if (db.databaseName() == ":memory:")

   406         QSKIP("does not work with in-memory databases", SkipSingle);

   407     else if (tst_Databases::isMSAccess(db))

   408         QSKIP("does not work with MS Access databases", SkipSingle);

   409 

   410     SqlProducer producer(db);

   411     SqlConsumer consumer(db);

   412 

   413     connect(&producer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   414     connect(&consumer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   415 

   416     producer.start();

   417     consumer.start();

   418 

   419     while (threadFinishedCount < 2)

   420         QTest::qWait(100);

   421 }

   422 

   423 // run with n threads in parallel. Change this constant to hammer the poor DB server even more

   424 static const int maxThreadCount = 4;

   425 

   426 void tst_QSqlThread::readFromSingleConnection()

   427 {

   428 #ifdef QOCI_THREADED

   429     QFETCH(QString, dbName);

   430     QSqlDatabase db = QSqlDatabase::database(dbName);

   431     CHECK_DATABASE(db);

   432 

   433     if (db.databaseName() == ":memory:")

   434         QSKIP("does not work with in-memory databases", SkipSingle);

   435 

   436     QObject cleanupHelper; // make sure the threads die when we exit the scope

   437     for (int i = 0; i < maxThreadCount; ++i) {

   438         SqlThread *reader = new SqlThread(SqlThread::SimpleReading, db, &cleanupHelper);

   439         connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   440         reader->start();

   441     }

   442 

   443     while (threadFinishedCount < maxThreadCount)

   444         QTest::qWait(100);

   445 #endif

   446 }

   447 

   448 void tst_QSqlThread::readWriteFromSingleConnection()

   449 {

   450 #ifdef QOCI_THREADED

   451     QFETCH(QString, dbName);

   452     QSqlDatabase db = QSqlDatabase::database(dbName);

   453     CHECK_DATABASE(db);

   454 

   455     if (db.databaseName() == ":memory:")

   456         QSKIP("does not work with in-memory databases", SkipSingle);

   457 

   458     QObject cleanupHelper;

   459     for (int i = 0; i < maxThreadCount; ++i) {

   460         SqlThread *reader = new SqlThread(SqlThread::SimpleReading, db, &cleanupHelper);

   461         connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   462         reader->start();

   463 

   464         SqlThread *writer = new SqlThread(SqlThread::SimpleWriting, db, &cleanupHelper);

   465         connect(writer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   466         writer->start();

   467     }

   468 

   469     while (threadFinishedCount < maxThreadCount * 2)

   470         QTest::qWait(100);

   471 #endif

   472 }

   473 

   474 void tst_QSqlThread::preparedReadWriteFromSingleConnection()

   475 {

   476 #ifdef QOCI_THREADED

   477     QFETCH(QString, dbName);

   478     QSqlDatabase db = QSqlDatabase::database(dbName);

   479     CHECK_DATABASE(db);

   480 

   481     if (db.databaseName() == ":memory:")

   482         QSKIP("does not work with in-memory databases", SkipSingle);

   483 

   484     QObject cleanupHelper;

   485     for (int i = 0; i < maxThreadCount; ++i) {

   486         SqlThread *reader = new SqlThread(SqlThread::PreparedReading, db, &cleanupHelper);

   487         connect(reader, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   488         reader->start();

   489 

   490         SqlThread *writer = new SqlThread(SqlThread::PreparedWriting, db, &cleanupHelper);

   491         connect(writer, SIGNAL(finished()), this, SLOT(threadFinished()), Qt::QueuedConnection);

   492         writer->start();

   493     }

   494 

   495     while (threadFinishedCount < maxThreadCount * 2)

   496         QTest::qWait(100);

   497 #endif

   498 }

   499 

   500 void tst_QSqlThread::transactionsFromSingleConnection()

   501 {

   502 #ifdef QOCI_THREADED

   503     QFETCH(QString, dbName);

   504     QSqlDatabase db = QSqlDatabase::database(dbName);

   505     CHECK_DATABASE(db);

   506 

   507     if (db.databaseName() == ":memory:")

   508         QSKIP("does not work with in-memory databases", SkipSingle);

   509 

   510     // start and commit a transaction

   511     QVERIFY_SQL(db, db.transaction());

   512     preparedReadWriteFromSingleConnection(); // read and write from multiple threads

   513     if (QTest::currentTestFailed())

   514         return;

   515     QVERIFY_SQL(db, db.commit());

   516 

   517     // reset test environment

   518     threadFinishedCount = 0;

   519 

   520     // start and roll back a transaction

   521     QVERIFY_SQL(db, db.transaction());

   522     preparedReadWriteFromSingleConnection(); // read and write from multiple threads

   523     if (QTest::currentTestFailed())

   524         return;

   525     QVERIFY_SQL(db, db.rollback());

   526 #endif

   527 }

   528 

   529 QTEST_MAIN(tst_QSqlThread)

   530 #include "tst_qsqlthread.moc"