A new install of Hydra always creates tables with src/sql/hydra.sql and assumes all the upgrade-*.sql files have been applied. This means testing migrations require a bit of care and attention to detail to step order.

Note that some changes, especially to the Builds table may take many many hours, and may require custom migration steps not covered here.

Changing the schema

1. Edit src/sql/hydra.sql to have the schema you want to end up with
2. Run make -C src/sql update-dbix hydra-postgresql.sql to regenerate the Perl modules in src/lib/Hydra/Schema/. Note: If you edited those files by hand, the Makefile will consider that an error and not update them again.
3. Write a at src/sql/upgrade-xx.sql, replacing xx with the next incremented number to convert the database from what hydra.sql was to what it is now.
4. Commit all the changed files.

Testing the migration

The basic idea is you need to create the database at the version before your change, apply your change, and run hydra-init. As follows is specifically how to do that, assuming your branch is based off of origin/master.

DANGER: These instructions will destroy your hydra database. Be 100% certain you're not connected to a server running Hydra with any data you care about.

Assuming you're running postgresql like this in a background terminal as written in https://www.notion.so/grahamc/Hydra-Hacking-a455cc6c9ec1489d9144ed12c19d61e4#e329417b95594d94b233c3ffcf6efc50:

cd path/to/hydra
initdb ./postgres
postgres -D ./postgres/ -k $(pwd)/postgres/

In another terminal, check out master and initialize the database:

git checkout origin/master
dropdb -h $(pwd)/postgres hydra; createdb -h $(pwd)/postgres hydra
hydra-init

At this point your database is empty. You may want to run hydra-server and add some users and jobsets and projects and stuff.

Now check out your branch and run hydra-init to run migrations again:

git checkout -
hydra-init