Empty Thoughts

It has been a couple of weeks since I announced the Django-SQLAlchemy project. I thought it would be a good idea to provide somewhat regular updates for those that are interested, but not following the project at a code level.

Summary

At the overall level we’ve seen tremendous progress on the project. In the last couple of week there were 77 commits affecting about 4500 lines of code. We implemented the testing framework into the project and as a result we have added about 45 tests. We are just getting started in the testing area and have a ways to go before we have adequately tested the existing code. But it is moving along nicely.

Additionally, we have a new regular committer with Samuel Hsiung coming on board and submitting patches to implement the inspectdb management command as well as all of the introspection stuff related to that. We really appreciate his help.

Details

As I mentioned in the previous post, we see the integration of Django and SQLAlchemy breaking down into three primary areas of development. I’ll outline how we’re progressing in each of these areas below:

1. Management Commands

As I mentioned above, Sam Hsiung implemented the inspectdb command. In addition to that we have implemented flush, reset, sql, sqlreset, and syncdb. Not all of these are what we would consider 100% complete, but they’re there and working for our current use case.

We are kind of stalled on the loaddata and dumpdata management commands until we implement extra. But there are lots of others that can be implemented. This is a great area for someone new to the project to get involved.

2. Declarative Mapper

This one is more difficult to gauge. Currently we have the declarative stuff working for our current test cases. Which includes straight models, Foreign Keys, and Many To Many. We’ve have not got into model inheritance nor have we really tested out the M2M relationships very extensively at this point.

All field mappings are done, but we continue to tweak them as we run into issues. There are currently very few tests related to the declarative area of things. This is an area that I plan to focus on over the next week.

Related fields are mapped although currently we’re letting Django handle the RelatedManager for us. Although this works it does so at the expense of some functionality that we’d like to provide down the road. We’ve modified the declarative mapper to support overriding this behavior but I’d like to provide a default implementation that takes advantage of SQLAlchemy’s attribute instrumentation to allow SQLAlchemy to manage this relationship but in a way that mirrors how Django manages it. Similar to how we’re managing QuerySet.

3. QuerySet Mapping

This area has been a lot of fun and also the area where we’ve provided the most tests so far. As I stated previously we are providing our own DjangoSQLAlchemyQuerySet in place of the default QuerySet. In doing so it’s necessary to map all of the methods that are available to Django. We’ve broken this into three smaller subsection of code:

a. QuerySet Methods – We have mapped a lot of the methods on the QuerySet. So instead of listing them all, I’ll list what is left. The QuerySet methods that still need some love are: dates, complex_filter, select_related, and extra (ugh).

Additionally there are a couple that need some work or further testing. order_by is implemented but we have not tested it for all forms of it. Remember order_by used to use one format and now uses a format similar to filters. We have a potential problem with delete regarding invalidating session information. I have not yet tested it so I can not say for sure if it is an issue.

We have added in an options method for passing extended options to SQLAlchemy. We have also added in a first method, just because it is convenient. Items like this are just in there for now. The end result of where Django stops and SQLAlchemy begins is to be determined.

b. Field Lookups – We have created a little mapper that handles the field lookups, and does the right thing in creating the join conditions needed. Currently we provide support for everything but range, year, month, day, search, regex, and iregex.

In addition to the above we have added two new constructs, just because we can, like and ilike. These two provide raw like ability for doing more complex LIKE matching in your field lookups.

Join conditions are working and tested for Foreign Key situations. They’ve yet to be tested for Many to Many relations.

c. Subclasses – This includes subclasses of QuerySet that are implemented within Django. Technically these probably do not need to be implemented as such, but the design makes sense on its own and it makes sense for what we are attempting to do.

So far we have implemented a DjangoSQLAlchemyValuesQuerySet and a DjangoSQLAlchemyValuesListQuerySet. Both of these function for the common cases although we have not tested them with Many to One values at this point.

We still need to implement the DateQuerySet and the EmptyQuerySet. So there’s some low hanging fruit there.

Wrap Up

Up to now a lot of the mapping that we’ve been doing has been just a lot of grunt work. Turning the crank and making sure things work as expected. We needed some early wins to know that we were heading in the right direction and we have that now. The next step is really hitting more of the edge cases, the more difficult areas of implementation. Before we get into that though I want to be sure we have good overall test coverage for what is implemented up to this point. That will be my focus over the next week or two.

It’s amazing to me how similar many of the approaches are between Django’s ORM and SQLAlchemy’s ORM, for the subset of functionality provided by Django. That makes the job a lot easier. Although there are also interesting differences.

For instance, Django’s order_by is not generative (they can not be chained), whereas with SQLAlchemy they are generative. Django’s get method respects current filters, whereas SQLAlchemy’s get method ignores any pre-existing filtering. (Note this is what I’m experiencing so if I got it wrong I’m sure I’ll be set straight.)

I must admit that the more I learn about SQLAlchemy the more I’m impressed with the flexibility and power of the framework. It has some amazing code, and the potential for Django that it provides through this project is exciting.

Once again I would like to thank Mike Bayer and Jason Kirtland for providing a ton of assistance. I know that I’ve been annoying at times, but they’ve been great in nudging us in the right direction when we lose our way.

One of the things that has kept me very busy over the past several months is something called Django-SQLAlchemy. I started toying with the idea back in the fall and it just kind of rumbled around in my head for a while as I thought through different approaches to integrate SQLAlchemy into my Django projects. Yes, I’m aware you can just import sqlalchemy and you’re done. But no one is every really talking about that, are they? What people want is the ability to keep everything the same but still have the power and flexibility of SQLAlchemy available to them. That’s what this project aims to do.

Finally, some time in or around December 2007, Brian Rosner and I began discussing the project. I was glad to hear that he had been thinking about similar things, and we decided that we would put forth some effort on the project. Brian and I have been working for the past several months trying to do a proof of concept and develop a roadmap for the project. A lot of hackish code was developed, and we frequently went down one road only to discover there was a much easier way. In the end we figured out what we think is a plausible plan for implementing SQLAlchemy into Django.

At PyCon 2008 in Chicago Brian and I decided to set up a Birds of a Feather session in one of the Open Spaces to discuss django-sqlalchemy. Much to our surprise we had a great turnout. First of all Mike Bayer, creator of SQLAlchemy, and Jason Kirtland, core contributor to SQLAlchemy, both showed up and were extremely helpful. They indicated that they are really interested in seeing the project become a success, and they would help in any way possible. During the meeting they even discussed modifying some functionality within SQLAlchemy in order to make our job a bit easier. Adam Gomaa also showed up and expressed an interest in helping on the project. We added Adam as a contributor immediately and we all began hacking away.

Over the next several days, Adam, Brian, and I made really good progress. We solidified some of the hackish filter translation code, we created a test runner, We made a lot of progress on mapping the related fields, and most importantly we completely replace the declarative layer (previously Elixir based), with code based on the declarative layer that Mike created in SQLAlchemy. We also bugged Mike and Jason continuously, and every time they provided us with valuable guidance that saved us hours of grief. We can not thank them enough for their help.

Development Roadmap

The fundamental approach to the django-sqlalchemy project is to implement SQLAlchemy as a custom database backend. Actual SQLAlchemy backends are made available through custom project settings. By doing so we can “hijack” all calls to the QuerySet with our own custom QuerySet that interprets these calls and passes them on to SQLAlchemy. We also need to be able to provide SQLAlchemy hooks into our Django models and this is handled by the declarative layer of the project. Finally, things like management commands that have affect on the database backend need to be overridden to do the right thing in SQLAlchemy terms.

1. Management Commands – Management commands that affect the database backend in any way need to be overridden to pass on calls to the SQLAlchemy layer. This affects at least the following: syncdb, sql*, reset, dbshell, createcachetable, dbshell, dumpdata, flush, inspectdb, loaddata.

2. Declarative Mapper – mapping the Django model structure into SQLAlchemy Table and Mapper information so that access to SQLAlchemy is available while still providing access to all of Django’s expectations for functionality of a model. This includes mapping all of the fields, related fields, polymorphic relations, etc. Plus once we get it functionally compliant, there will be a lot of hooks for extensions that will provide greater access to SQLAlchemy power.

3. QuerySet Mapping – Map the backend django-sqlalchemy database so that we can override Django’s QuerySet into a SqlAlchemyQuerySet that interprets and passes on all backend query functionality to SQLAlchemy. This includes the handing of sessions, metadata, and transactions.

Benefits

So why are we doing all of this? Well mostly we are scratching our own itch. We also sense that there is a lot of interest in the community for this type of thing. Additionally we realize that there are a lot of benefits provided by a django-sqlalchemy integration, that will help make Django even more powerful:

• Addition database support: MS-SQL, Firebird, MaxDB, MS Access, Sybase and Informix. IBM has also released a DB2 driver

• Connection Pooling

• Multi-Database Support, including things like sharding

• Extremely powerful query language

Initially all of these things may not be available as we first aim to achieve functional equivalence to Django’s ORM, but over time we will provide hooks and ways to tie into the additional functionality provided by SQLAlchemy.

Interest

So if you’re still reading along at this point, you probably have some interest in the project. We welcome you to get involved in any way that works best for you. We have set up the following resources for the project:

Gitorious for Source Control

IRC Channel #django-sqlalchemy

I will also try to be good about posting progress updates here.