ユーザー定義関数

  • [アーティクル]

ほとんどのデータベースには、独自の関数の定義に使用できる SQL 手続き型言語が用意されています。 ただし、SQLite はアプリのインプロセスで実行されます。 新しい SQL 言語を習得する代わりに、アプリのプログラミング言語を使用するだけで済みます。

スカラー関数

スカラー関数では、クエリ内の行ごとに 1 つのスカラー値が返されます。 CreateFunction を使用して、新しいスカラー関数を定義し、組み込みの関数をオーバーライドします。

func 引数でサポートされているパラメーターと戻り値の型の一覧については、「データ型」を参照してください。

state 引数を指定すると、その値が関数のすべての呼び出しに渡されます。 クロージャを避ける場合は、これを使用します。

関数が決定的である場合、クエリのコンパイル時に SQLite で追加の最適化を使用できるようにするには、isDeterministic を指定します。

次の例では、円柱の半径を計算するスカラー関数を追加する方法を示します。

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 演算子は、対応するスカラー関数によって実装されます。 これらのスカラー関数をアプリで定義すると、これらの演算子の動作がオーバーライドされます。

演算子 関数
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();

集計関数

集計関数では、クエリ内のすべての行に対して 1 つの集計値が返されます。 集計関数は CreateAggregate を使用して定義およびオーバーライドします。

seed 引数は、コンテキストの初期状態を指定します。 これは、クロージャを避ける場合にも使用します。

func 引数は、行ごとに 1 回だけ呼び出します。 コンテキストを使用して、最終的な結果を累積します。 コンテキストを返します。 このパターンでは、コンテキストを値の型または変更不可にすることができます。

resultSelector が指定されていない場合、コンテキストの最終的な状態が結果として使用されます。 これにより、sum や count などの関数の定義を単純化でき、行ごとに数値をインクリメントして返すだけで済むようになります。

すべての行を反復処理した後でコンテキストから最終的な結果を計算するには、resultSelector を指定します。

resultSelector の func 引数と戻り値の型でサポートされているパラメーターの型の一覧については、「データ型」を参照してください。

関数が決定的である場合、クエリのコンパイル時に SQLite で追加の最適化を使用できるようにするには、isDeterministic を指定します。

次の例では、列の標準偏差を計算する集計関数を定義します。

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) になります。 ただしこれは、目的の SqliteErrorCode を指定して関数内で SqliteException をスローすることによって変更できます。

デバッグ

SQLite では、実装が直接呼び出されます。 これにより、SQLite でクエリが評価されている間にトリガーされるブレークポイントを追加できます。 ユーザー定義関数の作成に役立つ、.NET の一連のデバッグ機能を利用できます。

関連項目