PostgreSQL Prático/Apêndices/Implementação de Banco de Dados com o PostgreSQL

15.2 – Implementação de Banco de Dados com o PostgreSQL - Modelo Físico

Softwares free de Modelagem e Gerenciamento do PostgreSQL

PGAdmin: (http://www.postgresql.org/ftp/pgadmin3/release/)

EMS: (http://www.sqlmanager.net/en/products/postgresql/manager/download)

DBDesigner: (http://fabforce.net/downloads.php)

DbVisualizer: http://www.dbvis.com/products/dbvis/

Em forma de Plug-ins para Eclipse

QuantumDB: (http://quantum.sourceforge.net/)

Azzurri/Clay: (http://www.azzurri.jp/en/software/clay/download.jsp)

SQLExplorer: (http://sourceforge.net/projects/eclipsesql)

Uma grande e boa relação de softwares de projeto, modelagem e gerenciamento para o PostgreSQL, free e comercial pode ser encontrada em no site oficial so PostgreSQL Brasil:

https://wiki.postgresql.org.br/wiki/Ferramentas.

Suporte à Acentuação na Criação de Bancos no PostgreSQL

A codificação default do PG 7.X é a SQL_ASCII

A do PG 8.X é a UNICODE

Ambas tem suporte a acentuação, mas geram problemas no backup/importação.

Codificação

Para um suporte estável à acentuação em português do Brasil uma boa opção é criar o banco passando a codificação (Encoding) LATIN1

ENCODING = 'LATIN1'

Criação do Banco

Criaremos o banco do projeto de testes com o PGAdmin, contendo esquemas, tabelas, views, funções do tipo SQL e Pl/PgSQL, usuários, privilégios, consultas, etc. para ilustrar nosso projeto e servir de base para os testes (em seguida).

Analisar o modelo sugerido e detalhar o banco, tipos de dados de cada campo, tamanho, esquemas do banco, usuários e senhas, privilégios de cada um (cuidados com a segurança), etc.

Ativar o Suporte às Funções Pl/Pgsql (Stored Procedures)

Após ter criado o banco, podemos ativar o suporte a plpgsql.

Ativar suporte a Pl/PgSQL requer dois passos:

- instalar a biblioteca Pl/PgSQL, que é do tipo contrib

- definir a linguagem (como sugerido abaixo)

Ativando na console do PG depois de conectar ao banco onde ficará o suporte:

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'

- Ativando como superusuário na console (fora dos bancos)

su - postgres

$ createlang plpgsql -U nomesuperuser nomebanco

Ou simplesmente:

$ createlang plpgsql nomebanco

JDBC

Alguns programas em Java o utilizam, como o plugin QuantumDB.

O JDBC para o PostgreSQL encontra-se em:

http://jdbc.postgresql.org/download.html#jars

Veja que para selecionar o arquivo .jar correto, precisamos cruzar a versão do PostgreSQL à esquerda com a versão do JDBC desejado.

Exemplo: Para uso como cliente em sua máquina pelo Quantum DB (no Eclipse) e com PostgreSQL 8.1 baixar o arquivo: 8.1-405 JDBC 3

Esquemas

Definir os esquemas do banco.

Quando o cliente precisa de muitas tabelas, organizadas em várias áreas a saída imediata é a criação de vários bancos de dados. Mas quando da implementação do aplicativo que irá utilizar estes bancos os desenvolvedores se depararão com a dificuldade de comunicação e acesso entre os bancos, já que com uma única conexão terão acesso a todos os objetos do banco. É muito útil para estes casos criar um único banco e neste criar vários esquemas, organizados por áreas: pessoal, administracao, contabilidade, engenharia, etc.

Mas e quando uma destas áreas tem outras sub-áreas, como por exemplo a engenharia, que tem reservatórios, obras, custos e cada um destes tem diversas tabelas. O esquema engenharia ficará muito desorganizado. Em termos de organização o ideal seria criar um banco para cada área, engenharia, contabilidade, administração, etc. E para engenharia, por exemplo, criar esquemas para cada subarea, custos, obras, etc. Mas não o ideal em termos de comunicação e acesso entre todos os bancos.

Criar Esquema

Num gerenciador do PG entra-se no banco e nesse cria-se o esquema.

Ou

CREATE SCHEMA nomeesquema;

Acessando Objetos de Esquemas

Para acessar um esquema devemos passar seu caminho:

nomeesquema.nometabela

Ou

nomebanco. nomeesquema.nometabela

Criando Tabela em Esquema

CREATE TABLE nomeesquema.nometabela (

...

)

Criando Esquema e tornando um Usuário dono

CREATE SCHEMA nomeesquema AUTHORIZATION nomeusuario;

Removendo privilégios de acesso a usuário em esquema

REVOKE CREATE ON SCHEMA public FROM PUBLIC

Com isso estamos tirando o privilégio de todos os usuários acessarem o esquema public.

Acesso aos Esquemas

Quando se cria um banco no PostgreSQL, por default, ele cria um esquema público (public) no mesmo e é neste esquema que são criados todos os objetos quando não especificamos o esquema. A este esquema public todos os usuários do banco têm livre acesso, mas aos demais existe a necessidade de se dar permissão para que os mesmos acessem.

Tabelas

O PostgreSQL permite adicionar privilégios por objeto do banco: tabela, esquema, banco, etc. Em termos de segurança é importante, em geral, que os privilégios sejam adicionados ao usuário por tabela, cada tabela tendo um dono e cada dono tendo seus específicos privilégios.

Dica de Desempenho: Na criação das tabelas alertar para a criação de índices para os campos envolvidos na cláusula WHERE. Isso tornará essas consultas mais rápidas.

Views

Juntamente com as funções armazenadas (stored procedures) as views são boas alternativas para tornar o código mais simples e o aplicativo mais eficientes, já que parte do processamento feito pelo código agora já está pronto e debugado no banco, o que torna o código mais rápido e eficiente. O uso de views e de funções armazenadas em bancos é semelhante ao uso de funções e classes no código.

Dica: para uso de views, sintaxe de funções internas e uso de cláusulas SQL no PostgreSQL, tutoriais de EMS e vários outros sobre PostgreSQL, além de PHP, JavaScript, etc, confira o site abaixo: http://ribafs..net ou http://ribafs.tk

Criação do Banco Tutorial sobre PGAdmin para criar o banco funcionarios.

Bem, de posse do script .sql acima, praticamente o que teremos de fazer é criar um banco vazio no PGAdmin.

Abrir o PGAdmin

Caso não tenha salvado a senha ele pedirá sempre que iniciar

Ao abrir clique com o botão direito à direita em Databases e em New Database.

- No diálogo New Database entre com o Name do banco (funcionarios), o Owner (postgres).

Idealmente mudar o nome do superusuario default para um nome mais seguro, assim como a senha (mínimo de 8 caracteres, misturando letras e algarismos e idealmente com símbolos).

Também altere Encoding (codificação) para LATIN1.

- Então selecione o banco funcionarios e clique no botão SQL acima.

- Clique no botão open file para indicar o nosso script sql gerado anteriormente.

Clique na setinha verde (Execute query)

Eventuais Correções:

Caso receba mensagens de erro sobre tipo UNSIGNED, verifique o script e remova todas as ocorrências de UNSIGNED e execute novamente. Como o DBDesigner foi projetado para o MySQL um outro erro que pode ocorrer é com a string AUTO_INCREMENT, que também deve ser removida e novamente devemos executar o script. Feitas estas correções o script executa normalmente e cria o nosso banco funcionarios.

Então verifique à esquerda que o banco já contém as 3 tabelas de acordo com o script.

Engenharia Reversa

Um ótimo software para conexão ao PostgreSQL, engenharia reversa (gera diagramas ER dos bancos existentes) e exporta os diagramas em forma de imagens: DbVisualizer.