Re operate the old c + + business and get used to the usual database operation mode, so take the time to encapsulate a C + + version of JSON orm. Now it supports sqlit3 and mysql, and postgres is ready.

Design ideas

Our general ORM, the basic pattern is to want to break away from the database, almost all in the programming language level model, by the program to deal with the database. Although it is separated from the specific operation of the database, we need to establish various model documents, use code to write the relationship between tables and other operations, so that beginners fall into the clouds for a while. My idea is to realize data design to provide structural information by using the advantages of perfect design tools of relational database, and make json objects automatically map into standard SQL query statements. As long as we understand the standard SQL language, we can complete the database query operation.

Technology selection

json Library

JSON-ORM data transmission is realized by json, which makes the data standard harmonious from the front end to the end. I chose rapidjson, but in order to implement efficiency, rapidjson directly operates memory, and uses std::move a lot. There are many restrictions when using it, and there will be memory access conflicts accidentally. So I encapsulate it and provide an easy to operate Rjson proxy class.

Database universal interface

The application class directly operates the general interface to separate from the underlying database. This interface provides the standard access of CURD, as well as batch insertion and transaction operation, which can basically meet more than 90% of the usual database operations.

  class Idb
  {
  public:
    virtual Rjson select(string tablename, Rjson& params, vector<string> fields = vector<string>(), int queryType = 1) = 0;
    virtual Rjson create(string tablename, Rjson& params) = 0;
    virtual Rjson update(string tablename, Rjson& params) = 0;
    virtual Rjson remove(string tablename, Rjson& params) = 0;
    virtual Rjson querySql(string sql, Rjson& params = Rjson(), vector<string> filelds = vector<string>()) = 0;
    virtual Rjson execSql(string sql) = 0;
    virtual Rjson insertBatch(string tablename, vector<Rjson> elements) = 0;
    virtual Rjson transGo(vector<string> sqls, bool isAsync = false) = 0;
  };

Implementation of access to underlying database

Now all the functions of sqlit3 and mysql have been implemented. postgres and oracle have also made technical preparations. They should be implemented in the near future (depending on whether there is time). mssql will not be written in a short time unless there is special demand.
The technology implementation mode I chose is basically the most low-level and efficient way. sqlit3 - sqllit3.h (official standard c interface); mysql - c api (MySQL Connector C 6.1); postgres - pqxx; oracle - occi; mssql -?

Design of intelligent inquiry mode

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

• page, size, sort
  In sqlit3 and mysql, this is a good implementation. It is very convenient to use limit to page. Sorting only needs to splice parameters directly to order by.
  Query example:

  Rjson p;
  p.AddValueInt("page", 1);
  p.AddValueInt("size", 10);
  p.AddValueString("size", "sort desc");
  (new BaseDb(...))->select("users", p);
  
  //Generate sql: select * from users order by age desc limit 0,10

• Fuzzy, switch parameter of fuzzy query, accurate matching when not provided
  It provides the switch between exact match and fuzzy match of field query.

  Rjson p;
  p.AddValueString("username", "john");
  p.AddValueString("password", "123");
  p.AddValueString("fuzzy", "1");
  (new BaseDb(...))->select("users", p);
  

• ins, lks, ors
  This is the most important three query methods, how to find out the common ground between them, reducing redundant code is the key.

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

    Query example:

    Rjson p;
    p.AddValueString("ins", "age,11,22,36");
    (new BaseDb(...))->select("users", p);
    
    //Generate sql: select * from users where age in (11,22,26)

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

    Query example:

    Rjson p;
    p.AddValueString("ors", "age,11,age,36");
    (new BaseDb(...))->select("users", p);
    
    //Generate sql: select * from users where (age = 11 or age = 26)

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

    Query example:

    Rjson p;
    p.AddValueString("lks", "username,john,password,123");
    (new BaseDb(...))->select("users", p);
    
    //Generate sql: select * from users where (username like '% John%' or password like '% 123%')

• count, sum
  The two statistical summation and processing methods are similar, and the query is generally used with group and fields.

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

    Query example:

    Rjson p;
    p.AddValueString("count", "1,total");
    (new BaseDb(...))->select("users", p);
    
    //Generate sql: select *, count (1) as total from users

  • Sum, database query function sum, field sum, for example:

    Query example:

    Rjson p;
    p.AddValueString("sum", "age,ageSum");
    (new BaseDb(...))->select("users", p);
    
    //Generate sql: select username, sum (age) as agesum from users

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

Rjson p;
p.AddValueString("group", "age");
(new BaseDb(...))->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 separator, and a field supports one or two operations.
Special: use "=" to make a field skip the influence of search and make fuzzy matching and exact matching appear in a query statement at the same time

• One field one operation, example:
  Query example:

Rjson p;
p.AddValueString("age", ">,10");
(new BaseDb(...))->select("users", p);

//Generate sql: select * from users where age > 10

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

Rjson p;
p.AddValueString("age", ">=,10,<=,33");
(new BaseDb(...))->select("users", p);

//Generate sql: select * from users where age > = 10 and age < = 33

• Use "=" to remove the search impact of the field, for example:
  Query example:

Rjson p;
p.AddValueString("age", "=,18");
p.AddValueString("username", "john");
p.AddValueString("fuzzy", "1");
(new BaseDb(...))->select("users", p);

//Generate sql: select * from users where age = 18 and username like '% John%'

Series article planning

• Design of general interface for c + + operational relational database (JSON ORM version c + +)
• Design and implementation of rjson -- rapid JSON agent
• Implementation and analysis of SQL it 3 database operation
• Implementation and analysis of mysql database operation
• Implementation and analysis of postgres database operation
• Implementation and analysis of oracle database operation
• Implementation and analysis of mssql database operation
• Summary (if required)

Project address

https://github.com/zhoutk/Jorm

feel

I haven't written C + + for many years, and I have recovered for a while, and I understand the new C + + standard. I feel that the world is changing so fast. Looking back over the years, I chose to learn more avant-garde technology, mainly doing node.js (Typescript), occasionally write python, go. I am used to dynamic language and functional programming. Suddenly return to C + +, found a lot of common ideas, implementation is really troublesome. This is people's choice. If there is a gain, there is a loss. Back then, I was crazy about C and C + +, so they didn't limit my choice of programming paradigm. In recent years, with the development of hardware technology, the thinking of software industry has also made rapid progress. In general, we no longer worry about the memory and algorithm efficiency. We pay more attention to the development efficiency and the humanization of program code. Back to the embrace of C + +, looking at my half wall C + + bookcase, familiar with the new C + + standard, I chose the main direction, template