Desafio Database do dia - Mostre a elegancia do código!

#PostgreSQL
#SQL

Como desafio para mim, criei um banco de dados de uma instituição financeira, onde tem cliente, agencia, conta, tipo de conta (corrente, poupança), movimentação, tipo movimentação(debito, credito).

create database banco;
--Substantivos objetos
create table cliente(
id SERIAL PRIMARY KEY,
nome VARCHAR(150),
email VARCHAR(100)
);
select * from cliente
insert into cliente (id,nome,email) values(1,'carlos silva', 'carlos_silva@banco.com');
insert into cliente (id,nome,email) values(2,'joao oliveira', 'joao_oliveira@banco.com');
insert into cliente (id,nome,email) values(3,'americo vespucio', 'americo_vespucio@banco.com');
insert into cliente (id,nome,email) values(4,'alvares cabral', 'alvares_cabral@banco.com');


create table tipo_conta(
id SERIAL PRIMARY KEY,
descricao VARCHAR(20)
);

insert into tipo_conta (id,descricao) values(1,'corrente');
insert into tipo_conta (id,descricao) values(2,'poupança');
select * from tipo_conta;

create table agencia(
id SERIAL PRIMARY KEY,
codigo VARCHAR(10)
);

insert into agencia (id,codigo) values(1,'aaa-1');
insert into agencia (id,codigo) values(2,'abb-2');
insert into agencia (id,codigo) values(3,'abc-3');

select * from agencia;



create table tipo_movimentacao(
id SERIAL PRIMARY KEY, 
descricao VARCHAR(20)
);

insert into tipo_movimentacao (id,descricao) values (1,'credito');
insert into tipo_movimentacao (id,descricao) values (2,'debito');

select * from tipo_movimentacao;


create table conta(
id SERIAL PRIMARY KEY,
codigo VARCHAR(10),
saldo DECIMAL(10,2),
id_agencia INTEGER REFERENCES agencia(id),
id_cliente INTEGER REFERENCES cliente(id),
tipo_conta INTEGER REFERENCES tipo_conta(id)
);
select * from conta;
insert into conta (id,codigo,saldo,id_agencia, id_cliente, tipo_conta) values (1,'ccabc-1',100.0 ,1,1,1);
insert into conta (id,codigo,saldo,id_agencia, id_cliente, tipo_conta) values (2,'ccxxx-1',0,1,2,1);
insert into conta (id,codigo,saldo,id_agencia, id_cliente, tipo_conta) values (3,'cczzz-2',400.0,2,3,1);
insert into conta (id,codigo,saldo,id_agencia, id_cliente, tipo_conta) values (4,'ccyyy-3',350.0 ,3,4,2);


create table Movimentacao(
id SERIAL PRIMARY KEY,
id_conta INTEGER REFERENCES conta(id),
id_cliente INTEGER REFERENCES cliente(id),
data_movimento TIMESTAMP,
valor DECIMAL(10,2),
tipo_movimentacao INTEGER REFERENCES tipo_movimentacao(id)
);

Então para ocorrer uma movimentação aconteceria dois inserts, esses inserts geram duas movimentações uma de débito do cliente1 e crédito do cliente2, que são registradas na tabela Movimentação.

Para manter a consistência dos dados criei uma trigger que dispara antes do insert na tabela movimentação. Esta trigger esta associada a uma função que primeiro avalia se no insert do cliente1 o valor é maior que o saldo na conta, se for então raise exception nele, se for falso então ele entra no outro if que é verificar o tipo_movimentacao se for debito subtrair do saldo do cliente que tem este tipo de movimentação, e se for crédito soma ao saldo do cliente.

Pois bem, para realizar o teste os dois inserts tem que esta em uma Transaction (Begin [Commit: Rollback]) pois se uma falhar, a outra não ocorrerá teoricamente . Usando somente o pgadmin3:

!!!!!COMO NÃO FAZER!!!!!!

O cliente1 tem 100 de saldo e o cliente2 tem 0. Então Coloquei o

Begin  
update movimenta(cliente1, 120, debito ...) 
update movimenta(cliente2, 120, credito) 
Commit;

O processo executa mas não é possivel visualizar claramente o que acontece, então executo linha por linha para realmente ver o que acontece, e realmente funciona mas nem tudo são flores. Como dentro da função no primeiro if ocorre um raise exception, logo não alcança o commit e sabe o que acontece? A tabela fica bloqueada shua shua, shua. Você não pode realizar um select na tabela Movimentação. Acabaste de bloquear a tabela do banco, um desastre financeiro vem logo em seguida. Terra arrasada! :(

Meu deus o que fazer agora?

Então você aprende o Rollback, que quanto executado volta ao estado anterior a transação. Como se nada estivesse acontecido. Ufa! Entre mortos e feridos voltamos...

Então fiz uma modificação nesta transaction colocando um exception e dentro um rollback, mas digo logo não é bom. Apesar de realizar o rollback não volta uma mensagem adequada.

!!!!!COMO NÃO FAZER!!!!!!

DO $$
BEGIN
-- DEBITO

 INSERT INTO movimentacao (id_conta, id_cliente, data_movimento, valor, tipo_movimentacao) VALUES (1, 1, CURRENT_TIMESTAMP, 1.0, 2);
 -- CREDITO
 INSERT INTO movimentacao (id_conta, id_cliente, data_movimento, valor, tipo_movimentacao) VALUES (2, 2, CURRENT_TIMESTAMP, 1.0, 1);
 BEGIN
 EXCEPTION
  WHEN OTHERS THEN
         RAISE NOTICE 'TRANSAÇÃO INVÁLIDA';
         ROLLBACK;
END;


COMMIT ; 
END $$;

Então procurando na internet, exatamente o stackoverflow porque o chatgpt não é bala de prata, um brother disse que uma função plpgsql run automaticamente dentro de uma transacion, logo eu não precisaria de um Begin Commit para meu update da tabela movimentação, bastaria dos updates simples. Que legal né pessoal! :p

---- >>>> COMO FAZER << -----

-- DEBITO
 INSERT INTO movimentacao (id_conta, id_cliente, data_movimento, valor, tipo_movimentacao) VALUES (1, 1, CURRENT_TIMESTAMP, 10.0, 2);
 -- CREDITO
 INSERT INTO movimentacao (id_conta, id_cliente, data_movimento, valor, tipo_movimentacao) VALUES (2, 2, CURRENT_TIMESTAMP, 10.0, 1);

Ótimo! Aprendemos mais alguma coisa neste fim de semana.

https://stackoverflow.com/questions/14550241/postgresql-cannot-begin-end-transactions-in-pl-pgsql