QTableView/QSqlQueryModel

[jpn]

So how do you update the data at the moment? Do you reset the query or..? It would work out of the box if you were able to handle it by emitting dataChanged().

[norobro]

Here is some of my code. As I said in my first post, I have just modified (slightly) one of the examples in the QT docs.

Qt::ItemFlags EditableSqlModel::flags(const QModelIndex &index) const
	{
	Qt::ItemFlags flags = QSqlQueryModel::flags(index);
	if (index.column() == 1 || index.column() == 2 || index.column() == 3 || index.column() == 4 || index.column() == 5)
		flags |= Qt::ItemIsEditable;
	return flags;
	}
 
bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
	{
	if (index.column() < 1 || index.column() > 5)
		return false;
 
	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
	int id = data(primaryKeyIndex).toInt();
	clear();
	bool ok=FALSE;
	if (index.column() == 1) 	ok = setName(id, value.toString());
	if (index.column() == 2) 	ok = setCountry(id, value.toString());
	if (index.column() == 3) 	ok = setTotal(id, value.toDouble());
	if (index.column() == 4) 	ok = setMachine(id, value.toString());
	if (index.column() == 5) 	ok = setOs_vendor(id, value.toString());
	refresh();
	return ok;
	}
 
void EditableSqlModel::refresh()
	{
	setQuery("SELECT host.id, user.name, user.country, host.total, host.machine, host.os_vendor FROM host left join user on host.id = user.id where user.name IS NOT NULL order by host.total");
	}
 
bool EditableSqlModel::setName(int id, const QString &name)
	{
	QSqlQuery query;
	query.prepare("update user set name = ? where id = ?");
	query.addBindValue(name);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setCountry(int id, const QString &Country)
	{
	QSqlQuery query;
	query.prepare("update user set country = ? where id = ?");
	query.addBindValue(Country);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setTotal(int id, const double &Total)
	{
	QSqlQuery query;
	query.prepare("update host set total = ? where id = ?");
	query.addBindValue(Total);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setMachine(int id, const QString &Machine)
	{
	QSqlQuery query;
	query.prepare("update host set machine = ? where id = ?");
	query.addBindValue(Machine);
	query.addBindValue(id);
	return query.exec();
	}
 
bool EditableSqlModel::setOs_vendor(int id, const QString &Os_vendor)
	{
	QSqlQuery query;
	query.prepare("update host set os_vedor = ? where id = ?");
	query.addBindValue(Os_vendor);
	query.addBindValue(id);
	return query.exec();
	}

Qt::ItemFlags EditableSqlModel::flags(const QModelIndex &index) const
	{
	Qt::ItemFlags flags = QSqlQueryModel::flags(index);
	if (index.column() == 1 || index.column() == 2 || index.column() == 3 || index.column() == 4 || index.column() == 5)
		flags |= Qt::ItemIsEditable;
	return flags;
	}

bool EditableSqlModel::setData(const QModelIndex &index, const QVariant &value, int /* role */)
	{
	if (index.column() < 1 || index.column() > 5)
		return false;
	
	QModelIndex primaryKeyIndex = QSqlQueryModel::index(index.row(), 0);
	int id = data(primaryKeyIndex).toInt();
	clear();
	bool ok=FALSE;
	if (index.column() == 1) 	ok = setName(id, value.toString());
	if (index.column() == 2) 	ok = setCountry(id, value.toString());
	if (index.column() == 3) 	ok = setTotal(id, value.toDouble());
	if (index.column() == 4) 	ok = setMachine(id, value.toString());
	if (index.column() == 5) 	ok = setOs_vendor(id, value.toString());
	refresh();
	return ok;
	}

void EditableSqlModel::refresh()
	{
	setQuery("SELECT host.id, user.name, user.country, host.total, host.machine, host.os_vendor FROM host left join user on host.id = user.id where user.name IS NOT NULL order by host.total");
	}

bool EditableSqlModel::setName(int id, const QString &name)
	{
	QSqlQuery query;
	query.prepare("update user set name = ? where id = ?");
	query.addBindValue(name);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setCountry(int id, const QString &Country)
	{
	QSqlQuery query;
	query.prepare("update user set country = ? where id = ?");
	query.addBindValue(Country);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setTotal(int id, const double &Total)
	{
	QSqlQuery query;
	query.prepare("update host set total = ? where id = ?");
	query.addBindValue(Total);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setMachine(int id, const QString &Machine)
	{
	QSqlQuery query;
	query.prepare("update host set machine = ? where id = ?");
	query.addBindValue(Machine);
	query.addBindValue(id);
	return query.exec();
	}

bool EditableSqlModel::setOs_vendor(int id, const QString &Os_vendor)
	{
	QSqlQuery query;
	query.prepare("update host set os_vedor = ? where id = ?");
	query.addBindValue(Os_vendor);
	query.addBindValue(id);
	return query.exec();
	}

To copy to clipboard, switch view to plain text mode 

[jpn]

The problem is that QSqlQueryModel represents content of a QSqlQuery. It is insufficient to emit dataChanged() because the content of the original query hasn't changed so nothing would change in the view anyway. So, one is forced to set a new query to get anything updated. On the other hand, setting a new query means rebuilding the model from scratch and losing current state of the view. I'm afraid I can't come up with any better solution than storing and restoring current index, possibly together with scroll bar values to retain exactly the same viewport position.

[norobro]

I'm going to try experimenting with the viewport position.

Thank you very much for taking time to think about my problem.

Norm