SQL CLRと差分系の処理


SQL Serverはデータ層アプリケーションによって「.NETのコード」を実行可能 = SQL CLR機能
これが他のDBMSと比較して優位な点ではあるが、これに依存するのもどうかと思う。
ちなみにPL/SQLで表現できるものはCLR使わない事、PL/SQLで実現できない事を行うためのもの。

どんな時に使うか?例えば
1.
データをクライアント側にキャッシュして差分だけ受け取りたいと考えた

STAsTextを格納するカラムを作って比較してみた
⇒データ量増大、速度劣化 = 失敗

http://technet.microsoft.com/ja-jp/library/bb933977.aspx

2.
HASHBYTESによってハッシュ化する事を思いついた、しかし上限に引っかかる。
※指定できる入力値は、8000 バイトまでに制限
HASHBYTES
http://technet.microsoft.com/ja-jp/library/ms174415.aspx

上記のようなパターンで、.NET使いたいときなんかに有効
.NET関数が使えることでSQL Serverは100倍拡張しやすいような気がする
(ちまちまクライアントでやるんじゃなくてがさっと実行できる可能性が高い)

まあ上記要件の場合は本当は下記使えばいいはず
BINARY_CHECKSUM
http://technet.microsoft.com/ja-jp/library/ms173784.aspx

例えばProj4net使って投影変換なんかが適当かも

データツールをまずインストールする。

SQL Server Data Tools
http://msdn.microsoft.com/ja-jp/library/hh272686(v=vs.103).aspx

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012
http://www.microsoft.com/ja-jp/download/details.aspx?id=36843

SQLserverで、SQLCLR機能を有効にする
sa等管理権限で下記を実行

sp_configure 'clr enabled', 1
RECONFIGURE
GO

Visual Studioで[SQL Serverデータベース プロジェクト]を作成する。
CLR関数等を作ってビルドすると、[ *.dacpac ]ファイルを得られる。

SSMS (Microsoft SQL Server Management Studio)で管理者として接続し、インスタンス配下の[データベース]にて右クリック
データ層アプリケーションの配置で配置できる。

例えば以下のようなプロシージャ[BINARY_CHECKSUM で本当はよいはず]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString HASHBYTES_CLR(SqlBinary bites)
    {
      if (bites == null || bites.IsNull)
        return new SqlString(string.Empty);

      string base64str = null;
      using (System.Security.Cryptography.SHA1 sha1 = new System.Security.Cryptography.SHA1Managed())
      {
        base64str = Convert.ToBase64String(sha1.ComputeHash(bites.Value));
      }

      return new SqlString(base64str);
    }
}

これを下記のようなクエリで確認可能

SELECT
  TAB1.SIKUCHOSON,
  TAB1.Shape AA,
  TAB2.Shape BB,
  LEN(Database1.dbo.HASHBYTES_CLR(TAB1.Shape.STAsBinary())),
  LEN(TAB1.Shape.STAsText())
FROM
 MapGdb.GIS.JPN_1 TAB1
LEFT OUTER JOIN
 MapGdb.GIS.JPN_2 TAB2
ON
 TAB1.OBJECTID = TAB2.OBJECTID
WHERE
 Database1.dbo.HASHBYTES_CLR(TAB1.Shape.STAsBinary()) <> Database1.dbo.HASHBYTES_CLR(TAB2.Shape.STAsBinary())

解説
MD5やSHA1などのハッシュ値は認証やデジタル署名、ファイルが改ざんされていないことの確認などに使用される。
アプリでは、「変更がありますが、保存しますか?」などの表示を出すかどうか判定によく使ったりする。
詳しくは調べれば出てくる。

どんな複雑なジオメトリでもハッシュ固定値で比較すれば、差分抽出が可能で生データ比較より高速化可能であるはず。
INSERTやUPDATE時にシステムカラムとしてメンテすれば、取得時との状態変化を比較しやすい上データ量増加も固定値微増程度。
オフラインの仕組みとしても検討できる。
また、更新フラグ等の立たない更新系の処理においても当然比較として使える可能性がある。

PG上においては上記の通りだが、一応SQL Server上のデータとしてはBINARY_CHECKSUM があるのでこれを使おう。

カテゴリー: 開発, 設計 タグ: , パーマリンク