Database change management

[GutZuFusss]

This is for the far, far future, but something one should keep in mind when facing certain decisions, that's why I am creating this ticket already.

I can't help but imagine people using this some day for private servers. Maybe even after you are long gone, but this is by far the best foundation one with some technical understanding can stumble upon.

And when Playa finally gets their millionth cashgrab by releasing EU420, they might also... Say change pets in such a way, that the old DB schema just won't do anymore. I suspect that this will actually happen quite a lot over the years, and a hoster of a private server might not always be the most competent sysadmin, let alone the sheer complexity of doing database schema changes when you skipped versions in between, lost track, and so forth. Now that I wrote it down I don't even think this needed the explaination, but im not deleting it now xx

Anyways, it's really nice to have (integrated) tooling like Liquibase for that because your schema gets updated automatically, no matter how many versions you skipped or did't skip, and you never lose any data. At some point when most current features are reflected in the database schema, something like Liquibase should be considered. From experience, it's also really useful for devs working on the same codebase and/or code that relies on the same database schema.

[the-marenga]

That looks very interesting. My solution for the near future would have been to just have a "version" column somewhere and to apply hard coded, hand written patches to the db (1=>2, 2=>3, ...) until version == current_version.

I will have a look at Liquibase, thanks for the suggestion 😄

[GutZuFusss]

``The idea is not to know which version you are on... But to be able to go between any two versions, no matter if there were changes in between. Just imagine you are 3 database changes behind, what do you do, if you don't want to loose any data? And honestly, I'd say having a manager for that is easier and faster, at the very least after the second version increment.

Say you want to add a column to a table, in e.g. Liquibase you simply add to the changelog xml file:

<changeSet id="add_forth_potion" author="gutzufusss">
    <addColumn tableName="character">
        <column name="potion_4" type="INT)" defaultValue="0">
            <constraints nullable="false"/>
        </column>
    </addColumn>
</changeSet>

And that's it. All the magic happens in the background and is taken care of for you, it calculates checksums for each change and will always know what changeset your current database state corresponds to, and which changesets will need to be executed to reach the desired state compatible with the current codebase.

No need to rush - You can think about it and switch at any point, what you do and what liquibase does is compatible... Kind of.

You would define the original changeset be just exactly the content of the db.sql you currently (or at the point you decide to implement it) have for schema initialization. You can do something like

<changeSet id="xxx">
     <executeSql filePathRelativeToChangelog="true" filePath="scripts/db.sql"/>
</changeSet>