Fonctions définies par l’utilisateur

Article
06/05/2023

La plupart des bases de données possèdent un dialecte procédural de SQL que vous pouvez utiliser pour définir vos propres fonctions. SQLite, en revanche, s’exécute in-process avec votre application. Il vous suffit donc, au lieu d’apprendre un nouveau dialecte de SQL, d’employer le langage de programmation de votre application.

Fonctions scalaires

Les fonctions scalaires retournent une valeur scalaire unique pour chaque ligne d’une requête. Définissez de nouvelles fonctions scalaires et remplacez les fonctions intégrées à l’aide de CreateFunction.

Pour connaître la liste des paramètres pris en charge et des types de retour de l’argument func, consultez Types de données.

L’argument state permet de passer cette valeur à chaque appel de la fonction. Utilisez cette option pour éviter les fermetures.

Si votre fonction est déterministe, spécifiez isDeterministic pour permettre à SQLite d’utiliser des optimisations supplémentaires lors de la compilation de requêtes.

L’exemple suivant montre comment ajouter une fonction scalaire pour calculer le rayon d’un cylindre.

connection.CreateFunction(
    "volume",
    (double radius, double height)
        => Math.PI * Math.Pow(radius, 2) * height);

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT name,
           volume(radius, height) AS volume
    FROM cylinder
    ORDER BY volume DESC
";

Opérateurs

Les opérateurs SQLite suivants sont implémentés par les fonctions scalaires correspondantes. Si elles sont définies dans votre application, ces fonctions scalaires remplaceront le comportement de ces opérateurs.

Opérateur	Fonction
X GLOB Y	glob(Y, X)
X LIKE Y	like(Y, X)
X LIKE Y ESCAPE Z	like(Y, X, Z)
X MATCH Y	match(Y, X)
X REGEXP Y	regexp(Y, X)

L’exemple suivant montre comment définir la fonction regexp pour activer l’opérateur correspondant. SQLite n’inclut pas d’implémentation par défaut de la fonction regexp.

connection.CreateFunction(
    "regexp",
    (string pattern, string input)
        => Regex.IsMatch(input, pattern));

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT count()
    FROM user
    WHERE bio REGEXP '\w\. {2,}\w'
";
var count = command.ExecuteScalar();

Fonctions d'agrégation

Les fonctions d’agrégation retournent une seule valeur agrégée pour toutes les lignes d’une requête. Définissez et remplacez les fonctions d’agrégation à l’aide de CreateAggregate.

L’argument seed spécifie l’état initial du contexte. Utilisez-le également pour éviter les fermetures.

L’argument func est appelé une fois par ligne. Utilisez le contexte pour accumuler un résultat final. Retournez le contexte. Ce modèle permet au contexte d’être un type valeur ou d’être immuable.

Si aucun resultSelector n’est spécifié, c’est l’état final du contexte qui est utilisé comme résultat. Cela peut simplifier la définition de fonctions telles que Sum et Count qui n’ont besoin que d’incrémenter un nombre à chaque ligne et de le retourner.

Spécifiez resultSelector pour calculer le résultat final à partir du contexte après itération sur toutes les lignes.

Pour connaître la liste des types de paramètres pris en charge pour l’argument func et les types de retour de resultSelector, consultez Types de données.

Si votre fonction est déterministe, spécifiez isDeterministic pour permettre à SQLite d’utiliser des optimisations supplémentaires lors de la compilation de requêtes.

L’exemple suivant définit une fonction d’agrégation pour calculer l’écart type d’une colonne.

connection.CreateAggregate(
    "stdev",

    // A tuple to maintain context between rows
    (Count: 0, Sum: 0.0, SumOfSquares: 0.0),

    // This is called for each row
    ((int Count, double Sum, double SumOfSquares) context, double value) =>
    {
        context.Count++;
        context.Sum += value;
        context.SumOfSquares += value * value;

        return context;
    },

    // This is called to get the final result
    context =>
    {
        var variance = context.SumOfSquares - context.Sum * context.Sum / context.Count;

        return Math.Sqrt(variance / context.Count);
    });

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT stdev(gpa)
    FROM student
";
var stdDev = command.ExecuteScalar();

Erreurs

Si une fonction définie par l’utilisateur lève une exception, le message est retourné à SQLite. SQLite génère alors une erreur, et Microsoft.Data.Sqlite lève une SqliteException. Pour plus d’informations, consultez Erreurs liées aux bases de données.

Par défaut, le code d’erreur SQLite est SQLITE_ERROR (ou 1). Vous pouvez toutefois le modifier : pour cela, levez une SqliteException dans votre fonction en spécifiant le SqliteErrorCode souhaité.

Débogage

SQLite appelle directement votre implémentation. Cela vous permet d’ajouter des points d’arrêt qui se déclenchent pendant que SQLite évalue les requêtes. L’expérience de débogage .NET complète est disponible pour vous aider à créer vos fonctions définies par l’utilisateur.

Partager via