Crearea vizualizarilor
O vizualizare (view) reprezinta o comanda de tip SELECT care a fost denumita si salvata in baza de date, motiv pentru care mai poate fi intalnita si sub numele de interogare cu nume sau interogare stocata.
Vizualizarile sunt salvate in baza de date si sunt rulate cu ajutorul instructiuni SELECT. Ele nu ocupa spatiu pentru stocarea datelor si nici nu creeaza copii redundante ale datelor stocate in tabelele referite (tabele de baza ale vizualizarilor).
In mod similar tabelelor, vizualizarile pot fi create, interogate, modificate sau sterse. Vizualizarile pot referi atat tabele, cat si alte vizualizari.
O vizualizare indica un tabel virtual care contine campuri si inregistrari. Datele continute sunt generate dinamic pe baza unor tabele. Daca se modifica datele din tabelele sursa, atunci se modifica si datele din vizualizare.
Vizualizarile ofera o serie de avantaje, printre care pot fi amintite urmatoarele:
• simplifica interogarile complexe; o vizualizare poate fi utilizata pentru a ascunde utilizatorilor finali complexitatea tabelelor sursa;
• sunt rulate prin intermediul instructiunii SELECT;
• limiteaza accesul la date pentru anumiti utilizatori;
• asigura securitate sporita; exista posibilitatea de a crea vizualizari care pot fi doar citite de utilizatori;
• permit introducerea de campuri calculate.
MySQL asigura suport pentru vizualizari incepand cu versiunea 5.x. In MySQL sintaxa utilizata pentru crearea unei vizualizari este urmatoarea:
CREATE [OR REPLACE] [DEFINER = utilizator] [SQL SECURITY {DEFINER | INVOKER}] VIEW nume_vizualizare [(lista_coloane)] AS instructiune_select
Daca este prezenta, clauza optionala OR REPLACE permite inlocuirea (recrearea) unei vizualizari. In cazul recrearii unei vizualizari fara utilizarea optiunii OR REPLACE este generata o eroare. Clauza nu este necesara daca nu exista nici o vizualizare cu numele specificat.
Clauzele DEFINER si SQL SECURITY specifica contul MySQL care urmeaza a fi utilizat pentru a verifica privilegiile asupra vizualizarii la rularea instructiunii care refera vizualizarea.
Daca pentru clauza DEFINER este specificata o valoare, aceasta trebuie sa corespunda unui utilizator de pe serverul MySQL (user_name@host_name). Valoarea implicita pentru clauza DEFINER este aceeasi cu numele utilizatorului care executa instructiunea CREATE VIEW.
Valorile permise pentru clauza SQL SECURITY sunt DEFINER si INVOKER. Acestea indica faptul ca privilegiile necesare trebuie sa fie detinute de utilizatorul care defineste sau invoca vizualizarea.
Valoarea implicita pentru clauza SQL SECURITY este DEFINER. Daca valoarea corespunzatoare clauzei SQL SECURITY este DEFINER si contul indicat in clauza DEFINER nu exista cand vizualizarea este referita, atunci este generata o eroare.
O vizualizare apartine unei baze de date. Implicit, o vizualizare noua este creata in baza de date activa. Pentru a specifica explicit baza de date in care urmeaza a fi creata vizualizarea, aceasta trebuie sa aiba o denumire de genul nume_baza_date.nume_vizualizare.
Tabelele de baza si vizualizarile apartin aceluiasi spatiu de nume, definit de catre baza de date, deci o baza de date nu poate contine o tabela si o vizualizare care sa aiba acelasi nume.
Vizualizarile trebuie sa contina nume unice de coloane, fara duplicate, in mod similar tabelelor. Implicit, numele coloanelor returnate prin intermediul instructiunii SELECT sunt utilizate pentru numele coloanelor din vizualizare.
Pentru a defini explicit numele coloanelor din vizualizare, poate fi utilizata optiunea lista_coloane, care contine o lista de identificatori separati prin virgula. Numarul de identificatori din lista_coloane trebuie sa fie acelasi cu numarul de coloane returnate de instructiunea SELECT.
Urmatorul exemplu defineste o vizualizare care sumarizeaza datele cu privire la filmele corespunzatoare categoriilor din baza de date sakila. La nivelul vizualizarii sunt precizate categoriile si numarul de filme din fiecare categorie.
CREATE | |
OR REPLACE | |
DEFINER = airman@'localhost' | |
SQL SECURITY DEFINER | |
VIEW sakila.view_films_catagory AS | |
SELECT c.name, COUNT(f.film_id) AS no_films | |
FROM sakila.category c LEFT OUTER JOIN sakila.film_category USING(category_id) | |
LEFT OUTER JOIN sakila.film f USING(film_id) | |
GROUP BY c.category_id; |
mysql> DESCRIBE sakila.view_films_catagory; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | name | varchar(25) | NO | | NULL | | | no_films | bigint | NO | | 0 | | +----------+-------------+------+-----+---------+-------+ 2 rows in set
Denumirea unei vizualizari poate fi utilizata intr-o interogare in mod similar numelui unui tabel. Dupa stocare, o vizualizare poate fi utilizata fara a mai rescrie interogarea pe care o contine.
Cea mai simpla modalitate de utilizare a unei vizualizari o constituie interogarea ei, dar o vizualizare poate fi utilizata si pentru a scrie portiuni dintr-o interogare.
mysql> SELECT * -> FROM sakila.view_films_catagory; +-------------+----------+ | name | no_films | +-------------+----------+ | Action | 64 | | Animation | 66 | | Children | 60 | | Classics | 57 | | Comedy | 58 | | Documentary | 68 | | Drama | 62 | | Family | 69 | | Foreign | 73 | | Games | 61 | | Horror | 56 | | Music | 51 | | New | 63 | | Sci-Fi | 61 | | Sports | 74 | | Travel | 57 | +-------------+----------+ 16 rows in set
Stergerea si actualizarea vizualizarilor
Sintaxa utilizata pentru stergerea unei vizualizari este urmatoarea:
DROP VIEW [IF EXISTS] nume_vizualizare [, nume_vizualizare], …
Instructiunea DROP VIEWS permite stergerea uneia sau mai multor vizualizari. Pentru a utiliza aceasta instructiune utilizatorul trebuie sa detina privilegiul DROP pentru vizualizarile care urmeaza a fi sterse. Daca una din vizualizarile din lista nu exista, va fi returnat un mesaj de eroare pentru vizualizarea care nu exista, iar restul de vizualizari sunt sterse.
mysql> DROP VIEW sakila.view_actors_category; ERROR 1051 (42S02): Unknown table 'sakila.view_actors_category'
Clauza IF EXISTS inhiba afisarea mesajului de eroare care apare la incercarea de stergere a unei vizualizari care nu exista. Daca aceasta clauza este prezenta, pentru orice vizualizare care nu exista este generat un mesaj de avertizare (WARNING).
mysql> DROP VIEW IF EXISTS sakila.view_actors_category; Query OK, 0 rows affected, 1 warning
mysql> SHOW WARNINGS; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 1051 | Unknown table 'sakila.view_actors_category' | +-------+------+---------------------------------------------+ 1 row in set
Modificarea definitiei pentru o vizualizare poate fi realizata cu ajutorul lui ALTER VIEW, care prezinta urmatoarea sintaxa.
ALTER [DEFINER = utilizator] [SQL SECURITY {DEFINER | INVOKER}] VIEW nume_vizualizare [(lista_coloane)] AS instructiune_select
Se poate observa ca aceasta instructiune este similara cu CREATE VIEW si are acelasi efect cu CREATE OR REPLACE VIEW. Instructiunea ALTER VIEW necesita prezenta privilegiilor CREATE VIEW si DROP, dar si privilegii pentru fiecare coloana referita in SELECT.
Vizualizari actualizabile
Pe langa vizualizarile de tip read-only pot fi definite si vizualizari actualizabile. Acestea pot fi utilizate in instructiuni de tip UPDATE, DELETE sau INSERT pentru a actualiza continutul tabelului de baza.
O vizualizare este actualizabila, daca intre inregistrarile vizualizarii si cele ale tabelului de baza este stabilita o relatie de tip 1 la 1. Pentru a crea o astfel de vizualizare este necesara, in plus, verificarea instructiunii SELECT dupa un set de reguli:
• instructiunea SELECT nu poate referi mai mult de un tabel;
• instructiunea SELECT nu poate utiliza clauze, precum GROUP BY sau HAVING;
• instructiunea SELECT nu poate utiliza clauza DISTINCT in lista de selectie;
• instructiunea SELECT nu poate contine expresii (functii, functii de sumarizare, campuri calculate).
Consideram urmatoarea vizualizare, sakila.view_email_staff, care preia numele si adresa de email pentru angajati din tabelul sakila.staff.
CREATE | |
VIEW sakila.view_email_staff AS | |
SELECT first_name, last_name, email | |
FROM sakila.staff; |
mysql> DESCRIBE sakila.view_email_staff; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | first_name | varchar(45) | NO | | NULL | | | last_name | varchar(45) | NO | | NULL | | | email | varchar(50) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set
Putem observa ca aceasta vizualizare respecta regulile expuse anterior. Datele din vizualizare pot fi obtinute foarte simplu, astfel:
mysql> SELECT * -> FROM sakila.view_email_staff; +------------+-----------+------------------------------+ | first_name | last_name | email | +------------+-----------+------------------------------+ | Mike | Hillyer | Mike.Hillyer@sakilastaff.com | | Jon | Stephens | Jon.Stephens@sakilastaff.com | +------------+-----------+------------------------------+ 2 rows in set
Daca dorim sa modificam domeniul pentru adresele web ale angajatilor, este de ajuns sa rulam o instructiune UPDATE cu urmatoarea forma:
mysql> UPDATE sakila.view_email_staff -> SET email = REPLACE(email, 'sakilastaff.com', 'sakilastaff.co.ro'); Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0
Modificarile pot fi sesizate atat prin accesarea tabelei de baza, cat si prin accesarea vizualizarii.
mysql> SELECT first_name, last_name, email -> FROM sakila.staff; +------------+-----------+--------------------------------+ | first_name | last_name | email | +------------+-----------+--------------------------------+ | Mike | Hillyer | Mike.Hillyer@sakilastaff.co.ro | | Jon | Stephens | Jon.Stephens@sakilastaff.co.ro | +------------+-----------+--------------------------------+ 2 rows in set
mysql> SELECT first_name, last_name, email -> FROM sakila.view_email_staff; +------------+-----------+--------------------------------+ | first_name | last_name | email | +------------+-----------+--------------------------------+ | Mike | Hillyer | Mike.Hillyer@sakilastaff.co.ro | | Jon | Stephens | Jon.Stephens@sakilastaff.co.ro | +------------+-----------+--------------------------------+ 2 rows in set
Daca o vizualizare actualizabila este utilizata intr-o instructiune de tip UPDATE, INSERT sau DELETE valorile din tabelul de baza vor fi actualizate.