前回、Androidで標準サポートされている「SQLite」に準じたデータベース（以下、DB）を作成するツールを紹介しました。SQLiteのDB作成方法自体は、前回書いたようにとても簡単ですが、その簡単さはSQLite独特のルールによって成り立っているようです。今回は、そのSQLiteのもつ独特なあれこれについて、メモを残しておきます。
本題の前に、SQLiteのバージョンについて書いておきます。SQLiteのバージョンは、大きく2と3に分かれていて、2と3では互換性がないようです。古いシステムで2が採用されているような場合を除いて、3を使うのがよさそうです。（現時点での最新版は、3.8.11.1）以降、SQLiteに関するメモは、SQLite3について記載していきます。
Storage Classes and Datatypes（データの種類とデータ型の微妙な関係）
下記ページに書かれていることを自分なりにメモ。
一般的なDBでは、データの格納場所（カラム）に対してDatatypes（データの型）を定義して、格納する値を規定しますが、SQLite3では、格納するデータそのものに、その種類を示すStorage Classes（以下、クラス）を定義して、格納する値を規定します。この「型」と「クラス」は、よく似た機能を持つので、用語として見た場合、区別がつかないと書かれています。困りました。
困りましたが、メモは続けます。まずは、SQLite3の持つ5つのクラス（値の種類）について。
NULLクラス： NULL値
**INTEGERクラス：**値の大きさに応じて1、2、3、4、6、8バイトで保存される符号付き整数。
**REALクラス：**IEEE方式の倍精度浮動小数点数。
**TEXTクラス：**UTF-8、UTF-16BE、UTF-16LEでエンコードされた文字列。
**BLOBクラス：**入力されたままのバイナリデータ。
その他、注意事項がこちら。
SQLite3には、クラスとしてのBoolean型はありません。代わりに0（偽）と1（真）をINTEGERクラスの値として保存します。 DATETIME型 に相当するクラスもありません。代わりに「Date And Time Functions」という機能を実装しており、この機能を使って、TEXT、REAL、INTEGERクラスの値として日時を保存します。
・TEXT： ISO8601形式の文字列として保存。（日時関数なら"YYYY-MM-DD HH:MM:SS"を返します）
・REAL： ユリウス暦紀元前4713年1月1日（グレゴリオ暦紀元前4714年11月24日）のグリニッジ正午（世界時）を元期（＝0日目）として、そこからの日数を整数部、時分秒を小数部で表す値として保存。
・INTEGER： 協定世界時 (UTC) での1970年1月1日真夜中（午前0時0分0秒）の時刻からの経過秒数を整数値として保存
Type Affinity（他のRDBMSとの連携）
今のところ、他のRDBMSとの連携は考えていませんが、ついでにその辺についてメモしておきます。
先ほど、一般的なDBでいうところの「型」とSQLiteの「クラス」が機能的によく似たものと書きましたが、「型」が値の格納場所を定義しているのに対して、「クラス」は格納する値自身を定義しているという大きな違いがあり、すんなり相互連携という訳にはいきません。そんな一般的ではないSQLiteの方には、他のDBとの互換性をもたせる仕組み（Type Affinity）が用意されています。
SQLite3では、値の格納場所であるColumnに「推奨されるデータ型（Type Affinity）」という仕組みを導入しています。あくまでも「推奨」というところがSQLiteのいい意味での緩さです。どういうことかというと、他のDBからデータを取り込む際に、SQLiteが持っていない型でも、SQLiteが用意した5つのType Affinityの中から似たものに割り振ることで取り込めるよ、というものです。
SQLite3のデータベースの各column（列）に割当可能な5つのType Affinity
TEXT affinity： NULL、TEXT、BLOBクラスの値を格納可能。数値を格納する場合、文字列に変換して格納。
NUMERIC affinity： 全クラスの値を格納可能。TEXTクラスの値を格納する場合、INTEGERか REALクラスへの変換を試み、変換できれば変換して格納。変換できなければTEXTクラスのまま格納。NULL、BLOBクラスの値は、変換せずに格納。
INTEGER affinity： 基本的にNUMERIC affinityと同じ動作ですが、CAST expressionsの場合のみ違う動作となります。
REAL affinity： 基本的にNUMERIC affinityと同じ動作ですが、整数形式の値の場合、小数点クラスにして格納します。
BLOB（かつての名称はNONE）affinity： どんなタイプの値でも変換せずに格納します。
CAST expressionsにおけるNUMERICとINTEGERの違いについて
ある式の中でBLOBタイプの値をINTEGERタイプへ変換する場合、最初にTEXTタイプの値に変換して、そのTEXT値の中から整数として解釈できる最も長い部分を抽出して、残りを無視します。また、ある式の中でREALタイプの値をINTEGERタイプへ変換する場合、変換前の値と0の間で最もその値に近い整数値へ変換します。
ある式の中でTEXTやBLOBの値をNUMERIC型へ変換する場合、最初にREALタイプの値へ変換します。さらに、REALタイプからINTEGERタイプへの変換を試みますが、その変換によって値がロスレスかつ可逆性を保つ場合にのみ、REALタイプからINTEGERタイプへ変換します。
CAST expressionsの詳細については、下記を参照。
SQLite Query Language: expression
Type Affinityへの割り振り方
他のRDBMSでよく使われるデータ型をSQLiteで用意された5つのType Affinityに割り振る方法は、以下のとおり。一番上の条件から順に確認していき、あてはまらなければ次の条件に照らし合わせていきます。
- 型名に"INT"を含む場合、INTEGER affinityに割り当て
- 型名に"CHAR"、“CLOB”、“TEXT"のいずれかを含む場合、TEXT affinityに割り当て
- 型名に"BLOB"を含む、または型指定が無い場合、BLOB affinityに割り当て
- 型名に"REAL”、“FLOA”、“DOUB"のいずれかを含む場合、REAL affinityに割り当て
- 上記のいずれにも当てはまらない場合は、NUMERIC affinity
具体的な例が下記項に書かれています。
SQLiteに関するあれこれについては、他のRDBMSとの比較として、Datatypesの扱いがよく分からなかったので、調べるのに時間がかかってしまい、本日はここまで。他のトピックについては、気になることがあったら（その2）でメモしようと思います。
【参考サイト】
コメント
コメントなどありましたら、GitHubのディスカッションへお願いします。（書き込みには、GitHubのアカウントが必要です）