Ferramenta para Suporte a Consultas Temporais em SGBDs
convencionais
*Edimar Manica1, Cristiano R. Cervi1,2, Carina F. Dorneles1, Renata Galante2 1
Ciência da Computação/ICEG–UPF Bairro São José – BR 285 Passo Fundo, RS
2
Instituto de Informática – UFRGS Bairro Agronomia – Porto Alegre, RS
edimarmanica@yahoo.com.br, (cervi@upf.br | crcervi@inf.ufrgs.br) dorneles@upf.br, galante@inf.ufrgs.br
Trabalho de Conclusão
Resumo. Modelos de dados temporais têm como objetivo manipular o tempo de forma implícita. Estes modelos não necessitam de um SGBD específico, uma vez que podem ser mapeados para um banco de dados convencional. Neste caso, não se deve eliminar a possibilidade de utilização de uma linguagem de consulta temporal, uma vez que esta permite manter a transparência do mapeamento do modelo para o usuário. Este artigo apresenta uma ferramenta que permite a realização de consultas escritas em TSQL2 em SGBDs convencionais.
Abstract. Temporal data models are designed to handle with time in an implicit fashion. These models do not require a specific DBMS, since it can be easily mapped to a traditional database. In this case, we should not eliminate the possibility of using a temporal query language, since it keeps the mapping transparency for the user. This article presents a tool that allows the execution of queries written in conventional TSQL2 over traditional DBMSs.
1. Introdução
Quando se utiliza um modelo de dados temporal para especificar uma aplicação não se restringe, necessariamente, a utilização de um SGBD (Sistema Gerenciador de Banco de Dados) específico para um modelo temporal. Bancos de dados convencionais podem ser utilizados se existir um mapeamento adequado entre o modelo de dados temporal e o banco de dados utilizado. Neste caso, a recuperação de informações utilizando a linguagem de consulta do próprio banco de dados exige que o usuário conheça em detalhes este mapeamento. Isto é exemplificado na Figura 1, que apresenta duas consultas, uma na linguagem de consulta do PostgreSQL (linguagem SQL convencional) e outra em uma linguagem de consulta temporal (TSQL2), ambas com o objetivo de retornar o histórico do salário de João (salário e período).
*
Figura 1. Exemplo de consulta em SQL convencional e consulta em TSQL2.
Observa-se na Figura 1 que em SQL convencional é necessário saber em qual tabela o atributo temporal salário está particionado, enquanto que na TSQL2 basta conhecer em qual tabela ele está modelado. Além disso, em SQL convencional é necessário saber quais atributos representam o tempo e em TSQL2 utiliza-se funções para obter as informações temporais.
Contudo, a utilização de um banco de dados convencional para armazenar as informações do modelo não deve eliminar a possibilidade de utilização de uma linguagem de consulta temporal (Carvalho 1997). Estas linguagens permitem a descrição de consultas temporais em um alto nível sem que o usuário tenha que se preocupar com os aspectos de implementação. Além da possibilidade de definir filtros sobre os dados envolvidos na consulta, o usuário pode definir faixas de tempo sobre as quais os dados devem ser considerados.
Neste contexto, Manica (2008) apresenta a ferramenta EMap, a qual permite a realização de consultas escritas em TSQL2 para o PostgreSQL. Isto é possível através do mapeamento da TSQL2 para a SQL suportada por este banco de dados. No entanto, apesar da SQL ser um padrão, cada SGBD possui suas especificidades de operadores, funções e representações. Considerando este fato, o presente trabalho descreve a adaptação da ferramenta EMap com o objetivo de atender diversos SGBDs, tratando suas diferenças. Particularmente, neste artigo, é apresentado o mapeamento para os SGBDs PostgreSQL e MySql, uma extensão do trabalho apresentado em Manica et. al (2008).
Este artigo é organizado como segue. Na Seção 2 são apresentados alguns conceitos básicos de banco de dados temporais utilizados neste trabalho. Na Seção 3 são descritos alguns trabalhos relacionados. A adaptação da ferramenta EMap para atender diversos SGBDs é abordada na Seção 4. Os experimentos e resultados são apresentados na Seção 5. Finalmente, na Seção 6 são descritas as considerações finais.
2. Conceitos de Bancos de Dados Temporais
A maioria das aplicações atuais tem necessidade de manipular, de alguma forma, informações históricas - dados relativos a estados passados da aplicação. Isto tem sido um dos motivos que impulsionam a realização de pesquisas na área de banco de dados temporais, com o objetivo de definir conceitos e estratégias para tratar informações históricas (Edelweiss 1998). Com isso, inúmeros conceitos têm sido definidos, dos quais se cita, por serem usados neste trabalho:
• Tempo de Validade: é o tempo durante o qual um fato do banco de dados é verdadeiro na realidade modelada. São definidos, geralmente, pelo próprio usuário. • Tempo de Transação: é o tempo durante o qual um fato é atual no banco de dados e
no tempo. Logo após ser armazenado ele fica atual até que seja logicamente excluído, através da inserção de um fato mais recente que se torna o atual. Um novo tempo de transação não pode ser mais antigo que o tempo atual, assim como não é possível alterar um tempo passado. É o tempo em que o fato é armazenado no banco de dados, sendo definido pelo próprio SGBD.
• Banco de dados bitemporal: são os bancos de dados que possuem suporte ao tempo de transação e ao tempo de validade.
A Figura 2 apresenta as atualizações do salário de um funcionário em um banco de dados bitemporal. Em 01/07/2006, o funcionário passou a receber R$ 800,00, porém só em 02/07/2006 que essa informação foi inserida no banco de dados. Após, em 27/12/2006 foi inserida a informação que o funcionário passaria a ganhar R$ 900,00, mas só passaria a receber este valor em 01/01/2007. E por fim, em 01/07/2007 o funcionário passou a receber R$ 1.000,00 valor que recebe até hoje, porém esta informação foi inserida no banco de dados apenas em 10/07/2007.
Figura 2 – Exemplo de banco de dados bitemporal
3. Trabalhos Relacionados
O trabalho de Zaupa (2002) apresenta uma forma de possibilitar a realização de consultas temporais usando o modelo TVM (Temporal Versions Model) em um banco de dados convencional. O TVM é um modelo de dados bitemporal proposto com base na união de um modelo de versões com informações temporais. O banco de dados utilizado no mapeamento proposto por Zaupa (2002) foi o DB2.
Hübler (2000) apresenta o mapeamento do modelo TF-ORM (Temporal Functionality in Objects with roles Model) para Oracle. O TF-ORM é um modelo de dados bitemporal orientado a objetos que utiliza o conceito de papéis para representar os diferentes comportamentos dos mesmos. As informações temporais são associadas às propriedades que
podem mudar de valor ao longo do tempo. Tanto o tempo de validade quanto o tempo de transação são utilizados como rótulos associados às propriedades que descrevem as instâncias e os atributos que variam com o tempo.
Green e Johnson (2003) apresentam o protótipo de uma ferramenta denominada ProSQL para suporte ao desenvolvimento de extensões para SQL, voltada principalmente para extensões temporais. Ele permite realizar consultas, além do suporte à linguagem de definição de dados (DDL - Data Definition Language) e à linguagem de manipulação de dados (DML - Data Manipulation Language). Esta ferramenta surgiu a partir da constatação de que muitas extensões temporais de SQL têm sido propostas, mas poucas são implementadas.
4. Adaptação da Ferramenta EMap
A ferramenta EMap – Extrai e Mapeia – foi implementada em Manica (2008) com o objetivo de permitir a realização de consultas escritas em TSQL2 para o PostgreSQL (PostgreSQL, 2008), segundo o modelo físico implementado pelo Btpgsql†. Isto é possível através de um mapeamento da TSQL2 para a linguagem SQL suportada pelo PostgreSQL. Neste trabalho esta ferramenta foi adaptada para ser utilizada em qualquer banco de dados relacional. Como uma extensão ao trabalho, neste artigo apresenta-se o mapeamento efetuado para consultas sobre o SGBD MySQL (MySQL, 2008).
O subconjunto da TSQL2 adotado para atender aos objetivos deste trabalho contém apenas os elementos apresentados na Tabela 1, onde destacam-se os predicados e funções temporais, os quais não são aceitos em SQL convencional.
Tabela 2 – Elementos Mapeados.
O primeiro passo foi verificar quais características eram usadas pela ferramenta, sendo aceitas pelo PostgreSQL e que não eram permitidas pelo MySQL ou que eram acessadas de outra forma por este SGBD. Na Tabela 2 estas características são apresentadas, onde observa-se que no PostgreSQL concatena-observa-se elementos através do operador ||, já no MySQL utiliza-se a função concat. Também, o nome e a estrutura das tabelas que armazenam metadados sobre a base de dados são diferentes. Além disso, o tipo de dado timestamp do PostgreSQL possui representação para mais infinito (infinity) e menos infinito (-infinity), enquanto que no MySQL, nem o tipo de dado timestamp, nem o datetime oferecem esta representação.
†
Tabela 2 – Características do PostgreSQL utilizadas pela ferramenta EMap, que não são permitidas pelo MySQL ou que são acessadas de outra forma por este SGBD.
Característica PostgreSQL MySQL
Concatenar elementos Operador: || Função: concat Metadados Tabelas: PG_CLASS,
PG_ATTRIBUTE, etc.
Tabelas: COLUMNS,
KEY_COLUMN_USAGE, etc. Mais infinito infinity (para tipo timestamp) -
Menos infinito -infinity (para tipo timestamp) -
A ferramenta necessita concatenar elementos quando acessada a função VALID ou a função TRANSACTION. Quando acessada a função VALID é necessário retornar “[“ concatenado com o atributo que representa o tempo inicial de validade, concatenado com “,”, concatenado com o atributo que representa o tempo final de validade, concatenado com “)”. Já, quando acessada a função TRANSACTION é necessário retornar “[“ concatenado com o atributo que representa o tempo inicial de transação, concatenado com “,”, concatenado com o atributo que representa o tempo final de transação, concatenado com “)”.
O mapeamento acessa os metadados da base de dados para verificar se um atributo está armazenado em uma determinada tabela. Isso é realizado quando um atributo é acessado sem que seja definido um alias para ele. Também, quando é acessado um atributo temporal particionado para outra tabela‡ é necessário fazer a junção da tabela física com a tabela origem, e para isto os metadados são consultados a fim de verificar os atributos que estabelecem o relacionamento entre estas tabelas.
As representações de menos infinito (-infinity) e mais infinito (infinity) são utilizadas pelo Btpgsql para expressar, respectivamente, o início da aplicação e o tempo corrente a medida que ele corre. Elas também podem ser utilizadas nas consultas em TSQL2.
Para gerenciar estas divergências foram realizadas algumas alterações. Foi definido que para concatenar elementos é utilizado a função concat(text, text). Esta função é definida pelo MySQL e para o PostgreSQL é necessário criá-la conforme Figura 3.
CREATE FUNCTION concat(text, text) RETURNS text AS $_$ DECLARE t text; BEGIN t = $1 || $2; RETURN t; END;$_$ LANGUAGE plpgsql;
Figura 3 – Definição da função concat(text, text).
Era necessário ter uma forma única, para qualquer banco de dados, de acessar seus metadados utilizados pela ferramenta. Para isso, foram definidas duas visões: vw_tabela e
vw_referencia. A visão vw_tabela possui dois atributos: (i) tabela e (ii) atributo, sendo seu objetivo permitir descobrir em que tabela um determinado atributo está armazenado. A criação desta tabela no PostgreSQL e no MySQL é observada na 4.
‡
Atributo temporal particionado para outra tabela é aquele atributo temporal que foi modelado em uma tabela (tabela origem) com outros atributos assíncronos (atributos que evoluem em tempos diferentes), porém ele foi particionado para outra tabela (tabela física). Isto é realizado para economizar espaço de armazenamento, pois se ele não fosse particionado cada novo valor deste atributo exigiria que fosse criado um novo registro repetindo, desnecessariamente, o valor de todos os outros atributos.
PostgreSQL CREATE VIEW vw_tabela AS
SELECT pc.relname AS tabela,
lower((pa.attname)::text) AS atributo
FROM pg_class pc JOIN pg_attribute pa
ON pa.attrelid = pc.oid WHERE pa.attisdropped = false
AND pc.relkind = 'r' AND pc.relnamespace = 2200
AND pa.attnum > 0;
MySQL
CREATE OR REPLACE VIEW vw_referencia AS (
SELECT table_name AS tabela, column_name AS atributo
FROM information_schema.`COLUMNS` C WHERE table_schema = 'teste'
);
Figura 4 – Criação da visão vw_tabela.
A visão vw_referencia possui quatro atributos: (i) tabela_referenciada; (ii) coluna_referenciada; (iii) tabela_referente e (iv) coluna_referente. O objetivo desta visão é permitir a descoberta de quais tabelas/colunas referenciam quais tabelas/colunas, a fim de fazer a junção de duas tabelas. A criação desta visão no PostgreSQL e no MySQL é ilustrada na Figura 5.
PostgreSQL CREATE VIEW vw_referencia AS
SELECT clf.relname AS tabela_referenciada, af.attname AS coluna_referenciada, cl.relname AS tabela_referente, a.attname AS coluna_referente FROM pg_attribute a
JOIN pg_class cl ON a.attrelid = cl.oid AND cl.relkind = 'r' JOIN pg_namespace n ON n.oid = cl.relnamespace JOIN pg_constraint ct ON a.attrelid = ct.conrelid AND ct.confrelid <> 0
AND ct.conkey[1] = a.attnum JOIN pg_class clf ON ct.confrelid = clf.oid AND clf.relkind = 'r' JOIN pg_namespace nf ON nf.oid = clf.relnamespace JOIN pg_attribute af ON af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1];
MySQL CREATE OR REPLACE view vw_referencia AS ( SELECT table_name AS tabela_referente, column_name AS coluna_referente, referenced_table_name AS tabela_referenciada, referenced_column_name AS coluna_referenciada FROM information_schema.KEY_COLUMN_USAGE K WHERE table_schema='teste' AND referenced_column_name IS NOT NULL )
Figura 5 – Criação da visão vw_referencia.
Quanto ao fato do MySQL não suportar -infinity e infinity, isso foi resolvido substituindo essas expressões para o MySQL, respectivamente, pelo menor valor suportado ‘0000-00-00 00:00:00’ e pelo maior valor suportado ‘9999-12-31 23:59:59’. Além disso, para o PostgreSQL usa-se o tipo de dado timestamp para os atributos que representam o tempo, o qual permite representar do ano 4713 antes de Cristo até o ano 294276 depois de Cristo§, além de possibilitar –infinity e infinity. No entanto, o tipo de dado timestamp do MySQL só permite representar de 1970 a 2037. Enquanto que o tipo de dado datetime
deste mesmo SGBD permite uma faixa de 1000 a 9999, também permitindo representar ‘0000-00-00 00:00:00’. Por este motivo, para o MySQL utiliza-se o tipo de dado datetime
para os atributos temporais.
§
5. Experimentos e Resultados
A seguir é apresentada a metodologia utilizada nos experimentos, seguida dos resultados obtidos a partir dos testes da ferramenta EMap após a alteração, para permitir acesso tanto à bases de dados do PostgreSQL quanto a bases do MySQL.
5.1. Metodologia
A metodologia utilizada neste trabalho foi a mesma realizada na primeira versão da ferramenta EMap apresentada em Manica (2008), porém na primeira versão apenas foram realizados testes em uma base de dados no PostgreSQL e neste trabalho testou-se também em uma base no MySQL. Utilizou-se uma modelagem de dados temporal adaptada de Cervi et al. (2008). Esta modelagem adaptada possui 5 tabelas, sendo 3 tabelas bitemporais, 1 tabela com atributos não temporais e atributos temporais e 1 tabela apenas com atributos não temporais.
A modelagem foi criada em uma base de dados no PostgreSQL e em uma base de dados no MySQL, juntamente com os metadados utilizados pelo Btpgsql e pela ferramenta. Estas bases de dados foram populadas com currículos de pesquisadores, contendo um total de 46 registros. Os currículos foram disponibilizados por pesquisadores da Universidade de Passo Fundo.
Para a validação da ferramenta foram executadas 100 consultas através da ferramenta em cima das duas bases de dados. Estas consultas foram criadas por 4 alunos do curso de Ciência da Computação da Universidade de Passo Fundo. Os alunos foram orientados a usar em cada consulta pelos menos um atributo, função ou operador temporal. Ainda, solicitou-se que eles usassem pelo menos uma vez cada operador e função temporal oferecida pelo subconjunto de TSQL2 suportado pela ferramenta.
Ressalta-se que eles não tiveram acesso ao modelo físico, tendo acesso apenas ao modelo conceitual**, pois a implementação da temporalidade deve ficar implícita nas consultas. Para demonstrar como eram as consultas temporais criadas pelos alunos, é apresentada a Figura 6 que mostra a utilização das funções e predicados temporais. A partir desta, é possível perceber que em várias consultas foram usados mais de um operador ou função temporal.
**
Modelo conceitual é o modelo de dados abstrato, que descreve a estrutura de um banco de dados de forma independente de um SGBD particular (HEUSER, 2001). Neste trabalho, o termo modelo conceitual foi usado para representar o modelo que abstrai a implementação da temporalidade, não abstraindo a entidade associativa resultante de um relacionamento NxN.
Utilização das funções e predicados temporais 0 5 10 15 20 25 30 35 VA LID TR AN SA CT ION BEG IN EN D OV ER LA PS INTE RS EC T PR EC ED ES
Função ou predicado temporal
N ú m e ro d e o c o rr ê n c ia s
Figura 6 – Utilização dos predicados e funções temporais
5.2. Resultados
A Figura 7 apresenta os resultados obtidos a partir da execução, nas duas bases, das 100 consultas criadas pelos alunos. Observa-se que não ocorreram erros da ferramenta, porém foram contatados 11% de erros do usuário.
Percentual de Erros - com os erros do usuário
89%
11% 0%
% Sem Erros % Erro do Usuário % Erro da Ferramenta
Figura 7 – Percentual de erros nas consultas considerando os erros do usuário
Para uma melhor avaliação da ferramenta os erros do usuário foram corrigidos. Estes erros eram na grande maioria erros de digitação, como por exemplo, digitar INTRESECT em vez de INTERSECT. Depois de corrigidos os erros, as consultas foram novamente executadas nas duas bases através da ferramenta e o resultado é apresentado na Figura 8, onde observa-se que 100% das consultas executaram corretamente.
Percentual de Erros - corrigidos os erros do usuário
100% 0%
% Sem Erros
% Erro da Ferramenta
Figura 8 - Percentual de erros nas consultas corrigidos os erros do usuário
6. Considerações Finais
Este trabalho apresentou um projeto de mapeamento de uma linguagem temporal (TSQL2) para o banco de dados PostgreSQL e MySQL, visando permitir aos usuários a realização de consultas de mais alto nível sobre os aspectos temporais utilizando SGBDs relacionais tradicionais. Uma das contribuições do trabalho está no fato de tornar a consulta transparente ao usuário, a fim de que eles não necessitem conhecer a forma como são implementados os conceitos de temporalidade, muito menos o processo de mapeamento.
O objetivo é possibilitar consultas sobre os dados atuais, sobre os dados passados e sobre os dados que eram considerados válidos em instantes passados, mesmo depois de terem sido alterados. Para isso, foi definido um subconjunto da TSQL2 a ser mapeado.
O PostgreSQL possui uma extensão – Btpgsql – que emula um banco de dados bitemporal e controla as restrições temporais, porém, este módulo não apresenta uma linguagem de consulta temporal. Neste contexto, a recuperação de informações temporais utilizando a linguagem de consulta do banco de dados exige do usuário um grande conhecimento sobre a forma de mapeamento do modelo temporal para o banco de dados, bem como do relacionamento dos atributos temporais com os valores das propriedades. Com isso, a utilização de um banco de dados convencional para armazenar as informações de um modelo de dados temporal não deve eliminar a possibilidade da utilização de uma linguagem de consulta temporal (Carvalho 1997).
Como trabalhos futuros, destacam-se a criação de uma ferramenta gráfica para a realização das consultas, o aumento do subconjunto do TSQL2 permitindo as cláusulas ORDER BY, GROUP BY e HAVING, funções de agregação (COUNT, AVG, SUM, MIN, MAX), eliminação de valores duplicados (DISTINCT) e avaliar a possibilidade de implementar mais operadores e funções temporais do TSQL2 como: CAST, CONTAINS, MEETS, FIRST, LAST, e até mesmo alguns que a TSQL2 não define, como por exemplo, AFTER e EQUAL.
Referências
CARVALHO, T. P. Implementação de Consultas para um Modelo de Dados Temporal Orientado a Objetos. Dissertação (Mestrado em Ciência da Computação) – Universidade Federal do Rio Grande do Sul, Porto Alegre, 1997.
CERVI, C. R. et al. Modelando o Perfil de Pesquisadores Através de Fontes de Dados Heterogêneas. In: Simpósio de Informática do Planalto Médio, 8, 2008, Passo Fundo, Anais... 1 CD_ROM.
EDELWEISS, N. Banco de Dados Temporais: Teoria e Prática. In: XVII Jornada de Atualização em Informática – JAI. XVII Congresso Nacional da sociedade Brasileira de computação. Anais... Belo Horizonte 1998. p 225-282.
GREEN, James; JOHNSON, Roger. ProSQL: A prototyping tool for SQL temporal language extensions. In: JAMES, A. E. et al., BNCOD, volume 2712 of LNCS, pages 190–197. Springer, 2003.
HEUSER, C. A. Projeto de Banco de Dados. 4. ed. Porto Alegre: Sagra Luzzatto, 2001. HÜBLER, P. N. Definição de um Gerenciador para o Modelo de Dados Temporal
TF-ORM.2000. Dissertação (Mestrado em Ciência da Computação) – Universidade Federal do Rio Grande do Sul, Porto Alegre, 2000.
MANICA, Edimar. Suporte a Consultas Temporais através de um Mapeamento da Linguagem TSQL2 para o PostgreSQL. Trabalho de Conclusão de Curso (Bacharel em Ciência da Computação) – Universidade de Passo Fundo, Passo Fundo, 2008.
MANICA, E. ; Cervi, C.R ; DORNELES, C. F. ; GALANTE, Renata de Matos . Mapeamento de um Modelo Lógico Temporal para um Modelo Físico de um SGBD. In: II Sessão de Pôsters do SBBD, 2008, Campinas. II Sessão de Pôsters do SBBD, 2008.
MYSQL. MySQL Documentation. Disponível em: <http://dev.mysql.com/doc>. Acessado em: 15 dez. 2008.
POSTGRESQL. PostgreSQL 8.0 Documentation. Disponível em: <http://www.postgresql.org/docs>. Acessado em: 20 maio 2008.
ZAUPA, A. P. Suporte a Consultas no Ambiente Temporal de Versões. Dissertação(Mestrado em Ciência da Computação) – Universidade Federal do Rio Grande do Sul, Porto Alegre, 2002.
