Firebird Guia

26/6/2014

Fir ebi r d Saved from http:// infoback. infoback. com.ar/buhardilla/firebird/fir com.ar/buhardilla/firebird/firebird.htm ebird.htm

search

6 Mar 2013 01:55:18

no other snapshots from this url

All snapsho snapshots ts from host host infoback.com.ar Linked from es.wikipedia.org » Firebird Text

Image

d o wn lo lo a d .z .z ip ip

re po po rt rt a bu bu se se

PIN THIS PAGE:

other

Firebird Contenido Firebi Firebird................... rd......................................................................................... ................................................................................................................................ .......................................................... 1 Introd Introducci ucción ón...................................................................... ............................................................................................................................................ ...................................................................... 5 Descripción y características generales.................................................................................................... 5 Arqui Arquitectu tectura..................................................... ra............................................................................................................................ .................................................................................. ........... 7 Servi Servidor....................................................... dor............................................................................................................................. ................................................................................. ........... 7 Cli Cliente..................................................................... ente.......................................................................................................................................... ..................................................................... 7 Bloqueos, versiones y generaciones................................................................................................ 7 Operaciones con múltiples tablas.............................................................................................................. 9 Transacci Transaccion ones.............................................................. es.................................................................................................................................... ...................................................................... 9 Concurrencia 1: niveles de aislamiento de transacciones................................................................. 11 Concurrencia 2: modo de bloqueo de una transacción..................................................................... 12 Bloq Bloqueo ueoss pesimi pesimistas...................................... stas............................................................................................................ ............................................................................... ......... 14 Manten Mantener er el context contexto........................ o............................................................................................... ........................................................................................... .................... 15 Integri Integridad dad referenci referencial al................................................................ ........................................................................................................................ ........................................................ 15 Instal Instalaci ación ón y puesta puesta en marcha............................... marcha...................................................................................................... ................................................................................ ......... 17 Descarga Descarga desde desde Internet Internet........................................................... ................................................................................................................... ........................................................ 17 Probar Probar la instalaci nstalación ón.................................................................. .......................................................................................................................... ........................................................ 17 ISQL....................................................................... ISQL.......................................................................................................................................... ................................................................... 18 Herram erra mientas................................................................ ientas............................................................................. ....................... ......................... .................. ...... ...... ...... ....... ................. ............. 19 IBServer..................................................................................................... ............................................................................................................ ................................ 19 IBGuard IBGuardiian................................................................................................. an.................................................................................................................................. ................................. 19 ISQL....................................................................... ISQL.......................................................................................................................................... ................................................................... 19 Gbak........................ Gbak............................................................................................... ................................................................................................................... ............................................ 19 IBConso IBConsolle............................................................... e................................................................................................................................... .................................................................... 19 Ejecutar sentencias SQL en IBConsole.................................................................................... 21 IBOConso IBOConsolle............................................................. e................................................................................................................................ ................................................................... 22 IB_SQL........................................... IB_SQL................................................................................................................. ........................................................................................... ..................... 23 SQL...................................................................... SQL............................................................................................................................................ ............................................................................... ......... 24 El lenguaj enguajee de consul consulta...................................... ta............................................................................................................. ................................................................................ ......... 24 SQLSQL son las iniciales de......................................................................................................... 24 Cursores Cursores vs conj conjuntos..... untos........................................................................... .................................................................................................................. ............................................ 24 Selección de registros: SELECT........................................................................................................ 25 Orden en la sala................................................................... sala........................................................................................................................... ........................................................ 29 Fil Filtrados trados y búsqu búsquedas........................ edas.............................................................................................. ........................................................................................... ..................... 30 Fechas/Ho Fechas/Horas ras en SQL..................................................................... SQL.................................................................................................................. ............................................. 32 Criteri Criterios os de selecci selección ón........................................................... ................................................................................................................... ........................................................ 34 Funci Funcion ones................................................................ es.................................................................................................................................... .................................................................... 38 Funci Funcion ones es de agregaci agregación ón.................................................................. .............................................................................................................. ............................................ 38 Subco Subconsu nsulltas........................................................................................................... tas................................................................................................................................ ..................... 41 Comparación con el resultado de una subconsulta.......................................................................... 43 Union Uniones................................................................... es....................................................................................................................................... .................................................................... 45 Consulta de varias tablas relacionadas........................................................................................... 45 Creación, modificación y borrado de objetos............................................................................................ 52 Len ua e de Defini Definici ción ón de Datos Datos DDL ........................................................... ............................................................................................ ................................. 52 52 http://ar chi ve.today/yKaUQ

1/47

26/6/2014

Fir ebi r d

Bases de Datos................................................................... Datos........................................................................................................................... ........................................................ 52 Creación Creación de una una base de datos............................. datos................................................................................................. .................................................................... 52 El tamaño no importa (perdón, Godzilla!)................................................................................... 53 Páginas............................ Páginas.................................................................................................. ...................................................................................................... ................................ 53 El juego juego de caracteres............................................................................. caracteres............................................................................................................. ................................ 54 Modificación de una base de datos........................................................................................... 57 Borrado Borrado de una una base de datos.............................. datos.................................................................................................. .................................................................... 58 Tablas......................................................... Tablas................................................................................................................................ ................................................................................ ......... 58 Creación Creación de una una tabl tabla.................................................................. a.............................................................................................................. ............................................ 58  Nombres  Nombres de objetos........ objetos............... ............. ............. .............. ............. ............. ............. ............. ............. ............. .............. ............. ............. ............. ............. ............. ...... 59 Tipos de datos soportados por Firebird...................................................................................... 59 Condiciones para los campos................................................................................................... 60 Campo Camposs calcul calculado ados.......................................................... s.................................................................................................................. ........................................................ 61 Restricci Restriccion ones.................................................................. es.......................................................................................................................... ........................................................ 62 Modificación de la estructura de una tabla................................................................................ 63 Borrado de una tabla completa................................................................................................. 65 Domi Dominios.............................. nios.................................................................................................... ....................................................................................................... ................................. 65 Creación Creación de domi domini nios............................... os..................................................................................................... ............................................................................... ......... 65 Modificación de dominios......................................................................................................... 66 Borrado Borrado de domi domini nios................................ os....................................................................................................... ................................................................................ ......... 66 Indi Indices......................................................... ces............................................................................................................................... ............................................................................... ......... 66 Crear un índi ndice............................................................... ce....................................................................................................................... ........................................................ 66 Modi Modifi ficar car un índi ndice.......................................................... ce.................................................................................................................. ........................................................ 66 Borrar Borrar un índi ndice.............................................................. ce...................................................................................................................... ........................................................ 67 Restricci Restriccion ones................................................................ es.................................................................................................................................... .................................................................... 67 PK..................................................................................................................................... PK.............................................................. ................................................................................ ......... 67 UQ............................................................................................................. UQ...................................... ....................................................................................................... ................................ 67 FK...................................................................................... FK.............................................................................................................................................. ........................................................ 67 Check..................................................................... Check......................................................................................................................................... .................................................................... 67  Not Null............ Null.................. ............. .............. ............. ............. ............. ............. ............. ............. ............. ............. .............. ............. ............. ............. ............. ............. ............. ........... .... 67 Generadores...... eneradores............................................................................. ............................................................................................................................... ........................................................ 67 Crear un generado generador.................................................. r...................................................................................................................... .................................................................... 68 Modi Modifi ficar car un generado generador........................................................ r................................................................................................................ ........................................................ 68 Borrar Borrar un generado generador................................................. r..................................................................................................................... .................................................................... 69 Excepci Excepcion ones................................................................. es..................................................................................................................................... .................................................................... 69 Defini Definirr excepci excepcion ones............................................................. es..................................................................................................................... ........................................................ 69 Modi Modifi ficar car excepci excepcion ones......................................................... es................................................................................................................. ........................................................ 69 Borrar Borrar excepci excepcion ones.............................................................. es...................................................................................................................... ........................................................ 69 Programaci Programación ón del servido servidor.................................................. r...................................................................................................................... .................................................................... 70 Scripts... Scripts.......................................................................... .......................................................................................................................................... ................................................................... 70 Procedi Procedimiento mientoss almacen almacenado ados................................................................. s............................................................................................................. ............................................ 71 Procedi Procedimiento mientoss de acción acción................................................................. ............................................................................................................. ............................................ 71 Trigg Triggers..................................................... ers........................................................................................................................... ........................................................................................... ..................... 72 Funciones del usuario (UDF).................................................................................................................. 73 Librerías estándar de funciones externas............................................................................................ 73 Crear funciones externas en Delphi................................................................................................... 73 Acceso desde desde Delph Delphii..................................................................... ............................................................................................................................. ........................................................ 74 BDE............................................................................ BDE................................................................................................................................................ .................................................................... 74 IBX.................................................................. IBX........................................................................................................................................ ............................................................................... ......... 74 DBX................................................................ DBX....................................................................................................................................... ................................................................................ ......... 74 ADO....................................................................................... ADO............................................................................................................................................... ........................................................ 74 IBO......................................................................................... IBO................................................................................................................................................. ........................................................ 74 http://ar chi ve.today/yKaUQ

2/47

26/6/2014

Fir ebi r d

UIB......................................................................................... UIB................................................................................................................................................. ........................................................ 74 Opti Optimizaci mización ón..................................................................... ......................................................................................................................................... .................................................................... 75 Plan Plan de ejecuci ejecución ón...................................................................... .............................................................................................................................. ........................................................ 75 Creación Creación de índi ndices....................................................... ces........................................................................................................................... .................................................................... 75 Selecti Selectivi vidad dad.................................................................. ...................................................................................................................................... .................................................................... 75 Restricciones declarativas vs. activas................................................................................................. 75 Restricción de la cantidad de registros a traer al cliente....................................................................... 75 Futu Futuro.................... ro........................................................................................... ............................................................................................................................... ........................................................ 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 principal que es objetivo objetivo del presente se denomina denomina Firebird , literalmente Pájar o de fue go , el Fénix que renace de sus cenizas. No tengo confirmación ‘oficial’,  pero creo que que el nombre nombre se eligi eligióó por por las múlt múltip iples les ‘vidas’ ‘vidas’ que ha tenido tenido el proyecto proyecto Interbase, que que culmi culminaron naron en Fireb Firebird ird.. Las característi cara cterísticas cas de este gestor de datos son  prácticamente  prácticamente igual iguales es –hasta la versión versión 1.0- a las las de Interbase de Borland Borland en su versión versión 6.0. 6.0. Y es que Firebi Firebird fue creado a partir partir de los los fuentes de Interbase 6.0, liberados liberados  por Borlan Borlandd bajo bajo licen licencia cia IPL. Un conjunto conjunto de excelentes excelentes y voluntari voluntariosos osos programad programadores ores se impuso impuso la la tarea de llevar llevar adelante adelante el gestor gestor de datos sin sin costo, agregando 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 [1] este modelo . Firebird Firebird es pequeña –una – una instalación completa completa ocupa alrededor de 10 Mb incluyendo librerías, librerías, ejemplos 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 Controles de validación validación (Check) (Chec k)

o

Columnas Columnas computadas, s ólo de lectura

·

Posibilidad de programación del servidor mediante disparadores –triggers- y proc edimientos almacenad os –stored  – stored procedures. procedures.

·

Posibili Posibilidad dad de definir definir procedim proc edimientos ientos almacenados que devuelvan ‘tablas ‘ tablas virtuales’

http://ar chi ve.today/yKaUQ

3/47

26/6/2014

Fir ebi r d

os

·

a

e e n c n e v s as

·

Generadores de números secuenciales, independientes de las transacciones

·

Excepciones

·

Eventos

·

Funciones Funciones definidas definidas por el usuario usua rio

·

Dominios

·

Un lenguaje SQL amplio amplio y potente, que contempla contempla funcio f unciones nes avanzadas a vanzadas como c omo ser  o

Subconsultas

o

Uniones

o

Funciones de agregación

o

Ordenamiento por columnas columnas calculadas, c alculadas, etc

o

Vectores Vectore s multidi multidimension mensionales ales (matrices)

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

 Arqui tectura Firebird Firebird se compone de dos partes: pa rtes: el servidor s ervidor propiamente propiamente dicho, y el cliente que se s e comuni c omunica ca con él. Servidor 

El servidor servidor es la parte que hace hac e el trabajo: almacena almacena los datos, los los procesa, proces a, ejecuta los procedimientos procedimientos almacenados y apli a plica ca las vali va lidaciones. daciones. Debe existir 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  gener aciona l .

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. Generalm Genera lmente ente se utiliz utilizan an bloqueos  de diferentes niveles –registro, página, tabla- para impedir que otras transacciones modifiquen datos que se están  procesando.  procesando. Esto tiene tiene la desventaj desventajaa de que esas transacciones transacciones quedarán imp imposi osibi bili litadas tadas de trabajar trabajar con los los datos bloqu bloqueados eados hasta que la la transacción que imp impuso uso el  bloqu  bloqueo eo termine. 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 lecturas repetibles repetibles . 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 Para lograr este nivel de aislamiento sería necesario bloquear  los  los cambios a todos los registros a considerar, o hacer una copia  (una nueva gene ració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 Firebird usa el segundo. En breve: cada vez que se hace una modificación a un registro, se crea una nueva versión o gene ració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 ·

Activa

·

Aceptada (Committed)

http://ar chi ve.today/yKaUQ

[3] :

4/47

26/6/2014

Firebird

Cancelada (Rolled back)

·

El estado de todas las transacciones existentes en la BD es mantenido en las  Páginas de Inv entario de Tran saccion es 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 transa cció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 http://archive.today/yKaUQ

5/47

26/6/2014

Firebird

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 ( Durab ilidad 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

[4] Cuando se trabaja en modo multiusuario , 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 segun do u suario no ve rá los cambios qu e 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 si uientes: http://archive.today/yKaUQ

6/47

26/6/2014

Firebird

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. [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 (lectura repetible

snapshot table stability (lectura repetible forzada): igual que la anterior, pero además desde el momento que accedemos a una tabla ésta  se bloq uea p ara e scritura .

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 f antasma , 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 d e 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 guar de el re gistro (p ost) bloq uea lo s cambios d e las d emá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. Re ad-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). http://archive.today/yKaUQ

7/47

26/6/2014

Firebird

Re ad-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 IBDatase t. 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 ant erior 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 pa ra 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 re istros. No uede utilizarse con o en una vista ni con tablas externas ni con los resultados de un rocedimiento almacenado llamado desde un SELECT." http://archive.today/yKaUQ

8/47

26/6/2014

.

,

,

Firebird

.

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 Basu ra 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.

<<>> 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 ke y) 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 a viso 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, http://archive.today/yKaUQ

9/47

26/6/2014

Firebird

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  for mato 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: Abra una ventana de terminal en la carpeta bin de la distribución de Firebird Ejecute el programa escribiendo isql Is l res onderá con un indicador rom t es ecial ' S http://archive.today/yKaUQ

L>'

como se ve en la si uiente ima en: 10/47

26/6/2014

Firebird

Escriba 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 ; isql indicará que una línea continúa una sentencia anterior cambiando el indicador (prompt) por ' CON>'.  Las senten cias se terminan con pun to y co ma (;). Si la conexión se puede establecer, isql responderá con un mensaje indicando que está ahora conectado a la base de datos. A partir de ese momento se pueden ejecutar sentencias SQL sobre esa base de datos, o usar algunos comandos específicos de isql como Show Tables . En la parte de herramientas  hablaremos más en  profundidad sobre la utilidad isql.

Herramientas IBServer 

IBGuardian

ISQL

Gbak http://archive.today/yKaUQ

11/47

26/6/2014

Firebird

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.

http://archive.today/yKaUQ

12/47

26/6/2014

Firebird

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 ejecución y estadísticas de ejecución).

, y el resultado se obtiene inmediatamente en la parte inferior (datos, plan de

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 <<>>, 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.

http://archive.today/yKaUQ

13/47

26/6/2014

Firebird

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 rec omenzar 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 subconunto de re istros ue cum len determinado criterio

http://archive.today/yKaUQ

14/47

26/6/2014

Firebird

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 r ecorr en los registros que c umplen 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 selecc ión, y el servidor modificará el subconjunto que cumpla el criterio sin más intervención nuestra.

Selección de regi stros: SELECT  La sentenc ia 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: http://archive.today/yKaUQ

15/47

26/6/2014

Firebird

¿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 sí  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 e l servido r . 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

http://archive.today/yKaUQ

16/47

26/6/2014

Firebird

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): , nivel (U$S ) 3) De la tabla de proyectos: una columna llamada ‘Proyecto’ con el siguiente formato () 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

http://archive.today/yKaUQ

BY  siempre se co loca a l final  de la instrucción, después de todas las demás cláusulas optativas. 17/47

26/6/2014

tra os y

Firebird

sque as

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 . 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

=, ==

Igualdad

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

Desigualdad

Comparación

> < >= <=

Lógicos

Mayor  Menor  Mayor o igual Menor o igual

!>, ~>, ^>

 No mayor 

!<, ~<, ^<

 No menor 

NOT

 Negación

AND

Conjunción (‘Y’)

OR

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 a visé. http://archive.today/yKaUQ

18/47

26/6/2014

Firebird

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





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 '.'. P ara 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 http://archive.today/yKaUQ

19/47

26/6/2014

Firebird

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. Vamos a crear una tabla llamada Prueba, con los siguientes campos: Campo

Tipo de dato

Clave

Integer  

Hora

Time

Fecha

Date

Todo

Timestamp

CREATE TABLE prueba (

clave intege r not null primary ke y, 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 es menor que hoy 2) devuelva los registros donde es menor que el momento actual 3) devuelva los registros donde sea igual a la fecha de ayer  4) devuelva los registros donde 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. LIKE : 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): http://archive.today/yKaUQ

20/47

26/6/2014

Firebird

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’. STARTING WITH : devuelve TRUE si la cadena comparada comienza con el valor pasado. Es equivalente a LIKE ; 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'

CONTAINING : 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 CON TAINING 'a'

BETWEEN  AND : devuelve TRUE cuando el valor comparado (que puede ser de cualquier tipo salvo BLOB) se encuentra entre y , 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:

http://archive.today/yKaUQ

21/47

26/6/2014

Firebird

FROM   employee  WHERE  hire_date NOT BET WEEN '1990-01-01' AND '1990-12-31'

Devolverá los empleados que no fueron contratados en 1990. IN : 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  subc onsu lta 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. IS NULL: devuelve TRUE si el valor es nulo; también puede usarse la forma 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 *  employee FROM   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 > ... [ ] END

http://archive.today/yKaUQ

22/47

26/6/2014

Firebird

<<>>

2) con búsqueda: para cada valor se especifica una expresión CASE ... [ ] END

<<>>

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  (): devuelve la misma cadena con todos los caracteres en mayúsculas. CAST(

as ): convierte el valor al tipo dado… si puede.

GEN_ID(,): incrementa el valor del  gene rado r  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. <<>> En dialecto 3 disponemos de la función EXTRACT para obtener una parte de una fecha: EXTRACT({year | month | day} FROM   )

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. <<>> (1.5) COALESCE(expr1,

expr2, expr3, … exprN) devuelve

el valor de la primera expresión no nula.

<<>>

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(): devuelve el valor medio del campo en

el grupo de registros considerado.

COUNT( | *):

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 ): igual que el anterior, pero cuenta

http://archive.today/yKaUQ

sólo los registros con valores diferentes en el campo pedido. 23/47

26/6/2014

Firebird

 MAX(): devuelve el valor máximo del campo en el grupo.  MIN(): devuelve el valor mínimo del campo en el grupo. SUM  (): 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 d eparta mento, es decir que la suma actuara sobre  subco nju ntos 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 c ada depa rtamento dife rente . 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: ??????

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

http://archive.today/yKaUQ

24/47

26/6/2014

Firebird

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: (salary) as TotalPorDepto select dept_no as depto, sum  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  employee from   where  job_country='USA' group by dept_no (salary)>200000 having sum  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. http://archive.today/yKaUQ

25/47

26/6/2014

Firebird

, . . 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'  last_name='Young' and  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= ='

http://archive.today/yKaUQ

'

='

'

26/47

26/6/2014

Firebird _

_

_

)

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 subco nsulta

Los siguientes operadores se pueden usar con subconsultas:  ALL : devuelve TRUE si el valor es igual a todos los devueltos por la subconsulta  ANY

| S OME :

EXISTS :

devuelve TRUE si el valor comparado es igual a alguno  de los devueltos por la 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    :

http://archive.today/yKaUQ

devuelve TRUE si la subconsulta devuelve exactamente  una fila.

27/47

26/6/2014

Firebird

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 un a 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. <<>>

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! <<>> 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  Joins ). encuentros (  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 WHERE:

[7] , listando los nombres de todas las tablas intervinientes luego de FROM, separadas por comas, y con las relaciones en la cláusula

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. http://archive.today/yKaUQ

28/47

26/6/2014

Firebird

¡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  preser va 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 {INNER | LEFT | RIGHT | FULL} JOIN ON

Escribamos algunos de los ejemplos anteriores c on la nueva sintaxis: SELECT department.department, employee.full_name

http://archive.today/yKaUQ

29/47

26/6/2014

Firebird

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, preserva ndo 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

http://archive.today/yKaUQ

30/47

26/6/2014

Firebird

 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. <<>>

Creación, modificación y borrado de ob jetos 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 http://archive.today/yKaUQ

31/47

26/6/2014

Firebird

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]  

[];

SCHEMA = equivalente a DATABASE = FILE 'filespec' [] [] = [LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }  

[]

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ág inas (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ág inas. 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. http://archive.today/yKaUQ

32/47

26/6/2014

Firebird

, 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 o rder ) 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   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á cu alquier intento de introducir caracteres extendidos en una tabla. Podemos enunciarlo como Re gla 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): <<>> 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

http://archive.today/yKaUQ

33/47

26/6/2014

Firebird _ 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

http://archive.today/yKaUQ

34/47

26/6/2014

Firebird 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  secuen cias de orden amiento o co llations. 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 ; = FILE 'filespec' [] [] = LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int []

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';

http://archive.today/yKaUQ

35/47

26/6/2014

Firebird

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'] ( [, | …]);

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: = {SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[] | (DATE | TIME | TIMESTAMP}[] | {DECIMAL | NUMERIC} [(precision [, scale])] [] | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)] [] [CHARACTER SET charname] | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)] [] | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int] [CHARACTER SET charname]

http://archive.today/yKaUQ

36/47

26/6/2014

Firebird

| BLOB [(seglen [, subtype])] = [[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 { | COMPUTED [BY] () | domain} [DEFAULT {literal | NULL | USER}] [NOT NULL]  

[]

http://archive.today/yKaUQ

37/47

26/6/2014

Firebird

[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: = [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 ()}

http://archive.today/yKaUQ

38/47

26/6/2014

Firebird

Mientras que las restricciones a nivel de columna siguen la sintaxis = [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 ()}

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  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 [, …];

donde es una construcción de la siguiente forma: = {ADD | ADD | ALTER [COLUMN] nombre_columna | DROP columna | DROP CONSTRAINT restricción} = {TO nuevo_nombre_columna | TYPE nuevo_tipo_de_datos | POSITION nueva_posición}

http://archive.today/yKaUQ

39/47

26/6/2014

Firebird

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: crear una columna temporal con los nuevos atributos copiar los datos de la columna anterior a la temporal  borrar la columna anterior  crear la columna nueva con los atributos nuevos copiar los datos de la columna temporal a la nueva  borrar la columna temporal Los programas de administración de bases de datos generalmente realizan estas operaciones automáticamente. <<>> 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 http://archive.today/yKaUQ

40/47

26/6/2014

Firebird

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

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

<<>> 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. <<>>

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 http://archive.today/yKaUQ

41/47

26/6/2014

Firebird

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 g enera dore s par a secu encias numéricas d onde no p ueda hab er 'hue cos' (sec uencia s aud itables, co mo los números de f actura s).

Crear un generador 

La sentencia para crear un generador es muy simple: CREATE GENERATOR nombre_generador;

http://archive.today/yKaUQ

42/47

26/6/2014

genera or se crea con va or

Firebird

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 TO

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 http://archive.today/yKaUQ

43/47

26/6/2014

Firebird

INPUT ;

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. <<>> SET NAMES ; 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 '' USER '' PASSWORD ''; 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 [, parametro …])] [RETURNS [, parametro …])] AS [terminador] =   []   < lista_de_declaracion_de_variables> = DECLARE VARIABLE nombre_variable ; [DECLARE VARIABLE nombre_variable ; …] = BEGIN   [ …] END = { | 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!

http://archive.today/yKaUQ

44/47

26/6/2014

Firebird

SET TERM ;!

Vemos como es necesario cambiar el terminador antes del cuerpo del procedimiento. Esto es así porque el procedimiento completo se interpreta como una sola instrucción (y por lo tanto tiene que terminar con el símbolo terminador del script); mientras que las instrucciones internas del procedimiento tienen  que terminar con  punto y coma. Si no cambiamos el terminador por defecto del script, se tomará el primer punto y coma del procedimiento como final del mismo, obteniéndose una instrucción inválida. En el ejemplo anterior, si eliminamos la línea SET TERM !; entonces el servidor interpretará lo siguiente como una orden: 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. Otro detalle a considerar es la utilización de la función gen_id para obtener el siguiente valor del generador de procedimientos almacenados.

GenAuditoria. Esto es muy común en la programación

La variable Current_Date contiene la fecha actual (de tipo DATE), disponible únicamente en dialecto 3. La variable Current_User contiene el usuario autenticado en el servidor (dialecto 3). 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  http://archive.today/yKaUQ

45/47

26/6/2014

Firebird

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 h aciendo pruebas con nuevos modelos opt imizados p ara otras tareas –co mo 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 d e datos, como ser los objetos. [2]  A partir de la versión 1.5 se ha reducido el es pectro de s istemas operativos en los que corre Firebird; s e sigue act ivamente el des arrollo en las plataformas más utilizadas, como Windows y Linux, pero el grues o de los programadores no trabaja en las d emás. Su d isponibilidad depende del trabajo de vo luntarios para cada plataforma. [3]  En realidad hay otro es tado, el ‘limbo’. Es un estado que so lamente su rge en transacciones q ue involucran mas de una bas e de datos , por lo que no lo cons ideraremos en la discusión. Las transacciones en limbo pueden ser eliminadas cuando se hace u n 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 Re ad a este nivel de aislamiento. [6]  Al final se incluye una referencia de los símbolos es peciales u sados en los es quemas de sintaxis [7]  El estándar ANSI-92 propone una nueva s intaxis para los en cuentros entre tablas, con más po sibilidades. No ob stante, 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 d e las utilidades. [9]  En la definición real de es ta tabla (de la bas e de dat os employee.gdb) se us an en realidad dominios para la mayoría de los campos; h e escrito los eq uivalentes más simples (sin comprobaciones n i restricciones) has ta que estudiemos el tema.

http://archive.today/yKaUQ

46/47