This issue recommends an open source structured sql generator called SQl-GeneratorPublic.

If you want to write a complex SQL, and a lot of SQL code is similar but not the same. Then it is recommended to use this tool, you do not have to write SQL repeatedly, more conducive to modification, maintenance and understanding. Especially in big data analysis scenarios, there is often a need to write complex SQL, which you can use as a repeat code generator.

Function

• Structured SQL writing logic, writing and reading SQL as if writing an article outline
• The repeated SQL needs to be written only once, and one place can be modified when the SQL changes
• Can pass parameters and debug some SQL
• View the reference tree and replacement process of SQL statements for easy analysis and understanding of SQL

Advantages

• Support online editing JSON and SQL, support code highlighting, syntax verification, one-click formatting, find and replace, code block folding, etc., good experience
• Support one-click SQL
• Support transparent transmission of parameters, such as @a(xx = #{yy}), yy variable can be passed to @a formula
• Supports nested parameter passing (taking subqueries as parameters), such as @a(xx = @b(yy = 1))
• does not limit what the user can write in JSON, so the tool can also be used as a repeat code generator
• Support to view the call tree and replacement details of SQL statements, easy to analyze reference relationships

Implementation

Using the JavaScript that works best with JSON, write a logical JS file that can be applied to both browser and server side.

The function is relatively lightweight, so it is preferred to implement the pure browser side.

The front-end uses Vue3 + Vite + Ant Design Vue development interface, and chooses Monaco Editor to achieve code editing, highlighting, formatting and other functions. Ensure code specification with TypeScript + ESLint.

The SQL generation logic is as follows:

1. 1. JSON string to object
   2. Starting from the entry, replace the params static parameter first to get the current layer analysis
   3. Performs recursive parsing of @xxx syntax. During recursive parsing, static parameters are replaced first, and then call parameters from the outer layer are replaced

.

1. Get the final SQL

The

parser was originally implemented as a regular non-greedy substitution, but could not implement nested calls, such as @a(xx = @b()), which would be recognized as @a(xx = @b(), matching the nearest closing parenthesis. Therefore, the subquery replacement algorithm is optimized for the case of parenthesis nesting, and the nested calls containing parenthesis statements are supported.

Parameter Description

• • Object key: defines the name of the SQL generation rule. main indicates the entry SQL, which is generated from the SQL statement.
  • Object value: Defines specific generation rules. It can be an SQL string or an object.
  • sql: defines a template SQL statement, which can be any string, such as a group of fields, a query condition, a calculation logic, and a complete SQL.
  • params: a static parameter that the parser preferentially replaces with

in the #{variable name} of the current statement

• #{xxx} : defines variables that can be replaced with the current level params first, otherwise passed by the outer layer
• @xxx(yy = 1 ||| zz = #{variable}) : references other SQL, can be passed parameters, parameters can be represented by variables, use ||| (three vertical bars) to separate the parameters.

{
"main": " Required, code is generated from here, using @ rule name () to reference other statements ",
" Rule name ": " can write any SQL statement @ rule name 2() @ dynamic parameter passing (a = to ||| b = star)",
" Rule name 2": {
"sql": " Specify replaceable values with #{parameter name} ",
"params": {
" Parameter name ": " Specifies static parameters in params, which are replaced preferentially "
}
},
< span class = "HLJS - attr" > "dynamic mass participation" : < / span > < span class = "HLJS - string" > "# {a} fishskin # {b}" < / span >
}

Example

Requirement: Calculate the height difference between two students with id = 1 and id = 2

The SQL looks something like this:

select (s1.height - s2.height) as Height difference
from
(select * from student where id = 1) s1,
(select * from student where id = 2)  s2

Obviously, the above SQL secondary table query twice, and except for the query id is different, the query logic is completely the same!

If the logic of querying students is modified, the above two sub-queries must be modified at the same time, which is not conducive to maintenance.

With this tool, you can automatically generate a complete SQL by writing the following JSON:

{
"main":  < span class = "HLJS - string" > "select @ height difference () from (@ students table (id = 1)), s1 (@ students table (id = 2)) s2" < / span >,
" Height difference ": "(s1.height-s2.height) as height difference ",
" student table ": "select * from student where id = #{id}"
}

With a method like function call + parameter passing, we do not need to write SQL repeatedly, and the logic of the entire SQL is clearer! Of course, the above is just an example, the real big data offline analysis scenario, SQL is N times more complex than this!

Complex example

Demand: Use a SQL to query the following table

What is the difficulty in this table?

1. 1. The difference between checking the summary and finding out the fine particle size can not be distinguished by group by union (red)
   2. data in different rows in the classification column are crossed, which can only be distinguished by union

instead of group by.

1. 1. Each column is joined by multiple tables, and different tables can be associated with different categories, so it is necessary to distinguish (gray indicates that it cannot be associated), and complete the missing fields (otherwise, it cannot be union)
   2. The calculation formula for the same column in different rows may be different (blue)
   3. Different columns have different filtering conditions (for example, the last two dark green columns are to check the whole school, and the remaining columns are only to check Grade 1)
   4. To query the same chain, only two complete data can be joined and then misaligned to get

.

Clearly, much of the query logic in this table is repetitive but different.

So, how many base table select will be included in the final SQL? How many times should each base table query be rewritten?

However, this table is only obtained after simplifying the actual demand of the fish skin, the actual demand is several times more complex!

Can you imagine how disgusting manual writing is? !

But with this tool, just write structured JSON like this:

{
"main":  "select (a/b-1) from (@check whole (date = today)) a, (@check whole (date = yesterday)) b",
" Check the whole ": " @Check grade () union @ Check Class 1 () union @ Check Class 2 () where date = #{date}",
" Check grade ": " @check summary _ Gender summary _ Gender group _ union @ Check summary _ Hobby summary (union @ Check summary _ Hobby group () union @ check summary _ Computer category summary () union @Search Summary _ Computer Category Group ()",
< span class = "HLJS - attr" > "check summary _ gender summary" < / span > < span class = "HLJS - string" > "@ check in addition to the computer associated table ()" < / span >,
" Search summary _ Gender grouping ": "@ Search computer association table () group by gender ",
< span class = "HLJS - attr" > "check summary _ hobby summary" < / span > < span class = "HLJS - string" > "@ check in addition to the computer associated table ()" < / span >,
" Search summary _ Hobby groups ": "@ Search computer association table () where hobbies in (xx) group by hobbies ",
< span class = "HLJS - attr" > "check summary _ computer category summary" < / span > < span class = "HLJS - string" > "@ check table in addition to the three even and learning ()" < / span >,
" Check summary _ Computer Category group ": "@ Check group by Computer Category () ",

	" Check Class 1 ": " @check class 1 _ Gender summary () union @check class 1 _ Gender grouping () union @check class 1 _ Hobby summary () union @check class 1 _ Hobby grouping () union @check class 1 _ Computer category summary () union @Search Summary _ Computer Category Group ()",
" Check class 1 _ Gender summary ": "@ Check computer association table () where Class 1 ",
" Check class 1 _ Gender grouping ": "@ Check computer association table () where Class 1 group by gender ",
" Check class 1 _ Hobby summary ": "@ Check computer association table () where Class 1 ",
" Check class 1 _ Hobby Group ": "@ Search computer association table () where 1 class and hobbies in (xx) group by hobbies ",
" Check class 1 _ Computer Category Summary ": "@ Check triple and learning table () where Class 1 ",
" Check class 1 _ Computer Category group ": "@ Check triple and learning table () where Class 1 group by Computer category ",

" Check Class 2 ": "@ Check Class 2 _ Gender summary () union @ Check Class 2 _ Gender grouping () union @ Check Class 2 _ Computer Category Summary () union @ Check Class 2 _ Computer Category grouping ()",
" Check class 2 _ Gender summary ": "@ Check computer association table () where Class 2 ",
" Check class 2 _ Gender grouping ": "@ Check computer association table () where Class 2 group by gender ",
" Check class 2 _ Computer Category Summary ": "@ Check triple and learning table () where Class 2 ",
" Check class 2 _ Computer Category group ": "@ Check triple and learning table () where Class 2 group by Computer category ",

" Look up all associated tables ": " @Check information table () left join (@check three-link table ()) left join (@check learning table ()) left join (@check computer table ()) left join (@Check the whole school information ())",
" Delete computer association table ": " @check information table () left join (@check study table ()) left join (@check whole school information ())",
" Check the triple sum learning table ": " @check information table () left join (@check computer table ()) left join (@check school information ())",
" Look up information table ": "select field from information table where grade = 1",
" Check the triple table ": "select field from the triple table where grade = 1",
" Look up study table ": "select field from study table where grade = 1",
" Check the computer table ": "select field from the computer table where grade = 1",
" Check the whole school information ": "select field from Information table "
}

can automatically generate SQL, you can also view the call relationship, very clear:

—END—

Open Source protocol: Apache2.0

Author: liyupi