1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
discard """
targets: "c js"
"""
import parsesql
doAssert $parseSQL("SELECT foo FROM table;") == "select foo from table;"
doAssert $parseSQL("""
SELECT
CustomerName,
ContactName,
Address,
City,
PostalCode,
Country,
CustomerName,
ContactName,
Address,
City,
PostalCode,
Country
FROM table;""") == "select CustomerName, ContactName, Address, City, PostalCode, Country, CustomerName, ContactName, Address, City, PostalCode, Country from table;"
doAssert $parseSQL("SELECT foo FROM table limit 10") == "select foo from table limit 10;"
doAssert $parseSQL("SELECT foo, bar, baz FROM table limit 10") == "select foo, bar, baz from table limit 10;"
doAssert $parseSQL("SELECT foo AS bar FROM table") == "select foo as bar from table;"
doAssert $parseSQL("SELECT foo AS foo_prime, bar AS bar_prime, baz AS baz_prime FROM table") == "select foo as foo_prime, bar as bar_prime, baz as baz_prime from table;"
doAssert $parseSQL("SELECT * FROM table") == "select * from table;"
doAssert $parseSQL("SELECT count(*) FROM table") == "select count(*) from table;"
doAssert $parseSQL("SELECT count(*) as 'Total' FROM table") == "select count(*) as 'Total' from table;"
doAssert $parseSQL("SELECT count(*) as 'Total', sum(a) as 'Aggr' FROM table") == "select count(*) as 'Total', sum(a) as 'Aggr' from table;"
doAssert $parseSQL("""
SELECT * FROM table
WHERE a = b and c = d
""") == "select * from table where a = b and c = d;"
doAssert $parseSQL("""
SELECT * FROM table
WHERE not b
""") == "select * from table where not b;"
doAssert $parseSQL("""
SELECT
*
FROM
table
WHERE
a and not b
""") == "select * from table where a and not b;"
doAssert $parseSQL("""
SELECT * FROM table
ORDER BY 1
""") == "select * from table order by 1;"
doAssert $parseSQL("""
SELECT * FROM table
GROUP BY 1
ORDER BY 1
""") == "select * from table group by 1 order by 1;"
doAssert $parseSQL("""
SELECT * FROM table
ORDER BY 1
LIMIT 100
""") == "select * from table order by 1 limit 100;"
doAssert $parseSQL("""
SELECT * FROM table
WHERE a = b and c = d or n is null and not b + 1 = 3
""") == "select * from table where a = b and c = d or n is null and not b + 1 = 3;"
doAssert $parseSQL("""
SELECT * FROM table
WHERE (a = b and c = d) or (n is null and not b + 1 = 3)
""") == "select * from table where(a = b and c = d) or (n is null and not b + 1 = 3);"
doAssert $parseSQL("""
SELECT * FROM table
HAVING a = b and c = d
""") == "select * from table having a = b and c = d;"
doAssert $parseSQL("""
SELECT a, b FROM table
GROUP BY a
""") == "select a, b from table group by a;"
doAssert $parseSQL("""
SELECT a, b FROM table
GROUP BY 1, 2
""") == "select a, b from table group by 1, 2;"
doAssert $parseSQL("SELECT t.a FROM t as t") == "select t.a from t as t;"
doAssert $parseSQL("""
SELECT a, b FROM (
SELECT * FROM t
)
""") == "select a, b from(select * from t);"
doAssert $parseSQL("""
SELECT a, b FROM (
SELECT * FROM t
) as foo
""") == "select a, b from(select * from t) as foo;"
doAssert $parseSQL("""
SELECT a, b FROM (
SELECT * FROM (
SELECT * FROM (
SELECT * FROM (
SELECT * FROM innerTable as inner1
) as inner2
) as inner3
) as inner4
) as inner5
""") == "select a, b from(select * from(select * from(select * from(select * from innerTable as inner1) as inner2) as inner3) as inner4) as inner5;"
doAssert $parseSQL("""
SELECT a, b FROM
(SELECT * FROM a),
(SELECT * FROM b),
(SELECT * FROM c)
""") == "select a, b from(select * from a),(select * from b),(select * from c);"
doAssert $parseSQL("""
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
""") == "select * from Products where Price between 10 and 20;"
doAssert $parseSQL("""
SELECT id FROM a
JOIN b
ON a.id == b.id
""") == "select id from a join b on a.id == b.id;"
doAssert $parseSQL("""
SELECT id FROM a
JOIN (SELECT id from c) as b
ON a.id == b.id
""") == "select id from a join(select id from c) as b on a.id == b.id;"
doAssert $parseSQL("""
SELECT id FROM a
INNER JOIN b
ON a.id == b.id
""") == "select id from a inner join b on a.id == b.id;"
doAssert $parseSQL("""
SELECT id FROM a
OUTER JOIN b
ON a.id == b.id
""") == "select id from a outer join b on a.id == b.id;"
doAssert $parseSQL("""
SELECT id FROM a
CROSS JOIN b
ON a.id == b.id
""") == "select id from a cross join b on a.id == b.id;"
doAssert $parseSQL("""
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
num_weeks int,
happiness happiness
);
CREATE INDEX table1_attr1 ON table1(attr1);
SELECT * FROM myTab WHERE col1 = 'happy';
""") == "create type happiness as enum ('happy' , 'very happy' , 'ecstatic' ); create table holidays(num_weeks int , happiness happiness );; create index table1_attr1 on table1(attr1 );; select * from myTab where col1 = 'happy';"
doAssert $parseSQL("""
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
""") == "insert into Customers (CustomerName , ContactName , Address , City , PostalCode , Country ) values ('Cardinal' , 'Tom B. Erichsen' , 'Skagen 21' , 'Stavanger' , '4006' , 'Norway' );"
doAssert $parseSQL("""
INSERT INTO TableName DEFAULT VALUES
""") == "insert into TableName default values;"
doAssert $parseSQL("""
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
""") == "update Customers set ContactName = 'Alfred Schmidt' , City = 'Frankfurt' where CustomerID = 1;"
doAssert $parseSQL("DELETE FROM table_name;") == "delete from table_name;"
doAssert $parseSQL("DELETE * FROM table_name;") == "delete from table_name;"
doAssert $parseSQL("""
--Select all:
SELECT * FROM Customers;
""") == "select * from Customers;"
doAssert $parseSQL("""
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;
""") == "select * from Customers where(CustomerName like 'L%' or CustomerName like 'R%' or CustomerName like 'W%') and Country = 'USA' order by CustomerName;"
# parse quoted keywords as identifires
doAssert $parseSQL("""
SELECT `SELECT`, `FROM` as `GROUP` FROM `WHERE`;
""") == """select "SELECT", "FROM" as "GROUP" from "WHERE";"""
doAssert $parseSQL("""
SELECT "SELECT", "FROM" as "GROUP" FROM "WHERE";
""") == """select "SELECT", "FROM" as "GROUP" from "WHERE";"""
|