サイトの全体構成

JRuby や Ruby から dbi を経由して SQLite バージョン 3 を使う

Ruby 言語を使い，リレーショナルデータベースを扱うプログラムを簡単に書くことが出来ます．

このページでは，組み込み型のリレーショナルデータベース管理システム SQLite バージョン 3 を使うことにする．

このページでは，Ruby プログラムの中に SQL プログラムを埋め込み，SQLite バージョン 3 上で動かします．そのための方法はいろいろありますが，ここでは，Ruby 用の dbi パッケージを使うことにします． dbi パッケージを使っているので，SQLite バージョン 3 以外のリレーショナルデータベース管理システムを使う場合でも簡単な書き換えと JDBC コネクタのインストール等の作業で済むと思います．

Ruby の中に埋め込まれた SQL を用いたテーブル定義
Ruby の中に埋め込まれた SQL を用いたテーブルへの行の挿入
Ruby の中に埋め込まれた SQL を用いた問い合わせ
テーブルの一覧表示

【Ruby on Rails との関係】

Ruby on Rails でもリレーショナルデータベースを簡単に扱うことができるが，この Web ページとは手段も目的も違っているので混同しないように．

Ruby プログラムの中に SQL プログラムを埋め込む場合（この Web ページの目的）：
Ruby プログラム → DBI → (JDBC) → SQLite バージョン 3 などリレーショナルデータベース管理システムのコネクタ
※ Ruby から SQLite バージョン 3 を使う方法として，sqlite3-ruby パッケージを使う方法もあります．別の Web ページで説明する．
Ruby プログラム → Active Record → activerecord_jdbcsqlite3_adapter などリレーショナルデータベース管理システムのアダプタ

SQLite 3の SQL に関する詳しい説明は：

http://www.hwaci.com/sw/sqlite/lang.html にあります．

前準備

必要となるソフトウェア

次のソフトウェアのインストールが済んでいること．「Ruby プログラミング」の Web ページを参考にしてください．

Ruby のインストールあるいは JRuby のインストールが済んでいること

あらかじめ決めておく事項

使用するデータベース管理システム

このページでは，SQLite バージョン 3 を使うことにする． MySQL, PostgreSQL, JavaDB などでも同様の手順で行えるはずです．

データベース管理システム名: SQLite バージョン 3

データベース名

使用するデータベースの名前のことを，「データベース名」と呼ぶことにする．このページでは，データベース名を，次のように書く．

データベース名： testdb
データベース名は，自由に決めていいですが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと．すでに存在するデータベースを使ってもいい（つまり，既存のデータベースのデータベース名を使う）し，新しいデータベースを作ることにしてもいいです．

（SQLite あるいは JavaDB の場合に限り）データベースディレクトリ

データベースファイルが置かれるディレクトリのことです． 自由に決めていいですが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと．データベース名と SQLite 3 のデータベースディレクトリとは別ものです．混同しないこと．

SQLite バージョン3 の場合:
このページでは，SQLite 3 のデータベースディレクトリ（絶対パス形式）を次のように書く．
```
C:/SQLiteDB (Windows の場合)
/var/SQLiteDB (Linux の場合)
```
Windows の場合
，頭に「C:」のように，ドライブ名が付くことに注意してください．
JavaDB の場合:
JavaDB データベースディレクトリ（絶対パス形式）は，SQLite と同様に，次のような形式になる．
```
C:/DerbyDatabases (Windows の場合)
/var/DerbyDatabases (Linux の場合)
```

※ なお，データベースディレクトリは，「dbdir」のように，相対パス形式で書く場合と，「C:/SQLiteDB」のように，絶対パス形式で書く場合があります．

データベース URL (接続文字列)

データベース URL (接続文字列) は，データベース管理システムの種類によって決まる値です．

SQLite バージョン3 の場合:
```
jdbc:sqlite:C:/SQLiteDB/testdb (Windows の場合)
jdbc:sqlite:/var/SQLiteDB/testdb (Linux の場合)
```
決まり文句 jdbc:sqlite:に， SQLite 3 のデータベースディレクトリ（絶対パス形式）と， データベース名を付けます．
MySQL の利用の場合:
```
jdbc:mysql://localhost:3306/testdb
```
決まり文句 jdbc:mysql:に，データベース名などを付けます．
PostreSQL の場合
```
jdbc:postgresql:testdb
```
決まり文句 jdbc:postgresql:に，データベース名を付けます．
JavaDB の場合:
```
jdbc:derby:C:/DerbyDatabases/testdb,create=true (Windows の場合)
jdbc:derby:/var/DerbyDatabases/testdb,create=true (Linux の場合)
```
決まり文句 jdbc:derby:に， SQLite 3 のデータベースディレクトリ（パス形式）と， データベース名と「,create=true」を付けます．「create=true」はデータベースが存在しないときは，接続時に自動生成するということ．

一般ユーザのアカウント（ユーザ名とパスワード）

MySQL, PostgreSQL などでは，データベース管理システムに接続するためにするために使う一般ユーザのアカウント（ユーザ名とパスワード）を作成しておくこと．

SQLite, JavaDB では不要です．

JDBC ドライバ名（Java クラス名）

JDBC ドライバ (Java クラス名) は，データベース管理システムの種類によって決まる値です．

SQLite バージョン3 の場合:
org.sqlite.JDBC
MySQL の利用の場合
com.mysql.jdbc.Driver
PostreSQL の場合
org.postgresql.Driver
JavaDB の場合:
org.apache.derby.jdbc.EmbeddedDriver
Firebird の場合
org.firebirdsql.jdbc.FBDriver

JDBC jar ファイルのダウンロード

JDBC jar ファイルをダウンロードし，分かりやすいディレクトリに置く．このページでは，次のディレクトリに置くことにします．

C:\Program Files\Java (Windows の場合)
/usr/java (Linux の場合)

SQLite バージョン3 の場合:
ダウンロード手順は別のページで説明している．
MySQL の利用の場合
ダウンロード手順は別のページで説明している．
PostreSQL の場合
http://jdbc.postgresql.org/ から入手できる．
JavaDB の場合:
Java DB (Derby) のインストールのときに，Eclipse プラグインもインストールしますが，その中に Java DB 用 JDBC の jar ファイルが含まれています．
Firebird の場合
ダウンロード手順は別のページで説明している．

gem で dbi パッケージをインストール

「gem update --system」の実行
先に ruby を最新のものに更新したいので，コンソールで，「gem update --system」を実行する．
「gem install dbi --include-dependencies」の実行
■ Windows の場合の実行例
インストール終了の確認
エラーメッセージが出ていないことを確認する．

gem で JDBC 用データベース・ドライバをインストール

データベース・ドライバの一覧表示
「gem list --remote dbd」を実行
JDBC 用のデータベース・ドライバのインストール
JDBC 用のデータベース・ドライバをインストールしたい場合は「gem install dbd-jdbc --include-dependencies」を実行

SQL を用いたテーブル定義

Ruby の中に埋め込まれた SQL によるテーブルの定義の実行例は下記の通り．

テーブル定義の要点

products: テーブル名 (複数形になっているのは Rails の流儀に習って)
id, product_name, type, cost, created_at: テーブルの属性名

ここでは，Rails の流儀に習って，テーブルには「id」という名前が付いた属性 を含ることにします．また id のデータ型は「integer primary key autoincrement not null」に設定します．

テーブルを定義するために，次のような SQL を実行してみる． SQL を用いたテーブル定義については，「リレーショナルデータベースのデータ構造と一貫性制約の Web ページで詳しく説明している．

create table products (
    id            INTEGER  PRIMARY KEY autoincrement not null,
    product_name  TEXT     UNIQUE not null,
    type          TEXT     not null,
    cost          real,
    created_at    DATETIME not null );

プログラムの例

Ruby のプログラムは次のようになる．Ruby プログラムの中に SQL プログラムが埋め込まれています．

#! ruby -Ks
# coding: windows-31j
require 'pp'
require 'rubygems'
require 'dbi'

# データベース名 testdb と，データベースディレクトリの設定
#   Windows の場合，頭に「C:」のように，ドライブ名が付くことに注意
#   MySQL, PostgreSQL などでは，データベースディレクトリが無いので，データベース名をそのまま書く
# Windows, SQLite 3
# DBNAME = "C:/SQLite/testdb"
# Linux, SQLite 3
DBNAME = "/var/SQLite/testdb"

# データベース URL（接続文字列）, ユーザ名，パスワード，JDBC ドライバ名
#  SQLite, JavaDB ではユーザ名とパスワードは nil
DBURL = "DBI:jdbc:sqlite:dbname=\"" + DBNAME + "\""
# DBURL = "jdbc:mysql://localhost:3306/" + DBNAME
# DBURL = "jdbc:postgresql:" + DBNAME
# DBURL = "jdbc:derby:" + DBNAME + ",create=true"
DBUSER = nil
DBPASSWORD = nil
JDBCDRIVER = "org.sqlite.JDBC"
# JDBCDRIVER = "com.mysql.jdbc.Driver"
# JDBCDRIVER = "org.postgresql.Driver"
# JDBCDRIVER = "org.apache.derby.jdbc.EmbeddedDriver"

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
dbh = DBI.connect(DBURL, DBUSER, DBPASSWORD, 'AutoCommit' => false, 'driver'=>JDBCDRIVER )
# SQL を用いた commodities テーブルの定義
sql = <<SQL
create table products (
    id            INTEGER  PRIMARY KEY autoincrement not null,
    product_name  TEXT     UNIQUE not null,
    type          TEXT     not null,
    cost          real,
    created_at    DATETIME not null );
SQL
dbh.do(sql)

dbh.commit
dbh.disconnect

■ Linux での実行結果の例

SQL を用いたテーブルへの行の挿入

Ruby の中に埋め込まれた SQL によるテーブルへの行の挿入の実行例は下記の通り．

ここでは次のようなテーブルを作る．

そのために，次のような SQL を実行する．

insert into commodities values( 1, 'Fukuoka apple',     'apple',  50);
insert into commodities values( 2, 'Kumamoto orange L', 'orange', 30);
insert into commodities values( 3, 'Kumamoto orange M', 'orange', 20);
insert into commodities values( 4, 'Fukuoka melon',     'melon',  NULL);

Ruby でプログラムを作るときの要点は：

dbh.prepare はプリペアード・ステートメント．これは，同一の SQL を繰り返し実行するときに便利
Ruby ではNULL がない ので，nil を使う

Ruby のプログラムは次のようになる．

#! ruby -Ks
# coding: windows-31j
require 'pp'

# JRuby で dbi を使うときは次のように書く（他の良い方法が思いつかない）．
$LOAD_PATH.unshift("C:/jruby140/lib/ruby/gems/1.8/gems")
require 'dbi-0.4.3/lib/dbi'
# Ruby の場合
# require 'dbi'

# データベース名 testdb と，データベースディレクトリ C:/SQLite 3の設定
#   Windows の場合，頭に「C:」のように，ドライブ名が付くことに注意
#   MySQL, PostgreSQL などでは，データベースディレクトリが無いので，データベース名をそのまま書く
DBNAME = "C:/SQLite/testdb"

# データベース URL（接続文字列）, ユーザ名，パスワード，JDBC ドライバ名
#  SQLite, JavaDB ではユーザ名とパスワードは nil
DBURL = "DBI:jdbc:sqlite:" + DBNAME
# DBURL = "jdbc:mysql://localhost:3306/" + DBNAME
# DBURL = "jdbc:postgresql:" + DBNAME
# DBURL = "jdbc:derby:" + DBNAME + ",create=true"
DBUSER = nil
DBPASSWORD = nil
JDBCDRIVER = "org.sqlite.JDBC"
# JDBCDRIVER = "com.mysql.jdbc.Driver"
# JDBCDRIVER = "org.postgresql.Driver"
# JDBCDRIVER = "org.apache.derby.jdbc.EmbeddedDriver"

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
dbh = DBI.connect(DBURL, DBUSER, DBPASSWORD, 'AutoCommit' => false, 'driver'=>JDBCDRIVER )
st = dbh.prepare("insert into commodities values (:1, :2, :3, :4)")
# SQL を用いたテーブルへの行の挿入
st.execute( 1, 'Fukuoka apple',     'apple', 50);
st.execute( 2, 'Kumamoto orange L', 'orange', 30);
st.execute( 3, 'Kumamoto orange M', 'orange', 20);
st.execute( 4, 'Fukuoka melon',     'melon',  nil);
st.finish

dbh.commit
dbh.disconnect

SQL を用いた問い合わせ

Ruby の中に埋め込まれた SQL による問い合わせの実行例は下記の通り．

Ruby でプログラムを作るときの要点は：

問い合わせの結果が欲しいので「dbh.execute(...) do |sth| ... end」
問い合わせの結果を整形して表示したいので，pp を使う

テーブルの全ての行の表示

SELECT * FROM commodities;

Ruby のプログラムは次のようになる．

#! ruby -Ks
# coding: windows-31j
require 'pp'

# JRuby で dbi を使うときは次のように書く（他の良い方法が思いつかない）．
$LOAD_PATH.unshift("C:/jruby140/lib/ruby/gems/1.8/gems")
require 'dbi-0.4.3/lib/dbi'
# Ruby の場合
# require 'dbi'

# データベース名 testdb と，データベースディレクトリ C:/SQLite 3の設定
#   Windows の場合，頭に「C:」のように，ドライブ名が付くことに注意
#   MySQL, PostgreSQL などでは，データベースディレクトリが無いので，データベース名をそのまま書く
DBNAME = "C:/SQLite/testdb"

# データベース URL（接続文字列）, ユーザ名，パスワード，JDBC ドライバ名
#  SQLite, JavaDB ではユーザ名とパスワードは nil
DBURL = "DBI:jdbc:sqlite:" + DBNAME
# DBURL = "jdbc:mysql://localhost:3306/" + DBNAME
# DBURL = "jdbc:postgresql:" + DBNAME
# DBURL = "jdbc:derby:" + DBNAME + ",create=true"
DBUSER = nil
DBPASSWORD = nil
JDBCDRIVER = "org.sqlite.JDBC"
# JDBCDRIVER = "com.mysql.jdbc.Driver"
# JDBCDRIVER = "org.postgresql.Driver"
# JDBCDRIVER = "org.apache.derby.jdbc.EmbeddedDriver"

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
dbh = DBI.connect(DBURL, DBUSER, DBPASSWORD, 'AutoCommit' => false, 'driver'=>JDBCDRIVER )

# SQL を用いた問い合わせ
sql = <<SQL
SELECT * FROM commodities; 
SQL

dbh.execute(sql) do |sth|
  pp sth.fetch_all
end
dbh.commit
dbh.disconnect

【実行結果の例】

条件に合致する行のみの表示 (その 1)
```
SELECT * FROM commodities WHERE type = 'orange';
```
上記の Ruby プログラムの「SELECT * FROM commodities; 」のところを「SELECT * FROM commodities WHERE type = 'orange';」に書き換え
【実行結果の例】
条件に合致する行のみの表示 (その 2)
```
SELECT * FROM commodities WHERE price > 25;
```
上記の Ruby プログラムの「SELECT * FROM commodities; 」のところを「SELECT * FROM commodities WHERE price > 25;」に書き換え
【実行結果の例】

テーブルの一覧表示

SQLite バージョン 3 でデータベース内のテーブル一覧を表示するには，sqlite_maste, sqlite_temp_masteという名前が付いた特別なテーブルを使う．

sqlite_master: TEMPORARY テーブル以外のデータベーススキーマが格納されている．
sqlite_temp_master: TEMPORARY テーブルのデータベーススキーマが格納されている．

データベーススキーマを見たいときは，次のような SQL を実行する．

select * from sqlite_master;

※ sqlite_master, sqlite_temp_master に，DROP TABLE, UPDATE, INSERT, DELETE 操作を行うことは許されていません

上記の Ruby プログラムの「SELECT * FROM commodities; 」のところを「SELECT * FROM sqlite_master;」に書き換え

【実行結果の例】

結果は長くなるので，一部分だけを載せています．

本サイトは金子邦彦研究室のWebページである．

資料等の公開では，原則，「クリエイティブコモンズ BY NC SA」として公開するようにしている． PDFファイル，パワーポイントファイルなどには，「クリエイティブコモンズ BY NC SA」を明記するとともに，ロゴを記載するようにしている（作業が間に合っていない分もあるのでご容赦ください）．

公開している資料をご利用になる場合の，再配布の条件，剽窃の防止などについて，別ページ »で説明再配布や資料改変の際には，そのページをご確認ください．

サイトマップは，サイトマップのページをご覧下さい．本サイト内の検索は，サイト内検索のページをご利用下さい．

問い合わせ先：金子邦彦（かねこくにひこ） [image]