サイトマップ サイト内検索 まとめ

埋め込み SQL

大学で使用した自作の資料等を，手直しの上公開している． クリエイティブ・コモンズ BY NC SA．

リレーショナルデータベースの基礎であるテーブル定義，一貫性制約，SQL，結合と分解，トランザクション，埋め込みSQL，実行計画，二次索引を学ぶ．SQLite 3 を用いて，SQL についての演習も行う．

【サイト内の関連ページ】

  • リレーショナルデータベースの基本まとめ：別ページ »で説明
  • SQL 入門演習（SQLite 3 を使用）（全３回）：別ページ »にまとめ

    入門者向け．リレーショナルデータベースを知る．

  • リレーショナルデータベースシステム（スライド資料と SQL 演習）（全15回）：別ページ »にまとめ

    入門者向け．設計と利用と運用．MySQL, Access を利用．基本を学ぶ．演習付き．

  • リレーショナルデータベース演習（全１５回）：別ページ »にまとめ

    入門者向け．設計と利用に関する演習．

  • リレーショナルデータベースの基礎（まとめ） [PDF], [ワードファイル]

    基礎を１つにまとめた資料．

事前準備としてエディタと 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. （Windows で，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

    を設定

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 の場合の手順例

Windows の場合の手順例

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

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

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

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

* 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

      * Windows の場合，次のようなエラーが発生することがあります． 

      ./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

      回避法ですが， sqlite3.dll　というファイルを、 https://www.sqlite.org/download.html から再度ダウンロードし，C:\Windows\System32 に置く（昔のファイルを上書き）と直る場合があります.

    いろいろな 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

    【実行結果の例】

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