Introduction to libdbclient interface

Keywords: Database SQL SQLite Oracle

This is a magical development interface!

It supports access to mysql, sqlite, Dream and oracle databases!

It provides a unified C++ abstract interface externally, hides the internal implementation details, and has a learning cost of 0 for users!

It provides abstract result table objects (result_table) and result_set objects (result_set) to satisfy the usage habits of different users!

Currently, it works only on Linux, does not support cross-platform, and is still under development and maintenance.

Well, did it cause you to lose your curiosity?Let's unveil it:

/*
 * Filename:
 *      db_client.h
 *
 * Effect:
 *      Database Operating Interface (supports domestic Dream database, mysql database, sqlite database and oracle database)
 *
 * Introduction:
 *      1. Encapsulates the DPI interface of the Dream database;
 *      2. C interface of mysql database is encapsulated.
 *      3. Encapsulates the sqlite3 interface of the sqlite database;
 *      4. Encapsulates the OCI (using ocilib) interface of the oracle database;
 *      5. Support automatic reconnection of disconnected lines (mysql and Dream. oracle use the pool provided by ocilib, it is not clear if automatic reconnection of disconnected lines is provided);
 *      6. Provides database connection pooling functionality (sqlite does not provide this functionality, but the interface is consistent with other databases);
 *      7. Provide a unified c++ access interface to the user layer, hiding specific database details;
 *      8. Provides common data type access (basic type operations such as TEXT(CHAR,VARCHAR,NVARCHAR,TEXT), SHORT(2), INT(4), LONG(8), FLOAT, DOUBLE, TIMESTAMP, and BLOB);
 *      9. Provide abstract result table objects (result_table) and result_set objects (result_set) to satisfy different usage habits;
 *
 * Author
 *      shenyi(E-mail: shenyi0106@163.com  QQ: 52851771)
 *
 * Time:
 *     2019/11/25
 *
 * Dependency:
 *     libdmdpi.so(Dream DPI Interface Library)
 *     libmysqlclient.so
 *     libsqlite3.so
 *     libocilib.so(ORACLE, Dependent on OCI Interface Library)
 *
 * Copyright:
 *
 */
#ifndef DB_CLIENT_H
#define DB_CLIENT_H
#include <string>
#include <vector>

#ifndef UNUSED
#define UNUSED(x) (void)(x)
#endif

#ifndef MIN
#define MIN(x,y) (x) > (y) ? (y) : (x)
#endif

#ifndef MAX
#define MAX(x,y) (x) > (y) ? (x) : (y)
#endif

//
// Supported underlying database interfaces
//
enum DB_CLIENT_TYPE
{
    DB_CLIENT_TYPE_DAMO   = 1,   // Dreaming
    DB_CLIENT_TYPE_MYSQL  = 2,   // MYSQL
    DB_CLIENT_TYPE_SQLITE = 3,   // SQLITE3
    DB_CLIENT_TYPE_ORACLE = 4,   // ORACLE(OCI)
};

//
// Supported data types (Currently only the following data types are supported by the access interface)
//
enum DATA_TYPE
{
    DATA_CTYPE_NULL      = -1,
    DATA_CTYPE_STRING    = 0,
    DATA_CTYPE_SSHORT    = 1,
    DATA_CTYPE_USHORT    = 2,
    DATA_CTYPE_SLONG     = 3,
    DATA_CTYPE_ULONG     = 4,
    DATA_CTYPE_FLOAT     = 5,
    DATA_CTYPE_DOUBLE    = 6,
    DATA_CTYPE_BLOB      = 12,
    DATA_CTYPE_SINT      = 13,
    DATA_CTYPE_UINT      = 14,
    DATA_CTYPE_TIMESTAMP = 15,
};

//
// log level
// The higher the number, the higher the level; lower log levels can contain higher log levels, but not vice versa.
// For example:
//   1. DBG_LEVEL_DEBUG is set up so that all logs with system size greater than or equal to DBG_LEVEL_DEBUG can be printed (all logs);
//   2. With DBG_LEVEL_WARN set, the system can only print logs at DBG_LEVEL_WARN and DBG_LEVEL_ERROR levels.
//
enum DBG_LEVEL
{
    DBG_LEVEL_DEBUG  = 1,
    DBG_LEVEL_INFO   = 4,
    DBG_LEVEL_WARN   = 8,
    DBG_LEVEL_ERROR  = 16,
};

//
// Default database connection and execution timeout
//
#define DEFAULT_TIMEOUT_SECOND 2

//
// Maximum number of parameters available in stmt mode
//
#define MAX_BIND_PARAM_COUNT 64

//
// Parameter structure in stmt mode
//
struct bind_param
{
#if SUPPORT_ORACLE
    char           name[64];  // Parameter name (denotes parameter name, ocilib does not support index-bound parameter mode)
    void          *lob;       // Use in internal function, caller ignore this parameter
#endif
    void          *buffer;    // Pointer to buffer
    long           buf_type;  // Parameter type, one of DATA_TYPE
    unsigned long  buf_size;  // Buffer size
    unsigned long  buf_len;   // Length of parameters
};


//
// Result Set Memory Table
// Build a schema from the query results in memory that can access any single metadata randomly like a two-dimensional table
//
class result_table
{
public:
    //
    // Get the number of rows in the Recordset
    //
    virtual int get_row_count() = 0;

    //
    // Get Number of Recordset Columns
    //
    virtual int get_col_count() = 0;


    //
    // Read the string value of a field
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_string_value(int row, int col, std::string &val) = 0;
    virtual bool get_string_value(int row, std::string name, std::string &val) = 0;

    //
    // Read the string value of a field (in the form of a return value)
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual std::string get_string_value(int row, int col) = 0;
    virtual std::string get_string_value(int row, std::string name) = 0;

    //
    // Read the short value of a field (16 bits)
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_short_value(int row, int col, short &val) = 0;
    virtual bool get_short_value(int row, std::string name, short &val) = 0;

    //
    // Read the field's int value (32 bits)
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_int_value(int row, int col, int &val) = 0;
    virtual bool get_int_value(int row, std::string name, int &val) = 0;

    //
    // Read the long value of a field (64 bits)
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_long_value(int row, int col, long &val) = 0;
    virtual bool get_long_value(int row, std::string name, long &val) = 0;

    //
    // Read float values for fields
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_float_value(int row, int col, float &val) = 0;
    virtual bool get_float_value(int row, std::string name, float &val) = 0;

    //
    // Read the double value of a field
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    virtual bool get_double_value(int row, int col, double &val) = 0;
    virtual bool get_double_value(int row, std::string name, double &val) = 0;

    //
    // Read the binary contents of a field
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    // val: Output parameter, pointing to binary buffer
    // ret_len: Output parameter, binary buffer length
    //
    //
    virtual bool get_blob_value(int row, int col, void **val, int *ret_len) = 0;
    virtual bool get_blob_value(int row, std::string name, void **val, int *ret_len) = 0;

    //
    // Date and time when the field was read
    // col: 0-based column index
    // Name: Column name (case insensitive)
    //
    // tv: output parameters, date structure
    //
    virtual bool get_datetime_value(int row, int col, struct tm *tv) = 0;
    virtual bool get_datetime_value(int row, std::string name, struct tm *tv) = 0;
};

//
// Result Set Operational Interface
// This interface allows you to sequentially obtain data for rows and columns
//
class result_set
{
public:
    //
    // Read next line
    //
    virtual bool fetch_row() = 0;

    //
    // Get the number of rows in the Recordset
    //
    // Be careful:
    //     Dream database This interface is not valid! It is valid only when fetch_row reaches the last row.
    //     SQLITE This interface is invalid!
    //
    virtual int  get_row_count() = 0;

    //
    // Get Number of Recordset Columns
    //
    virtual int  get_col_count() = 0;

    //
    // Read field value data (base interface)
    //
    // ctype: Input parameter, type of field read; one of DATA_TYPE;
    // val: Output parameter, read field content will be stored in buffer
    // buf_len: Input parameter, buffer val ue size
    // ret_len: Output parameter, field content size (can be nullptr, meaning you don't care about return length)
    //
    // Remarks:
    //    When val==nullptr and buf_len==0 and ret_len!=nullptr, the length of the field read is returned;
    //
    virtual bool get_value(int col, int ctype, void *val, int buf_len, int *ret_len) = 0;

    //
    // Read the string value of a field
    //
    // val:Output parameter
    //
    virtual bool get_string_value(int col, std::string &val) = 0;

    //
    // Read the string value of a field (as a return value)
    //
    virtual std::string get_string_value(int col) = 0;

    //
    // Read the short value of a field (16 bits)
    //
    // val:Output parameter
    //
    virtual bool get_short_value(int col, short &val) = 0;

    //
    // Read the field's int value (32 bits)
    //
    // val:Output parameter
    //
    virtual bool get_int_value(int col, int &val) = 0;

    //
    // Read the long value of a field (64 bits)
    //
    // val:Output parameter
    //
    virtual bool get_long_value(int col, long &val) = 0;

    //
    // Read float values for fields
    //
    // val:Output parameter
    //
    virtual bool get_float_value(int col, float &val) = 0;

    //
    // Read the double value of a field
    //
    // val:Output parameter
    //
    virtual bool get_double_value(int col, double &val) = 0;

    //
    // Read field binary data
    //
    // val: Output parameter, buffer for binary data
    // buf_len: Input parameter, buffer val ue size
    // ret_len: Output parameter, length of read binary data
    //
    virtual bool get_blob_value(int col, void *val, int buf_len, int *ret_len) = 0;

    //
    // Read Date Time Value of Field
    //
    // val:Output parameter
    //
    virtual bool get_datetime_value(int col, struct tm *val) = 0;
};

//
// Database Connection
// Represents a database connection through which data addition, deletion, and alteration can be performed.
// All execute_* interfaces are thread safe
//
class connection
{
public:
    //
    // Release connection to connection pool
    // Equivalent to connect_pool::close()
    //
    virtual void close() = 0;

    //
    // Query Data
    // Return Result Set Action Object
    //
    // Remarks:
    //    Use the free_result_set interface to release the result set operation object, or it will result in a serious memory leak
    //
    virtual result_set* execute_query(const char *sql, ...) = 0;
    virtual result_set* execute_query(std::string sql) = 0;
    virtual void free_result_set(result_set **result) = 0;

    //
    // Query Data
    // Return Result Set Memory Table
    //
    // Remarks:
    //    1. Use the free_data_table interface to free the result set memory, otherwise serious memory leaks will occur;
    //    2. This interface builds related schema data structures in memory based on the result_set operation object.
    //       Do not use this interface when querying large datasets, as this may result in no available memory.
    //    3. Recommended usage environment: use this interface when querying datasets <=1000
    //
    virtual result_table* execute_query2(const char *sql, ...) = 0;
    virtual result_table* execute_query2(std::string sql) = 0;
    virtual void free_data_table(result_table **table) = 0;

    //
    // Execute INSERT, DELETE, UPDATE statement interface
    //
    virtual bool execute_update(const char *sql, ...) = 0;
    virtual bool execute_update(std::string sql) = 0;
    virtual bool execute_update(std::vector<std::string> sqls) = 0;

    //
    // Bind parameters to perform data update operations through stmt mode
    // Param: parameter set (param[])
    // size: number of params for parameter set
    //
    virtual bool execute_bind(const char *sql, struct bind_param *param, int size) = 0;

    //
    // Is the connection valid
    //
    // Remarks:
    //   This interface is not valid (keep for later use)
    //
    virtual bool is_open() = 0;

    //
    // Transaction Related
    //
    // Remarks:
    //    The following three interfaces are not valid for sqlite!
    //
    virtual bool set_auto_commit(bool enable) = 0;
    virtual bool commit() = 0;
    virtual bool rollback() = 0;
};

//
// Database Connection Pool
// Initializing a certain number of database connections to remain in the system can improve database access efficiency
//
class connection_pool
{
public:
    //
    // Equivalent to function of the same name in connect
    // The goal is to reduce the steps for application-tier requests and releases connections
    //
    virtual result_set* execute_query(const char *sql, ...) = 0;
    virtual result_set* execute_query(std::string sql) = 0;
    virtual void free_result_set(result_set **result) = 0;

    //
    // Equivalent to function of the same name in connect
    // The goal is to reduce the steps for application-tier requests and releases connections
    //
    virtual result_table* execute_query2(const char *sql, ...) = 0;
    virtual result_table* execute_query2(std::string sql) = 0;
    virtual void free_data_table(result_table **table) = 0;

    //
    // Equivalent to function of the same name in connect
    // The goal is to reduce the steps for application-tier requests and releases connections
    //
    virtual bool execute_update(const char *sql, ...) = 0;
    virtual bool execute_update(std::string sql) = 0;
    virtual bool execute_update(std::vector<std::string> sqls) = 0;

    //
    // Equivalent to function of the same name in connect
    // The goal is to reduce the steps for application-tier requests and releases connections
    //
    virtual bool execute_bind(const char *sql, struct bind_param *param, int size) = 0;

    //
    // Connection pool type
    // One of the DB_CLIENT_TYPE enumerations
    //
    virtual int      dbtype() = 0;

    //
    // Get a connection object from the connection pool
    //
    // Return value:
    //   connect object returned successfully; nullptr returned unsuccessfully;
    //
    // Remarks:
    //  When the connection object is used up, the close interface must be used to release the connection object.
    //  Otherwise, the connection pool will be exhausted and no connection will be available.
    //
    virtual connection* open() = 0;

    //
    // Release a connection object to the connection pool
    //
    // conn:Connect Object
    //
    virtual void     close(connection *conn) = 0;
};

//
// Local Export Interface
//
extern "C"
{
    /*
      Functions:
           Set the print level of logs in the interface library
      Parameters:
           level: Log level, one of DBG_LEVEL;
      Return value:
           nothing
      Remarks:
           The default log level for the interface library is DBG_LEVEL_DEBUG;
    */
    void db_client_set_debug(int level);

    /*
      Functions:
           Create Create Database Connection Pool Object
      Parameters:
           dbtype  :  Database type, one of DB_CLIENT_TYPE;
           host    :  Database access address (in IP form);
           port    :  The access port of the database;
           name    :  The name of the database to be created (must be in English);
           user    :  User name to access the database
           passwd  :  Password to access database
           pool_num:  Specify the number of connections in the connection pool
     Return value:
           Connection pool object successfully returned, nullptr failed
     Remarks:
           1. Currently only databases listed in DB_CLIENT_TYPE are supported;
           2. Release the object using db_client_destory_pool when the connection pool is no longer in use;
           3. The number of connections in this connection pool is "one shaping", that is, when a connection pool is created, the specified number of connections is created.
              During use, connections are not automatically added or deleted based on the volume of business requests;
           4. Introduction to parameter use:
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             |         dbtype        |     host    |     port    |     name    |     user    |    passwd   |   pool_num  |
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_DAMO   |     IP Address|Port|Ignore|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_MYSQL  |     IP Address|Port|Database Name|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_SQLITE |    Full File Path|Ignore|Ignore|Ignore|Ignore|Ignore|Ignore|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_ORACLE |    TNS Name|Ignore|Ignore|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
    */
    connection_pool* db_client_create_pool(int dbtype, const char *host, int port, const char *name,
                                            const char *user, const char *passwd, int pool_num);

    /*
     Functions:
           A set of db_client_create_pool-related macro definitions to simplify the steps for creating a database connection pool.
     Remarks:
           See the section "Introduction to parameter use" in the db_client_create_pool function for specific parameter usage
    */
    #define  db_client_create_pool_mysql(host, port, name, user, passwd, pool) db_client_create_pool(DB_CLIENT_TYPE_MYSQL,  host, port, name,    user,    passwd,  pool)
    #define  db_client_create_pool_damo(host, port, user, passwd, pool)        db_client_create_pool(DB_CLIENT_TYPE_DAMO,   host, port, nullptr, user,    passwd,  pool)
    #define  db_client_create_pool_sqlite(path)                                db_client_create_pool(DB_CLIENT_TYPE_SQLITE, path, 0,    nullptr, nullptr, nullptr, 0   )
    #define  db_client_create_pool_oracle(host, user, passwd, pool)            db_client_create_pool(DB_CLIENT_TYPE_ORACLE, host, 0,    nullptr, user,    passwd,  pool)

    /*
     Functions:
           Release database connection pool objects
     Parameters:
            pool  :  Database Connection Pool Object
     Return value:
            Return 0 (ignore return value)

     Remarks:
            Refer to the db_client_create_pool interface description.
    */
    int db_client_destory_pool(connection_pool *pool);
}

#endif // DB_CLIENT_H

It exports three important C interfaces, which are the entrance to this interface library and the soul of it.Here are three important interfaces:

/*
  Functions:
       Set the print level of logs in the interface library
  Parameters:
       level: Log level, one of DBG_LEVEL;
  Return value:
       nothing
  Remarks:
       The default log level for the interface library is DBG_LEVEL_DEBUG;
*/
void db_client_set_debug(int level);

This interface sets the log level that can be output in the development library.(

The libdbclient development library adds a lot of log debugging information, which is controlled by different log levels. You can set the log level you need to output through this interface to control the development interface to output the debugging information you need.The default log level for the development interface is DBG_LEVEL_DEBUG, which means that it outputs all the logs.

/*
      Functions:
           Create Create Database Connection Pool Object
      Parameters:
           dbtype  :  Database type, one of DB_CLIENT_TYPE;
           host    :  Database access address (in IP form);
           port    :  The access port of the database;
           name    :  The name of the database to be created (must be in English);
           user    :  User name to access the database
           passwd  :  Password to access database
           pool_num:  Specify the number of connections in the connection pool
     Return value:
           Connection pool object successfully returned, nullptr failed
     Remarks:
           1. Currently only databases listed in DB_CLIENT_TYPE are supported;
           2. Release the object using db_client_destory_pool when the connection pool is no longer in use;
           3. The number of connections in this connection pool is "one shaping", that is, when a connection pool is created, the specified number of connections is created.
              During use, connections are not automatically added or deleted based on the volume of business requests;
           4. Introduction to parameter use:
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             |         dbtype        |     host    |     port    |     name    |     user    |    passwd   |   pool_num  |
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_DAMO   |     IP Address|Port|Ignore|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_MYSQL  |     IP Address|Port|Database Name|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_SQLITE |    Full File Path|Ignore|Ignore|Ignore|Ignore|Ignore|Ignore|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
             | DB_CLIENT_TYPE_ORACLE |    TNS Name|Ignore|Ignore|User Name|Password|Maximum Connections|
             +-----------------------+-------------+-------------+-------------+-------------+-------------+-------------+
*/
connection_pool* db_client_create_pool(int dbtype, const char *host, int port, const char *name,
                                            const char *user, const char *passwd, int pool_num);

The db_client_create_pool interface is the most important of the three interfaces, and all database access operations must begin with it.

Specific parameter descriptions and usage descriptions, which have been written in the interface file, are not repeated here.It is important to note that the parameters of this interface are not all useful because different database accesses require different parameters to be compatible with different database accesses. The parameters of this interface need to be treated differently according to the dbtype parameters. Refer to the instructions to match different parameters.

Additionally, it is important to note that connection pooling is a feature.The connection pool implemented by this development interface is a very simple connection pool. It does not provide additional capabilities to dynamically close and release connections, nor does it dynamically manage the connection pool based on changes in business volume.How many connections have you initiated, and it will always help you manage them without thinking about more things on your behalf.

You may have seen that for sqlite types, the parameter pool_num is 0 (not just pool_num, but other parameters are ignored), and it does not need this parameter because sqlite is a local file and it does not need a connection pool.

/*
     Functions:
           Release database connection pool objects
     Parameters:
            pool  :  Database Connection Pool Object
     Return value:
            Return 0 (ignore return value)

     Remarks:
            Refer to the db_client_create_pool interface description.
*/
int db_client_destory_pool(connection_pool *pool);

This last interface doesn't need to be covered much, but is released when assigned. This is the motto for developing C/C++. If you don't know this motto, you must not be developing C/C++.

        

The export function is described here. It's very simple. Is it simple to use the three interfaces, 0 learning cost?

For other C++ Abstract interfaces, you can directly look at the interface file, which has detailed instructions, and the name of the function can give you a clear idea of what it does, which is not described here.

Download link: Click on me

Seventeen original articles were published, 12 were praised, and 60,000 visits were received.
Private letter follow

Posted by khovorka on Tue, 03 Mar 2020 19:51:13 -0800