FTS5 en SQLite: Búsqueda Completa de Texto

SQLite Soporta la búsqueda completa de texto, FTS5, ¿pero de qué se trata esta característica? Supone un modo rápido de buscar palabras concretas en las columnas de texto correspondientes a la tabla de una base de datos. A continuación encontrarás traducido al español el artículo publicado originalmente en el sitio web de Xojo y que puedes encontrar en este enlace.

Sin la búsqueda completa de texto podrías ejecutar una búsqueda sobre una columna de texto mediante el uso del comando LIKE. Por ejemplo, podrías utilizar el siguiente comando SQL para encontrar todos los libros que tuviesen “cat” en la descripción:

SELECT Title FROM Book WHERE Desc LIKE '%cat%';

Pero dicha sentencia encontrarías en realidad las filas donde la columna Desc contuviese las letras “cat”, incluso si forman parte de otra palabra, como por ejemplo “cater”. De igual modo, el uso de LIKE no utiliza ninguna indexación de la tabla lo que supone que ha de recorrer todas las filas para ver si contiene el valor, y eso supone un impacto en el rendimiento cuando se trata de tablas de gran tamaño.

FTS5 en la práctica

La búsqueda de texto completa supone el modo de evitar estos problemas. Con SQLite puedes activar dicha capacidad creando lo que se denomina una tabla virtual utilizando cualquiera de estos dos motores FTS incluidos en SQLite: FTS4 o FTS5. El soporte FTS5 ha sido añadido a la clase SQLiteDatabase en Xojo 2016 Release 3 y proporciona capacidades de búsqueda más avanzadas, incluyendo el ránking y el resaltado de los resultados. Precisamente, este es el motor en el que se basan los siguientes ejemplos.

Para crear una tabla virtual FTS5, has de utilizar el comando SQL CREATE VIRTUAL TABLE. Este comando SQL crea una tabla virtual BookSearch utilizando FTS5:

CREATE VIRTUAL TABLE BookSearch USING fts5(ID, Title, Desc);

Esta sentencia SQL crea una tabla falsa que está vinculada con el motor de búsqueda completa de texto. Ahora se puede poblar dicha tabla con los datos a buscar, generalmente copiando los datos desde una tabla normal mediante el uso del siguiente comando SQL:

INSERT INTO BookSearch SELECT ID, Title, Desc FROM Book;

Con los datos ya cargados, ya puedes buscar utilizando la instrucción SELECT en combinación con la palabra clave MATCH. Por ejemplo, esta búsqueda SQL** encuentra todos los libros que contengan la palabra “cat” en cualquiera de las columnas:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'cat';

También puedes buscar en columnas concretas de la tabla FTS utilizando el nombre de la columna como prefijo en el criterio. Esta instrucción SQL ejecuta la búsqueda de “cat” sólo sobre la columna Desc:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'Desc:cat';

Para clasificar los resultados de la búsqueda por relevancia (desde los más relevantes a los menos relevantes) puedes utilizar ORDER BY con el valor de clasificación especial. Esta sentencia SQL clasifica los resultados de búsqueda:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'cat' ORDER BY rank;

Puedes emplear comodines en la búsqueda mediante el uso del caracter “*”. Esta sentencia SQL busca en la columna por todo el texto que comience con “prog”, de modo que encontrará “program”, “programming”, etc:

SELECT Title FROM BookSearch WHERE BookSearch MATCH 'prog*';

Como truco final, incluso puedes destacar el texto encontrado en los resultados utilizando la función de resaltado. Esta sentencia SQL emplea los caracteres menor que y mayor que para resaltar los resultados encontrados en la columna Desc(el 2 indica la tercera columna, la cual se corresponde con Desc):

SELECT Title, highlight(BookSearch, 2, '<', '>') AS HighlightDesc FROM BookSearch WHERE BookSearch MATCH 'cat';

Utiliza el método SQLSelect de la clase SQLiteDatabase para enviar cualquiera de los anteriores comandos a la base de datos SQLite.

También hay otras capacidades de búsqueda más avanzadas que se pueden realizar, y sobre las que puedes leer en la documentación oficial de SQLite para FTS5: