Optimizando un SELECT que compara columnas de la misma tabla

En general, debemos tener a todas las columnas de todas nuestras tablas normalizadas. Eso es lo correcto y es lo recomendable. Sin embargo, hay ocasiones en que desnormalizar las columnas es conveniente.

Una de esas ocasiones es cuando debemos escribir un SELECT que en la cláusula WHERE compara el contenido de dos columnas. Veamos un ejemplo.

Tenemos la tabla PRODUCTOS con la siguiente estructura:

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

Y queremos saber si hay productos cuyo precio de venta es menor que su precio de costo, así que escribimos el siguiente SELECT.

Listado 1.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA < PRD_PRECTO

La consulta nos mostrará el resultado correcto, pero si analizamos su rendimiento, encontraremos que no ha usado un índice.

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

El problema es que no podemos tener un índice que pueda ser usado en casos como este. O sea, que no podemos tener índices para:

• Comparar dos columnas de la misma tabla por =
• Comparar dos columnas de la misma tabla por <
• Comparar dos columnas de la misma tabla por >
• Comparar dos columnas de la misma tabla por <=
• Comparar dos columnas de la misma tabla por >=
• Comparar dos columnas de la misma tabla por <>

Si la tabla tiene pocas filas, eso no es un problema, Firebird es muy rápido para devolver el resultado de los SELECT. Pero si la tabla tiene muchas filas, allí ya es otro tema.

¿Y cómo podemos hacer para mejorar la velocidad de nuestro SELECT?

La solución es crear una columna que contenga la diferencia entre las dos columnas que nos interesan. La estructura de la tabla PRODUCTOS quedaría entonces así:

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

Para mantener actualizada a la columna PRD_DIFERE podríamos escribir un trigger como el siguiente:

Listado 2.

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

   NEW.PRD_DIFERE = NEW.PRD_PREVTA - NEW.PRD_PRECTO;

END;

Y para usar un índice entonces deberemos crearlo.

Listado 3.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_DIFERE);

Y si ahora escribimos el SELECT del Listado 1. modificado para que utilice a la columna PRD_DIFERE, tendríamos:

Listado 4.

SELECT
   *
FROM
   PRODUCTOS
WHERE
  PRD_DIFERE < 0

Queremos verificar si ahora se está usando un índice, así que miramos el rendimiento y encontramos:

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

Y comprobamos que sí, efectivamente ahora se usa un índice, y por lo tanto nuestro SELECT será mucho más rápido que antes.

Conclusión:

En general debemos tener a todas las columnas de todas nuestras tablas normalizadas, pero hay excepciones, como el caso mostrado en este artículo. Eso se debe a que el Firebird no utiliza índices cuando comparamos el contenido de una columna con el contenido de otra columna. La solución es crear una columna adicional que contendrá la diferencia entre los valores de las columnas que necesitamos comparar.

Desde luego que comparar precio de costo con precio de venta es sólo un ejemplo. También podemos comparar importe vendido contra importe cobrado, importe comprado contra importe pagado, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Planillas EXCEL dinámicas con Firebird (1)

Si queremos darle un muy alto valor agregado a nuestras aplicaciones entonces podemos proveerles de planillas Excel dinámicas. A nuestros clientes les encantará eso.

¿Qué es una planilla Excel dinámica?

Una planilla cuyos datos no fueron introducidos a mano sino que provienen de una fuente externa, en nuestro caso de una Base de Datos de Firebird.

Pero lo interesante aquí es que si cambian los datos de nuestra Base de Datos, también cambia la planilla Excel, automáticamente. Y si cambiamos el valor de algunas celdas en nuestra planilla Excel, entonces obtendremos una planilla Excel actualizada.

Muy interesante ¿verdad?

En este primer artículo de la serie veremos como obtener datos de las tablas y de las vistas que tenemos en nuestra Base de Datos de Firebird. En el siguiente artículo veremos algo quizás más interesante: cambiar el valor de una celda en la planilla Excel y que la planilla se actualice automáticamente.

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

El Diagrama 1. nos muestra el proceso.

1. Es nuestra Base de Datos creada con Firebird
2. Usamos OLEDB u ODBC para enviar los datos
3. Un archivo de conexión define  toda la información que se necesita para acceder y recuperar los datos de nuestra Base de Datos
4. La información de la conexión es copiada desde el archivo de conexión hacia la planilla Excel
5. Los datos son copiados dentro de la planilla Excel y ya pueden ser usados como si hubieran sido introducidos manualmente

Usando ODBC para la conexión

1. El driver ODBC de Firebird debe estar instalado en la computadora donde se encuentra la Base de Datos
2. Hay que definir un DSN (Data Source Name) para que la conexión pueda realizarse: Inicio | Panel de Control | Herramientas administrativas | Orígenes de datos ODBC | DSN de usuario | Agregar … | Firebird/Interbase driver | completar los datos pedidos

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

Para verificar que la conexión puede realizarse exitosamente es aconsejable hacer clic sobre el botón «Probar conexión». Si todo está ok veremos algo así:

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

Conectando la planilla Excel a la Base de Datos de Firebird

1. Abrir Excel
2. Crear una nueva planilla
3. Datos | De otras fuentes | Desde Microsoft Query | elegimos el DSN de nuestra Base de Datos

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

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

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

Como podemos ver en la Captura 5., el Excel nos muestra los nombres de todas las tablas y de todas las vistas que se encuentran en nuestra Base de Datos. Para ver solamente tablas o solamente vistas, podemos hacer clic en Opciones, como vemos a continuación:

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

y si hacemos clic sobre el signo + que se encuentra a la izquierda de las tablas y de las vistas, veremos los nombres de las columnas pertenecientes a la tabla o a la vista elegida.

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

Para elegir cuales de esas columnas queremos ver en nuestra planilla Excel, la seleccionamos y luego hacemos clic en el botón que la trasladará a «Columnas en la consulta:»

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

A continuación podemos Filtrar los datos y elegir el Criterio de ordenación y pedirle que muestre los datos en una planilla Excel.

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

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

Y ahora viene lo más interesante de todo, si elegimos «Informe de tabla dinámica» o si elegimos «Informe de gráfico y tabla dinámicos» entonces cada vez que se cambien los datos de nuestra Base de Datos también se cambiarán en la planilla Excel.

Algunos puntos a considerar

a) Los datos en la tabla dinámica están resumidos, o agrupados por filas. Varias filas de los datos originales ocuparán una sola fila en la planilla Excel

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

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

En la Captura 11. podemos ver los datos originales que tenemos en la tabla de nuestra Base de Datos. En la Captura 12. vemos la planilla Excel que fue creada a partir de esos datos. Como puedes ver, las filas se agruparon por fechas, y en las columnas numéricas se muestran las sumas de dichas columnas. Eso muchas veces no tiene sentido (como sumar los años en la columna ASC_ANOEJE, año del ejercicio contable, por ejemplo) pero es lo que hace Excel.

Para ver las filas originales, no agrupadas, podemos hacer clic sobre una de las filas de la planilla, clic con el botón derecho, y luego elegir la opción «Mostrar detalles».

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

En este caso, como el cursor está sobre la fila que tiene fecha 03/01/2014, las filas de esa fecha que tenemos en nuestra tabla original serán las mostradas.

 

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

b) Los resultados se crean en una nueva hoja. Un detalle a notar, es que el resultado de esa consulta fue creado en una nueva hoja de la planilla, no en la hoja que estábamos usando.

c) Se puede ver el detalle de todas las filas importadas. Para conseguirlo, arrastramos los nombres de las columnas que se encuentran en «Etiquetas de fila» hacia «Etiquetas de columna».

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

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

y luego continuamos igual que en la Captura 13.

Actualizando la planilla Excel

Por defecto, el contenido de la tabla dinámica que creamos en Excel no se actualiza cuando cambian los datos en el origen (o sea, en nuestra Base de Datos del Firebird)

Si el contenido de nuestra tabla en la Base de Datos de Firebird cambió, podemos mostrar los datos actualizados en la planilla Excel. Tenemos tres opciones:

a) Actualización manual. Colocamos el mouse en cualquier celda de la tabla que insertamos y luego clic con el botón derecho y elegimos la opción Actualizar

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

Si después de hacer clic en Actualizar volvemos a pedirle que nos muestre los detalles, como ya vimos en la Captura 13., entonces nuestra planilla Excel mostrará los datos actualizados.

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

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

También se puede actualizar manualmente la planilla Excel haciendo clic sobre cualquier celda de la tabla dinámica, luego en Opciones | Actualizar | Actualizar o Actualizar todo

b) Actualización automática. También podemos conseguir que la planilla Excel se actualice automáticamente, para ello hacemos clic en cualquier celda de la tabla dinámica creada, luego Opciones | Cambiar origen de datos | Propiedades de conexión … | elegimos el tiempo deseado

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

En la Captura 20. se pide que la planilla Excel sea actualizada cada 1 minuto, desde luego que tú puedes elegir otro tiempo, esto es sólo un ejemplo.

IMPORTANTE: La hoja creada con «Mostrar detalles», tal como vimos en la Captura 13. no cambiará, solamente cambia la hoja original.

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

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

En la Captura 21. vemos el contenido original de la tabla, se cambió un valor en la columna ASC_COTIZA en la Base de Datos del Firebird y un minuto después vemos que la planilla Excel se actualizó automáticamente, tal como podemos observar en la Captura 22.

c) Actualizar al abrir la planilla Excel. Si queremos que cada vez que se abra la planilla Excel muestre a sus datos actualizados, debemos marcar la casilla «Actualizar al abrir el archivo» que se muestra en la Captura 20.

Conclusión:

Poder enviar el resultado de consultar a nuestras tablas y a nuestras vistas de una Base de Datos del Firebird a una planilla Excel les resultará muy útil a nuestros clientes. Las tablas dinámicas muestran los datos agrupados y permiten que esos datos se actualicen cuando la tabla subyacente del Firebird se actualice. Eso es muy bueno para poder tomar buenas decisiones empresariales.

Hay muchísimo más que aprender sobre las tablas dinámicas de Excel. Es muy aconsejable que busques información en Internet sobre ellas, te resultará muy útil para que tus aplicaciones se destaquen sobre la competencia.

Artículos relacionados:

Tablas dinámicas en Excel

El índice del blog Firebird21

El foro del blog Firebird21

Limitando los derechos de ejecución de los usuarios

Una tarea muy importante que tenemos cuando administramos una Base de Datos es limitar los derechos que los distintos usuarios tienen sobre ella. De esa manera impediremos que vean o hagan algo que no deberían ver ni hacer.

Ejemplo:

Tenemos un stored procedure llamado MiStoredProcedure el cual hace un SELECT a una tabla llamada MiTabla. El usuario ELIZABETH debe tener el derecho de ejecutar ese stored procedure. ¿Cómo le otorgamos ese derecho?

Intento 1:

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

Cuando ELIZABETH quiera ejecutar a MiStoredProcedure obtendrá un mensaje de error: «no permission for read/select access to TABLE MiTabla»

¿Por qué el error?

Porque MiStoredProcedure necesita permiso para realizar el SELECT a la tabla MiTabla y no tiene ese permiso.

Intento 2:

   GRANT SELECT ON MiTabla TO ELIZABETH;

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

Ahora sí ELIZABETH podrá ejecutar a MiStoredProcedure, no tendrá problemas para hacerlo, pero … hay algo que está mal aquí. ¿Lo has descubierto?, ¿qué es lo que está mal?

Lo que está mal es que ELIZABETH podrá escribir por su cuenta algo como:

SELECT
   *
FROM
   MiTabla;

O sea, podrá conocer todo el contenido de MiTabla y eso puede ser muy inseguro. Por ejemplo, si MiStoredProcedure recibe como parámetro de entrada el Identificador de un Cliente y devuelve el saldo de ese cliente, solamente ese dato queremos que ELIZABETH conozca. Pero si tiene derecho de SELECT sobre la tabla CLIENTES podrá conocer todos los datos de todos los clientes (nombres, direcciones, teléfonos, e-mails, etc.) y no queremos que algo así pueda suceder. Puede ser muy peligroso.

Intento 3:

   GRANT SELECT ON MiTabla TO PROCEDURE MiStoredProcedure;

   GRANT EXECUTE ON PROCEDURE MiStoredProcedure TO ELIZABETH;

   COMMIT;

La diferencia con el Intento 2 es que aquí el derecho de SELECT no se le otorgó a ELIZABETH sino que se le otorgó a MiStoredProcedure.

Como ELIZABETH tiene derecho de ejecución sobre MiStoredProcedure entonces podrá ejecutarlo cuando quiera, pero sin embargo si escribe:

SELECT
   *
FROM
   MiTabla;

Recibirá el mensaje de error: «no permission for read/select access to TABLE MiTabla». ¿Por qué? Porque ella no tiene permiso de SELECT sobre MiTabla. Ese permiso lo tiene MiStoredProcedure, pero no lo tiene ELIZABETH.

Conclusión:

Mantener a los datos seguros y confidenciales puede ser muy importante en muchos casos, si nosotros somos buenos profesionales debemos tomar las medidas de seguridad apropiadas para que ningún usuario tenga la posibilidad de hacer aquello que no debería hacer.

En general, otorgarle a un usuario o a un rol derechos (permisos) sobre una tabla es un gran error.

Los derechos (permisos) deben otorgarse a las vistas y a los stored procedures, nunca a los usuarios ni a los roles.

De esta manera tendremos bien limitado lo que cada usuario puede hacer, y no nos encontraremos con sorpresas desagradables.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Copiando completamente o parcialmente una Base de Datos (1)

A veces puedes querer copiar una Base de Datos completa, con todo su contenido, pero a veces puede interesarte copiar solamente una parte de ella. Por lo tanto, tus opciones serían:

1. Copiarla totalmente. Con todos sus metadatos y todos sus datos
2. Copiar solamente los metadatos. Es decir, su estructura
3. Copiar todos los metadatos y el contenido de algunas tablas
4. Copiar algunos metadatos y el contenido de algunas tablas

Como el tema es largo, será tratado en 2 artículos. En este veremos como realizar esas tareas manualmente, o sea sin usar un programa de administración gráfica y en el siguiente artículo usaremos un programa de administración gráfica.

Opción 1. Copiarla totalmente. Con todos sus metadatos y todos sus datos

Para este caso lo más recomendable es hacer un backup usando el programa GBAK. ¿Por qué es lo más recomendable? porque al hacer un backup con GBAK se elimina la basura que contenía la Base de Datos original y en el backup no hay basura, ni índices (que serán creados cuando se restaure el backup), y el identificador de todas las transacciones del backup es puesto en cero.

GBAK -b MiBaseDatos.FDB MiBackup.FBK -user SYSDBA -password masterkey

Si el Servidor del Firebird no está funcionando también se puede copiar la Base de Datos con el Explorador de Windows o algún programa similar. ¿Por qué el Servidor del Firebird no debe estar funcionando? Porque si lo está y alguien está conectado, la Base de Datos puede corromperse. Y si nadie está conectado existe el riesgo (pequeño, pero existe) de que el Servidor esté realizando alguna tarea en la Base de Datos (por ejemplo, un sweep). Por lo tanto, copiar con el Explorador del Windows solamente es seguro si el Servidor del Firebird está apagado.

Opción 2. Copiar solamente los metadatos. Es decir, su estructura

El programa GBAK tiene la opción -m la cual copia solamente los metadatos. Su sintaxis es:

GBAK -b -m MiBaseDatos.FDB MiBackup.FBK -user SYSDBA -password masterkey

Opción 3. Copiar todos los metadatos y el contenido de algunas tablas

Para este caso lo mejor es crear un archivo de script con el contenido completo de la Base de Datos y luego eliminar lo que no nos interesa, modificar lo que queremos cambiar, y dejar como está a lo demás.

El programa ISQL tiene una opción -extract que sirve para crear un script de toda la Base de Datos.

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

En la Captura 1. vemos que todo el contenido de la Base de Datos DEISY.FDB fue copiado al archivo de script llamado DEISY.SQL

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

El archivo de script DEISY.SQL por supuesto que es muchísimo más largo, en la Captura 2. se muestran solamente las primeras líneas.

Ahora, tenemos la posibilidad de eliminar lo que ya no nos interesa tener y de modificar cualquier cosa. Podemos cambiar el nombre o el contenido de los dominios, de las tablas, de los stored procedures, etc. Lo que se nos ocurra.

Después de haber eliminado y modificado todo lo que quisimos, para generar una nueva Base de Datos, debemos quitarle el comentario a la línea CREATE DATABASE, poner el nombre que tendrá la nueva Base de Datos, y el usuario y la contraseña que la creará.

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

Para crear la Base de Datos llamada DEISY2.FDB, ingresamos a ISQL y con el comando INPUT ejecutamos el script. Todo lo que esté escrito dentro del script será ejecutado.

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

Si todo está bien, la nueva Base de Datos será creada. Si hay algún error, habrá que encontrar cual es el error y corregirlo. Luego, se vuelve a intentar crear la Base de Datos.

Copiando solamente el contenido de algunas tablas

Hasta ahora hemos visto como copiar a una Base de Datos completa, con todos sus metadatos y todos sus datos. Pero ¿y si queremos copiar solamente el contenido de algunas tablas?

Para eso entramos a ISQL, nos conectamos a una Base de Datos, y usando el comando OUTPUT enviamos a un archivo de texto todo lo que hacemos, tal y como vemos a continuación:

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

La primera línea le indica al ISQL que todo lo que se escriba a continuación se guarde en un archivo de texto. No se verá en la pantalla, sino que se guardará en el archivo cuyo nombre se escriba después de la palabra OUTPUT.

Ese nombre puede ser cualquiera y puede tener cualquier extensión. Para los datos se acostumbra a ponerle la extensión .DAT pero eso no es obligatorio.

El OUTPUT que se encuentra en la tercera línea se usa para que la salida vuelva a verse en la pantalla, o sea que dejará de enviarse al archivo de texto.

Entre el primer OUTPUT y el segundo OUTPUT puede haber muchas líneas, no solamente una como en este ejemplo.

En el contenido del archivo de texto BANCOS.DAT se verá algo como:

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

Donde, como puedes ver, se muestra el nombre de cada columna y el contenido de cada fila.

Opción 4. Copiar algunos metadatos y el contenido de algunas tablas

Es igual a la Opción 3.

Conclusión:

Como has podido ver en este artículo, con Firebird tenemos la posibilidad de realizar backups completos o parciales de nuestra Base de Datos.

Enviar el backup a un archivo de script tiene la gran ventaja de que todo es legible y así podríamos revisar ese script y descubrir errores o mejoras que se podrían realizar.

Artículos relacionados:

Entendiendo a los scripts

Usando scripts para documentar la Base de Datos

Ejecutando un script desde Visual FoxPro

El índice del blog Firebird21

El foro del blog Firebird21

Relacionando dos tablas: la forma vieja y la forma nueva

En Firebird, cuando queremos relacionar dos tablas (o conjuntos de resultados) entre sí tenemos dos posibilidades:

1. Usando la forma vieja
2. Usando la forma nueva

Caso 1. Usando la forma vieja

Esta sintaxis fue establecida en el año 1989. Las tablas se listan separadas por comas después de la cláusula FROM y la condición que las relaciona se pone en la cláusula WHERE. No hay una sintaxis especial que distinga cuales de las condiciones del WHERE son para filtrar filas y cuales son para relacionar tablas, se supone que mirando la sentencia el desarrollador sabrá cual es cual.

¿Problemas?

• La cláusula WHERE puede volverse muy larga y muy complicada de leer porque se la usa para relacionar tablas y también para filtrar filas
• Solamente se pueden relacionar tablas que tengan valores idénticos, si una de las tablas tiene NULL en una columna no se podrá hacer la relación. En otras palabras: solamente se puede hacer un INNER JOIN, no se pueden hacer OUTER JOIN.

Sintaxis:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla1,
   MiTabla2
WHERE
   MiCondición

Caso 2. Usando la forma nueva

Esta sintaxis fue establecida en el año 1992. La tabla principal se coloca después de la cláusula FROM y la tabla secundaria se coloca después de la cláusula JOIN, la condición que las relaciona se coloca después de ON. Por lo tanto se puede distinguir fácilmente cual es la condición usada para relacionar a la tablas y cual es la condición usada para filtrar filas, no hay confusión posible.

¿Ventajas?

• Es muy fácil saber cual es la condición usada para relacionar a las dos tablas
• Es muy fácil saber cual es la condición usada para filtrar filas
• La cláusula WHERE es más corta y por lo tanto más fácil de leer que cuando se usa la forma vieja
• Se puede usar tanto INNER JOIN como OUTER JOIN

Sintaxis:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición

Cuidado:

Tú puedes elegir cualquiera de las dos formas para relacionar tablas pero NUNCA debes mezclarlas. En un SELECT o usas la forma vieja o usas la forma nueva, no las mezcles, porque si las mezclas eso solamente te ocasionará problemas y ningún beneficio.

Recomendación:

La forma nueva es mejor, por eso se la inventó, por eso existe. Si ya tienes SELECTs escritos con la forma vieja y funcionan bien entonces déjalos como están, no los toques, pero para escribir todos tus nuevos SELECTs usa la forma nueva porque es la que verás cada vez más en todos los libros y documentos sobre SQL. Y es además la que siempre se usa en este blog.

Artículos relacionados:

Entendiendo a los JOIN

JOIN implícito y JOIN explícito

El índice del blog Firebird21

 

Crear una tabla por programa

A veces necesitamos crear una tabla dinámicamente, o sea por programa. Eso significa que dependiendo de los datos que introduzca el usuario o procese nuestro programa serán las columnas que tendrá la tabla.

En este artículo veremos una técnica para conseguir nuestro objetivo.

La tabla que crearemos tendrá dos columnas: una para guardar todas las fechas del rango especificado y otra para guardar si esa fecha corresponde a un feriado o no.

Por supuesto que en tus propias tablas creadas con esta técnica podrás tener muchas más columnas, y también podrás agregarle una Primary Key, Foreign Keys, índices, etc. Este ejemplo es simple y sencillo para mostrar la técnica, la cual podrá servirte de base para crear tablas muy complejas.

Todo lo que debemos hacer es crear un stored procedure que utilice el comando EXECUTE STATEMENT, como se ve a continuación:

CREATE PROCEDURE CREAR_TABLA_FECHAS(
   tcNombreTabla  VARCHAR(28),
   tdFechaInicial DATE,
   tdFechaFinal   DATE)
AS
   DECLARE VARIABLE lcComando VARCHAR(1024);
   DECLARE VARIABLE ldFecha   DATE;
BEGIN

   lcComando = 'CREATE TABLE ' || tcNombreTabla ||
             ' (TAB_FECHAX DATE,
                TAB_FERIAD CHAR(1))';

   EXECUTE STATEMENT :lcComando WITH AUTONOMOUS TRANSACTION;

   ldFecha = tdFechaInicial;

   WHILE (ldFecha <= tdFechaFinal) DO BEGIN
      lcComando = 'INSERT INTO ' || tcNombreTabla ||
                  ' (TAB_FECHAX, TAB_FERIAD)
           VALUES (''' || ldFecha || ''', ' || '''N'')';
      EXECUTE STATEMENT lcComando;
      ldFecha = ldFecha + 1;
   END

END;

Entonces, para ejecutar a este stored procedure escribiríamos algo como:

EXECUTE PROCEDURE CREAR_TABLA_FECHAS('Fechas2014', '01/JAN/2014', '31/DEC/2014')

Y este será el resultado que obtendremos:

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

Una tabla, llamada Fechas2014 (porque ese es el nombre que especificamos) que contiene dos columnas: TAB_FECHAX para guardar las fechas y TAB_FERIAD para guardar si la fecha corresponde a un feriado o no.

Y si revisamos el contenido de esa tabla, veremos algo como esto:

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

hay más filas, porque pedimos que tenga todas las fechas desde el 1 de enero de 2014 hasta el 31 de diciembre de 2014 y eso justamente es lo que tiene la tabla. Desde luego que aquí se muestran solamente algunas de esas filas.

Lo interesante es que la tabla fue creada por el stored procedure y también todas sus filas fueron insertadas por el stored procedure, el usuario no insertó esas filas.

Conclusión:

Usando el comando EXECUTE STATEMENT podemos crear, modificar, o borrar tablas dinámicamente, también insertarles filas, modificarlas, borrarlas, etc. Esto puede ser muy útil en muchas ocasiones pero debemos evaluar si vale la pena o no. ¿Por qué? primero, porque EXECUTE STATEMENT es lento, ya que recién en tiempo de ejecución el Firebird puede analizar la instrucción y segundo porque el Firebird puede detectar cualquier error que hayamos escrito solamente en tiempo de ejecución.

En este ejemplo hemos creado una tabla conteniendo dos columnas. Por supuesto que las tablas pueden tener muchas más columnas y también Primary Key, Foreign Keys, índices, etc. Todo puede hacerse con EXECUTE STATEMENT, por lo tanto debemos aprender a usarlo correctamente.

Artículos relacionados:

EXECUTE STATEMENT

El índice del blog Firebird21

Nombres de tablas con guión bajo

Si quieres que el nombre de una tabla empiece con un guión bajo tendrás problemas porque el Firebird no admite al guión bajo como primer carácter del nombre de una tabla.

Sin embargo … hay una solución.

Y la solución es rodear al nombre de la tabla con comillas, así:

CREATE TABLE
   "_CON_GUION"
   (GUI_IDENTI INTEGER,
    GUI_NOMBRE VARCHAR(40));

Desde luego que para acceder a esa tabla siempre tendrás que usar las comillas, por ejemplo:

INSERT INTO "_CON_GUION"
           (GUI_IDENTI, GUI_NOMBRE)
    VALUES (1         , 'SILVIA BEATRIZ');

SELECT
   *
FROM
   "_CON_GUION"

Conclusión:

A veces podría ser útil que los nombres de algunas tablas empiecen con un guión bajo, el Firebird no nos permite eso, salvo que el nombre de la tabla esté rodeado por comillas.

Artículo relacionado:

El índice del blog Firebird21

 

Optimizando las subconsultas

Las subconsultas son una herramienta muy útil que tenemos a nuestra disposición pero que en ocasiones pueden ser muy lentas. Consideremos este caso:

SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   (SELECT SUM (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT MAX (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT MIN (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT AVG (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI)
FROM
   MOVIMCAB C

Aquí, tenemos una tabla cabecera de movimientos (MOVIMCAB) y una tabla de detalles de movimientos (MOVIMDET).

Y lo que nos interesa es hallar, para cada venta realizada, el total de esa venta (usamos la función SUM()), la línea con el mayor total (usamos la función MAX()), la línea con el menor total (usamos la función MIN()), y el promedio de las líneas (usamos la función AVG()).

Todo bien hasta aquí y el SELECT anterior cumple con su objetivo pero … hay un pero. Y ese pero es que escribimos 4 subconsultas. Y cada subconsulta es independiente a las otras y por lo tanto se emplea mucho tiempo en obtener los resultados deseados. Se los obtiene, sí, pero se demora mucho en obtenerlos (por supuesto que en el caso de tablas muy grandes, si las tablas son pequeñas no se notará la demora).

Entonces, ¿podemos optimizar este SELECT para que se ejecute más rápido?

Sí, podemos.

El truco es realizar una sola subconsulta, y en ella obtener todos los datos que nos interesan, como vemos a continuación:

 

SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   CAST(LEFT     (TodoJunto, 10)            AS BIGINT),
   CAST(SUBSTRING(TodoJunto FROM 11 FOR 10) AS BIGINT),
   CAST(SUBSTRING(TodoJunto FROM 21 FOR 10) AS BIGINT),
   CAST(RIGHT    (TodoJunto, 10)            AS BIGINT)
FROM
   (SELECT
       MOVIMCAB.MVC_IDENTI,
       (SELECT
           LPAD(SUM(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(MAX(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(MIN(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(AVG(MOV_CANTID * MOV_PRECIO), 10)
        FROM
           MOVIMDET
        WHERE
           MOV_IDECAB = MVC_IDENTI) AS TodoJunto
    FROM
       MOVIMCAB) AS DERIVADA
JOIN
   MOVIMCAB C
      ON C.MVC_IDENTI = DERIVADA.MVC_IDENTI

 Aquí, para cada fila de la tabla MOVIMDET se realiza una sola subconsulta y se concatenan las columnas para guardarlas con el alias TodoJunto.

Como puedes ver, esta consulta es más complicada de escribir que la original, pero la ventaja es que se obtienen los resultados mucho más rápidamente porque cada fila de la tabla MOVIMDET es recorrida una sola vez, no 4 veces como en la consulta original.

Conclusión:

Se pueden optimizar las subconsultas concatenando las columnas que nos interesan y luego «desconcatenarlas».

Esta técnica para optimizar las subconsultas es muy útil cuando las tablas son muy grandes (es decir: tienen millones de filas) porque se consigue obtener los resultados muy rápido. El motivo es que cada fila de la tabla de detalles se recorre una sola vez.

Si no usamos esta técnica, cada fila de la tabla de detalles sería recorrida 4 veces. Y recorrer las filas 4 veces evidentemente es mucho más lento que recorrerlas 1 sola vez.

Sin embargo, si las tablas son pequeñas y siempre lo serán, probablemente no valga la pena el esfuerzo de optimizar las subconsultas.

Así que queda a tu criterio si decides optimizarlas o no, pero al menos ya conoces una técnica para optimizarlas si lo deseas.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

El índice del blog Firebird21

 

La problemática de las tablas maestras

Cuando diseñamos una Base de Datos normalmente creamos cuatro tipos de tablas:

• Configuración
• Maestras
• Movimientos
• Auxiliares

Las tablas de configuración servirán para guardar datos que ayudarán a personalizar la aplicación relacionada.

Las tablas maestras servirán para guardar datos que luego usarán las tablas de movimientos.

Las tablas de movimientos servirán para guardar lo que ocurre en la operación normal de la empresa u organización, sus actividades diarias. A su vez se clasifican en cabecera y en detalle.

Las tablas auxiliares servirán para guardar datos temporales.

En las tablas de movimientos siempre tenemos una columna donde guardamos la fecha del movimiento, así que no es problema conocer las fechas cuando las necesitamos.

Pero eso muchas veces no ocurre con las tablas maestras, en ellas no se suelen guardar las fechas. Y puede ser muy necesario conocerlas.

Ejemplo:

Tenemos una tabla maestra de CLIENTES donde entre otros datos guardamos el Identificador del Cliente, su Nombre, su Dirección, su Teléfono. Y una tabla cabecera de VENTAS donde guardamos el Identificador de la Venta, el Identificador del Cliente, el Número de la Factura, la Fecha de la venta y otros datos.

Le hacemos una venta al cliente Juan Pérez, e imprimimos la Factura Número «001-002-3456789» que corresponde a esa venta. En ella consta que la Dirección de Juan Pérez es «Colón 12345» y que su teléfono es el «0123-456789». Todo bien hasta ahí, ningún problema, se va Juan Pérez con su Factura.

Un tiempo después regresa Juan Pérez, le hacemos otra venta, pero nos dice que se mudó y que por lo tanto se cambió su Dirección, su Teléfono y hasta su Localidad. Así que ahora le imprimimos la Factura Número «001-002-9876543» que corresponde a esta última venta, la nueva Dirección es «Spartacus 44444» y el nuevo teléfono es el «0333-555555». Todo bien hasta ahí, ningún problema, se va Juan Pérez con su Factura.

Pero …. luego surge el problema.

Hay una auditoría, el ente recaudador de impuestos hace un control cruzado entre las Facturas de Juan Pérez y nuestras Facturas y al consultar la Factura «001-002-3456789» le dice que la Dirección es «Spartacus 44444» y el Teléfono es «0333-555555» porque esos son los datos que tenemos guardados de Juan Pérez en nuestra Base de Datos actualmente.

Y está mal.

Está mal porque la Dirección que se imprimió en esa Factura era «Colón 12345». Y aunque pasen los años y Juan Pérez se mude de casa muchas veces siempre que consultemos los datos de la Factura Número «001-002-3456789» deberíamos ver que la Dirección es «Colón 12345». No la última Dirección que registramos en nuestra tabla de CLIENTES sino la Dirección original.

¿Cuál es la solución a este problema?

Tenemos tres alternativas, dos malas y una buena.

Una alternativa mala es restaurar un backup de la fecha de la primera venta a Juan Pérez para saber que Dirección teníamos guardada ese día.

Otra alternativa mala es guardar en la tabla cabecera de VENTAS la Dirección, el Teléfono, la Localidad, el Email y otros datos de Juan Pérez. Es cierto que eso nos aseguraría de imprimir siempre los datos correctos pero nuestra tabla no estaría normalizada y por lo tanto gastaríamos mucho más espacio en el disco duro que el necesario.

La alternativa buena es guardar en otra tabla, no en la tabla de CLIENTES sino en otra tabla, los cambios que se realicen a los datos de nuestros clientes. La estructura de esta tabla (la podríamos llamar CAMBIOS_CLIENTES) debería ser idéntica a la tabla CLIENTES pero tendría una columna más, esa columna adicional sería el Identificador de la tabla (el que usamos para su Primary Key).

La tabla CLIENTES (y por lo tanto la tabla CAMBIOS_CLIENTES) debe tener una columna donde se guarde el nombre del usuario que insertó la fila y otra columna donde se guarden la fecha y la hora de esa inserción.

De esta manera siempre podríamos saber cuales eran los datos de Juan Pérez en cualquier día de cualquier mes de cualquier año.

¿Cuál era la Dirección de Juan Pérez el día 12 de agosto de 2013?

1. Buscamos en la tabla CAMBIOS_CLIENTES la fila más nueva que corresponda a Juan Pérez y que su fecha sea 12 de agosto de 2013 o anterior.
2. Si la encontramos, esa es la Dirección de Juan Pérez el día 12 de agosto de 2013
3. Si no la encontramos, buscamos en la tabla CLIENTES cual es la Dirección que originalmente tenía Juan Pérez
4. Si esa fila fue insertada el día 12 de agosto de 2013 o antes, esa es la respuesta buscada
5. Si la fila fue insertada posteriormente al 12 de agosto de 2013 entonces Juan Pérez aún no era nuestro cliente ese día y por lo tanto no podemos saber cual era su Dirección.

El problema con las tablas maestras

Lo que vimos en el ejemplo anterior, con las ventas a Juan Pérez y sus cambios de direcciones se aplica a todas las tablas maestras. Cualquier tabla maestra que tengamos adolecerá del mismo problema si no tomamos las debidas precauciones: que estaremos viendo los últimos datos, no los datos que teníamos anteriormente. Y en muchos casos los que deberíamos ver son los datos que teníamos anteriormente.

Una Base de Datos que no puede ser retrotraída a cualquier instante anterior no está bien diseñada.

Es así de simple:

• Si yo necesito recurrir a un backup para que una consulta me muestre los datos correctos entonces mi Base de Datos está mal diseñada. Nada que discutir.
• Si las tablas no están normalizadas entonces mi Base de Datos está mal diseñada. Nada que discutir.

Un buen diseño implica más trabajo

Todo tiene sus ventajas y sus desventajas, nada es perfecto. Podemos tener una Base de Datos perfectamente diseñada que nos permita responder a cualquier pregunta en cualquier momento, pero eso demandará más de nuestro tiempo. Y es que por cada tabla maestra deberemos tener una tabla de CAMBIOS. Y por cada tabla maestra deberemos tener un trigger AFTER UPDATE que le inserte una fila a la tabla de CAMBIOS. Y a su vez nuestras consultas (SELECTs) serán más complicadas cuando involucren a las preguntas ¿quién? ¿cuándo? o ¿desde cuándo? y ¿hasta cuándo?

La diferencia entre tener una Base de Datos muy bien diseñada y una pobremente diseñada la verás el día que quieras venderle tu aplicación a gente que entiende de Informática. Mientras tus clientes sean ignorantes en temas informáticos podrás venderles casi cualquier cosa pero el tema es muy distinto cuando hablas con gente entendida. Por ejemplo si una empresa tiene un Departamento de Informática es seguro que tendrás que entrevistarte con los encargados y allí enseguida descubrirán tu pobre diseño.

Por lo tanto, si quieres vender tus aplicaciones a empresas de cualquier tamaño y no pasar vergüenza cuando hables con el personal informático, diseña tu Base de Datos de la mejor manera posible.

Artículos relacionados:

El índice del blog Firebird21

Una forma rápida de escribir un SELECT

A veces, queremos escribir un SELECT el cual tendrá todas o casi todas las columnas de una tabla. Si queremos todas las columnas podríamos usar el asterisco * pero esa es una práctica no recomendada.

Si usamos el programa EMS SQL Manager tenemos una alternativa interesante y que nos facilitará el trabajo: hacemos clic con el botón derecho sobre el nombre de la tabla, luego elegimos la opción «Script to New SQL Editor» y luego la opción «Select», como se ve en esta imagen:

(haciendo clic en la imagen la verás más grande)

Y esto es lo que obtenemos:

(haciendo clic en la imagen la verás más grande)

O sea, un SELECT con todas las columnas de la tabla. Esto puede ahorrarnos mucho tiempo, sobre todo con tablas que tienen decenas de columnas.  Este SELECT podemos ahora «copiarlo y pegarlo» donde lo necesitemos.