Windows 10 & UWPにおけるSQLiteの扱い方Tips（1）

Microsoft.Data.Sqlite名前空間

　UWPアプリケーションで簡単なリレーショナルデータベースを使いたいときはSQLiteを使うのが易しいです。Nugetパッケージでも管理がされていて、Microsoft.Data.Sqlite名前空間で利用ができます。

ただ、SQLiteって軽量であって信頼性が高いことを念頭に置いて開発されているためか、SQLの拡張的構文に対してなどは保守的で、時刻情報もプリミティブデータとしては格納されておらず、文字列管理になっているのですね。ただ、DBへの書き込みと取得（INSERTとSELECT）については、時刻・時間用の関数が用意されており、そちらを使って書き込みと取得を行うことで機能を補っているようです。例えば、NOTIFICATIONと言うテーブルを作成して、プライマリキーを時刻Timeにしたとき、データの挿入と取得はdatetime関数などを使って行うことになります（ここではlocaltimeにしてしまっていますが、デフォルトはUTCで格納されます。この辺は各所の方が詳しいでしょう）。

CREATE TABLE NOTIFICATION (Time TEXT NOT NULL PRIMARY KEY,Level INTEGER NOT NULL,Name TEXT NOT NULL,Description TEXT NOT NULL)
INSERT INTO NOTIFICATION VALUES(datetime('now', 'localtime'), 10, "test", "This is a test.");
SELECT datetime(Time) FROM NOTIFICATION;

　このような時間処理をUWP C# .NET環境のMicrosoft.Data.Sqliteでやりたかったのですが、しょうもないところでハマったので一応書いておきます。

Microsoft.Data.Sqliteではデータの変換は内部的に勝手に行う

　親切な設計なのかは分かりませんが、Microsoft.Data.SqliteではSQLiteがプリミティブでもっていないデータ型からのデータ投入については自動的に変換かけてくれます。一方で取得するときはQueryReaderクラスにGetDateTimeメソッドがあるため、これを経由するとDateTime型にしてくれます。なんか今ひとつ対称性がないので（特に挿入側）勘違いしやすいですが…そういう設計です。というのに気づくのに2時間くらい掛かりました。

　つまり、以下のコードの場合、SqliteCommandクラスを作ったとき、コマンド文字列にdatetime()を含める必要はありませんということです。ついうっかり”INSERT INTO NOTIFICATION VALUES(datetime(” + “\”@TIME\”, ‘localtime’)…”みたいに頑張ってしまうんですが、これをやるとSqliteException: Syntax Error例外をいただいて悲しむだけなので。

        public bool InsertNotification(Domain.Notification notif)
        {
            bool ret;

            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, db_path);
            using (SqliteConnection conn = new SqliteConnection($"Filename={dbpath}"))
            {
                conn.Open();

                SqliteCommand comm = new SqliteCommand("INSERT INTO NOTIFICATION VALUES (" +
                    "@TIME, @Level, @Name, @Desc)");
                comm.Parameters.AddWithValue("@TIME", DateTime.Now);
                comm.Parameters.AddWithValue("@Level", (int)notif.Type);
                comm.Parameters.AddWithValue("@Name", notif.Function);
                comm.Parameters.AddWithValue("@Desc", notif.Description);
                comm.Connection = conn;
                int ins = comm.ExecuteNonQuery();
                if (ins == 1)
                {
                    ret = true;
                }
                else
                {
                    ret = false;
                }
            }
            return ret;
        }
    }

　取得する側もあんまり考えすぎないで

        public List<Domain.Notification> GetNotifications()
        {
            List<Domain.Notification> list = new List<Domain.Notification>();

            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, db_path);
            using (SqliteConnection conn = new SqliteConnection($"Filename={dbpath}"))
            {
                conn.Open();
                SqliteCommand comm = new SqliteCommand("SELECT datetime(Time), Level, Name, Description FROM NOTIFICATION");
                comm.Connection = conn;
                SqliteDataReader query = comm.ExecuteReader();
                while(query.Read())
                {
                    Domain.Notification notif = new Domain.Notification(
                        query.GetDateTime(0), 
                        (Domain.Notification.MessageType)Enum.ToObject(typeof(Domain.Notification.MessageType), query.GetInt16(1)), query.GetString(2), query.GetString(3));
                    list.Add(notif);
                }
            }
            return list;
        }

　みたいなので良いです。