def test_select_count(engine): statement = s.Select( c.Select(f.Count(n.star)), c.From(e.Identifier('person')), c.GroupBy(e.Identifier('age')), ) assert statement.__sql__(engine) == Sql(''' SELECT COUNT(*) FROM "person" GROUP BY "age" '''.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_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_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_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_join_cross(engine): statement = s.Select( c.Select(n.star), c.From(m.Join( e.Identifier('person'), 'CROSS', e.Identifier('detail'), )), ) assert statement.__sql__(engine) == Sql(''' SELECT * FROM "person" CROSS JOIN "detail" '''.strip())
def test_select_join_natural(engine): statement = s.Select( c.Select(n.star), c.From(m.Join( e.Identifier('person'), 'NATURAL', e.Identifier('detail'), )), ) assert statement.__sql__(engine) == Sql(''' SELECT * FROM "person" NATURAL JOIN "detail" '''.strip())
def test_select_qualified_as(engine): statement = s.Select( c.Select( m.As(e.Identifier('person', 'person_id'), e.Identifier('id')), e.Identifier('person', 'name'), ), c.From(e.Identifier('person')), ) assert statement.__sql__(engine) == Sql( 'SELECT "person"."person_id" AS "id", "person"."name" FROM "person"' )
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_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_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_join_inner_on(engine): statement = s.Select( c.Select(n.star), c.From(m.Join( e.Identifier('person'), 'INNER', e.Identifier('detail'), c.On(e.Equal( e.Identifier('person', 'person_id'), e.Identifier('detail', 'person_id'), )), )), ) assert statement.__sql__(engine) == Sql( 'SELECT * FROM "person" ' 'INNER JOIN "detail" ON "person"."person_id" = "detail"."person_id"' )
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_select(engine): sql = s.Select( c.Select(e.Identifier('name')), c.From(e.Identifier('person')), ) assert sql.__sql__(engine) == Sql('SELECT "name" FROM "person"')
def test_select_from(engine): statement = s.Select(c.Select(n.star), c.From(e.Identifier('person'))) assert statement.__sql__(engine) == Sql('SELECT * FROM "person"')