Vistas
Para esta lección es necesario es necesario usar la base de datos particular de cada uno, donde tenéis permisos de creación de vistas.
Una vista es un objeto que se define a partir de una consulta y que se comporta como una tabla si bien, dependiendo de la consulta en la que se basa, se pueden hacer más o menos cosas: consultar una vista siempre será posible, pero insertar o borrar filas en una vista o modificar un valor ya depende de cómo sea esa definición.
Lo más importante: una vista limita la cantidad de datos —filas y columnas— a los que podemos acceder y con los que podemos trabajar. Explicado con un ejemplo, si soy un comercial de la provincia de Alicante, mi vista solo me dejará consultar y dar altas y bajas de clientes de Alicante, nunca podré saber qué clientes hay en Valencia ni hacer nada con ellos.
Para crear una vista disponemos de la orden:
CREATE VIEW nombrevista AS consulta
Una vista es un objeto persistente, por lo tanto, para eliminarla del catálogo, hay que ejecutar:
DROP VIEW nombrevista
También se puede consultar la definición de una vista ya definida (aunque esto ya es particular de MariaDB/MySQL, en otros gestores tienen sus propios métodos):
SHOW CREATE VIEW nombrevista
Toda la información sobre vistas en MariaDB se puede consultar en https://mariadb.com/kb/en/views/.
Esta sesión se ha planteado en formato demostración por lo que se recomienda ejecutar una a una las siguientes sentencias y buscar los motivos por los que unas sentencias se ejecutan sin problemas y otras no.
Tras conectarse al servidor hay que elegir una base de datos en la que se nos permita crear vistas:
use mibd; -- la base de datos que te permita definir vistas
Vamos a copiar los contenidos de algunas tablas de TiendaOnLine a tablas locales que sean de nuestra propiedad
drop table if exists mitv;
drop table if exists miarticulo;
create table miarticulo (
cod varchar(7),
nombre varchar(45),
pvp decimal(7,2),
marca varchar(15),
imagen blob,
urlimagen varchar(100),
especificaciones text,
primary key (cod)) engine=innodb;
insert into miarticulo select * from tiendaonline.articulo;
create table mitv (
cod varchar(7),
panel varchar(45),
pantalla smallint(6),
resolucion varchar(15),
hdreadyfullhd varchar(6),
tdt tinyint(1),
primary key (cod),
foreign key (cod) references miarticulo (cod)) engine=innodb;
insert into mitv select * from tiendaonline.tv;
Creamos la primera vista:
create view vma as select cod,nombre,pvp from miarticulo;
Una vista se comporta como una tabla y puede consultarse.
select * from vma where pvp between 500 and 700;
Una vista se puede generar a partir de cualquier consulta, y tiene la característica añadida de poder restringir el acceso a solo un subconjunto de las filas posibles.
create view vmb as select cod,nombre,pvp from miarticulo where pvp between 500 and 700;
select * from vmb;
/*1*/ select cod,pvp from vmb where cod in (select cod from mitv) order by pvp;
Insertando
Una vista, bajo ciertas condiciones, permite insertar nuevos datos.
/*2*/ insert into vma (cod,nombre,pvp) values ('B001','MiArtículo',499);
select * from miarticulo where cod='B001';
select * from vma where cod='B001';
select * from vmb where cod='B001'; -- ¿por qué no se obtiene ninguna fila?
/*3*/ insert into vmb (cod,nombre,pvp) values ('B002','MiOtroArtículo',701);
select * from vmb where cod='B002'; -- ¿por qué no se obtiene ninguna fila?
select * from miarticulo where cod='B002'; -- sin embargo, la inserción sí se ha realizado
select * from vma where cod='B002'; -- y en esta vista sí aparece...
Las vistas solo son consultas encapsuladas en un objeto de base de datos, no son tablas ni copias de tablas. Cuando insertamos en "vma "o "vmb" estamos añadiendo filas a la tabla base de la vista, "miarticulo".
Borrando
Creamos otra vista para facilitar la comprobación de las acciones que solicitamos. En este caso vamos a intentar eliminar filas.
create view bart as select * from miarticulo where cod like 'B%';
-- recordando las vistas
select * from vma where cod in ('B001','B002');
select * from vmb where cod in ('B001','B002'); -- no obtiene nada, recuerda la definición de vmb
-- borrando a través de las vistas
select * from bart; -- estado inicial
/*4*/ delete from vma where cod='B001';
select * from bart; -- estado tras el primer borrado
/*5*/ delete from vmb where cod='B002';
select * from bart; -- estado final
Observado el estado final tras la ejecución, el borrado /*4*/ sí se ha realizado mientras que el /*5*/ no. Tenemos aquí una de las razones de ser de las vistas, la seguridad de los datos. Quien pueda usar la vista "vmb" no puede ver B001 ni B002 por el rango de precios al que está limitada. Si no los puede ver, no los puede borrar, no "sabe" que existen.
Modificando
Ahora comprobaremos la orden update. Esta orden tiene las mismas restricciones que delete.
select * from bart;
/*6*/ update vma set pvp = 800 where cod='B002';
select * from bart;
/*7*/ update vmb set pvp = 600 where cod='B002';
select * from bart;
Lo que pasaba con /*5*/ vuelve a ocurrir con /*7*/: si no puedes recuperar un artículo, no puedes actualizarlo.
Con más de una tabla
La vista se puede definir sobre varias tablas, pero eso afecta a las órdenes que se pueden ejecutar y en qué condiciones ("Inserting and Updating with Views", MariaDB Server Documentation).
create view vat as
select a.cod,nombre,pvp,resolucion,tdt
from miarticulo a, mitv t
where a.cod=t.cod
and pvp between 800 and 1200;
select * from vat order by pvp desc;
-- ejecuta una a una estas instrucciones
/*8*/ insert into vat values ('B003','OtroMás',1100,null,null); --Error: Can not insert into join view without fields list
/*9*/ insert into vat (cod,nombre,pvp,resolucion,tdt) values ('B003','OtroMás',1100,null,null); --Can not modify more than one base table through a join view
/*10*/ insert into vat (cod,nombre,pvp) values ('B003','OtroMás',1100);
-- ¿dónde ha ido a parar la inserción /*10*/
select * from vat where cod = 'B003';
select * from mitv where cod = 'B003';
select * from miarticulo where cod = 'B003';
En general, MariaDB/MYSQL no admite inserciones en vistas que pudieran afectar a más de una tabla, o digamos que se hace difícil conseguirlo. La vista "vat" está definida como un join de dos tablas, "miarticulo" y "mitv". Por eso, la inserción /*8*/ falla porque no hemos especificado columnas concretas; la /*9*/ por que hemos indicado columnas de ambas tablas. La /*10*/, sin embargo, se ha ejecutado sin problemas ya que únicamente contiene columnas de "miarticulo". Además, la vista maneja la clave primaria de "miarticulo". Es decir, hemos insertado, realmente, en "miarticulo".
Mediante la vista "vat" nunca podremos hacer actualizaciones en "mitv" por todo lo contrario: la vista no incluye la clave primaria de la tabla "mitv".
select * from vat;
select * from bart;
-- estas dos inserciones fallarán
/*11*/ insert into vat (resolucion,tdt) values ('800x600',1); -- ¿con qué valor de mitv.cod?
/*12*/ insert into vat (cod,resolucion,tdt) values ('B004','800x600',1); -- en realidad son miarticulo.cod, mitv.resolucion, mitv.tdt
/*13*/ delete from vat where cod='A0694'; --error: Can not delete from join view
-- comprobamos que no se ha ejecutado nada
select * from vat;
select * from bart;
/*14*/ update vat set pvp = 999 where cod='A0694'; -- ok
/*15*/ update vat set resolucion = '800x600',tdt=1 where cod='A0694'; -- ok
/*16*/ update vat set pvp = 850, resolucion = 'ninguna',tdt=1 where cod='A0694'; -- error: Can not modify more than one base table through a join view
select * from vat;
/*17*/ update vat set cod = 'B004' where cod='A0694'; -- error: solo vemos miarticulo.cod, y no hemos definido ON UPDATE para la clave ajena en mitv (foreign key (cod) references miarticulo (cod))
-- comprueba el estado final
select * from vat;
select * from bart;
select * from mitv where cod='A0694';
With check option
Vamos redefinir bart y crear otra vista "bart2".
drop view if exists bart2;
drop view if exists bart;
/*18*/create view bart as select * from miarticulo where cod like 'B%';
/*19*/create view bart2 as select * from miarticulo where cod like 'B%' with check option;
select * from bart;
select * from bart2;
/*20*/insert into bart2 (cod,nombre,pvp) values ('B010','Artículo B10',1999); -- ok
/*21*/insert into bart2 (cod,nombre,pvp) values ('C010','Artículo C10',1999); -- error
select * from bart;
select * from bart2;
select * from miarticulo where pvp=1999;
/*22*/insert into bart (cod,nombre,pvp) values ('C010','Artículo C10',1999); -- ok
select * from bart;
select * from bart2;
select * from miarticulo where pvp=1999;
El modificador WITH CHECK OPTION obliga a cumplir las condiciones del where, sea cual sea la operación a ejecutar sobre la vista: si "bart2" no tuviera esa opción, podríamos insertar en la vista cualquier artículo que, finalmente, se almacenaría en "miarticulo". Sin embargo, aunque el artículo existiría en mi base de datos, no podría verlo consultando la vista por la definición de "bart2". Con WITH CHECK OPTION el sistema no me deja insertar más que artículos cuyo "cod" empiece por 'B'.
En el ejemplo anterior, la orden /*20*/ se ha podido ejecutar, en bart2, porque el código de artículo comienza por 'B'.
En la orden /*21*/, la inserción no se ha podido realizar porque bart2 no admite trabajar con códigos que empiecen por otra letra.
Sin embargo, la vista bart, en la orden /*22*/ sí que permite la inserción que bart2 no permitió en /*21*/. Ahí está la diferencia entre usar with check option o no.
Finalmente, eliminamos todas las vistas y tablas creadas.
drop view bart;
drop view bart2;
drop view vat;
drop view vmb;
drop view vma;
drop table mitv;
drop table miarticulo;