Firebird
Contenido


Firebird................................................................................................................................................... 1

Introducción............................................................................................................................................ 5

Descripción y características generales.................................................................................................... 5

Arquitectura....................................................................................................................................... 7

Servidor........................................................................................................................................ 7

Cliente.......................................................................................................................................... 7

Bloqueos, versiones y generaciones................................................................................................ 7

Operaciones con múltiples tablas.............................................................................................................. 9

Transacciones.................................................................................................................................... 9

Concurrencia 1: niveles de aislamiento de transacciones................................................................. 11

Concurrencia 2: modo de bloqueo de una transacción..................................................................... 12

Bloqueos pesimistas..................................................................................................................... 14

Mantener el contexto................................................................................................................... 15

Integridad referencial........................................................................................................................ 15

Instalación y puesta en marcha............................................................................................................... 17

Descarga desde Internet................................................................................................................... 17

Probar la instalación.......................................................................................................................... 17

ISQL.......................................................................................................................................... 18

Herramientas................................................................................................................................... 19

IBServer..................................................................................................................................... 19

IBGuardian.................................................................................................................................. 19

ISQL.......................................................................................................................................... 19

Gbak........................................................................................................................................... 19

IBConsole................................................................................................................................... 19

Ejecutar sentencias SQL en IBConsole.................................................................................... 21

IBOConsole................................................................................................................................ 22

IB_SQL...................................................................................................................................... 23

SQL..................................................................................................................................................... 24

El lenguaje de consulta...................................................................................................................... 24

SQLSQL son las iniciales de......................................................................................................... 24

Cursores vs conjuntos....................................................................................................................... 24

Selección de registros: SELECT........................................................................................................ 25

Orden en la sala........................................................................................................................... 29

Filtrados y búsquedas................................................................................................................... 30

Fechas/Horas en SQL.................................................................................................................. 32

Criterios de selección................................................................................................................... 34

Funciones.................................................................................................................................... 38

Funciones de agregación.............................................................................................................. 38

Subconsultas................................................................................................................................ 41

Comparación con el resultado de una subconsulta.......................................................................... 43

Uniones....................................................................................................................................... 45

Consulta de varias tablas relacionadas........................................................................................... 45

Creación, modificación y borrado de objetos............................................................................................ 52

Lenguaje de Definición de Datos (DDL)............................................................................................ 52

Bases de Datos........................................................................................................................... 52

Creación de una base de datos................................................................................................. 52

El tamaño no importa (perdón, Godzilla!)................................................................................... 53

Páginas.................................................................................................................................. 53

El juego de caracteres............................................................................................................. 54

Modificación de una base de datos........................................................................................... 57

Borrado de una base de datos.................................................................................................. 58

Tablas......................................................................................................................................... 58

Creación de una tabla.............................................................................................................. 58

Nombres de objetos................................................................................................................. 59

Tipos de datos soportados por Firebird...................................................................................... 59

Condiciones para los campos................................................................................................... 60

Campos calculados.................................................................................................................. 61

Restricciones.......................................................................................................................... 62

Modificación de la estructura de una tabla................................................................................ 63

Borrado de una tabla completa................................................................................................. 65

Dominios..................................................................................................................................... 65

Creación de dominios.............................................................................................................. 65

Modificación de dominios......................................................................................................... 66

Borrado de dominios................................................................................................................ 66

Indices........................................................................................................................................ 66

Crear un índice....................................................................................................................... 66

Modificar un índice.................................................................................................................. 66

Borrar un índice...................................................................................................................... 67

Restricciones.................................................................................................................................... 67

PK.............................................................................................................................................. 67

UQ............................................................................................................................................. 67

FK.............................................................................................................................................. 67

Check......................................................................................................................................... 67

Not Null...................................................................................................................................... 67

Generadores..................................................................................................................................... 67

Crear un generador...................................................................................................................... 68

Modificar un generador................................................................................................................ 68

Borrar un generador..................................................................................................................... 69

Excepciones..................................................................................................................................... 69

Definir excepciones..................................................................................................................... 69

Modificar excepciones................................................................................................................. 69

Borrar excepciones...................................................................................................................... 69

Programación del servidor...................................................................................................................... 70

Scripts............................................................................................................................................. 70

Procedimientos almacenados............................................................................................................. 71

Procedimientos de acción............................................................................................................. 71

Triggers................................................................................................................................................ 72

Funciones del usuario (UDF).................................................................................................................. 73

Librerías estándar de funciones externas............................................................................................ 73

Crear funciones externas en Delphi................................................................................................... 73

Acceso desde Delphi............................................................................................................................. 74

BDE................................................................................................................................................ 74

IBX................................................................................................................................................. 74

DBX................................................................................................................................................ 74

ADO............................................................................................................................................... 74

IBO................................................................................................................................................. 74

UIB................................................................................................................................................. 74

Optimización......................................................................................................................................... 75

Plan de ejecución.............................................................................................................................. 75

Creación de índices........................................................................................................................... 75

Selectividad...................................................................................................................................... 75

Restricciones declarativas vs. activas................................................................................................. 75

Restricción de la cantidad de registros a traer al cliente....................................................................... 75

Futuro................................................................................................................................................... 75



Introducción

 

El mundo de las Bases de Datos es ancho y ajeno, parafraseando a un escritor muy conocido. Existen muchos sistemas de éstos, con características que los distinguen unos de otros. No obstante, todos comparten el mismo núcleo de funcionalidad, su objetivo básico: sirven para almacenar y procesar datos, a veces cantidades realmente grandes.

Las diferencias vienen después. Cómo se almacenan los datos, qué tipos de datos pueden contener, qué tan avanzado es el lenguaje que permite consultar esos datos, características avanzadas para optimizar el proceso de grandes cantidades de datos, por nombrar unas pocas. Aquí veremos solamente uno de estos sistemas –en realidad dos, ya que son prácticamente iguales- aunque me referiré eventualmente a otros sistemas para hacer algunas comparaciones que nos permitan situarnos mejor en el tema.

El sistema principal que es objetivo del presente se denomina Firebird, literalmente Pájaro de fuego, el Fénix que renace de sus cenizas. No tengo confirmación ‘oficial’, pero creo que el nombre se eligió por las múltiples ‘vidas’ que ha tenido el proyecto Interbase, que culminaron en Firebird. Las características de este gestor de datos son prácticamente iguales –hasta la versión 1.0- a las de Interbase de Borland en su versión 6.0. Y es que Firebird fue creado a partir de los fuentes de Interbase 6.0, liberados por Borland bajo licencia IPL. Un conjunto de excelentes y voluntariosos programadores se impuso la tarea de llevar adelante el gestor de datos sin costo, agregando y mejorando características. En estos momentos (Febrero 2003) ya está disponible la beta 2 de la versión 1.5 de Firebird, que ya se aparta un poco de Interbase proveyendo características diferenciales tanto en el lenguaje de consulta como en los procesos internos.

Por su lado, Borland sigue avanzando también con Interbase –pero ya no en forma gratuita. Se han liberado ya dos versiones más de Interbase, 6.5 y 7. Las mejoras y nuevas características de estas versiones no están disponibles en Firebird, o tienen otros nombres y sintaxis. Borland ya tomó partido en cuanto a Firebird: no se va a hacer ningún esfuerzo por mantener la compatibilidad, no sólo de Interbase sino tampoco en los componentes de acceso de sus herramientas de desarrollo. Así, por ejemplo, el controlador DBExpress de Interbase soporta las características nuevas de Interbase 7 pero no las de Firebird 1.5. Cuando hablemos de las formas de acceder por programa al servidor nos ocuparemos de estos problemas y las soluciones que están apareciendo.

 

 

 

Descripción y características generales

 

Firebird es un Gestor de Bases de Datos Relacionales. Es decir que trabaja con el esquema relacional surgido en los laboratorios de IBM, de la mano de E. F. Codd y Chris Date. Este esquema ha sido implementado con éxito en forma comercial, y de hecho la mayoría de las aplicaciones de Bases de Datos se utilizan gestores que trabajan con este modelo[1].

Firebird es pequeña –una instalación completa ocupa alrededor de 10 Mb incluyendo librerías, ejemplos de programación y BD de muestra-, fácil de instalar y ejecutar, y aún así muy poderosa. Puede trabajar muy bien con cantidades de datos que van desde unos pocos hasta varios millones de registros por tabla. Generalmente no es necesario tomar medidas especiales para optimizar el rendimiento del servidor, aunque tenemos la posibilidad de tocar algunas variables para sacar más provecho a situaciones particulares.

Se puede ejecutar en una amplia variedad de sistemas operativos: Windows en todas sus encarnaciones, Linux, Solaris, MacOS y otros[2].

Por su tamaño, rendimiento y poca necesidad de mantenimiento es un gestor ideal para aplicaciones pequeñas y medianas, que se puede llevar sin problemas a trabajar en situaciones más exigentes y con mayor carga de trabajo y datos.

Físicamente, Firebird trabaja con uno o varios archivos adonde almacena todos los datos y estructuras: no más proliferación de archivos como teníamos con Paradox o Dbase. Los archivos de Firebird 1.0 –al igual que los de Interbase 6.0- pueden tener hasta 2 Gb de tamaño cada uno.

Firebird posee la mayoría de las características avanzadas de los gestores modernos:

·         Transacciones

·         Posibilidad de definir índices ascendentes o descendentes, con restricción de unicidad

·         Posibilidad de definir restricciones a los datos en la declaración de las tablas, como ser

o        Integridad referencial

o        Unicidad

o        Controles de validación (Check)

o        Columnas computadas, sólo de lectura

·         Posibilidad de programación del servidor mediante disparadores –triggers- y procedimientos almacenados –stored procedures.

·         Posibilidad de definir procedimientos almacenados que devuelvan ‘tablas virtuales’

·         Posibilidad de definición de vistas

·         Generadores de números secuenciales, independientes de las transacciones

·         Excepciones

·         Eventos

·         Funciones definidas por el usuario

·         Dominios

·         Un lenguaje SQL amplio y potente, que contempla funciones avanzadas como ser

o        Subconsultas

o        Uniones

o        Funciones de agregación

o        Ordenamiento por columnas calculadas, etc

o        Vectores multidimensionales (matrices)

 

En el apéndice <<<??>>> se puede ver un resumen de las características y límites técnicos de Firebird.

 

 

 

Arquitectura

 

Firebird se compone de dos partes: el servidor propiamente dicho, y el cliente que se comunica con él.

Servidor

El servidor es la parte que hace el trabajo: almacena  los datos, los procesa, ejecuta los procedimientos almacenados y aplica las validaciones. Debe existir uno por lo menos en la red.

Cliente

El cliente es la parte que se encarga de la comunicación entre las aplicaciones y el servidor. Maneja los protocolos, envía y recibe datos, envía comandos, etc. Es una capa mínima –una librería de enlace dinámico, algunos archivos auxiliares y entradas en el registro de servicios del sistema- pero necesaria para la comunicación con el servidor. Debe instalarse en todos los equipos que vayan a acceder al servidor, incluido el equipo adonde resida físicamente el mismo servidor si se va a usar como terminal.

Bloqueos, versiones y generaciones

La principal característica que distingue a Firebird/Interbase del resto de los gestores comerciales conocidos como Oracle, SQLServer o MySQL es su arquitectura generacional.

La mayoría de los gestores de datos relacionales implementan un mecanismo de transacciones, y Firebird no es la excepción; la diferencia está en cómo se implementa el mecanismo. Generalmente se utilizan bloqueos de diferentes niveles –registro, página, tabla- para impedir que otras transacciones modifiquen datos que se están procesando. Esto tiene la desventaja de que esas transacciones quedarán imposibilitadas de trabajar con los datos bloqueados hasta que la transacción que impuso el bloqueo termine.

Pensemos en el siguiente ejemplo: una empresa tiene que consolidar los datos de los inventarios de sus sucursales, para lo cual tiene que leer los valores de cada sucursal y acumularlos. La aplicación que hará la consolidación comienza una transacción y va leyendo y sumando los datos de cada sucursal; no modifica nada, por lo que no serían necesarios los bloqueos… termina de sumar todo, almacena o muestra el resultado y termina la transacción. Todos contentos.

Ahora pensemos qué pasaría si en el medio del cálculo una sucursal transfiere productos a otra, o simplemente los vende.

Claro que la operación debe quedar registrada en la base de datos, por lo que cambian los datos que se están sumando… si no tomamos medidas, podemos llegar a sumar varias veces los mismos productos, o a dejar alguno afuera.

Estas situaciones se controlan mediante los distintos niveles de aislamiento de las transacciones, que indican lo que una transacción verá de los cambios que produce otra transacción que se activa al mismo tiempo. Para que los datos de la transacción que acumula sean fidedignos, se debe usar el nivel de aislamiento más alto posible: esta transacción no debería ver los cambios hechos por las demás en todo el tiempo que dure. Este nivel se denomina generalmente de lecturas repetibles.

Para lograr este nivel de aislamiento sería necesario bloquear los cambios a todos los registros a considerar, o hacer una copia (una nueva generación de datos) de los registros usados para la acumulación, que entonces podrían ser leídos cuantas veces fuera necesario. La mayoría de los gestores de bases de datos utilizan el primer mecanismo; Firebird usa el segundo.

 

En breve: cada vez que se hace una modificación a un registro, se crea una nueva versión o generación del mismo marcándola con un número que identifica a la transacción que realizó el cambio. Con la ayuda de otra estructura de la que hablaremos enseguida, este mecanismo permite a Firebird lograr el nivel de lecturas repetibles sin usar bloqueos, así como evitar el uso de archivos de registro de operaciones (log).

 

Las transacciones existentes en un determinado momento en la base de datos pueden estar en uno de tres estados[3]:

·         Activa

·         Aceptada (Committed)

·         Cancelada (Rolled back)

El estado de todas las transacciones existentes en la BD es mantenido en las Páginas de Inventario de Transacciones o TIP (Transaction Inventory Pages). Es una lista de todas las transacciones que tienen datos en la base en cada momento, acompañadas de su estado.

Cuando comienza una transacción, se le asigna un número único que la identifica (llamado UTN, Unique Transaction Number) y se le ‘entrega’ una lista de todas las transacciones que tienen datos en la base y sus estados a ese momento: una copia de la TIP.

Los números identificadores de las transacciones (UTNs) son enteros crecientes en el tiempo; es decir, si una transacción comienza después de otra es seguro que recibirá un UTN más grande que la primera. La consecuencia inversa será muy usada en el mecanismo de versiones: si hay dos versiones de un mismo registro, la que tenga la UTN más grande será posterior a la otra.

Cuando una transacción tiene que actualizar un registro, busca en su TIP si había otras transacciones activas al momento de comenzar. Si no hay ninguna otra, entonces actualiza el registro sin más. Pero si encuentra al menos una transacción activa en la TIP, no modifica el registro original: crea una nueva versión almacenando las diferencias (para ahorrar espacio) y su propio número de transacción, el UTN.

Ahora bien, si una transacción quiere leer un registro, compara su propio identificador con el de la última versión del registro. Si el UTN de la última versión es mayor al de la transacción en cuestión, se toma la versión anterior del registro. Así se recorre la cadena de versiones hasta encontrar una cuyo UTN sea menor que el de la transacción, y que además esté marcada como ‘Aceptada’ en el inventario (TIP). Cualquier otra versión es pasada por alto.

 

Ejemplo. Supongamos que tenemos la siguiente secuencia temporal:

 

  1. La transacción 100 modifica un registro. Se crea una versión con UTN=100.
  2. Se acepta la transacción 100 (estado: committed)
  3. Comienza la transacción 103, que modifica el mismo registro. Se crea una nueva versión con UTN=103.
  4. Se cancela la transacción 103 (estado: rolled back)
  5. Comienza la transacción 110, que modifica el registro. Se crea una nueva versión con UTN=110. La transacción no termina todavía (estado: active)
  6. Comienza la transacción 112.
  7. Comienza la transacción 115, que modifica el registro. Se crea una nueva versión con UTN=115.
  8. Se acepta la transacción 115 (estado: commited)
  9. La transacción 112 lee el registro en cuestión. ¿Cuál es la versión que recupera?

La versión 115 es posterior a su propio UTN, por lo que no se considera. Busca el anterior.

La versión 110 corresponde a una transacción que no ha terminado (estado = active) por lo que también se ignora.

La versión 103 corresponde a una transacción cancelada, por lo que también se ignora.

Finalmente, la versión 100 es correcta y es la que se recupera.

 

Con este mecanismo se obtienen dos beneficios directos:

·         Se trabaja en un nivel de aislamiento de lecturas repetibles sin bloquear las actualizaciones concurrentes a la lectura

·         No se necesita llevar un registro de las operaciones para recuperar la base de datos de fallos; toda la información está incluida en las versiones y la TIP. Lo único que necesita hacer el servidor para recuperar una base de datos después de un fallo de alimentación por ejemplo, es recorrer la TIP y marcar como canceladas a todas las transacciones que figuran como activas. De esta manera, los cambios que se habían producido son ignorados en subsecuentes operaciones.

 

Por supuesto que también hay alguna contra: la base de datos va acumulando ‘basura’ en la forma de versiones que no son utilizables. Esto obliga a ejecutar una acción de ‘limpieza’ a intervalos, proceso que se denomina sweep. Este proceso elimina las versiones anteriores de los registros, dejando solamente la última valida, pero puede impactar negativamente en la performance del servidor. Por defecto se ejecuta automáticamente cada 20.000 transacciones. Se puede configurar esta cantidad en los parámetros del servidor, y también se puede ejecutar manualmente la limpieza.

 

[ERC1] 

[ERC2] 

 

Operaciones con múltiples tablas

 

En el modelo relacional tenemos que lidiar con operaciones que involucran varias tablas. Por ejemplo, el típico caso de un pedido o una factura: tendremos por lo menos dos tablas, una para los datos que identifican al pedido (fecha, cliente, nro, etc) y la otra con los datos de cada item que compone el pedido (cantidad, codigo, precio unitario, etc). Cuando se ingresa una factura, también generalmente modificaremos datos en otras tablas: stock o inventario, cuenta corriente del cliente, etc. Sería muy problemático si por cualquier causa –como un corte de conexión- se interrumpe el proceso de actualización de algunas tablas después de terminar con otras: pueden quedar registros huérfanos, por ejemplo items que no tienen factura, o datos desactualizados como el total de la cuenta corriente del cliente.

Hay varias técnicas para combatir estos problemas, un par de las cuales veremos ahora: transacciones y restricciones de integridad referencial.

 

 

Transacciones

 

Una transacción es un conjunto de operaciones que se aceptan (Commit) o rechazan (Rollback) como una sola. Son como paréntesis que encierran las operaciones que se deben realizar o cancelar juntas: todas las operaciones quedan ‘en suspenso’ hasta que se termina la transacción. No se podrán ver desde ninguna otra transacción, lo cual es una de las mayores sorpresas cuando uno empieza a trabajar de esta manera (si no tenemos esto en cuenta, los cambios realizados en una estación no se verán desde otra hasta que se cierre y se vuelva a abrir la aplicación).

Las transacciones deben tener los siguientes atributos, reconocidos generalmente el acrónimo ACID, por sus iniciales en inglés:

·                    A por Atomicity (Atomicidad). Las operaciones incluidas en una transacción se manejan como un todo indivisible. Todas las operaciones se aceptan juntas, o ninguna.

·                    C por Consistency (Consistencia). Las transacciones siempre llevan la base de datos desde un estado consistente a otro; no pueden quedar datos inconsistentes, por ejemplo dos registros en la misma tabla con el mismo valor en la clave primaria. Si pueden darse inconsistencias durante el desarrollo de una transacción; pero las demás transacciones simultáneas que existan no verán este estado, y todas las inconsistencias deberán resolverse al momento de terminar la transacción.

·                    I por Isolation (aIslamiento). Cada transacción se ejecuta como si estuviera sola en la base de datos; los cambios producidos por otras transacciones concurrentes no deben verse desde las demás.

·                    D por Durability (Durabilidad o persistencia). Una vez que una transacción se acepta, sus cambios quedan fijos en la base de datos aún en caso de una falla del sistema o caída de la conexión. Como un corolario, también se asegura que los cambios no se mantendrán en caso de una caída prematura del sistema, antes que la transacción sea aceptada con Commit.

 

La implementación de transacciones de Firebird cumple con todas estas propiedades.

 

Debido al diseño de Firebird, todos los comandos deben ejecutarse en el contexto de una transacción; si no la controlamos nosotros, se empieza una automáticamente antes de ejecutar cada comando y se termina también automáticamente al finalizar el comando. Cuando queremos agrupar varias acciones en una sola transacción, debemos comenzarla y terminarla explícitamente. La acción de aceptar todos los cambios de la transacción se denomina en inglés Commit, mientras que el proceso de rechazar los cambios, volviendo atrás todas las acciones realizadas, se denomina Rollback.

El lenguaje de programación de Firebird no tiene sentencias de manejo de transacciones; esto es así porque todos los accesos a Interbase deben hacerse dentro del contexto de una transacción, y no se aceptan transacciones anidadas. Es decir que todos los procedimientos almacenados o triggers que puedan llegar a ejecutarse lo harán siempre dentro de la transacción del cliente que los ejecuta; en otras palabras, ¡la responsabilidad es toda nuestra!

En base a lo discutido, podemos ya entrever algunos problemas comunes en las aplicaciones y sus causas:

 

·                    Los datos se pierden al cerrar la aplicación

Se aceptaron los cambios en la aplicación, pero no se ha aceptado la transacción. Los datos están ‘en suspenso’ en la Base de Datos hasta que se ejecute Commit, de tal manera que si nuestra aplicación no lo hace y cierra la conexión, el servidor asumirá que se produjo algún problema y hará un Rollback automático; todas las modificaciones realizadas en esa transacción se perderán..

 

·                    Los cambios hechos en una estación no se reflejan en otras que acceden simultáneamente a los mismos datos

Esto es una consecuencia de las propiedades de Consistencia y Aislamiento. Como dijimos antes, las modificaciones realizadas dentro de una transacción deben ser aceptadas o canceladas en conjunto. Hasta ese momento, quedan en un ‘limbo’ informático y nadie se enterará de su existencia. Incluso después de terminada correctamente una transacción puede haber diferencias en lo que se ve en una y otra estación de trabajo… depende del nivel de aislamiento de las distintas transacciones concurrentes, un tema que veremos en breve.

 

·                    Problemas de bloqueos en registros que ya se modificaron

Cuando se trabaja con múltiples usuarios a la vez es importante mantener las transacciones de corta duración. ¿Por qué? Porque como hemos visto, los otros usuarios no verán nuestros cambios hasta que cerremos nuestra transacción; pero además, los registros modificados son bloqueados para la edición desde otras terminales –bloqueo que se mantiene mientras dura la transacción. Imaginemos que desde un puesto de venta se comienza una factura, se actualiza el stock, y antes de terminar la transacción el empleado se detiene a tomar un café… ¡no se podrán vender los mismos productos desde otras terminales hasta que vuelva y termine con su transacción!

También hay un problema con el “recolector de basura”de Firebird, pero ese problema es menos urgente que los anteriores (se nota después de un tiempo). En breve, el sistema recolector de basura de Firebird se activa después de N transacciones (un parámetro de configuración del servidor) y se encarga de eliminar los objetos auxiliares que crea el servidor mientras procesa los datos. Si dejamos pasar mucho tiempo sin que se active este sistema, se puede ralentizar mucho la operación del servidor.

 

Concurrencia 1: niveles de aislamiento de transacciones

 

Cuando se trabaja en modo multiusuario[4], accediendo desde varias estaciones a la misma Base de Datos, hay que tener especial cuidado con las transacciones concurrentes de los clientes. El servidor define varios niveles de aislamiento de transacciones concurrentes, que indican cómo se relacionan las transacciones simultáneas entre sí.

Como un ejemplo, consideremos la siguiente situación: un usuario empieza una transacción y hace algunos cambios. Mientras todavía su transacción está activa, otro usuario empieza una segunda transacción. El segundo usuario no verá los cambios que realice el primero, a menos que esté en el modo más bajo de aislamiento (que no existe en Firebird, ver a continuación). Este comportamiento puede confundir al más pintado, ya que posiblemente las dos transacciones se hagan... en la misma máquina! Entonces sería posible realizar un cambio en una ventana y no verlo desde otra; incluso re-ejecutando la consulta.

 

Los niveles de aislamiento de transacciones de Firebird son los siguientes:

read commited (lectura de lo aceptado): si estamos en una transacción con este nivel de aislación, podremos ver los cambios de los demás cuando ellos terminen su transacción con commit (nosotros tendremos que repetir la consulta, o sea cerrar y volver a abrir el conjunto de datos). No es necesario que terminemos nuestra transacción para ver las modificaciones.

snapshot (lectura repetible[5]): se garantiza que el usuario que esté en una transacción de este tipo verá siempre los mismos datos, aunque otros usuarios hagan cambios y los acepten. No veremos los cambios hasta que cerremos nuestra transacción y comencemos una nueva. Este nivel de aislamiento es ideal para los reportes, ya que en un entorno multiusuario puede darse que cambien los datos entre el momento de la vista previa en pantalla y la impresión propiamente dicha.

snapshot table stability (lectura repetible forzada): igual que la anterior, pero además desde el momento que accedemos a una tabla ésta se bloquea para escritura. Este nivel es propio de Interbase, y no es muy usado porque impone una restricción muy severa a los otros usuarios, que sólo estarán habilitados para leer de todas las tablas que toquemos mientras estemos en la transacción.

 

Hay un nivel más de aislamiento que es común en los sistemas de bases de datos locales: el llamado dirty read o de ‘lectura sucia’. En este nivel se pueden ver los cambios de las demás transacciones, inmediatamente. Se pueden producir problemas por leer datos que no han sido todavía aceptados; si el usuario que hizo la modificación finalmente rechaza los cambios, hemos leído datos inexistentes –se denominan comúnmente filas fantasma, phantom rows. Este nivel no cumple con las propiedades de aislamiento y consistencia, por lo que no se permite en Firebird.

 

Concurrencia 2: modo de bloqueo de una transacción

 

Cuando se modifica un registro desde una transacción, éste se bloquea para modificaciones desde otra transacción; el bloqueo impide que se haga una segunda modificación sobre los mismos datos mientras todavía no se han aceptado o rechazado los primeros.

Hay dos metodologías de bloqueo: optimista y pesimista.

·                    Los servidores que implementan bloqueos pesimistas asumen que es muy probable que dos usuarios accedan simultáneamente a los datos para modificarlos; entonces toman una postura preventiva y cuando un usuario empieza a editar un registro éste queda inmediatamente bloqueado para la edición desde cualquier otra terminal. Este modo de trabajo es muy común en los sistemas de bases de datos de escritorio.

·                    Los servidores SQL asumen en cambio que no es tan probable que se produzcan ediciones simultáneas a los mismos registros; basándose en este supuesto, solamente bloquea un registro cuando se ha realizado realmente una modificación en el mismo –notificando a la base de datos mediante Post, por ejemplo. Note que no hemos terminado todavía la transacción. Mientras tanto, el mismo registro puede estar siendo modificado en la memoria interna de varias terminales a la vez. La primera terminal que guarde el registro (post) bloquea los cambios de las demás. Cómo reaccionarán las aplicaciones que se encuentran con el bloqueo, depende de la configuración de las correspondientes transacciones como veremos a continuación.

 

El parámetro que se indica cómo reaccionará una transacción al encontrarse un registro bloqueado se denomina nivel de bloqueo, y se configura al momento de comenzar la transacción. Firebird tiene las siguientes opciones:

 

·                    Modo en espera (WAIT): si hay un conflicto con otra transacción (por ejemplo, las dos tratan de modificar el mismo registro y aceptar los cambios), el último proceso queda bloqueado hasta que se termina la primera transacción.

 

·                    Modo sin espera (NO WAIT): si hay un conflicto con otra transacción, el proceso recibe un mensaje de error inmediatamente.

 

¿Cuál es la razón de ser del primer modo? Pues que el conflicto puede ser temporal. Supongamos que un usuario (1) empieza una transacción en la cual agrega un registro con un valor A en la clave primaria. Antes que este usuario haga Commit, otro usuario (2) trata de ingresar un registro también con el valor A en la clave primaria. Si la segunda transacción se hace en modo WAIT, el usuario 2 queda bloqueado hasta que el primero termine su transacción. Si (1) hace Commit, (2) recibe un mensaje de error porque la clave primaria estaría duplicada; pero si (1) cancela su transacción con Rollback, (2) puede insertar su registro sin problemas. En el caso que la segunda transacción fuera NO WAIT, el usuario (2) recibe el mensaje de error al momento de querer insertar el registro, aunque luego (1) cancele los cambios.

 

Los distintos modos de aislamiento junto con los niveles de bloqueo se configuran mediante parámetros en la llamada a la API interna del servidor que comienza la transacción. Podemos ver algunos ejemplos en la configuración de los niveles de aislamiento pre-configurados en los componentes Interbase Express (IBX):

Snapshot: no se ven los cambios realizados por las otras transacciones, pero se permite el acceso de las mismas a los datos que estamos mirando o modificando; igualmente, podemos ver los datos que otra transacción esté trabajando al mismo tiempo, aunque vemos la versión que existía al momento de iniciar nuestra transacción. Parámetros por defecto: concurrency, nowait. Ideal para reportes.

 


Read Commited: los cambios realizados por los otros usuarios se ven después que acepten sus transacciones. Parámetros por defecto: read_commited, rec_version, nowait.

El parámetro rec_version hace que sólo veamos la última versión estable (aceptada) de los registros. La alternativa es no_rec_version, con lo que indicamos al servidor que solamente se puede ver un registro si no hay versiones pendientes de aceptación (más sobre esto luego, cuando tratemos la arquitectura multigeneracional). En el caso que desde otra transacción se haya modificado un registro pero todavía no se haya realizado commit, el servidor no nos dejaría modificar el registro.

 

Read-Only Table Stability: esta transacción no puede acceder a datos que hayan sido cambiados desde que empezó, ni permite a otras transacciones que accedan a los datos que ésta haya leído. Parámetros por defecto: read (sólo lectura, en este modo no se pueden modificar los datos), consistency (garantiza que los datos vistos por esta transacción no cambien, bloqueando los registros para escritura).

 

Read-Write Table Stability: igual que la anterior, pero ahora la transacción bloquea los registros tanto para lectura como para escritura. Parámetros por defecto: write, consistency.

 

Los modos más usados son el Snapshot y el ReadCommitted, ya que permiten el acceso concurrente a los mismos datos (sin bloqueos innecesarios).

 

Por ejemplo supongamos una tabla Mascotas con los siguientes datos:

 

Mascotas

Nombre

Categoria

Raza

Edad

Id

 

Lulú

 

Perro

 

Caniche

 

5

 

89

 

Tom

 

Gato

 

Angora

 

3

 

908

 

Pinky

 

Ratón

 

Estupidis mousiis

 

6

 

346

 

Supongamos que esta base de datos se accede desde dos terminales a la vez, A y B. Veamos algunos casos típicos (las acciones se suponen en la secuencia dada). Para el caso no importa si usamos un componente IBTable, IBQuery con IBUpdateSQL o un IBDataset.  Indique después de cada acción qué datos ve cada usuario:

·                    A inicia una transacción en modo Snapshot.

·                    B inicia una transacción en modo ReadCommitted y lee los datos de la tabla de mascotas.

·                    A pide el contenido de la tabla Mascotas. ¿Qué obtiene?

Lo mismo que está arriba

 

·                    B modifica la tabla de mascotas, arreglando la edad de Tom que no es 3 sino 4. B hace Commit y vuelve a abrir la tabla.

A ve lo mismo. B ve los datos de Tom modificados

 

·                    A actualiza la vista de los datos cerrando y abriendo la tabla pertinente, pero sin salir de su transacción.

A: lo mismo (no ve los cambios)

 

·                    A modifica la edad de Lulú, que no es 5 sino 4. Hace Commit y vuelve a abrir la tabla.


A ve ahora el cambio anterior de B. B todavía no ve el cambio de A.

 

·                    B cierra y abre la tabla.

Ahora B puede ver los cambios de A

 

·                    B emite la instrucción “Select * from Mascotas where Edad=4

B obtiene los datos de Lulú y Tom.

 

·                    B agrega un registro a la tabla de Mascotas, con los siguientes valores: ‘Paco’, ‘Loro’, ‘Apátrida’, 10, 79. Hace Commit. A actualiza la vista cerrando y abriendo la tabla.

A no ve los cambios hasta que no cierre su propia transacción.

 

·                    A borra el registro de Tom. Hace Commit.

B tiene que cerrar y abrir la tabla para ver los cambios.

¿Qué pasa si B modifica el registro que A acaba de borrar?

 

 

Bloqueos pesimistas

A partir de Firebird 1.5, se puede usar un modo de bloqueo pesimista, indicando explícitamente en la sentencia SELECT que se desea bloquear los registros resultado de la consulta. Veremos algunos ejemplos cuando estudiemos la sentencia SELECT más adelante; por ahora simplemente digamos que este agregado se hizo para simplificar la solución de algunos problemas. No es la forma recomendada de trabajar con los datos, y tiene además varias restricciones. Citando las notas de la versión 1.5:

"No está disponible en subconsultas, ni para joins. No se puede especificar con el operador DISTINCT, la cláusula GROUP BY o cualquier otra operación de agregación de registros. No puede utilizarse con o en una vista, ni con tablas externas, ni con los resultados de un procedimiento almacenado llamado desde un SELECT."

 

 

Mantener el contexto

Una característica que a veces es molesta: cuando cerramos una transacción ya sea con Commit o con Rollback, se cierran todos los controles de datos asociados a la misma. Firebird libera los recursos de la transacción, por lo que los componentes de datos se desconectan. Hay que volver a abrir uno por uno todos los componentes de acceso a datos que trabajen con esa transacción.

Para evitar el tener que abrir nuevamente todo, podemos indicar a Interbase que termine la transacción pero que mantenga los recursos internos -el contexto de la transacción. De esta manera mantenemos los componentes conectados aunque aceptamos o cancelamos la transacción. Las operaciones se denominan  CommitRetaining y RollbackRetaining.

Hay un problema con esta manera de proceder: mientras no cerremos totalmente la transacción, se evita que el Recolector de Basura de Interbase trabaje. El accionar del servidor se vuelve más lento por la proliferación de versiones –la mayoría ya obsoletas- de registros. Esto no debería ser un problema a menos que la aplicación se mantenga funcionando de esa manera durante horas, días, tal vez semanas... en cuyo caso podemos acomodar las cosas haciendo una copia de seguridad (backup) seguida de una restauración sobre la misma base (restore). Cuando se hace un Backup se eliminan los datos temporales (la “basura”) y al recuperar la Base de Datos ésta queda limpia de polvo y paja.

 

 

Integridad referencial

 

La integridad referencial se refiere al estado de las referencias entre tablas. Es importante que las referencias se mantengan, para asegurar que los datos se puedan acceder correctamente. Por ejemplo, si tenemos en una tabla los datos de una factura, y en otra el detalle de los productos pedidos. En la tabla de detalle seguramente tendremos solamente una referencia a la factura si hemos normalizado la base de datos. El valor del campo de referencia (por ejemplo, el Nro de factura) se debe corresponder al valor de algún campo clave en la tabla de facturas, con lo cual podemos recuperar el resto de los datos. Ahora pensemos qué sucedería si se modifica el valor del campo de enlace en cualquiera de las puntas de la relación.

·         Si modificamos el nro de factura en la tabla de detalle, estaremos referenciando a otra factura: esto puede estar bien, de hecho es la única manera de corregir un error en la asignación.

·         Si modificamos el nro. de factura en la tabla de facturas, pues… eso sí puede representar un problema, ya que todos los registros de detalle quedarían huérfanos, sin que sepamos a qué factura corresponden. Esto no se puede permitir, ya que no tendremos forma de restaurar la relación.

 

<<<Gráfico con la relación del ejemplo, con un diagrama E/R>>>

 

Las comprobaciones que impidan estos problemas y mantengan la integridad referencial se pueden llevar a cabo en el cliente o en el servidor; hablaremos ahora de esta última opción.

Firebird implementa la restricción denominada clave externa en forma declarativa: se crea la restricción como una propiedad más de una tabla, y el servidor se encargará de hacerla cumplir cuando sea necesario. En contraste, podemos hacer un control activo escribiendo triggers que controlen explícitamente las referencias.

Las restricciones de clave externa (foreign key) se declaran en la tabla detalle de la relación, en la tabla que referencia a la maestra. En nuestro ejemplo, la tabla de facturas sería la maestra, y la de detalles…

Para declarar una restricción de clave externa necesitamos lo siguiente:

·         Un campo o conjunto de campos del mismo tipo en las dos tablas. Los valores de estos campos en la tabla detalle referenciarán los valores de los campos correspondientes en la tabla maestra (el nro. de factura del detalle, en nuestro ejemplo)

·         Una restricción de unicidad sobre los campos de la relación en la tabla maestra. Puede ser una clave primaria o una restricción UNIQUE; cualquiera de las dos posibilidades implica que los campos de la relación deben ser declarados como no null (no nulables).

·         NO es necesario un índice sobre los campos de la tabla detalle; sí es conveniente para acelerar las operaciones de selección de datos sobre las dos tablas, por lo que Firebird crea un índice automáticamente sobre estos campos.

Firebird permite especificar la acción a tomar cuando se intenta violar la integridad referencial (por ejemplo cuando borramos un cliente que está referenciado en una factura), tanto para las operaciones de borrado como de actualización. Las opciones son (los nombres son parte de la sintaxis de la instrucción SQL de declaración):

·         No action: la operación se rechaza de plano.

·         Cascade: la acción se realiza también en los registros referenciados, automáticamente. Por ejemplo si cambiamos el nro. de un cliente y éste tiene hechas dos facturas, en las dos facturas se actualizará el nro. de cliente para que la referencia siga siendo válida. Si borramos el cliente, se borrarán las facturas que lo referencian –sin ningún aviso por parte del servidor, así que cuidado!

·         Null: el campo de la tabla maestra en el que se referencia al dato modificado o borrado toma el valor NULL, es decir, pierde la referencia.

·         Default: el campo de la tabla maestra en el que se referencia al dato modificado o borrado toma el valor por defecto declarado para ese campo.

 

Las más usadas son las dos primeras. Firebird incluso nos permite indicar una acción para un caso y otra para el restante, por ejemplo una combinación muy usada es NoAction en borrados, Cascade en modificaciones. El servidor entonces mantendrá las referencias aunque cambiemos el campo en la tabla de detalle, mientras que impedirá que eliminemos registros de detalle que estén siendo referenciados.

 

Esta forma de mantener la integridad referencial es muy práctica y valiosa, aunque a veces lleva a un comportamiento no óptimo en el que las operaciones llevan más tiempo del necesario. Esto es debido a la selectividad de los índices, tema que trataremos más adelante. En esos casos en que el comportamiento no sea el adecuado, tendremos que recurrir a una verificación activa de las relaciones usando triggers. Lo veremos también más adelante.

 

En la práctica, las restricciones de integridad referencial se aplican generalmente cuando se va a poner en producción el sistema, no antes. ¿Por qué? Pues porque el servidor no nos dejará modificar la estructura de una tabla que forme parte de una restricción de integridad referencial, y es muy común que la estructura varíe en el período de diseño y programación del sistema. Así, se crean todas las tablas y demás objetos, se trabaja con esta base de datos, y finalmente se agregan las restricciones para que no haya sorpresas. Es muy fácil generar un archivo script con las instrucciones SQL necesarias para declarar las restricciones a posteriori de la creación de las tablas. No obstante, hay que tener en cuenta que las restricciones se comprueban en el momento de su declaración –esto es, si tenemos datos que no cumplan con las restricciones no se podrán crear estas últimas.

 

 


Instalación y puesta en marcha

 

La instalación y puesta en marcha de Firebird es muy simple. Se incluye un programa instalador al estilo experto que nos guiará paso a paso en la instalación; sólo tendremos que seleccionar qué partes instalar y adónde.

A continuación veremos el proceso completo desde la descarga de los archivos de Internet, la instalación paso por paso y daremos un primer saludo a las herramientas que se incluyen con el paquete.

 

Descarga desde Internet

 

Firebird es de código abierto, y como tal se lo puede encontrar en Internet para su descarga libre y gratuita. No obstante, es un motivo común de confusión la existencia de distintas versiones, tanto de Firebird como de Interbase. Aquí trataré de aclarar el lío.

Se puede descargar el código fuente de FB/IB, en cuyo caso será necesario compilarlo, o bien el paquete de instalación ya compilado –lo que denominaremos formato binario. El proceso de compilación de los fuentes quedará para otros escritos; aquí me limitaré a hablar del gestor ya compilado.

El primer sitio donde Interbase estuvo disponible en su version Open Source fue, por supuesto, el de Borland. No he podido verificar si sigue disponible, por lo que pienso que no.

Hay otro sitio relacionado con Interbase desde sus inicios: http://mers.com[ERC3] . Es una compañía canadiense que desarrolla software usando herramientas de Borland. El primer grupo de soporte a usuarios de Firebird estuvo localizado en sus servidores hasta que el presidente de la compañía, Robert Schiek, fue contratado por Borland donde se desempaña ahora como administrador de la comunidad de Interbase (http://bdn.borland.com/interbase[ERC4] ). La lista de soporte ahora funciona en Yahoo! Groups, pero todavía hay un índice de las preguntas y respuestas en Mers, que se puede consultar con un buscador. En el sitio de la compañía se puede encontrar la versión 6.0.2 de Interbase Open, para Windows y Linux.

Y llegamos a Firebird. El proyecto comenzó apenas una semana después de la liberación de Interbase, en SourceForge (http://sourceforge.net/projects/firebird) [ERC5] -donde todavía se lo puede encontrar. El sitio oficial es ahora http://www.firebirdsql.org[ERC6] , un sitio derivado de IBPhoenix (http://www.ibphoenix.com[ERC7] ), de donde se pueden obtener las distintas versiones de Firebird así como herramientas, artículos, etc. La versión actual (estable) es la 1.0.2.908[ERC8] , y se puede descargar también una versión 1.5 Alpha (no para producción, inestable y sin garantías). Se espera que pronto se ponga en marcha la versión beta de Firebird 1.5.

Entonces, hagamos un resumen:

 

Versión

URL para descarga

Interbase 6.0.2

http://mers.com

Firebird 1.0.3

http://www.firebirdsql.org

Firebird 1.5.1

http://www.firebirdsql.org

 

La instalación es muy sencilla… cuando todo funciona :-P. en Windows, corremos el archivo ejecutable; en Linux, instalamos el paquete rpm o bien ejecutamos el script si descargamos la version no compilada. Las opciones son mínimas y se reducen a seleccionar los componentes a instalar. Cuando hay algun problema, bueno… habrá que empezar a preguntar. El mejor lugar para encontrar respuestas es el foro de soporte firebird-support en yahoogroups.com.

 

 

Probar la instalación

 

ISQL

 

Con la distribución estándar de Firebird se incluye una utilidad para ejecutar sentencias SQL contra el servidor, y mostrar sus resultados. Esta utilidad es de línea de comandos y se encuentra en el archivo isql.exe.

Se puede usar isql para probar la instalación del servidor de la siguiente manera:

 

 

Connect "localhost:c:\archivos de programa\firebird\examples\employee.gdb" user "sysdba" password "masterkey";

para conectar con la base de datos de ejemplo. Note que puede cortar la sentencia en dos o más líneas simplemente presionando <Enter>; isql indicará que una línea continúa una sentencia anterior cambiando el indicador (prompt) por 'CON>'. Las sentencias se terminan con punto y coma (;).

 

 

 

 

 

 

 

Herramientas

 

IBServer

 

IBGuardian

 

ISQL

 

Gbak

 

IBConsole

 

IBConsole es un producto realizado utilizando acceso directo a la API de Interbase (con los componentes IB Express, de los que hablaremos luego), por lo que su rendimiento es muy bueno. También es simple de usar y nos da la posibilidad de realizar las tareas administrativas comunes como ser: creación y destrucción de bases de datos; administración de usuarios y permisos; realización de backups; verificación de integridad de la Base de Datos; ejecución de sentencias y scripts SQL, etc.

A partir de la versión 1.5 de Firebird esta utilidad no se incluye más en la distribución. Se puede encontrar en codecentral.borland.com en sus versiones para Windows y Linux; no obstante, no se recomienda IBConsole como herramienta de administración para bases de datos Firebird, en parte porque está compilado con el conjunto de componentes IBX que no garantizan compatibilidad con las nuevas versiones de Firebird.

 


Accederemos a una Base de Datos de ejemplo que se incluye en el paquete (si no instaló los ejemplos, hágalo ahora; la base de datos de empleados utiliza técnicas que vale la pena estudiar). Usaremos la base de datos llamada Employee.gdb o su versión internacional (que contiene caracteres no ingleses) llamada Intlemp.gdb. Cualquiera de las dos sirve para nuestros ejemplos porque lo único que cambia es el contenido de las tablas, no su definición.

 

Para usar la base de datos, debemos registrarla en IBConsole. Los pasos necesarios son los siguientes:

·       registrar el servidor local con la opción correspondiente del menú ‘Server’

·      

en el menú contextual del árbol de objetos de la izquierda seleccionamos ‘Register…’

 

·       en el cuadro de diálogo que aparece a continuación, completamos la información de registro de la base de datos y aceptamos. La nueva Base de Datos debería aparecer en el árbol de objetos.

 

Hasta ahora lo único que hemos hecho es crear un alias para la Base de Datos; es decir, registrar con IBConsole la ubicación física del archivo y los datos de conexión, así como un nombre más descriptivo que el nombre del archivo. Para acceder a los datos debemos conectarnos a la base de datos. Normalmente en el momento del registro IBConsole conecta automáticamente con la base de datos, como podemos observar en el árbol que se abre y el gráfico de la BD tiene una marca verde.

De aquí en adelante, podemos conectarnos haciendo simplemente doble Click sobre el nodo en el árbol o seleccionando la opción pertinente del menú contextual.

 

El solo hecho de lograr la conexión ya indica que el servidor está funcionando correctamente. Todavía se pueden presentar problemas en el acceso a través de la red, pero nos ocuparemos de eso a su debido tiempo.

 

 

 

 

Ejecutar sentencias SQL en IBConsole

 

Para ejecutar cualquier sentencia SQL utilizamos la herramienta conocida como Interactive SQL. En IBConsole está integrada al producto, y se accede desde el menú Tools o el botón . La ventana tiene el siguiente aspecto:

 


 

Las sentencias SQL se escriben en el editor superior, se ejecutan con CTRL+E o el botón , y el resultado se obtiene inmediatamente en la parte inferior (datos, plan de ejecución y estadísticas de ejecución).

Por ejemplo, la siguiente imagen muestra el resultado de pedir el contenido de la tabla employee:


 

 

 

IBOConsole

 

Esta herramienta es como un ‘clon’ de IBConsole, pero construida con los componentes IB Objects. Es gratuita y se puede descargar de <<<Ver el enlace real en IBPhoenix>>>, y en general es más estable que las versiones de IBConsole que he probado. Se maneja de la misma manera, la interfaz es prácticamente igual, y podemos esperar que sea útil con las nuevas versiones de Firebird, ya que los componentes IBO soportan todas las características de estos servidores.

 

 

 

 

IB_SQL

 

 


SQL

 

El lenguaje de consulta

 

Tal vez el problema mayor que se presenta a los programadores ‘viejos’, los que usaban sistemas de datos orientados a archivos como Clipper, Dbase o Cobol, sea el de cambiar la forma de acceder a los datos. Lo que antes requería recorrer toda una tabla buscando coincidencias o armar tablas temporales con datos de distintas tablas relacionadas, ahora se hace con una sola sentencia de un lenguaje especial diseñado para eso: el Lenguaje Estructurado de Consulta o SQL.

Una de las características que distinguen a este lenguaje es su orientación a conjuntos de datos. Así por ejemplo, en SQL no pedimos ‘recorre todos los registros y cuando encuentres uno cuyo campo Nombre comience con A, me lo muestras’; en su lugar, pedimos ‘una tabla temporal formada por todos los registros que cumplan la condicion: el campo Nombre empieza con A’. Lleva un tiempo acostumbrarse a pensar en conjuntos de datos, pero se logra. Trataremos aquí de dar la mayor cantidad posible de ejemplos y ejercicios para que esta forma de pensar se haga casi natural.

SQL son las iniciales de “Structured Query Language” o Lenguaje Estructurado de Consulta. Es un lenguaje no procedural inventado en IBM a principios de los años 70, para implementar el modelo relacional de Codd. Inicialmente se le llamó SEQUEL (Structured English Query Language), luego pasó a ser SEQUEL/2 y finalmente SQL (esperemos que no siga la tendencia y en unos años se termine llamando S). Hoy en día es el lenguaje de consulta más utilizado por los gestores de Bases de Datos de todos los tamaños, desde Dbase o Paradox pasando por Oracle, Informix o SQL Server, hasta los gestores de datos utilizados en supercomputadoras.

Hay definidos tres estándares oficiales por el Instituto Nacional de Estándares Americano (American National Standards Institute, ANSI): SQL-86, SQL-89 y SQL-92.

La mayoría de los gestores de consultas implementan “a su manera” las recomendaciones del estándar. Estudiaremos los comandos más comunes, que se aplican a los motores de consulta más utilizados, y cuando sea pertinente comentaremos diferencias en distintos servidores.

A continuación veremos en detalle el lenguaje SQL que implementa Firebird. Los ejemplos utilizan la base de datos de ejemplo que viene con el servidor, llamada EMPLOYEE.GDB, y se ejecutan en IBConsole o IBOConsole.

 

Cursores vs conjuntos

 

Los gestores de datos tipo Dbase o Paradox trabajaban con el concepto de cursor; el lenguaje SQL trabaja con conjuntos.

Los cursores se pueden ver como una cinta sobre la que se encuentran los registros, con un señalador que se mueve adelante y atrás para marcar el registro activo o actual. Las operaciones sobre más de un registro se realizan recorriendo el cursor con el señalador y actuando sobre los registros a medida que son señalados.

 

Veamos dos operaciones típicas con este modelo:

·         Selección de un subconjunto de registros, que cumplen un criterio determinado, por ejemplo mostrar los productos de un rubro

El señalador se posiciona en el primer registro; comprueba si ese registro cumple con el criterio; si lo cumple se muestra, si no se saltea; el señalador pasa al siguiente registro, y repite la comparación hasta que no hay más registros en el cursor.

·         Modificación de un subconjunto de registros, por ejemplo modificar el precio de todos los productos de determinado rubro

La operatoria es prácticamente la misma, sólo que en lugar de mostrar los registros que cumplen el criterio se editan, se cambia el precio, y se vuelven a grabar. Hay que tener en cuenta que si el cursor está ordenado por algún índice, el cambio puede hacer que los registros se reubiquen… y el señalador seguirá al registro que modificó, por lo que puede ir a parar al final del cursor, o recomenzar al principio! En el ejemplo de los precios, seguramente el orden será por rubro por lo que la modificación de precios no afectará la posición de los registros.

 

Un servidor SQL como Firebird, en cambio, trabajará sobre conjuntos de registros en una sola operación. Así, las mismas operaciones del ejemplo anterior se harían mucho más simples:

·         Selección de un subconjunto de registros que cumplen determinado criterio

Simplemente se emite una sentencia SELECT y el servidor generará un cursor compuesto solamente con los registros que cumplen el criterio. Para mostrar estos registros se hace el mismo bucle anterior, pero solamente se recorren los registros que cumplen el criterio.

 

·         Modificación de un subconjunto de registros que cumplen un criterio

Se emite una sentencia UPDATE que incluye la operación a realizar y el criterio de selección, y el servidor modificará el subconjunto que cumpla el criterio sin más intervención nuestra.

 

Cuadro de texto: Nota: en todos los listados de sintaxis que figuran en esta sección, se utilizan algunos símbolos con significados especiales (no se deben incluir en la sentencia):

• Corchetes “[ ]”: sección opcional. No es indispensable para la correcta declaración de la sentencia
• Barra vertical “|”: separa opciones mutuamente excluyentes. Se debe utilizar una sola de ellas.
• Llaves “{ }”: agrupa opciones excluyentes.

 

 

Selección de registros: SELECT

 

La sentencia estrella de SQL es, sin ninguna duda, SELECT. Esta instrucción permite obtener datos de una o varias tablas, con un poder y flexibilidad increíbles. Si SQL no tuviera más que esta instrucción, igualmente valdría la pena aprenderlo.

La sintaxis básica de SELECT es como sigue[6]:

 

SELECT columnas | *

FROM tablas

[WHERE condición]

[ORDER BY columnas]

 

Hay muchas variaciones y agregados sobre este esqueleto básico, pero todo a su tiempo.

En palabras, esta instrucción devolverá una tabla virtual compuesta por las columnas especificadas (el asterisco * se reemplaza por todas las columnas de todas las tablas), con las filas de las tablas dadas que cumplan la condición (opcional), y todo ordenado por las columnas indicadas (opcional).

Por ejemplo, para obtener los nombres de los empleados podemos escribir:

 

SELECT FIRST_NAME

FROM EMPLOYEE

 

Y veremos en la ventana de ISQL:


La sentencia SQL desaparece del editor, que queda listo para escribir otra; pero puede recuperarse con el botón  o la opción de menú Query|Previous.

 

Algunas preguntas comunes a esta altura:

·¿Importa si las instrucciones se escriben en mayúsculas o minúsculas?

No. Para el servidor es lo mismo ‘SELECT’ que ‘select’. En la parte que hay que tener cuidado es en los nombres de campos y tablas, ya que hay dialectos SQL que distinguen entre mayúsculas y minúsculas. En Firebird, con el dialecto 1 de SQL no hay diferencias; con dialecto 3, las hay únicamente cuando se usan los nombres entre comillas dobles (“”).

·¿En qué orden se recuperan los registros si no especificamos una cláusula ORDER BY?

En ‘orden natural’, lo que significa que no podemos asegurar nada. El motor de consultas obtiene los datos en el orden que sea más eficiente.

·¿Es posible indicar algún formato en el resultado, por ejemplo el ancho de las columnas?

No. El lenguaje SQL es un lenguaje de consulta, no considera la parte de presentación de los resultados.

·¿Se pueden usar espacios en los nombres de campos y tablas?

Depende del servidor. En Interbase, únicamente podemos usar espacios con Dialecto 3 y si encerramos el nombre entre comillas dobles. Los servidores actuales tienden a ser consecuentes con esta norma: si está entre comillas, puede contener cualquier caracter. No obstante, recomendamos no utilizar caracteres especiales ni espacios debido a los potenciales problemas con los diferentes conjuntos de caracteres. En general se reemplazan los espacios por el caracter de subrayado (underscore, ‘_’) como en el campo FIRST_NAME usado en el ejemplo.

 

Vamos a extender la tabla de resultados para que muestre otras columnas: el apellido (last_name) y el país de trabajo (job_country). La sentencia queda como sigue

 

SELECT FIRST_NAME, LAST_NAME, JOB_COUNTRY

FROM EMPLOYEE

 

¿Cómo haría para que aparezca primero el apellido y después el nombre?

Cuando queremos todas las columnas de la tabla en el resultado, en el mismo orden que están definidas, podemos usar el comodín ‘*’ (asterisco):

 

SELECT *

FROM EMPLOYEE

 

Notemos en el resultado de esta sentencia que hay una columna –la última- que combina el contenido de los campos First_Name y Last_Name para mostrar el nombre completo. En el caso de esta tabla, este campo está definido así, como un campo calculado por el servidor. Nosotros podemos lograr el mismo efecto usando operadores para construir campos nuevos, no existentes en la tabla original.

Por ejemplo, vamos a mostrar una columna con el Nro. de empleado (emp_no), un guión, el nombre y el apellido:

 

SELECT emp_no || ' – ' || first_name || ' ' || last_name

FROM employee

 


Hemos utilizado aquí el operador de concatenación ‘||’ (dos barras verticales), que permite juntar cadenas de caracteres. El resultado es otra cadena. Notemos que podemos usar tanto nombres de campos como cadenas constantes, con comillas simples.

 

Un problema común cuando se utilizan columnas formadas por expresiones es el nombre de estas columnas. Por ejemplo, la columna generada en el ejemplo anterior se llama ‘F_1’. ¿Quién puso este nombre? El servidor. ¿Es posible cambiarlo? Si, asignando un alias al campo por medio de la partícula ‘as’:

 

SELECT emp_no || ' - ' || first_name || ' ' || last_name AS Empleado

FROM employee

 

Si ejecutan esta sentencia, verán el mismo resultado que antes pero ahora la columna se llama ‘Empleado’, lo cual es mucho más fácil de recordar.

 

Antes de continuar sumergiéndonos en las complejidades de SELECT, realice los siguientes ejercicios para afirmar lo anterior.

 

Ejercicios

1)      Obtenga los siguientes datos (y en este orden) de los empleados: Nombre, Apellido, Nro. de empleado, salario y país de trabajo.

2)      Ahora muestre tres columnas llamadas ‘Apellido’, ‘Nombre’, ‘Salario’. En la primera muestre el apellido, en la segunda el nombre, y en la tercera la información siguiente (los ángulos indican que es el nombre de un campo):

<Job_code>, nivel <job_grade> (U$S <salary>)

3)      De la tabla de proyectos: una columna llamada ‘Proyecto’ con el siguiente formato

(<proj_id>) <proj_name>

otra columna con el tipo de producto (product), y una tercera con el Nro. de empleado del líder del proyecto (team_leader)

 

Orden en la sala

El orden en que el servidor devuelve los datos depende de varios factores; en general, no se puede asumir un orden determinado por lo que hay que indicarlo explícitamente usando la cláusula ORDER BY.

La cláusula ORDER BY es muy simple de usar: es seguida por una lista de campos (que deben figurar en la lista de columnas a mostrar) por los que se ordenará.

Por ejemplo, para obtener datos de los empleados ordenados por país de trabajo, podemos hacer

 

SELECT *

FROM employee

ORDER BY job_country

 

Como podrán ver, hay muchos empleados que trabajan en cada país, que no guardan entre sí un orden predecible. Así, si tenemos que buscar un empleado por nombre en el listado anterior –suponiendo que sabemos en qué país trabaja, tendríamos que recorrer todos los empleados de ese país hasta encontrar el que buscamos.

Para simplificar la búsqueda, podemos indicar al servidor que queremos los resultados ordenados, si, por país; pero más todavía, el conjunto de empleados de cada país lo queremos ordenado por apellido y nombre. Pruebe la siguiente sentencia:

 

SELECT *

FROM employee

ORDER BY job_country, last_name, first_name

 

Ya se comienza a notar la potencia del lenguaje. Esta instrucción nos devuelve una tabla con todos los empleados organizados por país, dentro de cada país por apellido, y si hay apellidos repetidos, se ordenan por nombre.

Una variación posible es el sentido de la ordenación: si es ascendente o descendente. Por defecto Firebird devuelve los datos ordenados en forma ascendente; para indicar lo contrario agregamos la partícula ‘desc’ después del nombre del campo de ordenación. Por ejemplo,

 

SELECT *

FROM employee

ORDER BY job_country desc

 

Nos devolverá los empleados ordenados en forma descendente por país de trabajo.

Este modificador es válido sólo para la columna a la cual sigue; cada una de las columnas de ordenamiento debe tener su indicador de dirección o se tomará el ascendente por defecto. También se puede explicitar el orden ascendente usando ‘asc’.

 

SELECT *

FROM employee

ORDER BY job_country desc, last_name asc, first_name desc

 

Devolverá un conjunto ordenado por país en forma descendente, dentro de cada país los empleados se ordenarán por apellido en forma ascendente, y si hay apellidos repetidos, se ordenarán por nombre en forma descendente.

 

NOTA: como veremos en los ejemplos siguientes, la cláusula ORDER BY siempre se coloca al final de la instrucción, después de todas las demás cláusulas optativas.

Filtrados y búsquedas

Todos los ejemplos que hemos hecho hasta ahora devuelven el total de los registros de la tabla en cuestión. Como se habrán imaginado, el lenguaje SQL tiene una cláusula que sirve para reducir el conjunto de registros resultado a un subconjunto que cumpla con ciertas condiciones. Para hacer uso de esta opción solamente tenemos que agregar a la sentencia SELECT…FROM la cláusula WHERE <condición>.

La condición debe ser una expresión que devuelva un valor booleano: aquellos registros para los cuales la expresión se evalúe a Verdadero aparecerán en el conjunto resultado, el resto no.

Por ejemplo, si queremos un listado de los empleados que trabajan en Canada solamente, haríamos

 

SELECT *

FROM employee

WHERE job_country = 'Canada'

 

Podemos ordenar el resultado agregando al final la cláusula order by:

 

SELECT *

FROM employee

WHERE job_country = 'Canada'

ORDER BY last_name, first_name

 

Esta instrucción nos permitirá encontrar rápidamente un empleado de Canadá. Pero podemos también usar los operadores lógicos para afinar la búsqueda; por ejemplo, si queremos ver los empleados de Canada de categoría 4, haríamos

 

SELECT *

FROM employee

WHERE (job_country = 'Canada') AND (job_grade = 4)

 

Notemos que el ‘4’ no está rodeado de comillas; es un número, no una cadena. Los paréntesis no son necesarios en este ejemplo, pero pueden servir para aclarar el orden de evaluación.

El orden de evaluación puede determinar el resultado de una expresión: por ejemplo, no es lo mismo evaluar 1+2*3 de izquierda a derecha (1+2=3, 3*3=9) que asociando por precedencia como hacemos en matematicas: 1+(2*3) = 7. La siguiente tabla indica el orden de precedencia de los operadores en Firebird. Algún servidor puede diferir con este orden, pero en general todos lo consideran de la misma manera. La precedencia va de mayor a menor, leídos de arriba hacia abajo (por ejemplo, se evalúa primero una concatenación que una multiplicación; pero esta última se evalúa antes que una suma).

 

Tipo

Operador

Comentario

Cadenas

||

Concatenación

Matemáticos

*

/

+

-

Multiplicación

División

Suma

Resta

Comparación

=, ==

<>,[ERC9]  !=, ~=, ^=

>

<

>=

<=

!>, ~>, ^>

!<, ~<, ^<

Igualdad

Desigualdad

Mayor

Menor

Mayor o igual

Menor o igual

No mayor

No menor

Lógicos

NOT

AND

OR

Negación

Conjunción (‘Y’)

Disjunción (‘O’)

 

Es muy importante conocer el orden de precedencia de los operadores, para no cometer errores que pueden ser muy difíciles de detectar. Por ejemplo, si el resultado de una consulta tiene 1000 registros usando el criterio ‘WHERE n > 1+2*3’ y 1010 registros usando el criterio ‘WHERE n > (1+2)*3’, ¿cuál es el correcto? No sería fácil decidirlo. Y si deciden mal, y le entregan al jefe la lista de los empleados que cumplen la condición errónea, y éstos son despedidos… bueno, no digan que no les avisé.

 

Ejercicios

1)      Realizar una consulta que devuelva los nombres y apellidos de los empleados que cobran más de U$S 100.000 (afortunados ellos).

2)      Realizar una consulta que devuelva una tabla como la siguiente, con los datos ordenados por País, categoría, apellido y nombre:

Empleado

País

Categoría

Salario

<first_name><espacio><last_name>

<job_country>

<job_grade>

<salary>

 

3)      Agregar a la tabla anterior una columna con el código de trabajo (job_code) entre las de Categoría y Salario. Llamar a la columna ‘Codigo_trabajo’.

4)      Listar aquellos empleados que hayan sido contratados en 1990 (hire_date)

 

El último ejercicio pone el dedo en una llaga: el manejo de fechas.

 

Fechas/Horas en SQL

Nuevamente, cada servidor tiene sus convenciones con respecto al formato de las fechas. En Firebird tenemos varios tipos de campos para almacenar fechas y horas:

 

Dialecto 1

DATE: almacena fecha y hora, como dos números enteros de 32 bits. Equivalente al tipo timestamp del dialecto 3

 

Dialecto 3

DATE: almacena una fecha como un número entero que representa la cantidad de días pasados desde el 1 de Enero de 100. Puede almacenar hasta el día 29 de febrero de 32768. Un valor 0 para la fecha indicará el día 17 de Noviembre de 1858.

TIME: almacena una hora como un número entero. El rango permitido va desde las 00:00 AM hasta las 23:59.9999 PM, esto es, 23:59:59.5964 PM.

TIMESTAMP: almacena fecha y hora, como dos números enteros de 32 bits; la parte de fecha es igual al tipo DATE, la parte de hora igual a un campo de tipo TIME

 

Entonces, ¿cómo podemos usar fechas en criterios de selección? Ciertamente, no calculando la cantidad de días pasados desde el ‘día cero’, 17 de Noviembre de 1858. Firebird puede convertir un tipo de dato en otro, y particularmente con las fechas hace la conversión automáticamente si el formato del texto con la fecha coincide con alguno de los reconocidos:

CCYY-MM-DD
MM-DD-CCYY ó MM-DD-YY
MMM-DD-CCYY
ó MMM-DD-YY
DD-MMM-CCYY
ó DD-MMM-YY
CCYY-MM-DD HH:Min:SS:nnnn
MM-DD-CCYY HH:Min:SS:nnnn
ó MM-DD-YY HH:Min:SS:nnnn
MMM-DD-CCYY HH:Min:SS:nnnn
ó MMM-DD-YY HH:Min:SS:nnnn
DD-MMM-CCYY HH:Min:SS:nnnn
ó DD-MMM-YY HH:Min:SS:nnnn

donde


CC = siglo
YY = año
MM = mes
DD = día
HH = hora
Min = minutos
SS = segundos
nnnn = milisegundos

El separador de las fechas puede ser '-', '/' o '.'. Para las fechas se puede usar el formato americano (MM/DD) o el europeo (DD.MM), reconociéndose por el separador: el punto para el formato europeo, cualquier otro para el americano.

Por ejemplo, para ver todos los empleados contratados en 1990 podemos escribir:

 

SELECT *

FROM employee

WHERE (hire_date >= '1990-01-01')

  AND (hire_date < '1991-01-01')

 

Se definen en Firebird algunas literales que son reemplazadas por los valores que representan en el momento de la evaluación:

NOW: fecha y hora actuales

TODAY: fecha actual

YESTERDAY: fecha de ayer

TOMORROW: fecha de mañana

Estas literales se utilizan encerradas entre comillas simples, como en el siguiente ejemplo:

 

SELECT *

FROM employee

WHERE hire_date < 'TODAY'

 

Que nos devolverá todos los empleados contratados antes del día de hoy.

Asimismo, cuando trabajamos en Dialecto 3 podemos usar las funciones current_date, current_time y current_datetime (sin comillas) para referirnos a la fecha y hora actuales en los tres formatos: DATE, TIME y TIMESTAMP respectivamente.

Podemos tener una idea del tratamiento que da Firebird a las fechas creando una tabla con un campo de cada tipo, y probando los distintos valores. Las instrucciones de creación e inserción no han sido explicadas aún, pero considero importante el ejemplo en este momento.

CREATE TABLE prueba (

clave integer not null primary key,

hora time,

fecha date,

todo timestamp)

 
Vamos a crear una tabla llamada Prueba, con los siguientes campos:

Campo

Tipo de dato

Clave

Integer

Hora

Time

Fecha

Date

Todo

Timestamp

 

La sentencia SQL necesaria aparece en el cuadro de la derecha. No nos preocupemos por ahora de esta sentencia; lo único que tenemos que saber es que creará la tabla deseada. Ahora emitimos los siguientes comandos, uno por vez, para llenar la tabla con datos:

 

INSERT INTO prueba VALUES (1,'09:00','TODAY','NOW')

INSERT INTO prueba VALUES (2,'10:00','YESTERDAY','yesterday')

INSERT INTO prueba VALUES (3,'11:00','tomorrow','time')

INSERT INTO prueba VALUES (4,'12:00',current_date,'now')

INSERT INTO prueba VALUES (5,'13:00','2002-05-24','now')

INSERT INTO prueba VALUES (5,'13:00','2002-05-24','2002-05-24 12:35')

 

Si ahora pedimos los datos introducidos, con un SELECT común

 

SELECT *

FROM prueba

 

veremos cómo interpretó Firebird cada uno de los literales y funciones usados, en cada tipo de campo. También podemos probar otras combinaciones para ganar un entendimiento mayor de las expresiones que son válidas y las que no.

 

Ejercicios

En la base de datos donde creó la tabla Pruebas anterior:

1)      devuelva los registros donde <fecha> es menor que hoy

2)      devuelva los registros donde <todo> es menor que el momento actual

3)      devuelva los registros donde <fecha> sea igual a la fecha de ayer

4)      devuelva los registros donde <hora> es menor que las 12 del mediodía, ordenados por fecha

 

 

Criterios de selección

Hay una serie de funciones que se pueden usar en las expresiones de criterios de selección. Como de costumbre, estas funciones dependen del servidor de bases de datos, y veremos aquí en detalle las de Firebird.

·           <campo> LIKE <cadena>: comparación de cadenas con comodines. Es similar al operador de igualdad (=) pero solamente puede usarse con cadenas y permite el uso del comodín ‘%’ para indicar ‘cualquier carácter (cero o más)’ y ‘_’ para reemplazar un solo carácter que sin embargo debe existir. Se distingue entre mayúsculas y minúsculas.

Por ejemplo, la siguiente sentencia devolverá todos los empleados cuyo nombre empieza con ‘A’ (mayúscula):

 

SELECT *

FROM employee

WHERE first_name LIKE 'A%'

 

Mientras que la siguiente sentencia devolverá todos los empleados que contengan una ‘a’ minúscula en cualquier posición de su apellido:

 

SELECT *

FROM employee

WHERE last_name LIKE '%a%'

 

Pruebe a cambiar la ‘a’ de la condición por una ‘A’.

 

·           <campo> STARTING WITH <cadena>: devuelve TRUE si la cadena comparada comienza con el valor pasado. Es equivalente a LIKE <cadena%>; pero el optimizador de consultas puede usar un índice (si existe) con STARTING WITH, mientras que con LIKE no.

 

Como ejemplo, veamos el mismo ejemplo que antes: los empleados cuyo nombre comienza con ‘A’:

 

SELECT *

FROM employee

WHERE first_name STARTING WITH 'A'

 

 

·           <campo> CONTAINING <cadena>: devuelve TRUE si la cadena comparada contiene el valor dado. Es equivalente a LIKE <%cadena%>, pero tiene la ventaja de poder usarse con campos BLOB.

Como ejemplo, veamos el mismo ejemplo que antes: los empleados cuyo apellido contiene una ‘a’:

 

SELECT *

FROM employee

WHERE last_name CONTAINING 'a'

 

También existe la versión negada, es decir que devuelve TRUE cuando el valor NO contiene la cadena:

 

SELECT *

FROM employee

WHERE last_name NOT CONTAINING 'a'

 

 

·           <campo> BETWEEN <valor inferior> AND <valor superior>: devuelve TRUE cuando el valor comparado (que puede ser de cualquier tipo salvo BLOB) se encuentra entre <valor inferior> y <valor superior>, ambos inclusive. Estos límites deben ser del mismo tipo que el valor a comparar.

Por ejemplo, la siguiente sentencia devolverá los datos de los empleados que cobran entre U$S 50.000 y U$S 72.000:

 

SELECT *

FROM employee

WHERE salary BETWEEN 50000 AND 72000

 

La siguiente instrucción traerá los empleados contratados en 1990 (equivalente a la que vimos en un ejemplo anterior):

 

SELECT *

FROM employee

WHERE hire_date BETWEEN '1990-01-01' AND '1990-12-31'

 

Y la siguiente recuperará todos los empleados cuyo nombre empiece con ‘A’, ‘B’ o ‘C’:

 

SELECT *

FROM employee

WHERE first_name BETWEEN 'A' AND 'Cz'

 

Notemos el uso de ‘Cz’ para el límite superior; cualquier cadena que empiece con ‘C’ y siga con un carácter común será menor o igual que esta cadena, por lo que entran en la selección todos los apellidos comenzados en C.

La versión negada devuelve TRUE cuando el valor no está en el rango dado:

 

SELECT *

FROM employee

WHERE hire_date NOT BETWEEN '1990-01-01' AND '1990-12-31'

 

Devolverá los empleados que no fueron contratados en 1990.

 

·           <campo> IN <conjunto>: devuelve TRUE si el valor existe en el conjunto dado. El conjunto puede ser especificado por extensión, como una lista de valores separados por comas y encerrados entre paréntesis, o como una subconsulta que devuelva un solo campo del tipo correcto.

 

Por ejemplo, para ver aquellos empleados de USA, Canada y Alemania (Germany) podemos hacer:

 

SELECT *

FROM employee

WHERE job_country IN ('USA', 'Canada', 'Germany')

 

Mientras que para ver los empleados que son líderes de algún proyecto podríamos hacer:

 

SELECT *

FROM employee

WHERE emp_no IN (

  SELECT team_leader

  FROM project)

 

La consulta interior, encerrada entre paréntesis, se denomina subconsulta y es una poderosa posibilidad de SQL en la que ahondaremos en breve.

También existe la versión negada, como se habrán imaginado:

 

SELECT *

FROM employee

WHERE emp_no NOT IN (

  SELECT team_leader

  FROM project)

 

Devuelve los empleados que no son líderes de proyectos.

 

·           <campo> IS NULL: devuelve TRUE si el valor es nulo; también puede usarse la forma <valor> IS NOT NULL, que devolverá TRUE en el caso contrario. Estas instrucciones fueron creadas especialmente para lidiar con los valores nulos, y generalmente no equivalen a comparar simplemente un campo con el valor NULL… aunque también depende del servidor. Veamos un par de ejemplos:

 

SELECT *

FROM employee

WHERE phone_ext IS NULL

 

Devuelve los empleados cuya extensión de teléfono es desconocida. Pruebe a ejecutar la siguiente sentencia, que parece ser equivalente:

 

SELECT *

FROM employee

WHERE phone_ext = NULL

 

Este comportamiento es parte de las extravagancias de los nulos, de las que hablaremos muchas veces.

Si queremos ver los que sí tienen almacenada una extensión, haremos

 

SELECT *

FROM employee

WHERE phone_ext IS NOT NULL

 

 

(1.5) CASE

A partir de la versión 1.5 de Firebird, se puede generar una consulta condicional cuyas columnas tomarán valor en base al resultado de la evaluación de una expresión. La sintaxis es la siguiente:

Se pueden clasificar las sentencias CASE en dos formas:

1)      simple: se define la expresión después de la palabra CASE y los distintos valores se asignan en base a los distintos resultados de la evaluación de esta expresión

 

    CASE < expresion valor > 

<clausula WHEN simple>...

      [ <clausula else> ]

    END

 

 

<<<Ejemplo>>>

 

 

2)      con búsqueda: para cada valor se especifica una expresión

 

    CASE <clausula WHEN buscada>...

      [ <clausula else> ]

    END

 

 

<<<Ejemplo>>>

 

 

 

Funciones

Existen en todos los servidores algunas funciones que toman un parámetro entre paréntesis -usualmente el contenido de un campo- y devuelven el valor modificado. Se pueden usar tanto en la especificación de las columnas a devolver como en los criterios.

Firebird es bastante parco en cuanto a las funciones predefinidas; tenemos solamente un puñado:

 

UPPER(<cadena>): devuelve la misma cadena con todos los caracteres en mayúsculas.

CAST(<valor> as <tipo>): convierte el valor al tipo dado… si puede.

GEN_ID(<nombre generador>,<incremento>): incrementa el valor del generador en la cantidad pedida y devuelve el resultado. El generador queda incrementado.

SUBSTRING(cadena FROM inicio [FOR n]): devuelve n caracteres de cadena a partir del caracter nro. inicio. Si no se especifica la cláusula FOR, se devuelven todos los caracteres desde inicio hasta el final.

<<<OJO: creo que hay un problema cuando se usan juegos de caracteres de más de un byte… ver los mensajes de los foros de Firebird y probar con fss_unicode y iso8859_1>>>

 

En dialecto 3 disponemos de la función EXTRACT para obtener una parte de una fecha:

EXTRACT({year | month | day} FROM <campo fecha>)

Por ejemplo, para obtener los empleados que fueron contratados en el mes de enero (de cualquier año) haríamos

 

SELECT *

FROM employee

WHERE extract(month from hire_date) = 1

 

EMP_NO     FIRST_NAME      LAST_NAME            PHONE_EXT HIRE_DATE 

-------------------------------------------------------------------- …

11         K. J.           Weston               34        17/01/1990 …             

20         Chris           Papadopoulos         887       01/01/1990 …             

141        Pierre          Osborne                        03/01/1994 …             

 

 

(1.5) NULLIF(expr1,expr2) devuelve NULL si expr1 es igual a expr2, caso contrario devuelve expr1.

<<<Ejemplos>>>

 

(1.5) COALESCE(expr1, expr2, expr3, … exprN) devuelve el valor de la primera expresión no nula.

<<<Ejemplos>>>

 

 

Funciones de agregación

Las siguientes funciones operan sobre un conjunto de registros (que puede ser la tabla entera), devolviendo un solo valor por cada grupo:

AVG(<campo>): devuelve el valor medio del campo en el grupo de registros considerado.

COUNT(<campo> | *): devuelve la cantidad de registros del grupo. Si usamos la primera versión, con el nombre de un campo entre los paréntesis, devolverá la cantidad de filas en que este campo sea no nulo; la segunda versión, con un asterisco, devuelve la cantidad total de registros del grupo. Si no se encuentra ninguna fila que cumpla con las condiciones, devuelve 0.

COUNT(distinct <campo>): igual que el anterior, pero cuenta sólo los registros con valores diferentes en el campo pedido.

MAX(<campo>): devuelve el valor máximo del campo en el grupo.

MIN(<campo>): devuelve el valor mínimo del campo en el grupo.

SUM(<campo>): devuelve la suma de los valores del campo en el grupo. No considera los valores nulos. Si no encuentra nada para sumar (no hay registros o todos son nulos) no devuelve 0, como uno esperaría: simplemente devuelve un nulo.

Por ejemplo, la consulta siguiente devolverá el monto total de remuneraciones anuales, sumando el campo SALARY de todos los registros de la tabla EMPLOYEE:

select sum(salary)

from employee

 

SUM       

-----------

115522468

 

Pero SQL puede dar mucha más información que esa; por ejemplo, sería bueno conocer el total pagado en sueldos por departamento, es decir que la suma actuara sobre subconjuntos de registros.

Para especificar los límites de cada subconjunto de registros usamos la cláusula GROUP BY seguida de una expresión sobre campos de la tabla. Los registros se agruparán según los valores de esos campos, de manera que todos los integrantes de un grupo tengan el mismo valor en esos campos. Por ejemplo, la consulta indicada antes de totales pagados en salarios por departamento puede escribirse como sigue:

select sum(salary) as TotalPorDepto

from employee

group by dept_no

 

TOTALPORDEPTO

--------------

266643       

155262.5     

130442.81    

13480000     

95779.69     

110000  

 

 

Aún mejor, podemos mostrar el nro. de departamento junto a cada total:

select dept_no as Depto, sum(salary) as TotalPorDepto

from employee

group by dept_no

 

DEPTO TOTALPORDEPTO

--------------------

000   266643       

100   155262.5     

110   130442.81    

115   13480000     

120   95779.69     

121   110000   

 

Trate de visualizar mentalmente el resultado de la sentencia anterior sin la cláusula ORDER BY. Si Ud. puede, lo felicito; yo no sabría cómo armar las filas, mezclando nros. de departamento (que se repiten) y totales por cada departamento diferente. El intérprete SQL tampoco lo sabe, y se queja amargamente:

select dept_no as Depto, sum(salary) as TotalPorDepto

from employee

 

Dynamic SQL Error

SQL error code = -104

invalid column reference

 

como hicimos antes, debemos especificar que los registros se agruparán por nro. de departamento; entonces si, cada fila de la salida corresponde a un grupo y todos los registros del grupo tienen el mismo valor en dept_no, por lo que no hay ambigüedades.

La regla sintáctica es, entonces:

??????

Cuadro de texto: Cuando se usan funciones de agregación, los campos listados en una sentencia SELECT que no sean parámetros de funciones de agregación deberán figurar en una cláusula GROUP BY, que deberá existir en esa sentencia.

Suponga ahora que queremos ver solamente aquellos departamentos en los cuales se pagan más de 250.000 dolares al año en sueldos. En este caso la restricción no se puede aplicar con una cláusula WHERE, ya que la condición a cumplir utiliza el total por departamento calculado sobre los subconjuntos. Para aplicar una condición de filtrado después del agrupamiento de los registros, existe la partícula HAVING:

 

select dept_no as depto, sum(salary) as TotalPorDepto

from employee

group by dept_no

having sum(salary)>200000

 

DEPTO TOTALPORDEPTO

--------------------

000   266643       

115   13480000     

123   390500       

125   99000000      

621   276739.5     

623   287758.25    

671   219465.19    

 

Esos son todos los departamentos que cumplen con nuestra condición.

Ahora cambiamos la pregunta: queremos saber cuáles son los sueldos que se pagan por departamento, pero solamente en USA. Esta condición sí se debe aplicar registro por registro, ya que puede haber un departamento con empleados en distintos países. La sentencia resultante es:

select dept_no as depto, sum(salary) as TotalPorDepto

from employee

where job_country='USA'

group by dept_no

 

DEPTO TOTALPORDEPTO

--------------------

000   266643       

100   155262.5     

110   130442.81    

130   189042.94    

180   107377.5     

600   132900      

 

Y finalmente, juntamos las dos condiciones: queremos ver los departamentos en los que se pagan más de U$S 200000 en total, contando solamente los empleados que trabajan en USA:

select dept_no as depto, sum(salary) as TotalPorDepto

from employee

where job_country='USA'

group by dept_no

having sum(salary)>200000

 

DEPTO TOTALPORDEPTO

--------------------

000   266643       

621   276739.5     

623   287758.25    

671   219465.19 

 

Solamente quedan esos departamentos después de todos los filtros. Observe el orden de las distintas palabras clave en la sentencia: este orden debe respetarse.

Como ejemplo final, veamos esta misma lista pero ordenada de mayor a menor por monto:

select dept_no as depto, sum(salary) as TotalPorDepto

from employee

where job_country='USA'

group by dept_no

having sum(salary)>200000

order by 2 desc

 

Note que usamos el índice del campo de agregado. Hasta la versión 1.02 de Firebird ésta era la única manera de hacerlo, ya que no se podían usar expresiones en la cláusula ORDER BY. En Firebird 1.5 esto se ha corregido, por lo que la sentencia anterior puede escribirse (igual que en la mayoría de los gestores de Bases de Datos):

select dept_no as depto, sum(salary) as TotalPorDepto

from employee

where job_country='USA'

group by dept_no

having sum(salary)>200000

order by sum(salary) desc

 

Notemos la potencia del lenguaje; imagínese lo que tendría que hacer para obtener este resultado usando un cursor, recorriendo los registros uno por uno…

 

 

Subconsultas

 

Hay casos en los que no nos basta con una consulta de selección. Pensemos por ejemplo cómo podríamos obtener una tabla con registros en los que figure cierto valor, que se debe obtener de otra consulta. Un caso típico se da en las relaciones 'muchos-a-muchos', en las que tenemos dos tablas relacionadas entre sí a través de una tercera.

Por ejemplo, tomemos el caso de las tablas Employee y Project de la base de datos Employee.gdb que hemos trabajado antes. Estas tablas no se referencian entre sí directamente: no hay un campo en la tabla de empleados que referencie a un proyecto en particular –si este fuera el caso, cada empleado podría estar solamente en un proyecto a la vez. Lo mismo pasa con los proyectos: si hubiera un campo para almacenar un número de empleado, solamente ese empleado podría estar en ese proyecto… La situación real es más compleja. Los empleados pueden estar en más de un proyecto, y los proyectos pueden tener más de un empleado. Para esto se necesita una tercera tabla que contenga las relaciones. En Employee.gdb esta tabla se llama Employee_Project.

La tabla Employee_Project tiene la siguiente estructura:

 

CREATE TABLE EMPLOYEE_PROJECT (

  EMP_NO EMPNO NOT NULL,

  PROJ_ID PROJNO NOT NULL,

  PRIMARY KEY (EMP_NO,PROJ_ID)

);

 

Como vemos, solamente tiene dos campos: uno referencia a un empleado, el otro a un proyecto. Cada registro de esta tabla relaciona a un empleado con un proyecto, y la clave primaria es la conjunción de los dos campos para permitir repeticiones en cualquiera de ellos por separado pero no en los dos juntos (un mismo empleado no se puede poner en un mismo proyecto dos veces).

Ahora pensemos en la siguiente pregunta: ¿cuáles son los nombres de los proyectos en los cuales participa Bruce Young? Intente obtener una respuesta con SQL antes de seguir leyendo.

Podemos ejecutar la siguiente secuencia de acciones:

1)      buscar el nro. de empleado de Bruce Young, ya que éste es el campo que existe en la tabla intermedia Employee_Project. Es muy simple:

select emp_no

from employee

where first_name='Bruce'

  and last_name='Young'

 

EMP_NO    

-----------

4         

 

2)      buscar los proyectos en que participa este empleado:

select *

from employee_project

where emp_no=4

 

EMP_NO     PROJ_ID

-------------------

4          VBASE  

4          MAPDB  

 

3)      obtener los nombres de los proyectos 'VBASE' y 'MAPDB'

select Proj_id,Proj_Name

from project

where proj_id in ('VBASE','MAPDB')

 

PROJ_ID PROJ_NAME           

-----------------------------

VBASE   Video Database      

MAPDB   MapBrowser port  

 

Esto fue fácil, dirá usted. Sí. Pero no resulta difícil encontrarse relaciones de este tipo en las que un registro de una tabla se relaciona con cientos o miles de registros de la otra… en ese caso, sería muy engorroso y tal vez imposible de realizar en un tiempo prudencial la escritura de todos los 'ID' de la segunda tabla en el conjunto enviado al operador IN.

Esta es una de las aplicaciones de las subconsultas, como ya vimos en la sección de los criterios de selección: el argumento para el operador IN puede ser el resultado de una consulta. En este caso, quedaría

 

select Proj_id,Proj_Name

from project

where proj_id in (select Proj_Id from employee_project where emp_no=4)

 

PROJ_ID PROJ_NAME           

-----------------------------

VBASE   Video Database      

MAPDB   MapBrowser port 

 

El resultado es el mismo, como era de esperar. Lo que hemos hecho es juntar varios pasos en uno solo.

Y la pregunta de examen: ¿se puede obtener el mismo resultado con una sola sentencia? El resultado es afirmativo. Se pueden ejecutar todas las operaciones de selección en una sola como la siguiente:

select Proj_id,Proj_Name

from project

where proj_id in

  (select Proj_Id from employee_project where emp_no=

    (select emp_no from employee where first_name='Bruce' and last_name='Young')

  )

 

Observe especialmente que cada subconsulta va encerrada entre paréntesis.

 

Este no es, claro, el único caso en que se utilizan las subconsultas. Prácticamente cualquier problema que requiera la ejecución de sentencias de selección intermedias, cuyos resultados luego se usarán para obtener la respuesta, se puede escribir usando subconsultas.

 

 

Comparación con el resultado de una subconsulta

Los siguientes operadores se pueden usar con subconsultas:

<valor> ALL <subconsulta>: devuelve TRUE si el valor es igual a todos los devueltos por la subconsulta

<value> ANY | SOME <subconsulta>: devuelve TRUE si el valor comparado es igual a alguno de los devueltos por la subconsulta.

EXISTS <subconsulta>: devuelve TRUE si la subconsulta devuelve al menos una fila.

Ejemplo: determinar si hay al menos un proyecto en el que trabaje Leslie Johnson

select 'Si' as Hay_un_proyecto

from project

where exists

  (select Proj_Id from employee_project where emp_no=8)

 

HAY_UN_PROYECTO

----------------

Si             

Si             

Si             

Si             

Si             

Si         

 

Pruebe con un nro de empleado distinto, por ejemplo 1 (no existe) o 2 (no está asignado a ningún proyecto).

La respuesta es un tanto repetitiva… el hecho es que se mostrará un 'Si' por cada proyecto existente en la tabla 'Project', dado que la subconsulta no está relacionada con la consulta exterior. Entonces, por cada registro de la tabla Project se evalúa la subconsulta y ésta siempre es la misma, siempre da el mismo resultado. Veremos dos maneras de evitar las repeticiones:

1)      Usando una tabla con un solo registro en la consulta externa. En Firebird / Interbase existe una tabla así en todas las bases de datos: es la que almacena características de la base de datos en sí, y por consiguiente sólo tiene un registro. Se llama rdb$database. La consulta anterior quedaria de la siguiente manera

select 'Si' as Hay_un_proyecto

from rdb$database

where exists

  (select Proj_Id from employee_project where emp_no=8)

 

Esta consulta devuelve un solo 'Si' si hay al menos un proyecto que contenga al empleado, y NULL en caso que no haya ninguno. Es bastante eficiente ya que la subconsulta se ejecuta una sola vez.

 

2)      Usando una subconsulta correlacionada. En este caso la subconsulta se relaciona con la consulta exterior a través de algún campo, con lo que la evaluación de la subconsulta en cada registro de la exterior puede arrojar un resultado diferente.

select 'Si' as Hay_un_proyecto

from project

where exists

  (select Proj_Id

   from employee_project

   where emp_no = 8

     and employee_project.Proj_Id = project.Proj_id)

 

HAY_UN_PROYECTO

----------------

Si              

Si             

Si    

 

El resultado puede tener varias líneas, una por cada proyecto al que esté asignado el empleado. Esta consulta es más lenta que la anterior pero da una información extra: la cantidad de proyectos a los que está asignado el empleado.

 

SINGULAR <subconsulta>: devuelve TRUE si la subconsulta devuelve exactamente una fila.

 

 

Uniones

 

Firebird permite la unión de varias consultas en una sola, usando la palabra clave UNION entre las consultas. El resultado de la operación es una sola tabla, entonces ¿qué campos tendrá esta tabla? Si en todas las consultas unidas hay campos diferentes, ¿cuáles de ellos se incluirán en el resultado? Bueno, para no complicar las cosas el estándar SQL define que todas las consultas incluidas en una unión deben tener los mismos campos. O sea:

·         la misma cantidad de campos,

·         que se llamen igual,

·         estén en el mismo orden y

·         sean del mismo tipo.

Suena un poco restrictivo, pero siguen siendo muy útiles. Veamos algunos ejemplos.

 

<<<Ejemplos de uniones>>>

 

 

 

Consulta de varias tablas relacionadas

 

Hasta ahora hemos trabajado siempre con una sola tabla. Pero si seguimos las reglas de normalización tendremos que dividir muchas veces los datos en varias tablas; por ejemplo, en la tabla de proyectos (projects) de la base de datos que estamos usando existe un campo llamado team_leader, numérico. ¿Numérico? ¿Quiere decir que los líderes de proyecto se reconocen por un número únicamente, no por el nombre y apellido como todo el mundo? Bueno, justamente: ese número es el número de empleado de un registro de la tabla employee. ¡Después de todo, hasta los líderes de proyecto son empleados!

<<<gráfico de los datos en forma de arbol, a la derecha>>>

Veamos de otra manera la normalización de tablas: como un árbol. En este árbol el tronco es nuestra tabla ‘principal’, y cada nueva rama es un registro de otra tabla con un enlace hacia el tronco. En el caso menos botánico de las bases de datos relacionales el enlace viene dado por uno o más campos que tendrán los mismos valores en las dos tablas. Para acceder a todos los datos completos de un registro de la tabla principal (el tronco), debemos mostrar también los datos de todas las ramas que cuelgan de él. Algo así como hachar el árbol y poner todas las ramas una al lado de la otra.

Este esquema no sería funcional sin un lenguaje de consulta que permita obtener datos de tablas relacionadas a la vez; esto es lo que hace SELECT con las relaciones o encuentros (Joins).

Hay varias maneras de recuperar información de tablas relacionadas. Por ejemplo, podemos pedir que la BD nos devuelva los datos de todas las compras (tabla de facturas) de nuestros clientes (cuyos datos están en otra tabla); pero ¿qué pasará con los datos de los clientes que no han comprado nada todavía y por lo tanto no tienen facturas? Bueno, esto depende del tipo de encuentro que usemos al consultar como veremos en seguida.

En general, las relaciones siempre se hacen entre dos tablas; aunque puede haber varias de estas relaciones de pareja en una sola instrucción SELECT.

Veremos primero la forma más antigua[7], listando los nombres de todas las tablas intervinientes luego de FROM, separadas por comas, y con las relaciones en la cláusula WHERE:

 

SELECT columnas | *

FROM tabla1, tabla2, tabla3…

[WHERE condición]

[ORDER BY columnas]

 

Con un ejemplo se verá más claro. En la base de datos Employee.gdb tenemos varias tablas relacionadas, por ejemplo las de empleados (datos generales de los empleados) y la de departamentos (datos de cada departamento de la empresa). En la tabla de departamentos (Apartment) hay un campo que relaciona con la tabla de empleados: Mngr_No, o número de empleado del encargado (Manager). Este número se relaciona con el campo Emp_no de la tabla de empleados, de tal manera que cada departamento que tenga un encargado tendra en el campo Mngr_no su número de empleado. Si queremos mostrar el nombre del departamento y el nombre de su encargado, tendremos que buscar en las dos tablas. La siguiente es una manera de hacerlo en SQL:

 

SELECT department.department, employee.full_name

FROM employee, department

WHERE department.mngr_no = employee.emp_no

ORDER BY department.department

 

DEPARTMENT                FULL_NAME                            

----------------------------------------------------------------

Consumer Electronics Div. Cook, Kevin                          

Corporate Headquarters    Bender, Oliver H.                    

Customer Services         Williams, Randy                      

Customer Support          Young, Katherine                     

Engineering               Nelson, Robert   

 

Esta instrucción nos devuelve la lista de todos los departamentos, y el nombre del encargado, ordenados por departamento.

 

Cuadro de texto: NOTA: hay que tener cuidado porque ahora tenemos campos de dos tablas, que pueden llamarse igual. Para evitar ambigüedades, he indicado explícitamente al servidor de qué tabla tiene que obtener cada campo, escribiendo el nombre de la tabla antes del nombre del campo, separados por un punto. Esta sintaxis es opcional en casos como éste donde los campos de las dos tablas tienen nombres distintos; pero es obligatorio si tienen el mismo nombre. Para evitar problemas, conviene acostumbrarse a escribir el nombre de la tabla para cada campo cuando hay más de una tabla.
Podemos abreviar un poco la notación asignando alias a las tablas. Simplemente dejamos un espacio después del nombre real de la tabla y escribimos el nombre ficticio:

SELECT d.department, e.full_name
FROM employee e, department d
WHERE d.mngr_no = e.emp_no
ORDER BY d.department

Recordemos: al servidor le es indiferente cómo llamemos a las tablas. Lo único que ganamos usando alias es en facilidad para escribir y entender la sentencia nosotros, torpes humanos.

Cuadro de texto: Importante: Interbase permite en general las consultas que incluyen campos que tienen el mismo nombre en dos tablas usadas en la misma sentencia; obtiene del contexto la tabla más importante y toma de ahí los valores. En Firebird en cambio se rechazan las sentencias que pueden dar lugar a ambigüedades. Conviene tenerlo en cuenta cuando programamos usando Interbase y pensamos en Firebird para la instalación final.¡Un momento! Pidan una lista de los departamentos ordenados, y comparen… por ejemplo, ¿pueden ver en el primer resultado el departamento ‘Marketing’? Este es sólo uno de los que faltan. La razón: el campo ‘Mngr_No’ de esos registros tiene un valor nulo, implicando que no hay un encargado asignado al departamento (o alguien se olvidó de cargarlo en la base de datos).

Moraleja: cuando se utiliza esta forma de relación entre tablas (listado de las tablas en el FROM, condiciones de enlace en WHERE) únicamente aparecen en el resultado los registros de las dos tablas en las cuales haya valores de enlace coincidentes. Este tipo de encuentro entre tablas se denomina Encuentro Interno (INNER JOIN).

 

Ejercicio

Realice la misma consulta del ejemplo anterior, pero esta vez sin el criterio de selección (WHERE). ¿qué obtiene?

 

La unión interna que acabamos de ver es muy peligrosa; primero porque no muestra todos los registros, y podemos llevarnos un chasco… imagínense que le llevan la lista generada por la primera consulta al jefe y éste busca el departamento de marketing. Y segundo, porque si no especifican la relación entre las tablas como un criterio de selección (como en el ejercicio) entonces obtendrán lo que se denomina una Encuentro Natural o Cartesiano. En esta unión se muestran todas las combinaciones de los registros de las tablas. Es decir que si una tabla tiene 10 registros y la otra tiene 5, el resultado contendrá… 50 registros!

El problema de la unión cartesiana se ve fácilmente cuando pedimos la unión de tres o más tablas y nos olvidamos de alguna relación. Por ejemplo, si queremos ver el nombre de los clientes, las ventas que se le han hecho a cada uno, y la moneda que se utiliza en el país de origen del cliente, necesitamos las tres tablas Customer (clientes), Country (países) y Sales (ventas):

 

SELECT c.customer as Cliente, pais.currency as Moneda, s.order_date as FechaPedido,

  s.order_status as Estado, s.paid as Pagado

FROM customer c, country pais, sales s

WHERE c.cust_no = s.cust_no

  AND c.country = pais.country

ORDER BY c.customer

 

Hay varias cosas para notar en la consulta anterior.

·No aparecen todos los clientes. Esto ya era de esperar, ¿no? ¿Cuáles son los que aparecen?

·Las relaciones entre las distintas tablas se unen entre sí con AND, para indicar que se tienen que cumplir todas las condiciones para que el registro aparezca en el resultado.

 

Así se pueden agregar más tablas, simplemente listándolas en el FROM y agregando una condición al criterio de selección con AND. ¿Qué pasará si nos olvidamos de algún criterio? Pues que el servidor no emitirá ningún error, y generará una unión cartesiana con la tabla que dejemos ‘suelta’…

 

Hay otras formas de combinación de tablas. Podemos pedir al servidor que nos muestre todos los registros de una de las tablas, y únicamente los valores de la otra tabla cuando haya una correspondencia. Se dice que se preserva una tabla, la que se muestra completa.

Algunos servidores implementan estas combinaciones con variaciones de la sintaxis anterior, agregando nuevos operadores a la sentencia where. Firebird en cambio sigue el estándar ANSI-92 en el que se propone una nueva forma de especificar las combinaciones entre tablas, indicándolas en el FROM. La sintaxis completa sería

 

FROM <tabla1> {INNER | LEFT | RIGHT | FULL} JOIN <tabla2> ON <expresión con los campos relacionados>

 

Escribamos algunos de los ejemplos anteriores con la nueva sintaxis:

 

SELECT department.department, employee.full_name

FROM employee, department

WHERE department.mngr_no = employee.emp_no

ORDER BY department.department

 

Esta instrucción nos devuelve la lista de todos los departamentos, y el nombre del encargado, ordenados por departamento. Con la nueva sintaxis, quedaría:

 

SELECT department.department, employee.full_name

FROM employee INNER JOIN department ON department.mngr_no = employee.emp_no

ORDER BY department.department

 

 

O veamos la siguiente, que trae los datos de los pedidos, clientes y países

SELECT c.customer as Cliente, pais.currency as Moneda, s.order_date as FechaPedido, s.order_status as Estado, s.paid as Pagado

FROM customer c, country pais, sales s

WHERE c.cust_no = s.cust_no

  AND c.country = pais.country

ORDER BY c.customer

 

 

Con la nueva sintaxis:

SELECT c.customer as Cliente, pais.currency as Moneda, s.order_date as FechaPedido, s.order_status as Estado, s.paid as Pagado

FROM customer c

  INNER JOIN country pais ON c.country = pais.country

  INNER JOIN sales s ON c.cust_no = s.cust_no

ORDER BY c.customer

 

La nueva sintaxis separa físicamente los conceptos lógicos de relación o encuentro y criterios de selección.

 

Volvamos sobre el primer ejemplo, donde traíamos cada departamento con el nombre de su encargado. Notamos antes que no se listan todos los departamentos cuando pedimos una unión interna, ya que hay departamentos que no tienen encargado. Usando las extensiones a la sintaxis podemos pedir que se nos muestren todos los departamentos, completando el registro con el nombre del encargado cuando éste exista:

 

SELECT department.department, employee.full_name

FROM employee

  RIGHT JOIN department ON department.mngr_no = employee.emp_no

ORDER BY department.department

 

 

DEPARTMENT                FULL_NAME                            

----------------------------------------------------------------

Consumer Electronics Div. Cook, Kevin                          

Corporate Headquarters    Bender, Oliver H.                     

Customer Services         Williams, Randy                      

Customer Support          Young, Katherine                     

Engineering               Nelson, Robert                       

European Headquarters     Reeves, Roger                         

Field Office: Canada      Sutherland, Claudia                  

Field Office: East Coast  Weston, K. J.                        

Field Office: France      Glon, Jacques                        

Field Office: Italy       Ferrari, Roberto                      

Field Office: Japan       Yamamoto, Takashi                    

Field Office: Singapore                                        

Field Office: Switzerland Osborne, Pierre                      

Finance                   Steadman, Walter                      

Marketing                                                      

Pacific Rim Headquarters  Baldwin, Janet                       

Quality Assurance         Forest, Phil                         

Research and Development  Papadopoulos, Chris                  

Sales and Marketing       MacDonald, Mary S.                   

Software Development                                           

Software Products Div.                                   

 

Note los espacios vacíos en la columna 'FULL_NAME' del nombre del encargado. Hemos utilizado aquí una unión a derecha, es decir preservando la tabla de la derecha de la partícula JOIN (en este caso, department).

Podemos reescribir la misma consulta, con el mismo resultado, preservando la tabla izquierda:

 

SELECT department.department, employee.full_name

FROM department

  LEFT JOIN employee ON department.mngr_no = employee.emp_no

ORDER BY department.department

 

Notemos que el resultado es el mismo, solamente hemos cambiado el orden de las tablas en la cláusula FROM.

Por último, podemos preservar las dos tablas al mismo tiempo: se mostrarán todos los registros de ambas tablas, completando con nulos los lugares donde falten datos relacionados:

 

SELECT department.department, employee.full_name

FROM department

  FULL JOIN employee ON department.mngr_no = employee.emp_no

ORDER BY department.department

 

DEPARTMENT                FULL_NAME                            

----------------------------------------------------------------

Consumer Electronics Div. Cook, Kevin                          

Corporate Headquarters    Bender, Oliver H.                    

Customer Services         Williams, Randy                      

Customer Support          Young, Katherine                     

Engineering               Nelson, Robert                       

European Headquarters     Reeves, Roger                        

Field Office: Canada      Sutherland, Claudia                  

Field Office: East Coast  Weston, K. J.                        

Field Office: France      Glon, Jacques                        

Field Office: Italy       Ferrari, Roberto                     

Field Office: Japan       Yamamoto, Takashi                    

Field Office: Singapore                                        

Field Office: Switzerland Osborne, Pierre                      

Finance                   Steadman, Walter                     

Marketing                                                      

Pacific Rim Headquarters  Baldwin, Janet                       

Quality Assurance         Forest, Phil                         

Research and Development  Papadopoulos, Chris                  

Sales and Marketing       MacDonald, Mary S.                   

Software Development                                           

Software Products Div.                                         

                          Green, T.J.                          

                          Nordstrom, Carol                     

                          Bishop, Dana                         

                          Guckenheimer, Mark                   

                          Page, Mary                           

                          Johnson, Scott                       

                          Burbank, Jennifer M.                 

                          Brown, Kelly                         

                          Johnson, Leslie                      

                          Phong, Leslie                        

                          Fisher, Pete                          

                          Lee, Terri                           

                          Parker, Bill                         

                          Lambert, Kim                         

                          Yanowski, Michael                     

                          Stansbury, Willie                    

                          Bennet, Ann                          

                          Montgomery, John                     

                          De Souza, Roger                       

                          Young, Bruce                         

                          Ramanathan, Ashok                    

                          Hall, Stewart                        

                          Leung, Luke                           

                          O'Brien, Sue Anne                    

                          Ichida, Yuki        

 

Ahora tenemos espacios vacíos (recordemos que son valores nulos) en ambas columnas. Este tipo de consultas sirve para ubicar rápidamente registros huérfanos, que han quedado desenlazados de la tabla maestra en una relación maestro-detalle. Recordemos que se pueden declarar restricciones de clave externa para evitar esto.

<<<Poner algún otro ejemplo de las uniones externas>>>
Creación, modificación y borrado de objetos

 

Lenguaje de Definición de Datos (DDL)

 

 

El Lenguaje de Definición de Datos es un subconjunto de SQL con instrucciones para crear, modificar y borrar los distintos objetos que componen la Base de Datos como ser tablas, índices, dominios, etc. Incluso permiten la definición de Bases de Datos completas.

 

 

Bases de Datos

 

Creación de una base de datos

Para crear una base de datos se utiliza la sentencia CREATE DATABASE. La sintaxis es la siguiente:

 

CREATE {DATABASE | SCHEMA} 'filespec'

   [USER 'username' [PASSWORD 'password']]

   [PAGE_SIZE [=] int]

   [LENGTH [=] int [PAGE[S]]]

   [DEFAULT CHARACTER SET charset]

   [<secondary_file>];

 

SCHEMA = equivalente a DATABASE

<secondary_file> = FILE 'filespec' [<fileinfo>] [<secondary_file>]

<fileinfo> = [LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }

   [<fileinfo>]

 

Veremos las distintas opciones de la sentencia anterior a través de ejemplos.

 

1.    CREATE DATABASE ‘C:\EMPLEADOS.GDB’ USER ‘SYSDBA’ PASSWORD ‘masterkey’;

 

Esta es la instrucción más común: crea un archivo con el nombre dado como primer parámetro, conectando al servidor con el nombre de usuario y clave dados (el usuario usado debe tener permisos para creación de bases de datos… utilizaremos casi siempre el nombre del administrador del servidor, que por defecto es ‘SYSDBA’ con clave ‘masterkey’).

Si ya existe un archivo con el nombre pedido se cancela la operación.

No es obligatoria la extensión ‘.GDB’[8] en el archivo; el motor de datos no considera la extensión sino el contenido.

 

El tamaño no importa (perdón, Godzilla!)

El tamaño de las bases de datos Firebird es dinámico; el servidor va agregando páginas (a continuación) al archivo a medida que las necesita. Es posible indicar al servidor un tamaño máximo para un archivo de la base de datos, siempre que haya otros archivos a continuación. Por ejemplo, en la sentencia anterior podríamos agregar LENGHT 10000 para indicar al servidor un tamaño máximo de 10000 páginas; pero al tratarse del último archivo de la base (de hecho, el único) el servidor ignorará la indicación y considerará al archivo como de tamaño dinámico.

En ejemplos posteriores partiremos una base de datos en varios archivos, caso en el que sí se respeta el tamaño de cada archivo –salvo el último.

 

 

2.    CREATE DATABASE ‘C:\EMPLEADOS.GDB’ USER ‘SYSDBA’ PASSWORD ‘masterkey’ PAGE_SIZE 4096;

 

Crea la misma base de datos que el ejemplo anterior, pero instruye al servidor para que utilice páginas de 4096 bytes en lugar de 1024, que es el valor por defecto. Con esta configuración se alcanza un mejor rendimiento, como se explica a continuación.

Páginas

Los registros dentro de las tablas se organizan en conjuntos llamados páginas. Las páginas son la unidad de intercambio de datos entre el archivo en disco y la memoria; cuando el servidor necesita datos tiene que cargar una página entera a la memoria. Estas páginas se mantienen en la memoria por un tiempo, de manera que estén rápidamente disponibles si se solicitan nuevamente. La división en páginas es válida tanto para datos como para los índices.

El tamaño por defecto de 1024 bytes es muy conservador de memoria; en los sistemas actuales, se alcanza un rendimiento mucho mayor usando páginas de 4096 bytes o más (Interbase soporta hasta páginas de 8192 bytes; Firebird, 16384 bytes). Mientras más grandes las páginas, más memoria se usará en el servidor pero serán necesarias menos lecturas de disco. El punto óptimo es un compromiso entre los recursos usados y el rendimiento necesario para la aplicación en consideración.

 

 

3.    CREATE DATABASE ‘C:\EMPLEADOS.GDB’ USER ‘SYSDBA’ PASSWORD ‘masterkey’ PAGE_SIZE 4096 FILE ‘c:\EMPLEADOS2.GDB’ STARTING AT PAGE 10001;

 

Esta instrucción crea una base de datos dividida en dos archivos: el principal, ‘c:\empleados.gdb’, de hasta 40.960.000 bytes (4096*10000, unos 40 Mb) y otro secundario llamado ‘c:\empleados2.gdb’ que almacenará las páginas a partir de la 10001.

Una forma equivalente de definir la misma base de datos sería la siguiente:

CREATE DATABASE ‘C:\EMPLEADOS.GDB’ USER ‘SYSDBA’ PASSWORD ‘masterkey’ PAGE_SIZE=4096 LENGHT=10000 PAGES FILE ‘c:\EMPLEADOS2.GDB’;

 

Note también que los signos ‘=’ (igual), así como la palabra ‘PAGES’, son opcionales.

Cuando se especifican varios archivos, es importante tener en cuenta que en los archivos secundarios no se pueden incluir nombres de nodos de red; por lo tanto, si se crea la base de datos de forma remota, todos los archivos secundarios se crearán en el mismo nodo.

 

 

4.     CREATE DATABASE ‘C:\EMPLEADOS.GDB’ USER ‘SYSDBA’ PASSWORD ‘masterkey’ DEFAULT CHARACTER SET 'ISO8859_1';

 

Esta última sentencia especifica un juego de caracteres por defecto para la Base de Datos.

 

El juego de caracteres

El juego de caracteres de una base de datos determina los caracteres a usar en las columnas de tipo CHAR, VARCHAR o BLOB subtipo 1. Ligado al juego de caracteres estará también el tamaño de almacenamiento, ya que por ejemplo el juego UNICODE_FSS tiene caracteres que ocupan 1 byte y otros que ocupan hasta 3 bytes.

También actúa en el ordenamiento (collation order) y determina las transformaciones que se pueden hacer entre distintos juegos de caracteres (transliteration). Para ver los distintos juegos de caracteres y las especificaciones de ordenamiento (collation orders) disponibles, podemos interrogar a las tablas de sistema:

 

SELECT RDB$CHARACTER_SET_NAME, RDB$CHARACTER_SET_ID

FROM RDB$CHARACTER_SETS

ORDER BY RDB$CHARACTER_SET_NAME;

 

SELECT RDB$COLLATION_NAME, RDB$CHARACTER_SET_ID

FROM RDB$COLLATIONS

ORDER BY RDB$COLLATION_NAME;

 

En el ejemplo anterior (4) se especifica el juego de caracteres ‘ISO8859_1’ para ser usado por defecto; no obstante, se puede cambiar en forma individual para cada campo alfanumérico de una tabla.

Si no se especifica el juego de caracteres por defecto, se tomará como NONE; esto significa que no se hará ninguna conversión al almacenar caracteres en los campos alfanuméricos. Pero mucho cuidado: no se podrán copiar cadenas entre campos definidos con NONE y otros definidos con un juego de caracteres diferente. Esto restringe la utilización futura de distintos juegos de caracteres en campos específicos de una tabla.

Los clientes pueden especificar también el juego de caracteres con el que trabajarán, emitiendo una sentencia SET NAMES <juego de caracteres> antes de trabajar con los campos alfanuméricos. Esto le indica al servidor las transformaciones que debe efectuar en los caracteres que se pasan entre el servidor y el cliente, en ambos sentidos.

Si no especificamos un juego de caracteres para el cliente, se tomará NONE por defecto. La restricción que comentamos antes de no poder convertir caracteres extendidos entre NONE y cualquier otro se mantiene, por lo que el servidor rechazará cualquier intento de introducir caracteres extendidos en una tabla.

Podemos enunciarlo como

Regla Práctica: al acceder a una BD con un juego de caracteres distinto de NONE, el cliente también debería indicar un juego de caracteres distinto de NONE, preferentemente el mismo que la BD.

Es muy común  encontrarse con el siguiente problema: a pesar de haber definido el juego de caracteres iso8859_1 por defecto para la base de datos, cuando se quiere ingresar una cadena con eñes o vocales acentuadas el servidor se queja y rechaza el ingreso. Como podrá imaginarse si ha leído lo anterior con atención, falta definir el juego de caracteres en la conexión del cliente. Si está ejecutando un script SQL, agregue la sentencia SET NAMES iso8859_1; al principio. Si es un programa, defina el juego de caracteres de la conexión según lo requiera su lenguaje de programación.

 

Un juego de caracteres puede soportar varias especificaciones de ordenamiento (collation orders). Por ejemplo, el juego de caracteres ISO8859_1 usado para los caracteres europeos puede ser ordenado según especificaciones francesas (COLLATE FR_CA) o españolas (COLLATE ES_ES). Las distintas opciones se obtienen de las tablas de sistema como se indicó antes. Este es el resultado en un servidor Firebird 1.0 mediante la siguiente sentencia (una mezcla de las dos anteriores):

<<<Determinar que diferencia hay entre los ordenamientos fr_ca y es_es, con un ejemplo>>>

 

SELECT cs.RDB$CHARACTER_SET_NAME, co.RDB$COLLATION_NAME

FROM RDB$CHARACTER_SETS cs

  left join RDB$COLLATIONS co on cs.RDB$CHARACTER_SET_ID=co.RDB$CHARACTER_SET_ID

ORDER BY cs.RDB$CHARACTER_SET_NAME;

 

RDB$CHARACTER_SET_NAME

RDB$COLLATION_NAME                 

ASCII

ASCII                              

BIG_5

BIG_5                              

CYRL

DB_RUS                             

 

PDOX_CYRL                          

 

CYRL                               

DOS437

DB_UK437                            

 

DB_US437                           

 

DB_FRA437                          

 

DB_ITA437                          

 

DB_NLD437                          

 

DB_SVE437                          

 

DB_FIN437                          

 

DB_ESP437                          

 

DB_DEU437                          

 

PDOX_ASCII                         

 

PDOX_INTL                          

 

PDOX_SWEDFIN                       

 

DOS437                             

DOS850

DB_UK850                            

 

DB_US850                           

 

DB_FRA850                          

 

DB_ITA850                          

 

DB_PTB850                          

 

DB_FRC850                          

 

DB_NLD850                          

 

DB_SVE850                          

 

DB_ESP850                          

 

DB_DEU850                          

 

DOS850                             

DOS852

DB_PLK                             

 

DB_SLO                             

 

DB_CSY                              

 

PDOX_CSY                           

 

PDOX_HUN                           

 

PDOX_PLK                           

 

PDOX_SLO                           

 

DOS852                             

DOS857

DB_TRK                              

 

DOS857                             

DOS860

DB_PTG860                          

 

DOS860                             

DOS861

PDOX_ISL                           

 

DOS861                             

DOS863

DB_FRC863                          

 

DOS863                             

DOS865

DB_DAN865                          

 

DB_NOR865                          

 

PDOX_NORDAN4                       

 

DOS865                             

EUCJ_0208

EUCJ_0208                          

GB_2312

GB_2312                            

ISO8859_1

DA_DA                              

 

DE_DE                              

 

FI_FI                              

 

EN_UK                              

 

EN_US                              

 

NO_NO                               

 

FR_CA                              

 

FR_FR                              

 

ES_ES                              

 

IS_IS                              

 

ISO8859_1                          

 

IT_IT                               

 

PT_PT                              

 

DU_NL                              

 

SV_SV                              

ISO8859_2

CS_CZ                              

 

ISO8859_2                          

KSC_5601

KSC_5601                           

 

KSC_DICTIONARY                     

NEXT

NEXT                               

 

NXT_DEU                            

 

NXT_FRA                            

 

NXT_ESP                            

 

NXT_US                             

 

NXT_ITA                             

NONE

NONE                               

OCTETS

OCTETS                             

SJIS_0208

SJIS_0208                          

UNICODE_FSS

UNICODE_FSS                        

WIN1250

WIN1250                            

 

PXW_PLK                            

 

PXW_SLOV                           

 

PXW_CSY                            

 

PXW_HUN                            

 

PXW_HUNDC                          

WIN1251

WIN1251                            

 

PXW_CYRL                            

WIN1252

WIN1252                            

 

PXW_INTL                           

 

PXW_INTL850                        

 

PXW_NORDAN4                        

 

PXW_SPAN                           

 

PXW_SWEDFIN                         

WIN1253

WIN1253                            

 

PXW_GREEK                          

WIN1254

WIN1254                            

 

PXW_TURK                           

 

Se pueden agregar especificaciones de ordenamiento, lo que se denomina secuencias de ordenamiento o collations. Las secuencias de ordenamiento se implementan en Firebird como funciones en librerías externas (.dll en Windows, .so en Linux). En el sitio web www.ibcollations.com se puede encontrar una referencia y una librería de ejemplo que implementa varias secuencias.

 

 

Modificación de una base de datos

Para modificar una base de datos existente se utiliza la sentencia ALTER DATABASE:

 

ALTER {DATABASE | SCHEMA}

   ADD <add_clause>;

 

<add_clause> = FILE 'filespec' [<fileinfo>] [<add_clause>]

 

<fileinfo> = LENGTH [=] int [PAGE[S]]

   | STARTING [AT [PAGE]] int [<fileinfo>]

 

Como vemos, la única posibilidad de modificación es la adición de nuevos archivos a la base de datos.

Con esta instrucción no se puede dividir una base de datos; únicamente agregar archivos a continuación de los existentes. Para partir una base de datos, haga un respaldo –al restaurarlo puede especificar los nuevos tamaños de los archivos.

Un ejemplo:

ALTER DATABASE

  ADD FILE 'empleados2.gdb'

  STARTING AT PAGE 10001 LENGTH 10000

  ADD FILE 'empleados3.gdb';

 

La sentencia del ejemplo agregará un archivo llamado ‘empleados2.gdb’, que comenzará a partir de la página 10001 (si ya había más páginas que esas, comenzará a continuación de la última existente) y después de 10000 páginas seguirá colocando datos en un tercer archivo llamado ‘empleados3.gdb’.

 

 

Borrado de una base de datos

Para borrar  una base de datos (eliminando el archivo físico) se utiliza DROP DATABASE sin parámetros:

 

DROP DATABASE;

 

Hay que estar conectado a la base de datos antes de borrarla.

 

 

 

Tablas

 

Las tablas son las estructuras básicas que tienen que estar presentes en nuestra base de datos. ¿Qué es una base de datos sin datos? Entonces, veamos las posibilidades que nos brinda Interbase para la definición de tablas, y cómo plasmarlas en SQL.

 

Creación de una tabla

La sentencia CREATE TABLE se utiliza para definir la estructura de una tabla. La sintaxis general es la siguiente:

 

CREATE TABLE table [EXTERNAL [FILE] 'filespec']

   (<col_def> [, <col_def> | <tconstraint> …]);

 

En esencia, lo que espera esta sentencia es el nombre de la tabla –que debe ser único entre las tablas y procedimientos de la base de datos- y una lista de columnas o restricciones a nivel de tabla, entre paréntesis y separadas por comas. Opcionalmente se puede especificar que los datos de la tabla se guarden en un archivo externo.

Veamos, como antes, los distintos elementos de la sintaxis con ejemplos progresivos:

 

1.    CREATE TABLE EJEMPLO1 (CAMPO1 integer);

 

Esta sentencia crea una tabla llamada EJEMPLO1 con un solo campo de tipo entero llamado CAMPO1. Hay algunas cosas para notar aquí:

 

Nombres de objetos

Los nombres de los objetos de la Base de Datos deben seguir ciertas normas:

·Deben ser únicos en su tipo –por ejemplo, no puede haber dos tablas que se llamen igual- en la misma base de datos.

·Deben empezar con una letra

·En dialecto 3, se pueden usar caracteres no ingleses como la ñ, y también espacios; pero entonces, en todas las sentencias SQL que referencien al objeto (incluída la de creación de la tabla) hay que encerrar este nombre entre comillas dobles (“ ”).

·No se diferencian minúsculas de mayúsculas, salvo que estemos en Dialecto 3 y encerremos el nombre entre comillas dobles (“ ”). En otro caso, los nombres serán convertidos a mayúsculas y en las sentencias SQL se pueden escribir de cualquier forma.

 

 

Tipos de datos soportados por Firebird

Las columnas de una tabla almacenan datos de un solo tipo, que debe ser especificado en la declaración de la tabla. Los tipos de datos soportados por el servidor Firebird 1.0 son los siguientes:

 

<datatype> =

   {SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[<array_dim>]

   | (DATE | TIME | TIMESTAMP}[<array_dim>]

   | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]

   | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]

       [<array_dim>] [CHARACTER SET charname]

   | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}

       [VARYING] [(int)] [<array_dim>]

   | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]

       [CHARACTER SET charname]

   | BLOB [(seglen [, subtype])]

 

<array_dim> = [[x:]y [, [x:]y …]]

 

 

Nombre

Tipo

Tamaño real

Rango

 

SMALLINT

Número entero

2 bytes

-32768..32767

INTEGER

Número entero

4 bytes

-216..216-1

INT64 o BIGINT

Número entero

8 bytes

-232..232-1

 

FLOAT

Número real

 

 

DOUBLE PRECISION

Número real

 

 

DECIMAL(n,p)[EC10] 

Número real: n dígitos, p decimales

 

 

NUMERIC(n,p)

Número real: n dígitos, p decimales

En dialecto 3, n <= 18. Si n>15, se almacena como un INT64 que se escala multiplicando y dividiendo por 10^p

 

 

DATE

Fecha

4 bytes (double word)

 

TIME

Hora

4 bytes (double word)

 

TIMESTAMP

Fecha y hora

8 bytes

 

 

CHAR(n)

Caracteres, tamaño fijo. Máximo n caracteres

N bytes

 

VARCHAR(n)

Caracteres, tamaño variable. Máximo n caracteres

N+2 bytes. Los dos primeros bytes almacenan el tamaño

 

 

BLOB sub_type 0

Datos binarios arbitrarios

 

 

BLOB sub_type 1

Caracteres, sin límite de tamaño (Memo)

 

 

 

 

Condiciones para los campos

Veamos un ejemplo real, obtenido de la base de datos de ejemplo employee.gdb que se instala con Firebird[9]:

CREATE TABLE CUSTOMER (

       CUST_NO INTEGER NOT NULL,

       CUSTOMER VARCHAR(25) NOT NULL,

       CONTACT_FIRST VARCHAR(15),

       CONTACT_LAST VARCHAR(20),

       PHONE_NO VARCHAR(20),

       ADDRESS_LINE1 VARCHAR(30),

       ADDRESS_LINE2 VARCHAR(30),

       CITY VARCHAR(25),

       STATE_PROVINCE VARCHAR(15),

       COUNTRY VARCHAR(15),

       POSTAL_CODE VARCHAR(12),

       ON_HOLD CHAR(1) DEFAULT NULL,

      PRIMARY KEY (CUST_NO)

);

 

Se han declarado aquí algunas condiciones sobre los campos: por ejemplo, se indica que el campo CUST_NO no puede quedar vacío o que el campo ON_HOLD tomará un valor por defecto nulo. Estas condiciones son controladas por el servidor, por lo que la aplicación cliente no debe hacer nada para forzarlas.

La sintaxis completa de la definición de un campo es la siguiente:

<col_def> = col {<datatype> | COMPUTED [BY] (<expr>) | domain}

       [DEFAULT {literal | NULL | USER}]

       [NOT NULL]

       [<col_constraint>]

       [COLLATE collation]

 

·     NOT NULL: el registro no se introducirá en la tabla si este campo no tiene algún valor.

·     DEFAULT: el campo tomará el valor por defecto especificado si no se indica ninguno. El valor puede ser una constante, la palabra 'NULL' (se asigna valor nulo), o la palabra 'USER' (se asigna el nombre del usuario autenticado actualmente). También se puede usar la palabra 'TODAY' para asignar la fecha actual, si el campo es de tipo DATE.

·     COLLATE: válida para campos de caracteres, indica que se usará la secuencia de ordenamiento especificada.

·     COMPUTED BY: el valor del campo se calculará aplicando una fórmula, como se explica en la siguiente sección.

 

 

Campos calculados

Se pueden declarar campos calculados en el servidor, indicando la fórmula de cálculo en función de otros campos de la tabla. Por ejemplo, la siguiente definición es válida:

 

CREATE TABLE CLIENTES (

  ID_CLIENTE INTEGER NOT NULL PRIMARY KEY,

  NOMBRE VARCHAR(30) NOT NULL,

  APELLIDO VARCHAR(30) NOT NULL,

  NOMBRECOMPLETO COMPUTED BY (APELLIDO || ', ' || NOMBRE)

);

 

Se pueden usar funciones estándar de SQL y/o funciones externas (UDF), encerrando la expresión completa entre paréntesis. El tipo de datos de la columna calculada se asigna automáticamente, en base a la expresión de cálculo.

Veamos otros ejemplos:

 

Columna de subtotal para una factura

CREATE TABLE ITEMS (

  ID_ITEM INTEGER NOT NULL PRIMARY KEY,

  ID_FACTURA INTEGER NOT NULL REFERENCES FACTURAS(ID_FACTURA),

  CANTIDAD INTEGER DEFAULT 1 NOT NULL,

  DESCRIPCION VARCHAR(50) NOT NULL,

  PRECIO_UNIT NUMERIC(18,4) DEFAULT 0 NOT NULL,

  SUBTOTAL COMPUTED BY (CANTIDAD*PRECIO_UNIT)

);

 

Columna que calcula la fecha de cumpleaños del año actual en base a la fecha de nacimiento

CREATE TABLE FECHAS

(

  ID INTEGER,

  FECHANAC DATE,

  FECHACUMPLE COMPUTED BY (extract(year from current_date) || '-' ||

    extract(month from FechaNac) || '-' ||

    extract(day from FechaNac))

);

En este último ejemplo habría que tener cuidado con los años bisiestos: si la fecha de nacimiento cae un 29 de febrero, en todos los años que no fueran bisiestos se generaría un error. Esto es así porque la columna FECHACUMPLE se considera como de tipo DATE, por el formato especial que le dimos. Podemos evitar el problema usando cast:

CREATE TABLE FECHAS2

(

  ID INTEGER,

  FECHANAC DATE,

  FECHACUMPLE COMPUTED BY (cast(extract(year from current_date) || '-' ||

    extract(month from FechaNac) || '-' ||

    extract(day from FechaNac) as char(10)))

);

 

 

 

Restricciones

Además de las condiciones, Firebird acepta que se declaren restricciones a nivel de columna o de tabla.

La sintaxis para las restricciones de tabla es la siguiente:

<restriccion_de_tabla> = [CONSTRAINT constraint]

   {{PRIMARY KEY | UNIQUE} (col [, col …])

   | FOREIGN KEY (col [, col …]) REFERENCES otra_tabla

       [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

       [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

   | CHECK (<condicion>)}

 

 

Mientras que las restricciones a nivel de columna siguen la sintaxis

<col_constraint> = [CONSTRAINT constraint]

   { UNIQUE

   | PRIMARY KEY

   | REFERENCES other_table [(other_col [, other_col …])]

       [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

       [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

   | CHECK (<search_condition>)}

 

 

Como vemos, son casi iguales; de hecho, lo único que varía es la sintaxis, las restricciones son las mismas. Actualmente se soportan los siguientes cuatro tipos:

 

·       PRIMARY KEY (campo1, campo2, ...): clave primaria. Define los campos que formarán la clave primaria de la tabla.

·       UNIQUE (campo1, campo2, ...): unicidad. Indica que la combinación de los valores de los campos listados no puede repetirse en la tabla.

·       FOREIGN KEY (campo1, campo2, ...) REFERENCES tabla2 (campo3, campo4, ...): clave externa o foránea. Los campos (1, 2, ...) listados referencian a los campos (3, 4, ...) de la segunda tabla. Los valores de los campos de la segunda tabla deben identificar unívocamente a un registro para que la referencia sea válida; en la práctica, debe existir una clave primaria o una restricción de unicidad sobre esos campos.

Opcionalmente se puede especificar qué debe hacer el servidor cuando no se cumple la restricción, incluso con el detalle de las dos operaciones que pueden hacer que esto suceda: el borrado (ON DELETE) y la modificación de datos (ON UPDATE) de la tabla referenciada (tabla2). Las posibilidades son las siguientes:

·        NO ACTION: el servidor rechazará la operación

·        CASCADE: el servidor aplicará la misma operación en los registros de la tabla detalle (donde se define la restricción) para que la regla siga siendo válida

·        SET DEFAULT: los campos de la tabla de detalle toman los valores por defecto (se pierde la referencia)

·        SET NULL: los campos de la tabla de detalle toman el valor NULL (se pierde la referencia)

 

·       CHECK (condición): validación. Únicamente se permitirá el ingreso del registro en la base de datos si la condición se cumple.

 

Un ejemplo de restricciones de columna:

CREATE TABLE CLIENTES (

       NROCLIENTE INTEGER NOT NULL PRIMARY KEY,

       NOMBRE VARCHAR(50) NOT NULL,

       IDPOBLACION INTEGER REFERENCES POBLACIONES(IDPOBLACION)

);

Notemos que en el campo IDPOBLACION no fue necesario siquiera especificar que se trataba de una clave externa (foreign key).

 

 

Matrices

La utilización del modificador <array_dim> a continuación del tipo de datos indica que el campo contendrá una matriz de valores del tipo indicado. Por ejemplo, si definimos un campo como

 

Campo1 INTEGER[4,3]

 

estamos declarando una matriz de 4 x 3 enteros, que se accederán con el nombre del campo seguido de los correspondientes índices entre corchetes.

Las matrices pueden ser multidimensionales: simplemente escriba todas las dimensiones separadas con comas, entre los corchetes.

 

Modificación de la estructura de una tabla

Se puede modificar la estructura de una tabla ya creada -dentro de ciertos límites- usando la instrucción Alter table. La sintaxis completa es la siguiente:

 

ALTER TABLE tabla <operación> [, <operación> …];

 

donde <operación> es una construcción de la siguiente forma:

<operación> = {ADD <definición_de_columna>

| ADD <restricción_de_tabla>

| ALTER [COLUMN] nombre_columna <cláusula_de_columna_alternativa>

| DROP columna

| DROP CONSTRAINT restricción}

 

<cláusula_de_columna_alternativa > = {TO nuevo_nombre_columna

| TYPE nuevo_tipo_de_datos

| POSITION nueva_posición}

 

veremos las distintas opciones por separado para no confundirnos.

Por ejemplo, si tenemos la tabla de clientes que vimos antes

CREATE TABLE CLIENTES (

       NROCLIENTE INTEGER NOT NULL PRIMARY KEY,

       NOMBRE VARCHAR(50) NOT NULL

);

 

y queremos agregar las columnas 'direccion' y 'telefono', podemos hacer

 

ALTER TABLE CLIENTES

ADD DIRECCION VARCHAR(100),

ADD TELEFONO VARCHAR(50);

 

Ahora, notamos que la columna NOMBRE es muy corta para los datos que debemos introducir; podemos cambiarla haciendo

 

ALTER TABLE CLIENTES

ALTER COLUMN NOMBRE TYPE varchar(100);

 

Igualmente, si queremos cambiarle el nombre a una columna

 

ALTER TABLE CLIENTES

ALTER COLUMN NROCLIENTE TO NRO_CLIENTE;

 

Lo mismo con la posición de las columnas. En realidad al servidor le importa muy poco la posición que tenga una columna dentro de una tabla, por lo que es más bien una cuestión estética.

 

ALTER TABLE CLIENTES

DROP NOMBRE,

ADD NOMBRE VARCHAR(100) NOT NULL;

 

Para cambiar los atributos de una columna muchas veces es necesario borrar dicha columna y crearla de nuevo con los atributos deseados. Esta acción por supuesto que conlleva ¡la eliminación de los datos de la columna original! La práctica común en estos casos es la siguiente:

 

 

Los programas de administración de bases de datos generalmente realizan estas operaciones automáticamente.

<<<Ejemplo>>>

 

Hay que tener presente también que las tablas no son, en general, independientes una de otra; las relaciones que forman el modelo de datos pueden impedir que se realice un cambio en la estructura de una tabla. Piense por ejemplo en qué pasaría si tenemos una tabla A que referencia a una tabla B por medio de una columna (clave externa) y queremos modificar el tipo de datos de esa columna. La referencia a la tabla B ya no sería válida -los tipos de datos deben ser iguales en los dos lados de la referencia. Para evitar este problema, el servidor rechazará cualquier cambio que afecte a una columna que forma parte de una restricción. En estos casos, tendremos que eliminar primero la restricción, realizar el cambio, y volver a crear la restricción.

 

Borrado de una tabla completa

 

Se puede eliminar una tabla completa usando la sentencia Drop Table. La sintaxis es muy simple:

 

DROP TABLE Nombre_Tabla

 

Esta operación borrará todos los datos de la tabla; úselo con mucho cuidado!

 

Dominios

 

Es muy común que en una base de datos haya varias columnas del mismo tipo (por ejemplo: nombres de empleados, de clientes, de proveedores...). En casos como este, conviene definir nuevos 'tipos' de datos con las características comunes a todos (por ejemplo, Varchar(40)) y darle un nombre propio (por ejemplo, NombresDePersonas). Estos nuevos tipos se denominan Dominios. Los dominios se utilizan como si fueran tipos de datos normales, por ejemplo en las sentencias Create Table y Alter Table.

 

Creación de dominios

<<<Sintaxis de Create Domain>>>

 

CREATE DOMAIN NOMBRESDEPERSONAS AS VARCHAR(40);

 

También se pueden incluir restricciones en los dominios:

 

CREATE DOMAIN NOMBRESDEPERSONAS AS VARCHAR(40) NOT NULL;

CREATE DOMAIN IMPORTES AS NUMERIC(18,2) DEFAULT 0 NOT NULL;

CREATE DOMAIN MES INTEGER CHECK(VALUE > 0 AND VALUE <= 12);

 

Estas restricciones se aplicarán en todas las columnas definidas con este dominio.

 

Modificación de dominios

??? hay alguna instruccion para esto?

 

 

Borrado de dominios

Para eliminar directamente un dominio, Firebird implementa la sentencia Drop Domain

 

DROP DOMAIN Nombre_Dominio

 

<<<Ver que pasa cuando el dominio está en uso>>>

 

Esta sentencia hizo su aparición con Firebird 1.0; en Interbase, por lo menos hasta la versión 6 había que trabajar directamente con las tablas del sistema. <<<confirmar>>>

 

 

Indices

Los índices son estructuras auxiliares que utiliza el servidor para optimizar las consultas. Se construyen con los datos de un subconjunto de las columnas de la tabla a la que sirven, pero internamente en el servidor estos datos se almacenan de manera diferente para optimizar las búsquedas. Así, cuando se emite una instrucción SQL que debe buscar algún valor en una tabla, si esta tabla no tiene definido un índice sobre la columna en la que se busca entonces el servidor debe realizar una búsqueda secuencial –es decir, recorrer todos los registros hasta que encuentra el valor buscado. En cambio, si hay un índice sobre la columna buscada, el servidor automáticamente lo utiliza y recorre la estructura del índice para encontrar el valor. La estructura del índice es tal que esta búsqueda sólo lleva unas pocas lecturas.

Como veremos en la sección de optimización de consultas, una de las principales armas del programador para acelerar el proceso de los datos son los índices y la forma en que se puede indicar al servidor que los utilice.

 

Crear un índice

 

CREATE INDEX

 

Modificar un índice

 

ALTER INDEX

 

Borrar un índice

 

DROP INDEX

 

 

Restricciones

 

Las restricciones en el servidor sirven para mantener la integridad de los datos y también para implementar reglas de negocio. Estas restricciones se aplicarán siempre, no importa de dónde vengan los datos –ya sea desde la aplicación específica del programador o desde una herramienta de administración. En este aspecto son convenientes por la seguridad de los datos. También convienen al programador en el sentido que es más fácil mantener las reglas de negocio en el servidor, centralizadas, antes que hacerlo en todos los programas cliente que puedan estar en uso.

La mayor inconveniencia de la implementación de reglas de negocio en el servidor es la creación de dependencias entre las estructuras –por ejemplo, si se indica que una columna no puede tener valores repetidos, generalmente se hará por medio de una restricción UNIQUE; mientras esta restricción exista, no se podrá modificar el tipo o nombre de la columna. Para cambiar su tipo, por ejemplo para añadir más caracteres porque se nos ha quedado corta una columna, habrá que eliminar primero la restricción, hacer el cambio a la columna, y luego recrear la restricción. La situación empeora cuando se implementan también triggers y procedimientos almacenados.

No obstante las dificultades para alterar las estructuras, siempre es conveniente

 

PK

 

UQ

 

FK

 

Check

 

Not Null

 

 

Generadores

 

Los generadores son como 'variables' globales mantenidas en el servidor. Almacenan un número entero de 64 bits (BIGINT) que no es afectado por las transacciones. Esto significa que cuando un generador cambia de valor, todas las transacciones actualmente activas pueden ver ese valor inmediatamente. Esto significa que se puede usar un generador para obtener el valor de una secuencia numérica sin peligro de que dos clientes obtengan el mismo valor. Por esto es muy común usarlos en la generación de claves primarias.

Para acceder a un generador hay que usar una función predefinida: gen_id. La función espera dos parámetros:

 

Gen_id(nombre_generador, incremento): bigint

 

Nombre_generador es el nombre del generador

Incremento es un número entero que se sumará al valor actual antes de devolver el resultado.

 

Esta función se utiliza mucho en triggers para generar automáticamente el valor de una columna de clave primaria, por ejemplo:

 

 

 

Sin embargo, hay que tener cuidado: al no estar afectado un generador por las transacciones, una vez que se incrementa su valor ya no vuelven atrás –a no ser por una orden específica de un cliente. Esto implica que pueden quedar 'huecos' en la secuencia. Supongamos que el cliente A pide el valor de un generador (digamos 9), y lo incrementa en 1 quedando con valor 10. Almacena este valor en una columna de una tabla, y luego por cualquier motivo cancela la transacción (rollback). La modificación de la tabla se vuelve atrás… pero no así el generador. Si luego otro cliente hace lo mismo, obtendrá el valor 10 y el generador quedará en 11. Esta vez si se acepta la transacción, por lo que en la tabla queda el valor 10 pero no el 9. Por lo tanto:

No utilice los generadores para secuencias numéricas donde no pueda haber 'huecos' (secuencias auditables, como los números de facturas).

 

 

Crear un generador

La sentencia para crear un generador es muy simple:

 

CREATE GENERATOR nombre_generador;

 

El generador se crea con valor 0 (cero).

 

Modificar un generador

Se puede modificar el valor de un generador, para que comience a generar números a partir de ahí:

 

SET GENERATOR <Nombre_generador> TO <Nuevo_valor>

 

 

Borrar un generador

Drop generator (FB)

 

 

Excepciones

 

Definir excepciones

Create exception

 

Modificar excepciones

???

 

Borrar excepciones

Drop exception

 

(1.5) En la versión 1.5 de Firebird se agrega la posibilidad de cambiar el mensaje de la excepción al momento de lanzarla. Veremos esta característica cuando hablemos luego sobre Procedimientos Almacenados y Triggers.

 


Programación del servidor

 

Firebird permite escribir bloques de código que se almacenan en la base de datos. Se programan en un lenguaje especial que se denomina Procedure SQL o PSQL, que consta de las sentencias SQL estándar más algunas instrucciones especiales para estos procedimientos.

La ventaja es que se puede centralizar el proceso de los datos en el mismo servidor, lo que garantiza mejor rendimiento y más facilidad para actualizar el código, ya que sólo se hace en un lugar y todos los clientes se benefician inmediatamente. La desventaja es que el lenguaje no tiene la riqueza de un lenguaje común de alto nivel como Pascal o C.

Hay dos clases de objetos que se pueden programar en Firebird: Procedimientos Almacenados y Triggers. La principal diferencia entre los dos es la forma de ejecutarlos. Los Procedimientos Almacenados se ejecutan por demanda, es decir, el usuario tiene que activarlos explícitamente; los Triggers se ejecutan automáticamente cuando se realiza alguna acción sobre los datos (insert, update, delete).

 

Scripts

 

El servidor Firebird permite ejecutar una serie de instrucciones seguidas, obtenidas de un archivo de texto simple. Esta secuencia de instrucciones se denomina script.

Cada instrucción del script debe terminar en un símbolo especial llamado terminador. Por defecto este símbolo es el punto y coma (;) pero puede cambiarse con la instrucción SET TERM. Por ejemplo, si un script contiene la línea

 

SET TERM !;

 

Indica que a partir de esa línea y hasta el final del script se considerará como terminador el símbolo (!). Notemos que esta misma línea termina en punto y coma, ya que es una instrucción del script. Para volver al terminador por defecto tenemos que emitir la instrucción

 

SET TERM ;!

 

 

Para ejecutar un script en isql hacemos

INPUT <nombre_del_archivo>;

 

Veremos algunos ejemplos a continuación, cuando escribamos Triggers y Procedimientos Almacenados. Pero antes, repasemos algunas otras instrucciones de mucho uso en scripts:

 

SET SQL DIALECT 3;

Indica al compilador que la conexión se hará utilizando el dialecto 3 de SQL. Debe emitirse antes de abrir la conexión. <<<Confirmar>>>

 

SET NAMES <juego_de_caracteres>;

Indica al compilador que la conexión se hará utilizando por defecto el juego de caracteres dado. Debe emitirse antes de abrir la conexión.

 

CONNECT '<cadena_de_conexión>' USER '<nombre_usuario>' PASSWORD '<clave_de_conexión>';

Abre la conexión a la base de datos dada por la cadena de conexión, con el usuario y la clave indicadas.

 

COMMIT WORK;

Acepta los cambios realizados en la base de datos hasta esa línea.

 

 

 

Procedimientos almacenados

 

Para crear un procedimiento almacenado se usa la sentencia CREATE PROCEDURE:

 

CREATE PROCEDURE nombre

   [(parametro <tipo_de_datos> [, parametro <tipo_de_datos> …])]

   [RETURNS <tipo_de_datos> [, parametro <tipo_de_datos> …])]

   AS <cuerpo_del_procedimiento> [terminador]

 

<cuerpo_del_procedimiento> =

   [<lista_de_declaracion_de_variables>]

   <bloque>

 

< lista_de_declaracion_de_variables> =

   DECLARE VARIABLE nombre_variable <tipo_de_datos>;

   [DECLARE VARIABLE nombre_variable <tipo_de_datos>; …]

 

<bloque> =

BEGIN

   <sentencia_compuesta>

   [<sentencia_compuesta> …]

END

 

<sentencia_compuesta> = {<bloque> | sentencia;}

 

Bien, veamos algunos ejemplos para entender esta sintaxis. Dividiré a los procedimientos en dos tipos generales: procedimientos de acción y procedimientos de selección. No sé si esta es la denominación 'oficial', pero nos servirá para estudiarlos.

 

 

Procedimientos de acción

Los procedimientos de acción son aquellos que no devuelven un cursor, solamente ejecutan algunas acciones sobre los datos. Por ejemplo, podemos hacer un procedimiento que actualice una tabla de auditoría:

 

SET SQL DIALECT 3;

 

CONNECT 'localhost:c:\archivos de programa\firebird15\examples\employee.fdb' USER 'sysdba' PASSWORD 'masterkey';

 

SET TERM !;

 

CREATE PROCEDURE SP_AUDITORIA

AS

BEGIN

  INSERT INTO AUDITORIA (ID,FECHA,USUARIO)

  VALUES (GEN_ID(GENAUDITORIA,1),CURRENT_DATE,CURRENT_USER);

END!

 

COMMIT WORK!

 

SET TERM ;!

 

CREATE PROCEDURE SP_AUDITORIA

AS

BEGIN

  INSERT INTO AUDITORIA (ID,FECHA,USUARIO)

  VALUES (GEN_ID(GENAUDITORIA,1),CURRENT_DATE,CURRENT_USER);

 

Es decir, tenemos el comienzo de un procedimiento almacenado pero no su final (la palabra END). Esto provoca un error de compilación. Por lo tanto, es imprescindible cambiar de símbolo terminador antes de la definición del procedimiento.

 

En los procedimientos se pueden usar las sentencias SQL comunes, además de algunas extensiones especiales.

 

 

Triggers

 

Los triggers o disparadores son procedimientos que se programan en el servidor, en el lenguaje especial del servidor. Estos procedimientos se ejecutan automáticamente cuando se cumple una acción determinada. Hay seis posibilidades:

Before Insert: de produce antes de insertar un registro

After Insert: después de insertar un registro

Before / After Update: antes / después de actualizar un registro

Before / After Delete: antes / después de borrar un registro

 

Firebird acepta más de un trigger para cada evento; al crearlos se les asigna un número de orden que se seguirá al momento de ejecutarlos. Además, en Firebird 1.5 se agrega la posibilidad de definir triggers polivalentes, para más de un evento. Hablaremos de ellos más adelante.

 

 

 

 

Funciones del usuario (UDF)

 

Librerías estándar de funciones externas

 

Crear funciones externas en Delphi

 


Acceso desde Delphi

 

BDE

IBX

DBX

ADO

IBO

UIB

 

 


Optimización

 

Plan de ejecución

 

Creación de índices

 

Selectividad

 

Restricciones declarativas vs. activas

 

Restricción de la cantidad de registros a traer al cliente

 

Futuro

 

 

 



[1] Actualmente se están haciendo pruebas con nuevos modelos optimizados para otras tareas –como es el caso de las BD distribuidas de Internet o las específicamente diseñadas para trabajar con audio y video- o para otras estructuras de datos, como ser los objetos.

[2] A partir de la versión 1.5 se ha reducido el espectro de sistemas operativos en los que corre Firebird; se sigue activamente el desarrollo en las plataformas más utilizadas, como Windows y Linux, pero el grueso de los programadores no trabaja en las demás. Su disponibilidad depende del trabajo de voluntarios para cada plataforma.

[3] En realidad hay otro estado, el ‘limbo’. Es un estado que solamente surge en transacciones que involucran mas de una base de datos, por lo que no lo consideraremos en la discusión. Las transacciones en limbo pueden ser eliminadas cuando se hace un backup.

[4] Por supuesto, una transacción puede ver cualquier cambio hecho en la misma.

[5] En otros sistemas (y en la BDE) se denomina Repeatable Read a este nivel de aislamiento.

[6] Al final se incluye una referencia de los símbolos especiales usados en los esquemas de sintaxis

[7] El estándar ANSI-92 propone una nueva sintaxis para los encuentros entre tablas, con más posibilidades. No obstante, la forma inicial se mantiene en uso.

[8] ‘GDB’ son las iniciales de la empresa que creó Interbase: Groton Database Systems. Groton se llama el pueblo natal de Jim Starkey, su creador original. Es también la causa detrás de la ‘g’ inicial de los nombres de las utilidades.

[9] En la definición real de esta tabla (de la base de datos employee.gdb) se usan en realidad dominios para la mayoría de los campos; he escrito los equivalentes más simples (sin comprobaciones ni restricciones) hasta que estudiemos el tema.