async def test_use_index_avaiable_in_more_query(self):
sql_ValuesQuery = IntFields.filter(
pk=1).use_index("index_name").values("id").sql()
self.assertEqual(
sql_ValuesQuery,
"SELECT `id` `id` FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1",
)
sql_ValuesListQuery = IntFields.filter(
pk=1).use_index("index_name").values_list("id").sql()
self.assertEqual(
sql_ValuesListQuery,
"SELECT `id` `0` FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1",
)
sql_CountQuery = IntFields.filter(
pk=1).use_index("index_name").count().sql()
self.assertEqual(
sql_CountQuery,
"SELECT COUNT(*) FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1",
)
sql_ExistsQuery = IntFields.filter(
pk=1).use_index("index_name").exists().sql()
self.assertEqual(
sql_ExistsQuery,
"SELECT 1 FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1 LIMIT 1",
)
async def test_use_index(self):
sql = IntFields.filter(pk=1).only("id").use_index("index_name").sql()
self.assertEqual(
sql,
"SELECT `id` `id` FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1",
)
sql_again = IntFields.filter(
pk=1).only("id").use_index("index_name").sql()
self.assertEqual(
sql_again,
"SELECT `id` `id` FROM `intfields` USE INDEX (`index_name`) WHERE `id`=1",
)
async def test_select_for_update(self):
sql1 = IntFields.filter(pk=1).only("id").select_for_update().sql()
sql2 = IntFields.filter(pk=1).only("id").select_for_update(
nowait=True).sql()
sql3 = IntFields.filter(pk=1).only("id").select_for_update(
skip_locked=True).sql()
sql4 = IntFields.filter(pk=1).only("id").select_for_update(
of=("intfields", )).sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "postgres":
self.assertEqual(
sql1,
'SELECT "id" "id" FROM "intfields" WHERE "id"=1 FOR UPDATE',
)
self.assertEqual(
sql2,
'SELECT "id" "id" FROM "intfields" WHERE "id"=1 FOR UPDATE NOWAIT',
)
self.assertEqual(
sql3,
'SELECT "id" "id" FROM "intfields" WHERE "id"=1 FOR UPDATE SKIP LOCKED',
)
self.assertEqual(
sql4,
'SELECT "id" "id" FROM "intfields" WHERE "id"=1 FOR UPDATE OF "intfields"',
)
elif dialect == "mysql":
self.assertEqual(
sql1,
"SELECT `id` `id` FROM `intfields` WHERE `id`=1 FOR UPDATE",
)
self.assertEqual(
sql2,
"SELECT `id` `id` FROM `intfields` WHERE `id`=1 FOR UPDATE NOWAIT",
)
self.assertEqual(
sql3,
"SELECT `id` `id` FROM `intfields` WHERE `id`=1 FOR UPDATE SKIP LOCKED",
)
self.assertEqual(
sql4,
"SELECT `id` `id` FROM `intfields` WHERE `id`=1 FOR UPDATE OF `intfields`",
)
async def test_single_when(self):
category = Case(When(intnum__gte=8, then="big"), default="default")
sql = IntFields.all().annotate(category=category).values(
"intnum", "category").sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum`,CASE WHEN `intnum`>=8 THEN 'big' ELSE 'default' END `category` FROM `intfields`"
else:
expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">=8 THEN \'big\' ELSE \'default\' END "category" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_q_object_when(self):
category = Case(When(Q(intnum__gt=2, intnum__lt=8), then="middle"),
default="default")
sql = IntFields.all().annotate(category=category).values(
"intnum", "category").sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum`,CASE WHEN `intnum`>2 AND `intnum`<8 THEN 'middle' ELSE 'default' END `category` FROM `intfields`"
else:
expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">2 AND "intnum"<8 THEN \'middle\' ELSE \'default\' END "category" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_case_when_in_where(self):
category = Case(When(intnum__gte=8, then="big"),
When(intnum__lte=2, then="small"),
default="middle")
sql = (IntFields.all().annotate(category=category).filter(
category__in=["big", "small"]).values("intnum").sql())
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum` FROM `intfields` WHERE CASE WHEN `intnum`>=8 THEN 'big' WHEN `intnum`<=2 THEN 'small' ELSE 'middle' END IN ('big','small')"
else:
expected_sql = "SELECT \"intnum\" \"intnum\" FROM \"intfields\" WHERE CASE WHEN \"intnum\">=8 THEN 'big' WHEN \"intnum\"<=2 THEN 'small' ELSE 'middle' END IN ('big','small')"
self.assertEqual(sql, expected_sql)
async def test_func_default(self):
category = Case(When(intnum__gte=8, then=8),
default=Coalesce("intnum_null", 10))
sql = IntFields.all().annotate(category=category).values(
"intnum", "category").sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum`,CASE WHEN `intnum`>=8 THEN 8 ELSE COALESCE(`intnum_null`,10) END `category` FROM `intfields`"
else:
expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">=8 THEN 8 ELSE COALESCE("intnum_null",10) END "category" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_AE_default(self):
# AE: ArithmeticExpression
category = Case(When(intnum__gte=8, then=8), default=F("intnum") + 1)
sql = IntFields.all().annotate(category=category).values(
"intnum", "category").sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum`,CASE WHEN `intnum`>=8 THEN 8 ELSE `intnum`+1 END `category` FROM `intfields`"
else:
expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">=8 THEN 8 ELSE "intnum"+1 END "category" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_select_for_update(self):
sql = IntFields.filter(pk=1).only("id").select_for_update().sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "postgres":
self.assertEqual(
sql,
'SELECT "id" "id" FROM "intfields" WHERE "id"=1 FOR UPDATE',
)
elif dialect == "mysql":
self.assertEqual(
sql,
"SELECT `id` `id` FROM `intfields` WHERE `id`=1 FOR UPDATE",
)
async def test_sqlite_func_rand(self):
sql = IntFields.all().annotate(randnum=SqliteRandom()).values("intnum", "randnum").sql()
expected_sql = 'SELECT "intnum" "intnum",RANDOM() "randnum" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_mysql_func_rand_with_seed(self):
sql = IntFields.all().annotate(randnum=Rand(0)).values("intnum", "randnum").sql()
expected_sql = "SELECT `intnum` `intnum`,RAND(0) `randnum` FROM `intfields`"
self.assertEqual(sql, expected_sql)
async def test_async_iter(self):
counter = 0
async for _ in IntFields.all():
counter += 1
self.assertEqual(await IntFields.all().count(), counter)
async def test_force_index(self):
sql = IntFields.filter(pk=1).only("id").force_index("index_name").sql()
self.assertEqual(
sql,
"SELECT `id` `id` FROM `intfields` FORCE INDEX (`index_name`) WHERE `id`=1",
)
async def test_get_raw_sql(self):
sql = IntFields.all().sql()
self.assertRegex(sql, r"^SELECT.+FROM.+")
