SQL y los acentos

El dia de hoy, después de haber logrado finalizar con éxito y a tiempo los objetivos autoimpuestos para el sprint en curso, se divisa un aparente obstáculo: búsquedas 'like' y acentos.

Resulta que debido a algunos cambios en la nueva versión del proyecto, se hicieron algunos cambios a la base de datos, lo que implica realizar un traspaso de datos para que se ajusten a la nueva imagen de la misma.

El primer reto consiste en unificar los datos de los usuarios del sistema con una tabla interna de la aplicación, entre las cuales no existe en apariencia relación alguna. Después de realizar algunas pruebas en el DBVisualizer, se determinó que el siguiente query podría resultar:


select *
from persona
where nombre || ' ' || apellidopaterno || ' ' || apellidomaterno not in
(
select p.nombre || ' ' || apellidopaterno || ' ' || apellidomaterno
from persona p, app_user u
where upper(u.first_name || u.last_name) like upper(p.nombre || '%' || p.apellidopaterno || '%' || p.apellidomaterno)

A primera vista funcionó de maravilla, sin embargo revisando más a detalle los datos se hizo evidente que:

1. No todos los registros de la tabla 'app_user' estaban relacionados con los registros de la tabla 'persona'
2. Algunos nombres en la tabla 'persona' fueron registrados con acentos mientras que en la tabla 'app_user' no tenían acentos, o a la inversa

Buscando por los ilimitados mares de Google se encontró esta referencia, que menciona una posible opción para solucionar esta situación. 

Se menciona que modificando los siguientes parametros de la sesión en Oracle se realizan las búsquedas sin tomar en cuenta los acentos:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

Se continuará realizando algunas pruebas hasta lograr la solución final, por lo menos para este requerimiento.

Santas confusiones Batman!, como diría un miembro del equipo. Resulta que este proyecto no está en Oracle sino en Postgresql... Así que a comenzar a buscar de nuevo.

Después de algunos intentos, Google arrojó una página donde recomiendan utilizar la función to_ascii, pero advierten que no funciona con el encoding UTF-8 (justo el encoding que esta base de datos utiliza!). Así que se toma nota de dicha función para gozar de sus beneficios en otro momento.

Continuando con el Googling se encontró este sitio, donde hay un vasto material acerca de Postgresql. En dicho sitio se encontró una función cuyo autor es Thom Brown y promete buenos resultados.

Al intentar implementar dicha función a través del DBVisualizer, este lanzaba un error o en el mejor de los casos desplegaba un popup solicitando una entrada para procesar la petición, lo que definitivamente daba mala espina.

Así que el siguiente paso fue buscar una buena herramienta para trabajar con postgresql: pgAdmin 3. Después de ejecutar en consola 'emerge -DuNva pgadmin3' y esperar a que se instalaran un total de 5 paquetes y de configurar la conexión, finalmente se pudo realizar la prueba de crear la función en cuestión, esta vez con éxito.

Aquí está el código de la función que se probó:

CREATE OR REPLACE FUNCTION unaccent_string(text)
RETURNS text
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
SELECT translate(
    $1,
    'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
    'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
);
$$;

Ahora sí, al ejecutar el siguiente query, encuentra todos aquellos usuarios que están registrados en la tabla 'persona' aunque estos difieran entre sí por cuestión de acentos

select sin_acentos(upper(p.nombre || ' ' || apellidopaterno || ' ' || apellidomaterno))
from persona p, app_user u
where sin_acentos(upper(u.first_name || u.last_name)) ilike sin_acentos(upper(p.nombre || '%' || p.apellidopaterno || '%' || p.apellidomaterno))

Por Omar Otoniel