2020 年度 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>

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

1st Step

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

$ vi 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

$ 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

$ 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

$ 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

$ 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 の部分は自分のユーザアカウントに修正すること.

$ 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 を自分のアカウントに変えてアクセスしてみよ.

課題 (1)

Ruby スクリプト内で, (1) テーブルへのデータの挿入, (2) テーブル中の全データの表示, (3) テーブル中の一部データの表示 (選択 or 射影) を行いなさい. (2), (3) は 1 つの HTML ファイルとして出力すること. なお, HTML のタグや css を積極的に利用して, 人が読みやすい Web となるように出力を工夫しなさい.
- 利用するテーブルは任意とする. これまで作ったテーブルの中から選ぶと良い.
- 挿入する行数は 5 行以上とすること.
- 提出物：作成した Ruby スクリプト. HTML ファイルをブラウザで表示したもの (スクリーンショット)

Appendix

以下は参考資料. 2020 年度の演習では行う必要は無い.

Web サーバの準備

インストール

Web サーバとして Apache2 を利用する. Debian パッケージを利用してインストールする.

$ sudo -s

# apt-get update

# apt-get install apache2

apache2 パッケージをインストールすると自動的に Web サーバが起動する. この段階では apache2 の設定ファイルを変更する必要はない.

ブラウザで自分の IP にアクセスしてみよ. 以下のような URL をブラウザに打ち込めば良い.

http://10.176.0.XXX (XXX は自分の IP に)

上記のデフォルトページに書かれているように, 設定ファイルは /etc/apache2 以下に存在する. デフォルトのドキュメントルートは /etc/apache2/sites-enabled/000-default.conf に書かれている. ファイルを見るとドキュメントルートが /var/www/html であることがわかる.

# less /etc/apache2/sites-enabled/000-default.conf 

  ...(略)...
  DocumentRoot /var/www/html
  ...(略)...

そのため, /var/www/html 以下に HTML ファイルを置けば, ブラウザ上から閲覧することが可能となる.

Apache2 の設定ファイルとユーザディレクトリの有効化

Linux のディストリビューション毎に設定ファイルの置き方に多少の違いがあるが, Debian 系の Linux では設定ファイルは /etc/ の下にあるパッケージ名のディレクトリに置かれている.

# ls -l /etc/apache2/

  合計 80
  -rw-r--r-- 1 root root  7224 11月  4 03:46 apache2.conf
  drwxr-xr-x 2 root root  4096 12月  4 23:52 conf-available
  drwxr-xr-x 2 root root  4096 12月  4 23:52 conf-enabled
  -rw-r--r-- 1 root root  1782 11月  3 20:34 envvars
  -rw-r--r-- 1 root root 31063 11月  3 20:34 magic
  drwxr-xr-x 2 root root 12288 12月 10 15:33 mods-available
  drwxr-xr-x 2 root root  4096 12月 10 15:33 mods-enabled
  -rw-r--r-- 1 root root   320 11月  3 20:34 ports.conf
  drwxr-xr-x 2 root root  4096 12月  4 23:52 sites-available
  drwxr-xr-x 2 root root  4096 12月  4 23:52 sites-enabled

ここで, conf-available と conf-enabled, mods-available と mods-enabled, sites-available と sites-enabled のセットがあることに気づくだろう. それぞれ以下のような意味がある.

conf-      : 諸々の設定ファイル 
mods-      : モジュール
sites-     : サイトの設定 (root ディレクトリやサイト名, など)

-available : 利用可能なもの. 
-enabled   : 有効になっているもの.

モジュールなどを有効・無効にするために, 以下のコマンドが用意されている.

a2enmod <module 名>
a2dismod <module 名>

a2enconf <conf 名>
a2disconf <conf 名>

a2ensite <site 名>
a2dissite <site 名>

ユーザディレクトリを有効化するには, 上述の a2enmod コマンドで Apache2 の userdir モジュールを有効にするばよい. userdir モジュールを有効にすると, 各ユーザは自分の権限で Web ページを公開できるようになる (そうでなければ, 常に root 権限で /var/www/html/ 以下にファイルを置かねばならない). 例えば, <URL:http://10.176.0.199/~hogehoge/> のように, http://サイト名/~ユーザ名/ で各ユーザの資源を公開できる.

# a2enmod userdir

# /etc/init.d/apache2 restart  (apache2 の再起動)

  [ ok ] Restarting apache2 (via systemctl): apache2.service.

# exit

$

Web ページを作成するためには, ホームディレクトリ以下に public_html という名前のディレクトリを作成し, その中にファイルを置けば良い.

$ mkdir ~/public_html

$ cd ~/public_html

$ vi helloworld.htm

  <html>
    <body>
      Hello World!
    </body>
  </html>

作成が終わったら, 自分の仮想マシンに接続し, helloworld.htm を表示してみよ.

Ruby から MySQL を利用するためのパッケージのインストール

データベースを扱うために, Ruby の mysql2 ライブラリおよび ActiveRecord をインストールする. 今回は Debian パッケージを利用する (最新版を使いたい場合は, gem を使ってインストールする方が良いだろう).

$ cd ~/  (ホームディレクトリに移動)

$ sudo -s

# apt-get update

# apt-get install ruby-mysql2 ruby-activerecord

# exit

$

Active Record の利用

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

1st Step

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

$ vi db2.rb

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

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

  # テーブルにアクセスするためのクラスを宣言
  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.rb

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

$ ruby db2.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.rb の表示部分を書き直す.

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

$ ruby db2.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.rb を改良する.

$ vi db2.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.rb を実行すると HTML のソースが表示される.

$ ruby db2.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.rb を書き換える.

$ vi db2.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.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db2.rb

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

5th Step

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

$ vi db_info.rb

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

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

$ vi db2.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.rb を実行すると 3rd Step と同様の結果が得られる.

$ ruby db2.rb

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

6th Step

db2.rb を編集して, 出力を HTML ファイルとして書き出すようにする. まず始めに, HTML ファイルを格納するディレクトリを作成する.

$ mkdir ~/public_html

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

$ vi db2.rb

  ...(略)...

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

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

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

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

$ ruby db2.rb 

$ ls ~/public_html

  db1.htm db2.htm  helloworld.htm

Windows のブラウザで db2.htm を閲覧してみよ. http://10.176.0.XXX/~hogehoge/ の XXX の部分を自分の VM の IP に変更してアクセスしてみよ.