Syhnthèse SQL2 (SQL3 Niveau 1)

1. Syhnthèse SQL2 (SQL3 Niveau 1) Professeur Serge MIRANDA Directeur Dess MBDS Université de Nice Sophia Antipolis serge.miranda@unice.fr (Tiré - du livre EYROLLES, S. Miranda, A.Ruols, « CLIENT-SERVEUR : concepts, moteurs SQL et architectures parallèles » 3ème édition en Juin 96 -du Livre DUNOD, S.Miranda, 2001 « Des bases de données relationnelles aux bases de données objets »

2. Généralités • Les 4 facettes de SQL Langage de définition (d ’un schéma relationnel) *** Langage de manipulation de la base de données * Langage de contrôle de la base de données (interface transactionnelle, …) **** SQL Langage de communication (client-serveur, Internet XML, BD réparties) **** • Notation : **** vaut le détour • *** mérite d ’être vu (3ème étoile en 89) • * il faut le voir mais … • SEQUEL : • « Structured English as a QUEry Language » • Prototype SYSTEM-R d ’IBM, SAN JOSE (1975)

3. Généralités • Les concepts structurels de SQL : • Tables • Colonnes • Lignes PILOTE PL# PLNOM ADRESSE 1 JEAN PARIS 2 PIERRE NICE 3 PAUL PARIS AVION AVNOM AV# CAP LOC 100 A300 300 NICE 101 B707 250 PARIS 102 A300 300 LYON 103 B727 370 LYON VOL PL# HD HA VOL# AV# VD VA IT100 1 100 NICE PARIS 7 8 IT101 2 100 PARIS NICE 11 12 IT103 1 103 LYON PARIS 14 15 IT104 2 102 NICE NICE 17 18

4. Généralités Modèle de Codd SQL (SEQUEL*) Français Anglais Relation Relation Table Domaine Domain Domain (SQL2) Attribut Attribute Column n-uplet (tuple) Tuple Line Clé primaire Primary key Primary key (ORACLE V6, DB2, V4, ….) Clé étrangère Foreign key References (ORACLE V6, DB2 V4, ….) * SEQUEL : « Structured English as a QUEry Language »

5. Définition du schéma Create schema < Création de deux domaines Ville et Heure > create domain1 Ville as char (12) default ‘ PARIS ’ check (value in(‘ PARIS ’, ‘ NICE ’, ‘ TOULOUSE ’)) create domain Heure as hour check (value > 7 and value < 22) < Création des tables > create table2 PILOTE (PL# decimal (4), PLNOM char (12), ADR Ville check (value in (‘ PARIS ’, ‘ NICE ’)), SAL decimal (5), primary key (PL#)) create table AVION (AV# decimal (4), AVNOM char (12), CAP decimal (3) check (value > 100), LOC Ville, primary key (AV#))

6. Définition du schéma Create table VOL (VOL# char(5), PL# decimal (4) not null, AV# decimal(4), VD Ville, VA Ville, HD Heure, HA Heure, primary key (VOL#), foreign key (PL#) references PILOTE, initially deferred, foreign key (AV#) references AVION, on delete cascade, on update set null)) alter domain …. Drop constraint Exemple : create domain Ville as char(12) constraint c-Ville check (value in (‘ PARIS ’, … ))

7. Définition du schéma • Type syntaxique Char(n), decimal, integer, bit, float, … date (year, month, day), times (hour, minute, second), timestamp, interval • Dictionnaire … relationnel (INFORMATION-SCHEMA CATALOG) comprenant des tables systèmes accessibles par …. SQL. ALTER TABLE (ADD COLUMN, … ) DROP TABLE

8. Définition du schéma • Dictionnaire Relationnel Intégré • Exemple : DB2 comprend un dictionnaire intégré • (« CATALOG ») qui comprend des tables systèmes. • Les PRINCIPALES NAME SYSTABLES CREATOR COLCOUNT Pilote Serge 4 Avion Serge 4 Vol Serge 7 SYSCOLUMNS NAME TBNAME COLTYPE PL# Pilote SMALLINT PLNOM Pilote CHAR ADR Pilote CHAR AV# Avion SMALLINT …. …. …. NAME SYSINDEX TBNAME CREATOR PX Pilote Serge AX Avion Serge VX Vol Serge

9. Définition du schéma • Cluster : « Jointure dans la structure physique » • (System-R, Oracle) • Exemple : Cluster entre PILOTE et VOL sur PL# • Bloc de cluster pour chaque valeur de PL# • Exemple d ’un bloc de cluster pour la valeur pl# = 10 10 NICE SERGE IT 100 10 PARIS 100 NICE 7 8 IT 101 10 NICE 101 PARIS 11 12 IT 105 10 PARIS 104 TOUL 15 46 Create cluster PV (PIL# number (4)) (clé du cluster)

10. Manipulation BD • 2 Interrogation et mise à jour d ’une base • de données (noyau SQL) • 2.1 SQL interactif • Interrogation : <Mapping SQL> • Select < liste attributs cible ou * > • from < tables > • where < qualification sur les lignes > avec in, • exists, any, all, and, or, not, between, like, … • group by < attributs partitionnés > • having < qualification sur les partitions > • order < tri > • union • Mise à jour : • update R • set • where • Suppression : • delete • from R • where • Insertion : • insert • into R • values

11. Manipulation BD • 2.2 SQL «  intégré » (« embedded SQL ») • Pointeur logique : « cursor » (« impedance mismatch ») • (exec sql) • declare CX cursor for < requête SQL > • (exec sql) • open / fetch / update / delete / close CX (cursor) Current (élément pointé) Code retour : sqlcode

12. Manipulation BD • Exemple 3 : Exemples de requête de partitionnement • (GROUP BY) • select * from vol ; VOL VOL# PL# AV# VD VA IT100 1 50 NICE PARIS IT101 2 50 PARIS TOUL IT103 2 50 TOUL PARIS IT104 1 51 PARIS NICE IT105 1 52 NICE LYON group by PL# : VD PL# = 1 VOL# AV# VA IT100 50 NICE PARIS IT104 51 PARIS NICE IT105 52 NICE LYON VD PL# = 2 VOL# AV# VA IT101 50 PARIS TOUL IT103 50 TOUL PARIS

13. Manipulation BD • Un peu de version ….. • Select PL#, COUNT(*) … • from VOL • … • group by PL# • having count (*)  3 ;

14. Manipulation BD • Traduction des requêtes SQL suivantes en Français • SQL : • select PL#, count (*) • from VOL • group by PL# • having count (*)  3 ; • « Quels sont les numéros de pilotes qui assurent plus de trois vols • (avec le nombre de vols assurés) ? » • SQL : • select PL#, count (*) • from VOL • where VD = ‘ NICE ’ (1) • group by PL# (2) • having count (*) > 3 ; (3) • Requête en français : • « Quels sont les numéros des pilotes (avec le nombre de vols assurés) • qui assurent plus de 3 vols au départ de Nice ? »

15. Manipulation BD DIFFERENCE INTERSECTION SQL1 SQL2 SQL1 SQL2 (select AV# from AVNICE) select AV# from AVNICE (select AV# from AVNICE) select AV# from AVNICE intersect where not exists except where exists (select AV# from AVAIRBUS) ; (select AV# from AVAIRBUS where AVAIRBUS.AV# = AVNICE.AV#); (select AV# from AVAIRBUS where AVAIRBUS.AV# = AVNICE.AV#); (select AV# from AVAIRBUS); D ’autres solutions existent avec in, = any, …... PROJECTION SELECTION select AV#, AVNOM from AVION. select * from AVION where CAP > 200 ;

16. Manipulation BD JOINTURE DIVISION « Qules sont les noms des pilotes en service au départ de Nice ? » Il existe 7 façons différentes dont les deux principales sont : - Solution prédicative : select PLNOM from PILOTE, VOL where VOL-VD = ‘ NICE ’ and PILOTE.PL# = VOL.PL# ; - Solution ensembliste : select PLNOM from PILOTE where PL# in (select PL# from VOL where VD = ‘ NICE ’); « Quels sont les numéros de pilotes qui conduisent TOUS les avions ? » select distinct VX.PL# from VOL VX where not exists (select AV# from AVION where not exists (select * from VOL VY where VY.PL# = VX.PL# and AVION.AV# = VY.AV#));

17. Manipulation BD (exemples) • Sous-requêtes « indépendantes » : Q27 : Quels sont les noms des avions dont la capacité est supérieure de 10% à la moyenne des capacités des avions ? • select * • from AVION • where CAP > • (select avg (CAP)* 1.1 • from AVION) ; • (2) est évaluable séparément de (1) (1) (2) Q28 : Quels sont les numéros des pilotes qui conduisent un avion conduit aussi par le pilote n° 100 ? • select distinct PL# • from VOL • where AV# in • (select AV# • from VOL • where PL# = 100);

18. Manipulation BD (exemples) • Avec un seul mapping, on aurait dû introduire une variable de parcours : • select distinct PL# • from VOL AS VX • where VOL.AV# = VX.AV# • and VX.PL# = 100; • Une double variable de parcours pourrait être introduite : • select distinct VX. PL# • from VOL AS VX, VOL AS VY • where VX. AV# = VY.AV# • and VY.PL# = 100;

19. Manipulation BD (exemples) • Sous-requêtes « dépendantes »: Q29 : Quels sont les avions dont la capacité est supérieure de 10 % à la moyenne de capacités des avions localisés dans la même ville ? • select * • from AVION AX • where AX.CAP > • (select avg (AY.CAP)* 1.1 • from AVION AY • where AX.LOC = AY.LOC); (1) (2) L’évaluration de (2) requiert la connaissance d ’une valeur AX.LOC de (1)

20. III Contrôle BD • Contrôle de la base de données (noyau SQL) • « TRANSACTIONS » • begin / end transaction • Commit (Work) • Rollback (work) • lock table in exclusive mode • /shared mode • « VIEWS » • create view as < requête SQL > • with check option • grant / revoke • (« public », « user », ….)

21. Contrôle BD • Vue (« view ») en SQL2 • Table virtuelle (seule la définition est stockée) • Exemple : Création d ’une vue de sécurité pour cacher • les lignes de PILOTE correspondant à des salaires • supérieurs à 50 000 F : • create view BAS-SALAIRE as • select * • from PILOTE • where SAL  50.000; • GRANT / REVOKE : • ex : • grant update on BAS-SALAIRE to SERGE

22. Contrôle BD • Transaction en SQL • Une transaction SQL est un ensemble d ’opérations SQL rendu • « atomique » (« tout ou rien ») qui permet de passer d’un état cohérent • de la BD dans un autre («  A et C » de ACID) • Dans SQL2, le début d ’une transaction est implicite (pas de verbe • BEGIN TRANSACTION) • et la fin correspond au verbe COMMIT • (terminaison correcte) ou ROLLBACK (annulation et retour arrière) T (« COMMIT » : Tout) état cohérent j de la base de données état cohérent i de la base de données T (« ROLLBACK » : Rien)

23. 3 . Contrôle BD Propriétés ACID d’une transaction • ACID • A : Atomicité • C : Cohérence • I : Isolation • (effets d’une transaction non observables par une autre) • D : Durabilité

24. 3 . Contrôle BD (Transaction) • Transaction « bien formée » (1) • chaque action de lecture est précédée de • LOCK - Read • chaque action d ’écriture est précédée de • LOCK - Update • « Verrouillage à 2 phases » (2) • phase de croissance d ’acquisition de verrous • (« SEIZE BLOCK ») • phase de décroissance de libération de verrous • Si (1) et (2) alors il ‘ y a sérialisibilité • des transactions concurrentes

25. 3 . Contrôle BD (Transaction) • Problème d ’interblocage T6 : Modifie la table VOL et modifie la table AVION. begin T6 …. lock table VOL in exclusive mode update VOL set …. lock table AVION in exclusive mode wait wait ….. T5 : Modifie la table AVION et modifie la table VOL. begin T5 ….. Lock table AVION in excusive mode update AVION set ….. lock table VOL in exclusive mode wait wait ….. t T5 qui a verrouillé AVION est en attente de VOOL; T6 qui a verrouillé VOL est en attente d ’AVION. Si aucune action n ’est entreprise, c ’est l ’attente infinie ou interblocage entre T5 et T6. L ’interblocage peut être généralisé à n transactions, avec n>2; il résulte l ’existence d ’un cycle dans le graphe d ’attente entre transactions.

26. 3 . Contrôle BD (Transaction) • Solution à l ’interblocage (DB2) • Construction d ’un graphe d ’attente T6 T5 T6 AVION VOL T5 • T : verrou exclusif posé par T • T : demande de verrou par T • Détection d ’un cycle dans le graphe d ’attente, puis choix d ’une victime • (ex : T6) qui devra défaire (ROLLBACK) ses actions. • Prévoir un test de présence d ’interblocage (ex : valeur négative de • l ’indicateur SQLCODE) dans le programme d ’application. • exec sql … • if sqlcode < 0 (valeur indiquant l ’interblocage) • then do • exec sql rollback • …. • end

27. Niveaux d’Isolation de SQL2 • Les dangers d’interférence n’ont pas le même niveau d’importance d’où la définition de niveaux d’isolation • NIVEAUX d’ISOLATION de ANSI/ISO et SQL2 • NIVEAU 0 READ UNCOMMITED : le plus permissif ; garantie d’absence de pertes de MAJ ; verrous Exclusifs posés avant écriture et libérés après • NIVEAU 1 READ COMMITED : + Cohérence des MAJ : libération des verrous en fin de transaction • NIVEAU 2 : READ REPEATABLE : + Cohérence des lectures par ajout d’un verrou partagé avant READ et libération en fin de lecture • NIVEAU 3 SERIALIZABLE : le plus contraignant avec libération des verrous en fin de transaction ; OK reproductibilité des lectures • Note : le niveau 3 des transactions n’interdit pas les tuples fantômes • Exemple : Oracle prend en compte par défaut le niveau READ COMMITTED et le niveau d’isolation peut être modifié par la commande ISOLATION LEVEL ‘serializable’ dans Set Transaction

28. VERROUILLAGE d ’INTENTION (2 nouveaux types de verrous) Conséquence granularité : Si T1 verrouille une table, il faut vérifier qu’il n ‘existe aucun verrou incompatible (cf matrice de compatibilité, Microsoft) sur chacun des tuples de la table (HIERARCHIE DE VERROUS)  Introduction du Verrouillage en intention de lecture et en intention d’écriture Nouvelle Règle : avant de verrouiller le tuple RO1, la transaction T1 doit poser un verrou d’intention sur la relation R contenant RO1 Si T2 veut verrouiller R il peut y avoir incompatibilté ; par contre T2 peut verrouiller RO2 de T avec un verrouillage d’intention compatible sur T Note : IBM a défini 6 nouveaux types de verrous avec 13 Combinaisons possibles, MICROSOFT 2 nouveaux types (Update, Schema..)… compatibilité VERROUS Intention Update Intention Read Update Read Intention Read OUI OUI OUI READ OUI OUI Intention UPDATE OUI OUI UPDATE

29. 2PC et REPLICATION 1) Systèmes de réplication (« SNAPSHOT ») : pour DWH, Architecture multi-tier, Internet sans fil.. EX : Oracle 8 : Create SNAPSHOT Volparis as <requête SQL> avec raffraichissements réguliers des copies (complet ou « rapide ») ; réplication de tables, index, vue, trigger ou package ARCHITECTURES DE REPLICATION : - architectures maître esclave (DIFFUSION sites primaires, CENTRALISATION, CICULAIRE et CASCADE) - architectures symétriques (« update anywhere) avec mises à jour asynchrônes (contrôle centralisé ou réparti) ou synchrônes (2PC et RPC) EX SQL Server de Microsoft : Modèle « PUBLISH and SUBSCRIBE » (Editeur/ Soucripteur) avec 3 types de réplication : SNAPSHOT (read only comme DWH), TRANSACTIONAL (cohérence faible, envoi TI validee) et MERGE (cohérence forte) 2) 2PC : « Two-phase commit »: Protocole de terminaison à 2 phases pour les infostructures réparties (client serveur, réplication, BDR, Teleservice Internet) sur réseau fiable Seule façon d’assurer une COHERENCE FORTE EX : dans Oracle 8 : 2PC automatique lors du Commit d‘une Ti répartie

30. 3 . Contrôle BD (Reprise sur pannes et transactions) • Mécanismes de Reprise sur panne (DB2) • Journal de modification (« LOG ») et points de reprise • Protocole d ’écriture anticipée sur le journal • (« write -ahead log protocol ») • Paradigme faire/défaire/refaire • (« do, undo, redo ») • Reprise à chaud / à froid • Ecriture sur double (« shadow mechanism ») d’ IBM et • Oracle (« segment de ROLLBACK ») • mécanisme « Multiversion » avec • SCN (« System Change Number »)d’Oracle • Note : FALLBACK de TERADATA

31. JOURNAL DE MODIFICATION (LOG) Seule Opération atomique : E/S d’un bloc JOURNAL pour chaque T : IMAGE AVANT de chaque MAJ, IMAGE Après de chaque MAJ, Enregistrement BEGIN, Enregistrement END (Commit, Abort), Enregistrements de SAVE Point, Etat transaction Objectifs de la reprise sur panne : - Modifications des transactions non validées doivent être ignorées - Modifications -//- validées doivent être prises en compte

32. 3 . Contrôle BD (Transaction) • Protocole « Faire / défaire / refaire » Ancien état de l ’objet (image avant ) Nouvel état de l ’objet (image après ) FAIRE écriture JOURNAL Nouvel état de l ’objet Ancien état de l ’objet DEFAIRE lecture JOURNAL Ancien état REFAIRE Nouvel état lecture JOURNAL

33. API SQL2 • Interfaces de programmation • L ’application (API) avec SQL • - SQL dynamique, • - intégration dans langage hôte (« embedded SQL »), • - langage du module. • SQL dynamique • Trois types de verbes SQL dynamiques : • execute immediate • prepare from et execute; • (deallocate prepare pour la supprimer); • prepare from et manipulation des curseurs. • (open, fetch, close, update et delete current). • SQLDA (« SQL descriptor area »)

34. API SQL2 • Intégration dans langage hôte • exec sql begin and declare section. • Langage du module • Le langage de module est un petit langage pour écrire des requêtes SQL. • MODULE : = ensemble de procédures • PROCEDURE : = un ensemble de définitions, de paramères • et une seule requête SQL exprimée avec ces • paramètres.

35. Critiques de SQL • SQL et l'algèbre relationnelle : • SQL n ’est pas un …. • « bon » langage relationnel ! • SQL et les langages de programmation : • SQL n ’est pas un …. • « bon » langage de programmation !