Interfacing with sql databases with Qt5
This post is on an issue that I struggled with very recently, while working on Q-SoPrA. What I wanted to achieve was relatively simple: I wanted to have tables that fetch data from sql databases, and in which one column shows check boxes to set/unset a boolean variable. The screenshot below shows an example.
What you see in the screen shot is QTableView widget that shows data that it fetches from a QSqlTableModel that interfaces with a table of a sqlite database. This post is about how to create the interactive check boxes shown in the right-most column. There are two main hurdles in getting the QTableView widget to work with check boxes:
- Sqlite databases don’t actually support boolean variables. You can use an integer variable to ‘simulate’ a boolean variable by setting it to
true), but you will of course need to do a bit of extra work to make the QSqlTableModel properly treat it as a boolean (or something that can be switched on or off) in read & write operations. This can be done by sub-classing the QSqlTableModel, and re-implementing its flags(), data() and setData() functions, as suggested here.
- But if you do that, you’ll have a quite ugly looking table, since the check boxes are all left-aligned in their column, with no obvious way to change that. In this case, the only solution seems to be to use a QStyledItemDelegate to handle the
paint()function that determines how the column is visualised. A somewhat outdated example of how to do that is offered in the Qt FAQ.
For good results in my use-case, where I want to have check boxes that (1) are able to handle a ‘pretend boolean’ variable (a boolean that is actually an integer) from a sqlite database, and (2) are not all aligned to the extreme left of their column, you have to combine the two solutions mentioned above. I haven’t really encountered a worked out example of this combination, which is why I decided to provide one in this post.
In the below, I briefly outline how you could achieve a result like the one shown in the screenshot above.
In case you are not familiar with Qt5...
Qt5 is a popular library for C++ that can be used for the development of Graphical User Interfaces (GUIs). It also includes a module that allows for easy interfacing of your program with sql databases. I make heavy use of the Qt5 library for the development of Q-SoPrA, including the possibilities it offers for interfacing with sqlite databases. Qt5 comes packed with a number of great sql database classes, such as QSqlDatabase, QSqlQueryModel, QSqlTableModel, QSqlRelationalTableModel, and QSqlQuery.
For the development of Q-SoPrA, I make use of most of all these classes, although often a sub-classed version of them in which I re-implemented some of their member functions.
I think a very common setup for interfacing with sql databases is to have a QSqlTableModel (or QSqlRelationalTableModel) that fetches data from a table in your sql database, a QSortFilterProxyModel that reads from the QSqlTableModel and acts as a 'filtering layer', and a QTableView that reads from the filter and displays the results on the user's screen. This is also the kind of setup that I use for several widgets that I included in Q-SoPrA, although I typically subclass the QSqlTableModel / QSqlRelationalTableModel to change some of the ways in which the data is presented to the user (such as tool tip behaviour).
An assumption about your ‘pretend boolean’ variable
In the below I make one main assumption about your ‘pretend boolean’ variable, which is that this variable is stored in one of the tables of your sql database as an integer, and that you programmatically set this integer to
1 whenever necessary.
There are of course multiple ways to achieve this. I tend to use the QSqlQuery class for reading and writing interactions with sql databases. Basically, this class allows you to write and execute an sql query like you normally would. In the snippet below, I have included a fictional example where I set our ‘pretend boolean’ variable to
1 depending on some conditional.
Reading the state of our ‘pretend boolean’ can also be done with an QSqlQuery object. Say that we want to read the state of this variable for all rows of our sql table. The following snippet would achieve something like that.
Now, if we want user interaction with our ‘pretend boolean’ variable to be handled by a check box, we need to sub-class the QSqlTableModel that interfaces between the sql table and the QTableView that visualises the data for the user. More specifically, we need to re-implement the
setData() member functions, and make our ‘pretend boolean’ (an integer set to
1) behave as an item that can be checked and unchecked (I believe it was this discussion that led me to this insight). The flags() function basically determines how items recorded in the QSqlTableModel can be manipulated (see this list of possible flags). We want to re-implement this function to make sure that items that correspond to our ‘pretend boolean’ can be checked or unchecked by the user. This can be achieved quite easily:
In the code snippet above, we make the assumption that our ‘pretend boolean’ is always recorded in the seventh column of our sql table. This may of course be different in your case. All we do is to check whether the current index being accessed exists in the seventh column. If yes, then we communicate to the program that the item at this index should be checkable by the user (we also make sure that we return all flags that are set by default). If no, then we revert to the default behaviour of the
QSqlTableModel::flags() member function.
Our sub-classed version of QSqlTableModel still won’t understand how to handle our ‘pretend boolean’ properly. For that, we also need to re-implement the
setData() functions. The data() and setData() are used to read data from, and write data to the sql table with which the QSqlTableModel is interfacing.
We should keep in mind here that in the QSqlTableModel, data are stored under different ‘roles’ (see an overview of these roles here). For example, data stored under the
Qt::DisplayRole are the data that are actually shown to the user in the QTableView, data stored under the
Qt::ToolTipRole are the data that are shown when the user hovers his/her mouse cursor over an entry in the table, and data stored under the
Qt::EditRole are the data that the user can manipulate in an editor. In the list of roles you will also find the
Qt::CheckStateRole, and this is the role that we want to (re-)implement for our ‘pretend boolean’.
Another thing we should keep in mind is that we don’t want all data in our sql table to be treated as ‘pretend booleans.’ We will want to keep the default behaviour of the
setData() functions in most cases. We can do that in the same way by as we did with the
flags() function: We create a special case for the column that holds our
pretend boolean variable, and we revert to the default implementation of
QSqlTableModel::SetData() in all other cases.
Actually, in the example below we have one other exception, which is the special case in which the user is hovering the mouse cursor over a cell, which will cause the QSqlTableModel to return data under the Qt::ToolTipRole.
Let’s start with our
data() function. See a snippet with its re-implemented version below.
So, the basic structure of this function is quite simple. We first check if the column of the sql table that is being accessed is the column with our
pretend boolean. If yes, then we check whether the data are being accessed under the ‘Qt::CheckStateRole’. If the answer is yes again, we run a block of code that reads the current value of
mark in the sql table (which can be
1, assuming that we have been consistent in our implementation of the interaction with this column of the sql table), and then returns the value
Qt::Checked if the value read is
1, or returns the value
Qt::Unchecked if the value read is
There are a few other situations that the re-implemented function handles. If we are accessing data in the column with our
pretend boolean, but we are not accessing the data under the
Qt::CheckState role, then the function simply returns an empty QVariant(), effectively returning nothing. I did this to make sure that the corresponding column in the QTableView only shows a check box that visualises the current check state, and nothing else. If we are accessing data in any other column, the function first checks whether we are accessing data under the
Qt::ToolTipRole. If yes, then we treat it as another special case, in which the user gets shown a tool tip that simply contains the visible contents of the cell currently being hovered over with the mouse cursor. If we are not accessing the data under the
Qt::ToolTipRole (in all other cases), we just revert to the default implementation of the
If you have re-implemented the
data() function in this way, then your QTableView should already show check boxes in the corresponding column. So far, so good. However, there are still a few problems. One problem is that the check boxes are aligned to the extreme left of the column, which makes the table look ugly. We will deal with this later. A more urgent problem is that checking / unchecking the check boxes won’t actually do anything meaningful with the underlying data, unless we also re-implement the
setData() function. Let’s do that next.
We have a similar kind of check to the one we had with the
data() function: We check whether thes column we are writing to is the column that contains our
pretend boolean variable. In this case, we check at the same time whether we are trying to write data under the
Qt::CheckStateRole. If yes, we check whether the corresponding check box was set to checked or to unchecked, and write a
1 to the sql table in the first case, or a
0 in the latter. In all other situations we simply refer to the default implementation of the
After re-implementing the
data() function this way, checking / unchecking the check boxes in our QTableView will actually do something meaningful with the data in the underlying sql table.
If you don’t care about the alignment of the check boxes in their corresponding column of the QTableView, then you’re done. However, I think that the table looks much nicer with the check boxes aligned to the centre of their column. How to achieve this is what I will discuss next.
Styling the column with the check boxes
It turns out that the only way to align the check boxes to the centre of their column is to use a QStyledItemDelegate. Well, there is another approach that uses layouts, but that only works if you (1) manually append another column to your QSqlTableModel, (2) explicitly create
QCheckBox objects, (3) assign these to a parent
QWidget object, (4) to which you then apply a centralised layout. This is quite an expensive procedure that can significantly reduce the performance of your program, and it only works well if you don’t update your QSqlTableModel frequently.
This is because every time that you update your QSqlTableModel the manually appended column will disappear. You can of course solve this by writing a function that creates the extra column, and fills it with your manually created check boxes, and run this function whenever the QSqlTableModel's select() function gets called. However, this will slow down your program quite a bit, and I also found some other drawbacks to this approach that are a bit outside the scope of this discussion.
The approach that uses a QStyledItemDelegate is actually what is recommended in Qt’s FAQ section. An example code snippet is offered there as well, although it is a bit outdated (it uses some functions that were deprecated in Qt5). Essentially, what you are required to do is to create your own sub-class of QStyledItemDelegate, and re-implement its
editorEvent() functions. The
paint() function determines how the check box is visualised to the user, and the
editorEvent() function determines how the user can interact with the check box.
I included the snippet with my slightly adapted version of the re-implemented functions below. I am not going to pretend that I grasp every detail of what happens in these functions. I mostly replaced some of the deprecated functions in the example offered in Qt’s FAQ.
The re-implemented version of the
paint() function basically just changes the position at which the check box is drawn, by changing the rectangle within which the paint event takes place, and then calling the default version of the
paint() function with an option parameter that includes the altered rectangle.
The re-implemented version of the
editorEvent() function handles various events through which the user might manipulate the current state of the check box. If an event meets the required conditions (e.g., a
QEvent::MouseButtonRelease took place within the rectangle where the check box is drawn), then we call the
setData() function (the one we re-implemented earlier) with the appropriate parameters.
There is actually one other thing we need to do if we want our QStyledItemDelegate to work. We need to tell our QTableView object to use the delegate in its seventh column. In my case, the QTableView object is named
tableView, and the sub-classed version of the QStyledItemDelegate I created is named
CheckBoxDelegate. I set
tableView to use the
CheckBoxDelegate by using the following function:
And that should do the trick! Now you should have a QTableView with a nice-looking column of check boxes through which the user can interact with ‘pretend boolean’ variables in your sql database.
Post a comment
All comments are held for moderation.