Indexando una columna computada

Deja un comentario

Como sabes, las columnas computadas son muy útiles para ver su contenido en los SELECTs y también cuando las usamos en la cláusula WHERE (de un UPDATE, de un DELETE, o de un SELECT) o en la cláusula ORDER BY.

Ya las habíamos visto en varios artículos anteriormente, ahora veremos una de sus características más interesantes: pueden ser indexadas.

Las primeras versiones del Firebird no lo permitían, pero a partir de la versión 2.0 ya tenemos esa posibilidad.

Por ejemplo, la tabla de SUCURSALES tiene estos datos:

INDICE1

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

Como podemos ver, algunos nombres están en mayúsculas y algunos nombres están en minúsculas. Podríamos crear el siguiente índice:


CREATE INDEX IDX_SUCURSALES1 ON SUCURSALES COMPUTED BY (UPPER(SUC_NOMBRE));

COMMIT;

Y luego usarlo en comandos como los siguientes:


SELECT * FROM SUCURSALES ORDER BY UPPER(SUC_NOMBRE)

INDICE2

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

Aquí, los nombres de las sucursales aparecen ordenados alfabéticamente y al mirar el PLAN vemos que efectivamente se usó el índice recientemente creado.


SELECT * FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) STARTING WITH 'BOG'

INDICE3

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

Aquí le pedimos que nos muestre los nombres de todas las sucursales que empiezan con las letras ‘BOG’ y vemos que también usó el índice computado que habíamos creado anteriormente.


DELETE FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) = 'MIAMI - ESTADOS UNIDOS'

INDICE4

También en este caso se usó el índice de la columna computada, como podemos comprobarlo mirando el PLAN.

Otros ejemplos:


CREATE INDEX IDX_MOVIMCAB1 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX))

CREATE INDEX IDX_MOVIMCAB2 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

CREATE DESCENDING INDEX IDX_MOVIMCAB3 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

El índice IDX_MOVIMCAB1 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento.

El índice IDX_MOVIMCAB2 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento y el número del documento.

El índice IDX_MOVIMCAB3 de la tabla MOVIMCAB (cabecera de movimientos) es muy parecido al índice IDX_MOVIMCAB2 pero ahora el índice es descendente (es decir, de mayor a menor).

Conclusión:

Tener columnas computadas es algo muy bueno porque nos facilita la vida. Poder indexar a esas columnas computadas es algo más bueno aún.

Como hemos visto en los ejemplos anteriores, el Firebird usará a los índices de las columnas computadas cada vez que los necesita.

Esos índices pueden involucrar a una sola columna o a varias columnas (como en los dos últimos ejemplos donde IDX_MOVIMCAB2 y IDX_MOVIMCAB3 involucran a las columnas MVC_FECHAX y MVC_NRODOC).

Artículos relacionados:

Columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Programa para configurar al Firebird

Deja un comentario

En un artículo ya habíamos visto como configurar al Firebird:

Configurando al Firebird

pero lo hacíamos de forma manual. Si queremos configurarlo a través de una interface gráfica entonces podemos usar un programa creado por Jhonny Suárez, que podemos descargar desde:

http://sourceforge.net/projects/firebirdconfig/files/firebirdconfig/FirebirdConfig%200.2.0.39/

Allí encontraremos varios archivos, generalmente lo más recomendable es descargar el instalador, como vemos aquí:

FIREBIRDCONFIG1

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

Después de descargarlo, instalarlo, y ejecutarlo, veremos una pantalla similar a la siguiente:

FIREBIRDCONFIG2

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

¿Para qué sirve este programa?

Lo normal es que cuando necesitamos configurar al Firebird lo hagamos modificando el archivo FIREBIRD.CONF, pero no es tan amigable como configurarlo usando FIREBIRDCONFIG.EXE

O sea que nos facilita la tarea de configuración.

Lo que debemos recordar es que:

  1. Debemos grabar los cambios realizados haciendo clic sobre el botón “Save
  2. Los cambios realizados tendrán efecto solamente después que hayamos reiniciado el Servidor del Firebird. Mientras no reiniciemos el Servidor nada habrá cambiado.

Artículos relacionados:

Configurando al Firebird

Alias, archivos y rutas

Restringiendo el acceso a las Bases de Datos

¿En cual carpeta tener las Bases de Datos?

Descargar gratis archivos de configuración

Usando Firebird con procesadores multinúcleo

Acelerando los SORT

Entendiendo “forced writes”

Forced writes

El índice del blog Firebird21

El foro del blog Firebird21

Validando números de teléfono

2 comentarios

La operación más importante de todas en una Base de Datos es la introducción de datos válidos. Si los datos no son válidos entonces todo lo demás que hagamos (consultas, procesamientos) será incorrecto y no servirá, será inútil.

Un dato que a veces es importante validar y a veces no, es el número de teléfono.

Muchas veces el número de teléfono es simplemente informativo, está ahí pero prácticamente no se lo usa. Sin embargo en ocasiones es extremadamente importante que sea válido. Es para estos últimos casos que debemos asegurarnos de que pueda ser utilizado cuando se lo necesita.

¿Cómo validamos un número de teléfono?

Los números de teléfono completos siempre están compuestos de la siguiente forma:

  • Código del país o región
  • Código del área
  • Número local

Cada país o región grande tiene un número que lo identifica. Por ejemplo el código de Argentina es 54, el de Bolivia 591, el de Brasil 55, el de Estados Unidos 1, el de Paraguay 595, el de Puerto Rico 1787, etc. Para saber que se trata del código del país o región y no de un número local, se le antecede con el signo + o con doble cero. Es decir que escribir 00 ó escribir + es lo mismo, la misma cosa.

El código del área (el área puede ser una ciudad grande, un estado o provincia o departamento) siempre viene a continuación del código del país.

Y finalmente viene el número local.

Ejemplos:

+49 30 1234567 corresponde a Alemania (porque empieza con 49), Berlín (porque continúa con 30), y el 1234567 es el número de esa ciudad y país. También se lo podría haber escrito como. 00 49 30 1234567.

+54 351 1234567 corresponde a Argentina (porque empieza con 54), provincia de Córdoba (porque sigue con 351) y el 1234567 es el número de esa ciudad y de ese país al cual se desea llamar. También se lo podría escribir como: 00 54 351 1234567.

+595 21 123456 corresponde a Paraguay (porque empieza con 595), ciudad de Asunción (porque sigue con 21),  el 123456 es el número de esa ciudad y ese país al cual se desea llamar. También se lo podría escribir como: 00 595 21 123456.

¿Cómo diseñamos la tabla?

Es muy común que para guardar números de teléfono se defina una columna como CHAR o VARCHAR y luego una longitud suficientemente grande, como por ejemplo:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,
   CLI_NOMBRE VARCHAR(80),
   CLI_TELEFO CHAR(20)
);

En este caso la columna CLI_TELEFO está definida como CHAR(20) y la mayoría de los diseñadores pueden creer que está bien. Sin embargo, no es lo correcto. ¿Por qué no es correcto? Porque los usuarios en la columna CLI_TELEFO podrían insertar datos así:

1234567

+595-21-1234567

021-1234567

21.1234567

00-595-21-1234567

Y si nuestra aplicación debe usar esos números para hacerles llamadas a los clientes será muy problemático conseguirlo. Lo correcto por lo tanto es:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,         -- Identificador del Cliente
   CLI_NOMBRE VARCHAR(80),     -- Nombre del Cliente
   CLI_CODPAI CHAR(4),         -- Código del país al cual corresponde el teléfono
   CLI_CODARE CHAR(4),         -- Código del área dentro del país
   CLI_TELEFO CHAR(8)          -- Número de teléfono local
);

De esa manera nunca habrá confusión posible. Inclusive podríamos tener un trigger BEFORE INSERT OR UPDATE que verifique el código del país, algo como:

CREATE EXCEPTION 
   E_CODIGO_PAIS_INCORRECTO 'El código del país es incorrecto';
CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
   END;

Este trigger verifica que el país sea Argentina (código 54) o que sea Paraguay (código 595). Si los países fueran más de 2 entonces lo más conveniente sería tener una tabla, llamada por ejemplo PAISES en la cual se encontraran los códigos y los nombres de los países.

De todas maneras lo importante del trigger es que valida que el país ingresado sea solamente uno de los países válidos, evitando así que el usuario introduzca datos que no tienen sentido.

Análogamente a como se validó el código del país se podría validar el código del área. Algo como:

CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
      IF (NEW.CLI_CODPAI = '595') THEN BEGIN
         IF (NEW.CLI_CODARE <> '21' AND NEW.CLI_CODARE <> '61') THEN
            EXCEPTION E_CODIGO_AREA_INCORRECTO;
      END
 
   END;

Aquí, si el código del país es 595 el código del área debe ser 21 ó 61, ningún otro código de área será aceptado.

Conclusión:

Validar los números de teléfono a veces no es importante porque es un dato meramente informativo, pero hay ocasiones en que debemos asegurarnos de que sea un número válido. Para esos casos lo más conveniente es agruparlo según el código del país, del área, y número local. Esas tres columnas deben ser validadas para asegurarnos de que contienen números correctos.

Artículos relacionados:

Diseño de bases de datos. 1FN

El índice del blog Firebird21

El foro del blog Firebird21

Cambiando el tipo de una columna: de caracter a numérico y viceversa

2 comentarios

Lo ideal es siempre que una vez que has definido la cantidad de columnas de cada tabla y el tipo de cada una de esas columnas ya no necesites modificarlas. Pero lo ideal … no siempre es lo que ocurre en la vida real.

Entonces, ahora te encuentras con una situación en la cual consideras conveniente o imprescindible cambiar el tipo de una columna. Por ejemplo era VARCHAR y la quieres convertir a INTEGER. O era SMALLINT y la quieres convertir a CHAR, cosas así.

El comando deseado

Si tienes una columna que es de tipo CHAR o VARCHAR y la quieres cambiar a INTEGER muchos desearían que existiera algo similar a:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TYPE
   INTEGER

Pero tal comando no existe en Firebird, si lo intentas obtendrás el mensaje: “Cannot change datatype for column MiColumna from a character type to a non-character type.

¿Por qué, cuál es el problema? El problema es que Firebird no hace la conversión en el momento en que escribes un comando como el anterior sino en el momento en que consultas su valor (con un SELECT) o cambias su valor (con un UPDATE). Por lo tanto cuando escribes el comando ALTER TABLE él no puede saber si todos los valores pueden convertirse a INTEGER o no. Y entonces hace lo más seguro: rechaza el cambio.

Claro, tú puedes decir: “pero si solamente hay números enteros en esa columna, debería aceptar el cambio del tipo de datos”. Eso tú lo sabes, pero el Firebird no lo sabe porque no verifica que efectivamente así sea. ¿Y por qué no lo verifica? Porque tu tabla podría tener millones y millones de filas y verificarla podría demorar mucho tiempo, por lo tanto hace la sencilla: impide que modifiques el tipo de datos.

La solución

Ya hemos visto cual es el problema, ahora veremos que hay una solución. No es tan sencilla como escribir un comando ALTER TABLE pero funciona.

Paso 1. Verificar que todos los valores puedan ser convertidos a INTEGER

Evidentemente que el 100% de los valores deben poder convertirse a INTEGER, si hay al menos uno que no puede convertirse entonces primero hay que solucionar eso y luego continuar con los siguientes pasos.

SELECT
   CAST(MiColumna AS INTEGER) AS TodosSonNumerosEnteros
FROM
   MiTabla;

Si el SELECT anterior terminó con errores, y quieres ver cuales son las filas que tienen esos errores (o sea, las filas que no tienen números enteros) el siguiente artículo te muestra lo que puedes hacer para ver a esas filas problemáticas:

Validando que el contenido de un CHAR o VARCHAR sea numérico

Si el SELECT anterior terminó sin errores, entonces seguimos con el:

Paso 2. Renombrar la vieja columna

COMMIT;

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TO
   ColumnaVieja;

Si esta columna tiene dependencias (es decir, si ha sido usada en alguna vista, stored procedure o trigger) entonces obtendrás un mensaje de error. Debes solucionar eso antes de poder continuar.

Este paso es muy importante porque te permite descubrir si la columna tiene dependencias.

Paso 3. Agregarle una columna a la tabla con el tipo de datos deseado

COMMIT;

ALTER TABLE
   MiTabla
ADD
   MiNuevaColumna INTEGER;

Paso 4. Agregarle los valores a la nueva columna

COMMIT;

UPDATE
   MiTabla
SET
   MiNuevaColumna = CAST(ColumnaVieja AS INTEGER)
WHERE
   ColumnaVieja IS NOT NULL;

Paso 5. Verificar que la nueva columna tenga los valores correctos

COMMIT;

SELECT
   (MiNuevaColumna + 1)
FROM
   MiTabla;

Si el Firebird te muestra algún mensaje de error (supuestamente todo debería estar ok, pero siempre lo mejor es asegurarse de que así sea) entonces tienes que buscar el motivo y solucionarlo, antes de continuar.

Paso 6. Borrar la columna vieja

Si el SELECT anterior finalizó sin errores, entonces ya podemos borrar con toda seguridad a la columna vieja, porque ya no la necesitaremos más.

COMMIT;

ALTER TABLE
   MiTabla
DROP COLUMN
   ColumnaVieja;

COMMIT;

Este paso es muy conveniente hacerlo para no confundirnos. Si no borramos a la columna vieja más adelante podríamos estar asignándole valores, algo que muy probablemente ya no deberíamos hacer.

Paso 7 (opcional). Renombrar a la nueva columna

Si deseas que la nueva columna tenga el mismo nombre que tenía la columna vieja, entonces:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiNuevaColumna
TO
   MiColumna

COMMIT;

Paso 8 (opcional). Poner a la nueva columna en la misma posición que tenía la vieja columna

Si quieres que la nueva columna que acabas de crear se encuentre en la misma posición que tenía la columna vieja que acabas de borrar, entonces:

ALTER TABLE
   MiTabla
ALTER
   MiColumna
POSITION
   6;

COMMIT;

Si la vieja columna se encontraba en la posición 1, entonces después de POSITION escribirías 1. Si se encontraba en la posición 2, escribirías 2, y así sucesivamente. El 6 que se puso arriba es solamente un ejemplo.

Conclusión:

En Firebird no hay un comando que nos permita cambiar el tipo de datos de una columna. Y no lo hay por nuestra propia seguridad, de esa manera se evita que cometamos errores graves como convertir texto a INTEGER.

Sin embargo, si necesitamos hacerlo hay formas de conseguirlo. En este artículo se mostró la forma más segura de todas (no la única, pero sí la más segura y conveniente de utilizar).

Con el Paso 1. verificamos que todos los números sean enteros. Con el Paso 2. verificamos que no hay dependencias. Con el Paso 3. agregamos una columna que tiene el tipo de datos que deseamos tener. Con el Paso 4. le agregamos a la nueva columna los valores que debe tener. Con el Paso 5. verificamos que la nueva columna tenga los valores correctos. Con el Paso 6. borramos a la vieja columna, esto nos evitará confusiones más adelante. Con el Paso 7. renombramos a la nueva columna, para que se llame igual a la columna original. Con el Paso 8. ponemos a la nueva columna en la misma posición que tenía la columna original.

Siguiendo estos pasos te asegurarás que todo se ha realizado correctamente. Todo quedará como debe quedar.

Por lo tanto, si alguna vez necesitas cambiar el tipo de datos de una columna, ya sabes como lograrlo.

Artículos relacionados:

Validando que el contenido de un CHAR o VARCHAR sea numérico

El índice del blog Firebird21

El foro del blog Firebird21

Validando que el contenido de un CHAR o VARCHAR sea numérico

2 comentarios

Como sabes, en una columna de tipo CHAR o VARCHAR puedes escribir cualquier caracter que desees. Pero ¿y si quieres comprobar que solamente haya números allí?

Bien, hay varias técnicas para conseguir ese objetivo pero probablemente la más eficiente sea utilizar el predicado de comparación SIMILAR TO.

Caso 1. Comprobar que solamente hay números enteros

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 SIMILAR TO '[[:DIGIT:]]*'

Aquí, solamente son permitidos los dígitos: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9. Si en la columna MiColumna1 existe cualquier otro caracter entonces la condición del filtro (o sea, la condición escrita en el WHERE) será falsa.

Caso 2. Comprobar que solamente hay números decimales

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'

¿Qué le estamos diciendo aquí al Firebird? Que puede haber cualquier cantidad de dígitos, luego un punto que debe existir sí o sí, y luego cualquier cantidad de dígitos.

Caso 3. Comprobar que solamente haya números, pero estos pueden ser enteros o decimales

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'

Este caso es muy parecido al anterior pero ahora el punto decimal no es requerido, o sea que puede existir o no existir. En consecuencia, se aceptarán los números enteros y también los números decimales.

Caso 4. Comprobando que todos los números sean negativos

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 SIMILAR TO '\-[[:DIGIT:]]*.?[[:DIGIT:]]*' ESCAPE '\'

Para que la condición sea válida todos los números deben ser negativos.

Caso 5. Cuando algunos números pueden ser negativos

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 SIMILAR TO '\-?[[:DIGIT:]]*.?[[:DIGIT:]]*' ESCAPE '\'

Este es un caso mucho más común que el anterior. Aquí, se aceptan tanto números positivos como números negativos

Caso 6. Cuando quieres comprobar lo contrario a los casos anteriores

¿Y qué haríamos cuando lo que queremos comprobar no es alguno de los casos anteriores sino su contrario? Por ejemplo queremos ver todas las filas que no tienen un contenido numérico. Pues es muy fácil, simplemente escribimos la palabra NOT antes del predicado SIMILAR TO.

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla
WHERE
   MiColumna1 NOT SIMILAR TO '[[:DIGIT:]]*'

Aquí, veremos todas las columnas que no están compuestas exclusivamente por números enteros. O sea que esta consulta es la inversa a la del Caso 1. De forma parecida podríamos hacer para los otros casos, es decir escribiendo la palabra NOT delante de SIMILAR TO.

Conclusión:

El predicado de comparación SIMILAR TO es muy poderoso y nos permite comprobar que el contenido de una columna de tipo CHAR o VARCHAR sea lo que deseamos.

Artículos relacionados:

Los predicados de comparación
Usando SIMILAR TO
Validando un e-mail
El índice del blog Firebird21
El foro del blog Firebird21

Enterprise Architect – Modelado de Bases de Datos al más alto nivel

1 comentario

Crear una Base de Datos compleja es una tarea que nos tomará mucho tiempo completar y siempre estaremos propensos a cometer errores.

Con las bases de datos sencillas no hay problema, pero el tema es cuando la que debemos crear tendrá cientos de tablas, cientos o miles de triggers, miles de stored procedures, miles de vistas, etc.

La probabilidad de cometer errores en estos casos es muy alta, muchas veces los detectamos tarde, y eso tiene un gran costo en tiempo y en dinero que debemos emplear en corregirlos.

¿Qué hacemos en ese caso?

Pues una muy buena decisión es usar un programa que nos ayude con la tarea desde el principio, así será más difícil que cometamos errores. Y dentro de los programas de modelado uno que resalta por su potencia, rapidez, y facilidad de uso es Enterprise Architect. Y además funciona con Firebird.

El programa no es gratis, pero tiene una versión de prueba (free trial) que nos permitirá conocer como funciona y descubrir todas sus bondades.

Capturas de pantalla

A continuación, algunas capturas de pantalla del programa, para tener una idea de como es visualmente:

EA01

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

EA02

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

EA03

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

Descarga:

Puede ser cargado desde este enlace:

Enterprise Architect

Documentación:

El programa cuenta con una documentación excelente, aunque lastimosamente está toda en inglés. Hay un montón de webinarios, vídeos y tutoriales. ¿Por qué tantos? Porque el programa tiene demasiadas opciones y se necesita mirar mucha de su documentación para sacarle verdadero provecho, pero vale la pena hacerlo.

Webinarios

Vídeos

Tutoriales

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Firebird 3 y Firebird 4

2 comentarios

En este año 2015 está previsto que Firebird 3 sea liberado. ¿Y luego? pues tendremos a Firebird 4, el cual se prevé que sea liberado en el año 2016.

Ambas versiones harán a Firebird muchísimo más poderoso de lo que ya es.

Si quieres saber lo que está previsto que tengan ambas versiones, puedes leer el siguiente documento (en inglés):

Firebird – Lo que está planificado

Y si quieres escuchar una explicación (en inglés, pero con subtítulos en portugués) del documento anterior, puedes ingresar a:

Firebird – Donde estamos, a donde vamos

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries