Solamente llamar a vistas y a stored procedures

4 comentarios

Una característica que siempre se ve en el código fuente de las aplicaciones bien escritas es que solamente llaman a vistas y a stored procedures.

Y a fuer de ser sinceros, tales aplicaciones son escasas, muy escasas.

Muchos programadores escriben en el código fuente de sus aplicaciones los comandos INSERT, UPDATE, DELETE, y SELECT a columnas de tablas.

Y eso está mal, muy mal.

¿Por qué?

Bueno, hay varios motivos. El autor de este blog varias veces ha evaluado aplicaciones y códigos fuentes escritos por otras personas y al finalizar la evaluación ha elevado un informe detallando lo bueno y lo malo que encontró. Y en el capítulo “Comunicación con la Base de Datos” estos son algunos problemas:

  1. El código fuente es desprolijo. Si alguien escribe los comandos mencionados más arriba en general lo hace de forma desordenada, calculando valores de variables, o realizando otras tareas no relacionadas, y eso además de no ser eficiente queda “feo”.
  2. No permite el trabajo en equipo. Cuando se trabaja en equipo lo ideal es modularizar al máximo para que cada función o cada rutina sea escrita una sola vez y utilizada por muchos programadores. De esa manera si hay algún error solamente puede estar en un lugar y será fácil encontrarlo y corregirlo. Pero si cada quien escribe los comandos en su propio código fuente entonces los errores pueden estar en muchos lugares y encontrarlos y corregirlos demorará mucho más tiempo. Y cualquier cambio a cualquiera de los códigos fuente puede introducir un nuevo error. Y esto inclusive puede ocurrir aunque se trate de un solo programador pero que tiene por ejemplo el INSERT a una sola tabla en dos o más programas.
  3. No hay un responsable de la Base de Datos. Cuando se trabaja en equipo una persona debe ser la encargada de verificar el correcto diseño y funcionamiento de la Base de Datos. Si cada programador escribe lo que se le ocurre en su propio código fuente, tales verificaciones serán imposibles de realizar y los errores posibles, muchísimos.
  4. No se puede verificar que el comando finalizará sin error hasta el momento de ser ejecutado. Por ejemplo, mirando un INSERT en el código fuente no se puede tener la seguridad de que esa fila será insertada o que ocurrirá una excepción de “table unknown”, “column unknown” o alguna otra.
  5. No se puede comprobar la eficiencia del comando. Este es el punto más importante. Los programas de administración gráfica, como el EMS SQL Manager, nos muestran la cantidad de filas leídas, la cantidad de filas extraídas, y en forma gráfica los índices usados. También podemos ver el PLAN utilizado. Así es fácil descubrir que no se está usando un índice cuando sí debería usarse, o que las filas leídas son demasiadas, o que se está usando el índice incorrecto, etc. Y es muy fácil y muy rápido cambiar la vista o el stored procedure con la intención de hacerlo más eficiente. Sin embargo, si escribimos los comandos dentro del código fuente de una aplicación es imposible saber si es eficiente o no lo es. Podemos “creer” que es eficiente y que usa los índices correctos, cuando en realidad no es así. Y hay además otro problema: aunque hoy una vista o un stored procedure sean muy eficientes, podrían dejar de serlo dentro de unos meses cuando las tablas tengan más filas o se hayan cambiado o borrado algunas filas. Escribiendo los comandos INSERT, UPDATE, DELETE, y SELECT a columnas de tablas dentro del código fuente de nuestras aplicaciones jamás podremos estar seguros de que son los más eficientes que podemos tener, en cambio si dichos comandos están dentro de una vista o dentro de un stored procedure sí que podremos tener esa seguridad.

VISTAS-STORED-1

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

VISTAS-STORED-2

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

Mirando la Captura 1. sabemos que la vista involucra a 3 tablas y que las 3 tablas usan índices (e inclusive sabemos los nombres de esos índices), y que la cantidad de filas parece estar correcta, sin embargo … ese SORT en la Captura 2. nos llama poderosamente la atención porque los SORT son muy lentos, ya que deben ordenar las filas de las tablas; ese ordenamiento se realiza en la memoria del Servidor (cuando tal cosa es posible) o en el disco duro (cuando no puede ordenarse en la memoria). En general, debemos evitar a los SORT como a la peste, porque indican que la tabla, la vista, el stored procedure seleccionable, o los resultados deberán ser ordenados, y eso puede demorar muchísimo tiempo en tablas grandes.

Si en lugar de escribir una vista, como en el ejemplo de arriba, hubiéramos escrito un SELECT en el código fuente de nuestro lenguaje de programación, ¡¡¡jamás nos habríamos percatado de ese SORT malévolo!!!. Y claro, cuando las tablas tuvieran muchas filas los usuarios se quejarían de la lentitud, pero mientras tanto les hicimos perder mucho tiempo innecesariamente porque nuestra consulta no estaba optimizada.

Conclusión:

Si quieres programar correctamente y eficientemente y profesionalmente, en el código fuente de tus aplicaciones solamente debes llamar a vistas y a stored procedures. Nunca, jamás, y por ningún motivo, ejecutar un INSERT, un UPDATE, un DELETE, o un SELECT a columnas de una tabla.

Los SELECT solamente a vistas.

Los INSERT, UPDATE, DELETE, y algunos SELECT, solamente dentro de un stored procedure.

Cuando se programan aplicaciones siempre se van encontrando errores y problemas aquí y allá. Para disminuir esa cantidad de errores y de problemas, y para optimizar las consultas y los procesamientos, siempre lo mejor es desde el código fuente de nuestras aplicaciones llamar a vistas y a stored procedures, y a nada más.

Artículos relacionados:

Optimizando las consultas

Optimizando las subconsultas

Optimizando los JOIN

Evitando que el optimizador … optimice

¿Por qué usar stored procedures?

Usando un stored procedure como una función

Escribiendo un stored procedure

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

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

Descubriendo valores incorrectos en nuestras tablas

Deja un comentario

Siempre debemos verificar que en nuestras tablas tengamos valores correctos y solamente valores correctos porque cualquier valor incorrecto es basura y la basura jamás puede ser buena.

Si una columna tiene una restricción Foreign Key entonces si la tabla padre tiene valores correctos podemos estar seguros que la tabla hija también los tendrá. Pero … ¿y si la columna no tiene una restricción Foreign Key?

Supongamos que tenemos una columna para conocer el sexo de una persona, ‘F’=femenino, ‘M’=masculino. ¿Cómo podríamos verificar que tengamos solamente esos valores y que no tengamos ‘m’, ‘f’, ‘1’, ‘0’, o cualquier otra cosa?

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna NOT IN ('F', 'M')

En nuestra tabla cabecera de movimientos tenemos una columna llamada MVC_TIPMOV que nos indica cual es el tipo de movimiento. ‘ECM’ = entrada por compras, ‘SVT’ = salida por ventas, ‘EDV’ = entrada por devolución, ‘SDV’ = salida por devolución, ‘COB’ = cobranza, ‘PAG’ = pago. ¿Cómo podemos estar seguros que la columna MVC_TIPMOV tenga solamente uno de esos valores?

SELECT
   *
FROM
   MOVIMCAB
WHERE
   MVC_TIPMOV NOT IN ('ECM', 'SVT', 'EDV', 'SDV', 'COB', 'PAG')

¿Y para saber si algún movimiento ocurrió en un año distinto a 2011, 2012, 2013, que son los únicos años permitidos?

SELECT
   *
FROM
   MOVIMCAB
WHERE
   EXTRACT(YEAR FROM MVC_FECHAX) NOT IN (2011, 2012, 2013)

Conclusión:

Siempre debemos verificar que en nuestras tablas tengamos valores correctos y solamente valores correctos porque los valores incorrectos jamás servirán para algo bueno. Podemos hacer esa verificación con NOT IN.

Inclusive sería muy bueno que en nuestros programas agreguemos una opción que le permita a los usuarios realizar esta verificación. Si la verificación finaliza sin errores entonces podemos asegurarle que no tiene basura en sus tablas. Y si al verificar se encuentran errores entonces por supuesto que se debe corregirlos inmediatamente.

Artículo relacionado:

El índice del blog Firebird21

Usando la restrición CHECK

7 comentarios

Para evitar que ingrese basura en nuestra tabla (se le llama basura a un dato que está pero que no debería estar) el Firebird nos provee de varias armas: dominios, triggers y checks.

Un check es una restricción, una limitación que deben cumplir los datos para que sean considerados válidos y puedan ser grabados.

Por ejemplo, si los precios no pueden ser negativos podríamos tener un check que evite guardar precios negativos. Si las notas de los alumnos deben estar entre 0 y 100 podemos tener un check que evite ingresar notas fuera de ese rango. Si las fechas no pueden ser anteriores al día 1 de enero de 2013 podemos tener un check que evite ingresar fechas anteriores.

Todos esos ejemplos involucran a una sola tabla cada vez, pero el Firebird también nos permite tener checks que involucren a varias tablas. Los usaríamos por ejemplo para evitar que la cobranza se realice antes de la venta, o que el examen se realice antes de la inscripción del alumno, o que el cheque sea emitido antes de la apertura de la cuenta corriente en el Banco.

Después de haber creado una tabla le podemos agregar todos los checks que necesitemos. La sintaxis es la siguiente:

ALTER TABLE MiTabla ADD CONSTRAINT NombreCheck CHECK (MiCondición);

En EMS SQL Manager para escribir un check debes hacer click sobre la pestaña respectiva, como se muestra en esta imagen:

CHECK1

(haciendo click en la imagen la verás más grande)

Ejemplo 1. Evitar precios de costo negativos

ALTER TABLE
   PRODUCTOS
ADD CONSTRAINT
   CHK_PRODUCTOS1
CHECK (PRD_PRECTO >= 0);

La tabla se llama PRODUCTOS y en la columna PRD_PRECTO se guardan los precios de costo de los productos

Ejemplo 2. Evitar que las notas de los alumnos estén fuera de rango

ALTER TABLE
   EXAMENES
ADD CONSTRAINT
   CHK_EXAMENES1
CHECK(EXA_NOTAXX >= 0 AND EXA_NOTAXX <= 100);

La tabla se llama EXAMENES y en la columna EXA_NOTAXX se guardan las notas de los alumnos

Ejemplo 3. Evitar que la fecha de la venta sea anterior al 1 de enero de 2013

ALTER TABLE
   VENTASCAB
ADD CONSTRAINT
   CHK_VENTASCAB1
CHECK (VTC_FECHAX >= '01-01-2013');

La tabla se llama VENTASCAB (cabecera de las ventas) y en la columna VTC_FECHAX se guardan las fechas de las ventas

Ejemplo 4. Evitar la grabación si el valor de una columna no existe en otra tabla

ALTER TABLE
   BANCOS
ADD CONSTRAINT
   CHK_BANCOS1
CHECK (BAN_CODSUC IN (SELECT SUC_CODIGO FROM SUCURSALES));

La tabla se llama BANCOS y en la columna BAN_CODSUC se guarda el código de la Sucursal. Se busca ese código en la tabla de SUCURSALES. Si no existe, entonces esta fila no podrá ser grabada. Esta no es la única forma de resolver este problema, pero se muestra aquí para que puedas ver como usarla cuando necesites que una fila se grabe solamente si existe un valor (o más de un valor) en otra tabla.

Conclusión:

La restricción CHECK es extremadamente útil para evitar que alguien ingrese basura en las tablas de nuestra Base de Datos. Si la usamos bien entonces no estaremos dependiendo de que los programas (escritos en Visual FoxPro, Visual Basic, C, C++, Delphi, Java, PHP, etc.) se acuerden de evitar el ingreso de la basura. Simplemente si un dato no cumple con la restricción del check no será grabado. Punto.

En los ejemplos de arriba se mostró como evitar algunas situaciones comunes pero desde luego que hay muchísimas otras posibilidades. Como el Firebird nos permite tener un SELECT (o más de un SELECT) dentro de un CHECK entonces la cantidad de condiciones que podemos escribir es impresionante, y deberíamos usar esta facilidad para que nuestra Base de Datos sea confiable.