サイトマップ サイト内検索 まとめ

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

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

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

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

【Ruby on Rails との関係】

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

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

前準備

必要となるソフトウェア

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

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

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

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

データベース名

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

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

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

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

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

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

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

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

SQLite, JavaDB では不要です．

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

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

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

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

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

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

  1. 「gem update --system」の実行

    先に ruby を最新のものに更新したいので，コンソールで， 「gem update --system」を実行する．

  2. 「gem install dbi --include-dependencies」の実行

    * Windows の場合の実行例

  3. インストール終了の確認

    エラーメッセージが出ていないことを確認する．

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

  1. データベース・ドライバの一覧表示

    「gem list --remote dbd」を実行

  2. JDBC 用のデータベース・ドライバのインストール

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

SQL を用いたテーブル定義

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

テーブル定義の要点

ここでは，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 でプログラムを作るときの要点は：

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 でプログラムを作るときの要点は：

テーブルの一覧表示

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

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

select * from sqlite_master;

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

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

【実行結果の例】

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