MS_CrossDBSupport - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

クロスDB対応

概要

クロス DB 対応のポイントをまとめた。

補足（そもそもクロスDB対応が要るか）: 「将来どの DBMS にも載せ替えられるように」
という理由だけで抽象化を作り込むのは、多くの場合コストに見合わない。
クロス DB 対応が本当に必要になるのは、

  • パッケージ製品として複数 DBMS 環境に納入する
  • 開発は SQLite / LocalDB、本番は SQL Server のように環境で使い分ける
  • 移行（移行・マイグレーション）の過渡期に両対応が要る

といったケースである。
逆に接続先が確定しているなら、その DBMS 固有の機能を使い切るほうが
性能も開発効率も高い（データプロバイダ）。

詳細

SQL

以下の様に差異があるため、

クロス DB の際は、

  • SQL を外部ファイル化し、
  • DB によって参照先のファイルを切り替える

等の方式が良い。

基本的な構文

  • DELETE FROM の FROM があるとエラーになるケース

  • JOIN の構文が異なるケース

    • INNER JOIN が使えない。
    • RIGHT OUTER JOIN が使えない。

ヒント

  • ロック ヒント
  • クエリ ヒント（プラン）

ストアド・関数

  • Transact-SQL、PL/SQL などの
    ストアド・関数の定義や手続き記述の構文

  • バインド変数の記号

    • Oracle　　：「:」
    • その他 DB　：「@」

補足（差異の代表例）: 実際に問題になりやすい箇所を整理する。

観点 SQL Server Oracle PostgreSQL
件数制限 TOP n / OFFSET-FETCH ROWNUM / FETCH FIRST LIMIT / OFFSET
文字列連結 + / CONCAT || ||
空文字と NULL 区別する '' は NULL 扱い 区別する
日付の現在値 GETDATE() SYSDATE now()
NULL 置換 ISNULL / COALESCE NVL / COALESCE COALESCE
識別子の引用 [name] "name"（大文字が既定） "name"（小文字が既定）
採番 IDENTITY / SEQUENCE SEQUENCE SERIAL / IDENTITY
ダミー表 不要（SELECT 1） FROM DUAL が必要 不要

特に **Oracle の「空文字は NULL」**は、
移植時に業務ロジックの判定結果が変わるため注意が要る。
標準に寄せるなら COALESCE / CASE / FETCH FIRST を使うが、
古いバージョンでは使えないこともある。

補足（SQL の外部ファイル化）: 本文の「SQL を外部ファイル化する」方式は、
Open棟梁の動的パラメタライズド・クエリ（OTR_DynamicParameterizedQuery.md）や
かつての iBATIS / MyBatis と同じ発想である。

方式 利点 欠点
SQL を外部ファイル化し DBMS 別に切り替え 各 DBMS で最適な SQL が書ける ファイルが DBMS 数だけ増える
ORM に任せる（EF Core 等） プロバイダを差し替えるだけ 生成 SQL を制御できない。プロバイダ差も残る
標準 SQL に寄せて 1 本化 管理が簡単 性能を出しにくい。差異を完全には吸収できない

現実には、大半は ORM、性能や方言が問題になる箇所だけ
外部ファイル化した SQLという併用が扱いやすい。

データプロバイダ

インターフェイス

現 ADO である ADO.NETでは、

  • 旧 ADO
  • OLE DB
  • ODBC
  • JDBC

などと同じく API のインターフェイスは、ある程度標準化されている。

切替方式

  • ベンダ毎に別々のデータプロバイダ（バイナリ）を提供している。

  • このため、切替方式は、

    • 内部で使用するドライバを切り替えるスタイルではなく、
    • 使用するデータプロバイダ（バイナリ）自体を切り替えるスタイル。

  • それぞれのデータプロバイダの、名前空間.クラス型が異なるため、
    以下に列挙した汎用型等を使用してプログラミングする必要がある。
    ＃若しくは、自前の DB 部品等でラッピングする等の対策を取る必要がある。

移行メモ（誤字）: 元ページの「データプロバイダ（バイナリ）事態」は
「バイナリ）自体」の誤記。

補足（DbProviderFactory による切り替え）: 汎用型を使う場合、
インスタンスの生成をどうするかが問題になる。
これを解決するのが DbProviderFactory である。

// .NET Framework: 構成ファイルの providerName から取得
DbProviderFactory f = DbProviderFactories.GetFactory(providerName);

// .NET Core 以降: 明示的な登録が必要
DbProviderFactories.RegisterFactory(
    "Microsoft.Data.SqlClient", SqlClientFactory.Instance);

using DbConnection cn = f.CreateConnection();
cn.ConnectionString = cs;
using DbCommand cmd = cn.CreateCommand();

.NET Core 以降は machine.config による自動登録が無いため、
アプリ側で RegisterFactory を呼ぶ必要がある点に注意。

実装の差異

  • インターフェイス
    そもそも、インターフェイスの非互換がある。

    • 型指定、サイズ指定が必須

      サイズ指定は渡すデータのサイズではなく
      DDL で定義したスキーマのサイズを指定

    • 名前バインドをサポートしていない

    などがある。

  • 機能
    JDBC と比べると ADO.NETの方が、

    • 標準化されていないスコープや
    • ベンダ独自機能の実装
      • フェッチ・サイズ（DataReader）
      • 配列バインド

    が多く、クロス DB 対応には注意が必要。

補足（サイズ指定が性能に効く）: 「サイズ指定は DDL のサイズを指定」は
単なる作法ではなく、実行プランに影響する。

SQL Server で nvarchar(50) の列に対し、
サイズを指定せずに SqlParameter を渡すと、
値の長さに応じて nvarchar(4000) などと推定され、
呼び出しごとに異なる SQL テキストになってプラン キャッシュが分断される
（SQL Server アドホック クエリ問題の監視）。
さらに、型が食い違うと暗黙の型変換でインデックスが使われなくなる
（SQL Server のインデックス）。

// 型とサイズを明示する
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = name;

汎用型

  • System.Data.Common 名前空間
    https://learn.microsoft.com/ja-jp/dotnet/api/system.data.common

    • System.Data.Common.DbConnection クラス
    • System.Data.Common.DbConnectionStringBuilder クラス
    • System.Data.Common.DbTransaction クラス
    • System.Data.Common.DbCommand クラス
    • System.Data.Common.DbParameter クラス
    • System.Data.Common.DbDataReader クラス
    • System.Data.Common.DbDataAdapter クラス

型のマッピング

特に、SELECT した結果セットの取得時、
DBMS 型と .NET（や Java）のネイティブ型とのマッピングが異なるので、
プログラム側でキャストやコンバートのコード記述が必要になる場合がある。

主に以下の型のマッピングが問題になることがある。

数値型

  • short
  • int
  • long
  • float
  • double
  • decimal

日付

  • DateTime
  • TimeSpan

例

例えば、

  • ODP.NET などでは、Oracle の NUMBER 型のサイズによって
    .NET のネイティブ型へのマッピングが変わってくる。

  • 型変換の方法

    • DataReader については、
      Get[型名](int index) という
      メソッドがあるためこれを使用できる。

    • DataRow については、
      上記に対応するメソッドがないため自前で
      キャストやコンバートをする必要がある。

補足（NUMBER 型が厄介な理由）: Oracle の NUMBER は
精度・位取りを指定しないと 38 桁の可変精度になり、
.NET のどの整数型にも収まらない。
ODP.NET はサイズに応じて Int16 / Int32 / Int64 / Decimal を
返し分けるため、DDL を変えるとアプリが落ちるという事故が起きる。

対策は、

  • DDL で NUMBER(9) のように精度を明示する
  • 読み取り側で GetDecimal() に統一し、アプリ側で変換する
  • OracleDataReader.GetOracleDecimal() のようなプロバイダ固有型を使う
    （ただしクロス DB 対応とは相反する）

日付についても、Oracle の DATE は時刻を含む、
SQL Server の datetime は精度が 3.33ms 刻み、
datetime2 は 100ns 刻み、といった差があるため、
等値比較や丸めの挙動が変わる点に注意が要る。

分離レベル

DBMS によってロック・分離戦略が異なるので注意する。

補足: これがクロス DB 対応で最も見落とされやすい差異である。
DBMSのロック・分離戦略と同時実行制御で
詳述されているとおり、

  • Oracle / PostgreSQL: 多バージョン法（MVCC）。参照はロックを取らない
  • SQL Server（既定）: ロック法。参照も共有ロックを取る

このため、Oracle で動いていた設計をそのまま SQL Server に載せると
参照がブロックされて性能が出ないという事象が起きる。

対策は、SQL Server 側で RCSI
（READ_COMMITTED_SNAPSHOT ON）を有効にして
多バージョン法に寄せること。
Azure SQL Database では既定で ON になっている。

また、サポートする分離レベル自体も異なる
（Oracle は READ UNCOMMITTED と REPEATABLE READ を持たない）ため、
IsolationLevel を汎用型で指定する場合は
全 DBMS で成立する範囲に収める必要がある。

参考

Tags: 移行, データアクセス, .NET開発, ADO.NET

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

⚠️ **GitHub.com Fallback** ⚠️