Searching for something that will permit building database schema validation that may be put together right into a standalong tool or like a module for use in another application.

The scenario is - a customerOrhost application that's offered to clients, and maintained through service releases. The service releases include database scripts to up-to-date the database with any schema changes etc.

However, as this is a database and also the customer could either

  • run upgrade scripts improperly, or
  • mess around and screw up something within the DB

I must have it provided with each release therefore we can verify the struture, and perhaps some data, applies. Also to be readily available for remote debugging whenever a customer includes a service ticket.

Schema compare tools I have investigated work ideal for evaluating local databases, however i haven't seen something which can generate something that's deployable with this application.

Thanks!

Have a look at SchemaCrawler, that is ideally suitable for this purpose. SchemaCrawler can generate human-readable database schema (and data) "pictures", which are created to be in comparison using plain-text file comparison tools. SchemaCrawler is database and operating-system independent, free, open-source, and may be invoked in the command-line, in addition to via a Java API.

You state that schema compare tools don't meet your needs, but have you thought about utilizing a schema compare tool SDK and write your personal validation code? I product manage Red-colored Gate's SQL Compare and it is API, the SQL Comparison SDK, will, for instance, permit you to compare a schema snapshot against a target database and abort the deployment whether it does not match, in addition to saving a log from the variations, that could be e-mailed back.

http://www.red-gate.com/products/SQL_Comparison_SDK/index.htm