Chapitre 27 Etendre MySQL

Table des matières

27.1 Rouages de MySQL
27.1.1 Threads MySQL
27.1.2 Suite de test de MySQL
27.2 Ajouter des fonctions à MySQL
27.2.1 Fonctionnalités des fonctions utilisateur
27.2.2 Syntaxe de CREATE FUNCTION/DROP FUNCTION
27.2.3 Ajouter une nouvelle fonction définie par l'utilisateur (UDF)
27.2.4 Ajouter de nouvelles fonctions natives
27.3 Ajouter une nouvelle procédure à MySQL
27.3.1 La procédure Analyse
27.3.2 Ecrire une procédure

27.1 Rouages de MySQL

Ce chapitre décrit un grand nombre de notions que vous devez connaître lorsque vous travaillez sur le code de MySQL. Si vous envisagez de contribuer au développement de MySQL, que vous voulez accéder à du code ultra récent, ou que vous souhaitez simplement vous tenir au courant du développement, suivez les instructions de la section Section 2.4.3, « Installer à partir de l'arbre source de développement ». Si vous êtes intéressés par les rouages internes de MySQL, il est recommandé de vous inscrire à notre liste de diffusion interne internals. Cette liste est relativement calme. Pour les détails d'inscription, voyez Section 1.4.1.1, « Les listes de diffusion de MySQL ». Tous les développeurs de MySQL AB sont sur la liste internals et nous aidons ceux qui travaillent sur le code de MySQL. Utilisez cette liste pour poser des questions sur le code, et partagez les correctifs que vous voulez voir intégré au projet MySQL.

27.1.1 Threads MySQL

Le serveur MySQL crée les threads suivants :

  • Le thread de connexion TCP/IP, qui gère toutes les demandes de connexion, et crée un nouveau thread dédié pour gérer l'identification et le traitement des requêtes SQL, pour chaque connexion.

  • Sur Windows NT, il y a un thread appelé gestionnaire de pipe, qui effectue le même travail que le thread de gestion des demandes de connexion, sur les demandes de connexion par pipe.

  • Le threads de signal gère tous les signaux. Ce thread gère aussi normalement les alertes et les appels à process_alarm() pour forcer les délais d'expiration des connexions qui sont inactives.

  • Si mysqld est compilé avec l'option -DUSE_ALARM_THREAD, un thread dédié aux alarmes est créé. Il est uniquement utilisé sur certains systèmes où il y a des problèmes avec la fonction sigwait() ou si vous voulez utiliser la fonction thr_alarm() dans des applications qui n'ont pas de thread dédié aux signaux.

  • Lors de l'utilisation de l'option --flush_time=#, un thread dédié est créé pour vider les tables de la mémoire à intervalle régulier.

  • Chaque connexion a son propre thread dédié.

  • Chaque table différente sur laquelle des INSERT DELAYED sont pratiquées re¸oit un thread.

  • Si vous utilisez l'option --master-host, un thread de réplication sera démarré pour lire et appliquer les modifications du maître.

mysqladmin processlist affiche uniquement les threads de connexion, ceux de INSERT DELAYED et ceux de réplication.

27.1.2 Suite de test de MySQL

Jusqu'à récemment, notre suite de test principale étaient basée sur des données propriétaires de client, et pour cette raison, il n'a jamais été publié. Le seul système de test public actuel est notre script crash-me, qui est un script Perl DBI/DBD qui se trouve dans le dossier sql-bench, et divers tests qui font parti du dossier tests. Le manque d'une suite de test publique et standardisée rend difficile à nos utilisateurs et nos développeurs les possibilités de tests de régressions. Pour corriger ce problème, nous avons créé un nouveau système de tests qui est inclus dans les distributions source et binaires, à partir de la version 3.23.29.

Le jeu de test actuel ne couvre pas toutes les situations en MySQL, mais il permet d'identifier les bogues les plus courants lors de requêtes SQL, les problèmes de bibliothèques et aussi les problèmes de réplication. Notre but final est de lui faire couvrir 100% du code. Nous apprécions les contributions à notre suite de test. Vous pouvez notamment fournir des test qui examine certaines fonctions critiques de votre système, et qui assureront que les futures versions de MySQL le prennent en compte.

27.1.2.1 Exécuter la suite de tests MySQL

Le système de tests est constitué d'un interpréteur de langage de tests (mysqltest), un script Shell qui exécute tous les scripts (mysql-test-run), les cas de tests réels, écrits dans un langage spécial de tests, et leur résultats attendus. Pour exécuter ces tests sur votre système après une compilation, tapez make test ou mysql-test/mysql-test-run depuis la racine de la distribution. Si vous avez installé une distribution binaire, cd jusqu'au dossier d'installation (par exemple, /usr/local/mysql), et exécutez scripts/mysql-test-run. Tous les tests doivent réussir. Si ce n'est pas le cas, vous devriez essayer de trouver pourquoi, et faire un rapport de bogues à MySQL. See Section 27.1.2.3, « Rapporter des bugs dans la suite de tests MySQL ».

Si vous avez une copie de mysqld qui fonctionne sur la machine où vous voulez faire des tests, vous n'avez pas à l'arrêter, tant qu'elle n'utilise pas les ports 9306 et 9307. Si l'un de ces ports est pris, vous devriez éditer le script mysql-test-run et changer les valeurs des ports des maîtres et esclaves, en les rempla¸ant par des ports libres.

Vous pouvez exécuter des tests individuels avec mysql-test/mysql-test-run test_name.

Si l'un des tests échoue, vous devriez tester mysql-test-run avec l'option --force pour vérifier si aucun autre test n'échoue.

27.1.2.2 Améliorer la suite de tests MySQL

Vous pouvez utiliser le langage de mysqltest pour écrire vos propres cas de tests. Malheureusement, nous n'avons pas encore écrit une documentation complète pour ce logiciel, et nous prévoyons de le faire rapidement. Vous pouvez, toutefois, utiliser les cas de tests actuels comme exemples. Les points suivants devraient vous mettre le pied à l'étrier.

  • Les tests sont situé dans mysql-test/t/*.test

  • Un cas de tests est constitué de commandes terminées par un ;, et est similaire aux données d'entrées du client mysql. Une commande est par défaut une commande envoyée au serveur MySQL, à moins qu'il ne soit reconnu comme une commande interne (par exemple, sleep).

  • Toutes les requêtes qui produisent des résultats, comme SELECT, SHOW, EXPLAIN, etc., doivent être précédées par @/path/to/result/file. Le fichier contient alors les résultats attendus. Un moyen simple pour générer le résultat du fichier est d'exécuter mysqltest -r < t/test-case-name.test depuis le dossier de tests mysql-test, puis d'éditer le fichier résultant, si nécessaire, pour ajuster le contenu. Dans ce cas, soyez très prudent lors de l'ajout ou la suppression de caractères invisibles : assurez vous de ne changer que du texte, ou d'effacer des lignes. Vous pouvez utiliser od -c pour vous assurer que votre éditeur n'a pas perturbé le fichier durant l'édition. Bien sur, nous espérons que vous n'aurez jamais a éditer le résultat du fichier mysqltest -r ca vous n'avez à faire cela que lorsque vous découvrez un bug.

  • Pour être cohérent avec votre configuration, vous devriez placer les fichiers de résultats dans le dossier mysql-test/r et les nommer test_name.result. Si le test produit plus qu'un résultat, vous devez utiliser test_name.a.result, test_name.b.result, etc.

  • Si une commande retourne une erreur, vous devez, sur la ligne de la commande, le spécifier avec --error error-number. Le numéro d'erreur peut être une liste d'erreurs possibles, séparées par des virgules ','.

  • Si vous écrivez un test de réplication, vous devez, sur la première ligne du fichier de test, ajouter le code source include/master-slave.inc;. Pour passer entre le maître et l'esclave, utilisez connection master; et connection slave;. Si vous avez besoin d'utiliser une connexion alternative, vous pouvez utiliser connection master1; pour le maître, et connection slave1; pour l'esclave.

  • Si vous avez besoin d'une boucle, vous pouvez utiliser ceci :

    let $1=1000;
    while ($1)
    {
     # votre requête ici
     dec $1;
    }
    
  • Pour faire une pause entre les requêtes, utilisez la commande sleep. Elle supporte les fraction de secondes, ce qui vous permet d'utiliser sleep 1.3;, pour attendre 1,3 secondes.

  • Pour exécuter l'esclave avec des options additionnelles pour votre cas de tests, ajoutez les au format ligne de commande dans mysql-test/t/test_name-slave.opt. Pour le maître, ajoutez les dans mysql-test/t/test_name-master.opt.

  • Si vous avez une question sur la suite de tests, ou que vous avez un test à proposer, envoyez le par email à sur la liste interne. See Section 1.4.1.1, « Les listes de diffusion de MySQL ». Comme la liste n'accepte pas les attachements, vous devriez les placer sur le serveur FTP : ftp://support.mysql.com/pub/mysql/Incoming/

27.1.2.3 Rapporter des bugs dans la suite de tests MySQL

Si votre version de MySQL ne passe pas un teste, vous devez faire ceci :

  • N'envoyez pas de rapport de bug avant d'avoir étudier au maximum les raisons possibles de l'échec! Lorsque vous le faîtes, utilisez le programme mysqlbug, pour que nous puissions obtenir un maximum d'informations sur votre système et la version de MySQL. See Section 1.4.1.3, « Comment rapporter un bogue ou un problème ».

  • Assurez vous d'inclure le résultat de mysql-test-run, ainsi que le contenu de tous les fichiers .reject du dossier mysql-test/r.

  • Si un test de la suite échoue, vérifiez si le test échoue aussi en l'exécutant seul :

    cd mysql-test
    mysql-test-run --local test-name
    

    Si cela échoue, alors vous devriez configurer MySQL avec --with-debug et exécuter mysql-test-run avec l'option --debug. Si cela échoue aussi, envoyez le fichier de trace var/tmp/master.trace à ftp://support.mysql.com/pub/mysql/secret pour que nous puissions l'examiner. N'oubliez pas d'inclure une description complète de votre système, ainsi que de la version de l'exécutable mysqld, et de sa compilation.

  • Essayez d'exécuter mysql-test-run avec l'option --force pour voir si il n'y a pas d'autres tests qui échouent.

  • Si vous avez compilé MySQL vous-même, vérifiez notre manuel, ainsi que les notes de compilations pour votre plate-forme, ou bien, utilisez à la place un des exécutables que nous avons compilé pour vous, disponibles à http://www.mysql.com/downloads/. Toutes nos versions exécutables doivent passer la suite de tests.

  • Si vous obtenez une erreur, comme Result length mismatch ou Result content mismatch, cela signifie que le résultat de la suite de tests n'a pas la taille attendue. Cela peut être un bug de MySQL, ou que votre version de MySQL fournit un résultat d'une autre taille, dans certaines circonstances.

    Les résultats de tests qui ont échoués sont placés dans un fichier avec le même nom de base que le fichier de test, et avec l'extension .reject. Si votre test échoue, faites un diff sur les deux fichiers. Si vous ne pouvez pas voir où ils diffèrent, examinez ces deux fichiers avec od -c, et vérifiez leur tailles respectives.

  • Si un test échoue totalement, vous devriez vérifier les fichiers de log dans le dossier mysql-test/var/log, pour avoir des indices sur ce qui a échoué.

  • Si vous avez compilé MySQL avec le débogage, vous pouvez essayer de le déboger en exécutant mysql-test-run avec --gdb et/ou --debug. See Section D.1.2, « Créer un fichier de tra¸age ».

    Si vous n'avez pas compilé MySQL pour le débogage, vous devriez essayer de le faire. Spécifiez simplement l'option --with-debug dans le script de configure! See Section 2.4, « Installation de MySQL avec une distribution source ».

27.2 Ajouter des fonctions à MySQL

Il y a deux méthodes pour ajouter des fonctions à MySQL :

  • Vous pouvez ajouter la fonction grâce à l'interface de fonctions utilisateur (UDF). Les fonctions utilisateur sont ajoutées et supprimées dynamiquement avec les commandes CREATE FUNCTION et DROP FUNCTION. See Section 27.2.2, « Syntaxe de CREATE FUNCTION/DROP FUNCTION ».

  • Vous pouvez ajouter une fonction sous la forme native (intégrée) d'une fonction MySQL. Les fonctions natives sont compilées dans mysqld et sont disponibles en permanence.

Chaque méthode a ses avantages et inconvénients :

  • Si vous écrivez une fonction utilisateur, vous devez installer le fichier objet en plus du serveur lui-même. Si vous compilez votre fonction dans le serveur, vous n'avez pas ce problème.

  • Vous pouvez ajouter des UDF à une distribution binaire de MySQL. Les fonctions natives requièrent une modification de la distribution source.

  • Si vous mettez à jour votre distribution MySQL, vous pouvez continuer à utiliser vos fonctions précédemment installées. Pour les fonctions natives, vous devez refaire les modifications du code à chaque mise à jour.

Quelque soit la méthode que vous utilisez pour ajouter de nouvelles fonctions, ces fonctions pourront être utilisées comme des fonctions natives telles que ABS() ou SOUNDEX().

27.2.1 Fonctionnalités des fonctions utilisateur

L'interface MySQL pour créer des fonctions utilisateurs fournit les fonctionnalités et capacités suivantes :

  • Les fonctions peuvent retourner des chaînes, des entiers ou des nombre décimaux.

  • Vous pouvez définir des fonctions simples qui travaillent sur une ligne à la fois, ou bien des fonctions d'agrégation, qui travaillent sur plusieurs lignes à la fois.

  • Des informations fournies aux fonctions pour qu'elles puissent vérifier le nombre et le type des arguments qui leur sont passé.

  • Vous pouvez demander à MySQL de forcer certains arguments à certains types avant de les transmettre à votre fonction.

  • Vous pouvez indiquer qu'une fonction retourne NULL ou qu'une erreur est survenue.

27.2.2 Syntaxe de CREATE FUNCTION/DROP FUNCTION

CREATE [AGGREGATE] FUNCTION nom_fonction RETURNS {STRING|REAL|INTEGER}
       SONAME nom_librairie_partagée

DROP FUNCTION nom_fonction

Une fonction définie par un utilisateur (UDF) est une méthode pour intégrer une fonction qui fonctionne de la même fa¸on qu'une fonction native de MySQL, comme ABS() et CONCAT().

AGGREGATE est une nouvelle option pour MySQL version 3.23. Une fonction AGGREGATE fonctionne exactement comme une fonction native comme SUM ou COUNT().

CREATE FUNCTION enregistre le nom de la fonction, le type, et le nom des bibliothèques partagées dans la table mysql.func. Vous devez avoir les droits INSERT et DELETE dans la base mysql pour créer et supprimer les fonctions.

Toutes les fonctions actives sont rechargées chaque fois que le serveur démarre, sauf si vous démarrez mysqld avec l'option --skip-grant-tables. Dans ce cas, l'utilisation des UDF n'est pas prise en compte et les UDFs ne sont pas disponibles. (Une fonction active est une fonction qui peut être chargée avec CREATE FUNCTION et supprimée par REMOVE FUNCTION).

Concernant l'écriture des UDFs, Section 27.2, « Ajouter des fonctions à MySQL ». Pour que le mécanisme des fonctions UDF fonctionne, les fonctions doivent être écrites en C ou C++, votre système doit supporter le chargement dynamique et vous devez avoir compilé mysqld dynamiquement (pas statiquement).

Notez que pour faire fonctionner AGGREGATE, vous devez avoir une table mysql.func qui contient la colonne type. Si ce n'est pas le cas, vous devez exécuter le script mysql_fix_privilege_table pour résoudre ce problème.

27.2.3 Ajouter une nouvelle fonction définie par l'utilisateur (UDF)

Pour que le mécanisme UDF fonctionne, les fonctions doivent êtres écrites en C ou C++ et votre système doit supporter le chargement dynamique. Les sources de MySQL incluent un fichier sql/udf_example.cc qui définit 5 nouvelles fonctions. Consultez ce fichier pour voir comment marchent les conventions d'appels des UDF.

Pour que mysqld puisse utiliser les fonctions UDF, vous devez configurer MySQL avec l'option --with-mysqld-ldflags=-rdynamic. La raison est que sur diverses plates-formes, (Linux inclus) vous pouvez charger une bibliothèque dynamique (avec dlopen()) depuis un programme statique lié, que vous pouvez obtenir si vous utilisez l'option --with-mysql-ldflags=-all-static. Si vous voulez utiliser une UDF qui nécessite un accès aux symboles de mysqld (comme l'exemple methaphone dans sql/udf_example.cc qui utilise default_charset_info), vous devez lier le programme avec -rdynamic (voir man dlopen).

Pour chaque fonction que vous voulez utiliser dans SQL, vous devez définir les fonctions correspondantes en C (ou C++). Dans la discussion ci-dessous, le nom ``xxx'' est utilisé comme un exemple de nom de fonction. Pour faire la différence entre l'usage de SQL et de C/C++, XXX() (majuscules) indique l'appel d'une fonction SQL et xxx() (minuscules) indique l'appel d'une fonction C/C++.

Les fonctions C/C++ que vous écrivez pour l'implémentation de l'interface de XXX() sont :

  • xxx() (requis)

    La fonction principale. C'est là où le résultat de la fonction est calculé. La correspondance entre le type de SQL et le type retourné par votre fonction C/C++ est affiché ci-dessous :

    SQL typeC/C++ type
    STRINGchar *
    INTEGERlong long
    REALdouble
  • xxx_init() (optionnel)

    La fonction d'initialisation de xxx(). Elle peut-être utilisée pour :

    • Vérifier le nombre d'arguments de XXX().

    • Vérifier que les arguments correspondent aux types requis ou indiquer à MySQL de contraindre des arguments aux types que vous voulez quand la fonction principale est appelée.

    • Allouer la mémoire requise pour la fonction principale.

    • Spécifier la longueur maximale de la sortie.

    • Spécifier (pour les fonctions REAL) le nombre maximal de décimales.

    • Spécifier si le résultat peut-être NULL.

  • xxx_deinit() (optionnel)

    La terminaison de la fonction xxx(). Elle doit libérer toute la mémoire allouée par l'initialisation de la fonction.

Quand une requête SQL fait appel à XXX(), MySQL appelle l'initialisation de la fonction xxx_init(), pour laisser exécuter n'importe quelle action exigée, telle que la vérification d'arguments ou l'allocation de mémoire.

Si xxx_init() retourne une erreur, la requête SQL est annulée avec un message d'erreur et la fonction principale et la fonction de terminaison ne sont pas appelées. Autrement, la fonction principale xxx() est appelée une fois pour chaque ligne. Après que toutes les lignes aient été traitées, la fonction de terminaison xxx_deinit() est appelée pour procéder aux nettoyages requis.

Pour les fonctions d'agrégat (comme SUM()), vous pouvez également ajouter les fonctions suivantes :

  • xxx_reset() (requise)

    Remet la somme à zéro et insère l'argument en tant que valeur initiale pour un nouveau groupe.

  • xxx_add() (requise)

    Ajoute l'argument à l'ancienne somme.

Quand vous utilisez les UDF d'agrégat, MySQL opère comme suit :

Toutes les fonctions doivent être compatibles avec les threads (et non pas simplement la fonction principale, mais aussi les fonctions d'initialisation et de terminaison). Cela signifie que vous ne pouvez pas allouer de variables globales ou statiques. Si vous avez besoin de mémoire, allouez-la avec la fonction xxx_init() et libérez la avec xxx_deinit().

  1. Appeler xxx_init() pour laisser la fonction d'agrégat allouer la mémoire dont elle aura besoin pour stocker les résultats.

  2. Trier la table en accord avec la clause GROUP BY.

  3. Pour la première ligne dans un nouveau groupe, appeler la fonction xxx_reset().

  4. Pour chaque ligne appartenant à un même groupe, appeler la fonction xxx_add().

  5. Quand le groupe change ou lorsque la dernière ligne a été traitée, appeler xxx() pour obtenir le résultat de l'agrégat.

  6. Répéter les étapes de 3 à 5 tant que toutes les lignes n'ont pas été traitées.

  7. Appeler xxx_deinit() pour libérer la mémoire allouée.

Toutes les fonctions doivent être sûrs pour les threads (pas seulement la fonction principale, mais les fonctions d'initialisation et de terminaison également). Cela signifie que vous n'êtes pas autorisés à allouer une variable globale ou statique qui change ! Si vous avez besoin de mémoire, vous devez l'allouer avec la fonction xxx_init() et la libérer avec xxx_deinit().

27.2.3.1 Fonctions utilisateur : appeler des fonctions simples

La fonction principale doit être déclarée comme illustrée ici. Notez que le type de retour et les paramètres diffèrent, suivant que vous voulez déclarer une fonction SQL XXX() qui retournera une STRING, un INTEGER ou un REAL dans la commande CREATE FUNCTION :

Pour les fonctions de chaînes STRING :

char *xxx(UDF_INIT *initid, UDF_ARGS *args,
          char *result, unsigned long *length,
          char *is_null, char *error);

Pour les fonctions d'entiers INTEGER :

long long xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

Pour les fonctions de nombres à virgule flottante REAL :

double xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

Les fonctions d'initialisation et de terminaison sont déclarées comme ceci :

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void xxx_deinit(UDF_INIT *initid);

Le paramètre initid est passé aux trois fonctions. Il pointe sur une structure UDF_INIT qui est utilisée pour communiquer des informations entre les fonctions. Les membres de la structure UDF_INIT sont ceux qui sont listés ci-dessous. La fonction d'initialisation doit préparer les membres qu'elle veut, et notamment leur donner une valeur initiale (pour utiliser les valeurs par défaut, laissez le membre intact) :

  • my_bool maybe_null

    xxx_init() doit remplacer maybe_null par 1 si xxx() peut retourner NULL. La valeur par défaut est 1 si l'un des arguments n'est déclaré comme maybe_null.

  • unsigned int decimals

    Le nombre de décimales. La valeur par défaut est le nombre maximum de décimales dans l'argument passé à la fonction. Par exemple, vis la fonction re¸oit 1.34, 1.345 et 1.3, ce nombre sera 3, car 1.345 a 3 décimales.

  • unsigned int max_length

    La taille maximale de la chaîne résultat. La valeur par défaut dépend du type de résultat de la fonction. Pour les fonctions de chaînes, la valeur par défaut est la taille du plus grand argument. Pour les fonctions entières, la valeur est de 21 chiffres. Pour les fonctions à nombre à virgule flottante, la valeur est de 13, plus le nombre de décimales indiquées par initid->decimals. Pour les fonctions numériques, la taille inclut le signe et le séparateur décimal.

    Si vous voulez retourner un BLOB, vous devez donner à ce membre la valeur de 65 ko ou 16 Mo; cet espace mémoire ne sera pas alloué, mais utilisé pour décider quel type de colonne utiliser, si il y a un besoin de stockage temporaire.

  • char *ptr

    Un pointeur que la fonction peut utiliser pour ses besoins propres. Par exemple, la fonction peut utiliser initid->ptr pour transférer de la mémoire allouée entre les trois fonctions. En xxx_init(), allouez de la mémoire, et assignez la à ce pointeur :

    initid->ptr = allocated_memory;
    

    En xxx() et xxx_deinit(), utilisez initid->ptr pour exploiter ou supprimer la mémoire.

27.2.3.2 Appeler des fonctions utilisateurs pour les groupements

Voici une description des différentes fonctions que vous devez définir pour réaliser des calculs sur des regroupements, avec une fonction utilisateur :

Notez que ce qui suit n'est pas demandé ou utilisé par MySQL 4.1.1. Vous pouvez conserver cette définition pour assurer la compatibilité entre MySQL 4.0 et MySQL 4.1.1.

char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
                char *is_null, char *error);

Cette fonction est appelée lorsque MySQL trouve la première ligne dans un nouveau groupe. Dans cette fonction, vous devez remettre à zéro des variables internes de sommaire, puis indique le nouvel argument comme premier membre du nouveau groupe.

Dans de nombreuses situations, cela se fait en interne en remettant à zéro toutes les variables, et en appelant xxx_add().

Cette fonction n'est demandée que par MySQL 4.1.1 et plus récent :

char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);

Cette fonction est appelée à chaque fois qu'une ligne qui appartient à un groupe est trouvée, hormis la première ligne. Durant cette fonction, vous devez ajouter les données dans votre variable interne de sommaire.

Vous pouvez utiliser le pointeur error pour stocker un octet si quelque chose n'a pas fonctionné.

char *xxx_add(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

Cette fonction est appelée pour toutes les lignes du même groupe, hormis pour la première ligne. Dans cette dernière, vous devez ajouter la valeur dans UDF_ARGS pour vos variables internes.

La fonction xxx() doit être déclarée de manière identique à celle d'un fonction utilisateur simple. See Section 27.2.3.1, « Fonctions utilisateur : appeler des fonctions simples ».

Cette fonction est appelée lorsque toutes les lignes d'un groupe ont été traitées. Vous ne devez normalement pas accéder à la variable args ici, mais retourner votre valeur, à partir des valeurs du sommaire interne.

Tous les traitements des arguments de xxx_reset() et xxx_add() doivent être fait d'une manière similaire à celle des fonctions UDF normales. See Section 27.2.3.3, « Traitement des arguments ».

La gestion de la valeur retournée par xxx() doit être identique à celle d'une fonction utilisateur classique. See Section 27.2.3.4, « Valeurs de retour et gestion d'erreurs. ».

Le pointeur argument de is_null et error sont les mêmes pour tous les appels de xxx_reset(), xxx_add() et xxx(). Vous pouvez utiliser ces valeur pour vous rappeler si vous avez rencontré une erreur, ou si la fonction xxx() doit retourner NULL. Notez que vous ne devez pas stocker de chaîne dans *error! C'est un conteneur d'un seul octet!

is_null est remis à zéro pour chaque (avant d'appeler xxx_reset(). error n'est jamais remis à zéro.

Si isnull ou error sont modifiés après xxx(), alors MySQL va retourner NULL comme résultat de la fonction de groupement.

27.2.3.3 Traitement des arguments

Le paramètre args pointe sur une structure UDF_ARGS qui dispose des membre suivants :

  • unsigned int arg_count

    Le nombre d'arguments. Vérifiez cette valeur dans la fonction d'initialisation, si vous voulez que votre fonction soit appelée avec un nombre particulier d'arguments. Par exemple :

    if (args->arg_count != 2)
    {
        strcpy(message,"XXX() requires two arguments");
        return 1;
    }
    
  • enum Item_result *arg_type

    Le type de chaque argument. Les valeurs possibles pour chaque type sont STRING_RESULT, INT_RESULT et REAL_RESULT.

    Pour s'assurer que les arguments sont d'un type donné, et retourner une erreur dans le cas contraire, vérifiez le tableau arg_type durant la fonction d'initialisation. Par exemple :

    if (args->arg_type[0] != STRING_RESULT ||
        args->arg_type[1] != INT_RESULT)
    {
        strcpy(message,"XXX() requires a string and an integer");
        return 1;
    }
    

    Comme alternative à l'imposition d'un type particulier pour les arguments des fonctions, vous pouvez utiliser la fonction pour qu'elle modifie le type des arguments et donne aux valeurs de arg_type le type que vous souhaitez. Cela fait que MYSQL va forcer les arguments à un type donnée, pour chaque appel de la fonction xxx(). Par exemple, pour forcer le type des deux premiers arguments en chaîne et entier, vous pouvez utiliser la fonction d'initialisation xxx_init() :

    args->arg_type[0] = STRING_RESULT;
    args->arg_type[1] = INT_RESULT;
    
  • char **args

    args->args communique les informations à la fonction d'initialisation, ainsi que la nature des arguments avec laquelle elle a été appelée. Pour un argument constant i, args->args[i] pointe sur la valeur de l'argument. Voir plus bas pour les instructions d'accès à cette valeur. Pour les valeurs non constantes, args->args[i] vaut 0. Un argument constant est une expression qui utilise des constantes, comme 3 ou 4*7-2 ou SIN(3.14). Un argument non-constant est une expression qui fait référence aux valeurs qui peuvent changer de ligne en ligne, par exemple des noms de colonnes ou des fonctions qui sont appelées avec des arguments non-constants.

    Pour chaque invocation de la fonction principale, args->args contient les arguments réels qui sont passés à la ligne qui sera traitée.

    Les fonctions peuvent faire référence à un argument i comme ceci :

    • Un argument de type STRING_RESULT est donné sous la forme d'un pointeur de chaîne, plus une longueur, pour permettre la gestion des données binaires ou des données de taille arbitraire. Le contenu des chaînes est disponible avec l'expression args->args[i] et la taille de la chaîne est args->lengths[i]. Ne supposez pas que les chaînes sont terminés par le caractère nul.

    • Pour un argument de type INT_RESULT, vous devez transtyper la valeur args->args[i] en valeur long long :

      long long int_val;
      int_val = *((long long*) args->args[i]);
      
    • Pour un argument de type REAL_RESULT, vous devez transtyper la valeur args->args[i] en valeur double :

      double    real_val;
      real_val = *((double*) args->args[i]);
      
  • unsigned long *lengths

    Pour une fonction d'initialisation, le tableau lengths indique la taille maximale des chaînes pour chaque argument. Vous ne devez pas les modifier. Pour chaque appel de la fonction principale, lengths contient la taille réelle de toutes les chaînes arguments qui sont passé pour la ligne traitée. Pour les arguments de type INT_RESULT ou REAL_RESULT, lengths contient toujours la taille maximale de l'argument (comme pour la fonction d'initialisation).

27.2.3.4 Valeurs de retour et gestion d'erreurs.

La fonction d'initialisation doit retourner 0 si aucune erreur ne s'est produite et 1 sinon. Si une erreur s'est produite, xxx_init() doit stocker un message se terminant par un NULL dans le paramètre message. Le message sera retourné au client. La taille du tampon du message est de MYSQL_ERRMSG_SIZE caractères, mais vous devez essayer de garder une taille de message inférieure à 80 caractères, sinon, il remplit la largeur d'un écran de terminal standard.

La valeur de retour de la fonction principale xxx() est la valeur de la fonction, pour les fonctions long long et double. Une fonction de chaîne de caractères doit retourner un pointeur vers le résultat et stocker la taille de la chaîne de caractères dans l'argument length.

Affectez cette valeur au contenu et à la longueur de la valeur retournée. Par exemple :

memcpy(result, "chaîne retournée", 16);
*length = 16;

Le tampon result qui est passé à la fonction a une taille de 255 bits. Si votre résultat dépasse ceci, ne vous inquiétez pas de l'allocation de mémoire pour ce résultat.

Si votre fonction de chaînes de caractères a besoin de retourner une chaîne de caractères plus grande que 255 bits, vous devez allouer de l'espace pour cela avec malloc() dans votre fonction xxx_init(). Vous pouvez stocker la mémoire allouée dans le buffer ptr de la structure UDF_INIT pour être ré-utilisée par les appels futurs de xxx(). See Section 27.2.3.1, « Fonctions utilisateur : appeler des fonctions simples ».

Pour indiquer une valeur de retour NULL dans la fonction principale, mettez is_null à 1 :

*is_null = 1;

Pour indiquer une erreur retournée dans la fonction principale, mettez le paramètre error à 1:

*error = 1;

Si xxx() met *error à 1 pour chaque ligne, la valeur de la fonction est NULL pour la ligne en question et pour chaque ligne suivante traitée par le processus dans lequel XXX() est invoqué. (xxx() ne sera même pas appelé pour les lignes suivantes.) Remarque : dans les versions antérieures à 3.22.10, vous devez définir *error et *is_null :

*error = 1;
*is_null = 1;

27.2.3.5 Compiler et installer des fonctions utilisateurs

Les fichiers qui implémentent des fonctions utilisateurs doivent être compilés et installés sur le même hôte que celui du serveur. Ce processus est décrit plus bas, avec le fichier udf_example.cc qui est inclut dans les sources MySQL. Ce fichier contient les fonctions suivantes :

  • metaphon() retourne la version métaphone de la chaîne en argument. C'est une technique proche du soundex, mais elle est bien plus optimisée pour l'anglais.

  • myfunc_double() retourne la moyenne des codes ASCII des caractères de la chaîne passée en argument.

  • myfunc_int() retourne la somme de tailles des arguments.

  • sequence([const int]) retourne une séquence, commen¸ant à partir du nombre choisit ou 1, si aucun nombre n'a été fourni.

  • lookup() retourne l'adresse IP numérique d'un hôte.

  • reverse_lookup() retourne le nom d'hôte pour une adresse IP. Cette fonction peut être appelée avec une chaîne au format "xxx.xxx.xxx.xxx" ou quatre nombres.

Un fichier dynamiquement chargé doit être compilé sous la forme d'un objet partagé, grâce à une commande comme celle-ci :

shell> gcc -shared -o udf_example.so myfunc.cc

Vous pouvez facilement trouver les options correctes pour la compilation en exécutant cette commande dans le dossier sql de votre installation source :

shell> make udf_example.o

Vous devez exécuter une commande de compilation similaire à celle que le make affiche, sauf que vous devrez supprimer l'option -c près de la fin de la ligne, et ajouter -o udf_example.so à la fin de la ligne. Sur certains systèmes, vous devrez aussi supprimer -c de la commande).

Une fois que vous compilez un objet partagés contenant des fonctions utilisateurs, vous devez les installer, et prévenir le serveur MYSQL. Compiler un objet partagé avec udf_example.cc produit un fichier qui s'appelle udf_example.so (le nom exact peut varier suivant la plate-forme). Copiez ce fichier dans l'un des dossiers utilisé par ld, tel que /usr/lib. Par exemple, /etc/ld.so.conf.

Sur de nombreux systèmes, vous pouvez faire pointer la variable d'environnement LD_LIBRARY ou LD_LIBRARY_PATH pour qu'elle pointe dans le dossier où vous avez vos fichiers de fonctions. Le manuel de dlopen vous indiquera quelle variable utiliser sur votre système. Vous devriez indiquer cette valeur dans les options de démarrage de mysql.server et safe_mysqld, et redémarrer mysqld.

Après que la bibliothèque ait été installée, indiquez à mysqld ces nouvelles fonctions avec ces commandes :

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME "udf_example.so";
mysql> CREATE AGGREGATE FUNCTION avgcost
    ->        RETURNS REAL SONAME "udf_example.so";

Les fonctions peuvent être effacées plus tard avec DROP FUNCTION:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;

Les commandes CREATE FUNCTION et DROP FUNCTION modifient la table système func dans la base mysql. Le nom de la fonction, son type et le nom de la bibliothèque partagée sont alors sauvés dans la table. Vous devez avoir les droits de INSERT et DELETE dans la base mysql pour ajouter et effacer des fonctions.

Vous ne devez pas utiliser la commande CREATE FUNCTION pour ajouter une fonction qui a déjà été créée. Si vous devez reinstaller une fonction, vous devez la supprimer avec la commande DROP FUNCTION puis la reinstaller avec CREATE FUNCTION. Vous devrez faire cela, par exemple, si vous recompilez une nouvelle version de votre fonction, pour que mysqld utilise cette nouvelle version. Sinon, le serveur va continuer à utiliser l'ancienne version.

Les fonctions actives sont rechargées à chaque fois que le serveur démarre, à moins que vous ne démarriez le serveur mysqld avec l'option --skip-grant-tables. Dans ce cas, l'initialisation des fonctions utilisateurs sont ignorées, et ces fonctions sont inutilisables. Une fonction active doit avoir été créée avec CREATE FUNCTION et pas supprimée avec DROP FUNCTION.

27.2.3.6 Précautions à prendre avec les fonctions utilisateur

MySQL prend les mesures suivantes pour éviter une utilisation abusive des fonctions utilisateurs.

Vous devez avoir les droits de INSERT pour être capable d'utiliser la commande CREATE FUNCTION et le droit de DELETE pour être capable d'effacer une fonction (DROP FUNCTION. Ceci est nécessaire car ces commandes ajoutent et suppriment des lignes dans la table mysql.func.

UDF doit avoir au moins un symbole défini, en plus du symbole xxx qui correspond à la fonction principale xxx(). Ces symboles auxiliaires correspondent aux fonctions xxx_init(), xxx_deinit(), xxx_reset(), xxx_clear() et xxx_add(). Depuis MySQL 4.0.24, 4.1.10a et 5.0.3, mysqld supporte l'option --allow-suspicious-udfs qui spécifie si les UDF qui n'ont qu'une fonction xxx peuvent être chargées. Par défaut, cette fonction est désactivée, ce qui empêche les fonctions de charger des fonctions depuis des objets partagés autres que les UDF légales. Si vous avez de vieilles fonctions UDF qui contiennent uniquement le symbole xxx qui ne peuvent pas être recompilées pour utiliser un symbole auxiliaire, il sera nécessaire d'utiliser l'option --allow-suspicious-udfs. Sinon, vous devrez vous passer de cette fonctionnalité.

Les fichiers d'objet UDF ne peuvent pas être placés dans n'importe quel dossier. Ils doivent être placés dans un dossier système que le compilateur dynamique peut analyser. Pour assurer le fonctionnement de cette restriction, et éviter les attaques par spécification de chemins arbitraires en dehors de ceux que le compilateur dynamique peut atteindre, MySQL vérifie dans le nom de l'objet partagé spécifié dans la commande CREATE FUNCTION la présence de délimiteurs de dossiers. Depuis MySQL 4.0.24, 4.1.10a et 5.0.3, MySQL vérifie aussi les délimiteurs de fichiers stockés dans la table mysql.func lorsque vous chargez les fonctions. Cela évite les tentatives de spécifications de chemins interdits, en manipulant directement les tables mysql.func. Pour plus de détails sur les UDF et le compilateur dynamique, voyez Section 27.2.3.5, « Compiler et installer des fonctions utilisateurs ».

27.2.4 Ajouter de nouvelles fonctions natives

La procédure pour ajouter une nouvelle fonction native est décrite ici. Notez que vous ne pouvez ajouter de fonctions natives à une distribution binaire car la procédure implique la modifications du code source de MySQL. Vous devez compiler MySQL vous-même à partir d'une distribution des sources. Notez aussi que si vous migrez vers une autre version de MySQL (par exemple, quand une nouvelle version est réalisée), vous devrez répéter la procédure avec la nouvelle version.

Pour ajouter une nouvelle fonction native à MySQL, suivez les étapes suivantes :

  1. Ajoutez une ligne dans lex.h qui définit le nom de la fonction dans le tableau sql_functions[].

  2. Si le prototype de la fonction est simple (elle prend zéro, un, deux ou trois arguments), vous devez spécifier dans lex.h SYM(FUNC_ARG#) (où # est le nombre d'arguments) en tant que second argument dans le tableau sql_functions[] et ajouter une fonction qui crée un objet fonction dans item_create.cc. Regardez "ABS" et create_funcs_abs() pour un exemple.

    Si le prototype de la fonction est compliqué (par exemple, elle prend un nombre variable d'arguments), vous devez ajouter deux lignes à sql_yacc.yy. Une qui indique le symbole preprocesseur que yacc doit définir (cela doit être ajouté au début du fichier). Puis définir les paramètres de la fonction et un ``item'' avec ces paramètres à la règle simple_expr. Pour un exemple, vérifiez toutes les occurrences de ATAN dans sql_yacc.yy pour voir comment cela se fait.

  3. Dans item_func.h, déclarez une classe héritant de Item_num_func ou de Item_str_func, selon que votre fonction retourne une nombre ou un chaîne.

  4. Dans le fichier item_func.cc, ajoutez l'une des déclaration suivantes, selon que vous définissez une fonction numérique ou de chaîne de caractères :

    double   Item_func_newname::val()
    longlong Item_func_newname::val_int()
    String  *Item_func_newname::Str(String *str)
    

    Si vous héritez votre objet de l'un des éléments standards (comme Item_num_func) vous n'aurez probablement qu'à définir l'une des fonctions décrites ci-dessus et laisser l'objet parent prendre soin des autres fonctions. Par exemple, la classe Item_str_func définit une fonction val() qui exécute atof() sur la valeur retournée par ::str().

  5. Vous devez aussi probablement définir la fonction objet suivante :

    void Item_func_newname::fix_length_and_dec()
    

    Cette fonction doit au moins calculer max_length en se basant sur les arguments donnés. max_length est le nombre maximal de caractères que la fonction peut retourner. Cette fonction doit aussi définir maybe_null = 0 si la fonction principale ne peut pas retourner une valeur NULL. La fonction peut vérifier si l'un de ses arguments peut retourner NULL en vérifiant la variable maybe_null des arguments. Vous pouvez regarder Item_func_mod::fix_length_and_dec pour avoir un exemple concret.

Toutes les fonctions doivent être sûres pour les threads (en d'autres termes, n'utilisez aucune variable statique ou globale dans la fonction sans les protéger avec mutex).

Si vous voulez retourner NULL, à partir de ::val(), ::val_int() ou ::str() vous devez mettre null_value à 1 et retourner 0.

Pour les fonctions de l'objet ::str(), il y a d'autres considérations à prendre en compte :

  • L'argument String *str fournit un tampon de chaîne qui peut être utilisé pour contenir le résultat. (Pour plus d'informations à propos du type String, regardez le fichier sql_string.h.)

  • La fonction ::str() doit retourner la chaîne contenant le résultat ou (char*) 0 si celui-ci est NULL.

  • Aucune des fonctions de chaînes n'essaye d'allouer de mémoire tant que ce n'est pas nécessaire !

27.3 Ajouter une nouvelle procédure à MySQL

Avec MySQL, vous pouvez définir une procédure en C++ qui accède et modifie les données dans la requête avant que celle-ci ne soit envoyée au client. La modification peut être faite ligne par ligne ou au niveau de GROUP BY.

Nous avons crée une procédure d'exemple avec la version 3.23 de MySQL pour vous montrer comment cela fonctionne.

De plus, nous vous recommandons de jeter un oeil à mylua. Avec cela, vous pouvez utiliser le langage LUA pour charger une dynamiquement une procédure dans mysqld.

27.3.1 La procédure Analyse

analyse([max elements,[max memory]])

Cette procédure est définie dans le fichier sql/sql_analyse.cc. Elle examine les résultats de vos requêtes et en retourne une analyse :

  • max elements (256 par défaut) est le nombre maximal de valeurs distinctes que analyse retiendra par colonne. C'est utilisé par analyse pour vérifier si le type optimal de la colonne ne serait pas le type ENUM.

  • max memory (8192 par défaut) est le maximum de mémoire que analyse doit allouer par colonne quand elle essaye de trouver toutes les valeurs distinctes.

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

27.3.2 Ecrire une procédure

Pour le moment, la seule documentation à ce sujet est constituée par les sources.

Vous pourrez trouver toutes les informations sur les procédures en examinant les fichiers suivants :

  • sql/sql_analyse.cc

  • sql/procedure.h

  • sql/procedure.cc

  • sql/sql_select.cc