datacamp / antlr-tsql Goto Github PK

License: GNU Affero General Public License v3.0

Makefile 0.57% Python 21.26% ANTLR 77.51% Dockerfile 0.65%

antlr-tsql's Introduction

antlr-tsql

Development

ANTLR requires Java, so we suggest you use Docker when building grammars. The Makefile contains directives to clean, build, test and deploy the ANTLR grammar. It does not run Docker itself, so run make inside Docker.

Build the grammar

# Build the docker container
docker build -t antlr_tsql .

# Run the container to build the python grammar
# Write parser files to local file system through volume mounting
docker run -it -v ${PWD}:/usr/src/app antlr_tsql make build

Set up the Python module

Now that the Python parsing files are available, you can install them with pip:

pip install -r requirements.txt
pip install -e .

And parse SQL code in Python:

from antlr_tsql import ast
ast.parse("SELECT a from b")

Using the AST viewer

If you're actively developing on the ANLTR grammar or the tree shaping, it's a good idea to set up the AST viewer locally so you can immediately see the impact of your changes in a visual way.

Clone the ast-viewer repo and build the Docker image according to the instructions.
Spin up a docker container that volume mounts the Python package, symlink-installs the package and runs the server on port 3000:

docker run -it \
  -u root \
  -v ~/workspace/antlr-tsql:/app/app/antlr-tsql \
  -p 3000:3000 \
  ast-viewer \
  /bin/bash -c "echo 'Install development requirements in development:' \
    && pip install --no-deps -e app/antlr-tsql \
    && python3 run.py"

When simultaneously developing other packages, volume mount and install those too:

docker run -it \
  -u root \
  -v ~/workspace/antlr-ast:/app/app/antlr-ast \
  -v ~/workspace/antlr-plsql:/app/app/antlr-plsql \
  -v ~/workspace/antlr-tsql:/app/app/antlr-tsql \
  -p 3000:3000 \
  ast-viewer \
  /bin/bash -c "echo 'Install development requirements in development:' \
    && pip install --no-deps -e app/antlr-ast \
    && pip install --no-deps -e app/antlr-plsql \
    && pip install --no-deps -e app/antlr-tsql \
    && python3 run.py"

If you update the tree shaping logic in this repo, the app will auto-update.
If you change the grammar, you will have to first rebuild the grammar (with the antlr_tsql docker image) and restart the ast-viewer container.

Run tests

# Similar to building the grammar, but running tests
# and not saving the generated files
docker build -t antlr_tsql .
docker run -t antlr_tsql make build test

Or run the test locally, first build the grammar then run:

pytest

Travis deployment

Builds the Docker image.
Runs the Docker image to build the grammar and run the unit tests.
Deploys the resulting python files to PyPi when a new release is made, so they can be installed easily.

antlr-tsql's People

Contributors

Stargazers

Watchers

Forkers

optionalg fossabot pborne michalharakal lutzkuen

antlr-tsql's Issues

Bad support for several constructs of a new course

@sumedh10 is working on a Transact-SQL course but there are some queries that result in unshaped nodes. Idealy, we can turn these into shaped nodes that can be easily 'walked' with check_node and check_edge.

While loop

--First create the variable and assign a datatype
DECLARE @counter int

--Assign a value to the variable using SET
SET  @counter = 20
--Create a loop to run until  @counter = 30
WHILE @counter < 30
--Loop code starting point
BEGIN
    PRINT @counter
	SELECT @counter = @counter + 1
-- Loop finish
END

Link to diff viewer

Cursors

--Create a Cursor variable
DECLARE UpdateCursor CURSOR FOR  
--Select statement containing values to be processed
SELECT Recordid, CoronaryArteryDisease 
FROM Artery

Link to diff viewer

Fetch next et al.

--Start looping through the records in the query loop
WHILE @@FETCH_STATUS = 0   
BEGIN   
        UPDATE Kidney SET CoronaryArteryDisease = @CoronaryArteryDisease
        WHERE Recordid = @recordid
        
        FETCH NEXT FROM UpdateCursor INTO  @recordid, @CoronaryArteryDisease
END

Link to diff viewer

shape ast for JOIN

SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;

And the icing on the test cake:

SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2,
	   a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID AND AddressType = 'Main Office'
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID;

full AST parsing of expression rule

primative_expression, look out for constants with a sign in front of them:

constant
    : STRING // string, datetime or uniqueidentifier
    | BINARY
    | sign? DECIMAL
    | sign? (REAL | FLOAT)  // float or decimal
    | sign? dollar='$' (DECIMAL | FLOAT)       // money
    ;

TODO:

Case Example:

SELECT SalesOrderID, OrderDate,
    CASE
      WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
      ELSE 'Shipped'
    END AS ShippingStatus
FROM SalesLT.SalesOrderHeader;

add missing functions

UPPER, ROUND

see: https://msdn.microsoft.com/en-us/library/ms180055.aspx

full AST parsing of function calls

see visual_checks.yml:

function_call:
  # aggregate_windowed_function
  - "COUNT(ALL id = 1) OVER ( PARTITION BY a + b ORDER BY a)"
  - "COUNT(*)"
  - "AVG ( ALL id )"
  - "GROUPING_ID ( a, b )"
  # ranking windowed_function
  - "RANK () OVER ( PARTITION BY a + b ORDER BY a)"
  - "NTILE ( a + b )"
  # standard_call
  - "COALESCE ( a, b )"
  - "BINARY_CHECKSUM (*)"
  - "DATEADD ( a, 1 + 1, 2 + 2)"
  - "RIGHT ( 1 + 1, 2 + 2 )"
  # simple_call
  - "SESSION_USER"
  # cast_call
  - "CAST ( a as varchar )"

WITH should be a clause on SelectStmt

see postgres parser

PgQuery.parse("
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
")

shape AST for common SELECT statements

These should not have any Unshaped nodes...

DONE?!

SELECT TOP 10 PERCENT Name
FROM SalesLT.Product

SELECT * FROM SalesLT.Customer;

SELECT Title, FirstName FROM SalesLT.Customer;

SELECT Title + ' ' + LastName FROM SalesLT.Customer;

SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
FROM SalesLT.Customer;

SELECT CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
FROM SalesLT.SalesOrderHeader;

shape ast for UNION

SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Main Office'
INTERSECT
SELECT c.CompanyName
FROM SalesLT.Customer AS c
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping'
ORDER BY c.CompanyName;

Move tests folder to root

move with_expression out of statements

common table expressions all have with_expression in them. Like so,

select_statement
    : with_expression? query_expression order_by_clause? for_clause? option_clause? ';'?
    ;

Should move it out, like rule: with_expression? common_table_expression ...

see https://msdn.microsoft.com/en-us/library/ms175972.aspx

Remove vagrantfile

From #29

The Vagrantfile makes it possible to use the graphical tools that come with antlr, and also to watch and rebuild when files change. We could definitely replace the second part by using the Dockerfile + volume mounting. The first part (graphical tools) isn't as important now that we are using sqlwhat-viewer. I'll open an issue--if we can volume mount and rebuild on changes with Docker without a lot of fuss, then it seems reasonable to ditch the Vagrantfile.

ast parsing for UPDATE

UPDATE SalesLT.Customer
SET EmailAddress = NULL
WHERE CustomerID % 7 = 1;

datacamp / antlr-tsql Goto Github PK

antlr-tsql's Introduction

antlr-tsql

Development

Build the grammar

Set up the Python module

Using the AST viewer

Run tests

Travis deployment

antlr-tsql's People

Contributors

Stargazers

Watchers

Forkers

antlr-tsql's Issues

While loop

Cursors

Fetch next et al.

Recommend Projects

Recommend Topics

Recommend Org