Posted inInformation Technology / Raspberry Pi

Qt and USB – Pt. 4

seasoned_geek by seasoned_geek

The physical loading of the database isn’t performed by a class. It is performed by a series of C++ functions all contained in the following source file.

/*
 *      Copyright (c) 2018 Roland Hughes and Logical Solutions  ALL RIGHTS RESERVED
 *
 *      This code is "as-is" without any warranty expressed or implied. You may not modify or distribute it
 *      without having purchased the right to do so from the copyright holder. This code is provided for
 *      demonstration purposes only. Usage of it implies your express agreement to hold both the author and
 *      copyright holder harmless from damages both real and perceived.
 */


#include 
#include 

using namespace std;

#include 
#include 
#include 
#include 
#include 
#include 
#include 

static const int SPACES_PER_TAB = 4;
static const QString SPACE_STR(SPACES_PER_TAB, QChar(' '));

static const QString DB_DATE_FORMAT = QString("yyyy-MM-dd HH:mm:ss.z");

static QString lastVendor;
static QString lastDevice;
static QString lastClass;
static QString lastSubClass;
static QString lastUsagePage;
static QString lastLanguage;

void cleanLineIn(QString& lineIn)
{
    lineIn.replace(SPACE_STR, "\t");
    lineIn.remove(QRegExp("[\\n\\r]")); // get rid of trailing newline
}

QString buildVendorsSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;
    QString cmdStr;

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    switch(lineIn.at(0).toLatin1())
    {
    case '\t':
        if (lineIn.at(1) == '\t')
        {
            cmdStr = QString("INSERT INTO DEVICE_INTERVACE(VENDOR_ID, DEVICE_ID, INTERFACE, INTERFACE_NAME) "
                             "VALUES( '%1', '%2', '%3', '%4');").arg(lastVendor).arg(lastDevice).arg(keyStr).arg(nameStr);
        }
        else
        {
            cmdStr = QString("INSERT INTO VENDOR_DEVICE(VENDOR_ID, DEVICE_ID, DEVICE_NAME) "
                             "VALUES( '%1', '%2', '%3');").arg(lastVendor).arg(keyStr).arg(nameStr);
            lastDevice = keyStr;
        }
        break;
    default:
        cmdStr = QString("INSERT INTO VENDORS(VENDOR_ID, VENDOR_NAME) "
                         "VALUES('%1', '%2');").arg(keyStr).arg(nameStr);
        lastVendor = keyStr;
        break;
    }

    return cmdStr;
}

QString buildClassesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;
    QString cmdStr;

    if (lineIn.at(0) == 'C')
    {
        lineIn = lineIn.mid(2);
    }
    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    switch(lineIn.at(0).toLatin1())
    {
    case '\t':
        if (lineIn.at(1) == '\t')
        {
            cmdStr = QString("INSERT INTO PROTOCOL(CLASS_ID, SUBCLASS_ID, PROTOCOL_ID, PROTOCOL_NAME) "
                             "VALUES('%1', '%2', '%3', '%4');").arg(lastClass).arg(lastSubClass).arg(keyStr).arg(nameStr);
        }
        else
        {
            cmdStr = QString("INSERT INTO SUBCLASS(CLASS_ID, SUBCLASS_ID, SUBCLASS_NAME) "
                             "VALUES('%1', '%2', '%3');").arg(lastClass).arg(keyStr).arg(nameStr);
            lastSubClass = keyStr;
        }
        break;
    default:
        cmdStr = QString("INSERT INTO CLASS(CLASS_ID, CLASS_NAME) VALUES('%1', '%2');").arg(keyStr).arg(nameStr);
        lastClass = keyStr;
        break;
    }

    return cmdStr;
}


QString buildAudioTerminalsSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'A')
    {
        lineIn = lineIn.mid(2);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO AUDIO_TERMINAL(TERMINAL_TYPE, TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildHIDDescriptorsSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'H')
    {
        lineIn = lineIn.mid(3);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO HID_TYPES(DESCRIPTOR_TYPE, DESCRIPTOR_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildItemTypesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'R')
    {
        lineIn = lineIn.mid(2); // don't forget the trailing space
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO HID_ITEM_TYPES(ITEM_TYPE, ITEM_TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildBiasTypesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'B')
    {
        lineIn = lineIn.mid(5);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO BIAS_TYPES(ITEM_TYPE, ITEM_TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildPhysicalTypesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'P')
    {
        lineIn = lineIn.mid(4);  // don't forget trailing space
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO PHYSICAL_TYPES(ITEM_TYPE, ITEM_TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildUsagesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;
    QString cmdStr;

    if (lineIn.at(0) == 'H')
    {
        lineIn = lineIn.mid(4);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    switch(lineIn.at(0).toLatin1())
    {
    case '\t':
        cmdStr = QString("INSERT INTO USAGE_NAMES(PAGE_ID, USAGE_ID, USAGE_NAME) "
                         "VALUES( '%1', '%2', '%3');").arg(lastUsagePage).arg(keyStr).arg(nameStr);
        break;
    default:
        cmdStr = QString("INSERT INTO USAGES(PAGE_ID, PAGE_NAME) "
                         "VALUES('%1', '%2');").arg(keyStr).arg(nameStr);
        lastUsagePage = keyStr;
        break;
    }

    return cmdStr;
}

QString buildLanguagesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;
    QString cmdStr;

    if (lineIn.at(0) == 'L')
    {
        lineIn = lineIn.mid(2);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    switch(lineIn.at(0).toLatin1())
    {
    case '\t':
        cmdStr = QString("INSERT INTO DIALECTS(LANGUAGE_ID, DIALECT_ID, DIALECT_NAME) "
                         "VALUES( '%1', '%2', '%3');").arg(lastLanguage).arg(keyStr).arg(nameStr);
        break;
    default:
        cmdStr = QString("INSERT INTO LANGUAGES(LANGUAGE_ID, LANGUAGE_NAME) "
                         "VALUES('%1', '%2');").arg(keyStr).arg(nameStr);
        lastLanguage = keyStr;
        break;
    }
    return cmdStr;
}

QString buildCountryCodesSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'H')
    {
        lineIn = lineIn.mid(4);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO COUNTRY_CODE(COUNTRY_ID, KEYMAP_TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

QString buildVideoClassSQL(QString lineIn)
{
    QString keyStr;
    QString nameStr;

    if (lineIn.at(0) == 'V')
    {
        lineIn = lineIn.mid(3);
    }

    int firstSpace = lineIn.indexOf(' ');
    keyStr = lineIn.left(firstSpace).simplified();
    nameStr = lineIn.mid(firstSpace).simplified();

    return (QString("INSERT INTO VIDEO_CLASS(TERMINAL_TYPE, TERMINAL_TYPE_NAME) "
                     "VALUES('%1', '%2');").arg(keyStr).arg(nameStr));
}

void loadDbFromFile(QString filePath, QSqlDatabase db)
{
    QDateTime createDate = QFileInfo(filePath).created();

    QFile inFile(filePath);
    if (!inFile.exists())
    {
        cout << "could not locate " << filePath.toStdString() << " for input - database not loaded" << endl;
        return;
    }

    if (!inFile.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        cout << "could not open " << filePath.toStdString() << " for input - database not loaded" << endl;
        return;
    }

    std::vector tables;

    tables.push_back(buildVendorsSQL);
    tables.push_back(buildClassesSQL);
    tables.push_back(buildAudioTerminalsSQL);
    tables.push_back(buildHIDDescriptorsSQL);
    tables.push_back(buildItemTypesSQL);
    tables.push_back(buildBiasTypesSQL);
    tables.push_back(buildPhysicalTypesSQL);
    tables.push_back(buildUsagesSQL);
    tables.push_back(buildLanguagesSQL);
    tables.push_back(buildCountryCodesSQL);
    tables.push_back(buildVideoClassSQL);

    cout << db.lastError().text().toStdString() << endl;

    QString lineIn = " ";  // needs to be initialized so at() doesn't gag
    QString cmdStr;
    QSqlQuery query(db);

    db.transaction();   // start a transaction to speed up bulk insertion

    // first section is vendors
    for (auto f : tables)
    {
        cout << "top of tables loop " << endl;
        // find beginning of data
        while ((lineIn.at(0) == '#' || lineIn.length() < 3) && !inFile.atEnd()) { lineIn = inFile.readLine(); } while (lineIn.length() > 3  &&  !inFile.atEnd())
        {
            // skip any comment lines which might be in data
            if (lineIn.at(0) != '#')
            {
                cleanLineIn(lineIn);
                cmdStr = f(lineIn);
                if (cmdStr.length() > 0)
                {
                    query.exec(cmdStr);
                }
            }
            lineIn = inFile.readLine();
        }
    }

    cmdStr = QString("INSERT INTO CONTROL(FILE_DT) VALUES('%1');").arg(createDate.toString(DB_DATE_FORMAT));
    query.exec(cmdStr);
    cout << "finished loading" << endl;
    inFile.close();
    db.commit();
}

We will start our discussion with loadDbFromFile(). Yes, I verify the input file once again. While it may seem a waste of CPU cycles, this code could be used by something else later. The interesting trick is the vector of function pointers which all have the same signature. This lets me use the new style for loop to process the various sections. The functions are called in the order they exist in the input file. The first blank line found while processing a section indicates end of section and that we will have some comments and blank lines to skip before the next section starts.

You will note this entire for loop is encapsulated by a single transaction() and commit(). If you don’t start a transaction then all database I/O occurs one record at a time and it is sloooooow. On my really fast I5 desktop with a screaming hard drive I lost interest after about 25 minutes. Surrounded by a transaction() and commit() it executes in seconds. SQLite finally fixed that nasty little flaw!

Opting for this design allowed my section parsing methods to parse one line at a time returning the SQL statement needed to insert the data. Yes, I experimented with making each one do their own thing, but, the code was cumbersome and it ran slow.

I can hear the OOP purists wailing in anguish over my use of function pointers. Pure OOP isn’t always the best solution. Besides, I recently was doing some OpenVMS C programming and that code used quite a few of them. I will be the first to admit function pointers get abused. During the early days of DOS with overlay linkers, you couldn’t even be certain the function you wanted was still in local memory, causing no end of weirdness.

Ah, you kids, you have it so easy. Us old timers had to actually solve all of those problems so we could hang onto a tiny scrap of our sanity. Now you get to work on 64-bit processors and operating systems which can swap things in and out of memory near perfectly. You don’t have to deal with swapping a 64K chunk above the 640K wall then have the video card overwrite part of it. Fun times!

 

Roland Hughes started his IT career in the early 1980s. He quickly became a consultant and president of Logikal Solutions, a software consulting firm specializing in OpenVMS application and C++/Qt touchscreen/embedded Linux development. Early in his career he became involved in what is now called cross platform development. Given the dearth of useful books on the subject he ventured into the world of professional author in 1995 writing the first of the "Zinc It!" book series for John Gordon Burke Publisher, Inc.

A decade later he released a massive (nearly 800 pages) tome "The Minimum You Need to Know to Be an OpenVMS Application Developer" which tried to encapsulate the essential skills gained over what was nearly a 20 year career at that point. From there "The Minimum You Need to Know" book series was born.

Three years later he wrote his first novel "Infinite Exposure" which got much notice from people involved in the banking and financial security worlds. Some of the attacks predicted in that book have since come to pass. While it was not originally intended to be a trilogy, it became the first book of "The Earth That Was" trilogy:
Infinite Exposure
Lesedi - The Greatest Lie Ever Told
John Smith - Last Known Survivor of the Microsoft Wars

When he is not consulting Roland Hughes posts about technology and sometimes politics on his blog. He also has regularly scheduled Sunday posts appearing on the Interesting Authors blog.