Lección 5: Recuperar datos más complejos con uniones de tablas

Hasta este momento, cada una de las funciones de acceso a datos ha recuperado filas de una única tabla. En esta lección, aprenderá a utilizar uniones de tablas SQL con EGL para trabajar con varias tablas a la vez.

Por qué y cuándo se efectúa esta tarea

La descripción completa de las uniones de tablas SQL va más allá del ámbito de esta guía de aprendizaje. En resumen, una unión de tablas ensambla un conjunto de resultados procedente de dos o más tablas relacionadas.

Por ejemplo, la tabla CUSTOMERS de la base de datos incluye un número de ID de cliente como clave primaria, junto con los nombres de pila y los apellidos de los clientes (así como otras diversas columnas):

Tabla 1. Datos de ejemplo de la tabla CUSTOMER
CUSTOMER_ID	FIRST_NAME	LAST_NAME
1	Francisco	Ramirez
2	Andrea	Lundquist
3	Khaled	Said

La tabla ORDERS incluye un número de ID de pedido como clave primaria y el monto del pedido. También incluye el número de ID del cliente que ha realizado el pedido, haciendo que esa columna sea una clave foránea:

Tabla 2. Datos de ejemplo de la tabla ORDERS
ORDER_ID	CUSTOMER_ID	ORDER_AMOUNT
1	1	111,11
2	1	222,22
3	1	333,33

En este caso, la columna CUSTOMER_ID es adecuada para una unión de tablas, ya que esta columna crea una relación entre las dos tablas. En esta lección, unirá estas dos tablas con un componente SQLRecord personalizado, generando un conjunto de resultados que compara los clientes con los pedidos realizados por ellos.

Las uniones de tablas son operaciones de base de datos complejas, por lo que debe estar familiarizado con el comportamiento de la base de datos y probar la salida cuidadosamente. Como se muestra en esta lección, no siempre se obtienen los datos esperados.

Cree un archivo fuente EGL para que contenga un nuevo registro SQLRecord:
1. Pulse el proyecto EGLSQL con el botón derecho del ratón en la vista Explorador de proyectos y pulse Nuevo > Otros > EGL > Archivo fuente.
2. En la ventana Archivo fuente EGL nuevo, especifique el nombre data en el campo Paquete.
3. En el campo Nombre de archivo fuente EGL, especifique records.
4. Pulse Finalizar.
El archivo fuente EGL se crea y se abre en el editor.
En el archivo nuevo, debajo de la sentencia package, especifique el nombre de un SQLRecord nuevo para combinar las tablas CUSTOMER y ORDERS:
```
record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]]}
end
```
La primera línea de este registro es la misma que en los demás componentes SQLRecord: especifica un nombre para el registro y el estereotipo de SQLRecord. La segunda línea especifica las tablas con las que se relaciona este registro, como valores de la propiedad tableNames. Los registros de tabla única con los que ha trabajado en las lecciones anteriores sólo indicaban una tabla en esta propiedad, como por ejemplo la tabla ORDERS para el registro Orders:
```
record Orders type sqlRecord { 
  tablenames=[["EGL.ORDERS"]]
...
```
El registro OrderCustomerJoin que está creando debe indicar dos tablas en la propiedad tableNames para poder recuperar los datos de ambas tablas. A efectos de claridad, el registro incluye un alias para cada nombre de tabla ("O" para ORDERS y "C" para CUSTOMERS). Estos alias facilitan la referencia a los nombres de tabla en el registro, como verá muy pronto.
Una vez especificada la información básica para el componente Record, EGL puede especificar el resto. Sin embargo, primero debe especificar la base de datos de la que EGL recuperará información de conexión.
Establezca la base de datos Derby como base de datos SQL predeterminada para EGL:
1. Pulse Ventana > Preferencias.
2. En la ventana Preferencias, expanda EGL y pulse Conexiones de base de datos SQL.
3. En la lista Conexiones, seleccione EGLDerbyDB.
4. Pulse Aceptar.
Volviendo al archivo records.egl, sitúe el cursor sobre el nombre del registro (OrderCustomerJoin), púlselo con el botón derecho del ratón y luego pulse Registro SQL > Recuperar SQL. La característica de EGL Recuperar SQL crea un registro SQL a partir de las columnas de una tabla SQL. Este es sólo un aspecto del proceso que siguió al crear una aplicación de acceso a datos EGL en la lección 1.

En la solicitud de contraseña, conserva los valores predeterminados del nombre de usuario y la contraseña. La base de datos de ejemplo no requiere un nombre de usuario ni una contraseña determinados. La característica de EGL Recuperar SQL crea automáticamente el resto del componente SQLRecord, incluidos los campos correspondientes a las filas de las tablas y la propiedad keyItems:

record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
  keyItems=[CUSTOMER_ID, ORDER_ID]}

  CUSTOMER_ID int
    {column="C.CUSTOMER_ID", isReadOnly=yes};
  FIRST_NAME string
    {column="C.FIRST_NAME", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
  LAST_NAME string
    {column="C.LAST_NAME", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
...
  ORDER_ID int
    {column="O.ORDER_ID", isReadOnly=yes};
  orders_CUSTOMER_ID int
    {column="O.CUSTOMER_ID", isReadOnly=yes, 
      isSqlNullable=yes};
  ORDER_AMOUNT decimal(8,2)
    {column="O.ORDER_AMOUNT", isReadOnly=yes, 
      isSqlNullable=yes};
  ORDER_DETAILS string
    {column="O.ORDER_DETAILS", isReadOnly=yes, 
      isSqlNullable=yes, sqlVariableLen=yes, maxLen=111};
end

Observe que el valor de la propiedad column de cada campo empieza por el alias de la tabla especificado en la propiedad tableNames. Este alias puede ayudarle a realizar el seguimiento de la tabla con la que está relacionado un campo determinado.

Guarde el archivo records.egl, pero manténgalo abierto.
Cree un componente Program en el paquete programs denominado printCustomerOrders.
En el programa nuevo, elimine el código predeterminado.
En la función main del programa, cree una variable de matriz basada en el registro OrderCustomerJoin que acaba de crear:
```
allCustomerOrders OrderCustomerJoin[0];
```
Recuerde que puede utilizar la asistencia de contenido para insertar el tipo de componente Record pulsando Control+Barra espaciadora. Si no utiliza la asistencia de contenido, debe añadir manualmente una sentencia import al principio del archivo, inmediatamente debajo de la sentencia package:
```
import data.OrderCustomerJoin;
```

Utilice las sentencias open y forEach para recuperar las filas de la base de datos y visualizar los valores en la consola:

tempRec OrderCustomerJoin;
open resultSet for tempRec;
forEach (tempRec)
  SysLib.writeStdout(tempRec.CUSTOMER_ID :: " " :: 
    tempRec.LAST_NAME :: " " :: tempRec.ORDER_ID);
end

Guarde, genere y ejecute el programa. Si está familiarizado con las dificultades de las uniones de tablas, puede que haya observado el error de esta unión de tablas y la razón por la que la salida no resulta demasiado útil:
```
1 Filibuster 1
1 Filibuster 2
1 Filibuster 3
...
1 Filibuster 22
1 Filibuster 23
2 Lundquist 1
2 Lundquist 2
2 Lundquist 3
2 Lundquist 4
...
2 Lundquist 22
2 Lundquist 23
3 Kingman 1
3 Kingman 2
...
```
La base de datos no ha realizado una referencia cruzada lógica entre las tablas; en lugar de ello, ha emparejado cada fila de la tabla CUSTOMERS con cada fila de la tabla ORDERS en todas las combinaciones posibles. En estos resultados, cada cliente se registra como si realizara todos los pedidos, lo que es evidentemente incorrecto. Si explicita el código SQL subyacente a la sentencia open, puede ver que el código SQL está seleccionando cada una de las filas de ambas tablas sin utilizar una cláusula WHERE:
```
open resultSet for tempRec with
  #sql{
    select
      C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
      C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
      C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
      O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
      O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
    from EGL.CUSTOMER C, EGL.ORDERS O
    order by
      C.CUSTOMER_ID, O.ORDER_ID asc
  };
```
Para unir las tablas de forma significativa, debe indicar a la base de datos cómo debe fusionar las tablas lógicamente, en lugar de devolver la unión de las tablas. Podría hacerlo editando el código SQL explícito, pero en este caso debería hacerlo cada vez que utilice el registro, o de lo contrario correría el riesgo de recuperar datos inexactos. En lugar de ello, establecerá la condición de selección predeterminada para este registro de forma que el SQL implícito siempre realiza una unión de tablas significativa.
Si ha explicitado el código SQL subyacente a la sentencia open, hágalo de nuevo implícito situando el cursor sobre el nombre del registro, pulsándolo con el botón derecho del ratón y pulsando Sentencia SQL > Eliminar.
Vuelva a la definición del componente Record del archivo records.egl.
Añada la propiedad defaultSelectCondition a la definición del componente Record:
```
record OrderCustomerJoin type SQLRecord
  {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
  keyItems=[CUSTOMER_ID, ORDER_ID],
  defaultSelectCondition = #sqlCondition{ condition }}
```
La propiedad defaultSelectCondition especifica la cláusula WHERE en el código SQL implícito y el código SQL explícito predeterminado cuando se utiliza get u open con este registro.
Dentro del bloque #sqlCondition{}, sustituyendo el texto de "condition" predeterminado, especifique la cláusula WHERE correcta para una unión de tablas SQL entre estas dos tablas, teniendo cuidado de utilizar los alias de tabla en lugar de los nombres de tabla:
```
defaultSelectCondition = #sqlCondition{ O.CUSTOMER_ID = C.CUSTOMER_ID }}
```
No es necesario añadir la palabra clave WHERE; en este caso, EGL la inserta automáticamente en el código SQL.

Guarde el archivo. Ahora, si explicita el código SQL subyacente a la sentencia open, tendrá este aspecto:

open resultSet for tempRec with
  #sql{
    select
      C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
      C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
      C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
      O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
      O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
    from EGL.CUSTOMER C, EGL.ORDERS O
    where
      O.CUSTOMER_ID = C.CUSTOMER_ID
    order by
      C.CUSTOMER_ID, O.ORDER_ID asc
  };

Ahora, el código SQL incluye una cláusula WHERE que limitará las filas devueltas a aquellas cuyo número de cliente sea el mismo tanto en la tabla CUSTOMER como en la tabla ORDERS.

Guarde, genere y ejecute el programa.

Resultados

Con una unión de tablas significativa, el resultado de este programa muestra qué cliente ha realizado cada pedido:

1 Filibuster 1
1 Filibuster 2
1 Filibuster 3
...
1 Filibuster 11
1 Filibuster 18
2 Lundquist 7
2 Lundquist 8
2 Lundquist 12
2 Lundquist 15
2 Lundquist 20
3 Kingman 13
4 Liebowitz 14
6 Springsteen 22
7 St. Louis 16
8 Sharov 17
9 Hudak 23

A continuación figura el código completo de los dos archivos utilizados en esta lección. Si ve muchos errores marcados por símbolos X rojos en cualquiera de los archivos, asegúrese de que el código coincida con este código: Archivos printCustomerOrders.egl y records.egl completados después de la lección 5.