コードを舐める日々

わからないことがわからないことをしる日々

SQLiteのおさらい

SQLite3 の型は5つしかない。
Datatypes In SQLite Version 3

型名 概要
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.

varchar と指定しても、自動的に TEXT に変換される模様。
bookmarkのアプリを想定したサンプルコード書いてみた。

$ sqlite3 test.db
> CREATE TABLE bookmark (id INTEGER PRIMARY KEY, url VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, created VARCHAR(20) NOT NULL, UNIQUE(url));
> INSERT INTO bookmark (id, url, title, created) values (1, 'http://www.hatena.ne.jp', 'はてな', datetime('now', 'localtime'));
> INSERT INTO bookmark (id, url, title, created) values (2, 'http://d.hatena.ne.jp', 'はてなダイアリー', '2009-05-12 10:02:56');
> INSERT INTO bookmark (id, url, title, created) values (3, 'http://b.hatena.ne.jp', 'はてなブックマーク', '2009-05-12 10:02:56');
> INSERT INTO bookmark (id, url, title, created) values (4, 'http://d.hatena.ne.jp/makotoworld', 'makotoworldのはてなダイアリー', '2009-05-12 10:02:57');
> INSERT INTO bookmark (id, url, title, created) values (5, 'http://b.hatena.ne.jp/makotoworld', 'makotoworldのはてなブックマーク', '2009-05-12 10:02:57');
> SELECT * FROM bookmark;
1|http://www.hatena.ne.jp|はてな|2009-05-12 10:02:55
2|http://d.hatena.ne.jp|はてなダイアリー|2009-05-12 10:02:56
3|http://b.hatena.ne.jp|はてなブックマーク|2009-05-12 10:02:56
4|http://d.hatena.ne.jp/makotoworld|makotoworldのはてなダイアリー|2009-05-12 10:02:57
5|http://b.hatena.ne.jp/makotoworld|makotoworldのはてなブックマーク|2009-05-12 10:02:57
> .q

sqliteに入らなくても下記のように書くことでSQL文の実行もできる。

SQLite3のワンライナー

$ sqlite3 test.db "SELECT * FROM bookmark";

または上記のようにsql文を実行しなくてもsqlファイルを読み込ませることも可能。

bookmark.sqlをつくるよ

$ vi bookmark.sql
CREATE TABLE bookmark (
        id INTEGER PRIMARY KEY,
        url VARCHAR(255) NOT NULL,
        title VARCHAR(255) NOT NULL,
        created VARCHAR(20) NOT NULL,
        UNIQUE(url)
);
INSERT INTO bookmark (id, url, title, created) values (1, 'http://www.hatena.ne.jp', 'はてな', datetime('now', 'localtime'));
INSERT INTO bookmark (id, url, title, created) values (2, 'http://d.hatena.ne.jp', 'はてなダイアリー', '2009-05-12 10:02:56');
INSERT INTO bookmark (id, url, title, created) values (3, 'http://b.hatena.ne.jp', 'はてなブックマーク', '2009-05-12 10:02:56');
INSERT INTO bookmark (id, url, title, created) values (4, 'http://d.hatena.ne.jp/makotoworld', 'makotoworldのはてなダイアリー', '2009-05-12 10:02:57');
INSERT INTO bookmark (id, url, title, created) values (5, 'http://b.hatena.ne.jp/makotoworld', 'makotoworldのはてなブックマーク', '2009-05-12 10:02:57');

test.db にbookmark.sqlを実行するよ

$ sqlite3 test.db < bookmark.sql

INSERT 文を作成する時に行き詰まったことはタイムスタンプ。
MySQL とかなら、 NOW() で現在時刻を挿入できるが、SQLiteでは構文エラーになる。
調べてみたら、

date(timestring, modifier, modifier, ...) 年月日の表示
time(timestring, modifier, modifier, ...) 時間の表示
datetime(timestring, modifier, modifier, ...) 年月日+時間の表示
julianday(timestring, modifier, modifier, ...) ユリウス暦の日を表示
strftime(format, timestring, modifier, modifier, ...) 表示したいフォーマットを指定

現時点の年月日と時刻を挿入させたいなら datetime('NOW', 'LOCALTIME') と書く。
datetime() のみでは、グリニッジ標準時間として表示される。
LOCALTIME と入れることでローカルPCのタイムに合わせて変換表示される。

詳しくは原典を参照すればいい。
SQLite Query Language: Date And Time Functions
嬉しいことにサンプルもいくつかあるので、使い方も理解できる。
small scriptやCPANモジュールの動作確認とかなら、SQLiteの選択はありだなと思った。