# Установка sqlcheck ( https://github.com/jarulraj/sqlcheck#installation )
# Debian
wget https://github.com/jarulraj/sqlcheck/releases/download/v1.3/sqlcheck-x86_64.deb
dpkg -i sqlcheck-x86_64.deb

# Windows
# При установке для Windows не забудьте добавить sqlcheck в PATH
wget https://github.com/jarulraj/sqlcheck/releases/download/v1.3/sqlcheck.exe

# После sqlcheck, установим swissql
pip install swissql

$ # Использование отдельного модуля - Syntax
!python -m swissql syntax -q "SELECT * from x join y on x.id = y.id where x.id > 2"

[Generating syntax tree using sqlglot]
(SELECT expressions: 
  (STAR ), from: 
  (FROM expressions: 
    (TABLE this: 
      (IDENTIFIER this: x, quoted: False))), joins: 
  (JOIN this: 
    (TABLE this: 
      (IDENTIFIER this: y, quoted: False)), on: 
    (EQ this: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: x, quoted: False)), expression: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: y, quoted: False)))), where: 
  (WHERE this: 
    (GT this: 
      (COLUMN this: 
        (IDENTIFIER this: id, quoted: False), table: 
        (IDENTIFIER this: x, quoted: False)), expression: 
      (LITERAL this: 2, is_string: False))))

# Использование отдельного модуля - Style
!python -m swissql style -q "SELECT * from x join y on x.id = y.id where x.id > 2"

[Style sql query use sqlfluff]
/home/quakumei/Workspace/Code/sparkySQL/SparkSQL-Analyzer/swissql/analyzers/style/intermediate.sql
== [/home/quakumei/Workspace/Code/sparkySQL/SparkSQL-Analyzer/swissql/analyzers/style/intermediate.sql] FAIL
L:   1 | P:   1 | L044 | Query produces an unknown number of result columns.
L:   1 | P:   8 | L027 | Unqualified reference '*' found in select with more than
                       | one referenced table/view.
L:   1 | P:  10 | L010 | Keywords must be consistently upper case.
L:   1 | P:  17 | L010 | Keywords must be consistently upper case.
L:   1 | P:  17 | L051 | Join clauses should be fully qualified.
L:   1 | P:  24 | L010 | Keywords must be consistently upper case.
L:   1 | P:  39 | L010 | Keywords must be consistently upper case.
L:   1 | P:  53 | L009 | Files must end with a single trailing newline.
All Finished!

# Использование отдельного модуля - Anti_Pattern
!python -m swissql anti_pattern -q "SELECT * from x join y on x.id = y.id where x.id > 2"

�[33m[Detecting anti-patterns using sqlcheck]�[0m
sqlcheck version 1.2.1
Debug build (NDEBUG not #defined)
+-------------------------------------------------+
|                   SQLCHECK                      |
+-------------------------------------------------+
> RISK LEVEL    :: ALL ANTI-PATTERNS
> SQL FILE NAME :: temp.sql
> COLOR MODE    :: ENABLED
> VERBOSE MODE  :: ENABLED
> DELIMITER     :: ;
-------------------------------------------------
==================== Results ===================

-------------------------------------------------
SQL Statement at line 1: �[1m�[31mselect * from x join y on x.id = y.id where x.id > 2;�[0m�[39m
[temp.sql]: (�[1m�[32mHIGH RISK�[0m�[39m) �[1m�[34mSELECT *�[0m�[39m
● Inefficiency in moving data to the consumer:
When you SELECT *, you're often retrieving more columns from the database than
your application really needs to function. This causes more data to move from
the database server to the client, slowing access and increasing load on your
machines, as well as taking more time to travel across the network. This is
especially true when someone adds new columns to underlying tables that didn't
exist and weren't needed when the original consumers coded their data access.


● Indexing issues:
Consider a scenario where you want to tune a query to a high level of
performance. If you were to use *, and it returned more columns than you
actually needed, the server would often have to perform more expensive methods
to retrieve your data than it otherwise might. For example, you wouldn't be able
to create an index which simply covered the columns in your SELECT list, and
even if you did (including all columns [shudder]), the next guy who came around
and added a column to the underlying table would cause the optimizer to ignore
your optimized covering index, and you'd likely find that the performance of
your query would drop substantially for no readily apparent reason.

● Binding
Problems:
When you SELECT *, it's possible to retrieve two columns of the same name from
two different tables. This can often crash your data consumer. Imagine a query
that joins two tables, both of which contain a column called "ID". How would a
consumer know which was which? SELECT * can also confuse views (at least in some
versions SQL Server) when underlying table structures change -- the view is not
rebuilt, and the data which comes back can be nonsense. And the worst part of it
is that you can take care to name your columns whatever you want, but the next
guy who comes along might have no way of knowing that he has to worry about
adding a column which will collide with your already-developed names.
[Matching Expression: �[1m�[34mselect *�[0m�[39m at line 1]


==================== Summary ===================
All Anti-Patterns and Hints  :: 1
>  High Risk   :: 1
>  Medium Risk :: 0
>  Low Risk    :: 0
>  Hints       :: 0

# Как выглядит файл с BNF грамматикой
!cat rules/filter2.lark

SELECT WILDCARD ANTIPATTERN :END_OF_COMMENT:
start: /./ start | pattern start | /./ | pattern
pattern: "SELECT *"i
%import common.INT
//%ignore " "
%ignore "\n"

# Использование отдельного модуля - Rules
!python -m swissql rule -r filter2.lark -q "SELECT * from x join y on x.id = y.id where x.id > 2"

[Finding rules using lark]
Rule filter2.lark found:
Positions: (1, 1)
Comment: SELECT WILDCARD ANTIPATTERN 

# Использование отдельного модуля - Swissql extractor
!python -m swissql extract -f examples/example3.py
[Extracting Spark SQLs from file using lark]
Found:
SELECT * FROM table1
SELECT * FROM table1 WHERE id = 1
SELECT * FROM table1 WHERE id = 1 AND name = "John" OR name = "Jane" OR name = "Jack"
SELECT * FROM table
select * from tableA