[Interest] Close DB connection when not actively using?

Wed Aug 22 00:42:31 CEST 2018

Ahh, thank you, that was most informative. So, basically, don't use QSqlTableModel/QSqlRelationalTableModel etc, and I should be fine. Would you say a good approach would be "writing my own" based on the lower-level QAbstractTableModel, and populating it with QSqlQueries? It seems that could address all the issues, as then I could open/close connections as needed, put in paging code (automatic or not), spin the queries off into separate threads, etc. It would also enable me to write SQL queries however worked best for the specific use, which I like. Hmmm....

As for "why wouldn't your database be open?", there could be any number of reasons. This is a SQLITE database we're talking about, so perhaps the user doesn't have read/write permission to the proper directory. Maybe the location is on some removable media that isn't mounted. Etc. If we were talking about a "real" database, the server could be down, or not accepting connections. Lots of possibilities, so it behoves me to check. This is a public app, so I can't assume *anything* about the environment.

For what it's worth, I fully agree with your statement about each and every I/O going through the open/start/perform/commit/close cycle. That's essentially what I was envisioning my subclasses as doing - wrapping the *actual* db calls in that cycle. Also, yes, I/O should ideally be threaded off as you say. In my particular app, I highly doubt that the usage patterns would lend themselves to being able to notice these delays, even on a slower system with a lot of records, but it's certainly something to look at for future improvement. It's also an argument in favor of writing a custom "Table Model" class - it would be relatively easy at that point to spin the actual data I/O off to other threads.

Thanks again for the information and pointers! Lots to think about :-)!
-----------------------------------------------
Israel Brewster
Systems Analyst II
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

[cid:31a341a7-a50f-46bf-bdc7-58d8fdcf82e4 at flyravn.com]

[cid:5a6dfe8f-83f6-4356-9be9-5e594ccde583 at flyravn.com]

On Aug 21, 2018, at 2:09 PM, Roland Hughes <roland at logikalsolutions.com<mailto:roland at logikalsolutions.com>> wrote:

You need an architect level person to design your application. This is one of the far too numerous reasons AGILE is a false methodology. Developing even a simple application without a SAG (Systems Architecture Guide) opens an 8-lane highway for problems like these.

Why wouldn't your database be open?

You didn't write a custom table or replace any of the default I/O. As long as a QSqlTableModel is in existence it will keep the database open. You put it on a main window which won't go out of scope until the application is exited.

The "default" ease of use classes aren't for production systems connecting with a database. They all (as far as I can tell) take the lazy approach of opening the database once and keeping it open until the destructor is called. If you want to have an application which stays open, you have to write your own table class and provide all of your own I/O so it only connects to the database when data is needed. If you want a properly sized scroll bar, every I/O operation will need to perform a count(*) to get the maximum record count.

In a production quality system with a SAG, the SAG will state no database connection exists longer than a single transaction. Each I/O operation must

open

start transaction

perform I/O

commit transaction

close

Everyone of them. It's been a very long time since I looked at the code behind QSqlTableModel, but, it never used to do that. From your test it still doesn't. Part of this has to do with scrolling. They never wanted to figure out where they were in the database. One big select() creates a cursor in the database engine (or database itself) which lives as long as the transaction/connection. In order to dance around in that cursor, the connection must stay alive.

Putting scroll bars on a table necessitates either writing a lot of code yourself, or bad design. Most touch screen applications seem to rely on them rather than putting <Prev> <Next> buttons on the screen or enabling PageUp/PageDown. When your design uses this interface you still have to write a bit of code, but you can have a limited life connection. You only retrieve what fits on the screen bounded by the contents of either the first or last record.

Here's the real killer for you.

Almost every example you find on-line, including your test, is wrong. All I/O is performed in the GUI event loop instead of being threaded off. Oh sure, on a fast system with only a few thousand records in your database, like most examples, you don't notice the screen hang/stutter. If you are logging data points from sensors every few seconds, your database will have many hundreds of thousands of records after a few days. Something simple like changing the sort order of the display will cause the GUI to just hang until the I/O completes. If your database resides on a first generation (or something well below Class 10) MicroSD card, you won't even need a 100,000 records to see your GUI starting to hang.

On 08/21/2018 04:35 PM, Israel Brewster wrote:
Ok, so as a test, I threw together a basic project, consisting of just the default main window and boiler plate code created by Qt creator. I then modified main.cpp to be the following:

#include "mainwindow.h"
#include <QApplication>
#include <QSqlDatabase>

bool setupTestConnection(){
    QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("/tmp/testDb.db");
    bool result=db.open();
    db.close();
    return result;
}

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

    bool canOpen=setupTestConnection();
    //TODO: if can't open, give appropriate error and terminate.

    qDebug("Call Result: %i, DB is STILL open: %i",canOpen,QSqlDatabase::database(QSqlDatabase::defaultConnection,false).isOpen());

    MainWindow w;
    w.show();

    return a.exec();
}

And as expected (since I called close()), the debug line did show it was closed. Then in the main window I added a QSqlTableModel member (which I called testTable), and put the following in the QMainWindow constructor to set up the table model:

testTable.setTable("test");
testTable.select();

So keeping it as basic as I can. Note that I never need to call QSqlDatabase::database(), so that particular link you sent doesn't help. When running this new application, lsof shows that the database is open at all times - so apparently the table model opened the connection and kept it open. So how is this one constructed wrong? I'm only opening the DB in main to make sure I can (which is kinda important), and I make sure to close it when I am done checking.

And BTW, I *have* read the manual. Repeatedly over the years. Obviously I have managed to miss the solutions you are seeing.
-----------------------------------------------
Israel Brewster
Systems Analyst II
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

<image001.jpg>

<image002.jpg>

On Aug 21, 2018, at 1:08 PM, Roland Hughes <roland at logikalsolutions.com<mailto:roland at logikalsolutions.com>> wrote:

You opened the database in main. The function you called is so small it most likely was placed in-line by the compiler. This would be especially true if you did nothing with dbOpen.

You have no reason to open it there. Again, read the fine manual.

http://doc.qt.io/qt-5/qsqldatabase.html#database

Every time you call QSqlDatabase::database( "someConnectionName") it will open the database if it is not currently open. You should never open the database in main. Just set up all of the values for it.

On 08/21/2018 03:54 PM, Israel Brewster wrote:
Ok, maybe you can give me a pointer on what I'm doing wrong then. My code is structured like the following:

bool openDatabase(){
QSqlDatabase db=QSqlDatabase::addDatabase('QSQLITE');
db.setDatabaseName(<path_to_my_database_file>);
return db.open();
}

 int main(int argc, char *argv[])
{
QApplication a(argc, argv);
...
<various initialization code>
...
bool dbOpen=openDatabase();
...
<more initilization>
return a.exec();
}

And if I put a breakpoint at any point after the call to OpenDatabase, the database is open - it does not close, even though that QSqlDatabase object went out of scope. At no point in my application do I keep a copy of QSqlDatabase around in any of my classes - every call to QSqlDatabase is as a function local member, and as such goes out of scope (thereby theoretically calling the destructor) as soon as the function exits.

So what am I missing here?

-----------------------------------------------
Israel Brewster
Systems Analyst II
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

<image001.jpg>

<image002.jpg>

On Aug 21, 2018, at 12:31 PM, Roland Hughes <roland at logikalsolutions.com<mailto:roland at logikalsolutions.com>> wrote:

Why are you creating yet another class instead of properly structuring your application?

Take a gander at

http://www.logikalsolutions.com/wordpress/information-technology/qt-and-usb-pt-4/

for some ideas. You probably also want to find a copy of this book

http://www.theminimumyouneedtoknow.com/qt_book.html

Most importantly you need to read the fine manual.

http://doc.qt.io/qt-5/qsqldatabase.html#dtor.QSqlDatabase

QSqlDatabase::~QSqlDatabase()

Destroys the object and frees any allocated resources.

Note: When the last connection is destroyed, the destructor implicitly calls close<http://doc.qt.io/qt-5/qsqldatabase.html#close>() to release the database connection.

See also close<http://doc.qt.io/qt-5/qsqldatabase.html#close>().

If your database is open for the life of the application then the application has a failed architecture. Database connections aren't supposed to have actual life spans.

On 08/21/2018 03:08 PM, Israel Brewster wrote:

Ideally, what I'd have is a system where you set up the connection, and then it automatically opens/closes the connection when needed (i.e. when doing a SELECT, INSERT, UPDATE, etc). With my app, this would generally only be for a) periodic updates or b) in direct response to user input, so most of the time the connection could remain closed. To the best of my knowledge (correct me if I am wrong), this is not possible with the existing QSqlDatabase/ QSQLITE database driver classes.

--
Roland Hughes, President
Logikal Solutions
(630) 205-1593

http://www.theminimumyouneedtoknow.com<http://www.theminimumyouneedtoknow.com/>
http://www.infiniteexposure.net<http://www.infiniteexposure.net/>
http://www.johnsmith-book.com<http://www.johnsmith-book.com/>
http://www.logikalblog.com<http://www.logikalblog.com/>
http://www.interestingauthors.com/blog
http://lesedi.us<http://lesedi.us/>

_______________________________________________
Interest mailing list
Interest at qt-project.org<mailto:Interest at qt-project.org>
http://lists.qt-project.org/mailman/listinfo/interest

--
Roland Hughes, President
Logikal Solutions
(630) 205-1593

http://www.theminimumyouneedtoknow.com<http://www.theminimumyouneedtoknow.com/>
http://www.infiniteexposure.net<http://www.infiniteexposure.net/>
http://www.johnsmith-book.com<http://www.johnsmith-book.com/>
http://www.logikalblog.com<http://www.logikalblog.com/>
http://www.interestingauthors.com/blog
http://lesedi.us<http://lesedi.us/>

--
Roland Hughes, President
Logikal Solutions
(630) 205-1593

http://www.theminimumyouneedtoknow.com<http://www.theminimumyouneedtoknow.com/>
http://www.infiniteexposure.net<http://www.infiniteexposure.net/>
http://www.johnsmith-book.com<http://www.johnsmith-book.com/>
http://www.logikalblog.com<http://www.logikalblog.com/>
http://www.interestingauthors.com/blog
http://lesedi.us<http://lesedi.us/>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20180821/3d45bf4c/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 2298 bytes
Desc: image001.jpg
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20180821/3d45bf4c/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 6246 bytes
Desc: image002.jpg
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20180821/3d45bf4c/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Israel Brewster.vcf
Type: text/directory
Size: 417 bytes
Desc: Israel Brewster.vcf
URL: <http://lists.qt-project.org/pipermail/interest/attachments/20180821/3d45bf4c/attachment.bin>