Oracle SQL-Developer 2.1

I've been trying out Oracle SQL-Developer 2.1 early adopter for the last couple of days, because SQL-Developer is the standard query tool at the company where I'm working.

Personally, I mostly use Aqua Data Studio. Apart from its odious activation scheme, ADS is excellent in every respect. However, ADS is kind of expensive, while SQL-Developer is free.

Previous versions of SQL-Developer were promising, but not very polished. They would paint strange colors on the screen in my installation, and behave poorly with regard to threading and large queries. The new version seems like a big improvement so far. Although it doesn't have the same brilliant keyboard support as ADS, it does seem to work pretty solidly, and appears reasonably attractive. (Much prettier than TOAD at any rate.)

I did notice one amusing glitch so far:








In fact the query only returned about 20 rows.

Table size frequencies

Here's an interesting query to run on your Oracle database:

	WITH r AS (
	SELECT TRUNC(LOG(10, CASE WHEN num_rows = 0 THEN 1 ELSE num_rows END)) p
	FROM all_tables
	WHERE owner = '(your schema)'
	),
	s AS (
	SELECT TO_CHAR(POWER(10, p), '999,999,999,999') || ' -' || TO_CHAR(POWER(10, p+1), '9999,999,999,999') row_count
	FROM r
	)
	SELECT row_count, COUNT(*) num_tables
	FROM s
	GROUP BY ROLLUP(row_count)

view raw table-size-frequencies.sql hosted with ❤ by GitHub

Here's what I get on the main application I've been working on the last several years:

ROW_COUNT               NUM_TABLES
----------------------  ----------
1-10                            36
10-100                          22
100-1000                        22
1000-10000                      22
10000-100000                    22
100000-1000000                  33
1000000-10000000                20
10000000-100000000               2
100000000-1000000000             3
1000000000-10000000000           1
                               183

Inline data for DbUnit tests in Grails

There is a DbUnit plugin for Grails: http://docs.codehaus.org/display/GRAILS/DBUnit+Plugin.

However, like almost all tutorials for DbUnit, this plugin assumes that the best way to organize your test data is using DbUnit's FlatXmlDataSet. The example given on the Wiki link above has this FlatXmlDataSet:

	<?xml version="1.0" encoding="UTF-8"?>
	<dataset>
	<person id="1" name="Pessoa 1" email="p1@email.com" version="1" />
	<person id="2" name="Pessoa 2" email="p2@email.com" version="1" />
	<person id="3" name="Pessoa 3" email="p3@email.com" version="1" />
	<person id="4" name="Pessoa 4" email="p4@email.com" version="1" />
	<person id="5" name="Pessoa 5" email="p5@email.com" version="1" />
	</dataset>

view raw Grails-DbUnit-FlatXmlDataSet.xml hosted with ❤ by GitHub

During my 5 years of working with DbUnit, I have come to the conclusion that FlatXmlDataSet is not the best way to organize test data. Here are my reasons:

• XML is not a good representation for tabular data. CSV is more readable.


• For most of my tests, I would rather see the test data together with the test, rather than separate.

DbUnit tests often have 3 "phases":

1. Prime the database with setup data.


2. Run some code of the System Under Test.


3. Verify changed data in the database.

For most of my DbUnit tests, I would like to see the data used for the first and last parts together with the test itself.

For this reason, we've gravitated towards inline CSV datasets in most of our DbUnit test cases. You can achieve this using Groovy in Grails very simply.

The Wiki example, used in a test with inline datasets, might look like this:

	class PersonTests extends DbUnitTestCase {
	void testCountPerson() {
	cleanInsert("person", """
	id|name|email|version
	1|Pessoa 1|p1@email.com|1
	2|Pessoa 2|p2@email.com|1
	3|Pessoa 3|p3@email.com|1
	4|Pessoa 4|p4@email.com|1
	5|Pessoa 5|p5@email.com|1
	""")
	def c = Person.count()
	assertEquals 5, c
	}
	}

view raw Grails-DbUnit-PersonTests.groovy hosted with ❤ by GitHub

(I'm using pipe characters instead of commas in this case!)
If the same Person data were used in different test methods, we might factor it out into a common method or field.

What I tend to find is that I use a lot of common reference data in my tests, but test-specific detail data. I handle the common reference data in a setUp() method, or even in the base DbUnitTestCase class. I put the test-specific detail data in each test. This prevents duplication of the common stuff, and keeps each test clean and focused. By reading the test method, you can see everything involved in the test.

We define cleanInsert() and a few other conveniences in DbUnitTestCase:

	abstract class DbUnitTestCase extends GroovyTestCase {
	DataSource dataSource
	protected void setUp() {
	super.setUp();
	dataSource = ApplicationHolder.application.mainContext.getBean("dataSource")
	}
	void cleanInsert(IDataSet dataSet) {
	Connection connection = DataSourceUtils.getConnection(dataSource)
	IDatabaseConnection dbunitConnection = new DatabaseConnection(connection)
	DatabaseOperation.CLEAN_INSERT.execute(dbunitConnection, dataSet)
	DataSourceUtils.releaseConnection(connection, dataSource)
	}
	void cleanInsert(ITable... tables) {
	cleanInsert(dataSet(tables))
	}
	void cleanInsert(String tableName, String stringData) {
	cleanInsert(stringsTable(tableName, stringData))
	}
	IDataSet dataSet(ITable... tables) {
	new DefaultDataSet(tables)
	}
	ITable stringsTable(String tableName, String stringData) {
	Column[] columns = null
	List data = []
	stringData.splitEachLine("\\|") {items ->
	if (items && items.size() > 1 || items[0]) {
	if (!columns) {
	columns = items.collect {String columnName ->
	new Column(columnName.trim().toUpperCase(), DataType.UNKNOWN)
	}.toArray(new Column[0])
	}
	else {
	data.add(items.collect {it.trim()}.collect {it == "(NULL)" ? null : it}.toArray())
	}
	}
	}
	new DefaultTable(tableName, columns, data)
	}
	ITable emptyTable(String tableName) {
	new DefaultTable(tableName)
	}
	}

view raw Grails-DbUnit-DbUnitTestCase.groovy hosted with ❤ by GitHub

Tomcat Expert Seminar

Yesterday I attended SpringSource's Tomcat Expert Seminar.

This session rocked! I have learned a lot of neat ways to enhance our usage and troubleshooting techniques. The presenter, Filip Hanik, was not only extremely knowledgeable, but he was also entertaining and engaging. He handled questions from the audience extremely well, being able to give a direct and informative answer to nearly every question.

The most interesting topics for me were:

• Large scale deployments -- showed flexible ways to organise deployment to simplify instance configuration and management.


• Troubleshooting -- great tips on Tomcat but even more on Java and server-based systems in general.

I look forward to attending similar sessions in the future.