reorganização testes memsql

luizanisio · Mar 3, 2022 · 34130c8 · 34130c8
1 parent e9c59a7
commit 34130c8
Show file tree

Hide file tree

Showing 8 changed files with 2,267 additions and 0 deletions.
diff --git a/testes_memsql/PesquisaMemSQL.md b/testes_memsql/PesquisaMemSQL.md
@@ -0,0 +1,36 @@
+# Pesquisa textual em documentos Python + MemSQL
+Essa é uma proposta de pesquisa textual combinando a classe Python PesquisaBR com os recursos nativos do MemSQL, formando a classe python PesquisaBRMemSQL, permitindo busca em campos textuais e critérios de proximidade textual. O objetivo é refinar pesquisas textuais básicas do MemSQL com operadores de proximidade.
+
+### Estão disponíveis nesse repositório:
+<ul>
+  <li>Classe python <b>PesquisaBRMemSQL()</b>: classe responsável por fazer um link entre a classe que constrói e avalia os critérios avançados, e os critérios básicos de pesquisa do MemSQL</br>
+  <li>Classe python <b>PesquisaBR</b>(https://github.com/luizanisio/PesquisaTextualBR) que recebe um documento e um critério de pesquisa e retorna a avaliação.</li>
+  <li>Testes da classe que permitem validar todos os critérios e funcionalidades implementadas</li>
+  <li>Conversor de pesquisas com critérios avançados para critérios simples AND OR NOT aceitos pelo MemSQL</li>
+  <li>Scripts de criaçãod e tabelas e funções utilizadas pela classe PesquisaBrMemSQL()</li>
+</ul>
+
+### Objetos de banco de dados:
+<ul>
+  <li>Tabela <b>tipo_documento_cfg</b>: permite configurar tipos/domínios de documentos de tabelas que estão em outras bases do MemSQL para que o componente saiba de onde buscar os textos para fazer as análises.</li>
+  <li>Tabela <b>sessao_pesquisa</b>: tabela que consolida uma pesquisa realizada. É criado um registro com o nome da sessão de pesquisa (a aplicação pode criar um nome qualquer único) para referenciar a pesquisa realizada. Algumas queries são construídas e executadas com os critérios de pesquisa do MemSQL para fazer uma <i>pré pesquisa</i> que depois é refinada pela classe PesquisaBR com seus critérios de proximidade.</li>
+  <li>Tabela <b>sessao_pesquisa_doc</b>: tabela que consolida a lista de ids ou sequenciais de documentos que correspondem aos critérios de pesquisa. Pode-se incluir uma lista de ids e solicitar que a pesquisa seja refinada pelos critérios ou fazer uma pesquisa na base toda.</li>
+  <li>Tabela <b>mapa_pesquisa</b>: tabela que guarda um cache do mapa do documento a ser analisado. Esse mapa é criado quando um documento vai ser analisado pela classe PesquisaBR, para acelerar novas pesquisas, o mapa é recuperado já pronto. Existe um controle de validade do mapa de acordo com a data de atualização do documento original.</li>
+  <li>Tabela <b>cache_pesquisa</b>: tabela responsável por guardar um cache de comparação entre um critério de pesquisa e um documento. Caso um mesmo critério seja rodado repetidas vezes, o cache com o resultado é carregado e a análise não precisa ser realizada novamente. Existe um controle de validade do cache de análise do documento de acordo com a data de atualização do documento original.</li>
+  <li>Função <b>f_pre_processar_texto(texto)</b>: Essa função é responsável pelo pré-processamento de textos diretamente no banco com o mesmo pré-processamento da classe PesquisaBR. A ideia é permitir uma ingestão de dados diretamente no banco de dados sem a necessidade de chamada de uma api. Vários exemplos serão colocados aqui para demonstrar o uso da função e/ou da classe diretamente.</li>
+</ul>
+
+### Recursos da classe <b>PesquisaBRMemSQL()</b>:
+<ul>
+   <li>Conexão com o banco MemSQL: realizada por arquivo de configuração, é necessário que o usuário tenha acesso de escrita às tabelas do schema <b>pesquisabr</b>, e apenas leitura às tabelas com os textos originais.</li>
+   <li>Limpeza automática do cache: ao instanciar a classe, ela conecta com o MemSQL e realiza limpeza dos cacher de mapas e comparações não utilizados por mais de 180 dias. Esse parêmtro pode ser configurado.
+   <li>Método pesquisar: recebe como parâmteros o nome da sessão de pesquisa que será construída, o tipo de documento/domínio que será analisado e os critérios de pesquisa aceitos pela classe PesquisaBR. Pode-se realizar uma nova pesquisa, unir uma pesquisa existente com mais resultados de outros critérios de pesquisa ou filtrar documentos já incluídos na sessão de pesquisa.</li>
+   <li>Método pesquisar(sessao, tipo_documento, criterios, tipo: nova/união/filtro): recebe como parâmteros o nome da sessão de pesquisa que será construída, o tipo de documento/domínio que será analisado e os critérios de pesquisa aceitos pela classe PesquisaBR. Pode-se realizar uma nova pesquisa, unir uma pesquisa existente com mais resultados de outros critérios de pesquisa ou filtrar documentos já incluídos na sessão de pesquisa.</li>
+   <li>Método retorno(sessao): recebe como parâmteros o nome da sessão de pesquisa e retorna os ids que foram aceitos pelo critério de pesquisa, bem como um resumo de quantos documentos foram incluídos pela pré-pesquisa do MemSQL, quantos foram removidos e quantos foram aceitos após o refinamento da pesquisa.</li>
+</ul>
+
+### *** em breve...
+<ul>
+  <li>Serviço python para responder solicitações de pesquisa combinando as classes PesquisaBR e PesquisaBRMemSQL.</li>
+  <li>Scripts de exeplo para ingestão de dados e testes</li>
+</ul>
diff --git a/testes_memsql/__init__.py b/testes_memsql/__init__.py
@@ -0,0 +1,11 @@
+name = "pesquisabr"
+### Pode ser necessário ajustes nesse arquivo
+### Como está ele funciona sem dependências com o banco de dados
+
+#import pesquisabr.pesquisabr
+#import pesquisabr.pesquisabr
+#import pesquisabr.pesquisabr 
+#import pesquisabr.util
+#import pesquisabr.util_memsql
+from pesquisabr.pesquisabr import *
+#import pesquisabr.pesquisabr_memsql
diff --git a/testes_memsql/db_funcoes.sql b/testes_memsql/db_funcoes.sql
@@ -0,0 +1,55 @@
+DELIMITER //  
+# recebe um termo ou um texto, singulariza e remove pronomes oblíquos
+CREATE OR REPLACE FUNCTION pesquisabr.f_pre_processar_texto(texto longtext, usar_quebra_br boolean default false) RETURNS longtext AS
+  DECLARE 
+	  _txt longtext;
+  BEGIN
+    /* quebra de linha*/
+    _txt = REGEXP_REPLACE(lower(texto),"(<\\s*\\/?\\s*[bB][rR]\\s*\\/?\\s*>)",'\n','g');
+
+	/*acentos - usar [áâ...] não funcionou corretamente*/
+	_txt = REGEXP_REPLACE(_txt,'á|â|ä|à|ã', 'a', 'g');     
+	_txt = REGEXP_REPLACE(_txt,'é|ê|ë|è', 'e', 'g');
+	_txt = REGEXP_REPLACE(_txt,'í|ì|ï|î', 'i', 'g');
+	_txt = REGEXP_REPLACE(_txt,'ó|ò|ô|õ|ö', 'o', 'g');
+	_txt = REGEXP_REPLACE(_txt,'ñ', 'n', 'g');
+	_txt = REGEXP_REPLACE(_txt,'ú|ù|û|ü', 'u', 'g');
+	_txt = REGEXP_REPLACE(_txt,'ç', 'c', 'g');
+    _txt = REGEXP_REPLACE(_txt,'§',' parágrafo ','g');
+    _txt = REGEXP_REPLACE(_txt,'{|\\[','(','g');
+    _txt = REGEXP_REPLACE(_txt,'}|\\]',')','g');
+    /* aspas */
+    _txt = REGEXP_REPLACE(_txt,"'|´|`|“|”",'"','g');
+
+    /* separa números de outras palavras*/
+    _txt=REGEXP_REPLACE(_txt,'([a-z])([0-9])|([0-9])([a-z])','\\1 \\2','g');
+    /* números*/
+	_txt=REGEXP_REPLACE(_txt,'([0-9])([\\.,]+)([0-9])','\\1\\3','g');
+	_txt=REGEXP_REPLACE(_txt,'([0-9])([\\.,]+)(\\.,)','\\1\\3','g');
+
+    /* ignora símbolos que sobrarem*/
+	_txt=REGEXP_REPLACE(_txt,'([^a-z0-9\n])+',' ','g');
+
+    /* limpa espaços*/
+    _txt=REGEXP_REPLACE(_txt,' +',' ','g');
+
+	/* plurais */
+	_txt=REGEXP_REPLACE(_txt,'(^| |"|\n)(lei|pai)(s)( |$|"|\n)','\\1\\2\\4','g');
+	_txt=REGEXP_REPLACE(_txt,'(oes|aes)( |$|"|\n)','ao\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(ais)( |$|"|\n)','al\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(eis)( |$|"|\n)','el\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(ois)( |$|"|\n)','ol\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(les)( |$|"|\n)','l\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(res)( |$|"|\n)','r\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(zes)( |$|"|\n)','z\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(is)( |$|"|\n)','il\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(ns)( |$|"|\n)','m\\2','g');
+	_txt=REGEXP_REPLACE(_txt,'(a|e|i|o|u)(s)($| |"|\n)','\\1\\3','g');		
+
+    if usar_quebra_br THEN
+       _txt = REGEXP_REPLACE(_txt,'(\\n)|(\\r)','<br>','ig');
+    end if;
+    /* fim */
+   	return trim(_txt);
+  END //
+DELIMITER ;	
diff --git a/testes_memsql/db_tabelas.sql b/testes_memsql/db_tabelas.sql
@@ -0,0 +1,94 @@
+create database pesquisabr;
+create user usr_pesquisabr IDENTIFIED BY 'pesquisabr2020';
+grant all on pesquisabr.* to usr_pesquisabr;
+
+drop table pesquisabr.tipo_documento_cfg; 
+CREATE TABLE pesquisabr.tipo_documento_cfg (
+  tipo_documento varchar(100) NOT NULL COMMENT 'nome do tipo documento',
+  dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela',
+  tabela varchar(100) NOT NULL COMMENT 'nome da tabela criada no memsql para pesquisa textual com database ex: minhabase.textos ',
+  campo_texto varchar(100) NOT NULL COMMENT 'nome do campo que contém o texto original',
+  campo_score varchar(100) NOT NULL COMMENT 'nome do campo score na tabela - opcional - o score é somado ao score da pesquisa ',
+  campo_id varchar(100) NULL COMMENT 'nome do campo id da tabela - do tipo varchar - campo_id ou campo_seq devem estar preenchidos',
+  campo_seq varchar(100) NULL COMMENT 'nome do campo seq da tabela - do tipo int - campo_id ou campo_seq devem estar preenchidos',
+  campo_data varchar(100) NOT NULL COMMENT 'nome do campo que contém a data de controle de alteração do texto',
+  ind_substituir_texto bool not null default False COMMENT 'True indica que ao gerar o mapa de pesquisa, o texto é atualizado para o texto pré processado',
+  PRIMARY KEY (tipo_documento),
+  KEY (tipo_documento),
+  SHARD KEY (tipo_documento)
+  );
+
+
+drop table pesquisabr.sessao_pesquisa;
+CREATE TABLE pesquisabr.sessao_pesquisa (
+  sessao varchar(100) NOT NULL COMMENT 'nome da sessão de pesquisa',
+  tipo_documento varchar(100) not null COMMENT 'tipo do documento',
+  dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela',
+  ind_status char(1) DEFAULT 'N' COMMENT 'status do filtro N novo, I iniciado, F finalizado, M executando Match, P pré-filtrado',
+  dthr_status timestamp default null COMMENT 'data e hora do último status - serve para medir performance e fazer um call back',
+  qtd_pesquisa_rapida int default 0 not null COMMENT 'quantidade de documentos iniciais na sessão ou incluídos pela pré pesquisa AON',
+  qtd_doc_removidos int default 0 not null COMMENT 'quantidade de documentos removidos pelos filtros - serve para medir performance e fazer um call back',
+  qtd_doc_aceitos int default 0 not null COMMENT 'quantidade de documentos que atendem à pesquisa - serve para medir performance e fazer um call back',
+  PRIMARY KEY (sessao),
+  KEY (sessao, dthr),
+  SHARD KEY (sessao)
+  );
+
+drop table pesquisabr.sessao_pesquisa_doc;
+CREATE TABLE pesquisabr.sessao_pesquisa_doc (
+  sessao varchar(100) NOT NULL COMMENT 'nome da sessão de pesquisa' ,
+  id_documento varchar(100) not null COMMENT 'id do documento - id ou seq devem ser preenchidos com a chave original do documento',
+  seq_documento int not null COMMENT 'seq do documento - id ou seq devem ser preenchidos com a chave original do documento',
+  score float DEFAULT 0 COMMENT 'score da pesquisa + score do documento',
+  dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela' ,
+  ind_status char(1) DEFAULT 'N' COMMENT 'status do filtro N novo, I iniciado, F finalizado, M executando Match, P pré-filtrado',
+  dthr_status timestamp default null COMMENT 'data e hora do último status - serve para medir performance e fazer um call back',
+  KEY (sessao),
+  KEY (sessao, dthr),
+  SHARD KEY (sessao)
+  );
+
+drop table pesquisabr.mapa_pesquisa;
+CREATE TABLE pesquisabr.mapa_pesquisa (
+    tipo_documento varchar(100) NULL COMMENT 'nome do tipo documento',
+    id_documento varchar(100) NULL COMMENT 'id do documento',
+    seq_documento int NOT NULL COMMENT 'seq do documento',
+    mapa json COMMENT 'mapa do documento',
+    tamanho_mapa int comment 'tamanho do mapa de pesquisa',
+    tamanho_texto int comment 'tamanho do texto original',
+    dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela' ,
+    dthr_utilizacao timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data da última utilizacao do mapa' ,
+    KEY (id_documento, tipo_documento, seq_documento) USING CLUSTERED COLUMNSTORE
+  );
+
+drop table pesquisabr.cache_pesquisa;
+CREATE TABLE pesquisabr.cache_pesquisa (
+    tipo_documento varchar(100) NULL COMMENT 'nome do tipo documento',
+    id_documento varchar(100) NULL COMMENT 'id do documento',
+    seq_documento int NOT NULL COMMENT 'seq do documento',
+    ind_status char(1) COMMENT 'status da comparação do critério com o documento',
+    hash_criterios char(40) not null COMMENT 'hash do critério de pesquisa',
+    dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela' ,
+    dthr_utilizacao timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data da última utilização' ,
+    KEY (id_documento, tipo_documento, seq_documento, hash_criterios) USING CLUSTERED COLUMNSTORE
+  );
+
+######### tabela de exemplo para armazenar documentos texto puro ou mapas de documentos 
+drop table pesquisabr.documentos_diversos;
+CREATE TABLE pesquisabr.documentos_diversos (
+    seq bigint(11) NOT NULL AUTO_INCREMENT,
+    dominio_documento varchar(100) NULL COMMENT 'dominio do documento - agrupador',
+    id_documento varchar(100) NULL COMMENT 'id do documento',
+    seq_documento int NOT NULL COMMENT 'seq do documento',
+    texto longtext COMMENT 'conteúdo pré-processado do documento',
+    ind_json_campos bool default false COMMENT ' True indica que o conteúdo do texto é um json com campos separados',
+    tamanho_texto int comment 'tamanho do texto original',
+    score float not null default 0 comment 'score padrão do documento a ser somado com o score da pesquisa',
+    dthr timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data de inclusão na tabela' ,
+    KEY (seq) USING CLUSTERED COLUMNSTORE,
+    FULLTEXT KEY (texto)
+  );
+
+
+select * from pesquisabr.sessao_pesquisa;  
+select * from pesquisabr.sessao_pesquisa_doc;