Étendre un serveur MySQL/MariaDB avec des fonctions compilées – Partie 1/2

MySQL et MariaDB offrent la possibilité de développer des fonctions compilées, à utiliser comme toute autre fonction native dans vos requêtes SQL. Étudions la puissance et les dangers d’ajouter du code au serveur.

Les User Defined Functions sont un moyen d’étendre les possibilités du serveur MySQL en ajoutant des fonctions écrites en C ou C++, qui peuvent être utilisées comme d’autres fonctions SQL dans des requêtes (c’estàdire, qui se comportent comme les fonctions natives telles que REPLACE, SUBSTR ou SUM).

Elles sont entièrement compatibles entre MySQL et MariaDB, y compris au niveau binaire, car ces deux systèmes s’appuient sur la même interface pour l’écriture des UDF. Par souci de clarté, dans la suite de cet article les informations sont mentionnées pour MySQL, mais elles sont équivalentes pour MariaDB.

1. Qu’est-ce qu’une User Defined Function ?

1.1 Les fonctions en SQL

Tout d’abord, une fonction stockée est, à l’instar d’une procédure stockée, une fonction écrite comme une série d’instructions SQL, et enregistrée au niveau de la base de données. Elle porte un nom, et accepte un nombre arbitraire d’arguments. En plus des arguments d’appel, la fonction peut travailler avec d’autres données de la base, telles que le résultat d’une requête SELECT.

Le code SQL d’une fonction stockée est lisible en clair, et modifiable (par les personnes autorisées). Un export de la base peut contenir le code des fonctions et procédures stockées, si l’option –routines est précisée à l’outil de dump :

1.2 Cas des fonctions UDF

Une fonction User-Defined, quant à elle, est du code compilé sous la forme d’un fichier binaire partagé (shared object), et installé puis déclaré au niveau du serveur de bases de données. La fonction peut accepter par valeur un nombre prédéfini d’arguments, mais elle ne peut pas travailler sur d’autres données de la base.

Le code, puisque compilé, n’est pas lisible. Il n’est pas non plus exporté dans un dump de la base.

Une fonction UDF est généralement considérée plus véloce qu’une fonction SQL. Mais surtout, il n’est pas toujours possible, ou optimal, d’obtenir un résultat souhaité à l’aide des seules fonctions SQL. Par exemple, pour transformer une chaîne de caractères en mettant une majuscule à chaque mot, il peut s’avérer très complexe, voire impossible, de n’utiliser que les fonctions natives de MySQL (telles que LOCATE et SUBSTR). Et, même avec une boucle WHILE écrite en SQL, la complexité d’exécution et l’efficacité de l’algorithme sont considérablement moins contrôlables qu’avec du code C. La fonction compilée peut également utiliser des ressources (système, fichiers, structures de données, etc.) auxquelles les fonctions SQL natives n’ont pas accès (voir plus loin la section Sécurité).

En outre, UDF est actuellement (v5.7) la seule voie pour créer une fonction d’agrégat (à utiliser en conjonction avec la clause GROUP BY), fournissant un résultat à partir de plusieurs enregistrements (ayant un comportement similaire à SUM, AVG, etc.). Par exemple, MySQL ne fournit pas nativement de fonction pour calculer la valeur médiane d’une population de nombres : certains projets pourraient avoir besoin de cet outil.

Dans leur forme actuelle, les UDF ont été introduites dans MySQL à la version 3.23 (janvier 2001). Auparavant, qui voulait enrichir les possibilités de MySQL avec des fonctions écrites en langage C, devait recompiler [1] le serveur avec son code personnalisé (cette option demeure possible pour ajouter des fonctions natives [2]).

Les fonctions UDF peuvent servir à des fins très diverses, de l’implémentation de formules mathématiques ou statistiques, jusqu’à l’émission de requêtes HTTP, en passant par l’interprétation de balises XML ou l’extraction d’éléments HTML par exemple.

2. Mise en œuvre

Supposons que l’on souhaite réaliser une bibliothèque qui contiendra une fonction UDF que nous nommerons XXX (les noms de fonctions SQL sont indifférents à la casse). Le code source de chaque UDF se compose d’un ensemble de fonctions C que le moteur exécute selon un certain cycle. Notez qu’il est possible d’écrire plusieurs fonctions UDF dans un même shared object.

Il faut écrire le code en respectant certaines règles, puis le compiler, installer le binaire sur l’hôte qui exécute le service de bases de données, et déclarer la bibliothèque au serveur MySQL.

2.1 Prérequis

Pour pouvoir compiler le code d’une fonction UDF, le système doit disposer des paquets suivants (si basé sur Debian) :

2.2 Écriture d’une UDF

Une fonction UDF peut retourner un des trois types SQL suivants : INTEGER, REAL, STRING, qui correspondent respectivement aux types C : long long, double et char *. Les arguments qu’elle accepte sont de ces mêmes types, et par valeur (c’est-à-dire qu’une fonction ne peut pas modifier la valeur d’un champ de la base ou d’une variable MySQL).

Le code source d’une fonction UDF doit respecter certaines conventions et signatures [3]. Un exemple complet et instructif est fourni [4] avec le code source du serveur MySQL.

2.2.1 Fonctions simples

Une UDF simple (sans agrégat) se compose de trois fonctions C : xxx_init(), xxx(), et xxx_deinit(). Elles sont invoquées selon le cycle suivant :

Dans toute la suite, les xxx sont à comprendre comme un préfixe aux différentes fonctions C. Vous devrez le remplacer par le vrai nom de votre fonction UDF, tel qu’utilisé dans vos requêtes SQL.

  1. Le moteur identifie qu’une requête utilise la fonction XXX( ), et charge la bibliothèque correspondante si ce n’est pas déjà fait.
  2. Le moteur invoque le point d’entrée d’initialisation de la fonction : par convention, il s’agit obligatoirement du symbole xxx_init(), où le nom de la fonction UDF est en minuscules (ici xxx). La présence de cette fonction dans votre bibliothèque est optionnelle ; le moteur ne l’invoque que si elle s’y trouve, sans lever d’erreur dans le cas contraire. L’appel à _init a lieu une fois avant l’exécution de la requête (mais de nouveau pour chaque requête utilisant votre fonction, et autant de fois que la fonction y apparaît). Elle vous offre la possibilité d’allouer les ressources nécessaires (mémoire, etc.), de mettre à zéro vos compteurs, et autres opérations préliminaires. C’est également dans cette fonction que vous pouvez indiquer si la valeur NULL est une valeur de retour possible pour votre UDF.
  3. Une fois pour chaque ligne, le serveur appelle votre fonction xxx(), qui est la fonction principale (et qui porte obligatoirement le même nom que la fonction SQL). C’est elle qui retourne le résultat, ou qui indique si une erreur s’est produite (par exemple, en cas de division par zéro).
  4. Une fois la requête terminée, et toutes les lignes traitées, votre fonction xxx_deinit() (optionnelle) est appelée. C’est ici que vous libérez toutes les ressources réservées par _init ou dans votre fonction principale.

Si le code est en C++, penser à exporter les points d’entrée avec la directive extern “C” afin que les noms soient conservés.

2.2.2 Agrégats

Pour une fonction d’agrégat, deux points d’entrée supplémentaires doivent être fournis : xxx_add() et xxx_clear(). Le cycle devient le suivant :

  1. Identification et chargement.
  2. Appel à xxx_init().
  3. Le moteur groupe et trie les lignes selon les prescriptions de la clause GROUP BY.
  4. Au début de chaque nouveau groupe de lignes, appel de la fonction xxx_clear(). Vous y réinitialisez tous les compteurs et ressources communs à un même groupe de lignes.
  5. Pour chaque ligne du groupe, invocation de xxx_add(). Dans cette fonction, vous incrémentez vos compteurs et autres valeurs cumulatives dans vos propres structures de données (voir plus loin).
  6. À la fin de chaque groupe (une fois que _add a été appelé sur toutes les lignes du groupe), le moteur exécute votre fonction principale xxx() sur l’ensemble des données cumulées au cours de chaque appel _add dans votre structure personnelle.
  7. Reprise de l’étape 3, tant qu’il reste des groupes à traiter.
  8. Appel final à xxx_deinit().

Le nom _add() pourrait semer la confusion, mais il s’agit bien sûr d’ajouter une valeur à son groupe, afin d’y appliquer le calcul de votre choix. Il ne s’agit pas d’effectuer obligatoirement une simple addition.

2.2.3 Arguments et structure de travail

Chacune de ces fonctions reçoit en paramètre un pointeur vers une structure C UDF_INIT, toujours la même entre tous les appels au sein d’un même cycle.

Par exemple :

Un des membres de cette struct est char *ptr, réservé aux besoins du développeur : c’est par lui que vous pouvez maintenir vos compteurs et autres variables partagées via votre propre structure de données (particulièrement utile pour le cas des fonctions d’agrégat), en ayant pris soin d’allouer la mémoire nécessaire dans _init, et de la libérer dans _deinit.

Les arguments SQL passés à la fonction (les vraies valeurs, pour chaque ligne) se récupèrent grâce à la structure UDF_ARGS. Cette struct contient (entre autres) :

  • le membre unsigned int arg_count qui indique le nombre d’arguments ;
  • le membre enum Item_result *arg_type qui est un tableau indiquant le type SQL de chaque argument ;
  • le membre char **args qui fournit un tableau des valeurs effectives des arguments.

L’argument char *error vous permet d’indiquer (en lui donnant la valeur 1) si les paramètres d’appel à votre fonction provoquent une erreur. Dans ce cas, la valeur de retour de votre UDF pour cette ligne sera NULL.

2.2.4 Compilation

Une fois votre code rédigé (par exemple, dans un fichier glmf-udf.c), la compilation se fait par la commande :

Ceci produit le fichier glmf-udf.so. L’argument -I (i majuscule) précise le chemin des en-têtes MySQL, qui peut se trouver à un autre emplacement sur votre distribution. C’est le rôle du paquet libmysqlclient-dev installé en prérequis.

Tout votre code doit être thread-safe : plusieurs requêtes peuvent s’exécuter en même temps, et une même requête peut faire appel plusieurs fois à votre fonction en différents endroits. Il faut donc notamment proscrire les variables globales et statiques.

Gabriel Zerbib

La seconde partie de cet article sera publiée prochainement sur le blog, restez connectés 😉

Retrouvez cet article (et bien d’autres) dans GNU/Linux Magazine n°203, disponible sur la boutique et sur la plateforme de lecture en ligne Connect !