TTYF ~earlgrey の雑記~

主に自分用メモとか

VB の論理演算における三値論理に注意

この記事は、Visual Basic Advent Calendar 2016 の 23 日目のエントリーです。22 日目は sutefu さんの なんちゃって仮想労働者を作る2 でした。

三値論理とは

最初に、そもそも三値論理とは何か、というところの説明をします。通常プログラミング言語において、条件判定等で用いる論理値としては、真 (true)/偽 (false) の 2 つがよく用いられていますが、これにもうひとつ不明 (unknown) を加えた 3 つの値で論理体系を構成しているものが三値論理です。
SQL ではわりと馴染み深いとともに、直感的でないところがあるがゆえにハマりやすいポイントでもあります。

具体的に、三値論理での演算結果がどのようになるかを表で記します。これを真理値表といいます。

NOT
true false
false true
unknown unknown

AND true false unknown
true true false unknown
false false false false
unknown unknown false unknown

OR true false unknown
true true true true
false true false unknown
unknown true unknown unknown

XOR true false unknown
true false true unknown
false true false unknown
unknown unknown unknown unknown

NOT はともかくとして、他のやつはパッと見いまいち覚えづらいですね。なるほど直感的じゃあない...。
ただ、AND だと false→unknown の順で強く、OR だと true→unknown の順で強く、XOR だと unknown が強い、と覚えておけば導きやすいかもしれません?

Nullable 型と三値論理

実は .NET においても、Nullable 型、というか Nullable(Of Boolean)(C# だと Nullable<bool>)とともに三値論理が登場しました。動作については前述の真理値表の unknown を Nothing(C# だと null)に置き換えたものと同じですが、一応 VB 表記でもう一度書いておきます。

Not
True False
False True
Nothing Nothing

And/AndAlso True False Nothing
True True False Nothing
False False False False
Nothing Nothing False Nothing

Or/OrElse True False Nothing
True True True True
False True False Nothing
Nothing True Nothing Nothing

XOr True False Nothing
True False True Nothing
False True False Nothing
Nothing Nothing Nothing Nothing

VB における三値論理

さてここからが本題です。この三値論理ですが、VB においては非常に非直感的で凶悪な振る舞いをしますので、良く理解しておかないと SQL の三値論理並みにハマります。この先は注意すべきポイントについて、私が把握している限り紹介していきます。
なお、これらは VB 固有の問題(?)で、同じ .NET 系の言語でも C# には当てはまらなかったりします。今回は VB の Advent Calendar なので C# には触れませんが...。

制御文における三値論理

If 等の制御文において、Nothing は False と同等に扱われます。そして何より重要なのが、Not Nothing は True ではない、ということです。例えば次のコードの実行結果は、どちらも「不一致」を出力します。

Dim b As Boolean? = Nothing

If b Then
    Console.WriteLine("一致")
Else
    ' Nothing は True ではないのでこちら
    Console.WriteLine("不一致")
End If

If Not b Then
    Console.WriteLine("一致")
Else
    ' Not Nothing はやはり Nothing であり True ではないのでこちら
    Console.WriteLine("不一致")
End If

このあたりは MSDN のリファレンスでも説明が十分でなかった時期が存在していて、例えば While だと Visual Studio 2005 の時点で明記されていましたが、If だと Visual Studio 2012 になってやっと明記されました。

Nullable 型との比較演算子は Nullable(Of Boolean) を返す

これも注意が必要で、個人的には凶悪度ではさっきのより上だと思っています。特に直感的でなくハマりやすいのが、True/False と Nothing の等値・非等値比較も Nothing が返ってくる(True/False ではない)こと、さらには Nothing 同士の比較も常に Nothing であることでしょうか。例えば次のコードの実行結果も、やはりすべて「不一致」を出力します。マジですか...。

Dim b As Boolean? = Nothing

' 比較 1
If b <> True Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 2
If Not (b = True) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 3
If b <> False Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 4
If Not (b = False) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 5
If b = b Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 6
If b <> b Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

これは最新の Visual Studio 15 のリファレンス にも書かれていません。
それより問題なのが、この動作が Equals メソッドと整合性が取れていないことでしょうか。先ほどのサンプルコードを Equals メソッドを使って書き換えてみましょう。

Dim b As Boolean? = Nothing

' 比較 1,2
If Not b.Equals(True) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 3,4
If Not b.Equals(False) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 5
If b.Equals(b) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 6
If Not b.Equals(b) Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

この実行結果は、比較 6 のみ「不一致」、他は「一致」が出力されます。これなら直感的ですし、比較演算子を使った場合でも同じ結果になってほしいものですが...。

内部的な仕掛け

なぜこのように比較演算子と Equal メソッドの不整合が起きているかというと、VB コンパイラが比較文を書き換えているからです。
次のコードは、2 つ前のサンプルコードを一旦 Visual Studio 2015 でコンパイルしてから ILSpy で逆アセンブルしたものです。

Dim b As Boolean? = Nothing

' 比較 1
Dim valueOrDefault As Boolean = (Not b).GetValueOrDefault()
If valueOrDefault Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

Dim flag As Boolean? = b

' 比較 2
Dim flag2 As Boolean? = If(flag.HasValue,
                           New Boolean?(flag.GetValueOrDefault()),
                           Nothing)
Dim valueOrDefault2 As Boolean = (If(flag2.HasValue,
                                     New Boolean?(Not flag2.GetValueOrDefault()),
                                     flag2)).GetValueOrDefault()
If valueOrDefault2 Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 3
flag2 = b
Dim valueOrDefault3 As Boolean = (If(flag2.HasValue,
                                  New Boolean?(flag2.GetValueOrDefault()),
                                  Nothing)).GetValueOrDefault()
If valueOrDefault3 Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 4
flag2 = Not b
Dim valueOrDefault4 As Boolean = (If(flag2.HasValue,
                                     New Boolean?(Not flag2.GetValueOrDefault()),
                                     flag2)).GetValueOrDefault()
If valueOrDefault4 Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 5
Dim valueOrDefault5 As Boolean = (If((b.HasValue And b.HasValue),
                                  New Boolean?(b.GetValueOrDefault() = b.GetValueOrDefault()),
                                  Nothing)).GetValueOrDefault()
If valueOrDefault5 Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

' 比較 6
Dim valueOrDefault6 As Boolean = (If((b.HasValue And b.HasValue),
                                     New Boolean?(b.GetValueOrDefault() <> b.GetValueOrDefault()),
                                     Nothing)).GetValueOrDefault()
If valueOrDefault6 Then
    Console.WriteLine("一致")
Else
    Console.WriteLine("不一致")
End If

うわあ、なんだか凄いことになっちゃったぞ。
でも内容を理解する必要はなくて、三値論理を実現するために何やら面倒なことをやっているんだな、ぐらいの認識で良いと思います。

わざわざここまでして SQL でも賛否が別れる三値論理を取り込まなくていいのに、という気がしています。Equals および等値演算子 (==) 実装のガイドライン に書かれている、「等値演算子 (==) を実装する場合は、常に Equals メソッドをオーバーライドし、同じ処理を実行させます。」という基準にも反していますし。

Dictionary のキーとしては一致と見做されるのに等値演算子では別物として扱われるということが起きえるわけで、結構問題なはずなんですが、なぜこんな言語仕様になっているのか...。条件文や比較演算子で三値論理を適用しない Option を新設してほしい、みたいなのって、Roslyn へのプルリクエストではなく単なる要望として出せないものでしょうか。

LINQ での取り扱い

LINQ to Objects で、Nullable 型のメンバを含んだエンティティクラスに対して操作を行うこともあるかと思いますが、そんなときにも Nothing との比較は Nothing であることを頭に入れておく必要があります。<> 演算子を用いるときは要注意です。

まずはクエリ式の場合から行ってみましょう。

Dim numbers As Integer?() = {1, 2, Nothing, 3}
Dim filtered = From number In numbers Where number <> 2 Select number

Console.WriteLine(String.Join(",", filtered))

このコードの出力結果は「1,3」になります。「1,,3」ではありません。2 と一緒に Nothing も除外されているわけです。

次に拡張メソッドで書いてみましょう。

Dim numbers As Integer?() = {1, 2, Nothing, 3}
Dim filtered = numbers.Where(Function(x) x <> 2)

Console.WriteLine(String.Join(",", filtered))

こちらは Ontion Strict の設定によって結果が変わってきます。
まず Option Strict On の場合、こちらはコンパイルエラーが発生します。問題なのは Where メソッドの引数で、ここには Func(Of TSource, Boolean) が要求されるのに、実際は Func(Of TSource, Boolean?) が与えられているためです。エラーメッセージは「Option Strict On では 'Boolean?' から 'Boolean' への暗黙的な変換は許可されていません。」と出力されます。
次に Option Strict Off の場合は、コンパイルは通りますが InvalidOperationException が発生します。Where メソッドの引数が暗黙のうちに Function(x) x.Value <> 2 と書き換えられるため、Nothing を処理しようとしたときに Value を参照できずにエラーになる、といった具合です。

エラーを発生させないためには、次のように書く必要があります。これの出力結果は、ひとつめが「1,,3」、ふたつめが「1,3」です。

Dim numbers As Integer?() = {1, 2, Nothing, 3}

' 結果に Nothing を含める場合
Dim filtered = numbers.Where(Function(x) Not x.HasValue OrElse x.Value <> 2)
Console.WriteLine(String.Join(",", filtered))

' 結果に Nothing を含めない場合
Dim filtered2 = numbers.Where(Function(x) x.HasValue AndAlso x.Value <> 2)
Console.WriteLine(String.Join(",", filtered2))

ちなみに C# では、同じように書いたつもりでも結果が異なるので、これまた注意が必要です。下記のようにクエリ式、拡張メソッドどちらを用いても出力結果は「1,,3」です。

int?[] numbers = {1, 2, null, 3};

// クエリ式
var filtered = from number in numbers where number != 2 select number;
Console.WriteLine(String.Join(",", filtered));

// 拡張メソッド(HasValue をチェックしなくてもエラーにならない)
var filtered2 = numbers.Where(x => x != 2);
Console.WriteLine(String.Join(",", filtered2));

あ、最初に C# には触れないといったのに触れちゃいました。それはさておき、これ、C#er が何らかの理由で VB を書くことになったときにめちゃ戸惑うと思います。やっぱり三値論理を無効化する Option の登場を強く望みます。

VB でインターフェイスの明示的実装

この記事は、Visual Basic Advent Calendar 2016 の 17 日目のエントリーです。16 日目は mmYYmmdd さんの VBAHaskell での関数定義 でした。

今回紹介するネタは特に目新しいものではないのですが、VBインターフェイスの明示的実装方法を調べるときのググラビリティ向上のために選んでみました。もっとも英語でググる と I'm Feeling Lucky 一発で解答にたどり着けたりしますけど。

現状ググラビリティがもうひとつなのは、Microsoft がなぜか VB においてはインターフェイスの明示的実装という用語を使っていないからです。理由はよくわかりませんが...。

インターフェイスの明示的実装とは

そもそも明示的なインターフェイスの実装って何?ということを念のため確認しておくと、あるインターフェイスのメンバに対して、実装しているクラスの参照変数ではなく、インターフェイスの参照変数を通じてしか呼び出すことができないようにすることです。言葉だけだとちょっとわかりにくいですが、後でサンプルを出します。

どういうときに使うかというと、フレームワークやライブラリで呼び出すために必要なんだけど、ユーザーコードからは使ってほしくないメンバーを実装するときが多いでしょうか。例えば ICloneable とかですかね。詳しい指針は、古い文書ですが MSDN の解説 をご覧ください。

VB での書き方

VBインターフェイスの明示的実装を行うためには、実装するメンバを Private で修飾します。

Module Module1

    Sub Main()
        Dim one = New Getter()
        Dim ione = DirectCast(one, IGetOne)

        ' コンパイルエラー
        Console.WriteLine(one.GetOne())

        ' OK
        Console.WriteLine(ione.GetOne())
    End Sub

End Module

Interface IGetOne
    Function GetOne() As Integer
End Interface

Class Getter
    Implements IGetOne

    ' Private にすると明示的実装
    Private Function GetOne() As Integer Implements IGetOne.GetOne
        Return 1
    End Function
End Class

VB ならではの機能?

上記では明示的実装のために Private を使いましたが、Protected や Friend を指定することもできます。つまり、次のようなこともできます。

Module Module1

    Sub Main()
        Dim one = New Getter()
        Dim ione = DirectCast(one, IGetOne)

        ' コンパイルエラー
        Console.WriteLine(one.GetOne())

        ' OK
        Console.WriteLine(ione.GetOne())
    End Sub

End Module

Interface IGetOne
    Function GetOne() As Integer
End Interface

Class Getter
    Implements IGetOne

    ' Protected で明示的実装することもできる
    Protected Function GetOne() As Integer Implements IGetOne.GetOne
        Return 1
    End Function
End Class

Class Getter2
    Inherits Getter

    Sub action()
        ' OK
        Dim one = MyBase.GetOne()
    End Sub
End Class

この例は Protected ですが、正直あまり使いどころがわかりません。ただ、Friend だとそれなりに便利かも、というかフレームワークやライブラリで呼び出すときだけインターフェイスにキャストしなくても良いようにする、という目的だとむしろ Private を使うよりも適切かもしれません。

ちなみにちょっと試した限りでは、C# では明示的実装を行うとそのメンバは常に private 相当になり、protected や internal 相当で明示的実装をすることはできないっぽいので、VB ならではの機能、ということになります。

Node.js の開発環境構築(Debian 編)

このところ業務で Node.js をやる可能性がほんのり出てきたので、開発環境の構築方法をメモしてみます。よくわかってないでとりあえずやってみてる感じなので、実用的かどうかはわかりませんが。

nvm(Node Version Manager) のインストール

Debian だったら何でも apt-get で入れたくなってしまいますが、Debian では node というパッケージ名およびコマンド名が昔から別のパッケージに使われているせいで、Node.js のものはどちらも nodejs というものに変更されており、色々面倒なんだとか。
それに Node.js はバージョンがいろいろあるので、独自のバージョン管理システムを使い、インストールおよびバージョンを随時切り替えて使用するのが一般的らしいです。
中でも nvm(Node Version Manager) というのがメジャーっぽいのでこれを使うことにします。これは各ユーザのホームディレクトリにインストールするので、root 権限はいらないし環境も汚れないのが特徴みたいです。まさに開発環境向けなんですかね。

以下概ね 公式サイト の記述に従ってインストールしていきます。

インストールは bashスクリプトをダウンロードしてきて、それを実行することで行うようです。cURL または Wget を使う方法が挙げられていますが、とりあえず cURL を使うことにします。もし入ってなければ、こちらは apt-get でインストールします。

$ sudo apt-get install curl

次の手順でダウンロードするスクリプトを見てみると、何だか Git を使ってるみたいです。別になくても別の方法でインストールしてくれるみたいですが、ここは素直に Git で行きましょう。というわけで万一入ってなければこれも apt-get。

$ sudo apt-get install git

以上で準備できましたので、cURLスクリプトをダウンロードして実行します。

$ curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.31.4/install.sh | bash

出力はこんな感じ。ところどころにある nodeuser というのは、スクリプトを実行したユーザ名です。

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  9135  100  9135    0     0  55274      0 --:--:-- --:--:-- --:--:-- 55701
=> Downloading nvm from git to '/home/nodeuser/.nvm'
=> Cloning into '/home/nodeuser/.nvm'...
remote: Counting objects: 5191, done.
remote: Compressing objects: 100% (27/27), done.
remote: Total 5191 (delta 16), reused 0 (delta 0), pack-reused 5164
Receiving objects: 100% (5191/5191), 1.39 MiB | 450.00 KiB/s, done.
Resolving deltas: 100% (3124/3124), done.
Checking connectivity... done.
* (detached from v0.31.4)
  master

=> Appending source string to /home/nodeuser/.bashrc
=> Close and reopen your terminal to start using nvm or run the following to use it now:

export NVM_DIR="/home/nodeuser/.nvm"
[ -s "$NVM_DIR/nvm.sh" ] && . "$NVM_DIR/nvm.sh"  # This loads nvm

.bashrc に必要な設定をしたのでターミナルを開き直すか、最後の 2 行のコマンドを実行すれば nvm が使えるよ!と言われているので、ここは開き直しで。

nvm を使った Node.js のインストール

nvm は複数バージョンの Node.js 環境を切り替えられるので、Node.js インストールの際もバージョンを指定します。以下のコマンドで現在インストール可能なバージョン一覧を出力します。

$ nvm ls-remote

省略しますが、山のように表示されてしまいました。一旦これはナシで。
先ほどのコマンドにはさらに追加のオプションがあって、LTS(long-term support)版、つまりある程度長期的に安心して使えるバージョンのものだけを表示することもできるようです。

$ nvm ls-remote --lts
         v4.2.0   (LTS: Argon)
         v4.2.1   (LTS: Argon)
         v4.2.2   (LTS: Argon)
         v4.2.3   (LTS: Argon)
         v4.2.4   (LTS: Argon)
         v4.2.5   (LTS: Argon)
         v4.2.6   (LTS: Argon)
         v4.3.0   (LTS: Argon)
         v4.3.1   (LTS: Argon)
         v4.3.2   (LTS: Argon)
         v4.4.0   (LTS: Argon)
         v4.4.1   (LTS: Argon)
         v4.4.2   (LTS: Argon)
         v4.4.3   (LTS: Argon)
         v4.4.4   (LTS: Argon)
         v4.4.5   (LTS: Argon)
         v4.4.6   (LTS: Argon)
         v4.4.7   (LTS: Argon)
         v4.5.0   (Latest LTS: Argon)

v4.5.0 が LTS 版では一番新しいっぽいですね。というわけでこいつをインストール...したいところですが、そもそも何で Node.js 環境を用意しようとしてるかというと、実は AWS Lambda で使おうとしてるからだったりします。AWS のマニュアル によると、Lambda の Node.js 対応バージョンは v0.10.36 と v4.3.2 ということなので(リンク先は英語ですが、日本語マニュアルだと v0.10.36 しか書かれていません)、今回は v4.3.2 をインストールします。

$ nvm install v4.3.2
Downloading https://nodejs.org/dist/v4.3.2/node-v4.3.2-linux-x64.tar.xz...
######################################################################## 100.0%
Now using node v4.3.2 (npm v2.14.12)
Creating default alias: default -> v4.3.2

なんか npm とかいうのが見えますが、これは JavaScript のパッケージマネージャで、Node.js をインストールすると一緒についてくるみたいです。Node.js 専用のものかはまだよく調べてないのでワカリマセン。

Visual Studio Code のインストール

開発環境というからにはエディタも必要。Linux だったらエディタは Vim を嗜むべきなのかもしれませんが、私は Vim なんてさっぱりでスクリーンエディタは nano しか使えないクソ雑魚ですので、もっと甘やかしてくれるエディタがいいです。
まぁ MS 系プログラマーとしてはやっぱり Visual Studio Code ですよね。Debian 向けに deb パッケージもちゃんと用意されてるのがありがたい。
これも cURL でゲットできるとスマートな感じですが、go.microsoft.com によるリダイレクトをうまく扱う方法がわからないので、素直にブラウザで公式サイト から deb パッケージをダウンロードしてきます。

ダウンロードできたら dpkg でインストール。アップデートの場合も同じコマンドで OK です。

$ sudo dpkg -i code_1.4.0-1470329130_amd64.deb

使用しているデスクトップ環境にもよりますが、インストールするとちゃんとスタートメニュー的なところに追加してくれるみたいです。これは LXDE の場合です。

f:id:s_earlgrey:20160821165715p:plain

もしくはターミナルから code コマンドでも起動できます。よくこのコマンド名空いてたな...。

$ code

Windows における Node.js 環境構築のガイドライン によると、Visual Studio Code では eslint/npm script runner/EditorConfig の 3 つの拡張機能がオススメされていますので、これもインストールしておきます。

f:id:s_earlgrey:20160821171300p:plainf:id:s_earlgrey:20160821171307p:plainf:id:s_earlgrey:20160821171500p:plain

最初はとりあえずこんなところでしょうか。実際に何か作ってみるといろいろ足りないものもわかってくるかもしれませんが、それはまぁぼちぼち。
(というか今兼務してる他案件でドはまり中で、Node.js どころではなかったりするのですが...。)

MDF ファイルのみからデータベースをリストアする方法

先日開発環境でやらかしたので、忘れないうちに自分用にメモします。

注意とお約束

これから記述する内容は無保証ですので、適用する場合は自己責任でお願いします。
また、最終手段としてとりあえずデータベースを認識させるためのものであり、障害発生時点の状態に戻せるわけではなく、データページに破損がある場合や、チェックポイント未実行のデータがある場合、それらは失われます。また、MDF ファイルが著しく破損している場合はこの方法でも認識できないことがあるかもしれません。

前提

アタッチ機能で MDF ファイルからデータベースをリストアする場合、普通は LDF ファイル(トランザクションログ)を一緒に指定します。
ただし、もともと LDF ファイルの内容がすべて MDF に適用されていた状態であれば、MDF 単独でもアタッチ可能です。この場合、アタッチ後には初期化された LDF ファイルが自動的に作成されます。

Management Studio の GUI では、「データベースの詳細」グリッドから LDF ファイルを選択して削除します。

f:id:s_earlgrey:20160515220129p:plain

ただし、LDF ファイルに未適用データが存在していた場合は、この方法ではアタッチできません。強行しようとしても、以下のようなダイアログが表示されてしまいます。

f:id:s_earlgrey:20160515194904p:plain

指示に従って詳細を確認すると、次のように表示されます。

f:id:s_earlgrey:20160515195132p:plain

LDF ファイルが正しくないと書いてありますね。でも LDF ファイルがない場合はしょうがないので、多少のデータ消失リスクは受け入れて、なんとかデータベースをリストアして生きてるデータだけでも救出したいところです。

リストア手順

それではこれから具体的な手順を書いていきます。

まずは、復旧したいデータベースと同じ名前で新しいデータベースを作成します。今回の例では「Northwind」です。
作成したら、SQL Server のサービスを一旦停止します。その後で、リストアしたい MDF ファイルを、先ほど作成したデータベースの MDF ファイルに上書きコピーして、SQL Server のサービスを起動します。

何をしているかというと、まずは何とかして SQL Server に MDF ファイルをロードしてもらう必要があるので、一旦破損していないデータベースを作っておいて、その MDF ファイルを差し替えるということをしています。

当然、これだけでリストアできるほど単純ではありません。起動後、Management Studio で接続すると、オブジェクトエクスプローラーには次のように「(復旧待ち)」と表示され、テーブル等を参照することはできません。

f:id:s_earlgrey:20160515202145p:plain

続いて以下の SQL を実行してデータベースを復旧します。Northwind のところは実際のデータベース名に読み替えてください。

USE master
GO

-- データベースを緊急モードに切り替える
ALTER DATABASE Northwind SET EMERGENCY
GO

-- データベースをシングルユーザモードに切り替える
ALTER DATABASE Northwind SET SINGLE_USER
GO

-- データベースを復旧する
DBCC CHECKDB ('Northwind',REPAIR_ALLOW_DATA_LOSS)
GO

-- データベースをオンラインに切り替える(省略可)
ALTER DATABASE Northwind SET ONLINE
GO

-- データベースをマルチユーザモードに切り替える
ALTER DATABASE Northwind SET MULTI_USER
GO

なお、緊急モードへ切り替えるのに次のような SQL を紹介しているサイトもあります。これは SQL Server 2000 まではこの方法を使っていたようですが、2005 以降では最後の UPDATE で「システム カタログへのアドホック更新は許可されません。」というエラーになります。
2005 以降は上記の方法で OK です。

sp_configure 'allow updates', 1
GO

RECONFIGURE
GO

UPDATE sys.sysdatabases SET STATUS = 32768 WHERE dbid = db_id('Northwind')
GO

DBCC CHECKDB は、MDF ファイルが大きいほど時間がかかりますのでしばらくお待ちください。完了すると以下のようなメッセージが表示されます。

メッセージ 5173、レベル 16、状態 1、行 12
データベースのプライマリ ファイルと一致しないファイルが 1 つ以上あります。データベースをアタッチする場合は、正しいファイルを使用して操作を再試行してください。これが既存のデータベースの場合は、ファイルが壊れている可能性があるので、バックアップから復元する必要があります。
ログ ファイル 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\northwind_log.ldf' がプライマリ ファイルと一致しません。このログ ファイルは別のデータベースまたはログから以前に再構築された可能性があります。
警告: データベース 'Northwind' のログが再構築されました。トランザクションの一貫性は失われました。RESTORE チェーンが壊れ、サーバーが以前のログ ファイルのコンテキストを保持しなくなったので、以前のログ ファイルについて把握しておく必要があります。DBCC CHECKDB を実行して物理的な一貫性を検証してください。データベースは dbo 専用モードに設定されました。データベースが使用可能な状態になったら、データベース オプションを再設定し、余分なログ ファイルを削除してください。
'Northwind' の DBCC 結果。
(省略)
CHECKDB により、データベース 'Northwind' に 0 個のアロケーション エラーと 0 個の一貫性エラーが見つかりました。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。

かなり長々と表示されますが、大事なのは最後から 2 行目の内容でしょうか。とりあえず、 MDF ファイルに破損はなかったと書かれています。

ただし、エラーがなかったからと言って、データがすべて復旧したわけではありません。最初にも書きましたが、チェックポイント未実行のデータがあった場合、それらは失われます。
実際に上記のメッセージは、チェックポイント未実施で SQL Server を強制終了した時の MDF ファイルからリストアした時のものですが、データの内容は最後のチェックポイント時点まで戻っていました。

VB レガシー機能の多くを封じる方法

今回は、2 年前にとある勉強会で発表した時のネタの一部ですが、今でもこれについて具体的に紹介した日本語のリソースが見当たらないので投稿することにしました。

VB6 おじさん

昨今良くネタにされる、SIer を始めとした労働集約型の開発チームだと、政治的理由でプラットフォームに VB が選択されることも良くあると思います。VB 自体はそこまで悪い言語ではないですが(ただし個人的にはやはり機能面で C# を推したいです)、そういうチームで往々にして問題になるのが、VB6 時代の知識で止まっている人たちの存在です。
私だったら VB であることはやむなしとして、なるべく最新の言語仕様による記述を心掛けたいと思うところですが、VB6 おじさん(30 歳ちょいでも結構いたりする...)はそんなのお構いなく VB6 風味のコードを量産してくれます。

IDEコンパイラレベルでの対策

Option Strict On オプションによる、型付の厳格化/遅延バインディングの抑止はわりと多くのプロジェクトで採用されているかと思いますが、これだけでもそれなりに効果的です。でもできればもうひと押し欲しい!!

Visual Studio 2010 SP1 以降が必要ですが、そんな状況では、プロジェクトファイル(.vbproj)を直接テキストエディタで開き、どこかの PropertyGroup 要素の配下に "<VBRuntime>Embed</VBRuntime>" を追加します。私はよく次の画像の位置に書いてます。

f:id:s_earlgrey:20160509014823p:plain

これを行うことで、そのプロジェクトではいくつかの VB 固有機能(多くは VB6 風味)を無効化することができます。主には次のようなものです。

次のような機能は依然として有効です。

  • モジュール
    • VB では拡張メソッドはモジュールに定義する必要があるので...。
  • 暗黙の縮小変換
    • Double から Integer への変換とか
  • Microsoft.VisualBasic.CompilerServices.Conversions クラス
  • AscW/ChrW メソッド
  • vbBack/vbCr/vbCrLf/vbFormFeed/vbLf/vbNewLine/vbNullChar/vbNullString/vbTab/vbVerticalTab 定数
  • XML リテラル
  • Function と同名の暗黙のローカル変数
  • With
  • プロパティの参照渡し
  • Nullable との比較における三値論理

というわけで、あまりうれしくない機能も残りますが(個人的に下から 4 つは心底忌み嫌っています)、わりと強力です。特に既定のインスタンスが無効化されるのはうれしいかもしれません。

注意として、CInt 等の変換関数の機能にも変化が表れます。例えば CInt("123") のような全角数字は、通常なら 123 に変換してくれますが、<VBRuntime>Embed</VBRuntime> を設定している場合は InvalidCastException になります。実行してみないと違いがわからないところが少し厄介かもしれません。
このあたりは Culture を参照するかどうかが変わってくるように感じていますが、あまり調べられていません。

現実問題

ではこの機能を使って VB6 風味のコードを積極的に制限すべきかというと、実はそうは思っていません。制限される機能は VB の持ち味でもあり、それらがないものはもはや C# の劣化版に過ぎないからです。品質に小うるさい案件では厳格なコードが必要とは思いますが、だったら素直に C# を使ったほうが良いかと。
メンバーの反発も予想されます。

というわけで、私も実際に適用したのは、限られたメンバーだけが編集するコアなライブラリのプロジェクトでだけです。

おまけ

ちなみに冒頭で触れた勉強会の発表資料はこちらです。2 年前のものですが特に加筆はしていません。

www.slideshare.net

過去に登壇したのはこの一度きりですが、また何かネタを見つけてチャレンジしたいですね。

sp_executesql におけるオブジェクト名の記述方法

SqlClient でパラメータを使用した SQL を実行すると、実際は sp_executesql に変換されます。
プロファイラを使えばその様子を簡単に観察することができます。

using System;
using System.Data;
using System.Data.SqlClient;

static void Main(string[] args)
{
    using (var conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=AdventureWorksDW2014;Integrated Security=True"))
    using (var paramCmd = new SqlCommand("SELECT [ProductAlternateKey] FROM [DimProduct] WHERE [ProductKey] = @ProductKey", conn))
    using (var adhocCmd = new SqlCommand("SELECT [ProductAlternateKey] FROM [DimProduct] WHERE [ProductKey] = 1", conn))
    {
        conn.Open();

        // パラメータクエリの実行
        var param = new SqlParameter("ProductKey", SqlDbType.Int);
        paramCmd.Parameters.Add(param);
        param.Value = 1;
        var productAlternateKey = paramCmd.ExecuteScalar() as string;
        Console.WriteLine(productAlternateKey);

        // アドホッククエリの実行
        productAlternateKey = adhocCmd.ExecuteScalar() as string;
        Console.WriteLine(productAlternateKey);
    }

    Console.ReadKey();
}

上記のコードを実行したときのプロファイラの出力結果がこちらです。

f:id:s_earlgrey:20160505192832p:plain

色の付いた行がパラメータクエリで、確かに sp_executesql が実行されていることがわかります。一方で、後続のアドホッククエリは SQL がそのまま実行されています。

ちなみにデータベースにはデータウェアハウス用の Adventureworks を使用しています。(Adventure Works DW 2014 Full Database Backup.zip)

さてその sp_executesql ですが、リファレンスにこのような記述があります。

パフォーマンスを向上させるには、ステートメント文字列に完全修飾オブジェクト名を使用します。

完全修飾オブジェクト名というのは、サーバ名.データベース名.スキーマ名.オブジェクト名で表されます。つまりそのまま解釈すると、先のコードの場合だと、[APOLLO\SQLEXPRESS].[AdventureWorksDW2014].[dbo].[DimProduct] と書く必要があるということです(APOLLO というのはコンピュータ名です)。
これはめんどくさい。特にサーバ名は localhost じゃダメらしいので、開発環境と本番環境のサーバ名の違いを吸収するのが大変です。

一体なぜこんなことを?という感じですが、別の説明ページ にはこういう風に書かれています。

SQL Server によって実行プランが再利用されるようにするには、ステートメント文字列内のオブジェクト名を完全修飾名にする必要があります。

えええ本当ですか...、という感じです。正直信じがたいので、完全修飾名じゃない場合に実行プランが再利用されるかどうかを確かめてみます。

完全修飾でないどころか、データベース名やスキーマ名も省略したオブジェクト名を使って、同じ sp_executesql をパラメータ値を変えて 2 回実行します。

-- キャッシュ済実行プランをクリア
DBCC FREEPROCCACHE
GO

EXEC sp_executesql
    N'SELECT [ProductAlternateKey] FROM [DimProduct] WHERE [ProductKey] = @ProductKey',N'@ProductKey int',
    @ProductKey = 1
GO

EXEC sp_executesql
    N'SELECT [ProductAlternateKey] FROM [DimProduct] WHERE [ProductKey] = @ProductKey',N'@ProductKey int',
    @ProductKey = 2
GO

これを実行した後で、キャッシュされた実行プランを確認してみます。

SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
GO

f:id:s_earlgrey:20160505205315p:plain

sys.dm_exec_cached_plans
sys.dm_exec_sql_text

これを見る限り、2 回とも同じ実行プランが利用されています。少なくとも実行プラン再利用の観点からすると、sp_executesql で完全修飾オブジェクト名を使う必要はなさそうです。

BULK INSERT 時のソート操作

SQL Server で大量データを一括登録するときは BULK INSERT を用いますが、並び順を気にしておかないと、状況によっては内部的にソートが発生したり、登録後のテーブルが断片化したりします。
ただ私も詳細はあまりよく把握していないので、いろいろ検証してみます。

下準備

まずは一括登録用に、以下の 3 つのテーブルを作成します。ヒープというのは、クラスタ化インデックスを持たないテーブルのことです。

  • クラスタ化インデックスを持つテーブル
  • クラスタ化インデックスのキーを持つヒープ
  • 一意でないインデックスを持つヒープ
-- クラスタ化インデックス
CREATE TABLE [dbo].[BulkCopy_ClusteredIndex]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL,
    CONSTRAINT [PK_BulkCopy_ClusteredIndex] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

-- ヒープ 1(キーあり)
CREATE TABLE [dbo].[BulkCopy_Heap1]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL,
    CONSTRAINT [PK_BulkCopy_Heap1] PRIMARY KEY NONCLUSTERED
    (
        [ID] ASC
    )
)
GO

-- ヒープ 2(非一意インデックスあり)
CREATE TABLE [dbo].[BulkCopy_Heap2]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL
)
CREATE NONCLUSTERED INDEX [IX_BulkCopy_Heap2] ON [dbo].[BulkCopy_Heap2]
(
    [ID] ASC
)
GO

お次は、BULK INSERT 用のデータを作ります。Excel を使う手もありますが、ここは一度 SQL で登録してからテキストファイルに書き出す方式にします。
T-SQL – 1 million records in 1 second というポストを真似て、先ほど作成したテーブルに 100 万件ほど登録します。一応やってることを説明すると、まずは再帰 CTE を用いて目的の件数の平方根以上の最小の整数件分の共通テーブル式 (Base) を作って、次に Base 2 つの直積を取ったテーブル式 (Expand) を作る、という感じです。

DECLARE @rows int = 1000000;

WITH Base AS
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@rows))
),
Expand AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY b1.n) AS n
    FROM Base AS b1, Base AS b2
)

INSERT INTO [dbo].[BulkCopy_ClusteredIndex]
SELECT
    n,
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID()))
FROM Expand
WHERE n <= @rows
OPTION (MAXRECURSION 0)

GO

登録できたら、インポートおよびエクスポートウィザード を用いて、CSV 形式で出力します。主キーである ID 列の昇順でソートしたものと、ランダムに並べ替えたものの 2 種類を用意します。

-- ID 列の昇順 (BulkCopy_Ordered.csv)
SELECT * FROM [dbo].[BulkCopy_ClusteredIndex] ORDER BY ID

-- ランダムに並べ替え (BulkCopy_Random.csv)
SELECT * FROM [dbo].[BulkCopy_ClusteredIndex] ORDER BY NEWID()

あとは一時的に登録したデータを TRUNCATE でクリアして準備完了です。

データソースの並び順と BULK INSERT の ORDER 引数

SQL Server でよく使われるクラスタ化インデックスでは、データがキーの順序で物理的かつ連続的に並べられた状態で格納される、...というだけの説明をかつては書籍やネットの記事でよく見かけた気がしますが、これにはかなり語弊があります。
実際は極力連続的に並べようとしますが、それができない場合は、前後のページがポインタで連結されます(この状態が断片化です)。本当にすべてが連続的だとしたら、大量データの途中で追加や削除をしようとしたら大変なことになってしまいます。

少し話が逸れましたが、クラスタ化インデックスでは、大部分のデータがキーの順序で連続的に並んでいます。
BULK INSERT においては、基本的に BATCHSIZE 引数を指定した場合はその値の行数ごと、指定しない場合はデータソース全体を、連続した領域に割り当てようとします。そのため、読み込んだデータを登録する前に、クラスタ化インデックスのキーの順番に従ってソートする操作が発生します。
ただ、データソースが元からクラスタ化インデックスのキーと同じ順番で並んでいる場合は、改めてソートする必要はないはずです。こういう場合のために BULK INSERT では ORDER 引数が用意されており、これでデータソースの並び順をオプティマイザに対して明示的に示すことで、不要なソートを抑えることができます。

クラスタ化インデックスに ORDER 引数なしで BULK INSERT する場合

まずはクラスタ化インデックスに ORDER 引数なしで BULK INSERT してみましょう。SQL と実際の実行プランはこんな感じになります。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Random.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

f:id:s_earlgrey:20160502014352p:plain

INSERT の前にソートが行われていますね。ではここで、登録先のクラスタ化インデックスの断片化状況を見てみましょう。

SELECT
    index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
    page_count, avg_page_space_used_in_percent, record_count
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID(N'dbo.BulkCopy_ClusteredIndex'),
        OBJECT_ID(N'dbo.BulkCopy_ClusteredIndex.PK_BulkCopy_ClusteredIndex'),
        NULL,
        'DETAILED')
GO

f:id:s_earlgrey:20160503132835p:plain

index_level が 0 のものがクラスタ化インデックスのリーフレベル、つまりレコードのデータそのものを表します。avg_fragmentation_in_percent が断片化率で 0.39% です。ランダムに並んだデータを登録したにもかかわらず、事前のソートのおかげでほとんど断片化していないことがわかります。

今度はキーの順番でソート済みのデータを登録してみましょう。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

f:id:s_earlgrey:20160502011128p:plain

BulkCopy_Ordered.csvクラスタ化インデックスと同じ順番で並んでいますが、それとは関係なく INSERT の前にソートが行われています。

クラスタ化インデックスに ORDER 引数ありで BULK INSERT する場合

では今度は OREDR 引数ありでやってみましょう。登録するのはソート済のほうです。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

f:id:s_earlgrey:20160503135133p:plain

確かに事前ソートは行われていません。念のため断片化率もチェックしてみましょう。

f:id:s_earlgrey:20160503135333p:plain

断片化もしていません。やったね!期待通りです。


...ただここで疑問が。今のはもともとソート済のデータを使ったので ORDER 引数が有効に働きましたが、並び順がランダムな方のデータで ORDER を付けるとどうなるんでしょう。リファレンスには次のような記述があります。

データ ファイルが異なる順序で並んでいる場合、つまりクラスター化インデックス キーの順序以外の順で並んでいるか、テーブルにクラスター化インデックスが存在しない場合、ORDER 句は無視されます。

無視される、ということは強制的にソートされるという意味でしょうか。だとすると、本当にデータソースが ORDER で指定したとおりに並んでいるかどうかを確認するってこと?その場合の計算量は最大でも O(n) なのでソートよりは負荷が低いだろうけど、実際の登録前にデータをどこかにバッファしておくか二度読みが必要になるので、ちょっと考えにくい気がします。とりあえずやってみましょう。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Random.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

f:id:s_earlgrey:20160503142820p:plain

ソートされませんね。ちょっと嫌な予感がしつつも断片化率を見てみます。

f:id:s_earlgrey:20160503143203p:plain

99.25%(!) というか、fragment_count と page_count が同じ値なので、連続したページがひとつもないことになります。
この結果を見る限り、オプティマイザは実際のデータソースの並び順に関係なく、ORDER で指定されたとおりに並んでいると仮定してソートの有無を決めているようです。ORDER を使うときは、データソースの並び順を担保してあげることが必要ということですね。

では、ORDER で指定した並び順とキーの並び順が違っている場合は?
こちらも試してみましょう。ORDER でキーの逆順を指定してみます。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID DESC)
)
GO

f:id:s_earlgrey:20160503145123p:plain

ソートが行われています。さらに、ソート操作のアイコンにマウスポインタを持っていくと、詳細がポップアップされます。

f:id:s_earlgrey:20160503145357p:plain

最後の「並べ替え」に注目してください。ORDER では ID の降順を指定しましたが、実際は昇順でソートが行われています。
ここでもう一度さっきの引用を繰り返しますが、

データ ファイルが異なる順序で並んでいる場合、つまりクラスター化インデックス キーの順序以外の順で並んでいるか、テーブルにクラスター化インデックスが存在しない場合、ORDER 句は無視されます。

データファイルの実際の並び順はチェックしないので、ORDER の指定とクラスタ化インデックスの順序が異なる場合、ORDER は無視される、の方がしっくり来ますね。

キーを持つヒープに BULK INSERT する場合

今度は、非クラスタ化インデックスのキーを持つヒープへの BULK INSERT を見てみます。
クラスタ化インデックスも、クラスタ化インデックスほどではないにしてもリーフレベルでは断片化が気になります。

まずは ORDER なしから。

BULK INSERT testdb.dbo.BulkCopy_Heap1
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

f:id:s_earlgrey:20160503160246p:plain

やはり非クラスタ化インデックスの登録前にソートが発生しています。では ORDER を付けるとどうでしょう。

BULK INSERT testdb.dbo.BulkCopy_Heap1
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

f:id:s_earlgrey:20160503160524p:plain

ソートがなくなっています。ヒープに BULK INSERT する場合でも、ORDER で指定したのと同じ並び順を持つ非クラスタ化インデックスのキーの登録前にはソートが発生しない、ということが言えそうです。

非一意インデックスを持つヒープに BULK INSERT する場合

先ほどの非クラスタ化インデックスはキーでしたが、では非一意のインデックスしか持たないヒープの場合はどうでしょう。今回は最初から ORDER 付きで実行してみます。

BULK INSERT testdb.dbo.BulkCopy_Heap2
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

f:id:s_earlgrey:20160503161723p:plain

ORDER の指定とインデックスの順番が一致しているにもかかわらず、インデックスの登録前にソートが発生しています。キーのないテーブルに BULK INSERT する場合は、ORDER は無視されるようです。

まとめ

BULK INSERT を行う際の ORDER の指定ポリシーは、次のようにするのが良さそうです。

  • 登録先のテーブルにキーがある場合、クラスタ化インデックスであってもヒープであっても、可能であればデータソースをキーの順番どおりにあらかじめソートしておき、その通りに ORDER 引数を指定する。
  • 登録先のテーブルにキーがない場合、ORDER 引数は無視され、インデックス登録前に強制的にソートされるので指定しない。