DBExpress - Many to Many (Relaciones muchos a muchos) Parte 1

Así como en la entrada http://pablosoligo.blogspot.com/2011/07/dbexpressmaster-detail.html se presento el tema de las relaciones master-details aparece ahora el interrogante de si podemos tambien manejar las relaciones muchos a muchos de una manera similar.
Yo digo que si, lo he probado, podemos manejar casi de manera completamente automática las relaciones muchos a muchos, lo único que haremos es ayudarnos con comandos sql para producir el resultado esperado.
Para empezar pensemos una situación de relación muchos a muchos, propongo la siguiente, didáctica y bien sencilla

Creo se sobreentiende un usuario tiene muchos permisos y un permiso puede estar en muchos usuarios.

Para plantear esto como un maestro detalle o mejor dicho como un maestro y doble detalle pongamos a cada elemento en su posición

Usuario->Maestro

Permisos asignados->Detalle 1

Permisos no asignados->Detalle 2

¿Como quedan las consultas?

sqlUsuario

select *

from Usuario

sqlPermisosAsignados

select *

from UsuarioPermiso

Where Login=:Login

sqlPermisosDisponibles

select *

from Permiso as p

Where p.IdPermiso not in

(Select IdPermiso

from UsuarioPermiso as up

Where up.Login=:Login)

Bueno ahí esta la primera aproximación, luego veremos si es suficiente o tenemos que mejorar algún aspecto,

sqlUsuario no merece explicación, sqlPermisoAsignados casi que tampoco, son los permisos que le usuario ya tiene asignado y podemos considerarlo como un detalle del usuario, sqlPermisosDisponibles son todos los permisos que no tiene el usuario tema resuelto con un "Not In" el lector puede usar un Exists si prefiere y también lo podemos considerar un detalle del usuario, de hecho como esta planteado el único parámetros de las consultas a las que consideramos detalle es el login del usuario, dato presente en el maestro, condición necesaria y suficiente para armar nuestros master-details en DBExpress

Así quedaría la cosa, recordar enlazar los dataset detalle por su propiedad DataSource al DataSource llamado en este caso "datUsuario"

Llegado este punto terminamos con el servidor, llega la hora del cliente.

Siguiente lo mostrado en la entrada http://pablosoligo.blogspot.com/2011/07/dbexpressmaster-detail.html la cosa quedaría así, tanto los permisos disponibles como los permisos asignados son detalles del usuario

Relaciono las grillas los datasources etc, pongo algunos datos y abro solo el clientedataset maestro al abrir la ventana

procedure TFrmUsuario.FormCreate(Sender: TObject);

begin

  cdsUsuario.Open;

end;

y lo pruebo a ver que tal funciona.

Perfecto, me muevo entre usuarios y sus permisos disponibles y asignados se actualizan automáticamente.

Claro, la critica acá puede ser de que debo mostrar las descripciones de los permisos en permisos asignados, es un tema que dejamos para mas adelante ya que no hace a la diferencia en cuanto a programación, lo importante es entender la idea. Con esto tenemos el 70% del problema resuelto y solo escribimos una linea de código.

En la próxima entrada vemos la actualización de los permisos con este mismo esquema.

Master-Details y Autoincrementales

Ahora si, este tema no puedo decir que no esta documentado, he encontrado tutoriales y papers que describen el problema, eso si, siempre en Ingles y tampoco son tantos los lugares donde uno pueda encontrar una solución a este asunto.
Como vimos en la entrada

http://pablosoligo.blogspot.com/2011/07/dbexpressmaster-detail.html

DBExpress maneja muy bien la idea de maestro detalle, lo maneja como un elemento único simplificando el desarrollo, es mas, solo se encarga de llenar el campo de relación en el detalle y lo llena correctamente con el identificado del maestro.

Rapidamente aparece un problema con los autoincrementales, el valor clave del maestro lo pone la base, cuando dbexpress intenta insertar los registros detalles no sabe la clave del maestro, de hecho el problema aparece antes ¿Que clave lleva el detalle en el ClientDataSet cuando siquiera se la clave que va a tener el maestro?

Primero ataquemos el problema que acabamos de mencionar, voy a cambiar la base de datos de ejemplo vista en las entradas anteriores para indicar que ahora el idPedido es autoincremental, en una oracle sera colocar un trigger, en Interbase un generator o como sea según motor, la idea es que el valor lo pone la base y no yo.

Como ven, el clientDataSet(Maestro) tiene un evento que se ejecuta cuando hay un nuevo registro, tomemos esto para colocar un IdPedido temporal o comodín si así lo quieren llamar. Luego los detalles se generaran con este IdPedido comodín.

procedure TFrmPedido.cdsPedidoNewRecord(DataSet: TDataSet);

begin

  DataSet.FieldByName('IdPedido').Value := -1;

end;

Esto es una simplificación, si yo intentara ingresar mas registros maestros fallaría porque crearía dos registros con IdPedido=-1, solución y truco para esto.


procedure TFrmPedido.cdsPedidoNewRecord(DataSet: TDataSet);
begin
  DataSet.FieldByName('IdPedido').Value := DataSet.FieldByName('IdPedido').Tag - 1;
  DataSet.FieldByName('IdPedido').Tag := DataSet.FieldByName('IdPedido').Value;
end;

Ayudándonos con la propiedad tag y sin declarar mas variables hacemos que nuestro IdPedido comodín se incremente hacia el negativo. Vamos a usar es breve este valor comodín.

Ahora el siguiente problema, si el valor lo IdPedido lo pone la base, entonces no lo pongo yo.

¿Como le digo a DBExpress insertame todos los campos menos este que lo hace la base de datos?

La respuesta es simple, del lado servidor (Suponiendo trabajo con DataSnap) buscamos el DataSet, seleccionamos en campo y en providerflags ponemos el pfInUpdate en false, con esto cuando se genere el insert DBExpress dejara fuera a este campo. La figura muestra como hacerlo.

En esta entrada http://pablosoligo.blogspot.com/2011/07/logica-del-negocio.html  se demuestra que puedo tener injerencia en los datos justo antes de ser insertados efectivamente, ¿Como usar esto a mi favor y poner en orden el tema de las claves en este maestro-detalle?

El SqlServer se encargara de poner el valor de IdPedido al maestro, es mi responsabilidad ponerlos en el detalle. Como vimos en la entrada anterior puedo meterme justo antes de que se inserte el registro e interferir en la operación, veamos como:

procedure TSMPedido.dspPedidoBeforeUpdateRecord(Sender: TObject;

  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;

  var Applied: Boolean);

begin

  inherited;

  if (UpdateKind=ukInsert) and (SourceDS=sqlPedido) then begin

    DeltaDs.FieldByName('FechaHora').NewValue := Now();

  end;

  if (UpdateKind=ukInsert) and (SourceDS=sqlItemPedido) and (DeltaDS.FieldByName('IdPedido').NewValue<0) then begin

      sqlTableIdentity.Close;

      sqlTableIdentity.ParamByName('TableName').Value := 'Pedido';

      sqlTableIdentity.Open;

      DeltaDS.FieldByName('IdPedido').NewValue  := sqlTableIdentity.FieldByName('Identidad').Value;

  end;

end;

sqlTablaIdenty es un TSqlDataSet con el siguiente CommandText

SELECT IDENT_CURRENT(:TableName) as Identidad

En sqlServer retorna la identidad para una tabla en una conexión determinada, el lector sabrá como hacerlo en su motor de base de datos favorito, si utilizo un esquema de herencia para la creacion de servermodules mucho mejor porque puede poner este sqldataset en el servermodule padre y tenerlo listo en cualquier server module donde lo necesite usar.

Por lo demas lo que se hace es preguntar si es una inserción, si el dataset origen es el del detalle y si el valor de idPedido del detalle es negativo, pensemos que me puede estar insertando un detalle mas en un pedido previamente creado que ya tiene su IdPedido correctamente generado, ahí no necesito preguntar a la base y asignarlo por la fuerza.

Lógica del negocio

Es sabido, y existen tutoriales y vídeos que explican como crear métodos en datasnap y consumirlos desde una aplicación cliente, ya sea Delphi, ASP.NET, PHP etc. Existe también muchos tutoriales y vídeos que explican como utilizar la interfaz IAppServer.
Muchos nos sentimos muy tentados a utilizar intensivamente la interfaz IAppServer, principalmente por su facilidad y rapidez. En entradas anteriores dejamos pendiente el tema de los autoincrementales en relaciones maestro-detalle con DataSnap

http://pablosoligo.blogspot.com/2011/07/dbexpressmaster-detail.html

lo que voy a mostrar es por un lado la puerta de entrada es a resolver el problema mencionado, pero es mucho mas que eso.

¿Si utilizo la interfaz IAppServer como y donde genero mi lógica del negocio?

La tentación de usar esta interfaz es muy grande, pero si los componentes generar automáticamente todo, ¿Como puedo yo meter mi lógica en todo este automatismo?
Existen varios lugares pero quiero proponer el que viene acompañado de mas información para la aplicación efectiva

Como muestra la figura el TDataSetProvider tiene dos eventos que nos serán muy útiles, BeforeUpdateRecord y AfterUpdateRecord. Tomemos el primero, generemos el código del evento y veamos que podemos hacer.



procedure TSMPedido.dspPedidoBeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind;
  var Applied: Boolean);
begin
  inherited;



  if (UpdateKind=ukInsert) and (SourceDS=sqlPedido) then begin
    DeltaDs.FieldByName('FechaHora').NewValue := Now();
  end;

end;



Aunque muy simple el código de arriba nos presenta una idea de lo que podemos hacer. El evento como su nombre lo indica se ejecuta justo antes de insertar/modifica/borrar el registro.
En el ejemplo, muy simple, lo único que hacemos es preguntar si es una inserción, de ser así ponemos la fecha hora que nos de el sistema. DeltaDS contiene los registros que cambiaron, la diferencia entre los datos que salieron del servidor y los que volvieron del cliente.
Parándonos sobre un registro y un campo con OldValue, NewValue podemos acceder a esa diferencias, incluso en este caso pisamos la fecha con el valor que nos retorne el sistema. Podemos incluso liberar a la aplicación cliente de esta responsabilidad y dejar que el servidor de aplicación lo haga.

Esto que ponemos aquí es didáctico, pero ya nos da la pauta de que es posible justo antes o justo después de actualizar un registro poner nuestra lógica, modificar valores, remplazar, e incluso tocar otras tablas que estén relacionadas. A modo de ejemplo luego de insertar/modificar/borrar un ítem de un pedido debo actualizar los stocks, luego de actualizar la temperatura de algún sensor puedo generar alguna alarma etc.
Para mejor, los parámetros me dan acceso a los elementos involucrados en la operación e incluso me dejan ver si es un insert, un update o un delete.

DBExpress - Master-Detail

Es posible anidar dataset en dbexpress, utilidad muy interesante para casos de tablas relacionadas tipo master-detail. Existe buena documentación sobre esto en internet y quiero usar esta entrada como introducción a las próximas donde se ve un tema menos documentado que es el de los autoincrementales en casos de maestro-detalle.

DBExpress permite anidar DataSet para tablas de tipo maestro-detalle hasta un limite cercano a los 15 o 16 dataset, quien escribe nunca supero los 5 o 6 y en este sentido nunca tuve mayores problemas.

¿Por que yo querría trabajar con dataset anidados?

Bueno las ventajas son varias, en principio manejar todos los elementos de manera única, cosa que es una realidad, si el maestro fuera una tabla de pedidos y me paro sobre un pedido particular quiero ver, borrar, editar y añadir items de ese pedido y no de otros.

Para mejor cuando actualizo quiero una actualización en un bloque transaccional, para todo lo relacionado, maestro y detalle.

¿Como genero un conjunto de dataset maestro-detalle en delphi?

Es fácil, con un poco de practica sale como pan caliente, pero obviamente hay que relacionar varias cosas de manera ordenada así que naturalmente puede aparecer alguna complicación las primeras veces.

Una imagen, mil palabras.

La imagen anterior muestra los diferentes componentes y sus conexiones, figura en el texto de la propiedad a conectar y la flecha la dirección de conexión. Las consultas en cada uno de los datasets serian las siguientes.

SqlPedido: Select * from Pedido

SqlItemPedido: Select * from ItemPedido Where IdPedido=:IdPedido

Se observa en la consulta de SqlItemPedido un parametro, IdPedido, el parametro debe tener el mismo nombre que la columna de la tabla maestra.

Añadiendo un y solo un datasetProvider conectado al dataset maestro (En este caso sqlPedido) tenemos solucionada “la parte servidora” independientemente de si es una aplicación multicapa o no. Si fuera una simple aplicación cliente/servidor entendamos que la parte servidora son las conexiones a la base de datos, los datasets y los providers, la parte cliente son los clientsdataset involucrados.

La "Parte servidora" quedaría entonces así

Ahora llega el momento de configurar la parte cliente, aca la cosa puede ser muy simple o se puede complicar un poco depende del diseño de base de datos.
Esta seria la forma del lado cliente, dos clientdatasets uno para la tabla maestra y otro para la tabla detalle, dos datasources enlazados y dos grillas, completando dos navigators por si son necesarios y un boton para efectuar el applyUpdate que llevara los cambios a la base de datos.

Muy importante, el ApplyUpdate solo debe ejecutarse sobre el clientdataset maestro, DBExpress se encargara del detalla en el orden correspondiente según sea inserción, edición o borrado.


procedure TFrmPedido.btnApplyUpdateClick(Sender: TObject);
begin
  cdsPedido.ApplyUpdates(-1);
end;

Para saber si todo va bien basta con generar los campos en el clientdataset maestro como se indica abajo.

Si se observa un campo mas con el nombre del dataset detalle del lado servidor nos quedamos tranquilos, conectamos todo bien y estamos listos para dar el toque final.
Lo que nos queda por hacer ahora es hacer apuntar en el clientDataSet detalle el DataSetField al campo sqlItemPedido del clientDataSet maestro.

Con esto es suficiente para el clientDataSet detalle, todo lo va a manejar el maestro y en el detalle solo veremos los registros correspondiente al registro maestro actual. Incluso podemos ver que dbexpress genera automáticamente en el detalle el id del maestro correspondiente.
Podemos añadir, borrar, editar y el applyUpdate que usaremos es el del clientDataSet maestro que se encargara de todo.

Hilando un poco mas fino


En aplicaciones reales algunos cosas que no debemos dejar pasar.

¿Traemos todos los detalles juntos o los traemos a medida que nos vamos desplazando por el maestro?

Esto en realidad depende de la cantidad de datos, del tamaño etc, lo importante es saber donde determinar esto y yo he encontrado sumamente útil en variedad de casos retrasar el envío del detalle en el momento que se necesite
Como muestra la figura una de las opciones del datasetProvider es poFetchDetailsOnDemand, esta propiedad determina si los detalle se envían sobre demanda o todos al mismo tiempo.

Ahora sabemos crear master-detail condición necesaria para encarar un problema un poco mas dificil y menos documentado de como combinar esto con los autoincrementales que tanto nos gustan, incluso si no fueran autoincrementales es muy probable que el IdPedido sea responsabilidad de la base de datos y no nuestra.
La manera de resolver esto y otros temas quedan para próximas entradas.