Programmatically verify database structure in C++

Question

How would I verify a database is structured how my C++ program expects? Our source control was very weak in the past so we have many production installs out there using databases which are missing columns and tables which are now required in the current version of the C++ program. I'd like to have my app check to make sure the database is structured the way it expects at startup time. Ideally this would work for SQL, Oracle, Access, MySql DBs.

Answer 1

Assuming you can query the database using SQL, then you can use the DESCRIBE sql statement to request a description of the table you are looking at, e.g.

DESCRIBE table1;

The use your code to check through the description to analyse whether it is correct..

This will give you are list of Fields, their Type, and other information e.g.

Field |Type    |NULL |Key |Default| Extra
Col 1 |int(11) |NO   |PRI |NULL   | auto_increment
Co1 2 |time    |No   |    |NULL   |

You can then go through this table.

Answer 2

The difficulty seems to be in the cross-DBMS. ODBC drivers provide most of the functionality you need across all databases. In this situation I have used ODBC SQLTables and SQLDescribeColumn to extract a definition of all tables, columns and indexes on the database and then compared that to the output of the process run against a known good database.

This is easy enough if you just want to validate the structure, the code to repair such a database by adding columns and indexes followed logically from that but got a little harder.

Answer 3

"I'd like to have my app check to make sure the database is structured the way it expects at startup time."

And what the ... do you think this is going to solve ?

If the database is not structured the way your program expects, then I'd say your program is almost absolutely certain to fail (and quite early on at that, probably).

Supposing you could do the check (and I'm quite certain you could go a hell of a long way to achieve that, even far beyond what has been suggested here), what else do you think there is left for you to do but to abort your program saying "cannot run, database not as expected" ?

The result is almost guaranteed to be the same in both situations : your program won't run. If you are experiencing problems with "databases not structured as expected", then you need to look at (and fix the faults in) the overall process. Software does not live "in its own world", and neither do databases.