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.+")