Creating Data-Aware Forms

Qt takes an innovative approach to database interaction with forms. Instead of having a separate database-enabled version of every built-in widget, Qt is able to make any widget data-aware, using QSqlForm and QSqlPropertyMap to relate database fields to widgets. Any built-in or custom widget can be made data-aware using these classes.

QSqlForm is a QObject subclass that makes it easy to create forms to browse or edit individual records in a database. The common pattern of usage is this:

1. Create the editor widgets (QLineEdits, QComboBoxes, QSpinBoxes, etc.) for the record's fields.
2. Create a QSqlCursor and move it to the record to edit.
3. Create a QSqlForm object.
4. Tell the QSqlForm which editor widget is bound to which database field.
5. Call the QSqlForm::readFields() function to populate the editor widgets with the data from the current record.
6. Show the dialog.
7. Call the QSqlForm::writeFields() function to copy the updated values back into the database.

To illustrate this, we will look at the code for the CdForm dialog. This dialog allows the user to create or edit a CD record. The user can specify the CD's title, artist, and release year, and the title and duration of each track.

Figure 12.4. The CdForm dialog

Let's start with the class definition:

class CdForm : public QDialog
{
 Q_OBJECT
public:
 CdForm(QWidget *parent = 0, const char *name = 0);
 CdForm(int id, QWidget *parent = 0, const char *name = 0);
 ~CdForm();

protected slots:
 void accept();
 void reject();

private slots:
 void addNewArtist();
 void moveTrackUp();
 void moveTrackDown();
 void beforeInsertTrack(QSqlRecord *buffer);
 void beforeDeleteTrack(QSqlRecord *buffer);

private:
 void init();
 void createNewRecord();
 void swapTracks(int trackA, int trackB);

 QLabel *titleLabel;
 QLabel *artistLabel;
 ...
 QDataTable *trackTable;
 QSqlForm *sqlForm;
 QSqlCursor *cdCursor;
 QSqlCursor *trackCursor;
 int cdId;
 bool newCd;
};

We have declared two constructors: one for inserting a new CD into the database, the other for updating an existing CD. The accept() and reject() slots are reimplemented from QDialog.

CdForm::CdForm(QWidget *parent, const char *name)
 : QDialog(parent, name)
{
 setCaption(tr("Add a CD"));
 cdId = -1;
 init();
}

The first constructor sets the dialog's caption to "Add a CD" and calls the private init() function to do the rest.

CdForm::CdForm(int id, QWidget *parent, const char *name)
 : QDialog(parent, name)
{
 setCaption(tr("Edit a CD"));
 cdId = id;
 init();
}

The second constructor sets the caption to "Edit a CD" and also calls init().

void CdForm::init()
{
 db = QSqlDatabase::database("CD");
 db->transaction();
 if (cdId == -1)
 createNewRecord();

In init(), we start a transaction using the "CD" database connection. We need to use different connections in CdForm and ArtistForm, because we can have both forms open at the same time, and we don't want one form to roll back the transaction initiated by the other form.

If we have no CD to operate on, we call the private function createNewRecord() to insert a blank one into the database. This will allow us to use the CD ID as a foreign key in the tracks' QDataTable. If the user clicks Cancel, we roll back the transaction and the blank record will disappear.

For this dialog, we use a different connection to the database than in the ArtistForm. This is because we can only have one active transaction per connection, and we can end up in a situation where we need two, for example, if the user clicks Add New to pop up the ArtistForm.

 titleLabel = new QLabel(tr("&Title:"), this);
 artistLabel = new QLabel(tr("&Artist:"), this);
 yearLabel = new QLabel(tr("&Year:"), this);
 titleLineEdit = new QLineEdit(this);
 yearSpinBox = new QSpinBox(this);
 yearSpinBox->setRange(1900, 2100);
 yearSpinBox->setValue(QDate::currentDate().year());
 artistComboBox = new ArtistComboBox(db, this);
 artistButton = new QPushButton(tr("Add &New..."), this);
 ...
 cancelButton = new QPushButton(tr("Cancel"), this);

We create the labels, the line edit, the spin box, the combobox, and the buttons that form the user interface. The combobox is of type ArtistComboBox, which we will cover later on.

 trackCursor = new QSqlCursor("track", true, db);
 trackTable = new QDataTable(trackCursor, false, this);
 trackTable->setFilter("cdid = " + QString::number(cdId));
 trackTable->setSort(trackCursor->index("number"));
 trackTable->addColumn("title", tr("Track"));
 trackTable->addColumn("duration", tr("Duration"));
 trackTable->refresh();

We set up the QDataTable that allows the user to browse and edit the tracks on the current CD. This is very similar to what we did in the previous section with the ArtistForm class.

 cdCursor = new QSqlCursor("cd", true, db);
 cdCursor->select("id = " + QString::number(cdId));
 cdCursor->next();

We set up the QSqlCursor associated with the QSqlForm and make it point to the record with the correct ID.

 QSqlPropertyMap *propertyMap = new QSqlPropertyMap;
 propertyMap->insert("ArtistComboBox", "artistId");
 sqlForm = new QSqlForm(this);
 sqlForm->installPropertyMap(propertyMap);
 sqlForm->setRecord(cdCursor->primeUpdate());
 sqlForm->insert(titleLineEdit, "title");
 sqlForm->insert(artistComboBox, "artistid");
 sqlForm->insert(yearSpinBox, "year");
 sqlForm->readFields();

We create a QSqlPropertyMap. The QSqlPropertyMap class tells QSqlForm which Qt property holds the value of a certain type of editor widget. By default, QSqlForm already knows that a QLineEdit stores its value in the text property and that a QSpinBox stores its value in the value property. But it doesn't know anything about custom widgets such as ArtistComboBox. By inserting the pair ("ArtistComboBox", "artistId") in the property map and by calling installPropertyMap() on the QSqlForm, we tell QSqlForm to use the artistId property for widgets of type ArtistComboBox.

The QSqlForm object also needs a buffer to operate on, which we obtain by calling primeUpdate() on the QSqlCursor, and it needs to know which editor widget corresponds to which database field. At the end, we call readFields() to read the data from the database into the editor widgets.

 connect(artistButton, SIGNAL(clicked()),
 this, SLOT(addNewArtist()));
 connect(moveUpButton, SIGNAL(clicked()),
 this, SLOT(moveTrackUp()));
 connect(moveDownButton, SIGNAL(clicked()),
 this, SLOT(moveTrackDown()));
 connect(updateButton, SIGNAL(clicked()),
 this, SLOT(accept()));
 connect(cancelButton, SIGNAL(clicked()),
 this, SLOT(reject()));
 connect(trackTable, SIGNAL(beforeInsert(QSqlRecord *)),
 this, SLOT(beforeInsertTrack(QSqlRecord *)));
 ...
}

We connect the buttons' clicked() signals and the QDataTable's beforeInsert() signal to the private slots that are described next.

void CdForm::accept()
{
 sqlForm->writeFields();
 cdCursor->update();
 db->commit();
 QDialog::accept();
}

If the user clicks Update, we write the data into the QSqlCursor's edit buffer, we call update() to perform an UPDATE on the database, we call commit() to really write the record into the database, and we call the base class's accept() implementation to close the form.

void CdForm::reject()
{
 db->rollback();
 QDialog::reject();
}

If the user clicks Cancel, we roll back, leaving the database unchanged, and close the form.

void CdForm::addNewArtist()
{
 ArtistForm form(this);
 if (form.exec()) {
 artistComboBox->refresh();
 updateButton->setEnabled(artistComboBox->count() > 0);
 }
}

If the user clicks Add New, we pop up a modal ArtistForm dialog. The dialog allows the user to add new artists, and also to edit and delete existing artists. If the user clicks Update, we call ArtistComboBox::refresh() to ensure that its list of artists is up to date.

We enable or disable the Update button depending on whether there are any artists, since we don't want to allow a new CD to be created without an artist name.

void CdForm::beforeInsertTrack(QSqlRecord *buffer)
{
 buffer->setValue("id", generateId("track", db));
 buffer->setValue("number", trackCursor->size() + 1);
 buffer->setValue("cdid", cdId);
}

The beforeInsertTrack() slot is connected to the QDataTable's beforeInsert() signal. We set the record's id, number, and cdid fields.

void CdForm::beforeDeleteTrack(QSqlRecord *buffer)
{
 QSqlQuery query(db);
 query.exec("UPDATE track SET number = number - 1 "
 "WHERE track.number > "
 + buffer->value("number").toString());
}

The beforeDeleteTrack() slot is connected to the QDataTable's beforeDelete() signal. We renumber all the tracks that have a number higher than the track we delete to ensure that the track numbers remain consecutive. For example, if the CD contains six tracks and the user deletes track 4, then track 5 becomes track 4 and track 6 becomes track 5.

There are four functions that we have not covered: moveTrackUp(), moveTrackDown(), swapTracks(), and createNewRecord(). These are necessary to make the application usable, but their implementations do not show any new techniques, so we will not review them here. Their source code is on the CD.

Now that we have seen all the forms in the CD Collection application, we are ready to review the custom ArtistComboBox. As usual, we start with the class definition:

class ArtistComboBox : public QComboBox
{
 Q_OBJECT
 Q_PROPERTY(int artistId READ artistId WRITE setArtistId)
public:
 ArtistComboBox(QSqlDatabase *database, QWidget *parent = 0,
 const char *name = 0);

 void refresh();
 int artistId() const;
 void setArtistId(int id);

private:
 void populate();

 QSqlDatabase *db;
 QMap idFromIndex;
 QMap indexFromId;
};

The ArtistComboBox class inherits QComboBox and adds an artistId property and a few functions.

In the private section, we declare a QMap that associates artist IDs with combobox indexes and a QMap that associates combobox indexes with artist IDs.

ArtistComboBox::ArtistComboBox(QSqlDatabase *database,
 QWidget *parent, const char *name)
 : QComboBox(parent, name)
{
 db = database;
 populate();
}

In the constructor, we call the private function populate() to fill the combobox with the names and IDs in the artist table.

void ArtistComboBox::refresh()
{
 int oldArtistId = artistId();
 clear();
 idFromIndex.clear();
 indexFromId.clear();
 populate();
 setArtistId(oldArtistId);
}

In the refresh() function, we repopulate the combobox with the latest data from the database. We are also careful to ensure that the artist who was selected before the refresh is still selected afterward, unless that artist has have been deleted from the database.

void ArtistComboBox::populate()
{
 QSqlCursor cursor("artist", true, db);
 cursor.select(cursor.index("name"));

 int index = 0;
 while (cursor.next()) {
 int id = cursor.value("id").toInt();
 insertItem(cursor.value("name").toString(), index);
 idFromIndex[index] = id;
 indexFromId[id] = index;
 ++index;
 }
}

In the private function populate(), we iterate through all the artists and call QComboBox::insertItem() to add them to the combobox. We also update the idFromIndex and the indexFromId maps.

int ArtistComboBox::artistId() const
{
 return idFromIndex[currentItem()];
}

The artistId() function returns the ID for the current artist.

void ArtistComboBox::setArtistId(int id)
{
 if (indexFromId.contains(id))
 setCurrentItem(indexFromId[id]);
}

The setArtistId() function sets the current artist based on an artist ID.

In applications that often need comboboxes that show foreign keys, it would probably be worthwhile creating a generic DatabaseComboBox class whose constructor would allow us to specify the table name, the field to display, and the field to use for IDs.

Let's finish the CD Collection application by implementing its createConnections() and main() functions.

inline bool createOneConnection(const QString &name)
{
 QSqlDatabase *db;
 if (name.isEmpty())
 db = QSqlDatabase::addDatabase("QSQLITEX");
 else
 db = QSqlDatabase::addDatabase("QSQLITEX", name);
 db->setDatabaseName("cdcollection.dat");
 if (!db->open()) {
 db->lastError().showMessage();
 return false;
 }
 return true;
}
inline bool createConnections()
{
 return createOneConnection("")
 && createOneConnection("ARTIST")
 && createOneConnection("CD");
}

In createConnections(), we create three identical connections to the CD database. We don't give any name to the first one; it is used by default when we don't specify a database. The other ones are called "ARTIST" and "CD"; they are used by ArtistForm and CdForm.

int main(int argc, char *argv[])
{
 QApplication app(argc, argv);
 if (!createConnections())
 return 1;

 MainForm mainForm;
 app.setMainWidget(&mainForm);
 mainForm.resize(480, 320);
 mainForm.show();
 return app.exec();
}

The main() function is the same as most other Qt main() functions, except for the addition of a createConnections() call.

As we mentioned at the end of the previous section, one possible improvement would be to display the duration of each track as minutes and seconds rather than just seconds. Besides reimplementing QSqlCursor::calculateField(), this would also involve subclassing QSqlEditorFactory to provide a custom editor (which we could base on QTimeEdit) and using a QSqlPropertyMap to tell QDataTable how to get the value back from the editor. See the documentation for QDataTable's installEditorFactory() and installPropertyMap() functions for more information.

Another improvement would be to store an image of each CD's cover in the database and to show it in the CdForm. To implement this, we could store the image data as a BLOB in the database, retrieve it as a QByteArray, and pass the QByteArray to the QImage constructor.