trasaction related functionality for QtSql

Hello trolls,

i come to you for some suggestions about the QSqlQuery and the QSqlDriver more precisely into the sql transactions.

I think there is a lack of some functionality and in some case we can have some performance problem.

here are my proposals :

first if i have a good understand about the transaction management in qt, there are two ways to use it :

1) global (manage by the driver with begintransaction, committransaction and rollbacktransaction)
2) local (manage by the qsqlresult into the qsqlquery) it's an autocommit transaction except for select statement.

the main problem is in the second method. (i have done all of my test with ibase driver)

For example when you create a QSqlQuerymodel like that :
(1)

QSqlQueryModel *_model = new QSqlQueryModel();
QSqlQuery _request;
_request.prepare("SELECT * FROM PRODUCTS");
_request.exec();
_model->setQuery(_que);
while (_model->canFetchMore()) {
 _model->fetchMore();
}

In this case, a sql transaction stay opened until the "_model" is destroy.
it is not very good because for some reason during the life of the application, we don't want to destroy
the model and so a sql transaction will stay opened.

the problem is that Qt (for ibase, i don't know for the others drivers) opens a transaction in concurency isolation level,
and don't commit it if the statement is a "select query". so all others requests executed during the app's life will be more and more slow (cause to the concurency).

actually i could add at the beginning of the example a "_request.driver()->beginTransaction()" and at the end a "_request.driver()->commitTransaction()",
but i think is not very elegant and may not be appropriate in all cases.
for example if the model is set to a tableview, the view can fetch herself the model and so we don't know when we can do the "_request.driver()->commitTransaction()"

An other exemple for do a insert with a stored procedure :
(2)

QSqlQuery _addProduct;
_addProduct.prepare("EXECUTE PROCEDURE ADD_PRODUCT(?)");
_addProduct.bindValue(0, "Produit de test");
_addProduct.exec();
if (_addProduct.next()) {
	// here i get the returned id 
	int _idproduct = _addProduct.value(0).toInt();
}
// i must do a clear for commit :(
_addProduct.clear();

in this example, we can see i must do a clear for commit the transaction, because the stored proc return one parameter and so this query
has been seen as a select statement by the ibase driver and is not commit at the end like a classic insert.
moreover if i want rollback i can't
for rollback, the only solution that i found is to create
a global transaction before the prepare but i think it is not a solution.
i think that a "_addProduct.rollback()" or "_addProduct.commit()" is much better and offer a better way to manage transacions.

here are my suggestions :

1) add commit and rollback functions to the QsqlQuery object (that work only if there is no global transaction).

2) add a property autocommit boolean in the QsqlQuery class and add the possibility to set it (default value) via a connect option in the connection string.
i think it's necessary because today all insert or update request done via QSqlQuery are automatiquely commit at the end of the exec() method, so we can't rollback these operations (Except if a global transaction is started).
ex :

	QSqlDatabase _db;
	_db.setConnectOptions("character set=Latin1;autocommit=true");
	QSqlQuery *_query = new QSqlQuery(_db);
	qWarning() << _query->autocommit();
	// return true;
	// but you could set it manually 
	_query->setautocommit(false);
	

3) i think a commit should be done when the qsqlquery's statement is a select and there are no more record to fetch (only if autocommit is set).
in our example (1):
a commit should be done by the QsqlQuery object after that "_model->canFetchMore" had returned false.

4) It could be fine to define the isolation level of a transaction maybe a property in the QsqlQuery.

5) Maybe a QSqlTransaction object could be implemented for the management of transactions.

6) Multiple global transaction could be a nice idea for the management of tempory table (exist in firebird).

7) In the QsqlDatabse object add a list of actives transactions, could be fine.

what do you think about that ?

thanks