📌 目次
- システムをリバースエンジニアリングして設計書を作成するシチュエーション
- 一般的なリバースエンジニアリングの方法はコード解析である
- アプリ更改での解析手法
- アプリで入力項目を入れて実行するなどで値を発生させたあとに、読み書きや参照が発生するテーブルを特定する
- 設計書の作成や開発を自動化できる部分
- ストアドの修正とデバッグ
- ストアド宣言時のIN/OUT一覧表を作成するMySQLのクエリ例
- ストアドのコード内のすべてのストアド呼び出しのIN/OUT一覧表を作成するMySQLのクエリ例
- ER図の自動作成
- 最後に
①システムをリバースエンジニアリングして設計書を作成するシチュエーション
アプリのやデータベースの仕様書や設計書が無いというのは、実はよくある話です。
その場合に、どのような設計書が必要になるのかを決めて、分析方法考えるところからスタートします。
②一般的なリバースエンジニアリングの方法はコード解析である
コード解析が最も一般的に行われる理由は、エンジニアは改修対象のコードと設計書だけ受け取ることが多く、打ち手が限られるためです。コード解析は、可読性やシステムの仕組みが複雑になるほど、ミスが発生しやすいのです。たとえば、変数の見間違いや、条件分岐の誤解、複雑な処理フローの見落としなどが想定されます。コード解析はエンジニアのレベルに依存し、正しく行われたかどうかのダブルチェックも非常に難しいという点も問題です。
③アプリ更改での解析手法
1. ブラックボックス解析
UIやコードを動かして動作を確認します。ST(総合テスト)のようにUIでの確認が多いです。これだけではシステムの内部処理が見えないため、開発者への指示書や受入テストで実施されることが多いです。開発者がプログラム単体のバックエンド処理を確認するために、引数を渡してプログラムをキックすることもあります。
2. ソースコード解析
可読性が悪いコードや複雑なロジックの場合、エンジニアの理解ミスや見落としが生じやすい。分析結果のダブルチェックは、PMなどが**"テックリード"**できる場合のみ可能である。
3. データフロー解析
データのリレーションシップを分析して、マッピング表を作成できます。プログラム単位でのIN/OUTを調査する一般的な方法です。
4. データトレース
システムがログファイルやテーブルのカラムへ格納した実データを確認する方法です。カラムの実態を直感的に把握できるので開発の初期からあるのが望ましいです。開発後の総合テストに必要で、すべてのカラムの正常系テストのデータセットとして存在するのがベストです。テストの話になってしまうが、バリデーションチェックなど、異常系テストのデータセットも用意できるとよい。
5. データリネージ
データの流れや変換過程を全て追跡します。大規模システムで、バグが許されず、複雑な処理でデータの変換や統合を頻繁に行うシステムでは実施されます。
例:
[テーブルA.カラムA] → プログラムAの処理 ["カラムA + カラムB"] → プログラムBの引数で処理され [boolean型] に変化 → [テーブルB.カラムB] に格納
④アプリで入力項目を入れて実行するなどで値を発生させたあとに、読み書きや参照が発生するテーブルを特定する
アプリで入力項目を入れて実行するなどで値を発生させたあとに、読み書きや参照が発生するテーブルを特定するのが、最も確実な影響範囲調査になります。テーブルへのアクセス履歴の確認方法についてMySQLでひとつ例を挙げると、performance_schema.events_statements_summary_by_table を利用して直近の各テーブルのアクセス頻度や最終アクセスの履歴を取得できます。
データリネージを完全に行うには、変数や文字列結合を全て調査する必要があるため、すべてのデータを完全に追跡することは難しい場合があります。また工数がかかるので、採用されないことが多いです。
⑤設計書の作成や開発を自動化できる部分
1. 開発者用に現行仕様でのサンドボックス環境または開発環境を用意する
修正前と修正後のテストで、同じデータを入力して、動作に差異があるか比較をする際に必要です。
開発者がアプリの概要を簡単に理解できます。修正しながら影響範囲や動作結果を確認できます。
2. 開発環境に**AWS SCT (Schema Conversion Tool)**をインストールしてスキーマ情報を読み込む
AWS SCTは、AWSのアプリ配布ページからインストーラをダウンロードして簡単にインストール可能です。スタンドアローン実行が可能で、ローカルのDBスキーマを読み込んで利用できるため、大企業のセキュリティルールにも抵触しにくいです。
システムと同じ環境にAWS SCTをインストールしてスキーマを読み込み使用できます。AWS SCTのエクスポート機能を利用して、他の環境のAWS ECSにインポートすることも可能です。
作業者に渡したくないスキーマ情報は、スキーマ読み込み後に削除することができます。
DBのスキーマのバージョン管理はGitなどで行うことが多いですが、この作業で完結してもよいです。
3. AWS SCTでストアドを異なるDBの書き方へコンバート
AWS SCTは、コンバート機能により異なるDB間での移行作業を自動化できます。コンバートが失敗するのは変数を含むSQLや特定の構文で、規則性がある場合です。
コンバート時のサマリー画面にはWBSと根拠が作成されます。具体的には、[自動コンバートできる割合、修正工数、ストアドごとの複雑さ]などが表示されます。
[コンバート → 修正 → ストアドの保存]までをAWS SCT内で完結できるため、管理が容易になります。
ここまでがAWS SCTを活用することの主なメリットです。
⑥ストアドの修正とデバッグ
AWS SCTではストアドの実行エラーやコンパイルエラーの確認ができないため、手作業での修正やデバッグは、A5:SQL Mk-2などのSQL開発ツールを使用したほうが良いです。
ここまでがコーディングフェーズになります。
⑦ストアド宣言時のIN/OUT一覧表を作成するMySQLのクエリ例
スキーマ情報から正確な情報を抽出できます。
SELECT sp.name AS ProcedureName,
p.name AS ParameterName,
CASE WHEN p.is_output = 1 THEN 'OUT' ELSE 'IN' END AS Mode,
t.name AS DataType
FROM sys.parameters p
JOIN sys.objects sp ON p.object_id = sp.object_id
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE sp.type = 'P'
ORDER BY sp.name, p.parameter_id;
クエリの出力例
| Procedure Name | Parameter Name | Mode | Data Type |
|---|---|---|---|
| proc_test | param1 | IN | INT |
| proc_test | param2 | OUT | VARCHAR |
| sample_proc | paramA | IN | DECIMAL |
⑧ストアドのコード内のすべてのストアド呼び出しのIN/OUT一覧表を作成するMySQLのクエリ例
全てのストアドのコード内を参照して、ストアド名が含まれている場合のIN/OUTを抽出するクエリです。
変数を頻繁に使う書き方だと検出されない可能性があるので、100%は信用しないでください。
SELECT
r.ROUTINE_SCHEMA AS `Database`,
r.ROUTINE_NAME AS `Routine Name`,
r.ROUTINE_TYPE AS `Routine Type`, -- PROCEDURE or FUNCTION
p.PARAMETER_NAME AS `Parameter Name`,
p.PARAMETER_MODE AS `Mode`, -- IN, OUT, INOUT
p.DATA_TYPE AS `Data Type`,
p.ORDINAL_POSITION AS `Position`, -- パラメータの順番
CONCAT(r.ROUTINE_NAME, '_', p.PARAMETER_NAME) AS `Variable Name`
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS p
ON r.SPECIFIC_NAME = p.SPECIFIC_NAME
AND r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
WHERE r.ROUTINE_SCHEMA = DATABASE() -- 現在のDBのみ対象
ORDER BY r.ROUTINE_NAME, p.ORDINAL_POSITION;
クエリの出力例
| Database | Routine Name | Routine Type | Parameter Name | Mode | Data Type |
|---|---|---|---|---|---|
| mydb | proc_test | PROCEDURE | param1 | IN | INT |
| mydb | proc_test | PROCEDURE | param2 | OUT | VARCHAR |
| mydb | func_calc | FUNCTION | RETURN | OUT | FLOAT |
| mydb | another_proc | PROCEDURE | paramA | IN | DECIMAL |
⑧ER図の自動作成
TABLE CREATE文のスキーマ情報を分析することで、ER図を自動作成できます。以下のツールを活用すると、DBのスキーマ情報を読み込んで自動生成が可能です。
- dbdiagram.io
- DBeaver
- A5M2
また、AWS環境を利用する場合は AWS SCT (Schema Conversion Tool) も有効です。
⑨まとめ
皆様が参画中のプロジェクトで使えそうなノウハウがあれば積極的に取り入れてみてください。
紹介した分析方法の成果物一覧
| 設計書・図の名称 | ブラックボックス解析 | ソースコード解析 | データフロー解析 | データトレース | データリネージ | AWS SCT |
|---|---|---|---|---|---|---|
| 仕様書 | ○ | ○ | ○ | ○ | ○ | × |
| マッピング表 | × | ○ | ○ | ○ | ○ | × |
| システムフロー図 | ○ | ○ | ○ | × | × | × |
| ER図 | × | ○ | ○ | × | × | ○ |
| データフロー図 | × | ○ | ○ | ○ | ○ | × |
| データリネージ図 | × | ○ | ○ | ○ | ○ | × |
| ストアドプロシージャI/O定義書 | × | ○ | ○ | × | ○ | ○ |
| AWS ECS変換レポート | × | × | × | × | × | ○ |
参考資料: 設計書一覧
設計書の一覧を以下に示します。
1～9までは上記の表に含まれます。
それ以外で必須な設計書には、データ移行計画書、データマッピング定義書、API仕様書、バッチ処理一覧 などがあります。
| No. | 設計書・図の名称 | 内容概要 |
|---|---|---|
| 1 | 仕様書 | 現行仕様の詳細な記述 |
| 2 | マッピング表 | データのリレーションと流れを可視化 |
| 3 | システムフロー図 | システム全体の処理フロー |
| 4 | ER図 | データベースのスキーマ構造 |
| 5 | データフロー図 | データの流れと変換を視覚化 |
| 6 | データリネージ図 | データの変遷を追跡 |
| 7 | ストアドプロシージャI/O定義書 | ストアドプロシージャの入出力情報 |
| 8 | AWS ECS変換レポート | DBコンバートの成功率やエラー分析 |
| 9 | システム構成図 | ハードウェア・ソフトウェア・ネットワークの構成を示す |
| 10 | 業務フロー図 | ユーザーの業務手順を可視化し、移行の影響範囲を特定 |
| 11 | アプリケーション機能一覧 | システムの機能を一覧化し、移行時の対応範囲を整理 |
| 12 | 画面遷移図 | アプリの各画面とその遷移関係を図示 |
| 13 | API仕様書 | 外部システムとの連携APIの仕様を記載 |
| 14 | バッチ処理一覧 | バッチプログラムの処理内容、実行順、スケジュールを整理 |
| 15 | テーブル定義書 | 各テーブルのカラム構成、データ型、インデックス情報を記載 |
| 16 | インデックス設計書 | パフォーマンス最適化のためのインデックス戦略を整理 |
| 17 | データ移行計画書 | 旧システムから新システムへのデータ移行方針や手順を記載 |
| 18 | データマッピング定義書 | 旧DBと新DBのテーブル・カラムの対応関係を整理 |
| 19 | データクリーニング方針書 | データ移行時に不要データの削除や変換ルールを定義 |
| 20 | 移行スクリプト一覧 | データ移行用のSQLスクリプトやプログラム一覧 |
| 21 | 運用設計書 | 移行後のシステム運用ルール、障害対応手順を記載 |
⑩最後に
最小限の設計書とシンプルなコードで開発するにはどうしたらいいかを考えたとき、DBに設計書とコードを埋め込み、いつでも動的に確認できるものがベストではないかと思いました。先人は「メタデータ駆動型アーキテクチャ」と名付けたようです。
その開発手法に必要な調査が データリネージ であり、この記事にも少し書きました。
次回以降は、
✅ メタデータ駆動型アーキテクチャによるコーディング
✅ AWS SCTの紹介
について解説していきたいと考えています。