uonedb-qt team mailing list archive

[Pete Woods <pete.woods@xxxxxxxxxxxxx>]

Pete Woods has proposed merging lp:~pete-woods/u1db-qt/transaction-around-schema-init into lp:u1db-qt.

Commit message:
Improve database init performance by adding transactions

Requested reviews:
  U1DB Qt developers (uonedb-qt)

For more details, see:
https://code.launchpad.net/~pete-woods/u1db-qt/transaction-around-schema-init/+merge/226221

Improve database init performance by adding transactions
-- 
https://code.launchpad.net/~pete-woods/u1db-qt/transaction-around-schema-init/+merge/226221
Your team U1DB Qt developers is requested to review the proposed merge of lp:~pete-woods/u1db-qt/transaction-around-schema-init into lp:u1db-qt.

=== modified file 'src/database.cpp'
--- src/database.cpp	2014-02-17 16:52:42 +0000
+++ src/database.cpp	2014-07-09 22:38:27 +0000
@@ -34,6 +34,29 @@
 
 QT_BEGIN_NAMESPACE_U1DB
 
+namespace
+{
+class ScopedTransaction
+{
+public:
+    ScopedTransaction(QSqlDatabase &db) :
+            m_db(db), m_transaction(false)
+    {
+        m_transaction = m_db.transaction();
+    }
+
+    ~ScopedTransaction() {
+        if (m_transaction) {
+            m_db.commit();
+        }
+    }
+
+    QSqlDatabase &m_db;
+
+    bool m_transaction;
+};
+}
+
 /*!
     \class Database
     \inmodule U1Db
@@ -134,6 +157,8 @@
             QFile file(":/dbschema.sql");
             if (file.open(QIODevice::ReadOnly | QIODevice::Text))
             {
+                ScopedTransaction t(m_db);
+
                 while (!file.atEnd())
                 {
                     QByteArray line = file.readLine();
@@ -593,6 +618,8 @@
     if (!initializeIfNeeded())
         return "";
 
+    ScopedTransaction t(m_db);
+
     QString newOrEmptyDocId(docId);
     QVariant oldDoc = newOrEmptyDocId.isEmpty() ? QVariant() : getDocUnchecked(newOrEmptyDocId);
 
@@ -749,6 +776,8 @@
     if (!initializeIfNeeded())
         return QString("Database isn't ready");
 
+    ScopedTransaction t(m_db);
+
     QStringList results = getIndexExpressions(indexName);
     bool changed = false;
     Q_FOREACH (QString expression, expressions)
@@ -758,15 +787,24 @@
         return QString("Index conflicts with existing index");
 
     QSqlQuery query(m_db.exec());
+    query.prepare("INSERT INTO index_definitions VALUES (:indexName, :offset, :field)");
+
+    QVariantList indexNameData;
+    QVariantList offsetData;
+    QVariantList fieldData;
     for (int i = 0; i < expressions.count(); ++i)
     {
-        query.prepare("INSERT INTO index_definitions VALUES (:indexName, :offset, :field)");
-        query.bindValue(":indexName", indexName);
-        query.bindValue(":offset", i);
-        query.bindValue(":field", expressions.at(i));
-        if (!query.exec())
-            return QString("Failed to insert index definition: %1\n%2").arg(m_db.lastError().text()).arg(query.lastQuery());
+        indexNameData << indexName;
+        offsetData << i;
+        fieldData << expressions.at(i);
     }
+    query.addBindValue(indexNameData);
+    query.addBindValue(offsetData);
+    query.addBindValue(fieldData);
+
+    if (!query.execBatch())
+        return QString("Failed to insert index definition: %1\n%2").arg(m_db.lastError().text()).arg(query.lastQuery());
+
     return QString();
 }