トップページデータ処理リレーショナルデータベース(全11回)埋め込み SQL

埋め込み SQL

大学で使用した自作の教材を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.

リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,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」をクリック

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

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

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

    Next」をクリック

    [image]
  5. インストールが始まる

    [image]
  6. インストール完了の確認

    Close」をクリック

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

    Next」をクリック

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

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

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

    Next」をクリック

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

    Close」をクリック

    [image]
  13. Windows の環境変数 PATH の設定

    C:\Program Files\ruby-1.8\bin
    
    p

    を設定

    [image]

Ruby の SQLite3 パッケージ

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

参考 Web ページ: https://rubygems.org/gems/sqlite3?locale=ja

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

sqlite3.dll の設定

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

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

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

    [image]

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

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

    [image]

対話型 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; 

[image]

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

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

◆SQLプログラムの例

SELECT * FROM E;

◆期待される結果

[image]

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

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

require 'rubygems'
require 'sqlite3'

DBNAME = "/home/ubuntuuser/mydb"

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

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

db.close

Ubuntu の場合の手順の例(Leafpad エディタを使っている)

  1. 「アクセサリ」→「Leafpad」

    [image]
  2. 編集する

    [image]
  3. 編集が終わったらファイルを保存する.

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

    [image]

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

    [image]
  4. 実行してみる

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

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

    ◆実行結果の例

    [image]
  5. プログラムの要点を確認しておく

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 のプログラムを書き換えて、いろいろなことを行なってみる

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 でプログラムを作るときの要点は:

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

require 'rubygems'
require 'sqlite3'

DBNAME = "/home/ubuntuuser/mydb"

Dir.chdir( File.dirname( File.expand_path( DBNAME ) ) )
db = SQLite3::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

[image]

【実行結果の例】

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

[image]

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

[image]

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

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

データベーススキーマを見たいときは,次のような 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 = SQLite3::Database.new( DBNAME )

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

db.close

[image]

【実行結果の例】

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