miércoles, 4 de noviembre de 2009

EMULANDO AUTONUMERICOS


Un tema recurrente : generar una secuencia numérica correlativa, sin discontinuidades y a prueba de errores, para asignar valores a nuevos registros, al estilo de los autonuméricos, pero sin los inconvenientes de estos.
Ciertamente hay variedad de recetas para abordar este problema, pero la piedra de toque de todos ellos es su empleo en sistemas con elevada concurrencia de usuarios y que dicho sistema sea también efectivo dentro de una instrucción sql (y que permita por tanto un insert masivo). Personalmente siempre he usado transacciones (ADO) para asegurar que la lectura del posible nuevo valor, quede bloqueado (para nuevas lecturas) hasta que finalice el proceso de adjudicación del mismo. Esta vez propongo utilizar efectivamente transacciones, pero de forma implícita dentro de una instrucción sql. Esto, en un SGDB como Sql Server es bien simple, pues todas las instrucciones contenidas en un procedimiento almacenado, utilizan la misma transacción, de tal forma que un error de ejecución de una de ellas, trunca todo el procedimiento. Pero en Access, puesto que su lenguaje sql no admite lotes de instrucciones (solo podemos ejecutar/llamar comandos de uno en uno), esto no es posible, a no ser que seamos capaces en una sola sentencia de obtener un nuevo valor, actualizar la tabla y recuperar el valor escrito (hay que tener en cuenta que, una de las características de los autonuméricos es devolver el nuevo valor insertado).
Pongamos un ejemplo : Generar un nuevo número de factura. Para ello disponemos de un formulario para introducir la información de la cabecera del documento : Fecha, Cliente, Tarifa, etc, y un subformulario para los detalles de la misma. Una solución simple y clásica es obtener el mayor valor concedido al campo numerador mediante un Select Max() e incrementar en uno dicho dato ... Pero este sistema, desarrolla toda su lógica en el lado cliente y presenta inconvenientes evidentes en un entorno multiusuario. La solución que propongo consiste en tener dos tablas auxiliares, una en el 'lado datos' y otra en el 'lado cliente'. El objetivo es almacenar el último valor generado absoluto, y el relativo en cada máquina cliente. Ambas tablas deben estar accesibles en la base de datos donde ejecutemos el código : una como tabla local, y la otra (por ejemplo) como tabla vinculada.
Recapitulemos : Tenemos en una base de datos (de servidor) una tabla Facturas, y una tabla Numeradores que contiene el último número de factura generado. Cuando necesitemos generar un nuevo documento solo tenemos que ejecutar esta instrucción sql :

Update Numeradores

Left Join
(Select * From Facturas Where 1=0) As T
On Numeradores.Numerador = T.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,
Facturas.Tarifa = 1;

Condiciones previas : Numeradores debe contener un solo registro con valor 1 (valor inicial del autonumérico).
Dado que hemos relacionado de forma asimétrica ambas tablas, aún cuando la segunda tabla (en este caso una Select sobre Facturas) no devuelve resultados, puesto que hemos añadido la condición 1=0, la instrucción update (a efectos prácticos no es un update, sino un insert) afectará a tantos registros como haya en la tabla numeradores, es decir : uno. Al ejecutarse la sentencia, el valor del numerador se incrementa en 1 (no olvidemos que se trata de una instrucción update), en segundo lugar, el nuevo numerador generado se asigna al campo Nfactura de la tabla Facturas, pero dado que no hay ningún registro presente (en la consulta) de la tabla Facturas (hemos filtrado los resultados con esta condición imposible : 1=0), en realidad estamos insertando un nuevo registro en la tabla, y finalmente se actualizan el resto de los campos de Facturas con los valores incluidos en la instrucción sql.

Vamos a hablar de la tabla local de numeradores a la que previamente haciamos referencia. Supongamos que necesitamos agregar registros de uno en uno desde un formulario, y que vamos a introducir desde el propio formulario los datos en cada uno de los campos de la tabla Facturas : Fecha, Cliente, Tarifa, etc. Así pues, necesitamos generar un nuevo registro en dicha tabla, y recuperar automáticamente dicho registro para poder trabajar con él. ¿ En un escenario multiusuario con acceso concurrente a la tabla Facturas, si insertamos una nueva línea, como podemos estar seguros de recuperar el registro que nosotros hemos generado y no otro ? … cabe pensar en la posibilidad que escasos instantes despues de insertar el registro y antes de recuperarlo, otro usuario ha añadido otra línea en la tabla … Si utilizamos Max(NFactura) o leemos el valor de la tabla Numeradores, no nos sirve, puesto que el inoportuno usuario ha modificado también ambos campos …

La panacea a este dilema es incorporar una tabla de Numeradores local (o cliente) además de la tabla Numeradores del servidor que hasta ahora hemos ido usando en nuestra instrucción sql. Se trata de guardar en la tabla local el último Numerador asignado, y puesto que dicha tabla solo se modifica desde la aplicación cliente donde corre el código, tenemos la certeza de que el valor guardado en ella corresponde al último registro asignado por nosotros :

Update (Select Numeradores.Numerador As NumeradorServer,
Numeradores_locales.Numerador As NumeradorLocal
From Numeradores
Inner Join
Numeradores_locales
On Numeradores.Numerador >= Numeradores_locales.Numerador) As N
Left Join
(Select * From Facturas Where 1=0) As T
On N.NumeradorServer = T.NFactura
Set N.NumeradorServer = N.NumeradorServer + 1,
N.NumeradorLocal = N.NumeradorServer,
Facturas.NFactura = N.NumeradorServer,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,

Facturas.Tarifa = 1;

Para recuperar el último valor insertado (por nosotros) en la tabla Facturas :

Select Facturas.
*
From Facturas
Inner Join Numeradores_locales
On Facturas.NFactura = Numeradores_locales.Numerador

Pero … ¿ Solo puedo añadir registros de uno en uno ? …

No. Supongamos que tenemos que insertar un conjunto de registros en la tabla Facturas, estos registros los obtiene una consulta llamada Nuevas_Facturas. Dicha consulta debe tener los campos que nos interesa grabar en Facturas, y además, un campo para relacionarlo con Facturas.Nfactura y Numeradores.Numerador. El valor de dicho campo ha de ser tal, que sea imposible que coincida en cualquiera de ambas tablas. Si hemos empezado la numeración de documentos a partir de 1, un valor aceptable para este propósito seria -1.
Y esta es la sql a ejecutar :

Update (Numeradores
Left Join
Nuevas_facturas
On Numeradores.Numerador <> Nuevas_facturas.NFactura)
Left Join
Facturas On Facturas.NFactura = Nuevas_facturas.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = Nuevas_facturas.Fecha,
Facturas.Cliente = Nuevas_facturas.Cliente,
Facturas.Tarifa = Nuevas_facturas.Tarifa;

… con ello añadiremos tantos registros en la tabla Facturas como nos devuelva la consulta Nuevas_Facturas.
En líneas anteriores comentaba la necesidad de que si alguna de las modificaciones en tabla, de alguno de los registros, fallara, se truncará todo el proceso. Podemos comprobar (manipulando la tabla Numeradores para producir duplicados en el campo clave Nfactura), que caso de haber un error, todo el proceso queda abortado, permaneciendo intactas ambas tablas.


Ramon Poch. Terrassa a 04/11/2009.