新しい社内アプリケーションを作る際、データベースも含めバージョン管理したいと思い、SQL ServerのDDLを出力する方法を色々調べていました。今まで、SQL Server Management Studioを利用し、GUI上で操作していたのですが、コマンドラインで実行できるものとしてpythonのパッケージを発見しました。

ただし、社内の開発用端末にはpythonが標準でインストールされていないので、そのpythonのセットアップ（embeddableです）も含めて環境構築するスクリプトを作りました。

環境
Pythonパッケージについて
pythonの環境構築の流れ
作ったものはGitHubに公開しました
- ファイルの説明
  - Setup.ps1
    - Setup.Config.json
  - ExportDDL.ps1
    - ExportDDL.Config.json
- ExportDDL.ps1を実行すると・・・

環境

Windows 10 Pro 64bit (OS Build 19042.1466)
PSVersion : 5.1.19041.1320
pythonなし

Pythonパッケージについて

SQL Serverから定義スクリプトを出力できるパッケージは mssql-scripter というmicrosoftがメンテナンスしているものです。

github.com

pythonがインストールされていればこのパッケージをpipでインストールすればすぐに利用できます。

ただし、弊社のメンバーではpythonを触ったことのない方も多いため、pythonの環境も用意する必要があります。

pythonの環境構築の流れ

pythonはzipを解凍するだけで使用できるembeddableバージョンが存在します。

www.python.org

これを利用して、端末の環境を汚さず、mssql-scripter を実行できる環境までを構築していきます。

python embeddableバージョンのダウンロード・展開

powershellでは Invoke-WebRequest でダウンロードし、Expand-Archive で展開します。

また、pthファイル内の "#import site" のコメントを外す必要があります。

ダウンロード～展開までのコードサンプル

# ダウンロード～展開
Invoke-WebRequest -Method Get -Uri "https://www.python.org/ftp/python/3.9.5/python-3.9.5-embed-amd64.zip" -OutFile "python.zip"
Expand-Archive -Path "python.zip"  -DestinationPath "python"

# パスを移動して、pthファイルを修正
Push-Location python
$pthFile = Get-ChildItem python*._pth
# 本当はコメントはずすんだけど面倒なので追記して新規行追加しちゃう
Add-Content -Path $pthFile.FullName -Value "import site"

pipのインストール

pipのインストールはpython スクリプトをダウンロードし、そのスクリプトを実行することで実現できます。

pipのドキュメンテーション

pip.pypa.io

引用

This is a Python script that uses some bootstrapping logic to install pip.

Download the script, from https://bootstrap.pypa.io/get-pip.py.

Open a terminal/command prompt, cd to the folder containing the get-pip.py file and run:

https://bootstrap.pypa.io/get-pip.py をダウンロードしてそれをpythonで実行しろ、という事が書いてあります。

pipインストールのサンプルコード

※ダウンロード～展開までが終わった状態とします。

# pipインストールスクリプトのダウンロード
Invoke-WebRequest -Method Get -Uri "https://bootstrap.pypa.io/get-pip.py" -OutFile "get-pip.py"

# インストール
.\python.exe get-pip.py

mssql-scripter のインストール

あとはpipでmssql-scripter をインストールします。

そのために、pip関連のパスを通したりします。

mssql-scripter のインストールのサンプルコード

# パスを通す（インストールする時だけでOK）
$env:Path += ";$((Resolve-Path "Scripts").Path))"
.\python.exe -m pip install mssql-scripter

作ったものはGitHubに公開しました

この流れに合わせてエラー処理とか接続情報の保持とかを色々加味したものをgithubに公開しました。

github.com

ファイルの説明

f:id:NotShown:20220116000539p:plain — ファイル一覧

Setup.ps1

pythonのダウンロード～mssql-scripterのインストールまでを行います。実行してエラーなくインストールが終わればセットアップ完了です。

設定ファイルでバージョンを変えたりできます。 zipファイルとpythonフォルダを削除することで再度最初から実行できます。

Setup.Config.json

Setup.ps1の設定ファイルです。固定値になるところなどを設定ファイルにしています。

内容と説明コメント

{
    "python":{
        "version":"3.9.7", # バージョン
        "32":"win32",  # ビット数でダウンロードするバージョンが変わるのに対応
        "64":"amd64", # ビット数でダウンロードするバージョンが変わるのに対応
        "urlFormat":"https://www.python.org/ftp/python/{0}/python-{0}-embed-{1}.zip", # ダウンロードURLのフォーマット( {0}がバージョン、{1}がアーキテクチャ）
        "dlFile":"python.zip", # ダウンロードされたファイルの名前
        "diretoryName":"python", # 展開するときのフォルダ名
        "pthFileAdd":[ #  pthファイルに追加する行のリスト
            "import site"
        ]
    },
    "pip":{
        "url":"https://bootstrap.pypa.io/get-pip.py" # pipインストールのためのスクリプトURLの
    },
    "packages":[
        "mssql-scripter"# インストールするパッケージ (増やすこともできます)
    ]
}

ExportDDL.ps1

DBの定義を出力するスクリプトです。これを実行してダイアログに合わせてパスワードを入れれば出力されます。同じフォルダの DDL フォルダ内に出力されます。設定ファイル ExportDDL.Config.json にあるmssql-scripterの引数を変更することで動作は変更できます。

ExportDDL.Config.json

ExportDDL.ps1の設定ファイルです。

内容と説明コメント

{
    "login": {
        "server": "(localdb)\\MSSQLLocalDB", # DBサーバー
        "userId": "ddl_admin", # ユーザー名
        "connectionStringFile":".cs.xml" #保存される接続文字列のファイル（暗号化されます）
    },
    "databases": [
        "AdventureWorks2016"# 対象DB（これはmicrosoftのサンプルDBを使ってテストした名残）
    ],
    "mssqlScripter": {
        "args": [
            "--file-per-object", # オブジェクトごとにファイル出力
            "--script-drop-create", # 削除→新規作成のスクリプト
            "--object-permissions", # オブジェクトの許可拒否も出力
            "--display-progress", # 進捗表示
            "--target-server-version 2012" # これはLocalDBが2012だったので設定
        ],
        "logName":"mssqlscipterlog.txt", # ログファイル。実行内容が出力されます。
        "exportDirectory":"DDL" # 出力フォルダ。変更可能。
    },
    "python": {
        "directoryPath": "python",
        "mssqlScripterPath": "Scripts\\mssql-scripter.bat" # mssql-scripterの実行バッチのパス（pythonフォルダ基準)
    }
}

ExportDDL.ps1を実行すると・・・

サンプルはmicrosoftのサンプルデータベース AdventureWorks です。

docs.microsoft.com

実行するとこんな感じになります。

f:id:NotShown:20220116001609p:plain — 実行後のDDLフォルダ

これで出力完了です。

あとはバージョン管理システムに登録して差分などを管理していければいいと思います。

以上！

命名って難しい

変数、関数、クラスなどなど実装より命名に毎回悩むタイプの人間による技術についてのメモ。

Powershellでpythonをセットアップしつつ、SQL Serverのテーブルなどの定義をスクリプトに出力するパッケージを実行するPowershellスクリプト

環境