Les bases de données et le langage SQL

Bonjour,

Je fais une parenthèse où je ne parlerai pas de Python, mais d'un sujet très important en informatique, les bases de données. Pour mettre les choses en perspective, j'ai cherché quand j'ai commencé à penser à cette présentation combien il y avait de livres sur Python dans la catégorie « langages de programmation » sur amazon.fr. J'en ai trouvé douze. Sur amazon.com, il y en avait 119. J'ai recherché sur « SQL » dans la même catégorie, sur amazon.fr il y avait 312 titres et sur amazon.com … eh bien il y en avait 840. SQL est un langage qui a environ 15 ans de plus que Python, c'est normal qu'il y ait davantage de littérature, mais je pense que cela vous convaincra que c'est un gros morceau.

Du coup, ce que je vais vous présenter ici, c'est vraiment de l'extrait d'essence de concentré. Mais j'essaierai de faire de mon mieux.

Accéder à un fichier en lecture et en écriture dans un programme, on sait faire. Où les choses se compliquent en général, c'est quand deux programmes essaient d'accéder au même fichier en même temps. Le problème s'est posé très tôt en informatique, en particulier dans les systèmes de réservation des compagnies aériennes qui ne voulaient pas vendre le même siège à plusieurs personnes différentes. Très vite, on a pensé à avoir des programmes appelés systèmes de gestion de bases de données, ou SGBD, dont la seule fonction est de s'occuper des données. Un programme émet ce qu'on appelle une requête, et le SGBD s'occupe des accès aux fichiers et retourne les données. Et quand plusieurs programmes l'attaquent en même temps, c'est lui qui coordonne et met éventuellement en attente, c'est transparent pour le programme. L'ennui, c'est qu'au début il fallait quand même que le programmeur ait une bonne idée de la manière dont les données étaient stockées pour écrire ses requêtes. Et là encore très vite s'est posée la qu
estion de l'indépendance logique, c'est-à-dire d'avoir des systèmes où le programmeur n'a absolument pas besoin de savoir comment les données sont stockées. Et de ce côté-là les différentes solutions proposées laissaient beaucoup à désirer, jusque vers 1970 où un chercheur d'origine anglaise d'IBM, Codd, a eu une idée qui a complètement révolutionné les bases de données. Codd était un mathématicien de formation, et si jamais vous lisez un jour son papier original vous vous en rendrez compte. Son idée a été de s'appuyer sur des théories mathématiques existantes et sur la logique.

Imaginez que vous soyez un opérateur de téléphonie mobile. Il y a un certain nombre de choses qui vous intéressent chez vos clients – leur nom, leur adresse, vous voulez leur relevé d'identité bancaire pour prélever les factures, vous gérez leur numéro, vous allez comptabiliser le nombre d'appels locaux, de SMS et d'appels non locaux par exemple. Tout ça définit ce que je veux gérer pour mes abonnés (au moins en partie) et Codd appelle cela une relation – dans cette relation, je stockerai les faits que je connais sur chacun des abonnés. Deux choses très importantes pour Codd – il n'y a pas d'ordre particulier prédéfini, ni parmi les attributs d'un abonné, peu importe si je range son nom avant ou après son adresse, ni entre les informations relatives à des abonnés différents. Et surtout, par définition il ne doit pas y avoir de doublons, c'est-à-dire qu'un même ensemble de valeurs doit être stocké une fois et une seule. On doit être capable d'identifier séparément chaque ensemble d'attributs, et cela nous amèn
e à la notion de clef, c'est-à-dire un ensemble d'attributs qui identifie sans ambiguïté dans notre cas un abonné. Qu'est-ce-qui permet d'identifier un abonné ? Le nom et l'adresse forment une clef. Mais le numéro de téléphone me permet aussi d'identifier mon abonné. Et le relevé d'identité bancaire aussi. Je peux donc avoir plusieurs clefs possibles.

Mais on arrive ici à un problème informatique classique. Si je veux retrouver les informations que je connais sur l'abonné François Pignon, je vais faire des comparaisons entre ce que j'entre et ce qui est stocké dans la base de données. Si c'est différent au niveau minuscules/majuscules, ou si j'ai par exemple un blanc en trop, pour l'ordinateur ce sont des valeurs différentes et je risque de ne pas retrouver mes données. Une première chose à faire, c'est de standardiser la casse, et séparer en prénom et nom pour éliminer les problèmes d'espace.

Si je veux retrouver mes informations facilement, il faut que mes attributs soient des attributs simples, et cela va me demander, dans la conception de la base de données, une phase de normalisation des données. Par exemple je vais stocker l'adresse sous forme voie, ville et code postal – cela me permettra de rechercher plus facilement ville ou code postal pour des statistiques par exemple. Mais savoir à quel niveau de détail descendre n'est pas évident. Par exemple un même boulevard du Général Leclerc peut être appelé avenue ou rue, le « du » peut manquer, comme Leclerc a été nommé Maréchal à titre posthume le grade peut changer, certaines personnes peuvent être mauvaises en orthographe, et d'autres aimer la simplicité. Si vous voulez faire des statistiques par rue, il faudrait encore découper. Si c'est pour imprimer l'adresse sur une enveloppe, la Poste connait son métier et ce ne sera pas si grave. Où je veux en venir, c'est que la notion d'attribut simple est relative et dépend ce ce que vous voulez faire
des données.

Je reviens à mes abonnés. Je peux donc les identifier soit par leur adresse, soit par leur numéro – je laisse le RIB à la banque. Mais le problème c'est que dans une relation client j'aime bien que mes identifiants ne changent pas. On peut changer de numéro de téléphone. On peut se marier et changer de nom ; on peut déménager. On peut même tout faire en même temps. Pour l'opérateur, la seule manière de s'y retrouver est d'affecter un code client qui lui ne bougera pas, qui sera lui aussi une clef et que l'on appellera la clef primaire.

Mais je n'ai pas fini avec mes soucis. Monsieur et madame ont probablement chacun leur téléphone, et les prélèvements vont être sur un compte commun. Je vais donc me retrouver avec pas mal d'informations communes. Ils peuvent déménager, et si l'on met à jour l'adresse pour un et pas pour l'autre cela risque d'être le souk.

Ce que l'on va faire c'est que l'on va fragmenter les informations en plusieurs relations, chacune avec sa clef, les abonnés, leurs différents contrats avec le numéro de téléphone associé, et pour chaque numéro la consommation mensuelle par exemple. Je suis en train d'abréger sauvagement mais tout ceci s'appelle la modélisation, c'est ultra important et c'est difficile à bien faire.

Le but du jeu c'est d'arriver à ce que dans chaque relation, les attributs qualifient la clef, toute la clef, et rien que la clef. C'est la définition de William Kent, un autre spécialiste des bases de données, et si vous parvenez à cela vous aurez une base de données solide.

Un résultat important de la normalisation c'est l'intégrité des données – le fait que les données soient bonnes et le restent. Avant le relationnel, les programmes devaient effectuer beaucoup de contrôles avant de stocker ou de modifier les données. En mettant les contrôles dans la base, même si un autre programme écrit moins soigneusement accède aux mêmes données, il ne les pourrira pas.

Comment ces contrôles sont-ils effectués ? D'un côté en associant un domaine à chaque donnée, plus ou moins un type, et d'autre part en définissant des contraintes. Les contraintes ont été le cheval de bataille de Chris Date, un autre anglais d'IBM qui a rejoint Codd en Californie et qui est devenu son plus proche collaborateur et ami. Il y a plusieurs types de contraintes, comme par exemple le fait de dire qu'une combinaison de valeurs ne doit se retrouver qu'une seule fois dans une relation, et, ce qui est peut-être le plus intéressant, l'intégrité référentielle, qui revient à dire par exemple que l'on n'accepte un code client dans la relation des contrats que s'il correspond à un abonné existant, un numéro de téléphone dans les consommations que si le numéro existe, et un code postal que si on le trouve dans une relation qui contient tous les codes postaux valides. Cela permet de détecter beaucoup de fautes de frappe !

Mais revenons à Codd. Sa grande idée, c'est que l'on pouvait OPERER sur des relations, au sens d'exécuter des opérations résultant en de nouvelles relations. Il a défini un certain nombre d'opérations, voici les trois principales :

La possibilité de définir par application de critères un sous-ensemble d'une relation, opération qu'il a appelée sélection,

Ne retenir que certains des attributs de la relation originelle, ce qu'il a appelé projection

Et la possibilité de construire une nouvelle relation hybride à partir d'attributs provenant de plusieurs relations et rapprochées par des valeurs communes ; par exemple rapprocher le nom de l'abonné de sa date de souscription en rapprochant la relation des abonnés de celle des contrats sur le code client. Il a appelé cette opération la jointure.

Un autre employé d'IBM, Don Chamberlin, qui était un spécialiste des bases de données de l'époque, a raconté qu'en assistant à une des présentations en interne de ses idées par Codd, il le voyait résoudre en une équation des questions qui lui auraient demandé 5 pages de programmation. Et il s'est dit « Waow, c'est puissant ! »

Codd a également insisté pour que les insertions, mises à jour et destructions puissent être des opérations massives, pas du tout les opérations enregistrement par enregistrement qui étaient habituelles à l'époque. Il a aussi défini que lorsqu'une valeur manquerait, on pourrait stocker à la place quelque chose qu'il a appelé NULL. Et là, il a mis le doigt dans quelque chose qui n'est pas simple. En fait, il peut y avoir à la base deux raisons pour laquelle une valeur manque – soit l'attribut est inapplicable, soit on ne le connait pas. Si vous stockez des informations sur des animaux, par exemple, une durée de gestation peut être manquante parce que l'animal est ovipare, ou parce qu'il s'agit d'un petit mammifère récemment découvert au fin fond de la jungle de Bornéo et que les scientifiques n'ont pas encore pu étudier. Sur le tard, Codd a envisagé de différencier les deux cas, mais cela n'a jamais été fait.

Je vous ai dit que Date était le plus proche collaborateur de Codd, ils ont été associés pendant plusieurs années après avoir quitté IBM. Chris pense que les NULLs étaient une erreur et n'ont rien à faire dans le relationnel. Il a écrit beaucoup de livres, pas « SQL sans les NULLS » mais je pense que c'est un titre qu'il aimerait.

Bon, puisqu'on en parle vous allez me dire « et SQL, dans tout ça ? ». J'y viens. Mais d'abord il faut savoir que le relationnel et SQL, ce n'est pas la même chose. Par exemple, le professeur Stonebraker de Berkeley avait dirigé un projet d'après les idées de Codd, le projet Ingres dont Postgres descend aujourd'hui, qui utilisait au départ Quel, un autre langage que beaucoup trouvaient supérieur à SQL.

SQL, qui s'appelait Sequel au départ, a été inventé par Don Chamberlin et Ray Boyce, décédé malheureusement très jeune d'une rupture d'anévrisme, dans le cadre du principal projet d'IBM autour des idées de Codd, System R. Une des raisons du succès de SQL, c'est peut-être qu'un certain Ed Oates en a lu les spécifications dans des publications d'IBM. Il en a parlé à son ami Larry, qui avait lu le papier de Codd et en avait saisit l'importance, et avec un autre ami et programmeur de première force, Bob Miner, ils ont fondé une société, Software Development Laboratories, et en travaillant comme des fous avec un quatrième mousquetaire, Bruce Scott, ils sont parvenus à sortir avant IBM une base de données qui s'appuyait sur SQL et tournait sur des machines concurrentes de celles d'IBM. Ils ont appelé leur produit Oracle.

Je vais essayer de vous montrer comment Chamberlin et Boyce ont essayé de concrétiser les idées de Codd, non pas avec le SQL de l'époque, mais avec celui d'aujourd'hui, qui a un peu évolué.

Plutôt que de parler de relations ils ont préféré parler de tables, et pour définir une relation ils ont utilisé CREATE TABLE, avec la liste des attributs et leurs types. Dans beaucoup d'outils il faut terminer une commande SQL par un point-virgule. Les types sont très restreints par rapport à ce que Codd envisageait – essentiellement du texte, du numérique, des dates et du binaire. Si vous définissez une table comme cela, c'est du bon SQL mais du mauvais relationnel parce ce que rien n'interdit les doublons. On va donc dire que le code client est la clef primaire, que le code postal n'est valide que si on le trouve dans une table de référence, et que l'on ne peut pas avoir deux abonnés avec le même nom et la même adresse – c'est important, parce que les numéros de clients sont généralement produits automatiquement et protègent contre les doublons « techniques » mais pas les doublons réels. Avec cela Chris Date sera heureux.

A côté de CREATE Chamberlin et Boyce ont défini DROP et ALTER, pour respectivement détruire et modifier une table.

Pour la sélection, SELECT suivi de la liste des noms des colonnes de la table séparés par des virgules, dans n'importe quel ordre, suivi de FROM et du nom de la table. Cela ramène toutes les informations d'une table. On ne le fait jamais dans un programme mais on peut utiliser SELECT * pour ramener toutes les colonnes dans l'ordre du CREATE TABLE.

Et pour filtrer, WHERE suivi d'un nom de colonne et d'une comparaison.

Ce qui est intéressant, c'est que le résultat est une relation au sens de Codd. On peut mettre l'expression entre parenthèses, lui donner un nom, et l'employer comme si c'était une table. On ne fera pas cela pour quelque chose d'aussi simple, on ajoutera simplement d'autres conditions avec AND ou OR. SQL connait les opérateurs de comparaison classiques, il en a qui lui sont propres comme LIKE qui compare du texte à du texte avec un caractère joker (le pourcent), et aussi IS NULL pour vérifier si une colonne est renseignée ou pas.

Pour la projection, c'est encore SELECT, et l'on limite les colonnes à quelques unes ; bien entendu on peut combiner avec une sélection, qui peut s'appliquer à des colonnes que l'on ne ramène pas. Sauf que là, gros problème par rapport aux idées de Codd. Si parmi les colonnes il y a au moins une clef complète, pas de souci. Mais sinon le résultat n'est pas une relation : on a des doublons. Et derrière tout tombe par terre, parce que vouloir appliquer des opérateurs relationnels à des choses qui ne sont pas des relations, c'est comme vouloir calculer des modulos avec des nombres qui ne sont pas des entiers. SQL ne vous dira rien, mais il vous retournera des résultats incorrects. Pour s'en tirer, il faut rajouter DISTINCT, et vous obtiendrez un résultat sans doublon qui est une vraie relation et rendra le sourire à Codd.

Il y a une autre manière de s'en tirer, c'est de rajouter une fonction de groupe, par exemple de compter en précisant par GROUP BY par quoi on groupe. Plus de doublons, et l'on sait que l'on a deux MARTIN. Il y a quelques fonctions de groupe, les plus utilisés sont COUNT(), MAX() et MIN(), et SUM() pour les colonnes numériques. La signification est évidente.

Pour la jointure on ajoute d'autres tables avec JOIN en précisant avec ON sur quelles colonnes s'effectue le rapprochement. Comme souvent des colonnes portent le même nom dans plusieurs tables, on donne un petit nom aux tables, ici a et c, et l'on préfixe chaque nom de colonne par a. ou c. pour préciser dans quelle table se trouve chaque colonne (ce n'est obligatoire que s'il y a ambiguïté, mais c'est aussi plus clair dans les programmes). Les conditions peuvent s'appliquer aux colonnes de n'importe quelle table.

Chamberlin et Boyce se sont aussi sentis obligés d'ajouter quelque chose dont Codd n'avait rien à cirer – l'ordre. La plupart des produits ajoutent aussi une clause LIMIT pour retourner par exemple les cinq abonnés les plus récents. Dans SQL Server il n'y a pas LIMIT mais TOP qui permet de faire la même chose, et dans Oracle on utilise un numéro interne appelé rownum.

SELECT est le gros morceau. Pour ajouter des lignes c'est INSERT INTO suivi du nom de la table et de la liste des colonnes, puis soit d'un SELECT soit d'une clause VALUES.

Pour mettre à jour c'est UPDATE nom de la table SET nom de colonne = valeur, avec une clause WHERE similaire à ce qu'on écrit dans un SELECT.

Pour détruire, DELETE FROM, là encore avec une clause WHERE.

Voilà. C'était rapide, mais c'est une introduction.