C#メモ: SQLite

ラベル SQLite の投稿を表示しています。すべての投稿を表示

2020年1月1日水曜日

SQLiteメモ

[SQLite]C#でSQLiteを使うための準備
[SQLite]コマンドラインで実行したい

▪️DB作成および接続関連
[SQLite]DB作成および接続
→[SQLite]外部キープラグマの確認

▪️Table作成関連
[SQLite]テーブルが作られてなければ作成する
[SQLite]Unique制約で大文字小文字を同じ物と判定する方法
[SQLite]文字数制約

▪️CRUD関連
[SQLite]複数のデータベースを結合する

2016年2月1日月曜日

[SQLite]文字数制約

文字数の制約をつけたい。
ひとまず、型の後ろにカッコつけて数字かけばいいのかな？

using System;
using System.Data.SQLite;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=:memory:"))
                {
                    con.Open();

                    using (SQLiteCommand cmd = con.CreateCommand())
                    {

                        cmd.CommandText = "CREATE TABLE [Sample] (" +
                                          "[data]  TEXT(5) NOT NULL" +
                                          ");";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Sample] ([name]) VALUES('123456');";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

あら・・・怒られない。
そもそも、文字数制限をかけれるような話をみたことないもんなぁ。

じゃ、別の方法で行ってみます。

cmd.CommandText = "CREATE TABLE [Sample] (" +
                                          "[data]  TEXT NOT NULL CHECK(LENGTH([data]) <= 5)" +
                                          ");";

怒られた。

constraint failed
CHECK constraint failed: Sample

[SQLite]Unique制約で大文字小文字を同じ物と判定する方法

例えば、このブログのタグもそうですが、SQLiteもSQLITEもsqliteも一つのものとして判断してほしい場合があると思います。

そこでUniqueキー制約をつけてみると・・・

using System;
using System.Data;
using System.Data.SQLite;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=:memory:"))
                {
                    con.Open();

                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "CREATE TABLE [Blog](" +
                                          "[tag]  TEXT    NOT NULL, " +
                                          "UNIQUE([tag])" +
                                          ");";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Blog] ([tag]) VALUES('SQLite');";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Blog] ([tag]) VALUES('SQLITE');";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

問題なく入ります。
大文字と小文字を別物として判断しているからです。

そこで、大文字小文字を区別してほしくない列にCOLLATE NOCASEをつけてみます。

using System;
using System.Data;
using System.Data.SQLite;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=:memory:"))
                {
                    con.Open();

                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "CREATE TABLE [Blog](" +
                                          "[tag]  TEXT    NOT NULL, " +
                                          "UNIQUE([tag] COLLATE NOCASE)" +
                                          ");";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Blog] ([tag]) VALUES('SQLite');";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Blog] ([tag]) VALUES('SQLITE');";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

エラーがでるようになりました。

constraint failed
UNIQUE constraint failed: Blog.tag

もちろん、PRIMARY KEYであっても同じことです。

cmd.CommandText = "CREATE TABLE [Blog](" +
                                        "[tag]  TEXT , " +
                                        "PRIMARY KEY([tag] COLLATE NOCASE)" +
                                        ");";

uniqueじゃないと怒ってくれます。

constraint failed
UNIQUE constraint failed: Blog.tag

2016年1月31日日曜日

SQLiteでは、デフォルトで外部キーチェックが無効となっています。

そもそも外部キー制約が取り入れられたのがVer3.6.19からです。
2009-10-14リリースからですね。
https://www.sqlite.org/oldnews.html

せっかくなので確認してみます。
まずは、デフォルトでの動作確認です。
従業員テーブルと、部署テーブルを定義してから
部署テーブルにデータをいれずに、従業員テーブルにデータを
入れて外部参照エラーを起こさせるシナリオです。

using System;
using System.Data;
using System.Data.SQLite;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=:memory:"))
                {
                    con.Open();

                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        // 部署テーブルの作成
                        cmd.CommandText = "CREATE TABLE [Department](" +
                                          "[id]    INTEGER NOT NULL PRIMARY KEY, " +
                                          "[name]  TEXT    NOT NULL" +
                                          ");";
                        cmd.ExecuteNonQuery();

                        // 従業員テーブルの作成
                        cmd.CommandText = "CREATE TABLE [Employee](" +
                                          "[id]     INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                          "[name]   TEXT    NOT NULL, " +
                                          "[depID]  INTEGER NOT NULL," +
                                          "FOREIGN KEY([id]) REFERENCES [Employee]([depID]) " +
                                          ");";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [Employee] (name, depID) VALUES('hoge', 100);";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

何事もなく終了します。
そこで、Foreign Keyプラグマを有効にします。
https://www.sqlite.org/pragma.html#pragma_foreign_keys

下記のように、SQL文として実行することでも有効にできますが・・・
cmd.CommandText = "PRAGMA foreign_keys = ON;";
cmd.ExecuteNonQuery();

接続文字列に指定することもできます。
[SQLite]DB作成および接続

SQLiteConnection con = new SQLiteConnection("Data Source=:memory:;Foreign Keys=True")
これで実行すると、下記のようにエラーになります

SQL logic error or missing database
foreign key mismatch - "Employee" referencing "Employee"

[SQLite]DB作成および接続

◆DBを作成および接続

using System;
using System.Data.SQLite;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            string filepath = "sample.db";

            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=" + filepath))
                {
                    con.Open();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

▪️Data Source
オープンしたいデータベースのパスを指定します。
必須項目はこちらのみですが、それ以外に設定できるものは、以下のとおり。

情報は、System.Data.SQLiteのドキュメントです。
SQLiteConnection Classに記載されています。

あらためて説明(めぼしいとこだけ抜粋)
▪️Data Source
file名、:memory:、URI(ver3.7.7以降)に対応している。

:memory: は、ファイルを生成せずにon memoryで処理したい場合に指定。
UNCのパスを使うときは、先頭のスラッシュは２つずつ記載しないといけないそうな。
例としてこんなのが書いてある
(e.g. "\\Network\Share\test.db" would become "\\\\Network\Share\test.db")

この項目だけ必須。
あとは、任意。

Pragmaの設定とかをSQLiteConnectionで設定できます。

--------------------------------------------------------------------------------------------------
▪️Version
バージョン番号。説明は3としか書いてないです・・・
あるサイトにバージョンは3で固定と言った内容をみかけたのですが、どうなんだろう。
Default: 3

--------------------------------------------------------------------------------------------------
▪️UseUTF16Encoding
True→EncodeにUTF16が使われる
False→EncodeにUTF8が使われる
Defaul:False

--------------------------------------------------------------------------------------------------
▪️Cache Size
メモリに保持できる最大ページ数を指定。
保持できる最大メモリ数は、Cache Size x Page Size(bytes)となる
Default:2000

--------------------------------------------------------------------------------------------------
▪️Page Size
ページサイズの指定
Default:1024(byte)

※(メモ)データベースのデータを管理するユニットのサイズ、
データが多くあるのに、ページが小さいと複数のページを読み込まないといけなくなる
逆にたいしたデータがないのに大きいページを設定すると、無駄な読み込みが発生する
SQL Serverあたりは8kbとなっているみたい。
ちなみに、2の累乗の値を設定する必要があるそうな。

--------------------------------------------------------------------------------------------------
▪️Foreign Keys
ver3.6.19から外部参照が使えるようになったのですが
互換性を考慮してか、デフォルトではOFFとなっています。
そのため、この項目をTrueにして接続しないと、外部参照のエラーは発生しません。
（外部参照を設定してもなんでもうけつけてしまう）
Default:false

--------------------------------------------------------------------------------------------------
▪️Password
パスワードを設定することができる。
SQLiteConnectionクラスのChangePasswordメソッドで変更できる

--------------------------------------------------------------------------------------------------
▪️Default IsolationLevel
トランザクション分離レベル
SQLiteConnectionの項目には特に説明はないけど
BeginTransactionの引数で使用できるIsolationLevelの説明だと
SerializableとReadCommittedのみ対応しているみたい。
Default:Serializable

--------------------------------------------------------------------------------------------------
▪️Default Timeout
コマンド実行タイムアウト時間
Default:30[s]

--------------------------------------------------------------------------------------------------
▪️Journal Mode
データの変更時に、一時的に生成されるJournalファイルのモードを設定
Delete, Persist, Off, Truncate, Memory, Walから選べる
Deleteは、コミット後ジャーナルファイルが削除される
Truncate は、削除するかわりにジャーナルサイズを0にする(削除するより早い)
Persistは、コミット後ジャーナルファイルのヘッダーを0に書き換えるだけ(Trancateするより早い)
OFFは、ジャーナルを使わない。ロールバックは使えなくなります。

Memoryは、ジャーナルをファイルではなくメモリ上で行う。
Walは、ログを吐き出した時点でデータファイルに書き込みを行う
Default:Delete

--------------------------------------------------------------------------------------------------
▪️Synchronous
同期の設定。
OFF、Normal、Fullとの３つのモードあり。
OFFの場合、書き込みが完了するか待たずに処理を再開。
NormalとFullは、ディスクへのデータ書き込みを待ってから次の処理に移る。
Fullは、完全に待つが、NormalはFullほどまたない。
Default:Full

※(メモ)下記サイトのPRAGMA default_synchronousの説明がわかりやすかったです
http://net-newbie.com/sqlite/lang.html

--------------------------------------------------------------------------------------------------
▪️Pooling
コネクションプーリング
Default:False

--------------------------------------------------------------------------------------------------
▪️Max Pool Size
コネクションプーリングの最大数
Default:100

[SQLite]複数のデータベースを結合する

SQLiteでは、1つのDBがファイルとなります。
他のDBファイルにあるデータと結合する場合は、ATTACH DATABASEで取り込んでから結合する。

簡単なdbを二つ作ってみます。

C:\Users\test>sqlite3 data1.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> create table table1 (id, value);
sqlite> insert into table1 (id, value) values(1, 'a1');
sqlite> insert into table1 (id, value) values(2, 'a2');
sqlite> insert into table1 (id, value) values(3, 'a3');
sqlite> .quit

C:\Users\test>sqlite3 data2.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> create table table2 (id, value);
sqlite> insert into table2 (id, value) values(1, 'b1');
sqlite> insert into table2 (id, value) values(2, 'b2');
sqlite> insert into table2 (id, value) values(3, 'b3');
sqlite> .quit

こんな状態です

data1.db > table1
id          value
----------  ----------
1           a1
2           a2
3           a3

data2.db table2
id          value
----------  ----------
1           b1
2           b2
3           b3

別々のファイルのid列を結合した結果を表示したい。
そんな時は、データベースをATTACHすればよいです。

DBに接続した直後に .databases を実行すると現在接続できているdbが表示されます。

C:\Users\test>sqlite3 data1.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> .databases
seq  name             file

---  ---------------  -----------------------

0    main             C:\Users\test\data1.db

ATTACH DATABASE [filePath] AS databaseName
ATTACH [filePath] AS databaseName

で、DBの追加ができます

sqlite> ATTACH "C:\Users\test\data2.db" AS db2;
sqlite> .databases
seq  name             file

---  ---------------  -------------------------

0    main             C:\Users\test\data1.db

2    db2              C:\Users\test\data2.db

あとは、db名.table名.column名で指定すればよいだけですね

sqlite> SELECT main.table1.id, main.table1.value, db2.table2.value
   ...> FROM main.table1
   ...> INNER JOIN db2.table2
   ...> ON main.table1.id = db2.table2.id;
id          value       value
----------  ----------  ----------
1           a1          b1
2           a2          b2
3           a3          b3

using System;
using System.Data.SQLite;
using System.Windows.Forms;

namespace SampleCode
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection("Data Source=data1.db"))
                {   
                    con.Open();

                    // data2.dbをAttachする
                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "ATTACH [data2.db] AS db2;";

                        cmd.ExecuteNonQuery();
                    }

                    using (SQLiteCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "SELECT main.table1.id, main.table1.value, db2.table2.value " +
                                          "FROM main.table1 " +
                                          "INNER JOIN db2.table2 " +
                                          "ON main.table1.id = db2.table2.id;";

                        SQLiteDataReader reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            string data = string.Format("{0}\t{1}\t{2}", reader[0].ToString(), reader[1].ToString(), reader[2].ToString());
                            Console.WriteLine(data);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

注意点として、ATTACH する時ダブルクォートかブラケットで囲まないとエラーになります。
OK "ATTACH \"data2.db\" AS db2;";
OK "ATTACH [data2.db] AS db2;";
NG "ATTACH data2.db AS db2;";
↓
database schema has changed
no such column: data2.db

SQLiteだと、ロックがファイル単位でロックがかかるので
頻繁に更新のあるテーブルは分けて、別ファイルで結合するのがよさそうですね。

ちなみに、DETACHで現在の接続から切り離す事ができます。
DETACH DATABASE [databaseName]
DETACH [databaseName]

[SQLite]コマンドラインで実行したい

ちょっとした動作の確認をしたい場合にコードを書くのはしんどいのでコマンドラインで確認したくなると思います。

そんな時は、SQLiteのサイトに行き、ダウンロードのページに
http://www.sqlite.org/download.html

Precompiled Binaries for Windowsの項目に
sqlite-tools-win32-x86-3100200.zipのところに「command-line shell program」と説明されているので
これをダウンロードします。

あとは、適当な場所に配置してパスを通すなりすればよいと思います。

登録: 投稿 (Atom)