sql: switch fts normalization to unidecode (#57009)

debian/control

@@ -28,6 +28,7 @@ Depends: graphviz,
          python3-pyproj,
          python3-quixote,
          python3-requests,
+         pytohn3-unidecode,
          python3-uwsgidecorators,
          python3-vobject,
          python3-xstatic-leaflet,

setup.py

@@ -174,6 +174,7 @@ setup(
         'XStatic-Leaflet',
         'XStatic-Leaflet-GestureHandling',
         'pyproj',
+        'unidecode',
     ],
     package_dir={'wcs': 'wcs'},
     packages=find_packages(),

wcs/sql.py

@@ -20,12 +20,12 @@ import io
 import json
 import re
 import time
-import unicodedata
 import uuid
 
 import psycopg2
 import psycopg2.extensions
 import psycopg2.extras
+import unidecode
 
 try:
     import cPickle as pickle
@@ -284,7 +284,7 @@ class FtsMatch(Criteria):
 
     @classmethod
     def get_fts_value(cls, value):
-        return unicodedata.normalize('NFKD', value).encode('ascii', 'ignore').decode('ascii')
+        return unidecode.unidecode(value)
 
     def as_sql(self):
         return 'fts @@ plainto_tsquery(%%(c%s)s)' % id(self.value)
@@ -3450,7 +3450,7 @@ def get_period_total(
 # latest migration, number + description (description is not used
 # programmaticaly but will make sure git conflicts if two migrations are
 # separately added with the same number)
-SQL_LEVEL = (54, 'add patch column on snapshot table')
+SQL_LEVEL = (55, 'update full text normalisation (switch to unidecode)')
 
 
 def migrate_global_views(conn, cur):
@@ -3587,6 +3587,7 @@ def migrate():
         # 38: extract submission_agent_id to its own column
         # 41: update full text normalization
         # 51: add index on formdata blockdef fields
+        # 55: update full text normalisation (switch to unidecode)
         set_reindex('formdata', 'needed', conn=conn, cur=cur)
     if sql_level < 46:
         from wcs.carddef import CardDef