SQLServer Compact Edition

SQLServerCeで行ロックがかかっているかの実験。

using (var connection1 = new SqlCeConnection(Settings.Default.Setting))
using (var connection2 = new SqlCeConnection(Settings.Default.Setting))
{
    connection1.Open();
    connection2.Open();
    var command1 = connection1.CreateCommand();
    var command2 = connection2.CreateCommand();
    using (var tran1 = connection1.BeginTransaction())
    using (var tran2 = connection2.BeginTransaction())
    {
        command1.Transaction = tran1;
        command2.Transaction = tran2;

        command1.CommandText = "select * from table1 with (updlock) where id = 1";
        command2.CommandText = "select * from table1 with (updlock) where id = 1";

        using (var reader1 = command1.ExecuteReader())
        using (var reader2 = command2.ExecuteReader())
        {
            while (reader1.Read() && reader2.Read())
            {
                Console.WriteLine(reader1.GetInt32(0));
                Console.WriteLine(reader2.GetInt32(0));
            }
        }
    }
}

確かに。

SQLite

以前のエントリで、Decimal型がDoubleに丸められてDBにInsertされることを書いた。
その後いろいろ調べた結果、フィールド型にNONEを指定していたのが原因だった。フィールド型をNONEで作成すると、NUMERICのaffinityが適用されるらしい。

http://www.sqlite.org/datatype3.html
Datatypes In SQLite Version 3 - 2.1 Determination Of Column Affinity

1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.
2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.
4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
5. Otherwise, the affinity is NUMERIC.

つまり、NONEのaffinityを適用されるためには、フィールドをBLOB型もしくは型指定なし（NONEの指定ではない）で作成しなくてはならないようだ。NONE指定すると、その他になり5番のNUMERICが適用されるわけだ。

ちなみに、↓がテーブルを作成する際に使用したSQLiteStudioのテーブル作成画面。
カラム追加のデフォルトデータタイプがNONEになっている。

そして、しっかりDDLにNONEと記載される。

引っかかった。

その後、データタイプをBlobで作成したところ、文字列として格納されることを確認した。

FriendFeed

以前のエントリで、RDBにスキーマレスなデータを格納する方法を2種類検討した。

で、2種類の方法をうまくいいとこ取りした製品があるようだ。
http://www.infoq.com/jp/news/2009/04/friendfeed-schemaless-mySQL

検索を行うカラムのみ分解しておけ、ということなのね。

SQLite-型のないカラム

SQLiteはデータ型を指定せずにカラムを作成することがで、様々な型を適切(?)に格納することができる。
これは、オブジェクトをプロパティで分割して格納する際に非常に都合がよい。

というわけで、検証。
DecimalがDoubleとして格納されるために有効桁数が小さくなってしまってるので困りもの。どうにか回避方法はないものか。

static void Main(string[] args)
{
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["SQLiteConnection"];
DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName);

using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = setting.ConnectionString;
connection.Open();
DbCommand insertCommand = connection.CreateCommand();
insertCommand.CommandText = "Insert into Data Values (?, ?, ?)";
{
DbParameter p1 = factory.CreateParameter();
p1.DbType = DbType.Int32;
insertCommand.Parameters.Add(p1);
DbParameter p2 = factory.CreateParameter();
p2.DbType = DbType.String;
insertCommand.Parameters.Add(p2);
DbParameter p3 = factory.CreateParameter();
p3.DbType = DbType.Object;
insertCommand.Parameters.Add(p3);
}
PropertyInfo[] props = typeof(Data).GetProperties();
var propDic = props.ToDictionary(x => x.Name);
using (DbTransaction tran = connection.BeginTransaction())
{
foreach (var data in GetPersonList().Take(2))
{
insertCommand.Parameters[0].Value = data.IntVal;
foreach (var prop in props)
{
insertCommand.Parameters[1].Value = prop.Name;
insertCommand.Parameters[2].Value = prop.GetValue(data, null);
insertCommand.ExecuteNonQuery();
}
}
tran.Commit();
}

DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandText = "Select * From Data";
using (DbDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
var prop = propDic[reader.GetString(1)];
Console.Write(prop.Name + ":" + reader.GetFieldType(2) + ":");
if (prop.PropertyType == typeof(int))
{
Console.WriteLine(reader.GetInt32(2));
}
else if (prop.PropertyType == typeof(long))
{
Console.WriteLine(reader.GetInt64(2));
}
else if (prop.PropertyType == typeof(string))
{
Console.WriteLine(reader.GetString(2));
}
else if (prop.PropertyType == typeof(bool))
{
Console.WriteLine(reader.GetBoolean(2));
}
else if (prop.PropertyType == typeof(DateTime))
{
Console.WriteLine(reader.GetDateTime(2));
}
else if (prop.PropertyType == typeof(double))
{
Console.WriteLine(reader.GetDouble(2));
}
else if (prop.PropertyType == typeof(decimal))
{
Console.WriteLine(reader.GetDecimal(2));
}
}
}
}
}

static IEnumerable<Data> GetPersonList()
{
int id = 0;
while (true)
{
yield return new Data
{
IntVal = id,
LongVal = long.MaxValue,
Text = Path.GetRandomFileName(),
Flag = id % 2 == 0,
Date = new DateTime(2010, 1, 1).AddDays(id),
DoubleVal = double.MaxValue,
DecimalVal = decimal.MaxValue,
};
id++;
}
}

private class Data
{
public int IntVal { get; set; }
public long LongVal { get; set; }
public string Text { get; set; }
public bool Flag { get; set; }
public DateTime Date { get; set; }
public double DoubleVal { get; set; }
public decimal DecimalVal { get; set; }
}

SQLクエリ

以前のエントリで、データをプロパティに分解して格納する方法、検索方法がどのように変わるか検討してみた。

通常
<Person>テーブル

ID

Name

Age

Address

Sex

Select * From Person Where Age >= 20 And Age <= 29 And Sex='男'

20代男性を検索

分割
<Person>テーブル

ID

PropertyName

Value

Select * From Person Where ID in
(Select Id From Person Where PropertyName = 'Age' And Value >= 20 And Value <= 29
Intersect
Select Id From Person Where PropertyName = 'Sex' And Value = '男')

AndはIntersectに、ORはUnionに変換すれば良さそう。でもパフォーマンス大丈夫か？

DbProviderFactory

DbProviderFactoryを使用することにより、データプロバイダに依存しない実装が可能。で、備忘録

使用可能なProvider一覧を取得

DbProviderFactories.GetFactoryClasses();

machine.configより、DataProviderの情報を取得している。
戻り値はDataTable型なので、DataGridViewを使えば簡単に内容を確認することができる。

ApplicationでDataProviderを追加したい場合は、app.configに設定することで追加可能。 例えば、PostgreSQLの場合は、↓の様に設定。

<system.data>
<DbProviderFactories>
<add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.8.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
</DbProviderFactories>
</system.data>

そうすることで、DbFactoryが取得可能となる。

app.configよりConnectionStringを取得してDbProviderFactoryを作成 

ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["MyConnection"];
DbProviderFactory factory = DbProviderFactories.GetFactory(css.ProviderName);

ConfigurationManagerからConnectionStringSettingsを取得。(System.Configuration.dllの参照が必要)
ConnectionStringSettingsよりProviderNameを取得し、DbProviderを作成

あとは、factory.CreateXXXXX()で作成し放題！

SQLite

というわけで（？）、ファイルDBのSQLiteを評価中。

とりあえずパフォーマンス比較をやってみた

DB	Insert	Update	Delete	Select
MDB	21.32	32.07	27.18	46.05
SQLServer CE	3.53	4.90	4.05	9.59
SQLite	0.89	1.05	0.85	1.07

※処理
Insert×10万回
Update×10万回（主キーを条件に1レコード更新）
Select×10万回（主キーを条件に1レコード取得）
Delete×10万回(主キーを条件に1レコード削除)
テーブルは、2カラム（主キー、値）のみ。

※環境

OS	WIndows7 Ultimate(x64)
CPU	Core-i7 920
Memory	12G

SQLiteはえー。

スキーマレス

RDBにスキーマレスなデータを格納する方法を検討中

スキーマレスといえばXMLDBという気もするが、インストール不要のお手軽ファイルDBがよいので、やっぱりRDBになってしまう。格納方法として思いついたのが以下の2通り。

• シリアライズ

オブジェクトをシリアライズしてそのままBlob型のカラムに格納する方法

ID	Data
1	<?xml version="1.0"?> <名前 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <姓>山田</姓> <名>太郎</名> </名前>
2	<?xml version="1.0"?> <名前 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <姓>山田</姓> <名>次郎</名> </名前>

プログラム側はC#なので、オブジェクトをXMLSerializerに突っ込んでDBに保存・読込を行えばよいので実装が楽そう。問題はどうやって検索するのか？

• プロパティーに分解

オブジェクトのプロパティー単位でレコードを追加する方法

ID	PropertyName	Value	Type
1	姓	山田	System.String
1	名	太郎	System.String
2	姓	山田	System.String
2.	名	次郎	System.String

こちらは検索はできそう。でもレコード数が多くなるので、Insertが大変そう。

File DB

業務で使用するDBとして以下のような要件を満たすDBを探している

• インストール不要
• 共有フォルダにおいて数人に共有

現在のところMSAccess(MDB)なのだが、負荷をかけると結構重い。
他によい選択肢はないのだろうか？