def test_format_annotation(): sql = 'SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition", ("library_book"."edition" + 1) AS "next_edition" FROM "library_book"' expected_sql = dedent('''\ SELECT "library_book".*, ("library_book"."edition" + 1) AS "next_edition" FROM "library_book"''') assert format_sql(sql) == expected_sql
def test_format_sql(): sql = 'SELECT "library_author"."id", "library_author"."name", "library_author"."date_of_birth", "library_author"."biography" FROM "library_author" LIMIT 21' expected_sql = dedent('''\ SELECT "library_author".* FROM "library_author" LIMIT 21''') assert format_sql(sql) == expected_sql
def test_format_where(): sql = r"""SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition" FROM "library_book" WHERE "library_book"."name" LIKE '%tusk love%' ESCAPE '\'""" expected_sql = dedent("""\ SELECT "library_book".* FROM "library_book" WHERE "library_book"."name" LIKE '%tusk love%' ESCAPE '\\'""") assert format_sql(sql) == expected_sql
def test_format_nested_where_no_parenthesis_last(): sql = """SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition" FROM "library_book" WHERE (("library_book"."name" = 'tusk love' OR "library_book"."name" = 'the mountain range of gold') AND "library_book"."edition" = 1)""" expected_sql = dedent('''\ SELECT "library_book".* FROM "library_book" WHERE (("library_book"."name" = 'tusk love' OR "library_book"."name" = 'the mountain range of gold') AND "library_book"."edition" = 1)''') assert format_sql(sql) == expected_sql
def test_format_order_by(): sql = '''SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition" FROM "library_book" ORDER BY "library_book"."name" ASC, "library_book"."edition" DESC LIMIT 1''' expected_sql = dedent('''\ SELECT "library_book".* FROM "library_book" ORDER BY "library_book"."name" ASC, "library_book"."edition" DESC LIMIT 1''') assert format_sql(sql) == expected_sql
def test_format_nested_where(): sql = """SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition" FROM "library_book" WHERE ("library_book"."name" = 'tusk love' AND ("library_book"."edition" = 1 OR "library_book"."edition" = 2))""" expected_sql = dedent('''\ SELECT "library_book".* FROM "library_book" WHERE ("library_book"."name" = 'tusk love' AND ("library_book"."edition" = 1 OR "library_book"."edition" = 2) )''') assert format_sql(sql) == expected_sql
def test_format_joins(): sql = 'SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition", "library_author"."id", "library_author"."name", "library_author"."date_of_birth", "library_author"."biography", "library_publisher"."id", "library_publisher"."name" FROM "library_book" INNER JOIN "library_author" ON ("library_book"."author_id" = "library_author"."id") LEFT OUTER JOIN "library_publisher" ON ("library_book"."publisher_id" = "library_publisher"."id")' expected_sql = dedent('''\ SELECT "library_book".*, "library_author".*, "library_publisher".* FROM "library_book" INNER JOIN "library_author" ON ("library_book"."author_id" = "library_author"."id") LEFT OUTER JOIN "library_publisher" ON ("library_book"."publisher_id" = "library_publisher"."id")''') assert format_sql(sql) == expected_sql
def test_format_case_when(): sql = '''SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition", CASE WHEN "library_book"."edition" = 1 THEN 'First' WHEN "library_book"."edition" = 2 THEN 'Second' ELSE 'Other' END AS "text_edition" FROM "library_book"''' expected_sql = dedent('''\ SELECT "library_book".*, CASE WHEN "library_book"."edition" = 1 THEN 'First' WHEN "library_book"."edition" = 2 THEN 'Second' ELSE 'Other' END AS "text_edition" FROM "library_book"''') assert format_sql(sql) == expected_sql
def test_format_multiple(): sql = dedent(''' SELECT "library_book"."id", "library_book"."name", "library_book"."author_id", "library_book"."publisher_id", "library_book"."synopsis", "library_book"."publish_date", "library_book"."edition" FROM "library_book" SELECT "library_author"."id", "library_author"."name", "library_author"."date_of_birth", "library_author"."biography" FROM "library_author" ''') expected_sql = dedent(''' SELECT "library_book".* FROM "library_book" SELECT "library_author".* FROM "library_author" ''') assert format_sql(sql) == expected_sql