SQL Tree Model Help

[MTK358]

I am writing a tree model for an app I'm making that takes it's data from an SQLite database. Each item in the db contains the values "id" (unique identifier), "parent" (id of parent, or 0 for toplevel item), and "name" (the user-visible name of the item).

It compiles fine, but the view comes out empty! I have no idea where to begin troubleshooting.

The code to fill in the db, create the model, and attach it to a QTreeView:

db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    db.open();
    QSqlQuery *query = new QSqlQuery(db);
 
    query->exec("CREATE TABLE tags (id INTEGER, parent INTEGER, name TEXT)");
    query->exec("INSERT INTO tags VALUES (1, 0, 'item 1')");
    query->exec("INSERT INTO tags VALUES (2, 1, 'sub 1')");
    query->exec("INSERT INTO tags VALUES (3, 2, 'sub sub 1')");
    query->exec("INSERT INTO tags VALUES (4, 2, 'sub sub 2')");
    query->exec("INSERT INTO tags VALUES (5, 0, 'item 2')");
    query->exec("INSERT INTO tags VALUES (6, 5, 'sub 1')");
 
    tagModel = new TagTreeModel(query);
    ui->tagTree->setModel(tagModel);

db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    db.open();
    QSqlQuery *query = new QSqlQuery(db);

    query->exec("CREATE TABLE tags (id INTEGER, parent INTEGER, name TEXT)");
    query->exec("INSERT INTO tags VALUES (1, 0, 'item 1')");
    query->exec("INSERT INTO tags VALUES (2, 1, 'sub 1')");
    query->exec("INSERT INTO tags VALUES (3, 2, 'sub sub 1')");
    query->exec("INSERT INTO tags VALUES (4, 2, 'sub sub 2')");
    query->exec("INSERT INTO tags VALUES (5, 0, 'item 2')");
    query->exec("INSERT INTO tags VALUES (6, 5, 'sub 1')");

    tagModel = new TagTreeModel(query);
    ui->tagTree->setModel(tagModel);

To copy to clipboard, switch view to plain text mode 

The header for the model class:

class TagTreeModel : public QAbstractItemModel
{
    Q_OBJECT
 
public:
    TagTreeModel(QSqlQuery *tagDbQuery);
 
    QModelIndex index(int row, int column, const QModelIndex &parent) const;
    QModelIndex parent(const QModelIndex &child) const;
    int rowCount(const QModelIndex &parent) const;
    int columnCount(const QModelIndex &parent) const;
    Qt::ItemFlags flags(const QModelIndex &index) const;
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const;
    QVariant headerData(int seaction, Qt::Orientation orientation, int role) const;
 
private:
 
    QSqlQuery *query;
};

class TagTreeModel : public QAbstractItemModel
{
    Q_OBJECT

public:
    TagTreeModel(QSqlQuery *tagDbQuery);

    QModelIndex index(int row, int column, const QModelIndex &parent) const;
    QModelIndex parent(const QModelIndex &child) const;
    int rowCount(const QModelIndex &parent) const;
    int columnCount(const QModelIndex &parent) const;
    Qt::ItemFlags flags(const QModelIndex &index) const;
    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const;
    QVariant headerData(int seaction, Qt::Orientation orientation, int role) const;

private:

    QSqlQuery *query;
};

To copy to clipboard, switch view to plain text mode 

The implementation of the model:

TagTreeModel::TagTreeModel(QSqlQuery *tagDbQuery) :
        QAbstractItemModel()
{
    query = tagDbQuery;
}
 
QModelIndex TagTreeModel::index(int row, int column, const QModelIndex &parent) const
{
    qint32 parentId;
    if (parent.isValid())
        parentId = parent.internalId();
    else
        parentId = 0;
 
    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentId);
    query->exec();
 
    if (query->seek(row))
        return createIndex(row, column, query->value(0).toInt());
    return QModelIndex();
}
 
QModelIndex TagTreeModel::parent(const QModelIndex &child) const
{
    if (!child.isValid())
        return QModelIndex();
 
    query->prepare("SELECT parent FROM tags WHERE id=?");
    query->addBindValue(child.internalId());
    query->exec();
    query->next();
    qint32 parent = query->value(0).toInt();
 
    if (parent == 0)
        return QModelIndex();
 
    query->prepare("SELECT parent FROM tags WHERE id=?");
    query->addBindValue(parent);
    query->exec();
    query->next();
    qint32 parentOfParent = query->value(0).toInt();
 
    int row = 0;
    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentOfParent);
    query->exec();
    for (int i=0; query->next(); i++)
    {
        if (query->value(0).toInt() == parent)
        {
            row = i;
            break;
        }
    }
 
    return createIndex(row, 0, parent);
}
 
int TagTreeModel::rowCount(const QModelIndex &parent) const
{
    qint32 parentId;
    if (parent.isValid())
        parentId = parent.internalId();
    else
        parentId = 0;
 
    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentId);
    query->exec();
    return query->size();
}
 
int TagTreeModel::columnCount(const QModelIndex &parent) const
{
    Q_UNUSED(parent);
 
    return 1;
}
 
QVariant TagTreeModel::data(const QModelIndex &index, int role) const
{
    if (!index.isValid() || role != Qt::DisplayRole)
        return QVariant();
 
    query->prepare("SELECT name FROM tags WHERE id=?");
    query->addBindValue(index.internalId());
    query->exec();
    query->next();
    return query->value(0);
}
 
Qt::ItemFlags TagTreeModel::flags(const QModelIndex &index) const
{
    if (!index.isValid())
        return Qt::ItemIsEnabled;
    return Qt::ItemIsEnabled | Qt::ItemIsSelectable;
}
 
QVariant TagTreeModel::headerData(int section, Qt::Orientation orientation, int role) const
{
    Q_UNUSED(section);
 
    if (orientation != Qt::Horizontal || role != Qt::DisplayRole)
        return QVariant();
    return QVariant(QString("Tag"));
}

TagTreeModel::TagTreeModel(QSqlQuery *tagDbQuery) :
        QAbstractItemModel()
{
    query = tagDbQuery;
}

QModelIndex TagTreeModel::index(int row, int column, const QModelIndex &parent) const
{
    qint32 parentId;
    if (parent.isValid())
        parentId = parent.internalId();
    else
        parentId = 0;

    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentId);
    query->exec();

    if (query->seek(row))
        return createIndex(row, column, query->value(0).toInt());
    return QModelIndex();
}

QModelIndex TagTreeModel::parent(const QModelIndex &child) const
{
    if (!child.isValid())
        return QModelIndex();

    query->prepare("SELECT parent FROM tags WHERE id=?");
    query->addBindValue(child.internalId());
    query->exec();
    query->next();
    qint32 parent = query->value(0).toInt();

    if (parent == 0)
        return QModelIndex();

    query->prepare("SELECT parent FROM tags WHERE id=?");
    query->addBindValue(parent);
    query->exec();
    query->next();
    qint32 parentOfParent = query->value(0).toInt();

    int row = 0;
    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentOfParent);
    query->exec();
    for (int i=0; query->next(); i++)
    {
        if (query->value(0).toInt() == parent)
        {
            row = i;
            break;
        }
    }

    return createIndex(row, 0, parent);
}

int TagTreeModel::rowCount(const QModelIndex &parent) const
{
    qint32 parentId;
    if (parent.isValid())
        parentId = parent.internalId();
    else
        parentId = 0;

    query->prepare("SELECT id FROM tags WHERE parent=?");
    query->addBindValue(parentId);
    query->exec();
    return query->size();
}

int TagTreeModel::columnCount(const QModelIndex &parent) const
{
    Q_UNUSED(parent);

    return 1;
}

QVariant TagTreeModel::data(const QModelIndex &index, int role) const
{
    if (!index.isValid() || role != Qt::DisplayRole)
        return QVariant();

    query->prepare("SELECT name FROM tags WHERE id=?");
    query->addBindValue(index.internalId());
    query->exec();
    query->next();
    return query->value(0);
}

Qt::ItemFlags TagTreeModel::flags(const QModelIndex &index) const
{
    if (!index.isValid())
        return Qt::ItemIsEnabled;
    return Qt::ItemIsEnabled | Qt::ItemIsSelectable;
}

QVariant TagTreeModel::headerData(int section, Qt::Orientation orientation, int role) const
{
    Q_UNUSED(section);

    if (orientation != Qt::Horizontal || role != Qt::DisplayRole)
        return QVariant();
    return QVariant(QString("Tag"));
}

To copy to clipboard, switch view to plain text mode 

[waynew]

First off, I don't think QtSql is going to recognize a Sqlite database named :memory - where is this defined? It needs to be like myDB.sqlite Have you checked to see if the database is actually being created? With another tool maybe. How about a qDebug to see if the database is open? Also, I don't see where you have populated your model with a model->select();

[Lykurg]

The :memory: is fine. It creates a temporal database in the memory with no file representation. But sharing a query with the model is a bad idea it will come to problems. Better just tell the model the database name and let the model do all the rest. Debug if any of your model methods is called.

[MTK358]

Also, I don't see where you have populated your model with a model->select();

What select() method? It doesn't exist in QAbstractItemModel!

But sharing a query with the model is a bad idea it will come to problems. Better just tell the model the database name and let the model do all the rest.

Why?

And I need different parts of the program to access different parts of the database, so isn't it necessary to share it?

[Lykurg]

It definitly work, but it is a better design if your model is independent. It simply can save you from a lot of possible errors. And there is no need. If you give the connection name and database name you can "duplicate" the connection without any loose.

But is any of your model method called?

[MTK358]

But is any of your model method called?

Yes. headerData() seems to be called a huge amount of times, but data() is never called!

[MTK358]

Help? Anyone?

[ChrisW67]

How are you determining that data() is never called? Is it the absence of displayed values in your view, a debug output statement in the method, single-stepping/breakpoints? Are you checking that your queries succeed? Are you checking that internalId() returns what you are expecting?

[MTK358]

I gave up on it for a few days and revisited it recently, and found out that the problem was that SQLite does not support the QSqlQuery::size() method.

[kouhj]

I found subclassing QSqlQuery solves this issue. Thanks to your orginal code!

class TMySqlQuery : public QSqlQuery
{
public:
        TMySqlQuery(QSqlDatabase db);
        int size();
 
};

class TMySqlQuery : public QSqlQuery
{
public:
        TMySqlQuery(QSqlDatabase db);
        int size();

};

To copy to clipboard, switch view to plain text mode 

TMySqlQuery::TMySqlQuery(QSqlDatabase db)
    : QSqlQuery (db)
{
}
 
 
int TMySqlQuery::size()
{
    if (QSqlQuery::size() == -1) {
        int c = at();
        last();
        int s = at();
        seek(c, false);
        return s+1;
    } else {
        return QSqlQuery::size();
    }
}

TMySqlQuery::TMySqlQuery(QSqlDatabase db)
    : QSqlQuery (db)
{
}


int TMySqlQuery::size()
{
    if (QSqlQuery::size() == -1) {
        int c = at();
        last();
        int s = at();
        seek(c, false);
        return s+1;
    } else {
        return QSqlQuery::size();
    }
}

To copy to clipboard, switch view to plain text mode