Folding-Hyperspace

2012-07 Update

While I make use of QSqlTableModel::setRecord to set new values when the user performs a submit operation, this may not work with some databases in all cases. What works with SQLite and MySQL doesn't appear to work correctly with MS SQL using the ODBC interface. If your data table contains a primary key field, setRecord seems to attempt to change it and fails, even though you didn't change the key in the record. This can be fixed by simply using the QAbstractItemModel::setData base method instead, which allows you to select specific fields to modify instead of the whole record.

Easy Qt Database Forms #2

This is a continued discussion of Easy Qt Database Forms #1 so please refer to the first article for background information if necessary. There are basically three ways to design Qt database forms:

• Model/View: easy to use but restricted to the built in view editors unless you want to add custom delegates. This can be quite a bit of work if you need a custom data editor.
• QDataWidgetMapper: combine this object with a data model and it causes the data-aware widgets to synchronize with the model for both input and output. This doesn't work with all field types if your data field structure is complex or you need specialized conversions.
• Direct Data-Aware Widgets: use the Qt data-aware widgets and add program glue to make them function as explained in the first article. Note that data-aware widgets aren't really aware because they know nothing on their own about the database model or how to respond to it. They are simply widgets that can hold data in a format that is useful with database field data.

Now I would like to expand on the third method, showing how to use QSignalMapper to automatically control data-aware widgets from a database model such as QSqlTableModel. This could also possibly be applied to other models that are not linked to a database.

I was recently working on a new project that required me to write an editor for an old ODCB .mdb format database and convert it into a newer SQLite format. The editor should ideally work with both types of database. While I could have used QDataWidgetMapper for the job, I needed to support many custom features with how the data-aware widgets worked, such as changing their suffix text and doing input/output conversions between the field value and display value when a metric boolean field was turned on or off and also making various fields visible or invisible based on other system field settings. I also like to use QComboBox widgets to control fields where I need to map a database code value to the index of the combo box through a map. This way I can easily add new code values to the field and their corresponding line in the combo box without disturbing any of the existing codes that were used in the database in a previous version. This particular database also had hundreds of fields in several tables which would require a lot of glue code using the way I did it in the first article.

What I needed was a good way of finding all the widget objects on the form and associating them with the database fields. Here is the design method I came up with to make this happen.

Setting the data-aware widgets on form creation and table record changes.

Qt provides some very helpful QObject meta functions for doing this. I decided to name my data widgets with the same name as the database field they would edit allowing the objectName property to be used as the field name when I wanted to access the corresponding field in the database. In this example the Metric check box field name is metricConvert.

When I first show the form or change the row pointer I call loadDbControls to load all my data-aware controls.

• I used buttons named Next and Previous to control a dbCurrentRow integer that specifies the table row I am working on.
• Before doing anything I set a global disableControls boolean, as done in the first article, to prevent any of the data-aware widget slots from making changes to the database when I change their values. The setValue method may trigger the valueChanged signal which could cause unnecessary actions like changing the field by the data-aware widget.
• I check my dbCurrentRow pointer to make sure it is in the range of allowed rows. I only set widget values if it is in range.
• I pull QSqlRecord variables for each of the tables that I am going to check field values for.
• Note that the sysid table only has one record so I don't use dbCurrentRow on it.
• Although I don't show it here, I load a global metricConvert boolean from the record. It is used in the next section to control how fields are converted after input and what suffix to assign to the data-aware widgets that access units.

I then set each type of data-aware widget as follows. The following points are important for this step:

• All the data-aware widgets are children of the editTabs widget so I use the findChildren method to find them.
• I set the different data-aware widgets in separate loops because the setup can be different depending on the type of widget.
• I loop through the list and use qobject_cast to make sure that the object is really a QSpinBox before using it.
• I set some properties for the widgets.
• I set global QStringList lists with field names in the form constructor and could compare them to the object name to select different setups for fields if necessary. The global metricConvert boolean also plays a part in conversion of the field values for display. I'm not showing this more complex setup here.
• I check the object name against the QSqlRecord variables for each table to see if that field is defined in the table.
• I test the index values and use them to set the widget from the table value if they exist in the table.

At the end of loadDbControls I set the disableControls boolean back to the initial value. The clearDbControls method simply clears all the data-aware widgets should my row pointer go out of range.

Pushing the data back into the database model on the data-aware widget value change signals.

In the first article I showed how to use the value changed signal of each data-aware widget to set the database field. This required setting up a slot for each widget and adding code to change the specific field. Now is the time to find a use for QSignalMapper and simplify this process.

In my form constructor I setup the models for the databases and also use some QStringList lists to set field names that will drive how I set the data-aware widgets when loading them with values. This allows me to use a different suffix and conversion values for second, feet or inch field units when setting the data-aware widgets in the loadDbControls method.

I now obtain a list of all QWidget objects in the editTabs object using the findChildren method. I then use qobject_cast to set pointers to the different data-aware widgets I used.

Note that if this parent widget had some widgets of these types that weren't linked to database fields that I might not want to connect their signals through signalMapper. If I forgot to do this, it would not be too serious. The slot that received the events would ignore them if they were not a field name and any slot connected to the widget by other means would still get a signal because Qt delivers signals to all registered slots.

The QSignalMapper object is then created and used to pass the proper value changed signal for the widget type through to a single slot called mapped_value_change. Note that the very useful QSignalMapper mapped signal allows me to pass a pointer of the widget that created the signal. I finally set the dbCurrentRow row pointer variable to the first model row.

At this point I can set the global disableControls boolean to false and call the loadDbControls method to set the initial data-aware widget values.

Building the single master value changed slot.

Here is the start of the single mapped_value_change slot used to set the database fields.

• Thanks to QSignalMapper, I get a pointer to the widget that sent the signal.
• I check the disableControls boolean and return if it was set.
• I make sure the dbCurrentRow table row pointer is in range.
• I use qobject_cast to convert the widget pointer into the three possible data-aware widget types I used.
• I get the field name from the widget's objectName property.
• I pull QSqlRecord variables for each table and also index values for the field name.
• Note that the sysid table only has one record so I don't use dbCurrentRow on it.

I can now test the different object pointers to see if they are valid and set the fields. The global QStringList lists I set in the constructor allow me to select the proper units if necessary. The value metricConvert is a global boolean that was synchronized with the corresponding database field when I loaded the controls in the loadDbControls method and is tested to pick the proper conversion value.

Once I get valueConvert set correctly I can store the field.

If the widget wasn't the double spin box I check the integer spin box which doesn't require as much value processing because it isn't unit based.

(code is omitted here)

At the end of my mapped_value_change slot I'm setting the check box values.

• I need a final check for a change to the metricConvert field which triggers calls to updateDb which submits all the data, and also loadDbControls to reload the metric values.
• If my dbCurrentRow value was invalid I also force a submit of all data and a field reload.

Well, that is really all there is too this way of running our data controls. We have seen how QSignalMapper allows us to take hundreds of data-aware widgets and easily set all their value changed signals into a single slot that can process all the fields at once. I find that this is easier than messing with custom delegates in a QTableView, or using QDataWidgetMapper, which may not properly map the type of the field to the widget. It gives me total control over how I display and operate my database field widgets and can be easily customized by using QStringList lists of field names with tests for special cases.