LE SQL de A à Z : 1re partie - bases de données, SQL et types de données

Notons 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 appelé à tort (y compris par moi-même !) SQL 3. À 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 quelque 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.

À noter, la future norme SQL:2003, sortira en juillet 2003.

Mê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 temporelles de l'aide en ligne !!!

De plus, le plus petit dénominateur commun entre les quatre 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.

Dans un excellent article, Peter Gulutzan fait le point sur l'essentiel de ce qui est normatif chez les principaux éditeurs de SGBDR : http://www.dbazine.com/gulutzan3.html
Et fustige leur comportement, similaire à celui des constructeurs d'ordinateurs en matière d'OS UNIX incompatibles et qui maintenant pleurent à chaudes larmes devant le rouleau compresseur « Linux » !
Une autre étude, par Michael M. Gorman montre que la volonté de ces éditeurs de SGBDR et leurs intérêts mercantiles à courte vue sont incompatibles avec le respect des standards…
À lire donc : http://www.tdan.com/i016hy01.htm

Nous avons aussi démontré qu'une même table créée sur différents SGBDR avec les mêmes données n'avait pas 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.
À 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

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 suivantes :

CREATE, ALTER, DROP

Qui permettent respectivement de créer, modifier, supprimer un élément de la base.

C'est la partie du SQL qui s'occupe de traiter les données.
Elle comporte les commandes de base suivantes :

INSERT, UPDATE, DELETE, SELECT

Qui permettent respectivement d'insérer, de modifier de supprimer et d'extraire des données.

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 :

GRANT, REVOKE

Qui permettent respectivement d'attribuer et de révoquer des droits.

C'est la partie du SQL chargée de contrôler la bonne exécution des transactions.
Elle comporte les commandes de base suivantes :

SET TRANSACTION, COMMIT, ROLLBACK

Qui permettent de gérer les propriétés ACID des transactions.

Il s'agit d'éléments procéduraux que l'on intègre à un langage hôte :

SET, DECLARE CURSOR, OPEN, FETCH...

Le terme « ACID », ne fait pas référence, loin s'en faut au LSD, mais plus basiquement 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és, la concaténation des tables des clients des différentes entités ne peut entraîner la présence de plusieurs clients 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.

Voici ce que disent, Sébastien BRAU, Christian CHANE-NAM, Louis-Laurent ANTIGNY, Gilberto DE VASCONCELOS sur les propriétés ACID d'un SGBDR :

Atomicité : si tout se passe correctement, les actions de la transaction sont toutes validées, sinon 
on retourne à l'état initial. 
L'unité de travail est indivisible. Une transaction ne peut être partiellement effectuée. 

Cohérence : le passage de l'état initial à l'état final respecte la cohérence de la base. 

Isolation : les effets de la transaction ne sont pas perceptibles tant que celle-ci n'est pas terminée. 
Une transaction n'est pas affectée par le traitement des autres transactions. 

Durabilité : les effets de la transaction sont durables. 

Rendre transparentes la complexité et la localisation des traitements et des données, tout en assurant 
un bon niveau de performance, tels sont aujourd'hui les demandes des entreprises et les nouveaux 
critères sur lesquels s'arrêtent leurs choix.

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ées ou annulées. 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.

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 pallie en intimant à ses utilisateurs de passer à SQL Server dès que le nombre d'utilisateurs dépasse 10 !

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.

À 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 plateformes 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 objet 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.

NOTA : Pour en savoir plus sur le sujet, lire l'étude comparative sur les SGBDR à base de fichiers et ceux utilisant un moteur relationnel, intitulée Quand faut-il investir sur le client/serveur ? On y discute aussi des différents modes de verrouillage…

CHARACTER (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 chaine.

Exemple :

NOM_CLIENT CHAR(32)
OBSERVATIONS VARCHAR(32000)

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ères du pays
Ces types de données sont codés sur 4 octets (UNICODE) et on doit spécifier la longueur de la chaine.

Exemple :

NOM_CLIENT NCHAR(32)
OBSERVATIONS NCHAR VARYING(32000)

Nota : la valeur maximale de la longueur est fonction du SGBDR.

NUMERIC (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 PRÉCISION : réel à virgule flottante dont la représentation est binaire, de grande précision
BIT : chaine de bits de longueur fixe
BIT VARYING : chaine de bits 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 :

NUMERIC (15,2)

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 :

SELECT CAST(3.14159 AS FLOAT (16,4)) AS PI_FLT, CAST(3.14159 AS DECIMAL (16,4)) AS PI_DEC

PI_FLT                                                PI_DEC 
----------------------------------------------------- ------------------ 
3.1415899999999999                                    3.1416

NOTA : on peut utiliser une notation particulière pour forcer le typage implicite. Il s'agit d'une lettre précédant la chaine à 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ée de caractères allant de 0 à F).

Exemple :

SELECT N'toto' AS MY_NCHAR, B'01010111' AS MY_BINARY_BIT, X'F0A1' AS MY_BINARY_HEX

DATE : 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… où AAAA est l'année sur quatre 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 :

1999-03-26 22:54:28.123

est le 26 mars 1999 à 22 h 54 m, 28 s et 123 millisecondes.

Mais peu de moteurs de requêtes l'implémentent 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 :

INTERVAL précision_min TO [précision_max]

où précision_min et précision_max peuvent prendre les valeurs :

{YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

avec la condition supplémentaire suivante : précision_max ne peut être qu'une mesure temporelle plus fine que précision_min.

Exemple :

JOURS      INTERVAL DAY
TRIMESTRE  INTERVAL MONTH TO DAY
TACHE      INTERVAL HOUR TO SECOND
DUREE_FILM INTERVAL MINUTE

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 heures est illimitée, mais celle en minutes et en secondes ne peut dépasser 59.

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ée
IMAGE : stockage d'image dans un format déterminé
OLE : stockage d'objet OLE (Windows)

BOOLEAN (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.

Il est possible de créer de nouveaux 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 :

CREATE DOMAIN nom_du_domaine AS type_de_donnée

Exemple :

CREATE DOMAIN DOM_CODE_POSTAL AS CHAR(5)

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 :

• ils peuvent faire l'objet de contraintes globales ;
• ils peuvent être modifiés (ce qui modifie le type de toutes les colonnes de table utilisant ce domaine d'un seul coup !).

Exemple :

ALTER DOMAINE DOM_CODE_POSTAL
ADD CONSTRAINT MIN_CP CHECK (VALUE >= '01000')

Dans ce cas le code postal saisi 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ème pour insérer un nouveau type de donnée à l'aide de commandes « barbares » propres au SGBDR.

Exemple (SQL Server) :

sp_addtype DOM_CODE_POSTAL, 'CHAR(5)', 'null'

Un des immenses avantages de passer par des définitions de domaines plutôt que d'utiliser directement des types de données 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 domaines suivant :

D_KEY_INTEGER   entier
D_TRIGRAMME     char(3)
D_CODE          char(8)
D_LIBELLE_COURT varchar(16)
D_LIBELLE       varchar(32)
D_LIBELLE_LONG  varchar(64)
D_TITRE         varchar(128)
D_DATE          date
D_TEMPS         dateTime
D_TEXTE         text
D_ADRESSE       varchar(32) /* spécifique aux adresses */
D_BOOLEEN       smallint(1) /* contraint à 0 ou 1, valeur par défaut 0 */
D_MONNAIE
D_ENTIER_COURT
D_ENTIER_LONG
D_REEL
...