MySQL check if table has correct schema

Question

I am currently developing server software in C++ with a MySQL data backend. I am using the official MySQL/connector library from Oracle to work with MySQL. The connection itself is working and I'm not having any issues with that.
My problem is that the database and the table schemas tend to change every once in a while because new tables and columns keep getting added. Also exiting column may be changed for the same reason. To make sure I recognize outdated server software quickly I wanted to add a warning when the database has changed.
My first idea was to hardcode how the database (and tables and such) should look and then check whether the current database matches the hardcoded data. But I have no clue how to achive that.

In summary I want to be able to detect whether

• A table has been added or removed
• A column in a table has been altered
• A column in a table has been added or removed

with as little C++ code as possible. Also it should be quite easy to maintain.

Additional information will be added when required.

Answer 1

I would suggest the following approach:

1) fork and execute the mysql command line client. Set up a pair of pipes, to mysql's standard input and output.

2) At this point you should be able to execute simple commands by piping them to mysql via the standard input pipe, and read the output from the standard output pipe.

You will need to make careful notes as to the output format of each mysql command, so that you know when you finished reading its output, and you can send the next command.

3) As the first order of being, execute:

show tables;

The output that comes back will list all tables in the database. Parsing the output into a list of table names is trival. Then execute for each table:

show create table <tablename>;

The resulting output shows all fields in the table, its keys, and constraints. Pretty much all of this table's schema. Lather, rinse, repeat, for every table.

4) In this manner you can capture a basic schema of the entire database, for comparison purposes. If necessary, use the same approach to capture the triggers, and other objects. You'll likely need to do some minor massaging of the data, and exclude a few bits. "show create table", for example, will include the current AUTO_INCREMENT values, which you can ignore.

This general approach, of driving a mysql process via its standard input and output, is bit wobbly, of course. With a little bit of work, you can use mysql's native client library, and execute all of these commands, and capture their results, directly. This should be more reliable.