Thursday, March 1, 2012

Sequel Pro Bundles to Copy as DBUnit FlatXMLDataSet or XMLDataSet

Sequel Pro (http://www.sequelpro.com/) is the best MySQL UI for the Mac. You can connect to your MySQL running locally or to remote instances over SSH and it has a ton of features. Best of all its free to use but I highly recommend donating (http://www.sequelpro.com/donate/) if you use it a lot. One of the features of Sequel Pro that I put to good use recently was Bundles. Bundles are simple extensions to Sequel Pro that you can write. They execute in many different contexts like when selecting a table, selecting a row in a table etc.

One of the painful things while writing unit tests for database access code is getting your database in a particular state. This requires inserting rows into these tables as part of the test setup. If you use frameworks like DBUnit for Java or PHPUnit for PHP, one would use the FlatXMLDataSet or XMLDataSet to populate these rows. For eg., this is how an employee table might be populated using FlatXMLDataSet:


<dataset>
<employee id="1" name="Steve Jobs" title="CEO" dept="1" />
<employee id="2" name="Steve Wozniak" title="CTO" dept="2" />
</dataset>


This is pretty simple but as you add tables with more columns, you spend quite a bit of time writing these XML files by hand.

If you have tables with BLOB columns, you would need to use XMLDataSet which is even more verbose than FlatXMLDataSet:


<table name="employee">
<column name="id"/>
<column name="name"/>
<column name="title"/>
<column name="dept"/>
<row>
<value>1</value>
<value>Steve Jobs</value>
<value>CEO</value>
<value>1</value>
</row>
<row>
<value>2</value>
<value>Steve Wozniak</value>
<value>CTO</value>
<value>2</value>
</row>
</table>


Obviously nobody wants to do these by hand. In the past I have generated these using DBUnit scripts or using Jailer. I thought it would be easier if I could simply select one or more rows in Sequel Pro and then right click to convert to the appropriate XML format. I have created two different bundles one to Copy as FlatXmlDataSet and another to Copy as XMLDataSet to do just that.

I have submitted a pull request to SequelPro and you can also look at my fork in github: https://github.com/chandraonline/Bundles

To install these bundles:


  • Download CopyAsDataSet-1.0.tgz

  • tar xvzf CopyAsDataSet-1.0.tgz

  • copy the two directories to "~/Library/Application Support/Sequel Pro/Bundles"

  • In Sequel Pro do Bundles->Reload Bundles



Now you can select one or more rows, right click Bundles -> Copy -> CopyAsFlatXMLDataSet or Bundles -> Copy -> CopyAsXMLDataSet and you will have the data in that format in your clipboard.

No comments:

Post a Comment