Entendiendo a GSTAT (2)

Deja un comentario

Ya hemos empezado a entender a GSTAT en el artículo:

Entendiendo a GSTAT (1)

así que ahora continuaremos interiorizándonos más sobre este muy útil programa.

Opción -fetch

A veces, estamos trabajando en una computadora pero no estamos solos, hay algunos curiosos cerca nuestro que están mirando lo que estamos haciendo. Desde luego que si escribimos nuestra contraseña podrán verla … y recordarla … y usarla cuando no estemos presentes.

gstat01

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

Claro, podríamos pedirles que desaparezcan, que se vayan a otro lado, que dejen de curiosear, que se dediquen a hacer algo productivo bien lejos de nosotros, etc., pero eso no siempre es posible. Y entonces ¿qué hacemos?

La solución es guardar nuestra contraseña en un archivo de texto (que podría estar en un pen-drive, por ejemplo) y extraer la contraseña desde ese archivo de texto.

gstat02

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

Desde luego que el nombre del archivo puede ser cualquiera, no necesariamente FIREBIRD.TXT, y nuestra contraseña también puede ser cualquiera, no necesariamente contra99. Esto es solamente un ejemplo.

Recuerda que una contraseña que todos conocen es lo mismo que no tener contraseña.

gstat03

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

En la Captura 3. vemos que la contraseña se obtiene desde el archivo de texto FIREBIRD.TXT que se encuentra en el pen-drive colocado en la unidad G: y en la carpeta \SQL

De esta manera, siempre y cuando no dejemos nuestro pen-drive al alcance de los curiosos, ellos no podrán conocer cual es nuestra contraseña.

Esta, es una muy importante medida de seguridad que deberíamos acostumbrarnos a adoptar: nunca escribir la contraseña, siempre obtenerla de un archivo de texto.

gstat04

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

En la Captura 4. estamos viendo la forma correcta de usar al programa GSTAT. Nuestra contraseña la obtenemos de un archivo de texto y la salida del programa GSTAT la enviamos a otro archivo de texto. Esta es la forma mejor y más recomendable.

Artículos relacionados:

Entendiendo a GSTAT (1)

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

 

 

Anuncios

Buscando texto eficientemente dentro de un string grande

1 comentario

Cuando necesitamos buscar un texto dentro de un string podemos usar LIKE ‘%TextoBuscado%’ o también podemos usar CONTAINING. Ambos funcionarán muy bien, pero tienen un problema: si la cantidad de filas es muy grande o la columna donde puede encontrarse el texto que buscamos tiene muchos caracteres, puede ser muy lento, a veces inclusive extremadamente lento.

La idea para este artículo la obtuve de aquí:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

Listado 1.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna LIKE '%Asunción%'

Listado 2.

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna CONTAINING 'Asunción'

Tanto el SELECT del Listado 1. como el SELECT del Listado 2. harán bien su trabajo, pero como ya dijimos antes, si la cantidad de filas de MiTabla es muy grande o la cantidad de caracteres en MiColumna es muy grande, pueden ser muy lentos. ¿Por qué? Porque el Firebird nunca usará un índice en esos casos y por lo tanto deberá buscar secuencialmente en cada columna de cada fila la palabra que deseamos encontrar.

Si necesitamos gran velocidad en la búsqueda entonces debemos emplear otro aprovechamiento: usar un diccionario de palabras.

La idea es la siguiente: cada palabra que aparece en la columna MiColumna la guardamos en otra tabla, junto con su correspondiente Identificador. A esa columna le definiremos un índice y de esa manera las búsquedas serán rapidísimas.

Desde luego que hacer eso solamente se justificará si las búsquedas realizadas mediante el Listado 1. o el Listado 2. son lentas, en otro caso no vale la pena tomarse la molestia de hacerlo.

Para que nuestra técnica sea más inteligente también debemos tener en cuenta que a veces los usuarios se equivocan al escribir las palabras que están buscando. Por ejemplo, quieren buscar ‘Asunción’ pero escribieron ‘Asuncion’; o sea, sin el acento sobre la letra ‘o’. O escribieron ‘ASUNCION’, o sea todo en mayúsculas; o escribieron ‘Asumción’, o sea que en lugar de la letra ‘n’ pusieron la letra ‘m’.

Entonces lo que haremos será lo siguiente:

  1. Crear un dominio que acepte tanto mayúsculas como mínusculas (case insensitive) y que acepte tanto palabras acentuadas como no acentuadas (accent insensitive)
  2. Crear una tabla donde se guardarán las palabras y en la cual usaremos el dominio creado
  3. Crear un índice normal que usaremos para buscar palabras normales
  4. Crear un índice inverso que usaremos para buscar palabras invertidas
  5. Crear un stored procedure seleccionable que servirá para extraer todas las palabras de un texto
  6. Crear un trigger que ejecutará al stored procedure seleccionable y luego guardará cada palabra distinta en la tabla, junto con el Identificador de la fila donde se encuentra

Paso 1. Crear un dominio

Listado 3.

CREATE DOMAIN D_PALABRAS_CI_AI 
AS VARCHAR(40) 
CHARACTER SET ISO8859_1
COLLATE ES_ES_CI_AI;

Definimos este dominio como VARCHAR(40) porque suponemos que ninguna de las palabras tendrá más de 40 caracteres, si eso pudiera ocurrir entonces tendrías que aumentar el tamaño.

Paso 2. Crear la tabla donde se guardarán las palabras

palabras01

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

Nuestra tabla (bastante apropiadamente) se llama DICCIONARIO porque contendrá una lista de palabras.

En la columna DIC_TABLAX se guardará el nombre de la tabla que contiene las palabras que podríamos buscar. Eso porque podríamos tener varias tablas en las cuales sería útil realizar esta búsqueda.

En la columna DIC_COLUMN se guardará el nombre de la columna que contiene las palabras que podríamos buscar. Eso porque dentro de una misma tabla podríamos tener varias columnas que nos interesan.

En la columna DIC_IDECAB se guardará el Identificador de la fila que corresponde a la tabla DIC_TABLAX. De esa manera podremos saber en cual fila de la tabla DIC_TABLAX se encuentra la palabra buscada.

En la columna DIC_PALABR se guardará la palabra que puede ser buscada.

Paso 3. Crear un índice normal

Listado 4.

CREATE INDEX IDX_DICCIONARIO ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR)
);

Paso 4. Crear un índice inverso

Listado 5.

CREATE INDEX IDX_DICCIONARIO1 ON DICCIONARIO COMPUTED BY (DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR))
);

Paso 5. Crear un stored procedure seleccionable

Listado 6.

CREATE PROCEDURE SPG_HALLAR_PALABRAS(
   ftcTexto VARCHAR(32765))
RETURNS(
   ftcPalabra TYPE OF D_NOMBRE40)
AS
   DECLARE VARIABLE lnI        SMALLINT;
   DECLARE VARIABLE lnInicio   SMALLINT;
   DECLARE VARIABLE lnLongitud SMALLINT;
BEGIN

   lnI        = 1;
   lnInicio   = 1;
   ftcTexto   = ftcTexto || ' ';
   lnLongitud = CHARACTER_LENGTH(ftcTexto);

   WHILE (lnI <= lnLongitud) DO BEGIN
      IF(CAST(SUBSTRING(ftcTexto FROM lnI FOR 1) AS D_PALABRAS_CI_AI) NOT SIMILAR TO '[[:ALNUM:]]' AND POSITION(SUBSTRING(ftcTexto FROM lnI FOR 1) IN 'áéíóúñÁÉÍÓÚÑ') = 0) THEN BEGIN
         IF(lnI > lnInicio) THEN BEGIN
            ftcPalabra = SUBSTRING(ftcTexto FROM lnInicio FOR lnI - lnInicio);
            SUSPEND;
         END
         lnInicio = lnI + 1;
      END
      lnI = lnI + 1;
   END
END;

El stored procedure SPG_HALLAR_PALABRAS hallará cada una de las palabras contenidas en el texto que se le envíe como parámetro de entrada. Veamos algunos ejemplos:

Listado 7.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('Hoy es un día lluvioso')

Listado 8.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('        Hoy es un día lluvioso')

Listado 9.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('       Hoy         es         un día lluvioso')

Listado 10.

SELECT
   *
FROM
   SPG_HALLAR_PALABRAS('   Hoy es un        día lluvioso            ')

Tanto si ejecutamos el Listado 7., como el Listado 8., como el Listado 9., como el Listado 10., siempre obtendremos el mismo resultado, aún cuando a la frase original se le hayan agregado espacios en blanco al principio, en el medio, y al final del texto:

palabras02

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

Paso 6. Crear un trigger 

Listado 11.

CREATE TRIGGER PRODUCTOS_BIUD FOR PRODUCTOS
   ACTIVE BEFORE INSERT OR UPDATE OR DELETE
   POSITION 1
AS
BEGIN

   -- Se borran las filas que se habían insertado correspondientes a este Producto

   IF (UPDATING OR DELETING) THEN
      DELETE FROM
         DICCIONARIO
      WHERE
         DIC_TABLAX = 'PRODUCTOS' AND
         DIC_COLUMN = 'PRD_NOMBRE' AND
         DIC_IDECAB = OLD.PRD_IDENTI;

   -- Se insertan las nuevas filas, una fila por cada palabra de la columna PRD_NOMBRE

   IF (INSERTING OR UPDATING) THEN BEGIN
      INSERT INTO DICCIONARIO (DIC_TABLAX, DIC_COLUMN, DIC_IDECAB, DIC_PALABR)
         SELECT 'PRODUCTOS', 'PRD_NOMBRE', NEW.PRD_IDENTI, ftcPALABRA FROM SPG_HALLAR_PALABRAS(NEW.PRD_NOMBRE);

   END

END;

Para cada tabla que nos interese deberemos crear un trigger similar al mostrado en el Listado 11., de esa manera cada vez que se realice un INSERT, un UPDATE, o un DELETE a alguna fila de esa tabla que nos interesa, se actualizará también la tabla DICCIONARIO.

En el Listado 11. la tabla que nos interesa se llama PRODUCTOS, y dentro de esa tabla la columna que nos interesa se llama PRD_NOMBRE.

Eso significa que cada una de las palabras que coloquemos en la columna PRD_NOMBRE se insertará en la tabla DICCIONARIO para que podamos buscarla muy rápidamente.

Agregando filas a la tabla DICCIONARIO

Para verificar que todo funciona bien le agregaremos algunas filas a la tabla PRODUCTOS y al hacerlo también le estaremos agregando filas a la tabla DICCIONARIO.

Listado 12.

INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CC267', 'COCA COLA DE 1 LITRO RETORNABLE');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CP389', 'CERVEZA PILSEN DE 750 C.C.');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('LT224', 'Leche Trébol de 1 litro descremada');
INSERT INTO PRODUCTOS (PRD_CODIGO, PRD_NOMBRE) VALUES('CB357', 'Cerveza BUDWEISER 66 DE 1 ÑITRO');

Al insertar esas filas a la tabla PRODUCTOS nuestra tabla DICCIONARIO quedó así:

palabras03

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

Verificando que funciona

Ahora que tenemos todo listo lo único que nos falta comprobar es que funcione bien.

Mirando la Captura 3. podemos ver que la palabra “LITRO” está escrita en mayúsculas, en minúsculas, y también mal escrita (en la fila 24 dice “ÑITRO” en lugar de “LITRO”).

Entonces, ¿cómo podemos obtener rápidamente las 3 filas donde se encuentra la palabra ‘LITRO’?

Listado 13.

SELECT
   *
FROM
   DICCIONARIO
WHERE
   DIC_TABLAX || DIC_COLUMN || UPPER(DIC_PALABR) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || 'LITRO' OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('ITRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('TRO') OR
   DIC_TABLAX || DIC_COLUMN || REVERSE(UPPER(DIC_PALABR)) STARTING WITH 'PRODUCTOS' || 'PRD_NOMBRE' || REVERSE('RO')

Si ejecutamos el Listado 13., obtendremos:

palabras04

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

O sea, que tenemos todas las apariciones de la palabra ‘LITRO’ aún aquella que está mal escrita. Justamente para eso sirven las 3 últimas condiciones puestas en la cláusula WHERE, aquellas que usan la función REVERSE(). En el primer caso escribimos ‘ITRO’, eso significa que la primera letra puede estar mal escrita. En el segundo caso escribimos ‘TRO’, eso significa que las primeras dos letras pueden estar mal escritas. En el tercer caso escribimos ‘RO’, eso significa que las primeras tres letras pueden estar mal escritas.

¿Y qué tan eficiente es nuestro SELECT?

Veamos el PLAN que ejecutó el Firebird para saberlo.

palabras05

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

Como se puede ver en la Captura 5. para la primera línea de la cláusula WHERE usará el índice IDX_DICCIONARIO, y para la segunda, la tercera, y la cuarta líneas, usará el índice IDX_DICCIONARIO1.

O sea, exactamente lo que queríamos conseguir.

Veamos ahora la eficiencia del Listado 13. en forma gráfica.

palabras06

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

Está perfecto. Hay 3 filas que cumplen con la condición impuesta en la cláusula WHERE y hay 3 filas extraídas usando los índices.

Conclusión:

Normalmente podemos usar LIKE ‘%MiTextoBuscado%’ o CONTAINING ‘MiTextoBuscado’ cuando queremos obtener las filas que tienen a ‘MiTextoBuscado’ en ellas. Sin embargo, hay ocasiones en que usar dichas sub-cláusulas puede ser extremadamente lento: funciona bien, pero son muy lentas.

Para esos casos lo conveniente es tener un diccionario de palabras, que servirá para indicarnos en cuales filas está el texto que buscamos.

En este artículo hemos visto un método que podemos utilizar para conseguir nuestro objetivo: búsquedas muy rápidas del texto, aunque la tabla tenga muchísimas filas o aunque tenga columnas que contienen muchísimas palabras.

Artículos relacionados:

https://blog.cincura.net/233577-poor-mans-full-text-using-psql-only-on-firebird/

La función REVERSE()

El índice del blog Firebird21

El foro 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