djangoとsqlalchemyでunion allで繋げるようなものを参照・更新含めて考えてみた
djangoとsqlalchemyでunion allで繋げるようなものを参照・更新含めて考えてみた。
はじめはdjangoのormでviewを使った方法を考えて、ちょっとしたことからdjangoが思いもしない挙動をしたのでsqlalchemyではどうだったのか確認しようとした。
状況説明
以下のような仮想的な状況を考えた。
- X,Y２つのテーブルがある。(例えばactiveなテーブルとhistoryテーブルのようなイメージ)
- これら２つを対象としたqueryを書きたい
- 発行するSQLの回数を減らしたい
実際に行う動作は以下の通り。
- X,Yをunion allするview(XorY)を考える(参照時)
- 更新時には取得したXorYを各インスタンスに振り分けながら全てのものに対して同じ値で更新してみる。
- (実際の利用時には、恐らく、全てを更新する必要はないかもしれない)
djangoの場合
参照について
djangoは内部で繋げるDBがどのような状況であるか全く関知しない。唯一気をつける要素としてはmanaged=False migrationの対象にしないこと程度。
したがって自分でDDLを実行してテーブル(view)を生成する。
生成するViewは以下のようなもの。
create view xory as select id as x_id, -1 as y_id, -1 as id from x union all select -1 as x_id, id as y_id, -1 as id from y
idフィールドがあるのはdjangoが暗黙のうちにサロゲートキーとしてidフィールドを仮定するため(どのようなフィールドを主キーにするかは設定で変えられる) ちょっと不格好。
更新について
更新する際に、取得したXorYのオブジェクトから適切なインスタンス(X or Y)に振り分けなければいけない。 実際に試してみたところ、単にid要素がNoneでないインスタンスを作れば良い。既にデータがDBに存在するモデルオブジェクトとして扱われるようだ。 特に更新時に不整合的なチェックも起きずに、UPDATE文が発行された。
この動作に驚いた。てっきり何らかの不整合が生じてエラーメッセージを目にするかと思いながら実行してみたが何事も無くすんなり通ってしまった。
結果のgistは以下。djangoのormを1ファイルで動作させるためのちょっとした細工は存在している。
https://gist.github.com/podhmo/a08abde229290838d6c5
sqlalchemyの場合
djangoの更新についての挙動にすごく驚いたものの、よくよく考えて見れば、djangoのormはDBと密につながっているというよりは単なるデータアクセスのための仲介オブジェクトに過ぎないということを思い出したのだった。そこでsqlalchemyではどうだったか試してみることにした。
参照について
とりあえず、djangoの場合と同様な形式にすることを考え、viewを定義する方法を調べた。 以下のようなsqlalchemyのリポジトリのwikiページがあったのでこれを参考にすることにした。
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views
自分でDDL定義を書いてDDLElementというものを作っている。そこで作られたview()関数にselectableとしてviewで使いたい文を渡している。
ちょっとハマったのは定数-1を返す部分をどのように記述すれば良いのか分からなかった所。以下の様にするのが正しかった。
sa.literal_column("-1").label("y_id") # -1 as y_id に対応
それ以外は普通にsqlalchemyの式表現をつなげて望みのものを作り上げれば良い。
https://gist.github.com/podhmo/d8a3b760f0c061508386
改善が必要
この辺りで、そう言えば、sqlalchemyならviewを介さずとも、mapperに直接クエリーをマッピングできたような記憶を思い出した。
また、XorYを使って検索した結果がおかしいということに気づいた。返される行数で期待する数は6なのに対して、qs.count()を数えた際には6個、len(list(qs.all()))は4個だった。
よくよく考えてみると以下のような形で値を生成していた。
----------- x id | y id ----------- 1 | -1 2 | -1 3 | -1 -1 | 1 -1 | 2 -1 | 3
ここで値を見てみると、x_idだけで絞りこまれているようだ。sqlalchemyは取得したqueryがオブジェクトとなる時identity mapで管理される。これはmodelとprimary keyのペアをtupleで持った構造をkeyとしているが、このprimary keyの設定を忘れていたらしい。__mapper_args__にprimary_keyというオプションを使って設定を渡せるようだ。
結局、以下の様にXorYを定義することで、viewを削除、identity mapのprimary keyの扱いを正しくする事ができた。
class XorY(Base): lhs = sa.select([X.name, X.id.label("x_id"), sa.literal_column("-1").label("y_id")]).select_from(X.__table__) rhs = sa.select([Y.name, sa.literal_column("-1").label("x_id"), Y.id.label("y_id")]).select_from(Y.__table__) __table__ = sa.union_all(lhs, rhs).alias() __mapper_args__ = { "primary_key": [__table__.c.x_id, __table__.c.y_id] } query = Session.query_property()
更新
更新の際に、XorYからX,Yに振り分けなければいけないが。これがなかなか上手くいかず。queryを無くす事もできなかった。
Session.query.get(x_id)# XorYはXではないので当然queryが発行されるX(id=self.x, name=self.name)# 新しいインスタンスを生成したと解釈して、commit時にidがconflictしてエラー
djangoの挙動に驚いていたときに、感じた事柄がsqlalchemy側からみた場合には自然な反応であった事に安堵したものの。 djangoに出来ることがsqlalchemyでできないというのはなんとなく悔しさを感じる。
identity mapの概念を考えると全くこれは正しい振る舞いでは有るのだけれど、発行するクエリーをdjangoのように減らすためにはidentity mapをなんとかごまかす必要がある。
結局こうした
内部的にはidentity mapはstate(InstanceState)という形式でアクセスする。内部を覗いた所このstateがkeyを持っているとDBに存在しているという扱いになるようだ。また、session_idを持っているとこれがSessionで管理する際のキーとして使われているようだ。ちなみにstateは_sa_instance_stateという変数に格納されているが、それを直接触るのは行儀が良くない模様だった。
def convert(self):
if self.x_id != -1:
x = X(id=self.x_id, name=self.name)
state = attributes.instance_state(x)
state.key = (X, (x.id, ))
state.session_id = self.query.session.hash_key
self.query.session.identity_map.add(state)
return x
else:
y = Y(id=self.y_id, name=self.name)
state = attributes.instance_state(y)
state.key = (Y, (y.id, ))
state.session_id = self.query.session.hash_key
self.query.session.identity_map.add(state)
return y
色々試行錯誤した結果上手くいったsqlalchemyのgist。
