Пользовательские функции

  • Статья

Большинство баз данных имеют процедурный диалект SQL, который можно использовать для определения собственных функций. SQLite, однако, работает в процессе с приложением. Вместо того чтобы изучать новый диалект SQL, можно просто использовать язык программирования приложения.

Скалярные функции

Скалярные функции возвращают одно скалярное значение для каждой строки в запросе. Определяйте новые скалярные функции и переопределяйте встроенные, используя CreateFunction.

Раздел Типы данных содержит список поддерживаемых параметров и возвращаемых типов для аргумента func.

При указании аргумента state это значение будет передаваться в каждый вызов функции. Используйте этот способ, чтобы избежать завершений.

Укажите isDeterministic, если функция является детерминированной, чтобы разрешить SQLite использовать дополнительные оптимизации при компиляции запросов.

В следующем примере показано, как добавить скалярную функцию для вычисления радиуса цилиндра.

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

Операторы

Следующие операторы SQLite реализуются соответствующими скалярными функциями. Определение этих скалярных функций в приложении будет переопределять поведение этих операторов.

Оператор Function
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)

В следующем примере показано, как определить функцию regexp для включения соответствующего оператора. SQLite не включает реализацию по умолчанию функции 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();

Агрегатные функции

Агрегатные функции возвращают одно агрегированное значение для всех строк в запросе. Определяйте и переопределяйте агрегатные функции с помощью CreateAggregate.

Аргумент seed указывает начальное состояние контекста. Он также используется, чтобы избежать завершений.

Аргумент func вызывается один раз для каждой строки. Используйте контекст для накопления конечного результата. Возвратите контекст. Этот шаблон позволяет контексту быть типом значения или неизменяемым.

Если resultSelector не указано, в качестве результата используется конечное состояние контекста. Это может упростить определение функций, таких как sum и count, для которых требуется только увеличение числа строк и их возврат.

Укажите resultSelector, чтобы вычислить окончательный результат из контекста после прохода по всем строкам.

Раздел Типы данных содержит список поддерживаемых параметров для аргумента func и возвращаемых типов для resultSelector.

Укажите isDeterministic, если функция является детерминированной, чтобы разрешить SQLite использовать дополнительные оптимизации при компиляции запросов.

В следующем примере определяется агрегатная функция для вычисления стандартного отклонения столбца.

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

ошибки

Если определяемая пользователем функция создает исключение, сообщение возвращается SQLite. Затем SQLite вызовет ошибку, а Microsoft.Data.SQLite вызовет SqliteException. Дополнительные сведения см. в статье Ошибки базы данных.

По умолчанию кодом ошибки SQLite будет SQLITE_ERROR (или 1). Но его можно изменить, выполнив SqliteException в функции с указанным нужным SqliteErrorCode.

Отладка

SQLite вызывает вашу реализацию напрямую. Это позволяет добавлять точки останова, которые инициируются, когда SQLite оценивает запросы. Для создания определяемых пользователем функций доступны все возможности отладки .NET.

См. также