JetBrains IDEで外部キーを表示できるよう、Cloudflare D1向けJDBC driver「d1-jdbc-driver」を修正するプルリクを作った

前回、自作のアプリを Cloudflare Pages + D1 に乗せてみました。
Hono + React + TanStack Router + TanStack Query + Chart.js + Drizzle ORMなアプリを、Cloudflare Pages と D1 に乗せてみた - メモ的な思考的な

Cloudflare D1にあるテーブルの

• Cloudflare の D1 のページ
• D1 Manager
  • https://github.com/JacobLinCool/d1-manager

で確認できます。

一方、アプリはWebStormなどのJetBrains IDEで開発しているため、JetBrains IDEでも Cloudflare に直接接続できると便利そうでした。

　
何かないか調べたところ、 d1-jdbc-driver がありました。READMEに従い設定してみたところ、JetBrains IDEのWebStormから接続できました。
https://github.com/isaac-mcfadyen/d1-jdbc-driver

　
ただ、Known issueとして

Foreign keys are not currently shown in the introspection window, although they are still there and working as normal.

と、外部キーまわりのサポートがないようでした。

それがあると嬉しいと思いつつGithubのissueをながめていたところ、同じように外部キーまわりの機能を望んでいる方がいるようでした。
https://github.com/isaac-mcfadyen/d1-jdbc-driver/issues/4

　
そこで、

• 少なくとも、テーブルに foreign key だけでも表示できるといいなと思ったこと
• JDBC driver の修正方法が気になること

から、修正してプルリクを出してみたところ、早速マージしていただきました。

　
ということで、今回の記事では、修正するために調べたことをメモとして残します。

　
目次

環境

• Windows 11 WSL2
• IntelliJ IDEA 2023.3

調査

そもそもJavaやJDBC driverに詳しくないので、基本的なところから調べ始めました。

KotlinでJDBC driverを書けるかについて

以前、JetBrains IDEのプラグインを書いた時に Kotlin を使いました。
RubyMine 2023.3系から、rails routes を便利に扱える Railways プラグインが動かなくなったので、代替プラグイン Railroads を作りました - メモ的な思考的な

そのため、JavaではなくKotlinでJDBC driverを書けるといいなと思って調べたところ、「書ける」と書かれたstackoverflowの回答がありました。
Is it applicable to write a JDBC driver in Kotlin? - Stack Overflow

とはいえ、現在の d1-jdbc-driver はJavaで実装されているため、Kotlinで書くのは別の機会にしました。

外部キーを表示するために必要な JDBC driver の修正について

現在の d1-jdbc-driver の実装では、いくつか SQLException("Not implemented: ***") となっているメソッドがありました。

このうち、外部キーに関係するメソッドを実装すれば、いい感じにJetBrains IDEで表示できるかもしれないと考えました。

　
次に、他のJDBC driverの実装を調べてみたところ、Microsoft の SQL Server 向け JDBC driver に詳しいドキュメントがありました。

ドキュメントを読むと

• getCrossReference メソッド (SQLServerDatabaseMetaData) - JDBC Driver for SQL Server | Microsoft Learn
• getImportedKeys メソッド (SQLServerDatabaseMetaData) - JDBC Driver for SQL Server | Microsoft Learn
• getExportedKeys メソッド (SQLServerDatabaseMetaData) - JDBC Driver for SQL Server | Microsoft Learn

あたりで、 外部キー という記載がありました。

そのため、まずはこれら3つのメソッドを修正するところから始めることにしました。

SQLiteの外部キー情報を設定する方法について

修正対象のメソッドは分かりましたが、修正するためにはSQLiteの外部キー情報をどのように設定すればよいかが分かりません。

そこで、 d1-jdbc-driver の既存の実装を見たところ、主キーの情報を設定している箇所がありました。
https://github.com/isaac-mcfadyen/d1-jdbc-driver/blob/v1.1/src/main/java/org/isaacmcfadyen/D1DatabaseMetaData.java#L906-L962

　
これより、同じような感じで外部キー情報を設定し、

new D1ResultSet(ApiKey, AccountId, DatabaseUuid, columnNames, rows, columnSchema)

な感じで D1ResultSet のインスタンスを生成すれば良さそうと分かりました。

　
また、設定する項目の値については、

• d1-jdbc-driver の既存の実装
• getPrimaryKeys メソッド (SQLServerDatabaseMetaData) - JDBC Driver for SQL Server | Microsoft Learnr の getExportedKeys メソッドによって返される結果セット

を見比べる限り一致していました。

そのため、各メソッドでMicrosoftのドキュメントの項目に返すことで、何とかなりそうな気がしました。

UPDATE_RULEやDELETE_RULEの数値について

各メソッドでは、外部キーのActionを UPDATE_RULE や DELETE_RULE として設定すれば良さそうと分かりました。

ただ、Microsoftのドキュメントによると、それらの値は数値であり、文字列ではありません。

Microsoftのドキュメントには数値も記載されていたものの、できれば数値のハードコーディングは避けたいです。

　
そこで調べてみたところ、Oracle Javaのドキュメントに記載がありました。

• DatabaseMetaData (Java Platform SE 8 )
• https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#importedKeyNoAction

　
これより、既存の作りと同様

JSONObject ruleType = new JSONObject();
ruleType.put("NO ACTION", DatabaseMetaData.importedKeyNoAction);

とすれば、文字列を数値に変換できてハードコーディングしなくて済みそうとわかりました。

SQLiteの外部キー情報を取得する方法について

続いて、SQLiteの外部キー情報の取得方法を調べることにしました。

主キーでは queryDatabase("PRAGMA table_info(" + table + ")") のような感じでデータを取得していたため、同じような方法があるのかなと思って調べたところ、stackoverflowの回答がありました。
foreign keys - Output of the SQLite's foreign_key_list pragma - Stack Overflow

SQLiteの PRAGMA の foreign_key_list() を使えば良さそうです。
https://www.sqlite.org/pragma.html#pragma_foreign_key_list

外部キー制約名の取得について

stackoverflowの回答 にあった foreign_key_list() で取得できる値には、外部キー制約名が含まれていませんでした。

どこで取得するんだろうと思って調べたところ、stackoverflowに回答がありました。
How to get the names of foreign key constraints in SQLite? - Stack Overflow

これより、「システムテーブルの SQL 列に CREATE TABLE したときのSQLが保存されているので、それをパース・取得する」くらいしか方法がないと分かりました。

　
ただ、

• やれなくはないが、毎回D1へアクセスしてパースするのは厳しい
• Drizzle ORMで外部キー制約を作った時は外部キー制約名は設定されていなかったので、取得しても無駄になりそう
  • https://github.com/thinkAmi-sandbox/react_hono_with_cloudflare_pages_d1-example/blob/main/migrations/0000_tidy_phantom_reporter.sql

ということから、外部キー制約名を表示する優先度は高くなさそうと考えました。

　
そこで、今回はひとまず <table_name>_<id>_<seq> という形で対応することにしました。

　
ちなみに、外部キー名を編集するのではなく null や空文字を設定すると、 #FAKE みたいなprefixがついてしまいます。さすがに見栄えが良くないので編集することにしました。

JDBC driver の実装をデバッグする方法について

ここまでの調査で実装はできそうでした。

ただ、

• 変数の中身の確認
• うまく動いていないときの動作確認

をしたくなったことから、 JDBC driver の実装をデバッグする方法を探してみました。

すると以下の記事がありました。
JDBCドライバの作り方 #Java - Qiita

　
そこで、「テスト用の実行処理」を参考に、 main メソッドを持つ以下のようなクラスを作ってIntelliJ IDEAでデバッグ実行できるようにしました。

package org.isaacmcfadyen;

import java.sql.SQLException;

public class MyClass {
    public static void main(String[] args) throws SQLException {
        try (D1Connection con = new D1Connection("token", "account_id","database_id")) {
            var dbmd = con.getMetaData();
            var rs = dbmd.getCrossReference(null, null, null, null, null, "orders");
        }
    }
}

　
なお、 token ・ database_id は、d1-jdbc-driver のREADMEに取得方法が記載されています。

一方、account_id は最初どこで見れるのか分かりませんでしたが、CloudflareのコンソールURLに含まれていると分かりました。

具体的には、 https://dash.cloudflare.com/<アカウントID> の アカウント の値になります。

IntelliJ IDEAでビルドして jar ファイルを生成する方法について

今回、最終的には WebStormにJDBC driverの jar を設定したいのですが、そもそも jar を生成する方法がわかりません。

そこで方法を調べたところ、以下の記事がありました。

• アーティファクト | IntelliJ IDEA ドキュメント
• IntelliJ IDEAでJavaコードをビルドしても、jarファイルが作成されない | 下町のフリーソフト開発室
• Intellij IDEAで実行可能なjarを作る方法 #Java - Qiita

　
上記の記事を参考に、各種設定を行った後、Build ArtifactからReBuildを実行したところ、無事に jar ファイルができました。

　
なお、ビルドとリビルドの違いについては、IntelliJ IDEAのドキュメントに記載がありました。今回は規模が大きくないので、常時再ビルド(ReBuild)します。

• ビルド : アーティファクト全体を構築します。その後、アーティファクトは段階的に構築されます: IDE は、最後のビルド以降に行われた変更のみをビルドします。

• 再ビルド : 初めてのようにアーティファクト全体を構築します。技術的には、これはクリーンの後にビルドが続きます。

• クリーン : アーティファクト出力ディレクトリのすべての内容を削除します。

https://pleiades.io/help/idea/working-with-artifacts.html#build-manually

　
ちなみに、WebStormで jar を追加のJDBC driverとして設定する方法は、 d1-jdbc-driver のREADMEに記載があります。
https://github.com/isaac-mcfadyen/d1-jdbc-driver

実装

以上で必要な調査が終わったので、あとは実装するだけです。

今回は

• getImportedKeys
• getExportedKeys
• getCrossReference

を実装します。

　
なお、今回は最低限動くところがゴールなので、 getImportedKeys と getExportedKeys は同じ実装にしています。

もし JDBC driver の実装に詳しい方がいれば、上記2つのメソッドをより良く実装する方法を教えていただけるとありがたいです。

public class D1DatabaseMetaData extends D1Queryable implements DatabaseMetaData {
    // ...
    @Override
    public ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException {
        return getCrossReference(null, null, null, catalog, schema, table);
    }

    @Override
    public ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException {
        return getCrossReference(null, null, null, catalog, schema, table);
    }

    @Override
    public ResultSet getCrossReference(String parentCatalog, String parentSchema, String parentTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException {
        ArrayList<String> columnNames = new ArrayList<>();
        columnNames.add("PKTABLE_CAT");
        columnNames.add("PKTABLE_SCHEM");
        columnNames.add("PKTABLE_NAME");
        columnNames.add("PKCOLUMN_NAME");
        columnNames.add("FKTABLE_CAT");
        columnNames.add("FKTABLE_SCHEM");
        columnNames.add("FKTABLE_NAME");
        columnNames.add("FKCOLUMN_NAME");
        columnNames.add("KEY_SEQ");
        columnNames.add("UPDATE_RULE");
        columnNames.add("DELETE_RULE");
        columnNames.add("FK_NAME");
        columnNames.add("PK_NAME");

        JSONObject stringType = new JSONObject();
        stringType.put("type", "TEXT");
        JSONObject intType = new JSONObject();
        intType.put("type", "INTEGER");

        JSONArray columnSchema = new JSONArray();
        // PKTABLE_CAT
        columnSchema.put(stringType);
        // PKTABLE_SCHEM
        columnSchema.put(stringType);
        // PKTABLE_NAME
        columnSchema.put(stringType);
        // PKCOLUMN_NAME
        columnSchema.put(stringType);
        // FKTABLE_CAT
        columnSchema.put(stringType);
        // FKTABLE_SCHEM
        columnSchema.put(stringType);
        // FKTABLE_NAME
        columnSchema.put(stringType);
        // FKCOLUMN_NAME
        columnSchema.put(stringType);
        // KEY_SEQ
        columnSchema.put(intType);
        // UPDATE_RULE
        columnSchema.put(intType);
        // DELETE_RULE
        columnSchema.put(intType);
        // FK_NAME
        columnSchema.put(stringType);
        // PK_NAME
        columnSchema.put(stringType);

        JSONObject ruleType = new JSONObject();
        ruleType.put("NO ACTION", DatabaseMetaData.importedKeyNoAction);
        ruleType.put("CASCADE", DatabaseMetaData.importedKeyCascade);
        ruleType.put("SET NULL", DatabaseMetaData.importedKeySetNull);
        ruleType.put("SET DEFAULT", DatabaseMetaData.importedKeySetDefault);
        ruleType.put("RESTRICT", DatabaseMetaData.importedKeyRestrict);

        JSONObject results = queryDatabase("PRAGMA foreign_key_list(" + foreignTable + ")");
        JSONArray fkList = results.getJSONArray("results");
        ArrayList<ArrayList<Object>> rows = new ArrayList<>();

        for (int i = 0; i < fkList.length(); i++) {
            JSONObject fkItem = fkList.getJSONObject(i);

            ArrayList<Object> row = new ArrayList<>();
            row.add(null);
            row.add(null);
            row.add(fkItem.get("table"));
            row.add(fkItem.get("to"));
            row.add(null);
            row.add(null);
            row.add(foreignTable);
            row.add(fkItem.get("from"));
            row.add(fkItem.get("seq"));
            row.add(ruleType.get(fkItem.get("on_update").toString()));
            row.add(ruleType.get(fkItem.get("on_delete").toString()));

            // If null is set, #FAKE_<table>_<number> is set, so <foreignTable>_<id>_<seq> set
            row.add(foreignTable + "_" + fkItem.get("id").toString() + "_" + fkItem.get("seq").toString());
            row.add(null);

            rows.add(row);
        }

        return new D1ResultSet(ApiKey, AccountId, DatabaseUuid, columnNames, rows, columnSchema);
    }
// ...

動作確認

今回は

• Wranglerでテスト用のD1を作る
• Wranglerで各種外部キーを持つテーブルを作る
• WebStormのDatabase toolsで表示する

という流れで動作確認をします。

Wranglerでテスト用のD1を作る

Wranglerのドキュメントに従い作成します。
https://developers.cloudflare.com/workers/wrangler/commands/#d1

$ npx wrangler login
$ npx wrangler d1 create d1-driver-test

Wranglerで各種外部キーを持つテーブルを作る

ON UPDATE と ON DELETE まわりをしっかり見たかったので、網羅するようなテーブル数と設定を行っています。

CREATE TABLE `shops` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `staffs` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `products` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `orders` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text,
  `product_id` integer,
  `shop_id` integer,
  `staff_id` integer,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON UPDATE cascade ON DELETE set null,
  FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON UPDATE set default ON DELETE restrict,  
  CONSTRAINT `fk__staff_who_ordered` FOREIGN KEY (`staff_id`) REFERENCES `staffs`(`id`) ON UPDATE no action ON DELETE no action
);

　
このSQLを d1.sql みたいなファイルに保存し、

$ npx wrangler d1 execute d1-driver-test --remote --file=./d1.sql

とすることで、Cloudflare上のD1にテーブルができました。

WebStormのDatabase toolsで表示する

想定通りの表示となりました。

作ったプルリク

完成したのでプルリクを作りました。
https://github.com/isaac-mcfadyen/d1-jdbc-driver/pull/5

すると、早速マージしていただけました。ありがたい限りです。

　
なお、プルリクを作るためにforkしたリポジトリはこちらです。
https://github.com/thinkAmi/d1-jdbc-driver

その他資料

JDBC driver をゼロから作るときの資料

中身は詳しく見てないのですが、いつか役立つかもしれないので、リンクだけ置いておきます。

• JDBC ドライバの基礎知識：仕組みと5つのタイプを徹底解説 | CData Software Blog
• Create your own type 3 JDBC driver, Part 1 | InfoWorld
• Writing a custom jdbc driver in java a very basic one - Stack Overflow
• JDBC over Thriftを作成する　その１（JDBCドライバの作り方） - Symfoware
• Class.forName で DB アクセスできるようになる理由 - すぱいだー日記。