LoginSignup
8
9

More than 1 year has passed since last update.

@yamaokunousausa(うさうさ)

C#でもOuteside/2-way SQLしたい

Last updated at Posted at 2019-09-21

TL;DR

.NET用の2-way SQLライブラリを作ってみた系の記事だよ。

対象読者

  • .NETでもOuteside/2-way SQLしたい人
  • ビルドタイムコード生成の手法に興味のある人

動機(立派な建前)

まず、データベースに対するクエリの構築にどんな手法を用いるかについて考えてみます。

  • データベースの機能/性能を最大限に生かすためには、オブジェクトのトラッキング機能等は不要
  • RDBへのAPIが文字列(SQL)ベースな以上、文字列構築の表現力の確保がポイント
  • LINQ的なType safeなビルダーには表現力の限界がある
  • StringBuilder等を使った文字列操作だとSQLの検証がしにくい
  • SQLが外部化されれば検証は行いやすい

っというわけで、.NET以外の世界ではOuteside/2-way SQLなライブラリがよく使用されていたりもすると思います。
.NETの世界でも、自分達だけで使うものを個々に作っている例は観測できますが、定番品というのはないように思います。

以上のような背景を踏まえ、自分も作ってみることにしました。
どうせなら、自分なりに偏った方針でやってみたろ、っということで、以下のような方針で実装をしてみました。

  • 2-way SQLというかOutside SQLが主目的
  • Outside SQLにするためには2-way SQLになるという形
  • データアクセス用interfaceのソースとSQLファイルを用意して、そこから実装を生成するタイプのライブラリ
  • コードコメント式の評価など、無駄な実行コストをなるべく少なくする
  • 逆転の発想、式を実行時に評価するのではなく、式の記述にC#のコードを使用して、それを元に式の処理はビルドタイムで実装を生成

作ったもの

使用例

以下のようなinterfaceとSQLファイルを用意します。

IExampleAccessor.cs
using System.Collections.Generic;

using Smart.Data.Accessor.Attributes;

[DataAccessor]
public interface IExampleAccessor
{
    [Execute]
    void Create();

    [Insert]
    void Insert(DataEntity entity);

    [Query]
    List<DataEntity> QueryDataList(string type = null, string order = null);
}
IExampleAccessor.Create.sql
CREATE TABLE IF NOT EXISTS Data (Id int PRIMARY KEY, Name text, Type text)
IExampleAccessor.QueryDataList.sql
SELECT * FROM Data
/*% if (!String.IsNullOrEmpty(type)) { */
WHERE Type = /*@ type */'A'
/*% } */
/*% if (!String.IsNullOrEmpty(order)) { */
ORDER BY /*# order */Id
/*% } */

SQLファイルのは名称はクラス名+メソッド名ベースで、interfaceのソースファイルがあるフォルダのSqlサブフォルダ下に配置します。
なお、InsertメソッドについてはSQLファイルがありませんが、これはInsertAttributeが定型SQLを自動生成するためです。

あと、クエリの実行結果用の入れ物を用意しておきます。

public class DataEntity
{
    public long Id { get; set; }

    public string Name { get; set; }

    public string Type { get; set; }
}

使用例は以下のようになります。

using System;
using System.IO;

using Microsoft.Data.Sqlite;

using Smart.Data;
using Smart.Data.Accessor;
using Smart.Data.Accessor.Engine;

public static class Program
{
    public static void Main()
    {
        // Initialize
        var engine = new ExecuteEngineConfig()
            .ConfigureComponents(c => c.Add<IDbProvider>(new DelegateDbProvider(() => new SqliteConnection("Data Source=test.db")))
            .ToEngine();
        var factory = new DataAccessorFactory(engine);

        // Create data accessor
        var dao = factory.Create<IExampleAccessor>();

        // Create
        dao.Create();

        // Insert
        dao.Insert(new DataEntity { Id = 1L, Name = "Data-1", Type = "A" });
        dao.Insert(new DataEntity { Id = 2L, Name = "Data-2", Type = "B" });
        dao.Insert(new DataEntity { Id = 3L, Name = "Data-3", Type = "A" });

        // Query
        var typeA = dao.QueryDataList("A");
        var all = dao.QueryDataList();
        var ordered = dao.QueryDataList(order: "Name DESC");
    }
}

2-way SQL

以下の種類のコメントをサポートしています。

種類 例
@ パラメータ /*@ id */
# 生パラメータ /*# order #/
% コードブロック /*% if (!String.IsNullOrEmpty(name)) { */
! プラグマ /*!using System.Text */

パラメータ

SELECT * FROM Data WHERE Id = /*@ id */1

IList型のIN句への展開にも対応しています。

生パラメータ

SELECT * FROM Data ORDER BY /*# order */Id

生パラメータ部分は、C#の引数がそのままSQLの文字列として展開されます。

コードブロック

SELECT * FROM Data
/*% if (IsNotNull(id)) { */
WHERE Id >= /*@ id */0
/*% } */

コードブロックはC#で記述します。

プラグマ

コードブロックで使用するC#のコード用に以下のようなプラグマを用意しています。

/*!using System.Text */

/*!using */を使用するとその名前空間のクラスが使用できるようになります。
標準ではSystemとSystem.Linq名前空間のクラスが使用できるようになっています。

/*!helper MyLibrary.CustomScriptHelper */

/*!using */を使用するとusing staticの意味になります。
コードブロックの記述が行いやすいように、ヘルパークラスのインポートに使用します。

標準で以下のヘルパークラスがビルトインされています。

public static class ScriptHelper
{
    public static bool IsNull(object value);

    public static bool IsNotNull(object value);

    public static bool IsEmpty(string value);

    public static bool IsNotEmpty(string value);

    public static bool Any(Array array);

    public static bool Any(ICollection ic);
}

機能

基本機能

DataAccessorAttributeを付加したinterfaceが自動生成の対象となり、メソッドに付加した属性により処理の実装が構築されます。

メソッドの属性としては以下のものを用意しています。

[DataAccessor]
public interface ISampleAccessor
{
    [Execute]
    int Execute();

    [ExecuteScalar]
    long Count();

    [ExecuteReader]
    IDataReader Enumerate();

    [QueryFirstOrDefault]
    DataEntity QueryData(long id);

    [Query]
    IList<DataEntity> QueryBufferd();

    [Query]
    IEnumerable<DataEntity> QueryNonBufferd();
}

QueryAttributeは戻り値がListの場合は即時評価、IEnumerableの場合は遅延評価になります。

非同期サポート

メソッドの戻り値がTaskの場合、処理は非同期処理として構築されます。

[DataAccessor]
public interface IAsyncAccessor
{
    [Execute]
    ValueTask<int> ExecuteAsync();
}

また、メソッドの引数にCancellationTokenを追加すると、非同期処理内ではそのCancellationTokenが使用されます。

[DataAccessor]
public interface ICancelableAccessor
{
    [Execute]
    ValueTask<int> ExecuteAsync(CancellationToken cancel);
}

データベースコネクション

処理で使用されるコネクションは設定で登録されたIDbProviderが使用されます。

var engine = new ExecuteEngineConfig()
    .ConfigureComponents(c => c.Add<IDbProvider>(new DelegateDbProvider(() => new SqlConnection(ConnectionString))))
    .ToEngine();

以下のようにIDbProviderSelectorを登録することで、複数のコネクションを使い分けることも可能です。

config.ConfigureComponents(c =>
{
    var selector = new NamedDbProviderSelector();
    selector.AddProvider("Main", new DelegateDbProvider(() => new SqlConnection(MainConnectionString)));
    selector.AddProvider("Sub", new DelegateDbProvider(() => new SqlConnection(SubConnectionString)));
    c.Add<IDbProviderSelector>(selector);
});

どのIDbProviderを使用するかはProviderAttributeで指定します

[DataAccessor]
[Provider("Primary")]
public interface IPrimaryAccessor
{
...
}

また、メソッドの引数にDbConnectionを追加すると、そのコネクションが使用されるようになります。

[DataAccessor]
public interface IDbConnectionAccessor
{
    [Execute]
    int Execute(DbConnection con);
}

同様にDbTransactionを追加すると、そのトランザクション(コネクションはトランザクションのコネクション)が使用されます。

[DataAccessor]
public interface ITransactionAccessor
{
    [Execute]
    int Execute(DbTransaction tx);
}

その他特殊引数

TimeoutAttribute付きの引数を追加すると、CommandTimeoutにその値が設定されます。

[DataAccessor]
public interface ITimeoutAccessor
{
    [Execute]
    int Execute([Timeout] int timeout);
}

引数ではなくメソッドにCommandTimeoutAttributeを付加することで固定値の設定もできます。

[DataAccessor]
public interface ICommandTimeoutAccessor
{
    [Execute]
    [CommandTimeout(30000)]
    int Execute();
}

マッピング

クエリ結果のオブジェクトへのマッピングについて、標準の実装ではオブジェクトの名称が一致(完全一致を優先)するプロパティに対して値が設定されます。

プロパティ単位でルールを変更したい場合にはNameAttributeを付加します。
また、マッピング対象にしたくない属性にはIgnoreAttributeを付加します。

public class DataEntity
{
    [Name("USER_NAME")]
    public string UserName { get; set; }

    [Ignore]
    public int IgnoreMember { get; set; }
}

なお、対象となるプロパティを選択するIPropertySelectorの実装を差し替えることで、名称のマッチングルール自体を変更することも可能です。

また、IResultMapperFactoryの実装を用意することで、クエリ結果の処理自体のカスタマイズも可能になっています。

public interface IResultMapperFactory
{
    bool IsMatch(Type type);

    Func<IDataRecord, T> CreateMapper<T>(IResultMapperCreateContext context, Type type, ColumnInfo[] columns);
}

config.ConfigureResultMapperFactories(mappers => mappers.Add(new CustomResultMapperFactory));

パラメータ/戻り値の型ハンドル

ParameterBuilderAttribute派生クラスを使用することでDbParameterに設定されるプロパティをカスタマイズすることができます。

標準ではDbType.AnsiString/DbType.AnsiStringFixedLengthを設定するAnsiStringAttribute等を用意しています。

[DataAccessor]
public interface IAnsiStringAccessor
{
    [QueryFirstOrDefault]
    DataEntity QueryEntity([AnsiString(3)] string code);
}

また、以下のように型に対する標準ルールの変更も可能です。

config.ConfigureTypeMap(map => map[typeof(string)] = DbType.AnsiString);

実行結果の型の変換についてはResultParserAttribute派生クラスを使用することカスタマイズが可能です。

public sealed class CustomParserAttribute : ResultParserAttribute
{
    public override Func<object, object> CreateParser(IServiceProvider serviceProvider, Type type)
    {
        return x => Convert.ChangeType(x, type, CultureInfo.InvariantCulture);
    }
}

public class ParserEntity
{
    [CustomParser]
    public long Value { get; set; }
}

また、ITypeHandlerの実装を登録しておくことでDbParameterの作成と実行結果の型変換の標準ルールを変更することもできます。

public sealed class DateTimeTickTypeHandler : ITypeHandler
{
    public void SetValue(DbParameter parameter, object value)
    {
        parameter.DbType = DbType.Int64;
        parameter.Value = ((DateTime)value).Ticks;
    }

    public Func<object, object> CreateParse(Type type)
    {
        return x => new DateTime((long)x);
    }
}

config.ConfigureTypeHandlers(handlers => handlers[typeof(DateTime)] = new DateTimeTickTypeHandler());

なお、現在の実装の制限事項として、パラメータ/戻り値の型ハンドルが適用されるのは、メソッドの引数またはメソッドの引数のプロパティまでとなっています。

ストアドプロシージャ

ProcedureAttributeをメソッドに指定すると、ストアドプロシージャの実行ができます。

ProcedureAttributeではSQLファイルは不要です。

引数をrefやoutにすることでストアドプロシージャのIN/OUTに対応できます。
また、InputAttribute等をプロパティに付加したパラメータクラスを使用することもできます。

CREATE PROCEDURE PROC1
    @param1 INT,
    @param2 INT OUTPUT,
    @param3 INT OUTPUT
AS
BEGIN
    SELECT @param2 = @param2 + 1
    SELECT @param3 = @param1 + 1
    RETURN 100
END

public class Parameter
{
    [Input]
    [Name("param1")]
    public int Parameter1 { get; set; }

    [InputOutput]
    [Name("param2")]
    public int Parameter2 { get; set; }

    [Output]
    [Name("param3")]
    public int Parameter3 { get; set; }

    [ReturnValue]
    public int ReturnValue { get; set; }
}

[DataAccessor]
public interface IProcedureAccessor
{
    [Procedure("PROC1")]
    int Execute(int param1, ref int param2, out int param3);

    [Procedure("PROC1")]
    void Execute(Parameter parameter);
}

SQLビルダー属性

Outside SQLを使うとしても、INSERT文などの定型文まで全てSQLファイルを用意するのは面倒くさいです。
そこで、定型SQLを自動生成する仕組みを用意しています。

MethodAttribute派生クラスを用意し、GetNodes()メソッドで以下のような実装を行うことでMethodInfoを元にしたSQLの自動生成が可能です。

public override IReadOnlyList<INode> GetNodes(ISqlLoader loader, IGeneratorOption option, MethodInfo mi)
{
    var sql = new StringBuilder();

    // MethodInfoを元にSQLを構築

    var tokenizer = new SqlTokenizer(sql.ToString());
    var builder = new NodeBuilder(tokenizer.Tokenize());
    return builder.Build();
}

このような形で実装したMethodAttribute派生クラスは、メソッドに対して以下のように適用するだけでよく、SQLファイルの作成は不要となります。

[DataAccessor]
public interface IExampleAccessor
{
    [Insert]
    void Insert(DataEntity entity);
}

なお、標準では基本処理としてInsertAttribute、CountAttribute、SelectAttribute、SelectSingleAttribute等を用意してあります。
また、対象DBMS毎の方言に対応したUPSERT等の実装もオプションで用意してあります。

ASP.NET Coreのサポート

以下のように使用できるMicrosoft.Extensions.DependencyInjection連携機能も用意しています。

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

    // Use data accessor
    services.AddSingleton<IDbProvider>(new DelegateDbProvider(() => new SqliteConnection("Data Source=test.db")));
    services.AddDataAccessor(config =>
    {
        config.AccessorAssemblies.Add(Assembly.GetExecutingAssembly());
    });
}

public class HomeController : Controller
{
    private readonly ISampleAccessor sampleAccessor;

    public HomeController(ISampleAccessor sampleAccessor)
    {
        this.sampleAccessor = sampleAccessor;
    }

...
}

自前Resolverとの連携機能

Microsoft.Extensions.DependencyInjectionを使用する代わりに自前のResolverとの連携機能も用意しています。

自前Resolverとの連携機能を使用して、複数のデータソースを扱う場合の例は以下のようになります。

public void ConfigureContainer(ResolverConfig config)
{
    config.UseDataAccessor();
    config
        .Bind<IDbProvider>()
        .ToConstant(new DelegateDbProvider(() => new SqliteConnection("Data Source=primary.db")))
        .Named(DataSource.Primary);
    config
        .Bind<IDbProvider>()
        .ToConstant(new DelegateDbProvider(() => new SqliteConnection("Data Source=secondary.db")))
        .Named(DataSource.Secondary);
}

自前Resolverとの連携を使用する場合のメリットは以下になります。

  • 標準DependencyInjectionではサポートされないNamedをサポートするので、複数のデータソースの扱いが楽
  • コンポーネントの得録情報がResolverにない場合に、動的に登録情報を生成するIMissingHandlerという機構があるため、標準DependencyInjectionでAddDataAccessor()しているような事前のコンポーネント登録が不要

今後の課題

  • 現状、コードの自動生成においてメタ情報収集にリフレクションAPIを使用しており、ビルド環境への対応にバギーな部分がある点の改善(リフレクション以外の手段によるメタデータの収集？)
  • 引数の型取得を自前で実装しているため、引数/戻り値の型ハンドルに制限事項があるため、Roslynを使用することで制限をなくしたい
  • クエリ結果のマッピングについて、単純なオブジェクトのプロパティのみへの対応のみなので、コンストラクタ引数やタプルへの対応等も検討

うさコメ

まあ、まだ実験的な面の強いライブラリです(´・ω・`)
実用的かどうかはこれから検証してみるぽ。

それと、このライブラリのコード生成に使用している仕組みについてもまとめておきたいです。
っというか、この記事はその前段階として書いてみたものだったりして( ˙ω˙)

立派な建前はさておき、このライブラリを作ってみることにした出発点の一つとして、コード生成のメタ情報取得にRoslyn(SemanticModel)やMono.CecilのAPIを覚えるのも面倒くさいから、使い慣れたリフレクションAPIを使えないかな？、っというのがあったりするんですが。
その他、ビルド時の成果物生成のカスタマイズ方法について、調査した内容がコード生成用ビルド設定の基盤になっていたり、Dapperの代わりにXamarin.Formsで使う事を目的として用意したMicro-ORMの応用だったりだとか、色々な調査や応用のまとめとして生まれたのがこのライブラリなのでした。

8
9
9

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
9