Dealing with databases with easyb

easyb supports plug-in development-- for instance, the dbunit-plug-in supports database management via DbUnit-- if you find that a particular story requires some prerequisite data, you can put that data in a database via the database_model call, which takes standard JDBC connection information (URL, user name, driver, etc) and then a String representation of DbUnit's standard FlatXmlDataSet type. So for example, in a given clause you can seed a database like so:


given "the database is properly seeded", {
 database_model("org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:hsql://127.0.0.1", "sa", ""){
       return new File("./src/conf/seed.xml").text
    }
}			

Using Groovy's MarkupBuilder is easy and lacks XML syntax:


given "the database is properly seeded", {
 database_model("org.hsqldb.jdbcDriver",
  "jdbc:hsqldb:hsql://127.0.0.1", "sa", ""){
    def writer = new StringWriter();
    def builder = new MarkupBuilder(writer);

    builder.dataset(){
    word(word_id:1, spelling:"bellicose", part_of_speech:"Adjective")
     definition(definition_id:10, definition:"demonstrating willingness \
        and willingness to fight",
     word_id:1, example_sentence:"The pugnacious youth had no friends \
       left to pick on." )
     synonym(synonym_id:20, word_id:1, spelling:"belligerent")
     synonym(synonym_id:21, word_id:1, spelling:"aggressive")
   }
  return writer.toString()
 }
}

Or just create the XML using """.


given "the database is properly seeded", {
 database_model("org.hsqldb.jdbcDriver",
  "jdbc:hsqldb:hsql://127.0.0.1", "sa", ""){
   """<?xml version='1.0' encoding='UTF-8'?>
   <dataset>
    <word WORD_ID="1" SPELLING="pugnacious" PART_OF_SPEECH="Adjective"/>
    <definition DEFINITION_ID="10" DEFINITION="Combative in nature; belligerent."
      WORD_ID="1"
      EXAMPLE_SENTENCE="The pugnacious youth had no friends left to pick on."/>
    <synonym SYNONYM_ID="20" WORD_ID="1" SPELLING="belligerent"/>
    <synonym SYNONYM_ID="21" WORD_ID="1" SPELLING="aggressive"/>
   </dataset>
   """
 }
}

That makes integration testing easy, doesn't it? The DbUnit plug-in is a separate jar file you can download from the easyb Google code website-- just ensure you have that jar in your classpath and easyb will find it if you invoke the database_model call.

The plug-in in action

The easiest way to demonstrate the plug-in is to look at its own story-- in this case, the dbunit-plug-in uses easyb to verify itself.

Using easyb itself, it is rather easy to describe what the plug-in does and how to use it. A story to validate the database plug-in sounds a bit like this:

  • given that database has an initialized data model (i.e. tables to hold stuff)
  • and given the plug-in’s method is invoked with a dataset (i.e. rows for tables)
  • when a select statement is issued for a value only found in the dataset inserted by the plug-in
  • then the desired item (i.e. column value) should be returned

In essence, this story is the use case for the plug-in– reading it you can most likely ascertain that the plug-in puts rows in database tables. Reading this story, it is also evident that at some point, it may be helpful to start and stop a database instance during the course of the story– i.e. start it up at the commencement of the story and stop it at the end.

Using easyb’s story format, you can sketch out the body of the story as follows (note, the story includes starting and stopping a database):


given "a database is up and running", {}
and "the database has an initialized data model", {}
and "the database_model method is invoked with a dataset", {}
when "a select statement is issued for a value that could
only be present if the database_model call worked", {}
then "the word bellicose should be returned", {}
and "shut down the database", {}

Making the story executable entails adding some logic– in the course of doing so, the story demonstrates how to use the database plug-in (minus the first and last steps, right?)!

The first given can be coded as follows (in Groovy, that is):


given "a database is up and running", {
 Server.main(["-no_system_exit", "true"] as String[])
}

The Server instance in the above code is HSQLDB’s org.hsqldb.Server type. This is, by the way, a handy database that is easily controlled programatically and easy to install (it’s a jar file!).

The next given is a bit more complicated– the database needs an empty schema– one can’t assume one is present, so the story just creates it. This also happens to validate the story quite nicely– no assumptions are made about the underlying database!


and 

given "the database has an initalized data model", {
 sql = Sql.newInstance("jdbc:hsqldb:hsql://127.0.0.1",
   "sa", "", "org.hsqldb.jdbcDriver")
 ddl = """DROP TABLE definition IF EXISTS;
  DROP TABLE synonym IF EXISTS;
  DROP TABLE word IF EXISTS;

  CREATE TABLE word (
   WORD_ID bigint default '0' NOT NULL,
   PART_OF_SPEECH varchar(100) default ''  NOT NULL,
   SPELLING varchar(100) default '' NOT NULL,
   PRIMARY KEY  (WORD_ID),
   UNIQUE (SPELLING));

  CREATE TABLE definition (
   DEFINITION_ID bigint default '0' NOT NULL,
   DEFINITION varchar(500) NOT NULL,
   WORD_ID bigint default '0' NOT NULL,
   EXAMPLE_SENTENCE varchar(1000),
   FOREIGN KEY (WORD_ID) REFERENCES word(WORD_ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
   PRIMARY KEY  (DEFINITION_ID));

  CREATE TABLE synonym (
   SYNONYM_ID bigint default '0' NOT NULL ,
   WORD_ID bigint default '0' NOT NULL ,
   SPELLING varchar(100) default '' NOT NULL ,
   FOREIGN KEY (WORD_ID) REFERENCES word(WORD_ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
   PRIMARY KEY  (SYNONYM_ID));
  commit;"""
 sql.execute(ddl);
}

The data model here is quite simple– three tables and a bit of GroovySQL to jam it into the database.

Next, you need to actually use the plug-in to seed the database with data! The core API of the plug-in is the database_model closure, which takes standard JDBC connection information (URL, user name, driver, etc) and a String representing the dataset. In this case, the story uses Groovy’s MarkupBuilder to to create an XML representation of the data.


and 

given "the database_model method is invoked with a dataset", {
 database_model("org.hsqldb.jdbcDriver",
   "jdbc:hsqldb:hsql://127.0.0.1", "sa", ""){
  def writer = new StringWriter();
  def builder = new MarkupBuilder(writer);

  builder.dataset(){
   word(word_id:1, spelling:"bellicose", part_of_speech:"Adjective")
   definition(definition_id:10, definition:"demonstrating willingness and \
       willingness to fight ",
    word_id:1, example_sentence:"The pugnacious youth had no friends left \
      to pick on." )
   synonym(synonym_id:20, word_id:1, spelling:"belligerent")
   synonym(synonym_id:21, word_id:1, spelling:"aggressive")
  }
  return writer.toString()
 }
}

Note, the and clauses– in truth they’re noops but they serve to make the story read more nicely. Notice how the MarkupBuilder instance creates data that matches the data model found in the 2nd given. For instance, the word “bellicose” is inserted with an id of 1. That right there is the heart of the plug-in– inserting data into the database so that a high level story can play out. Now, using easyb, it’s time to validate that the call to database_model worked.

Validating the database_model call means querying the database for the data that the database_model call should have inserted– in my case, the story verifies the word bellicose is present!


when "a select statement is issued for a value that could 
 only be present if the database_model call worked", {
  value = null
  sql.eachRow("select word.spelling from word where word.word_id = 1"){
   value = it.spelling
  }
}

Note how the story still uses GroovySQL to talk to the database. Once the word (stored in the value variable) is obtained, it’s time to verify it is there.


then "the word bellicose should be returned", {
 value.shouldBe "bellicose"
}

Lastly, stopping the database is as easy as sending a shutdown command (via SQL) to the instance of HSQLDB.


and "shut down the database", {
 sql.execute("SHUTDOWN")
}

BDD with databases in Java can't get any easier!

Plug-in resources: