def test_where(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(
compile(q),
('SELECT * FROM "author"', [])
)
q = q.where(T.author.is_staff.is_(True))
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."is_staff" IS %s', [True])
)
q = q.where(T.author.first_name == 'John')
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s', [True, 'John'])
)
q = q.where(T.author.last_name == 'Smith', op=operator.or_)
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s', [True, 'John', 'Smith'])
)
q = q.where(T.author.last_name == 'Smith', op=None)
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."last_name" = %s', ['Smith'])
)
def test_distinct_bool(self):
q = Q().fields('*').tables(T.author)
self.assertEqual(
compile(q),
('SELECT * FROM "author"', [])
)
self.assertFalse(
q.distinct()
)
q = q.distinct(True)
self.assertEqual(
compile(q),
('SELECT DISTINCT * FROM "author"', [])
)
self.assertTrue(
q.distinct()[0]
)
self.assertEqual(
compile(q.distinct(False)),
('SELECT * FROM "author"', [])
)
self.assertEqual(
compile(q),
('SELECT DISTINCT * FROM "author"', [])
)
def test_having(self):
q = Q().fields('*').tables(T.author).group_by(T.author.status)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status"', [])
)
q = q.having(T.author.is_staff.is_(True))
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s', [True])
)
q = q.having(T.author.first_name == 'John')
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s', [True, 'John'])
)
q = q.having(T.author.last_name == 'Smith', op=operator.or_)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s', [True, 'John', 'Smith'])
)
q = q.having(T.author.last_name == 'Smith', op=None)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."last_name" = %s', ['Smith'])
)
def test_smartsql(self):
author_mapper, book_mapper = mapper_registry[Author], mapper_registry[Book]
slww = self.data["slww"]
fields = [smartsql.compile(i)[0] for i in author_mapper.get_sql_fields()]
self.assertListEqual(
fields,
[
'"ascetic_tests_author"."id"',
'"ascetic_tests_author"."first_name"',
'"ascetic_tests_author"."last_name"',
'"ascetic_tests_author"."bio"',
],
)
self.assertEqual(smartsql.compile(book_mapper.sql_table.author)[0], '"books"."author_id"')
q = author_mapper.query
self.assertEqual(
smartsql.compile(q)[0],
'''SELECT "ascetic_tests_author"."id", "ascetic_tests_author"."first_name", "ascetic_tests_author"."last_name", "ascetic_tests_author"."bio" FROM "ascetic_tests_author"''',
)
self.assertEqual(len(q), 3)
for obj in q:
self.assertTrue(isinstance(obj, Author))
q = q.where(author_mapper.sql_table.id == slww.author_id)
self.assertEqual(
smartsql.compile(q)[0],
"""SELECT "ascetic_tests_author"."id", "ascetic_tests_author"."first_name", "ascetic_tests_author"."last_name", "ascetic_tests_author"."bio" FROM "ascetic_tests_author" WHERE "ascetic_tests_author"."id" = %s""",
)
self.assertEqual(len(q), 1)
self.assertTrue(isinstance(q[0], Author))
def test_tables(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(
compile(q),
('SELECT * FROM "author"', [])
)
q = q.tables(T.author.as_('author_alias'))
self.assertEqual(
compile(q),
('SELECT * FROM "author" AS "author_alias"', [])
)
self.assertEqual(
type(q.tables()),
TableAlias
)
self.assertEqual(
compile(TableJoin(q.tables())),
('"author" AS "author_alias"', [])
)
self.assertEqual(
compile(q.tables((q.tables() + T.book).on(T.book.author_id == T.author.as_('author_alias').id))),
('SELECT * FROM "author" AS "author_alias" LEFT OUTER JOIN "book" ON ("book"."author_id" = "author_alias"."id")', [])
)
self.assertEqual(
compile(q),
('SELECT * FROM "author" AS "author_alias"', [])
)
def test_distinct_bool(self):
q = Q().fields('*').tables(T.author)
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
self.assertFalse(q.distinct())
q = q.distinct(True)
self.assertEqual(compile(q), ('SELECT DISTINCT * FROM "author"', []))
self.assertTrue(q.distinct()[0])
self.assertEqual(compile(q.distinct(False)),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q), ('SELECT DISTINCT * FROM "author"', []))
def test_tablealias(self):
table = Book.s.as_('book_alias')
self.assertIsInstance(table, TableAlias)
self.assertIsInstance(table.pk, Field)
self.assertIsInstance(table.title, Field)
self.assertIsInstance(table.author, Field)
self.assertEqual(compile(table.pk), ('"book_alias"."id"', []))
self.assertEqual(compile(table.title), ('"book_alias"."title"', []))
self.assertEqual(compile(table.author),
('"book_alias"."author_id"', []))
def test_model(self):
self.assertIsInstance(Author.first_name, Field)
self.assertEqual(compile(Author), ('"author"', []))
self.assertEqual(compile(Author.first_name),
('"author"."first_name"', []))
self.assertEqual(
compile(
(TableJoin(Author) & Post).on(Post.author_id == Author.id)),
('"author" INNER JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(Join(Author, Post, on=(Post.author_id == Author.id))),
('"author" JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(InnerJoin(Author, Post, on=(Post.author_id == Author.id))),
('"author" INNER JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(LeftJoin(Author, Post, on=(Post.author_id == Author.id))),
('"author" LEFT OUTER JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(RightJoin(Author, Post, on=(Post.author_id == Author.id))),
('"author" RIGHT OUTER JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(FullJoin(Author, Post, on=(Post.author_id == Author.id))),
('"author" FULL OUTER JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile(CrossJoin(Author, Post, on=(Post.author_id == Author.id))),
('"author" CROSS JOIN "post" ON ("post"."author_id" = "author"."id")',
[]))
def test_table(self):
table = Book.s
self.assertIsInstance(table, Table)
self.assertIsInstance(table.pk, Field)
self.assertIsInstance(table.title, Field)
self.assertIsInstance(table.author, Field)
self.assertEqual(compile(table.pk), ('"sqlbuilder_book"."id"', []))
self.assertEqual(compile(table.title),
('"sqlbuilder_book"."title"', []))
self.assertEqual(compile(table.author),
('"sqlbuilder_book"."author_id"', []))
def test_issue_20(self):
t1, t2 = T.tb1, T.tb2
tj = t2.on(t1.id == t2.id)
self.assertEqual(compile(tj),
('"tb2" ON ("tb1"."id" = "tb2"."id")', []))
self.assertEqual(
compile(t1 + tj),
('"tb1" LEFT OUTER JOIN "tb2" ON ("tb1"."id" = "tb2"."id")', []))
self.assertEqual(
compile(t1 + tj),
('"tb1" LEFT OUTER JOIN "tb2" ON ("tb1"."id" = "tb2"."id")', []))
def test_join_priorities(self):
t1, t2, t3, t4, t5 = T.t1, T.t2, T.t3, T.t4, T.t5
self.assertEqual(
compile(t1 | t2.on(t2.t1_id == t1.id) * t3.on(t3.t1_id == t1.id) +
t4.on(t4.t1_id == t1.id) - t5.on(t5.t1_id == t5.id)),
('"t1" FULL OUTER JOIN "t2" ON ("t2"."t1_id" = "t1"."id") CROSS JOIN "t3" ON ("t3"."t1_id" = "t1"."id") LEFT OUTER JOIN "t4" ON ("t4"."t1_id" = "t1"."id") RIGHT OUTER JOIN "t5" ON ("t5"."t1_id" = "t5"."id")',
[]))
self.assertEqual(
compile((((
(t1 | t2).on(t2.t1_id == t1.id) * t3).on(t3.t1_id == t1.id) +
t4).on(t4.t1_id == t1.id) - t5.on(t5.t1_id == t5.id))),
('"t1" FULL OUTER JOIN "t2" ON ("t2"."t1_id" = "t1"."id") CROSS JOIN "t3" ON ("t3"."t1_id" = "t1"."id") LEFT OUTER JOIN "t4" ON ("t4"."t1_id" = "t1"."id") RIGHT OUTER JOIN "t5" ON ("t5"."t1_id" = "t5"."id")',
[]))
def test_group_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.group_by(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name"',
[]))
q = q.group_by(T.author.age)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(type(q.group_by()), ExprList)
self.assertEqual(
compile(q.group_by()),
('"author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(compile(q.group_by([T.author.id, T.author.status])), (
'SELECT * FROM "author" GROUP BY "author"."id", "author"."status"',
[]))
self.assertEqual(compile(q.group_by([])),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q.group_by(reset=True)),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"',
[]))
def test_order_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.order_by(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC',
[]))
q = q.order_by(T.author.age.desc())
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
self.assertEqual(type(q.order_by()), ExprList)
self.assertEqual(compile(q.order_by()), (
'"author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
self.assertEqual(
compile(q.order_by([T.author.id.desc(), T.author.status])),
('SELECT * FROM "author" ORDER BY "author"."id" DESC, "author"."status" ASC',
[]))
self.assertEqual(compile(q.order_by([])),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q.order_by(reset=True)),
('SELECT * FROM "author"', []))
self.assertEqual(compile(q), (
'SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC',
[]))
def test_distinct(self):
q = Q().tables(T.author).fields(T.author.first_name,
T.author.last_name, T.author.age)
self.assertEqual(compile(q), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
q = q.distinct(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
q = q.distinct(T.author.age)
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(type(q.distinct()), ExprList)
self.assertEqual(
compile(q.distinct()),
('"author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(compile(q.distinct([T.author.id, T.author.status])), (
'SELECT DISTINCT ON ("author"."id", "author"."status") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q.distinct([])), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q.distinct(reset=True)), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(compile(q), (
'SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
def test_alias_subquery(self):
alias = Q().fields(T.book.id.count()).tables(T.book).where((T.book.pub_date > '2015-01-01') & (T.book.author_id == T.author.id)).group_by(T.book.author_id).as_("book_count")
q = Q().fields(T.author.id, alias).tables(T.author).where(T.author.status == 'active').order_by(alias.desc())
self.assertEqual(
compile(q),
('SELECT "author"."id", (SELECT COUNT("book"."id") FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") AS "book_count" FROM "author" WHERE "author"."status" = %s ORDER BY "book_count" DESC', ['2015-01-01', 'active'])
)
def test_fields_subquery(self):
sub_q = Q().fields(T.book.id.count().as_("book_count")).tables(T.book).where(T.book.pub_date > '2015-01-01').group_by(T.book.author_id)
q = Q().fields(T.author.id, sub_q.where(T.book.author_id == T.author.id)).tables(T.author).where(T.author.status == 'active')
self.assertEqual(
compile(q),
('SELECT "author"."id", (SELECT COUNT("book"."id") AS "book_count" FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") FROM "author" WHERE "author"."status" = %s', ['2015-01-01', 'active'])
)
def run(self, **kwargs):
self._result = kwargs
# data filtered from Serenity input...
data_to_test = self._filter_data_from_serenity(kwargs)
# ... will be updated by database data
metrics_to_query = self._get_which_metrics_query()
log.debug(metrics_to_query)
for metric, fields in metrics_to_query.iteritems():
# TODO: How big set of data we should analyze?
# In the meaning of time (where statement)
where_clause = "time > now() - " + TIME_FROM_NOW +\
" and source = \'%s\'" % self.source
query_to_execute = compile(
Q().tables('"' + metric + '"').
fields(fields["field"]).where(where_clause)
)
log.debug(self._format_query_to_string(query_to_execute))
database_output = self._get_data_from_database(
self._format_query_to_string(query_to_execute))
data_to_test[metric] = (database_output, fields)
self._result[DATA_FIELD] = data_to_test
return self._result
def test_t25_1(self):
self.assertEqual(
compile(Q().fields(
Q().tables(T.a).fields(F('*').count()).as_('tot'),
Q().tables(T.a).fields(F('*').count()).as_('another_tot'))),
('SELECT (SELECT COUNT(*) FROM "a") AS "tot", (SELECT COUNT(*) FROM "a") AS "another_tot"',
[]))
def test_case(self):
self.assertEqual(
compile(Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
])), ('CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s END ',
[1, 'one', 2, 'two']))
def test_where_subquery(self):
sub_q = Q().fields(T.author.id).tables(T.author).where(T.author.status == 'active')
q = Q().fields(T.book.id).tables(T.book).where(T.book.author_id.in_(sub_q))
self.assertEqual(
compile(q),
('SELECT "book"."id" FROM "book" WHERE "book"."author_id" IN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s)', ['active'])
)
def test_t25_1(self):
self.assertEqual(
compile(Q().fields(
Q().tables(T.a).fields(F('*').count()).as_('tot'),
Q().tables(T.a).fields(F('*').count()).as_('another_tot')
)),
('SELECT (SELECT COUNT(*) FROM "a") AS "tot", (SELECT COUNT(*) FROM "a") AS "another_tot"', [])
)
def test_case_with_expr_and_default(self):
self.assertEqual(
compile(Case([
(1, 'one'),
(2, 'two'),
], F.a, 'other')),
('CASE "a" WHEN %s THEN %s WHEN %s THEN %s ELSE %s END ', [1, 'one', 2, 'two', 'other'])
)
def test_where_subquery(self):
sub_q = Q().fields(T.author.id).tables(
T.author).where(T.author.status == 'active')
q = Q().fields(T.book.id).tables(T.book).where(
T.book.author_id.in_(sub_q))
self.assertEqual(compile(q), (
'SELECT "book"."id" FROM "book" WHERE "book"."author_id" IN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s)',
['active']))
def test_case_in_query(self):
self.assertEqual(
compile(Q().tables(T.t1).fields('*').where(F.c == Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
], default='other'))),
('SELECT * FROM "t1" WHERE "c" = CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s ELSE %s END ', [1, 'one', 2, 'two', 'other'])
)
def test_case_with_expr(self):
self.assertEqual(
compile(Case([
(1, 'one'),
(2, 'two'),
], F.a)),
('CASE "a" WHEN %s THEN %s WHEN %s THEN %s END ', [1, 'one', 2, 'two'])
)
def test_tables(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.tables(T.author.as_('author_alias'))
self.assertEqual(compile(q),
('SELECT * FROM "author" AS "author_alias"', []))
self.assertEqual(type(q.tables()), TableAlias)
self.assertEqual(compile(TableJoin(q.tables())),
('"author" AS "author_alias"', []))
self.assertEqual(
compile(
q.tables((q.tables() + T.book).on(
T.book.author_id == T.author.as_('author_alias').id))),
('SELECT * FROM "author" AS "author_alias" LEFT OUTER JOIN "book" ON ("book"."author_id" = "author_alias"."id")',
[]))
self.assertEqual(compile(q),
('SELECT * FROM "author" AS "author_alias"', []))
def test_case_with_expr_and_default(self):
self.assertEqual(
compile(Case([
(1, 'one'),
(2, 'two'),
], F.a, 'other')),
('CASE "a" WHEN %s THEN %s WHEN %s THEN %s ELSE %s END ',
[1, 'one', 2, 'two', 'other']))
def test_case_with_default(self):
self.assertEqual(
compile(Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
], default='other')),
('CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s ELSE %s END ', [1, 'one', 2, 'two', 'other'])
)
def test_case(self):
self.assertEqual(
compile(Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
])),
('CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s END ', [1, 'one', 2, 'two'])
)
def test_field(self):
# Get field as table attribute
self.assertEqual(type(T.book.title), Field)
self.assertEqual(compile(T.book.title), ('"book"."title"', []))
self.assertEqual(type(T.book.title.as_('a')), A)
self.assertEqual(compile(T.book.title.as_('a')), ('"a"', []))
self.assertEqual(type(T.book.title__a), A)
self.assertEqual(compile(T.book.title__a), ('"a"', []))
# Get field as class F attribute (Legacy)
self.assertEqual(type(F.book__title), Field)
self.assertEqual(compile(F.book__title), ('"book"."title"', []))
self.assertEqual(type(F.book__title.as_('a')), A)
self.assertEqual(compile(F.book__title.as_('a')), ('"a"', []))
self.assertEqual(type(F.book__title__a), A)
self.assertEqual(compile(F.book__title__a), ('"a"', []))
# Test with context
al = T.book.status.as_('a')
self.assertEqual(
compile(Q().tables(T.book).fields(T.book.id, al).where(
al.in_(('new', 'approved')))),
('SELECT "book"."id", "book"."status" AS "a" FROM "book" WHERE "a" IN (%s, %s)',
['new', 'approved']))
def test_query(self):
author, book = self._create_objects()
self.assertIsInstance(Book.s.q, Query)
q = Book.s.q.where(Book.s.pk == book.id)
self.assertEqual(compile(q), (
'SELECT "sqlbuilder_book"."id", "sqlbuilder_book"."title", "sqlbuilder_book"."author_id" FROM "sqlbuilder_book" WHERE "sqlbuilder_book"."id" = %s',
[book.id]))
book2 = q[0]
self.assertEqual(book2.id, book.id)
def test_case_with_default(self):
self.assertEqual(
compile(
Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
], default='other')),
('CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s ELSE %s END ',
[1, 'one', 2, 'two', 'other']))
def test_table(self):
table = Book.s
self.assertIsInstance(table, Table)
self.assertIsInstance(table.pk, Field)
self.assertIsInstance(table.title, Field)
self.assertIsInstance(table.author, Field)
self.assertEqual(
compile(table.pk),
('"sqlbuilder_book"."id"', [])
)
self.assertEqual(
compile(table.title),
('"sqlbuilder_book"."title"', [])
)
self.assertEqual(
compile(table.author),
('"sqlbuilder_book"."author_id"', [])
)
def test_as_table(self):
author_query_alias = Q(T.author).fields(T.author.id).where(
T.author.status == 'active').as_table('author_query_alias')
self.assertEqual(
compile(Q().fields(T.book.id, T.book.title).tables(
(T.book & author_query_alias).on(
T.book.author_id == author_query_alias.id))),
('SELECT "book"."id", "book"."title" FROM "book" INNER JOIN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s) AS "author_query_alias" ON ("book"."author_id" = "author_query_alias"."id")',
['active']))
def test_tablealias(self):
table = Book.s.as_('book_alias')
self.assertIsInstance(table, TableAlias)
self.assertIsInstance(table.pk, Field)
self.assertIsInstance(table.title, Field)
self.assertIsInstance(table.author, Field)
self.assertEqual(
compile(table.pk),
('"book_alias"."id"', [])
)
self.assertEqual(
compile(table.title),
('"book_alias"."title"', [])
)
self.assertEqual(
compile(table.author),
('"book_alias"."author_id"', [])
)
def test_query(self):
author, book = self._create_objects()
self.assertIsInstance(Book.s.q, Query)
q = Book.s.q.where(Book.s.pk == book.id)
self.assertEqual(
compile(q),
('SELECT "sqlbuilder_book"."id", "sqlbuilder_book"."title", "sqlbuilder_book"."author_id" FROM "sqlbuilder_book" WHERE "sqlbuilder_book"."id" = %s', [book.id])
)
book2 = q[0]
self.assertEqual(book2.id, book.id)
def test_fields_subquery(self):
sub_q = Q().fields(T.book.id.count().as_("book_count")).tables(
T.book).where(T.book.pub_date > '2015-01-01').group_by(
T.book.author_id)
q = Q().fields(T.author.id,
sub_q.where(T.book.author_id == T.author.id)).tables(
T.author).where(T.author.status == 'active')
self.assertEqual(compile(q), (
'SELECT "author"."id", (SELECT COUNT("book"."id") AS "book_count" FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") FROM "author" WHERE "author"."status" = %s',
['2015-01-01', 'active']))
def test_alias_subquery(self):
alias = Q().fields(T.book.id.count()).tables(
T.book).where((T.book.pub_date > '2015-01-01')
& (T.book.author_id == T.author.id)).group_by(
T.book.author_id).as_("book_count")
q = Q().fields(T.author.id, alias).tables(
T.author).where(T.author.status == 'active').order_by(alias.desc())
self.assertEqual(compile(q), (
'SELECT "author"."id", (SELECT COUNT("book"."id") FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") AS "book_count" FROM "author" WHERE "author"."status" = %s ORDER BY "book_count" DESC',
['2015-01-01', 'active']))
def test_case_in_query(self):
self.assertEqual(
compile(Q().tables(
T.t1).fields('*').where(F.c == Case([
(F.a == 1, 'one'),
(F.b == 2, 'two'),
],
default='other'))),
('SELECT * FROM "t1" WHERE "c" = CASE WHEN ("a" = %s) THEN %s WHEN ("b" = %s) THEN %s ELSE %s END ',
[1, 'one', 2, 'two', 'other']))
def test_where(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(compile(q), ('SELECT * FROM "author"', []))
q = q.where(T.author.is_staff.is_(True))
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."is_staff" IS %s', [True]))
q = q.where(T.author.first_name == 'John')
self.assertEqual(compile(q), (
'SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s',
[True, 'John']))
q = q.where(T.author.last_name == 'Smith', op=operator.or_)
self.assertEqual(compile(q), (
'SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s',
[True, 'John', 'Smith']))
q = q.where(T.author.last_name == 'Smith', op=None)
self.assertEqual(
compile(q),
('SELECT * FROM "author" WHERE "author"."last_name" = %s',
['Smith']))
def test_having(self):
q = Q().fields('*').tables(T.author).group_by(T.author.status)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."status"', []))
q = q.having(T.author.is_staff.is_(True))
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s',
[True]))
q = q.having(T.author.first_name == 'John')
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s',
[True, 'John']))
q = q.having(T.author.last_name == 'Smith', op=operator.or_)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s',
[True, 'John', 'Smith']))
q = q.having(T.author.last_name == 'Smith', op=None)
self.assertEqual(compile(q), (
'SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."last_name" = %s',
['Smith']))
def test_join(self):
self.assertEqual(
compile((T.book & T.author).on(T.book.author_id == T.author.id)),
('"book" INNER JOIN "author" ON ("book"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile((T.book + T.author).on(T.book.author_id == T.author.id)),
('"book" LEFT OUTER JOIN "author" ON ("book"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile((T.book - T.author).on(T.book.author_id == T.author.id)),
('"book" RIGHT OUTER JOIN "author" ON ("book"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile((T.book | T.author).on(T.book.author_id == T.author.id)),
('"book" FULL OUTER JOIN "author" ON ("book"."author_id" = "author"."id")',
[]))
self.assertEqual(
compile((T.book * T.author).on(T.book.author_id == T.author.id)),
('"book" CROSS JOIN "author" ON ("book"."author_id" = "author"."id")',
[]))
def test_join_nested(self):
t1, t2, t3, t4 = T.t1, T.t2, T.t3, T.t4
self.assertEqual(
compile(t1 + (t2 * t3 * t4)().on((t2.a == t1.a) & (t3.b == t1.b)
& (t4.c == t1.c))),
('"t1" LEFT OUTER JOIN ("t2" CROSS JOIN "t3" CROSS JOIN "t4") ON ("t2"."a" = "t1"."a" AND "t3"."b" = "t1"."b" AND "t4"."c" = "t1"."c")',
[]))
self.assertEqual(
compile((t1 + (t2 * t3 * t4)()).on((t2.a == t1.a) & (t3.b == t1.b)
& (t4.c == t1.c))),
('"t1" LEFT OUTER JOIN ("t2" CROSS JOIN "t3" CROSS JOIN "t4") ON ("t2"."a" = "t1"."a" AND "t3"."b" = "t1"."b" AND "t4"."c" = "t1"."c")',
[]))
self.assertEqual(
compile(t1 + (t2 + t3).on((t2.b == t3.b) | t2.b.is_(None))()),
('"t1" LEFT OUTER JOIN ("t2" LEFT OUTER JOIN "t3" ON ("t2"."b" = "t3"."b" OR "t2"."b" IS NULL))',
[]))
self.assertEqual(
compile((t1 + t2.on(t1.a == t2.a))() +
t3.on((t2.b == t3.b) | t2.b.is_(None))),
('("t1" LEFT OUTER JOIN "t2" ON ("t1"."a" = "t2"."a")) LEFT OUTER JOIN "t3" ON ("t2"."b" = "t3"."b" OR "t2"."b" IS NULL)',
[]))
def test_set(self):
q1 = Q(T.book1).fields(T.book1.id, T.book1.title).where(T.book1.author_id == 10)
q2 = Q(T.book2).fields(T.book2.id, T.book2.title).where(T.book2.author_id == 10)
self.assertEqual(
compile(q1.as_set() | q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
self.assertEqual(
compile(q1.as_set() & q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
self.assertEqual(
compile(q1.as_set() - q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
self.assertEqual(
compile(q1.as_set(all=True) | q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
self.assertEqual(
compile(q1.as_set(all=True) & q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
self.assertEqual(
compile(q1.as_set(all=True) - q2),
('(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)', [10, 10])
)
def test_table(self):
self.assertEqual(type(T.book), Table)
self.assertEqual(compile(T.book), ('"book"', []))
self.assertEqual(
compile(
T.author.get_field(('first_name', 'last_name')) == ('fn1',
'ln2')),
('"author"."first_name" = %s AND "author"."last_name" = %s',
['fn1', 'ln2']))
self.assertEqual(
compile(
T.author.get_field(('first_name__a',
'last_name__b')) == ('fn1', 'ln2')),
('"a" = %s AND "b" = %s', ['fn1', 'ln2']))
self.assertEqual(type(T.book__a), TA)
state = State()
state.push("context", CONTEXT.FIELD_PREFIX)
compile(T.book__a, state)
self.assertEqual((''.join(state.sql), state.params), ('"a"', []))
self.assertEqual(compile(T.book__a), ('"a"', []))
self.assertEqual(type(T.book.as_('a')), TA)
state = State()
state.push("context", CONTEXT.FIELD_PREFIX)
compile(T.book.as_('a'), state)
self.assertEqual((''.join(state.sql), state.params), ('"a"', []))
self.assertEqual(compile(T.book.as_('a')), ('"a"', []))
ta = T.book.as_('a')
self.assertEqual(
compile(Q().tables(ta).fields(ta.id, ta.status).where(
ta.status.in_(('new', 'approved')))),
('SELECT "a"."id", "a"."status" FROM "book" AS "a" WHERE "a"."status" IN (%s, %s)',
['new', 'approved']))
t = T.book
self.assertIs(t.status, t.status)
self.assertIs(t.status, t.f.status)
self.assertIs(t.status, t.f('status'))
self.assertIs(t.status, t.f['status'])
self.assertIs(t.status, t['status'])
self.assertIs(t.status, t.__getattr__('status'))
self.assertIs(t.status, t.get_field('status'))
def test_t25_2(self):
product = T.sales_product
sales = T.sales_sale
category = T.sales_category
categoryassignment = T.sales_categoryassignment
company_id = 1
start_date = datetime.datetime(2017, 12, 1, 0, 0, 0)
end_date = datetime.datetime(2017, 12, 23, 23, 59, 59)
joins = (sales & product).on(sales.product == product.pk)
where = (product.company_id == company_id) & (sales.created_at.between(func.date(start_date), func.date(end_date)))
inner_table = Q().tables(
(joins + categoryassignment).on(categoryassignment.product == product.pk)
).fields(
categoryassignment.category_id.as_('category_id'),
func.date(sales.created_at).as_('date'),
func.coalesce(func.sum(sales.quantity)).as_('quantity')
).where(
where
).group_by(
func.date(sales.created_at), categoryassignment.category_id
).as_table('g')
category_quantity_breakdown_q = Q().tables(
(inner_table + category).on(inner_table.category_id == category.pk)
).fields(
category.id,
category.name,
func.json_agg(inner_table).as_('days')
).group_by(
category.id, category.name
)
self.assertEqual(compile(category_quantity_breakdown_q), (
'SELECT "sales_category"."id", "sales_category"."name", JSON_AGG("g") AS "days" '
'FROM (SELECT "sales_categoryassignment"."category_id" AS '
'"category_id", DATE("sales_sale"."created_at") AS "date", '
'COALESCE(SUM("sales_sale"."quantity")) AS "quantity" FROM "sales_sale" INNER '
'JOIN "sales_product" ON ("sales_sale"."product" = "sales_product"."pk") LEFT '
'OUTER JOIN "sales_categoryassignment" ON '
'("sales_categoryassignment"."product" = "sales_product"."pk") WHERE '
'"sales_product"."company_id" = %s AND "sales_sale"."created_at" BETWEEN '
'DATE(%s) AND DATE(%s) GROUP BY DATE("sales_sale"."created_at"), '
'"sales_categoryassignment"."category_id") AS "g" LEFT OUTER JOIN '
'"sales_category" ON ("g"."category_id" = "sales_category"."pk") GROUP BY '
'"sales_category"."id", "sales_category"."name"',
[company_id, start_date, end_date]
))
def test_distinct(self):
q = Q().tables(T.author).fields(T.author.first_name, T.author.last_name, T.author.age)
self.assertEqual(
compile(q),
('SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
q = q.distinct(T.author.first_name, T.author.last_name)
self.assertEqual(
compile(q),
('SELECT DISTINCT ON ("author"."first_name", "author"."last_name") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
q = q.distinct(T.author.age)
self.assertEqual(
compile(q),
('SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
self.assertEqual(
type(q.distinct()),
ExprList
)
self.assertEqual(
compile(q.distinct()),
('"author"."first_name", "author"."last_name", "author"."age"', [])
)
self.assertEqual(
compile(q.distinct([T.author.id, T.author.status])),
('SELECT DISTINCT ON ("author"."id", "author"."status") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
self.assertEqual(
compile(q.distinct([])),
('SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
self.assertEqual(
compile(q.distinct(reset=True)),
('SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
self.assertEqual(
compile(q),
('SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
def test_group_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(
compile(q),
('SELECT * FROM "author"', [])
)
q = q.group_by(T.author.first_name, T.author.last_name)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name"', [])
)
q = q.group_by(T.author.age)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"', [])
)
self.assertEqual(
type(q.group_by()),
ExprList
)
self.assertEqual(
compile(q.group_by()),
('"author"."first_name", "author"."last_name", "author"."age"', [])
)
self.assertEqual(
compile(q.group_by([T.author.id, T.author.status])),
('SELECT * FROM "author" GROUP BY "author"."id", "author"."status"', [])
)
self.assertEqual(
compile(q.group_by([])),
('SELECT * FROM "author"', [])
)
self.assertEqual(
compile(q.group_by(reset=True)),
('SELECT * FROM "author"', [])
)
self.assertEqual(
compile(q),
('SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age"', [])
)
def test_order_by(self):
q = Q().tables(T.author).fields('*')
self.assertEqual(
compile(q),
('SELECT * FROM "author"', [])
)
q = q.order_by(T.author.first_name, T.author.last_name)
self.assertEqual(
compile(q),
('SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC', [])
)
q = q.order_by(T.author.age.desc())
self.assertEqual(
compile(q),
('SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', [])
)
self.assertEqual(
type(q.order_by()),
ExprList
)
self.assertEqual(
compile(q.order_by()),
('"author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', [])
)
self.assertEqual(
compile(q.order_by([T.author.id.desc(), T.author.status])),
('SELECT * FROM "author" ORDER BY "author"."id" DESC, "author"."status" ASC', [])
)
self.assertEqual(
compile(q.order_by([])),
('SELECT * FROM "author"', [])
)
self.assertEqual(
compile(q.order_by(reset=True)),
('SELECT * FROM "author"', [])
)
self.assertEqual(
compile(q),
('SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC', [])
)
def test_t25_2(self):
product = T.sales_product
sales = T.sales_sale
category = T.sales_category
categoryassignment = T.sales_categoryassignment
company_id = 1
start_date = datetime.datetime(2017, 12, 1, 0, 0, 0)
end_date = datetime.datetime(2017, 12, 23, 23, 59, 59)
joins = (sales & product).on(sales.product == product.pk)
where = (product.company_id == company_id) & (sales.created_at.between(
func.date(start_date), func.date(end_date)))
inner_table = Q().tables((joins + categoryassignment).on(
categoryassignment.product == product.pk)).fields(
categoryassignment.category_id.as_('category_id'),
func.date(sales.created_at).as_('date'),
func.coalesce(func.sum(
sales.quantity)).as_('quantity')).where(where).group_by(
func.date(sales.created_at),
categoryassignment.category_id).as_table('g')
category_quantity_breakdown_q = Q().tables(
(inner_table +
category).on(inner_table.category_id == category.pk)).fields(
category.id, category.name,
func.json_agg(inner_table).as_('days')).group_by(
category.id, category.name)
self.assertEqual(compile(category_quantity_breakdown_q), (
'SELECT "sales_category"."id", "sales_category"."name", JSON_AGG("g") AS "days" '
'FROM (SELECT "sales_categoryassignment"."category_id" AS '
'"category_id", DATE("sales_sale"."created_at") AS "date", '
'COALESCE(SUM("sales_sale"."quantity")) AS "quantity" FROM "sales_sale" INNER '
'JOIN "sales_product" ON ("sales_sale"."product" = "sales_product"."pk") LEFT '
'OUTER JOIN "sales_categoryassignment" ON '
'("sales_categoryassignment"."product" = "sales_product"."pk") WHERE '
'"sales_product"."company_id" = %s AND "sales_sale"."created_at" BETWEEN '
'DATE(%s) AND DATE(%s) GROUP BY DATE("sales_sale"."created_at"), '
'"sales_categoryassignment"."category_id") AS "g" LEFT OUTER JOIN '
'"sales_category" ON ("g"."category_id" = "sales_category"."pk") GROUP BY '
'"sales_category"."id", "sales_category"."name"',
[company_id, start_date, end_date]))
def test_fields(self):
q = Q().tables(T.author)
q = q.fields(T.author.first_name, T.author.last_name)
self.assertEqual(compile(q), (
'SELECT "author"."first_name", "author"."last_name" FROM "author"',
[]))
q = q.fields(T.author.age)
self.assertEqual(compile(q), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
self.assertEqual(type(q.fields()), FieldList)
self.assertEqual(
compile(q.fields()),
('"author"."first_name", "author"."last_name", "author"."age"',
[]))
self.assertEqual(
compile(q.fields([T.author.id, T.author.status])),
('SELECT "author"."id", "author"."status" FROM "author"', []))
self.assertEqual(compile(q.fields([])), ('SELECT FROM "author"', []))
self.assertEqual(compile(q.fields(reset=True)),
('SELECT FROM "author"', []))
self.assertEqual(compile(q), (
'SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"',
[]))
def test_fields(self):
q = Q().tables(T.author)
q = q.fields(T.author.first_name, T.author.last_name)
self.assertEqual(
compile(q),
('SELECT "author"."first_name", "author"."last_name" FROM "author"', [])
)
q = q.fields(T.author.age)
self.assertEqual(
compile(q),
('SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
self.assertEqual(
type(q.fields()),
FieldList
)
self.assertEqual(
compile(q.fields()),
('"author"."first_name", "author"."last_name", "author"."age"', [])
)
self.assertEqual(
compile(q.fields([T.author.id, T.author.status])),
('SELECT "author"."id", "author"."status" FROM "author"', [])
)
self.assertEqual(
compile(q.fields([])),
('SELECT FROM "author"', [])
)
self.assertEqual(
compile(q.fields(reset=True)),
('SELECT FROM "author"', [])
)
self.assertEqual(
compile(q),
('SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author"', [])
)
def test_set(self):
q1 = Q(T.book1).fields(T.book1.id,
T.book1.title).where(T.book1.author_id == 10)
q2 = Q(T.book2).fields(T.book2.id,
T.book2.title).where(T.book2.author_id == 10)
self.assertEqual(compile(q1.as_set() | q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
self.assertEqual(compile(q1.as_set() & q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
self.assertEqual(compile(q1.as_set() - q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
self.assertEqual(compile(q1.as_set(all=True) | q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
self.assertEqual(compile(q1.as_set(all=True) & q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
self.assertEqual(compile(q1.as_set(all=True) - q2), (
'(SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s)',
[10, 10]))
def test_case(self):
self.assertEqual(
compile(Cast(F.field_name, 'text')),
('CAST("field_name" AS text)', [])
)
def test_expr(self):
tb = T.author
self.assertEqual(
compile(tb.name == 'Tom'),
(('"author"."name" = %s'), ['Tom'])
)
self.assertEqual(
compile(tb.name != 'Tom'),
(('"author"."name" <> %s'), ['Tom'])
)
self.assertEqual(
compile(tb.counter + 1),
('"author"."counter" + %s', [1])
)
self.assertEqual(
compile(1 + tb.counter),
('%s + "author"."counter"', [1])
)
self.assertEqual(
compile(tb.counter - 1),
('"author"."counter" - %s', [1])
)
self.assertEqual(
compile(10 - tb.counter),
('%s - "author"."counter"', [10])
)
self.assertEqual(
compile(tb.counter * 2),
('"author"."counter" * %s', [2])
)
self.assertEqual(
compile(2 * tb.counter),
('%s * "author"."counter"', [2])
)
self.assertEqual(
compile(tb.counter / 2),
('"author"."counter" / %s', [2])
)
self.assertEqual(
compile(10 / tb.counter),
('%s / "author"."counter"', [10])
)
self.assertEqual(
compile(tb.is_staff & tb.is_admin),
('"author"."is_staff" AND "author"."is_admin"', [])
)
self.assertEqual(
compile(tb.is_staff | tb.is_admin),
('"author"."is_staff" OR "author"."is_admin"', [])
)
self.assertEqual(
compile(tb.counter > 10),
('"author"."counter" > %s', [10])
)
self.assertEqual(
compile(10 > tb.counter),
('"author"."counter" < %s', [10])
)
self.assertEqual(
compile(tb.counter >= 10),
('"author"."counter" >= %s', [10])
)
self.assertEqual(
compile(10 >= tb.counter),
('"author"."counter" <= %s', [10])
)
self.assertEqual(
compile(tb.counter < 10),
('"author"."counter" < %s', [10])
)
self.assertEqual(
compile(10 < tb.counter),
('"author"."counter" > %s', [10])
)
self.assertEqual(
compile(tb.counter <= 10),
('"author"."counter" <= %s', [10])
)
self.assertEqual(
compile(10 <= tb.counter),
('"author"."counter" >= %s', [10])
)
self.assertEqual(
compile(tb.mask << 1),
('"author"."mask" << %s', [1])
)
self.assertEqual(
compile(tb.mask >> 1),
('"author"."mask" >> %s', [1])
)
self.assertEqual(
compile(tb.is_staff.is_(True)),
('"author"."is_staff" IS %s', [True])
)
self.assertEqual(
compile(tb.is_staff.is_not(True)),
('"author"."is_staff" IS NOT %s', [True])
)
self.assertEqual(
compile(tb.status.in_(('new', 'approved'))),
('"author"."status" IN (%s, %s)', ['new', 'approved'])
)
self.assertEqual(
compile(tb.status.not_in(('new', 'approved'))),
('"author"."status" NOT IN (%s, %s)', ['new', 'approved'])
)
self.assertEqual(
compile(tb.last_name.like('mi')),
('"author"."last_name" LIKE %s', ['mi'])
)
self.assertEqual(
compile(tb.last_name.ilike('mi')),
('"author"."last_name" ILIKE %s', ['mi'])
)
self.assertEqual(
compile(P('mi').like(tb.last_name)),
('%s LIKE "author"."last_name"', ['mi'])
)
self.assertEqual(
compile(tb.last_name.rlike('mi')),
('%s LIKE "author"."last_name"', ['mi'])
)
self.assertEqual(
compile(tb.last_name.rilike('mi')),
('%s ILIKE "author"."last_name"', ['mi'])
)
self.assertEqual(
compile(tb.last_name.startswith('Sm')),
('"author"."last_name" LIKE REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['Sm'])
)
self.assertEqual(
compile(tb.last_name.istartswith('Sm')),
('"author"."last_name" ILIKE REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['Sm'])
)
self.assertEqual(
compile(tb.last_name.contains('mi')),
('"author"."last_name" LIKE \'%%\' || REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['mi'])
)
self.assertEqual(
compile(tb.last_name.icontains('mi')),
('"author"."last_name" ILIKE \'%%\' || REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['mi'])
)
self.assertEqual(
compile(tb.last_name.endswith('th')),
('"author"."last_name" LIKE \'%%\' || REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') ESCAPE \'!\'', ['th'])
)
self.assertEqual(
compile(tb.last_name.iendswith('th')),
('"author"."last_name" ILIKE \'%%\' || REPLACE(REPLACE(REPLACE(%s, \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') ESCAPE \'!\'', ['th'])
)
self.assertEqual(
compile(tb.last_name.rstartswith('Sm')),
('%s LIKE REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['Sm'])
)
self.assertEqual(
compile(tb.last_name.ristartswith('Sm')),
('%s ILIKE REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['Sm'])
)
self.assertEqual(
compile(tb.last_name.rcontains('mi')),
('%s LIKE \'%%\' || REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['mi'])
)
self.assertEqual(
compile(tb.last_name.ricontains('mi')),
('%s ILIKE \'%%\' || REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') || \'%%\' ESCAPE \'!\'', ['mi'])
)
self.assertEqual(
compile(tb.last_name.rendswith('th')),
('%s LIKE \'%%\' || REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') ESCAPE \'!\'', ['th'])
)
self.assertEqual(
compile(tb.last_name.riendswith('th')),
('%s ILIKE \'%%\' || REPLACE(REPLACE(REPLACE("author"."last_name", \'!\', \'!!\'), \'_\', \'!_\'), \'%%\', \'!%%\') ESCAPE \'!\'', ['th'])
)
self.assertEqual(
compile(+tb.counter),
('+"author"."counter"', [])
)
self.assertEqual(
compile(-tb.counter),
('-"author"."counter"', [])
)
self.assertEqual(
compile(~tb.counter),
('NOT "author"."counter"', [])
)
self.assertEqual(
compile(tb.name.distinct()),
('DISTINCT "author"."name"', [])
)
self.assertEqual(
compile(tb.counter ** 2),
('POWER("author"."counter", %s)', [2])
)
self.assertEqual(
compile(2 ** tb.counter),
('POWER(%s, "author"."counter")', [2])
)
self.assertEqual(
compile(tb.counter % 2),
('MOD("author"."counter", %s)', [2])
)
self.assertEqual(
compile(2 % tb.counter),
('MOD(%s, "author"."counter")', [2])
)
self.assertEqual(
compile(abs(tb.counter)),
('ABS("author"."counter")', [])
)
self.assertEqual(
compile(tb.counter.count()),
('COUNT("author"."counter")', [])
)
self.assertEqual(
compile(tb.age.between(20, 30)),
('"author"."age" BETWEEN %s AND %s', [20, 30])
)
self.assertEqual(
compile(tb.age[20:30]),
('"author"."age" BETWEEN %s AND %s', [20, 30])
)
self.assertEqual(
compile(T.tb.cl[T.tb.cl2:T.tb.cl3]),
('"tb"."cl" BETWEEN "tb"."cl2" AND "tb"."cl3"', [])
)
self.assertEqual(
compile(tb.age[20]),
('"author"."age" = %s', [20])
)
self.assertEqual(
compile(tb.name.concat(' staff', ' admin')),
('"author"."name" || %s || %s', [' staff', ' admin'])
)
self.assertEqual(
compile(tb.name.concat_ws(' ', 'staff', 'admin')),
('concat_ws(%s, "author"."name", %s, %s)', [' ', 'staff', 'admin'])
)
self.assertEqual(
compile(tb.name.op('MY_EXTRA_OPERATOR')(10)),
('"author"."name" MY_EXTRA_OPERATOR %s', [10])
)
self.assertEqual(
compile(tb.name.rop('MY_EXTRA_OPERATOR')(10)),
('%s MY_EXTRA_OPERATOR "author"."name"', [10])
)
self.assertEqual(
compile(tb.name.asc()),
('"author"."name" ASC', [])
)
self.assertEqual(
compile(tb.name.desc()),
('"author"."name" DESC', [])
)
self.assertEqual(
compile(((tb.age > 25) | (tb.answers > 10)) & (tb.is_staff | tb.is_admin)),
('("author"."age" > %s OR "author"."answers" > %s) AND ("author"."is_staff" OR "author"."is_admin")', [25, 10])
)
self.assertEqual(
compile((T.author.first_name != 'Tom') & (T.author.last_name.in_(('Smith', 'Johnson')))),
('"author"."first_name" <> %s AND "author"."last_name" IN (%s, %s)', ['Tom', 'Smith', 'Johnson'])
)
self.assertEqual(
compile((T.author.first_name != 'Tom') | (T.author.last_name.in_(('Smith', 'Johnson')))),
('"author"."first_name" <> %s OR "author"."last_name" IN (%s, %s)', ['Tom', 'Smith', 'Johnson'])
)
def test_as_table(self):
author_query_alias = Q(T.author).fields(T.author.id).where(T.author.status == 'active').as_table('author_query_alias')
self.assertEqual(
compile(Q().fields(T.book.id, T.book.title).tables((T.book & author_query_alias).on(T.book.author_id == author_query_alias.id))),
('SELECT "book"."id", "book"."title" FROM "book" INNER JOIN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s) AS "author_query_alias" ON ("book"."author_id" = "author_query_alias"."id")', ['active'])
)
def base_query(self, conditions):
return compile(Q().tables(self.get_tables()).fields(self.get_fields()).where(conditions))
("T.user.age <@ func.int4range(25, 30)",
('"user"."age" <@ INT4RANGE(%s, %s)', [25, 30])),
("T.user.is_staff IS NOT TRUE AND T.user.is_admin IS FALSE",
('"user"."is_staff" IS NOT TRUE AND "user"."is_admin" IS FALSE', [])),
("T.user.is_staff AND T.user.age BETWEEN 25 AND 30 AND T.user.is_admin",
('"user"."is_staff" AND "user"."age" BETWEEN %s AND %s AND "user"."is_admin"', [25, 30])),
("T.user.is_staff AND T.user.age BETWEEN 25 AND 30 OR T.user.is_admin",
('"user"."is_staff" AND "user"."age" BETWEEN %s AND %s OR "user"."is_admin"', [25, 30])),
("T.user.is_staff AND T.user.age NOT BETWEEN 25 AND 30 + T.t1.delta AND T.t1.f1",
('"user"."is_staff" AND "user"."age" NOT BETWEEN %s AND (%s + "t1"."delta") AND "t1"."f1"', [25, 30])),
("T.user.age IN F.l1 AND T.user.age NOT IN F.l2')",
('"user"."age" IN "l1" AND "user"."age" NOT IN "l2"', [])),
("T.user.age DESC AND T.user.first_name ASC",
('"user"."age" DESC AND "user"."first_name" ASC', [])),
("T.user.age <@ func.int4range('%(min)s', '%(max)s')",
('"user"."age" <@ INT4RANGE(%s, %s)', ['%(min)s', '%(max)s']))
]
for t, expected in tests:
sql = smartsql.compile(e(t))
print(t, '\n', sql, '\n\n')
assert expected == sql
