Convert *Loader classes into Flyway migrations

[scionaltera]

Now that we're using Flyway to manage the database it's possible to move the initial data load for biomes, emotes, etc. into SQL scripts that run after the schema has been created. Managing it that way will make it a lot easier to load new things into the database in later releases.

[scionaltera]

After looking through this a little further, the Loader pattern seems like the right way to go.

We can't use pure SQL migrations in Flyway without also hard-coding UUIDs in each entry, because it's Hibernate that is assigning them and not PostgreSQL.

We can't use Flyway's Java migrations or even its Spring migrations either; those aren't managed by Hibernate. You either get a Connection so you can do raw PreparedStatement queries or a Spring JdbcTemplate which are both much lower level than Hibernate and would not be able to assign IDs short of calling UUID.randomUUID() all over the place. I'd much rather let Hibernate do it however Hibernate wants to do it internally.

[scionaltera]

Ok I know I closed this but I couldn't stop thinking about it. I think I found a pattern that will work and will actually be better than the Loader model:

"Repeatable" SQL migrations are checksummed and get run again by Flyway if their checksum changes. It is possible to get PostgreSQL to generate a UUID. Locally I was able to make a SQL migration that generated its own UUIDs and upserted each record.

What's cool about that is that if the default load of data ever changes - like if we wanted to add more emotes or commands (seems quite likely to me) - we update the SQL script and Flyway will apply the changes on the next reboot without disturbing the existing data. We can even modify existing records this way to fix typos and stuff. Neither of those things was actually easy to do with the Loaders and up until now I had applied changes by wiping the database first. This should drastically reduce the number of situations where you need to start with an empty database.