SQLite3で遊ぶ(2)

昨日は途中で眠っちゃったので、仕切り直し。今日はテーブルの作成(CREATE TABLE)、レコードの挿入(INSERT)、それにSELECTを実行します。それからトランザクションも。
1. テーブルの作成(init.rb)
CREATE TABLEを使ってpeopleというテーブルを作ります。rubyco.dbというファイルが作られます。

# init.rb
require 'sqlite3'

sql = <<'EOD'
  CREATE TABLE people (
     id TEXT PRIMARY KEY
    ,name TEXT
    ,mail TEXT
    ,age INTEGER
  );
EOD
db = SQLite3::Database.new('rubyco.db')
db.execute_batch(sql)

2. レコードの挿入(create1.rb)
SQLite3::Database#execute_batchメソッドを使ってレコードを挿入します。

# create1.rb
require 'sqlite3'

sql = <<'EOD'
INSERT INTO people VALUES ('alice', 'Alice Liddell', 'alice@example.com', 23);
INSERT INTO people VALUES ('bobby', 'Bobby Horton',  'bobby@example.com', 18);
EOD
db = SQLite3::Database.new('rubyco.db')
db.execute_batch(sql)

3. レコードの挿入(create2.rb)
SQLite3::Database#executeメソッドを使ってレコードを挿入します。プレースホルダを使っています。

# create2.rb
require 'sqlite3'

ary = [
  ['chris', 'Chris Davis',   'chris@example.org', 21],
  ['david', 'David',         'david@example.com', 21],
  ['elvis', 'Elvis',         'elvis@example.org', 17],
]
db = SQLite3::Database.new('rubyco.db')
ary.each do |vars|
  db.execute('INSERT INTO people VALUES (?, ?, ?, ?)', *vars)
end

4. レコードの表示(list.rb)

# list.rb
require 'sqlite3'

db = SQLite3::Database.new('rubyco.db')
rows = db.execute('SELECT * FROM people')
rows.each do |record|
  p record
end

5. トランザクション(transaction1.rb)
Rubyのブロックはトランザクションを作るのに最適です。明示的にcommitをする必要はありません。

# transaction1.rb
require 'sqlite3'

f = ['flower', 'Flowery Flower', 'flower@example.com', 15]
g = ['ginger', 'Ginger',         'ginger@example.org', 20]
h = ['henry',  'Henry',          'henry@example.com', 20]

SQLite3::Database.new('rubyco.db').transaction do |db|
  db.execute('INSERT INTO people VALUES (?, ?, ?, ?)', f)
  db.execute('INSERT INTO people VALUES (?, ?, ?, ?)', g)
  db.execute('INSERT INTO people VALUES (?, ?, ?, ?)', h)
end

6. トランザクション(transaction2.rb)
こちらはわざと例外をraiseして、rollbackさせる例です。inge, jill, keithをINSERTしますが、例外が投げられるのでINSERTは「なし」になります。

# transaction2.rb
require 'sqlite3'

ary = [
  ['inge',  'Ingeborg', 'flower@example.com', 15],
  ['jill',  'Jill',     'ginger@example.org', 18],
  ['keith', 'Keith',    'keith@example.com',  19],
  nil # intentional error
]

begin
  SQLite3::Database.new('rubyco.db').transaction do |db|
    ary.each do |vars|
      raise "Intentional Error!" unless vars
      db.execute('INSERT INTO people VALUES (?, ?, ?, ?)', vars)
    end
  end
rescue RuntimeError => e
  puts e
end

7. 以上を組み合わせて実行します。

（テーブルを作ります。ここでrubyco.dbもできます）
C:\work> ruby init.rb

（一覧を見てみましょう。まだレコードはありません）
C:\work> ruby list.rb

（aliceとbobbyをINSERTします）
C:\work> ruby create1.rb

（一覧を見てみましょう。確かにレコードが増えています）
C:\work> ruby list.rb
["alice", "Alice Liddell", "alice@example.com", "23"]
["bobby", "Bobby Horton", "bobby@example.com", "18"]

（chrisとdavidとelvisをINSERTします）
C:\work> ruby create2.rb

（一覧を見てみましょう。確かにレコードが増えています）
C:\work> ruby list.rb
["alice", "Alice Liddell", "alice@example.com", "23"]
["bobby", "Bobby Horton", "bobby@example.com", "18"]
["chris", "Chris Davis", "chris@example.org", "21"]
["david", "David", "david@example.com", "21"]
["elvis", "Elvis", "elvis@example.org", "17"]

（トランザクションを使ってflower, ginger, henryをINSERTします）
C:\work> ruby transaction1.rb

（一覧を見てみましょう。確かにレコードが増えています）
C:\work> ruby list.rb
["alice", "Alice Liddell", "alice@example.com", "23"]
["bobby", "Bobby Horton", "bobby@example.com", "18"]
["chris", "Chris Davis", "chris@example.org", "21"]
["david", "David", "david@example.com", "21"]
["elvis", "Elvis", "elvis@example.org", "17"]
["flower", "Flowery Flower", "flower@example.com", "15"]
["ginger", "Ginger", "ginger@example.org", "20"]
["henry", "Henry", "henry@example.com", "20"]

（トランザクションを使いますが、最後に例外を投げてrollbackします）
C:\work> ruby transaction2.rb
Intentional Error!

（ちゃんとロールバックされていて、レコードは増えていません）
C:\work> ruby list.rb
["alice", "Alice Liddell", "alice@example.com", "23"]
["bobby", "Bobby Horton", "bobby@example.com", "18"]
["chris", "Chris Davis", "chris@example.org", "21"]
["david", "David", "david@example.com", "21"]
["elvis", "Elvis", "elvis@example.org", "17"]
["flower", "Flowery Flower", "flower@example.com", "15"]
["ginger", "Ginger", "ginger@example.org", "20"]
["henry", "Henry", "henry@example.com", "20"]