 he Problem I faced
One of the biggest parts of my job as an Apprentice Master at KSC is
teaching students to build object systems that connect to a relational database.
I've written extensively about this subject ([Brown 96a], [Brown 96b]), so I
won't rehash much of that here. However, there is one purely mechanical aspect
of my job that tends to drive me to distraction on a regular basis.
In the Apprentice Program, we set up a portion of our corporate network to
resemble our client's environment as closely as possible. If a client is using
a relational database, we must set up the appropriate database and give them
access to it. Over the years in the apprentice programs, we've had students
using SQL Server, Oracle, DB2, and just about any other database you'd care to
name. In [Brown 95a], the development process derives our relational database
designs from our object designs. That means that we determine the database
schemas as part of our object design process. As you can imagine, in an
iterative design process schemas tend to change quite a bit.
Why is it hard?
One of the most annoying parts of setting up and modifying database schemas
is that while SQL is pretty well standardized across all vendors for querying
and database creation, schema modification is decidedly not standardized.
Perhaps it might be better to say that every database vendor has their own
standard - they just haven't convinced any other vendors to switch over to
their standard! Simple tasks like changing a column name, adding a
column, or removing a column from a table require different commands on Oracle,
SQL Server and DB2. Not only are the commands different, but each vendor has
their own maintenance tool (SQL*Plus, SAF, etc.) that each have their own little
quirks that you have to master.
The Solution
After spending long hours poring over manuals to find the obscure command to
rename a column in one particular database, I finally decided to build a tool to
handle this for me. What I was able to find was that judiciously applying a
couple of design patterns, Command and Strategy, made it
possible to build an extremely flexible system with only a few classes.
Hopefully, explaining the design will clarify the meanings of these patterns for
you, and give you some insights about how to apply them in your own programs as
well.
I decided to implement the system in VisualAge for Java in order to get the
power and flexibility that Java gives in dealing with different database
connections. I also liked the potential of implementing the tool as either a
standalone application or a three-tier applet/application pair that VisualAge
provides. In a later article, I'll describe how some of the features of the
JDBC (Java Database Connectivity specification) allow us to flexibly work with
any database regardless of the peculiarities of any particular database API.
Metadata
The first step I took towards designing my tool was sketching out the
primary domain objects in my system. Since I was dealing with a relational
database, the two classes that stood out in my design were DbTableand
DbColumn. Now, this may seem obvious to most of you, but believe me,
this isn't necessarily a natural abstraction. I've often seen database tools
that have no representation of the data elements apart from the actual
tables and columns in the database. This sort of "map of the thing"
separate from "the thing" is often referred to as Metadata.
It's an extremely powerful concept, and gives us a lot of flexibility in our
design.
I came up with the following design for my DbTable and DbColumn
classes:
A DbTable contains a Vector of DbColumns.
Each DbTable has a unique name. DbTable has methods to add and remove
columns, and to find a column with a particular name. A DbColumn is
simply a triplet of a name, a datatype, and a boolean indicating if it
is required (NOT NULL) or not. These two classes form the heart
of our system. As you will see later, the rest of the classes in the system
exist to manipulate these two classes in different ways.
Command
The next set of classes that I developed was a set of classes to represent
the different actions that I could possibly take on a database table. In my
particular case, I was concerned with adding columns to a table, removing
columns from a table, and renaming a column. There are many other possibilities
that I could have implemented, but these three covered the bulk of the work that
I needed to do. The question arises now - why did I need to implement these as
classes? Aren't these just behaviors of a Table or Column? In fact there are
corresponding behaviors in my DbTable and DbColumn classes,
but I need to take a short digression to explain why I needed to implement these
actions as classes.
I envisioned myself and my apprentices as using the tool in the following
way: I would pick a table from a list of tables in a particular database, and
then would see a list of the different columns in the table, along with the
information about those columns. I would want to be able to select any column,
choose to modify or delete it, and be able to add columns to the list. Most
interactive applications like this also feature some sort of undo/redo facility.
It's nice to be able to "back out" of an action that you didn't
intend - like deleting a column when you really meant to rename it. The
standard design pattern that you can apply in cases like this is the Command
pattern from [Gamma 94]. The intent of the Command pattern states you
can "Encapsulate a request as an object, thereby letting you parameterize
clients with different requests, queue or log requests, and support undoable
operations".
This is a very useful facility and one that I wanted in my design. But
there was another reason that I wanted to use the Command pattern
completely separate from the undo/redo facility. The way that SQL databases
change a database schema involve making a series of "deltas" or
changes to the particular database as a series of SQL commands. For instance,
if you want to add a column to a table, you would use the ALTER
command in SQL. As an example, if you had an employees table and wanted
to add a salary column, you would use the following command in ANSI
standard SQL:
ALTER employees ADD salary money NOT NULL WITH DEFAULT
Each different action (add, remove, rename) has a different set of commands
that you have to execute to perform the action. Therefore, while the table
representation is useful for viewing what your final database table will
look like, the commands are necessary to actually get to the
representation you want. So, what I arrived at was a hierarchy that looked like
the following:
The methods applyTo(DbTable aTable) and backoutFrom(DbTable
aTable) support changing a DbTable to incorporate a
particular change, and undoing that change respectively. The method generateSQLWith(SQLGenerator
gen, DbTable table) is responsible for creating and executing the correct
SQL statements that actually change a database table to its new form.
As an example, let's look at how the applyTo() method is
implemented in the class AddColumn:
public void applyTo(DbTable table) { table.addColumn(column);
}
Pretty straightforward, eh? ThebackoutFrom() method is
equally simple - it's implemented by calling removeColumn()with
the command's column. As you can imagine, the DeleteColumn
command is implemented by simply switching the addColumn() and
removeColumn() messages between the applyTo() and
backoutFrom() methods. RenameColumn is also very
simple. It holds onto the old column name and the new column name. It's
applyTo()method finds the column with the old name and renames it
to the new name, while its backoutFrom() method does the opposite.
The generateSQLWith() methods in the three command classes are
a little harder to understand. To understand how they are implemented, we need
to take a closer look at our problem and investigate another design pattern:
Strategy.
Strategy
As I mentioned earlier, one of the annoying aspects of schema maintenance is
that the actual SQL commands to add, delete and rename columns differ from
database to database. For instance, the command to rename a column in SQL
Server is very simple:
sp_rename tableName.oldName,tablename.newName
On the other hand, accomplishing the same task in Oracle requires a bit more
work:
CREATE TABLE temporary ( newColumnName, col1, col2, …)
AS SELECT oldName, col1, col2, … FROM tableName
DROP TABLE tableName
RENAME temporary to tableName
We need to be able to encapsulate the particular database specifics in our
design so that we can operate correctly on each database. One potential way in
which we could do this is to make subclasses of each command class for each
database. We could have a OracleAddColumnclass and a SybaseAddColumn
class and override the method generateSQLWith() in each. However,
this makes adding a new database a real pain. We would have to add new
subclasses of each command class every time we wanted to add a new database - a
lot of unnecessary work for little benefit.
A better solution is to use the Strategy design pattern from [Gamma
94]. Strategy's intent is to "Define a family of algorithms, encapsulate
each one and make them interchangeable. Strategy lets the algorithms
vary independently from clients that use it." The last bit - about letting
the algorithms vary independently from the clients is exactly what we want. We
want to detach our database dependencies from the particular commands that the
user has created. The following diagram will begin to show how we can
accomplish this:
We have an abstract superclass SQLGenerator and subclasses for
each particular database. Note that this only adds one new class for
each database, not the many necessary in the other solution. Each class
implements methods to build the particular SQL for a given command in that
database. For instance the SqlServerSQLGenerator class would
generate SQL based on the sp_rename
command we described above. On the other hand, the OracleSQLGenerator
class would know to generate the more complex SQL shown in the other example.
Let's try to tie this together and make it a little more concrete.
The implementation of generateSQLWith() in RenameCommand
looks like the following:
public void generateSQLWith(SQLGenerator gen, DbTable table) {
gen.buildRenameSqlFor(this,table);
}
If the first parameter passed in was an instance of SqlServerSQLGenerator,
the following method would be called:
public void buildRenameSqlFor(RenameColumn renameCommand, DbTable sourceTable){
StringBuffer spRenameCommand = new StringBuffer();
spRenameCommand.append("sp_rename ");
spRenameCommand.append(renameCommand.getOldName());
spRenameCommand.append(',');
spRenameCommand.append(renameCommand.getNewName());
execute(spRenameCommand.toString());
}
The implementation in OracleSQLGenerator looks similar, but
instead creates three StringBuffers, one for each SQL statement,
and executes them separately.
The End Result
Putting the different pieces together, we arrive at a final design that
looks like the following:
There is one additional class in the final design that we haven't discussed
- TableBuilder. A TableBuilder does just that - it
builds tables using the Commands that we've seen. It contains a Stack
of Commands and allows the user to undo actions by simply popping them from the
top and undo-ing them against the current DbTable. As you might
have guessed, it's an implementation of another pattern - in this case the Builder
pattern from [Gamma 94].
Final thoughts
That's it for this time. Hopefully, you can now see a little better how
Command and Strategy can work with code generation. Also, you
have seen how keeping a separate metadata representation of an entity can make
understanding how to work with it easier. As I mentioned earlier, in a later
article we'll return to this example and examine how we can use the JDBC
features in VisualAge for Java. We'll look at creating DbTables
from existing system table information regardless of the actual database we're
working against and how to execute the SQL we've generated. A simplified
version of this code that doesn't execute the SQL against a database is
available from http:\\www.ksccary.com. If
you have any comments or questions, please feel free to contact me at
kbrown@ksccary.com.
References
[Brown 96a] Brown and Whitenack, "Crossing Chasms: A pattern language
for Object-Relational Integration",Pattern Languages of Program Design, Vol
II, John Vlissides, Jim Coplien, and Norman Kerth Eds., Addison-Wesley, 1996 (http://www.ksccary.com/ordbjrnl.htm)
[Brown 96b] Brown and Whitenack, "A Pattern Language for Relational
Databases and Smalltalk", Object Magazine, October 1996 (http://www.ksccary.com/pldbjrnl.htm)
[Gamma 94] Erich Gamma, Richard Helm, Ralph Johnson and John Vlissides, Design
Patterns: Reusable Elements of Object Oriented Software, Addison-Wesley
Publishing Company, Reading MA. (http://st-www.cs.uiuc.edu/patterns/DPBook/DPBook.html)
Enjoy the article?
Subscribe
to Eye on Objects! | 
