Sisteme de gestiune a bazelor de date. Note de curs
64
Cuprins 1. Sisteme de gestiune a bazelor de date. Componentele și funcţiile unui sistem de gestiune a bazelor de date .............................................................. 7 2. Sistemul de gestiune a bazelor de date MySQL ................................... 11 3. Implementarea și popularea unei baze de date utilizând SGBD-ul MySQL......................................................................................................... 13 4. Tabele virtuale în interogări.................................................................. 17 5. Rutine stocate în SGBD ........................................................................ 27 5.1 Proceduri stocate ............................................................................... 28 5.2 Declanșatoare .................................................................................... 30 5.3 Funcții stocate ................................................................................... 33 6. Căutare rapidă. Indecși ......................................................................... 41 7. Prelucrări bazate pe cursoare ................................................................ 43 8. Drepturi și utilizatori într-un SGBD ..................................................... 45 8.1 Sistemul de acces al drepturilor MySQL .......................................... 45 8.2 Gestiunea drepturilor și utilizatorilor ................................................ 46 8.3 Limitarea resurselor utilizatorilor ..................................................... 51 9. Gestiunea tranzacțiilor .......................................................................... 53 10. Replicarea bazelor de date.................................................................... 59 Bibliografie .................................................................................................. 66 5
Transcript
Cuprins 1. Sisteme de gestiune a bazelor de date. Componentele și funcţiile unui sistem de gestiune a bazelor de date .............................................................. 7
2. Sistemul de gestiune a bazelor de date MySQL ................................... 11
3. Implementarea și popularea unei baze de date utilizând SGBD-ul MySQL......................................................................................................... 13
4. Tabele virtuale în interogări .................................................................. 17
5. Rutine stocate în SGBD ........................................................................ 27
1. Sisteme de gestiune a bazelor de date. Componentele și funcţiile unui sistem de gestiune
a bazelor de date
Apărute în anii ’60, sistemele de gestiune a bazelor de date (SGBD) reprezintă un ansamblu de programe ce permit utilizatorilor să interacționeze cu o bază de date, în vederea creării, actualizării și interogării acesteia. SGBD-ul este cel care asigură și supervizează: introducerea de informații în baza de date, actualizarea și extragerea datelor din baza de date, autorizarea și controlul accesului la date, păstrarea independenței dintre structura bazei de date și programe. [1]
Obiectivul esențial al unui SGBD este furnizarea mediu eficient, adaptat utilizatorilor care doresc să consulte sau să actualizeze informațiile conținute în baza de date. Bazele de date sunt concepute pentru a prelucra un volum foarte mare de informații. Gestiunea acestora impune nu numai o structurare riguroasă a datelor, ci și o raționalizare a procedurilor de acces și prelucrare. [1]
Astfel, o cerere de acces la baza de date, de exemplu o interogare, este formulată de utilizator în termenii conceptelor uneia din vederile definite în sistem. Această cerere este interceptată de către SGBD și interpretată de către o componentă a acestuia, interpretorul DML (Data manipulation Language - Limbaj de manipulare a datelor). Rezultatul este o reprezentare în format intern a interogării. În continuare, interogarea parcurge o serie de etape succesive de prelucrare prin care, dintr-o interogare formulate în termenii unor concepte de la nivelul vederilor, rezultă o serie de comenzi de acces la fișierele fizice din baza de date. În etapele succesive de transformare, SGBD folosește infromațiile de descriere de la toate nivelele bazei de date (modelul extern, modelul conceptual, nivelul intern), împreună cu descrierile interfețelor model extern-model conceptual și model conceptual-nivel intern. Pe lângă acestea, SGBD mai poate consulta o serie de tabele cum ar fi: tabelele de autorizare a accesului la date, tabelele conținând informațiile de control al accesului concurent, ș.a.m.d. De asemenea, pe toate nivelele de prelucrare, pe lângă transformarea dintr-o formă de reprezentare în alta, mai
7
apar și pași de prelucrare care au drept scop optimizarea execuției interogării respective. [2]
Cererile de acces la fișierele fizice, rezultate din transformarea interogării, sunt preluate și rezolvate de către un sistem de gestiune a fișierelor. Acesta poate fi un sistem general, parte a sistemului de operare care găzduiește baza de date, sau un sistem specializat, adaptat cerințelor speciale ale sistemului de gestiune a bazelor de date. Datele extrase din fișierele fizice, sub forma unor șiruri de biți, parcurg apoi calea inversă, rezultatul transformărilor succesive fiind un răspuns formulat în termenii cunoscuți de utilizator (sub formă de tabele, rapoarte, etc.). [2]
Prin urmare, principalele funcții ale unui SGBD vizează [1]:
• Descrierea ansamblului de date la nivel fizic și conceptual; • Crearea (inițializarea) și exploatarea (consultarea și actualizarea) bazei
de date; • Controlul integrității bazei de date; • Confidențialitatea informațiilor conținute în baza de date; • Accesul simultan al mai multor utilizatori la informații; • Securitatea în funcționare; • Furnizarea unui set de comenzi și instrucțiuni, necesare atât
utilizatorilor pentru consultarea directă a bazei de date, prin intermediul DML, cât și programatorilor, pentru redactarea programelor de lucru cu baza de date;
• Revizia și restructurarea bazei de date; • Monitorizarea performanțelor.
SGBD conține trei limbaje pentru proiectarea și exploatarea bazelor de date, și anume:
• Limbaje de definire a datelor • Limbaje de manipulare a datelor • Limbaje de control al datelor.
8
A. Limbaje de definire a datelor Arhitectura unei baze de date este specificată printr-o serie de definiții
redactate sub formă de instrucțiuni scrise în limbajul de definire a datelor, DDL (Data Definition Language). Execuția acestor definiții se materializează într-un ansamblu de tabele, tabele virtuale, proceduri stocate, secvențe, etc. care sunt memorate într-un fișier special, denumit dictionar de date. Un dicționar de date conține, așadar, metadate, adică date relative la alte date, fiind consultat înaintea oricărei citiri sau modificări a datelor din baza de date. [1].
Cel mai important limbaj dedicat bazelor de date, SQL (Structured Query Language), prezintă comenzi DDL cum ar fi: CREATE / ALTER / DROP TABLE, CREATE / ALTER / DROP VIEW, CREATE / DROP INDEX, CREATE / DROP PROCEDURE, CREATE / DROP TRIGGER, CREATE / DROP SEQUENCE, etc.
B. Limbaje de manipulare a datelor Prin intermediul instrucțiunilor DML (Data manipulation Language) se
efectuează următoarele operațiuni:
• Adăugarea unor date în baza de date; • Modificarea datelor existente (actualizare); • Ștergerea unor date din baza de date; • Extragerea unor date din baza de date. Cele mai cunoscute comenzi DML sunt: INSERT (pentru adăugarea de
înregistrări într-o tabelă), UPDATE (pentru modificarea valorii unora sau mai multor atribute, pe una sau mai multe înregistrări ale unei tabele), DELETE (pentru ștergerea de înregistrări dintr-o tabelă) și SELECT (pentru extragerea de informații din una sau mai multe tabele).
C. Limbaje de control al datelor Prin comenzile DCL (Data Control Language) se declară utilizatorii unei
baze de date, grupurile de utilizatori (profilurile), precum și drepturile fiecărui utilizator/profil la obiecte din schema bazei de date (tabele, tabele virtuale, proceduri, etc.). Cele mai frecvente comenzi SQL sunt: CREATE / DROP USER, CREATE / DROP ROLE, GRANT și REVOKE.
9
10
2. Sistemul de gestiune a bazelor de date MySQL
Există diferențe considerabile între SGBD-urile aflate azi pe piață, în privința performanțelor și prețului. Acces sau Visual FoxPro, ambele produse Microsoft, sunt SGBD-uri mai modeste în privința dimensiunii bazei de date. Visual FoxPro nu are opțiuni de declarare a utilizatorilor și grupurilor de utilizatori, cu atât mai mult opțiuni avansate de administrare. În schimb, este foarte generos în realizarea de meniuri, formulare și rapoarte, ceea ce l-a făcut mult timp preferatul informaticienilor în dezvoltarea de aplicații mici și medii. Astăzi, nume grele în domeniul bazelor de date sunt SQL Server (Microsoft), Oracle (Oracle), DB2 (IBM), iar cei mai serioși competitori ai acestora sunt SGBD-urile open source, cum ar fi: MySQL, PostgreSQL, etc. [1]
Se va utiliza în continuare ca studiu de caz SGBD-ul MySQL. MySQL este un sistem de baze de date cu performanţe ridicate, dar
relativ simplu, a cărui configurare şi administrare sunt mult mai simple decât în cazul sistemelor mai mari. MySQL este produs de firma suedeză AB Software şi poate fi descărcat gratuit de la adresa http://www.mysql.org. De regulă, distribuţiile standard ale sistemelor de operare Linux conţin şi serverele MySQL şi PostgreSQL.
Distribuţia MySQL include următoarele instrumente [3]:
• Un server SQL. Acesta este motorul care activează MySQL şi care furnizează accesul la bazele de date.
• Programe client pentru accesul la server. Un program interactiv permite introducerea interogărilor în mod direct şi vizualizarea rezultatelor, iar numeroasele programe administrative şi utilitare ajută la rularea sitului. Un utilitar permite controlarea serverului. Altele permit importarea sau exportarea de date, verificarea permisiunilor de acces şi multe altele.
• O bibliotecă client pentru scrierea propriilor programe. Se pot scrie programe client în C, deoarece biblioteca este scrisă în C, dar biblioteca mai furnizează şi baza pentru terţe asocieri pentru alte limbaje.
Serverul MySQL reprezintă una dintre cele mai bune soluţii pentru stocarea bazelor de date (în această categorie concurând cu PostgreSQL). Prin stabilitate şi simplitate, a câştigat teren în domeniul aplicaţiilor Web. Oferă posibilităţi asemănătoare serverelor de baze de date proprietare, fiind renumit prin viteza mare de execuţie a interogărilor simple [4].
O caracteristică importantă a serverului MySQL este aceea că la el se pot conecta mai mulţi clienţi simultan. Clienţii pot folosi mai multe baze de date simultan. Se poate obţine acces la MySQL în mod interactiv, folosind numeroase interfeţe care permit introducerea de interogări şi vizualizarea rezultatelor: clienţi în linie de comandă, browsere Web sau clienţi X Window System. De asemenea, este disponibilă o varietate de interfeţe de programare pentru limbaje precum C, Perl, Java, PHP şi Python. Astfel, oferă utilizatorului opţiunea de a folosi programe client preambalate sau de a-şi scrie propriile programe client pentru aplicaţii personalizate [4].
MySQL poate fi folosit integral în reţele, iar bazele de date sunt accesibile de oriunde din Internet, se pot partaja datele cu oricine, oriunde. Dar MySQL are controlul accesului, astfel încât persoanele care nu au dreptul să citească anumite date nu vor avea această posibilitate. MySQL rulează pe numeroase varietăţi de UNIX, precum şi pe alte sisteme non-UNIX, ca Windows şi OS/2. MySQL rulează pe echipamente de la calculatoare de birou la servere cu performanţe ridicate [4].
12
3. Implementarea și popularea unei baze de date utilizând SGBD-ul MySQL
În această etapă se realizează crearea structurii bazei de date
considerate ca studiu de caz (gestiunea_absolventilor [8]) pe baza facilităților oferite de SGBD-ul ales.
Rezultatul ei este programul de creare scris în limbajul de definire a datelor (DDL) acceptat de SGBD-ul MySQL [8].
13
Este exemplificată, de asemenea, și instrucțiunea INSERT SQL de
manipulare a datelor (DML) [8].
14
Structura bazei de date considerată ca studiu de caz se poate vizualiza
în Figura 1.
Fig. 1. Diagrama bazei de date gestiunea_absolventilor [8]
15
16
4. Tabele virtuale în interogări Cunoscută sub denumirea de tabel virtual, o vedere constă într-un set
de înregistrări care este returnat în cazul în care o anumită interogare este executată. Datele corspunzătoare unei vederi nu sunt memorate într-un obiect al bazei de date, iar ceea ce se memorează este o frază SELECT care regăsește în mod dinamic datele, atunci când este utilizată.
O vedere nu este o copie a datelor reprezentate de interogare, ci mai degrabă o modalitate prin care datele pot fi recuperate prin invocarea interogărilor prin intermediul alias-urilor.
Avantajele tabelelor virtuale sunt multiple [1], [5], [6]:
• Simplificarea interogărilor și salvarea rezultatelor intermediare. După scrierea interogării, aceasta poate fi utilizată cu ușurință, nefiind necesară cunoașterea interogării în întregime.
• Securizarea datelor. Drepturile de acces și manipulare acordate unor categorii de utilizatori pot fi la nivelul vederilor, nu la tabele în totalitatea lor. În acest mod se expun părți din tabele, nu tabele complete.
• Modificarea formatării și reprezentării datelor. Vederile pot returna date formatate și prezentate într-un alt mod decât în tabelele de bază.
• Construirea unor surse de date pentru rapoarte și controale ale formularelor (liste, combo box-uri sau grid-uri).
Utilizarea vederilor pentru simplificarea uniunilor complexe
Una din cele mai comune utilizări ale vederilor constă din ascunderea elementelor complexe ale limbajului SQL, ceea ce implică, deseori, utilizarea uniunilor. De exemplu, vederea de mai jos [8] unește două tabele (date_identificare și studii) pentru a returna o listă a tuturor absolvenţilor universităţii, pe specializări şi promoţii.
17
Pentru a regăsi o listă a absolvenților specializării “Informatica”, se va proceda astfel:
Această instrucțiune regăsește anumite date din vedere prin utilizarea
clauzei where. Când SGBD prelucrează cererea, adaugă clauza where specificată la eventuale clauze where existente în vedere, astfel încât datele să fie filtrate în mod corespunzător.
18
Utilizarea vederilor pentru reformatarea datelor regăsite
O altă utilizare comună a vederilor constă din reformatarea datelor regăsite.
Următoarea vedere [8] execută concatenarea numelui și prenumelui absolvenților, separate prin spațiu, și afișează adresa de domiciliu a acestora fără spații.
Eliminarea spațiilor din partea dreaptă a fiecărei valori a fost realizată prin utilizarea funcției RTRIM(). Pentru câmpurile afișate au fost utilizate alias-uri (nume alternative).
Utilizarea vederilor pentru filtrarea datelor
Vederile sunt, de asemenea, utile și pentru aplicarea clauzelor where
comune. De exemplu, pentru determina numărul de absolvenţi ai
specializărilor Informatică şi Programare Avansată şi Baze de date care au găsit un loc de muncă, se va executa vederea de mai jos [8].
Clauza where din acest exemplu selectează studenții de la cele două specializări și îi elimină pe cei care nu au completat informații în câmpul
19
denumire_companie. În final, se afișează numărul înregistrărilor rămase în urma filtrării.
O altă filtrare ar putea fi în funcție de orașul în care absolvenții au găsit un loc de muncă, de exemplu afișarea absolvenților care au găsit un loc de muncă în oraşul “Alba Iulia”. În clauza where se va verifica dacă informația conținută în câmpul localitate_companie este cea indicată în enunț, iar în caz afirmativ se reține înregistrarea [8].
20
Pentru selectarea absolvenţilor care au domiciliul în străinătate, este necesar ca în clauza where să se elimine înregistrările pentru care câmpul tara_domiciliu conține valoarea “Romania” [8].
Utilizarea vederilor alături de câmpuri cu valoare calculată
Vederile sunt foarte utile pentru simplificarea modului de utilizare a
câmpurilor cu valoare calculată. În continuare este prezentată o vedere ce regăsește absolvenții care au mai mult de 2 ani vechime la actualul loc de muncă.
Pentru aceasta a fost necesară determinarea anului curent, cu ajutorul funcțiilor YEAR(CURDATE()), precum și a anului angajării absolventului (YEAR(joburi.data_angajare)). În clauza where s-a verificat dacă diferența valorilor returnate de cele două funcții este mai mare sau egală cu 2, iar în caz afirmativ, a fost selectată înregistrarea curentă [8].
21
În mod similar, au fost selectați și afișați absolvenții (nume, prenume şi specializare) care au găsit un loc de muncă în timpul studiilor sau la mai puţin de un an de la absolvire [8].
Pentru afișarea listei companiilor care au angajaţi mai mulţi de 2 absolvenţi, a fost necesară o numărare (funcția count) a absolvenților din fiecare companie, și o filtrare a rezultatelor în clauza where a instrucțiunii select (numarul absolvenților din fiecare companie să fie mai mare sau egal cu 2) [8].
22
De remarcat este faptul că este indicată crearea vederilor refolosibile,
așa cum a fost procedat în exemplul anterior. Extinderea domeniului de acțiune al vederii (prin nelegarea acesteia de anumite date) o face mai utilă și elimină necesitatea de creare și întreținere a mai multor vederi asemănătoare.
Prin utilizarea atributului ALGORITHM specific MySQL se poate
optimiza execuția vederii prin intermediul a trei setări descrise în continuare.
MERGE
Algoritmul MERGE determină MySQL să combine definiția vederii cu orice alte clauze în momentul execuției acesteia [7].
În exemplul de mai jos, definiția vederii și interogarea select au fost îmbinate (merge).
23
TEMPTABLE Când o vedere are asignată opțiunea TEMPTABLE, un tabel
temporar corespunzător este creat în momentul creării vederii.
UNDEFINED
Când o vedere are proprietatea UNDEFINED (implicită), MySQL încearcă să determine care dintre cei doi algoritmi (MERGE sau TEMPTABLE) ar trebui să fie utilizat. Având în vedere că sunt puține situații în care algoritmul TEMPTABLE este preferat (de exemplu când sunt utilizate în interogare funcții agregat), algoritmul MERGE este, în general, mai eficient. MySQL va alege opțiunea TEMPTABLE doar dacă interogarea denotă o relație de unu-la-unu între rezultatele sale și cele găsite în vedere [7].
Utilizarea bazei de date INFORMATION_SCHEMA Baza de date INFORMATION_SCHEMA conține un tabel numit
views care conține atributele de mai jos:
24
Utilizarea opțiunilor de securitate pentru vederi [DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }] Opțiunile de securitate adăugate comenzii CREATE VIEW ajută la
controlul modalităților în care sunt determinate privilegiile de fiecare dată când este executată o vedere [7].
Clauza DEFINER determină contul de utilizator pentru care vor fi examinate privilegiile la execuția vederii și stabilește dacă privilegiile sunt suficiente pentru a executa vederea. Utilizatorul este identificat prin sintaxa „user@host” (de exemplu root@localhost). Doar super-utilizatorii au posibilitatea de a seta această clauză.
Clauza SQL_SECURITY determină dacă trebuie examinate privilegiile utilizatorului care a creat vederea (DEFINER), sau privilegiile celui care o invocă (INVOKER) în momentul execuției acesteia.
Restricții în tabele virtuale WITH [CASCADED | LOCAL] CHECK OPTION
Clauza WITH CHECK OPTION permite ca la orice inserare sau
modificare, înregistrările din tabela virtuală să respecte condițiile din clauza where.
Dacă sursa unei vederi este tot o vedere care, la rândul său, este tot o vedere, ș.a.m.d., clauza CHECK OPTION poate utiliza opțiunea CASCADED pentru a verifica dacă inserarea / modificarea respectă condițiile din clauzele where ale tuturor vederilor, sau LOCAL pentru a verifica doar respectarea predicatul din clauza where a tabelei virtuale curente.
25
26
5. Rutine stocate în SGBD Când se utilizează un server de baze de date în dezvoltarea aplicațiilor
(de exemplu într-o configurație client-server), există două opțiuni de bază pentru stocarea și executarea programelor. Programele pot fi memorate local, la nivelul aplicațiilor care trimit comenzi către serverul SQL și prelucrează rezultatele returnate de acestea. A doua opțiune presupune dezvoltarea și înregistrarea programelor ca rutine stocate în catalogul SGBD-ului (“înăuntrul” bazei de date, la dicționarul de date) și crearea de aplicații care apelează aceste proceduri și prelucrează rezultatele returnate de acestea. [5]
Utilizarea rutinelor stocate prezintă mai multe avantaje față de variant programelor stocate la nivelul aplicațiilor utilizator: [5], [6], [7]
• Utilizarea de rutine stocate la nivelul SGBD-ului duce la o scădere a redundanței proceselor de dezvoltare în cazul care mai multe aplicații scrise în diferite limbaje de programare se conectează la aceeași bază de date.
• Compilarea și executarea rutinelor stocate se face o singură dată, la crearea lor, și sunt memorate într-o formă direct executabilă, evitându-se, astfel, repetarea fazelor de compilare și optimizare la fiecare apel al rutinelor. Se evită, totodată, transmiterea prin rețea a codului rutinei la fiecare executare a acesteia (se reduce traficul de rețea comparativ cu programele memorate la nivelul aplicației).
• Rutinele stocate pot fi create de persoane specializate în baze de date și pot fi modificate independent de aplicațiile care o apelează.
• Rutinele stocate oferă un mecanism suplimentar de securitate. Utilizatorii nu au acces direct la codul rutinelor, iar dreptul de execuție al acestora poate fi acordat sau nu, în funcție de statutul fiecărui utilizator, de către administratorul bazei de date.
Stocarea de rutine la nivelul SGBD-ului prezintă și anumite dezavantaje:
• Portabilitate redusă, sintaxa rutinelor stocate variind de la un SGBD la altul.
• Necesitatea creșterii performanței serverului pe care acestea rulează.
27
Rutinele stocate în serverul SQL sunt similare procedurilor din alte limbaje de programare, în sensul că [5]:
• Acceptă parametri de intrare de la programul apelant și returnează valori prin parametri de ieșire către acesta.
• Conțin instrucțiuni de programare care efectuează operații în baza de date și pot apela, la rândul lor, alte rutine stocate.
• Returnează către apelant o valoare care indică succesul sau eșecul execuției acesteia, și, eventual, cauza eșecului.
Setarea opțiunilor de securitate
Clauza DEFINER din sintaxa rutinelor determină contul de utilizator care va fi consultat, cu scopul stabilirii privilegiilor necesare execuției interogărilor definite în corpul rutinelor. În cadrul acestei clauze este necesar să fie specificat atât numele de utilizator, cât și gazda, utilizând sintaxa „user@host”.
Rutinele stocate sunt, în general, de trei tipuri: proceduri stocate (stored procedures), funcții stocate (stored functions) și declanșatoare (triggers).
INSERT, UPDATE, și DELETE. Se utilizează, în general, când se lucrează cu datele din baza de date, de exemplu când se dorește extragerea de înregistrări sau inserarea, modificarea sau ștergerea unor valori ale câmpurilor înregistrărilor.
Un exemplu de procedură care conține o comandă SELECT pentru preluarea tuturor absolvenților din baza de date, este prezentat mai jos [8]:
28
Apelul procedurii se realizează prin intermediul comenzii CALL urmată
de numele procedurii, și, eventual, de lista de parametri. Rezultatul apelului este prezentat mai jos:
Setarea parametrilor de intrare și a parametrilor de ieșire Procedurile stocate acceptă atât parametri de intrare, cât și parametri de
ieșire. Pentru fiecare parametru este necesar să se declare numele acestuia, tipul (orice tip valid de dată MySQL) și să se specifice dacă acesta este utilizat pentru a transmite informație în procedură (IN), dacă transmite informație din procedură spre programul apelant (OUT), sau dacă efectuează ambele sarcini (INOUT).
Exemplu Procedura prezentată mai jos stochează informații în tabela
chestionar_joburi(id_intrebare,cnp,intrebare,raspuns) prin utilizarea unei comenzi INSERT [8].
Primul pas a constat în scrierea comenzii CREATE TABLE pentru definirea structurii tabelei date:
29
A urmat, apoi, scrierea corpului procedurii init_chestionar, cu variabila
cnp dată ca parametru de intrare [8].
La apelul procedurii descrise mai sus se va genera un chestionar
pentru fiecare absolvent înregistrat în baza de date. Câmpurile id_intrebare, cnp și intrebare se vor popula la execuția procedurii, iar câmpul raspuns va fi populat de către absolvenți prin completarea într-un formular Web a răspunsului la întrebările generate de chestionar.
5.2 Declanșatoare Declanșatoarele (trigger-ele) sunt o clasă specială de proceduri
stocate, asociate unei tabele, definite pentru a fi lansate în execuţie automat la iniţierea unei operaţii de tip UPDATE, INSERT sau DELETE asupra tabelei în cauză [5].
Spre deosebire de procedurile stocate, declanșatoarele sunt asociate unor tabele individuale.
Un trigger este iniţiat ori de câte ori se încearcă operaţia de modificare corespunzătoare asupra tabelei căreia îi este ataşat. Un trigger poate conţine instrucţiuni SQL complexe şi poate accesa datele din alte tabele. Trigger-ul şi
30
operaţia care îl declanşează sunt considerate ca un tot unitar (tranzacţie) ceea ce înseamnă că dacă execuţia trigger-ului eşuează, dintr-un motiv sau altul, atunci şi operaţia care a declanşat trigger-ul se anulează. O tabelă poate avea asociate mai multe trigger-e. Instrucţiunea CREATE TRIGGER poate fi definită cu oricare dintre clauzele FOR UPDATE, FOR INSERT sau FOR DELETE [5].
Dacă se foloseşte clauza FOR UPDATE, atunci se poate, de asemenea, folosi clauza IF UPDATE (nume_coloana) prin care se poate crea un trigger specializat pentru situaţia modificării unei coloane anume. Clauza IF UPDATE (nume_coloana) este, de fapt, un test care returnează valoarea logică true în cazul în care coloana dată ca parametru a fost modificată de operaţia ce a declanşat triggerul şi false în caz contrar [5].
SQL permite asocierea la o singură tabelă a mai multe trigger-e de acelaşi tip (UPDATE, INSERT sau DELETE).
Principalele evenimente declanșator, împreună cu o scurtă descriere a acestora [1], sunt prezentate în Tabelul 1.
Comandă Codul declanșatorului se lansează înaintea executării unei comenzi INSERT în tabela-țintă
BEFORE INSERT
Linie Se execută înaintea inserării fiecărei linii în tabelă
AFTER INSERT
Linie Se execută după inserarea fiecărei linii în tabelă
AFTER INSERT
Comandă Se lansează după execuția unei comenzi de inserare de linii în tabelă
INSTEAD OF INSERT
Linie În loc să se insereze o linie în tabelă, se execută codul din acest declanșator
BEFORE UPDATE
Comandă Codul acestuia se execută înaintea executării unei comenzi UPDATE pentru tabela-țintă
BEFORE UPDATE
Linie Se execută înaintea modificării fiecărei linii din tabelă
AFTER UPDATE
Linie Se execută după modificarea fiecărei linii
AFTER UPDATE
Comandă Se lansează după execuția comenzii UPDATE (după modificarea tuturor liniilor afectate de comandă)
31
INSTEAD OF UPDATE
Linie În loc să se modifice o linie din tabela-țintă, se execută codul din acest declanșator
BEFORE DELETE
Comandă Se execută înaintea comenzii DELETE
BEFORE DELETE
Linie Se execută înainte de ștergerea fiecărei linii
AFTER DELETE
Linie Se execută după ștergerea fiecărei linii
AFTER DELETE
Comandă Se lansează după execuția comenzii DELETE (după ștergerea tuturor liniilor afectate de comandă)
INSTEAD OF DELETE
Linie Se execută în locul ștergerii unei linii din tabela-țintă
Proprietăţi ale triggerelor [5]:
1. Instrucţiunea CREATE TRIGGER trebuie să fie prima într-un batch şi se aplică unei singure tabele.
2. Deşi un trigger poate să facă referire la obiecte din afara bazei de date curente, el poate fi creat numai în baza de date curentă.
3. Acelaşi trigger poate fi asociat cu mai multe tipuri de acţiuni asupra unei tabele; practic, orice combinaţie dintre INSERT, DELETE şi UPDATE este permisă.
4. Orice operaţie de tip SET poate fi specificată în corpul unui trigger. Setările rămân valabile până la ieşirea din trigger după care revin la valorile iniţiale.
5. La executarea unui trigger se pot returna rezultate la fel ca în cazul procedurilor stocate. Acest lucru este, de regulă, nedorit în aplicaţii, de aceea trebuie evitate frazele SELECT sau atribuirile de variabile prin instrucţiunea SET. Orice trigger care conţine una dintre aceste tipuri de instrucţiuni necesită un tratament special în fiecare aplicaţie care ar putea activa triggerul. Prin instrucţiunea SET NOCOUNT plasată la începutul trigger-ului se poate evita returnarea de către trigger a oricărei relaţii rezultat.
6. Un trigger AFTER nu se poate asocia unei vederi. 7. O instrucţiune TRUNCATE TABLE nu activează eventualele triggere de
tip DELETE asociate tabelei curente.
32
Exemplu Trigger-ul de mai jos se declanșează la inserarea de informații în tabela
date_identificare (prin apelul procedurii init_chestionar pentru fiecare rând adăugat). Tabela menționată va fi populată la înregistrarea utilizatorilor în aplicație (prin completarea de către aceștia a unui formular Web sau direct în baza de date de către administrator) [8].
Rezultatul obținut după execuția trigger-ului (popularea chestionarului
pentru utilizatorul cu cnp-ul 1234567890000) este următorul [8]:
5.3 Funcții stocate Funcțiile stocate suportă doar execuția comenzii SELECT, acceptă doar
parametri de intrare și trebuie să returneze o singură valoare. Se utilizează, în general, când se dorește manipularea datelor sau efectuarea de calcule speciale.
La utilizarea parametrilor de intrare, este necesar să se specifice numele și tipul (orice tip valid de dată MySQL) acestora la declararea funcției.
33
Pentru exemplificarea funcțiilor stocate se va utiliza baza de date gestiunea_facturilor [8], prezentată mai jos.
Fig. 2. Baza de date gestiunea_facturilor [8]
Instrucţiunile SQL de creare a bazei de date şi a tabelelor acesteia au fost următoarele [8]:
34
Baza de date a fost populată cu înregistrările prezentate mai jos [8]: 35
Următoarea funcție [8] returnează toate facturile scadente din baza de date, împreună cu data scadenței acestora. Parametrii de intrare ai funcției sunt id_s și id_n (variabile identificate cu seria și numărul facturii în clauzele where din cadrul instrucțiunilor SELECT aflate în corpul funcției), data_f (reprezentând data facturii; valorile câmpului data_f sunt stocate succesiv în variabila @data_f prin utilizarea instrucțiunii SELECT prezentată mai jos) și termen_plată (variabilă de tipul int, corespunzătoare câmpului termen_plata de același tip; valorile acestui câmp sunt stocate în variabila @termen_plata prin utilizarea unei instrucțiuni SELECT). Printr-o
36
instrucțiune de atribuire SET, variabila @data_scadenta (care va fi returnată de funcție) ia, succesiv, valorile stocate în variabila @data_f la care se adaugă (prin intermediul funcției DATEADD(date, INTERVAL expr DAY)) termenul de plată al facturilor (ca număr zile).
Funcția este apelată într-o instrucțiune SELECT, iar la apel primește
ca parametri de intrare seria, numărul, data facturii și termenul de plată al acesteia. Efectul acestei instrucțiuni constă în afișarea tuturor facturilor scadente (serie și număr), împreună cu data scadentă a acestora [8].
Funcția de mai jos [8] returnează valoarea fiecărui produs dintr-o factură și ia ca parametri de intrare id-ul facturii (variabila corespunzătoare câmpului id_f din tabela linii_facturi), cantitatea produsului (cant_produs) și prețul unitar al produsului (pret_unitar_produs). Valorile stocate în câmpurile cant_produs și pret_unitar_produs sunt stocate în variabilele @c_p, respectiv, @p_u, prin intermediul clauzelor SELECT prezente în
37
corpul funcției. Valoarea fiecărui produs aflat pe stoc se calculează ca fiind produsul dintre cantitatea și prețul unitar al acestuia, informații reținute în variablele @c_p și @p_u. Rezultatul este rotunjit la 2 zecimale cu ajutorul funcției ROUND(valoare, 2) și stocat, apoi, în variabila @valoare_f, prin intermediul comenzii SET. Funcția returnează valorile reținute în variabila @valoare_f (valorile tuturor produselor de pe stoc).
Funcția definită mai sus este apelată prin numele ei și prin lista de parametri, într-o instrucțiune SELECT. Alături de valoarea produsului, comanda afișează și seria și numărul facturii, și denumirea produsului [8].
38
Următorul exemplu [8] prezintă o funcție stocată care returnează TVA-ul fiecărui produs dintr-o factură. Lista de parametri este aceeași cu cea din exemplul anterior, iar TVA-ul este calculat ca raport între produsul dintre cantitate și preț unitar (@c_p * @p_u) și valoarea 0.19. Rezultatul este rotunjit la 2 zecimale, este stocat în variabila @tva_p și este returnat de funcție.
Funcția este, de asemenea, apelată într-o instrucțiune SELECT prin nume și lista de parametri, alături de seria și numărul facturii și denumirea produsului, informații ordonate după serie și număr factură [8].
39
Următoarele instrucțiuni prezintă apeluri (prin nume și lista de parametri) în cadrul unor vederi, ale funcțiilor create mai sus [8].
În cadrul instrucțiunii SELECT de mai jos, sunt afișate seria și
numărul facturii și valorea totală a fiecărei facturi, cea din urmă obținută prin însumarea valorii fiecărui produs cu valoarea TVA-ului aferent acestuia. Valoarea fără TVA și valoarea TVA-ului sunt utilizate prin apelul informațiilor aferente din vederile create la pasul anterior [8].
40
6. Căutare rapidă. Indecși
Să presupunem că este necesară rularea unei interogări de forma:
pentru a selecta toate înregistrările care conțin produsul cu codul 2 (“Dezvoltare aplicatie Web”) facturat (din tabela linii_facturi a bazei de date gestiunea_facturilor). Serverul bazei de date va trebui să parcurgă întreaga tabelă linii_facturi linie cu linie, pentru a căuta toate liniile pentru care codul produsului este egal cu 2. Dacă tabela conține foarte multe linii, dintre care doar foarte puține (poate nici una) vor fi returnate de interogarea propusă, această metodă este clar ineficientă. Astfel, pentru a avea acces direct și rapid la liniile unei tabele, se vor folosi indecșii. Indecșii unei tabele funcționează similar cu indexul unei cărți de specializate [9]. Într-un astfel de index, aflat, de obicei, la sfârșitul unei cărți, se găsesc principalii termeni întâlniți în cartea respectivă, sortați alfabetic, indicându-se în dreptul fiecărui termen pagina sau paginile la care poate fi întâlnit indexul respectiv în carte. O persoană interesată de un anumit termen, nu va citi întreaga carte, ci va căuta în index pagina sau paginile corespunzătoare. Există două tipuri de indecși:
• Indecși unici – sunt generați automat pentru coloanele ce fac parte din cheia primară sau asupra căreia s-a definit o constrângere UNIQUE.
• Indecși non-unici – care sunt definiți de utilizator.
Pentru problema enunțată mai sus se va defini un index non-unic de forma:
41
Este indicată crearea unui index atunci când [9]:
• Coloana care se indexează conține o plajă mare de valori; • Coloana care se indexează conține mai multe valori nule (valorile
nule nu sunt incluse în index); • Una sau mai multe coloane sunt frecvent utilizate împreună în clauza
where sau în condițiile de join; • Tabela este mare și majoritatea interogărilor returnează un număr
mic de linii din această tabelă (aproximativ 5% din numărul total de înregistrări).
42
7. Prelucrări bazate pe cursoare
Operațiile de regăsire din SQL funcționează cu seturi de rânduri cunoscute sub numele de seturi de rezultate. Folosind instrucțiuni SELECT simple, nu există nici o modalitate de a regăsi primul rând, rândul următor sau precedentele 10 rânduri dintr-un set de rezultate. De asemenea, nu există nici posibilitatea de deplasare printre rânduri înainte sau înapoi cu mai multe rânduri deodată. Pentru aceasta, majoritatea SGBD-urilor pun la dispoziție cursoarele [6].
Un cursor este o interogare de baze de date stocată pe serverul SGBD – nu o instrucțiune SELECT, ci setul de rezultate regăsit de instrucțiunea respectivă. Odată stocat un cursor, aplicațiile pot derula sau parcurge datele în sus și în jos, după necesități [6].
O prelucrare bazată pe cursoare se desfășoară în mai multe faze după cum urmează [5]:
• Declararea unui cursor: unui nume de cursor i se asociază setul de înregistrări rezultat, corespunzător unei fraze SELECT; de asemenea, se specifică o serie de caracteristici ale cursorului.
• Deschiderea cursorului: se execută fraza select asociată, realizându-se ceea ce se numește “popularea cursorului”.
• Încărcarea cursorului: se poziționează cursorul în dreptul unei înregistrări și se realizează accesul la conținutul acesteia.
• Prelucrare: se execută operațiile specifice aplicației. • Închiderea cursorului: se șterge setul de înregistrări cu care s-a
populat cursorul, dar se menține cursorul în sine împreună cu definiția sa.
• Dealocarea cursorului: se șterge cursorul împreună cu definiția sa (operație care poate sau nu să fie efectuată, în funcție de SGBD-ul utilizat).
43
Fazele menționate sunt evidențiate în exemplul următor:
Pentru verificarea funcționării procedurii stocate se va realiza un apel al acesteia (cu comanda CALL urmată de numele procedurii), iar rezultatele vor fi afișate cu o comandă SELECT aplicată pe tabela clienti_temporar în care au fost reținute informațiile din procedură.
44
8. Drepturi și utilizatori într-un SGBD
Nimic nu este mai valoros pentru o organizație decât datele acesteia, motiv pentru care trebuie oprit accesul neautorizat la date. Evident, în același timp datele trebuie să fie accesibile utilizatorilor care au nevoie de ele, iar majoritatea programelor de tip SGBD furnizează utilizatorilor mecanisme de acordare, respectiv de limitare a accesului la date.
8.1 Sistemul de acces al drepturilor MySQL Procesul general de control al drepturilor se desfășoară în două stagii
distincte: autentificarea conexiunii și verificarea cererii. Împreună, aceste stagii sunt parcurse în cinci pași [7]:
1. MySQL utilizează conținutul tabelei user pentru a determina dacă să fie acceptată sau respinsă conexiunea inițiată. În cadrul acestui pas se verifică gazda și utilizatorul specificat cu un rând existent în tabela user. MySQL determină, de asemenea, dacă utillizatorul a solicitat o conexiune sigură și dacă numărul maxim de conexiuni permise pe oră pentru acel cont a fost depășit. Prin execuția pasului 1 se finalizează stagiul de autentificare a conexiunii.
2. În pasul al doilea se inițiază stagiul de verificare a cererii. În cazul în care conexiunea a fost acceptată, MySQL verifică dacă numărul maxim permis pe oră de interogări și actualizări a fost depășit. Apoi privilegiile aferente acordate în tabela user sunt examinate. Dacă sunt activate toate aceste drepturi (dacă sunt setate pe y), atunci utilizatorul are acces pe orice bază de date de pe server. Un server MySQL configurat corect are toate aceste drepturi dezactivate, ceea ce determină apariția pasului 3.
3. Este examinată tabela db pentru a identifica bazele de date la care are acces utilizatorul conectat la server. Toate drepturile active în acest tabel vor fi disponibile pentru toate tabelele bazelor de date pentru care are acces utilizatorul. Dacă nu este activat nici un drept, însă numele de utilizator și gazda sunt prezente, procesul sare la
45
pasul 5. Dacă numele de utilizator este regăsit, însă nu este prezent numele gazdei, procesul trece la pasul 4.
4. În cazul în care se găsește un rând în tabela db cu numele de utilizator specificat, însă numele gazdei este null, atunci va fi examinată tabela host. Dacă numele gazdei specificat în cadrul conexiunii se regăsește în acest tabel, atunci utilizatorul va primi drepturile specificate în tabela host, și nu pe cele specificate în tabela db pentru respectiva bază de date.
5. În final, dacă un utilizator intenționează să execute o comandă care nu este permisă în tabelele user, db sau host, atunci vor fi examinate tabelele tables_priv și columns_priv pentru a determina dacă utilizatorul are dreptul de a utiliza comanda respectivă pe tabelele sau coloanele specificate în cerere.
Informațiile cu privire la drepturile acordate de SGBD-ul MySQL sunt stocate în baza de date mysql, care se instalează implicit. Mai precis, 6 tabele existente în această bază de date joacă un rol important în procesul de autentificare și acordare a drepturilor [7]:
• user: determină care utilizatori se pot autentifica pe serverul de baze de date și de pe ce gazdă este permisă conexiunea.
• db: specifică bazele de date la care sunt permise conexiuni din partea unor utilizatori.
• host: reprezintă o extensie a tabelei db, oferind nume adiționale de gazde de pe care un utilizator se poate conecta la serverul de baze de date.
• tables_priv: determină ce utilizatori pot accesa anumite tabele. • columns_priv: determină ce utilizatori pot accesa anumite coloane. • procs_priv: gestionează utilizarea rutinelor stocate.
8.2 Gestiunea drepturilor și utilizatorilor Drepturile unui utilizator de a executa anumite instrucțiuni SQL pot fi:
• Drepturi de sistem – permit utilizatorilor să execute o gamă largă de instrucțiuni SQL, ce pot modifica datele sau structura bazei de date
46
(de exemplu: CREATE SESSION, CREATE TABLE, DROP TABLE, CREATE PROCEDURE, EXECUTE PROCEDURE, CREATE VIEW, CREATE USER, DROP USER, etc.).
• Drepturi la nivel de obiect – permit utilizatorilor să execute anumite instrucțiuni SQL numai în cazul schemei sale, și nu asupra întregii baze de date (de exemplu: SELECT, INSERT, UPDATE, DELETE, EXECUTE).
Crearea utilizatorilor
Pentru crearea de noi conturi de utilizator se folosește comanda CREATE USER. În momentul creării, nu se acordă nici un fel de drepturi, acestea putând fi atribuite prin utilizarea ulterioară a comenzii GRANT.
Comanda de mai jos crează utilizatorul sgbd cu parola parola_sgbd:
Pentru a verifica dacă aceasta s-a executat cu succes, se afișează conținutul tabelei user a bazei de date mysql:
Ștergerea utilizatorilor
Pentru a șterge un utillizator existent, se apelează comanda DROP USER.
47
Redenumirea utilizatorilor
Comanda RENAME USER redenumește un utilizator existent.
Acordarea și revocarea drepturilor
Gestiunea drepturilor de acces se realizează cu ajutorul comenzilor GRANT și REVOKE.
Tabelul de mai jos prezintă privilegiile gestionate de comenzile GRANT și REVOKE și comenzile SQL aferente [7].
Tabel 2. Drepturi gestionate de comenzile GRANT și REVOKE
Drept (privilegiu) Comandă SQL / Descriere
ALL PRIVILEGES Afectează toate privilegiile cu excepția WITH GRANT OPTION
ALTER ALTER TABLE
ALTER ROUTINE Afectează comenzile de modificare sau ștergere a rutinelor stocate
CREATE CREATE TABLE
CREATE ROUTINE Afectează comenzile de creare a rutinelor stocate
CREATE TEMPORARY TABLES
CREATE TEMPORARY TABLES
CREATE USER Afectează crearea, ștergerea, redenumirea și revocarea privilegiilor utilizatorilor
48
CREATE VIEW CREATE VIEW DELETE DELETE
DROP DROP TABLE
EXECUTE Afectează dreptul utilizatorului de a executa proceduri stocate
EVENT Afectează dreptul de a executa evenimente
FILE SELECT INTO OUTFILE și LOAD DATA INFILE
GRANT OPTION Afectează dreptul utilizatorului de a stabili privilegii
INDEX CREATE INDEX și DROP INDEX INSERT INSERT
LOCK TABLES LOCK TABLES PROCESS SHOW PROCESSLIST
REFERENCES Este prezentă pentru facilități viitoare oferite de MySQL
RELOAD FLUSH
REPLICATION CLIENT Afectează dreptul utilizatorului de a cere locația serverelor master și slave
REPLICATION SLAVE Acordă privilegii de replicare a serverelor slave
SELECT SELECT SHOW DATABASES SHOW DATABASES
SHOW VIEW SHOW CREATE VIEW SHUTDOWN SHUTDOWN
SUPER
CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS și SET GLOBAL (nivel de administrator)
TRIGGER Afectează dreptul de a executa trigger-e UPDATE UPDATE USAGE Doar conexiune, fără privilegii
În exemplul de mai jos, utilizatorul sgbd@localhost are drepturi de selectare și inserare din / în tabela produse a bazei de date gestiunea_facturilor.
Aceste drepturi au fost stocate în tabela db a bazei de date mysql prin
activarea acestora (setarea pe y).
49
Drepturile pot fi acordate unui utilizator și pe parcurs. De exemplu, utilizatorul sgbd@localhost poate avea și drepturi de actualizare a informațiilor din tabela produse:
Pentru acordarea unui dreptla nivel de coloană, comanda GRANT are forma următoare:
Modificarea poate fi vizualizată în tabela columns_priv a bazei de date mysql:
Drepturile pot fi retrase prin intermediul comenzii REVOKE. Spre exemplu, dacă se dorește retragerea permisiunii utilizatorului sgbd@localhost de a insera informații în tabela produse, se execută comanda de mai jos:
50
Vizualizarea tuturor privilegiilor unui utilizator
Pentru vizualizarea tuturor privilegiilor unui anumit utilizator, se folosește comanda SHOW GRANTS FOR.
De exemplu, pentru a vizualiza toate drepturile utilizatorului sgbd@localhost, se va executa comanda de mai jos:
Umătoarea instrucțiune afișează toate drepturile utilizatorului curent:
8.3 Limitarea resurselor utilizatorilor Consumul de resurse MySQL este gestionat la fel ca și alte privilegii,
prin intermediul tabelelor de privilegii. Există, în general, patru privilegii cu privire la utilizarea resurselor existente, acestea fiind situate în tabela user [7]:
• max_connections: determină numărul maxim de conexiuni pe oră pe care le poate iniția un utilizator.
51
• max_questions: determină numărul maxim de interogări pe oră (utilizând comenzi SELECT) pe care le poate executa un utilizator.
• max_updates: determină numărul maxim de actualizări pe oră (utilizând comenzi INSERT și UPDATE) pe care le poate executa un utilizator.
• max_user_connections: determină numărul maxim de conexini simultane pe care le poate iniția un utilizator.
De exemplu, comanda prezentată mai jos creează utilizatorul sgbd1@localhost cu drepturi de inserare, selecție și actualizare a înregistrărilor din tabela clienti a bazei de date gestiunea_facturilor cu un număr maxim de conexiuni pe oră de 3600.
Sunt afișate, apoi, drepturile noului utilizator:
Setarea limitărilor noului utilizator sunt vizibile în tabela mysql.user:
52
9. Gestiunea tranzacțiilor
În general, un SGBD deservește mai mulți utilizatori, care accesează concurent datele din tabele. Accesul concurent al utilzatorilor este asigurat prin capacitatea de multiprogramare a sistemului de operare al calculatorului gazdă, care permite execuția concurentă a mai multor procese. Execuția concurentă a mai multor procese poate avea loc atât într-un sistem uniprocesor, prin partajarea timpului de execuție al procesorului între mai multe procese, cât și într-un sistem multiprocesor în care mai multe procese pot fi executate real simultan, pe mai multe procesoare ale sistemului. Indiferent de numărul de procesoare ale sistemului, accesul concurent al mai multor utilizatori la datele memorate în tabelele unei baze de date necesită tehnici de menținere a consistenței și a siguranței datelor memorate. [10]
Menținerea consistenței și a siguranței bazelor de date în situația în care mai mulți utilizatori le accesează concurrent și în condițiile în care pot să apară erori de funcționare (defecte) ale sistemului de calcul se bazează pe conceptul de tranzacție. [10]
O tranzacție este o unitate logică de prelucrare care asigură consistența și siguranța bazei de date. În principiu, orice execuție a unui program se poate considera o tranzacție dacă baza de date este într-o stare consistentă atât înainte, cât și după execuția sa. Consistența bazei de date este garantată indiferent de faptul că tranzacția a fost executată în mod concurent cu alte tranzacții sau că au apărut defecte în timpul execuției tranzacției. [2]
În general, o tranzacție constă dintr-o secvență de operații de citire și scriere a bazei de date, la care se adaugă o serie de operații de calcul. Baza de date poate fi într-o stare temporar inconsistent în timpul executării tranzacției, dar trebuie să fie în stări consistente atât înainte, cât și după execuția tranzacției. [2]
Este redată, mai jos, sintaxa unei tranzacții MySQL:
START TRANSACTION
WITH CONSISTENT SNAPSHOT
53
| READ WRITE
| READ ONLY
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
Operația BEGIN marchează începutul execuției unei tranzacții, urmată de operații de citire sau scriere a înregistrărilor în baza de date (READ, WRITE).
Dacă tranzacția reușește să execute cu success toate operațiile prevăzute, atunci aceasta se va finaliza printr-o operație de validare (COMMIT). În schimb, dacă tranzacția nu reușește să-și execute complet toate operațiile, atunci se va declanșa operația de abortare (ROLLBACK).
Comanda de validare a unei tranzacții are dublu rol [2]:
• Indică SGBD-ului momentul de la care efectele tranzacției pot fi reflectate în baza de date și devin vizibile altor tranzacții;
• Marchează momentul începând de la care efectele tranzacției nu mai pot fi anulate (tranzacția nu se mai poate aborta) și modificările efetuate în baza de date devin permanente.
Operația de validare este vitală în cazul sistemelor concurente, unde este posibilă executarea în același timp a mai multor tranzacții care accesează aceeași bază de date. Prin validare se pot preveni o serie de fenomene nedorite cum este abortarea în cascadă a tranzacțiilor.
De reținut este faptul că anumite comenzi SQL nu pot fi abortate. În general, acestea sunt cele din categoria DDL, cum ar fi: CREATE / DROP DATABASE, CREATE, DROP / ALTER TABLES, CREATE, DROP / ALTER PROCEDURE, CREATE, DROP / ALTER FUNCTION, etc.
Comportamentul unei tranzacții poate fi controlat utilizând variabila numită AUTOCOMMIT. Dacă această variabilă este setată pe 1 (implicit), atunci fiecare comandă SQL este considerată o tranzacție completă și este
54
validată implicit după execuția sa. Când variabila AUTOCOMMIT este setată pe 0 (utilizând comanda SET AUTOCOMMIT=0), comenzile SQL care vor urma sunt considerate ca fiind o tranzacție și nicio activitate nu este validată până când va fi folosită o comanda COMMIT explicită.
Exemplu de execuție a unei tranzacții Studiu de caz: baza de date gestiunea_facturilor 1. Marcarea începutului execuției tranzacției:
2. Aplicarea unui discount de 100 de lei pentru produsul cu codul 2
(pentru câmpul pret_unitar_produs din tabela linii_facturi):
3. Majorarea prețului unitar al produsului cu codul 2 cu 100 lei:
4. Setarea codului 1 pentru produsul cu codul 5 din tabela linii_facturi:
5. Afișarea conținutului tabelei linii_facturi pentru a verifica dacă modificările au fost efectuate cu succes:
55
6. Până în momentul execuției comenzii COMMIT, modificările nu vor fi vizibile. În cazul în care una dintre operațiile de actualizare nu s-a efectuat cu succes, va fi apelată comanda ROLLBACK:
7. Se poate verifica rezultatul execuției acesteia, utilizând din nou
comanda SELECT pe tabela linii_facturi:
Practic, după rularea comenzii ROLLBACK, au fost anulate cele trei actualizări și s-a revenit la forma inițială a datelor.
8. Se aplică din nou moficicările, însă acestea vor fi urmate de o
comandă COMMIT în acest caz:
56
9. Comanda de validare aplicată este următoarea:
10. După execuția acesteia, modificările au următorul efect:
11. Se observă că după o comandă COMMIT, execuția comenzii ROLLBACK nu mai are nici un efect:
57
58
10. Replicarea bazelor de date
Replicarea bazelor de date este procesul prin care două sau mai multe baze de date aflate în site-uri (locaţii) diferite comunică între ele, transferând date şi informaţii. Astfel, sistemul de replicare conține unul sau mai multe servere care supervizează şi asigură transferul informaţional între sursă şi destinaţie, adică între terminalele unde se introduc, se modifică, se şterg şi se prelucrează datele. La rândul lor, bazele de date sunt grupate în: baza de date principală, care la orice moment reprezintă imaginea reală a întregului sistem, şi replici ale acesteia, care reprezintă imaginea reală a sistemului la un anumit moment de timp, când replica a fost creată [2], [11], [12], [13].
Fiecare server are bazele de date proprii, SGBD-ul propriu și un sistem de gestiune a comunicațiilor propriu. Utilizatorii locali ai fiecărui server pot exploata baza de date locală independent (la fel ca orice bază de date centralizată) sau pot exploata baza de date distribuită prin solicitarea accesului și la alte noduri ale rețelei. Astfel, baza de date distribuită apare ca un obiect virtual, rezultat al cooperării dintre mai multe baze de date locale. Această cooperare este realizată prin SGBDD care este o extensie logică a SGBD locale. În forma sa cea mai generală, o bază de date distribuită poate fi compusă din mai multe noduri dispersate geografic în care funcționează diferite tipuri de SGBD locale, sub diferite sisteme de operare și pe platforme hardware diferite. Este rolul SGBDD de a asigura conlucrarea acestor noduri și compatibilitatea dintre sistemele locale care funcționează în fiecare nod [2].
Exemplu Se vor utiliza trei servere, numite după cum urmează: Server 1, Server
2 şi Server de agregare. Server 1 conţine ambele două tabele ale bazei de date gestiunea_facturilor (clienti şi facturi), în timp ce Server2 conţine doar tabela facturi. Tabela facturi este invizibilă pentru utilizatorii PC-ului pe care rulează Server 1. Utilizatorii PC-ului cu Server 2 nu văd tabela clienti. Server 2 este într-o relaţie de master-slave bidirecţional cu Server 1. În acest mod, orice modificare din prima tabelă va adăuga o linie şi în a doua tabelă. De asemenea, modificările de pe Server 2 vor fi replicate pe Server 1. Server 1 şi Server 2 sunt dispersate geografic, iar serverul de agregare face replicarea ambelor tabele pentru backup şi pentru prelucrarea datelor [14].
59
Fig. 3. Schema procesului de replicare [14]
Configurarea serverului principal Într-o primă etapă, a fost necesară „instruirea” serverului MySQL
pentru a scrie log-uri pentru baza de date gestiunea_facturilor. Aceste log-uri au fost utilizate de către serverele secundare pentru a „vedea” ce modificări au fost executate pe serverul principal. Astfel, se adaugă următoarele linii în fişierul principal de configurare al MySQL (my.cnf) [14].
Pentru realizarea relaţiei de master-slave bidirecţional cu Server 2, se adaugă, în acelaşi fişier, liniile [14]:
replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 log-bin-index=mysql-bin.index log-slave-updates replicate-do-db=gestiunea_facturilor Următorul pas a constat în crearea unui utilizator MySQL cu drepturi
de replicare a bazei de date, astfel [14]: mysql -u root -padmin grant reload, super, replication client, replication slave on *.* to
'maria' @'192.168.1.2' identified by parola12'; grant reload, super, replication client, replication slave on *.* to
'maria' @'192.168.1.4' identified by 'parola12';
Se scriu, apoi, în fişierul my.cnf, următoarele linii [14]: master-host=192.168.1.2 master-user=maria master-password=parola12
61
Se execută în MySQL următoarele comenzi [14]: flush privileges; use gestiunea_facturilor; flush tables with read lock; unlock tables; show master status; Ultima comandă a avut ca rezultat următorul tabel [14]:
File Position Binlog_do_db Binlog_ignore_db
mysql-bin.000025 106 gestiunea_facturilor
Un exemplu de fişier mysql-bin este prezentat mai jos [14]:
Descrierea formatului
CREATE TABLE clienti
(id INT, telefon INT);
INSERT INTO clienti
VALUES (12, 0258810119);
.
.
.
mysql-bin.000025
fişier log
poziţia MySQL
eveniment
bi l
62
Se exportă datele într-un singur fişier de tip sql, care va fi transferat de pe serverul principal pe serverele secundare şi importat, mai apoi, în MySQL-ul de pe serverele secundare [14].
Configurarea serverelor secundare Prima etapă a fost cea a creării bazei de date gestiunea_facturilor pe
cele două servere de replicare secundare [14]: mysql -u root –padmin; create database gestiunea_facturilor; quit; Se importă, apoi, fişierul gestiunea_facturilor.sql în baza de date nou
creată: mysql -u root -padmin clie gestiunea_facturilor <
gestiunea_facturilor.sql; use gestiunea_facturilor; Se configurează bazele de date MySQL de pe serverele secundare să
lucreze ca servicii „slave” şi se declară „master” baza de date de pe serverul principal. Astfel, se realizează următoarele modificări în fişierele my.cnf de pe serverele secundare [14]:
master-connect-retry=60 replicate-do-db= gestiunea_facturilor Pentru realizarea relaţiei de master-slave bidirecţional cu Server 1, în
fişierul de configurare al MySQL, aflat pe Server 2, se adaugă liniile [14]: replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 log-bin=mysql-bin.log log-bin-index=mysql-bin.index log-slave-updates replicate-do-table= gestiunea_facturilor.facturi Ultima comandă a permis replicarea doar a celei de-a doua tabele din
baza de date. În final, se execută următoarele comenzi, pe ambele servere secundare
[14]: mysql -u root –padmin; slave stop; change master to master_host='192.168.1.3', master_user='maria',
Master_host reprezintă adresa IP a serverului principal, în cazul de faţă
192.168.1.3. Master_user reprezintă utilizatorul cu drepturi de replicare. Master_password reprezintă parola pentru „master_user” de pe serverul
principal.
64
Master_log_file este fişierul log rezultat la executarea comenzii „show master status” pe serverul principal.
Master_log_pos reprezintă poziţia MySQL rezultată la executarea aceleiaşi comenzi „show master status” pe serverul principal.
În consola de comandă MySQL se rulează comanda de pornire [14]: start slave; quit; show slave status \G;
La fel ca și în cazul bazelor de date centralizate, gestiunea operațiilor de blocare a fost realizată printr-o componentă a SGBD numită lock manager. În cazul bazei de date replicate, această gestiune a fost mai complexă deoarece a presupus transformarea cererilor de blocare, lansate de către utilizator asupra unor date logice, în operații de blocare a datelor fizice.
65
66
Bibliografie
[1] Fotache, Marin, SQL. Dialecte DB2, Oracle, PostgreSQL și SQL Server, Ediția a II-a, Editura Polirom, Iași, 2009, ISBN: 978-973-46-1339-7.
[2] Dollinger Robert, Andron Luciana, Baze de date și gestiunea tranzacțiilor, Editura Albastră, Cluj-Napoca, 2004, ISBN: 973-650-140-X.
[3] Dubois, Paul, trad. de Mihai Mănăstireanu, MySQL, Editura Teora, Bucureşti, 2001, ISBN: 973-20-0246-8.
[5] Dollinger, Robert, Andron, Luciana, Utilizarea sistemului SQL Server (SQL 7.0, SQL 2000), Editura Albastră, Cluj Napoca, 2004, ISBN: 973-650-141-8.
[6] Forta, Ben, SQL în lecții de 10 minute, Editura Teora, București, 2004, ISBN 10: 1-59496-029-1, ISBN 13: 978-1-59496-029-1.
[7] Gilmore, W. Jason, Beginning PHP and MySQL: From Novice to Professional, Fourth Edition, President and Publisher: Paul Manning, Apress Publishing House, 2010, ISBN-13 (pbk): 978-1-4302-3114-1, ISBN-13 (electronic): 978-1-4302-3115-8.
[8] Muntean, Maria, Olteanu, Emil, Baze de date. Aplicații, Editura Seria Didactica a Universității “1 Decembrie 1918” din Alba Iulia, 2014.
[9] Popescu, Carmen, Manual de informatică pentru clasa a XII-a, Editura L&S Informat, București, 2007, ISBN: 978-973-7658-11-1.
[10] Ionescu, Felicia, Baze de date relaționale și aplicații, Editura Tehnică, București, 2004, ISBN: 973-31-2207-6.
[11] Dragomir-Loga, G., C., Distribuirea resurselor în sistemele de baze de date. Dezvoltarea de metode de implementare, Teză de doctorat, Conducător Ştiinţific: Ignat, I., Universitatea Tehnică din Cluj-Napoca, 2007.
[12] Schneider, R., D., MySQL Database Design and Tuning, Sams Publishing, ISBN: 0-672-32765-1, 2005.
