個人開発者のためのSQLiteメモ

目次

0.SQLiteを選択する理由

SQLiteのメリットは「手軽」「軽量」「(そこそこ)速い」

SQLiteが「遅い」という誤解

データベースのパフォーマンスの比較がよく話題に挙がり、SQLiteは「遅い」とされているようです。果たしてそれは本当でしょうか?
ベンチマーク結果からの評価によって比較がされますが、 下記の理由から「遅い」と結論づけてしまっていることが多いようです。
ちなみ自分もSQLiteが遅いという思い込みからMySQLへと移行した過去がありましたが、今ではSQLiteで十分という考えに落ち着いています。

開発(デバッグ)がしやすい

SQLiteならデバッグ作業にも威力を発揮します。
ファイルの差替えだけでDB構造を再現することができるのは魅力的です。

複数のテストケース(DBファイル)を用意しておき、差替えて使うこともできます。

大規模になってから移行すればいいじゃない

小中規模(~10万件)ならSQLiteを選択。それ以上で利用するならMySQL等を使用。
プロトタイプ時にはSQLiteを採用して、本番では別のDBを利用するという手もあり。

そもそも、そんなに大規模なシステムを構築することなんて……ある?

1.SQLiteを利用する

Perlでの使用

[前準備] DBD::SQLiteパッケージのインストール(ほとんどの環境ではすでにインストール済)
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=sample.db");

$dbh->do("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , value TEXT)");

my $sth = $dbh->prepare("INSERT INTO test (value) VALUES(?)");
$dbh->{AutoCommit} = 0; # トランザクションの開始

for (("apple", "orange", "grape")){
$sth->execute($_);
}
$dbh->commit;

my $sth_select = $dbh->prepare("SELECT value FROM test LIMIT ?");
$sth_select->execute(2); # LIMIT 2とする
my $value;
$sth_select->bind_columns(\$value);
while ($sth_select->fetch){
print "$value\n";
}
$dbh->disconnect;

WSHでの使用

[前準備] SQLite ODBC Driverをインストールしておきます
// WSH (JavaScript)

var con = WScript.CreateObject("ADODB.Connection");
con.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\\sample.db";
con.Open();

con.Execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , value TEXT)");

var cmd = WScript.CreateObject("ADODB.Command");
cmd.ActiveConnection = con;
cmd.CommandText = "INSERT INTO test (value) VALUES(?)"
cmd.CommandType = 1;
cmd.Prepared = true;

con.BeginTrans(); // トランザクションの開始

var list = ["apple", "orange", "grape"];
for (var i=0;i < list.length;i++){
cmd.Parameters(0) = list[i];
cmd.Execute();
}
con.CommitTrans();

var cmd_select = WScript.CreateObject("ADODB.Command");
cmd_select.ActiveConnection = con;
cmd_select.CommandText = "SELECT value FROM test LIMIT ?"
cmd_select.CommandType = 1;
cmd_select.Prepared = true;

cmd_select.Parameters(0) = 2; // LIMIT 2とする
var rs = cmd_select.Execute();
while (!rs.Eof){
WScript.Echo(rs.Fields("value").Value);
rs.MoveNext();
}

Adobe AIR(ActionScript3)での利用

package
{
import flash.data.*;
import flash.events.*;
import flash.filesystem.*;

public class SQLiteTest {
private var con:SQLConnection;

public function DBUtil(){
var folder:File = File.applicationStorageDirectory;
var dbFile:File = folder.resolvePath("sample.db");

con = new SQLConnection();
con.addEventListener(SQLEvent.OPEN, openHandler);
con.open(dbFile);
}

public var data:Array;
private function openHandler(evt:SQLEvent):void{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = con;
stmt.text = "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, value TEXT)";
stmt.execute();

var stmt_insert:SQLStatement = new SQLStatement();
stmt_insert.sqlConnection = con;
stmt_insert.text = "INSERT INTO test (value) VALUES(:value)";


con.begin(); // トランザクション開始
var list:Array = ["apple", "orange", "grape"];
for (var i:uint = 0;i < list.length;i++){
stmt_insert.parameters[":value"] = name;
stmt_insert.execute();
}
con.commit();

var stmt_select:SQLStatement = new SQLStatement();
stmt_select.sqlConnection = conn;
stmt_select.text = "SELECT value FROM test LIMIT :num";
stmt_insert.parameters[":num"] = 2; // LIMIT 2とする
stmt_select.execute();

var result:SQLResult = stmt_select.getResult();
data = result.data;
}
}
}

Javaでの利用

[前準備] sqlite-jdbcを利用してSQLiteを使用します
import java.sql.*;

public class SQLiteTest{
public static void main(String[] args) throws ClassNotFoundException{
Class.forName("org.sqlite.JDBC");
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:sqlite:sample.db");

Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, value TEXT)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO test (value) VALUES(?)");
con.setAutoCommit(false); // トランザクションの開始

String[] list = new String[]{"apple", "orange", "grape"};
for (int i=0;i < list.length;i++) {
pstmt.setString(1, list[i]);
pstmt.execute();
}
con.commit();

PreparedStatement stat_select = con.prepareStatement("SELECT value FROM test LIMIT ?");
stat_select.setInt(1, 2); // LIMIT 2とする
ResultSet rs = stat_select.executeQuery();
while (rs.next()){
System.out.println(rs.getString("value"));
}
}
catch (SQLException e){
System.err.println(e.getMessage());
}
finally {
try {
if (con != null) con.close();
}
catch (SQLException e){
System.err.println(e.getMessage());
}
}
}
}

コマンドラインから利用する

[前準備]SQLiteをダウンロードする

PATHを通して、コマンドラインからコマンドを実行します。
sqlite3 sample.db

[SQL等コマンドを入力]

.quit

SQLiteをGUIで利用する

GUIから利用するにはたくさんのツールがありますが、FirefoxやThunderbirdを使用しているならばアドオンにSQLiteManagerというものがあります。
開発中などテーブルの中身をいじったりするのに重宝しています。

SQLiteManager

2.基本的なSQL構文

SELECT [columnName, ...] FROM [tableName] WHERE [columnName] = ? ORDER BY [columnName] DESC LIMIT ?

SELECT userName, sum(point) AS totalPoint FROM [tableName] GROUP BY userName HAVING totalPoint > ?

DELETE * FROM criminals WHERE crime_id NOT IN(SELECT id FROM crime WHERE parent_job = "politician")

SELECT user.id, item.name FROM user INNER JOIN item ON user.id = item.user_id

INSERT INTO [tableName] (user_id, value) VALUES (?, ?)

DELETE FROM [tableName] WHERE id = ?

UPDATE [tableName] SET value = ? WHERE id = ?

CREATE TABLE times (
id INTEGER PRIMARY KEY NOT NULL ,
time TEXT DEFAULT( DATETIME('now','localtime') )
)

時間操作(TEXT型にDATETIME形式で値を格納した場合)

SELECT * FROM table WHERE time = datetime('now','localtime', '-3 days')

TEXT型なのでLIKEが使える。
SELECT * FROM table WHERE time LIKE "2013-07%"

時間操作(AdobeAIRのDate型は内部的にはjuliandayで値が格納される)

値だけ見ても何時なのか分からないのがデメリットだが、日付の足し算、引き算をするのは楽かも。
SELECT * FROM table WHERE time > julianday('now','localtime','-3 days')
月ごとに集計
SELECT strftime("%Y-%m", time) AS month, count(*) FROM table GROUP BY month

CASE句 (曜日を表示する)

SELECT datetime('now') AS datetime,
CASE strftime('%w','now')
WHEN '0' THEN '日'
WHEN '1' THEN '月'
WHEN '2' THEN '火'
WHEN '3' THEN '水'
WHEN '4' THEN '木'
WHEN '5' THEN '金'
WHEN '6' THEN '土'
END AS day

CASE句2 (曜日ごとに振り分け)

SELECT datetime('now') AS datetime,
CASE
WHEN strftime('%w','now') = '0' THEN '休日'
WHEN strftime('%w','now') = '6' THEN '(基本的に)休日'
ELSE '平日'
END AS day

[詳細] SQLiteの構文

3.過去にこんな使い方したような……