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