Presenting Data in Tabular Form

The QDataTable class is a database-aware QTable widget that supports browsing and editing. It interacts with a database through a QSqlCursor. Here, we will review two dialogs that use QDataTable. Together with the QSqlForm-based dialog presented in the next section, these forms constitute the CD Collection application.

The application uses three tables, defined as follows:

CREATE TABLE artist (
 id INTEGER PRIMARY KEY,
 name VARCHAR(40) NOT NULL,
 country VARCHAR(40));

CREATE TABLE cd (
 id INTEGER PRIMARY KEY,
 artistid INTEGER NOT NULL,
 title VARCHAR(40) NOT NULL,
 year INTEGER NOT NULL,
 FOREIGN KEY (artistid) REFERENCES artist);

CREATE TABLE track (
 id INTEGER PRIMARY KEY,
 cdid INTEGER NOT NULL,
 number INTEGER NOT NULL,
 title VARCHAR(40) NOT NULL,
 duration INTEGER NOT NULL,
 FOREIGN KEY (cdid) REFERENCES cd);

Some databases don't support foreign keys. For those, we must remove the FOREIGN KEY clauses. The example will still work, but the database will not enforce referential integrity.

Figure 12.1. The CD Collection application's tables

The first class that we will write is a dialog that allows the user to edit a list of artists. The user can insert, update, or delete artists using the QDataTable's context menu. The changes are applied to the database when the user clicks Update.

Figure 12.2. The ArtistForm dialog

Here's the class definition for the dialog:

class ArtistForm : public QDialog
{
 Q_OBJECT
public:
 ArtistForm(QWidget *parent = 0, const char *name = 0);

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

private slots:
 void primeInsertArtist(QSqlRecord *buffer);
 void beforeInsertArtist(QSqlRecord *buffer);
 void beforeDeleteArtist(QSqlRecord *buffer);

private:
 QSqlDatabase *db;
 QDataTable *artistTable;
 QPushButton *updateButton;
 QPushButton *cancelButton;
};

The accept() and reject() slots are reimplemented from QDialog.

ArtistForm::ArtistForm(QWidget *parent, const char *name)
 : QDialog(parent, name)
{
 setCaption(tr("Update Artists"));

 db = QSqlDatabase::database("ARTIST");
 db->transaction();

 QSqlCursor *artistCursor = new QSqlCursor("artist", true, db);
 artistTable = new QDataTable(artistCursor, false, this);
 artistTable->addColumn("name", tr("Name"));
 artistTable->addColumn("country", tr("Country"));
 artistTable->setAutoDelete(true);
 artistTable->setConfirmDelete(true);
 artistTable->setSorting(true);
 artistTable->refresh();

 updateButton = new QPushButton(tr("Update"), this);
 updateButton->setDefault(true);
 cancelButton = new QPushButton(tr("Cancel"), this);

In the ArtistForm constructor, we start a transaction using the "ARTIST" database connection. Then we create a QSqlCursor on the database's artist table, and a QDataTable to display it.

The second argument to the QSqlCursor constructor is an "auto-populate" flag. By passing true, we tell QSqlCursor to load information about every field in the table and to operate on all the fields.

The QDataTable constructor's second argument is also an auto-populate flag. If true, the QDataTable automatically creates columns for each field in the QSqlCursor's result set. We pass false and call addColumn() to provide two columns corresponding to the result set's name and country fields.

We pass ownership of the QSqlCursor to the QDataTable by calling setAutoDelete(), so we don't need to delete it ourselves. We call setConfirmDelete() to make the QDataTable pop up a message box asking the user to confirm deletions. We call setSorting(true) to allow the user to click on the column headers to sort the table according to a certain column. Finally, we call refresh() to populate the QDataTable with data from the database.

We also create an Update and a Cancel button.

connect(artistTable, SIGNAL(beforeDelete(QSqlRecord *)),
 this, SLOT(beforeDeleteArtist(QSqlRecord *)));
connect(artistTable, SIGNAL(primeInsert(QSqlRecord *)),
 this, SLOT(primeInsertArtist(QSqlRecord *)));
connect(artistTable, SIGNAL(beforeInsert(QSqlRecord *)),
 this, SLOT(beforeInsertArtist(QSqlRecord *)));
connect(updateButton, SIGNAL(clicked()),
 this, SLOT(accept()));
connect(cancelButton, SIGNAL(clicked()),
 this, SLOT(reject()));

We connect three of the QDataTable's signals to three private slots. We connect the Update button to accept() and the Cancel button to reject().

 QHBoxLayout *buttonLayout = new QHBoxLayout;
 buttonLayout->addStretch(1);
 buttonLayout->addWidget(updateButton);
 buttonLayout->addWidget(cancelButton);

 QVBoxLayout *mainLayout = new QVBoxLayout(this);
 mainLayout->setMargin(11);
 mainLayout->setSpacing(6);
 mainLayout->addWidget(artistTable);
 mainLayout->addLayout(buttonLayout);
}

Finally, we put the QPushButtons into a horizontal layout, and we put the QDataTable and the horizontal layout into a vertical layout.

void ArtistForm::accept()
{
 db->commit();
 QDialog::accept();
}

If the user clicks Update, we commit the transaction and call the base class's accept() function.

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

If the user clicks Cancel, we roll back the transaction and call the base class's reject() function.

void ArtistForm::beforeDeleteArtist(QSqlRecord *buffer)
{
 QSqlQuery query(db);
 query.exec("DELETE FROM track WHERE track.id IN "
 "(SELECT track.id FROM track, cd "
 "WHERE track.cdid = cd.id AND cd.artistid = "
 + buffer->value("id").toString() + ")");
 query.exec("DELETE FROM cd WHERE artistid = "
 + buffer->value("id").toString());
}

The beforeDeleteArtist() slot is connected to the QDataTable's beforeDelete() signal, which is emitted just before a record is deleted. Here, we perform a cascading delete by executing two queries: one to delete all the tracks from CDs by the artist and one to delete all the CDs by the artist. Performing these deletions does not risk relational integrity, because they are all done within the context of the transaction that began in the form's constructor.

Another approach would have been to prevent the user from deleting artists that are referred to by the cd table. To achieve this, we would have to reimplement QDataTable::contextMenuEvent() so that we could handle the deletion ourselves. A crude alternative that will work if the database has been set up to enforce relational integrity is to simply attempt the deletion and leave it to the database to prevent it.

void ArtistForm::primeInsertArtist(QSqlRecord *buffer)
{
 buffer->setValue("country", "USA");
}

The primeInsertArtist() slot is connected to the QDataTable's primeInsert() signal, which is emitted just before the user starts editing a new record. We use it to set the default value of the new record's country field to "USA", the ideal default for a U.S.-centric application.

This is one way of setting default values for fields. Another way is to subclass QSqlCursor and reimplement primeInsert(), which makes sense if we will use the same QSqlCursor many times in the same application and want to ensure consistent behavior. A third way is to do it at the database level, using DEFAULT clauses in the CREATE TABLE statements.

void ArtistForm::beforeInsertArtist(QSqlRecord *buffer)
{
 buffer->setValue("id", generateId("artist", db));
}

The beforeInsertArtist() slot is connected to the QDataTable's beforeInsert() signal, which is emitted when the user has finished editing a new record and presses Enter to save it. We set the value of the id field to a generated value. We rely on a function called generateId() to produce a unique primary key.

Since we will need generateId() a few times, we define it inline in a header file and include it each time we need it. Here's a quick (and inefficient) way of implementing it:

inline int generateId(const QString &table, QSqlDatabase *db)
{
 QSqlQuery query(db);
 query.exec("SELECT max(id) FROM " + table);
 query.next();
 return query.value(0).toInt() + 1;
}

The generateId() function can only be guaranteed to work correctly if it is executed within the context of the same transaction as the corresponding INSERT statement.

Some databases support auto-generated fields. For these, we simply need to tell the database to auto-generate the id field and call setGenerated("id", false) on the QSqlCursor to tell it not to generate the value of the id field.

We will now review another dialog that uses QDataTable. For this dialog, we will implement a masterdetail view. The master view is a list of CDs. The detail view is a list of tracks for the current CD. This dialog is the main window of the CD Collection application.

This time, we provide Add, Edit, and Delete buttons to allow the user to modify the CD list, rather than relying on a context menu. When the user clicks Add or Edit, a CdForm dialog pops up. (CdForm is covered in the next section.)

Figure 12.3. The MainForm dialog

Another difference between this example and the previous one is that we must resolve a foreign key, so we can show the artist's name and country rather than the artist's ID. To accomplish this, we must use QSqlSelectCursor, a subclass of QSqlCursor that supports arbitrary SELECT statements, in this case a join.

First, the class definition:

class MainForm : public QDialog
{
 Q_OBJECT
public:
 MainForm(QWidget *parent = 0, const char *name = 0);

private slots:
 void addCd();
 void editCd();
 void deleteCd();
 void currentCdChanged(QSqlRecord *record);

private:
 QSplitter *splitter;
 QDataTable *cdTable;
 QDataTable *trackTable;
 QPushButton *addButton;
 ...
 QPushButton *quitButton;
};

The MainForm class inherits from QDialog.

MainForm::MainForm(QWidget *parent, const char *name)
 : QDialog(parent, name)
{
 setCaption(tr("CD Collection"));

 splitter = new QSplitter(Vertical, this);

 QSqlSelectCursor *cdCursor = new QSqlSelectCursor(
 "SELECT cd.id, title, name, country, year "
 "FROM cd, artist WHERE cd.artistid = artist.id");
 if (!cdCursor->isActive()) {
 QMessageBox::critical(this, tr("CD Collection"),
 tr("The database has not been created.
"
 "Run the cdtables example to create a sample "
 "database, then copy cdcollection.dat into "
 "this directory and restart this application."));
 qApp->quit();
 }

 cdTable = new QDataTable(cdCursor, false, splitter);
 cdTable->addColumn("title", tr("CD"));
 cdTable->addColumn("name", tr("Artist"));
 cdTable->addColumn("country", tr("Country"));
 cdTable->addColumn("year", tr("Year"));
 cdTable->setAutoDelete(true);
 cdTable->refresh();

In the constructor, we create a read-only QDataTable for the cd table and its associated cursor. The cursor is based on a query that joins the cd and the artist tables. The QDataTable is read-only because it operates on a QSqlSelectCursor. Read-only tables don't provide a context menu.

If the cursor query fails, we pop up a message box indicating that something is wrong and terminate the application.

 QSqlCursor *trackCursor = new QSqlCursor("track");
 trackCursor->setMode(QSqlCursor::ReadOnly);
 trackTable = new QDataTable(trackCursor, false, splitter);
 trackTable->setSort(trackCursor->index("number"));
 trackTable->addColumn("title", tr("Track"));
 trackTable->addColumn("duration", tr("Duration"));

We create the second QDataTable and its cursor. We make the table read-only by calling setMode(QSqlCursor::ReadOnly) on the cursor, and call setSort() to sort the tracks by track number.

 addButton = new QPushButton(tr("&Add"), this);
 editButton = new QPushButton(tr("&Edit"), this);
 deleteButton = new QPushButton(tr("&Delete"), this);
 refreshButton = new QPushButton(tr("&Refresh"), this);
 quitButton = new QPushButton(tr("&Quit"), this);
 connect(addButton, SIGNAL(clicked()),
 this, SLOT(addCd()));
 ...
 connect(quitButton, SIGNAL(clicked()),
 this, SLOT(close()));
 connect(cdTable, SIGNAL(currentChanged(QSqlRecord *)),
 this, SLOT(currentCdChanged(QSqlRecord *)));
 connect(cdTable,
 SIGNAL(doubleClicked(int, int, int, const QPoint &)),
 this, SLOT(editCd()));
 ...
}

We set up the rest of the user interface and create the signalslot connections necessary to produce the desired behavior.

void MainForm::addCd()
{
 CdForm form(this);
 if (form.exec()) {
 cdTable->refresh();
 trackTable->refresh();
 }
}

When the user clicks Add, we pop up a modal CdForm dialog, and if the user clicks Update on it, we refresh the QDataTables.

void MainForm::editCd()
{
 QSqlRecord *record = cdTable->currentRecord();
 if (record) {
 CdForm form(record->value("id").toInt(), this);
 if (form.exec()) {
 cdTable->refresh();
 trackTable->refresh();
 }
 }
}

When the user clicks Edit, we pop up a modal CdForm dialog, with the current CD's ID as argument to the CdForm constructor. This will cause the dialog to start up with its fields populated with the current CD's data.

When we parameterize a form with an ID as we have done here, it is possible that the ID will not be valid by the time the form appears. For example, the user could click Edit a fraction of a second before another user deletes the CD. What we could have done in CdForm is to execute a SELECT on the ID that is passed in immediately after the transaction() call and only proceed if the ID still exists. Here, we simply rely on the database to report an error if an attempt to use an invalid ID is made.

void MainForm::deleteCd()
{
 QSqlRecord *record = cdTable->currentRecord();
 if (record) {
 QSqlQuery query;
 query.exec("DELETE FROM track WHERE cdid = "
 + record->value("id").toString());
 query.exec("DELETE FROM cd WHERE id = "
 + record->value("id").toString());
 cdTable->refresh();
 trackTable->refresh();
 }
}

When the user clicks Delete, we remove all the tracks for the current CD from the track table and then the current CD from the cd table. Then we update both tables.

void MainForm::currentCdChanged(QSqlRecord *record)
{
 trackTable->setFilter("cdid = "
 + record->value("id").toString());
 trackTable->refresh();
}

The currentCdChanged() slot is connected to the cdTable's currentChanged() signal, which is emitted when the user modifies the current CD or when the user makes another CD current. Whenever the current CD changes, we call setFilter() on the track table and refresh it to make it display the tracks related to the current CD, and we call refresh() to force the table to repopulate itself with the relevant data.

This is all the code that is needed to implement MainForm. One possible improvement would be to show the duration of each track split into minutes and seconds (for example, "02:35") rather than just as seconds ("155"). We could accomplish this by subclassing QSqlCursor and reimplementing the calculateField() function to transform the duration field into a QString with the desired format:

QVariant TrackSqlCursor::calculateField(const QString &name)
{
 if (name == "duration") {
 int duration = value("duration").toInt();
 return QString("%1:%2").arg(duration / 60, 2)
 .arg(duration % 60, 2);
 }
 return QVariant();
}

We would also need to call setCalculated("duration", true) on the cursor to tell QDataTable to use the value returned by calculateField() for the duration field, instead of simply using value().