　サラッと流し読み。

情報源

https://www.sqlite.org/docs.html
- https://www.sqlite.org/faq.html#q4

　気になったのをいくつか抜き出してみた。

主キーに`0`が使えないのはなぜ？

　気になっていた。が、答えはなかった。

SQLiteが同じテーブルの2つの異なる行で主キーとして '0'と '0.0'を使うことを許可していないのはなぜですか？

　解答は以下。

この問題は、主キーが数値型の場合に発生します。あなたの主キーのデータ型をTEXTに変更すればうまくいきます。

各行には一意の主キーが必要です。数値型の列の場合、SQLiteは'0'と'0.0'は同じ数値であると見なします。これは、数値的に同じであるためです。（前の質問を参照してください。）したがって、値は一意ではありません。

　0と0.0は同値だから一意にならないためそれぞれキーにならないのはわかる。でも、主キーの最小値が1であり0でない理由は不明のまま。私が知りたいのはそこなのに。

テーブルの列を追加・削除する方法は？

　テーブルをまるごと作り直すしかない。そのコード例は以下。alter tableには列を追加・削除する機能がない。

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

DELETEしたのにサイズがそのままで小さくならん

　vacuumを使え。vacuumプラグマを使って自動化することもできる。

　ただし以下の点に注意。

実行に時間がかかる
元ファイルの2倍の一時ディスクが必要

`ROUND(9.95,1)`が`10.0`ではなく`9.9`を返すのはなぜ？

　10進数の少数値は2進数で正確に表現できない。よって誤差が生じるもの。

SQLiteは2進演算を使用し、2進数では、9.95を有限のビット数で書く方法はありません。あなたに最も近いのは、64ビットIEEEフロートで9.95になることができる（これはSQLiteが使用するものです）9.949999999999999289457264239899814128875732421875です。そのため、 "9.95"と入力すると、SQLiteは実際には数値が上記のはるかに長い値であると理解します。そしてその値は切り捨てられます。

この種の問題は、浮動小数点2進数を扱うときに常に起こります。覚えておくべき一般的な規則は、10進数での有限表現（別名 "base-10"）を持つほとんどの小数値は、2進数（別名 "base-2"）の有限表現を持たないことです。そしてそれらは利用可能な最も近い2進数を使って近似されます。その近似は通常非常に近いですが、それはわずかにずれており、場合によっては結果が予想していたものと多少異なることがあります。

　小数値は使わないほうが良さそう。C#ならfloatやdoubleのほかに正確に表現するためのDecimal型があるのだが。

Unicode文字の大文字と小文字を区別しないマッチングは機能しない

SQLiteのデフォルト設定は、大文字と小文字を区別しないASCII文字の比較のみをサポートします。

外部のUnicodeの比較および変換ルーチンに対してリンクする機能を提供します。

　たとえば「あ」「ア」「ｱ」を区別しないような検索はできない。ICUとやらを使えばできるのかな？

INSERTは本当に遅い

　トランザクションを使え。BEGIN, COMMIT。

　他にはPRAGMA synchronynchronous = OFFを実行する。だが途中で電源が切れたらDBファイルが破損する。

誤ってレコードを削除してしまった。回復できない？

　できない。

　ここには書いていなかったが「論理削除」という方法が使える。実際は削除フラグを立てるだけでデータは残したまま。ただしアプリ側で削除フラグがあるレコードは取得しないようにする。これにより削除されたようにみえる。でもDB管理者はデータを閲覧できる。

　もちろんファイル容量は減らない。なにかしらの契機やタイミングで削除することになるだろう。たとえば削除フラグでなく削除日時にして、その日時から1ヶ月が過ぎていたら削除するとか。

シンタックスダイアグラムみせて？

https://www.sqlite.org/syntaxdiagrams.html

期待するカラム名を返させるには？

　AS句を使え。詳細はsqlite3_column_name。

　たとえば以下。count(*)に列名Aを付与した。

create table users(id int primary key, name text);
insert into users values(1, 'yamada');
insert into users values(2, 'suzuki');
select count(*) as A from users;

対象環境

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

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

やってみる

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

SQLite3学習　よくある質問

情報源