def test_delete(engine):
statement = s.Delete(
c.DeleteFrom(e.Identifier('person')),
c.Where(e.Equal(e.Identifier('person_id'), 'mosky')),
)
assert statement.__sql__(engine) == Sql('''
DELETE FROM "person" WHERE "person_id" = 'mosky'
'''.strip())
def test_where_and(engine):
sql = c.Where(
e.And(
e.Like(e.Identifier('name'), 'Mo%'),
e.GreaterThan(e.Identifier('age'), 18),
))
assert sql.__sql__(engine) == Sql('''
WHERE "name" LIKE 'Mo%' AND "age" > 18
'''.strip())
def test_select_where_equal(engine):
statement = s.Select(
c.Select(e.Identifier('person_id')),
c.From(e.Identifier('person')),
c.Where(e.Equal(e.Identifier('person_id'), 'mosky')),
)
assert statement.__sql__(engine) == Sql('''
SELECT "person_id" FROM "person" WHERE "person_id" = 'mosky'
'''.strip())
def test_update(engine):
statement = s.Update(
c.Update(e.Identifier('person')),
c.Set(m.Assign(e.Identifier('name'), 'Mosky Liu')),
c.Where(e.Equal(e.Identifier('person_id'), 'mosky')),
)
assert statement.__sql__(engine) == Sql('''
UPDATE "person" SET "name" = 'Mosky Liu' WHERE "person_id" = 'mosky'
'''.strip())
def test_select_where_false(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(False),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE FALSE
'''.strip())
def test_select_where_is_null(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(e.Equal(e.Identifier('name'), None)),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE "name" IS NULL
'''.strip())
def test_select_where_like(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(e.Like(e.Identifier('name'), 'Mosky%')),
c.Limit(3), c.Offset(1),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' LIMIT 3 OFFSET 1
'''.strip())
def test_select_order_by_desc(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(e.Like(e.Identifier('name'), 'Mosky%')),
c.OrderBy(m.Ordering(e.Identifier('age'), 'desc')),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age" DESC
'''.strip())
def test_select_where_in(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(e.In(
e.Identifier('person_id'),
m.List('andy', 'bob'),
)),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE "person_id" IN ('andy', 'bob')
'''.strip())
def test_select_where_greater_than_like(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('person')),
c.Where(e.And(
e.GreaterThan(e.Identifier('age'), 20),
e.Like(e.Identifier('name'), 'Mosky%'),
)),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'
'''.strip())
def test_select_where_condition(engine):
sql = s.Select(
c.Select(e.Identifier('name')),
c.From(e.Identifier('person')),
c.Where(
e.Or(
e.Like(e.Identifier('name'), 'Mo%'),
e.GreaterThanOrEqual(e.Identifier('age'), 18),
)),
)
assert sql.__sql__(engine) == Sql('''
SELECT "name" FROM "person" WHERE "name" LIKE 'Mo%' OR "age" >= 18
'''.strip())
def test_select_param(engine):
statement = s.Select(
c.Select(n.star),
c.From(e.Identifier('table')),
c.Where(e.And(
e.Equal(e.Identifier('auto_param'), e.Parameter('auto_param')),
e.Equal(e.Identifier('using_alias'), e.Parameter('using_alias')),
e.Equal(e.Identifier('custom_param'), e.Parameter('my_param')),
)),
)
assert statement.__sql__(engine) == Sql(
'SELECT * FROM "table" '
'WHERE "auto_param" = %(auto_param)s '
'AND "using_alias" = %(using_alias)s '
'AND "custom_param" = %(my_param)s'
)
def test_select_where(engine):
sql = s.Select(c.Select(e.Identifier('name')),
c.From(e.Identifier('person')), c.Where(False))
assert sql.__sql__(engine) == Sql('''
SELECT "name" FROM "person" WHERE FALSE
'''.strip())
def test_where(engine):
sql = c.Where(True)
assert sql.__sql__(engine) == Sql('WHERE TRUE')