Elixir needs help resolving foreign keys

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


2 Responses to “Elixir needs help resolving foreign keys”

  1. The Third Bit » Blog Archive » Elixir and Foreign Keys Says:

    […] Balogh and David Cooper have run into an interesting “feature” of Elixir, and are putting together a patch to correct it.  Watching students discover that the […]

  2. David Cooper Says:

    Google the phrase: elixir belongs_to

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: