Мотивът за написването на есе беше статията . Също ми се стори интересна статията, публикувана преди 4 години — .
Интересното е, че една и съща идея —реализиране на бизнес логиката в БД".
мине през главата не само на мен.
Също така в бъдеще бих искал да запазя, преди всичко за себе си, интересни идеи, възникнали по време на реализацията. Особено като се има предвид, че относително отскоро беше взето стратегическо решение за смяна на архитектурата и прехвърляне на бизнес логиката на ниво бекенд. Така че всичко, което беше разработено, скоро няма да бъде нужно на никого и никого няма да интересува.
Описаните методи не са нищо особено или уникално know how, всичко по класическия начин и е реализирано многократно (аз например прилагах подобен подход преди 20 години на Oracle). Просто реших да събера всичко на едно място. Вдруг за някого ще е полезно. Както показа практиката — доста често една и съща идея идва независимо на различни хора. А и полезно е да оставиш нещо за спомен.
Разбира се, че нищо в този свят не е перфектно, грешки и печатни грешки, за съжаление, са възможни. Критиката и коментарите са винаги добре дошли и очаквани. И още един малък детайл — конкретни детайли на реализацията са опуснати. Все пак всичко се използва в реално работещ проект. Така че статията е по-скоро есе и описание на общата концепция, не повече от това. Надявам се детайлите да са достатъчни за разбиране на общата картина.
Общата идея е - „разделяй и владей, скривай и владей“
Идеята е класическа — отделна схема за таблиците, отделна схема за съхранените функции.
Клиентът няма директен достъп до данните. Всичко, което клиентът може да направи, е само да извика съхранена функция и да обработи получения отговор.
Роли
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Схеми
Схема на съхранение на таблици
Целеви таблици, реализиращи предметни сущности.
CREATE SCHEMA store AUTHORIZATION store;
Схема на системните функции
Системни функции, по-специално за логиране на промените на таблиците.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;
Схема на локалния одит
Функции и таблици за реализиране на локален одит на изпълнението на съхранявани функции и промените на целевите таблици.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Схема на сервизни функции
Функции за сервизни и DML функции.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Схема на бизнес функции
Функции за крайни бизнес функции, които се извикват от клиента.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Права за достъп
Роля — Методи за оптимизация има пълен достъп до всички схеми (отделена от ролята 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;
Роля — USER има привилегия EXECUTE в схемата business_functions.
CREATE ROLE user_role;
Привилегиите между схемите
GRANT
Тъй като всички функции се създават с атрибут SECURITY DEFINER е необходима инструкция 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;
И така схемата на БД — е готова. Можете да започнете с попълването на данни.
Целеви таблици
Създаването на таблици е тривиално. Няма особени изисквания, освен че беше решение да се откаже от използването на SERIAL и да се генерират последователности явно. Плюс, разбира се, максимално използване на инструкцията
COMMENT ON ...Коментари за на всичките обектите, без изключения.
Локален одит
За водене на журнал за изпълнението на съхранявани функции и промени в целевите таблици се използва таблица за локален одит, която включва, наред с други, детайли за клиентското свързване, етикет на извиквания модул, действителните стойности на входни и изходни параметри във формат JSON.
Системни функции
Предназначени са за логиране на промените в целевите таблици. Представляват триггерни функции.
Шаблон — системна функция
---------------------------------------------------------
-- ВСТАВКА
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();
---------------------------------------------------------
-- ОБНОВЛЕНИЕ
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 ();
---------------------------------------------------------
-- УДАЛЕНИЕ
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 ();Сервизни функции
Предназначени за изпълнение на сервизни и DML операции върху целевите таблици.
Шаблон — сервизна функция
--ВСТАВКА
--ВРЪЩА id НА НОВИЯ РЕД
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Генерирайте нов id
new_id = nextval('store.table.seq');
-- Вмъкване в таблицата
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--УДАЛЕНИЕ
--ВРЪЩА БРОЙ ИЗТРИТИ РЕДОВЕ
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;
-- ОБНОВЛЕНИЕ НА ДЕТАЙЛИ
-- ВРЪЩА БРОЙ АКТУАЛИЗИРАНИ РЕДОВЕ
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;Бизнес функции
Предназначени за крайни бизнес функции, извиквани от клиента. Връщат винаги — JSON. За прихващане и логиране на грешки при изпълнение, се използва блок EXCEPTION.
Шаблон — бизнес функция
СЪЗДАЙТЕ ИЛИ ЗАМЕНИТЕ ФУНКЦИЯ business_functions.business_function_template(
--Входни параметри
)
ВРЪЩА JSON AS $$
ДЕКЛАРИРАЙ
------------------------
--за улавяне на изключения
error_message text ;
error_json json ;
result json ;
------------------------
ЗАПОЧВАЙ
--ЛОГВАНЕ
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
'НАЧАЛО',
json_build_object
(
--Входни параметри
)
);
ПРЕДПОЛАГАМ business_functions.notice('business_function_template');
--ЗАПОЧВАНЕ НА БИЗНЕС ЧАСТ
--КРАЙ НА БИЗНЕС ЧАСТ
-- УСПЕШЕН РЕЗУЛТАТ
ПРЕДПОЛАГАМ business_functions.notice('резултат');
ПРЕДПОЛАГАМ business_functions.notice(result);
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
'ЗАВЪРШЕНО',
json_build_object( 'резултат', result )
);
ВРЪЩАМ result ;
----------------------------------------------------------------------------------------------------------
-- УЛАВЯНЕ НА ИЗКЛЮЧЕНИЯ
ИЗКЛЮЧЕНИЕ
КОГАТО ДРУГИ ТОГАВА
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
'НАЧАЛО',
json_build_object
(
--Входни параметри
) , TRUE );
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА',
json_build_object('SQLSTATE', SQLSTATE ), TRUE
);
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА',
json_build_object('SQLERRM', SQLERRM ), TRUE
);
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = RETURNED_SQLSTATE ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-RETURNED_SQLSTATE', json_build_object('RETURNED_SQLSTATE', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = COLUMN_NAME ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-COLUMN_NAME',
json_build_object('COLUMN_NAME', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = CONSTRAINT_NAME ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = PG_DATATYPE_NAME ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = MESSAGE_TEXT ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-MESSAGE_TEXT', json_build_object('MESSAGE_TEXT', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = SCHEMA_NAME ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(s
'business_function_template',
' ГРЕШКА-SCHEMA_NAME', json_build_object('SCHEMA_NAME', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = PG_EXCEPTION_DETAIL ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = PG_EXCEPTION_HINT ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-PG_EXCEPTION_HINT', json_build_object('PG_EXCEPTION_HINT', error_message ), TRUE );
ВЗЕМИ СТЕК ДИАГНОСТИКА error_message = PG_EXCEPTION_CONTEXT ;
ПРЕДПОЛАГАМ loc_audit_functions.make_log
(
'business_function_template',
' ГРЕШКА-PG_EXCEPTION_CONTEXT', json_build_object('PG_EXCEPTION_CONTEXT', error_message ), TRUE );
ВДИГНИТЕ ПРЕДУПРЕЖДЕНИЕ 'АЛАРМА: %' , SQLERRM ;
ИЗБЕРЕТЕ json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) В НЕПОДВИЖЕН error_json ;
ВРЪЩАЙТЕ error_json ;
КРАЙ
$$ ЯЗИК plpgsql СИГУРЕН ДЕФИНИТОР;Резюме
За да опиша общата картина, смятам, че е напълно достатъчно. Ако някой се интересува от детайли и резултати - пишете коментари, с удоволствие ще допълня картината с допълнителни щрихи.
P.S.
Логиране на проста грешка — тип на входния параметър
-[ ЗАПИС 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | НАЧАЛО
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Type1",
| "title": "СЪЗДАДЕНО ОТ addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "описание",
| "lowerTr": "100",
| "measurement": "измерение1",
| "methodology": "m1",
| "passportUrl": "files",
| "upperTr": "200",
| "weightingFactor": 100.123,
| "actualTrValue": null,
| "upperTrCalcNumber": "120"
| },
| "CardId": 3
| }
-[ ЗАПИС 2 ]-
date_trunc | 2020-08-19 13:15:46
id | 1073
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА
jsonb_pretty | {
| "SQLSTATE": "22P02"
| }
-[ ЗАПИС 3 ]-
date_trunc | 2020-08-19 13:15:46
id | 1074
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА
jsonb_pretty | {
| "SQLERRM": "невалиден синтаксис на входа за тип numeric: "null""
| }
-[ ЗАПИС 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ВЪРНАТО_SQLSTATE
jsonb_pretty | {
| "RETURNED_SQLSTATE": "22P02"
| }
-[ ЗАПИС 5 ]-
date_trunc | 2020-08-19 13:15:46
id | 1076
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ИМЕ_НА_КОЛОНА
jsonb_pretty | {
| "COLUMN_NAME": ""
| }
-[ ЗАПИС 6 ]-
date_trunc | 2020-08-19 13:15:46
id | 1077
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ИМЕ_НА_ОБРЪЩЕНИЕ
jsonb_pretty | {
| "CONSTRAINT_NAME": ""
| }
-[ ЗАПИС 7 ]-
date_trunc | 2020-08-19 13:15:46
id | 1078
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ИМЕ_НА_PG_ДАННИ
jsonb_pretty | {
| "PG_DATATYPE_NAME": ""
| }
-[ ЗАПИС 8 ]-
date_trunc | 2020-08-19 13:15:46
id | 1079
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ТЕКСТ_НА_СЪОБЩЕНИЕ
jsonb_pretty | {
| "MESSAGE_TEXT": "невалиден синтаксис на входа за тип numeric: "null""
| }
-[ ЗАПИС 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ИМЕ_НА_СХЕМА
jsonb_pretty | {
| "SCHEMA_NAME": ""
| }
-[ ЗАПИС 10 ]-
date_trunc | 2020-08-19 13:15:46
id | 1081
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ПД_ДЕТАЙЛ_НА_ИСКАНЕТО
jsonb_pretty | {
| "PG_EXCEPTION_DETAIL": ""
| }
-[ ЗАПИС 11 ]-
date_trunc | 2020-08-19 13:15:46
id | 1082
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ПД_ЗАБЕЛЕЖКА_КЪМ_ИСКАНЕТО
jsonb_pretty | {
| "PG_EXCEPTION_HINT": ""
| }
-[ ЗАПИС 12 ]-
date_trunc | 2020-08-19 13:15:46
id | 1083
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ПД_КОНТЕКСТ_НА_ИСКАНЕТО
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ГРЕШКА-ТЕКСТ_НА_СЪОБЩЕНИЕ
jsonb_pretty | {
| "MESSAGE_TEXT": "невалиден синтаксис на входа за тип numeric: "null""
| }Източник: habr.com
