diff options
Diffstat (limited to 'tests')
| -rw-r--r-- | tests/stdlib/tparsesql.nim | 438 |
1 files changed, 438 insertions, 0 deletions
diff --git a/tests/stdlib/tparsesql.nim b/tests/stdlib/tparsesql.nim new file mode 100644 index 000000000..e73a1d7ee --- /dev/null +++ b/tests/stdlib/tparsesql.nim @@ -0,0 +1,438 @@ +import unittest + +import sequtils +import strutils +import parsesql + +proc fold(str: string): string = + var + lines = str.split("\L") + minCount = 1000 + while lines.len > 0 and lines[0].strip().len == 0: + lines.delete(0, 0) + while lines.len > 0 and lines[lines.len-1].strip().len == 0: + lines.delete(lines.len, lines.len) + for line in lines: + var count = 0 + while line[count] == ' ': + inc count + if minCount > count: + minCount = count + for i, line in lines: + lines[i] = line[minCount..^1] + return lines.join("\L") + +proc parseCheck(have: string, need: string) = + var + sql = parseSQL(have) + sqlHave = renderSQL(sql, true).strip() + sqlNeed = need.fold().strip() + var + haveLines = sqlHave.split("\L") + needLines = sqlNeed.split("\L") + for i in 0..<haveLines.len: + if haveLines[i] != needLines[i]: + echo "" + echo " --- have --- " + echo sqlHave + #echo repr(sql) + echo " --- need --- " + echo sqlNeed + echo " --- lines --- " + echo repr haveLines[i] + echo repr needLines[i] + echo "line: ", i + raise newException(Exception, "Two don't equal.") + + +suite "sql": + + test "basic": + parseCheck "SELECT foo FROM table;", """ + SELECT + foo + FROM + table; + """ + + test "dont require ; at the end": + parseCheck "SELECT foo FROM table", """ + SELECT + foo + FROM + table; + """ + + test "limit": + parseCheck "SELECT foo FROM table limit 10", """ + SELECT + foo + FROM + table + LIMIT + 10; + """ + + test "fields": + parseCheck "SELECT foo, bar, baz FROM table limit 10", """ + SELECT + foo, + bar, + baz + FROM + table + LIMIT + 10; + """ + + test "as_field": + parseCheck "SELECT foo AS bar FROM table", """ + SELECT + foo AS bar + FROM + table; + """ + + parseCheck "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; + """ + + test "select *": + parseCheck "SELECT * FROM table", """ + SELECT + * + FROM + table; + """ + # TODO: COUNT(*) + #parseCheck "SELECT COUNT(*) FROM table", """ + # SELECT * + # FROM table; + #""" + + test "where": + parseCheck """ + SELECT * FROM table + WHERE a = b and c = d + """, """ + SELECT + * + FROM + table + WHERE + ((a = b) and (c = d)); + """ + + parseCheck """ + SELECT * FROM table + WHERE not b + """, """ + SELECT + * + FROM + table + WHERE + (not b); + """ + + parseCheck """ + SELECT + * + FROM + table + WHERE + a and not b + """, """ + SELECT + * + FROM + table + WHERE + (a and (not b)); + """ + + parseCheck """ + SELECT * + FROM table + WHERE not a and not b + """, """ + SELECT + * + FROM + table + WHERE + ((not a) and (not b)); + """ + + parseCheck """ + 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))); + """ + + test "having": + parseCheck """ + SELECT * FROM table + HAVING a = b and c = d + """, """ + SELECT + * + FROM + table + HAVING + ((a = b) and (c = d)); + """ + + test "group by": + parseCheck """ + SELECT a, b FROM table + GROUP BY a + """, """ + SELECT + a, + b + FROM + table + GROUP BY + a; + """ + + parseCheck """ + SELECT a, b FROM table + GROUP BY 1, 2 + """, """ + SELECT + a, + b + FROM + table + GROUP BY + 1, 2; + """ + + test "as table": + parseCheck """ + SELECT t.a FROM t as t + """, """ + SELECT + t.a + FROM + t AS t; + """ + + test "subselects": + parseCheck """ + SELECT a, b FROM ( + SELECT * FROM t + ) + """, """ + SELECT + a, + b + FROM + ( + SELECT + * + FROM + t + ); + """ + + parseCheck """ + SELECT a, b FROM ( + SELECT * FROM t + ) as foo + """, """ + SELECT + a, + b + FROM + ( + SELECT + * + FROM + t + ) AS foo; + """ + + parseCheck """ + SELECT a, b FROM ( + SELECT * FROM ( + SELECT * FROM ( + SELECT * FROM ( + SELECT * FROM inner as inner1 + ) as inner2 + ) as inner3 + ) as inner4 + ) as inner5 + """, """ + SELECT + a, + b + FROM + ( + SELECT + * + FROM + ( + SELECT + * + FROM + ( + SELECT + * + FROM + ( + SELECT + * + FROM + inner AS inner1 + ) AS inner2 + ) AS inner3 + ) AS inner4 + ) AS inner5; + """ + + parseCheck """ + 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 + ); + """ + + test "original test": + parseCheck """ + 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'); + """ + + test "joins": + parseCheck """ + SELECT id FROM a + JOIN b + ON a.id == b.id + """, """ + SELECT + id + FROM + a + JOIN + b + ON + (a.id == b.id); + """ + + parseCheck """ + 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); + """ + + parseCheck """ + SELECT id FROM a + INNER JOIN b + ON a.id == b.id + """, """ + SELECT + id + FROM + a + INNER JOIN + b + ON + (a.id == b.id); + """ + + parseCheck """ + SELECT id FROM a + OUTER JOIN b + ON a.id == b.id + """, """ + SELECT + id + FROM + a + OUTER JOIN + b + ON + (a.id == b.id); + """ + + parseCheck """ + SELECT id FROM a + CROSS JOIN b + ON a.id == b.id + """, """ + SELECT + id + FROM + a + CROSS JOIN + b + ON + (a.id == b.id); + """ |