2022 年度 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) [標準添付ライブラリ]
環境構築
$ sudo -s $ sudo apt update $ sudo apt upgrade $ sudo apt-get install -y libssl-dev libreadline-dev zlib1g-dev $ sudo apt install autoconf bison build-essential libssl-dev libyaml-dev libreadline-dev zlib1g-dev libncurses5-dev libffi-dev libgdbm-dev libgdbm-compat-dev libdb-dev sqlite3 libsqlite3-dev
Ruby のインストール
ここでは複数のRubyをシステム内に共存させるためのツールrbenvを使用します。
$ cd $HOME $ git clone https://github.com/rbenv/rbenv.git $HOME/.rbenv
パスを通す
$ echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> $HOME/.bashrc $ echo 'eval "$(rbenv init -)"' >> $HOME/.bashrc $ source .bashrc
ruby-build をインストール．
$ mkdir -p "$(rbenv root)"/plugins $ git clone https://github.com/rbenv/ruby-build.git "$(rbenv root)"/plugins/ruby-build
Ruby のバージョンは 3.1.0 にすること．インストールには 10 分くらいかかる．
$ rbenv install 3.1.0 $ rbenv global 3.1.0 $ ruby -v ruby 3.1.0p0 (2021-12-25 revision fb4df44d16) [x86_64-linux]
Rails のインストール
$ gem i rails $ rails -v Rails 7.0.3
NodeJS, npm, Yarn のインストール
NodeJS は NodeSource で公開されているリポジトリを利用する．この nodejs パッケージには npm が含まれている．
$ sudo -s # apt install curl # curl -fsSL https://deb.nodesource.com/setup_lts.x | bash - # apt-get install -y nodejs # npm install -g yarn # exit
Ruby mysql2 ライブラリのインストール
$ sudo apt install libmysqld-dev $ gem install mysql2 -v 0.5.3
apache2 のインストール
Web サーバとして Apache2 を利用する. Debian パッケージを利用してインストールする.
$ sudo -s # apt-get install apache2 # a2enmod userdir # /etc/init.d/apache2 restart # exit
mysql2 ライブラリを利用する場合
作業ディレクトリを作成し，そこで作業することにする．
$ cd ~/ $ wget https://www.gfd-dennou.org/arch/iotex/oss/IoTeX_2022/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 スクリプトが埋め込まれているのがわかると思う. なお, ヒアドキュメントでは << を使う. << の後ろにヒアドキュメントの始まりと終わりを示す文字列の識別子を書く (<< と識別子の間に空白を入れない). EOS（End Of String）とEOL（End Of Line）が使われている例が多いが, 統一されていれば何を使っても問題ない. なお，プログラムの先頭で require 'erb' をするのを忘れないこと.
$ 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 といった文字列が表示されている部分) を含めること．