カテゴリー別アーカイブ: Database

過去にやった「リレーショナルデータベースの間違った使い方」

いきあたりばったりのアーキテクチャと教訓:リレーショナルデータベースの間違った使い方10項目

動的なテーブルの作成 ⇒ INSERTのトリガで月ごとのテーブル作るようにしたらINSERTが遅すぎて失敗しました。
テーブルをキャッシュとして使う ⇒ Railsではセッションキャッシュに使ってたけど、のちにcookieセッションに変わったと思う。
テーブルをキューとして使う ⇒ やりました。Dequeを使うのがいいのですが、自作のデータグリッドウィジェットと自作のTcl版ActiveRecordの依存性が高すぎて変更が大変。いまだに変えられない。
テーブルをログとして使う ⇒ 他にどうすんの?KVSってやつ?検索とか似たような機能あるの?
分散したグローバルなロック ⇒ なにそれ?
ストアドプロシージャ ⇒ 使ったことない。
使われない項目 ⇒ いくつかあるけど、そんなに多くない。検索キーにならないカラムが増えていくのを、YAML形式とかでテキストカラムに押し込めるべきか悩んでる。
ORMによって繰り返されるクエリ ⇒ よくやりました。暗黙的遅延ロード機能なんかつけるからだ。ActiveRecordなら:include=>"groups"、EFなら.Include("groups")を使う。
負荷のコントロール ⇒ DBじゃないけど、ウェブサーバのリバースプロキシとかはよくやりました。メモリリークだらけのRailsには必要だった。

暗号化APIを有効にしたSQLite3のTclバインディングをコンパイルする

コンパイルに使用したもの
ActiveTcl 8.4.19.5 http://www.activestate.com/activetcl/downloads
wxSQLite3(wxsqlite3-2.1.2.zip) http://sourceforge.net/projects/wxcode/files/Components/wxSQLite3/
SQLite3ソースコード(sqlite-autoconf-3070701.tar.gz) http://www.sqlite.org/download.html
MinGW(mingw-get-inst-20110530.exe) http://sourceforge.net/projects/mingw/files/Automated%20MinGW%20Installer/mingw-get-inst/

作業フォルダはどこでもいいですが、今回はc:\srcとします。
そこにSQLite3ソースコードとwxSQLite3を展開します。
こんな感じ。

c:\src
├─sqlite-autoconf-3070701
│  └─tea
│      ├─doc
│      ├─generic
│      ├─tclconfig
│      └─win
└─wxsqlite3-2.1.2
    ├─build
    ├─build29
    ├─dbadmin
    │  └─images
    ├─docs
    │  └─html
    ├─include
    │  └─wx
    ├─lib
    ├─samples
    ├─sqlite3
    │  ├─include
    │  ├─lib
    │  └─secure
    │      ├─aes128
    │      ├─aes256
    │      └─src
    │          ├─codec
    │          └─codec-c
    ├─src
    └─website

以下のフォルダ内の全てのファイルを
C:\src\wxsqlite3-2.1.2\sqlite3\secure\src\codec-c

ここに上書きコピーします。
C:\src\sqlite-autoconf-3070701\tea\generic

そして、以下のソースコードをテキストエディタで開きます。
C:\src\sqlite-autoconf-3070701\tea\generic\tclsqlite3.c

4行目の"../../sqlite3.c"を

#ifdef USE_SYSTEM_SQLITE
# include 
#else
#include "../../sqlite3.c"
#endif

"sqlite3secure.c"に書き換える。

#ifdef USE_SYSTEM_SQLITE
# include 
#else
#include "sqlite3secure.c"
#endif

これで、コンパイルの準備はできました。

さて、MinGW shellを起動しましょう。

ディレクトリを移動してコンパイルします。

cd /c/src/sqlite-autoconf-3070701/tea
$ ./configure CFLAGS="-DSQLITE_HAS_CODEC"
$ make

デフォルトではAES128コーデックが使われます。
試してませんが、AES256にしたければ、以下のようにすればよいと思います。

$ ./configure CFLAGS="-DSQLITE_HAS_CODEC -DCODEC_TYPE=CODEC_TYPE_AES256"

wxSQLite3 AES128ならGUIツールのSQLite2009 Proが対応していますが、AES256に対応しているツールはなさそうなので、セキュリティの強化以外の理由でAES256を選択する理由はないと思います。

これで以下のファイルができました。これ単体でTclのパッケージです。

C:\src\sqlite-autoconf-3070701\tea\sqlite3771.dll

直接tclshからloadするか、pkgIndex.tclとともにTcl/libにインストールしてpackage requireすることもできます。

さて、テストしてみましょう。

load sqlite3771.dll Sqlite3
 
# create plain database file
sqlite3 pdb plain.db
 
# create encrypted database file
sqlite3 sdb secret.db -key password
 
# SQL test script
# 1. create table
# 2. populate test data
# 3. execute query
set sql {
	create table users (
		id integer primary key autoincrement not null,
		name text,
		age integer
	);
	insert into users (name, age) values ("山田太郎", 30);
	select * from users;
}
 
pdb eval $sql
#=>; 1 山田太郎 30
pdb close
 
sdb eval $sql
#=>; 1 山田太郎 30
sdb close
 
# Re-open plain.db
sqlite3 pdb plain.db
pdb eval {
	select * from users;
}
#=>; 1 山田太郎 30
pdb close
 
# Re-open secret.db without a key
sqlite3 sdb secret.db
sdb eval {
	select * from users;
}
#=>; file is encrypted or is not a database
sdb close

既存のデータベースファイルを暗号化するには、rekeyするか、dumpを取得して
暗号化した新規データベースでrestoreすればOKです。
この作業には、wxSQLite3に付属するコンパイル済みのshellを使用してください。
C:\src\wxsqlite3-2.1.2\sqlite3\secure\aes128\sqlite3shell.exe

追記:Tclからもできます。

load sqlite3771.dll Sqlite3
# Encrypt plain database
sqlite3 pdb plain.db
pdb rekey "password"
pdb close
 
# Reopen as plain database
sqlite3 pdb plain.db
pdb eval {
	select * from users;
}
#=>; file is encrypted or is not a database
 
# Decript secret database
sqlite3 sdb plain.db -key password
sdb rekey ""; # specify null string as encryption key
sdb close
 
# Re-open plain.db
sqlite3 pdb plain.db
pdb eval {
	select * from users;
}
#=>; 1 山田太郎 30
pdb close

最後はちょっとはしょった説明になりましたが、できてしまえば結構簡単です。
tclsqliteの暗号化APIについてのドキュメントはないのですが、ネイティブの関数と基本的に変わりはないと思います。
間違ったコマンドを与えてやるとエラーメッセージに関数リストが出てきたり、
関数に間違った引数を与えてやることで使い方が出てきたりするので、いろいろ試してみるとよいと思います。

(tea) 1 % load sqlite3771.dll Sqlite3
(tea) 2 % sqlite3 sdb secret.db -key password
(tea) 3 % sdb ?
bad option "?": must be authorizer, backup, busy, cache, changes, close, collate, collation_needed, commit_hook, complete, copy, enable_load_extension, errorcode, eval, exists, function, incrblob, interrupt, last_insert_rowid, nullvalue, onecolumn, profile, progress, rekey, restore, rollback_hook, status, timeout, total_changes, trace, transaction, unlock_notify, update_hook, version, or wal_hook

なお、.NET Frameworkから使えるADOプロバイダとしてSystem.Data.SQLite.dllが暗号化に対応していますが、これはRSA-MS Cryptだそうです。名前しか分かりませんがとりあえずAESとは別物です。SQLite Encryption Extension($2,000)は4つのコーデックがコンパイル時に選択できるようです。

SQLite3にformatみたいな関数がないのでTclでやる

1-12みたいな連番文字列を01012に一括変換したかった。別のカラムに分けるのが普通でしょうが、諸事情により。
SQLiteの組み込み関数には日付の操作関数は充実してるけど、文字列操作関数は大したものがない。
それで、Tclでやることにした。

package require sqlite3
sqlite3 db ./app.db; # handle and database file
 
db eval {SELECT * FROM squence WHERE field LIKE "%-%"} values {
	set R-V $values(field)
	if {[scan ${R-V} %d-%d R V] == 2} {
		set RRVVV [format %02d%03d $R $V]
		db eval [format {UPDATE squence SET field="%s" WHERE id=%d} $RRVVV $values(id)]
	}
}

02012みたいなのは、クォートしないと数値とみなされて2012みたいになってしまうので注意が必要です。
LIKE以外にもGLOBやREGEXPも使えるらしい。

050-で始まる電話番号を持つ人のリストを取得するなど。
select * from users where phone_number REGEXP "^050-.+$"

C#とSQLiteの日時データ型とパフォーマンス

1年ほど前に手がけた仕事では、C#+Windows.Forms+Entity Framework+System.Data.SQLite という組み合わせでアプリケーションを作成しました。

次に始まる開発でもこの組み合わせにするつもりですが、いろいろとやりにくかった点をまとめておきたいと思います。まずは、SQLiteの日時カラムについて。

続きを読む

LINQ to Entities + SQLiteでCAST(expression AS type)

前回のエントリにて、アルファベットと数字の混在する部屋番号というのを扱ったけれど、結局、文字部分を削除しても問題ないということになったので、それはまあ、良くなった。実はいろいろ調べてAlphanumeric sortというのがあると知ったのだが、まあ、いろいろ役にたつことだろう。

さて、それならわざわざ文字列にしなくてもよいだろうということで、INTEGERに変更した。それでずいぶん無駄なロジックやキャストなんかが減って、すっきりした。

しかし、そこに新たな要求が来た。

  1. 部屋番号の範囲を指定して、複数の居住者を検索する。
  2. 部屋番号を文字列とみなして前方一致検索する。

1. の場合、INTEGERなので問題ない。

2. の場合、WHERE句で文字列にキャストしないとLIKEが使えない。クライアントサイドでの絞込みは馬鹿らしい。

正直こんなもん、SQLiteなら文字列も数値も同じだから、LIKEが使えていいんじゃないのかと思うけど、LINQ to Entitiesではそうもいかない。ObjectQuery<T>のWhereは条件に文字列とプレースホルダが使えるから、その線で探してみた。CASTを使えばよいらしい。Googleで調べるのに2時間もかかったよ。はー疲れた。

ObjectQuery<User> userQuery = entities.User.Include("Room")
  .Where("CAST(it.Room.room_no AS System.String) LIKE @keyRoom",
  new ObjectParameter("keyRoom", keyRoom + "%"));

CAST (expr AS type) というのがSQLiteにもあって、それは結構すぐ見つかったけど、typeのところがC#の型になるとは思い浮かばず、TEXTか?VARCHAR(4)とかか?と意味不明なエラーに苦しんでいました。System.Data.SQLiteのせいじゃないかと思って、dotConnect for SQLiteを自腹で買おうか真剣に悩んで見つけたサンプルをCASTでgrepしたら見つかりました。

Download dotConnect for SQLite

SQLite3にDateTime.Ticksでタイムスタンプを保存したら

ぱっと見ただけじゃ時刻が分からない。ものすごい数字に強い人ならともかくも。

しかし、多分integer(C#ではlong)で保存した方がインデックスが有効になるし、速いと思うので、Ticksで保存したいと思う。

そんな場合は、こうすればよい。

select datetime(created_at/pow(10,7) - 62135888400, 'unixepoch', 'localtime') as d, * from events;

長い。unixepochに変換するためにこうなってしまう。もっと直接的に計算できる項を見つければいいんだろうけど。SQLiteではCREATE FUNCTIONできないようだし。

SQL Server 2008 Express Editionのインストールではまる

VS2008についてきたSQL Server 2005 Expressからアップデートした。しかし、Management Studioは2005のままだったので、なんとなく気持ち悪くてこいつも2008にしようとしたところ、なぜかはまった。

それは、インストールルールのチェックで、Visual Studio 2008 SP1を要求されるという問題だった。すでにSP1はインストールされているんだけど・・・。

これがなかなかクリアできず、結局SQL Server 2008ごとアンインストールしてインストールしなおした。

しかし、同じ問題が出る。異様に時間のかかるインストール・アンインストールに発狂しそうになりながら、待っている間いろいろ調べていると、ようやく答えにたどり着いた。

CSS SQL Server Engineers  SQL Server 2008, Visual Studio 2008 SP1, and .Net Framework 3.5 SP1 explained....

結局、以前F#のインタラクティブシェルを使ってみたくて入れた、Visual Studio 2008 Shell (integrated mode) というやつが原因だった。これをアンインストールするとすんなり進んだ。

なお、僕の環境では入っていなかったが、Visual Studio Tools for Applications 2.0というやつも削除する必要があるらしい。こちらは再頒布してる場所が見つからなかった。どうやって戻すのかは不明。