CodexのSkillsを使ったPostgreSQLからSQL Serverへのストアド移行

概要

とあるシステムリプレイスの案件で、RDBをPostgreSQLからSQL Serverに変更する要件があり、その中でストアド・プログラム（以下、ストアド）をPostgreSQLからSQL Serverに変換する作業をした。

具体的な方法として、AIエージェント（Codex）のスキル（Skills）を使って変換する形をとった。以下はその備忘録。

要件

• 社内システムのデスクトップアプリを、ASP.NET Coreを使ったWebアプリに移行する
• 既存システムのRDBはPostgreSQLだが、リプレイス後はSQL Serverを使用する
  • それに伴いテーブル・ビュー・ストアドなどのRDBオブジェクトをSQL Serverに移植する

開発環境・使用ツール

• MacBook pro(Appleシリコン)
  • 開発用端末としてはMacを使用
• Docker
  • ローカルでのDB環境構築用
• VS Code
  • 開発エディタとして使用
• DBever
  • SQLクライアントとして使用
• Codex
  • AIエージェントとしてCodexが使用できる環境だったので、AIは主にCodexを使用
  • Skillsを使って変換作業を効率化
• .NET 10
  • 対象のWebアプリ、及び自動テストはC#で実装
• Gauge
  • Markdownでテストシナリオを書くためのフレームワーク
  • ストアドのテストをGaugeを使って実装

環境構築のはまりポイント

DB環境はDockerを使って構築したが、MacだとDocker上でもSQL Serverコンテナイメージは動作しなかった。どうやらSQL ServerコンテナはARMでは動かないらしい。代わりに、Mac上でも動作して、SQL Serverと互換性のあるDBとしてAzure-SQL -Edgeがあったので、Dockerイメージはこれを使用。

テーブルとビューの変換

ストアドの変換をする前に、テーブルやビューの変換を行った。
SQLクライアントツールからDDLを抜き出し、型変換が必要な部分だけAIで変換。考慮するポイントはいくつかありましたが、基本的には機械的な変換で対応可能だったので、比較的短期間で完了。

ストアドのおさらいとRDB間の差異

ストアドを移植するにあたり、ストアド・プログラムに関する基礎知識と、PostgreSQLとSQL Server間での差異について知っておく必要があったので、おさらいしながら簡単に整理。

ストアドのおさらい

RDBのストアド・プログラムは大きくファンクションとプロシージャに分かれる（厳密にはトリガーもあるが、ここでは割愛）。

ファンクションとプロシージャは一般には以下のように認識される。

• ファンクション
  • パラメータを受け取って戻り値を返す処理。いわゆる関数
  • DML（SELECT文など）の中で使用することができる
  • トランザクション制御、エラーハンドリングなどはできない場合が多い
• プロシージャ
  • 戻り値はない（パラメータは受け取れる）
  • SELECT文から呼び出すことはできず、EXECなどの構文で呼び出す
  • トランザクション制御、エラーハンドリングなどが可能

ただし、これらは一般論であり、細かい仕様はRDBによって大きく異なる。
SQLの構文は標準規格によってどのRDBでもある程度統一されているが、ストアドに関しては構文も仕様もRDB毎の差異が大きい。

PostgerSQLとSQL Serverのストアドの差異

細かく見ると様々な違いがあるが、以下の差異が移行作業するにあたってポイントになった。

• エラーハンドリングの違い
  • PostgreSQLのファンクション：エラーハンドリングが可能
  • SQL Serverのファンクション：エラーハンドリング不可
• ファンクションの戻り値の違い
  • PosrgreSQL：ユーザー定義型という独自の型を定義して返せる
  • SQL Server：PostgreSQLで言うところのユーザー定義型に該当するものはない
• プロシージャの違い
  • PostgreSQL：値は返せない
  • SQL Server：結果セットを呼ばれるデータ返すことができる

移行の方針

既存システム（PostgreSQL）のストアドは、割合としてファンクションが多かった。その中には、エラーハンドリングが含まれているものや、戻り値としてユーザー定義型の値を返すようなファンクションもあった。

しかし、先の比較で見た通り、SQL Serverのファンクションはエラーハンドリングが出来ず、ユーザー定義型に該当するものもない。そのため、PostgreSQLでファンクションとして定義されていたとしても、SQL Server側でもファンクションとしてそのまま移植することが難しかった。

一方、SQL Serverの場合はプロシージャで結果セットと呼ばれるデータを返すことができます。また、プロシージャであればエラーハンドリングが可能。

そういった背景から、PostgreSQLではファンクションとして定義されているプログラムは、処理内容によってはSQL Serverではプロシージャとして作成した。

スキルによる変換とチェック

元々ファンクションだったものを条件に応じてファンクションかプロシージャに振り分ける必要はあったものの、ロジックはそのまま引き継ぐため、ある程度機械的な変換で対応可能。機械的な変換はAIの得意分野なので、ストアドの変換も基本的にAIを使って実施することにした。

毎回変換ルールについてプロンプトに詳細を伝えるのは面倒なので、スキル（Skills）を使うことにした。プログラムの変換部分だけではなく、ドキュメント生成スキルや変換後のチェックなど、いくつかのスキルを作成して精度を上げるように試行錯誤した。

最終的には、以下の流れで変換作業が行われるようにした。

1. 既存コードからMarkdownドキュメントを生成
   • パラメータや戻り値、読み取りテーブルや更新対象テーブル、処理概要や副作用などをまとめたもの
2. 予め決めたルール（SKILL.mdに定義）に従って変換
3. 変換後に、ドキュメントと内容を照らし合わせて矛盾がないかチェック
4. ドキュメントとの整合性チェックや、コード間での差分がある場合は、差分確認用の資料を別で出力

スキルとしてはドキュメント生成とストアド変換の2つを作成した。変換のスキルの中で2つのフェーズを用意。1つ目のフェーズとして変換を行い、2つ目のフェーズとして整合性チェックと差分出力をしてもらうようにした。

この仕組みで、変換の精度を上げつつ、変換後にどのような差異があるかを人間がチェックしやすい形にした。

自動テストによる検証

スキルによる変換で、構文エラーなどはなく登録できる状態にまではスムーズに実現できたが、実行結果が同じになることが確認できなければプログラムとして同じ内容であることを保証できない。

1つ1つのストアドに対して、戻り値や更新されたレコードの結果を自動で比較してくれる仕組みとした。テスト作成にあたっては、自然言語でテストステップを記述可能なGaugeを導入。

1つのストアドに対しては、以下のようなテストシナリオを作成。

# SampleProcedure1 のテスト

// SampleFunction1(PostgreSQL) → SampleProcedure1(SQL Server)

## 正常系のテスト

### PostgreSQL

// 更新対象テーブル
* "postgresql" の "table1" のデータを削除する

// 読み取り対象テーブル
* "postgresql" の "table2" のデータを削除する

// 読み取り対象テーブルのデータ作成
* "postgresql" の "table2" にCSV "seed/table2.csv" のデータを投入する

// ストアド実行
* "postgresql" でSQL "SELECT SampleFunction1('param1', 'param2').result;" を実行する
* 実行結果が "ok" である

// CSV出力
* "postgresql" の "table1" テーブルのデータを "output/postgresql/table1.csv" に出力する

### SQL Server

// 更新対象テーブル
* "sqlserver" の "table1" のデータを削除する

// 読み取り対象テーブル
* "sqlserver" の "table2" のデータを削除する

// 読み取り対象テーブルのデータ作成
* "sqlserver" の "table2" にCSV "seed/table2.csv" のデータを投入する

// ストアド実行
* "sqlserver" でプロシージャ "SampleProcedure1" をパラメータ "'param1', 'param2'" 実行する
* 実行結果が "ok" である

// CSV出力
* "sqlserver" の "table1" テーブルのデータを "output/sqlserver/table1.csv" に出力する

### CSV出力結果の比較
* CSV "output/postgresql/table1.csv" と "output/sqlserver/table1.csv" の中身が一致すること

テストの流れとしては

1. DockerでPostgreSQLとSQL Server環境を用意し、同じテーブル環境を作成
2. CSVでテストデータを用意
3. CSVデータ取り込んで（PostgreSQLとSQL Serverそれぞれの）テストデータを作成
4. ファンクション（及びプロシージャ）を実行
5. 戻り値を検証
6. 更新されたテーブルをCSV出力し、出力結果が同じことを検証

このようなテストシナリオを変換するストアドの数だけ作成し、各プログラムで実行結果に差異がないかどうかを検証した。

比較結果で差分が出るケースもあったけれど、その際は出力結果のCSVとストアドの内容からAIに原因の分析・調査をしてもらい、そのままAIに修正してもらう形で対応した。

最初からAIによる変換だけで100%同じ結果にはならないだろうと予測していたけれど、検証の結果、やはりいつくかの差分が出た。当然ながらコードの行数が長いストアドで差分が出やすかった。
とはいえ、自動テストと組み合わせながら差分の修正を重ねたことで、より高い精度での変換になったかと思う。
テストデータのパターンとして全ての分岐パターンを網羅できているわけではおそらくないけれど、実際の運用で使用しているデータでもテストを行い、最低限の品質は保証できたかと思う。

テストの作成に関しても、最初はスキルを使ったテストデータとテストシナリオの作成を試行錯誤した。出力の精度としては悪くなかったけれど、微調整の必要な部分が多々あったので、結局多くの部分を手動で作成した。ストアドの数がもっと膨大だった場合は、スキルをもっと上手く調整して、テストの作成もスキルで実施したかもしれない。

所感

AIエージェント（Codex）のスキルは、以前にブログ記事のレビュー用スキルと、機能改修する際の影響範囲と修正箇所調査を調査するスキルを作ったことがある。その時はどちらも思ったほどの精度が出ず、結局のところ毎回プロンプトを細かく調整して依頼した方が望んだ結果を得やすいなー、という感覚だった。

今回のストアド変換の作業に関しては、スキルを比較的有効活用できたように思う。
変換したいストアド毎に指示の出し方を分ける必要はなく、しかし予め決めた細かな変換ルールに従って変換してほしい、というケースだったので、このようなケースではスキルがかなり便利だと分かった。

たしかし、機械的な作業をどのようなルールにで行うのかを確定させるために、色々と試行錯誤しながらSKILL.mdを調整していくことの難しさも感じた。今回、ストアドの変換やテスト作成のスキルは試行錯誤しながらSKILL.mdを何度も修正した。
試行錯誤しながらSKILL.mdの精度を上げておくことが長期的な作業の効率化と品質向上につながる見込みがあるのなら、今後もスキルを活用する余地がありそう。

今回、ストアドの移行作業をしたが、その作業のほとんどをAIに任せたため、ストアドそのものに関する知識や経験は必要最低限しか身についていない。学習を優先するのか、それとも作業スピードを優先するのか、みたいな目的の違いによっても、スキルを使うべきか否かの判断は分かれるかも知れない。今回の作業でAIのスキルの使い所と使い方の勘所はなんとなく分かってきた気がする。今後も使い所を見極めて有効活用していきたい。