依存性を排除したデータベース
アプリケーション構築実践法
第35回エンバカデロ・デベロッパーキャンプ

エンバカデロ・テクノロジーズ セールスコンサルタント
筑木真志

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属しますアジェンダ OracleからSQL Serverに移行した事例のお話
 移行作業における問題点の考察とその解決
 まとめ

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します2

OracleからSQL Serverに移行した事例のお話
 その1：当時の状況と結果

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属しますとある現場で、このようなお願いをされました

お宅のシステムに興味あるのだけど、うちのSQLServerで動かないかな…

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します4

当時の状況 その1：システム条件
 おおむねこんな感じの良くある組み合わせです
項目

製品

OSWindows Server

データベース

Oracle + SQL Server

O/R マッパーMyBatis

Webサーバー

IIS

アプリケーションサーバー

Apache Tomcat + Apache Struts 2 + JSP

フロントエンド

jQuery UI

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します5

当時の状況 その2：データベース設計方針
 RDBはOracle XEを想定
 外部整合性やトリガーは使用しない
 RDBはデータの保持に徹する
 複雑なクエリや、汎用性のあるクエリはVIEWや表関数にする

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します6

当時の状況 その3：データベースの管理 テーブル定義書は、所謂Excel設計書
 マクロでテーブル定義書からDDLを生成する
 論理名を入力すると対応するフィールドの物理名と型・サイズを別の列に出力する
 ビューはSQL文をそのまま記載する

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します7

移行のストラテジー
 クライアント層とビジネスロジック層は変えない
クライアント層

上位層には手を加えない

ビジネスロジック層

データアクセス層

Oracle

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します

SQL Server

データアクセス層で対処する

8

その結果…
 問題なく移行できました！

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します9

でも…
 結構苦労もしました

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します10OracleからSQL Serverに移行した事例のお話
 その2：移行作業の簡単な概要

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属しますOracleとSQL Serverの主な違い
OracleSQL Server

構造

インスタンス/スキーマ/テーブル

インスタンス/データベース/スキーマ/テーブル

標準の認証方法

データベース認証

Windows認証、SQL Server認証

スキーマ

スキーマとユーザーが同一

スキーマとユーザーが分離

文字列型

VARCHAR2/CHAR

NVARCHAR/NCHAR

文字コード

UTF-8（日本語は主に3バイト）

UTF-16（日本語は主に2バイト）

文字列リテラルの表現

‘文字列’

“文字列”

文字列結合|| / CONCAT関数 ※引数２個+ / CONCAT関数 ※引数可変

NULL値判定

NVL(c, v)/NVL2(c, v1, v2)

ISNULL

日付型

DATE/TIMESTAMPSMALLDATETIME/DATETIME

型変換

to_char/to_number/to_dateなど

convertDUAL表ありなし

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します12

移植に当たって その0：事前準備
 O/RマッパーはMyBatisを使用していたので、MapperファイルをOracle用とSQL Server用に分離
 mybatis-config.xmlをOracle用とSQL Server用に作成し、ターゲット毎に切り替える

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します13

移植に当たって その1：データ型の差異の吸収 一般的なデータ型しか使っていなかったので機械的に置き換えた
 DDLはテーブル定義書から生成しているので、フィールド単位の差異はそこで吸収
OracleSQL Server

備考

VARCHAR2(n)NVARCHAR(n/3)

原則として1/3で切り上げ電話番号など日本語を使用しないフィールドについてはVARCHARを使用

CHAR

CHAR

CHAR型はフラグ類しか使わないルールだった

NUMBER(n,m)

DECIMAL(n,m)

DATETIMEDATETIME2

BLOB

BLOB

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します14

移植に当たって その2：互換関数の作成
 OracleにあってSQL Serverにない関数のうち、メジャーかつ、移植可能な物はストアドファンクションを作成
 参考： TRIM関数はSQL Server 2017でサポートされた本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します15

移植に当たって その3：複雑なSQL 複雑なSQLはVIEWもしくは表関数化 I/Oを同一にして、中身を入れ替える

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します16

移植に当たって その4：ストアドプロシージャ
 一生懸命移植する• SQL Server Migration Assistantの力を借りるのは非常に有効な手段

何が「依存性を排除する」だ！！！！

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します17

移植に当たって その5：テスト
 同じデータを与えて、結果が同じかどうかで同一性を担保する• 単体テストのテストケースを流用するのがベスト• 投入データとそのエビデンスは「未来の自分のために」必ず残す

 データアクセス層での同一性が担保出来れば、それより上層のテストは不要• Oracle版とSQL Server版の両方で「総合テスト」やらないよね…
• やりたいのであれば半々にするとか• でも、最低、疎通確認くらいはしようね…

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します18

移植に当たって その6：データ移行
 マスタデータは片方を正とし、一方向に変換する
 データの移行は以下の方法を使用• BULK INSERT/SQL Loader• INSERT文そのもの• 各種ツールの使用
•••
•
•
•

SQL Developer
SQL Server Management Studio
SQL Server インポートおよびエクスポート ウィザード
SQL Server Migration Assistant
A5:SQL Mk-2など

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します

19

移行作業における問題点の考察とその解決なにが問題だったのか？

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します移行作業の流れと登場人物の復習

データベース層• Excel

ビジネスロジック層••••

Oracle/SQL Server
TomcatStruts2
MyBatis

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します

クライアント層• JSP• jQuery UIデータ移行• 各種ツール

21

登場人物、多くありませんか？

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します22問題点その1：Excelブックによるデータベース管理
 Excelなので、誰もが気軽に弄れてしまいます。

増殖するテーブル定義書。
どれが正？

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します23

問題点その2：特定DBに依存したシステム
 今回は対応するDBが2種類なのでDDLを出力するマクロの修正量があまりありませんでした
 MyBatisなどのマッパーファイル、メンテナンスし続けられますか？
 今後、Db2とかPostgreSQLとかMySQLなどに対応する場合は？
• データベースが増える度にマクロを書き換えますか？
• そして、そのテーブル仕様書はメンテナンスし続けられますか？

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します24

問題点その3：曖昧になった三層構造
 StrutsのView層にビジネスロジックが記載されていた• View層は画面に関することであって、ビジネスロジックはModel層に記載すべき

 面倒な画面の実装とそのデバッグ• 画面表現がJSP側とjQuery UI側とで分離している• メンテナンスしづらいですよね？

 この処理の守備範囲はどっち？

• 入力値のバリデーション• あるコードがデータベースにあるかどうか？

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します25

移行作業における問題点の考察
エンバカデロのツールを使った解決法

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属しますエンバカデロのツールを使って改善します

データベース層

ビジネスロジック層

Oracle/SQL Server
TomcatStruts2
MyBatis• Excel••••• ER/Studio• Delphi/C++Builder• RAD Server• FireDAC

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します

クライアント層• JSP• jQuery UI• Delphi/C++Builder• Sencha

データ移行• 各種ツール• Delphi/C++Builder• FireDAC

27

改善その1：ER/Studioでデータベース設計を一元化する
 データのモデリング
 データベースに応じたDDLの生成
 テーブル定義書の生成

Excel設計書からの脱却！

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します28

改善その2：特定データベースの依存性解消
 ER/StudioとFireDACは多種多様なデータベースに対応• 管理：ER/Studio• 構築：Delphi/C++Builder ＆ FireDAC• FireDACはマクロ機能にてデータベースの依存性をカバー出来ます
エンタープライズデータベース

and more…

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します29改善その3：システム内の役割の明確化
 システム内の役割分担が明確になった見通しの良いシステム
 フロントエンド、バックエンド共に多彩な選択肢
 レガシーなシステムのモダナイズ
階層選択肢

クライアント層

Delphi/C++Builder
Senchaなど

ビジネスロジック層

RAD Server ＆ Delphi/C++Builder

データベース層

Delphi/C++Builder ＆ FireDAC

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します30

まとめ KISSの原則
 単純化する事による、役割分担の明確化
 昨日の自分は他人。そして、明日の自分も他人 移行ツールは移行ツール管理ツールは管理ツールです• 餅は餅屋。Excel仕様書で管理するのは止めましょう！

 残す物は残す。捨てる物は捨てる物の見極めが肝心

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します31

THANKS!
www.embarcadero.com/jp
第35回 エンバカデロ・デベロッパーキャンプ

本文書の一部または全部の転載を禁止します本文書の著作権は、著作者に帰属します