I've added some output to the generated migrations files to make them
easier to recreate, hence the big diff.
These are important:
```
persistence/sql/migrations/20211004110001000000_change_client_primary_key.cockroach.down.sql
persistence/sql/migrations/20211004110001000000_change_client_primary_key.cockroach.up.sql
persistence/sql/migrations/20211004110003000000_change_client_primary_key.cockroach.down.sql
persistence/sql/migrations/20211004110003000000_change_client_primary_key.cockroach.up.sql
persistence/sql/migrations/20211011000001000000_change_jwk_primary_key.cockroach.down.sql
persistence/sql/migrations/20211011000001000000_change_jwk_primary_key.cockroach.up.sql
persistence/sql/migrations/20211011000003000000_change_jwk_primary_key.cockroach.down.sql
persistence/sql/migrations/20211011000003000000_change_jwk_primary_key.cockroach.up.sql
persistence/sql/src/20220210000001_nid/20220210000001000000_nid.cockroach.up.sql
```
Closes#3964
Supersedes #3993 (thanks @hperl)
This patch adds the ability to execute down migrations using:
```
hydra migrate sql down -e --steps {num_of_steps}
```
Please read `hydra migrate sql down --help` carefully.
Going forward, please use the following commands
```
hydra migrate sql up ...
hydra migrate sql status ...
```
instead of the previous, now deprecated
```
hydra migrate sql ...
hydra migrate status ...
```
commands.
See https://github.com/ory-corp/cloud/issues/7350
This patch changes the internal data structure and reduces four (sort of redundant) tables into one. As part of this change, a few new tools have been added:
* Introduce the `hydra sql gen` command and a convenience Make target with autocompletion. The command reads migration templates from a source directory and produces migration files in a target directory. Its main function is to split a single source file into multiple files using split marks.
* Introduce the `hack/db-diff.sh` command to generate database schema diffs at different commits. This script is used to view and review the impact of migrations on the database schema.
BREAKING CHANGES: This patch merges four SQL Tables into a new table, deleting the old tables in the process. The migrations in this patch are expected to be applied offline. Please be aware that *there are no down migrations*, and if something goes wrong, data loss is possible. Always back up your database before applying migrations. For more information, see [Hydra 2.x Migration Guide](https://www.ory.sh/hydra/docs/guides/migrate-v2).
Rows with NULL login_challenge in `hydra_oauth2_consent_request` and corresponding `hydra_oauth2_consent_request_handled` are deleted as a side effect of the merge migration. This is done with the assumption that only a very small number of sessions, issued by pre-1.0 Hydra, will be affected. Please contact us if this assumption doesn't apply or if the deletion adversely affects your deployment.
Signed-off-by: Grant Zvolsky <grant@zvolsky.org>
hackerman
Arne Luenser
Grant Zvolsky