Показалось интересным то, что одна и та же мысль-"реализовать бизнес-логику в БД".
пришла в голову не только мне одному.
Также на будущее хотелось сохранить, для себя в первую очередь, интересные наработки возникшие по ходу реализации. Особенно учитывая то, что относительно недавно было принято стратегическое решение о смене архитектуры и переносе бизнес-логики на уровень backend. Так, что все, что было наработано, скоро никому не понадобится и никому будет не интересно.
Описанные методы не являются каким то открытием и исключительным 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;
Права доступа
Роль — DBA имеет полный доступ ко всем схемам (отделена от роли 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;
Привилегии между схемами
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.
Системные функции
Предназначены для логирования изменений в целевых таблицах. Представляют собой триггерные функции.
--------------------------------------------------------- -- INSERT 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(); --------------------------------------------------------- -- UPDATE 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 (); --------------------------------------------------------- -- DELETE 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 операций над целевыми таблицами.
--INSERT --RETURN id OF NEW ROW CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE ) RETURNS integer AS $$ DECLARE new_id integer ; BEGIN -- Generate new id new_id = nextval('store.table.seq'); -- Insert into table INSERT INTO store.table ( id , column ) VALUES ( new_id , new_column ); RETURN new_id ; END $$ LANGUAGE plpgsql SECURITY DEFINER; --DELETE --RETURN ROW NUMBERS DELETED 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; -- UPDATE DETAILS -- RETURN ROW NUMBERS UPDATED 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.
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', 'STARTED', json_build_object ( --IN Parameters ) ); PERFORM business_functions.notice('business_function_template'); --START BUSINESS PART --END BUSINESS PART -- SUCCESFULLY RESULT PERFORM business_functions.notice('result'); PERFORM business_functions.notice(result); PERFORM loc_audit_functions.make_log ( 'business_function_template', 'FINISHED', json_build_object( 'result',result ) ); RETURN result ; ---------------------------------------------------------------------------------------------------------- -- EXCEPTION CATCHING EXCEPTION WHEN OTHERS THEN PERFORM loc_audit_functions.make_log ( 'business_function_template', 'STARTED', json_build_object ( --IN Parameters ) , TRUE ); PERFORM loc_audit_functions.make_log ( 'business_function_template', ' ERROR', json_build_object('SQLSTATE',SQLSTATE ), TRUE ); PERFORM loc_audit_functions.make_log ( 'business_function_template', ' ERROR', json_build_object('SQLERRM',SQLERRM ), TRUE ); GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ; PERFORM loc_audit_functions.make_log ( 'business_function_template', ' ERROR-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', ' ERROR-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', ' ERROR-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', ' ERROR-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', ' ERROR-MESSAGE_TEXT',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', ' ERROR-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', ' ERROR-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', ' ERROR-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', ' ERROR-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;
Итог
Для описания общей картины, думаю вполне достаточно. Если кого заинтересовали детали и результаты-пишите комментарии, с удовольствием дополню картину дополнительными штрихами.
P.S.
-[ 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": "CREATED BY 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 | } -[ RECORD 2 ]- date_trunc | 2020-08-19 13:15:46 id | 1073 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR 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 | ERROR jsonb_pretty | { | "SQLERRM": "invalid input syntax for type numeric: \"null\"" | } -[ RECORD 4 ]- date_trunc | 2020-08-19 13:15:46 id | 1075 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-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 | ERROR-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 | ERROR-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 | ERROR-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 | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: \"null\"" | } -[ RECORD 9 ]- date_trunc | 2020-08-19 13:15:46 id | 1080 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-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 | ERROR-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 | ERROR-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 | ERROR-PG_EXCEPTION_CONTEXT jsonb_pretty | { usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: \"null\"" | }
ссылка на оригинал статьи https://habr.com/ru/post/515628/
Добавить комментарий