El motivo para escribir este estudio fue el artículo . También me pareció interesante un artículo publicado hace 4 años — .
Me pareció interesante que el mismo pensamiento-"implementar la lógica empresarial en la base de datos".
no solo me vino a la mente a mí.
Además, para el futuro me gustaría conservar, sobre todo para mí, los desarrollos interesantes surgidos durante la implementación. Especialmente considerando que, relativamente recientemente, se tomó la decisión estratégica de cambiar la arquitectura y trasladar la lógica empresarial al nivel del backend. Por lo tanto, todo lo que se ha desarrollado pronto no será útil para nadie y no le interesará a nadie.
Los métodos descritos no son un descubrimiento o algo excepcional know how, todo es clásico y se ha 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. Tal vez a alguien le sirva. Como ha demostrado la práctica, frecuentemente la misma idea llega a diferentes personas de manera independiente. Y dejarlo para mí como recuerdo es útil.
Por supuesto, nada en este mundo es perfecto, errores y typos son, lamentablemente, posibles. La crítica y los comentarios son siempre bienvenidos y esperados. Y otro pequeño detalle: se han omitido detalles específicos de implementación. Todo se está utilizando mientras está en un proyecto en funcionamiento. Así que el artículo es solo un estudio y una descripción de la concepción general, no más. Espero que los detalles sean suficientes para comprender el cuadro general.
La idea general es «divide y vencerás, oculta y domina»
La idea es clásica: un esquema separado para tablas, un esquema separado para funciones almacenadas.
El cliente no tiene acceso a los datos directamente. Todo lo que el cliente puede hacer es invocar 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 temáticas.
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 tablas objetivo.
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 de negocio
Funciones para funciones de negocio 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 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;
Rol — USER tiene el 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 BD está listo. Se puede comenzar a llenarlo con datos.
Tablas objetivo
La creación de tablas es trivial. No hay características especiales, salvo que se decidió renunciar al uso de SERIAL y generar secuencias explícitamente. Además, por supuesto, se maximiza el uso de la instrucción
COMMENT ON ...Comentarios para todas objetos, sin excepciones.
Auditoría local
Para registrar la ejecución de funciones almacenadas y cambios en tablas objetivo, 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 objetivo. Son funciones de activación.
Plantilla — función del sistema
---------------------------------------------------------
-- INSERTAR
CREAR O REEMPLAZAR FUNCIÓN sys_functions.table_insert_log ()
DEVUELVE TRIGGER AS $$
COMENZAR
REALIZAR loc_audit_functions.make_log( ' '||'tabla' , 'insertar' , json_build_object('id', NUEVO.id) );
DEVOLVER NULO ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
CREAR TRIGGER table_after_insert DESPUÉS DE INSERTAR EN storage.table POR CADA FILA EJECUTAR PROCEDIMIENTO sys_functions.table_insert_log();
---------------------------------------------------------
-- ACTUALIZAR
CREAR O REEMPLAZAR FUNCIÓN sys_functions.table_update_log ()
DEVUELVE TRIGGER AS $$
COMENZAR
SI ANTIGUO.columna != NUEVO.columna
ENTONCES
REALIZAR loc_audit_functions.make_log( ' '||'tabla' , 'actualizar' , json_build_object('ANTIGUO.columna', ANTIGUO.columna , 'NUEVO.columna', NUEVO.columna ) );
FIN SI ;
DEVOLVER NULO ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
CREAR TRIGGER table_after_update DESPUÉS DE ACTUALIZAR EN storage.table POR CADA FILA EJECUTAR PROCEDIMIENTO sys_functions.table_update_log();
---------------------------------------------------------
-- ELIMINAR
CREAR O REEMPLAZAR FUNCIÓN sys_functions.table_delete_log ()
DEVUELVE TRIGGER AS $$
COMENZAR
REALIZAR loc_audit_functions.make_log( ' '||'tabla' , 'eliminar' , json_build_object('id', ANTIGUO.id ) );
DEVOLVER NULO ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
CREAR TRIGGER table_after_delete DESPUÉS DE ELIMINAR EN storage.table POR CADA FILA EJECUTAR PROCEDIMIENTO sys_functions.table_delete_log ();Funciones de servicio
Están destinadas a implementar operaciones de servicio y DML sobre las tablas objetivo.
Plantilla — función de servicio
--INSERTAR
--DEVOLVER id DE NUEVA FILA
CREAR O REEMPLAZAR FUNCIÓN service_functions.table_insert ( nueva_columna store.table.column%TIPO )
DEVUELVE entero AS $$
DECLARE
nuevo_id entero ;
COMENZAR
-- Generar nuevo id
nuevo_id = nextval('store.table.seq');
-- Insertar en la tabla
INSERTAR EN store.table
(
id ,
columna
)
VALORES
(
nuevo_id ,
nueva_columna
);
DEVOLVER nuevo_id ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
--ELIMINAR
--DEVOLVER NÚMEROS DE FILAS ELIMINADAS
CREAR O REEMPLAZAR FUNCIÓN service_functions.table_delete ( id_actual entero )
DEVUELVE entero AS $$
DECLARE
contador_filas entero ;
COMENZAR
ELIMINAR DE store.table DONDE id = id_actual;
OBTENER DIAGNÓSTICOS contador_filas = ROW_COUNT;
DEVOLVER contador_filas ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;
-- DETALLES DE ACTUALIZACIÓN
-- DEVOLVER NÚMEROS DE FILAS ACTUALIZADAS
CREAR O REEMPLAZAR FUNCIÓN service_functions.table_update_column
(
id_actual entero
,nueva_columna store.table.column%TIPO
)
DEVUELVE entero AS $$
DECLARE
contador_filas entero ;
COMENZAR
ACTUALIZAR store.table
ESTABLECER
columna = nueva_columna
DONDE id = id_actual;
OBTENER DIAGNÓSTICOS contador_filas = ROW_COUNT;
DEVOLVER contador_filas ;
FIN
$$ LENGUAJE plpgsql DEFINIDOR DE SEGURIDAD;Funciones de negocio
Están destinadas a las funciones de negocio finales invocadas por el cliente. Siempre devuelven — JSON. Para interceptar y registrar errores de ejecución, se utiliza el bloque EXCEPCIÓN.
Plantilla — función de negocio
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Parámetros de entrada
)
RETURNS JSON AS $$
DECLARE
------------------------
--para captura de excepciones
error_message text ;
error_json json ;
result 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 PARTE DEL NEGOCIO
--FIN PARTE DEL NEGOCIO
-- RESULTADO EXITOSO
PERFORM business_functions.notice('resultado');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'FINALIZADO',
json_build_object( 'result',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- CAPTURA DE EXCEPCIONES
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'INICIADO',
json_build_object
(
--Parámetros de entrada
) , 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
(
'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 'ALERTA: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Summary
Para describir la imagen general, creo que es suficiente. Si alguien está interesado en los detalles y resultados, deje un comentario, con gusto completaré la imagen con detalles adicionales.
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": "Type1",
| "title": "CREADO POR addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "decricao",
| "lowerTr": "100",
| "measurement": "measurement1",
| "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 no vá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-NOMBRE_DE_COLUMNA
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-NOMBRE_DE_RESTRICCIÓN
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-NOMBRE_DE_TIPO_PG
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-TEXTO_DE_MENSAJE
jsonb_pretty | {
| "MESSAGE_TEXT": "sintaxis de entrada no vá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-NOMBRE_DE_ESQUEMA
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-DETALLE_DE_EXCEPCIÓN_PG
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-SUGERENCIA_DE_EXCEPCIÓN_PG
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-CONTEXTO_DE_EXCEPCIÓN_PG
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERROR-TEXTO_DE_MENSAJE
jsonb_pretty | {
| "MESSAGE_TEXT": "sintaxis de entrada no válida para tipo numérico: "null""
| }Fuente: habr.com
