Archive for the ‘elixir’ Category

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/, 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

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.