Elixir needs help resolving foreign keys

June 12, 2007

Briefly, our model for the new tickets is like this:

class Project(Entity):
    has_field('id', Integer, primary_key=True)
    has_many('tickets', of_kind='Ticket')

class Ticket(Entity):
    has_field('id', Integer, primary_key=True)
    belongs_to('project', of_kind='Project', 
                       column_kwargs={'primary_key' : True})
    has_many('fields', of_kind='FieldType')
class FieldType(Entity):
    """ One of many different FieldType classes """
    has_field('data', SomeType)
    belongs_to('ticket', of_kind='Ticket')

The primary key for ticket is (id, project_id), with project_id being a foreign key. Thus, the foreign key for FieldType should be (ticket_id, ticket_project_id). The correct SQL for the FieldType table looks like this:

CREATE TABLE fieldtype (
        data TEXT, 
        id INTEGER NOT NULL, 
        ticket_id INTEGER, 
        ticket_project_id INTEGER, 
        PRIMARY KEY (id), 
         CONSTRAINT fielddatastring_ticket_id_ticket_project_id_fk 
           FOREIGN KEY(ticket_id, ticket_project_id) 
             REFERENCES ticket (id, project_id)

Yesterday, however, we found that this foreign key was not being created for all of the different FieldTypes. Sometimes they would get just (ticket_id) as their foreign key, leading to this incorrect create statement:

CREATE TABLE fieldtype (
        data TEXT, 
        id INTEGER NOT NULL, 
        ticket_id INTEGER, # Notice only ticket_id as the foreign key
        PRIMARY KEY (id), 
         CONSTRAINT fielddatastring_ticket_id_fk 
           FOREIGN KEY(ticket_id) REFERENCES ticket (id)

Why was this happening? When you define a relationship with elixir, it has to wait until you define the class on the target side before making the connection. So our first relationship has_many('tickets', of_kind='Ticket') sticks around in a set somewhere until belongs_to('project', of_kind='Project', column_kwargs={'primary_key' : True}) is defined, and then the connections are made and foreign keys are defined.

However, our classes aren’t defined in the order above; it goes Project, FieldType, Ticket. Before Ticket is defined, there are two relationships waiting: one from Project, and one from FieldType. If the Project connection is made first, the foreign key is created and Ticket’s primary key becomes (id, project_id). If FieldType is connected first, then Ticket’s primary key is just (id), so FieldType will have an improper foreign key.

The solution? Before elixir tries to resolve connections, sort the list of pending relationships so that any future primary keys are resolved first. This ends up being a 10-line hack in elixir/entity.py, defining a compare method and then making the call to that method. The primary keys will be set before any other relationships try to link to that table, so all the foreign keys will point to the correct columns.

Here’s the sort function:

def relationship_cmp(a,b):
    """ Resolve primary keys of a belongs_to first """
    for rel, val in [(a,-1), (b,1)]:
        if hasattr(rel, 'column_kwargs') and \
            return val
        return 0


Ignore all those subversion copies

June 11, 2007

I’ve been grepping the DrProject source frequently, and I’m getting double the results, one from the real file and one from the .svn copy, which is annoying and clutters up my results. Here’s a zsh function to automatically ignore the subversion directories:

rgrep $@ | grep -v svn

Approximatng Polymorphic Inheritance with Elixir

June 7, 2007

In DrProject’s new ticketing system, the default ticket will be nothing more than a todo list, which the user can then extend however she wishes; the tickets will grow with the project.

When a user wants to add a new field to her project’s tickets, she has to give us some information: the title of the new field, any default values, and the type of data the new field is going to store. We take this information and turn it into a Field object, which gets flushed to the database and always shows up whenever anyone looks at that project’s tickets.

class Field(Entity):
    has_field('title', Unicode())
    has_field('type', Integer)
    has_field('default', Unicode())    
    belongs_to('project', of_kind='Project')

What a Field object doesn’t store is any actual ticket data; that task is left up to a class that holds the specific data type (remember, we’re dealing with a database–not just Python–so types matter). We want to provide a decent selection of canonical types to choose from, so we’re at least going to have a string type, a date/time type, an enumeration, and float and integer types. Creating each of these classes means a lot of repetition; each class has one special data field, and then it has to reference the Ticket and the Field it belongs to.

class FieldDataString(Entity):
    has_field('data', Unicode())
    belongs_to('ticket', of_kind='Ticket')
    belongs_to('field', of_kind='Field')

class FieldDataDatetime(Entity):
    has_field('data', DateTime)
    belongs_to('ticket', of_kind='Ticket')
    belongs_to('field', of_kind='Field')

…and so on.

The normal solution: create a base class for all the common features and then have each subclass handle its special data type. However, we’re dealing with an ORM so inheritance doesn’t work the way it’s supposed to; I tried it, and all my derived classes got smooshed into a single table along with the base class (elixir list posting).

Up to this point, we’ve been busy figuring out the problems with our model, so I just accepted the redundancy and worked with it. Now that we have a handle on where we are going, Greg wants us to extend the model. This means that changes need to be made to each specific type, which is not only tedious, but a great way to introduce bugs. I decided to have another go at inheritance, so we can specify all the commonality in one place.

Examining the elixir source (which is lovely–great job guys), I found that the derived types were getting smooshed in with the base class because of the way elixir sets up relationships. When a belongs_to relationship is declared, elixir stuffs it into the correct class’s __dict__ by pulling out a frame object from the call stack. The relationship has no way of knowing what class it’s supposed to go into; it’s blindly pushed into the first one it finds.

Inspired by all the metaclass magic going on in elixir, I decided to try out some of my own. Normally, a relationship is attached during the class definition time, and then all the relationships are processed in the EntityMeta metaclass. Instead of using the basic metaclass, I declared my own derived metaclass, which attaches the proper relationship objects to each class and then sends it off to elixir for processing.

class FieldDataMeta(EntityMeta):
    """ Sets up common relationships for FieldData*Type* classes """
    def __init__(cls, name, bases, dict_):
        if bases[0] is object:
            return # don't process FieldData base class

        # Pull the existing statements, or create an empty list
        if not hasattr(cls, STATEMENTS):
            setattr(cls, STATEMENTS, [])
            statements = getattr(cls, STATEMENTS)

        # Add the FieldData* relationships
        ticket_rel = (belongs_to, ('ticket',), dict(of_kind='Ticket'))
        field_rel = (belongs_to, ('field',), dict(of_kind='Field', lazy=False))
        statements.extend([ticket_rel, field_rel])

        EntityMeta.__init__(cls, name, bases, dict_)

It’s not inheritance in the way that I expected, but it gets rid of the redundancy and simplifies the code a bit, so I’m happy.

The full implementation is available here.

Oh, Canada

May 23, 2007

Greg Wilson and the folks at the University of Toronto were nice enough to give me a plane ticket to come up to Canada for a week, to get started working on DrProject.

Toronto was an absolutely beautiful city; very different from Orlando.

  • You walk everywhere you want to go, and the weather is perfect for being outside.
  • Public transportation is available and reliable.
  • People don’t curse you for riding a bike, or try to run you over (but watch out in Montreal).
  • Lots of old, ornate Victorian buildings, and new, progressive buildings that are a pleasure to behold.
  • So many small shops, the choices for eating and shopping are astounding, with no Big Box stores to be found.
  • The people are wonderful. It’s a very diverse population, but everyone I met was amicable and friendly.

My partner for the Summer of Code, David Cooper, cut his Florida vacation short so that we could get started on our work together. He’s knows a lot more than me about Linux and web development, so I’m going to have to play catch up once we get into the web programming side of our project. David was nice enough to let me stay in his apartment along with his lovely girlfriend Kate, who wasn’t too happy that David invited me to stay without knowing anything about me (I could have been a serial killer).

I learned a lot in the short time I was there: how to run a meeting, use revision control (svn), work with elixir and SQLAlchemy, give a technical presentation, and most importantly, work on a team. I think that the biggest hurdle this summer is going to be the communication between David and I; by the end, we should both be fantastic at expressing our technical ideas (we’ve already had quite a few disconnects, but we’re getting better at being on the same page now).

I was really nervous about leaving the country for the first time, but I’m glad I did; it was a fantastic experience. Getting to meet everyone in person and work on the design with David face-to-face helped me to understand my colleagues much better, and I think it’s important to know the people you’re working with if you want to achieve effective collaboration. I hope that I’ll be able to fly up there again soon (nudge, Greg :)).