Design and Implementation of Universal ORM

introduce

Our general ORM, the basic mode is to be separated from the database, almost all models are established at the programming language level, and the program is used to deal with the database. Although it is out of the specific operation of the database, we need to establish various model documents, use code to write the relationship between tables, etc., so that beginners are like a cloud. My idea is to use the advantages of the perfect design tools of relational databases to realize data design to provide structural information, so that json objects can be automatically mapped into standard SQL query statements. As long as we understand the standard SQL language, we can complete database query operations.

dependencies

This project relies on my other project, Zjson, which provides a concise, convenient and efficient Json library. The library is easy to use and is a single-file library, just need to download and import the project. For specific information, please go to gitee-Zjson or github-Zjson .

Design ideas

ZORM data transfer is implemented by json, so that the data standard can be harmoniously unified from the front end to the end. The goal of this project is not only to be used in C++, but also to be used in conjunction with node.js as a dynamic link library, so I hope to be able to operate json as simple and convenient as javascript. Therefore, the zjson library was established first as the first project of this project. The general operation interface of the database is designed to realize the separation from the underlying database. This interface provides CURD standard access, as well as batch insert and transaction operations, which can basically satisfy more than 90% of the usual database operations. The basic goal of the project is to support Sqlite3, Mysql, Postges three relational databases, and also support windows, linux and macOS.

project progress

Now all the functions of sqlit3 and mysql have been implemented, and postgres has also made technical preparations.
The technical implementation method I chose is basically the most efficient way at the bottom. sqlit3 - sqllit3.h (official standard c interface); mysql - c api (MySQL Connector C 6.1); postgres - pqxx.

task list:

• [x] Sqlite3 implementation

  • [x] linux
  • [x] windows
  • [x] macos

• [x] Mysql implementation

  • [x] linux
  • [x] windows
  • [x] macos

• [ ] Pstgre implementation

  • [ ] linux
  • [ ] windows
  • [ ] macos

Database common interface

The application class directly operates this common interface to realize the separation from the underlying database. This interface provides CURD standard access, as well as batch insert and transaction operations, which can basically satisfy more than 90% of the usual database operations.

 class ZORM_API Idb
  {
  public:
      virtual Json select(string tablename, Json& params, vector<string> fields = vector<string>(), Json values = Json(JsonType::Array)) = 0;
      virtual Json create(string tablename, Json& params) = 0;
      virtual Json update(string tablename, Json& params) = 0;
      virtual Json remove(string tablename, Json& params) = 0;
      virtual Json querySql(string sql, Json params = Json(), Json values = Json(JsonType::Array), vector<string> fields = vector<string>()) = 0;
      virtual Json execSql(string sql, Json params = Json(), Json values = Json(JsonType::Array)) = 0;
      virtual Json insertBatch(string tablename, Json& elements, string constraint = "id") = 0;
      virtual Json transGo(Json& sqls, bool isAsync = false) = 0;
  };

instance construction

Global query switch variable:

• DbLogClose : sql query statement display switch
• parameterized : whether to use parameterized queries

Sqlite3:

 Json options;
    options.addSubitem("connString", "./db.db");    //数据库位置
    options.addSubitem("DbLogClose", false);        //显示查询语句
    options.addSubitem("parameterized", false);     //不使用参数化查询
    DbBase* db = new DbBase("sqlite3", options);

Mysql:

 Json options;
    options.addSubitem("db_host", "192.168.6.6");   //mysql服务IP
    options.addSubitem("db_port", 3306);            //端口
    options.addSubitem("db_name", "dbtest");        //数据库名称
    options.addSubitem("db_user", "root");          //登记用户名
    options.addSubitem("db_pass", "123456");        //密码
    options.addSubitem("db_char", "utf8mb4");       //连接字符设定[可选]
    options.addSubitem("db_conn", 5);               //连接池配置[可选]，默认为2
    options.addSubitem("DbLogClose", true);         //不显示查询语句
    options.addSubitem("parameterized", true);      //使用参数化查询
    DbBase* db = new DbBase("mysql", options);

Intelligent query design

Query reserved words: page, size, sort, fuzzy, lks, ins, ors, count, sum, group

• page, size, sort, and paging sorting are better implemented in sqlit3 and mysql. It is very convenient for limit to paginate. Sorting only needs to splicing parameters directly into order by.
  Query example:

   Json p;
  p.addSubitem("page", 1);
  p.addSubitem("size", 10);
  p.addSubitem("size", "sort desc");
  (new DbBase(...))->select("users", p);
  
  生成sql：   SELECT * FROM users  ORDER BY age desc LIMIT 0,10

• fuzzy, fuzzy query switching parameter, if not provided, it provides the switching between exact matching and fuzzy matching of field query for exact matching.

   Json p;
  p.addSubitem("username", "john");
  p.addSubitem("password", "123");
  p.addSubitem("fuzzy", 1);
  (new DbBase(...))->select("users", p);
   
  生成sql：   SELECT * FROM users  WHERE username like '%john%'  and password like '%123%'

• ins, lks, ors
  These are the three most important query methods. How to find the common ground between them and reduce redundant code is the key.

  • ins, database form field in query, one field has multiple values, for example:
    Query example:

     Json p;
    p.addSubitem("ins", "age,11,22,36");
    (new DbBase(...))->select("users", p);
    
    生成sql：   SELECT * FROM users  WHERE age in ( 11,22,26 )

  • ors, database table multi-field precise query, or connection, multiple fields to multiple values, for example:
    Query example:

     Json p;
    p.addSubitem("ors", "age,11,age,36");
    (new DbBase(...))->select("users", p);
    
    生成sql：   SELECT * FROM users  WHERE  ( age = 11  or age = 26 )

  • lks, database table multi-field fuzzy query, or connection, multiple fields to multiple values, for example:
    Query example:

     Json p;
    p.addSubitem("lks", "username,john,password,123");
    (new DbBase(...))->select("users", p);
    
    生成sql：   SELECT * FROM users  WHERE  ( username like '%john%'  or password like '%123%'  )

• count, sum
  The summation of these two statistics is handled in a similar way. It is generally used in conjunction with group and fields when querying.

  • count, database query function count, row statistics, for example:
    Query example:

     Json p;
    p.addSubitem("count", "1,total");
    (new DbBase(...))->select("users", p);
    
    生成sql：   SELECT *,count(1) as total  FROM users

  • sum, database query function sum, sum of fields, for example:
    Query example:

     Json p;
    p.addSubitem("sum", "age,ageSum");
    (new DbBase(...))->select("users", p);
    
    生成sql：   SELECT username,sum(age) as ageSum  FROM users

• group, database grouping function group, for example:
  Query example:

   Json p;
  p.addSubitem("group", "age");
  (new DbBase(...))->select("users", p);
  
  生成sql：   SELECT * FROM users  GROUP BY age

Inequality operator query support

The supported inequality operators are: >, >=, <, <=, <>, =; the comma character is the separator, and one field supports one or two operations.
Special: Use "=" to make a field skip the search effect, so that fuzzy matching and exact matching appear in one query statement at the same time

• One field and one operation, example:
  Query example:

   Json p;
  p.addSubitem("age", ">,10");
  (new DbBase(...))->select("users", p);
  
  生成sql：   SELECT * FROM users  WHERE age> 10

• One field and two operations, example:
  Query example:

   Json p;
  p.addSubitem("age", ">=,10,<=,33");
  (new DbBase(...))->select("users", p);
  
  生成sql：   SELECT * FROM users  WHERE age>= 10 and age<= 33

• Use "=" to remove the fuzzy effect of the field, example:
  Query example:

   Json p;
  p.addSubitem("age", "=,18");
  p.addSubitem("username", "john");
  p.addSubitem("fuzzy", "1");
  (new DbBase(...))->select("users", p);
  
  生成sql：   SELECT * FROM users  WHERE age= 18  and username like '%john%'

  For specific usage, please refer to uint test.

unit test

For a fully functional unit test case, see the test case in the tests directory.

Test case running result sample

project address

 https://gitee.com/zhoutk/zorm
或
https://github.com/zhoutk/zorm

run method

The project compiles and runs normally under vs2019, gcc7.5, and clang12.0.

 git clone https://github.com/zhoutk/zorm
cd zorm
cmake -Bbuild .

---windows
cd build && cmake --build .

---linux & macos
cd build && make

run zorm or ctest

Note that under linux, you need to install the mysql development library first, and manually create the database dbtest first.
The command under ubuntu is: apt install libmysqlclient-dev

Related items

There will be a series of projects released, related to network services, so stay tuned...

gitee-Zjson
github-Zjson