Remove database schemas from Specification repo

[mrshll1001]

I think we should remove the database schemas from the specification repository, along with the Github actions and behaviour which generates them.

This would mean the following files get removed:

• build_database_mysql.sh
• build_database_postgresql.sh
• database/
  • database_mysql.sql
  • database_postgresql.sql
• datapackage.json
• requirements_build_database.in
• schema
• validate_examples_json_schema.sh
• .github/workflows/build_database.yml

General high-level motivation: the specification repo should be scoped to the schemas and documentation. Storage and implementation of HSDS should be abstracted away. Having these in the repo risks implying there is a normative way to represent HSDS internally in a program, and risks the schema definitions drifting from these if there are build failures.

Some practical matters:

We've seen that there are indeed problems stemming from maintaining these database schema files, and I'm not sure we are seeing any benefit from them. It's unclear what the state of the builds are (#554, #357), and the builds cause constant failures when working with branches for governance of the specification.

• Example build failure

This stems from how they're integrated into the repository, and conflicts with the rules we have for the governance. We also have the problem that according to #554 and #357; the database_mysql.sql file does not get updated. Meaning that it's currently out of date compared to the Specification.

The build errors stem from the tooling's attempts to generate databases and commit them back after every push. This means that governing the spec requires an additional step to override checks when merging PRs because there has been a failure. This is bad practice and trains community members to override checks to enact governance could lead to potential security issues. Also, it's mildly irritating to take these additional steps and then have github inform you of build errors after every push to every branch.

Even taken by themselves, I worry about the usefulness of these files. These are designed to be used as a single jumping off point for bootstrapping a database schema from the HSDS Schemas but their position in the build process and repository might imply otherwise to some people. They cannot be used for migrations. I feel that someone who would use these to bootstrap a database might also believe they can be used to migrate that database schema to newer versions based on upgrades of HSDS. The fact that these are inside the schema repository instead of elsewhere adds weight that these schemas are normative and useful.

I think the best way forward is to remove them from the specification repo. This improves our ability to maintain and govern HSDS and reduces ambiguity. For the types of people who want/need these types of auto-generated database schema files; we can take one of the following approaches:

• migrate the builds and files to a dedicated repo. Add a README which clarifies the purpose and build process (e.g. don't use these for migrations). This repo can be set to track the default branch of HSDS, and could auto-build the databases from the schemas after each release.
• Write a tutorial or how-to, on bootstrapping a database schema from the HSDS Schemas. Basically use the scripts/tools which are in the current build-process, but imbue them with the context and learning that people might need on their journey.

[mrshll1001]

Oh, it should also be noted that:

• Neither the database schemas, the scripts to generate them, nor the Github actions are considered normative content. They're not part of the specification.
• The docs which mention them also need updating, which presents another step for maintenance and also is non-normative content: https://docs.openreferral.org/en/latest/hsds/database_schemas.html

[MikeThacker1]

@mrshll1001 I'm not bright enough to understand everything you say here but I want to raise one concern.

I know version 3 rightly moved away FROM defining the standard in the open knowledge foundation's way of defining a relational database TO considering the standard to be one defining API methods. As I understand it, the UK profile still enables us to define the standard in a way that reflects the one-to-many relationships that can be shown in an entity relation diagram (ERD) and I tend to use an ERD to remind myself of the relationships. Is it possible for me to go on doing that?

[mrshll1001]

@MikeThacker1, the short answer to your question is that yes it will be possible to continue generating an ERD for the relationships in the schema :-)

Currently, the ERDs such as those in the docs are currently generated from the Frictionless Datapackage Serialization (datapackage.json) file, which is in turn generated from the schemas at every update. My understanding is that there are no plans to remove this.

In this issue, I am proposing removing the .sql files which are used to stand up a HSDS-shaped database quickly. I don't think this repo is the appropriate place for them, I don't think they're used, and they cause problems when governing/managing the standard. If you're actually using these .sql files to generate any ERDs, I'd be keen to hear this, but just in case you are I would avoid using the database_mysql.sql file because it is not kept up to date with the standard.

On the note of ERD diagrams: in the future, I would like to generate these directly from the JSON Schemas rather than the datapackage. This is for two reasons:

1. datapackage.json is a serialization rather than the canonical schema. It contains fields needed for a datapackage format to work which aren't in the main spec such as various id fields used to link up tables etc.
2. This would mean we are not reliant on any interim serialization to generate diagrams, allowing us to deprecate automatic generation of datapackage.json at a later date. We might not want to do this, ofc, but by not having our diagramming capacity coupled to a serialization like this would allow us the flexiblity to move forward.

I did some thinking on how we might do this in #577, but I haven't been able to progress it.

[MikeThacker1]

Thanks @mrshll1001

My understanding is that there are no plans to remove this [the ERD].
Good

On the note of ERD diagrams: in the future, I would like to generate these directly from the JSON Schemas rather than the datapackage
If/when we get round to this, I'd love to see the "crow's feet" introduced to indicate the one-to-many relationships