Azure Mobile Services で指定した条件でレコードが存在しなければINSERT、存在すればUPDATEを実行する (TSQLによるMERGE文の実行)

Azure Mobile ServicesでRDBMSを使うにあたって、たとえばユーザーIDをキーとしてクライアントの端末のIDと何かのステータスをサーバーに登録したいケースがあります。このとき、クライアント側では自分のIDと状態が登録済みかどうかわからないケースがあります（端末IDだけならクライアント側に状態持てるけどそれ以外にも更新する値があるとこのケースが生まれます）。そこで、Mobile ServicesのカスタムAPIを使ってサーバーサイドで処理をすることにしました。

サーバー側でSQLを発行して、SELECT～で登録済みかどうか確認して...ということもできますが、トランザクション管理の必要もあるのでちょっと面倒です。SQL一文でできればいいなあと思って居たら、MERGER文というものを教えてもらいました。今回は、これをMobile ServicesのカスタムAPIで使ってみました。

まず、Mobile Services側でテーブルと列を作っておきます。今回、TSQLを実行するのですがこの場合はあらかじめ列が定義されていないと(該当する列を使うTSQLを実行すると)エラーになります。

userdevices テーブルに userId と deviceId という列を持ったテーブルです(それ以外の列は自動で作られる)。

f:id:tanaka733:20140130010256p:plain

そしてカスタムAPIを追加します。今回は userdevice というエンドポイントで作りました。そしてAPIの実装は次のように記述します。テストのためにGETとPOSTのアクセス許可は「すべてのユーザー」にしています。

exports.post = function(request, response) {
    var sql =
        "MERGE metroestore.UserDevices D " +
        "USING (SELECT ?, ?) AS S (userId, deviceId) " +
        "ON (D.userId = S.userId) " +
        "WHEN MATCHED THEN " +
        "UPDATE SET deviceId = S.deviceId " +
        "WHEN NOT MATCHED THEN " +
        "INSERT (userId, deviceId) " +
        "VALUES (S.userId, S.deviceId);"
    request.service.mssql.query(sql, [request.body.userId, request.body.deviceId], {
      success: function(results) {
         response.send(statusCodes.OK, results);
      },
      error: function(err) {
         response.send(statusCodes.INTERNAL_SERVER_ERROR, err);
      }
    });
};

exports.get = function(request, response) {
    var userDeviceTable = request.service.tables.getTable('UserDevices');
    userDeviceTable.read({
        success: function(results) {
            response.send(statusCodes.OK, results);
        }
    });
};

exports.post とか exports.get で関数を公開してPOSTやGETの動作を定義しているのは、node.js の express のお作法になります。Mobile Service用のオブジェクトは、 request.service のプロパティとして定義されていて、POSTでは mssql というプロパティを使ってTSQLを直接実行しています。GET では table を使っています。

mssql.query というメソッドでTSQLを実行できますが、第一引数はSQLになります。このときバインド変数を使うことができて、バインドする値は第二引数で与えます。今回はPOSTのBODYでJSON形式で渡すことにして、そのプロパティ値を与えています。第三引数で成功時と失敗時の処理を書いています。成功時なら200、失敗時なら500ステータスとエラーの内容を渡しています。(本当はエラーで渡されるオブジェクトの中身がわからないので直接クライアントに渡すのはあまりよくないですが)

Node.jsで記述しているわけですが、このあたりのMobile Services用のリファレンスは

Windows Azure のモバイルサービスのサーバースクリプトリファレンス

が詳しいです。あと、実際に管理ポータルで編集するときは、

ショートカットキー

に目を通しておくと役立ちます。時に Ctrl+7 で選択行のコメント化・非コメント化ができるとかは便利です。

肝心のMERGE文ですが、

MERGE (Transact-SQL)

が詳しかったです。USINGのところで、与えられた値をSELECTすることで表を定義しておき(上のSQLではSという荒井ラスをつけている)、ON句でマッチさせる条件を書き、あとは存在する場合はUPDATE、しない場合はINSERTを行って居ます。そして、MERGE文の場合は、「;」を書かないと構文エラーになります。

では、実際に動かしてみましょう。PowerShellでカジュアルにアクセスします。

> (Invoke-WebRequest https://metroestore.azure-mobile.net/api/userdevice).Content
[]
> $json = '{"userId":"user", "deviceId":"hoge"}'
> Invoke-WebRequest -Uri https://metroestore.azure-mobile.net/api/userdevice -Method Post -Body  $json -ContentType "application/json"
> (Invoke-WebRequest https://metroestore.azure-mobile.net/api/userdevice).Content
[{"id":"ED8AC53A-B156-42ED-9FA0-F89706422577","userId":"user","deviceId":"hoge"}]

> $json = '{"userId":"user", "deviceId":"fuga"}'
> Invoke-WebRequest -Uri https://metroestore.azure-mobile.net/api/userdevice -Method Post -Body  $json -ContentType "application/json"
> (Invoke-WebRequest https://metroestore.azure-mobile.net/api/userdevice ).Content
[{"id":"ED8AC53A-B156-42ED-9FA0-F89706422577","userId":"user","deviceId":"fuga"}]

と、こんな感じで最初にINSERTされて、次にUPDATEされていることがわかります。ちなみに格納されているデータ一覧は管理ポータルからも確認できます。

f:id:tanaka733:20140130012713p:plain

というわけで、Azure Mobile Services でも普通のTSQLが使えるよ、という紹介でした。

銀の光と碧い空

クラウドなインフラとC#なアプリ開発の狭間にいるエンジニアの日々

Azure Mobile Services で指定した条件でレコードが存在しなければINSERT、存在すればUPDATEを実行する (TSQLによるMERGE文の実行)