Somewhere between last week's blog entry and this one, I decided it was long past time for my annual machine rebuild. It doesn't take long for a Windows machine to get overloaded with cruft. Some of it I put there on purpose, some of it I didn't. Attempts at routine maintenance ultimately can't hold up to all the things that want to invade your system.

So I put everything, or so I thought, in one spot to be backed up. Actually I've gotten my machine to a point where almost everything critical is in one place: under my profile directory. My Eclipse workspace, my documents, my FitNesse installation (I think I need to figure out how to install the product elsewhere and have it point to the profile directory for its documents), my email, my CVS repository, and just about anything else I want to have each time I rebuild a machine. That includes a list of software to download and install, not the software itself (unless I'm married to a specific version). It amounts to only about half a gig, enough to back up on a CD.

There's always something I forget to centralize or back up. This time I forgot to back up my database. There's no valuable data in it currently; about the only thing I wanted to keep was the schema for this blog series. Oh well.

After spending a bunch of time rebuilding the machine (there are so many pieces of software I don't remember having, but I did, and they need to be there), I'm now ready to get back to the blog series. I remembered my mistake when I reopened the Eclipse project. Many of the tests don't run, since there is no database! I was going to tackle something different in this blog installment, but I guess now is as good as any time to deal with the relationship between the application and the database.

If you're fortunate enough to have control of the database for your application needs, there's little reason to design it any differently than the application's object structure (performance might be one reason). You might be in a less fortunate situation, where you have a high-falutin' DBA dictating what you're going to have to work with.

If you're not stuck with someone else's mandate, you can treat the database schema and persistable class definitions as different views on the same entity. One can generate the other, and vice versa. Since I no longer have a schema, I'm going to have the application generate it.

I scraped together the following test:

package schema;

import junit.framework.*;

public class TableGeneratorTest extends TestCase {
   public void testSimple() {
      TableGenerator<Simple> generator = new TableGenerator<Simple>();
      generator.create(new SimpleAccess());

      SimpleAccess access = new SimpleAccess();
      final String name = "abc";
      Simple simple = new Simple(name);
      access.save(simple);
      Simple retrieved = access.find(name);
      assertEquals(name, retrieved.getName());
   }
}

This test required me to build SimpleAccess and Simple:

Simple

package schema;

import persistence.*;

class Simple implements Persistable {
   public static final String NAME = "name";
   private String name;

   Simple(String name) {
      this.name = name;
   }

   public Object get(String key) {
      return name;
   }

   public String getName() {
      return name;
   }
}

SimpleAccess

package schema;

import java.util.*;

import persistence.*;
import persistence.types.*;

class SimpleAccess extends DataAccess<Simple> {
   private static Column[] COLUMNS = new Column[] {
      new StringColumn(Simple.NAME)
   };

   public String getTable() {
      return "testsimple";
   }

   public String getKeyColumn() {
      return Simple.NAME;
   }

   public Simple create(Map<String, Object> row) {
      return new Simple((String)row.get(Simple.NAME));
   }

   public Column[] getColumns() {
      return COLUMNS;
   }
}

Simple enough. (Sorry!)

Hmm. As soon as I started to code a solution, I realized that I wanted to break it down and code a test to prove I can build the correct SQL. Moving over to SqlTest, I wrote:

public void testCreateTable() {
   String statement = String.format(
         "create table t (a varchar(%d),b varchar(%<d))",
         StringColumn.DEFAULT_WIDTH);
   assertEquals(statement, sql.create());
}

I got it to pass with this code in Sql:

public String create() {
   Transformer columnDefinition = new Transformer() {
      public String transform(Object input) {
         Column column = (Column)input;
         String declaration = String.format("varchar(%d)", StringColumn.DEFAULT_WIDTH);
         return String.format("%s %s", column.getName(), declaration);
      }};
   String columnDefinitions = StringUtil.commaDelimit(columns, columnDefinition);
   return String.format("create table %s (%s)", table, columnDefinitions);
}

Which, of course, only supports fields of type String. I'll fix that, but for now, it passes and meets the needs of TableGenerator, which only demonstrates generating tables with VARCHAR columns (I'll fix that too).

For now, I'm ok with assuming all Strings are to be stored with a default width in the database. I'll define DEFAULT_WIDTH in StringColumn as:

public static final int DEFAULT_WIDTH = 32;

With the Sql modification done, I coded TableGenerator.

package schema;

import persistence.*;
import sql.*;

public class TableGenerator<T extends Persistable> {
   public void create(DataAccess<T> access) {
      String sql = new Sql(access.getTable(), access.getColumns()).create();
      new JdbcAccess().execute(sql);
   }
}

Wow. I'm pretty thrilled about how easy it is to code new functionality. That's a very simple class and method, maybe it belongs elsewhere. On the Persister class, perhaps? Maybe later; I need to get the rest of my unit tests working by building their tables.

One minor detail before I fix the problem with data types: I can only run the test once. I've got to ensure we delete the test table. Looking at JdbcAccessTest, its setUp and tearDown code creates and drops a table. I think it's time to add those as capabilities to JdbcAccess itself. Further, I need to be able to try and drop a table without having it throw an exception if no such table exists.

JdbcAccessTest

public class JdbcAccessTest extends TestCase {
   ...
   private JdbcAccess access;

   protected void setUp() {
      access = new JdbcAccess();
      access.execute(String.format("create table %s (%s varchar(1))", TABLE,
            COLUMN_NAME));
   }

   protected void tearDown() {
      access.dropIfExists(TABLE);
   }

   public void testDrop() {
      access.drop(TABLE);
      try {
         access.drop(TABLE);
      }
      catch (JdbcException expected) {
         Exception e = (Exception)expected.getCause();
         assertTrue(e.getMessage(), e.getMessage().indexOf("Unknown table") != -1);
      }
   }

   public void testDropIfExists() {
      access.dropIfExists(TABLE);
      try {
         access.drop(TABLE);
      }
      catch (JdbcException expected) {
         Exception e = (Exception)expected.getCause();
         assertTrue(e.getMessage(), e.getMessage().indexOf("Unknown table") != -1);
      }
      access.dropIfExists(TABLE); // shouldn't throw exception
   }
   ...

I always post my blogs and then review them quickly to correct any formatting errors. In doing so, I notice that testDrop and testDropIfExists contain the same try/catch code. I've already posted the code, but I'm going to make a refactoring change that will appear in the next drop.

JdbcAccess

public void drop(String table) {
   execute("drop table " + table);
}

public void dropIfExists(String table) {
   try {
      drop(table);
   }
   catch (JdbcException expected) {
      Exception e = (Exception)expected.getCause();
      if (e.getMessage().indexOf("Unknown table") == -1)
         throw expected;
   }
}

TableGeneratorTest now looks like this:

TableGeneratorTest

package schema;

import persistence.*;
import junit.framework.*;

public class TableGeneratorTest extends TestCase {
   private JdbcAccess jdbc;
   private SimpleAccess access;

   protected void setUp() {
      jdbc = new JdbcAccess();
      access = new SimpleAccess();
      jdbc.dropIfExists(access.getTable());
   }

   protected void tearDown() {
      jdbc.drop(access.getTable());
   }

   public void testSimple() {
      TableGenerator<Simple> generator = new TableGenerator<Simple>();
      generator.create(new SimpleAccess());

      final String name = "abc";
      Simple simple = new Simple(name);
      access.save(simple);
      Simple retrieved = access.find(name);
      assertEquals(name, retrieved.getName());
   }
}

Maybe I could put the TableGenerator code into JdbcAccess. No, not a good idea. That would create a dependency of JdbcAccess on the classes Persistable and DataAccess. Best to keep JdbcAccess as ignorant of anything else in the system as possible.

One final note: I moved the source folders into a single Eclipse source directory, src. I also included a lib directory with the MySQLConnector JAR file. If you download the code archive, your best bet might be to start afresh with a new project.

OK, now time to go back to SqlTest and get the create method to support IntegerColumn. Right now the tests all use the COLUMNS constant, currently defined as:

private static Column[] COLUMNS = { new StringColumn("a"),
      new StringColumn("b") };

I'll change that to:

private static Column[] COLUMNS = { new StringColumn("a"),
      new IntegerColumn("b") };

...and see what breaks. Two test methods break. I change testInsert to:

public void testInsert() {
   final Object[] values = { "1", 2 };
   assertEquals("insert into t (a,b) values ('1',2)", sql.insert(values));
}

In testCriteria, I have to switch around the Equals and Like criteria clauses.

public void testCriteria() {
   int value = 1;
   String pattern = "p%";
   Criteria criteria = new And(new Equals(COLUMNS[1], value), new Like(
         COLUMNS[0], pattern));
   String sqlString = sql.select(criteria);
   assertEquals("select a,b from t where (b=1) and (a like 'p%')",
         sqlString);
}

Now I can fix testCreateTable (which I just now chose to rename to testCreate).

public void testCreate() {
   String statement = String.format(
         "create table t (a varchar(%d),b integer)",
         StringColumn.DEFAULT_WIDTH);
   assertEquals(statement, sql.create());
}

I can get that to quickly pass with this nasty bit of code:

public String create() {
   Transformer columnDefinition = new Transformer() {
      public String transform(Object input) {
         Column column = (Column)input;
         String declaration = null;
         if (column instanceof StringColumn)
            declaration = String.format("varchar(%d)", StringColumn.DEFAULT_WIDTH);
         else
            declaration = "integer";
         return String.format("%s %s", column.getName(), declaration);
      }};
   String columnDefinitions = StringUtil.commaDelimit(columns, columnDefinition);
   return String.format("create table %s (%s)", table, columnDefinitions);
}

Horrors. That defeats the point of the having the Column type hierarchy in the first place. I'll move things over.

Here are all the changes to the Column type hierarchy to support deriving a declaration from a given Column object:

Column

package persistence.types;

public interface Column {
   String getName();
   String sqlValue(Object object);
   String declaration();
}

StringColumnTest

public void testDeclaration() {
   assertEquals(String.format("%s varchar(%s)", ColumnTest.NAME,
         StringColumn.DEFAULT_WIDTH), column.declaration());
}

StringColumn

public String declaration() {
  return String.format("%s varchar(%s)", super.getName(), DEFAULT_WIDTH);
}

IntegerColumnTest

public void testDeclaration() {
   assertEquals(String.format("%s integer", ColumnTest.NAME), column
         .declaration());
}

IntegerColumn

public String declaration() {
   return String.format("%s integer", super.getName());
}

Now I can fix the code in the create method.

public String create() {
   Transformer columnDefinition = new Transformer() {
      public String transform(Object input) {
         return ((Column)input).declaration();
      }};
   String columnDefinitions = StringUtil.commaDelimit(columns, columnDefinition);
   return String.format("create table %s (%s)", table, columnDefinitions);
}

OK, last step. I wrote this crummy little utility class to generate the tables I need:

package schema;

import domain.*;

public class TableCreator {
   public static void main(String[] args) {
      new TableGenerator<Customer>().create(new CustomerAccess());
      new TableGenerator<User>().create(new UserAccess());
   }
}

I'm not sure where to put this class or what to do with. I don't think I want to run it all the time, i.e. as part of my JUnit runs. Or maybe I do. For now, I don't care. I run TableCreator. I run my unit tests. All green! Time to get ready for the weekend.

code