QSqlRelationalTableModel expose to QML

lsqlrelationaltablemodel.cpp

#include <QSqlRecord>
#include "lqsqlrelationaltablemodel.h"

LQSqlRelationalTableModel::LQSqlRelationalTableModel(QObject *parent, QSqlDatabase db)
    : QSqlRelationalTableModel(parent, db)
{
}

LQSqlRelationalTableModel::~LQSqlRelationalTableModel()
{
}

bool LQSqlRelationalTableModel::select()
{
    bool ret = QSqlRelationalTableModel::select();

    if (ret)
        generateRoleNames();

    return ret;
}

void LQSqlRelationalTableModel::generateRoleNames()
{
    role_names.clear();

    for (int i = 0; i < record().count(); ++i)
    {
        role_names[Qt::UserRole + i + 1] = record().fieldName(i).toUtf8();
    }
}

QVariant LQSqlRelationalTableModel::data(const QModelIndex &item, int role) const
{
    if (item.row() >= rowCount())
        return QString();

    if (role < Qt::UserRole)
        return QSqlQueryModel::data(item, role);
    else
    {
        int columnIdx = role - Qt::UserRole - 1;
        QModelIndex modelIndex = this->index(item.row(), columnIdx);
        return QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
    }
}

lsqlrelationaltablemodel.h

#ifndef LQSQLRELATIONALTABLEMODEL_H
#define LQSQLRELATIONALTABLEMODEL_H

#include <QSqlDatabase>
#include <QSqlRelationalTableModel>

class LQSqlRelationalTableModel : public QSqlRelationalTableModel
{
    Q_OBJECT

public:
    LQSqlRelationalTableModel(QObject *parent = 0, QSqlDatabase db = QSqlDatabase());
    ~LQSqlRelationalTableModel();

    QHash<int, QByteArray> roleNames() const {return role_names; }
    bool select();
    QVariant data(const QModelIndex &item, int role) const;

private:
    void generateRoleNames();
    QHash<int, QByteArray> role_names;
};

#endif // LQSQLRELATIONALTABLEMODEL_H

main.cpp

#include <QApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>
#include <QtQuick/QQuickWindow>

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>

#include <QDebug>

#include "lqsqlrelationaltablemodel.h"

int main(int argc, char* argv[])
{
    QApplication app(argc, argv);

    //open the database
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("songs.db");

    if (!db.open())
    {
        qDebug() << db.lastError();
        exit(0);
    }

    QSqlQuery query;

    QStringList table_list = db.tables(QSql::Tables);

    if (!table_list.contains("music"))
    {
        qDebug() << "Creating table music";

        //create table if not exists
        bool ret = query.exec("create table if not exists music (id integer primary key, title varchar(100), artist varchar(100), year integer)");

        if (!ret)
        {
            qDebug() << query.lastError().text();
            exit(500);
        }

        //insert some data into the database
        if (!query.exec(QString("insert into `music` values(null, '%1', '%2', %3)").arg("Singing Out Low", "Eddie Shafire", "2013")))
        {
            qDebug() << query.lastError().text();
            exit(500);
        }

        if (!query.exec(QString("insert into `music` values(null, '%1', '%2', %3)").arg("Purple Rain", "Harry Blue", "2011")))
        {
            qDebug() << query.lastError().text();
            exit(500);
        }
    }

    LQSqlRelationalTableModel *model = new LQSqlRelationalTableModel(qApp, db);
    model->setTable("music");
    model->select();

    QScopedPointer<QQmlApplicationEngine> engine(new QQmlApplicationEngine());
    engine->rootContext()->setContextProperty(QString("tablemodel"), model);
    engine->load("main.qml");

    QQuickWindow *window = qobject_cast<QQuickWindow*>(engine->rootObjects().at(0));
    window->show();

    return app.exec();
}

main.qml

import QtQuick 2.3
import QtQuick.Controls 1.2
import QtQuick.Window 2.0

Window {
    id: root
    width: 500
    height: 500

    TableView {
        anchors.fill: parent
        TableViewColumn {
            role: "title"
            title: "Title"
            width: 200
        }
        TableViewColumn {
            role: "artist"
            title: "Artist"
            width: 200
        }
        TableViewColumn {
            role: "year"
            title: "Year"
            width: 50
        }

        model: tablemodel
    }
}