contexts
hi everyone, this is Triad, in the past years I have thought about writing about these tools as follows
- write a business system by intercepting all the sql, and then according to these sql automatically analyze the table with the table, fields and fields are related to how the tool , i.e., sql bloodline analysis tools
- Thinking of rewriting sql dynamically, like adding a condition to where dynamically.
- Write a sql formatting tool
- Write a middleware like mycat to split libraries and tables.
- Write a sql firewall to prevent a where 1=1 followed by no other conditions leading to a query of the full table
- Write a tool to translate sql between databases, for example, to automatically translate sql from sqlserver to oracle.
But without exception, all failed, because to realize these requirements above, all need a core class library, i.e., sql parsing engine, unfortunately, I did not find the right one, which is the track I was looking for in the first place
- I found it.tsql-parserBut he only supports sql server, so he can only pass.
- And then I found it again.SqlParser-cs,
His syntax tree parses out like this.
((), )
// Elided for readability
{
"Query": {
"Body": {
"Select": {
"Projection": [
{
"Expression": {
"Ident": {
"Value": "a",
"QuoteStyle": null
}
}
}
...
Uh, how can I say, the grammar tree is a bit too ugly, and at the same time very difficult to understand, it's not at all like what I imagined, so I also had to pass.
-
Next I found some other libraries that parse sql based on antlr, such asSQLParser,Since the code is automatically generated by antlr, it is more difficult to perform manual optimization, so it is still pass.
-
And finally I found another onesqlparser for gspBut it's paid for, and it's massively expensive, and PASS.
I found a circle down, I found that meet my requirements of the class library does not exist, so my ideas above, but also once on hold, but each time on hold, will make my inner reluctance aggravated by a point, finally one day, I resolved to do it yourself, food, so recently spent about 3 months to write a sql parsing engine from scratch, including lexical parser to the parser, do not rely on any third-party components, pure c# code. rely on any third-party components, pure c# code, in a variety of scenarios through the 156 unit tests as well as a variety of real business environment verification, today it is1.0.0 has been officially released, this project is based on the MIT protocol open source, with the following advantages.
- Supports 5 major databases, oracle, sqlserver, mysql, pgsql and sqlite.
- Extreme speed, parsing ordinary sql, the time is basically under 0.3 milliseconds, of course, the longer the sql, the longer it takes to parse.
- Documentation , as we all know , I triad of open source projects , has always been well-documented and easy to understand , to do to see the end of the hand , at the same time , I will also be based on user feedback to continue to add as well as improve the documentation .
- Simple and easy to understand code
raison d'être
is a free, full-featured and high-performance sql parsing engine library that helps you parse and process sql easily, quickly and efficiently.
Getting Started
Next, I will presentusage
Installation via Nuget
You can run the following command to install it in your project .
PM> Install-Package
Support Framework
netstandard2.0
Start with the simplest demo
Let's look at how the simplest select statement is parsed.
var sql = "select * from test";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
}
}
}
};
to the above as an example, all the leaf nodes of the abstract syntax tree are subclasses of sqlExpression, and a variety of sqlExpression nodes can be nested with each other to form a complex tree, other sql parsing engine is also divided into statement and expression, I think it's too complex, so all the unified for the sqlExpression, the top-level sqlExpression is divided into a total of four types.
- Query Statement (SqlSelectExpression)
- Insert Statement (SqlInsertExpression)
- Delete Statement (SqlDeleteExpression)
- Update Statement (SqlUpdateExpression)
Of these 4 top-level statements, I think the most complex is the query statement because there are so many combinations of queries to be compatible with a wide variety of situations, the other 3 are instead very simple. At this stage, there are 38 subclasses of sqlExpression, which I will explain to you with examples in the following demo.
1. Select query statement
As in the example above, the SqlSelectExpression represents a query statement, and the SqlSelectQueryExpression is the actual specific query statement, which includes the
- All columns to be queried (Columns field)
- Data source (From field)
- Conditional Filter Statements (Where Fields)
- Grouping statement (GroupBy field)
- Sort statement (OrderBy field)
- Pagination statement (Limit field)
- Intostatement(sql servercategorical，as ifSELECT id,name into test14 from TEST t)
- ConnectBy statement (oracle specific, e.g. SELECT LEVEL l FROM DUAL CONNECT BY NOCYCLE LEVEL<=100)
- WithSubQuerys statement, common table expression, i.e. CTE
Where Columns is a list, each of his children is a SqlSelectItemExpression, his body represents a logical clause, the value of the logical clause, you can be these
- fields, such as name.
- Binary expressions such as +3
- Function calls such as LOWER()
- A complete query statement such as SELECT name FROM TEST2 t2
Including order by, partition by, group by, between, in, case when followed by logical clauses, this will be demonstrated later, in this example, because it is to query all columns, so there is only a SqlSelectItemExpression, his body is SqlAllColumnExpression (for all columns), From represents the data source to be queried, in this case only a single table query, so the value of From is SqlTableExpression (for a single table), the table name is a SqlIdentifierExpression, that is, the identifier expression, which means that this is an Identifier, in SQL, an identifier is a name used to name database objects. These objects can include tables, columns, indexes, views, schemas, databases, and so on. Identifiers enable us to refer to and manipulate these objects, in this case, the value of identifier is test, which means that the table name is test.
1.1 Various scenarios of query return columns
1.1.1 Querying specified fields
var sql = "select id AS bid, testName from test t";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "id",
},
Alias = new SqlIdentifierExpression()
{
Value = "bid",
},
},
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "testName",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
In the above example, we specified that we want to query 2 fields, id and, at this point, there are 2 values in the Columns list, the
The first SqlSelectItemExpression contains the
- The main body, the body field, in this example his value is a SqlIdentifierExpression expression with a value of id, indicating that the column name is id.
- Alias, the Alias field, who in this example is also a SqlIdentifierExpression with a value of bid, representing the column alias as bid.
The second SqlSelectItemExpression is a SqlPropertyExpression in the body, which means that it is a property expression, SqlPropertyExpression it contains
- The table name, i.e., the Table field, has a value of t, i.e., the table is named t
- Attribute name, i.e., the Name field, with a value of Name, i.e., the attribute is named name
Together they represent the name field of table t, and the second SqlSelectItemExpression also has a column alias, i.e., testName, and this query is also a single-table query, but the SqlTableExpression he has an additional Alias alias field, i.e., it says, the table alias is t.
1.1.2 Query columns as binary expressions
var sql = "select 1+2 from test";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlNumberExpression()
{
Value = 1M,
},
Operator = ,
Right = new SqlNumberExpression()
{
Value = 2M,
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
In this example, the value of the field to be queried is a binary expression, SqlBinaryExpression, who contains the
- The left part, the Left field, has the value of a SqlNumberExpression, i.e., a numeric expression, and its value is 1
- The right part, the Right field, has the value of a SqlNumberExpression, i.e., a numeric expression, and its value is 2
- The middle symbol, the Operator field, has the value add, which is addition
This example demonstrates that SqlSelectItemExpression represents a logical clause, not just a field.
1.1.3 Queries listed as strings/numbers/booleans
var sql = "select ''' ''',3,true FROM test";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlStringExpression()
{
Value = "' '"
},
},
new SqlSelectItemExpression()
{
Body = new SqlNumberExpression()
{
Value = 3M,
},
},
new SqlSelectItemExpression()
{
Body = new SqlBoolExpression()
{
Value = true
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
In this example, to query the three fields for the string, number and Boolean value, string expression that is SqlStringExpression, body that is the value of the string ' ', number expression that is SqlNumberExpression, the value of 3, Boolean expression that is SqlBoolExpression, the value of true;
1.1.4 Queries listed as function calls
1.1.4.1 Simple function calls
var sql = "select LOWER(name) FROM test";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "LOWER",
},
Arguments = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
In this example, the expression to be queried is a function call, and the function call expression, SqlFunctionCallExpression, contains the
- The function name, the Name field, with the value LOWER.
- The list of function parameters, i.e. the Arguments field, has only one value in the list, i.e. the function has only one parameter and the value of the parameter is name
1.1.4.2 Function calls with over clauses
var sql = "SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY ORDER BY ,) as rnum FROM TEST t";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ROW_NUMBER",
},
Over = new SqlOverExpression()
{
PartitionBy = new SqlPartitionByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "rnum",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
In this example, SqlFunctionCallExpression it contains Over clauses in addition to the regular fields, specifically these
- The function name, the Name field, with a value of ROW_NUMBER.
- The list of function parameters, i.e. the Arguments field, has a value of null, i.e. no parameters.
- The Over clause, i.e. the Over field, has a value of a SqlOverExpression expression, which in turn contains the following in the SqlOverExpression itself
- PartitionBy partition clause, the value is a SqlPartitionByExpression expression, the content of the expression is also very simple, there is only one Items, that is, a list of partition expressions, in this example, the list has only one value SqlPropertyExpression, that is, based on the partitioning
- OrderBy sort clause, the value of the SqlOrderByExpression expression, the content of the expression is also very simple, there is only one Items, that is, a list of sorting expressions, the list of values for the SqlOrderByItemExpression, that is, the sorting sub-item expression, sorting sub-item expression and contains the following content
- The basis for sorting, i.e., the Body field, in this example, the basis for sorting is two SqlPropertyExpression expressions, i.e., according to, sorted by
- The sort type, the OrderByType field, has a value of Asc or Desc and defaults to asc, in both examples the default sort type is asc
1.1.4.3 Function calls with the within group clause
var sql = "select name,PERCENTILE_CONT(0.5) within group(order by \"number\") from TEST5 group by name";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "PERCENTILE_CONT",
},
WithinGroup = new SqlWithinGroupExpression()
{
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "number",
LeftQualifiers = "\"",
RightQualifiers = "\"",
},
},
},
},
},
Arguments = new List<SqlExpression>()
{
new SqlNumberExpression()
{
Value = 0.5M,
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
};
In this example, SqlFunctionCallExpression it contains within group clauses in addition to the regular fields, specifically these
- The function name, the Name field, with a value of PERCENTILE_CONT.
- Function parameter list, i.e., Arguments field, there is only one item in the list, which means that there is only 1 parameter, and the parameter is a SqlNumberExpression expression with a value of 0.5
- within group clause, that is, WithinGroup field, his value is a SqlWithinGroupExpression expression, SqlWithinGroupExpression in turn contains OrderBy sorting clause, here according to the number field sorting
1.1.5 Query columns as subqueries
var sql = "select c.*, (select as province_name from portal_area a where = c.province_id) as province_name, (select as city_name from portal_area a where = c.city_id) as city_name, (CASE WHEN c.area_id IS NULL THEN NULL ELSE (select as area_name from portal_area a where = c.area_id) END )as area_name from portal.portal_company c";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "province_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "city_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = ,
Right = new SqlNullExpression()
},
Value = new SqlNullExpression()
},
},
Else = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_company",
},
Schema = new SqlIdentifierExpression()
{
Value = "portal",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
};
In this example, the value of the column to be queried is a SqlSelectExpression expression, i.e., the column to be queried is a subquery
1.2 Where Conditional Filter Statements
1.2.1 Binary expressions
var sql = "SELECT * FROM test WHERE ID =1";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "ID",
},
Operator = ,
Right = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
In this example, the value of the where field is a binary expression, SqlBinaryExpression, who includes the
- The left part, the Left field, has the value of a SqlIdentifierExpression, i.e., an identifier expression, and its value is ID
- The right part, the Right field, has the value of a SqlNumberExpression, i.e., a numeric expression, and its value is 1
- The middle symbol, the Operator field, has the value EqualTo, which is the equal sign, but of course, it can also be greater than, less than, greater than or equal to, less than or equal to, unequal, etc.
The sides of a binary expression can be very flexible, and can be a variety of other expressions, as well as self-nesting another binary expression to form a very complex binary expression
1.2.2 between/not between clauses
var sql = "SELECT * FROM test WHERE ID BETWEEN 1 AND 2";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBetweenAndExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "ID",
},
Begin = new SqlNumberExpression()
{
Value = 1M,
},
End = new SqlNumberExpression()
{
Value = 2M,
},
},
},
};
The between clause contains the
- The Begin part, the Begin field, in this example, has the value of a SqlNumberExpression, which has a value of 1
- The End part, the End field, in this example, has the value of a SqlNumberExpression, which has a value of 2
- Body body part, i.e. Body field, value is SqlIdentifierExpression, i.e. identifier expression, value is id
- The inverse part, the IsNot field, if it is not between, then IsNot=true
1.2.3 is null/is not null clauses
var sql = "select * from test rd where is null";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Operator = ,
Right = new SqlNullExpression()
},
},
};
The is null/is not null clauses are mainly embodied in binary expressions, where the Operator field is Is/IsNot and the right field is SqlNullExpression, the null expression, which represents the value as null
1.2.4 The exists/not exists clause
var sql = "select * from TEST t where EXISTS(select * from TEST2 t2)";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlExistsExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
},
},
};
exists/not exists clauses, mainly embodied in SqlExistsExpression expressions.
- The main body, or body field, in this example has the value of a SqlSelectExpression expression
- The inverse part, the IsNot field, if it is not exists, then IsNot=true
1.2.5 like/not like clauses
var sql = "SELECT * from TEST t WHERE name LIKE '%a%'";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name",
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "%a%"
},
},
},
};
like clause, mainly in the binary expression, Operator field is Like/NotLike, in this example the right field is a string expression, that is, SqlStringExpression expression, the value is %a%.
1.2.6 all/any clauses
var sql = "select * from customer c where >all(select from orderdetail o)";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "customer",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Age",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = ,
Right = new SqlAllExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Quantity",
},
Table = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "orderdetail",
},
Alias = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
},
},
},
};
all/any clause, mainly in the SqlAllExpression/SqlAnyExpression expression, which is another SqlSelectExpression expression in its body
1.2.7 in/ not in clauses
var sql = "SELECT * from TEST t WHERE IN ('a','b','c')";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlInExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
TargetList = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a"
},
new SqlStringExpression()
{
Value = "b"
},
new SqlStringExpression()
{
Value = "c"
},
},
},
},
};
in/not in clauses, mainly in the SqlInExpression expression, which contains the
- The body field, the body of the in, is in this case a SqlPropertyExpression with a value of
- TargetList field, i.e. the target list of in, in this case it is a list of SqlExpression, which includes 3 SqlStringExpression, i.e. string expression, a,b,c.
- The inverse part, the IsNot field, if it is not in, then IsNot=true
Of course, in also has another type of subquery, the
var sql = "select * from TEST5 WHERE NAME IN (SELECT NAME FROM TEST3)";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
Where = new SqlInExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
SubQuery = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST3",
},
},
},
},
},
},
};
In the SqlInExpression expression here, it contains the
- The body field, the body of the in, is in this case a SqlIdentifierExpression with the value NAME
- SubQuery field, i.e. subquery, value is a SqlSelectExpression
- IsNot field, IsNot=true if not in
1.2.8 case when clauses
var sql = "SELECT CASE WHEN ='1' THEN 'a' WHEN ='2' THEN 'b' ELSE 'c' END AS v from TEST t";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Value = new SqlStringExpression()
{
Value = "a"
},
},
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "2"
},
},
Value = new SqlStringExpression()
{
Value = "b"
},
},
},
Else = new SqlStringExpression()
{
Value = "c"
},
},
Alias = new SqlIdentifierExpression()
{
Value = "v",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
The case when clause, which is mainly embodied in the SqlCaseExpression expression, contains the
- A list of various case when key-value pairs, i.e., the Items field, where each element in the list is a SqlCaseItemExpression expression, which, in turn, contains the
- The condition, or Condition field, in this case is a binary expression, a SqlBinaryExpression expression, with a value of ='1'
- The value field, value, in this case is the string a
- Else field, the default value, in this case the string c
There is another syntax for case when, as follows:
var sql = "select case when 'a' then 1 else 2 end from test t ";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlStringExpression()
{
Value = "a"
},
Value = new SqlNumberExpression()
{
Value = 1M,
},
},
},
Else = new SqlNumberExpression()
{
Value = 2M,
},
Value = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
In this SqlCaseExpression expression, he includes the
- The subject variable of the case condition, the Value field, which in this example has the value SqlPropertyExpression, has the value
- A list of various when then key-value pairs, i.e. the Items field, where each element in the list is a SqlCaseItemExpression expression, which, in turn, contains the
- The condition, or Condition field, in this case is the string expression SqlStringExpression, which has the value a
- value, that is, the Value field, in this case the value of SqlNumberExpression, its value is 1
- Else field, the default value, in this case the number 2
1.2.9 not clause
var sql = "select * from TEST t WHERE not ='abc'";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "abc"
},
},
},
},
};
not clause, mainly in the SqlNotExpression expression, which has only one body field, that is, the part that represents the negation of the
1.2.10 Variable clauses
var sql = "select * from TEST t WHERE not =:name";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = ,
Right = new SqlVariableExpression()
{
Name = "name",
Prefix = ":",
},
},
},
},
};
The variable clause, which is mainly embodied in the SqlVariableExpression expression, consists of the following parts.
- Variable name, i.e. field Name, here the value is name.
- Variable prefix, here the value is.
1.3 From Data Source
In sql, the From keyword is followed by various forms to specify the data source. The main ones are as follows
1.3.1 Table Name or View
select * from test
This parsing result was demonstrated above.
1.3.2 Sub-queries (sub-tables)
var sql = "select * from (select * from test) t";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlSelectExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
},
},
};
In this example, the value of the data source From is a SqlSelectExpression, i.e., a SqlSelectExpression can be nested within a SqlSelectExpression, and we notice that the internal SqlSelectExpression has a table alias with the field Alias. The value of the identifier is t, which means that the table alias is t;
1.3.3 Concatenated table lookup (JOIN)
var sql = "select from test t1 left join test2 t2 on = right join test3 t3 on =";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t1",
},
},
JoinType = ,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
JoinType = ,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test3",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
},
};
In the example above, we demonstrated how a concatenated table query is parsed, with the value of the From field being a SqlJoinTableExpression, i.e., a concatenated table query expression, that contains the
- The left part, the Left field
- The right part, the Right field
- Join type, that is, JoinType field, the value includes InnerJoin, LeftJoin, RightJoin, FullJoin, CrossJoin, CommaJoin.
- The table association condition, the Conditions field. Here, the value of the Conditions field is a binary expression SqlBinaryExpression
In this example, a total of three tables to check, SqlJoinTableExpression in the left field is a SqlJoinTableExpression, that is, SqlJoinTableExpression in the SqlJoinTableExpression can be nested in the SqlJoinTableExpression, infinite nesting.
1.3.4 Common Table Expressions (CTE)
var sql = "with c1 as (select name from test t) , c2(name) AS (SELECT name FROM TEST2 t3 ) select *from c1 JOIN c2 ON =";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
WithSubQuerys = new List<SqlWithSubQueryExpression>()
{
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c1",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c2",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
Columns = new List<SqlIdentifierExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c1",
},
},
JoinType = ,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c1",
},
},
Operator = ,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c2",
},
},
},
},
},
};
Common Table Expressions (CTE), mainly embodied in the WithSubQuerys field of SqlSelectQueryExpression, he is a list of SqlWithSubQueryExpression expressions, i.e., a list of common tables, and every element inside it is a member of the SqlWithSubQueryExpression expression, this expression, containing the
- The source portion of the public table, the FromSelect field, whose value in this example is a SqlSelectExpression expression, i.e. a query
- The table alias for the public table, the Alias field, whose value in this example is c1
- The column portion of the public table, the Columns field, has only one column name in this example, name
1.3.5 Result sets returned by functions
Specific databases support queries from functions that return a result set, such as oracle to add a custom function splitstr, his role is to split a string according to the ; sign to return multiple rows of data
var sql = "SELECT * FROM TABLE(splitstr('a;b',';'))";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlReferenceTableExpression()
{
FunctionCall = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TABLE",
},
Arguments = new List<SqlExpression>()
{
new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "splitstr",
},
Arguments = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a;b"
},
new SqlStringExpression()
{
Value = ";"
},
},
},
},
},
}
},
};
The result set returned by the function is mainly embodied in the SqlReferenceTableExpression expression, which internally contains a FunctionCall field with the value of the SqlFunctionCallExpression expression, representing the query from the result set of the function call.
1.4 OrderBy Sort Statements
var sql = "select from FlowActivity fa order by desc, asc";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType =
},
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType =
},
},
},
},
};
OrderBy sort clause, the value of the SqlOrderByExpression expression, the content of the expression is also very simple, there is only one Items, that is, a list of sorting sub-item expression, the value of the list is SqlOrderByItemExpression, that is, the sorting sub-item expression, sorting sub-item expression and contains the following content
- The basis for sorting, i.e., the Body field, in this example, the basis for sorting is two SqlPropertyExpression expressions, i.e., according to, sorted by
- The sort type, the OrderByType field, has a value of Asc or Desc and defaults to asc, in these 2 examples there is asc and Desc
- The NullsType field that determines whether null comes first or last, in oracle, pgsql, sqlite we can specify the position of null in the sort, as in the following sql
select * from TEST5 t order by desc nulls FIRST, ASC NULLS last
Then we have the NullsType field, which has and,corresponding to his value.
1.5 GroupBy Grouping Statements
var sql = "select from FlowActivity fa group by , HAVING count()>1";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
Having = new SqlBinaryExpression()
{
Left = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "count"
},
Arguments = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
Operator = ,
Right = new SqlNumberExpression()
{
Value = 1M
},
},
},
},
};
GroupBy grouping statement, the value is a SqlGroupByExpression expression, who is as follows
- A list of grouped subexpressions, i.e., the Items field, with the value SqlExpression in the list, whose value is a logical clause
- The grouping filter clause, the Having field, has a logical clause whose value, in this case, is a SqlBinaryExpression.
1.5 Limit paging clause
1.5.1 mysql，sqlite
var sql = "select * from test t limit 1,5";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 1M,
},
RowCount = new SqlNumberExpression()
{
Value = 5M,
},
},
},
};
Limit paging clause with the value of a SqlLimitExpression expression, who looks like this
- The number per page, or RowCount field, which in this example has a value of 5
- The skip quantity, the Offset field, in this example, has a value of 1.
1.5.2 oracle
var sql = "SELECT * FROM TEST3 t ORDER BY DESC FETCH FIRST 2 rows ONLY";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "TEST3"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
OrderByType = ,
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "NAME" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
RowCount = new SqlNumberExpression()
{
Value = 2
}
}
}
};
1.5.3 pgsql
var sql = "select * from test5 t order by limit 1 offset 10;";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test5",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 10M,
},
RowCount = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
1.5.4 sqlServer
var sql = "select * from test t order by OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "test"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "name" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 5
},
RowCount = new SqlNumberExpression()
{
Value = 10
}
}
}
};
1.6 ConnectBy hierarchical query statement (oracle specific)
var sql = "SELECT EMPLOYEEID , MANAGERID , LEVEL FROM EMPLOYEE e START WITH MANAGERID IS NULL CONNECT BY NOCYCLE PRIOR EMPLOYEEID = MANAGERID ORDER SIBLINGS BY EMPLOYEEID ";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "LEVEL",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "EMPLOYEE",
},
Alias = new SqlIdentifierExpression()
{
Value = "e",
},
},
ConnectBy = new SqlConnectByExpression()
{
StartWith = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
Operator = ,
Right = new SqlNullExpression()
},
Body = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
Operator = ,
Right = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
IsNocycle = true,
IsPrior = true,
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
},
IsSiblings = true,
},
},
},
};
ConnectBy hierarchical query clause with the value of the SqlConnectByExpression expression, who looks like this
- Specify the root condition of the hierarchical query, i.e., the StartWith field, which in this example has the value of a SqlBinaryExpression binary expression.
- The subject associates the conditional clause, the Body field, whose value in this example is a SqlBinaryExpression binary expression
- The IsPrior field is used to indicate which column in the hierarchy is the parent node, and has a value of true if the Prior keyword is present in the sql
- IsNocycle field, used to prevent circular references leading to infinite recursion, true if Nocycle exists in sql
- order by clause for sorting
1.7 Into clause (sql server specific)
var sql = "SELECT name into test14 from TEST as t ";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
},
},
Into = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test14"
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
};
into clause, which in this example has the value SqlTableExpression, i.e. into a table.
2. Insert statement
2.1 Insertion of individual values
var sql = "insert into test11(name,id) values('a1','a2')";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
As in the above example, the insert statement is represented as a SqlInsertExpression who contains the
- The list of fields to be inserted, i.e., the Columns field, has a value of a list of SqlExpression, in this example the value is 2 SqlIdentifierExpression, whose values are name and id, i.e., inserting the name and id fields
- Values list, that is, ValuesList field, the value of a List<SqlExpression> of the list, that is, the list of elements in the list is a list, each element represents a set of data to be inserted, the example list in the list there is only a List
, and the values in the sublist are a1 and a2, i.e., the values to be inserted are a1 and a2. - The table into which the data is to be inserted, i.e., the Table field, which in this case is test11.
Why is the ValuesList field a nested list within a list? Mainly because multiple lists of values can be inserted, so let's move on to the next section
2.2 Inserting multiple values
var sql = "insert into test11(name,id) values('a1','a2'),('a3','a4')";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a3"
},
new SqlStringExpression()
{
Value = "a4"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
In this example, there are 2 child elements in the ValuesList field, i.e. 2 List
2.3 Values to be inserted as a subquery
var sql = "INSERT INTO TEST2(name) SELECT name AS name2 FROM TEST t";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2"
},
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
Alias = new SqlIdentifierExpression()
{
Value = "name2"
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
},
};
As in the above example, the insert statement is represented as a SqlInsertExpression who contains the
- The list of fields to be inserted, i.e. the Columns field, has the value of a list of SqlExpression, in this case name.
- The subquery source, i.e. the FromSelect field, has a value of a SqlSelectExpression, i.e. a subquery
- The table into which the data is to be inserted, i.e. the Table field, which in this example has the value TEST2
3. Update statement
var sql = "update test set name ='4',d='2024-11-22 08:19:47.243' where name ='1'";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlUpdateExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Items = new List<SqlExpression>()
{
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "4"
},
},
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "d"
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "2024-11-22 08:19:47.243"
},
},
},
};
As in the above example, the update statement is represented as a SqlUpdateExpression who contains the
- The list of (field-values) to be updated, i.e. the Items field, with a value of a list of SqlExpression, in this case the values are 2 SqlBinaryExpression, i.e. name='4' and d='2024-11-22 08:19:47.243'
- The conditional filter clause, the Where field, represents the filter condition, in this case the value is a SqlBinaryExpression, i.e. name = '1'.
- The table where the data is to be updated, i.e. the Table field, which in this example has the value test
4. Delete
var sql = "delete from test where name=4";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlDeleteExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = ,
Right = new SqlNumberExpression()
{
Value = 4M
},
},
};
As in the example above, the delete statement is represented as a SqlDeleteExpression, who contains the
- The conditional filter clause, the Where field, represents the filter condition, in this case the value is a SqlBinaryExpression, i.e. name=4
- The table from which the data is to be deleted, i.e. the Table field, which in this example has the value test
5. Annotation processing
5.1 Single-line comments
var sql = @"select *--abc from test lbu WHERE a ='1'--aaaaaa
FROM test";
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
As in the example above, the single line comment is correctly ignored and parsed correctly.
5.2 Multi-line comments
var sql = @"/*This
is
top */
select *--abc
FROM test/*this
is the
bottom */"; var sqlAst = (sql, ); var sqlAst = (sql, )
var sqlAst = (sql, );
The parsing results are as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
As in the example above, multi-line comments are correctly ignored and parsed correctly.
6. how to parse the ast abstract syntax tree
When we pass the
var sql = @"select * from test";
var sqlAst = (sql, );
After parsing the sql to get the abstract syntax tree, we have to parse the abstract syntax tree to get the data we want, at this time we have to use the visitor pattern (visitor) .
6.1 The Visitor Model
The most important feature of the Visitor pattern is the separation of structure and algorithm, combined with the understanding of this project, is that the structure of the ast abstract syntax tree has been parsed, you can write an algorithm to parse this syntax tree according to your own needs. This is a 1-N operation, that is, an abstract syntax tree, can correspond to N parsing algorithms, when we want to customize the algorithm to parse the abstract syntax tree, we need to customize a Visitor class, and implement the IAstVisitor interface
public class CustomVisitor : IAcceptVisitor
{
}
But to implement this interface, we need to implement many methods in the interface, and some data is not our concern, so I provide an abstract class BaseAstVisitor that implements the IAcceptVisitor interface to simplify the operation, we only need to inherit this abstract class, and then rewrite the methods we are interested in can be
public class CustomVisitor : BaseAstVisitor
{
}
In this project, I provide 2 basic vistors for you to use, UnitTestAstVisitor and SqlGenerationAstVisitor, you can refer to these 2 visitors to write your own algorithms to parse abstract syntax trees. Next, I will introduce the usage of these 2visitors.
6.2 UnitTestAstVisitor
When we pass the
var sql = @"select * from test";
var sqlAst = (sql, );
After parsing the sql to get the abstract syntax tree, sqlAst is actually still a data structure, we can monitor the variable through the vs to see the internal structure, but if it is a very complex sql, the tree will be huge, to rely on us to manually go to slowly open the view of the exhaustion, that's right! write unit tests, I started with the results of hand-written to compare the results of the engine parsed out, and then I was exhausted, that is not to say that this work is not a person to do, so after a painful experience I wrote this UnitTestAstVisitor to generate the structure of the ast string for me, let's take a look at the use of the next
var sql = @"select * from test";
var sqlAst = (sql, );
var unitTestAstVisitor = new UnitTestAstVisitor();
(unitTestAstVisitor);
var result = ();
where result is the string generated by parsing the abstract syntax tree, as follows:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
Then paste this generated string into vs and compare it to the engine generated result.
((expect));
At this point, my workload for writing unit tests has been greatly reduced, and the structure of the generated sqlAst syntax tree is much more readily apparent.
6.2 SqlGenerationAstVisitor
After we generate the abstract syntax tree by parsing the sql, if we want to add a where condition to this abstract syntax tree, for example, add ='a'
var sql = @"select * from test";
var sqlAst = (sql, );
if (sqlAst is SqlSelectExpression sqlSelectExpression && is SqlSelectQueryExpression sqlSelectQueryExpression)
{
= new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Table = new SqlIdentifierExpression()
{
Value = "test"
},
Name = new SqlIdentifierExpression()
{
Value = "name"
}
},
Operator = ,
Right = new SqlStringExpression()
{
Value = "a"
}
};
}
Well, now we have added, the next thing we must be thinking of the abstract syntax tree into sql statements, at this point, you need to use the SqlGenerationAstVisitor, he is responsible for the abstract syntax tree into sql
var sqlGenerationAstVisitor = new SqlGenerationAstVisitor();
(sqlGenerationAstVisitor);
var newSql = ();
The newSql we get is the new sql, and his value is
select * from test where( = 'a')
So far, we have achieved our goal.
7. Theoretical foundations of sql parsing
The reason why sql can be parsed by us is mainly because sql is a formal language, an important difference between natural and formal languages is that a statement in a natural language may have multiple meanings, while a statement in a formal language can only have one semantics; the syntax of a formal language is artificially specified, with certain grammar rules, the grammar parser will be able to parse out the unique meaning of a statement's unique meaning.
Project Insights
- The only solution to the nesting problem is to use recursion
- For basic projects, unit testing is very, very important, because the process of development may be constantly refactoring, that previously run test cases may fail, if at this point you need to rely on people to manually go back to the test to verify the words, the amount of work is the amount of days to do, do not finish, simply can not be done, so the correct solution is to write unit tests, add a new feature, write 1-N unit tests for this feature! So the right solution is to write unit tests, after adding a new feature, write 1-N unit tests for the feature to make sure that the new feature covers all situations, and then run all the unit tests again to make sure that it doesn't affect the old feature. Of course, the most devastating thing about running unit tests is that when I run all the unit tests and they are red (i.e. fail) dozens of times, the sky is falling.
Open source address, welcome star
This project is based on the MIT protocol open source, the address is
/TripleView/
Thanks also to the following programs
- Alibaba open source druid
put at the end
If you think the project is good, welcome to a key three even (recommended, star, attention), and welcome to join the three open source exchange group, QQ group number: 799648362
