The first method we encounter is the threadinit() class method (the full code is available as relation.py). It is identical to the one we encountered in the Entity class and should be called once for every thread.

Chapter5/relation.py

@classmethod
def threadinit(cls,db):
		if not hasattr(cls.threadlocal,'connection') or 
cls.threadlocal.connection is None:
				cls.threadlocal.connection=sqlite.connect(db)
				cls.threadlocal.connection.row_factory = sqlite.Row
				cls.threadlocal.connection.execute(
													"pragma 
foreign_keys=1")

The inittable() class method is the method that should be called once when we start an application:

Chapter5/relation.py

@classmethod
def inittable(cls, entity_a, entity_b,
							reltype="N:N", cascade=None):
	sql='''create table if not exists %(table)s (
			%(a)s_id references %(a)s on delete cascade,
			%(b)s_id references %(b)s on delete cascade,
			unique(%(a)s_id,%(b)s_id)
	);
	'''%{'table':cls.__name__,
				'a':entity_a.__name__,'b':entity_b.__name__}
	with cls.threadlocal.connection as conn:
			cursor=conn.cursor()
			cursor.execute(sql)
	cls.columns=[entity_a.__name__,entity_b.__name__]

It takes the two classes involved in the relations as arguments to construct a proper SQL statement to create a bridging table if it does not exist yet (highlighted).

For example, CarOwner.inittable(Car,Owner) will result in a statement like this:

create table if not exists CarOwner (
				Car_id references Car on delete cascade,
				Owner_id references Owner on delete cascade,
				unique(Car_id,Owner_id)

There are a couple of interesting things to note here. There are two columns each referring to a table by way of the references clause. Because we do not explicitly state which column we reference inside the table, the reference is made to the primary key. This is a convenient way to write this down and works because we always define a proper primary key for any table that represents an entity.

Another thing to note is the on delete cascade clause. This helps us to maintain something called referential integrity. It ensures that when the record that is referenced is deleted, the records in the bridging table that refer to it are deleted as well. This way, there will never be entries in a table that represent a relation that points to non-existing entities. To ensure that this referential integrity checking is actually performed, it is necessary to execute a pragma foreign_keys = 1 instruction for each connection to the database. This is taken care of in the threadinit() method.

Finally, there is a unique constraint over both the columns. This effectively ensures that we only maintain, at most, a single entry in this table for each relation between two entities. That is, if I own a car, I can enter this specific relation only once.

If the execution of this statement went well, inittable() finishes with storing the names of the entity classes that this relation refers to in the columns class variable.

How can we make sure that the classes we are passing as arguments to the initdb() method are subclasses of Entity?

The __init__() method constructs an instance of a Relation, that is, we use it to record the relation between two specific entities.

Chapter5/relation.py

def __init__(self,a_id,b_id,stub=False):
		self.a_id=a_id
		self.b_id=b_id
		if stub : return
		cols=self.columns[0]+"_id,"+self.columns[1]+"_id"
		sql='insert or replace into %s (%s) values(?,?)'%(
			self.__class__.__name__,cols)
		with self.threadlocal.connection as conn:
			cursor=conn.cursor()
			cursor.execute(sql,(a_id,b_id))
			if cursor.rowcount!=1:
					raise ValueError()

It takes the IDs of both Entity instances that are involved in this specific relation and a stub parameter.

The __init__() method is not meant to be called directly as it doesn't know nor check whether the IDs passed to it make any sense. It simply stores those IDs if the stub parameter is true or inserts a record in the table if it isn't.

Normally, we would use the add() method to create a new relationship with all necessary type checking. Separating this makes sense as all this checking is expensive and is unnecessary if we know that the IDs we pass are correct. The list() method of the Relation class for example retrieves only pairs of valid IDs so that we can use the __init__() method without the need for costly additional checks.

The SQL statement that is constructed may look like this for a new CarOwner relation:

insert or replace into CarOwner (Car_id,Owner_id) values(?,?)

If we would try to insert a second relation between the same entities, the unique constraint on both columns together would be violated. If so, the insert or replace clause would make sure that the insert statement wouldn't fail, but there still would be just one record with these two IDs.

Note that the insert statement could fail for another reason. If either of the IDs we try to insert does not refer to an existing record in the table it refers to, it would fail with an exception sqlite3.IntegrityError: foreign key constraint failed.

The final sanity check in the last line is to use the rowcount attribute to verify that just one record was inserted.

The add() method does make sure that the instances passed to it are in the correct order by checking the names of the classes against the names of the columns stored by the inittable() method. It raises a ValueError() if this is not correct, otherwise it instantiates a new relation by calling the class constructor with both IDs.

Chapter5/relation.py

@classmethod
def add(cls,instance_a,instance_b):
		if instance_a.__class__.__name__ != cls.columns[0] :
				raise ValueError("instance a, wrong class")
		if instance_b.__class__.__name__ != cls.columns[1] :
				raise ValueError("instance b, wrong class")
		return cls(instance_a.id,instance_b.id)

The list() method is meant to return a list of zero or more Relation objects.

Chapter5/relation.py

@classmethod
def list(cls,instance):
		sql='select %s_id,%s_id from %s where %s_id = ?'%(
				cls.columns[0],cls.columns[1],
				cls.__name__,instance.__class__.__name__)
		with cls.threadlocal.connection as conn:
				cursor=conn.cursor()
				cursor.execute(sql,(instance.id,))
				return [cls(r[0],r[1],stub=True)
								for r in cursor.fetchall()]

It needs to work for both sides of the relation: if we pass a Car instance, for example, to the list() method of the CarOwner class, we should find all records where the Car_id column matches the id attribute of the Car instance.

Likewise, if we pass an Owner instance, we should find all records where the Owner_id column matches the id attribute of the Owner instance. But precisely because we gave the columns in the table that represents the relation meaningful names derived from the names of the classes and hence the tables, this is rather straightforward. For example, the SQL constructed for CarOwner.list(car) might look like the following:

select Car_id,Owner_id from CarOwner where Car_id = ?

Whereas the SQL for CarOwner.list(owner) would look like the following:

select Car_id,Owner_id from CarOwner where Owner_id = ?

This is accomplished by referring to the class name of the instance passed as argument (highlighted).

After executing this SQL statement, the results are fetched with the fetchall() method and returned as a list of relation instances. Note that this list may be of zero length if there weren't any matching relations.

The last method of note defined for the Relation class is the delete() method.

Chapter5/relation.py

def delete(self):
		sql='delete from %s where %s_id = ? and %s_id = ?'%(
			self.__class__.__name__,self.columns[0],self.columns[1])
		with self.threadlocal.connection as conn:
			cursor=conn.cursor()
			cursor.execute(sql,(self.a_id,self.b_id))
			if cursor.rowcount!=1:
					raise ValueError()

It constructs an SQL delete statement which, in our CarOwner example, may look like this:

delete from CarOwner where Car_id = ? and Owner_id = ?

The sanity check we perform in the last line means that an exception is raised if the number of deleted records is not exactly one.

Now that we have a simple Entity and Relation framework, let's look at how we can use this to implement the foundation of our books application.