PostgreSQL Prático/Exercícios

16 – Exercícios

Exemplo Prático

Vamos criar um banco (clientes_ex), contendo uma tabela (cliente) e um usuário (operador) que terá apenas alguns privilégios de acesso à tabela cliente (INSERT, SELECT, UPDATE) e será obrigado a utilizar senha. Veja que não terá privilégio DELETE. Então adicionar alguns registros e executar consultas dos quatro tipos: INSERT, SELECT, UPDATE e DELETE (este apenas para verificar se realmente ele não tem este privilégio).

1)

CREATE DATABASE clientes_ex WITH ENCODING 'latin1';

-- Para SGBDs que não estejam com esta configuração, pelo menos este banco a usará

Para Exibir a Codificação do lado do Cliente

SHOW CLIENT_ENCODING;

Para Voltar à Codificação Padrão

RESET CLIENT_ENCODING;

Alterando Banco para suportar Datas dd/mm/yyyy

ALTER DATABASE clientes_ex SET DATESTYLE = SQL, DMY;

-- No caso este banco apenas ficará com esta configuração de data

-- Para alteração definitiva para todos os bancos alterar o script "postgresql.conf".

Exibindo o DateStyle Atual

SHOW DATESTYLE;

2)

CREATE TABLE cliente (

codigo INT PRIMARY KEY,

nome VARCHAR(40) NOT NULL,

data_nasc DATE NOT NULL,

bonus NUMERIC(12,2),

observacao TEXT

);

3)

CREATE ROLE operador WITH PASSWORD 'operador9128' VALID UNTIL '26/05/2007';

O usuário somente terá os privilégios até a data determinada.

REVOKE ALL ON cliente FROM operador;

GRANT SELECT,UPDATE,INSERT ON cliente TO operador;

Dica: Caso a tabela tenha campo tipo serial também devemos dar acesso ao objeto sequence gerado:

GRANT SELECT,UPDATE,INSERT ON cliente_codigo_seq TO operador;

-- Considerando que o nome da sequência seja cliente_codigo_seq.

Para permitir ao usuário operador que faça login, use:

ALTER ROLE operador WITH LOGIN;

Obs.: Veja como está aqui o pg_hba.conf:

host all all 127.0.0.1/32 md5

4)

Fazer o login como usuário operador para executar as consultas abaixo:

INSERT INTO cliente (codigo, nome, data_nasc, bonus, observacao) VALUES (1, 'João Pedro', '01/01/1967', 18.35, 'Apenas um texto de teste');

INSERT INTO cliente (codigo, nome, data_nasc, bonus, observacao) VALUES (2, 'Pedro Paulo Rosado', '04/11/1973', 25.35, );

INSERT INTO cliente (codigo, nome, data_nasc, bonus, observacao) VALUES (3, 'José Roberto', '25/06/1938', 12.65, NULL);

Observe que para campos que não exigem NOT NULL, podemos entrar apenas ou NULL.

SELECT * FROM cliente;

SELECT codigo FROM cliente;

SELECT * FROM cliente WHERE codigo = 5;

SELECT * FROM cliente WHERE codigo = 5 AND nome='João de Brito Cunha';

UPDATE cliente SET nome = 'Roberval Taylor' WHERE codigo = 3;

UPDATE cliente SET nome = 'João Almeida' WHERE nome = 'Pedro Paulo';

-- Esta consulta não é eficiente, já que nomes podem se repetir, melhor seria pela chave

Observe ainda, que campos do tipo numérico não têm delimitador, mas os demais tem o delimitador apóstrofo, exceto palavras-chaves e funções como NULL, TRUE, NOW(), etc.

DELETE FROM cliente; -- Esta apaga todos os registros da tabela

DELETE FROM cliente WHERE codigo=1;

DELETE FROM cliente WHERE codigo=2 AND nome = 'Chico Manoel';

Veja as mensaens quando o user operador tenta excluir algum registro:

clientes_ex=> DELETE FROM cliente WHERE codigo=2 AND nome = 'Chico Manoel'

ERROR: permission denied for relation cliente

Ou seja, falta privilégio para excluir e as regras funcionaram.

Um pequeno teste de conexão cia PHP:

<?php

$con=pg_connect('host=127.0.0.1 user=operador password=operador9128 dbname=clientes_ex');

if ($con){

echo "OK";

}else{

echo "NOK";

}

?>

EXERCÍCIO DE UM PEQUENO CONTROLE DE ESTOQUE

Utilizaremos somente minúsculas para os nomes dos objetos (bancos, esquemas, tabelas, campos, etc) e quando composto por duas ou mais palavras separar com sublinhado.

clientes

funcionarios

produtos

vendas

vendas_itens

bonus

comissoes

Por enquanto iremos criar apenas a tabela produtos, mais adiante criaremos as demais tabelas.

Obs.: A tabela de produtos irá guardar também uma informação sobre a posição do produto no local onde é estocado.

Esta posição conterá abscissa (x) e ordenada (y), ou seja a distância horizontal da esquerda e a distância vertical de baixo para cima. Exemplo simplificado da disposição dos produtos:

                                  ProdA    

---

x,y----------------x+10,y --------------x+20,y

                       x                       |                                                       

| |

                                               |                                                       

| |

                                               |Y                                              |Y                                                      

|Y

                                               |                                                       

| |

                                               |                                                       

| |

onde x=10cm e y=5cm

Existem tipos de dados geométricos no PostgreSQL, para pontos, linhas, polígonos, círculos, etc.

Iremos utilizar o ponto (point).

Vamos criar uma versão resumida da tabela Produtos:

CREATE TABLE produtos (codigo int, nome char(40), preco numeric(12,2));

Para excluir uma tabela:

DROP TABLE nometabela;

1 - Instalar o PostgreSQL (de acordo com seu sistema operacional) e realizar as configurações básicas nos arquivos pg_hba.conf e no postgresql.conf. Mude o estilo da data para um compatível com o brasileiro, mude os locales para pt_BR, mude a codificação para LATIN1 e permita conexão TCP/IP para uma máquina de IP 10.1.1.1.

Configure também a autenticação desta máquina para md5;

2 - Criar um banco com nome controle_estoque;

3 – Criar um esquema esq_estoque;

4 – Criar um grupo de usuários grupo_estoque;

5 – Criar dentro do esquema esq_estoque, tabelas, de acordo com as estruturas abaixo com os devidos atributos (campos), tipos de dados, tamanhos e constraints:

clientes (cpf, nome, endereco, cidade, uf, cep, telefone, data_cadastro, data_nascimento);

funcionarios (cpf, nome, endereco, cidade, uf, cep, telefone, data_admissao, data_nascimento);

produtos (codigo_produto, nome, unidade, quantidade, preco_unitario, estoque_minimo, estoque_maximo); -- nome deve ser UNIQUE

vendas (codigo_venda, data_venda, cpf_cliente, cpf_funcionario);

vendas_itens (codigo_item, codigo_venda, codigo_produto, quantidade_item);

bonus (codigo_bonus, cpf_cliente, codigo_venda, bonus);

comissoes (codigo_comissao, cpf_funcionario, codigo_venda, comissao);

6 – Criar as chaves estrangeiras que façam os devidos relacionamentos entre as tabelas;

7 – Remover somente a chave primária da tabela clientes e Adicionar novamente com nome clientes_pk;

8 – Adicionar a constraint NOT NULL no campo preco_unitário de produtos;

9 – Adicionar uma constraint CHECK que exija valores maiores que zero no estoque_minimo do produtos;

10 – Alterar o nome do campo nome da tabela produtos para descricao e o nome da tabela clientes para clientes2. Renomeie novamente para clientes;

11 – Alterar o tipo de dados do campo quantidade de produtos para NUMERIC(12,2);

12 – Criar três usuários user_cli, user_prod e user_adm, todos no grupo grupo_teste, com os seguintes privilégios:

user_cli tem permissão de executar as consultas SELECT, UPDATE E INSERT na tabela clientes;

user_pro tem permissão de executar a consulta SELECT na tabela produtos;

user adm pode fazer o que bem entender em todos os bancos do servidor.

13 – Criar uma view que guarde a soma dos bonus por cliente. Receberá um cliente e retornará sua soma;

14 – Criar uma view que guarde a soma das comissões por funcionário. Receberá um funcionário e retornará sua soma;

15 – Criar uma transação com o bloco:

Venda e Atualização do estoque,

Atualização do bônus do cliente,

Atualização da comissão do vendedor

16 – Cadastrar pelo menos três registros em cada tabela;

17 – Gerar um dump do banco e editar o script para ver seu conteúdo;

18 – Consultar qual o produto mais caro e o mais barato;

19 – Qual o cliente mais antigo;

20 – Atualize o preço de um produto, adicionando R$ 3.85 ao mesmo;

21 – Consulte qual o cliente que não tem bonus e o remova da tabela;

22 – Crie um banco chamado cep_brasil, com uma única tabela cep_tabela cuja estrutura deve ser:

create table cep_full (cep char(8), tipo char(72), logradouro char(70),bairro char(72), municipio char(60), uf char(2));

Importe o arquivo cep_brasil_unique.csv existente no CD ou no site:

http://ribafs.byethost2.com seção downloads – PostgreSQL.

- Então execute \timing,

- Faça uma consulta que retorne apenas o seu CEP

- E anote o tempo gasto.

23 – Agora adicione uma chave primária na tabela. Então faça a mesma consulta anterior e veja a diferença de desempenho por conta do índice adicionado;

22 – Execute o PgAdmin, conecte ao banco controle_estoque para verificar o banco criado, esquemas, grupo de usuários e usuários, esquema, tabelas, fazer algumas consultas, visualizar os dados, a estrutura das tabelas e outras atividades;

23 – Faça o mesmo com o EMS PostgreSQL Manazer;

24 – Conecte ao banco com o DbVisualizer para verificar suas tabelas, esquema e veja o DER (Diagrama Entidade-Relacionamento) e salve como imagem uma cópia do DER.

25 – Criar uma tabela "site" contendo um campo com ip do visitante, do tipo inet.

26 – Criar uma tabela "geometria", contendo campos do tipo ponto, polígono e círculo.