Django MySql全文搜索有效,但不适用于测试(Django MySql Fulltext search works but not on tests)

35 IT屋

I used this SO question to enable full text search on a mysql db in Django application.

# models.py

class CaseSnapshot(BaseModel):
    text = models.TextField(blank=True)


class Search(models.Lookup):
    lookup_name = "search"

    def as_mysql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return f"MATCH (%s) AGAINST (%s IN BOOLEAN MODE)" % (lhs, rhs), params


models.TextField.register_lookup(Search)

Because Django does not support full text search on mysql, we have to add our index, so we modify the generated migration file:

# Generated by Django 2.2 on 2020-04-28 03:41

from django.db import migrations, models

# Table details
table_name = "by_api_casesnapshot"
field_name = "text"
index_name = f"{table_name}_{field_name}_index"


class Migration(migrations.Migration):

    dependencies = [("by_api", "0033_add_tag_color")]

    operations = [
        migrations.CreateModel(...), # As auto-generated
        migrations.RunSQL(
            f"CREATE FULLTEXT INDEX {index_name} ON {table_name} ({field_name})",
            f"DROP INDEX {index_name} ON {table_name}",
        ),
    ]

Checking the Work

# dbshell

mysql> SHOW INDEX FROM by_api_casesnapshot;

+---------------------+--------------------------------+-------------+-----+------------+-----+
| Table               | Key_name                       | Column_name | ... | Index_type | ... |
+---------------------+--------------------------------+-------------+-----+------------+-----+
| by_api_casesnapshot | PRIMARY                        | id          | ... | BTREE      | ... |
| by_api_casesnapshot | by_api_casesnapshot_text_index | text        | ... | FULLTEXT   | ... |
+---------------------+--------------------------------+-------------+-----+------------+-----+
#shell 
>>> snap = CaseSnapshot.objects.create(text="XXX")

# Regular query first
>>> CaseSnapshot.objects.filter(text__contains="X") 
<QuerySet [<CaseSnapshot pk=1>]>

# Now test custom search lookup
>>> CaseSnapshot.objects.filter(text__search="XXX")
<QuerySet [<CaseSnapshot pk=1>]>

>>> CaseSnapshot.objects.filter(text__search="X*")
<QuerySet [<CaseSnapshot pk=1>]>

>>> CaseSnapshot.objects.filter(text__search="X*").query.__str__()

'SELECT `by_api_casesnapshot`.`id`, `by_api_casesnapshot`.`text` FROM `by_api_casesnapshot` WHERE MATCH (`by_api_casesnapshot`.`text`) AGAINST (X* IN BOOLEAN M
ODE)'

All that works exactly as intended so far.

Issue

Here is where things fail - all queries using the __search lookups during test runs return an queryset empty set, even thought the generated query is identical.

Tests

from django.test import TestCase
from by_api.models import CaseSnapshot


class TestModels(TestCase):
    def test_search(self):
        CaseSnapshot.objects.create(text="XXX")
        rv1 = CaseSnapshot.objects.filter(text__contains="XXX")
        # Passes
        self.assertEqual(rv1.count(), 1)

        rv2 = CaseSnapshot.objects.filter(text__search="XXX")
        # Fails - count i
        self.assertEqual(rv2.count(), 1)


I tried to debug this with pdb inside the test run, and note the generated query is identical to the one produced above in the shell environment:

>>> rv2.query.__str__())
'SELECT `by_api_casesnapshot`.`id`, `by_api_casesnapshot`.`text` FROM `by_api_casesnapshot` WHERE MATCH (`by_api_casesnapshot`.`text`) AGAINST (XXX IN BOOLEAN MODE)'

Question

Why does use of __search lookup during tests produce empty queryset?

What I have tried

Update

I just noticed if I use unittest.TestCase instead of django.test.TestCase the test passes - could this be related to how django's test case wraps db transactions?

解决方案

Answer from Adam Chainz frmo django-mysql library:

InnoDB doesn't make changes to full text indexes until commits. You can use TransactionTestCase to get around this.

I change the test class to use TransactionTestCase and the tests now pass.

我使用了 SO问题来在mysql数据库中启用全文搜索Django应用程序。



 #models.py 

class CaseSnapshot(BaseModel ):
text = models.TextField(blank = True)


class Search(models.Lookup):
lookup_name =" search"

def as_mysql(self,编译器,连接):
lhs,lhs_params = self.process_lhs(编译器,连接)
rhs,rhs_params = self.process_rhs(编译器,连接)
参数= lhs_params + rhs_params
返回f"匹配(%s)反对(%s在布尔模式下)"%(lhs,rhs),参数


models.TextField.register_lookup (搜索)


由于Django不支持mysql全文搜索,因此我们必须添加索引,因此我们修改了生成的迁移文件:



 #由Django 2.2于2020-04-28 03:41由django.db导入迁移,模型

生成

#表详细信息
table_name =" by_api_casesnapshot"
field_name ="文本"
index_name = f" {table_name} _ {field_name} _index"


类迁移(migrations.Migration):

依赖关系= [(" by_api"," 0033_add_tag_color")]

操作= [
migrations.CreateModel(... ),#作为自动生成的
迁移。RunSQL(
f"在CREATE FULLTEXT INDEX {index_name} ON {table_name}({field_name})"",
f" DROP INDEX {index_name} ON {table_name }",
),
]



检查工作



 #dbshel​​l 

mysql>从by_api_casesnapshot显示索引;

+ --------------------- + ------------------- ------------- + ------------- + ----- + ------------ + --- -+
|桌子键名|列名| ... |索引类型| ... |
+ --------------------- + ----------------------- --------- + ------------- + ----- + ------------ + ----- +
| by_api_casesnapshot |主要| id | ... | BTREE | ... |
| by_api_casesnapshot | by_api_casesnapshot_text_index |文字| ... |全文| ... |
+ --------------------- + ----------------------- --------- + ------------- + ----- + ------------ + ----- +


  #shell 
>> snap = CaseSnapshot.objects.create(text =" XXX")

#常规查询优先
>>> CaseSnapshot.objects.filter(text__contains =" X")
< QuerySet [< CaseSnapshot pk = 1>]>

#现在测试自定义搜索查找
>>> CaseSnapshot.objects.filter(text__search =" XXX")
< QuerySet [< CaseSnapshot pk = 1>]>

>> CaseSnapshot.objects.filter(text__search =" X *")
< QuerySet [< CaseSnapshot pk = 1>]>

>> CaseSnapshot.objects.filter(text__search =" X *")。query .__ str __()

'select`by_api_casesnapshot`.`id`,`by_api_casesnapshot`.`text` from`by_api_casesnapshot`在哪里匹配(`by_api_casesnapshot`.`text`)反对(X * IN BOOLEAN M
ODE)'



到目前为止,所有操作均按预期进行。



问题



这是失败的地方-使用 __ search 查找的所有查询在测试运行期间,即使认为生成的查询是



测试



 来自django.test从by_api.models导入TestCase 
导入CaseSnapshot


类TestModels(TestCase):
def test_search(self):
CaseSnapshot.objects .create(text =" XXX")
rv1 = CaseSnapshot.objects.filter(text__contains =" XXX")
#传递
self.assertEqual(r v1.count(),1)

rv2 = CaseSnapshot.objects.filter(text__search =" XXX")
#失败-计算
self.assertEqual(rv2.count (),1)




我尝试使用pdb进行调试测试运行,并注意生成的查询与上面在shell环境中生成的查询相同:



 >> ; rv2.query .__ str __())
'从`by_api_casesnapshot```````id```by_api_casesnapshot`.`text`匹配(`by_api_casesnapshot`.text`)再次(XXX在布尔模式下) )'





问题



为什么在测试期间使用 __ search 查找会产生空查询集?



我尝试过的事情





更新



我只是注意到如果使用 unittest.TestCase 而不是 django.test.TestCase 测试通过-这可能与django的测试用例包装方式有关db事务?


解决方案

来自亚当链的答案z frmo django-mysql库:




在提交之前,InnoDB不会对全文索引进行更改。您可以使用TransactionTestCase解决此问题。




我将测试类更改为使用 TransactionTestCase 并且测试现在通过。


本文地址:IT屋 » Django MySql全文搜索有效,但不适用于测试