Please parse Stored Procedures

[kabirsachdev]

Thanks for sharing this parser!

Had got an error while parsing the following stored procedure :

CREATE PROCEDURE GetAllProducts()
BEGIN
    SELECT *  FROM products;
END

ERROR :
File "/usr/.local/lib/python3.6/site-packages/pyparsing.py", line 4067, in parseImpl
return self.expr._parse( instring, loc, doActions, callPreParse=False )
File "/usr/.local/lib/python3.6/site-packages/pyparsing.py", line 1709, in _parseCache
value = self._parseNoCache(instring, loc, doActions, callPreParse)
File "/usr/.local/lib/python3.6/site-packages/pyparsing.py", line 1530, in _parseNoCache
loc,tokens = self.parseImpl( instring, preloc, doActions )
File "/usr/.local/lib/python3.6/site-packages/pyparsing.py", line 2689, in parseImpl
raise ParseException(instring, loc, self.errmsg, self)
pyparsing.ParseException: Expected select (at char 0), (line:1, col:1)

[klahnakoski]

There was never any plans to add stored procedures; that is a whole other language.

But, maybe it can be done; even if it does not implement the full spec right away. This is a good first test.

[kabirsachdev]

@klahnakoski is it possible for you to help with directing me to the part of the code which would require being changed in order to make it work? Thanks.

[klahnakoski]

@kabirsachdev The code is a number of parsing rules, all in sql_parser.py. Look it over to see if you can get a sense of how it works: Like all declarative languages: they are easy to read, but hard to write/debug (compared to procedural languages).

If you have a sense for what is written, you can start adding rules for parsing procedure declarations and some simple statements.

Here is a link to the formalism: https://en.wikipedia.org/wiki/Parsing_expression_grammar

The pyparsing project has a number of examples, ranging from simple to complex

[klahnakoski]

... read sql_parser.py from the bottom, upwards

[kabirsachdev]

Will look into this, thanks @klahnakoski!

Had another question about not being able to parse queries with string and date-time functions . Would be great if you could help with the same

[kabirsachdev]

An example of the error while parsing Date-Time queries :
Query -

SELECT Order_Date + INTERVAL '3' MONTH

Error -

raise ParseException(sql, e.loc, "Expecting one of (" + (", ".join(expecting)) + ")")
pyparsing.ParseException: Expecting one of (, offset, using, cross join, reserved, is, or, right join, left outer join, binary_and, end, binary_or, concat, right outer join, not_between, union all, asc, full outer join, group by, neq, when, in, between, desc, and, inner join, order by, where, collate nocase, left join, like, case, as, from, having, limit, else, join, select, with, nlike, then, full join, nin, on, union) (at char 29), (line:1, col:30)

[klahnakoski]

I have captured SELECT Order_Date + INTERVAL '3' MONTH in a test on my local dev. It will be a few days before I can release fix it.

[kabirsachdev]

Thanks, @klahnakoski
Could you also help me out with another variation of date-time queries, which uses the EXTRACT function? Example :

SELECT EXTRACT(year from '2012-10-08') FROM tableName