SQLiteのテーブル構造を取得するには

データセットとか使ってればそんな必要もないでしょうが、コード内からテーブル構造を取得して云々するコトもあるわけです。

SQL Server なら sp_columns  を呼べばいいわけですが、SQLiteでは?
cmd.exe 呼び出して .schema 叩いて出力をリダイレクトさせて…
なんて力業に出る必要はありませんでした。

System.Data.SQLite にきちんとその道が用意されています。
こんな感じで取得します。

Dim strConnectionString As String = "data source = " & strSQLiteFileName

Using cnSQLite As New SQLiteConnection(strConnectionString)
     cnSQLite.Open()
     '---スキーマ情報を取得する
     Dim objSchemaInfo As DataTable
     Dim strTableInfo() As String = {Nothing, Nothing, "テーブル名", Nothing}
     objSchemaInfo = cnSQLite.GetSchema("Columns", strTableInfo)

     '---取得したスキーマ情報を格納する
     For Each objRow As DataRow In objSchemaInfo.Rows
          Console.Writeline(objRow!COLUMN_NAME.ToString)
     Next

     cnSQLite.Close()
End Using

小さな嵌り所としては、”COLUMN_NAME”を取ってくるのに”!”を使うところ。
“.” ではないところが要注意です。

System.Data.SQLite で、日付型のラウンドトリップに失敗することがある

 SQLite の特徴の一つに「弱い型付け」があります。
それゆえ、PHPとか Ruby とかと相性がよいわけです。

が、それゆえに、.NET 系の「強い型付け」な言語と相性が悪かったりします。

先日遭遇したのは、こんな事例。

SQLiteに日付型のデータを入れて、取ってくるところでExceptionになる。
一言で言うと「ラウンドトリップできない」と。

ラウンドトリップ失敗の例

つまり、パラメータクエリで日付を渡すと、取得時に落ちるわけです。

原因は、” Now.ToString() “でした。
つまり、 DateTime.ToString を引数なしで呼び出すと、「地域と言語の指定」に従った「短い形式」で文字列にするわけです。日本の場合、一般的には “yyyy/mm/dd” 形式。

ですが、SQLite では “yyyy-mm-dd”形式 – 要するに、 ISO 8601 形式 (“T”は無くても良い)を日付として認識し、”yyyy/mm/dd”は日付として認識してくれない用なのです。

ためしに、コンソールでこんな風にやってみると…

sqlite> select datetime(‘2010-01-20′);
2010-01-20 00:00:00
sqlite> select datetime(‘2010/01/20′);

sqlite>

スラッシュ区切りは日付にあらずと言うわけです。が、そこは弱い型付けの SQLite、しっかりInsertは成功してるんですね。さらに、コンソールでのSelectも成功。 System.Data.SQLite で変換失敗というわけです。

解決策は意外と簡単で、
1)パラメータ渡すのにいちいち ToString しないで DateTime で渡す
2)どうしても ToString したかったら、 ToString(“s”) を使う。
3)DataSet を使う

データセットを使ってみた例

相手が SQL Server とか Oracle とかなら、そもそもコンパイルか Insert 時にエラーになりそうですが、 SQLite の場合にはこうなりますってことで。

SQLite の日付はUTC

 SQLite って何ってのは、とりあえずググって下さい。 本家はコチラ www.sqlite.org 。

どうやって落とすか、インストールするか、使うかってのも、色々リソースがあるので省略。
どうやってDataSetをデザインタイムでいじるのかってのも、コチラ http://sqlite.phxsoftware.com/ から。
で、本題。SQLite での日付型は、UTCで管理されるというオハナシ。
たとえば
CREATE TABLE test2(id int, name text, UpdateDate datetime);
なんてのがあって、
insert into test2 values(1,’Tokyo’,datetime(‘now’));
なんてカマして安心していると、中に入っているのはUTCというコトです。

安心しきってると、日付データがもれなく9時間ずれます。プログラム内で現在時刻と比較したり、SQL Server とかと比較したりすると、軽めに死ねそうです。

で、どうするか。

1) SQLを工夫する
select datetime(UpdateDate,’localtime’) from test2;
とすると、ローカルタイムに変換してくれます。
データセットデザイナでやると、型推論がうまくいかないのか、Object型になっちゃうのが難点。

2) DataSet でなんとかする
DataSet – DataTable の当該行を選択してプロパティ→DateTimeMode を”Utc”に変更。
そうすると、DateTime の Kind プロパティが “Utc”になるので、ToLocalTime してOK。

3) コード内でアレコレ
DBから取ってきてそのまま突っ込んでも、”Kind”プロパティが”Unspecified”のママです。ようするに、「時刻がUTCか地方時なのか知らないけど、とにかく入ってるよ。」状態ってことです。
まぁ、確かに日本は夏時間もないのでそのまま9時間加算してあげれば良いような気もします。ただ何となく個人的にどうにも居心地が悪いのです。
やるとすれば、こんな感じかなと…

‘—-まずはDBから取ってきた生の状態が DateTime の中身。
Dim dteResult As DateTime = CDate(objCommand.ExecuteScalar())
‘—-これをUTC時刻として(つまりオフセット0で) DateTimeOffset に入れる。
Dim dtoLocal As New DateTimeOffset(dteResult, New TimeSpan(0))
‘—-ここで、地方時を呼び出す
Console.WriteLine(dtoLocal.LocalDateTime)
‘—-これできちんと地方時に変換されました。


ま、どの方法がどうこうもあるけど、知っとかないと痛手くらいそうな場所なのでメモ。