αααααΆαααα»αα
αααΆααααααΆααααΆααααααααααΌαααααΆααααααΊα’ααααα
ααΆα αΆααααΌα ααΆαα½αα±ααα αΆααα’αΆααααααααααααα·αααΌα ααααΆ - "α’αα»αααααααααα·ααααΆα’αΆααΈααααααα αααα»αααΌαααααΆααα·αααααα".
ααΆβαα·αβαααβααΆαβααβαααα»αβααβαααβααΆαβαα·αα
ααΌα ααααΆαααααααααααααΆαααααα’ααΆαα αααα»αα ααα’αα·αααα ααΆααααΌααααααΆαααααα½ααααα»αααααΆαα ααΆαα’αα·αααααααα½αα±ααα αΆααα’αΆαααααααααααΆαααΎαα‘αΎααααα»αα’αα‘α»ααααα’αα»ααααα ααΆαα·αααααααα·α αΆαααΆααΆααααΈααααααΆααααααα α α·αααααΆαα»αααααΆαααααααααΌαααΆαααααΎα‘αΎαααΎααααΈααααΆααααααΌαααααΆαααααααααα·αααααααααααα·ααααΆα’αΆααΈααααααα ααααα·αααΆααααααα ααΌα αααα α’αααΈαβαααβααααΌαβααΆαβαααααΎαβα‘αΎαβααΉαβαα·αβααΆαβααααααααβαααααΆααβα’αααβααΆβααααΆααβα‘αΎα α αΎαβααΉαβαα·αβααΆαβααααααααβαααααΆααβα’αααβααΆβααααΆααβα‘αΎαα
αα·ααΈααΆααααααααααΆααα·αααααΆαα·ααααααΆααααααααααΆαααααΎα α¬αα·ααααααααα ααΉαααΈααααα’αααΈααααααααΆαααΊαα»ααΆα α αΎαααααΌαααΆαα’αα»ααααααΆα αααΎααα (α§ααΆα ααα αααα»αααΆαααααΎαα·ααΈααΆαααααααααααααααΆααΆαααΈ 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;
αα·αααα·ααα½αααΆα
αα½ααΆααΈ - ααΆααΆ ααΆααα·αααα·α αΌαααααΎααααααα αααααααααααΆαααααΆααα’αα (αααααα ααααΈαα½ααΆααΈααα αΆαα 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;
αα½ααΆααΈ - α’αααβααααΎ ααΆαα―ααα·αααα· α αα αα αααα»αααααΆααααΆα business_functions.
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