やってみる

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

SQLite3スキーマバージョンのインクリメントを自動化できない問題

 以前sqlite_masterテーブルでsqlを書き換えたあと表題の件をしたい。さて、どうやる?

成果物

何いってんの?

 以下参照。

意味あるの?

 そもそもsqlite_masterテーブルでsqlを書き換えできなかったのだから意味はないかもしれない。だが、頑張ったし、他のことにも応用できるため、メモを残しておく。

スキーマバージョンのインクリメントを自動化できないか

案1(不可)

 .parameter set KEY VALUEで値をセットできるが、リテラルのみ。pragmaSQL文の結果をセットできない。

案2(不可)

 .parameter initsqlite_parametersテーブルが作成されるが、そこにinsert文でバージョン値をセットできない。pragma文の実行結果をテーブルに代入する方法がない。insertなどのSQL文内でpragmaコマンドを実行できないから。

案3(取得のみ可)

 ファイル出力すればいけた。

.param init
.headers off
.mode list
.output ./schema_version.txt
pragma schema_version;
.output stdout
insert into sqlite_parameters values('schema_version', (
  select rtrim(data, x'0A') 
  from fsdir('./schema_version.txt')));
.param list
schema_version 0

 ついでにインクリメント方法。

select (cast(value as integer) + 1) 
from sqlite_parameters 
where key='schema_version';
1

 これをパラメータにセットするなら以下。

update sqlite_parameters set schema_version=(
  select (cast(value as integer) + 1) 
  from sqlite_parameters 
  where key='schema_version'
) where key='schema_version';

 めっちゃくちゃ冗長。

 セットするときにインクリメントする方法は以下。

.param init
.headers off
.mode list
.output ./schema_version.txt
pragma schema_version;
.output stdout
insert into sqlite_parameters values('@schema_version', (
  select (cast(rtrim(data, x'0A') as integer) + 1)
  from fsdir('./schema_version.txt')));
.param list
schema_version 1

 OK。これが最短。

 ところで、取得した値をどうやってセットしよう?

 信じられないほど無駄に難しくて非効率。バカじゃないかと思う。もっと簡単な方法があるのでは? 今まで学習してきた成果を遺憾なく発揮せねば不可能だった。ただsqlite_parameters.schema_version = (pragma schema_version)++;と書けたら良かったのに……。これだけのことに何行使ってんだよ。

 だからといって、TclPythonなどのプログラミング・インタフェースを使ったら負けだと思う。意地でもSQLite3文脈内のドットコマンドとSQL文だけでやると、こうなる。私の勝ちだ。やっぱ流石だよな俺らって。

案4(設定不可)

pragma schema_version = @schema_version;
Error: near "@schema_version": syntax error

 パラメータはドットコマンド構文で使えない……。SQL構文でのみ使える……。

 変数を使うときはパラメータ。パラメータを使うにはSQL構文のみ。では、ドットコマンドに変数を使いたいなら? 答えは以下。

案5(設定可)

 例によって.output.readコンボ。これでコマンドを動的に作って実行できる!

.output ./pragma_schema_version.sql
select 'pragma schema_version = ' || @schema_version || ';';
.output stdout
.read ./pragma_schema_version.sql
pragma schema_version;
1

 ちなみに複数行のコードを出力したいとき、改行コードはchar(10)である。

select 'A' || char(10) || 'B';

スキーマバージョンをインクリメントする方法まとめ

 バージョンの取得。

.param init
.headers off
.mode list
.output ./schema_version.txt
pragma schema_version;
.output stdout
insert into sqlite_parameters values('@schema_version', (
  select (cast(rtrim(data, x'0A') as integer) + 1)
  from fsdir('./schema_version.txt')));
.param list

 バージョンの設定。

.output ./pragma_schema_version.sql
select 'pragma schema_version = ' || @schema_version || ';';
.output stdout
.read ./pragma_schema_version.sql

 バージョンの確認。

pragma schema_version;
1

所感

 これを一般化したら「SQLite3のCLI文脈にて動的コマンドを実行する方法」だろうか?

対象環境

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

前回まで