I want to share what I’ve done working on my current project to verify data in a database. The application we’re developing relying on SOAP messages exchange where the messages contain information to be written in datasource (DB2, MSSQL, Postgres, AS/400 etc.) Yeah we’re using them all.
You can consider this as a mappping SOAP(Business document) Database table.
When my service endpoint stores information from business document to database I want to make sure that data will be stored in correct fields.
So here you go.
1. Initialize DbAssert by passing Xml file defining the datasources according to IntegrationSpec.
dbAssert = DbAssert.initAssert("com/x/testfw/Sources.xml");
2. Set source name you’re going to use to check data in. This comes from your Source.xml file.
dbAssert.sourceName("postgres");
3. Set condition to search record by. To do this you need to know the field name to search by and supposed value of the field stored by your business logic you’re testing.
Let’s say that my code adds new customer to hypothetical “customers” table. I know that my “customers” table contains “id” field and new customer should be stored with id 123 in this field.
So I need to look record in database by “id” field with value 123.
Usage:
dbAssert.condition("id", 123);
For database it becomes SELECT … WHERE id = 123;
Any additional condition you can add by using
dbAssert.addCondition("last_name", "Coupland");
In this case for database it becomes SELECT … WHERE id = 123 AND last_name = ‘Coupland’;
4. You’re able to make assertions.
dbAssert.assertColumn("customers.name", "Douglas");
Where “customers.name” is table name and field name to retrieve value from.
“Douglas” is the value we expect in this database field. You can add as many assertions as you want.
You’re also able to specify table name just once and then for assertions use field names.
dbAssert.table("customers");
dbAssert.condition("id", 123);
dbAssert.assertColumn("last_name", "Coupland");
dbAssert.assertColumn("name", "Douglas");
etc.
Ok. There is more. You can pass a HashMap with fieldName – expectedValue pairs to assertColumn method.
dbAssert.sourceName("testSource");
dbAssert.table("customers");
dbAssert.condition("id", 123);
final Map columnsMap = new HashMap();
columnsMap.put("name", "Douglas");
columnsMap.put("last_name", "Coupland");
dbAssert.assertColumn(columnsMap);
It’s very useful when your table is fairly big.
What else?
Setting table name (dbAssert.table(“tableName”)) resets already set conditions the same is true for dbAssert.sourceName(“sourceName”).Using dbAssert.condition(“key”, “value”) resets previously set conditions as well. Note: dbAssert.addCondition(“key”, “value”) DOESN’T reset previously defined cond’s.
It’s intended only for database mapping verifications. It’s not a general-purpose testing class. You’ll still need to write jUnit tests. Sorry. :) I want to keep it as simple as possible. I would consider it as just another type of assertion statement you can use in your jUnit tests with already existing ones but for testing data in database. I like it more than DBUnit of course I do it’s implemented by myself. ;)
11 Comments
Danil, what I appreciate in your approach are steps 1) and 2) — if I have understood right, you use an xml with specifications of all your data sources. This is good.
But why not to extend this approach further? Why do you describe your reference data in code?
For an example, I’ve looked at DbUnit’s page. They have such a nice code snippet on asserting table contents:
ITable actual = dbDataSet.getTable(“TABLE”);
IDataSet expDataSet = new FlatXmlDataSet(new File(“expDataSet.xml”));
ITable expected = expDataSet.getTable(“TABLE”);
Assertion.assertEquals(expected, actual);
It means: you have actual data in your DB and you have xml file with reference data, and you compare them. No need to write endless assertColumns or Maps initialization statements. Moreover, you can generate xml files with reference data from some other database.
Your opinion?
Simplicity! and flexibility.
But, I agree if I find myself writing tons of assertColumn statements I’ll have to think about another approach. But I don’t really like idea that I have to write additional xml file with expected data especially if it’s a couple fields. Moreover I would have to care about columns I don’t want to check, maybe provide mechanism to exclude them from verification. That’s btw what dbunit does. Then it’s getting harder to change my assertions and expected values – go to separate file and change there. I lose flexibility and visibility. I want to keep things as simple as possible but…. I agree not simpler ;) Unit test should be in my opinion self contained I mean without many additional resourced to look into if I need to realize what’s being checked there.
This looks very sexy.
Is there a way to make assertions on number of records returned? For example, assert that (SELECT COUNT(*) FROM customers where name=”John”) > 0 ?
Sometimes assertion like that may make sense. For example, in a case when you know that at least one new record should be added to the table, but you don’t know what id is it going to have.
It would also be nice to be able to use implicit join in the statements that are being asserted. For example assert that in (SELECT c.id, c.name, t.status FROM customers c, transactions t WHERE c.id=t.owner and c.name=”John”) (t.status) equals to “Completed”.
However, I guess adding support for such query will make API less simple and easy-to-use.
Good idea about count(), Basil. I’ll look into this if I can implement it w/o making the API complicated. It should be pretty easy and handful. But I’m not sure about the second one with JOINS. It makes it more error prone and the real use of this in unit test is doubtful for me now.
Ok. I have a few ideas for the api. It’ll be more Ruby’sh styled.
//set the datasource name to use
dbAssert.source(“source”);
// set table name to query
dbAssert.table(“table”);
// set condition to retrieve data by e.g. select … from table where id=1;
dbAssert.condition(“id”, 1);
// check if the field “name” contains expected value “value”
dbAssert.assert_column(“name”,”value” );
//checks field “user_name” for non empty value
dbAssert.assert_not_null(“user_name”);
// check if count of records returned more than one
dbAssert.assert_count_gt(“*”, 1);
// chek if number of unique departments returned by query less than 2
dbAssert.assert_count_lt(“department”, 2);
//check if number of returned records “count(*)..” equals 0
dbAssert.assert_count(0);
// returns value of field “user_name” as String
final String userName = dbAssert.column_value(“user_name”);
What about testing of hierarchical data? For example a tree stored in a table with implicit parent-child relationships. Testing such implicit relationships is thing I need much now. It is not only useful for tree data, but for checking other implicit relationships in data when foreign-key constraints are not enough.
As an afterthought on numerous `assertColumn’ problem, I think you can get rid of visual clutter by introducing a string array of pairs and then getting a cycle through it.
I mean something like this:
dbAssert.assertColumns(new String[] {
“last_name”, “Coupland”,
“name”, “Douglas”,
…
});
Yury, I hope you don’t expect something like
assert.my_program_correct(); ;)
But what you could do is using current assertions in the following way
dbAssert.source(”source”);
dbAssert.table(”books”);
dbAssert.condition(”id”, 1);
dbAssert.assert_column(“title”, ” Effective Java”);
// get value of your FK
final int authorId = dbAssert.column_value(”author_id”);
//check relation
dbAssert.table(”authors”);
dbAssert.condition(“author_id”, authorId);
dbAssert.assert_column(“name”, “Joshua”);
dbAssert.assert_column(“last_name”, “Bloch”);
Thus you can check Books Authors relationship.
It’s very flixible, isn’t it?
Can you provide me with your use case and we’ll all try to find out the best way how to apply dbAssert for your purpose?
Mikhail, string array would work. Yep, it seems good.
Finally I haven’t caught why it is preferrable to dbUnit ))
Teapot, nobody says that it’s better or worse than dbunit. BTW, have you tried dbUnit?
I did what was preferable for me and my project and it was much more easier for me to implement dbAssert than dealing with dbUnit its configuration and its ITable’s and IDataSet’s classes. Moreover, I already had everything in place – I had datasources defined I had existing unit test, one thing was missed – dbAssertions and I just added them. The thing is that there is no universal solution for everything but there is preferable one and mine was preferable for me and my project.