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.
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.
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…