While this class provides access to the database, the bulk of database creation is split off into a regular C++ 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 "logikalusbinfo.h"
#include
#include
using namespace std;
#include
#include
#include
#include
#include
#include
#include
//static const char *defaultIdPaths[] = { "/var/lib/usbutils/usb.ids", "/usr/share/kcmusb/usb.ids", "/usr/share/misc/usb.ids"};
static const char *defaultIdPaths[] = { "/home/roland/usb.ids"};
extern void loadDbFromFile(QString filePath, QSqlDatabase db);
LogikalUSBInfo::LogikalUSBInfo(QObject *parent) : QObject(parent)
{
databasePath = QDir::homePath(); // default to user home dir
}
LogikalUSBInfo *LogikalUSBInfo::getInstance()
{
// C++11 Magic static variable. Compiler ensures it only
// gets created once and is thread safe.
//
static LogikalUSBInfo myInfo;
// Return a reference
return &myInfo;
}
void LogikalUSBInfo::initializeDatabase(QString destDir, QString destName, QString usbIdsPath)
{
databaseInitialized = false;
if (!destDir.isEmpty())
{
databasePath = destDir;
}
if (!destName.isEmpty())
{
dbName = destName;
}
QSqlDatabase db = QSqlDatabase::addDatabase(driverName, connectionName); // this name is internal and used by QSqlDatabase to keep track of connection
QString fullPathName = QDir(databasePath).absoluteFilePath( dbName);
db.setDatabaseName( fullPathName); // Here we set the exact path and name of the database file
// Now we need to find our file and check the date against last database load
//
if (idFileLocated(usbIdsPath))
{
if (!databaseExists() || !databaseIsCurrent())
{
createNewDatabase();
}
else
{
databaseInitialized = true;
emit databaseReady();
}
}
else
{
retrieveFileAndCreateDb();
}
}
bool LogikalUSBInfo::idFileLocated(QString usbIdsPath)
{
if (usbIdsPath.length() > 0)
{
if (QFile::exists(usbIdsPath))
{
idFilePath = usbIdsPath;
return true;
}
else
{
cout << "File: " << usbIdsPath.toStdString() << " not found - trying defaults " << endl;
}
}
for (auto ptr : defaultIdPaths)
{
if (QFile::exists(ptr))
{
idFilePath = ptr;
return true;
}
}
return false;
}
bool LogikalUSBInfo::databaseExists()
{
QSqlDatabase db = QSqlDatabase::database(connectionName, false);
return QFile::exists(db.databaseName());
}
void LogikalUSBInfo::createNewDatabase()
{
QSqlDatabase db = QSqlDatabase::database(connectionName, false);
(void) db.close(); // just in case
(void) QFile::remove(db.databaseName());
if (!db.open())
{
cout << "unable to open new empty database " << endl;
return;
}
QSqlQuery query;
QString cmd = "CREATE TABLE CONTROL( FILE_DT TEXT);";
db.transaction(); // make this a bit quicker via transaction
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating control table: " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE VENDORS(VENDOR_ID TEXT PRIMARY KEY, VENDOR_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating vendors table" << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE VENDOR_DEVICE(VENDOR_ID TEXT REFERENCES VENDORS(VENDOR_ID) NOT DEFERRABLE, "
"DEVICE_ID TEXT, "
"DEVICE_NAME TEXT, "
"PRIMARY KEY (VENDOR_ID, DEVICE_ID));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating vendor_device table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE DEVICE_INTERFACE(VENDOR_ID TEXT REFERENCES VENDORS(VENDOR_ID) NOT DEFERRABLE, "
"DEVICE_ID TEXT REFERENCES VENDOR_DEVICE(VENDOR_ID, DEVICE_ID), "
"INTERFACE TEXT, "
"INTERFACE_NAME TEXT, "
"PRIMARY KEY(VENDOR_ID, DEVICE_ID, INTERFACE));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating device_interface table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE CLASS(CLASS_ID TEXT PRIMRY KEY, CLAS_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating class table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE SUBCLASS(CLASS_ID TEXT REFERENCES CLASS(CLASS_ID) NOT DEFERRABLE, "
"SUBCLASS_ID TEXT, "
"SUBCLASS_NAME TEXT, "
"PRIMARY KEY(CLASS_ID, SUBCLASS_ID));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating subclass table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE PROTOCOL(CLASS_ID TEXT REFERECES CLASS(CLASS_ID) NOT DEFERRABLE, "
"SUBCLASS_ID TEXT REFERENCES SUBCLASS(CLASS_ID, SUBCLASS_ID) NOT DEFERRABLE, "
"PROTOCOL_ID TEXT, "
"PROTOCOL_NAME TEXT, "
"PRIMARY KEY(CLASS_ID, SUBCLASS_ID, PROTOCOL_ID));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating protocol table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE AUDIO_TERMINAL(TERMINAL_TYPE TEXT PRIMRY KEY, TYPE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating audio_terminal table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE HID_TYPES(DESCRIPTOR_TYPE TEXT PRIMARY KEY, DESCRIPTOR_NAME);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating hid_types table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE HID_ITEM_TYPES(ITEM_TYPE TEXT PRIMARY KEY, ITEM_TYPE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating hid_item_types table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE BIAS_TYPES(ITEM_TYPE TEXT PRIMARY KEY, ITEM_TYPE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating bias_types table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE PHYSICAL_TYPES(ITEM_TYPE TEXT PRIMARY KEY, ITEM_TYPE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating physical_types table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE USAGES(PAGE_ID TEXT PRIMARY KEY, PAGE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating usages table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE USAGE_NAMES(PAGE_ID TEXT REFERENCES USAGES(PAGE_ID) NOT DEFERRABLE, "
"USAGE_ID TEXT, "
"USAGE_NAME, "
"PRIMARY KEY(PAGE_ID, USAGE_ID));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating usage_names table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE LANGUAGES(LANGUAGE_ID TEXT PRIMARY KEY, LANGUAGE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating languages table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE DIALECTS(LANGUAGE_ID TEXT REFERENCES LANGUAGES(LANGUAGE_ID) NOT DEFERRABLE, "
"DIALECT_ID TEXT, "
"DIALECT_NAME TEXT, "
"PRIMRY KEY(LANGUAGE_ID, DIALECT_ID));";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating dialects table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE COUNTRY_CODE(COUNTRY_ID TEXT PRIMARY KEY, KEYMAP_TYPE TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating country_code table " << query.lastError().text().toStdString() << endl;
}
cmd = "CREATE TABLE VIDEO_CLASS(TERMINAL_TYPE TEXT PRIMARY KEY, TERMINAL_TYPE_NAME TEXT);";
query = db.exec( cmd);
if (query.isValid())
{
cout << "Error creating video_class table " << query.lastError().text().toStdString() << endl;
}
db.commit(); // now we can commit all of the tables.
// Yes, in a production system there would be far more error handling before a commit.
emit loading();
QFuture future = QtConcurrent::run(loadDbFromFile, idFilePath, db);
// QFuturewatcher is a template, not a class, have to use old syntax
//
watcher.setFuture(future);
connect(&watcher, SIGNAL(finished()), this, SLOT(databaseLoaded()));
}
void LogikalUSBInfo::databaseLoaded()
{
databaseInitialized = true;
emit loadingComplete();
}
void LogikalUSBInfo::retrieveFileAndCreateDb()
{
// TODO::
}
bool LogikalUSBInfo::databaseIsCurrent()
{
bool retVal = false;
if (idFilePath.length())
{
QDateTime createDate = QFileInfo(idFilePath).created();
QSqlDatabase db = QSqlDatabase::database(connectionName, true);
QString cmdStr = QString("SELECT FILE_DT FROM CONTROL LIMIT 1;");
QSqlQuery q = db.exec(cmdStr);
if (q.next())
{
QSqlRecord rec = q.record();
QDateTime lastLoaded = rec.field("FILE_DT").value().toDateTime();
if (createDate == lastLoaded)
{
retVal = true;
}
}
}
return retVal;
}
QString LogikalUSBInfo::vendorName(QString vendorID)
{
QString retVal;
if (isDBReady())
{
QSqlDatabase db = QSqlDatabase::database(connectionName, true);
QString cmdStr = QString("SELECT VENDOR_NAME FROM VENDORS WHERE VENDOR_ID = '%1' LIMIT 1;").arg(vendorID);
QSqlQuery q = db.exec(cmdStr);
//
// in case there is a miss
if (q.next())
{
QSqlRecord rec = q.record();
retVal = rec.field("VENDOR_NAME").value().toString().trimmed();
}
}
return retVal;
}
QString LogikalUSBInfo::deviceName(QString vendorID, QString productID)
{
QString retVal;
if (isDBReady())
{
QSqlDatabase db = QSqlDatabase::database(connectionName, true);
QString cmdStr = QString("SELECT DEVICE_NAME FROM VENDOR_DEVICE WHERE VENDOR_ID = '%1' AND DEVICE_ID = '%2' LIMIT 1;").arg(vendorID).arg(productID);
QSqlQuery q = db.exec(cmdStr);
//
// in case there is a miss
if (q.next())
{
QSqlRecord rec = q.record();
retVal = rec.field("DEVICE_NAME").value().toString().trimmed();
}
}
return retVal;
}
Because the bulk of our default values are assigned in the class definition, our constructor does very little. Had I wished, I could have even assigned that value in the header file.
This brings up an interesting topic. Initializing default values in the header file. I’m a big fan of it, except when I will needlessly have to include additional headers. I’m also not a big fan of empty constructors. Because I chose to do a few lazy-man one-liner methods in the header file I had to include QDir. When I’m writing code for my own use, I put the one-liners in the header file a lot. The downside of doing this in a production system is you don’t get your doxygen comments. Granted, I didn’t use any doxygen comments to generate any documentation here. Every shop I go to has a different standard, and I have not adopted my own standard.
getInstance() is another interesting method. I know, everybody and their brother tells you to return a reference, after they tell you not to use a singleton class, but you can’t use a reference to connect signals and slots.
initializeDatabase() is pretty straight forward. We establish our path and database name. Either open an existing database or create an empty database file. Search for the usb.ids file. If the database does not exist or is not current we call the method to create a new database. If we cannot locate the usb.ids file we call the stubbed retrieveFileAndCreateDb() method. That will be fleshed out at a later date. Assuming we find a database and it is current we set our initialized flag and emit databaseReady().
idFileLocated() is a rather simple method. We look for the defined file. If it isn’t found we search a vector of known locations stopping when we find a valid one.
createNewDatabase() gets a bit interesting. We nuke any existing file, create a new empty one, then issue a series of create statements to create all of the tables. I bound these query executions by a single transaction() and commit(). This helps improve performance. The interesting part is we thread off the actual database load via QFuture and then assign it to a QFutureWatcher which is a class member. The watcher has to outlive this method. You will also notice the old school connect() statement. New style connect() does not work with templates and QFutureWatcher is a template.
The only other method worthy of discussion is databaseIsCurrent(). You may have noticed in the CREATE SQL statements that I created a CONTROL table with a single column. In truth it is a single record containing a single field. Most of today’s script kiddies don’t think about this. You don’t want to pay the price of creating this database each and every time initializeDatabase() is called. We store in text format the create date and time of the usb.ids file which was used. Yes, in a production quality system I would have also stored the full path to the file so we wouldn’t blindly compare the creation timestamp of some unknown file to whatever the user told us to use this time. For this example, what I did is optimal and now I’ve told you how to make it a bit more bullet proof.
