DBManager

DBManager is an open source database management library developed by Nintersoft team. It has been developed as part of SmartClass project, but it bacame an independent project what makes it able to incorporate into other projects (some of them developed by us).

Menu

DBManager details

File name: DBManager.dll, DBManager.so
Version: 1.0.0.0
Price: Free
Release year: 2018
Operational System: Windows, MAC, Linux
Licence: Nintersoft Open Source Code Licence

What is it for?

DBManager is a database interface library created from Qt libraty. This class is designed to auxiliate studants and inexperient programmers to manage and manipulate either SQLite or MySQL database tables. In other words, you will be able to make simple operations like: create tables, insert values, check if the entrie exists, retrieve values, and others; in databases, everything in a easy and uncomplicated manner, even if the programmer does not know SQL.

Dependencies (Windows)

In order to use the DBManager you must include some information while developing your project, but do not worry, besides the sample program, we are going to give you the steps to link your project against our library.

    Project directory

First, you have to create a folder called dbmanager in the project directory (if you give another name, you will have to update the directory variable at QMake configuration), and then, copy the header files (*.h) and the link library file (*.lib) there.

Finally, you will have to copy the dbmanager.dll file to the directory of the executable file (before running it).

    QMake (.pro file)

You have to add the following lines:

    Header

You have to add the following lines:

Dependencies (Linux/MAC)

If you are working with nom Windows systems, you will have to download the source code in our official repository and compile the project.

After, you will just have to follow the steps described in this tutorial : https://wiki.qt.io/How_to_create_a_library_with_Qt_and_use_it_in_an_application.

Methods, classes and enumerators

    enum DBManager::DBConnectionType

       DBManager::DBConnectionType::SQLITE (0x00)

Indicates that the current database is SQLite.

       DBManager::DBConnectionType::MYSQL (0x01)

Indicates that the current database is MySQL.

    class DBManager::DBData

It is a data storage class, which is used as parameter to configure the current database manager. It  has the following properties:

They must be accessed/modified through the following methods:

    DBManager(const DBManager::DBData &data = DBData(), const QString &tablePrefix = “”,
DBConnectionType connectionType = SQLITE, const QString &connectionName = QString(“default”));

Builds an object with the properties defined in the DBManager::DBData class, including the table name, host name, user, password, etc.

The other initialization parameters are the default table prefix, the database type (MySQL or SQLite) and the connection name, which is extremely important in case of the application works with multiple simultaneous database connections.

It is important to notice that the connection is not opened right after the constructor, but you have to call the “openDB()” method so as to establish connection.

    DBManager(const QStringList &data, DBConnectionType connectionType = SQLITE,
const QString &connectionName = QString(“default”));

This is an overloaded constructor, it builds the internal data according to the ones given in the QStringList.

It is important to notice that the data given to the constructor have a certain order (and fixed size: 7), they are:

MySQL

SQLite

The other initialization parameters are the default table prefix, the database type (MySQL or SQLite) and the connection name, which is extremely important in case of the application works with multiple simultaneous database connections.

    bool createTable(const QString &tableName, const QStringList &columns);

This method creates new table in the database, where the first parameter is the name of the table and the second is the string list containing the name of the columns and the data type.

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

    bool insertRow(const QString &tableName, const QStringList &columnName, const QList<QVariant> &data);

This method insert data into an existent table (received through the first parameter), where the second parameter is the name of the columns and the third their corresponding values.

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

    bool updateRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QStringList &columnName, const QList<QVariant> &data, const QString &operation = “=”);

This method updates the table (given in the first parameter) values.
The new values must be given in fith parameter and its respective column on the fourth.
Hence, the second and the third parameter gives the values and column conditions of the columns in which the rows will be updated. The comparison operation must be given on the sixth parameter (the operation is the equality one by default).

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

    bool updateRow(const QString &tableName, const QStringList &columnNameCond, const QList<QVariant> &condition, const QStringList &columnName, const QList<QVariant> &data, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test.

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

    bool removeRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = “=”);

This method removes every occurrence of the returned values which fits the conditional test.

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

   bool removeRow(const QString &tableName, const QStringList &columnNameCond, const QList<QVariant> &condition, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test.

Returns true if the operation occurred successfully, or false otherwise (or in case of any syntax error of the given values).

    bool rowExists(const QString &tableName, const QString &columnNameCond, const QVariant &data, const QString &operation = “=”);

This method checks if there is at least one row which matchs to the condition criteria. It returns true if at least a row is returned, or false otherwise (or in case of any syntax error of the given values).

    bool rowExists(const QString &tableName, const QStringList &columnNameCond, const QList<QVariant> &data, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test.

Returns true if at least a row is returned, or false otherwise (or in case of any syntax error of the given values).

    QList<QVariant> retrieveRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = “=”);

This method is similar to “rowExists”. However, not only does it inform you if there is a row which matchs the search criteria, but also returns the first complete occurrence. In other words, it returns the data contained on the resulting table row.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList<QVariant> retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QList<QVariant> &condition, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList<QVariant> retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QList<QVariant> &condition, QStringList columnName, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test. This method also allows you to specify which columns will be in the returned list.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAll(const QString &tableName);

This method returns all entries of the specified table. It is similar to the “retrieveRow” method, but instead of returning only the first row, it returns the whole table (thats why a list of QVariant list is returned).

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAll(const QString &tableName, const QStringList &columns);

This is an overloaded method. This method allows you to specify which columns are going to be retrieved in the returned list.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAllCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = “=”);

This method is similar to “retrieveAll”, but it returns only the rows that match with the conditional supplied in the parameters.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAllCond(const QString &tableName, const QStringList &columnCondition, const QList<QVariant> &condition, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAllCond(const QString &tableName, const QStringList &columnName, const QString &columnCondition, const QVariant &condition, const QString &operation = “=”);

This is an overloaded method. It allows you to specify which columns will be in the returned list.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    QList< QList<QVariant> > retrieveAllCond(const QString &tableName, const QStringList &columnName, const QStringList &columnCondition, const QList<QVariant> &condition, const QString &operation = “=”);

This is an overloaded method. It allows you to give more than onle column and more than one value to comparison on the conditional test. This method also allows you to specify which columns will be in the returned list.

Returns an empty list either if no result is given or in case of any (syntax or size) error on the given parameters.

    int rowsCount(const QString &tableName);

This method returns the rows count of the given table.

Returns -1 if anything goes wrong.

    int rowsCountCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = “=”);

This method returns the rows count of the specified table returned by the comparison query. Where the values and columns must be provided as second and third parameters respectively.

Returns -1 if anything goes wrong.

    int rowsCountCond(const QString &tableName, const QStringList &columnCondition, const QList<QVariant> &condition, const QString &operation = “=”);

This is a overloaded method. You are able to give more than one column and values to the conditional operation execution.

Returns -1 if anything goes wrong.

    bool clearTable(const QString &tableName);

This method cleans the specified table without removing it.

Returns true on success, or false otherwise.

    bool dropTable(const QString &tableName);

This method removes the specified table.

Returns true on success, or false otherwise.

    QSqlQuery runCustomQuery();

Returns a QSqlQuery object build based on the current database connection. With this object, you are able to execute any SQL query.

    QVariant pixmapToVariant(const QPixmap &pixmap);

This method returns a QVariant object generated from a QPixmap given as parameter. It is useful to store images in the data.

    QPixmap variantToPixmap(const QVariant &variant);

This method returns an image generated from the QVariant  given as parameter. It is useful to recover images from the database.

In case of an invalid input, a null QPixmap is returned.

    static QString getUniqueConnectionName(const QString &partname = “”);

This static method returns a unique QString, which can be used as the database connection name (avoiding conflicts between connections with the same name). The partName parameter is used as prefix.

    bool setConnectionType(DBConnectionType cType);

Changes the current database type in which DBManager is going to connect to.

Returns true if it is possible to assign it, or false otherwise (do note that it is not possible to change the database type while the database connection is open).

    DBConnectionType connectionType();

Returns the current database type.

    bool setConnectionName(const QString &cName);

Defines a new connection name.

Returns true if it is possible to assign it, or false otherwise (do note that it is not possible to change the connection name while the database connection is open).

    QString currentConnectionName();

Returns the current connection name.

    bool setDatabaseData(const DBManager::DBData &dbData);

Defines a new setting to the database tables.

Returns true if it is possible to assign it, or false otherwise (do note that it is not possible to change the settings while the database connection is open).

    const DBManager::DBData databaseData();

Returns the current database connection settings.

    bool setDBPrefix(const QString &prefix);

Defines a new standart prefix to the database tables.

Returns true if it is possible to assign it, or false otherwise (do note that it is not possible to change the prefix while the database connection is open).

    inline QString dbPrefix();

Returns the current prefix of the tables.

    bool openDB();

Starts the connection with the database (according to the connection name). Returns true on success or false otherwise.

Please, refer to http://doc.qt.io/qt-5/qsqldatabase.html#open for the original and complete documentation;

    bool isOpen();

Returns true if the current connection is open. Identical to QSqlDatabase::isOpen();

Please, refer to http://doc.qt.io/qt-5/qsqldatabase.html#isOpen for the original and complete documentation;

    bool isValid();

Returns true if the database contains a valid driver. Identical to QSqlDatabase::isValid();

Please, refer to http://doc.qt.io/qt-5/qsqldatabase.html#isValid for the original and complete documentation;

    void closeDB();

Finishes the current connection to the database.

    QSqlError lastError();

This method returns the cause of the latest error related to the database manipulation, for example, resquest syntax, invalid type or position, etc. Please, refer to QSqlError for major details.

In case of no error happening during the use of the database, a QSqlError(“”, “”, “”) object will be returned;

    bool hasValidSettings();

Indicates if the set values are accordingly with the specified database. This method returns true if the settings are valid (check the table below, it explains the necessary set of data to establish connection with each kind of database), or false otherwise.

MySQL

SQLite

In order to check, you just have to evoke the following method:

Sample program

This project also contains a base program, where it is possible to test the connection and comprehend a little bit better how does the library work.

It is available in the folder “Sample program” at the project’s repository. It can be accessed through: https://github.com/Nintersoft/DBManager/blob/master/sample%20program/. There, you are able to download the source code which is contained in a zip file or rather explore it at GitHub.

Expansions

The DBManager library can be used with any gui application that needs connection to either MySQL or SQLite database (and that uses Qt library).

Currently, it is already part of some of our projects developed by our team, even SmartClass, the project which DBManager was born.

You can also use it in your own applications, and to make your life easier, we have already made the Windows compiled library in our repository (in the following versions: MSVC2017_x86, MSVC2017_x64 and MinGW_x86), containing the necessary headers and the dynamic and static library link files. However, if you are developing either for MAC or Linux, you just have to clone the project’s directory in our official repository, compile and link it to your application (you can follow this official tutorial to get information about the library implementation).

Open Source Code

DBManager is under Nintersoft Open Source Code Licence, therefore it can be used for personal and commercial purposes under the same licence..

 Esta página encontra-se disponível em seu idioma!
Sidebar