18.3. Queries and Result Sets

18.3. Queries and Result Sets
	Chapter 18. Database Programming

[ fromfile: database.xml id: queryresultsets ]

In Example 18.7, we define a function that queries an existing Amarok MySQL database. Amarok is a jukebox program for KDE which can use a variety of different back-ends for storing its Mp3 metadata. Instead of storing all tag data for a song in a single row, like we did in Example 18.6, Amarok spreads the tag data across many tables. import() will query the “tags“ table and, for each URL, insert a row of preference data into the “statistics“ table.

Example 18.7. src/mmjbamarok/src/importcommand.cpp

#include <QDebug>
#include <filetagger.h>
#include "commands.h"
#include "database.h"
#include "ratingmapper.h"
#include "mmjbamarok.h"
#include <QDateTime>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QFileInfo>
#include <QSqlError>
void ImportCommand::run() {
    RatingMapper mapper;
    FileTagger ft;
    setAborted(false);
    QSqlDatabase db = Database::connect();
    
    uint timestamp = QDateTime::currentDateTime().toTime_t();
    uint lidate = 0;
    QSqlQuery insert;
    int entries=0;
    int files=0;
    if (remember) {
        lidate = lastImportDate();
    }
    insert.prepare("INSERT INTO statistics (url, deviceid, rating,"
                   " uniqueid, createdate, accessdate)"
                   " VALUES (?, ?, ?, ?, ?, ?) "
                   " ON DUPLICATE KEY UPDATE rating=? ");  
    QSqlQuery query;
    query.exec("select url, deviceid, uniqueid from uniqueid");
    QFileInfo finfo;
    int size = query.size();
    emit newProgressRange(0, size);
    while (query.next() && !isAborted()) { 
        QString filename = query.value(0).toString();
        emit newProgressValue(++files);
        if (files < initialSkip) continue;
        if (!filename.endsWith(".mp3", Qt::CaseInsensitive)) continue;
        QVariant deviceid = query.value(1).toInt();
        QString uniqueid = query.value(2).toString();
        if (remember || verbose || dryRun) {
            finfo.setFile(filename);
            if (!finfo.exists()) {
                qDebug() << "Can't find file: " << filename << endl;
                continue;
            }
        }
        if (remember) {
            uint tt = finfo.lastModified().toTime_t();;
            if (tt < lidate) {
                if (verbose) {
                    qDebug() << "Skipping " << filename 
                           << " because of earlier modification date. \n";
                }
                // emit newRow(filename, "skipped");
                continue;
            }
        }
        ft.setFileName(filename);
        Preference preference = ft.preference();
        int rating = preference.intValue(); 
        if (rating == 0) continue;
        if (verbose || dryRun) {
            qDebug() << (dryRun? "[DRYRUN: " : "[" ) 
                   << files << "] " << rating << " : " << filename << endl;
        }
        if (dryRun) {
            emit newRow(filename, QString("<dryrun: %1>").arg(rating));
            continue;
        }
        insert.addBindValue(filename);
        insert.addBindValue(deviceid);
        insert.addBindValue(rating); 
        insert.addBindValue(uniqueid);
        insert.addBindValue(timestamp);
        insert.addBindValue(timestamp);
        insert.addBindValue(rating);
        if (insert.exec()) {
            entries++;
            emit newRow(filename, preference.toString());
        }
        else {
            QString lastError = insert.lastError().text();
            emit newRow(filename,  lastError);
            qDebug() << "    Err: " << insert.executedQuery() ;
            qDebug() << "   " <<  lastError; 
        }
    }
    if (verbose) qDebug() << "Files processed: " << files << endl;
    qDebug() << "Entries imported:" << entries << endl;
    if (remember && !isAborted() ) {
        if (dryRun) qDebug() << " saveLastImportDate() " << 
                        endl;
        else saveLastImportDate();
    }
    //    QSqlDatabase::removeDatabase(db.connectionName());
    QSqlDatabase::removeDatabase(Database::connectionName());
    //    db.close();        
}

	Prepare an SQL statement that inserts a new statistics record, or updates the current record (new rating), if the record is already there. Each ? is a positional parameter to which we later addBindValue().
	Iterate through result set.

In this example, a JDBC programmer might observe that QSqlQuery serves the purpose of at least two JDBC classes. It is being used in two ways.

As a PreparedStatement - something to store the query, add bind values to, etc.
As a cursor into the ResultSet - something to iterate through the query results.

The function iterates through all songs that are in Amarok's library. For each song, it extracts the preference string from a MusicMatch id3v2 tag and, with the help of RatingMapper, translates the preference string into an integer number of “stars”. Finally, the number of stars is inserted into Amarok's statistics table, under the “rating” column.


18.2. Connecting to MySQL from Qt		18.4. Long Running Threads and the Command Pattern