Этюд по реализация бизнес-логики на уровне хранимых функций PostgreSQL19.08.2020 16:02

Побудительным мотивом к написанию этюда послужила статья «В карантин нагрузка выросла в 5 раз, но мы были готовы». Как Lingualeo переехал на PostgreSQL с 23 млн юзеров.

Показалось интересным то, что одна и та же мысль-»реализовать бизнес-логику в БД».

jxywmloivzoe9jko7vymisrnueg.jpeg

пришла в голову не только мне одному.

Также на будущее хотелось сохранить, для себя в первую очередь, интересные наработки возникшие по ходу реализации. Особенно учитывая то, что относительно недавно было принято стратегическое решение о смене архитектуры и переносе бизнес-логики на уровень 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;


Привилегии между схемами


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.

Системные функции


Предназначены для логирования изменений в целевых таблицах. Представляют собой триггерные функции.

Шаблон — системная функция 
---------------------------------------------------------
-- 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;


Итог


Для описания общей картины, думаю вполне достаточно. Если кого заинтересовали детали и результаты-пишите комментарии, с удовольствием дополню картину дополнительными штрихами.

© Habrahabr.ru