Indexação de Texto Completo SQLite: Guia Prático com FTS5

Introdução

Neste laboratório, você aprenderá como usar a extensão FTS5 do SQLite para pesquisa de texto completo eficiente. Você criará tabelas FTS5, inserirá dados, realizará pesquisas e otimizará consultas usando cláusulas MATCH. Este laboratório oferece uma introdução prática à indexação de texto completo no SQLite.

Criar um Banco de Dados SQLite e Habilitar FTS5

Nesta etapa, você criará um banco de dados SQLite e habilitará a extensão FTS5. FTS5 é um módulo que fornece recursos de pesquisa de texto completo.

Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Criar um banco de dados SQLite:

Crie um arquivo de banco de dados chamado books.db e conecte-se a ele usando o comando sqlite3:
```
sqlite3 books.db
```
Este comando inicia o shell do SQLite, onde você pode executar comandos SQL.
Habilitar a extensão FTS5:

Antes de usar o FTS5, você precisa habilitá-lo. Execute o seguinte comando SQL:
```
CREATE VIRTUAL TABLE book_search USING fts5();
```
Se você vir um erro como Error: no such module: fts5, isso significa que a extensão FTS5 não está disponível. Na maioria das versões modernas do SQLite, o FTS5 está incluído por padrão. Se você encontrar este erro, certifique-se de que sua versão do SQLite esteja atualizada. Se o comando for executado sem erros, a extensão FTS5 está habilitada.

Este comando cria uma tabela virtual chamada book_search usando o módulo fts5. Tabelas virtuais são um recurso do SQLite que permite estender o banco de dados com funcionalidade personalizada.
Definir o esquema da tabela:

Agora, vamos definir as colunas para nossa tabela book_search. Incluiremos colunas para title (título), author (autor) e content (conteúdo). Execute o seguinte comando SQL:
```
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);
```
Este comando cria a tabela book_search com as colunas especificadas. As colunas title, author e content serão indexadas pelo FTS5, permitindo que você execute pesquisas de texto completo nelas.
Configurar o tokenizador (Opcional):

Você pode personalizar como o FTS5 tokeniza o texto especificando um tokenizador. O tokenizador unicode61 fornece bom suporte Unicode. Para usá-lo e remover diacríticos (acentos), recrie a tabela com o seguinte comando:
```
DROP TABLE IF EXISTS book_search;
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");
```
Este comando primeiro descarta a tabela book_search existente (se existir) e, em seguida, a recria com o tokenizador unicode61. A opção remove_diacritics 1 diz ao FTS5 para remover diacríticos do texto durante a indexação.

Inserir Dados na Tabela FTS5

Nesta etapa, você inserirá dados na tabela book_search. Esses dados serão usados para pesquisa de texto completo nas etapas posteriores.

Inserir dados do livro:

Execute os seguintes comandos SQL para inserir dados de exemplo de livros na tabela 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.'
);

Esses comandos inserem cinco linhas na tabela book_search, cada uma representando um livro. Os valores de title, author e content são fornecidos para cada livro. Observe a aspa simples escapada em 'The Hitchhiker''s Guide to the Galaxy'. Aspas simples dentro de uma string devem ser escapadas dobrando-as.

Verificar a inserção de dados:

Para confirmar se os dados foram adicionados corretamente, execute o seguinte comando:

SELECT * FROM book_search;

Este comando recupera todas as linhas e colunas da tabela book_search. Você deve ver os dados que acabou de inserir.

Saída Esperada:

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 Pesquisas Básicas de Texto Completo

Nesta etapa, você executará pesquisas básicas de texto completo usando o operador MATCH.

Pesquisar por um único termo:

Para encontrar livros contendo a palavra "fantasy" (fantasia), execute o seguinte comando SQL:
```
SELECT * FROM book_search WHERE book_search MATCH 'fantasy';
```
Este comando pesquisa em todas as colunas da tabela book_search o termo "fantasy". O operador MATCH é usado para executar a pesquisa de texto completo.

Saída Esperada:
```
The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
```
Pesquisar por múltiplos termos:

Você pode pesquisar por múltiplos termos separando-os com espaços. Para encontrar livros contendo tanto "love" (amor) quanto "society" (sociedade), execute o seguinte comando SQL:
```
SELECT * FROM book_search WHERE book_search MATCH 'love society';
```
Este comando pesquisa por linhas que contêm tanto "love" quanto "society" em qualquer uma das colunas indexadas.

Saída Esperada:
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```
Pesquisar dentro de uma coluna específica:

Para pesquisar dentro de uma coluna específica, especifique o nome da coluna antes do operador MATCH. Por exemplo, para encontrar livros com "Orwell" na coluna author (autor), execute o seguinte comando SQL:
```
SELECT * FROM book_search WHERE author MATCH 'Orwell';
```
Este comando pesquisa apenas na coluna author o termo "Orwell".

Saída Esperada:
```
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.
```

Usar Recursos Avançados de MATCH

Nesta etapa, você explorará recursos avançados do operador MATCH, incluindo pesquisas de prefixo e operadores booleanos.

Pesquisa de Prefixo:

Use o curinga * para realizar uma pesquisa de prefixo. Para encontrar livros contendo palavras que começam com "soci", execute o seguinte comando SQL:
```
SELECT * FROM book_search WHERE book_search MATCH 'soci*';
```
Este comando pesquisa por termos que começam com "soci", como "society" (sociedade).

Saída Esperada:
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

Operadores Booleanos:

FTS5 suporta operadores booleanos como AND (E), OR (OU) e NOT (NÃO) para criar consultas de pesquisa mais complexas.

AND: Encontrar documentos contendo ambos os termos. O comportamento padrão quando os termos são separados por espaços é AND.
```
SELECT * FROM book_search WHERE book_search MATCH 'love AND society';
```
Isso é equivalente a SELECT * FROM book_search WHERE book_search MATCH 'love society';

Saída Esperada:
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

OR: Encontrar documentos contendo qualquer um dos termos.

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

Este comando encontra livros contendo "love" ou "injustice" (ou ambos).

Saída Esperada:

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: Excluir documentos contendo um termo específico.

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

Este comando encontra livros que não contêm a palavra "fantasy".

Saída Esperada:

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.

Resumo

Neste laboratório, você aprendeu como usar a extensão FTS5 do SQLite para pesquisa de texto completo. Você criou uma tabela FTS5, inseriu dados, realizou pesquisas básicas usando o operador MATCH e explorou recursos avançados como pesquisas de prefixo e operadores booleanos. Essas habilidades fornecem uma base para construir recursos de pesquisa poderosos em seus aplicativos SQLite.

Indexação de Texto Completo no SQLite

Introdução

Criar um Banco de Dados SQLite e Habilitar FTS5

Inserir Dados na Tabela FTS5

Realizar Pesquisas Básicas de Texto Completo

Usar Recursos Avançados de MATCH

Resumo