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.
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.dbEste 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.
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.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.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.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.
