[Grok-dev] Grok with existing SQLAlchemy mapped classes

[Kevin Teague]

Yes, I had the same requirements, that the classes that the ORM is
mapping onto don't have any Grok (or web-specific) dependencies. In my
case, this was for a bioinformatic setting, where some developer's
were working with the model from command-line scripts, and other
developer's needed to display that same model in an internal-only web
application.

For the "core" model, I made a package who's only dependency is on SQL
Alchemy. Inside that package the mappings are defined. There are
functions for:

def setup_tables(metadata, tables):
    ...
def setup_mappers(tables, mappers):
    ...

Then in the web-specific package which depends upon Grok and
collective.lead there is a class to provide the IDatabase interface
for collective.lead which simply references the core package:

    class SbsDatabase(grok.GlobalUtility, Database):
        grok.provides(IDatabase)
        grok.name('sbs')

        @property
        def _url(self):
            conf = SolexaConfig()
            db = conf.solexa_metadata_db
            return URL( drivername='postgres',
                        username=db['username'],
                        password=db['password'],
                        host=db['host'],
                        port=None,
                        database=db['name']
                      )

        def _setup_tables(self, metadata, tables):
            """
            Map the database structure to SQLAlchemy Table objects
            """
            solexa.model.saconfig.setup_tables(metadata, tables)

        def _setup_mappers(self, tables, mappers):
            """
            Map the database Tables to SQLAlchemy Mapper objects
            """
            solexa.model.saconfig.setup_mappers(tables, mappers)

Then the next step is to map the model classes to URLs, so that
traversal and view.url() works as expected. Here I wrote module which
is kinda messy (some of the code below is just noise ... it could use
a bit of a clean-up) but it does the job for our somewhat modest
requirements. The key is to provide traversers that know how to get
from a URL to an SQL Alchemy model, and then when those model objects
are instantiated use the zope.location.location.locate() function to
give the object the correct __name__ and __parent__ attribtues.

Overall, I'm pretty happy with this solution though ... we've had many
projects in my eight years in bioinformatics where we have "command-
line only" developers and "web only" developers, and they've always
ended up developing their own logic specific to "web" or "command-
line" and maintenance always becomes a royal PITA. By keeping the
dependency on the core model package to just SQL Alchemy the command-
line only people don't get freaked out by a bunch of web-specific code
and we can get the dev's to collaborate on a shared model :)


    """
    The sbs.locate module handles mapping from SQL Alchemy objects
    into our URL structure.
    """
    import grok
    import zope.location.location
    from collective.lead.interfaces import IDatabase
    import solexa.model
    import sbs.app
    import sbs.interfaces

    mappings = {
        'libraries': (sbs.interfaces.ILibraries, solexa.model.Library,
'name'),
        'projects': (sbs.interfaces.IProjects, solexa.model.Project,
'id'),
        'protocols': (sbs.interfaces.IProtocols,
solexa.model.Protocol, 'id'),
        'adaptor_protocols': (sbs.interfaces.IProtocols,
solexa.model.AdaptorProtocol, 'id'),
        'flowcells': (sbs.interfaces.IFlowcells,
solexa.model.Flowcell, 'lims_flowcell_code'),
        'runqueue': (sbs.interfaces.IRunQueue, solexa.model.RunQueue,
None),
        'maqqueue': (sbs.interfaces.IMaqQueue, solexa.model.MaqQueue,
None),
    }

    def locate(context, obj):
        """
        Locate an SQL Alchemy object with our URL scheme
        """
        container = sbs.app.SQLContainer()

        # walk up the __parent__'s of the context until we get
        # to the ISequencingTechnology object (e.g. Illumina or SOLID)
        seqtech = context
        while not sbs.interfaces.ISequencingTechnology.providedBy
(seqtech):
            seqtech = seqtech.__parent__

        for key, value in mappings.items():
            klass = value[1]
            if isinstance(obj, klass):
                name = key
                primary_key_name = value[2]
                zope.interface.alsoProvides(container, value[0])

        container.__parent__ = seqtech
        container.__name__ = name

        obj.__name__ = str(getattr(obj, primary_key_name))
        obj.__parent__ = container


    class SQLContainerTraverser(grok.Traverser):
        """
        Each SBS technology (e.g. Illumina, SOLID) has a simple URL
space that is
        the name of the table, then the name of the id for a record in
that table.

        For example:

        /projects/10 - Project #10
        /libraries/MM0246 - Library MM0246
        /protocols/SLX-SAGE - Procotol SLX-SAGE

        This traverser will support traversal to just the table
objects.
        A second traverser will support navigation into individual SQL
Alchemy
        objects.
        """
        grok.context(sbs.interfaces.ISequencingTechnology)

        mappings = mappings

        def traverse(self, name):
            container = sbs.app.SQLContainer()
            # assign __parent__ and __name__ attributes
            # URL stands for Uniform Resource Location
            zope.location.location.locate(container, self.context,
name)
            if self.mappings.has_key(name):
                # declare that the generic container has a specific
interface
                zope.interface.alsoProvides(container, self.mappings
[name][0])
                container.model_class = self.mappings[name][1]
                container.primary_key_fieldname = self.mappings[name]
[2]
                return container
            else:
                return None

    class SQLModelTraverser(grok.Traverser):
        """
        Based on the Container that represents a SQL Table, fetch a
model object
        from that table, and give that model a location. Also mark the
model
        object as providing the ISQLModel interface so that Views for
that model
        can have the default view name of 'index'.
        """
        grok.context(sbs.app.SQLContainer)

        def traverse(self, name):
            session = zope.component.getUtility(IDatabase,
'sbs').session
            model = session.query(
                self.context.model_class).filter(
                getattr(self.context.model_class,
self.context.primary_key_fieldname) == name
            ).first()
            if model:
                zope.interface.alsoProvides(model,
sbs.interfaces.ISQLModel)
                zope.location.location.locate(model, self.context,
name)
            return model