/* **************************************************************************** * 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 #include #include #include #include #include #include #include class QSqlQuery; class QString; class QStringList; template 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& _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& _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& 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 . 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 getFieldNamesAndTypes( const QString& tableName ) const; /*! * \brief Retrieves the relations as used between two tables. * \return A Hash containing the relations by tablename. */ QHash > 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 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& 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& 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 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 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 m_qhRelations; ///< Hash used to store the Relations queries by database type. QHash 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 */