def test_order_by_sql_query_with_order_by_name(self): qs1 = Report.objects.values("name") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, _ = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_report.name FROM tests_report ORDER BY " + "tests_report.name ASC", )
def test_order_by_sql_query_with_order_by_null_first(self): qs1 = Report.objects.values("name").order_by( F("name").desc(nulls_first=True)) compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, _ = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_report.name FROM tests_report ORDER BY " + "tests_report.name IS NOT NULL, tests_report.name DESC", )
def test_startswith_endswith_sql_query_case_insensitive(self): qs1 = Author.objects.filter(name__istartswith="abc").values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_author.num FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(tests_author.name AS STRING), %s)", ) self.assertEqual(params, ("(?i)^abc",))
def test_cast_param_to_float_with_no_params_query(self): qs1 = Number.objects.filter(item_id__exact=F("num")).values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_number.num FROM tests_number WHERE " + "tests_number.item_id = (tests_number.num)", ) self.assertEqual(params, ())
def test_cast_param_to_float_for_foreign_key_field_query(self): qs1 = Number.objects.filter(item_id__exact="10").values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_number.num FROM tests_number WHERE " + "tests_number.item_id = %s", ) self.assertEqual(params, (10,))
def test_cast_param_to_float_for_int_field_query(self): qs1 = Number.objects.filter(num__lte=1.1).values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_number.num FROM tests_number WHERE " + "tests_number.num <= %s", ) self.assertEqual(params, (1,))
def test_cast_param_to_float_lte_sql_query(self): qs1 = Number.objects.filter( decimal_num__lte=Decimal("1.1")).values("decimal_num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_number.decimal_num FROM tests_number WHERE " + "tests_number.decimal_num <= %s", ) self.assertEqual(params, (Decimal("1.1"), ))
def test_iexact_sql_query_case_insensitive_value_match(self): qs1 = Author.objects.filter(name__upper__iexact="abc").values("name") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS((UPPER(CONCAT('^(?i)', " + "CAST(UPPER(tests_author.name) AS STRING), '$'))), %s)", ) self.assertEqual(params, ("abc",))
def test_contains_sql_query_case_sensitive_transform(self): qs1 = Author.objects.filter(name__upper__contains="abc").values("name") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + 'REPLACE(REPLACE(REPLACE((UPPER(%s)), "\\\\", "\\\\\\\\"), ' + '"%%", r"\\%%"), "_", r"\\_"))', ) self.assertEqual(params, ("abc",))
def test_regex_sql_query_case_insensitive_with_transform(self): qs1 = Author.objects.filter(name__upper__iregex="abc").values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_author.num FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "CONCAT('(?i)', (UPPER(%s))))", ) self.assertEqual(params, ("abc",))
def test_startswith_endswith_sql_query_with_bileteral_transform(self): qs1 = Author.objects.filter(name__upper__startswith="abc").values( "name" ) compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() self.assertEqual( sql_compiled, "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "REPLACE(REPLACE(REPLACE(CONCAT('^', (UPPER(%s))), " + '"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))', ) self.assertEqual(params, ("abc",))
def test_regex_sql_query_case_sensitive_with_transform(self): qs1 = Author.objects.filter(name__upper__regex="abc").values("num") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() if USING_DJANGO_3: expected_sql = ( "SELECT tests_author.num FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "UPPER(%s))") else: expected_sql = ( "SELECT tests_author.num FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "(UPPER(%s)))") self.assertEqual(sql_compiled, expected_sql) self.assertEqual(params, ("abc", ))
def test_contains_sql_query_case_insensitive_transform(self): qs1 = Author.objects.filter( name__upper__icontains="abc").values("name") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() if USING_DJANGO_3: expected_sql = ( "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "REPLACE(REPLACE(REPLACE(CONCAT('(?i)', UPPER(%s)), " + '"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))') else: expected_sql = ( "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS(CAST(UPPER(tests_author.name) AS STRING), " + "REPLACE(REPLACE(REPLACE(CONCAT('(?i)', (UPPER(%s))), " + '"\\\\", "\\\\\\\\"), "%%", r"\\%%"), "_", r"\\_"))') self.assertEqual(sql_compiled, expected_sql) self.assertEqual(params, ("abc", ))
def test_iexact_sql_query_case_insensitive_function_transform(self): qs1 = Author.objects.filter( name__upper__iexact=F("last_name")).values("name") compiler = SQLCompiler(qs1.query, self.connection, "default") sql_compiled, params = compiler.as_sql() if USING_DJANGO_3: expected_sql = ( "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS(UPPER(tests_author.last_name), " + "CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))" ) else: expected_sql = ( "SELECT tests_author.name FROM tests_author WHERE " + "REGEXP_CONTAINS((UPPER(tests_author.last_name)), " + "CONCAT('^(?i)', CAST(UPPER(tests_author.name) AS STRING), '$'))" ) self.assertEqual(sql_compiled, expected_sql) self.assertEqual(params, ())