Az etüd megírására ösztönző motiváció egy cikk volt . Érdekesnek tűnt egy négy évvel ezelőtt megjelent cikk is — .
Érdekesnek tűnt, hogy ugyanaz a gondolat—"megvalósítani az üzleti logikát az adatbázisban".
nemcsak nekem jutott eszembe.
Jövőbeli szempontból szerettem volna megőrizni azokat az érdekes megoldásokat, amelyek a megvalósítás folyamán felmerültek. Különösen figyelembe véve, hogy viszonylag nemrég stratégiai döntést hoztak az architektúra megváltoztatásáról és az üzleti logika backend szintre történő áthelyezéséről. Tehát minden, ami eddig kidolgozásra került, hamarosan senkinek sem lesz szüksége, és senkit sem fog érdekelni.
A leírt módszerek nem jelentenek valami felfedezést vagy kivételes know how, minden klasszikus, és már többször megvalósították (például egy hasonló megközelítést 20 évvel ezelőtt alkalmaztam az Oracle-on). Egyszerűen összegyűjtöttem mindent egy helyre. Hátha hasznos lesz valakinek. Mint ahogy a gyakorlat mutatja — elég gyakran ugyanaz az ötlet függetlenül jut eszébe különböző embereknek. És magamnak is emlékül hasznos.
Természetesen, semmi sem tökéletes a világon, és sajnos hibák és elgépelések lehetségesek. A kritikát és a megjegyzéseket szívesen fogadjuk és várjuk. Még egy apró részlet — a konkrét megvalósítás részletei el lettek hagyva. Végül is mindez még egy valós projektben működik. Tehát a cikk egy etüd és az általános koncepció leírása, nem több. Remélem, hogy az általános kép megértéséhez elegendő a részletek.
Az általános ötlet — „oszd meg és uralkodj, rejtsd el és birtokolj”
A klasszikus ötlet — külön séma a táblák számára, külön séma a tárolt függvények számára.
Az ügyfél nem fér hozzá az adatokhoz közvetlenül. Minden, amit az ügyfél végrehajthat — csak tárolt függvények hívása és a kapott válasz feldolgozása.
Szerepek
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Sémák
A táblák tárolására szolgáló séma
Célzott táblák, amelyek a tárgyak entitásait valósítják meg.
CREATE SCHEMA store AUTHORIZATION store;
A rendszerszolgáltatások sémája
Rendszerszolgáltatások, különösen a táblák változásainak naplózására.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;
Helyi audit séma
Funkciók és táblázatok a tárolt eljárások végrehajtásának és a cél táblázatok módosításának helyi auditálására.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Szolgáltatási funkciók sémája
Funkciók szolgáltatási és DML funkciókhoz.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Üzleti funkciók sémája
Funkciók végső üzleti funkciókhoz, amelyeket az ügyfél hív meg.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Hozzáférési jogok
Szerep — DBA teljes hozzáféréssel rendelkezik az összes sémához (elválasztva a DB Owner szereptől).
CREATE ROLE 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;
Szerep — USER jogosultsággal rendelkezik EXECUTE a sémában business_functions.
CREATE ROLE user_role;
Jogosultságok a sémák között
GRANT
Mivel minden funkciót a következő attribúttal hoznak létre SECURITY DEFINER szükséges utasítás REVOKE EXECUTE ON ALL FUNCTION… FROM public;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA sys_functions FROM public;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA loc_audit_functions FROM public;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA service_functions FROM public;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA business_functions FROM public;
GRANT USAGE ON SCHEMA sys_functions TO dba_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sys_functions TO dba_role;
GRANT USAGE ON SCHEMA loc_audit_functions TO dba_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA loc_audit_functions TO dba_role;
GRANT USAGE ON SCHEMA service_functions TO dba_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_functions TO dba_role;
GRANT USAGE ON SCHEMA business_functions TO dba_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO dba_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO user_role;
GRANT ALL PRIVILEGES ON SCHEMA store TO GROUP business_functions;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA store TO business_functions;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA store TO business_functions;
Tehát az adatbázis séma készen áll. Lehet kezdeni az adatok feltöltését.
Cél táblázatok
A táblázatok létrehozása triviális. Nincs különleges követelmény, kivéve, hogy el lett döntve a következő használatának mellőzése SERIAL és a sorozatok kifejezett generálása. Továbbá, természetesen a COMMENT ON ... utasítás maximális használata.
COMMENT ON ...Megjegyzések a adattal, tehát csak a TXE jelző nem lesz elegendő. objektumokhoz, kivétel nélkül.
Helyi audit
A tárolt eljárások végrehajtásának és a cél táblázatok módosításának naplózására helyi audit táblázatot használnak, amely tartalmazza az ügyfélkapcsolat részleteit, a hívott modul címkéjét, valamint a bemeneti és kimeneti paraméterek tényleges értékeit JSON formátumban.
Rendszerfunkciók
A cél táblázatok módosításainak naplózására szolgálnak. Ezek trigger funkciók.
Sablon – rendszerfunkció
---------------------------------------------------------
-- BESZÚRÁS
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();
---------------------------------------------------------
-- FRISSÍTÉS
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 ();
---------------------------------------------------------
-- TÖRLÉS
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 ();Szerviz funkciók
Célzott táblákon végzett szerviz és DML műveletek végrehajtására szolgálnak.
Sablon — szerviz funkció
--BESZÚRÁS
--ÚJ SOR ID-jének VISSZAADÁSA
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Új id generálása
new_id = nextval('store.table.seq');
-- Beszúrás a táblába
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--TÖRLÉS
--TÖRLT SOROK SZÁMÁNAK VISSZAADÁSA
CREATE OR REPLACE FUNCTION service_functions.table_delete ( current_id integer )
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
DELETE FROM store.table WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- FRISSÍTÉS RÉSZLETEI
-- FRISSÍTETT SOROK SZÁMÁNAK VISSZAADÁSA
CREATE OR REPLACE FUNCTION service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
UPDATE store.table
SET
column = new_column
WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Üzleti funkciók
Végfelhasználói üzleti funkciók végrehajtására szolgál, amelyet a kliens hív meg. Mindig visszaadnak — JSON. A végrehajtási hibák elfogására és naplózására az EXCEPTION blokkot használják. KIVÉTEL.
Sablon — üzleti funkció
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Input parameters
)
RETURNS JSON AS $$
DECLARE
------------------------
--for exception catching
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
--LOGGING
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'KEZDŐDÖTT',
json_build_object
(
--BEMENETI Paraméterek
)
);
PERFORM business_functions.notice('business_function_template');
--ÜZLETI RÉSZ KEZDETE
--ÜZLETI RÉSZ VÉGE
-- SIKERES EREDMÉNY
PERFORM business_functions.notice('result');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'BESZÉG ELFOGADVA',
json_build_object( 'result',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- KIVÉTELKEZELÉS
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'KEZDŐDÖTT',
json_build_object
(
--BEMENETI Paraméterek
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-VISSZAADOTT_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = COLUMN_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-OLOK_NÉV',
json_build_object('COLUMN_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-KORLÁT_NÉV',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_DATATYPE_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-PG_ADAT_TÍPUS_NÉV',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-ÜZENET_TEX',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(s
'business_function_template',
' HIBA-SCHÉMA_NÉV',json_build_object('SCHEMA_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_DETAIL ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-PG_KIVÉTEL_RÉSZLET',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_HINT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-PG_KIVÉTEL_AJÁNLÁS',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_CONTEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' HIBA-PG_KIVÉTEL_KONTEXTUS',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
RAISE WARNING 'FIGYELMEZTETÉS: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Result
A teljes kép leírásához szerintem elegendő ennyi. Ha valakit érdekelnek a részletek és az eredmények, írjon kommentet, szívesen kiegészítem a képet további részletekkel.
P.S.
Egyszerű hiba naplózása - a bemeneti paraméter típusa
-[ RECORD 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ELKEZDVE
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Típus1",
| "title": "LET LÉTREHOZVA addKD által",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "IGAZ",
| "description": "leírás",
| "lowerTr": "100",
| "measurement": "mérés1",
| "methodology": "m1",
| "passportUrl": "fájlok",
| "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 | HIBA
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 | HIBA
jsonb_pretty | {
| "SQLERRM": "érvénytelen bemeneti szintaxis a numerikus típushoz: "null""
| }
-[ RECORD 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | HIBA-VISSZATÉRT_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 | HIBA-OLOK_NÉV
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 | HIBA-KONSTRÁLÁS_NÉV
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 | HIBA-PG_ADAT_TÍPUS_NÉV
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 | HIBA-ÜZENET_SZÖVEG
jsonb_pretty | {
| "MESSAGE_TEXT": "érvénytelen bemeneti szintaxis a numerikus típushoz: "null""
| }
-[ RECORD 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | HIBA-SCHÉMA_NÉV
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 | HIBA-PG_KIVÉTEL_RÉSZLET
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 | HIBA-PG_KIVÉTEL_TIPP
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 | HIBA-PG_KIVÉTEL_KONTEXTUS
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | HIBA-ÜZENET_SZÖVEG
jsonb_pretty | {
| "MESSAGE_TEXT": "érvénytelen bemeneti szintaxis a numerikus típushoz: "null""
| }Forrás: habr.com
