Ejemplo de recursión (4). Actualizando filas recursivamente

Así como podemos consultar filas de forma recursiva, también podemos actualizar esas filas recursivamente si es lo que necesitamos.

Veamos un caso:

Tenemos una tabla llamada CUENTAS que tiene estos datos:

Captura 1. Si haces clic en la imagen la verás más grande

En la columna CUE_IDENTI guardamos el identificador de cada fila; en la columna CUE_IDEPAD guardamos el identificador de su fila padre; en la columna CUE_TIMEST guardamos la fecha y la hora de la última actualización de una cuenta. Ahora supongamos que queremos que la cuenta CRÉDITOS y todas las cuentas que sean sus descendientes tengan la fecha y la hora actuales.

Listado 1.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (
      WITH RECURSIVE MiCTE AS (
         
         SELECT
            CUE_IDENTI
         FROM
            CUENTAS
         WHERE
            CUE_IDENTI = 6
         
         UNION ALL
         
         SELECT
            T1.CUE_IDENTI
         FROM
            CUENTAS T1
         JOIN
            MiCTE 
               ON T1.CUE_IDEPAD = MiCTE.CUE_IDENTI
         
      )
      
      SELECT CUE_IDENTI FROM MiCTE

   );

Captura 2. Si haces clic en la imagen la verás más grande

Tal y como podemos ver en la Captura 2. se actualizó la columna CUE_TIMEST de la cuenta que tiene CUE_IDENTI =6 y también de todos sus descendientes. En el Listado 1. podemos ver que la primera cuenta a ser actualizada es la que tiene CUE_IDENTI = 6, a continuación se actualizan las demás filas.

Desde luego que no solamente podemos cambiar la fecha y la hora al actualizar, podemos cambiar cualquier dato que necesitemos.

Explicación:

Como en la cláusula WHERE del UPDATE estamos usando IN eso significa que queremos actualizar varias filas. ¿Qué filas actualizaremos? Aquellas cuya columna CUE_IDENTI se encuentre en la tabla virtual que crearemos. En nuestra tabla virtual la primera fila es la que tiene CUE_IDENTI = 6, así que esa fila sí o sí será actualizada. A continuación hallamos el CUE_IDENTI de las demás filas que queremos actualizar. Como la tabla virtual es recursiva entonces se le irán agregando filas hasta que la condición del JOIN deje de cumplirse. El último SELECT es el que devuelve todas las filas de la tabla virtual, y todas esas filas tienen una sola columna, llamada CUE_IDENTI.

Entonces, nuestro UPDATE recursivo sería el equivalente a este UPDATE no recursivo.

Listado 2.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (6, 7, 8, 9, 10)

¿Cuál es la diferencia? Que en el Listado 2. debemos conocer  todos los identificadores que deseamos actualizar, en cambio en el Listado 1., no. Allí solamente necesitamos conocer el identificador de la primera cuenta, nada más. Todas las cuentas que sean sus descendientes serán actualizadas, sean 4 como en este caso o sean 500 o sean 1000. La única limitación es que pueden ser como máximo 1024 porque el Firebird permite hasta 1024 recursiones.

El Listado 2. es más sencillo, pero requiere conocer los identificadores de todas las cuentas que queremos actualizar y si los usuarios están constantemente agregando cuentas tendremos que estar cambiando nuestro UPDATE a cada rato. Por el contrario con el Listado 1. lo único que debemos conocer es el identificador de la primera cuenta que deseamos actualizar y todas las cuentas que sean sus descendientes se actualizarán también.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

El índice del blog Firebird21

El foro del blog Firebird21

Guardando y leyendo una columna de tipo TIMESTAMP en VFP

Si programas usando el lenguaje Visual FoxPro entonces puedes tener dos dudas:

1. ¿Cómo hago para guardar un dato en una columna de tipo TIMESTAMP?
2. ¿Cómo hago para leer una columna de tipo TIMESTAMP?

1. Guardando una columna de tipo TIMESTAMP

Las columnas TIMESTAMP del Firebird se guardan como una fecha, un espacio en blanco, y una hora. Algo como:

03/08/2014 11:48:25

Que significa: “tres de agosto de dos mil catorce, once horas, cuarenta y ocho minutos, veinticinco segundos”.

En Visual FoxPro para convertir un string en un timestamp se usa la función CTOT(). Veamos un ejemplo:

MiFecha = DATE()
MiHora ="11:48"
MiColumna = CTOT(DTOC(MiFecha) + " " + MiHora)

La función DTOC() significa “date to character” y convierte una fecha a caracter. La función CTOT() significa “character to timestamp” y convierte un caracter a timestamp.

Hay que usar la función DTOC() para poder concatenar la fecha, el espacio en blanco, y la hora.

Fíjate que no es obligatorio completar los segundos, si no pones los segundos el Firebird asumirá que es “00”

Otro ejemplo:

MiFecha = DATE()
MiHora ="11:48:25"
MiColumna = CTOT(DTOC(MiFecha) + " " + MiHora)

 Aquí sí se establecieron los segundos. Es opcional.

Otro ejemplo:

MiFecha = DATE(2014, 8, 3)
MiHora = TIME()
MiColumna = CTOT(DTOC(MiFecha) + " " + MiHora)

 Aquí se usó la función DATE() para establecer la fecha deseada y la función TIME() para establecer la hora deseada.

2. Leyendo una columna de tipo TIMESTAMP

Muy bien, ya has guardado una columna de tipo TIMESTAMP en tu tabla del Firebird, pero ahora quieres leer el contenido de esa columna. ¿Cómo lo haces?

Aquí tienes dos posibilidades:

1. Dejar el resultado en una variable de tipo timestamp
2. Separar la columna timestamp para tener en una variable la fecha y en otra variable la hora

Si la que te interesa es la primera posibilidad nada tienes que hacer, la columna que has leído de tu tabla Firebird ya está en el formato adecuado y puedes usarla sin problemas.

Si lo que te interesa es separar el contenido de la columna de tipo TIMESTAMP para tener en una variable la fecha y en otra variable la hora esto es lo que debes escribir:

MiFecha = TTOD(MiColumna)
MiHora = TTOC(MiColumna, 2)

La función TTOD() significa “timestamp to date” y retorna la parte de la fecha de un TIMESTAMP

La función TTOC() significa “timestamp to character” y cuando su segundo argumento es el número 2 retorna la parte de la hora de un TIMESTAMP.

Conclusión:

Usar columnas y variables de tipo TIMESTAMP entre Visual FoxPro y Firebird es sencillo, ninguna complicación, solamente hay que recordar cuales son las funciones adecuadas para usar, eso es todo.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Obteniendo el TIMESTAMP de la última actualización a una tabla

Este artículo está basado en el FAQ1 escrito por Karol Bieniaszewski:

http://itstop.pl/en-en/Porady/Firebird/FAQ1/TABLE-MODIFICATION-TIMESTAMP

A veces puede ser útil para nosotros conocer cual fue la última vez que se realizó un INSERT, UPDATE, o DELETE a una tabla. Tenemos 3 formas de obtener esa información:

1. Guardar el TIMESTAMP en cada fila de la tabla
2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP
3. Usar generadores

Opción 1. Guardar el TIMESTAMP en cada fila de la tabla

La opción 1. es muy fácil de implementar (simplemente le agregamos una columna TIMESTAMP a la tabla) y también puede ser muy conveniente porque sabremos no solamente cuando se actualizó por última vez la tabla sino también cada fila de ella. Lo único que necesitamos hacer es un SELECT y en la cláusula ORDER BY elegimos la columna donde guardamos el TIMESTAMP y la ordenamos descendentemente con DESC. Por ejemplo:

SELECT
   PRD_TIMEST
FROM
   PRODUCTOS
ORDER BY
   PRD_TIMEST DESC
ROWS
   1

En este caso, a la columna donde guardamos el TIMESTAMP la hemos llamado PRD_TIMEST. El gran problema con esta solución es cuando las filas de nuestra tabla se cuentan por muchos millones. Si no tenemos un índice DESC sobre la columna PRD_TIMEST entonces obtener el resultado puede demorar una eternidad. Y si tenemos un índice DESC sobre esa columna entonces cada INSERT o UPDATE modificará el contenido de esa columna y también al índice asociado, lo cual causará una demora. En otras palabras, si no tenemos un índice DESC el SELECT será muy lento, y si tenemos un índice DESC entonces el INSERT y el UPDATE serán lentos. No es una buena situación cuando la tabla tiene millones de filas.

Opción 2. Crear otra tabla y guardar en ella el Nombre de la Tabla y el TIMESTAMP

Supongamos que creamos una tabla llamada TIEMPOS con las columnas TIE_NOMBRE y TIE_TIMEST, para guardar en esas columnas el nombre de la tabla y el TIMESTAMP de su última modificación.

Aquí podemos tener problemas de lentitud si se hacen muchos INSERT y UPDATE simultáneamente a la tabla original (a la tabla PRODUCTOS, en nuestro ejemplo). ¿Por qué? porque nuestra tabla TIEMPOS tendrá problemas de concurrencia cuando dos o más transacciones quieran actualizar la misma fila. Supongamos que 20 usuarios están actualizando a la tabla PRODUCTOS, entonces cada uno de esos 20 usuarios quiere guardar el TIMESTAMP de su modificación a la tabla PRODUCTOS en una fila de la tabla TIEMPOS. Como la fila es una sola (porque el TIMESTAMP de la tabla PRODUCTOS se guarda en una sola fila de la tabla TIEMPOS) entonces 1 solo usuario podrá guardar el TIMESTAMP, los restantes 19 tendrán que esperar. Luego, el usuario 2 modifica el TIMESTAMP y los restantes 18 tienen que seguir esperando. Y así sucesivamente. No es una buena situación tampoco.

Opción 3. Usando generadores

Esta solución es más complicada de implementar que las dos anteriores pero tiene la gran ventaja de que es rapidísima en todas las situaciones.

Paso 1. Crear un generador para la tabla original

CREATE GENERATOR ULTIMA_MODIFICACION_PRODUCTOS

En este generador se guardará el TIMESTAMP que nos interesa

Paso 2. Crear un trigger

CREATE TRIGGER AIU_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE ltTimeStamp TIMESTAMP;
   DECLARE VARIABLE lnNumero    BIGINT;
BEGIN

   ltTimeStamp = (SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE);

   lnNumero = CAST(EXTRACT(YEAR        FROM ltTimeStamp) ||
                   LPAD(EXTRACT(MONTH  FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(DAY    FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(HOUR   FROM ltTimeStamp), 2, '0') ||
                   LPAD(EXTRACT(MINUTE FROM ltTimeStamp), 2, '0') ||
                   LPAD(CAST(EXTRACT(SECOND FROM ltTimeStamp) * 10000 AS INTEGER), 6, '0')
              AS BIGINT);

   lnNumero = GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, -GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0) + lnNumero);

END;

Lo que hace este trigger es:

1. Obtener la fecha y hora actuales
2. Convertir la fecha y hora en un número BIGINT
3. Actualizar el generador para guardar en él el número BIGINT obtenido en el punto 2.

Cuando finaliza el trigger tendremos en el generador ULTIMA_MODIFICACION_PRODUCTOS la fecha y la hora en que se realizó el último INSERT o el último UPDATE a la tabla PRODUCTOS.

Por supuesto que si lo deseamos podemos tener tres generadores: uno para guardar los INSERT, otro para guardar los UPDATE y otro para guardar los DELETE, eso ya dependerá de nosotros.

Paso 3. Convertir el valor BIGINT del generador en un TIMESTAMP

El generador cuyo valor establecimos con el trigger será algo así: 201403112149285420

O sea, año 2014, mes 03, día 11, hora 21, minutos 49, segundos 28, milisegundos 5420

Pero en general no nos interesará ver un número entero sino un TIMESTAMP y para ello necesitamos escribir un stored procedure seleccionable que se encargue de convertir al número BIGINT en un TIMESTAMP.

CREATE PROCEDURE OBTENER_TIMESTAMP(
   tnGenerador BIGINT)
RETURNS(
   fttTimeStamp TIMESTAMP)
AS
BEGIN

   IF (tnGenerador = 0) THEN
      fttTimeStamp = NULL;
   ELSE
      fttTimeStamp = CAST(SUBSTRING(tnGenerador FROM  1 FOR 4) || '-' ||
                          SUBSTRING(tnGenerador FROM  5 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  7 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM  9 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 11 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 13 FOR 2) || '-' ||
                          SUBSTRING(tnGenerador FROM 15 FOR 4)
                     AS TIMESTAMP);

   SUSPEND;

END;

Paso 4. Obtener el valor del generador

Ahora, queremos conocer el TIMESTAMP de la última modificación a la tabla PRODUCTOS, lo conseguimos con:

SELECT
   fttTimeStamp
FROM
   OBTENER_TIMESTAMP(GEN_ID(ULTIMA_MODIFICACION_PRODUCTOS, 0))

Y obtendremos algo como:

Captura 1. Si haces clic en la imagen la verás más grande

que es justamente lo que estábamos necesitando.

Conclusión:

Usar el método de los generadores para obtener el TIMESTAMP de la última modificación a una tabla es lo más rápido que podemos tener. Si la tabla no tiene muchas filas o si se la actualiza raramente entonces se podría emplear cualquiera de los otros dos métodos también, pero en tablas grandes y que son actualizadas muy frecuentemente el mejor método es el de los generadores.

Artículo relacionado:

El índice del blog Firebird21

EXTRACT()

Descripción: Extrae y devuelve un elemento de una expresión que sea de tipo DATE, TIME o TIMESTAMP.

Tipo de resultado: Smallint o Numeric

Sintaxis:

EXTRACT(<parte> OF <fechahora>)

<parte> = YEAR | MONTH | WEEK | DAY | WEEKDAY | YEARDAY | HOUR | MINUTE | SECOND | MILLISECOND

<fechahora> = Una expresión que sea de tipo DATE, TIME o TIMESTAMP

Los tipos de datos retornados y posibles rangos son mostrados en la tabla de abajo. Si tratas de extraer una parte que no está presente en el argumento (por ejemplo SECOND de un DATE o YEAR de un TIME) un error ocurre.

|-----------------------------------------------------------------------------------|
| Parte          Tipo          Rango            Comentario                          |
|-----------------------------------------------------------------------------------|
| YEAR           Smallint      1-9999                                               |
| MONTH          Smallint      1-12                                                 |
| WEEK           Smallint      1-53                                                 |
| DAY            Smallint      1-31                                                 |
| WEEKDAY        Smallint      0-6               0 = Domingo                        |
| YEARDAY        Smallint      0-365             0 = 1 de enero                     |
| HOUR           Smallint      0-23                                                 |
| MINUTE         Smallint      0-59                                                 |
| SECOND         Numeric(9, 4) 0.0000-59.9999    incluye milisegundos como fracción |
| MILLISECOND    Numeric(9, 1) 0.00-999.9                                           |
|-----------------------------------------------------------------------------------|

Ejemplo:

SELECT
   EXTRACT(DAY   FROM MVC_FECHAX) AS DIA_DE_LA_VENTA,
   EXTRACT(MONTH FROM MVC_FECHAX) AS MES_DE_LA_VENTA
FROM
   MOVIMCAB
WHERE
   MVC_TIPMOV = 'SVT'

.