はじめに
職場の後輩から「SQLite3 "dot commands" が知らない間に増えているんですが・・・」と教えてもらって、ちょっと調べたら、SQLite3 から Excel が呼び出せたりと有用なコマンドが増えていますので、使い方をまとめてみました。
OSS データベースの SQLite は、ユーザが SQLite データベースで SQL ステートメントを入力し実行することを可能にするsqlite3(Windowsでは sqlite3.exe)という名前の Command Line Shell プログラム(以下、CLI)を提供しています。Oracle の SQL*Plus や PostgreSQL の psql に相当します。
この CLI は、通常、入力された SQL ステートメントを実行しますが、"." で始まる "dot commands" と呼ばれる特別なコマンドがあります。
この "dot commands" は、SQLite3 がリリースされた当初、22だったコマンドが、なんと最新バージョンでは 60 以上のコマンドが存在します。SQLite本家の Command Line Shell For SQLite というページでコマンドの説明がされていますが、最新ではなく、すべて紹介されている訳ではないと思われます。また、Qiitaをはじめ すべてに解説した資料が見つかりませんでしたので、ここではデフォルトのコンパイルで使える、すべてのコマンドの使い方についてまとめています。
使用している SQLite3 のバージョンは 3.31.1 です。
dot-commands 一覧
dot-commands をクリックしますと詳細な説明へ移動します。
| コマンド名 | 説明 |
|---|---|
| .archive ... | アーカイブを管理 |
| [.auth] ON | OFF | authorizer コールバックの表示。通常ビルドでは使用不可 |
| .backup ?DB? FILE | データベース(デフォルトは "main")をファイルにバックアップ |
| .bail on | off | エラー発生時の停止を制御 (デフォルトは OFF) |
| .binary on | off | ファイル出力モードを設定 (デフォルトは OFF) |
| .cd DIRECTORY | 作業ディレクトリを変更 |
| .changes on | off | SQLによる更新レコード数の表示を制御 |
| .check GLOB | テスト結果をGLOB形式の文字列でチェック |
| .clone NEWDB | クローンデータベースを作成 |
| .databases | データベース一覧を表示 |
| .dbconfig ?op? ?val? | データベース設定を表示または設定 |
| .dbinfo ?DB? | データベース情報を表示 |
| .dump ?TABLE? ... | データベースを構成するSQLを表示 |
| .echo on | off | エコー表示を制御 |
| .eqp on | off | full | ... | EXPLANE QUERY PLAN を自動で実行する機能を制御 |
| .excel | SQLを実行後にExcelを起動して出力 |
| .exit ?CODE? | リターンコードを付与して、sqlite3 CLIを終了 |
| .expert | クエリ向けインデックス作成の提案 |
| .explain ?on | off | auto? | EXPLAIN フォーマットモードを設定 (デフォルトは auto) |
| .filectrl CMD ... | さまざまなsqlite3_file_control()オペレーションを実行 |
| .fullschema ?--indent? | スキーマとSTATテーブルを表示 |
| .header on | off | ヘッダー表示を制御 |
| .help ?-all? ?PATTERN? | ヘルプを表示 |
| .import FILE TABLE | ファイルからテーブルにデータをインポート |
| .imposter INDEX TABLE | imposter テーブルを作成 |
| .indexes ?TABLE? | インデックス一覧を表示 |
| .limit ?LIMIT? ?VAL? | SQLITE_LIMIT の制限値を表示または設定 |
| .lint OPTIONS | 潜在的なスキーマ問題点をレポート |
| .load FILE ?ENTRY? | 拡張ライブラリをロード |
| .log FILE | off | ログ出力を制御 |
| .mode MODE ?TABLE? | フォーマットモードを設定 |
| .nullvalue STRING | NULL値の代替文字列の設定をします。 |
| .once (-e | -x | FILE) | SQL実行後、ファイルに出力 |
| .open ?OPTIONS? ?FILE? | データベースをオープン |
| .output ?FILE? | 出力ファイルを指定 |
| .parameter CMD ... | SQLパラメータ・バインディングを管理 |
| .print STRING... | 文字列を表示 |
| .progress N | opcode 進捗状況表示を設定 |
| .prompt MAIN CONTINUE | プロンプト表示を変更 |
| .quit | sqlite3 CLI を終 |
| .read FILE | SQLファイルを読み込みで実行 |
| .recover | データベースを修復 |
| .restore ?DB? FILE | データベースを復元 (デフォルトは "main") |
| .save FILE | インメモリデータベースをファイルに保存 |
| .scanstats on | off | sqlite3_stmt_scanstatus() メトリックの設定。通常ビルドでは使用不可 |
| .schema ?PATTERN? | スキーマを表示 |
| .selftest ?OPTIONS? | selftest テーブルの中に定義されたセルフテストを実行 |
| .separator COL ?ROW? | 区切り文字を指定 |
| .sha3sum ... | SHA3ハッシュ値を表示 |
| .shell CMD ARGS... | 外部コマンドを実行 |
| .show | 設定値を表示 |
| .stats ?on | off? | ステータス表示を制御 |
| .system CMD ARGS... | システムコマンドを実行 |
| .tables ?TABLE? | テーブルの一覧を表示 |
| .testcase NAME | testcase-out.txtに出力 |
| .testctrl CMD ... | さまざまな sqlite3_test_control() オペレーションを実行 |
| .timeout MS | タイムアウト時間を設定 |
| .timer on | off | SQL文の実行時間を測定するタイマーを設定 |
| .trace ?OPTIONS? | 実行したSQL文をトレースを設定 |
| .vfsinfo ?AUX? | 最上位 VFS の情報を表示 |
| .vfslist | VFS の一覧を表示 |
| .vfsname ?AUX? | VFS スタック名を出力 |
| .width NUM1 NUM2 ... | カラム幅を設定 |
SQLite3 での事前準備
ここで書かれている "dot commands" の使い方について確認するために、以下の SQL を CLI で実行して、テーブルとデータを作成して下さい。
$ sqlite3
sqlite> CREATE TABLE emp(
...> id INTEGER PRIMARY KEY,
...> name1 TEXT,
...> name2 TEXT );
sqlite> INSERT INTO emp(id, name1, name2) VALUES (1, "Toshiba", "Taro");
sqlite> INSERT INTO emp(id, name1, name2) VALUES (2, "Kannai", "Jiro");
sqlite> INSERT INTO emp(id, name1, name2) VALUES (3, "Mishima", "Saburo");
.archive (アーカイブ管理)
SQLite アーカイブ・フォーマットをサポートします。(.archive , .ar)
このコマンドは、unixシステムの tar コマンドに似ています。以下のコマンドが可能です。
sqlite> .ar -cf ARCHIVE count.sql
sqlite> .shell ls -l AR*
-rw-r--r--. 1 sqlite sqlite 1536 4月 14 20:51 ARCHIVE
sqlite> .ar -tf ARCHIVE
count.sql
.backup (バックアップ)
オープンしているメインデータベースのバックアップを行います。(.backup , .bac)
sqlite> .backup backup1.db
sqlite> .shell ls -l back*
-rw-r--r--. 1 sqlite sqlite 8192 3月 14 18:58 backup1.db
.bail (エラー発生時の停止制御)
エラーが起きた際に停止するかどうか ON, OFF で制御します。(.bail, .bai)
ON の場合、エラーが起きた時点で、処理を停止します(デフォルトは OFF)。バッチ処理などで効果があると思います。
[sqlite@localhost work]$ cat error.sql
SELECT count() FROM emp;
SELE;
SELECT * FROM emp WHERE id=2;
[sqite@localhost work]$ sqlite3 test1.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .read count.sql
3
Error: near line 2: near "SELE": syntax error
2|Kannai|Jiro
sqlite> .bail on
sqlite> .read count.sql
3
Error: near line 2: near "SELE": syntax error
例では、SQLファイルは2行目でエラーが発生するようにしています。
デフォルトの場合、エラー発生後、3行目のSQLが実行されます.bail on で設定した場合、エラー発生後、処理が停止されて3行目のSQLが実行されません。
.binary (ファイル出力モードの設定)
ファイル出力をバイナリモードにするかどうかを ON, OFF で設定します。(.binary , .bin)
ON の場合、ファイル出力をバイナリモードにします(デフォルトは OFF)。Windowsだけ対応しています。
sqlite> SELECT * FROM emp;
sqlite> .output binary.log
sqlite> .binary on
sqlite> SELECT * FROM emp;
.cd (作業ディレクトリの変更)
作業ディレクトリを変更します。(.cd)
sqlite> .cd ..
.changes (更新レコード数の表示制御)
更新レコード数の表示制御を ON, OFF で行います。(.changes , .cha)
sqlite> .changes on
sqlite> UPDATE emp SET name1='Kitajima' WHERE id=3;
changes: 1 total_changes: 1
.check (テスト結果のチェック)
.testcase の出力が オプションで指定したGLOB形式の文字列と一致しない場合、FAILEDとします。(.check , .che)
sqlite> .shell cat testcase-out.txt
0 errors out of 1 tests
sqlite> .check "0*"
testcase-? ok
sqlite> .check "1*"
testcase-? FAILED
Expected: [1*]
Got: [0 errors out of 1 tests
]
.clone (クローンDBの作成)
新しいデータベースファイル名を指定して、クローンデータベースを作成します。(.clone , .c)
sqlite> .clone test3.db
emp... done
sqlite> .shell ls -l test3*
-rw-r--r--. 1 sqlite sqlite 8192 3月 14 21:33 test3.db
.databases (DB一覧の表示)
データベース一覧を表示します。(.databases , .dat)
データベースをATTACHしている場合に役に立ちます。
sqlite> .databases
main: /home/sqlite/work/test1.db
.dbconfig (DB設定の表示・設定)
sqlite3_db_config() で設定できるオプションを表示、設定を行います。(.dbconfig , .dbc)
sqlite> .dbconfig
defensive off
dqs_ddl on
dqs_dml on
enable_fkey off
enable_qpsg off
enable_trigger on
enable_view on
fts3_tokenizer off
legacy_alter_table off
legacy_file_format off
load_extension on
no_ckpt_on_close off
reset_database off
trigger_eqp off
trusted_schema on
writable_schema on
オプションを設定する場合、オプション名と値を引数として与えます。
sqlite> .dbc load_extension off
load_extension off
.dbinfo (DB情報の表示)
データベースの情報(ページサイズなど)を表示します。(.dbinfo , .dbi)
データベース名を指定しますと指定されたデータベースの譲歩を表示します。
sqlite> .dbinfo
database page size: 4096
write format: 1
read format: 1
reserved bytes: 0
file change counter: 2
database page count: 2
freelist page count: 0
schema cookie: 1
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3031001
number of tables: 1
number of indexes: 0
number of triggers: 0
number of views: 0
schema size: 81
data version 2
.dump (DBを構成するSQLの表示)
データベースを構成する全てのSQLを表示します。(.dump , .d)
テーブル名を指定することで、そのテーブルを構成する全てのSQLを表示します。
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE emp(
id INTEGER PRIMARY KEY,
name1 TEXT,
name2 TEXT );
INSERT INTO emp VALUES(1,'Toshiba','Taro');
INSERT INTO emp VALUES(2,'Kannai','Jiro');
INSERT INTO emp VALUES(3,'Mishima','Saburo');
COMMIT;
.echo (エコー表示の制御)
エコー表示を ON, OFF で制御します。(.echo , .e)
sqlite> .echo on
sqlite> SELECT * FROM emp;
SELECT * FROM emp;
1|Toshiba|Taro
2|Kannai|Jiro
3|Mishima|Saburo
.eqp (自動 EXPLANE QUERY PLAN)
クエリ実行前に、EXPLANE QUERY PLAN を自動で実行する機能の制御を ON, OFF, FULL, TRIGGER で制御します。(.eqp)
FULL : EXPLANE 詳細表示を自動的に実行させます。
TRIGGER : FUUL + TRIGGER のバイトコードを表示します。
sqlite> .mode column
sqlite> .eqp full
sqlite> SELECT * FROM emp;
QUERY PLAN
`--SCAN TABLE emp
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 9 0 00 Start at 9
1 OpenRead 0 2 0 3 00 root=2 iDb=0; emp
2 Rewind 0 8 0 00
3 Rowid 0 1 0 00 r[1]=rowid
4 Column 0 1 2 00 r[2]=emp.name1
5 Column 0 2 3 00 r[3]=emp.name2
6 ResultRow 1 3 0 00 output=r[1..3]
7 Next 0 3 0 01
8 Halt 0 0 0 00
9 Transaction 0 0 1 1 01 usesStmtJournal=0
10 Goto 0 1 0 00
1 Toshiba Taro
2 Kannai Jiro
3 Mishima Sabu
.excel (Excel出力)
.excel コマンドの後、次のSQLを実行後にExcelを起動して出力します。(.excel)
sqlite> .excel
sqlite> SELECT * FROM emp;
.exit (sqlite3コマンドの終了)
リターンコードを付けて、sqlite3 シェルツールを終了します。(.exit , .ex)
sqlite> .exit 1
[sqlite@localhost work]$
.expert (インデックス作成の提案)
実行したクエリに対して、インデックス作成を提案します。(.expert , exp)
sqlite> .expert
sqlite> SELECT * FROM emp WHERE name2='jiro';
CREATE INDEX emp_idx_000c3da3 ON emp(name2);
SEARCH TABLE emp USING INDEX emp_idx_000c3da3 (name2=?)
.explain (フォーマットモードの設定)
EXPLAIN フォーマットモード を ON, OFF, AUTO で変更できます。(.explain , expl)
OFF の場合、通常のフォーマットモードで EXPLAIN PLAN の結果が表示されます。
sqlite> .explain off
sqlite> EXPLAIN SELECT * FROM emp;
0|Init|0|9|0||00|Start at 9
1|OpenRead|0|2|0|3|00|root=2 iDb=0; emp
2|Rewind|0|8|0||00|
3|Rowid|0|1|0||00|r[1]=rowid
4|Column|0|1|2||00|r[2]=emp.name1
5|Column|0|2|3||00|r[3]=emp.name2
6|ResultRow|1|3|0||00|output=r[1..3]
7|Next|0|3|0||01|
8|Halt|0|0|0||00|
9|Transaction|0|0|1|0|01|usesStmtJournal=0
10|Goto|0|1|0||00|
.filectr (sqlite3_file_control)
さまざまなsqlite3_file_control()オペレーションを実行します。
sqlite> .filectr
Available file-controls:
.filectrl size_limit [LIMIT]
.filectrl chunk_size SIZE
.filectrl persist_wal [BOOLEAN]
.filectrl psow [BOOLEAN]
.filectrl tempfilename
.filectrl has_moved
.filectrl lock_timeout MILLISEC
.fullschema (スキーマとSTATテーブルの表示)
データベース・スキーマと sqlite_stat テーブルを表示します。(.fullschema , .f)
sqlite_stat テーブル "sqlite_stat1", "sqlite_stat3", "sqlite_stat4" が存在すれば、その情報を表示します。
sqlite> .fullschema
CREATE TABLE emp(
id INTEGER PRIMARY KEY,
name1 TEXT,
name2 TEXT );
/* No STAT tables available */
.header (ヘッダー表示の制御)
クエリ結果を表示する際、ヘッダーを表示するか ON, OFFで制御します(デフォルトはOFF)。(.header , .h)
sqlite> .header on
sqlite> SELECT * FROM emp WHERE id=2;
id|name1|name2
2|Kannai|Jiro
.help (ヘルプの表示)
ドット・コマンドのヘルプを表示します。(.help , .h)
コマンドを指定することで詳細を表示することができます。
sqlite> .help .testcase
.check GLOB Fail if output since .testcase does not match
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.import (データのインポート)
ファイルからテーブルへのデータをインポートします。(.import , .i)
sqlite> shell cat test1.csv
1,aaa
2,bbb
sqlite> CREATE TABLE test1(a INTEGER, b TEXT);
sqlite> .mode csv
sqlite> .import test1.csv test1
sqlite> SELECT * FROM test1;
1,aaa
2,bbb
.imposter (imposterテーブルの作成)
指定されたインデックスから imposter テーブルを作成します。(.imposter , .impos)
sqlite> .imposter emp_i1 emp2
CREATE TABLE "emp2"("name1","name2","_ROWID_",PRIMARY KEY("name1","name2","_ROWID_"))WITHOUT ROWID;
WARNING: writing to an imposter table will corrupt the "emp_i1" index!
sqlite> SELECT * FROM emp2;
Kannai|Jiro|2
Mishima|Saburo|3
Toshiba|Taro|1
imposter テーブルについては以下をご覧下さい。
https://www.sqlite.org/imposter.html
.indexes (インデックス一覧の表示)
データベースに存在するインデックスを一覧表示します。(.indexes , .in)
sqlite> CREATE INDEX emp_i1 on emp(name1);
sqlite> .indexes
emp_i1
.limit (制限値(SQLITE_LIMIT)の表示/設定)
SQLITE_LIMIT で設定されているオプションを表示、設定を行います。(.limit)
sqlite> .limit
length 1000000000
sql_length 1000000000
column 2000
expr_depth 1000
compound_select 500
vdbe_op 250000000
function_arg 127
attached 10
like_pattern_length 50000
variable_number 999
trigger_depth 1000
worker_threads 0
オプションを設定する場合、オプション名と値を引数として与えます。
sqlite> .limit column 500
column 500
.lint (潜在的なスキーマ問題点のレポート)
潜在的なスキーマの問題点をレポートします。(.lint , .lin)
sqlite> .lint fkey-indexes
sqlite>
.load (拡張ライブラリのロード)
sqlite3 で使用できる拡張ライブラリをロードします。(.load , .lo)
sqlite> SELECT acos(0.1);
Error: no such function: acos
sqlite> .load ./libsqlitefunctions.so
sqlite> SELECT acos(0.1);
1.47062890563334
例では、拡張関数の拡張ライブラリを読み込んで、acos関数を使用しています。
拡張関数を詳しくはSQLite3 で、たった2行追加して拡張ライブラリ(拡張SQL関数)を使おう!をご覧下さい。
.log (ログ出力の制御)
ログ出力の制御を行います。(.log , .l)
ファイル名を指定するとログ出力を開始します。.log off でログ出力を止めます。
sqlite> .log log.txt -- ログ出力開始
sqlite> SELECT * FROM test1;
Error: no such table: test1
sqlite> .log off -- ログ出力終了
sqlite> SELECT * FROM test2;
Error: no such table: test2
sqlite> .q
[sqlite@localhost work]$ cat log.txt
(1) no such table: test1 in "SELECT * FROM test1;"
例では、ログ出力を停止した後のエラーは出力をされていません。
.mode (フォーマットモードの設定)
クエリ出力のフォーマットモードを設定します(デフォルトは list)。(.mode , .m)
sqlite3 プログラムでは、8種類("csv", "column", "html", "insert", "line", "list", "quote", "tabs", "tcl")の出力フォーマットが使えます。
ascii モード
sqlite> .mode ascii
sqlite> SELECT * FROM emp WHERE id=1;
1ToshibaTaro
csv モード
sqlite> .mode csv
sqlite> SELECT * FROM emp WHERE id=1;
1,Toshiba,Taro
column モード
sqlite> .mode column
sqlite> SELECT * FROM emp WHERE id=1;
1 Toshiba Taro
html モード
sqlite> .mode html
sqlite> SELECT * FROM emp WHERE id=1;
<TR><TD>1</TD>
<TD>Toshiba</TD>
<TD>Taro</TD>
</TR>
INSERT モード
sqlite> .mode insert
sqlite> SELECT * FROM emp WHERE id=1;
INSERT INTO "table" VALUES(1,'Toshiba','Taro');
line モード
sqlite> .mode line
sqlite> SELECT * FROM emp WHERE id=1;
id = 1
name1 = Toshiba
name2 = Taro
list モード
sqlite> .mode list
sqlite> SELECT * FROM emp WHERE id=1;
1|Toshiba|Taro
quote モード
sqlite> .mode quote
sqlite> SELECT * FROM emp WHERE id=1;
1,'Toshiba','Taro'
tabs モード
sqlite> SELECT * FROM emp WHERE id=1;
1 Toshiba Taro
tcl モード
sqlite> .mode tcl
sqlite> SELECT * FROM emp WHERE id=1;
"1" "Toshiba" "Taro"
.nullvalue (NULL出力文字列の設定)
NULL値の代わりに出力する文字列を設定します。(.nullvalue , .n)
sqlite> INSERT INTO emp(id) VALUES(4);
sqlite> SELECT * FROM emp WHERE id=4;
4||
sqlite> .nullvalue NONE
sqlite> SELECT * FROM emp WHERE id=4;
4|NONE|NONE
例では、NULL値の場合、NONE という文字列が表示されます。
.once (SQLコマンドのファイルへ出力)
1つのSQLコマンドの実行結果をファイルに出力します。(.once , .on)
オプションは以下のとおりです。
ファイル名 ファイルに結果を出力します。
-e Invoke system text editor
-x スプレッドシートを開きます。
sqlite> .once test1.txt
sqlite> select * from emp;
sqlite> .shell cat test1.txt
1|Toshiba|Taro
2|Kannai|Jiro
3|Mishima|Saburo
.open (DBオープン)
データベースファイルを指定してオープンします。(.open , .op)
.open オプション ファイル名
sqlite> .open test2.db
sqlite> .databases
main: /home/sqlite/work/test2.db
オプションは以下のとおりです。
--append ファイル終端にデータベースを追加するためにappendvfsを使用
--deserialize sqlite3_deserialize()を使用して、メモリへロード
--hexdb インメモリ・データベースとして dbtotxt 出力をロード
--maxsize N --hexdb か --deserialize でロードする際の最大サイズ
--new 空データベースとして新規ファイルをオープン
--nofollow シンボリックリンクの場合、オープンしない
--readonly 読み込み専用でオープン
--zip ZIPファイルをオープン
.output (出力ファイルの指定)
クエリ結果などを書き出す出力ファイルを指定します。(.output, )
sqlite> .output output.txt
sqlite> SELECT * FROM emp WHERE id=3;
sqlite> .q
[sqlite@localhost work]$ cat output.txt
3|Mishima|Saburo
.parameter (SQLパラメータ・バインディングの管理)
SQLパラメータ・バインディングの管理を行います。(.parameter , .par)
.parameter CMD ...
sqlite> .sch temp.sqlite_parameters
sqlite> .parameter init
sqlite> .sch temp.sqlite_parameters
CREATE TABLE temp.sqlite_parameters(
key TEXT PRIMARY KEY,
value ANY
) WITHOUT ROWID;
sqlite> .parameter set $var1 2
sqlite> .parameter list
$var1 2
sqlite> SELECT * FROM emp WHERE id = $var1;
2|Kannai|Jiro
オプションは以下のとおりです。
clear 全てのバインディングを消去します。
init バインディング情報を格納するバインディングテーブル(sqlite_parameters)を作成します。
list パラメータバインディング一覧を表示します。
set PARAMETER VALUE パラメータとその値をセットします。
パラメータは $ : @ ? のどれかから始まる必要があります。
unset PARAMETER バインディングテーブルからパラメータを削除します。
.print (文字列の表示)
指定された文字列リテラル値を表示します。(.print , .pr)
sqlite> .print "Test Statement"
Test Statement
.progress (opcode進捗状況表示の設定)
opcode 進捗状況表示の設定します。(.progress , .pro)
引数 N を指定すると、opcode が N 個、実行される毎に progress を表示します。
sqlite> .progress 10
sqlite> SELECT * FROM emp;
1|Toshiba|Taro
Progress 1
2|Kannai|Jiro
3|Mishima|Saburo
Progress 2
.prompt (プロンプト表示の変更)
プロンプト表示を変更することができます。(.prompt , .p)
<sqlite>.prompt <hello>
<hello>
.quit (sqlite3の終了)
sqlite3 シェルツールを終了します。(.quit , .q)
sqlite> .q
[sqlite@localhost work]$
.read (SQLファイルの読み込み/実行)
SQLファイルを読み込んで実行します。(.read)
[sqlite@localhost work]$ cat count.sql
SELECT count() FROM emp;
SELECT * FROM emp WHERE id=2;
[mototaka@localhost work]$ sqlite3 test1.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .read count.sql
3
2|Kannai|Jiro
例では、count.sql というSQLファイルを読み込んで実行しています。
.recover (リカバリー)
データベースが壊れている時に、データベースの修復を試みます。(.recover , .r)
sqlite> .recover
PRAGMA foreign_keys=OFF;
BEGIN;
PRAGMA writable_schema = on;
CREATE TABLE IF NOT EXISTS emp(
id INTEGER PRIMARY KEY,
name1 TEXT,
name2 TEXT );
INSERT INTO "emp"("id", "name1", "name2") VALUES( 1, 'Toshiba', 'Taro' );
INSERT INTO "emp"("id", "name1", "name2") VALUES( 2, 'Kannai', 'Jiro' );
INSERT INTO "emp"("id", "name1", "name2") VALUES( 3, 'Mishima', 'Saburo' );
PRAGMA writable_schema = off;
COMMIT;
.restore (リストア)
バックアップしたファイルからデータベースを復元します。(.restore , .res)
sqlite> .restore backup1.db
sqlite> .tab
emp
.save (インメモリデータベースの保存)
インメモリデータベースをファイルに書き出します。(.save , .sav)
sqlite> .save test1.db
sqlite> .shell ls -l test1*
-rw-r--r--. 1 sqlite sqlite 8192 3月 14 18:32 test1.db
.schema (スキーマの表示)
データベース・スキーマを表示します。(.schema , .sch)
テーブル名を指定することで、そのスキーマを表示します。
sqlite> .schema emp
CREATE TABLE emp(
id INTEGER PRIMARY KEY,
name1 TEXT,
name2 TEXT );
.scanstats (sqlite3_stmt_scanstatus)
sqlite3_stmt_scanstatus() メトリックを設定します。通常ビルドでは使用不可となっています。 (.scanstatus, .scan)
.screenstats, .s
sqlite> .scan on
Warning: .scanstats not available in this build.
.selftest (セルフテストの実行)
データベースの状態をチェックするために、セルフテストを実行します。(.selftest , .self)
selftest テーブルを作成して、テストケースを INSERT する必要があります。selftest テーブルが存在しない場合、PRAGMA integrity_check を実行してデータベースの状態をチェックします。
sqlite> .selftest
Missing SELFTEST table - default checks only
0 errors out of 1 tests
sqlite> CREATE TABLE selftest(
...> tno INTEGER PRIMARY KEY, -- Test number
...> op TEXT, -- 'run' or 'memo'
...> cmd TEXT, -- SQL command to run, or text of "memo"
...> ans TEXT -- Expected result of the SQL command
...> );
sqlite> INSERT INTO selftest VALUES (1,'run',
...> "SELECT count() FROM emp;","3");
sqlite> .self
0 errors out of 1 tests
.separator (区切り文字の指定)
カラム間とレコード間の区切り文字を指定します。(.separator, se)
sqlite> .separator : :LF\n
sqlite> SELECT * FROM emp WHERE id=3;
3:Mishima:Saburo:LF
.sha3sum (SHA3ハッシュ値の表示)
データベース内容をSHA3ハッシュで計算して表示します。(.sha3sum , .sh3)
この値を比較することで、データベースが更新されているかを確認できます。
sqlite> .sha3sum
c05e91c6bb335903337a74c4bead652233566c97873336815aaca245
オプションは以下のとおりです。
--schema sqlite_masterテーブルのハッシュ値を計算
--sha3-224 sha3-224 アルゴリズムを使用
--sha3-256 sha3-256 アルゴリズムを使用(デフォルト)
--sha3-384 sha3-384 アルゴリズムを使用
--sha3-512 sha3-512 アルゴリズムを使用
.shell (外部コマンドを実行)
カレントディレクトリで、外部コマンドを実行します。(.shell, .sh)
sqlite> .shell ls -l
合計 288484
-rw-rw-r--. 1 sqlite sqlite 1670062 2月 10 21:23 SQLite-3.31.1.zip
.....
.show (設定値の表示)
現在、設定されている値を表示します。(.show , .sh)
sqlite> .show
echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: :memory:
.stats (ステータス表示の制御)
SQL実行後、ステータスを表示する機能を ON, OFF で制御します。(.stats, .st)
デフォルトは OFF です。
sqlite> .stats on
sqlite> SELECT * FROM emp WHERE id=2;
2|Kannai|Jiro
Memory Used: 153736 (max 155064) bytes
Number of Outstanding Allocations: 177 (max 179)
Number of Pcache Overflow Bytes: 4104 (max 4104) bytes
Largest Allocation: 87200 bytes
Largest Pcache Allocation: 4104 bytes
Lookaside Slots Used: 33 (max 69)
Successful lookaside attempts: 109
Lookaside failures due to size: 0
Lookaside failures due to OOM: 0
Pager Heap Usage: 13640 bytes
Page cache hits: 2
Page cache misses: 2
Page cache writes: 0
Page cache spills: 0
Schema Heap Usage: 2264 bytes
Statement Heap/Lookaside Usage: 5008 bytes
Fullscan Steps: 0
Sort Operations: 0
Autoindex Inserts: 0
Virtual Machine Steps: 11
Reprepare operations: 0
Number of times run: 1
Memory used by prepared stmt: 5008
Bytes received by read(): 33111
Bytes sent to write(): 2776
Read() system calls: 230
Write() system calls: 166
Bytes read from storage: 0
Bytes written to storage: 0
Cancelled write bytes: 0
.system (システムコマンドの実行)
システムコマンドを実行します。(.system , .sy)
sqlite> .shell ls -l
合計 288484
-rw-rw-r--. 1 sqlite sqlite 1670062 2月 10 21:23 SQLite-3.31.1.zip
.....
.tables (テーブルの一覧表示)
データベースに存在するテーブルを一覧表示します。(.tables , .tab)
sqlite> .tables
emp
.testcase (testcase-out.txt出力)
出力を"testcase-out.txt"ファイルへ転送し始めます。
.output コマンドに似ています。
sqlite> .testcase
sqlite> .selftest
sqlite> .q
[sqlite@localhost work]$ more testcase-out.txt
1: Expected: [4]
1: Got: [3]
1 errors out of 1 tests
.testctrl (sqlite3_test_control)
さまざまな sqlite3_test_control() オペレーションを実行します。 (.testctrl)
sqlite> .testctrl
Available test-controls:
.testctrl always BOOLEAN
.testctrl assert BOOLEAN
.testctrl byteorder
.testctrl extra_schema_checks BOOLEAN
.testctrl imposter SCHEMA ON/OFF ROOTPAGE
.testctrl internal_functions
.testctrl localtime_fault BOOLEAN
.testctrl never_corrupt BOOLEAN
.testctrl optimizations DISABLE-MASK
.testctrl pending_byte OFFSET
.testctrl prng_restore
.testctrl prng_save
.testctrl prng_seed SEED ?db?
.testctrl reserve BYTES-OF-RESERVE
.timeout (タイムアウト時間の設定)
タイムアウト時間をミリ秒で設定します。(.timeout , .timeo)
設定されたタイムアウト時間までロック待ちをします。タイムアウト時間を設定していない場合、ロック待ちしないで即時でエラーを返します。
PRAGMA busy_timeout と同じ働きをします。
sqlite> .timeout 5000
sqlite> INSERT INTO emp(id) VALUES(5); -- 他プロセスで test1.db をロックさせています。
Error: database is locked -- 5秒後にエラーが表示
.timer (タイマーの設定)
SQL文の実行時間を測定するタイマーを ON, OFF で設定します。(.timer)
Linux の time コマンドと同じような動作をします。
sqlite> .timer on
sqlite> INSERT INTO emp(id) VALUES(4);
Run Time: real 0.006 user 0.000493 sys 0.000000
.trace (トレースの設定)
実行したSQL文をトレースするための設定を行います。(.trace , .tr)
sqlite> .trace trace.txt
sqlite> SELECT count() FROM emp;
4
sqlite> DELETE FROM emp WHERE id=4;
sqlite> .q
[sqlite@localhost work]$ cat trace.txt
SELECT count() FROM emp;
DELETE FROM emp WHERE id=4;
例では、trace.txt ファイルに実行したSQL文を出力しています。
オプションは以下のとおりです。
FILE 指定されたファイルに出力
stdout 標準出力に出力
stderr stderr に出力
off トレースを停止
--expanded クエリ・パラメータを拡張
--plain 入力したSQLを出力
--stmt 実行したステートメントをトレース(SQLITE_TRACE_STMT)
--profile ステートメントを分析 (SQLITE_TRACE_PROFILE)
--row 各レコードを分析 (SQLITE_TRACE_ROW)
--close トレース接続をクローズ (SQLITE_TRACE_CLOSE)
.vfsinfo 最上位VFSの情報表示
最上位の VFS の情報を表示します。(.vfsinfo , .vs)
sqlite> .vfsinfo
vfs.zName = "unix"
vfs.iVersion = 3
vfs.szOsFile = 120
vfs.mxPathname = 512
.vfslist (VFSの一覧表示)
使用可能な全ての VFS の一覧を表示します。(.vfslist , .vfs)
sqlite> .vfslist
vfs.zName = "unix" <--- CURRENT
vfs.iVersion = 3
vfs.szOsFile = 120
vfs.mxPathname = 512
-----------------------------------
vfs.zName = "apndvfs"
vfs.iVersion = 3
vfs.szOsFile = 144
vfs.mxPathname = 1024
-----------------------------------
vfs.zName = "memdb"
vfs.iVersion = 2
vfs.szOsFile = 120
vfs.mxPathname = 1024
-----------------------------------
vfs.zName = "unix-excl"
vfs.iVersion = 3
vfs.szOsFile = 120
vfs.mxPathname = 512
-----------------------------------
vfs.zName = "unix-dotfile"
vfs.iVersion = 3
vfs.szOsFile = 120
vfs.mxPathname = 512
-----------------------------------
vfs.zName = "unix-none"
vfs.iVersion = 3
vfs.szOsFile = 120
vfs.mxPathname = 512
.vfsname (VFSスタック名の出力)
VFS スタック名を出力します。(.vfsname , .vfsn)
sqlite> .vfsname
unix
.width (カラム幅の設定)
column モードのカラム幅を設定します。(.widths , .w)
負数の場合、右寄せで表示します。
sqlite> .width -2 8 7
sqlite> SELECT * FROM emp;
id name1 name2
-- -------- -------
1 Toshiba Taro
2 Kannai Jiro
3 Mishima Saburo
まとめ
この "dot commands" は、バージョン毎に改善されて、さまざまコマンドがサポートされています。特にバッチ実行で効果を発揮すると思われ、知っていると自動化に大きな違いが出てくると思います。
もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。