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;
Next, to be able to play with this in Storm, we need to define some classes and set up some references:
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)
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
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
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
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
And that should just about do it :-)
