SQL2. Bases de données, SQL et types de données
Voici le premier chapitre d'un long article
concernant le SQL et son implémentation dans les SGBD les plus courants
(Paradox, Access, Oracle, SQL Server, Sybase…).
Tout le monde connaît le SQL, mais l’avez-vous suffisamment fréquenté
pour en tirer toute l’essence ?
Dans cet article, nous allons voir l’historique de ce langage et ses
différentes composantes.
Dans les suivants, nous verrons dans l’ordre la manipulation des
données à l’aide des commandes SELECT, INSERT, UPDATE, DELETE, puis la
création des structures de bases de données avec CREATE, ALTER, DROP et
enfin l’attribution et la révocation des droits (GRANT et REVOKE).
Mais au fait, le SQL, bientôt mort ?… Pas si sûr !
Préambule 1. SQL un langage ? 2. SQL une histoire... 2.1. SQL une norme 2.2. SQL un standard 3. Remarques préliminaires sur les SGBDR 4. Les subdivisions du SQL 4.1. DDL : " Data Definition Language " 4.2. DML : " Data Manipulation Language " 4.3. DCL : " Data Control Language " 4.4. TCL : " Transaction Control Language " 4.5. SQL intégré : " Embedded SQL " 5. Implémentation physique des SGBDR 5.1. SGBDR "fichier" 5.2. SGBDR "Client/Serveur" 6. Type de données 6.1. Types alphanumériques 6.2. Types numériques 6.3. Types temporels 6.4. Types " BLOBS " (hors du standard SQL 2) 6.5. Autres types courants, hors norme SQL 92 6.6. Les domaines, ou la création de types spécifiques 7. Contraintes de données 8. Triggers et procédures stockées 9. Résumé 10. Conclusion PréambuleParler du SQL de nos jours comme d’une
nouveauté, serait une gageure… cependant, ne faut-il pas voir en cet
indestructible langage, une tentative un peu tardive, mais souhaitable,
au travers des différents middleware disponibles, de standardisation
d’un mode d’interrogation des données ? Force est de constater que même les bases de données objet et le web se mettent au SQL. Le poids du passé sans doute... Mais alors que faire ? Squeezer SQL ou s’en accommoder ? Il y a plus d’une vingtaine d’années le
COBOL était, disait-on, assuré d’une mort certaine et à court terme.
Aujourd’hui le défunt est encore haletant bien que défraîchi. En sera
t-il de même pour le SQL ? Tout le laisse supposer ! 1. SQL un langage ?Et d’abord, SQL est-il un vrai langage ? Si l’on
doit accepter ce mot au sens informatique du terme, il semble difficile
de dire oui tant SQL est loin de la structure et des possibilités d’un
langage de programmation courant. Point de variable, point de procédure
ou de fonction… Pourtant il s’agit bien de former des phrases qui seront
compilées afin d’exécuter des traitements. Même si SQL s’est doté au fil du temps d’extensions, comme la possibilité de paramétrer les requêtes, il y a des lacunes importantes qui sont autant de frein à sa pénétration. Par exemple SQL ne sait être récursif [1] alors que ce mode d’exécution est nécessaire pour résoudre une frange importante de problèmes, notamment ceux pour traiter les arbres ou les graphes. En fait SQL est un langage de type "
déclaratif ". On spécifie ce que l’on veut obtenir ou faire et c’est la
machine qui décide comment elle doit l’exécuter. Pour certains, SQL est
perçu comme un pis-aller tandis que d’autres préfèrent l’éviter ou
retarder au plus l’inéluctable moment où son apparition sera
incontournable. La différence fondamentale entre les
langages courants comme C ou Pascal, qui sont des langages procéduraux,
réside dans le fait qu’avec ces derniers, vous indiquez l’ensemble des
instructions nécessaires à traiter un problème. Vous gardez ainsi une
certaine maîtrise sur le cycle d’exécution et l’enchaînement des
différentes tâches d’un programme vous est parfaitement connu. En
revanche, dans SQL vous n’avez, et d’ailleurs ne devez, pas avoir la
moindre idée de comment la machine exécute votre demande, ni même dans
quel ordre elle décompose le traitement de la requête en différentes
tâches, ni d’ailleurs même comment elle les synchronise. Lors d’une extraction concernant
plusieurs tables, le moteur relationnel pourrait parfaitement lancer
plusieurs traitements en parallèles pour extraire les données de chacune
des tables, puis effectuer les jointures des différentes tables
résultantes en fonction de l’ordre de terminaison des extractions... C’est pourquoi, les moteurs relationnels incluent des modules d’optimisation logique et parfois statistique. Ainsi un optimiseur logique préférera
traiter en premier les clauses excluant de la réponse un maximum de
données, tandis qu'un optimiseur statistique commencera par traiter les
tables de plus faible volume.
Bref, pour conclure cette brève
présentation de SQL, nous pouvons affirmer que, malgré ses défauts, et
en attendant un futur langage objet pour l’interrogation des données,
incluant tous les mécanismes dynamiques et bien entendu la récursivité [1],
il nous faut continuer à utiliser sagement SQL, du mieux que nous
pouvons, en rendant ainsi hommage a quelques-uns de ses créateurs... 2. SQL une histoire...Nous sommes en 1970. Le docteur Codd, un
chercheur d’IBM à San José, propose une nouvelle manière d’aborder le
traitement automatique de l’information, se basant sur la théorie de
l’algèbre relationnel (théorie des ensembles et logique des prédicats).
Cette proposition est faite afin de garantir une plus grande
indépendance entre la théorie et l’implémentation physique des données
au sein des machines. C’est ainsi que naîtrons, vers la fin des années
70, les premières applications basées sur la proposition de Ted Codd,
connues de nos jours sous l’acronyme SGBDR (signifiant Système de
Gestion de Bases de Données Relationnelles). Dans cette même période, Peter CHEN tente
une approche pragmatique et conceptuelle du traitement automatique des
données en proposant le modèle Entité-Association comme outil de
modélisation. Nous sommes en 1976. En parallèle, différents chercheurs
travaillent à réaliser ce que seront les SGBDR d’aujourd’hui : citons
entre autres l’équipe de Gene Wong à l’université de Berkeley qui entame
le projet INGRES en 1972, et en 1975 propose le langage QUEL comme
outil d’interrogation des données (aucun ordre de mise à jour des
données n’y figure). De même à Noël 1974 démarre chez IBM le projet qui portera le nom de System R, et donnera comme langage d’interrogation SEQUEL (Structured English QUEry Langage) en 1976. Lors d’une conférence internationale à
Stockolm (IFIP Congres), Larry Ellison, dirigeant d’une petite
entreprise appelée Software Development Laboratories entend parler du
projet et du langage d’interrogation et de manipulation des données. Il
rentre en contact avec les chercheurs d’IBM. Quelques années après,
Larry Ellison sort le premier SGBDR commercialisé et change le nom de sa
société. Elle s’appellera désormais Oracle... IBM, pour sa part, sortira une version
commerciale bien après celle d’Oracle, ce qui fera dire à l’un des
responsables du projet que " cela vous montre combien de temps il faut à
IBM pour faire n’importe quoi... ". Finalement la première installation
de System R est réalisée en 1977 chez Pratt & Whitney, et déjà les
commandes du langage d’interrogation se sont étoffées de quelques
judicieuses techniques préconisées par Oracle, comme la gestion des
curseurs. Au même moment apparaissent d’autres
langages d’interrogation comme QBE de Zloof (IBM 1977) repris pour
Paradox par Ansa Software, ou REQUEST de Fred Damerau (basé sur le
langage naturel) ou encore RENDEZ VOUS (1974) de l’équipe de Ted Codd
ainsi que SQUARE (Boyce 1975). Mais le point critique du langage SEQUEL porte sur l’implémentation du ... rien ! Ou plutôt devrais-je dire du nul !!! Finalement la première réalisation connue
sous le nom de SQL (82) voit le jour après l’arrivée de DB2, une
avancée significative du System R d’IBM datant de mars 1979. On peut donc dire que le langage SQL
est né en 1979, mais baptisé SQL en 1982. En définitive SQL a vingt ans,
et, comme le dit la chanson ... on n’a pas tous les jours 20 ans... Et 20 ans pour les uns ça suffit !, pour les autres ça se fête... 2.1. SQL une normeNotons que SQL sera normalisé à quatre
reprises : 1986 (SQL 86 - ANSI), 1989 (ISO et ANSI) et 1992 (SQL 2 - ISO
et ANSI) et enfin 1999 (SQL:1999 - ISO) souvent apellé à tort (y
compris par moi même !) SQL 3. A ce jour, aucun SGBDR n'a implémenté la
totalité des spécifications de la norme actuellement en vigueur. Mais je
dois dire que le simple (?) SELECT est argumenté de quelques 300 pages
de spécifications syntaxiques dans le dernier document normatif... Néanmoins, la version SQL 2 (1992) est la
version vers laquelle toutes les implémentations tendent. C'est pourquoi
nous nous baserons sur SQL 2. A noter, la future norme SQL:2003, sortira en juillet 2003. 2.2. SQL un standardMême si SQL est considéré comme le
standard de toutes les bases de données relationnelles et
commercialisées, il n'en reste pas moins vrai que chaque éditeur tend à
développer son propre dialecte, c'est à dire à rajouter des éléments
hors de la norme. Soit fonctionnellement identiques mais de syntaxe
différentes (le LIKE d'Access en est l'exemple le plus stupide !) soit
fonctionnellement nouveau (le CONNECT BY d'Oracle pour la récursivité
par exemple). Il est alors très difficile de porter une base de données
SQL d'un serveur à l'autre. C'est un moindre mal si l'on a respecté au
maximum la norme, mais il est de notoriété absolue que lorsque l'élément
normatif est présent dans le SGBDR avec un élément spécifique ce sera
toujours l'élément spécifique qui sera proposé et documenté au détriment
de la norme ! Exemple, la fonction CURRENT_TIMESTAMP,
bien présente dans MS SQL Server, n'est pas spécifié dans la liste des
fonctions temporelle de l'aide en ligne !!!
De plus, le plus petit dénominateur
commun entres les 4 poids lourds de l'édition que sont Oracle, Sybase
(ASE), Microsoft (SQL Server) et IBM (DB2) est tel qu'il est franchement
impossible de réaliser la moindre base de données compatible entre ces
différentes implémentations, sauf à n'y stocker que des données
numériques !!! Pour chaque SGBDR, on parle alors de dialecte. Il y a donc le dialecte SQL d'Oracle, le dialecte SQL de Sybase...etc. Nous avons aussi démontré qu'une même
table créée sur différents SGBDR avec les mêmes données n'avait par
forcément le même comportement au regard du simple SELECT du fait des
jeux de caractères et collations par défaut. Or tous les SGBDR ne sont
pas paramétrables à ce niveau et ceux qui le sont, ne présentent pas en
général les mêmes offres en cette matière. A lire : Une question de caractères... Enfin, pour tous ceux qui veulent
connaître la norme comparée au dialecte de leur SGBDR favori, il suffit
de lire le tableau de comparaison des fonctions de SQL : Toutes les fonctions de SQL 3. Remarques préliminaires sur les SGBDRAvant tout, nous supposerons connues les notions de "bases de données", "table", "colonne", "ligne", "clef", "index", "intégrité référentielle" et "transaction", même si ces termes, et les mécanismes qu'ils induisent seront plus amplement décrits au fur et à mesure de votre lecture. Voici quelques points qu'il convient d'avoir à l'esprit lorsque l'on travaille sur des bases de données :
4. Les subdivisions du SQLLe SQL comporte 5 grandes parties, qui permettent : la définition des éléments d'une base de données (tables, colonnes, clefs, index, contraintes...), la manipulation des données (insertion, suppression, modification, extraction…), la gestion des droits d'accès aux données (acquisition et révocation des droits), la gestion des transactions et enfin le SQL intégré.
La plupart du temps, dans les bases de données "fichier" (dBase,
Paradox...) le SQL n'existe qu'au niveau de la manipulation des données,
et ce sont d'autres ordres spécifiques qu'il faudra utiliser pour créer
des bases, des tables, des index ou gérer des droits d'accès. Certains auteurs ne considèrent que 3 subdivisions incluant la gestion des transactions au sein de la manipulation des données... Cependant ce serait restreindre les fonctionnalités de certains SGBDR capable de gérer des transactions comprenant aussi des ordres SQL de type définition des données ou encore gestion des droits d'accès ! 4.1. DDL : " Data Definition Language "C'est la partie du SQL qui permet de créer des bases de données, des tables, des index, des contraintes…
Elle possède les commandes de base suivante :
Qui permettent respectivement de créer, modifier, supprimer un élément de la base. 4.2. DML : " Data Manipulation Language "C'est la partie du SQL qui s'occupe de traiter les données.
Elle comporte les commandes de base suivantes :
Qui permettent respectivement d'insérer, de modifier de supprimer et d'extraire des données. 4.3. DCL : " Data Control Language "C'est la partie du SQL qui s'occupe de gérer les droits d'accès aux tables.
Elle comporte les commandes de base suivantes :
Qui permettent respectivement d'attribuer et de révoquer des droits. 4.4. TCL : " Transaction Control Language "C'est la partie du SQL chargé de contrôler la bonne exécution des transactions.
Elle comporte les commandes de base suivantes :
Qui permettent de gérer les propriétés ACID des transactions. 4.5. SQL intégré : " Embedded SQL "Il s'agit d'éléments procéduraux que l'on intégre à un langage hôte :
Le terme "ACID" fait référence aux termes suivants : A - Atomicité : une transaction
s'effectue ou pas (tout ou rien), il n'y a pas de demi-mesure. Par
exemple l'augmentation des prix de 10% de tous les articles d'une table
des produits ne saurait être effectuée partiellement, même si le système
connaît une panne en cours d'exécution de la requête. C - Cohérence : le résultat ou les
changements induits par une transaction doivent impérativement
préserver la cohérence de la base de données. Par exemple lors d'une
fusion de société, la concaténation des tables des clients des
différentes entités ne peut entraîner la présence de plusieurs client
ayant le même identifiant. Il faudra résoudre les conflits portant sur
le numéro de client avant d'opérer l'union des deux tables. I - Isolation : les transactions
sont isolées les unes des autres. Par exemple la mise à jour des prix
des articles ne sera visible pour d'autres transactions que si ces
dernières ont démarré après la validation de la transaction de mise à
jour des données. Il n'y aura donc pas de vue partielle des données
pendant toute la durée de la transaction de mise à jour. D - Durabilité : une fois
validée, une transaction doit perdurer, c'est à dire que les données
sont persistantes même s'il s'ensuit une défaillance dans le système.
Par exemple, dès lors qu'une transaction a été validée, comme la mise à
jour des prix, les données modifiées doivent être physiquement stockées
pour qu'en cas de panne, ces données soient conservées dans l'état où
elles ont été spécifiées à la fin de la transaction. Pour assurer l'ensemble de ces fonctions
de base, les SGBDR utilisent le principe de la journalisation : un
fichier dit "journal", historise toutes les transactions que les
utilisateurs effectuent et surtout leur état : en cours, validé ou
annulée. Une mise à jour n'est réellement effectuée que si la
transaction aboutit. Ainsi en cas de panne du système, une relecture du
journal permet de resynchroniser la base de données pour assurer sa
cohérence. De même en cas de "RollBack", les instructions de la
transaction sont lues "à l'envers" afin de rétablir les données telles
qu'elles devaient être à l'origine de la transaction. 5. Implémentation physique des SGBDRIl existe à ce jour, deux types courant
d'implémentation physique des SGBD relationnels. Ceux qui utilisent un
service de fichiers associés à un protocole de réseau afin d'accéder aux
données et ceux qui utilisent une application centralisée dite serveur
de données. Nous les appellerons SGBDR "fichier" et SGBDR client/serveur
(ou C/S en abrégé). 5.1. SGBDR "fichier"Le service est très simple à réaliser :
il s'agit de placer dans une unité de stockage partagée (en général un
disque d'un serveur de réseau) un ou plusieurs fichiers partageables. Un
programme présent sur chaque poste de travail assure l'interface pour
traiter les ordres SQL ainsi que le va et vient des fichiers de données
sur le réseau. Il convient de préférer des SGBDR à forte granularité au niveau des fichiers. En effet plus il y a de fichiers pour une même base de données et moins la requête encombrera le réseau, puisque seuls les fichiers nécessaires à la requête seront véhiculés sur le réseau. Ces SGBDR "fichier" ne proposent en
général pas le contrôle des transactions, et peu fréquemment le DDL et
le DCL. Ils sont, par conséquent, généralement peu ACID ! Les plus connus sont ceux qui se
reposent sur le modèle XBase. Citons parmi les principaux SGBDR
"fichier" : dBase, Paradox, Foxpro, BTrieve, MySQL, … Généralement basés
sur le modèle ISAM de fichiers séquentiels indexés. Avantage : simplicité du fonctionnement, coût peu élevé voire gratuit, format des fichiers ouverts, administration quasi inexistante. Inconvénient : faible capacité de
stockage (quoique certains, comme Paradox, acceptent 2 Go de données par
table !!!), encombrement du réseau, rarement de gestion des
transactions, faible nombre d’utilisateurs, faible robustesse, cohérence
des données moindre. Access se distingue du lot en étant
assez proche d’un serveur SQL : pour une base de données, un seul
fichier et un TCL. Mais cela présente plus d’inconvénients que
d’avantages : en effet pour interroger une petite table de quelques
enregistrements au sein de base de données de 500 Mo, il faut rapporter
sur le poste client, la totalité du fichier de la base de données... Un
non sens absolu, que Microsoft pali en intimant à ses utilisateurs de
passer à SQL Server dès que le nombre d’utilisateurs dépasse 10 ! 5.2. SGBDR "Client/Serveur"Le service consiste à faire tourner sur
un serveur physique, un moteur qui assure une relative indépendance
entre les données et les demandes de traitement de l'information venant
des différentes applications : un poste client envoie à l'aide d'un
protocole de réseau, un ordre SQL (une série de trames réseau), qui est
exécuté, le moteur renvoie les données. De plus le SGBDR assure des
fonctions de gestions d'utilisateurs de manière indépendante aux droits
gérés par l'OS. A ce niveau il convient de préférer des
SGBDR C/S qui pratiquent : le verrouillage d'enregistrement plutôt que
le verrouillage de page (évitez donc SQL Server...), et ceux qui
tournent sur de nombreuses plates-formes système (Oracle, Sybase...).
Enfin certains SGBDR sont livrés avec des outils de sauvegarde et
restauration. Les SGBDR "C/S" proposent en général la
totalité des services du SQL (contrôle des transactions, DDL et DCL).
Ils sont, par conséquent, pratiquement tous ACID. Enfin de plus en plus
de SGBD orientés objets voient le jour. Dans ce dernier cas, ils
intègrent la plupart du temps le SQL en plus d'un langage spécifique
d'interrogation basé sur le concept objet (O², ObjectStore, Objectivity,
Ontos, Poet, Versant, ORION,GEMSTONE...) Les serveurs SQL C/S les plus connus sont : Oracle, Sybase, Informix, DB2, SQL Server, Ingres, InterBase, SQL Base... Avantage : grande capacité de
stockage, gestion de la concurrence dans un SI à grand nombre
d’utilisateurs, haut niveau de paramétrage, meilleure répartition de la
charge du système, indépendance vis à vis de l'OS, gestion des
transactions, robustesse, cohérence des données importante. Possibilité
de montée en charge très importante en fonction des types de plateformes
supportées. Inconvénient : lourdeur dans le
cas de solution "monoposte", complexité du fonctionnement, coût élevé
des licences, administration importante, nécessité de machines
puissantes. 6. Type de donnéesDernier point que nous allons aborder dans ce
premier article, les différents types de données spécifiés par SQL et
leur disponibilité sur les 5 systèmes que nous avons retenus pour notre
étude. Selon la norme ISO de SQL 92 6.1. Types alphanumériquesCHARACTER (ou CHAR) : valeurs alpha de longueur fixe CHARACTER VARYING (ou VARCHAR ou CHAR VARYING) : valeur alpha de longueur maximale fixée Ces types de données sont codés sur 2 octets (EBCDIC ou ASCII) et on doit spécifier la longueur de la chaîne. Exemple :
NATIONAL CHARACTER (ou NCHAR ou NATIONAL CHAR) : valeurs alpha de longueur fixe NATIONAL CHARACTER VARYING (ou NCHAR VARYING ou NATIONAL CHAR VARYING) : valeur alpha de longueur maximale fixée sur le jeu de caractère du pays Ces types de données sont codés sur 4 octets (UNICODE) et on doit spécifier la longueur de la chaîne. Exemple :
Nota : la valeur maximale de la longueur est fonction du SGBDR. 6.2. Types numériquesNUMERIC (ou DECIMAL ou DEC) : nombre décimal à représentation exacte à échelle et précision facultatives INTEGER (ou INT): entier long SMALLINT : entier court FLOAT : réel à virgule flottante dont la représentation est binaire à échelle et précision obligatoire REAL : réel à virgule flottante dont la représentation est binaire, de faible précision DOUBLE PRECISION : réel à virgule flottante dont la représentation est binaire, de grande précision BIT : chaîne de bit de longueur fixe BIT VARYING : chaîne de bit de longueur maximale Pour les types réels NUMERIC, DECIMAL,
DEC et FLOAT, on doit spécifier le nombre de chiffres significatifs et
la précision des décimales après la virgule. Exemple :
signifie que le nombre comportera au plus 15 chiffres significatifs dont deux décimales. ATTENTION : le choix entre le type
DECIMAL (représentation exacte) et le type FLOAT ou REAL
(représentation binaire) doit être dicté par des considérations
fonctionnelles. En effet, pour des calculs comptables il est
indispensable d'utiliser le type DECIMAL exempt, dans les calculs de
toute fraction parasite capable d'entraîner des erreurs d'arrondis. En
fait le type DECIMAL se comporte comme un entier dans lequel la virgule
n'est qu'une représentation positionnelle. En revanche pour du calcul
scientifique on préférera utiliser le type FLOAT, plus rapide dans les
calculs. Exemple :
NOTA : on peut utiliser une notation
particulière pour forcer le typage implicite. Il s'agit d'une lettre
précédent la chaîne à transtyper. Les lettres autorisées, sont : N (pour
Unicode), B pour binary (chaine de 0 et 1) et X pour binary (chaine
hexadécimale constituées de caractères allant de 0 à F). Exemple :
6.3. Types temporelsDATE : date du calendrier grégorien TIME : temps sur 24 heures TIMESTAMP : combiné date temps INTERVAL : intervalle de date / temps Rappelons que les valeurs stockées doivent avoir pour base le calendrier grégorien [2]
qui est en usage depuis 1582, date à laquelle il a remplacé le
calendrier julien. En matière de temps, la synchronisation s’effectue
par rapport au TU ou temps universel (UTC : Universal Time Coodinated)
anciennement GMT (Greenwich Mean Time) l’ensemble ayant été mis en
place, lors la conférence de Washington DC en 1884, pour éviter que les
chemins de chemins ne se télescopent. ATTENTION : Le standard ISO adopté
pour le SQL repose sur le format AAAA-MM-JJ. Il est ainsi valable
jusqu’en l’an 9999… ou AAAA est l’année sur 4 chiffres, MM le mois sur
deux chiffres, et JJ le jour. Pour l'heure le format ISO est
hh:mm:ss.nnn (n étant le nombre de millisecondes) Exemple :
est le 26 mars 1999 à 22h 54m, 28s et 123 millisecondes. Mais peu de moteurs de requêtes l’implémente de manière aussi formelle... Le type INTERVAL est très particulier. Il est malheureusement rarement présent dans les SGBDR.
Sa définition se fait à l'aide de la syntaxe suivante :
où précision_min et précision_max peuvent prendre les valeurs :
avec la condition supplémentaire suivante : précision_max ne peut être qu'une mesure temporelle plus fine que précision_min. Exemple :
ATTENTION Veuillez noter que les réceptacles de valeurs temporelles ainsi créés par le type INTERVAL sont des entiers, et que leur valeur est contrainte lorsqu'ils sont définis avec une précision maximum sur tous les éléments les composant sauf le premier. Ainsi, dans une colonne définie par le type INTERVAL MONTH TO DAY, on pourra stocker une valeur de 48 mois et 31 jours, mais pas une valeur de 48 mois et 32 jours. De même dans un INTERVAL HOUR TO SECOND la valeur en heure est illimitée, mais celle en minute et en seconde ne peut dépasser 59. 6.4. Types " BLOBS " (hors du standard SQL 2)Longueur maximale prédéterminée, donnée de
type binaire, texte long voire formaté, structure interprétable
directement par le SGBDR ou indirectement par add-on externes (image,
son, vidéo...). Attention : ne sont pas normalisés ! On trouve souvent les éléments suivants : TEXT : suite longue de caractères de longueur indéterminé IMAGE : stockage d'image dans un format déterminé OLE : stockage d'objet OLE (Windows) 6.5. Autres types courants, hors norme SQL 92BOOLEAN (ou LOGICAL) :
curieusement le type logique (ou encore booléen) est absent de la norme.
On peut en comprendre aisément les raisons… La pure logique booléenne
ne saurait être respectée à cause de la possibilité offerte par SQL de
gérer les valeurs nulles. On aurait donc affaire à une logique dite " 3
états " qui n’aurait plus rien de l’algèbre booléenne. La norme passe
donc sous silence, et à bon escient ce problème et laisse à chaque
éditeur de SGBDR le soin de concevoir ou non un booléen " à sa manière
". On peut par exemple implémenter un tel type de données, en utilisant une colonne de type caractère longueur 1, non nul et restreint à deux valeurs (V / F ou encore T / F). MONEY : est un sous type du type NUMERIC avec une échelle maximale et une précision de deux chiffres après la virgule. BYTES (ou BINARY) : Type binaire (octets) de longueur devant être précisée. Permet par exemple le stockage d’un code barre. AUTOINC : entier à incrément automatique par trigger. 6.6. Les domaines, ou la création de types spécifiquesIl est possible de créer de nouveau types de données à partir de types pré existants en utilisant la notion de DOMAINE. Dans ce cas, avant d'utiliser un domaine, il faut le recenser dans la base à l'aide d'un ordre CREATE :
Exemple :
Dès lors il ne suffira plus que d'utiliser ce type à la place de CHAR(5). L'utilisation des domaines possède de nombreux avantages :
Exemple :
Dans ce cas le code postal saisie devra
au minimum s'écrire 01000. On pourrait y ajouter une contrainte maximum
de forme (VALUE <= '99999'). NOTA : certains SGBDR n'ont pas
implémenté l'ordre CREATE DOMAIN. C'est le cas par exemple de SQL
Server. Ainsi, il faut aller "trifouiller" les tables systèmes pour
insérer un nouveau type de donnée à l'aide de commandes "barbares"
propre au SGBDR. Exemple (SQL Server) :
Un des immenses avantages de passer par
des définitions de domaines plutôt que d'utiliser directement des types
de donnés est que les domaines induisent une bonne normalisation de la
conception du schéma des données. Pour ma part j'utilise souvent le jeu de domaine suivant :
7. Contraintes de donnéesDans la plupart des SGBDR il est possible de contraindre le formatage des données à l'aide de différents mécanismes. Parmi les contraintes les plus courantes au sein des données de la table on trouve :
Enfin entre deux tables liées, il est souvent
nécessaire de définir une contrainte de référence qui oblige un
enregistrement référencé par sa clef à être présent ou détruit en même
temps que l'enregistrement visé est modifié, inséré ou supprimé. Ce
mécanisme est appelé INTÉGRITÉ RÉFÉRENTIELLE. Exemple : Soit une base de données contenant deux tables : CLIENT et COMMANDE dotées des structures suivantes ... CLIENT :
COMMANDE :
Et dans lesquelles on trouve les valeurs suivantes : CLIENT :
COMMANDE :
Si l'on détruit la ligne de la table CLIENT
concernant le n°212 (MARTIN) alors les factures 1999-11 et 1999-14
deviennent orphelines. Il faut donc interdire la suppression de ce
client tant que des références de ce client persistent dans la table
COMMANDE. De même, le changement de la valeur de la clef NO_CLIENT ferait perdre la valeur de référence du lien entre les deux tables, à moins que la modification ne soit répercutée dans la table fille. NOTA : on parle alors de tables en relation mère / fille ou encore maître / esclave. 8. Triggers et procédures stockéesEn ce qui concerne les SGBDR en
architecture client / serveur, il est courant de trouver des mécanismes
de triggers (permettant d'exécuter du code en fonction d'un événement
survenant dans une table) ainsi que des procédures stockées (du code
pouvant être déclenché à tout moment). Dans les deux cas, c'est sur le
serveur, et non dans le poste client que la procédure ou le trigger
s'effectue. L'avantage réside dans une plus grande
intégrité du maniement des données et souvent d'un traitement plus
rapide que si le même code trournait sur le poste client. Ainsi le calcul d'un tarif dans une table de
prestation peut dépendre de conditions parfois complexes ne pouvant être
facilement exécutée à l'aide de requêtes SQL. Dans ce cas on aura
recours au langage hôte du SGBDR, pour lequel on écrira une procédure
permettant de calculer ce tarif à partir de différents paramètres. Mais la plupart du temps, triggers et
procédures stockées s'écrivent dans un langage propre au SGBDR (Transact
SQL pour SQL Server et Sybase, PL/SQL pour Oracle, etc...). Pour un
aperçu du langage Transact SQL, veuillez lire l'article "Un aperçu du
langage Transact SQL". Exemple : on désire calculer le tarif
d'adhésion à une mutuelle santé pour une famille composée d'un homme né
le 11/5/1950, d'une compagne née le 21/6/1965, d'un fils né le
16/3/1992, d'une fille né le 11/1/1981 et d'une grand mère à charge
(ascendant) née le 21/12/1922, le futur adhérent désirant payer sa
cotisation au mois. Les bases tarifaires établies sont les suivantes : Table "TARIF_BASE" :
Table "TARIF_MAJO"
Table "TARIF_MINO"
Il est très difficile d'établir une requête
permettant de trouver le bon tarif dans un tel cas. En revanche, en
passant la table de paramètre suivant à une procédure : Table "PARAMS" :
Il n'est pas très compliquée d'écrire dans un langage donné une procédure permettant de calculer ce tarif.
Une telle, procédure pourrait s'écrire dans un pseudo code proche du Pascal :
9. RésuméVoici les différentes implémentations du
SQL sur quelques uns des différents moteurs relationnels que nous avons
choisi d’analyser.
(a) Avec quelques limitations (par exemple les contraintes d’intégrité référentielles ne peuvent être crées par le SQL) (b) Limité en rollback à 255 enregistrements (c) Pas dans le SQL, mais en code du langage hôte (d) Mais possible à l'aide de triggers ou de commandes spécifiques 10. ConclusionEn matière de SGBDR " fichier ", Paradox se
révèle plus pauvre au niveau du DDL et du TCL, mais plus riche en
matière de DML qu’Access. Quant aux types de données, Paradox se révèle
bien plus complet qu’Access qui n’intègre même pas de champ de type "
image "... Pensez que dans Access le type entier n’est même pas défini !
Enfin en matière de BLOB la plupart des SGBDR acceptent jusqu’à 2Go de
données, sauf Access qui est limité à 64 Ko... En ce qui concerne la capacité de stockage Access révèle très rapidement de nombreuses limites, comme en nombre d'utilisateurs en réseau. En matière de contrôle des transactions Paradox est limité à 255 enregistrements en RollBack. Mais la présence de tables auxiliaires permet de dépasser ces limites sans encombre, à condition de prévoir le code à mettre en œuvre. Différence fondamentale pour Paradox, pas de DCL. Mais cela est largement compensé par un niveau de sécurité a forte granularité qui n’est pas compatible avec le SQL normalisé. Ainsi dans Paradox on peut placer des droits au niveau des tables mais aussi de chaque champ et le moteur crypte les données dès qu’un mot de passe est défini (SQL Base de Centura permet aussi de crypter les données à l’aide des plus récents algorithmes de chiffrage) Point très négatif pour Access dans sa catégorie : il pratique le verrouillage de pages ! Enfin les vues n’existent pas dans Access mais elles sont présentent dans Paradox sous une forme non SQL appelée " vue de requête reliés " (QBE). En matière de serveur SQL C/S, le SGBDR
Sybase se révèle très proche de SQL Server ce qui n'est pas absurde
puisqu'ils sont parents. Oracle possède une bonne diversité de types
mais sa conformité à la norme laisse à désirer (pas de JOIN par exemple,
pauvreté des fonctions temporelles). En revanche Oracle possède un type
de champ bien utile et intégré à toutes les tables, le ROWID qui donne
le n° de la ligne dans la table et qui est toujours unique, même si la
ligne a été supprimée. On retrouve des mécanismes similaires dans SQL
Server sous le nom de GUID ou bien avec l'auto incrémentation via
"identity".
|