Apache Derby Dbのアプリケーションへの組み込み方と、ユーザー定義型、ユーザー関数の使い方
Apache Derby 10.9.1.0のアプリケーションへの組込方法と、ユーザー定義型とユーザー定義関数の使用例について記述する。
動機
昨今、アプリケーションのバックエンドにSQLite3などの軽量・組み込み型データベースが使われる例が非常に多くなっている。
PCの性能、搭載メモリもリッチになり、少しデータ量が多くなるのであれば、独自のフォルダ構造をもつ大量のファイルを扱うよりも、RDBMSをバッキングストアとして使った方が、アプリケーションをシンプルに作れるケースも多いものと思われる。
また、既存のRDBMSを使うことで
- 広く知られているSQL的手法による開発、保守の容易性
- SQLというI/Fで共通化されるため、他アプリやツールがデータベースを再利用することが容易
- RDBMSがもっている基本的なメンテナンスツールが、そのまま利用可能
- データベースを使うならば、煩雑な処理になりがちな各種設定情報などの小さな情報もついでに全部データベースで管理できてしまう.
のような利点もある。
今回は、スタンドアロン型のJavaアプリケーションのバックエンドに組み込み用RDBMSとして、Apache Derbyを活用する方法について調べてみるものとする。
Apache Derbyについて
Apache FoundationのDB Projectの「Apache Derby」は、JDK6、JavaEE5 Development Kit以降に同梱されているJavaDBと呼ばれるデータベースエンジンである。
Apache Derbyの オリジナルは、1996年にCloudscape社が開発し、1997年にCloudscapeという名前でリリースされたPure Javaのデータベースエンジンである。
これは 1999年にInformix社に買収され、2001年に更にIBMによって買収されている。
2004年にIBMよりApache財団に寄贈され、Apache License2のもとにオープンソース化された。
2006年よりJDK6とともに「JavaDB」としてバンドルされ、今日まで続いている。
(ディストリビューション名を「JavaDB」としているだけで、中身は同じものである。)
JavaDBとApache Derbyのバージョンの関係
JDKに同梱されているJavaDBとApache Derbyのバージョンは以下となっている。
| java | apache derby |
|---|---|
| JDK6 | 10.2〜10.6〜 |
| JDK7 | 10.8〜 |
JDK6はリリース期間が非常に長いため、初期は10.2だったが、リリースにあわせてバージョンも少しづつあがっている。*1
JDK7は10.8スタートである。
実際、どのバージョンを使っているのか確認するには、付属ツール sysinfo を実行することで確認できる。
今回は最新版である、Apache Derby 10.9 をアプリケーションに組み込むことを想定しているが、細かな点はともかく、基本的にはDerby 10.6(後期JDK6のJavaDB)以降であれば、そのまま使えるものと思われる。
なお、ユーザー定義型(UDT)やインメモリデータベースなどの機能は、Apache Derby 10.5以降の機能である。
Apache Derbyの特徴
- Pure Javaである。
- 軽量である。
- 組み込みモードでは、Derby自身のサイズは2.7MB + α なので、アプリケーションに約3MBほどプラスすることになる。*2
- ANSI SQL92準拠の高機能性
- 一時表も利用可能.
- SQL標準のデータ型をサポート
- SQL:1999機能もサポート。
- ユーザー定義関数
- トリガーをサポート
- 部分的にはSQL:2003機能もサポート。
- Oracleでおなじみ「シーケンス」はDerbyでも利用可能.
- Window関数群 (row_numberなどが一部使用可能)
- Order By時のNull順序を指定可能
- 部分的にはSQL:2008機能もサポート。
- OFFSET, FETCH句などをサポートしている。(これでMySQLのLimit相当のことができる。)
- JavaDBとしてJDKに同梱される高いJDBC準拠
- データベースはフォルダ構造下のファイルとなる
- 読み取り専用のデータベースとしてzip, jarファイルの中に入れることができる.
- インメモリデータベースとしても利用可能*3
- アーカイブログモードがあり、バックアップの仕組みもある。
- ライセンスがApache License, Version2である
- アプリケーション組込用途として最適
- 欠点としては、やや古いアーキテクチャでありMVCC(Multi version Concurrency control)をサポートしていない。
を特徴とする。
また、RDBMSは大別して、
があるが、
Apache Derbyは、配備方法とJDBCドライバを使い分けることによって、この、どちらのタイプのデータベースとしても利用できる。
今回は「組み込みモード」に特化してアプリケーション組み込み用途としてのDerbyについて調べるものとする。
準備するもの
以下のものを準備する.
- JDK7
- jdk1.7.0 update4
- (Mac OS Xでのテストにはjdk1.7.0 u6 developer previewを使用)
- 今回開発する予定の自作アプリをJDK7で作成するため。
- JDK7でなければならないわけではなく、今回のソースも、try-with-resources構文以外はJDK6でも通用するはず.
- Apache Derby 10.9.1.0
derbyのアプリケーションへの組み込み方
ライブラリファイル
自作アプリケーションにDerbyを組み込むには、Apache Derbyの配布ファイルを展開した中の、以下の2つのjarをライブラリとしてコピーし、クラスパスを通すだけで良い。
- lib/derby.jar (Derby本体)
- lib/derbyLocale_ja_JP.jar (日本語リソース)
データベースファイル
自作アプリケーションが使用するデータベースファイルを、どこに配置するかは、アプリケーションの性質による。
Apache Derbyのデータベースは、指定された特定のディレクトリ下作成される複数のディレクトリとファイルから構成されるものである。
デフォルトでは、このディレクトリの中にロックファイルやアーカイブログファイルなどが作成され、また一時ファイルも多数作成されることになる。
Apache Derbyは、データベースを読み込み専用で利用することも可能である。
この場合は、自身のクラスパス上にデータベースファイルをそのまま置くか、zip, jarに圧縮して任意の場所に置くことができる。
ただし、一時表を使用しなければならない場合、たとえばクエリ結果の大規模なソートが必要だったりすると一時表が必要となるが、クラスパスによる指定やzip, jarによるデータベースファイルでは、デフォルトでは、一時ファイルを利用することができず、書き込みエラーが発生することがある。
その場合、一時ファイルだけは特定のフォルダに振替えるなどの事前処置が必要となる。
一時ディレクトリの指定方法はデータベースプロパティの指定によって行うことができる。
http://db.apache.org/derby/docs/10.9/ref/rrefproper34037.html
(指定された一時ディレクトリの下にデータベース名によるサブディレクトリが作成される。サブディレクトリ中に作成された一時ファイルはDerbyのデータベースのシャットダウン処理でクリアされるが、ディレクトリ自身は残される。また、複数のDerbyのインスタンスがある場合、それらが同じデータベース名をもっているとディレクトリ名が衝突してしまうため、データベースには一意の固有名をつけることが推奨される。)
読み書き可能な通常のデータベースとして使う場合には、一般的には、ユーザーごとのアプリケーションデータの保管場所に配置するのが妥当と考えられる。
(データベースはフォルダ構造になっており、ファイルコピー等で扱うようなドキュメントではなく、また、一般ユーザーが見ても扱い方が不明なものであるから、マイドキュメントや書類フォルダにおくには違和感がある。)
これはDerbyに限った話ではないが、ユーザーごとのアプリケーションデータの保管場所としては、
- Windowsであれば環境変数%APP_DATA%が示す場所
- Windows 2000/XPであれば、たとえば、C:\Documents and Settings\ユーザー名\Application Data\アプリ名
- Windows Vista/7であれば、たとえば、C:\Users\ユーザー名\AppData\Roming\アプリ名
- Macであれば ~/Library の下
- Linuxであれば ~/ (ホームディレクトリ)の下
- データベースファイルのような一般ユーザが見ても意味不明なものは、ドットで始まるフォルダにしておくほうが良いかもしれない.
というのが一般的ではないか、と思われる。
このデータベースのディレクトリ位置を求めるJavaコード例を以下に示す。
/** * アプリケーション名とデータベース名から、データベースのパスを取得する. * @param appName アプリケーション名 * @param dbName データベース名 * @return データベースのパス */ public static String getDbDirectory(String appName, String dbName) { File appBaseDir; String appDataDir = System.getenv("APP_DATA"); if (appDataDir != null) { // 環境変数APP_DATAが設定されている場合 (Windowsはデフォルトで設定済み) appBaseDir = new File(appDataDir); } else { File userDir = new File(System.getProperty("user.home")); String osName = System.getProperty("os.name").toLowerCase(); if (osName.contains("os x")) { // Mac OS Xの場合 appBaseDir = new File(userDir, "Library"); } else { appBaseDir = userDir; } } // アプリケーション用ディレクトリ // (なければ作成する.) File appDir = new File(appBaseDir, appName); appDir.mkdirs(); // データベース用ディレクトリ // (Apache Derbyによってディレクトリの生成は任せる。ここでは作成しない.) File dbDir = new File(appDir, dbName); return dbDir.getAbsolutePath(); }
データベースの作成またはコピー
Apache Derbyは、JDBCコネクションによる初回接続時によって、コネクション文字列で指定されたデータベースを起動しようとする。
そのとき、データベースが存在しない場合は接続エラーとなる。
ただし、以下のいずれかのオプションが指定されている場合
- create=true → 新規作成
- createFrom=コピーもとのデータベースのパス → 既存DBを元にした新規作成
- restoreFrom=コピーもとのデータベースのパス → 既存DBで初期化する.
は、データベースの作成、再作成を行う指示となる。
[空のデータベース作成方法]
はじめてデータベースを作成する場合には、接続文字列のオプションとして「create=true」を指定することで、指定されたデータベースディレクトリの位置に(まだ、データベースファイルが存在しなければ)Apache Derbyの空のデータベースファイルを作成することができる。
この場合、アプリケーションは初回接続によるデータベース作成時には、テーブルの作成や初期マスタデータなどを設定する処理が必要になる。
- すでに作成済みのデータベースに対して 「create=true」を指定すると警告が発生するがエラーにはならず接続はできる。(警告は Connection#getWarnings() で取得できる。)
- なお、derbyに付属するdblookユーテリティにより既存DBにあるテーブル定義等のDDLを一括して採取することができる。
- また、標準ストアドプロージャでファイルによるインポート・エクスポートは可能である。
[データベースファイルのコピーによる作成]
あるいは、「createFrom=既存DBへのパス」の指定により、事前定義されたデータベースへのパスを指定することで、それをコピーして作成することができる。
この場合、事前にひな形となるデータベースファイルを対話ツール「ij」などを用いて新規作成し、あらかじめテーブルや初期データなどを設定したものを準備しておき、実際の実行時には、それをコピーする、という方法を取ることができる。
- ただし、「createFrom=」「restoreFrom=」ともに、コピーもとのデータベースは実フォルダでなければならない。(クラスパスやzip, jarによる読み取り専用データベースが使えない。)
- また、「create=true」の場合と異なり、すでにデータベースが作成済みの場合に「createFrom=」を指定するとエラーとなる。
- 「restoreFrom=」の場合は、データベースが作成済みであっても無くてもどちらでも良く、いつでも、そのデータベースが新しく作成/再作成される。
※ その他、バックアップされたデータベースとアーカイブログをもとに、指定されたデータベースをリカバリする、という機能も、同じような指定方法となっている。
データベースのアップグレード
derby.jarのバージョンを上げて新しくした場合でも、古いバージョンで作成したデータベースには引き続きアクセスできる。(逆はできない。)
このように、とくに何も指定せす使用する場合は、データベースの構造を変換しない「ソフトアップグレード」での利用となる。
恒久的にデータベースをフル・アップグレードする場合には、接続時に「upgrade=true」という属性をつけることで自動的に変換が行われる。
ds.setConnectionAttributes("upgrade=true");
もし、古いデータベースとして残す必要がなければ、フル・アップグレードしたほうが良いかもしれない。*4
Derbyのブートとシャットダウン方法
ブート処理
一般的にはJDBCはデータベースサーバにクライアントとして接続するためのAPIであり、データベースが起動して使用可能な状態にするのはデータベースサーバー側の問題である。
しかし、Apache Derbyを組込みモードで使用する場合、データベースサーバに相当するものも自分自身であり、自分のプロセス内でデータベースの起動処理を行う必要がある。
この起動処理は、Apache Derbyでは、単に初回JDBCによるコネクションを取得する、という行為だけで良い。
DriverManager.getConnection("jdbc:derby:memory:myDb;create=true").close();
これにより、データベースはオープンされた状態となり、以下のような処理が行われる。
シャットダウン処理
前述のとおり、組込みモードではアプリケーション自身がデータベースを管理するため、アプリケーションが終了するまえにデータベースを終了させる必要がある。
終了をDerbyに通知するためにはjdbcコネクションのオプションとして「shutdown=true」を指示して、コネクションを取得することで行う。
シャットダウンが成功した場合は、コネクションはとれないためSQLExceptionが発生する。(この場合、SQLExceptionが発生するのが正しい。)
DriverManager.getConnection("jdbc:derby:;shutdown=true");
なお、データベース名の指定の有無で挙動が異なる.
- データベース名の指定がない場合、すべてのデータベースを閉じて、データベースエンジンのシャットダウンを行うことを意味する。
- データベース名を指定する場合は、そのデータベースだけが閉じられる。データベースシステムはシャットダウンされない。もし、そのデータベースが、まだ開かれていない場合はSQLExceptionとなる。(つまり、成功しても失敗しても、どちらもSQLExceptionが発生する。)
データベースがシャットダウンされると、使用されていたロックが解除され、一時ファイル等も削除される。
http://db.apache.org/derby/docs/10.9/devguide/tdevdvlp20349.html
インメモリデータベースであればシャッドダウンすればデータは、すべて消滅する。
(必要であれば、ファイルにエクスポートしたり、バックアップをとるなどを自分で行う必要がある。)
なお、シャットダウンせずに終了した場合、次回にデータベースを開くときにログファイルよりデータベースのリカバリが行われる。
ログファイルが残っている量によっては、このリカバリ処理には、かなり長い時間がかかることになるため、必ずシャットダウンすることが望ましい。
※ シャットダウン処理はすぐに完了する。おそらく、メモリ上で認識しているデータベースの状態をファイルにフラッシュして確定させ、ログファイルに、"ここまでは確定済み"と示しているだけと思われる。
※ まとまったデータの書き込みが終わるなどのタイミングで随時SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASEを呼び出しておくと、その時点までの状態はファイルに確定されるので、万が一、シャットダウンできずに終了した場合でも次回起動時のリカバリ処理は最小限になるようである。
データベースのブートとシャットダウン処理の実装
public static void main(String[] args) throws Exception { // アプリケーションの終了時に確実にDerbyをシャットダウンさせるように // シャットダウンフックを設定する. Runtime rt = Runtime.getRuntime(); rt.addShutdownHook(new Thread() { @Override public void run() { // データベースシステムを安全にシャットダウンする. try { DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch (Exception ex) { // apache-derbyがシャットダウンに成功すると、 // コネクションは得られず、必ずSQL例外がかえって来る. System.out.println(ex); } } }); // 組み込みモード用のデータソースを作成 EmbeddedDataSource ds = new EmbeddedDataSource40(); // データベースのパスを取得 String dbName = getDbDirectory("myApp", "db.derby"); // データベースのパスを設定 ds.setDatabaseName(dbName); // データベースフォルダが実在しなければ // create=true を意味するオプションの指定をする。 // 指定する場合は"create"固定 (不要であればnull) if (!(new File(dbName).exists())) { ds.setCreateDatabase("create"); } // 接続テスト try (Connection conn = ds.getConnection()) { // これまでに警告があれば、その警告を表示する. // (データベースが作成済みでcreate=trueを指定した場合などに警告がでる) System.out.println("warn=" + conn.getWarnings()); } // もしデータソースを引き続き利用する場合は、 // もう "create=true"の機能は不要であるので、nullにして解除しておく。 ds.setCreateDatabase(null); …. }
実行結果
run: warn=null java.sql.SQLException: Derby システムがシャットダウンされました。 構築成功 (合計時間: 0 秒)
Derby特有の接続文字列と、コネクションの取得方法
Derbyの組み込み型のJDBCのURLは、以下のようになる。
ディレクトリ指定(デフォルト)の場合
ディレクトリを指定し、そこにファイルとしてデータベースを置く場合は、以下のようにパスを指定する。
jdbc:derby:<データベースディレクトリのパス>;オプション
組み込みモードであるため、データベースへのパスは、データベースファイル(実体はフォルダ)までのパスを指定することになる。
Windowsの場合はフォルダ区切りは「/」に変換しておく。
指定するパスが絶対パスでない場合は、カレントディレクトリ相対となる。
インメモリデータベースの場合
組み込みモードにおけるインメモリデータベースは、単に識別するための名前だけつける。
名前で識別されるが、識別可能なのは同一JavaVM上の同じDerbyインスタンスのみである。
jdbc:derby:memory:<データベース識別名>;オプション
EmbeddedDataSource#setDatabaseName()で使用する場合には、「memory:<識別名>」のように指定する。
http://db.apache.org/derby/docs/10.9/devguide/cdevdvlpinmemdb.html#cdevdvlpinmemdb
圧縮された読み込み専用データベースなどの場合
データベースがjarやzipなどに圧縮されている場合、あるいはクラスパス上にある読み込み専用のデータベースの場合には、少し違った記述方法になる。
jdbc:derby:jar:(/Users/seraphy/db.jar)db.derby
※ db.jarの中に「db.derby」というフォルダがあり、その中にデータベースファイル一式が格納されている場合
http://db.apache.org/derby/docs/10.9/devguide/cdevdeploy11201.html
http://db.apache.org/derby/docs/10.9/devguide/rdevdvlp22102.html
DriverManagerと、どのDataSourceのどちらを使うか?
スタンドアロンなアプリケーションであれば、DriverManagerを使って特に問題はない。
DataSourceを使うことによるメリットは、
- データソースを作成しデータベース名などの各種設定を行う部分と、それを利用する側とを簡単に分離できる
- EmbeddedDataSourceには
- setCreateDatabaseプロパティ、この値を「create」とすると、create=trueと指定したのと同じになる。
- setShutdownDatabase プロパティ、この値を「shutdown」とすると、shutdown=trueと指定したのと同じになる。
- その他、Derbyの接続文字列につけるオプションに関するアクセッサメソッドがいろいろある。
といった、ちょっと便利な機能がついてくるところである。
また、組込みモードでもDerbyには、データソースとして
- EmbeddedDataSource40
- EmbeddedConnectionPoolDataSource40
- (後ろの数字はJDBCのバージョンを示す、数字がない版はJDBC3.0版である。)
が存在する。
EmbeddedConnectionPoolDataSource40は、その名のとおり、コネクションプールを実装するデータソースのようであるし、JavaDocにも、そのようにかかれている。
が、コネクション取得・解放を繰り返して時間をはかったところでは、埋め込み型のスタンドアロンなアプリケーションでは、こちらを使っても特に目立った効果はなさそうである。
おそらく一度DBがロードされたら、以後はコネクションを作成するという動作はオンメモリで完結するためではないか、と想像される。
なので、今回は、EmbeddedDataSource40を使うこととした。
テーブルの作成とチューニング項目
文字列の扱い (文字照合順序、大文字・小文字の比較方法など)
Apache Derbyは、SQL92標準に準拠しているので、SQL標準のデータ型は、ほとんど使用できる。
ただし、SQL92でオプション扱いである、NCHAR, NVARCHARは定義されていない。
国際化が考慮されていないというわけでなく、そもそもCHAR, VARCHARでUnicode文字列扱いとなっているので、日本語などの扱いに基本的には問題はない。
ただし、国際化された文字列の比較などを、どのように扱うか、といった設定は存在する。
- デフォルトは、Unicodeのコードポイントによる比較 (つまり、ユニコードの数値での比較となる.)
- 接続文字列のオプションで以下を指定することが可能
- territory ロケールの指定 (省略時はLocale.getDefault() と同じ)
- collation 照合順序 並べ替え規則、大文字・小文字の区別などの指定
http://db.apache.org/derby/docs/10.9/devguide/cdevcollation.html
ページサイズのチューニング
Apache Derbyのアーキテクチャでは、テーブル作成時とクエリ時には、以下の4つのパラメータが性能にかかわってくると考えられる。
- derby.storage.pageSize 物理的なテーブルのページサイズ
- ページサイズはDerbyがテーブルや索引を読み書きする単位となる.
- デフォルトは4096
- 64MB以上空きメモリのあるWindowsNT系なら、8192が推奨される。
- ページサイズは、 4096、8192、16384、または 32768 の中から 1 つだけ指定できる。
- derby.storage.pageReservedSpace 挿入時、ページ確保時に空きを残す比率
- 挿入処理でページを作成したとき、将来の挿入・更新に備えて、どのくらい空きを残すかを示す.
- デフォルトで20が設定される.
- マスタ系で更新が発生しないことが分かっている場合は比率を下げることが可能.
- derby.storage.minimumRecordSize 最小レコード長
- 更新系のテーブルでVARCHAR, VARCHAR FOR BIT DATA型の可変サイズの列がある場合、事前に最小サイズを広めに取っておくことでレコード更新時にページ溢れを起こす可能性を下げることができる。
- 多く取りすぎると、無駄になる。
- デフォルト、および、最小値は12バイト
- 最大値は、derby.storage.pageSize * (1 - derby.storage.pageReservedSpace/100) - 100.
- derby.storage.pageCacheSize メモリ上にキャッシュするページの個数
- メモリ上にキャッシュしておくページの個数を示す
- デフォルトは1000ページ (デフォルト4096ページサイズなら、x1000 で約4MBのメモリが必要)
- 最小は40ページ
- 最近のデスクトップアプリであればメモリを贅沢に使えるので、もう少し大きくても良いかもしれない。(ヒープがあるかぎり指定可能.)
ページサイズ、ページのリザーブサイズ、行の最小サイズといったパラメータは、CREATE TABLEやCREATE INDEX文でテーブルや索引を作成するときに使われる。
テーブルや索引を作成後に変更しても、作成済みのものに影響を与えない。
この二つのパラメータは、システムプロパティでもデータベースプロパティとしても設定できるが、その性質からすれば、データベースプロパティとして以下のようにプロシージャを実行してデータベース固有の設定とするのが妥当であろう。
-- changing the default for the database CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.storage.pageSize', '8192'); -- modifying the default for the database CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( ’derby.storage.pageReservedSpace’, ’20’); -- changing the default for the database CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.storage.minimumRecordSize', '128');
ページキャッシュサイズは、システムプロパティとして設定可能である。
(変更後はDerbyの再起動が必要。データベースのプロパティとしては設定できない。)
System.setProperty(
"derby.storage.pageCacheSize",
"2000"
);
なお、pageSizeやminimumRecordSize等で表や索引の更新効率を改善しようとしても、
索引の更新が頻繁・長期にわたると、索引ページが断片化され、パフォーマンスが劣化してしまう。
チューニングガイドによると、この場合の、索引を再作成するとパフォーマンスが著しく改善される、とのことである。
(索引を再作成する、という機能はないので、索引をドロップしてから、あらためて作成する。)*5
※ あるいは、SYSCS_UTIL.SYSCS_COMPRESS_TABLEプロシージャの説明によると、テーブルとインデックスの圧縮にともない、インデックスも再構築が行われるようである。(一旦、現在アクティブな行データだけを抜き出してコピーする、という動きを行うため) (2014/2/19追記)
テーブルのページ使用率の診断と、テーブルサイズの圧縮
以下のSQLによって、テーブルの使用ページ数、空きページ数の確認と圧縮予測を確認することができる。
select SUBSTR(CONGLOMERATENAME, 1, 20) -- 表またはインデックス名 , ISINDEX -- インデックスか? 0以外ならインデックス , NUMALLOCATEDPAGES -- データが割り当てられたページ数 , NUMFREEPAGES -- テーブルに関連づけられている未使用ページ数 , (NUMALLOCATEDPAGES + NUMFREEPAGES) as TOTALPAGES -- 全ページ数 , NUMUNFILLEDPAGES -- 使用中だが、まだ空きのあるページの数 , PAGESIZE -- ページサイズ , ESTIMSPACESAVING -- 圧縮した場合に節約できると予想されるバイト数 from sys.systables st , table(syscs_diag.space_table(st.tablename)) as t where st.tabletype = 'T';
http://db.apache.org/derby/javadoc/engine/org/apache/derby/diag/SpaceTable.html
※ これらパフォーマンスチューニングに役立つ診断用テーブルや関数が、いろいろある。
SYSCS_DIAG diagnostic tables and functions
http://db.apache.org/derby/docs/10.9/ref/rrefsyscsdiagtables.html
もし、未使用のページが多いようであれば、SYSCS_UTIL.SYSCS_COMPRESS_TABLEプロシージャを実行することで、任意のテーブルを圧縮することができる。(インデックスも再構築される。)
Apache DerbyはDeleteして未使用になった行もファイルサイズを縮退させずに再利用するため、大量のDeleteを実行したあと、もしくは定期的に、このプロシージャを実行すると良いのかもしれない。
なお、少し古いが気になる情報として、マルチスレッドでINSERTを繰り返すと*6 かなりスカスカな状態のページのまま、沢山のページを作ってしまうようである。
http://old.nabble.com/excessive-disk-space-allocation-td20080198.html
http://thread.gmane.org/gmane.comp.apache.db.derby.devel/36430
バグ・トラッキング(?)では、この問題はOPENのままなので現在も変わらず、かもしれない。
https://issues.apache.org/jira/browse/DERBY-2338
https://issues.apache.org/jira/browse/DERBY-2337
複数スレッドからの同時書き込みでなければ、問題は発生しないようであるが、使い方によってはディスクスペースを浪費しやすい癖がある、ということを覚えておいたほうがよいかもしれない。
行ロックの無効化 (パフォーマンスのチューニング)
Apache Derbyは、複数ユーザーからの並列実行を想定しており、デフォルトで行ロックが有効で、且つ、READ_COMMITTEDなトランザクションモードで動作する。
行ロックが有効な場合、行ロックされているロック数が閾値を超えると、テーブルロックに昇格する。
行ロックを無効とすると、常にテーブルロックとなる。
チューニングガイドによると、シングルユーザーでの利用で、マルチスレッドでデータベースを更新することがない場合(もしくは読み込み専用データベースである場合)は、そもそも競合が発生することはないため、メモリ負荷の大きい行ロックを無効とし、トランザクション分離レベルはREAD COMMITTEDにする良いのこと。
http://db.apache.org/derby/docs/10.9/tuning/
-- database-level property CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.storage.rowLocking', 'false');
あるいは、システムプロパティでの設定も可。
(データベースを、どのように利用するかはアプリケーションの実装次第なので、データベースよりもJava側に書いたほうが本筋な気もする。)
System.setProperty(
"derby.storage.rowLocking",
"false"
);
ユーザー定義型・ユーザー定義関数の作成
Apache Derbyはユーザー定義型、ユーザー定義関数をJavaで記述できる、というメリットがある。
ユーザー定義型
ユーザー定義型は単にSerializableインターフェイスを実装したクラスであれば良い。
しかし、公式ドキュメント例によると、シリアライズするデータを完全に制御できるという意味でExternalizableを実装することが推奨されるようである。
なお、Externalizableを使う場合であってもシリアルバージョンUIDの設定はしておくべきである。
(さもないと、Externalizableで管理しているデータの中身が変わらなくても、このクラス自身のメソッドやフィールドが変わるなどにより互換性がなくなる可能性がある。)
アプリケーション内のエンティティとして使われているオブジェクトなどは、シリアライズ可能にしてユーザー定義型としてDerbyに登録すると、オブジェクトそのままの形でデータベースに格納・取得できるようになる。
ただし、ユーザー定義型は、自身を含めて、どのタイプとも比較できない。
http://db.apache.org/derby/docs/10.9/ref/rrefsqlj58560.html
比較できないということは、where句での比較、order byやgroup by, joinにつかえないだけでなく、
行を圧縮するための判定もできないのでdistinct, union, intersect, exceptも使えず、
当然、比較できなければ索引もつくれないのでindexのカラムとしても使えない。
単純にデータを格納できる、というだけのことである。
※ 使おうとすると、以下のようなエラーに遭遇するであろう。
エラー X0X67: Columns of type '"APP"."HOSTADDR"' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
もし、検索したり、あるいは、それをキーとしてオブジェクトを連結させるために使うのであれば、やはり既存のデータ型に分解して登録する必要がある。
ユーザー定義関数/プロシージャ
ユーザー定義関数/プロシージャには
のような用途がある。
ユーザー定義関数そのものの記述方法は簡単で、単なるpublic staticなクラスメソッドであれば良い。
Java型とSQL型とのマッチングなどは、以下に記載がある。
http://db.apache.org/derby/docs/10.9/ref/rrefsqljargmatching.html
ユーザー定義関数を使うことで、SQL文中でユーザー定義型をサポートしたり、SQL単体では難しい処理と連携することでSQLを、より強力なものにすることができる。
また、ResultSet派生クラスを返すユーザー定義関数を定義することで、それをSELECT文のFROM句に使うことができるようになる。
つまり、データベース以外のデータソース、たとえばアプリケーション内で保持しているArrayListのようなオブジェクトや、あるいはCSVファイルのようなものをテーブルと見立ててSQLを実行することができる。
もちろん、それら同士や、既存のテーブルとのJOINも可能である。(とはいえ、効率には注意しなければならない。)
ユーザ定義型とユーザ定義関数の実装例
IPアドレスとポートのペアを保持すると想定する実験用ユーザー定義型と、その生成関数を実装してみる。
package apachederbysample.types; import java.io.Externalizable; import java.io.IOException; import java.io.ObjectInput; import java.io.ObjectOutput; /** * ホストアドレスを保持するユーザー定義型. * Externalizableを実装することでデータベースにデータを格納できるようにする. * * 以下のDDLでユーザー定義型を宣言する. * create type hostaddr * external name 'apachederbysample.types.HostAddr' * language java; * * @author seraphy */ public class HostAddr implements Externalizable { // ExternalizableはSerializeの派生なので、シリアルバージョンが必要. private static final long serialVersionUID = 8911826349422765018L; private static final int VERSION = 1; private String address; private int port; /** * デフォルトコンストラクタ */ public HostAddr() { this(null, 0); } /** * 引数付きコンストラクタ * @param address * @param port */ public HostAddr(String address, int port) { this.address = address; this.port = port; } public int getPort() { return port; } public String getAddress() { return address; } @Override public void readExternal(ObjectInput in) throws IOException, ClassNotFoundException { if (in.readInt() < VERSION) { throw new IOException("サポートされていないバージョンです"); } address = in.readUTF(); port = in.readInt(); } @Override public void writeExternal(ObjectOutput out) throws IOException { out.writeInt(VERSION); out.writeUTF(address); out.writeInt(port); } /** * このユーザー定義型の文字列表現. * これを実装しておくと、ijなどのselect文を実行したときに中身を表示できる. * @return このオブジェクトの文字列表現 */ @Override public String toString() { StringBuilder buf = new StringBuilder(); buf.append(address); buf.append(":"); buf.append(port); return buf.toString(); } /** * 引数からHostAddr型を生成して返すユーザー定義関数. * * ユーザー定義関数はpublic staticな関数であれば良い. * 以下のDDLでユーザー定義関数を宣言する. * * create function makeHostAddr(address varchar(256), port integer) returns hostaddr * language java * deterministic -- 引数が同じなら常に同じ値を返すことを示す.(省略時はnot deterministic) * parameter style java -- 戻り値のタイプ * no sql -- 関数の中でSQLは実行しないことを示す * external name 'apachederbysample.types.HostAddr.makeHostAddr'; * * @param address * @param port * @return HostAddr型 */ public static HostAddr makeHostAddr(String address, int port) { return new HostAddr(address, port); } }
オブジェクト一般にいえることだが、toStringメソッドをオーバーライドしておくと、ijなどのツールでクエリを実行したとき、ユーザー定義型のカラムの内容が分かりやすくなる。
また、staticメソッドによるファクトリメソッドを用意している。
これを、ユーザー定義関数として登録することにより、SQL文の中から「HostAddr型」を構築し、SQL中で利用可能にすることができるようにしている。
ユーザー定義型、ユーザー定義関数のクラスパス
組み込みモードで実行する場合、アプリケーションとderby.jarは同じクラスパス上にあるため、ユーザー定義型、ユーザー定義関数を使う上でクラスパスの設定をあらためて行う必要はない。
ただし、ijやSquirrel SQL Clientなどの外部ツールでデータベースを編集する場合には、これらのユーザー定義型、ユーザー定義関数のクラスパスを設定する必要がある。
■ ijの起動時のクラスパスにアプリケーションも入れる方法
java -cp ../lib/derby.jar:../lib/derbynet.jar:../lib/derbytools.jar:../lib/derbyclient.jar:/Users/seraphy/NetBeansProjects/ApacheDerbySample/build/classes org.apache.derby.tools.ij
このようにしてijを起動すれば、ユーザー定義型、ユーザー定義関数はijからでも利用可能となる。
■ Squirrel SQL Clientの場合
ドライバクラスの指定で、アプリケーションのjarもしくはclassesフォルダも指定すれば良い。
※ Squirrel SQL Clientの使い方については下記にメモしている。
http://d.hatena.ne.jp/seraphy/20120721
■ jar ファイルを登録して使用する場合 (汎用的な手順)
アプリケーションとDerbyデータベースが別のJavaVM上で動いている場合などでは、アプリケーションが参照するユーザー定義型とDerbyが参照するユーザー定義型の、それぞれにクラスパスを通しておくことで、アプリケーションとDerby間でユーザー定義型を交換することが可能になる。
通常の方法でijを立ち上げる場合には、当然、上記のような関係となるため、アプリケーション内のユーザー定義型をDerbyに認識させる手順が必要となる。
derbyには、そのために起動後に追加でjarファイルをインポートし、それに動的にクラスパスを設定する機能がある。
この機能を使うためには、当該クラスを一旦、jar化しておく必要がある。
Jar化したら以下のようにクラスをロードしクラスパスを通す。
seraphy$ ij
ij バージョン 10.9
ij> connect 'jdbc:derby:/Users/seraphy/db.derby';
ij> call sqlj.install_jar(
'/Users/seraphy/NetBeansProjects/ApacheDerbySample/dist/ApacheDerbySample.jar',
'APP.apachederbysample', 0);
> > 0 行が挿入/更新/削除されました
ij> call syscs_util.syscs_set_database_property(
'derby.database.classpath',
'APP.apachederbysample');
> > 0 行が挿入/更新/削除されましたクラスパスの指定方法等についてのマニュアルは以下。
sqlj.install_jarの第一引数がjarへのパス、第二引数が「スキーマ名.識別子名」
で、第三引数は未使用で0を指定する。
第二引数のスキーマは組み込みモードの場合は暗黙で「APP」となる。
識別子名は自由につけて良く、これは後続の「derby.database.classpath」の指定時に使われる。
derby.database.classpathは動的変更が可能なDerbyのプロパティの一つであり、ここで追加のクラスパスを指定する。
ここでは先に指定したjarに対する識別子を設定する。(複数ある場合は、識別子をコロンで区切る)
データベースにデータベースワイドのプロパティを設定する方法は以下を参照。
このように設定した場合、jarファイルと、クラスパスの設定はデータベース内に取り込まれて永続化される。
よって、次回起動時には単純にijを起動するだけでユーザー定義型、ユーザー定義関数が利用可能になる。
開発時など、この挙動が不味い場合もあるだろうから、注意が必要である。
もし、jarファイルを変更した場合には以下のように再度、jarファイルを取り込む必要がある。
call sqlj.replace_jar(
'/Users/seraphy/NetBeansProjects/ApacheDerbySample/dist/ApacheDerbySample.jar',
'APP.apachederbysample');
ユーザー定義型・ユーザー定義関数の登録
ユーザー定義型、ユーザー定義関数のクラスパスが通った状態であれば、以下のようにDDLを発行することで登録することができる。
(厳密には、宣言するだけならばクラスパスが通ってなくてもできるが、何かしようとするとClassNotFoundExceptionになる。)
-- ユーザー定義型の宣言 create type hostaddr external name 'apachederbysample.types.HostAddr' language java; -- ユーザー定義関数の宣言 create function makeHostAddr(address varchar(256), port integer) returns hostaddr language java deterministic -- 引数が同じなら常に同じ値を返すことを示す.(省略時はnot deterministic) parameter style java -- 戻り値のタイプ no sql -- 関数の中でSQLは実行しないことを示す external name 'apachederbysample.types.HostAddr.makeHostAddr';
DDLの文法は以下。
http://db.apache.org/derby/docs/10.9/ref/rrefcreatefunctionstatement.html
http://db.apache.org/derby/docs/10.9/ref/rrefsqljcreatetype.html
※ 10.9のマニュアルによると、ユーザー定義関数の引数としてCLOB, BLOBなどは使えず、宣言しようとするとエラーになると記載があるが、10.9で試したところ、エラーにはならず、実際に実装してみたところも、ちゃんと機能するようになっている。軽く使ったかぎりでは、とくに問題らしきものは見当たらなかったのだが…。BLOB, CLOBを引数にうけて何らかの結果を返すような関数があると便利と思うので、これが正式にサポートされることを期待したい。
ユーザー定義型とユーザー定義関数を使ってみる。
テーブルを作成し、insertし、selectしてみる。
このとき、テーブルには先ほど作成したHOSTADDR型のカラムを設定し、
INSERT文の中では、先ほど作成したmakeHostAddr関数を使って文字列と数値のリテラルからHOSTADDR型を生成してみる。
ij> create table testtbl3 (
idx integer primary key generated always as identity,
host hostaddr not null,
comment varchar(64)
);
> > > > 0 行が挿入/更新/削除されました
ij> autocommit off;
insert into testtbl3 (host, comment) values (makeHostAddr('192.168.1.2', 80), 'aaa');
insert into testtbl3 (host, comment) values (makeHostAddr('192.168.1.2', 81), 'bbb');
insert into testtbl3 (host, comment) values (makeHostAddr('192.168.1.3', 80), 'ccc');
insert into testtbl3 (host, comment) values (makeHostAddr('192.168.1.3', 81), 'ddd');
commit;ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> select * from testtbl3;
ij> IDX |HOST |COMMENT
----------------------------------
1 |192.168.1.2:80 |aaa
2 |192.168.1.2:81 |bbb
3 |192.168.1.3:80 |ccc
4 |192.168.1.3:81 |ddd
4 行が選択されました
ij> exit;select文でhostaddr型を表示するとき、HostAddr#toString() による文字列化が行われているのが確認できる。
ユーザー定義型をJavaからも使ってみる
ユーザー定義型は、Derby内で、すでにクラスが判明しているため、
プログラム的に型のマッピングを指定する必要もなく、普通にアクセスできる。
組み込みモードである場合はクラスパスが同じであることを想定できるため、考慮すべきことは、ほとんどない。
public static void testUDT(DataSource ds) throws SQLException { // JDBCのコネクションをオープンし、クエリを実行する try (Connection conn = ds.getConnection()) { HostAddr hostAddr = new HostAddr("localhost", 80); // fetch-all String sql = "select idx, host, comment from testtbl3 order by idx"; try (PreparedStatement stm = conn.prepareStatement(sql); ResultSet rs = stm.executeQuery()) { while (rs.next()) { int idx = rs.getInt(1); // ユーザー定義型はDerby側で対応するJavaクラスが分かっており、 // 明示的に型をマップする必要はない。単にCastするだけでOK. hostAddr = (HostAddr) rs.getObject(2); String comment = rs.getString(3); System.out.println(idx + "/" + hostAddr + "/" + comment); } } // insert HostAddr insHostAddr = new HostAddr( hostAddr.getAddress(), hostAddr.getPort() + 1); String sqlIns = "insert into testtbl3 (host, comment) values (?, ?)"; try (PreparedStatement stm = conn.prepareCall(sqlIns)) { stm.clearParameters(); // ユーザー定義型は、普通にsetObjectで渡すことができる. stm.setObject(1, insHostAddr); stm.setString(2, new Timestamp(System.currentTimeMillis()).toString()); int ret = stm.executeUpdate(); System.out.println("ret=" + ret); } } }
結果例
run: warn=java.sql.SQLWarning: データベース '/Users/seraphy/db.derby' は作成されませんでした。代わりに既存のデータベースに接続されました。 1/192.168.1.2:80/aaa 2/192.168.1.2:81/bbb 3/192.168.1.3:80/ccc 4/192.168.1.3:81/ddd 5/192.168.1.3:82/2012-07-23 03:04:04.033 ret=1 java.sql.SQLNonTransientConnectionException: Database '/Users/seraphy/db.derby' shutdown. 構築成功 (合計時間: 0 秒)
表関数の使い方
表関数は、ResultSet派生クラスを返すユーザー定義関数の一種である。
ResultSetで返される結果セットを擬似的な表とみたてて、SQL文中でテーブルのごとく使えるようになるという、超便利そうな機能である。
応用例としては
- CSVファイルのようなテキストファイルを読み込んで、これをテーブルとみたてて検索・集計する。
- OracleやMySQLのような外部データベースとの結果とDerby内のテーブルとを結合する。
- アプリケーション内で保持しているコレクションをテーブルとみたててテーブルとJOINして補完する。
といったことが考えられる。
実装例
以下に、単純に連番を返すだけの1カラムしかない仮想テーブルを返す表関数を実装する。
レコード数(連番の最大数)は、表関数の引数で指定する。
/** * 連続する数値を返す仮想テーブル. * @author seraphy */ public class RangeTable extends VTITemplate implements RestrictedVTI { /** * 現在インデックス */ private int cur; /** * 終了インデックス */ private int end; /** * 現在値 */ private int val; /** * コンストラクタ * @param start * @param end */ public RangeTable(int start, int end) { this.cur = start; this.end = end; } /** * 表関数の結果セットを最適化するためのパラメータを受け取る. * RestrictedVTIインターフェイスにより、 * 表関数が呼び出されるときに、SQLより要求されるカラムと、もし行の範囲が分かっていれば * そのWhere条件が渡される. * @param columnNames SQLで使われるカラム名のリスト * @param restriction この表の条件(もし、わかっている場合なら。不明ならnull) * @throws SQLException */ @Override public void initScan(String[] columnNames, Restriction restriction) throws SQLException { // テスト用に表示してみる。最適化はしない。 StringBuilder buf = new StringBuilder(); buf.append("columns: ["); for (String columnName : columnNames) { buf.append(columnName); buf.append(","); } buf.append("], restriction="); if (restriction != null) { buf.append("{sql="); buf.append(restriction.toSQL()); buf.append("}"); } System.out.println("initScan: " + buf); } @Override public void close() throws SQLException { // 使い終わった場合に呼び出される System.out.println("lastSeq=" + val); } @Override public boolean next() throws SQLException { // 最終インデックスまではレコードは有効 return (val = cur++) < end; } @Override public int getInt(int columnIndex) throws SQLException { // 単純化のため、カラムインデックスがなんであれ、常にvalを返す. return val; } @Override public boolean wasNull() throws SQLException { // nullを返すことはないので常にfalse return false; } @Override public int findColumn(String columnName) throws SQLException { // 単純化のため、カラム名がなんであれ、最初のカラムを返す. return 1; } /** * テーブルを返すユーザー定義関数. * * ResultSet派生クラスを返すことでSQL上で表として扱うことができる. * ユーザー定義関数はpublic staticな関数であれば良い. * 以下のDDLでユーザー定義関数を宣言する. * * create function makeRangeTable(cnt int) returns table -- テーブルを返却 * ( * -- 返却されるテーブル型のレコード定義 * seq integer * ) * language java * parameter style DERBY_JDBC_RESULT_SET -- テーブルを返すことを明示 * no sql -- 関数の中でSQLは実行しないことを示す * external name 'apachederbysample.types.RangeTable.makeRangeTable'; * * @param end * @return * @throws SQLException */ public static RangeTable makeRangeTable(int end) throws SQLException { return new RangeTable(0, end); } }
(※ プログラム中には、実験のため、コンソールへの出力を随所に入れてある。)
表関数を定義するDDLは以下のとおり。
create function makeRangeTable(cnt int) returns table -- テーブルを返却 ( -- 返却されるテーブル型のレコード定義 seq integer ) language java parameter style DERBY_JDBC_RESULT_SET -- テーブルを返すことを明示 no sql -- 関数の中でSQLは実行しないことを示す external name 'apachederbysample.types.RangeTable.makeRangeTable';
基本的にはユーザー関数定義であるが、テーブル構造を示す部分や、テーブルを返すことを示すキーワードなどを設定する必要がある。
プログラム中にはカラム名は意識する必要がなかったが、第一カラムが「seq」というカラム名でアクセスされることが定義されている。
※ 注意点としては、表関数の実装で返す戻り型は、スーパークラスである「ResultSet」ではなく、明確に具象クラスである「RangeTable」を返す必要がある。(リフレクションで何かやってるらしい?)
使用例1 (単純アクセス)
実際に使ってみる。
ij> select * from table(makeRangeTable(3)) as t; initScan: columns: [SEQ,], restriction= SEQ ----------- 0 1 2 3 行が選択されました lastSeq=3 ij>
表関数makeRangeTableに引数を渡している。その戻り値を「table()」でラップすることで、テーブルとして利用可能となる。
忘れてはならないことは、必ず表には名前をつける、ということ。
(表に名前をつけないとSyntax Errorが発生する。)
使用例2 (JOIN)
歯抜けの連番をみつけるのに、連番テーブルがあると便利だね、というセオリーがある。
http://codezine.jp/article/detail/1076
http://www.geocities.jp/oraclesqlpuzzle/10-172.html
ここに仮想表としての連番テーブルがあるので、これを使って実験してみる。
ij> create table testtbl4(
idx integer primary key,
val varchar(16)
);
autocommit off;
insert into testtbl4 (idx, val) values(1, 'aaa');
insert into testtbl4 (idx, val) values(2, 'bbb');
insert into testtbl4 (idx, val) values(4, 'ddd');
insert into testtbl4 (idx, val) values(8, 'ggg');
insert into testtbl4 (idx, val) values(9, 'hhh');
commit;
select seq
, idx
, val
from table(makeRangeTable(100)) as seqtbl
left outer join
testtbl4
on testtbl4.idx = seqtbl.seq
where seqtbl.seq < (select coalesce(max(idx), 0) from testtbl4)
;
> > > 0 行が挿入/更新/削除されました
ij> ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> 1 行が挿入/更新/削除されました
ij> ij> > > > > > > > > initScan: columns: [SEQ,], restriction=
SEQ |IDX |VAL
----------------------------------------
0 |NULL |NULL
1 |1 |aaa
2 |2 |bbb
3 |NULL |NULL
4 |4 |ddd
5 |NULL |NULL
6 |NULL |NULL
7 |NULL |NULL
8 |8 |ggg
9 行が選択されました
lastSeq=100
ij> 仮想表であっても、JOINは普通に可能である。
※ ちなみに、Apache DerbyにはValues Expressionによる、式による行セットを作ることができるので、表関数を使うまでもなく、ある程度の定数テーブルであればSQL標準だけで作ることが可能である。
select seq , idx , val from (values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) as seqtbl(seq) left outer join testtbl4 on testtbl4.idx = seqtbl.seq;
VTITemplateとは?
ResultSetインターフェイスをオーバーライドして、ほとんどのメソッドでUnsupported Operation Exceptionを発生させるだけのクラス。
このクラスをベースとすれば、必要なメソッドだけをオーバーライドすれば良い。
最低限必要な実装として
- next カーソルを進め、終端に達したらfalseを返す
- close 使い終わったら必ず呼び出されるので、ここで後始末する.
- 各種getXXXメソッド
- wasNull
各種getXXXメソッドはデフォルトでUnsupported Operation Exception例外が発生するだけの実装となっている。
Derbyからアクセスされるときはカラム名ではなく、必ずインデックスで問い合わせられ、
また、getObjectではなく、integer型であればgetIntのように、カラム型にマップされているメソッドに対して直接問い合わせが行われる。
したがって、今回のように1カラムしかなく、それがintegerである場合は、getIntだけを実装すれば良い。
プリミティブ型への問い合わせの後には、wasNullメソッドが呼び出されるため、これも必ず実装する必要がある。
範囲の最適化 RestrictedVTIインターフェイス
RestrictedVTIインターフェイスを実装しinitScanメソッドを用意すると、SQL中で使われるカラム名と、もし判明しているのであれば行の範囲についての情報が渡されるようになる。
initScanは、ResultSetの他のメソッドが呼び出されるよりも前に呼ばれることが保証されている。
ここで、たとえばカラム数が数百もあるような場合でもSQLで実際に数個のカラムしか使わない場合は、この表関数内では、その数個のカラムだけを保持するように実装すれば効率が良くなる。
また、Where句の指定で、この表関数へのアクセスが明確に範囲が分かっているのであれば、行の範囲制限も可能となる。
たとえば、
ij> select seq from table(makeRangeTable(10)) as t where t.seq > 5;
initScan: columns: [SEQ,], restriction={sql="SEQ" > 5}
SEQ
-----------
6
7
8
9
4 行が選択されました
lastSeq=10
ij> SQL中で表関数が5以下のものを必要としないことは明白なのでinitScanにヒントが渡されている。
(しかし、この実装では、とくに何もしていないため、10行分カーソルを回している。)
表関数の注意点と、更なる最適化
Apache Derbyにおける表結合は、
- Nested Loop (標準の結合方法)
- Hash Join (イコールによる比較の場合のみ可、メモリ負荷大)
のいずれかである。
Derbyでは、ネステッドループが標準である。
ということは、2つの表を結合したとき、他方の表は繰り返し参照される、ということである。
たとえば、表関数同士をNested Loopする例としては以下のようになる。
ij> select t1.seq, t2.seq from table(makeRangeTable(3)) t1 > cross join table(makeRangeTable(5)) t2; initScan: columns: [SEQ,], restriction= initScan: columns: [SEQ,], restriction= SEQ |SEQ ----------------------- 0 |0 0 |1 0 |2 0 |3 0 |4 lastSeq=5 initScan: columns: [SEQ,], restriction= 1 |0 1 |1 1 |2 1 |3 1 |4 lastSeq=5 initScan: columns: [SEQ,], restriction= 2 |0 2 |1 2 |2 2 |3 2 |4 lastSeq=5 15 行が選択されました lastSeq=3 ij>
Cross Joinするとか、いかにもわざとらしいが、要するに、t2側がt1側のレコード数だけ繰り替えし呼び出されていることがわかる。
(どちらが駆動表になるかは明確ではない。現在までの仕組みでは、t1がt2よりも行数が少ないということはSQLからは分からないためである。)
この連番を返すだけの、ほとんどコストゼロの仮想表であれば何も気にする必要はないだろうが、
ファイルアクセスや他のデータベースの結果セットとJOINするのであれば、このような特性を考えて、うまく最適化する必要があるだろう。
このあたりについては、詳しくは調べきれていないが、表関数のコストを見積もり、最適化するための仕組みは存在するようである。
http://db.apache.org/derby/docs/10.9/devguide/cdevspecialtfoptimizer.html
これらによって、外部表へのアクセスなどを行う場合に、より実用的なパフォーマンスが得られるようになるかもしれない。
SQLを使うユーザー定義関数
ユーザー定義関数のうち、関数の中からデータベースアクセスを行う関数を定義することもできる。
特別な違いはとくになく、以下の点を注意すれば良い。
- DDLでユーザー定義関数を宣言するとき、no sqlではなく、reads sql dataのように読み書きモードを指定する。
- 自分自身のデータベースへアクセスする場合は、ネステッドコネクションを利用する
- 関数の中でコミットやロールバック等は行わない
ネステッドコネクションの利用
ネステッドコネクションとは、そのユーザー定義関数を呼び出したSQLステートメントと同じコネクションを返すものである。
try (Connection conn = DriverManager.getConnection("jdbc:default:connection")) { System.out.println("nested-connection: " + conn); …. }
「jdbc:default:connection」という接続文字列を使うことで、現在のコネクションと同じコネクションを取得することができる。
(取得したコネクションは、一般的な作法と同様に明示的にcloseする必要がある。)
コネクションが同一であり、且つ、トランザクションも同一である。
従って、まだコミットされていない変更なども、同一トランザクション内となるため、アクセスすることが出来る。
実装例
/** * TESTTBL4の次のインデックスを求める. * IDXは1から最大までのインデックスの連番において、IDXに欠番があれば、 * その最初の欠番を用いる. そうでなければ最大のIDX+1の番号を用いる. * * このファンクションは以下のDDLによってユーザー定義関数として登録される. * * create function nextIdxForTestTbl4() returns integer * language java * not deterministic -- 引数が同じでも同じ値を返すとは限らないことを明示する.(デフォルト) * parameter style java -- 戻り値のタイプ * reads sql data -- 関数の中でSQLによるテーブルの読み込みを行うことを示す * external name 'apachederbysample.pg.DbStoredServices.nextIdxForTestTbl4'; * * @return 次のIDX * @throws SQLException */ public static int nextIdxForTestTbl4() throws SQLException { int ret = 1; // このユーザー定義関数を呼び出したステートメントと同じコネクションを再利用する. // (同一コネクション、トランザクションでの処理となる.) // (このコネクションに対してコミット等を行ってはならない) try (Connection conn = DriverManager.getConnection("jdbc:default:connection")) { System.out.println("nested-connection: " + conn); // 現在の最大のインデックスを取得する. (なければ0) int mx = 0; String sqlCnt = "select coalesce(max(idx), 0) + 1 from testtbl4"; try (Statement stm = conn.createStatement()) { try (ResultSet rs = stm.executeQuery(sqlCnt)) { if (rs.next()) { mx = rs.getInt(1); } } } // 1から最大連番+1までの間で未使用の欠番があれば、それを取得する. String sqlNextIdx = " select seq from table(makeRangeTable(?)) as seqtbl" + " where not exists (select * from testtbl4 where idx = seq)" + " and seq > 0" + " order by seq" + " fetch first 1 row only"; // SQL:2008 行数制限指定 try (PreparedStatement stm = conn.prepareStatement(sqlNextIdx)) { // makeRangeTableの引数に現在の最大のインデックス + 1を渡す // (欠番がない場合は、最大インデックス + 1 がマッチすることになる。) stm.setInt(1, mx + 1); try (ResultSet rs = stm.executeQuery()) { if (rs.next()) { ret = rs.getInt(1); } } } } return ret; }
DDLは以下のようになる。
create function nextIdxForTestTbl4() returns integer language java not deterministic -- 引数が同じでも同じ値を返すとは限らないことを明示する.(デフォルト) parameter style java -- 戻り値のタイプ reads sql data -- 関数の中でSQLによるテーブルの読み込みを行うことを示す external name 'apachederbysample.pg.DbStoredServices.nextIdxForTestTbl4';
*1:JDK6は期間が長過ぎたせいで、付属ツールもバージョンがあがっていたり、JavaVMが拡張されたり、そもそも使える標準APIも増えていたりするので、単にJDK6とひとくくりに出来ないところがある。
*2:類似コンセプトの組み込み可能なPure JavaなDBとしては、HSQLDB, H2などもある。
*3:インメモリデータベースであっても、もちろん、エクスポート・インポートは可能であるからアプリケーションの起動と終了時のみファイルに永続化する、という使い方も可能であろう。
*4:古いデータベースで、double型のカラムにDouble.MAX_VALUEを格納できない、という問題が発生したところ、データベースをupgrade=trueでフル・アップグレードしたら解決した。derbyのバージョンとデータベースのバージョンは合わせていたほうが余計なトラブルを避けられるのかもしれない。※2014/09/04追記
*5:チューニングガイドhttp://db.apache.org/derby/docs/10.10/tuning/ctundepth1003116.html によると、"Rebuilding indexes improves performance significantly in these situations. To rebuild an index, drop it and then re-create it."とあるので、インデックスを再構築するにはドロップして作成する、という手順のようである。
*6:複数スレッドから同一ページへの衝突を避けるという意図があるのか(単なる憶測だが)
