Skip to content

Database

Database Interface

Five Databases

The database creation base class of IRdbCore varies based on the database type. We provide five database supports, corresponding as follows:

Name Interface
MySql IRdbMysqlDatabaseInterface
Sqlite IRdbSqliteDatabaseInterface
Postgres IRdbPostgreDatabaseInterface
SqlServer IRdbSqlServerDatabaseInterface
MariaDb IRdbMariaDbDatabaseInterface

If users want to implement their own database type support, the naming convention should follow the pattern of the above interfaces, IRdbXXXXDatabaseInterface. If you want to implement a database named Doggy, then its name should be IRdbDoggyDatabaseInterface.

IRdbXxxDatabaseInterface

Among the various database interfaces above, they are largely similar. We take IRdbSqliteDatabaseInterface as an example. Its interface is as follows:

1
2
3
4
5
6
7
8
9
template<typename T, bool enabled = true>
class IRdbSqliteDatabaseInterface : public IRdbDatabaseInterface<T, IRdbSqliteDialect, enabled>
{
public:
    IRdbSqliteDatabaseInterface() = default;

public:
    virtual QSqlDatabase openDatatbase(const IRdbSource &) override;
};

This base class implements the openDatabase function. This function takes the IRdbSource parameter, which is obtained in the deeper base class's getSource function. This function is always a pure virtual function, and users must override it when implementing the database instance. We will see this function later.

IRdbDatabaseInterface

As seen above, IRdbSqliteDatabaseInterface inherits from IRdbDatabaseInterface. In fact, all database base classes inherit from IRdbDatabaseInterface. Its implementation is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
template<typename T, typename Dialect, bool enabled = true>
class IRdbDatabaseInterface : public IRdbDatabaseWare, public ITaskWareUnit<T, IRdbCatagory>, public ISingletonUnit<T>
{
public:
    IRdbDatabaseInterface();

public:
    const IRdbDialect& getDialect() const;

public:
    virtual QString getClassName() const final;

public:
    virtual double $order() const final;
    virtual void $task() final;
};

When inheriting from IRdbDatabaseInterface, you need to provide three template parameters. The first is the CRTP class name, which is the name of the specific database class. The second parameter Dialect is the implementation of the database dialect, which handles differences between databases. We will discuss this later. The third parameter bool enabled indicates whether the current database connection test is enabled; if it fails, an error will be reported.

The IRdbDatabaseInterface inherits from three classes: the first is IRdbDatabaseWare, which we will discuss later. The second is ITaskWareUnit, which is part of the task registration system. The functions $order() and $task() overridden in the class are inherited from ITaskWareUnit. $order() is used for task sorting, while $task() is used for task execution. The third parameter ISingletonUnit<T> provides a T& instance() function for the type T, allowing the database to be used via the Meyers singleton pattern.

IRdbDatabaseWare

Its implementation is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
class IRdbDatabaseWare
{
public:
    explicit IRdbDatabaseWare(const IRdbDialect&);
    virtual ~IRdbDatabaseWare();

public:
    ISqlQuery createQuery();

public:
    virtual IRdbSource getSource() const = 0;
    virtual QString getClassName() const = 0;
    QStringList getRdbTables() const;
    QStringList getRdbViews() const;
    virtual void dropTable(const IRdbTableInfo& table);
    virtual void dropView(const IRdbViewInfo& view);

protected:
    virtual QSqlDatabase openDatatbase(const IRdbSource&) = 0;

public:
    const IRdbDialect& m_dialect;
    QSqlDatabase m_db;
};

This base class stores a reference to IRdbDialect& m_dialect, which is the concrete implementation of database statements.

Notable is the function ISqlQuery createQuery(), which creates an ISqlQuery object. Users can use this object for database operations. All the CRUD operations performed in the Model are done using ISqlQuery objects.

The class also includes a set of functions for table operations: getRdbTables() retrieves all database table names, getRdbViews() retrieves all database view names, dropTable(const IRdbTableInfo& table) deletes a database table, and dropView(const IRdbViewInfo& view) deletes a database view. This set handles operations on database entities.

The getClassName() function retrieves the name of the current class.

The getSource() function, mentioned earlier, returns an IRdbSource object, which contains information for connecting to the database.

IRdbSource

IRdbSource contains information such as driverName and databaseName, used for connecting to the database. The usage varies for different databases.

IRdbDialect

We can directly look at its implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
class IRdbDialectWare
{
public:
    IRdbDialectWare() = default;
    virtual ~IRdbDialectWare() = default;

public:
    virtual QString databaseType() const = 0;

public:
    virtual QString createTableSql(const IRdbTableInfo& info) const; 
    virtual QString dropTableSql(const IRdbTableInfo& info) const;
    virtual QString dropViewSql(const IRdbViewInfo& info) const;

    QString countSql(const IRdbEntityInfo& info) const;
    QString countSql(const IRdbEntityInfo& info, const IRdbCondition& condition) const;

    virtual void insert(ISqlQuery &query, const IRdbTableInfo &info, void *) const;
    virtual void insert(ISqlQuery& query, const IRdbTableInfo& info, const void*) const;

    void insertAll(ISqlQuery& query, const IRdbTableInfo& info, QVector<const void*>) const;


    QString findOneSql(const IRdbEntityInfo& info, const IRdbCondition& condition) const;
    QString findAllSql(const IRdbEntityInfo& info) const;
    QString findAllSql(const IRdbEntityInfo &info, const IRdbCondition& condition) const;
    QString findColumnSql(const IRdbEntityInfo& info, const QStringList& columns) const;
    QString findColumnSql(const IRdbEntityInfo& info, const QStringList& columns, const IRdbCondition&) const;

    QString existSql(const IRdbEntityInfo&, const IRdbCondition&) const;

    QString updateOne(const IRdbTableInfo& info, const QStringList& columns) const;
    QString updateWhere(const IRdbTableInfo& info, const QVariantMap& map, const IRdbCondition& condition) const;

    QString deleteTableSql(const IRdbEntityInfo& info) const;
    QString deleteTableSql(const IRdbEntityInfo& info, const IRdbCondition& condition) const;
    QString truncateTableSql(const IRdbEntityInfo& info) const;

    virtual QString getSqlType(const IRdbTableInfo& info, int index) const = 0;

public:
    QString conditionToSql(const IRdbCondition&) const;

protected:
    QString toWhereSql(const IRdbCondition&) const;
    QString toOrderBySql(const IRdbCondition&) const;
    QString toGroupBySql(const IRdbCondition&) const;
    QString toHavingSql(const IRdbCondition&) const;
    QString toLimitSql(const IRDatabaseCondition&) const;

    QString fromWhereClause(const IRdbWhereClause&) const;
    QString fromOrderByClause(const IRdbOrderByClause&) const;
    virtual QString fromLimitClause(const IRdbLimitClause&) const;
    QString fromGroupByClause(const IRdbGroupByClause&) const;
    QString fromHavingClause(const IRdbHavingClause&) const;
    virtual QString createSqlCommonKeyClause(const IRdbTableInfo& info, int index) const;

public:
    virtual void bindParameter(QSqlQuery& query, const QString& field, const QVariant& value) const;
    virtual QString quoteName(const QString& name) const;

protected:
    static QString getVividName(const QString&);
};

As seen in the code above, it generates a series of SQL statements. This is because the SQL grammar rules of different databases are largely similar, but some parts differ. For the common parts, we implement them in the base class, and for the differing parts, we use virtual functions for overriding.

Differences Between Different Databases

Sqlite

Its creation method is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// SqliteDb.h
#pragma once

#include "rdb/database/IRdbSqliteDatabaseInterface.h"

class SqliteDb : public IRdbSqliteDatabaseInterface<SqliteDb>
{
public:
    SqliteDb() = default;

public:
    virtual IRdbSource getSource() const final;
};

// SqliteDb.cpp
#include "SqliteDb.h"

IRdbSource SqliteDb::getSource() const
{
    IRdbSource source;
    source.databaseName = "abcde.db";
    source.driverName = "QSQLITE";
    return source;
}

This creates a database file named abcde.db in the program's root directory.

MySql

This database does not have default support on Windows. Users need to manually compile the dynamic link library for support. For instructions on how to compile, please search for related tutorials.

For convenience, the following database configurations are entirely set up in a Docker environment, so details such as passwords are not disclosed.

Its code is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
//MySqlDb.h
#pragma once

#include "rdb/database/IRdbMySqlDatabaseInterface.h"
using namespace IWebCore;

class MySqlDb : public IRdbMysqlDatabaseInterface<MySqlDb>
{
public:
    MySqlDb() = default;

public:
    virtual IRdbSource getSource() const final;
};

// MySqlDb.cpp
#include "MySqlDb.h"

IRdbSource MySqlDb::getSource() const
{
    IRdbSource source;
    source.driverName = "QMYSQL";
    source.databaseName = "TestDb";
    source.host = "127.0.0.1";
    source.user = "root";
    source.password = "xxxx";
    source.port = 3306;
    return source;
}

MariaDb

Same as above, the specific code is listed as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// MariaDb.h
#pragma once

#include "rdb/database/IRdbMariaDbDatabaseInterface.h"

class MariaDb : public IRdbMariaDbDatabaseInterface<MariaDb>
{
public:
    MariaDb() = default;

public:
    virtual IRdbSource getSource() const final;
};

// MariaDb.cpp
#include "MariaDb.h"

IRdbSource MariaDb::getSource() const
{
    IRdbSource source;
    source.driverName = "QMYSQL";
    source.databaseName = "TestDb";
    source.host = "127.0.0.1";
    source.user = "root";
    source.password = "xxxx";
    source.port = 3305;
    return source;
}

SqlServer

SqlServer is configured using ODBC. The related code is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// SqlServerDb.h
#pragma once

#include "rdb/database/IRdbSqlServerDatabaseInterface.h"

class SqlServerDb : public IRdbSqlServerDatabaseInterface<SqlServerDb>
{
public:
    SqlServerDb() = default;

public:
    virtual IRdbSource getSource() const final;
};

//SqlServerDb.cpp
#include "SqlServerDb.h"

IRdbSource SqlServerDb::getSource() const
{
    IRdbSource source;
    source.driverName = "QODBC3";
    source.databaseName = "TestDb";
    source.user = "sa";
    source.password = "xxxxxxxx";
    source.host = "localhost";
    source.port = 1433;

    return source;
}

Postgres

Postgres is a database type natively supported by Qt. The code to use it is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
// PostgresDb.h
#pragma once

#include "rdb/database/IRdbPostgreDatabaseInterface.h"

class PostgreDb : public IRdbPostgreDatabaseInterface<PostgreDb>
{
public:
    PostgreDb() = default;

public:
    virtual IRdbSource getSource() const final;
};

// PostgresDb.cpp
#include "PostgresDb.h"

IRdbSource PostgreDb::getSource() const
{
    IRdbSource source;
    source.driverName = "QPSQL";
    source.databaseName = "TestDb";
    source.user = "postgres";
    source.password = "xxx";
    source.host = "127.0.0.1";
    source.port = 5432;

    return source;
}

Extending Database Types

If users want to extend database types, they need to inherit from the following base classes to create their own types.

Dialect

Users need to inherit from IRdbDialectInterface to create their database's Dialect dialect. When inheriting, users need to distinguish which functions require overriding. If a function marked as virtual needs to be overridden, users can modify it directly in the base class, mark it as virtual, and then inherit it in the subclass. It is recommended to maintain a consistent naming convention, naming the generated Dialect as IRdbXxxDialect.

Database

The second class users need to create is the DatabaseInterface class. Users can refer to the writing style of other DatabaseInterface classes. It is recommended to maintain a consistent naming convention, naming the generated class as IRdbXxxDatabaseInterface.