やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3ドットコマンド（.dump）

SQLite3ドットコマンド（.dump）

SQLite3

　DBファイルの内容をすべてSQL文にして出力する。

成果物

.help

.dump ?TABLE? ...        Render all database content as SQL

.dump

　データベースファイルのスキーマやレコードをすべてSQL文として出力する。

　ターミナルで以下コマンド実行。

sqlite3 :memory: "create table T(C text);" "insert into T values('AAA');" ".dump"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T(C text);
INSERT INTO T VALUES('AAA');
COMMIT;

ファイル化

　ターミナルで以下コマンド実行。

sqlite3 :memory: "create table T(C text);" "insert into T values('AAA');" ".dump" > dump.sql
cat dump.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T(C text);
INSERT INTO T VALUES('AAA');
COMMIT;

対話モードから実行

　ターミナルで以下コマンド実行。

sqlite3

　対話モードになる。

sqlite>

　以下コマンド実行。

create table T(C text);
insert into T values('AAA');
.dump

　なお、.dumpの結果をファイル出力したいなら以下。

.output dump.sql
.dump

　その後、ファイル内容を表示させたいなら以下。

.output stdout
.shell cat dump.sql

　対話モード内にて以下のように出力される。

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T(C text);
INSERT INTO T VALUES('AAA');
COMMIT;

複数テーブルすべて

　テーブル作成するSQLファイルを作成する。

echo "create table T1(C text);
insert into T1 values('AAA');
insert into T1 values('BBB');
create table T2(C text);
insert into T2 values('あああ');
insert into T2 values('いいい');
create table T3(C text);
insert into T3 values('アアア');
insert into T3 values('イイイ');" > create_tables.sql

　.dumpする。

sqlite3 :memory: \
".read create_tables.sql" \
".dump" > dump.sql

　.dump出力結果をみてみる。

cat dump.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T1(C text);
INSERT INTO T1 VALUES('AAA');
INSERT INTO T1 VALUES('BBB');
CREATE TABLE T2(C text);
INSERT INTO T2 VALUES('あああ');
INSERT INTO T2 VALUES('いいい');
CREATE TABLE T3(C text);
INSERT INTO T3 VALUES('アアア');
INSERT INTO T3 VALUES('イイイ');
COMMIT;

複数テーブル単一テーブルのみ

　単一テーブルの指定。

sqlite3 :memory: \
".read create_tables.sql" \
".dump T1" > dump_t1.sql

複数テーブル中指定した複数テーブルのみ（不可）

3つのうち2つのテーブルを出力しようとするも失敗。

sqlite3 :memory: \
".read create_tables.sql" \
".dump T1 T3" > dump_t1_t3.sql

　以下エラー。意味不明。.dumpの引数なのか？　ヘルプには.dump ?TABLE? ...と書いてあったのだが、これはテーブル名を任意に複数与えることができるという意味ではなかったのか？

Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?

　--preserve-rowidsというのは引数なのか？　指定したrowidだけを保存できるということか？　使ってみるも同様のエラー……。

sqlite3 :memory: \
".read create_tables.sql" \
".dump --preserve-rowids 1 T1" > dump_t1_rowid_1.sql
Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?

　ググってみるとクォートすればできるとあるのだが、できない……。

sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1' 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
'.dump "T1" "T3"' > dump_t1_t3.sql
Usage: .dump ?--preserve-rowids? ?--newlines? ?LIKE-PATTERN?

　ちなみに,, OR,||,AND,&&を使っても同様のエラーだった。

sqlite3 :memory: \
".read create_tables.sql" \
".dump T1, T3" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1', 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1', 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1' AND 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1' && 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1' OR 'T3'" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1' || 'T3'" > dump_t1_t3.sql

　以下はエラーは出ないが空ファイルが出力された。

sqlite3 :memory: \
".read create_tables.sql" \
".dump 'T1, T3'" > dump_t1_t3.sql

妥協案

　テーブルの数だけコマンドを実行する。面倒……。

sqlite3 :memory: \
".read create_tables.sql" \
".dump T1" > dump_t1_t3.sql
sqlite3 :memory: \
".read create_tables.sql" \
".dump T3" >> dump_t1_t3.sql

　内容を確認する。

cat dump_t1_t3.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T1(C text);
INSERT INTO T1 VALUES('AAA');
INSERT INTO T1 VALUES('BBB');
COMMIT;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T3(C text);
INSERT INTO T3 VALUES('アアア');
INSERT INTO T3 VALUES('イイイ');
COMMIT;

like句パターンでテーブル名を指定

　テーブル名はlike句パターン文字列で指定できる。

sqlite3 :memory: \
".read create_tables.sql" \
".dump '%2'" > dump_like.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE T2(C text);
INSERT INTO T2 VALUES('あああ');
INSERT INTO T2 VALUES('いいい');
COMMIT;

　これよりも複数の固定テーブル名を指定できるほうがずっと嬉しいのだが……。

バイナリデータは？

echo "create table Bins(B blob);
insert into Bins values(x'FF');
insert into Bins values(randomblob(16));
" > create_bin.sql
sqlite3 :memory: \
".read create_bin.sql" \
".dump" > dump_bin.sql
sqlite3 :memory: \
".read create_bin.sql" \
"select * from Bins;" \
".output dump_bin.sql" \
".dump" \
".output stdin"

　画面にはselectしたバイナリデータが出た。

�
d�إ���n� �*��

　.dumpの出力結果。

cat dump_bin.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Bins(B blob);
INSERT INTO Bins VALUES(X'ff');
INSERT INTO Bins VALUES(X'a98418d9285e8965429ee0a07dc0f7ca');
COMMIT;

selectしたバイナリ値はodコマンドにて16進数で見ることができる。

sqlite3 :memory: \
".read dump_bin.sql" \
"select * from Bins;" > a.bin
od -h a.bin
0000000 0aff 84a9 d918 5e28 6589 9e42 a0e0 c07d
0000020 caf7 000a
0000023

.read

　.dumpで出力したSQLを.readで読み込んで実行させる。

SQL文

　.dumpでSQLファイルを作成する。

sqlite3 :memory: \
"create table T(C text);" \
"insert into T values('AAA');" \
".dump" > dump.sql

　.readでSQLファイルを読み込んで実行する。

sqlite3 :memory: \
".read dump.sql" \
".tables" \
"select * from T;"
T
AAA

ドットコマンド

echo "pragma function_list;" > func_list.sql
sqlite3 :memory: \
".read func_list.sql"

結果

group_concat|1
julianday|1
ntile|1
nullif|1
sqlite_compileoption_get|1
current_timestamp|1
sqlite_compileoption_used|1
sum|1
quote|1
printf|1
likelihood|1
last_value|1
rank|1
round|1
rtrim|1
nth_value|1
random|1
trim|1
time|1
total|1
substr|1
replace|1
upper|1
typeof|1
load_extension|1
avg|1
abs|1
strftime|1
randomblob|1
unicode|1
percent_rank|1
row_number|1
last_insert_rowid|1
sqlite_log|1
unlikely|1
char|1
count|1
date|1
sqlite_record|1
total_changes|1
changes|1
sqlite_version|1
coalesce|1
glob|1
zeroblob|1
hex|1
sqlite_source_id|1
datetime|1
cume_dist|1
instr|1
dense_rank|1
ifnull|1
current_date|1
current_time|1
lag|1
like|1
max|1
min|1
lead|1
lower|1
ltrim|1
first_value|1
length|1
likely|1
edit|0
shell_int32|0
shell_putsnl|0
shell_module_schema|0
writefile|0
shell_escape_crnl|0
readfile|0
json_valid|0
json_type|0
json_set|0
json_extract|0
json_array_length|0
snippet|0
geopoly_overlap|0
fts5_fold|0
shell_add_schema|0
json_insert|0
fts5_expr_tcl|0
fts5|0
fts3_tokenizer|0
fts5_isalnum|0
geopoly_xform|0
icu_load_collation|0
fts5_expr|0
upper|0
json_array|0
zipfile_cds|0
fts5_rowid|0
fts5_source_id|0
offsets|0
fts5_decode_none|0
lsmode|0
json_group_array|0
json_object|0
geopoly_contains_point|0
highlight|0
sha3_query|0
rtreenode|0
sqlar_compress|0
json|0
rtreecheck|0
json_group_object|0
geopoly_regular|0
fts5_decode|0
json_quote|0
matchinfo|0
geopoly_debug|0
sha3|0
geopoly_bbox|0
match|0
lower|0
bm25|0
geopoly_svg|0
json_replace|0
optimize|0
regexp|0
geopoly_ccw|0
zipfile|0
json_patch|0
like|0
sqlar_uncompress|0
geopoly_blob|0
json_remove|0
geopoly_within|0
rtreedepth|0
geopoly_area|0
geopoly_json|0
geopoly_group_bbox|0

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで