ActiveRecordのorderメソッドを使ったソート機能で、予想した動きをしなかったので調べてみました。

環境

Ruby: 2.6.3
Rails: 5.2.3

前準備

簡単なpostsテーブルを想定してみます。

postsテーブル

id	title
1	最初の投稿
2	2番目の投稿
3	3番目の投稿

コントローラのindexアクションで、idの降順（値が大きい順番）にします。

app/controllers/posts_controller.rb

  def index
    @posts= Post.order(id: :desc)
  end

これにより、postsテーブルの中身がidの降順で表示されます。

SQLも以下の通り。

疑問１

ここで、以下の要件が付与されました。
- 通常はidの降順で表示させるが、titleの昇順でソートさせるボタンもつけること

そこで以下のようにしました。

app/views/posts/index.html.erb

<%= link_to 'Titleでソート', posts_path(title_sort: :true) %>

app/controllers/posts_controller.rb

  def index
    @posts = Post.order(id: :desc)
    if params[:title_sort]
      @posts = @posts.order(title: :asc)
    end
  end

アプリを起動してTitleでソートをクリックしましたが、タイトルの昇順でソートされない！なんで？？？

検証&&仮説

そこでTitleでソートをクリックしたときのSQLをログで確認しました。

SQLを見ると
①まずidの降順（DESC）でソート
②続いてtitleの昇順（ASC）でソート
をしています。
一見、問題なさそうなのですが、ORDER BY句で複数の条件を指定した場合、①のほうが優先順位が高いことになるので、①でソートした結果をくつがえして②の結果を反映させることはできないのです。

解決方法

ですので、これを解決するには以下のようにします。

app/controllers/posts_controller.rb

  def index
    @posts = Post.order(id: :desc)
    if params[:title_sort]
      @posts = Post.order(title: :asc)
    end
  end

2つ目のorderメソッドのレシーバをPostにするのです。
これでtitleの昇順にソートされました！

疑問2

ただ、ここで疑問が。
①のソートをしているのは、コントローラの

@posts = Post.order(id: :desc)

の場所であり、②のソートをしているのは、コントローラの

@posts = @posts.order(title: :asc)

です。それぞれ独立したコードなのに、なぜ①と②をまとめたSQLで実行しているのでしょう？
（てか、まとめて実行しなきゃ、タイトルでもソートできるんじゃね？）
　

検証&&仮説

これはおそらくこういう理屈なのだと思います。

@posts = @posts.order(title: :asc)

のコードを実行しようとして、右辺の@postsを参照したときに「＠postsってなんだっけ」となり、そこで

@posts = Post.order(id: :desc)

にたどりつく。
「@postsというのは、Post.order(id: :desc)の結果なのだな」
ということになり、さらにもともとのコードも実行するので、まとめたSQLが実行されてしまう（たぶん）。
　
その証拠に

app/controllers/posts_controller.rb

  def index
    @posts = Post.order(id: :desc)
    if params[:title_sort]
      @posts = @posts.order(title: :asc)
    end
    @posts = @posts.order(id: :asc)
  end

のようにしてTitleでソートをクリックすると、SQLがずらずらとつながりました。

これがオブジェクトの参照渡し、というやつでしょうか？

※もし認識が間違っていましたらご指摘お願いしますm(__)m

• メッセージの通り (行ではなく) 列定義の合計サイズが大きすぎる。
• エンコードやバイトの扱いによって varchar(255) この合計がそのままサイズに反映されるわけではないはず

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs'

Original by Github issue

https://github.com/YumaInaura/YumaInaura/issues/1949

やりたいこと

カラムごとに条件を変えたSELECT文を書きたい。

SELECT (出力データ) 
FROM (テーブル) 
WHERE (出力条件) 
GROUP BY (指定カラム) 
ORDER BY (指定カラム)

上記のSELECT文はよく書くと思うんですが、
カラムごとに条件を変えたSELECT文ってどうすればいいんだろうと考えたとき、
簡単にできそうと思いあれこれ試すも、WHERE句が1つしか書けなくてムズムズ…
(SQL力なさすぎるがゆえ)

ちょっと考えたら…

やりたいイメージはできていてもなかなか形にならなかったんですが、
case文みたいなものはSQLにはないのかと。

早速ググりました。

ありました…

（知ってる人からしたら当然ですが…）

CASEを使ってみる

指定した複数の条件ごとにカラムをもってデータを出力したい。

例のテーブル：果物の売上データ(sales_data)
→顧客番号(c_num)、年(year)、月(month)、日(date)、商品(item)、売上数(s_qty)

SELECT date, 
COUNT(DISTINCT c_num) AS 購入顧客数 , SUM(s_qty) AS 売上数 
FROM sales_data 
WHERE year = 2019 AND month = 05 AND item = apple 
GROUP BY date 
ORDER BY date;

この場合、year = 2019 AND month = 05 AND item = apple以外の条件の出力が1つのSELECT文では厳しいです。

SELECT date, 
COUNT(DISTINCT CASE WHEN year = 2019 AND month = 05 AND item = apple then c_num end) AS "りんごを購入した顧客数_201905", 
COUNT(DISTINCT CASE WHEN year = 2019 AND month = 05 AND item = orange then c_num end) AS "オレンジを購入した顧客数_201904", 
SUM(CASE WHEN year = 2019 AND month = 05 AND item = apple then s_qty end) AS "りんごの売上数_201905", 
SUM(CASE WHEN year = 2019 AND month = 05 AND item = orange then s_qty end) AS "オレンジの売上数_201904"
FROM sales_data  
GROUP BY date 
ORDER BY date;

COUNTやSUMの中でCASE WHENで条件を記述することでカラムごとに条件を変更することができ、横に並べて出力することができます。
CASEの場合はWHEREではなく、WHENという点に注意。

おわりに

SQLは苦手意識あったんですが、こうして色々調べて自分で試すと理解が深まって良いと思いました。

問題

次のコードのように LINQ to SQL で NTEXT 型のカラムを where で扱おうとしても

using(var dc = new DataContext())
{
    var NoDescriptionApples = dc.Apples
        .Where(i => "" == i.description).ToList(); // NG
    ...
}

そもそも素の SQL 文上で NTEXT 型の文字列比較はサポートされていない為、実行時エラーになります。

対処方法

NTEXT 型のまま比較しようするから弾かれるので、 Substring() で比較対象となる文字列より１文字だけ多く切り出して比較します。

using(var dc = new DataContext())
{
    var NoDescriptionApples = dc.Apples
        .Where(i => "" == i.description.Substring(0, 1)).ToList(); // OK
    ...
}

C# 上での Substring() は SQL の SUBSTRING に翻訳され、 SUBSTRING に NTEXT 型のデータを与えて実行した場合の戻り型は NVARCHAR 型になる為、普通に文字列比較ができるようになります。

その他の対処方法

今回のようなケースではそもそも NTEXT 型の使用を諦めて NVARCHAR 型にしてしまうのが一般的なようです。

注) ご本人様に許可もらってないので、怒られたら消します。

参考資料

「写経」を自動化し、オートで功徳を積める仕組みを作ってみたのでございます。

実行風景

ちょっと説明

Go には pyautogui のような便利なパッケージがなさそうだったが、今回のような写経の自動化の場合は、「キーボード入力の仮想化」と「クリップボードへの書き込み」ができればよかったので、それらができるパッケージで大体した。

一番大変だったところは、rune と キーマップのマッピングを書くところ。

DoSyakyou の部分はオリジナルと同じなのでだいぶ楽できた。

sleepが短すぎると正確に動作しないので、オリジナルより気持ち多めにとってある。

コード

package main

import (
    "time"

    "github.com/atotto/clipboard"
    "github.com/micmonay/keybd_event"
)

var (
    kb    keybd_event.KeyBonding
    kbmap map[rune]int
)

func press(ji rune) {
    kb.SetKeys(kbmap[ji])
    kb.Launching()
}

func pressESC(count int, sleeptime time.Duration) {
    for i := 0; i < count; i++ {
        kb.SetKeys(keybd_event.VK_ESC)
        kb.Launching()
    }
}

func pressCTRL_V() {
    kb.SetKeys(keybd_event.VK_V)
    kb.HasCTRL(true)
    kb.Launching()
    kb.HasCTRL(false)
}

func pressEnter() {
    kb.SetKeys(keybd_event.VK_ENTER)
    kb.Launching()
}

func GijiHenkan(kanji, roumaji string, sleeptime time.Duration) {
    for _, ji := range roumaji {
        press(ji)
        time.Sleep(sleeptime)
    }

    time.Sleep(sleeptime)

    clipboard.WriteAll(kanji)

    pressESC(2, sleeptime)

    pressCTRL_V()

    time.Sleep(sleeptime)
}

func Kaigyou(kaigyousleeptime time.Duration) {
    pressEnter()
    time.Sleep(kaigyousleeptime)
}

func DoSyakyou(sleeptime, kaigyousleeptime time.Duration) {
    GijiHenkan("摩", "ma", sleeptime)
    GijiHenkan("訶", "ka", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mi", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("心", "sinn", sleeptime)
    GijiHenkan("経", "gyou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("観", "kann", sleeptime)
    GijiHenkan("自", "ji", sleeptime)
    GijiHenkan("在", "zai", sleeptime)
    GijiHenkan("菩", "bo", sleeptime)
    GijiHenkan("薩", "satu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("行", "gyou", sleeptime)
    GijiHenkan("深", "jinn", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mixtu", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("時", "ji", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("照", "syou", sleeptime)
    GijiHenkan("見", "ken", sleeptime)
    GijiHenkan("五", "go", sleeptime)
    GijiHenkan("蘊", "unn", sleeptime)
    GijiHenkan("皆", "kai", sleeptime)
    GijiHenkan("空", "kuu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("度", "do", sleeptime)
    GijiHenkan("一", "ixtu", sleeptime)
    GijiHenkan("切", "sai", sleeptime)
    GijiHenkan("苦", "ku", sleeptime)
    GijiHenkan("厄", "yaku", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("舍", "sya", sleeptime)
    GijiHenkan("利", "ri", sleeptime)
    GijiHenkan("子", "si", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("色", "siki", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("異", "i", sleeptime)
    GijiHenkan("空", "kuu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("空", "kuu", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("異", "i", sleeptime)
    GijiHenkan("色", "siki", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("色", "siki", sleeptime)
    GijiHenkan("即", "soku", sleeptime)
    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("空", "kuu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("空", "kuu", sleeptime)
    GijiHenkan("即", "soku", sleeptime)
    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("色", "siki", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("受", "jyu", sleeptime)
    GijiHenkan("想", "sou", sleeptime)
    GijiHenkan("行", "gyou", sleeptime)
    GijiHenkan("識", "siki", sleeptime)
    GijiHenkan("亦", "yaku", sleeptime)
    GijiHenkan("復", "bu", sleeptime)
    GijiHenkan("如", "nyo", sleeptime)
    GijiHenkan("是", "ze", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("舍", "sya", sleeptime)
    GijiHenkan("利", "ri", sleeptime)
    GijiHenkan("子", "si", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("諸", "syo", sleeptime)
    GijiHenkan("法", "hou", sleeptime)
    GijiHenkan("空", "kuu", sleeptime)
    GijiHenkan("相", "sou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("生", "syou", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("滅", "metu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("垢", "ku", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("浄", "jyou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("増", "zou", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("減", "genn", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("故", "ko", sleeptime)
    GijiHenkan("空", "kuu", sleeptime)
    GijiHenkan("中", "tyuu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("色", "siki", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("受", "jyu", sleeptime)
    GijiHenkan("想", "sou", sleeptime)
    GijiHenkan("行", "gyou", sleeptime)
    GijiHenkan("識", "siki", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("眼", "genn", sleeptime)
    GijiHenkan("耳", "ni", sleeptime)
    GijiHenkan("鼻", "bi", sleeptime)
    GijiHenkan("舌", "zextu", sleeptime)
    GijiHenkan("身", "sinn", sleeptime)
    GijiHenkan("意", "i", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("色", "siki", sleeptime)
    GijiHenkan("声", "syou", sleeptime)
    GijiHenkan("香", "kou", sleeptime)
    GijiHenkan("味", "mi", sleeptime)
    GijiHenkan("触", "soku", sleeptime)
    GijiHenkan("法", "hou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("眼", "genn", sleeptime)
    GijiHenkan("界", "kai", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("乃", "nai", sleeptime)
    GijiHenkan("至", "si", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("意", "i", sleeptime)
    GijiHenkan("識", "siki", sleeptime)
    GijiHenkan("界", "kai", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("明", "myou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("亦", "yaku", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("明", "myou", sleeptime)
    GijiHenkan("尽", "jinn", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("乃", "nai", sleeptime)
    GijiHenkan("至", "si", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("老", "rou", sleeptime)
    GijiHenkan("死", "si", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("亦", "yaku", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("老", "rou", sleeptime)
    GijiHenkan("死", "si", sleeptime)
    GijiHenkan("尽", "jinn", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("苦", "ku", sleeptime)
    GijiHenkan("集", "syuu", sleeptime)
    GijiHenkan("滅", "metu", sleeptime)
    GijiHenkan("道", "dou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("智", "ti", sleeptime)
    GijiHenkan("亦", "yaku", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("得", "toku", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("以", "i", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("所", "syo", sleeptime)
    GijiHenkan("得", "toku", sleeptime)
    GijiHenkan("故", "kou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("菩", "bo", sleeptime)
    GijiHenkan("提", "dai", sleeptime)
    GijiHenkan("薩", "saxtu", sleeptime)
    GijiHenkan("埵", "ta", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("依", "e", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mixtu", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("故", "ko", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("心", "sinn", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("罣", "kei", sleeptime)
    GijiHenkan("礙", "ge", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("罣", "kei", sleeptime)
    GijiHenkan("礙", "ge", sleeptime)
    GijiHenkan("故", "ko", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("有", "u", sleeptime)
    GijiHenkan("恐", "ku", sleeptime)
    GijiHenkan("怖", "hu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("遠", "onn", sleeptime)
    GijiHenkan("離", "ri", sleeptime)
    GijiHenkan("一", "ixtu", sleeptime)
    GijiHenkan("切", "sai", sleeptime)
    GijiHenkan("顛", "tenn", sleeptime)
    GijiHenkan("倒", "dou", sleeptime)
    GijiHenkan("夢", "mu", sleeptime)
    GijiHenkan("想", "sou", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("究", "ku", sleeptime)
    GijiHenkan("竟", "kyou", sleeptime)
    GijiHenkan("涅", "ne", sleeptime)
    GijiHenkan("槃", "hann", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("三", "sann", sleeptime)
    GijiHenkan("世", "ze", sleeptime)
    GijiHenkan("諸", "syo", sleeptime)
    GijiHenkan("仏", "butu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("依", "e", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mixtu", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("故", "ko", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("得", "toku", sleeptime)
    GijiHenkan("阿", "a", sleeptime)
    GijiHenkan("耨", "noku", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("三", "sann", sleeptime)
    GijiHenkan("藐", "myaku", sleeptime)
    GijiHenkan("三", "sann", sleeptime)
    GijiHenkan("菩", "bo", sleeptime)
    GijiHenkan("提", "dai", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("故", "ko", sleeptime)
    GijiHenkan("知", "ti", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mixtu", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("大", "dai", sleeptime)
    GijiHenkan("神", "jinn", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("大", "dai", sleeptime)
    GijiHenkan("明", "myou", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("上", "jyou", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("是", "ze", sleeptime)
    GijiHenkan("無", "mu", sleeptime)
    GijiHenkan("等", "tou", sleeptime)
    GijiHenkan("等", "dou", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("能", "nou", sleeptime)
    GijiHenkan("除", "jyo", sleeptime)
    GijiHenkan("一", "ixtu", sleeptime)
    GijiHenkan("切", "sai", sleeptime)
    GijiHenkan("苦", "ku", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("真", "sinn", sleeptime)
    GijiHenkan("実", "jitu", sleeptime)
    GijiHenkan("不", "hu", sleeptime)
    GijiHenkan("虚", "ko", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("故", "ko", sleeptime)
    GijiHenkan("説", "setu", sleeptime)
    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("蜜", "mixtu", sleeptime)
    GijiHenkan("多", "ta", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("即", "soku", sleeptime)
    GijiHenkan("説", "setu", sleeptime)
    GijiHenkan("呪", "syu", sleeptime)
    GijiHenkan("曰", "watu", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("羯", "gya", sleeptime)
    GijiHenkan("諦", "tei", sleeptime)
    GijiHenkan("羯", "gya", sleeptime)
    GijiHenkan("諦", "tei", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("羯", "gya", sleeptime)
    GijiHenkan("諦", "tei", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("波", "ha", sleeptime)
    GijiHenkan("羅", "ra", sleeptime)
    GijiHenkan("僧", "sou", sleeptime)
    GijiHenkan("羯", "gya", sleeptime)
    GijiHenkan("諦", "tei", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("菩", "bo", sleeptime)
    GijiHenkan("提", "ji", sleeptime)
    GijiHenkan("薩", "so", sleeptime)
    GijiHenkan("婆", "wa", sleeptime)
    GijiHenkan("訶", "ka", sleeptime)
    Kaigyou(kaigyousleeptime)

    GijiHenkan("般", "hann", sleeptime)
    GijiHenkan("若", "nya", sleeptime)
    GijiHenkan("心", "sinn", sleeptime)
    GijiHenkan("経", "gyou", sleeptime)
    Kaigyou(kaigyousleeptime)
    Kaigyou(kaigyousleeptime)
    Kaigyou(kaigyousleeptime)
}

func init() {
    kb, _ = keybd_event.NewKeyBonding()

    kbmap = make(map[rune]int, 0)

    kbmap['a'] = keybd_event.VK_A
    kbmap['b'] = keybd_event.VK_B
    kbmap['c'] = keybd_event.VK_C
    kbmap['d'] = keybd_event.VK_D
    kbmap['e'] = keybd_event.VK_E
    kbmap['f'] = keybd_event.VK_F
    kbmap['g'] = keybd_event.VK_G
    kbmap['h'] = keybd_event.VK_H
    kbmap['i'] = keybd_event.VK_I
    kbmap['j'] = keybd_event.VK_J
    kbmap['k'] = keybd_event.VK_K
    kbmap['l'] = keybd_event.VK_L
    kbmap['m'] = keybd_event.VK_M
    kbmap['n'] = keybd_event.VK_N
    kbmap['o'] = keybd_event.VK_O
    kbmap['p'] = keybd_event.VK_P
    kbmap['q'] = keybd_event.VK_P
    kbmap['r'] = keybd_event.VK_R
    kbmap['s'] = keybd_event.VK_S
    kbmap['t'] = keybd_event.VK_T
    kbmap['u'] = keybd_event.VK_U
    kbmap['v'] = keybd_event.VK_V
    kbmap['w'] = keybd_event.VK_W
    kbmap['x'] = keybd_event.VK_X
    kbmap['y'] = keybd_event.VK_Y
    kbmap['z'] = keybd_event.VK_Z
}

func main() {
    time.Sleep(5 * time.Second)

    sleeptime := 100 * time.Millisecond
    kaigyousleeptime := 200 * time.Millisecond

    DoSyakyou(sleeptime, kaigyousleeptime)
}

gormを使ったコードのテストをするとき、Dockerなどを使ってDBを立ち上げてテストする必要がありますが、go-sqlmockを使うと、実際のDBの代わりにモックを使ってテストすることができます。

この記事は、簡単なモデルを例にしたサンプルコードです。

パッケージのインストール

gormとgo-sqlmockは以下のコマンドでインストールできます。

go get -u github.com/jinzhu/gorm
go get -u github.com/DATA-DOG/go-sqlmock

テスト対象のソース

RepositoryパターンでCreateとGetをGormで実装した例です。

user/user.go

package user

import (
    "github.com/jinzhu/gorm"
)

type User struct {
    ID   string `gorm:"primary_key"`
    Name string
}

type Repository struct {
    *gorm.DB
}

func (p *Repository) Create(id string, name string) error {
    person := &User{
        ID:   id,
        Name: name,
    }
    return p.DB.Create(person).Error
}

func (p *Repository) Get(id string) (*User, error) {
    var person User

    err := p.DB.Where("id = ?", id).Find(&person).Error
    return &person, err
}

テストコード

先程のコードの、CreateとGetをそれぞれテストしてみます。

まず、DBモックとGORMのオープンです。

user/user_test.go

package user

import (
    "regexp"
    "testing"

    sqlmock "github.com/DATA-DOG/go-sqlmock"
    "github.com/jinzhu/gorm"
)

func getDBMock() (*gorm.DB, sqlmock.Sqlmock, error) {
    db, mock, err := sqlmock.New()
    if err != nil {
        return nil, nil, err
    }

    gdb, err := gorm.Open("postgres", db)
    if err != nil {
        return nil, nil, err
    }
    return gdb, mock, nil
}

これを使ってテストを書いてみます。

Createのテスト

user/user_test.go

func TestCreate(t *testing.T) {
    db, mock, err := getDBMock()
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()
    db.LogMode(true)

    r := Repository{DB: db}

    id := "2222"
    name := "BBBB"

    // Mock設定
    mock.ExpectQuery(regexp.QuoteMeta(
        `INSERT INTO "users" ("id","name") VALUES ($1,$2)
         RETURNING "users"."id"`)).
        WithArgs(id, name).
        WillReturnRows(
            sqlmock.NewRows([]string{"id"}).AddRow(id))

    // 実行
    err = r.Create(id, name)
    if err != nil {
        t.Fatal(err)
    }
}

mock.ExpectQueryのところで、期待するSQLとパラメータと、実行結果のレコードを設定しています。
実際に実行した時に、この期待結果と異なる場合、以下のようなエラーが返ります。

--- FAIL: TestCreate (0.00s)
    user_test.go:48: Query 'INSERT INTO "users" ("id","name") VALUES ($1,$2) RETURNING "users"."id"', arguments do not match: argument 0 expected [string - 2222X] does not match actual [string - 2222]

Getのテスト

user/user_test.go

func TestGet(t *testing.T) {
    db, mock, err := getDBMock()
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()
    db.LogMode(true)

    r := Repository{DB: db}

    id := "1111"
    name := "AAAA"

    // Mock設定
    mock.ExpectQuery(regexp.QuoteMeta(
        `SELECT * FROM "users" WHERE (id = $1)`)).
        WithArgs(id).
        WillReturnRows(sqlmock.NewRows([]string{"id", "name"}).
            AddRow(id, name))

    // 実行
    res, err := r.Get(id)
    if err != nil {
        t.Fatal(err)
    }

    if res.ID != id || res.Name != name {
        t.Errorf("取得結果不一致  %+v", res)
    }
}