SQL, déjalo manejar las transacciones y la calidad de datos
SQL Server
Actualizado el 10 de Julio del 2019 por Gnomi (Publicado el 8 de Julio del 2019)
1.513 visualizaciones desde el 8 de Julio del 2019
Articulo original en: https://www.gnomi.club/sql-desarrollo/
¿Desarrollando aplicaciones de bases de datos con motores SQL?, deja que el motor de base de datos se encargue de muchas de los controles que usualmente cargas al código de tu aplicación. Integridad referencial, Restricciones, Indices, Procedimientos Almacenados, Triggers, son algunas de las herramientas que te ayudaran a tener una base de datos sólida y una aplicación simple.
Structured Query Language, un lenguaje informático que ha alcanzado el nivel de Estándar ANSI, utilizado para Sistemas de Bases de Datos Relacionales. Consta de:
Su alcance se enfoca en la inserción, consulta, modificación y borrado de información, la creación y modificación de esquemas y el control de acceso a los datos.
Importante saber que SQL se fundamenta en los principios del Algebra Relacional, la cual presenta 2 tipos de operadores:
Operadores básicos o primitivos.
Operadores no básicos o derivados.
Así que si te preguntas si es necesario llevar Matemáticas, Cálculos, Algebras, etc. en tus planes de estudio, aquí tienes la respuesta a tus interrogantes.
Aplicaciones de bases de datos
Una aplicación de base de datos, típicamente tiene dos componentes, por un lado, una base de datos donde se almacena la información, y otra, una aplicación encargada del mantenimiento y consulta de esa información.
Una de los requerimientos mas importantes en este tipo de aplicaciones, es que la información almacenada sea correcta, consistente y libre de errores. Controles como validar que una fecha no sea mayor que otra, que un numero no sea negativo, que un campo obligatoriamente tenga un valor definido, son algunas de las validaciones que todo desarrollador debe aplicar a la hora de programar la aplicación.
Dependiendo del tipo de empresa para la que se trabaje, o bien del equipo de trabajo con que se cuente, habrán situaciones donde tanto la creación y accesos a la base de datos estarán a cargo de un grupo especializado y habrán situaciones donde se tiene control total. Si el control de la base de datos esta fuera de tu alcance, lo que hay que hacer es saber que pedir y solicitarlo en concordancia.
Al momento de programar los formularios de Insertar, Modificar o Eliminar datos, los programadores incluyen en su código todos los controles necesarios para que en tiempo de ejecución del formulario, se valide la calidad de información que ira a la base de datos.
En realidad esto no es necesario, se puede entregar todo ese control a la base de datos misma, y con ello simplificar el código al lado de los formularios de mantenimiento de la información.
Explota el poder del motor de SQL, y programa en cada base de datos lo necesario de manera que el desarrollo de tus aplicaciones se simplifique. Haz que la misma base de datos se proteja de información inconsistente o inválida.
Si programas el control de la calidad de datos en la misma base de datos, asegurarás que cualquier aplicación que interactúe con ella, con o sin controles en tiempo de ejecución, no dañe la información almacenada.
Integridad Relacional
No te limites únicamente a crear tablas y columnas. Haz la tarea. Crea las llaves primarias, indices únicos, indices adicionales y relaciona esas tablas y establece el tipo de relación a utilizar en cada caso.
Vistas
Las vistas son consultas a una o mas tablas relacionadas guardadas en la misma base de datos. Por ejemplo, una consulta que relacione la tabla de Encabezados de Factura, con la tabla Detalles de Facturas, podría guardarse en la base de datos como una vista y a partir de allí, se llama a la vista desde la aplicación y se deja de construir la consulta completa en el código.
Al no ser tablas como tales, no almacenan datos, por lo tanto las vistas prácticamente no consumen espacio en disco duro. Es por esto que se pueden construir tantas vistas como sea necesario, sin temor a reducir los recursos del servidor.
Deja los JOIN, del lado de la base de datos!!! y que sea el motor de base de datos quien haga el esfuerzo, no tu aplicación!
Restricciones
¿Necesitas validar que un dato sea mayor a cero? ¿o que una fecha sea mayor o igual a la fecha actual? ¿o que un texto no contenga algún carácter en específico? Utiliza las restricciones de chequeo y elimina ese código de tu aplicación:
Triggers
Los triggers te permitirán activar tareas al momento de insertar, actualizar o eliminar datos. Por ejemplo, se podria dar el caso en una base de datos de Recursos Humanos, que exija que cada vez que un empleado nuevo es creado en la tabla de empleados, automaticamente se cree un registro relacionado en las tablas de beneficios y de planillas:
Los triggers también pueden ser utilizados para validar información. Por ejemplo, antes de insertar un registro, se puede validar que el campo X contenga datos y que obedezca un formato especifico, por ejemplo una direccion de correo electronico; si no cumple las condiciones programadas, rechaza el registro y devuelve un error.
Procedimientos almacenados
Los procedimientos almacenados son pequeños programas que se guardan en la misma base de datos y que son capaces de recibir parámetros y devolver resultados.
En este apartado especifico, recomendamos para cada base de datos contar un procedimiento almacenado para Consultar, otro para Insertar, otro para Modificar y otro para Eliminar datos de una tabla especificada.
Esto permitirá entregar a la base de datos las tareas transaccionales, liberando al código en la aplicación de esa función. Usualmente lo que se hace es:
Con este método, tanto la instrucción, como el nombre de la base de datos, el nombre de la tabla, el nombre de los campos y el valor de esos campos, viajan a través de la red, quedando a expensas de ser capturados por cualquier hacker que ande por ahí...
La instrucción resulta muy obvia y cualquiera que capture esta cadena sabrá que acción es, que tabla afecta, cuales campos son actualizados y con que valores. A partir de allí podrá hacer cualquier cosa. Claro, esto en un escenario de hackeo de información.
Además quien lleva la carga del procesamiento es la aplicación.
Teniendo en la base de datos los procedimientos almacenados para las transacciones, el método varia:
en este caso, la instrucción ya no es tan obvia... ademas, quien lleva el procesamiento es el servidor, y no la aplicación cliente.
Tomando como referencia Microsoft SQL Server, mira a continuación los siguientes procedimientos almacenados:
Procedimiento Almacenado para Consultar
Este procedimiento recibe como parametros el nombre de tabla o vista a consultar, las columnas, la definicion de agrupacion, la definicion de filtro y el orden deseado.
En su ejecucion, construye la instruccion SQL y ejecuta, devolviendo el set de datos requeridos.
Solo reemplaza lo que esta entre comillas por los nombres correspondientes.
Procedimiento Almacenado para insertar registros
Este procedimiento es para insertar registros en la tabla especificada. Recibe como parámetros el nombre de tabla, el nombre de las columnas y los correspondientes valores. Si la inserción de registro es rechazada por la base de datos, el procedimiento recoge el error y lo regresa a la aplicación. Si el registro es aceptado, regresa un 0 (cero).
Procedimiento Almacenado para modificar registros
Recibe como parámetros, el nombre de la tabla a modificar, los campos a afectar y el valor de dichos campos. Retorna un 0 (cero), si la modificación es aceptada, o bien retorna el error correspondiente.
Procedimiento para eliminar registros
Para su uso se proveen como parametros, la tabla a ser afectada y la condicion de filtro de los registros deseados.
Igualmente regresa un cero si la eliminación fue exitosa, o bien el mensaje de error correspondiente.
Como puedes observar, ninguno de estos procedimientos se preocupa por la calidad de datos, o por la existencia de relaciones entre tablas, o restricciones que puedan haber. Cada procedimiento hace lo que debe, intenta ejecutar la transacción, si no lo puede, captura el error y lo presenta a la aplicación que lo llama.
En resumen...
Mezclando todos estos conceptos, podrás programar todo el control de transacciones y calidad de información, a la base de datos y alivianar el código del lado de tu aplicación. En tu aplicación prácticamente lo que necesitarás es realizar la conexión, poder ejecutar los procedimientos y poder desplegar en pantalla los posibles mensajes recibidos de la base de datos.
Esta técnica permite programaciones mas avanzadas, como por ejemplo el Desarrollo de Aplicaciones conducidas por Datos
Comparte esta técnica en tus redes sociales, abajo encontrarás los botones que te facilitarán la acción. Igual puedes comentar y aportar tus observaciones!!!
Bendiciones!!!
¿Desarrollando aplicaciones de bases de datos con motores SQL?, deja que el motor de base de datos se encargue de muchas de los controles que usualmente cargas al código de tu aplicación. Integridad referencial, Restricciones, Indices, Procedimientos Almacenados, Triggers, son algunas de las herramientas que te ayudaran a tener una base de datos sólida y una aplicación simple.
SQL mas allá de un lenguaje de consultas
Structured Query Language, un lenguaje informático que ha alcanzado el nivel de Estándar ANSI, utilizado para Sistemas de Bases de Datos Relacionales. Consta de:
- Lenguaje de definición de datos
- Lenguaje de manipulación de datos
- Lenguaje de control de datos
- Lenguaje de manipulación de datos
- Lenguaje de control de datos
Su alcance se enfoca en la inserción, consulta, modificación y borrado de información, la creación y modificación de esquemas y el control de acceso a los datos.
Importante saber que SQL se fundamenta en los principios del Algebra Relacional, la cual presenta 2 tipos de operadores:
Operadores básicos o primitivos.
- Proyección (π).
- Selección (σ).
- Unión (U).
- Diferencia (-).
- Producto cartesiano (X).
- Selección (σ).
- Unión (U).
- Diferencia (-).
- Producto cartesiano (X).
Operadores no básicos o derivados.
- Intersección (∩).
- Unión natural ().
- División (/).
- Unión natural ().
- División (/).
Así que si te preguntas si es necesario llevar Matemáticas, Cálculos, Algebras, etc. en tus planes de estudio, aquí tienes la respuesta a tus interrogantes.
Aplicaciones de bases de datos
Una aplicación de base de datos, típicamente tiene dos componentes, por un lado, una base de datos donde se almacena la información, y otra, una aplicación encargada del mantenimiento y consulta de esa información.
Aplicacion Base de Datos SQL
Una de los requerimientos mas importantes en este tipo de aplicaciones, es que la información almacenada sea correcta, consistente y libre de errores. Controles como validar que una fecha no sea mayor que otra, que un numero no sea negativo, que un campo obligatoriamente tenga un valor definido, son algunas de las validaciones que todo desarrollador debe aplicar a la hora de programar la aplicación.
Dependiendo del tipo de empresa para la que se trabaje, o bien del equipo de trabajo con que se cuente, habrán situaciones donde tanto la creación y accesos a la base de datos estarán a cargo de un grupo especializado y habrán situaciones donde se tiene control total. Si el control de la base de datos esta fuera de tu alcance, lo que hay que hacer es saber que pedir y solicitarlo en concordancia.
Al momento de programar los formularios de Insertar, Modificar o Eliminar datos, los programadores incluyen en su código todos los controles necesarios para que en tiempo de ejecución del formulario, se valide la calidad de información que ira a la base de datos.
En realidad esto no es necesario, se puede entregar todo ese control a la base de datos misma, y con ello simplificar el código al lado de los formularios de mantenimiento de la información.
Programa la base de datos
Explota el poder del motor de SQL, y programa en cada base de datos lo necesario de manera que el desarrollo de tus aplicaciones se simplifique. Haz que la misma base de datos se proteja de información inconsistente o inválida.
Si programas el control de la calidad de datos en la misma base de datos, asegurarás que cualquier aplicación que interactúe con ella, con o sin controles en tiempo de ejecución, no dañe la información almacenada.
Integridad Relacional
No te limites únicamente a crear tablas y columnas. Haz la tarea. Crea las llaves primarias, indices únicos, indices adicionales y relaciona esas tablas y establece el tipo de relación a utilizar en cada caso.
Vistas
Las vistas son consultas a una o mas tablas relacionadas guardadas en la misma base de datos. Por ejemplo, una consulta que relacione la tabla de Encabezados de Factura, con la tabla Detalles de Facturas, podría guardarse en la base de datos como una vista y a partir de allí, se llama a la vista desde la aplicación y se deja de construir la consulta completa en el código.
Al no ser tablas como tales, no almacenan datos, por lo tanto las vistas prácticamente no consumen espacio en disco duro. Es por esto que se pueden construir tantas vistas como sea necesario, sin temor a reducir los recursos del servidor.
Deja los JOIN, del lado de la base de datos!!! y que sea el motor de base de datos quien haga el esfuerzo, no tu aplicación!
Restricciones
¿Necesitas validar que un dato sea mayor a cero? ¿o que una fecha sea mayor o igual a la fecha actual? ¿o que un texto no contenga algún carácter en específico? Utiliza las restricciones de chequeo y elimina ese código de tu aplicación:
Triggers
Los triggers te permitirán activar tareas al momento de insertar, actualizar o eliminar datos. Por ejemplo, se podria dar el caso en una base de datos de Recursos Humanos, que exija que cada vez que un empleado nuevo es creado en la tabla de empleados, automaticamente se cree un registro relacionado en las tablas de beneficios y de planillas:
Los triggers también pueden ser utilizados para validar información. Por ejemplo, antes de insertar un registro, se puede validar que el campo X contenga datos y que obedezca un formato especifico, por ejemplo una direccion de correo electronico; si no cumple las condiciones programadas, rechaza el registro y devuelve un error.
Procedimientos almacenados
Los procedimientos almacenados son pequeños programas que se guardan en la misma base de datos y que son capaces de recibir parámetros y devolver resultados.
En este apartado especifico, recomendamos para cada base de datos contar un procedimiento almacenado para Consultar, otro para Insertar, otro para Modificar y otro para Eliminar datos de una tabla especificada.
Esto permitirá entregar a la base de datos las tareas transaccionales, liberando al código en la aplicación de esa función. Usualmente lo que se hace es:
- Establecer una conexión a la base de datos
- Construir una instrucción Select, Insert, Update o Delete, en la aplicación basado en los datos ingresados en el formulario.
- Ejecutar la instrucción, y esperar la respuesta del motor de base de datos
- Mostrar el resultado al usuario.
- Construir una instrucción Select, Insert, Update o Delete, en la aplicación basado en los datos ingresados en el formulario.
- Ejecutar la instrucción, y esperar la respuesta del motor de base de datos
- Mostrar el resultado al usuario.
Con este método, tanto la instrucción, como el nombre de la base de datos, el nombre de la tabla, el nombre de los campos y el valor de esos campos, viajan a través de la red, quedando a expensas de ser capturados por cualquier hacker que ande por ahí...
1
insert into employees (nombre,salario) values ("XXXX",150000)
La instrucción resulta muy obvia y cualquiera que capture esta cadena sabrá que acción es, que tabla afecta, cuales campos son actualizados y con que valores. A partir de allí podrá hacer cualquier cosa. Claro, esto en un escenario de hackeo de información.
Además quien lleva la carga del procesamiento es la aplicación.
Teniendo en la base de datos los procedimientos almacenados para las transacciones, el método varia:
- Establecer una conexión a la base de datos
- Indicar a la base de datos que ejecute el procedimiento almacenado respectivo, con los parámetros del caso.
- Esperar y mostrar el resultado.
- Indicar a la base de datos que ejecute el procedimiento almacenado respectivo, con los parámetros del caso.
- Esperar y mostrar el resultado.
1
execute spins('employees','nombre,salario','"XXXX",150000')
en este caso, la instrucción ya no es tan obvia... ademas, quien lleva el procesamiento es el servidor, y no la aplicación cliente.
Tomando como referencia Microsoft SQL Server, mira a continuación los siguientes procedimientos almacenados:
Procedimiento Almacenado para Consultar
Este procedimiento recibe como parametros el nombre de tabla o vista a consultar, las columnas, la definicion de agrupacion, la definicion de filtro y el orden deseado.
En su ejecucion, construye la instruccion SQL y ejecuta, devolviendo el set de datos requeridos.
Solo reemplaza lo que esta entre comillas por los nombres correspondientes.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE PROCEDURE [dbo].[spselect]
@tabla varchar(max),
@columnas varchar(max),
@grupo varchar(max),
@filtro varchar(max),
@orden varchar(max)
AS
begin
declare @consul varchar(max)
if len(ltrim(rtrim(@tabla)))=0
begin
set @consul= 'Select ' + @columnas + @grupo + @filtro + @orden
end
else
begin
set @consul= 'Select ' + @columnas +' from ' + @tabla + @grupo + @filtro + @orden
end
--select @consul
execute (@consul)
return
end
Procedimiento Almacenado para insertar registros
Este procedimiento es para insertar registros en la tabla especificada. Recibe como parámetros el nombre de tabla, el nombre de las columnas y los correspondientes valores. Si la inserción de registro es rechazada por la base de datos, el procedimiento recoge el error y lo regresa a la aplicación. Si el registro es aceptado, regresa un 0 (cero).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
create PROCEDURE [dbo].[spinsert]
@tabla varchar(50),
@columnas varchar(8000),
@valores varchar(8000)
AS
BEGIN
SET NOCOUNT ON;
declare @orden varchar(8000);
declare @return_value int;
declare @return_msg varchar(500);
declare @lastid int;
set @valores=ltrim(rtrim(@valores));
begin try
set @orden='Insert into ' + @tabla + ' (' + @columnas + ') values (' + @valores + ')';
set @return_value=0;set @lastid=IDENT_CURRENT(@tabla);
execute (@orden);
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @return_value = ERROR_NUMBER();
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@return_msg = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);end catch
return @return_value;
END
Procedimiento Almacenado para modificar registros
Recibe como parámetros, el nombre de la tabla a modificar, los campos a afectar y el valor de dichos campos. Retorna un 0 (cero), si la modificación es aceptada, o bien retorna el error correspondiente.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create PROCEDURE [dbo].[spmodify]
@tabla varchar(50),
@stmt varchar(8000),
@filtro varchar (8000)
AS
BEGIN
SET NOCOUNT ON;
declare @orden varchar(8000)
declare @return_value int
begin try
set @orden='update ' + @tabla + ' set ' + @stmt + ' ' +@filtro
set @return_value=0;execute (@orden)
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @return_value = ERROR_NUMBER()
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);end catch
return @return_value
END
Procedimiento para eliminar registros
Para su uso se proveen como parametros, la tabla a ser afectada y la condicion de filtro de los registros deseados.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create PROCEDURE [dbo].[spdelete]
@tabla varchar(50),
@filtro varchar(max)
AS
BEGIN
SET NOCOUNT ON;
declare @eliminar varchar(max);
declare @return_value int;
begin try
set @eliminar='delete from ' + @tabla +' '+ @filtro;
set @return_value=0;execute (@eliminar);
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @return_value = ERROR_NUMBER();
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);end catch
return @return_value;
END
Igualmente regresa un cero si la eliminación fue exitosa, o bien el mensaje de error correspondiente.
Como puedes observar, ninguno de estos procedimientos se preocupa por la calidad de datos, o por la existencia de relaciones entre tablas, o restricciones que puedan haber. Cada procedimiento hace lo que debe, intenta ejecutar la transacción, si no lo puede, captura el error y lo presenta a la aplicación que lo llama.
En resumen...
Mezclando todos estos conceptos, podrás programar todo el control de transacciones y calidad de información, a la base de datos y alivianar el código del lado de tu aplicación. En tu aplicación prácticamente lo que necesitarás es realizar la conexión, poder ejecutar los procedimientos y poder desplegar en pantalla los posibles mensajes recibidos de la base de datos.
Esta técnica permite programaciones mas avanzadas, como por ejemplo el Desarrollo de Aplicaciones conducidas por Datos
Comparte esta técnica en tus redes sociales, abajo encontrarás los botones que te facilitarán la acción. Igual puedes comentar y aportar tus observaciones!!!
Bendiciones!!!
Comentarios... (1)