2021 年度 DB 技術 : SQL の利用 (1)

はじめに

本演習では, Ruby スクリプトから MySQL サーバにアクセスし, テーブルの情報を Web ブラウザから閲覧するための HTML ファイルを作成する.

今回の演習で利用する Ruby のライブラリ.

mysql2 <URL:https://rubygems.org/gems/mysql2/>
- 右カラムに「ドキュメント」内に "Usage (使い方)" 有り.
Active Record <URL:https://github.com/rails/rails/tree/master/activerecord>
- Active Record の基礎 - Rails ガイド <URL:https://railsguides.jp/active_record_basics.html>
ERB (埋め込み Ruby) [標準添付ライブラリ]
- 使い方：<URL:https://magazine.rubyist.net/articles/0017/0017-BundledLibraries.html>

サンプルファイル

以下で述べる 1st Step から 6th Step までのサンプルスクリプトをサーバ上に用意している．それを活用して構わない．

mysql2 のサンプル

  $ ls ~sugiyama/sample1

    db1-1.rb  db1-2.rb  db1-3.rb  db1-4.rb  db1-5.rb  db1-6.rb  db_info.yml 

ActiveRecord のサンプル

  $ ls ~sugiyama/sample2

    db2-1.rb  db2-2.rb  db2-3.rb  db2-4.rb  db2-5.rb  db2-6.rb  db_info.yml

例えば以下のようにして，自分のディレクトリにコピーして使うことができる．

mysql2 の 1st Step のサンプルを使う場合:

  $ cp ~sugiyama/sample1/db1-1.rb .

また，各ステップでの差分は，diff コマンドで調べることができる．-c オプションを付けて diff コマンドを実行した場合には，互いに異なる行の先頭に "!" が付く.

$ diff -c db1-1.rb db1-2.rb 

   2020-07-26 23:05:38.398766808 +0900
   2020-07-26 23:05:13.938921563 +0900
   ***************
   *** 12,16 ****

     sql = "SELECT * FROM 商品"
     client.query( sql ).each do |item|
  !    p item     # 各レコードが `{ key => value}` 形式のオブジェクトになっている
     end
   --- 12,17 ----

    sql = "SELECT * FROM 商品"
    client.query( sql ).each do |item|
  !   # p item
  !   puts "#{item["商品番号"]}\t#{item["商品名"]}\t#{item["価格"]}"
    end
  --- db1-2.rb*** db1-1.rb

mysql2 ライブラリを利用する場合

作業ディレクトリ (今回は，sample1) を作成し，そこで作業することにする．

$ mkdir ~/sample1

$ cd ~/sample1

1st Step

テーブル「商品」に含まれるデータを表示する ruby プログラムを作成する. 以下の内容を db1-1.rb としてホームディレクトリ以下に保存する. なお, ユーザ名とパスワードは適宜修正すること.

$ vi db1-1.rb    (もしくは, $ cp ~sugiyama/sample1/db1-1.rb . ) 

  #!/usr/bin/env ruby
  # coding: utf-8
  require 'mysql2'

  # データベースへの接続
  client = Mysql2::Client.new(
    :host     => "localhost",
    :username => "******",    # <= 自分のユーザ名
    :password => "******",    # <= 自分のパスワード
    :database => "******"     # <= 自分のデータベース名 (学生番号)
  )

  sql = "SELECT * FROM 商品"
  client.query( sql ).each do |item|
    p item     # 各レコードが `{ key => value}` 形式のオブジェクトになっている
  end

このプログラムにはパスワードが書かれているので, 最低限の作法として, 他人に読めないようパーミッションを設定する必要がある.

$ chmod 600 db1-1.rb

db1-1.rb を実行すると以下のような出力を得る. テーブルの 1 行 1 行がハッシュとして保存されていることがわかる.

$ ruby db1-1.rb

  {"商品番号"=>"A01", "商品名"=>"オフィス用紙 A4", "価格"=>2000}
  {"商品番号"=>"A02", "商品名"=>"オフィス用紙 A3", "価格"=>4000}
  {"商品番号"=>"A03", "商品名"=>"オフィス用紙 B5", "価格"=>1500}
  {"商品番号"=>"B01", "商品名"=>"トナーカートリッジ黒", "価格"=>25000}
  {"商品番号"=>"C01", "商品名"=>"ホワイトボード", "価格"=>14000}
  {"商品番号"=>"X00", "商品名"=>"ノート", "価格"=>120}
  {"商品番号"=>"X01", "商品名"=>"テープ", "価格"=>100}
  {"商品番号"=>"Y01", "商品名"=>"はさみ", "価格"=>100}
  {"商品番号"=>"A04", "商品名"=>"紙", "価格"=>300}

2nd Step

1st Step で, 取り出したデータがハッシュの形で保管されていることがわかったので, 次にハッシュを使って db1-1.rb の表示部分を書き直す.

$ cp db1-1.rb db1-2.rb  (もしくは, $ cp ~sugiyama/sample1/db1-2.rb . ) 

$ chmod 600 db1-2.rb

$ vi db1-2.rb

  ...(略)...

  sql = "SELECT * FROM 商品"
  client.query( sql ).each do |item|
    # p item
    puts "#{item["商品番号"]}\t#{item["商品名"]}\t#{item["価格"]}"
  end

db1-2.rb を実行すると以下のような出力を得る. ハッシュのキーとしてカラム名(属性名)を与えることで, その値が得られることがわかる.

$ ruby db1-2.rb

  A01   オフィス用紙 A4   2000
  A02   オフィス用紙 A3   4000
  A03   オフィス用紙 B5   1500
  B01   トナーカートリッジ黒   25000
  C01   ホワイトボード    14000
  X00   ノート   120
  X01   テープ   100
  Y01   はさみ   100
  A04   紙       300

3rd Step

puts 関数を用いてテーブル「商品」を HTML 形式で出力してみる. db1-2.rb を db1-3.rb としてコピーし, 以下のように修正する.

$ cp db1-2.rb db1-3.rb   (もしくは, $ cp ~sugiyama/sample1/db1-3.rb . ) 

$ chmod 600 db1-3.rb

$ vi db1-3.rb

  ...(略)...
  sql = "SELECT * FROM 商品"

  puts "<html>"
  puts "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">"
  puts "<body>"
  puts "<table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>"
  client.query( sql ).each do |item|
    puts "<tr><td>#{item["商品番号"]}</td><td>#{item["商品名"]}</td><td>#{item["価格"]}</td></tr>"
  end
  puts "</table>"
  puts "</body></html>"

db1-3.rb を実行すると HTML のソースが表示される.

$ ruby db1-3.rb

  <html>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <body>
  <table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>
  <tr><td>A01</td><td>オフィス用紙 A4</td><td>2000</td></tr>
  <tr><td>A02</td><td>オフィス用紙 A3</td><td>4000</td></tr>
  <tr><td>A03</td><td>オフィス用紙 B5</td><td>1500</td></tr>
  <tr><td>B01</td><td>トナーカートリッジ黒</td><td>25000</td></tr>
  <tr><td>C01</td><td>ホワイトボード</td><td>14000</td></tr>
  <tr><td>X00</td><td>ノート</td><td>120</td></tr>
  <tr><td>X01</td><td>テープ</td><td>100</td></tr>
  <tr><td>Y01</td><td>はさみ</td><td>100</td></tr>
  <tr><td>A04</td><td>紙</td><td>300</td></tr>
  </table>
  </body></html>

4th Step

3rd Step の段階では, puts 命令が多すぎてプログラムが読みにくい. このような場合には ERB を用いてヒアドキュメントに Ruby スクリプトを埋め込むと良い. ERB の説明は例えば, <URL:https://magazine.rubyist.net/articles/0017/0017-BundledLibraries.html> を参照すると良い.

ERB では埋め込む際に以下のタグを用いる.

<% … %>
Ruby スクリプト片をその場で実行

<%= … %>
式を評価した結果をその場に挿入

db1-3.rb を db1-4.rb としてコピーし, db1-4.rb を ERB を用いて以下のように書き直す. ヒアドキュメント内の HTML に <%...%>, <%=...%> の形で ruby スクリプトが埋め込まれているのがわかると思う. なお, ヒアドキュメントでは << を使う. << の後ろにヒアドキュメントの始まりと終わりを示す文字列の識別子を書く (<< と識別子の間に空白を入れない). EOS（End Of String）とEOL（End Of Line）が使われている例が多いが, 統一されていれば何を使っても問題ない.

$ cp db1-3.rb db1-4.rb   (もしくは, $ cp ~sugiyama/sample1/db1-4.rb . ) 

$ chmod 600 db1-4.rb

$ vi db1-4.rb

  require 'erb'
  require 'mysql2'

  # データベースへの接続  
  ...(省略. 1st Step 参照)...

  #SQL の実行と埋め込み
  sql = "SELECT * FROM 商品"

  contents = <<EOS
  <html>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <body>
  <table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>
  <% client.query( sql ).each do |item| %>
  <tr> 
    <td><%= item["商品番号"] %></td><td><%= item["商品名"] %></td><td><%= item["価格"] %></td>
  </tr>
  <% end %>
  </table>
  </body></html>
  EOS

  # おまじない
  erb = ERB.new(contents)
  puts erb.result(binding)

db1-4.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db1-4.rb 

  ... 出力は省略 ...

5th Step

アカウント・パスワードの情報は, ファイル内に書いておくのではなく, 別ファイルに分けて外部から参照・実行できない場所に保存するのが良い. ここでは db_info.yml に以下のような YAML 形式でアカウント・パスワードの情報を保管する.

$ vi db_info.yml

  SERV: "localhost"
  USER: "******"
  PASS: "******"
  DBNM: "******"

このファイルにはパスワードが書かれているので, 最低限の作法として, 他人に読めないようパーミッションを設定する必要がある.

$ chmod 600 db_info.yml

次に db1-4.rb を db1-5.rb としてコピーし, db1-5.rb を以下のように変更する.

$ cp db1-4.rb db1-5.rb    (もしくは, $ cp ~sugiyama/sample1/db1-5.rb . ) 

$ vi db1-5.rb

  require 'erb'
  require 'mysql2'   
  require 'yaml'

    ...(略)...

  # 設定ファイルの読み込み
  mydb = YAML.load_file( "db_info.yml" )

  # データベースへの接続
  client = Mysql2::Client.new(
    :host => "#{mydb["SERV"]}",
    :username => "#{mydb["USER"]}",
    :password => "#{mydb["PASS"]}",
    :database => "#{mydb["DBNM"]}"
  )    

  ...(略)...

db1-5.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db1-5.rb 

  ... 出力は省略 ...

6th Step

これまでは全て標準出力に出力してきたが, このステップではHTML ファイルに出力することにする. まずはじめに, HTML ファイルを格納するためのディレクトリを作る.

$ mkdir ~/public_html

db1-5.rb を db1-6.rb としてコピーし, db1-6.rb を編集して出力を HTML ファイルとして書き出すようにする. なお, ファイルパスの HOGEHOGE の部分は自分のユーザアカウントに修正すること.

$ cp db1-5.rb  db1-6.rb   (もしくは, $ cp ~sugiyama/sample1/db1-6.rb . ) 

$ vi db1-6.rb

  ...(略)...

  #ファイルオープン
  # HOGEHOGE の部分を自分のユーザ名に書き換えること 
  fp = open("/home/HOGEHOGE/public_html/db1.htm", "w")

  # おまじない 
  erb = ERB.new(contents)
  fp.puts erb.result(binding)

  #ファイルクローズ
  fp.close

db1-6.rb を実行すると, ~/public_html 以下に新たに db1.htm というファイルが出来ていることがわかる.

$ ruby db1-6.rb 

$ ls ~/public_html

  db1.htm

確認のためにブラウザで db1.htm を閲覧してみよ. https://pluto.epi.it.matsue-ct.jp/~HOGEHOGE/db1.htm の HOGEHOGE を自分のアカウントに変えてアクセスしてみよ.

Active Record の利用

ActiveRecord は Ruby on Rails 標準の O/R (object/Relational) マッパーである. SQL を意識せずに, オブジェクト指向的にデータベースを扱うことができる. Active Record 単体でも利用することができる.

作業ディレクトリ (今回は，sample2) を作成し，そこで作業することにする．

$ mkdir ~/sample2

$ cd ~/sample2

1st Step

ActiveRecord を用いた例として, 以下の内容を db2-1.rb として保存する. 但し, ユーザ名, パスワード, データベース名を自分の環境に合わせて適宜修正すること.

$ vi db2-1.rb    (もしくは, $ cp ~sugiyama/sample2/db2-1.rb . ) 

  #!/usr/bin/env ruby
  # coding: utf-8
  require 'active_record'

  # DB接続設定
  ActiveRecord::Base.establish_connection(
    adapter:  "mysql2",
    host:     "localhost",
    username: "xxxxxx",
    password: "xxxxxx",
    database: "xxxxxx",
  )

  # テーブルにアクセスするためのクラスを宣言
  class User < ActiveRecord::Base
    self.table_name = '商品'
  end

  # レコード取得
  User.all.each do |item|
    p item
  end

  # 検索 (射影)
  User.select('商品番号').each do |item|
    p item
  end

  # 検索 (選択)
  User.where(商品番号: 'A01').each do |item|
    p item
  end

このプログラムにはパスワードが書かれているので, 最低限の作法として, 他人に読めないようパーミッションを設定する必要がある.

$ chmod 600 db2-1.rb

db3.rb を実行すると以下のようになる. SQL 文を直接書かなくてもデータベース操作ができていることがわかる.

$ ruby db2-1.rb

  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>
  #<User 商品番号: "A02", 商品名: "オフィス用紙 A3", 価格: 4000>
  #<User 商品番号: "A03", 商品名: "オフィス用紙 B5", 価格: 1500>
  #<User 商品番号: "B01", 商品名: "トナーカートリッジ黒", 価格: 25000>
  #<Usear 商品番号: "C01", 商品名: "ホワイトボード", 価格: 14000>
  #<User 商品番号: "X00", 商品名: "ノート", 価格: 120>
  #<User 商品番号: "X01", 商品名: "テープ", 価格: 100>
  #<User 商品番号: "Y01", 商品名: "はさみ", 価格: 100>
  #<User 商品番号: "A04", 商品名: "紙", 価格: 300>
  #<User 商品番号: "A01">
  #<User 商品番号: "A02">
  #<User 商品番号: "A03">
  #<User 商品番号: "B01">
  #<User 商品番号: "C01">
  #<User 商品番号: "X00">
  #<User 商品番号: "X01">
  #<User 商品番号: "Y01">
  #<User 商品番号: "A04">
  #<User 商品番号: "A01", 商品名: "オフィス用紙 A4", 価格: 2000>

2nd Step

1st Step で, 取り出したデータがハッシュの形で保管されていることがわかったので, 次にハッシュを使って db2-1.rb の表示部分を書き直す.

$ cp db2-1.rb db2-2.rb   (もしくは, $ cp ~sugiyama/sample2/db2-2.rb . ) 

$ chmod 600 db2-2.rb

$ vi db2-2.rb

  ...(略)...

  User.all.each do |item|
     # puts item
     puts "#{item["商品番号"]}\t#{item["商品名"]}\t#{item["価格"]}"
  end

  ## 検索 (射影)
  #User.select('商品番号').each do |item|
  #  p item
  #end

  ## 検索 (選択)
  #User.where(商品番号: 'A01').each do |item|
  #  p item
  #end

db2-2.rb を実行すると以下のような出力が得られる. ハッシュのキーとしてカラム名(属性名)を与えることで, その値が得られることがわかる.

$ ruby db2-2.rb

  A01   オフィス用紙 A4   2000
  A02   オフィス用紙 A3   4000
  A03   オフィス用紙 B5   1500
  B01   トナーカートリッジ黒   25000
  C01   ホワイトボード    14000
  X00   ノート   120
  X01   テープ   100
  Y01   はさみ   100
  A04   紙       300

3rd Step

前節と同様に puts 関数を使って HTML のソースを出力できるように db2-2.rb を改良する.

$ cp db2-2.rb db2-3.rb    (もしくは, $ cp ~sugiyama/sample2/db2-3.rb . ) 

$ chmod 600 db2-3.rb

$ vi db2-3.rb

  ...(省略)...

  puts "<html>"
  puts "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">"
  puts "<body>"
  puts "<table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>"
  User.all.each do |item|
    puts "<tr><td>#{item["商品番号"]}</td><td>#{item["商品名"]}</td><td>#{item["価格"]}</td></tr>"
  end
  puts "</table>"
  puts "</body></html>"

db2-3.rb を実行すると HTML のソースが表示される.

$ ruby db2-3.rb

  <html>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <body>
  <table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>
  <tr><td>A01</td><td>オフィス用紙 A4</td><td>2000</td></tr>
  <tr><td>A02</td><td>オフィス用紙 A3</td><td>4000</td></tr>
  <tr><td>A03</td><td>オフィス用紙 B5</td><td>1500</td></tr>
  <tr><td>B01</td><td>トナーカートリッジ黒</td><td>25000</td></tr>
  <tr><td>C01</td><td>ホワイトボード</td><td>14000</td></tr>
  <tr><td>X00</td><td>ノート</td><td>120</td></tr>
  <tr><td>X01</td><td>テープ</td><td>100</td></tr>
  <tr><td>Y01</td><td>はさみ</td><td>100</td></tr>
  <tr><td>A04</td><td>紙</td><td>300</td></tr>
  </table>
  </body></html>

4th Step

ここでは前節にならって ERB を用いるように db2-3.rb を書き換える. プログラムの先頭で require 'erb' をするのを忘れないこと.

$ cp db2-3.rb  db2-4.rb    (もしくは, $ cp ~sugiyama/sample2/db2-4.rb . ) 

$ chmod 600 db2-4.rb

$ vi db2-4.rb

  require 'active_record'
  require 'erb'

  # データベースへの接続  
  ...(省略. 1st Step 参照)...

  contents = <<EOS
  <html>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <body>
  <table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>
  <% User.all.each do |item| %>   
  <tr> 
    <td><%= item["商品番号"] %></td><td><%= item["商品名"] %></td><td><%= item["価格"] %></td>
  </tr>
  <% end %>
  </table>
  </body></html>
  EOS

  # おまじない
  erb = ERB.new(contents)
  puts erb.result(binding)

db2-4.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db2-4.rb

  ...(出力は省略)...

5th Step

前節と同様に, アカウント・パスワードの情報を別ファイルに置く. db_info.yml にアダプタの情報 (ADPT) を追加する.

$ vi db_info.yml

  SERV: "localhost"
  USER: "******"
  PASS: "******"
  DBNM: "******"
  ADPT: "mysql2"

このファイルにはパスワードが書かれているので, 最低限の作法として, 他人に読めないようパーミッションを設定する必要がある.

$ chmod 600 db_info.yml

また, db2-4.rb を以下のように変更する.

$ cp db2-4.rb db2-5.rb    (もしくは, $ cp ~sugiyama/sample2/db2-5.rb . ) 

$ vi db2-5.rb

  require 'active_record'
  require 'erb'
  require 'yaml'

  # 設定ファイルの読み込み
  mydb = YAML.load_file( "db_info.yml" )

  # DB接続設定
  ActiveRecord::Base.establish_connection(
    adapter:  mydb["ADPT"],
    host:     mydb["SERV"],
    username: mydb["USER"],
    password: mydb["PASS"],
    database: mydb["DBNM"]
  )

  ...(略)...

db2-5.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db2-5.rb

  ...(出力は省略)...

6th Step

db2-5.rb を編集して, 出力を HTML ファイルとして書き出すようにする. まず始めに, HTML ファイルを格納するディレクトリが存在するか確認し，もし存在しない場合は以下のように public_html ディレクトリを作成する.

$ mkdir ~/public_html

db2-5.rb を編集する. なお, ファイルパスの hogehoge の部分は自分のユーザアカウントに修正すること. 出力ファイル名は db2.htm にすること.

$ cp db2-5.rb db2-6.rb     (もしくは, $ cp ~sugiyama/sample2/db2-6.rb . ) 

$ vi db2-6.rb

  ...(略)...

  #ファイルオープン
  fp = open("/home/hogehoge/public_html/db2.htm", "w")

  # おまじない 
  erb = ERB.new(contents)
  fp.puts erb.result(binding)

  #ファイルクローズ
  fp.close

db2-6.rb を実行すると, ~/public_html 以下に新たに db2.htm というファイルが出来ていることがわかる.

$ ruby db2-6.rb 

$ ls ~/public_html

  db1.htm db2.htm  helloworld.htm

ブラウザで db2.htm を閲覧してみよ. https://pluto.epi.it.matsue-ct.jp/~HOGEHOGE/db2.htm の HOGEHOGE を自分のアカウントに変えてアクセスしてみよ.

課題

Ruby スクリプト内で, (1) テーブルへのデータの挿入, (2) テーブル中の全データの表示, (3) テーブル中の一部データの表示 (選択 or 射影) を行いなさい. (2), (3) の操作の結果を 1 つの HTML ファイルとして出力すること. なお, HTML のタグや css を積極的に利用して, 人が読みやすい Web となるように出力を工夫しなさい.
- mysql2 か Active Record のどちらか好きな方を使うこと.
- 利用するテーブルは任意とする. これまで作ったテーブルの中から選ぶと良い.
- 挿入する行数は 5 行以上とすること.
- 提出物：作成した Ruby スクリプト. HTML ファイルをブラウザで表示したもの (スクリーンショット). 但し，スクリーンショットには必ずブラウザのアドレスバー (http://pluto.epi.it.matsue-ct.jp/~jXXXX/YYY.htm といった文字列が表示されている部分) を含めること．