SQLServer」タグアーカイブ

AzureのSQL DATABASEから多層ネストされたJSONを返すREST APIを作る場合、SELECT文だけで間に合ってしまう(Transact-SQL)

たまたまAzureのSQL DATABASE(≒SQL Server)のドキュメントを読んでいたときに気が付いたのですが、最近のSQL Serverってクエリ末尾に”FOR JSON”を付与すればクエリ結果をそのままJSONに出来るんですって!?そんなこと知らずにDapperでガリガリとSQLをパースしてJSONを作っていたとか馬鹿丸出しですねワタシ。パトラッシュ・・次から頑張るよ・・・・。

ということでSQLだけで多層ネストJSONが出来るまでを検証しました。以下にその方法を示します。

準備:Azure上にサンプルDBを作成 (テンプレートの”AdventureWorkLT”を利用)

このサンプルデータを利用して「セールスの存在する顧客別にオーダーとそのオーダー詳細を表示する」という3層ネスト構造を再現します。

SQLで表現するとこんな感じです。

実行するとこんな感じ。

SQL実行結果

いかにもDapperで分割してくださいと言わんばかりの出力結果ですが、今回はDapperは使わずSQL DATABASEの基本機能だけしか使いません。ですのでこのSQLの文末に”FOR JSON AUTO”を付与すればめでたく解決・・・ではなく、ここからネストさせたい階層ごとにサブクエリを定義していく必要があります。それをストアドファンクション化したのが以下となります。

ポイントは次の通りです。

  • JSONの受けは必ずNVARCHAR(MAX)とする
  • “FOR JSON PATH”とする(PATH付けないとコケる)
  • ROOT(‘INFO’)とすることでルートオブジェクト配列にアクセスしやすくする
  • サブクエリでのエイリアスがJSONでの型名となる

こうして作成したJSONをオンラインパーサーで確認するとちゃんと3層ネスト構造となっていました。

オンラインパーサーでのJSON構造確認結果

あとはこのまま普通にHTTP TriggerやPHPファイルからレスポンスを返してあげればREST APIの作成完了なのです。余談ですが逆にSQLだけでJSONを解析することも可能です。

実はこの逆変換の方が大変でした。ポイントは次の通りです。

  • “CROSS APPLY OPENJSON”を用いてJSON型を手動で展開する
  • 展開したJSONの中に再度JSON型があった場合はさらに展開する
  • OPENJSONのWITHにて型指定する必要がある(型推論ないんかい・・・
  • ”CROSS APPLY”で展開した列名は展開対象のエイリアスとは別となる
  • “CROSS APPLY”しても展開元のエイリアスは呼べる(これになかなか気付かなかった・・・)

今どきのSQLServerってこんな感じでSQLを駆使していけばJSON化もパースもできるんですね。でも楽になった気があまりしないのは気のせいではないかもしれません。正直、慣れてなければ面倒くさいからこれならDapper使うわってのもアリではないでしょうか。あとSQLの見通しが悪くなってるのがかなり保守観点でマイナス。

ですので間違ってもこれらの「SQLだけでREST APIは作れる」という知識だけで 判断してはダメです。
「(言語名)経験○○年の要員がホゲホゲ」なんて考えのマトモにスキル判定も出来ない無能揃いのSES業者が「運用でSQLバリバリやってましたHAHAHA」という定型作業しか経験のない人材を押し込んでくることになりかねないので要注意です。

ExpresswebでASP.NET構成を動かす

Expresswebを借りたは良いけど、よく考えたら特にやりたい動的コンテンツもなかったので、 あまり深く追求してこなかった私ですが、今回少し踏み込んでDB設定をしてみました。
それにしてもExpresswebに用意されたマニュアルの不親切なこと。
パスワードポリシーとかの存在を全く書いてないよ。

まあ安かろう不親切だろうってことなんですかね。

  • はじめに

ASP.NET構成の設定値は初期状態のままだとApp_Dataの下にAspnetdb.mdfに保存される。
正直Expresswebで動かせるかどうかよく分からなかったのと、せっかく3インスタンスほどDB空間が提供されてるから、やはりそれを使ってみたい。
と、そんな程度の理由でDBを載せ替えてみることにした。
ということで備忘録代わりに記事にしてみた。

(1).ASP.NET構成のDB空間を作成する
C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe
「アプリケーション サービスのために SQL Server を構成する」を選択して
DB名はデフォルトのaspnetdb以外にすること

(2).DB接続ユーザーの作成する
ManagementStudio(MSの純正SQL Server用GUIツール)の上でユーザーを作成する。

Expresswebのポリシーに引っ掛からないように以下のルールで生成する必要がある。

ユーザー名:4文字以上
パスワード:8文字以上英数記号が必要

※.作成したDBに対してユーザー権限に[db_owner]を付与しておくこと。

(3).接続文字列をWeb.configに設定する

<connectionStrings>
 <remove name="LocalSqlServer"/>
 <add name="LocalSqlServer" connectionString="Persist Security Info=False;Server=PCNAME\SQLEXPRESS;Initial Catalog=hogedb;PASSWORD=$Hoge1234;User ID=hogera" providerName="System.Data.SqlClient" />
</connectionStrings>

※.ExpresswebのDBサーバー名はExpresswebコントロールパネルにログインして
[スペース]-[(スペース名)]-[スペース詳細を表示]に記述してある

(4).ASP.NET構成の接続テスト
Web.configを保存したらASP.NET構成にて必要な設定を行う
ASP.NET構成

(5).ローカル側にてデータのエクスポート
ManagementStudioにてDB名を右クリック [タスク]-[スクリプトの生成]にてクエリを生成する
SQL Server Management Studio

(6).Expresswebにデータのインポート
基本的にExpresswebのSQLServer操作はmyLittleAdminというWebアプリにて操作するらしい。
こんなツール知らなかったけど、phpMyAdminよりは出来が良いかもしれないと個人的には思った。
myLittleAdminはコントロールパネルから[データベース]-[SQL Server 2008]-[データベースを参照]にて起動する。
起動したmyLittleAdminの[ツール]-[新しいクエリ]を開き、先ほど生成したクエリをペーストして[実行]ボタンを押せばインポート完了。

一応、これで作業は一通り完了。

ちなみに私の環境は以下の通りです。

Visual Studio 2008 Professional

SQL Server 2008 Express

なお、ここまでの説明で意味がサッパリな人はExpresswebを使わないほうが無難です。

あとHTTPモジュールが動かないのが解決できない。 気が向いたらサポートに問い合わせることにしよう。

などとここまで書いてから箇条書きのところに

<ol>を使おうとしたら、連続してないと駄目らしい。
勝手に妙なタグ補完されて、意図したレイアウトにできない。
有名だから使ってみたけど、こんな使いにくいブログにするんじゃなかった・・・。

Windows7 64BitにSSMSEがインストールできない

開発機(Windows7 Ultimate 64bit)にSSMSE(Microsoft SQL Server Management Studio Express)をインストールしようとすると

このパッケージをインストールするときに予期しないエラーが検出されました。
(中略)エラーコードは29506です

とか言われてこけて、その対処に手間取った。

エラーコードで検索するとUACの問題と出るのでUACを無効にしてもインストールに失敗した。
いぶかしみつつ管理者権限でインストール・・と思っても右クリックメニューに「管理者として実行」が出てこない。
あれ?MSIって管理者権限指定できなかったっけ?
ならRUNASだね、と思ったら実行ファイルじゃないから巧くいかない。
MSCにおけるMMC.EXEみたいに関連づけられた実行ファイルがあるんだろうけど、単純にそれを知らない。
ということで再度検索。
msiファイルを管理者権限で実行する
なになに?msiexecを使えばいい?
ということでRUNASで管理者ユーザーで実行しても失敗する。
なんで?
コマンドプロンプトをかまして

runas /user:Administrator "cmd /k msiexec /i C:\Users\yuh\Downloads\SQLServer2005_SSMSEE_x64.msi"

みたいにしても駄目・・・で、気付いた。
コマンドプロンプトを起動するときに「管理者として実行」をすればいいんじゃないか。

msiexec /i C:\Users\yuh\Downloads\SQLServer2005_SSMSEE_x64.msi

これで成功した。
どうやら「管理者として実行」はRUNASでは同程度のオペレーションを実現できないらしい。
まあ確かにセキュリティを考えるとそれが正しいんだろうけど・・納得いかんなぁ。
もしかして・・・と思って再検索したら、普通にこのやり方で処理されてる方がいた。
DB:: Windows7上にSSMSEEをインストール
検索が足りなかった。

ワタシ馬鹿よねぇ~。お馬鹿さんよねぇ~(;_;