1 - Pré-requis
Il vous faut installer H2 à partir de l’un des deux liens présents sur la page d’accueil du site de h2.
Il faut également que le compilateur java soit accessible à partir de la machine virtuelle utilisée par H2. Pour cela, il vous faut avoir installé le JDK, et le fichier tools.jar doit être présent dans le classpath d’H2.
Voir aussi les fonctions personnalisées dans le manuel d’H2
Exemple de fichier bat pour lancer la console H2 avec le fichier tools.jar dans le classpath :
@SET CLASSPATH=%CLASSPATH%;C:\Program Files (x86)\Java\jdk1.6.0_17\lib\tools.jar
@"C:\Program Files (x86)\Java\jdk1.6.0_17\bin\javaw.exe" -Xmx256M -cp "h2-1.2.134.jar;%CLASSPATH%" org.h2.tools.Console %*
@if errorlevel 1 pause2 - Les fonctions personnalisées dans H2
Les fonctions personnalisées peuvent être écrites et compilées dans votre environnement de programmation favori et rendues accessibles en mettant votre librairie dans le classpath et en créant des alias sur vos fonctions. Voir le manuel d’H2
Nous nous intéresserons ici aux fonctions écrites "en ligne", c’est à dire dans la console H2.
Une fonction écrite en ligne commence par l’instruction SQL :
CREATE ALIAS NOM_FONCTION AS $$
// Ici prend place le corps de la fonction
$$;On entoure le corps de la fonction de $$ pour éviter d’avoir à préfixer tous les caractères réservés du langage SQL dans le corps de la fonction.
Le corps de la fonction s’écrit comme en java. Je me contente de reprendre les exemples du manuel.
Example 1 (fonction simple)
CREATE ALIAS NEXT_PRIME AS $$
String nextPrime(String value) {
return new BigInteger(value).nextProbablePrime().toString();
}
$$;Example 2 (fonction nécessitant d’importer des classes - la fonction est alors séparée de la section imports par l’instruction @CODE)
CREATE ALIAS IP_ADDRESS AS $$
import java.net.*;
@CODE
String ipAddress(String host) throws Exception {
return InetAddress.getByName(host).getHostAddress();
}
$$;Exemple 3 (fonction utilisant la connexion courante)
CREATE ALIAS QUERY AS $$
ResultSet query(Connection conn, String sql) throws SQLException {
return conn.createStatement().executeQuery(sql);
}3 - Créer une fonction pour visualiser les différences entre deux tables de même structure
Dans cette partie, nous allons réaliser un exercice en vraie grandeur. Il s’agit de créer une fonction DIFF qui nous permettra de produire les différences entre deux versions du fichier des communes mis à disposition par l’INSEE.
3.1 - Récupération des fichiers
Décompresser les fichiers et les placer dans un répertoire INSEE
3.2 - Chargement des fichiers dans H2 Le chargement des fichiers dans H2 utilise la commande CSVREAD (voir aussi ce tutoriel).
DROP TABLE IF EXISTS INSEE2009;
DROP TABLE IF EXISTS INSEE2010;
CREATE TABLE INSEE2009 AS SELECT DEP||COM AS INSEE, * FROM CSVREAD('C:\INSEE\comsimp2009.txt', NULL, NULL, CHAR(9));
CREATE TABLE INSEE2010 AS SELECT DEP||COM AS INSEE, * FROM CSVREAD('C:\INSEE\comsimp2010.txt', NULL, NULL, CHAR(9));
ALTER TABLE INSEE2009 ADD CONSTRAINT UNIQUE_INSEE2009 UNIQUE(INSEE);
ALTER TABLE INSEE2010 ADD CONSTRAINT UNIQUE_INSEE2010 UNIQUE(INSEE);Remarquez que je profite du chargement des fichiers pour créer un nouveau champ "INSEE" qui est l’identifiant des communes sur 5 caractères et qui n’est pas présent en tant que tel dans le fichier txt d’origine.
Remarquez aussi le CHAR(9) qui est un moyen pratique d’indiquer que c’est la tabulation qui sert de séparateur de champs dans le fichier que l’on importe.
Bien penser enfin à indexer le champ qui nous servira d’identifiant, car ce champ est utiliser pour faire des jointures qui prendraient beaucoup de temps si ce champ n’était pas indexé.
3.3 - Ecrire une fonction qui permettra de comparer les deux tables La fonction ci-dessous se sert de la connexion à la base en cours d’utilisation et crée un ResultSet composé de l’union de 3 résultats partiels :
- les communes de la première table qui ne sont plus dans la deuxième
- les communes apparues dans la deuxième table
- les communes présents dans les deux tables mais dont l’un des attributs au moins est différent.
La troisième partie, la plus compliquée, se sert de l’instruction CASE WHEN pour ne produire un résultat que lorsque les champs sont effectivement différents. Un attribut permettant de dire s’il s’agit d’un ajout (A) d’une suppression (R) ou d’une modification (M) est ajouté à la volée dans chacun des résultats partiels (colonne CHANGE).
DROP ALIAS IF EXISTS DIFF;
CREATE ALIAS DIFF AS $$
ResultSet query(Connection conn, String t1, String t2, String key) throws SQLException {
// Nous aurons besoin d'une version des noms de table avec guillemets et d'une version sans
String t1_ = t1.startsWith("\"")?t1:"\""+t1+"\"";
String t2_ = t2.startsWith("\"")?t2:"\""+t2+"\"";
// Clés de jointure
key = key.startsWith("\"")?key:"\""+key+"\"";
String key1 = t1_ + "." + key;
String key2 = t2_ + "." + key;
StringBuffer sb = new StringBuffer();
// Lignes disparaissant dans la table t2
sb.append("SELECT 'R' AS CHANGE, " + t1_ + ".* FROM " + t1_ + " LEFT OUTER JOIN " + t2_ + " ON (" + key1 + "=" + key2 + ") WHERE (" + key2 + " IS NULL)\n");
sb.append("UNION\n");
// Lignes apparaissant dans la table t2
sb.append("SELECT 'A' AS CHANGE, " + t2_ + ".* FROM " + t2_ + " LEFT OUTER JOIN " + t1_ + " ON (" + key2 + "=" + key1 + ") WHERE (" + key1 + " IS NULL)\n");
sb.append("UNION\n");
// Lignes modifiées
sb.append("SELECT 'M' AS CHANGE, " + key1 + ",");
// Liste des champs de la table t1
ResultSet rsColumns = conn.createStatement().executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + t1 + "'");
// Boucle permettant d'écrire les différences pour chaque attribut
while (!rsColumns.isLast()) {
rsColumns.next();
String col = "\""+rsColumns.getString(1)+"\"";
if (col.equals(key)) continue;
sb.append("\n CASE WHEN " + t1_ + "." + col + " <> " + t2_ + "." + col + " THEN " + t1_ + "." + col + "||'->'||" + t2_ + "." + col + " ELSE '' END AS " + col);
if (!rsColumns.isLast()) sb.append(",");
}
sb.append("\nFROM " + t1_ + ", " + t2_ + "\n");
sb.append("WHERE (" + key1 + " = " + key2 + ") AND (\n");
// Liste des champs de la table t1
rsColumns = conn.createStatement().executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + t1 + "'");
// Boucle permettant de tester si un attribut au moins est différent (hormis la clé)
while (!rsColumns.isLast()) {
rsColumns.next();
String col = "\""+rsColumns.getString(1)+"\"";
if (col.equals(key)) continue;
sb.append(t1_ + "." + col + " <> " + t2_ + "." + col);
if (!rsColumns.isLast()) sb.append(" OR\n");
}
sb.append(")");
sb.append("ORDER BY CHANGE, " + key);
return conn.createStatement().executeQuery(sb.toString());
}
$$;Et voici le résultat obtenu : CALL DIFF(’INSEE2009’, ’INSEE2010’, ’INSEE’) ;
CALL DIFF(’INSEE2009’, ’INSEE2010’, ’INSEE’) ;
| CHANGE | INSEE | CDC | CHEFLIEU | REG | DEP | COM | AR | CT | TNCC | ARTMAJ | NCC | ARTMIN | NCCENR |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| M | 04040 | Castellard-Melan->Castellard-Mélan | |||||||||||
| M | 24095 | CHALEIX->CHALAIS | Chaleix->Chalais | ||||||||||
| M | 2B029 | 2->5 | |||||||||||
| M | 2B036 | 2->3 | |||||||||||
| M | 2B054 | 2->3 | |||||||||||
| M | 2B055 | 2->3 | |||||||||||
| M | 2B059 | 2->3 | |||||||||||
| M | 2B069 | 2->3 | |||||||||||
| M | 2B072 | 2->3 | |||||||||||
| M | 2B077 | 2->3 | |||||||||||
| M | 2B087 | 2->3 | |||||||||||
| M | 2B101 | 2->3 | |||||||||||
| M | 2B102 | 2->3 | |||||||||||
| M | 2B109 | 2->5 | |||||||||||
| M | 2B113 | 2->3 | |||||||||||
| M | 2B125 | 2->3 | |||||||||||
| M | 2B136 | 2->5 | |||||||||||
| M | 2B140 | 2->3 | |||||||||||
| M | 2B145 | 2->3 | |||||||||||
| M | 2B166 | 2->3 | |||||||||||
| M | 2B172 | 2->5 | |||||||||||
| M | 2B185 | 2->5 | |||||||||||
| M | 2B188 | 2->5 | |||||||||||
| M | 2B192 | 2->3 | |||||||||||
| M | 2B195 | 2->3 | |||||||||||
| M | 2B205 | 2->5 | |||||||||||
| M | 2B206 | 2->3 | |||||||||||
| M | 2B207 | 2->3 | |||||||||||
| M | 2B210 | 2->3 | |||||||||||
| M | 2B214 | 2->3 | |||||||||||
| M | 2B223 | 2->5 | |||||||||||
| M | 2B230 | 2->5 | |||||||||||
| M | 2B239 | 2->5 | |||||||||||
| M | 2B241 | 2->3 | |||||||||||
| M | 2B242 | 2->3 | |||||||||||
| M | 2B243 | 2->3 | |||||||||||
| M | 2B245 | 2->3 | |||||||||||
| M | 2B246 | 2->3 | |||||||||||
| M | 2B250 | 2->3 | |||||||||||
| M | 2B252 | 2->3 | |||||||||||
| M | 2B255 | 2->3 | |||||||||||
| M | 2B257 | 2->5 | |||||||||||
| M | 2B265 | 2->5 | |||||||||||
| M | 2B273 | 2->3 | |||||||||||
| M | 2B274 | 2->3 | |||||||||||
| M | 2B280 | 2->3 | |||||||||||
| M | 2B286 | 2->3 | |||||||||||
| M | 2B287 | 2->5 | |||||||||||
| M | 2B293 | 2->3 | |||||||||||
| M | 2B297 | 2->3 | |||||||||||
| M | 2B298 | 2->5 | |||||||||||
| M | 2B299 | 2->3 | |||||||||||
| M | 2B301 | 2->5 | |||||||||||
| M | 2B302 | 2->3 | |||||||||||
| M | 2B303 | 2->3 | |||||||||||
| M | 2B307 | 2->3 | |||||||||||
| M | 2B311 | 2->3 | |||||||||||
| M | 2B313 | 2->3 | |||||||||||
| M | 2B314 | 2->5 | |||||||||||
| M | 2B317 | 2->3 | |||||||||||
| M | 2B318 | 2->3 | |||||||||||
| M | 2B319 | 2->3 | |||||||||||
| M | 2B332 | 2->5 | |||||||||||
| M | 2B333 | 2->5 | |||||||||||
| M | 2B335 | 2->3 | |||||||||||
| M | 2B340 | 2->3 | |||||||||||
| M | 2B343 | 2->3 | |||||||||||
| M | 2B346 | 2->3 | |||||||||||
| M | 2B355 | 2->3 | |||||||||||
| M | 31455 | RIEUX->RIEUX-VOLVESTRE | Rieux->Rieux-Volvestre | ||||||||||
| M | 34005 | 3->2 | |||||||||||
| M | 34010 | 3->2 | |||||||||||
| M | 34012 | 3->2 | |||||||||||
| M | 34035 | 3->2 | |||||||||||
| M | 34042 | 3->2 | |||||||||||
| M | 34060 | 3->2 | |||||||||||
| M | 34067 | 3->2 | |||||||||||
| M | 34111 | 3->2 | |||||||||||
| M | 34115 | 3->2 | |||||||||||
| M | 34128 | 3->2 | |||||||||||
| M | 34152 | 3->2 | |||||||||||
| M | 34163 | 3->2 | |||||||||||
| M | 34171 | 3->2 | |||||||||||
| M | 34174 | 3->2 | |||||||||||
| M | 34185 | 3->2 | |||||||||||
| M | 34195 | 3->2 | |||||||||||
| M | 34221 | 3->2 | |||||||||||
| M | 34236 | 3->2 | |||||||||||
| M | 34238 | 3->2 | |||||||||||
| M | 34243 | 3->2 | |||||||||||
| M | 34261 | 3->2 | |||||||||||
| M | 34264 | 3->2 | |||||||||||
| M | 34274 | 3->2 | |||||||||||
| M | 34282 | 3->2 | |||||||||||
| M | 34342 | 3->2 | |||||||||||
| M | 34343 | 3->2 | |||||||||||
| M | 45265 | ROSOY-LE-VIEIL->ROZOY-LE-VIEIL | Rosoy-le-Vieil->Rozoy-le-Vieil | ||||||||||
| M | 62477 | IZEL-LES-HAMEAUX->IZEL-LES-HAMEAU | Izel-les-Hameaux->Izel-lès-Hameau | ||||||||||
| M | 63181 | JOSERAND->JOZERAND | Joserand->Jozerand | ||||||||||
| M | 73231 | Saint-Etienne-de-Cuines->Saint-Étienne-de-Cuines | |||||||||||
| M | 74220 | Reignier-Esery->Reignier-Ésery | |||||||||||
| M | 82161 | Saint-Etienne-de-Tulmont->Saint-Étienne-de-Tulmont |
(102 enregistrements, 1201 ms)
(102 enregistrements, 1201 ms)
RSS 2.0