La motivazione principale per scrivere questo studio è stata un articolo . Ho trovato interessante anche un articolo pubblicato 4 anni fa — .
È interessante notare che lo stesso concetto-"realizzare la logica di business nel DB".
è venuto in mente non solo a me.
Inoltre, vorrei conservare per il futuro, prima di tutto per me stesso, idee interessanti emerse durante l'implementazione. Soprattutto considerando che è stata recentemente presa una decisione strategica di cambiare l'architettura e spostare la logica di business a livello backend. Quindi, tutto ciò che è stato sviluppato presto non sarà più necessario e non interesserà a nessuno.
I metodi descritti non sono una scoperta o un qualcosa di esclusivo know how, tutto secondo tradizione e già realizzato molte volte (io, ad esempio, ho applicato un approccio simile 20 anni fa su Oracle). Ho deciso di raccogliere tutto in un unico posto. Potrebbe essere utile a qualcuno. Come ha dimostrato la pratica, spesso la stessa idea viene in mente a persone diverse in modo indipendente. È anche utile per ricordare.
Certo, però nulla in questo mondo è perfetto, errori e refusi possono purtroppo verificarsi. Critiche e osservazioni sono sempre benvenute e attese. E un ultimo piccolo dettaglio: i dettagli specifici dell'implementazione sono omessi. Dopotutto, tutto viene utilizzato in un progetto funzionante. Quindi, l'articolo è solo un bozza e una descrizione del concetto generale, nient'altro. Spero che i dettagli siano sufficienti per comprendere il quadro generale.
L'idea generale è - «dividi e conquista, nascondi e domina»
L'idea è classica - uno schema separato per le tabelle, uno schema separato per le funzioni memorizzate.
Il cliente non ha accesso diretto ai dati. Tutto ciò che il cliente può fare è chiamare una funzione memorizzata e elaborare la risposta ricevuta.
Ruoli
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Schemi
Schema di archiviazione delle tabelle
Tabelle target che implementano entità tematiche.
CREA SCHEMA store AUTORIZZAZIONE store ;
Schema delle funzioni di sistema
Funzioni di sistema, in particolare per il logging delle modifiche delle tabelle.
CREA SCHEMA sys_functions AUTORIZZAZIONE sys_functions ;
Schema di audit locale
Funzioni e tabelle per l'implementazione dell'audit locale delle esecuzioni delle funzioni memorizzate e delle modifiche delle tabelle target.
CREA SCHEMA loc_audit_functions AUTORIZZAZIONE loc_audit_functions;
Schema delle funzioni di servizio
Funzioni per funzioni di servizio e DML.
CREA SCHEMA service_functions AUTORIZZAZIONE service_functions;
Schema delle funzioni aziendali
Funzioni per le funzioni aziendali finali richiamate dal cliente.
CREA SCHEMA business_functions AUTORIZZAZIONE business_functions;
Diritti di accesso
Ruolo — DBA ha accesso completo a tutti gli schemi (separato dal ruolo DB Owner).
CREA RUOLO dba_role;
GRANT store TO dba_role;
GRANT sys_functions TO dba_role;
GRANT loc_audit_functions TO dba_role;
GRANT service_functions TO dba_role;
GRANT business_functions TO dba_role;
Ruolo — UTENTE ha il privilegio ESEGUI nello schema business_functions.
CREA RUOLO user_role;
Privilegi tra schemi
GRANT
Poiché tutte le funzioni sono create con l'attributo SECURITY DEFINER è necessaria l'istruzione REVOCATE EXECUTE ON ALL FUNCTION… FROM public;
REVOCA ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA sys_functions DA public ;
REVOCA ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA loc_audit_functions DA public ;
REVOCA ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA service_functions DA public ;
REVOCA ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA business_functions DA public ;
CONCEDI USO SULLO SCHEMA sys_functions A dba_role ;
CONCEDI ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA sys_functions A dba_role ;
CONCEDI USO SULLO SCHEMA loc_audit_functions A dba_role ;
CONCEDI ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA loc_audit_functions A dba_role ;
CONCEDI USO SULLO SCHEMA service_functions A dba_role ;
CONCEDI ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA service_functions A dba_role ;
CONCEDI USO SULLO SCHEMA business_functions A dba_role ;
CONCEDI ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA business_functions A dba_role ;
CONCEDI ESECUZIONE SU TUTTE LE FUNZIONI NELLO SCHEMA business_functions A user_role ;
CONCEDI TUTTI I PRIVILEGI SULLO SCHEMA store AL GRUPPO business_functions ;
CONCEDI TUTTI I PRIVILEGI SU TUTTE LE TABELLE NELLO SCHEMA store A business_functions ;
CONCEDI USO SU TUTTE LE SEQUENZE NELLO SCHEMA store A business_functions ;
Quindi lo schema del database è pronto. Possiamo iniziare a popolarlo con i dati.
Tabelle di destinazione
La creazione delle tabelle è banale. Nessuna particolarità, tranne che è stato deciso di rinunciare all'uso di SERIAL e generare sequenze esplicitamente. Inoltre, naturalmente, il massimo utilizzo dell'istruzione
COMMENTA SU ...Commenti per tutti gli oggetti, senza eccezioni.
Audit locale
Per registrare l'esecuzione delle funzioni memorizzate e le modifiche alle tabelle di destinazione, viene utilizzata una tabella di audit locale, che include dettagli sulla connessione del cliente, il nome del modulo chiamato e i valori effettivi dei parametri di input e output in formato JSON.
Funzioni di sistema
Destinate a registrare le modifiche alle tabelle di destinazione. Sono funzioni trigger.
Modello - funzione di sistema
---------------------------------------------------------
-- INSERIRE
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_insert AFTER INSERT ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_insert_log();
---------------------------------------------------------
-- AGGIORNARE
CREATE OR REPLACE FUNCTION sys_functions.table_update_log ()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.column != NEW.column
THEN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column' , NEW.column ) );
END IF ;
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_update AFTER UPDATE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_update_log ();
---------------------------------------------------------
-- CANCELLARE
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_delete AFTER DELETE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_delete_log ();Funzioni di servizio
Destinate all'implementazione di operazioni di servizio e DML sulle tabelle di destinazione.
Modello — funzione di servizio
--INSERISCI
--RESTITUISCI ID DELLA NUOVA RIGA
CREA O SOSTITUISCI LA FUNZIONE service_functions.table_insert ( new_column store.table.column%TYPE )
RESTITUISCE intero AS $$
DICHIARARE
new_id intero ;
INIZIO
-- Genera nuovo id
new_id = nextval('store.table.seq');
-- Inserisci nella tabella
INSERISCI IN store.table
(
id ,
column
)
VALORI
(
new_id ,
new_column
);
RESTITUISCI new_id ;
FINE
$$ LINGUAGGIO plpgsql SICUREZZA DEFINER;
--ELIMINA
--RESTITUISCI NUMERI DELLE RIGHE ELIMINATE
CREA O SOSTITUISCI LA FUNZIONE service_functions.table_delete ( current_id intero )
RESTITUISCE intero AS $$
DICHIARARE
rows_count intero ;
INIZIO
ELIMINA DA store.table DOVE id = current_id;
OTTIENI DIAGNOSTICA rows_count = ROW_COUNT;
RESTITUISCI rows_count ;
FINE
$$ LINGUAGGIO plpgsql SICUREZZA DEFINER;
-- AGGIORNA DETTAGLI
-- RESTITUISCI NUMERI DELLE RIGHE AGGIORNATE
CREA O SOSTITUISCI LA FUNZIONE service_functions.table_update_column
(
current_id intero
,new_column store.table.column%TYPE
)
RESTITUISCE intero AS $$
DICHIARARE
rows_count intero ;
INIZIO
AGGIORNA store.table
IMPOSTA
column = new_column
DOVE id = current_id;
OTTIENI DIAGNOSTICA rows_count = ROW_COUNT;
RESTITUISCI rows_count ;
FINE
$$ LINGUAGGIO plpgsql SICUREZZA DEFINER;Funzioni aziendali
Progettate per funzioni aziendali finali invocabili dal cliente. Restituiscono sempre — JSON. Per intercettare e registrare gli errori di esecuzione, si utilizza il blocco ECCEZIONE.
Template — funzione aziendale
CREA O SOSTITUISCI LA FUNZIONE business_functions.business_function_template(
--Parametri di input
)
RESTITUISCE JSON AS $$
DICHIARARE
------------------------
--per la cattura delle eccezioni
error_message text;
error_json json;
result json;
------------------------
INIZIO
--REGISTRAZIONE
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
'AVVIATO',
json_build_object
(
--Parametri di input
)
);
ESEGUI business_functions.notice('business_function_template');
--INIZIO PARTE AZIENDALE
--FINE PARTE AZIENDALE
-- RISULTATO SUCCESSO
ESEGUI business_functions.notice('result');
ESEGUI business_functions.notice(result);
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
'FINITO',
json_build_object( 'result',result )
);
RESTITUISCI result;
----------------------------------------------------------------------------------------------------------
-- CATTURA ECCEZIONI
ECCEZIONE
QUANDO ALTRO ALLORA
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
'AVVIATO',
json_build_object
(
--Parametri di input
) , TRUE );
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
OTTIENI DIAGNOSTICA IMPILATA error_message = RETURNED_SQLSTATE ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = COLUMN_NAME ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-COLUMN_NAME',
json_build_object('COLUMN_NAME',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = CONSTRAINT_NAME ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = PG_DATATYPE_NAME ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = MESSAGE_TEXT ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = SCHEMA_NAME ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-SCHEMA_NAME',json_build_object('SCHEMA_NAME',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = PG_EXCEPTION_DETAIL ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = PG_EXCEPTION_HINT ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
OTTIENI DIAGNOSTICA IMPILATA error_message = PG_EXCEPTION_CONTEXT ;
ESEGUI loc_audit_functions.make_log
(
'business_function_template',
' ERRORE-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
SOLLEVA AVVISO 'ALLARME: %' , SQLERRM ;
SELEZIONA json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RESTITUISCI error_json ;
FINE
$$ LINGUAGGIO plpgsql SECURITY DEFINER;Risultato
Per descrivere il quadro generale, penso che sia più che sufficiente. Se qualcuno è interessato ai dettagli e ai risultati, scrivete nei commenti, sarò lieto di arricchire l'immagine con ulteriori dettagli.
P.S.
Logging di un errore semplice — tipo di parametro di ingresso
-[ RECORD 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | STARTED
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Type1",
| "title": "CREATO DA addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "descrizione",
| "lowerTr": "100",
| "measurement": "misura1",
| "methodology": "m1",
| "passportUrl": "files",
| "upperTr": "200",
| "weightingFactor": 100.123,
| "actualTrValue": null,
| "upperTrCalcNumber": "120"
| },
| "CardId": 3
| }
-[ RECORD 2 ]-
date_trunc | 2020-08-19 13:15:46
id | 1073
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE
jsonb_pretty | {
| "SQLSTATE": "22P02"
| }
-[ RECORD 3 ]-
date_trunc | 2020-08-19 13:15:46
id | 1074
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE
jsonb_pretty | {
| "SQLERRM": "sintassi di input non valida per tipo numerico: "null""
| }
-[ RECORD 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-RETURNED_SQLSTATE
jsonb_pretty | {
| "RETURNED_SQLSTATE": "22P02"
| }
-[ RECORD 5 ]-
date_trunc | 2020-08-19 13:15:46
id | 1076
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-COLUMN_NAME
jsonb_pretty | {
| "COLUMN_NAME": ""
| }
-[ RECORD 6 ]-
date_trunc | 2020-08-19 13:15:46
id | 1077
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-CONSTRAINT_NAME
jsonb_pretty | {
| "CONSTRAINT_NAME": ""
| }
-[ RECORD 7 ]-
date_trunc | 2020-08-19 13:15:46
id | 1078
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-PG_DATATYPE_NAME
jsonb_pretty | {
| "PG_DATATYPE_NAME": ""
| }
-[ RECORD 8 ]-
date_trunc | 2020-08-19 13:15:46
id | 1079
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-MESSAGE_TEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "sintassi di input non valida per tipo numerico: "null""
| }
-[ RECORD 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-SCHEMA_NAME
jsonb_pretty | {
| "SCHEMA_NAME": ""
| }
-[ RECORD 10 ]-
date_trunc | 2020-08-19 13:15:46
id | 1081
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-PG_EXCEPTION_DETAIL
jsonb_pretty | {
| "PG_EXCEPTION_DETAIL": ""
| }
-[ RECORD 11 ]-
date_trunc | 2020-08-19 13:15:46
id | 1082
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-PG_EXCEPTION_HINT
jsonb_pretty | {
| "PG_EXCEPTION_HINT": ""
| }
-[ RECORD 12 ]-
date_trunc | 2020-08-19 13:15:46
id | 1083
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-PG_EXCEPTION_CONTEXT
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERRORE-MESSAGE_TEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "sintassi di input non valida per tipo numerico: "null""
| }Fonte: habr.com
