Podnetom na napísanie etúdy bola článok . Takisto sa zdala zaujímavá článok zverejnený pred 4 rokmi — .
Zaujímavé bolo to, že tá istá myšlienka - "realizovať obchodnú logiku v DB".
prišla na myseľ nielen mne samotnému.
Taktiež som si chcel v budúcnosti uchovať, pre seba predovšetkým, zaujímavé poznatky, ktoré vznikli počas realizácie. Zvlášť keď sa relatívne nedávno prijalo strategické rozhodnutie o zmene architektúry a presune obchodnej logiky na úroveň backendu. Takže všetko, čo bolo vyvinuté, čoskoro nikomu nebude potrebné a nikomu nebude zaujímavé.
Opísané metódy nie sú žiadnym objavom ani výnimočným know how, všetko je klasické a bolo realizované viackrát (ja som napríklad podobný prístup aplikoval pred 20 rokmi na Oracle). Rozhodol som sa len všetko zhromaždiť na jednom mieste. Možno to niekomu príde vhod. Ako ukázala prax — dosť často tá istá myšlienka príde nezávisle rôznym ľuďom. A pre seba si to nechať na pamiatku je užitočné.
Samozrejme, že nič na tomto svete nie je dokonalé, chyby a preklepy sú bohužiaľ možné. Kritiku a pripomienky vítam a očakávam. A ešte jedna maličkosť — konkrétne detaily realizácie sú vynechané. Nakoniec všetko je stále používané v skutočne fungujúcom projekte. Takže článok je skôr etúda a opis všeobecnej koncepcie, nič viac. Dúfam, že na pochopenie celkového obrazu sú detaily postačujúce.
Hlavná myšlienka — „rozdeľuj a panuj, skrývaj a maj v moci“
Klasická myšlienka — samostatná schéma pre tabuľky, samostatná schéma pre uložené funkcie.
Klient nemá prístup k údajom priamo. Všetko, čo klient môže vykonať — je len zavolať uloženú funkciu a spracovať prijatú odpoveď.
Úlohy
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Schémy
Schéma ukladania tabuliek
Cieľové tabuľky, ktoré realizujú predmetné entity.
CREATE SCHEMA store AUTHORIZATION store ;
Schéma systémových funkcií
Systémové funkcie, najmä pre logovanie zmien tabuliek.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;
Schéma lokálneho auditu
Funkcie a schémy na realizáciu lokálneho auditu vykonávania uložených funkcií a zmeny cieľových tabuliek.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Schéma servisných funkcií
Funkcie pre servisné a DML funkcie.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Schéma obchodných funkcií
Funkcie pre konečné obchodné funkcie vyvolávané klientom.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Práva prístupu
Úloha — DBA má plný prístup ku všetkým schémam (oddelené od úlohy DB Owner).
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;
Úloha — USER má privilégiu EXECUTE v schéme business_functions.
CREATE ROLE user_role;
Privilégiá medzi schémami
GRANT
Keďže všetky funkcie sú vytvorené s atribútom SECURITY DEFINER je potrebné pokyn 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 ;
Schéma DB je teda pripravená. Môžeme pristúpiť k naplneniu údajmi.
Cieľové tabulky
Vytváranie tabuliek je triviálne. Žiadne zvláštne aspekty, okrem toho, že sa rozhodlo vzdať sa použitia SERIAL a explicitne generovať sekvencie. Plus, samozrejme maximálne využitie pokynu
COMMENT ON ...Komentáre pre všetkých objekty, bez výnimky.
Lokálny audit
Na vedenie denníka vykonávania uložených funkcií a zmeny cieľových tabuliek sa používa tabuľka lokálneho auditu, ktorá zahŕňa aj detaily klientskej spojenia, označenie vyvolávaného modulu, skutočné hodnoty vstupných a výstupných parametrov vo forme JSON.
Systémové funkcie
Sú určené na zaznamenávanie zmien v cieľových tabuľkách. Predstavujú spúšťacie funkcie.
Šablóna — systémová funkcia
---------------------------------------------------------
-- VLOŽIŤ
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU sys_functions.table_insert_log ()
VRACIA TRIGGER AS $$
ZAČAŤ
VYKONAŤ loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
VRÁTIŤ NULL ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;
VYTVORIŤ TRIGGER table_after_insert PO VLOŽENÍ NA storage.table PRE KAŽDÝ RÁD EXECUTE PROCEDÚRU sys_functions.table_insert_log();
---------------------------------------------------------
-- AKTUALIZOVAŤ
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU sys_functions.table_update_log ()
VRACIA TRIGGER AS $$
ZAČAŤ
AK OLD.column != NEW.column
POTOM
VYKONAŤ loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column', NEW.column ) );
KONIEC AK ;
VRÁTIŤ NULL ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;
VYTVORIŤ TRIGGER table_after_update PO AKTUALIZÁCII NA storage.table PRE KAŽDÝ RÁD EXECUTE PROCEDÚRU sys_functions.table_update_log ();
---------------------------------------------------------
-- ZMAZANIE
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU sys_functions.table_delete_log ()
VRACIA TRIGGER AS $$
ZAČAŤ
VYKONAŤ loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
VRÁTIŤ NULL ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;
VYTVORIŤ TRIGGER table_after_delete PO ZMAZANÍ NA storage.table PRE KAŽDÝ RÁD EXECUTE PROCEDÚRU sys_functions.table_delete_log ();Služobné funkcie
Sú určené na realizáciu služobných a DML operácií nad cieľovými tabuľkami.
Šablóna — služobná funkcia
--VLOŽIŤ
--VRÁTIŤ ID NOVÉHO RÁD
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU service_functions.table_insert ( new_column store.table.column%TYPE )
VRACIA integer AS $$
DEKLAROVAŤ
new_id integer ;
ZAČAŤ
-- Vygenerovať nové id
new_id = nextval('store.table.seq');
-- Vložiť do tabuľky
VLOŽIŤ DO store.table
(
id ,
column
)
HODNOTY
(
new_id ,
new_column
);
VRÁTIŤ new_id ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;
--ZMAZAŤ
--VRÁTIŤ ČÍSLA RÁD ZMAZANÝCH
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU service_functions.table_delete ( current_id integer )
VRACIA integer AS $$
DEKLAROVAŤ
rows_count integer ;
ZAČAŤ
ZMAZAŤ Z store.table KDE id = current_id;
ZÍSKAŤ DIAGNOSTIKY rows_count = ROW_COUNT;
VRÁTIŤ rows_count ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;
-- AKTUALIZOVAŤ PODROBNOSTI
-- VRÁTIŤ ČÍSLA RÁD AKTUALIZOVANÝCH
VYTVORIŤ ALEBO NATRLO VIAC FUNKCIU service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
VRACIA integer AS $$
DEKLAROVAŤ
rows_count integer ;
ZAČAŤ
AKTUALIZOVAŤ store.table
NASTAVIŤ
column = new_column
KDE id = current_id;
ZÍSKAŤ DIAGNOSTIKY rows_count = ROW_COUNT;
VRÁTIŤ rows_count ;
KONIEC
$$ JAZYK plpgsql SECURITY DEFINER;Obchodné funkcie
Sú určené pre konečné obchodné funkcie volané klientom. Vždy vracajú — JSON. Na zachytenie a protokolovanie chýb vykonania sa používa blok VÝNIMKA.
Šablóna — obchodná funkcia
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Vstupné parametre
)
RETURNS JSON AS $$
DECLARE
------------------------
--na zachytávanie výnimiek
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
--LOGOVANIE
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ZAČATÉ',
json_build_object
(
--IN Parametre
)
);
PERFORM business_functions.notice('business_function_template');
--ZAČIATOK OBCHODNEJ ČASTI
--KONIEC OBCHODNEJ ČASTI
-- ÚSPEŠNÝ VÝSLEDOK
PERFORM business_functions.notice('výsledok');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'SKONČENÉ',
json_build_object( 'výsledok',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- ZACHYTÁVANIE VÝNIMIEK
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ZAČATÉ',
json_build_object
(
--IN Parametre
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' CHYBA',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' CHYBA',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' CHYBA-RETURNED_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',
' CHYBA-COLUMN_NAME',
json_build_object('COLUMN_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' CHYBA-CONSTRAINT_NAME',
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',
' CHYBA-PG_DATATYPE_NAME',
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',
' CHYBA-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' CHYBA-SCHEMA_NAME',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',
' CHYBA-PG_EXCEPTION_DETAIL',
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',
' CHYBA-PG_EXCEPTION_HINT',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',
' CHYBA-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
RAISE WARNING 'POPLACH: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Záver
Na opísanie celkového obrazu si myslím, že to úplne postačuje. Ak niekoho zaujímajú podrobnosti a výsledky, píšte komentáre, s radosťou doplním obraz ďalšími detailmi.
P.S.
Zaznamenávanie jednoduchej chyby — typ vstupného parametra
-[ ZÁZNAM 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ZAČATÉ
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Typ1",
| "title": "VYTVORENÉ pomocou addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "popis",
| "lowerTr": "100",
| "measurement": "mierka1",
| "methodology": "m1",
| "passportUrl": "files",
| "upperTr": "200",
| "weightingFactor": 100.123,
| "actualTrValue": null,
| "upperTrCalcNumber": "120"
| },
| "CardId": 3
| }
-[ ZÁZNAM 2 ]-
date_trunc | 2020-08-19 13:15:46
id | 1073
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA
jsonb_pretty | {
| "SQLSTATE": "22P02"
| }
-[ ZÁZNAM 3 ]-
date_trunc | 2020-08-19 13:15:46
id | 1074
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA
jsonb_pretty | {
| "SQLERRM": "neplatná vstupná syntax pre typ číselný: "null""
| }
-[ ZÁZNAM 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-VRÁTENÝ_SQLSTATE
jsonb_pretty | {
| "RETURNED_SQLSTATE": "22P02"
| }
-[ ZÁZNAM 5 ]-
date_trunc | 2020-08-19 13:15:46
id | 1076
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-NÁZOV_STĹPCA
jsonb_pretty | {
| "COLUMN_NAME": ""
| }
-[ ZÁZNAM 6 ]-
date_trunc | 2020-08-19 13:15:46
id | 1077
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-NÁZOV_OBMEDZENIA
jsonb_pretty | {
| "CONSTRAINT_NAME": ""
| }
-[ ZÁZNAM 7 ]-
date_trunc | 2020-08-19 13:15:46
id | 1078
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-NÁZOV_DATOVÉHO_TYPU_PG
jsonb_pretty | {
| "PG_DATATYPE_NAME": ""
| }
-[ ZÁZNAM 8 ]-
date_trunc | 2020-08-19 13:15:46
id | 1079
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-TEKST_SPRÁVY
jsonb_pretty | {
| "MESSAGE_TEXT": "neplatná vstupná syntax pre typ číselný: "null""
| }
-[ ZÁZNAM 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-NÁZOV_SCHÉMY
jsonb_pretty | {
| "SCHEMA_NAME": ""
| }
-[ ZÁZNAM 10 ]-
date_trunc | 2020-08-19 13:15:46
id | 1081
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-PG_VYJASNENIE_VÝNIMKY
jsonb_pretty | {
| "PG_EXCEPTION_DETAIL": ""
| }
-[ ZÁZNAM 11 ]-
date_trunc | 2020-08-19 13:15:46
id | 1082
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-PG_NÁZOV_NÁPRAVY_VÝNIMKY
jsonb_pretty | {
| "PG_EXCEPTION_HINT": ""
| }
-[ ZÁZNAM 12 ]-
date_trunc | 2020-08-19 13:15:46
id | 1083
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-PG_KONTEXT_VÝNIMKY
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-TEKST_SPRÁVY
jsonb_pretty | {
| "MESSAGE_TEXT": "neplatná vstupná syntax pre typ číselný: "null""
| }Zdroj: habr.com
