Looking for Computer Science & Information Technology online
courses ?
Check my new web site: https://www.yesik.it !
Apache Derby est une base de données relationnelle open-source entièrement développée en Java par la fondation Apache.
Derby a la particularité de pouvoir être utilisé comme gestionnaire de base de données embarqué dans une application Java. Ce qui rend inutile l'installation et la maintenance d'un serveur de base de données autonome. A l'inverse Derby supporte aussi un mode de fonctionnement client-serveur.
Dans cet article, nous allons utiliser Derby en mode embarqué pour créer notre première base de données – et nous servir de l'utilitaire ij fourni avec Derby pour effectuer quelques requêtes SQL histoire de nous familiariser avec cet outil.
Installer Apache Derby
Téléchargement et vérification
Avant de pouvoir utiliser Derby il faut l'installer. Ce qui veut dire d'abord le télécharger. Sur la page http://db.apache.org/derby/derby_downloads.html j'ai choisi la plus récente version stable: derby-10.5.1.1. Derby est disponible en plusieurs distributions. Pour ma part, j'ai pris la distribution bin qui contient Derby compilé et sa documentation.
Comme j'ai téléchargé le fichier db-derby-10.5.1.1-bin.zip à partir d'un site miroir avant d'aller plus loin j'ai vérifié l'intégrité de mon fichier avec md5sum ainsi que la signature du fichier:
# Vérifier l'intégrité du fichier sh$ ls db-derby-10.5.1.1-bin.zip sh$ echo '6A7CEFF129A4E2FDF36D113BA001D149 db-derby-10.5.1.1-bin.zip' | md5sum --check - db-derby-10.5.1.1-bin.zip: OK sh$ wget http://www.apache.org/dist/db/derby/db-derby-10.5.1.1/db-derby-10.5.1.1-bin.zip.asc # Vérifie l'origine du fichier sh$ wget http://svn.apache.org/repos/asf/db/derby/code/trunk/KEYS sh$ gpg --import KEYS sh$ gpg --verify db-derby-10.5.1.1-bin.zip.asc gpg: Signature made Tue 14 Apr 2009 11:27:12 PM CEST using DSA key ID 37AA956A gpg: checking the trustdb gpg: no ultimately trusted keys found gpg: Good signature from "Myrna van Lunteren <m.v.lunteren@gmail.com>" gpg: WARNING: This key is not certified with a trusted signature! gpg: There is no indication that the signature belongs to the owner. Primary key fingerprint: 66C3 0B69 5415 91E3 A777 F84D 0E13 F75A 37AA 956A
Ok: le fichier est intègre. Et il apparaît comme étant signé par un développeur du projet Apache. Je dis bien apparaît car comme vous le voyez dans le message d'avertissement, le fichier est bien signé par "Myrna van Lunteren" mais rien ne me prouve que la signature appartient bien au propriétaire indiqué. Dans le cadre de cette démonstration le risque est faible. Ca me suffira donc pour faire confiance au fichier téléchargé...
Bref, il est temps de décompresser l'archive:
sh$ unzip -q db-derby-10.5.1.1-bin.zip sh$ ls db-derby-10.5.1.1-bin db-derby-10.5.1.1-bin.zip.asc db-derby-10.5.1.1-bin.zip KEYS sh$ ls db-derby-10.5.1.1-bin bin docs javadoc lib NOTICE test demo index.html KEYS LICENSE RELEASE-NOTES.html
Et voilà, Derby est installé. Notez que le dossier db-derby-10.5.1.1-bin/docs contient l'ensemble de la documentation de Derby. N'hésitez pas à vous y reporter si nécessaire.
Définir les variables d'environnement
Maintenant Derby est installé (dans votre répertoire courant). Néanmoins avant de pouvoir nous en servir un (tout) petit peu de configuration est nécessaire. En particulier, comme Derby peut être installé n'importe où dans l'arborescence de votre disque, il est nécessaire de lui dire où se trouvent ses fichiers. Pour ce faire, vous devez définir la variable d'environnement DERBY_HOME pour désigner le dossier que nous venons d'extraire:
sh$ export DERBY_HOME=`pwd`/db-derby-10.5.1.1-bin
Seule la définition de DERBY_HOME est absolument obligatoire. Néanmoins pour faciliter l'utilisation des outils livrés avec Derby, il est conseillé de rajouter le sous-répertoire bin à votre PATH (pas au CLASSPATH!)
sh$ export PATH="${PATH}:${DERBY_HOME}/bin"
Pour tester votre installation vous pouvez demander à Derby d'afficher les informations sur votre système:
sh$ java -jar ${DERBY_HOME}/lib/derbyrun.jar sysinfo
------------------ Java Information ------------------
Java Version: 1.6.0_07
Java Vendor: Sun Microsystems Inc.
Java home: /usr/lib/jvm/java-6-sun-1.6.0.07/jre
Java classpath: /home/sylvain/Developpement/derby/db-derby-10.5.1.1-bin/lib/derbyrun.jar
OS name: Linux
OS architecture: i386
OS version: 2.6.24-mobal.chicoree.fr
Java user name: sylvain
Java user home: /home/sylvain
Java user dir: /home/sylvain/Developpement/derby
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[/home/sylvain/Developpement/derby/db-derby-10.5.1.1-bin/lib/derby.jar] 10.5.1.1 - (764942)
[/home/sylvain/Developpement/derby/db-derby-10.5.1.1-bin/lib/derbytools.jar] 10.5.1.1 - (764942)
[/home/sylvain/Developpement/derby/db-derby-10.5.1.1-bin/lib/derbynet.jar] 10.5.1.1 - (764942)
[/home/sylvain/Developpement/derby/db-derby-10.5.1.1-bin/lib/derbyclient.jar] 10.5.1.1 - (764942)
Si vous obtenez ces informations (et sans doute plus) c'est que Derby est convenablement installé. Dans le cas contraire vérifiez DERBY_HOME. Assurez-vous aussi que le JDK est correctement installé. J'ai testé avec un JDK Sun 6. Mais Derby (au moins dans la version que j'ai installée) devrait pouvoir fonctionner avec tout JDK à partir du 1.4.
Travailler avec Derby
Pour ce tutoriel notre base va rester très simple: Une table avec des classes d'animaux (reptiles, oiseaux, etc.). Et une table avec des espèces. Chaque espèce appartenant à une classe – ce qui constituera une association N-à-1 entre les deux tables.
Ma première base
En standard, Derby est livré avec l'utilitaire ij. Il s'agit d'une interface en mode texte qui permet de se connecter à une base Derby et d'exécuter des commandes SQL:
sh$ java -jar ${DERBY_HOME}/lib/derbyrun.jar ij
ij version 10.5
ij>
Note:
Si vous avez ajouté ${DERBY_HOME}/bin à votre PATH vous pouvez invoquez plus simplement ij:
sh$ ij ij version 10.5 ij>
La première chose à faire est de créer la base. Derby n'a pas de commande SQL CREATE DATABASE pour explicitement créer une nouvelle base. Par contre, on peut demander à Derby de créer une base au moment de la connexion:
ij> CONNECT 'jdbc:derby:taxondb;create=true';
ij> SHOW CONNECTIONS;
CONNECTION0* - jdbc:derby:taxondb
* = current connection
Voilà: la base taxondb a été créé et nous somme connecté dessus, comme le prouve le résultat de la commande SHOW CONNECTIONS.
Quittez maintenant ij (vous pouvez le faire en tapant la commade EXIT – ou plus simplement ctrl-d ou ctrl-c.
ij> EXIT; sh$
Puisque nous sommes revenus dans le shell, nous allons en profiter pour voir que Derby a créé un répertoire taxondb dans le répertoire courant. C'est là que Derby stockera l'ensemble des données et méta-informations de la base.
sh$ ls
db-derby-10.5.1.1-bin derby.log taxondb
db-derby-10.5.1.1-bin.zip
Remarque:
Si aucune instance de Derby n'est connectée à la base de données, il est possible d'en faire un backup simplement en copiant ce dossier (offline backup):
sh$ cp -R taxondb /path/to/backup/
Par contre, cette technique est fortement déconseillée quand la base est en fonctionnement. Dans ce cas, il est préférable d'utiliser une des techniques décrites dans le guide de l'administrateur de Derby pour faire une sauvegarde en-ligne (online backup). Les détails se trouvent dans le dossier ${DERBY_HOME}/docs/html/adminguide/.
Avant d'aller plus loin, nous allons faire un petit détour par le système de sécurité de Derby. Celui-ci est assez complexe. Nous n'allons pas l'étudier en détail, mais juste y jeter un oeil, histoire d'avoir un premier contact avec certains concepts que vous retrouverez dans la documentation officielle.
Authentifier l'utilisateur
Nous allons donc pour cet article nous contenter de limiter l'accès à la base de données aux utilisateurs connus fournissant le bon mot de passe. Dans le répertoire courant, créez le fichier derby.properties contenant le texte suivant:
derby.connection.requireAuthentication=true derby.authentication.provider=BUILTIN derby.user.sylvain=my-password
- La première propriété (derby.connection.requireAuthentication) indique à Derby que dorénavant il ne doit plus accepter les connexions anonymes;
- derby.authentication.provider=BUILTIN précise que l'authentification repose sur les fonctionnalités intégrées à Derby. De façon plus claire, cela veut dire que les identifiants et mots de passe des utilisateurs seront donnés dans le fichier derby.properties lui-même;
- la (ou les) ligne(s) de la forme derby.user.USERNAME=PASSWORD définissent les utilisateurs et leur mot de passe. Ici, un seul utilisateur est défini: sylvain et son mot de passe est fixé à my-pasword.
Vérifions un peu ce qui se passe si nous essayons maintenant de nous reconnecter à notre base à partir de ij:
sh$ ij ij version 10.5 ij> CONNECT 'jdbc:derby:taxondb'; ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
Bien, l'accès anonyme est maintenant refusé.
ij> CONNECT 'jdbc:derby:taxondb;user=sylvain'; ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication.. ij> CONNECT 'jdbc:derby:taxondb;user=sylvain;password=my-password'; ij>
Comme vous le voyez, la connexion n'est autorisée qu'avec le nom d'utilisateur et le mot de passe adéquat.
Un mot sur la sécurité
Maintenant que nous sommes connectés, nous allons pouvoir commencer à créer nos tables. Mais avant, un avertissement cependant: la sécurité que nous venons de mettre en place est très facile à contourner. Et de bien des manières. Par exemple:
- le fichier derby.properties doit être lisible par Derby – donc par l'utilisateur sous l'identité duquel tourne le programme utilisant Derby. Par conséquent, cet utilisateur peut lire le fichier de propriétés et voir les mots de passe des autres utilisateurs.
-
Il est aussi possible de se connecter à partir d'un autre répertoire:
sh$ cd /tmp sh$ ij ij version 10.5 ij> CONNECT 'jdbc:derby:/path/to/taxondb'; ij> # Connecté! -
Dans le même ordre d'idée, un utilisateur doté de suffisamment de privilèges sur le dossier contenant le fichier derby.properties peut changer les permissions pour rendre le fichier illisible par Derby (ou même supprimer le fichier). Dans les deux cas, Derby se comporte comme si le fichier était absent, donc en autorisant les accès anonymes;
sh$ chmod -r derby.properties sh$ ij ij version 10.5 ij> CONNECT 'jdbc:derby:taxondb'; ij> # Connecté!
Comme vous le voyez, ce genre de sécurité doit être confiné dans un environnement parfaitement contrôlé. Par exemple où le logiciel utilisant la base de données est le seul à avoir les permissions sur la base et le fichier derby.properties. Une alternative est de stocker les autorisations dans la base elle-même et d'encrypter celle-ci. Mais pour cette introduction nous en resterons sur les considérations élémentaires vues plus haut.
Créer une table
Contrairement à ce que nous venons de voir pour la base elle-même, la création des tables se fait de façon plus conventionnelle. A l'aide d'une requête SQL. Commençons par la table représentant les classes d'animaux:
ij> CREATE TABLE Classe ( > Id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > Designation VARCHAR(30) NOT NULL UNIQUE); 0 rows inserted/updated/deleted
Cette première table contient deux colonnes:
- Id qui contient un identifiant unique pour l'espèce. Cet identifiant sera un numéro séquentiel généré automatiquement par Derby (GENERATED ALWAYS AS IDENTITY). Il nous servira de clé primaire (PRIMARY KEY).
- Designation qui contiendra la désignation de la classe d'animaux (reptile, oiseau, etc.). Il ne peut pas y avoir deux classes ayant la même désignation (UNIQUE). Toute classe doit avoir une désignation (NOT NULL).
Dès maintenant il est possible de peupler la table des classes d'animaux:
ij> INSERT INTO Classe(Designation) VALUES
> ('oiseaux'),
> ('mammifères'),
> ('reptiles');
3 rows inserted/updated/deleted
Piège:
Avec Derby, le délimiteur pour les valeurs chaînes de caractères est l'apostrophe. Vous ne pouvez pas utiliser de guillemets à la place. Ceux-ci sont réservés pour mettre autour des noms de champs:
ij> INSERT INTO Classe(Designation) VALUES ("bivalves"); ERROR 42X04: Column 'bivalves' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'bivalves' is not a column in the target table.
Par ailleurs, Derby convertit automatiquement en MAJUSCULES les identifiants sauf s'ils sont délimités par les guillemets:
ij> INSERT INTO Classe("Designation") VALUES ('bivalves'); ERROR 42X14: 'Designation' is not a column in table or VTI 'APP.CLASSE'.
L'exemple ci-dessus provoque une erreur, car lors de la création de la table, nous n'avons pas utilisé de guillemets autour du champ Designation. Cet identifiant est donc connu par Derby sous sa forme majuscule. Ce qui explique pourquoi l'exemple ci-dessous, lui, est accepté:
ij> INSERT INTO Classe("DESIGNATION") VALUES ('bivalves');
1 row inserted/updated/deleted
Bien sûr, vous pouvez examiner le contenu de la table à l'aide d'une requête SELECT:
ij> SELECT * FROM Classe; ID |DESIGNATION ------------------------------------------ 1 |oiseaux 2 |mammifères 3 |reptiles 4 |bivalves 4 rows selected
Ma première relation
Le sous-ensemble de SQL-92 supporté par Derby contient la définition des clés étrangères. Nous allons donc pouvoir créer la table Espèce en précisant de manière explicite sa relation avec la table Classe:
ij> CREATE TABLE Espèce ( > Id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > Nom VARCHAR(50) NOT NULL UNIQUE, > Classe INT REFERENCES Classe(Id)); 0 rows inserted/updated/deleted
Comme vous le voyez, le champ Espèce.Classe référence le champ Classe.Id. Autrement dit, cette contrainte m'empèche d'insérer une espèce donc la classe n'est pas présente dans la table correspondante:
ij> INSERT INTO Espèce(Nom, Classe) VALUES ('Mygale de Leblond', 6);
ERROR 23503: INSERT on table 'ESPÈCE' caused a violation of foreign key
constraint 'SQL090509011909552' for key (6). The statement has been rolled
back.
Il n'y a pas d'enregistrement dans la table Classe dont Id vaut 6, donc l'insertion ci-dessus est refusée par Derby.
Par contre, les insertions valides fonctionnent (heureusement!):
ij> INSERT INTO Espèce (Nom, Classe) VALUES
> ('Pie bavarde', 1),
> ('Merle noir', 1),
> ('Coucou gris', 1),
> ('Mulot sylvestre', 2),
> ('Lapin européen', 2),
> ('Tortue des bois', 3),
> ('Lézard des murailles', 3),
> ('Hirondelle rustique', 1),
> ('Crocodile du Nil', 1);
9 rows inserted/updated/deleted
Jointures
Derby supporte aussi les jointures. Par exemple, pour extraire l'ensemble des oiseaux connus dans la base de données, je peux utiliser la requête suivante:
ij> SELECT Classe.Designation, Espèce.Nom > FROM Classe > INNER JOIN Espèce ON Classe.Id = Espèce.Classe > WHERE Classe.Designation = 'Oiseaux'; DESIGNATION |NOM --------------------------------------------------------------------------------- 0 rows selected
Ah tiens? Ca ne marche pas? Le problème ici est que j'ai précisé dans ma clause WHERE Oiseaux avec un O majuscule. Or j'avais mis une minuscule lors de la saisie des données. Comme Derby fait une comparaison sensible à la casse, aucune donnée ne concorde. Ré-écrivons la requête avec oiseaux en minuscules:
ij> SELECT Classe.Designation, Espèce.Nom > FROM Classe > INNER JOIN Espèce ON Classe.Id = Espèce.Classe > WHERE Classe.Designation = 'oiseaux'; DESIGNATION |NOM --------------------------------------------------------------------------------- oiseaux |Pie bavarde oiseaux |Merle noir oiseaux |Coucou gris oiseaux |Hirondelle rustique oiseaux |Crocodile du Nil 5 rows selected
Mieux. Mais que fait ce crocodile ici? J'ai du faire une petite erreur lors de la saisie. Nous allons corriger ceci dès la prochaine section.
Sous-requêtes
ij> SELECT * FROM Espèce WHERE Nom LIKE 'Croco%'; ID |NOM |CLASSE -------------------------------------------------------------------------- 10 |Crocodile du Nil |1
Et oui: les reptiles, ça n'est pas la classe 1, c'est la classe... euh... combien déjà? Inutile de faire appel à votre mémoire, puisque Derby supporte les requête imbriquées:
ij> UPDATE Espèce > SET Classe = (SELECT Id FROM Classe WHERE Designation = 'reptiles') > WHERE Nom LIKE 'Croco%'; 1 row inserted/updated/deleted
ij> SELECT Espèce.* FROM Espèce INNER JOIN Classe ON Espèce.Classe = Classe.Id > WHERE Classe.Designation = 'reptiles'; ID |NOM |CLASSE -------------------------------------------------------------------------- 7 |Tortue des bois |3 8 |Lézard des murailles |3 10 |Crocodile du Nil |3 3 rows selected
Bon nous avons vu les requêtes SELECT, INSERT et UPDATE. Juste par acquis de conscience essayons de faire un DELETE. Mais vous vous doutez bien que c'est une opération supportée par Derby.
ij> DELETE FROM Classe WHERE Designation = 'mammifères'; ERROR 23503: DELETE on table 'CLASSE' caused a violation of foreign key constraint 'SQL090509011909552' for key (2). The statement has been rolled back.
Forcément, les contraintes d'intégrité référentielle nous empêchent de supprimer la classe de mammifères tant qu'il y a des espèces qui y font référence. Je dois donc supprimer d'abord les espèces de mammifères avant de supprimer cette classe. Ici aussi, comme la requête DELETE supporte les sous-requêtes, cela nous évite d'avoir à nous souvenir de l'Id des mammifères:
ij> DELETE FROM Espèce > WHERE Classe = (SELECT Id FROM Classe WHERE Designation = 'mammifères'); 2 rows inserted/updated/deleted ij> DELETE FROM Classe WHERE Designation = 'mammifères'; 1 row inserted/updated/deleted
Accès concurents
Voilà notre rapide tour d'horizon de Derby en mode "client texte" est fini. Rien de bien notable. Comme tous les SGBD-R, Derby supporte son propre sous-ensemble de SQL. Ceci dit, les requêtes de bases sont supportées sans problème.
Avant de terminer, un point est à bien comprendre. A un instant donné, une base Derby ne peut être utilisée que par une seule instance de Derby. Autrement dit, un seul programme peut être connecté simultanément à une base Derby.
Faites l'expérience de lancer ij dans deux shells différents en vous connectant à la même base. La seconde connexion devrait vous donner le message d'erreur suivant:
ij> CONNECT 'jdbc:derby:taxondb;user=sylvain;password=my-password'; ERROR XJ040: Failed to start database 'taxondb', see the next exception for details. ERROR XSDB6: Another instance of Derby may have already booted the database /home/sylvain/Developpement/derby/taxondb.
En mode embarqué, une base Derby ne peut donc pas être partagée par plusieurs applications. Si vous avez besoin d'avoir plusieurs applications qui accèdent de manière concurrente à une base Derby, il vous faudra utiliser le serveur livré avec la distribution de Derby.
Ressources
- http://db.apache.org/derby/ - le site officiel du projet Apache Derby
