CODEWITHSUNDEEP
t-sqlparsingantlr4
Robert Ivan
Robert Ivan

Reputation: 19

Parsing a T-SQL query using ANTLR4

I'm currently developing a T-SQL parser that will be able to map the query in a database for my school project. So far I've managed to parse most of it but I have a problem that I can't seem to solve.. I'm using C# with tsql.g4 grammar and ANTLR4.

How do I access the expression? I want to know that I'm in the select and that I have some sort of expression, but what kind of expression is the expression? Is it possible to extract only the column name, schema(if there is one) and database(if there is one) from the select_statement?

Whenever I try to access expression from the c# code:

context: GrammarSQLParser.Dml_clauseContext context context.select_statement().query_expression().query_specification().select_list().select_list_elem().expression()

I only get the method CopyFrom(), I don't have any full_column_name() method to access further table_name() and so on..

edit1: after close inspection, the expression is a column_ref_expression, but how do I access it from expression? They're different objects..

This is the query:

select p.BusinessEntityID,p.FirstName,p.LastName,adresstbl.* from person.Person as p 
join (select bea.BusinessEntityID, adr.AddressLine1, adr.AddressLine2, adr.City, adr.PostalCode from person.BusinessEntityAddress as bea 
join person.Address as adr
on bea.AddressID = adr.AddressID )
as adresstbl 
on not p.BusinessEntityID != adresstbl.BusinessEntityID order by p.BusinessEntityID

This is my parse tree:

Lisp Parse Tree: (tsql_file (batch (sql_clauses (sql_clause (dml_clause (select_statement (query_expression (query_specification select (select_list (select_list_elem (expression (full_column_name (table_name (id (simple_id p))) . (id (simple_id BusinessEntityID))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id p))) . (id (simple_id FirstName))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id p))) . (id (simple_id LastName))))) , (select_list_elem (table_name (id (simple_id adresstbl))) . *)) from (table_sources (table_source (table_source_item_joined (table_source_item (table_name_with_hint (table_name (id (simple_id person)) . (id (simple_id Person)))) (as_table_alias as (table_alias (id (simple_id p))))) (join_part join (table_source (table_source_item_joined (table_source_item (derived_table (subquery (select_statement (query_expression ( (query_expression (query_specification select (select_list (select_list_elem (expression (full_column_name (table_name (id (simple_id bea))) . (id (simple_id BusinessEntityID))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id adr))) . (id (simple_id AddressLine1))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id adr))) . (id (simple_id AddressLine2))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id adr))) . (id (simple_id City))))) , (select_list_elem (expression (full_column_name (table_name (id (simple_id adr))) . (id (simple_id PostalCode)))))) from (table_sources (table_source (table_source_item_joined (table_source_item (table_name_with_hint (table_name (id (simple_id person)) . (id (simple_id BusinessEntityAddress)))) (as_table_alias as (table_alias (id (simple_id bea))))) (join_part join (table_source (table_source_item_joined (table_source_item (table_name_with_hint (table_name (id (simple_id person)) . (id (simple_id Address)))) (as_table_alias as (table_alias (id (simple_id adr))))))) on (search_condition (search_condition_and (search_condition_not (predicate (expression (full_column_name (table_name (id (simple_id bea))) . (id (simple_id AddressID)))) (comparison_operator =) (expression (full_column_name (table_name (id (simple_id adr))) . (id (simple_id AddressID)))))))))))))) ))))) (as_table_alias as (table_alias (id (simple_id adresstbl))))))) on (search_condition (search_condition_and (search_condition_not not (predicate (expression (full_column_name (table_name (id (simple_id p))) . (id (simple_id BusinessEntityID)))) (comparison_operator ! =) (expression (full_column_name (table_name (id (simple_id adresstbl))) . (id (simple_id BusinessEntityID)))))))))))))) (order_by_clause order by (order_by_expression (expression (full_column_name (table_name (id (simple_id p))) . (id (simple_id BusinessEntityID))))))))))) )

Upvotes: 1

Views: 1609

Answers (1)

Scott
Scott

Reputation: 26

expression could be one of many things. If you look at the grammar, you will see

expression
    : DEFAULT                                                  #primitive_expression
    | NULL                                                     #primitive_expression
    | LOCAL_ID                                                 #primitive_expression
    | constant                                                 #primitive_expression
    | function_call                                            #function_call_expression
    | expression COLLATE id                                    #function_call_expression
    | case_expr                                                #case_expression
    | full_column_name                                         #column_ref_expression
    | '(' expression ')'                                       #bracket_expression
    | '(' subquery ')'                                         #subquery_expression
    | '~' expression                                           #unary_operator_expression

    | expression op=('*' | '/' | '%') expression               #binary_operator_expression
    | op=('+' | '-') expression                                #unary_operator_expression
    | expression op=('+' | '-' | '&' | '^' | '|') expression   #binary_operator_expression
    | expression comparison_operator expression                #binary_operator_expression
    ;

You need to figure out which of these things it is. In your case, it is a column_ref_expression, in which case you will need to cast it to Column_ref_expressionContext and then you will be able to access full_column_name.

There are various ways to figure out which it is, or you can just assume if you only want to support a limited subset of the grammar.

Upvotes: 1

Related Questions