Indexación de Texto Completo en SQLite

Introducción

En este laboratorio, aprenderá a utilizar la extensión FTS5 de SQLite para realizar búsquedas de texto completo (full-text searching) eficientes. Creará tablas FTS5, insertará datos, realizará búsquedas y optimizará consultas utilizando cláusulas MATCH. Este laboratorio proporciona una introducción práctica a la indexación de texto completo en SQLite.

Skills Graph

Crear una base de datos SQLite y habilitar FTS5

En este paso, creará una base de datos SQLite y habilitará la extensión FTS5. FTS5 es un módulo que proporciona capacidades de búsqueda de texto completo (full-text search).

Primero, abra su terminal en la VM de LabEx. Su ruta predeterminada es /home/labex/project.

1. Crear una base de datos SQLite:

   Cree un archivo de base de datos llamado books.db y conéctese a él utilizando el comando sqlite3:

   sqlite3 books.db

   Este comando inicia el shell de SQLite, donde puede ejecutar comandos SQL.

2. Habilitar la extensión FTS5:

   Antes de poder usar FTS5, debe habilitarla. Ejecute el siguiente comando SQL:

   CREATE VIRTUAL TABLE book_search USING fts5();

   Si ve un error como Error: no such module: fts5, significa que la extensión FTS5 no está disponible. En la mayoría de las versiones modernas de SQLite, FTS5 se incluye de forma predeterminada. Si encuentra este error, asegúrese de que su versión de SQLite esté actualizada. Si el comando se ejecuta sin errores, la extensión FTS5 está habilitada.

   Este comando crea una tabla virtual llamada book_search utilizando el módulo fts5. Las tablas virtuales son una característica de SQLite que le permite extender la base de datos con funcionalidad personalizada.

3. Definir el esquema de la tabla:

   Ahora, definamos las columnas para nuestra tabla book_search. Incluiremos columnas para title (título), author (autor) y content (contenido). Ejecute el siguiente comando SQL:

   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);

   Este comando crea la tabla book_search con las columnas especificadas. Las columnas title, author y content serán indexadas por FTS5, lo que le permitirá realizar búsquedas de texto completo en ellas.

4. Configurar el tokenizador (Opcional):

   Puede personalizar cómo FTS5 tokeniza el texto especificando un tokenizador (tokenizer). El tokenizador unicode61 proporciona una buena compatibilidad con Unicode. Para usarlo y eliminar diacríticos (acentos), vuelva a crear la tabla con el siguiente comando:

   DROP TABLE IF EXISTS book_search;
   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");

   Este comando primero elimina la tabla book_search existente (si existe) y luego la vuelve a crear con el tokenizador unicode61. La opción remove_diacritics 1 le dice a FTS5 que elimine los diacríticos del texto durante la indexación.

Insertar datos en la tabla FTS5

En este paso, insertará datos en la tabla book_search. Estos datos se utilizarán para la búsqueda de texto completo (full-text searching) en pasos posteriores.

1. Insertar datos de libros:

   Ejecute los siguientes comandos SQL para insertar datos de muestra de libros en la tabla book_search:

   INSERT INTO book_search (title, author, content) VALUES (
       'The Lord of the Rings',
       'J.R.R. Tolkien',
       'A fantasy epic about hobbits, elves, and the battle against Sauron.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'Pride and Prejudice',
       'Jane Austen',
       'A classic novel about love, class, and society in 19th-century England.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'The Hitchhiker''s Guide to the Galaxy',
       'Douglas Adams',
       'A comedic science fiction series following the misadventures of Arthur Dent.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'To Kill a Mockingbird',
       'Harper Lee',
       'A powerful story about racial injustice in the American South.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       '1984',
       'George Orwell',
       'A dystopian novel about totalitarianism and surveillance.'
   );

   Estos comandos insertan cinco filas en la tabla book_search, cada una representando un libro. Se proporcionan los valores de title (título), author (autor) y content (contenido) para cada libro. Observe la comilla simple escapada en 'The Hitchhiker''s Guide to the Galaxy'. Las comillas simples dentro de una cadena deben escaparse duplicándolas.

2. Verificar la inserción de datos:

   Para confirmar que los datos se han agregado correctamente, ejecute el siguiente comando:

   SELECT * FROM book_search;

   Este comando recupera todas las filas y columnas de la tabla book_search. Debería ver los datos que acaba de insertar.

   Resultado esperado:

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
   The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
   To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Realizar búsquedas básicas de texto completo (Full-Text Searches)

En este paso, realizará búsquedas básicas de texto completo utilizando el operador MATCH.

1. Buscar un solo término:

   Para encontrar libros que contengan la palabra "fantasy" (fantasía), ejecute el siguiente comando SQL:

   SELECT * FROM book_search WHERE book_search MATCH 'fantasy';

   Este comando busca el término "fantasy" en todas las columnas de la tabla book_search. El operador MATCH se utiliza para realizar la búsqueda de texto completo.

   Resultado esperado:

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.

2. Buscar múltiples términos:

   Puede buscar múltiples términos separándolos con espacios. Para encontrar libros que contengan tanto "love" (amor) como "society" (sociedad), ejecute el siguiente comando SQL:

   SELECT * FROM book_search WHERE book_search MATCH 'love society';

   Este comando busca filas que contengan tanto "love" como "society" en cualquiera de las columnas indexadas.

   Resultado esperado:

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

3. Buscar dentro de una columna específica:

   Para buscar dentro de una columna específica, especifique el nombre de la columna antes del operador MATCH. Por ejemplo, para encontrar libros con "Orwell" en la columna author (autor), ejecute el siguiente comando SQL:

   SELECT * FROM book_search WHERE author MATCH 'Orwell';

   Este comando busca solo el término "Orwell" en la columna author.

   Resultado esperado:

   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Usar características avanzadas de MATCH

En este paso, explorará las características avanzadas del operador MATCH, incluyendo las búsquedas de prefijo (prefix searches) y los operadores booleanos.

1. Búsqueda de prefijo:

   Utilice el comodín * para realizar una búsqueda de prefijo. Para encontrar libros que contengan palabras que comiencen con "soci", ejecute el siguiente comando SQL:

   SELECT * FROM book_search WHERE book_search MATCH 'soci*';

   Este comando busca términos que comiencen con "soci", como "society" (sociedad).

   Resultado esperado:

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

2. Operadores booleanos:

   FTS5 admite operadores booleanos como AND, OR y NOT para crear consultas de búsqueda más complejas.

   • AND: Encuentra documentos que contengan ambos términos. El comportamiento predeterminado cuando los términos están separados por espacios es AND.

     SELECT * FROM book_search WHERE book_search MATCH 'love AND society';

     Esto es equivalente a SELECT * FROM book_search WHERE book_search MATCH 'love society';

     Resultado esperado:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

   • OR: Encuentra documentos que contengan cualquiera de los términos.

     SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

     Este comando encuentra libros que contengan "love" (amor) o "injustice" (injusticia) (o ambos).

     Resultado esperado:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

   • NOT: Excluye documentos que contengan un término específico.

     SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

     Este comando encuentra libros que no contengan la palabra "fantasy" (fantasía).

     Resultado esperado:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
     1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Resumen

En este laboratorio, ha aprendido a utilizar la extensión FTS5 de SQLite para la búsqueda de texto completo (full-text searching). Creó una tabla FTS5, insertó datos, realizó búsquedas básicas utilizando el operador MATCH y exploró características avanzadas como las búsquedas de prefijo (prefix searches) y los operadores booleanos. Estas habilidades proporcionan una base para construir potentes capacidades de búsqueda en sus aplicaciones SQLite.