Entendiendo los índices de expresiones

Los índices sirven para dos cosas:

1. Mostrar el resultado de los SELECTs ordenados por el criterio que nos interesa
2. Realizar búsquedas o filtros, para que solamente sean afectadas las filas que cumplan con la condición que establecimos

Lo más común es que los índices estén compuestos por una o más columnas en forma directa. Veamos un ejemplo:

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

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

Tenemos una tabla ALUMNOS y para ordenar a los alumnos por APELLIDOS y por NOMBRES podríamos crear un índice como el siguiente:

Listado 1.

   CREATE INDEX IDX_ALUMNOS ON ALUMNOS(ALU_APELLD, ALU_NOMBRE);

Y está muy bien, funcionará perfectamente.

Podríamos escribir entonces un SELECT como el siguiente:

Listado 2.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_APELLD,
   ALU_NOMBRE

Y así obtendríamos un resultado como este:

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

Donde como puedes observar, los resultados aparecen ordenados por ALU_APELLD. Pero si queremos saber la diferencia entre ALU_TOTANO y ALU_TOTCOB no es posible usar un índice normal.

¿Y entonces?

Entonces la solución es crear un índice de expresión.

¿Qué es un índice de expresión?

Un índice en el cual se utiliza una expresión aritmética o una expresión alfanumérica o funciones internas.

Ejemplos de índices de expresión:

Listado 3.

   CREATE INDEX IDX_ALUMNOS2 ON ALUMNOS COMPUTED BY (LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1));

   CREATE INDEX IDX_ALUMNOS3 ON ALUMNOS COMPUTED BY (ALU_TOTANO - ALU_TOTCOB);

Como puedes ver, la diferencia entre el índice creado en el Listado 1. y los índices creados en el Listado 3., es que en estos últimos se escribieron las palabras COMPUTED BY y también se usó la función LEFT() en IDX_ALUMNOS2 y una operación aritmética de resta en IDX_ALUMNOS3.

En todos los índices de expresión se deben escribir las palabras COMPUTED BY, tal como vimos en el Listado 3.

Usando índices de expresión

Algo muy importante a recordar es que cuando usamos índices de expresión debemos usarlos exactamente igual a como los definimos.

Listado 4.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1) = 'KM'

En este caso el Firebird usará el índice IDX_ALUMNOS2 porque la expresión escrita en la cláusula WHERE es la misma expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 5.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_NOMBRE, 1) || LEFT(ALU_APELLD, 1) = 'MK'

En el SELECT del Listado 5. el Firebird no usará el índice IDX_ALUMNOS2 porque la condición escrita en la cláusula WHERE no es igual a la expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 6.

SELECT
   *
FROM
   ALUMNOS
WHERE
   ALU_TOTANO - ALU_TOTCOB > 1000

En el SELECT del Listado 6. el Firebird usará el índice IDX_ALUMNOS3 porque la condición escrita en la cláusula WHERE es la misma expresión escrita en la definición del índice IDX_ALUMNOS3.

Listado 7.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_TOTANO - ALU_TOTCOB

En el SELECT del Listado 7. también se usará el índice IDX_ALUMNOS3 porque la expresión escrita en la cláusula ORDER BY es la misma expresión que se usó en la definición del índice IDX_ALUMNOS3.

Conclusión:

Los índices de expresión pueden ser muy útiles en algunos casos, es bueno saber que contamos con esta herramienta para poder usarla cuando nos haga falta.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Validando un e-mail

A veces necesitamos verificar que el e-mail que el usuario introdujo sea un e-mail válido, es decir que cumpla con las reglas para ser usado como tal.

¿Cómo lo validamos?

Bien, hay varias formas de hacerlo pero probablemente la más inteligente sea usando expresiones regulares.  Mediante una expresión regular comparamos un string cualquiera con un patrón de caracteres. El resultado de esa comparación puede ser verdadero o falso, desde luego.

Entonces, para validar un e-mail usando expresiones regulares podríamos escribir algo como:

SELECT
   IIF('minombre@midominio.com' SIMILAR TO '[[:ALNUM:]-_.]*@[[:ALNUM:]-_.]*', 'válido', 'no válido')
FROM
   RDB$DATABASE

El e-mail que queremos validar es minombre@midominio.com y para ello en el SELECT usamos SIMILAR TO. Al usar SIMILAR TO le estamos diciendo al Firebird que usaremos una expresión regular.

¿Y qué significa todo lo que está a continuación de SIMILAR TO?

:ALNUM: es la abreviación de alfanumérico, es decir que incluye todos los caracteres desde la ‘a’ hasta la ‘z’, desde la ‘A’ hasta la ‘Z’ y desde el ‘0’ hasta el ‘9’.

* significa que el caracter precedente puede encontrarse 0 ó muchas veces

@ significa que sí o sí debe existir el carácter @ en esa posición

[] significa que es una clase, o sea cualquiera de los miembros de esa clase pueden existir

Entonces, en castellano lo que dice esa expresión regular es: «Cualquier carácter alfanumérico, el guión, el guión bajo y el punto, pueden repetirse entre cero y muchas veces. El símbolo de arroba debe existir, es obligatorio. Y luego cualquier carácter alfanumérico, el guión, el guión bajo y el punto, pueden repetirse entre cero y muchas veces.»

Entonces, ahora nos falta verificar que realmente con esa expresión regular podemos validar e-mails, así que probamos con varios de ellos, como:

‘minombre@midominio.com’

‘MiNombre@MiDominio.com’

‘MiNombre123@MiDominio.com’

‘Mi_Nombre_123@Mi_Dominio.com’

‘Mi_Nombre_123@Mi_Dominio.com.py’

Y vemos que con todos ellos funciona perfectamente. Así que ahora probamos con e-mails que sabemos inválidos, como los siguientes:

‘minombremidominio’

‘minombremidominio.com’

‘.com’

Y vemos que efectivamente son rechazados.

Bien, la validación no es perfecta ya que acepta los siguientes e-mails:

‘@com’

‘@.com’

Pero de todas maneras es de una gran ayuda y simple de escribir. Hay varias formas de validar que no empiece con @, una de ellas es la siguiente:

SELECT
   IIF('@com' SIMILAR TO '[[:ALNUM:]-_.]*@[[:ALNUM:]-_.]*' and
       SUBSTRING('@com' FROM 1 FOR 1) <> '@', 'válida', 'no válida')
FROM
   RDB$DATABASE

Donde lo que hacemos es verificar que el primer carácter no sea una @. Desde luego que en general no estarás validando contra una constante de tipo string sino contra una columna. Por ejemplo para usarlo en un trigger podrías escribir algo como:

CREATE EXCEPTION E_EMAIL_NO_VALIDO 'El e-mail no es válido, verifícalo.';

CREATE TRIGGER BIU_CLIENTES FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
   DECLARE VARIABLE lcEmailOK CHAR(1);
BEGIN

   lcEmailOK = (SELECT
                   IIF(NEW.CLI_EMAILX
                   SIMILAR TO '[[:ALNUM:]-_.]*@[[:ALNUM:]-_.]*' AND
                   SUBSTRING(NEW.CLI_EMAILX FROM 1 FOR 1) <> '@', 'T', 'F')
                FROM
                   RDB$DATABASE);

   IF (lcEmailOK = 'F') THEN
      EXCEPTION E_EMAIL_NO_VALIDO;

END;

La gran mayoría de los usuarios si escriben mal un e-mail es porque se equivocaron al hacerlo, no por maldad sino por una equivocación humana. Para detectar algunas de esas equivocaciones podríamos mejorar un poco el trigger anterior, que ahora quedaría así:

CREATE TRIGGER BIU_CLIENTES FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 0
AS
   DECLARE VARIABLE lcEmailOK CHAR(1);
BEGIN

   lcEmailOK = (SELECT
                   IIF(NEW.CLI_EMAILX
                   SIMILAR TO '[[:ALNUM:]-_.]*@[[:ALNUM:]-_.]*' AND
                   SUBSTRING(NEW.CLI_EMAILX FROM 1 FOR 1) <> '@', 'T', 'F')
                FROM
                   RDB$DATABASE);
   
   IF (lcEmailOK = 'T') THEN
      lcEmailOK = IIF(NEW.CLI_EMAILX CONTAINING '--', 'F', lcEmailOK);
   
   IF (lcEmailOK = 'T') THEN
      lcEmailOK = IIF(NEW.CLI_EMAILX CONTAINING '__', 'F', lcEmailOK);
   
   IF (lcEmailOK = 'T') THEN
      lcEmailOK = IIF(NEW.CLI_EMAILX CONTAINING '..', 'F', lcEmailOK);
   
   IF (lcEmailOK = 'F') THEN
      EXCEPTION E_EMAIL_NO_VALIDO;

END;

Donde lo que hacemos es verificar que no haya escrito dos guiones seguidos, dos guiones bajos seguidos, o dos puntos seguidos. Ninguna de esas posibilidades es normalmente usada en un e-mail, así que las rechazamos. Desde luego que podrías agregar otras comparaciones si te parecen necesarias.

Y de esta manera siempre que el usuario esté intentando insertar o actualizar los datos de un cliente se verificará que el e-mail de ese cliente sea un e-mail que cumple con las reglas mínimas para ser admitido como tal. Si no las cumple entonces se lanzará una excepción informándole del problema.

Artículos relacionados:

Los predicados de comparación

Usando SIMILAR TO

El índice del blog Firebird21

El foro del blog Firebird21

 

Columnas computadas

Firebird además de las columnas normales nos permite tener columnas computadas.

¿Qué es una columna computada?

Una columna cuyo contenido depende de otras columnas, previamente definidas.

¿Para qué se usan las columnas computadas?

Para poder referenciar a los datos de varias formas, o sea que los datos pueden encontrarse en sus columnas originales o en las columnas computadas. También para escribir fórmulas que estén siempre accesibles.

¿Cómo se declara una columna computada?

Con la cláusula COMPUTED BY

Ejemplo:

Tenemos una tabla llamada ALUMNOS que tiene estas columnas (y varias más, pero que ahora no nos interesan):

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL) ;

El dominio D_IDENTIFICADOR es un INTEGER y el dominio D_NOMBRE25 es un VARCHAR(25).

En la columna ALU_IDENTI se guarda el identificador del alumno, en ALU_NOMBRE sus nombres y en ALU_APELLD sus apellidos.

Pero en los informes queremos que aparezcan primero los apellidos, luego una coma, luego un espacio en blanco, y luego los nombres. Claro que podríamos conseguirlo realizando las concatenaciones correspondientes en cada caso pero lo más sencillo es tener una columna computada, como la siguiente:

ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)

Entonces, la estructura de la tabla ALUMNOS quedaría así:

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL,
   ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)) ;

Si usas EMS SQL Manager, le dices que quieres agregar una columna a la tabla, como siempre lo haces, pero ahora eliges la opción «COMPUTED BY expression» y escribes la expresión que deseas:

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

 Si ahora escribimos un SELECT para ver los datos de los alumnos obtendremos algo similar a esto:

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

Fíjate en la columna ALU_APENOM, tiene los mismos datos que las columnas ALU_NOMBRE y ALU_APELLD, pero concatenados de la forma que determinamos anteriormente.

¿Cómo se cambia el valor de una columna computada?

La única forma es cambiando los valores de sus columnas base (en este ejemplo, de ALU_NOMBRE y de ALU_APELLD), si intentas algo como esto:

UPDATE
   ALUMNOS
SET
   ALU_APENOM = 'PRUEBA DE CAMBIO DE NOMBRE'
WHERE
   ALU_IDENTI = 1200

El Firebird se enojará contigo y te mostrará el mensaje:

«This column cannot be updated because it is derived from an SQL function or expression.Attempted update of read-only column.«

O sea que la columna no puede ser actualizada porque es derivada de una función o expresión SQL. Intentaste actualizar una columna que es read-only (sólo lectura).

¿Dónde se usa una columna computada?

• En el comando SELECT para ver sus valores, para poner una condición de filtro (cláusula WHERE) o para mostrar a los datos ordenados (cláusula ORDER BY).
• En el comando UPDATE y en el comando DELETE, cuando se coloca una condición de filtro (cláusula WHERE)

No se puede insertar un valor, ni actualizar un valor, ya que las columnas computadas obtienen sus valores de columnas previamente definidas.

SELECT
   ALU_APENOM
FROM
   ALUMNOS

Sí se puede (desde Firebird 2.0) indexar a una columna computada, pero hay que hacerlo de esta forma:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE);

Ya que si se intenta así:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS ALU_APENOM

Se obtendrá un mensaje de error similar al siguiente:

Unsuccessful metadata update.
Attempt to index COMPUTED BY column in INDEX IDX_ALUMNOS1.

SQL Code: -607
IB Error Number: 335544351

El mensaje dice que la actualización de los metadatos no tuvo éxito, porque se intentó indexar una columna computada.

SELECT
   ALU_APENOM
FROM
   ALUMNOS
ORDER BY
   ALU_APENOM

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

¿Se puede cambiar una columna computada?

Se puede cambiar el tipo de datos y la expresión. No se puede cambiar una columna base en una columna computada o viceversa.

¿Las columnas computadas se usan solamente para concatenar columnas alfanuméricas?

No, también pueden usarse con columnas numéricas. Supongamos que tenemos una tabla de PRODUCTOS y deseamos saber cual es nuestro porcentaje de ganancia sobre el precio de costo, entonces podríamos escribir algo como esto:

PRD_PORCEN COMPUTED BY (PRD_PREVTA * 100 / PRD_PRECTO)

Donde PRD_PREVTA es el precio de venta del producto y PRD_PRECTO es el precio de costo del producto. En la columna PRD_PORCEN siempre tendremos los porcentajes de ganancia.

Desde luego que podríamos escribir cualquier otra fórmula matemática, no solamente esa.

Conclusión:

Las columnas computadas son muy útiles para que escribamos menos y podamos visualizar los datos en varias formas distintas, además las podemos usar con las cláusulas WHERE y ORDER BY.

No se puede indexar a una columna computada, pero sí se puede indexar las columnas que la componen.

Artículos relacionados:

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un SELECT en una columna computada

Indexando una columna computada

El índice del blog Firebird21

El foro del blog Firebird21

Usando subconsultas en expresiones

Antes de la versión 2.5 del Firebird no se podían usar subconsultas en expresiones aunque dichas subconsultas retornaran un solo valor. Esto nos obligaba a usar la construcción SELECT … INTO

Ahora, estas asignaciones ya son posibles dentro de un stored procedure, un trigger o un execute block:

MiVar = (SELECT … FROM …);

IF ((SELECT … FROM …) = 1) THEN …

IF(25 = ANY(SELECT … FROM …)) THEN …

IF(2104 IN (SELECT … FROM …)) THEN …

Por supuesto que en los dos primeros ejemplos el SELECT debe retornar una sola fila.

lcNomCli = (SELECT CLI_NOMBRE FROM CLIENTES WHERE CLI_IDENTI = 11111);     /* Nombre del cliente */

lnPreVta = (SELECT PRD_PREVTA FROM PRODUCTOS WHERE PRD_IDENTI = 12345);     /* Precio de venta del producto */

Fíjate que debes rodear al SELECT con paréntesis o recibirás un mensaje de error.