introduce

The basic models of our general ORM are all intended to be separated from the database. Almost all models are established at the programming language level, and the program deals with the database. Although it is separated from the specific operations of the database, we need to establish various model documents, use code to write the relationships between tables, and other operations, which makes beginners feel confused for a while. My idea is to use the advantages of the complete design tools of relational databases to implement 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 another project of mine, Zjson, which provides a simple, convenient and efficient Json library. The library is easy to use and is a single-file library that only needs to be downloaded and introduced into the project. For specific information, please visit gitee-Zjson or github-Zjson .

Design ideas

ZORM data transmission is implemented using json, so that data standards can be harmonious and unified from the front end to the back end. The goal of this project is not only to be used in C++, but also to be used as a dynamic link library in conjunction with node.js. Therefore, I hope to be able to operate json as simply and conveniently as javascript. Therefore, the zjson library was established first as the precursor to this project. A common database operation interface is designed to achieve separation from the underlying implementation database. This interface provides CURD standard access, as well as batch insertion and transaction operations, which can basically meet more than 90% of usual database operations. The basic goal of the project is to support three relational databases: Sqlite3, Mysql, and Postges, as well as windows, linux, and macOS.

project progress

Now all the functions of sqlit3 and mysql have been implemented, and technical preparations have been made for postgres.
The technical implementation method I chose is basically the lowest efficient method. 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
• [x] Pstgre implementation
  • [x] linux
  • [ ] windows
  • [x]macos

Database common interface

The application class directly operates this common interface to achieve separation from the underlying implementation database. This interface provides CURD standard access, as well as batch insertion and transaction operations, which can basically meet more than 90% of 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 query Sql( string  sql , Json params = Json() , Json values ​​= Json(JsonType::Array ) , vector< string > fields = vector< string > () ) = 0 ;
       virtual Json exec Sql( string  sql , Json params = Json() , Json values ​​= Json(JsonType::Array) ) = 0 ;
       virtual Json insert Batch( string  tablename , Json& elements , string  constraint = "id" ) = 0 ;
       virtual Json trans Go(Json& sqls , bool  isAsync = false ) = 0 ;
  };

Instance construction

Global query switch variables:

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

SQLite3:

    Json options ;
     options .addSubitem( "connString" , "./db.db" );     //Database location 
    options .addSubitem( "DbLogClose" , false );         //Display query statement 
    options .addSubitem( "parameterized" , false ) ;      //Do not use parameterized query 
    DbBase* db = new DbBase( "sqlite3" , options );

Mysql:

    Json options;
    options.add Subitem( "db_host" , "192.168.6.6" ) ;    //mysql service IP 
    options.add Subitem( "db_port" , 3306) ;             //Port 
    options.add Subitem( "db_name" , "dbtest" ) ;         //Database name 
    options.add Subitem( "db_user" , "root" ) ;           //Register user name 
    options.add Subitem( "db_pass" , "123456" ) ;         //Password 
    options.add Subitem( "db_char" , " utf8mb4" ) ;        //Connection character setting [optional] 
    options.add Subitem( "db_conn" , 5) ;                //Connection pool configuration [optional], default is 2 
    options.add Subitem( "DbLogClose" , true ) ;          //Do not display query statements 
    options.add Subitem( "parameterized" , true ) ;       //Use parameterized query 
    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, paging sorting
  is easier to implement in sqlit3 and mysql. It is very convenient to use limit for paging. For sorting, you only need to directly splice the parameters 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); Generate sql : SELECT * FROM users ORDER BY age desc LIMIT 0 , 10
• fuzzy, fuzzy query switching parameter, if not provided, it will be exact matching
  . Provides switching between exact matching and fuzzy matching of field query.Json p; p.addSubitem(“username”, “john”); p.addSubitem(“password”, “123”); p.addSubitem(“fuzzy”, 1 ); ( new DbBase(…)) – > select (“users”, p); Generate sql : SELECT * FROM users WHERE username like ‘%john%’ and password like ‘%123%’
• ins, lks, ors
  are the three most important query methods. How to find the common points between them and reduce redundant code is the key.
  • ins, database form field in query, one field for multiple values, for example:
    Query example:Json p; p.addSubitem(“ins”, “age,11,22,36”); ( new DbBase(…)) – > select (“users”, p); Generate sql : SELECT * FROM users WHERE age in ( 11 , 22 , 26 )
  • ors, precise query of multiple fields in the database table, 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); Generate sql : SELECT * FROM users WHERE (age = 11 or age = 26 )
  • lks, multi-field fuzzy query of database table, 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); Generate sql : SELECT * FROM users WHERE ( username like ‘%john%’ or password like ‘%123%’ )
• The two statistical summations of count and sum
  are processed similarly. They are generally used in conjunction with group and fields when querying.
  • count, database query function count, row statistics, example:
    Query example:Json p; p.addSubitem( “count” , “1,total” ); ( new DbBase(…)) -> select( “users” , p); Generate sql: SELECT *,count( 1 ) as total FROM users
  • sum, database query function sum, field summation, example:
    Query example:Json p; p.addSubitem( “sum” , “age,ageSum” ); ( new DbBase(…)) -> select( “users” , p); Generate sql: SELECT username,sum(age) as ageSum FROM users
• group, database grouping function group, example:
  query example:Json p; p.addSubitem( “group” , “age” ); ( new DbBase( … ))-> select ( “users” , p); Generate sql: SELECT * FROM users GROUP BY age

Unequal operator query support

The supported inequality operators are: >, >=, <, <=, <>, =; the comma character is the delimiter, and one field supports one or two operations.
Special feature: Using “=” can make a certain field skip the search impact, allowing fuzzy matching and exact matching to appear in one query statement at the same time.

• One operation per field, example:
  Query example:Json p; p.addSubitem(“age”, “>,10”); ( new DbBase(…)) – > select (“users”, p); Generate 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); Generate sql : SELECT * FROM users WHERE age >= 10 and age <= 33
• Use “=” to remove the fuzzy influence of fields, example:
  Query example:Json p; p.addSubitem(“age”, “=,18”); p.addSubitem(“username”, “john”); p.addSubitem(“fuzzy”, “1”); ( new DbBase(…)) – > select (“users”, p); Generate sql : SELECT * FROM users WHERE age = 18 and username like ‘%john%’For specific usage, please refer to uint test.

unit test

For complete functional unit test cases, please see the test cases in the tests directory.

Test case execution result example

project address

https: //gi tee.com /zhoutk/ zorm
or
https: //gi thub.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 projects

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

gitee-Zjson
github-Zjson