埋め込み SQL

事前準備としてエディタと Ruby 処理系のインストール

※ Windows の場合には Ruby 処理系として ActiveScriptRuby を推奨しておく．エディタは何でもよいが，特に こだわりがない場合には， エディタとしては MkEditor や xyzzy を勧めておく。

◆ Windows の場合の手順例を下に示す.

（Windows の場合のみ，オプション）MkEditor のインストール (Windows only. Install an editor. It is optional)

MkEditor は優れたエディタ．Windows で動く．

http://www.mk-square.com/

• 「はい」をクリック
  
• 「次へ」をクリック
  
• 「次へ」をクリック
  

（Windows の場合のみ) ActiveScriptRuby のインストール

1. ActiveScriptRuby のインストーラプログラム ActiveRuby.msi を起動
2. ようこそ画面

   「Next」をクリック

   
3. インストールディレクトリの設定

   デフォルトのままでよい．「Next」をクリック

   
4. インストール開始の確認

   「Next」をクリック

   
5. インストールが始まる
   
6. インストール完了の確認

   「Close」をクリック

   
7. RDoc のインストーラプログラム RDoc.msi を起動
8. ようこそ画面

   「Next」をクリック

   
9. インストールディレクトリの設定

   デフォルトのままでよい．「Next」をクリック

   
10. インストール開始の確認

    「Next」をクリック

    
11. インストールが始まる
12. インストール完了の確認

    「Close」をクリック

    
13. Windows の環境変数 PATH の設定

    C:\Program Files\ruby-1.8\bin
    

    p

    を設定

    

Ruby の SQLite 3 パッケージ

授業で配布している USB メモリを使っている人は， SQLite 3 パッケージはインストール済みなので、特に何も行う必要はない．

【関連する外部ページ】: https://rubygems.org/gems/sqlite3?locale=ja

◆ Windows の場合の手順例を下に示す

sqlite3.dll の設定

1. sqlite3.dll を C:\Windows\System32 にコピーする

Ruby の SQLite 3 パッケージのインストール

1. Windows のコマンドプロンプトを使う
2. Ruby の SQLite 3 パッケージのインストーラプログラム sqlite3-ruby-1.2.5-x86-mingw32.gem を使う
   

   エラーが出たときは，環境変数 PATH の設定を間違っている可能性がある．

3. インストールできたかは，「gem list」の実行で確認できる．実行例は次の通り．
   

対話型 Ruby 処理系を使ってみる(Interactive Ruby Interpreter)

対話型の処理系は，コンソールでコマンドを打つたびに Ruby プログラムの評価結果が表示されるもの．

◆ Ubuntu の場合の手順例

• 端末を起動する

  「アクセサリ」→「LXTerminal」

  
• 端末の中で「irb」コマンドを実行
  
• 足し算

  1 + 2 + 3
  1234567890123456789 + 1234567890123456789
  

  
• sin, sqrt, log

  「include Math」は，sin などの数学関数の機能を取り込むための操作。

  include Math
  sin(1.57)
  sqrt(2)
  log(10)
  

• 変数

  x = 100
  y = 200
  x + y
  

  
• irb を終了したい時は exit で終了する

  exit 
  

  

◆　Windows の場合の手順例

• Windows の「スタート」から「Ruby 1.8」を選び，「irb」を選ぶ

  irb は 「Interactive Ruby」

  
• 足し算

  1 + 2 + 3
  1234567890123456789 + 1234567890123456789
  

  
• sin, sqrt, log

  include Math
  sin(1.57)
  sqrt(2)
  log(10)
  

• 変数

  x = 100
  y = 200
  x + y
  

  

Ruby の中に埋め込まれた SQL による問い合わせ (SQL query embedded in a Ruby program)

• Ruby の中に埋め込まれた SQL を用いた問い合わせ
• Ruby の中に埋め込まれた SQL を用いたテーブルへの行の挿入
• テーブルの一覧表示

SQLite 3の SQL に関する詳しい説明は：

• http://www.hwaci.com/sw/sqlite/lang.html にあります．

SQLite 3 のデータ型 (SQL Data Types)

データ型の種類は，データベース管理システムごとに違う． SQLite では，integer, real, text, BLOB などのデータ型を扱うことができる。 詳しい説明は https://www.sqlite.org/datatype3.html にある．要点は下の通り．

• NULL: 空値 (a NULL value)
• integer: 符号付きの整数 (signed integer) ※ SQLite では BIGINT と書いても integer と書いても同じ「８バイトの整数」という意味
• real: 浮動小数点値 (floating point value)
• text: 文字列 (text string)

  ※ 日時を示す datetimeについて．SQLite では datetime と書いても text と書いても同じ「可変長文字列」の意味．だけど使い分けるべき（その方が分かりやすい）

• BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object). 入力がそのままの形で格納される (stored exactly as it was input)． ※ SQLite では LARGEBLOB と書いても BLOB と書いても同じ「長大なバイナリ・ラージ・オブジェクト」という意味

※ SQLite 3のデータ型と，SQL の標準が定めるデータ型の定義は異なる．大胆にまとめると，SQLite 3のデータ型の方がより大きな範囲のデータを扱える．

前準備

演習では E テーブルを用いる． すでに「テーブルの更新，トランザクション」の回の授業で E テーブルを作成した．それが残っている場合には、次の SQL を実行する必要は無い（実行したとしても、同じ名前のテーブルを二重に定義できないのでエラーになる）．残っていない場合には次の SQL を実行する．

If you already define table 'E', you don't have to execute the following SQL again.

create table E (
    name          text     not null,
    score         integer  not null check ( score >= 0 AND score <=100 ),
    student_name  text     not null,
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );

begin transaction;
insert into E values( 'Database',    80, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    95, 'AA', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    80, 'LL', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 85, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 75, 'LL', datetime('now', 'localtime'), NULL );
commit;
SELECT * FROM E; 

Ruby の中に埋め込まれた SQL 問い合わせプログラムの例

SQL を用いてテーブルの全ての行の表示 (All rows in a table)

◆SQLプログラムの例

SELECT * FROM E;

◆期待される結果

◆ ここでは、Sqliteman を使うのではなく、Ruby のプログラムを作成してみる．

そこで、次の Ruby のプログラムをエディタで入力し，名前をつけて保存する

require 'rubygems'
require 'sqlite3'

DBNAME = "/home/ubuntuuser/mydb"

Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )

sql = <<SQL
SELECT * FROM E;
SQL
db.execute(sql) do |row|
    p row
end

db.close

■ Ubuntu の場合の手順の例（Leafpad エディタを使っている）

1. 「アクセサリ」→「Leafpad」
   
2. 編集する
   
3. 編集が終わったらファイルを保存する．

   ファイル名は英語が良い ファイルを保存したディレクトリとファイル名を覚えておくこと．

   

   下の実行例では、ディレクトリ名は「/home/ubuntuuser」、ファイル名は「hoge.rb」

   
4. 実行してみる

   端末 (LXTerminal) で、次のように実行し、実行結果を確認する．「ruby」は、ruby言語処理系を呼び出すためのコマンド。

   cd ＜保存した Ruby プログラムのディレクトリ名 ＞
   ruby ＜保存した Ruby プログラムのファイル名 ＞
   

   ◆実行結果の例

   
5. プログラムの要点を確認しておく
   • SQLプログラム「SELECT * FROM E;」を，文字列 のデータとして変数 sql に格納している部分

     sql = <<SQL
     SELECT * FROM E;
     SQL
     

   • 変数 sql に格納された SQL プログラムを評価させている部分

     db.execute(sql)
     

   • 評価結果を１行づつ変数 row に格納し，変数 row の表示を行う。 そのことを評価結果の全ての行について行う部分

     do |row|
         p row
     end
     

./sqlite3.dll: 127: 指定されたプロシージャが見つかりません。 - Init_sqlite3 (LoadError)
 ./sqlite3.dll
            from C:/Program
 Files/ruby-1.8/lib/ruby/vendor_ruby/1.8/rubygems/custom_require.rb:31:in
 'require'
            from hoge.rb:2

いろいろな SQL による問い合わせ (SQL query embedded in a Ruby program)

先ほど作った Ruby のプログラムを書き換えて、いろいろなことを行なってみる

• 条件に合致する行のみの表示 (その 1)

  SELECT * FROM E WHERE student_name = 'KK';
  

  Ruby のプログラムは次のようになる．

  ○ 先ほどのプログラムを、ほほそのまま使える．「SELECT * FROM E WHERE student_name = 'KK';」の１行を書き換えるだけ．

  require 'rubygems'
  require 'sqlite3'
  
  DBNAME = "/home/ubuntuuser/mydb"
  
  Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
  db = SQLite 3::Database.new( DBNAME )
  
  sql = <<SQL
  SELECT * FROM E WHERE student_name = 'KK';
  SQL
  db.execute(sql) do |row|
      p row
  end
  
  db.close
  

  【実行結果の例】

  ファイルの書き換えと保存が終わったら、再度保存して、もう１度実行してみる．

  
• 条件に合致する行のみの表示 (その 2)

  SELECT * FROM E WHERE score > 80;
  

  Ruby のプログラムは次のようになる．

  ○ 先ほどのプログラムを、ほほそのまま使える．「SELECT * FROM E WHERE score > 80;;」の１行を書き換えるだけ．

  require 'rubygems'
  require 'sqlite3'
  
  DBNAME = "/home/ubuntuuser/mydb"
  
  # データベースオープン
  Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
  db = SQLite 3::Database.new( DBNAME )
  
  sql = <<SQL
  SELECT * FROM E WHERE score > 80;
  SQL
  db.execute(sql) do |row|
      p row
  end
  
  db.close
  

  【実行結果の例】

  ファイルの書き換えと保存が終わったら、再度保存して、もう１度実行してみる．

  

Ruby プログラムの中に埋めこまれた SQL プログラムを用いたデータベース更新 (Database update using Embedded SQL in a Ruby Program)

次のような SQL を Ruby プログラムの中で実行することを考える。

insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    85, 'CC', datetime('now', 'localtime'), NULL );

Ruby でプログラムを作るときの要点は：

• db.execute_batch(...) は，Ruby で複数の SQL を1度に実行するためのもの
• db.transaction do ... end はトランザクション開始のRuby の決まり文句．正常終了すれば自動的にコミットし，異常終了の場合には自動的にロールバックされる．

Ruby のプログラムは次のようになる．

require 'rubygems'
require 'sqlite3'

DBNAME = "/home/ubuntuuser/mydb"

Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )

sql = <<SQL
insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    85, 'CC', datetime('now', 'localtime'), NULL );
SQL

db.transaction do |db| db.execute_batch( sql ) end

db.close

【実行結果の例】

ファイルの書き換えと保存が終わったら、再度保存して、もう１度実行してみる． 何も表示されないけれど，それが正常な動作なので，心配しないこと

SQLiteman などを使って，テーブル E を確認しておく. 確かに2行増えている．

Ruby プログラムの中に埋めこまれた SQL プログラムを用いたテーブル一覧表示 (Table List using Embedded SQL in a Ruby Program)

SQLite 3 でデータベース内のテーブル一覧を表示するには，sqlite_master, sqlite_temp_masterという名前が付いた特別なテーブルを使う．

• sqlite_master: TEMPORARY テーブル以外のデータベーススキーマが格納されている．
• sqlite_temp_master: TEMPORARY テーブルのデータベーススキーマが格納されている．

データベーススキーマを見たいときは，次のような SQL を実行する．

select * from sqlite_master;

※ sqlite_master, sqlite_temp_master に，DROP TABLE, UPDATE, INSERT, DELETE 操作を行うことは許されていません

Ruby のプログラムは次のようになる．

require 'rubygems'
require 'sqlite3'

DBNAME = "/home/ubuntuuser/mydb"

# データベースオープン
Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite 3::Database.new( DBNAME )

sql = <<SQL
SELECT * FROM sqlite_master;
SQL
db.execute(sql) do |row|
    p row
end

db.close

【実行結果の例】

ファイルの書き換えと保存が終わったら、再度保存して、もう１度実行してみる．