PostgreSQL 저장 함수 수준의 비즈니스 로직 구현에 관한 연구

이 스케치를 쓰게 된 원동력은 기사였습니다. “격리 기간 동안 작업량이 5배 증가했지만 우리는 준비가 되어 있었습니다.” Lingualeo가 23만 명의 사용자를 보유한 PostgreSQL로 전환한 방법. 4년 전에 출판된 기사도 흥미로웠습니다. MySQL에서 비즈니스 로직 구현.

같은 생각이 흥미로워 보였습니다. "데이터베이스에 비즈니스 로직 구현".

나만 생각난 게 아니었다.

또한 미래를 위해 우선 구현 과정에서 발생한 흥미로운 발전을 보존하고 싶었습니다. 특히 비교적 최근에 아키텍처를 변경하고 비즈니스 로직을 백엔드 수준으로 이전하기 위한 전략적 결정이 내려졌다는 점을 고려하면 더욱 그렇습니다. 그래서 개발된 모든 것은 곧 누구에게도 쓸모가 없고 누구에게도 관심이 없게 될 것입니다.

설명된 방법은 일종의 발견이나 예외가 아닙니다. 노하우, 모든 것이 고전적이며 여러 번 구현되었습니다.(예를 들어 저는 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 소유자 역할과 분리됨).

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 특권이 있다 실행 다이어그램에서 비즈니스_기능.

CREATE ROLE user_role;

체계 간 권한

GRANT
모든 함수는 속성으로 생성되기 때문에 보안 정의자 필요한 지침 모든 기능에 대한 실행 취소… 공개;

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 ;

이제 데이터베이스 스키마가 준비되었습니다. 데이터 채우기를 시작할 수 있습니다.

대상 테이블

테이블을 만드는 것은 간단합니다. 사용하지 않기로 결정한 것 외에 특별한 기능은 없습니다. 연속물 명시적으로 시퀀스를 생성합니다. 물론 지침을 최대한 활용하는 것도 가능합니다.

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. 실행 오류를 가로채서 기록하려면 블록을 사용하세요. 예외.

템플릿 - 비즈니스 기능

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;

합계

전체적인 그림을 설명하자면 이 정도면 충분하다고 생각합니다. 세부 사항과 결과에 관심이 있는 분이 계시다면 댓글을 달아 주시면 사진에 추가 작업을 추가해 드리겠습니다.

PS

단순 오류 로깅 - 입력 매개변수 유형

-[ 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""
                | }

출처 : habr.com