El motivo para escribir este estudio fue un artículo . También me pareció interesante un artículo publicado hace 4 años — .
Me pareció interesante que la misma idea-"implementar la lógica de negocio en la base de datos".
no se me ocurrió solo a mí.
Además, quería conservar, para mí principalmente, las ideas interesantes que surgieron durante la implementación. Especialmente teniendo en cuenta que recientemente se tomó la decisión estratégica de cambiar la arquitectura y trasladar la lógica de negocio al nivel del backend. Así que todo lo que se ha desarrollado pronto no será necesario para nadie y no interesará a nadie.
Los métodos descritos no son un descubrimiento ni algo excepcional know how, todo es clásico y ha sido implementado en múltiples ocasiones (yo, por ejemplo, apliqué un enfoque similar hace 20 años en Oracle). Simplemente decidí reunir todo en un solo lugar. Quizás le sirva a alguien. Como ha demostrado la práctica, a menudo la misma idea se le ocurre a diferentes personas de manera independiente. Y también, para dejar un recuerdo, es útil.
Por supuesto, nada en este mundo es perfecto, los errores y las erratas son posibles. Se agradecen y esperan críticas y comentarios. Y un pequeño detalle más: se han omitido detalles específicos de la implementación. Después de todo, todo se utiliza aún en un proyecto en funcionamiento. Así que el artículo es solo un estudio y una descripción del concepto general, no más que eso. Espero que para entender la imagen general, suficientes detalles.
La idea general es «divide y vencerás, oculta y poseerás»
La idea es clásica: un esquema separado para tablas, un esquema separado para funciones almacenadas.
El cliente no tiene acceso directo a los datos. Todo lo que el cliente puede hacer es llamar a una función almacenada y procesar la respuesta recibida.
Roles
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Esquemas
Esquema de almacenamiento de tablas
Tablas objetivo que implementan entidades del dominio.
CREATE SCHEMA store AUTHORIZATION store;
Esquema de funciones del sistema
Funciones del sistema, en particular para registrar cambios en las tablas.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;
Esquema de auditoría local
Funciones y tablas para implementar la auditoría local de la ejecución de funciones almacenadas y cambios en las tablas de destino.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Esquema de funciones de servicio
Funciones para funciones de servicio y DML.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Esquema de funciones comerciales
Funciones para las funciones comerciales finales llamadas por el cliente.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Accesos
Rol — DBA tiene acceso completo a todos los esquemas (separado del rol de propietario de la base de datos).
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;
Rol — USER tiene privilegio El resultado fue una solución bastante universal que se podía trasladar a todas las tablas: en el esquema business_functions.
CREATE ROLE user_role;
Privilegios entre esquemas
GRANT
Dado que todas las funciones se crean con el atributo SECURITY DEFINER se requiere la instrucción REVOKE EXECUTE ON ALL FUNCTION… FROM public;
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;
Así que el esquema de la base de datos está listo. Se puede proceder a llenar los datos.
Tablas de destino
La creación de tablas es trivial. Sin características especiales, excepto que se decidió prescindir del uso de SERIAL y generar secuencias explícitamente. Además, por supuesto, el máximo uso de la instrucción
COMMENT ON ...Comentarios para todas objetos, sin excepciones.
Auditoría local
Para llevar un registro de la ejecución de funciones almacenadas y cambios en las tablas de destino, se utiliza una tabla de auditoría local que incluye detalles de la conexión del cliente, la etiqueta del módulo llamado, y los valores reales de los parámetros de entrada y salida en formato JSON.
Funciones del sistema
Destinadas a registrar cambios en las tablas de destino. Son funciones de disparador.
Plantilla — función del sistema
---------------------------------------------------------
-- INSERTAR
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'tabla' , 'insertar' , 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();
---------------------------------------------------------
-- ACTUALIZAR
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( ' '||'tabla' , 'actualizar' , 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 ();
---------------------------------------------------------
-- ELIMINAR
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'tabla' , 'eliminar' , 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 ();Funciones de servicio
Destinadas a la implementación de operaciones de servicio y DML en las tablas objetivo.
Plantilla — función de servicio
--INSERTAR
--DEVOLVER id DE LA NUEVA FILA
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Generar nuevo id
new_id = nextval('store.table.seq');
-- Insertar en la tabla
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--ELIMINAR
--DEVOLVER NÚMEROS DE FILAS ELIMINADAS
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;
-- ACTUALIZAR DETALLES
-- DEVOLVER NÚMEROS DE FILAS ACTUALIZADAS
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;Funciones de negocio
Destinadas a las funciones de negocio finales invocadas por el cliente. Siempre devuelven — JSON. Para interceptar y registrar errores de ejecución, se utiliza un bloque EXCEPCIÓN.
Plantilla — función de negocio
CREAR O REEMPLAZAR FUNCIÓN business_functions.business_function_template(
-- Parámetros de entrada
)
RETORNA JSON AS $$
DECLARE
------------------------
-- para captura de excepciones
error_message texto ;
error_json json ;
resultado json ;
------------------------
BEGIN
--REGISTRO
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'INICIADO',
json_build_object
(
-- Parámetros de entrada
)
);
PERFORM business_functions.notice('business_function_template');
-- INICIO DE LA PARTE DEL NEGOCIO
-- FIN DE LA PARTE DEL NEGOCIO
-- RESULTADO EXITOSO
PERFORM business_functions.notice('resultado');
PERFORM business_functions.notice(resultado);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'FINALIZADO',
json_build_object( 'resultado',resultado )
);
RETURN resultado ;
----------------------------------------------------------------------------------------------------------
-- CAPTURA DE EXCEPCIONES
EXCEPCIÓN
CUANDO OTROS ENTONCES
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'INICIADO',
json_build_object
(
-- Parámetros de entrada
) , VERDADERO );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLSTATE',SQLSTATE ), VERDADERO
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLERRM',SQLERRM ), VERDADERO
);
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 ), VERDADERO );
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 ), VERDADERO );
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 ), VERDADERO );
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 ), VERDADERO );
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 ), VERDADERO );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-SCHEMA_NAME',json_build_object('SCHEMA_NAME',error_message ), VERDADERO );
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 ), VERDADERO );
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 ), VERDADERO );
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 ), VERDADERO );
RAISE WARNING 'ALARMA: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , VERDADERO ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LENGUAJE plpgsql DEFINidor DE SEGURIDAD;Summary
Para describir el panorama general, creo que es más que suficiente. Si alguien está interesado en los detalles y los resultados, escriba comentarios, con gusto añadiré toques adicionales al cuadro.
P.D.
Registro de un error simple: tipo de parámetro de entrada
-[ REGISTRO 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | INICIADO
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Tipo1",
| "title": "CREADO POR addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "VERDADERO",
| "description": "descripción",
| "lowerTr": "100",
| "measurement": "medida1",
| "methodology": "m1",
| "passportUrl": "files",
| "upperTr": "200",
| "weightingFactor": 100.123,
| "actualTrValue": null,
| "upperTrCalcNumber": "120"
| },
| "CardId": 3
| }
-[ REGISTRO 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"
| }
-[ REGISTRO 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": "sintaxis de entrada inválida para tipo numérico: "null""
| }
-[ REGISTRO 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"
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": "sintaxis de entrada inválida para tipo numérico: "null""
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": ""
| }
-[ REGISTRO 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": "sintaxis de entrada inválida para tipo numérico: "null""
| }Fuente: habr.com
