ZORM    中文介绍

Introduce

The basic models of ORM use to be separated from the database. Almost all of them build models at the level of programming language, and let the program deal with all things of the database. Although it is separated from the specific operation of the database, we have to establish various models and write the relationship between tables etc. This is very unfriendly to ordinary developers. My idea is to design tables use tools of relational databases, in our project, json objects can be automatically mapped into standard SQL. As long as we understand the standard SQL language, we can complete the database query operation. Furthermore, We can handled the relationship between tables through views or stored procedures. So our appliction can process all things only using Zorm and Json.

Related items

This project relies on my other project Zjson, which provides a simple, convenient and efficient Json library. The library is easy to use, a single file library, you only need to download and import the project. Please move to gitee-Zjson or github-Zjson.

Design ideas

ZORM data transmission using json, so that data style can be unified from the front to the end. This project aims to be used not only in C++, but also as a dynamic link library used by node.js etc. So we hope to operate json concisely and conveniently like javascript. Therefore, the zjson library was established before this. The general operation interface of database is designed separating from the databases. This interface provides CURD standard api, as well as batch insert and transaction operations, which can basically cover more than 90% of normal database operations. The basic goal of the project is to support Sqlite 3, MySQL, Postges and dm8. Can running on Windows, Linux, or MacOS.

Project characteristics

This series of projects are developed in the form of a single header file. It is simple to use. If you need something, just download it to your project, include it into your codes, and use it directly.

Project progress

Now all functions of using sqlit3, mysql and postgres have been implemented. The technologies I used is sqlit3 - sqllit3.h（c api）；mysql - c api （MySQL Connector C 6.1）；dm8 - dpi；postgres - c api(pgsql14). The pqxx branch implements the encapsulation of libpqxx 7.7.4. It's runs normally on Linux and Macos, but there are problems running on Windows, which need to be solved.

task list：

• Sqlite3
  • linux
  • windows
  • macos
• Mysql
  • linux
  • windows
  • macos
• Postgre
  • linux
  • windows
  • macos
• Dm8
  • linux
  • windows
  • macos

Database interface

The interface was designed to separate operations from databases.

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

Example of DbBase

Global query switch variable:

• DbLogClose : show sql or not
• parameterized : query using parameterized or not

Sqlite3:

    Json options;
    options.add("connString", "./db.db");    //where database locate
    options.add("DbLogClose", false);        //show sql
    options.add("parameterized", false);     //no parameterized
    DbBase* db = new DbBase("sqlite3", options);

Mysql:

    Json options;
    options.add("db_host", "192.168.6.6");   //mysql service IP
    options.add("db_port", 3306);            //port
    options.add("db_name", "dbtest");        //database's name
    options.add("db_user", "root");          //username
    options.add("db_pass", "123456");        //password
    options.add("db_char", "utf8mb4");       //Connection character setting[optional]
    options.add("db_conn", 5);               //pool setting[optional]，default is 2
    options.add("DbLogClose", true);         //not show sql
    options.add("parameterized", true);      //use parameterized
    DbBase* db = new DbBase("mysql", options);

Postgres:

    Json options;
    options.add("db_host", "192.168.6.6");
    options.add("db_port", 5432);
    options.add("db_name", "dbtest");
    options.add("db_user", "root");
    options.add("db_pass", "123456");
    options.add("db_conn", 5);
    options.add("DbLogClose", false);
    options.add("parameterized", true);
    DbBase* db = new DbBase("postgres", options);

Design of intelligent query use Json

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

• page, size, sort   //paging and set query order example：

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

• fuzzy   //Fuzzy query switch, if not provided, it is exact matching. Provides it or not will switch between exact matching and fuzzy matching.

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

• ins, lks, ors   //Three most important query methods. How to find the common points among them is the key to reduce redundant codes.

  • ins   //single field, multiple values：

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

  • ors   //exact matching; multiple fields, multiple values：

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

  • lks   //fuzzy matching; multiple fields, multiple values：

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

• count, sum

  Two statistics function.

  • count   //count, line statistics：

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

  • sum   //sum, columns statistics：

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

• group   ：

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

Unequal operator query support

The supported operators are : >, >=, <, <=, <>, = . Comma is the separator. One field supports one or two operations.Special features: using "=" can enable a field to skip the fuzzy matching. So fuzzy matching and exact matching can appear in one query at the same time.

• one field, one operation：

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

• two field, two operation：

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

• use "=" skip fuzzy matching：

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

Details in unit test, thanks!

Unit test

Detailed description, please move to tests catalogue.

Example of test case running results

Project site

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

run guidance

The project is built in vs2022, gcc12.12.0(at lest gcc8.5.0), clang12.0 success。

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 1：on linux need mysql dev lib and create a db named dbtest first. the command of ubuntu： apt install libmysqlclient-dev
• note 2：on linux need libpq dev lib (gcc at least 8). the command of ubuntu： apt-get install libpq-dev
• note 3：on macos need postgresql@14.
  the command is ： brew install postgresql@14
• note 4：On windows, if yout want use branch pqxx, need compile libpqxx7.7.4, as follows： cmake -A x64 -DBUILD_SHARED_LIBS=on -DSKIP_BUILD_TEST=on -DPostgreSQL_ROOT=/d/softs/pgsql .. cmake --build . --config Release cmake --install . --prefix /d/softs/libpqxx
• note 5：on windows, postgres10 is the last version which support win32, So I only support the x64 version using pg14。
• note 6: About pqxx branch, on windows, postgres can only link libpqxx7.7.4's dll using debug version, and run with a Expression:__acrt_first_block==header, I'm try to solve it ...

Associated projects

gitee-Zjson github-Zjson