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

環境構築

$ sudo apt update
$ sudo apt upgrade

Ruby mysql2, activerecord ライブラリのインストール

$ sudo apt install libmysqld-dev ruby-mysql2 ruby-activerecord

apache2 のインストール

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

$ sudo apt install apache2 
$ sudo a2enmod userdir
$ sudo systemctl restart apache2

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

作業ディレクトリを作成し，そこで作業することにする．

$ cd ~/

$ wget https://www.gfd-dennou.org/arch/iotex/oss/IoTeX_2023/j4db-sample-1.tar.gz

$ tar zxvf j4db-sample-1.tar.gz

$ cd ~/j4db-sample-1

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 文でテーブルの中身を表示する SQL 文を書く
  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-2.rb の表示部分を書き直す. puts 文のところで，ハッシュ item のキーワードとして属性名を指定すること．

$ vi db1-2.rb

  ...(略)...

  sql = "*****************"    # select 文でテーブルの中身を表示する SQL 文を書く
  client.query( sql ).each do |item|
    # p item
    puts "#{item["<key1>"]}\t#{item["<key2>"]}\t#{item["<key3>"]}"  # "<key1>", "<key2>", "<key3>" に属性名を指定する
  end

修正後に db1-2.rb を実行すると以下のような出力を得る. 以下の例はテーブルとして「商品」を与え，item["<key>"] の key の部分に商品テーブルのカラム名(属性名)を与えた場合の例である．

$ chmod 600 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

Active Record の利用

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

作業ディレクトリ (今回は，j4db-sample-1) に移動して作業する．

$ cd ~/j4db-sample-1

1st Step

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

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

$ chmod 600 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-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>"
  ########################
  ###ここにソースを書く###
  ########################
  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

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

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

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

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

サンプルの db2-4.rb では ERB を用いて db2-3.rb を以下のように書き直す. ヒアドキュメント内の HTML に <%...%>, <%=...%> の形で ruby スクリプトが埋め込んでみよ．プログラムの先頭で require 'erb' をするのを忘れないこと.

なお, ヒアドキュメントでは << を使う. << の後ろにヒアドキュメントの始まりと終わりを示す文字列の識別子を書く (<< と識別子の間に空白を入れない). EOS（End Of String）とEOL（End Of Line）が使われている例が多いが, 統一されていれば何を使っても問題ない.

$ 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>
  ########################
  ###ここにソースを書く###
  ########################
  </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 に以下のような YAML 形式でアカウント・パスワードの情報を保管する.

$ vi db_info.yml

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

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

$ chmod 600 db_info.yml

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

$ chmod 600 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"]
  )
  ...(略)...

  # レコード取得・表示                                    
  contents = <<EOS                                        
  <html>                                                  
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <body>                                                    
  <table><tr><th>商品番号</th><th>商品名</th><th>価格</th></tr>                                                      
  ##########################################                
  ###ここにソースを書く (db2-4.rb と同じ)###                
  ##########################################                
  </table>                                                  
  </body></html>                                            
  EOS
  ...(略)...

db2-5.rb を実行すると 4th Step (db2-4.rb) と同様の結果が得られることを確認すること.

$ ruby db2-5.rb

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

6th Step

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

$ mkdir ~/public_html

db2-5.rb を編集するので，コピーを作る．

$ cp db2-5.rb db2-6.rb

db2-6.rb のファイル末尾を以下のように修正する．なお, ファイルパスの hogehoge の部分は自分のユーザアカウントに修正すること. 出力ファイル名は db2-6.htm にすること.

$ vi db2-6.rb

  ...(略)...

  #ファイルオープン
  fp = open("/home/hogehoge/public_html/db2-6.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

  db2-6.htm

ブラウザで db2-6.htm を閲覧してみよ (http://10.176.0.XX/~hogehoge/db2-6.htm)． 10.176.0.XX と hogehoge の部分は自分の VM に合わせること．

課題

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