MySQL, Storm, and Relationships

I rarely work seriously with databases, but I've been building an API for a contract with PBS.org, and though we have DBAs tasked for the project, everyone's pretty busy. So I dusted off my decade-old DB (formerly known as) skills, and did the work myself.

I've worked with the Storm ORM a fair amount since it was released, but only on small projects. Any time I've needed to use relationships with Storm, I've been using SQLite and so it was all faked. Due to the impact of the PBS gig (which is almost done now!), I really needed to sit down and map everything out. The first thing I needed to do was get a quick refresher on MySQL's dialect with regard to foreign keys. The next thing I needed to clarify was exactly how to ensure that what I've been doing with Storm relationships in SQLite was valid for MySQL and suitable for production use at PBS. It was :-)

Given how infrequently I use this stuff, I thought that my notes would be good to document, for future quick-reference. Given that there are likely users out there who would also benefit from this, a blog post seemed a nice way to do this :-)

The SQL below is modified from an example in the MySQL documentation, slightly tweaked to be a smidge more interesting. The two CREATE TABLE statements define the schemas for a one-to-many table relationship:

	CREATE TABLE parent (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(50),
	PRIMARY KEY (id)
	) ENGINE=INNODB;
	CREATE TABLE child (
	id INT NOT NULL AUTO_INCREMENT,
	parent_id INT,
	name VARCHAR(50),
	PRIMARY KEY (id),
	INDEX par_ind (parent_id),
	FOREIGN KEY (parent_id) REFERENCES parent(id)
	) ENGINE=INNODB;

view raw 01-one-to-many.sql hosted with ❤ by GitHub

Next, to be able to play with this in Storm, we need to define some classes and set up some references:

	from storm.locals import Int, Storm, Reference, ReferenceSet, Unicode
	class Parent(Storm):
	__storm_table__ = 'parent'
	id = Int(primary=True)
	name = Unicode()
	class Child(Storm):
	__storm_table__ = 'child'
	id = Int(primary=True)
	name = Unicode()
	parent_id = Int()
	parent = Reference(parent_id, Parent.id)
	Parent.children = ReferenceSet(Parent.id, Child.parent_id)

view raw 02-models.py hosted with ❤ by GitHub

The parent attribute on the Child class is a Storm reference to whatever parent object is associated with the child object that gets created; the parent_id attribute is what is actually mapped to the MySQL field parent_id (which, in turn, MySQL references to the parent table). I hope I just didn't make that more of a confusing mess than it needed to be :-)

The children attribute that gets added to the Parent class is a reference to all Child instances that are associated with a particular Parent instance. I've got some usage below, if that's not clear.

Let's create a parent:

	>>> from storm.locals import *
	>>> p1 = Parent()
	>>> p1.name = u'parent 1'
	>>> store.add(p1)
	>>> store.flush()
	>>> print p1.id

view raw 03-interactive.py hosted with ❤ by GitHub

Note that if you add an __init__ method to your Storm classes, you can save a step or two of typing in these usage examples (see the Storm tutorial for more information).

Next, we'll create and associate a child:

	>>> child1 = Child()
	>>> child1.name = u'child 1'
	>>> store.add(child1)
	>>> child1.parent = p1
	>>> store.flush()
	>>> print child1.id

view raw 04-interactive.py hosted with ❤ by GitHub

There's more than one way to do this, though, given the way in which Storm has encoded relationships. Above, we created the child and then set the child's parent attribute. Below, we create the child and then use the chilren's add method to associate it with a parent:

	>>> child2 = Child()
	>>> child2.name = u'child 2'
	>>> store.add(child2)
	>>> p1.children.add(child2)
	>>> store.flush()
	>>> print child2.id

view raw 05-interactive.py hosted with ❤ by GitHub

We're doing all that flushing so that the created objects refresh with their new ids.

Lastly, let's take a look at what's we've just added to the database:

	>>> store.commit()
	>>> p1.children.count()
	2
	>>> for ch in p1.children:
	... print "id: %s, name: %s" % (ch.id, ch.name)
	...
	id: 1, name: child 1
	id: 2, name: child 2

view raw 06-interactive.py hosted with ❤ by GitHub

And that should just about do it :-)