Quirks, Caveats, and Gotchas In SQLite のまとめと、Cloudflare D1の挙動

D1に備えて学んでおこうかと。

はっきり言って、他のデータベースについても全然よく知らない。が、SQLiteだけの特徴みたいなものがあるなら、知ってて損はないだろうというモチベーション。

SQLiteのドキュメントの中に、それ用のページがあったので軽くまとめていく。

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

この記事を書いてる時点でわかったD1の挙動に関することは⛅マークを付けてある。（SQLiteのデフォルトと違うものなど）

1. Overview

• SQLはスタンダードな言語でありつつ、その挙動はDBによって違う
• そういうもんなので、割り切りつつも、このページを読もうね

あ、はい。

2. SQLite Is Embedded, Not Client-Server

• SQLiteは埋め込み用途で、他のデータベースとは違う
• いわゆるクラサバ用途での利用を想定してない

と本家は言っているけど、Cloudflareさんがクラサバ用途に使うって言ってるんですよ・・。

3. Flexible Typing

• カラムに型は指定できるが、必須ではない
• INTEGERなカラムに"123"を入れると、エラーにならず、123にキャストしてくれる
  • SQLite用語ではAffinityという
  • https://www.sqlite.org/datatype3.html#affinity
• ただし"xyz"の場合は、キャストできないので、"xyz"のまま入る
• その程度の弱い型付け
• CREATE TABLE ... STRICTとすれば、イメージ通りの強い型付けにもできる
  • https://www.sqlite.org/stricttables.html
• ⛅ しかしD1はSTRICTなテーブルには対応してなさそう
• カラムの型にBOOLEANはない
• INTEGERで1と0または、TRUEとFALSEというキーワードが使える
• DATETIMEもないので、TEXTでISO-8601するかINTEGERでUNIXタイムスタンプをいれよ

ORMからしかSQLを流さない場合は、あんまり関係ないかもしれんけど。

4. Foreign Key Enforcement Is Off By Default

• 外部キー制約はデフォルトでOFF（後方互換性のため）
• PRAGMA foreign_keys = 1などして有効にする
• ⛅ D1はデフォルトで外部キー制約がONになってた
• ⛅ PRAGMA foreign_keysにも対応してると書いてあるが、OFFにできなかった
  • https://developers.cloudflare.com/d1/platform/client-api/#pragma-statements

ONでよさそう。

5. PRIMARY KEYs Can Sometimes Contain NULLs

• プライマリキーにNULLを設定できる
• そしてNULLとNULLは重複扱いにならないので、ユニーク制約にもかからない
• しかしINTEGER PRIMARY KEYとWITHOUT ROWIDする場合は例外
• NULLをいれると某Affinityが働いて、よしななINTEGERになる

これもバグだとわかっているが、後方互換性のためにそうなってるとのこと。

6. Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause

• max()みたいな集計関数を使うとき、GROUP BYしなくても、他のカラムを返せる
• SELECT max(salary), first_name, last_name FROM employeeできる
  • min()とmax()の場合、複数の行が該当するかもしれないが、気まぐれに1行だけ返る
• 集計関数を使わない場合は、GROUP BYをDISTINCT ONの代わりに使える
• SQLiteはDISTINCTは使えるが、DISTINCT ONはサポートしてない

なるほど。

7. Does Not Do Full Unicode Case Folding By Default

• upper()やlower()といった関数に対応するのは、ASCII文字列だけ
• すべてのユニコードをサポートしたい場合、自分でフラグ付けてコンパイルする

なるほど。

8. Double-quoted String Literals Are Accepted

• SQLiteのおいては、カラム名への'と、文字列への"を使い分ける必要がない
• MySQL 3.xとの互換性のため、"がカラムにマッチしなければ、文字列として解釈する

これも互換性のためにそうなってるとのこと。（そして後悔してる）

9. Keywords Can Often Be Used As Identifiers

• UNIONやTRUEなどのキーワードも、テーブル名やカラム名に指定できる
• CREATE TABLE union(true INT, with BOOLEAN)はエラーにならないってこと
• ⛅ D1ではシンタックスエラーになる・・・！
  • どうやらテーブル名にキーワードは使えないらしい
  • CREATE TABLE foo(INTEGER, TEXT TEXT)は通った

まあそんなことせんやろうけど。

10. Dubious SQL Is Allowed Without Any Error Or Warning

• ポステルの法則（ロバストネス原則）に従って実装してきた
  • Robustness principle - Wikipedia
• が、最近のトレンドとして、柔軟に受け入れるより、厳密に間違いを示すほうがよいことに気づきつつある

え？ってなった。具体的な例はなかった。

11. AUTOINCREMENT Does Not Work The Same As MySQL

• AUTOINCREMENTはMySQLのそれとは異なる挙動をする
• https://www.sqlite.org/autoinc.html
  • SQLiteにおいて、INTEGER PRIMARY KEYなカラムはROWIDと同じ
  • その際、INSERT時に採番されるのは、その時点でのROWIDの最大値+1になる
  • AUTOINCREMENTの指定に関係なくそうなる
  • なので、同じ番号が再利用される可能性があるということ
  • AUTOINCREMENTが指定されると、その再利用をしなくなる

そもそもAUTOINCREMENTなんかいらんでしょ？無駄やしって書いてあった。

12. NUL Characters Are Allowed In Text Strings

• ヌル文字（ASCII0x00やユニコード\u0000）を文字列の中に含むことができる
• できるけど、おすすめはしないとのこと
  • https://www.sqlite.org/nulinstr.html

どういうときに使うんやろう。

13. SQLite Distinguishes Between Integer And Text Literals

• SQLiteでは、1='1'はfalseになる

他のSQLでこれが通るのが理解できないって書いてあった。ポステルの法則はどこいったんや。

まとめ

あれやこれや、すごい正直なお気持ちが書かれたページだった。

とりあえずCloudflareさんは、D1側のドキュメントに、SQLite3との互換性をまとめてほしいです。