def test_insert(engine):
statement = s.Insert(
c.InsertInto(e.Identifier('person')),
c.Columns(m.List(e.Identifier('person_id'), e.Identifier('name'))),
c.Values(m.List('mosky', 'Mosky Liu')),
)
assert statement.__sql__(engine) == Sql(
"""INSERT INTO "person" ("person_id", "name") """
"""VALUES ('mosky', 'Mosky Liu')"""
)
def test_insert_values_multiple(engine):
statement = s.Insert(
c.InsertInto(e.Identifier('person')),
c.Values(
m.List('mosky', 'Mosky Liu'),
m.List('yiyu', 'Yi-Yu Liu'),
),
)
assert statement.__sql__(engine) == Sql(
'INSERT INTO "person" '
"VALUES ('mosky', 'Mosky Liu'), ('yiyu', 'Yi-Yu Liu')"
)
def test_insert_no_values(engine):
statement = s.Insert(
c.InsertInto(e.Identifier('person')),
c.Values(m.List()),
)
assert statement.__sql__(engine) == Sql('''
INSERT INTO "person" VALUES ()
'''.strip())
def test_select_join_right_using(engine):
statement = s.Select(
c.Select(n.star),
c.From(m.Join(
e.Identifier('person'), 'RIGHT', e.Identifier('detail'),
c.Using(m.List(e.Identifier('person_id'))),
)),
)
assert statement.__sql__(engine) == Sql('''
SELECT * FROM "person" RIGHT JOIN "detail" USING ("person_id")
'''.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_subquery_as(engine):
sql = s.Select(
c.Select(
e.Identifier('t', n.star),
m.As(e.Add(e.Identifier('a'), e.Identifier('b')),
e.Identifier('total')),
),
c.From(
m.As(
m.List(
s.Select(
c.Select(
m.As(f.Sum(e.Identifier('c1')), e.Identifier('a')),
m.As(f.Sum(e.Identifier('c2')), e.Identifier('b')),
),
c.From(e.Identifier('table')),
), ), e.Identifier('t'))),
)
assert sql.__sql__(engine) == Sql(
'SELECT "t".*, "a" + "b" AS "total" FROM ('
'SELECT SUM("c1") AS "a", SUM("c2") AS "b" FROM "table"'
') AS "t"')
def test_list_mixed(engine):
sql = m.List(e.Identifier('name'), n.star, 'Mosky')
assert sql.__sql__(engine) == Sql('''
("name", *, 'Mosky')
'''.strip())
def test_list_identifier(engine):
sql = m.List(e.Identifier('name'), e.Identifier('age'))
assert sql.__sql__(engine) == Sql('("name", "age")')
def test_list(engine):
sql = m.List('Mosky', 'TP')
assert sql.__sql__(engine) == Sql("('Mosky', 'TP')")
def test_values_list(engine):
sql = c.Values(m.List('Mosky', 42, e.Identifier('person', 'name')))
assert sql.__sql__(engine) == Sql('''
VALUES ('Mosky', 42, "person"."name")
'''.strip())