Review Questions

1.	What is controller code? Which Qt classes are controller classes?
2.	What pattern(s) is/are used in the design of `InputField` ?
3.	Because there is a 1:1 correspondence between `InputField` and `Question` , we could easily combine the two classes into one. What would you call that class? Explain the advantages or disadvantages of this design.
4.	How do you determine what item(s) is/are selected in a `QListView` ?
5.	If we wanted to iterate through items in an `QAbstractItemModel` , what would be a good class to use?
6.	There are two distinct model-view class pairs for storing and displaying tree-like data. What are they called? Why would you use one versus the other?

Chapter 18. Qt SQL Classes

This chapter gives a general introduction to the capabilities of Qt's SQL classes, using MySQL as an example back end.

18.1	Introduction to MySQL	424
18.2	Queries and Result Sets	427
18.3	Database Models	429

Qt 4 provides a platform-neutral database interface similar to JDBC but without the annoying mandatory exception-handling code. You can use Qt to connect to a variety of different SQL databases, including Oracle, PostgreSQL, and SybaseSQL. In the examples that follow, we use MySQL ^[1] because it

^[1] http://dev.mysql.com/doc/

Is open source
Is available on all platforms
Comes pre-installed on most Linux distributions
Has excellent documentation
Is very widely used

18.1. Introduction to MySQL

After you have installed MySQL on your system, you can create a database from its shell by entering mysql as the "root" or admin user , as shown here. ^[2]

^[2] If you did not install MySQL yourself, then you may need to ask a system admin to create a MySQL account for you.

/home/files>

mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Next, cut and paste the following lines from Example 18.1 into the MySQL shell.

Example 18.1. src/sql/dbcreate.sql

create database mp3db;
use mp3db;
grant all on mp3db.* to 'mp3user'@'localhost' identified by 'mp3dbpw';
grant all on mp3db.* to mp3user identified by 'mp3dbpw';

The last two lines grant user permissions for mp3user , which is the userid we will use for all subsequent operations on the data.

Connecting to MySQL from Qt

Connecting initially to the database requires four pieces of information: user, password, database, and host, as shown in Example 18.2.

Example 18.2. src/sql/mp3db.cpp

[ . . . . ]

bool Mp3db::connect()
{
    QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("mp3user");
    db.setUserName("mp3db");
    db.setPassword("mp3dbpw");
    if (!db.open()) {
        qCritical("Cannot open database: %s (%s)",
                 db.lastError().text().toLatin1().data(),
                 qt_error_string().toLocal8Bit().data());
        return false;
    }
    return true;
}

After the database connection has been opened, we use a very powerful class called QSqlQuery , which has a member function exec () that enables us to submit standard SQL statements to the database.

Defining a Table

Each database has a collection of tables . A table is very much like an array of struct , where each column corresponds to a data member. To define a table, we must describe each of the columns , which can also be thought of as fields, properties, or data members .

Example 18.3. src/sql/ filetagger .sql

CREATE TABLE FileTagger (
  Artist      varchar(100),
  TrackTitle  varchar(100),
  AlbumTitle  varchar(100),
  TrackNumber    varchar(10),
  Genre         varchar(20),
  Comment          varchar(200),
  Preference     varchar(20),
  Filename       varchar(200),
  PRIMARY KEY(Filename),
  INDEX(Preference),
  INDEX(Genre)
);

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

Example 18.3 defines a single table in SQL called FileTagger . This table has a structure that includes columns for each of the properties of the FileTagger class. There are three ways you can create this table.

Cut and paste the contents of Example 18.3 into the mysql shell.

Source the file from the mysql shell.

>

mysql mp3db -u mp3user -p

Enter password:

XXXXX

mysql>

source filetagger.sql

Pass it as a string to QSqlQuery::exec() .

If you don't want to keep reentering the db/user/pw each time you run mysql's command line shell, you can set default values in ~/.my.cnf (*nix) or c:\mysql\my.cnf (Win32).

[mysql]
user=mp3user
password=mp3dbpw
database=mp3db

Inserting Rows

We wish to extract data from the ID3 tags of MP3 files and import them into the FileTagger table. The first step is to prepare an SQL statement for inserting the data, as shown in Example 18.4. Prepared statements are useful when we must execute the same SQL statement repeatedlythe server only needs to parse the string once.

Example 18.4. src/sql/mp3db.cpp

[ . . . . ]

Mp3db::Mp3db() {
    connect();
    m_insertQuery.prepare("INSERT INTO FileTagger ("
                        "Artist, TrackTitle, AlbumTitle,
                        TrackNumber, Genre, "
                        "Comment, Preference, Filename) VALUES
                        (?,?,?,?,?,?,?,?)");
}

We left the ? character in the parts of the SQL prepared statement where we later wish to bind values. When we have a FileTagger object with the desired data to import, we call addFile() , shown in Example 18.5. This method binds the values into the prepared statement.

Example 18.5. src/sql/mp3db.cpp

[ . . . . ]

void Mp3db::addFile(FileTagger* song) {
    m_insertQuery.addBindValue(song->getArtist());
    m_insertQuery.addBindValue(song->getTrackTitle());
    m_insertQuery.addBindValue(song->getAlbumTitle());
    m_insertQuery.addBindValue(song->getTrackNumber());
    m_insertQuery.addBindValue(song->getGenre());
    m_insertQuery.addBindValue(song->getComment());
    m_insertQuery.addBindValue(song->getPreference());
    m_insertQuery.addBindValue(song->getFilename());
    m_insertQuery.exec();
}