Making sure that a table is constructed correctly
I have a schema of a database and a web application. I want to have the web application be able to select, insert and remove rows to a table, but the table may not exist, maybe in a testing environment, and the table may be missing columns, most likely because the web application has updated.
I want to be able to make sure that the table is ready to accept the data that the web application sends to it during the time the application is alive.
The idea I had is the application (written in Java) will have a table structure embedded into it, and when the application starts, just copy all of the data in the table (if it exists) to a temporary table, delete the old table and make a new one with the temporary table's data, and then drop the temporary table. As you can tell, it's nowhere near innovative.
Another idea I had is use the
SHOW COLUMNS command to correct any missing columns parallel with the
SHOW TABLES LIKE to check if it exists, but I feel like Stack Overflow would've had a better solution. Is that all I can do?
Have a look at JPA an Hibernate. There is hbm2ddl.auto property. Looks like "update" option does what you want.
There are many ways to solve the problem of consistency of the database version and the version of the application. However, in the production database, this situation is unacceptable. I think that the simplest ways are the best. To ensure such compliance, it is enough to execute a script that updates the database before performing the testing.
START TRANSACTION; DROP TABLE ... IF EXISTS; CREATE TABLE ... COMMIT;
Remember about IF EXISTS and having DROP grant!
Such a script can be easily managed by placing it in RCS and controlling the version number needed in the application. You can also save this version number in some table in the database itself and check when the application starts, whether the number is compatible with the assumed one and if you do not call the database update script.