Actualizando una tabla con datos de otra tabla

Deja un comentario

En ocasiones podemos necesitar que en una columna de una tabla se encuentren los mismos datos que se encuentran en una columna de otra tabla.

En Firebird el comando UPDATE no soporta la cláusula JOIN. Así que algo como esto no es posible hacer:

Listado 1.

UPDATE
   Tabla1 T1
JOIN
   Tabla2 T2
   ON T1.Columna1 = T2.Columna2
SET
   T1.Columna3 = T2.Columna4

Sin embargo, esto sí podemos hacer:

Listado 2.

UPDATE
   Tabla1 T1
SET
   T1.Columna3 = (SELECT T2.Columna4 FROM Tabla2 T2 WHERE T1.Columna1 = T2.Columna2 ROWS 1)

El SELECT devolverá a una sola fila y a una sola columna de esa fila. O sea, en otras palabras, obtendremos un único valor. Nos aseguramos que devuelva una sola fila al escribir ROWS 1 y devuelve una sola columna porque en el SELECT se escribió una sola columna. Es por lo tanto perfectamente válido lo que hemos hecho.

Desde luego que dentro del SELECT sí podemos usar JOIN y no solamente a una, sino a varias tablas, no hay problemas con eso. También podemos usar las cláusulas GROUP BY, HAVING, ORDER BY, etc.

De esta manera, aunque el comando UPDATE no soporta la cláusula JOIN hemos obtenido el mismo resultado que si la soportara.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Firebird 3.0 Quick Start Guide

Deja un comentario

La documentación para empezar a usar rápidamente Firebird 3 se encuentra disponible (en inglés) y la puedes descargar desde:

Firebird 3. Quick Start Guide

En ese documento encontrarás los siguientes temas:

  • Instalación
  • Verificación de que la instalación fue realizada correctamente
  • Ubicaciones por defecto de los componentes de Firebird 3
  • Configuración del Servidor
  • Administración del Servidor
  • Conexiones a bases de datos
  • Backups

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

CloudaIDE framework

1 comentario

Poder terminar nuestras aplicaciones más rápidamente siempre es beneficioso para nosotros. CloudaIDE framework sirve justamente para eso. Crea aplicaciones “en la nube”. Y es gratis.

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

Importante: Requiere Windows de 64 bits o Linux de 64 bits.

Algo muy bueno es que no tiene restricciones para Firebird. O sea que las funcionalidades que son pagas para MySql, PostgreSql, y Oracle, son gratis para Firebird.

Puede ser descargado desde:

http://cloudaide.org/index.html

En esa página hay también un vídeo explicativo que demuestra lo fácil que es usar a CloudaIDE framework. Y algo muy bueno de ese vídeo es que está basado en una Base de Datos de … Firebird.

Así que si tienes intenciones de desarrollar aplicaciones para “la nube”, este programa puede resultarte de gran ayuda y deberías probarlo.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

¿Cómo funciona una transacción SNAPSHOT?

Deja un comentario

Como recordarás, una transacción en Firebird puede tener uno de estos tres aislamientos:

  • READ COMMITED
  • SNAPSHOT
  • SNAPSHOT TABLE STABILITY

Los aislamientos le dicen al Firebird lo que debe hacer cuando una fila quiere ser actualizada (UPDATE) o borrada (DELETE) por más de una transacción al mismo tiempo.

Veamos un ejemplo:

  1. La transacción T1 empieza (su aislamiento es SNAPSHOT, y su modo de bloqueo es WAIT)
  2. La transacción T2 empieza (su aislamiento es SNAPSHOT)
  3. La transacción T2 actualiza (UPDATE) a una fila X de la tabla PRODUCTOS.
  4. La transacción T2 finaliza con un COMMIT
  5. La transacción T3 empieza (su aislamiento es SNAPSHOT)
  6. La transacción T3 también actualiza (UPDATE) a la misma fila X de la tabla PRODUCTOS
  7. La transacción T1 trata de actualizar (UPDATE) a la misma fila X de la tabla PRODUCTOS, pero como esa fila está bloqueada por la transacción T3 entonces deberá esperar hasta que la transacción T3 finalice.

Sin embargo, debemos notar que en este ejemplo la transacción T1 fallará siempre. ¿Por qué? Porque tendrá un conflicto con la transacción T3 si la transacción T3 finalizó con un COMMIT, o tendrá un conflicto con la transacción T2 si la transacción T3 finalizó con un ROLLBACK. O sea que, sin importar como termine la transacción T3 (con un COMMIT o con un ROLLBACK) la transacción T1 fallará.

Te puedes preguntar: ¿y por qué la transacción T1 debe esperar hasta que finalice la transacción T3? Después de todo, en ambos casos fallará, entonces ¿por qué la espera?

La respuesta está en que el Firebird solamente verifica la última versión de una fila para saber si ocurrió un conflicto o no. Si verificara la anteúltima versión entonces podría hacer fallar a la transacción T1 en el mismo momento en que hiciera un UPDATE, pero eso implicaría más trabajo y por lo tanto solamente verifica a la última versión.

Una transacción SNAPSHOT en el momento en que se inicia copia en su porción de la memoria RAM de la computadora la TIP (Transaction Inventory Page) conteniendo a todas las transacciones que están activas en ese momento. O sea, la transacción T2 conoce cuales son todas las transacciones que estaban activas cuando se inició la transacción T2, pero desconoce totalmente a las transacciones que se iniciaron después que ella y por lo tanto supone que están activas ya que evidentemente no habían finalizado cuando empezó la transacción T2. Cada transacción tiene un número único, que se guarda en la TIP. Cuando una transacción inicia bloquea a su propio número y lo desbloquea cuando finaliza (sea con un COMMIT o con un ROLLBACK). De esta manera es muy fácil saber si una transacción está activa o no. Si no se puede desbloquear su número, está activa. Si se puede desbloquear su número, no está activa.

Cuando una transacción SNAPSHOT trata de bloquear a una fila para hacerle un UPDATE o un DELETE, lo que puede ocurrir es lo siguiente:

  • Si la última versión de esa fila fue creada por una transacción que tiene un número menor y que no está en su copia de la TIP, el bloqueo tendrá éxito. ¿Por qué? porque como no está en su copia de la TIP y el número es menor, significa que una transacción anterior que insertó o actualizó a la fila ya finalizó con un COMMIT.
  • Si la última versión de una fila fue creada por una transacción cuyo número está en la TIP entonces debe verificar si esa transacción ya finalizó. ¿Y cómo lo verifica? Tratando de bloquear el número que esa otra transacción tiene en la TIP. Si lo consigue, la otra transacción ya finalizó y se puede bloquear a la fila con éxito.
  • Si el último COMMIT a la fila fue realizado por una transacción que tiene un número de transacción mayor, eso significa que esa otra transacción empezó después. Y por lo tanto, no se podrá bloquear a la fila.

Ejemplo:

Empieza una transacción, su número es 529, y en su copia de la TIP tiene a los números 521, 525, 526, 528. Eso significa que esas 4 transacciones están activas, aún no han finalizado ni con un COMMIT ni con un ROLLBACK.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila X es el 291. Como el número de transacción 291 no está en la copia de la TIP, eso significa que la transacción 291 (o una transacción anterior a ella) ya ha finalizado con un COMMIT y por lo tanto se podrá realizar el UPDATE con éxito a la fila X.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila es el 526. Como el número de transacción 526 está en la copia de la TIP, eso significa que la transacción 526 estaba activa cuando se inició la transacción 529. Pero ¿está activa ahora? quizás sí, quizás no, para verificarlo la transacción 529 trata de bloquear al número 526 en la TIP global, no en su propia copia de la TIP. Si consigue realizar el bloqueo, la transacción 526 ya no está activa y entonces podrá realizar el UPDATE con éxito. ¿Y si no consigue bloquear, qué hace? Eso dependerá del modo de bloqueo. Si es WAIT, seguirá intentando bloquear hasta tener éxito. Si es NO WAIT lanzará una excepción con un mensaje de error.

La transacción cuyo número es 529 quiere hacer un UPDATE a una fila X de la tabla PRODUCTOS, el número de transacción que tiene la última versión de esa fila es el 540. ¿Podrá la transacción 529 realizar el UPDATE? Depende. Si la transacción 540 finaliza con un COMMIT, no podrá. ¿Por qué no? Porque 540 es mayor que 529. Si la transacción 540 finaliza con un ROLLBACK entonces hay que buscar el número que tiene la última versión de esa fila X cuya transacción finalizó con un COMMIT. Si el último COMMIT a la fila X fue realizado por la transacción 520, la transacción 529 podrá realizar el UPDATE (porque 520 es menor que 529). Si el último COMMIT a la fila X fue realizado por la transacción 535, la transacción 529 no podrá realizar el UPDATE (porque 535 es mayor que 529).

Una transacción SNAPSHOT solamente puede actualizar (UPDATE) o borrar (DELETE) a las filas creadas por las transacciones que empezaron antes que ella.

Recuerda que el Firebird crea una nueva versión de una fila cada vez que se ejecuta el comando UPDATE o el comando DELETE en esa fila.

Sin importar como finalice la transacción (con un COMMIT o con un ROLLBACK) hay una nueva fila. Esto va creando filas inservibles (se les llama “basura”) y por ese motivo hay que limpiar a la Base de Datos de basura cada cierto tiempo.

Una fila tiene la siguiente forma:

| Nº de Transacción | Columna1 | Columna2 | Columna 3| etc.

Importante: Una transacción T1 (cuyo aislamiento es SNAPSHOT) puede actualizar (UPDATE) o borrar (DELETE) a una fila solamente cuando el Nº de Transacción que realizó el último COMMIT a esa fila es menor que el número de la transacción T1.

Artículos relacionados:

Entendiendo a las transacciones

Entendiendo a los identificadores de las transacciones

Modos de bloqueo de las transacciones

Bloqueos mortales

Lock conflict on no wait transaction. Deadlock

El índice del blog Firebird21

El foro del blog Firebird21

¿Cuál es la mejor manera de almacenar una IP?

4 comentarios

Este artículo es una colaboración de Esteban Herrero, uno de los más antiguos seguidores de este blog. Gracias Esteban.

 

Si tenemos la siguiente IP 192.168.10.21 y quisiera tener un campo para almacenarla, lo más fácil sería crear un campo Char(15), pero podríamos pensar que no todas las IP tienen 15 caracteres, entonces sería mejor utilizar un VarChar(15). Lo cierto es que cada carácter almacenado ocupa  1 byte, haciendo que el almacenamiento de esta Ip lleve 13 bytes o 15 con todos los caracteres.

Pero hay una mejor forma de guardar una Ip y es utilizando un campo BigInt y sólo estaríamos utilizando 64 Bits (8 bytes). Xq no usar un campo Integer, la razón es muy simple es 32 bits y el valor máximo es 2,147,483,647 y nuestro número convertido no entraría.

 

Vamos a un ejemplo:

 

Ip 192.168.10.21

 

11000000 10101000 00001010 00010101 (en binario)

 

Ahora a Convertir:

21 * 1  +

10 * 256 +

168 * 256 * 256 +

192 * 256 * 256 * 256 = 3,232,238,101

Cómo hacerlo en Firebird? Con estos 2 procedimientos:

Execute Procedure SP_INTTOIP(3232238101);

Execute Procedure sp_iptoint(‘192.168.10.21’);

 

CREATE PROCEDURE SP_INTTOIP(

NIP BIGINT)

RETURNS(

IP VARCHAR(15))

AS

DECLARE VARIABLE Octet1 BIGINT;

DECLARE VARIABLE Octet2 BIGINT;

DECLARE VARIABLE Octet3 BIGINT;

DECLARE VARIABLE Octet4 BIGINT;

DECLARE VARIABLE RestoIP BIGINT;

BEGIN

Octet1 = :nIp / 16777216;

RestoIp = :nIp – (Octet1 * 16777216);

Octet2 = RestoIp / 65536;

RestoIp = RestoIp – (Octet2 * 65536);

Octet3 = RestoIp / 256;

Octet4 = RestoIp – (Octet3 * 256);

Ip = Cast(Octet1 as VARCHAR(3)) || ‘.’ || Cast(Octet2 as VARCHAR(3)) || ‘.’ || Cast(Octet3 as VARCHAR(3)) || ‘.’ || Cast(Octet4 as VARCHAR(3));

SUSPEND;

END;

 

CREATE PROCEDURE SP_IPTOINT(

CIP VARCHAR(15))

RETURNS(

NINT BIGINT)

AS

DECLARE VARIABLE lcColumna VARCHAR(4);

DECLARE VARIABLE nCan1 SMALLINT;

DECLARE VARIABLE nFactor BIGINT;

BEGIN

 

nInt = 0;

nFactor = 16777216;

 

WHILE ((Char_Length(cIp) > 0) AND (POSITION(‘.’ in CIP) <> 0)) DO BEGIN

EXECUTE PROCEDURE Parser(cIp, ‘.’) RETURNING_VALUES :lcColumna;

nCan1 = CHAR_LENGTH(:lcColumna) + 1;

cIp = Overlay(cIp placing ” from 1 for nCan1);

cIp = trim(cIp);

nInt = nInt + (Cast(:lcColumna as Smallint) * nFactor);

nFactor = nFactor / 256;

END

nInt = nInt + CAST(cIp as SMALLINT);

SUSPEND;

END;

 

CREATE PROCEDURE PARSER(

TCTEXTO VARCHAR(18192),

TCSEPARADOR VARCHAR(12))

RETURNS(

FTCNOMBRE VARCHAR(1024))

AS

DECLARE VARIABLE lnPosicion SMALLINT;

BEGIN

lnPosicion = Position(tcSeparador IN tcTexto);

ftcNombre = Left(tcTexto, lnPosicion – 1) ;

END;

 

Crear una función en Firebird 2.x que nos devuelva una fecha en cualquier formato

3 comentarios

A veces podríamos llegar a necesitar representar a las fechas en diferentes formatos, por ejemplo a la fecha 28 de marzo de 2017 podríamos querer presentarla como:

  • 28/03/2017
  • 03/28/2017
  • 2017/03/28
  • 28-03-2017
  • 28-MAR-2017
  • 28/MAR/2017
  • 28 de marzo de 2017
  • etc.

¿Cómo podemos obtener la fecha en el formato que nos interesa?

En Firebird no existe una función llamada DATE_FORMAT() o equivalente como sí existe en Oracle y en MySQL, por ejemplo. Pero si necesitamos esa función entonces simplemente … podemos crearla.

Ya sabemos como escribir un stored procedure y usarlo como si de una función se tratara, lo hemos visto en el artículo:

https://firebird21.wordpress.com/2014/04/20/usando-un-stored-procedure-como-una-funcion/

Entonces, usaremos esa técnica para escribir nuestra propia función DATE_FORMAT()

Listado 1.

CREATE PROCEDURE DATE_FORMAT(
   tdFechax DATE,
   tcFormat VARCHAR(128))
RETURNS(
   ftcFecha VARCHAR(128))
AS
   DECLARE VARIABLE lnDia SMALLINT;
   DECLARE VARIABLE lnMes SMALLINT;
   DECLARE VARIABLE lnAno SMALLINT;
   DECLARE VARIABLE lcMes VARCHAR(128);
BEGIN

   tcFormat = UPPER(tcFormat);

   lnDia = EXTRACT(DAY FROM tdFechax);
   lnMes = EXTRACT(MONTH FROM tdFechax);
   lnAno = EXTRACT(YEAR FROM tdFechax);

   IF (tcFormat = '%D/%M/%Y') THEN
      ftcFecha = LPAD(lnDia, 2, '0') || '/' || LPAD(lnMes, 2, '0') || '/' || lnAno;

   IF (tcFormat = '%M/%D/%Y') THEN
      ftcFecha = LPAD(lnMes, 2, '0') || '/' || LPAD(lnDia, 2, '0') || '/' || lnAno;

   IF (tcFormat = '%Y/%M/%D') THEN
      ftcFecha = lnAno || '/' || LPAD(lnMes, 2, '0') || '/' || LPAD(lnDia, 2, '0');

   IF (tcFormat = '%D-%M-%Y') THEN
      ftcFecha = LPAD(lnDia, 2, '0') || '-' || LPAD(lnMes, 2, '0') || '-' || lnAno;

   IF (tcFormat = '%M-%D-%Y') THEN
      ftcFecha = LPAD(lnMes, 2, '0') || '-' || LPAD(lnDia, 2, '0') || '-' || lnAno;

   IF (tcFormat = '%Y-%M-%D') THEN
      ftcFecha = lnAno || '-' || LPAD(lnMes, 2, '0') || '-' || LPAD(lnDia, 2, '0');

   IF (tcFormat = 'NAME') THEN BEGIN
      lcMes = '';
      lcMes = IIF(lnMes =  1, 'Enero'     , lcMes);
      lcMes = IIF(lnMes =  2, 'Febrero'   , lcMes);
      lcMes = IIF(lnMes =  3, 'Marzo'     , lcMes);
      lcMes = IIF(lnMes =  4, 'Abril'     , lcMes);
      lcMes = IIF(lnMes =  5, 'Mayo'      , lcMes);
      lcMes = IIF(lnMes =  6, 'Junio'     , lcMes);
      lcMes = IIF(lnMes =  7, 'Julio'     , lcMes);
      lcMes = IIF(lnMes =  8, 'Agosto'    , lcMes);
      lcMes = IIF(lnMes =  9, 'Septiembre', lcMes);
      lcMes = IIF(lnMes = 10, 'Octubre'   , lcMes);
      lcMes = IIF(lnMes = 11, 'Noviembre' , lcMes);
      lcMes = IIF(lnMes = 12, 'Diciembre' , lcMes);
      ftcFecha = lnDia || ' de ' || lcMes || ' de ' || lnAno;
   END

   SUSPEND;

END;

Por supuesto que podrías agregar más formatos de fecha si lo deseas, en el Listado 1. se mostraron algunas de las posibilidades.

Ahora, cuando queremos ver a una fecha con alguno de los formatos que definimos en nuestro stored procedure lo haríamos así:

Listado 2.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, '%D/%M/%Y') F
      ON 1 = 1

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

Listado 3.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, '%Y-%M-%D') F
      ON 1 = 1

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

Listado 4.

SELECT
   F.ftcFecha
FROM
   RDB$DATABASE
LEFT JOIN
   DATE_FORMAT(CURRENT_DATE, 'NAME') F
      ON 1 = 1

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

Conclusión:

Aunque Firebird nativamente no tiene una función DATE_FORMAT o equivalente, es bastante sencillo realizar una, tal y como hemos podido ver en este artículo. Y aunque en nuestros ejemplos hemos usado la tabla RDB$DATABASE no es ninguna obligación utilizarla, puedes usar cualquier tabla que desees, también cualquier fecha que desees, y también cualquier formato que desees. Si ese formato no se encuentra en el Listado 1., pues lo agregas y listo.

Artículos relacionados:

Usando un stored procedure como una función

El índice del blog Firebird21

El foro del blog Firebird21

Fechas aleatorias

Deja un comentario

A veces cuando estamos realizando pruebas necesitamos datos aleatorios, o sea datos que pueden ser cualesquiera, no podemos anticipar cuales serán.

Supongamos que necesitamos fechas aleatorias. Para ello, como de costumbre usaremos la función RAND(). Esta función nos devolverá un número entre 0 y 0,999999. También usaremos la función FLOOR() que nos devuelve la parte entera de un número. También usaremos la función DATEADD() que le suma un número a una fecha para devolvernos una nueva fecha, la fecha original más la cantidad de días que le hemos sumado.

Veamos algunos ejemplos:

Listado 1. Fechas entre el 1 de enero de 2017 y el 31 de marzo de 2017

SELECT
   DATEADD(FLOOR(90 * RAND()) DAY TO DATE '2017-JAN-01')
FROM
   RDB$DATABASE

Explicación:

La cantidad de días posibles es 90, así que multiplicamos a RAND() por 90. La cantidad de días debe ser un número entero, así que usamos la función FLOOR(), porque la función RAND() nos devuelve un número con decimales. Lo que estamos sumando son días así que usamos DAY, la fecha la expresamos como un string así que debemos convertirla a tipo fecha con DATE.

En síntesis, lo que estamos diciendo es: muéstrame una fecha aleatoria entre el 1 de enero de 2017 y el 31 de marzo de 2017

Listado 2. Fechas entre el 1 de enero de 2017 y el 31 de diciembre de 2017

SELECT
   DATEADD(FLOOR(365 * RAND()) DAY TO DATE '2017-JAN-01')
FROM
   RDB$DATABASE

Explicación:

Como la cantidad de días posibles es 365, entonces multiplicamos a 365 por RAND()

Listado 3. Fechas entre el 1 de abril de 2017 y el 30 de abril de 2017

SELECT
   DATEADD(FLOOR(30 * RAND()) DAY TO DATE '2017-APR-01')
FROM
   RDB$DATABASE

Explicación:

Como la cantidad de días posibles es 30, entonces multiplicamos 30 por RAND(). Como la fecha inicial es el 1 de abril de 2017, entonces escribimos esa fecha después de DATE.

Artículos relacionados:

La función DATEADD()

La función FLOOR()

La función RAND()

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries