open_source / dbconnector

/* ****************************************************************************
 * Copyright 2019 Open Systems Development BV                                 *
 *                                                                            *
 * Permission is hereby granted, free of charge, to any person obtaining a    *
 * copy of this software and associated documentation files (the "Software"), *
 * to deal in the Software without restriction, including without limitation  *
 * the rights to use, copy, modify, merge, publish, distribute, sublicense,   *
 * and/or sell copies of the Software, and to permit persons to whom the      *
 * Software is furnished to do so, subject to the following conditions:       *
 *                                                                            *
 * The above copyright notice and this permission notice shall be included in *
 * all copies or substantial portions of the Software.                        *
 *                                                                            *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR *
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,   *
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL    *
 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER *
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING    *
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER        *
 * DEALINGS IN THE SOFTWARE.                                                  *
 * ***************************************************************************/

#include "dbconnector.h"

#include <QtDebug>
#include <QSqlError>
#include <QSqlDriver>
#include <QSqlField>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlIndex>
#include <QVariant>
#include <QCoreApplication>

#include "log.h"
#include "dcxmlconfig.h"
#include "dbrelation.h"
#include "conversionutils.h"

using namespace osdev::components;

DbConnector::DbConnector()
    : m_pWatchDog( QPointer<DbConnectionWatchDog>() )
    , m_dataBase()
    , m_sUserName()
    , m_sPassword()
    , m_sDatabaseName()
    , m_sHostName()
    , m_sDbType()
    , m_sDbIdentifier()
    , m_bDbOpen( false )
    , m_qhRelations()
    , m_qhTableInfo()
{
    this->constructQueryHash();
}

DbConnector::DbConnector( const QString& sDbIdentifier )
    : m_pWatchDog( QPointer<DbConnectionWatchDog>() )
    , m_dataBase()
    , m_sUserName()
    , m_sPassword()
    , m_sDatabaseName()
    , m_sHostName()
    , m_sDbType()
    , m_sDbIdentifier( sDbIdentifier )
    , m_bDbOpen( false )
    , m_qhRelations()
    , m_qhTableInfo()
{
    this->constructQueryHash();
}

DbConnector::DbConnector( const QString& sUserName,
                          const QString& sPassword,
                          const QString& sDatabaseName,
                          const QString& sHostName,
                          const QString& sDbType,
                          const QString& sDbIdentifier )
    : m_pWatchDog( QPointer<DbConnectionWatchDog>() )
    , m_dataBase()
    , m_sUserName( sUserName )
    , m_sPassword( sPassword )
    , m_sDatabaseName( sDatabaseName )
    , m_sHostName( sHostName )
    , m_sDbType( sDbType )
    , m_sDbIdentifier( sDbIdentifier )
    , m_bDbOpen( false )
    , m_qhRelations()
    , m_qhTableInfo()
{
    this->constructQueryHash();
}

DbConnector::DbConnector(const QHash<QString, QString>& _qhCredentials )
    : DbConnector( _qhCredentials.value( "username" ),
                   _qhCredentials.value( "password" ),
                   _qhCredentials.value( "dbname" ),
                   _qhCredentials.value( "hostname" ),
                   _qhCredentials.value( "dbtype" ),
                   _qhCredentials.value( "identifier" ) )
{
    this->constructQueryHash();
}

DbConnector::DbConnector( const DbConnector& source )
    : QObject( source.parent() )
    , m_pWatchDog( QPointer<DbConnectionWatchDog>() )
    , m_dataBase( source.getDatabase() )
    , m_sUserName( source.getUsername() )
    , m_sPassword( source.getPassWord() )
    , m_sDatabaseName( source.getDatabaseName() )
    , m_sHostName( source.getHostName() )
    , m_sDbType( source.getDbType() )
    , m_sDbIdentifier( source.getDbIdentifier() )
    , m_bDbOpen( source.isOpen() )
    , m_qhRelations()
    , m_qhTableInfo()
{
    this->constructQueryHash();
}

DbConnector::~DbConnector()
{
}

/****************************************************************************
 *      C o n n e c t o r   a n d   G e t / S e t   m e t h o d s
 ****************************************************************************/
bool DbConnector::connectDatabase()
{
    m_dataBase = QSqlDatabase::addDatabase( m_sDbType, m_sDbIdentifier );
    m_dataBase.setDatabaseName( m_sDatabaseName );
    m_dataBase.setHostName( m_sHostName );
    m_dataBase.setPassword( m_sPassword );
    m_dataBase.setUserName( m_sUserName );

    m_bDbOpen = m_dataBase.open();

    if( !m_bDbOpen )
    {
        LogError( "[dbConnector::ConnectDatabase]", QString( "There was an error opening database %1 [dbConnector::ConnectDatabase] %2" )
                  .arg( m_sDatabaseName).arg(m_dataBase.lastError().text() ) );

        LogError( "[dbConnector::ConnectDatabase]", QString( "No use in continuing without a database. Ending %1" ).arg( QCoreApplication::applicationName() ) );
    }
    else
    {
        LogInfo( "[dbConnector::ConnectDatabase]", "Database successfull connected" );
    }

    return m_bDbOpen;
}

void DbConnector::setUserName( const QString& sUserName )
{
    m_sUserName = sUserName;
}

QString DbConnector::getUsername() const
{
    return m_sUserName;
}

void DbConnector::setPassword(const QString& sPassword )
{
    m_sPassword = sPassword;
}

QString DbConnector::getPassWord() const
{
    return m_sPassword;
}

void DbConnector::setDatabaseName( const QString& sDatabaseName )
{
    m_sDatabaseName = sDatabaseName;
}

QString DbConnector::getDatabaseName() const
{
    return m_sDatabaseName;
}

void DbConnector::setHostName(const QString& sHostname )
{
    m_sHostName = sHostname;
}

QString DbConnector::getHostName() const
{
    return m_sHostName;
}

void DbConnector::setDbType( const QString& sDbType )
{
    m_sDbType = sDbType;
}

QString DbConnector::getDbType() const
{
    return m_sDbType;
}

void DbConnector::setDbIdentifier(const QString& sDbIdentifier )
{
    m_sDbIdentifier = sDbIdentifier;
}

QString DbConnector::getDbIdentifier() const
{
    return m_sDbIdentifier;
}

QString DbConnector::getLastError() const
{
    return m_dataBase.lastError().text();
}

void DbConnector::setCredentials(const QHash<DCXmlConfig::eDbSettings, QString> &_qhCredentials )
{
    m_sUserName     = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbUsername );
    m_sPassword     = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbPassword );
    m_sDatabaseName = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbName );
    m_sHostName     = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbHostName );
    m_sDbType       = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbType );
    m_sDbIdentifier = _qhCredentials.value( DCXmlConfig::eDbSettings::eDbIdentifier );
}

QSqlDatabase DbConnector::getDatabase() const
{
    return selectDatabase();
}

bool DbConnector::supportTransactions() const
{
    return this->selectDatabase().driver()->hasFeature( QSqlDriver::Transactions );
}

bool DbConnector::transactionBegin()
{
    return this->selectDatabase().transaction();
}

bool DbConnector::transactionCommit()
{
    return this->selectDatabase().commit();
}

bool DbConnector::transactionRollback()
{
    return this->selectDatabase().rollback();
}

/****************************************************************************
 *                   C R U D   F u n c t i o n a l i t y
 ****************************************************************************/
bool DbConnector::createRecord( const QSqlRecord& record, const QString& sTable )
{
    // Build the fieldsList and values list, based on the QSqlRecord.
    // When done, call createRecord( sField, sValues, sTable );
    QStringList lstFields;
    QStringList lstValues;

    for( int index = 0; index < record.count(); index++ )
    {
        lstFields.append( this->quoteFieldName( record.fieldName( index ) ) );
        lstValues.append( this->quoteFieldValue( record.field(index).value() ) );
    }

    if( lstFields.isEmpty() || lstValues.isEmpty() )
    {
       return false;
    }

    return this->createRecord( lstFields.join( "," ), lstValues.join( "," ), this->quoteTableName( sTable ) );
}

bool DbConnector::createRecord(const QString& sFields,
                               const QString& sValues,
                               const QString& sTable )
{
    bool bResult = false;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        QSqlQuery oQuery( mDb );
        bResult = oQuery.exec( "INSERT INTO " + sTable
                               + " ( " + sFields + " ) "
                               + "VALUES ( " + sValues + " ) " );
        if( !bResult )
        {
            LogError("dbConnector::createRecord",
                     QString( "There was a problem adding a record : %1 [%2]" )
                            .arg( oQuery.lastQuery() )
                            .arg( oQuery.lastError().text() ) );
        }
        else
        {
            LogDebug("dbConnector::createRecord", "Record saved to database.");
        }
    }
    else
    {
        LogError("dbConnector::createRecord",
                 "Database is closed. Please connect first");
    }

    return true; // @todo : For now..
}

QSqlQuery* DbConnector::readRecord(const QString& sQuery )
{
    QSqlQuery *pQuery = nullptr;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        pQuery = new QSqlQuery( sQuery, mDb );
        if( pQuery->exec() )
        {
            LogError("dbConnector::readRecord",
                     "There was a problem reading a record : "
                     + pQuery->lastError().text());
        }
        else
        {
            LogInfo("dbConnector::readRecord", "Record updated.");
        }
    }
    else
    {
        LogError("dbConnector::readRecord",
                 "Database is closed. Please connect first");
    }

    return pQuery;
}

QSqlQuery* DbConnector::readTableData( const QString& sTable ) const
{
    QSqlQuery *pQuery = nullptr;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        pQuery = new QSqlQuery( mDb );
        QString sQuery = QString( "SELECT * FROM %1" ).arg( this->quoteTableName( sTable) );

        if ( !pQuery->exec( sQuery ) )
        {
            LogError("dbConnector::readTableData",
                     "There was an error retrieving the tableData");
            LogError("dbConnector::readTableData",
                     pQuery->lastError().text());
            LogError("dbConnector::readTableData",
                     pQuery->lastQuery());
        }
    }

    return pQuery;
}

bool DbConnector::updateRecord(const QString& sIdNumber,
                               const QString& sIdField,
                               const QString& sValue,
                               const QString& sFieldName,
                               const QString& sTable )
{
    bool bResult = false;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        QSqlQuery oQuery( mDb );
        bResult = oQuery.exec( "UPDATE " + this->quoteTableName(sTable)
                               + " SET " + sFieldName + " = " + sValue
                               + " WHERE " + sIdField + " = " + sIdNumber );
        if( !bResult )
        {
            LogError("dbConnector::updateRecord",
                     "There was a problem updating a record : "
                     + oQuery.lastError().text());
        }
        else
        {
            LogInfo("dbConnector::updateRecord", "Record updated.");
        }
    }
    else
    {
        LogError("dbConnector::updateRecord",
                 "Database is closed. Please connect first");
    }

    return bResult;
}

bool DbConnector::deleteRecord( const QString& sIdNumber,
                                const QString& sIdField,
                                const QString& sTable )
{
    bool bResult = false;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        QSqlQuery oQuery( mDb );
        bResult = oQuery.exec( "DELETE FROM "  + this->quoteTableName(sTable)
                               + " WHERE ( "  + sIdField + ")"
                               + " = ( "  + sIdNumber + " ) " );
        if( !bResult )
        {
            LogError("dbConnector::deleteRecord",
                     "There was a problem deleting a record : "
                     + oQuery.lastError().text());
        }
        else
        {
            LogInfo("dbConnector::deleteRecord",
                    "Record deleted from database.");
        }
    }
    else
    {
        LogError("dbConnector::deleteRecord",
                 "Database is closed. Please connect first");
    }

    return bResult;
}

/****************************************************************************
 *                      H E L P E R   M E T H O D S
 ****************************************************************************/
bool DbConnector::clearTable( const QString& sTableName )
{
    bool bResult = false;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        QSqlQuery oQuery( mDb );
        bResult = oQuery.exec( "DELETE FROM " + this->quoteTableName(sTableName) );
        if( !bResult )
        {
            LogError("dbConnector::clearTable",
                     "There was a problem deleting the records : "
                     + oQuery.lastError().text());
        }
        else
        {
            LogInfo("dbConnector::clearTable", "Table " + sTableName + " Emptied.");
            bResult = true;
        }
    }
    else
    {
        LogError("dbConnector::clearTable",
                 "Database is closed. Please connect first");
    }

    return bResult;
}

int DbConnector::getNumRecords( const QString& sTable ) const
{
    int nResult = -1;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        QSqlQuery oQuery( mDb );
        if( oQuery.exec( "SELECT COUNT(*) as NumRecords FROM " + this->quoteTableName(sTable) ) )
        {
            nResult = 0;
            // Retrieve the Numbers of Records
            while ( oQuery.next() )
            {
                nResult += oQuery.record().value( "NumRecords" ).toInt();
            }
        }
        else
        {
            LogError("dbConnector::getNumRecords",
                     "There was a problem counting the records : "
                     + oQuery.lastError().text());
        }
    }
    else
    {
        LogError("dbConnector::getNumRecords", "Database is closed. Please connect first");
    }

    return nResult;
}

bool DbConnector::recordsExist( const QString& sTable, const QString& sField, const QList<QVariant>& slFieldValues) const
{
    static const QString countQuery("SELECT COUNT(DISTINCT %2) as NumRecords FROM %1 WHERE %2 IN ( %3 )");
    if (slFieldValues.isEmpty())
    {
        return true;
    }

    QStringList valueList;
    for ( const auto& v : slFieldValues)
    {
        valueList.append(this->toSqlValueString(v));
    }
    QStringList uniqueValueList = valueList.toSet().toList();
    auto queryResult = this->executeQuery(countQuery
                                            .arg(this->quoteTableName(sTable))
                                            .arg(this->quoteFieldName(sField))
                                            .arg(uniqueValueList.join(",")));
    if (!queryResult)
    {
        return false;
    }

    int nResult = 0;
    while ( queryResult->next() )
    {
        nResult += queryResult->record().value( "NumRecords" ).toInt();
    }

    return ( uniqueValueList.size() == nResult );
}

QStringList DbConnector::getTableNames() const
{
    QStringList lstTableNames;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        lstTableNames = mDb.tables();
    }

    return lstTableNames;
}

QStringList DbConnector::getFieldNames( const QString& tableName ) const
{
    return QStringList( this->getFieldNamesAndTypes( tableName ).keys() );
}

QHash<QString, QVariant::Type> DbConnector::getFieldNamesAndTypes(
        const QString& tableName ) const
{
    QHash<QString, QVariant::Type> qhResult;
    QString _table;

    // Check if there is a 'dot' in the tablename. A Scheme is present which should be removed.
    if( tableName.contains( "." ) )
    {
        _table = stripSchemaFromTable( tableName );
    }
    else
    {
        _table = tableName;
    }

    // Build the correct query to get all the information of the given table.
    QString sQuery = m_qhTableInfo.value( m_dataBase.driver()->dbmsType() ).arg( _table );
    if( !sQuery.isNull() && !sQuery.isEmpty() )
    {
        QSqlQuery oQuery( this->selectDatabase() );
        if( oQuery.exec( sQuery ) )
        {
            while( oQuery.next() )
            {
                QString sFieldName = oQuery.value( 0 ).toString();
                QVariant::Type vFieldType = ConversionUtils::stringToQvarType( oQuery.value( 1 ).toString() );

                qhResult.insert( sFieldName, vFieldType );
            }
        }
        else
        {
            // The query failed. Lets figure out why..
            LogDebug( "[DbConnector::getFieldNamesAndTypes]", QString( "The query reported an error : \n %1 for error : %2 \n" )
                      .arg( oQuery.lastError().text() )
                      .arg( sQuery ) );
        }
    }

    return qhResult;
}

QSqlDatabase DbConnector::selectDatabase() const
{
    QSqlDatabase dbResult;

    if ( !m_sDbIdentifier.isEmpty() )
    {
        dbResult = QSqlDatabase::database( m_sDbIdentifier );
    }
    else
    {
        dbResult = m_dataBase;
    }

    return dbResult;
}

bool DbConnector::associate( const QString& tableName, const QString& filterField, const QList<QVariant>& filterValueList, const QString& associateToField, const QVariant& associateToValue )
{
    static const QString sQuery("UPDATE %1 SET %2 = %3 WHERE %4 IN ( %5 )");
    if (filterValueList.isEmpty())
    {
        return true;
    }

    QStringList valueList;
    for (const auto& value : filterValueList)
    {
        valueList.append(this->toSqlValueString(value));
    }
    const auto q = this->executeQuery( sQuery
                                        .arg(this->quoteTableName(tableName))
                                        .arg(this->quoteFieldName(associateToField))
                                        .arg(this->toSqlValueString(associateToValue))
                                        .arg(this->quoteFieldName(filterField))
                                        .arg(this->toSqlValueString(filterValueList)));
    if (q && !q->lastError().isValid())
    {
        return true;
    }
    return false;
}

bool DbConnector::disassociate( const QString& tableName, const QString& filterField, const QList<QVariant>& filterValueList, const QString& associateToField, const QVariant& associateToValue )
{
    static const QString sDisassociateQuery("UPDATE %1 SET %2 = NULL WHERE %3 NOT IN ( %4 ) AND %2 = %5");
    static const QString sDisassociateAllQuery("UPDATE %1 SET %2 = NULL WHERE %3 = %4");
    QString sQuery;
    if (filterValueList.isEmpty())
    {
        sQuery = sDisassociateAllQuery
                    .arg(this->quoteTableName(tableName))
                    .arg(this->quoteFieldName(associateToField))
                    .arg(this->quoteFieldName(associateToField))
                    .arg(this->toSqlValueString(associateToValue));
    }
    else
    {
        QStringList valueList;
        sQuery = sDisassociateQuery
                    .arg(this->quoteTableName(tableName))
                    .arg(this->quoteFieldName(associateToField))
                    .arg(this->quoteFieldName(filterField))
                    .arg(this->toSqlValueString(filterValueList))
                    .arg(this->toSqlValueString(associateToValue));
    }

    const auto q = this->executeQuery( sQuery );
    if (q && !q->lastError().isValid())
    {
        return true;
    }
    return false;
}

std::unique_ptr<QSqlQuery> DbConnector::executeQuery( const QString& sQuery ) const
{
    std::unique_ptr<QSqlQuery> pQuery;

    QSqlDatabase mDb = this->selectDatabase();
    if( mDb.isValid() && mDb.isOpen() )
    {
        pQuery.reset(new QSqlQuery( sQuery, mDb ));
        const auto err = pQuery->lastError();
        if(err.isValid())
        {
            QString queryString = pQuery->executedQuery();
            if (queryString.isEmpty())
            {
                queryString = pQuery->lastQuery();
            }
            LogError("dbConnector::executeQuery",
                     QString( "Query '%1' gave error : '%2'" )
                     .arg(queryString)
                     .arg(err.text()));
        }
    }
    else
    {
        LogError("dbConnector::executeQuery",
                 "Database is closed. Please connect first");
    }

    return pQuery;
}

QString DbConnector::toSqlValueString(const QVariant& value) const
{
    LogDebug("DbConnector::toSqlValueString", QString("Value is %1 (type is %2)").arg(value.toString()).arg(value.type()));
    switch(value.type())
    {
        case QVariant::Int:
            return value.toString();
        case QVariant::Uuid:
            return "'" + value.toString().replace( "{", "" ).replace( "}", "" ) + "'";
        case QVariant::List:
        {
            QStringList valueList;
            for ( const auto& v : value.toList() )
            {
                valueList.append( this->toSqlValueString( v ) );
            }
            return valueList.join( "," );
        }
        default:
            return "'" + value.toString() + "'";
    }
}

QHash<QString, QList<DbRelation*> > DbConnector::getRelations( const QStringList& tables )
{
    QHash<QString, QList<DbRelation*> > qhResult;
    /* First we get the type of database we're using. For each database type
     * ( MySQL, PostGresQL or ORACLE ) the way of retrieving is different.
     * We make sure the returned fields are always the same..
     */

    // Check if the query database was build or not..
    if( 0 == m_qhRelations.count() )
    {
        constructQueryHash();
    }

    // Return the query of our database...
    QString relQuery = m_qhRelations.value( m_dataBase.driver()->dbmsType() );
    if( relQuery.isEmpty() ) /// Probably not a match or not implemented..
    {
        return QHash<QString, QList<DbRelation*>>();
    }
    else
    {
        // Retrieve the list of tables from the configuration :
        QStringList tableList = tables;
        if( tableList.isEmpty() && 0 == tableList.count() )
        {
            tableList = this->getTableNames();
        }

        for( auto& table : tableList )
        {
            qhResult.insert( table, getRelationByTableName( table ) );
        }
    }

    return qhResult;
}

void DbConnector::constructQueryHash()
{
    m_qhRelations.insert(QSqlDriver::UnknownDbms, "");
    m_qhRelations.insert(QSqlDriver::MSSqlServer, "");

    m_qhRelations.insert(QSqlDriver::MySqlServer, "SELECT \
                                                        TABLE_SCHEMA AS schema_name, \
                                                        TABLE_NAME AS table_name, \
                                                        CONSTRAINT_NAME AS constraint_name, \
                                                        COLUMN_NAME AS index_column, \
                                                        REFERENCED_TABLE_NAME AS foreign_table, \
                                                        REFERENCED_COLUMN_NAME AS foreign_column \
                                                    FROM \
                                                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE \
                                                    WHERE \
                                                        TABLE_SCHEMA = SCHEMA() \
                                                      AND \
                                                        TABLE_NAME = '%1' \
                                                      AND \
                                                        REFERENCED_TABLE_NAME IS NOT NULL" );

    // @todo : Different schemas in the same database, with the same constraints will screw this up...
    m_qhRelations.insert(QSqlDriver::PostgreSQL, "SELECT DISTINCT \
                                                        n.nspname  AS schema_name, \
                                                        pc.relname AS table_name, \
                                                        r.conname AS constraint_name, \
                                                        pc1.relname AS foreign_table, \
                                                        r.contype AS constraint_type, \
                                                        pg_catalog.pg_get_constraintdef(r.oid) as definition \
                                                    FROM \
                                                        pg_constraint AS r \
                                                        JOIN pg_catalog.pg_namespace n on n.oid = r.connamespace \
                                                        JOIN pg_class AS pc ON pc.oid = r.conrelid \
                                                        LEFT JOIN pg_class AS pc1 ON pc1.oid = r.confrelid \
                                                    WHERE \
                                                        r.contype = 'f' \
                                                      AND \
                                                        pc.relname = '%1' \
                                                    ORDER BY \
                                                        schema_name, \
                                                        table_name, \
                                                        foreign_table" );

    m_qhRelations.insert(QSqlDriver::Oracle, "");
    m_qhRelations.insert(QSqlDriver::Sybase, "");
    m_qhRelations.insert(QSqlDriver::SQLite, "");
    m_qhRelations.insert(QSqlDriver::Interbase, "");
    m_qhRelations.insert(QSqlDriver::DB2, "" );

    // ============================================================================================================
    //                                    Build the table Information Query Hash
    // ============================================================================================================

    m_qhTableInfo.insert(QSqlDriver::UnknownDbms, "");
    m_qhTableInfo.insert(QSqlDriver::MSSqlServer, "");
    m_qhTableInfo.insert(QSqlDriver::MySqlServer, "SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = '%1'" );
    m_qhTableInfo.insert(QSqlDriver::PostgreSQL, "SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = '%1'" );
    m_qhTableInfo.insert(QSqlDriver::Oracle, "");
    m_qhTableInfo.insert(QSqlDriver::Sybase, "");
    m_qhTableInfo.insert(QSqlDriver::SQLite, "");
    m_qhTableInfo.insert(QSqlDriver::Interbase, "");
    m_qhTableInfo.insert(QSqlDriver::DB2, "" );
}

QList<DbRelation*> DbConnector::getRelationByTableName( const QString& tableName )
{
    QList<DbRelation*> qlResult;

    if ( 0 == m_qhRelations.count() || 0 == m_qhTableInfo.count() )
    {
        this->constructQueryHash();
    }

    // Return the query of our database...
    QString relQuery = m_qhRelations.value( m_dataBase.driver()->dbmsType() );
    if( !relQuery.isEmpty() )
    {
        QSqlQuery oQuery( this->selectDatabase() );
        if( oQuery.exec( relQuery.arg( stripSchemaFromTable( tableName ) ) ) )
        {
            while( oQuery.next() )
            {
                DbRelation *pRel = new DbRelation();
                pRel->setSchemaName( oQuery.record().value( "schema_name" ).toString() );
                pRel->setTableName( oQuery.record().value( "table_name" ).toString() );
                pRel->setConstraintName( oQuery.record().value( "constraint_name" ).toString() );

                switch ( m_dataBase.driver()->dbmsType() )
                {
                    case QSqlDriver::UnknownDbms:
                    case QSqlDriver::MSSqlServer:
                    case QSqlDriver::Oracle:
                    case QSqlDriver::Sybase:
                    case QSqlDriver::SQLite:
                    case QSqlDriver::Interbase:
                    case QSqlDriver::DB2:
                        pRel->setIndexColumn( "" );
                        break;
                    case QSqlDriver::PostgreSQL:
                        pRel->setIndexColumn(
                                    parseDefinition(
                                        oQuery.record().value( "definition" ).toString() ) );

                        // Get schema name associated with the foreign table, instead of the target-table.
                        pRel->setForeignTable( getSchemaByTable( oQuery.record().value( "foreign_table" ).toString() ) );

                        break;
                    case QSqlDriver::MySqlServer:
                        pRel->setIndexColumn( oQuery.record().value("index_column").toString() );
                        pRel->setForeignTable( oQuery.record().value( "foreign_table" ).toString() );
                        break;

                }
                pRel->setForeignPrimKey( this->getPrimaryKey( oQuery.record().value( "foreign_table" ).toString() ) );

                qlResult.append( pRel );
                    LogDebug( "[DbConnector::getRelationByTableName]", QString( "Relation->schemaName        : %1" ).arg( pRel->schemaName() ) );
                    LogDebug( "[DbConnector::getRelationByTableName]", QString( "Relation->tableName         : %1" ).arg( pRel->tableName() ) );
                    LogDebug( "[DbConnector::getRelationByTableName]", QString( "Relation->constraintName    : %1" ).arg( pRel->constraintName() ) );
                    LogDebug( "[DbConnector::getRelationByTableName]", QString( "Relation->foreignTable      : %1" ).arg( pRel->foreignTable() ) );
                    LogDebug( "[DbConnector::getRelationByTableName]", QString( "Relation->indexColumn       : %1" ).arg( pRel->indexColumn() ) );
            }
        }
        else
        {
            LogInfo( "[DbConnector::getRelationByTableName]", QString( "There were no relations for table : %1" ).arg( tableName ) );
            LogInfo( "[DbConnector::getRelationByTableName]", QString( "Query executed : %1" ).arg( oQuery.lastQuery() ) );
            LogInfo( "[DbConnector::getRelationByTableName]", QString( "Query reported the following error : %1" ).arg( oQuery.lastError().text() ) );
        }
    }
    else
    {
        LogInfo( "[DbConnector::getRelationByTableName]", QString( "No query was found for database type : %1" ).arg( m_dataBase.driverName() ) );
    }

    return qlResult;
}

QString DbConnector::parseDefinition( const QString& definition ) const
{
    QString _definition = definition;
    QString leftPattern  = "FOREIGN KEY (";
    QString rightPattern = ") REFERENCES";

    QString noLeft = _definition.replace( leftPattern, "" );

    return noLeft.left( noLeft.indexOf( rightPattern ) ).replace( "\"", "" );
}

QString DbConnector::stripSchemaFromTable( const QString& tableName ) const
{
    QString sResult = tableName;

    if( tableName.contains(".") )
    {
        sResult = tableName.right( tableName.size() - tableName.indexOf( "." ) - 1 ).replace("\"", "");
    }

    return sResult;
}

QVariant DbConnector::getRelationKey( const QString& tableName,
                                      const QString& filterField,
                                      const QVariant& filterValue,
                                      const QString& foreignPrimKey ) const
{
    QString l_primKey;
    if( foreignPrimKey.isNull() || foreignPrimKey.isEmpty() )
    {
        // Retrieve the primary key of the given table.
        l_primKey = this->getPrimaryKey( tableName );
    }
    else
    {
        l_primKey = foreignPrimKey;
    }

    // If none was found, return an empty QVariant
    if ( l_primKey.isNull() )
    {
        LogInfo( "[DbConnector::getRelationKey]",
                 QString( "There was no primary keyfield found for : %1" )
                 .arg( tableName ) );
        return QVariant();
    }

    // Build the QueryString with the parameters given :
    //  %1  - Primary Key ( l_primKey )
    //  %2  - tableName (Quoted if needed)
    //  %3  - filterField
    //  %4  - filterValue
    QString sQuery = QString( "SELECT \"%1\" FROM %2 WHERE \"%3\" = '%4'" )
                        .arg( l_primKey, this->quoteTableName(tableName), filterField, filterValue.toString().replace("{", "").replace( "}", "" ) );

    QVariant l_resPK;
    QSqlQuery oQuery( m_dataBase );
    if( oQuery.exec( sQuery ) && oQuery.first() && oQuery.size() == 1 )
    {
        l_resPK = oQuery.value( l_primKey );
    }
    else if( oQuery.size() > 1 )
    {
        LogInfo( "[DbConnector::getRelationKey]",
                 QString( "Multiple records found for value : %1" )
                 .arg( filterValue.toString() ) );
    }
    else
    {
        LogInfo( "[DbConnector::getRelationKey]",
                 QString( "There was no primary key retrieved for : %1" )
                 .arg( sQuery ) );
    }
    return l_resPK;
}

QString DbConnector::getPrimaryKey( const QString& tableName ) const
{
    switch( m_dataBase.driver()->dbmsType() )
    {
        case QSqlDriver::MySqlServer:
            return this->getPrimaryKeyMySQL( tableName );
        case QSqlDriver::PostgreSQL:
            return this->getPrimaryKeyPostGreSQL( tableName );
        case QSqlDriver::UnknownDbms:
        case QSqlDriver::MSSqlServer:
        case QSqlDriver::Oracle:
        case QSqlDriver::Sybase:
        case QSqlDriver::SQLite:
        case QSqlDriver::Interbase:
        case QSqlDriver::DB2:
            break;
    }
    return QString();
}

QString DbConnector::getPrimaryKeyMySQL( const QString& tableName ) const
{
    QString sQuery = QString( "SHOW INDEX FROM %1 WHERE Key_name = '%2'" )
                    .arg( tableName )
                    .arg( m_dataBase.primaryIndex( this->quoteTableName( tableName ) ).name() );

    QSqlQuery oQuery( m_dataBase );
    if( oQuery.exec( sQuery ) && oQuery.first() && oQuery.size() == 1 )
    {
        return oQuery.value( "Column_name" ).toString();
    }

    return QString();
}

QString DbConnector::getPrimaryKeyPostGreSQL( const QString& tableName ) const
{
    QString sResult;
    QString _table;
    if( tableName.contains( "." ) )
    {
        _table = this->stripSchemaFromTable( tableName );
    }
    else
    {
        _table = tableName;
    }

    QString sQuery = QString("SELECT \
                                    c.column_name AS key_field, \
                                    c.data_type AS key_type, \
                                    tc.table_name AS table_name \
                                FROM \
                                    information_schema.table_constraints tc \
                                JOIN \
                                    information_schema.constraint_column_usage AS ccu \
                                    USING (constraint_schema, constraint_name) \
                                JOIN \
                                    information_schema.columns AS c ON c.table_schema = tc.constraint_schema \
                                  AND \
                                    tc.table_name = c.table_name AND ccu.column_name = c.column_name \
                                WHERE \
                                    constraint_type = 'PRIMARY KEY' \
                                  AND \
                                    tc.table_name = '%1'").arg( _table );

    QSqlQuery oQuery( m_dataBase );
    if( oQuery.exec( sQuery ) )
    {
        // Select the first record.
        oQuery.first();
        LogDebug( "[DbConnector::getPrimaryKeyPostGreSQL]",
                  QString( "KeyField found.. : %1" ).arg( oQuery.value( "key_field" ).toString() ) );
        sResult = oQuery.value( "key_field" ).toString();
    }
    else
    {
        LogInfo( "[DbConnector::getPrimaryKeyPostGreSQL]",
                 QString( "There was an error running the query : %1" ).arg( sQuery ) );
        LogInfo( "[DbConnector::getPrimaryKeyPostGreSQL]",
                 QString( "Dblayer gave error : %1" ).arg( oQuery.lastError().text() )  );
    }

    return sResult;
}

QString DbConnector::quoteTableName( const QString& tableName ) const
{
    QString sResult( tableName );
    switch( m_dataBase.driver()->dbmsType() )
    {
        case QSqlDriver::MySqlServer:
            return sResult;

        case QSqlDriver::PostgreSQL:
            if( -1 == sResult.indexOf( "\"" )  )
            {
                // Ok. Seems like it is not quoted.
                sResult = QString( "\"" + QString( sResult.replace('.', "\".\"" ) + "\"") );
                return sResult;
            }
            else
            {
                return sResult;
            }

        case QSqlDriver::UnknownDbms:
        case QSqlDriver::MSSqlServer:
        case QSqlDriver::Oracle:
        case QSqlDriver::Sybase:
        case QSqlDriver::SQLite:
        case QSqlDriver::Interbase:
        case QSqlDriver::DB2:
            LogInfo( "[OrmHandler::quoteTableName]",
                     QString( "Unknown database requested : %1" )
                     .arg( m_dataBase.driverName() ) );
            break;
    }
    return sResult;
}

QString DbConnector::quoteFieldName( const QString& fieldName ) const
{
    QString sResult( fieldName );
    switch( m_dataBase.driver()->dbmsType() )
    {
        case QSqlDriver::MySqlServer:
            return sResult;

        case QSqlDriver::PostgreSQL:
            if( -1 == sResult.indexOf( "\"" )  )
            {
                // Ok. Seems like it is not quoted.
                sResult = QString( "\"%1\"" ).arg(fieldName);
                return sResult;
            }
            else
            {
                return sResult;
            }

        case QSqlDriver::UnknownDbms:
        case QSqlDriver::MSSqlServer:
        case QSqlDriver::Oracle:
        case QSqlDriver::Sybase:
        case QSqlDriver::SQLite:
        case QSqlDriver::Interbase:
        case QSqlDriver::DB2:
            LogInfo( "[OrmHandler::quoteFieldName]",
                     QString( "Unknown database requested : %1" )
                     .arg( m_dataBase.driverName() ) );
            break;
    }
    return sResult;
}

QString DbConnector::quoteFieldValue( const QVariant& value ) const
{
    QString sResult( value.toString() );
    switch( value.type() )
    {
        case QVariant::Uuid:
        case QVariant::Date:
        case QVariant::DateTime:
        case QVariant::String:
            sResult = QString( "%1%2%1" ).arg( quoteChar() ).arg( sResult );
            break;
        default:
            break;
    }

    return sResult;
}

QString DbConnector::quoteChar() const
{
    QString sResult = "";
    switch( m_dataBase.driver()->dbmsType() )
    {
        case QSqlDriver::MySqlServer:
            return sResult;

        case QSqlDriver::PostgreSQL:
            sResult = QString( "'" );
            return sResult;

        case QSqlDriver::UnknownDbms:
        case QSqlDriver::MSSqlServer:
        case QSqlDriver::Oracle:
        case QSqlDriver::Sybase:
        case QSqlDriver::SQLite:
        case QSqlDriver::Interbase:
        case QSqlDriver::DB2:
            LogInfo( "[OrmHandler::quoteFieldName]",
                     QString( "Unsupported database requested : %1" )
                     .arg( m_dataBase.driverName() ) );
            break;
    }
    return sResult;
}

QString DbConnector::getSchemaByTable( const QString& tableName ) const
{
    QString sResult;
    QString sQuery;

    switch( m_dataBase.driver()->dbmsType() )
    {
        case QSqlDriver::PostgreSQL:
            sQuery = QString( "SELECT \
                                    table_schema, \
                                    table_name \
                                FROM \
                                    information_schema.tables \
                                WHERE \
                                    table_name = '%1'" )
                    .arg( tableName );
            break;
        case QSqlDriver::MySqlServer:
        case QSqlDriver::UnknownDbms:
        case QSqlDriver::MSSqlServer:
        case QSqlDriver::Oracle:
        case QSqlDriver::Sybase:
        case QSqlDriver::SQLite:
        case QSqlDriver::Interbase:
        case QSqlDriver::DB2:
            LogInfo( "[OrmHandler::quoteTableName]",
                     QString( "Unknown database requested : %1" )
                     .arg( m_dataBase.driverName() ) );
            break;
    }

    // If no joy, return an empty string.
    if( sQuery.isEmpty() )
    {
        return QString();
    }

    // Geronimo!
    QSqlQuery oQuery( m_dataBase );
    if( oQuery.exec( sQuery ) )
    {
        if( oQuery.first() )
        {
            sResult = oQuery.value( "table_schema" ).toString() + QString( "." ) + tableName;
        }

    }
    return sResult;
}

void DbConnector::slotDbConnected( const QString& db_name, bool is_open )
{
    // We're not using the name (yet) already there for future implementations.
    Q_UNUSED( db_name );
    m_bDbOpen = is_open;
}

void DbConnector::startDbWatchDog( const int check_interval )
{
    if( m_pWatchDog.isNull() )
    {
        m_pWatchDog = new DbConnectionWatchDog( check_interval, this );
        #if( QT_VERSION >= QT_VERSION_CHECK( 5, 2, 0) )
            connect( m_pWatchDog.data(), &DbConnectionWatchDog::signalDbConnected, this, &DbConnector::slotDbConnected );
        #else
            connect( m_pWatchDog.data(), SIGNAL( signalDbConnected( const QString&, bool ) ), this, SLOT( slotDbConnected( const QString&  bool ) ) );
        #endif
    }

    if( m_pWatchDog )
    {
        m_pWatchDog->start();
        LogInfo( "[DbConnector::startDbWatchDog]", QString( "Database Watchdog started with interval : %1" ).arg( check_interval ) );
    }
}