[Grok-dev] thoughts while writing a tutorial

[Brandon Craig Rhodes]

Wichert Akkerman <wichert at wiggy.net> writes:

> Previously Brandon Craig Rhodes wrote:

>> Two weeks ago I promised a tutorial on using SQLAlchemy from
>> Grok...
>
> I am actually working on the very same thing as well at the
> moment. Can you give me a pointer to your example code?

I have nothing on the web yet.  Since it looks like a week or two of
learning before I have an actual working tutorial, let me quickly
outline my approach for you, Wichert.

1. Define Your Schema
 
   I put my schema in a "schema.py" module, to keep things clean; but
   as long as you put some information into ZAlchemy's "metadata",
   you've done the job you need to.  If you define your tables
   statically in your code, things are easy:

      from sqlalchemy import (
          Table, Column,
          Integer, String, DateTime,
          ForeignKey,
          )
      
      import z3c.zalchemy
      metadata = z3c.zalchemy.metadata()
      
      services = Table(
          'services', metadata,
          Column('tag', String(), unique=True, nullable=False),
          Column('name', String(), unique=True, nullable=False),
          Column('fullname', String(), unique=True, nullable=False),
          Column('status', String(), nullable=False, default='Running'),
          )

   ... and so forth, through as many tables as you like.  Though I at
   first wanted SQLAlchemy to use its powers of introspection to
   determine what tables our database system has - because describing
   an existing database in a new programming language like this
   duplicates information, and requires changes to be made two places
   the next time they change a table - I realized that being explicit
   about the database structure has two important advantages:

    * First, it means that testing becomes easy, because your test
      suite can import the "schema" module and then ask each of the
      described tables to create itself in a test database.  This
      means you can do unit tests in the absence of an external
      database!  By contrast, if we relied upon introspection to fill
      in the schema, you could only test by mocking up the tables
      separately.

    * Second, since your code is now explicit about how the database
      should work, it will fail cleanly (by refusing, with a clear
      message, to run at all!) on the day that one of the tables
      changes without your knowing it was going to; or when you try
      running it against a wrong or old version of your application
      database.

   If you, despite these reasons, still want to introspect the schema
   from the database, then you will need to briefly create an open
   engine to the database at schema-instantiation time, and your code
   will look something like (I'm doing this from memory, so the
   keyword args might be wrong):

      engine = sa.create_engine('postgres://localhost/mydatabase')
      services = Table(
          'services', metadata, autoload=True, with=engine,
          )
      del engine

   There is probably a more clever way to create the engine by
   instantiating the utility we will create (see below); but the above
   does work.

2. Define Your ORM

   If your application simply wants to query tables like those defined
   above, then skip this section.  But if you want to use the
   SQLAlchemy ORM, that lets you "hook up" Python objects to tables so
   that each object instance represents a particular table row, then I
   would create a module called "orm.py" and do something like:

      import sqlalchemy
      import your_app.schema as schema

      # Create a class to "hook up" to SQLAlchemy.

      class Service(object):
          pass

      # And, hook it up.

      sqlalchemy.mapper(Service, schema.services)

   Of course you might want to create fancier objects than this tiny
   "Service" class; you might even have big objects defined in other
   files that you "import" here to use "mapper" on.  This would lead
   to the interesting property that you could test the behavior of
   your ORM object methods in the absence of any database - because if
   a unit test or doctest chose not to import "orm.py", then the
   objects would just stand "on their own" and you could create them
   and call them and it would simply manipulate its instance variables
   as local Python values.  But an integration tester could actually
   import "orm", and then references to the object's attributes would
   read and write to the database.

3. Connecting To Your Database

   Finally, you need to provide a utility that knows how to connect to
   your database.  Create a module like "database.py" or something
   that says (and the ZAlchemy module documentation has more examples
   of how this can work):

      import sqlalchemy as sa

      from z3c.zalchemy.interfaces import IAlchemyEngineUtility
      from z3c.zalchemy.datamanager import AlchemyEngineUtility
      from zope.component import provideUtility

      engineUtility = AlchemyEngineUtility(
          'my_database',
          'postgres://localhost/my_database',
          echo=False,
          )

      provideUtility(engineUtility, IAlchemyEngineUtility)

   With this present in your application - and also, when any test
   suite chooses to import or grok this module - then code that uses
   the database tables in "schema.py" or the objects in "orm.py" will
   know how to talk to your database.

4. Finally, How To Get A Session

   All you now need to have working code is a database session!

   Every time you need to access or manipulate objects or tables, you
   will need to create a session; the SQLAlchemy ORM manual is full of
   examples of using sessions to get objects from the database and
   save them back.  To do this in your Grok app, do:

      session = z3c.zalchemy.datamanager.getSession()

   You might even want a little "db.py" module that provides this call
   for you more conveniently.

   According to the ZAlchemy documentation, the changes made to the
   database with the session you grab will only be committed when the
   current Zope 3 transaction commits, and will be aborted if the
   transaction is cancelled.  I myself have not tested this yet, and I
   am sure what events, exactly, would cause a commit or abort; can
   this happen automatically, for example, if the user cancels the
   browser request while it's running?  Zope transactions are one of
   the things I will have to read up on as I expand the above into a
   full tutorial, so that I can explain them better.

5. Testing

   As noted above, there are several tricks to make testing easier -
   like having a separate "orm.py" module that connects all of your
   objects to the database, so that the objects themselves can be
   tested without SQLAlchemy interfering at all by sometimes not
   importing "orm".  But some of your unit tests will need a database,
   and I found it was easiest to provide this through two steps.
   Briefly:

    - Provide a convenience module that creates an in-RAM database and
      creates all your tables there.

    - Call this module from your unit tests or doctests.

   The question (as you might have seen on this mailing list recently)
   is where to put the module!  Giving it a "normal" name would mean
   it would be grokked when you tried to run your whole application,
   so we need its name to start with "test"; but this means that the
   "./bin/test" script will try to run it as a test suite and complain
   if it lacks a test_suite function!  So I compromised and created a
   "test_testdb.py" module (but asked whether Grok could learn to
   ignore "test" directories completely):

      from zope.component import provideUtility
      from z3c.zalchemy.interfaces import IAlchemyEngineUtility
      from z3c.zalchemy.datamanager import AlchemyEngineUtility

      # Create a database that lives only in RAM.

      engineUtility = AlchemyEngineUtility(
          'database',
          'sqlite:///:memory:',
          echo=False,
          )

      provideUtility(engineUtility, IAlchemyEngineUtility)

      # Second, create all of the tables defined in our schema, so
      # that they are available for testing.

      from myproject.schema import *
      engine = engineUtility.getEngine()
      for table in [ services ]:
          table.create(bind = engine)

      # Finally, since the Zope "testrunner" will discover us cowering
      # here and think from our name that we are a test, we provide an
      # empty test suite to prevent its throwing an error.

      import unittest
      def test_suite():
          return unittest.TestSuite()

   And now, with this module to make things convenient, something like
   a doctest can look like:

      >>> import grok

      >>> grok.grok('myproject.tests.test_testdb')
      >>> grok.grok('myproject.schema')
      >>> grok.grok('myproject.orm')

      >>> from myproject.orm import Service

      Here we go!

      >>> session = z3c.zalchemy.datamanager.getSession()
      >>> service1 = Service()
      >>> service1.tag = 'email'
      >>> service1.name = 'Email'
      >>> service1.fullname = 'Central Campus Email'
      >>> service1.status = 'running'
      >>> session.flush()

      >>> print service1.tag
      'email'

   The important thing when testing this way is to never, ever, say
   "import transaction; transaction.commit()", because - for reasons
   that are not clear to me - the ZAlchemy code closes the database
   connection once "commit()" has been called, which destroys the
   in-memory database we created by passing ":memory:" to sqlite!

   A normal database, of course, exists even when you're not connected
   to it, so calling "transaction.commit()" is fine, because your data
   will still be there when your new session re-connections.  But when
   using a ":memory:" database for fast and footprint-less testing
   (you could, of course, use a tmpfile to avoid this), limit yourself
   to "session.flush()" to make data appear.

There!

That's about everything I've learned so far.  And having typed all
this out will make writing the tutorial next week easier.  Let me
know, Wichert, whether it answered any questions, or whether you are
trying to do something it does not address at all; maybe there are
additional approaches I should describe in the finished tutorial.

-- 
Brandon Craig Rhodes   brandon at rhodesmill.org   http://rhodesmill.org/brandon