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.                                                  *
 * ***************************************************************************/

#ifndef OSDEV_COMPONENTS_DBCONNECTOR_H
#define OSDEV_COMPONENTS_DBCONNECTOR_H

#include "dcxmlconfig.h"
#include "dbconnectionwatchdog.h"

#include <QObject>
#include <QSqlDatabase>
#include <QVariant>
#include <QList>
#include <QSqlDriver>
#include <QSqlRecord>
#include <QSharedPointer>

#include <memory>

class QSqlQuery;
class QString;
class QStringList;
template<class K, class V> class QHash;
class QSqlDatabase;

namespace osdev  {
namespace components {

class DbRelation;

/**
 * @brief Database connector
 */
class DbConnector : public QObject
{
    Q_OBJECT

public:

    /*! Default Ctor */
    DbConnector();

    /**
     * @brief Ctor with DatabaseIdentifier
     * @param sDbIdentifier Database identifier
     */
    DbConnector(const QString& sDbIdentifier);

    /*!
     * @brief Constructor with connection information
     * @param sUserName Username
     * @param sPassword Password
     * @param sDatabaseName Name of the database
     * @param sHostName Name of the host
     * @param sDbType Type of database. Could be one of the string, supported by QSQLDriver.
     * @param sDbIdentifier Used internally to distinguish between connections.
     *                      If given and already existing, it will be overwritten,
     *                      else a new connection will be created.
     */
    DbConnector(const QString& sUserName,
                const QString& sPassword,
                const QString& sDatabaseName,
                const QString& sHostName,
                const QString& sDbType,
                const QString& sDbIdentifier );

    /// copy-constructor
    DbConnector( const DbConnector& source );
    /// assignment operator
    DbConnector& operator=( const DbConnector& ) = delete;
    /// move-constructor
    DbConnector( DbConnector&& ) = delete;
    /// move operator
    DbConnector& operator=(DbConnector&&) = delete;

    /**
     * @brief Ctor with connection Information packed in a nice QHash
     * @param _qhCredentials Credentials
     */
    DbConnector( const QHash<QString, QString>& _qhCredentials );

    /*! Dtor */
    virtual     ~DbConnector();

    /**
     * @brief Connect to the database
     * @return True if connection is succesful, false otherwise
     */
    bool        connectDatabase();

    /**
     * @brief Set the username
     * @param sUserName Username
     */
    void        setUserName(const QString& sUserName );

    /// @return Current username
    QString     getUsername() const;

    /**
     * @brief Set the password
     * @param sPassword Password
     */
    void        setPassword( const QString& sPassword );

    /// @return Current password
    QString     getPassWord() const;

    /**
     * @brief Set the database name
     * @param sDatabaseName Database name
     */
    void        setDatabaseName(const QString& sDatabaseName );

    /// @return Current database name
    QString     getDatabaseName() const;

    /**
     * @brief Set the hostname where the database resides
     * @param sHostname Hostname
     */
    void        setHostName( const QString& sHostname );

    /// @return Current hostname
    QString     getHostName() const;

    /**
     * @brief Set the type of database
     * @param sDbType Type of database
     */
    void        setDbType(const QString& sDbType );

    /// @return Current type of database
    QString     getDbType() const;

    /**
     * @brief Set the database identifier
     * @param sDbIdentifier Database identifier
     */
    void        setDbIdentifier( const QString& sDbIdentifier );

    /// @return Current database identifier
    QString     getDbIdentifier() const;

    /// @return Get the last error that occurred
    QString     getLastError() const;

    /**
     * @brief Set credentials from a configuration
     * @param _qhCredentials Hash containing the credentials
     */
    void        setCredentials(const QHash<DCXmlConfig::eDbSettings, QString>& _qhCredentials );

    /// @return Current QSqlDatabase instance
    QSqlDatabase    getDatabase() const;

    /*!
     * \brief   Indicates wether the database supports transactions.
     * \return  True if so. False if not, or the database is closed (will check).
     */
    bool        supportTransactions() const;

    /*!
     * \brief   Implemented for convenience. This will start a transaction.
     *          (See also supportTransactions() before calling this method );
     * \return  True if successful( i.e. If the driver supports it). False if it failed.
     */
    bool        transactionBegin();

    /*!
     * \brief   Implemented for convenience. This will commit a transaction.
     *          (See also supportTransactions() before calling this method );
     * \return  True if successful( i.e. If the driver supports it). False if it failed.
     */
    bool        transactionCommit();

    /*!
     * \brief   Implemented for convenience. This will rollback a transaction.
     *          (See also supportTransactions() before calling this method );
     * \return  True if successful( i.e. If the driver supports it). False if it failed.
     */
    bool        transactionRollback();

    /**
     * @brief   Creates a new record.
     * @param   record to insert into sTable. All field info and values are housed
     *          inside the object......
     * @return  True on success. False on Failure.
     */
    bool        createRecord( const QSqlRecord& record, const QString& sTable );

    /**
     * @brief   Create a new record
     * @param   sFields Comma-separated list of fields
     * @param   sValues Comma-seperated list of value
     * @param   sTable Database table to change
     * @return  True on success. False on failure.
     */
    bool        createRecord( const QString& sFields,
                              const QString& sValues,
                              const QString& sTable );

    /**
     * @brief   Perform a read-query on the database.
     * @param   sQuery SQL query to perform
     * @return  QSqlQuery object pointer containing the result of the query.
     *          The caller is responsible for cleaning up the object after processing.
     */
    QSqlQuery*  readRecord( const QString& sQuery );

    /**
     * @brief   Update a field in an existing record
     * @param   sIdNumber Value to match to locate the record
     * @param   sIdField Field that must contain sIdNumber
     * @param   sValue New value
     * @param   sFieldName Field name in which so store sValue
     * @param   sTable Table in which to search/replace
     * @return  True on success. False on failure, use getLastError() to retrieve
     *          the reason for failure
     */
    bool        updateRecord( const QString& sIdNumber,
                              const QString& sIdField,
                              const QString& sValue,
                              const QString& sFieldName,
                              const QString& sTable );

    /**
     * @brief   Delete the selected record
     * @param   sIdNumber Value to match to locate the record
     * @param   sIdField Field that must contain sIdNumber
     * @param   sTable Table in which to delete
     * @return  True on success. False on failure, use getLastError() to retrieve
     *          the reason for failure
     */
    bool        deleteRecord(const QString& sIdNumber,
                             const QString& sIdField,
                             const QString& sTable );

    // Helper methods
    /**
     * @brief   Clear a whole table
     * @param   sTableName Name of the table
     * @return  True on success. False on failure, use getLastError() to retrieve
     *          the reason for failure
     */
    bool        clearTable( const QString& sTableName );

    /**
     * @brief   Read all data for a specific table
     * @param   sTable Table to read
     * @return  QSqlQuery object pointer containing the result of the query.
     *          The caller is responsible for cleaning up the object after processing.
     */
    QSqlQuery*  readTableData( const QString& sTable ) const;

    /**
     * @brief   Gets the number of records for the specified table
     * @param   sTable Table to read
     * @return  Number of records in that table
     */
    int         getNumRecords( const QString& sTable ) const;

    /**
     * @brief   Check if the given list of values for a specific field in a specific table are found as records in the database.
     * @param   sTable Table to query.
     * @param   sField The field name for which the values are checked.
     * @param   slFieldValues The values that are searched for.
     * @return  true if all values are found or the slFieldValues list was empty, false otherwise.
     */
    bool        recordsExist( const QString& sTable, const QString& sField, const QList<QVariant>& slFieldValues) const;

    // Specific Meta-Data functionality
    /**
     * @brief   Retrieve all table names
     * @return  List of table names
     */
    QStringList getTableNames() const;

    /*!
     * \brief   Removes the Schema from the variable. It searches
     *          for a pattern like <Schema>.<Table> and chops off
     *          the part before (including the) point.
     * \param   tableName - The name of the table (including the schemaname).
     * \return  Table name. Check for .isEmpty() for success.
     */
    QString stripSchemaFromTable( const QString& tableName ) const;

    /**
     * @brief   Retrieve the field-names for a table
     * @param   tableName Name of the table
     * @return  List of fields
     */
    QStringList getFieldNames( const QString& tableName ) const;

    /**
     * @brief   Retrieve the field-names and types for a table
     * @param   tableName Name of the table
     * @return  Hash of field-name => type
     */
    QHash<QString, QVariant::Type> getFieldNamesAndTypes(
            const QString& tableName ) const;

    /*!
     * \brief   Retrieves the relations as used between two tables.
     * \return  A Hash containing the relations by tablename.
     */
    QHash<QString, QList<DbRelation*> > getRelations( const QStringList& tables = QStringList() );

    /*!
     * \brief   Get the relations belonging to the given table name.
     * \param   tableName The table we like to get the constraints from.
     * \return  A List of all relation objects. Ownership of the pointers is
     *          transferred to the caller. The caller is responsible for cleaning up the objects.
     */
    QList<DbRelation*> getRelationByTableName( const QString& tableName );

    /*!
     * \brief Retrieve a primaryKey, based on the filter settings
     * \param tableName     - The table we want the primary key from.
     * \param filterField   - The fieldname we like to filter on.
     * \param filterValue   - The value we like to filter on.
     * \return The value of the primary key on success or an empty QVariant
     */
    QVariant getRelationKey(const QString& tableName, const QString& filterField, const QVariant& filterValue , const QString &foreignPrimKey = QString() ) const;

    /*!
     * \brief   Gets the primary key from the given table
     * \param   tableName - The table we would like the primary key from.
     * \return  The name of the key-field of the given table. It will return an empty string
     *          if the table doesn't have a primary key or the table doesn't exist.
     */
    QString getPrimaryKey( const QString& tableName ) const;

    /*!
     * \brief   Some databases are quite picky when it comes to tables and scheme's
     *          Based on the database we have connected, we quote the tablename
     *          to satisfy the db-driver.
     * \param   tableName - the tablename as given from the database layer.
     * \return  The quoted tablename.
     */
    QString quoteTableName( const QString& tableName ) const;

    /*!
     * \brief   Quote a fieldname if necessary.
     *          Based on the database we have connected, we quote the fieldname
     *          to satisfy the db-driver.
     * \param   fieldName - the fieldname to quote.
     * \return  The quoted fieldname.
     */
    QString quoteFieldName(const QString& fieldName ) const;

    /*!
     * \brief   Quote a fieldName if the type demands it.
     * \param   The value as QVariant. Based on its type it will receive quotes around it.
     * \return  The quote fieldValue
     */
    QString quoteFieldValue( const QVariant& value ) const;

    /*!
     * \brief   Each database has its own "quircks" when it comes to quotes.
     *          Based on the database type, the correct quote-character is returned
     * \return  Th quote-character based on the database type.
     */
    QString quoteChar() const;

    /*!
     * \brief   Some databases want to use the schema in front af the table name.
     * \param   tableName - The name of the table we want to retrieve the schema for.
     * \return  The Schema name as QString or an empty QString if unsuccessfull
     */
    QString getSchemaByTable( const QString& tableName ) const;

    /*!
     * \brief   Select the database on which to perform operations
     * \return  Selected database
     */
    QSqlDatabase selectDatabase() const;

    /*!
     * \brief   Associate a list of records to a specific foreign record.
     * \param   tableName The table to use.
     * \param   filterField The fieldname that is filtered on.
     * \param   filterValueList A comma separated list of filter values that determine the set of records the assocation is set to.
     * \param   associateToField The foreign key field.
     * \param   associateToValue The foreign record that the list of records is associated to.
     * \return  true if assocation is successful, false if not.
     */
    bool associate( const QString& tableName, const QString& filterField, const QList<QVariant>& filterValueList, const QString& associateToField, const QVariant& associateToValue );

    /*!
     * \brief   Disassociate records that do not belong to the given list but do have an assocation to the given foreign record.
     * \param   tableName The table to use.
     * \param   filterField The fieldname that is filtered on.
     * \param   filterValueList A comma separated list of filter values.
     *                          The records that do have an assocation to the given foreign record and are not part of the list will be disassociated.
     * \param   associateToField The foreign key field.
     * \param   associateToValue The foreign record from which records that are not part of the given list are disassociated from.
     * \return  true if disassocation is successful, false if not.
     */
    bool disassociate( const QString& tableName, const QString& filterField, const QList<QVariant>& filterValueList, const QString& associateToField, const QVariant& associateToValue );

    /*!
     * \brief   Start a database connection watchdog. On certain times (5 secs default) all database connections are checked and reopened if necessary.
     */
    void    startDbWatchDog( const int check_interval );

    /*!
     * \brief   Indicates the current database open or closed. It will be set by the watchdog during operation and the connection method during the initialization.
     * \return  True if database is open, False if not. If it is not supported by the database connection, we assume always True. The error from the database driver should be checked.
     */
    bool    isOpen() const { return m_bDbOpen; }

private:
    /*!
     * \brief   Execute a query on a database.
     * \param   sQuery Query to execute.
     * \return  A pointer to the executed QSqlQuery.
     * \retval  nullptr Database is not connected to or is not valid.
     * \note    The caller is responsible for disposing of the returned QSqlQuery object before the database is destroyed!
     */
    std::unique_ptr<QSqlQuery> executeQuery( const QString& sQuery ) const;

    /*!
     * \brief   Convert a QVariant to an Sql value string.
     * \return  Sql representation of the value.
     */
    QString toSqlValueString(const QVariant& value) const;

    /*!
     * \brief   Retrieve the Primary key-field from the a specific table
     *          in a MySQL Database. Called by getPrimaryKey..
     * \param   tableName - The name of the table we're investigating.
     * \return  The primary key field name for the specified the table name.
     */
    QString getPrimaryKeyMySQL( const QString& tableName ) const;

    /*!
     * \brief   Retrieve the Primary key-field from the a specific table
     *          in a PostGreSQL Database. Called by getPrimaryKey..
     * \param   tableName - The name of the table we're investigating.
     * \return  The primary key field name for the specified the table name.
     */
    QString getPrimaryKeyPostGreSQL( const QString& tableName ) const;

    /**
     * @brief   Parses the specified definition.
     * @param   definition The definition to parse.
     * @return  the indexColumn as defined in the definition (Pleonasm someone?)
     */
    QString parseDefinition( const QString& definition ) const;

    /**
     * @brief   Build the hash table, containing the relation queries for each type of database.
     */
    void            constructQueryHash();

    QPointer<DbConnectionWatchDog>  m_pWatchDog;

    QSqlDatabase    m_dataBase;        ///< Used database when a DbIdentifier is specified

    QString         m_sUserName;       ///< Username
    QString         m_sPassword;       ///< Password
    QString         m_sDatabaseName;   ///< Name of the database
    QString         m_sHostName;       ///< Host on which the database runs
    QString         m_sDbType;         ///< Type of database
    QString         m_sDbIdentifier;   ///< Identifier for the database
    bool            m_bDbOpen;         ///< Identifying the database open or closed.

    QHash<QSqlDriver::DbmsType, QString> m_qhRelations;  ///< Hash used to store the Relations queries by database type.
    QHash<QSqlDriver::DbmsType, QString> m_qhTableInfo;  ///< Hash used to store the TableMetaInfo queries by database type.

private slots:
    void    slotDbConnected( const QString& db_name, bool is_open );

};

} // End namespace components
} // End namespace osdev

#endif	/* OSDEV_COMPONENTS_DBCONNECTOR_H */