segunda-feira, 18 de fevereiro de 2013

Implementando Algoritmo BuscaBR em MySQL

A necessidade de melhoria no acesso aos dados armazenados em grandes Banco de Dados
tem levado as empresas a buscarem formas práticas de tornar as consultas mais eficientes.

Como se já não bastasse às dificuldades que são geradas através da grafia da língua falada ou escrita quer pela ortografia e gramática oficial ou pelas influências externas à língua portuguesa utilizada no Brasil também possui as suas regras ortográficas e gramaticais além de uma grande quantidade de influências internas e externas que dificultam o seu aprendizado. Também o estrangeirismo é muito presente e termina por criar novas palavras que são incorporadas na sua grafia original ou sofrem aportuguesamento, gerando assim, novas dificuldades e às vezes criando exceções as regras existentes.
Isso deixa os bancos de dados cada vez mais desorganizadas e nem sempre temos o retorno esperado quando fazemos uma consulta.

Devido a isso foi criado a rotina SoundEx (registrado por Robert Russell e Margaret Odell) nos Estados Unidos em 1918 para tentar minimizar as informações, a rotina codificava os fonemas para corrigir a grafia errada das palavras, mas a rotina supria as necessidades norte americanas, mas na realidade brasileira comprometeu a eficiência. Devido a isso foi criado uma função capaz de abordar todas as características da língua portuguesa, com base em um estudo das deficiências do SoundEx foi desenvolvido uma análise detalhada dos fonemas da língua portuguesa e também os erros de grafia que foram cometidos. Por fim, chegou-se a função PL/SQL (registrado pelo Oracle) com o nome de BuscaBR e que na língua portuguesa foi bastante eficaz.

O que é o BuscaBR?
Nada mais é do que uma rotina para indexar sua base de dados.

Eu implementei duas funções em MySQL, a principal é a "phonembr" e ela que será utilizada indexar as palavras, a outra função serve apenas para normalizar a string.

drop function if exists phonembr_normalize;
delimiter //
create function phonembr_normalize(str varchar(255))
returns varchar(255)
begin
    declare pos int;
    declare chars_special varchar(255);
    declare chars_normal varchar(255);    
    
    set chars_special = 'ÁÀÃÂÉÊÍÓÔÕÚÜ';
    set chars_normal = 'AAAAEEIOOOUU';    
    
    set str = upper(str);    
    set pos = length(chars_normal);    
    while pos > 0 do
        set str = replace(str, substring(chars_special, pos, 1), substring(chars_normal, pos, 1));
        set pos = pos-1;
    end while;        
    
    set str = trim(str);
    while str regexp ' {2,}' do 
        set str = replace(str, '  ', ' ');
    end while;    
        
    set pos = length(str);
    while pos > 0 do 
        if substring(str, pos, 1) regexp '[^A-Z0-9Ç@._ +-]+' then
            set str = concat(substring(str, 1, pos-1), substring(str, pos+1));
        end if;
        set pos = pos-1;
    end while;        

    return str;
end //
delimiter ;

drop function if exists phonembr;
delimiter //
create function phonembr(str varchar(255))
returns varchar(255)
begin
    declare pos int;
    declare break tinyint;
    declare chars_s varchar(255);
    declare chars_r varchar(255);
        
    set str = phonembr_normalize(str);            
    set chars_s = 'BL,BR,CA,CE,CI,CO,CU,CK,Ç,CH,CS,CT,GE,GI,GM,GL,GR,L,N,MD,MG,MJ,PH,PR,Q,RG,RS,RT,RM,RJ,ST,TR,TL,TS,W,X,Y,Z';
    set chars_r = 'B,B,K,S,S,K,K,K,S,S,S,T,J,J,M,G,G,R,M,M,G,J,F,P,K,G,S,T,SM,J,T,T,T,S,V,S,I,S';    
    
    set break = 0;
    while not break do 
        set str = replace(str, substring_index(chars_s, ',', 1), substring_index(chars_r, ',', 1));    
        if not locate(',', chars_s) then
            set break = 1;
        else
            set chars_s = substring(chars_s, locate(',', chars_s)+1);
            set chars_r = substring(chars_r, locate(',', chars_r)+1);
        end if;    
    end while;                                                                        
    
    if str regexp '[A-Z]+[MRS] ' then
        set str = replace(str, 'M ', ' ');
        set str = replace(str, 'R ', ' ');
        set str = replace(str, 'S ', ' ');
    end if;            
    if str regexp '[A-Z]+[MRS]$' then
        set str = substr(str, 1, length(str)-1);
    end if;
    
    set str = replace(str, 'A', '');
    set str = replace(str, 'E', '');
    set str = replace(str, 'I', '');    
    set str = replace(str, 'O', '');
    set str = replace(str, 'U', '');
    set str = replace(str, 'H', '');    
    
    set pos = length(str);
    while pos > 0 do 
        if substr(str, pos, 1) = substr(str, pos-1, 1) then
            set str = concat(substr(str, 1, pos-1), substr(str, pos+1));
        end if;
        set pos = pos-1;
    end while;   

   return str;
end //
delimiter ;

Execute as funcões acima em sua base de dados e faça o seguinte teste:

select phonembr('casa');
select phonembr('kasa');
select phonembr('caza');

como resultado as três consultas irá retornar KS, ou seja independente se a
palavra foi escrita com S, Z ou K o resultado será o mesmo.

Bom galera este foi a implementação do algoritmo BuscaBR em MySQL, vou deixar um link da documentação do algoritmo www.brunoportfolio.com/arquivos/pdf/BuscaBR_Fonetica.pdf. Espero que vocês tenham gostado, até a próxima.