2013年8月17日土曜日

SQL の migration を SQLAlchemy で語りき

概要
 SQLAlchemy の migration 用ツール alembic でDBの初期設定を SQL script のデータを流し込み効率化する(sqliteでのみ検証)


対象者
 SQLAlchemyを軽度に使っている初級以上の python ユーザー、もしくは Rails 等の軽量フレームワークを使っている人


背景
 今年の GW に Ruby on Rails (以下 Rails) を触ってみて、いかんせん小骨に刺さっていたことがある。なぜ彼らは”SQL構文を覚える必要がない”ことを強調しているのだろうか。
 サーバーサイドの開発はかなり効率化されている。MVC の分離に始まり、ルーティング、テンプレートやテストツール、そしてデータベースの構造を Migrate してバージョン管理しやすいようにしている。だが、その実現には SQL 構文ではなく、専用の構文を覚えることを強制している。別に強制は珍しい話ではない。多くの軽量フレームワークで行われていることだ。Rails の設定には yaml を使うことや、python の flask ではテンプレートに jinja2 を使うことなどである。そもそも、業界だって sass や node.js など覚えるべきことはどんどん増えている。ソフトウェア業界にいる以上、このような現象は当然であり、諦めるしかないことと皆覚悟している。このような状況で SQL だけを覚える必要がないというのはあまりに滑稽で、言い逃れに聞こえかねない。だとすると、本音は違うところにあると考えるのが自然だろう。実際は、SQL 構文を使わせないようしたいのだ。

 使わせない理由は、SQL の文法は差分管理に向かないことだろう。中身のデータは永続化しているが、構造は開発中は日々変更されるし、運用中の変更もありえる。SQL構文にはその差分管理を記載する方法はない。ALTER構文があるがバージョンと紐付かないのである。つまり、継続的インテグレーションには不向きだということだ。だからといって、SQL 構文を全く使わせないのはおかしい。例えば、DB の設計では ER 図を用いて行うことが多い。僕の場合は Eclipse の ERMaster plug-in を用いて行うが出力は SQLのスクリプトで出すことが多い。差分管理には向かないにせよ、最初の立ち上げには SQLのスクリプトファイル でDDLを作り、それを流しこむことで初期テーブルを作ったほうが立ち上げ効率は良いだろう

イメージ的には
rails generate scaffold friend name:string address:string

rails generate scaffolds file=friend.sql

とできればどれだけ楽だろうか。
これができないから SQLのスクリプトを各フレームワーク用のスクリプトに手作業翻訳とはありえない話だ。と、いうことで Rails でやり方を延々と探していたが、ruby はまだまだ弱いので見つけられなかった。その中で使い慣れた python を用いた方法が見つかった。


準備
  1. 環境は windows7 。pythonは2.7.3
  2. python に sqlalchemy(0.8.2), alembic(0.6.0), sqlahelper(不明) を pip か easy_install なりを使って入れておく。
  3. > mkdir alembic_test # ワーキングディレクトリ
  4. > cd alembic_test
  5. > alembic init alembic # alembic はプロジェクト名なので適当でよい
  6. フォルダ直下に DDLのsqlスクリプト "test.sql" を置く



test.sql
DROP TABLE IF EXISTS REFERENCE;
CREATE TABLE PACAKGE(
    _ID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL UNIQUE,
    OWNER TEXT
)
実装

alembic.ini を開き読み込み用の SQL スクリプト名を書いておく
+ base.script = test.sql
alembic/env.py
を以下のように変更する

# from myapp import mymodel
# target_metadata = mymodel.Base.metadata

- target_metadata = None
+ import os
+ # point 1
+ sqlscript=config.get_main_option("base.script")
+ if len(context.script.get_heads()) is 0 and os.path.isfile(sqlscript):
+     # point 2
+     with open(sqlscript) as f:
+         from sqlalchemy.engine import create_engine
+         from sqlalchemy import MetaData
+         engine = create_engine(config.get_main_option("sqlalchemy.url"))
+         connection = engine.connect()
+         for script in f.read().split(";"):
+             connection.execute(script)
+         connection.close()
+
+     # point 3
+     import sqlahelper
+     target_metadata=sqlahelper.get_base().metadata
+
+     # point 4
+     old_fn = context._proxy.context_opts['fn']
+     def migration_aspect(rev, _context):
+         old_fn(rev, _context)
+         template_args = context._proxy.context_opts['template_args']
+         opts = _context.opts
+         tmp = template_args[opts['upgrade_token']]
+         template_args[opts['upgrade_token']] = \
+             template_args[opts['downgrade_token']]
+         template_args[opts['downgrade_token']] = tmp
+         return []
+     context._proxy.context_opts['fn'] = migration_aspect
+ else:
+     target_metadata=None
+
+ #target_metadata = None
順に説明しよう

point 1
config.get_main_option()でalembic.iniに設定した読み込み用の sqlスクリプト"test.sql"を取得し、
ファイルの存在確認と、このスクリプトが一度目しか通らないように head という履歴情報にデータがないことを確認する

point 2
"test.sql"を読み込んで、とにかくdbファイルを作る
この方法は SQLAlchemy tutorial — MapFis を参考にした

pont 3
alembic には autogenerate という現在のDBの状況をスクリプト上の設定DBに変換するための自動生成機能がある。その設定には、target_metadata に特定のオブジェクトをはめる必要がある。今回は alembic使ってみた - podhmoの日記 を参考に point 2 で作ったdbを読み込むこととする

point 4
先ほどまでのものだけだと、バージョンアップすると、空のDBが作られ、バージョンダウンするとpoint 2のテーブルが作られるスクリプトが生成される。望ましいのと逆なのだ。手作業で入れ替えるのも面倒なのでむりやり、autogenerate が動作した時の変換関数をラッピングして、処理後にバージョンアップコードとバージョンダウンコードを入れ替えてしまう。かなり強引だが、特定のファイルのみの変更で済ませるにはこれしかなかった


実行と結果
実装が手間だった分、ここから先は alembic の仕組みに従うので簡単
> alembic revision --autogenerate -m "generate"
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate] Detected removed table u'PACAKGE'
INFO  [alembic.autogenerate] Detected removed table u'sqlite_sequence'
  Generating ***\alembic\versions\21b9c4f42450
_g
  enerate.py...done
 自動生成された ****\alembic\versions\21b9c4f42450_generate.py を開くと、
"""generate

Revision ID: 21b9c4f42450
Revises: None
Create Date: 2013-08-17 00:00:58.812000

"""

# revision identifiers, used by Alembic.
revision = '21b9c4f42450'
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(u'sqlite_sequence',
    sa.Column(u'name', sa.VARCHAR(), nullable=True),
    sa.Column(u'seq', sa.VARCHAR(), nullable=True),
    sa.PrimaryKeyConstraint()
    )
    op.create_table(u'PACAKGE',
    sa.Column(u'_ID', sa.INTEGER(), nullable=False),
    sa.Column(u'NAME', sa.TEXT(), nullable=False),
    sa.Column(u'OWNER', sa.TEXT(), nullable=True),
    sa.PrimaryKeyConstraint(u'_ID')
    )
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table(u'PACAKGE')
    op.drop_table(u'sqlite_sequence')
    ### end Alembic commands ###

と test.sql に対応したものが出来上がる。


まとめ
 実際には、8個程度のテーブルを使っているが難なく作ることができた。60行程度書くのが嫌なために、かなり時間をかけたものである。また、フレームワーク標準搭載のリレーションテーブルが作られてしまうので、Python の O/Rマッパー SQLAlchemy を使ったリレーショナルマッピング基本 4 パターン | CUBE SUGAR STORAGE でも見ながら手直しが必要だろう。
 最近は継続的な開発ばかり目がいっているが、立ち上げの効率化ほど重要な事はない。
このあたりのツールがもっと揃うことを切に願うばかりだ

0 件のコメント:

コメントを投稿