Major restructuring of backend features

[Ludee]

At the project meeting we discussed some major developments to keep up with the growing use of the platform.

• [ ] Remove schemas in database backend
• [x] Move metadata from comment on table to a dedicated metadata table.
• [ ] Implement versioning with revisions

[Ludee]

It is issue 1000.

[jh-RLI]

Save metadata in a separate table:

I would be in favor of keeping the comment on table as the entry point for adding/editing/deleting the tables metadata. From there, the metadata will always be saved/updated/deleted as JSONB in the new metadata table on add/edit/delete actions when using the MetaEdit. This would have the advantage that we don't have to touch the previous functionality and the metadata will still be "close" to the data. @wingechr what do you think about this?

Note on the following: I take this back Metadata Search is working, i was just confused why the metaSearch table is empty on my local machine.

I found this table here. The structure is basically what we need, but I'm not sure if this table is in use or only there and the corresponding functionality is missing. Nevertheless, the table "comment" (metadata) column is a TSVECTOR data type for our use case a JSONB type would be more suitable. I would like to recycle this table if it is not in use. I think it was intended that the table be used to enable text search on the metadata string.

Additional: To enable text search on metadata, we can use a solution like the one here or build something of our own. But I would have to do some more research on this.

Edit: Just found this Issue. Seems like metadata is included in the search but might not work anymore?

[wingechr]

I don't mind doing it this way (add it as comment first). reduces the chance of breaking things

[jh-RLI]

Regarding implementing versioning see https://github.com/OpenEnergyPlatform/oeplatform/issues/337

[wingechr]

see also notes in closed #1356