簡単導入Oracle SQLclと手慣れたJSで少し凝ったデータ登録の方法
こんにちは、技術戦略部の水口です。
最近データ移行の仕事をする機会があり、その調査の中で見つけた、Oracle Databaseで手軽に柔軟にデータ登録する方法を紹介したいと思います。
具体的には、Oracle SQL Developerコマンドラインと呼ばれる、Oracle SQLcl（以降、SQLcl）でJavaScriptを利用した方法です。
SQLclは依存関係が少なく比較的導入が簡単で、利用プログラマが多いJavaScriptを組み合わせることで少し凝ったデータ登録が手軽に行えます。
世の中的にこの方法でデータ登録を紹介されている例が少なかったので、参考になれば幸いです。
SQLclのインストール
SQLclはJavaベースのコマンドライン・インタフェースで、動作環境にはJRE8以上が必要です。Oracle Clientは不要です。
SQLclのインストールもダウンロードページからダウンロードしたzipファイルを展開するだけなので、導入の敷居は比較的低いかと思います。
必要に応じて、展開されたbinディレクトリを環境変数PATHに追加してください。
ちなみに、筆者の環境は以下の通りで、環境変数PATHも編集しています。
- Ubuntu 20.04.4 LTS (WSL2)
- OpenJDK 1.8.0_312
- Nashorn （JavaScriptエンジン標準同梱、ES5.1のサポート）
これ以降は、筆者の環境を元に説明していきます。
SQLclでのJavaScriptの利用方法
SQLclでは以下のように、手軽にJavaScriptを書いて実行できます。
①作業ディレクトリでJavaScriptのファイル(test.js)を以下の内容で作成する。
ctx.write('Hello world!n');
②sqlコマンドの引数に簡易接続ネーミング・メソッドで接続先を指定し実行する（※）。
$ sql user/password@//hostname:1521/test
※次に紹介する例では接続先の指定は必要となりますが、今回の例ではDB処理が一切無いので「sql /nolog」でも問題はありません。
③SQLclのプロンプト上でscriptコマンドを実行する。
SQL> script test.js
Hello world!
SQL>
SQLclでのJavaScriptによるデータ登録
ここからが本題です。
SQLclにはLOADコマンドのようにCSVファイルをDBのテーブルにインポートする機能はありますが、少し凝ったことをしようとすると対応できません。
具体的には、以下のようなケースが挙げられます。
- 正規化されていない注文データなどのCSVファイルを正規化しつつインポートするケース
- カラムの整形処理が必要なケース
- サロゲートキーをSEQUENCEで採番しなければならないケース
このようなケースでSQLclのJavaScriptの利用が選択肢の一つとなります。
本記事では、以下の注文データ（TSVファイル）をordersテーブルとorder_detailsテーブルに登録するプログラムを紹介します。
TSVファイル
[orders.tsv]
order_no user_name detail_no item_name quantity
0000000001 user1 001 item1 2
0000000001 user1 002 item2 1
0000000001 user1 003 item3 5
0000000002 user2 001 item2 3
0000000002 user2 002 item4 2
0000000003 user3 001 item1 1
0000000003 user3 002 item2 10
0000000003 user3 003 item3 1
0000000003 user3 004 item4 10
0000000004 user1 001 item4 2
DDL
CREATE TABLE orders
(
id NUMBER(10) PRIMARY KEY,
order_no varchar(10) NOT NULL,
user_name varchar(10) NOT NULL
);
CREATE SEQUENCE orders_seq;
CREATE TABLE order_details
(
id NUMBER(11) PRIMARY KEY,
order_id NUMBER(10) NOT NULL,
detail_no varchar(3) NOT NULL,
item_name varchar(40 char) NOT NULL,
quantity number(5) NOT NULL
);
ALTER TABLE order_details add constraint fk_order_details_oi FOREIGN KEY (order_id) REFERENCES orders(id);
CREATE INDEX ix_order_details_oi ON order_details(order_id) TABLESPACE indx;
CREATE SEQUENCE order_details_seq;
サンプルプログラム
[load-tsv.js]
var Files = Java.type('java.nio.file.Files');
var Paths = Java.type('java.nio.file.Paths');
function throwSqlExceptionIfError() {
var e = util.getLastException();
if (e) {
throw e;
}
}
function generateOrderId() {
var orderId = util.executeReturnOneCol('SELECT orders_seq.nextval FROM dual');
throwSqlExceptionIfError();
return orderId;
}
function createOrder(order, orderId) {
var bind = {
"id" : orderId,
"order_no" : order.orderNo,
"user_name" : order.userName,
};
util.executeUpdate('INSERT INTO orders (id, order_no, user_name)'
+ ' VALUES (:id, :order_no, :user_name)', bind);
throwSqlExceptionIfError();
}
function createOrderDetail(order, orderId) {
var bind = {
"order_id" : orderId,
"detail_no" : order.detailNo,
"item_name" : order.itemName,
"quantity" : order.quantity,
};
util.executeUpdate('INSERT INTO order_details (id, order_id, detail_no, item_name, quantity)'
+ ' VALUES (order_details_seq.nextval, :order_id, :detail_no, :item_name, :quantity)', bind);
throwSqlExceptionIfError();
}
function loadTSV(tsvPath) {
var stream = null;
try {
var conn = sqlcl.getConn();
if (conn === null) {
throw new Error('DBに接続できませんでした。\n');
}
conn.setAutoCommit(false);
var first = true;
var orderIds = {};
stream = Files.lines(tsvPath);
stream.forEach(function(line) {
if (first) {
// タイトル行はスキップ
first = false;
return;
}
var values = line.split('\t');
if (values.length < 5) {
// 項目数が不足しているデータはスキップ
return;
}
var order = {
"orderNo" : values[0],
"userName" : values[1],
"detailNo" : values[2],
"itemName" : values[3],
"quantity" : values[4],
};
if (!orderIds[order.orderNo]) {
// ordersテーブルには重複したデータを登録しないようにする
orderIds[order.orderNo] = generateOrderId();
createOrder(order, orderIds[order.orderNo]);
}
createOrderDetail(order, orderIds[order.orderNo]);
});
conn.commit();
} catch (e) {
ctx.write(e);
if (conn) {
conn.rollback();
}
} finally {
if (stream) {
stream.close();
}
}
}
(function() {
if (args.length <= 1) {
ctx.write('第1引数にTSVファイルのパスを指定してください。\n');
return;
}
var tsvPath = Paths.get(args[1]);
if (!Files.exists(tsvPath)) {
ctx.write('第1引数にTSVファイルのパスを正しく指定してください。\n');
return;
}
loadTSV(tsvPath);
})();
【解説】引数の処理
今回のサンプルでは、TSVファイルのパスは以下のようにスクリプトの実行時に引数で渡します。
SQL> script load-tsv.js orders.tsv
スクリプト側ではargsというグローバル変数で参照できます。
if (args.length <= 1) {
ctx.write('第1引数にTSVファイルのパスを指定してください。n');
return;
}
var tsvPath = Paths.get(args[1]);
args以外にも利用できるグローバル変数がいくつかあるので、使い方も含めて、Oracleが提供するドキュメントやコードサンプルを参照してください。
【解説】Javaクラスの利用
Java.type()関数を使ってJavaのクラスにアクセスできます。
var Files = Java.type('java.nio.file.Files');
var Paths = Java.type('java.nio.file.Paths');
今回のサンプルでは、TSVファイルの読み込み処理で、JavaのStream APIを利用しています。
stream = Files.lines(tsvPath);
stream.forEach(function(line) {
// 途中省略
}
【解説】DBのトランザクション管理
sqlclというグローバル変数を経由してコネクションを取得し、DBのトランザクション管理を実現しています。
コネクションを取得した後はJavaでよく見かける実装と殆ど同じなので、理解しやすいかと思います。
try {
var conn = sqlcl.getConn();
if (conn === null) {
throw new Error('DBに接続できませんでした。n');
}
conn.setAutoCommit(false);
// 途中省略
conn.commit();
} catch (e) {
ctx.write(e);
if (conn) {
conn.rollback();
}
} finally {
【解説】DB参照系の処理
DB参照系／更新系の処理では、グローバル変数のutilが提供するexcuteで始めるメソッドが便利です。
今回のサンプルでは、先頭行の先頭列の値を取得できるutil.executeReturnOneCol()を使い、orders.idのシーケンスを取得しています。
function generateOrderId() {
var orderId = util.executeReturnOneCol('SELECT orders_seq.nextval FROM dual');
throwSqlExceptionIfError();
return orderId;
}
エラーハンドリングは少し特殊で、utilのexcuteで始めるメソッドのエラーは、util.getLastException()でチェックする必要があります。
今回のサンプルでは、エラーがある場合は例外をスローする処理をメソッドに抽出して共通化しています。
function throwSqlExceptionIfError() {
var e = util.getLastException();
if (e) {
throw e;
}
}
【解説】DB更新系の処理
Oracleが提供するドキュメントやコードサンプルでDB更新系の処理を見つけられなかったのですが、SQLclのインストール先にdbtools-common.jarがあり、その中のoracle.dbtools.db.DBUtilクラスの定義が参考になりました。
今回のサンプルでは、util.executeUpdate()で更新系の処理を実行しています。
function createOrder(order, orderId) {
var bind = {
"id" : orderId,
"order_no" : order.orderNo,
"user_name" : order.userName,
};
util.executeUpdate('INSERT INTO orders (id, order_no, user_name)'
+ ' VALUES (:id, :order_no, :user_name)', bind);
throwSqlExceptionIfError();
}
エラーハンドリングは、DB参照系の処理と同様です。
まとめ
このように、SQLclは簡単にインストールできて、JavaScriptで手軽に柔軟にデータを登録できます。
ちょっとしたデータ登録であれば十分に使えるポテンシャルがありそうです。
SQLclのドキュメントはJavaScriptに関する情報が少ないので、今後充実していってほしいと思っています。
さて、ラクーン技術戦略部では一緒に働く仲間を募集しています！採用情報はこちらです。
