Database versioning and automatic upgrading

[liambaloh]

The database structure changes every so often. To make site upgrades easier, an automated system for DB upgrades should probably exist - something which simply updates the database with ALTER, CREATE and DROP statements as needed between versions.

(Low priority feature)

[vintprox]

Migrations is the good concept for MySQL+PHP stack

[liambaloh]

There is currently a folder of migration scripts located here: https://github.com/OneHourGameJam/OneHourGameJam/tree/master/SQL/migrations

But these must be executed manually and there isn't a good way to determine which ones you need to execute to get from the version you're on to the most recent one...

[vintprox]

Really weird workaround would be to clear database, and then run those migrations in order every time. But we know how awful it is for performance and actual versioning.

You can require last migration number in config. If none, all migrations are gonna run (database then should be cleared before). When user runs upgrade script, it iterates through migration files to find bigger order number (than one specified in config) and builds queue.

[liambaloh]

The plan at the moment is to tie a migration file to a version, stored in config and compare it to a constant number somewhere in code. If the two don't match, migration scripts are ran. But that's not in the code yet, so you have to do it manually.

[winniehell]

@liambaloh would using https://flywaydb.org/ be an option? It uses a structure similar to the one that already exists in the SQL directory here.

[liambaloh]

Eh, writing a database versioning script would take a few hours at most, probably not worth the annoyance of including another external dependency. It's just a version number and dictionary of database-version to sql query. If the version doesn't match the last entry in the list, find it and execute all SQL queries between it and the end, update the version number in the database once done.

I'd like to keep external dependencies to a minimum, at least until #55 is done (and yes, I've been really bad with ignoring #70 for a long time until it got out of date, It's on me and I'm sorry 😢)

[winniehell]

I'd like to keep external dependencies to a minimum

My suggestion was probably too brief. 😃 I didn't intend to add flyway as a PHP dependency but use it as a command line tool. Not sure if that is feasible given that I don't need to admin this. 😉

It's just a version number and dictionary of database-version to sql query

This is basically what flyway does: https://flywaydb.org/getstarted/how

[liambaloh]

Ah okay! That makes it way more appealing. There are two concerns that came to mind though:

• The first is just a general feeling that we'd be doing the database equivalent of using visual studio for something notepad could do, which isn't necessarily a bad thing, but it seems overkill to me... This concern can't really be taken away, so I won't dwell on it, but thought I'd mention it anyway.
• The second is more of a technical problem: While the database structure could be migrated with this, we also use the config table to store key-value pairs, which would more commonly be implemented as an additional table with dozens of columns, with just one entry. We store the data as entries in a single table called configs though. So we'd need some data migration, but definitely not other data migration.

Anyway, the way I make the migration files at the moment is by looking through the SQL history in MySQL Workbench (though we use MariaDB)

[Denneledoe]

yes. code. mhmm, PHP