サーバー構築

CakePHP2でSQLite3を使う方法

CakePHP2 で SQLite3 を使う機会があったので記録として残しておきます。

すごく簡単に使えます。

SQLite3 の導入のメリットは引っ越しが簡単で、データベースファイルの移動だけで新しい環境に移せます。わざわざデータをダンプして移し替える手間が無いし、読み込みがメインなら使い勝手は良いですね。SQL文が MySQL と少し違いがあリますが･･･。

今回は CakePHP2 で SQLite3 を使う事を前提で記述します。

公開ディレクトリは、/home/user/cake/app/webroot/ と仮定して作業しますので、ご自分の環境に合わせて設定してください。
データベース名は、「cake」で作ります。

SQLite3とphp5-sqliteをインストール

// インストール
# apt-get install sqlite3 php5-sqlite

SQLite3 のデータベース保存用ディレクトリを作成

気をつける点が一つ、公開ディレクトリに SQLite3 の保存用ディレクトリを作らないようにして下さい。やむを得ない場合は、「.htaccess」などでディレクトリに直接アクセス出来ないように設定してください。ダウンロードされた場合、中身が丸見えです･･･。
ここからは一般ユーザーで作業します。

// データベースファイルを保存するディレクトリを作る
$ mkdir /home/user/sqlite3/

SQL文を作成・流し込み

SQL文の見本です。
今回は、cake.sql（utf-8）を作り一気に流し込みます。INSERT の前にトランザクションを使ってますが、データ量が少ない場合は不要です。データが大量にある場合はトランザクションを使った方が流し込みが速くなります。

-- テーブル
CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `username` VARCHAR(50),
  `password` VARCHAR(50),
  `role` VARCHAR(20),
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
) ;

-- トランザクション開始
BEGIN TRANSACTION;

-- データ
INSERT INTO `users` (`username`, `password`, `role`, `created`, `modified`) VALUES('yamada', '0123456789', 1, datetime('now', 'localtime'), datetime('now', 'localtime'));
INSERT INTO `users` (`username`, `password`, `role`, `created`, `modified`) VALUES('tanaka', '0123456789', 1, datetime('now', 'localtime'), datetime('now', 'localtime'));
INSERT INTO `users` (`username`, `password`, `role`, `created`, `modified`) VALUES('sato', '0123456789', 1, datetime('now', 'localtime'), datetime('now', 'localtime'));

-- コミット
COMMIT;

-- インデックス作成
CREATE INDEX `username` on `users`(`username`, `password`);

SQLite の日付関数は、そのまま使うと、-9時間（UTC）なので、datetime('now', 'localtime')の様に「localtime」を指定する必要があります。

それでは SQLite にデータを流し込みます。cake.sql を先ほど作ったディレクトリにアップしておいてください。

// 移動
$ cd /home/user/sqlite3/

// 流し込み（リストア）
$ sqlite3 cake.sqlite3 < cake.sql

流し込みを行うと、/home/user/sqlite3/cake.sqlite3 が作成されます。これがDBファイルです。私は、拡張子「.sqlite3」を付けていますが、流し込みの時に指定しなければ、「cake」だけでも動きますが、拡張子を付けた方が SQLite3 のファイルだと分りやすいので付けています。

パーミッションの設定

SQLite に書き込みをしない場合は、この作業は不要ですが一応作業しておきます。

// パーミッションの設定（ディレクトリ側）
$ chmod 777 /home/user/sqlite3/

// パーミッションの設定（DBファイル側）
$ chmod 666 /home/user/sqlite3/cake.sqlite3

レンタルサーバーの場合は、「700」と「600」にして下さい。書き込みでエラーが出る場合は、「707」と「606」などに変更して下さい。
パーミッションを変更しないで書き込みを行うと、「SQLSTATE[HY000]: General error: 14 unable to open database file」のエラーが出ます。

CakePHP2のdatabase.phpを修正

パスを/home/~から指定してあげるのがポイント。これで CakePHP でSQLite3 が使えるようになります。意外と簡単ですね。

class DATABASE_CONFIG {

//	public $default = array(
//		'datasource' => 'Database/Mysql',
//		'persistent' => false,
//		'host' => 'localhost',
//		'login' => 'user',
//		'password' => 'password',
//		'database' => 'database_name',
//		'prefix' => '',
//		//'encoding' => 'utf8',
//	);
    
    public $default = array(
		'datasource' => 'Database/Sqlite',
		'persistent' => false,
		'database' => '/home/user/sqlite3/cake.sqlite3',
		'prefix' => '',
		'encoding' => 'utf8',
	);

	public $test = array(
		'datasource' => 'Database/Mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'user',
		'password' => 'password',
		'database' => 'test_database_name',
		'prefix' => '',
		//'encoding' => 'utf8',
	);
}

SQLite3 で知っておいた方が良い事

SQLite3 にログイン

パスワードなどは無い。

// SQLite3に入る。（DBファイル名を指定する）
$ sqlite3 cake.sqlite3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

SQLite3 を抜ける

SQLite3 のコマンドは、初めにドット「.」が付きます。

// SQLite3を抜ける
sqlite> .exit

SQLiteの内部テーブル参照

テーブルの情報を見ることが出来ます。

// 内部テーブル参照
sqlite> select * from sqlite_master;
table|users|users|2|CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `username` VARCHAR(50),
  `password` VARCHAR(50),
  `role` VARCHAR(20),
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE sqlite_sequence(name,seq)
index|username|users|4|CREATE INDEX `username` on `users`(`username`, `password`)

表示方法変更

上記のままだと見にくいので表示方法を変更。

// 表示方法変更（デフォルト：.mode list）
sqlite> .mode line


// 再度、内部テーブルを参照してみると･･･。
sqlite> select * from sqlite_master;
    type = table
    name = users
tbl_name = users
rootpage = 2
     sql = CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `username` VARCHAR(50),
  `password` VARCHAR(50),
  `role` VARCHAR(20),
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
)

    type = table
    name = sqlite_sequence
tbl_name = sqlite_sequence
rootpage = 3
     sql = CREATE TABLE sqlite_sequence(name,seq)

    type = index
    name = username
tbl_name = users
rootpage = 4
     sql = CREATE INDEX `username` on `users`(`username`, `password`)

テーブル削除

MySQLと同じです。

// テーブルを削除
sqlite> drop table users;

データベースの削除

cake.sqlite3 を削除するだけです。

// データベースの削除
$ rm /home/user/sqlite3/cake.sqlite3

バキューム

PostgreSQL を扱った事がある人なら分ると思いますが、MySQLでは無い作業があります。SQLite では DELETE しても物理的に削除されず、cake.sqlite3 がだんだん肥大化していきます。バキュームを行うと、空き領域の開放をしてDBファイルが小さくなり、デフラグも一緒に行われます。Cronで仕込んでおくと便利です。3.1からauto_vacuumも使えますが、cron でvacuumをした方が良いかも。

// バキューム
$ sqlite3 cake.sqlite3 vacuum

SQLite3のバージョンを調べる

// バージョンを調べる
# dpkg -l sqlite3
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                     Version                  Description
+++-========================-========================-================================================================
ii  sqlite3                  3.7.3-1                  A command line interface for SQLite 3

database is lockedエラー

SQLite3 は、書き込みが多すぎると「database is locked」が頻発します。SQLite3 では明示的にトランザクションを開始しなくても行われていますが、その際にコミットが出来なく、「database is locked」が発生している様です。「database is locked」が多発する時は、明示的にトランザクションを指定し、コミットのタイミングを調整する事によって解消できるかもしれません。ジャーナルファイルをメモリ上に作る方法で、書き込みを速度をアップする方法もあります。方法はネットで調べてください。キーワード「SQLite3 PRAGMA journal_mode = MEMORY」それでも無理なら、SQLite3 の限界だと思った方が吉。

空き容量の確保

トランザクションなどを行うと、ジャーナルファイル（cake.sqlite3-journal）が作られます。その為、ディスクの容量をある程度は確保しておきましょう。

読んで頂いて有り難うございます！

RECOMMENDこちらの記事も人気です。