[ fromfile: database.xml id: qsqldatabase ]

QSqlDatabase is a somewhat misleading name for this class. It does not represent a database on disk, but rather a connection to a database. A better name for this class would be QSqlConnection.

Connecting initially to a database server requires these pieces of information: driver type, host name, user name, password, and database name, as shown in Example 18.1. In the case of SQLite, you need only a filename, which is passed to QSqlDatabase::setDatabaseName().

A connection (i.e., an instance of QSqlDatabase) is created initially with the static QSqlDatabase::addDatabase(). The instance can be given an optional connection name and can later be retrieved again using that name. The default connection can be reused with the QSqlDatabase::database() method.

[ . . . . ]

void testprepare::testPrepare() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setUserName("amarok");
    db.setPassword("amarok");
    db.setDatabaseName("amarok");
    QVERIFY(db.open());

Each database has a collection of tables. A query statement that changes the definition of a table is called a DDL statement. A table is very much like an array of struct, where each data member corresponds to a column, and each object roughly corresponds to a record, or a row in the table. To define a table, we must describe what a record looks like. That means describing each of the columns, which can also be thought of as fields, properties, or data members. Example 18.2 defines a table in SQL called MetaData.

[ . . . . ]

bool MetaDataTable::createMetadataTable() {
    QSqlDatabase db = DbConnectionSettings::lastSaved();
    if (m_driver == "QMYSQL")
       m_createTableQStr = QString("CREATE TABLE if not exists %1 ("
         "TrackTitle  text, Artist text, "
         "AlbumTitle  text, TrackTime integer, TrackNumber integer, "
         "Genre varchar(30),  Preference integer, Comment  text, "
         "FileName  varchar(255) PRIMARY KEY, INDEX(Genre) ) "
         "DEFAULT CHARSET utf8").arg(m_tableName);
    else m_createTableQStr = QString("CREATE TABLE IF NOT EXISTS %1 ("    
         "TrackTitle  text, Artist text, AlbumTitle  text, "
         "TrackTime integer, TrackNumber integer, Genre varchar(30), "
         "Preference integer, Comment  text, FileName  varchar(255) "
         "PRIMARY KEY)").arg(m_tableName);
    QSqlQuery q(m_createTableQStr);
    if (!q.isActive()) {
       qDebug() << "Create Table Fail: " << q.lastError().text() 
                << q.lastQuery();
       return false;
    }
    db.commit();
    return true;
}

We have different SQL create strings depending on which QSqlDriver is in use. Each additional database we want to support must be tested separately, because SQL syntax can vary from one server to another. For example, we started out using time as the type for the TrackTime column in MySQL, but we changed it to integer^[105] so that we can use the same schema with both databases.

After the database connection is opened, we use a powerful class called QSqlQuery, which has a member function exec().

Using the QSqlQuery, there are two ways to execute SQL statements:

exec(QString) is slower because it requires the server to parse each SQL statement. Prepared statements are safer because you do not need to escape strings. They are also faster, especially when the same SQL statement is executed repeatedly with different parameter values. The SQL driver needs to parse the query string only once.

Example 18.3 shows an example using prepared statements to insert or update rows. We use named parameters here, but positional parameters are also possible with the use of addBindValue and parameters of the form of :1, :2, etc. The SQL syntax for a single operation that can insert or update a row in MySQL is slightly different from that of SQLite, so again, we have two different insert strings.

[ . . . . ]

MetaDataTable::MetaDataTable(QObject* parent)
    : QObject(parent), m_tableName("MetaData") {
    setObjectName(m_tableName);
    m_mdl = Abstract::MetaDataLoader::instance();
    m_driver = DbConnectionSettings::lastSaved().driverName();
    Q_ASSERT(createMetadataTable());
    QString preparedQuery = "INSERT into MetaData" 
         "(Artist, TrackTitle, AlbumTitle, TrackNumber, TrackTime, Genre,"
         "Preference, FileName, Comment) VALUES (:artist, :title, :album,"
         ":track, :time, :genre, :preference, :filename, :comment) "
         "ON DUPLICATE KEY UPDATE Preference=VALUES(Preference),"
         "Genre=VALUES(Genre), AlbumTitle=VALUES(AlbumTitle),"
         "TrackTitle=VALUES(TrackTitle), TrackNumber=VALUES(TrackNumber),"
         "Artist=VALUES(Artist), COMMENT=VALUES(Comment)"; 
    if (m_driver == "QSQLITE") {
        preparedQuery = "INSERT or REPLACE into MetaData"
            "(Artist, TrackTitle, AlbumTitle, TrackNumber, TrackTime, "
            "Genre, Preference, FileName, Comment)"
            "VALUES (:artist, :title, :album, :track, :time, :genre, "
            ":preference, :filename, :comment)";
    }
    bool prepSuccess = m_insertQuery.prepare(preparedQuery);
    if (!prepSuccess) {
        qDebug() << "Prepare fail: " << m_insertQuery.lastError().text() 
                 << m_insertQuery.lastQuery();
        abort();
    }

In some cases, the Qt SQL driver may not support server-side prepared queries, but with Qt SQL you can still use prepared queries for client-side character escaping, which is the safest way to insert data, or process user-supplied data. Prepared statements protect you from SQL injection attacks and other possible parsing errors, and should be faster than regular queries that need to be parsed every time they are executed.

Example 18.4 shows the prepared statement in use. First, we call bindValue() on the query for each column, and then call exec().

[ . . . . ]

bool MetaDataTable::insert(const MetaDataValue &ft) {
    using namespace DbUtils;

    QSqlDatabase db = DbConnectionSettings::lastSaved();
    QSqlRecord record = db.record(m_tableName);
    if (record.isEmpty() && !createMetadataTable()) {
        qDebug() << "unable to create metadata: " 
                 << db.lastError().text();
        return false;
    }

    m_insertQuery.bindValue(":artist", ft.artist());
    m_insertQuery.bindValue(":title", ft.trackTitle());
    m_insertQuery.bindValue(":album", ft.albumTitle());
    m_insertQuery.bindValue(":track", ft.trackNumber());
    QTime t = ft.trackTime();
    int secs = QTime().secsTo(t);
    m_insertQuery.bindValue(":time", secs);
    m_insertQuery.bindValue(":genre", ft.genre());
    m_insertQuery.bindValue(":filename", ft.fileName());
    int pref = ft.preference().intValue();
    m_insertQuery.bindValue(":preference", pref);
    m_insertQuery.bindValue(":comment", ft.comment());

    bool retval = m_insertQuery.exec();

    if (!retval) {
        qDebug() << m_insertQuery.lastError().text() 
                 << m_insertQuery.lastQuery();
        abort();
    }
    emit inserted(ft);
    return retval;
}

As you can see, the Qt SQL does not provide a way for you to "write once, run anywhere" against different database engines. And although it is possible to map columns to properties, you still write the object-relational mapping code and must test on different servers.