Almacenamiento de las columnas de tipo BLOB

Deja un comentario

En Firebird tenemos la posibilidad de almacenar (guardar) archivos dentro de las bases de datos. Podemos usar esta característica para guardar documentos de texto, hojas de cálculo, gráficos, fotografías, canciones, vídeos, etc.

Para ello, declaramos a la columna como de tipo BLOB (Binary Large OBjets), o sea: “objetos binarios de gran tamaño”.

¿Dónde se guardan esos objetos binarios de gran tamaño?

Si declaramos a una columna como siendo de tipo CHAR, VARCHAR, SMALLINT, INTEGER, etc., es en esa misma columna donde se guardan sus valores. Pero con las columnas de tipo BLOB no sucede así. En este caso en la columna se guarda un puntero (un número que indica una dirección) a la ubicación física del archivo BLOB.

(Algo similar ocurre en los xBase: dBase, Visual FoxPro, etc., allí se les llama “campos memo” y se guardan en archivos distintos a las tablas .DBF)

Firebird hace lo siguiente:

  • Si el archivo BLOB cabe en la misma página que su fila (registro), se guarda en esa página. Recuerda que los tamaños de página pueden ser de 4.096, 8.192, 16.384 bytes. Como en general los archivos BLOB tienen un tamaño mayor a 16.384 bytes entonces es muy raro que sean guardados en la misma página que su fila respectiva.
  • Si el archivo BLOB no cabe en la misma página que su fila (registro) entonces se guarda en otra o en otras páginas. Estas páginas son del tipo “overflow”. Recordarás que todo en Firebird se guarda dentro de páginas. Los archivos BLOB no son la excepción. Esas “páginas de overflow” siempre están relacionadas con una tabla y por lo tanto son localizadas a través de las filas (registros) de esa tabla. El Firebird sabe a cual tabla pertenece cada “página de overflow” porque en la cabecera de la “página de overflow” coloca ese dato, así también como la fila (registro) que le corresponde y la secuencia en que se encuentra (si un archivo BLOB ocupa varias páginas el Firebird debe poder saber cual es la primera página, cual la segunda, cual la tercera, etc.)

Conclusión:

Las columnas de tipo BLOB normalmente se utilizan para guardar archivos dentro de ellas. Esos archivos como todo en Firebird se guardan dentro de páginas de la Base de Datos. Si el archivo BLOB es pequeño entonces podría guardarse en la misma página que su fila respectiva, pero lo normal es que el archivo BLOB sea más grande que el tamaño de una página y en ese caso se guarda en páginas llamadas de “overflow”. En la cabecera de las páginas de overflow se encuentran los datos que permiten saber a cual  tabla pertenecen, a cual fila de esa tabla, y la posición de esa página con relación a las demás páginas de ese archivo BLOB.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Entendiendo las páginas de la Base de Datos

Deja un comentario

El Firebird guarda todos los datos en “páginas”. Nada hay dentro de una Base de Datos que no esté dentro de una página.

¿Qué es una página?

Es una cantidad predefinida y fija de bytes que son tratados como una unidad.

 ¿Cuál es el tamaño en bytes de una página?

Es el tamaño que se definió cuando se creó la Base de Datos. Si no se especificó un tamaño entonces tendrá el valor por defecto que en Firebird 2.5 es de 4096 bytes. Los tamaños posibles son los siguientes:

  • 4096 bytes
  • 8192 bytes
  • 16384 bytes

¿Se puede cambiar el tamaño en bytes de las páginas?

Sí, se puede, para eso se debe usar el programa GBAK. Al restaurar un backup se puede especificar el tamaño que tendrán las páginas de la Base de Datos restaurada. La opción para ello es -page_size [tamaño], por ejemplo: -page_size 8192

Recuerda que el tamaño de las páginas de la Base de Datos original no cambia, el que cambia es el tamaño de las páginas de la Base de Datos restaurada.

¿Y qué ocurre si especifico un tamaño que no sea ninguno de los anteriores?

Entonces el Firebird usará uno de los anteriores. Si el tamaño que especificaste es menor que 4096, usará 4096. Si es mayor que 4096, usará el tamaño predefinido que sea menor al que especificaste. Por ejemplo, si especificaste 16000, usará 8192 porque 8192 es menor que el tamaño que especificaste.

 ¿Y cómo puedo saber el tamaño de las páginas de mi Base de Datos?

Hay dos formas:

1. Usando el programa GSTAT con la opción -h, como vemos a continuación:

PAGES01

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

El número que verás a continuación de “Page size” siempre será uno de los siguientes: 4096, 8192, 16384

2. Haciendo un SELECT a la tabla MON$DATABASE

SELECT
   MON$PAGE_SIZE
FROM
   MON$DATABASE

NOTA: Versiones antiguas de Firebird también permitían 1024 y 2048, pero ahora esos tamaños ya son obsoletos.

¿Cuántas clases de páginas distintas hay?

Aunque todas las páginas tienen exactamente el mismo tamaño, se las utiliza para distintas cosas. Para saber en que se utiliza cada página tienen un número hexadecimal que las identifica al cual se le llama “tipo de página”.

  • 0x01. Es la Header Page (página de cabecera) y solamente hay una. En ella se guardan datos tales como: el tamaño de las páginas, la versión del ODS, la transacción más antigua, la última transacción activa, la siguiente transacción, etc.
  • 0x02. Es una Page Inventory Page (página de inventario). Puede haber varias. Su sigla es PIP. Siempre la primera PIP está a continuación de la Header Page. Se usa para saber cuales son las páginas que tiene actualmente la Base de Datos y si están libres para ser usadas (o sea, “disponibles”) o no.
  • 0x03. Es una Transaction Inventory Page (página de inventario de las transacciones). Siempre hay al menos una. Aquí se guardan el número de las transacciones y su estado (activa, limbo, confirmada, desechada). El mayor número posible de transacción es 2.147.483.647, cuando una Base de Datos alcanzó a ese número de transacciones se debe hacer un ciclo backup/restore para que el número de la transacción regrese a 1.
  • 0x04. Es una Pointer Page (página de punteros). Hay al menos una por cada tabla (de metadatos o del usuario) que tiene la Base de Datos. En la Pointer Page se guardan los números todas las páginas de datos que pertenecen a una sola tabla. Eso significa que en una página de datos solamente puede haber filas de una sola tabla, nunca se mezclan filas de una tabla con las de otra tabla en una página de datos. Las tablas grandes tienen muchas Pointer Page.
  • 0x05. Es una Data Page (página de datos). Hay al menos una por cada tabla que tiene filas (tanto sean metadatos como del usuario). Todos los datos de esta página corresponden a una sola tabla.
  • 0x06. Es una Index Root Page (página del índice raíz). Cada tabla de la Base de Datos tiene una Index Root Page, la cual describe los índices que tiene esa tabla. Aunque una tabla no tenga índices igual tiene una Index Root Page.
  • 0x07. Es una Index B-Tree Page (página de índice B-Tree). Si una tabla no tiene índices, no tendrá una página de tipo 0x07. Todos los datos de una página Index B-Tree corresponden a un solo índice de una sola tabla.
  • 0x08. Es una BLOB Data Page (página de datos para columnas de tipo BLOB). Solamente existen para tablas que tienen al menos una columna de tipo BLOB. En esta página se guarda el contenido de esas columnas. Todos los datos corresponden a una sola columna de una sola tabla.
  • 0x09. Es una Generator Page (página de generadores). Hay al menos una por cada Base de Datos, aunque ningún generador (también llamado “secuencia”) haya sido definido.
  • 0x0A. Es una Write Ahead Log Page (página de escribir por delante el log). Hay al menos una por cada Base de Datos, pero actualmente no se la está usando, es un desperdicio de espacio, y probablemente ya no exista en Firebird 3.0

¿Qué sucede con una página de datos cuando las filas que guardaba son eliminadas?

Supongamos que en una página se encuentran las filas de la tabla VENTAS y escribes DELETE FROM VENTAS borrando así a todas esas filas. ¿Qué pasa con la página, se la elimina de la Base de Datos? No, permanece ahí, pero en la PIP (Page Inventory Page) se la marca como “disponible”. O sea que puede ser usada nuevamente, y cuando el Firebird necesite una nueva página usará a una de las “disponibles”. ¿Por qué eso? Porque para el Firebird es mucho más rápido usar una página “disponible” (es decir, libre, que nadie la está usando) que alojar una nueva página en el disco duro.

De la misma manera, cuando se hace una “recolección de basura” pueden quedar muchas páginas “disponibles”. Eso es debido a que la “recolección de basura” no elimina a esas páginas de la Base de Datos, sino que las marca como “disponibles”.

Por supuesto que esto implica que si hay muchas páginas “disponibles” hay mucho espacio dentro de la Base de Datos que no está siendo usado para algo útil. Si quieres disminuir el tamaño de la Base de Datos puedes hacer un ciclo backup/restore para que todas esas páginas “disponibles” desaparezcan físicamente.

Resumiendo:

Todo dentro de una Base de Datos de Firebird se guarda dentro de una página, nada está afuera de una página. Todas las páginas tienen el mismo tamaño, aunque se las use para cosas distintas. Ese tamaño se puede especificar al crear la Base de Datos o puede ser cambiado cuando se restaura un backup con el programa GBAK. Se puede usar el programa GSTAT con la opción -h o un SELECT a la tabla MON$DATABASE para conocer el tamaño de las páginas. Cada página dentro de la Base de Datos tiene un número hexadecimal que la identifica y al cual se conoce como el “tipo de página”. Todos los datos dentro de una página corresponden a la misma cosa, nunca se mezclan. Por ejemplo, si una página se usa para guardar las filas de la tabla VENTAS en esa página solamente habrá filas de la tabla VENTAS, jamás habrá en esa página filas de la tabla EMPLEADOS. Cuando todo el contenido de una página de datos es eliminado a esa página se la marca como “disponible” y puede ser reutilizada; si se desea eliminar a todas las páginas “disponibles” hay que hacer un ciclo backup/restore.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Guardando las columnas BLOB en tablas separadas

5 comentarios

Las columnas de tipo BLOB nos permiten guardar en ellas contenido muy grande: típicamente mayor a 32765 bytes. ¿Por qué ese número? Porque una columna de tipo VARCHAR puede guardar hasta 32765 bytes entonces usaríamos una columna BLOB cuando necesitamos un espacio de almacenamiento mayor.

El contenido de las columnas de tipo VARCHAR puede ser indexado, el contenido de las columnas de tipo BLOB no puede ser indexado.

Entonces, si estamos seguros de que la columna siempre contendrá menos de 32765 bytes lo conveniente es que sea de tipo VARCHAR, para poder indexarla cuando lo deseemos.

Ok, ya estudiamos el caso y decidimos que necesitamos una columna de tipo BLOB, ¿en cuál tabla la guardamos?

Aquí tenemos dos posibilidades:

  1. Guardarla en la misma tabla que los demás datos
  2. Guardarla en una tabla separada

Por ejemplo, tenemos una tabla de EMPLEADOS y queremos guardar las fotografías de los empleados. O tenemos una tabla de VENTAS y queremos guardar las facturas escaneadas.

Si elegimos la posibilidad 1. entonces debemos olvidarnos por completo de escribir algo como SELECT * FROM EMPLEADOS, o como SELECT * FROM VENTAS, porque el tiempo que se demorará en obtener el conjunto resultado puede ser muy grande.

Por el contrario, si elegimos la posibilidad 2. nunca tendremos ese problema ya que la columna BLOB se encuentra en otra tabla y para leer su contenido debemos específicamente escribir un JOIN.

Elegir la posibilidad 1. tiene las ventajas de que nuestros SELECTs son más sencillos de escribir y que usamos menos espacio en el disco duro pero tiene la desventaja de que los resultados se obtienen más lentamente. Y nuestra prioridad siempre debe estar enfocada en conseguir la máxima velocidad posible.

Ejemplo. Guardar las fotografías de los empleados

BLOB1

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

En la Captura 1. vemos la estructura de la tabla EMPLEADOS.

BLOB2

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

Y en la Captura 2. vemos la estructura de la tabla EMPLEADOSFOT, que es la tabla donde guardamos las fotografías de los empleados.

Evidentemente al usar dos tablas estamos ocupando más espacio en el disco duro porque tenemos dos columnas (EMF_IDENTI y EMF_IDECAB en nuestro ejemplo) que no necesitaríamos si usáramos una sola tabla.

Al usar dos tablas cuando consultamos a la tabla de EMPLEADOS no recuperamos las fotografías, para recuperarlas debemos escribir un JOIN similar a éste:

SELECT
   E.EMP_IDENTI,
   E.EMP_NOMBRE,
   E.EMP_APELLD,
   F.EMF_FOTOGR
FROM
   EMPLEADOS    E
LEFT JOIN
   EMPLEADOSFOT F
      ON E.EMP_IDENTI = F.EMF_IDECAB

Empleamos la cláusula LEFT JOIN para poder obtener los datos de todos los empleados, inclusive los de aquellos que no tienen fotografías.

Conclusión:

Si necesitas tener columnas de tipo BLOB entonces lo conveniente y lo recomendable es guardarlas en tablas separadas. Relacionarás la tabla padre y la tabla hija con un identificador común para escribir la cláusula JOIN cuando necesites recuperar la columna de tipo BLOB, tal como se vio en el SELECT de arriba.

Si guardas los datos y la columna de tipo BLOB en una sola tabla entonces tus SELECTs serán más sencillos de escribir y ahorrarás espacio en el disco duro. Pero tus consultas serán más lentas.

Y tu prioridad debería ser tener consultas rápidas, ya que en esta época usar unos pocos bytes más no importa.

Artículos relacionados:

Usando columnas de tipo BLOB

El índice del blog Firebird21

 

Usando columnas de tipo BLOB

9 comentarios

Las letras BLOB significan: Binary Large OBject o en castellano: objetos binarios de gran tamaño.

Se refieren a un tipo de datos que podemos utilizar cuando declaramos una columna de una tabla.

¿Para qué se usan las columnas de tipo BLOB?

Para guardar objetos cuyo tamaño es o puede ser muy grande, por ejemplo:

  • Archivos de texto muy grandes
  • Documentos (.DOC, .PDF, etc.)
  • Hojas de cálculo (.XLS, etc.)
  • Gráficos y fotografías (.CAD, .CRW, .GIF, .JPG, .PNG, .TIFF, etc.)
  • Canciones y música (.MID, .MP3, .WAV, .WMA, etc.)
  • Vídeos (.AVI, .FLV, .MP4, .RMVB, WMV, etc.)
  • Páginas web (.HTM, .HTML, .CSS, etc.)
  • Y cualquier otro archivo

El tamaño de los archivos que se puede guardar en una columna de tipo BLOB es muy grande, no es ilimitada, pero sí muy grande.

¿Cuál es el tamaño máximo que puede tener un archivo que se guarda en una columna de tipo BLOB?

Eso depende del tamaño de la página de tu Base de Datos. La escala es la siguiente:

1 Kb —> 64 Mb

2 Kb —> 512 Mb

4 Kb —> 4 Gb (4.096 Mb)

8 Kb —> 32 Gb (32.768 Mb)

16 Kb —> 256 Gb (262.144 Mb)

Si usas Firebird 2.5 ó posterior se recomienda que el tamaño de las páginas de tus bases de datos sea de 4 Kb o más. Eso implica que cada archivo que guardes en una columna de tipo BLOB podrá tener un tamaño de 4Gb o más. Lo cual es más que suficiente para casi todos los casos porque ese tamaño es para cada archivo guardado, tus tablas pueden tener cientos o miles de archivos cada uno de ellos pesando varios Gigabytes.

¿Es preferible guardar el enlace a un archivo o el archivo mismo?

Las columnas de tipo BLOB te permiten guardar al archivo entero pero ¿cuál de esas alternativas es mejor?

  1. En la tabla guardar el enlace a un archivo
  2. En la tabla guardar el archivo completo

En Firebird se recomienda la opción 2. por las siguientes razones:

  • Cuando haces un backup también copias el archivo
  • Si se guarda el enlace al archivo entonces cualquiera podría borrarlo o modificarlo
  • Si se guarda el enlace al archivo no podrías saber quien lo agregó, lo modificó o lo borró

¿Se pueden usar funciones con las columnas de tipo BLOB?

Sí, si el contenido de una columna de tipo BLOB es texto entonces puedes usar las funciones de string, tales como CAST(), LOWER(), UPPER(), TRIM(), SUBSTRING(), etc. en esa columna. También las podrás concatenar y asignar a variables locales dentro de un stored procedure o de un trigger.

¿Se puede filtrar el contenido de las columnas de tipo BLOB?

Sí, puedes usar las cláusulas LIKE y CONTAINING en tu SELECT.

¿Se puede ordenar una consulta por una columna de tipo BLOB?

No, eso no puede hacerse, por la forma en que internamente están estructurados los índices del Firebird no es posible ordenar las columnas de tipo BLOB de acuerdo a su contenido. Eso implica que no puedes usar la cláusula ORDER BY.

Resumiendo:

  • Se puede usar la cláusula WHERE con una columna de tipo BLOB
  • No se puede usar la cláusula ORDER BY con una columna de tipo BLOB

¿Es rápido o es lento consultar columnas de tipo BLOB?

En general es bastante rápido pero si no estás seguro de que necesitarás el archivo lo recomendable es no especificarlo en la consulta. O sea que lo correcto es escribir:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS

Y escribir algo como:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE,
   PRD_FOTOGR
FROM
   PRODUCTOS

 Solamente cuando estás seguro de que necesitarás obtener la fotografía del producto.

¿Qué es el subtipo de una columna de tipo BLOB?

Es un número o una palabra que nos dice cual puede ser el contenido de esa columna, o sea que es lo que se puede guardar en esa columna.

Subtipo 0   —> se puede guardar cualquier cosa que se desee

Subtipo 1 —> se puede guardar texto

Subtipo Text —> es lo mismo que Subtipo 1, es un sinónimo

Número positivo —> reservado para uso futuro por el Firebird

Número negativo —> el usuario (o sea, nosotros) puede definir el subtipo

¿Se puede tener un dominio de tipo BLOB?

Sí, por supuesto, BLOB es un tipo de datos por lo tanto podemos tener dominios de ese tipo de datos, ejemplo:

CREATE DOMAIN
   D_COMENTARIOS
AS
   BLOB SUB_TYPE 1;

Entonces, si una columna se declara con el dominio D_COMENTARIOS en esa columna se podrán escribir millones de palabras, más que suficientes para cualquier comentario que el usuario pueda necesitar alguna vez.

¿Cómo se utilizan los subtipos del usuario?

Como ya sabes, si un subtipo tiene un número negativo entonces es un subtipo del usuario, y por lo tanto es el usuario quien decide que guardar en las columnas de tipo BLOB y de un subtipo negativo.

Por ejemplo, un usuario puede decir:

  • “el subtipo -1 lo usaré para guardar archivos .PDF”
  • “el subtipo -2 lo usaré para guardar archivos .DOC”
  • “el subtipo -3 lo usaré para guardar archivos .XLS”

Pero el Firebird no sabe eso (ni le interesa), el Firebird simplemente guarda los archivos y se los envía al Cliente cuando éste se los pide. Nada más que eso.

Por consiguiente es total responsabilidad del usuario saber lo que está guardando en cada subtipo negativo.

¿Y qué ocurre si no se especifica el subtipo?

Especificar el subtipo es opcional, no es obligatorio. Si no se especifica el Firebird lo trata como si fuera de subtipo 0, o sea datos de tipo binario.

Artículo relacionado:

El índice del blog Firebird21

PHP: guardando un archivo en un campo BLOB

1 comentario

Si estás programando con PHP y usando una Base de Datos de Firebird puedes encontrarte con la necesidad de guardar un archivo en un campo BLOB, aquí está una forma de hacerlo, colaboración de Jaume.

$f = realpath("factura.pdf");

$stream = fopen($f, "r");

$dpdf = stream_get_contents($stream);

fclose($stream);

$sql = "UPDATE OR INSERT INTO MiTabla (MiColumna1, MiColumna2) VALUES (MiValor1, :foo)";

try {
   $queri = $co->prepare($sql);
   $queri->bindParam(':foo', $dpdf);
   $queri->execute();
   $queri = NULL;
}
catch (PDOException $e) {
   $ok    = false;
   $queri = NULL;
   $inf   = $e->getMessage();
}

Artículo relacionado:

El índice del blog Firebird21

ASCII_VAL()

1 comentario

Descripción: Devuelve el código ASCII del caracter recibido como argumento

Tipo de resultado: SmallInt

Sintaxis:

ASCII_VAL(carácter)

carácter: un [VAR]CHAR o texto de tipo BLOB con una longitud máxima de 32767 bytes

  • Si el argumento es una cadena alfanumérica que tiene más de un carácter, el código ASCII del primer carácter es devuelto
  • Si el argumento es una cadena alfanumérica vacía, el número 0 es devuelto
  • Si el argumento es NULL, devuelve NULL

IMPORTANTE: Si la función externa ASCII_VAL() está declarada en tu Base de Datos, ella tendrá preferencia. Para que la función interna pueda usarse deberás borrar (DROP) o cambiar (ALTER) a la función externa.

.