Locking an SQLite database

Today I will return to the topic of SQLite once again. This time I will discuss why and how to lock an SQLite database for exclusive access to make sure that it's not modified by other processes.

Of course one of the goals of SQLite, like most database engines, is concurrency - i.e. the ability for multiple processes to read and write data to the same database. As we can read in the documentation, "in order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held".

That makes perfect sense if we use SQLite with a web application, which should handle as many concurrent requests as possible. But there are some scenarios when we might want to keep the database locked for exclusive access. For example, an SQLite database is often used as a cache for various data. If we run two instances of a program and both use the same cache, it may sometimes lead to strange results. Sometimes it's just easier to prevent such situation than to try to handle it. As you can guess, that's the situation I came across in the WebIssues client.

One way to achieve this is to simply use one large transaction throughout the lifetime of the connection. The documentation advises such solution when the database is used as the application file format. However, sometimes we want all modifications to be committed as soon as possible, for example to prevent losing data on a crash, while still keeping an exclusive lock as long as the connection is open.

Fortunately, SQLite has a useful pragma statement called locking_mode. If we set it to EXCLUSIVE, the obtained locks are never released until the connection is closed. However, the name of this setting is slightly misleading, because it doesn't really obtain an exclusive lock, it just ensures that the lock is never released. So perhaps "persistent" locking mode would be a better name.

In order to lock the database, we need to perform the following sequence of commands:

  • PRAGMA locking_mode = EXCLUSIVE

The BEGIN EXCLUSIVE command actually locks the database. It begins an empty transaction, which doesn't change anything, but ensures that the exclusive lock is acquired on the database. If the database is already locked by another process, this command will fail with the SQLITE_BUSY error.

Note that the standard Qt driver for SQLite sets the busy timeout to 5 seconds by default, so the application will freeze for a while before reporting an error. We can change it using the QSQLITE_BUSY_TIMEOUT connection option, or when using a custom driver, by simply changing the parameter of sqlite3_busy_timeout to a smaller value.

Filed under: Blog

QComboBox with separator

I haven't written for quite some time mostly because I'm getting more and more busy with WebIssues. Take a look at the online demo of the latest version, 1.0-beta2. But here's another post in my Qt series.

In version 4.4 of Qt a long awaited feature was added to the QComboBox widget. It is now possible to create separator items, which are drawn as thin, gray lines and cannot be selected. Before 4.4 it was necessary to create a custom item delegate, and also to add a few hacks in order to correctly calculate the height of the popup list and to make it impossible to select the separator using up and down arrow keys. Now we can simply use the insertSeparator method.

Perhaps it's a matter of taste, but I don't like that default separator. It has no space above and below and it is basically hardly visible. However we can change the way it looks by simply implementing a custom QItemDelegate and replacing the default delegate using setItemDelegate.

If you look at the source code of the combo box, or actually the internal QComboBoxDelegate class, you will see that a separator item has the Qt::AccessibleDescriptionRole data set to the string "separator". We can take advantage of this and increase the height of the separator to 5 pixels:

QSize sizeHint( const QStyleOptionViewItem& option, const QModelIndex& index ) const
    QString type = Qt::AccessibleDescriptionRole ).toString();
    if ( type == QLatin1String( "separator" ) )
        return QSize( 5, 5 );
    return QItemDelegate::sizeHint( option, index );

We can also override the paint method in order to draw a dark, horizontal line:

void paint( QPainter* painter, const QStyleOptionViewItem& option,
    const QModelIndex& index ) const
    QString type = Qt::AccessibleDescriptionRole ).toString();
    if ( type == QLatin1String( "separator" ) ) {
        QItemDelegate::paint( painter, option, index );
        int y = ( + option.rect.bottom() ) / 2;
        painter->setPen(  option.palette.color( QPalette::Active, QPalette::Dark ) );
        painter->drawLine( option.rect.left(), y, option.rect.right(), y );
    } else {
        QItemDelegate::paint( painter, option, index );

It's convenient to create a custom class which inherits QComboBox and automatically creates the custom delegate in the constructor. Then we can also add the missing, but very useful addSeparator method:

void addSeparator()
    insertSeparator( count() );

The custom delegate allows us to do much more interesting things. A nice feature of the <select> element in HTML is the ability to create groups of items. Parent items are displayed using bold font and are cannot be selected. Child items, on the other hand, are slightly indented. Here's an example:

Select element

It's easy to do something like this using a regular QComboBox in Qt. Assuming that the combo box uses the standard model, we can add the following helper method to add a "parent" item of a group:

void addParentItem( const QString& text )
    QStandardItem* item = new QStandardItem( text );
    item->setFlags( item->flags() & ~( Qt::ItemIsEnabled | Qt::ItemIsSelectable ) );
    item->setData( "parent", Qt::AccessibleDescriptionRole );

    QFont font = item->font();
    font.setBold( true );
    item->setFont( font );

    QStandardItemModel* itemModel = (QStandardItemModel*)model();
    itemModel->appendRow( item );

We make the item disabled and non-selectable in the same way as Qt does in case of separators. We also indicate that it's a parent item using the accessible description data. Finally we make the item's default font bold. The code for adding child items is even simpler:

void SeparatorComboBox::addChildItem( const QString& text, const QVariant& data )
    QStandardItem* item = new QStandardItem( text + QString( 4, QChar( ' ' ) ) );
    item->setData( data, Qt::UserRole );
    item->setData( "child", Qt::AccessibleDescriptionRole );

    QStandardItemModel* itemModel = (QStandardItemModel*)model();
    itemModel->appendRow( item );

We append 4 spaces to the item's text so that it's size is adjusted, while making the item still selectable by typing a few first letters. We also set the user data (for compatibility with addItem) and indicate that it's a child item. Now we have to slightly customize the way those special types of items are painted by the delegate:

    if ( type == QLatin1String( "parent" ) ) {
        QStyleOptionViewItem parentOption = option;
        parentOption.state |= QStyle::State_Enabled;
        QItemDelegate::paint( painter, parentOption, index );
    } else if ( type == QLatin1String( "child" ) ) {
        QStyleOptionViewItem childOption = option;
        int indent = option.fontMetrics.width( QString( 4, QChar( ' ' ) ) );
        childOption.rect.adjust( indent, 0, 0, 0 );
        childOption.textElideMode = Qt::ElideNone;
        QItemDelegate::paint( painter, childOption, index );

We paint the parent item as if it was enabled, otherwise it would be grayed. We also adjust the rectangle of the child item by the width of four spaces to make the indent.

Note that the width of "text " may sometimes be slightly smaller than the sum of widths of " " and "text" because of font kerning. The longest item may sometimes appear truncated (with "..." at the end). To prevent this, we disable the elide mode.

Filed under: Blog

QCompleter with multi-selection

The QCompleter class offers an out-of-the-box functionality of automatically suggesting matching items when typing in the associated QLineEdit. It can be used as a nice replacement for QComboBox when there is a lot of items or when the user can enter a free text (not constrained to one of the items).

One important functionality that the QCompleter lack is the ability to complete a list of items separated with commas. Such completer, together with the line edit widget, could be used as a simple combo box with multi-selection, for editing comma separated tags, etc. Quick googling revealed two simple solutions: this and this. They are both written in Python, but can be easily translated to C++. The following code snippet is based on the first, simpler and much more elegant example. Obviously it's a bit more verbose that its equivalent in Python, but I personally find it easier to understand (perhaps because Python is not my native language ;).

The idea is based on the concept of "path", which is originally used to support completion based on tree models (for example the folders and files in the file system), but it can be slightly abused to support multi-selection. In this implementation, the path is not based on the model's index (because our model is a flat list of strings, not a tree), but rather on the original contents of the associated line edit widget, with the part after the last comma replaced with the selected item.

class MultiSelectCompleter : public QCompleter
    MultiSelectCompleter( const QStringList& items, QObject* parent );

    QString pathFromIndex( const QModelIndex& index ) const;
    QStringList splitPath( const QString& path ) const;

MultiSelectCompleter::MultiSelectCompleter( const QStringList& items, QObject* parent )
    : QCompleter( items, parent )


QString MultiSelectCompleter::pathFromIndex( const QModelIndex& index ) const
    QString path = QCompleter::pathFromIndex( index );

    QString text = static_cast<QLineEdit*>( widget() )->text();

    int pos = text.lastIndexOf( ',' );
    if ( pos >= 0 )
         path = text.left( pos ) + ", " + path;

    return path;

QStringList MultiSelectCompleter::splitPath( const QString& path ) const
    int pos = path.lastIndexOf( ',' ) + 1;

    while ( pos < path.length() && pos ) == QLatin1Char( ' ' ) )

    return QStringList( path.mid( pos ) );

Filed under: Blog

Careful with QString::fromRawData!

In one of my posts, Qt, SQLite and locale aware collation, I showed that raw strings in UTF-16 can be efficiently wrapped into a QString by using its fromRawData static method. According to the Qt documentation, "the caller must guarantee that this raw string will not be deleted or modified as long as the QString (or an unmodified copy of it) exists". The statement in parentheses is the tricky part. Let's consider the following example:

void callback( const wchar_t* data, int len )
    QString string = QString::fromRawData( reinterpret_cast<const QChar*>( data ), len );

    doSomething( string );

The data passed to the callback function is guaranteed to be valid only as long as the function is being executed. The QString object is created on the stack, so it is destroyed before the callback function exists. So far, the condition for using fromRawData is fulfilled. The string is also passed another function called doSomething.

Everything is fine if doSomething is a simple, stateless function with no side effects. An example of such function is QString::localeAwareCompare, which I used in the mentioned post. It simply compares two strings character by character to determine their lexical order.

But let's see what happens if doSomething was declared like this:

void doSomething( const QString& string )
    if ( string == lastString )

    // ...

    lastString = string;

Notice that this function has a state - the lastString variable, which can be global, static, or member of some object. The lifetime of this state variable exceeds the scope of the function, and also the callback function. Also, it happens to be an unmodified copy of the string which was created using QString::localeAwareCompare! The next time the callback is called, lastString may point to random garbage, because the previous data may be no longer valid.

This type of error is very difficult to notice, neither by code analysis nor by symptoms. It is not always evident that doSomething makes a copy of the string. In case of a setter function like setName it is quite obvious. But when we pass the string to the constructor of a QRegExp, we would expect that the string should only remain valid as long as the regular expression object exists. Wrong! The Qt regular expressions engine caches compiled state machines, so if we create another QRegExp and pass the same expression to it, it doesn't have to be parsed again. Of course this is more complex than the example shown above, but it surely involves creating an unmodified copy of the string that exceeds the lifetime of our function.

The term "unmodified copy" is not very precise. Actually it means a "shallow copy", which is usually created by Qt when assigning one string to another to avoid allocating memory and copying all data (which is called a "deep copy" and is safe, because the original data is no longer referenced).

But in some cases it's not even clear whether we're creating a deep copy or a shallow copy. Let's consider another example:

QString a = "foo";
QString b;
b.append( a );

We would expect that append always creates a deep copy of data, because it modifies the target string. But it turned out (and I learned this the hard way) that if some string is appended to a null string, only a shallow copy of it is created, as an optimization. If we use an empty string instead:

QString a = "foo";
QString b = "";
b.append( a );

then "b" becomes a deep copy of "a", as expected.

Such errors are difficult to track also because they rarely cause a crash. In case of a SQLite callback, the passed data will usually remain valid, if the database is not very big, and we will not notice any error. In other situations, the data may be overwritten and we will notice a strange behavior, but it may be difficult to debug.

Of course it doesn't mean that we shouldn't use fromRawData - it is very useful, especially in time intensive calculations, but it has to be used with extra care. In case of doubt, it's safer to use fromUtf16 instead.

Filed under: Blog

QSqlQueryModel and QTreeView - part 3

In the previous post I started describing the SqlTreeModel which combines multiple QSqlQueryModels so that they can be used in a QTreeView.

We already know how to map items from multiple SQL models in order to create a hierarchy of items, how to map rows and columns from the SqlTreeModel into the SQL models, and how to implement the five pure virtual methods of QAbstractItemModel.

Our tree model already works, but items cannot be sorted by clicking on the column header. There are several ways to do that. One could use the QSqlTableModel which has built-in sorting support, but I find it easier to use the more generic QSqlQueryModel as it is more flexible and allows writing SQL queries by hand. Although it doesn't support sorting directly, the query can be modified so that it includes different ORDER BY clauses to achieve the same effect.

QAbstractItemModel has a virtual method called sort, which is called by the view when the user clicks on a column header. The default implementation does nothing, so we should provide a custom implementation in our derived class, SqlTreeModel. We should determine the order clauses based on the passed sort column index and order, pass new queries to QAbstractItemModels and rebuild the tree.

In fact, since QSqlTableModel is generic and can be reused by multiple different models with different queries, the sort only remembers the sort column and order and calls another virtual method, updateQueries, which is implemented by actual models. Individual models can extend this mechanism, and call this method, for example, when some filtering criteria are changed.

Some columns may be irrelevant for specific levels of items. For example, in WebIssues, the projects tree has two columns, Name and Type, but Type is only relevant for folders, and not for projects or alerts. When sorting by Type, the queries for projects and alerts are not modified, so the previous sorting order is retained at these levels, which produces logical and predictable results.

The last problem is that when the tree is rebuilt (for example, after changing the sort order), the view should be updated, but we don't wan't the current selection or expanded state of nodes to be lost, so we shouldn't simply call reset (which is what the standard SQL models do). Instead we should use the layoutAboutToBeChanged and layoutChanged signals.

The first signal indicates that the view should prepare for changes in the model. The second signal indicates that the changes are completed and the view should update itself. We also have to retrieve persistent indexes just after signalling layoutAboutToBeChanged and update these indexes to reflect changes in the model just before signalling layoutChanged.

How to map old indexes to new indexes? While updating the model, some rows may be added, some may be deleted, and some may be reordered. However, the identifiers (i.e. primary keys) of existing items will not change. So we should first map old indexes to levels and identifiers of corresponding items. After updating the tree, we should map levels and identifiers back to new indexes. To do that, we need a function which will find the index of the item with specified identifier at the specified level, but with the internal structures used to index the tree, it's an easy task. Such function is also useful, for example, if we wan't to select an item with given identifier.

The complete SqlTreeModel class is part of version 1.0 of the WebIssues client and it's available under the terms of the GNU General Public License. You can also use it, along with this series of posts, as a starting point for creating a custom model that goes beyond the standard functionality provided by the Qt framework.

Filed under: Blog
Syndicate content