2017年01月09日

SQLiteを使って環境設定からデータの保存・読み出しまで

前置き

少量のデータを保存するのならオブジェクトをシリアライズしてファイル保存ですみます。大量に扱う必要があるのなら、そのための専用機能であるデータベースソフトを使うのが常道。
ファイル読書で済む程には少なくないけど、データベースの環境構築しサービス管理するほどには大量でもない場合などで候補になるのがSQLiteです。DB立てられないスマホなどでも重宝します。

SQLiteとは
ファイルへの入出力を標準SQLで実行できるライブラリです。以下のような特徴を持ちます。
・起動元アプリケーションのプロセスで実行される。
・他のアセンブリへの依存がない。(※当該アセンブリを.NETから使うためのDLLは必要です)
・構成ファイルや設定など不要
・パブリックドメイン(商用利用を含めて無料で使える)
・書き込みは遅いが読み出しは速い。(ファイルをSSDに配置すれば初回から相当に速い)

前提
データの入出力はSQLにより行うのでSQLの基本とADO.NETの基本を知らないと厳しい
ADO.NETを知っているなら学習コストはほとんど不要

テスト用の画面作成
01.SampleWindow.png
上記のようなテスト用の画面を作ります。WinFromsでもWPFでもなんでも構いません。ボタン配置してハンドラを登録しているだけです。コードビハインド側はこんな感じ。

using System.Windows;

namespace WpfApplication1
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void buttonCreate_Click(object sender, RoutedEventArgs e)
        {
        }

        private void buttonInsert_Click(object sender, RoutedEventArgs e)
        {
        }

        private void buttonSelect_Click(object sender, RoutedEventArgs e)
        {
        }
    }
}

ライブラリをNuGetでインストール

02.PakageManager.png
「メニュー」の「ツール」から「NuGetパッケージマネージャ」を選択し「ソリューションのNuGetパッケージの管理」を選択します。

03.InstallSQLite.png
「SQLite」で検索するといくつか列挙されます。どれでもいいんですが、LINQやEntityFrameworkは今回使わないので、一番基本となる「System.Data.SQLite.Core」を選択してインストールボタンを押せば完了です。


コードを書く
using System.Windows;
using System.Data.SQLite;     // ※これを追加
using System;
using System.Collections.Generic;

namespace WpfApplication1
{
    public partial class MainWindow : Window
    {
        // 接続文字列で指定しているDataSourceがファイル名になります。拡張子はなんでもよし
        // ファイルがあればそれをオープンし、なければ指定ファイル名で作成されます。
        private const string ConnectionString = @"Data Source=Sample.db";

        public MainWindow()
        {
            InitializeComponent();
        }
        // テーブルの作成
        // T_MEMOというテーブルを作成しています。
        // 2回目以降は既にテーブルがあるという例外がスローされます。
        private void buttonCreate_Click(object sender, RoutedEventArgs e)
        {
            using (var con = new SQLiteConnection(ConnectionString))
            {
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE T_MEMO(ID INTEGER PRIMARY KEY AUTOINCREMENT,MEMO TEXT) ";

                    cmd.ExecuteNonQuery();
                }
            }
        }
        // データの挿入
        // パラメタライズドクエリも使えます
        private void buttonInsert_Click(object sender, RoutedEventArgs e)
        {
            using (var con = new SQLiteConnection(ConnectionString))
            {
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO T_MEMO (MEMO) VALUES (@p_memo)";
                    cmd.Parameters.Add(new SQLiteParameter("@p_memo", DateTime.Now.ToLongTimeString()));

                    cmd.ExecuteNonQuery();
                }
            }
        }

       // データの読み出し
        private void buttonSelect_Click(object sender, RoutedEventArgs e)
        {
            var list = new List<string>();

            using (var con = new SQLiteConnection(ConnectionString))
            {
                con.Open();

                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT MEMO FROM T_MEMO";

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(reader.GetString(0));
                        }
                    }
                }
            }

            MessageBox.Show(string.Join(",",list));
        }

        // 非接続型の読み出しも可能です。
        private void buttonSelect2_Click(object sender, RoutedEventArgs e)
        {
            var adapter = new SQLiteDataAdapter("SELECT MEMO FROM T_MEMO", ConnectionString);

            var ds = new DataSet();

            adapter.Fill(ds, "T_MEMO");

            MessageBox.Show(ds.Tables[0].Rows.Count.ToString());
 
        }
    }

まとめ
引っかからなければ10分で全部行けるでしょう。データベースを立てる際の種々の設定や管理などが全く不要となるので簡単です。
posted by RR at 21:04 | Comment(0) | SQLite | このブログの読者になる | 更新情報をチェックする

2017年01月25日

SQLite 概要と環境設定

本家サイト

全てはここから(英語) つ https://www.sqlite.org/

概要

SQLiteは起動元アプリケーション自身のプロセス内で実行されるライブラリであり、他のアセンブリへの依存がなく、サーバーも不要であり、構成ファイルや設定も不要なトランザクション機能をもつSQLデータベースエンジンです。SQLiteのコードはパプリックドメインです。つまり、商用でも個人利用でもその目的を研ハズ無料で使うことが出来ます。SQLiteは著名なプロジェクトを含む世界中のアプリケーションで利用されています

特徴

self-contained他のライブラリにほとんど依存しない
serverless別プロセスのサービスなどは不要で、起動元アプリのプロセス内で実行
zero-configurationインストールとかセットアップだとか面倒なし
transactionalACID(原子性・一貫性・独立性・永続性)特性あり
public domain商用利用を含めて完全無料
Single Database File単一のデータファイルにすべて構成される
Stable Cross-Pratform Database Fileデータファイルは異なるアーキテクチャ下でも大丈夫
Compactサイズ最適化ありでコンパイルすればライブラリは500KB以下
Manifest typingカラムの型を厳密に指定する必要なく実際のデータ依存でよし
Valiable-length recordsデータ長も固定ではなく必要な分だけ使う
Readable source code標準的プログラマなら読めるようなコードで実装
APIAPIだってシンプル簡単
Source CodeANSI-Cで記述され、入手もコンパイルも簡単

RDBMSの方が良い場合も

Client/Server Applicationsクラサバ構成:DBサーバー処理が重いもの
High-volume Websites大量アクセスWeb系:更新処理が多くサーバーの負荷分散とか必要なもの
Very large datasets大量データ:理論上は上限140テラバイトだけど実際はもっと少ない
High Concurrency排他制御:参照系は問題ないけど、更新系は並行処理ができません

環境作成

SQLite_Nuget.PNG

NuGetでケットするのみ。
Visual Stuioの「ツール」「NuGetパッケージマネージャ」「ソリューションのNuGetパッケージの管理」で上図の画面が開きます。
パッケージソース:nuget.org、フィルター:すべて、として「SQLite」を検索するとそれらしいのが並びます。

Sytem.Data.SQLite.Core : 本体のSQLite.Interop.dllとADO.NETに相当するSystem.Data.SQLite.dll
Sytem.Data.SQLite:全部盛りSQLite.Interop.dllとSystem.Data.SQLite.dllの他、EntityFramework用のSystem.Data.SQLite.EF6.dllとLinq to SQLite用のSystem.Data.SQLite.Linq.dllが設定されます。
SQLite.EF6:System.Data.SQLite.EF6.dllだけ。
SQLite.Linq:QLite.Linq.dllだけ。

良くわからないなら全部入ってるSytem.Data.SQLiteでよい。EFを使わないならSytem.Data.SQLite.CoreとSQLite.Linqを別個に入れるというのもあり。

ツール

データファイルの中身を覗いたり、テーブルをGUIで作るような管理ツールは必要。
無料版に限っても沢山あります。いくつか紹介。

SQLite Studio  英語版のみですが使い方に迷うこともないでしょう。現在も開発が続いてるようです。
DB Browser for SQLite多機能。CSV出力とかデータファイル最適化とかクエリ実行とかいろいろできます。
SQLite ControlCenter  本家 SQLiteCCの日本語版。
そのほかにもイッパイあります。
posted by RR at 00:12 | Comment(0) | SQLite | このブログの読者になる | 更新情報をチェックする

2017年01月28日

SQLite 接続文字列・オプション・チューニングなど

本家サイト

SQLite   つ https://www.sqlite.org/
SQLite.NET つ https://system.data.sqlite.org/index.html


接続文字列

通常は以下のようにデータファイル名を指定するぐらい。
指定したパスに相当する実フォルダがないと例外がスローされる。
ファイルはあればオープン、なければクリエイトオープンされる。
データファイルの拡張子はなんでもいいけどdbが多いみたい。

var con = new SQLiteConnection(@"Data Source=C:\DataFolder\DataFile.db");

オンメモリーというのもある。
実ディスクへのアクセスはなく一時テーブルのような使い方ができる。
コネクションをクローズしたりプロセス終了したりすれば全部消える。

var con = new SQLiteConnection("Data Source=:memory:;");


オプション

本家サイトの仕様(英語) つ https://www.sqlite.org/pragma.html
これらは接続文字列で指定する。 つ https://www.connectionstrings.com/sqlite/

コード上は以下のようにSQLiteConnectionStringBuilderクラスを使って指定する
            var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder
            {
                DataSource= @"C:\DataFolder\DataFile.db",
                JournalMode = SQLiteJournalModeEnum.Persist,
                SyncMode = SynchronizationModes.Off
            };
            var con = new SQLiteConnection(builder.ToString());

※API仕様をダウンロードするかVSで定義に移動すればプロパティを確認できます。
 若しくは下記でソースコードが公開されてます。

https://system.data.sqlite.org/index.html/artifact/fef1667ea62d75c2


データの型

通常のDBでは型や桁数など厳密な指定が必要となるが、SQLiteは値それ自体により動的に決まる。
各値は以下のどれかが割り当てられる。

NULLNULL値
INTEGER符号付整数(サイズは1〜8バイトに自動格納される)
REAL浮動小数点値(8バイト)
TEXTテキスト文字
BLOB入力値どおり
厳密な仕様は以下で確認できます。
https://www.sqlite.org/datatype3.html


日付型の扱い

日付型がないので、用途により数値か文字列にして変換関数使う。
大概はDataTimeクラスのToString,Parse,ParseExactで用が足る。


トランザクション制御

トランザクションを明示的に記述しない場合は、自動で行われる。
コミット処理はコストが高いので、更新処理は纏めてコミットした方が断然速い。

    using (var conn = new SQLiteConnection(@"Data Source=C:\DataFolder\DataFile.db")
    {
        conn.Open();
        using (SQLiteTransaction sqlt = conn.BeginTransaction())
        {
             for(int cnt = 0 ; cnt < 10000 ; ++cnt)
             {
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO T_MEMO (MEMO) VALUES (@p_memo)";
                    cmd.Parameters.Add(new SQLiteParameter("@p_memo", DateTime.Now.ToLongTimeString()));
                    cmd.ExecuteNonQuery();
                }
              }
            sqlt.Commit();
        }
        conn.Close();
    }


パフォーマンス

パフォーマンス向上させる手段はいくつもあるようで検索すると結構ヒットします。

参照系は充分に速い
・データファイルを分けて、保存先を物理的に異なるディスク上に分散配置する
・SSD上に置けば最速
・データ件数が多すぎて遅いなら他のRDBMSに代替を考える時期

更新系はチューニングの余地が多い
・トランザクションの開始と終了を明示
・replace文を都度発行してまとめて更新
・WALモードは遅い
・Sync=OFF,Journal=Persistがお勧め
・UPDATE/DELETEはコストが高いのでINSERT/ALTERで代替する
・仕様によっては更新系は夜間バッチで行おう

などなど。
posted by RR at 11:01 | Comment(0) | SQLite | このブログの読者になる | 更新情報をチェックする

2017年02月10日

SQLiteのオプション一覧

オプション

SQLiteのオプションは接続文字列で指定する。


string connectionString = @"data source=sample.db;synchronous=Normal;journal mode=Persist";


上記のように即値で指定してもよい。
付与するオプションが多い場合などは接続文字のビルダーを使う。

            var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder
            {
                DataSource = @"sample.db",
                SyncMode = SynchronizationModes.Normal,
                JournalMode = SQLiteJournalModeEnum.Persist,
            };
            string connectionString = builder.ToString();

オプション一覧

いろいろ試してみたけど、そもそも意味がよくわからないものや、指定有無で何が変ったのかよくわからないものが多かった。
指定が必須なのは赤文字のDataSourceのみ。通常はこれだけで行ける。他に使用しそうなのは太字にしてみた。もちろん個人的感想です。


int Version
インスタンス化されるSQLiteエンジンの既定版を指定する。現在は3のみ有効。つまり、現在のSQLiteはバージョン3ってことを意味している。
SynchronizationModes SyncMode
ファイルフラッシュの同期モードを指定する。Normalはコード上で重要な契機に、Fullは書き込み毎に、OffはOSのデフォにお任せ。未指定時はNormal。
bool UseUTF16Encoding
エンコーディングの指定。既定はFalseであり、この場合はUTF8エンコーディングって意味。
bool Pooling
コネクションプーリングの使用有無。既定はFalse
bool BinaryGUID
GUIDをバイナリフォーマットで保持するかどうか。デフォルトはTrueで保持される。
string DataSource
オープンする対象のデータファイル名。デフォルト空文字列。
string Uri
DataSoruceプロパティの代替。デフォはNull。
string FullUri
SQLite URI 文法によるDataSourceプロパティの代替。デフォはNull。
int DefaultTimeout
新しく生成されたコマンドのデフォルトタイムアウト時間。タイムアウト時間を設定できないSQLiteトランザクション内のような内部で使われるコマンドに特に有用である。デフォルト30。
int BusyTimeout
SQLite coreライブラリで使われるBusy Timeout値。デフォルト0.
int PrepareRetries
preparing SQLにより実行されるリトライ回数の最大値。データベースのスキーマが変更されたことによりpreparationが失敗する場合にのみ適用される。デフォルトは3
int ProgressOps
プログレスイベント間の仮想マシン命令のおおよその値。プログレスイベントを実際に発生させるためには、SQLiteConnection.Progressイベントにハンドラを登録する必要がある。デフォルト0.
bool Enlist
分散トランサクションが存在している場合に、自動でそれに参加するか否かのスイッチ。デフォルトTrue。
bool FailIfMissing
Trueの場合は、指定されたデータベースファイルが存在しない場合に例外がスローされます。Falseの場合は自動で生成されます。デフォはFalse。
bool LegacyFormat
Trueの場合、最大限の互換性のために3.XXフォーマットを使用しますが、その結果データベースサイズが大きくなります。デフォはFalse。
bool ReadOnly
Trueの場合は読み取り専用でオープンされて、書き込みはできなくなります。デフォはFalse。
string Password
データベースの暗号化用パスワード。デフォは空文字列。
byte[] HexPassword
データベースの暗号化用バイト配列パスワード。デフォはNull。
int PageSize
ページサイズ。デフォは4096.
int MaxPageCount
データベースが保持できるであろう最大のページ数。デフォは0.
int CacheSize
キャッシュサイズ。デフォは-2000。
SQLiteDateFormats DateTimeFormat
日付型のフォーマット。デフォはDefault。ISO8601と一緒。
DateTimeKind DateTimeKind
日付型の種類。デフォはUnspecified。
string DateTimeFormatString
フォーマットやパースで使われる文字列。デフォはNull。
string BaseSchemaName
.NET Frameworkとの互換性のために使用されるプレースホルダベースのスキーマ名。デフォは"sqlite_default_schema"
SQLiteJournalModeEnum JournalMode
SQLiteがトランザクションジャーナルファイルをどう扱うか。Default,Delete,Persist,Off,Trancate,Memory,Walの指定がある。デフォのDefaultは既存のジャーナルモードを使用する。
IsolationLevel DefaultIsolationLevel
トランザクション分離レベル。デフォはSerializable。
DbType DefaultDbType
既定のデータベース種別。デフォはBadDbType。
string DefaultTypeName
デフォルトタイプ名。デフォはNull.
string VfsName
VFS名。デフォはNull.
bool ForeignKeys
Trueの場合は外部制約キーが使える。デフォはFalse.
bool RecursiveTriggers
入れ子になったトリガーの使用可否。デフォはFalse.
string ZipVfsVersion
Null以外が設定された場合にZipVfsが使えます。System.Data.SQLite.dllがINTEROP_INCLUDE_ZIPVFSオプションありでコンパイルされている必要がある。さもないと設定しても無駄。デフォはNull。
SQLiteConnectionFlags Flags
特別な振る舞いに関するスイッチ群。ロギングや数値型マッピングの型の指定など。デフォはDefault
bool SetDefaults
Trueならデータベースをオープンするためにデフォルト値が使われる。。デフォはTrue。
bool ToFullPath
Trueならデータソースファイルをオープンする前に指定されたデータソースファイル名からフルパスを解決するよう試みる。デフォTrue。
bool NoDefaultFlags
Trueの場合は既定の構成を使うことを回避する。デフォはTure.
bool NoSharedFlags
Trueの場合は共通の接続フラグを構成することを回避する。デフォはFalse。
posted by RR at 01:23 | Comment(0) | SQLite | このブログの読者になる | 更新情報をチェックする