Usando vistas para insertar, actualizar, y borrar filas

4 comentarios

Probablemente ya has usado vistas para consultar datos y si no lo has hecho seguro que pronto lo harás, ya que eso es lo correcto. Tener las consultas precompiladas aumenta la velocidad de respuesta.

Sin embargo, ¿sabías que también puedes usar las vistas para insertar, actualizar, o borrar filas?

En un entorno multiusuario, donde muchas personas pueden estar al mismo tiempo conectadas a una Base de Datos es importantísimo limitar lo que cada una de esas personas puede hacer. De lo contrario, alguien podría estar haciendo algo que debería tener prohibido. Por ejemplo, un vendedor no debería tener la posibilidad de cambiar el precio de venta de un producto porque eso se presta a corrupción. Ni tampoco poder cambiar el porcentaje de comisión que cobra por cada venta.

Si necesitas que un usuario pueda insertar, actualizar, o borrar filas de una tabla puedes hacerlo de dos formas:

  1. Otorgándole derechos sobre esa tabla
  2. Otorgándole derechos sobre una vista, la cual se encargará de realizar las operaciones sobre dicha tabla

El problema de hacerlo con la primera forma es que el usuario podrá también ver y cambiar columnas que no debería poder ver o cambiar. Si le das el derecho de consulta sobre la tabla PRODUCTOS hace un SELECT * FROM PRODUCTOS y ve todas las filas y todas las columnas de esa tabla.

Mediante la segunda forma, en cambio, puedes controlar muchísimo mejor lo que puede y lo que no puede hacer. Si le das el derecho de consulta sobre la vista V_PRODUCTOS  la cual contiene un SELECT PRD_CODIGO, PRD_NOMBRE FROM PRODUCTOS, entonces solamente podrá ver el código y el nombre de cada producto y nada más.

Desde el punto de vista del Firebird puedes tratar a una vista como si fuera un usuario. Es decir que a una vista puedes otorgarle derechos (privilegios, permisos) sobre una tabla.

Y a los usuarios humanos les otorgas derechos sobre la vista, no sobre la tabla.

Por lo tanto, en lugar de escribir esto:

GRANT ALL ON PRODUCTOS TO ALICIA

GRANT ALL ON PRODUCTOS TO CLAUDIA

GRANT ALL ON PRODUCTOS TO MIRTHA

Sería mucho mejor que escribas esto:

GRANT ALL ON PRODUCTOS TO V_PRODUCTOS

GRANT ALL ON V_PRODUCTOS TO ALICIA

GRANT ALL ON V_PRODUCTOS TO CLAUDIA

GRANT ALL ON V_PRODUCTOS TO MIRTHA

Y fíjate en la primera línea, allí se trata a la vista V_PRODUCTOS como si fuera una persona, ¡¡¡se le están otorgando derechos sobre una tabla!!!. Los derechos se le otorgan a la vista, no a una persona.

¿Qué significa todo esto?

Que ni ALICIA, ni CLAUDIA, ni MIRTHA podrán hacer algo con la tabla PRODUCTOS, no tienen derechos ahí, ni siquiera para consultarla. Pero sí tienen derechos sobre la vista V_PRODUCTOS y por lo tanto estarán limitadas a lo que la vista haga.

Hasta ahí todo bien pero ¿cómo hago para insertar, actualizar, o borrar filas usando vistas?

Muy simple, las vistas también tienen triggers. Entonces es en esos triggers donde escribirás el código que insertará, actualizará, o borrará filas de la/s tabla/s subyacentes. Aunque en realidad no es obligatorio que uses triggers pero sí es conveniente porque te da más control.

VISTAS1

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

VISTAS2

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

VISTAS3

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

Como puedes ver en la Captura 3, esta vista solamente nos permite insertar o actualizar las columnas PRD_IDENTI, PRD_CODIGO, y PRD_NOMBRE, pero no podemos hacer lo mismo con las columnas PRD_PRECTO y PRD_PREVTA. ¿Por qué no? porque en el trigger solamente podemos usar las columnas que pertenecen a la vista. Ni la columna PRD_PRECTO ni la columna PRD_PREVTA pertenecen a la vista, por lo tanto no las podemos usar en el trigger. Si lo intentas, obtendrás un mensaje de error.

En otras palabras, si a un usuario le otorgamos derechos sobre la vista V_PRODUCTOS él solamente podrá ver, insertar, y actualizar las columnas PRD_IDENTI, PRD_CODIGO, y PRD_NOMBRE, y ninguna columna más, porque ninguna otra columna pertenece a la vista.

Por lo tanto, si necesitamos que un usuario pueda acceder a las columnas PRD_PRECTO y PRD_PREVTA entonces deberemos crear otra vista, que le otorgue acceso a esas columnas.

¿Pero hacerlo así no nos obliga a trabajar más?

Pues sí, pero no puedes tenerlo todo fácil en la vida. Se trabaja más pero en contrapartida tu aplicación será mucho más segura y mucho más confiable. Si cualquiera puede meter las pezuñas en tus tablas eso tarde o temprano te provocará grandes problemas. Así que mejor evitarlos desde el principio.

UPDATE OR INSERT INTO V_PRODUCTOS
         (PRD_IDENTI, PRD_CODIGO, PRD_NOMBRE)
  VALUES (0         , '1234'    , 'Producto xxx');

Como puedes ver en el código fuente de arriba, la inserción, actualización, o borrado se hace de la forma normal, las diferencias son:

  1. Se escribe el nombre de una vista, no el nombre de una tabla
  2. Solamente se pueden usar las columnas que pertenecen a la vista

Conclusión:

Usar vistas para realizar todas las operaciones de manipulación de tablas (inserción, actualización, borrado, consulta) es lo mejor, es lo correcto, es lo ideal. ¿Por qué? porque eso nos da un control total, 100%, sobre lo que cada usuario puede hacer o no hacer.

De esta manera los usuarios nunca, y por ningún motivo, acceden directamente a las tablas. Todos los accesos lo hacen a través de las vistas. Y en nuestras vistas podemos usar la cláusula WHERE para establecer condiciones. Por lo tanto, solamente podrán hacer lo que específicamente queremos que puedan hacer. Y nada más.

Artículo relacionado:

El índice del blog Firebird21

 

Una vista para verificar la transacción actual

Deja un comentario

A veces queremos saber cuales son las características de la transacción actual ¿es “read only”? ¿tiene acceso exclusivo a las tablas? si hay un conflicto al actualizar o borrar datos ¿espera o inmediatamente sale con error?, etc.

La siguiente vista nos dará esa información:

SELECT
   MON$TRANSACTION_ID             AS tnIdentificadorTransaccion,
   MON$ATTACHMENT_ID              AS tnIdentificadorConexion,
   IIF(MON$STATE = 1, 'ACTIVA', 'INACTIVA') AS tcEstadoTransaccion,
   MON$TIMESTAMP                  AS tdFechaHora,
   MON$TOP_TRANSACTION            AS tnUltimaTransaccion,
   MON$OLDEST_TRANSACTION         AS tnOldestInterestingTransaction,
   MON$OLDEST_ACTIVE              AS tnOldestActiveTransaction,
   DECODE(MON$ISOLATION_MODE, 0, 'Acceso exclusivo', 1, 'Lecturas repetidas', 2, 'Lee filas confirmadas inmediatamente', 3, 'No lee una fila si otra transacción la usa') AS tcModoAislamiento,
   DECODE(MON$LOCK_TIMEOUT, -1, 'Espera por siempre', 0, 'No espera', 'Espera ' || MON$LOCK_TIMEOUT || ' segundos') AS tcTiempoEspera,
   IIF(MON$READ_ONLY = 1, 'Read Only', 'Read Write')               AS tcReadOnly,
   IIF(MON$AUTO_COMMIT = 1, 'Auto COMMIT', 'No auto COMMIT')       AS tcAutoCommit,
   IIF(MON$AUTO_UNDO = 1, 'Tiene savepoint', 'No tiene savepoint') AS tcAutoUndo,
   MON$STAT_ID                                                     AS tnIdentificadorEstadisticas
FROM
   MON$TRANSACTIONS
WHERE
   MON$ATTACHMENT_ID  = CURRENT_CONNECTION AND
   MON$TRANSACTION_ID = CURRENT_TRANSACTION;

Si la transacción es SNAPSHOT TABLE STABILITY entonces tiene acceso exclusivo a todas las tablas (algo no recomendable en Firebird)

Si la transacción es SNAPSHOT entonces las lecturas pueden repetirse muchas veces y siempre se obtendrán los mismos resultados. Suele ser la más aconsejable para las consultas (SELECT, FETCH)

Si la transacción es READ COMMITED RECORD_VERSION entonces la última versión confirmada del registro es leída inmediatamente. Es la opción por defecto. Suele ser la más aconsejable para mantenimiento de los datos (INSERT, UPDATE, DELETE)

Si la transacción es READ COMMITED NO RECORD_VERSION y otra transacción tiene una versión no confirmada del registro, la transacción actual espera hasta que la otra transacción termine con un COMMIT o con un ROLLBACK (si el modo de bloqueo es WAIT) o termina inmediatamente con error (si el modo de bloqueo es NO WAIT). Estas transacciones pueden causar muchos “deadlock”, por lo tanto hay que pensar muy bien si es el aislamiento correcto.

Es aconsejable que la consulta anterior se guarde como una vista entonces cuando quieras saber las características de la transacción actual simplemente escribes:

SELECT * FROM V_TRANSACCION_ACTUAL

y listo, ya podrás saberlas.

Artículos relacionados:

Entendiendo las transacciones

Modos de bloqueo de las transacciones

El índice del blog Firebird21

Un stored procedure para recrear todas las vistas

Deja un comentario

En ocasiones necesitamos recrear las vistas de nuestra Base de Datos; a veces ocurre que después de modificar la estructura de una tabla cuando queremos ejecutar una vista obtenemos el mensaje de error: “string truncation”.

Como pueden ser varias las vistas afectadas por ese problema, lo más inteligente es tener un stored procedure que se encargue de recrearlas a todas:

CREATE PROCEDURE RECREAR_VISTAS
AS
   DECLARE VARIABLE lcNombreVista VARCHAR(   32);
   DECLARE VARIABLE lcCodigoVista VARCHAR(32765);
   DECLARE VARIABLE lcColumna     VARCHAR(   32);
   DECLARE VARIABLE lcComando     VARCHAR(32765);
   DECLARE VARIABLE lcFila        VARCHAR( 1024);
BEGIN

   -- Para cada vista de la Base de Datos
   FOR
      SELECT
         TRIM(R.RDB$RELATION_NAME),
         R.RDB$VIEW_SOURCE
      FROM
         RDB$RELATIONS R
      WHERE
         R.RDB$VIEW_SOURCE IS NOT NULL
      ORDER BY
         R.RDB$RELATION_NAME
      INTO
         :lcNombreVista,
         :lcCodigoVista
      DO BEGIN
         lcFila    = '';
         lcColumna = '';
         -- Para cada columna de esta vista
         FOR
            SELECT
               TRIM(F.RDB$FIELD_NAME)
            FROM
               RDB$RELATION_FIELDS F
            WHERE
               F.RDB$RELATION_NAME = :lcNombreVista
            ORDER BY
               F.RDB$FIELD_POSITION
            INTO
               :lcColumna
            DO
               IF (lcFila = '') THEN
                  lcFila = ' ' || :lcColumna;
               ELSE
                  lcFila = :lcFila || ', ' || ASCII_CHAR(13) || ASCII_CHAR(10) || ' ' || :lcColumna;
         -- Se modifica la vista
         lcComando = 'CREATE OR ALTER VIEW ' ||
                   :lcNombreVista || '(' ||
                   ASCII_CHAR(13) || ASCII_CHAR(10) ||
                   :lcFila ||
                   ') ' ||
                   ASCII_CHAR(13) || ASCII_CHAR(10) ||
                   'AS ' ||
                   :lcCodigoVista ;
         EXECUTE STATEMENT lcComando;
      END
END;

Este stored procedure se encargará de recrear a todas las vistas de la Base de Datos, asegurándonos por tanto que todas ellas se encuentran en perfectas condiciones.

¿Cómo funciona este stored procedure?

En primer lugar, obtiene el nombre de cada vista y el código fuente de ella.

En segundo lugar, obtiene el nombre de cada columna involucrada en esa vista.

En tercer lugar, guarda todos esos datos en una variable de tipo carácter.

En cuarto y último lugar, ejecuta el comando que recrea la vista.

Artículo relacionado:

El índice del blog Firebird21

 

¿Está la vista optimizada?

4 comentarios

Como seguramente sabes, una vista es una consulta (un SELECT) que se guarda dentro de la Base de Datos con dos objetivos principales:

  1. que las consultas que involucren a las filas y a las columnas de la vista sean más rápidas
  2. escribir menos y por lo tanto que el código fuente sea más legible

Pero no todas las consultas (y por consiguiente, no todas las vistas) están optimizadas.

¿Qué significa que una consulta o una vista está optimizada?

Que se ejecuta lo más rápidamente posible

Las consultas que usan varias tablas siempre se pueden escribir de varias formas distintas. Y siempre hay una forma que es mejor que las demás. En este caso la palabra “mejor” se refiere a la velocidad con la cual devuelve las filas. Si escribiendo la consulta de la forma “X” tarda 5 segundos en mostrar sus resultados y escribiendo otra consulta equivalente “Y” tarda 2 minutos, entonces evidentemente la forma “X” es mejor.

Una de nuestras tareas es conseguir que todas las consultas y todas las vistas estén optimizadas.

¿Cómo se puede saber si una consulta está optimizada?

Hasta que tengas mucha experiencia con el tema quizás debas basarte en “prueba y error” pero hay algo que debes tener en cuenta:

  • En prácticamente la totalidad de las consultas optimizadas se usa un índice en cada tabla involucrada

¿Cómo se puede saber si se está usando un índice en cada tabla involucrada?

En general el Firebird utilizará un índice siempre que el nombre de una columna sea escrito en las cláusulas WHERE, JOIN, UNION u ORDER BY y haya un índice compuesto por esa columna.

La frase anterior significa que si tienes un índice compuesto por CLI_CODSUC y CLI_IDENTI, el Firebird usará el índice si escribes ambas columnas o si escribes la primera columna (en este caso: CLI_CODSUC) pero no lo usará si solamente escribes la segunda columna (en este caso: CLI_IDENTI)

¿Hay una forma gráfica de saber si una vista está usando índices en cada tabla?

Sí, los programas de administración gráfica pueden darte esa información, por ejemplo si usas el EMS SQL Manager y creas la siguiente vista:

CREATE VIEW V_ABM_BANCOS(
   BAN_CODSUC,
   BAN_IDENTI,
   BAN_NOMBRE)
AS
SELECT
   BAN_CODSUC,
   BAN_IDENTI,
   BAN_NOMBRE
FROM
   BANCOS

puedes ver si usa algún índice o no haciendo click en la pestaña “Performance Analysis”, como se muestra a continuación:

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

Lo que veremos en este caso será lo siguiente:

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

Y nos muestra gráficamente que no se está usando un índice.

“Non-indexed reads” significa: “lecturas que no usan un índice”

“Indexed reads” significa: “lecturas que usan un índice”

Esta tabla es muy pequeña, solamente tiene 16 filas así que usar un índice o no será casi lo mismo, pero en tablas grandes que tienen miles o millones de filas la diferencia en el tiempo empleado puede ser grandísima, monstruosa.

¿Cómo se puede cambiar la vista anterior para que utilice un índice?

Agregándole la cláusula WHERE o la cláusula ORDER BY, por ejemplo:

CREATE VIEW V_ABM_BANCOS(
   BAN_CODSUC,
   BAN_IDENTI,
   BAN_NOMBRE)
AS
SELECT
   BAN_CODSUC,
   BAN_IDENTI,
   BAN_NOMBRE
FROM
   BANCOS
WHERE
   BAN_CODSUC >= 0

Si ahora hacemos click en “Performance Analysis” lo que veremos será esto:

VISTAS3

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

o sea que nuestra vista ahora sí está usando un índice.

Aquí hay otro ejemplo de una vista que no está optimizada:

VISTAS4

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

Como puedes ver las lecturas a la tabla SUCURSALES no usan un índice. En la casi totalidad de los casos eso es un error, lo correcto es que sí usen un índice.

Conclusión:

Siempre debemos verificar que nuestras consultas (o sea los SELECT) y nuestras vistas estén optimizadas porque si están optimizadas devolverán los resultados mucho más rápidamente. Para que estén optimizadas deben (prácticamente siempre) utilizar un índice en cada tabla. El programa EMS SQL Manager nos puede mostrar gráficamente si una consulta o una vista usa un índice en cada tabla o no lo hace.

Para obligarle al Firebird a utilizar un índice debemos escribir las columnas de ese índice en las cláusulas WHERE, JOIN, UNION u ORDER BY.

Si el índice que utiliza el Firebird no nos parece el más adecuado podemos indicarle cual índice utilizar con la cláusula PLAN. Más información sobre la cláusula PLAN puedes encontrar en:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

Artículos relacionados:

Usando un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Código fuente de una vista

1 comentario

Si deseas obtener el código fuente de una vista puedes escribir:

SELECT
    RDB$VIEW_SOURCE
FROM
    RDB$RELATIONS
WHERE
    RDB$VIEW_SOURCE IS NOT NULL AND
    RDB$RELATION_NAME = 'V_ABM_ALUMNOS'

donde V_ABM_ALUMNOS es el nombre de la vista.

Nombres de todas las vistas

1 comentario

Si queremos obtener el nombre de todas las vistas (pero no el nombre de las tablas) podemos escribir:

SELECT DISTINCT
    RDB$VIEW_NAME
FROM
    RDB$VIEW_RELATIONS;

Y si queremos obtener el nombre de todas las vistas que están relacionadas con una tabla, podemos escribir:

SELECT DISTINCT
    RDB$VIEW_NAME
FROM
    RDB$VIEW_RELATIONS
WHERE
    RDB$RELATION_NAME='ALUMNOS';

En este ejemplo obtendremos los nombres de todas las vistas que referencian a la tabla ALUMNOS.

 

 

Nombres de todas las tablas y de todas las vistas

1 comentario

A veces podemos necesitar conocer el nombre de todas las tablas y de todas las vistas que hay en una Base de Datos. Con esta consulta lo conseguiremos:

SELECT DISTINCT
    RDB$RELATION_NAME
FROM
    RDB$RELATION_FIELDS
WHERE
    RDB$SYSTEM_FLAG = 0;

Fíjate que te muestra las tablas y también las vistas.

También podríamos obtener esa misma información con esta consulta:

SELECT
    RDB$RELATION_NAME
FROM
    RDB$RELATIONS
WHERE
    RDB$SYSTEM_FLAG=0;

Older Entries