A semana foi corrida, e o final de semana foi totalmente utilizado para descanso e lazer. Mas a vontade de escrever é maior, e cá estou novamente pronto para mais um post. A idéia hoje era falar sobre scripts SQL, então imaginei algo diferente, e pensei em mostrar como fazer um script interativo no PostgreSQL 8.3, associando o seu uso a um cenário hipotético. Vejamos…
Em um universo ideal, é muito comum existir um banco de dados de desenvolvimento, um de testes, um de homologação e um de produção. Neste cenário, imagine que seu Analista de Testes precise testar uma nova funcionalidade do sistema relacionada ao módulo de compras, e que para isso constantemente você tenha que aplicar a carga de testes desse módulo no banco de dados de teste, não é de se estranhar que você possua uma carga pronta (é o mínimo que você deveria ter).
Veja a seguir o script simplificado que representa esse cenário:
CREATE TABLE tb_cliente (
nr_documento numeric(14,0) NOT NULL,
nm_cliente character varying(60)
);
ALTER TABLE blog.tb_cliente OWNER TO postgres;
CREATE TABLE tb_compra (
nr_nota_fiscal numeric(15,0) NOT NULL,
dt_compra date,
nr_documento_cliente numeric(14,0)
);
ALTER TABLE blog.tb_compra OWNER TO postgres;
CREATE TABLE tb_item_compra (
nr_nota_fiscal numeric(15,0) NOT NULL,
nr_referencia integer NOT NULL,
qt_item smallint DEFAULT 1
);
ALTER TABLE blog.tb_item_compra OWNER TO postgres;
CREATE TABLE tb_produto (
nr_referencia integer NOT NULL,
ds_produto character varying(60) NOT NULL,
vl_venda numeric(15,2)
);
ALTER TABLE blog.tb_produto OWNER TO postgres;
CREATE SEQUENCE tb_produto_nr_referencia_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE blog.tb_produto_nr_referencia_seq OWNER TO postgres;
ALTER SEQUENCE tb_produto_nr_referencia_seq OWNED BY tb_produto.nr_referencia
ALTER TABLE tb_produto ALTER COLUMN nr_referencia SET DEFAULT nextval('tb_produto_nr_referencia_seq'::regclass);
ALTER TABLE ONLY tb_compra
ADD CONSTRAINT pk_compra PRIMARY KEY (nr_nota_fiscal);
ALTER TABLE ONLY tb_item_compra
ADD CONSTRAINT pk_item_compra PRIMARY KEY (nr_nota_fiscal, nr_referencia);
ALTER TABLE ONLY tb_cliente
ADD CONSTRAINT pk_pessoa PRIMARY KEY (nr_documento);
ALTER TABLE ONLY tb_produt
ADD CONSTRAINT pk_produto PRIMARY KEY (nr_referencia);
ALTER TABLE ONLY tb_compra
ADD CONSTRAINT fk_cliente_compra_01 FOREIGN KEY (nr_documento_cliente) REFERENCES tb_cliente(nr_documento);
ALTER TABLE ONLY tb_item_compra
ADD CONSTRAINT fk_compra_item_compra_01 FOREIGN KEY (nr_nota_fiscal) REFERENCES tb_compra(nr_nota_fiscal);
ALTER TABLE ONLY tb_item_compr
ADD CONSTRAINT fk_produto_item_compra_01 FOREIGN KEY (nr_referencia) REFERENCES tb_produto(nr_referencia);
Esse é o conteúdo simplificado da entidade cliente no banco de dados de teste:
nr_documento | nm_cliente
--------------+---------------------------
74727756632 | Marta Antonia
56548986527 | Antonia Josefina
47040567970 | Adamantina Pereira
24348435149 | Carlos Augusto
80987468692 | José Silveira
56096344407 | Maria Eleontina de Castro
79056669606 | José da Silva Sauro
31887461081 | Ribamar de Castr
45792555043 | Manoel Bandeira
Esse é o conteudo simplificado da entidade produto no banco de dados de teste:
nr_referencia | ds_produto | vl_venda
---------------+-------------------+----------
1 | Sapato Velho | 15.00
2 | Sapato Novo | 25.00
3 | Blusa Velha | 35.00
4 | Blusa Nova | 45.00
5 | Calça Jeans Velha | 29.50
6 | Calça Jeans Nova | 49.50
7 | Peruca Masculina | 439.9
8 | Camisola | 19.50
E este é o Script de Carga de Teste para as entidades tb_compra e tb_item_compra:
postgres@banco $ cat ~/scripts/carga_compra_teste.sql
/***************************************************
*
* Script de geração de carga
* Modulo de Compras - Banco de dados de Teste
*
* (c) 2007 Dickson Guedes <guediz at gmail dot com>
*
****************************************************/
INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente
VALUES (1234567890, now(), 56473847366);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1234567890, 6, 2);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1234567890, 4, 1);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1234567890, 3, 15);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item
VALUES (1234567890, 2, 2);
INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente
VALUES (1122334455, now(), 56096344407);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1122334455, 6, 2);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1122334455, 4, 1);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item)
VALUES (1122334455, 3, 15);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item
VALUES (1122334455, 2, 2);
E para executá-lo e utilizo o utilitário psql:
postgresql@banco $ psql bdtesteWelcome to psql 8.4devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute quer
\q to quit
postgres=# \i ~/scripts/carga_compra_teste.sql
E se eu quisesse tornar esse script interativo?
O utilitário psql além de aceitar instruções SQL, possui um conjunto de instrucoes proprias que nos permitem extenter suas funcionalidades. Utilizaremos duas delas: \echo, que permite enviar um texto para a console do psql e \prompt (a partir da versão 8.3 do PostgreSQL) que permite receber um dado da entrada e salvar um uma variável.
Nosso script acima poderia ficar assim:
/***************************************************
*
* Script de geração de carga
* Modulo de Compras - Banco de dados de Teste
*
* (c) 2007 Dickson Guedes <guediz at gmail dot com>
*
****************************************************/
\echo """"""""""""""""""""""""""""""""""""""""""
\echo " Gerando carga para o ambiente: TESTE "
\echo " "
\echo " Cenário: Prestacoes de compra a prazo "
\echo " "
\echo """"""""""""""""""""""""""""""""""""""""""
\echo
\echo Continuar..: Enter
\echo Cancelar...: Ctrl+C
\prompt continua
\unset continuar
\echo
SELECT nr_documento || ' -> ' || nm_cliente AS "Lista de Clientes" FROM tb_cliente;
\echo
\prompt 'Informe um dos números de CPFs acima.: ' v_nr_document
\prompt 'Informe o número da nota fiscal......: ' v_nr_nota_fiscal
\echo '* Inserindo compra para o cliente:' :v_nr_documento 'com número de nota fiscal:' :v_nr_nota_fiscal
INSERT INTO tb_compra (nr_nota_fiscal, dt_compra, nr_documento_cliente) VALUE
(:v_nr_nota_fiscal, now(), :v_nr_documento);
INSERT INTO tb_item_compra (nr_nota_fiscal, nr_referencia, qt_item) VALUES
(:v_nr_nota_fiscal, 2, ((RANDOM()*5)+1)::int2),
(:v_nr_nota_fiscal, 4, ((RANDOM()*5)+1)::int2),
(:v_nr_nota_fiscal, 7, ((RANDOM()*5)+1)::int2),
(:v_nr_nota_fiscal, 5, ((RANDOM()*5)+1)::int2);
\echo "* Script finalizado!"
E para executá-lo e utilizo também o utilitário psql:
postgresql@banco $ psql bdtesteWelcome to psql 8.4devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute quer
\q to quit
postgres=# \i ~/scripts/carga_compra_teste.sql
DICA 1: quando usar o \prompt você pode usar 2 (dois) argumentos: um texto explicativo seguido do nome da variável que você deseja armazenar o valor recebido.
DICA 2: os valores das variáveis são recuperados utilizando-se o dois pontos (”:”) precedendo o nome da variável que se deseja ler o valor, como por exemplo \echo ‘Valor da variavel é’ :variavel_qualquer.
DESAFIO 1: Tente alterar o script acima para que os produtos também sejam aleatórios, ou seja, cada vez que o script for executado ele gere uma carga diferente.
DESAFIO 2: Como você trataria o problema de um erro de digitação ou problema de violação de chaves? Você poderia utilizar transações?
Uma das atividades mais comuns de um DBA é a geração do arquivo de dump de suas bases de dados, que nada mais é do que um arquivo contendo a imagem do seu banco de dados em um determinado instante do tempo. Como existem ferramentas de linha de comando que geram esses arquivos, é bastante comum utilizá-las em scripts autônomos, agendados diariamente no período noturno. Mas nem tudo são flores e podemos ser supreendidos ao saber que justo aquele dump que precisávamos não foi executado com sucesso. E agora?