Database versioning and automatic upgrading about onehourgamejam HOT 9 CLOSED

Migrations is the good concept for MySQL+PHP stack

from onehourgamejam.

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...

from onehourgamejam.

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.

from onehourgamejam.

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.

from onehourgamejam.

@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.

from onehourgamejam.

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 😢)

from onehourgamejam.

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

from onehourgamejam.

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)

from onehourgamejam.

yes. code. mhmm, PHP

from onehourgamejam.