Mono Works

チラシのすきま

メモ:SQLiteに関するあれこれ(Datatypes)

前回、Androidで標準サポートされている「SQLite」に準じたデータベース(以下、DB)を作成するツールを紹介しました。SQLiteのDB作成方法自体は、前回書いたようにとても簡単ですが、その簡単さはSQLite独特のルールによって成り立っているようです。今回は、そのSQLiteのもつ独特なあれこれについて、メモを残しておきます。

androiddevmemo20150928_00

本題の前に、SQLiteのバージョンについて書いておきます。SQLiteのバージョンは、大きく2と3に分かれていて、2と3では互換性がないようです。古いシステムで2が採用されているような場合を除いて、3を使うのがよさそうです。(現時点での最新版は、3.8.11.1)以降、SQLiteに関するメモは、SQLite3について記載していきます。

Storage Classes and Datatypes(データの種類とデータ型の微妙な関係)

下記ページに書かれていることを自分なりにメモ。

Datatypes In SQLite Version 3

一般的な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に割り振る方法は、以下のとおり。一番上の条件から順に確認していき、あてはまらなければ次の条件に照らし合わせていきます。

  1. 型名に"INT"を含む場合、INTEGER affinityに割り当て
  2. 型名に"CHAR"、“CLOB”、“TEXT"のいずれかを含む場合、TEXT affinityに割り当て
  3. 型名に"BLOB"を含む、または型指定が無い場合、BLOB affinityに割り当て
  4. 型名に"REAL”、“FLOA”、“DOUB"のいずれかを含む場合、REAL affinityに割り当て
  5. 上記のいずれにも当てはまらない場合は、NUMERIC affinity

具体的な例が下記項に書かれています。

Datatypes In SQLite Version 3

SQLiteに関するあれこれについては、他のRDBMSとの比較として、Datatypesの扱いがよく分からなかったので、調べるのに時間がかかってしまい、本日はここまで。他のトピックについては、気になることがあったら(その2)でメモしようと思います。

【参考サイト】

Datatypes In SQLite Version 3

メモ:SQLiteのデータベースを作る(作成ツールのご紹介) | Mono Works

コメント

コメントなどありましたら、GitHubのディスカッションへお願いします。(書き込みには、GitHubのアカウントが必要です)
執筆者
"ぽぽろんち" @pporoch
pporoch120
Mono Worksの中の人。好きなことをつらつらと書き留めてます。
ギターを始めてから 練習動画をYouTubeにアップしてます。ご笑納ください。
"DQX@ぬここ(UD487-754)、コツメ(NO078-818)"
採用案内