金子邦彦研究室プログラミングRuby による Web/データベース・プログラミングRuby 用の sqlite3-ruby パッケージを利用して Ruby から SQLite バージョン 3 を使う

Ruby 用の sqlite3-ruby パッケージを利用して Ruby から SQLite バージョン 3 を使う

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

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

このページでは,Ruby プログラムの中に SQL プログラムを埋め込み,SQLite 3 上で動かします. そのための方法はいろいろありますが,ここでは,Ruby 用の sqlite3-ruby パッケージを使うことにします.JRuby では sqlite3-ruby パッケージは動かない (2009/11 時点) みたいなので,JRuby を使いたい人は,この Web ページを参考にしないこと.

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

前準備

必要となるソフトウェア

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

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

使用する SQLite 3のデータベースのデータベースファイル名を決めておくこと. このページでは,次のように書く.Ruby の流儀で,Windows の場合は「\」のことを「\\」と書く.

SQLite でのテーブル定義と問い合わせ

SQL はリレーショナルデータベース言語の標準である. ここでは SQLite が持つ SQL の機能のうち主要な部分を紹介する。

SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.

  1. SQLite 3のデータ型

    データ型の種類は,データベース管理システムごとに違う. SQLite では,扱えるデータ型として次の 5 種類がある.詳しい説明は https://www.sqlite.org/datatype3.html にある.

    SQLite 3のデータ型と,SQL の標準が定めるデータ型の定義は異なる.大胆にまとめると,SQLite 3のデータ型の方がより大きな範囲のデータを扱える.

  2. SQL の文字列定数

    SQL の規格では,文字列定数はシングルクオーテーションマーク「'」で囲むことになっている.

  3. SQL テーブル定義文 (create-table-statement) の例

    create table <table-name> (<column-name> <type-name> [<column constraint> ...], ...);

    ※ 「 [<column constraint> ...]」は省略可能であることに注意

  4. SQL 列制約 (column-constraint) の例

    create-table-statement の中に含める一貫性制約やデフォルト値の指定

  5. SQL テーブル制約 (table-constraint) の例

    create-table-statement の中に含める一貫性制約のうち複数の属性に関わるものは table-constraint の形で記述することになる.

  6. SQL 挿入文 (insert statement) の例

    テーブルへの行の挿入

  7. begin transaction, commit, ROLLBACK

    SQL 挿入文 (insert statement) などでのデータベース更新を行うときは,最初に「begin transaction;」を実行する. データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する.

  8. SQL の SELECT, FROM, WHERE の例

    SQL での問い合わせ には SELECT, FROM, WHERE 句が多用される.

SQLite 3 のデータベースの新規作成 (Create a new SQLite database), SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constrant specification using SQL)

ここでは Ruby の中に埋め込まれた SQL を用いて,order_records テーブルを定義し,一貫性制約を記述する.(Define 'score_records' table and specify integrity constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): score_records(name, score, student_name, created_at, updated_at)

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

create table order_records (
    id            INTEGER  PRIMARY KEY autoincrement not null,
    year          INTEGER  not null CHECK ( year > 2008 ),
    month         INTEGER  not null CHECK ( month >= 1 AND month <= 12 ),
    day           INTEGER  not null CHECK ( day >= 1 AND day <= 31 ),
    customer_name TEXT  not null,
    product_name  TEXT  not null,
    unit_price    REAL     not null CHECK ( unit_price > 0 ),
    qty           INTEGER  not null DEFAULT 1 CHECK ( qty > 0 ),
    created_at    DATETIME not null,
    updated_at    DATETIME,
    CHECK ( ( unit_price * qty ) < 200000 ) );

ここでは,Rails の流儀に習って,

プログラムの例

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

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

# SQLite 3 のデータベースファイル名を DBNAME に設定してください.
# Windows の場合. 「C:」のようなドライブ名を付ける
DBNAME = "C:/SQLite/mydb"
# Linux の場合
# DBNAME = "/var/SQLite/mydb"
DBDIR = File.dirname( DBNAME )
DBBASENAME = File.basename( DBNAME )

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )
# SQL を用いた order_records テーブルの定義
sql = <<SQL
create table order_records (
    id            INTEGER  PRIMARY KEY autoincrement not null,
    year          INTEGER  not null CHECK ( year > 2008 ),
    month         INTEGER  not null CHECK ( month >= 1 AND month <= 12 ),
    day           INTEGER  not null CHECK ( day >= 1 AND day <= 31 ),
    customer_name TEXT  not null,
    product_name  TEXT  not null,
    unit_price    REAL     not null CHECK ( unit_price > 0 ),
    qty           INTEGER  not null DEFAULT 1 CHECK ( qty > 0 ),
    created_at    DATETIME not null,
    updated_at    DATETIME,
    CHECK ( ( unit_price * qty ) < 200000 ) );
SQL
db.execute(sql)

db.close

【実行結果の例】

[image]

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

今度は,次のような order_records テーブルを作る. (Construct table 'order_records')

[image]

Ruby の中に埋め込まれた SQL を用いてorder records テーブルへの行の挿入を行う (Insert rows into table 'order_records' using SQL)

insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2019, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now', 'localtime') );

「insert into ...」は行の挿入. datetime('now', 'localtime') は現在日時の取得.DATETIME型は、YYYY-MM-DD HH:MM:SS形式.

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

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

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

# SQLite 3 のデータベースファイル名を DBNAME に設定してください.
# Windows の場合. 「C:」のようなドライブ名を付ける
DBNAME = "C:/SQLite/mydb"
# Linux の場合
# DBNAME = "/var/SQLite/mydb"
DBDIR = File.dirname( DBNAME )
DBBASENAME = File.basename( DBNAME )

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )
# SQL を用いた行の挿入
sql = <<SQL
insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2019, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now', 'localtime') );
SQL
# db.execute_batch(...) は複数の SQL をi度に実行するためのもの
db.transaction do |db| db.execute_batch( sql ) end

db.close

【実行結果の例】


[image]
[image]

order_records テーブルの中身を確認 (Inspect the table 'order_table')


[image]

insert into には 2つの方法がある.(Two styles of "insert into")

■ 属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );

■ 属性の値の並び方を,属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now', 'localtime') );

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

ここでは,Ruby の中に埋め込まれた SQL を用いた問い合わせの実行例を示す. SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

SQL を用いた更新 (Update using SQL)

ここでは Ruby の中に埋め込まれた SQL を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である. この SQL 文を 「begin transaction」と「commit」で囲む. ("UPDATE ... SET ..." means database update).

「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む. ("UPDATE ... SET ..." means database update.

UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime')
WHERE id = 1;

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

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

# SQLite 3 のデータベースファイル名を DBNAME に設定してください.
# Windows の場合. 「C:」のようなドライブ名を付ける
DBNAME = "C:/SQLite/mydb"
# Linux の場合
# DBNAME = "/var/SQLite/mydb"
DBDIR = File.dirname( DBNAME )
DBBASENAME = File.basename( DBNAME )

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )
# SQL を用いた問い合わせ
sql = <<SQL
UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime')
WHERE id = 1;
SQL
db.transaction do |db| db.execute( sql ) end

db.close

【実行結果の例】

[image]

order_records テーブルの中身を確認 (Inspect the table 'order_table')

[image]

SQL を用いた行の削除 (Delete row(s) using SQL)

ここでは Ruby の中に埋め込まれた SQL を用いた行の削除 (delete row(s))の実行例を示す.

DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である. この SQL 文を 「begin transaction」と「commit」で囲む. ("DELETE FROM ... WHERE ..." means deletion of rows).

DELETE FROM order_records
WHERE id = 2;

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

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

# SQLite 3 のデータベースファイル名を DBNAME に設定してください.
# Windows の場合. 「C:」のようなドライブ名を付ける
DBNAME = "C:/SQLite/mydb"
# Linux の場合
# DBNAME = "/var/SQLite/mydb"
DBDIR = File.dirname( DBNAME )
DBBASENAME = File.basename( DBNAME )

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )
# SQL を用いた問い合わせ
sql = <<SQL
DELETE FROM order_records
WHERE id = 2;
SQL
db.transaction do |db| db.execute( sql ) end

db.close

【実行結果の例】

[image]

order_records テーブルの中身を確認 (Inspect the table 'order_table')

[image]

テーブルの一覧表示

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

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

select * from sqlite_master;
select * from sqlite_temp_master;

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

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

#! ruby -Ks
# coding: windows-31j

require 'pp'
require 'rubygems'
require 'sqlite3'

# SQLite 3 のデータベースファイル名を DBNAME に設定してください.
# Windows の場合. 「C:」のようなドライブ名を付ける
DBNAME = "C:/SQLite/mydb"
# Linux の場合
# DBNAME = "/var/SQLite/mydb"
DBDIR = File.dirname( DBNAME )
DBBASENAME = File.basename( DBNAME )

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )
# SQL を用いた問い合わせ
sql = <<SQL
SELECT * FROM sqlite_master;
SQL
# db.execute(...) do |row| ... end は,結果が複数の行になるときの決まり文句
db.execute(sql) do |row|
    p row
end

db.close

【実行結果の例】

[image]