Intéressé par des cours d'informatique en ligne ?
Visitez mon nouveau site
https://www.yesik.it !
Tables virtuelles — Dans l'approche traditionnelle, pour manipuler des données externes dans une base, il est nécessaire de passer par une phase d'import. L'utilisateur qui fait des requêtes travaille donc sur des données qui ne sont pas synchronisées avec la réalité.
Avec les tables virtuelles les données seront récupérées à la demande lors des requêtes.
Apache Derby permet de créer des tables virtuelles. C'est à dire des tables dont le contenu n'est pas stocké par Derby, mais dynamiquement généré par du code Java.
Concrètement, il s'agit d'écrire une méthode Java publique statique renvoyant une instance de ResultSet. Si le principe n'est pas compliqué en soi, la mise en oeuvre à partir de zéro est un peu fastidieuse, ne serait-ce que par le nombre de méthodes à coder pour satisfaire l'interface ResultSet de Java.
Ici, pour présenter l'écriture de tables virtuelles pour Derby, je vais donc utiliser les classes utilitaires fournies par Rick Hillegas de Sun[1] (Vous trouverez un lien pour les télécharger à la fin de cet article).
Sommaire
Tables virtuelles en 4 étapes
La création d'une table virtuelle avec Derby peut se diviser en 4 étapes:
- Mise en oeuvre
- Dans cette étape, vous codez la table virtuelle sous la forme d'une classe Java qui met en oeuvre l'interface ResultSet. Accessoirement, cette étape peut être omise si vous avez la possibilité d'obtenir un ResultSet représentant vos données par un autre moyen (par exemple, via un appel JDBC). Nous ne nous intéresserons pas à cette dernière possibilité ici.
- Publication
- Il s'agit ensuite d'écrire une méthode publique statique qui permettra à Derby d'instancier la classe définie à l'étape précédente. C'est cette méthode qui servira d'interface avec Derby.
- Déclaration
- Et nous voici arrivé côté Derby. Cette étape consiste à déclarer une fonction SQL appelant la méthode Java définie à l'étape précédente.
- Invocation
- L'étape finale, qui consiste à appeler la fonction dans une requête SQL.
Mise en oeuvre
Cette étape est en quelque sorte la plus importante, puisque c'est là que vous allez coder la logique spécifique à votre application. Son rôle est de générer les données de la table virtuelle.
Pour pouvoir être utilisée comme une table, ces données doivent être présentées sous la forme d'un objet qui met en oeuvre l'interface ResultSet. Plutôt que de tout coder depuis zéro, nous allons coder dans cet exemple une classe dérivée de sun.javadb.vti.core.EnumeratorTableFunction. Celle-ci étant spécifiquement conçue pour permettre d'accéder à une collection d'objets comme à une table:
package fr.chicoree.derby; import java.sql.ResultSet; import java.sql.SQLException; import sun.javadb.vti.core.EnumeratorTableFunction; class CountryEnumerator extends EnumeratorTableFunction { static final String[] columns = { "Digram", "Country" }; public CountryEnumerator() throws SQLException { super(columns); final String[][] collection = { { "fr", "France" }, { "uk", "United Kingdom" }, { "us", "United States" }, { "de", "Deutschland" } }; setEnumeration(collection); } @Override public String[] makeRow(Object obj) throws SQLException { return (String[])obj; } }
Une grande partie du travail se passe dans le constructeur de CountryEnumerator. C'est là qu'est transmis à EnumeratorTableFunction le nom des colonnes, et la collection de données. Chaque élément de cette collection deviendra une ligne de la table. Et justement, comme son nom l'indique, c'est le rôle de makeRow de fabriquer une ligne à partir d'un élément de la collection.
Dans cet exemple, puisque chaque élément de la collection est déjà sous la forme d'un tableau de 2 chaînes de caractères, le code de makeRow est réduit au strict minimum. Nous examinerons cette méthode plus en détail sur un autre exemple à la fin de cet article .
Publication
Avec cette seconde étape nous allons terminer le travail côté Java. Il s'agit ici de coder une méthode publique statique qui permettra à Derby d'instancier notre table virtuelle. Généralement cette fonction se limite à un wrapper autour de l'appel à l'opérateur new:
public class TableFunctions { public static ResultSet countryEnumerator() throws SQLException { return new CountryEnumerator(); } }
Remarque:
Rien n'oblige la fonction qui sert à publier la table virtuelle à faire partie de la classe qui en fait la mise en oeuvre. Ici j'ai fait le choix de la définir dans une classe séparée car je trouve cela plus propre. Mais le choix inverse aurait été tout aussi valable.
Déclaration
Nous voici maintenant côté Derby. Et là, il va falloir importer notre table virtuelle sous la forme d'une fonction SQL:
ij> CREATE FUNCTION CountryEnumerator()
RETURNS TABLE( -- La fonction renvoie une table de deux colonnes
digram CHAR(2),
country VARCHAR(20)
)
LANGUAGE JAVA -- La fonction est écrite en Java
PARAMETER STYLE DERBY_JDBC_RESULT_SET -- et retourne son résultat sous la forme d'un ResultSet
NO SQL -- La fonction ne fait aucune requête à la base
EXTERNAL NAME 'fr.chicoree.derby.TableFunctions.countryEnumerator';
Attention:
A partir de maintenant, il est impératif d'avoir compilé le code Java de votre table virtuelle, de l'avoir packagé dans un JAR, et enfin que ce JAR soit dans le CLASSPATH de Derby.
Invocation
Une fois ceci fait, il devient enfin possible d'utiliser cette fonction comme une table dans une requête SQL:
ij> SELECT * FROM TABLE ( CountryEnumerator() ) AS T;
DIGRAM | COUNTRY |
fr | France |
uk | United Kingdom |
us | United States |
de | Deutschland |
4 rows selected
Un exemple plus complexe
Pour ce second exemple, vous allons mettre en oeuvre une table virtuelle qui renvoie la liste des fichiers d'un dossier donné. Cette fonction sera un peu plus complexe que notre premier exemple, pour deux raisons:
- Tout d'abord, elle va recevoir un argument: le nom de dossier dont on veut obtenir le contenu;
- Ensuite, le contenu des colonnes sera extrait d'un objet (ce qui nous permettra de mieux comprendre le fonctionnement de makeRow).
class FileEnumerator extends EnumeratorTableFunction { static final String[] columns = { "name", "size", "directory", "hidden" }; public FileEnumerator(String directory) throws SQLException { super(columns); File dir = new File(directory); if (!dir.exists()) throw new SQLException(new FileNotFoundException(directory)); setEnumeration(dir.listFiles()); } @Override public String[] makeRow(Object obj) throws SQLException { File file = (File)obj; return new String[] { file.getName(), Long.toString(file.length()), Boolean.toString(file.isDirectory()), Boolean.toString(file.isHidden()) }; } } public class TableFunctions { /* ... */ public static ResultSet fileEnumerator(String directory) throws SQLException { return new FileEnumerator(directory); } }
Plusieurs points sont à noter dans le code ci-dessus:
- Tout d'abord, constatez que le constructeur reçoit en argument le dossier dont on souhaite récupérer le contenu. Cet argument est en fait transmis via la méthode publique statique TableFunction.fileEnumerator qui sert de point d'entrée pour Derby
- Ensuite, remarquez que makeRow renvoie un tableau de chaînes de caractères (String[]). D'où les divers appels à toString. C'est la mise en oeuvre de la classe EnumeratorTableFunction qui veut cela.
Regardons maintenant comment déclarer cette fonction dans Derby:
CREATE FUNCTION FileEnumerator(VARCHAR(80)) RETURNS TABLE(name VARCHAR(40), size INT, directory CHAR(5), hidden CHAR(5)) LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET NO SQL EXTERNAL NAME 'fr.chicoree.derby.TableFunctions.fileEnumerator';
ij> SELECT * FROM TABLE ( FileEnumerator('/home/sonia') ) AS T;
NAME |SIZE |DIRE&|HIDD&
----------------------------------------------------------------
.bash_logout |220 |false|true
.psql_history |13 |false|true
.profile |675 |false|true
.bashrc |3116 |false|true
mbox |1218 |false|false
.bash_history |49 |false|true
6 rows selected
Type et conversions
Derby ne supporte pas le type BOOLEAN. C'est pourquoi j'utilise ici le type CHAR(5) pour les colonnes hidden et directory. Afin de récupérer ces valeurs sous leur représentation textuelle true ou false.
A l'inverse, la colonne size est déclarée avec le type INT. Par conséquent Derby accédera à cette valeur via un appel à ResultSet.getInt. Qui est codée dans StringColumnTableFunction pour faire l'opération inverse de Integer.toString. Le surcoût lié à la conversion des données en chaînes de caractères, puis à la conversion inverse est sans doute négligeable dans la plupart des cas. Si cela ne l'était pas, ou si cet aller-retour entraînait pour une raison ou une autre une perte d'information vous n'auriez guère d'autre solution que de vous passer de StringColumnTableFunction pour écrire votre propre mise en oeuvre...
Usage
A titre d'illustration, voici quelques exemples de requêtes SELECT utilisant notre table virtuelle. Vous constaterez qu'il nous est possible d'utiliser l'éventail des possibilités de SQL fournies par Derby pour manipuler ces données exactement comme s'il s'agissait de tables ordinaires:
-- Extraire les .png SELECT name FROM TABLE ( FileEnumerator('/home/sylvain/images') ) AS T WHERE T.name LIKE '%.png'
-- Espace total occupé par les images .png et .jpeg SELECT SUM(size) FROM TABLE ( FileEnumerator('/home/sylvain/images') ) AS T WHERE T.name LIKE '%.png' OR T.name LIKE '%.jpeg'
-- Extraire les fichiers présents dans un dossier mais pas dans un autre SELECT T1.name FROM TABLE ( FileEnumerator('/home/sylvain/master') ) AS T1 LEFT JOIN TABLE ( FileEnumerator('/home/sylvain/copie') ) AS T2 ON T1.name = T2.name WHERE T2.name IS NULL
A quoi ça sert?
L'usage des fonctions virtuelles est très vaste: puisqu'elles peuvent tout aussi bien servir pour accéder à partir de Derby à des données live, à importer des données d'un fichier au format plus ou moins exotique, ou encore à interfacer Derby avec une autre application – ou un autre SGBD (Système de Gestion de Bases de Données) . Tout en permettant d'utiliser la machinerie SQL de Derby pour manipuler et/ou analyser ces données, et cela sans qu'il soit nécessaire de les stocker dans la base.
La seule limite est votre créativité!
Ressources
- sampleTableFunctions.zip – Java DB Table Functions, classes et documentation (note: JavaDB est le nom commercial pour Apache Derby chez Sun).
