Verfying a database is as you expect it it be

Question

I've been writing a java app on my machine and it works perfectly using the DB I set up, but when I install it on site it blows up because the DB is slightly different.

So I'm in the process of writing some code to verify that:

• A: I've got the DB details correct

• B: The database has all the Tables I expect and they have the right columns.

I've got A down but I've got no idea where to start with B, any suggestions?

Target DB is for the current client is Oracle, but the app can be configured to run on SQL Server as well. So a generic solution would be appreciated, but is not nessisary as I'm sure I can figure out how to do one from the other.

Answer 1

You'll want to query the information_schema of the database, here are some examples for Oracle, every platform I am aware of has something similar.

http://www.alberton.info/oracle_meta_info.html

Answer 2

You might be able to use a database migration tool like LiquiBase for this -- most of these tools have some way of checking the database. I don't have first hand experience using it so it's a guess.

Answer 3

Most generic solution would be to execute queries with select clause having the expected coulmns and from clause having table names, within try catch block. You can put where clause as 1=2 so as not to fetch any data. If query executed without throwing exception then you have got the expected table and columns.

Answer 4

I use DbUnit to test databases. It is a Java based solution, that integrates well with Junit. It is possible to use it with almost no Java. I havent used it in exactly the same situation as you described, but it should be close enough to work.

Answer 5

The slightly different piece might be better handled by scripting the creation of the database in the first place. A automated process gives you a better chance of making the two identical.

Another point worth making is that you minimize your risk by making your devl and prod environments identical - same database schema and vendor for both. Change the circumstances that make the two different.

Lastly, you don't say what is "slightly" different, but sometimes these are unavoidable (e.g. Oracle uses sequences, SQL Server uses identities). Maybe Hibernate can help you to switch between vendors more reliably. It abstracts details in such a way that changing databases can mean modifying a single value in a configuration file.

Answer 6

What you need to have is basically Unit Tests for your database. "A column must exist named FOOBAR, the type must be Integer. No foreign keys may exist etc."

This is doable with plain JUnit and JDBC (ask the table for its meta-data) as you may want to ensure that you are absolutely certain what is being done which may be harder when using e.g. dbUnit.

Answer 7

You can check for the presence of tables, columns, views, etc. using these tables in Oracle

USER_TABLES USER_VIEWS USER_PROCEDURE

(or for everything) USER_OBJECTS WHERE OBJECT_TYPE = '??'

To keep going... USER_TAB_COLS for table columns

Regards K

Answer 8

If you're using plain JDBC, you should try utilizing this method: DatabaseMetadata.getTables and other similar methods available in the metadata class.

Answer 9

I use MigrateDB for this. It lets you build queries that do things like check for the existence of given tables, columns, rows, indexes, etc. for a given database and use those as "tests." If a test fails, it triggers an "action" (which is just another query that knows how to remedy the problem.)

MigrateDB supports multiple database platforms (you can specify the "check for table existence query" for each platform, for example), completely configurable tests (you can make your own up), comes with fairly complete Oracle tests, and can be run in "audit only" mode so that it only tells you what the differences are.

It's a nice, robust solution.