We will look at an example where an object has data in two different tables in the database. We add an address to the Person class. Each person will only have one address. An Address is a class with instance variables for a street and a city. Here are the declarations of each class:

In the database we will use two tables, PEOPLE and ADDRESSES to store the data. Two tables are used since more than one person may have the same address. First we need to create the GLORP descriptor for the database. Here is the definition of the descriptor class:

Now we need to provide the list of classes and tables. Here are the relevant methods:

The definition of the ADDRESSES table is straightforward.

In the PEOPLE table we need to add a foreign key¹. Here is how it is done.

Each class also needs a descriptor entry. The Address class has the same structure as the Person class in the last example. So the descriptor does not have anything new.

In the Person class we need information about the address. The last two entries below do this. The OneToOneMapping tells GLORP to use a different table (via a one-to-one map) to get the data for address. Note we do not provide information about which table to get the data for the address instance variable. That information will be added in a different location.

We still need to let GLORP know how to deal with the address instance variable in the Person class. This is done using a class model. In the method we provide information about each instance variable.

Of interest is “address”. The line:

tells GLORP that the instance variable address holds an object of type Address. Since the descriptor contains information about the Address class, this is how GLORP knows how to map the address instance variable of Person to the database.

Now we can log on to the database and create the tables.

Once the tables are created we can add some data. Here we create a Person object with an address and add it to the database.

Just for the record here is the SQL generated during the above transaction.

With some data in the database we can make queries on the data. Here is a simple query. Since the statement just reads data there is no need to put this statement in a transaction or unit of work.

The query uses the following SQL to get the data from the database:

Alert readers will note that only the id is read from the ADDRESSES table. The Person object created by the query has a proxy² for its instance variable. The data for the address is not fetched from the database until it is actually used. After adding the accessor methods to the People and Address classes the following statement:

will trigger the proxy to fetch the Address object. The SQL used to do that is:

Using a unit of work any change to the Person object will be saved to the database. Here we change the Address object in the Person object. When the unit of work is committed the address data is updated.

Here is the SQL used to update the data. Note that GLORP is making an unneeded request for the next value in the ADDRESSES is sequence.

Once we are done with the session we logout:

Adding a Person object to the database also adds the person’s address if the address is new. If two people have the same address, it is possible to add the same address twice. For example suppose we first do:

And at some later time we do:

Now the address table will have two rows with the same data. If you do not want this to occur, you must take care. You should read the address from the database and then add it to the Person object.

Although adding a person to the database will add its address to the database if needed, deleting a person does not delete its address. That is if one first does:

And then does

The address associated with roger is still in the address table. So, you must explicitly delete each object from the database.

In our descriptor we can define pseudovariables for a class. These are variables that do not exist in the class, but we can use in a GLORP query about the class. Below we define a pseudovariable addressId for the address_id column in the PEOPLE table.

In a query we can then use the pseudovariable as if it were an instance variable. Here is an example use of the psuedovariable.

In general we prefer not to deal with database id’s

Often an object will contain a collection. The way this is handled in a database is to create a separate table for the collection. Each row in the table contains a foreign key indicating who owns the row. GLORP handles this with a one-to-many map. To illustrate this we give the Person class a collection of email addresses. An EmailAddress has a username and host. We create EMAIL_ADDRESSES table with columns:

Here is the definition of the EmailAddress class.

The class also has standard accessor methods to set and get userName and host.

The Person class definition:

The Person class also has get and set methods for lastName and firstName.

To save space the example does not include the address added in the one-to-one section. Since the only thing new here is the one-to-one mapping the example is given in full without comment. Here is the Descriptor:

When the email addresses are read for a Person we can determine the order in which they are read from the database. This is done by telling the mapping the order to use when reading values. In the descriptor for the Person we indicate that the addresses are to be sorted by the userName. The ordering is done on the database. Multiple orderBy: messages can be sent to a mapping.

The read order can be specified by indicating the table and column explicitly. There is no reason to do this in the current example. However, when we have association (tie or link) tables a mapping will involve three tables. In that case being able to indicate the table and column can be useful. Here is how to specify the table and column.

When reading a person from the database as below, the email addresses in the Person object are stored in an ordered collection.

As of GLORP 0.3.111 it is possible to specify in the class model which type of collection GLORP will use. Below we indicate that a SortedCollection should be used. We can use all the standard collection classes except a Dictionary, which requires extra information. Using a dictionary is covered in the next section.

Mapping a dictionary to a database table is slightly more complex then mapping an ordered collection. To illustrate this we will create an address book that stores instances of Person. The address book will store people in a dictionary. Keys will be a string that users associate a particular person with. Here is the AddressBook class.

The Person class just has the instance variables firstName, lastName and id. The class definition is below. You can add whatever methods you want to the class.

We need three tables in the database. First we need one to hold the Person object data. Second we need a table to for address books. Each row in the ADDRESS_BOOK table represents one address book. In this case the table just has two columns, one for a primary key and another for the title of the address book. The third table, ADDRESS_BOOK_LINKS, is an association (link or tie) table. Each row in the association table represents one entry in an address book.

Note how we indicate that the entries instance variable is a dictionary.

The address book descriptor includes a dictionary mapping which ties the data from the other two tables to the address book.

Each row in the ADDRESS_BOOK_LINKS represents one entry in an address book. It has foreign keys to the other tables to indicate which person and which address book the entry is in. It also stores the key for the entry in the dictionary.

The following shows an example of adding an AddressBook to the database.

There are times when we do not want a separate table for each object. These objects are ones whose identity does not depend on identity⁴ like money or date range. In this case we store multiple objects in a single table. As an example we will use a Name object to represent the name of a Person object. First we have an outline of the Person and Name classes. Note that the Name class does not need an id. In the database it is stored in the same row as the rest of the Person object data, which does have a database id.

The descriptor defines a Person table with four columns. Note the use of the EmbeddedValueOneToOneMapping in descriptorForPerson:.

When you read a Person object from the database, as below, the Name object for the Person name instance variable is not a proxy. There is no performance reason to read just part of the row, so the Name object data is read and the Name object is instantiated.

Currently it does not seem possible to use the embedded value as the primary key of the table.

There are times when the data for one object is located in multiple tables. Normalizing a database can be one cause of this. As an example we will use a Person class with the instance variables firstName, lastName and id. The database tables are PEOPLE and NAMES with the columns indicated below. The last_name_id column is the foreign key to the NAMES table.

We could create a class to represent the Names table, but in this example we will only use a Person class. Here is the definition of the class. You can add whatever methods you want to the class.

Clearly, a join on the two tables is needed to get the data we need for a Person object. The method descriptorForPerson: contains the description of the join. The rest of the descriptor is straightforward.

Association tables⁵, sometimes called tie or link tables, are common. For example consider a database for a bookstore for books on order. We will have table of books and a table of customers. To reuse part of previous examples, we will make this a table of people. An individual may have many books on order. Also a given book may be on order by many different people. This is handled by using an association table. Each row of the table can represent the order of a book by one person. The table at least contains a column to refer to a Person in the PEOPLE table and a column to refer to a Book in the BOOKS table.

In the example the association table is called BOOKS_ON_ORDER. To keep the example small the Book class only contains an id and title instance variables. We do not show the standard accessor methods for the Book class. We add an instance variable booksOnOrder to the Person class. We show all but the needed accessor methods in the Person class.

The descriptor has several new things. First of all it has more tables than classes.

The new thing here is the ManyToManyMapping mapping. The amazing thing is that we do not need to include the name of the association table. GLORP will deduce which table is the association table. We will see later how it explicitly list the association table.

The tables are straightforward. The BOOKS table and PEOPLE table have primary keys and the columns for title and names.

The BOOKS_ON_ORDER table is the association or tie table. Each row represents one book on order for a person. So it has two foreign keys, one to the PEOPLE table and one to the BOOKS table.

This is the end of the GlorpTutorialDescriptor class. Here is sample code to add some books and people to the database.

An example of reading books.

Now to have some people order books.

We can of course read a person and see what books they have on order.

A more interesting query is to find all people that have a particular book or books on order. The following query will not work.

While the where block would work if we were dealing with Smalltalk objects. However the where: block is used to generate SQL. There are limited messages that can be sent to the parameter of the where block. One class of messages that can be sent is instance variables of the class. So we can use ‘where: [:each | each booksOnOrder’. Since booksOnOrder is collection we can send it either anySatisfyJoin: or anySatisfyExists:. We will look at both.

First we have:

This generates the following SQL query on the database.

A second way is to use:

Or equivalently

This results in the following SQL:

You can decide which is better for your situation.

A Person object contains a collection of books in the booksOnOrder instance variable. We can specify the order in which this collection is read from the database when we read a Person object. In the following modification of descriptorForPerson: the orderBy: message sent to the ManyToManyMapping orders the books by the title instance variable. Actually the ordering is done on the database side on the column mapped to the title instance variable of the Book class.

We can also use a block to indicate the order.

While the block syntax is a bit more verbose it allows us to reverse the order as the following illustrates.

It is also possible to explicitly specify the field on the table to be used to determine the order.

If one looks at this example closely it is not clear how GLORP determines which table is the tie or link table. In the descriptor for the ManyToManyMapping one just provides the name of the instance variable (booksOnOrder) and the type of elements that will be held in the instance variable (Book). If you look at the examples that come with GLORP you will find places where the message “useLinkTable” is sent to the ManyToManyMapping as is done below. This tells GLORP that it needs to find a link table for this mapping. However, since ManyToManyMapping automatically sets the useLinkTable to true, this message is not need except to remind the reader that a link table is used.

There are times when the link table is populated via other means and we want to insure that we do not add or deleted link table entries. This can be done by making the mapping read only as is done below.

Once this is done we cannot add or delete items from the link table via this mapping. It can be done other ways. However the following code will not add the books to the Sam’s list of books on order. No exceptions are raised, so Sam is added to the PEOPLE table.