MacでMS SQL Serverを動かしてsql-cliや.NET Coreから利用してみる


先日のConnect();でLinux(Ubuntu)で動くSQL Serverのパブリックプレビューが公開されました。Mac用にはDocker Imageが公開されましたので試してみます。

ただ動かしても仕方ないのでMac上で利用できるSQLServerのCLIクライアントツールであるsql-cliと.NET CoreからEntityFramework経由で利用してみます。

SQL Serverの動作自体は[ここ](https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker)の通りにやればだいたい動きます（大きな問題ではありませんがpersist（データの永続化）だけはうまくいきません）。

##環境

* El Capitanを利用しますが、Sierraでも動きます。

##ツールの準備

下記をインストールしておきます。

* Docker for Mac（1.12.3）
* Sql-cli（npmでインストールできるSQLServerのクライアントツール）

###Docker

[ここ](https://www.docker.com/products/docker#/mac)からダウンロードしてインストールするだけですが、インストール後、メモリの割当を4G以上にしておく必要があります。

また、ハードウエアがIntelのVirtual Technologyに対応している必要があり、2010年以降のMacじゃないと動かないみたいです。

インストールが完了したら動作を確認しておきます。

```bash
docker -v
Docker version 1.12.3, build 6b644ec
```

###Sql-cli

npm(node.js)がインストールされていなければインストールしましょう。その後、

```bash
sudo npm install -g sql-cli
```

とします。これによりmssqlコマンドが利用できるようになります。


```bash
mssql -V
0.4.10
```

として動作を確認しておきましょう。

##SQL Serverを起動させる

###取得する

まずはpullで取得します。

```bash
 sudo docker pull microsoft/mssql-server-linux
```

###実行してみる

```bash
docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssw0rd! -p 1433:1433 -d microsoft/mssql-server-linux
```

```bash
docker ps

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
f0100623c3b3        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   3 seconds ago       Up 2 seconds        0.0.0.0:1433->1433/tcp   boring_shockley
```

###エラーログを確認してみる

```bash
syslog -k Sender Docker
```

##SQL serverを利用してみる（sql-cli）

動作を確認するためにはManagement Studio等を利用すればいいのですが、めんどくさいのでMacで利用できるクライアントツールである[Sql-cli](https://github.com/hasankhan/sql-cli)を利用してみます。GUIが良ければ有償になりますが[SQLPro for MSSQL](http://www.macsqlclient.com/)とうのもあります。

###ログイン

では、データベースにログインしてみましょう。

>SQL Serverのコンテナは何も警告なく落ちてる場合があるのでdocker psで起動を確認してみてみるといいでしょう。

```bash
mssql -s localhost -u sa -p P@ssw0rd!

Connecting to localhost...done

sql-cli version 0.4.10
Enter ".help" for usage hints.
mssql>
```

mssql>プロンプトが表示されればOKです。

###データベースを確認して見る

.databasesでデータベース一覧が表示されます。最後に;はいりません。

```bash
mssql> .databases

name
------
master
tempdb
model
msdb
```

標準では4つのデータベースが見えますね。

###CREATE DATABASE

ではデータベースを作成してみます。ここではsampleという名前にします。

```bash
create database sample;
```

作成ができたら、データベースを切り替えます。

```bash
use sample;
```

###CREAT TABLE

次にテーブルです。mssqlは改行は明示的に\で指定してやる必要があるようです。改行を入れたコマンドをコピペする場合は注意しましょう。

```bash
create table members \
( \
	id int identity(1,1) not null, \
	name nvarchar(128), \
	email nvarchar(128), \
	constraint pk_members primary key (id) \
);

```

テーブルが作成されたか.tablesで確認します。

```bash
.tables
```

###INSERT

テーブルができたらインサートします。ここからはSQLの世界です。

```bash
insert into members(name,email) values('hoge','hoge@hoge.com');\
insert into members(name,email) values('foo','foo@foo.com');
```

###SELECT

ではSELECTしてみましょう。

```bash
select * from members;
id  name  email
--  ----  -------------
1   hoge  hoge@hoge.com
2   foo   foo@foo.com

2 row(s) returned
```

mssqlを終了するには.quitを利用します。

```bash
.quit
```

##コンテナにログインしデータを確認する

###コンテナにログインしてみる

稼働しているコンテナにはdocker execコマンドを利用することでログインできます。

> 169b7f39e3a4の部分はdocker ps等で取得したCONTAINER IDとなります。

ログインしてみます。

```bash
docker exec -it 169b7f39e3a4 /bin/bash
```

SQL Server関連のファイルは/var/opt/mssql以下にあります。

```bash
cd /var/opt/mssql

ls -l

drwxr-xr-x 2 root root 4096 Nov 22 03:17 data
drwxr-xr-x 2 root root 4096 Nov 22 03:04 etc
drwxr-xr-x 2 root root 4096 Nov 22 03:17 log
drwxr-xr-x 2 root root 4096 Nov 22 03:04 secrets

cd data

ls -l

-rw-r----- 1 root root  4194304 Nov 22 03:10 master.mdf
-rw-r----- 1 root root  2097152 Nov 22 03:18 mastlog.ldf
-rw-r----- 1 root root  8388608 Nov 22 03:17 model.mdf
-rw-r----- 1 root root  8388608 Nov 22 03:17 modellog.ldf
-rw-r----- 1 root root 13959168 Nov 22 03:04 msdbdata.mdf
-rw-r----- 1 root root   524288 Nov 22 03:05 msdblog.ldf
-rw-r----- 1 root root  8388608 Nov 22 03:17 sample.mdf
-rw-r----- 1 root root  8388608 Nov 22 03:18 sample_log.ldf
-rw-r----- 1 root root  8388608 Nov 22 03:04 tempdb.mdf
-rw-r----- 1 root root  8388608 Nov 22 03:10 templog.ldf
```

mdfやldfファイルが/var/opt/mssql/data以下にあることが確認できます。バックアップ等を考慮する際はこのことを覚えて置くとよいでしょう。

##課題

上記でみたmdf等のデータは当然ですがコンテナをrmすると消えてしまいます（Dockerの再起動やコンテナのrestartは問題ないです）。このような状態を避けるためにdocker run実行時に-vオプションにて[ローカルのPath:コンテナのPath]と指定することにより、コンテナ側の指定したPathをローカルにマウントすることができ、コンテナ削除後もローカルにデータを残すことができます。が、私の環境ではうまくいきませんでした。

```bash
docker run -v ~/mssql:/var/opt/msql （以下省略）
```

おそらくはコンテナ側のユーザーにホスト側の書込権限が無いためと思われます。永続化自体を行う方法は他にもありますが、手間がかかるためまともな解決方法が分かり次第追記します。

##.NET Coreから利用してみる

コンソールだかから利用してもほぼ意味がないので、.NET Coreアプリからアクセスしてみましょう。

##準備

まずは.NET Coreの開発環境を整えましょう。インストールするものは下記の3つといったところです。

* 最新のOpenSSL
* .NET Core (SDK)
* Visual Studio Code（必須ではない）

###OpenSSL

.NET Core SDKのインストールに際しては最新のOpenSSL環境が必要のためinstallページの情報にしたがってbrewでインストールしておきます。

>インストール自体に必須ではにですが、dotnet restoreした時にエラーになるようです。

```bash
brew install openssl

ln -s /usr/local/opt/openssl/bin/openssl /usr/local/bin/openssl
ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/libcrypto.dylib
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/libssl.dylib
```

###.NET Core

[ここ](https://www.microsoft.com/net/core#macos)からダウンロードしてインストールしましょう。インストールができたら、紹介されている動作確認をしてみてください。

まあ基本的にdotnetコマンドが動いていれば大丈夫です。

```bash
dotnet --version
1.0.0-preview2-003148
```

###Visual Studio Code

必須ではありませんが、エディタとして[Visual Studio Code](https://code.visualstudio.com/)をインストールすると良いでしょう。また、拡張機能としてC#をインストールすればC#のシンタックスハイライトが効くようになりますが、ここでは細かな設定方法は割愛させていただきます。

##コード書く

###コードのテンプレートを生成する

ここでは最低限の動作を確認するためにコンソールアプリケーションのテンプレートを生成して利用します。dotnet newを利用すると他にもASP.NET MVC用のテンプレートを作成することもできます。

```bash
mkdir test
cd test
dotnet new
```

とするとtestディレクトリ以下にProgram.csとproject.jsonが生成されます。

###project.jsonの編集とrestore

EntiryFrameworkにてSQLServerを利用するために2行追加しました。以下はdependenciesのみの抜粋です。

```diff
"dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.1"
        },
+       "Microsoft.EntityFrameworkCore": "1.0.0",
+       "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0"
      },
```

編集したら

```bash
dotnet restore
```

します。restoreはdependenciesに記述したライブラリをダウンロードするコマンドです。

>Visual Studio Codeが「リストあするか？」と聞いてくるはずなのでYESとすればいいでしょう。

これでSQLServerにアクセスするために必要なライブラリが取得できました。

###Program.csを編集する

では、SQL Serverにアクセスしてmembersテーブルの内容を取得するプログラムを書いてみます。
実装するパートは下記の３つです。

* モデルクラス
* Contextクラス
* Mainクラス

多さっぱに説明すると、モデルクラスはテーブルと対になるものです。Contextクラスはモデルクラスと物理データベースのマッピング定義します。MainクラスはモデルおよびContextクラスを利用して実際にデータベースを制御するコードを記述します。

```csharp
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication
{
    //model
    public class Member
    {
        public int id {get;set;}
        public string name {get;set;}
        public string email {get;set;}
    }

    //context
    public class MyContext:DbContext
    {
        public DbSet<Member> Members{get;set;}

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
                => optionsBuilder.UseSqlServer(@"server=localhost;database=sample;uid=sa;pwd=P@ssw0rd!;");
    }

    //main
    public class Program
    {
        public static void Main(string[] args)
        {
            using(var context = new MyContext())
            {
                //select
                var ret = context.Members
                    .ToList();

                foreach(var x in ret)
                {
                    Console.WriteLine($"{x.name} {x.email}");
                }
            }
        }
    }
}
```

###動作確認

記述が終わったら実行して動作を確認してみます。INSERTした情報が表示されました。

```bash
dotnet run

Project test (.NETCoreApp,Version=v1.1) was previously compiled. Skipping compilation.

hoge hoge@hoge.com
foo foo@foo.com
```

これで.NET系の開発すべてをMac上で行うことができるようになりました。すばらしい！

##その他

以下関連リンク

[MySQLに対して.NET Coreからアクセスする方法](http://qiita.com/zaburo/items/a167c9ad94fcea625c56)
[MySQLに対してDapperでアクセスする方法](http://qiita.com/zaburo/items/239612ab632bcb5ab18c)