SQLite3学習 インタフェース概要

　どんな文脈から、どう操作できるか。

成果物

• github

文脈

　SQLite3の利用には大きく分けて2つの文脈がある。

文脈	概要
SQL開発	C言語のI/Fにて実行時ロード可能な拡張機能を開発する
SQL使用	SQL文(SQL関数)、SQLite3独自コマンド(CLI, プラグマ)を使ってDB管理する

SQL使用

　SQLite3を使用するとき、そのコマンド体系と実行環境は3つに大別できる。

Syntax / IF	CLI	対話モード	SQL文実行環境
起動引数	○	☓	☓
ドットコマンド	○	○	☓
SQL文	○	○	○

文脈	例	概要
CLI	$ sqlite3 :memory: "select 1;"	CLI
対話モード	sqlite> select 1;	CLI
SQL文実行環境	select 1; (以下参照)	Tcl, C言語, Python, C#, ...

run.tcl

package require sqlite3
sqlite3 db ./sample.db
db eval { CREATE TABLE users (id int, name text) }
db eval { INSERT INTO users (id ,name) VALUES (1,'A') }
set res [db eval { SELECT id, name FROM users }]
foreach row $res { puts $row }
db eval { DROP TABLE users } -list
db close

　ターミナルで以下コマンドにより上記を実行する。

tclsh run.tcl

CLIで起動引数コマンドを実行する

　ターミナルでコマンドを実行する。以下のような構文体系をもって。

sqlite3 [OPTIONS] FILENAME [SQL]

　詳細はヘルプ参照。

sqlite3 --help

ヘルプ

Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -zip                 open the file as a ZIP Archive

　インメモリでSQL実行するなら以下。

sqlite3 :memory: "create table T(A text);" "insert into T values('AAA');" "select * from T;"

AAA

　DBファイルを指定するなら以下。

sqlite3 /tmp/work/lite.db "create table T(A text);" "insert into T values('AAA');" "select * from T;"

AAA

　ドットコマンドも実行できる。

sqlite3 :memory: ".headers on" ".mode column" "create table T(A text);" "insert into T values('AAA');" "select * from T;"

A         
----------
AAA       

　ドットコマンドやSQL文をa.sqlファイル内に書いたとき、それを実行するシェルコマンドは以下。

sqlite3 :memory: < a.sql

対話モードでドットコマンドを実行する

　ターミナルで以下のように実行する。

sqlite3

sqlite3 :memory:

sqlite3 /tmp/work/a.db

　対話モードになる。

sqlite> 

　そこにドットコマンドやSQL文を入力する。

.headers on
.mode column
.width 26
values(1234567890, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

column1                     column2                   
--------------------------  --------------------------
1234567890                  ABCDEFGHIJKLMNOPQRSTUVWXYZ

.width 0

column1     column2                   
----------  --------------------------
1234567890  ABCDEFGHIJKLMNOPQRSTUVWXYZ

.width -1

c  column2                   
-  --------------------------
1  ABCDEFGHIJKLMNOPQRSTUVWXYZ

.width 0 3

column1     col
----------  ---
1234567890  ABC

　ドットコマンドは以下。

.help

ドットコマンド一覧

.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?TABLE? ...        Render all database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in a spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for specified queries
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
.nullvalue STRING        Use STRING in place of NULL values
.once (-e|-x|FILE)       Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?on|off?          Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set column widths for "column" mode

　SQL文もそのまま実行できる。

select 1;

1

SQL文実行環境でSQLを実行する

　起動引数やドットコマンドに相当する処理ができるかどうかは、そのインタフェース次第。

　URIでそれらの一部を表現することも可。以下参考。

• SQLite3学習　ビルドオプション動作確認（SQLITE_ALLOW_URI_AUTHORITY）
• https://codeday.me/jp/qa/20190322/456904.html

Tcl

run.tcl

package require sqlite3
sqlite3 db ./sample.db
db eval { CREATE TABLE users (id int, name text) }
db eval { INSERT INTO users (id ,name) VALUES (1,'A') }
set res [db eval { SELECT id, name FROM users }]
foreach row $res { puts $row }
db eval { DROP TABLE users } -list
db close

　ターミナルで以下コマンドにより上記を実行する。

tclsh run.tcl

DBファイル間接続（attach database）

　テスト用に2つのDBファイルを作る。

sqlite3 ./users.db \
"create table users(id integer primary key, name text, items, text);" \
"insert into users(name, items) values('Yamada', '[1,2]');" \
"insert into users(name, items) values('Suzuki', '[1]');"

sqlite3 ./items.db \
"create table items(id integer primary key, name text);" \
"insert into items(name) values('AAA');" \
"insert into items(name) values('BBB');"

　2つのDBをメモリ上で結合させる。

　Yamadaが持っているitemsの名前一覧を取得する。

sqlite3 :memory: \
"attach database 'file:./users.db' as U;" \
"attach database 'file:./items.db' as I;" \
"select name from (select value from json_each((select items from U.users where name='Yamada'))) as yamada_item_ids left join I.items on yamada_item_ids.value=I.items.id;"

AAA
BBB

細かいSQL

sqlite3 :memory: \
"attach database 'file:./users.db' as U;" \
"attach database 'file:./items.db' as I;" \
"select * from U.users;" \
"select * from I.items;" \
"select * from json_each((select items from U.users where name='Yamada'));" \
"select name from I.items where id=(select value from json_each((select items from U.users where name='Yamada')));" \
"select name from (select value from json_each((select items from U.users where name='Yamada'))) as yamada_item_ids left join I.items on yamada_item_ids.value=I.items.id;"

対象環境

• 2019-08-15
• Raspbierry pi 3 Model B+
• Raspbian stretch 9.0 2018-11-13
• bash 4.4.12(1)-release
• SQLite 3.29.0

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

前回まで

• SQLite3学習をはじめよう
• SQLite3学習　SQLiteについて
• SQLite3学習　SQLiteの適切な用途
• SQLite3学習　SQLiteの特徴
• SQLite3学習　SQLiteのクセ
• SQLite3学習　データ型とアフィニティ
• SQLite3学習　演算子の一覧
• SQLite3学習　よくある質問
• SQLite3学習　SQLiteダウンロード＆コンパイル
• SQLite3学習　Tclで操作する
• SQLite3学習　ビルドオプション動作確認（SQLITE_ALLOW_URI_AUTHORITY）
• SQLite3学習　面白そうなコンパイルオプション
• SQLite3学習　SQLiteの拡張について
• SQLite3学習　JSON拡張
• SQLite3学習　JSON拡張（json_extract）
• SQLite3学習　JSON拡張（json_each）
• SQLite3学習　JSON拡張（json_tree オブジェクト→行）
• SQLite3学習　JSON拡張（json_tree オブジェクトツリー→行）
• SQLite3学習　JSON拡張（json_tree オブジェクト配列→行）
• SQLite3学習　JSON拡張（json_group_array 行→配列）
• SQLite3学習　JSON拡張（json_group_object 行→オブジェクト）
• SQLite3学習　JSON拡張（json_array_length）
• SQLite3学習　JSON拡張（json_type）
• SQLite3学習　JSON拡張（json_valid）
• SQLite3学習　JSON拡張（json_quote）
• SQLite3学習　JSON拡張（json_array）
• SQLite3学習　JSON拡張（json_object）
• SQLite3学習　JSON拡張（json_patch）
• SQLite3学習　JSON拡張（json_insert）
• SQLite3学習　JSON拡張（json_replace）
• SQLite3学習　JSON拡張（json_set）
• SQLite3学習　JSON拡張（json_remove）
• SQLite3学習　全文検索（FTS5）
• SQLite3学習　全文検索FTSを日本語で使う方法を調べてみた
• 形態素解析MeCabをインストールする
• SQLite3学習　全文検索FTS5のMeCab用トークナイザを実装する
• SQLite3学習　FTS5+MeCabでクエリ構文
• SQLite3学習　FTS5のテーブル作成と初期化
• SQLite3学習　FTS5の補助関数
• SQLite3学習　FTS5のfts5vocab仮想テーブル
• SQLite3学習　再帰クエリ（WITH RECURSIVE）
• SQLite3学習　R-Treeモジュール
• SQLite3学習　ファイル入出力（SQL集計）
• SQLite3学習　拡張関数（generate_series）
• SQLite3謎　主キーの型をintにするとinsertで値を省略したらNULLになってしまう
• SQLite3学習　入出力関数（fsdir, readfile, writefile, edit）
• SQLite3ビルド　コンパイルオプションを付与する方法（CFLAGS等））
• SQLite3　コンパイルオプション確認方法（pragma compile_options）
• SQLite3ビルド　ICUを有効にする（SQLITE_ENABLE_ICU）
• SQLite3拡張　ICUを動的ロードする
• SQLite3拡張　ICUでcollateする
• SQLite3拡張　ICUで全文検索する（FTS4）
• SQLite3拡張　SQL関数一覧（pragma function_list）
• SQLite3拡張　仮想テーブルモジュール一覧（pragma module_list）
• SQLite3拡張　プラグマ一覧（pragma pragma_list）
• SQLite3謎　values()構文