AppMaster oferece amplos recursos para trabalhar com o banco de dados. Por exemplo, usando o bloco Search, você pode encontrar os dados necessários, anexar tabelas relacionadas a eles, classificá-los na ordem correta, etc. Porém, em certas situações, isso pode não ser suficiente, e então o bloco SQL Exec vem para o resgate. Ele permite que você execute qualquer consulta de banco de dados usando todo o poder do SQL.
Vamos considerar o funcionamento do bloco usando o exemplo de um banco de dados que contém um catálogo de livros.
Vamos fazer um pequeno trabalho de preparação. É necessário criar um processo de negócio que, na sua forma mais simples, permita o envio de solicitações, bem como o recebimento de seus resultados.
Você também precisará criar um endpoint que permitirá acessar esse processo de negócios.
Na fase inicial, isso deve ser suficiente. Você pode publicar seu aplicativo e prosseguir para os testes. Para isso é muito conveniente utilizar o Swagger, que é criado automaticamente na publicação.
Utilizamos uma consulta simples que deve solicitar todos os livros do banco de dados.
SELECIONE * DE public.book
Observe que a própria tabela (como todas as outras tabelas criadas no editor de banco de dados) possui um prefixo que indica o esquema - público.
Você pode verificar se a solicitação foi concluída com sucesso e o resultado foi recebido.
Mas o problema é que é muito difícil perceber a resposta desta forma. Uma possível solução é modificar um pouco a solicitação e deixar nela apenas os campos necessários – por exemplo, o título do livro e o número de páginas. Além disso, seria razoável estabelecer um limite, não solicitar todos os livros do banco de dados, mas limitar-se a dez.
SELECIONE nome, páginas FROM public.book LIMIT 10
Muito melhor, mas ainda não adequado para uso real. Afinal, em um processo de negócio, você precisa não apenas receber uma solicitação, mas também fazer algo com seu resultado. Para isso, não basta ter o resultado em forma de texto; você precisa transformá-lo em um modelo adequado para uso posterior.
Para fazer isso, vamos voltar ao processo de negócios e refiná-lo um pouco. No bloco final, adicionaremos uma nova variável - um array de modelos de livros.
Você também precisará de um bloco que converterá o JSON recebido como resultado da solicitação em um modelo. Desserializar JSON para modelar.
Vamos repetir o pedido anterior e ter certeza de que o resultado ficou muito mais adequado, tanto para percepção visual quanto para posterior utilização em PB.
Agora, podemos passar para uma lógica mais complexa. Vamos criar um processo de negócios que:
- Recebe o título do livro como entrada.
- Determina a qual categoria (gênero) pertence.
- Como resultado, retorna 3 livros aleatórios da mesma categoria (nesse caso, o livro da solicitação não deve estar entre eles)
Para fazer isso, criaremos um novo processo de negócios que combina a pesquisa usando blocos padrão e o uso de consultas SQL.
O primeiro bloco é encontrar um livro pelo título e também determinar a quais categorias ele pertence. Para fazer isso, utilizamos o bloco de pesquisa com os seguintes parâmetros:
- _Com = Categorias - além do livro em si, o resultado da consulta requer informações da tabela de categorias associadas.
- _Limit = 1 - apenas um livro precisa ser encontrado.
- _Ilike = False - o nome deve corresponder exatamente ao solicitado.
- Nome - índice do título do livro, passado do bloco Iniciar.
Usando o bloco Array Element com índice 0, pegamos o primeiro (e único) livro do resultado.
Um livro pode pertencer a várias categorias diferentes ao mesmo tempo e, neste caso, qualquer uma delas nos servirá. Ele pode ser selecionado aleatoriamente usando o bloco de elementos aleatórios
Depois disso, temos todos os dados necessários, resta apenas criar a própria solicitação, que pode ser assim:
SELECT * FROM public.book WHERE id IN (SELECT rel1_id FROM public.book_categorys_category_books_pivot WHERE book_categorys_category_books_pivot.rel2_id = X) AND id <> Y ORDER BY random() LIMITE 3
Onde X é o id da categoria do livro e Y é o id do próprio livro.
Observe que esta consulta inclui uma subconsulta. Primeiro, na tabela book_categorys_category_books_pivot (é usada para armazenar informações sobre os relacionamentos entre duas tabelas), são encontrados todos os identificadores de livros correspondentes à categoria selecionada. Depois disso, é executada uma consulta que encontra aleatoriamente 3 livros que correspondem ao intervalo especificado, excluindo o ID do livro, cujo título foi originalmente passado para o processo de negócio.
Para um estudo mais detalhado da estrutura do banco de dados do projeto, você pode usar o botão Open DB nas configurações de Deploy Plans.
Isso permitirá que você abra o banco de dados no editor e obtenha acesso direto para visualizar e editar dados. Porém, você deve ter cuidado e levar em consideração o fato de que alterar a própria estrutura de dados no editor impossibilitará a publicação posterior do projeto.
Vamos voltar à criação de um processo de negócios. É necessário concluir a compilação da requisição e para isso converter o livro e o ID da categoria de inteiro para string e também montar a requisição final utilizando o bloco Concat Strings (Multiple).
A última etapa é executar a consulta, converter o resultado em um modelo e enviá-lo para o bloco End como resultado da consulta.
Você pode salvar suas alterações, criar um endpoint, publicar seu projeto e verificar se a solicitação funciona corretamente. Nesse caso, um bloco SQL Exec com uma consulta composta complexa foi usado para substituir muitos outros blocos e simplificar a estrutura do processo de negócios.
O uso do bloco SQL Exec não se limita à recuperação de dados e pode ser usado em uma ampla variedade de cenários. Vamos dar uma olhada em mais algumas opções.
- Contando o número de comentários de um livro com id=X
SELECIONE CONTAGEM(id) FROM public.comment WHERE book_id = X - Cálculo da classificação média de um livro, levando em consideração todas as classificações fornecidas nos comentários:
SELECIONE AVG(taxa) FROM public.comment WHERE book_id = X - Excluir todos os comentários escritos antes de 2023 (pode ser usado, por exemplo, para limpar rapidamente o log).
DELETE * FROM public.comment WHERE criado_em < '2023-01-01'
Concluindo, é importante ressaltar que para melhorar a segurança, foi adicionado um filtro ao bloco SQL Exec para operações perigosas que podem levar a alterações de esquema.
CREATE/ALTER/DROP/TRUNCATE para TABLE|COLUMN|INDEX|CONSTRAINT|SEQUENCE|SCHEMA|DATABASE são desabilitados pelo filtro se o aplicativo estiver hospedado em servidores AppMaster. Ao hospedar no local - por padrão, todas as solicitações estão disponíveis sem restrições.
