Mac OS X 10.5 Leopard には、Ruby とともに、SQLite 3 も sqlite3-ruby も標準でインストールされているので、それを使ってみたときのメモ。自分で書いてる RubyCocoa のプログラムで使ってとりあえず動いたものをメモするけど、使い方が正しいかどうかはちょっとわからない。もっといい方法や、正しい方法が見つかれば、順に書き加えたり、書き換えたりしていくつもり。
SQLite とは SQL データベース管理ソフトで、MySQL みたいにサーバーにインストールしてデータベースを管理するのではなく、データベースをローカルのファイルで管理する（ネットワーク上にあってもいいかもしれないけど、試したことがないのでわからない）。つまり、データベースがファイルなので、移動させたり、バックアップしたりと管理が楽。ただ、そのために巨大なデータベースには向かないけど、小さめのデータベースなら速さも十分使い物になると思う。SQL のコマンド（？）もだいたいのものが使える。
ここでは、基本的に自分で使ってみたものをメモしていくので、すべてがカバーされることはないと思う。SQL のコマンドは Google で探せばいろいろ見つかると思うので試してください。自分は w3schools.com の SQL tutorial をよく参考にしますが、すべて英語です。日本語で、Ruby から SQLite を使う方法をまとめている人（組織？）が他にもいる（ある）ので、そちらも参考にしてください（リンク）。というか、そっちの方がわかりやすいかも。
あと、Ruby で使う、というより、SQLite 自体の使い方のメモになっている部分が多いです（当然と言えば当然ですが）。
SQLite を Ruby から使う、もしくは RubyCocoa のプログラムで使う場合は、まず、ライブラリを読み込む。
require 'rubygems'
require 'sqlite3'
Windows や Linux などで使う前提で書いてあるサイトなんかには、require 'sqlite3' だけしか書いてないことが多くて、Mac OS X で初めて試したときに全然動かなくて迷ったんだけど、その前に require 'rubygems' とする必要があるみたい。もしかしたら、ちゃんと設定すれば必要ないのかもしれないけど、これでとりあえず動いているので問題なしとしておく。
あと、SQL では、コマンドを大文字で表記するようだけど、小文字で書いても問題なく動く。ただ、コマンドである、というのをわかりやすくするために大文字で書いておく。
このページでは、データベースファイルやテーブル自体に対する操作についてメモしてある。レコードに対する操作は、次のページにメモすることにした。
SQLite を Ruby で使う２ - データを扱う（予定）
さて、SQLite を使えるようになって最初にすることは、データベースファイルを作ること。
db = SQLite3::Database.new("test.db")
これは、db という SQLite のオブジェクトを作っている。"test.db" のところでファイル名を指定する。RubyCocoa のアプリケーションで使う場合は、
相対パスだとアプリケーションパッケージ内のファイルを参照することになるので、絶対パスを入れる。
指定した名前のデータベースファイルがない場合は、新規に空のデータベースファイルを作る。ある場合は、そのファイルを開く（新規に上書きはしない）。
include SQLite3 とすれば、SQLite3 といちいち入力しなくてもいいので、何度もデータベースファイルを開けたり作ったりして
何回も入力するのが面倒な場合はそうしてください。
include SQLite3
db = Database.new("test.db")
データベースを使った後は、close で閉じる。
db.close
通常の処理
SQLite オブジェクトに対して execute メソッドで SQL コマンドを実行していく。
db.execute(SQLite コマンド)
SELECT なんかコマンドだと、実行すると結果の配列が帰ってくるので、Array オブジェクトと同様にブロックで処理できる。
db.execute(SQLite コマンド（SELECT）) do |row|
p row # row は結果の行で、各列の値が配列で返ってくる
end
複数のコマンドを一つの String に書き込んで実行する
execute では、１つのコマンドしか処理しないので、複数のコマンド（テーブルを複数作る場合など）がある場合は、一番最初のものだけ実行される。そのようなとき、すべて実行させるには、execute_batch を使う。
sql = <<-SQL
CREATE TABLE table1 (
text text
);
CREATE TABLE table2 (
number integer
);
SQL
db.execute_batch(sql)
繰り返し同じコマンドを実行する
一度の処理でコマンドを一度しか実行しない場合は、execute_batch() でいいけど、同じコマンドを繰り返し一連の操作で実行する場合はブロックに渡して実行する。
ary.each do |element|
db.execute(処理)
end
ただし、処理を続ける場合は、いちいち commit して実行スピードが遅くなるので、transaction でブロックにして一度にすべて処理するようにする。
db.transaction do
ary.each do |element|
db.execute(処理)
end
end
transaction をブロックにしない場合は、明示的に commit か rollback で閉じる
db.transaction
ary.each do |element|
db.execute(処理)
end
db.commit
データベースファイルを作ったら、そこにテーブルを作る。これも execute メソッドを使うんだけど、テーブルを作るコマンド CREATE TABLE は複数行にわたって書くのが普通なので、= << を使って String オブジェクト（文字列）を作る。SQL のコマンドは大文字で書かれることが多いけど、別に小文字でも問題なく動く。ただ、スクリプトの中で見分けやすいように大文字にしておく。
CREATE TABLE テーブル名 (フィールド定義);
sql = <<SQL
CREATE TABLE test_table (
フィールド名 データ型 ［制約］,
フィールド名 データ型 ［制約］);
SQL
各フィールドは半角コンマで区切り、最後はつけない。
ちなみに TextMate だと、<< の代わりに <<- を使って（インデントのときのように）、この例のように SQL を終端文字として使うと、SQL コマンドが色付けされる。
データは次の５つに分類される。ただ、テーブルを作るときにちゃんと定義しなくても大丈夫なようで、データを入力すると勝手に変換されるらしい。まあ、他の人が見たとき用のメモのためにも自分が後で参照する場合のためにも、データ型を書いておいた方がいいと思うけど。
null - そのまま null
integer - 整数値
full - float 型の数値
text - テキスト（ユニコード）
blob - バイトコード（イメージなどを保存する場合、バイトコードであつかう、でも、使ったことないのでよくわからない）
ただし、他の SQL 言語のデータ型も理解するので（限定されないようだが）、あとで他のデータベースソフトに移行することを考えている場合などは、
それにあわせて定義しておいた方がいいかもしれない。
制約には、次のものがある。
NOT NULL - null を値として入れられなくなる
PRIMARY KEY - テーブルの主となるフィールド
AUTOINCREMENT - 新しいレコードが追加されると、最後のレコードの値に１がつかされた値が自動で入るようになる。integer を定義したる場合にだけ使える
UNIQUE - 同じ値を持ったレコードを追加できなくなる
DEFAULT - デフォルト値を設定できる。これを書いたすぐ後にデフォルト値を指定する
CHECK - CHECK(条件) に当てはまるレコードだけが追加できるようになる
後で参照したりするために、それぞれのエントリーに固有の識別番号をつけたい場合など、いちいち正しい番号を計算して、なんてやってられない。そんなときは、フィールド型を integer に指定した後に PRIMARY KEY AUTOINCREMENT と加える。
id integer PRIMARY KEY AUTOINCREMENT
これで、id というフィールドに値を入れるときに null を入れると、数値が一つずつ自動で数え上がる固有の値が入るはず（何の指定もしなくてもいいかも）。
と書いてはみたものの、実は SQLite にはデフォルトで作られる ROWID なるフィールドがあって、何もしないでも常にここで指定した id と同じ値が入ってるようだ。つまり、この上のように id というフィールドを作らなくても、ROWID という名前で常にアクセスできる。例えば、
db.execute("SELECT * FROM table WHERE rowid == 3")
なんてすると、ROWID というフィールドに 3 が入ってるレコードを得られる。特にレコードを消したりしてなければ、３番目のレコードになるはず。
この２つは、全く同じという訳ではないけど、基本的には同じようなことをするらしい。ただ、この id が重要な役割を果たす場合なんかは、id のフィールドを作って対応した方がいいかもしれない。一応、integer PRIMARY KEY と ROWID は同じ（integer PRIMARY KEY のフィールドの値が ROWID のエイリアスになると書いてある）らしいけど、AUTOINCREMENT をつけるとどうなるかわからない。
名前、住所、電話番号の簡単なアドレス帳を作る場合などは次の様な感じになる。
address_book という名前のテーブルを作って、項目は姓、名、郵便番号、住所、電話番号、ついでに、年齢なんてのを入れてみる。それぞれ、last_name、first_name、postal_code、address、phone_num、age とする。郵便番号は英語だと zip code だけど、まあ、日本の郵便番号ってことで。これを execute で実行する。
sql = <<SQL
CREATE TABLE address_book (
last_name text,
first_name text,
postal_code text,
address text,
phone_num text,
age integer
);
SQL
db.execute(sql)
テーブルをデータベースファイルから削除するには DROP TABLE を使う。
db.execute("DROP TABLE address_book")
データベースファイルにどんなテーブルがあるかという情報を得たい場合は、テーブルを管理しているテーブル sqlite_master にアクセスする。sqlite_master テーブルには、次の情報が格納されている。
type - データのテーブル、もしくはインデックスのテーブル
name - テーブル名、もしくはインデックスのテーブル名（？）
tbl_name - テーブル名
rootpage - 固有の id（？）
sql - テーブルを作ったときの SQL コマンド
これを得るには、次のコマンドを実行する。
db.execute("SELECT * FROM sqlite_master")
結果は、上の５つが配列になった配列として返ってくる。なので、テーブル名だけを得たい場合は、次のようにすればいい。
tables = db.execute("SELECT tbl_name FROM sqlite_master WHERE type == 'table'")
これで、tables にはデータベースファイルに含まれるすべてのテーブル名が配列として入る。ただし、それぞれの要素は配列になっているので、取り出すときに注意。flatten で最初からテーブル名の配列にしてしまってもいい。
tables = db.execute("SELECT tbl_name FROM sqlite_master WHERE type == 'table'").flatten
また、どのデータベースファイルにも sqlite_sequence という ROWID を管理しているテーブルがあるので、そのテーブルをのぞいて処理をするようにする。
まあ、こんな感じにしたらいい。
tables = db.execute("SELECT tbl_name FROM sqlite_master WHERE type == 'table' AND tbl_name != 'sqlite_sequence'").flatten
これを応用して、データベースファイルに特定の名前のテーブルが含まれるかどうかを調べるには、この tables に対して include?() を使えばいい。
tables = db.execute("SELECT tbl_name FROM sqlite_master WHERE type == 'table'").flatten
tables.include?("address_book")
もしくは、簡単に、
db.execute("SELECT tbl_name FROM sqlite_master WHERE type == 'table'").flatten.include?("address_book")
テーブルが既に存在すれば true が、しなければ false が返ってくる。
テーブルの情報を得るには table_info(テーブル名) を使う。次の値がハッシュとして入っている配列が返ってくる。
cid - フィールド id
name - フィールド名
type - データ型
notnull - NOT NULL かどうか
dflt_value - デフォルト値
pk - プライマリキー
テーブルに含まれるフィールド名を取り出すには、次のようにする。
fields = Array.new
db.table_info("text_data") do |row|
fields << row["name"]
end
これで、fields にテーブル名が配列として入る。もっと簡単な方法があるかもしれないけど、とりあえず思いつかない。
SQLite ではテーブルに対する変更は今のところ次の２つに限られていいる。これ以外の変更が必要な場合は、新しいテーブルを作り直す必要がある。
フィールドの追加
テーブルに新しいフィールドを追加する場合は次のようにする。
ALTER TABLE テーブル名 ADD フィールド名 データ型
db.execute("ALTER TABLE address_book ADD COLUMN cell_num text")
テーブル名の変更
テーブル名を変更する場合は次のようにする。
ALTER TABLE テーブル名 RENAME TO 新テーブル名
db.execute("ALTER TABLE address_book RENAME TO address_database")