0

If I am testing a database rowset, what would be the criteria to test against?

Should I test that a row exists with a name of A, etc etc (What's in the database)? Is this a bad idea? This sort of test is fragile as the data can change but the query can be correct.

With that in mind, would it be better to check the column names I have returned in the dataset? Like so (http://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset).

Thanks

GurdeepS
  • 65,107
  • 109
  • 251
  • 387

3 Answers3

0

Testing what's in the database is pointless unless it is data static reference data tables (and you can often make these read only, so again pointless).

Testing columns names? Why would these change? Hopefully developers are not making changes against the production server?

What you probably want, is as part of your CI build process to fully deploy your databases (applying all incremental change scripts [you have those right?]) to a test server.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
0

You don't need to test anything about the database except that the database and the code work.

So then, using a database that you have created in your test environment, connect the code that uses the database to it, and test that it does whatever it is supposed to.

Remember that in a test, it is very rare that you need to check how something is done, but rather you should be testing that dome thing does in fact get done.

For example, if you are using hibernate to persist your entities, you might have a test called canRoundTripAFooBar that saves a foobar, loads it back again, and compares each field to make sure the one you loaded and the one that you saved are the same (except for those bit that should not be the same, like Ids or something)

time4tea
  • 2,169
  • 3
  • 16
  • 21
0

Don't test what's in the database. Just provide some examples of how your system delivers value.

If the value is genuinely in the database - for instance, another service uses that data - then describe the things your system provides in terms of that value. For instance:

  • My system can save a user with his family of four children and a spouse in another country.
  • My system can handle Japanese addresses.
  • My system can provide historical price data.

If you really have to, you can then associate big chunks of data - rather than individual rows - with the domain concepts to which they match. Removing some of the duplication - putting it in custom test queries, for instance - will help to keep this less brittle.

If you can think of examples of the things your system provides then you can probably cover all the data it provides too.

Another way to make the tests still less brittle is to use your persistence layer or the persistence layer of the system you're serving (Hibernate, for instance) to translate the data back into domain objects which make more sense for these examples.

This will also help you check that the data you're providing is actually valuable, rather than simply there. If it's your own system you're providing value to, you can do this and retain complete freedom where the format used to store the data is concerned. You can also do things like:

  • My system does not allow me to store children without a parent.
  • My system does not allow addresses with more than 150 characters in a line.

and check for database exceptions - also allowing you to verify constraints.

Lunivore
  • 17,277
  • 4
  • 47
  • 92
  • What would be a way to test a select query which returns a set of rows, each representing a PC? – GurdeepS Jan 12 '11 at 23:14
  • Depends who uses the data. If it's another team / system, find out what they find valuable about the data, then show an example of that. What's particular about the PCs? Do they look to see what software is installed? Who's using them? What the admin password is? Then you can write a bit of JUnit which runs the query called `shouldBeAbleToFindAdminPasswordsForAllPCs`, and shows an example of a couple of things you expect. – Lunivore Jan 13 '11 at 01:13
  • I see. So the standout attribute is the test and what you're looking for. So if I test to see that password has a value of "passwords", it will fail when the table changes, so this is a good thing to test frequently. Do yiu normally test the value more than the column name returned? – GurdeepS Jan 14 '11 at 00:04
  • Always. Nobody cares about the column name beyond using it to access the information. If you can express the thing that's valuable, you get to change the rest without changing your tests, rather than pinning things down and making it brittle. Other developers also get to understand what's valuable and how it's used, so they can change it safely. If you never change anything you can manually test everything once. Automated tests, and manual test plans, are only there to help make things easy to change. – Lunivore Jan 14 '11 at 00:32