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_simple_arithmetic_with_precedence(self):
self.assertEqual(
parse("SELECT 1+2*3"),
[
ast.SelectStatement(columns=[
ast.Infix(
"+",
ast.Integer(1),
ast.Infix("*", ast.Integer(2), ast.Integer(3)),
)
])
],
)
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_generated_column_constraint(
self):
sql = """
CREATE TABLE people(
age INTEGER GENERATED ALWAYS AS ( 2 + 2 ) STORED,
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="age",
definition=ast.ColumnDefinition(
type="INTEGER",
constraints=[
ast.GeneratedColumnConstraint(
ast.Infix("+", ast.Integer(2),
ast.Integer(2)),
storage=ast.GeneratedColumnStorage.
STORED,
)
],
),
),
],
),
],
)
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_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_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_string_concatenation(self):
self.assertEqual(
parse("SELECT 'a' || 'b'"),
[
ast.SelectStatement(columns=[
ast.Infix("||", ast.String("a"), ast.String("b"))
])
],
)
def test_parse_expression_list(self):
self.assertEqual(
parse("SELECT 1 IN (1, 2, 3)"),
[
ast.SelectStatement(columns=[
ast.Infix(
"IN",
ast.Integer(1),
ast.ExpressionList(
[ast.Integer(1),
ast.Integer(2),
ast.Integer(3)]),
)
])
],
)
def test_parse_create_table_statement_with_default_clause(self):
sql = """
CREATE TABLE people(
name TEXT DEFAULT '',
age INTEGER DEFAULT ( 2 + 2 ),
employed BOOLEAN DEFAULT TRUE,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""
self.assertEqual(
parse(sql),
[
ast.CreateTableStatement(
name="people",
columns=[
ast.Column(
name="name",
definition=ast.ColumnDefinition(
type="TEXT", default=ast.String("")),
),
ast.Column(
name="age",
definition=ast.ColumnDefinition(
type="INTEGER",
default=ast.Infix("+", ast.Integer(2),
ast.Integer(2)),
),
),
ast.Column(
name="employed",
definition=ast.ColumnDefinition(
type="BOOLEAN", default=ast.Boolean(True)),
),
ast.Column(
name="last_updated",
definition=ast.ColumnDefinition(
type="TIMESTAMP",
default=ast.DefaultValue.CURRENT_TIMESTAMP,
),
),
],
),
],
)
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)]),
],
),
)
])
],
)
