VoxelGuest version 5 persistence system needs a DB schema versioning and update system

[nristock]

Since the ORM system we use does not have an automated DB schema update system we need to integrate our own. I suggest using a version table that keeps track of table schema versions.

+----+----------------+--------------+-----------+
| id | currentVersion | owningModule | tableName |
+----+----------------+--------------+-----------+
|    |                |              |           |

id => general identifier
currentVersion => the current version of the table schema
owningModule => an identifier that identifies the module that owns a specific data table
tableName => the name of the specific table

The update itself should be processed by the modules. Therefor, I'd add a method to the module interface that gets called when a module is enabled. The module manager should pass the current version of the schema and the module should return either the same version or execute the update and then return the new version which gets stored in the version table.

Any more ideas about it?

[MikeMatrix]

Sounds good. How about a delta update system, where you go from one version to the next, until you reach the fitting version. The programmers have to specify an 1 method interface implementation or something to update, so the data fits the new format. I'm not familiar with the code though.

[TheCryoknight]

Why would you want to make a full version system? Why not just update the tables dynamically, such as have a default value defined through annotations and if a table doesn't have a column add it to the schema and populate with the default value, and if it didn't have it remove the column. You could even make an annotation for previously known names.

[nristock]

It is way more complicated to do an automated delta update than to simply let the module developer update their tables. What should we do if the data type changed? Simply drop the column and create a new one? Doing that would cause the loss of data. What do we do if SQL constraints change? What if the default value changes? There are even more topics which could possibly cause problems (triggers, functions, etc.). Sure, we can overcome most of the problems by introducing an ass full of new annotations. But this then is more or less the same as writing a little bit of code to do the update.

Hibernate has a schema auto update system as well. Even the hibernate developers highly discourage using it.