As I pointed out many times in my Qt and databases book, most of the database examples you find online and even in official Qt documentation are shit. This is not my ego talking. They all do everything in the main event loop. Almost all of the cheat by using only a handful of rows so everything “seems” to work. Then you try to use it on real data and wonder why the application seems to lock up. This is the reason I pulled down data for well north of 1,000 drawings. Nowhere to hide. You can see the test data with the following file.
Now, let’s take a look at the actual import methods.
// This method is hard coded for the screen scrape I had.
// You need to modify this for any variation.
//
void DataBaseIO::importData(QString fullPathToFile)
{
QFuture future = QtConcurrent::run(this, &DataBaseIO::detachedImport, fullPathToFile);
}
void DataBaseIO::detachedImport(QString fullPathToFile)
{
QFile dataFile( fullPathToFile);
dataFile.open( QIODevice::ReadOnly | QIODevice::Text );
QTextStream in(&dataFile);
QString skippedStr;
QTextStream skipped(&skippedStr);
QString queryStr;
QSqlQuery preparedQuery(db);
preparedQuery.prepare("INSERT INTO drawings( draw_dt, elm_no) VALUES( :draw_dt, :elm_no);");
importCancel = false;
while( !in.atEnd() && !importCancel)
{
QString line = in.readLine();
QString lineCopy = line;
//std::cout << "line: " << line.toStdString().c_str() << std::endl;
line = line.simplified(); // change all whitespace to one space
line.replace( " ", ""); // remove the spaces
line.replace( "Lotto", ",", Qt::CaseInsensitive);
QStringList lst = line.split(",");
QDate drawDt = QDate::fromString( lst[0], "MM/dd/yyyy");
QStringList numLst = lst[1].split("-");
QString msgTxt = QString("%1 %2").arg(drawDt.toString("yyyy/MM/dd")).arg(lst[1]);
emit importedValue( msgTxt);
QListIterator i(numLst);
while(i.hasNext())
{
preparedQuery.bindValue(":draw_dt", drawDt.toJulianDay());
preparedQuery.bindValue( QString(":elm_no"), i.next());
preparedQuery.exec();
if (preparedQuery.lastError().isValid())
{
//std::cout << "lastError: " << preparedQuery.lastError().text().toStdString().c_str() << std::endl;
//std::cout << "skipped: " << lineCopy.toStdString().c_str() << std::endl;
skipped << lineCopy << endl; } } } skipped.flush(); dataFile.close(); if (skippedStr.length() > 0)
{
emit displayReport("Skipped Records", skippedStr);
}
emit importCompleted();
}
Most people think linearly. You do your 12 steps in order then you move on. You cannot do that with databases or any large external data set. It’s amazing how many developers I run into who claim to be Qt developers and claimed to have used SQLite who know nothing about QFuture. Threading was pretty brutal before we got QFuture as part of the package. If you’ve worked with Qt long enough you went through the era of having very little threading capability, then being told to derive your own class from QThread only to later be told, never to do that. But hey, you’ve all heard diet experts:
Starch is bad for you. Complex carbohydrates are good.
What’s a complex carbohydrate? Starch. They couldn’t come out and admit they were wrong for crying out loud! They had to use a new word.
Never ever perform database IO in the main event loop.
I don’t have a spreadsheet or graphing component in this example program, but those are the two places you see horrific examples in some very official places. It works fine when you have under 100 records the the OS level caching already has it all in RAM. When you start getting into the thousands is when you notice the hang.
Take a look at this class name: DataBaseIO. Always create an IO module for database work. This is a throwback to my VAX BASIC days when we created an IO module for each and every indexed file in our custom ERP package as a means of trapping errors. We only had ON ERROR GOTO back then, besides, do you really want 500 different modules all coding something like
GET #INVDTL, KEY #0 GE KEY$
then trapping for the error locally?
Creating an IO class also allows you to hide implementation details while forcing an architecture on the application. Take a look at the QFuture statement again. That’s a fire and forget. There is no waiting for completion in the external interface. When this is done it emits importCompleted() and possibly displayReport(). While it is running it repeatedly emits importedValue() to entertain the human. There is a class level boolean importCancel which gets set by a slot ultimately connected to the Cancel button.
Let’s look at one more pair of methods.
void DataBaseIO::top12Report()
{
QFuture future = QtConcurrent::run(this, &DataBaseIO::detachedTop12);
}
void DataBaseIO::detachedTop12()
{
QString msgTxt;
QTextStream rpt(&msgTxt);
QSqlQuery q(db);
rpt << "Number hit_count" << endl
<< "--------- ---------" << endl;
rpt.setFieldWidth(9);
rpt.setFieldAlignment(QTextStream::AlignRight);
q.exec("select elm_no, count(*) as hit_count from drawings group by elm_no order by hit_count desc limit 12;");
while (q.next())
{
QSqlRecord rec = q.record();
int no = rec.field("elm_no").value().toInt();
int hits = rec.field("hit_count").value().toInt();
rpt << no << hits << endl;
}
rpt.flush();
emit displayReport( "Top 12 Report", msgTxt);
}
While it is true I could have ran this report off of the draw_stats temporary table, that would mean I needed to put in a lot more machinery to control its creation and use. It would also mean the table would need to be permanent. Adding insult to injury it would have also required me to create that table first. After the import I worked on the bottom and top reports.
Lots of programmers coming from a Java background tend to not create a local QSqlRecord. After all, you dot-off a zillion things in Java due to the nature of the language. If you ever think you might need more than one field it is more efficient to create the local record. I’m sure somewhere someone will go to great lengths to prove that statement wrong, but I don’t care. If you want to read up on Qt and shallow copy you can do so here.
I haven’t run this application on the Pi or the Droid yet, but, it has been my experience over the years most embedded systems suck at dynamic memory allocation. I’ve talked to people who have been doing this longer than myself and that is their assessment as well. You can be using the same compiler and in some cases the same CPU and dynamic memory allocation always goes slower on the target . . . especially when on battery. Some hardware people tried to explain it to me, but, I don’t need to know why, I just need to know “There Be Dragons.”
Related Posts:
Where Did My QDebug Output Go?
