AppMaster offre ampie funzionalità per lavorare con il database. Ad esempio, utilizzando il blocco Cerca, puoi trovare i dati necessari, allegarvi tabelle correlate, ordinarli nell'ordine corretto, ecc. Tuttavia, in alcune situazioni, questo potrebbe non essere sufficiente, e quindi arriva il blocco SQL Exec il salvataggio. Ti consente di eseguire qualsiasi query sul database utilizzando tutta la potenza di SQL.
Consideriamo il funzionamento del blocco utilizzando l'esempio di un database che contiene un catalogo di libri.
Facciamo un piccolo lavoro di preparazione. È necessario creare un processo aziendale che, nella sua forma più semplice, consenta di inviare richieste, nonché di riceverne i risultati.
Dovrai inoltre creare un endpoint che ti consentirà di accedere a questo processo aziendale.
Nella fase iniziale, questo dovrebbe essere sufficiente. Puoi pubblicare la tua applicazione e passare ai test. Per questo è molto comodo utilizzare Swagger, che viene creato automaticamente durante la pubblicazione.
Usiamo una semplice query che dovrebbe richiedere tutti i libri dal database.
SELEZIONA * DA public.book
Tieni presente che la tabella stessa (come tutte le altre tabelle create nell'editor del database) ha un prefisso che indica lo schema: pubblico.
Puoi verificare che la richiesta sia stata completata con successo e che il risultato sia stato ricevuto.
Ma il problema è che è molto difficile percepire la risposta in questa forma. Una possibile soluzione è modificare leggermente la richiesta e lasciare solo i campi necessari, ad esempio il titolo del libro e il numero di pagine. Inoltre, sarebbe ragionevole fissare un limite, richiedere non tutti i libri dal database, ma limitarsi a dieci.
SELEZIONA nome, pagine DA public.book LIMIT 10
Molto meglio, ma ancora non adatto all'uso reale. Dopotutto, in un processo aziendale non è necessario solo ricevere una richiesta ma anche fare qualcosa con il suo risultato. Per questo non è sufficiente avere il risultato sotto forma di testo; è necessario trasformarlo in un modello adatto per un ulteriore utilizzo.
Per fare ciò, torniamo al processo aziendale e perfezioniamolo un po'. Nel blocco finale aggiungeremo una nuova variabile: un array di modelli di libri.
Avrai anche bisogno di un blocco che converta il JSON ricevuto a seguito della richiesta in un modello. Deserializza JSON nel modello.
Ripetiamo la richiesta precedente e assicuriamoci che il risultato sia diventato molto più adatto, sia per la percezione visiva che per un ulteriore utilizzo in BP.
Ora possiamo passare a una logica più complessa. Creiamo un processo aziendale che:
- Riceve il titolo del libro come input.
- Determina a quale categoria (genere) appartiene.
- Restituisce come risultato 3 libri a caso della stessa categoria (in questo caso, il libro della richiesta non dovrebbe essere tra questi)
Per fare ciò, creeremo un nuovo processo aziendale che combini la ricerca tramite blocchi standard e l'utilizzo di query SQL.
Il primo blocco consiste nel trovare un libro in base al titolo e anche nel determinare a quali categorie appartiene. Per fare ciò, utilizziamo il blocco di ricerca con i seguenti parametri:
- _With = Categorie: oltre al libro stesso, il risultato della query richiede informazioni dalla tabella delle categorie associata.
- _Limite = 1: è necessario trovare un solo libro.
- _Ilike = False - il nome deve corrispondere esattamente a quello richiesto.
- Nome - indice del titolo del libro, passato dal blocco Start.
Utilizzando il blocco Array Element con indice 0, prendiamo il primo (e unico) libro dal risultato.
Un libro può appartenere a diverse categorie contemporaneamente e in questo caso ognuna di esse sarà adatta a noi. Può essere selezionato in modo casuale utilizzando il blocco Elemento casuale
Dopodiché disponiamo di tutti i dati necessari e non resta che creare la richiesta stessa, che potrebbe assomigliare a questa:
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
Dove X è l'id della categoria del libro e Y è l'id del libro stesso.
Tieni presente che questa query include una sottoquery. Innanzitutto, dalla tabella book_categorys_category_books_pivot (viene utilizzata per memorizzare informazioni sulle relazioni tra due tabelle), vengono trovati tutti gli identificatori del libro corrispondenti alla categoria selezionata. Successivamente, viene eseguita una query che trova 3 libri casuali che corrispondono all'intervallo specificato, escluso l'ID del libro, il cui titolo è stato originariamente passato al processo aziendale.
Per uno studio più dettagliato della struttura del database del progetto, è possibile utilizzare il pulsante Apri DB nelle impostazioni dei piani di distribuzione.
Ti consentirà di aprire il database nell'editor e ottenere l'accesso diretto alla visualizzazione e alla modifica dei dati. Tuttavia, dovresti fare attenzione e tenere conto del fatto che la modifica della struttura dei dati stessa nell'editor renderà impossibile pubblicare ulteriormente il progetto.
Torniamo alla creazione di un processo aziendale. È necessario completare la compilazione della richiesta e per fare ciò convertire l'ID del libro e della categoria da intero a stringa ed inoltre assemblare la richiesta finale utilizzando il blocco Concat Strings (Multiple).
L'ultimo passaggio consiste nell'eseguire la query, convertire il risultato in un modello e inviarlo al blocco End come risultato della query.
Puoi salvare le modifiche, creare un endpoint, pubblicare il tuo progetto e verificare che la richiesta funzioni correttamente. In questo caso è stato utilizzato un blocco SQL Exec con una query composta complessa per sostituire molti altri blocchi e semplificare la struttura del processo aziendale.
L'utilizzo del blocco SQL Exec non si limita al recupero dei dati e può essere utilizzato in un'ampia varietà di scenari. Diamo uno sguardo più da vicino ad alcune altre opzioni.
- Conteggio del numero di commenti per un libro con id=X
SELECT COUNT(id) FROM public.comment WHERE book_id = X - Calcolo della valutazione media di un libro, tenendo conto di tutte le valutazioni fornite nei commenti:
SELEZIONA AVG(tariffa) DA public.comment DOVE book_id = X - Cancellazione di tutti i commenti scritti prima del 2023 (può essere utilizzato, ad esempio, per cancellare velocemente il log).
ELIMINA * DA public.comment DOVE creato_at <'2023-01-01'
In conclusione, vale la pena notare che per migliorare la sicurezza è stato aggiunto un filtro al blocco SQL Exec per le operazioni pericolose che possono portare a modifiche dello schema.
CREATE/ALTER/DROP/TRUNCATE per TABLE|COLUMN|INDEX|CONSTRAINT|SEQUENCE|SCHEMA|DATABASE sono disabilitati dal filtro se l'applicazione è ospitata sui server AppMaster. Quando si ospita in sede, per impostazione predefinita tutte le richieste sono disponibili senza restrizioni.
