def test_parse_create_table_statement_with_generated_clause(self):
# Regression test for https://github.com/iafisher/sqliteparser/issues/9
sql = "create table t2 (c1 text, c2 text generated always as (upper(c1)));"
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="t2",
columns=[
ast.Column(
name="c1",
definition=ast.ColumnDefinition(type="text"),
),
ast.Column(
name="c2",
definition=ast.ColumnDefinition(
type="text",
constraints=[
ast.GeneratedColumnConstraint(
ast.Call(
ast.Identifier("upper"),
[ast.Identifier("c1")],
))
],
),
),
],
)
],
)
sql = "create table t1 (c1 text,c2 generated always as (2+2));"
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="t1",
columns=[
ast.Column(
name="c1",
definition=ast.ColumnDefinition(type="text"),
),
ast.Column(
name="c2",
definition=ast.ColumnDefinition(
type=None,
constraints=[
ast.GeneratedColumnConstraint(
ast.Infix("+", ast.Integer(2),
ast.Integer(2)))
],
),
),
],
)
],
)
def test_parse_comparisons(self):
self.assertEqual(
parse("SELECT 0 < x OR 20 >= x"),
[
ast.SelectStatement(columns=[
ast.Infix(
"OR",
ast.Infix("<", ast.Integer(0), ast.Identifier("x")),
ast.Infix(">=", ast.Integer(20), ast.Identifier("x")),
)
])
],
)
def test_parse_AND(self):
self.assertEqual(
parse('SELECT ("size" >= 1) AND ("size" <= 4)'),
[
ast.SelectStatement(
columns=[
ast.Infix(
"AND",
ast.Infix(">=", ast.Identifier("size"), ast.Integer(1)),
ast.Infix("<=", ast.Identifier("size"), ast.Integer(4)),
)
]
)
],
)
def test_parse_create_table_statement_with_simple_check_constraint(self):
sql = """
CREATE TABLE people(
name TEXT CHECK(name != '')
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="name",
definition=ast.ColumnDefinition(
type="TEXT",
constraints=[
ast.CheckConstraint(
ast.Infix(
"!=",
ast.Identifier("name"),
ast.String(""),
))
],
),
),
],
),
],
)
def test_parse_column_with_constraints(self):
self.assertEqual(
parse_column("id INTEGER PRIMARY KEY"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.PrimaryKeyConstraint()]),
),
)
self.assertEqual(
parse_column("id INTEGER UNIQUE"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.UniqueConstraint()]),
),
)
self.assertEqual(
parse_column("id INTEGER not NULL"),
ast.Column(
name="id",
definition=ast.ColumnDefinition(
type="INTEGER", constraints=[ast.NotNullConstraint()]),
),
)
self.assertEqual(
parse_column("model INTEGER REFERENCES models"),
ast.Column(
name="model",
definition=ast.ColumnDefinition(
type="INTEGER",
constraints=[
ast.ForeignKeyConstraint(columns=[],
foreign_table="models",
foreign_columns=[])
],
),
),
)
self.assertEqual(
parse_column("name TEXT NOT NULL check(name != '')"),
ast.Column(
name="name",
definition=ast.ColumnDefinition(
type="TEXT",
constraints=[
ast.NotNullConstraint(),
ast.CheckConstraint(
ast.Infix("!=", ast.Identifier("name"),
ast.String(""))),
],
),
),
)
def test_parse_function(self):
self.assertEqual(
parse("SELECT foo(1, 2) + bar(3, baz(4))"),
[
ast.SelectStatement(columns=[
ast.Infix(
"+",
ast.Call(
ast.Identifier("foo"),
[ast.Integer(1), ast.Integer(2)]),
ast.Call(
ast.Identifier("bar"),
[
ast.Integer(3),
ast.Call(ast.Identifier("baz"),
[ast.Integer(4)]),
],
),
)
])
],
)
def test_parse_special_functions(self):
self.assertEqual(
parse("SELECT count(*)"),
[
ast.SelectStatement(
columns=[ast.Call(ast.Identifier("count"), [], star=True)])
],
)
self.assertEqual(
parse("SELECT count(DISTINCT x)"),
[
ast.SelectStatement(columns=[
ast.Call(
ast.Identifier("count"),
[ast.Identifier("x")],
star=False,
distinct=True,
)
])
],
)