DataProvider - A Static Library for Handling Databases from C++

Introduction

As part of our work at Secured Globe, Inc., we work a lot with databases, mainly Sqlite. The idea behind this article was to create a static library to handle common database related tasks, among them:

I needed a static library which can be used whenever we want to combine a database in our code. The idea is to be able to do the following:

1. Create an ad-hoc database when and if one doesn't exist already.
2. Fill the database with default values.
3. Find the value/s of a given field/s or by running an SQL query.
4. Update or insert a new value, based on its existence, to a given field in a given table.
5. Handling settings, as a special type of table used to hold (read and write) the program's settings.
6. Read from database to memory and write to database from memory.

Functions and Functionalities

Here are some of the functions and functionalities which are part of our static library.

Using CppSQlite3U

Over the years, we have used a wrapper called CppSQlite3U, which is wrapped around sqlite3 for the Unicode support. It's true that you can support Unicode without it, but it has other useful functions and classes such as: CppSQLite3Exception, CppSQLite3DB, CppSQLite3Statement and CppSQLite3Query. The library (.cpp and .h files) can be downloaded from here.

Global Variables

Since we use CppSqlite3, we define the following global variables:

wchar_t DBFILENAME[1024]{ L"" };    // used to store the selected path for the database
CppSQLite3DB SG_DB_Handle;          // a global handle for our database
BOOL DB_Open = FALSE;               // Gives us indication if the database is open
static CCritSec SqlQueueCritSect;   // Used to lock our database during processing / updating

The SG_DBField structure

We developed a modern C++ structure which is used to hold field names and their values. For the purpose of simplicity, we store the values as strings (LPCWSTR).

First, we define possible data types for fields (and of course, that can be changed or expanded).

typedef enum _SG_DBDataType
{
    SG_DBDataInt,              // Integer
    SG_DBDataReal,             // Real / Double
    SFDBDataString,            // String
    SG_DBDataBool,             // Boolean
    SG_DBDataErr               // Error
}SG_DBDataType;

Then we have the structure code:

typedef struct _SG_DBField
{
    LPCWSTR FieldName;         // Field Name
    LPCWSTR FieldValue;        // Field Value (as String)
    SG_DBDataType ValueType;   // Field Type (SG_DBDataType)
    _SG_DBField()              // struct's constructors
    {
        ValueType = SFDBDataString;
    };
    _SG_DBField(LPCWSTR FN, LPCWSTR FV, SG_DBDataType FVType = SFDBDataString)
    {
        FieldName = FN;
        FieldValue = FV;
        ValueType = FVType;
    };
} SG_DBField;

Now, let's go to real life example - a function which updates a record in a table, keeping a pair of field name and value - unique.

void UpdateRecordUnique(LPCWSTR TableName, SG_DBField UniquePair, vector<SG_DBField> DBF);

To explain further, if you have a table for cars in a parking lot, you will want to define the license plate as a unique field, restricting having more than one record with any given license plate. Now, let's say we add a new car to this table, which we call CARS.

LicensePlate: 356-RBF
Model: Chevrolet
Date: 8/15/2021 10:00 AM

We would then want to use the following data as parameters of this function:

Tablename - _T("CARS:)
UniquePair - {_T("LicensePlate"), _T("356-RBF") }

followed by a vector with all the data we wish to insert or update in our database.

UpdateRecordUnique(
    L"CARS",
    {
        SG_DBField(L"LICENSEPLATE", L"356RBF")
    },
    {
        SG_DBField(L"LICENSEPLATE", L"356RBF")
        SG_DBField(L"MODEL", L"CHEVROLET"),
        SG_DBField(L"DATE", L"8/15/2021 10:00 AM", SG_DBDataDate)
    }
);

Creating an Ad-Hoc Database

We always tend to create our database whenever it is missing (i.e., during the first run of the program). That aligns with our concept of "no installation", which allows the user just to double click and start the software and if needed, the software will install itself. For that reason, we have:

void CreateDB();

This function will create the database from scratch, but let's first discuss when will that function be called.

There is another function we call first, which opens the database if it exist, or creates one (and then opens it) if it doesn't.

bool OpenDB()
{
    bool result = FALSE;
    if (PathFileExists(DBFILENAME) == FALSE)    // needs to create DB
    {
        CreateDB();
        DBCreateDefaultSettings();
    }
    if (DB_Open) goto db_open;
    // Open database
    try
    {
        SG_DB_Handle.open(DBFILENAME);
    }
    catch (CppSQLite3Exception &e)
    {
        return false;
    }
 db_open:
     DB_Open = true;
     return true;
 }

As discussed, we check whether DBFILENAME exists and if it doesn't, we create a brand-new database. But what if there is an older version of our database and we want to replace it with a new one? For that scenario, we use:

#define DB_VER                            4 // this number will increment from 0 whenever 
                                            // we update the structure of the database

We use DB_VER as follows:

First, let's define some constants:

APP_REGISTRY_NAME <a unique name for your program>

We store it in the Registry. We read its last stored value and compare it with this definition (DB_VER), so when the database structure is changed, a new database will be created, replacing the old one.

// Load last stored DB version
WriteLogFile(_T("Get last DB version"));

LONG lResult;
HKEY hKey;
int DBVer = 0;
DWORD dwLen;
CString w_szRegPath;

// Open Registry key
w_szRegPath.Format(L"SOFTWARE\\%s\\%s", APP_REGISTRY_NAME, APP_REGISTRY_NAME);
lResult = RegOpenKeyEx(HKEY_CURRENT_USER, w_szRegPath, 0, KEY_ALL_ACCESS, &hKey);
if (lResult == ERROR_SUCCESS)
{
    // Read key from Registry
    dwLen = sizeof(DWORD);
    lResult = RegGetValue(hKey, NULL, DBVER, RRF_RT_ANY, NULL, &DBVer, &dwLen);
    if (lResult != ERROR_SUCCESS)
    {
        DBVer = 0;
    }
}
RegCloseKey(hKey);

if (DBVer == 0)
{
    // First run of our software
    SG_FormatMessageBox(L"Welcome to <MY SOFTWARE> \nThis is your first run");
}
else if (DB_VER > DBVer)
{
    if (PathFileExists(DBFILENAME))
    {
        BOOL fSuccess = FALSE;
        while (-1)
        {
            fSuccess = DeleteFile(DBFILENAME);
            if (!fSuccess) // Database may be opened by another program
            {
                CString Error;
                Error.Format(L"Need to delete DB file '%s' Error %d.
                Please close any programs that are using it", DBFILENAME, GetLastError());
                SG_MessageBox(Error, L"Error", MB_OK);
            }
            else
                break;
        }
        SG_MessageBox(L"Database structure has been changed,
                        so old DB was deleted", L"Info", MB_OK);
    }
}

// Save DB Version
lResult = RegCreateKeyEx(HKEY_CURRENT_USER, w_szRegPath, 0, NULL,
    REG_OPTION_NON_VOLATILE, KEY_READ | KEY_WRITE, NULL, &hKey, NULL);
if (lResult != ERROR_SUCCESS)
{
    WriteLogFile(L"Error Storing DB ver in Registry (open)");
}
else
{
    // set value
    DBVer = DB_VER;
    lResult = RegSetValueEx(hKey, DBVER, 0, REG_DWORD,
              (const BYTE*)&DBVer, sizeof(DWORD));
    if (lResult != ERROR_SUCCESS)
    {
        WriteLogFile(L"Error Storing DB ver in Registry");
    }
    RegCloseKey(hKey);
}

Now, let's get back to the creation of the database. For the purpose of this article, let's assume we would like to have a table named BATCH which will look like this:

We define a string which will be used whenever we need to create this table:

wchar_t CreateBatchTable[] = L"CREATE TABLE BATCH ( \
    ID    INTEGER PRIMARY KEY AUTOINCREMENT, \
    DataSourceFileName    TEXT NOT NULL, \
    DATASOURCEID    INTEGER, \
    UniqueFieldName    TEXT, \
    DataSourceType    INTEGER, \
    ProcessingType    INTEGER, \
    Status    INTEGER \
); ";

That would be the equivalent to running the following table creation query from DB Browser:

CREATE TABLE "BATCH" (
    "ID"    INTEGER,
    "DataSourceFileName"    TEXT NOT NULL,
    "DATASOURCEID"    INTEGER,
    "UniqueFieldName"    TEXT,
    "DataSourceType"    INTEGER,
    "ProcessingType"    INTEGER,
    "Status"    INTEGER,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

As a result, our CreateDB() function will look like this:

void CreateDB()
{
    WriteLogFile(L"DB Path '%s'", DBFILENAME);
    SG_DB_Handle.close();
    SG_DB_Handle.open(DBFILENAME);
    BOOL result = TRUE;
    BOOL DBCreated = FALSE;

    try
    {
        if (!SG_DB_Handle.tableExists(L"BATCH"))
        {
            WriteLogFile(L"Creating table BATCH");
            result = SG_DB_Handle.execDML((LPCTSTR)CreateBatchTable);
            DBCreated = TRUE;
        }
        // Add here other code for other tables
    }
    catch (CppSQLite3Exception & e)
    {
        result = FALSE;
    }
    if (DBCreated)
        WriteLogFile(L"Creating database %s", DBFILENAME);

    return;
}

Note that CreateDB() can be changed to return true/false based on its success.

The WriteLogFile() Function and the logfunc() Member

The WriteLogFile() is a function I developed as part of the following article. As part of our DataProvider namespace, we defined the following member, to be used to write to a log file, or do anything else (like display a message on screen, or just call wprintf(). For such flexibility, we defined the following function data type:

typedef void(*logFunc)(LPCWSTR lpText, ...);

Then, we just add a member of logFunc to our namespace.

logFunc logfunc{ nullptr };

We use logfunc() as follows:

if (logfunc) logfunc(L"SQL Error %s in query %s", e.errorMessage(), sql);

By doing so, we take into consideration a scenario where the programmer who uses our library doesn't want to have anything stored in a log file or printed anywhere, in such case, logfunc will be equal to nullptr.

Storing Data in a Bi-dimensional Vector

I find it very useful to use a bi-dimensional vector of std::wstring.

vector<vector<wstring>> data

I used that for many projects, mostly to store a grid of values, like an Excel sheet.

Here are some useful functions:

Creating an Ad-Hoc Table Based on a Bi-dimensional Vector of std::wstring

The following function creates an ad-hoc table named 'TEMP' and stores in it the values of a bi-dimensional vector of std::wstring. We treat the grid's header differently, assuming it would contain the field names. We predefine TABLE_TEMP as the name of the table (i.e., "TEMP"). Note that in all of our tables, there is one field name common to all, which is used as a unique ID, and is called "UID". It would normally be an auto incremental Integer.

For example, if our table is CLIENTS, it would have the following creation string:

CREATE TABLE "CLIENTS" (
    "UID"    INTEGER,
    "LASTUPDATE"    DATE,
    "ClientID"    TEXT,
    "ClientName"    TEXT,
    PRIMARY KEY("UID" AUTOINCREMENT)
);

so as you can see, the UID field has a special purpose.

Now the function itself looks like this:

bool CreateTempTableFromData(vector<vector<wstring>> data)
{
    if (data.size() == 0) return false;
    bool result = FALSE;
    vector<wstring> header; // We treat the grid header differently
    header = data[0];

    wstring UniquePairName{ L"" }, UniquePairValue{ L"" };

    wstring CreateTempTable;
    if (logfunc) logfunc(L"Deleting old %s Table", TABLE_TEMP);
    DeleteTable(TABLE_TEMP);

    CreateTempTable = L"CREATE TABLE IF NOT EXISTS ";
    CreateTempTable += TABLE_TEMP;
    CreateTempTable +=L"(";

    for (int i = 0; i < header.size(); i++)
    {
        wstring FieldType;
        if (header[i] == L"UID") // Is this our Index field?
        {
            UniquePairName = header[i];
            FieldType = L" INTEGER";
        }
        else
            FieldType = L" TEXT";

        CreateTempTable += L"\"";
        CreateTempTable += header[i];
        CreateTempTable += L"\"";
        if (i == header.size() - 1)
            CreateTempTable += FieldType + (wstring)L"); ";
        else
            CreateTempTable += FieldType + (wstring)L", ";
    }
    OpenDB();
    try
    {
        SG_DB_Handle.execQuery(CreateTempTable.c_str());
    }
    catch (CppSQLite3Exception &e)
    {
        wstring err = e.errorMessage();
        if(logfunc) logfunc(L"SQL Error: %s. Query = %s",
        err.c_str(),
            CreateTempTable.c_str());
        return false;
    }

    wstring w_szSQL_insert = L"";
    for (int i = 1; i < data.size(); i++)
    {
        vector<SG_DBField> Param;
        int j;
        vector<wstring>values = data[i];
        for (j = 0; j < header.size(); j++)
        {
            if (header[j] == UniquePairName && values.size() > j)
            {
                UniquePairValue = values[j];
            }
            if (values.size() <= j)
                break;
            SG_DBField temp(header[j].c_str(), values[j].c_str());
            Param.push_back(temp);
        }
        if (j == 0)
            continue;
        if (j < header.size())
        {
            for (j = values.size(); j < header.size(); j++)
            {
                SG_DBField temp{ header[j].c_str(), L"" };
                Param.push_back(temp);
            }
        }
        if (UniquePairName != L"")
        {
            CDataProvider::InsertOrUpdate(
                TABLE_TEMP,
                SG_DBField(UniquePairName.c_str(), UniquePairValue.c_str()),
                Param.size(),
                Param
            );
        }
        else
        {
            CString Fields, Values;
            wstring insertQuery;

            MakeFieldsValues(Fields, Values, Param.size(), Param);

            insertQuery = MakeInsertStatement(TABLE_TEMP, Fields, Values);
            w_szSQL_insert.append(insertQuery.c_str());
            w_szSQL_insert.append(L"; ");

            if ((i % 100) == 0)
            {
                if (logfunc)
                    logfunc(L"%d / %d", i, data.size());

                try
                {
                    OpenDB();
                    string w_szaSQL_insert = w2string(w_szSQL_insert);
                    SQL_BEGIN_TRANSACTION;
                    SQL_EXECUTE(w_szaSQL_insert.c_str());
                    SQL_COMMIT;
                }
                catch (CppSQLite3Exception& e)
                {
                    if (logfunc) logfunc(L"Error inserting record: %s\n", e.errorMessage());
                    Sleep(1500);
                }
                w_szSQL_insert.clear();
                w_szSQL_insert = L"";
            }
        }
    }

    if (((data.size() - 1) % 100) != 0)
    {
        try
        {
            OpenDB();
            string w_szaSQL_insert = w2string(w_szSQL_insert);
            SQL_EXECUTE(w_szaSQL_insert.c_str());
        }
        catch (CppSQLite3Exception& e)
        {
            if (logfunc) logfunc(L"Error inserting record: %s\n", e.errorMessage());
            Sleep(1500);
        }
        w_szSQL_insert.clear();
        w_szSQL_insert = L"";
   }
   return result;
}

History

• 8^th January, 2022: Initial version