Funzioni definite dall'utente

Articolo
04/01/2024

La maggior parte dei database dispone di un dialetto procedurale di SQL che si può usare per definire funzioni personalizzate. SQLite, tuttavia, viene eseguito in-process con l'app. Invece di dover imparare un nuovo dialetto di SQL, è sufficiente usare il linguaggio di programmazione dell'app.

Funzioni scalari

Le funzioni scalari restituiscono un singolo valore scalare per ogni riga in una query. Definire nuove funzioni scalari ed eseguire l'override di quelle predefinite usando CreateFunction.

Vedere Tipi di dati per un elenco dei parametri supportati e dei tipi restituiti per l'argomento func.

Se si specifica l'argomento state, tale valore verrà passato a ogni chiamata della funzione. Usare questa opzione per evitare le chiuse.

Specificare isDeterministic se la funzione è deterministica per consentire a SQLite di usare ottimizzazioni aggiuntive durante la compilazione di query.

L'esempio seguente mostra come aggiungere una funzione scalare per calcolare il raggio di un cilindro.

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
";

Operatori

Gli operatori di SQLite seguenti vengono implementati dalle funzioni scalari corrispondenti. La definizione di queste funzioni scalari nell'app sostituirà il comportamento di questi operatori.

Operatore	Funzione
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'esempio seguente mostra come definire la funzione regexp per abilitare l'operatore corrispondente. SQLite non include un'implementazione predefinita della funzione 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();

Funzioni di aggregazione

Le funzioni di aggregazione restituiscono un singolo valore aggregato per tutte le righe di una query. Definire ed eseguire l'override delle funzioni di aggregazione usando CreateAggregate.

L'argomento seed specifica lo stato iniziale del contesto. Usare questa opzione anche per evitare le chiusure.

L'argomento func viene richiamato una volta per riga. Usare il contesto per accumulare un risultato finale. Restituisce il contesto. Questo modello consente al contesto di essere un tipo di valore o non modificabile.

Se non si specifica alcun resultSelector, come risultato viene usato lo stato finale del contesto. In questo modo è possibile semplificare la definizione di funzioni come somma e conteggio che devono solo incrementare un numero a ogni riga e restituirlo.

Specificare resultSelector per calcolare il risultato finale dal contesto dopo l'iterazione di tutte le righe.

Vedere Tipi di dati per un elenco dei tipi di parametri supportati per l'argomento func e dei tipi restituiti per resultSelector.

Se la funzione è deterministica, specificare isDeterministic per consentire a SQLite di usare ottimizzazioni aggiuntive durante la compilazione di query.

L'esempio seguente definisce una funzione di aggregazione per calcolare la deviazione standard di una colonna.

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();

Errori

Se una funzione definita dall'utente genera un'eccezione, il messaggio viene restituito a SQLite. SQLite genererà un errore e Microsoft.Data.Sqlite genererà una SqliteException. Per altre informazioni, vedere Errori del database.

Per impostazione predefinita, il codice di errore SQLite verrà SQLITE_ERROR (o 1). È tuttavia possibile modificarlo generando un'eccezione SqliteException nella funzione con l'oggetto SqliteErrorCode specificato desiderato.

Debug

SQLite chiama direttamente l'implementazione. In questo modo è possibile aggiungere punti di interruzione che si attivano mentre SQLite sta valutando le query. L'esperienza di debug .NET completa è disponibile per creare le funzioni definite dall'utente.

Condividi tramite