Use SQLAlchemy ORM

[ockham]

Gourmet has seen a rather long history of database backends, from Metakit over SQLite to SQLAlchemy. As a consequence, we're still using a layer of abstraction (in gourmet/db.py) around SQLAlchemy's "core" SQL functions, that should get rid of in favor of SQLAlchemy's native ORM, which would allow for more intuitive database abstraction, particularly for developers new to Gourmet that already know SQLAlchemy. See also issue #685 (which I've started to work on -- contact @ockham if you're interested), and #686 and #682 for bonus points.

This is one of the ideas we're suggesting as a project for GSoC 2013. For general information about Gourmet's participation in GSoC 2013, see the wiki page.

[ockham]

We might investigate using SQLkit to connect SQLAlchemy models to (py)GTK.

[ockham]

Implementing basic ORM classes like Recipe, Ingredient etc. isn't that hard -- I've already started a local branch for this. What's harder is migrating all those direct SQL functions that we're currently using to query (and modify) recipes, their ingredients, categories etc. to those ORM classes.

• We should probably start with migrating something like the recipe card display for which there is at least a slight chance that we can eventually just pass it a new Recipe object as argument to their constructor (not the entire DB).
• To refactor the recipe index, we will probably need to write a TreeModel interface for our Recipe class which might (partly) replace PageableStore. We'll base this on PyGtk's GenericTreeModel.

[ockham]

I've just pushed a work-in-progress (ie unfinished, dirty) branch, sql-alchemy-orm. This is probably the biggest refactoring project so far, and I could use some help with finishing it.

So as stated in the original post, the main goal is to replace our custom DB handling functions is gourmet/db.py with SQLAlchemy's ORM. That means we use objects made from classes like Recipe or Ingredient (all found in gourmet/models; BTW, the recipe card and index related stuff is now within the gourmet/views folder) which we obtain from and save to the database through the use of a Session object, which we pass along to the different UI components that need to access the DB. Porting to the ORM thus means (mostly):

• DOING A BACKUP OF YOUR RECIPE DATA (i.e. your ~/.gourmet folder)!!!
• BTW, running this branch without any ~/.gourmet folder and recipe.db within won't currently work, so you're going to need some dummy data.
• Reading the (excellent) SQLAlchemy ORM tutorial, or the following will be all Greek to you ;-)
• Replacing dictionaries that have been used to pass along recipes by actual Recipe objects.
• Replacing functions like add_recipe by constructing a new Recipe object, and (later) committing it to the Session.
• Replacing functions like search_recipes() by self.session.query(Recipe).all(), etc.
• We need to pass a Session object to classes (like UI components) that need access to the DB (instead of a database object, most often called something like rd in the class constructor) in order to modify the DB or gather additional data from it. Note that not everything really needs to access the DB, sometimes classes and functions (like exporters) only need to be passed a Recipe object, which! Even simple RDBMS-style relations (like recipes "having" ingredients) can be mapped by the SQLAlchemy ORM, without requiring any additional explicit DB queries. I've done this to some classes already (but haven't even removed the rd arguments yet). This should also make for cleaner encapsulation/separation of data.
• As announced in the previous comment, I've moved some of the recipe index display logic to RecIndex. To that end, there's now an SqlaModel class in gtk_extras/pageable_store.py (derived from gtk.GenericTreeModel) which provides the glue between the SQLAlchemy-ORM Recipe class, and the gtk.TreeView. Also, I'm using TreeModelFilters for filtering out recipes that have been moved to the trash, i.e. with their deleted property set to True, and for pagination. I haven't yet implemented filtering according to user specified criteria (i.e. the UI filter box), nor sorting (for which there's gtk.TreeModelSort. The SqlaModel/TreeModelFilter based approach has the advantage that changes to a recipe immediately show up in the index, with no additional programming logic required. OTOH, propagating changes like setting a recipe's deleted attribute is quite slow, and adding a new recipe will currrently not cause it to show up in the index at all -- which we obviously need to fix.
• If something's broken that I've only fixed recently with one of the latest versions (e.g. the InfoBar used when importing and exporting recipes), chances are that it's because of the git rebase having gone wrong at some point. (Some of the recipe card display logic got spilled into gourmet/tests/test_reccard.py.)