Consultas case insensitive no Firebird

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Veja neste artigo como realizar consultas case insensitive, ou seja, sem diferenciar letras maiúsculas e minúsculas no banco de dados Firebird. Serão apresentadas soluções adequadas às versões 1.5, 2.0 e 2.1.2 e superiores.

Introdução

A maioria das aplicações que desenvolvemos, independente de plataforma, precisam fazer consultas a bancos de dados com frequência. Os tipos de dados e valores utilizados são vários e por isso as consultas devem estar adequadas às várias situações possíveis.

Por exemplo, fazer uma consulta a uma tabela usando um valor inteiro como filtro é relativamente simples, pois esse tipo de dado é “exato”. Ou seja, um valor numérico inteiro não possui variações como acentos, maiúsculo ou minúsculo, etc.

Porém, quando trabalhamos com colunas do tipo texto, enfrentamos esse tipo de dificuldade que muitas vezes atrapalham a realização de queries. O fator que mais causa problema com esse tipo de consulta é a variação de letras maiúsculas e minúsculas. Afinal, o usuário que está buscando um registro em sua aplicação, não deve ser obrigado a saber exatamente como o texto está escrito, se totalmente em maiúsculo, em minúsculo, ou com variações.

Suponha, por exemplo, uma tabela de clientes com a seguinte estrutura.

Estrutura da tabela de clientes

Figura 1: Estrutura da tabela de clientes

O usuário de uma aplicação que acesse essa tabela pode desejar pesquisar os clientes por código, nesse caso ele deve informar um valor inteiro e que será facilmente localizado no banco de dados. Porém, ele pode desejar consultar pelo nome ou endereço do cliente, nesse caso, independente de como os dados estão gravados e de como o valor será digitado no campo de busca da aplicação, os registros devem ser recuperados. Para esses casos, precisamos de uma consulta CASE INSENSITIVE, ou seja, que desconsidere as diferenças entre letras maiúsculas e minúsculas.

Além disso, a consulta não pode perder desempenho por causa dessa dificuldade. Os resultados devem ser exibidos o mais rápido possível. Percebemos então que será necessário utilizar algum recurso para garantir o desempenho da aplicação, como criar ÍNDICES nas colunas de pesquisa.

Nesse artigo veremos como contornar essa situação utilizando o banco de dados Firebird. Serão apresentadas soluções adequadas às versões 1.5, 2.0 e 2.1.2 desse SGBD.

Colunas case insensitive no Firebird

Uma forma de realizar consultas case insensitive é converter tanto o texto da coluna quanto o valor buscado para maiúsculo. Porém, esse método tem algumas limitações, pois nem todos os caracteres podem ser convertidos para maiúsculo, por exemplo, em alguns idiomas europeus.

Para evitar esse problema, é preciso definir o mesmo COLLATION para os dois textos, de forma a torná-los compatíveis. Como foi dado o exemplo das linguagens europeias, nesse artigo utilizaremos o collation DE_DE e o charset do banco é o ISO8859_1.

O collation pode ser definido no momento da criação da tabela ou direto na consulta, como veremos a seguir.

Listagem 1: Definindo o collation na criação da tabela

CREATE TABLE CLIENTES
(
	CODIGO	INT NOT NULL PRIMARY KEY,
	NOME		VARCHAR(100) COLLATE DE_DE,
	ENDERECO	VARCHAR(100) COLLATE DE_DE
)

Listagem 2: Definindo o collation na consulta

SELECT CODIGO, NOME COLLATE DE_DE, ENDERECO COLLATE DE_DE
FROM CLIENTES

Então já sabemos como contornar o problema de compatibilidade entre idiomas, resta agora aplicar um método para garantir o bom desempenho da consulta. A seguir veremos como resolver isso em algumas versões do Firebird.

Solução para o Firebird 1.5

Precisamos de um índice para as colunas NOME e ENDERECO, mas a consulta não será feita diretamente pelo valor da coluna, pois este será convertido para maiúsculo. Como no Firebird 1.5 não é possível definir um índice que funcione sobre uma função, uma saída é criar colunas cujo valor será o conteúdo do nome e endereço já convertidos para maiúsculo. Essas colunas podem ser preenchidas em um trigger que seja executado em inserções e alterações.

Os códigos a seguir mostram como fazer isso.

Listagem 3: Criando a tabela com colunas auxiliares

CREATE TABLE CLIENTES
(
	CODIGO		INT NOT NULL PRIMARY KEY,
	NOME			VARCHAR(100) COLLATE DE_DE,
	NOME_MAIUSC	VARCHAR(100) COLLATE DE_DE,
	ENDERECO		VARCHAR(100) COLLATE DE_DE,
	ENDERECO_MAIUSC	VARCHAR(100) COLLATE DE_DE
)

Teríamos então o seguinte trigger para preencher as colunas auxiliares.

Listagem 4: Trigger para preencher as colunas auxiliares

CREATE TRIGGER TGR_CLIENTES_BIU FOR CLIENTES
ACTIVE
BEFORE INSERT OR UPDATE
AS
BEGIN
  NEW.NOME_MAIUSC = UPPER (NEW.NOME);
  NEW.ENDERECO_MAIUSC = UPPER (NEW.ENDERECO);
END

Agora sim podemos criar os índices sobre as colunas em maiúsculo, pois elas serão utilizadas para consulta.

Listagem 5: Criando índice sobre as colunas auxiliares

CREATE INDEX IDX_NOME ON CLIENTES (NOME_MAIUSC);
CREATE INDEX IDX_ENDERECO ON CLIENTES (ENDERECO_MAIUSC);

Na consulta, bastaria aplicar o collate no texto a ser buscado, pois a coluna já está devidamente configurada.

Listagem 6: Consulta pelas colunas auxiliares

SELECT * FROM CLIENTES WHERE NOME_MAIUSC = UPPER(:BUSCA COLLATE DE_DE)

SELECT * FROM CLIENTES WHERE ENDERECO_MAIUSC = UPPER(:BUSCA COLLATE DE_DE)

De fato essa solução é um tanto “trabalhosa” e requer várias linhas de código adicionais. Na versão 2.0 isso pode ser resolvido mais facilmente, como veremos a seguir.

Solução para o Firebird 2.0

Nessa versão foi inserido um recurso que permite criar índices sobre expressões e não apenas sobre colunas “puras”. Nesse caso, podemos criar um índice que funcione sobre as colunas NOME e ENDERECO já aplicadas na função UPPER e com o collate.

Listagem 7: Criação de índices sobre a função UPPER

CREATE INDEX IDX_NOME ON CLIENTES COMPUTED BY (UPPER (NOME COLLATE DE_DE));

CREATE INDEX IDX_ENDERECO ON CLIENTES COMPUTED BY (UPPER (ENDERECO COLLATE DE_DE));

Assim podemos fazer a consulta diretamente pelas colunas originais, utilizando a mesma expressão definida no índice.

Listagem 8: Consulta pelas colunas originais com índice

SELECT * FROM CLIENTES WHERE UPPER (NOME COLLATE DE_DE) = UPPER(:BUSCA COLLATE DE_DE)

SELECT * FROM CLIENTES WHERE UPPER (ENDERECO COLLATE DE_DE) = UPPER(:BUSCA COLLATE DE_DE)

Como vemos, a solução nessa versão do Firebird é bem mais prática e simples de se aplicar. Porém, veremos que na versão 2.1.2 é possível fazer o mesmo utilizando ainda menos código.

Solução a partir do Firebird 2.1.2

A partir da versão 2.1.2 é possível utilizar um novo collation chamado UNICODE_CI que funciona para o conjunto de caracteres UTF8, um padrão universal que busca eliminar incompatibilidades entre idiomas, reunindo vários tipos de símbolos gráficos (caracteres).

Para definir o charset do banco de dados, basta selecionar a opção correta no momento da criação do mesmo. A figura a seguir mostra a tela de criação de um database no IBExpert, note que há um campo “Charset” em destaque, nele deve ser selecionada a opção “UTF8”.

Definindo o charset do banco

Figura 2: Definindo o charset do banco

O “CI” no nome do collation significa exatamente CASE INSENSITIVE e faz com que na utilização da coluna sejam desconsideradas diferenças entre letras maiúsculas e minúsculas.

Assim, poderíamos alterar o código da Listagem 1 para usar esse novo collation.

Listagem 9: Criando a tabela com o collation UNICODE_CI

CREATE TABLE CLIENTES
(
	CODIGO	INT NOT NULL PRIMARY KEY,
	NOME		VARCHAR(100) COLLATE UNICODE_CI,
	ENDERECO	VARCHAR(100) COLLATE UNICODE_CI
)

Com isso, a criação dos índices poderia ser feita da forma mais comum, como vemos a seguir.

Listagem 10: Criação dos índices normalmente

CREATE INDEX IDX_NOME ON CLIENTES(NOME);
CREATE INDEX IDX_ENDERECO ON CLIENTES(ENDERECO);

A consulta também não precisaria ter o collation ou a função UPPER.

Listagem 11: Consulta usando o collation UNICODE_CI

SELECT * FROM CLIENTES WHERE NOME = :BUSCA

SELECT * FROM CLIENTES WHERE ENDERECO = :BUSCA

Conclusão

Com a introdução do collation UNICODE_CI, a realização de consultas case insensitive se tornou bem mais simples (vale lembrar que esse collation foi criado para o charset UTF8, caso esse não seja o charset do banco, essa solução não irá funcionar). Porém, nem sempre é possível migrar a versão do banco, portanto, aqui foram apresentadas soluções para três diferentes versões do Firebird.

Espero que as informações apresentadas nesse artigo possam ser úteis. Até a próxima oportunidade.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?