If this were one of my geek books, we would spend an inordinate amount of time with me dissecting code in order to teach you something. But, you didn’t pay for this, so I’m only going to speak about one source file. (You really shouldn’t just thieve things on the Internet, you should pay for them. Christ! There’s an ebook which sells for a buck. Are you trying to turn into a universally hated arch criminal like Jamie Dimon?) Well, at least I know two of you won’t, but the rest . . .
bool DataBaseIO::verifyTables()
{
bool retVal = false;
QString queryStr;
QSqlQuery q(db);
queryStr = "SELECT name FROM sqlite_master WHERE type='table' AND name='drawings';";
q.exec(queryStr);
if (!q.next())
{
// first time or after database deletion
// SQLite doesn't have an office Date type. When stored as real
// the values need to be Julian Day numbers
//
queryStr = "CREATE TABLE drawings ( "
"draw_dt REAL NOT NULL, "
"elm_no INT NOT NULL); ";
std::cout << "Creating database with following command: " << queryStr.toStdString().c_str() << std::endl;
if (q.exec(queryStr))
{
// a dutiful program should check the result of each exec
q.exec("CREATE UNIQUE INDEX drawing_idx on drawings( draw_dt, elm_no);");
q.exec("CREATE INDEX elm_no_idx on drawings( elm_no);");
retVal = true;
}
}
else
{
retVal = true;
}
return retVal;
}
I’m not going to go through the entire source file, but there are a few methods of interest. This one is called by the init procedure and the clear procedure. For those who have never worked with SQLite, that first select statement is how you verify a real table exists.
// either clear an existing temporary table or create a new one.
//
queryStr = "SELECT name FROM sqlite_temp_master WHERE type='table' AND name='draw_stats';";
q.exec(queryStr);
if (q.next())
{
queryStr = "DELETE FROM temp.draw_stats;";
}
else
{
queryStr = "CREATE TEMPORARY TABLE "
"draw_stats(elm_no INT PRIMARY KEY NOT NULL, hit_count INT NOT NULL, "
"since_last INT NOT NULL, pct_hits INT NOT NULL, ave_btwn INT NOT NULL, delta INT NOT NULL);";
}
Later on in the method detachedPastDueReport() you will find the above snippet. That is how you check for a temporary table’s existence. You will note you have to look in two different system tables. Yes, the PastDueReport isn’t an appropriate name, well, not an appropriate name if you are used to the version of this application found here.
Why would we have to check for the existence of a temporary table? Because, the user might not have exited the application before running this again. Could I have chosen to physically drop the table on exit? Yes. Had I done that, we wouldn’t have gotten to speak about how to check if a temporary table exists.
Each database system will have its own rules about how to check if a temporary table exists and just how long it lives. Under SQLite the rule currently is “until the creator’s connection is closed.” I have not checked into the definition of “closed.” Remember, we do not have an independent server with SQLite. It is, for all intents and purposes a glorified indexed file system. There is no guard dog or gate keeper between you and the data. There is no database user name and password required for access or anything resembling table/column level security.
With a server in the middle, monitoring connections, “closed” would mean any time connection is lost. With a local file I would suspect “closed” only covers an orderly exit. You stack dump mid application and I would highly suspect that temporary table is still hanging around when you come back. The SQLite library might clean up all temporaries upon connection initialization, but I severely doubt it because that would severely ham-fist multiple processes sharing the same database. Process one is merrily chugging along doing whatever it does, creating a few temporary tables it needs along the way and here comes process two. I didn’t create those temporaries, nuke em! Yes, that would work out well for all involved . . . NOT.
Remember what I told you in an earlier post? I used to keep this stats information in memory. This is a tiny example program for amusement and educational purposes only. Today we have Gigs of RAM. Back then I had 640K (less everything DOS took) and couldn’t even dream of ever owning a Gig of disk space. Now, in an era where we squander disk space on Facebook and cat videos, things have changed. We employ relational databases to solve problems which are ever increasing in size and scope.
With that change also comes responsibility. The old tricks we used to use during the days of chirping crickets with incredibly slow transfer rates aren’t valid anymore. (Seagate 20 MEG MFM hard drives chirped like crickets during read/write cycles.) Oh, you can code them and they will work some/most of the time, but they are incredibly poor design. You should be putting such data into either a temporary or permanent table not in some structure/object in RAM.
Temporary data will eventually become permanent just like “temporary fixes” left in place for decades.
Related Posts:
Where Did My QDebug Output Go?
