PostgreSQL Prático/Exercícios
16 – Exercícios
editarExemplo 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.