SQLite/Rubyの使い方

ruby

RubyでSQLite3を使った際のメモです。これだけ覚えておけばだいたいOK.

用語

レコード・行・ROWなどはごっちゃに使われることもあるので念のため確認しましょう。

  • テーブル：表のこと。
  • レコード：データセットのこと。行、ROWと言うこともある。
  • フィールド：レコードを構成する要素のこと。列、COLUMNと言うこともある。
  • クエリ：データの検索または検索に使うSQL文のこと。

コマンドライン

DBの作成や消去、内容の確認などはコマンドラインから行うことが多いと思います。最低限必要なのは以下のコマンド。

SQLiteの起動と終了
  • 起動
>sqlite3 database.db
  • 終了
sqlite> .exit
Tableの作成

SQLite3には実質、INTEGER,REAL,TEXT,BLOB の4つの型しかありません。TIME/DATEのような気の利いた型はないので時間の扱いなどは後ほど。一つ特殊なものにINTEGER PRIMARY KEYがあります。詳細はこちら。

これだけ覚えて入れば大体応用できるんじゃないでしょうか。

sqlite> create table items (
> id integer primary key,
> timestamp text not null,
> text text
> );
作成した内容の確認
sqlite> .schema
sqlite> .table
新しいフィールドを追加する
sqlite>alter table items add name text;
データの取得

select文での、データの特定方法は奥が深いので、ここでは基本形と、よく使うものだけあげておきます。

  • 基本形
select (取り出すフィールド) from (DB名) (条件);

よく使う形

  • 最新データの確認

idの降順に10個、全フィールドを取得します。

sqlite> select * from items order by id desc limit 10;

・特定データの確認
idを指定してフィールドを取得します。

sqlite> select * from items where id=2;
フィールドの追加と消去
  • 追加
sqlite> insert into items (timestamp, text) values (datetime('now', 'localtime'), 'hoge')

時間の入力にはdatetime関数を使います。このページの下のほうでほどもう少し詳しく説明してます。

  • 消去

何も指定しないと全部消去になっちゃうので危険です。条件をselect文で確かめてからやりましょう。

sqlite> delete from items;

だと全消去。
id=2のフィールドを消す場合、

sqlite> select * from items where id=2;

で確認し

sqlite> delete from items where id=2;

で消去

Rubyからの呼び方

ライブラリのロード
require 'rubygems' # gem install sqlite3-ruby した場合
require 'sqlite3'
フィールドを読み込み、配列に追加する
sql_opt = "order by time desc"
db = SQLite3::Database.new("database.db")
sql = "select * from items #{sql_opt}"
db.execute(sql) {|row|
　　@table << row
}
db.close
フィールドの追加
db = SQLite3::Database.new("database.db")
new_item = {:timestamp => time, :name => name, :text => text}
sql = "insert into items values (:id, :timestamp, :name, :text)"
db.execute(sql, new_item)
db.close
Primary Key について
new_item = {:timestamp => time, :name => name, :text => text}
sql = "insert into items values (:id, :timestamp, :name, :text)"

この文で、:id の値が指定されていませんが、Primary Keyは自動にインクリメントされます。

時間の扱い

SQLiteは特定のフォーマットに沿った文字列を、時間として扱うことができます。 ここでは以下のフォーマットを使います。。

"%Y-%m-%d %X:%M:%S"

詳細はこちら。

上の「追加」の例で指定してるtimeですが、書き込む前にフォーマットしておきます。

time = Time.now.strftime("%Y-%m-%d %X:%M:%S")
new_item = {:timestamp => time, :name => name, :text => text}
sql = "insert into items values (:id, :timestamp, :name, :text)"

※SQLiteの機能でもできるような気がするのですがRubyからの呼び出し方法がわからなかったのと、あったとしても手間は変わらないと思ったのでRubyでフォーマットしてます。

これで、一通りは使えましたね。