0

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?

FrostBytes
  • 33
  • 4

2 Answers2

0

Have a look at JPA an Hibernate. There is hbm2ddl.auto property. Looks like "update" option does what you want.

For more details What are the possible values of the Hibernate hbm2ddl.auto configuration and what do they do

classeswilldullyourmind
  • 91
  • 9
  • Thanks, but I wouldn't want to add another dependency to my already long list of dependencies. Not to mention, hbm2ddl.auto is apparently unsafe in production and only should be used in development environments. – FrostBytes Jul 10 '19 at 22:19
0

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.

Slawomir Dziuba
  • 1,265
  • 1
  • 6
  • 13
  • Thanks. I'm probably going to create a meta table for database updating, but that will work just fine for now. – FrostBytes Jul 11 '19 at 00:11
  • A simple database dump and management of its versions, just like the code, works surprisingly well, especially with frequent changes, you can always come back to one. Various methods of "alchemy" are promoted, generating schemes directly from the code, eg mapping objects. This reversal of the order seems to be a good idea, but it is not. It usually generates at least performance errors. The SQL programmer can not fix the problem without changes in the code of the entire application. It's best to stick to simplicity. – Slawomir Dziuba Jul 11 '19 at 05:31