¿Cómo insertar rápidamente filas en una tabla desde otra tabla?

6 comentarios

A veces queremos que en una tabla T1 se inserten datos que se encuentran en una tabla T2. Hay varias formas de conseguir eso, la más rápida de todas es la siguiente:

INSERT INTO
   T1
SELECT
   *
FROM
   T2

Por supuesto que debes reemplazar T1 y T2 por los verdaderos nombres de tus respectivas tablas. Esto funciona siempre y cuando ambas tablas tengan la misma estructura, si ese no es el caso entonces hay que especificar las columnas involucradas.

INSERT INTO
   T1
   (T1Columna1,
    T1Columna2,
    T1Columna3)
SELECT
   T2Columna1,
   T2Columna2,
   T2Columna3
FROM
   T2

Los nombres de las columnas pueden ser distintos ya que el Firebird no se fija en el nombre de las columnas para realizar la inserción. Los únicos requisitos son:

  • La cantidad de columnas después del INSERT debe ser igual a la cantidad de columnas después del SELECT
  • Los tipos de datos de cada columna después del INSERT deben ser iguales o compatibles con los tipos de datos de cada columna después del SELECT

NOTA:

Si la estructura de las tablas puede cambiar es preferible poner los comandos dentro de una instrucción EXECUTE STATEMENT para que no muestre el error “object in use”. O sea:

EXECUTE STATEMENT “INSERT INTO T1…”

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Listando todos los documentos de cada día

1 comentario

A veces puede ser muy interesante tener en una columna todos los números de documento de una fecha fundamentalmente porque se ahorra mucho espacio en la pantalla o en el informe.

Si escribimos algo como esto:

SELECT
   VTC_FECHAX,
   LIST(VTC_NRODOC, ';')
FROM
   VENTASCAB
GROUP BY
   VTC_FECHAX

Obtendremos algo como esto:

LISTA1

Captura 1. (si haces clic en la imagen la verás más grande)

Fíjate que el segundo parámetro de la función LIST() es el punto y coma. Ese será usado como separador. Desde luego que puedes usar cualquier otro separador que desees siempre y cuando no se encuentre dentro de los caracteres que puede mostrar el primer argumento (o sea que con este ejemplo no deberías usar como separador ni el guión ni un dígito 0..9; cualquier otro carácter sí es admisible).

LISTA2

Captura 2. (si haces clic en la imagen la verás más grande)

En este segundo ejemplo se usó como separador tres espacios en blanco para que los números de documentos no estén tan amontonados y sea más fácil distinguirlos.

Artículo relacionado:

El índice del blog Firebird21

Entendiendo a las UNION

9 comentarios

El lenguaje SQL como probablemente sabes se basa en la Matemática. Más específicamente en el Cálculo, el Álgebra y la Teoría de Conjuntos.

¿Recuerdas la Teoría de Conjuntos?

Es esa de los diagramas de Venn, seguro que los has visto en el Colegio o en la Universidad.

UNION01

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

Si nuestras tablas están bien diseñadas entonces cada fila es única, en ninguna tabla tendremos dos filas que sean exactamente iguales. Entonces, a cada fila podemos imaginarla como un elemento de un conjunto. Además, cuando escribimos un SELECT el resultado que obtenemos también es un conjunto.

Un conjunto puede estar vacío (en ese caso el SELECT no retorna resultados) o puede contener uno o más elementos (cuando el SELECT sí retorna resultados).

Por lo tanto ya tenemos a los conjuntos (todas las filas devueltas por cada SELECT) y a los elementos de los conjuntos (cada una de las filas devueltas por los SELECT). En la literatura SQL leerás que el resultado de ejecutar un SELECT es un conjunto resultado. Ese es el motivo.

¿Qué es una UNION en SQL?

Es la unión de dos conjuntos. Su sintaxis es la siguiente:

SELECT MiColumna1, MiColumna2, ... FROM MiTabla1

UNION

SELECT MiColumna1, MiColumna2, ... FROM MiTabla2

La unión de dos conjuntos es siempre otro conjunto. Que puede estar vacío (en el caso que ambos conjuntos originales hubieran estado vacíos) o contener uno o más elementos.

Ejemplo 1:

Tenemos una tabla llamada COMPRASCAB (cabecera de las compras) y otra tabla llamada VENTASCAB (cabecera de las ventas) y deseamos un conjunto resultado que sea la unión de ambas tablas. ¿Para qué? para imprimir un informe que nos muestre todas las compras y todas las ventas. En este ejemplo, para que sea manejable, mostraremos solamente las compras y las ventas del día 9 de abril de 2012 aunque lo normal es que se muestren de varias fechas.

SELECT
   CMC_FECHAX,
   CMC_TIPDOC,
   CMC_NRODOC
FROM
   COMPRASCAB
WHERE
   CMC_FECHAX = '09/APR/2012'

UNION02

Captura 1. Las compras del día 9 de abril de 2012

SELECT
   VTC_FECHAX,
   VTC_TIPDOC,
   VTC_NRODOC
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '09/APR/2012'

UNION03

Captura 2. Las ventas del día 9 de abril de 2012

 Y ahora queremos obtener un conjunto resultado que sea la unión entre COMPRASCAB y VENTASCAB y por lo tanto estará compuesto por todas las filas de COMPRASCAB y por todas las filas de VENTASCAB.

SELECT
   'ECM' AS TIPMOV,
   CMC_FECHAX,
   CMC_TIPDOC,
   CMC_NRODOC
FROM
   COMPRASCAB
WHERE
   CMC_FECHAX = '09/APR/2012'

UNION

SELECT
   'SVT' AS TIPMOV,
   VTC_FECHAX,
   VTC_TIPDOC,
   VTC_NRODOC
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '09/APR/2012'

UNION04

Captura 3. Las compras y las ventas del día 9 de abril de 2012

 Algunas observaciones:

  1. En el conjunto resultado tenemos todas las compras y todas las ventas ocurridas el día 9 de abril de 2012
  2. Para poder diferenciar a las compras de las ventas hemos agregado una nueva columna llamada TIPMOV la cual puede tener el valor “ECM” cuando es una entrada por compras o un valor “SVT” cuando es una salida por ventas. Agregar una columna para diferenciar a las filas de las tablas puede no ser necesario en otros casos.

Ejemplo 2:

En el Ejemplo 1. hemos visto la unión de dos tablas. Pero si lo necesitamos podemos unir tres o más tablas también, para simular el diagrama de Venn con el que empieza este artículo.

En una institución educativa tenemos las tablas ALUMNOS, PROFESORES, y EMPLEADOS y queremos obtener un conjunto resultado que sea la unión de esas tres tablas. Por lo tanto podríamos escribir:

SELECT
   "ALU" AS PERSONA,
   ALU_CEDULA,
   ALU_APELLD,
   ALU_NOMBRE
FROM
   ALUMNOS

UNION

SELECT
   "PRO" AS PERSONA,
   PRO_CEDULA,
   PRO_APELLD,
   PRO_NOMBRE
FROM
   PROFESORES

UNION

SELECT
   "EMP" AS PERSONA,
   EMP_CEDULA,
   EMP_APELLD,
   EMP_NOMBRE
FROM
   EMPLEADOS

En este caso hemos unido tres tablas (ALUMNOS, PROFESORES, EMPLEADOS) para obtener un conjunto resultado compuesto por las filas de esas tres tablas. Para poder diferenciar si una fila corresponde a un alumno, a un profesor, o a un empleado, hemos agregado una columna llamada PERSONA.

Requisitos para poder unir dos conjuntos:

  1. La cantidad de columnas devueltas por cada conjunto debe ser exactamente la misma (o sea que si el primer SELECT devuelve 4 columnas, el segundo SELECT también debe devolver 4 columnas).
  2. El tipo de datos debe corresponder columna a columna. Eso significa que si la primera columna del primer conjunto es INTEGER la primera columna del segundo conjunto también debe ser INTEGER. Si la segunda columna del primer conjunto es DATE, la segunda columna del segundo conjunto también debe ser DATE. Y así sucesivamente.
  3. Los nombres de las columnas no importan, pueden ser iguales o distintos, eso no importa. Lo que sí importa es que los tipos de datos sean idénticos.

Simulando los diagramas de VENN:

La forma usual de simular los diagramas de Venn, como el que se muestra al inicio de este artículo es a través de vistas. De esa manera podríamos unir:

– Vista1 con Vista2

– Vista1 con Vista3

– Vista2 con Vista3

– Vista1 con Vista2 con Vista3

Y así tendríamos todas las posibilidades de unión de tres conjuntos.

Conclusión:

Usar UNION es muy conveniente cuando los datos que necesitamos están en dos o más conjuntos (tablas o vistas o stored procedures seleccionables)  y las columnas tienen el mismo tipo de datos. Recuerda que no todas las columnas de ambos conjuntos deben tener el mismo tipo de datos, solamente las que se usarán en la UNION deben ser iguales.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo a los JOIN

3 comentarios

En SQL hay dos formas de relacionar tablas entre sí:

  1. Con la cláusula FROM
  2. Con la cláusula JOIN

Cuando se las relaciona mediante la cláusula FROM se escribe algo como:

SELECT
   Columna1,
   Columna2,
   ColumnaN
FROM
   Tabla1,
   Tabla2
WHERE
   Condición_entre_Tabla1_y_Tabla2

En cambio, cuando se las relaciona mediante la cláusula JOIN se escribe algo como:

SELECT
   Columna1,
   Columna2,
   ColumnaN
FROM
   Tabla1
JOIN
   Tabla2
   ON Condición_entre_Tabla1_y_Tabla2

La primera forma (la que utiliza FROM) es la forma antigua de relacionar dos tablas entre sí. Está basada en el cálculo.

La segunda forma (la que utiliza JOIN) es la forma moderna de relacionar dos tablas entre sí. Está basada en el álgebra.

NOTA: Quizás no sabías que el lenguaje SQL está basado en tres ramas de la Matemática: el cálculo integral, el álgebra y la teoría de conjuntos.

Un JOIN es el producto cartesiano entre las dos tablas involucradas. Eso significa que el resultado de un JOIN combina cada fila de la primera tabla con cada fila de la segunda tabla.

Por ejemplo, si la primera tabla tiene 3 filas y la segunda tabla tiene 7 filas al hacer un JOIN el resultado tendrá 21 filas (que es lo que se obtiene al multiplicar 3 por 7)

En tablas compuestas por miles o por millones de filas, la cantidad de filas del resultado puede ser asombrosamente grande; por ese motivo es que siempre debemos acotar lo más posible a los JOIN. En otras palabras, ponerles la mayor cantidad de condiciones para que obtengamos un resultado manejable.

Ejemplo:

Nuestra tabla de SUCURSALES tiene estas filas:

JOIN1

Captura 1. Tabla de SUCURSALES (si haces clic en la captura la verás más grande)

Y nuestra tabla de BANCOS tiene estas filas:

JOIN2

Captura 2. Tabla de BANCOS (si haces clic en la captura la verás más grande)

Listado 1. Viendo el producto cartesiano de la tabla SUCURSALES por la tabla BANCOS

SELECT
   B.*,
   S.*
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_IDENTI > 0

JOIN3

Captura 3. Resultado del JOIN entre BANCOS y SUCURSALES (si haces clic en la captura la verás más grande)

 Como puedes ver, cada Banco se ha combinado con cada Sucursal. Eso ocurrió porque nuestra condición (B.BAN_IDENTI > 0) es siempre verdadera e involucra a solamente una de las tablas. Podemos también ver la cantidad de lecturas ocurridas en cada tabla haciendo clic en la pestaña “Performance Analysis” del EMS SQL Manager y esto será lo que obtendremos:

JOIN4

Captura 4. Performance Analysis del JOIN entre BANCOS y SUCURSALES (si haces clic en la captura la verás más grande)

 Acotemos ahora el JOIN para volverlo más manejable.

Listado 2. Viendo el producto cartesiano de la tabla SUCURSALES por la tabla BANCOS relacionando ambas tablas.

SELECT
   B.*,
   S.*
FROM
   BANCOS     B
JOIN
   SUCURSALES S
   ON B.BAN_CODSUC = S.SUC_CODIGO

Como ahora tenemos una condición que relaciona a ambas tablas entre sí (mediante las columnas B.BAN_CODSUC y S.SUC_CODIGO) aunque el resultado sigue siendo el producto cartesiano entre ellas (es decir: todas las filas de SUCURSALES combinadas con todas las filas de BANCOS) la condición limita o acota las filas devueltas por el SELECT, quedando ahora así:

JOIN5

Captura 5. Relacionando las tablas BANCOS y SUCURSALES entre sí (si haces clic en la captura la verás más grande)

Y al hacer clic sobre “Performance Analysis” esto es lo que obtenemos:

JOIN6

Captura 6. Performance Analysis del JOIN entre BANCOS y SUCURSALES (si haces clic en la captura la verás más grande)

Si ahora le ponemos una condición usando la cláusula WHERE el conjunto resultado estará aún más limitado o acotado.

Listado 3. Un JOIN con una cláusula WHERE

SELECT
   B.*,
   S.*
FROM
   BANCOS B
JOIN
   SUCURSALES S
   ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_IDENTI = 6

Al usar WHERE para poner una condición que limita la cantidad de filas devueltas obtendremos aún menos filas, como podemos ver aquí:

JOIN7

Captura 7. Usando WHERE para limitar las filas devueltas (si haces clic en la captura la verás más grande)

Y al hacer clic sobre la pestaña “Performance Analysis” esto es lo que obtenemos:

JOIN8 

Captura 8. Performance Analysis al usar WHERE para limitar las filas devueltas (si haces clic en la captura la verás más grande)

Conclusión:

Un JOIN es igual al producto cartesiano entre las filas de dos tablas. Eso significa que se combinan todas las filas de la primera tabla con todas las filas de la segunda tabla. Debido a ello en tablas que tienen miles o millones de filas el resultado de un JOIN puede estar compuesto por una cantidad asombrosamente grande de filas. Para limitar esa cantidad debemos asegurarnos que una o más columnas de la primera tabla se relacionen con una o más columnas de la segunda tabla. Además, siempre que podamos debemos usar la cláusula WHERE para limitar aún más la cantidad de filas devueltas.

En nuestro ejemplo, con el SELECT del Listado 1. el Firebird hizo 24 lecturas (21 en BANCOS y 3 en SUCURSALES). Con el SELECT del Listado 2. el Firebird hizo 14 lecturas (7 en BANCOS y 7 en SUCURSALES). Con el SELECT del Listado 3. el Firebird hizo 4 lecturas (1 en BANCOS y 3 en SUCURSALES). Al ir poniendo condiciones conseguimos bajar la cantidad inicial de lecturas (24) a solamente 4.

Menos lecturas implica mucha mayor velocidad de respuesta.

Artículo relacionado:

El índice del blog Firebird21

Los índices de las restricciones

3 comentarios

Cada vez que estableces una restricción (Primary Key, Foreign Key, Unique Key) el Firebird automáticamente crea un índice asociado para esa restricción.

Por lo tanto, no deberías crear un índice sobre esas mismas columnas ya que si haces eso tendrás dos índices idénticos y eso te acarreará dos problemas.

  1. El Firebird podría confundirse sobre cual índice usar y por lo tanto tu consulta será más lenta de lo que debería
  2. Cada vez que insertes una fila (o actualices una columna involucrada en el índice que creaste) el Firebird deberá actualizar dos índices y eso lo hará más lento.

Sin embargo…hay una excepción a esta regla.

Por defecto, el Firebird crea los índices de forma ascendente (es decir: 1, 2, 3, 4, 5, 6, 7, etc.) y a veces para tus consultas podrías necesitar un índice descendente. Y en esos casos sí deberías crear el índice.

Por ejemplo, si usas la función MAX() en una consulta, tener un índice descendente sobre esa columna te aumentará la velocidad de respuesta de forma impresionante.

Otro ejemplo, si deseas que la consulta te aparezca ordenada en forma descendente un índice descendente es lo adecuado.

Entonces, no crees un índice sobre una columna que tiene una restricción (Primary Key, Foreign Key, Unique Key) porque el Firebird ya crea ese índice por su propia cuenta. Las únicas excepciones son si necesitas ordenar una consulta en forma descendente o si empleas la función MAX().

Artículo relacionado:

El índice del blog Firebird21

Entendiendo las tablas autoreferenciadas

1 comentario

Como ya hemos visto en estos dos artículos:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

https://firebird21.wordpress.com/2013/07/19/autorreferenciar-una-tabla-algunos-ejemplos/

podemos escribir una consulta en la cual una tabla se referencia a sí misma. Explayémonos un poco más sobre este tema.

Evidentemente para que referenciemos una tabla con sí misma en ella deben encontrarse los datos que necesitamos. Aquí tenemos dos posibilidades:

  1. Que la referencia se haga a dos columnas de la tabla
  2. Que la referencia se haga a una sola columna de la tabla

Quizás habrás notado que en el primero de los dos artículos superiores se usaron dos columnas y en el segundo artículo solamente se usó una columna.

En general, cuando se trata de una tabla maestra (es decir: una tabla que no depende de otras tablas) usamos la primera opción. Y cuando se trata de una tabla de movimientos (es decir: una tabla que depende de otras tablas) usamos la segunda opción. Pero esa no es una regla inmutable, es lo más común pero no está grabado en piedra.

Ejemplo 1:

Necesitamos registrar los datos de algunos animales (por ejemplo: perros) y entre otras cosas queremos saber cual es el padre de cada perro, cual es el abuelo, etc. (lo que en castellano se llama pedigrí)

Para ello podríamos tener una tabla para los hijos, otra para los padres, otra para los abuelos, etc. Pero sería tedioso trabajar así porque tendríamos muchos datos duplicados (un mismo perro podría encontrarse en dos o en tres tablas). Una manera mucho más racional sería tener una sola tabla e identificar en ella al padre de cada perro.

AUTO6

(si haces clic en la imagen la verás más grande)

Y los datos de esa tabla podrían ser algo así:

AUTO7

(si haces clic en la imagen la verás más grande)

Entonces, para ver los nombres de cada perro y los nombres de sus respectivos padres podríamos escribir:

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI

Como ves, la tabla ANIMALES tiene un JOIN a la tabla ANIMALES, o sea que se referencia a sí misma. Al ejecutar ese SELECT obtendríamos este resultado:

AUTO8

(si haces clic en la imagen la verás más grande)

Donde podemos ver el nombre del padre de cada perro. En algunos casos, también podríamos obtener el nombre del abuelo (INDIO es hijo de AFRO quien a su vez es hijo de SATAN).

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre",
   A.ANI_IDENTI AS "Id. abuelo",
   A.ANI_NOMBRE AS "Nombre del abuelo"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI
LEFT JOIN
   ANIMALES A
      ON P.ANI_IDEPAD = A.ANI_IDENTI

Si ejecutamos este SELECT esto será lo que obtendremos:

AUTO9

(si haces clic en la imagen la verás más grande)

 El cual claramente nos indica que el padre de INDIO es AFRO y que su abuelo es SATAN. De similar manera podríamos obtener los nombres de los perros que no tienen padre (bueno, en la vida real sí lo tienen pero no los tenemos registrados quizás porque no sabemos sus nombres), de los perros que no tienen abuelos, de los perros que son hermanos entre sí, de los hijos de un perro, de los nietos de un perro, etc.

En este ejemplo hemos usado dos columnas de la tabla ANIMALES (ANI_IDENTI y ANI_IDEPAD) para hacer la referencia.

Ejemplo 2:

Queremos conocer los nombres de los alumnos que se han aplazado en Matemática y también se han aplazado en Historia. Las calificaciones van del 0 al 100 y quienes obtienen menos de 40 puntos se aplazan.

La estructura de nuestra tabla de CALIFICACIONES es la siguiente:

AUTO10

(si haces clic en la imagen la verás más grande)

Y estas son algunas de sus filas:

AUTO11

(si haces clic en la imagen la verás más grande)

 Entonces, para ver los nombres de los alumnos que se aplazaron en Matemática y también en Historia podríamos escribir:

SELECT
   C.CAL_IDEALU AS "Id. alumno",
   A.ALU_NOMBRE AS "Nombre del alumno",
   C.CAL_NOTAXX AS "Matemática",
   D.CAL_NOTAXX AS "Historia"
FROM
   CALIFICACIONES C
JOIN
   CALIFICACIONES D
      ON C.CAL_IDEALU = D.CAL_IDEALU
JOIN
   ALUMNOS A
      ON C.CAL_IDEALU = A.ALU_IDENTI
WHERE
   C.CAL_NOTAXX < 40 AND
   D.CAL_NOTAXX < 40 AND
   C.CAL_CODMAT = 'MAT' AND
   D.CAL_CODMAT = 'HIS'

Con lo cual obtendríamos:

AUTO12

(si haces clic en la imagen la verás más grande)

 Y así sabríamos que RAQUEL se ha aplazado en ambas materias.

En este ejemplo hemos usado solamente una columna de la tabla CALIFICACIONES (la columna: CAL_IDEALU) para hacer la referencia pero hemos necesitado además de un WHERE para establecer la condición.

Conclusión:

Hay muchos casos en los cuales lo más eficiente que podemos tener es una tabla que se referencia a sí misma. Siempre que podamos meter toda la información que necesitamos en una sola tabla es una posibilidad a considerar, pues evitamos la redundancia y en general las consultas son más rápidas.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autorreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

Autorreferenciar una tabla. Algunos ejemplos.

5 comentarios

Hay ocasiones en las cuales la más eficiente forma de obtener el resultado de una consulta es a través de una tabla que se referencia a sí misma.

En el artículo:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

ya hemos visto como lograrlo, pero ese no es el único caso.

Por ejemplo, tenemos una tabla llamada VENTASCAB (cabecera de las ventas) algunas de cuyas filas son las siguientes:

AUTO1

(si haces clic en la imagen la verás más grande)

Y lo que nos interesa ver son las ventas a los clientes a quienes les vendimos el día 21/04/2012 y también el día 22/04/2012, o sea los clientes a quienes les hemos vendido en ambos días.

Este SELECT no funcionará:

SELECT
   VTC_IDENTI,
   VTC_TIPDOC,
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '21/APR/2012' AND
   VTC_FECHAX = '22/APR/2012'

Si lo ejecutamos esto será el resultado que obtendremos:

AUTO2

(si haces clic en la imagen la verás más grande)

 Correctamente nos dice que no hay datos para mostrar. ¿Por qué? porque en ninguna fila VTC_FECHAX es igual al 21 de abril y también igual al 22 de abril. En una fila VTC_FECHAX puede ser igual a una de esas fechas, pero no a ambas.

Probemos entonces de otra forma:

Este SELECT tampoco funcionará:

SELECT
   VTC_IDENTI,
   VTC_TIPDOC,
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '21/APR/2012' OR
   VTC_FECHAX = '22/APR/2012'

Al ejecutarlo este es el resultado que obtenemos:

AUTO3

(si haces clic en la imagen la verás más grande)

 Que tampoco es lo que queremos porque nos muestra los clientes a quienes le hemos vendido o el 21 de abril o el 22 de abril o ambos días. Pero a nosotros no nos interesan los clientes a quienes solamente les hemos vendido el 21 de abril ni nos interesan los clientes a quienes solamente les hemos vendido el 22 de abril. Los que nos interesan son los clientes a quienes les hemos vendido en ambos días y el SELECT anterior nos muestra a más clientes de los que necesitamos.

Entonces ¿cómo resolvemos este problema?

Mediante una autorreferencia a la tabla VENTASCAB, como se ve en el siguiente listado:

SELECT
   DISTINCT
   V1.VTC_IDENTI,
   V1.VTC_TIPDOC,
   V1.VTC_NRODOC,
   V1.VTC_FECHAX,
   V1.VTC_IDECLI
FROM
   VENTASCAB V1
JOIN
   VENTASCAB V2
      ON V1.VTC_CODSUC = V2.VTC_CODSUC AND
         V1.VTC_IDECLI = V2.VTC_IDECLI
WHERE
   ((V1.VTC_FECHAX = '21/APR/2012' AND V2.VTC_FECHAX = '22/APR/2012') OR
   (V1.VTC_FECHAX = '22/APR/2012' AND V2.VTC_FECHAX = '21/APR/2012'))

Al ejecutar este SELECT esto será lo que obtendremos:

AUTO5

(si haces clic en la imagen la verás más grande)

 Que es justamente lo que estábamos necesitando. Aquí podemos ver que al cliente con Identificador 348 le hemos hecho cuatro ventas el día 21 de abril y una venta el día 22 de abril. Y es el único cliente al cual le hemos vendido en ambos días.

¿Cuándo debemos autorreferenciar una tabla?

Cuando la respuesta está en ella y no se soluciona ni con AND ni con OR.

Artículos relacionados:

Usando un INNER JOIN para autoreferenciar una tabla

El índice del blog Firebird21

Comparando las arquitecturas

Deja un comentario

Este es un archivo PDF cuyo autor es Thomas Steinmaurer y que nos muestra en una sola página todas las características de cada una de las arquitecturas (SuperServer, Classic, SuperClassic, Embedded) del Firebird.

fb25_architecture_comparison

 

Un breve intermezzo

8 comentarios

Hace varios días que no publico artículos en mi blog, debido principalmente a que mi señora está por tener a nuestra primera hija y estamos a las apuradas por ese motivo, con casi cero tiempo libre.

Espero dentro de poco poder retornar a la escritura habitual de uno o dos artículos por día.

Saludos.

Walter.

 

Detectando modificaciones no autorizadas a nuestras tablas

10 comentarios

Si los usuarios pueden insertar, actualizar, o borrar registros de las tablas desde afuera de nuestras aplicaciones (Contabilidad, Producción, Sueldos, Ventas, etc.) eso solamente puede significar problemas, nada bueno obtendremos de sus acciones.

Por ejemplo, un empleado se enteró de cual es el password del usuario SYSDBA y como ese empleado conoce suficiente de Informática entonces se conecta a la Base de Datos como SYSDBA y estando allí … hace lo que se le ocurre.

Algunos ejemplos de lo que podría hacer:

  1. Se aumenta su propio sueldo, digamos en un 8%
  2. Se pone de acuerdo con un cliente de la empresa para marcar como cobrada una Factura que aún no ha sido cobrada
  3. Un alumno se aplazó en una materia, pero la marca como aprobada
  4. Modifica el precio de venta de un producto para que el vendedor cobre una comisión mayor por esa venta

Inclusive si es inteligente, podría aumentar su sueldo y también el de otros empleados más para que sea más difícil deducir que fue él quien lo alteró. Si un solo empleado recibió un aumento fraudulento de sueldo cuando se detecte todas las miradas se dirigirán hacia él; pero si son 30 los empleados que recibieron el aumento fraudulento del sueldo será más difícil encontrar al culpable. Si es lo suficientemente astuto inclusive pondrá en la lista de beneficiarios a aquellas personas con las cuales no se lleva bien: si Juan es el empleado deshonesto y todos saben que él y Raúl son enemigos, si Raúl también recibió el aumento (e inclusive un aumento mayor) será más difícil creer que el culpable es Juan.

Y entonces ¿por qué ocurrió ese aumento fraudulento? para los propietarios de la Empresa la respuesta es fácil … porque la aplicación de Sueldos falló, y por lo tanto el programador es el culpable. Y claro, el pobre programador puede pasarse horas y horas buscando errores en su código fuente y jamás los encontrará, porque el problema no está en su programa sino en la modificación de las tablas desde afuera de su programa.

¿Cómo se puede detectar si una fila se insertó o modificó desde afuera de nuestras aplicaciones?

Una técnica es agregándole a cada tabla una columna para “checksum”. Un checksum es un número que se usa para controlar que los datos que existen en las demás columnas fueron puestos de forma autorizada.

DETECTAR1

(si haces clic en la imagen la verás más grande)

En este ejemplo a la tabla de BANCOS se le agregó una columna llamada BAN_CHECKS donde se guardará el checksum de la fila.

Para generar el checksum un método es el siguiente:

  • Convertir el valor de cada columna a carácter
  • Concatenar todas esas columnas
  • Concatenarle un testigo
  • Grabar el hash

Ejemplo en Visual FoxPro:

Si conoces el lenguaje Visual FoxPro aquí puedes ver una posible implementación:

WITH ThisForm
   M.BAN_CODSUC = .Text1.Value
   M.BAN_IDENTI = .Text2.Value
   M.BAN_NOMBRE = .Text3.Value
   M.BAN_CHECKS = Str(M.BAN_CODSUC, 2) + Str(M.BAN_IDENTI, 3) + M.BAN_NOMBRE + "ASU-PAR-123456"
ENDWITH

lcComando = "EXECUTE PROCEDURE Grabar_Banco(?M.BAN_CODSUC, ?M.BAN_IDENTI, ?M.BAN_NOMBRE, ?M.BAN_CHECKS)"

=SQL_Exec(gnHandle, lcComando)

Y este es el stored procedure Grabar_Banco:

SET TERM ^ ;

CREATE PROCEDURE GRABAR_BANCO(
   CodSuc TYPE OF COLUMN BANCOS.BAN_CODSUC,
   Identi TYPE OF COLUMN BANCOS.BAN_IDENTI,
   Nombre TYPE OF COLUMN BANCOS.BAN_NOMBRE,
   CheckS TYPE OF COLUMN BANCOS.BAN_CHECKS)
AS
BEGIN

   UPDATE OR INSERT INTO BANCOS
            (BAN_CODSUC, BAN_IDENTI, BAN_NOMBRE, BAN_CHECKS)
     VALUES (:CodSuc   , :Identi   , :Nombre   , Hash(:CheckS)) ;

END^

SET TERM ; ^

¿Qué es un testigo?

Son los caracteres adicionales que se le agregan a un string para que los intrusos no obtengan el mismo resultado del hash que obtenemos nosotros. En el ejemplo el testigo es ASU-PAR-123456

Aunque los intrusos obtengan y graben un hash en la columna BAN_CHECKS jamás obtendrán el mismo resultado porque ellos no le están agregando el testigo al string.

Por ejemplo, ellos escriben:

INSERT INTO BANCOS VALUES(0, 0, ‘BANCO ITAÚ’, HASH(‘0 0 BANCO ITAÚ’))

Pero nosotros escribimos:

INSERT INTO BANCOS VALUES(0, 0, ‘BANCO ITAÚ’, HASH(‘0 0 BANCO ITAÚ ASU-PAR-123456’))

Y como los strings que son argumentos de la función HASH() son distintos también lo serán los valores devueltos por dicha función. Si ellos no saben que le agregamos un testigo al string (o lo saben, pero no saben cual es ese testigo) jamás obtendrán el mismo resultado de la función HASH()

Detectando una inserción o modificación no autorizada:

Al escribir: SELECT * FROM BANCOS esto es lo que obtenemos:

DETECTAR2

(si haces clic en la imagen la verás más grande)

¿Cómo podemos averiguar si todas las columnas tienen valores legítimos o alguna es fraudulenta?

Escribiendo el comando:

SELECT
   HASH(CAST(BAN_CODSUC AS CHAR(2)) || CAST(BAN_IDENTI AS CHAR(3)) || BAN_NOMBRE || 'ASU-PAR-123456') AS MiCheck,
   BAN_CHECKS
FROM
   BANCOS

Y esto es lo que obtenemos:

DETECTAR3

(si haces clic en la imagen la verás más grande)

 Si te fijas con atención verás que en la última fila los números no coinciden. ¿Por qué no coinciden? Porque fue ingresada de forma fraudulenta, o sea desde afuera de nuestra aplicación.

Si solamente quieres ver las filas fraudulentas podrías escribir:

SELECT
   BAN_CODSUC,
   BAN_IDENTI,
   BAN_NOMBRE
FROM
   BANCOS
WHERE
   HASH(CAST(BAN_CODSUC AS CHAR(2)) || CAST(BAN_IDENTI AS CHAR(3)) || BAN_NOMBRE || 'ASU-PAR-123456') <> BAN_CHECKS

Y en ese caso obtendrías esto:

DETECTAR4

(si haces clic en la imagen la verás más grande)

 Conclusión:

Si usas la función HASH() en una columna de checksum y a la función HASH() además del string con los datos le envías un testigo podrás demostrar que el valor que tiene una columna fue puesto desde afuera de tu aplicación. Y por lo tanto, tu programa no es el culpable de cualquier cosa mala que haya pasado.

Para tu mayor protección podrías firmar un contrato con tu cliente donde especificas claramente que te desligas de toda  responsabilidad si alguna fila fue insertada o modificada desde afuera de tu aplicación.

Artículos relacionados:

La función HASH()

El índice del blog Firebird21

Older Entries