def test_alias_with_coerce(self):
UpperModel.create(data='test')
um = UpperModel.get()
self.assertEqual(um.data, 'TEST')
Alias = UpperModel.alias()
normal = (UpperModel.data == 'foo')
aliased = (Alias.data == 'foo')
_, normal_p = compiler.parse_node(normal)
_, aliased_p = compiler.parse_node(aliased)
self.assertEqual(normal_p, ['FOO'])
self.assertEqual(aliased_p, ['FOO'])
expected = ('SELECT "uppermodel"."id", "uppermodel"."data" '
'FROM "uppermodel" AS uppermodel '
'WHERE ("uppermodel"."data" = ?)')
query = UpperModel.select().where(UpperModel.data == 'foo')
sql, params = compiler.generate_select(query)
self.assertEqual(sql, expected)
self.assertEqual(params, ['FOO'])
query = Alias.select().where(Alias.data == 'foo')
sql, params = compiler.generate_select(query)
self.assertEqual(sql, expected)
self.assertEqual(params, ['FOO'])
def test_alias_with_coerce(self):
UpperModel.create(data='test')
um = UpperModel.get()
self.assertEqual(um.data, 'TEST')
Alias = UpperModel.alias()
normal = (UpperModel.data == 'foo')
aliased = (Alias.data == 'foo')
_, normal_p = compiler.parse_node(normal)
_, aliased_p = compiler.parse_node(aliased)
self.assertEqual(normal_p, ['FOO'])
self.assertEqual(aliased_p, ['FOO'])
expected = (
'SELECT "uppermodel"."id", "uppermodel"."data" '
'FROM "uppermodel" AS uppermodel '
'WHERE ("uppermodel"."data" = ?)')
query = UpperModel.select().where(UpperModel.data == 'foo')
sql, params = compiler.generate_select(query)
self.assertEqual(sql, expected)
self.assertEqual(params, ['FOO'])
query = Alias.select().where(Alias.data == 'foo')
sql, params = compiler.generate_select(query)
self.assertEqual(sql, expected)
self.assertEqual(params, ['FOO'])
def test_union_sql(self):
union = (User.select(User.username) |
UniqueModel.select(UniqueModel.name))
sql, params = compiler.generate_select(union)
self.assertEqual(sql, (
'SELECT "users"."username" FROM "users" AS users UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel'))
def test_union_sql(self):
union = (User.select(User.username)
| UniqueModel.select(UniqueModel.name))
sql, params = compiler.generate_select(union)
self.assertEqual(
sql,
('SELECT "users"."username" FROM "users" AS users UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel'))
def test_subselect_with_join(self):
inner = User.select(User.id, User.username).alias('q1')
outer = (Blog.select(inner.c.id, inner.c.username).from_(inner).join(
Comment, on=(inner.c.id == Comment.id)))
sql, params = compiler.generate_select(outer)
self.assertEqual(sql, (
'SELECT "q1"."id", "q1"."username" FROM ('
'SELECT "users"."id", "users"."username" FROM "users" AS users) AS q1 '
'INNER JOIN "comment" AS comment ON ("q1"."id" = "comment"."id")'))
def test_union_subquery(self):
union = (User.select(User.username) |
UniqueModel.select(UniqueModel.name))
query = User.select().where(User.username << union)
sql, params = compiler.generate_select(query)
self.assertEqual(sql, (
'SELECT "users"."id", "users"."username" '
'FROM "users" AS users WHERE ("users"."username" IN '
'(SELECT "users"."username" FROM "users" AS users UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel))'))
def test_subselect_with_column(self):
inner = User.select(User.username.alias('name')).alias('t1')
outer = (User.select(inner.c.name).from_(inner))
sql, params = compiler.generate_select(outer)
self.assertEqual(sql, (
'SELECT "t1"."name" FROM '
'(SELECT "users"."username" AS name FROM "users" AS users) AS t1'))
query = outer.order_by(inner.c.name.desc())
self.assertEqual([u[0] for u in query.tuples()], ['u1', 'u0'])
def test_union_subquery(self):
union = (User.select(User.username)
| UniqueModel.select(UniqueModel.name))
query = User.select().where(User.username << union)
sql, params = compiler.generate_select(query)
self.assertEqual(sql, (
'SELECT "users"."id", "users"."username" '
'FROM "users" AS users WHERE ("users"."username" IN '
'(SELECT "users"."username" FROM "users" AS users UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel))'))
def test_subselect_with_join(self):
inner = User.select(User.id, User.username).alias('q1')
outer = (Blog
.select(inner.c.id, inner.c.username)
.from_(inner)
.join(Comment, on=(inner.c.id == Comment.id)))
sql, params = compiler.generate_select(outer)
self.assertEqual(sql, (
'SELECT "q1"."id", "q1"."username" FROM ('
'SELECT "users"."id", "users"."username" FROM "users" AS users) AS q1 '
'INNER JOIN "comment" AS comment ON ("q1"."id" = "comment"."id")'))
def test_subselect_with_column(self):
inner = User.select(User.username.alias('name')).alias('t1')
outer = (User
.select(inner.c.name)
.from_(inner))
sql, params = compiler.generate_select(outer)
self.assertEqual(sql, (
'SELECT "t1"."name" FROM '
'(SELECT "users"."username" AS name FROM "users" AS users) AS t1'))
query = outer.order_by(inner.c.name.desc())
self.assertEqual([u[0] for u in query.tuples()], ['u1', 'u0'])
def test_subselect(self):
inner = User.select(User.username)
self.assertEqual([u.username for u in inner.order_by(User.username)],
['u0', 'u1'])
# Have to manually specify the alias as "t1" because the outer query
# will expect that.
outer = (User.select(User.username).from_(inner.alias('t1')))
sql, params = compiler.generate_select(outer)
self.assertEqual(
sql, ('SELECT "users"."username" FROM '
'(SELECT "users"."username" FROM "users" AS users) AS t1'))
self.assertEqual([u.username for u in outer.order_by(User.username)],
['u0', 'u1'])
def test_subselect(self):
inner = User.select(User.username)
self.assertEqual(
[u.username for u in inner.order_by(User.username)], ['u0', 'u1'])
# Have to manually specify the alias as "t1" because the outer query
# will expect that.
outer = (User
.select(User.username)
.from_(inner.alias('t1')))
sql, params = compiler.generate_select(outer)
self.assertEqual(sql, (
'SELECT "users"."username" FROM '
'(SELECT "users"."username" FROM "users" AS users) AS t1'))
self.assertEqual(
[u.username for u in outer.order_by(User.username)], ['u0', 'u1'])
def test_union_from(self):
uq = User.select(User.username).where(User.username << ['a', 'b', 'd'])
oq = (OrderedModel.select(OrderedModel.title).where(
OrderedModel.title << ['a', 'b']).order_by())
iq = UniqueModel.select(UniqueModel.name)
union = uq | oq | iq
query = User.select(SQL('1')).from_(union)
sql, params = compiler.generate_select(query)
self.assertEqual(sql, (
'SELECT 1 FROM ('
'SELECT "users"."username" FROM "users" AS users '
'WHERE ("users"."username" IN (?, ?, ?)) '
'UNION '
'SELECT "orderedmodel"."title" FROM "orderedmodel" AS orderedmodel'
' WHERE ("orderedmodel"."title" IN (?, ?)) '
'UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel'
')'))
self.assertEqual(params, ['a', 'b', 'd', 'a', 'b'])
def test_union_from(self):
uq = User.select(User.username).where(User.username << ['a', 'b', 'd'])
oq = (OrderedModel
.select(OrderedModel.title)
.where(OrderedModel.title << ['a', 'b'])
.order_by())
iq = UniqueModel.select(UniqueModel.name)
union = uq | oq | iq
query = User.select(SQL('1')).from_(union)
sql, params = compiler.generate_select(query)
self.assertEqual(sql, (
'SELECT 1 FROM ('
'SELECT "users"."username" FROM "users" AS users '
'WHERE ("users"."username" IN (?, ?, ?)) '
'UNION '
'SELECT "orderedmodel"."title" FROM "orderedmodel" AS orderedmodel'
' WHERE ("orderedmodel"."title" IN (?, ?)) '
'UNION '
'SELECT "uniquemodel"."name" FROM "uniquemodel" AS uniquemodel'
')'))
self.assertEqual(params, ['a', 'b', 'd', 'a', 'b'])
