Энэхүү тоймыг бичихэд түлхэц болсон зүйл бол нийтлэл байв . 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;
Схемийн хоорондох давуу эрх
ТУСГАЛ
Бүх функцууд нь атрибутаар үүсгэгддэг тул АЮУЛГҮЙ БАЙДЛЫГ ТОДОРХОЙЛОГЧ заавар шаардлагатай БҮХ ҮЙЛ АЖИЛЛАГААНЫ ГҮЙЦЭТГЭЛИЙГ Цуцлах... олон нийтээс;
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""
| }
Эх сурвалж: www.habr.com