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

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

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

前準備

必要となるソフトウェア

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

• Ruby のインストールと sqlite3-ruby パッケージのインストール が済んでいること

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

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

• データベースファイル名:

  C:/SQLite/mydb (Windows の場合)
  /var/SQLite/mydb (Linux の場合)
  

  データベースファイルは，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

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のデータ型の方がより大きな範囲のデータを扱える．

   • NULL: 空値 (a NULL value)
   • INTEGER: 符号付きの整数 (signed integer) ※ SQLite では BIGINT と書いても INTEGER と書いても同じ「８バイトの整数」という意味
   • REAL: 浮動小数点値 (floating point value)
   • TEXT: 文字列 (text string) ※ SQLite では DATETIME と書いても TEXT と書いても同じ「可変長文字列」の意味．
   • BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object). 入力がそのままの形で格納される (stored exactly as it was input)． * SQLite では LARGEBLOB と書いても BLOB と書いても同じ「長大なバイナリ・ラージ・オブジェクト」という意味
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 の中に含める一貫性制約やデフォルト値の指定

   • PRIMARY KEY ・・・ 主キー制約
   • not null ・・・ 非空制約
   • UNIQUE ・・・ 一意制約
   • CHECK (<expression>) ・・・ 更新時にチェックされる式 ( expression) ※ SQLite 固有の機能
   • REFERENCES <foreign-table> (<column-name>, ...) ・・・ 参照整合性制約
   • DEFAULT (<expression>) ・・・ デフォルト値 (default value) の指定
   • autoincrement ・・・ 自動インクリメント (auto increment)
5. SQL テーブル制約 (table-constraint) の例

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

   • PRIMARY KEY(<indexed-column>, ...)
   • UNIQUE(<indexed-column>, ...)
   • CHECK(<expression>)
6. SQL 挿入文 (insert statement) の例

   テーブルへの行の挿入

   • insert into <table-name> values (<expression>, ...);
   • insert into <table-name> (<column-name>, ...) values ( <expression>, ...);
7. begin transaction, commit, ROLLBACK

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

   • commit: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を確定したいとき
   • ROLLBACK: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を破棄したいとき
8. SQL の SELECT, FROM, WHERE の例

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

   • SELECT * FROM <table-name>;

     table-name で指定したテーブルの全ての行を表示

   • SELECT * FROM <table-name> WHERE <expression>;

     table-name で指定したテーブルのうち expression で指定した条件を満足する行だけを抽出して表示

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 の流儀に習って，

• テーブル名は複数形の名詞にする
• テーブルには「id」という名前が付いた属性 を含めることする． また id のデータ型は「integer primary key autoincrement not null」に設定する．

プログラムの例

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

【実行結果の例】

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

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

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

• db.execute_batch(...) は複数の SQL をi度に実行するためのもの
• db.transaction do ... end はトランザクション開始の決まり文句．正常終了すれば自動的にコミットし，異常終了の場合には自動的にロールバックされる．

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

【実行結果の例】

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

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 ページで説明する．ここでは，テーブルの中身を確認して欲しい．

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

  SELECT * FROM order_records;
  

  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 order_records;
  SQL
  # db.execute(...) do |row| ... end は，結果が複数の行になるときの決まり文句
  db.execute(sql) do |row|
      p row
  end
  
  db.close
  

  【実行結果の例】

  
• 条件に合致する行のみの表示 (その 1)

  SELECT * FROM order_records WHERE product_name = 'orange A';
  

  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 order_records WHERE product_name = 'orange A';
  SQL
  # db.execute(...) do |row| ... end は，結果が複数の行になるときの決まり文句
  db.execute(sql) do |row|
      p row
  end
  
  db.close
  

  【実行結果の例】

  
• 条件に合致する行のみの表示 (その 2)

  SELECT * FROM order_records WHERE unit_price > 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
  SELECT * FROM order_records WHERE unit_price > 2;
  SQL
  # db.execute(...) do |row| ... end は，結果が複数の行になるときの決まり文句
  db.execute(sql) do |row|
      p row
  end
  
  db.close
  

  【実行結果の例】

  

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

【実行結果の例】

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

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

【実行結果の例】

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

テーブルの一覧表示

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

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

データベーススキーマを見たいときは，次のような 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

【実行結果の例】