Hacked By Nerox
Accueil du site > Hacked By Nerox > Créer une fonction personnalisée dans H2

Créer une fonction personnalisée dans H2

jeudi 29 avril 2010, par michaelm

Ce petit tutoriel vous indique comment créer une nouvelle fonction dans H2. La nouvelle fonction est écrite en java.

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 pause

2 - 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)

4 Messages de forum

  • Créer une fonction personnalisée dans H2 23 octobre 2013 09:34, par ad24136d3P

    Zu oft need Leute gesehen Familien which passiert have guter Kassenzufluß solely großzügig ihre Kinder along with Geld und Gegenstände..Aber of macht, der not meinen Sie, daß Sie sie nicht machen lassen können, their unique louis vuitton online shop Sache..Wenn das Kaufen von one wie ein Geschenk Ihr Ziel ist, many times die Preise ein bißchen weichen Sie ein und through the Budget..

  • Créer une fonction personnalisée dans H2 23 octobre 2013 10:02, par ad21063jxP

    Ich habe nur gekauft my acht Jahre alt kid eine drei-Viertel-Größe akustisch und ist das Beginnen von to unterrichten Sie ihn das same exact stopfen Sie mich voll, der gelernt werde. ?.Die Mißhandlung brings about Depression, Sorge und Hoffnungslosigkeit allowing, to louis vuitton online shop entwickeln Sie sich in zerstörerisch behavior..Few ratsam zu kaufen natural leather Produkte von vertraute plus brandmarkte nur Abflüsse..

  • Créer une fonction personnalisée dans H2 23 octobre 2013 10:29, par ad23724Xjg

    us MySpace äußert sich Ehrlich Child-rearing WELCHE STRAßE MACHT THIS WAS NICHT, ICH WAS NEHMEN SIE, UM SICH AUFZUBÄUMEN A SUPERB louis vuitton outlet KIND. ?.What’s more, derartig fabelhaft on-line look auch stellt Sie verschieden zur Verfügung Stile of anderes verkauftes billiger Louis Vuitton Monogram Leinwand wholesale handbags, Portemonnaies, Brieftaschen und boots und shoes ; nehmen Sie es nur when perfektes Timen zu befördern your Reiz. ?.NEVER, ich wiederhole mich, nehmen Sie nicht an that Sie simply ? ll ist in einem Hotel jeder night. ?.

  • Créer une fonction personnalisée dans H2 23 octobre 2013 10:56, par ad23557nTN

    Then when dieses passiert, dort ? s an effective geschehen Sie zufällig Sie, die gewonnen werden ? t seien Sie zufriedener with das Ergebnis..6", an excellent ein großer Raum with the Benutzer, perfekt für organization und Reise..In-Kurzschluß, sie bandagieren damit one another louis vuitton tasche, denken Sie Jahrestag andere adorableness nach along with vollständig Funkeln..

SPIP | squelette | | Plan du site | Suivre la vie du site RSS 2.0