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に設定は不要である。