Section 12.3. SQLObject and Inheritance

12.3. SQLObject and Inheritance

Object-oriented programming languages provide many ways to represent relationships between objects: composition, containment, and inheritance. Relational databases can represent composition by reference as a row that has a foreign key column that points into another table. The DB can also represent containment with a reference table. However, there's no good way to represent inheritance through relational algebra.

Inheritance is often the best tool available to avoid code repetition, and SQLObject provides convenient methods to help you map the inheritance method to elements in the RDBMS. With that said, note that most problems can be resolved in multiple ways, and in some cases you might want to consider redefining your objects in terms of composition.

12.3.1. Python Objects, Inheritance, and Composition

Python is a fully object-oriented language. Every new-style class is derived from object, and it even supports multiple inheritance. Python also supports composition. Its flavor of composition is aggregation. This means that objects can hold references (or pointers) to other objects, but they don't own them. Ownership implies that the lifetime of the owned object is tied to its owner. Because Python is garbage collected and its garbage collection is based on reference counting, there's no object ownership in Python. If object A creates an object B and passes a reference to another object B, then B will stay alive as long as either A and C (or any other object) keeps reference to it. The fact that it was created by object A has no bearing at all.

12.3.2. Composition vs. Inheritance in Standard Python

Suppose you need to model a vehicle domain in your code. There are different kinds of vehicles, such as cars, airplanes, and submarines. There are many common attributes to all these vehicles (such as model, year, color, owner, and so on). But some attributes are relevant only for specific types of vehicles (such as number of doors for cars, wing span for airplanes, and max diving depth for submarines). You can represent this domain with the following composition-based design:

class Vehicle(object):     def __init__(self):         self.make = ''         self.model = ''         self.year = ''         self.color = ''         self.owner = '' class Car(object):     def __init__(self):         self.vehicle = Vehicle()         self.doors = 0 class Airplane(object):     def __init__(self):         self.vehicle = Vehicle()         self.wingSpan = 0 class Submarine(object):     def __init__(self):         self.vehicle = Vehicle()         self.maxDivingDepth = 0 c = Car() c.vehicle.make = 'Ford' c.vehicle.model = 'Mustang' c.vehicle.year = '1978' c.vehicle.color = 'Red' c.vehicle.owner = 'SpongeBob SquarePants' c.doors = 3

The Car, Airplane, and Submarine classes have a common vehicle attribute that contains the model, year, color, and owner. Working with the common attributes is a little annoying because you have to go through one more level of indirection.

You can also represent the same domain with inheritance-based design. The Car, Airplane, and Submarine classes will inherit from the Vehicle base class instead of holding a reference to it:

class Car(Vehicle):     def __init__(self):         self.doors = 0 class Airplane(Vehicle):     def __init__(self):         self.wingSpan = 0 class Submarine(Vehicle):     def __init__(self):         self.maxDivingDepth = 0 c = Car() c.make = 'Ford' c.model = 'Mustang' c.year = '1978' c.color = 'Red' c.owner = 'SpongeBob SquarePants' c.doors = 3

As you can see, the code for inheritance-based design is more concise. However, the composition-based design allows more flexibility, such as dynamically attaching a new subtype of vehicle with extra attributes or methods to an existing car, airplane, or submarine. You can also share the same vehicle attribute between multiple instances.

Of course, you could use metaclasses and other dynamic tricks to achieve the same thing, but composition is often a lot cleaner and more obvious.

12.3.3. SQLObject Composition vs. Inheritance

SQLObject obviously supports composition out of the box. You can have a Vehicle, Car, Airplane, and Submarine table. Every row in the Car, Airplane, and Submarine table will contain a foreign key column that will point to the Vehicle table:

class Vehicle(SQLObject):     model = StringCol()     make = StringCol()     year = StringCol()     color = StringCol()     owner = StringCol() class Car(SQLObject):     vehicle = ForeignKey('Vehicle')     doors = IntCol() v = Vehicle(make='Ford', model='Mustang', year='1978', color='Red', owner='SpongeBob SquarePants') c = Car(vehicle=v.id, doors=5) print c print c.vehicle.make, c.vehicle.model, c.vehicle.year, c.vehicle.color, c.vehicle. owner, c.doors

Output:

Ford Mustang 1978 Red SpongeBob SquarePants 5

The problem with this approach is that you have to access the Vehicle attributes via another indirection level, and you have to instantiate two objects to get a single conceptual object.

Because the ForeignKey column class makes the Vehicle attributes available through the c instance, without requiring you to issue a special query, these limitations aren't serious. You can create nice interfaces using composition. But, you can do better (in this situation) with real inheritance. SQLObject supports it via a collection of classes called InheritableSQLObject, InheritableSQLMeta, and InheritableSelectResults that for the most part work like their noninheritable counterparts but let you specify inheritance relationships between SQLObject model classes:

from sqlobject.inheritance import InheritableSQLObject class Vehicle(InheritableSQLObject):     model = StringCol()     make = StringCol()     year = StringCol()     color = StringCol()     owner = StringCol() class Car(Vehicle):     doors = IntCol() c = Car(make='Ford', model='Mustang', year='1978', color='Red', owner='SpongeBob SquarePants', doors=5) print c

Output:

<Car 1 doors=5 model='Mustang' make='Ford' year='1978' color='Red' owner="'SpongeBob SquarePants'">

The syntax is so much sweeter now. You can really convey your intention in an object-oriented manner. You create a single object passing in the attributes of the "base class," too. It feels just like standard object-oriented Python. To make it all happen, you have to derive your base class (Vehicle in this case) from InheritableSQLObject and your derived classes from the base class. The next question is to look inside the DB and see what SQLObject concocted there. Here is the DB schema:

CREATE TABLE car (     id INTEGER PRIMARY KEY,     child_name TEXT,     doors INT ); CREATE TABLE vehicle (     id INTEGER PRIMARY KEY,     child_name TEXT,     model TEXT,     make TEXT,     year TEXT,     color TEXT,     owner TEXT );

SQLObject transparently adds a child_name column to both tables. This column contains the name of the child table (if there is one). SQLObject uses this column to locate the linked table in order to aggregate the contents of corresponding rows in car and vehicle tables to a coherent Car object:

connection.debug = True connection.debugOutput = True print Car.select()[0]

Output:

SELECT car.id, car.child_name, car.doors FROM car WHERE (car.id = 1) LIMIT 1 <Car 1 doors=5 model='Mustang' make='Ford' year='1978' color='Red' owner="'SpongeBob SquarePants'">

SQLObject allows arbitrarily deep single-inheritance hierarchy. But for now at least, multiple inheritance isn't supported at all.

Here is an inheritance hierarchy with two levels. Asset is the base class, Vehicle inherits from Asset, and Car inherits from Vehicle. The Car objects aggregate the attributes of all their base classes:

class Asset(InheritableSQLObject):     price = StringCol() class Vehicle(Asset):     model = StringCol()     make = StringCol()     year = StringCol()     color = StringCol()     owner = StringCol() class Car(Asset):     doors = IntCol() Car(make='Ford', model='Mustang', year='1978', color='Red', owner='SpongeBob SquarePants', doors=5, price='$500')

Note

SQLObject's debug facilities don't handle inheritable objects particularly well right now. You will get only the SQL statements on the derived class and not on the base class.