Động lực để viết bản phác thảo này là bài báo . Tôi cũng thấy bài viết được xuất bản cách đây 4 năm rất thú vị - .
Có vẻ thú vị là cùng một suy nghĩ - "triển khai logic nghiệp vụ trong cơ sở dữ liệu".
Không chỉ có tôi nghĩ đến điều đó.
Ngoài ra, trong tương lai, trước hết, tôi muốn lưu giữ cho bản thân mình những phát triển thú vị nảy sinh trong quá trình thực hiện. Đặc biệt khi xem xét rằng gần đây, một quyết định chiến lược đã được đưa ra nhằm thay đổi kiến trúc và chuyển logic nghiệp vụ sang cấp độ phụ trợ. Vì vậy, mọi thứ đã được phát triển sẽ sớm trở nên vô dụng với bất kỳ ai và không được ai quan tâm.
Các phương pháp được mô tả không phải là một dạng khám phá hay ngoại lệ nào đó. biết làm thế nào, mọi thứ đều cổ điển và đã được triển khai nhiều lần (ví dụ: tôi đã sử dụng cách tiếp cận tương tự trên Oracle 20 năm trước), tôi chỉ quyết định thu thập mọi thứ vào một nơi. Trong trường hợp nó có ích cho ai đó. Như thực tế đã chỉ ra, khá thường xuyên, cùng một ý tưởng đến với những người khác nhau một cách độc lập. Và sẽ rất hữu ích nếu bạn giữ nó làm kỷ niệm cho riêng mình.
Tất nhiên, trên đời này không có gì là hoàn hảo, rất tiếc là có thể xảy ra lỗi và lỗi chính tả. Những lời chỉ trích và bình luận đều được hoan nghênh và mong đợi mạnh mẽ. Và một chi tiết nhỏ nữa - chi tiết thực hiện cụ thể đã bị lược bỏ. Tuy nhiên, mọi thứ vẫn được sử dụng trong một dự án hoạt động thực sự. Vì vậy, bài viết chỉ là bản phác thảo và mô tả khái niệm chung, không có gì hơn. Tôi hy vọng có đủ chi tiết để hiểu được bức tranh tổng thể.
Ý tưởng chung là "chia để trị, ẩn và sở hữu"
Ý tưởng này rất cổ điển - một lược đồ riêng cho các bảng, một lược đồ riêng cho các hàm được lưu trữ.
Khách hàng không có quyền truy cập trực tiếp vào dữ liệu. Tất cả những gì khách hàng có thể làm là gọi hàm được lưu trữ và xử lý phản hồi nhận được.
Vai trò
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Đề án
Sơ đồ lưu trữ bảng
Các bảng mục tiêu triển khai các thực thể chủ đề.
CREATE SCHEMA store AUTHORIZATION store ;
Sơ đồ chức năng hệ thống
Các chức năng hệ thống, đặc biệt là để ghi nhật ký các thay đổi trong bảng.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;
Chương trình kiểm toán địa phương
Các hàm và bảng để triển khai kiểm tra cục bộ việc thực thi các hàm được lưu trữ và các thay đổi đối với các bảng mục tiêu.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Sơ đồ chức năng dịch vụ
Các hàm dành cho dịch vụ và hàm DML.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Sơ đồ chức năng nghiệp vụ
Chức năng cho các chức năng kinh doanh cuối cùng được khách hàng gọi.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Quyền truy cập
Vai trò - DBA có toàn quyền truy cập vào tất cả các lược đồ (tách biệt với vai trò Chủ sở hữu 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;
Vai trò - USER có đặc quyền HÀNH HÌNH trong sơ đồ chức năng kinh doanh.
CREATE ROLE user_role;
Ưu đãi giữa các chương trình
BAN CHO
Vì tất cả các hàm đều được tạo bằng thuộc tính CÔNG CỤ ĐỊNH NGHĨA BẢO MẬT hướng dẫn cần thiết THU HỒI THỰC HIỆN TRÊN TẤT CẢ CHỨC NĂNG… TỪ công khai;
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 ;
Vậy là lược đồ cơ sở dữ liệu đã sẵn sàng. Bạn có thể bắt đầu điền dữ liệu.
Bảng mục tiêu
Tạo bảng là chuyện nhỏ. Không có tính năng đặc biệt nào, ngoại trừ việc nó được quyết định không sử dụng NỐI TIẾP và tạo ra các chuỗi một cách rõ ràng. Ngoài ra, tất nhiên, việc sử dụng tối đa các hướng dẫn
COMMENT ON ...Bình luận cho của tất cả đối tượng, không có ngoại lệ.
Kiểm toán địa phương
Để ghi lại quá trình thực thi các chức năng được lưu trữ và các thay đổi đối với bảng mục tiêu, bảng kiểm tra cục bộ được sử dụng, bao gồm, cùng với những thứ khác, chi tiết về kết nối máy khách, nhãn của mô-đun được gọi và các giá trị thực của đầu vào và các tham số đầu ra ở dạng JSON.
Chức năng hệ thống
Được thiết kế để ghi nhật ký các thay đổi trong bảng mục tiêu. Chúng là các chức năng kích hoạt.
Mẫu - chức năng hệ thống
---------------------------------------------------------
-- 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 ();Chức năng dịch vụ
Được thiết kế để triển khai các hoạt động dịch vụ và DML trên các bảng mục tiêu.
Mẫu - chức năng dịch vụ
--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;Chức năng kinh doanh
Được thiết kế cho các chức năng kinh doanh cuối cùng được khách hàng yêu cầu. Họ luôn quay trở lại - JSON. Để chặn và ghi lại các lỗi thực thi, hãy sử dụng khối NGOẠI LỆ.
Mẫu - chức năng kinh doanh
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;Tổng
Để miêu tả được bức tranh tổng thể, tôi nghĩ thế là khá đủ. Nếu ai quan tâm đến chi tiết và kết quả, hãy viết bình luận, tôi sẽ sẵn lòng bổ sung thêm những điểm nhấn cho bức tranh.
PS
Ghi lại một lỗi đơn giản - loại tham số đầu vào
-[ 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""
| }
Nguồn: www.habr.com