vaguely

和歌山に戻りました。ふらふらと色々なものに手を出す毎日。

【C#】 SQLite-net (sqlite-net-pcl) を試す

はじめに

この記事は C# Advent Calendar 2020 - Qiita の三日目の記事です。

いつもの諸事情により SQLite を扱うことになりました。

条件はこんな感じ。

  1. .NET, Mono で SQLite を扱うことができる
  2. Unity でも動く
  3. 今も更新されている

条件 2.がなければ EntityFramework Core 一択という気持ちはあるのですが、今回は SQLite-net を使うことにしました。

なお、以下のサンプルは .NET 5 のコンソールアプリですが、Unityでも(インストール以外は)同じように使える。。。はずです。

Environments

  • .NET ver.5.0.100
  • sqlite-net-pcl ver.1.7.335
  • Microsoft.Extensions.DependencyInjection ver.5.0.0

準備

インストール

README に従って、 NuGet が使える環境では NuGet で、Unity のように NuGet が使えない場合は SQLite.cs をプロジェクトに追加します。

DI を追加する

ありがたいことに .NET Core 、 .NET 5 ではこの通り コンソールアプリでも DI が使えるので、「Microsoft.Extensions.DependencyInjection」を NuGet で追加して、 DI を使えるようにします。

Program.cs

using System;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using SqliteSample.Controllers;

namespace SqliteSample
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var servicesProvider = BuildDi();
            using (servicesProvider as IDisposable)
            {
                var mainController = servicesProvider.GetRequiredService<MainController>();
                await mainController.StartAsync();
            }
        }
        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddTransient<MainController>();
            return services.BuildServiceProvider();
        }
    }
}

Database の生成と接続

sqlite-net-pcl は Syncronous API と Asynchronous API の二つの API を持っており、それぞれ専用のクラスがあります。

今回は Asynchronous API の方を試してみます。(Unity の場合は Syncronous API の方が良いと思いますが)

で、まず Database ファイルが存在しない場合は新規作成、存在する場合はそのまま使うようにしてみます。

DbContext.cs

using System;
using System.IO;
using System.Threading.Tasks;
using SQLite;

namespace SqliteSample.Models
{
    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "Databases/memory.db");
            // if the database file isn't exist, a new database file will be created.
            this.db = new SQLiteAsyncConnection(path,
                SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.ReadWrite );
        }
    }
}

参照専用のデータを持つテーブルがある場合、「SQLiteAsyncConnection」実行より前にファイルの有無を調べて、ない場合のみデータ追加、といった処理を行うと良さそうです。

Entity Framework Core と同じように使いたいため、このクラスを DI で依存クラスに挿入できるようにしておきます。

Program.cs

using System;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using SqliteSample.Controllers;
using SqliteSample.Models;

namespace SqliteSample
{
    class Program
    {
...
        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddTransient<MainController>();
            services.AddScoped<DbContext>();            
            return services.BuildServiceProvider();
        }
    }
}

テーブル生成とマイグレーション

次はテーブルの生成とマイグレーションです。

こちらも先ほどと同じような感じで、テーブルが存在しなければ作成、存在すればそれを使う、という処理を自動で行ってくれます。

なおかつテーブルに変更があった場合はそのマイグレーションも自動で行ってくれます。

Author.cs

using SQLite;

namespace SqliteSample.Models
{
    [Table("author")]
    public class Author
    {
        [PrimaryKey, AutoIncrement]
        [Column("id")]
        [NotNull]
        public int Id{ get; set; }
        [Column("name")]
        [NotNull]
        public string Name { get; set; } = "";
    }
}

Book.cs

using SQLite;

namespace SqliteSample.Models
{
    [Table("book")]
    public class Book
    {
        [PrimaryKey, AutoIncrement]
        [Column("id")]
        [NotNull]
        public int Id{ get; set; }
        [Column("name")]
        [NotNull]
        public string Name { get; set; } = "";
        public int AuthorId{get; set; }
    }
}

なお、「SQLiteAsyncConnection」を使う場合、テーブル生成もすべて非同期となります。

DbContext.cs

    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
...
        }
        public async Task InitAsync()
        {
            await db.CreateTableAsync<Book>();
            await db.CreateTableAsync<Author>();
        }
    }
}

コンストラクタで呼べないので、「Program.cs」から実行することにします。

Program.cs

...
    class Program
    {
        static async Task Main(string[] args)
        {
            var servicesProvider = BuildDi();
            using (servicesProvider as IDisposable)
            {
                var dbContext = servicesProvider.GetRequiredService<DbContext>();
                await dbContext.InitAsync();
                
                var mainController = servicesProvider.GetRequiredService<MainController>();
                await mainController.StartAsync();
            }
        }
...

マイグレーションの注意点

カラムの追加については問題なく行えるのですが、リネーム、削除については注意が必要です。

というのも、リネームすると別カラムとして追加され、1.カラムにデータ追加 -> 2.削除 -> 3.同じ名前でカラム追加 とすると1.のデータが復活します。

ということで、基本的にリネームや削除が必要な場合、中のデータを引き上げて一旦削除 -> 再作成して登録、とする必要がありそうです。

外部キーの設定

結果から言うと、少なくとも .NET 5 環境 (sqlite-net-pcl 環境?)で外部キーを扱うのは難しそうです。

もともと sqlite-net-pcl は該当の機能を持っておらず、「OneToMany」や「ManyToOne」を追加できる「SQLiteNetExtensions」を試したところ、ビルド時にエラーとなりました。

まぁそもそも PostgreSQL などで行うような複雑な構造(いやそれが PostgreSQL なら良いかは別として)を SQLite で持つべきではない気がするので、下手に頑張るよりシンプルさを心がける方が良いかもしれません。

テーブルにアクセスする

EntityFramework Core ぽく扱えるようにするため、 DbContext クラスにもう少し追加します。

DbContext.cs (Full)

using System;
using System.IO;
using System.Threading.Tasks;
using SQLite;

namespace SqliteSample.Models
{
    public class DbContext: IDisposable
    {
        private readonly SQLiteAsyncConnection db;
        public DbContext()
        {
            var path = Path.Combine(Directory.GetCurrentDirectory(), "Databases/memory.db");
            // if the database file isn't exist, a new database file will be created.
            this.db = new SQLiteAsyncConnection(path,
                SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex | SQLiteOpenFlags.ReadWrite );
        }
        public async Task InitAsync()
        {
            await db.CreateTableAsync<Book>();
            await db.CreateTableAsync<Author>();
        }
        public async void Dispose()
        {
            await db.CloseAsync();
        }
        // TransactionやCreate, Update, Delete はこちらを使う
        public SQLiteAsyncConnection Database => db;
        // Readはこちらから
        public AsyncTableQuery<Book> Books => db.Table<Book>();
        public AsyncTableQuery<Author> Authors => db.Table<Author>();
    }
}

。。。ほとんど「SQLiteAsyncConnection」を渡して何とかするみたいになってしまいますが、まぁ雰囲気で。

とにあれデータを追加して検索してみます。

MainController.cs (Full)

using System;
using System.Threading.Tasks;
using SqliteSample.Models;

namespace SqliteSample.Controllers
{
    public class MainController
    {
        private readonly DbContext db;
        public MainController(DbContext db)
        {
            this.db = db;
        }
        public async Task StartAsync()
        {
            await AddSamplesAsync();
            // maybe I can't get the result as IEnumerable<Book>
            foreach(var book in (await db.Books.Where(b => b.Id > 0).ToArrayAsync()))
            {
                Console.WriteLine($"Book ID: {book.Id} Name: {book.Name} AuthorId: {book.AuthorId}");
            }
        }
        private async Task AddSamplesAsync()
        {
            var author = new Author
            {
                Name = "Ian Griffiths"
            };
            await db.Database.RunInTransactionAsync(connection => {
                connection.Insert(author);
                connection.Insert(new Book
                {
                    Name = "Programming C# 8.0",
                    AuthorId = author.Id,
                });
            });
        }
    }
}

おわりに

多少制限はあるものの、想像以上に便利に使えるライブラリが存在してくれていることに感謝。

特に Unity でもクラス一つ追加するだけで使えるというのは本当にありがたい。

あえて難点を挙げるとすればググりにくいことw

ともあれ、今後も状況に合わせて Entity Framework Core と使い分けていきたいと思います。

明日は hiro_matsuno2 さんです。

よろしくお願いいたします(..)_。