WSGIアプリからDBSprocketsを使う
先日作ったプロキシサーバに管理画面を付けたくなったので、前から気になっていたDBSprocketsを素のWSGIから使えるようにしてみた。
Pylons、TG2には依存せずにDBSproketsDBMechanicの全機能が一応使えるはず。
- dbmechanic.py
""" dbmechanic module for WSGI Initial version by Nozomu Kaneko <nozom.kaneko _at_ gmail {d0t} com> based on the Pylons version. """ import pkg_resources from paste.httpexceptions import HTTPException, HTTPFound from paste.registry import StackedObjectProxy from toscawidgets.api import Widget, WidgetBunch from genshi.template import TemplateLoader from sqlalchemy.exceptions import IntegrityError, ProgrammingError from dbsprockets.sprockets import Sprockets from webob import Request from selector import Selector __all__ = ['DBMechanic', 'c'] c = StackedObjectProxy(name="C") def render_genshi(template_name, genshi_loader, **kw): template = genshi_loader.load(template_name) return template.generate(c=c, **kw).render() def flash(environ, msg, status=None): environ["flash.message"] = msg environ["flash.status"] = status or "status_ok" def get_flash(environ): msg = environ.get('flash.message', '') environ['flash.message'] = '' return msg def get_status(environ): status = environ.get('flash.status', '') environ['flash.status'] = '' return status def redirect_to(location): headers = [('location', location)] raise HTTPFound(headers=headers) def message_from_exception(e): message = None if hasattr(e,"message"): message = e.message if isinstance(message,str): try: message = message.decode('utf-8') except: message = None return message class ContextObj(object): pass class DBMechanic(object): sprockets = None def __init__(self, provider, controller, *args, **kwargs): self.provider = provider self.sprockets = Sprockets(provider, controller) self.controller = controller self.genshi_loader = TemplateLoader([ pkg_resources.resource_filename('dbsprockets.dbmechanic.frameworks.pylons', 'templates'), ]) selector = Selector(consume_path=False) selector.add('/', GET=self.index) selector.add('/tableView/{tableName}', GET=self.tableView) selector.add('/tableDef/{tableName}', GET=self.tableDef) selector.add('/addRecord/{tableName}', GET=self.addRecord) selector.add('/editRecord/{tableName}', GET=self.editRecord) selector.add('/add', POST=self.add) selector.add('/edit', POST=self.edit) selector.add('/delete/{tableName}', GET=self.delete) self.app = selector sprocket = self.sprockets['databaseView'] self.databaseValue = sprocket.session.getValue() self.databaseView = sprocket.view.widget self.databaseDict = dict(controller=self.controller) def __call__(self, environ, start_response): # setup app env registry = environ['paste.registry'] registry.register(c, ContextObj()) try: return self.app(environ, start_response) except HTTPException, http_exc: resp = http_exc.response(environ) return resp(environ, start_response) def index(self, environ, start_response): c.w = WidgetBunch() c.w.databaseView = self.databaseView c.w.mainView = Widget("widget") # flash(environ, "Welcome!") start_response('200 OK', [('Content-Type', 'text/html')]) return render_genshi('index.html', self.genshi_loader, tg_flash=get_flash(environ), tg_status=get_status(environ)) def tableView(self, environ, start_response): request = Request(environ) tableName = request.urlvars['tableName'] c.tableName = tableName c.page = request.params.get('page', 1) c.recordsPerPage = request.params.get('recordsPerPage', 25) #this should probably be a decorator c.page = int(c.page) c.recordsPerPage = int(c.recordsPerPage) sprocket = self.sprockets['tableView__'+tableName] c.w = WidgetBunch() c.w.mainView = sprocket.view.widget c.w.databaseView = self.databaseView c.mainValue = sprocket.session.getValue(values=request.params, page=c.page, recordsPerPage=c.recordsPerPage) c.mainCount = sprocket.session.getCount(values=request.params) for key, value in self.databaseDict.iteritems(): setattr(c, key, value) start_response('200 OK', [('Content-Type', 'text/html')]) return render_genshi('tableView.html', self.genshi_loader, tg_flash=get_flash(environ), tg_status=get_status(environ)) def tableDef(self, environ, start_response): request = Request(environ) tableName = request.urlvars['tableName'] c.tableName = tableName sprocket = self.sprockets['tableDef__'+tableName] c.w = WidgetBunch() c.w.mainView = sprocket.view.widget c.w.databaseView = self.databaseView c.mainValue = sprocket.session.getValue() for key, value in self.databaseDict.iteritems(): setattr(c, key, value) start_response('200 OK', [('Content-Type', 'text/html')]) return render_genshi('index.html', self.genshi_loader, tg_flash=get_flash(environ), tg_status=get_status(environ)) def addRecord(self, environ, start_response): request = Request(environ) tableName = request.urlvars['tableName'] c.tableName = tableName sprocket = self.sprockets['addRecord__'+tableName] c.w = WidgetBunch() c.w.mainView = sprocket.view.widget c.w.databaseView = self.databaseView kw = {} for key, value in request.params.iteritems(): if key and value: kw[key] = value c.mainValue = sprocket.session.getValue(values=kw) for key, value in self.databaseDict.iteritems(): setattr(c, key, value) start_response('200 OK', [('Content-Type', 'text/html')]) return render_genshi('index.html', self.genshi_loader, tg_flash=get_flash(environ), tg_status=get_status(environ)) def editRecord(self, environ, start_response): request = Request(environ) tableName = request.urlvars['tableName'] c.tableName = tableName sprocket = self.sprockets['editRecord__'+tableName] c.w = WidgetBunch() c.w.mainView = sprocket.view.widget c.w.databaseView = self.databaseView kw = {} for key, value in request.params.iteritems(): if key and value: kw[key] = value c.mainValue = sprocket.session.getValue(values=kw) for key, value in self.databaseDict.iteritems(): setattr(c, key, value) start_response('200 OK', [('Content-Type', 'text/html')]) return render_genshi('index.html', self.genshi_loader, tg_flash=get_flash(environ), tg_status=get_status(environ)) def add(self, environ, start_response): request = Request(environ) tableName = request.params['tableName'] params = request.POST.copy() try: self._createRelationships(tableName, params) self.provider.add(tableName, values=params) except (IntegrityError, ProgrammingError), e: message = message_from_exception(e) if message: flash(environ, message, "status_alert") request.urlvars['tableName'] = tableName return self.addRecord(environ, start_response) redirect_to(self.controller + '/tableView/' + tableName) def edit(self, environ, start_response): request = Request(environ) tableName = request.params['tableName'] params = request.POST.copy() try: self._createRelationships(tableName, params) self.provider.edit(tableName, values=params) except (IntegrityError, ProgrammingError), e: message = message_from_exception(e) if message: flash(environ, message, "status_alert") return self.editRecord(environ, start_response) redirect_to(self.controller + '/tableView/' + tableName) def delete(self, environ, start_response): request = Request(environ) tableName = request.urlvars['tableName'] params = request.GET.copy() self.provider.delete(tableName, values=params) redirect_to(self.controller + '/tableView/' + tableName) def _createRelationships(self, tableName, params): #this might become a decorator #check to see if the table is a many-to-many table first if tableName in self.provider.getManyToManyTables(): return #right now many-to-many only supports single primary keys id = params[self.provider.getPrimaryKeys(tableName)[0]] relationships = {} for key, value in params.iteritems(): if key.startswith('many_many_'): relationships.setdefault(key[10:], []).append(value) for key, value in relationships.iteritems(): self.provider.setManyToMany(tableName, id, key, value)
WSGIProxyで使うには、wsgiapp.pyに以下の変更を行う。
+import pkg_resources from paste.proxy import TransparentProxy +from paste.cascade import Cascade +from paste.urlparser import StaticURLParser +from paste.registry import RegistryManager +from paste.deploy.converters import aslist +from toscawidgets.middleware import TGWidgetsMiddleware +from toscawidgets.mods.wsgi import WSGIHostFramework +from genshi.template import TemplateLoader from sqlalchemy import engine_from_config +import elixir +from dbsprockets.sprockets import SAProvider import model +from dbmechanic import DBMechanic class ProxyAdmin(object): + def __init__(self, controller_hosts): + self.controller_hosts = controller_hosts + + dbmechanic = DBMechanic(SAProvider(elixir.metadata), '') + dbmechanic.genshi_loader = TemplateLoader([ + pkg_resources.resource_filename('wsgiproxy', 'templates'), + ]) + # Static files + staticapp = StaticURLParser( + pkg_resources.resource_filename('wsgiproxy', 'public')) + self.dbmechanic = Cascade([staticapp, dbmechanic]) + def __call__(self, environ, start_response): + if environ["HTTP_HOST"] in self.controller_hosts: + return self.dbmechanic(environ, start_response) query = model.HostNameMapping.query.filter_by(enabled=True) mapping = query.filter_by(hostname=environ["HTTP_HOST"]).first() if mapping: proxy = TransparentProxy(force_host=mapping.mapped) else: proxy = TransparentProxy() return proxy(environ, start_response) -def make_app(global_conf, **kw): +def make_app(global_conf, controller_hosts="", **kw): # Here we merge all the keys into one configuration # dictionary; you don't have to do this, but this # can be convenient later to add ad hoc configuration: conf = global_conf.copy() conf.update(kw) # Setup model model.metadata.bind = engine_from_config(conf, 'sqlalchemy.') model.setup_all() # model.create_all() # This is a WSGI application: - app = ProxyAdmin() + app = ProxyAdmin(aslist(controller_hosts)) + + # Setup ToscaWidgets + app = TGWidgetsMiddleware(app, WSGIHostFramework()) + + # Establish the Registry for this application + app = RegistryManager(app) return app
development.iniに１行追加する。
[app:devel]
# This application is meant for interactive development
use = egg:TinyWSGIProxy
sqlalchemy.url = sqlite:////%(here)s/data.db
+controller_hosts = admin p.p # config.privoxy.org
DBSprocketsに含まれるPylons用のテンプレート(dbsprockets/dbmechanic/frameworks/pylons/templates)は、そのままではうまく動かなかったので、以下の修正を行った。
--- index.html 2008-03-19 04:04:00.000000000 +0900 +++ index.html 2008-03-23 05:44:50.000000000 +0900 @@ -8,7 +8,7 @@ <body> <div class="tableList" style="background-color:#aaddff; float:left; clear:left; border:1px solid; margin:0px 5px 0px 0px; padding:10px;"> <b>Tables</b> - ${c.w.databaseView(value=databaseValue, controller=controller)} + ${Markup(c.w.databaseView(value=databaseValue, controller=controller))} </div> <div class="mainView" style="bg-color:#ffffff;"> <py:if test="not isinstance(tableName, Undefined)"> @@ -22,7 +22,7 @@ </py:if> <py:if test="mainView is not None"> - ${c.w.mainView(value=mainValue)} + ${Markup(c.w.mainView(value=c.mainValue) or '')} </py:if> </div> </body> --- tableView.html 2008-03-19 04:04:00.000000000 +0900 +++ tableView.html 2008-03-23 05:46:11.000000000 +0900 @@ -8,7 +8,7 @@ <body> <div class="tableList" style="background-color:#aaddff; float:left; clear:left; border:1px solid; margin:0px 5px 0px 0px; padding:10px;"> <b>Tables</b> - ${c.w.databaseView(value=c.databaseValue, controller=c.controller)} + ${Markup(c.w.databaseView(value=c.databaseValue, controller=c.controller))} </div> <div class="mainView" style="bg-color:#ffffff;"> <py:if test="not isinstance(c.tableName, Undefined)"> @@ -74,9 +74,9 @@ </div> <div style="float:clear"><br/></div> <py:if test="mainView is not None"> - ${c.w.mainView(value=c.mainValue)} + ${Markup(c.w.mainView(value=c.mainValue))} </py:if> - <div style="float:clear"/> + <div style="float:clear"> </div> <div style="float:right"> Records Per Page: <a href="${c.controller}/tableView/$c.tableName?page=1&recordsPerPage=10">10</a>
数カ所HTMLがエスケープされてしまう部分があったのでMarkup()で囲むようにした。最後の変更はデザインが崩れてしまうため。
あと、以下は必須ではないけど気分の問題で変更してみた。
--- master.html 2008-03-19 04:04:00.000000000 +0900 +++ master.html 2008-03-23 05:40:48.000000000 +0900 @@ -7,7 +7,7 @@ <xi:include href="tw_resources.html" /> <head py:match="head" py:attrs="select('@*')"> <meta content="text/html; charset=UTF-8" http-equiv="content-type" py:replace="''"/> - <title py:replace="''">Your title goes here</title> + <title>WSGIProxy Admin</title> <link py:for="css in tg_css" py:replace="ET(css.display())" /> <link py:for="js in tg_js_head" py:replace="ET(js.display())" /> <meta py:replace="select('*')"/> @@ -25,7 +25,7 @@ <!-- End of main_content --> </div> <div id="footer"> - <p>Pylons under the hood!</p> + <p>WSGI under the hood!</p> </div> </body>
これだけでも動作するのだけど、やっぱりデザインが入っていないと寂しいので、TurboGearsのプロジェクトテンプレートからpublicディレクトリを丸ごとコピーしてきて、最終的にはこんな感じになった。
