Podnětem k napsání studie byl článek . Také se mi zdálo zajímavé, že článek byl publikován před 4 lety — .
Zajímavé mi přišlo, že stejná myšlenka — "implementovat obchodní logiku v DB".
napadla nejen mě.
Také bych chtěl do budoucna uchovat zajímavé poznatky, které vznikly během realizace, především pro sebe. Zvlášť vzhledem k tomu, že poměrně nedávno bylo přijato strategické rozhodnutí o změně architektury a přesunu obchodní logiky na úroveň backendu. Takže to, co bylo vytvořeno, brzy nebude nikomu potřebné a nikoho nezajímá.
Popisované metody nejsou žádným objevem či výjimečným know how, vše podle klasiky a bylo realizováno mnohokrát (já například podobný přístup použil před 20 lety na Oracle). Jen jsem se rozhodl všechno shromáždit na jedno místo. Možná se to někomu bude hodit. Jak ukázala praxe — poměrně často stejný nápad přichází nezávisle různým lidem. A také pro sebe jako vzpomínku je to užitečné.
Samozřejmě, že nic na tomto světě není dokonalé, chyby a překlepy jsou, bohužel, možné. Kritika a připomínky jsou vítány a očekávány. A ještě jedna maličkost — konkrétní detaily realizace jsou opomenuty. Všechno se totiž zatím používá v reálném pracovním projektu. Takže článek je jako studie a popis obecné koncepce, nic víc. Doufám, že pro pochopení celkového obrázku, jsou detaily dostatečné.
Obecná myšlenka — „dělení a vládnutí, skrývání a vlastnění“
Klasická myšlenka — samostatný schéma pro tabulky, samostatný schéma pro uložené funkce.
Klient nemá přístup k datům přímo. Všechno, co může klient provést — pouze zavolat uloženou funkci a zpracovat přijatou odpověď.
Role
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Schémata
Schéma pro ukládání tabulek
Cílové tabulky, které realizují předmětové entity.
CREATE SCHEMA store AUTHORIZATION store;
Schéma systémových funkcí
Systémové funkce, zejména pro logování změn tabulek.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;
Schéma lokálního auditu
Funkce a schémata pro realizaci lokálního auditu provádění uložených funkcí a změn cílových tabulek.
VYTVORIT SCHÉMA loc_audit_functions AUTORIZACE loc_audit_functions;
Schéma servisních funkcí
Funkce pro servisní a DML funkce.
VYTVORIT SCHÉMA service_functions AUTORIZACE service_functions;
Schéma obchodních funkcí
Funkce pro konečné obchodní funkce vyvolávané klientem.
VYTVORIT SCHÉMA business_functions AUTORIZACE business_functions;
Práva přístupu
Role — DBA má plný přístup ke všem schématům (odděleno od role DB Owner).
VYTVORIT ROLE dba_role;
UDĚLOVAT store PRO dba_role;
UDĚLOVAT sys_functions PRO dba_role;
UDĚLOVAT loc_audit_functions PRO dba_role;
UDĚLOVAT service_functions PRO dba_role;
UDĚLOVAT business_functions PRO dba_role;
Role — UŽIVATEL má privilegium PROVÉST ve schématu business_functions.
VYTVORIT ROLE user_role;
Privilegie mezi schématy
UDĚLOVAT
Vzhledem k tomu, že všechny funkce jsou vytvářeny s atributem SECURITY DEFINER je potřeba příkaz ODEBRAT EXECUTE NA VŠECH FUNKCÍCH… OD veřejnosti;
ODEBRAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA sys_functions OD veřejnosti;
ODEBRAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA loc_audit_functions OD veřejnosti;
ODEBRAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA service_functions OD veřejnosti;
ODEBRAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA business_functions OD veřejnosti;
UDĚLOVAT POUŽITÍ VE SCHÉMA sys_functions PRO dba_role;
UDĚLOVAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA sys_functions PRO dba_role;
UDĚLOVAT POUŽITÍ VE SCHÉMA loc_audit_functions PRO dba_role;
UDĚLOVAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA loc_audit_functions PRO dba_role;
UDĚLOVAT POUŽITÍ VE SCHÉMA service_functions PRO dba_role;
UDĚLOVAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA service_functions PRO dba_role;
UDĚLOVAT POUŽITÍ VE SCHÉMA business_functions PRO dba_role;
UDĚLOVAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA business_functions PRO dba_role;
UDĚLOVAT EXECUTE NA VŠECH FUNKCÍCH VE SCHÉMA business_functions PRO user_role;
UDĚLOVAT VŠECHNA PRÁVA VE SCHÉMA store PRO SKUPINU business_functions;
UDĚLOVAT VŠECHNA PRÁVA NA VŠECH TABELÁCH VE SCHÉMA store PRO business_functions;
UDĚLOVAT POUŽITÍ NA VŠECH SEKVENCÍCH VE SCHÉMA store PRO business_functions;
Takže schéma DB je hotové. Můžeme začít s naplňováním daty.
Cílové tabulky
Vytvoření tabulek je triviální. Žádné zvláštnosti, s výjimkou toho, že bylo rozhodnuto vzdát se používání SERIAL a generovat sekvence explicitně. Plus, samozřejmě maximální využití příkazu
KOMENTÁŘ K ...Komentáře pro všechny objekty, bez výjimek.
Lokální audit
Pro vedení záznamu o provádění uložených funkcí a změn cílových tabulek se používá tabulka lokálního auditu, která obsahuje také podrobnosti o klientském spojení, značku vyvolávaného modulu, skutečné hodnoty vstupních a výstupních parametrů ve formátu JSON.
Systémové funkce
Jsou určeny pro logování změn v cílových tabulkách. Představují spouštěcí funkce.
Šablona — systémová funkce
---------------------------------------------------------
-- VLOŽIT
VYTVORIT NEBO REPLACE FUNCTION sys_functions.table_insert_log ()
VRACÍ TRIGGER AS $$
ZAČÍT
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
VRÁTIT NULL ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
VYTVOŘIT TRIGGER table_after_insert PO VLOŽENÍ NA storage.table PRO KAŽDÝ ŘÁDEK VYKONAT PROCEDURU sys_functions.table_insert_log();
---------------------------------------------------------
-- AKTUALIZACE
VYTVORIT NEBO REPLACE FUNCTION sys_functions.table_update_log ()
VRACÍ TRIGGER AS $$
ZAČÍT
POKUD OLD.column != NEW.column
POTOM
PERFORM loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column' , NEW.column ) );
KONEC POKUD ;
VRÁTIT NULL ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
VYTVOŘIT TRIGGER table_after_update PO AKTUALIZACI NA storage.table PRO KAŽDÝ ŘÁDEK VYKONAT PROCEDURU sys_functions.table_update_log();
---------------------------------------------------------
-- SMAZAT
VYTVORIT NEBO REPLACE FUNCTION sys_functions.table_delete_log ()
VRACÍ TRIGGER AS $$
ZAČÍT
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
VRÁTIT NULL ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
VYTVOŘIT TRIGGER table_after_delete PO SMAZÁNÍ NA storage.table PRO KAŽDÝ ŘÁDEK VYKONAT PROCEDURU sys_functions.table_delete_log ();Služební funkce
Jsou určeny k provádění servisních a DML operací na cílových tabulkách.
Šablona — služební funkce
--VLOŽIT
--VRÁTIT id NOVÉHO ŘÁDKU
VYTVORIT NEBO REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
VRACÍ integer AS $$
DECLARE
new_id integer ;
ZAČÍT
-- Generovat nové id
new_id = nextval('store.table.seq');
-- Vložit do tabulky
VLOŽIT DO store.table
(
id ,
column
)
HODNOTY
(
new_id ,
new_column
);
VRÁTIT new_id ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
--SMAZAT
--VRÁTIT ČÍSLO ŘÁDKŮ SMAZANÝCH
VYTVORIT NEBO REPLACE FUNCTION service_functions.table_delete ( current_id integer )
VRACÍ integer AS $$
DECLARE
rows_count integer ;
ZAČÍT
SMAZAT Z store.table KDE id = current_id;
ZÍSKAT DIAGNOSTIKU rows_count = ROW_COUNT;
VRÁTIT rows_count ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;
-- AKTUALIZACE DETAILŮ
-- VRÁTIT ČÍSLO ŘÁDKŮ AKTUALIZOVANÝCH
VYTVORIT NEBO REPLACE FUNCTION service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
VRACÍ integer AS $$
DECLARE
rows_count integer ;
ZAČÍT
AKTUALIZOVAT store.table
NASTAVIT
column = new_column
KDE id = current_id;
ZÍSKAT DIAGNOSTIKU rows_count = ROW_COUNT;
VRÁTIT rows_count ;
KONEC
$$ JAZYK plpgsql BEZPEČNOSTNÍ DEFINER;Obchodní funkce
Jsou určeny pro koncové obchodní funkce vyvolávané klientem. Vždy vracejí — JSON. Pro zachycení a logování chyb provádění se používá blok VYJÍMKY.
Šablona — obchodní funkce
VYTVOŘIT NEBO ZMĚNIT FUNKCI business_functions.business_function_template(
-- Vstupní parametry
)
VRACÍ JSON AS $$
DECLARE
------------------------
-- pro zachytávání výjimek
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
-- ZAZNAMENÁNÍ
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ZAČÁTEK',
json_build_object
(
-- Vstupní parametry
)
);
PERFORM business_functions.notice('business_function_template');
-- ZAČÁTEK OBCHODNÍ ČÁSTI
-- KONEC OBCHODNÍ ČÁSTI
-- ÚSPĚŠNÝ VÝSLEDOK
PERFORM business_functions.notice('výsledek');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'SKONČENO',
json_build_object( 'výsledek',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- ZACHYTÁVÁNÍ VÝJIMEK
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ZAČÁTEK',
json_build_object
(
-- Vstupní parametry
) , 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 'ALARM: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Výsledek
Pro popsání celkového obrazu si myslím, že to stačí. Pokud někoho zaujaly detaily a výsledky, pište do komentářů, rád doplním obraz dalšími detaily.
P.S.
Logování jednoduché chyby — typ vstupního parametru
-[ ZÁZNAM 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | SPUŠTĚNO
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Type1",
| "title": "VYTVOŘENO pomocí addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "decription",
| "lowerTr": "100",
| "measurement": "measurement1",
| "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á syntaxe vstupu pro typ numerický: "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-RETURNED_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-JMÉNO_SLUPCE
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ÁZEV_OHLAŠOVANÉHO_OHROZENÍ
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-PG_DATATYPE_NAME
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-ZPRÁVA_TEXTU
jsonb_pretty | {
| "MESSAGE_TEXT": "neplatná syntaxe vstupu pro typ numerický: "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ÁZEV_SČÍTACÍHO_KONTEXTU
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_EXCEPTION_DETAIL
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_EXCEPTION_HINT
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_EXCEPTION_CONTEXT
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | CHYBA-ZPRÁVA_TEXTU
jsonb_pretty | {
| "MESSAGE_TEXT": "neplatná syntaxe vstupu pro typ numerický: "null""
| }Zdroj: habr.com
