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

     2 **

     3 ** Copyright (C) 2010 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 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", SkipAll );

   295         else

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

   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 = 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", SkipSingle);

   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", SkipSingle);

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

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

   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", SkipSingle);

   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", SkipSingle);

   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", SkipSingle);

   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", SkipSingle);

   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"