18.2. Queries and Result Sets

18.2. Queries and Result Sets
	Chapter 18. Database Programming

[ fromfile: database.xml id: queryresultsets ]

In Example 18.4, we inserted rows into a MetaData table. In this application, we try to encapsulate all of the SQL operations we may need on MetaData SQL table in a class called MetaDataTable. Example 18.5 shows a simple query that is returned as a QStringList:

Example 18.5. src/libs/sqlmetadata/metadatatable.cpp

[ . . . . ]

QStringList MetaDataTable::genres() const {
    QStringList sl;
    QSqlDatabase db = DbConnectionSettings::lastSaved();
    QSqlQuery q("SELECT DISTINCT Genre from MetaData");
    if (!q.isActive()) {
        qDebug() << "Query Failed: " << q.lastQuery() 
                 << q.lastError().text();
    } else while (q.next()) {
        sl << q.value(0).toString();
    }
    return sl;
}

When we want to return a row of data, what is the best way to present it in our API? I prefer returning an object with getters and setters, but whether it should be a heap MetaDataObject or a stack MetaDataValue is open to debate. If you return pointers to heap objects from here, you have to worry about who owns and is responsible for deleting them afterward. Example 18.6 shows another approach, where we convert the QObject to its base class value type and return that.

Example 18.6. src/libs/sqlmetadata/metadatatable.cpp

[ . . . . ]

MetaDataValue MetaDataTable::findRecord(QString fileName) {
    using namespace DbUtils;
    QFileInfo fi(fileName);
    MetaDataObject f;
    if (!fi.exists()) return f;                  
    QString abs = fi.absoluteFilePath();

    QSqlDatabase db = DbConnectionSettings::lastSaved();
    QString qs = QString("select * from %1 where FileName = \"%2\"")
                  .arg(m_tableName).arg(escape(abs));
    QSqlQuery findQuery(qs);
    if (!findQuery.isActive()) {
        qDebug() << "Query Failed: " << findQuery.lastQuery() 
                 << findQuery.lastError().text();
        return f;
    }
    if (!findQuery.first()) return f;
    QSqlRecord rec = findQuery.record();
    for (int i=rec.count() -1; i >= 0; --i) {    
        QSqlField field = rec.field(i);
        QString key = field.name();
        QVariant value = field.value();
        if (key == "Preference") {
            int v = value.toInt();
            Preference p(v);
            f.setPreference(p);
        }
        else if (key == "TrackTime") {           
            QTime trackTime;
            trackTime = trackTime.addSecs(value.toInt());
            f.setTrackTime(trackTime);
        }
        else {
            f.setProperty(key, value);           
        }

    }
    return f;                                    
}

	Return a QObject by value? Don't forget, MetaDataValue is the base class of this particular QObject.
	Properties in QObject map to column names / field values in the table!
	SQLite has no time type, so we must store as int.
	Using QObject setProperty for other columns.
	Create a value type from this local stack QObject about to be destroyed.

In this example, we create a stack MetaDataObject, for the purposes of setting properties. Then we return it by value, so a temporary MetaDataValue is returned. This illustrates how derived objects can be implicitly converted to base class types.^[108]

^[108]Provided the copy constructor is not private.


18.1. QSqlDatabase: Connecting to SQL from Qt		18.3. Database Models