sqlite3 – SQLiteデータベースのコマンドラインツール

SQLiteはファイルを作成して、そのファイルをデータベースとして扱う軽量なデータベース管理システムになります。
sqlite3はSQLiteのコマンドラインツールになります。プログラミング言語のAPIからSQLiteを利用することもできますが、簡単に利用するためにコマンドラインツールを利用するのも選択肢の一つになります。
また、この記事でのsqlite3はパッケージからインストールしたものを利用します。

1 インストール
2 sqlite3コマンドの構文
3 sqlite3の起動
- 3.1 対話的な画面の起動
- 3.2 ファイルを利用してSQLを実行
4 データの挿入とトランザクション
5 外部キー制約
6 オートバキュームの設定
7 SQLiteのデータ型
8 参考

インストール

sqlite3のインストールは例えば、Ubuntu環境では以下のようにインストールできます。

sudo apt install sqlite3

1	sudo apt install sqlite3

sqlite3コマンドの構文

sqlite3 [options] [databasefile] [SQL]

1	sqlite3 [options] [databasefile] [SQL]

ファイルからSQLを読み取る例

sqlite3 mydata.db < file.sql

1	sqlite3 mydata.db < file.sql

cat file.sql | sqlite3 mydata.db

1	cat file.sql \| sqlite3 mydata.db

sqlite3の起動

対話的な画面の起動

引数のSQLを省略すると対話的な画面で起動することができます。

$ sqlite3 mydata.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite>

$ sqlite3 mydata.db

SQLite version 3.40.1 2022-12-28 14:03:47

Enter ".help" for usage hints.

sqlite>

終了する場合は、.quitコマンド又は.exitコマンドで終了できます。ドットコマンドはSQLiteを操作するためのメタコマンドになります。

sqlite> .quit
$

1 2	sqlite> .quit $

ファイルを利用してSQLを実行

SQLはコマンドラインで入力する他に標準入力から入力する方法があります。

table.sql

CREATE TABLE data_table (
   num1 INTEGER
  ,num2 INTEGER
  ,num3 INTEGER
);

CREATE TABLE data_table (

num1 INTEGER

,num2 INTEGER

,num3 INTEGER

);

コマンド例と実行結果

$ sqlite3 mydata.db < table.sql 
$ sqlite3 mydata.db '.schema'
CREATE TABLE data_table (
   num1 INTEGER
  ,num2 INTEGER
  ,num3 INTEGER
);

$ sqlite3 mydata.db < table.sql

$ sqlite3 mydata.db '.schema'

CREATE TABLE data_table (

num1 INTEGER

,num2 INTEGER

,num3 INTEGER

);

sqlite3コマンドの構文のSQLはSQLiteのメタコマンドも利用できます。

データの挿入とトランザクション

多くのデータを挿入したい場合、SQLiteではトランザクションを利用しなければデータの挿入時間が大幅に変わります。
例えば、以下のようなデータファイルdata.datから

data.dat

0	0	0
1	1	1
2	2	2
(中略)
499998	499998	499998
499999	499999	499999

0 0 0

1 1 1

2 2 2

(中略)

499998 499998 499998

499999 499999 499999

1000行ほどトランザクションを利用せずに

INSERT INTO data_table VALUES (0,0,0);
INSERT INTO data_table VALUES (1,1,1);
INSERT INTO data_table VALUES (2,2,2);
(中略)
INSERT INTO data_table VALUES (998,998,998);
INSERT INTO data_table VALUES (999,999,999);

INSERT INTO data_table VALUES (0,0,0);

INSERT INTO data_table VALUES (1,1,1);

INSERT INTO data_table VALUES (2,2,2);

(中略)

INSERT INTO data_table VALUES (998,998,998);

INSERT INTO data_table VALUES (999,999,999);

のようなSQLをコマンドで生成して挿入しようとすると、私の環境では

$ time head -n1000 data.dat | awk '{print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}' | sqlite3 mydata.db

real	0m9.199s
user	0m0.139s
sys	0m0.570s

$ time head -n1000 data.dat | awk '{print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}' | sqlite3 mydata.db

real 0m9.199s

user 0m0.139s

sys 0m0.570s

約9秒かかりました。これでは、50万行のデータを全てデータベースに入れるには時間がかかりすぎてしまいます。

しかし、以下のように

BEGIN;
INSERT INTO data_table VALUES (0,0,0);
INSERT INTO data_table VALUES (1,1,1);
(中略)
INSERT INTO data_table VALUES (998,998,998);
INSERT INTO data_table VALUES (999,999,999);
END;

BEGIN;

INSERT INTO data_table VALUES (0,0,0);

INSERT INTO data_table VALUES (1,1,1);

(中略)

INSERT INTO data_table VALUES (998,998,998);

INSERT INTO data_table VALUES (999,999,999);

END;

BEGIN～END文で挟んで、トランザクションを利用すると、

$ time head -n1000 data.dat | awk 'BEGIN{print "BEGIN;"}; {print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}; END{print "END;"}' | sqlite3 mydata.db

real	0m0.403s
user	0m0.022s
sys	0m0.006s

$ time head -n1000 data.dat | awk 'BEGIN{print "BEGIN;"}; {print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}; END{print "END;"}' | sqlite3 mydata.db

real 0m0.403s

user 0m0.022s

sys 0m0.006s

挿入時間は0.4秒ほどになり、50万行のデータを全て挿入すると

$ time cat data.dat | awk 'BEGIN{print "BEGIN;"}; {print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}; END{print "END;"}' | sqlite3 mydata.db

real	0m7.206s
user	0m7.291s
sys	0m0.182s

$ time cat data.dat | awk 'BEGIN{print "BEGIN;"}; {print sprintf("INSERT INTO data_table VALUES (%d,%d,%d);",$1,$2,$3)}; END{print "END;"}' | sqlite3 mydata.db

real 0m7.206s

user 0m7.291s

sys 0m0.182s

約7秒ほどと現実的な時間でデータを全て挿入できます。

SQLiteでのトランザクションの構文はTransactionを参照してください。

また、データベースにデータを入れる方法としては.importコマンドを利用する方法もあります。

外部キー制約

外部キー制約はsqlite3を起動するとき常にOFFの状態になっています。SQLiteで外部キー制約の設定を確認する方法とONにする方法は以下のようになります。

sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1

sqlite> PRAGMA foreign_keys;

sqlite> PRAGMA foreign_keys = ON;

sqlite> PRAGMA foreign_keys;

常にONにしたい場合は~/.sqlitercにPRAGMA文を書き込むか、

~/.sqliterc

PRAGMA foreign_keys = ON;

1	PRAGMA foreign_keys = ON;

sqlite3コマンドを実行するときに-cmdオプションを付けて、

sqlite3 -cmd 'PRAGMA foreign_keys=ON;' [databasefile] [SQL]

1	sqlite3 -cmd 'PRAGMA foreign_keys=ON;' [databasefile] [SQL]

のように実行する方法があります。

~/.sqlitercは設定を行うドットコマンドやSQL文を記述し、sqlite3コマンドを実行するときにその内容が最初に実行されます。

オートバキュームの設定

オートバキュームの設定とFULLのオートバキュームの設定の方法は以下のようになります。こちらの設定はデータベースファイル毎に最初の一度設定すれば良いものになります。
ただし、この設定はデータベースファイルを作成して、テーブルを作成する前に予め設定しておく必要があります。

sqlite> PRAGMA auto_vacuum;
0
sqlite> PRAGMA auto_vacuum = FULL;
sqlite> PRAGMA auto_vacuum;
1

sqlite> PRAGMA auto_vacuum;

sqlite> PRAGMA auto_vacuum = FULL;

sqlite> PRAGMA auto_vacuum;

なので、auto_vacuumを設定したい場合はデータベースファイルを作成すると同時にPRAGMA文を入れておくとauto_vacuumの設定ができます。

$ ls mydata.db  # ファイルの存在チェック
ls: 'mydata.db' にアクセスできません: そのようなファイルやディレクトリはありません
$ sqlite3 mydata.db 'PRAGMA auto_vacuum=FULL'
$ sqlite3 mydata.db 'PRAGMA auto_vacuum'
1

$ ls mydata.db # ファイルの存在チェック

ls: 'mydata.db' にアクセスできません: そのようなファイルやディレクトリはありません

$ sqlite3 mydata.db 'PRAGMA auto_vacuum=FULL'

$ sqlite3 mydata.db 'PRAGMA auto_vacuum'

オートバキュームが設定されていない場合、SQLiteでDELETE文で大量のデータを削除するとデータベース上ではレコードが消えますが、空いたデータベースページが残り、データベースファイル自体のサイズは変更されません。

オートバキュームはその空いたデータベースページを自動で処理してデータベースファイルのサイズを減らすことができる設定になります。

この設定のデメリットとして、オートバキュームの動作によって、予期せぬ遅延が発生してプログアムに影響したり、データベースファイルが断片化しやすくなる可能性がありますが、AndroidでSQLiteを利用する際はauto_vacuumの設定がFULLになるようにビルドされたSQLiteが利用されているので、用途に対するシステム要件が厳しくない限り、設定しておいても良いと思います。

ただし、データベースファイルを長い間利用したり、頻繁な挿入・削除・更新を行なうとデータベースファイルが断片化していくので、そのときはVACUUM文を利用してデータベースファイルを再構築することを考えるべきかもしれません。