Comparaison de SGBD libres : langage, triggers, administration

CONSERVATOIRE NATIONAL DES ARTS ET MÉTIERS
CENTRE REGIONAL DE MONTPELLIER
Examen probatoire
du cycle ingénieur C.N.A.M.
Filière : Système de conduite
Présenté par Christophe CARPENA
Comparaison de SGBD libres :
langage, triggers, administration
Soutenu en Mai 2006
JURY
Président :
Membres :
M. Ranchin
M. Marc Nanard (Professeur principal)
Mme Maguelonne Teisseire
Remerciements
Je tiens à remercier le CNAM ainsi que l’ensemble des professeurs pour les connaissances
fort utiles qu’ils m’ont apportées et je regrette de n’avoir pas pu devenir un spécialiste de tous
ces domaines forts intéressants.
Un remerciement particulier à tous mes amis Jéjé, Béa, Grim et Ludivine, Vincent et Cathy…
(Pardon à ceux que je n’ai pas cité) qui m’ont supporté dans tous les sens du terme. Je ne vous
parlerai plus de cours. Du moins, plus aussi souvent…
Le dernier remerciement, mais non le moindre, est pour ma douce étoile du nord Océane, sans
qui je n’en serais pas ou j’en suis aujourd’hui.
Comparaison des SGBD Libres.doc
2/49
CNAM 2005-2006
Table des matières
Introduction _______________________________________________________________ 4
1
Caractéristiques d'un SGBD ______________________________________________ 5
2
Les SGBD libres disponibles ______________________________________________ 7
3
4
5
2.1
Les SGBD les plus aboutis _________________________________________________ 7
2.2
Les SGBD en progression__________________________________________________ 9
2.3
Synthèse et Choix _______________________________________________________ 12
Le langage des SGBD : SQL _____________________________________________ 14
3.1
Normes et dialecte SQL __________________________________________________ 14
3.2
SQL-92, les différents niveaux _____________________________________________ 16
3.3
SQL-99 ________________________________________________________________ 22
Les déclencheurs ______________________________________________________ 27
4.1
Qu’est-ce qu’un déclencheur ______________________________________________ 27
4.2
Langage SQL/PSM ______________________________________________________ 28
4.3
Support de SQL/PSM ____________________________________________________ 31
4.4
Interfaces avec les langages de programmation. ______________________________ 31
Administration ________________________________________________________ 34
5.1
Protection des données ___________________________________________________ 34
5.2
Optimisation ___________________________________________________________ 39
5.3
Gestion des sauvegardes / restaurations _____________________________________ 41
5.4
Tableau de synthèse _____________________________________________________ 42
Conclusion _______________________________________________________________ 43
Bibliographie _____________________________________________________________ 44
Glossaire _________________________________________________________________ 46
Comparaison des SGBD Libres.doc
3/49
CNAM 2005-2006
Introduction
« Range ta chambre ! »
Quel enfant n'a pas entendu de ses parents cette phrase synonyme de corvée à venir. En ce qui
concerne le rangement, l’informatique représente un avantage certain : il se fait de manière
automatisée. Que ce soit le logiciel de défragmentation qui range les bouts de fichiers en les
déplaçant sur le disque, ou les bases de données qui rangent les informations afin de pouvoir
aisément les retrouver, ces tâches s’exécutent de façon automatique, sans intervention
humaine à proprement parler.
L'évolution de l'informatique a toujours été rapide et impressionnante. Le domaine des bases
de données ne déroge pas à la règle. Au commencement, les fichiers à plat ont stocké les
informations, sans structure. Puis sont apparus, chronologiquement parlant, les bases de
données hiérarchiques présentant une structure arborescente où chaque enregistrement n'a
qu'un seul possesseur, les bases de données réseaux, lesquelles ont été en mesure de lever de
nombreuses difficultés du modèle précédent, les bases de données relationnelles utilisant des
principes de l’algèbre ensembliste et les bases de données orientées objet dont les
informations sont représentées sous forme d'objets. Chaque évolution a levé nombre de
limitations de la génération précédente et a ajouté toujours plus de fonctionnalités.
L’évolution des technologies des bases de données était nécessaire dans notre société de
l’information. En effet, il n’est pas rare d’avoir, aujourd’hui, des bases de données contenant
des gigaoctets d'informations. Certaines atteignent même plusieurs téraoctets alors que le
texte du roman Les trois Mousquetaires ne représente qu’environ 1,35 mégaoctets. Les bases
de données peuvent donc stocker l’équivalent de plusieurs millions de livres. On pourrait se
demander comment retrouver ces informations. Au vu de la taille et de leur nombre, il
semblerait facile à une information de se perdre, à une erreur de se glisser, à une personne
malintentionnée de la modifier à son avantage. La maintenance, le bon fonctionnement et la
sécurisation d’un tel système sont d’une grande difficulté. Les systèmes de gestion de base de
données doivent donc offrir des facilités pour l'exécution de ces tâches.
Nous verrons au cours de cet exposé comment les systèmes de gestion de base de données
(SGBD) libres répondent à ces problématiques. Dans un premier temps, nous répertorierons
les caractéristiques principales d’un SGBD et quels sont les SGBD libres disponibles
répondant à ces critères. Ensuite nous verrons comment les interroger, voir s’ils parlent tous le
même langage, et donc, voir si l’on peut s’adresser à eux de la même façon. Dans un
deuxième temps, nous analyserons comment, grâce aux déclencheurs (Trigger), nous
tenterons de maintenir l’intégrité d’une base et en faciliter l’administration. Pour terminer,
nous aborderons les éléments à considérer pour administrer une base (par exemple la
sauvegarde des informations ou encore, la gestion et la restriction des accès). Comment
empêcher un pirate d’entrer ?
Comparaison des SGBD Libres.doc
4/49
CNAM 2005-2006
1 Caractéristiques d'un SGBD
La fonction première d'un Système de Gestion de Base de Données (SGBD) est d'être un outil
de stockage d'informations offrant des fonctions simples de manipulation de grands volumes
de données. L’un des avantages de ces SGBD est que l'interrogation de ces informations
s’effectue d’une manière indépendante de l'architecture physique de stockage. Les SGBD
garantissent la cohérence de ces données en cas de mise à jour simultanée par plusieurs
utilisateurs. Les transactions assurent l'intégrité des données en cas d'opérations incorrectes
réalisées par un programme ou un utilisateur. Les données stockées dans un SGBD sont dites
persistantes, leur fiabilité et leur récupération en cas de panne matérielle ou logicielle doit être
toujours possible. De plus, le SGBD doit assurer la confidentialité des données en cas d'accès
malveillant ou accidentel.
Les fonctionnalités essentielles d'un SGBD sont donc les suivantes :
¾ Le système doit assurer la persistance des données.
Lorsqu’une transaction (ensemble de requêtes) est validée, les données doivent être
persistantes, c'est à dire qu'elles doivent être enregistrées de manière permanente.
¾ Le système doit assurer la fiabilité des données.
Une transaction doit être atomique, autrement dit, soit exécutée complètement, soit pas
du tout. Des mécanismes de reprise sur panne doivent être présents. La panne d'une
mémoire ou d'un disque ne doit pas endommager les données.
¾ Le système doit offrir la possibilité à plusieurs utilisateurs de manipuler les données
concurremment.
Il doit au minimum assurer la sérialisation des transactions. La sérialisation des
transactions est l'exécution en parallèle d’un ensemble de transactions, produisant le
même résultat que si elles étaient exécutées en série.
¾ Le système doit offrir la possibilité à l'utilisateur d'interroger la base de façon simple.
Le langage de requête SQL a été crée dans le but d'interroger, de manière relativement
simple, les bases de données. Il peut tout de même être remplacé, pour le néophyte,
par une interface graphique.
Comparaison des SGBD Libres.doc
5/49
CNAM 2005-2006
¾ Le système doit assurer la confidentialité des données.
La notion d'utilisateur et de droits associés (droits de lecture, d'écriture et d'exécution
sur les objets de la base) est indispensable afin assurer cette confidentialité. Le
système doit également prévoir des mécanismes de cession et de retrait de droits.
¾ Le système doit pouvoir efficacement gérer les demandes
Les performances doivent être suffisamment bonnes. Pour cela, le SGBD doit utiliser
des techniques d'indexation, d'optimisation de requêtes et de gestion de caches. De
plus, il est essentiel que le SGBD puisse facilement évoluer et s’adapter à
l’augmentation du nombre de requêtes et à celle de l’espace de stockage, cette
augmentation se produisant immanquablement au fil des ans.
Après avoir recensé les principes généraux des bases de données, passons maintenant
en revue les SGBD libres existants, afin de voir s'ils répondent tous à l'ensemble de ces
critères.
Comparaison des SGBD Libres.doc
6/49
CNAM 2005-2006
2 Les SGBD libres disponibles
Les SGBD se singularisent dans le monde du logiciel libre. En effet, les logiciels libres sont
rarement concurrents. Il existe des logiciels libres aux fonctionnalités proches mais le public
visé est en général différent. Les SGBD libres font donc un peu office d’exception en
proposant plusieurs logiciels proches en terme de fonctionnalités et visant un public similaire.
Nous allons, dans un premier temps, examiner les SGBD libres principalement du point de
vue des systèmes sur lesquels ils peuvent être utilisés et surtout de la taille de base visée par
les développeurs du SGBD afin de voir s’ils satisfont tous à la caractéristique d'évolutivité
que nous avons définie comme essentielle.
2.1 Les SGBD les plus aboutis
2.1.1 MySQL (http://www.mysql.com/)
MySQL, dont la dernière version stable sortie à l'heure où ces lignes sont écrites est la version
5, est certainement la base de données libre la plus connue. Elle est en effet la plus présente
dans les entreprises en terme de part de marché. Offrant l'avantage d'être facilement
configurable pour les petites bases (moins de cinq minutes pour installer un serveur), elle
supporte l'utilisation de grosses bases contenant des millions de lignes. La gestion des
grappes1 (clusters) est présente mais encore fortement limitée (l'ensemble du contenu des
tables doit être présent en mémoire vive). Cette architecture (toutes les données en mémoire
vive) offre certes un temps de réponse minimum, mais impose une forte contrainte. La version
5.1, réduira cette contrainte en imposant uniquement les index en mémoire centrale.
MySQL possède plusieurs systèmes d'écriture des données. Les systèmes de fichiers sont
MyIsam, InnoDB, BDB, etc. Les fonctionnalités supportées, comme les clés étrangères ou les
transactions, sont différentes en fonction du système de fichier choisi. Dans cette étude, nous
considèrerons uniquement le système de fichier InnoDB qui assure les fonctionnalités
présentées précédemment.
MySQL fonctionne sur les systèmes suivants :
¾
¾
¾
¾
¾
¾
1
Linux
Unix: Solaris, HP-UX, AIX, SCO
BSD: FreeBSD
Apple: Mac OS
Windows
Novell: NetWare
Ensemble de machines qui coopèrent, visible de l’extérieur comme un seul serveur extrêmement puissant.
Comparaison des SGBD Libres.doc
7/49
CNAM 2005-2006
2.1.2 MaxDB/SAP DB (http://www.sapdb.org/)
Le SGBD MaxDB est développé par MySQL AB. Les fonctionnalités sont relativement
proches de celles de la version 5 de MySQL. Certaines fonctionnalités, comme les
déclencheurs (triggers), qui auparavant n'étaient présentes que dans MaxDB, ont été portées
dans MySQL. Cette importation de fonctionnalités de MaxDB dans MySQL est encore plus
marquée dans la version 5.1en cours de développement. Il semble que, dans un futur plus ou
moins proche, la fusion des deux produits sera une réalité. MaxDB supportent les grappes.
La liste suivante est un résumé des principales différences entre MaxDB et MySQL :
¾ MaxDB ne fonctionne pas sur toutes les plates-formes supportées par MySQL
¾ MaxDB supporte les procédures stockées. Avec MySQL, les procédures stockées sont
implémentées en version 5. Max DB supporte aussi les déclencheurs (triggers),
lesquels sont en partie implémentés dans MySQL 5 comme nous le verrons. MaxDB
contient un débogueur pour les langages utilisés dans les procédures stockées. Il peut
déclencher des sous-programmes imbriqués en cascade, et supporte les déclencheurs
multiples sur action et par ligne.
¾ MaxDB est livré avec des interfaces utilisateur en mode texte ou graphique, ou encore
sur le web. MySQL est livré avec des interfaces utilisateurs en mode texte
uniquement; une interface utilisateur graphique (MySQL Control Center) est
distribuée séparément. Les interfaces utilisateur sur le web pour MySQL sont offertes
par des éditeurs tiers.
¾ MaxDB contient des fonctionnalités administratives que MySQL ne possède pas
encore : la planification de tâches, les événements et alerte ainsi que l'envoi de
messages à une base de données administrateur sur signal d'alerte.
MaxDB fonctionne sur les systèmes suivants :
¾ Linux
¾ Unix: Solaris, HP-UX, AIX
¾ Windows
2.1.3 INGRES (http://www.ingres.com/)
INGRES (la dernière version : INGRES 2006) est un SGBD relationnel qui vient d’entrer
dans le monde du libre. Cette base de données est d’une installation simple et supporte les
bases allant de quelques lignes à une taille demandant l’utilisation d’une grappe (cluster). Elle
respecte la norme SQL-92 avec beaucoup d’extensions propriétaires. Les transactions simples
et distribuées sont ainsi implémentées.
Comparaison des SGBD Libres.doc
8/49
CNAM 2005-2006
INGRES fonctionne sur les systèmes suivants :
¾ Linux
¾ Unix: Solaris, HP-UX, AIX, OpenVMS
¾ Windows
2.1.4 PostgreSQL (http://www.postgresql.org/)
Autre grand nom de la base de données libre, PostgreSQL, dont la version actuelle est la 8.1,
est une base de données relationnel objet (Possibilité de stocker des objets dans la base). Le
SGBD supporte une grande partie de la norme SQL et offre les fonctionnalités des bases de
données modernes comme les requêtes complexes, les clés étrangères, les déclencheurs, les
vues. Il supporte aussi l’objet relationnel dont les extensions sont spécifiées dans la norme
SQL-99. Néanmoins, PostgreSQL étant la seule base libre à supporter l'objet relationnel, la
comparaison avec d'autres bases sur ce sujet n'a pas de vraiment de sens.
PostgreSQL fonctionne sur les systèmes suivants:
¾ Linux
¾ Unix
¾ Windows
2.2 Les SGBD en progression
2.2.1 Berkeley DB (http://www.sleepycat.com/)
Racheté par Oracle, cette base est présente dans d’autres produits comme par exemple
MySQL grâce à une implémentation pensée pour l’intégration. De par la façon dont Berkeley
DB stocke les informations (Clé, Valeur stockée avec les types de l’application), elle est le
choix idéal pour les requêtes prédéterminées portant sur des données mouvantes tandis que les
SGBD traditionnels sont plus performants sur les requêtes dynamiques portant sur des
données statiques. OpenLDAP, un serveur libre d’annuaire, utilise cette base car la structure
Clé/Valeur donne de meilleures performances.
Ce produit est décliné en trois versions :
¾ la version de base
¾ une version optimisée pour Java, utilisée par exemple par Sun dans certains de ses
produits
¾ Berkeley DB XML, une surcouche ajoutant des nombreuses fonctionnalités XML
comme le support de XML Query2 (Nommé aussi XQuery, c’est un langage qui offre
la possibilité d'extraire des informations d'un document XML).
2
Norme XQuery du W3C (en anglais): http://www.w3.org/TR/xquery
Comparaison des SGBD Libres.doc
9/49
CNAM 2005-2006
Berkeley DB fonctionne sur les systèmes suivants :
¾ Linux
¾ Windows
¾ Et sur tous les systèmes supportant JAVA.
2.2.2 Firebird (http://www.firebirdsql.org/)
Ce challenger de MySQL, en terme de part de marché, est l'étoile montante dans le firmament
des SGBD libres. La dernière version stable à l'heure où ces lignes sont écrites est la version
1.5, la version 2.0 étant encore en bêta. Les bases de données supportées par Firebird vont de
la petite base ne contenant que quelques lignes à de bases de taille importante en comportant
plusieurs millions.
Firebird 2 possède un grand nombre de nouvelles fonctionnalités. Parmi celles-ci, les tables
temporaires alimentées par requête (derived table), une augmentation de la taille maximale
des tables, une gestion améliorée des index supprimant la limite de 252 octets, une
amélioration de l'optimisation des requêtes et le support des sauvegardes incrémentales à
chaud.
La liste des fonctions supplémentaires qui seront présentes dans la version 3 est longue3.
Parmi celles-ci, l'amélioration du support de la norme SQL : le SGBD supportera les requêtes
récursives, la notion de schéma et même les grappes (clusters).
Firebird fonctionne sur les systèmes suivants :
¾
¾
¾
¾
¾
Linux
Unix: Solaris, HP-UX
BSD: FreeBSD
Apple: Mac OS
Windows
Firebird ne rivalise pas encore en terme de fonctionnalités ou de support de la norme SQL
avec les grands SGBD libres. Mais sa communauté active, son développement rapide, ainsi
que la succession de versions comportant des améliorations toujours plus impressionnantes,
en font une valeur sure dont il convient de suivre l’évolution.
3
Liste complète : http://www.firebirdsql.org/devel/engine/roadmap2006.html
Comparaison des SGBD Libres.doc
10/49
CNAM 2005-2006
2.2.3 Apache DB (http://db.apache.org/)
Le projet Derby est une base de données entièrement écrite en Java. Ce SGBD ne supporte
pas la gestion des droits utilisateurs, ni complètement les jointures externes ou encore les
déclencheurs, il ne peut donc pas être pris en compte dans ce comparatif.
Derby fonctionne sur les systèmes suivants :
¾ Linux
¾ Windows
¾ Et sur tous les systèmes supportant JAVA.
2.2.4 HypersonicSQL (http://hsqldb.org/)
HypersonicSQL est un SGBD écrit en java. Il respecte l’ensemble des critères énoncés
comme les transactions, les déclencheurs, la gestion des droits des utilisateurs. Par contre, il
ne supporte pas les grappes (clusters).
HypersonicSQL fonctionne sur les systèmes suivants :
¾ Linux
¾ Windows
¾ Et sur tous les systèmes supportant JAVA.
2.2.5 PicoSQL
(http://www.picosoft.it/picosql/)
PicoSQL est un SGBD écrit dans le but de pouvoir être facilement installé et d’utiliser peu de
mémoire. Il supporte les transactions et même les vues en mise à jour. Malheureusement, la
gestion des déclencheurs ainsi que celle des droits des utilisateurs ne sont pas encore
présentes.
PicoSQL fonctionne sur les systèmes suivants :
¾ Linux
¾ Windows
Comparaison des SGBD Libres.doc
11/49
CNAM 2005-2006
2.2.6 Daffodil DB (http://www.daffodildb.com/)
Daffodil DB et One$DB, sa version libre et gratuite, contiennent les mêmes fonctionnalités.
Entièrement écrit en Java, ce SGBD est basé sur la norme SQL-99. Pour information, l’objet
relationnel n’est pas implémenté dans Daffodil DB.
One$DB fonctionne sur les systèmes suivants :
¾ Linux
¾ Windows
¾ Et sur tous les systèmes supportant JAVA.
2.2.7 SQLite (http://www.sqlite.org)
SQLite est une petite librairie C qui gère un SGBD sans configuration. Le manque de
fonctionnalités importantes , comme les déclencheurs, les jointures externes, les clés
étrangères ainsi que l’inexistence de la gestion des droits utilisateurs (Grant et Revoke), fait
que ce SGBD ne sera pas l’objet d’une étude approfondie.
2.3 Synthèse et Choix
Firebird est, en terme de part de marché, deuxième derrière MySQL, mais le développement
rapide de ce SGBD en terme de fonctionnalités n’est pas suffisant en soi et, à ce jour, il ne
peut entrer dans ce comparatif de par une non implémentation de pans complets de la norme
SQL et du non support des grappes (clusters).
SQLite ne possède pas les fonctionnalités nécessaires, la gestion des droits, par exemple, est
inexistante. Cette base de données ne peut également entrer dans ce comparatif.
HypersonicSQL, One$DB et SleepyCat (Berkeley DB) n'autorisent pas les évolutions du
système grâce aux grappes (cluster). SleepyCat (Berkeley DB) est plus souvent intégré dans
d’autres offres logicielles comme MySQL ou Oracle. Ces SGBD ne supportant pas
l'évolutivité voulue et notamment les grappes (clusters), nous arrêterons ici leur étude.
Comparaison des SGBD Libres.doc
12/49
CNAM 2005-2006
Synthèse :
Une analyse rapide de l'évolutivité des SGBD et des fonctionnalités nous font donc retenir
pour une analyse approfondie les SGBD suivants : MySQL, MaxDB, PosgreSQL, Ingres.
Nous venons donc de voir quels SGBD répondent au cahier des charges suivant :
¾ Le système doit assurer la fiabilité des données : la possibilité d’exécuter des
transactions4 doit être présente.
¾ Le système doit offrir la possibilité à plusieurs utilisateurs de manipuler les données
concurremment, c'est-à-dire qu'il doit gérer des utilisateurs différents et verrouiller les
enregistrements.
¾ Le système doit assurer la confidentialité des données par l'existence d'une gestion des
droits utilisateurs. Ce point sera abordé dans la partie consacrée à l'administration.
¾ Le système doit être évolutif, notamment avec la possibilité de gérer des grappes
(clusters).
¾ Le système doit offrir, à l'utilisateur, la possibilité d'interroger la base de façon
simple : support du langage SQL.
Comme nous l'analyserons dans le chapitre suivant, le standard SQL a évolué au cours du
temps et des dérives, sous forme de dialectes différents, sont apparues. Nous verrons
comment les SGBD ont adapté et supportent ce langage SQL, lequel est autant employé par
l’utilisateur lors de l'interrogation de la base, que par l’administrateur pour assurer l’intégrité
des données avec l’aide des déclencheurs (triggers).
4
MySQL supporte les transactions avec le système de fichier InnoDB mais pas avec le système de fichier
MyIsam par exemple.
Comparaison des SGBD Libres.doc
13/49
CNAM 2005-2006
3 Le langage des SGBD : SQL
Le langage SQL, acronyme de Structured Query Language, a été développé par IBM dans les
années soixante dix afin de traiter les données contenues dans les ordinateurs centraux
(mainframes). Le cœur du SQL est le modèle relationnel. On pourrait définir ce modèle
comme un modèle ensembliste simple. Le modèle relationnel a été inventé en 1970 par Dr
E.F. Codd, un chercheur d’IBM5. Le projet System/R a commencé en 1974 et a produit un
langage nommé SEQUEL (Structured English QUEry Language). Celui-ci a été implémenté
dans un prototype nommé SEQUEL-XRM durant les années 1974-1975. Plus tard, IBM a
rajouté les tables multiples et la gestion de plusieurs utilisateurs en simultané. Cette version 2,
SEQUEL/2, a été renommée « SQL ».
SQL est utilisé pour créer, maintenir et interroger des bases de données relationnelles et il
utilise des mots courants de la langue anglaise pour la plupart des commandes, ce qui le rend
facile à utiliser. Il est souvent amélioré par l’utilisation d’un langage de programmation. La
différence fondamentale entre SQL et un langage de programmation standard est que SQL est
déclaratif. On spécifie les données à extraire de la base et le SGBD est le seul à déterminer les
actions à entreprendre pour retrouver ces données.
Le paragraphe suivant montrera qu’il existe différents SQL et pourquoi ils existent. Leur
étude engendrera l'analyse des différentes fonctionnalités supportées par les SGBD, et dans
quelle mesure ils les supportent.
3.1 Normes et dialecte SQL
La norme SQL, ou devrait-on plutôt dire les normes SQL, ont évoluées au cours des années.
Afin de les distinguer, le nom de la norme SQL est donc suffixé de l'année de sortie. En 1986,
l’American National Standard Institute (ANSI) valide la première norme SQL, SQL-86. Elle
a, depuis, été adoptée par l’International Organization for Standardization (ISO). SQL est un
standard international, nombre de SGBD le supportent. Cependant chaque SGBD possède des
extensions propriétaires pour des raisons commerciales mais aussi historiques.
Une première révision du standard, connue sous le nom SQL-89, mais aussi sous le nom
SQL1, a été publiée en 1989. Elle ajoute l'intégrité référentielle à la norme SQL-86.
Malheureusement des pans entiers de cette nouvelle norme sont restés inachevés et donc
incomplets à cause de conflits d’intérêts d’ordre commerciaux. Afin de faire de la norme un
véritable standard, l’ANSI a publié une version majeure en 1992. Nommée SQL-92, connue
aussi sous le nom SQL2, cette version ne laisse que peu de place à l’interprétation. Son
descriptif passe ainsi de 100 pages à 600. La norme définit trois niveaux de conformité
possibles. Chaque niveau ajoute des fonctionnalités supplémentaires à celles du niveau
précédent. Les noms de ces différents niveaux sont « entrée » (Entry), « intermédiaire »
(Intermediate) et « complet » (Full).
5
Dr. E. F. Codd, A Relational Model of Data for Large Shared Data Banks,
http://www.cs.duke.edu/~junyang/cps216/papers/codd-1970.pdf
Comparaison des SGBD Libres.doc
14/49
CNAM 2005-2006
Il existe un « quatrième » niveau nommé "Transitional" se situant entre le niveau « entrée » et
le niveau « intermédiaire ». Il ne fait pas partie de la norme ANSI SQL mais est proposé par
le Federal Information Processing Standards Publications (FIPS PUBS)6.
Lorsqu’un SGBD clame qu’il respecte la norme SQL-92, il devrait préciser, ce qui est
important, le niveau. Nous verrons dans la prochaine section, une description condensée des
différences entre ces niveaux.
En 1999, une nouvelle version de la norme, nommée SQL-99, mais aussi connue sous le nom
de SQL3, a été diffusée. Elle introduit de nouveaux concepts avancés comme les concepts de
l’objet relationnel ou encore du langage de programmation structurée SQL/PSM. Elle reprend
le principe des niveaux de la norme SQL-92 (Entry, Intermediate et Full) mais les remplace
par les siens : SQL-99 Central (Core) et Modules/Améliorations (Non-core, Enhanced). Pour
des facilités de lecture, la norme a été découpée en parties; les mille cinq cents pages ont été
scindées en neuf volumes traitant, chacun, d’un domaine particulier : Sql, Sql/Psm, etc...
L’objet relationnel reste l’avancée majeure de cette norme, l’intégration d’XML fait ses
débuts. SQL-99 a également normalisé les déclencheurs, même si beaucoup de SGBD les
possédaient déjà. Cet ordre chronologique (les déclencheurs écrits avant la norme ne la
respectent pas entièrement) engendre des problèmes de portabilité. Par exemple, entre Oracle
et son PL/SQL ou Microsoft SQL Server et son Transac-SQL, ces langages ne sont pas
totalement compatibles, ni compatibles avec SQL/PSM d'ailleurs.
Récemment, en 2003, l’ANSI/ISO a rendu publique la norme SQL-2003. Les fonctionnalités
les plus marquantes de cette nouvelle norme sont les suivantes :
¾ Une augmentation des types de collections (Objets gérant des ensembles d’objets : les
tableaux, les listes, les arbres sont des exemples de collection)
¾ Une amélioration des spécifications de l’objet relationnel.
¾ Des nouvelles références dans XML.
Nous pouvons déduire aisément des paragraphes précédents que SQL-92 était le précédent
standard et SQL-99 le standard actuel, même si peu de bases de données implémentent l’objet
relationnel. Dans les SGBD libres, seul PosgreSQL supporte l’objet relationnel. Dans les
systèmes commerciaux, Oracle possède cette fonctionnalité. SQL-2003 sera le prochain
standard supporté par les SGBD. Cette norme, très récente est peu implémentée dans les
SGBD; la partie XML est la plus fréquemment présente mais elle trouve ses fondements dans
la norme SQL-99. La norme SQL-2003 étant très peu supportée, elle ne sera pas analysée.
Etudions maintenant plus en détails les principaux apports de la norme SQL-92 avant de voir
rapidement les ajouts apportés par SQL-99.
6
Federal Information Processing Standards Publication 127-2, Announcing the Standard for
Database Language SQL, http://www.itl.nist.gov/fipspubs/fip127-2.htm
Comparaison des SGBD Libres.doc
15/49
CNAM 2005-2006
3.2 SQL-92, les différents niveaux
3.2.1 Le niveau « entrée »
Le niveau « entrée » est avant tout une correction de la norme SQL-89. Il apporte un
complément de commandes manquantes mais pourtant indispensables à l’usage. Il met en
place une standardisation des codes de retour des commandes via une variable nommée
SQLSTATE et la possibilité de nommer les colonnes par n’importe quel mot.
Auparavant, les codes n’étaient pas normalisés. La variable SQLCODE contenait zéro si tout
allait bien, cent si la requête ne renvoyait pas de données et une valeur négative en cas
d’erreur. Mais il n’y avait aucune normalisation des codes négatifs, entraînant l’écriture de
programmes non portables. SQLCODE a été conservé pour des problèmes de compatibilité.
SQLSTATE est un code normalisé à 5 caractères remplaçant SQLCODE. Chaque caractère
peut être une lettre majuscule (de A à Z) ou un chiffre (de 0 à 9). Les deux premiers caractères
déterminent la classe d’erreur et les trois derniers une sous-classe. Par exemple l’erreur 01504
se découpe de la façon suivante :
¾ classe d’erreur 01Î 01 : Avertissement
¾ sous classe d’erreur 504Î 504 : la requête UPDATE ou DELETE ne contient pas de
clause WHERE.
La norme ajoute aussi la possibilité de renommer les colonnes et d’utiliser les mots clés du
langage en tant que nom de table ou colonne en l’insérant entre des guillemets. Tous les
SGBD actuels annonçant supporter SQL-92, doivent respecter au minimum ces fonctions en
plus du langage SQL pur.
3.2.2 Le niveau « Intermédiaire »
3.2.2.1 Les variables
Le niveau « Intermédiaire », en plus des fonctions du niveau « entrée », offre un meilleur
support pour les données de type temps.
Il introduit les types suivants :
¾ DATE qui contient un jour, un mois et une année
¾ TIME qui contient une heure, une minute, une seconde
¾ TIMESTAMP qui est la concaténation d’une date et d’une heure au sein de la même
donnée
Comparaison des SGBD Libres.doc
16/49
CNAM 2005-2006
¾ INTERVAL supporte deux précisions. Dans la première, l’intervalle est compté en
mois et année. Dans la seconde, l’intervalle est compté en seconde minute heure et
jour.
Il est à noter que SQL-92 supporte les opérations arithmétiques sur les dates, comme par
exemple l’addition ou la soustraction de dates.
SQL-92 admet la création de domaines qui sont, néanmoins, des sous-ensembles de domaines
existants et offre la possibilité d'inclure des contrôles d’intégrité.
Exemple : création du domaine Monnaie par la commande suivante :
CREATE DOMAINE Monnaie is DECIMAL(5,2)
CHECK(VALUE>0)
NOT NULL
Le niveau intermédiaire propose, également, les améliorations suivantes :
¾ Différents alphabets. Les caractères propres à chaque pays sont gérés.
¾ Le support des collations (COLLATE). Cette fonctionnalité entraîne le support de
plusieurs ordres de comparaison pour les chaînes de caractères et, ainsi, gère
correctement les caractères accentués lors des phases de tris. Un tri binaire, au vu du
code ASCII des caractères, aurait donné : « parle, parleur, parloir, parlé » alors que
l’on s'attend, de façon naturelle, à avoir « parlé » entre « parle » et « parleur ».
¾ La concaténation de chaînes par l’opérateur « || » et l’extraction de sous-chaînes par la
commande SUBSTRING.
¾ La conversion d’un type de donnée en un autre par la commande CAST
¾ Les chaînes de longueurs variables (VARCHAR) et la possibilité de connaître la
longueur d’une chaîne par la commande LENGTH.
3.2.2.2 L’intégrité référentielle
L’intégrité référentielle7 a été améliorée dans SQL-92. Précédemment, toute violation
entraînait le rejet de la mise à jour. La nouvelle norme définit d'éventuelles actions correctives
en cas de violations lors de la suppression d’un tuple :
¾ Supprimer en cascade (ON DELETE CASCADE). Cette option supprime tous les
tuples dont dépend le tuple supprimé.
¾ Rendre nul l’attribut référençant (ON DELETE SET NULL)
7
L’intégrité référentielle est un jeu de règles empêchant d'effacer ou de modifier par inadvertance des données
d'une table lorsque cela peut déclencher des problèmes sur une table en relation (clés étrangères par exemple)
Comparaison des SGBD Libres.doc
17/49
CNAM 2005-2006
Il est maintenant possible d’exécuter les contraintes d’intégrité à la fin de chaque opération
(IMMEDIATE) ou la fin de la transaction (DEFERRED) :
SET CONSTRAINTS { ALL | « nom de la contrainte » } {DEFERRED | IMMEDIATE}
Pour en terminer avec l’intégrité, SQL-92 définit quatre niveaux d'isolation de transaction
pour empêcher trois phénomènes indésirables de se produire lors de transactions concurrentes
qui sont :
¾ La lecture inconsistante : une transaction lit des données écrites par une transaction
concurrente qui n’est pas encore validée.
¾ La lecture non répétitive : Une transaction relit des données qu'elle a lues
précédemment et trouve que ces données ont été modifiées par une autre transaction
(validée depuis la lecture initiale).
Comparaison des SGBD Libres.doc
18/49
CNAM 2005-2006
¾ Les lignes fantômes : Une transaction exécute à nouveau une requête renvoyant un
ensemble de lignes satisfaisant à une condition de recherche et trouve que l'ensemble
des lignes satisfaisant à cette condition a changé du fait d'une autre transaction
récemment validée.
Ces phénomènes peuvent ou ne peuvent pas se produire en fonction du niveau d’isolation.
Les niveaux d'isolation des transactions définis sont les suivants :
Niveau d'isolation
Lecture de données
non validées
(Uncommitted Read)
Lecture de données
validées
(Committed Read)
Lecture répétée
(Repeatable Read)
Sérialisable
(Serializable)
Lecture
inconsistante
Lecture non
répétitive
Lignes fantômes
Possible
Possible
Possible
Impossible
Possible
Possible
Impossible
Impossible
Possible
Impossible
Impossible
Impossible
Le niveau « Sérialisable » supprime tous les effets des accès concurrents mais en contrepartie
il est également le plus lent. Une requête avec un niveau d’isolation « Lecture de données non
validées » pourrait lire des enregistrements verrouillés en écriture par une autre requête et
donc ne pas attendre le relâchement des verrous8. Pour certains types de requêtes, comme
celles utilisées dans un entrepôt de données, le fait de ne pas avoir les dernières données à
jour n’est pas significatif.
8
Mécanisme gérant la simultanéité d’accès afin d’éviter des incohérence (deux écritures en même temps).
Comparaison des SGBD Libres.doc
19/49
CNAM 2005-2006
3.2.2.3 Union, intersection et différence
SQL-89 supportait uniquement l’union de deux ensembles de résultats :
Select Co1,Col2
From Table1
UNION
Select Co1,Col2
From Table2
SQL-92 étend les opérations ensemblistes disponibles par l’ajout des commandes gérant
l’intersection (INTERSECT) et la différence (EXCEPT) d'ensembles.
3.2.2.4 Les jointures
Les jointures externes ont la capacité de prendre en compte les éléments n’ayant pas de
correspondance dans l’autre table lors d’une jointure.
SELECT ...
FROM table1
LEFT OUTER JOIN table2
ON <Conditions de jointure>
Toutes les lignes de table1 seront affichées que <Conditions de jointure> soit réalisée ou non
dans table2. La requête suivante affiche tous les étudiants, qu'ils aient ou non une note. Si un
étudiant n'a pas de note, le champ Numetu de la table correspondante (NOTES) aura la valeur
NULL. :
SELECT DISTINCT(E.Nometu), N.Numetu
FROM ETUDIANT E
LEFT OUTER JOIN NOTES N
ON E.Numetu=N.Numetu;
On peut ainsi retenir les éléments n’ayant pas de correspondance dans la table de gauche
(LEFT OUTER). Sur le même principe, on peut aussi retenir les éléments n’ayant pas de
correspondance dans celle de droite (RIGHT OUTER) ou dans les deux (FULL OUTER)
3.2.2.5 Les Schémas
SQL92, grâce à la commande ALTER TABLE, offre la possibilité de modifier le schéma9.
La commande est : ALTER TABLE <Nom de la table> <Modification>
9
Ensemble des objets (tables, index, déclencheurs…) propriété d'un utilisateur
Comparaison des SGBD Libres.doc
20/49
CNAM 2005-2006
Les différentes modifications possibles sont :
¾
¾
¾
¾
¾
Ajout d’une colonne (ADD COLUMN)
Modification de la définition d’une colonne (ALTER COLUMN)
Suppression d’une colonne (DROP COLUMN)
Ajout d’une contrainte (ADD CONSTRAINT)
Suppression d’une contrainte (DROP CONSTRAINT)
3.2.2.6 Exécution de requêtes
L’exécution d’une requête nécessite deux grandes phases : la première est la phase de
compilation de la requête (PREPARE), la deuxième est celle d’exécution (EXECUTE). Avec
SQL-89, ces deux phases étaient indissociables. Avec SQL-92, il est possible de lancer la
compilation de la requête (PREPARE), puis d’exécuter plusieurs fois la requête (EXECUTE).
3.2.3 Le niveau « Complet »
Les fonctionnalités supplémentaires apportées par le niveau « complet » sont les suivantes :
¾ Support de contraintes d’intégrité portant sur plusieurs tables grâce à la clause
CHECK.
¾ Maintenance de vues concrètes (MATERIALIZED VIEW). Une vue concrète est une
table contenant une copie physique de tuples insérés dans d'autres tables. Les vues
concrètes offrent la possibilité, par exemple, d’optimiser les requêtes lorsque les
données changent peu en stockant les jointures. Des déclencheurs (triggers) sont
utilisés pour leur mise à jour.
¾ Support de tables temporaires. Elles sont détruites en fin de transactions.
¾ Possibilité de faire un select dans la clause from, engendrant la création d’une table
temporaire.
Exemple :
Select Nom, Prenom
From (Select * from Etude where diplome=’ingenieur’);
¾ Possibilité de renommer les colonnes afin de les faire correspondre dans les requêtes
comportant un des termes suivants : UNION, INTERSECT, EXCEPT.
¾ Extension du support des dates et temps avec support des fuseaux horaires et
possibilité de définir des échelles de temps pour le type INTERVAL
Comparaison des SGBD Libres.doc
21/49
CNAM 2005-2006
¾ Création d'un type de donnée BIT pour supporter les objets binaires comme les
images
3.3 SQL-99
La première forme normale de Codd (valeur atomique c'est à dire non composée) est
inadaptée aux objets complexes tel que les documents structurés (document XML) ou les
données multimédias. C’est pourquoi la norme SQL-99 a introduit une dénormalisation de la
première forme normale avec l'objet relationnel ou encore avec les objets larges tels que les
CLOB (Character Large Object) et les BLOB (Binary Large Object). Néanmoins, la recherche
dans les objets larges (Large Object) est limitée et séquentielle. L’absence de pointeurs
visibles était, dans SQL-92, pénalisante. Stocker un gros objet et utiliser des références à
plusieurs endroits dans la base génère des économies de place. Il est aussi possible d’éviter de
faire de coûteuses jointures grâce à ces références. L’objet relationnel est fait pour palier aux
points faibles du modèle relationnel.
SQL-99 a introduit les modifications principales suivantes :
¾ Suppression du type de donnée BIT qui servait à supporter les objets binaires. L’objet
relationnel le remplace avantageusement.
¾ Extension des fonctions comparaisons floues
SQL-92 les prenait en compte grâce à l’instruction LIKE. SQL-99 en étend les
possibilités grâce à l’instruction SIMILAR et par l’utilisation d’expressions régulières
façon
UNIX :
WHERE
NAME
SIMILAR
‘(SQL(86|89|92|99))|(SQL(1|2|3))’. Cette ligne prend en compte les versions de
SQL sorties avant l’année 2000.
¾ Utilisation de nouveaux type de données :
o
o
o
o
o
BLOB
CLOB
BOOLEAN
ARRAY : JourDelaSemaine VARCHAR(10) ARRAY[7]
ROW : NID ROW (NOM VARCHAR(50), PRENOM VARCHAR(50))
SELECT E.NID.NOM
FROM Employé E
¾ Ajout de la possibilité de définir des types propres à l’utilisateur par la commande
CREATE TYPE.
Comparaison des SGBD Libres.doc
22/49
CNAM 2005-2006
¾ Possibilité de mettre à jour les vues (View) :
Dans la norme SQL-92, les vues n’étaient qu’en lecture seule. Les applications étaient
donc obligées de modifier les tables sous-jacentes à la vue en cas de mise à jour, ce qui
enlève une partie de l’intérêt des vues.
¾ Création de points de sauvegarde (Savepoint) :
Ces points de sauvegarde agissent comme des sous ensembles de transaction. Dans
une transaction, il est possible d’annuler un sous ensemble de la transaction et donc de
remonter à l’état du dernier point de sauvegarde sans pour autant annuler la transaction
en entier. ROLLBACK TO SAVEPOINT Nom et RELEASE SAVEPOINT agissent
respectivement comme les ROLLBACK et COMMIT d’une transaction. SET
SAVEPOINT Nom définit un point de sauvegarde nommé « Nom ».
¾ Introduction de XML avec la possibilité de retrouver les données grâce à XQUERY.
Cette partie est étendue dans SQL-2003.
¾ Apparition des requêtes récursives.
Par exemple, sur une table « Etapes » composée de deux colonnes, « VilleDépart » et
« VilleArrivée », on pourrait exécuter la requête suivante :
SELECT VilleArrivée
START WITH VilleDépart=’Montpellier’
CONNECT BY PRIOR VilleArrivée=VilleDépart;
¾ Création de rôles facilitant la gestion des droits des utilisations par définition
d'ensembles de droits « prêts à l’emploi ». On prépare un rôle « Mise a jour des
clients » en lui attribuant les droits nécessaires. Il suffira ensuite d’attribuer ce rôle à
l’ensemble des utilisateurs devant mettre à jour les clients.
¾ Mise en place de déclencheurs (Trigger).
Un déclencheur est une procédure compilée qui s’exécute automatiquement lorsque
l’évènement associé le déclenchant se produit comme la mise à jour d’une colonne
dans la base.
¾ Utilisation du langage de programmation SQL/PSM qui est un langage procédural.
L’écriture de procédures stockées ou de déclencheurs dans ce langage est possible.
Comparaison des SGBD Libres.doc
23/49
CNAM 2005-2006
¾ Création d'extensions nécessaires à l’objet relationnel comme la notation pointé
(Employé.Salaire), la définition d'héritage etc.
Avant de voir les tableaux récapitulatifs des fonctionnalités supportées par les SGBD,
précisons la légende :
Ce que l’on peut retenir du tableau suivant est que le SGBD possédant le plus grand nombre
de fonctions des normes SQL92 et SQL99 est PosgreSQL.
Les principales fonctions manquantes de PosgreSQL version 8.1 sont :
¾
¾
¾
¾
les vues modifiables. Cette fonctionnalité est prévue dans la prochaine version.
les vues concrètes
le Check sur plusieurs tables
les requêtes récursives
Comparaison des SGBD Libres.doc
24/49
CNAM 2005-2006
Tableau récapitulatif SQL - Page 1/2
Tableau récapitulatif SQL - Page 2/2
Comparaison des SGBD Libres.doc
26/49
CNAM 2005-2006
4 Les déclencheurs
Après avoir étudié comment le langage SQL est supporté par les différents SGBD, voyons
comment il est utilisé, en conjonction avec les déclencheurs, afin de faciliter l’intégrité des
données.
4.1 Qu’est-ce qu’un déclencheur
Un déclencheur (Trigger) est une suite d'instructions stockée dans une base de données (sorte
de procédure stockée). Il est associé à une table de la base de donnée. Lorsqu’un événement
(mise à jour d’un champ par une requête, suppression…) se produit sur celle-ci, la suite
d'instructions est exécutée automatiquement, contrairement aux procédures stockées qui
doivent être appelées.
Ces événements sont au nombre de six :
¾
¾
Before Update : Avant une mise à jour de la table
After Update : Après une mise à jour de la table
¾
¾
Before Insert : Avant une insertion dans la table
After Insert : Après une insertion dans la table
¾
¾
Before Delete : Avant une suppression dans la table
After Delete : Après une suppression dans la table
Lorsqu’une requête va tenter de mettre à jour, d’insérer une valeur ou d’en supprimer une, il
est donc possible de lancer une suite d'instructions avant ou après le passage de cette requête.
Le programme peut être lancé, soit pour chaque ligne modifiée de la table (FOR EACH
ROW), soit une seule fois pour l’ensemble des lignes (FOR EACH STATEMENT).
Lors de l’écriture d’un déclencheur de type ligne (ROW), il est possible d’avoir les valeurs
avant (OLD) et après modification (NEW). Ceci est également valable dans le cas d’un
déclencheur de type « Statement », les variables se nomment alors OLD_TABLE et
NEW_TABLE et contiennent l’ensemble des enregistrements avant et après altération par la
requête.
Le mot clé ATOMIC, précise que le corps du trigger doit être exécuté comme une transaction,
c'est à dire d'un seul coup et sans concurrence (exécution atomique).
La ligne [WHEN condition] rajoute une condition à vérifier avant l'exécution de la requête.
CREATE TRIGGER NomTrigger
{ BEFORE | AFTER } { INSERT | DELETE | UPDATE } ON NomTable
{ REFERENCING
OLD [ROW] [AS] NomAnciennesValeursLigne
| NEW [ROW] [AS] NomNouvellesValeursLigne
| OLD TABLE [AS] NomAnciennesValeursTable
| NEW TABLE [AS] NomNouvellesValeursTable }
FOR EACH { ROW | STATEMENT }
[WHEN condition]
BEGIN [ATOMIC]
Code du trigger
END
Exemple :
TABLE test1 (a1 INT);
TABLE test2 (a2 INT);
CREATE TRIGGER RecopieTest1 BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
END;
Les déclencheurs lance donc des requêtes lors de certains événements. Attention, une requête
lancée dans un déclencheur peut provoquer le démarrage d'un autre déclencheur. Il appartient
au développeur de vérifier les boucles infinies pouvant se produirent.
Comme nous venons de le voir, les déclencheurs lancent des requêtes lors de certains
évènements, mais il est possible d’aller au-delà de ce simple lancement. En effet, les
déclencheurs peuvent être écrits dans le même langage que les procédures stockées, à savoir
SQL/PSM. L’utilisation d'un langage structuré renforce les possibilités des déclencheurs.
4.2 Langage SQL/PSM
Le langage SQL/PSM (Persistent Stored Module) est le langage procédural normalisé dans la
norme SQL-99. La notion de « Module » est la suivante : un module contient un ensemble de
fonctions ou procédures stockées partageant des propriétés similaires, comme travailler sur la
même table ou être une suite logique d’opération.
SQL/PSM est utilisable dans les procédures stockées et les déclencheurs. Nous allons
maintenant en voir les principes de base.
4.2.1 Déclaration
La création d'un module (ensemble de fonctions ou procédures stockées) se fait par
l'instruction suivante :
CREATE MODULE ModuleName LANGUAGE [SQL|C| …]
…
END MODULE
Comparaison des SGBD Libres.doc
28/49
CNAM 2005-2006
On remarquera dans cette instruction comme dans la suivante, le mot clé LANGUAGE qui
offre la possibilité du choix du langage et donc de ne pas utiliser obligatoirement le langage
SQL/PSM. Les procédures ou les fonctions stockées peuvent s’écrire avec différents langages
comme C, Ada, Cobol, Pascal…
Exemple en java :
CREATE OR REPLACE PROCEDURE AjouteEmp (empno NUMBER, ename VARCHAR2, sal
NUMBER, deptno NUMBER)
AS LANGUAGE JAVA
NAME ‘Employee.AjouteEmp( int, java.lang.String, float, int)’;
Un des avantages du langage SQL/PSM par rapport à un langage évolué tel que C ou Java est
qu'il n'y pas besoin d'une conversion de type entre les données de la base et les variables
utilisées pour les stocker.
Voici un exemple de module :
MODULE gestion_compte LANGUAGE SQL;
DECLARE total DECIMAL(16,2);
FUNCTION solde (SQLCODE, client_id) RETURNS FLOAT;
SELECT SUM(CPT_CREDIT) - SUM(CPT_DEBIT) INTO total
FROM COMPTE_BANCAIRE
WHERE CLI_ID = client_id;
RETURN total;
PROCEDURE credite (SQLCODE, client_id, montant DECIMAL(16,2));
INSERT INTO COMPTE BANCAIRE (CLI_ID, CPT_CREDIT, DATE_HEURE_TRANSACTION)
VALUES (client_id, montant, CURRENT_TIMESTAMP);
PROCEDURE debite (SQLCODE, client_id, montant DECIMAL(16,2))
INSERT INTO COMPTE BANCAIRE (CLI_ID, CPT_DEBIT, DATE_HEURE_TRANSACTION)
VALUES (client_id, montant, CURRENT_TIMESTAMP);
END MODULE;
4.2.2 Contrôle de l’exécution
L'instruction suivante fait des tests du type « Si…Alors…Sinon » :
If <Cond1>
Then <Instruction>
Else
If <Cond2>
Then <Instruction2>
EndIf;
EndIf;
Comparaison des SGBD Libres.doc
29/49
CNAM 2005-2006
L’instruction suivante effectue une suite d’instructions en fonction du contenu de la variable :
CASE valeur
When 1 Then <Instruction1>
When 2 Then <Instruction2>
Else <Instruction3>
END CASE
Les instructions suivantes réalisent des boucles :
LOOP
<Instruction>
END LOOP
L'instruction Loop est une instruction répétant une boucle à l'infinie. L'instruction LEAVE
entraîne la sortie de la boucle.
L'instruction « Tant…que » exécute le code présent au sein de sa boucle tant que la condition
est vraie.
WHILE <Cond>
END WHILE
L'instruction « Répéter…Jusqu'à » exécute le code présent au sein de sa boucle jusqu'à ce que
la condition soit vraie.
REPEAT
UNTIL
END REPEAT
L'instruction suivante parcourt le curseur :
FOR <variable de boucle> AS <Curseur> DO <Instruction>
END FOR
Après avoir vu les principes de base de SQL/PSM, analysons maintenant comment ces
fonctions sont supportées par les différents SGBD.
Comparaison des SGBD Libres.doc
30/49
CNAM 2005-2006
4.3 Support de SQL/PSM
L'appel de procédures stockées peut s'effectuer comme l'envoi d'une requête à la base et cela,
dans à peu près tous les langages. Par contre, les bases de données ne supportent pas n'importe
quel langage pour l'écriture des procédures stockées.
La notion de module que nous venons de voir, à savoir une compilation de fonctions, n'existe
pas dans les SGBD libres. Il est possible de préciser le mot clé LANGAGE au niveau de la
syntaxe de la procédure afin de déterminer dans quel langage celle-ci a été écrite.
Toutes les bases de données libres supportent le langage SQL/PSM avec, tout de même, un
bémol pour la base de données Ingres 2006 qui n'accepte qu'un sous ensemble des fonctions.
Seul PosgreSQL supporte l'écriture de procédures stockées et déclencheurs dans divers
langages, tel que TCL, Perl et Python.
Pour des raisons chronologiques, le langage SQL/PSM est supporté par les bases de données
libres, plus qu'il ne l'est dans les bases de données commerciales. En effet, ces dernières ont
implémenté un équivalent au langage SQL/PSM bien avant la publication de la norme
SQL-99. Cette dernière aurait du sortir en 1996 au lieu de fin 1999, ce qui a contribué a une
non conformité des SGBD commerciaux. C'est, par contre, un point fort pour les SGBD
libres, qui ayant mis en place ce langage après la publication de la norme, ont pu la respecter
fidèlement. Les déclencheurs peuvent aider à maintenir l'intégrité de la base, laquelle fait
partie des tâches de l'administrateur de base de données. Avant de lister les tâches
d'administration existant sur les SGBD, voyons comment un SGBD peut s’interfacer avec un
langage de programmation afin de développer de nouvelles applications, comme des
applications de maintenance par exemple.
4.4 Interfaces avec les langages de programmation.
4.4.1 SQL intégré
Lorsque le SQL est intégré, on insère les instructions SQL dans celles du langage de
programmation. Un préprocesseur est nécessaire pour convertir les instructions SQL en appels
à des procédures fournies dans une librairie du SGBD. Chaque préprocesseur est donc propre
à un SGBD et la façon d'invoquer le SQL est, par conséquent, différente d'un SGBD à l'autre.
Le gros défaut de cette approche réside dans le fait que les instructions SQL sont spécifiées
dans le code source, de façon statique et en fonction du SGBD. Les performances sont
grandes (pas de traduction à faire en fonction du SGBD), ce qui constitue un avantage pour le
SQL intégré. Par contre, les instructions figées et valables uniquement pour un seul SGBD en
sont un inconvénient.
Comparaison des SGBD Libres.doc
31/49
CNAM 2005-2006
4.4.2 SQL dynamique
Le SQL dynamique offre la possibilité de construire des instructions SQL sans les connaître à
priori. L’utilité principale est de fabriquer un code générique et réutilisable. La
programmation est, certes, un peu plus complexe. Une requête peut être préparée avec divers
paramètres et ces paramètres n'être donnés qu’au moment de l’exécution. Le SGBD fait tous
les choix possibles lors de cette phase (compilation de la requête, utilisation des index…) et,
donc, passe un minimum de temps lors de l’exécution.
4.4.3 API
Chaque SGBD possédant ses propres méthodes d'accès, la tâche des programmeurs désireux
de s'y connecter ne s'en trouve pas simplifiée. Ces "API", comme on les nomme (Application
Programming Interface), sont autant de syntaxes à maîtriser afin de communiquer avec
chaque SGBD. L'idée est alors venue de fournir une librairie de fonctions, indépendante du
SGBD, afin de faciliter la programmation et d’améliorer l’interopérabilité des programmes
envers les divers SGBD.
Si on utilise une API propriétaire (comme OCI chez Oracle, libmysql.dll pour MySQL), le
code sera très efficace. Si en revanche, on utilise une API générique, telle que, par exemple
ODBC (Open Database Connectivity), la portabilité du code sera meilleure, mais l'exécution
sera moins performante car la conversion des instructions SQL en leur équivalent du SGBD
utilisé ne pourra se faire que pendant l'exécution car c'est seulement à ce moment que l'on
saura à quel type de SGBD on a réellement affaire.
C’est Microsoft qui a écrit ODBC, librairie générique d’accès aux bases de données. RDO
(Remote Data Object), est une surcouche d’ODBC également développée par Microsoft.
Celle-ci est destinée à faciliter le travail des programmeurs Visual Basic, alors qu'ODBC est
davantage tournée vers les programmeurs C/C++. Une autre technique d'accès, encore plus
simple, a ensuite fait son apparition : "DAO" (Data Access Object).
La standardisation n'était plus vraiment « standard ». Microsoft, souhaitant offrir une méthode
de connexion indépendante des sources de données, tente d'unifier encore une fois le tout et
développe une nouvelle API : "OLE-DB" (Object Linking And Embedding Database).
Puis, Microsoft toujours, afin d'aider les développeurs qui peinent à écrire des fonctions
"OLE-DB", propose la technologie ADO (ActiveX Data Object), laquelle accède à diverses
sources de données plus facilement. ADO.Net, évolution d’ADO est la dernière norme
proposée par Microsoft.
Les normes de Microsoft, actuellement utilisées sont : ODBC, ADO et ADO.NET.
Cependant, Microsoft n’est pas le seul à avoir écrit des librairies génériques. IDAPI est une
API d’accès aux bases de données, développée par Borland. Elle sait aussi interroger ODBC
et cela lui ouvre l’ensemble des bases du marché. JDBC (Java DataBase Connectivity) est
une API de connexion aux bases de données fournie avec Java.
Comparaison des SGBD Libres.doc
32/49
CNAM 2005-2006
Il suffit de disposer d’un driver supportant l’API choisie pour qu'un programme fonctionne
avec la base de donnée. Ce dernier peut, par exemple, se connecter sur une base MySQL. Si
l'on change de driver dans ODBC pour utiliser une base Microsoft Sql Serveur, un même
programme fonctionnera de la même façon.
Tableau récapitulatif :
Tous les SGBD libres de ce comparatif supportent les deux plus grands standards actuels, à
savoir ODBC et JDBC, d'où, ce critère importera peu au moment du choix.
Voyons maintenant quelles sont les tâches d’administration et en quoi les déclencheurs ou des
programmes écrits avec l’aide de ces API, peuvent venir en aide à l'administrateur.
Comparaison des SGBD Libres.doc
33/49
CNAM 2005-2006
5 Administration
La première des tâches d'administration d'une base de données, chronologiquement parlant,
est de prévoir la dimension du serveur qui va l'héberger, c'est-à-dire le nombre de processeurs,
la taille mémoire et les besoins de stockage nécessaires lors de la création de la base et, en
tenant compte, si possible des besoins futurs.
Ces choix dépendent de la finalité du système :
¾
¾
¾
¾
¾
La base doit elle être en ligne 365 jours par an ou pas ?
Combien d'utilisateurs vont accéder en simultané à la base ?
En cas de panne, quel est le temps acceptable pour restaurer une sauvegarde ?
La perte de données est-elle acceptable ou pas ?
…
Autant de questions qui vont déterminer le dimensionnement du système et le choix du niveau
de redondance des équipements afin de pallier aux pannes.
Une fois le système choisi et installé, les principales tâches de l'administrateur de base de
données sont :
¾
¾
¾
¾
¾
La gestion de la sécurité
L'optimisation des requêtes
L'optimisation de l'utilisation des disques
La gestion des sauvegardes
La conception et la création de nouvelles bases
5.1 Protection des données
La sécurité d'une base dépend principalement de la définition des droits de chaque utilisateur.
Il est cependant possible de se prémunir contre certains types d'attaques dirigées vers un
SGBD par l'utilisation d'un pare-feu (firewall) ou des éléments réseaux empêchant l'écoute
des communications, comme les commutateurs (switches). Maintenir à jour le SGBD et le
système d'exploitation en installant les mises à jour de sécurité est indispensable afin de
supprimer les risques dus aux failles. Dans tous les cas, la définition des droits des utilisateurs
est primordiale. Ils définissent quels utilisateurs ont quels droits sur quels objets et sécurise le
SGBD contre toute manipulation malencontreuse. L’écriture de programmes, ou mieux de
déclencheurs (Trigger), peut aider à vérifier l’intégrité des données.
Comparaison des SGBD Libres.doc
34/49
CNAM 2005-2006
5.1.1 Identification et authentification des utilisateurs
Les utilisateurs sont identifiés sur un SGBD par un nom de connexion (login) et par un mot de
passe. L'attaque, par un intrus essayant de se faire passer pour quelqu’un d’autre, peut se
produire en deux endroits différents :
¾ Au niveau de la base
¾ Au niveau du réseau
Au niveau de la base, il ne faut pas que n'importe quel utilisateur puisse avoir accès au
catalogue.
Une base de données « spéciale », souvent nommée catalogue ou méta-données, contient un
ensemble de tables stockant, entre autre, les informations suivantes : la liste des bases, la liste
des tables, la liste des vues, la liste des utilisateurs et de leur droits… En fait, le catalogue
contient l'ensemble des informations donnant la possibilité de gérer les bases de données
présentes dans le SGBD. Ce catalogue contient, dans une table, la liste des utilisateurs et les
mots de passes. Ces mots de passe sont chiffrés. Mais, les fonctions de hachage à sens
unique10 utilisées, comme par exemple MD5 ou SHA, offrent une résistance inégale au test de
toutes les combinaisons (Brut Force Attack).
Deuxième possibilité d'attaque, la récupération du mot de passe par écoute des trames réseaux
(Sniffing). Des protocoles comme SSL, rajoutent une couche de chiffrement à la transmission
de ces données, ce qui réduit fortement la possibilité d'écoute en rendant les données
inintelligibles pour toute personne autre que l’émetteur et le SGBD.
Tous les SGBD de ce comparatif supportent cette fonctionnalité.
5.1.2 Contrôle d'accès aux données
Les commandes GRANT et REVOKE attribuent et retirent des droits aux utilisateurs. Les droits
sont les suivants :
¾ SELECT, droit de lecture des informations. Ce droit peut être réduit à certaines
colonnes d’une ligne par SELECT (col,...)
¾ INSERT, droit de rajout de lignes
¾ UPDATE, droit de modification de lignes. Ce droit peut être réduit à certaines
colonnes d’une ligne par UPDATE (col,...)
¾ DELETE, droit de suppression de lignes
10
Dans un SGBD, x est le mot de passe et f(x) la valeur chiffrée, stockée dans la base. C’est une fonction
répondant aux critères suivants : Il est facile de calculer f(x) ayant x, il est quasi-impossible de calculer x en
ayant f(x). Si x et x' sont différents alors f(x) est différent de f(x').
Comparaison des SGBD Libres.doc
35/49
CNAM 2005-2006
¾ ALTER, droit de modification de la définition de la table
Le droit ALL contient l’ensemble des droits précédents (Insert, Update, Delete et Alter).
Un utilisateur ayant reçu un droit avec l'option GRANT peut le transmettre à son tour à
condition que le droit soit transmis avec, dans la commande, WITH GRANT OPTION.
GRANT { SELECT | INSERT | UPDATE | DELETE | ALTER | ALL }
ON {nom_table | nom_vue | nom_base …} TO nom_utilisateur
{WITH GRANT OPTION};
La suppression d’un droit se fait de la façon suivante :
REVOKE { SELECT | INSERT | UPDATE | DELETE | ALTER | ALL }
ON {nom_table | nom_vue | nom_base …}
FROM nom_utilisateur { CASCADE }
L’option CASCADE supprime les droits de l’utilisateur marqués dans la commande mais aussi
les droits de tous les utilisateurs à qui un utilisateur aurait donné ces mêmes droits. Admettons
que A donne un droit à B avec WITH GRANT OPTION et que B donne ce droit à C. Si A ne
met pas CASCADE, il retire le droit à B mais pas à C. Si A spécifie CASCADE, il retire le droit
à B et C.
Les droits sont cumulatifs. Supposons que l’utilisateur A reçoive, à la fois de l’utilisateur B et
l’utilisateur C, le droit de lire une table T, il faut que B et C retirent tous les deux ce droit de
lecture à A pour que celui-ci n’y accède plus.
PosgreSQL a ajouté des droits à ceux précédemment cités comme, par exemple, le droit de
créer des clés étrangères (REFERENCES), des déclencheurs (TRIGGER), des schémas
(CREATE) et des tables temporaires (TEMPORARY).
MySQL a aussi ajouté ses propres droits, comme par exemple, l'obligation faite à l’utilisateur
d'employer une connexion chiffrée, les limitations en terme de nombre de requêtes
(MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR) ou en terme de nombre de
connexions (MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS).
Ingres a aussi ajouté des droits comme par exemple le droit de créer des séquences (compteur
partagé et auto incrémenté) ou le droit de consulter les statistiques du SGBD.
Le tableau ci-après répertorie les droits supportés. Sa lecture en fait ressortir un point
important : tous les SGBD supportent les droits définis par l’ISO dans la norme SQL. Mais
chaque SGBD a rajouté des droits supplémentaires qui lui sont propres.
Comparaison des SGBD Libres.doc
36/49
CNAM 2005-2006
5.1.3 La notion de Rôle
Comme nous venons de le voir, la gestion des droits est définie par utilisateur. Au départ,
dans les SGBD, il n'existait pas, à proprement parler, de groupe comme pour les utilisateurs
Windows (cette notion est naissante dans MaxDB). Afin de palier à ce manque et de faciliter
la gestion des droits, la notion de rôle a été inventée. Un rôle n'est pas un groupe d'utilisateurs
mais un ensemble de droits sur un ensemble d'objets. Par exemple, une personne devant
mettre à jour les tables concernant les ressources humaines d'une entreprise, doit mettre à jour
la table des employés et la table des formations suivies. Un rôle autorisant l'écriture dans ces
deux tables peut être créé. Ce rôle sera ensuite attribué à l'ensemble des utilisateurs faisant
partie des ressources humaines afin qu'ils puissent effectuer leur mission.
La création de rôle se fait par la commande CREATE ROLE <Nom du Rôle>.
La commande GRANT attribue les droits à un rôle. La syntaxe est identique à celle utilisée
pour donner des droits à un utilisateur.
Il faut, ensuite, ajouter ce rôle aux utilisateurs concernés :
GRANT role
TO { nomutilisateur | PUBLIC } [ WITH ADMIN OPTION ]
WITH ADMIN OPTION donne à l'utilisateur le droit d'assigner, de retirer, de modifier et de
supprimer à son tour les privilèges du rôle reçus. PUBLIC est un rôle particulier car il est
attribué par défaut à tous les utilisateurs.
Il est à noter que seul le SGBD MySQL ne supporte pas les rôles.
5.1.4 Protection des échanges réseau
Comme nous l'avons déjà évoqué, les échanges réseaux sont en proie à des attaques ayant
pour but de découvrir les mots de passe des utilisateurs. Même s'il est toujours possible de
créer un tunnel SSL (Sorte de tunnel chiffré11, créé entre deux machines, dans lequel passent
les informations), un système de chiffrage implémenté au niveau du protocole de la base de
données facilite la mise en place d’une solution de chiffrage et rend les informations écoutées
incompréhensibles. Il devient impossible de voler le mot de passe. Pour une sécurité accrue, il
faut que le protocole de chiffrement soit éprouvé. Des protocoles utilisés partout dans le
monde, comme par exemple RSA, sont des bons candidats à la sécurisation des transmissions
d'informations entre le client et la base de données. Seul le SGBD Ingres ne supporte pas le
chiffrement des communications.
Il est à noter que la création de tunnels SSL est plus à la charge de l’administrateur réseaux
que de l’administrateur de base de données. Néanmoins, l’administrateur de base de données
doit avoir des compétences réseaux afin de comprendre comment les mots de passe peuvent
être découverts ou pourquoi les temps de réponses de ses requêtes sont mauvais. Le problème
ne provient pas toujours de la base de données.
11
Le mot « crypter », dérivé de l’anglais «to crypt », est utilisé dans le langage courant à la place du terme
« chiffrer », employé par les spécialistes de la cryptologie.
5.1.5 Protection des données stockées
L'accès aux fichiers de la base doit, bien sûr, être réservé au programme de base de données.
Lors de l'installation de la base sur le serveur, il est fortement conseillé de créer un utilisateur
particulier et de ne donner les droits sur le répertoire de l'application ainsi que sur les fichiers
de données qu'à cet utilisateur. Cette mesure évite qu'une personne puisse récupérer les
fichiers, les copier, et les remonter sur son propre SGBD. Les administrateurs du poste ont
évidemment accès à ces fichiers. La cryptographie peut, encore une fois, offrir un niveau de
sécurité supplémentaire en chiffrant les fichiers. Cependant la surcharge de travail pour le
processeur réserve cette méthode à des bases pour lesquelles la sécurité est cruciale. Les bases
de données étudiées ne supportent pas le chiffrage des données, sauf PosgreSQL qui offre la
possibilité de chiffrer certaines colonnes. Par contre, il est possible de chiffrer des partitions
du disque dur. Ce système étant transparent pour la base, il peut être mis en place quelle que
soit la base.
5.1.6 Intégrité du logiciel
Le logiciel du SGBD doit pouvoir faire l'objet de contrôles afin de vérifier que l'application
exécutée est bien celle qui a été validée par les autorités compétentes, et non pas une version
modifiée de manière non autorisée. Ce besoin concerne, à la fois, le logiciel serveur et les
logiciels clients. La cryptographie, grâce notamment aux fonctions de hachage, peut assurer la
non modification des programmes. Les SGBD libres ne supportent pas cette fonctionnalité.
5.1.7 Services d'audit
Afin de pouvoir contrôler les accès à posteriori, il est nécessaire d'avoir un système qui
répertorie les connexions mais aussi les actions des différents utilisateurs sur les bases. Il est
aussi indispensable que ce système soit protégé contre les modifications, autres que celles
faites par le SGBD. Les SGBD libres ne supporte pas cette fonctionnalité.
5.2 Optimisation
5.2.1 Optimisation des requêtes
L'optimisation des requêtes et des temps de réponse du SGBD incombe à l'administrateur de
bases de données. Quatre vingt dix pour cent de l'optimisation de la base est faite pendant la
conception. Les dix pour cent restants se font pendant la production.
Comparaison des SGBD Libres.doc
39/49
CNAM 2005-2006
Le SGBD doit fournir à l'administrateur toutes les informations nécessaires à cette
optimisation :
¾ L'occupation mémoire
Afin d'avoir les performances maximales, il faut que le SGBD ne fonctionne qu'en
mémoire, il ne doit pas utiliser le fichier d'échange présent sur le disque. Les
informations de cette occupation mémoire doivent être détaillées par zones car le
SGBD utilise des zones mémoires différentes : une pour stocker les modifications de
données, une pour trier les tables lors de l'exécution des requêtes… L'affichage de ces
informations est nécessaire à l'administrateur, qui doit voir si une requête ne manque
pas de mémoire par exemple dans la zone de tri.
¾ Les choix effectués par le programme qui optimise les requêtes
Avant d'exécuter une requête, le SGBD choisit l'algorithme qu'il va utiliser pour les
jointures et détermine les index possibles… L'affichage de ces informations est
nécessaire à l'administrateur pour déterminer où se situe l'éventuel goulet
d'étranglement (un index manquant par exemple). L’administrateur de base de données
peut aussi décider de créer une table contenant une jointure précalculée. Cette solution
entraîne un gain de temps considérable dans le cas de tables souvent lues et peu mises
à jour. La mise à jour de cette table contenant une jointure est assurée par des triggers
présents sur la table concernée.
Tous les SGBD de ce comparatif supportent ces fonctionnalités. Il est à noter que l’évolution
de la complexité des SGBD, avec notamment les grappes (clusters) à répartition de charges
(Data Grid), rend de plus en plus difficile l’optimisation des serveurs. C’est pourquoi la
tendance naissante est d’introduire une optimisation calculée et décidée par le SGBD luimême.
5.2.2 Optimisation de l'utilisation des disques
Même si le fait de déplacer des tables fréquemment utilisées sur des systèmes de disques
différents améliore les performances, la principale préoccupation de l’administrateur de base
de données, au niveau des disques, est la gestion de l’espace restant en fonction de
l’augmentation de taille de la base.
Voyons maintenant comment les SGBD stockent les objets présents dans une base de données
ou comment se fait la liaison entre l’architecture logique (les bases, les tables) et l’architecture
physique (les fichiers).
Une solution simple est l’utilisation de fichiers plats. Chaque base ou chaque table se trouve
dans un fichier contenant les informations. C’est ainsi que fonctionne le système de fichier
MyIsam de MySQL. Un répertoire porte le nom de la base et chaque fichier contient les
données présentes dans la table. La base, dans le pire des cas (cas de MyIsam), ou une table
dans le meilleur des cas, doit absolument tenir sur un seul disque. Ce système ne possède
aucune souplesse de gestion.
Comparaison des SGBD Libres.doc
40/49
CNAM 2005-2006
Pour remédier aux défauts de la solution précédente, la plupart des SGBD gèrent des espaces
de stockages virtuels (tablespace). Un tablespace est, en fait, un ensemble de fichiers pouvant
se trouver sur des espaces de stockage physiques différents. Il est possible de rajouter
facilement un nouveau fichier à cet espace de stockage logique et donc d’augmenter la
possibilité de stockage de données. Une table peut être partagée sur plusieurs fichiers du
même espace de stockage virtuel (tablespace).
5.3 Gestion des sauvegardes / restaurations
Les sauvegardes des bases sont destinées à pouvoir effectuer une régénération des données
après un sinistre (corruption de base de données, problème système). Après l’installation
d’une base, un administrateur de bases de données doit mettre en place un plan de sauvegarde
en fonction du degré de tolérance de perte des données et de la durée d'indisponibilité
acceptable pour l’application.
Il existe plusieurs types de sauvegarde :
¾ La sauvegarde complète de la base de données : sauvegarde de tous les fichiers de la
base à un instant donné.
¾ La sauvegarde partielle de la base de données : sauvegarde d'une partie des fichiers de
la base à un instant donné.
¾ La sauvegarde des journaux de reprises : sauvegarde des fichiers log, à intervalles
réguliers.
Afin de mieux comprendre les sauvegardes, il est nécessaire de savoir que lorsqu’une
modification est effectuée sur une base de données, celle-ci est enregistrée à deux niveaux :
¾ Sur les données proprement dites, lorsque la modification est enregistrée dans les
fichiers de données.
¾ Dans les fichiers journaux de reprises où l’on recense séquentiellement les
modifications apportées à la base.
Il existe deux modes de sauvegarde / restauration :
¾ Base fermée dite "à froid".
La base étant arrêtée, cette sauvegarde est une simple copie de fichier.
¾ Base ouverte dite "à chaud"
La base de données continue à être utilisée. En mode restauration, les données sont
indisponibles le temps de la restauration et de la mise à niveau grâce aux journaux de
reprise.
Comparaison des SGBD Libres.doc
41/49
CNAM 2005-2006
La présence de fichiers journaux de reprises générés par le SGBD est nécessaire pour
effectuer une sauvegarde à chaud. Dans le cadre d'environnements à haute disponibilité, l'arrêt
de la base est impossible. Le problème qui se pose provient, alors, de la constante
modification des valeurs présentes. Pour effectuer une sauvegarde à chaud, l'espace de
stockage virtuel (tablespace) est passé en mode "Sauvegarde". Les modifications sont
mémorisées uniquement dans les fichiers journaux durant la sauvegarde de cet espace de
stockage virtuel. Ensuite, l'espace de stockage virtuel est mis à jour avec les fichiers journaux
avant d'être repassé en mode "Normal".
La planification des tâches est un système offrant la possibilité de lancer, avec une fréquence
déterminée, une tâche comme la sauvegarde des bases de données, la mise à jour des
statistiques nécessaires au programme d'optimisation des requêtes, etc. Elle automatise donc
la procédure. Les bases de données libres, fonctionnant sous forme de commandes saisies sur
la ligne de commande (Mysql, PostgreSQL), s’appuient sur le système (Planificateur de tache
sous Windows, cron sous linux) afin d'offrir cette possibilité.
5.4 Tableau de synthèse
Les différentes fonctionnalités exposées ci-dessus sont présentées dans le tableau de synthèse
suivant :
Le choix devient, cette fois, plus difficile.
Ingres et MaxDB ont l'avantage d'avoir une administration intégrée avec des interfaces
graphiques natives tandis que MySQL utilise des éditeurs tiers pour produire les logiciels
contenant des interfaces. PostgreSQL, de par ses modules en ligne de commande, offre la
possibilité d'écrire des scripts shell, ce qui le rend hautement configurable et automatisable.
Comparaison des SGBD Libres.doc
42/49
CNAM 2005-2006
Conclusion
Cette étude montre que les SGBD libres possèdent nombre d’atouts. Même s’ils n’occupent
pas encore la majorité des parts de marché, ils en sont la valeur montante. Le développement
rapide des ultimes fonctionnalités leurs faisant défaut, par rapport à leurs homologues
commerciaux, et leurs coûts imbattables en font des alternatives qui ne peuvent qu'être
sérieusement considérées.
Le nombre impressionnant de SGBD libres offre la possibilité de choisir celui qui convient le
mieux aux besoins. La couverture va de la petite base mono poste mono utilisateur à la base
géante contenant des milliards de lignes et nécessitant un cluster à répartition de charge pour
gérer des milliers de requêtes par seconde. Le respect de la norme SQL engendre un nombre
minimal d’adaptions à réaliser en cas de changement de SGBD. L’utilisation d’API
génériques évite l'adaptation des programmes en fonction de la base.
L’utilisation des procédures stockées offre l’optimisation du temps d’exécution des requêtes.
La facilitation des opérations de maintenance est présente avec les déclencheurs (triggers). La
souplesse et les possibilités rajoutées par le langage procédural SQL/PSM ouvrent de
nombreux horizons quant à l’utilisation des requêtes stockées et des déclencheurs.
L’administration de bases de données se compose des tâches suivantes :
¾ Gestion de la sécurité : droit d’accès et chiffrement des communications
¾ Optimisation du SGBD, des différentes zones mémoires, des requêtes par l’utilisation
des déclencheurs
¾ Gestion des sauvegardes
On peut se demander comment les SGBD commerciaux vont arriver à survivre à la déferlante
des SGBD libres. Demain, les SGBD libres auront les mêmes fonctionnalités que les SGBD
commerciaux. Ils possèdent, en outre, un avantage de taille : un prix imbattable. Les espaces
de stockage auront des tailles gigantesques qui pourront stocker le savoir de l’humanité dans
tous les domaines. L’accès se fera en langage naturel, il suffira de penser à une question pour
en avoir la réponse. Dans ces conditions, quel sera l’intérêt d’aller à l’école ? L’humanité
arrivera t-elle à encore progresser dans ses connaissances ?
Comparaison des SGBD Libres.doc
43/49
CNAM 2005-2006
Bibliographie
Carolyn Begg et Thomas Connolly, Système de base de données, Edition Eyrolles, 2005.
Christian Marée, Guy Ledant, SQL2 Initiation/Programmation, Editions Dunod, 1999.
Cyril Nocton, PHP4 et MySQL en ligne, Micro Application, 2001.
Georges Gardarin, Bases de données, Edition Eyrolles, 2003.
Pierre Risler, Manuel d’administration Oracle. 2005.
http://www.MySQL.com, Le site officiel du SGBD MySQL, MySQL AB, 1995-2006
http://www.sapdb.org, un site officiel du SGBD MaxDB, SAP AG. On peut aussi trouver ce
logiciel sur le site de MySQL.
http://www.firebirdsql.org, le site officiel du SGBD FireBird, Firebird Project, 2000-2006
http://www.ingres.com, le site officiel du SGBD Ingres, Ingres Corporation, 2006
http://www.postgresql.org, le site officiel du SGBD PosgreSQL, PostgreSQL Global
Development Group, 1996-2006
http://www.sleepycat.com/, le site officiel du SGBD Berkeley DB.
http://db.apache.org/, le site officiel du SGBD Derby, Apache Software Foundation, 20012006
http://hsqldb.org/, le site officiel du SGBD Hypersonic SQL, The hsqldb Development Group,
2001-2005
Comparaison des SGBD Libres.doc
44/49
CNAM 2005-2006
http://www.picosoft.it/picosql/, le site officiel du SGBD PicoSQL, 2006.
http://www.daffodildb.com/, le site officiel du SGBD Daffodil DB et de sa déclinaison open
source One$DB, Daffodil Software Ltd, 2004-2006
http://www.sqlite.org, le site officiel du SGBD SQLite, 2006
http://sqlpro.developpez.com, Site contenant diverses informations sur les bases de données,
Frédéric Brouard, 2003-2006
http://www.linux-france.org/article/cel/SICOMOR/SGBDR/html/Rapport_7-9V10.html,
Sécurisation des SGBDR, EADS Sycomore.
http://www.cs.duke.edu/~junyang/cps216/papers/codd-1970.pdf, Dr. E. F. Codd, A Relational
Model of Data for Large Shared Data Banks,
http://www.itl.nist.gov/fipspubs/fip127-2.htm Federal Information Processing Standards
Publication 127-2, Announcing the Standard for Database Language SQL
Comparaison des SGBD Libres.doc
45/49
CNAM 2005-2006
Glossaire
ANSI :
American National Standard Institute. Institut américain chargé de valider et publier
des normes.
Atomique :
Une transaction atomique (ensemble d’instructions) est un ensemble indivisible,
entièrement exécuté ou pas du tout.
Cache :
Technique de stockage en mémoire de valeurs lues ou à écrire sur le disque. En
groupant les accès disques, on augmente la vitesse globale d’échange avec le disque.
Clé étrangère :
Une clé étrangère est une clé primaire ou candidate dans une autre table.
Collation :
Définit la position ordinale de chaque caractère dans un jeu de caractère. Le même jeu
de caractère peut donc être trié différemment.
Commutateur :
Dispositif réseau reliant plusieurs machines sur un même câble et qui ne transmet les
informations qu’au bon destinataire (pas de broadcast).
Contrainte d’intégrité :
Une contrainte d'intégrité est une clause qui oblige que les données saisies dans la base
soient conformes aux données attendues (Not Null, Unique…)
Déclencheur :
Bout de programme sur le serveur qui est automatiquement invoqué lorsqu'un
enregistrement est inséré, mis à jour ou effacé. Il effectue de petites opérations tel que
des vérifications de données, des modifications...
Domaine :
Ensemble de valeurs que peut prendre un attribut.
Comparaison des SGBD Libres.doc
46/49
CNAM 2005-2006
Entrepôt de données :
Outil d'aide à la décision, basé sur une base de données fédérant et homogénéisant les
informations des différents services d'une organisation.
Fonctions de hachage à sens unique :
Fonction répondant aux critères suivants : Il est facile de calculer f(x) ayant x mais il
est quasi-impossible de calculer x en ayant f(x). Si x et x' sont différents alors f(x) est
différent de f(x').
Elles servent à assurer la détection d’une modification dans un document.
Grappe :
Cluster en Anglais. Ensemble de machines qui coopèrent, visibles de l’extérieur
comme un seul serveur extrêmement puissant.
Index :
Structure de données offrant la possibilité d'accéder de manière efficace à un tuple en
connaissant la valeur d'un de ses attributs. Un index améliore les performances d'accès
en interrogation mais pénalise les mises à jour et prend de la place sur le disque.
Intégrité référentielle :
Capacité d'un SGBD à imposer sur une table des règles de validation provenant d'une
autre table (clé étrangère).
International Standards Organization (ISO) :
Organisation non gouvernementale qui fédère des organismes nationaux et s'occupe
d'édicter des normes internationales.
Jointure externe :
La jointure consiste à combiner deux tables (ou plus) pour obtenir une table résultat.
En concaténant deux à deux les lignes des deux tables initiales (produit cartésien) et en
ne gardant (restriction) que les lignes dont deux colonnes (ou plus) dans les tables
initiales vérifient une certaine propriété, on obtient une jointure. Si on garde toutes les
lignes d’une table ou des deux, on parle de jointure externe.
Objet large :
Type de donnée stockant une information de grande taille.
Pare-feu :
Dispositif logiciel ou matériel protégeant un réseau en bloquant certaines
communications jugées dangereuses.
Comparaison des SGBD Libres.doc
47/49
CNAM 2005-2006
Point de sauvegarde :
Sous ensemble de transactions.
Procédures stockées :
Ensemble d’instructions présentes dans le SGBD et prêtes à être exécutées.
Relationnel objet :
Se dit d’une base qui supporte les objets.
Requête :
Ensemble d’instructions manipulant des ensembles de données.
Rôle :
Ensemble de droits sur un ensemble d’objet de la base.
Sauvegarde :
Copie de la base afin de pouvoir la restaurer en cas de problème grave.
Schéma :
Ensemble des objets (tables, index, déclencheurs…) propriété d'un utilisateur.
Sérialisation :
Exécution en parallèle d’un ensemble de transactions produisant le même résultat que
si elles étaient exécutées en série.
SGBD :
Système de Gestion de Base de Données. Programme gérant une base de données,
autrement dit un grand volume d’informations.
SQL :
Structured Query Langage. Langage utilisé dans les bases de données afin de
manipuler les données.
SSL :
Secure Socket Layer. Couche logicielle effectuant le chiffrement/déchiffrement d’une
communication quelconque.
Comparaison des SGBD Libres.doc
48/49
CNAM 2005-2006
Transaction :
Ensemble de requêtes. Une transaction simple est executé sur un serveur. Les
transactions distribuées peuvent être exécutées sur plusieurs serveurs.
Tuple :
Un tuple est une ligne structurée comme un ensemble de valeurs.
Verrou :
Mécanisme gérant la simultanéité d’accès afin d’éviter des incohérences (deux
écritures en même temps).
Vue :
Du point de vue de l’utilisateur, c’est comme une table. Du point de vue de la base,
c’est le résultat d’une requête. Les vues simplifient la structure d’une base et
restreignent l’accès à des données.
XML :
eXtensible Markup Language. C’est un langage de balise extensible dont la forme
générale est <balise>texte</balise>.
XQuery :
Langage offrant la possibilité de retrouver des informations dans un document XML.
Comparaison des SGBD Libres.doc
49/49
CNAM 2005-2006