DBMechanic - dbsprockets

Updated Mar 17, 2008 by nerkles
Labels:	Featured

DBMechanic

Web tool useful for administrating sqlalchemy defined databases.

The purpose of DBMechanic is to act as a front-end to a user's SQL Database. For the time being it only supports Turbogears and sqlalchemy, but the possibility exists for a back-end database system based on SQLObject and TG1, even ZODB or some user-based database can be supported by writing your own provider class. Right now we are concentrating on the bleeding edge of web frameworks. In the future the hope is that DBMechanic will not only be able to view and manipulate the database, but also modify the design.

Usage

Turbogears 2.0

from model import metadata
from dbsprockets.dbmechanic.frameworks.tg2 import DBMechanic
from dbsprockets.saprovider import SAProvider

class RootController(BaseController):
    dbmechanic = DBMechanic(SAProvider(metadata), '/dbmechanic')

Turbogears 1.0

Basic Example

from model import metadata
from dbsprockets.dbmechanic.frameworks.tg import DBMechanic
from dbsprockets.saprovider import SAProvider

class Root(controllers.RootController):

    dbmechanic = DBMechanic(SAProvider(metadata), '/dbmechanic')

Security

Of course, you don't want unauthorized users to modify your database, so it would be prudent to add some code like this:

from model import metadata
from dbsprockets.dbmechanic.frameworks.tg import DBMechanic
from dbsprockets.saprovider import SAProvider

class Root(controllers.RootController):

    dbmechanic = DBMechanic(SAProvider(metadata), '/dbmechanic')
    dbmechanic = identity.SecureObject(dbmechanic,identity.in_group('admin'))

Now only people who belong to the admin group can view/edit your dbmechanic instance.

Add these lines to your project's middleware.py:

At the end of the imports section at top:

from toscawidgets.middleware import TGWidgetsMiddleware
from toscawidgets.mods.pylonshf import PylonsHostFramework

And these below the CUSTOM MIDDLEWARE HERE line, inside make_app:

    host_framework = PylonsHostFramework(default_view="genshi")
    app = TGWidgetsMiddleware(app, host_framework)

Create a controller called dbmechanic (or whatever you prefer):

paster controller dbmechanic

In the new controller, add these lines to the imports section (replace myproject with the name of your project):

from dbsprockets.dbmechanic.frameworks.pylons.dbmechanic import DBMechanic
from dbsprockets.saprovider import SAProvider
import myproject.model as model

Delete the index function and insert this in the controller:

    def __init__(self, *args, **kwargs):
        model.meta.Session.configure(bind = model.meta.Session)
        model.meta.metadata.bind = model.meta.engine
        saprovider = SAProvider(model.meta.metadata)
        dbmechanic_controller = DBMechanic(
                provider=saprovider,
                controller='/dbmechanic', *args, **kwargs)
        self.index = dbmechanic_controller.index
        self.tableDef = dbmechanic_controller.tableDef
        self.tableView = dbmechanic_controller.tableView
        self.addRecord = dbmechanic_controller.addRecord
        self.editRecord = dbmechanic_controller.editRecord
        self.edit = dbmechanic_controller.edit
        self.add = dbmechanic_controller.add
        self.delete = dbmechanic_controller.delete

That's all.