Instalar tsearsh2 en PostgreSQL con soporte español con ispell

Introducción

Este pequeño manual trata sobre la instalación, configuración y realización de una pequeña prueba del motor de búsqueda para texto[4] Tsearch2[3] usado en PostgreSQL[1][2] con soporte para diccionario Español usando ispell y codificación de caracteres iso-8859-1, este manual se basa en mi experiencia personal usando Tsearch2, como parte del proyecto de software libre HelpDesk fantASTIC[9]. Es importante destacar que actualmente (20 Abril 2008) Tsearch2 está integrado en PostgreSQL 8.3, por lo cual este gran parte de este manual será válido sólo para versiones inferiores a PostgreSQL 8.3, en mi caso he usado Tsearch2 usando ispell con idioma español con PostgreSQL 8.1 y supongo que este manual funcionaría perfectamente bien con PostgreSQL 8.0, al mismo tiempo estoy seguro que con PostgreSQL 8.2 presentará un problema, provocado por un bug presente en un paquete llamado snowball[5] usado por Tsearch2.

El ejemplo aquí descrito ha sido realizado en GNU / Linux Debian Etch 4.0 r0, pero no implica que no pueda ser posible su implementación en otras distribuciones de GNU / Linux o en otros sistemas operativos soportados por PostgreSQL (como en MS Windows).

Se recomienda leer un poco (aunque no es necesario para completar la realización de este ejemplo) el manual de Tsearch2 ubicado en: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

Instalar PostgreSQL en Debian Etch GNU/Linux

Aquí no hay mucho que decir:

#apt-get install postgresql-8.1 postgresql-contrib-8.1 postgresql-client-8.1

Importante: no olvidar instalar el paquete postgresql-contrib, pues es allí donde viene Tsearch2.

Descripción de Ejemplo

Como parte de esta explicación, expongo un pequeño ejemplo, el cual se trata del almacenamiento de "artículos" los cuales constan de un "nombre" y del "contenido" del artículo, el objetivo es poder hacer búsquedas por aproximación entre todos los artículos registrados (usando Tsearch2 y PostgreSQL).

Crear Base de Datos de Prueba

Una vez instalado PostgreSQL se ha creado en nuestro sistema un usuario llamado "postgres", el cual es el administrador del RDBMS, para crear la nueva base de datos, que se llamará: "articulos" es posible hacer:

#su postgres
$createdb articulos

Nota: De aquí en adelante voy a suponer que usted sabe usar alguna herramienta cliente básica de PostgreSQL, en este manual usaré psql, cabe destacar que también es posible utilizar las herramientas gráficas, como por ejemplo pgAdmin3.

Definir un nuevo lenguaje procedural en la Base de Datos

Para este ejemplo crearemos algunos scripts en PL/pgSQL, para lo cual es necesario que nuestra base de datos "articulos" tenga soporte para este lenguaje procedural.

Para instalar plpgsql, ejecutar desde una consola, estando siempre como usuario postgres (o cualquier usuario con los privilegios suficientes):

$createlang  plpgsql articulos

También es posible hacer esto mismo desde una consola SQL dentro de PostgreSQL:

CREATE PROCEDURAL LANGUAGE plpgsql;

Diseño de Estructura de la Base de Datos (mi ejemplo)

Para ejemplo necesitamos sólo una tabla, la cual se llamará "articulo" y tendrá esta estructura:


Para hacer uso de Tsearch2, necesitamos crear un campo más en la tabla "articulo", este campo debe ser de tipo "tsvector" (este tipo de dato "tsvector" se instalará en nuestra base de datos cuando instalemos Tsearch2, algo que se describe más adelante). Tsearch2 usará este campo extra con el fin de crear una "especie" de índice de busqueda (no es un índice como tal a nivel de base de datos).

Nota: Mi proyecto HelpDesk fantASTIC[9], utiliza PEAR MDB2[6] como capa de abstracción de base de datos, MDB2 hace una especie de reconocimiento o revisión de los tipos de datos de los campos de las tablas que se acceden por medio de él, el tipo de dato "tsvector" no es reconocido por PEAR MDB2[6], lo cual genera un error al intentar acceder por medio de este a tablas que contengan campos con este tipo de datos, razón por la cual sugiero crear una tabla extra para almacenar los índices de Tsearch2, y mediante un trigger mantener ambas tablas sincronizadas, en lugar de crear un campo extra dentro de la tabla.

Supongo que este problema (el no reconocimiento de los tipos de datos especiales como "tsvector") también se podría presentar al usar cualquier otro framework de acceso a base de datos, que revise los tipos de datos de los campos y no tenga soporte para el tipo "tsvector", en caso contrario (ya sea que el tipo de datos de los campos no sea de importancia o de que sí exista soporte, para el tipo de datos "tsvector") este modelo seguiría siendo siempre funcional.

A continuación se presenta el modelo DER versión 2 a utilizar en este ejemplo:




La tabla "pg_ts_articulo" almacenará el índice "tsvector", para cada artículo en el campo "articulo_tsearch_idx" de tipo "tsvector".

Cada ves que un artículo se cree, modifique o borre en la tabla "articulo" un trigger se disparará y hará los cambos pertinentes en la tabla "pg_ts_articulo", para que el índice siempre esté actualizado.

Instalar Tsearch2 en la Base de Datos "articulos"

Para instalar Tsearch2 en nuestra base de datos, utilizamos el archivo sql provisto por el paquete "postgresql-contrib", el cual se encuentra en la siguiente ruta:

/usr/share/postgresql/<'version'>/contrib/tsearch2.sql

En mi caso estoy usando PostgreSQL 8.1, por lo cual ejecuto como usuario con privilegios sobre la base de datos "articulos":

$psql -d articulos -f /usr/share/postgresql/8.1/contrib/tsearch2.sql

Aparecerán mensajes como este:

SET
BEGIN
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:13: NOTICE:  CREATE TABLE / PRIMARY KEY creará el índice implícito «pg_ts_dict_pkey» para la tabla «pg_ts_dict»
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
...
CREATE OPERATOR
CREATE OPERATOR
CREATE OPERATOR CLASS
COMMIT

Si todo sale bien, se han agregado las siguientes tablas a la base de datos "articulos":

pg_ts_cfg
pg_ts_cfgmap
pg_ts_dict
pg_ts_parser



Estas tablas son usadas para configurar y parametrizar Tsearch2.

Instalar soporte a lenguaje español de Tsearch2 con ispell usando codificación iso-8859-1

En la página Web de Tsearch2[3] podrá encontrar el archivo tsearch2_espanol.tar.bz2 el cual contiene lo necesario para que Tsearch2 soporte el lenguaje español mediante ispell usando codificiación iso-8859-1. En la página Web de Tsearch2 busque:

tsearch2_espanol.tar.bz2 - spanish ispell dictionary in iso-8859-1 encoding (Denis Torres) [10]

Descargue el archivo y descomprímalo, encontrará los siguientes archivos:

"espanol.aff", fichero de afijos, si desea conocer más sobre estos archivos puede visitar aquí [7].
"espanol.dict", diccionario de palabras en español, si desea conocer más sobre estos archivos puede visitar [7].
"espanol.stop", lista de palabras que no serán indizadas por Tsearch2.[8]
"default_spanish.sql", script sql que parametriza las opciones correspondientes en las tablas de configuración de Tsearch2, para agregar una nueva configuración de diccionario para Tsearch2 llamada "default_spanish".

Copie los siguientes archivos en /usr/local/pgsql/share/contrib/, (si la ruta no existe puede crearla):

espanol.aff
espanol.dict
espanol.stop

Nota: en debian no sería la mejor opción instalar estos archivos en la ruta /usr/local/pgsql/share/contrib/, si usted desea puede copiar estos archivos en otro lugar, pero asegúrese de ajustar el archivo "default_spanish.sql" antes de ejecutarlo.

Luego ejecute el script "default_spanish.sql", en la base de datos "articulos"

$psql -d articulos -f {ruta_al_script}/default_spanish.sql

Crear estructura de datos Diseñada

Aquí no hay mucho que explicar, supongo que el lector sabe crear tablas en SQL. En otras palabras crear las tablas "articulo" y "pg_ts_articulo".

CREATE TABLE articulo(
id_articulo serial NOT NULL,
nombre text NOT NULL,
contenido text,
primary key (id_articulo)
);

CREATE TABLE pg_ts_articulo (
id_articulo integer NOT NULL,
articulo_tsearch_idx tsvector,
PRIMARY KEY (id_articulo)
);

CREATE INDEX idx_articulo_tsearch
ON pg_ts_articulo USING gist (articulo_tsearch_idx);

ALTER TABLE pg_ts_articulo ADD FOREIGN KEY (id_articulo) REFERENCES articulo (id_articulo) ON UPDATE RESTRICT ON DELETE CASCADE;

Es importante hacer notar:

1- Se ha creado para el campo "articulo_tsearch_idx" un índice a nivel de base de datos de tipo: "gist". (Leer el manual de Tsearch2 para obtener detalles sobre los indices GIST).
2- Se ha agregado una relación entre los campos "id_articulo" desde la tabla "articulo" hacia "pg_ts_articulo" con la restricción: ON DELETE CASCADE para que al momento de borrar un "articulo" se borre también su registro relacionado en "pg_ts_articulo".

Crear trigger para la sincronización de los artículos con campo de índice de Tsearch2

Cada vez que se actualice la tabla "articulo" debería actualizarse el índice de Tsearch2 en la tabla "pg_ts_articulo", este trabajo lo hará el siguiente trigger:

CREATE OR REPLACE FUNCTION actualizar_indice_tsearch2() RETURNS "trigger" AS $$
BEGIN

DELETE FROM pg_ts_articulo WHERE pg_ts_articulo.id_articulo = NEW.id_articulo;
INSERT INTO pg_ts_articulo
VALUES (NEW.id_articulo, to_tsvector('default_spanish', coalesce(NEW.nombre) || ' '  || coalesce(NEW.contenido)));

RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER tsvectorupdate AFTER UPDATE OR INSERT ON articulo
FOR EACH ROW EXECUTE PROCEDURE actualizar_indice_tsearch2();

Nota: El indice insertado en "pg_ts_articulo" abarca no sólo el "contenido" sino también el "nombre" del "articulo" para que al momento de realizar una búsqueda se evalúen ambos campos[coalesce(NEW.nombre) || ' ' || coalesce(NEW.contenido)].

Introducir algunos datos de pruebas

INSERT INTO articulo (nombre,contenido) VALUES ('Función echo',  'Muestra todos sus parámetros por la salida definida. echo() no es realmente una función (es una sentencia del lenguaje) de modo que no se requiere el uso de los paréntesis. De hecho, si se indica más de un parámetro, no se pueden incluir los paréntesis.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función sin',  'Devuelve el seno de arg en radianes.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función print_r', 'print_r -- Imprime información legible para humanos sobre una variable.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función print',  'Muestra el valor de cadena por la salida definida. Siempre devuelve el valor 1. print() no es realmente una función (es una sentencia del lenguaje) de modo que no se requiere el uso de los paréntesis.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función array_pop',  'array_pop() extrae y devuelve el último valor de la matriz, acortando la matriz en un elemento. Si matriz  está vacía (o no es una matriz), se regresará NULL.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función array',  ' Devuelve una matriz con los parámetros que se le pasan. A dichos parámetros se les puede dar un índice usando el operador =>. Lea la sección sobre los tipos de matrices para más información sobre matrices. Nota: array() es una construcción del lenguaje que se utiliza para representar matrices literales, no una función regular. La forma "índice => valor" separada por comas, define índices y valores. el índice puede ser de tipo cadena o numérico. Cuando el índice es omitido, se genera automáticamente un índice numérico, empezando en cero. Si el índice es un entero, el siguiente índice generado será igual al índice con número mayor + 1. Note que cuando se definen dos índices idénticos, el último sobre escribe al primero.Tener una coma después del último elemento de la matriz, aunque inusual, es sintácticamente valido. El siguiente ejemplo demuestra cómo crear una matriz bidimensional, cómo especificar claves para matrices asociativas, y cómo especificar índices no consecutivos en matrices normales.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función cos',  'cos() devuelve el coseno del parámetro arg. El parámetro arg está en radianes.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función tan',  'Devuelve la tangente de arg en radianes.');
INSERT INTO articulo (nombre,contenido) VALUES ('Función array_rand',  'array_rand() es bastante útil cuando desea elegir una o más entradas aleatorias de una matriz. Recibe una matriz de entrada y un argumento opcional num_req que especifica cuántas entradas desea seleccionar; si no se precisa éste argumento, recibe un valor por defecto de 1. Si está eligiendo únicamente una entrada, array_rand() devuelve la clave de una entrada al azar. De lo contrario, devuelve una matriz de claves para las entradas aleatorias. Esto se ha hecho de esta forma para que usted pueda elegir claves al igual que valores al azar de la matriz. ');

Probar actualizaciones en ambas tablas

Primero:

SELECT * FROM articulo;

Luego:

SELECT * FROM pg_ts_articulo;

Ambas tablas deben contener datos.

Crear función plpgsql para realizar las búsquedas

CREATE TYPE respuesta_buscar_articulo AS (id_articulo int, nombre text,contenido text, puntaje real);

CREATE OR REPLACE FUNCTION buscar_articulo(text) RETURNS SETOF respuesta_buscar_articulo AS $$
DECLARE
AGUJA ALIAS FOR $1;
RESPUESTA RECORD;
BEGIN

FOR RESPUESTA IN (
SELECT
articulo.id_articulo,
headline('default_spanish',articulo.nombre,tsq) AS nombre,
headline('default_spanish',articulo.contenido,tsq) AS contenido,
rank(articulo_tsearch_idx,tsq) as puntaje
FROM
pg_ts_articulo,
to_tsquery('default_spanish',AGUJA) tsq,
articulo
WHERE
articulo.id_articulo = pg_ts_articulo.id_articulo
AND
articulo_tsearch_idx @@ tsq
ORDER BY puntaje DESC )
LOOP
RETURN NEXT RESPUESTA;
END LOOP;

RETURN;
END;
$$
LANGUAGE plpgsql;

Hacer búsquedas de prueba

A continuación sólo debemos hacer algunas pruebas:

1- Coincidir sólo una palabra:

select * from buscar_articulo('función');

2- Coincidir 2 (ambas a la vez) palabras:

select * from buscar_articulo('función & matriz');

3- Coincidir cualquiera de 2 palabras:

select * from buscar_articulo('función | seno');

Notar que en esta última búsqueda los artículos que involucran ambas palabras "función" y "seno" tienen mayor puntaje.

Enlaces

[1] http://www.postgresql.org
[2] http://es.wikipedia.org/wiki/Postgresql
[3] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
[4] http://en.wikipedia.org/wiki/Full_text_search
[5] http://archives.postgresql.org/pgsql-general/2007-09/msg00794.php
[6] http://pear.php.net/package/MDB2
[7] http://www.datsi.fi.upm.es/%7Ecoes/espell_leame/espell_leame.html
[8] Se agradece a las personas que colaboraron para la elaboración de esta lista de palabras: https://lists.ubuntu.com/archives/ubuntu-ni/2007-August/001921.html
[9] http://sourceforge.net/projects/fantastic/
[10] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/tsearch2_espanol.tar.bz2