แรงผลักดันในการเขียนภาพร่างนี้คือบทความ . ฉันยังพบว่าบทความที่ตีพิมพ์เมื่อ 4 ปีที่แล้วน่าสนใจ - .
น่าสนใจเหมือนกันที่คิดแบบนั้น - "ใช้ตรรกะทางธุรกิจในฐานข้อมูล".
ไม่ใช่แค่ฉันเท่านั้นที่นึกถึง
นอกจากนี้ ในอนาคต ฉันอยากจะรักษาการพัฒนาที่น่าสนใจที่เกิดขึ้นระหว่างการดำเนินการก่อนอื่นเพื่อตัวฉันเอง โดยเฉพาะอย่างยิ่งเมื่อพิจารณาว่าเมื่อเร็วๆ นี้ มีการตัดสินใจเชิงกลยุทธ์เพื่อเปลี่ยนสถาปัตยกรรมและโอนตรรกะทางธุรกิจไปยังระดับแบ็กเอนด์ เพื่อว่าทุกสิ่งที่ได้รับการพัฒนาจะไม่มีประโยชน์กับใครเลยในไม่ช้าและจะไม่เป็นประโยชน์กับใครเลย
วิธีการที่อธิบายไว้ไม่ใช่การค้นพบหรือวิธีพิเศษใดๆ ทราบว่าทุกอย่างเป็นแบบคลาสสิกและมีการใช้งานหลายครั้ง (เช่น ฉันใช้วิธีการที่คล้ายกันเมื่อ 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""
| }
ที่มา: will.com