Funções definidas pelo usuário

Artigo
05/10/2023

A maioria dos bancos de dados tem um dialeto processual do SQL que você pode usar para definir suas próprias funções. No entanto, o SQLite é executado em processo com seu aplicativo. Em vez de ter que aprender um novo dialeto do SQL, basta usar a linguagem de programação do seu aplicativo.

Funções escalares

As funções escalares retornam um único valor escalar para cada linha em uma consulta. Defina novas funções escalares e substitua as internas usando CreateFunction.

Consulte os tipos de dados para obter parâmetros com suporte e tipos de retorno para o argumento func.

Especificar o argumento state passará esse valor para cada invocação da função. Use isso para evitar fechamentos.

Especifique isDeterministic se sua função for determinística para permitir que o SQLite use otimizações adicionais ao compilar consultas.

O exemplo a seguir mostra como adicionar uma função escalar para calcular o raio de um 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
";

Operadores

Os seguintes operadores SQLite são implementados por funções escalares correspondentes. Definir essas funções escalares em seu aplicativo substituirá o comportamento desses operadores.

Operador	Função
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)

O exemplo a seguir mostra como definir a função regexp para habilitar seu operador correspondente. O SQLite não inclui uma implementação padrão da função 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();

Funções de agregação

As funções de agregação retornam um único valor agregado para todas as linhas em uma consulta. Definir e substituir funções de agregação usando CreateAggregate.

O argumento seed especifica o estado inicial do contexto. Use isso também para evitar fechamentos.

O argumento func é invocado uma vez por linha. Use o contexto para acumular um resultado final. Retorne o contexto. Esse padrão permite que o contexto seja um tipo de valor ou imutável.

Se não for especificado resultSelector, o estado final do contexto será usado como resultado. Isso pode simplificar a definição de funções como soma e contagem que só precisam incrementar um número em cada linha e devolvê-lo.

Especifique resultSelector para calcular o resultado final a partir do contexto depois de iterar em todas as linhas.

Consulte os Tipos de dados para obter uma lista de parâmetros com suporte para o argumento func e tipos de retorno para resultSelector.

Especifique isDeterministic se sua função for determinística para permitir que o SQLite use otimizações adicionais ao compilar consultas.

O exemplo a seguir define uma função de agregação para calcular o desvio padrão de uma coluna.

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

Errors

Se uma função definida pelo usuário gerar uma exceção, a mensagem será retornada ao SQLite. O SQLite gerará um erro e o Microsoft.Data.Sqlite lançará uma SqliteException. Para obter mais informações, consulte Erros de banco de dados.

Por padrão, o código do erro SQLite será SQLITE_ERROR (ou 1). No entanto, você pode alterá-lo lançando uma função SqliteException com o SqliteErrorCode desejado especificado.

Depuração

O SQLite chama sua implementação diretamente. Isso permite adicionar pontos de interrupção que disparam enquanto o SQLite está avaliando consultas. A experiência completa de depuração do .NET está disponível para ajudá-lo a criar suas funções definidas pelo usuário.

Compartilhar via