Unify database documentation

[chr-schr]

Problem

Currently the database documentation is spread out over multiple places:

1. In /doc/database
2. As comments in the backend code, e.g. entity.rs

Some database constraints are not documented at all, as far as I can see. [1] [2]

Ideally, there should be only one place to look for database documentation.

792 updates and improves the database documentation itself but does not narrow down the spread of information

Possible solution

In the last meeting we talked shortly about database comments and diesel: In diesel 2.1+ it's possible to include database comments like the following in the schema.rs:

COMMENT ON COLUMN maps.name IS 'The name of the map
        Example: "My map"
        (only alphanumerical characters and whitespace)';

COMMENT ON TABLE maps IS 'Some description about the maps table';

The generated schema.rs will then contain the following:

diesel::table! {
    use postgis_diesel::sql_types::Geography;
    use diesel::sql_types::*;
    use super::sql_types::PrivacyOption;
    use super::sql_types::Geography;
    use super::sql_types::Geometry;

    /// Some description about the maps table
    maps (id) {
        id -> Int4,
        /// The name of the map
        /// Example: "My map"
        /// (only alphanumerical characters and whitespace)
        name -> Text,
        creation_date -> Date,
        deletion_date -> Nullable<Date>,
        last_visit -> Nullable<Date>,
[...]

From the database comments we could then rather easily generate a markdown table for /doc/database/schemata/03er_table_descriptions.md

Thoughts

Maybe schema.rs is not the best place to aggregate all database documentation? This process seems kind of awkward to me, but manually adding database documentation in multiple places doesn't seem ideal either.

Do you have any thoughts on this? @markus2330 @Bushuo @horenso

[horenso]

I support the idea of adding comments to the schema!

One of the pain points of the current setup is the schema.patch mechanism, that applies changes to the generated schema.rs file. We store a history of diffs, instead of just a flat history of schemas. I suggest that we ditch the patching mechanism and checkin the most recent schema.rs into Git. We can add additional comments to it directly.

The downside of this approach is that we have to update the schema.rs file for every migration. The workflow would be:

1. Write the migration in SQL (unchanged)
2. Generate the schema.rs with diesel

3. Annoying but easy: Change what diesel generated wrongly back, by comparing the files through Git. (gis types...) This is way easier then getting line numbers of diff files correctly.

   Having the documentation in the schema.rs prevents the actual names (tables, columns) and types from becoming outdated.

[markus2330]

@horenso thx for the reply

I moved the separate issue of improving schema.rs workflow to #1193

Here please only posts about database documentation.