2016-08-07

LambdicSql - 生まれ変わりました -

LambdicSql C#

※注) 以前のブログもβ版に合わせてコードを修正しているので、前のブログ記事と比べても変化は感じられません。

LambdicSql_α0.0.51 をリリースしました。コードのほとんどを書き直すほどの破壊的大変更を入れました。まさにRebornなのです！
www.nuget.org
変更内容は盛りだくさんです。
先日、総監督と打ち合わせして、その内容をかなり取り込みました。ボリューミーなので何回かに分けて書きます。手っ取り早く全貌を見たい方は、こちらのページを参照お願いします。
github.com

変更一覧

今回は1.を書きます。

書き心地 100%Up(当社比)

大きくはこれです。以前比べると、かなり書きやすく、そして読みやすくなりました。

using Dapper;
using LambdicSql;
//★重要 using static.
using static LambdicSql.Keywords;
using static LambdicSql.Funcs;
using static LambdicSql.Utils;

public void TestStandard()
{
    //ラムダでSQLを作成
    var query = Db<DB>.Sql(db =>
    Select(new SelectData
    {
        Name = db.tbl_staff.name,
        Count = Count(db.tbl_remuneration.money),
        Total = Sum(db.tbl_remuneration.money),
        Average = Avg(db.tbl_remuneration.money),
        Minimum = Min(db.tbl_remuneration.money),
        Maximum = Max(db.tbl_remuneration.money),
    }).
    From(db.tbl_remuneration).
        Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
    GroupBy(db.tbl_staff.id, db.tbl_staff.name));

    //文字列とパラメータに変更
    var info = query.Build(_connection.GetType());
    Debug.Print(info.SqlText);
}
public void TestCase()
{
   //case文がこんなにすっきり書ける
    var query = Db<DB>.Sql(db =>
        Select(new SelectData()
        {
            Type = Case().
                      When(db.tbl_staff.id == 3).Then("x").
                      When(db.tbl_staff.id == 4).Then("y").
                      Else("z").
                   End()
        }).
        From(db.tbl_staff));
}

Sql.Create()でまとめて書けるようになった

以前は句ごとに分かれていました。そのため、毎回 db=> て書く必要がありました。それが今回の変更で一つのExpressionとして表現するようになったので、db=>は最初の一回でよくなりました。

using staticを使いやすく変更

それから、これはVisualStudio2015を使ってないと無理なのですが(そろそろ、みんな使ってますよね？)メソッドをstaticにすることによって、using staticを使って唐突にメソッドを使えるようにしました。SelectとかSumとか唐突に呼べるようになって、さらにSQLっぽくなったのではないでしょうか。残念ながら、2013までの人はメソッドの前にクラス名を書く必要があります。

組み合わせ自由自在

以前までもできましたが、さらに自由になりました。暗黙の変換の導入でキャストの手間が減りました。まあ、キャストはコンパイルを通すためだけなので。

public void TestSqlExpression()
{
    //式単位で扱うときも統一的に扱える
    var expMoneyAdd = Db<DB>.Sql(db => db.tbl_remuneration.money + 100);
    var expWhereMin = Db<DB>.Sql(db => 3000 < db.tbl_remuneration.money);
    var expWhereMax = Db<DB>.Sql(db => db.tbl_remuneration.money < 4000);

    //式の型を覚えていて、暗黙に変換される
    //上手く合わないときだけCast<>()メソッドを使う    
    var query = Db<DB>.Sql(db =>
        Select(new SelectData1()
        {
            Name = db.tbl_staff.name,
            PaymentDate = db.tbl_remuneration.payment_date,
            //decimalに変換される
            Money = expMoneyAdd,
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        //boolに変換され、それらを&&や||で演算できる
        Where(expWhereMin && expWhereMax).
        OrderBy(new Asc(db.tbl_staff.name)));
}

条件式作成も書きやすく、直感的に

public void TestWhereEx(bool minCondition, bool maxCondition)
{
    //Conditionメソッドの第一引数は、その条件が有効か否かを設定    
    //無効なら消える
    //||演算も当然できるし、()を使った複雑な式にも対応
    var exp = Sql<DB>.Create(db =>
        Condition(minCondition, 3000 < db.tbl_remuneration.money) &&
        Condition(maxCondition, db.tbl_remuneration.money < 4000));

    //条件がなくなったらWhereは消えます
    var query = Sql<DB>.Create(db => SelectFrom(db.tbl_remuneration).Where(exp));
}

2016-07-31

lambdicSql - Queryの自由な組み立て - ③サブクエリ -

LambdicSql C#

サブクエリは前からある機能ですが、おさらいで。
これも、クエリを先に作っておいて、Castで文中に入れ込みます。入れたところで展開されます。

Select句

public void SelectSubQuery()
{
    //moneyの全合計
    var sub = Db<DB>.Sql(db=>
        Select(new
        {
            total = Sum(db.tbl_remuneration.money)
        }).
        From(db.tbl_remuneration));

    //スタッフごとにグルーピングされたmoneyと全体の合計を同時に表示する
    var query = Db<DB>.Sql(db =>
        Select(new
        {
            name = db.tbl_staff.name,
            personalTotal = Sum(db.tbl_remuneration.money),
            total = sub.Cast<decimal>()//★サブクエリ 代入したかのようなコードが書ける
        }).
        From(db.tbl_staff).
        Join(db.tbl_remuneration, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        GroupBy(db.tbl_remuneration.staff_id, db.tbl_staff.name));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS name,
	SUM(tbl_remuneration.money) AS personalTotal,
	(SELECT
	SUM(tbl_remuneration.money) AS total
	FROM tbl_remuneration) AS total
FROM tbl_staff
	JOIN tbl_remuneration ON (tbl_remuneration.staff_id) = (tbl_staff.id)
GROUP BY tbl_remuneration.staff_id, tbl_staff.name

Where句

public void WhereInSubQuery()
{
    //サブクエリ作成
    var sub = Db<DB>.Sql(db =>
        Select(new
        {
            money = db.tbl_remuneration.money
        }).
        From(db.tbl_remuneration).
        Join(db.tbl_staff, db => db.tbl_remuneration.staff_id == db.tbl_staff.id).
        Where(3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    //Inにサブクエリを入れる
    var query = Sql<Data>.Create(db =>
    Select(new Asterisk()).
    From(db.tbl_remuneration).

    //条件指定
    Where(In(db.tbl_remuneration.money, sub.Cast<int>()));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT *
FROM tbl_remuneration
WHERE tbl_remuneration.money IN(
	(SELECT
	tbl_remuneration.money AS money
	FROM tbl_remuneration
		JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
	WHERE ((@p_0) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_1))))

From句

From句で使うときは少しコツがあって、DB定義の部分で使っておきます。ここに入ってないと、続くクエリ構築で使えないのです。その後、From句で(JoinでもOK）で使います。

public void FromSubQuery()
{
    //サブクエリ作成
    var sub = Db<DB>.Sql(db =>
        Select(new
        {
            name = db.tbl_staff.name,
            payment_date = db.tbl_remuneration.payment_date,
            money = db.tbl_remuneration.money,
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        Where(3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    var query = Db<DB>.Sql(db =>
        Select(new
        {
            name = sub.Body.name
        }).
        //From句でサブクエリのテーブル（的なもの）を指定
        //View的な使い方
        From(sub));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
sub.name AS name
FROM 
	(SELECT
	tbl_staff.name AS name,
		tbl_remuneration.payment_date AS payment_date,
		tbl_remuneration.money AS money
	FROM tbl_remuneration
		JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
	WHERE ((@p_2) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_3))) sub

履歴

2016/09/02 β版対応

2016-07-31

lambdicSql - Queryの自由な組み立て - ②Expression単位 -

LambdicSql C#

LambdicSqlではExpression単位で分解、構築できるようにしました。
Castっていうキーワードがポイントです。Sql.Query().ExpressionはISqlExpressionという型を返します。これを文中に埋め込むとその文字列になります。でもISqlExpressionではコンパイル通らないので、それがコンパイルできる型にキャストするわけです。

public void SqlExtension()
{
    var expMoneyAdd = Db<DB>.Sql(db => db.tbl_remuneration.money + 100);
    var expWhereMin = Db<DB>.Sql(db => 3000 < db.tbl_remuneration.money);
    var expWhereMax = Db<DB>.Sql(db => db.tbl_remuneration.money < 4000);

    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Name = db.tbl_staff.name,
            PaymentDate = db.tbl_remuneration.payment_date,
            //作っておいたExpressionを組み込み
            //最終的にはdecimalで扱いたい
            Money = expMoneyAdd.Cast<decimal>(),
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        //組み合わせることも可能です。
        Where(expWhereMin && expWhereMax).
        OrderBy(new Asc(db.tbl_staff.name)));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	tbl_remuneration.payment_date AS PaymentDate,
	(tbl_remuneration.money) + (@p_0) AS Money
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
WHERE ((@p_1) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_2))
ORDER BY
	tbl_staff.name ASC

条件文を構築しやすくるためのヘルパ

条件文も上の書き方で、連結していけるわけですが、ちょっと面倒です。

//こんな感じで連結可能
var expWhereMin = Db<DB>.Sql(db => 3000 < db.tbl_remuneration.money);
var expWhereMax = Db<DB>.Sql(db => db.tbl_remuneration.money < 4000);
var expWhere = Db<DB>.Sql(db => expWhereMin && expWhereMax.Cast);

これはよくやるので、簡単にかけるようにしました。
Whereの組み立てで、その条件を有効にするか否かは頻繁にあり、そこで便利に使うことができます。ちなみに全部無効でExpressionが空になった場合はWhere句自体が消えます。

public void ContinueConditions(bool minEnable, bool maxEnable)
{
    //その条件を有効にするかどうかを指定することができる
    var exp = Db<DB>.Sql(db =>
        Condition(minEnable, 3000 < db.tbl_remuneration.money) &&
        Condition(maxEnable, db.tbl_remuneration.money < 4000) &&
        db.tbl_staff.id == 1);

    var query = Db<DB>.Sql(db =>
        Select(Asterisk(db.tbl_remuneration)).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        //特殊仕様で渡されたExpressionが空ならWhere句は消える。Havingも同様。
        Where(exp));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

履歴

2016/09/02

2016-07-31

lambdicSql - Queryの自由な組み立て - ①句単位 -

LambdicSql C#

Queryの組み立てシリーズです。まずは、クエリ単位で分割、構築する方法です。Concatでクエリを連結できるようにしました。Concatは型に関してわざと緩くしました。使っているDBの型が違っても結合できます。最終的にSQL文字列になったときに意味が通っていたらOKです。（ダメならSQL実行で例外が発生します。)

public void QueryConcat()
{
    //句単位で別々に記述
    var select = Db<DB>.Sql(db =>
        Select( new SelectedData()
        {
            Name = db.tbl_staff.name,
            PaymentDate = db.tbl_remuneration.payment_date,
            Money = db.tbl_remuneration.money,
        }));

    var from = Db<DB>.Sql(db =>
            From( db.tbl_remuneration).
        Join( db.tbl_staff,  db.tbl_remuneration.staff_id == db.tbl_staff.id));

    var where = Db<DB>.Sql(db =>
        Where( 3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    var orderby = Db<DB>.Sql(db =>
        OrderBy(new Asc(db.tbl_staff.name)));

    //クエリ構築
    var query = select.Concat(from).Concat(where).Concat(orderby);

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	tbl_remuneration.payment_date AS PaymentDate,
	tbl_remuneration.money AS Money
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
WHERE ((@p_0) < (tbl_remuneration.money)) AND ((tbl_remuneration.money) < (@p_1))
ORDER BY
	tbl_staff.name ASC

例えば、Where句以外は使いまわしとかの場合は、外の句はstatic readonlyにしておくと便利ですね。

static readonly SqlQuery<SelectedData> select = Db<DB>.Sql(db =>
    Select(new SelectedData()
    {
        Name = db.tbl_staff.name,
        PaymentDate = db.tbl_remuneration.payment_date,
        Money = db.tbl_remuneration.money,
    }));

static readonly ISqlQuery from = Db<DB>.Sql(db =>
        From(db.tbl_remuneration).
    Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id));

static readonly ISqlQuery orderby = Db<DB>.Sql(db =>
    OrderBy(new Asc(db.tbl_staff.name)));
        
public IEnumerable<SelectedData> Execute(decimal min, decimal max)
{
    //Where句だけここで作成
    var where = Db<DB>.Sql(db =>
        Where( 3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));

    //クエリ構築
    var query = select.Concat(from).Concat(where).Concat(orderby);

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

履歴

2016/09/02

2016-07-31

lambdicSql - Queryの自由な組み立て -

LambdicSql C#

LambdicSqlα_0.0.40 をリリースしました。
Queryの組み立てに関して仕様追加と、既存の仕様を変更しました。α版なんで、破壊的な変更もバンバンいれます。
www.nuget.org

Queryは組み合わせることが重要

以前、おださんからこのような資料を紹介してもらいました。

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か？ from kwatch

www.slideshare.net
この中では、クエリを分解、構築、抽象化できると便利ですよ。って書かれてました。ということで、これをLambdicSqlに取り入れてみました。

LambdicSqlで可能な分解、構築

サブクエリは前からありました。
今回は1,2を追加しました。サンプルコードが長くなるので、3回に分けて書きます。

2016-07-30

lambdicSql - CASE式に対応しました -

LambdicSql C#

LabdicSql_α0.0.39をリリースしました。CASEに対応しています。
www.nuget.org
迷いましたがWHENとTHENはC#上ではWhenThenで書くようにしました。それからENDは書かなくても良いようにしました。
SELECT句への合成はサブクエリと同じ書き方で書くようにしました。

検索ケース式

[TestMethod]
public void Case1()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Type = Case().
                        When(db.tbl_staff.id == 3).Then("x").
                        When(db.tbl_staff.id == 4).Then("y").
                        Else("z").
                    End()
        }).
        From(db.tbl_staff));
            
    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SQLです。ちょっと頑張って綺麗にタブが入るようにしました。この改善でサブクエリにも同様に改善されています。

SELECT
	CASE
		WHEN (tbl_staff.id) = (@p_0) THEN @p_1
		WHEN (tbl_staff.id) = (@p_2) THEN @p_3
		ELSE @p_4
	END AS Type
FROM tbl_staff

単純ケース式

public void Case2()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Type = Case(db.tbl_staff.id).
                        When(3).Then("x").
                        When(4).Then("y").
                        Else("z").
                    End()
        }).
        From(db.tbl_staff));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
	CASE tbl_staff.id
		WHEN @p_0 THEN @p_1
		WHEN @p_2 THEN @p_3
		ELSE @p_4
	END AS Type
FROM tbl_staff

履歴

2016/09/02 β版対応

2016-07-24

LambdicSql -DistinctとAll

LambdicSql C#

α0.0.36をリリースしました。
www.nuget.org

DistinctとAll

enumで指定できるように変更しました。

Select文

[TestMethod]
public void Distinct()
{
    var query = Db<DB>.Sql(db =>
        Select(AggregatePredicate.Distinct, new
        {
            id = db.tbl_remuneration.staff_id
        }).
        From(db.tbl_remuneration));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);

    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT DISTINCT
tbl_remuneration.staff_id AS id
FROM tbl_remuneration

集計関数

[TestMethod]
public void GroupByPredicateDistinct()
{
    var query = Db<DB>.Sql(db =>
        Select(new SelectedData()
        {
            Name = db.tbl_staff.name,
            Count = (int)Count(AggregatePredicate.Distinct, db.tbl_remuneration.money),
            Total = Sum(AggregatePredicate.Distinct, db.tbl_remuneration.money)
        }).
        From(db.tbl_remuneration).
            Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
        GroupBy(db.tbl_staff.id, db.tbl_staff.name));

    //文字列化
    Debug.Print(query.Build(typeof(SqlConnection)).Text);
            
    //Dapperを使っているなら、以下のように実行できます
    var datas = _connection.Query(query).ToList();
}

SELECT
tbl_staff.name AS Name,
	COUNT(DISTINCT tbl_remuneration.money) AS Count,
	SUM(DISTINCT tbl_remuneration.money) AS Total
FROM tbl_remuneration
	JOIN tbl_staff ON (tbl_remuneration.staff_id) = (tbl_staff.id)
GROUP BY tbl_staff.id, tbl_staff.name

ささいなことですが。

Windowsアプリテスト自動化ライブラリFriendly開発者の日記です。

LambdicSql - 生まれ変わりました -

変更一覧

書き心地 100%Up(当社比)

Sql.Create()でまとめて書けるようになった

using staticを使いやすく変更

組み合わせ自由自在

条件式作成も書きやすく、直感的に

lambdicSql - Queryの自由な組み立て - ③サブクエリ -

Select句

Where句

From句

履歴

lambdicSql - Queryの自由な組み立て - ②Expression単位 -

条件文を構築しやすくるためのヘルパ

履歴

lambdicSql - Queryの自由な組み立て - ①句単位 -

履歴

lambdicSql - Queryの自由な組み立て -

Queryは組み合わせることが重要

LambdicSqlで可能な分解、構築

lambdicSql - CASE式に対応しました -

検索ケース式

単純ケース式

履歴

LambdicSql -DistinctとAll

DistinctとAll

Select文

集計関数