Memo/SQLite

SQLite

SQLite †

SQLite Home Page
SQLite - Wikipedia
- SQLite はMySQLやPostgreSQLと同じデータベース管理システムであるが、サーバとしてではなくアプリケーションに組み込んで利用される軽量のデータベースである。一般的なRDBMSに比べて大規模な仕事には不向きだが、中小規模ならば速度に遜色はない。また、APIは単純にライブラリを呼び出すだけであり、データの保存に単一のファイルのみを使用することが特徴である。バージョン3.3.8からは全文検索のFTS1モジュールがサポートされた。

バックアップ/リストア †

How To Use The SQLite Dump Command

.dump: SQLファイルを出力

sqlite3 mydb.db .dump | gzip > mydb.$(date '+%w').gz

import: SQLファイルを入力して新しいDBを作る
```
zcat mydb.$(date '+%w').gz | sqlite3 mydb.db
```

.backup: バイナリ形式のファイルのままコピーを取る
- cpコマンドだとファイルが破損するので注意。
- すぐロックするため「Error: database is locked」がよく出る。「.timeout 1000」で緩和できる
```
sqlite3 mydb.db ".backup mydb.$(date '+%w').db"
# オプションを追加して実行したい時。例:1秒待つ
echo -e ".timeout 1000\n.backup mydb.$(date '+%w').db" | sqlite3 mydb.db
```

.restore: .backupで作成したDBファイルから戻す
```
sqlite3 mydb.db ".restore mydb.$(date '+%w').db"
```

↑

最適化 †

http://www.sqlite.org/lang_vacuum.html

データを削除しても、通常DBBファイルサイズは小さくならない。また更新が多いと段々遅くなる。定期的にvacuumすると良い
```
sqlite3 trac.db vacuum
sqlite3 trac.db reindex
```

↑

異なるDB間でのテーブルコピー †

記事
- SQLite 異なるDB間でテーブルをコピー karak

sqlite3 new.db
sqlite> ATTACH DATABASE 'old.db' AS old;
sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /tmp/new.db
2    old              /tmp/old.db

sqlite> BEGIN;
sqlite> INSERT INTO main.table1(c1,c2,c3) SELECT c1,c2,c3 FROM old.table1;
sqlite> COMMIT;
sqlite> DETACH DATABASE old;

↑

CentOS5.xでpecl sqliteのインストール †

PHP: SQLite - Manual はSQLite 2.xまでで、3.xには対応しない。3.xはPHP: PDO_SQLITE DSN - Manual を使う

記事
- pxt | yumで入れたPHP5でsqliteを使う～phpize, pecl, sqlite～

通常のインストールではエラーが出てインストールできない

pecl install sqlite

running: phpize
Cannot find config.m4.
Make sure that you run '/usr/bin/phpize' in the top level source directory of the module

ERROR: `phpize' failed

元のソースを変更する事でインストールできる。

sqlite.c.1.03.patch

cd /tmp
wget http://pecl.php.net/get/SQLite-1.0.3.tgz
tar xzf SQLite-1.0.3.tgz
cd SQLite-1.0.3
phpize
./configure

patch < sqlite.c.1.03.patch

make
make install

cat > /etc/php.d/sqlite.ini << EOS
extension=sqlite.so
EOS

service httpd restart

確認
php -m | grep sqlite                                            
pdo_sqlite
sqlite

↑

複数のDBを連結して使用 †

複数のDBを連結して使用する(ATTACH) - Life with IT
attachの最大は SQLITE_MAX_ATTACHED:10 http://www.sqlite.org/limits.html

dbファイル名に'.'が入っている場合は「'」「"」で囲む必要がある

sqlite3 foo.db

sqlite> CREATE TABLE item (id INT, name text);
sqlite> .tables
item
sqlite> INSERT INTO item VALUES (1, 'item1');
sqlite> INSERT INTO item VALUES (2, 'item1');
sqlite> .exit

----
sqlite3 bar.db

sqlite> CREATE TABLE item (id INT, name text);
sqlite> .tables
item
sqlite> INSERT INTO item VALUES (3, 'item3');
sqlite> INSERT INTO item VALUES (4, 'item4');

----
sqlite> attach 'foo.db' AS foo;
sqlite> attach 'bar.db' AS bar;
sqlite> SELECT * FROM foo.item UNION ALL SELECT * FROM bar.item;
1|item1
2|item2
3|item3
4|item4
sqlite> detach bar;
sqlite> detach foo;