Não sou o primeiro, nem quero ser o último a desejar a você boas festas e um abençoado 2009!!!
terça-feira, 23 de dezembro de 2008
Boas Festas!!!
Não sou o primeiro, nem quero ser o último a desejar a você boas festas e um abençoado 2009!!!
DbDesigner Fork: Fraco, mas Gratuito!
Ferramentas para projeto de banco de dados estão entre as mais úteis para o desenvolvimento de sistemas. Infelizmente, também estão entre as mais difíceis de se encontrar dentre os softwares gratuitos. O DbDesigner Fork é uma boa opção livre para a modelagem de bancos de dados, apresentando suporte para Oracle, SQL Server, MySql e Firebird, além do PostgreSQL.
O projeto está disponível para Windows e Linux e sua última versão está datada de julho de 2007. Originou-se do DBDesigner, uma ferramenta mais antiga. Seu desenvolvimento apresenta alguma taxa de atividade e possivelmente será feito algum novo release em breve. Gera arquivos XML com a modelagem realizada em uma boa interface visual.
Apresenta uma série de recursos bastante úteis:
- Engenharia Reversa
- Sincronização
- Modelagem visual de diagramas de Entidade-Relacionamento, com integridade referencial
- Recursos para exportação SQL
- Importação da ferramenta ErWin
- Exportação dos diagramas como figuras (PNG e BMP)
DbDesigner e PostgreSQL
Nesta análise me concentrarei basicamente nas características específicas que o software tem em relação com o o PostgreSQL. A ferramenta apresentou um resultado razoável, pois foram constatados bugs e limitações. O ponto forte, além da gratuidade, foi a interface amigável, apesar de não tão moderna.
A conexão com o banco foi dificultada pelo fato da ferramenta só aceitar conexão ODBC com o PostgreSQL. Tive de baixar e instalar o driver ODBC, além de criar um data source no sistema operacional.
A interface visual é simples e a seleção da visualização em query mode permitiu a realização de consultas ao SGBD sem sair da ferramenta.
A engenharia reversa funcionou muito bem. Puxou os dados do PostgreSQL rapidamente e criou objetos no diagrama corrente. No entanto, não importou as chaves estrangeiras. Quando realizada mais de uma vez, criou mais de uma tabela com o mesmo nome no diagrama.
A sincronização de banco de dados simplesmente não funcionou. Fiquei em dúvida se a mensagem de erro apresentada foi em virtude de bug da ferramenta ou da versão do driver ODBC utilizada. Abaixo, a tela de sincronização:
Na exportação de dados, uma decepção: não há ligação direta para o PostgreSQL. O DbDesigner exporta um script SQL que deve ser submetido ao banco.
Avaliação geral
Creio que houve uma série de avanços em relação às versões anteriores da ferramenta, mas ainda está distante de ser utilizável sem ressalvas, especialmente nas grandes empresas. Abaixo listo algumas das limitações da ferramenta:
- Falta Suporte a trabalho em grupos.
- Ausência de Controle de versões.
- Grande espaçamento de tempo entre as atualizações de versão.
- Número grande de bugs e de requested features por atender.
- Falta de interface WEB
Observações
Aconselho a quem deseja utilizar esta ferramenta para suas aplicações que preserve o modelo gerado, que é criado em formato XML, em uma ferramenta de controle de versões. Em releases anteriores do DbDesigner cheguei a perder algumas horas de trabalho por corrupção do arquivo.
O projeto está disponível para Windows e Linux e sua última versão está datada de julho de 2007. Originou-se do DBDesigner, uma ferramenta mais antiga. Seu desenvolvimento apresenta alguma taxa de atividade e possivelmente será feito algum novo release em breve. Gera arquivos XML com a modelagem realizada em uma boa interface visual.
Apresenta uma série de recursos bastante úteis:
- Engenharia Reversa
- Sincronização
- Modelagem visual de diagramas de Entidade-Relacionamento, com integridade referencial
- Recursos para exportação SQL
- Importação da ferramenta ErWin
- Exportação dos diagramas como figuras (PNG e BMP)
DbDesigner e PostgreSQL
Nesta análise me concentrarei basicamente nas características específicas que o software tem em relação com o o PostgreSQL. A ferramenta apresentou um resultado razoável, pois foram constatados bugs e limitações. O ponto forte, além da gratuidade, foi a interface amigável, apesar de não tão moderna.
A conexão com o banco foi dificultada pelo fato da ferramenta só aceitar conexão ODBC com o PostgreSQL. Tive de baixar e instalar o driver ODBC, além de criar um data source no sistema operacional.
A interface visual é simples e a seleção da visualização em query mode permitiu a realização de consultas ao SGBD sem sair da ferramenta.
A engenharia reversa funcionou muito bem. Puxou os dados do PostgreSQL rapidamente e criou objetos no diagrama corrente. No entanto, não importou as chaves estrangeiras. Quando realizada mais de uma vez, criou mais de uma tabela com o mesmo nome no diagrama.
A sincronização de banco de dados simplesmente não funcionou. Fiquei em dúvida se a mensagem de erro apresentada foi em virtude de bug da ferramenta ou da versão do driver ODBC utilizada. Abaixo, a tela de sincronização:
Na exportação de dados, uma decepção: não há ligação direta para o PostgreSQL. O DbDesigner exporta um script SQL que deve ser submetido ao banco.
Avaliação geral
Creio que houve uma série de avanços em relação às versões anteriores da ferramenta, mas ainda está distante de ser utilizável sem ressalvas, especialmente nas grandes empresas. Abaixo listo algumas das limitações da ferramenta:
- Falta Suporte a trabalho em grupos.
- Ausência de Controle de versões.
- Grande espaçamento de tempo entre as atualizações de versão.
- Número grande de bugs e de requested features por atender.
- Falta de interface WEB
Observações
Aconselho a quem deseja utilizar esta ferramenta para suas aplicações que preserve o modelo gerado, que é criado em formato XML, em uma ferramenta de controle de versões. Em releases anteriores do DbDesigner cheguei a perder algumas horas de trabalho por corrupção do arquivo.
sexta-feira, 5 de dezembro de 2008
Retornando registros de consultas genéricas com RETURN QUERY
Recentemente, recebi uma dúvida sobre o RETURN QUERY, funcionalidade implementada na versão 8.3 do PostgreSQL, que permite o retorno de conjuntos de linhas e colunas de uma função. A dúvida do Patrick é bastante comum e até pouco tempo eu não teria uma resposta adequada:
"Patrick Espake disse...
Eu estou tentando usar isso, para retorna uma linha que vem de um inner join de diversas tabelas.
O que eu coloco para o RETURNS SETOF? Visto que os meus dados vem de uma junção de diversas tabelas.
Obrigado."
A tabela indivíduo será utilizada para o teste do RETURN QUERY retornando uma consulta qualquer.
CREATE TABLE individuo (
cod integer PRIMARY KEY,
nome varchar(50));
INSERT INTO individuo VALUES (1,'Teste');
INSERT INTO individuo VALUES (2,'Teste 2');
Agora, a função RET_ROWS mostra como utilizar o retorno de tipos RECORD para funções:
--Retornando consulta de várias linhas
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT * FROM individuo; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
A grande diferença desta forma de programar está na chamada da função. Ao utilizar valor de retorno do tipo RECORD, deve ser indicada a lista de campos a serem retornados e seus tipos. Esta pode ser vista como uma limitação, embora na prática também seja uma forma de se garantir a confiabilidade dos resultados retornados na execução de comandos SQL dentro de funções pela validação dos valores retornados.
SELECT * from ret_rows() as (c1 integer, c2 varchar(50)) ; --Declaração dos campos do record
Falta considerar a execução de uma junção dentro da função, que é nossa questão original. Para tanto, será criada uma tabela com os filhos dos indivíduos para sofrer junção com a de indivíduos. O exemplo abaixo executou sem qualquer problema na versão 8.3:
CREATE TABLE filhos (
cod integer PRIMARY KEY,
filhos integer);
INSERT INTO filhos VALUES (1,1);
INSERT INTO filhos VALUES (2,3);
--Retornando Resultado de Junção
CREATE OR REPLACE FUNCTION ret_filhos (psql integer) RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT individuo.nome, filhos.filhos FROM individuo, filhos WHERE individuo.cod = filhos.cod AND individuo.cod = $1; -- Consulta
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
SELECT * from ret_filhos(1) as (c1 varchar(50), c2 integer) ; --Declaração dos campos de retorno
SELECT * from ret_filhos(2) as (c1 varchar(50), c2 integer) ;
Outras Soluções
Outras soluções para o retorno de registros em funções são a consolidação e retorno dos dados dentro de um campo texto ou XML.
Resolveu, Patrick?
sexta-feira, 28 de novembro de 2008
PostgreSQL: Tendências pelo Mundo e no Brasil!
Você já se perguntou que países mais utilizam o PostgreSQL? Em que colocação ficaria o Brasil neste ranking? As buscas sobre o PostgreSQL estão aumentando ou diminuindo? Este tipo de pergunta é difícil de responder em virtude da facilidade de instalação e difusão do software. Estatísticas confiáveis são difíceis de conseguir e as que são disponíveis são certamente sujeitas a erros substanciais. Por exemplo, o fato de termos o PostgreSQL instalado, não significa que esteja sendo realmente executado.
No site do Google Insights podem ser buscadas informações relativas a buscas de termos em geral. A busca por PostgreSQL revelou interessantes idéias. Confira por conta própria aqui.
Algumas curiosidades:
- A procura por PostgreSQL tem diminuído nos últimos 4 anos. Este dado não significa uma diminuição da base instalada, uma vez que o que é medido é a quantidade de buscas a respeito.
- Se em 2004, Japão e Rússia eram os países com maior interesse, Cuba e China hoje ocupam esta posição. Abaixo, uma visão do dia de hoje (28/11/2008)
- O Brasil ainda ocupa uma posição intermediária em buscas na internet sobre o tema. O Paraguai é o país da América do Sul que mais pesquisa sobre o PostgreSQL.
- Em 2008 a quantidade de buscas está estável, com queda à medida que se aproxima o fim do ano.
- Dentre os estados brasileiros, temos como grandes buscadores o Distrito Federal, o meu Ceará e Santa Catarina. A região Norte em Geral e alguns estados apresentaram um baixíssimo índice de buscas sobre o PostgreSQL, enquanto que a Região Sul é a que apresentou uma maior difusão, com todos os estados apresentando bom índice de buscas. Estes dados apresentam uma forte influência da concentração dos profissionais de informática no país.
No site do Google Insights podem ser buscadas informações relativas a buscas de termos em geral. A busca por PostgreSQL revelou interessantes idéias. Confira por conta própria aqui.
Algumas curiosidades:
- A procura por PostgreSQL tem diminuído nos últimos 4 anos. Este dado não significa uma diminuição da base instalada, uma vez que o que é medido é a quantidade de buscas a respeito.
- Se em 2004, Japão e Rússia eram os países com maior interesse, Cuba e China hoje ocupam esta posição. Abaixo, uma visão do dia de hoje (28/11/2008)
- O Brasil ainda ocupa uma posição intermediária em buscas na internet sobre o tema. O Paraguai é o país da América do Sul que mais pesquisa sobre o PostgreSQL.
- Em 2008 a quantidade de buscas está estável, com queda à medida que se aproxima o fim do ano.
- Dentre os estados brasileiros, temos como grandes buscadores o Distrito Federal, o meu Ceará e Santa Catarina. A região Norte em Geral e alguns estados apresentaram um baixíssimo índice de buscas sobre o PostgreSQL, enquanto que a Região Sul é a que apresentou uma maior difusão, com todos os estados apresentando bom índice de buscas. Estes dados apresentam uma forte influência da concentração dos profissionais de informática no país.
Funções de Suporte a Tipos ENUM (8.3)
O PostgreSQL 8.3 disponibiliza algumas funções que permitem a consulta a campos ENUM com pouquíssimo esforço. As funções implementadas são: enum_first, enum_last e enum_range.
Para exemplificar sua utilização, vamos criar o tipo enum signos:
CREATE TYPE signos AS ENUM ('Áries', 'Touro', 'Gêmeos', 'Câncer', 'Leão', 'Virgem', 'Libra',
'Escorpião', 'Sagitário', 'Capricórnio', 'Aquário', 'Peixes');
* enum_first
1- Primeiro elemento de um tipo ENUM
SELECT enum_first(null::signos);
* enum_last
1- Último elemento de um tipo ENUM
SELECT enum_last(null::signos);
* enum_range - Intervalos de elementos de um tipo ENUM.
1 - Retornando todos os valores em ordem
SELECT enum_range(null::signos);
2 - Retorna valores até o ENUM Virgem, incluindo o 'Virgem'
SELECT enum_range(null, 'Virgem'::signos);
3 - Retorna valores a partir do ENUM Virgem, incluindo o 'Virgem'
SELECT enum_range('Virgem'::signos, null);
4 - Retorna valores do intervalo entre Touro e Virgem, incluindo os dois itens.
SELECT enum_range('Gêmeos'::signos,'Virgem'::signos);
Para exemplificar sua utilização, vamos criar o tipo enum signos:
CREATE TYPE signos AS ENUM ('Áries', 'Touro', 'Gêmeos', 'Câncer', 'Leão', 'Virgem', 'Libra',
'Escorpião', 'Sagitário', 'Capricórnio', 'Aquário', 'Peixes');
* enum_first
1- Primeiro elemento de um tipo ENUM
SELECT enum_first(null::signos);
* enum_last
1- Último elemento de um tipo ENUM
SELECT enum_last(null::signos);
* enum_range - Intervalos de elementos de um tipo ENUM.
1 - Retornando todos os valores em ordem
SELECT enum_range(null::signos);
2 - Retorna valores até o ENUM Virgem, incluindo o 'Virgem'
SELECT enum_range(null, 'Virgem'::signos);
3 - Retorna valores a partir do ENUM Virgem, incluindo o 'Virgem'
SELECT enum_range('Virgem'::signos, null);
4 - Retorna valores do intervalo entre Touro e Virgem, incluindo os dois itens.
SELECT enum_range('Gêmeos'::signos,'Virgem'::signos);
quinta-feira, 27 de novembro de 2008
Campos Tipo ENUM no PostgreSQL 8.3
Campos com enumerações de valores aceitos são implementados em vários bons bancos de dados. No caso do PostgreSQL, este recurso poderia ser realizado através de constraints tipo CHECK ou domínios. Na versão 8.3 foi introduzido um tipo de dado ENUM, facilitando ainda mais a operação de campos com uma lista restrita de valores permitidos.
Vantagens:
- Cria listas de valores aceitos que podem ser reutilizadas várias vezes, inclusive dentro de uma mesma tabela, para disciplinar os valores aceitos por um determinado campo.
- Apresenta funções específicas para manipulação dos tipos ENUM criados.
- Melhor uso de espaço: o armazenamento campos Enum ocupam 4 bytes em disco. Dependendo da quantidade de valores armezenados, pode ser economizado um bom espaço no banco de dados.
- Podem ser visualizados através de consultas a pg_enum.
Desvantagens:
- O tipo ENUM é diferente dos demais tipos do PostgreSQL, portanto não pode ser comparado com valores de outros tipos. Na ausência de CAST, empregue um operador customizado ou uma função que realize esta conversão.
- Falta de recursos para alterações no tipo. Não é possível simplesmente inserir ou retirar valores de um tipo, mas os valores de campos de tabelas tipo ENUM podem ser livremente alterados.
- Enumerados que não sejam de valores alfanuméricios não podem ser criados. Para enumerados de datas e números deve ser empregada uma restrição do tipo CHECK, por exemplo, ou outra maneira de simular o ENUM.
Sintaxe:
1 - Criação e Utilização de Tipo Enumerado
CREATE TYPE dia_sem AS ENUM ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab');
--Utilização na criação de tabelas
CREATE TABLE registro (
dia_coleta dia_sem,
dia_analise dia_sem,
dia_resultado dia_sem);
INSERT INTO registro VALUES ('Seg', 'Ter', 'Qua');
INSERT INTO registro VALUES ('Seg', 'Ter', 'Dom');
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuW'); --ERRO - Não encontrado no tipo ENUM
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuA'); --ERRO - Case sentitive
2 - Implementação similar com a cláusula check
CREATE TABLE registro_teste (
dia_coleta text check (dia_coleta in ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab')));
3 - Criação de Tipo Enumerado II
CREATE TYPE situacao AS ENUM ('Em elaboração', 'Proposto',
'Em homologação', 'Aprovado', 'Recusado', 'Cancelado', 'Efetivado');
--Utilização na criação de tabelas
CREATE TABLE documento (
descricao varchar(50) PRIMARY KEY,
estado situacao);
INSERT INTO documento VALUES ('Ata de reunião de 12/12/2008', 'Aprovado');
INSERT INTO documento VALUES ('Levantamento do Protótipo', 'Cancelado');
4 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Tipo e Valor)
SELECT * from pg_enum;
5 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Valor, do Tipo e o Valor)
SELECT oid,* from pg_enum;
Vantagens:
- Cria listas de valores aceitos que podem ser reutilizadas várias vezes, inclusive dentro de uma mesma tabela, para disciplinar os valores aceitos por um determinado campo.
- Apresenta funções específicas para manipulação dos tipos ENUM criados.
- Melhor uso de espaço: o armazenamento campos Enum ocupam 4 bytes em disco. Dependendo da quantidade de valores armezenados, pode ser economizado um bom espaço no banco de dados.
- Podem ser visualizados através de consultas a pg_enum.
Desvantagens:
- O tipo ENUM é diferente dos demais tipos do PostgreSQL, portanto não pode ser comparado com valores de outros tipos. Na ausência de CAST, empregue um operador customizado ou uma função que realize esta conversão.
- Falta de recursos para alterações no tipo. Não é possível simplesmente inserir ou retirar valores de um tipo, mas os valores de campos de tabelas tipo ENUM podem ser livremente alterados.
- Enumerados que não sejam de valores alfanuméricios não podem ser criados. Para enumerados de datas e números deve ser empregada uma restrição do tipo CHECK, por exemplo, ou outra maneira de simular o ENUM.
Sintaxe:
CREATE TYPE name AS ENUMExemplos:
( 'label' [, ... ] )
1 - Criação e Utilização de Tipo Enumerado
CREATE TYPE dia_sem AS ENUM ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab');
--Utilização na criação de tabelas
CREATE TABLE registro (
dia_coleta dia_sem,
dia_analise dia_sem,
dia_resultado dia_sem);
INSERT INTO registro VALUES ('Seg', 'Ter', 'Qua');
INSERT INTO registro VALUES ('Seg', 'Ter', 'Dom');
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuW'); --ERRO - Não encontrado no tipo ENUM
INSERT INTO registro VALUES ('Seg', 'Ter', 'QuA'); --ERRO - Case sentitive
2 - Implementação similar com a cláusula check
CREATE TABLE registro_teste (
dia_coleta text check (dia_coleta in ('Dom', 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab')));
3 - Criação de Tipo Enumerado II
CREATE TYPE situacao AS ENUM ('Em elaboração', 'Proposto',
'Em homologação', 'Aprovado', 'Recusado', 'Cancelado', 'Efetivado');
--Utilização na criação de tabelas
CREATE TABLE documento (
descricao varchar(50) PRIMARY KEY,
estado situacao);
INSERT INTO documento VALUES ('Ata de reunião de 12/12/2008', 'Aprovado');
INSERT INTO documento VALUES ('Levantamento do Protótipo', 'Cancelado');
4 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Tipo e Valor)
SELECT * from pg_enum;
5 - Consulta aos enumerados no catálogo do PostgreSQL (OID do Valor, do Tipo e o Valor)
SELECT oid,* from pg_enum;
segunda-feira, 20 de outubro de 2008
PostgreSQL em Evento de Curitiba
O SERPRO - Serviço Federal de Processamento de Dados, promove o II Fórum de Tecnologia em Software Livre, em Curitiba. O evento ocorrerá de 10 a 12 de novembro de 2008, na Regional Serpro de Curitiba.
O PostgreSQL faz parte da programação por meio de uma palestra sobre as funcionalidades da versão 8.3 e de um minicurso de PostgreSQL Avançado, ministrado por mim. Plataformas, tecnologias e processos baseados em software e formatos livres e abertos fazem parte da programação do evento.
Participe! As incrições podem ser feitas pelo site: http://www.cta.softwarelivre.serpro.gov.br/
quinta-feira, 9 de outubro de 2008
PostgreSQL no Novo Concurso do SERPRO
(09/10/2008) Saiu o edital do concurso do SERPRO - Serviço Federal de Processamento de Dados e pela primeira vez o banco de dados PostgreSQL aparece no edital. No cargo 5: ‘ANALISTA – ESPECIALIZAÇÃO: DESENVOLVIMENTO DE SISTEMAS’, aparece no conteúdo programático o tópico ‘PostgreSQL em Linux’. Dos bancos de dados proprietários, apenas o ADABAS (Software AG) foi citado explicitamente.
Uma curiosidade: no cargo 17, ANALISTA – ESPECIALIZAÇÃO: SUPORTE TÉCNICO, o PostgreSQL também faz parte do conteúdo programático, mas esta escrito errado.
E difícil afirmar que cairá ao menos uma questão de PostgreSQL no concurso, no entanto este edital consiste em mais um indicador da crescente utilização deste banco de dados na esfera federal brasileira.
Confira aqui o edital completo e boa sorte!
Uma curiosidade: no cargo 17, ANALISTA – ESPECIALIZAÇÃO: SUPORTE TÉCNICO, o PostgreSQL também faz parte do conteúdo programático, mas esta escrito errado.
E difícil afirmar que cairá ao menos uma questão de PostgreSQL no concurso, no entanto este edital consiste em mais um indicador da crescente utilização deste banco de dados na esfera federal brasileira.
Confira aqui o edital completo e boa sorte!
sexta-feira, 3 de outubro de 2008
SERPRO Participa da PgCon 2008
O SERPRO - Serviço Federal de Processamento de Dados participou da PgCon 2008 como patrocinador. A ferramenta é o principal banco de dados livre homologada para compor as soluções da empresa, que conta com um portfólio de centenas de sistemas ativos.
Saiba mais neste link.
Saiba mais neste link.
quinta-feira, 2 de outubro de 2008
Esqueça o Comando SHOW: Use PG_SETTINGS
O comando SHOW é bastante utilizado para visualizar as configurações de um servidor PostgreSQL de datas, log, gerência de memória etc. Uma vez que na versão em que estou trabalhando tempos 187 parâmetros distintos, este comando se revela bastante prático para se ter uma visão geral da configuração e bem fácil de usar sem.
Abaixo, exemplos de sua sintaxe:
- SHOW ALL
- SHOW work_mem
No entanto, apresenta uma série de desagradáveis limitações:
- O resultado da consulta das variáveis de configuração não pode ser alterado (essa atualização pode ser feita pelo comando SET).
- Os campos mostrados são poucos e até insuficientes dependendo da necessidade (nome do parâmetro, valor corrente e descrição)
- Não é possível selecionar um subconjunto dados para ser visualizado (linhas e colunas), tampouco agregar colunas adicionais na consulta retornada.
- Não é possível montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 variáveis importantes, deve-se usar SHOW ALL ou fazer 10 comandos SHOW indicando as variáveis desejadas.
Apesar de sua utilizada, este comando simplesmente limita a flexibilidade de consultas e relatórios sobre as configurações do PostgreSQL. Como contornar esta limitação?
A tabela virtual PG_SETINGS contorna TODAS AS LIMITAÇÕES apresentadas pelo comando SHOW. As únicas restrições são:
- Não se pode inserir registros ou excluí-los (assim como não se pode adicionar e excluir variáveis de configuração)
- Certas variáveis não podem ser alteradas com o servidor em funcionamento ou apresentam restrições (como 'autovacuum' e 'bonjour_name', por exemplo).
As vantagens obtidas são várias:
- O resultado da consulta das variáveis de configuração pode ser alterado, respeitando-se as restrições de cada variável e seus valores aceitos .
- São mostradas mais informações sobre cada item de configuração (11 colunas)
- É possível selecionar um subconjunto dados para ser visualizado (utilizando comando SELECT!!!)
- É possível (e fácil) montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 (ou mais) variáveis importantes, pode ser empregado apenas um comando que retorne exclusivamente as variáveis desejadas.
Exemplos de utilização de PG_SETTINGS:
1 - Sintaxe básica, retornando todas as configurações.
SELECT * FROM pg_settings;
2 - Seleção de variáveis de configuração que comecem com 'AUTO'.
SELECT * FROM pg_settings WHERE NAME LIKE 'auto%';
3 - Consulta de variáveis de configuração com UNION.
SELECT * FROM pg_settings WHERE NAME LIKE 'auto%'
UNION
SELECT * FROM pg_settings WHERE NAME LIKE 'Date%';
4 - Consulta de variáveis de configuração com junção.
SELECT * FROM pg_settings p1, pg_settings p2
WHERE P1.NAME LIKE 'log_%' AND P1.NAME = P2.NAME AND UPPER(P2.UNIT) = 'KB';
Abaixo, exemplos de sua sintaxe:
- SHOW ALL
- SHOW work_mem
No entanto, apresenta uma série de desagradáveis limitações:
- O resultado da consulta das variáveis de configuração não pode ser alterado (essa atualização pode ser feita pelo comando SET).
- Os campos mostrados são poucos e até insuficientes dependendo da necessidade (nome do parâmetro, valor corrente e descrição)
- Não é possível selecionar um subconjunto dados para ser visualizado (linhas e colunas), tampouco agregar colunas adicionais na consulta retornada.
- Não é possível montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 variáveis importantes, deve-se usar SHOW ALL ou fazer 10 comandos SHOW indicando as variáveis desejadas.
Apesar de sua utilizada, este comando simplesmente limita a flexibilidade de consultas e relatórios sobre as configurações do PostgreSQL. Como contornar esta limitação?
A tabela virtual PG_SETINGS contorna TODAS AS LIMITAÇÕES apresentadas pelo comando SHOW. As únicas restrições são:
- Não se pode inserir registros ou excluí-los (assim como não se pode adicionar e excluir variáveis de configuração)
- Certas variáveis não podem ser alteradas com o servidor em funcionamento ou apresentam restrições (como 'autovacuum' e 'bonjour_name', por exemplo).
As vantagens obtidas são várias:
- O resultado da consulta das variáveis de configuração pode ser alterado, respeitando-se as restrições de cada variável e seus valores aceitos .
- São mostradas mais informações sobre cada item de configuração (11 colunas)
- É possível selecionar um subconjunto dados para ser visualizado (utilizando comando SELECT!!!)
- É possível (e fácil) montar consultas envolvendo junção, union e outros recursos da linguagem SQL.
- Para se visualizar 10 (ou mais) variáveis importantes, pode ser empregado apenas um comando que retorne exclusivamente as variáveis desejadas.
Exemplos de utilização de PG_SETTINGS:
1 - Sintaxe básica, retornando todas as configurações.
SELECT * FROM pg_settings;
2 - Seleção de variáveis de configuração que comecem com 'AUTO'.
SELECT * FROM pg_settings WHERE NAME LIKE 'auto%';
3 - Consulta de variáveis de configuração com UNION.
SELECT * FROM pg_settings WHERE NAME LIKE 'auto%'
UNION
SELECT * FROM pg_settings WHERE NAME LIKE 'Date%';
4 - Consulta de variáveis de configuração com junção.
SELECT * FROM pg_settings p1, pg_settings p2
WHERE P1.NAME LIKE 'log_%' AND P1.NAME = P2.NAME AND UPPER(P2.UNIT) = 'KB';
segunda-feira, 22 de setembro de 2008
Que tipo de aplicação você implementa com PostgreSQL?
Esta e a enquete que está sendo realizada no momento pela comunidade postgresql.org
Participe!!! O link está abaixo:
http://www.postgresql.org/community/
Participe!!! O link está abaixo:
http://www.postgresql.org/community/
segunda-feira, 8 de setembro de 2008
Exemplos de Código e SQL e PgSQL
O site abaixo apresenta uma série de bons exemplos (e simples de reproduzir) de código SQL e PlPgSQL. São abordados assuntos como funções matemáticas, de datas, índices, tabelas, consultas, cursores, tipos de dados, entre outros recursos do banco. Boa sugestão para a lista de favoritos de quem trabalha com o PostgreSQL:
http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm
http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm
sexta-feira, 5 de setembro de 2008
pgCon 2008 - Participe Você Também!
Antes tarde do que nunca, segue lembrete do maior evento de PostgreSQL nacional, a pgCon 2008!
A expectativa dos organizadores é de um grande crescimento do número de participantes em relação ao ano passado.
Site oficial: http://pgcon.postgresql.org.br/
Inscrições: http://pgcon.postgresql.org.br/inscricoes.html
Grade do evento: http://pgcon.postgresql.org.br/programacao.html
Aproveite os preços promocionais!
A expectativa dos organizadores é de um grande crescimento do número de participantes em relação ao ano passado.
Site oficial: http://pgcon.postgresql.org.br/
Inscrições: http://pgcon.postgresql.org.br/inscricoes.html
Grade do evento: http://pgcon.postgresql.org.br/programacao.html
Aproveite os preços promocionais!
Templates no PostgreSQL...
Templates são uma poderosa ferramenta para a criação de bancos de dados padronizados, e uma característica estável implementada no PostgreSQL. No entanto, nem sempre é bem corretamente utilizada e entendida. Neste texto será comentado o seu funcionamento e os benefícios que podem ser obtidos.
* Templates de Bancos de Dados
Templates são modelos. Criar um banco de dados com base em um template é simplesmente utilizar um banco de dados como modelo para a criação de outro. Como ganhos do uso de templates podem ser destacados:
- Melhor padronização dos bancos de dados criados
- Maior velocidade na criação de novos bancos de dados, evitando-se a necessidade de se rodar scripts e criar objetos após a sua criação.
* Implementação no PostgreSQL
No PostgreSQL, a criação de qualquer banco de dados se dá pela cópia de objetos e estrutura de um banco já existente para o novo a ser criado. São definidos por padrão dois bancos de dados templates: template0 e template1, no entanto qualquer banco de dados pode ser utilizado como template em um servidor PostgreSQL.
O template0 é mais enxuto e deve ser predominantemente utilizado para criação de bancos que serão populados via restauração de backup. Não apresenta todos objetos e funções do template1, o qual é mais utilizado e é recomendado como principal padrão para criação de novos bancos de dados.
Acrescentar funções, tabelas e outros objetos a um banco de dados template, é torná-los disponíveis a qualquer novo banco de dados que for criado posteriormente. A atualização de bancos criados anteriormente com o template que foi atualizado deve ser feita de forma manual caso seja necessária.
Abaixo, a criação de bancos de dados utilizando templates:
-- Criação sem parâmetro -T, cria banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate "Banco de Dados Template 1"
-- Criação de novo banco de dados com base no template 0
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres -T template0 dbtemplate2 "Banco de Dados Template 0"
-- Criação de novo banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate3 "Banco de Dados Template 1"
* Personalização de Template no PostgreSQL
Personalizar um template para uso na criação de novos bancos de dados é relativamente fácil e demanda apenas algumas precauções:
- Evitar alterar os templates padrão do PostgreSQL (template0 e template1) - Crie novos bancos de dados e personalize-os, utilizando-os posteriormente como templates.
- Planeje que tipo de função o banco de dados template deve oferecer: banco de dados web, banco de dados para Data Warehouse, etc., criando templates específicos mais adequados.
- Nomeie seus templates de forma distinta, para diferenciá-los de bancos de dados de aplicações.
- Não utilizar bancos de dados de aplicações como templates. O processo de cópia do template para o novo banco de dados exige que o template não esteja sofrendo acesso por parte de outros usuários, o que pode gerar erros na criação de novos bancos.
Abaixo, uma proposta de atividade para testar a personalização do banco de dados template dbtemplate3:
- Abra o banco de dados dbtemplate3.
- Crie as rotinas CPF_formatar e CPF_validar que estão na seção de algoritmos deste blog.
- Feche o banco de dados.
- Crie novo banco de dados, chamado "corporativo", utilizando como template o dbtemplate3.
- Abra o banco de dados "corporativo" e localize as funções que você adicionou ao template.
* Templates de Bancos de Dados
Templates são modelos. Criar um banco de dados com base em um template é simplesmente utilizar um banco de dados como modelo para a criação de outro. Como ganhos do uso de templates podem ser destacados:
- Melhor padronização dos bancos de dados criados
- Maior velocidade na criação de novos bancos de dados, evitando-se a necessidade de se rodar scripts e criar objetos após a sua criação.
* Implementação no PostgreSQL
No PostgreSQL, a criação de qualquer banco de dados se dá pela cópia de objetos e estrutura de um banco já existente para o novo a ser criado. São definidos por padrão dois bancos de dados templates: template0 e template1, no entanto qualquer banco de dados pode ser utilizado como template em um servidor PostgreSQL.
O template0 é mais enxuto e deve ser predominantemente utilizado para criação de bancos que serão populados via restauração de backup. Não apresenta todos objetos e funções do template1, o qual é mais utilizado e é recomendado como principal padrão para criação de novos bancos de dados.
Acrescentar funções, tabelas e outros objetos a um banco de dados template, é torná-los disponíveis a qualquer novo banco de dados que for criado posteriormente. A atualização de bancos criados anteriormente com o template que foi atualizado deve ser feita de forma manual caso seja necessária.
Abaixo, a criação de bancos de dados utilizando templates:
-- Criação sem parâmetro -T, cria banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate "Banco de Dados Template 1"
-- Criação de novo banco de dados com base no template 0
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres -T template0 dbtemplate2 "Banco de Dados Template 0"
-- Criação de novo banco de dados com base no template 1
C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres dbtemplate3 "Banco de Dados Template 1"
* Personalização de Template no PostgreSQL
Personalizar um template para uso na criação de novos bancos de dados é relativamente fácil e demanda apenas algumas precauções:
- Evitar alterar os templates padrão do PostgreSQL (template0 e template1) - Crie novos bancos de dados e personalize-os, utilizando-os posteriormente como templates.
- Planeje que tipo de função o banco de dados template deve oferecer: banco de dados web, banco de dados para Data Warehouse, etc., criando templates específicos mais adequados.
- Nomeie seus templates de forma distinta, para diferenciá-los de bancos de dados de aplicações.
- Não utilizar bancos de dados de aplicações como templates. O processo de cópia do template para o novo banco de dados exige que o template não esteja sofrendo acesso por parte de outros usuários, o que pode gerar erros na criação de novos bancos.
Abaixo, uma proposta de atividade para testar a personalização do banco de dados template dbtemplate3:
- Abra o banco de dados dbtemplate3.
- Crie as rotinas CPF_formatar e CPF_validar que estão na seção de algoritmos deste blog.
- Feche o banco de dados.
- Crie novo banco de dados, chamado "corporativo", utilizando como template o dbtemplate3.
- Abra o banco de dados "corporativo" e localize as funções que você adicionou ao template.
segunda-feira, 18 de agosto de 2008
DBLINK: Consultas, Comandos SQL, Transações e Funções
A realização de consultas e a execução de comandos SQL são as principais atividades realizadas com o DBLINK. Continuando os comentários iniciados no post anterior, este tópico apresenta exemplos de aplicação desta tecnologia do PostgreSQL.
As rotinas dblink e dblink_exec permitem a realização de consultas, inserção, alteração e exclusão de dados em bancos distintos. Podem ser utilizadas dentro de uma transação ou de uma função.
* Consultas
As consultas são feitas com a rotina dblink e seguem uma sintaxe diferente do tradicional. Pode ser criada uma conexão para a execução da consulta ou pode ser usada uma que já tenha sido criada anbteriormente.
A rotina dblink tem dois parâmetros, sendo o primeiro a conexão e o segundo o comando a ser executado. Além dos parâmetros, o dblink pede que se liste os campo e seus tipos de dados.
O exemplo abaixo mostra uma consulta que utiliza a conexão "myconn", criada pela função dblink_connect:
--Consulta em banco de dados de estoque de dentro do banco de dados de venda usando conexão criada
SELECT estoque_prod.* FROM dblink('myconn', 'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);
O exemplo abaixo mostra uma consulta que apresenta como parâmetro uma string para conexão:
--Consulta em banco de dados de estoque de dentro do banco de dados de venda com conexão dentro do comando
SELECT * FROM dblink('hostaddr=10.200.239.55 port=5432 dbname=estoque user=postgres password=post',
'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);
* Inserção, Alteração e Exclusão de Dados
Estas operações são implementadas por meio da rotina dblink_exec. Como parâmetros são fornecidos a conexão e o comando a ser executado:
--Inserção de registros com DBLINK_EXEC
--Detalhe: uso das aspas simples. As mesmas devem estar duplicadas.
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (6, ''Relogio'', 400, 110.90);');
--Atualização de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - 1 WHERE cod_prod = 6;');
--Exclusão de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'DELETE FROM ESTOQUE_ITEM WHERE cod_prod = 6;');
* Dblink Dentro de uma Transação
Comandos do dblink funcionam normalmente dentro de transações.
Abaixo, um exemplo de transação de venda, sensibilidando dois bancos de dados distintos:
BEGIN;
INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (1, 1000 , DEFAULT);
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (5, ''Relogio'', 400, 110.90);');
END;
* Uso do Dblink Dentro de Funções
O dblink pode ser livremente utilizado dentro de funções. Pode ainda apresentar parametrização e a chamada à função pode estar inscrita dentro de uma transação maior.
Abaixo, um exemplo com dois parâmetros, simulando uma transação de compra e venda:
-- Uso de dblink DENTRO de uma função
CREATE OR REPLACE FUNCTION func_venda(cod_prd integer, quant integer) RETURNS INTEGER AS $$
DECLARE
comando_venda TEXT;
comando_estoque TEXT;
BEGIN
comando_venda := 'INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (' || $1 || ',' || $2 || ', DEFAULT);';
EXECUTE comando_venda;
comando_estoque := 'SELECT dblink_exec(''myconn'', ''UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - ' || $2 || ' WHERE cod_prod = ' || $1 || ''');';
EXECUTE comando_estoque;
RETURN 1; --Retorno com sucesso
END;
$$ LANGUAGE plpgsql;
SELECT func_venda(1,1);
As rotinas dblink e dblink_exec permitem a realização de consultas, inserção, alteração e exclusão de dados em bancos distintos. Podem ser utilizadas dentro de uma transação ou de uma função.
* Consultas
As consultas são feitas com a rotina dblink e seguem uma sintaxe diferente do tradicional. Pode ser criada uma conexão para a execução da consulta ou pode ser usada uma que já tenha sido criada anbteriormente.
A rotina dblink tem dois parâmetros, sendo o primeiro a conexão e o segundo o comando a ser executado. Além dos parâmetros, o dblink pede que se liste os campo e seus tipos de dados.
O exemplo abaixo mostra uma consulta que utiliza a conexão "myconn", criada pela função dblink_connect:
--Consulta em banco de dados de estoque de dentro do banco de dados de venda usando conexão criada
SELECT estoque_prod.* FROM dblink('myconn', 'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);
O exemplo abaixo mostra uma consulta que apresenta como parâmetro uma string para conexão:
--Consulta em banco de dados de estoque de dentro do banco de dados de venda com conexão dentro do comando
SELECT * FROM dblink('hostaddr=10.200.239.55 port=5432 dbname=estoque user=postgres password=post',
'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);
* Inserção, Alteração e Exclusão de Dados
Estas operações são implementadas por meio da rotina dblink_exec. Como parâmetros são fornecidos a conexão e o comando a ser executado:
--Inserção de registros com DBLINK_EXEC
--Detalhe: uso das aspas simples. As mesmas devem estar duplicadas.
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (6, ''Relogio'', 400, 110.90);');
--Atualização de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - 1 WHERE cod_prod = 6;');
--Exclusão de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'DELETE FROM ESTOQUE_ITEM WHERE cod_prod = 6;');
* Dblink Dentro de uma Transação
Comandos do dblink funcionam normalmente dentro de transações.
Abaixo, um exemplo de transação de venda, sensibilidando dois bancos de dados distintos:
BEGIN;
INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (1, 1000 , DEFAULT);
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (5, ''Relogio'', 400, 110.90);');
END;
* Uso do Dblink Dentro de Funções
O dblink pode ser livremente utilizado dentro de funções. Pode ainda apresentar parametrização e a chamada à função pode estar inscrita dentro de uma transação maior.
Abaixo, um exemplo com dois parâmetros, simulando uma transação de compra e venda:
-- Uso de dblink DENTRO de uma função
CREATE OR REPLACE FUNCTION func_venda(cod_prd integer, quant integer) RETURNS INTEGER AS $$
DECLARE
comando_venda TEXT;
comando_estoque TEXT;
BEGIN
comando_venda := 'INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (' || $1 || ',' || $2 || ', DEFAULT);';
EXECUTE comando_venda;
comando_estoque := 'SELECT dblink_exec(''myconn'', ''UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - ' || $2 || ' WHERE cod_prod = ' || $1 || ''');';
EXECUTE comando_estoque;
RETURN 1; --Retorno com sucesso
END;
$$ LANGUAGE plpgsql;
SELECT func_venda(1,1);
quinta-feira, 14 de agosto de 2008
DBLINK: Introdução, dblink_connect/ dblink_disconnect e dblink_get_connections
O dblink é um dos mais importantes contribs do PostgreSQL. Consiste em um conjunto de métodos para acesso a outros bancos de dados do PostgreSQL em uma mesma sessão.
Com esta biblioteca é possível fazer consultas, inserções, alteração e exclusão de dados em mais de um banco de dados (PostgreSQL) sem sair da sessão corrente, o que facilita muito a comunicação entre bancos de dados.
Não faz parte do núcleo deste SGBD, mas pode ser instalado sem grandes problemas e é relativamente fácil de se utilizar.
As principais funções do dblink são:
- dblink_connect
- dblink_get_connections
- dblink - para consultas
- dblink_exec - utilizado na inserção, alteração e exclusão de dados
- dblink_disconnect - liberar conexões dblink
Existem alternativas ao uso desta solução para a comunicaçção entre bancos de dados, tais como cargas diárias de bancos de dados, e outros softwares, mas o dblink tem sido bastante empregado na construção da interoperabilidade de bancos de dados do PostgreSQL e se posiciona como boa alternativa.
Ressalvas:
- Este artigo não visa esgotar a sintaxe do dblink e todos os seus comandos. Apenas mostra uma visão geral do seu funcionamento.
Para testar o dblink, crie dois bancos de dados distintos, chamados estoque e vendas, no mesmo servidor ou em máquinas distintas, e insira as seguintes tabelas e dados:
--Banco de Dados VENDA
CREATE TABLE VENDA_ITEM (
cod_venda SERIAL PRIMARY KEY,
cod_prod INTEGER NOT NULL,
quant_vendida INTEGER NOT NULL,
data DATE DEFAULT current_date NOT NULL);
--Banco de Dados ESTOQUE
CREATE DATABASE ESTOQUE;
DROP TABLE ESTOQUE_ITEM;
CREATE TABLE ESTOQUE_ITEM (
cod_prod SERIAL PRIMARY KEY,
desc_prod VARCHAR(50) NOT NULL,
quant_estocada INTEGER NOT NULL CHECK (quant_estocada > 0),
preco NUMERIC(10,2) CHECK (preco > 0));
INSERT INTO ESTOQUE_ITEM VALUES (1, 'Lápis Preto', 10000, 1.50);
INSERT INTO ESTOQUE_ITEM VALUES (2, 'Borracha', 50, 0.90);
INSERT INTO ESTOQUE_ITEM VALUES (3, 'Caderno 200 Folhas', 175, 8.90);
INSERT INTO ESTOQUE_ITEM VALUES (4, 'Agenda do Ano', 40, 12.50);
* dblink_connect
A rotina dblink_connect cria uma conexão na sessão corrente do PostgreSQL para outro banco de dados. Antes de utiliza-la, é necessário configurar o banco de dados para aceitar a conexão, alterando o arquivo pg_hba e as permissões do usuário.
Como parâmetros, devem ser fornecidos o nome da conexão e uma string de parâmetros de conexão para o banco a ser acessado:
--Criação de Conexão com o DBLINK
-- Conecta com o banco de dados estoque
SELECT dblink_connect('myconn', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Cria nova conecxão com o banco de dados estoque, sem interferir na anterior
SELECT dblink_connect('myconn2', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Conecta com o banco de dados vendas
SELECT dblink_connect('myconn3', 'hostaddr=10.211.239.55 port=5432 dbname=vendas user=postgres password=post');
Ressalvas:
- Evite criar muitas conexões pelo dblink. Cada conexão que for criada vai ocupar memória extra e pode impactar no desempenho do seu sistema. A execução dos exemplos anteriores vai criar três conexões ativas na memória.
- Pode haver lentidão de acesso na conexão dblink. Se um dos bancos de dados conectados estiver com problemas de lentidão, a conexão como um todo será lenta.
* dblink_get_connections
Esta função mostra as conexões ativas do dblink. Não mostra as demais conexões ao banco de dados.
Não apresenta parâmetros. Abaixo, a sintaxe:
--Consultando conexões abertas com DBLINK
SELECT dblink_get_connections();
* dblink_disconnect
A rotina dblink_disconnect encerra conexões criadas com o dblink, liberando a memória. Recebe como parâmetro o nome da conexão a ser encerrada. Execute este comando periodicamente para liberar recursos de hardware, evitando manter conexões ociosas.
--Desconexão
SELECT dblink_disconnect('myconn3');
Os próximos posts comentarão as demais funcionalidades do dblink, apresentando exemplos.
Referência:
- Livro PostgreSQL Prático - dblink
Com esta biblioteca é possível fazer consultas, inserções, alteração e exclusão de dados em mais de um banco de dados (PostgreSQL) sem sair da sessão corrente, o que facilita muito a comunicação entre bancos de dados.
Não faz parte do núcleo deste SGBD, mas pode ser instalado sem grandes problemas e é relativamente fácil de se utilizar.
As principais funções do dblink são:
- dblink_connect
- dblink_get_connections
- dblink - para consultas
- dblink_exec - utilizado na inserção, alteração e exclusão de dados
- dblink_disconnect - liberar conexões dblink
Existem alternativas ao uso desta solução para a comunicaçção entre bancos de dados, tais como cargas diárias de bancos de dados, e outros softwares, mas o dblink tem sido bastante empregado na construção da interoperabilidade de bancos de dados do PostgreSQL e se posiciona como boa alternativa.
Ressalvas:
- Este artigo não visa esgotar a sintaxe do dblink e todos os seus comandos. Apenas mostra uma visão geral do seu funcionamento.
Para testar o dblink, crie dois bancos de dados distintos, chamados estoque e vendas, no mesmo servidor ou em máquinas distintas, e insira as seguintes tabelas e dados:
--Banco de Dados VENDA
CREATE TABLE VENDA_ITEM (
cod_venda SERIAL PRIMARY KEY,
cod_prod INTEGER NOT NULL,
quant_vendida INTEGER NOT NULL,
data DATE DEFAULT current_date NOT NULL);
--Banco de Dados ESTOQUE
CREATE DATABASE ESTOQUE;
DROP TABLE ESTOQUE_ITEM;
CREATE TABLE ESTOQUE_ITEM (
cod_prod SERIAL PRIMARY KEY,
desc_prod VARCHAR(50) NOT NULL,
quant_estocada INTEGER NOT NULL CHECK (quant_estocada > 0),
preco NUMERIC(10,2) CHECK (preco > 0));
INSERT INTO ESTOQUE_ITEM VALUES (1, 'Lápis Preto', 10000, 1.50);
INSERT INTO ESTOQUE_ITEM VALUES (2, 'Borracha', 50, 0.90);
INSERT INTO ESTOQUE_ITEM VALUES (3, 'Caderno 200 Folhas', 175, 8.90);
INSERT INTO ESTOQUE_ITEM VALUES (4, 'Agenda do Ano', 40, 12.50);
* dblink_connect
A rotina dblink_connect cria uma conexão na sessão corrente do PostgreSQL para outro banco de dados. Antes de utiliza-la, é necessário configurar o banco de dados para aceitar a conexão, alterando o arquivo pg_hba e as permissões do usuário.
Como parâmetros, devem ser fornecidos o nome da conexão e uma string de parâmetros de conexão para o banco a ser acessado:
--Criação de Conexão com o DBLINK
-- Conecta com o banco de dados estoque
SELECT dblink_connect('myconn', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Cria nova conecxão com o banco de dados estoque, sem interferir na anterior
SELECT dblink_connect('myconn2', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Conecta com o banco de dados vendas
SELECT dblink_connect('myconn3', 'hostaddr=10.211.239.55 port=5432 dbname=vendas user=postgres password=post');
Ressalvas:
- Evite criar muitas conexões pelo dblink. Cada conexão que for criada vai ocupar memória extra e pode impactar no desempenho do seu sistema. A execução dos exemplos anteriores vai criar três conexões ativas na memória.
- Pode haver lentidão de acesso na conexão dblink. Se um dos bancos de dados conectados estiver com problemas de lentidão, a conexão como um todo será lenta.
* dblink_get_connections
Esta função mostra as conexões ativas do dblink. Não mostra as demais conexões ao banco de dados.
Não apresenta parâmetros. Abaixo, a sintaxe:
--Consultando conexões abertas com DBLINK
SELECT dblink_get_connections();
* dblink_disconnect
A rotina dblink_disconnect encerra conexões criadas com o dblink, liberando a memória. Recebe como parâmetro o nome da conexão a ser encerrada. Execute este comando periodicamente para liberar recursos de hardware, evitando manter conexões ociosas.
--Desconexão
SELECT dblink_disconnect('myconn3');
Os próximos posts comentarão as demais funcionalidades do dblink, apresentando exemplos.
Referência:
- Livro PostgreSQL Prático - dblink
sexta-feira, 1 de agosto de 2008
Aprovado! Instalador Amigável para Linux
Mês passado criei um post sobre novos instaladores do PostgreSQL para Linux e Mac. Depois de um tempinho, resolvi testar no Linux.
Na plataforma Linux, a instalação do PostgreSQL normalmente é feita com uma série de comandos do sistema operacional, o que é uma boa maneira de ter maior controle dos resultados. No entanto este processo pode ser dificultoso para neófitos e mesmo para os mais experientes. O procedimento também pode ficar difícil se algo não sair conforme o manual ou tutorial utilizado estiver indicando.
O instalador novo é uma boa opção para quem quer um servidor rodando, mas não deseja perder tempo em configurações avançadas e digitação em prompts de comando. Sua interface é gráfica, simples e agradável.
Os testes foram feitos em uma máquina com Linux Fedora 2.
Preparação da Instalação
Faça o login na estação Linux como usuário root. O próximo passo é fazer o download do instalador e copiá-lo em uma pasta do sistema de arquivos.
Abra o terminal, entre no diretório em que está o instalador e use o comando exec para rodar o programa:
exec /home/usr/postgresql-8.3.3-2-linux.bin
O sistema abrirá uma tela com um assistente amigável para a instalação do PostgreSQL como servidor na máquina.
Etapas da Instalação
As telas são bem simples e futuras versões do instalador devem ter novos recursos. Abaixo, a tela inicial.
Em seguida aparece a tela de seleção do diretório de instalação.
Telas de diretório de dados e de senha do superusuário. Por padrão este instalador cria um superusuário chamado POSTGRES .
Etapas de definição da porta de comunicação utilizada (padrão 5432) e locale (localização geográfica associada a um conjunto de parâmetros: país, idioma, etc.).
Terlas de seleção de linguagens procedurais para as funções e procedimentos e a progress bar de instalação. Observe que as linguagens serão instaladas no template1, geralmente utilizado como base para criação de novos bancos de dados.
Resultado da Instalação
Imediatamente após a instalação pode-se trabalhar com o PostgreSQL.
Abaixo, a tela de conclusão da instalação e visualização dos componentes instalados.
Considerações Finais
Espero que este instalador facilite a vida de quem está iniciando na ferramenta ou de quem tem de fazer várias instalações em curto prazo. Acredito que certas etapas poderiam ser unificadas e que novos parâmetros poderiam ser configuráveis para uma instalação mais personalizada. Apesar destas ressalvas, minha avaliação é super positiva.
Na plataforma Linux, a instalação do PostgreSQL normalmente é feita com uma série de comandos do sistema operacional, o que é uma boa maneira de ter maior controle dos resultados. No entanto este processo pode ser dificultoso para neófitos e mesmo para os mais experientes. O procedimento também pode ficar difícil se algo não sair conforme o manual ou tutorial utilizado estiver indicando.
O instalador novo é uma boa opção para quem quer um servidor rodando, mas não deseja perder tempo em configurações avançadas e digitação em prompts de comando. Sua interface é gráfica, simples e agradável.
Os testes foram feitos em uma máquina com Linux Fedora 2.
Preparação da Instalação
Faça o login na estação Linux como usuário root. O próximo passo é fazer o download do instalador e copiá-lo em uma pasta do sistema de arquivos.
Abra o terminal, entre no diretório em que está o instalador e use o comando exec para rodar o programa:
exec /home/usr/postgresql-8.3.3-2-linux.bin
O sistema abrirá uma tela com um assistente amigável para a instalação do PostgreSQL como servidor na máquina.
Etapas da Instalação
As telas são bem simples e futuras versões do instalador devem ter novos recursos. Abaixo, a tela inicial.
Em seguida aparece a tela de seleção do diretório de instalação.
Telas de diretório de dados e de senha do superusuário. Por padrão este instalador cria um superusuário chamado POSTGRES .
Etapas de definição da porta de comunicação utilizada (padrão 5432) e locale (localização geográfica associada a um conjunto de parâmetros: país, idioma, etc.).
Terlas de seleção de linguagens procedurais para as funções e procedimentos e a progress bar de instalação. Observe que as linguagens serão instaladas no template1, geralmente utilizado como base para criação de novos bancos de dados.
Resultado da InstalaçãoImediatamente após a instalação pode-se trabalhar com o PostgreSQL.
Abaixo, a tela de conclusão da instalação e visualização dos componentes instalados.
Considerações Finais
Espero que este instalador facilite a vida de quem está iniciando na ferramenta ou de quem tem de fazer várias instalações em curto prazo. Acredito que certas etapas poderiam ser unificadas e que novos parâmetros poderiam ser configuráveis para uma instalação mais personalizada. Apesar destas ressalvas, minha avaliação é super positiva.
sexta-feira, 25 de julho de 2008
Validação de CPF - Algoritmo Sem Loops
O algoritmo para validação de CPF pode ser implementado sem loops. A solução alternativa que aparece convertida para Pl/ PgSQL foi retirada do site do Clube do Hardware, com alguns ajustes. O código ficou bem pequeno e os loops foram trocados por operações com vetores. Observe que no PostgreSQL os vetores começam a partir da posição zero.
O código aparece abaixo:
-- Formatar CPF com vetor
CREATE OR REPLACE FUNCTION CPF_Validar_Sem_Loop(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF SEM LOOP
-- Conversão para o PL/ PGSQL: Cláudio Bezera Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://forum.clubedohardware.com.br/validacao-cpf/51717
-- Retorna 1 para CPF correto.
DECLARE
vet_cpf integer [11]; --Recebe o CPF DIGITADO
dv integer; -- Primeiro número do DV (Dígito Verificador)
dv1 integer; -- Segundo número do DV (Dígito Verificador)
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (par_cpf FROM 1 FOR 1) as integer);
vet_cpf[1] := cast(substring (par_cpf FROM 2 FOR 1) as integer);
vet_cpf[2] := cast(substring (par_cpf FROM 3 FOR 1) as integer);
vet_cpf[3] := cast(substring (par_cpf FROM 4 FOR 1) as integer);
vet_cpf[4] := cast(substring (par_cpf FROM 5 FOR 1) as integer);
vet_cpf[5] := cast(substring (par_cpf FROM 6 FOR 1) as integer);
vet_cpf[6] := cast(substring (par_cpf FROM 7 FOR 1) as integer);
vet_cpf[7] := cast(substring (par_cpf FROM 8 FOR 1) as integer);
vet_cpf[8] := cast(substring (par_cpf FROM 9 FOR 1) as integer);
vet_cpf[9] := cast(substring (par_cpf FROM 10 FOR 1) as integer);
vet_cpf[10] := cast(substring (par_cpf FROM 11 FOR 1) as integer);
-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv:=0;
ELSE dv:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:=(vet_cpf[0]*11)+(vet_cpf[1]*10)+(vet_cpf[2]*9)+(vet_cpf[3]*8)+(vet_cpf[4]*7)+(vet_cpf[5]*6)+(vet_cpf[6]*5)+(vet_cpf[7]*4)+(vet_cpf[8]*3)+(dv*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv1:=0;
ELSE dv1:=(11-rest); END IF;
IF (dv = vet_cpf[9]) and (dv1 = vet_cpf[10]) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;
Sintaxe para a chamada da função criada:
SELECT CPF_Validar_Sem_Loop ('número do cpf');
Caso queira relembrar o algoritmo com loops ou ver ais comentários sobre o cálculo de CPF, clique aqui.
O código aparece abaixo:
-- Formatar CPF com vetor
CREATE OR REPLACE FUNCTION CPF_Validar_Sem_Loop(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF SEM LOOP
-- Conversão para o PL/ PGSQL: Cláudio Bezera Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://forum.clubedohardware.com.br/validacao-cpf/51717
-- Retorna 1 para CPF correto.
DECLARE
vet_cpf integer [11]; --Recebe o CPF DIGITADO
dv integer; -- Primeiro número do DV (Dígito Verificador)
dv1 integer; -- Segundo número do DV (Dígito Verificador)
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (par_cpf FROM 1 FOR 1) as integer);
vet_cpf[1] := cast(substring (par_cpf FROM 2 FOR 1) as integer);
vet_cpf[2] := cast(substring (par_cpf FROM 3 FOR 1) as integer);
vet_cpf[3] := cast(substring (par_cpf FROM 4 FOR 1) as integer);
vet_cpf[4] := cast(substring (par_cpf FROM 5 FOR 1) as integer);
vet_cpf[5] := cast(substring (par_cpf FROM 6 FOR 1) as integer);
vet_cpf[6] := cast(substring (par_cpf FROM 7 FOR 1) as integer);
vet_cpf[7] := cast(substring (par_cpf FROM 8 FOR 1) as integer);
vet_cpf[8] := cast(substring (par_cpf FROM 9 FOR 1) as integer);
vet_cpf[9] := cast(substring (par_cpf FROM 10 FOR 1) as integer);
vet_cpf[10] := cast(substring (par_cpf FROM 11 FOR 1) as integer);
-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv:=0;
ELSE dv:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:=(vet_cpf[0]*11)+(vet_cpf[1]*10)+(vet_cpf[2]*9)+(vet_cpf[3]*8)+(vet_cpf[4]*7)+(vet_cpf[5]*6)+(vet_cpf[6]*5)+(vet_cpf[7]*4)+(vet_cpf[8]*3)+(dv*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv1:=0;
ELSE dv1:=(11-rest); END IF;
IF (dv = vet_cpf[9]) and (dv1 = vet_cpf[10]) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;
Sintaxe para a chamada da função criada:
SELECT CPF_Validar_Sem_Loop ('número do cpf');
Caso queira relembrar o algoritmo com loops ou ver ais comentários sobre o cálculo de CPF, clique aqui.
sexta-feira, 18 de julho de 2008
Instalação no Linux e Mac com Apenas um Clique!
Estão disponíveis os betas de instaladores para Linux e Mac que permite a instalação com apenas "um clique". A criação destes instaladores foi feita péla equipe do EnterpriseDB. Espera-se que a dificuldade de instalação nestas plataformas seja minimizada.
Os instaladores contém o PostgreSQL 8.3.3 com pgAdmin 1.8.4, pl/Java e o plugin para debug da linguagem pl/pgsql.
O download pode ser feito em:
Linux 32 e 64 bits: http://www.postgresql.org/download/linux
Mac OS X 10.4: http://www.postgresql.org/download/macosx
Os instaladores contém o PostgreSQL 8.3.3 com pgAdmin 1.8.4, pl/Java e o plugin para debug da linguagem pl/pgsql.
O download pode ser feito em:
Linux 32 e 64 bits: http://www.postgresql.org/download/linux
Mac OS X 10.4: http://www.postgresql.org/download/macosx
quarta-feira, 9 de julho de 2008
PostgreSQL: Presença Fraca no Youtube
A presença do PostgreSQL entre os grandes SGBDs pode ser medida de diversas formas. Em busca de vídeos interessantes de PostgreSQL, fiz uma busca no Youtube, e me decepcionei. Encontrei apenas 356 vídeos. É muito pouco para cobrir todas as funcionalidades e possibilidades deste banco de dados.
Considerando-se que este resultado envolve todos os idiomas, os resultados são ainda mais fracos, pois temos muito pouca coisa em Português. A qualidade da imagem também deixa a desejar, possivelmente pelo formato do Youtube, que reduz a resolução e prejudica apresentações que capturam telas do computador e as filmagens de apresentações em telões multimídia. Faltam comentários nos vídeos, a visualização ainda é pequena por parte da comunidade e muitos permanecem sem avaliação (o Youtube tem uma graduação que vai até 5 estrelas). Vi bons vídeos em italiano, mas acho que a estrada a ser trilhada pelo PostgreSQL em vídeo ainda está no seu início.
Para ter uma visão comparativa, fiz outras buscas para outros SGBDs. Entre os bancos de dados livres, apesar do PostgreSQL só estar atrás do MySQL, não se pode dizer que é uma boa colocação. A abrangência, quantidade e qualidade dos vídeos pode melhorar muito. O próprio campeão MySQL teve muito mais resultados encontrados, mas bem abaixo do que poderia ser. O fato é que ainda não utilizamos vídeos como forma de aprendizado e divulgação de conhecimento como poderíamos.
Mesmo os Bancos de Dados proprietários apresentam poucas opções, mas o SQL Server teve quase o dobro de vídeos que o PostgreSQL. O Oracle também decepcionou e pode ser alcançado pelo PostgreSQL com algum trabalho da comuindade.
O ranking total aparece abaixo. Faltam alguns SGBDs, mas o propósito não era uma busca exaustiva. A grande surpresa foi o DB2, com a terceira colocação. Os bancos de dados livres lideram com o MySQL e o PostgreSQL ocupa apenas uma posição intermediária, com o quinto lugar.
1 - MySQL: 1330
2 - SQL Server: 674
3 - IBM DB2: 461
4 - Oracle (busca por "Oracle SQL"): 406
5 -PostgreSQL: 356
6 - Firebird (busca por "Firebird SQL"): 171
7 - ACCESS (busca por "ACCESS SQL"): 50
8 - SQLite: 22
9 - ADABAS: 5
10 - HSQLDB: 0
O que podemos fazer a respeito?
Bom, creio que podemos influir positivamente quanto a esta questão:
- Assistir a vídeos de PostgreSQL no Youtube, dando feedback aos seus criadores, através de comentários e da colocação das estrelas, indicando os melhores. Esta ação permite que se possa estimular a produção de melhores vídeos e o reconhecimento dos autores.
- Postar novos fragmentos de palestras e de cursos com conteúdo novo e diversificados. Muitos eventos de Software Livre, de Banco de Dados em Geral e de PostgreSQL são gravados e poderia ser feita seleção e publicação de parte deste material.
- Produzir novos vídeos voltados para a internet. Podem ser pequenos, podem ser focados em temas específicos, indo do básico ao avançado. É a melhor forma de aprender a fazer mais e melhor.
Quem se habilita?
Este é um problema realmente grave?
Acredito que não, mas que é um ponto que pode ser melhor trabalhado pela comunidade do PostgreSQL.
Considerando-se que este resultado envolve todos os idiomas, os resultados são ainda mais fracos, pois temos muito pouca coisa em Português. A qualidade da imagem também deixa a desejar, possivelmente pelo formato do Youtube, que reduz a resolução e prejudica apresentações que capturam telas do computador e as filmagens de apresentações em telões multimídia. Faltam comentários nos vídeos, a visualização ainda é pequena por parte da comunidade e muitos permanecem sem avaliação (o Youtube tem uma graduação que vai até 5 estrelas). Vi bons vídeos em italiano, mas acho que a estrada a ser trilhada pelo PostgreSQL em vídeo ainda está no seu início.
Para ter uma visão comparativa, fiz outras buscas para outros SGBDs. Entre os bancos de dados livres, apesar do PostgreSQL só estar atrás do MySQL, não se pode dizer que é uma boa colocação. A abrangência, quantidade e qualidade dos vídeos pode melhorar muito. O próprio campeão MySQL teve muito mais resultados encontrados, mas bem abaixo do que poderia ser. O fato é que ainda não utilizamos vídeos como forma de aprendizado e divulgação de conhecimento como poderíamos.
Mesmo os Bancos de Dados proprietários apresentam poucas opções, mas o SQL Server teve quase o dobro de vídeos que o PostgreSQL. O Oracle também decepcionou e pode ser alcançado pelo PostgreSQL com algum trabalho da comuindade.
O ranking total aparece abaixo. Faltam alguns SGBDs, mas o propósito não era uma busca exaustiva. A grande surpresa foi o DB2, com a terceira colocação. Os bancos de dados livres lideram com o MySQL e o PostgreSQL ocupa apenas uma posição intermediária, com o quinto lugar.
1 - MySQL: 1330
2 - SQL Server: 674
3 - IBM DB2: 461
4 - Oracle (busca por "Oracle SQL"): 406
5 -PostgreSQL: 356
6 - Firebird (busca por "Firebird SQL"): 171
7 - ACCESS (busca por "ACCESS SQL"): 50
8 - SQLite: 22
9 - ADABAS: 5
10 - HSQLDB: 0
O que podemos fazer a respeito?
Bom, creio que podemos influir positivamente quanto a esta questão:
- Assistir a vídeos de PostgreSQL no Youtube, dando feedback aos seus criadores, através de comentários e da colocação das estrelas, indicando os melhores. Esta ação permite que se possa estimular a produção de melhores vídeos e o reconhecimento dos autores.
- Postar novos fragmentos de palestras e de cursos com conteúdo novo e diversificados. Muitos eventos de Software Livre, de Banco de Dados em Geral e de PostgreSQL são gravados e poderia ser feita seleção e publicação de parte deste material.
- Produzir novos vídeos voltados para a internet. Podem ser pequenos, podem ser focados em temas específicos, indo do básico ao avançado. É a melhor forma de aprender a fazer mais e melhor.
Quem se habilita?
Este é um problema realmente grave?
Acredito que não, mas que é um ponto que pode ser melhor trabalhado pela comunidade do PostgreSQL.
segunda-feira, 23 de junho de 2008
Limitações: Configuração de Case Sensitivity and Accent Sensitivity
A partir de hoje colocarei à medida que forem surgindo, limitações que encontro no PostgreSQL. A primeira limitação a ser mencionada é a falta de uma configuração para Case Sensitivity e Accent Sensitivity no SGBD.
Case Sensitivity e Accent Sensitivity são características particularmente desejáveis nos bancos de dados atuais. A não existência de configuração para o armazenamento e/ou recuperação de dados com base nestas características é uma lacuna a ser preenchida neste banco de dados. Atualmente não há este recurso e as buscas de dados levando em conta ou não maiúsculas e minúsculas exigem alterações nas aplicações (com UPPER, LOWER, LIKE e ILIKE).
Uma consulta com Case Sensitivity (sensível ao caso) retorna valores levando em consideração se os caracteres buscados estão em maiúsculas ou minúsculas. Desta forma, a busca pelo valor 'CLA' retorna resultados diferentes da busca por 'Cla'. O não uso de sensitividade nas buscas de caracteres é chamado de Case Insensitivity.
Uma consulta com Accent Sensitivity (sensível ao acento) retorna valores levando em consideração se os caracteres buscados estão em maiúsculas ou minúsculas. Desta forma, a busca pelo valor 'CLA' retorna resultados diferentes da busca por 'Cla'. O não uso de sensitividade nas buscas de caracteres acentuados é chamado de Accent Insensitivity.
Proposta:
- A implementação de tal característica como configuração poderia ser feita para o banco todo, por tablespace, tabela ou visão, facilitando a implementação e evitando alterações nas aplicações mais complexas que necessitam destas funcionalidades.
- Poderia ser feita via comando SET e na criação de objetos do banco, como na de tabelas, indicando que colunas são SENSITIVE e INSENSITIVE.
- O padrão deve ser INSENSITIVE por questões de desempenho.
Ressalvas:
- Novas propostas para esta funcionalidade são bem vindas e podem ser colocadas no comentário deste post.
quarta-feira, 11 de junho de 2008
Formatação de CPF - Uma função simples
O CPF, número do Cadastro de Pessoa Física, apresenta o formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador. Uma vez que o mesmo é bastante utilizado, sua validação e apresentação são opções que podem ser implementadas dentro do banco de dados , com reuso do mesmo código para uso em vários sistemas.
Neste post vamos fazer uma função simplificada para formatação e apresentação de CPF.
A consulta abaixo faz a formatação de um CPF. Apresenta a desvantagem de se ter de repetir várias vezes o valor do CPF a ser apresentado.
SELECT substring('12345678912' FROM 1 FOR 3) || '.' || substring('12345678912' FROM 4 FOR 3) || '.' || substring('12345678912' FROM 7 FOR 3) || '-' || substring('12345678912' FROM 10 FOR 2);
O uso de uma função aumenta a simplicidade e facilita o reuso:
CREATE OR REPLACE FUNCTION CPF_formatar(par_cpf varchar(11)) RETURNS varchar(14) AS $$
-- ROTINA DE FORMATAÇÃO DE CPF
-- Código PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Retorna o CPF formatado no formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador
-- em caso de erro retorna 'ERRO'
BEGIN
IF char_length(par_cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 'ERRO';
END IF;
RETURN substring(par_cpf FROM 1 FOR 3) || '.' || substring(par_cpf FROM 4 FOR 3) || '.' || substring(par_cpf FROM 7 FOR 3) || '-' || substring(par_cpf FROM 10 FOR 2);
END;
$$ LANGUAGE PLPGSQL;
SELECT CPF_formatar('12345678912');
Neste post vamos fazer uma função simplificada para formatação e apresentação de CPF.
A consulta abaixo faz a formatação de um CPF. Apresenta a desvantagem de se ter de repetir várias vezes o valor do CPF a ser apresentado.
SELECT substring('12345678912' FROM 1 FOR 3) || '.' || substring('12345678912' FROM 4 FOR 3) || '.' || substring('12345678912' FROM 7 FOR 3) || '-' || substring('12345678912' FROM 10 FOR 2);
O uso de uma função aumenta a simplicidade e facilita o reuso:
CREATE OR REPLACE FUNCTION CPF_formatar(par_cpf varchar(11)) RETURNS varchar(14) AS $$
-- ROTINA DE FORMATAÇÃO DE CPF
-- Código PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Retorna o CPF formatado no formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador
-- em caso de erro retorna 'ERRO'
BEGIN
IF char_length(par_cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 'ERRO';
END IF;
RETURN substring(par_cpf FROM 1 FOR 3) || '.' || substring(par_cpf FROM 4 FOR 3) || '.' || substring(par_cpf FROM 7 FOR 3) || '-' || substring(par_cpf FROM 10 FOR 2);
END;
$$ LANGUAGE PLPGSQL;
SELECT CPF_formatar('12345678912');
domingo, 8 de junho de 2008
Validação de CPF com Pl/ PgSQL
A partir de hoje passamos a divulgar algoritmos de funções e consultas que sejam de utilidade pública. A validação de CPF com Pl/ PgSQL foi escolhida em primeiro lugar por ser um algoritmo simples mas bastante útil (além disto, procurei em vários sites e não encontrei um exemplo em PL/ PGSQL).
O CPF é utilizado por muitos sistemas brasileiros como identificação dos indivíduos. Validar o CPF é fazer a verificação dos dois últimos dígitos que são gerados a partir dos nove primeiros. O código abaixo foi uma tradução mais ou menos literal do código em javascript deste site.
Talvez possa ser feita otimização ou melhoria neste algoritmo, mas a idéia é que vocês o melhorem e atualizem neste site. Estejam à vontade para utilizar e compartilhar este código.
CREATE OR REPLACE FUNCTION CPF_Validar(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF
-- Conversão para o PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://webmasters.neting.com/msg07743.html
-- Retorna 1 para CPF correto.
DECLARE
x real;
y real; --Variável temporária
soma integer;
dig1 integer; --Primeiro dígito do CPF
dig2 integer; --Segundo dígito do CPF
len integer; -- Tamanho do CPF
contloop integer; --Contador para loop
val_par_cpf varchar(11); --Valor do parâmetro
BEGIN
-- Teste do tamanho da string de entrada
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Inicialização
x := 0;
soma := 0;
dig1 := 0;
dig2 := 0;
contloop := 0;
val_par_cpf := $1; --Atribuição do parâmetro a uma variável interna
len := char_length(val_par_cpf);
x := len -1;
--Loop de multiplicação - dígito 1
contloop :=1;
WHILE contloop <= (len -2) LOOP
y := CAST(substring(val_par_cpf from contloop for 1) AS NUMERIC);
soma := soma + ( y * x);
x := x - 1;
contloop := contloop +1;
END LOOP;
dig1 := 11 - CAST((soma % 11) AS INTEGER);
if (dig1 = 10) THEN dig1 :=0 ; END IF;
if (dig1 = 11) THEN dig1 :=0 ; END IF;
-- Dígito 2
x := 11; soma :=0;
contloop :=1;
WHILE contloop <= (len -1) LOOP
soma := soma + CAST((substring(val_par_cpf FROM contloop FOR 1)) AS REAL) * x;
x := x - 1;
contloop := contloop +1;
END LOOP;
dig2 := 11 - CAST ((soma % 11) AS INTEGER);
IF (dig2 = 10) THEN dig2 := 0; END IF;
IF (dig2 = 11) THEN dig2 := 0; END IF;
--Teste do CPF
IF ((dig1 || '' || dig2) = substring(val_par_cpf FROM len-1 FOR 2)) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;
O CPF é utilizado por muitos sistemas brasileiros como identificação dos indivíduos. Validar o CPF é fazer a verificação dos dois últimos dígitos que são gerados a partir dos nove primeiros. O código abaixo foi uma tradução mais ou menos literal do código em javascript deste site.
Talvez possa ser feita otimização ou melhoria neste algoritmo, mas a idéia é que vocês o melhorem e atualizem neste site. Estejam à vontade para utilizar e compartilhar este código.
CREATE OR REPLACE FUNCTION CPF_Validar(par_cpf varchar(11)) RETURNS integer AS $$
-- ROTINA DE VALIDAÇÃO DE CPF
-- Conversão para o PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Algoritmo original: http://webmasters.neting.com/msg07743.html
-- Retorna 1 para CPF correto.
DECLARE
x real;
y real; --Variável temporária
soma integer;
dig1 integer; --Primeiro dígito do CPF
dig2 integer; --Segundo dígito do CPF
len integer; -- Tamanho do CPF
contloop integer; --Contador para loop
val_par_cpf varchar(11); --Valor do parâmetro
BEGIN
-- Teste do tamanho da string de entrada
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Inicialização
x := 0;
soma := 0;
dig1 := 0;
dig2 := 0;
contloop := 0;
val_par_cpf := $1; --Atribuição do parâmetro a uma variável interna
len := char_length(val_par_cpf);
x := len -1;
--Loop de multiplicação - dígito 1
contloop :=1;
WHILE contloop <= (len -2) LOOP
y := CAST(substring(val_par_cpf from contloop for 1) AS NUMERIC);
soma := soma + ( y * x);
x := x - 1;
contloop := contloop +1;
END LOOP;
dig1 := 11 - CAST((soma % 11) AS INTEGER);
if (dig1 = 10) THEN dig1 :=0 ; END IF;
if (dig1 = 11) THEN dig1 :=0 ; END IF;
-- Dígito 2
x := 11; soma :=0;
contloop :=1;
WHILE contloop <= (len -1) LOOP
soma := soma + CAST((substring(val_par_cpf FROM contloop FOR 1)) AS REAL) * x;
x := x - 1;
contloop := contloop +1;
END LOOP;
dig2 := 11 - CAST ((soma % 11) AS INTEGER);
IF (dig2 = 10) THEN dig2 := 0; END IF;
IF (dig2 = 11) THEN dig2 := 0; END IF;
--Teste do CPF
IF ((dig1 || '' || dig2) = substring(val_par_cpf FROM len-1 FOR 2)) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;
segunda-feira, 2 de junho de 2008
PGCon 2008: Seja um palestrante!
Uma das melhores formas de colaborar com a comunidade de software livre é compartilhar os conhecimentos. A PGCon 2008 oficialmente divulgou hoje a chamada de palestras e tutoriais.
A PGCon é a maior convenção brasileira, e possivelmente latino-americana de PostgreSQL. O site da edição de 2008 que ocorrerá dias 26 e 27 de Setembro de 2008 no Centro de Convenções da Unicamp em Campinas, SP, já está no ar aqui!
Esta é uma arena aberta àqueles que têm o espírito de compartilhamento e conteúdo para divulgar. Não perca esta oportunidade!
===================================================
Comunicado Oficial:
===================================================
A Comunidade Brasileira de PostgreSQL tem o prazer de convida-lo para
participar do PGCon Brasil 2008[1]. Após a realização do PGCon Brasil
2007[2], estamos novamente convidando a comunidade brasileira a enviar
suas propostas de trabalho para a segunda edição deste evento. O PGCon
Brasil 2008 será realizado nos dias 26 e 27 de setembro de 2008 na
UNICAMP[3] (Campinas-SP).
* Instruções:
- As palestras terão duração de 50 minutos, incluindo o tempo
para as perguntas. Haverá também a escolha de apenas um tutorial
que poderá ter até 120 minutos de duração.
- Qualquer palestrante pode enviar mais de uma proposta;
- O PGCon Brasil não se responsabiliza pelos gastos
com deslocamento, hospedagem e alimentação dos palestrantes;
- Todas as propostas deverão ser enviadas até 22/06/2008 em
formato texto para o e-mail; "pgcon@postgresql.org.br" contendo o
seguinte formulário:
* Formulário:
o Nome completo
o E-mail
o Telefones de contato
o Endereço completo
o Local de onde você virá (UF / Cidade)
o Mini currículo (até 500 caracteres)
o Título
o Tipo (palestra e/ou tutorial)
o Nível (iniciante, intermediário ou avançado)
o Resumo da palestra (até 500 caracteres)
o Descrição completa da palestra (até 3000 caracteres)
* Calendário:
- 02/06/2008 - Publicação da chamada de trabalhos;
- 22/06/2008 - Última data para envio de propostas;
- 11/07/2008 - Última data para seleção de palestras e envio
dos resultados aos palestrantes;
- 18/07/2008 - Última data para confirmação das palestras
selecionadas e publicação do resultado;
- 15/08/2008 - Última data para envio do rascunho das apresentações;
- 24/09/2008 - Última data para envio da versão final das apresentações;
A PGCon é a maior convenção brasileira, e possivelmente latino-americana de PostgreSQL. O site da edição de 2008 que ocorrerá dias 26 e 27 de Setembro de 2008 no Centro de Convenções da Unicamp em Campinas, SP, já está no ar aqui!
Esta é uma arena aberta àqueles que têm o espírito de compartilhamento e conteúdo para divulgar. Não perca esta oportunidade!
===================================================
Comunicado Oficial:
===================================================
A Comunidade Brasileira de PostgreSQL tem o prazer de convida-lo para
participar do PGCon Brasil 2008[1]. Após a realização do PGCon Brasil
2007[2], estamos novamente convidando a comunidade brasileira a enviar
suas propostas de trabalho para a segunda edição deste evento. O PGCon
Brasil 2008 será realizado nos dias 26 e 27 de setembro de 2008 na
UNICAMP[3] (Campinas-SP).
* Instruções:
- As palestras terão duração de 50 minutos, incluindo o tempo
para as perguntas. Haverá também a escolha de apenas um tutorial
que poderá ter até 120 minutos de duração.
- Qualquer palestrante pode enviar mais de uma proposta;
- O PGCon Brasil não se responsabiliza pelos gastos
com deslocamento, hospedagem e alimentação dos palestrantes;
- Todas as propostas deverão ser enviadas até 22/06/2008 em
formato texto para o e-mail; "pgcon@postgresql.org.br" contendo o
seguinte formulário:
* Formulário:
o Nome completo
o E-mail
o Telefones de contato
o Endereço completo
o Local de onde você virá (UF / Cidade)
o Mini currículo (até 500 caracteres)
o Título
o Tipo (palestra e/ou tutorial)
o Nível (iniciante, intermediário ou avançado)
o Resumo da palestra (até 500 caracteres)
o Descrição completa da palestra (até 3000 caracteres)
* Calendário:
- 02/06/2008 - Publicação da chamada de trabalhos;
- 22/06/2008 - Última data para envio de propostas;
- 11/07/2008 - Última data para seleção de palestras e envio
dos resultados aos palestrantes;
- 18/07/2008 - Última data para confirmação das palestras
selecionadas e publicação do resultado;
- 15/08/2008 - Última data para envio do rascunho das apresentações;
- 24/09/2008 - Última data para envio da versão final das apresentações;
quinta-feira, 29 de maio de 2008
Uso de SET em Funções Pl/PgSQL
Na versão 8.3 do PostgreSQL foi implementado um recurso interessante, que permite a parametrização da execução de uma função utilizando o comando SET no momento da sua criação. Desta forma, a configuração é alterada apenas durante a sua execução, retornando aos valores prévios após o seu encerramento.
Valores de tablespace padrão (default_tablespace), encoding do cliente (client_encoding) entre outros são definidos de forma explícita, de modo que se force o comportamento esperado da função não importando que programa faça a sua chamada de execução.
Existe também a opção de utilização da cláusula FROM CURRENT para fazer com que a alteração de configuração seja mantida após a execução da função.
Nem todos os parâmetros de execução podem ser alterados com o comando SET dentro de uma função. Os erros são revelados no momento de criação da função.
O comando SHOW mostra a lista de parâmetros e seus valores correntes.
Criação de Função com Parâmetros de Configuração
O código abaixo mostra a criação de uma função que atualiza a tabela salário. Após o corpo da função são definidos os valores dos itens de configuração array_nulls, default_tablespace, commit_delay e client_encoding.
CREATE TABLE SALARIO (cod integer PRIMARY KEY, provento real);
INSERT INTO SALARIO VALUES (1, 1000.00);
INSERT INTO SALARIO VALUES (2, 100.00);
CREATE OR REPLACE FUNCTION salario_aumento() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET array_nulls = OFF
SET default_tablespace = 'pg_default'
SET commit_delay TO 10
SET client_encoding TO UNICODE;
Mantendo Alterações na Configuração
O código abaixo mostra a criação de uma função na qual é alterado e mantido o valor do client_encoding. A cláusula FROM CURRENT indica que parâmetro terá o valor de configuração que foi alterado mantido.
CREATE OR REPLACE FUNCTION salario_parametros() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET client_encoding TO UNICODE
SET client_encoding FROM CURRENT;
Ressalva no Uso de SET em Funções
Alguns dos itens de configuração não são alteráveis pelo comando SET dentro de uma função. Neste caso, será retornado erro no ato da criação da função. No exemplo abaixo, tanto o log_checkpoints quanto o transaction_isolation não puderam ser alterados no escopo de uma função.
-- Não funcionaram
CREATE OR REPLACE FUNCTION salario_parametros_teste() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET log_checkpoints = ON
SET transaction_isolation = SERIALIZABLE;
ERROR: parameter "log_checkpoints" cannot be changed now
********** Error **********
ERROR: parameter "log_checkpoints" cannot be changed now
SQL state: 55P02
Valores de tablespace padrão (default_tablespace), encoding do cliente (client_encoding) entre outros são definidos de forma explícita, de modo que se force o comportamento esperado da função não importando que programa faça a sua chamada de execução.
Existe também a opção de utilização da cláusula FROM CURRENT para fazer com que a alteração de configuração seja mantida após a execução da função.
Nem todos os parâmetros de execução podem ser alterados com o comando SET dentro de uma função. Os erros são revelados no momento de criação da função.
O comando SHOW mostra a lista de parâmetros e seus valores correntes.
Criação de Função com Parâmetros de Configuração
O código abaixo mostra a criação de uma função que atualiza a tabela salário. Após o corpo da função são definidos os valores dos itens de configuração array_nulls, default_tablespace, commit_delay e client_encoding.
CREATE TABLE SALARIO (cod integer PRIMARY KEY, provento real);
INSERT INTO SALARIO VALUES (1, 1000.00);
INSERT INTO SALARIO VALUES (2, 100.00);
CREATE OR REPLACE FUNCTION salario_aumento() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET array_nulls = OFF
SET default_tablespace = 'pg_default'
SET commit_delay TO 10
SET client_encoding TO UNICODE;
Mantendo Alterações na Configuração
O código abaixo mostra a criação de uma função na qual é alterado e mantido o valor do client_encoding. A cláusula FROM CURRENT indica que parâmetro terá o valor de configuração que foi alterado mantido.
CREATE OR REPLACE FUNCTION salario_parametros() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET client_encoding TO UNICODE
SET client_encoding FROM CURRENT;
Ressalva no Uso de SET em Funções
Alguns dos itens de configuração não são alteráveis pelo comando SET dentro de uma função. Neste caso, será retornado erro no ato da criação da função. No exemplo abaixo, tanto o log_checkpoints quanto o transaction_isolation não puderam ser alterados no escopo de uma função.
-- Não funcionaram
CREATE OR REPLACE FUNCTION salario_parametros_teste() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET log_checkpoints = ON
SET transaction_isolation = SERIALIZABLE;
ERROR: parameter "log_checkpoints" cannot be changed now
********** Error **********
ERROR: parameter "log_checkpoints" cannot be changed now
SQL state: 55P02
quarta-feira, 14 de maio de 2008
Acessando o PostgreSQL a partir do OpenOffice Base com SDBC
O BrOffice Base é um banco de dados com características similares às do Microsoft Access. Dentre seus recursos, a interface com o usuário e os vários assistentes e funcionalidades de interface para a criação de tabelas, consultas, formulários e relatórios diversos tornaram esta ferramenta uma boa e produtiva opção para implementadores.
No entanto, bancos como o PostgreSQL apresentam maior robustez e recursos de gerenciamento. Você pode utilizar no seu projeto a produtividade do Base com a robustez do PostgreSQL. A integração é simples e neste texto é mostrada a integração via SDBC - Star(Office) DataBase Connectivity. Outras opções de midlleware de conexão seriam JDBC, ODBC e unixodbc. Cada uma destas tecnologias têm suas vantagens e limitações que estão fora do escopo deste texto.
O SDBC é um padrão que tem sido atualizado constantemente, daí a ser um dos mais empregados, ao lado do JDBC, tendo a vantagem de ser desatrelado à tecnologia Java.
1 - Instalação do Driver SDBC
Fazer o Download do Driver SDBC no site http://dba.openoffice.org/drivers/postgresql/index.html. No meu caso, criei uma pasta e fiz o download do arquivo compactado para:
C:\Program Files\PostgreSQL\8.3\SDBC.
A instalação do SDBC Driver pode ser feita pelo Writer ou outro programa do BrOffice. Todos os demais programas (de apresentações, planilha, etc.) poderão utilizá-lo. Selecione no menu menu Ferramentas\ Gerenciador de extensão... (Tools\Extension Manager)
Clique no botão adicionar e indique o caminho do arquivo compactado com o driver. Selecione o arquivo que você baixou e o BrOffice vai adicionar o driver.
2 - Configuração do Base para a Conexão.
Após instalar o driver, deve ser reiniciado o BrOffice para que o mesmo apareça para o BrOffice Base. A partir daí será possível a conexão.
Abra o Base e selecione a opção de conexão a um banco de dados existente. Desta forma, você ingressará em um assistente de conexão bem simplificado. Após a instalação do driver SDBC aparecerá em uma grande lista de opções o driver do PostgreSQL.
Após selecionar o PostgreSQL e disparar próximo, aparecerá um campo para digitação de parâmetros. Entre com o host e o nome do banco. Abaixo, os valores testados no exemplo.
host=localhost dbname=teste
Na próxima tela, é definido o usuário do PostgreSQL que vai conectar-se via Base. Este usuário deve estar previamente cadastrado e com as permissões necessárias. Ainda existe a opção de testar a conexão.
Não desmarque a opção de senha obrigatória a não ser que haja uma boa razão. De outro modo, a segurança do seu SGBD estará em risco.
Na última tela do assistente, se escolhe se o banco de dados passa a ser registrado na ferramenta. Em caso afirmativo, o Base vai gravar um arquivo de configurações para reutilização. Você pode gravá-lo na pasta do driver SDBC ou em outro lugar para melhor gerenciamento.
3 - Utilização
Após a conexão via assistente, o Base vai abrir a tela e mostrar os esquemas e objetos do PostgreSQL com a sua própria interface visual. É uma maneira de trabalhar com banco de dados de forma produtiva, pois é possível criar formulários e relatórios de forma rápida e simples, acessível a leigos, por exemplo. Para maiores detalhes, procurar por bons sites de BrOffice Base.
4 - Ressalvas
Os outros middlewares para conexão devem ser explorados por possuírem vantagens/ desvantagens diferentes do SDBC. Praticamente todos apresentam bugs conhecidos e limitações.
A questão de como utilizar o potencial do BrOffice Base está em aberto. A criatividade e adesão dos usuários é que vai fazer ou não este casamento frutificar.
No entanto, bancos como o PostgreSQL apresentam maior robustez e recursos de gerenciamento. Você pode utilizar no seu projeto a produtividade do Base com a robustez do PostgreSQL. A integração é simples e neste texto é mostrada a integração via SDBC - Star(Office) DataBase Connectivity. Outras opções de midlleware de conexão seriam JDBC, ODBC e unixodbc. Cada uma destas tecnologias têm suas vantagens e limitações que estão fora do escopo deste texto.
O SDBC é um padrão que tem sido atualizado constantemente, daí a ser um dos mais empregados, ao lado do JDBC, tendo a vantagem de ser desatrelado à tecnologia Java.
1 - Instalação do Driver SDBC
Fazer o Download do Driver SDBC no site http://dba.openoffice.org/drivers/postgresql/index.html. No meu caso, criei uma pasta e fiz o download do arquivo compactado para:
C:\Program Files\PostgreSQL\8.3\SDBC.
A instalação do SDBC Driver pode ser feita pelo Writer ou outro programa do BrOffice. Todos os demais programas (de apresentações, planilha, etc.) poderão utilizá-lo. Selecione no menu menu Ferramentas\ Gerenciador de extensão... (Tools\Extension Manager)
Clique no botão adicionar e indique o caminho do arquivo compactado com o driver. Selecione o arquivo que você baixou e o BrOffice vai adicionar o driver.
2 - Configuração do Base para a Conexão.
Após instalar o driver, deve ser reiniciado o BrOffice para que o mesmo apareça para o BrOffice Base. A partir daí será possível a conexão.
Abra o Base e selecione a opção de conexão a um banco de dados existente. Desta forma, você ingressará em um assistente de conexão bem simplificado. Após a instalação do driver SDBC aparecerá em uma grande lista de opções o driver do PostgreSQL.
Após selecionar o PostgreSQL e disparar próximo, aparecerá um campo para digitação de parâmetros. Entre com o host e o nome do banco. Abaixo, os valores testados no exemplo.
host=localhost dbname=teste
Na próxima tela, é definido o usuário do PostgreSQL que vai conectar-se via Base. Este usuário deve estar previamente cadastrado e com as permissões necessárias. Ainda existe a opção de testar a conexão.
Não desmarque a opção de senha obrigatória a não ser que haja uma boa razão. De outro modo, a segurança do seu SGBD estará em risco.
Na última tela do assistente, se escolhe se o banco de dados passa a ser registrado na ferramenta. Em caso afirmativo, o Base vai gravar um arquivo de configurações para reutilização. Você pode gravá-lo na pasta do driver SDBC ou em outro lugar para melhor gerenciamento.
3 - Utilização
Após a conexão via assistente, o Base vai abrir a tela e mostrar os esquemas e objetos do PostgreSQL com a sua própria interface visual. É uma maneira de trabalhar com banco de dados de forma produtiva, pois é possível criar formulários e relatórios de forma rápida e simples, acessível a leigos, por exemplo. Para maiores detalhes, procurar por bons sites de BrOffice Base.
4 - RessalvasOs outros middlewares para conexão devem ser explorados por possuírem vantagens/ desvantagens diferentes do SDBC. Praticamente todos apresentam bugs conhecidos e limitações.
A questão de como utilizar o potencial do BrOffice Base está em aberto. A criatividade e adesão dos usuários é que vai fazer ou não este casamento frutificar.
segunda-feira, 28 de abril de 2008
Você Realmente Conhece a Versão 8.3 e suas Novidades?
Semana passada apresentei uma palestra com este tema e fiz um mapa mental com a ferramenta livre Freemind para organizar o conteúdo e dar uma estética melhor.
Coloquei as principais alterações da versão 8.3 e gerei uma imagem. Quem precisar, pode utilizá-la. Sugestões de melhoria também são bem vindas. Com certeza esqueci algum detalhe. As informações são da própria documentação do PostgreSQL.
Coloquei as principais alterações da versão 8.3 e gerei uma imagem. Quem precisar, pode utilizá-la. Sugestões de melhoria também são bem vindas. Com certeza esqueci algum detalhe. As informações são da própria documentação do PostgreSQL.
sexta-feira, 18 de abril de 2008
101 Posts!
Este é o Post número 101 deste blog. Mais que um número, é uma conquista que credito a cada um dos freqüentadores. O que era o "Meu Blog" está cada dia mais se tornando um recurso da
comunidade, que colabora com idéias, textos, críticas, sugestões e com a divulgação deste link.
Dia 04/04/07, depois de uma resolução tomada no meu aniversário, resolvi começar este blog para me forçar a estar sempre estudando Banco de Dados, uma vez que ministrava uma disciplina na área e não era um verdadeiro DBA nos meus empregos (e também para conhecer o funcionamento interno de um blog!). Aos poucos o conteúdo foi se distanciando da disciplina, que não estou mais ensinando, e se tornou uma pequena base de dados sobre o PostgreSQL. Os acessos foram aumentando e sem querer fui conhecendo mais da comunidade do Banco de Dados do elefantinho.
O reconhecimento da comunidade tem sido recompensador, e fez com que este site sempre apareça entre os primeiros blogs de PostgreSQL a serem recuperados no Google, considerando não só o Brasil, mas todo o planeta. O número de links e referências a este site também cresceram bastante e atingiram algumas centenas, o que é raro em blogs tão específicos. É motivo para maior cuidado e para a busca de novas idéias para um constante aprimoramento. Convites para consultorias e treinamentos, que quase nunca posso atender, atestam que este veículo alcança um grande contingente de pessoas. Que bom!
(Pessoalmente, sou um grande usuário, pois consulto as notas que eu mesmo postei no blog quando estou em dúvida.)
Claro que existem dificuldades: cada vez menos tempo, mais responsabilidades no mundo fora da internet e uma maior dificuldade em ter novas idéias. Neste período, vários outros blogs surgiram e desapareceram. Muitos deles pararam no tempo, sem atualização, ou patinam sem conseguir conciliar qualidade e um bom ritmo. Mas o grande estímulo para oferecer algo, para difundir conhecimento tem ajudado nos momentos mais difíceis.
Se você está lendo este post, saiba que este é um espaço para:
- Divulgar seu site/ evento/ notícia de PostgreSQL
- Propor soluções de problemas
- Apresentar problemas e questionamentos
- Pesquisar
- Votar em enquetes (estou pensando em criar mais...)
Tentarei manter este espaço sempre atualizado e em sintonia com a evolução da tecnologia. Quero melhorar seus materiais e conto com a sua colaboração!
Espero poder continuar colaborando para que outras pessoas também se beneficiem por muito tempo ainda.
Agradeço a todos que lêem este post e a todos os freqüentadores do "Meu Blog de PostgreSQL".
comunidade, que colabora com idéias, textos, críticas, sugestões e com a divulgação deste link.
Dia 04/04/07, depois de uma resolução tomada no meu aniversário, resolvi começar este blog para me forçar a estar sempre estudando Banco de Dados, uma vez que ministrava uma disciplina na área e não era um verdadeiro DBA nos meus empregos (e também para conhecer o funcionamento interno de um blog!). Aos poucos o conteúdo foi se distanciando da disciplina, que não estou mais ensinando, e se tornou uma pequena base de dados sobre o PostgreSQL. Os acessos foram aumentando e sem querer fui conhecendo mais da comunidade do Banco de Dados do elefantinho.
O reconhecimento da comunidade tem sido recompensador, e fez com que este site sempre apareça entre os primeiros blogs de PostgreSQL a serem recuperados no Google, considerando não só o Brasil, mas todo o planeta. O número de links e referências a este site também cresceram bastante e atingiram algumas centenas, o que é raro em blogs tão específicos. É motivo para maior cuidado e para a busca de novas idéias para um constante aprimoramento. Convites para consultorias e treinamentos, que quase nunca posso atender, atestam que este veículo alcança um grande contingente de pessoas. Que bom!
(Pessoalmente, sou um grande usuário, pois consulto as notas que eu mesmo postei no blog quando estou em dúvida.)
Claro que existem dificuldades: cada vez menos tempo, mais responsabilidades no mundo fora da internet e uma maior dificuldade em ter novas idéias. Neste período, vários outros blogs surgiram e desapareceram. Muitos deles pararam no tempo, sem atualização, ou patinam sem conseguir conciliar qualidade e um bom ritmo. Mas o grande estímulo para oferecer algo, para difundir conhecimento tem ajudado nos momentos mais difíceis.
Se você está lendo este post, saiba que este é um espaço para:
- Divulgar seu site/ evento/ notícia de PostgreSQL
- Propor soluções de problemas
- Apresentar problemas e questionamentos
- Pesquisar
- Votar em enquetes (estou pensando em criar mais...)
Tentarei manter este espaço sempre atualizado e em sintonia com a evolução da tecnologia. Quero melhorar seus materiais e conto com a sua colaboração!
Espero poder continuar colaborando para que outras pessoas também se beneficiem por muito tempo ainda.
Agradeço a todos que lêem este post e a todos os freqüentadores do "Meu Blog de PostgreSQL".
sexta-feira, 11 de abril de 2008
Uso de UUIDs no PostgreSQL
Implementado no PostgreSQL 8.3, o tipo de dado UUID (Universally Unique Identifier) é pouco conhecido e pouco utilizado. Em post anterior, descrevi esta funcionalidade. Agora demonstrarei como a mesma foi implementada e como pode ser empregada para inserção e alteração de dados, consulta, dentro de funções e com tipos compostos.
Inicialmente, vamos criar uma tabela com campo do tipo UUID e fazer operações de inserção e consulta aos dados. A sintaxe se mantém a mesma para a inserção e alteração de dados. Campos UUID são similares a campos caractere, com a diferença de terem os caracteres "-" opcionais.
Um fato interessante é que o PostgreSQL não apresenta algoritmo padrão para a geração de UUIDs, uma vez que não foi obtido consenso sobre a melhor opção. Cabe ao desenvolvedor criar seu algoritmo e colocá-lo na aplicação ou no banco como função, ou obter um gerador de outras fontes.
--Criação de tabela com campo UUID e inserção de registros
CREATE TABLE uuid_test (identidade UUID);
INSERT INTO uuid_test VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('12345678-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('82345678-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('723456789c0b4ef8bb6d6bb9bd380a11'); --Pode ser feita inserção sem os "-"
SELECT * FROM uuid_test;
--Consultas de UUID, incluindo consulta com conversão para UUID
SELECT '12345678-9c0b-4ef8-bb6d-6bb9bd380a11' = UUID('123456789c0b4ef8bb6d6bb9bd380a11');
SELECT UUID('12345678-9c0b-4ef8-bb6d-6bb9bd380a11'), UUID('123456789c0b4ef8bb6d6bb9bd380a11');
SELECT UUID('12345678-9c0b-4ef8-bb6d-6bb9bd380a11') = UUID('123456789c0b4ef8bb6d6bb9bd380a11');
Funções podem retornar dados do tipo UUID, inclusive em conjuntos de linhas (SETOF). Abaixo apresento duas funções, uma retornando um UUID simples e outra retornando um conjunto de linhas com UUIDs.
--Função SIMPLES que retorna tipo UUID
CREATE FUNCTION ret_uuid_simples () RETURNS uuid AS $$
BEGIN
RETURN '12345678-9c0b-4ef8-bb6d-6bb9bd380a11';
END;
$$ LANGUAGE plpgsql;
SELECT * FROM ret_uuid_simples();
--Função que retorna tipo UUID
CREATE FUNCTION ret_uuid () RETURNS SETOF uuid AS $$
BEGIN
RETURN QUERY SELECT identidade FROM uuid_test LIMIT 1;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM ret_uuid();
O tipo de dado UUID pode ser utilizado na construção de tipos que podem ser reaproveitados.
--Uso de UUID em tipos
CREATE TYPE pessoa_uuid AS (nome varchar(50), ident UUID);
CREATE table ALUNO_UUID (
nomealu pessoa_uuid NOT NULL,
nasc date,
serie int);
INSERT INTO ALUNO_UUID VALUES (('Carla Paulina','82345678-9c0b-4ef8-bb6d-6bb9bd380a11'), '12/12/2001',1);
SELECT * FROM ALUNO_UUID;
SELECT nomealu, (nomealu).nome, (nomealu).ident FROM ALUNO_UUID;
Inicialmente, vamos criar uma tabela com campo do tipo UUID e fazer operações de inserção e consulta aos dados. A sintaxe se mantém a mesma para a inserção e alteração de dados. Campos UUID são similares a campos caractere, com a diferença de terem os caracteres "-" opcionais.
Um fato interessante é que o PostgreSQL não apresenta algoritmo padrão para a geração de UUIDs, uma vez que não foi obtido consenso sobre a melhor opção. Cabe ao desenvolvedor criar seu algoritmo e colocá-lo na aplicação ou no banco como função, ou obter um gerador de outras fontes.
--Criação de tabela com campo UUID e inserção de registros
CREATE TABLE uuid_test (identidade UUID);
INSERT INTO uuid_test VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('12345678-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('82345678-9c0b-4ef8-bb6d-6bb9bd380a11');
INSERT INTO uuid_test VALUES ('723456789c0b4ef8bb6d6bb9bd380a11'); --Pode ser feita inserção sem os "-"
SELECT * FROM uuid_test;
--Consultas de UUID, incluindo consulta com conversão para UUID
SELECT '12345678-9c0b-4ef8-bb6d-6bb9bd380a11' = UUID('123456789c0b4ef8bb6d6bb9bd380a11');
SELECT UUID('12345678-9c0b-4ef8-bb6d-6bb9bd380a11'), UUID('123456789c0b4ef8bb6d6bb9bd380a11');
SELECT UUID('12345678-9c0b-4ef8-bb6d-6bb9bd380a11') = UUID('123456789c0b4ef8bb6d6bb9bd380a11');
Funções podem retornar dados do tipo UUID, inclusive em conjuntos de linhas (SETOF). Abaixo apresento duas funções, uma retornando um UUID simples e outra retornando um conjunto de linhas com UUIDs.
--Função SIMPLES que retorna tipo UUID
CREATE FUNCTION ret_uuid_simples () RETURNS uuid AS $$
BEGIN
RETURN '12345678-9c0b-4ef8-bb6d-6bb9bd380a11';
END;
$$ LANGUAGE plpgsql;
SELECT * FROM ret_uuid_simples();
--Função que retorna tipo UUID
CREATE FUNCTION ret_uuid () RETURNS SETOF uuid AS $$
BEGIN
RETURN QUERY SELECT identidade FROM uuid_test LIMIT 1;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM ret_uuid();
O tipo de dado UUID pode ser utilizado na construção de tipos que podem ser reaproveitados.
--Uso de UUID em tipos
CREATE TYPE pessoa_uuid AS (nome varchar(50), ident UUID);
CREATE table ALUNO_UUID (
nomealu pessoa_uuid NOT NULL,
nasc date,
serie int);
INSERT INTO ALUNO_UUID VALUES (('Carla Paulina','82345678-9c0b-4ef8-bb6d-6bb9bd380a11'), '12/12/2001',1);
SELECT * FROM ALUNO_UUID;
SELECT nomealu, (nomealu).nome, (nomealu).ident FROM ALUNO_UUID;
quinta-feira, 10 de abril de 2008
RETURN QUERY - Novo recurso do PostgreSQL 8.3
A cláusula RETURN QUERY permite que o programador faça uma função que retorne um conjunto de linhas. Foi acrescentada no PostgreSQL 8.3, permitindo maior versatilidade nas implementações.
Para ilustrar esta funcionalidade, vamos criar e popular uma tabela:
CREATE TABLE FUNCTESTE (
cod serial primary key, nome varchar(50), aniversario date default now());
INSERT INTO FUNCTESTE VALUES (1, 'Cláudio', DEFAULT);
...
INSERT INTO FUNCTESTE VALUES (10, 'Ana Cláudia', '01/01/2008');
Vamos retornar um conjunto de linhas utilizando a cláusula RETURN QUERY dentro de uma função. Observe que o código da função abaixo retorna um conjunto de linhas (SETOF) que tem de ser iguais aos campos da tabela FUNCTESTE:
--Retornando consulta de várias linhas com FOR
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF FUNCTESTE AS $$
BEGIN
RETURN QUERY SELECT * FROM FUNCTESTE; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
Para testar a função:
select * from ret_rows();
Em uma função, o RETURN QUERY pode ser utilizado mais de uma vez, mas o retorno feito com RETURN; ´faz a descarga dos valores de uma vez só.
--Retornando a mesma consulta várias vezes com WHILE
CREATE OR REPLACE FUNCTION ret_rows_while () RETURNS SETOF FUNCTESTE AS $$
DECLARE
i INTEGER :=1;
BEGIN
WHILE i <= 5 LOOP
RETURN query SELECT * FROM FUNCTESTE LIMIT 1; --Consulta a ser repetida
i:= i + 1;
END LOOP;
RETURN ;
END;
$$ LANGUAGE plpgsql;
Pode ser retornado um conjunto de elementos de qualquer valor.
--Retornando consulta de vários resultados do tipo inteiro
CREATE OR REPLACE FUNCTION ret_rows_int () RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY SELECT cod FROM FUNCTESTE; --Consulta
RETURN ; -- Retorno de dados
END;
$$ LANGUAGE plpgsql;
select * from ret_rows_int();
A cláusula complementa a função do RETURN NEXT, que era a única possibilidade implementada nas versões anteriores. A sintaxe em geral é mais simples.
Para ilustrar esta funcionalidade, vamos criar e popular uma tabela:
CREATE TABLE FUNCTESTE (
cod serial primary key, nome varchar(50), aniversario date default now());
INSERT INTO FUNCTESTE VALUES (1, 'Cláudio', DEFAULT);
...
INSERT INTO FUNCTESTE VALUES (10, 'Ana Cláudia', '01/01/2008');
Vamos retornar um conjunto de linhas utilizando a cláusula RETURN QUERY dentro de uma função. Observe que o código da função abaixo retorna um conjunto de linhas (SETOF) que tem de ser iguais aos campos da tabela FUNCTESTE:
--Retornando consulta de várias linhas com FOR
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF FUNCTESTE AS $$
BEGIN
RETURN QUERY SELECT * FROM FUNCTESTE; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
Para testar a função:
select * from ret_rows();
Em uma função, o RETURN QUERY pode ser utilizado mais de uma vez, mas o retorno feito com RETURN; ´faz a descarga dos valores de uma vez só.
--Retornando a mesma consulta várias vezes com WHILE
CREATE OR REPLACE FUNCTION ret_rows_while () RETURNS SETOF FUNCTESTE AS $$
DECLARE
i INTEGER :=1;
BEGIN
WHILE i <= 5 LOOP
RETURN query SELECT * FROM FUNCTESTE LIMIT 1; --Consulta a ser repetida
i:= i + 1;
END LOOP;
RETURN ;
END;
$$ LANGUAGE plpgsql;
Pode ser retornado um conjunto de elementos de qualquer valor.
--Retornando consulta de vários resultados do tipo inteiro
CREATE OR REPLACE FUNCTION ret_rows_int () RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY SELECT cod FROM FUNCTESTE; --Consulta
RETURN ; -- Retorno de dados
END;
$$ LANGUAGE plpgsql;
select * from ret_rows_int();
A cláusula complementa a função do RETURN NEXT, que era a única possibilidade implementada nas versões anteriores. A sintaxe em geral é mais simples.
Assinar:
Postagens (Atom)