Library for SQLite and MySQL database support (lib825db)

lib825db provides a base class CDB and inherited classes CDBSQLite and CDBMySQL to facilitate apps being able to use common code for SQLite and MySQL databases.

Most of our existing apps using SQLite use the C++ wrapper code namespace sq3 provided by libsql, libsql3_7_9, or lib825sql. To edit this code to use lib825db instead is fairly simple.

Existing apps will have:

#include "sqlite3.h"
#include "sq3.hpp"

using namespace sq3;

Replace with:

#include "dbsql.h"

// Add using namespace just below all include lines
using namespace dbsql;

Make these changes to all source files in the project that have the #include "sqlite3.h" and #include "sq3.hpp" lines.

Existing apps will have a global database instance (usually in the main source file) such as:

database db;

Replace with:

CDB* appDB = nullptr;

Other source files in the project may have "extern database db;" to reference the global instance. Replace this with "extern CDB* appDB;"

Existing apps will have code to open the database such as:

db.open("/mnt/nand/apps/<appdir>/myapp.db3");
if(!db.is_open) {
   DrawErrorBox("Error opening database", 2);
}

Replace with code similar to:

CAppDatabaseCfg appDBcfg;
string path = appDataPath + CFGFILE_DB;
appDBcfg.SetArchiveFilepath(path);
appDBcfg.ReadConfig();

if(appDBcfg.GetDBType() == CAppDatabaseCfg::appDataMySQL) {
   appDB = new CDBMySQL(appDBcfg.GetServer(), appDBcfg.GetUser(), appDBcfg.GetPassword(), appDBcfg.GetDatabase());
} else {
   appDB = new CDBSQLite("/mnt/nand/apps/<appdir>/myapp.db3");
}

if(appDB->OpenDatabase() == false) {
   DEBUG_MSG("Error [%s]\n", appDB->GetErrorMsg().c_str());
   DrawErrorBox("Cannot open database", 4);
}

CAppDatabaseCfg is not part of lib825db. This code is in the ID Storage app to provide a user interface and load/save database configuration. The files appdatabase.cpp and appdatabase.h may be copied from ID Storage for your own apps and customized if desired.

This instantiates either a CDBMySQL object for MySQL or a CDBSQLite object for SQLite based on the configuration. The created object is assigned to the pointer CDB* appDB.

Existing apps will have SQL execute statements such as:

int ct;
string sql = "SELECT COUNT(*) FROM [Trucks];";
db.execute(sql, ct);

Edit the code to appear like this:

int ct;
string sql = "SELECT COUNT(*) FROM Trucks;";
appDB->Execute(sql, ct);

To support both SQLite and MySQL the brackets must be removed from table and field names in SQL statements.

Existing apps may have code such as:

string sql = "INSERT INTO [Trucks] ([ID],[TareWt]) VALUES ('" + truckID +"'," + IntToStr(tare) + ");"; 
if (rc_is_okay(db.execute(sql)) == false) {
   DrawErrorBox("Cannot insert record", 4);
}

Edit the code to appear like this:

string sql = "INSERT INTO Trucks (ID,TareWt) VALUES ('" + truckID +"'," + IntToStr(tare) + ");"; 
if (appDB->Execute(sql) == false) {
   DrawErrorBox("Cannot insert record", 4);
}

Existing apps will have code such as:

string sql = "SELECT * FROM [railcars] WHERE [ID] = '" + strId + "';";
statement st(db, sql);
cursor cur(st.get_cursor());
if(cur.step() == SQLITE_ROW) {
   cur.get("ID", curRailcar.strId);
   cur.get("Target", curRailcar.target);
}

Edit the code to appear like this:

string sql = "SELECT * FROM railcars WHERE ID = '" + strId + "';";
CDBStatement stmt(appDB, sql);
if(stmt.Step() == DBSQL_ROW) {
   stmt.Get("ID", curRailcar.strId);
   stmt.Get("Target", curRailcar.target);
}

Be sure to replace SQLITE_ROW with DBSQL_ROW.

The CDBStatement destructor will automatically cleanup the results of the query. However, there may be some cases where it is desired to cleanup the query results before the destructor runs. In this case the CDBStatement::Close() function may be called. There are also CDBStatement::Query(const char* sql) and CDBStatement::Query(const std::string& sql) functions that can be used to re-query using the same CDBStatement instance. There is also a CDBStatement constructor without the SQL parameter. In this case a Query function call is necessary to get results from the statement.

When an app finishes existing apps should close the database:

db.close();

Replace with:

appDB->CloseDatabase();
delete appDB;
appDB = nullptr;

There may be cases where an app needs to format the SQL string differently for SQLite or MySQL. This can be accomplished by checking a dynamic cast of the CDB* pointer such as:

string sql;
if(dynamic_cast<CDBMySQL*>(appDB)) {
  // Format SQL for MySQL
} else {
  // Format SQL for SQLite
}

The following example shows creating a table with an integer primary key that auto increments. The keyword is AUTO_INCREMENT in MySQL and AUTOINCREMENT in SQLite. Also note that MySQL will give an error without the AUTO_INCREMENT keyword when INSERT INTO is attempted if "id" is not supplied. SQLite does not require the AUTOINCREMENT keyword for an INTEGER PRIMARY KEY field. SQLite will automatically supply a rowid value for the primary key when INSERT INTO is performed.

string sql;
sql = "CREATE TABLE IF NOT EXISTS trans (";
if(dynamic_cast<CDBMySQL*>(appDB)) {
  sql += "id INTEGER PRIMARY KEY AUTO_INCREMENT,";
} else {
  sql += "id INTEGER PRIMARY KEY AUTOINCREMENT,";
}
sql +=  "datetm DATETIME, "
        "cost VARCHAR(16),"
        "pmt_type VARCHAR(50),"
        "badge VARCHAR(20),"
        "customer VARCHAR(20),"
        "truck VARCHAR(20),"
        "mileage INT,"
        "gross REAL,"
        "gross2 REAL,"
        "gross3 REAL,"
        "gross4 REAL,"
        "grossT REAL,"
        "axles INT,"
        "code TEXT,"
        "codeexpires INT"
");";

if(appDB->Execute(sql) == false) {
  DrawErrorBox("Error creating transaction table", 2);
}