Reset migrations after release of `v1.0.0`

[sphuber]

Currently there are 37 migrations for the Django backend and a bit less for SqlAlchemy. Aside from adding time to each time a database is created (not so much a real problem for new profiles, but time consuming for testing with the fixture manager), it adds a lot of complexity and overhead on for example testing and maintenance. It would be good if at some point we can "reset" the initial schema. Ideally this is done directly after the release of v1.0.0.

In addition, the concept of the database schema generation and version needs to be properly documented. Especially since it will also be exposed through verdi database version.

[sphuber]

To make this reset possible, first one final migration has to be added before the release of 1.0.0. This migration will define a key in the settings table in the database that defines the current database schema. This is already being done, but in different ways for each backend which makes it difficult to write the logic necessary for the migration reset. For Django we are already writing the schema version ourselves in the settings table. However, for SqlAlchemy, this is delegated to Alembic which writes the current revision (which is also different from the schema version used for Django) in the alembic_version table.

The suggestion is as follows:
1). Add a migration before aiida-core==1.0.0 that will insert the setting with key schema_version with the value 0.0.41* for both backends.
2). Release aiida-core==1.0.0
3). Create a new branch that will reset the migrations.
4). Delete all old migrations and recreate the initial schema migration setting the schema_version to 1.0.0
5). Change the schema version check logic, which is currently backend dependent, to an independent one, that checks the setting schema_version.

• If it does not exist, it concerns a database of aiida-core==0.* that did not perform the final migration. A message should be printed to first install aiida-core==1.0.0 and perform the migrations. This will bring it to 0.0.41.

• If it does exist and it is equal to 0.0.41, that means that the current state of the database should match that of the reset initial schema. Simply change the schema_version to 1.0.0

• If it does exist and is

• The value 0.0.41 is chosen because the leading zero indicates it is the first schema generation that was used by the aiida-core==0.* releases. The terminal number 41 stems from the fact that the current number of migrations stands at 40.