やってみる

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

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

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

SQLite3

　キーバリュー形式の表を作り、SQL文で変数として使う。

成果物

.help

.parameter CMD ...       Manage SQL parameter bindings

.parameter

sqlite3 :memory: ".parameter"

.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with '$', ':', '@', or '?'
   unset PARAMETER         Remove PARAMETER from the binding table

公式ドキュメント

SQL Parameter

CREATE TEMP TABLE sqlite_parameters(
  key TEXT PRIMARY KEY,
  value ANY
) WITHOUT ROWID;
  • .parameterは.paramと省略できる
  • .param initはtemp.sqlite_parametersテーブルが未存在なら作成する
  • .param listはtemp.sqlite_parametersテーブルのレコードを一覧する
  • .param clearはtemp.sqlite_parametersテーブルを削除する
  • .param set KEY VALUEはエントリを追加する
  • .param unset KEYはエントリを削除する

　tempはDBのこと。　mainではなくtemp。attachするときのDB名。

? : @ $

　SQL文でparameterのkeyのプレフィックスなどに以下を指定するらしい。正直よくわからん。動作するコードが発見できなかったので。

記号 意味
? 1から始まるインデックス指定
: 名前付きインデックス
@ パラメータ名プレフィックス
$ Tcl形式

.param

sqlite3 :memory: ".param"
sqlite3 :memory: ".param init"
sqlite3 :memory: ".param clear"
sqlite3 :memory: ".param set key value" ".param list"
sqlite3 :memory: ".param set key value" ".param list" ".param clear" ".param list"
sqlite3 :memory: ".param set key value" ".param list" ".param unset key" ".param list"

　clearは削除されていない……。unsetで削除できた。

.parameter set

sqlite3 :memory: \
".parameter set my_name Yamada" \
".tables" \
"select sql from sqlite_master;" \
".headers on" \
"select * from sqlite_parameters;"

key|value
my_name|Yamada

　判明したことは以下。

  • .parameter set キー 値するとsqlite_parametersテーブルが作成される
  • sqlite_parametersテーブル名は.tablesで出力されない
  • sqlite_parametersテーブルのスキーマはselect sql from sqlite_master;で出力されない
  • sqlite_parametersテーブルの列名はkey,valueである

SQL内で使う

sqlite3 :memory: \
".param set @target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = @target_name ;"

1|Yamada|A

　試行錯誤の結果、以下のルールを用いると成功した。

  • .param setするときのkey名に@をプレフィクスとして付与する
  • SQL文内でkeyのvalueを参照するとき@key_nameとする
    • クォートは不要

　なお、@を:に置き換えても成功する。

数値もOK

sqlite3 :memory: \
".param set @target_name Yamada" \
".param set @target_age 20" \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where age <= @target_age;"

1|Yamada|12|A
3|Suzuki|18|B
4|Tanaka|19|A

複数もOK

sqlite3 :memory: \
".param set @target_name Yamada" \
".param set @target_age 20" \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where name = @target_name and age <= @target_age;"

1|Yamada|12|A

応用

環境変数をパラメータにする

　たとえばユーザ入力を受け付けて絞込条件にする。

WHILE_NAME=Yamada
WHILE_AGE=20
sqlite3 :memory: \
".param set @target_name ${WHILE_NAME}" \
".param set @target_age ${WHILE_AGE}" \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where name = @target_name and age <= @target_age;"

　だがこの場合、シェルコマンド自体を文字列編集してしまえばいいので何でもできてしまうため有り難みがわかりにくい。

　.paramなんて使わず以下のほうが早くね？　と言われるとその通り。

WHILE_NAME=Yamada
WHILE_AGE=20
sqlite3 :memory: \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where name = '${WHILE_NAME}' and age <= ${WHILE_AGE};"

　強いて言うならリテラルの有無が面倒なくらい。

　でも、SQLite3構文だけで完結できることが、何か有意義なことが、たぶんきっとおそらくある、と思いたい。

ファイル内容をパラメータにする

echo -e "@target_name\tYamada\n@target_age\t20" > params.tsv
sqlite3 :memory: \
".param init" \
".mode tabs" \
".import params.tsv sqlite_parameters" \
".param list" \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where name = @target_name and age <= @target_age;"

@target_age  20
@target_name 'Yamada'
1  Yamada  12 A

　.import,.mode,.paramのコンボ。

　これなら便利かも？　他のアプリでファイルを操作すればSQL絞込条件を編集できる。

　パラメータ付きSQL文は、それ自体が何かをする定式として使える。

　できればパラメータとそれを使うSQL文はひとまとめにしたい。オブジェクト指向のクラスみたいに扱えたらいいのに。メンバ変数にパラメータを、メンバメソッドにSQL文を持ったクラスとして。

　でもSQL文を動的生成したほうが応用できるか。

ユーザ入力受付

WHILE_NAME=`zenity --entry --text="\`printf "ユーザ名入力"\`"`
WHILE_AGE=20
sqlite3 :memory: \
".param set @target_name ${WHILE_NAME}" \
".param set @target_age ${WHILE_AGE}" \
"create table users(id integer primary key, name text, age int, class text);" \
"insert into users(name,age,class) values('Yamada', 12, 'A');" \
"insert into users(name,age,class) values('Yamada', 21, 'C');" \
"insert into users(name,age,class) values('Suzuki', 18, 'B');" \
"insert into users(name,age,class) values('Tanaka', 19, 'A');" \
"select * from users where name = @target_name and age <= @target_age;"

　たとえば出現したテキストボックスにSuzukiと入力すると以下の出力結果になる。

3|Suzuki|18|B

使える箇所

　おそらく以下参考先と同じ？

　ざっくりまとめると以下。

  • 可
    • 値, 式, 列名
  • 不可

　面倒なので検証はしない。

失敗した試行錯誤ログ

失敗した試行錯誤

sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?target_name;"

Error: near "target_name": syntax error

sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=:target_name;"







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=@target_name;"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=$target_name;"







Error: near ";": syntax error







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?$target_name;"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?:target_name;"







Error: near ":target_name": syntax error







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?@target_name;"







Error: near "@target_name": syntax error







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name='?$target_name';"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?'$target_name';"







Error: near "''": syntax error







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?$'target_name';"







Error: unrecognized token: "$"







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?'target_name';"







Error: near "'target_name'": syntax error







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=$'target_name';"







Error: unrecognized token: "$"







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?;"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name='?';"













sqlite3 :memory: \
".parameter set target_name Yamada" \
".parameter list" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name=?;"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name='Yamada';"







1|Yamada|A







sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = target_name ;"







Error: no such column: target_name






sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = ? ;"













sqlite3 :memory: \
".parameter set target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = ?1 ;"













sqlite3 :memory: \
".param set 1 Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = ?1 ;"













sqlite3 :memory: \
".param set @target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = @target_name ;"







1|Yamada|A





　やっとできた！　SQL文側だけでなくkey側にも必要なのかよ……。



sqlite3 :memory: \
".param set ?target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = ?target_name ;"







Error: near "target_name": syntax error







sqlite3 :memory: \
".param set ?target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = ?1 ;"













sqlite3 :memory: \
".param set :target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = :1 ;"











　できた。:でもいける。



sqlite3 :memory: \
".param set :target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = :target_name ;"







1|Yamada|A





　でも$ではできない。



sqlite3 :memory: \
".param set $target_name Yamada" \
"create table users(id integer primary key, name text, class text);" \
"insert into users(name,class) values('Yamada', 'A');" \
"insert into users(name,class) values('Suzuki', 'B');" \
"insert into users(name,class) values('Tanaka', 'A');" \
"select * from users where name = $target_name ;"







.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with '$', ':', '@', or '?'
   unset PARAMETER         Remove PARAMETER from the binding table
Error: near ";": syntax error





　頼むから動くコードを載せてくれ公式。ネットにもぜんぜん情報ないし、使い方わかってる人いるの？　これ便利だと思うのだが。






.param clear



　なんか削除されないんですけど……。



sqlite3 :memory: \
".param set K1 V1" \
".param list" \
".param clear" \
".param list"







K1 'V1'
K1 'V1'





　もしやテーブルからは削除されてる？　と思ったがやはり同じ。



sqlite3 :memory: \
".param set K1 V1" \
".param list" \
"select * from sqlite_parameters;" \
".param clear" \
".param list" \
"select * from sqlite_parameters;"







K1 'V1'
K1|V1
K1 'V1'
K1|V1





.param init



　何の役に立つの？



　.param set前にsqlite_parametersテーブルへアクセスすると未存在だと怒られる。



sqlite3 :memory: \
"select * from sqlite_parameters;"







Error: no such table: sqlite_parameters





　だが、.param initしておけば空テーブルが作成されるので怒られない。それだけ。



sqlite3 :memory: \
".param init" \
"select * from sqlite_parameters;"











　.param initで全件削除できるかも？　と思ったが、削除されず。



sqlite3 :memory: \
".param set K1 V1" \
".param init" \
".param list" \







K1 'V1'





.param unset KEY



　パラメータを削除できる唯一のコマンド。



sqlite3 :memory: \
".param set K1 V1" \
".param list" \
".param unset K1" \
".param list"







K1 'V1'





　作成されて削除された。



全件削除できないか試したが無理だった。



sqlite3 :memory: \
".param set K1 V1" \
".param list" \
".param unset" \
".param list"







K1 'V1'
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with '$', ':', '@', or '?'
   unset PARAMETER         Remove PARAMETER from the binding table
K1 'V1'







sqlite3 :memory: \
".param set K1 V1" \
".param list" \
".param unset *" \
".param list"







K1 'V1'
K1 'V1'








対象環境






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





前回まで