SQLite
2008/4/10更新
対応バージョン: 3.5.7
クエリのデフォルトの出力は通常1レコードにつき1行で、ヘッダなし、区切り文字が「|」である。
sqlite> select * from customer; 1|foo|01-234-5678|park 2|bar|99-999-9999|apart 3|baz||
この出力形式を変更するにはまず現在の設定を.showで確認し、必要に応じて各種設定を変更するとよい。
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:
以下、それぞれの設定を説明する。
.echo ON|OFF
コマンドechoのON/OFFを切り替える。
sqlite> .echo ON sqlite> select * from customer; select * from customer; ← コマンドecho 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz||
.explain ON|OFF
EXPLAIN(説明用)に適した設定に複数パラメータをまとめて変更する。
.explain OFF(デフォルト)
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite> select * from customer;
1|foo|01-234-5789|park
2|bar|99-999-9999|apart
3|baz||
.explain ON
sqlite> .explain ON
sqlite> .show
echo: off
explain: on ← 変更
headers: on ← 変更
mode: explain ← 変更
nullvalue: ""
output: stdout
separator: "|"
width: 4 13 4 4 4 13 2 13 ← 変更
sqlite> select * from customer;
id name tel addr
---- ------------- ---- ----
1 foo 01-234-5789 park
2 bar 99-999-9999 apart
3 baz
.headers ON|OFF
ヘッダ出力のON/OFFを切り替える。
sqlite> .headers ON sqlite> select * from customer; id|name|tel|address ← ヘッダ 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz||
.mode
.modeを使うことでデータの出力形式を変更できる。
以下、指定可能な出力形式を示す。
csv
カンマ区切りのCSVとして出力する。
sqlite> .mode csv customer sqlite> select * from customer; 1,foo,01-234-5789,park 2,bar,99-999-9999,apart 3,baz,"",""
.nullvalueの設定でNULL値の代替文字列を指定できる。
sqlite> .nullvalue "N/A" sqlite> select * from customer; 1,foo,01-234-5789,park 2,bar,99-999-9999,apart 3,baz,N/A,N/A
column
カラム毎にスペースで区切って桁揃えをしつつ出力する。
sqlite> .mode column customer sqlite> select * from customer; 1 foo 01-234-5789 park 2 bar 99-999-9999 apart 3 baz
各カラムの桁は.widthで指定できる。各カラムともデフォルトは0(自動調整)。
sqlite> .width 3 8 12 16 customer sqlite> select * from customer; 1 foo 01-234-5789 park 2 bar 99-999-9999 apart 3 baz
html
HTMLのtableタグとして出力する。
sqlite> .mode html customer sqlite> select * from customer; <TR><TD>1</TD> <TD>foo</TD> <TD>01-234-5789</TD> <TD>park</TD> </TR> <TR><TD>2</TD> <TD>bar</TD> <TD>99-999-9999</TD> <TD>apart</TD> </TR> <TR><TD>3</TD> <TD>baz</TD> <TD></TD> <TD></TD> </TR>
insert
SQLのinsert文として出力する。
sqlite> .mode insert customer sqlite> select * from customer; INSERT INTO customer VALUES(1,'foo','01-234-5789','park'); INSERT INTO customer VALUES(2,'bar','99-999-9999','apart'); INSERT INTO customer VALUES(3,'baz','','');
line
1カラムにつき1行出力する。
sqlite> .mode line customer
sqlite> select * from customer;
id = 1
name = foo
tel = 01-234-5789
address = park
id = 2
name = bar
tel = 99-999-9999
address = apart
id = 3
name = baz
tel =
address =
list
1レコードにつき1行出力するデフォルトの形式である。
sqlite> .mode list customer sqlite> select * from customer; 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz||
.separatorで区切り文字を変更できる。
個々のテーブルに対する変更はできず、全てのテーブルが対象となる。
sqlite> .separator ":" sqlite> select * from customer; 1:foo:01-234-5789:park 2:bar:99-999-9999:apart 3:baz::
tabs
カラム毎にタブで区切って出力する。
sqlite> .mode tab customer sqlite> select * from customer; 1 foo 01-234-5789 park 2 bar 99-999-9999 apart 3 baz
tcl
Tclのリスト形式で出力する。
sqlite> .mode tcl customer sqlite> select * from customer; "1" "foo" "01-234-5789" "park" "2" "bar" "99-999-9999" "apart" "3" "baz" "" ""
.output stdout|<ファイル名>
通常のstdoutへの出力をファイルに切り替える。
qlite> select * from customer; 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz|| sqlite> .output data.out sqlite> select * from customer; (何も出力されない) sqlite> .q % cat data.out 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz||
2008/1/28更新
対応バージョン: 3.5.4
テーブル作成
テーブルの作成にはcreate table文を使う。
ここでは例として顧客に関するテーブルを作成する。
sqlite> create table customer( ...> id integer, ...> name text, ...> tel text, ...> address text);
データ挿入
続けてデータを挿入する。
これにはファイルからデータを取り込む方法とSQLのinsert文を使う方法がある。
まず外部ファイルからデータを取り込んでみる。
まず/tmp/data.txtというファイルを以下の内容で作成する。区切り文字は「|」にする。
1|foo|01-234-5678|park
次にこのファイルを.import文で読み込む。
sqlite> .import /tmp/data.txt customer
読み込まれたかどうか確認する。
sqlite> select * from customer; 1|foo|01-234-5678|park
次にSQL文でデータをインサートしてみる。
sqlite> insert into customer values(2,"bar","99-999-9999","apart"); ← 全カラム sqlite> insert into customer(id,name) values(3,"baz"); ← 特定のカラム
結果、3レコードがインサートされる。
sqlite> select * from customer; 1|foo|01-234-5678|park 2|bar|99-999-9999|apart 3|baz||
どちらの方法も結果は同じなので、目的に応じて使い分けるとよい。
2009/7/9更新
対応バージョン: 3.5.7
SQLiteの「.dump」を使い、テーブル毎にCREATE TABLE文を含むそのテーブルの全ての構成要素を抽出する。
使用方法
-a
指定したDBに対して全テーブル抽出
-f
% unload
usage: unload [-a|-f conf_file] db
-a : unload all tables
-f conf_file : unload specified tables
(*1) エラー処理はきちんとやっていない(指定DBが本当にDBかどうか、ディスクに空きがない場合の対応など)
(*2) 純粋にデータだけ抽出する場合と違い、CREATE TABLE文なども含まれるのでテーブル自体を再作成する必要が出てきた時など便利である。
ソース
#!/bin/sh
###############################################################################
# Unload SQLite table
###############################################################################
#
# Init
#
LANG=C
LC_ALL=C
toolName=`basename $0`
#
# Get now date/time
#
nowDate=`date '+%Y%m%d'`
nowTime=`date '+%H%M'`
now="${nowDate}-${nowTime}"
#
# Check parameter
#
opt=$1
case ${opt} in
"-a")
existConf="OFF"
db=$2
;;
"-f")
existConf="ON"
confFile=$2
db=$3
if [ ! -f ${confFile} ]; then
echo "${confFile} : no such file"
exit 1
fi
;;
*)
db=""
;;
esac
if [ -z ${db} ]; then
echo "usage: ${toolName} [-a|-f conf_file] db"
echo " -a : unload all tables"
echo " -f conf_file : unload specified tables"
exit 1
fi
if [ ! -f $db ]; then
echo "${db} : no such database"
exit 1
fi
#
# Decide target tables
#
if [ ${existConf} = "ON" ]; then
targetTables=`awk '$0 !~ /^#/ {print $1}' ${confFile}`
else
targetTables=`echo .tables | sqlite3 ${db}`
fi
#
# Unload table
#
echo "Unload tables to \"${db}-<table>-${now}.dump\""
set $targetTables
numTargets=$#
cnt=1
for target in ${targetTables}
do
echo $cnt $numTargets $target | awk '{printf(" [%04d/%04d] %s ... ",$1,$2,$3)}'
backupFile="${db}-${target}-${now}.dump"
echo ".dump ${target}" | sqlite3 ${db} > ${backupFile}
echo "done"
cnt=`expr ${cnt} + 1`
done
使用例
% sqlite3 test.db sqlite> .tables customer purpose test sqlite> .q % unload -a test.db Unload tables to "test.db-<table>-20090709-1409.dump" [0001/0003] customer ... done [0002/0003] purpose ... done [0003/0003] test ... done % ls -1 *.dump test.db-customer-20090709-1409.dump test.db-purpose-20090709-1409.dump test.db-test-20090709-1409.dump % more test.db-customer-20090709-1409.dump BEGIN TRANSACTION; CREATE TABLE customer( id integer, name text, tel text, address text); INSERT INTO "customer" VALUES(1,'foo','01-234-5789','park'); INSERT INTO "customer" VALUES(2,'bar','99-999-9999','apart'); INSERT INTO "customer" VALUES(3,'baz',NULL,NULL); COMMIT; % cat <<END > conf customer purpose END % unload -f conf test.db Unload tables to "test.db-<table>-20090709-1412.dump" [0001/0002] customer ... done [0002/0002] purpose ... done
2008/4/10更新
対応バージョン: 3.5.7
SQL文の前にexplainキーワードを付けると実際のSQLを実行せず内部で実行される仮想マシン命令を出力するのでプログラムのデバッグなどに役立つ。
sqlite> select * from customer; 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz|| sqlite> .explain ← クエリの出力形式をexplain(説明用)に設定 sqlite> explain select * from customer; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- -- -- -- ------------------------------- -- ------- 0 Trace 0 0 0 explain select * from customer; 00 1 Goto 0 13 0 00 2 OpenRead 0 2 0 00 3 SetNumColumns 0 4 0 00 4 Rewind 0 11 0 00 5 Column 0 0 1 00 6 Column 0 1 2 00 7 Column 0 2 3 00 8 Column 0 3 4 00 9 ResultRow 1 4 0 00 10 Next 0 5 0 00 11 Close 0 0 0 00 12 Halt 0 0 0 00 13 Transaction 0 0 0 00 14 VerifyCookie 0 10 0 00 15 TableLock 0 2 0 customer 00 16 Goto 0 2 0 00
2008/4/10更新
対応バージョン: 3.5.4
データベースやテーブルに関する情報はSQLiteの管理コマンド(先頭が「.」で始まる)で取得できる。
以下、主なものについて説明する。
.databases
接続中のデータベースの名前とファイル名の一覧を表示する。
sqlite> .databases seq name file --- --------- -------------------------------- 0 main /home/foo/test.db
.tables [<パターン>]
テーブルの一覧を表示する。引数でテーブル名のパターンを指定可能。
sqlite> .tables customer purpose sqlite> .tables tom customer
.schema [<パターン>]
テーブル定義(*)を表示する。引数でテーブル名のパターンを指定可能。
sqlite> .schema customer CREATE TABLE customer( id integer, name text, tel text, address text);
(*) 正確にいうとテーブルを定義する際のCREATE文
.indices <テーブル>
指定したテーブルに関する全てのインデックス名を表示する。
sqlite> .indices foo foo_idx_1
2008/4/10更新
対応バージョン: 3.5.7
外部ファイルに記述されているSQLを実行するには以下のような方法がある。
SQLiteの管理コマンド.readを使用する
% cat select.sql select * from customer; % sqlite3 test.db sqlite> .read select.sql 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz||
sqlite3コマンドに直接引き渡す
% sqlite3 test.db < select.sql 1|foo|01-234-5678|park 2|bar|99-999-9999|apart 3|baz||
ヒアドキュメントを使用してシェルスクリプト内に記述する
% cat select.sh #!/bin/sh db=$1 sqlite3 $1 <<END select * from customer; END % sh ./select.sh test.db 1|foo|01-234-5678|park 2|bar|99-999-9999|apart 3|baz||
2008/7/1更新
対応バージョン: 3.5.7
DB連結(ATTACH)
既に接続しているDBに別のDBを連結する場合はATTACHを使用する。
例) foo.dbにbar.dbを連結する
% sqlite3 foo.db sqlite> .tables customer purpose sqlite> attach "bar.db" as bar; sqlite> select * from bar.test; :
DB名に「.」が含まれている場合はDB名をクォーテーション(「"」or「'」)で囲まないと以下のエラーになる。
sqlite> attach bar.db as bar; SQL error: no such column: bar.db
DB連結解除(DETACH)
逆に、接続中のDBを削除するにはDETACHを使用する。
sqlite> detach bar;
2008/4/10更新
対応バージョン: 3.5.7
SQLiteではデータを削除しても領域自体は削除されず、ファイルサイズも減らない。
不要な領域を整理(削除)してファイルサイズを減らすにはvacuumコマンドを使用する。
これによりアプリケーションによってはパフォーマンスが向上する場合もある。
例) 10万レコードを挿入し、すべて削除、その後vacuumを行う
テーブル作成
% sqlite3 sample.db "create table test(data text);"
データ挿入
% echo | awk '{for(i = 1; i <= 100000; i++) \
printf("sqlite3 sample.db \"insert into test values(%d);\"\n", i)}' | sh
% ls -l sample.db
-rw-r--r-- ... 1454080 ... sample.db
全レコード削除
% sqlite3 sample.db "delete from test;" % ls -l sample.db -rw-r--r-- ... 1454080 ... sample.db ← サイズ変わらず
vacuum
% sqlite3 sample.db vacuum % ls -l sample.db -rw-r--r-- ... 2048 ... sample.db ← サイズ減少
2008/4/10更新
対応バージョン: 3.5.7
DBを作成するには、単に作成したいDB名を指定してsqlite3を実行するだけである。
% sqlite3 test.db : sqlite>
ただし、テーブル作成などDB内に何か作成する行為を行わないとDBそのものも作成されない。
sqlite> create table foo ... sqlite> .tables foo sqlite> .q
DBはファイルとして作成される。
% file test.db test.db: SQLite 3.x database
2008/4/10更新
対応バージョン: 3.5.7
SQLiteのバージョン3はデータ自身の種類を表す「記憶クラス(データ型)」とカラムの種類を表す「カラム型」という概念を持つ。
基本的にどの記憶クラスをどのカラム型に格納してもよいが、カラム型によっては格納データが加工される場合がある。
以下、記憶クラスとカラム型の一覧を示す。
記憶クラス
NULLクラス
NULL値。
INTEGERクラス
符号付整数。最大8バイトで格納される。
REALクラス
浮動小数点値。8バイトのIEEE浮動小数点数として格納される。
TEXTクラス
テキスト文字列。エンコーディングはUTF-8、UTF-16BE、UTF-16-LEのいずれかが使用される。
BLOBクラス
入力されたデータがそのまま格納される。
カラム型
TEXT型
TEXT型にはNULLクラス、TEXTクラス、BLOBクラスの値が格納できる。
数値データを格納するとテキストの値に変換される。
NUMERIC型
NUMERIC型には全てのクラスの値が格納できる。
テキストデータを格納しようとした場合、まずINTEGERクラス又はREALクラスの値に変換しようとし、成功すればそのクラスの値として格納され、失敗すればTEXTクラスの値として格納される。
また型を指定しない場合もNUMERIC型になる。
INTEGER型
INTEGER型は基本的にNUMERIC型と同じである。
ただし小数点を含む値を格納しようとした場合は整数の値に変換される。
REAL型
REAL型も基本的にNUMERIC型と同じであるが、整数を格納しようとした場合は小数点が付与されて格納される。
NONE型
NONE型は型定義のない型で、どのクラスのデータを格納する時にもデータ変換をしない。
2008/5/10更新
対応バージョン: 3.5.7
通常テーブルやインデックスがロックされていると即座にエラーを返すが.timeoutコマンドによりロック待ちの時間を変更することができる。
デフォルト(.timeoutは0)
sqlite> .tables Error: database is locked ← 即座にエラー
ロック待ち時間を3秒に変更
sqlite> .timeout 3000 ← 時間はミリ秒単位で指定 sqlite> .tables : (3秒待って) : Error: database is locked
2008/4/10更新
対応バージョン: 3.5.7
バックアップ
バックアップは管理コマンド.dumpを使用する。
.dumpによって出力される内容はシンプルで、CREATE文やINSERT文といったDBを再構築するためのSQL文の羅列である。
mysql> .dump [<テーブル>]
引数にテーブル名を指定すればそのテーブルに関するSQLが出力され、テーブル名を省略すれば全てのテーブルに関するSQLが出力される。
例) 2つのテーブルが存在し、最初のテーブルには3レコードが存在し、2つ目のテーブルにはデータが存在しない場合
sqlite> .tables customer purpose sqlite> select * from customer; 1|foo|01-234-5789|park 2|bar|99-999-9999|apart 3|baz|| sqlite> select * from purpose; (データなし) sqlite> .dump BEGIN TRANSACTION; CREATE TABLE customer( id integer, name text, tel text, address text); INSERT INTO "customer" VALUES(1,'foo','01-234-5789','park'); INSERT INTO "customer" VALUES(2,'bar','99-999-9999','apart'); INSERT INTO "customer" VALUES(3,'baz',NULL,NULL); CREATE TABLE purpose( id integer, desc text); COMMIT; sqlite> .output test.dump ← 出力先としてファイルを指定 sqlite> .dump ← 上記ファイルに出力
また、以下のようにすればコマンドラインから簡単にバックアップファイルが作成できる。
% echo ".dump" | sqlite3 test.db | gzip -c > test.dump.gz
リストア
リストアはいったん全てのデータを削除(つまりDBファイル削除)した後にバックアップファイルを読み込ませるだけでよい。
例)
% rm test.db % sqlite3 test.db < test.dump
バックアップファイルは単純なSQL文の羅列なので、必要に応じてリストアするテーブルを減らしたり挿入するデータに修正を加えることも可能である。
2010/5/15更新
対応バージョン: 3.6.23.1
公式サイト
準備
あらかじめインストールしておくもの
Tcl
SQLite同梱のTclバインディングを使用する場合のみ
導入に必要なもの
sqlite-3.6.23.1.tar.gz
導入OS
Ubuntu 9.10
インストール
配布ファイル展開
% tar zxvf sqlite-3.6.23.1.tar.gz % cd sqlite-3.6.23.1
make,インストール
% ./configure % make % sudo make install
インストール物 (man,infoは除く)
/usr/local/bin/sqlite3 /usr/local/lib/libsqlite3.* /usr/local/include/sqlite3.h /usr/local/include/sqlite3ext.h
各種設定
SQLiteに設定は不要である。